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