Expense report Workflow Notifications Error Handling

In this section we will mainly focus on the notifications that are sent to the APPROVER for the approval as it is very important to see that such notifications reach the APPROVER on time so that there is no delay in the processing of the expense report to avoid late fee enforced by the Credit Card company. These notifications will have the message_name as 'OIE_REQ_EXPENSE_REPORT_APPRVL' in wf_notifications table. The mail status of the notification can be SENT, CANCELED, MAIL, UNAVAIL, ERROR, NULL. Let us see in detail about these and the actions required to get the notification to SENT status..

The below is the script to check the notification status:


SELECT wn.notification_id, aerh.invoice_num,
TO_CHAR (wn.begin_date, 'DD-MON-YYYY hh:mi:ss') begin_date, wn.to_user,
wn.from_user, wr2.display_name preparer, wr3.display_name reported_for,
wn.status, wn.mail_status mail_status, aerh.expense_status_code,
wr.email_address, aerh.total, aerh.report_submitted_date,
aerh.description
FROM wf_notifications wn,
wf_item_activity_statuses was,
wf_items wi,
ap_expense_report_headers_all aerh,
wf_roles wr,
wf_roles wr2,
fnd_user fu,
wf_roles wr3
WHERE 1 = 1
AND wn.notification_id = was.notification_id
AND was.item_type = wi.item_type
AND was.item_key = wi.item_key
AND wn.status IN ('OPEN', 'CANCELED')
AND wn.message_name = 'OIE_REQ_EXPENSE_REPORT_APPRVL'
AND aerh.invoice_num = wi.user_key
AND aerh.expense_status_code IN ('PENDMGR', 'RESOLUTN')
AND wn.recipient_role = wr.NAME(+)
AND wi.owner_role = wr2.NAME
AND aerh.employee_id = fu.employee_id
AND fu.user_name = wr3.NAME(+)