GL Account Analysis

Description
Categories: Enginatics
Repository: Github
Detail GL transaction report with one line per transaction including all segments and subledger data, with amounts in both transaction currency and ledger currency.
Run GL Account Analysis and other Oracle EBS reports with Blitz Report™ on our demo environment
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 --GL Data Query
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,
gjsv.user_je_source_name source_name, --XXECL
gjh.external_reference reference,
gjcv.user_je_category_name category_name, --XXECL
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,
xxen_util.meaning(gjh.tax_status_code,'TAX_STATUS',101) tax_status_code,
gjl.je_line_num line_number,
gcck.concatenated_segments,
fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_seg', 'SQLGL', 'GL#', gcck.chart_of_accounts_id, NULL, gcck.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') concatenated_segments_desc,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id) then gjl.entered_dr end line_entered_dr,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id) then gjl.entered_cr end line_entered_cr,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id) then nvl(gjl.entered_dr,0)-nvl(gjl.entered_cr,0) end line_entered_amount,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id) then gjl.accounted_dr end line_accounted_dr,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id) then gjl.accounted_cr end line_accounted_cr,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id) then nvl(gjl.accounted_dr,0)-nvl(gjl.accounted_cr,0) end line_accounted_amount,
gjl.description line_description,
zrb.tax_rate_code,
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
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num) then nvl2(xal.gl_sl_link_id,xal.entered_dr,gjl.entered_dr) end entered_dr,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num) then nvl2(xal.gl_sl_link_id,xal.entered_cr,gjl.entered_cr) end entered_cr,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num) then nvl(nvl2(xal.gl_sl_link_id,xal.entered_dr,gjl.entered_dr),0)-nvl(nvl2(xal.gl_sl_link_id,xal.entered_cr,gjl.entered_cr),0) end entered_amount,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num) then nvl2(xal.gl_sl_link_id,xal.currency_code,gjh.currency_code) end transaction_currency,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num) then nvl2(xal.gl_sl_link_id,xal.accounted_dr,gjl.accounted_dr) end accounted_dr,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num) then nvl2(xal.gl_sl_link_id,xal.accounted_cr,gjl.accounted_cr) end accounted_cr,
case when 1=row_number() over (partition by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num order by gjl.je_header_id,gjl.je_line_num,xal.gl_sl_link_id,xal.ae_header_id,xal.ae_line_num) then nvl(nvl2(xal.gl_sl_link_id,xal.accounted_dr,gjl.accounted_dr),0)-nvl(nvl2(xal.gl_sl_link_id,xal.accounted_cr,gjl.accounted_cr),0) end accounted_amount,
gl.currency_code ledger_currency,
&revaluation_columns
(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 gjh.encumbrance_type_id=get.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,
nvl(xah.accounting_date,gjh.default_effective_date) accounting_date, 
xe.transaction_date,
xte.transaction_number,
-- Document Sequences
gjh.doc_sequence_id document_seq_id, --XXECL      
(select fds.name from fnd_document_sequences fds where fds.doc_sequence_id = gjh.doc_sequence_id)  document_seq_name, --XXECL       
gjh.doc_sequence_value document_seq_value, --XXECL
nvl(gjl.subledger_doc_sequence_id,xah.doc_sequence_id) sub_doc_seq_id, --XXECL  
(select fds.name from fnd_document_sequences fds where fds.doc_sequence_id = nvl(gjl.subledger_doc_sequence_id,xah.doc_sequence_id))  sub_doc_seq_name, --XXECL
nvl(gjl.subledger_doc_sequence_value,xah.doc_sequence_value)    sub_doc_seq_val, --XXECL
gjh.close_acct_seq_value gl_close_acct_seq_val, -- XXECL
xah.close_acct_seq_value sl_close_acct_seq_val, --XXECL
nvl2(xe.event_id,xah.close_acct_seq_value,gjh.close_acct_seq_value) reporting_seq, --XXECL
--
decode(xal.gl_sl_link_id,
       null,(select gjlr.jgzz_recon_ref from gl_je_lines_recon gjlr where gjlr.je_header_id=gjl.je_header_id and gjlr.je_line_num=gjl.je_line_num),
       xal.jgzz_recon_ref
      ) reconciliation_reference, --XXECL
--
&lp_gjl_dff_cols
--
case
when nvl2(xe.event_id,decode(gjsv.user_je_source_name,'Assets' ,xah.je_category_name,'Payables',xah.je_category_name,'Receivables',xah.je_category_name,'Cost Management',xah.je_category_name,gjcv.user_je_category_name),gjcv.user_je_category_name) in ('Payments','Receipts')
then gjsv.user_je_source_name
when nvl2(xe.event_id,decode(gjsv.user_je_source_name,'Assets' ,xah.je_category_name,'Payables',xah.je_category_name,'Receivables',xah.je_category_name,'Cost Management',xah.je_category_name,gjcv.user_je_category_name),gjcv.user_je_category_name) = 'Receiving'
then
  case
  when xe.event_id is not null and gjsv.user_je_source_name = 'Cost Management' and nvl2(xe.event_id,decode(gjsv.user_je_source_name,'Assets' ,xah.je_category_name,'Payables',xah.je_category_name,'Receivables',xah.je_category_name,'Cost Management',xah.je_category_name,gjcv.user_je_category_name),gjcv.user_je_category_name) = 'Receiving' and xte.entity_code = 'WO_ACCOUNTING_EVENTS'
  then
     (select
       pha.attribute6
      from
       cst_write_offs cwos,
       po_distributions_all poda,
       po_headers_all pha
      where
        pha.po_header_id=poda.po_header_id and
        poda.po_distribution_id = cwos.po_distribution_id and
        cwos.write_off_id = xte.transaction_number
     )
  when xe.event_id is not null and gjsv.user_je_source_name = 'Cost Management' and nvl2(xe.event_id,decode(gjsv.user_je_source_name,'Assets' ,xah.je_category_name,'Payables',xah.je_category_name,'Receivables',xah.je_category_name,'Cost Management',xah.je_category_name,gjcv.user_je_category_name),gjcv.user_je_category_name) = 'Receiving'
  then
     (select
       pha.attribute6
      from
       rcv_transactions rt,
       rcv_shipment_headers rsh,
       po_headers_all pha
      where
        pha.po_header_id=rt.po_header_id and
        rsh.shipment_header_id = rt.shipment_header_id and
        rt.transaction_id = xte.transaction_number
     )
  else
   null
  end
when xe.event_id is not null and gjsv.user_je_source_name = 'Receivables'
then
  coalesce
  (
   (select distinct
     rabs.name
    from
     ra_cust_trx_line_gl_dist_all ractl,
     ra_customer_trx_all ract,
     ra_customer_trx_lines_all ractla,
     ra_batch_sources_all rabs
    where
     rabs.batch_source_id = ract.batch_source_id and
     ract.customer_trx_id = ractla.customer_trx_id and
     ractla.customer_trx_id = ractl.customer_trx_id and
     ractla.customer_trx_line_id = ractl.customer_trx_line_id and
     ractl.event_id = xe.event_id  and
     ractl.code_combination_id = gjl.code_combination_id and
     rownum=1
   ),
   (select distinct
     rabs.name
    from
     ar_receivable_applications_all araa,
     ra_customer_trx_all ract,
     ra_customer_trx_lines_all ractla,
     ra_batch_sources_all rabs
    where
     rabs.batch_source_id = ract.batch_source_id and
     ract.customer_trx_id = ractla.customer_trx_id and
     ractla.customer_trx_id = araa.customer_trx_id and
     araa.event_id = xe.event_id and
     rownum=1
   ),
   gjsv.user_je_source_name
  )
when xe.event_id is not null and gjsv.user_je_source_name = 'Payables'
then
  case
  when nvl2(xe.event_id,decode(gjsv.user_je_source_name,'Assets' ,xah.je_category_name,'Payables',xah.je_category_name,'Receivables',xah.je_category_name,'Cost Management',xah.je_category_name,gjcv.user_je_category_name),gjcv.user_je_category_name) = 'Payments'
  then
    nvl(
    (select distinct
      apia.source
     from
      ap_invoices_all apia,
      ap_invoice_lines_all apila,
      ap_invoice_payments_all aip
     where
     aip.accounting_event_id = xe.event_id and
     aip.payment_num = xe.event_number and
     apia.invoice_id = aip.invoice_id and
     apila.invoice_id = aip.invoice_id and
     rownum=1
    ),gjsv.user_je_source_name)
  else
    nvl(
    (select distinct
      apia.source
     from
      ap_invoices_all apia,
      ap_invoice_lines_all apila,
      ap_invoice_distributions_all apida,
      xla_distribution_links xdl
     where
      xdl.application_id = 200 and
      xdl.ae_header_id = xal.ae_header_id and
      xdl.ae_line_num = xal.ae_line_num and
      xdl.source_distribution_type = 'AP_INV_DIST' and
      xdl.source_distribution_id_num_1 = apida.invoice_distribution_id and
      apia.invoice_id = apida.invoice_id and
      apila.invoice_id = apida.invoice_id and
      apila.line_number = apida.invoice_line_number and
      rownum=1
    ),
    gjsv.user_je_source_name
   )
  end
else
 gjsv.user_je_source_name
end sl_source,
case
when gjsv.user_je_source_name='Assets' and xte.source_id_int_3 is not null
then to_char(xte.source_id_int_3)
when gjsv.user_je_source_name='Payables' and xte.transaction_number is not null
then
 case when decode(gjsv.user_je_source_name,'Assets' ,xah.je_category_name,'Payables',xah.je_category_name,'Receivables',xah.je_category_name,'Cost Management',xah.je_category_name,gjcv.user_je_category_name)='Payments'
 then
   (select distinct aba.batch_name from
     ap_batches_all aba,
     ap_invoices_all aia2,
     ap_invoice_payments_all aipa
    where
     aia2.batch_id = aba.batch_id and
     aia2.invoice_id = aipa.invoice_id and
     aipa.check_id = xte.source_id_int_1 and
     rownum=1
   )
 else
   (select
     aba.batch_name
    from
     ap_batches_all aba
    where
     aba.batch_id = aia.batch_id and
     rownum=1
   )
 end
when gjsv.user_je_source_name='Receivables' and xte.transaction_number is not null
then
 case when decode(gjsv.user_je_source_name,'Assets' ,xah.je_category_name,'Payables',xah.je_category_name,'Receivables',xah.je_category_name,'Cost Management',xah.je_category_name,gjcv.user_je_category_name)='Receipts'
 then
   (select distinct
     nvl(aba.name ,'')
    from
     ar_cash_receipt_history_all acrha,
     ar_batches_all aba
    where
     aba.batch_id = acrha.batch_id and
     acrha.cash_receipt_id = xte.source_id_int_1 and
     rownum=1
   )
 else
   (select
     rba.name
    from
     ra_batches_all rba
    where
     rba.batch_id = rcta.batch_id and
     rownum=1
   )
 end
else
 null
end sl_batch_no,
--Assets
fab.asset_number,
--AP
nvl(aia.invoice_num,rcta.trx_number) invoice_number,
nvl(aia.description,rcta.comments) description,
nvl(aia.invoice_date,rcta.trx_date) invoice_date,
aia.gl_date,
nvl(aia.invoice_currency_code,rcta.invoice_currency_code) invoice_currency,
aia.payment_currency_code payment_currency,
nvl(xxen_util.meaning(aia.payment_method_code,'PAYMENT METHOD',200),aia.payment_method_code) payment_method,
coalesce(aia.invoice_amount,apsa.amount_due_original) invoice_amount,
pha.segment1 purchase_order,
pra.release_num release,
rt.quantity po_quantity,
prha.segment1 requisition,
prla.line_num requisition_line,
--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=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,
coalesce(aps.segment1,hca.account_number) vendor_or_customer_number, --XXECL
coalesce(aps.vendor_name,hp.party_name) vendor_or_customer_name, --XXECL
coalesce(assa.vendor_site_code,hcsua.location) vendor_or_customer_site, --XXECL
--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,
decode(xal.party_type_code,
'C',xal.party_type_code||'-'||nvl(hp.party_name,'UNKNOWN')||'-' ||nvl(hcsua.location,'0'),
'S',xal.party_type_code||'-'||nvl(aps.vendor_name,'UNKNOWN')|| '-'||nvl(assa.vendor_site_code,'0')
) mdm_party_desc,
--Projects
coalesce(ppa.segment1,case when xte.application_id=222 and xte.entity_code='TRANSACTIONS' and rcta.interface_header_context='PROJECTS INVOICES' then rcta.interface_header_attribute1 end) 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 employee_name,
nvl(papf.employee_number,papf.npw_number) employee_number,
--Payroll
(select pjv.name from per_jobs_vl pjv where paaf.job_id=pjv.job_id) job,
(select hapft.name from hr_all_positions_f_tl hapft where paaf.position_id=hapft.position_id and hapft.language=userenv('lang')) position,
(select haouv.name from hr_all_organization_units_vl haouv where paaf.organization_id=haouv.organization_id) assignment_organization,
--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,
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 mgd.disposition_id=mmt.transaction_source_id and mgd.organization_id=mmt.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 mso.sales_order_id=mmt.transaction_source_id)--Sales Order, Internal Order, RMA
when mmt.transaction_source_type_id=11 then (select ccu.description from cst_cost_updates ccu where ccu.cost_update_id=mmt.transaction_source_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 gcck