Vendor Transaction Report

1.Overview

This document talks about getting the vendor transaction detailed report in oracle apps r12

2.Technologies and Tools Used

The following technologies have been used to achieve the expected output.

• SQL Code

3.Use Case

This report is to track and analyses vendor credit, debit and adjustment details of the finance team.

This document gives you the SQL query to fetch the details.

4.Architecture

Step 1: compile xxa_pkgutil  packages from below url

https://doyensys.com/blogs/sales-margin-by-item/?preview=true

Step 2:

SQL Report Code:

SELECT DISTINCT
aia.invoice_id,
aia.invoice_num document_no,
aia.invoice_date document_date,
aps.segment1 vendor_number,
aia.invoice_type_lookup_code “Transaction Type”,
aps.vendor_name,
apss.vendor_site_code vendor_site,
apss.pay_group_lookup_code pay_group,
aipa.invoice_payment_type,
aia.invoice_currency_code doc_currency,
nvl(aia.exchange_rate, 1) fx_rate,
(
SELECT
LISTAGG(segment1, ‘,’) WITHIN GROUP(
ORDER BY
segment1 ASC
)
FROM
(
SELECT DISTINCT
aila1.invoice_id,
pha.segment1
|| ‘-‘
|| pra.release_num segment1
FROM
apps.po_headers_all pha,
apps.ap_invoice_lines_all aila1,
po_line_locations_all plla,
po_lines_all pla,
po_releases_all pra,
ap_invoices_all aia1,
ap_invoice_distributions_all aida1
WHERE
aila1.po_header_id = pha.po_header_id
AND plla.po_header_id = pla.po_header_id
AND plla.po_line_id = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND aia1.invoice_id = aila1.invoice_id
AND pra.po_release_id (+) = plla.po_release_id
AND pra.po_release_id = aila1.po_release_id
AND aida1.invoice_id = aila1.invoice_id
AND aida1.invoice_line_number = aila1.line_number
UNION
SELECT DISTINCT
aila1.invoice_id,
pha.segment1
FROM
apps.po_headers_all pha,
apps.ap_invoice_lines_all aila1,
ap_invoices_all aia1
WHERE
1 = 1
AND aia1.invoice_id = aila1.invoice_id
AND aila1.po_header_id = pha.po_header_id
AND aila1.po_release_id IS NULL
) a
WHERE
a.invoice_id = aia.invoice_id
GROUP BY
a.invoice_id
) po_number,
apsa.due_date,
decode(aia.payment_status_flag, ‘N’, ‘UN-PAID’, ‘P’, ‘Partial Paid’,
‘Y’, ‘PAID’) payment_status_flag,
aia.gl_date,
aia.terms_date,
apt.name payment_term,
(
SELECT
LISTAGG(check_date, ‘,’) WITHIN GROUP(
ORDER BY
check_date ASC
)
FROM
(
SELECT DISTINCT
aipa1.invoice_id,
to_char(aca1.check_date, ‘DD-MON-YYYY’) check_date
FROM
apps.ap_invoice_payments_all aipa1,
apps.ap_checks_all aca1
WHERE
1 = 1
AND aca1.check_id = aipa1.check_id
) a
WHERE
a.invoice_id = aia.invoice_id
GROUP BY
a.invoice_id
) payment_date,
(
SELECT
LISTAGG(check_number, ‘,’) WITHIN GROUP(
ORDER BY
check_number ASC
)
FROM
(
SELECT DISTINCT
aipa1.invoice_id,
aca1.check_number
FROM
apps.ap_invoice_payments_all aipa1,
apps.ap_checks_all aca1
WHERE
1 = 1
AND aca1.check_id = aipa1.check_id
) a
WHERE
a.invoice_id = aia.invoice_id
GROUP BY
a.invoice_id
) payment_no,
(
SELECT
iby.payment_method_name
FROM
iby_payment_methods_vl iby
WHERE
iby.payment_method_code = aia.payment_method_code
) payment_method,
CASE
WHEN aia.invoice_type_lookup_code = ‘PREPAYMENT’ THEN
– 1 * aia.invoice_amount
ELSE
aia.invoice_amount
END transaction_amt_doc_cury,
CASE
WHEN aia.invoice_type_lookup_code = ‘PREPAYMENT’ THEN
– 1 * nvl(aia.amount_paid, 0)
ELSE
nvl(SUM(aipa.amount), 0)
END payment_amount,
CASE
WHEN aia.invoice_type_lookup_code = ‘PREPAYMENT’ THEN
– 1 * nvl(xxa_pkgutil.fnprepaymentapplied(aia.invoice_id), 0)
ELSE
xxa_pkgutil.fnamountapplied(aia.invoice_id)
END amount_applied,
CASE
WHEN aia.invoice_type_lookup_code = ‘PREPAYMENT’ THEN
– 1 * nvl(xxa_pkgutil.fnprepaymentapplied(aia.invoice_id), 0)
ELSE
( aia.invoice_amount – apsa.amount_remaining )
END total_amt_applied,
CASE
WHEN aia.invoice_type_lookup_code = ‘PREPAYMENT’ THEN
– 1 * ( coalesce(aia.amount_paid, aia.invoice_amount, 0) + nvl(xxa_pkgutil.fnprepaymentapplied(aia.invoice_id), 0) )
ELSE
apsa.amount_remaining
END balance_doc,
CASE
WHEN aia.invoice_type_lookup_code = ‘PREPAYMENT’ THEN
– 1 * round(nvl(aia.invoice_amount, 0) * nvl(aia.exchange_rate, 1), 2)
ELSE
round(aia.invoice_amount * nvl(aia.exchange_rate, 1), 2)
END transaction_amt_loc_cury,
CASE
WHEN aia.invoice_type_lookup_code = ‘PREPAYMENT’ THEN
– 1 * round(nvl(xxa_pkgutil.fnprepaymentapplied(aia.invoice_id), 0) * nvl(aia.exchange_rate, 1), 2)
ELSE
round((aia.invoice_amount – apsa.amount_remaining) * nvl(aia.exchange_rate, 1), 2)
END amount_applied_local,
CASE
WHEN aia.invoice_type_lookup_code = ‘PREPAYMENT’ THEN
round((- 1 *(coalesce(aia.amount_paid, aia.invoice_amount, 0) + nvl(xxa_pkgutil.fnprepaymentapplied(aia.invoice_id), 0))) *
nvl(aia.exchange_rate, 1), 2)
ELSE
round(apsa.amount_remaining * nvl(aia.exchange_rate, 1), 2)
END balance_local,
gl.concatenated_segments “GL Code(Liability side)”,
(
SELECT
LISTAGG(r.receipt_num, ‘,’) WITHIN GROUP(
ORDER BY
r.receipt_num ASC
)
FROM
(
SELECT DISTINCT
d.invoice_id,
rsh.receipt_num
FROM
ap_invoice_distributions_all d,
rcv_transactions rt,
rcv_shipment_headers rsh
WHERE
1 = 1
AND rt.transaction_id = d.rcv_transaction_id
AND rt.shipment_header_id = rsh.shipment_header_id
) r
WHERE
r.invoice_id = aia.invoice_id
GROUP BY
r.invoice_id
) receipt_num,
(
SELECT
LISTAGG(r.shipment_num, ‘,’) WITHIN GROUP(
ORDER BY
r.shipment_num ASC
)
FROM
(
SELECT DISTINCT
d.invoice_id,
rsh.shipment_num
FROM
ap_invoice_distributions_all d,
rcv_transactions rt,
rcv_shipment_headers rsh
WHERE
1 = 1
AND rt.transaction_id = d.rcv_transaction_id
AND rt.shipment_header_id = rsh.shipment_header_id
) r
WHERE
r.invoice_id = aia.invoice_id
GROUP BY
r.invoice_id
) shipment_num,
(
SELECT
LISTAGG(r.invoice_num, ‘,’) WITHIN GROUP(
ORDER BY
r.invoice_num ASC
)
FROM
(
SELECT DISTINCT
ida2.invoice_id,
aia1.invoice_num
FROM
ap_invoice_distributions_all ida1,
apps.ap_invoices_all aia1,
ap_invoice_distributions_all ida2
WHERE
1 = 1
AND ( aia1.invoice_id = ida1.invoice_id )
— AND aia.invoice_id = ida2.invoice_id
AND ida2.line_type_lookup_code = ‘PREPAY’
AND ida1.invoice_distribution_id = ida2.prepay_distribution_id
) r
WHERE
aia.invoice_id = r.invoice_id
GROUP BY
r.invoice_id
) prepayment_invoice,
(
SELECT
LISTAGG(r.gl_date, ‘,’) WITHIN GROUP(
ORDER BY
r.gl_date ASC
)
FROM
(
SELECT DISTINCT
ida2.invoice_id,
aia1.gl_date
FROM
ap_invoice_distributions_all ida1,
apps.ap_invoices_all aia1,
ap_invoice_distributions_all ida2
WHERE
1 = 1
AND ( aia1.invoice_id = ida1.invoice_id )
— AND aia.invoice_id = ida2.invoice_id
AND ida2.line_type_lookup_code = ‘PREPAY’
AND ida1.invoice_distribution_id = ida2.prepay_distribution_id
) r
WHERE
aia.invoice_id = r.invoice_id
GROUP BY
r.invoice_id
) prepayment_date,
decode(ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code),
‘FULL’, ‘Fully Applied’, ‘UNAPPROVED’, ‘Unvalidated’,
‘NEEDS REAPPROVAL’, ‘Needs Revalidation’, ‘APPROVED’, ‘Validated’, ‘NEVER APPROVED’,
‘Never Validated’, ‘CANCELLED’, ‘Cancelled’, ‘UNPAID’, ‘Unpaid’,
‘AVAILABLE’, ‘Available’) status,
aia.wfapproval_status approval,
ap_invoices_pkg.get_holds_count(aia.invoice_id) holds_count,
ap_invoices_pkg.get_sched_holds_count(aia.invoice_id) sched_holds_count,
decode(apps.ap_invoices_pkg.get_posting_status(aia.invoice_id), ‘P’, ‘Partial’, ‘N’, ‘No’,
‘S’, ‘Selected’, ‘Y’, ‘Yes’, ‘D’,
‘Draft Accounted’, apps.ap_invoices_pkg.get_posting_status(aia.invoice_id)) accounted,
xxa_pkgutil.fn_gl_batch(aia.invoice_id) “GL Batch”,
nvl(xxa_pkgutil.fn_gl_transfer_status(aia.invoice_id), ‘No’) “GL Transfer”,
— nvl(d.batch_name, ‘–‘) “GL Batch”
xxa_pkgutil.fn_gl_transfer(aia.invoice_id) “GL Status” –, aida.DESCRIPTION Tax_DESCRIPTION
FROM
apps.ap_invoices_all aia,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all apss,
apps.ap_invoice_payments_all aipa,
apps.ap_checks_all aca,
apps.ap_payment_schedules_all apsa,
apps.ap_terms apt,
apps.hr_operating_units hou,
apps.ap_batches_all d,
org_organization_definitions oo,
gl_code_combinations_kfv gl,
ap_invoice_payments aip,
(
SELECT
*
FROM
ap_invoice_distributions_all
WHERE
line_type_lookup_code = ‘ITEM’
) aida
WHERE
1 = 1
AND aia.vendor_id = aps.vendor_id
AND aia.vendor_site_id = apss.vendor_site_id
AND aps.vendor_id = apss.vendor_id
AND aipa.invoice_id (+) = aia.invoice_id
AND aca.check_id (+) = aipa.check_id
AND apsa.invoice_id = aia.invoice_id
AND apt.term_id = aia.terms_id
AND hou.organization_id = oo.operating_unit
AND aia.batch_id = d.batch_id (+)
AND apss.org_id = oo.operating_unit
AND gl.code_combination_id = aia.accts_pay_code_combination_id
AND aia.invoice_id = aip.invoice_id (+)
AND ( aia.invoice_id = aida.invoice_id (+) )
AND oo.organization_id = :p61_org– org_id
AND ( upper(aia.invoice_type_lookup_code) = upper(:p61_transaction_type)
OR :p61_transaction_type IS NULL )
AND ( aia.vendor_id IN (
SELECT DISTINCT
column_value
FROM
TABLE ( xxa_pkgutil.sflist(:p61_vendor, ‘:’) )
)
OR :p61_vendor IS NULL )
AND ( trunc(aia.gl_date) >= to_date(:p61_from_date, ‘DD-MON-YYYY’)
OR :p61_from_date IS NULL )
AND ( trunc(aia.gl_date) <= to_date(:p61_to_date, ‘DD-MON-YYYY’)
OR :p61_to_date IS NULL )
AND ( decode(aia.payment_status_flag, ‘P’, ‘N’, aia.payment_status_flag) = :p61_sw_f_p_trx
OR :p61_sw_f_p_trx IS NULL )
AND ( ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.invoice_type_lookup_code) =
decode(:p61_sw_f_p_trx, ‘N’, decode(ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag,
aia.invoice_type_lookup_code), ‘CANCELLED’, ‘-1’, ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag,
aia.invoice_type_lookup_code)), ap_invoices_pkg.get_approval_status(aia.invoice_id, aia.invoice_amount, aia.payment_status_flag, aia.
invoice_type_lookup_code))
OR :p61_sw_f_p_trx IS NULL )
GROUP BY
aia.invoice_id,
aia.invoice_num,
aia.invoice_date,
aps.segment1,
apss.vendor_site_code,
aia.invoice_type_lookup_code,
aps.vendor_name,
aipa.invoice_payment_type,
aia.invoice_currency_code,
aia.exchange_rate,
aia.invoice_id,
aia.org_id,
apsa.due_date,
aia.payment_status_flag,
aia.gl_date,
aia.terms_date,
apt.name,
aca.check_date,
aia.invoice_amount,
aia.amount_paid,
apsa.amount_remaining,
d.batch_name,
gl.concatenated_segments,
aida.invoice_distribution_id,
apss.pay_group_lookup_code,
aia.wfapproval_status,
aida.description,
aia.payment_method_code;

5.Screen shot

Output:

This will be the output that we can generate using this document.