Oracle Form built-in

Description

Executes the key trigger that corresponds to the specified built-in subprogram. If no such key trigger exists, then the specified subprogram executes. This behavior is analogous to pressing the corresponding function key.

Syntax

PROCEDURE DO_KEY
(built-in_subprogram_name VARCHAR2);
Built-in Type restricted procedure
Enter Query Mode yes

Parameters

built-in_subprogram_name Specifies the name of a valid built-in subprogram.

Built-in Key Trigger Associated Function Key
BLOCK_MENU Key-MENU [Block Menu]
CLEAR_BLOCK Key-CLRBLK [Clear Block]
CLEAR_FORM Key-CLRFRM [Clear Form]
CLEAR_RECORD Key-CLRREC [Clear Record]
COMMIT_FORM Key-COMMIT [Commit]
COUNT_QUERY Key-CQUERY [Count Query Hits]
CREATE_RECORD Key-CREREC [Insert Record]
DELETE_RECORD Key-DELREC [Delete Record]
DOWN Key-DOWN [Down]
DUPLICATE_ITEM Key-DUP-ITEM [Duplicate Item]
DUPLICATE_RECORD Key-DUPREC [Duplicate Record]
EDIT_TEXTITEM Key-EDIT [Edit]
ENTER Key-ENTER [Enter]
ENTER_QUERY Key-ENTQRY [Enter Query]
EXECUTE_QUERY Key-EXEQRY [Execute Query]
EXIT_FORM Key-EXIT [Exit/Cancel]
HELP Key-HELP [Help]
LIST_VALUES Key-LISTVAL [List]
LOCK_RECORD Key-UPDREC [Lock Record]
NEXT_BLOCK Key-NXTBLK [Next Block]
NEXT_ITEM Key-NEXT-ITEM [Next Item]
NEXT_KEY Key-NXTKEY [Next Primary Key Fld]
NEXT_RECORD Key-NXTREC [Next Record]
NEXT_SET Key-NXTSET [Next Set of Records]
PREVIOUS_BLOCK Key-PRVBLK [Previous Block]
PREVIOUS_ITEM Key-PREV-ITEM [Previous Item]
PREVIOUS_RECORD Key-PRVREC [Previous Record]
PRINT Key-PRINT [Print]
SCROLL_DOWN Key-SCRDOWN [Scroll Down]
SCROLL_UP Key-SCRUP [Scroll Up]
UP Key-UP [Up]


** Built-in: DO_KEY
** Example: Simulate pressing the [Execute Query] key.
*/
BEGIN
Do_Key('Execute_Query');
END;

Finding Responsibility of a Form in oralce apps

Below is the Query, which helps you to find all the responsibilities of a Form, to which it has been attached.


SELECT   FORMS.form_name,
         FORMSTL.user_form_name,
         RESTL.responsibility_name,
         FORMSTL.language
  FROM   fnd_form FORMS,
         fnd_form_tl FORMSTL,
         fnd_form_functions FUNC,
         fnd_menu_entries MENU,
         fnd_responsibility RES,
         fnd_responsibility_tl RESTL
 WHERE       FORMSTL.user_form_name LIKE 'CUM Workbench%'
         AND FORMS.form_id = FORMSTL.form_id
         AND FUNC.form_id = FORMS.form_id
         AND MENU.function_id = FUNC.function_id
         AND RES.menu_id(+) = MENU.menu_id
         AND RES.responsibility_id = RESTL.responsibility_id(+)

Users who have Access to a Particular Form

select a.menu_id
     , menu_name
     , c.responsibility_name
     , g.user_name
     , g.description
  from fnd_menu_entries a
     , fnd_menus b
     , fnd_responsibility_vl c
     , fnd_form_functions d
     , fnd_form_vl e
     , fnd_user_resp_groups f
     , fnd_user             g
 where e.form_name like '%'
   and e.form_id       = d.form_id
   and d.function_id   = a.function_id
   and a.menu_id       = b.menu_id
   and a.menu_id       = c.menu_id
   and c.responsibility_id = f.responsibility_id
   and f.user_id           = g.user_id
 order by 1,2,3,4

From and Function with Responcibility attached the form Query

/* Formatted on 9/9/2014 4:20:21 PM (QP5 v5.115.810.9015) */
SELECT DISTINCT
       rtl.responsibility_name,
       ff.function_name,
       ffl.user_function_name,
       form_name,
       user_form_name
