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 (+)
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 (+)
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
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.
ReplyDeleteOracle Fusion SCM Online Training
Oracle Integration Cloud Service Online Training