[Fusion] Query to Retrieve Remittance Email for Suppliers Configured with Email Delivery in Oracle Fusion

 

Oracle Fusion allows suppliers to receive remittance advice through various delivery methods, including email. This blog post presents a precise SQL query to retrieve invoice, supplier, and remittance email details where the delivery method is configured as EMAIL. 

The following SQL query retrieves the invoice number, supplier name, and the remittance email address, but only for those suppliers who are configured for email delivery.

SELECT 
    aia.invoice_num,
    psv.vendor_name,
    iepa.remit_advice_email
FROM    
    ap_invoices_all aia,
    poz_suppliers_v psv,
    iby_external_payees_all iepa
WHERE 
    1 = 1
    AND aia.vendor_id = psv.vendor_id
    AND psv.party_id = iepa.payee_party_id (+)
    AND 'EMAIL' = iepa.remit_advice_delivery_method (+)
    AND aia.invoice_num = '10145524';

ap_invoices_all: Holds invoice header information.

poz_suppliers_v: Supplier view that provides names and party relationships.

iby_external_payees_all: Stores supplier payment communication preferences like delivery method and remittance email.

Checking Remittance Email in Oracle Fusion

Login to Oracle Fusion and navigate to Procurement → Suppliers


Click on Search button and enter the Supplier Name and then select it


 Go to Profile → Payments → Payment Attributes → Separate Remittance Advice Delivery


Here you will find remittance advice delivery details