Dude! You are getting Paid!-----------------------------------------

Integrating Payables & Project Expenses in 11i

Applications Integration

Lakshmi Sampath, Stone Blue Technologies

Lakshmi SampathPresidential Ballroom D

12:15 pm – 1:15 pmThursday

Presenter Qualifications• Principal Consultant at Stone Blue Technologies. • Expertise in implementing EBusiness Suite 11i –

including IStore, ISupplier, CRM, OM, Fin & HR• Presented several Papers and hosted Panels at

the OAUG, Oracle Open World, Austin Oracle Users Group etc.

• ENCORE Presenter @ COLLABORATE-2006• Board Member of Austin Oracle Users Group

Agenda for today’s presentation• Key Items for each integration point• Entering Expense Reports in 11i • Setting Up in Payables and Oracle Projects• Submitting the Streamline Processes • PA AP Integration Processes • Tables involved during the integration

processes along with Typical Error Messages during each process

• Troubleshooting tips & Bug Information• New features in 11i

Who will benefit ?

Oracle PA/AP Support/Developers Oracle Payables Support/Developers PA and AP Functional analysts End-users and ... Of course all of you who are here and watching

1. Key Items for each integration pointData flow during each process and tables involvedTransaction flow between PA and APConcurrent Manager ProcessesVarious problems encountered and error

messagesTroubleshooting/Bug InfoTips to overcome typical error messagesNew features in 11i and set-up parameters

2. Entering Expense Reports in 11iOracle Projects through pre-approved batches windowOracle Self -Service Expenses (Web Expenses/Internet Expenses) and imported into AP and then interfaced to PAOracle Payables using the Invoice and the Distributions window in AP and then interfaced into Oracle Projects.Imported into Oracle Projects from an external source and then processed through the Transaction Import Process. No longer supported in 11i are Oracle Project Time and Expense and Oracle Personal Time and Expense (PTE)

3. Setting Up in PayablesSelect the Expense Reimbursement Address as Office/Home (AP, Setup->Financials->HR)Automatically Create Employee as Supplier(AP, Setup->Options->Payables->Expense Report)Accept or override the employee address(AP, Setup->Options->Payment->Allow Address Change)Define/Determine the Expense Report Cost Account (Auto accounting rules)Define/Determine the Expense Report Liability Account (Auto accounting rules)

3. Setting up in Oracle ProjectsPA: Summarize Expense Report lines

(SYSADMIN: Profile>System>Value–Application level)PA: Allow Override of PA Distributions in AP/PO (Site, Application, Responsibility and User)PA: Auto approve Expense Reports (Application level)PA: Default Expenditure Organization in AP/PO (Site Level)Invoice Batch Source = ”PA Invoices”(PA Setup/System/Implementation Option /Billing )

4. Submitting the streamline ProcessesSubmit two or more processes in one step. Options Available: DXES, DXEU, XES, XEU etc. Some processes use a lot of system resources (DXES, DXEU etc.).

We run theI) PRC: Distribute Expense Report Costs separately II) then use XES – Interface Expense Report Costs to AP

(Summarized) to submit PRC: Submit Interface Streamline process to complete the rest of the processes.

We may use any combination of streamline processes or perform individual functions to invoke these interface processes. But they must be invoked in the correct order for them to run successfully. It is always advisable to use Streamline Process instead of individual processes.

5. PA AP Integration ProcessesTime to have some fun !!!

We have a Web-based Custom Expense application (fully integrated with Oracle Projects) using which all the employees fill up their expense reports online.

A Concurrent Program (SQL Script) is set up in Oracle Projects to execute weekly to transfer the ‘APPROVED’ expense reports from the web interface into the Project Accounting interface table (Pa_transaction_interface_all).

Following Information transferred during the web download: Employee_number, expenditure_item_date, project_number, task_number, expenditure_type, quantity, transaction_status_codeand expenditure_ending_date.

5. Ooops … some more to sayFollowing Processes must be invoked to complete the

Expense Reports IntegrationPRC: Transaction ImportPRC: Distribute Expense Report Costs PRC: Interface Expense Reports to Payables AP: Payables Invoice Import

PRC: Tieback Expense Reports from Payables.

a) PRC: Transaction ImportTransaction import loads transactions as pre-approved expenditure

