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

依user所提供sample,有些PO因狀況不同而沒列到(如以下SQL).

由於workflow已結束, 要以更新need by date並approve的方式發出PDF.

select wn.notification_id,

       wn.message_name,
       wn.begin_date,
       wn.mail_status,
       wn.item_key,
       wn.user_key,
       wn.subject
  from wf_notifications wn
 where 1=1
   --and wia.item_key ='2271515-175073'
   and wn.message_name = 'EMAIL_PO_PDF'
   and wn.mail_status ='ERROR'
   and wn.status = 'OPEN'
   and wn.begin_date >= to_date('20190801','yyyymmdd')
 
union all

select wn.notification_id,

       wn.message_name,
       wn.begin_date,
       wn.mail_status,
       wn.item_key,
       wn.user_key,
       wn.subject
  from wf_notifications wn
 where 1=1
   --and wia.item_key ='2271515-175073'
   and wn.message_name = 'PO_PO_HAS_BEEN_APPROVE'
   and wn.mail_status ='ERROR'
   and wn.status = 'CANCELED'
   and wn.begin_date >= to_date('20190801','yyyymmdd')