[Home] [Help]
| Product: | PA - Projects |
|---|---|
| Description: | |
| Implementation/DBA Data: | Not implemented in this database |
SELECT PRJ.PROJECT_ID
, PRJ.NAME
, PRJ.SEGMENT1
, PPR.PROGRESS_STATUS_CODE
, PAL.MEANING
, PAL.PREDEFINED_FLAG
, PTY1.PARTY_ID
, PTY1.PARTY_NAME
, PTY2.PARTY_ID
, PTY2.PARTY_NAME
, USR.USER_ID
, USR.USER_NAME
, PRJ.START_DATE
, PRJ.COMPLETION_DATE
, PRJ.DESCRIPTION
FROM PA_PROJECTS_ALL PRJ
, PA_PROJ_PROGRESS_REPORTS PPR
, PA_LOOKUPS PAL
, HZ_PARTIES PTY1
, HZ_PARTIES PTY2
, PA_PROJECT_PLAYERS PLAY
, FND_USER USR
WHERE PA_XC_VIEW_UTILS.ALLOW_QUERY(PRJ.PROJECT_ID) = 'Y'
AND PRJ.PROJECT_ID = PPR.PROJECT_ID
AND PPR.TASK_ID = 0
AND PPR.PROGRESS_STATUS_CODE = PAL.LOOKUP_CODE
AND PAL.LOOKUP_TYPE = 'PA_XC_PROGRESS_STATUS'
AND PRJ.CARRYING_OUT_ORGANIZATION_ID = PTY1.PARTY_ID
AND PRJ.PROJECT_ID = PLAY.PROJECT_ID
AND PLAY.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
AND PLAY.PERSON_ID = PTY2.PARTY_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PLAY.START_DATE_ACTIVE)
AND TRUNC(NVL(PLAY.END_DATE_ACTIVE
, SYSDATE))
AND PTY2.PARTY_ID = USR.CUSTOMER_ID UNION ALL SELECT PRJ.PROJECT_ID
, PRJ.NAME
, PRJ.SEGMENT1
, PPR.PROGRESS_STATUS_CODE
, PAL.MEANING
, PAL.PREDEFINED_FLAG
, PTY1.PARTY_ID
, PTY1.PARTY_NAME
, -999
, NULL
, -999
, NULL
, PRJ.START_DATE
, PRJ.COMPLETION_DATE
, PRJ.DESCRIPTION
FROM PA_PROJECTS_ALL PRJ
, PA_PROJ_PROGRESS_REPORTS PPR
, PA_LOOKUPS PAL
, HZ_PARTIES PTY1
WHERE PA_XC_VIEW_UTILS.ALLOW_QUERY(PRJ.PROJECT_ID) = 'Y'
AND PRJ.PROJECT_ID = PPR.PROJECT_ID
AND PPR.TASK_ID = 0
AND PPR.PROGRESS_STATUS_CODE = PAL.LOOKUP_CODE
AND PAL.LOOKUP_TYPE = 'PA_XC_PROGRESS_STATUS'
AND PRJ.CARRYING_OUT_ORGANIZATION_ID = PTY1.PARTY_ID
AND NOT EXISTS (SELECT 'Y'
FROM PA_PROJECT_PLAYERS PLAY
WHERE PRJ.PROJECT_ID = PLAY.PROJECT_ID
AND PLAY.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
AND TRUNC(SYSDATE) BETWEEN TRUNC(PLAY.START_DATE_ACTIVE)
AND TRUNC(NVL(PLAY.END_DATE_ACTIVE
, SYSDATE)))