View: PA_XC_KEY_MEMBERS_V

View Text

SELECT USR.USER_ID
, USR.USER_NAME
, PTY1.PARTY_ID
, PTY1.PARTY_NAME
, PTY2.PARTY_ID
, PTY2.PARTY_NAME
, RESP.RESPONSIBILITY_ID
, RESP.RESPONSIBILITY_KEY
FROM HZ_PARTIES PTY1
, HZ_PARTIES PTY2
, HZ_PARTY_RELATIONSHIPS REL
, FND_USER USR
, FND_USER_RESP_GROUPS URES
, FND_RESPONSIBILITY RESP
WHERE USR.CUSTOMER_ID = PTY1.PARTY_ID
AND REL.OBJECT_ID = PTY2.PARTY_ID
AND PTY1.PARTY_ID = REL.SUBJECT_ID
AND REL.PARTY_RELATIONSHIP_TYPE = 'EMPLOYEE_OF'
AND USR.USER_ID = URES.USER_ID
AND URES.RESPONSIBILITY_ID = RESP.RESPONSIBILITY_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(URES.START_DATE)
AND TRUNC(NVL(URES.END_DATE
, SYSDATE))
AND PTY1.STATUS = 'A'

Columns