AP Trial Balance sql query for r12

AP Trial Balance sql query for r12

Hi Friends, We are going to discuss about AP Trial Balance sql query in this post. We will share the complete query which helps to prepare the AP trial balance in r12. Using this SQL query we will be able to extract the complete AP trial balance from r12. AP trial balance report is very critical for finance prospective which helps to show the Supplier Outstanding amount as of date. We do refer the AP trial balance report for AP and GL reconciliation in r12. Using this below shared sql query we can develop the custom AP trial balance report in r12. This is one of the important sql query for AP and GL reconciliation in r12. We will also share some of the important tables too which helps to prepare the AP trial balance sql query. Please find below the complete AP Trial Balance sql query for r12.

AP Trial Balance sql query for r12

2 Important Tables for AP trial Balance Query

1. xla_trial_balances

2. xla_transaction_entities

Detail AP Trial Balance sql query for r12

Here below is the developed sql query which helps to extract the complete AP trial balance in r12 system.

SELECT  aps.vendor_name "Supplier Name",
aps.segment1 "Supplier Number",
aia.invoice_num,
aia.invoice_type_lookup_code "Invoice Type",
aia.description,
aia.invoice_currency_code,
aia.invoice_amount,
XLA_TRIAL.BALANCE "Balance Amount",
gcc.segment1|| '-'|| gcc.segment2|| '-'|| gcc.segment3|| '-'|| gcc.segment4|| '-'|| gcc.segment5 "GL ACCOUNT",
aia.invoice_date,
ap.NAME "Invoice term"
  FROM ap_invoices_all aia,
       ap_suppliers aps,
       gl_code_combinations gcc,
    xla.xla_transaction_entities xte,
       (SELECT   XLA_TRIAL.code_combination_id,
                 NVL (XLA_TRIAL.applied_to_entity_id, XLA_TRIAL.source_entity_id) entity_id,
                 SUM (NVL (XLA_TRIAL.acctd_rounded_cr, 0)),
                 SUM (NVL (XLA_TRIAL.acctd_rounded_dr, 0)),
                   SUM (NVL (XLA_TRIAL.acctd_rounded_cr, 0))
                 - SUM (NVL (XLA_TRIAL.acctd_rounded_dr, 0)) BALANCE,
                 party_id
            FROM xla_trial_balances XLA_TRIAL
           WHERE 1=1
           and trunc(XLA_TRIAL.gl_date) <=TO_DATE ('31-Mar-2019')
           and XLA_TRIAL.definition_code ='AP_200_1001'
                       GROUP BY XLA_TRIAL.code_combination_id,
                 NVL (XLA_TRIAL.applied_to_entity_id, XLA_TRIAL.source_entity_id),
                 XLA_TRIAL.party_id
          HAVING SUM (NVL (XLA_TRIAL.acctd_rounded_cr, 0)) <>
                                            SUM (NVL (XLA_TRIAL.acctd_rounded_dr, 0))) XLA_TRIAL,
       ap_terms ap
 WHERE XLA_TRIAL.entity_id = xte.entity_id
   AND XLA_TRIAL.code_combination_id = gcc.code_combination_id
   AND ap.term_id = aia.terms_id;
   AND xte.application_id=200
   AND xte.source_id_int_1 = aia.invoice_id
   AND aia.vendor_id = aps.vendor_id

AP Trial Balance sql query for r12