SQL Query to find Oracle Internet Expenses Overdue receipts

SQL Query to find Oracle Internet Expenses Overdue receipts-

Important Note: Please visit my post in this blog  'Oracle Internet Expenses(I-Expenses) Overdue Receipts Notifications Setup' for more information


select aerh.report_header_id,
aerh.employee_id,
aerh.description,
aerh.total,
aerh.receipts_status
from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
AP_AUD_RULE_SETS rs,
Ap_Aud_Rule_Assignments_All Rsa
where aerh.receipts_status in ('REQUIRED', 'MISSING')
And Aerh.Bothpay_Parent_Id Is Null
and rsa.org_id = aerh.org_id
And Rsa.Rule_Set_Id = Rs.Rule_Set_Id
and rs.rule_set_type = 'NOTIFY'
and TRUNC(aerh.report_submitted_date)
BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
and
(
(aerh.RECEIPTS_STATUS = 'REQUIRED' and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
Or
(aerh.RECEIPTS_STATUS = 'MISSING' and rs.NOTIFY_DOCUMENT_REQUIRED_CODE = 'REQUIRED' and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
)
and Not Exists
(select 1 from wf_items where aerh.RECEIPTS_STATUS = 'REQUIRED' and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.overdue%' and end_date is null and rownum=1)
and Not Exists
(Select 1 From Wf_Items Where Aerh.Receipts_Status = 'MISSING' And Item_Type = 'APWRECPT' And Item_Key Like To_Char(Aerh.Report_Header_Id)||':receipts.missing%' And End_Date Is Null And Rownum=1);