SELECT pou.NAME carrying_out_org_name
,ppa.NAME project_name
,ppa.segment1 project_num
,ppa.segment1 project_number
--,TO_CHAR(ppa.completion_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') completion_date
--,ppa.description
--,TO_CHAR(ppp.end_date_active, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') end_date_active
,TO_CHAR((SELECT MAX (a.effective_start_date)
FROM per_all_people_f a, per_person_types_tl b, per_assignments_f c
WHERE 1=1
AND a.person_id=c.person_id(+)
AND b.person_type_id=c.person_type_id(+)
AND b.user_person_type='Employee'
AND a.person_id = pap.person_id), 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') effective_start_date
,TO_CHAR((pap.effective_start_date-1), 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') effective_end_date
,pap.person_number employee_number
,ppn.full_name
,prt.project_role_name project_role
,pps.project_status_name
--,ppt.project_type
--,TO_CHAR(ppp.start_date_active, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') start_date_active
--,ppa.attribute1 OMS
-- ,NULL auto_billing_frequency
FROM pjf_projects_all_vl ppa,
pjf_project_types_vl ppt,
hr_all_organization_units pou,
pjf_project_statuses_vl pps,
pjf_project_parties ppp,
--pa_role_controls prc,
pjf_proj_role_types_v prt,
per_all_people_f pap,
per_person_names_f ppn,
per_person_types_tl pptl,
per_assignments_f paf
WHERE ppa.project_type_id = ppt.project_type_id
AND ppa.project_status_code = pps.project_status_code
AND ppa.carrying_out_organization_id = pou.organization_id
AND ppp.project_id = ppa.project_id
AND ppp.project_role_id = prt.project_role_id
AND ppp.resource_source_id = pap.person_id
AND ppp.resource_source_id = ppn.person_id
AND ppn.name_type = 'GLOBAL'
AND ppp.object_type = 'PA_PROJECTS'
--AND ppp.resource_type_id = 101
--AND ppp.project_role_id = prc.project_role_id
--AND prc.role_control_code = 'ALLOW_AS_PROJ_MEMBER'
AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND UPPER(pps.project_status_name) IN ('SUBMITTED', 'ACTIVE')
AND pap.person_id=paf.person_id(+)
AND pptl.person_type_id=paf.person_type_id(+)
AND pptl.user_person_type='Ex-employee'
Order By pap.effective_end_date,ppn.full_name
,ppa.NAME project_name
,ppa.segment1 project_num
,ppa.segment1 project_number
--,TO_CHAR(ppa.completion_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') completion_date
--,ppa.description
--,TO_CHAR(ppp.end_date_active, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') end_date_active
,TO_CHAR((SELECT MAX (a.effective_start_date)
FROM per_all_people_f a, per_person_types_tl b, per_assignments_f c
WHERE 1=1
AND a.person_id=c.person_id(+)
AND b.person_type_id=c.person_type_id(+)
AND b.user_person_type='Employee'
AND a.person_id = pap.person_id), 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') effective_start_date
,TO_CHAR((pap.effective_start_date-1), 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') effective_end_date
,pap.person_number employee_number
,ppn.full_name
,prt.project_role_name project_role
,pps.project_status_name
--,ppt.project_type
--,TO_CHAR(ppp.start_date_active, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') start_date_active
--,ppa.attribute1 OMS
-- ,NULL auto_billing_frequency
FROM pjf_projects_all_vl ppa,
pjf_project_types_vl ppt,
hr_all_organization_units pou,
pjf_project_statuses_vl pps,
pjf_project_parties ppp,
--pa_role_controls prc,
pjf_proj_role_types_v prt,
per_all_people_f pap,
per_person_names_f ppn,
per_person_types_tl pptl,
per_assignments_f paf
WHERE ppa.project_type_id = ppt.project_type_id
AND ppa.project_status_code = pps.project_status_code
AND ppa.carrying_out_organization_id = pou.organization_id
AND ppp.project_id = ppa.project_id
AND ppp.project_role_id = prt.project_role_id
AND ppp.resource_source_id = pap.person_id
AND ppp.resource_source_id = ppn.person_id
AND ppn.name_type = 'GLOBAL'
AND ppp.object_type = 'PA_PROJECTS'
--AND ppp.resource_type_id = 101
--AND ppp.project_role_id = prc.project_role_id
--AND prc.role_control_code = 'ALLOW_AS_PROJ_MEMBER'
AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND UPPER(pps.project_status_name) IN ('SUBMITTED', 'ACTIVE')
AND pap.person_id=paf.person_id(+)
AND pptl.person_type_id=paf.person_type_id(+)
AND pptl.user_person_type='Ex-employee'
Order By pap.effective_end_date,ppn.full_name
No comments:
Post a Comment