SELECT PPA.segment1 project_number
,OCTV.name contracty_type
,PPA.name project_name
,HOU.name organization_name
,PPA.project_currency_code
,TO_CHAR (PPA.completion_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') completion_date
,ROUND((NVL((INV.cont_curr_billed_amt), 0)), 2) project_to_date_Invoiced_sum
,ROUND((NVL((INV_UNRECOG.cont_curr_billed_amt), 0)), 2) Draft_invoice_amount
,ROUND(NVL((SELECT SUM(PNAR_AMOUNT)
FROM (
SELECT ((PEI.DENOM_RAW_COST/100) * (100 - NVL(PEI.REVENUE_RECOG_PERCENTAGE, 0))) PNAR_AMOUNT
FROM PJC_EXP_ITEMS_ALL PEI
WHERE PEI.project_id = PPA.project_id
AND PEI.REVENUE_RECOGNIZED_FLAG <> 'F'
UNION ALL
SELECT ((PBE.BILL_TRNS_AMOUNT/100) * (100 - NVL(PBE.REVENUE_RECOGNZD_PERCENTAGE, 0))) PNAR_AMOUNT
FROM PJB_BILLING_EVENTS PBE
WHERE PBE.project_id = PPA.project_id
AND PBE.REVENUE_RECOGNZD_FLAG <> 'F'
)
), 0), 2) PNAR_AMOUNT
FROM pjf_projects_all_vl PPA
,pjb_cntrct_proj_links PCPL
,okc_k_lines_b OKL
,okc_k_headers_all_b OKH
,okc_contract_types_vl OCTV
,pjf_project_types_vl PPT
,hr_all_organization_units HOU
,(SELECT SUM(PILD.cont_curr_billed_amt) cont_curr_billed_amt, MAX(PIH.invoice_date) last_date_invoiced
,PILD.contract_id, PILD.contract_line_id
FROM pjb_inv_line_dists PILD
,pjb_invoice_headers PIH
WHERE 1=1
AND PILD.invoice_id = PIH.invoice_id
AND PIH.transfer_status_code = 'A'
AND PIH.gl_date <= :P_AS_OF_DATE
GROUP BY PILD.contract_id, PILD.contract_line_id
) INV
,(SELECT SUM(PILD.cont_curr_billed_amt) cont_curr_billed_amt, MAX(PIH.invoice_date) last_date_invoiced
,PILD.contract_id, PILD.contract_line_id
FROM pjb_inv_line_dists PILD
,pjb_invoice_headers PIH
WHERE 1=1
AND PILD.invoice_id = PIH.invoice_id
AND PIH.transfer_status_code <> 'A'
AND PIH.gl_date <= :P_AS_OF_DATE
GROUP BY PILD.contract_id, PILD.contract_line_id
) INV_UNRECOG
WHERE 1=1
and PPA.project_id = PCPL.project_id
AND PCPL.version_type = 'C' -- Current
AND PCPL.contract_line_id = OKL.id
AND PCPL.major_version = OKL.major_version
AND OKL.chr_id = OKH.id
AND OKL.major_version = OKH.major_version
AND OKL.version_type = 'C' -- Current
AND OKH.version_type = 'C' -- Current
AND OKH.contract_type_id = OCTV.contract_type_id
AND PPA.project_type_id = PPT.project_type_id
AND HOU.organization_id = PPA.carrying_out_organization_id
AND OKH.sts_code <> 'DRAFT'
AND PPT.project_type NOT IN ('INTERCOMPANY', 'Intercompany')
AND INV.contract_id (+) = OKH.id
AND INV.contract_line_id (+) = OKL.id
AND INV_UNRECOG.contract_id (+) = OKH.id
AND INV_UNRECOG.contract_line_id (+) = OKL.id
AND EXISTS (SELECT 1 FROM pjb_bill_plans_vl PBP
,pjb_billing_methods_b PBM
WHERE PBP.contract_id = OKH.id
AND PBP.bill_method_id = PBM.bill_method_id
AND PBM.bill_method_flag = 'R'
AND PBP.version_type = 'C'
AND PBP.on_hold_Flag = 'Y')
,OCTV.name contracty_type
,PPA.name project_name
,HOU.name organization_name
,PPA.project_currency_code
,TO_CHAR (PPA.completion_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') completion_date
,ROUND((NVL((INV.cont_curr_billed_amt), 0)), 2) project_to_date_Invoiced_sum
,ROUND((NVL((INV_UNRECOG.cont_curr_billed_amt), 0)), 2) Draft_invoice_amount
,ROUND(NVL((SELECT SUM(PNAR_AMOUNT)
FROM (
SELECT ((PEI.DENOM_RAW_COST/100) * (100 - NVL(PEI.REVENUE_RECOG_PERCENTAGE, 0))) PNAR_AMOUNT
FROM PJC_EXP_ITEMS_ALL PEI
WHERE PEI.project_id = PPA.project_id
AND PEI.REVENUE_RECOGNIZED_FLAG <> 'F'
UNION ALL
SELECT ((PBE.BILL_TRNS_AMOUNT/100) * (100 - NVL(PBE.REVENUE_RECOGNZD_PERCENTAGE, 0))) PNAR_AMOUNT
FROM PJB_BILLING_EVENTS PBE
WHERE PBE.project_id = PPA.project_id
AND PBE.REVENUE_RECOGNZD_FLAG <> 'F'
)
), 0), 2) PNAR_AMOUNT
FROM pjf_projects_all_vl PPA
,pjb_cntrct_proj_links PCPL
,okc_k_lines_b OKL
,okc_k_headers_all_b OKH
,okc_contract_types_vl OCTV
,pjf_project_types_vl PPT
,hr_all_organization_units HOU
,(SELECT SUM(PILD.cont_curr_billed_amt) cont_curr_billed_amt, MAX(PIH.invoice_date) last_date_invoiced
,PILD.contract_id, PILD.contract_line_id
FROM pjb_inv_line_dists PILD
,pjb_invoice_headers PIH
WHERE 1=1
AND PILD.invoice_id = PIH.invoice_id
AND PIH.transfer_status_code = 'A'
AND PIH.gl_date <= :P_AS_OF_DATE
GROUP BY PILD.contract_id, PILD.contract_line_id
) INV
,(SELECT SUM(PILD.cont_curr_billed_amt) cont_curr_billed_amt, MAX(PIH.invoice_date) last_date_invoiced
,PILD.contract_id, PILD.contract_line_id
FROM pjb_inv_line_dists PILD
,pjb_invoice_headers PIH
WHERE 1=1
AND PILD.invoice_id = PIH.invoice_id
AND PIH.transfer_status_code <> 'A'
AND PIH.gl_date <= :P_AS_OF_DATE
GROUP BY PILD.contract_id, PILD.contract_line_id
) INV_UNRECOG
WHERE 1=1
and PPA.project_id = PCPL.project_id
AND PCPL.version_type = 'C' -- Current
AND PCPL.contract_line_id = OKL.id
AND PCPL.major_version = OKL.major_version
AND OKL.chr_id = OKH.id
AND OKL.major_version = OKH.major_version
AND OKL.version_type = 'C' -- Current
AND OKH.version_type = 'C' -- Current
AND OKH.contract_type_id = OCTV.contract_type_id
AND PPA.project_type_id = PPT.project_type_id
AND HOU.organization_id = PPA.carrying_out_organization_id
AND OKH.sts_code <> 'DRAFT'
AND PPT.project_type NOT IN ('INTERCOMPANY', 'Intercompany')
AND INV.contract_id (+) = OKH.id
AND INV.contract_line_id (+) = OKL.id
AND INV_UNRECOG.contract_id (+) = OKH.id
AND INV_UNRECOG.contract_line_id (+) = OKL.id
AND EXISTS (SELECT 1 FROM pjb_bill_plans_vl PBP
,pjb_billing_methods_b PBM
WHERE PBP.contract_id = OKH.id
AND PBP.bill_method_id = PBM.bill_method_id
AND PBM.bill_method_flag = 'R'
AND PBP.version_type = 'C'
AND PBP.on_hold_Flag = 'Y')
No comments:
Post a Comment