Query to find concurrent program responsibility in Oracle apps

Hi, This post will show the query to retrieve responsibility details for a concurrent program in Oracle apps.

Query to find concurrent program responsibility:

SELECT frt.responsibility_name,

               frg.request_group_name,

               frg.description

  FROM fnd_request_groups frg

             ,fnd_request_group_units frgu

             ,fnd_concurrent_programs fcp

             ,fnd_concurrent_programs_tl fcpt

             ,fnd_responsibility_tl frt

             ,fnd_responsibility frs

 WHERE frgu.unit_application_id = fcp.application_id

 AND   frgu.request_unit_id = fcp.concurrent_program_id

 AND   frg.request_group_id = frgu.request_group_id

 AND   frg.application_id = frgu.application_id

 AND   fcpt.source_lang = USERENV('LANG')

 AND   fcp.application_id = fcpt.application_id

 AND   fcp.concurrent_program_id = fcpt.concurrent_program_id

 AND   frs.application_id = frt.application_id

 AND   frs.responsibility_id = frt.responsibility_id

 AND   frt.source_lang = USERENV('LANG')

 AND   frs.request_group_id = frg.request_group_id

 AND   frs.application_id = frg.application_id

 AND   fcpt.user_concurrent_program_name LIKE <Concurrent program name>;

Cheers!!

How to pass multiple values to a parameter in concurrent program in Oracle apps

Hi! In this post, I will show you how to pass multiple values separated by comma in concurrent program parameter in Oracle apps.

To achieve the same requirement in the database, we can use lexical parameter like below. It retrieves the department names based on the department numbers that we pass in the p_deptno parameter.

SELECT dname "Department" FROM dept WHERE deptno IN (&p_deptno);

However, the above format won't work in concurrent program parameter but it can be achieved using REGEXP. The below code shows a sample procedure that uses a cursor with SELECT query to achieve the same requirement that can be used in concurrent programs.

To pass multiple values separated by commas in Concurrent program parameter:

CREATE OR REPLACE PROCEDURE getDept (p_deptno IN VARCHAR2)

AS

CURSOR get_dept_cur IS SELECT dname FROM dept WHERE deptno IN 

(SELECT REGEXP_SUBSTR(UPPER(p_deptno), '[^,]+', 1, level) FROM dual

CONNECT BY REGEXP_SUBSTR(p_deptno, '[^,]+', 1, level) IS NOT NULL);

BEGIN

FOR i IN get_dept_cur LOOP

dbms_output.put_line(i.dname);

END LOOP;

END;

/

EXEC getDept('10,20,30,40');

Hope this helps. Happy learning!