Oracle eBusiness Suite R12: Initialize Application Context to debug SQLs inside a Concurrent program

Oracle concurrent programs initializes application context before running the actual code. Therefore, if we take out the SQLs from the concurrent program code (PLSQL or Reports) and execute in the development tools like Toad or SQL Developer, the results could be different. It could be due to 3 different kind of context variables

i.  NLS Context variables:- Many language specific views (Eg:- FND_FLEX_VALUES_VL) will be deepndendent on the the NLS language context variable.

ii. Multi Org context variables :- Org specific synonyms(Eg:-PO_HEADERS) in R12 will not return any data unless we initialize the Multi Org context variables.

iii.Security Context variables:- Access to many Application Object library features (Eg:- Profile option values) is possible only after setting the fnd_global security variables (user_id, responsibility_id etc)

The below script accepts concurrent request id as input and initializes the context variables accordingly. The intialized values can be found in DBMS OUTPUT.DECLARE CURSOR cur_requests IS SELECT req.request_id ,req.requested_by ,req.responsibility_id ,req.responsibility_application_id ,req.security_group_id ,req.conc_login_id ,req.program_application_id ,req.concurrent_program_id ,req.priority_request_id ,multi_org_category ,req.org_id FROM fnd_concurrent_requests req ,fnd_concurrent_programs prog WHERE request_id = &request_id AND req.concurrent_program_id = prog.concurrent_program_id; rec_requests cur_requests%ROWTYPE; l_session_id NUMBER; l_string VARCHAR2(4000); BEGIN OPEN cur_requests; FETCH cur_requests INTO rec_requests; CLOSE cur_requests; fnd_global.initialize(session_id => l_session_id ,user_id => rec_requests.requested_by ,resp_id => rec_requests.responsibility_id ,resp_appl_id => rec_requests.responsibility_application_id ,security_group_id => rec_requests.security_group_id ,site_id => NULL ,login_id => NULL ,conc_login_id => rec_requests.conc_login_id ,prog_appl_id => rec_requests.program_application_id ,conc_program_id => rec_requests.concurrent_program_id ,conc_request_id => rec_requests.request_id ,conc_priority_request => rec_requests.priority_request_id ,form_id => NULL ,form_appl_id => NULL ,conc_process_id => NULL ,conc_queue_id => NULL ,queue_appl_id => NULL ,server_id => NULL); -- initialize Multi-Org Context mo_global.init('M'); IF (rec_requests.multi_org_category = 'S') THEN mo_global.set_policy_context(rec_requests.multi_org_category ,rec_requests.org_id); ELSIF (rec_requests.multi_org_category = 'M') THEN mo_global.set_policy_context(rec_requests.multi_org_category ,NULL); END IF; ---Print the initialized values SELECT 'User :- ' || fnd_global.user_name || CHR(13) || 'Responsibility :- ' || fnd_global.resp_name || CHR(13) || 'Application :- ' || fnd_global.application_name || CHR(13) || 'Request ID :- ' || fnd_global.conc_request_id || CHR(13) || 'MO Global Access Mode :- ' || mo_global.get_access_mode || CHR(13) || 'Org Name :- ' || fnd_global.org_name || CHR(13) || 'HR Business Group :- ' || (SELECT NAME FROM hr_all_organization_units WHERE organization_id = fnd_global.per_business_group_id) || CHR(13) || 'Security Profile :- ' || (SELECT security_profile_name FROM per_security_profiles WHERE security_profile_id = fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL')) INTO l_string FROM dual; dbms_output.put_line(l_string); END;