items and expenditure batches are created as having a status of ‘Released’. Data Transfer occurs from

pa_transaction_interface_all(Project Interface Table)

pa_expenditure_batches_all, pa_expenditures_all,pa_expenditure_items_all

( Project Accounting Tables )

6. Tables Involved during the Integration Process

PA_TRANSACTION_INTERFACE_ALLPA_TRANSACTION_SOURCES PA_TRANSACTION_XFACE_CTRL_ALL

PA_EXPENDITURE_GROUPS_ALL

PA_EXPENDITURE_BATCHES

PA_EXPENDITURES_ALL

PA_EXPENDITURE_ITEMS_ALL

Data Flow from PA interface to PA

6 a) Tables Involved during PRC Transaction Import

Just a little more info ...

Transaction Import process generates two reports:(1) An exception report listing all rejected transactions(2) A summary report showing the successfully imported transactions.

From version 11i onwards, a new form called review transactions has been added in Oracle Projects. (Navigation is: Expenditures -> Transaction Import -> Review Transactions). This form is extremely useful in expediting minor additions to the expenditure batches. It can also be used to correct rejected transactions in the interface table using this form.

Limitations of Transaction Import Process

Transaction Import detects only one error per transaction each time you run the import process. If a single transaction has multiple errors, you will need to run Transaction Import more than once to discover all the errors.

Enhancements of Transaction Import Process in 11i(1) Multi-Currency Transactions (2) Supplier Invoices. The following columns for transactions can be imported, depending on the options we specify for the Transaction Source:(1) Employee Organization Override(2) User-Specified Billable flag(3) User-Specified Expenditure Reference(4) User-Specified Original Transaction Reference (for adjusting transactions)

Pre- and Post-Processing ExtensionsExpenditure Item Descriptive Flexfield validationsValidation of Account Codes Provided for Accounted TransactionsAdjusting or Reversing Imported Transactions in Oracle Projects (depending on Transaction Source option)Allow Adjustments option for accounted transactions

Rejection Code : PA_EX_PROJECT_CLOSEDSolution: Assign the expenses to a different Project .

Rejection Code : PA_EX_PROJECT_DATESolution: Assign the expenses to a different Project which was active during

the item date or Change the project start date to an earlier date.

Rejection Code: EI_DATE_AFTER_END_DATESolution: Change the expenditure_item_date or the

expenditure_ending_date.

As promised, here are those “Driving me crazy” exceptions

Some more goodies ...Rejection Code: INVALID_END_DATESolution: Change the expenditure_ending_date to a valid week-ending date.

Rejection Code: INVALID_PROJECTSolution: Check to make sure if the project exists.

Rejection Code: INVALID_EMPLOYEESolution: Check to make sure if the employee info exists in the projects.

Rejection Code: PA_EXP_TYPE_INACTIVESolution: Change the expenditure item date, expenditure type, or expenditure types.

Some More Exceptions

Rejection Code: PA_EXP_TASK_TCSolution: Make sure there are no overlaps in the pa_transaction_controls table for this

task_id and project_id within the expenditure item date/ending_date

Rejection Code: PA_EXP_TASK_EFFSolution: Change the expense report task number to reflect the active task number.

Rejection Code: NO_ASSIGNMENTSolution: This was one of the issues with our Import. Explanation

This is like “ Buy 1 get 2 free “The min(effective_start_date) for each employee in --> per_all_assignments_fShould match ----->effective_start_date in per_all_people_f table and date_start column inper_periods_of_service table.

Rejection Code: NO_MATCHING_ITEM Description: No eligible expenditure item exists in PA for this adjustment.Solution: If the transaction is an adjustment with a negative quantity, and the

unmatched negative flag is not set to Yes, an original, approved, un reversed expenditure item matching the transaction’s employee/organization, item date, expenditure type, project, task, reversing quantity, reversing cost and non-labor resource and non-labor organization must exist. Also, the matching expenditure item must have been originally loaded from the same transaction source.

Note: If the employee number is specified, transaction import ignores any value for the organization and derives the organization value based on the employee’s assignment. Transactions with anher expenditure type classes (other than Expense Reports) do not require an employee number

6(b) PRC : Distribute Expense CostsCosting = Calculate the Expenditure and Assign to Project

