Oracle Openworld Directory Index

Who am I ?

Who will benefit ?

Oracle Project Accounting Support/Developers
Oracle Payables Support/Developers
PA and AP Functional analysts
End-users and ...
Of course all of you who are here and watching
Key Items for each integration point
Data flow during each process and tables involved
Transaction flow between PA and AP
Concurrent Manager Processes
Various problems encountered and error messages
Troubleshooting/Bug Info
Tips to overcome typical error messages
New features in 11i and set-up parameters
Entering Expense Reports in 11i
Entered in Oracle Projects through pre-approved batches window
Entered in Oracle Self -Service Expenses (Web Expenses/Internet Expenses) and imported into Oracle Payables and then interfaced to Oracle Projects.
Entered directly in Oracle 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)
Setting Up in Payables and Oracle Projects

Select 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)
PA: 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 )
Submitting the streamline Processes
Streamline processes submit two or more processes in one step. There are several streamline options available:
DXES, DXEU, XES, XEU etc. Some processes use a lot of system resources (DXES, DXEU etc.).
In Collective, We run the PRC: Distribute Expense Report Costs separately and then use XES – Interface Expense Report Costs to AP (Summarized) streamline option 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.

PA ? AP Integration Processes Time to have some fun !!!
We have a web-based Custom Expense application (fully integrated with Oracle Projects) using which all the Collective 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). The following info is transferred at the web interface during the expense download: Employee_number, expenditure_item_date, project_number, task_number, expenditure_type, quantity, transaction_status_code and expenditure_ending_date.
Ooops … some more to say
Once the expense information is transferred into the Oracle Projects Interface, the following concurrent processes are invoked in order to complete the transfer into Oracle Payables in the same sequential order as shown below (either using Streamline process or invoking them individually
PRC: Transaction Import
PRC: Distribute Expense Report Costs
PRC: Interface Expense Reports to Payables
AP: Payables Invoice Import
PRC: Tieback Expense Reports from Payables.
Okay, What do we do now ???

We are now going to look at the functionality of the
various concurrent processes and peek at the
problems faced by us (Collective) and …hold your
breath…tips to overcome those “Driving me crazy”
error messages and exceptions.
PRC: Transaction Import

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

Data Flow from PA interface to PA




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.
(( The exception report will provide rejection reasons for all rejected items. While only the items that are rejected appear on the exception report, Transaction Import process rejects the entire expenditure and updates all the items in that expenditure with the status of rejected (sets Transaction_status_code =’R’ in Pa_transaction_interface_all table). You must correct the rejected transactions and then re-run the process ))
From version 11 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.

11i --> like a breath of fresh air …
Limitations of Transaction Import Process in previous versions
Until 11i Transaction Import process can only be used for importing the Expense reports and can not be used for loading supplier Invoices. We could enter supplier Invoices only from Oracle Payables and then interface the supplier Invoices from Oracle Payables to Oracle Projects using the Interface Supplier Invoices from Payables process provided by Oracle Projects.
But from 11i onwards, Supplier Invoices from Oracle Payables can also be interfaced via the 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.



11i --> It’s like dancing in the rain ...
Enhancements in Transaction Import Process in 11i
Transaction Import process now supports
(1) Multi-Currency Transactions (2) Supplier Invoices.
We can now import the following columns for transactions, 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 Extensions
Expenditure Item Descriptive Flexfield validations
Validation of Account Codes Provided for Accounted Transactions
Adjusting or Reversing Imported Transactions in Oracle Projects (depending on Transaction Source option)
Allow Adjustments option for accounted transactions



As promised, here are those “Driving me crazy” exceptions
Rejection Code : PA_EX_PROJECT_CLOSED
Description: This happens when the expenditure is assigned to a project which is no longer active.
Solution: Assign the expenses to a different Project .

Rejection Code : PA_EX_PROJECT_DATE
Description: This happens when the expenditure Item date is not within the active dates of the project.
Solution: 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_DATE
Description: This happens when the expenditure item date is after expenditure ending dates.
Solution: Change the expenditure_item_date or the expenditure_ending_date.





Some more goodies ...
Rejection Code: INVALID_END_DATE
Description: The value for the expenditure ending date is not a valid week ending date.
Solution: Change the expenditure_ending_date to a valid week-ending date.

Rejection Code: INVALID_PROJECT
Description: No Project exists with project number specified.
Solution: Check to make sure if the project exists.

Rejection Code: INVALID_EMPLOYEE
Description: No employee exists with the employee number specified.
Solution: Check to make sure if the employee info exists in the projects.

Rejection Code: PA_EXP_TYPE_INACTIVE
Description: The expenditure item falls outside the effective dates of the expenditure type.
Solution: Change the expenditure item date, expenditure type, or expenditure types.

Is it christmas already ...
Rejection Code: PA_EXP_TASK_TC
Description: The transaction violates an expenditure control at the task level. If the transaction
control has overlapping assignments within this expenditure_item_date/expenditure_ending_date
the transaction gets rejected with the above code.
Solution: Make sure there are no overlaps in the pa_transaction_controls table for this task_id and
project_id.

Rejection Code: PA_EXP_TASK_EFF
Description: Expenditure item date is not within the active dates of the task.
Solution: Change the expense report task number to reflect the active task number.

Rejection Code: NO_ASSIGNMENT
Description: The employee doesn’t have a valid assigment.
Solution: This was one of the issues with our Import. Even though the assignment information existed in
all the HR tables, the assignment wouldn’t show up in the Applications. We kept getting the rejection code
‘NO ASSIGNMENT even after updating the member assignment in the forms. After doing some research
on this issue, we figured out that ………………..
This is like “ Buy 1 get 2 free “
The min(effective_start_date) for each employee in --> per_all_assignments_f (per_assigments_f inv10.7)
Should match ----->
effective_start_date in per_all_people_f (per_people_f in V 10.7) table and date_start column in
per_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 any other expenditure type classes (other than Expense Reports) do not require an employee number.


PRC : Distribute Expense Costs …Oh Yeah
Costing = 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 costing
When this distribute process runs, it invokes the
Auto 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.

If what I said sounded like Greek ...
Distribution of cost data in PA
Don’t tell this to your boss ...
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 Rule
Description: A GL account could not be determined for the cost distribution line, due to incomplete Auto Accounting setup
Solution: 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_DATE
Description: Project Accounting Period is not open
Solution: Make sure your PA Period is open.
PRC: Interface Expense Reports to Payables
PATTER process collects eligible Cost distributed Expense reports and sends them to Payables Interface tables
Invokes 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 the earliest 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.
Interfacing Expense data from PA to AP




Bugs and Exceptions?! Here you go
1. 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
correctly

Open 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. I
i) 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_all
where 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_all
where transfer_status_code like ‘T’;

