i-Expense Common setup and Trouble Shoot

i-Expense Common setup and Trouble Shoot

Using Oracle iExpenses Module, employees can enter and submit expense reports. There is a Workflow process that is initiated behind when an expense report is submitted which automatically routes expense reports for approval. Oracle Internet Expenses(iExpenses) integrates with Oracle Payables to provide quick processing of expense reports for payment.

This article includes these topics:

Process Overview
Approval Hierarchy
Administrative features in iExpense
Workflow Notifications -- Error Handling

Process Overview:

The employees would be issued Corporate credit cards which could be used for organization's business expenses.
The bank would send in all the credit card transaction files on a daily basis or the agreed time interval.
These credit card transactions have to be loaded into AP_CREDIT_CARD_TRXNS_ALL table using credit card transaction
loader programs. After loading credit card transactions data into the AP_CREDIT_CARD_TRXNS_ALL table,
the Credit Card Transactions Validation program should be run to validate the loaded data. Once validated,
employees can enter these credit card transactions on expense reports to get re-imbursement from the company.

Once the employee vouchers the expenses through iExpense responsibility or
Expense report screen in Account Payables responsibility and submits it,
the workflow with the internal name APEXP is initiated which would validate the data entered, determine
the Approver, sends notifications to the approver. The Approver has to then Approve or Reject the notification accordingly.
Once the workflow activities are complete, use the concurrent program Expense Report Import to create Payables invoices
from expense reports you enter in Payables or Oracle Internet Expenses. You can then use Payables to validate and
pay these invoices and create accounting entries for them.


Approval Hierarchy in iExpense:

If the profile option "AME: Installed" is set to Yes, then the routing rules for approvals setup in Oracle Approvals Management are used, If set to No then the Employee/Supervisor Approval Process is used.
Employee/Supervisor Approval Process:
In the Employee/Supervisor Approval Process, the supervisor of the Expense report's owner would become the APPROVER. Every Employee should be assigned a Supervisor within Human Resources Responsibility- (New Hire: Assignments).An employee must be set up as a supplier prior to entering the expense report.
Managers can approve an expense report only if the total amount of the expense report does not exceed their signing limit. The Manager (Spending) Approval Process in the Expense Reporting workflow uses the signing limits you define(Navigation: Account Payables responsibility - Employees> Signing Limits )to determine which manager has authority to approve expense reports. If the amount on the expense report exceeds the signing limit of the approver, the expense report is automatically forwarded to the manager of the approver. This goes up the management hierarchy until an Approver is found who could approve the Expense report.

This process does not look at the purchasing Approval Hierarchy.
Administrative features in iExpense:
• Delegating the authorization to enter the expenses on one’s behalf:
You can authorize an employee to enter expense reports for another employee. An
employee who is assigned the responsibility to enter expense reports for another
employee is an authorized delegate
There are 2 ways this could be done:
• Go to System Administrator>Security> Users
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given.

 Go to the iExpense responsibility > Expense Preferences >Authorization Preferences
Under Expenses Entry Delegations, click on the Add another row and enter the name to whom the access to enter the expense reports on your behalf has to be given.

• Re-direct or Auto respond to the notifications:
If the approver is on leave, the notification could be delegated or forwarded to the desired approver using Create Vacation Rule.

Steps to create Vacation rule:
1. iExpense Responsibility> Expenses Home
2. Select the "Vacation Rule" link
3. Select the "Create New Rule" link
4. For Item Type select "Expenses" for all Notifications or select the desired Notification Type from List of Values
5. Select the "Next" link to proceed to the next setup page for the rule
6. Enter both a a valid Start and End Date for the new rule
7. Ensure the "Reassign" radio button is selected
8. Keep "All Employees and Users" in the menu box
9. Select the "Delegate" radio button - Assign/Reassign action is prohibited for Purchasing Notifications
10. Select the Flashlight icon next to the blank menu box for selecting a user
11. A new sub-screen should pop-up to perform the user search
12. For "Search By" select "Username" - (DO NOT search by Name)
13. Perform the needed wildcard (%) search at the top of the page to locate the desired
username
14. Click the radio button next to the desired name of the user to have the notifications sent to
15. Press the "Select" button followed by the "Apply" button
16. Continue on with the saving of the Vacation Rule for latter use

• Assigning the Card to another person to voucher the expenses on someone’s behalf
This could be done if a person has to access the outstanding credit card charges of another person to voucher them (temporarily). The disadvantage with this would be, the charges will be vouchered under the assignee’s name and would go to the assignee’s manager for Approval.

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 (+)


