Integration of Oracle Payables (AP) with Other ERP Modules

Accounts payable (AP) encompasses all the transactions related to money that a person or company owes to suppliers, but has not been paid yet.  From a functional perspective, the invoice is used to register the transactions between debtors and suppliers, and, from a technical-design perspective, the invoice is the common entity between Oracle Accounts Payables with other modules.  Thus, the analysis of the interconnection between the Oracle tables containing information about invoices with other modules of the financial suite along with troubleshooting hints is the main purpose of this paper. Assets, Projects, Purchasing, Property Manager, Grants Accounting, and Inventory are the modules included in this review.

Oracle Assets Integration

The Create Mass Additions program (APMACR) transfers invoice distribution to Oracle Assets (FA) as long as the “track as asset check box” is enabled for the item line being processed.  The screen listed below shows the “Track As Asset” checkbox.

Mass Additions Create Flow in R12

In R12, Accounts Payable does not validate the clearing accounts set up in FA while inserting into the FA_MASS_ADDITIONS_GT.

AP calls the Assets validation API, which validates the invoice distribution lines.  The validation will check whether the lines meet the criteria to become mass addition records according to the setups in FA.

The process can be summarized as follows:

1.      The AP_MASS_ADDITIONS_CREATE_PGK (apmassab.pls) is executed when the Mass Additions Create Process is run.

2.      AP_MASS_ADDITIONS_CREATE_PGK selects the invoice distribution records according to the GL date and book type code parameters entered when the process is launched.  Furthermore, the invoice distributions to be selected must meet the following criteria:

a.      ap_invoice_distributions_all.assets_addition_flag = U

b.      ap_invoice_distributions_all.assets_tracking_flag = Y

c.       ap_invoice_distributions_all.posted_flag = Y

The selected distributions will be inserted into the FA_MASS_ADDITIONS_GT temporary table.

3.      The FA_MASSADD_CREATE_PKG.create_lines procedure is executed once the FA_MASS_ADDITIONS_GT temporary table is populated.

4.      FA_MASSADD_CREATE_PKG.create_lines procedure will populate the FA_MASS_ADDITIONS_GT table.  The package is also responsible for marking the lines as ‘Processed’ or ‘Rejected’.

5.      Control is transferred back to AP_MASS_ADDITIONS_CREATE_PGK, which will set ap_invoice_distributions_all.assets_addition_flag = Y if the respective record in the FA_MASS_ADDITIONS_GT table was ‘Processed’.  On the contrary, if record is rejected, then ap_invoice_distributions_all.assets_addition_flag = N.

Mass Additions Create Program Parameters

Two parameters are required to run the Mass Additions Create Program, which are listed as follows:

1.      The asset book type code

2.      Account date

AP will transfer to FA all the distribution lines with an accounting date lower than the Accounting Date parameter, which also belongs to the specified FA book parameter.

In R12, a new column known as the asset book type code is available to specify the asset book for the invoice distribution.  This column can be set at the line level, which then defaults to the invoice distribution; however, the asset book type code on the distribution level determines which lines are selected by the Mass Additions Create program.  On the contrary, in 11i, AP fetches all the valid asset accounts to determine the book for the invoice distribution and, subsequently, populate Assets with the invoice distributions previously selected.

In R12, AP only validates the asset book type code on the invoice distributions because no validation is available in the beginning.  The asset book type code may be NULL or populated with one value used during the execution.  Screen shot listed below shows the Parameters screen for the Mass Additions Create program.

In summary, the Mass Additions Create Program from AP is responsible for the population of the FA_MASS_ADDITIONS_GT table, but Assets module is responsible for the validation of the lines transferred from AP.

Useful Documents

Useful information can be found in the following documents:

-        White Paper on Mass Additions Create Process in R12 (Doc ID 567690.1)

-        Troubleshooting Guide for Mass Additions Create (Doc ID 133597.1)

-        Understanding Mass Additions Create (APMACR) and Troubleshooting from FA perspective (Doc ID 1070597.1)

Entity Relation Model for Accounts Payable and Assets

