How to set org context in Oracle apps R12 and 11i

Set org context in R12


The SQL command to set the ORG_ID prior to running a script is:


SQL> exec mo_global.init('AR');
exec mo_global.set_policy_context('S','&org_id');
Enter the org_id when prompted.


The procedure - mo_global.set_policy_context has two parameters 
p_access_mode & p_org_id


p_access_mode          Description
S In case you want your current session to work against Single ORG_ID
M In case you want your current session to work against multiple ORG_IDs


p_org_id: Only applicable if p_access_mode is passed value of "S"


If using Toad
Begin
mo_global.set_policy_context(‘S’, &org_id);
End;


Set org context in 11i:


The SQL command to set the ORG_ID prior to running a script is:
SQL> execute dbms_application_info.set_client_info(&org_id);
Enter the org_id when prompted.
If using Toad
Begin
fnd_client_info.set_org_context(&org_id);
End;

Create XML Publisher Report(With out RDF file)

Required Files:
Template file: HGXPUBTEST.rtf
Data Template: HGXPUBTEST.xml

Output Files:
Output in PDF: HGXPUBTEST_OUTPUT.pdf

Define Concurrent Program
Navigate to System Administrator->Concurrent->Program->Define
Provide folliowing information as shown in the picture.
Program: HG-XML Publisher Test Report
Short Name: HGXPUBTEST
Application: Payables
Executable Name: XDODTEXE
Format: XML

Points To Note:
1.Note down short name. We need to use this XML Publisher defintion to refer this CP.
2.Select executable name as "XDODTEXE" if xml data source is data template(HGXPUBTEST.xml). Method is Java Concurrent Program. If you are generating xml data using Oracle Reports(RDF), then you must define executable(System Administrator->Concurrent->Program->Executables) for this CP and provide it's name in CP define window. Method will be Oracle Reports in this case.
3.XML Publisher engine needs data input in XML format. So always select format is XML.( Final output will be PDF/RTF/HTML.. as selected by you in report submission.)
4.If you want to see final output in XML format then you need set format type as Text instead of XML.

CP definition looks like this after creation.

Add new CP to the Request Group, then add Request Group to your Responsibilty.

Create Data Definition
Navigate to XML Publisher Administrator->Data Definitions
Click Create Data Definition button.

Provide folliowing information as shown in the picture.
Code: HGXPUBTEST
Name: HG-XML Publisher Test Report(your choice)
Application: Payables(your choice)
Click Apply
In the second part of the same page upload file HGXPUBTEST.xml as the Data Template.
Click Save.

Points To Note:
1.Value given in the Code field must be same as the short name of the CP 'HG-XML Publisher Test Report'.
2.HGXPUBTEST.xml is the data template file which holds Paramters, Data Query, Triggers, Data Structure of the report.

Definition looks like this after creation.

Create Template
Navigate to XML Publisher Administrator->Templates
Click Create Template button.

Provide folliowing information as shown in the picture.
Code: HGXPUBTEST(your shoice)
Name: HG-XML Publisher Test Report(your choice)
Application: Payables(your choice)
Type: RTF
Data Definition: HG-XML Publisher Test Report
Upload template HGXPUBTEST.rtf.
Click Apply

Template looks like this after creation.

Submit Request
If you want final output in different format(RTF, HTML, etc), click Options button and change the Format type. Default is PDF.

CP Output(PDF)

Status_Code and Phase_Code meaning in FND_CONCURRENT_REQUEST table

STATUS_CODE Column:

A - Waiting

B - Resuming

C - Normal

D - Cancelled

E - Error

F - Scheduled

G - Warning

H - On Hold

I - Normal

M - No Manager

Q - Standby

R - Normal

S - Suspended

T - Terminating

U - Disabled

W - Paused

X - Terminated

Z - Waiting

PHASE_CODE column

C - Completed

I - Inactive

P - Pending

R - Running

Oracle Flashback Query: Recovering at the Row Level (Recover deleted rows)

In a data recovery context, it is useful to be able to query the state of a table at a previous time. If, for instance, you discover that at 12:30 PM, an employee 'JOHN' had been deleted from your EMPLOYEE table, and you know that at 9:30AM that employee's data was correctly stored in the database, you could query the contents of the table as of a time before the deletion to find out what data had been lost, and, if appropriate, re-insert the lost data in the database.

