Sure Apps
Welcome
Home
Login/Register
Commands
Sql
Pl/Sql
Reports
Forms
Oracle Apps
FAQs
Sql
Pl/Sql
Reports
Forms
Oracle Apps
Learn
Sql
Pl/Sql
Reports
Forms
Oracle Apps

Few PL/Sql Commands

1. To display messages from pl/sql in any sql editor.
dbms_output.put_line('Message'); If we want to display the variable value then no need to mention in the quotes.

2. To display messages from pl/sql in Oracle Applications log file.
fnd_file.put_line(fnd_file.log,'Message'); If we want to display the variable value then no need to mention in the quotes.

3. To display messages from pl/sql in Oracle Applications output file.
fnd_file.put_line(fnd_file.output,'Message'); If we want to display the variable value then no need to mention in the quotes.

4. To make a format mask for the date parameter.
When we pass a value for date parameter using FND_STANDARD_DATE and if we use a date data type for IN type parameter in a procedure, it fails. Because application sends the value as YYYY/MM/DD HH24:MI:SS and procedure expects DD-MON-YYYY or DD/MM/YYYY.

So we get the error Cause: FDPSTP failed due to ORA-01861: literal does not match format string. To avoid this please follow the below steps.

a.) Instead Date type data type make the IN parameter data type as VARCHAR2 in procedure definition.

b.) Declare one local variable with the date data type.

c.) Apply the following format mask to the received parameter value and assign it to the declared variable.

l_date := TRUNC (TO_DATE (p_date, 'YYYY/MM/DD HH24:MI:SS'));


5. To Initialize apps environment in pl/sql to submit concurrent programs.
First derive values for the user id, responsiblity id and resposiblity Application id and use the function to set the environment.

a.) SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = [username];

b.) SELECT responsibility_id INTO l_resp_id FROM fnd_responsibility WHERE responsibility_key = [responsibility_key];

c.) SELECT responsibility_application_id INTO l_resp_appl_id FROM fnd_user_resp_groups WHERE user_id = l_user_id AND responsibility_id = l_resp_id;

l_user_id and l_resp_id are already derived from first and second query. And now use the following function to initialize.

fnd_global.apps_initialize (user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_resp_appl_id);


6. To set the org context, i.e. multi org to view the data of a single operating unit
MO_GLOBAL.SET_POLICY_CONTEXT('M',org_id);.


7. To add a layout to the XMl/BI publisher report if we are submitting from pl/sql
l_xml_layout := FND_REQUEST.ADD_LAYOUT('[application short name]',l_rtf_template,'en','','PDF');

In above case l_xml_layout is a varibale of BOOLEAN type and l_rtf_template is the template code. And in place of pdf we can have other output formats like Excel or html etc..


8. To submit the concurrent program from pl/sql.
We need to call a function to submit the program;

l_request_id :=FND_REQUEST.SUBMIT_REQUEST('[application short name]',
[program_short_name],
[program_desc],
SYSDATE,
FALSE,
parameter1,...parameter100);

In above the pass the values for application short name concurrent program short name and program description name. Also pass the parameters, we need to pass only those parameters which are part of program definition. We can pass upto 10 parameters, we can pass the remaining parameters as nulls or even it works if we do not mention all other parameters since they are defualed to NULL.

And make sure we issue the COMMIT after the above function.
DisclaimerContact UsPrivacy Policy