Supplier master remittance advice email sql query in oracle apps r12 : SQL Query to Extract Supplier remittance advice information's in Oracle

Supplier master remittance advice email sql query in oracle apps r12 : SQL Query to Extract Supplier remittance advice information's in Oracle

Remittance Advice is Kind of Payment Notifications to Supplier. Supplier master remittance advice contains information about the Supplier Invoice Number against which the Payment issued. Remittance advice also have the Information's about Payment like Payment number , Payment date and the Supplier Invoice number to which this payment has processed. With the help to Remittance Advice , Suppliers gets the Notifications for his payment. In Oracle apps, We can send the remittance advice email automatically to supplier when the payment for that supplier creates in Oracle application.

 

To Send Supplier master remittance advice email , We should have proper setup in the system and if you want to know the detail steps then you can refer this link Remittance advice setup in Oracle apps .

 

Once the  Supplier master remittance advice setup completed in system , then in the second phase we need to maintain the supplier Remittance email in the supplier Master. Remittance advice setup reads the Supplier email from the Supplier Master to send email for remittance advice to supplier. We should know that in which place we need to maintain this remittance advice email in supplier master in oracle apps r12. Here below I will share the complete sql query to fetch the Supplier master remittance advice email in oracle apps r12.

Step to Maintain the remittance advice email in Supplier Master

Step1:- Go to Supplier Master and Then Click on the Payment Details Tab Highlighted below.



Step2:- Under Separate Remittance Advice Delivery Tab maintain the Supplier remittance advice email.

 

 

SQL Query to Extract Supplier remittance advice information's in Oracle

 

SELECT

aps.vendor_id,

aps.vendor_name "Vendor Name",

aps.end_date_active,

aps.segment1 "Vendor Number",

aps.vendor_type_lookup_code "Vendor Type",

apss.vendor_site_code "Vendor Site Code",

ou.name "Operating Unit",

iepa.remit_advice_delivery_method "Remittance Delivery Method",

iepa.remit_advice_email "Remittance Advice Email"

FROM

ap.ap_suppliers aps,

ap.ap_supplier_sites_all apss,

hr_operating_units ou,

iby.iby_external_payees_all iepa

WHERE nvl(aps.vendor_type_lookup_code,'uu') <> 'EMPLOYEE'

AND iepa.supplier_site_id = apss.vendor_site_id

and st.org_id=:P_ORG_ID

AND iepa.payee_party_id = aps.party_id

AND apss.org_id = ou.organization_id

AND apss.vendor_id = aps.vendor_id

and iepa.remit_advice_email is not null

and aps.segment1=:P_SUPPLIER_NUMBER

ORDER BY aps.vendor_name, apss.vendor_site_code