Tuesday 19 November 2013

AR to GL Query

Provide Set of book id, Period name, AR invoice/Transaction to the following query. It will give all GL Batch Name, GL Header and Line details along with the Posted status at the line level.

Query:
SELECT gjl.reference_4 AR_Transaction,
       gjb.je_batch_id,
       gjb.name batch_name,
       gjh.je_header_id,
       gjh.name Journal_Header_Name,
       DECODE (gjh.ACTUAL_FLAG,
               'A', 'Actual',
               'B', 'Budget',
               'E', 'Encumbrance')
          Balance_Type,
       gjl.subledger_doc_sequence_value Doc_Seq_Value,
       gjl.effective_date GL_Date,
       gjl.code_combination_id,
       gjl.accounted_dr,
       gjl.accounted_cr,
       gjl.description Description,
       DECODE (gjl.status,  'P', 'Posted',  'U', 'Unposted',  gjl.status)
          Journal_Line_Status
  FROM gl_je_headers gjh, gl_je_lines gjl, gl_je_batches gjb
 WHERE     gjh.je_source = 'Receivables'
       AND gjh.je_header_id = gjl.je_header_id
       AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
       AND gjh.JE_HEADER_ID = gjl.JE_HEADER_ID
       AND gjh.period_name = gjb.default_period_name
       AND gjh.set_of_books_id = &sob_id
       AND gjh.period_name = '&Period_Name'
       AND gjl.reference_4 = '&AR_trx_number';

No comments:

Post a Comment