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 contact@staging.doyensys.com or use the comment section below to ask your questions.
