View: PA_PROJ_INFO_VIEW

Product:	PA - Projects
Description:	Basic project information for projects
Implementation/DBA Data:	APPS.PA_PROJ_INFO_VIEW

View Text

SELECT PROJ.ORG_ID
, PROJ.PROJECT_ID
, PROJ.NAME
, PROJ.SEGMENT1
, PROJ.START_DATE
, PROJ.COMPLETION_DATE
, PS.PROJECT_STATUS_NAME
, PROJ.PROJECT_STATUS_CODE
, FND.MEANING
, PROJ.PROJECT_TYPE
, PROJ.DESCRIPTION
, ORG.ORGANIZATION_ID
, ORG.NAME
, PLAYER.PERSON_ID
, PEOPLE.FULL_NAME
, PROJ.DISTRIBUTION_RULE
, ROLES.PROJECT_ROLE_TYPE
, ROLES.MEANING
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJECT_RATE_DATE
, PROJ.PROJECT_RATE_TYPE
, PROJ.ALLOW_CROSS_CHARGE_FLAG
FROM PA_PROJECTS PROJ
, HR_ORGANIZATION_UNITS ORG
, PER_PEOPLE_F PEOPLE
, PA_PROJECT_PLAYERS PLAYER
, PA_PROJECT_ROLE_TYPES ROLES
, PA_PROJECT_STATUSES PS
, FND_LOOKUPS FND
WHERE PROJ.CARRYING_OUT_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND PROJ.PROJECT_ID = PLAYER.PROJECT_ID
AND PLAYER.PERSON_ID = PEOPLE.PERSON_ID
AND ( PEOPLE.CURRENT_EMPLOYEE_FLAG='Y' OR PEOPLE.CURRENT_NPW_FLAG='Y')
AND TRUNC(SYSDATE) BETWEEN PEOPLE.EFFECTIVE_START_DATE
AND PEOPLE.EFFECTIVE_END_DATE
AND PLAYER.PROJECT_ROLE_TYPE = ROLES.PROJECT_ROLE_TYPE
AND ROLES.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
AND TRUNC(SYSDATE) BETWEEN PLAYER.START_DATE_ACTIVE
AND NVL(PLAYER.END_DATE_ACTIVE (+)
, SYSDATE+1)
AND PS.PROJECT_STATUS_CODE = PROJ.PROJECT_STATUS_CODE
AND FND.LOOKUP_TYPE = 'YES_NO'
AND FND.LOOKUP_CODE = PROJ.PUBLIC_SECTOR_FLAG
AND PS.STATUS_TYPE = 'PROJECT' UNION SELECT PROJ.ORG_ID
, PROJ.PROJECT_ID
, PROJ.NAME
, PROJ.SEGMENT1
, PROJ.START_DATE
, PROJ.COMPLETION_DATE
, PS.PROJECT_STATUS_NAME
, PROJ.PROJECT_STATUS_CODE
, FND.MEANING
, PROJ.PROJECT_TYPE
, PROJ.DESCRIPTION
, ORG.ORGANIZATION_ID
, ORG.NAME
, TO_NUMBER(NULL)
, NULL
, PROJ.DISTRIBUTION_RULE
, NULL
, NULL
, PROJ.PROJECT_CURRENCY_CODE
, PROJ.PROJECT_RATE_DATE
, PROJ.PROJECT_RATE_TYPE
, PROJ.ALLOW_CROSS_CHARGE_FLAG
FROM PA_PROJECTS PROJ
, HR_ORGANIZATION_UNITS ORG
, PA_PROJECT_STATUSES PS
, FND_LOOKUPS FND
WHERE PROJ.CARRYING_OUT_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND NOT EXISTS ( SELECT NULL
FROM PA_PROJECT_PLAYERS PP
, PER_PEOPLE_F PEOPLE
WHERE PP.PROJECT_ID = PROJ.PROJECT_ID
AND PP.PERSON_ID = PEOPLE.PERSON_ID
AND (PEOPLE.CURRENT_EMPLOYEE_FLAG = 'Y' OR PEOPLE.CURRENT_NPW_FLAG = 'Y')
AND TRUNC(SYSDATE) BETWEEN PEOPLE.EFFECTIVE_START_DATE
AND PEOPLE.EFFECTIVE_END_DATE
AND PP.PROJECT_ROLE_TYPE = 'PROJECT MANAGER'
AND TRUNC(SYSDATE) BETWEEN PP.START_DATE_ACTIVE
AND NVL(PP.END_DATE_ACTIVE
, SYSDATE+1))
AND PS.PROJECT_STATUS_CODE = PROJ.PROJECT_STATUS_CODE
AND FND.LOOKUP_TYPE = 'YES_NO'
AND FND.LOOKUP_CODE = PROJ.PUBLIC_SECTOR_FLAG
AND PS.STATUS_TYPE = 'PROJECT'

Columns