Tuesday, June 19, 2012

AP Trail Balances SQL Query for R12

SELECT  aia.invoice_num, aia.description inv_description,aia.attribute2 "Dept", aps.segment1 vendor#,
       aps.vendor_name, aia.invoice_currency_code, aia.invoice_amount,tb.diff "Amount Remain",
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment5
       || '.'
       || gcc.segment6
       || '.'
       || gcc.segment7 ACCOUNT,
       aia.invoice_type_lookup_code,
       aia.invoice_date, aia.attribute5 status,
       ap.NAME terms
  FROM xla.xla_transaction_entities xte,
       (SELECT   tb.code_combination_id,
                 NVL (tb.applied_to_entity_id, tb.source_entity_id) entity_id,
                 SUM (NVL (tb.acctd_rounded_cr, 0)),
                 SUM (NVL (tb.acctd_rounded_dr, 0)),
                   SUM (NVL (tb.acctd_rounded_cr, 0))
                 - SUM (NVL (tb.acctd_rounded_dr, 0)) diff,
                 party_id
            FROM xla_trial_balances tb
           WHERE tb.definition_code =:definition_code  -- Ex :  'AP_200_1001'
               and trunc(tb.gl_date) <=TO_DATE ('1-NOV-2011')
                       GROUP BY tb.code_combination_id,
                 NVL (tb.applied_to_entity_id, tb.source_entity_id),
                 tb.party_id
          HAVING SUM (NVL (tb.acctd_rounded_cr, 0)) <>
                                            SUM (NVL (tb.acctd_rounded_dr, 0))) tb,
       ap_invoices_all aia,
       ap_suppliers aps,
       gl_code_combinations gcc,
       ap_terms ap
 WHERE tb.entity_id = xte.entity_id
   AND xte.application_id =:application_id   --Ex :  200
   AND xte.source_id_int_1 = aia.invoice_id
   AND aia.vendor_id = aps.vendor_id
   AND tb.code_combination_id = gcc.code_combination_id
   AND ap.term_id = aia.terms_id
Posted by at

7 comments:

  1. AnonymousApril 19, 2013 at 12:27 PM

    Thanks buddy. You saved me from another week long of experiments!

    regards,

    ReplyDelete
  2. deepthiJune 11, 2015 at 8:22 AM

    In my case, the below condition is not satisfying for an invoice which is still
    appearing in the output of report 'Accounts Payable Trial Balance(1)'

    'AND xte.source_id_int_1 = aia.invoice_id'

    ReplyDelete
  3. UnknownFebruary 10, 2016 at 7:35 PM

    not getting the invoices who do have void payment :( gotta work on it ::

    ReplyDelete
  4. AruMay 16, 2017 at 3:11 AM

    Hi,

    How to decide which definition_code should be passed into query.

    In our system they have many definition_code were defined. Please let me know ASAP.

    Thank You,
    Arun

    ReplyDelete
  5. UnknownOctober 4, 2017 at 6:50 AM

    Thank you brother! You saved my day.

    ReplyDelete
  6. UnknownJune 14, 2018 at 2:25 AM

    sir respect for you, i was doing a minor mistake and this query off yours fixed it

    ReplyDelete
  7. Kasun DasanayakeJuly 9, 2018 at 12:43 AM

    It is working. Thanks

    ReplyDelete

Subscribe to: Post Comments (Atom)