Introduction
This Post illustrates steps required to Vendor Ledger Report Query in Oracle EBS R12.
Script to Vendor Ledger Report Query
SELECT flag, vendor_num, vendor_name, vendor_site_id, vendor_site_code,
state_code, project_code, transaction_type, transaction_num,
doc_category_name, document_num, po_number, transaction_date,
gl_date, status, description, currency, SUM (entered_dr) entered_dr,
SUM (entered_cr) entered_cr, SUM (accounted_dr) accounted_dr,
SUM (accounted_cr) accounted_cr
FROM (SELECT 1 flag, ap.invoice_id invoice_id, aps.segment1 vendor_num,
aps.vendor_name vendor_name,
ap.vendor_site_id vendor_site_id,
apss.vendor_site_code vendor_site_code,
apss.state state_code, gl.segment2 project_code,
ap.invoice_type_lookup_code transaction_type,
ap.invoice_num transaction_num, fdsc.NAME doc_category_name,
ap.voucher_num document_num,
(SELECT poh.segment1
FROM po_headers_all poh,
po_distributions_all pod,
ap_invoice_distributions_all apd
WHERE poh.po_header_id = pod.po_header_id
AND pod.po_distribution_id = apd.po_distribution_id
AND apd.invoice_id = ap.invoice_id
AND ROWNUM = 1) po_number,
ap.invoice_date transaction_date, ap.gl_date gl_date,
DECODE
(ap_invoices_pkg.get_posting_status (ap.invoice_id),
‘Y’, ‘Accounted’,
‘P’, ‘Partial’,
‘Unaccounted’
) status,
ap.description description,
ap.invoice_currency_code currency,
DECODE (xl.accounting_class_code,
‘LIABILITY’, NVL (xl.entered_dr, 0),
‘PREPAID_EXPENSE’, -1 * xl.entered_cr,
0
) entered_dr,
DECODE (xl.accounting_class_code,
‘LIABILITY’, NVL (xl.entered_cr, 0),
0
) entered_cr,
DECODE (xl.accounting_class_code,
‘LIABILITY’, NVL (xl.accounted_dr, 0),
‘PREPAID_EXPENSE’, -1 * xl.accounted_cr,
0
) accounted_dr,
DECODE (xl.accounting_class_code,
‘LIABILITY’, NVL (xl.accounted_cr, 0),
0
) accounted_cr
FROM ap_invoices_all ap,
ap_suppliers aps,
ap_supplier_sites_all apss,
gl_code_combinations gl,
fnd_doc_sequence_categories fdsc,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_lines xl,
xla_ae_headers xah
WHERE ap_invoices_pkg.get_posting_status (ap.invoice_id) IN
(‘Y’, ‘P’)
AND ap.invoice_type_lookup_code <> ‘PREPAYMENT’
AND gl.code_combination_id = ap.accts_pay_code_combination_id
AND fdsc.code(+) = ap.doc_category_code
AND fdsc.table_name(+) = ‘AP_INVOICES_ALL’
AND aps.vendor_id = ap.vendor_id
AND ap.vendor_site_id = apss.vendor_site_id
AND TRUNC (xah.accounting_date) BETWEEN :cp_gl_from_date
AND :cp_gl_to_date
AND ap.org_id = :p_org_id
AND ap.vendor_id = :p_vendor_id
AND source_id_int_1 = ap.invoice_id
AND xte.source_id_int_1 = ap.invoice_id
AND xte.entity_code = ‘AP_INVOICES’
AND xe.entity_id = xte.entity_id
AND xe.event_id = xah.event_id
AND xah.gl_transfer_status_code = ‘Y’
AND xl.ae_header_id = xah.ae_header_id
AND xl.accounting_class_code IN (‘LIABILITY’, ‘PREPAID_EXPENSE’))
GROUP BY flag,
invoice_id,
vendor_num,
vendor_name,
vendor_site_id,
vendor_site_code,
state_code,
project_code,
transaction_type,
transaction_num,
doc_category_name,
document_num,
po_number,
transaction_date,
gl_date,
status,
description,
currency
UNION ALL
SELECT flag, vendor_num, vendor_name, vendor_site_id, vendor_site_code,
state_code, project_code, transaction_type, transaction_num,
doc_category_name, document_num, po_number, transaction_date,
gl_date, status, description, currency, SUM (entered_dr) entered_dr,
SUM (entered_cr) entered_cr, SUM (accounted_dr) accounted_dr,
SUM (accounted_cr) accounted_cr
FROM (SELECT 2 flag, apc.check_id check_id, aps.segment1 vendor_num,
aps.vendor_name vendor_name,
— null pan,
apc.vendor_site_id vendor_site_id,
apss.vendor_site_code vendor_site_code,
–NULL gstin,
apss.state state_code, NULL project_code,
‘PAYMENT’ transaction_type,
TO_CHAR (apc.check_number) transaction_num,
fdsc.NAME doc_category_name,
TO_CHAR (apc.check_voucher_num) document_num, NULL po_number,
apc.check_date transaction_date, xah.accounting_date gl_date,
DECODE (xah.gl_transfer_status_code,
‘Y’, ‘Accounted’,
‘Unaccounted’
) status,
apc.description description, apc.currency_code currency,
NVL (xl.entered_dr, 0) entered_dr,
NVL (xl.entered_cr, 0) entered_cr,
NVL (xl.accounted_dr, 0) accounted_dr,
NVL (xl.accounted_cr, 0) accounted_cr
FROM ap_checks_all apc,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_lines xl,
xla_ae_headers xah,
ap_suppliers aps,
ap_supplier_sites_all apss,
fnd_doc_sequence_categories fdsc
WHERE xah.gl_transfer_status_code = ‘Y’
AND fdsc.code(+) = apc.doc_category_code
AND fdsc.table_name(+) = ‘AP_CHECKS_ALL’
AND xte.source_id_int_1 = apc.check_id
AND xte.entity_code = ‘AP_PAYMENTS’
AND xe.entity_id = xte.entity_id
AND xe.event_id = xah.event_id
AND xl.ae_header_id = xah.ae_header_id
AND xl.accounting_class_code = ‘LIABILITY’
AND aps.vendor_id(+) = apc.vendor_id
AND apc.vendor_site_id = apss.vendor_site_id(+)
AND TRUNC (xah.accounting_date) BETWEEN :cp_gl_from_date
AND :cp_gl_to_date
AND apc.org_id = :p_org_id
AND apc.vendor_id = :p_vendor_id)
GROUP BY flag,
check_id,
vendor_num,
vendor_name,
vendor_site_id,
vendor_site_code,
state_code,
project_code,
transaction_type,
transaction_num,
doc_category_name,
document_num,
po_number,
transaction_date,
gl_date,
status,
description,
currency
ORDER BY vendor_num, vendor_site_code, gl_date, transaction_date;
What we expect in the script.
This script helps us to Vendor Ledger Report Query. Couple of tables which is being used in the scripts are ap_checks_all ,xla.xla_transaction_entities,xla_events ,xla_ae_lines xl,xla_ae_headers xah,ap_suppliers,ap_supplier_sites_all apss etc.
Summary
This Post described the script Vendor Ledger Report Query in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
