AP Trail Balances SQL Query for R12

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



-----------------------

AP Invoice liability (Trial Balance) Query: R12 AP GL PO RCV XLA

SELECT   NVL (f.currency_code, d.invoice_currency_code) currency_code,
         d.invoice_amount, d.wfapproval_status, d.invoice_num, d.invoice_id,
         i.segment1 v_code, i.vendor_name,
         NVL (d.invoice_amount, 0) - NVL (d.amount_paid, 0) amt,
         SUM (a.acctd_rounded_cr) - SUM (a.acctd_rounded_dr) amount,
         b.accounting_date, f.segment1 po_num, f.comments descr,
         h.receipt_num rct_num, c.segment6 loc, c.segment2 cost_centre,
         d.doc_sequence_value ap_voucher, j.employee_number, j.full_name
    FROM xla_transaction_entities xte,
         ap.ap_invoice_distributions_all b,
         ap.ap_invoice_lines_all k,
         gl.gl_code_combinations c,
         ap.ap_invoices_all d,
         po.po_distributions_all e,
         po.po_headers_all f,
         apps.rcv_transactions g,
         apps.rcv_shipment_headers h,
         apps.po_vendors i,
         apps.per_all_people_f j,
         (SELECT   /*+ index(tb XLA_TRIAL_BALANCES_N2) */
                   tb.code_combination_id, tb.ledger_id, tb.party_id party_id,
                   tb.gl_date, tb.ae_header_id,
                   NVL (tb.applied_to_entity_id,
                        tb.source_entity_id
                       ) entity_id,
                   tb.definition_code, tb.record_type_code,
                   SUM (NVL (tb.acctd_rounded_cr, 0)) acctd_rounded_cr,
                   SUM (NVL (tb.acctd_rounded_dr, 0)) acctd_rounded_dr,
                     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 = 'AP_200_1001'
               AND tb.gl_date <= :acc_date --GL Date Value               
               AND tb.code_combination_id IN (SELECT code_combination_id
                                              FROM gl_code_combinations a
                                              WHERE a.segment3 = :acc_code)--GL CC Value
          GROUP BY tb.code_combination_id,
                   tb.ledger_id,
                   tb.party_id,
                   tb.party_id,
                   NVL(tb.applied_to_entity_id,tb.source_entity_id),
                   tb.record_type_code,
                   tb.party_id,
                   tb.gl_date,
                   tb.definition_code,
                   tb.ae_header_id) a
   WHERE a.code_combination_id = c.code_combination_id
     AND d.INVOICE_NUM = :invoice_num   --Invoice Num    
     AND a.definition_code = 'AP_200_1001'
     AND a.entity_id = xte.entity_id
     AND xte.application_id = 200
     AND xte.ledger_id = a.ledger_id
     AND NVL (source_id_int_1, (-99)) = d.invoice_id
     AND d.invoice_id = k.invoice_id
     AND k.line_number = b.invoice_line_number
     AND c.segment3 = :acc_code               --GL CC Code     
     AND a.gl_date <= :acc_date               --Gl Date 
     AND k.invoice_id = b.invoice_id
     AND b.distribution_line_number = '1'
     AND k.line_number = '1'
     AND b.po_distribution_id = e.po_distribution_id(+)
     AND e.po_header_id = f.po_header_id(+)
     AND b.rcv_transaction_id = g.transaction_id(+)
     AND g.shipment_header_id = h.shipment_header_id(+)
     AND f.agent_id = j.person_id(+)
     AND i.vendor_id = d.vendor_id
     AND d.set_of_books_id = a.ledger_id
     AND d.org_id = :l_org_id           --Invoice Org Id   
     AND TRUNC (SYSDATE) BETWEEN j.effective_start_date(+) 
     AND j.effective_end_date(+)
  HAVING SUM (a.acctd_rounded_cr) - SUM (a.acctd_rounded_dr) <> 0
  GROUP BY f.currency_code,
         d.invoice_amount,
         b.project_id,
         d.wfapproval_status,
         d.invoice_num,
         d.invoice_id,
         b.accounting_date,
         f.segment1,
         h.receipt_num,
         i.segment1,
         i.vendor_name,
         c.segment6,
         d.doc_sequence_value,
         c.segment2,
         j.employee_number,
         j.full_name,
         f.agent_id,
         f.comments,
         d.invoice_currency_code,
         b.posted_flag,
         d.amount_paid,
         d.invoice_id