Please note the below flow is for 11i

PA – AP Flow:

Here iam considering the AP invoice integration with Oracle Projects. I will explain about the Expense Reports flow in another topic.

This integration involves 2 sub processes given below:

  • Interfacing Supplier Invoice From Oracle Payables To Oracle Projects
  • Interfacing Supplier Invoice Adjustments From Oracle Projects To Oracle Payables

Interfacing Supplier Invoice From Oracle Payables To Oracle Projects:

This process involves :

  1. Creating the invoice.
  2. Approve the invoice.
  3. Account for the invoice in payables.
  4. Run the ‘Interface Supplier Costs’ process in Oracle Projects which interfaces the invoices from AP to PA.

Interfacing Supplier Invoice Adjustments From Oracle Projects To Oracle Payables:

This process involves:

  1. Adjust the supplier invoice interfaced to Oracle Projects by either splitting the quantity or transferring an invoice from one project/task to another.
  2. Execute the PRC: Distribute Supplier Invoice Adjustment Costs process in Oracle Projects.
  3. Run the PRC: Interface Supplier Invoice Adjustment Costs to Payables process in Oracle Projects.

Important Project Related Fields in AP:

  • Project Name – Project Name to which the Invoice is accounted in Payables.
  • Task Number – Task Number of the Project to which the invoice is accounted. Note: If the Task is not chargeable, the system displays the following error message: APP-PA-19270 The Task is Not Chargeable. The same error will be received if the Chargeable Flag is not checked while defining Tasks in Projects Setup or if the task is a Parent Task. Expenditures cannot be created at the Parent Task level.
  • Expenditure Types – Expenditure Type of the invoice. This is based on the projects expenditure types (pa_expenditure_types table)
  • Expenditure Item Date – The date of the invoice expenditure item to be created in Projects.
  • Expenditure Organization – Active Project Expenditure/Event Organization against which the invoice has to be mapped.
    The Expenditure Org can be find in PA_ALL_Organizations table with pa_org_use_type = ‘EXPENDITURES’. For any organization to be a Expenditure Organization, it has to be classified in HR as ‘Expenditure/Event Organization’.
  • Quantity – Based on the Expenditure Type definition, quantity is verified by checking the PA_QUANTITY column in the AP_INVOICE_DISTRIBUTIONS_ALL. If the COST_RATE_FLAG column in PA_EXPENDITURE_TYPES table is set to Y, then the quantity field in the Payables Invoice Workbench needs to be filled in.

Once all the requisite information has been entered and the invoice distribution saved, the system checks whether the values given in the Project, Task, Expenditure Type and Expenditure Organization fields are active as of the Expenditure Item Date.

The following columns in the AP_INVOICE_DISTRIBUTIONS_ALL table are relevant for project-related supplier invoices:

  1. PROJECT_ACCOUNTING_CONTEXT – This column is set to Yes if the Project ID column is filled.
    1. ASSET_ADDITION_FLAG – If the project-related invoice distribution is charged to a Capital Project, then the ASSET_ADDITION_FLAG is set to P when the PA_ADDITION_FLAG is set to Y, Z or T.

