How to find the SID information for a running request

Always good to know the long running request SID information of apps for analysis and troubleshoot purpose. The following query will help to identify that, pass long running 'request id' as parameter

SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
v$process c,
v$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';


Cheers!!!

How to set the Organization Context in R12

To set org context to any specific single org:

BEGIN
   MO_GLOBAL.SET_POLICY_CONTEXT('S', ); 
END;

To set the mo security profile so that sqlplus will have same org context as given responsibility

DECLARE
   l_resp_id number;
   l_user_id number;
   l_security_profile_id number;
   l_org_id number;

BEGIN

   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name LIKE '&USER_NAME';

   SELECT responsibility_id
     INTO l_resp_id
     FROM FND_RESPONSIBILITY_VL
    WHERE responsibility_name LIKE '&responsibility_name';

   FND_GLOBAL.APPS_INITIALIZE(l_user_id,
                              l_resp_id,
                              ); -- APPLICATION_ID: Oracle Payables is 200 

   MO_GLOBAL.INIT('SQLAP');

END;
/

How To Set the Applications Context - FND_GLOBAL.APPS_INITIALIZE

In Release 12, the architecture of multi-org and the way in which data is partitioned by operating unit has changed significantly. As a result the ways in which data are stored and accessed has changed.

To set the Application Context value, call procedure
FND_GLOBAL.APPS_INITIALIZE(user_id in number,resp_id in number, resp_appl_id in number security_group_id in number);

This procedure sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session. You can use it for routines such as PL/SQL or other programs that are not integrated with either the Oracle Applications concurrent processing facility or Oracle Forms (both of which already do a similar initialization for a database session). The typical use for this routine would be as part of the logic for launching a separate non–Forms session from an established Oracle Applications form session. You can also use this procedure to set up a database session for manually testing application code using SQL*Plus. This routine should only be used when the session must be established outside of a normal form or concurrent program connection.

EXEC FND_GLOBAL.APPS_INITIALIZE(p_user_id, p_resp_id, p_resp_appl_id);

To get the values of
p_user_id,
p_resp_id
p_resp_appl_id
use the following sql from sqlplus as apps user:

select application_id, Responsibility_id, responsibility_name
from fnd_responsibility_vl
where responsibility_name like 'your_reponsibility_name'

select user_id, user_name
from fnd_user
where user_name = 'your_user_name'

For example:
exec fnd_global.APPS_INITIALIZE (1271,50243,222);

OR:

You can obtain valid values to use with this procedure by using profile option routines to retrieve these values in an existing Oracle Applications form session. 

USER_ID - The User ID number.

RESP_ID - The ID number of the responsibility.

RESP_APPL_ID - The ID number of the application to which the responsibility belongs. 

SECURITY_GROUP_ID - The ID number of the security group. This argument is automatically defaulted by the API. The caller should not pass a value for it. 

You can obtain the values for the input parameters from within Oracle Applications.

To do this:
1. From the top menu, navigate to Help->Examine
2. You will be asked to enter an ORACLE password. Enter the password for the APPS database user.
3. Click on the LOV for Block, and select block $PROFILES$
4. Click on the Field LOV, and select field USER_ID
5. Make a note of the value returned in the Value field.
6. Click on theField LOV again, and select field RESP_ID
7. Make a note of the value returned in the Value field.

Use these values for the USER_ID nad RESP_ID parameters. You can get the application Id from the Diagnostic Apps Check report.

Products must call the MO_GLOBAL.init() API to execute the multiple organizations initialization.

Multiple organizations initialization performs the following:

1.Initializes the security policy predicate
2.Populates a global temporary table that is used in the user interfaces and the security policy function.
The FND_GLOBAL.APPS_INITIALIZE routine does NOT automatically call mo_global.init routine.
You must explicitly invoke the mo_global.init routine to initialize the organization context.

EXEC mo_global.init (p_appl_short_name);

To find out the latest AD patch-set from 11i

select bug_number, decode(bug_number
,'1351004' ,'11i.AD.A'
,'1460640' ,'11i.AD.B'
,'1475426' ,'11i.AD.C'
,'1627493' ,'11i.AD.D'
,'1945611' ,'11i.AD.E'
,'2141471' ,'11i.AD.F'
,'2344175' ,'11i.AD.G'
,'2673262' ,'11i.AD.H'
,'4038964' ,'11i.AD.I.1'
,'4229931' ,'11i.AD.I.2'
,'4337683' ,'11i.AD.I.2'
,'4502904' ,'11i.AD.I.3'
,'4605654' ,'11i.AD.I.4 Delta.4'
,'4712847' ,'11i.AD.I.3'
,'4712852' ,'11i.AD.I.4'
,'5161676' ,'11i.AD.I.5'
,'5161680' ,'11i.AD.I.5'
,'6502079' ,'11i.AD.I.6'
,'6502082' ,'11i.AD.I.6'
,'7429271', '11i AD.I.7'
 ) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number IN ( '1351004' ,'1460640' ,'1475426' ,'1627493' ,'1945611'
      ,'2141471' ,'2344175' ,'2673262' ,'4038964' ,'4229931' ,'4337683' ,
      '4502904' ,'4605654' ,'4712847' ,'4712852' ,'5161676' ,'5161680' ,
      '6502079' ,'6502082','7429271' )

Current ICM logfile and logfile, output file from concurrent request

--To findout the ICM current logfile
SELECT 'LOG=' || fcp.logfile_name LogFile
FROM fnd_concurrent_processes fcp, fnd_concurrent_queues fcq
WHERE fcp.concurrent_queue_id = fcq.concurrent_queue_id
AND fcp.queue_application_id = fcq.application_id
AND fcq.manager_type = '0'AND fcp.process_status_code = 'A';