SENT: The notification has been successfully sent to the APPROVER.
CANCELED: The system is setup to send the notifications n times (n can be set in the workflow attributes) with a timeout after 5 days. If the Approver does not approve or reject within the time frame, the workflow will cancel the notification automatically and then the notification will end up in the status CANCELED.
Action: In such cases, RETRY would not work instead ask the preparer to withdraw and re-
submit the expense report.
MAIL: If the notification is not SENT to the approver, the status would be MAIL.
Action: Ask the DBA to bounce the workflow. This would solve the problem most times. But if it doesn’t i.e if the status is still MAIL, then RETRY the activity ‘Request Approval from APPROVER’ using the Workflow Administrator responsibility (demo below). If yet not sent, check the Attributes like Approver’s email etc.
BLANK: Sometimes the value of the status would be NULL.
Action: Sometimes even if the status is BLANK, the notification is sent to the approver. First check with the Approver to see if the notification is sent. If not sent, RETRY the activity.

UNAVAIL: This would happen usually when there is problem with the Approver’s preferences or Email address.
Action: Check the notification preference of the Approver using the below:
SELECT WFU.NAME, WFU.display_name,WFU.notification_preference, email_address ,wfu.status
FROM WF_USERS WFU
WHERE WFU.name in (‘NAME’) (NAME would be the user_name in FND_USER table)
The notification_preference can be set to one of the below
MAILTEXT
QUERY
DISABLED
MAILHTM2
MAILHTML
MAILATTH
Make sure that it is set to MAILHTML
Also check if the email address returned in the above query is a valid one and matches with the one in HR Employee record (hr_employee table), fnd_user table and the outlook email address.
Also check if the status is ACTIVE in the above query, if the APPROVER is an existing employee and his FND_USER account is ACTIVE.



FAILED:
Action RETRY would solve this case.
ERROR:
Action: Check the ERROR and act accordingly. Some sample Errors:

--Failed Activity AP Validate Expense Report 
Activity Type Function
Error Name -20001
Error Message ORA-20001: APP-SQLAP-10000: ORA-00984: column not allowed here occurred in AddDistributionLine with parameters (&PARAMETERS) while performing the following operation: parse cursor
Error Stack AP_WEB_EXPENSE_WF.APValidateExpenseReport(APEXP, 142459, 0, Call DoAPValidationORA-00984: column not allowed here occurred in AddDistributionLine with parameters (&PARAMETERS) while performing the following operation: parse cursor) Wf_Engine_Util.Function_Call(AP_WEB_EXPENSE_WF.APVALIDATEEXPENSEREPORT, APEXP, 142459, 175587, RUN)
Check if the code combinations are all passed in correct and if it exists. There is something wrong in the data which results in this error. If the expense report errors out in this Stage i.e it has not passed the validation, Status in expense report header would not be WEBEXPENSE and hence cannot see this in AP.

--. No Approver/Performer ERROR:

Check to see if there is any supervisor assigned in HR
Check to see if Supervisor exists in USERS list and in HR.
If everything is fine, In the status monitor, query for the Item key
Click on Active History
Check Request approval from APPROVER, hit Update attributes.
Fill in the appropriate Manager Id (Employee ID), Manager name(WF_USER.NAME), Manager Display Name(WF_user.display_name), approver ID, approver name ,Approver display name and hit Apply.

Go back to Active history, Check Request approval from APPROVER, hit Rewind.

--DISCARD folder error:
Failed Activity Request Approval from APPROVER
Activity Type Notice
Error Name WFRSPPR_BOUNCE
Error Message 2405: Notification '1251131' has bounced. Check mailer log and discard folder. STATUS=ERROR ROLE=ABHI

Ask the DBA to resend the notification from Workflow mailer. This is due to Mail Format or OUTLOOK version issues.
--Environment context Error:
Failed Activity Request Approval from APPROVER

Activity Type Notice

