RSS

Tag Archives: requests

Oracle 11i/R12 List all Requests/Programs within a Responsibility

 
SELECT DISTINCT FCPT.USER_CONCURRENT_PROGRAM_NAME,
 FCPT.DESCRIPTION,
 FA_VL.APPLICATION_NAME,
 FA_VL.APPLICATION_SHORT_NAME,
 RSP_TL.RESPONSIBILITY_NAME
 , FRG.REQUEST_GROUP_NAME

FROM FND_REQUEST_GROUPS FRG,FND_CONCURRENT_PROGRAMS_TL FCPT

 INNER JOIN FND_REQUEST_GROUP_UNITS FRGU
 ON FCPT.CONCURRENT_PROGRAM_ID = FRGU.REQUEST_UNIT_ID
 OR FCPT.APPLICATION_ID = FRGU.APPLICATION_ID

 INNER JOIN FND_RESPONSIBILITY FR
 ON FR.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID

 INNER JOIN FND_RESPONSIBILITY_TL RSP_TL
 ON FR.RESPONSIBILITY_ID = RSP_TL.RESPONSIBILITY_ID

 INNER JOIN FND_APPLICATION_VL FA_VL
 ON FA_VL.APPLICATION_ID = FCPT.APPLICATION_ID

WHERE UPPER (FCPT.USER_CONCURRENT_PROGRAM_NAME) LIKE
    NVL (UPPER ('%' || :_PROGRAM_NAME || '%'), '')
  AND UPPER (RESPONSIBILITY_NAME) LIKE
    NVL (UPPER ('%' || :RESPONSIBILITY_NAME || '%'), '')
  AND FCPT.LANGUAGE = USERENV ('LANG')
  AND FRG.REQUEST_GROUP_ID = FRGU.REQUEST_GROUP_ID
ORDER BY 3, 1;
 
Leave a comment

Posted by on January 5, 2013 in Queries

 

Tags: , , , , ,