Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 6 July 2017

Query to Get GL Journal Reconciliation Details in Oracle Applications R12



This query provide the details of GL Journal Reconciliation details



SELECT c.name ledger_name,
         a.name journal_name,
         e.request_id,
         e.GROUP_ID,
         a.je_batch_id,
         (SELECT INITCAP (ppf.full_name)
            FROM per_all_people_f ppf, fnd_user fu
           WHERE fu.user_id = e.posted_by AND ppf.person_id = fu.employee_id
                 AND SYSDATE BETWEEN ppf.effective_start_date
                                 AND ppf.effective_start_date)
            posted_by,
         a.accrual_rev_flag,
         a.accrual_rev_status,
         a.je_category user_je_category_name,
         a.je_source user_je_source_name,
         d.segment1 company,
         a.period_name,
         DECODE (e.status,  'P', 'Posted',  'U', 'Unposted') unposted_status,
         TO_DATE (TRUNC (a.posted_date), 'DD-MM-YYYY hh24:mi:ss') posted_date,
         (SELECT user_name
            FROM fnd_user
           WHERE user_id = e.posted_by)
            user_name,
         b.je_line_num,
         (   d.segment1
          || '.'
          || d.segment2
          || '.'
          || d.segment3
          || '.'
          || d.segment4
          || '.'
          || d.segment5
          || '.'
          || d.segment6
          || '.'
          || d.segment7
          || '.'
          || d.segment8
          || '.'
          || d.segment9)
            concatenated_segments,
         c.currency_code,
         b.entered_dr,
         b.entered_cr,
         b.accounted_dr,
         b.accounted_cr,
         DECODE (c.currency_code,
                 'USD', b.entered_dr,
                 b.entered_dr * currency_conversion_rate)
            entered_dr_in_usd,
         DECODE (c.currency_code,
                 'USD', b.entered_cr,
                 b.entered_cr * currency_conversion_rate)
            entered_cr_in_usd,
         DECODE (c.currency_code,
                 'USD', b.accounted_dr,
                 b.accounted_dr * currency_conversion_rate)
            accounted_dr_in_usd,
         DECODE (c.currency_code,
                 'USD', b.accounted_cr,
                 b.accounted_cr * currency_conversion_rate)
            accounted_cr_in_usd
    FROM apps.gl_je_headers a,
         apps.gl_je_lines b,
         apps.gl_ledgers c,
         apps.gl_code_combinations d,
         apps.gl_je_batches e
   WHERE     a.je_header_id = b.je_header_id
         AND a.ledger_id = c.ledger_id
         AND b.code_combination_id = d.code_combination_id
         AND a.je_batch_id = e.je_batch_id
         AND e.chart_of_accounts_id = c.chart_of_accounts_id
   --      and rownum<100
       ORDER BY a.je_source, a.name, b.je_line_num


Share this article :

0 comments:

Post a Comment