Oracle Internet Expenses Overdue Receipts Notifications
Steps required to setup Oracle Internet Expenses Overdue receipt Notifications-
Go to 'Internet Expenses Setup and Administration' responsibility
Click on 'Internet Expenses Setup'
Below page will get opened, click on Audit tab as shown below and after that click on Notifications tab as shown-
Then you have create rule set name and provide description.
After that you can see 2 options which you have to set as per your requirement .
1. Receipts Received Notification: You would like to send notification once receipts recevied.
2. Overdue receipt Notifications:
Here you have enter in days like after how many days after expense report submitted want to send overdue receipt notification etc.
You can see above screenshot how to setup values.Above Overdue receipt notification interval set for 15 days. Means After 15 days of expesne creation, if no receipt recevied then send notification to person who has submitted.
Concurrent program 'Expenses Overdue Receipts Tracking' which tracks and send notification of Overdue receipts expenses-
You can schedule this program on daily basis.
Criteria for this program is-
- receipts status is REQUIRED or MISSING
- original reports (excludes bothpay child reports)
- reports that have effective notification rules
- no Overdue/Missing Receipts WF exists
- REQUIRED receipts are overdue
MISSING receipts are overdue and phys doc is reqd
Database I-Expenses Tables which holds setup values-
SQL Query to find Oracle Internet Expenses Overdue required/missing receipts-
from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
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'
BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
(aerh.RECEIPTS_STATUS = 'REQUIRED' and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
(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);