VIEW: APPS.PA_PEOPLE_LOV_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'