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
Good Blog,thanks for shariong this informative article.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
ReplyDeleteOracle Fusion HCM Online Training
Oracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
Big Data and Hadoop Training In Hyderabad
Oracle Fusion HCM Training In Hyderabad