Many times we need to execute custom PLSQL procedures and we often choose to create concurrent programs to do that but there are certain cases where we need to use forms personalization to call PLSQL procedure.
I will take one simple business requirement to help you understand the need for this kind of approach. Let’s say your business has a third party system like Agile to maintain your inventory items but your item creation should start from Oracle Apps. As we don’t want to give our base table MTL_SYSTEM_ITEMS_B table access to agile system we will create a custom table (preferred in custom schema) with limited columns which need to be shared with agile system.
Custom Table Script:
|
1 2 3 4 |
CREATE TABLE “APPS”.”ERPS_DEMO_FORMS_PERS” ( “ID” VARCHAR2(100 BYTE), “NAME” VARCHAR2(100 BYTE) ); |
Custom procedure to call from Inventory item Form via Forms personalization.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE OR REPLACE PROCEDURE erps_demo_forms_pers_prc( p_inv_item VARCHAR2, p_inv_desc VARCHAR2) AS BEGIN INSERT INTO erps_demo_forms_pers VALUES (p_inv_item, p_inv_desc ); COMMIT; EXCEPTION WHEN OTHERS THEN NULL; END; |
Now having Custom table and custom procedure compiled in database we will move on to front end and personalization to call this procedure.
Loging to Apps front end and navigate to Inventory Responsibility.
Navigation: Inventory > Items > Master Items
If it prompts for selection of inventory organization select anything as per your preference. This form being a master form your selection doesn’t matter here in anyway.
Navigation: Help > Diagnostics > Custom Code > Personalize
Enter information as below.
Recommendation: Always limit personalization scope to USER when you are developing so that if something goes wrong you can at least log in as another user and disable it.
Navigate to “Actions” Tab and enter the information as shown in screenshot below.
Argument:
|
1 2 3 4 5 |
=’declare begin erps_demo_forms_pers_prc(”’||${item.MTL_SYSTEM_ITEMS.INVENTORY_ITEM_MIR.value}||”’, ”’||${item.MTL_SYSTEM_ITEMS.DESCRIPTION_MIR.value}||”’); end’ |
Save it.
Close form.
Open form again, Create new inventory item and save it.
Now you should be able to see the item in both standard base table and also in our custom table.