Oracle Projects Integration

Information about projects can be entered for supplier invoices and expense reports invoices when Oracle Projects is installed.  The project name, task, and expenditure information are recorded for each invoice transaction.  Additionally, the Account Generator creates a charge account for each invoice distribution with project information associated.  Project information contained in the invoice distributions tables are transferred to projects when applicable.

Entering Supplier Invoices and Expense Report Invoices

Invoices associated to suppliers and invoices generated from expense reports can be associated to projects.

Supplier Invoices can be created from the Invoice Workbench or via the Payables Open Interface.   The user can either enter manually the project information or the project-related distribution set in the invoice workbench, or the system will derive the project information from project-related purchase orders or recurring templates.  In this regard, further information can be found in the Oracle Payables User’s Guide Release 12 page 3-274.

The invoice workbench can also be used to add project information to any existing supplier invoice previously entered or imported.

Expense reports entered in Internet Expenses are processed by the Expense Reporting Workflow, and any changes made to the expense report after import will not be included in the workflow.

The determination of the expenditure item date for supplier invoices distribution lines is specified in the PA: Default Expenditure Item Date for Supplier Cost profile option.  Payables and Purchasing reads this profile to determine how the expenditure item date will be obtained.

Creation of Project-Related Expense Reports

The creation of expense reports project related encompasses the steps listed below:

1.      Population of the following expense report interface tables: AP_EXPENSE_REPORT_HEADERS_ALL, AP_EXPENSE_REPORT_LINES_ALL, AND AP_EXP_REPORT_DISTS_ALL

2.      Submission of the expense reports for review, approval or rejection.

3.      After approval, population of Payables tables with the APXEXPER - Expense Report Export; this program creates invoices in Payables with the data extracted from the interface tables.

4.      The project information in the invoice can be modified from the invoice workbench.

Potential Problems Related to Integration

1.      EXPENDITURE_TYPE is the common column between AP_INVOICE_DISTRIBUTIONS_ALL and PA_EXPENDITURE_TYPES; therefore, project accounting will not be displayed when AP_INVOICE_DISTRIBUTIONS_ALL and PA_EXPENDITURE_TYPES do not share a value in the EXPENDITURE_TYPE column.

2.      Project information cannot be updated in the invoices when the PA: Allow Override of PA Distributions in AP/PO profile option is disabled.

3.      The total amount of the invoice cannot be modified when the invoice has already been interfaced to Oracle Projects.

Useful Documents

Useful information can be found in the following document:

-        Missing Expenditure_Type in ap_invoice_distributions_all (Doc ID 1093075.1)

Entity Relation Model for Accounts Payable and Projects

Entity Relation Model for Accounts Payable and Expense Reports

Oracle Purchasing Integration

Accounts Payable is fully integrated with Purchasing via the Invoice. Invoices can be matched to purchase orders and information related to the purchase order can be accessed from the application.  The structures currently used to store data about invoices and purchase orders minimize the redundancy of data and facilitate the manipulation of data from centralized places.

Supplier and Purchasing Integration

Payables shares suppliers with Purchasing.  Moreover, suppliers can be entered in either the Payables or Purchasing application and use that supplier to create requisitions and purchase orders in Purchasing.   Later on, invoices can be created, within Payables, for the same supplier and match the invoices to one or more purchase order shipments, purchase order distributions, or purchase order receipts.

Additionally, Payables and Purchasing share the lookup values, which can be used during supplier entry.  Values can be created for the following lookup types in the Oracle

Payables Lookups window:

• Supplier Type

• Minority Group

Elsewhere, values can be created for the following lookup types in the Oracle Purchasing Lookups window:

• FOB

• Pay Group

• Supplier Type

• Minority Group

• Freight Terms

Useful Documents

-        How to Match Invoices to Purchase Orders White Paper (Doc ID 198535.1)

