Create a Trace for Concurrent_program

Check the "Enable Trace" check box for the concurrent program that you want to get the trace file. and Save the changes from Sysadmin Responsibility.

Run the program from the respective Responsibility and notedown the request Id.

you can find the path of trace file using below Query.

select fcr.request_id "Request ID"
--, fcr.oracle_process_id "Trace ID"
, p1.value||'/'||lower(p2.value)||'_ora_'||fcr.oracle_process_id||'.trc' "Trace File"
, to_char(fcr.actual_completion_date, 'dd-mon-yyyy hh24:mi:ss') "Completed"
, fcp.user_concurrent_program_name "Program"
, fe.execution_file_name|| fe.subroutine_name "Program File"
, decode(fcr.phase_code,'R','Running')||'-'||decode(fcr.status_code,'R','Normal') "Status"
, fcr.enable_trace "Trace Flag"
from fnd_concurrent_requests fcr
, v$parameter p1
, v$parameter p2
, fnd_concurrent_programs_vl fcp
, fnd_executables fe
where p1.name='user_dump_dest'
and p2.name='db_name'
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fe.application_id
and fcp.executable_id=fe.executable_id
and ((fcr.request_id = &request_id
or fcr.actual_completion_date > trunc(sysdate)))
order by decode(fcr.request_id, &request_id, 1, 2), fcr.actual_completion_date desc;

--you will be prompted to enter the request_id;

Steps to Move a Concurrent program from one instance(Database) to other

• Define your concurrent program and save it in first instance(for how to register a concurrent program click here)
• Connect to your UNIX box on first instance and run the following command to download the .ldt file
  FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”Concurrent program application short name”CONCURRENT_PROGRAM_NAME=”concurrent program short name”
• Move the downloaded .ldf file to new instance(Use FTP)
• Connect to your UNIX box on second instance and run the following command to upload the .ldt file
  FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt

Script to find the list of responsibility's for which the program assigned

SELECT DISTINCT *
FROM apps.fnd_responsibility_tl
WHERE responsibility_id IN
  (SELECT responsibility_id
  FROM apps.fnd_responsibility_vl
  WHERE request_group_id IN
    (SELECT request_group_id
    FROM apps.fnd_request_group_units
    WHERE request_unit_id =
      (SELECT DISTINCT concurrent_program_id
      FROM Apps.fnd_concurrent_programs_tl
      WHERE user_concurrent_program_name = :Concurrent_Program_name
      )
    )
  AND end_date IS NULL
  )
AND "LANGUAGE" LIKE 'US'
ORDER BY responsibility_name;

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

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

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

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

Script to display status of all the Concurrent Managers

select distinct Concurrent_Process_Id CpId, PID Opid,
            Os_Process_ID Osid,
            Q.Concurrent_Queue_Name Manager,
            P.process_status_code Status,
            To_Char(P.Process_Start_Date, 'MM-DD-YYYY HH:MI:SSAM') Started_At
from   Fnd_Concurrent_Processes P,
            Fnd_Concurrent_Queues Q,
            FND_V$Process
where  Q.Application_Id = Queue_Application_ID
  and  Q.Concurrent_Queue_ID = P.Concurrent_Queue_ID
  and  Spid = Os_Process_ID
  and  Process_Status_Code not in ('K','S')
order  by Concurrent_Process_ID, Os_Process_Id, Q.Concurrent_Queue_Name

Get the complete history of concurrent program.

SELECT DISTINCT fcpt.user_concurrent_program_name,
frg.request_group_name,
fcp.concurrent_program_name,
-- frt.responsibility_name,
-- uncommment the above to get the list of responsibility's
fat.application_name,
fa.APPLICATION_SHORT_NAME,
fa.BASEPATH
FROM fnd_request_group_units frgu,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_request_groups frg,
fnd_executables fe,
fnd_responsibility fr,
fnd_responsibility_tl frt,
fnd_application_tl fat,
fnd_application fa
WHERE 1 = 1
AND fat.application_id = frgu.application_id
AND frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.request_group_id = frg.request_group_id
AND fe.executable_id = fcp.executable_id
AND fcp.concurrent_program_id = fcpt.concurrent_program_id
AND frg.request_group_id = fr.request_group_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.APPLICATION_ID = fat.APPLICATION_ID
AND fcpt.user_concurrent_program_name LIKE "&concurrent_program_name

