Introduction
Business requested to provide the Payment reversal information
Cause of the issue:
Business want to cross check with Payment reversals
How do we solve:
We have created below query to retrieve data
Query:
SELECT
hou.name,
aps.vendor_name,
aps.segment1 vendor_number,
apssa.vendor_site_code,
aia.invoice_num,
aia.invoice_date,
aia.invoice_amount,
aia.source invoice_source,
aia.invoice_type_lookup_code,
aia.description,
aia.pay_group_lookup_code,
aia.invoice_currency_code,
aia.payment_currency_code,
aca.check_number,
aca.check_date,
aipa.amount payment_amount,
aipa.accounting_date payment_accounting_date,
aca.payment_method_code,
aca.checkrun_name,
aca.status_lookup_code
FROM
ap_invoices_all aia,
poz_suppliers_v aps,
poz_supplier_sites_v apssa,
ap_invoice_payments_all aipa,
ap_checks_all aca,
hr_operating_units hou
WHERE
aia.vendor_id = aps.vendor_id
AND aia.vendor_site_id = apssa.vendor_site_id
AND aia.org_id = apssa.prc_bu_id
AND aia.cancelled_by IS NULL
AND aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND nvl(aipa.reversal_flag, ‘N’) = ‘Y’
AND trunc(aca.check_date) BETWEEN ‘2018-01-01’ AND ‘2022-12-31’
AND aia.org_id IN ( ‘xxxxx’ )
AND hou.organization_id = aia.org_id
ORDER BY
1,
3,
5