--to find the log file and output file from concurrent request
SELECT REQUEST_ID,logfile_name, outfile_name, outfile_node_name, last_update_date
FROM apps.FND_CONCURRENT_REQUESTS
WHERE REQUEST_ID =&Req_ID

Standard API to Disable or End Date Responsibility

The delete responsibility happens by adding the end date.

Therefore, first query fnd_user_resp_groups_direct to get the start_date for the existing assignment.
Then use the following API to end date it.

if (fnd_user_resp_groups_api.assignment_exists(
      x.user_id, x.responsibility_id,
      x.RESPONSIBILITY_APPLICATION_ID))
then
    fnd_user_resp_groups_api.update_assignment(
         user_id => x.user_id,
         responsibility_id => x.responsibility_id,
         responsibility_application_id => x.RESPONSIBILITY_APPLICATION_ID,
         start_date => x.start_date,
         end_date => sysdate, 
        description => null);
end if; 

One concern is the user could see the changes are not getting reflected immediately when queried on User Define Form.
These processes are deferred at the workflow level of processing.  It should be no longer than a couple minutes before the changes are reflected in the define users  (FNDSCAUS) form.

Script to check what ATG/Workflow related patches are installed in e-business suite

This script will help you to check what ATG/Workflow related patches are installed in 11i and R12
SELECT BUG_NUMBER,
LAST_UPDATE_DATE,
DECODE (
bug_number,
2728236,
'OWF.G INCLUDED IN 11.5.9',
3031977,
'POST OWF.G ROLLUP 1 - 11.5.9.1',
3061871,
'POST OWF.G ROLLUP 2 - 11.5.9.2',
3124460,
'POST OWF.G ROLLUP 3 - 11.5.9.3',
3126422,
'11.5.9 Oracle E-Business Suite Consolidated Update 1',
3171663,
'11.5.9 Oracle E-Business Suite Consolidated Update 2',
3316333,
'POST OWF.G ROLLUP 4 - 11.5.9.4.1',
3314376,
'POST OWF.G ROLLUP 5 - 11.5.9.5',
3409889,
'POST OWF.G ROLLUP 5 Consolidated Fixes For OWF.G RUP 5',
3492743,
'POST OWF.G ROLLUP 6 - 11.5.9.6',
3868138,
'POST OWF.G ROLLUP 7 - 11.5.9.7',
3262919,
'FMWK.H',
3262159,
'FND.H INCLUDE OWF.H',
3258819,
'OWF.H INCLUDED IN 11.5.10',
3438354,
'11i.ATG_PF.H INCLUDE OWF.H',
3140000,
'ORACLE APPLICATIONS RELEASE 11.5.10 MAINTENANCE PACK',
3240000,
'11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 1',
3460000,
'11.5.10 ORACLE E-BUSINESS SUITE CONSOLIDATED UPDATE 2',
3480000,
'ORACLE APPLICATIONS RELEASE 11.5.10.2 MAINTENANCE PACK',
4017300,
'ATG_PF:11.5.10 Consolidated Update (CU1) for ATG Product Family',
4125550,
'ATG_PF:11.5.10 Consolidated Update (CU2) for ATG Product Family',
5121512,
'AOL USER RESPONSIBILITY SECURITY FIXES VERSION 1',
6008417,
'AOL USER RESPONSIBILITY SECURITY FIXES 2b',
6047864,
'REHOST JOC FIXES (BASED ON JOC 10.1.2.2) FOR APPS 11i',
4334965,
'11i.ATG_PF.H RUP3',
4676589,
'11i.ATG_PF.H.RUP4',
5473858,
'11i.ATG_PF.H.RUP5',
5903765,
'11i.ATG_PF.H.RUP6',
6241631,
'11i.ATG_PF.H.RUP7',
4440000,
'Oracle Applications Release 12 Maintenance Pack',
5082400,
'12.0.1 Release Update Pack (RUP1)',
5484000,
'12.0.2 Release Update Pack (RUP2)',
6141000,
'12.0.3 Release Update Pack (RUP3)',
6435000,
'12.0.4 RELEASE UPDATE PACK (RUP4)',
5907545,
'R12.ATG_PF.A.DELTA.1',
5917344,
'R12.ATG_PF.A.DELTA.2',
6077669,
'R12.ATG_PF.A.DELTA.3',
6272680,
'R12.ATG_PF.A.DELTA.4',
7237006,
'R12.ATG_PF.A.DELTA.6',
6728000,
'12.0.6 RELEASE UPDATE PACK (RUP6)',
7303030,
'12.1.1 Maintenance Pack',
7651091,
'Oracle Applications Technology Release Update Pack 2 for 12.1 (R12.ATG_PF.B.DELTA.2)',
7303033,
'Oracle E-Business Suite 12.1.2 Release Update Pack (RUP2)',
bug_number
)
bug,
ARU_RELEASE_NAME
FROM AD_BUGS b
WHERE b.BUG_NUMBER IN
('2728236',
'3031977',
'3061871',
'3124460',
'3126422',
'3171663',
'3316333',
'3314376',
'3409889',
'3492743',
'3262159',
'3262919',
'3868138',
'3258819',
'3438354',
'3240000',
'3460000',
'3140000',
'3480000',
'4017300',
'4125550',
'6047864',
'6008417',
'5121512',
'4334965',
'4676589',
'5473858',
'5903765',
'6241631',
'4440000',
'5082400',
'5484000',
'6141000',
'6435000',
'5907545',
'5917344',
'6077669',
'6272680',
'7237006',
'6728000',
'7303030',
'7651091',
'7303033')
ORDER BY BUG_NUMBER, LAST_UPDATE_DATE, ARU_RELEASE_NAME;

Cheers!