Enable/Disable the parameter field Dynamically

Introduction : This post provides the guidance to the user with the necessary information for creating parameters for a concurrent program and making them conditionally enable/disable.
Steps to be followed :- 
1.Create a procedure with two input parameters.
2.Create an executable with execution method as PL/SQL Procedure
  and execution file as the procedure created in step1.
3.Create a concurrent program with executable created in step2.
4.Assign the concurrent program to a request set to run the
  concurrent program from a responsibility.
5.Create two two three values sets.
6.Create three parameters for the concurrent program
  created in step 3 using value sets created in step 5.
7.Go to the responsibility to which the concurrent program assigned in step 4 to run the program..
Installation Steps


Step1 : Create procedure 
CREATE OR REPLACE procedure test_proc(
errbuf OUT varchar2,
retcode out NUMBER, 
p_One IN VARCHAR2,
p_two_dummy IN VARCHAR2,
p_two IN NUMBER)
is
begin
fnd_file.put_line (fnd_file.LOG,'Log File');
fnd_file.put_line (fnd_file.OUTPUT,'Out put File' || p_one || '   ' || p_two);
end test_proc;
/
Run above procedure in SQL * Plus / Toad.Step2:  Creating Executable:Navigation :- System Administrator Responsibility → Concurrent →  Program → Executable
Executable : TEST_PROC
Short Name: TEST_PROC
Application: Custom Development
Execution Method: PL/SQL Stored Procedure
Execution File Name : TEST_PROC


Save and close the Concurrent Program executable window.
Step 3: Creation of Values Sets
 3.1 Creating Value set
       Value Set Name  :LAMS_SRS_YES_NO_MAND
       Description     :Yes/No   
       List Type       : List Of Values
       Format          : Char
       Security Type   : No Security
       Validation Type : Table
       Edit Information:
            Table Name : FND_LOOKUPS
            Value      : MEANING         TYPE :VARCHAR2
            ID         : LOOKUP_CODE     TYPE :VARCHAR2
            Where/Order By : WHERE Lookup_type = 'YES_NO'    
Click on Test → Ok → Save and close Validation Table Information window and then value set window.


  3.2 Creating Value set for Dummy Parameter:
      Value Set Name: CST_SRS_MARGIN_ORDER_DUMMY2
      List Type     : List Of Values
      Format        : Char(Check Uppercase Only)
      Security Type : No Security
      Validation Type : None
  
  3.3 Value set for Sales Order Numbers:      Value Set Name : ONT_ORDER
      List Type   : List Of Values
      Format      : Number(Check Numbers Only)
      Security Type : No Security
      Validation Type : Table
      Edit Information:
        Table Name : OE_ORDER_HEADERS_ALL
        Value   : ORDER_NUMBER           TYPE :NUMBER
        Where/Order By : where :$FLEX$.XXLSS_ORDER_DUMMY = 'Y'     


Click on Test → Ok → Save and close Validation Table Information window and then value set window.
 Step 4 : Creating a concurrent Program 


   Program          : TEST_PROC
   Short Name       : TEST_PROC
   Application      : Custom Development
   Executable  Name : TEST_PROC


