SELECT DISTINCT PPA.project_status_code
,HOU.name organization_name
,PPA.description
,OCT.name contract_type
,PPT.project_type
,PPA.segment1 project_number
,PPA.name project_name
,TO_CHAR(PPA.start_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) start_date
,TO_CHAR(PPA.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) completion_date
,TO_CHAR(PPA.scheduled_finish_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) closed_date
,OCH.contract_number
,OCH.estimated_amount contract_amount
,(SELECT SUM(PRD.cont_curr_revenue_amt) cont_curr_revenue_amt
FROM pjb_rev_distributions PRD
WHERE PRD.contract_id = OCH.id
AND PRD.major_version = OCH.major_version) revenue_amount
,(SELECT DISTINCT ppn.full_name
FROM pjf_project_parties proj_dir
,pjf_proj_role_types_tl prt
,per_all_assignments_f paf
,per_person_names_f ppn
WHERE proj_dir.project_id = PPA.project_id
AND proj_dir.project_role_id = prt.project_role_id
AND prt.PROJECT_ROLE_NAME =’Project Director’
AND proj_dir.resource_source_id = paf.person_id
AND paf.ASSIGNMENT_STATUS_TYPE=’ACTIVE’
AND paf.person_id = ppn.person_id
AND ppn.name_type = ‘GLOBAL’
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND NVL (paf.effective_end_date, SYSDATE+1)
AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active AND NVL(proj_dir.end_date_active, SYSDATE+1)
AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND NVL (ppn.effective_end_date, SYSDATE+1)
AND paf.primary_flag = ‘Y’
AND ROWNUM < 2) Director
,(SELECT DISTINCT ppn.full_name
FROM pjf_project_parties proj_dir
,pjf_proj_role_types_tl prt
,per_all_assignments_f paf
,per_person_names_f ppn
WHERE proj_dir.project_id = PPA.project_id
AND proj_dir.project_role_id = prt.project_role_id
AND prt.PROJECT_ROLE_NAME =’Project Manager’
AND proj_dir.resource_source_id = paf.person_id
AND paf.ASSIGNMENT_STATUS_TYPE=’ACTIVE’
AND paf.person_id = ppn.person_id
AND ppn.name_type = ‘GLOBAL’
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND NVL (paf.effective_end_date, SYSDATE+1)
AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active AND NVL(proj_dir.end_date_active, SYSDATE+1)
AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND NVL (ppn.effective_end_date, SYSDATE+1)
AND paf.primary_flag = ‘Y’
AND ROWNUM < 2
) Manager
FROM pjf_projects_all_vl PPA
,pjb_cntrct_proj_links PCPL
,okc_k_headers_all_b OCH
,hr_all_organization_units HOU
,okc_contract_types_vl OCT
,pjf_project_types_vl PPT
WHERE PPA.project_id = PCPL.project_id (+)
AND OCH.id = PCPL.contract_id
AND PPA.carrying_out_organization_id = HOU.organization_id
AND OCH.contract_type_id = OCT.contract_type_id
AND PPA.project_type_id = PPT.project_type_id
AND pcpl.major_version=OCH.major_version
,HOU.name organization_name
,PPA.description
,OCT.name contract_type
,PPT.project_type
,PPA.segment1 project_number
,PPA.name project_name
,TO_CHAR(PPA.start_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) start_date
,TO_CHAR(PPA.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) completion_date
,TO_CHAR(PPA.scheduled_finish_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) closed_date
,OCH.contract_number
,OCH.estimated_amount contract_amount
,(SELECT SUM(PRD.cont_curr_revenue_amt) cont_curr_revenue_amt
FROM pjb_rev_distributions PRD
WHERE PRD.contract_id = OCH.id
AND PRD.major_version = OCH.major_version) revenue_amount
,(SELECT DISTINCT ppn.full_name
FROM pjf_project_parties proj_dir
,pjf_proj_role_types_tl prt
,per_all_assignments_f paf
,per_person_names_f ppn
WHERE proj_dir.project_id = PPA.project_id
AND proj_dir.project_role_id = prt.project_role_id
AND prt.PROJECT_ROLE_NAME =’Project Director’
AND proj_dir.resource_source_id = paf.person_id
AND paf.ASSIGNMENT_STATUS_TYPE=’ACTIVE’
AND paf.person_id = ppn.person_id
AND ppn.name_type = ‘GLOBAL’
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND NVL (paf.effective_end_date, SYSDATE+1)
AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active AND NVL(proj_dir.end_date_active, SYSDATE+1)
AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND NVL (ppn.effective_end_date, SYSDATE+1)
AND paf.primary_flag = ‘Y’
AND ROWNUM < 2) Director
,(SELECT DISTINCT ppn.full_name
FROM pjf_project_parties proj_dir
,pjf_proj_role_types_tl prt
,per_all_assignments_f paf
,per_person_names_f ppn
WHERE proj_dir.project_id = PPA.project_id
AND proj_dir.project_role_id = prt.project_role_id
AND prt.PROJECT_ROLE_NAME =’Project Manager’
AND proj_dir.resource_source_id = paf.person_id
AND paf.ASSIGNMENT_STATUS_TYPE=’ACTIVE’
AND paf.person_id = ppn.person_id
AND ppn.name_type = ‘GLOBAL’
AND TRUNC (SYSDATE) BETWEEN paf.effective_start_date AND NVL (paf.effective_end_date, SYSDATE+1)
AND TRUNC (SYSDATE) BETWEEN proj_dir.start_date_active AND NVL(proj_dir.end_date_active, SYSDATE+1)
AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date AND NVL (ppn.effective_end_date, SYSDATE+1)
AND paf.primary_flag = ‘Y’
AND ROWNUM < 2
) Manager
FROM pjf_projects_all_vl PPA
,pjb_cntrct_proj_links PCPL
,okc_k_headers_all_b OCH
,hr_all_organization_units HOU
,okc_contract_types_vl OCT
,pjf_project_types_vl PPT
WHERE PPA.project_id = PCPL.project_id (+)
AND OCH.id = PCPL.contract_id
AND PPA.carrying_out_organization_id = HOU.organization_id
AND OCH.contract_type_id = OCT.contract_type_id
AND PPA.project_type_id = PPT.project_type_id
AND pcpl.major_version=OCH.major_version
Recent Posts
