1. Enabling/Disabling the fields
2. Changing the List of Values in a LOV field at runtime.
3. Defaulting values
4. Additional record level validations
5. Navigation to other screens.
6. Enabling Special Menu
Primarily there are two methods of extending Oracle Forms, and these are
CUSTOM.pll
FORMS Personalizations
In this article we will cover the basics of using CUSTOM.pll for extending Oracle Forms
How and why does CUSTOM.pll work?
Every form in Oracle Apps is created using something called as TEMPLATE.fmb. But some of the modules like HRMS have their own HR Specific Templates[HRTEMPLT.fmb]. These template files have form level triggers that make call to CUSTOM.pll. The triggers that can be trapped using CUSTOM.pll in HRMS screen can be different than those which can be trapped for other modules.
Commonly used events that are trapped using CUSTOM.pll are:-
ZOOM
WHEN-NEW-FORM-INSTANCE
WHEN-NEW-BLOCK-INSTANCE
WHEN-NEW-RECORD-INSTANCE
WHEN-NEW-ITEM-INSTANCE
WHEN-VALIDATE-RECORD
However, for example in HRMS, you can also write code in CUSTOM.pll to trap below listed events :-
PRE-DELETE and POST-DELETE
PRE-UPDATE and POST-UPDATE
PRE-INSERT and POST-INSERT
POST-FORMS-COMMIT
WHEN-CREATE-RECORD
KEY-DELREC
How to identify which trigger is most suitable for writing your business logic?
You can either open the FMB itself, and see the triggers which are calling CUSTOM.pll.
However, there is a easier way to work out the most suitable triggers. You can navigate to Help/Diagnostics/Custom Code/Show Custom Events
Once that radio button has been set, you will see the list of Events Displayed on the screen.
In some cases, the desired WHEN-NEW-BLOCK-INSTANCE or WHEN-NEW-ITEM-INSTANCE are not being fired. What can I do?
It should always be possible to trap these events in CUSTOM.pll . But in some cases, the form might have these triggers at block/field level, with the trigger property being OVERRIDE. Due to this, the corresponding form level triggers[ to invoke CUSTOM.pll] do not fire. In this case you must raise a bug with Oracle on Metalink.
Structure code code in CUSTOM.pll
IF event_name = 'WHEN-NEW-FORM-INSTANCE'
THEN
IF form_name = 'ARXTWMAI' AND block_name = 'INVOICE_HEADER'
THEN
xx_arxtwmai.default_customer;
ELSIF form_name = 'ARXTWMAI' AND block_name = 'INVOICE_HEADER'
THEN
.....
END IF;
ELSIF event_name = 'WHEN-NEW-BLOCK-INSTANCE'
THEN
.....
END IF ;
Lets take some scenario's where CUSTOM.pll can be used
1. Change the label of a field
app_item_property2.set_property ('BLOCK.FIELD',label,'New Label');
2. Default a value
copy (TO_CHAR (n_person_id),'PERSON_BLOCK.PERSON_ID' );
3. Alter the SQL for LOV Query
PROCEDURE filter_customers_in_lov IS
v_customer_id recordgroup;
n_temp_id NUMBER;
v_customer_lov lov;
BEGIN
v_customer_id := create_group_from_query('XX_CUSTOMER_GROUP'
,'select ... from hz_cust_accounts where ..your custom criteria here..');
n_temp_id := populate_group(v_customer_id);
v_customer_lov := find_lov('EXISTING_LOV_NAME_HERE');
IF get_lov_property(v_customer_lov,group_name) = 'EXISTING_GROUP_NAME_HERE'
THEN
set_lov_property(v_customer_lov,group_name,'XX_CUSTOMER_GROUP');
END IF;
END filter_customers_in_lov;
4. Make a field mandatory
app_item_property2.set_property ('XXBLOCK_NAME.XXFIELD_NAME',required,property_true);
Similarly you can enable or disable the fields too.
5. You can display messages, for example
FND_MESSAGE.CLEAR;
fnd_message.set_name('APPL_SHORT_NAME_HERE', 'MSG_NAME_HERE'); or fnd_message.set_string('message text');
fnd_message.warn or fnd_message.error or fnd_message.
6. Enable or Disable Special Menu
PROCEDURE manage_special_menu IS
mi_id menuitem;
BEGIN
mi_id := find_menu_item('SPECIAL.SPECIAL15');
IF name_in('system.cursor_block') = 'INVOICE_HEADER' THEN
app_special2.instantiate('SPECIAL15', 'Print Invoice');
set_menu_item_property(mi_id, displayed, property_true);
set_menu_item_property(mi_id, enabled, property_true);
ELSE
set_menu_item_property(mi_id, displayed, property_false);
END IF;
END manage_special_menu;
7. Handle the click on Special Menu
IF event_name = 'SPECIAL15' THEN
IF form_name = 'INVOICE_FORM' THEN
xx_invoice_form.process_printing;
END IF;
IF form_name = 'SUPPLIER_FORM' THEN
xx_supplier_form.email_supplier;
END IF;
END IF;
8. Ask user a question, and take appropriate action
v_token_value VARCHAR2(1000);
n_button_selection INTEGER;
BEGIN
fnd_message.set_name('APPL', 'MESSAGE');
fnd_message.set_token('XXTOKEN1', v_token_value);
n_button_selection := fnd_message.question('Email Invoice', 'Fax Invoice', '', 1, 2, 'question');
IF n_button_selection = 1 THEN
xx_call_invoice_print;
ELSE
xx_fax_invoice;
END IF;
9. Call Another form function
fnd_function.EXECUTE(
function_name => 'XX_FORM_FUNCTION_NAME_HERE'
,open_flag => 'Y'
,session_flag => 'SESSION'
,other_params => 'P_INVOICE_ID = "' || n_invoice_header_id || '"'
,activate_flag => 'Y');
10. Make some segments of a KeyFlexfield Display-Only depending upon some condition
For example to make 1st segment of a KFF display-only, we can use
IF v_check_result='xyz' THEN
FND_KEY_FLEX.UPDATE_DEFINITION(
BLOCK => 'BLOCKNAMEHERE'
,FIELD => 'FLEXFIELDNAME'
,ENABLED => 'Y'
,DISPLAYABLE => 1);
END IF ;
As you may have gathered by now, almost any form related task can be done using CUSTOM.pll
Action Type in CUSTOM | Allowed |
Opening SQL Cursors | Yes |
Executing pl/sql stored procedures | Yes |
Referencing fields using bind notation like :block.field | No |
Exception management | Yes |
For additional reading on CUSTOM.pll, please visit
Best Practices for Development on CUSTOM.pll
Playing with CUSTOM.pll
Comments
i think u can elobrate this topic using some real time example
this will help in great
thnaks
balkrishna
If you can organize this site ...that will be really great
thanks
I have three or four button in forms,
i want to disable one button for particular user,not for senior lable person(like manager)
can u send me the steps for this)
thanks in advance
pawan
You can use forms personalizatito n to achieve this. In the context field, enter the USERNAME for which button needs to be hidden.
In case there are two many users, then my question would be if superuser and lower-level user have differing responsibilitie s. If so, your context could be responsibility.
If for some reasons you can not use Forms Personalization , then in CUSTOM.pll, use fnd_global.user _id or fnd_global.empl oyee_id to drive your logic.
Thank s,
Anil
I want to convert forms from Forms 4.5 to Forms 10g, What is the procedure for it?
Will have to convert forms4.5 to forms 6i & then forms 6i to forms 10g? or is there any tool to do it. Please guide?
Yes, try 4.5-->6.0-->10g route
If that does not work, then 4.5-->6.0-->9g- ->10g will certainly work.
Than ks,
Anil Passi
excellent article
The document what your given is very good
Actually i have one problem i need to display lov values based on my record group
For this i created one record group programmaticall y and i attached this group to lov and I attached this lov to my item when i run it ‘s showing the old values.
Actuall y the lov what am chaining is key flex field item lov i want to aching how can i help me out
I haven't tried this, and I assume you aren't changing the LOV against the individual segment of KeyFlex.
Anywa y, if standard KEY-FLEX API gets called in WHEN-NEW-ITEM-I NSTANCE, then it will override your LOV/RG changes.
Try to get your code to execute in WHEN-NEW-ITEM-I NSTANCE
Thanks ,
Anil Passi
would please give me an example because iam structed in middle i need to finish it asap
please
tha nks
regards
sandee p.
Usuall y you never alter the KFF using personalization or CUSTOM.pll
Are you simlpy wanting to filter the values during data entry?
You can also append to the where conditions of a Key Flexfield, a common example is for exclusion of accounts with summary flag
fnd_key_flex.de fine(BLOCK => 'YOUR BLOCK NAME'
,field => 'Your KFF Field Name'
,appl_short_nam e => 'SQLGL'
,code => 'GL#'
,num => v_chart_of_acct s
,id => 'GL_CODE_COMBIN ATION_ID'
,displayable => 'ALL'
,description => 'ACCOUNT_FLEX_D ESC'
,where_clause => 'summary_flag != ''Y'' and ENABLED_FLAG='' Y'' and detail_posting_ allowed_flag = ''Y''');
Than ks,
Anil Passi
thanks
i got it.
regards
sandeep
The articles in your website is excellent.It gives an indepth knowledge to anyone.The presentation of the articles is also excellent.
I am looking forward for some more articles like this.
i want form fire trigger sequence in oracle forms please send ASAp.
Thanks & Regards,
subbu
You can put some messages in these form triggers and note down the sequence in which they fire. If I remember it correctly, the sequence is pre-form, when-new-form, pre-block,-when -new-block, pre-item, when-new-item.
I tried this some 8yrs ago, by putting in messages into these triggers. Please correct this if your findings are any different.
Tha nks
Anil Passi
first of all, thanks so much for this wonderful site, i learnt a lot.
i m thinking to customize the LoV query in Expenditure Type column in AP Invoice Distribution.
currently the Lov showing all the expenditure types, while in fact it should be restricted based on Resource Group defined in Project.
can i do this thru custom.pll???
Thanks&Regards,
Ferry
Sure you can use CUSTOM.pll
But this can also be achieved using forms personalization FP
step 1. create a new record group with a query filtering on resource group
step 2. attach that record group to the existing LOV
Both these steps are possible via FP
Thanks
Anil
Can we handle the clear tool from tool bar in custom.pll
Reg ards
B.kishan kumar
i need some help on the point 10 on this document. copied below:
,FIELD => 'FLEXFIELDNAME' what do we need to put here?
for instance what could be FIELD when the flexfield is 'system items'? can we know this using the 'help->diagnost ic->??'
thanks ,
ahmed
10. Make some segments of a KeyFlexfield Display-Only depending upon some condition
For example to make 1st segment of a KFF display-only, we can use
IF v_check_result= 'xyz' THEN
FND_KEY_FL EX.UPDATE_DEFIN ITION(
BLOCK => 'BLOCKNAMEHERE'
,FIELD => 'FLEXFIELDNAME'
,ENABLED => 'Y'
,DISPLAYABLE => 1);
END IF ;
Iam facing one situtation ,How to trap the clear record event in the custom.pll in PO screen. KEY-DELREC and KEY-CLRREC are captured only in HRMS module or it can also capture in PO(distribution ) module
Regard s,
B.kishan kumar
Iam facing one situtation ,How to trap the clear record event in the custom.pll in PO screen. KEY-DELREC and KEY-CLRREC are captured only in HRMS module or it can also capture in PO(distribution ) module
Regard s,
B.kishan kumar
Please open your PO form and see if APP_STANDARD.EV ENT is getting called from the desired trigger at FORM Level
Also, use examine to see if any other alternate trigger/event combination may be used
Thanks
Anil passi
I need to write two custom codes to custom.pll and add a DFF. Details are
1)the oracle user,contractor and preparer should all be employees. in requisition and receipt forms emp name is mandatory. the two validations needs to such that supp name associated with contractor should not be same as the suppname entered in requisition form and receipt form. If they are same then the validation should not allow to save transaction if a) ora user/preparer has entered supp to whom preparer/user belong to and b)if user has entered receipt against PO having supp same as user associated supp.The DFF will have Context value field for employee having LOV of ‘Employee ’ and ‘Contract or’ ,if user selects the value as ‘Contract or’, then supplier field will be prompted ,which is mandatory .
Thanks
chris
I have a requirement to block the master org in the change organization form.
How can i do this using custom.pll.
Tha nks and regards,
Srinid hi.
I found your site couple of weeks back and I found it very usefull, thanks for all your efforts. If possible please provide an insight.
Quest ion : Requirement is that If ITEM has 0(ZERO) UNIT_PRICE than user should not be able to select it in PO form (POXPOEPO).
I dont want to customize form for this but enable to do it using Custom.pll, I'm able to pop-up message using Form Personalization but still Unit Price field is editable.
Than ks
I read your article on Making some segments of a KeyFlexfield Display-Only depending upon some condition.
Sim ilarly, is it possible to make some segments of a Descriptive Flexfield Disabled or ReadOnly. It will be helpful if you assist me.
Thanks,
Ni le.
Such a wonderful site... You have Anil Passi...
I would like to have the firing sequence of the triggers in the forms 6i.....
Regard s,
Mark V.
I have one issue, that is a particular forms retrieves required record when the Help->diagnosti cs-Custom Code ->Off .
But the same time Help->diagnosti cs-Custom Code ->Normal, when i move the block its not showing correct records.
Question is Help->diagnosti cs-Custom Code ->Off in this case what are the code will be disabled.
I have one issue, that is a particular forms retrieves required record when the Help->diagnosti cs-Custom Code ->Off .
But the same time Help->diagnosti cs-Custom Code ->Normal, when i move the block its not showing correct records.
Question is Help->diagnosti cs-Custom Code ->Off in this case what are the code will be disabled. Is it personalization code or custom.pll code or even some customization code from form trigger
is there any way using form Personalization , I can disable the When-Validate-I tem trigger.
You can use set item property item_is_valid = true.
I dont remember the exact syntax though. But if the item is in valid, when WVI will not be fired. But why would you ever want to stop standard validation doe by Oracle's form?
This can cause data corruption if not analysed properly.
THan ks,
Anil
I discovered that bind variable :po_approve.app roval_path_id can not be used in record group, how to overcome this limitation?
Tha nks.
I have a requirement in Purchasing Order form by using Form personalization or Custom.pll, Requirement is as follows
1. Po Num(segment1 column) should be enabled (its done, no issue abt it)
2. Po number will be populated on our own sequences along with resp_name (first 3 letters) by concatanated with resp_name.
Tha nks in advance for your valuable suggestions,
With Best,
Ramprasa d K
M U M B A I..
For example BBC(where BBC is British Brod Corporation)
I need to do a validation in a standart form (OEXOEORD.fmb from release 12), using custom.pll, when i press the commit button.
Thanxs It's URGENT!!!
sorry my english!!!!!!!
thanxs again.
I need to do a validation in a standart form (OEXOEORD.fmb from release 12), using custom.pll, when i press the commit button.
Thanxs It's URGENT!!!
sorry my english!!!!!!!
thanxs again.
Enable show custom events, and see if WHEN-VALIDATE-R ECORD is captured CUSTOM.pll prior to COMMIT taking place.
Thanks,
Anil Passi
Its good to follow foot steps of Great people.Its not a bad thing to copy good ideas from people like you
All your examples are very useful. I need to open a Custom From by button press of an Oracle Form. At present this button press is opening an Oracle Form. What are the steps?
Thanks,
Julius
Can we write code in CUSTOM.pll for the event WHEN-BUTTON-PRE SSED? At present 'Find' button of eBusiness Center Form (ASTRCALL) is calling Universal Search Form (ASTLSGEN). I created a Custom Form based on ASTLSGEN.fmb. Now I want 'Find' button of eBusiness Center Form (ASTRCALL) should call my form instead of Universal Search Form (ASTLSGEN).
Ca n it be done by Form Personalization ? How?
If we need to use CUSTOM.pll for this, what are the steps?
Thanks,
Julius
I suggest that you hide that button using forms personalization and then create a new tools menu via FP. This new tool menu can call your custom form.
Thanks,
Anil
Many thanks for your quick respond.
That means FP or CUSTOM.pll will not work for WHEN-BUTTON-PRE SSED event of an Apps form?
Julius
thnks alot anil
regards
jyothsna.a
in the custom.pll i hv written the code in when-new-form-i nstance trigger
by taking the form name n block name and set the displayed property off
i hc compiled the pll in the corerect directory path n plx is generated
so when i go back to the particular form in the front end i
didnot find the displated property_off for the particular iem
so
since it is compiled succesfuuly
what might be the problem
if (form_name = 'xxxx' and block_name = 'xxxxx')
then
param_to_pass1 := name_in('xxxxx. yyL1');
param_to_pass2 := name_in('xxxxx. yyL2');
param_to_pass3 := name_in('xxxxx. yyL3');
);
set_item_proper ty(xxxx.yyL1,DI SPLAYED,PROPERT Y_OFF);
commit;
regards
jyoth sna.
Secondl y, please put some debug messages there temporarily, using fnd_message.str ing
Thanks
Ani l Passi
first - great sites. Thank you for enabling all the sharing.
i have a question on the playing with custom.pll-
For example to make 1st segment of a KFF display-only, we can use
IF v_check_result= 'xyz' THEN
FND_KEY_FL EX.UPDATE_DEFIN ITION(
BLOCK => 'BLOCKNAMEHERE'
,FIELD => 'FLEXFIELDNAME'
,ENABLED => 'Y'
,DISPLAYABL E => 1);
END IF ;
If you use this above, isnt it going to affect everyone at that time? what if 2 people access it and you conditions are maybe per a specific responsibility? is this update global or per the session?
I want to create a validation message on book of sales order.
I want to perform to validation ( promised date > schedule date , margin > 10) on each line.
here is the rub-
I want to fire the message when user hits book
I want only one message
the message should list both validations and each line that fails
ex.
Th e following lines have failed validation-
Ma rgin validation
Line 1
Line 2
Line 3
Line 4
Date Validation
Line 5
Line 6
Line 7
Recently I have worked on custom.pll and I have question what is the best way of coding.
I have to disable some buttons based on some conditions. Initially I have written the code like below.
IF event_name = 'WHEN-NEW-ITEM- INSTANCE' THEN
IF form_name='GLXR JDEF' THEN
l_category_name := NAME_IN('REC_EN TRY.USER_JE_CAT EGORY_NAME');
BEGIN
condition
END;
IF condition > 0 THEN
set_item_proper ty('BUTT_REC_LI NE',ENABLED,PRO PERTY_FALSE);
set_item_proper ty('GENERATE',E NABLED,PROPERTY _FALSE);
ELSE
set_item_proper ty('BUTT_REC_LI NE',ENABLED,PRO PERTY_TRUE);
set_item_proper ty('GENERATE',E NABLED,PROPERTY _TRUE);
END IF;
END IF;
END IF;
After reviewed by some body I was suggested to use this way.
IF form_name='GLXR JDEF' AND event_name = 'WHEN-NEW-ITEM- INSTANCE' THEN
l_category_name := NAME_IN('REC_EN TRY.USER_JE_CAT EGORY_NAME');
BEGIN
condition check
END;
IF condition > 0 THEN
set_item_proper ty('BUTT_REC_LI NE',ENABLED,PRO PERTY_FALSE);
set_item_proper ty('GENERATE',E NABLED,PROPERTY _FALSE);
ELSE
set_item_proper ty('BUTT_REC_LI NE',ENABLED,PRO PERTY_TRUE);
set_item_proper ty('GENERATE',E NABLED,PROPERTY _TRUE);
END IF;
END IF;
Could you please tell me, which is the best way of coding and what are the differences from first method and second method.
Your reply is appreciated. Thanks in advance.
Regards,
Chand ra R
AR Collection user>Collection s>collections>a ging tab>transaction s button
After transaction page is opened ,when i right click .View notes item is disabled.
Requi rement:I need the view notes to be enabled when the type is payment.
Can it be done using form personalization ?
If not in what way i can get the solution.
Pleas e help.
Thank you.
I have 10 segments in one DFF.
When form opens, out of 10 segments of DFF, 2 should be read only.
And in DFF 4th segment's visibility should be change as per value in 3rd segment. i.e. if 3rd segment value is 'O' then segment 4 should get visible and for other than 'O' value in segment3, segmen4 should not be visible.
How can I achive this?
RSS feed for comments to this post