FROM fnd_compiled_menu_functions fcmf,
     fnd_form_functions ff,
     fnd_form_functions_tl ffl,
     fnd_responsibility fr,
     fnd_responsibility_tl rtl,
     fnd_user_resp_groups urg,
     fnd_user u,
     fnd_form_vl form1
WHERE     fcmf.function_id = ff.function_id
      AND fr.menu_id = fcmf.menu_id
      AND urg.responsibility_id = fr.responsibility_id
      AND rtl.responsibility_id = fr.responsibility_id
      AND fcmf.grant_flag = 'Y'
      AND fr.application_id = urg.responsibility_application_id
      AND u.user_id = urg.user_id
      AND form1.form_id = ff.form_id
      --         AND UPPER (ffl.user_function_name) = UPPER ('suppliers')
      --        AND upper(ff.function_name) = upper('AP_APXVDMVD')
      AND form_name = 'ARXCUDCI'
      AND ff.function_id = ffl.function_id

How to use folder forms in Oracle

In oracle Applications' GUI includes unique features to enhance the productivity of Oracle's application users. Folder is one the features, that enhance the feature for high productivity.
What is Folder..
As per documentation is say .."A folder is a special kind of window that allows users to customize the set of fields and records displayed for a particular business entity without programming. Using the folder customization menu or tool palette, users can specify the records and the fields they wish to see, as well as field sequence, field prompts, and field display widths. Users can customize folders at any time, saving them for personal use or sharing them with others."
The most common use of folder technology is in the gateway to a key applications entity. Users can customize the folder window to display the set of fields and records that are most relevant to the business at hand. From the folder window, users can perform an action involving one or more of the returned records. Or, they can select a single record, open a detail window that shows all the fields for that record, and perform an action involving that record.
Here are the 9 steps to enable folders in custom forms.
These Changes will make sure that folder can be saved and its query criteria are also saved.
1. Pick up Template.fmb from your $AU_TOP/resource directory on server where forms are residing.
2. Check out all the libraries get loaded successfully along with the Template.
3. Check for APPDAYPK.pll loaded fine, else copy it from APPSTAND.fmb
4. Attach APPFLDR library/pll

APPFLDR can be found in $AU_TOP/Resource directory on server where forms are residing.

5. Copy the STANDARD_FOLDER Object Group

STANDARD_FOLDER Object group can be referenced from APPSTAND form which resides in $AU_TOP/Resource. This will also copies the dependent objects required for object group.
This will copy Folder Related Blocks,Canvases and other necessary objects.

6. Let us say we have One Main block, Not concerned with Control BlocksMain Block:

XXPH_RCPT_HIS then
Create <BLOCKNAME>_RECORD_COUNT parameter
as XXPH_RCPT_HIS_ RECORD_COUNT parameter
where BLOCKNAME is the name of the block containing columns to be displayed. Data Type should be Number and Put default Value of 2.
Canvas
You need two canvas- one content and one stacked. Let us name content canvas as XXPH_RCPT_HIS_CONTENT and stacked canvas as XXPH_RCPT_HIS_STACKED. Both the canvas have same window.
Windows
Create One Window. Name it XXPH_RCPT_HIS_WIN with view name as XXPH_RCPT_HIS_WIN
Blocks Main Block:
XXPH_RCPT_HIS contains database columns. Items should be on Stacked canvas (XXPH_RCPT_HIS_STACKED).

7. Create following eight triggers:

WHEN-NEW-BLOCK-INSTANCE app_folder.event('WHEN-NEW-BLOCK-INSTANCE');
KEY-PREV-ITEM if (:parameter.XXPH_rcpt_his_record_count = 1) then
previous_item;
else
app_folder.event('KEY-PREV-ITEM');
end if;
KEY-NEXT-ITEM if (:parameter.xxph_rcpt_his_record_count = 1) then
next_item;
else
app_folder.event('KEY-NEXT-ITEM');
end if;
PRE-BLOCK app_folder.event('PRE-BLOCK');
POST-BLOCK app_folder.event('POST-BLOCK');
PRE-QUERY
app_folder.event('PRE-QUERY');
KEY-EXEQRY app_folder.event('KEY-EXEQRY');
POST-QUERY app_folder.event('POST-QUERY');

8. PROMPT Block:

Create new block XXPH_RCPT_HIS_PROMPT: which will contain prompts for the column displayed in upper block.
Make subclass information as DYNAMIC_PROMPT.
This block should contain:
Items which will be Prompts for above block columns (Item name should be same as Column name in above block). Prompt Items should be on Stacked canvas (XXPH_RCPT_HIS_STACKED).
Create new Items specified below:
Item Name: FOLDER_OPEN, Item Type: Button, Canvas: XXPH_RCPT_HIS_CONTENT,Subclass:Dynamic_Title
Item Name: FOLDER_DUMMY, Item Type: Text Item , Canvas: TOOLBAR ,Subclass:Folder_Dummy
Item Name: FOLDER_TITLE, Item Type: Display Item, Canvas: XXPH_RCPT_HIS_CONTENT,Subclass:Dynamic_Title
Item Name: ORDER_BY1, Item Type: Button, Canvas: XXPH_RCPT_HIS_STACKED
Item Name: ORDER_BY2, Item Type: Button, Canvas: XXPH_RCPT_HIS_STACKED
Item Name: ORDER_BY3, Item Type: Button, Canvas: XXPH_RCPT_HIS_STACKED
Item Name:Folder_Switcher, Item Type:Text,Canvas: XXPH_RCPT_HIS_CONTENT,Subclass:SWITCHER
Check out your Both Canvases for setting these new buttons,prompts.

9. Form Level Triggers

FOLDER_ACTION --
-- Remove the message and uncomment the line after it to activate the folder actions
--
-- message('You must modify the FOLDER_ACTION trigger in your form!');
app_folder.event(:global.folder_action);

WHEN-NEW-FORM-INSTANCE
app_folder.define_folder_block(' XXPORCHUK ', -- 'Object Name'
' XXPH_RCPT_HIS ', -- 'folder_block',
' XXPH_RCPT_HIS_PROMPT ', -- 'prompt_block',
'XXPH_RCPT_HIS_STACKED', --'stacked_canvas',
'XXPH_RCPT_HIS_WIN', --'window',
NULL); --'disabled functions');

Oracle has provided Folder forms for ease of use. Oracle users can use folders to set the look and feel as per their need and comfort. They can also set folders to query data based on certain criteria.
We shall use the Payables Invoice form to demonstrate how to use folder forms.
Open payables Invoice form.

Note that the menu has the item Folder, , and this icon, . Clicking on the icon opens the Folder Tools window. It looks like the following,

Clicking on Folder item on the menu brings up the items as shown below,

Both options have the same functionalities. The only difference is the former option is GUI based whereas the latter is non GUI based.
Let us review the fields on the form.

The top left icon, , denotes the folder icon. Clicking on this button pulls up the available folders for this form.

We can select any available folder. The folder AP Invoice Entry is set as the default folder for this responsibility and so this folder opened on clicking on the form.
Let us now change some field positions.
We shall move the field, Date invoice Received, to the right of Invoice Amount. You can drag and drop the field or you can use the Folder Tools window or Folder menu item. On rearranging the fields it looks like this,

Now we shall query for some Invoices. Query criteria are the following,
Type = Standard
Invoice Date > 01-jan-2012
Supplier Name like A%
Enter the criteria after hitting F11.

Click on Ctrl+F11 to execute the query.

Now the records are queried. Click on Folder > View Query

The search criteria are saved internally by Oracle. If we create a new folder then the look and the search criteria will be saved as attributes of the folder.
To create a new folder you can click on this icon, , on Folder Tools window or Folder > Save as item on the Folder menu.

We need to give the folder a new name. Let us name it as XX AP Invoice query folder.

• Select the option, Ask each time, as we want Oracle to ask the user whether he/she wants the folder to open and query for the records automatically.
• Check the box, Include Query, so that the same query criteria is saved on the folder.
• Select, Open as Default, to make the folder the default folder.
• Select, Public, so that all users who have access to this form will have access to this folder as well.

Click on Show Query button to review the query that will be saved.

Review the query and then click on OK button to go back to the previous form. Click on OK on that form.

Click on OK again.

Now this folder becomes your default folder. Close the Invoice form.
Open the Invoice form.

The invoice form opens and asks the user whether Oracle should query for the records based on the folder query. We shall select “No” so that Oracle does not query for the records.
Now we want to query for Invoices whose Supplier names start with B. In query mode we enter,

