Thursday, December 3, 2015

Query to Get Multiple Transfer to GL

SELECT DISTINCT
  gjh.period_name Period_Name,
  gjb.name        Batch_Name,
  gjb.group_id
  ,gjh.*
FROM
  gl_je_batches gjb,
  gl_import_references gir,
  gl_je_headers gjh,
  (select
     gir.gl_sl_link_id,
     gir.gl_sl_link_table,
     gh.ledger_id,
     count(1) count_ln
   from gl_import_references gir, gl_je_headers gh
   where gir.gl_sl_link_id IS NOT NULL
     and gir.gl_sl_link_table = 'XLAJEL'
     and (gir.gl_sl_link_id, gir.gl_sl_link_table, gh.ledger_id) in
      (select /*+ parallel(xah) parallel (xal) leading(xah) */
       distinct gl_sl_link_id, gl_sl_link_table, xah.ledger_id
       from xla_ae_headers xah, xla_ae_lines xal
       where xah.application_id = xal.application_id
       and xah.ae_header_id = xal.ae_header_id
       and xah.ledger_id = '1'
       -- and xah.application_id = '200'
       and xah.accounting_entry_status_code = 'F'
       and xah.accounting_date between '01-JAN-2008' and '31-MAY-2008'
       and (xah.upg_batch_id is null OR xah.upg_batch_id = -9999)
      )
     and gh.je_batch_id = gir.je_batch_id
     and gh.je_header_id = gir.je_header_id
     -- and UPPER(gh.je_source) = UPPER('Payables')
     and nvl(gh.accrual_rev_je_header_id, 0) = 0
   group by gir.gl_sl_link_id, gir.gl_sl_link_table, gh.ledger_id
   having count(1) > 1
  ) dup
WHERE dup.gl_sl_link_id = gir.gl_sl_link_id
  and dup.gl_sl_link_table = gir.gl_sl_link_table
  and gir.je_batch_id = gjb.je_batch_id
  and gir.je_header_id = gjh.je_header_id
  and gjh.ledger_id = dup.ledger_id
  and nvl(gjh.accrual_rev_je_header_id, 0) = 0
  -- and UPPER(gjh.je_source) = UPPER('Payables')
;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect