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'