GL Account Analysis

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)