GL Account Analysis (Distributions)

with h as
(
select
&hierarchy_levels
x.flex_value_set_id,
x.child_flex_value_low,
x.child_flex_value_high
from
(
select
substr(sys_connect_by_path(ffvnh.parent_flex_value,'|'),2) path,
ffvnh.child_flex_value_low,
ffvnh.child_flex_value_high,
ffvnh.flex_value_set_id
from
(select ffvnh.* from fnd_flex_value_norm_hierarchy ffvnh where ffvnh.flex_value_set_id=:flex_value_set_id) ffvnh
where
connect_by_isleaf=1 and
ffvnh.range_attribute='C'
connect by nocycle
ffvnh.parent_flex_value between prior ffvnh.child_flex_value_low and prior ffvnh.child_flex_value_high and
ffvnh.flex_value_set_id=prior ffvnh.flex_value_set_id and
prior ffvnh.range_attribute='P'
start with
ffvnh.parent_flex_value=:parent_flex_value
) x
)
--
-- Main Query
--
select
&hierarchy_levels3
y.*
from
(
select
&hierarchy_levels2
x.*,
case when max(x.je_header_id) over (partition by x.ledger,x.concatenated_segments) is not null then 'Y' else 'N' end has_activity,
case when sum(case when x.record_type='Balance' then abs(nvl(x.accounted_amount,0)) else 0 end) over (partition by x.ledger,x.concatenated_segments)=0 then 'Y' else 'N' end zero_balance
from
(
select
case when count(distinct gp.period_num) over ()>1 then lpad(gp.period_num,2,'0')||' ' end||gjh.period_name period_name,
lpad(gp.period_num,2,'0')||' '||gjh.period_name period_name_label,
gl.name ledger,
(select gjsv.user_je_source_name from gl_je_sources_vl gjsv where gjh.je_source=gjsv.je_source_name) source_name,
gjh.external_reference reference,
(select gjcv.user_je_category_name from gl_je_categories_vl gjcv where gjh.je_category=gjcv.je_category_name) category_name,
gjb.name batch_name,
xxen_util.meaning(gjb.status,'MJE_BATCH_STATUS',101) batch_status,
gjh.posted_date,
gjh.name journal_name,
gjh.description journal_description,
gjh.doc_sequence_value document_number,
xxen_util.meaning(gjh.tax_status_code,'TAX_STATUS',101) tax_status_code,
gjl.je_line_num line_number,
gcck.concatenated_segments,
case when 1=row_number() over (partition by gjl.rowid order by xal.gl_sl_link_id,xdl.temp_line_num) then gjl.entered_dr end line_entered_dr,
case when 1=row_number() over (partition by gjl.rowid order by xal.gl_sl_link_id,xdl.temp_line_num) then gjl.entered_cr end line_entered_cr,
case when 1=row_number() over (partition by gjl.rowid order by xal.gl_sl_link_id,xdl.temp_line_num) then nvl(gjl.entered_dr,0)-nvl(gjl.entered_cr,0) end line_entered_amount,
case when 1=row_number() over (partition by gjl.rowid order by xal.gl_sl_link_id,xdl.temp_line_num) then gjl.accounted_dr end line_accounted_dr,
case when 1=row_number() over (partition by gjl.rowid order by xal.gl_sl_link_id,xdl.temp_line_num) then gjl.accounted_cr end line_accounted_cr,
case when 1=row_number() over (partition by gjl.rowid order by xal.gl_sl_link_id,xdl.temp_line_num) then nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) end line_accounted_amount,
gjl.description line_description,
coalesce(
zrb.tax_rate_code,
listagg(zl.tax_rate_code,', ') within group (order by zl.tax_rate_code) over (partition by nvl(rctla.link_to_cust_trx_line_id,rctla.customer_trx_line_id)),
aila.tax_rate_code,
aila.tax_classification_code
) tax_rate_code,
coalesce(
zrb.percentage_rate,
max(zl.tax_rate) over (partition by nvl(rctla.link_to_cust_trx_line_id,rctla.customer_trx_line_id)),
aila.tax_rate,
(
select
zl.tax_rate
from
zx.zx_lines zl
where
zl.application_id=200 and
zl.entity_code='AP_INVOICES' and
zl.event_class_code in ('STANDARD INVOICES','EXPENSE REPORTS','PREPAYMENT INVOICES') and
aila.invoice_id=zl.trx_id and
zl.trx_level_type='LINE' and
aila.line_number=zl.trx_line_id and
rownum=1
)
) tax_rate,
xxen_util.meaning(gjl.tax_line_flag,'YES_NO',0) tax_line,
xxen_util.meaning(gjl.taxable_line_flag,'YES_NO',0) taxable_line,
xxen_util.meaning(gjl.amount_includes_tax_flag,'YES_NO',0) amount_includes_tax,
xxen_util.meaning(xal.accounting_class_code,'XLA_ACCOUNTING_CLASS',602,'Y') accounting_class,
xxen_util.meaning(gcck.gl_account_type,'ACCOUNT_TYPE',0) account_type,
&segment_columns
nvl2(xal.gl_sl_link_id,xdl.unrounded_entered_dr,gjl.entered_dr) entered_dr,
nvl2(xal.gl_sl_link_id,xdl.unrounded_entered_cr,gjl.entered_cr) entered_cr,
nvl(nvl2(xal.gl_sl_link_id,xdl.unrounded_entered_dr,gjl.entered_dr),0)-nvl(nvl2(xal.gl_sl_link_id,xdl.unrounded_entered_cr,gjl.entered_cr),0) entered_amount,
nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code) transaction_currency,
nvl2(xal.gl_sl_link_id,xdl.unrounded_accounted_dr,gjl.accounted_dr) accounted_dr,
nvl2(xal.gl_sl_link_id,xdl.unrounded_accounted_cr,gjl.accounted_cr) accounted_cr,
nvl(nvl2(xal.gl_sl_link_id,xdl.unrounded_accounted_dr,gjl.accounted_dr),0)-nvl(nvl2(xal.gl_sl_link_id,xdl.unrounded_accounted_cr,gjl.accounted_cr),0) accounted_amount,
gl.currency_code ledger_currency,
&revaluation_columns
nvl(gjh.doc_sequence_value,xah.doc_sequence_value) doc_sequence_value,
(select xett.name from xla_event_types_tl xett where xte.application_id=xett.application_id and xte.entity_code=xett.entity_code and xe.event_type_code=xett.event_type_code and xett.language=userenv('lang')) event_type,
xal.currency_conversion_date,
(select gdct.user_conversion_type from gl_daily_conversion_types gdct where xal.currency_conversion_type=gdct.conversion_type) currency_conversion_type,
xal.currency_conversion_rate,
xxen_util.description(gjh.actual_flag,'BATCH_TYPE',101) balance_type,
(select gbv.budget_name from gl_budget_versions gbv where gjh.budget_version_id=gbv.budget_version_id) budget_name,
(select get.encumbrance_type from gl_encumbrance_types get where get.encumbrance_type_id = gjh.encumbrance_type_id) encumbrance_type,
gjh.currency_conversion_date conversion_date,
gjh.currency_conversion_type conversion_type,
gjh.currency_conversion_rate conversion_rate,
xah.description accounting_event_description,
xah.accounting_date,
xe.transaction_date,
xte.transaction_number,
--Source Line Description
coalesce(aida.description,aila.description,aila.item_description,rctla.description,pla.item_description,rsl.item_description) source_line_description,
--Assets
case
when xte.application_id = 140 and xte.entity_code = 'TRANSACTIONS'
then (select fab.asset_number from fa_additions_b fab,fa_transaction_headers fth where fth.asset_id=fab.asset_id and fth.transaction_header_id=xte.source_id_int_1 and fth.event_id = xe.event_id)
when xte.application_id = 140 and xte.entity_code = 'DEPRECIATION'
then (select fab.asset_number from fa_additions_b fab, fa_deprn_detail fdd where fab.asset_id=fdd.asset_id and fdd.asset_id=xte.source_id_int_1 and fdd.period_counter=xte.source_id_int_2 and fdd.event_id=xe.event_id and rownum=1)
end asset_number,
--AP
coalesce(aia.invoice_num,rcta.trx_number,(select distinct last_value(aia.invoice_num) over (order by aipa.invoice_payment_id range between unbounded preceding and unbounded following) from ap_invoice_payments_all aipa,ap_invoices_all aia where aipa.invoice_id=aia.invoice_id and aipa.check_id=aca.check_id)) invoice_number,
coalesce(aia.description,rcta.comments,(select distinct last_value(aia.description) over (order by aipa.invoice_payment_id range between unbounded preceding and unbounded following) from ap_invoice_payments_all aipa,ap_invoices_all aia where aipa.invoice_id=aia.invoice_id and aipa.check_id=aca.check_id)) invoice_description,
coalesce(aila.line_number,rctla.line_number) invoice_line_number,
coalesce(aia.invoice_date,rcta.trx_date,(select distinct last_value(aia.invoice_date) over (order by aipa.invoice_payment_id range between unbounded preceding and unbounded following) from ap_invoice_payments_all aipa,ap_invoices_all aia where aipa.invoice_id=aia.invoice_id and aipa.check_id=aca.check_id)) invoice_date,
aia.gl_date,
nvl(aia.invoice_currency_code,rcta.invoice_currency_code) invoice_currency,
nvl(aca.currency_code,aia.payment_currency_code) payment_currency,
nvl(xxen_util.meaning(aia.payment_method_code,'PAYMENT METHOD',200),aia.payment_method_code) payment_method,
nvl(aia.invoice_amount,(select sum(apsa.amount_due_original) from ar_payment_schedules_all apsa where rcta.customer_trx_id=apsa.customer_trx_id)) invoice_amount,
aca.check_number,
xxen_util.client_time(aca.check_date) payment_date,
xxen_util.meaning(aca.status_lookup_code,'CHECK STATE',200) check_state,
aca.amount check_amount,
aca.cleared_amount check_cleared_amount,
aipa.amount payment_amount,
decode(aca.currency_code,gl.currency_code,aipa.amount,aipa.payment_base_amount) payment_amount_functional,
cbbv.bank_name,
cbbv.eft_swift_code swift_code,
cba.bank_account_name,
cba.masked_account_num bank_account_num,
cba.masked_iban iban_number,
pha.segment1 purchase_order,
pra.release_num release,
pla.line_num purchase_order_line,
plla.shipment_num po_shipment_number,
nvl(rt.quantity,plla.quantity) quantity,
nvl(plla.price_override,pla.unit_price) price,
plla.quantity*nvl(plla.price_override,pla.unit_price) po_amount,
pha.currency_code po_currency,
--AR
coalesce(
ooha.order_number,
case
when xte.entity_code='TRANSACTIONS' and rcta.interface_header_context in ('ORDER ENTRY','INTERCOMPANY') then to_number(rcta.interface_header_attribute1)
when mmt.transaction_source_type_id in (2,8,12) then (select to_number(mso.segment1) from mtl_sales_orders mso where mmt.transaction_source_id=mso.sales_order_id)
end
) sales_order,
jrrev.resource_name salesperson,
(select name from ra_rules rr where rcta.invoicing_rule_id=rr.rule_id) invoice_rule,
(select rr.name from ra_customer_trx_lines_all rctla, ra_rules rr where rcta.customer_trx_id=rctla.customer_trx_id and rctla.line_type='LINE' and rctla.accounting_rule_id=rr.rule_id and rownum=1) accounting_rule,
nvl(rt.quantity,(plla.quantity-nvl(plla.quantity_cancelled,0))) po_quantity,
coalesce(
(select aps.vendor_name from ap_suppliers aps where coalesce(decode(xal.party_type_code,'S',xal.party_id,null),aia.vendor_id,aca.vendor_id,rt.vendor_id,pha.vendor_id)=aps.vendor_id),
(select hp.party_name from hz_cust_accounts hca, hz_parties hp where coalesce(decode(xal.party_type_code,'C',xal.party_id,null),rcta.bill_to_customer_id,acra.pay_from_customer,paa.customer_id)=hca.cust_account_id and hca.party_id=hp.party_id),
(select hp.party_name from hz_parties hp,hz_cust_accounts hca,oe_order_lines_all oola where hca.party_id=hp.party_id and hca.cust_account_id=oola.sold_to_org_id and oola.line_id=coalesce(gxeh.source_line_id,case when mmt.transaction_source_type_id in (2,8,12) then mmt.trx_source_line_id end))
) vendor_or_customer,
--Projects
nvl(case when xte.entity_code='TRANSACTIONS' and rcta.interface_header_context='PROJECTS INVOICES' then rcta.interface_header_attribute1 end,ppa.segment1) project,
pt.task_number task,
pea.expenditure_group,
xxen_util.meaning(pea.expenditure_class_code,'EXPENDITURE CLASS CODE',275) expenditure_class_code,
xxen_util.meaning(pea.expenditure_status_code,'EXPENDITURE STATUS',275) expenditure_status_code,
pet.expenditure_category,
peia.expenditure_type,
pet.description expenditure_type_description,
peia.expenditure_item_date,
peia.quantity expenditure_item_quantity,
xxen_util.meaning(pet.unit_of_measure,'UNIT',275) expenditure_unit_of_measure,
papf.full_name incurred_by_person,
nvl(papf.employee_number,papf.npw_number) incurred_by_employee_number,
--WIP
bd.department_code,
br.resource_code,
we.wip_entity_name wip_job,
wt.operation_seq_num,
nvl(wt.transaction_quantity,mmt.transaction_quantity) transaction_quantity,
nvl(wt.transaction_uom,mmt.transaction_uom) transaction_uom,
nvl(wt.primary_quantity,mmt.primary_quantity) primary_quantity,
--Inventory
xxen_util.client_time(mmt.transaction_date) inv_transaction_date,
mp.organization_code inv_organization,
coalesce(mmt.subinventory_code,rt.subinventory,rsl.to_subinventory) inv_subinventory,
msiv.concatenated_segments inv_item,
msiv.description inv_item_description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) inv_item_type,
mmt.transaction_cost inv_transaction_unit_cost,
mmt.actual_cost inv_actual_unit_cost,
mmt.transaction_reference inv_transaction_reference,
mtst.transaction_source_type_name inv_transaction_source_type,
mtt.transaction_type_name inv_transaction_type,
case
when mmt.transaction_source_type_id=6 then (select mgd.segment1 from mtl_generic_dispositions mgd where mmt.transaction_source_id=mgd.disposition_id and mmt.organization_id=mgd.organization_id) --Account Alias
when mmt.transaction_source_type_id in (2,8,12) then (select mso.segment1||'.'||mso.segment2||'.'||mso.segment3 from mtl_sales_orders mso where mmt.transaction_source_id=mso.sales_order_id)--Sales Order, Internal Order, RMA
when mmt.transaction_source_type_id=11 then (select ccu.description from cst_cost_updates ccu where mmt.transaction_source_id=ccu.cost_update_id) --Cost Update
when mmt.transaction_source_type_id=9 then (select mcch.cycle_count_header_name from mtl_cycle_count_headers mcch where mcch.cycle_count_header_id=mmt.transaction_source_id) --Cycle Count
when mmt.transaction_source_type_id=3 then (select gcck.concatenated_segments from gl_code_combinations_kfv gcck where mmt.transaction_source_id=gcck.code_combination_id) --Account
when mmt.transaction_source_type_id=13 or mmt.transaction_source_type_id>100 then mmt.transaction_source_name --Inventory
when mmt.transaction_source_type_id=10 then (select mpi.physical_inventory_name from mtl_physical_inventories mpi where mmt.transaction_source_id=mpi.physical_inventory_id and mmt.organization_id=mpi.organization_id) --Physical Inventory
when mmt.transaction_source_type_id=1 then pha.segment1 --PO
when mmt.transaction_source_type_id=16 then (select okhab.contract_number from okc_k_headers_all_b okhab where mmt.transaction_source_id=okhab.id) --Project Contracts
when mmt.transaction_source_type_id=7 then (select prha.segment1 from po_requisition_headers_all prha where mmt.transaction_source_id=prha.requisition_header_id) --Requisition
when mmt.transaction_source_type_id=5 then (select we.wip_entity_name from wip_entities we where mmt.transaction_source_id=we.wip_entity_id) --WIP Job or Schedule
when mmt.transaction_source_type_id=4 then (select mtrh.request_number from mtl_txn_request_headers mtrh where mmt.transaction_source_id=mtrh.header_id) --Move Order
end inv_transaction_source,
mmt.transaction_id inv_transaction_id,
coalesce(pla.operating_unit,
(select haouv.name from hr_all_organization_units_vl haouv where
coalesce(
aca.org_id,
aida.org_id,
aipa.org_id,
aaa.org_id,
acra.org_id,
rcta.org_id,
rctla.org_id,
cbaua.org_id,
jrs.org_id,
ooha.org_id,
oola.org_id,
paa.org_id,
pdra.org_id,
pea.org_id,
peia.org_id,
ppa.org_id,
pda.org_id,
pha.org_id,
plla.org_id,
pra.org_id,
prda.org_id
)=haouv.organization_id)) operating_unit,
--AP/AR MDM Party/Site Identifier
nvl2(xal.party_type_code,xal.party_type_code||'-'||nvl(to_char(xal.party_id),'UNKNOWN')||'-'||nvl(to_char(xal.party_site_id),'0'),null) mdm_party_id,
case xal.party_type_code
when 'C'
then xal.party_type_code || '-' ||
     nvl((select hp.party_name from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id=xal.party_id),'UNKNOWN') || '-' ||
     nvl((select hcsua.location from hz_cust_site_uses_all hcsua where hcsua.site_use_id = xal.party_site_id),'0')
when 'S'
then xal.party_type_code || '-' ||
     nvl((select aps.vendor_name from ap_suppliers aps where aps.vendor_id=xal.party_id),'UNKNOWN') || '-' ||
     nvl((select apssa.vendor_site_code from ap_supplier_sites_all apssa where apssa.vendor_site_id = xal.party_site_id),'0')
end mdm_party_desc,
--Record history and ID colu