Query To Find Contracts With Revenue on Hold in Oracle Fusion

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')