Tuesday, 27 September 2016

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

No comments:

Post a Comment