PRC: Distribute Expense Report Pre-requisite for the generation of revenue and Invoices for expense report expenditure items.

Auto-accounting - Critical piece of project costingWhen this distribute process runs, it invokes theAuto accounting function ‘Expense Report Cost Account’and populates the respective Debit CCID (dr_code_combination_id

column) in the Pa_cost_distribution_lines_all table (cdl).

How PA Date is determined?PA Date is determined based on the first open or future

period that is on or after the expenditure_item_date.

6 (b) Distribution of cost data in PA

PA_EXPENDITURES_ALL

PA_EXPENDITURE_ITEMS_ALL PA_EXPENDITURE_BATCHES

PA_COST_DISTRIBUTION_LINES_ALL

Common Exceptions Encountered

1. Rejection Code: KEY_FLEX_FAIL-Invalid Accounting Flexfield and AA_NULL_CCID- Auto Accounting Error: null ccid

Description: The GL account returned by Auto Accounting does not pass validation rules defined for the gl account and the code combination id derived by AutoAccounting is invalid respectively.Solution: Check to see if the project code, task code and Organization_id are correct. Find out which gl code the above combination will hit. Make sure you haven’t excluded this particular GL code in the Cross-validation rules and make sure dynamic inserts are turned on.

2. Rejection Code: AA_FAIL - Incomplete Autoaccounting RuleDescription: A GL account could not be determined for the cost distribution line, due to

incomplete Auto Accounting setupSolution: Check to see if the Project_code, task_code, incurred_by_organization_id are correct. Everything should be in sync in order to avoid this.

3. Rejection Code: NO_PA_DATEDescription: Project Accounting Period is not openSolution: Make sure your PA Period is open.

6(c) PRC: Interface Expense Reports to Payables

PATTER process collects eligible Cost distributed Expense reports and sends them to Payables Interface tablesInvokes the auto accounting function ‘Expense Report Liability Account’ ( populates cr_code_combination_id in pa_cost_distribution_lines_all and accts_pay_code_combination_id in ap_expense_report_headers_all)

PA Auto accounting rule takes precedence over the employee’s default expense account when determining the invoice liability account (and also it does not invoke Account Generator/ Flexbuilder).

How is GL Date determined?In Oracle Projects, the GL Date for costs is the end date of theearliest open or future GL period that is on or after the latest PA Date of the cost distribution lines included in an expense report. All cost distribution lines for an expense report are sent together to Payables and use the same GL date which becomes the GL date of the invoice in payables. Note:If expense reports from any source fail to post to Payables, we may need to redistribute costs (using the PRC: Distribute Expense Report Costs process)Typically, Auto-Accounting rules require occasional updates. Transactions may fail during distribution and interfacing if the Auto-accounting rules are incomplete.

6 (c) Interfacing Expense data from PA to AP

PA_COST_DISTRIBUTION_LINES_ALL

AP_EXPENSE_REPORT_HEADERS_ALL AP_EXPENSE_REPORT_LINES_ALL

Bugs and Exceptions?! Here you go1. Problem: The process does not pick up expenditure items with

transfer_status_code=’P’ (pending). No error messages appear.Solution: Set Automatic Release to ‘YES’ for PRC: Distribute

Expense Report Costs. (Note: 1068250.6, 1060896.6)

2. Problem: After running the PRC: Submit Interface Streamline Processes with Streamline Option: DXEU, the process completes with a status of “Error” and Rejection Reason – No Open Project Accounting Period or Could not create a valid GL Date.

Solution: Verify all the periods are open in PA, AP and GL. (Note: 1080222.6)

3. Problem: After running the PRC: Submit Interface Streamline Processes either individually or as a streamline, you get an error ‘Combination Missing’

Solution?!Check if Auto accounting rules are setup correctlyOpen a TAR with Oracle

and then research the expense data based on the following:=)

Remember, the following information is provided for research purposes only.

Do not modify the data using SQL PLUS. Oracle will not support it.

Investigation Step No. Ii) select * from pa_cost_distribution_lines_all

where transfer_status_code like ‘X’and transfer_rejection_reason like ‘Missing data on record to be transferred%’and cr_code_combination_id is null;

or

select * from pa_cost_distribution_lines_allwhere expenditure_item_id in (select expenditure_item_id

from pa_expenditure_items_all where cost_distributed_flag like ‘S’)

