Oracle EBS: 針對未正常寄送的PO pdf重送

問題: 系統異常, 造成PO pdf檔未寄送, 在wf_notifications中的mail_status為ERROR

解法: 取 [SQL 1] 的item_key(並記錄 notification_id以作後續比對), 再參考 [SQL 2] 執行Retry. 

 
SQL 1:
 
select * from wf_notifications
 where message_name ='PO_EMAIL_PO_PDF_WITH_RESPONSE'
   and begin_date >= to_date('20190802','yyyymmdd')
   and status = 'OPEN'
   and nvl(mail_status,'X') ='ERROR'
   order by notification_id

SQL 2:

 
DECLARE
   CURSOR c1
   IS
      SELECT   pa.instance_label label, iass.activity_result_code RESULT,
               iass.item_type, iass.item_key, process_activity,
               pa.process_name
          FROM wf_item_activity_statuses iass, wf_process_activities pa
         WHERE iass.item_type = 'POAPPRV'
           --AND pa.process_name = 'EMAIL_DOC_PROCESS'
           AND pa.process_name = 'EMAIL_DOC_PROCESS_CO'
           AND iass.process_activity = pa.instance_id
           and iass.item_key = '2271515-175073'
           AND iass.begin_date >= TO_DATE ('20190101', 'yyyymmdd')
           and pa.instance_label = 'NOTIFY_WEB_SUPP_PDF'
           AND EXISTS (
                  SELECT ias.item_key
                    FROM wf_item_activity_statuses ias,
                         wf_process_activities pa
                   WHERE ias.item_type = 'POAPPRV'
                    --AND pa.process_name = 'EMAIL_DOC_PROCESS'
                     AND ias.item_key = iass.item_key
                     AND ias.process_activity = pa.instance_id
                     AND ias.activity_status = 'ERROR')
      ORDER BY iass.item_type;

BEGIN
   FOR v1 IN c1
   LOOP
      BEGIN
         wf_engine.handleerror (v1.item_type,
                                v1.item_key,
                                v1.process_name || ':' || v1.label,
                                'RETRY'
                               );
         COMMIT;

      EXCEPTION
         WHEN OTHERS
         THEN
           DBMS_OUTPUT.put_line (   'error in the following item key :'
                                  || v1.item_key
                                 );
      END;
   END LOOP;

END;