Monday, 24 September 2018

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 (+)

2 comments:

  1. 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.
    Oracle 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

    ReplyDelete
  2. I would like to thank you for the efforts you have made in writing this article. I am hoping the same best work from you in the future as well.

    Oracle Fusion SCM Online Training
    Oracle Integration Cloud Service Online Training

    ReplyDelete