Remittance Advice by Email to Supplier
Here’s a quick solution to send Remittance advice to supplier by email.
Pre Requisite
1. UTL_SMTP should install on database
2. Valid Exchange Server IP with access to and from database server
3. Supplier Email address should be updated in oracle Application Supplier Site Interface
(see field email address in below screen)
4. Valid Sender email address on same domain which is used in Send mail Package
Create Following Package to send email
CREATE OR REPLACE PACKAGE APPS.AHMAD_MAIL_PKG IS
PROCEDURE send_email ( sender IN VARCHAR2 , (continued below)
recipient IN VARCHAR2 , (subject IN VARCHAR2 , message IN VARCHAR2 );
END AHMAD_MAIL_PKG;
/
CREATE OR REPLACE PACKAGE BODY APPS.AHMAD_mail_pkg
IS
PROCEDURE send_email (
sender IN VARCHAR2,
recipient IN VARCHAR2,
subject IN VARCHAR2,
MESSAGE IN VARCHAR2
)
IS
/*
Your mail host IP address or Name should be here
*/
mailhost VARCHAR2 (100) := ‘172.19.76.85′; /
* Exchange Server Ip Address */
mail_conn UTL_SMTP.connection;
PROCEDURE send_header (NAME IN VARCHAR2, header IN VARCHAR2)
IS
BEGIN
UTL_SMTP.write_data (mail_conn,
NAME || ‘: ‘ || header || UTL_TCP.crlf
);
END send_header;
BEGIN
mail_conn := UTL_SMTP.open_connection (mailhost);
UTL_SMTP.helo (mail_conn, mailhost);
UTL_SMTP.mail (mail_conn, sender);
UTL_SMTP.rcpt (mail_conn, recipient);
UTL_SMTP.open_data (mail_conn);
send_header (’From’, ‘< ' || sender || '>‘);
send_header (’To’, ‘< ' || recipient || '>‘);
send_header (’Subject’, subject);
UTL_SMTP.write_data (mail_conn, UTL_TCP.crlf || MESSAGE);
UTL_SMTP.close_data (mail_conn);
UTL_SMTP.quit (mail_conn);
EXCEPTION
WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error
THEN
UTL_SMTP.quit (mail_conn);
raise_application_error
(-20000,
‘Failed tosend mail due to the following error: ‘
|| SQLERRM
);
WHEN OTHERS
THEN
raise_application_error (-20001,
‘The following error has occured: ‘
|| SQLERRM
);
END send_email;
END AHMAD_mail_pkg;
/
Create Following Function to Format Invoices detail which are being paid
CREATE OR REPLACE FUNCTION APPS.AHMAD_PAYMENT_INVOICE_DTL
(P_CHECK_NUMBER IN VARCHAR2 ) RETURN VARCHAR2 IS
CURSOR GET_INV_DTL IS
SELECT AIPA.INVOICE_ID,AIA.INVOICE_NUM,AIA.INVOICE_DATE,AIA.DESCRIPTION,
AIA.INVOICE_AMOUNT,AIA.INVOICE_CURRENCY_CODE
FROM AP_INVOICE_PAYMENTS_ALL AIPA,AP_INVOICES_ALL AIA
WHERE AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.CHECK_ID IN (SELECT CHECK_ID FROM AP_CHECKS_ALL WHERE CHECK_NUMBER = P_CHECK_NUMBER);
XVAL varchar2(4000);
BEGIN
XVAL := RPAD(’Invoice Number’,20,’ ‘)||RPAD(’Dated’,10,’ ‘)
||RPAD(’Amount’,20,’ ‘)||RPAD(’Currency’,10,’ ‘)||RPAD(’Description’,40,’ ‘)||chr(10);
FOR I IN GET_INV_DTL LOOP
XVAL := XVAL || RPAD(I.INVOICE_NUM,20,’ ‘)||RPAD(I.INVOICE_DATE,10,’ ‘)
||RPAD(I.INVOICE_AMOUNT,20,’ ‘)||RPAD(I.INVOICE_CURRENCY_CODE,10,’ ‘)
||RPAD(I.DESCRIPTION,40,’ ‘)||CHR(10);
END LOOP;
RETURN XVAL;
END AHMAD_PAYMENT_INVOICE_DTL;
/
Package to Send Invoices on basis of batch number to Supplier
CREATE OR REPLACE package APPS.AHMAD_REMITANCE_ALERTS_PKG as
procedure alert_cp(errbuf out varchar2, retcode out varchar2);
procedure alert(p_batch in varchar2);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.AHMAD_remitance_alerts_pkg
AS
PROCEDURE alert_cp (errbuf OUT VARCHAR2, retcode OUT VARCHAR2)
IS
BEGIN
alerts_pkg.alert;
END;
PROCEDURE alert(p_batch in varchar2)
IS
CURSOR c_remitance
IS
SELECT aca.amount, aca.bank_account_name, to_char
(aca.check_date,’ddth Month yyyy’) check_date,
aca.check_number, aca.currency_code, aca.vendor_name,
aca.vendor_site_code, aca.check_voucher_num,
aca.cleared_amount, aca.cleared_date, aca.doc_sequence_value,
aca.base_amount,aca.CHECKRUN_NAME || aca.VENDOR_ID MAIL_REF,
(SELECT pvc.email_address
FROM po_vendor_contacts pvc
WHERE pvc.vendor_site_id = aca.vendor_site_id) email,ACA.BANK_ACCOUNT_NUM,
(SELECT ABB.BANK_NAME|| ‘ / ‘ ||ABB.BANK_BRANCH_NAME
FROM AP_BANK_ACCOUNTS_ALL ABAA,AP_BANK_BRANCHES ABB
WHERE ABAA.BANK_BRANCH_ID = ABB.BANK_BRANCH_ID
AND ABAA.BANK_ACCOUNT_ID = ACA.BANK_ACCOUNT_ID) BNK_BRNCH,
(SELECT pvc.PHONE
FROM po_vendor_contacts pvc
WHERE pvc.vendor_site_id = aca.vendor_site_id) PHONE,
(SELECT pvc.FAX
FROM po_vendor_contacts pvc
WHERE pvc.vendor_site_id = aca.vendor_site_id) FAX,
FTV.TERRITORY_SHORT_NAME COUNTRY
FROM ap.ap_checks_all aca,FND_TERRITORIES_VL FTV
WHERE ACA.COUNTRY = FTV.TERRITORY_CODE
AND aca.checkrun_name IS NOT NULL
AND aca.checkrun_id IN (
SELECT aisca.checkrun_id
FROM ap_inv_selection_criteria_all aisca
WHERE aisca.checkrun_name IS NOT NULL
AND aisca.status = ‘CONFIRMED’)
AND aca.checkrun_name = p_batch ;
l_sender_address VARCHAR2 (50);
l_probation_alert NUMBER;
l_document_alert NUMBER;
l_contract_alert NUMBER;
l_contact_alert NUMBER;
l_receipt VARCHAR2 (160);
l_msg VARCHAR2 (4000);
c_msg VARCHAR2 (4000);
l_full_name VARCHAR2 (100);
BEGIN
l_sender_address := ‘erp@oracleebusinesssuite.com’; /*
Senders Email Address Company Should Keep this as Standard */
l_msg := NULL;
FOR i IN c_remitance
LOOP
c_msg :=
i.check_date||chr(10)
|| ‘.’||CHR(10)
|| ‘Reference: ‘
|| i.MAIL_REF ||chr(10)
||’.'||chr(30)
|| i.vendor_name||chr(10)
|| i.vendor_site_code||chr(10)
|| I.COUNTRY ||CHR(10)
|| ‘Fax No: ‘
|| i.FAX||chr(10)
|| ‘.’||chr(10);
c_msg := CHR(10) || c_msg || ‘Dear Sir,’||chr(10)
||’We have Advised our bank to credit your account: ‘
||i.bank_account_num ||’ with the ‘||i.bnk_brnch||
‘, in settlement of the following invoices’||chr(10)
||’.'||chr(10)
||AHMAD_PAYMENT_INVOICE_DTL(i.check_number)||chr(10);
l_msg := c_msg || ‘
Email Sent by Oracle Alerts on ‘ || SYSDATE;
ahmad_mail_pkg.send_email (l_sender_address,
i.email,
‘Your Payment is Ready’,
l_msg
);
END LOOP;
END alert;
END AHMAD_remitance_alerts_pkg;
/
You can schedule this as request on day end or write this as after report parameter in payment report.