Query to get Payables and Receivables Project Details (Project Cash Flow Report)

with gl_period_dates as ( 

       select period_name, 

              year_start_date, 

              add_months(year_start_date,12)-1 year_end_date, 

              quarter_start_date, 

              add_months(quarter_start_date,3)-1 quarter_end_date,

              start_date period_start_date, 

              end_date period_end_date

       from   gl_periods 

       where  period_set_name='Global Calendar'

       and    period_year=:p_year 

       and   (quarter_num IN (:p_quarter) OR coalesce(:p_quarter, NULL) IS NULL)

       and   (period_name IN (:p_month) OR coalesce(:p_month, NULL) IS NULL)),

receivables as (

select fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              pcpl.contract_id,

              fnbuv.bu_name,

              xlp.name le_name,

              ppavl.name, 

              ppavl.segment1,

OCH.contract_number,

              SUM(NVL(aps.amount_applied,0)) cash_in,

-- SUM(NVL(xal.ACCOUNTED_DR, 0)-NVL(xal.ACCOUNTED_CR, 0)) billing_amt

(SELECT sum(NVL(PROJECT_CURR_BILLED_AMT,0)) 

          FROM

          pjb_inv_line_dists idl

          

          WHERE 1=1

          and idl.CONTRACT_ID = och.id

          and  invoice_id in ( select invoice_id from pjb_invoice_headers 

  where

          contract_id = idl.contract_id  

  and invoice_status_code = 'ACCEPTED')

          ) billing_amt

FROM   ra_customer_trx_all rct,

okc_k_headers_all_b och,

xla_transaction_entities xlate,

xla_events xet,

xla_ae_headers xah,

xla_ae_lines xal,

ar_payment_schedules_all aps,

fun_names_business_units_v fnbuv,

xle_entity_profiles xlp,

gl_period_dates gpd,

              pjf_projects_all_vl ppavl,

              (select distinct project_id, 

                      contract_id, 

                      major_version

               from   pjb_cntrct_proj_links ) pcpl

WHERE to_char(rct.interface_header_attribute1) = to_char(och.contract_number)

AND   to_char(rct.interface_header_attribute2) = to_char(och.id)

AND   rct.set_of_books_id = xah.ledger_id

       and   pcpl.contract_id=och.id

       and   pcpl.project_id=ppavl.project_id

       --and   och.version_type='C'

AND   och.major_version = (

         SELECT max(och1.major_version)

         FROM okc_k_headers_all_b och1

         WHERE och1.id = OCH.id)

       AND   pcpl.major_version = och.major_version  

       AND   xet.entity_id = xlate.entity_id

       AND   xet.application_id = xlate.application_id

       AND   rct.customer_trx_id = xlate.source_id_int_1

       AND   xal.ae_header_id = xah.ae_header_id

       AND   xet.event_id = xah.event_id

       AND   xah.ledger_id = Xal.ledger_id

   AND   xlp.LEGAL_EMPLOYER_FLAG='Y'

       AND   xal.accounting_class_code = 'RECEIVABLE'

       AND   rct.customer_trx_id = aps.customer_trx_id

       AND   fnbuv.bu_id = rct.org_id

       AND   xlp.legal_entity_id = rct.legal_entity_id

       and   xal.period_name = gpd.period_name

   AND   xlate.entity_code='TRANSACTIONS'

   and   xlate.ledger_id=xah.ledger_id

       and   trunc(xal.accounting_date) between gpd.period_start_date and gpd.period_end_date

       /* --- Prompts --- */

       and  (ppavl.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)

       and  (ppavl.carrying_out_organization_id IN (:p_carrying_out_organization_id) OR COALESCE(:p_carrying_out_organization_id, NULL) IS NULL)

       and  (fnbuv.bu_id IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

       and  (xlp.legal_entity_id IN (:p_legal_entity_id) OR COALESCE(:p_legal_entity_id, NULL) IS NULL)

       and  (och.id IN (:p_contract_id) OR COALESCE(:p_contract_id, NULL) IS NULL)

and exists ( 

              select 'Y'

              from   gl_seg_val_hier_cf cf,

                     fnd_tree_version_vl ftvvl,

                     xla_mapping_set_values xmsv

              where  cf.tree_structure_code='GL_ACCT_FLEX'

              and    ftvvl.tree_code=cf.tree_code

              and    xmsv.enabled_flag='Y'

              and    ftvvl.tree_version_id=cf.tree_version_id

              and    xmsv.value_constant=cf.dep0_pk1_value

              and    xmsv.input_value_constant1=TO_CHAR(ppavl.carrying_out_organization_id)

              and    exists (

                     select 'Y'

                     from   xla_mapping_set_flavors xmsf, 

                            fnd_id_flex_structures fifs

                     where  xmsf.stripe_id=fifs.id_flex_num

                     and    fifs.id_flex_code='GL#'

                     and    fifs.id_flex_structure_code='xxxx' 

                     and    xmsf.mapping_set_code=xmsv.mapping_set_code

                     and    xmsf.mapping_set_flavor_id=xmsv.mapping_set_flavor_id)

              and    sysdate between ftvvl.effective_start_date and ftvvl.effective_end_date

              and    sysdate between xmsv.effective_start_date and xmsv.effective_end_date

              /* --- Prompts --- */

              and   (cf.dep0_pk1_value in (:p_lob_profit_centre) or coalesce(:p_lob_profit_centre,null) is null)

              and   (cf.dep27_pk1_value in (:p_lob_sub_business_group) or coalesce(:p_lob_sub_business_group,null) is null)

              and   (cf.dep28_pk1_value in (:p_lob_business_group) or coalesce(:p_lob_business_group,null) is null)

              and   (cf.dep29_pk1_value in (:p_lob_service_bundle) or coalesce(:p_lob_service_bundle,null) is null)

              and   (cf.dep30_pk1_value in (:p_lob_division) or coalesce(:p_lob_division,null) is null)

              /* --- End Prompts --- */

              and    cf.tree_code='XXXXX'

              and    xmsv.mapping_set_code ='WDAP_LOB_POO'

              union all 

              select 'Y' 

              from   dual 

              where  coalesce(:p_lob_profit_centre,null) is null

              and    coalesce(:p_lob_sub_business_group,null) is null

              and    coalesce(:p_lob_business_group,null) is null

              and    coalesce(:p_lob_service_bundle,null) is null

              and    coalesce(:p_lob_division,null) is null)

       /* --- End Prompts --- */ 

       and    1=1

GROUP BY fnbuv.bu_id, 

          och.id,

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              pcpl.contract_id,

              fnbuv.bu_name,

              xlp.name,

              ppavl.name, 

              ppavl.segment1,

OCH.contract_number),

payables as (

SELECT fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              aia.invoice_id,              

              fnbuv.bu_name,

              xlp.name le_name,

              ppavl.name, 

              ppavl.segment1, 

SUM(NVL(aipa.INVOICE_BASE_AMOUNT, 0)) cash_out,

SUM(NVL(xal.ACCOUNTED_DR, 0)) liability_amt

FROM   ap_invoices_all aia,

ap_invoice_payments_all aipa,

fun_names_business_units_v fnbuv,

xla_transaction_entities xte,

xla_events xe,

xla_ae_headers xah,

xla_ae_lines xal,

xle_entity_profiles xlp,

gl_period_dates gpd,

              pjf_projects_all_vl ppavl

WHERE  1 = 1

AND   aipa.invoice_id = aia.invoice_id

       and   aia.project_id=ppavl.project_id

AND   fnbuv.bu_id = aia.org_id

AND   aia.invoice_id = xte.source_id_int_1

AND   aia.set_of_books_id = xah.ledger_id

AND   xe.entity_id = xah.entity_id

AND   xte.entity_id = xe.entity_id

AND   xal.ae_header_id = xah.ae_header_id

AND   xte.application_id = xah.application_id

and   xte.ledger_id=xah.ledger_id

    AND   xte.entity_code='TRANSACTIONS'

AND   xal.accounting_class_code = 'LIABILITY'

AND   xlp.legal_entity_id = aia.legal_entity_id

       and   xal.period_name = gpd.period_name

       and   trunc(xal.accounting_date) between gpd.period_start_date and gpd.period_end_date

       /* --- Prompts --- */

       and  (fnbuv.bu_id IN (:p_business_unit_id) OR COALESCE(:p_business_unit_id, NULL) IS NULL)

       and  (xlp.legal_entity_id IN (:p_legal_entity_id) OR COALESCE(:p_legal_entity_id, NULL) IS NULL)

       and  (ppavl.project_id IN (:p_project_id) OR COALESCE(:p_project_id, NULL) IS NULL)

       and  (ppavl.carrying_out_organization_id IN (:p_carrying_out_organization_id) OR COALESCE(:p_carrying_out_organization_id, NULL) IS NULL)

       and exists ( 

              select 'Y'

              from   gl_seg_val_hier_cf cf,

                     fnd_tree_version_vl ftvvl,

                     xla_mapping_set_values xmsv

              where  cf.tree_structure_code='GL_ACCT_FLEX'

              and    ftvvl.tree_code=cf.tree_code

              and    xmsv.enabled_flag='Y'

              and    ftvvl.tree_version_id=cf.tree_version_id

              and    xmsv.value_constant=cf.dep0_pk1_value

              and    xmsv.input_value_constant1=TO_CHAR(ppavl.carrying_out_organization_id) 

              and    exists (

                     select 'Y'

                     from   xla_mapping_set_flavors xmsf, 

                            fnd_id_flex_structures fifs

                     where  xmsf.stripe_id=fifs.id_flex_num

                     and    fifs.id_flex_code='GL#'

                     and    fifs.id_flex_structure_code='xxxxx' 

                     and    xmsf.mapping_set_code=xmsv.mapping_set_code

                     and    xmsf.mapping_set_flavor_id=xmsv.mapping_set_flavor_id)

              and    sysdate between ftvvl.effective_start_date and ftvvl.effective_end_date

              and    sysdate between xmsv.effective_start_date and xmsv.effective_end_date

              /* --- Prompts --- */

              and   (cf.dep0_pk1_value in (:p_lob_profit_centre) or coalesce(:p_lob_profit_centre,null) is null)

              and   (cf.dep27_pk1_value in (:p_lob_sub_business_group) or coalesce(:p_lob_sub_business_group,null) is null)

              and   (cf.dep28_pk1_value in (:p_lob_business_group) or coalesce(:p_lob_business_group,null) is null)

              and   (cf.dep29_pk1_value in (:p_lob_service_bundle) or coalesce(:p_lob_service_bundle,null) is null)

              and   (cf.dep30_pk1_value in (:p_lob_division) or coalesce(:p_lob_division,null) is null)

              /* --- End Prompts --- */

              and    cf.tree_code='XXXXX'

              and    xmsv.mapping_set_code ='WDAP_LOB_POO'

              union all 

              select 'Y' 

              from   dual 

              where  coalesce(:p_lob_profit_centre,null) is null

              and    coalesce(:p_lob_sub_business_group,null) is null

              and    coalesce(:p_lob_business_group,null) is null

              and    coalesce(:p_lob_service_bundle,null) is null

              and    coalesce(:p_lob_division,null) is null)

       /* --- End Prompts --- */ 

       and    1=1

GROUP BY fnbuv.bu_id, 

              xlp.legal_entity_id,

              ppavl.project_id,

              ppavl.carrying_out_organization_id,

              aia.invoice_id,              

              fnbuv.bu_name,

              xlp.name,

              ppavl.name, 

              ppavl.segment1)

SELECT ar.carrying_out_organization_id,

       ar.bu_name,  

       ar.le_name,        

ar.segment1, 

       ar.name project_name, 

       nvl(ar.cash_in,0) cash_in,

nvl(ar.billing_amt,0) billing_amt,

nvl(ap.cash_out,0) cash_out,

nvl(ap.liability_amt,0) liability_amt,

(nvl(ar.cash_in,0)-nvl(ap.cash_out,0)) cash_flow,

(nvl(ar.billing_amt,0)-nvl(ar.cash_in,0)) receivable_amt,

(nvl(ap.liability_amt,0)-nvl(ap.cash_out,0)) payable_amt

FROM   receivables ar, 

       payables ap 

WHERE  ar.bu_id=ap.bu_id(+)

AND    ar.legal_entity_id=ap.legal_entity_id(+)

AND    ar.project_id=ap.project_id (+)

order by ar.segment1,

       ar.bu_name, 

       ar.le_name