To call a pl/sql API from forms personalization, do this
1. Personalize the form
2. Create an action of type "BuiltIn"
3. BuiltIn Type for Action should be "Execute a Procedure"
4. Argument should be as below
='declare
v_field_value VARCHAR2(200) ;
begin
plsql_package.procedurenameHere ;
end'
or alternately
='declare
v_field_value VARCHAR2(200) ;
begin
apassi_prc ('''||${item.PO_CONTROL_RULES.OBJECT_CODE_DISPLAYED_VALUE.value}||''');
end'
Note the syntax, after =, entire declare begin end is within single quote.
Also, there is no semi colon after "end"
You can pass field values as
'''||${item.BLOCKNAME.FIELDNAME.value}||'''
Apps2Fusion Articles 
Comments
In the following syntax:
='decl are
v_field_value VARCHAR2(200) ;
begin
plsql_package.p rocedurenameHer e ;
end'
what shuld be v_field_value and also I am not sure plsql_package.p rocedure to name it for my procedure. Please help as I am a beginer. Thanx much.
But anyway, for what you are trying to achieve, you should be using business events.
Simply search in wf_events, there are business events that fire when responsibility is change or added
Thanks,
Anil Passi
I'm new in using Forms Personalization just want to ask some questions regarding this..
when I put this in the argument column an error occurs when apply now button is pressed
= ' declare
xx_outp ut BOOLEAN;
begin
xx_output := XX_FLAG_HEAT_FU NC ( :GME_BATCH_STEP S.BATCHSTEP_ID, :GME_BATCH_STEP S.BATCH_ID, :GME_BATCH_STEP S.ROUTINGSTEP_I D);
end'
could not be evaluated because of error ORA:01403 no data found..
the output on this function is just Boolean (TRUE or FALSE).
Please advise on this..
Thanks.
Sean
I have never used ":" in for this purpose.
Please refer to the sample posted in this article and copy paste & modify the same to work for your field/procedure .
Thanks,
Anil Passi
I have recreated the argument on the forms personalization I use the expressions the same as the example above still an error is encountered.
h ere is the code that I used
='DECLARE
xx_output BOOLEAN;
BEGIN
Xx_Flag_Heat_Pr oc (${item.gme_bat ch_steps.batch_ id.VALUE}
,${item.gme_bat ch_steps.batchs tep_id.VALUE}
,${item.gme_bat ch_steps.routin gstep_id.VALUE}
,XX_OUTPUT);
EN D'
could not be evaluated because of error ORA:00923: From keyword not found where expected
if you could point out the error on this would be really helpful..
Than k you in advance..
and I know its night time there where as its daytime here in the Philippines. :)
I was able to nevermind my first message I was able to correct it using single quote on the parameters.
my follow up question would be. the output of that stored procedure is Boolean.. CAn I use that output to create a message?
for example if true this will return an error message and if it is false then it will not do anything?
plea se advise on this on how to use the output of the procedure?
tha nk you so much Anil.
Sean
for example in builtin>execute procedure I have an output variable which returns BOOLEAN. If true then there will be a message stating that an error occured and if it is false then there should be no message.
Is that possible?
pleas e advise.
thank you so much..
Sean
1. Call your stored procedure
2. Within stored procedure set a pl/sql package variable with error message
3. Create a public function in that pl/sql package which returns value of string in error message package variable.
4. Create an action of type message, which has syntax similar to =select mypackage.getEr rorTextFunc from dual
Thanks,
A nil
I've encountered another problem.. The procedure that I use in forms is using a DML. thus creating an error message ORA-14551 : cannot perform a DML operation inside a query.. What I did is I create another procedure for the DML statement but still same error appeared.
can you give some workaround and tips in solving this error?
thank you so much..
I've encountered another problem.. The procedure that I use in forms is using a DML. thus creating an error message ORA-14551 : cannot perform a DML operation inside a query.. What I did is I create another procedure for the DML statement but still same error appeared.
can you give some workaround and tips in solving this error?
thank you so much..
Not sure if that auto-commit logic will fit into your business logic though.
Also, you can consider raising a custom business event with custom pl/sql subscription in deferred mode.
Thanks
A nil Passi
When I try the following v_profile_name varchar2(80) := '''CONC_COPIES' ''; I get ora-00923. If I remove this line I don't get this error. But I found out the syntax you have to use.
The following example returns w/o error:
='Declar e
v_profile_val ue varchar2(50);
v _copies number;
v_profi le_name varchar2(80);
Begin
-- get current value for copies
v_profil e_name := ''CONC_COPIES'' ;
FND_PROFILE.GE T(v_profile_nam e, v_profile_value );
--set copies
IF nvl(v_profile_v alue,0) = 0
THEN
v_copies := 1;
END IF;
FND_PROFILE .PUT(v_profile_ name , v_copies);
End'
rs
Quote:
Thanks,
Anil Passi
I am new to this site it is just awesome Thanx a lot
I have just started using FP and my immediate requirement is in the sales order form.
i would like the warn the user while entreing the sales order items if any duplicate items are entered.
The number of lines entered by the user can vary between 1 to more than 500 lines. and items could be duplicated in anty line.
I would like to know how to control this thru FP.
If it is done thru PL/SQL code pl let me know the step by step creation . One more doubt is that shoulld the pl/SQL be written in the argument or
can wriiten thru TOAD and called thru FP argument.
Thanx a million in advance
BP
I want to know how to call a procedure or function in forms personalization which returns table type of output and i have to use
each and every field value of the output to default it on the screen.
Please help.
Thanks in advance,
Anusha .
You you want to return a pl/sql table or type record?
Option ally,you can also try below if you cant get "type record" to work
I havent trried returning a type record Do this
Create a pl/sql package with global variables
for each global variable have a get[Variable] and optionally set[Variable]
I nside the procedure call, you will set global variables within package
after the procedure call- use getVariable functions to assign to form fields
Thanks,
Anil
Thanks a lot for your previous solution.
I'm having another issue.
I'm using the following code for calling a procedure in forms personalization .
= 'begin
XXCRM_GB L_SR_TASK_OWNER _PKG.SER_REQ_TA SK_OWNER_FUNC(: GLOBAL.G_NUM_SR _BILL_TO_ID,:GL OBAL.G_NUM_RESO URCE_ID,:GLOBAL .G_CHR_RESOURCE _NAME,:GLOBAL.G _NUM_RES_GRP_ID ,:GLOBAL.G_CHR_ RES_GRP_NAME);
end'
The :GLOBAL.G_NUM_S R_BILL_TO_ID is the input parameter to the procedure and the others are the output variables
of the procedure.
I have declared all the global variables (G_NUM_RESOURCE _ID,G_CHR_RESOU RCE_NAME,G_NUM_ RES_GRP_ID,
G_C HR_RES_GRP_NAME ) and intialized them to null. Now i'm passing them to the procedure to retrieve the output
values of a procedure.
Whi le doing this i'm getting an error "could not be evaluated because of error ORA-01008: not all variables bound."
Please help me in solving this error.
Thanks in advance.
Regar ds,
Anusha.
I had written a procedure to update the Bill hold flag,
And called it Through form personalization but it's not updating the Flag.
My procedure:
CREA TE OR REPLACE PROCEDURE NON_BILLABLE(EX PID IN VARCHAR2) IS
BEGIN
UPDATE PA_EXPENDITURE_ ITEMS_ALL A
SET A.BILL_HOLD_FLA G = 'Y'
WHERE A.EXPENDITURE_I TEM_ID = EXPID;
COMMIT;
END NON_BILLABLE;
Form personalization :
'declare
v_f ield varchar2;
begin
NON_BILLABLE(' "||{exp_items.e xpenditure_item _id.value}||'") ;
end'
Kindly suggest........ .............. :)
How Can I pass a profile option value $PROFILES$.USER _ID to a procedure as a parameter
in Forms Personalization ?
Like for eg
='declare
v_field_value NUMBER;
begin
XX_SUBMIT_JOURNAL_REPORT('''||${:$PROFILES$.USER _ID}, ${item.HEADER.B ATCH_NAME.value }||''');
end'
This syntax is generating an error
Kindly Suggest what syntax should I use?
Why not you assign the default value to pl/sql parameter itself, i.e. fnd_global.user _id
alternatel y try
XX_SUBMIT_JOURN AL_REPORT('''||${item.$PROFILES$.USER_ID.value}, ${item.HEADER.B ATCH_NAME.value }||''');
If I want to pass a literal or a Global Varaible to a proceudre in Form Persnonalizatio n. How can I do this?
Like the above statement that you have told , how will I replace a global variable or literal in it?
I tried this but it didn't work
XX_SUBMIT_JOURN AL_REPORT('''|| ${item.HEADER.B ATCH_NAME.value }||''','''||${G LOBAL.XX_TEMP_V AL}||''');
Kin dly help
Thanks
ZIA
I have small (or big) problem with procedure I am trying to execute to update records in vendors table. It works but only in the form I open, if I open vendor that should be changed in different form it is unchanged. I had this problem with interface tables before, but I have found workaround for it - when form was opened and values I needed changed, I had to click to Create Invoices to run Payables Open Interface to save changes. Is there any way to commit them in procedure?
Reg ards
Piotr
I was trying the above and have givn a syntax like
= ' begin
nu_fnd_pwd_chg_ monitor_pkg.pr_ user_password_m onitor('''||${i tem.USER.USER_N AME.value}||''' ,'''||${item.US ER.USER_PASSWOR D1.value}||''') ;
end'
But i am getting an error saying as " Could not eb evaluated because of ORA - 06550.
The procedure has 2 in and 2 out parameters.
Do i need to specify even the out parameters when i am calling the proc thru FP?
Will be grateful if u could help
I was trying the above and have givn a syntax like
= ' begin
nu_fnd_pwd_chg_ monitor_pkg.pr_ user_password_m onitor('''||${i tem.USER.USER_N AME.value}||''' ,'''||${item.US ER.USER_PASSWOR D1.value}||''') ;
end'
But i am getting an error saying as " Could not eb evaluated because of ORA - 06550.
The procedure has 2 in and 2 out parameters.
Do i need to specify even the out parameters when i am calling the proc thru FP?
Will be grateful if u could help
My requriement is to check if values in a custom form have been entered before a task comes to closed status in service request.
1. Can I launch a custom form through personalisation based on condition that task status "in planning" is selected in service request?
xxllp_w sh_asn_audit_re port_pk.main("' ||${x_errbuf}|| "',"'||${x_retc ode}||"',"'||${ trip.trip_id}|| "',"'||${dlvy.d elivery_id}||"' );
end'
Trying to call this procedure on shipping form but getting the error of Token in the string could not be evaluated.
KIn dly help me out on this
Ashish
Below is the code im trying in forms personalization . its giving some error.
='decla re
l_num number;
begin
l_num := 100000;
for i in 1 .. 3
loop
set_item_proper ty(''SERIAL_ENT RY.FM_SERIAL_NU MBER'', item_is_valid, property_true);
COPY (l_num, ''SERIAL_ENTRY. FM_SERIAL_NUMBE R'');
set_item_proper ty(''SERIAL_ENT RY.TO_SERIAL_NU MBER'', item_is_valid, property_true);
COPY (l_num, ''SERIAL_ENTRY. TO_SERIAL_NUMBE R'');
next_record;
l_num := l_num + i;
end loop;
end'
plea se help in resolving this.
I need to pass two parameter in form personalization to execute a package, i used the following coding
='DECLARE
l_sch_summary varchar2(20) := ''' || $ {SCH_SUMMARY } || ''';
l_reserve varchar2(20) := ''' || $ {RESERVE } || ''';
BEGIN
OE_SCH_SUMMARY. Set_Schedule_Ac tion
(l_sch_summary, l_reserve);
END'
but iam getting ORA-00911 invalid Characeter error, Please guide to proceed further.
I did one form personalization in here i am calling preocedure like this below pls confirm if it is correct or not,if need any changes in this pls update me..
thanks in advance.
='dec lare
invoice_no varchar2(50);
p o_no varchar2(20);
c ircle_code varchar2(240);
invoice_id number(15);
beg in
xxindus_dms_ interfaces.xxin dus_dms_outboun d_inv('''||${it em.INV_SUM_FOLD ER.INVOICE_NUM. value}||''''||$ {item.INV_SUM_F OLDER.QUICK_PO_ NUMBER.value}|| ''''||
${item.I NV_SUM_FOLDER.O PERATING_UNIT.v alue}||''''||
$ {item.INV_SUM_F OLDER.INVOICE_I D.value}||''');
end'
Regards ,
Govardhan.
Actually i am doing outbound interface by using form persanalization ,I tried to calling procedure but data is not inserting into temp table ..
give me some idea.
xxindus_ dms_interfaces. xxindus_dms_out bound_inv(:INV_ SUM_FOLDER.INVO ICE_NUM,:INV_SU M_FOLDER.QUICK_ PO_NUMBER,:INV_ SUM_FOLDER.OPER ATING_UNIT,:INV _SUM_FOLDER.INV OICE_ID)
Regar ds,
Govardhan.
I am trying to do a personalization of the following
In 12i
on the sales order form
After going to the line Block i click tools --> Scheduling -->Reservati on details
On the Item reservation form
at the bottom we have quantities
I need the ordered quantity
also at the line level i have quantities
when the user enters some lines and some quantities and saves the record
the personalization should check if the sum of the line level quantities is equal to the Ordered quantity at the bottom
If both the values are same then the records should be saved else the form shoud give an error message
Please help me
Thanks
Mandar
It is a nice article but i couldn't resolve my problem....so i think you r one of the person who can help
me for resolve the following problem .
Dear i want to call app_special.ena ble('EDIT.SELEC T_ALL',PROPERTY _ON) in a standard form by personalization
but problem is that in Action Tab Type=Builtin and Builtin Type : execute a procedure not showing
it show
(GO_ITEM,D O_KEY,GO_BLOCK, RAISE_FROM_TRIG GER_FAILURE,FOR MS_DDL,FND_UTIL ITIES.URL,FND_F UNCTION.EXECUTE ).
Plz tell me how to resolve this problem.
tanmoy
Want to call a form procedure (handler) written on the button thru personalization .
The handler OE_ORDER_CONTRO L.Availability_ button('WHEN-BU TTON-PRESSED') is called from AVAILABLITY
button on Quick Sales order form which refreshes the 'Availibility window'. How can this be done thru personalization after entering an order line item?
thanks
='declar e v_field_value VARCHAR2(200); begin po_approve_c1.f orward_check('' WHEN-CHECKBOX-C HANGED''); end'
The "po_approve_c1" is defined in one of the attached libraries and the syntax above is not working properly. Does anyone has any example how to execute the procedure defined in one of the attached libraries?
some how i am writing the following and not able to get rid of the ora-00923 can you please guide me
begin
IF FND_PROFILE.SAV E_USER ('XXPS TEST', '''||
${global. org_name_prof.v alue}||''') THEN
commit;
END IF;
end
below one is working fine
begin
IF FND_PROFILE.SAV E_USER ('XXUSPS TEST', 'CHICAGO') THEN
commit;
END IF;
end
i amnot sure if its due to some variance in single quote ..?
I am using the follwing code to execute a procedure in form personalization s:
='declare
v_ desc varchar2(240) := ${ITEM.LINE.USE R_ITEM_DESCRIPT ION.VALUE};
BEG IN
update mtl_system_item s_b
set description=v_d esc
,last_updat ed_by='''||${IT EM.LINE.CREATED _BY.VALUE}||'''
,last_update_d ate=sysdate
whe re inventory_item_ id='''||${ITEM. LINE.INVENTORY_ ITEM_ID.VALUE}| |''';
update mtl_system_item s_tl
set description=''' ||${ITEM.LINE.U SER_ITEM_DESCRI PTION.VALUE}||' ''
where inventory_item_ id='''||${ITEM. LINE.INVENTORY_ ITEM_ID.VALUE}| |''';
commit;
However, it is failing in runtime because the value in field LINE.USER_ITEM_ DESCRIPTION has quotes, for example: 8' x 7".
Is there a solution for this?
Thanks!
Thanks for your time.
I added a custom form to capture further details of a line item. That data is saved in a custom table.
I call this form using special menu entry added by forms customization.
I use forms personalization on the standard form to create global variables and then I use forms personalization on my custom form to auto-populate fields on my custom form.
Now after that, once I enter other line details in my custom form and save it, it saves.
Now again from the standard form, I change a field and then re-open the custom form. As that custom form is based on the custom table, this time as the data already exists, it fetches from the custom table and populates the form.
Due to this, that changed value on the standard form is not reflected in the custom form, as it was not saved in the custom table.
For this I added a personalization . This personalization is called in the trigger event when I open the form via that special menu.
The code I used is:
DECLARE
v_label_number VARCHAR2 (30)
:= ''' || ${item.LOT_ENTR Y.LOT_NUMBER.va lue} || ''';
v_sec_qty NUMBER
:= ''' || ${item.LOT_ENTR Y.SECONDARY_QUA NTITY.value} || ''';
BEGIN
UPDATE xxtbz_label_hea der_v
SET pieces = v_sec_qty
WHERE label_number = NVL (v_label_number , '0');
COMMIT;
EXCEPTI ON
WHEN OTHERS
THEN
NULL;
END
This is not getting triggered. Please help.
I have a requirement in form personaization where the user needs to be restricted from seeing the Expense Report type of invoices from Invoice workbench form. Please provide your inputs as am new to form personalization .
Thanks,
Vina y
I am trying to call a URL from FP. The URL has to be buildt using a query/or a package with invoice_id from the form in the where condition.
I have tried several solutions with no luck.
I am now able to call a function that returns the URL into a pl/sql variable called l_url. The question now is how can i use l_url in the buildt in that calls a URL?
Thanks
Ro y Feirud
Iam using the syntax as mentioned by you, till iam facing error mentioning not able to evaluate the string.
Code: 'begin xxfin_col_ptp_w f_pkg.get_varia bles (''' || ${item.IEXPYPRS _PTP_LIST.DELIN QUENCY_ID.value } || ''' ); end'
Kindly help.
I have a following requirement.
I have a Custom DFF on AR Receipts form. The receipt when saved, creates a deduction, whose data gets reflected in Claims (Channel revenue management). I need to update this Claim with the data from custom DFF after successful creation of the reciepts.
Can we achieve this through forms personalization . If so can you guide in this regards.
Thank s,
Sandeep
I woudl like to display on hand quantity based on unit of issue and not unit of measure. Can I do this using personalisation ?
Please help.
Thanks
K umar
Is it possible to execute a procedure in FP which has OUT variable..and we can assign these OUT values in Global variable.
Pleas e suggest.
Thank s,
Manish
Thanks for your great work and a amazing good book about Apps Customizations. It's very usefull material!
Can you (or anyone) help me with the following. I want to be able to refresh a form automatically using FP (or custom pll if needed). How can I execute the logic which fires of when I normally select" Actions > Refresh in a standard form. Is it a special event which I can call from FP?
Hope you can help me with the question.
Than ks,
Bob
I am calling below procedure through Form personalization at suppose sequence 10. I want, if this procedure completes only
then the form personalization goes to the next sequence 20. Please tell me how can I achieve this.
PROCEDUR E wait_till_inser t(p_in_delivery _id IN NUMBER
,p_in_op_code IN VARCHAR2) IS
lv_cnt NUMBER := 0;
BEGIN
LOOP
SELECT COUNT(1)
INTO lv_cnt
FROM zeb_ont_ship_if ace_err_stg
WHERE delivery_id = p_in_delivery_i d
AND operation_code = p_in_op_code;
EXIT WHEN lv_cnt > 0;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
May i ask for you expertise on, what should be quite simple a task.
I am using a personalization to validate an input against a given format, and am using a custom procedure with 2 inputs and 2 outputs.
The personalization I have knocked up is as follows
Trigge r event: When validate record
Trigger object: Forecast_sets
C ondition: :FORECAST_SETS. DISABLE_DATE IS NULL AND :FORECAST_SETS. FORECAST_DESIGN ATOR NOT LIKE ('%_FcBOM')
Ac tions:
Property - Local variable - XX_IN_NAME-valu e =${item.forecas t_sets.forecast _designator.val ue}
Property - Local variable - XX_IN_MASK-valu e =AAA_N_AAA ( or what ever we fancy)
Propert y - Local variable - XX_BOOLEAN-valu e =NULL
Property - Local variable - XX_MESG - value =NULL
Builtin - Execute a procedure - Argument
='DECLARE
Vfie ld1 varchar2(1);
Vf ield2 varchar2(500);
BEGIN
'XXMRP.XXMRP_F ORECAST_NAMING ('''||${VAR.XX_ IN_NAME.VALUE}| |''','''||${VAR .XX_IN_MASK.VAL UE}||''','''||$ {VAR.XX_BOOLEAN .VALUE}||''','' '||${VAR.XX_MES G.VALUE}||''')' ;
commit;
END'
.
.
Other sequenced bits here.
However the procedure call always fails with ORA-00923 'FROM' keyword not found where expected.
If i move the above code into TOAD and use test values instead of the local variable values it works fine.
You help would be gratefully received as this is become a little too frustrating :S
many thanks in advance
Sparqu i
I managed to sort my issue out.
It was as follows:
local variables declared in seperate personalization - active on trigger "When-New-Block "
Dropped the procedure completely, recreated as a Function returning a VARCHAR2 value of 'TRUE' or 'FALSE'
Droppe d 2 parameters for the function call, leaving just the inputs (from 4 parameters to just 2).
Called the function from the conditions tab of the personalisation using the following convention
(Co ndition) Pkg.Function_na me(${VAR.local variable1.VALUE },${VAR.local variable2.VALUE }) = 'TRUE'.
So in my example it reads:
(Condit ion)
:FORECAST_SETS .DISABLE_DATE IS NULL AND :FORECAST_SETS. FORECAST_DESIGN ATOR NOT LIKE ('%_FcBOM') AND XXMRP.XXMRP_FOR ECAST_NAMING (${VAR.XX_IN_NA ME.VALUE},${VAR .XX_IN_MASK.VAL UE}) = 'TRUE'
Worked a treat - hope this helps someone else
Regards
Sparqui
how to display popup message with multiple rows select stmt through the form personalization .
Regards,
nar esh
Your blogs are very usefull, I thank you for them.
My requirement is the Request date in the Sales quote form in the OM module should be not equal to sysdate while saving, it should throw error if the user puts the sysdate in the Request Date using Form Personalization .
Thanks
Rupal i Sarode
Your blogs have always been a great support to me. I have found all most all my question's answer so far
from your blog. Here is something I have been struggling on lately
1. I have created a workflow which will trigger when a newn supplier site code is created.
2. I have already created the PL/SQL procedure which will fire the workflow. The workflow runs fine.
3. I have personalized the Supplier Site form using when new record instance action type builtin and Type Execute a procedure .
here I have given 'begin XX_Notif_supp_c reation_wf_pkg. START_WF('''||$ {item.SITE.VEND OR_SITE_ID.valu e}||''');end'
4. The procedure runs fine but it is supposed to run after I have created a supplier site but instead of doing this it fires when I press sites button.
How can I resolve this issue?
Regards ,
Rubayat
I need to call a stored procedure in when-button-pre ssed trigger using form personalization ? Can anyone give me the steps. High prirority task. Any help is greatly appreciated.
Th ansk
Valli
Run it in key-commit trigger and after the procedure write commit_form.
I'm new in using Forms Personalization just want to ask some questions regarding this..
I am doing form personalization in standard PO form. It needs to trigger form personalization on button click.
Lets assume we need to show the message on button click based on some condition.
How can I achieve this ?
Please suggest.
Thanks
Mayank
jmomdzcvcxbnytx nap
good, keep up writing.
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'
find this matter to be actually something that I believe I would by no
means understand. It seems too complex and extremely
wide for me. I'm looking forward in your next put up, I'll attempt to get the grasp of it!
It was practical. Keep on posting!
clicked submit my comment didn't appear. Grrrr... well I'm not writing all that over again. Anyhow, just wanted to say great blog!
I quite enjoyed reading it, you are a great author.
I will make sure to bookmark your blog and will come back very soon. I want to encourage you to ultimately continue your
great job, have a nice afternoon!
Reading this post reminds me of my good old room mate!
He always kept talking about this. I will forward this article to him.
Fairly certain he will have a good read. Many thanks for sharing!
RSS feed for comments to this post