To avoid the same invoice distribution being interfaced to both Projects and Fixed Assets, you must interface any project-related invoice distribution to Oracle Projects before you interface it to Oracle Assets.

  1. PA_ADDITION_FLAG- The PA_ADDITION_FLAG tracks the status of project-related supplier invoice distribution lines and expense report distribution lines. For supplier invoice distributions entered via Oracle Payables, the PA_ADDITION_FLAG is set to N if the distribution is project-related, otherwise it is set to E and it is updated by Oracle Projects when the distribution is processed by the Oracle Projects Interface Supplier Invoice process. Oracle Projects sets the PA_ADDITION_FLAG to Y or Z after the item is successfully processed, or may be set to a rejection code if the line is rejected during transfer to Projects. For supplier invoice adjustment lines interfaced from Projects to Payables (which must net to zero with another line), the value for the PA_ADDITION_FLAG is set to T. Listed below are the Quick Codes available for the PA_ADDITION_FLAG:
    PA_ADDITION_FLAG DESCRIPTION
    B No open PA period
    C Task does not allow charges
    D Outside project dates
    E Non-project related invoice distributions
    I Outside task dates
    J Project level transaction controls violated
    K Task level transaction controls violated
    M Invalid project/task combination
    N New line not yet processed by Oracle Projects
    P Project is closed
    Q Transaction control extension violated
    S Temporary status used during processing
    T Adjustment line transferred from Oracle Projects
    V Invalid data (catch-all error)
    X Burdening error
    Y Transferred to Oracle Projects
    Z Net zero adjustment line. Never transferred to PA

     

    If an item is rejected, you must correct the rejection reason and re-run the interface process.

    Once the Invoice is interfaced to Projects, the following tables are populated with appropriate values:

    • PA_EXPENDITURE_GROUPS_ALL
    • PA_EXPEDITURES_ALL
    • PA_EXPENDITURE_ITEMS_ALL
    • PA_COST_DISTRIBUTION_LINES_ALL

The interrelationship between the relevant Payables and Projects tables are as follows:

Release 11i
AP_INVOICES_ALL PA_EXPENDITURE_ITEMS_ALL PA_COST_DISTRIBUTION_LINES_ALL
INVOICE_NUM ORIG_USER_EXP_TXN_REFERENCE
INVOICE_ID ORIG_EXP_TXN_REFERENCE1 SYSTEM_REFERENCE2
VENDOR_ID VENDOR_ID SYSTEM_REFERENCE1
ORG_ID ORG_ID ORG_ID
AP_INVOICE_DISTRIBUTIONS_ALL PA_EXPENDITURE_ITEMS_ALL PA_COST_DISTRIBUTION_LINES_ALL
DISTRIBUTION_LINE_NUMBER SYSTEM_REFERENCE3
DIST_CODE_COMBINATION_ID DR_CODE_COMBINATION_ID
INVOICE_ID SYSTEM_REFERENCE2
EXPENDITURE_TYPE EXPENDITURE_TYPE
EXPENDITURE_ORGANIZATION_ID EXPENDITURE_ORGANIZATION_ID
PROJECT_ID PROJECT_ID PROJECT_ID
TASK_ID TASK_ID TASK_ID
EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE GL_DATE
ORG_ID ORG_ID ORG_ID
PA_QUANTITY QUANTITY QUANTITY
AMOUNT RAW_COST AMOUNT
Release 12
==============
The following columns were added in R12 to table PA_EXPENDITURE_ITEMS_ALL with the purpose of linking data between PA and AP/PO:DOCUMENT_HEADER_ID:
This column refers to a header-level record identifier in another application. If the expenditure item originated in Oracle Payables, it holds the AP_INVOICES_ALL.INVOICE_ID. If the expenditure item originated in Oracle Purchasing, it holds the PO_HEADERS_ALL.PO_HEADER_ID. This column is not used for labor and usage expenditure items. 

DOCUMENT_DISTRIBUTION_ID :
This column refers to a distribution-level record identifier in another application. If the expenditure item originated in Oracle Payables, it holds the AP_INVOICE_DISTRIBUTIONS_ALL.INVOICE_DISTRIBUTION_ID. If the expenditure item originated in Oracle Purchasing, it holds the RCV_TRANSACTIONS.TRANSACTION_ID. This column is not used for labor and usage expenditure items.

DOCUMENT_LINE_NUMBER:
This column refers to a line-level record identifier in another application. If the expenditure item originated in Oracle Payables, it holds the AP_INVOICE_LINES_ALL.LINE_NUMBER. If the expenditure item originated in Oracle Purchasing, it holds the PO_DISTRIBUTIONS_ALL.PO_DISTRIBUTION_ID. This column is not used for labor and usage expenditure items.

