View: PA_PROJECT_PARTIES_V

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

View Text

SELECT PPP.PROJECT_PARTY_ID
, PPP.OBJECT_ID
, PPP.OBJECT_TYPE
, PPP.PROJECT_ID
, PPP.RESOURCE_ID
, PPP.RESOURCE_TYPE_ID
, PPP.RESOURCE_SOURCE_ID
, PE.FULL_NAME PARTY_NAME
, PPP.PROJECT_ROLE_ID
, PPRT.PROJECT_ROLE_TYPE
, DECODE(PA.ASSIGNMENT_ID
, NULL
, PPRT.MEANING
, PA.ASSIGNMENT_NAME)
, PPRT.ROLE_PARTY_CLASS
, PPP.START_DATE_ACTIVE
, PPP.END_DATE_ACTIVE
, PPP.SCHEDULED_FLAG
, PPP.RECORD_VERSION_NUMBER
, PPA.START_DATE
, PPA.COMPLETION_DATE
, PPP.GRANT_ID
, PA.ASSIGNMENT_ID
, PA.RECORD_VERSION_NUMBER
, DECODE(PA.ASSIGNMENT_ID
, NULL
, 'N'
, PA_ASGMT_WFSTD.IS_APPROVAL_PENDING(PA.ASSIGNMENT_ID))
, PRD.ORGANIZATION_ID
, HAOU.NAME
, HR_GENERAL.GET_WORK_PHONE(PE.PERSON_ID)
, PE.EMAIL_ADDRESS
, PJ.NAME
, 'EMPLOYEE'
, PA_PROJECT_PARTIES_UTILS.ACTIVE_PARTY(PPP.START_DATE_ACTIVE
, PPP.END_DATE_ACTIVE) ACTIVE
, U.USER_NAME
, U.USER_ID
, PE.PARTY_ID
FROM PA_PROJECT_PARTIES PPP
, PA_PROJECTS_ALL PPA
, PA_PROJECT_ROLE_TYPES PPRT
, PER_ALL_PEOPLE_F PE
, PA_PROJECT_ASSIGNMENTS PA
, PER_ALL_ASSIGNMENTS_F PRD
, PER_JOBS PJ
, HR_ALL_ORGANIZATION_UNITS HAOU
, FND_USER U
WHERE PPP.RESOURCE_TYPE_ID = 101
AND PPP.PROJECT_ID = PPA.PROJECT_ID
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND PPP.RESOURCE_SOURCE_ID = PE.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PE.EFFECTIVE_START_DATE)
AND TRUNC(PE.EFFECTIVE_END_DATE)
AND PPP.PROJECT_PARTY_ID = PA.PROJECT_PARTY_ID(+)
AND PPP.RESOURCE_SOURCE_ID = PRD.PERSON_ID
AND PRD.PRIMARY_FLAG = 'Y'
AND PRD.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND TRUNC(SYSDATE) BETWEEN TRUNC(PRD.EFFECTIVE_START_DATE)
AND TRUNC(PRD.EFFECTIVE_END_DATE)
AND PRD.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND NVL(PRD.JOB_ID
, -99) = PJ.JOB_ID(+)
AND U.EMPLOYEE_ID (+)= PPP.RESOURCE_SOURCE_ID
AND PPP.OBJECT_ID = PPA.PROJECT_ID
AND PPP.OBJECT_TYPE = 'PA_PROJECTS' UNION ALL SELECT PPP.PROJECT_PARTY_ID
, PPP.OBJECT_ID
, PPP.OBJECT_TYPE
, PPP.PROJECT_ID
, PPP.RESOURCE_ID
, PPP.RESOURCE_TYPE_ID
, PPP.RESOURCE_SOURCE_ID
, HZP.PARTY_NAME
, PPP.PROJECT_ROLE_ID
, PPRT.PROJECT_ROLE_TYPE
, PPRT.MEANING
, PPRT.ROLE_PARTY_CLASS
, PPP.START_DATE_ACTIVE
, PPP.END_DATE_ACTIVE
, PPP.SCHEDULED_FLAG
, PPP.RECORD_VERSION_NUMBER
, PPA.START_DATE
, PPA.COMPLETION_DATE
, PPP.GRANT_ID
, -999
, -999
, 'N'
, HZO.PARTY_ID
, HZO.PARTY_NAME
, HZP.PRIMARY_PHONE_AREA_CODE || DECODE(HZP.PRIMARY_PHONE_NUMBER
, NULL
, NULL
, DECODE(HZP.PRIMARY_PHONE_AREA_CODE
, NULL
, HZP.PRIMARY_PHONE_NUMBER
, '-' || HZP.PRIMARY_PHONE_NUMBER) ) || DECODE(HZP.PRIMARY_PHONE_EXTENSION
, NULL
, NULL
, '+' || HZP.PRIMARY_PHONE_EXTENSION) PHONE_NUMBER
, HZP.EMAIL_ADDRESS
, NULL
, 'PERSON'
, PA_PROJECT_PARTIES_UTILS.ACTIVE_PARTY(PPP.START_DATE_ACTIVE
, PPP.END_DATE_ACTIVE) ACTIVE
, U.USER_NAME
, U.USER_ID
, PPP.RESOURCE_SOURCE_ID
FROM PA_PROJECT_PARTIES PPP
, PA_PROJECTS_ALL PPA
, PA_PROJECT_ROLE_TYPES PPRT
, HZ_PARTIES HZP
, HZ_PARTIES HZO
, HZ_RELATIONSHIPS HZR
, FND_USER U
WHERE PPP.RESOURCE_TYPE_ID = 112
AND PPP.PROJECT_ID = PPA.PROJECT_ID
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND PPP.RESOURCE_SOURCE_ID = HZP.PARTY_ID
AND HZP.PARTY_TYPE = 'PERSON'
AND HZO.PARTY_TYPE = 'ORGANIZATION'
AND HZR.RELATIONSHIP_CODE IN ('EMPLOYEE_OF'
, 'CONTACT_OF')
AND HZR.STATUS = 'A'
AND HZR.SUBJECT_ID = HZP.PARTY_ID
AND HZR.OBJECT_ID = HZO.PARTY_ID
AND U.PERSON_PARTY_ID (+)= PPP.RESOURCE_SOURCE_ID
AND HZR.SUBJECT_TYPE = 'PERSON'
AND HZR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND HZR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PPP.OBJECT_ID = PPA.PROJECT_ID
AND PPP.OBJECT_TYPE = 'PA_PROJECTS' UNION ALL SELECT PPP.PROJECT_PARTY_ID
, PPP.OBJECT_ID
, PPP.OBJECT_TYPE
, PPP.PROJECT_ID
, PPP.RESOURCE_ID
, PPP.RESOURCE_TYPE_ID
, PPP.RESOURCE_SOURCE_ID
, HZO.PARTY_NAME
, PPP.PROJECT_ROLE_ID
, PPRT.PROJECT_ROLE_TYPE
, PPRT.MEANING
, PPRT.ROLE_PARTY_CLASS
, PPP.START_DATE_ACTIVE
, PPP.END_DATE_ACTIVE
, PPP.SCHEDULED_FLAG
, PPP.RECORD_VERSION_NUMBER
, PPA.START_DATE
, PPA.COMPLETION_DATE
, PPP.GRANT_ID
, -999
, -999
, 'N'
, -999
, NULL
, HZO.PRIMARY_PHONE_AREA_CODE || DECODE(HZO.PRIMARY_PHONE_NUMBER
, NULL
, NULL
, DECODE(HZO.PRIMARY_PHONE_AREA_CODE
, NULL
, HZO.PRIMARY_PHONE_NUMBER
, '-' || HZO.PRIMARY_PHONE_NUMBER) ) || DECODE(HZO.PRIMARY_PHONE_EXTENSION
, NULL
, NULL
, '+' || HZO.PRIMARY_PHONE_EXTENSION) PHONE_NUMBER
, HZO.EMAIL_ADDRESS
, NULL
, 'ORGANIZATION'
, PA_PROJECT_PARTIES_UTILS.ACTIVE_PARTY(PPP.START_DATE_ACTIVE
, PPP.END_DATE_ACTIVE) ACTIVE
, NULL
, -999
, PPP.RESOURCE_SOURCE_ID
FROM PA_PROJECT_PARTIES PPP
, PA_PROJECTS_ALL PPA
, PA_PROJECT_ROLE_TYPES_VL PPRT
, HZ_PARTIES HZO
WHERE PPP.RESOURCE_TYPE_ID = 112
AND PPP.PROJECT_ID = PPA.PROJECT_ID
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND PPP.RESOURCE_SOURCE_ID = HZO.PARTY_ID
AND HZO.PARTY_TYPE = 'ORGANIZATION'
AND PPP.OBJECT_ID = PPA.PROJECT_ID
AND PPP.OBJECT_TYPE = 'PA_PROJECTS'

Columns