Query to find adjustments in oracle apps

Query to find adjustments in oracle apps

In this post , We will be discuss about the AR adjustment SQL query in oracle apps. First , i will try to explain the Adjustment purpose in oracle apps. We do create the adjustments in oracle apps , to write of the Balances either from AR Invoice or from AR receipt. For example We have raised a Customer Invoice of $20 but customer has only given the receipt of $19 and now we want to write off the remaining balance of $1 so we will create the adjustments to write off the remaining amount and close the debit amount. These type of Adjustment details stored by the AR adjustment table in oracle apps. We can write the queries using these adjustment tables to find out the adjustment details in oracle apps. Here below i will be share the detail Query to find the adjustment detail in oracle apps. 

Important Table to store the AR adjustments in oracle apps

1. AR_ADJUSTMENTS_ALL

Detail SQL Query to find adjustments in oracle apps

This below query helps to find the Ar adjustment details in oracle apps. This query will extract the AR adjustment details from GL to AR.

SELECT 

       gjh.doc_sequence_value voucher_no,

       gjh.je_source,

       gjh.je_category,

       gjh.period_name,

       gjh.status,

       gjh.actual_flag,

       gjh.default_effective_date,

       gjl.je_line_num,

       gjl.description description,

       xal.accounted_dr debit,

       xal.accounted_cr credit,

          gcc.segment1

       || '-'

       || gcc.segment2

       || '-'

       || gcc.segment3

       || '-'

       || gcc.segment4

       || '-'

       || gcc.segment5

          gl_code,

       TO_CHAR (ADJUST_TBL.adjustment_number) aDJUSTMNET_trx_num,

       ADJUST_TBL.apply_date aDJUSTMENT_trx_date

  FROM gl.gl_je_headers gjh,

       gl.gl_je_lines gjl,

       gl.gl_code_combinations gcc,

       gl.gl_periods glp,

       gl.gl_import_references imp,

       xla.xla_ae_lines xal,

       xla.xla_ae_headers xah,

       xla.xla_events xe,

       xla.xla_transaction_entities xte,

       ar_adjustments_all ADJUST_TBL

 WHERE     1 = 1

       AND gjh.je_header_id = gjl.je_header_id

       AND gjl.status || '' = 'P'

       AND gjl.code_combination_id = gcc.code_combination_id

       AND gjh.period_name = glp.period_name

       AND glp.gjh.period_name = :p_period_name

       AND glp.adjustment_period_flag <> 'Y'

       AND gjh.je_source = 'Receivables'

       AND gjl.je_header_id = imp.je_header_id

       AND gjl.je_line_num = imp.je_line_num

       AND imp.gl_sl_link_id = xal.gl_sl_link_id

       AND imp.gl_sl_link_table = xal.gl_sl_link_table

       AND xal.application_id = xah.application_id

       AND xal.ae_header_id = xah.ae_header_id

       AND xah.application_id = xe.application_id

       AND xah.event_id = xe.event_id

       AND xe.application_id = xte.application_id

       AND xte.application_id = 222

       AND xe.entity_id = xte.entity_id

       AND xte.entity_code = 'ADJUSTMENTS'

       AND xte.source_id_int_1 = ADJUST_TBL.adjustment_id