AP Trail Balances SQL Query for R12

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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 ('31-Dec-2017')
                       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;