*** If zero rows are returned, then do the following query. Otherwise go to ii)

select * from pa_cost_distribution_lines_allwhere transfer_status_code like ‘T’;

If no rows are retrieved here also, then this fix may not help.

Investigation Step No. IIIf a few rows are retrieved, then you can assume that PRC: Interface Expense Reports

to Payables was successful and there were some problems with the next two processes.

ii) select * from ap_expense_report_headers_allwhere vouchno=0and accts_pay_code_combination_id is null;

*** get the report_header_id for the retrieved rows If no rows are retrieved, then this error may be due to some other reason..iii) select * from ap_expense_report_lines_all

where report_header_id=&report_header_id;

If you have retrieved at least one row for i) and ii) and none for iii) then following may be the fix for your problem.

Here you go…..Solution :

select count(*) from ap_expense_report_headers_allwhere vouchno=0and accts_pay_code_combination_id is null;

If you get the count in multiples of 100, then this is due to bug #1551379

(Ref# TAR-13305123.6)You have to delete one expense report and process the rest of the expense

reports first and then process one expense report separately. This will resolve the issue.

This issue is finally resolved in 1701594 for versions 10.7, 11.0 and 11.5.

6 (d) AP: Payables Invoice Importcreates invoices and invoice distribution lines (ap_invoices_all and ap_invoice_distributions_all) from Payables Interface tables (ap_expense_report_headers_alland ap_expense_report_lines_all).

If you do not enable Automatically create Employee as Supplier, you must manually enter the employee as a supplier before submitting Payables Invoice Import.

6(d)Invoice Import Process at a glance

PA_COST_DISTRIBUTION_LINES_ALL

AP_EXPENSE_REPORT_HEADERS_ALL AP_EXPENSE_REPORT_LINES_ALL

Typical Exceptions During Invoice Import

1. Rejection Code: Error Code: AddrLine [1-3] TooLarge:

Solution: Login to PA module, go to Setup, HR, Employees, Query the employee for whom you have this error, then click on More, Addresses and change the address line [1-3] and reduce the character length to 35 and do the import. It should work fine.

2. Rejection Code: Error Code: Create Duplicate Supplier

Description: This may be due to any of the following reasons:

i) When you try to create an expense report for an employee name that is not already a supplier and a supplier name already exists for the same employee .

ii) Login to AP module, go to Suppliers screen and query the employee name and under Classification see if you have checked One TimeCheck box.

Solution: If i) is true, you know what to do.

If ii) is true then Remove the One Time Checkbox.

Here again...3. Rejection Code: Error Code: Create EFT Site

Description: The Financials Option for payment method is Electronic. The employee exists as a supplier in Oracle Payables, but is missing bank info in Supplier site.Solution: Enter the necessary bank information in the supplier site.

4. Rejection Code: Error code: Create EDI Site Description: This is a Setup Problem. When we setup Supplier sites in Payables, the site name should be either ‘OFFICE’ or ‘HOME’. If not, then we will get this exception.

Solution: Create a new site called ‘HOME’ or ‘OFFICE’ for the supplier.

6 (e) PRC: Tieback Expense Reports from Payables

Identifies Expense reports rejected by Payables Invoice Import

Reconciles Expense reports in Oracle Projects and the related invoices in payables

updates the purgeable_flag from ‘No’ to ‘Yes’ for each expense report in the ap_expense_report_headers_alltable

6 (f) Adjusting Project Related Expense Reports

You can adjust an expense report in Oracle Projects at any time, but you cannot interface adjustments to Payables until an invoice exists in Payables and you have run the tieback process.

In order to process expense report adjustments, the following processes must be invoked:

PA: Distribute Supplier Invoice Adjustment CostsPA: Interface Supplier Invoice Adjustment Costs to Payables

There is no need to run Payables Invoice Import for adjustments to expense reports already interfaced or tied back from Payables.

EpilogueAnd so with a closing thought that although tremendous advanceshave and are being made in this subject area to handle the processes and tackle all arising problems, this paper strives to be a small step in that direction and hopes to be a part of that tide in bringing to shore a trove of vital information.

Acknowledgements

• Ram Ganapathy – Dell Computer Corporation

Q & A

Thank You!

Email Addresslakshmi@stonebluetech.com