Error Name WF_ERROR 
Error Message [WF_ERROR] ERROR_MESSAGE=3835: Error '-20001 - ORA-20001: Oracle error -20001: ORA-20001: APP-FND-02500: Error occurred during product initialization for MO when executing 'begin MO_GLOBAL.INIT; end;'. SQLCODE = -20001 SQLERROR = ORA-20001: APP-FND-02902: Multi-Org profile option is required. Please set either MO: Security Profile or MO: Operating Unit profile option. has been detected in FND_GLOBAL.INITIALIZE.' encountered during execution of Generate function 'WF_XML.Generate' for event 'oracle.apps.wf.notification.send'. ERROR_STACK= Wf_Engine_Util.Function_Call(AP_WEB_EXPENSE_WF.CALLBACKFUNCTION, APEXP, 144392, SET_CTX) Wf_Engine_Util.Execute_Selector_Function(APEXP, 144392, SET_CTX) Wf_Engine_Util.Function_Call(AP_WEB_EXPENSE_WF.CALLBACKFUNCTION, APEXP, 144392, TEST_CTX) Wf_Engine_Util.Execute_Selector_Function(APEXP, 144392, TEST_CTX) Wf_Engine.CB(TESTCTX, APEXP:144392:175390, ::::) Wf_Engine.oldCB(TESTCTX, APEXP:144392:175390, ::::) WF_MAIL.SetContext(1252292) WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 1252292) WF_XML.Generate(oracle.apps.wf.notification.send, 1252292) WF_XML.Generate(oracle.apps.wf.notification.send, 1252292) Wf_Event.setMessage(oracle.apps.wf.notification.send, 1252292, WF_XML.Generate) Wf_Event.dispatch_internal()
Error Stack

RETRY would resolve the problem.
If all the attempts were futile in sending the notification to the Approver, the last action to resolve would be to ask the approver to go to IExpense responsibility and approve manually.



RETRY Demo:• Log into Workflow Admin responsibility
• Go to Administrator Workflow > Status Monitor
• Key in the following values:
Type Internal Name: APEXP
User Key: Expense Report Number
• Click Go
• Select the Radio button and click on Activity History.



• Select the Activity you want to RETRY say “Request Approval from APPROVER” and click on the Retry button on the top.



• Click on Submit button in the next screen.

• Confirm the details in the next page by clicking the OK button.




REWIND Demo:• Log into Workflow Admin responsibility
• Go to Administrator Workflow > Status Monitor
• Key in the following values:
Type Internal Name: APEXP
User Key: Expense Report Number
• Click Go
• Select the Radio button and click on Activity History.



• Select the Activity which you want to REWIND for example say “Request Approval from APPROVER” and click on the Rewind button on the bottom.



• Choose the activity to which you want the workflow to return in the next screen and select the "Apply" button. This would REWIND the activity.


UPDATE ATTRIBUTES Demo:
In the “Activity History” screen, click on the “Update Attributes” Button at the bottom. This opens up a page with all the attributes where you could update the necessary and hit “Apply” button to save the changes.

References:

http://ajitraut.blogspot.com/2009/05/status-monitor-key-in-following-values.html?_sm_au_=ikV8klD8mL733pNF

Oracle Employee Signing/Approval Limits

There is no API and other Method To Enter Signing Limits Metalink Note (171837.1)
The only option is direct insert in base table. :)

INSERT INTO ap_web_signing_limits_all
(document_type,
employee_id,
cost_center,
signing_limit,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
org_id
)
VALUES ('APEXP',
123,
r1.cost_centre,
l_sup_amt,
SYSDATE,
gl_user_id,
gl_login_id,
SYSDATE,
gl_user_id,
201
);

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 (+)

Oracle Apps iExpenses Tables

The below query for relation ship between Oracle IExpenses  and Oracle Payables.

The below query will give the End to End details raising expense report to Payments made  against.

select *

  from ap_expense_report_headers_all aerha

      ,ap_expense_report_lines_all aerla

      ,ap_expense_report_params_all aerpa

      ,per_all_people_f papf

      ,ap_invoices_all aia

      ,ap_invoice_payments_all aipa

      ,ap_checks_all aca

 where aerha.employee_id = papf.person_id

   and aerha.report_header_id = aerla.report_header_id

   and aerla.web_parameter_id = aerpa.parameter_id

   and aerha.expense_status_code = 'PAID'

   and papf.effective_end_date >= trunc(sysdate)

   and aerha.invoice_num = aia.invoice_num

   and aia.invoice_id = aipa.invoice_id

   and aipa.check_id = aca.check_id;

SELECT AP_VALIDATION_ERROR,
         EXPDA.REPORT_HEADER_ID,
         EXPDA.REPORT_LINE_ID,
         EXPDA.REPORT_DISTRIBUTION_ID,
         EXPL.ITEM_DESCRIPTION,
         EXPL.WEB_PARAMETER_ID,
         EXPDA.WEB_PARAMETER_ID,
         (select fu.user_name from fnd_user fu where fu.user_id=expl.created_by) requested_user,
         expda.*
    FROM APPS.AP_EXPENSE_REPORT_HEADERS_ALL EXPH,
         APPS.AP_EXPENSE_REPORT_LINES_ALL EXPL,
         APPS.AP_EXP_REPORT_DISTS_ALL EXPDA
   WHERE     1 = 1
         AND EXPH.REPORT_HEADER_ID = EXPL.REPORT_HEADER_ID
         AND EXPL.REPORT_HEADER_ID = EXPDA.REPORT_HEADER_ID
         AND EXPL.REPORT_LINE_ID = EXPDA.REPORT_LINE_ID
         AND EXPL.AP_VALIDATION_ERROR IS NOT NULL
