Query to Get the Oracle AR Invoice Account Details

SELECT                                     --distinct source_distribution_type

         gl.NAME, rctt.NAME AS trx_type, acra.trx_number, hp.party_name,

         --  (select name from gl_ledgers where ledger_id = acra.set_of_bookS_id) lEDGER_NAME,

         acra.customer_trx_id, acra.creation_date,

         gcc1.concatenated_segments AS ACCOUNT, acra.trx_date,

         DECODE (xah1.application_id,

                 200, 'Payables',

                 222, 'Receivables',

                 xah1.application_id

                ) SOURCE,

         xah1.gl_transfer_status_code, xah1.je_category_name,

         --xal1.code_combination_id,

         xal1.accounting_class_code,        

         --xte1.*

         --nvl(sum(xdl1.unrounded_accounted_dr),0) - nvl(sum(xdl1.unrounded_accounted_cr),0) xla_adjustment

         (NVL (SUM (xal1.accounted_dr), 0) - NVL (SUM (xal1.accounted_cr), 0)

         ) accounted_adjustment,

           NVL (SUM (xal1.entered_dr), 0)

         - NVL (SUM (xal1.entered_cr), 0) entered_adjustment

    FROM xla_ae_headers PARTITION (ar) xah1,

         xla_ae_lines PARTITION (ar) xal1,

         --xla_distribution_links xdl1

         --xla_events xe1,

         xla.xla_transaction_entities xte1,

         ra_customer_trx_all acra,

         gl_code_combinations_kfv gcc1,

         gl_ledgers gl,

         apps.ra_cust_trx_types_all rctt,

         hz_cust_accounts hca,

         hz_cust_acct_sites_all hcas,

         hz_cust_site_uses_all hcsu,

         hz_parties hp,

         hr_operating_units hou

   WHERE 1 = 1

     AND acra.set_of_books_id = gl.ledger_id

     AND acra.cust_trx_type_id = rctt.cust_trx_type_id

     AND xah1.application_id = 222

--     AND xah1.ledger_id = 2193                      --ARROW CENTRAL EUROPE

     --AND xah1.accounting_date BETWEEN :PERIOD_START_DATE   AND :PERIOD_END_DATE

     AND xal1.ae_header_id = xah1.ae_header_id

     --AND xal1.code_combination_id = :code_combination_id

     --and xe1.aevent_id=xah1.event_id and xe1.entity_id=xah1.entity_id

     AND xte1.entity_id = xah1.entity_id

     AND acra.customer_trx_id = xte1.source_id_int_1

     AND hca.cust_account_id = hcas.cust_account_id

     AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id

     AND hca.party_id = hp.party_id

     AND hcsu.site_use_code = 'BILL_TO'

     AND hou.organization_id = acra.org_id

     AND hcsu.org_id = hou.organization_id

     AND hca.cust_account_id = acra.sold_to_customer_id

     AND gcc1.code_combination_id = xal1.code_combination_id

--and xdl1.ae_header_id=xal1.ae_header_id and xdl1.ae_line_num=xal1.ae_line_num and xdl1.application_id=222

--and aca1.cash_receipt_id=xdl1.applied_to_source_id_num_1

GROUP BY xah1.application_id,

         xah1.gl_transfer_status_code,

         xah1.je_category_name,

         xal1.code_combination_id,

         xal1.accounting_class_code,

         acra.trx_number,

         acra.customer_trx_id,

         acra.creation_date,

         acra.trx_date,

         gcc1.concatenated_segments,

         gl.NAME,

         rctt.NAME,

         hp.party_name

  HAVING (NVL (SUM (xal1.accounted_dr), 0) - NVL (SUM (xal1.accounted_cr), 0)) <> 0

      OR NVL (SUM (xal1.entered_dr), 0) - NVL (SUM (xal1.entered_cr), 0) <> 0