Save and click on Parameters.
Step 5: Creation of Parameters
  5.1 Yes/No Parameter
         Seq          : 10
         Parameter    : p_one
         Value Set    : AMS_SRS_YES_NO_MAND
         Enabled      : Yes
         Required     : Yes
         Display      : Yes
  5.2Creating Dummy Parameter
         Seq : 15
         Parameter : p_two_dummy
         Value Set    : CST_SRS_MARGIN_ORDER_DUMMY2
         Default Type : SQL Statement
         Default Value: select decode        (:$FLEX$.XXONT_OSR_YES_NO,'Y','Y','N',NULL) from dual
         Enabled   : Yes
         Required  : No
         Display   : No 
  5.3 Creating Conditional Parameter
          Seq : 20
          Parameter :  p_two
          Value Set : ONT_ORDER      
           Enabled   : Yes
           Required  : Yes
            Display   : Yes 


Step 6 : Assigning the concurrent program a request group:Navigation:- System Administrator → Security → Responsibility → Request
Query for the Request group, 'OM Concurrent Programs'
Select Request Type and click on Add New
Type : Program
Name : TEST_PROC
Application : Custom Development
Save and close the Window.
Step7 : Running  the concurrent request:-
Navigation: Order Management Super User → Reports, Requests → Run Requests  →
  Give Program Name as 'TEST_PROC'
 When you select Yes for the first parameter then second parameter become mandatory.

Query to get Request group of a program

Using this query we can get the list of request groups to which our concurrent program has been assigned.

select * from fnd_request_groups where request_group_id IN
(
                             SELECT request_group_id
                               FROM fnd_request_group_units
                              WHERE request_unit_id =
                                       (SELECT DISTINCT concurrent_program_id
                                                   FROM fnd_concurrent_programs_tl
                                                  WHERE user_concurrent_program_name =
                                                           '<Concurrent Program Name>'))

Query to get the list of responsibility's to which concurrent program has assigned

 SELECT DISTINCT *

           FROM apps.fnd_responsibility_tl

          WHERE responsibility_id IN (

                   SELECT responsibility_id

                     FROM apps.fnd_responsibility_vl

                    WHERE request_group_id IN (

                             SELECT request_group_id

                               FROM apps.fnd_request_group_units

                              WHERE request_unit_id =

                                       (SELECT DISTINCT concurrent_program_id

                                                   FROM Apps.fnd_concurrent_programs_tl

                                                  WHERE user_concurrent_program_name =

                                                           '<Concurrent Program Name>'))

                      AND end_date IS NULL)

 AND "LANGUAGE" LIKE 'US'

       ORDER BY responsibility_name

Query to find the user, responsibility and concurrent program details of submitted requests

Select B.user_concurrent_program_name,C.user_name,D.responsibility_name, A.* 
from apps.fnd_concurrent_requests A, apps.fnd_concurrent_programs_tl B, Apps.fnd_user C, apps.fnd_responsibility_tl D
 where 1=1
and  B.user_concurrent_program_name like 
 '<Enter the concurrent program name>'
 and B.concurrent_program_id=A.concurrent_program_id
 and A.requested_by=C.user_id
 and A.responsibility_id=D.responsibility_id
and b.language=userenv('LANG')
and d.language=userenv('LANG')
 order by request_date desc

---------------------------------------

Including the Out put views 


SELECT E.FILE_NAME,B.USER_CONCURRENT_PROGRAM_NAME,C.USER_NAME,D.RESPONSIBILITY_NAME, A.* 
from apps.fnd_concurrent_requests A, apps.fnd_concurrent_programs_tl B, Apps.fnd_user C, apps.fnd_responsibility_tl D , apps.FND_CONC_REQ_OUTPUTS_V E
 WHERE 1=1
and  B.user_concurrent_program_name like  'Hologic Printed Purchase Order Report'
 and B.concurrent_program_id=A.concurrent_program_id
 AND A.REQUESTED_BY=C.USER_ID
 and c.user_name ='LMEDEIROS'
 AND A.RESPONSIBILITY_ID=D.RESPONSIBILITY_ID
 AND E.REQUEST_ID(+)=A.REQUEST_ID
and b.language=userenv('LANG')
and d.language=userenv('LANG')
 order by request_date ;