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