Customer Refund Application
This query will provide AP payment details against Customer Refund in Oracle Fusion. Receivable department initiate a refund and an invoice is create in Payable. Invoice type will be PAYMENT_REQUEST for such type of transactions.
Detail Query
SELECT aia.org_id bu_id,
hou.name bu_name,
chk.check_id document_id,
TO_CHAR(chk.check_number) document_number,
chk.doc_sequence_value voucher,
chk.check_date document_date,
'Payment' document_type,
chk.description document_desc,
aia.invoice_currency_code currency,
nvl(aia.exchange_rate, 1) exchange_rate,
chk.status_lookup_code document_status,
nvl(nvl(aia.invoice_amount, 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,
CASE WHEN (nvl(nvl(aia.invoice_amount, 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)) >= 0
THEN CASE WHEN chk.status_lookup_code = 'VOIDED'
THEN (nvl((nvl((aipa.amount + NVL(aipa.discount_taken,0)) * nvl (aia.exchange_rate, 1), 0)), 0))
ELSE (nvl((nvl(ABS((aipa.amount + NVL(aipa.discount_taken,0)) * nvl (aia.exchange_rate, 1)), 0)), 0)) END
ELSE 0 END functional_dr,
CASE WHEN (nvl(nvl(aia.invoice_amount, 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)) < 0
THEN (nvl((CASE WHEN chk.status_lookup_code = 'VOIDED'
THEN (nvl((nvl((aipa.amount + NVL(aipa.discount_taken,0)) * nvl (aia.exchange_rate, 1), 0)), 0))
ELSE (nvl((nvl(ABS((aipa.amount + NVL(aipa.discount_taken,0)) * nvl (aia.exchange_rate, 1)), 0)), 0)) END
+ 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))
ELSE 0 END 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,
aia.invoice_num applied_doc,
(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
) applied_doc_type
FROM ap_invoices aia,
ap_invoice_payments aipa,
ap_checks chk,
hr_operating_units hou,
hz_parties hp,
hz_party_sites hps
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = chk.check_id
AND 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.invoice_type_lookup_code = 'PAYMENT REQUEST'
AND aia.approval_status NOT IN ('NEVER APPROVED')
AND aipa.posted_flag = 'Y'
--Parameters
AND aia.org_id = :p_bu_id
AND hp.party_id = nvl(:p_supplier_number, hp.party_id)
AND TRUNC(aipa.accounting_date) BETWEEN :p_from_date and :p_to_date
hou.name bu_name,
chk.check_id document_id,
TO_CHAR(chk.check_number) document_number,
chk.doc_sequence_value voucher,
chk.check_date document_date,
'Payment' document_type,
chk.description document_desc,
aia.invoice_currency_code currency,
nvl(aia.exchange_rate, 1) exchange_rate,
chk.status_lookup_code document_status,
nvl(nvl(aia.invoice_amount, 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,
CASE WHEN (nvl(nvl(aia.invoice_amount, 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)) >= 0
THEN CASE WHEN chk.status_lookup_code = 'VOIDED'
THEN (nvl((nvl((aipa.amount + NVL(aipa.discount_taken,0)) * nvl (aia.exchange_rate, 1), 0)), 0))
ELSE (nvl((nvl(ABS((aipa.amount + NVL(aipa.discount_taken,0)) * nvl (aia.exchange_rate, 1)), 0)), 0)) END
ELSE 0 END functional_dr,
CASE WHEN (nvl(nvl(aia.invoice_amount, 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)) < 0
THEN (nvl((CASE WHEN chk.status_lookup_code = 'VOIDED'
THEN (nvl((nvl((aipa.amount + NVL(aipa.discount_taken,0)) * nvl (aia.exchange_rate, 1), 0)), 0))
ELSE (nvl((nvl(ABS((aipa.amount + NVL(aipa.discount_taken,0)) * nvl (aia.exchange_rate, 1)), 0)), 0)) END
+ 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))
ELSE 0 END 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,
aia.invoice_num applied_doc,
(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
) applied_doc_type
FROM ap_invoices aia,
ap_invoice_payments aipa,
ap_checks chk,
hr_operating_units hou,
hz_parties hp,
hz_party_sites hps
WHERE aia.invoice_id = aipa.invoice_id
AND aipa.check_id = chk.check_id
AND 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.invoice_type_lookup_code = 'PAYMENT REQUEST'
AND aia.approval_status NOT IN ('NEVER APPROVED')
AND aipa.posted_flag = 'Y'
--Parameters
AND aia.org_id = :p_bu_id
AND hp.party_id = nvl(:p_supplier_number, hp.party_id)
AND TRUNC(aipa.accounting_date) BETWEEN :p_from_date and :p_to_date