View: PA_PEOPLE_LOV_V

Product:	PA - Projects
Description:
Implementation/DBA Data:	APPS.PA_PEOPLE_LOV_V

View Text

SELECT 101
, P.PERSON_ID
, P.FULL_NAME
, PAF.ORGANIZATION_ID
, OU.NAME
, TO_NUMBER(NULL)
, NULL
, DECODE(NVL(P.CURRENT_NPW_FLAG
, 'N')
, 'Y'
, 'CWK'
, 'EMP')
, DECODE(NVL(P.CURRENT_NPW_FLAG
, 'N')
, 'Y'
, P.NPW_NUMBER
, P.EMPLOYEE_NUMBER)
, P.PARTY_ID
FROM HR_ALL_ORGANIZATION_UNITS OU
, PER_ALL_ASSIGNMENTS_F PAF
, PER_PEOPLE_F P
WHERE ( P.CURRENT_EMPLOYEE_FLAG = 'Y' OR P.CURRENT_NPW_FLAG = 'Y')
AND (P.EMPLOYEE_NUMBER IS NOT NULL OR P.NPW_NUMBER IS NOT NULL)
AND PAF.PERSON_ID = P.PERSON_ID
AND PAF.PRIMARY_FLAG = 'Y'
AND PAF.ASSIGNMENT_TYPE IN('E'
, 'C')
AND PAF.JOB_ID IS NOT NULL
AND ( ( TRUNC(SYSDATE) BETWEEN TRUNC(P.EFFECTIVE_START_DATE)
AND TRUNC(P.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAF.EFFECTIVE_START_DATE)
AND TRUNC(PAF.EFFECTIVE_END_DATE)) OR ( TRUNC(SYSDATE) < TRUNC(P.EFFECTIVE_START_DATE)
AND TRUNC(P.EFFECTIVE_START_DATE) BETWEEN TRUNC(PAF.EFFECTIVE_START_DATE)
AND TRUNC(PAF.EFFECTIVE_END_DATE)))
AND OU.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND TRUNC(SYSDATE) >= DECODE(NVL(FND_PROFILE.VALUE('PA_ALLOW_FDE_AS_PM')
, 'Y')
, 'Y'
, (TRUNC(SYSDATE)-1)
, TRUNC(P.EFFECTIVE_START_DATE)) UNION ALL SELECT DISTINCT 112
, HZP.PARTY_ID
, HZP.PARTY_NAME
, HZO.PARTY_ID
, HZO.PARTY_NAME
, PPP.OBJECT_ID
, PPP.OBJECT_TYPE
, 'PARTY'
, NULL
, HZP.PARTY_ID
FROM PA_PROJECT_PARTIES PPP
, HZ_PARTIES HZO
, HZ_RELATIONSHIPS HZR
, HZ_PARTIES HZP
WHERE HZP.PARTY_TYPE='PERSON'
AND HZR.SUBJECT_ID=HZP.PARTY_ID
AND HZR.OBJECT_ID=HZO.PARTY_ID
AND HZR.RELATIONSHIP_CODE IN ('EMPLOYEE_OF'
, 'CONTACT_OF')
AND HZR.STATUS = 'A'
AND PPP.RESOURCE_SOURCE_ID=HZO.PARTY_ID
AND PPP.RESOURCE_TYPE_ID=112
AND HZR.SUBJECT_TYPE = 'PERSON'
AND HZR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'

Columns