with apg_trial_balance_lvl2_base as
(
--noetix_sys.apg0_ou_acl_map_base xmap replaced by gl_ledgers and hr_operating_unit
--noetix_env_pkg.get_language replaced by userenv('lang')
select /*+ materialize */
xlatb.applied_to_application_id,
tbent.entity_code applied_to_entity_code,
xlatb.applied_to_entity_id,
tbent.transaction_number applied_to_transaction_num,
(case when tbent.entity_code = 'AP_INVOICES' then 'Invoice' when tbent.entity_code = 'AP_PAYMENTS' then 'Payment' else tbent.entity_code end) applied_to_transaction_type,
xlatb.code_combination_id,
xlatb.ledger_id,
xlatb.definition_code report_definition_code,
sum(nvl(xlatb.entered_rounded_dr,0))-sum(nvl(xlatb.entered_rounded_cr, 0)) rounded_applied_amount,
sum(nvl(xlatb.acctd_rounded_dr,0))-sum(nvl(xlatb.acctd_rounded_cr,0)) rounded_applied_amount_base,
:p_trial_bal_effective_date trial_balance_effective_date
from
xla.xla_transaction_entities tbent,
xla_trial_balances xlatb,
hr_operating_units hou,
gl_ledgers gl
where 3=3 and
2=2 and
nvl(tbent.security_id_int_1,-9999) = hou.organization_id and
hou.set_of_books_id= gl.ledger_id and
tbent.ledger_id=gl.ledger_id and
xlatb.applied_to_entity_id is not null and
tbent.ledger_id = xlatb.ledger_id and
xlatb.applied_to_entity_id = tbent.entity_id and
tbent.application_id = 200 and
xlatb.gl_date <= :p_trial_bal_effective_date
group by
(case when tbent.entity_code = 'AP_INVOICES' then 'Invoice' when tbent.entity_code = 'AP_PAYMENTS' then 'Payment' else tbent.entity_code end),
:p_trial_bal_effective_date,
tbent.entity_code,
tbent.transaction_number,
xlatb.applied_to_application_id,
xlatb.applied_to_entity_id,
xlatb.code_combination_id,
xlatb.definition_code,
xlatb.ledger_id
),
apg_trial_balance_lvl_base as
(
select /*+ materialize */
xlatb.applied_to_entity_id,
min(xlatb.gl_date) gl_date
from
xla_trial_balances xlatb,
hr_operating_units hou,
gl_ledgers gl
where 3=3 and
2=2 and
xlatb.applied_to_entity_id is not null and
hou.set_of_books_id= gl.ledger_id and
xlatb.ledger_id=gl.ledger_id
group by xlatb.applied_to_entity_id
),
apg_trial_balance_lvl1_base as
(
select /*+ materialize */
evcls.name accounting_event_class_name,
xlatb.code_combination_id code_combination_id,
max(xlatb.gl_date) gl_date,
xlatb.ledger_id ledger_id,
nvl(entty.security_id_int_1,-9999) org_id,
sum(nvl(xlatb.entered_rounded_cr,0)-nvl(xlatb.entered_rounded_dr,0)) original_amount,
sum(nvl(xlatb.acctd_rounded_cr,0)-nvl(xlatb.acctd_rounded_dr,0)) original_amount_base,
xlatb.party_id party_id,
xlatb.record_type_code record_type_code,
xlatb.definition_code report_definition_code,
tbdfn.name report_definition_name,
xlatb.source_entity_id source_entity_id,
vend.vendor_name supplier_name,
party.party_name third_party_name,
(case when vend.vendor_id is null then party.party_type else 'SUPPLIER' end) third_party_type_code,
xlatb.trx_currency_code transaction_currency_code,
entty.entity_code transaction_entity_code,
entty.source_id_int_1 transaction_id,
entty.transaction_number transaction_number,
(case when entty.entity_code = 'AP_INVOICES' then 'Invoice' when entty.entity_code='AP_PAYMENTS' then 'Payment' else entty.entity_code end)transaction_type,
:p_trial_bal_effective_date Trial_Balance_Effective_Date
from
apg_trial_balance_lvl_base inv,
xla_event_classes_tl evcls,
xla_tb_definitions_tl tbdfn,
hz_parties party,
ap_suppliers vend,
xla_trial_balances xlatb,
xla.xla_transaction_entities entty,
hr_operating_units hou,
gl_ledgers gl
where 3=3 and
2=2 and
nvl(entty.security_id_int_1,-9999) = hou.organization_id and
hou.set_of_books_id= gl.ledger_id and
entty.ledger_id=gl.ledger_id and
xlatb.source_entity_id = entty.entity_id and
xlatb.applied_to_entity_id is null and
entty.ledger_id = xlatb.ledger_id and
xlatb.source_application_id = entty.application_id and
xlatb.definition_code = tbdfn.definition_code and
xlatb.party_id = vend.vendor_id and
vend.party_id = party.party_id and
entty.application_id = 200 and
(inv.gl_date <= :p_trial_bal_effective_date or xlatb.gl_date<=:p_trial_bal_effective_date) and
evcls.language = userenv('lang') and
evcls.entity_code = entty.entity_code and
evcls.application_id = entty.application_id and
evcls.event_class_code = xlatb.event_class_code and
tbdfn.language = userenv('lang') and
inv.applied_to_entity_id (+) = entty.entity_id
group by
(case when entty.entity_code = 'AP_INVOICES' then 'Invoice' when entty.entity_code='AP_PAYMENTS' then 'Payment' else entty.entity_code end),
entty.entity_code,
entty.source_id_int_1,
entty.transaction_number,
evcls.name,
:p_trial_bal_effective_date,
nvl(entty.security_id_int_1,-9999),
party.party_name,
tbdfn.name,
vend.vendor_name,
xlatb.code_combination_id,
xlatb.definition_code,
xlatb.ledger_id,
xlatb.party_id,
(case when vend.vendor_id is null then party.party_type else 'SUPPLIER' end),
xlatb.record_type_code,
xlatb.source_entity_id,
xlatb.trx_currency_code
),
apg_trial_balance_details as
(
select
tbase.accounting_event_class_name,
tbase.code_combination_id,
ltb.rounded_applied_amount applied_amount,
ltb.rounded_applied_amount_base applied_amount_base,
ltb.applied_to_entity_code,
ltb.applied_to_transaction_num,
ltb.applied_to_transaction_type,
gl.currency_code currency_code_base,
gl.name ledger_name,
hou.name operating_unit_name,
tbase.original_amount,
tbase.original_amount_base,
tbase.record_type_code,
(
case when ltb.trial_balance_effective_date < tbase.gl_date
then - 1 * ltb.rounded_applied_amount
else
nvl(tbase.original_amount, 0) - nvl(ltb.rounded_applied_amount, 0)
end
) remaining_amount,
(
case when ltb.trial_balance_effective_date < tbase.gl_date
then - 1 * ltb.rounded_applied_amount_base
else
nvl(tbase.original_amount_base, 0) - nvl(ltb.rounded_applied_amount_base, 0)
end
) remaining_amount_base,
tbase.report_definition_name,
tbase.supplier_name supplier_name,
tbase.third_party_name third_party_name,
tbase.third_party_type_code third_party_type_code,
tbase.transaction_currency_code transaction_currency_code,
(select inv.invoice_date from ap_invoices_all inv where inv.invoice_id = tbase.transaction_id)transaction_date,
tbase.transaction_entity_code,
tbase.gl_date transaction_gl_date,
tbase.transaction_number,
tbase.transaction_type,
tbase.trial_balance_effective_date,
round((to_date(tbase.trial_balance_effective_date)-tbase.gl_date),0)age_days
from
apg_trial_balance_lvl2_base ltb,
apg_trial_balance_lvl1_base tbase,
hr_operating_units hou,
gl_ledgers gl
where 2=2 and
tbase.org_id = hou.organization_id and
hou.set_of_books_id= gl.ledger_id and
ltb.applied_to_entity_id (+) = tbase.source_entity_id and
ltb.code_combination_id (+) = tbase.code_combination_id and
tbase.trial_balance_effective_date = ltb.trial_balance_effective_date (+) and
ltb.report_definition_code (+) = tbase.report_definition_code
)
select
x.ledger_name,
&chart_of_accounts_name
x.operating_unit_name,
x.accounting_event_class_name,
&segment_columns
x.applied_amount,
x.applied_amount_base,
x.applied_to_entity_code,
x.applied_to_transaction_num,
x.applied_to_transaction_type,
x.currency_code_base,
x.original_amount,
x.original_amount_base,
x.record_type_code,
x.remaining_amount,
x.remaining_amount_base,
x.report_definition_name,
x.supplier_name,
x.third_party_name,
x.third_party_type_code,
x.transaction_currency_code,
x.transaction_date,
x.transaction_entity_code,
'' pmt,
x.transaction_gl_date,
x.transaction_number document_no,
x.transaction_type,
x.trial_balance_effective_date,
case when x.age_days>120
then '120 + days'
when x.age_days>91 and x.age_days<=120
then '90 -120 days'
when x.age_days>61 and x.age_days<=90
then '60 - 90 days'
when x.age_days>31 and x.age_days<=60
then '30 - 60 days'
else
'Current' end ageing,
x.age_days "Age(Days)"
from
apg_trial_balance_details x,
gl_code_combinations_kfv gcck
where
1=1 and
gcck.code_combination_id=x.code_combination_id |
