Oracle Internet Expenses(I-Expenses) Overdue Receipts Notifications Setup

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
    or
    MISSING receipts are overdue and phys doc is reqd

Database I-Expenses Tables which holds setup values-

AP_AUD_RULE_SETS
Ap_Aud_Rule_Assignments_All


SQL Query to find Oracle Internet Expenses Overdue required/missing receipts-


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);