Wednesday, August 31, 2016

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect