eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PA_PROJECT_PARTIES_V

Source

View Text - Preformatted


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'

View Text - HTML Formatted

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'