Thursday, February 6, 2014

Oracle Apps Forms Training technical



TRAINING DOCUMENT
Table of Contents


1Basic Steps for Customizing Oracle Forms for Oracle Applications


The basic steps to be followed to customize Oracle Forms for Oracle Applications
  1. Obtain the TEMPLATE.fmb form from $AU_TOP/forms/US directory from the server and place the form in the PLSQLLIB directory of the local machine.
  2. Obtain the APPSTAND.fmb form from $AU_TOP/forms/US directory from the server and place the form in the PLSQLLIB directory of the local machine.
  3. Obtain the Oracle libraries (PLL file extension) attached to the TEMPLATE.fmb form from the directory $AU_TOP/resource and place the libraries also in the PLSQLLIB directory of the local machine.

  4. Create a copy of the TEMPLATE.fmb and put the new copy in a working directory and rename the file to a customized name as per the standards.
    TIP: As a recommended standard the file name would always be 8 characters long with the first three characters would be the short name of the application where the form is registered and the next two characters would be a sub module name and the last three characters would be a short brief name of the customization.
  5. Open the new form in Oracle Forms Developer, the form should open in the Forms Developer without any errors.
    NOTE: The errors that might arise are due to the libraries and the APPSTAND.fmb form and the other Form Libraries (Refer Appendix A for the list of Form libraries) not present in the PLSQLLIB directory.
  6. Rename the Module Name to a customized name; the usual convention is renaming the module name to the file name of the form itself.
  7. Rename the Window Name of the form, from BLOCKNAME to the an appropriate window name according to the Window naming standards
  8. Change the Window Title, the title can be changed in the properties sheet of the Window.
  9. Rename the Canvas name of the form, from BLOCKNAME to an appropriate name according to the Canvas naming standards.
    CHECK: Ensure that the view name of the canvas in the WINDOW property of the form is also reflecting the canvas name that has been changed.
  10. lThe *.fmb would contain a master block ’BLOCKNAME’ and a detail block ‘DETAILBLOCK’. Give appropriate names to the same depending upon the customization (whether it is a master/detail block or tabular form or data entry form) and discard the blocks which is not required.
  11. Rename the Block Name of TEMPLATE.fmb from BLOCKNAME or DETAILBLOCK to an appropriate name.
    CHECK: Ensure that the first navigational block in the MODULE property of the form is also reflecting the block name that has been changed.
  12. Change the code in APP_CUSTOM package, change the window name in the APP_WINDOW.CLOSE_FIRST_WINDOW
    WARNING: Ignoring this step results in the window not closing when the window is tried closing by clicking the X symbol or trying to close the window from FILE → CLOSE FORM menu option or by pressing F4.
  13. Change the code in the PRE-FORM trigger, change the Window name from BLOCKNAME to the Window Name in APP_WINDOW. SET_WINDOW_POSITION call.
    WARNING: Ignoring this step results in a Developer Error message popping up saying that invalid window name passed to the form while opening the form every time.
  14. Change the arguments that have been passed to the FND_STANDARD.FORM_INFO call in the PRE-FORM trigger. Ensure that the application short name, form description and the version arguments are changed.
    WARNING: This step is important, as FORM_INFO call informs the Oracle Applications Help utility to look for the help files in the appropriate custom application.
    NOTE: The version that has been mentioned in the FORM_INFO call would be displayed in the when the Help → About Oracle Applications menu option is selected.
    TIP: Ensure that the every version change of the form has a log of changes in the PRE-FORM trigger itself.
  15. Change argument in the FDRCSID call in the WHEN-NEW-FORM-INSTANCE trigger with the appropriate form name, form version, etc.
  16. Compile the form, copy the *.fmb file to the $PROD_TOP/US/forms directory and generate the *.fmx file in the same directory using “f60gen”, where the PROD_TOP is the custom Application top directory.
  17. Register the Form, in Oracle Applications, open the Forms Registration window (Navigational path : Oracle Applications → Responsibility: Application Developer→ Application → Form) and enter the Form name, Application name and the User form name.
NOTE: The Form name is the file name of the form, and the Application name is the custom application where the executable of the form is kept.

  1. Register the Form function, in Oracle Applications, open the Functions Registration window (Navigational path : Oracle Applications → Responsibility: Application Developer→ Application → Function) and enter the Function name, the User Function name, the Form type as FORM and the User form name as the name given in the previous step.
    TIP: The function name is usually kept as the “<PROD>_<FORMNAME>” where PROD is the custom application short name and FORMNAME is the name of the form file.
    NOTE: There is a provision for entering parameters that can be passed to the form. Please refer the below section for using for form functions.
  2. Add the Form function to a menu, in Oracle Applications, open the Menus window (Navigational path : Oracle Applications → Responsibility: System Administrator→ Application → Menu) , query the menu where the form should appear and add the User form function name to the menu in the detail block.

2Developing Stacked Canvas Form


The stacked canvas is extension of content canvas which can be display or hide information dynamically. A stacked canvas is displayed at top on the content canvas Stacked canvases obscure some part of the underlying content canvas and often are shown and hidden programmatically. You can display more than one stacked canvas in a window at the same time.
The main advantage of stacked canvas

  • Scrolling views as generated by Oracle Designer.
  • To extend the size of the content canvas. To understand better please consider the below example. If you want to create a form with 50 columns and 50 rows if you place all 50 columns in content canvas you have to scroll the complete form to see the 50th column. Instead of that if you create a stacked canvas with say for 30 columns out of 50 then you need not scroll the complete form you can scroll the stacked canvas part so that the remaining 20 columns will remain constant.
  • Creating an overlay effect within a single window.
  • Displaying headers with constant information such as company name.
  • Creating a cascading or a revealing effect within a single window.
  • Displaying additional information.
  • Displaying information conditionally
  • Hiding information


The size of a stacked canvas is smaller than the window it is displayed in, because the stacked canvases are used to cover up only part of the content canvas in the window. If it is not smaller than the existing canvas, then the stacked canvas will completely obscure the content canvas, which defeats the purpose of having an alternate view. A stacked canvas will remain in view until it is explicitly dismissed or replaced by another canvas.


A common error that can occur is that stacked canvases can disappear at unfortunate times. This is usually related to the automatic re-ordering that Forms uses to display the active item. In this case, something that has to be displayed in order to show the active item has overlapped the stacked canvas and caused it to be pushed farther down in the stack of displayed canvases.

Stacked Canvas Specific Properties
How to Create a Stacked Canvas in the Object Navigator

  1. Click the Canvases node in the Object Navigator.
  2. Click the Create icon.
  3. A new canvas entry displays with a default name of CANVASXX.
  4. If the Property Palette is not already displayed, click the new canvas entry and select Tools—>Property Palette.
  5. Set the Canvas Type property to Stacked. Additionally, set the properties that are described in the above table according to your requirements.
Note: To convert an existing content canvas to a stacked canvas, change its Canvas Type property value from Content to Stacked. In order for the stacked canvas to display properly, make sure that its position in the stacking order places it in front of the content canvas assigned to the same window. The stacking order of canvases is defined by the sequence in which they appear in the Object Navigator.


How to Create a Stacked Canvas in the Layout Editor
  1. In the Object Navigator, double-click the object icon for the content canvas on which you wish to create a stacked canvas. Or Create Stacked Canvas First then import Stacked Canvas View port on Main Content Canvas through ViewStacked ViewImport.
The Layout Editor displays.
  1. Click the Stacked Canvas tool in the toolbar
  2. Adjust the stacked canvas view port according to requirement.

  1. Open the Property Palette of the stacked canvas. Set the canvas properties according to your requirements (described earlier in the lesson).








Create data block using the wizard ‘emp_table’ and design its layout on the Stack Canvas and inherit the subclass information for the block and items created.
    • Assign Block Property Subclass information as BLOCK
    • Assign Item Property Subclass information as required ex: Text ItemText-item





Adjust the viewport and the canvas as required in the stack canvas.



Displaying Stacked Canvases in the Layout Editor
You can display a stacked canvas as it sits over the content canvas in the Layout Editor. Check the display position of stacked canvases by doing the following:

  1. Select View—>Stacked Views in the Layout Editor. The Stacked/Tab Canvases dialog box is displayed, with a list of all the stacked canvases assigned to the same window as the current content canvas.
  2. Select the stacked canvases you want to display in the Layout Editor.
Note: [Control] + Click to clear a stacked canvas that was previously selected.


Now you can See the stack canvas on the content canvas.
To set the location where to display the stack canvas ON the content canvas Adjust the X and Y co-ordinate property of the stacked canvas or simply drag the stack canvas to the desired location.



Horizontal Scroll bar on Stacked Canvas: Stacked Canvas PropertyAssign “Yes” For Horizontal Scroll Bar.


3Developing Tabbed Canvas Form

Tab canvas is a special type of canvas that enables you to organize and display related information on separate tabs. Like stacked canvases, tab canvases are displayed on top of a content canvas.

A tab page is a sub object of a tab canvas. Each tab canvas is made up of one or more tab pages. A tab page displays a subset of the information in the entire tab canvas. Each tab page has a labeled tab that end users can click to access information on the page.

Each tab page occupies an equal amount of space on the tab canvas.

Uses and Benefits of Tab Canvases
• Create an overlay effect within a single window.
• Display large amounts of information on a single canvas.
• Hide information.
• Easily access required information by clicking the tab.


