Description:
Account payable trial balance is used to verify payable liabilities that are mentioned in the general ledger. This helps the businesses while reconciling their bank statements. Trial balance includes subtotal of unpaid and partially paid invoices based on its journal entries are made.
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
Summary:
This Post explained what the steps should follow to create AP Invoice liability (Trial Balance) Query: R12 AP GL PO RCV XLA.
queries?
Do drop a note by writing us at doyen.ebiz@gmail.comor use the comment section below to ask your questions.