Querying the past state of the table is achieved using the AS OF clause of the SELECT statement. For example, the following query retrieves the state of the employee record for 'JOHN' at 9:30AM, April 4, 2003:

SELECT * FROM EMPLOYEE AS OF TIMESTAMP 
   TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
   WHERE name = 'JOHN';

Restoring John's information to the table EMPLOYEE requires the following update:

INSERT INTO employee 
    (SELECT * FROM employee AS OF TIMESTAMP 
     TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
     WHERE name = 'JOHN');

The missing row is re-created with its previous contents, with minimal impact to the running database.

See Also: Oracle Database Application Developer's Guide - Fundamentals for a more extensive discussion of the use of the SELECT... AS OF SQL statement and extensive examples of its use. Oracle Database SQL Reference for more details on the syntax of the SELECT... AS OF form of the SELECT statement.

How to Register a Concurrent Program with a Responsibility

1.      Login into System Administrator responsibility
2.      Navigate to Security / Responsibility / Define
3.      Query up the responsibility that is being used
4.      Note the name of the Request Group
5.      Navigate to Security / Responsibility / Request
6.      Query up the Request Group
7.      Add the report under Requests
8.      Save it

Sequence of SQL statement processed -- basic knowledge to increase the performance of your script

Sequence of SQL statement processed

When a query is submitted to the database, it is executed in the following order:

FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause

The following diagram will show a clear picture:

So why is it important to understand this?
When a query is executed, First all the tables and their join conditions are executed filtering out invalid references between them.

Then the WHERE clause is applied which again filters the records based on the condition given. Now you have handful of records which are GROUP-ed
And HAVING clause is applied on the result. As soon as it is completed, the columns mentioned are selected from the corresponding tables. And finally sorted using ORDER BY clause. So when a query is written it should be verified based on this order, otherwise it will lead wrong result sets.

Example:
Recently I came across the following problem.

The original query wasSELECT DISTINCT so.departure_airport, so.airport_name
FROM special_offers so, special_offers_groups_join sogj
WHERE so.resort_code = '000170' AND so.accomodation_code = '015397'
AND so.departure_date = '2008-01-13'
AND so.nights = '7'
AND sogj.group_id = '1'
AND sogj.special_offer_id = so.special_offer_id
ORDER BY price ASC
Table data:
departure_airport    airport_name                       price
'MAN'                      'Manchester Airport'           344
'MAN'                      'Manchester Airport'           288
'MAN'                      'Manchester Airport'            316
'BRS'                       'Bristol'                             289
'BRS'                       'Bristol'                             345
'BRS'                       'Bristol'                             317
'BHX'                      'Birmingham Airport'             343
'BHX'                      'Birmingham Airport'             287
'BHX'                      'Birmingham Airport'             315

Here the query is executed as follows:

All the records from both the tables are taken.

Conditions mentioned in the WHERE clause is applied fetching say 9 records.
Now the distinct of departure_airport and airport_name alone is taken since price is not in SELECT clause. This gives you three rows randomly selected (mostly in the order of insertion to the table!)
And the result is sorted based on price (incorrect price column due to distinct keyword).
So the output is not ordered properly based on price!

So we need to rewrite the query as follows:

All the records from both the tables should be taken.

Conditions mentioned in the WHERE clause needs to applied on the resulting resultset.

Group the resultset based on departure_airport and airport_name to get the minimum price in each group.

Departure_airport               airport_name                                   minprice
'BHX'                                'Birmingham Airport'                       287
'BRS'                                 'Bristol'                                       289
'MAN'                                'Manchester Airport'                      288

Select the columns departure_airport, airport_name and min(so.price) from the resultset. And finally apply the ORDER By clause which orders the resultset based on the column min(so.price).

So the query should be:SELECT so.departure_airport, so.airport_name, MIN(so.price) AS minprice
FROM special_offers so, special_offers_groups_join sogj
WHERE so.resort_code = '000170'
AND so.accomodation_code = '015397'
AND so.departure_date = '2008-01-13'
AND so.nights = '7'
AND sogj.group_id = '1'
AND sogj.special_offer_id = so.special_offer_id
GROUP BY so.departure_airport,so.airport_name
ORDER BY minprice;