Then we shall execute the query. No rows are returned as one of the search criteria of the folder query is that the supplier names must start with A.
We can still query for the supplier names starting with B. First we need to remove the query on the form. Click on F4 to leave Enter Query mode. Now click on Folder > Reset Query.

A popup message is displayed. Click on OK button. Now click on F11 to go into query mode and enter the search criterion as we had done earlier.

Execute the query by clicking on Ctrl+F11.

Now the folder returns Invoices with the search criterion.
In this article we discussed how to use Folder functionality. In another article we shall discuss how folders can be administered.

Users who have Access to a Particular Form Query

/* Formatted on 7/17/2014 5:37:50 PM (QP5 v5.115.810.9015) */
SELECT a.menu_id,
       menu_name,
       c.responsibility_name,
       g.user_name,
       g.description
FROM fnd_menu_entries a,
     fnd_menus b,
     fnd_responsibility_vl c,
     fnd_form_functions d,
     fnd_form_vl e,
     fnd_user_resp_groups f,
     fnd_user g
WHERE     e.form_name LIKE '%'
      AND e.form_id = d.form_id
      AND d.function_id = a.function_id
      AND a.menu_id = b.menu_id
      AND a.menu_id = c.menu_id
      AND c.responsibility_id = f.responsibility_id
      AND f.user_id = g.user_id
ORDER BY 1, 2, 3, 4

How to register the forms in oracle applications

Steps

1. Download the required fmb(s) and pll(s) from the server.
2. Develop the custom form in forms developer.
3. Move the form into server.
4. Compile the form file.
5. Form registration.
6. Form function registration.
7. Attaching the form function into menu.

Steps in detail

1. Download the TEMPLATE.fmb and APPSTAND.fmb from $AU_TOP/forms/US.
    (You can find all the oracle apps seeded forms in this directory)

2. Download the required pll files from $AU_TOP/resource.
    (You can find all the oracle apps seeded library files in this directory)

3. Copy and save the TEMPLATE.fmb into meaningful custom name.

4. Delete the following objects
             i. Window name (BLOCKNAME)
            ii. Canvas name (BLOCKNAME)
           iii. Data blocks (BLOCKNAME and DETAILBLOCK)

5. Create the windows, canvases and data blocks as per the requirements. (Don’t forget to sub class the properties for the same.) If you are creating the multi record blocks then include a one non-data base item in the same block and sub class the property called “CURRENT_RECORD_INDICATOR”.

6. You must modify the following triggers

          i. PRE-FORM

         ii. WHEN-NEW-FORM-INSTANCE.

7. You do not modify these form-level triggers, but you can write block or item level triggers that override the form-level trigger.
           i. KEY-LISTVAL
          ii. ON-ERROR.

8. You do not modify the following triggers in any way.
               i. STANDARD_ATTACHMENTS
              ii. ZOOM
             iii. FOLDER_ACTION
             iv. KEY-HELP
              v. KEY-EXIT
             vi. KEY-EDIT
            vii. KEY-COMMIT
           viii. WHEN-WINDOW-CLOSED
            ix. CLOSE_WINDOW

9. You must modify the APP_CUSTOM package body as mentioned like a below figure.

10. Move the fmb file into $XXCUSTOM_TOP/forms/US.

11. Compile the fmb file in PuTTY (connect to PuTTY and change the current directory into $XXCUSTOM_TOP/forms/US, execute the below mentioned command with apps password.)

Release 12
frmcmp_batch module= userid=apps/ output_file= module_type=form batch=yes compile_all=special

11i Release
f60gen module= userid=APPS/ output_file= module_type=form batch=yes compile_all=special

12. Register the form in oracle apps.
Navigation -> Application Developer > Application > Form

13. Register the form functions
Navigation -> Application Developer > Application > Function

Description TAB (Function name and user function name)
Properties TAB (Type -> Form)
Form TAB (chose the custom form name)

14. Attach this form function into menu.
Navigation -> Application Developer > Application > Menu
Query the appropriate menu and add the custom function into the same.

15. Verify the form in oracle apps.

Calling a Report from a Form in oracle apps