If no rows are retrieved here also, then this fix may not help.
Investigation Step No. II

If 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_all
where vouchno=0
and 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_all
where vouchno=0
and 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.

AP: Payables Invoice Import
creates invoices and invoice distribution lines (ap_invoices_all and ap_invoice_distributions_all) from Payables Interface tables (ap_expense_report_headers_all and 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.

Invoice Import Process at a glance

Typical Exceptions During Invoice Import
1. Rejection Code: Error Code: Addr Line [1-3] Too Large:
Description: Invoice Import attempted to create a supplier from an employee and the address line for the employee home address exceeds 35 characters. This happens all the time because, In PA module, (per_addresses table) the data type for address_line[1-3] have a data type of varchar2(60) and in AP (po_vendor_sites table) the data type for address_line[1-3] have a data type of varchar2(35). So anytime, the address_line[1-3] exceeds 35 characters, we will get this error.
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.
Some more of those...
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 Time Check 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.

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_all table.


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 Costs
PA: 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.
Epilogue

And so with a closing thought that although tremendous advances have 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

Teresa Myers -- Collective Technologies,
Ram Ganapathy – Dell Computer Corporation



Fill Out a Survey and Get a Chance to Win a Compaq iPAQ!

We want to know what you think! Fill out the survey that was handed out at the beginning of the session for a chance to win a Compaq iPAQ. Remember to include your name and email in the available section and we will enter your name into two daily drawings to win an iPAQ