SQL Coding Guidelines
If you want to SELECT into a procedure parameter,
declare the parameter as IN OUT, whether or not you reference the parameter
value, unless the parameter is a field.
To handle NO_DATA_FOUND exceptions, write an exception
handler. Do not code COUNT statements to detect the existence of rows unless
that is your only concern.
Do not check for errors due to database integrity
problems. For example, if a correct database would have a table SYS.DUAL with
exactly one row in it, you do not need to check if SYS.DUAL has zero or more
than one row or if SYS.DUAL exists.
Trigger
style should be either Override or Before.
When-Create-Record
fires even when insert_allowed is set to null. Code logic to test first if the
record insert allowed and then allow the logic to be executed.
Following
is the checklist of all the properties or code that should be set or written
in:
1. Have
property classes been applied to each corresponding form builder object? Such
as
Windows, canvases, blocks, items etc.
1. Does
module name match exactly with form name?
2. Has
first navigation data block been set?
Non Modal Windows
1. Have
window positioning logic been coded in app_custom? (app_custom.open_window)
2. Have
a proper title been assigned to the main and detail window?
3. Have
the window opening and closing logic been coded into app_custom? (app_custom.open_window
and app_custom.close_window).
4. Have
the menu entries been appropriately enabled and disabled for the form?
5. Have
you assigned the content canvas of the main window to the dialog window (rule)?
1. Have
you set the navigation properties of each block?
Dialog Blocks
1. Have
you coded the logic to disable the key triggers for Dialog Blocks?
2. Have
you set the previous and next navigation data block property to itself (disable
tabs)
for the dialog block?
Single Record Blocks:
1. Have
you kept the navigation to same record for single block window?
2. Have
you disabled first record, last record, pevious record and next record menu
entries?
(5-14).
3. Have
you disabled KEY-CREREC, KEY-DOWN, KEY-NEXT? (Disble only if required)
Multi-Record Block:
1. Have you set the navigation style for multi-record
block to Change Record?
2. Have you accounted for the situation when drilling
down to detail is not allowed?
Master-Detail Block:
1. Have you set the coordination property to prevent
Masterless Operation?
2. Have you set the Master-Detail property to ISOLATED?
3. Have you set the property of dialog detail block to
DEFERRED?
In
Oracle Forms, you need to change the block Key Mode property to Non–Updatable
to turn off Oracle Forms default ROWID references for blocks based on views.
Specify the primary keys for your view by setting the item level property
Primary Key to True. For example, a view based on the EMP table has the columns
ROW_ID, EMPNO, ENAME, DEPTNO, and DNAME. Set the Key Mode property of block
EMP_V to Non–Updatable, and set the Primary Key property of EMPNO to True. If
your block is based on a table, the block Key Mode should be Unique.
on-insert:
1. Does your trigger included the 'OR is null' clause
for null allowed columns and vice versa.
1. does the lock statement contains RTRIM for db side
fields.
1.
Have you applied the CREATION_OR_LAST_UPDATE property class
to WHO columns (only CREATION_DATE and LAST_UPDATE_DATE) of all you block
fields?
2.
Have you set the query length of all your text items to
255 characters?
3.
Have you applied DISPLAY_ITEMS PC to all your hidden
placeholder items? (items with null canvas value)
1.
If you have changing pop lists make sure each of the
list populated contains the default value set at design time.
1. Have you set all the buttons property to Mouse
Navigate NO.?
2. Have you set the ‘fire-in-enter-query-mode’ property
appropriately to each mouse button?
3. Have you called APP_VALIDATE on each button before
performing the button associated operations?
1.
Does your LOV restrict user only to valid values? (not
applicable in query find windows only)
2.
Does the title of the LOV correspond to the name of the
object in the LOV and is it plural?
3.
Prompt of the first item matches the prompt of the item
which invoked the LOV?
4.
Have you reset the RECORD_STATUS to query in POST-QUERY
trigger?
5.
Have you decoded ‘Y’ to ‘*’ and ‘N’ to ‘NULL’ in LOV
values?
6. You must not
populate hidden fields by returning values in them in Enter-Query mode.
Check all the constraints mentioned in the forms
specifications. Most of common of them Unique and foreign key constraints.
1. For each sub-function a separate menu should be
created specific to a specific parent menu entry.
2. For sub-menu there should not be any prompt but they
must be included in the menu.
3. Assign a menu entry a sub-menu to create a tree like
structure.
4. Setup and Reports must exist at the top level entry.
5. The user function names defined in the Form
Functions form are displayed in menu entries.
6. Function names should be
<appl_prefix>_<form_name>_<mode> such as XXCS_XCSINVEN_INQUIRY
for invoice entry form of XXCS application. If calling a form of any other
module then the convention remains same such as XXCS_FNDRSRUN.
7. Hide sub-functions as opposed to enable/disable, if
the function is not available for a particular user.
8. Sub-function
menu is place below the form function entry.
9. A
menu of sub-functions always assumes the name of the form entry with”_MENU”
appended, for example:”PO_POXPOMPO_MENU”. The user menu name should be the
<formname>: Subfunctions, for example:”Purchase Orders: Subfunctions”.
10. All
sub-functions for each form are predefined by the developer, and are named
<form>_<subfunction>, for example:”PO_POXPOMPO_DELETE”.
11. Where there are many restrictable subfunctions for
a particular form, and those subfunctions group well into categories
(Approvals, forexample), group the Subfunctions according to their category,
creating for example, ”PO_POXPOMPO_APPROVALS_MENU”,
1. Register
key flex fields.
2. Assign
null canvas to all DFF columns.
3. Apply
TEXT_ITEM propertly class to attribute and attribute category columns.
4. Set
query length to 255.
5. Create
DFF standard displayed field (contatenated segment field) as 2000 characters.
6. Name
the field as DESC_FLEX
7. Apply
the property class TEXT_ITEM_DESC_FLEX.
8. Apply
the appropriate canvas to DESC_FLEX.
9. Attach
ENABLE_LIST_LAMP LOV from the template form to the the displayed field
10. Set
validate from list property set to no.
11. Call your
flex field definition procedure (FND_DESCR_FLEX.DEFINE) from
WHEN-NEW-FORM-INSTANCE. After the define procedure you may call
UPDATE_DEINITION procedure to enable, disable or update flexfield by passing
appropriate paramenter depending on the form logic.
12. Call
FND_FLEX.EVENT from PRE–QUERY, POST–QUERY, PRE–INSERT, PRE–UPDATE,
WHEN–VALIDATE–RECORD, WHEN–NEW–ITEM–INSTANCE and WHEN–VALIDATE–ITEM triggers
defined at form level. Make sure these triggers are not overidden by the block
or item level triggers. If required set record or item level triggers execution
style to BEFORE so that the form level triggers still execute. If setting
record status to query from post-query trigger at block level that trigger’s
execution style must be set to AFTER.
FND_DESCR_FLEX.DEFINE
(
/*
Arguments that specify the flexfield location */
BLOCK=>’block_name’,
FIELD=>’field_name’,
[DESCRIPTION=>’description_field_name’,]
[DATA_FIELD=>’concatenated_hidden_IDs_field’,]
/*
Arguments that specify the flexfield */
APPL_SHORT_NAME=>’application_short_name’,
DESC_FLEX_NAME=>’descriptive
flexfield_name’
/*
other optional parameters */
[VDATE=>’date’,]
[TITLE
=>’Title’,]
[AUTOPICK=>’
{Y|N}’,]
[USEDBFLDS=>’
{Y|N}’,]
[READ_ONLY=>’
{Y|N}’,]
[LOCK_FLAG=>’
{Y|N}’,]
[HELP=>’APPL=application_short_name;
TARGET=target_name’,]
[CONTEXT_LIKE=>’WHERE_clause_fragment’}
);
Table Specifications
Combinations Table
Create a combinations table. Table specifications
include
XXX_ID
NUMBER 38 NOT NULL,
STRUCTURE_ID NUMBER
(38) NOT NULL,
SEGMENT
1…N VARCHAR2
(60) NULL
ENABLED_FLAG VARCHAR2
(1) NOT NULL
SUMMARY_FLAG
VARCHAR2
(1) NOT NULL
START_DATE_ACTIVE
DATE NULL
END_DATE_ACTIVE DATE
NULL
DERIVED_COLUMN ANY_DATA_TYPE
NULL (Contains segment qualifier values, can be defined with any valid name)
WHO
columns
Create a special maintenance form to define valid key
combinations.
Define unique index on XXX_ID column.
Create an ORACLE sequence for your column with the same
grants and synonyms as your combinations table (for insert privileges). Name
your sequence YOUR_TABLE_NAME_S.
Register your key flex field table.
Foreign Key Reference Table
Define unique id column exactly the same as in
combinations table (may or may not be null)
Define structure id column exactly the same as in
combinations table (may or may not be null) {pass by num parameter or make a
corresponding field on the form}
Adding flexfields to forms
Create hidden id fields (unique_id, structure field (optional)
and segment fields (optional)).
Set query length of 2000 for hidden id field and 255
for all other fields mentioned above.
Create concatenated segments field with varchar2 (2000)
Apply text_item property class to each the above
mentioned fields.
Attach ENABLE_LIST_LAMP LOV from the template form to
the concatenated (displayed field) and set validate from list to no
Call your flex field definition procedure (FND_KEY_FLEX.DEFINE)
from WHEN-NEW-FORM-INSTANCE. After the define procedure you may call
UPDATE_DEINITION procedure to enable, disable or update flexfield by passing
appropriate paramenter depending on the form logic.
Call FND_FLEX.EVENT from PRE–QUERY, POST–QUERY, PRE–INSERT,
PRE–UPDATE, WHEN–VALIDATE–RECORD, WHEN–NEW–ITEM–INSTANCE and WHEN–VALIDATE–ITEM
triggers defined at form level. Make sure these triggers are not overidden by
the block or item level triggers. If required set record or item level triggers
execution style to BEFORE so that the form level triggers still execute. If
setting record status to query from post-query trigger at block level that
trigger’s execution style must be set to AFTER.
FND_KEY_FLEX.DEFINE
(
BLOCK=>’ORDERS’,
FIELD=>’KFF_CONCATENATED_VALUES’,
APPL_SHORT_NAME=>’SQLGL’,
CODE=>’GL#’,
NUM=>’101’,
DISPLAYABLE=>’ALL’
INSERTABLE=>’ALL’
UPDATEABLE=>’’);
FND_DESCR_FLEX.UPDATE_DEFINITION(
/*
Arguments that specify the flexfield location */
BLOCK=>’block_name’,
FIELD=>’field_name’,
/*
Argument to enable or disable flexfield */
[ENABLED=>’{Y|N}’,]
/*
Other optional parameters */
[VDATE=>’date’,]
[TITLE
=>’Title’,]
[AUTOPICK=>’{Y|N}’,]
[USEDBFLDS=>’{Y|N}’,]
[READ_ONLY=>’{Y|N}’,]
[LOCK_FLAG=>’{Y|N}’,]
[HELP=>’APPL=application_short_name;
TARGET=target_name’,]
[CONTEXT_LIKE=>’WHERE_clause_fragment’}
1. Show message number for warn and error messages
2. Do not show messages.
3. Message naming standard is
<prod_name>_<group_code>_<self_explanatory_message>
4. Assign 0 message number to the messsage if you wish
not to display the prefix and the message number
5. custom message defined should be greater than
400,000
6. Use two messages to distinguish singular and plural
token values
e.g., One row updated.
&NUMBER_OF_ROWS
rows updated.
[Two
distinct messages]
Creating Row LOV
To display LOV on startup
At
the end of WHEN-NEW-FORM- INSTANCE call
EXECUTE_TRIGGER('QUERY_FIND');
Implementing Row LOV
To implement a Row-LOV, create an LOV that selects the
primary key of the row the
user wants into a form parameter, and then copy that value
into the primary key field in the results block right before executing a query.
1. Create a parameter for your
primary key
2. Create an LOV
3. Create a block level PRE-QUERY
Trigger
1.
Set Execution Hierarchy Before
2.
Write the code
IF :parameter.G_query_find =
'TRUE' THEN
<Primary Key> :=
:parameter.<Your parameter>;
:parameter.G_query_find :=
'FALSE';
END
IF;
4.
Create a Query Find Trigger
Finally, create a block-level user-named trigger
QUERY_FIND on the results block
(Execution Hierarchy: Override) that contains:
APP_FIND.QUERY_FIND('<Your
LOV Name>');