Using RUN_PRODUCT Built-in we can call a Report from a Form.
It will take 7 Parameters as follows
1) Product name :: REPORT/GRAPHICS
2) Module name  :: Report name
3) Communication Mode :: Synchronous /Asynchronous
SYN :: we cannot work with the Form until and unless you closes the Report.
ASYN:: We can work with Form & Reprot (Navigate)
4) Exec :: Batch/Run time
Batch :: it will fetch all records at a time
Run time :: It will fetch only one Record at a time
5) File name :: database/Filesystem
6) Parameters :: Report Parameters
7) Display :: For Graphics (Mostly null)
Ex :: If we have :p_deptno parameter then we can call as Follows
RUN_PRODUCT (‘REPORT’,’C:TEST1.rep’,SYN,BATCH,DATABASE,p_deptno,’ ‘);

How to avoid duplicate record in Oracle Applications Custom forms

It is a very common requirement that end user should not be able to enter same record multiple times.

Solution

Create a function under Program Units in Oracle Forms Builder

FUNCTION DUPLICATE_ENTRY_CHECK (BLK_COL_NAME VARCHAR2)

   RETURN BOOLEAN

IS

   IS_DUPLICATE     BOOLEAN       := FALSE;

   CURRENT_REC_NO   NUMBER (3)    := NULL;

   LAST_REC_NO      NUMBER (3)    := NULL;

   CUR_VAL          VARCHAR2 (20) := NULL;

   FORM_NAME        VARCHAR2 (40) := NULL;

BEGIN

   CURRENT_REC_NO := TO_NUMBER (NAME_IN ('SYSTEM.CURSOR_RECORD'));

   CUR_VAL := NAME_IN (BLK_COL_NAME);

   FORM_NAME := GET_APPLICATION_PROPERTY (CURRENT_FORM_NAME);

   SET_FORM_PROPERTY (FORM_NAME, VALIDATION, PROPERTY_FALSE);

   LAST_RECORD;

   IF NAME_IN ('SYSTEM.CURSOR_RECORD') <> '1'

   THEN

      LAST_REC_NO := TO_NUMBER (NAME_IN ('SYSTEM.CURSOR_RECORD'));

      FIRST_RECORD;

      FOR ROW_COUNT IN 1 .. LAST_REC_NO

      LOOP

         IF :SYSTEM.CURSOR_RECORD <> CURRENT_REC_NO

         THEN

            IF CUR_VAL = NAME_IN (BLK_COL_NAME)

            THEN

               IS_DUPLICATE := TRUE;

               EXIT;

            END IF;

         END IF;

         IF LAST_REC_NO <> ROW_COUNT

         THEN

            NEXT_RECORD;

         END IF;

      END LOOP;

   END IF;

   SET_FORM_PROPERTY (FORM_NAME, VALIDATION, PROPERTY_TRUE);

   IF IS_DUPLICATE = TRUE

   THEN

      GO_RECORD (CURRENT_REC_NO);

      GO_ITEM (BLK_COL_NAME);

      RETURN TRUE;

   ELSE

      GO_RECORD (CURRENT_REC_NO);

      GO_ITEM (BLK_COL_NAME);

      RETURN FALSE;

   END IF;

END;

Go to relevant item and create a trigger "KEY-NEXT-ITEM"

Example

DECLARE

   AL_ID       ALERT;

   AL_BUTTON   NUMBER;

BEGIN

   IF :XX_BB_LINES.SUPPLIER_ID IS NOT NULL

   THEN

      IF NOT DUPLICATE_ENTRY_CHECK ('XX_BB_LINES.SUPPLIER_ID')

      THEN

         --:PO_DETAIL.SALES_TAX:=5;

         NEXT_ITEM;

      ELSE

         -- PRU_MESSAGE(1,'DUPLICATE ENTRY IS NOT ALLOWED.');

         AL_ID := FIND_ALERT ('SUPPLIER');

         AL_BUTTON := SHOW_ALERT (AL_ID);

         RAISE FORM_TRIGGER_FAILURE;

      END IF;

   END IF;

END;

Now go to relevant block and also create a trigger named "KEY-COMMIT"

Example

DECLARE

   AL_ID       ALERT;

   AL_BUTTON   NUMBER;

BEGIN

   IF DUPLICATE_ENTRY_CHECK ('XX_BB_LINES.SUPPLIER_ID')

   THEN

      AL_ID := FIND_ALERT ('SUPPLIER');

      AL_BUTTON := SHOW_ALERT (AL_ID);

      RAISE FORM_TRIGGER_FAILURE;

   ELSE

      COMMIT_FORM;

   END IF;

END;