-        Purchase Order Matching Frequently Asked Questions (FAQ's) (Doc ID 213480.1)

-        Purchase Order Matching Troubleshooting Guide (Doc ID 214273.1)

Oracle Property Manager Integration

Payables can be used to issue payments recorded in Oracle Property Manager.  The information related to property leases are managed in Property Manager, subsequently, this information can be exported to Oracle Payables, via Payables Open Interface, for further payment processing.  The mandatory columns of the Payables Open Interface tables must be populated, so that the data may be transferred to Payables; otherwise, records will be rejected.  Detailed instructions for required and optional columns for the Payables Open Interface can be found in the useful documents section.

Useful Documents

-        Using SQL to Populate the Payables Open Interface Tables (Doc ID 234886.1)

Oracle Grants Accounting Integration

Grants Accounting can be conceived as an extension of Oracle Projects to provide project management features specific for public sector organizations.  Invoices, Distribution Sets, and Distributions are the entities shared by Oracle Grant Accounting and Payables.  Grants Accounting are related to awards, which are used to generate invoice distributions.  From a functional perspective, Grants Accounting includes all the features provided by Oracle Projects plus the following:

-        Multi-funded Projects

-        Award Management

-        Flexible Funds Control by Award

-        Powerful Indirect Costing

-        Online Invoice Review and Proposal

-        Simplified Billing

-        Award Status Inquiry

-        Award Segment in Payables Distribution Sets

-        Award Security

-        Government Reporting

-        Electronic Reporting

-        Multi-Organization Support

-        Integration with Oracle Labor Distribution.

Funds Check Hold in Invoices can be one of the main issues faced by Invoices associated to Oracle Grants Accounting Invoices.  Details to troubleshoot funds check problem can be found in the note listed in the Useful Documents section.

Useful Documents

-        Grants Accounting Funds Checking Troubleshooting Guide Part 2 (Doc ID 950195.1)1

Entity Relation Model for Accounts Payable and Purchasing

Oracle Inventory Integration

Recording information associated with the movement of goods between statistics-gathering countries may be necessary to comply with the European Union’s Intrastat requirements.

The relation between Invoices and Oracle Inventory is obtained via the Purchase Order.   The tables MTL_SYSTEM_ITEMS_B and PO_LINES_ALL contain the values that make possible the relational connection.  Missing data or orphan records specifically related to MTL_SYSTEM_ITEMS_B and PO_LINES_ALL may be considered the main problem observed between Payables and Oracle Inventory.   Other errors may be related to invoice distributions, which are not associated to purchase orders; therefore, the referential link between the Payables and Inventory tables cannot be established.

General Considerations

The invoice is the main entity of the Payables module; the invoice also contains all the columns with which the dependency with other financial tables is established.  Thus, the correct population of invoices headers and invoice distributions tables along with other payables tables is fundamental for the integration with other modules.    The note listed below contains instructions for the implementation of scripts to identify corruptions on invoices.  These scripts identify the problematic records and suggest potential Generic Data Fixes.

Data Corruption

R12 Diagnostic Scripts to Identify Corruptions on a Specific Invoice, For Which Generic Data Fix (GDF) are Available (Doc ID 1076312.1)

Payables Open Interface Issues

Simple, PO Matched, and Project Related are the three types of invoices that can be created with the Payables Open Interface.  Each type of invoice has specific combinations of mandatory and optional columns, which may lead to human errors when populating the interface tables and, consequently, several records may be rejected records because of incomplete information.  Detail information for the population of the interface tables can be found in the document “Using SQL to Populate the Payables Open Interface Tables (Doc ID 234886.1)”; however, several times the rejection messages found in the debug log file of the Payables Open Interface may not be very descriptive, such as when PO Matched Invoices are rejected because of Insufficient Receipt Information or Invalid Purchase Order.   To minimize user error when populating interface tables with PO Matched Invoices, PL/SQL code to populate interface tables for PO Matched Invoices is included below; this code only populates required columns and includes validation statements with tables from other modules.  Code can also be modified to insert more invoices and lines.

DECLARE

   ap_header   ap_invoices_interface%ROWTYPE;

   ap_lines    ap_invoice_lines_interface%ROWTYPE;

   x           NUMBER;

   y           NUMBER;

BEGIN

   FOR x IN 1 .. 1

   LOOP

-- Get invoice_id --

      SELECT ap_invoices_interface_s.NEXTVAL

        INTO ap_header.invoice_id

       FROM DUAL;

      ap_header.invoice_num := 'TEST-' || SYSDATE || '-' || x;

-- Here enter the segment1 (purchase order number) and org_id

      SELECT t1.segment1, t1.org_id

        INTO ap_header.po_number, ap_header.org_id

        FROM po_headers_all t1

       WHERE t1.type_lookup_code = 'STANDARD'

         AND t1.approved_flag = 'Y'

         AND (t1.closed_code IS NULL OR t1.closed_code = 'OPEN')

         AND t1.org_id = &org_id

         AND t1.segment1 = &po_number;

      ap_header.invoice_amount := 100;

      SELECT t1.lookup_code

        INTO ap_header.SOURCE

        FROM ap_lookup_codes t1

       WHERE t1.lookup_type = 'SOURCE' AND t1.lookup_code = 'INVOICE GATEWAY';

-- Insert an invoice header --

      INSERT INTO ap_invoices_interface

                  (invoice_id, invoice_num,

                   po_number, invoice_amount,

                   SOURCE

                  )

           VALUES (ap_header.invoice_id, ap_header.invoice_num,

                   ap_header.po_number, ap_header.invoice_amount,

                   ap_header.SOURCE

                  );

-- Insert invoice line --

      FOR y IN 1 .. 1

      LOOP

         ap_lines.invoice_id := ap_header.invoice_id;

         SELECT ap_invoice_lines_interface_s.NEXTVAL

           INTO ap_lines.invoice_line_id

           FROM DUAL;

         ap_lines.line_number := y;

         ap_lines.line_type_lookup_code := 'ITEM';

         ap_lines.amount := ap_header.invoice_amount;

         SELECT t2.po_header_id, t2.po_line_id,

                t3.line_location_id

           INTO ap_lines.po_header_id, ap_lines.po_line_id,

                ap_lines.po_line_location_id

           FROM po_headers_all t1, po_lines_all t2, po_line_locations_all t3

          WHERE t1.po_header_id = t2.po_header_id

            AND t2.po_line_id = t3.po_line_id

            AND t1.segment1 = ap_header.po_number

            AND ROWNUM < 2;

         INSERT INTO ap_invoice_lines_interface

                     (invoice_id, invoice_line_id,

                      line_number, line_type_lookup_code,

                      amount, po_header_id,

                      po_line_id, po_line_location_id

                     )

              VALUES (ap_lines.invoice_id, ap_lines.invoice_line_id,

                      ap_lines.line_number, ap_lines.line_type_lookup_code,

                      ap_lines.amount, ap_lines.po_header_id,

                      ap_lines.po_line_id, ap_lines.po_line_location_id

                     );

         COMMIT;

      END LOOP;

   END LOOP;

END;

Conclusion

Accounts payable (AP) encompasses all the transactions related to money that a person or company owes to suppliers, but has not been paid yet.  The invoice is used to register the transactions between debtors and suppliers, and, technically the invoice can be considered the common entity between Oracle Accounts Payables with other modules.  Payables is directly related to Assets, Projects, Purchasing, Property Manager, Grants Accounting, and Inventory.  Some relevant aspects about the functional and technical aspects of the modules previously discussed are listed as follows:

-        Track as asset check box must be enabled for the item line being processed.

-        Project accounting will not be displayed when AP_INVOICE_DISTRIBUTIONS_ALL and PA_EXPENDITURE_TYPES do not share a value in the EXPENDITURE_TYPE column.

-        The mandatory columns of the Payables Open Interface tables must be populated, so that the data may be transferred to Payables; otherwise, records will be rejected.

-        Grant Accounting includes all the features provided by Oracle Projects plus other functionalities related to public sector organizations.

-        Simple, PO Matched, and Project Related are the three types of invoices that can be created with the Payables Open Interface.  Each type of invoice has specific combinations of mandatory and optional columns, which may lead to human errors when the interface tables are populated.