DOCUMENT_PAYMENT_ID:
This column refers to the payment record identifier in Oracle Payables. It holds the AP_PAYMENT_DISTRIBUTIONS_ALL.PAYMENT_ID. This column is only populated when you enable cash basis accounting or, for payment discounts, when you use accrual basis accounting. This column is not used for labor and usage expenditure items.

VENDOR_ID:
This column indicates the vendor identifier for supplier cost transactions. If the expenditure item originated in Oracle Payables, it holds the AP_INVOICES_ALL.VENDOR_ID. If the expenditure item originated in Oracle Purchasing, it holds the PO_HEADERS_ALL.VENDOR_ID. This column is not used for labor and usage expenditure items.

DOCUMENT_TYPE:
The supplier cost interface process uses this column to indicate the transaction type. Valid values are as follows: Payables, Purchasing, or Receiving.

DOCUMENT_DISTRIBUTION_TYPE:
The supplier cost interface process uses this column to indicate the transaction type of the distribution. Valid values are as follows: Payables, Purchasing, or Receiving.

The value in SYSTEM_REFERENCE columns depends on where the expenditure is coming from.
Usually the reference columns in PA_COST_DISTRIBUTION_LINES_ALL table are populated for Supplier costs – Invoices or Receipts.

In case of Supplier invoice:
SYSTEM_REFERENCE2 holds invoice id
SYSTEM_REFERENCE3 holds invoice line number
SYSTEM_REFERENCE4 holds invoice_payment_id from table ap_payment_hist_dists (for discount lines)
holds variance type or tax type (like TRV, ERV, NONREC_TAX)
SYSTEM_REFERENCE5 holds PAYMENT_HIST_DIST_ID  from table ap_payment_hist_dists (for discount lines)

If it is Receipt Accrual:
SYSTEM_REFERENCE2 holds PO_HEADER_ID
SYSTEM_REFERENCE3 holds PO_DISTRIBUTION_ID
SYSTEM_REFERENCE4 holds RCV_TRANSACTION_ID from rcv_receiving_sub_ledger
SYSTEM_REFERENCE5 holds RCV_SUB_LEDGER_ID from rcv_receiving_sub_ledger

It is possible to map between the TRANSFER_STATUS_CODE column in the PA_COST_DISTRIBUTION_LINES_ALL table and the PA_ADDITION_FLAG column in the AP_INVOICE_DISTRIBUTIONS_ALL table. TRANSFER_STATUS_CODEs available in the PA_COST_DISTRIBUTION_LINES_ALL table for supplier invoices are:

    • V- Interface from Oracle Payables – Upon creation set to Received
    • P – Pending – Upon execution of Distribute Cost Process after Supplier Invoice Adjustments
    • G – If payables rules do not allow adjustments to the invoice (example: if the invoice is cancelled), then distribute supplier invoice adjustments program would create the CDL and set the value to (G), these lines will always reside in Oracle Projects and will not be transferred to Oracle Payables
    • A – Transfer Costs – If successfully transferred to Oracle Payables, set to Accepted
    • X – Rejected in transfer to Oracle Payables, set to Rejected in Transfer

Comments

  1. Andy So

    Hi and Thanks for this great documentation. I have a question though. Does this apply to R12 R11.

    In table above linking tables PA_COST_DISTRIBUTION_LINES_ALL and AP_INVOICE_DISTRIBUTIONS_ALL.

    I fear that Project’s SYSTEM_REFERENCE3 does not to AP’s DISTRIBUTION_LINE_NUMBER. I believe system reference 3 is now linked to Line_number in AP_Invoice_Lines_all. This is new R12 AP table.
    If what I fear is correct, what would be the direct line-to-line link of Projects to AP?
    Again, thanks for this wonderful article.

Leave a Reply

Your email address will not be published. Required fields are marked *


8 + = sixteen

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

:) ;) :D ;D >:( :( :o More smilies »