Creating Tabbed Canvas.
Create canvas and select canvas type as Tab from property palette.

  1. Set the Canvas Type property to Tab. Additionally, set the canvas properties according to your requirements.

Creating Tab pages for Tab Canvas
Select tab canvas in Object Navigator, create new tab pages by selecting Creating Icon from toolbar. A tab page displays in the Object Navigator, with a default name of PAGEXX
Change Tab Page Label Names to required names (N.V: Tab Canvas PropertyLabel

Displaying Items on Tabbed Canvas
Select Data block items from Object Navigator and change the selected item canvas property to Tab Canvas, Then select Tab page accordingly to requirement.


Select all the EMP Fields and create a layout for EMPDETAILS Page.


Create a Tab Canvas in the Layout Editor
  1. In the Object Navigator, double-click the object icon for the content canvas on which you want to create a tab canvas. The Layout Editor displays.
  2. Click the Tab Canvas tool in the toolbar.
  3. Click and drag the mouse in the canvas where you want to position the tab canvas.
  4. Form Builder creates a tab canvas with two tab pages by default.
  1. Open the Property Palette of the tab canvas. Set the canvas properties according to your requirements.

Displaying Tab Canvases in the Layout Editor
You can display a Tab canvas as it sits over the content canvas in the Layout Editor. Check the display position of Tab canvases by doing the following:

  1. Select View—>Stacked Views in the Layout Editor.
  1. The Stacked/Tab Canvases dialog box is displayed, with a list of all the stacked canvases assigned to the same window as the current content canvas.
  2. Select the Tab canvases you want to display in the Layout Editor.

Note: [Control] + Click to clear a Tab canvas that was previously selected.

Now you can see the tab canvas on the content canvas.

To set the location where to display the Tab canvas on the content canvas, Adjust the X and Y co-ordinate property of the Tab canvas or simply drag the Tab canvas to the desired location.

4Implementing Master Detail Form

4.1Master / Detail Relationships

A master-detail relationship is an association between two base table blocks:
    • A master block
    • A detail block.
It reflects a primary key to foreign key relationship between the tables on which the blocks are based.

  • The master-detail relationship automatically does the following:
  • Ensures that the detail block displays only those records that are associated with the current (master) record in the master block
  • Coordinates querying between the two blocks
  • The master block is related to the detail block through the join condition.
  • The join condition establishes the primary key item(s) in the master block and the foreign key item(s) in the detail block:
e.g.. deptblk.deptno = empblk.deptno
  • You can define a master-detail relationship at any time during the form development process. You define a master-detail relationship between blocks by creating and setting the properties of a relation object.
  • A relation is a logical object that specifies the relationship between one master block and one corresponding detail block. In the Object Navigator, the relation object appears under the block that is the master block in the relation.
  • When you create a relation, Oracle Forms generates the triggers and PL/SQL procedures required to enforce coordination between the master and detail blocks. The actual code that Oracle Forms generates depends on how the properties of the relation are set. .
Creating Master/Detail Relation
  • There are two ways to create a relation in Oracle Forms:
  • Create the relation in the New Block window at the same time you create the detail block for that relation.
  • In the Object Navigator, insert a relation object under the relations node for the appropriate master block.
  • Once you create a relation between two blocks, you can change its properties at any time. When you change the Master Deletes or Coordination properties of an existing relation, Oracle Forms adds, removes, or edits triggers as required
Setting properties that effect co-ordination
  • Master Deletes
  • Coordination
  • Prevent Master-less Operation

  • When you create a relation, you need to decide how Oracle Forms should manage coordination between the master and detail blocks.

Specifically, you need to answer the following questions:
  • Should operators be able to delete a master record if there are associated detail records present?
  • If operators can delete a master record, should Oracle Forms delete the associated detail records also?
  • If an operator makes a new record in the master block the current record, should Oracle Forms fetch the associated detail records immediately, or wait until the operator navigates to the detail block?

Master Deletes Properties:
    • Non-Isolated
    • Isolated
    • Cascading
Coordination Properties:
    • Immediate
    • Deferred with Auto-Query
    • Deferred with No Auto-query

Master Deletes Properties:
Non-Isolated: The default setting. Prevents the deletion of a master record if associated detail records exist in the database.
Isolated: Allows the master record to be deleted and does not affect the associated detail records in the database.
Cascading: Allows the master record to be deleted and automatically deletes any associated detail records from the base table at commit time. When relations are nested to several levels, only records in the immediate detail block are deleted.
Coordination Properties:
Immediate: (Deferred False, Auto-Query False) The default setting. When a coordination-causing event occurs, the detail records are fetched immediately.
Deferred with Auto-Query: (Deferred True, Auto-Query True)When a coordination-causing event occurs, Oracle Forms defers fetching the associated detail records until the operator navigates to the detail block.
Deferred with No Auto-query: (Deferred True, Auto-Query False) When a coordination-causing event occurs, Oracle Forms does not automatically fetch the detail records. To fetch the detail records, the operator must navigate to the detail block and explicitly execute a query.







Master Detail Triggers
  • ON-CLEAR-DETAILS
  • ON-POPULATE-DETAILS
  • ON-CHECK-DELETE-MASTER
Master Detail Triggers
  • CHECK_PACKAGE_FAILURE
  • CLEAR_ALL_MASTER_DETAILS
  • QUERY_MASTER_DETAILS
ON-CLEAR-DETAILS
Fires when a coordination-causing event occurs in a block that is a master block in a Master/Detail relation. A coordination-causing event is any event that makes a different record the current record in the master block.

ON-POPULATE-DETAILS
Forms Developer creates this trigger automatically when a Master/Detail relation is defined. It fires when Forms Developer would normally need to populate the detail block in a Master/Detail relation

ON-CHECK-DELETE-MASTER
Forms Developer creates this trigger automatically when you define a master/detail relation and set the Delete Record Behavior property to Non-Isolated. It fires when there is an attempt to delete a record in the master block of a master/detail relation
.


5Implementing DML Operations

Simple View/ Single table based form default will allow all DML operations,if we are using complex views, then we required manually enable DML operations on form.
Enabling DML Operations on Complex Views
  1. Create Block Level ON-LOCK trigger, it will lock the record when we do update, use NULL if you don’t have specific locking mechanism.
  2. Create/enable block level triggers PRE-INSERT, PRE-UPDATE and PRE_DELETE, These triggers will update the WHO COLUMNS in the table.
  3. Create WHO column in the complex view block as Non Data Base Items, WHO columns are CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN and LAST_UPDATE_BY
  4. Populate information in WHO columns use API APP_STANDARD.SET_WHO in block level triggers PRE-INSERT, PRE-UPDATE and PRE-DELETE.
  5. Create BLOCK Level triggers ON-INSERT, ON-UPDATE and ON-DELETE.
  6. Write Package/Procedures in Program Unit for manually inserting/update BLOCK Level items information in the DB tables.
  7. Call program unit written procedure from ON-INSERT, ON-UPDATE and ON-DELETE.





 


Set Sub Class Information as Text-Items and select Canvas.
EMPNO should be a database item. Primary Key property should be ‘Yes’, Insert Allowed should be ‘NO’ and Update Allowed should be ‘No’.


Select the Triggers node under the EMP_DEPT data-block click Create and add 4 Triggers.
1. ON-LOCK:
In the PL/SQL Editor, enter the following code, then click Compile:
NULL;
2. PRE-INSERT:
In the PL/SQL Editor, enter the following code, then click Compile:
FND_STANDARD.SET_WHO;
3. PRE-UPDATE:
In the PL/SQL Editor, enter the following code, then click Compile:
FND_STANDARD.SET_WHO;
4. ON-INSERT:
In the PL/SQL Editor, enter the following code, then click Compile:
DECLARE
V_CNT NUMBER:=0;
BEGIN
SELECT COUNT(*)
INTO V_CNT
FROM DEPT
WHERE DEPTNO=:EMP_DEPT.DEPTNO;
IF V_CNT=0 THEN
DML_OPERATIONS.INSERT_INTO_DEPT;
END IF;
DML_OPERATIONS.INSERT_INTO_EMP;
END;
5. WHEN-NEW-RECORD-INSTANCE
In the PL/SQL Editor, enter the following code, then click Compile:
APP_ITEM_PROPERTY.SET_PROPERTY('EMP_DEPT.EMPNO',INSERT_ALLOWED,PROPERTY_FALSE);
:EMP_DEPT.HIREDATE:=SYSDATE;








Program Unit:
  1. Create Package Spec:
  1. Select the Program Unit node under the DML_OPERATIONS form,
click Create , and Enter New Program Unit Name and select package Spec.
  1. In the PL/SQL Editor, enter the following code, then click Compile:
PACKAGE DML_OPERATIONS IS
procedure insert_into_emp ;
procedure insert_into_dept;
END;
  1. Create Package Body:

  1. Select the Program Unit node under the DML_OPERATIONS form,
  2. click Create , and Enter New Program Unit Name and select package Body.


  1. In the PL/SQL Editor, enter the following code, then click Compile:
PACKAGE BODY DML_OPERATIONS IS
procedure insert_into_emp is
--v_empno number ;
begin
select empno_s.nextval into :emp_dept.empno from dual;
insert into emp
(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values(:emp_dept.empno,
:EMP_DEPT.ENAME,
:EMP_DEPT.JOB,
:EMP_DEPT.MGR,
:EMP_DEPT.HIREDATE,
:EMP_DEPT.SAL,
:EMP_DEPT.COMM,
:EMP_DEPT.DEPTNO,
:EMP_DEPT.CREATED_BY,
:EMP_DEPT.CREATION_DATE,
:EMP_DEPT.LAST_UPDATED_BY,
:EMP_DEPT.LAST_UPDATE_DATE,
:EMP_DEPT.LAST_UPDATE_LOGIN);
exception
when others then
fnd_message.debug(sqlerrm||sqlcode);
raise form_trigger_failure;
end insert_into_emp;
procedure insert_into_dept is
begin
insert into dept(DEPTNO,
DNAME,
LOC,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES(:EMP_DEPT.DEPTNO,
:EMP_DEPT.DNAME,
:EMP_DEPT.LOC,
:EMP_DEPT.CREATED_BY,
:EMP_DEPT.CREATION_DATE,
:EMP_DEPT.LAST_UPDATED_BY,
:EMP_DEPT.LAST_UPDATE_DATE,
:EMP_DEPT.LAST_UPDATE_LOGIN);
EXCEPTION
when others then
fnd_message.debug(sqlerrm||sqlcode);
raise form_trigger_failure;
end insert_into_dept;

END;







6Implementing QueryFind Behaviour In Forms


6.1Enabling query behavior

Oracle Applications allows the Query Find behavior to be implemented in the forms. There are two implementations for the Query Find, using Find windows and using Row-Lov’s.

6.1.1Implementing Query Find window

The Query Find window would be based on a result block, where the customization demands the find window. The following steps would describe how to implement the Query find window.
        1. Copy the QUERY_FIND object group from APPSTAND, please note that the object group has to copied and not referenced. The QUERY_FIND comes with a QF block, canvas and window.
          NOTE: The Query Find block has the following buttons FIND, NEW, CLEAR. The Find button has the pre-written code for Finding the records and similarly New and Clear Buttons.
        2. Rename the block, canvas and window to an appropriate name.
        3. Edit the WHEN-BUTTON-PRESSED trigger in the NEW button, mention the result block name in the APP_FIND.NEW call.
E.g: APP_FIND.NEW (‘MYBLOCK’);
CHECK: The New button itself should be deleted if the block for which the query behavior required is a read-only block (The block does not allow the Insert operation).
There might be certain customizations where the Find window does not require a NEW button also.
        1. Edit the WHEN-BUTTON-PRESSED trigger in the FIND button, mention the result block name in the APP_FIND.FIND call.
E.g: APP_FIND.FIND (‘MYBLOCK’);
        1. Edit the KEY-NXTBLK trigger of the Find block, mention the result block name in the APP_FIND.FIND call.
E.g: APP_FIND.FIND (‘MYBLOCK’);
        1. Modify the Block navigation properties of the Find block appropriately to the result block. Typically the previous navigation block would be the result block.
        2. Create the necessary items in the Find block and the respective Item level validations and the List of Values for the Items added.
          CHECK: The Items that have been created should not be database items and the required property must be set to NO.
        3. Code the PRE-QUERY trigger for the result block, the logic would be to copy the values from the Find block items to the result block items if the Find button is pressed. The parameter Q_query_find is checked against the value ‘TRUE’ (The parameter Q_query_find is set to the value ‘TRUE’ when the APP_FIND.QUERY_FIND call is made) and then the values are copied to the result block items.
E.g: IF :parameter.G_query_find = ’TRUE’ THEN
COPY ( name_in (‘MYFINDBLK.MYFINDITEM’), ’MYRESULTBLK.RESULTITEM’);
:parameter.G_query_find := ’FALSE’;
END IF;
TIP: Use the std. QF call APP_FIND.QUERY_RANGE to query on a ranges of numbers, dates and characters.
        1. Create a user defined trigger QUERY_FIND in the results block, the trigger would have the QF call to find the
E.g: APP_FIND.QUERY_FIND ( ‘MYRESULTBLK’, ‘MYFINDBLK’, ‘MYFINDWINDOW’);
TIP: To enable the Query Find window to appear first when the form opens, call the trigger QUERY_FIND in the WHEN-NEW-BLOCK-INSTANCE trigger.
E.g: execute_trigger (‘QUERY_FIND’ );

6.1.2Implementing ROW-LOV

The ROW-LOV implementation would also be based on the result block, the following steps describe the same.
  1. Create a parameter to hold the value of the primary key of the result block.
E.g. create a parameter P_PRIMARY_KEY in the form
  1. Create a LOV (E.g. MYROWLOV) for the identifying the desired record. The LOV should contain the description columns where the user would want to filter the records and the primary key field of the result block as a column.
    NOTE: The return value of the LOV should be the parameter created in the previous step.
    TIP: Enabling the long list in the LOV for the ROW-LOV is always recommended.
  2. Code a PRE-QUERY trigger in the result block, and copy the value of the parameter to the result blocks primary key item.
E.g: IF :parameter.G_query_find = ’TRUE’ THEN
COPY ( name_in (‘PARAMETER.P_PRIMARY_KEY’), ’MYRESULTBLK.PRIMARYKEY’);
:parameter.G_query_find := ’FALSE’;
END IF;
  1. Create a user defined trigger EXECUTE_QUERY in the results block, code a call to the QF call.
E.g: APP_FIND.QUERY_FIND ( ‘MYROWLOV’ );

7Coding Summary/Detail (Working with Multi Blocks Canvas) Windows

Most of the Oracle Application forms would typically have a Query Find window which results in a Summary block window and by selecting a record in the Summary window, the user can navigate to the detailed window where the user can see more information. The combination of Summary window and the details window can be implemented in a single block, hence the name combination block.
The following steps would explain how to implement Combination block in the customized forms.
            1. Open the Template form and make the necessary modifications in the Template form.
            2. Identify the Data entity (Table/View) which needs the Summary/Details window.
            3. Create two Windows and two canvases which would hold the Summary and the Detailed Items.
            4. Create the required items in the block. In this step there would certain items that would be displayed in the Summary window as well as the detailed window. Identify these items and create Mirror items for the same (Naming convention that is recommended is <item_name>_MIR). Set the Database item property for the Mirror items to Yes and also set the Synchronize with Item property to the item that is copied from.
            5. Set the number of items displayed property of the block to that of the desired number that needs to be displayed in the Summary window. For all the items that need to be displayed in the detailed window, set the items displayed to one.
            6. Set the navigational property of the Summary items and detailed items properly to the desired first item and the desired last item respectively.
            7. In the Last item of the summary block, code a KEY-NEXT-ITEM trigger and navigate to the next record by making a call to next_record built-in.
            8. In the First item of the summary block, code a KEY-PREV-ITEM trigger and navigate to the first record by making a call to APP_COMBO.KEY_PREV_ITEM standard procedure call.
            9. Create a parameter called <block_name>_RECORD_COUNT in the form. The data type of the parameter must be number and the default value of the parameter must be 2 if the summary window is to be displayed first or 1 if the detailed window is to be displayed first.
            10. Create a WHEN-NEW-ITEM-INSTANCE trigger in the block level and add the following code in the trigger.
:PARAMETER.<block_name>_RECORD_COUNT := GET_ITEM_PROPERTY (
:CURSOR.SYSTEM_ITEM, RECORD_DISPLAYED);
            1. Create a Switcher Item with the name as SWITCHER in the block and assign a property class called SWITCHER and place the same at the TOOLBAR canvas. And also this item must be the last item in the block and X position and Y position must be 0. Create a WNII trigger for the switcher item and add the following code in the same.
IF (name_in(‘PARAMETER.<block_name>_RECORD_COUNT’) > 1)
THEN
GO_ITEM (‘<first item of summary window>’);
ELSE
APP_WINDOW.SET_WINDOW_POSITION ( ‘<detailed window>’,
‘OVERLAP’,
‘<summary window>’);
GO_ITEM (‘first item of detailed window’);
END IF;
            1. Create a SUMMARY_DETAIL trigger at the combination block with the following piece of the code.
IF GET_ITEM_PROPERTY(:SYSTEM.CURSOR_ITEM,
RECORDS_DISPLAYED) > 1
THEN
:PARAMETER.<block name>_RECORD_COUNT := 1;
ELSE
:PARAMETER.<block name>_RECORD_COUNT := 2;
END IF;
GO_ITEM(’<block name>.Switcher’);
            1. Add a Drill down indicator (Sub class: DRILLDOWN_RECORD_INDICATOR) in the combination block and in the WNII trigger code the following piece of code.
execute_trigger(‘SUMMARY_DETAIL’);
            1. Add a PRE-BLOCK trigger in the block and in the trigger enable the menu ‘SUMMARY_DETAIL’ using the APP_SPECIAL.ENABLE standard call. Similarly disable the menu in the POST-BLOCK trigger of the block.

8Table and Column Registration


Custom application tables are registered using a PL/SQL routine in the AD_DD package.

Flexfields and Oracle Alert are the only features or products that depend on this information.
Therefore tables (and all of their columns) that will be used with flexfields or Oracle Alert are only needed to be registered.

AD_DD API is used to delete the registrations of tables and columns from Oracle Application Object
Library tables.

If the table is altered later, then revised or new calls to the table registration routines are to be included.
To alter a registration first delete the registration, then reregister the table or column. First delete the column registration and then the table registration.

The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. No need to register views.

Procedures in the AD_DD Package:

procedure register_table (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_tab_type in varchar2,
p_next_extent in number default 512,
p_pct_free in number default 10,
p_pct_used in number default 70);

procedure register_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2,
p_col_seq in number,
p_col_type in varchar2,
p_col_width in number,
p_nullable in varchar2,
p_translate in varchar2,
p_precision in number default null,
p_scale in number default null);

procedure delete_table (p_appl_short_name in varchar2,
p_tab_name in varchar2);

procedure delete_column (p_appl_short_name in varchar2,
p_tab_name in varchar2,
p_col_name in varchar2);


p_appl_short_name: The application short name of the application that owns the
table (usually your custom application).

p_tab_name: The name of the table (in uppercase letters).

p_tab_type: There are four types of table types
  1. Transaction Data – almost all application tables
  2. Seed Data – used only by Oracle Applications products like FND tables
  3. Interim – They are the temporary tables, which are used for validation
  4. Special Flex field data

Use ’T’ if it is a transaction table (almost all application tables), or ’S’ for a”seed data” table
(used only by Oracle Applications products like FND tables).

p_pct_free: The percentage of space in each of the table’s blocks reserved for future updates to the table
(1–99). The sum of p_pct_free and p_pct_used must be less than 100.

p_pct_used: Minimum percentage of used space in each datablock of the table (1–99). The sum of p_pct_free
and p_pct_used must be less than 100.

p_col_name: The name of the column (in uppercase letters).

p_col_seq: The sequence number of the column in the table (the order in which the column
appears in the table definition).

p_col_type: The column type (‘NUMBER’, ’VARCHAR2’,’DATE’, etc.).

p_col_width: The column size (a number). Use 9 for DATE columns, 38 for NUMBER columns
(Unless it has a specific width).

p_nullable: Use ’N’ if the column is mandatory or ’Y’ if the column allows null values.

p_translate: Use ’Y’ if the column values will be translated for an Oracle Applications product release (used only
by Oracle Applications products) or ’N’ if the values are not translated (most application columns).

p_next_extent: The next extent size, in kilobytes. Do not include the ’K’.

p_precision: The total number of digits in a number.

p_scale: The number of digits to the right of the decimal point in a number.

Example :

Registering the table emp_ps to custom application XXCUST.



After running the above steps, it should be committed.
These details will be saved in FND_TABLES and FND_COLUMNS.
In the front end, these details will be seen in
Application Developer Responsibility
Application Database Table






Query the form with Table name. Here it is EMP_PS. Details of the registered table and registered columns will be seen here.

9Flex fields

A Flexfield is a field made up of segments. The end user can use the segments to store the required values in the segments and name the segments according to his/her need. The following sections would not cover the vastness of the topic, but the basic aim of these sections is to help the Developer customize a DFF or KFF in the custom form.
For more concepts and usage of Flexfields please refer to the Oracle Applications Developers Guide and the Oracle Applications Flexfield user guides.

9.1Benefits of Flex fields

  • Flex fields provide you with the features you need to satisfy the following business needs:
  • Customize your applications to conform to your current business practice for accounting codes, product codes, and other codes.
  • Customize your applications to capture data that would not otherwise be tracked by your application.
  • Have ”intelligent fields” that are fields comprised of one or more segments, where each segment has both a value and a meaning.
  • Rely upon your application to validate the values and the combination of values that you enter in intelligent fields.
  • Have the structure of an intelligent field change depending on data in your form or application data.
  • Customize data fields to your meet your business needs without programming

9.1.1Attaching a DFF to Oracle Application forms

A descriptive flexfield or shortly DFF is a flexfield that is used an “expansion space” in the customized form. The objective of this section is to aid the Oracle Application developer to add a DFF to the form.
The following steps would describe the steps that need to be followed.
  1. Identify the customization where a DFF is needed for future needs. Also identify the underlying table/view upon which the customization is based on.
    CHECK: The table/view where the DFF would be placed should have the 15 ATTRIBUTE columns and an ATTRIBUTE_CATEGORY column.
    TIP: The ATTRIBUTE columns form the segments of the DFF and the ATTRIBUTE_CATEGORY would be the reference column for the form.
    TIP: The DFF segments and the reference column can be named other than ATTRIBUTE and ATTRIBUTE_CATEGORY also.
  2. Register the table using the AD_DD.REGISTER_TABLE and AD_DD.REGISTER_COLUMN table registration API’s.
    NOTE: A table registration generation procedure is provided in the APPENDIX B.
    NOTE: It is recommended that even if the form is based on a View, the main table of the view is registered in AOL.
  3. Register the flexfield using the Flexfield registration window (Oracle Applications Application Developer Flexfields Register). In the form, enter the Application name, Name and Title of the DFF, Table application where the table is registered and the table name. In the Structure column field select the ATTRIBUTE_CATEGORY column. Also in the Columns window ensure that all the DFF segments are enabled for the DFF.
  4. Code the DFF in the form. Create the hidden fields (DFF segments) in the block. Set the query field size to 255 for the hidden fields.
    CHECK: Ensure the Item/block handlers like ON-UPDATE triggers also handle the newly created items.
  5. Create the displayed field with the query length as 2000 and place it at the canvas at the required position. Also attach the ENABLE_LIST_LAMP LOV for the field.
  6. In the WHEN-NEW-FORM-INSTANCE trigger call the Flexfield definition procedure. The sample call is given below.
FND_DESCR_FLEX.DEFINE(
BLOCK=>’block_name’,
FIELD=>’field_name’,
APPL_SHORT_NAME=>’DFF application_short_name’,
DESC_FLEX_NAME=>’descriptive flexfield_name’
);
  1. Invoke the standard flexfield calls in the form. The following table provides all the calls that need to be coded in the form.
    Trigger Level
    Trigger
    Standard procedure calls
    Form
    PRE-QUERY
    Fnd_flex.event(‘PRE-QUERY’);
    Form
    POST-QUERY
    Fnd_flex.event(‘POST-QUERY’);
    Form
    PRE- INSERT
    Fnd_flex.event(‘PRE-INSERT’);
    Form
    PRE-UPDATE
    Fnd_flex.event(‘PRE-UPDATE’);
    Form
    WHEN-VALIDATE-RECORD
    Fnd_flex.event(‘WHEN-VALIDATE-RECORD’);
    Form
    WHEN-NEW-ITEM-INSTANCE
    Fnd_flex.event(‘WHEN-NEW-ITEM-INSTANCE’);
    Form
    WHEN-VALIDATE-ITEM
    Fnd_flex.event(‘WHEN-VALIDATE-ITEM’);
  2. The Oracle Application developer or the End user is free to define any new flexfield in the form. The navigation path is: Oracle Applications Application Developer Flexfields Segments. Please refer the developers guide or the Flexfields guide for further information.

9.2What is Key Flex Field? (KFF)


A field that business can use to store information represented as ‘Codes’. It serves as an intelligent primary key, where each segment contains meaningful information

9.2.1When to use a KFF

  • Used to uniquely identify an application entity with an intelligent key, where the key can be multipart and each part can have some meaning.
  • Use KFF that Oracle Applications provides to integrate your applications seamlessly with Oracle Applications.

9.2.2Referencing a KFF to Oracle Application forms

A key flexfield or shortly KFF is a flexfield that is used an “expansion space” in the customized form. The objective of this section is to aid the Oracle Application developer to add a KFF to the form.
The following steps would describe the steps that need to be followed.
  1. Create the hidden fields (ID Segment field) in the block. Set the query field size to 2000 for the hidden field (ID Segment field).
  2. Create the displayed field with the query length as 2000 and place it at the canvas at the required position. Also attach the ENABLE_LIST_LAMP LOV for the field. Set the Validate from LOV property for the item as No.
  3. In the WHEN-NEW-FORM-INSTANCE trigger call the Flexfield definition procedure. The sample call is given below.
FND_KEY_FLEX.DEFINE(
BLOCK=>’block_name’,
FIELD=>’concatenated_segments_field_name’,
ID=>’Unique_ID_field’,
DATA_FIELD=>’concatenated_hidden_IDs_field’,
APPL_SHORT_NAME=>’application_short_name’,
CODE=>’key_flexfield_code’,
NUM=>’structure_number’
);

  1. Invoke the standard flexfield calls in the form. The following table provides all the calls that need to be coded in the form.
Trigger Level
Trigger
Standard procedure calls
Form
PRE-QUERY
Fnd_flex.event(‘PRE-QUERY’);
Form
POST-QUERY
Fnd_flex.event(‘POST-QUERY’);
Form
PRE- INSERT
Fnd_flex.event(‘PRE-INSERT’);
Form
PRE-UPDATE
Fnd_flex.event(‘PRE-UPDATE’);
Form
WHEN-VALIDATE-RECORD
Fnd_flex.event(‘WHEN-VALIDATE-RECORD’);
Form
WHEN-NEW-ITEM-INSTANCE
Fnd_flex.event(‘WHEN-NEW-ITEM-INSTANCE’);
Form
WHEN-VALIDATE-ITEM
Fnd_flex.event(‘WHEN-VALIDATE-ITEM’);


Examples of KFF

Scenario to Implement Key Flex Field:
Suppose there is a requirement to capture Locations (Country, State, City and Street) details for all the employees as shown in the following screen


Steps to Implement Location Key Flex Field in Forms:
1. Create the following table through which we are going to create the flex field.

Name Null? Type
----------------------------------------- -------- ---------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(40)
CCID NUMBER(20)
2. Register the table using the AD_DD.Register_Table procedure
3. Register the column of the table using the AD_DD.Register_Column
procedure.
4. Fire a commit statement after Table/Column registration.
5 Using TEMPLATE.fmb, design the form with the Data Block as shown
in the following screen shot
Here CCID is a reference field between custom (your) table and flex field table.

KFF (Non-Database Item) is a field to which you want to display the generated Key Flex Field value
DESCR (Non-Database Item) is a field to display the detailed description of the Flex Field values stored in KFF item.
6. Define the corresponding flexfield in the WHEN-NEW-FORM-INSTANCE trigger of your form as shown below.
Fnd_Key_Flex.Define(Block => 'EMP_DFF',
Field => 'KFF',
Description=>'DESCR',
ID => 'CCID',
Appl_Short_Name => 'OFA',
Code => 'LOC#',
Num =>101);
Application_Short_Name can be found using the Application Developer Responsibility ApplicationRegister
Code can be found using the Application Developer ->Flex FieldKey Segments,
Go to Help DiagnosticsExamine will open the form as shown in the following screen shot


Num can be found using the Application Developer ->Flex FieldKey Segments.
Go to Help DiagnosticsExamine will open the form as shown in the following screen shot
Open Segments form using the Application Developer ->Flex FieldKey Segments
7. Invoke Flex Field Functionality by adding following 7 Triggers at Block Level.
PRE–QUERY
FND_FLEX.EVENT(’PRE–QUERY’);

POST–QUERY
FND_FLEX.EVENT(’POST–QUERY’);

PRE–INSERT
FND_FLEX.EVENT(’PRE–INSERT’);

PRE–UPDATE
FND_FLEX.EVENT(’PRE–UPDATE’);

WHEN–VALIDATE– RECORD
FND_FLEX.EVENT(’WHEN–VALIDATE–RECORD’);

WHEN–NEW–ITEM–INSTANCE
FND_FLEX.EVENT(’WHEN–NEW–ITEM–INSTANCE’);

WHEN–VALIDATE–ITEM
FND_FLEX.EVENT(’WHEN–VALIDATE–ITEM’);
8. Register and Run your form.

10Enabling the Folders feature in Oracle Applications Forms

Customizing forms with folder feature or Folder forms as it is called provides the end user to “customize” the fields at a desired position and at desired width. To enable this feature the Oracle Application developer needs to follow these steps
  1. Customize the form from the TEMPLATE file and create the required windows and canvases.
    NOTE: Only the items (fields) placed in a stacked canvas can be ‘movable’, hence place all such items in a stacked canvas.
  2. Attach the library APPFLDR.pll to customization, while attaching the Forms builder would ask whether to remove a non-portable directory for the form library, select yes.
  3. Also open the APPSTAND.fmb in the forms builder along with the file and make a reference to the STANDARD_FOLDER object group available in the APPSTAND form. (Drag and drop the STANDARD_FOLDER object group from the APPSTAND.fmb to customized form’s object group).
    NOTE: In this step just reference the object group, do not copy. (i.e. in the Popup select reference and do not select copy).
  4. Identify the block that needs to be folder enabled (say CUSTOM_BLOCK). Make necessary changes in the layout.
    CHECK: No headings “Prompts” are required for the columns.
  5. Create a “Prompt” block (say CUSTOM_BLOCK_PROMPT) to hold the column headings and the standard folder items and add the following standard items in the Prompt block.
    Field 
    Class 
    Note
    FOLDER_OPEN
    FOLDER_OPEN 
    To be placed on the left top corner of the content canvas
    FOLDER_TITLE
    DYNAMIC_TITLE
    To be placed on the left top corner of the content canvas next to the item FOLDER_OPEN
    FOLDER_DUMMY
    FOLDER_DUMMY
    -
    ORDER_BY1 
    FOLDER_ORDERBY
    Place beneath all fields in the content canvas
    ORDER_BY2
    FOLDER_ORDERBY
    Place beneath all fields in the stacked canvas
  6. Add all Prompt fields 'to be displayed' in the Prompt block
    TIP: The developer can copy and paste the fields from the main block to Prompt block and change the class property of all these fields  to “DYNAMIC_PROMPT”.
    CHECK: For each Prompt field set the Initial value properties to the default column heading required for the corresponding field.
  7. Add the following code in the triggers in form as shown in the table.

Trigger Type
Trigger Name
Code
Form Level
FOLDER_ACTION
app_folder.event (
:global.folder_action
);
Form Level
FOLDER_RETURN_ACTION
IF (:global.folder_action
= 'SHOW-FIELD')
THEN
app_item_property.set_property(
'<block_name>' || '.' ||
NAME_IN('GLOBAL.
FOLDER_FIELD'),
ALTERABLE,
PROPERTY_ON );
END IF;
Form Level
WHEN-NEW-FORM_INSTANCE
app_folder.define_folder_block (
'<FOLDER_OBJECT_NAME>',
'<F_BLOCK>', -- folder block 
'<P_BLOCK>, --  prompt block 
'<STACKED_CANVAS>',
'<FOLDER_WINDOW>'
); 
app_folder.event(
'WHEN-NEW-BLOCK-INSTANCE'
); 
Block Level Of MY_BLOCK
PRE-QUERY
App_folder.event('PRE-QUERY');
Block Level Of MY_BLOCK
KEY-NEXT-ITEM
App_folder.event('KEY-NEXT-ITEM'); 
Block Level Of MY_BLOCK
KEY-EXEQRY
App_folder.event('KEY-EXEQRY'); 
Block Level Of MY_BLOCK
WHEN-NEW-BLOCK-INSTANCE'
app_folder.event(
'WHEN-NEW-BLOCK-INSTANCE'
);
Block Level Of MY_BLOCK
KEY-PREV-ITEM
App_folder.event('KEY-PREV-ITEM');
Block Level Of MY_BLOCK
POST-BLOCK
App_folder.event('POST-BLOCK');
Block Level Of MY_BLOCK
PRE-BLOCK
App_folder.event('PRE-BLOCK');
  1. There is a trigger called FOLDER_RETURN_ACTION in the template form. The Oracle application developer and the Oracle application designer can code their custom code in the trigger based on the actions done by the End user who uses the Customized form. The following actions are supported in folder form.
ADD-FIELD, ALLOW-INCLUDE-WHERE-CLAUSE, CONFIRM-AUTOQUERY, AUTOQUERY, CONFIRM-HIDE-FIELD, DELETE-FOLDER, HIDE-FIELD, NEW-CONTEXT, NEXT-BLOCK, OPEN-FOLDER, RESET-WHERE-CLAUSE, SAVE-FOLDER, SET-WHERE-CLAUSE, SHOW-FIELD and VIEW-SIZE.
TIP: The folder items can be verified using the call “APP_FOLDER.EVENT(‘VERIFY’)”, essentially this verifies the fields of the Folder block and the prompt block are properly placed in the layout.
TIP: The Oracle Application developer can disable the some folder functionality like Hide field, show field etc. This can be done by passing the disabled functions argument in the call
app_folder.define_folder_block(
object_name ,
folder_block_name ,
prompt_block_name ,
folder_canvas_name ,
folder_window_name ,
<disabled_functions> ,
tab_canvas_name ,
fixed_canvas_name
);

The standard events that can be disabled are :
AUTOQUERY, PUBLIC, DEFAULT, ORDERBY, NEW, SAVE, DELETE, ADD, SHOW, CUT, HIDE, SWAP, MOVE, BIGGER, WIDEN, SMALLER, SHRINK, AUTOSIZE, PROMPT, CONTENTS, QUERY, RESET

Example:
app_folder.define_folder_block (
'<FOLDER_OBJECT_NAME>',
'<FOLDER_BLOCK>',
'<PROMPT_BLOCK>,
'<STACKED_CANVAS>',
'<FOLDER_WINDOW>',
'AUTOQUERY,SHRINK,AUTOSIZE,SHRINK'
); 

11Using the Attachment feature of Oracle Applications

The attachments feature enables users to link unstructured data, such as images, word processing documents, spreadsheets, or text to their application data. The following steps would help the developers to enable the attachments for the entities, so that the end users can attach the documents to the entities.
The attachments can be enabled at form level and form functions level. If the attachments were enabled at form level and the form function level, the form function attachments would override since the form function level is the lowest level. Please refer to Oracle Applications Developers guide for more details.
The attachments can be implemented in the following two ways:
  1. Defining a new attachment to a new custom form.
  2. Add an existing attachment of a standard form to the “custom/standard” form.

  1. Defining a new attachment to a new custom form.
The following three steps would help the developers to define an attachment to a new custom form.
    • Define your document entities using the Document Entities window
    • Define your document categories using the Document Categories window
    • Define your attachment functions using the Attachment Functions window
The following section would be more legible when explained with an example. A simple example would be attaching a note (A short text file) to the custom form CUSTFORM which is registered in the custom application MYAPPLICATION. The primary key of the table on which the CUSTFORM is based is PRIMKEY, the block where the field PRIMKEY is present is CUSTBLOCK.
  1. Define document entities
          1. Navigate to the Document entities window (Navigational path: Oracle Applications → Responsibility: Application Developer→ Attachments →Document Entities) and define the entities in the document entities block.
          2. Enter the Table name, a unique Entity name and Entity Id, an entity prompt and the application where the table is registered.
NOTE: The table is where the entity is stored and a primary key should always identify the entity record.
E.g: Enter the Table name, Entity name, Entity Id, Prompt and application as CUSTFORM, Custom Form Attachment Demo, DEM_CUSTFORM, Attachment Demo and MYAPPLICATION respectively.
  1. Define document categories
    1. Navigate to the Document categories window (Navigational path: Oracle Applications → Responsibility: Application Developer→ Attachments →Document Categories) and define the categories with the default data type in the document categories block.
E.g: Define the category as Attachment Demo – Short File, with the data type as “Short Text”.
    1. The assignments button would lead to the forms and functions that are enabled to the category, enable the new category to the ‘Form’ ‘CUSTFORM’.
      TIP: Alternatively the categories attached to the form can be defined in the “Attachment Functions” form. Please refer the next step for the same.
  1. Define attachment functions
    1. Navigate to the Document categories window (Navigational path: Oracle Applications → Responsibility: Application Developer→ Attachments →Attachment Functions) to define the attachments.
    2. Enter the Type, Name, a User name and the Session Context in the Attachment Functions window.
E.g: Enter the Type, Name and the User name as Form, CUSTFORM and Attachment Demo – Custom Form.
    1. Define the blocks in the custom form by navigating to the Block Declarations window. Key in the block name and the method of the attachment implementation. There are methods currently used, viz. Allow Control, Query Only.
E.g: Enter the Block name as CUSTBLOCK and the method as ‘Allow Control’.
    1. Choose Organization, Set of Books, Business Unit, or None, depending on how the form and its data (attached documents) are secured. Typically this feature is used for security of the documents attached. Specifying an Organization or SOB or a BU for the documents would secure the attachments for the corresponding Organization or SOB or BU.
    2. The context fields are used to display the Window Title of the attachment window. Typically the context field values are derived from the calling form with the syntax of block_name.field_name. If more than one context field is mentioned, the window title would be the first field value and the second field value separated by comma.
E.g: Enter one of the fields “NAMEFIELD” of the form CUSTFORM as the context field for the attachment. The syntax would be CUSTBLOCK.NAMEFIELD. Here NAMEFIELD is another field in the form.
    1. Enter the entities for the attachment in the Entities Declaration window by navigating to the same. The Entities Declaration window has the Entities, Display method which has two options Main window and the related window. Select any one of the options.
    2. Select the privileges that are required to for the block that can be anything like Insert, Update, and Delete etc.
    3. In the Primary key fields tab, provide the primary field. I.e. CUSTBLOCK.NAMEFIELD.
  1. Add an existing attachment of a standard form to the “custom/standard” form
  1. Navigate to the Document categories window (Navigational path: Oracle Applications → Responsibility: Application Developer→ Attachments →Attachment Functions) to define the attachments.
  2. Query for the Type, Name, a User name and the Session Context in the Attachment Functions window.
  3. Query the blocks for the standard form by navigating to the Block Declarations window. Query the block name and the method of the attachment implementation.
  4. Choose Organization, Set of Books, Business Unit, or None, depending on how the form and its data (attached documents) are secured. Typically this feature is used for security of the documents attached. Specifying an Organization or SOB or a BU for the documents would secure the attachments for the corresponding Organization or SOB or BU.
  5. The context fields are used to display the Window Title of the attachment window. Typically the context field values are derived from the calling form with the syntax of block_name.field_name. If more than one context field is mentioned, the window title would be the first field value and the second field value separated by comma.
  6. Enter the entities for the attachment in the Entities Declaration window by navigating to the same. The Entities Declaration window has the Entities, Display method which has two options Main window and the related window. Select any one of the options.
  7. Select the privileges that are required to for the block that can be anything like Insert, Update, and Delete etc.
  8. In the Primary key fields tab, provide the primary field.

12Using the basic features of Oracle Applications features

12.1Special Menus

The special menus in Oracle Applications are used for performing some special tasks. The special menus that appear in the Tools option of the Toolbar of the Oracle Applications can be used either at Form level, Block level or the individual item level. These are the basic steps to use the special menus
NOTE: The Oracle Application developer can use up to 45 Special Menus in Oracle Applications; the first 15 special menus (SPECIAL1 to SPECIAL15) will be available under tools menu. For using the other special menus (SPECIAL16 to SPECIAL45) please refer the step 5.
NOTE: To use checkboxes in the special menus please refer to step 6.
        1. In the PRE-FORM trigger, call the INSTANTIATE standard call to enable the special menu and to assign a label to the special menu.
APP_SPECIAL.INSTANTIATE ('SPECIALn', '&My Special Menu');
where in the first argument ‘n’ in the SPECIALn has ranges from 1 to 45 and the second argument is the label of the special menu.
TIP: Using a ‘&’ (ampersand) character in the second argument (hint) will enable the shortcut keys for the form.
        1. Write a user defined trigger named SPECIALn at the form level and code the corresponding logic behind the usage of the special menu.
        2. Enable the special menu by using the APP_SPECIAL.ENABLE call at the any level.
E.g.: At the PRE-BLOCK trigger of the corresponding block where the special menu is going to work, call the enable procedure.
APP_SPECIAL.ENABLE ('SPECIALn', PROPERTY_ON);
        1. Disable the special menu by using the APP_SPECIAL.DISABLE.
E.g.: At the POST-BLOCK trigger of the corresponding block where the special menu is going to work, call the disable procedure.
APP_SPECIAL.ENABLE ('SPECIALn', PROPERTY_OFF);
        1. For using the menus 16 to 45 we have to enable the SPECIAL_B and SPECIAL_C menus (actually it is the Toolbar Items) before we call the instantiate call for the special menus 16 to 45. Use the following call to do the same
APP_SPECIAL.INSTANTIATE ('SPECIAL_B', 'My Action Menu');
or
APP_SPECIAL.INSTANTIATE ('SPECIAL_C', 'My Report Menu');
NOTE: The SPECIAL_B and the SPECIAL_C menus appear on the Tool bar of Oracle Applications window.
TIP: Use of APP_SPECIAL.ENABLE(‘SPECIAL_X’, PROPERTY_ON) or APP_SPECIAL.ENABLE(‘SPECIAL_X’, PROPERTY_OFF) would enable/disable the whole menu under SPECIAL_X tool bar item.
        1. For using the checkbox in the special menus, call the instantiate procedure with the SPECIALn menu suffixed with ‘_CHECKBOX’.
E.g. APP_SPECIAL.INSTANTIATE ('SPECIALn_CHECKBOX', '&My Special Menu');
NOTE: Use the call APP_SPECIAL.SET_CHECKBOX (‘SPECIALn_CHECKBOX’, ‘TRUE’) or APP_SPECIAL. SET_CHECKBOX (‘SPECIALn_CHECKBOX’, ‘FALSE’) to set the value of the checkbox to checked or unchecked.
NOTE: Use the call APP_SPECIAL.GET_CHECKBOX (‘SPECIALn_CHECKBOX’) to get the value of the checkbox, the call returns a Boolean value.

12.2Popup Menus

With the introduction of popup menus (right click menus) in Oracle Forms 6, subsequently the feature was also incorporated in Oracle Applications 11i. The following steps would describe how to use the features of popup menus.
NOTE: To add a submenu under the popup menus, make appropriate changes in the popup menu POPUP menu, which comes along with the TEMPLATE.fmb
  1. In the PRE-POPUP-MENU trigger of the Item, instantiate the popup menu using the APP_POPUP.INSTANTIATE call.
E.g.: APP_POPUP.INSTANTIATE (‘POPUPn’, ’My Popup Menu’);
NOTE: Up to 15 popup menus for Oracle Applications can be customized.
  1. Write an Item level trigger POPUPn and code the appropriate handler logic for the popup menu.

12.3Coding the Calendar

The Calendar is a form object that allows the user to select the date and time values from a Calendar. Some special validation rules ensuring that only valid dates can be selected can also be coded.
The following tables shows the standard calendar procedures that can are used extensively.

S. No
Calendar Calls
Description
CALENDAR.SHOW(<first_date>)
The Calendar can be invoked by the call.
CALENDAR.SETUP
Disables the Dates in the calendar
CALENDAR.EVENT
The calendar event procedure, this is used internally by the Calendar procedures.

There are various needs to disable the dates of the calendar, so that the user doesn’t select certain dates. This is achieved by the SETUP calendar procedure. Given below are certain examples of the SETUP standard calls.
Examples:
      1. Start date and End date validation:
There are 2 fields Start date and End date in the form and the user cannot select a start date more than the End date and vice versa. The following code would enable the Developer to achieve that.

In the KEY-LISTVAL trigger of the Start date, code the following call followed by the calendar.show call.
calendar.setup('STARTDATE', NVL( <block_name.end_date>, TO_DATE ('31-DEC-4712', 'DD-MON-YYYY')), TO_DATE('31-DEC-4712', 'DD-MON-YYYY'));

In the KEY-LISTVAL trigger of the End date, code the following call followed by the calendar.show call.
calendar.setup('ENDDATE', TO_DATE('01-JAN-0001', 'DD-MON-YYYY'), NVL( <block_name.start_date>, TO_DATE('01-JAN-0001', 'DD-MON-YYYY')));

      1. Disable the weekends in the Calendar:
Use the call CALENDAR.SETUP (‘WEEKEND’) to suppress the Saturdays and the Sundays.
NOTE: The Weekend would disable only Saturdays and Sundays.
TIP: To disable the other dates (for Eg. Fridays & Saturdays) use the SQL statement argument in the CALENDAR.SETUP standard call.

      1. Advanced Option in Calendar.Setup:
The developer can use the SQL Statement in the CALENDAR.SETUP call & dynamically the dates can be disabled.
NOTE: The SQL statement should select 2 date columns with alias as LOW_DATE and HIGH_DATE respectively. This is mandatory.

Other features in Calendar:
  1. Title:
The developer can provide custom titles to the Calendar block. Use the call calendar.setup('TITLE',NULL,NULL,'<custom title>') to provide the title.
  1. Display Only calendar:
The developer can display only the calendar depriving the select feature in the calendar. Use the call calendar.setup ( ‘DISPLAY’) for the same.
  1. Display Time along with the calendar:
The user may wish to select the date along with the timestamp. Use the call calendar.setup('CHAR_DATETIME') before the Calendar.show call.
NOTE: If the Data type of date field in which the Calendar is attached is “Datetime” then automatically the Time part would be painted in the Calendar invoked.


13Using Multiple Form functions in Oracle Applications

Multiple form functions are a very potential feature in Oracle Applications where most of the customizations don’t completely utilize. Typical examples would be disabling buttons/menus for certain responsibilities/access rights for certain responsibilities etc. This section may provide only couple of examples only but the Oracle Applications designer and the developer is free to implement multiple form functions features to the maximum creativity.
Examples:
  1. Requirement: The Deletion property entry of the form is limited to certain responsibilities and not for all the responsibilities.
Implementation: For this requirement we can create an additional form function and assign this form function to the responsibilities who are allowed to delete the records in the form. The following steps would explain how to achieve the functionality.
    1. Create the custom form from the template form and customize the required blocks and windows.
    2. The default delete allowed property of the block (name of the block is say MYBLOCK) have to be set to No.
    3. Create a custom form function; say the name of the form function is MYFORM_DELETEALLOWED.
    4. In the PRE-FORM form level trigger of the form, add the following piece of code.
IF fnd_function.test (‘MYFORM_DELETEALLOWED’)
THEN
Set_block_property ( ‘MYBLOCK’, DELETE_ALLOWED,
PROPERTY_ON );
END IF;
    1. Assign the MYFORM_DELETEALLOWED property to the responsibilities who can delete the records from the custom form.
  1. Requirement: There are certain special menus on the custom form that needs to be disabled for certain responsibilities.
Implementation: For this requirement also we can create an additional form function and assign this form function to the responsibilities who are allowed access the menu. The following steps would explain how to achieve the functionality.
    1. Create the custom form from the template form and customize the required blocks and windows.
    2. Create a custom form function; say the name of the form function is MYFORM_MENU_DISABLED.
    3. In the PRE-FORM form level trigger of the form code, that enables the Special menu.
IF NOT fnd_function.test (‘MYFORM_MENU_DISABLED’)
THEN
APP_SPECIAL.INSTANTIATE (<..........>);
END IF;
    1. Assign the MYFORM_DELETEALLOWED property to the responsibilities that cannot access the special menu.

13.1Implementing Query only forms using Form functions

In most of the custom developments in Oracle Applications forms where there is a data entry, the end user would also prefer to have a view only form with the same layout and the Query source. In this case the developer need not duplicate the form & change the properties of the blocks in the form. The developer can create a Form function with the parameter “QUERY_ONLY” with the value passed as “Yes”.
The following steps explain how to implement the Query only forms.
  1. Create a form function with the parameter as ‘QUERY_ONLY=“Yes”’.
  2. In the PRE-FORM of the form, disable the INSERT, UPDATE and the DELETE property of the blocks. Also disable any associated buttons and the menus.
The following example code is shows how to disable the Properties of the block and the other associated items.

IF name_in ('PARAMETER.QUERY_ONLY') = 'Yes'
THEN
--
-- <Custom block name> Block
--
set_block_property ( '<Custom Block>',
INSERT_ALLOWED, PROPERTY_OFF );
set_block_property ('<Custom Block>',
UPDATE_ALLOWED, PROPERTY_OFF );
set_block_property ('<Custom Block>',
DELETE_ALLOWED, PROPERTY_OFF );
--
-- Disable Items
--
app_item_property.set_property ('<control block>.<items>',
DISPLAYED, PROPERTY_OFF);
END IF;

WARNING: The developer must ensure that all the Functions like Buttons/Menus and all the other navigational blocks must be disabled while coding the Query only forms.

14Using Zoom form feature in Oracle Applications

The Zoom form feature adds another dimension while customizations, the user get more flexibility of having/storing additional information other than standard Oracle Applications provide. The Zoom forms are essentially Extension forms over the standard Oracle forms, where the Oracle Application designer can store additional information about the Data Entity in focus in a customized extension table.
NOTE: The user can invoke the Zoom form from any Standard form from the menu option View Zoom.
There are some basic steps in Implementation of the Zoom forms in Oracle Applications, which are explained below.
  1. Develop the Zoom form from the Template form and customize the required form elements.
    CHECK: The extension table upon which the Zoom form is based should have a foreign key relationship with the primary key of the standard table.
    E.g: If we intend to have a ZOOM form on Items standard form, the extension table should have a foreign key viz. INVENTORY_ITEM_ID and ORGANIZATION_ID which are the primary keys of the table MTL_SYSTEM_ITEMS_B.
    NOTE: Also note that in the customized Zoom form we need to create the parameters as same as the INVENTORY_ITEM_ID and ORGANIZATION_ID to capture the foreign keys.
    TIP: The Zoom form need not be always based on the Primary Key of the standard table. The Oracle Applications designer can customize based on the users requirement on other columns of the standard tables.
  2. Modify the default WHERE clause of the Zoom form block to use parameter values as query criteria if they are not null. The following example code is provided for the same.
WHERE (:parameter.<parameter column> IS NULL OR
<zoom form block>.<block column>
LIKE :parameter.<parameter column>)
  1. In the WHEN-NEW-FORM-INSTANCE trigger of the form, check whether the parameter is passed, if the parameter is passed Navigate to the Zoom form block and find all the records in the block by writing a EXECUTE_QUERY. The following example code is provided for the same.
IF name_in(‘parameter.<parameter column>’) IS NULL
THEN
GO_BLOCK(‘<zoom form block>’);
EXECUTE_QUERY;
END IF
  1. Compile the ZOOM form and register the zoom form and create a form function for the zoom form.
  2. Make modifications in CUSTOM.pll:
    1. In the ZOOM_AVAILABLE procedure of the CUSTOM package enable the ZOOM menu and the ZOOM button. The following code would explain the same.
PROCEDURE zoom_available
IS
BEGIN
IF (form_name = ‘<standard form>’
AND block_name = ‘<block name>’)
THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
    1. In the EVENT procedure add the code to call the custom ZOOM function using the function call FND_FUNCTION.EXECUTE. The following code would explain the same.
PROCEDURE event (event_name IN VARCHAR2)
IS
BEGIN
IF (event_name = ‘ZOOM’)
THEN
IF (form_name = ‘<standard form>’
AND block_name = ‘<block name>’)
THEN
FND_FUNCTION.EXECUTE(
FUNCTION_NAME=>’Zoom form function’,
OPEN_FLAG=>’Y’,
SESSION_FLAG=>’Y’,
OTHER_PARAMS=>
’<parameter list>||’”’);
END IF;
END IF;
END;
    1. Compile the CUSTOM.pll and place the “PLX” file in AU_TOP/resource directory and restart the Oracle Applications session.

15Frequently used standard form calls

APP_EXCEPTION.RAISE_EXCEPTION
Summary
This form call is used for handling the exceptions that are raised in the forms.
Description
The exception type, code and the exception text are passed as the parameters, when the exception is raised a popup modal window is raised.
Arguments
PROCEDURE app_exception.raise_exception(
exception_type VARCHAR2 DEFAULT NULL,
exception_code NUMBER DEFAULT NULL,
exception_text VARCHAR2 DEFAULT NULL
);
Example
BEGIN
..code..
EXCPETION
app_exception.raise_exception(
exception_type => ‘WHEN-VALIDATE-ITEM’,
exception_code => ‘<exception_code>’,
exception_text => fnd_message. get(‘<custom_message>’)
);
END;

APP_FIELD.SET_DEPENDENT_FIELD
Summary
This method can be used in the Item Handlers, where for an item we can have a dependent field based on certain conditions.
Description
The method call required the dependent field name and the mater field to be passed, with a Boolean condition to be validated.
This can be called from Item handler events like WHEN–VALIDATE–ITEM, PRE–RECORD and POST–QUERY.
Arguments
PROCEDURE app_field.set_dependent_field(
event VARCHAR2,
master_field VARCHAR2,
dependent_field VARCHAR2
invalidate BOOLEAN DEFAULT FALSE
);
Example
IF <condition>
THEN
app_field.set_dependent_field(
event => ‘WHEN-VALIDATE-ITEM’,
master_field => ‘<master_field>’,
dependent_field => ‘<dependent_field>’
);
END IF;

APP_ITEM_PROPERTY.SET_PROPERTY
Summary
This event sets the property of an Item.
Description
The various properties that can be set to an item are
ALTERABLE
ALTERABLE_PLUS
ENTERABLE
ENTERABLE_PLUS
ENABLED
DISPLAYED
REQUIRED
HINT_TEXT
PROMPT_TEXT
INSERT_ALLOWE,
UPDATEABLE
NAVIGABLE
ICON_NAME
Arguments
PROCEDURE app_item_property.set_property(
ITEM_NAME VARCHAR2,
PROPERTY VARCHAR2,
VALUE NUMBER
);
Example
app_item_property.set_property (
item_name => ‘<item_name>’,
property => ENABLED,
value => PROPERTY_ON
);

APP_QUERY.QUERY_FOREIGN_KEY
Summary
If the form has any non-base table items and if we need to query based on that field, use this routine in PRE-QUERY trigger of the block.
Description
Allow simple Query on foreign key non-database fields by calling the routine in the PRE-QUERY trigger.
Arguments
PROCEDURE app_query.query_foreign_key(
block_name VARCHAR2,
db_field VARCHAR2,
non_db_field VARCHAR2,
table_name VARCHAR2,
id_column VARCHAR2,
name_column VARCHAR2,
extra_where_clause VARCHAR2 DEFAULT NULL
);
Example
app_query.query_foreign_key(
block_name => ‘<block_name>’,
db_field => ‘<INVENTORY_ITEM_ID>’,
non_db_field => ‘<ITEM NAME>’,
table_name => ‘<ITEM TABLE>’,
id_column => ‘<INVENTORY_ITEM_ID>’,
name_column => ‘<Item name of the block>’
);

APP_STANDARD.EVENT
Summary
This routine has the standard Oracle Applications routine calls.
Description
Some of standard events that are available to the routine are.
  • WHEN-NEW-BLOCK-INSTANCE
  • WHEN-NEW-FORM-INSTANCE
Arguments
PROCEDURE app_standard.event (
event_name VARCHAR2
);
Example
app_standard.event (‘WHEN-NEW-BLOCK-INSTANCE’);

APP_STANDARD.SYNCHRONIZE
Summary
Synchronizes the Menu and Toolbar items.
Description
Dynamic changes to the form can affect which menu items apply, although the state of the menu items is not re–evaluated automatically.
If you make a change that affects which items in the toolbar and menu can be used, call this routine, and it re–evaluates the menu and toolbar.
Arguments
procedure APP_STANDARD.SYNCHRONIZE;

APP_WINDOW.SET_COORDINATION
Summary
This routine is used in Master-Child blocks, where the relations of the blocks can be set at run time.
Description
The various relations that can be set at run time are IMMEDIATE and DEFERRED. For this purpose a check box (Control Item with values IMMEDIATE/ DEFERRED) have to be placed in the child window. Call this routine when the value of the checkbox changes & when the window is closed or the child window is opened.
Arguments
PROCEDURE app_window.set_coordination(
event VARCHAR2,
coordination VARCHAR2,
relation_name VARCHAR2
);
Example
app_window.set_coordination(
event => ‘WHEN-CHECKBOX-CHANGED’,
coordination => ‘<value of the checkbox>’,
relation_name => ‘<name of the relation>’
);

APP_WINDOW.SET_TITLE
Summary
Sets the Session information in the Window title.
Description
The window title would be appended with the session variable that is passed to routine.
This can be called in any trigger.
Arguments
PROCEDURE app_window.set_title(
window_name VARCHAR2,
session VARCHAR2,
instance1 VARCHAR2 DEFAULT
’APP_ARGUMENT_NOT_PASSED’,
instance2 VARCHAR2 DEFAULT
’APP_ARGUMENT_NOT_PASSED’,
instance3 VARCHAR2 DEFAULT
’APP_ARGUMENT_NOT_PASSED’
);
Example
app_window.set_title(
window_name => ‘<window name>’,
session => ‘<session information>’,
);

APP_WINDOW.SET_WINDOW_POSITION
Summary
Sets the child window position relative to the parent window.
Description
Code this trigger in the APP_CUSTOM trigger, when the windows of the customized form are called, use this routine to set the window positions relatively with a parent window.
The various relations that are possible are
  • CASCADE
  • RIGHT
  • BELOW
  • OVERLAP
  • CENTER
  • FIRST_WINDOW
The first window of the form is to be set in the PRE-FORM trigger.
Arguments
PROCEDURE app_window.set_window_position(
child VARCHAR2,
rel VARCHAR2,
parent VARCHAR2 DEFAULT null
);
Example

app_window.set_window_position(
‘<child_window>’,
‘<relations>’,
‘<parent window>’
);

FND_CURRENCY.GET_FORMAT_MASK
Summary
This function uses the normal default values to create a format mask.
Description
In Oracle application forms the format of the currency fields can be customized according to profile options set by the user.
This can be extended to custom forms also. Call this function in the PRE-FORM trigger.
This routine uses the following profiles to create the format mask:
  • CURRENCY:THOUSANDS_SEPARATOR
  • CURRENCY:NEGATIVE_FORMAT
  • CURRENCY:POSITIVE_FORMAT
Arguments
FUNCTION fnd_currency.get_format_mask(
currency_code IN VARCHAR2
field_length IN NUMBER
)
RETURN VARCHAR2;
Example

PRE-FORM
APP_ITEM_PROPERTY.SET_PROPERTY(
’<field to be displayed>,
FORMAT_MASK,
fnd_currency.get_format_mask(
currency_code => <currency code to
be displayed>
field_length => <length of the field>
)
);

FND_FUNCTION.EXECUTE
Summary
This routine can be used to call the registered form function from a customized form.
Description
Executes the specified form function. Only executes functions that have a form attached. Displays a message to the end user if the function is not accessible for that responsibility.
Arguments
procedure fnd_function.execute (
function_name IN VARCHAR2,
open_flag IN VARCHAR2 DEFAULT ’Y’,
session_flag IN VARCHAR2 DEFAULT ’SESSION’,
other_params IN VARCHAR2 DEFAULT NULL,
activate IN VARCHAR2 DEFAULT ’ACTIVATE’,
browser_target IN VARCHAR2 DEFAULT NULL
);
Example
IF
Fnd_function.execute(
function_name => <function_name>,
open_flag => ’Y’,
session_flag => ’Y’,
other_params => ’<param_list>’
);

FND_FUNCTION.TEST
Summary
Checks whether the function exists or not.
Description
Function security is a one of the basic features of Oracle Applications. This can be extended to customized Oracle forms also, this particular function can be used for checking certain function exists for the responsibilities and the customized oracle application code can behave accordingly.
Arguments
FUNCTION fnd_function.test (
Function_name IN VARCHAR2
)
RETURN BOOLEAN;
Example

IF fnd_function.test (Function_name => ‘<function
name>’)
THEN
..Process records..
END IF;

FND_MESSAGE.DEBUG
Summary
This routine is used for debugging purposes.
Description
The routine pops up the message strings that are passed as a parameter.
Arguments
PROCEDURE fnd_message.debug (
value IN VARCHAR2
);
Example
BEGIN
...code...
fnd_message.debug (‘debug message’);
END;

FND_ORG.CHOOSE_ORG
Summary
This routine is used for making the user select the Inventory organizations when the customized form is invoked.
Description
Call this procedure in PRE–FORM to ensure the organization parameters are set. If the local form has no organization parameters passed, the global defaults are used.
Arguments
PROCEDURE fnd_org.choose_org(
ALLOW_CANCEL IN BOOLEAN DEFAULT false
);

FND_STANDARD.FORM_INFO
Summary
This sets the form basic information.
Description
This has to be called from the trigger WHEN-NEW-FORM-INSTANCE.
Arguments
PROCEDURE form_info(
version VARCHAR2,
title VARCHAR2,
   application_short_name VARCHAR2,
   date_last_modified VARCHAR2,
   last_modified_by VARCHAR2
);
Example
FND_STANDARD.FORM_INFO(
'$Revision: <form version>$',
'<form name>',
'<application short name>',
'$Date: <date time> $',
'$Author: XYZ $'
);

FND_STANDARD.SET_WHO
Summary
Sets the WHO columns like created by, last updated by columns.
Description
Usage
Used in the PRE-UPDATE and PRE-INSERT triggers.
Syntax is : FND_STANDARD.SET_WHO
Arguments
None


Appendix A: File attached to TEMPLATE form


Form Module:
  1. APPSTAND.fmb
  2. TEMPLATE.fmb
Libraries:
  1. APPCORE.pll
  2. APPCORE2.pll
  3. APPDAYPK.pll
  4. APPFLDR.pll
  5. CUSTOM.pll
  6. FNDSQF.pll
  7. FV.pll
  8. GHR.pll
  9. GLOBE.pll
  10. GMS.pll
  11. IGILUTIL.pll
  12. JA.pll
  13. JE.pll
  14. JL.pll
  15. OPM.pll
  16. PQH_GEN.pll
  17. PSAC.pll
18. VERT.pll


Appendix B: Keywords


OCA : Oracle Applications
AOL : Application Object Library
Directory structure :
AU_TOP : Applications Utilities top.
PLSQLLIB : The directories in the local machine where the library files of forms are kept.
Posted by at

3 comments:

  1. gay3December 11, 2017 at 10:55 PM

    Lot of details in single page. Very useful

    ReplyDelete
  2. UnknownMarch 5, 2018 at 9:11 PM

    This comment has been removed by the author.

    ReplyDelete
  3. UnknownMarch 5, 2018 at 9:13 PM

    I need this as a document material, is this possible.

    ReplyDelete

Subscribe to: Post Comments (Atom)