Reminder Alert to PO Workflow Approver

Workflow
Mail Notification

                                                                                        
Description

This
blog is used to send an email notification to the transaction types like Purchase
Order, Requisition, Requisition
Change Order
and IExpense.  The Select
queries first it will select an email address then it will select display data
for specific message type, finally it will send an email notification.
— This select query is used
to retrieve an email addresses for specific 
   Message type from WF_NOTIFICATIONS table.
SELECT DISTINCT pap.email_address, pap.full_name
           FROM wf_notifications wfn, per_all_people_f pap
          WHERE wfn.MESSAGE_TYPE IN
                                 (‘POAPPRV’, ‘REQAPPRV’, ‘APEXP’, ‘POREQCHA’)
            AND wfn.status = ‘OPEN’
            AND wfn.to_user = pap.full_name;
— This query is used to
retrieve the display data for the message types 
   Purchase Order, Requisition, Requisition
Change Order and IExpense
          
    SELECT ‘Purchase Order’ trx_type, pha.segment1 trx_number,
       wfn.to_user approver_name, wfn.from_user requester_name,
       wfn.begin_date notif_start_date, wfn.subject,
       DECODE (wfn.MESSAGE_TYPE,
              
‘POAPPRV’, ‘Purchasing’,
              
‘REQAPPRV’, ‘Requisition’,
              
‘APEXP’, ‘iExpenses’,
              
‘POREQCHA’, ‘Requisition Change Order’
              ) item_type,
       wfn.status, pap.email_address
FROM
   
wf_notifications wfn
,
   
per_all_people_f pap
,
   
po_headers_all pha
WHERE
    wfn.MESSAGE_TYPE = ‘POAPPRV’
    AND wfn.status = ‘OPEN’
    AND wfn.to_user = pap.full_name
    AND NVL (pap.email_address, ‘XX’) = NVL (i.email_address, NVL (pap.email_address, ‘XX’))
    AND SUBSTR (wfn.item_key, 1, INSTR (wfn.item_key, ‘-‘) 1) = pha.po_header_id
    AND wfn.to_user = i.full_name
UNION ALL
SELECT DECODE (wfn.MESSAGE_TYPE,
              
‘REQAPPRV’, ‘Requisition’,
              
‘POREQCHA’, ‘Requisition Change Order’
              ) trx_type,
       prha.segment1 trx_number, wfn.to_user approver_name,
       wfn.from_user requester_name, wfn.begin_date notif_start_date,
       wfn.subject,
       DECODE (wfn.MESSAGE_TYPE,
              
‘POAPPRV’, ‘Purchasing’,
              
‘REQAPPRV’, ‘Requisition’,
              
‘APEXP’, ‘iExpenses’,
              
‘POREQCHA’, ‘Requisition Change Order’
              ) item_type,
       wfn.status, pap.email_address
FROM wf_notifications wfn,
      
per_all_people_f pap
,
      
po_requisition_headers_all prha
WHERE
   wfn.MESSAGE_TYPE IN (‘REQAPPRV’, ‘POREQCHA’)
   AND wfn.status = ‘OPEN’
   AND wfn.to_user = pap.full_name
   AND NVL (pap.email_address, ‘XX’) = NVL (i.email_address, NVL (pap.email_address, ‘XX’))
   AND SUBSTR (wfn.item_key, 1, INSTR (wfn.item_key, ‘-‘) 1) = prha.requisition_header_id
   AND wfn.to_user = i.full_name
UNION ALL
SELECT ‘iExpenses’ trx_type, aerh.invoice_num trx_number,
       wfn.to_user approver_name, wfn.from_user requester_name,
       wfn.begin_date notif_start_date, wfn.subject,
       DECODE (wfn.MESSAGE_TYPE,
              
‘POAPPRV’, ‘Purchasing’,
              
‘REQAPPRV’, ‘Requisition’,
              
‘APEXP’, ‘iExpenses’,
              
‘POREQCHA’, ‘Requisition Change Order’
              ) item_type,
       wfn.status, pap.email_address
  FROM wf_notifications wfn,
      
per_all_people_f pap
,
      
ap_expense_report_headers_all aerh
WHERE
    wfn.MESSAGE_TYPE = ‘APEXP’
    AND wfn.status = ‘OPEN’
    AND wfn.to_user = pap.full_name
    AND NVL (pap.email_address, ‘XX’) = NVL (i.email_address, NVL
   (pap.email_address, ‘XX’))
    AND wfn.item_key = aerh.report_header_id
    AND wfn.to_user = i.full_name;
               



— This plsql block is used to send mail     
DECLARE
   p_to                 VARCHAR2 (100)    :=
‘Valid Email Id’;
  
lv_smtp_server      
VARCHAR2 (100)    :=
‘199.1.1.77’;
  
lv_domain           
VARCHAR2 (100);
   lv_from              VARCHAR2 (100)      :=
‘Valid Domain’;
  
v_connection         UTL_SMTP
.connection;
   c_mime_boundary      CONSTANT VARCHAR2 (256):= ‘–AAAAA000956–‘;
BEGIN
   v_clob := ‘Number’ || ‘,’ || ‘Name’ || UTL_TCP.crlf;
  
v_connection
:= UTL_SMTP.open_connection (lv_smtp_server);
   –To open the connection      UTL_SMTP.helo (v_connection, lv_domain);
   UTL_SMTP.mail (v_connection, lv_from);
   UTL_SMTP.rcpt (v_connection, p_to); — To send mail to valid
receipent
   UTL_SMTP.open_data (v_connection);
   UTL_SMTP.write_data (v_connection, ‘From: ‘ || lv_from || UTL_TCP.crlf);
   IF TRIM (p_to) IS NOT NULL
   THEN
      UTL_SMTP.write_data (v_connection, ‘To: ‘ || p_to || UTL_TCP.crlf);
   END IF;
   UTL_SMTP.write_data (v_connection,
                        ‘Subject: Item Creation Report’ || UTL_TCP.crlf
                       );
   UTL_SMTP.write_data (v_connection, ‘MIME-Version: 1.0’ || UTL_TCP.crlf);
   UTL_SMTP.write_data (v_connection,
                           ‘Content-Type: multipart/mixed;
boundary=”‘
                        || c_mime_boundary
                        || ‘”‘
                        || UTL_TCP.crlf
                       );
   UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
   UTL_SMTP.write_data (v_connection,
                           ‘This is a multi-part message
in MIME format.’
                        || UTL_TCP.crlf
                       );
   UTL_SMTP.write_data(v_connection, ‘–‘ ||c_mime_boundary|| UTL_TCP.crlf);
   UTL_SMTP.write_data(v_connection,‘Content-Type: text/plain’||UTL_TCP.crlf
                       );
   UTL_SMTP.write_data (v_connection, UTL_TCP.crlf);
   UTL_SMTP.close_data (v_connection);
   UTL_SMTP.quit (v_connection);
   COMMIT;

END;



— By
— Eswaramoorthi M
  • September 27, 2016 | 23 views