ORACLE MASTERMINDS

Tuesday 3 January 2017

Accounts Payable Trial Balance Report

Description

Account Payable Trial Balance Report meant to verify that total accounts payable liabilities in Payables equal those in the general ledger. Before closing a period, you can compare the cumulative total liability provided by this report with the total liability provided by your general ledger to reconcile these balances.

The Trial Balance Report lists and subtotals by supplier all unpaid and partially paid invoices for which Payables created journal entries. Payables list and subtotal the invoices by the Accounts Payable Liability account.

SELECT pv.vendor_name supplier_name, ai.invoice_num invoice_number,

ai.invoice_date invoice_date, ai.invoice_currency_code invoice_curr,

atb.invoice_amount invoice_amount,

atb.remaining_amount remaining_amount,

atb.code_combination_id code_combination_id,

atb.vendor_id third_party_id,

fnd_flex_ext.get_segs ('SQLGL', 'GL#',:p_chart_of_accounts_id,

atb.code_combination_id

) concat_segments,

ai.description invoice_description, atb.invoice_id source_invoice_id,

atb.org_id, atb.set_of_books_id, pvs.attribute1

FROM ap_trial_bal_gt atb,

po_vendors pv,

ap_system_parameters_all asp,

ap_invoices_all ai,

po_vendor_sites_all pvs

WHERE NVL (atb.org_id, -99) = NVL (ai.org_id, -99)

AND NVL (atb.org_id, -99) = NVL (asp.org_id, -99)

AND atb.vendor_id = pv.vendor_id

AND atb.invoice_id = ai.invoice_id

AND atb.set_of_books_id = :p_set_of_books_id

AND atb.request_id = :p_conc_request_id

AND pv.vendor_id = pvs.vendor_id

AND ai.vendor_site_id = pvs.vendor_site_id

AND ( NVL (pvs.attribute1, 'X') = NVL (:p_ap_owner, 'X')

OR pvs.attribute1 = NVL (:p_ap_owner, pvs.attribute1)

)

SELECT fnd_flex_ext.get_segs ('SQLGL', 'GL#', p_chart_of_accounts_id,

atb.code_combination_id )s_concat_segments,

SUM (atb.invoice_amount) s_invoice_amount,

SUM (atb.remaining_amount) s_remaining_amount

FROM ap_trial_bal_gt atb, ap_system_parameters_all asp,

ap_invoices_all ai