Saturday, May 17, 2014

AR Transactions to GL Drill Down

select * from RA_CUSTOMER_TRX_ALL  where trx_number = &trx_num --pk customer_trx_id ; trx_number=100771
select * from RA_CUST_TRX_LINE_GL_DIST_ALL where customer_trx_id=1557 --pk cust_trx_line_gl_dist_id
select * from XLA_DISTRIBUTION_LINKS where source_distribution_id_num_1 in (1471,1331) --(373185,373186,373191) --pk application_id
select * from XLA_AE_LINES where ae_header_id=(14807) -- 193233 --pk ae_header_id
select * from GL_CODE_COMBINATIONS where code_combination_id in (98480,98482,98483)
select * from GL_LEDGERS where ledger_id=2023 --xla_ae_line links with this table using ledger_id

select distinct t.* from (
select gl.name Ledger_Name,
       (gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' ||
       gcc.segment4 || '-' || gcc.segment5 || '-' || gcc.segment6) Account,
       rctlgda.gl_date Gl_Date,
       xal.accounting_class_code class,
       xal.accounted_dr,
       xal.accounted_cr
  from GL_LEDGERS                   gl,
       GL_CODE_COMBINATIONS         gcc,
       XLA_AE_LINES                 xal,
       XLA_DISTRIBUTION_LINKS       xdl,
       RA_CUST_TRX_LINE_GL_DIST_ALL rctlgda,
       RA_CUSTOMER_TRX_ALL          rcta
 where gl.ledger_id = xal.ledger_id
   and gcc.code_combination_id = xal.code_combination_id
   and xal.ae_header_id = xdl.ae_header_id
   and xdl.source_distribution_id_num_1 = rctlgda.cust_trx_line_gl_dist_id
   and rctlgda.customer_trx_id = rcta.customer_trx_id
   and rcta.trx_number = &trx_number
   and xdl.source_distribution_type ='RA_CUST_TRX_LINE_GL_DIST_ALL' ) t

0 comments:

Post a Comment