Executive summary
Today I am going to share a solution i have implemented for previous O&G client based in Texas that went live on Oracle cloud financials while maintaining their travel expenses on Concur SAP.
You can implement Oracle ERP Cloud Service while co-existing with your current employee expense management system. This provides you a low risk phased cloud adoption approach with minimal implementation effort and without disrupting your current financial management processes.
This Paper discusses how You can leverage your third party Expenses offering for expense entry, corporate card integration, policy enforcement and auditing expense reports and continue to rely on your Oracle cloud financial system for employee and credit card payment processing and accounting. This white paper discusses implementation steps and considerations for implementing Oracle ERP Cloud Service to co-exist with your legacy expense system
Introduction
Fully automated financial integration between the Expense solution ( in this paper we will use Concur® as an example) and the Oracle Cloud® ERP application – freeing you from manual processes through middle ware.
While some cloud projects opt for FBDI manual invoice upload we managed to fully automate expense report processing and payment in Oracle cloud payment via the middle ware.
While the manual process is prone to errors and high maintenance we at previous projects managed to automate the process from:
· Creating of the employee’s profile.
· Importing expense reports.
· Creating suppliers, addresses, sites and banks.
· Updating back Concur with the payment status upon successful transmission.
Without seamless integration between your expense, payment, accounting applications, companies with many expense reports and transnational documents experience a growing number of mistakes such as lost invoices, late payments, invoice duplicates, double payments and human error during data entry. This integration eliminates these mistakes with features, auto-validation and semi-real-time payment statuses.
With Concur integration with Oracle cloud, payments are maintained at a central location. After the data is accurately captured imported and validated, it is sent to the disbursement bank for reimbursement.
Improved payment and accounting Management
Oracle cloud's payment has made fund disbursement management easy for many companies across the globe. The advanced process enables companies to quickly automate the entire payment process and collect all the relevant data and approvals along the way making it easy for one source system to manage payment and communicate with your disbursement bank.
Also it made it easier to account for your expense (third party and employees ) tax , project related expenses as well as close the payable period and publish one payable trial balance.
Solution details
Overview
The outlined solution is divided into two parts:
1- Inbound interface: consuming the file generated from 3rd part expense system.
2- Outbound Interface: updating the 3rd party application with the payment status from Oracle ERP cloud.
Inbound Interface from Concur to Oracle Cloud ERP:
Diagram-1: SAE file Outbound Overall Process Flow
Concur outbound feed File requirements (SAE).
1. Once expense reports are approved in concur the middle-ware will pull the SAE file (standard accounting extract).
2. Separate SAE files should be generated for each business unit.
3. SAE file should indicate if the line is tax or expense charges.
4. SAE file should indicate if the line is credit or cash.
5. The SAE file should indicate the credit card program (IBCP, CBCP or IBIP) for those credit lines.
o CBCP (Company Liability) The company receives the statement and pays the card.
o IBCP (Shared Liability) The individual receives the statement and the company pays the card
o IBIP (Individual Liability) The individual receives the statement and pays the P-card.
i. This credit card program (IBIP) is managed outside of concur.
6. The SAE file should not include the personal expense for those countries registered with shared liability program (IBCP).
7. SAE file should have one separate line for cash advances or prepayments
8. The SAE file should not include the POET (Project, Organization, Expenditure Type, Task, and Source) if you would like to account for the project expenditures.
o For more details on how to derive your project details on the invoice distribution Please refer to (How to get Invoice Distribution account should be derived from project details for Third Party Expense Report (Doc ID 2254361.1)
Vendor outbound feed File format:
This extract is primarily used as the source of data that will ultimately be imported to the client General Ledger or Accounts Payable system. This feed however contains a wide range of
data to accommodate a broad range of integration needs.
The extract file contains the following:
o Batch Constants
o Employee Level Data
o Report Level Data
o Entry Level Data
o Credit Card Data
o Journal Level Data
o Car Mileage Data
o Cash Advance Data
o Allocation Data
o Travel Allowance Data
o Tax Data
o Assigned Travel
o Request Data
o Future Use (Not currently utilized)
All the different data elements are included in each row.
This file is delimited by a | (Pipe). The specific data elements in the AP/GL file are listed below.
Cloud AP Setup Requirements:
Suppliers:
· The credit card providers should be created as suppliers in CLOUD.
· IF your client does not have CONCUR PAY implemented then employees should be created as suppliers and concur’s expense reports should be imported as invoices not one-time payment request.
· IF your client has CONCUR PAY implemented then employees do not have to be created as suppliers and concur’s expense reports can be imported as invoices not one-time payment request. Please refer to the SAE mapping file attached above to see how the the employee banking details are provided on the SAE file under concur pay section.
· For each supplier created for employee it must have the employee number stored on the supplier profile (e.g. header attribute 1).
o We recommend when defining supplier for employees you should append the employee number of unique identified on the supplier name as indiviuals can share the same full name.
Invoices:
o For all cash expense line the invoice line type will be ‘ITEM’ and for all remaining lines the invoice line type will be ‘MISCELLANEOUS’
o The code combination coming from concur should exist in CLOUD.
o The tax amounts will come from concur as separate lines. CLOUD should not calculate any taxes. The tax line will be as ‘MISCELLANEOUS’ line in CLOUD.
o The tax amounts will come from concur as separate lines. CLOUD should not calculate any taxes. The tax line will be as ‘MISCELLANEOUS’ line in CLOUD.
o All invoices are imported with payment method as EFT.
o Create custom user validation on EFT payment method to ensure invoices with EFT payment method will have a supplier bank.
Ø For more details on payment method user validation Please refere to (How to Create an AP Invoice Hold For Suppliers With Payment Method Electronic And Missing Bank Account (Doc ID 2024872.1))
Mapping Logic Cloud and Middleware
Diagram-2: SAE file Outbound detailed Flow
1. Logic Used to Validate the SAE file:
Read SAE file (country specific).
For Each SAE record.
o If the Report ID + Payee Type (employee/Credit Card) exists in Report Headers List
o Add SAE record to Report Details List attached to the Report Header.
Else
o Add a new entry to the Report Headers List.
o Add SAE record to Report Details List attached to new Report Header.
o Lookup Employee ID or Credit Card ID in Supplier Cross Reference table.
o If found Add Required Supplier info to the Report Header.
Else
o Add Employee ID to the Supplier Create List.
o Set the required Supplier info to blank on the Report Header.
End If
End If
End for Each
For Each Employee ID on the Supplier Create List
o Gather the required information from the Data Warehouse.
o Gather the employee banking details from the Cloud Web Service .
o Add required information and banking details to the Create Suppliers Request.
End for Each
If the Create Suppliers Request is not empty
o Call the Create Suppliers web service.
For Each entry in the web service response
o Locate all Report Headers for that employee ID.
o For Each Report Header found
o Add required Supplier info to the Report Header.
End for Each
End for Each
End If
For Each entry on the Report Headers List
o Create a new Invoice Header for the Report ID + Payee Type.
For Each SAE Record attached to the Report Header entry
o Create a new Invoice Details entry [details/mapping – TBS].
End for Each
End for Each
o Add Invoice Header information to the web service payload.
o Add Invoice Details information to the web service payload.
o Invoke the Upload process to create the Invoice Load file for the Entity/Country.
2. Logic Used to Create Oracle Cloud AP invoices:
1. Expense report key would be the invoice number used in Oracle Cloud Payables.
2. In case of splitting single expense report to credit and cash the invoice number will be appended with. # whereas # is number of credits attached to the expense report. (please refer to invoices # 1.2 and # 1.1 created below)
3. If we have an expense report with cash and credit card lines, then we need to create the invoices as below:
Let us say we have one expense report EXP1 with below lines
a. Cash line with amount 20
b. Cash line with amount 25
c. Amex credit card line with amount 20
d. Amex credit card line with amount 25
e. Citi bank credit card line with amount 20
f. Citi bank credit card line with amount 25
g. Citi bank credit card line with amount 30
Then we need to create three invoices as below:
Invoice # 1 Total Expense Report Lines for the Employee Supplier:
EXP1 with amount 45 with below lines
Note: EXP1 will have source, as ‘CONCUR’ and invoice type will be ‘Standard’
o Cash line with amount 20 Line Type: ITEM –Account will come from SAE File
o Cash line with amount 25 Line Type: ITEM –Account will come from SAE File
o Amex credit card line with amount 20 Line Type: MISCELLANEOUS –Account will come from SAE File
o Amex credit card line with amount -20 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
o Amex credit card line with amount 25 Line Type: MISCELLANEOUS –Account will come from SAE File
o Amex credit card line with amount -25 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
o Citi bank credit card line with amount 20 Line Type: MISCELLANEOUS –Account will come from SAE File
o Citi bank credit card line with amount -20 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
o Citi bank credit card line with amount 25 Line Type: MISCELLANEOUS –Account will come from SAE File
o Citi bank credit card line with amount -25 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
o Citi bank credit card line with amount 30 Line Type: MISCELLANEOUS –Account will come from SAE File
o Citi bank credit card line with amount -30 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
Invoice # 1.1 Expense Report’s Credit Card Lines for the Credit Card Provider Supplier (AMEX):
EXP1.1 with amount 45 with below lines
Note: EXP1.1 will have source, as ‘CONCUR’ and invoice type will be ‘Standard’
o Amex credit card line with amount 20 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
o Amex credit card line with amount 25 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
Invoice # 1.2 Expense Report’s Credit Card Lines for the Credit Card Provider Supplier (CITI):
EXP1.2 with amount 75 with below lines
Note: EXP1.2 will have source, as ‘CONCUR’ and invoice type will be ‘Standard’
o Citi bank credit card line with amount 20 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
o Citi bank credit card line with amount 25 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
o Citi bank credit card line with amount 30 Line Type: MISCELLANEOUS –Credit card clearing account which will come from credit card supplier site
4. If we have expense report with cash and cash advance lines, then we need to create the invoices as below.
Let us say we have one expense report EXP2 with below lines
o Cash line with amount 20
o Cash Advance line with amount 25
Then we need to create one invoice as below:
Invoice # 2 Total Expense Report Lines (Cash & Advances) for the Employee Supplier:
EXP2 with amount (-5) with below lines
Note: EXP2 will have source, as ‘CONCUR’ and invoice type will be ‘Standard’
o Cash line with amount 20 Line Type: ITEM –Account will come from SAE File
o Cash advance line with amount (-25) Line Type: ITEM –Account will come from SAE File
5. If we have expense report with cash and tax lines, then we need to create the invoices as below
Let us say we have one expense report EXP3 with below lines
o Cash line with amount 20
o Tax line with amount 5
Then we need to create one invoice as below:
Invoice # 3 Total Expense Report Lines (Cash & Tax) for the Employee Supplier:
EXP3 with amount 25 with below lines
Note: EXP3 will have source, as ‘CONCUR’ and invoice type will be ‘Standard’
o Cash line with amount 20 Line Type: ITEM – Account will come from SAE file
Tax line with amount 5 Line Type: MISCELLANEOUS – Account will come from SAE file
6. If we have expense report with total lines that are less than zero, then we need to create the credit memo as below
Let us say we have one expense report EXP4 with below lines
o Credit line with amount - 20
o Tax line with amount - 5
Then we need to create one invoice as below:
Invoice # 4 Total Expense Report Lines (Cash & Advances) for the Credit Card Provider Supplier:
EXP3 with amount 25 with below lines
Note: EXP3 will have source, as ‘CONCUR’ and invoice type will be ‘CREDIT MEMO:
o Credit line with amount - 20 Line Type: ITEM – Account will come from SAE file
Tax line with amount - 5 Line Type: MISCELLANEOUS – Account will come from SAE file
Note: if the lines are credit not cash then they still need to be offsetted with the credit card clearing account and similar credit memo will be created for the credit card company.
3. Invoice and Supplier Exception Handling
1. Middlware to E-mail any errors to the interested parties of each BU.
2. Once the payload has been uploaded, it will wait for an AP person to run the import process for the Entity/Country. Errors from that import will be visible to the and allow the AP person to either perform any corrections and reprocess the import for the failed invoices or manually enter the invoice using the details displayed when reviewing the failed invoices.
3. Invoices for suppliers that could not be auto-created (duplicate supplier name, for example) will still be inserted in the invoice FBDI with supplier name and site thus fail to when the business users run the invoice invoice allowing the above corrective re-processing or manual entry.
4. All invoices are imported with payment method as ‘EFT’.
5. If supplier bank creation failed or no banking informations were found for the employee in HCM then the invoice will fail during validation due to the custom payment method validation on the ‘EFT’.
Outbound Interface from Oracle Cloud ERP to Concur
Diagram- 3: ERP Payment Status File Process Flow
Follow the below logic to Extract the expense report’s payment status and remittance advice:
Read the invoice number for those PPR in a complete status
1. Fetch the invoice numbers selected by the completed PPR.
2. Subtract any number from the invoices numbers if it is appended with (.) to handle cases where credit card expense reports. (please refer to invoice # 1.1 and # 1.2 example in this document to understand the splitting logic for credit card expense reports).
3. Fetch following fields into flat file:
A. Invoice amount payed * 100
B. Invoice_id
C. Org_id
D. check_date in YYYYMMDD format
E. vendor_name
F. Payment Method (If ‘CHECK’ then ‘C’ and if ‘EFT’ then ‘E’ and for rest use payment mentod code as is)
G. check_number
H. invoice_num
I. report_key: If invoice number is 123.3455 then report key is data before DOT (.) i.e. 123
J. If there is no DOT then use INVOICE_NUM as REPORT_KEY
K. payment_currency_code
L. amount_paid
M. invoice_amount
Assumptions for the outbound file
o Invoices only with source as ‘CONCUR’
o ATTRIBUTE10 for table ap_invoices_all SHOULD BE NULL
o For Invoices whose payment_status_flag <> ‘N’
o For the payments issued to suppliers or refunds received from suppliers the STATUS_LOOKUP_CODE <> 'VOIDED'
o The amount that invoice payments that made to suppliers should not be ZERO. Basically, for ap_invoice_payments_all the amount<>0
Exception handling for PO invoices
1. Since we can not update the base tables back with the extraction status we will use the last run date to determine the PPRs eligible for extraction.
2. PPR name would be used as a parameter name in case there was errors occurred during transmission and for which the outbound file should be generated again.
3. The boundary application does not accept duplicates which adds another layer of security in case the same expense report updated with the payment status twice.
Logic to generate AMEX remittance advice
The custom concurrent program will provide the features needed to satisfy the following basic business needs:
· To allow the client to pay the credit card provider e.g. American Express for corporate credit card charges via middleware transmission
· To allow client to provide the credit card provider detail remittance information, in their proprietary format, to accurately credit card member accounts for company paid corporate credit card charges
·
Report Sample output and mapping
The above output is broken down into different sections and pointers and understand the mapping of every number.
The fourth row is a POINTER
Pseudologic for Section A
Section A is populated in variable v_header_record.
Let’s see SECTION B
The fourth row is a POINTER
SECTION C
The fourth row is a POINTER
SECTION D
Section D
Pseudologic for Section D
Reference Documents
Following is the web service process for obtaining the Image request:
1. Invoice web service:
Service WSDL URL: https://(FADomain,FSCMServices)/fscmService/InvoiceInterfaceService?WSDL
2. Supplier web service
Service WSDL URL https://(FADomain,FSCMServices)/fscmService/SupplierServiceV2?WSDL
Conclusion
Integrate Oracle Cloud with your expense third party application with approach that offers dynamic options to comply with your reimbursement requirements.
Integration Key Features
1. SaaS invoicing software solution
2. Takes in new invoices via file import.
3. Issues payments to employees and vendors via direct deposit or check
4. Accounting for your employee expenses and their tax and project related expenses in one consolidated system.
if you have any questions please contact me sameh11i@yahoo.com