Responsibilities using Concurrent Program

We know to use any Concurrent Program first it needs to be added to the Request Group and Request Group needs to be attached to Responsibility. Then we can able to submit the program using program request form.

One program can be attached to many Request Groups. Again Request Group can be attached to many Responsibilities. Because of One-Many-Many relationship, it is very difficult to find out responsibilities using particular concurrent program in UI forms.

But we can get the list from database using the following SQL...

SELECT
FRS.responsibility_key
,FRS.responsibility_name
,FRG.request_group_name
,FCP.concurrent_program_name
,FCP.user_concurrent_program_name
FROM
fnd_concurrent_programs_vl FCP
,fnd_request_group_units FRU
,fnd_request_groups FRG
,fnd_responsibility_vl FRS
WHERE FCP.concurrent_program_name = (Program_Name)
AND FRU.request_unit_id = FCP.concurrent_program_id
AND FRG.application_id = FRU.application_id
AND FRG.request_group_id = FRU.request_group_id
AND FRS.group_application_id = FRG.application_id
AND FRS.request_group_id = FRG.request_group_id

SQL to get list of Scheduled Concurrent Programs

Any Oracle Application (EBS) user can schedule Concurrent Programs, if he has access to it. Down the line, it is very difficult for users/administrators/developers to remember list of schedules concurrent programs. Following SQL will help to get the latest list of Scheduled Concurrent Programs.

SELECT FCR.request_id REQUEST_ID
,FCP.concurrent_program_name PROGRAM_SHORT_NAME
,FCP.user_concurrent_program_name PROGRAM_NAME
,FNU.user_name SUBMITTED_BY
,TO_CHAR(FCR.requested_start_date
,'DD-MON-YYYY HH24:MM:SS'
) REQUEST_START_DATE
,'Every '|| DECODE(LENGTH(FCL.class_info)
,39,FCL.class_info
,SUBSTR(FCL.class_info,1,INSTR(FCL.class_info,':',1)-1)||' '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1)+1,1)
,'N','Minute(s) '
,'D','Day(s) '
,'H','Hour(s) '
,'M','Month(s) '
)
|| 'after '
|| DECODE(SUBSTR(FCL.class_info,INSTR(FCL.class_info,':',1,2)+1,1)
,'S','Start '
,'C','Completion '
)
|| 'of prior request'
) SCHEDULED_INTERVAL
,NVL(TO_CHAR(FCL.end_date_active
,'DD-MON-YYYY'),'forever'
) ENDING_ON
FROM APPS.fnd_concurrent_requests FCR
,APPS.fnd_concurrent_programs_vl FCP
,APPS.fnd_user FNU
,APPS.fnd_conc_release_classes FCL
WHERE FCR.phase_code = 'P'
AND FCR.status_code IN ('I','Q')
AND FCR.program_application_id = FCP.application_id
AND FCR.concurrent_program_id = FCP.concurrent_program_id
AND FCR.requested_by = FNU.user_id
AND FCR.release_class_app_id = FCL.application_id
AND FCR.release_class_id = FCL.release_class_id
ORDER BY FCP.concurrent_program_name
, FCR.requested_start_date

Submitting XML Publisher Report Concurrent Program from PL/SQL

XML Publisher template will be attached as layout when we submit concurrent program using request form based on the program short name. Whereas, if we submit the program using FND_REQUEST.submit_request API then we will find only XML output, template won’t be used.

To avoid this issue, program layout needs to be set using FND_REQUEST.add_layout API with template application, code, language, territory and output format before submit request.