ORDER BY EXPL.REPORT_LINE_ID DESC;

How to authorize an employee to enter expense reports for another employee

You can authorize an employee to enter expense reports for another employee. An employee who is assigned the responsibility to enter expense reports for another employee is an authorized delegate. There are 2 ways this could be done: 

1. Go to System Administrator>Security> Users
Query for the User’s record. Under Securing Attributes tab, add the below details:
Attribute: ICX_HR_PERSON_ID
Application: Self-Service Web Applications
Value: Employee Id or the Person_id to whom the access to enter the expense reports has to be given. 

2. Go to the iExpense responsibility > Expense Preferences >Authorization Preferences
Under Expenses Entry Delegations, click on the Add another row and enter the name to whom the access to enter the expense reports on your behalf has to be given.

iExpense Implementation Steps

In this article i have given a broad outline of the setups to be done for iExpense implementation ,this may vary depending on the requirements for your implementation 

1) Set the tax Codes to be Uses in iExpenese

Payables - > Setup- > Tax - > Codes

2) Set the Hierarchy for Tax Code Defaulting

Payables - > Setup- > Options - > Payables

Here template mean expense template .

 3) For Project based expenses set the Expenditure type to be used in iExpense

Project ->Setup - >Expenditures - > Expenditure Type

To use a expenditure type it has to be assigned to Expenditure type class of "Expense Reports"

4 ) Payables Manager: Setup > Options > Financials

Expense Clearing Account ->This will be a default liability account for iExpenses expenses reports imported into Oracle Payables

5) Payables Manager: Setup > Options > Payables

Internet Expenses users can specify a reimbursement currency that is different from your company functional currency only if Oracle Payables is set up for multiple currencies

6) Payables - > Setup - > lookup - > Purchasing

7) Setup the signing limits

Paybles - > Employees- > Signing Limits

Expense Report goes to the Person defined here based on the cost center and the signing limit for approval

8 ) Setup Employee as suppliers

9 )

Create Expense Templates

Paybles - > Setup - >Invoice - > Expense Report Templates

Define Expense Items and assign then to Expense categories and for project based expense assign them against the project expenditure type . You can define policy and assign the policy schedule here and , dictate if justification is always mandatory or only with the policy violations . Default GL Accounts to ease the data entry for the users .

10)

Paybles - > Setup - > Options -> Payables

Set the Default Expense Template to be Used here , and mention if the prepayment can be applied against expense report by checking the apply advances check box .

11 ) To Show project and task on iExpense , do the changes in AK Developer and enable the node display .

12) Log on to Internet Expenses Setup and Administration

A - > Create Policy Schedules for your Expense Categories .

B - > Setup the expense field you want to enable or disable .

C -> Setup the Accounting flexfield to enabled , to let users update the default segment value .

13 )

A) -> Make sure all expense approving managers are set up as Oracle users.

B) -> Make sure every employee is assigned to one Oracle user only!

C) -> Submit the Synchronize WF LOCAL tables process regularly to update the Workflow resource information

14 ) Technically some of the customization which can be done of iExpenses are

A) OA Framework Customization on the Page

B) Customize Project Expense Report Account Generator (PAAPWEBX)

C) Customize expense approval workflow by default it follow employee supervisor hierachy

15) Some of the important Profiles in iExpenses are given below , use them as per your requirement

OIE:Allow Credit Lines Yes
OIE:Allow Non-Base Pay No
OIE:Approver Required Yes, with Default
OIE:CC Approver Req No
OIE:CC Payment Notify Yes
OIE:Carry Advances Forward Yes
OIE:Enable Advances Payables
OIE:Enable Approver Yes
OIE:Enable Bar Code
OIE:Enable Cost Center Read-only
OIE:Enable Credit Card No
OIE:Enable DescFlex Lines Only
OIE:Enable Expense Allocation Splitting Yes
OIE:Enable Expense Allocations Automatically-enabled, with Online Validation
OIE:Enable Policy
OIE:Enable Project Allocations Yes
OIE:Enable Project Expenditure Organization Yes
OIE:Enable Tax Yes
OIE:Grace Period 30
OIE:Purpose Required No
OIE:Report Number Prefix XXX-EXP:

16) Once the expense report is approved it can be imported into payables by running the expense report import program which will load as a Payable invoice of type "Expense Report"