Where clause and from clause for better performance: In SQL

Order of tables in the FROM clause should be:

1. Larger table

2. Smaller table

Order of conditions in WHERE clause should be:

2. Less selective

1. More selective

All Boolean conditions without built-in functions or sub queries are evaluated in reverse from the order they are found in the WHERE clause, with the last predicate being evaluated first.

How to submit a concurrent program from pl sql

Using FND_REQUEST.SUBMIT_REQUEST function & by passing the required parameters to it we can submit a concurrent program from backend.
But before doing so, we have to set the environment of the user submitting the request.

We have to initialize the following parameters using FND_GLOBAL.APPS_INITIALIZE procedure:
· USER_ID
· RESPONSIBILITY_ID
· RESPONSIBILITY_APPLICATION_ID


Syntax ............

FND_GLOBAL.APPS_INITIALIZE:
procedure APPS_INITIALIZE(user_id in number,
resp_id in number,
resp_appl_id in number);


FND_REQUEST.SUBMIT_REQUEST:
REQ_ID := FND_REQUEST.SUBMIT_REQUEST ( application => 'Application Name', program => 'Program Name', description => NULL, start_time => NULL, sub_request => FALSE, argument1 => 1 argument2 => ....argument n );

Where, REQ_ID is the concurrent request ID upon successful completion.
And concurrent request ID returns 0 for any submission problems.

Example:
First get the USER_ID and RESPONSIBILITY_ID by which we have to submit the program:

SELECT USER_ID,
RESPONSIBILITY_ID,
RESPONSIBILITY_APPLICATION_ID,
SECURITY_GROUP_ID
FROM FND_USER_RESP_GROUPS
WHERE USER_ID = (SELECT USER_ID
FROM FND_USER
WHERE USER_NAME = '&user_name')
AND RESPONSIBILITY_ID = (SELECT RESPONSIBILITY_ID
FROM FND_RESPONSIBILITY_VL
WHERE RESPONSIBILITY_NAME = '&resp_name');


Now create this procedure
CREATE OR REPLACE PROCEDURE APPS.CALL_RACUST (p_return_code OUT NUMBER,
p_org_id NUMBER, -- This is required in R12
p_return_msg OUT VARCHAR2)
IS
v_request_id VARCHAR2(100) ;
p_create_reciprocal_flag varchar2(1) := 'N'; -- This is value of create reciprocal customer
-- Accounts parameter, defaulted to N
BEGIN


-- First set the environment of the user submitting the request by submitting
-- Fnd_global.apps_initialize().
-- The procedure requires three parameters
-- Fnd_Global.apps_initialize(userId, responsibilityId, applicationId)
-- Replace the following code with correct value as get from sql above

Fnd_Global.apps_initialize(10081, 5559, 220);

v_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST('AR','RACUST','',
'',FALSE,p_create_reciprocal_flag,p_org_id,
chr(0) -- End of parameters);

p_return_msg := 'Request submitted. ID = ' || v_request_id;
p_return_code := 0; commit ;


EXCEPTION

when others then
p_return_msg := 'Request set submission failed - unknown error: ' || sqlerrm;
p_return_code := 2;
END;


Output:
DECLARE
V_RETURN_CODE NUMBER;
V_RETURN_MSG VARCHAR2(200);
V_ORG_ID NUMBER := 204;
BEGIN
CALL_RACUST(
P_RETURN_CODE => V_RETURN_CODE,
P_ORG_Id => V_ORG_ID,
P_RETURN_MSG => V_RETURN_MSG
);
DBMS_OUTPUT.PUT_LINE('V_RETURN_CODE = ' || V_RETURN_CODE);
DBMS_OUTPUT.PUT_LINE('V_RETURN_MSG = ' || V_RETURN_MSG);
END;


If Return Code is 0(zero) then it has submitted the Customer Interface program successfully and the request id will appear in Return Message as :
V_RETURN_CODE = 0
V_RETURN_MSG = Request submitted. ID = 455949