Customer Refund
This query will help you to get Customer Refund, initiated in AR module. Once refund is initiated, an AP Invoice created in payable module.
Detail Query
SELECT aia.org_id bu_id,
hou.name bu_name,
aia.invoice_id document_id,
aia.invoice_num document_number,
aia.doc_sequence_value voucher,
aia.invoice_date document_date,
(SELECT flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'IBY_DOCUMENT_TYPES'
AND flv.language = 'US'
AND flv.lookup_code = aia.invoice_type_lookup_code) document_type,
aia.description document_desc,
aia.invoice_currency_code currency,
nvl(aia.exchange_rate, 1) exchange_rate,
(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')
) document_status,
nvl(nvl(CASE WHEN aia.cancelled_date IS NOT NULL THEN aia.cancelled_amount ELSE aia.invoice_amount END, 0)
+ nvl((SELECT SUM(NVL(aila.amount, 0))
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'AWT'
AND aila.line_source = 'MANUAL WITHHOLDING'
AND aila.org_id = aia.org_id
AND NVL (aila.discarded_flag, 'N') = 'N'
AND NVL (aila.cancelled_flag, 'N') = 'N'
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date
)
,0)
,0) entered_amount,
nvl(decode(aia.invoice_type_lookup_code,
'DEBIT', ((ABS(nvl(CASE WHEN aia.cancelled_date IS NOT NULL THEN (SELECT SUM(nvl(amount,0)) FROM ap_invoice_distributions_all WHERE invoice_id = aia.invoice_id
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date AND posted_flag = 'Y') ELSE aia.invoice_amount END, 0) * nvl (aia.exchange_rate, 1))
+ nvl((SELECT SUM(nvl(aila.amount, 0) * nvl (aia.exchange_rate, 1))
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'AWT'
AND aila.line_source = 'MANUAL WITHHOLDING'
AND aila.org_id = aia.org_id
AND nvl (aila.discarded_flag, 'N') = 'N'
AND nvl (aila.cancelled_flag, 'N') = 'N'
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date
),0))),
'CREDIT', ((ABS (nvl(CASE WHEN aia.cancelled_date IS NOT NULL THEN (SELECT SUM(nvl(amount,0)) FROM ap_invoice_distributions_all WHERE invoice_id = aia.invoice_id
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date AND posted_flag = 'Y') ELSE aia.invoice_amount END, 0) * nvl(aia.exchange_rate, 1))
+ nvl((SELECT SUM (nvl(aila.amount, 0) * nvl(aia.exchange_rate, 1))
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'AWT'
AND aila.line_source = 'MANUAL WITHHOLDING'
AND aila.org_id = aia.org_id
AND nvl(aila.discarded_flag, 'N') = 'N'
AND nvl (aila.cancelled_flag, 'N') = 'N'
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date
),0))
),
0)
,0) functional_dr,
nvl(decode(aia.invoice_type_lookup_code,
'PAYMENT REQUEST', ((ABS(nvl(CASE WHEN aia.cancelled_date IS NOT NULL THEN (SELECT SUM(nvl(amount,0)) FROM ap_invoice_distributions_all WHERE invoice_id = aia.invoice_id
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date AND posted_flag = 'Y') ELSE aia.invoice_amount END, 0) * nvl(aia.exchange_rate, 1))
+ nvl((SELECT SUM (nvl (aila.amount, 0) * nvl (aia.exchange_rate, 1))
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'AWT'
AND aila.line_source = 'MANUAL WITHHOLDING'
AND aila.org_id = aia.org_id
AND nvl (aila.discarded_flag, 'N') = 'N'
AND nvl (aila.cancelled_flag, 'N') = 'N'
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date
),0))),
0)
,0) functional_cr,
hp.party_id vendor_id,
hp.party_name vendor_name,
hp.party_number vendor_number,
hp.party_type vendor_type,
hps.party_site_id vendor_site_id,
hps.party_site_name vendor_site_code
FROM ap_invoices aia,
hr_operating_units hou,
hz_parties hp,
hz_party_sites hps,
(SELECT accounting_date, invoice_id
FROM ap_invoice_distributions
WHERE posted_flag = 'Y'
) dist
WHERE aia.org_id = hou.organization_id
AND aia.party_id = hp.party_id
AND aia.party_site_id = hps.party_site_id
AND hp.party_id = hps.party_id
AND aia.approval_status NOT IN('NEVER APPROVED')
AND aia.invoice_type_lookup_code IN ('PAYMENT REQUEST')
AND aia.invoice_id = dist.invoice_id(+)
--Parameters
AND aia.org_id = :p_bu_id
AND TRUNC(dist.accounting_date) BETWEEN :p_from_date and :p_to_date
AND hp.party_id = nvl(:p_supplier_number, hp.party_id)
hou.name bu_name,
aia.invoice_id document_id,
aia.invoice_num document_number,
aia.doc_sequence_value voucher,
aia.invoice_date document_date,
(SELECT flv.meaning
FROM fnd_lookup_values flv
WHERE flv.lookup_type = 'IBY_DOCUMENT_TYPES'
AND flv.language = 'US'
AND flv.lookup_code = aia.invoice_type_lookup_code) document_type,
aia.description document_desc,
aia.invoice_currency_code currency,
nvl(aia.exchange_rate, 1) exchange_rate,
(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')
) document_status,
nvl(nvl(CASE WHEN aia.cancelled_date IS NOT NULL THEN aia.cancelled_amount ELSE aia.invoice_amount END, 0)
+ nvl((SELECT SUM(NVL(aila.amount, 0))
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'AWT'
AND aila.line_source = 'MANUAL WITHHOLDING'
AND aila.org_id = aia.org_id
AND NVL (aila.discarded_flag, 'N') = 'N'
AND NVL (aila.cancelled_flag, 'N') = 'N'
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date
)
,0)
,0) entered_amount,
nvl(decode(aia.invoice_type_lookup_code,
'DEBIT', ((ABS(nvl(CASE WHEN aia.cancelled_date IS NOT NULL THEN (SELECT SUM(nvl(amount,0)) FROM ap_invoice_distributions_all WHERE invoice_id = aia.invoice_id
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date AND posted_flag = 'Y') ELSE aia.invoice_amount END, 0) * nvl (aia.exchange_rate, 1))
+ nvl((SELECT SUM(nvl(aila.amount, 0) * nvl (aia.exchange_rate, 1))
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'AWT'
AND aila.line_source = 'MANUAL WITHHOLDING'
AND aila.org_id = aia.org_id
AND nvl (aila.discarded_flag, 'N') = 'N'
AND nvl (aila.cancelled_flag, 'N') = 'N'
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date
),0))),
'CREDIT', ((ABS (nvl(CASE WHEN aia.cancelled_date IS NOT NULL THEN (SELECT SUM(nvl(amount,0)) FROM ap_invoice_distributions_all WHERE invoice_id = aia.invoice_id
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date AND posted_flag = 'Y') ELSE aia.invoice_amount END, 0) * nvl(aia.exchange_rate, 1))
+ nvl((SELECT SUM (nvl(aila.amount, 0) * nvl(aia.exchange_rate, 1))
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'AWT'
AND aila.line_source = 'MANUAL WITHHOLDING'
AND aila.org_id = aia.org_id
AND nvl(aila.discarded_flag, 'N') = 'N'
AND nvl (aila.cancelled_flag, 'N') = 'N'
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date
),0))
),
0)
,0) functional_dr,
nvl(decode(aia.invoice_type_lookup_code,
'PAYMENT REQUEST', ((ABS(nvl(CASE WHEN aia.cancelled_date IS NOT NULL THEN (SELECT SUM(nvl(amount,0)) FROM ap_invoice_distributions_all WHERE invoice_id = aia.invoice_id
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date AND posted_flag = 'Y') ELSE aia.invoice_amount END, 0) * nvl(aia.exchange_rate, 1))
+ nvl((SELECT SUM (nvl (aila.amount, 0) * nvl (aia.exchange_rate, 1))
FROM ap_invoice_lines_all aila
WHERE aila.invoice_id = aia.invoice_id
AND aila.line_type_lookup_code = 'AWT'
AND aila.line_source = 'MANUAL WITHHOLDING'
AND aila.org_id = aia.org_id
AND nvl (aila.discarded_flag, 'N') = 'N'
AND nvl (aila.cancelled_flag, 'N') = 'N'
AND TRUNC(accounting_date) BETWEEN :p_from_date and :p_to_date
),0))),
0)
,0) functional_cr,
hp.party_id vendor_id,
hp.party_name vendor_name,
hp.party_number vendor_number,
hp.party_type vendor_type,
hps.party_site_id vendor_site_id,
hps.party_site_name vendor_site_code
FROM ap_invoices aia,
hr_operating_units hou,
hz_parties hp,
hz_party_sites hps,
(SELECT accounting_date, invoice_id
FROM ap_invoice_distributions
WHERE posted_flag = 'Y'
) dist
WHERE aia.org_id = hou.organization_id
AND aia.party_id = hp.party_id
AND aia.party_site_id = hps.party_site_id
AND hp.party_id = hps.party_id
AND aia.approval_status NOT IN('NEVER APPROVED')
AND aia.invoice_type_lookup_code IN ('PAYMENT REQUEST')
AND aia.invoice_id = dist.invoice_id(+)
--Parameters
AND aia.org_id = :p_bu_id
AND TRUNC(dist.accounting_date) BETWEEN :p_from_date and :p_to_date
AND hp.party_id = nvl(:p_supplier_number, hp.party_id)