Project Unbilled Balances Query - FUSION

WITH
FUNCTION get_rev_todate_amt (
      p_contract_id      IN NUMBER,     
      p_contract_line_id IN NUMBER,
      P_AS_OF_DATE       IN DATE
   )
RETURN NUMBER
IS
  ln_amount NUMBER;
BEGIN  

   SELECT SUM(PRD.cont_curr_revenue_amt) cont_curr_revenue_amt
     INTO ln_amount
     FROM pjb_rev_distributions PRD
    WHERE PRD.gl_date <= P_AS_OF_DATE
      AND PRD.contract_id = p_contract_id
      AND PRD.contract_line_id = p_contract_line_id;  

    RETURN ln_amount;
EXCEPTION WHEN OTHERS THEN
      RETURN 0;
END;

SELECT PPA.segment1 project_number
    ,OCTV.name contracty_type
    ,PPA.name project_name
    ,PPA.description
    ,HOU.name organization_name
    ,PPA.project_currency_code
    ,OKH.contract_number
    ,OKL.line_number
    ,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(get_rev_todate_amt(OKH.id, OKL.id, :P_AS_OF_DATE), 0), 2) project_to_date_revenue_sum
    ,ROUND(NVL(get_rev_todate_amt(OKH.id, OKL.id, :P_AS_OF_DATE), 0), 2) - ROUND(NVL((INV.cont_curr_billed_amt), 0), 2) unbilled_balance
    ,PBC.billing_cycle_name
     ,TO_CHAR (INV.last_date_invoiced, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = american') last_date_invoiced
     ,OKH.id contract_id
     ,OKL.id contract_line_id
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
,pjb_bill_plans_vl PBP
,pjf_billing_cycles_vl PBC
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 OKL.bill_plan_id    = PBP.bill_plan_id (+)
AND OKL.major_version   = PBP.major_version (+)
AND PBP.billing_cycle_id = PBC.billing_cycle_id (+)