Script to fetch iExpense Workflow Notification 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 (+)