CUSTOMER

Cloud Integration 9.0

Document Version: 1 – 2016-12

Procurement Solutions Integration Guide for Oracle

Ariba Procure-to-Order
Ariba Procure-to-Pay
Content

About Oracle Financials integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5

About file-based integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
About data transfer from the Ariba Procurement Solution to Oracle Financials. . . . . . . . . . . . . . . . . . 5
About data transfer from Oracle Financials to the Ariba Procurement Solution. . . . . . . . . . . . . . . . . . 5
File-based integration dataflow diagrams. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
About Web Services-Based Integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7
About importing master data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
About exporting transactional data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
Supported versions and integration landscape. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
About installing the Ariba Integration Toolkit. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8
About integration transaction support. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9

Integration scenarios and guidelines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Requirements for integration implementations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Purchase order integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Change and cancel purchase order integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Receipts integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .11
Negative receipt integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Invoice integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Configuring the Ariba Integration Toolkit. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

Downloading the Ariba Integration Toolkit. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Creating directory structures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Configuring the DB Connector. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Configuring additional default values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
How to find your default chart of accounts ID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
How to find your default preparer ID for purchase orders. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .16
How to find your default structure ID. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
About creating SQL queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Configuring for non-default accounting segments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Configuring the Ariba Data Transfer Tool. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Configuration for export events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Configuration for import events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .19
Running the Data Transfer Tool. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Scheduling the Ariba Integration Toolkit on Windows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
How to export a purchase order to Oracle Financials. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .20
How to import status from Oracle Financials. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Procurement Solutions Integration Guide for Oracle

2 CUSTOMER Content
 Scheduling the Ariba Integration Toolkit on Unix or Linux. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

Configuring the Ariba Procurement Solution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

How to enabling generation of an ERP order. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Specifying the preferred ordering method. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
Specifying a unique number for purchase orders. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
Enabling cancel order integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
How to allow creation of split orders based on currency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

Configuring Oracle Financials. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

Configuration tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Creating an Ariba Procurement Solution User. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Configuring the Ariba Procurement Solution CPI programs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Purchase order export. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Enabling CPO export. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Enabling change receipt export. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35
Using integration concurrent programs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
How to run a concurrent program. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
How to test integration concurrent programs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38
How to run integration concurrent programs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39

About master and transactional data integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41

Master data integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Master data query location. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Transactional data integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Transactional data export notes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
Supported export integration events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
Transaction ID and error imports. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
About importing remittance advice. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Setting up remittance import. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
About running the remittance import event. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
About flex field customizations. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50
Example flex field scenario. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50

About integration error handling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

Integration system errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Transactional data errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51
Exporting errors from the Ariba Procurement Solution to interface tables. . . . . . . . . . . . . . . . . . . . . 51
Exporting errors from interface tables to Oracle Financials base tables. . . . . . . . . . . . . . . . . . . . . . .52
Purchase order export errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52
Receipt export errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Master data and remittance import errors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52

Oracle Financials integration events. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54

Procurement Solutions Integration Guide for Oracle

Content CUSTOMER 3
 Revision history. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

Procurement Solutions Integration Guide for Oracle

4 CUSTOMER Content
About Oracle Financials integration

Integration between the Ariba Procurement Solution and Oracle Fianancials supports the exchange of both master
and transaction data. You can enable integration either through the File channel or Web services.

About file-based integration

You enable file-based integration of master and transactional data between the Ariba Procurement Solution and
Oracle Financials with the Ariba Integration Toolkit.

The Ariba Integration Toolkit is an Ariba-developed middleware solution that consists of the Data Transfer Tool
and the DB Connector. The Ariba Integration Toolkit enables exchange of data from the Ariba Procurement
Solution to Oracle Financials, and from Oracle Financials to the Ariba Procurement Solution.

About data transfer from the Ariba Procurement Solution

to Oracle Financials

Data transfer from the Ariba Procurement Solution to Oracle Financials is a two-step process that first involves the
Data Transfer Tool, and then the DB Connector.

1. The Data Transfer Tool downloads transactional data from the Ariba Procurement Solution to a configured
location on your file system. The data from the Ariba Procurement Solution is exported in the form of CSV files
that are generated for each integration event that you run.
2. The DB Connector reads the downloaded CSV files from the file system and uploads the data into the interface
tables in Oracle Financials.

About data transfer from Oracle Financials to the Ariba

Procurement Solution

Data transfer from Oracle Financials to the Ariba Procurement Solution is a two-step process that first involves the
DB Connector, and then the Data Transfer Tool.

1. The DB Connector downloads master data and transaction status data from Oracle Financials and transports
it to a location on your file system. The data is downloaded in the form of CSV files.
2. The Data Transfer Tool reads the CSV files from your file system and uploads the data to the Ariba
Procurement Solution.

For more information, see Configuring the Ariba Integration Toolkit [page 13].

Procurement Solutions Integration Guide for Oracle

About Oracle Financials integration CUSTOMER 5
File-based integration dataflow diagrams
The following diagrams illustrate the integration of master and transactional data for file-based integration.

Transporting master data from Oracle Financials to the Ariba Procurement Solution

1. The DB Connector reads master data from Oracle Financials and then transports it in the form of CSV files.
2. The Data Transfer Tool reads the CSV files and uploads data into the Ariba Procurement Solution.

Transactional data integration between Oracle Financiala and the Ariba Procurement Solution

1. The Data Transfer Tool exports data from the Ariba Procurement Solution in the form of CSV files.

Procurement Solutions Integration Guide for Oracle

6 CUSTOMER About Oracle Financials integration
 2. The DB Connector reads the CSV files and tranfers data to the staging tables in Oracle Financials.
3. The corresponding Oracle process (concurrent program) then transfers data from the staging tables to the
main Oracle tables after validation.
4. The DB connector retrieves the status of each transaction from Oracle Financials.
5. The Data Transfer tool reads the CSV files and uploads the status into the Ariba Procurement Solution.

About Web Services-Based Integration

Web Services-based integration to allow integration of master data and transactional data between Ariba
procurement solutions and Oracle Financials requires that you build the integration using an Enterprise
Application Integrations (EAI) tool.

For transactional data integration, Ariba procurement solutions publishes a SOAP message for the export event
you run. Your EAI tool consumes the SOAP message and sends it to Oracle Financials. Your EAI tool must return
the status of the transaction from Oracle Financials to Ariba procurement solutions.

For master data integration, Oracle Financials publishes a SOAP message corresponding to the event and posts
that message to your EAI tool. The EAI tool transforms the message into a format that allows Ariba procurement
solutions to consume the message.

Note
Master data integration using Web Services is limited to a subset of available events. You must use file-based
integration for any events that are not enabled for Web Services.

Ariba provides Web Services Description Language (WSDL) files for both import and export events. You must
import the WSDL file into your EAI tool to ensure that the generated SOAP messages adhere to the respective
WSDL files.

The following graphic illustrates the integration of master and transactional data on Web Services based
integration:

Procurement Solutions Integration Guide for Oracle

About Oracle Financials integration CUSTOMER 7
About importing master data

1. When Oracle master data is changed or updated, Oracle Financials posts the message to your EAI tool.
2. Your EAI tool then performs the necessary data transformations and transfers the SOAP message to the Ariba
Procurement Solution.

About exporting transactional data

1. When Ariba transactional data is successfully processed (created, changed or updated), Ariba Procurement
Solution post the message to your EAI tool.
2. Your EAI tool then performs the necessary data transformations and transfers the SOAP message to Oracle
Financials.

Supported versions and integration landscape

Integration between Ariba Procurement Solution and Oracle Financials is built on a standard generic variant. If you
have already built an integration with Oracle Financials using the standard generic variant, the out-of- the-box
integration provided with Ariba Procurement Solution will not impact your existing integration.

If you choose to upgrade to the out-of-the-box integration, your existing integration events will continue to work
and will not be affected.

Integration of the Ariba Procurement Solution is supported with the following Oracle Financial and Oracle
Middleware versions:

● Oracle Financial: Oracle Financial 12.2.4

● Oracle Middleware:
○ Oracle Fusion Middleware 12c (12.1.3)
○ Oracle Fusion Middleware 11 g (11.1.1.4)

Note
Ariba recommends using separate Development and Production integration environments.

About installing the Ariba Integration Toolkit

You must install the Ariba Integration Toolkit if you are using file-based integration.

For more information about the Ariba Integration Toolkit, see the Ariba On Demand Solutions Integration Toolkit
Guide.

Procurement Solutions Integration Guide for Oracle

8 CUSTOMER About Oracle Financials integration
The SQL Queries and Packages that enable data exchange with Oracle Financials are available on https://
connect.ariba.com .

To download these Queries and Packages, login to connect.ariba.com and click Ariba Connect for On-Demand
Solutions Ariba Procure-to-Pay or Ariba Procure-to-Order Sample SQL Queries Only (Oracle) .

About integration transaction support

Not all possible transactions are supported in file-based and web-based integration between Ariba Procurement
Solution and Oracle Financials. File-based integration supports a more complete set of transactions.

The following transactions are supported as part integration between Ariba Procurement Solution and Oracle
Financials:

Transaction File-Based Integration Web Services-Based Integration

Purchase Order Yes Yes

Change Purchase Order Yes Yes

Cancel Purchase Order Yes Yes

Receipt Yes Yes

Change Receipt Yes Yes

Invoice Yes Yes

Remittance Yes Yes

Expense Report No No

Import of Error and ID for all transactions Yes Yes

Master Data All master data (end to end) Limited (Only Procurement Unit and Sup­
plier)

Procurement Solutions Integration Guide for Oracle

About Oracle Financials integration CUSTOMER 9
Integration scenarios and guidelines

Depending on the integrations between Ariba Procurement Solution and Oracle that you want to implement, there
are different sequences of steps to undertake. Scenarios and guidelines are available for purchase orders, cancel
and change orders, receipts and negative receipts, and invoices.

Requirements for integration implementations

Integration implementations require that you download all SQL queries from https://connect.ariba.com and copy
them to a location on your file system. File-based integration and web-based integration have additional
requirements.

● For file-based integration, install the Ariba Integration Toolkit and configure the Data Transfer Tool and DB
Connector. For more information, see Configuring the Ariba Integration Toolkit [page 10].
● For Web services-based integration, build the integration using an Enterprise Application Integration (EAI) tool
and import the WSDL provide by Ariba into the EAI tool.

Purchase order integration

1. Enable generation of the ERP order. For more information, see Enabling Generation of an ERP Order [page
22].
2. Specify a preferred ordering method. For more information, see Specifying the Preferred Ordering Method
[page 22].
3. Specify a unique number (PO number) for purchase orders if required. This unique number is the PO number
that you export to Oracle Financials. For more information, see Specifying a Unique Number for Purchase
Orders [page 23].
4. Create a purchase order in the Ariba Procurement Solution.

In file-based integration, after the PO is approved, the status of the PO is changed to Ordering and the PO is
exported as a CSV file from the Ariba Procurement Solution to your file system by the Data Transfer Tool. The DB
Connector then reads the CSV file and inserts the data into Oracle Interface tables using the SQL queries you
downloaded previously. For PO integration, Ariba uses Oracle standard PO interface tables,
PO_HEADERS_INTERFACE, PO_LINES_INTERFACE and PO_DISTRIBUTIONS_INTERFACE.

In file-based integration, the standard Oracle concurrent program Import Standard Purchase Orders moves
the data from the interface tables into the base tables.

In Web services based-integration, the EAI tool pushes the data from SOAP message to the interface tables.

If the PO is successfully processed by the Oracle concurrent program, the PO data is moved to the base tables.

Procurement Solutions Integration Guide for Oracle

10 CUSTOMER Integration scenarios and guidelines
If there is an error, the error details are inserted into the PO_INTERFACE_ERRORS table. When you run the PO
status import event, the status changes from Ordering to Ordered if the PO is successfully processed, else the
status changes to Composing in the Ariba Procurement Solution.

Change and cancel purchase order integration

1. For cancel PO integration, enable the parameter application.procure.usecancelorderintegration.
For more information, see Enabling Cancel Order Integration [page 23].
2. Create the change or cancel PO in the Ariba Procurement Solution.
3. In a file-based integration after the change or cancel PO is approved, the status of the PO is changed to
Ordering and the PO is exported to your file sysetem as a CSV file by the Data Transfer Tool. The DB
Connector then reads the CSV file and inserts the data into the Oracle interface tables in Oracle Financials. For
change PO and cancel PO integration, Ariba uses the following custom interface tables:
○ ARBA_PO_CHANGE_ACTIONS_INT (also used for cancel PO)
○ ARBA_PO_CHANGE_HEADERS_INT
○ ARBA_PO_CHANGE_LINES_INT
○ ARBA_PO_CHANGE_DISTS_INT
4. For a change PO in a file-based integration, the Ariba PO Change - Change PO Integration (a Custom
Program) moves the data from the interface tables to the base tables.
5. For a cancel PO in a file-baed integration, the Ariba PO Change - Cancel PO Integration (a Custom
Program) moves the data from the interface tables to the base tables.
6. In Web services based-integration, your EAI tool transports the data using SOAP messages to the Oracle
interface tables.
7. If the PO is successfully processed by the respective concurrent program, the PO data is moved to the Oracle
base tables. If there is an error, the error details are inserted into the ARBA_PO_CHANGE_INT_ERRORS table.
When you run the change or cancel PO status import event, the status changes from Ordering to Ordered if
the PO is successfully processed, else the status changes to Composing in the Ariba Procurement Solution.

Receipts integration
1. Create a receipt in the Ariba Procurement Solution.
2. In a file-based integration after the receipt is approved, the status of the receipt is changed to Processing and
the receipt is exported to your file system as a CSV file by the Data Transfer Tool. The DB Connector then
reads the CSV file and inserts the data into the Oracle interface tables in Oracle Financials. For receipt
integration, Ariba uses the following standard interface tables:
3. RCV_HEADERS_INTERFACE
4. RCV_TRANSACTIONS_INTERFACE.
5. The standard concurrent program Receiving Transaction Processor moves the data from the interface
tables to the Oracle base tables.
6. In Web Services based-integration, the EAI tool transports the receipt data using SOAP message to the Oracle
interface tables.

Procurement Solutions Integration Guide for Oracle

Integration scenarios and guidelines CUSTOMER 11
7. If the receipt is successfully processed by Oracle, the status of the receipt in Ariba changes from Processing to
Received. If there is an error, the status changes to Awaiting Processing in the Ariba Procurement Solution.
The error details are inserted into the PO_INTERFACE_ERRORS table.

Negative receipt integration

Create a negative receipt if you want to reverse receipt information because of an incorrect shipment or manual
errors.

1. Create a receipt with negative quantity or amount.

2. In a file-based integration after the receipt is approved, the status of the receipt in the Ariba Procurement
Solution is changed to Processing and the receipt is exported to your file system as a CSV file by the Data
Transfer Tool. The DB Connector then reads the CSV file and inserts the data into the Interface tables in
Oracle Financials. For negative receipt integration, Ariba uses the Oracle custom interface table,
RCV_TRANSACTIONS_INTERFACE_STG.
3. The Ariba Receipt Change - Change Receipt Integration custom concurrent program moves the
data from the interface tables to the Oracle base tables.
4. In Web Services based-integration, the EAI tool transports the receipt data using SOAP message to the Oracle
interface tables.
5. If the receipt is successfully processed, the status of the receipt in the Ariba Procurement Solution changes
from Processing to Received. If there is an error, the status remains as Processing. The error details are
inserted into the PO_INTERFACE_ERRORS table.

Invoice integration
1. Create an invoice in the Ariba Procurement Solution.
2. In a file-based integration, after the invoice is approved, the status of the invoice in the Ariba Procurement
Solution is changed to Reconciling and the invoice is exported to yuor file system as a CSV file by the Data
Transfer Tool. The DB Connector then reads the CSV file and inserts the data into the Oracle interface tables in
Oracle Financials. For invoice integration, Ariba uses the following standard Oracle interface tables:
○ AP_INVOICES_INTERFACE
○ AP_INVOICE_LINES_INTERFACE.
3. The standard Oracle concurrent program Payables Interface Open Import moves the data from the
interface tables to the Oracle base tables.
4. In Web services based-integration, the EAI tool transports the invoice data using SOAP message to the Oracle
interface tables.
5. There is no status import into Ariba for invoices. You can view rejected records in the Oracle
AP_INTERFACE_REJECTIONS table. Rectify the error records and run the Oracle concurrent program again.

Procurement Solutions Integration Guide for Oracle

12 CUSTOMER Integration scenarios and guidelines
Configuring the Ariba Integration Toolkit

The Ariba Integration Toolkit is an Ariba-developed middleware solution that consists of the Data Transfer Tool
and the DB Connector. The Ariba Integration Toolkit enables exchange of data from Ariba procurement solutions
to Oracle Financials, and from Oracle Financials to Ariba procurement solutions.

Downloading the Ariba Integration Toolkit

Download the Ariba Integration Toolkit, aribaintegrationtoolkit.zip, from https://connect.ariba.com. The
ZIP file contains a file called aribaintegrationtoolkit.jar. Unzip the JAR file to a folder on your file system.

When you extract the JAR file, the following folder structure is created:

● ariba
● bin
● classes
● META-INF
● sample-bin
● sample-options

Note
If you are using multiple ERP instances, ensure that you install Ariba Integration Toolkit for each ERP instance.

Creating directory structures

Before running the DB Connector, you must first create directory structures to handle the various files upon which
the tool relies. These directories include:

● config: where the properties file is located.

● querydir: where the SQL queries for that event group are located.
● outputdir: for data export. Indicates where the CSV files generated by the SQL queries are located. The DB
Connector creates this directory automatically.
● inputdir: for data to be imported into your ERP system. Indicates where the CSV files exported from your
Ariba system are stored.
● logdir: where log files are stored.
● archivedir: where archives are stored. The DB Connector creates this directory automatically.

Procurement Solutions Integration Guide for Oracle

Configuring the Ariba Integration Toolkit CUSTOMER 13
The following example shows how to configure the directories for a scenario in which you extract master data from
the Oracle ERP system database and upload transactional data (in this case, payments) to the Oracle system
database. In this example, the DB Connector is installed in the following directory:

/IntegrationToolkit

The directory structure for the scenario would be:

When you define parameter values in this file, the values for string parameters must be enclosed within single
quotes.

/IntegrationToolkit
Config
OracleFinancials
MasterData
Queries --> querydir
Logs --> logfile
Payments
Queries --> querydir
Archives --> archivedir
Logs --> logFile directory

Configuring the DB Connector

After unzipping the Ariba Integration Toolkit and creating the directory structure, configure the DB Connector. The
sample-bin folder contains the sample shell script (batch file) to run the import and export events. You must
configure a shell script/batch file for each integration event.

You must provide values for the following parameters:

● JDBC Driver
● Ariba Integration Toolkit Home
● Java_Home
● Config

Provide the location of the oracle.properties file in the Config parameter. We recommend that you use
separate properties file for each integration event. For example, PO_TransactionalData.bat is the batch file to
export purchase orders and Receipt_TransactionalData.bat is the batch file to export receipts. We
recommend creating separate oracle.properties files for both integration events.

The sample oracle.properties file for import and export is available at sample-options/DBConnector/
data-import or data-export directory.

In the oracle.properties file, specify values for the following parameters:

● querydir
● outputdir(for export event)
● inputdir(for import events)
● archivedir
● databasedriver
● databasehost

Procurement Solutions Integration Guide for Oracle

14 CUSTOMER Configuring the Ariba Integration Toolkit
 ● databaseusername
● databasepassword
● databasemapping
● csvencoding
● logFile
● DBC error severity (SkipLine, WarnUser, TreatAsException)
● SMTP
● Timestampquery (query used to retrieve the current time from the DB)
● Timestampformat

For detailed description of these parameters, see the Ariba On-Demand Solutions Integration Toolkit Guide.

The oracle.properties file contains _from and _to parameters that take the from and to timestamp from the
Ariba Integration Toolkit and Oracle Financials respectively. These parameters can be used in the SQL queries to
perform incremental data loads.

The DBSyntax.table.oracle.properties file, located under sample-options\DBConnector\sql-

syntax-mappings, contains the parametized string values for all parameters that are used in SQL queries. For
example, the accounting combination.sql query is filtered based on chart of accounts ID ‘@COAID@’. The value for
this parameter is defined in the DBSyntax.table.oracle.properties file as COAID = 200. At runtime, the
SQL query substitutes the value for this parameter from this file.

Configuring additional default values

Retrieving master data from Oracle Financials requires that you set additional values for the Chart of Accounts ID
(COAID), default preparer ID for purchase orders, and default structure ID.

The following parameters are used in SQL queries to retrieve master data from Oracle Financials:

● Chart of Accounts ID (COAID)

● Value Set Name
● Structure ID

How to find your default chart of accounts ID

Procedure

1. Start SQL Plus, and connect to your Oracle Financials database.

2. Enter the following query:

select chart_of_accounts_id, organization_name from org_organization_definitions;

3. Select your default chart of accounts ID from the list of IDs.

Procurement Solutions Integration Guide for Oracle

Configuring the Ariba Integration Toolkit CUSTOMER 15
How to find your default preparer ID for purchase orders

Procedure

1. Ariba recommends you create a new user, called AribaUser, to serve as the purchasing agent for purchase
orders sent from the Ariba Procurement Solution.
2. After creating this user, find the preparer ID with the following query:

select person_id, full_name from per_people_f where last_name like ‘AribaUser’;

How to find your default structure ID

Procedure

1. Start SQL Plus, and connect to your Oracle Financials database.

2. Enter the following query against the table fnd_id_flex_structures_tl:

Results

select ID_FLEX_STRUCTURE_NAME, ID_FLEX_NUM from fnd_id_flex_structures_tl

where id_flex_structure_name = 'PO Item Category';

If this query does not return your structure names and IDs, your company’s item category flexfield structure
code might be different. If so, enter the correct name.

About creating SQL queries

For each event group that you plan to run, create individual .sql files and store them in the querydir directory.
SQL query files must be stored in the querydir directory so that the DB Connector can find and execute them.

Each file must contain one query formatted on a single line with no semicolon at the end of the line.

Note
For all export integration events, you have a postprocessing.sql file, in addition to the other SQL queries.
This postprocessing.sql performs an update or delete operation after the data is exported.

Procurement Solutions Integration Guide for Oracle

16 CUSTOMER Configuring the Ariba Integration Toolkit
Sample queries

The sample query illustrates extracting suppliers from Oracle Financials.

Note
This query is formatted for documentation purposes. In the actual query file, the query would appear on a single
line:

Select vend.vendor_id as "UniqueName",

vend.vendor_name as "Name" From po_vendors vend
Where
(not vend.vendor_type_lookup_code = 'EMPLOYEE')
AND ((vend.start_date_active < sysdate) or (vend.start_date_active is null))
AND ((vend.end_date_active >= sysdate) or (vend.end_date_active is null))
AND (vend.employee_id is null)
AND vend.enabled_flag = 'Y'
AND vend.vendor_id in
(select site.vendor_id from po_vendor_sites_all site, po_vendor_contacts
cont,org_organization_definitions org
where (vend.vendor_id = site.vendor_id)
AND (cont.vendor_site_id = site.vendor_site_id)
AND (site.purchasing_site_flag = 'Y')
AND ((site.inactive_date >= sysdate) or (site.inactive_date is null))
AND ((cont.inactive_date >= sysdate) or (cont.inactive_date is null))
AND (site.org_id = org.operating_unit)
AND (org.chart_of_accounts_id = @COAID@))

Note
COAID is the parameter for the above query, and the value is set in the oracle.properties file. When the SQL
query is executed, the actual value is substituted for this parameter.

For more information on creating SQL Queries, see the Ariba On-Demand Solutions Integration Toolkit Guide.

Configuring for non-default accounting segments

Ariba Procurement Solution provides built-in support for common accounting segments. To use other segments
you must modify your SQL queries and metadata AML files that reference those segments.

Ariba Procurement Solution supports the following accounting segments:

● Company
● Cost Center
● Region
● Account
● Product
● Sub-Account

To modify your SQL queries and AML metadata to reference other accounting segments, your Designated Support
Contact must log a service request and an Ariba Customer Support representative will contact you.

Procurement Solutions Integration Guide for Oracle

Configuring the Ariba Integration Toolkit CUSTOMER 17
Configuring the Ariba Data Transfer Tool
After you have configured the DB Connector, configure the Ariba Data Transfer Tool for both import and export
events. Under sample-options\DataTransferTool there are four folders, Data Upload, Data Download,
Spend Visibility, and SV-Download.

Configuration for export events

Configure parameters in the sample-options file in the Data Download folder.

Replace the text between each set of angle brackets with your site’s specific information as listed below:

● site = <specify the realm>

● serviceMode = 0 for (Ariba On Demand Application)
● sharedSecret = <welcome1> . This is configured in Ariba procurement solution using
Ariba Administrator -> Integration Manager -> IntegrationToolKit Security.
● set downloadDir = <directory location where you want to download the data>
● set event = <enter the ITK name of the integration event>
● set logDir = <directory of the log>
● set timestampFile = <timestamp file location>
● set filePrefix = <the folder name by which the file must get download>
● set JAVA_HOME = <java home dir>
● set operation = Download for Export
● setintegraionPasswordKeyPath = For more information, see the Ariba On-Demand
Solutions Integration Toolkit Guide.
● clientKeyStore - the path where the certificate is stored.
● clientKeyStorePassword - the password to access the certificate from the key store.
● clientKeyPassword - the password to access the certificate from the key.
● set urlPrefix = <URL to be used to transfer your data> for example: http://
s1.ariba.com/Buyer/filedownload

Note
The parameters, clientkeystore, clientkeystorepassword, and clientkeypassword are applicable
only when you are using certificate based authentication. For more information, see the Ariba On-Demand
Solutions Integration Toolkit Guide.

Procurement Solutions Integration Guide for Oracle

18 CUSTOMER Configuring the Ariba Integration Toolkit
Configuration for import events
Configure the parameters in the sample-options file in the Data Upload folder for import events.

Replace the text between each set of angle brackets with your site’s specific information as listed below:

● set topDir = <enter the dir location where DB Connector downloads the import data>
● set inDir = %topDIR%\InDir
● set outDir=< directory location where you want the data transfer tool to save sent
data>
● set filterPrefix = optional <files/directories to be ignored in the inDir during
processing of the import event>
● set operation= Incremental-upload for all the import events
● set urlPrefix= <URL to be used to transfer data> for example: http://s1.ariba.com/
Buyer/filedownload

Running the Data Transfer Tool

To run the Data Transfer Tool from the command prompt, go to the <ITKRootdir>/bin/> and run the batch file
for the desired event.

For example: D:/ITK/bin/ >aribafiletransfer.bat <location of the transactional batch

file>

Scheduling the Ariba Integration Toolkit on Windows

Using the Windows Scheduler, you can schedule both the Data Transfer Tool and the DB Connector components
of theAriba Integration Toolkit.

The Windows Scheduler is available at the following location on a Windows machine:

Start Programs Accessories System Tools Task Scheduler .

Procurement Solutions Integration Guide for Oracle

Configuring the Ariba Integration Toolkit CUSTOMER 19
How to export a purchase order to Oracle Financials
The following steps illustrate how to export a purchase order from the Ariba Procurement Solution to Oracle
Financials.

Procedure

1. Create a Purchase Order batch file PO_transactionaldata.bat to run the Data Transfer Tool at 11:00
hours, which in turn triggers the Export Purchase Order task. The batch file must contain the following
script:

aribafiletransfer.bat ..\sample-options\DataTransferTool\data-download
\PO_transactionaldata.bat

The batch file exports the CSV files.

2. Schedule the DB Connector (via another batch file - PO_dbctool_ora_import.bat) to be run at 11:05
hours. This batch file picks up the purchase order CSV files that have been downloaded by the Data Transfer
Tool from procurement solutions. The DB Connector then inserts the data into the Oracle Financials interface
tables.
3. The concurrent program can be scheduled in Oracle, or can be triggered by setting up a database trigger for
the custom interface table.

Results

When the concurrent program is run, data is moved from the interface tables into the Oracle Financials base tables
only if the data is successfully processed. If not, the data is moved to error tables.

Note
You can also choose to schedule the execution of the concurrent program in Oracle Financials instead of setting
a trigger in the database.

Procurement Solutions Integration Guide for Oracle

20 CUSTOMER Configuring the Ariba Integration Toolkit
How to import status from Oracle Financials
The following steps illustrate how to import status information from Oracle Financials to the Ariba Procurement
Solution.

Procedure

1. Schedule the DB Connector (using POIDStatus_dbctool_ora_export.bat) to download the Purchase

Order ID details from Oracle Financials at 11:15 hours.
2. Schedule the Data Transfer Tool to run the PurchaseOrderStatusImport task. This task runs the POID and
POError SQL queries. This task uploads the two CSV files (ID and Error) to the Ariba Procurement Solution.
Schedule this task to be run at 11:20 hours.

Results

Note
The Data Transfer Tool runs the PurchaseOrderStatusImport task, which internally calls both the
PurchaseOrderIDImport and PurchaseOrderErrorImports tasks.

Scheduling the Ariba Integration Toolkit on Unix or

Linux
On Unix or Linux, schedule Ariba Integration Toolkit execution using the crontab utility. The crontab utility is a
file that contains the schedule of cron jobs to be run and at the specified time intervals.

When you schedule jobs by using crontab, the jobs are automatically executed by the cron daemon at the
specified time intervals.

Procurement Solutions Integration Guide for Oracle

Configuring the Ariba Integration Toolkit CUSTOMER 21
Configuring the Ariba Procurement Solution

How to enabling generation of an ERP order

When you create a requisition for the first time, a direct order or PCardOrder is generated on approval. To ensure
that an ERP order is generated by default, you must change the default behavior.

Context

To change the default behavior, have your Designated Support Contact log a service request to have an Ariba
Customer Support representative contact you before you can perform the following steps:

Procedure

1. Log in to the Service Manager.

2. From the navigation bar on the left, under Site Manager, click Customer Sites.
3. Log in as Customer Support Admin to the relevant realm.
4. Click Customization Manager.
5. Click on Advanced on the top right corner.
6. Click Parameters.
7. Under Search Filters, search for ordermethods.
8. Click Edit.
9. Move ariba.purchasing.ordering.GenericPOERP to the top of the list.
10. Click OK.
11. Click Save.

Specifying the preferred ordering method

The preferred ordering method determines the format in which purchase orders are sent to a supplier based on
the supplier location.

Purchase orders can be sent in the following formats:

● Fax
● Email

Procurement Solutions Integration Guide for Oracle

22 CUSTOMER Configuring the Ariba Procurement Solution
 ● Printing
● Electronic Ordering

By default the value is set to Electronic Ordering. To ensure that the purchase order is downloaded in a CSV file,
you must set the value of the preferred ordering method to Printing.

Specifying a unique number for purchase orders

By default, order numbers in the Ariba Procurement Solution are generated starting from EP10. To specify your
preferred unique numbers for purchase orders, have your Designated Support Contact log a service request so an
Ariba Customer Support representative contacts you.

Note
Oracle Financials does not accept order numbers that are more than 20 characters even though in the Ariba
Procurement Solution you can provide more than 20 characters. Therefore, ensure that you do not specify
order numbers that exceed 20 characters.

Enabling cancel order integration

To enable cancel order integration, the parameter Application.Procure.UseCancelOrderIntegration
must be set to Yes. To set the value for this parameter, have your Designated Support Contact log a service
request so an Ariba Customer Support representative contacts you.

How to allow creation of split orders based on

currency
If you create a requisition with line items that use different currencies, the purchase order created must be split
based on the different currencies. Oracle Financials does not accept a purchase order with line items using
different currencies.

Context

To split the order based on currencies as explained below, have your Designated Support Contact log a service
request so an Ariba Customer Support representative contacts you.

Procurement Solutions Integration Guide for Oracle

Configuring the Ariba Procurement Solution CUSTOMER 23
Procedure

1. Log in to the Service Manager.

2. From the navigation bar on the left, under Site Manager, click Customer Sites.
3. Log in as Customer Support Admin to the relevant realm.
4. Click Customization Manager.
5. Click Field Configuration.
6. In Browse Classes, enter procurelineitem and click Search.
7. Click ProcureLineItem.
8. Click Create Field on the bottom right side and choose Computed Field.
9. In the Label field, enter a lable for the field.
10. In the Expression field, click Add.
11. Expand Amount and click on Currency.
12. Click Add next to the currency field.
13. Click the Advanced tab
14. Select Split Order on this Field and click OK.
15. Click Customizing on the top of the page.
16. Select Publish.

Procurement Solutions Integration Guide for Oracle

24 CUSTOMER Configuring the Ariba Procurement Solution
Configuring Oracle Financials

For the Ariba Procurement Solution to interact with Oracle Financials, you must configure both Oracle Financials
and your Ariba Procurement Solution.

Configuration tasks
Configure Oracle Financials involves creating an Ariba Procurement Solution user in Oracle Financials, configuring
the Ariba Procurement Solution CPI programs, installing and configuring Ariba changed and cancelled purchase
order programs and change receipt export, and defining currency conversion rates.

Creating an Ariba Procurement Solution User

Ariba recommends that you create a user account called AribaUser in Oracle Financials. You can use an existing
account, if required. The Ariba Procurement Solution creates purchase orders under this user account.

Configuring the Ariba Procurement Solution CPI programs

Configuring the Ariba Procurement Solution CPI programs involves registering an Oracle CPI ID, updating the data
group, adding environment variables, creating a CPI directory, and creating a CPI user:

How to register the Oracle CPI ID

Procedure

1. Log on to Oracle Financials.

2. Choose Switch Responsibility System Administrator.

3. Use the Oracle Financials application’s Java client menu to navigate to Security Oracle Register .
4. Insert a new record, using the following fields. Fill in your own preferred password for password.

Username = CPI
Password = password
Privilege = Enabled
Install Group = 1
Description = Custom Product Interfaces

Procurement Solutions Integration Guide for Oracle

Configuring Oracle Financials CUSTOMER 25
How to register the Oracle Financials CPI application

Procedure

1. Log on to Oracle Financials.

2. Use the menu to navigate to Application Register .

3. Insert a new record with the following information:

Long Name = Custom Product Interfaces

Short Name = CPI
Basepath = CPI_TOP
Description = Custom Product Interfaces

How to update the data group

Procedure

1. Log on to Oracle Financials.

2. Use the menu to navigate to Security Oracle Datagroup .

3. Navigate through the Edit Find menu to your existing Standard Data Group, and add a new
Application-Oracle ID pairing, as illustrated below:

Application = Custom Product Interfaces

Oracle ID = APPS

Use your own Oracle Financials application’s ID, as appropriate.

How to add environment variables

Procedure

1. Log on to Oracle Financials, and switch to the $APPL_TOP directory.

2. Edit the environment file (<filename>.env), as follows:

○ Find these two lines:

FND_TOP=”$APPL_TOP/fnd/11.5.0”
Export FND_TOP

○ Add the following two lines just below the previous lines:

CPI_TOP="$APPL_TOP/cpi/1.0.0"
Export CPI_TOP

Procurement Solutions Integration Guide for Oracle

26 CUSTOMER Configuring Oracle Financials
 The completed section appears as follows:

FND_TOP=”$APPL_TOP/fnd/11.5.0”
Export FND_TOP
CPI_TOP=”$APPL_TOP/cpi/1.0.0”
Export CPI_TOP

3. Save the changes, and close the file.

How to create the CPI Directory

Procedure

1. Log on to Oracle Financials, and switch to the $APPL_TOP directory.

2. In the $APPL_TOP directory, create the directory structure for cpi:

+ cpi
|
+ 1.0.0
admin
bin
forms
install
log
out
sql
srw

How to create the Oracle Financials CPI user

Procedure

1. From SQL Plus, connect to your Oracle Financials database,

2. Enter the following commands to create the CPI user:

Sqlplus apps/apps (username and password example)

SQL> create user cpi identified by cpi
2 default tablespace apps_data temporary tablespace temp
3 quota unlimited on apps_data
4 quota unlimited on apps_idx;
SQL> grant connect to cpi;
SQL> exit;

Procurement Solutions Integration Guide for Oracle

Configuring Oracle Financials CUSTOMER 27
Purchase order export

To enable a purchase order to be exported successfully to Oracle Financials, use the Oracle standard concurrent
program Standard Purchase Order Import.

After data from the Ariba Procurement Solution is inserted into the standard interface tables, this program
validates this data and inserts the data into the base tables. You can define a trigger or schedule to execute the
Standard Purchase Order Import concurrent program. The program supports catalogue quotations in
addition to purchase order export.

Enabling CPO export

Exporting changed and canceled purchase orders to Oracle 11i involves the following tasks:

1. Enable change and cancel order integration in the Ariba Procurement Solution.
2. Install the CPI files for change and cancel purchase orders. For more information, see Installing the Change
and Cancel Order CPI Files [page 28].
3. Set up the concurrent executables. For more information, see Setting Up Change and Cancel Order
Concurrent Executables [page 29].
4. Define the concurrent programs and their parameters. For more information, see Defining the Change and
Cancel Order Concurrent Programs [page 30].
5. Add the concurrent programs to the request group. For more information, see Adding the Change and Cancel
Order Concurrent Programs to the Request Group [page 33]
6. Run the concurrent programs. For more information, see Running the Change and Cancel Order Concurrent
Programs [page 34].

The Cancel PO and Change PO programs update orders in Oracle Financials. Schedule these programs to run at
regular intervals.

The Error Report program is a diagnostic tool that you can run based on your requirement. The Purge program is a
cleanup tool that you can run as needed to delete old change and cancel order data.

How to install the change and cancel order CPI files

Procedure

1. Download the procurement solution installation files from https://connect.ariba.com .

2. Copy the following files into the cpi/1.0.0/install folder.
○ arba_po_change_install_objects.sql
○ arba_po_change_interface_tbls.sql
○ arba_po_change_val_pkg_spec.sql
○ arba_po_change_val_pkg_body.sql
○ arba_po_change_pkg_spec.sql

Procurement Solutions Integration Guide for Oracle

28 CUSTOMER Configuring Oracle Financials
 ○ arba_po_change_pkg_body.sql
○ arba_po_change_ld_insert_pkg_spec.sql
○ arba_po_change_ld_insert_pkg_body.sql
○ arba_po_archive_po_sv_spec.sql
○ arba_po_archive_po_sv_body.sql

Note
For Oracle 11i, apply the following patch over the arba_po_change_ld_insert_pkg_body.sql installation
file:

arba_po_change_ld_insert_pkg_body.sql & arba_po_change_pkg_body.sql

ARBA_PO_ARCHIVE_PO_SV_SPEC/BODY is a new package introduced in Oracle 12.0.4 to archive

purchase orders.

PaymentHeader and RemittanceHeader queries are modified in Oracle 12.0.4. The modified queries are
available at https://connect.ariba.com .

3. Copy the following files into the cpi/1.0.0/sql folder.

○ arba_po_change_cancel_integration.sql
○ arba_po_change_change_integration.sql
○ arba_po_change_purge_interface.sql
○ arba_po_change_error_report.sql
4. Install the packages by running the following script:

Sqlplus apps/apps
SQL> @arba_po_change_install_objects

How to set up change and cancel order concurrent executables

Procedure

1. Use the menu to navigate to Concurrent Program Executable .

2. Insert the following four new records:

Record Values

Cancel PO Executable = ARBA_PO_CHANGE_CANCEL

Short Name = ARBA_PO_CHANGE_CANCEL
Application = Custom Product Interfaces
Description = Ariba PO Change - Cancel PO Integration
Execution Method = SQL*Plus
Execution File Name = arba_po_change_cancel_integration

Procurement Solutions Integration Guide for Oracle

Configuring Oracle Financials CUSTOMER 29
 Record Values

Change PO Executable = ARBA_PO_CHANGE_CHANGE

Short Name = ARBA_PO_CHANGE_CHANGE
Application = Custom Product Interfaces
Description = Ariba PO Change - Change PO Integration
Execution Method = SQL*Plus
Execution File Name = arba_po_change_change_integration

Purge Executable = ARBA_PO_CHANGE_PURGE

Short Name = ARBA_PO_CHANGE_PURGE
Application = Custom Product Interfaces
Description = Ariba PO Change Interface Purge Program
Execution Method = SQL*Plus
Execution File Name = arba_po_change_purge_interface

Error Report Executable = ARBA_PO_CHANGE_ERROR_REPORT

Short Name = ARBA_PO_CHANGE_ERROR_REPORT
Application = Custom Product Interfaces
Description = Ariba PO Change Interface Error Report
Execution Method = SQL*Plus
Execution File Name = arba_po_change_error_report

How to define the change and cancel order concurrent programs

Procedure

1. Navigate to Concurrent Program Define .

2. Insert four new records to correspond to the executables you set up in Setting Up Change and Cancel Order
Concurrent Executables [page 29].
Provide inputs only for the fields and check boxes mentioned in the following table. Leave all other fields and
check boxes blank.

Record Values

Cancel PO Program = Ariba PO Change - Cancel PO Integration

Enabled = checked
Short Name = ARBA_PO_CHANGE_CANCEL
Application = Custom Product Interfaces
Description = Ariba PO Change - Cancel PO Integration
Name = ARBA_PO_CHANGE_CANCEL
Use In SRS = checked
Restart on System Failure = checked
Format = Text
Save = checked
Print = checked

Procurement Solutions Integration Guide for Oracle

30 CUSTOMER Configuring Oracle Financials
 Record Values

Change PO Program = Ariba PO Change - Change PO Integration

Enabled = checked
Short Name = ARBA_PO_CHANGE_CHANGE
Application = Custom Product Interfaces
Description = Ariba PO Change - Change PO Integration
Name = ARBA_PO_CHANGE_CHANGE
Use In SRS = checked
Restart on System Failure = checked
Format = Text
Save = checked
Print = checked

Purge Program = Ariba PO Change Interface Purge Program

Enabled = checked
Short Name = ARBA_PO_CHANGE_PURGE
Application = Custom Product Interfaces
Description = Ariba PO Change Interface Purge Program
Name = ARBA_PO_CHANGE_PURGE
Use In SRS = checked
Restart on System Failure = checked
NLS Compliant = checked
Format = Text
Save = checked
Print = checked

Error Report Program = Ariba PO Change Interface Error Report

Enabled = checked
Short Name = ARBA_PO_CHANGE_ERROR_REPORT
Application = Custom Product Interfaces
Description = Ariba PO Change Interface Error Report
Name = ARBA_PO_CHANGE_ERROR_REPORT
Use In SRS = checked
Restart on System Failure = checked
Format = Text
Save = checked
Print = checked
Style = Landwide
Style Required = checked

3. For the Cancel PO program just created, click Parameters and create the Debug Mode parameter as follows.

Seq = 10
Parameter = Debug Mode
Description = Debug Level (1=Standard, 2=Standard & Debug)
Enabled = checked
Value Set = FND_NUMBER_1
Default Type = Constant
Default Value = 1
Display Size = 1
Concatenated Description Size = 25
Description Size = 50
Prompt = Debug Level (1=Standard, 2=Standard & Debug)

Make sure to fill in the fields and select only the check boxes noted below. Leave all other fields and check
boxes blank.
4. Repeat step 3 [page 31] for the Change PO program.
5. For the Purge program, click Parameters and create the following six parameters.

Procurement Solutions Integration Guide for Oracle

Configuring Oracle Financials CUSTOMER 31
 Fill in the fields and select only the check boxes in the following table. Leave all other fields and check boxes
blank.

Parameter Values

Purge Cancel Data Seq = 10

Parameter = Purge CANCEL Data
Description = Purge records with “CANCEL” action
Enabled = checked
Value Set = PO_PDOI_YES_NO_VALUES
Default Type = SQL Statement
Default Value = SELECT MEANING FROM FND_LOOKUPS WHERE
LOOKUP_TYPE ='YES_NO' AND LOOKUP_CODE ='Y'
Required = checked
Display = checked
Display Size = 50
Concatenated Description Size = 25
Description Size = 50
Prompt = Purge CANCEL Data

Purge Update Data Seq = 20

Parameter = Purge UPDATE Data
Description = Purge records with “UPDATE” action
Enabled = checked
Value Set = PO_PDOI_YES_NO_VALUES
Default Type = SQL Statement
Default Value = SELECT MEANING FROM FND_LOOKUPS WHERE
LOOKUP_TYPE ='YES_NO' AND LOOKUP_CODE ='Y'
Required = checked
Display = checked
Display Size = 50
Concatenated Description Size = 25
Description Size = 50
Prompt = Purge UPDATE Data

Purge Accepted Data Seq = 30

Parameter = Purge Accepted Data
Description = Purge records with process code of
“Accepted”
Enabled = checked
Value Set = PO_PDOI_YES_NO_VALUES
Default Type = SQL Statement
Default Value = SELECT MEANING FROM FND_LOOKUPS WHERE
LOOKUP_TYPE ='YES_NO' AND LOOKUP_CODE ='Y'
Required = checked
Display = checked
Display Size = 25
Concatenated Description Size = 25
Description Size = 50
Prompt = Purge Accepted Data

Procurement Solutions Integration Guide for Oracle

32 CUSTOMER Configuring Oracle Financials
 Parameter Values

Purge Rejected Data Seq = 40

Parameter = Purge Rejected Data
Description = Purge records with process code of
“Rejected”
Enabled = checked
Value Set = PO_PDOI_YES_NO_VALUES
Default Type = SQL Statement
Default Value = SELECT MEANING FROM FND_LOOKUPS WHERE
LOOKUP_TYPE ='YES_NO' AND LOOKUP_CODE ='Y'
Required = checked
Display = checked
Display Size = 25
Concatenated Description Size = 25
Description Size = 50
Prompt = Purge Rejected Data

Start Date Seq = 50

Parameter = Start Date
Description = Purge all data created after this date
Enabled = checked
Value Set = FND_STANDARD_DATE
Display = checked
Display Size = 11
Concatenated Description Size = 25
Description Size = 50
Prompt = Purge Start Date

End Date Seq = 60

Parameter = End Date
Description = Purge all data created before this date
Enabled = checked
Value Set = FND_STANDARD_DATE
Default Type = SQL Statement
Default Value = SELECT (SYSDATE - 10) FROM SYS.DUAL
Required = checked
Display = checked
Display Size = 11
Concatenated Description Size = 25
Description Size = 50
Prompt = Purge End Date

How to add the change and cancel order concurrent programs to

the request group

Procedure

1. Navigate to Security Responsibility Request .

2. Use the following query to find the request group where you want to add the concurrent program:

Group = All Reports

Application = Oracle Purchasing

Procurement Solutions Integration Guide for Oracle

Configuring Oracle Financials CUSTOMER 33
3. In the Requests area, add the following four records.

Record Values

Cancel PO Type = Program

Name = Ariba PO Change - Cancel PO Integration
Application = Custom Product Interfaces

Change PO Type = Program

Name = Ariba PO Change - Change PO Integration
Application = Custom Product Interfaces

Purge Type = Program

Name = Ariba PO Change Interface Purge Program
Application = Custom Product Interfaces

Error Report Type = Program

Name = Ariba PO Change Interface Error Report
Application = Custom Product Interfaces

How to run the change and cancel order concurrent programs

Procedure

1. Use the menu to navigate to Reports Run , choose Single Reques,, and then click OK.
2. To run the Cancel PO, Change PO, or Error Report program, enter its request name as specified in the previous
procedure, and then click Submit.

For example:
Request Name = Ariba PO Change - Cancel PO Integration
3. To run the Purge program, do the following:
a. Enter the request name: Ariba PO Change Interface Purge Program
b. Click OK.
c. Click or tab to any other field to display the Parameters dialog box.
d. Enter values for the parameters, and then click OK.

The first two parameters, Purge CANCEL Data and Purge UPDATE Data, specify whether to purge
cancel orders or change orders. One or both of these two parameters must be set to Yes.

The next two parameters, Purge Accepted Data and Purge Rejected Data, specify whether to
purge accepted or rejected orders. One or both of these two parameters must also be set to Yes.

The last two parameters, Purge Start Date and Purge End Date, specify the date range of the
records to be purged.
e. Click OK.
The Submit Request dialog box appears.

Procurement Solutions Integration Guide for Oracle

34 CUSTOMER Configuring Oracle Financials
 f. Click Submit.

Limitations in Oracle12.0.4

● The tax codes created at the Global Configuration Owner level are not supported.
● The tax codes created at the Legal Entity (LE) level get replicated to all the Operating Units belonging to that
LE.
● The TaxRateLookupByTaxCode.sql query supports only the standard Tax Types. If you need support for
custom Tax Types, you must modify this query.

Enabling change receipt export

Enabling change receipt export involves installing PL/SQL packages for the change receipt transaction, setting up
concurrent program executables, defining the concurrent program for the change receipt, setting up concurrent
program parameters, and adding the change receipt program to the appropriate request group.

How to install the PL/SQL packages for the change receipt

transaction

Procedure

1. Download the Ariba Procurement Solution installation files from https://connect.ariba.com .

2. Copy the following files into the cpi/1.0.0/install folder.
○ receipt_interface_stg.sql
○ arba_receipt_change_pkg_spec.sql
○ arba_receipt_change_pkg_body.sql
3. Copy the arba_receipt_change_integration.sql file into the cpi/1.0.0/sql folder.
4. Install the packages by running the following script:

Sqlplus apps/apps

SQL> - conn sqlplus apps/apps@connectionsting

SQL> @receipt_interface_stg.sql

SQL> @arba_receipt_change_pkg_spec

SQL> @arba_receipt_change_pkg_body

Procurement Solutions Integration Guide for Oracle

Configuring Oracle Financials CUSTOMER 35
How to set up concurrent program executables

Procedure

1. Use the menu to navigate to Concurrent Program Executable .

2. Insert a new record with the following information:

Executable = ARBA_RECEIPT_CHANGE_CHANGE

Short Name = ARBA_RECEIPT_CHANGE_CHANGE

Application = Custom Product Interfaces

Description = Ariba Receipt Change - Change Receipt Integration

Execution Method = SQL*Plus

Execution File Name = ARBA_RECEIPT_CHANGE_INTEGRATION

How to define the concurrent program for the change receipt

Procedure

1. Use the menu to navigate to Concurrent Program Define .

2. Insert a new record using the following information:

Program = Ariba Change Receipt - Change Receipt Integration

Enabled = checked
Short Name = ARBA_RECEIPT_CHANGE_CHANGE
Application = Custom Product Interfaces
Description = Ariba Change Receipt - Change Receipt Integration
Executable:
Name = ARBA_RECEIPT_CHANGE_CHANGE
Method = SQL*Plus
Request:
Use In SRS = checked
Enable Trace = checked
Restart on System Failure = checked
Output:
Format = Text
Save = checked
Print = checked

Procurement Solutions Integration Guide for Oracle

36 CUSTOMER Configuring Oracle Financials
How to set up concurrent program parameters

Procedure

1. In the Concurrent Program dialog box, click Parameters.

2. Create the Debug mode parameter.
3. Make sure to fill in the fields and check only the parameter noted in the following table.
4. Leave all other fields and checkboxes blank.

Parameter Values

Debug level Seq = 10

Parameter = Debug Level
Description = Debug Level (1=Standard, 2=Standard &
Debug)
Enabled = checked
Value Set = FND_NUMBER_1
Description = CPI debugging level
Default Type = Constant
Default Value = 1
Required = checked
Display = checked
Display Size = 1
Description Size =50
Concatenated Description Size = 25
Prompt = Debug Level (1=Standard, 2=Standard & Debug)

How to add the change receipt concurrent program to the

appropriate request group

Procedure

1. Use the menu to navigate to Security Responsibility Request .

2. Use the following query to find the request group where you want to add the concurrent program:

Group = All Reports

Application = Oracle Purchasing

3. In the Requests area, add the following record:

Type = Program
Name = Ariba Change Receipt - Change Receipt Integration
Application = Custom Product Interfaces
Description = Ariba Change Receipt - Change Receipt Integration

Procurement Solutions Integration Guide for Oracle

Configuring Oracle Financials CUSTOMER 37
Using integration concurrent programs
Your Oracle Financials administrator or Oracle DBA can automate the running of the concurrent program through
schedules or database triggers. This ensures that you do not have to log in to Oracle Financials and run a
concurrent program every time the Ariba Procurement Solution exports some transactional data.

During your implementation, to test export events, you must run the concurrent program manually . The following
sections explain you can manually run the concurrent programs.

How to run a concurrent program

When you run a concurrent program, it transfers data from the interface tables to the base tables.

Procedure

1. Navigate to the View menu of the Payables Responsibility and choose Request.
2. In the Request screen that appears, submit a New Request and choose Payables Open Interface Import.
3. Choose the Source from where you are sending your exported CSV files.
4. Click Submit. This generates a request ID.

Results

You can view the request ID from the Request Form.

How to test integration concurrent programs

To test the concurrent programs, ensure that data from the Ariba Procurement Solution is inserted into interface
tables.

Context

Context

For example, to insert receipt data into interface tables, you receive a line item in the Ariba Procurement Solution.
The scheduled task ProcessReceipts processes the receipts information and moves the data into the
PushDataEntry object. This object, in turn, inserts data into the interface tables.

Procurement Solutions Integration Guide for Oracle

38 CUSTOMER Configuring Oracle Financials
Procedure

1. Run the concurrent program for the integration you are testing: Receiving Transaction Processor.
2. Run the individual import tasks to import the document’s status from Oracle Financials into the Ariba
Procurement Solution.

How to run integration concurrent programs

The general steps for running all integration concurrent programs are identical.

Procedure

1. Log in to Oracle Financials using the Purchasing Agent or Purchasing Manager responsibility.
2. Run the concurrent program.
3. Review the status of the concurrent program.

How to run the concurrent program for receiving

Procedure

1. Navigate to Reports Run , and then choose Single Request.

2. Enter the following information:

Request Name = Receiving Transaction Processor

3. Click Submit Request.

How to run the concurrent program for payments

Procedure

1. Navigate to Payable Application Request Run , and then choose Single Request.
2. Enter the following information:

Request Name = Payables Open Interface Import

3. When you change focus from the Request Name field, the Parameters window appears. For example, enter
the following data for parameters to this new concurrent program:

Procurement Solutions Integration Guide for Oracle

Configuring Oracle Financials CUSTOMER 39
 ○ Batch Name = N/A
○ Source of data = Ariba
○ Purge = No
○ Summarize Report = No
4. Click OK to close the Parameters window, and then click Submit Request.

How to review the status of the concurrent program

Procedure

1. Navigate to Requests.
2. Execute a query using the Find button, and look for the report that has just been run.
3. Optionally, click View Output and View Log to view more information.

Procurement Solutions Integration Guide for Oracle

40 CUSTOMER Configuring Oracle Financials
About master and transactional data
integration

Master and transactional data integration occurs both on import from Oracle Financials to the Ariba Procurement
Solution, and on export from the Ariba Procurement Solution to Oracle Financials. Many integration events are
predefined, and can be run either individually or in batch mode. Customization is also supported.

Master data integration

To import master data, Ariba Procurement Solution run an Import Batch Data task. You can also import master
data by executing individual master data import events.

When running the Batch Data Load, the Ariba DB Connector uses SQL queries to download master data in CSV
files from Oracle Financials. The Ariba Data Transfer Tool uploads these CSV files (master data) to the Ariba
Procurement Solution.

The following table lists the master data import integration events:

Integration Event (Dis­ Description SQL Queries Parameter Used

play Name)

Import Accounting Loads accounting combinations from AccountCombination.sql @COAID@ (Chart of

Combinations Oracle Financials. Accounts ID) &
@Langval@

Import Accounts Defines the accounting entity Ac­ Account.sql @LookupAccountingS

count. egment@ &
@Langval@

Import Addresses Loads shipping and billing addresses Address.sql @COAID@ (Chart of
from Oracle Financials. Accounts ID) &
@Langval@

Import Companies Defines the accounting entity Com­ Company.sql @LookupCompanySegm

pany. ent@ & @Langval@

Import Defines the accounting entity Cost­ CostCenter.sql @LookupCostCenterS

CostCenters Center. egment@ &
@Langval@

Import Currency Loads the currency conversion rates. CurrencyConversionRate.sql @lastupdatedate@

Conversion Rates

Import Purchasing Defines the Operating Unit and In­ PurchasingUnit.sql @COAID@ (Chart of
Units ventory Org. Accounts ID)

Import ERP Loads partitioned commodity codes ERPCommodityCode.sql @DefaultStructureI

Commodity Codes from Oracle Financials. d@

Procurement Solutions Integration Guide for Oracle

About master and transactional data integration CUSTOMER 41
Integration Event (Dis­ Description SQL Queries Parameter Used
play Name)

Import Products Defines the accounting entity Prod­ Product.sql @LookupProductSegm

uct. ent@ & @Langval@

Import Accounting Defines the Oracle accounting entity Region.sql @LookupRegionSegme

Regions Region. nt@ & @Langval@

Import Defines the Oracle accounting entity SubAccount.sql @LookupSubAccountS

Subaccounts SubAccount. egment@ &
@Langval@

Import Suppliers Defines the set of suppliers that is Supplier.sql and SupplierLo­ @COAID@
valid. cation.sql

Note
For a supplier to be imported into
the Ariba Procurement Solution,
you must define at least one ac­
tive supplier site and associated
active contact in Oracle Finan­
cials.

Import Tax Codes Imports details of tax codes main­ TaxCode.sql @LEID@
tained in Oracle Financials.
@COAID@ (Release 11i)

Import Tax Rates Imports details of tax rates main­ TaxRateLookupByTax­ @LEID@
By Tax Code tained in Oracle Financials. Code.sql
@COAID@ (Release 11i)
Lookup
Note
The Tax Type supported in the
TaxRateLookupByTaxCode.
sql are only standard ones. If
you need support for custom Tax
Types, you must modify the
query.

Integration events can be run individually from Ariba Administrator.

To schedule batch import of master data, you must schedule the Import Batch Data event from the Ariba
Integration Toolkit. You can specify the following names in theAriba Integration Toolkit for the Import Batch
Data event:

● Import Batch Data

● Batch Data Load

After the scheduled batch event is executed, all master data is imported into the Ariba Procurement Solution. If
you do not encounter any errors in procurement solutions, the master data has been successfully imported into
the Ariba Procurement Solution.

Procurement Solutions Integration Guide for Oracle

42 CUSTOMER About master and transactional data integration
 Note
You can check the output of the master data imports by viewing the downloaded CSV files.

Master data query location

You can download the master data queries from https://connect.ariba.com.

Transactional data integration

To export purchase orders, invoices, and receipts to Oracle Financials, the Ariba Procurement Solution execute
integration events. These events can be scheduled to run automatically, or can be executed manually.

Integration events are executed from the Data Transfer Tool, which downloads the exported CSV files to an
appropriate location on the Ariba DB Connector. The Ariba DB Connector then reads the CSV files and transfers
the data to Oracle Financials Interface tables through inserts.

To update the status of transactions in the Ariba Procurement Solution, transaction IDs and errors (if any) are
imported from Oracle Financials.

As part of the integration between the Ariba Procurement Solution and Oracle Financials, the following
transactions are integrated:

● Exporting purchase orders

● Exporting changed purchase orders
● Exporting cancel purchase orders
● Exporting receipts
● Exporting negative receipts
● Exporting Payments/Invoice
● Importing Remittance
● Importing Transactional ID/Error data

Transactional data export notes

● In a PO based or non-PO based invoice, the description field accepts more than 240 characters in the Ariba
Procurement Solution. However, Oracle Financials does not accept more than 240 characters. Therefore,
when providing a description, restrict the description to 240 characters.
● Amount based purchase orders are also supported with this release.
● Though Ariba supports export of receiving tolerances, in the out of the box integration with Oracle Financials,
receiving tolerances are not exported by the Export Receipts to ERP event. To export receiving
tolerances to Oracle Financials, you must customize the Export Receipts to ERP event to add the
tolerance field. For more information, see the Ariba Procurement Data Import and Administration Guide.

Procurement Solutions Integration Guide for Oracle

About master and transactional data integration CUSTOMER 43
● The Ariba Procurement Solution allows you to receive based on quantity or amount. For more information, see
the Ariba Procurement User Guide. For amount-based receipts, the Ariba integration code maps the amount to
the quantity in Oracle Financials, and sets the price to 1. For example, if the amount of the receipt in the Ariba
Procurement Solution is $95, it is exported to Oracle Financials as an receipt with a quantity of 95 and with a
price of $1.
● In a receipt, if you have both accepted quantity and rejected quantity, only accepted quantity is integrated with
the Oracle Financials system. All rejected quantities are ignored.
● All purchase orders are integrated with Oracle Financials as Frozen orders.
● If the download date timestamp of the cancel PO event falls within the download date timestamp of the PO
export event, then duplicate cancel POs are downloaded in the CSV file.

Supported export integration events

You can run export events from the Ariba Procurement Solution in batch mode or individually.

Transaction Display Name SQL Query, CSV Files, and Concurrent Programs

Purchase Orders Export Purchase Order SQL Files

● PurchOrdHeaderDetails.sql
● PurchOrdLineDetails.sql
● PurchOrdSplitDetails.sql
● postprocessing.sql
CSV Files
● PurchOrdHeaderDetails.csv
● PurchOrdLineDetails.csv
● PurchOrdSplitDetails.csv
Concurrent Program
● Import Standard Purchase Orders (Standard Program)

Changed Purchase Or­ Export Change Purchase SQL Files

ders Order ● ChangePurchOrdHeaderDetails.sql
● ChangePurchOrdLineDetails.sql
● ChangePurchOrdSplitDetails.sql
● postprocessing.sql
CSV Files
● ChangePurchOrdHeaderDetails.csv
● ChangePurchOrdLineDetails.csv
● ChangePurchOrdSplitDetails.csv
Concurrent Program
● Ariba PO Change - Change PO Integration (Custom Pro­
gram)

Procurement Solutions Integration Guide for Oracle

44 CUSTOMER About master and transactional data integration
 Transaction Display Name SQL Query, CSV Files, and Concurrent Programs

Canceled Purchase Or­ Export Cancel Purchase SQL Files

der Order ● CancelPurchOrdHeader.sql
● postprocessing.sql
CSV Files
● CancelPurchOrdHeader.csv
Concurrent Program
● Ariba PO Change - Cancel PO Integration (Custom Pro­
gram)

Receipts Export Receipts to ERP SQL Files

● ReceiptHeaderExportWS.sql
● ReceiptDetailExportWS.sql
● postprocessing.sql
CSV Files
● ReceiptHeaderExportWS.csv
● ReceiptDetailExportWS.csv
Concurrent Program
● Receiving Transaction Processor (Standard Program)

Negative Receipts Export ERP Receipts SQL Files

● ReceiptHeaderExportWS.sql
● ReceiptDetailExportWS.sql
● postprocessing.sql
CSV Files
● ReceiptHeaderExportWS.csv
● ReceiptDetailExportWS.csv
Concurrent Program
● Ariba Receipt Change - Change Receipt Integration (Custom
Program)

Payment/Invoice Export Payment in 3 SQL Files

file format ● PaymentHeaderExport.sql
● PaymentDetailWithSplitAccountingExport.s
ql
● postprocessing.sql
CSV Files
● PaymentHeaderExport.csv
● PaymentDetailWithSplitAccountingExport.c
sv
Concurrent Program
● Payables Open Interface Import (Standard Program)

Procurement Solutions Integration Guide for Oracle

About master and transactional data integration CUSTOMER 45
Running export events from the Ariba Integration Toolkit

To run these events from the Ariba Integration Toolkit, you must specify the Integration Toolkit transaction names
rather than the batch names

Transaction Name Integration Toolkit Names

Purchase Order Export Purchase Order

Change Purchase Order Export Change Purchase Order

Cancel Purchase Order Export Cancel Purchase Order

Receipt/Negative Receipt Export Receipt

Export ERP Receipts

Payment/Invoice Export Payment in 3 file format

Transaction ID and error imports

Ariba Procurement Solution imports transaction IDs and Errors from Oracle Financials. Integration events can be
run individually from Ariba Administrator.

Integration Event (Display Name) Description

Import Purchase Order Error Imports error messages resulting from the export of purchase order to Ora­
cle Financials.

Import Purchase Order ID Imports information about purchase orders that have been exported suc­
cessfully to Oracle Financials.

Import Change Purchase Order Imports error messages that result from the export of change orders to Ora­
Error cle Financials.

Import Change Purchase Order ID Imports information about change orders that were exported successfully to
Oracle Financials.

Import Cancel Purchase Order Imports error messages that result from the export of cancel orders to Ora­
Error cle Financials.

Import Cancel Purchase Order ID Imports information about cancel orders that were exported successfully to
Oracle Financials.

Import Purchase Order ERP Header Imports the ERP header status for a set of purchase orders that are in an Or­
Status File dered state and that are created during a specified time period. For cancel
PO, the status is changed to No if the purchase order is received/invoiced in
ERP.

Import Receipt Error Imports back any error messages that result from a receipt export to Oracle
Financials.

Import Receipt ID Imports back the information about receipts that have been exported suc­
cessfully to Oracle Financials.

Procurement Solutions Integration Guide for Oracle

46 CUSTOMER About master and transactional data integration
 Integration Event (Display Name) Description

Import Invoice Status Imports the ERP invoice number if the invoice is successfully created in your
Oracle Financials system. If the invoice fails to get created in your Oracle Fi­
nancials system, this event imports information about the errors that were
reported by your Oracle Financials system.

Note
Before you execute this event, download the queries
InvoiceIDImport.sql and InvoiceErorrImport.sql from
connect.ariba.com based on the Oracle Financials version you are using.

Import Remittance Imports remittance details from Oracle Financials.

Running ransaction ID and error import events from Ariba

Integration Toolkit

To run transaction ID and error import events from the Ariba Integration Toolkit, you must specify the Ariba
Integration Toolkit event names rather than the batch names.

Integration Event Integration Toolkit Names Description

Name

Import Purchase Import Purchase Order Status Imports error and ID information of purchase orders that have
Order Status been exported.

Import Change Import Change Purchase Order Imports error and ID information of change purchase orders that
Purchase Order Status have been exported.
Status

Import (Queue) Import Cancel Purchase Order Imports error and ID information of cancel purchase orders that
Cancel Purchase Status have been exported.
Order Status
Import (Queue) Cancel Purchase
Order Status

Import Receipt Import Receipt Status Imports error and ID information of receitps that have been ex­
Status ported.

Note
The status import pulls the status of a transaction only after 30 minutes from the time the export event is run.
For example, if you run an export event at 2.30 P.M., the status import will happen only at 3.00 P.M. for that
event.

Procurement Solutions Integration Guide for Oracle

About master and transactional data integration CUSTOMER 47
About importing remittance advice
Remittance advice is an electronic or paper document that identifies and provides details about open invoices that
are in the process of being paid. Remittance details include the payment methods, bank information, and a list of
paid invoices with discount and paid amounts.

ERP remittance import is available for:

● All procurement solutions vendors within a specified period of time.

● Invoices (that is, purchase order matched invoices and non-purchase order invoices), credit memos.
● Cleared or paid payments and void payments.

The remittance import process flow involves these steps:

1. The Ariba DB Connector reads the value stored in _to column in the TimeStamp section of the .properties
file. The Ariba DB Connector uses this timestamp for incremental download in the SQL query.
2. Each payment record or document in Oracle Financials is mapped to create a payment transaction, or, in the
case of a void document, to update a payment transaction.
3. The Import Remittance task uploads remittance CSV.

Setting up remittance import

To set up remittance imports in Oracle Financials, you specify the details for incremental import (pull). The Ariba
Procurement Solution incrementally imports payment data, including remittance advice.

Every time ImportRemittance event is run from Ariba DB Connector, the data is validated against the timestamp
of the previously executed ImportRemittance event that is logged in the Ariba Procurement Solution. Only the
payments data in Ariba DB Connector with the ap_invoice_payments_all.last_updated_date value
(timestamp) greater than the payment timestamp in the Ariba Procurement Solution, are imported.

Note
To facilitate recovery when ImportRemittance fails, for any reason, change the timestamp of the
transactional import in the Ariba DB Connector to the value in the backup file to import the data again.

About specifying payment methods

In the Ariba Procurement Solutionr, default payment methods are check, wire, and ACH. You must map these to
the payment methods in Oracle Financials by importing the master data CSV file containing the Oracle Financials
payment methods.

The following table lists the default configuration mappings for payment methods:

Oracle Financials (continued) Ariba Procurement Solution

Check check

Procurement Solutions Integration Guide for Oracle

48 CUSTOMER About master and transactional data integration
 Oracle Financials (continued) Ariba Procurement Solution

Wire wire

Electronic - EFT ACH

Note
If you use a payment method that is not mapped in the initial configuration, you must change the mapping in the
Ariba DB Connector SQL query.

Importing remittance advice for specific vendors

You can create filters in SQL queries to import remittance advice for specific vendors.

Note
Before deactivating a supplier, supplier location, or contact at a supplier location, make sure that the
ImportRemittance event is run successfully and all payments have been imported to your Ariba Procurement
Solution.

About running the remittance import event

Importing remittance information from Oracle Financials is a four-step process.

1. First create an invoice and export it to Oracle Financials from the Ariba Procurement Solution.
2. Ensure that the invoice has successfully reached the Oracle base tables. You must then approve the invoice
from the Oracle Financials Invoice Workbench and make the payment (Full or Partial).
3. Run the Ariba DB Connector to import the remittance data from Oracle into the Ariba Procurement Solution.
This event creates two CSV files (Remittance.csv and RemittanceDetails.csv).
4. Run the ImportRemittance event from the Ariba Administrator or as a scheduled task to upload the
Remittance CSV files into the Ariba Procurement Solution.

If the import is successful, you see the Payment Transaction created in the Ariba Procurement Solution made
against the payment request.

Note
You can perform a batch data upload for Remittance, using the Data Transfer Tool. For more information on
how to perform the batch upload, see the Integration Toolkit User Guide.

Procurement Solutions Integration Guide for Oracle

About master and transactional data integration CUSTOMER 49
About flex field customizations
In addition to the standard available purchase order fields, you can add flex fields (normal or computed) to a
document that you want to send from the Ariba Procurement Solution to Oracle Financials.

To add flex fields, customize the respective AML file. Customization is performed through the Service Manager
user interface. For more information, see the Service Administration Guide.

Example flex field scenario

Suppose you want to add a flex field called vendor_name to your purchase order header file. You need to
customize the corresponding AML file to ensure that the field is added.

After you have added the required field and published it, when the purchase order event is run, this new field is
available as part of the exported header CSV file. To ensure that this new field is read by the Ariba DB Connector
and consumed in Oracle Financials, you must modify the PurchOrdheaderDetails.sql query.

To add the flex field called vendor_name, please have your Designated Support Contact log a service request and
an Ariba Customer Support representative will contact you.

The out of the box query is as follows:

insert into
po_headers_interface(interface_header_id,interface_source_code,action,document_type_
code,document_num,agent_id,vendor_id,vendor_site_id,org_id,frozen_flag,currency_code
,attribute1,last_update_date) values
(po_headers_interface_s.nextval,'Ariba','ORIGINAL','STANDARD',!
ERPOrderID!,@AGENTID@,!Supplier!,!SupplierLocation!,(SELECT operating_unit FROM
org_organization_definitions where organization_id=(substr(!ProcurementUnit!,
1,instr(!ProcurementUnit!,':')-1))),'Y',!TotCostCurrency!,(substr(!ProcurementUnit!,
1,instr(!ProcurementUnit!,':')-1)),sysdate)

The modified query is as follows:

insert into
po_headers_interface(interface_header_id,interface_source_code,action,document_type_
code,document_num,agent_id,vendor_id,vendor_site_id,org_id,frozen_flag,currency_code
,attribute1,last_update_date, vendor_name) values
(po_headers_interface_s.nextval,'Ariba','ORIGINAL','STANDARD',!
ERPOrderID!,@AGENTID@,!Supplier!,!SupplierLocation!,(SELECT operating_unit FROM
org_organization_definitions where organization_id=(substr(!ProcurementUnit!,
1,instr(!ProcurementUnit!,':')-1))),'Y',!TotCostCurrency!,(substr(!ProcurementUnit!,
1,instr(!ProcurementUnit!,':')-1)),sysdate,!vendor_name!)

In the modified query, a new column called vendor_name and value !vendname! are added. The exclamation
mark prefixed and suffixed to the value indicates that the value must be read from the CSV file and inserted into
the interface tables.

Procurement Solutions Integration Guide for Oracle

50 CUSTOMER About master and transactional data integration
About integration error handling

Integration errors can occur during both import and export. These include system errors, transactional data
errors, purchase order export errors, receipt export errors and master data and remittance import errors. In each
case there are steps you can take to correct any problems.

Integration system errors

System errors may occur if either the Ariba Procurement Solution or Oracle Financials is down when exporting or
importing data.

To handle system errors, run the export or import task again once the respective system is back in service.

Transactional data errors

When you encounter transactional data errors (for example, incorrect data), import the error information from
Oracle Financials into the Ariba Procurement Solution. This information enables you to resolve the errors and run
the transaction again.

Transactional errors can occur at the following phases during export:

● From the Ariba Procurement Solution into the interface tables

● From the interface tables into Oracle Financials base tables

Exporting errors from the Ariba Procurement Solution to

interface tables

DB Connector imports exported CSV files from the Ariba Procurement Solution into the Oracle Financials Interface
tables. Errors may occur due to connection failure or incorrect/invalid data.

When errors occur due to connection failure or incorrect/invalid data, DB Connector continues to try to exporting
the data each time it is scheduled to run until the data is inserted into the Interface tables successfully.

When performing batch inserts, if there is an error in one of the records, then the entire batch process fails and no
records are inserted into the Interface tables.

If an error occurs when exporting purchase orders, the Ariba Procurement Solution logs an error, and the
purchase requisition associated with that order remains in the Ordering state.

Procurement Solutions Integration Guide for Oracle

About integration error handling CUSTOMER 51
Exporting errors from interface tables to Oracle Financials
base tables
An Oracle Financials error can occur when Oracle Financials rejects an incoming purchase order, receipt, or
invoice (for example, because an exported order contains a supplier that has been deactivated on Oracle
Financials).

When Oracle Financials rejects an order, receipt, or invoice, the concurrent program provides information on the
causes for rejection of the invoice, receipt, or order. The concurrent program records the error information in the
appropriate tables.

Purchase order export errors

When a purchase order export fails to create a purchase order in Oracle Financials because data is invalid, you can
import the error information into the Ariba Procurement Solution using the Error Import task (Import Purchase
Order Error).

The Error Import task changes the state of the Requisition to Composing. You can then correct the data and
resubmit the requisition to Oracle Financials.

Note
Error messages returned from Oracle Financials are truncated to 40 characters in the Ariba Procurement
Solution.

For detailed information on error handling for exporting changed and canceled purchase orders, see the Ariba
Buyer Procurement Guide.

Receipt export errors

If a receipt export to Oracle Financials fails, run the ReceiptErrorImport task. The task changes the status of the
receipt from Processing to Awaiting Processing. Correct any errors, and run the ProcessPendingReceipts
scheduled task to export the receipt and change the status to Processing.

For detailed information on error handling for exporting receipts, see the Ariba Buyer Procurement Guide.

Master data and remittance import errors

For master data or transactional data imports such as remittance, you can import the data any number times after
correcting the data.

The Remittance Import event uses the incremental import feature in the DB Connector.

Procurement Solutions Integration Guide for Oracle

52 CUSTOMER About integration error handling
To re-run a failed remittance import:

1. Replace the remittance timestamp file with the old timestamp file.
2. Re-run the import task.

Procurement Solutions Integration Guide for Oracle

About integration error handling CUSTOMER 53
Oracle Financials integration events

A list of the integration events provided with the Ariba Procurement Solution that work with Oracle Financials
configurations is available. For complete information on using integration events, see the Ariba Buyer
Configuration Reference Guide.

Event Name Description File or Web Service

Import Accounting Combination Imports accounting combinations from Ora­ File

cle Financials. Used to validate accounting
information, for example, to indicate that
only certain cost centers are available in a
given region.

Import Accounts Imports the accounting entity Account. File

Import Addresses Imports shipping and billing addresses from File

Oracle Financials.

Import Buyer Payment Bank Location Imports payment bank information for the File
buying company from a CSV file. For more
information, see the Ariba Settlement Guide.

Import Company Imports the accounting entity Company. File

Import Cost Centers Imports the accounting entity CostCenter. File

Import Currency Conversion Rates Imports the currency conversion rates. File

Import Inventory Organization Imports the Oracle Inventory Organization File

information.

Import Operating Unit Imports the Oracle Operating Unit informa­ File
tion.

Import Partitioned Commodity Code Imports partitioned commodity codes from File
Oracle Financials.

Import Payment ID Imports payments information that have File

been exported to Oracle Financials.

Import Payment Error Imports error messages resulting from the File
export of payment data to Oracle Financials.

Import Payment Terms Language Imports payment information in specified File

languages to Oracle Financials, providing de­
scriptions of the payment terms in different
languages to clients from different regions.

Import Payment Terms Imports payment terms information. File

Import Product Imports accounting entity Product informa­ File

tion.

Export Purchase Order Exports Purchase Orders in CSV files. File / Web Service

Export Change Purchase Orders Exports changed Purchase Orders in CSV File / Web Service
files.

Procurement Solutions Integration Guide for Oracle

54 CUSTOMER Oracle Financials integration events
 Event Name Description File or Web Service

Export Cancel Purchase Order Exports canceled Purchase Orders in CSV File / Web Service
files.

Export Purchasing Units Exports purchasing units File / Web Service

Import Purchasing Units Imports purchasing units from Oracle Finan­ File / Web Service
cials

Import Purchase Order Status Imports purchase order ID and Error infor­ File
mation from Oracle Financials back to Ariba
Buyer.

Import Change Purchase Order Status Imports changed purchase order ID and Er­ File
ror information from Oracle Financials back
to Ariba Buyer.

Import Cancel Purchase Order Status Imports canceled purchase order ID and Er­ File
ror information from Oracle Financials back
to Ariba Buyer.

Import Purchase Order Error Imports error messages resulting from the File
export of purchase order to Oracle Finan­
cials.

Import Cancel Purchase Order Error Imports error messages that result from the File
export of cancel orders to Oracle Financials.

Import Change Purchase Order Error Imports error messages that result from the File
export of change orders to Oracle Financials.

Import Purchase Order ID Imports information about purchase orders File

that have been exported to Oracle Financials.

Import Change Purchase Order ID Imports information about change orders File
that were exported to Oracle Financials.

Import Cancel Purchase Order ID Imports information about cancel orders that File
were exported to Oracle Financials.

Import Purchase Order ERP Header Imports the ERP header status for a set of File
Status Ariba Buyer purchase orders that are in an
Ordered state and that are created during a
specified time period.

Export Payment Requests Exports payment data to Oracle Financials. File / Web Service

Import Payment Status Imports payment ID and Error information File

from Oracle Financials back to Ariba Buyer.

Import Receipt Error Imports back any error messages that result File
from a receipt export to Oracle Financials.

Import Receipt ID Imports back the information about receipts File

that have been exported to Oracle Financials.

Export Receipts Exports receipts to Oracle Financials. File / Web Service

Import Region Imports Oracle accounting entity Region.

Import Requisitions Imports requisitions created in Oracle Finan­ File / Web Service
cials

Procurement Solutions Integration Guide for Oracle

Oracle Financials integration events CUSTOMER 55
Event Name Description File or Web Service

Import Remittance Imports remittance details from Oracle and File / Web Service
sends them to AN, which can then route the
information to suppliers accordingly.

Import Sub Account Imports Oracle accounting entity SubAc­ File

count.

Import Supplier Location Remittance Imports remittance information associated File

Information with a supplier location. For more informa­
tion, see the Ariba Settlement Guide.

Import Supplier Location Supplement Imports standard supplier information by File

adding Ariba-specific information, such as
preferred ordering method and purchasing
card usage. For details on the relationship of
this integration event to ImportSupplier, see
the Ariba Buyer Data Load Guide.

Import Supplier Bank Payment Locations Imports payment bank information from a File
CSV file. For more information, see the Ariba
Settlement Guide.

Import Supplier Imports valid set of supplier information. File / Web Service

Note
For a supplier to be imported to Ariba
Buyer, you must define at least one active
supplier site and associated active con­
tract in Oracle Financials.

Import Remittance Location Imports a list of valid remittance send ad­ File
dresses from CSV files. This event is used for
fraud protection, to ensure that remittance
addresses defined in a supplier’s profile on
Ariba SN are valid. For more information, see
the Ariba Settlement Guide.

Import Unit Of Measure Mapping Imports a mapping from canonical units of File
measure to partition-specific ones.

Import UOM Info Imports a mapping between these two fields. File
It is required only for configurations that are
doing receipt export.

Oracle systems include two codes for units

of measure: UOM_CODE and
UNIT_OF_MEASURE. The UOM_CODE is
used for requisitions and purchase orders.
However, receipt exports require the
UNIT_OF_MEASURE field.

Import Users Imports a list of valid users and the User Pro­ File
file data for each user.

Procurement Solutions Integration Guide for Oracle

56 CUSTOMER Oracle Financials integration events
Revision history

The following table provides a brief history of the updates to this guide. Ariba updates the technical documentation
for its On Demand solutions if

● software changes delivered in service packs or hot fixes require a documentation update to correctly reflect
the new or changed functionality;
● the existing content is incorrect or user feedback indicated that important content is missing.

Ariba reserves the right to update its technical documentation without prior notification. Most documentation
updates will be made available in the same week as the software service packs are released, but critical
documentation updates may be released at any time.

To provide feedback on this guide or any Help@Ariba resources, click the Submit Feedback link on any
Help@Ariba page.

Document Month/Year of Updated Chapter/ Short Description of Change

Version Update Section

1 December 2016 N/A Reset revision history for Ariba Cloud Integration Release 9.0.

Procurement Solutions Integration Guide for Oracle

Revision history CUSTOMER 57
Important Disclaimers and Legal Information

Coding Samples
Any software coding and/or code lines / strings ("Code") included in this documentation are only examples and are not intended to be used in a productive system
environment. The Code is only intended to better explain and visualize the syntax and phrasing rules of certain coding. SAP does not warrant the correctness and
completeness of the Code given herein, and SAP shall not be liable for errors or damages caused by the usage of the Code, unless damages were caused by SAP
intentionally or by SAP's gross negligence.

Accessibility
The information contained in the SAP documentation represents SAP's current view of accessibility criteria as of the date of publication; it is in no way intended to be a
binding guideline on how to ensure accessibility of software products. SAP in particular disclaims any liability in relation to this document. This disclaimer, however, does
not apply in cases of willful misconduct or gross negligence of SAP. Furthermore, this document does not result in any direct or indirect contractual obligations of SAP.

Gender-Neutral Language
As far as possible, SAP documentation is gender neutral. Depending on the context, the reader is addressed directly with "you", or a gender-neutral noun (such as "sales
person" or "working days") is used. If when referring to members of both sexes, however, the third-person singular cannot be avoided or a gender-neutral noun does not
exist, SAP reserves the right to use the masculine form of the noun and pronoun. This is to ensure that the documentation remains comprehensible.

Internet Hyperlinks
The SAP documentation may contain hyperlinks to the Internet. These hyperlinks are intended to serve as a hint about where to find related information. SAP does not
warrant the availability and correctness of this related information or the ability of this information to serve a particular purpose. SAP shall not be liable for any damages
caused by the use of related information unless damages have been caused by SAP's gross negligence or willful misconduct. All links are categorized for transparency (see:
http://help.sap.com/disclaimer).

Procurement Solutions Integration Guide for Oracle

58 CUSTOMER Important Disclaimers and Legal Information
Procurement Solutions Integration Guide for Oracle
Important Disclaimers and Legal Information CUSTOMER 59
www.ariba.com

Copyright © 2016 Ariba, Inc. All rights reserved.

This documentation, as well as the Ariba solutions, software and/or
services described in it, contain proprietary information. They are
provided under a license or other agreement containing restrictions
on use and disclosure and are also protected by copyright, patent
and/or other intellectual property laws. Except as permitted by such
agreement, no part of the document may be reproduced or
transmitted in any form by any means, electronic, mechanical or
otherwise, without the prior written permission of Ariba, Inc.
Ariba, Inc. assumes no responsibility or liability for any errors or
inaccuracies that may appear in the documentation. The
information contained in the documentation is subject to change
without notice.
Ariba and Ariba products and services mentioned herein as well as
their respective logos are trademarks or registered trademarks of
Ariba, Inc. in the United States and other countries. Please see
http://www.ariba.com/legal/trademarks for additional trademark
information and notices.
Ariba Sourcing solutions (On Demand and software) are protected
by one or more of the following patents, including without limitation:
U.S. Patent Nos. 6,199,050; 6,216,114; 6,223,167; 6,230,146;
6,230,147; 6,285,989; 6,408,283; 6,499,018; 6,564,192; 6,871,191;
6,952,682; 7,010,511; 7,072,061; 7,130,815; 7,146,331;
7,152,043;7,225,152; 7,277,878; 7,249,085; 7,283,979; 7,283,980;
7,296,001; 7,346,574; 7,383,206; 7,395,238; 7,401,035; 7,407,035;
7,444,299; 7,483,852; 7,499,876; 7,536,362; 7,558,746; 7,558,752;
7,571,137; 7,599,878; 7,634,439; 7,657,461; 7,693,747; 8,364,577;
and 8,392,317. Patents pending.
Other Ariba product solutions are protected by one or more of the
following patents:
U.S. Patent Nos. 6,199,050, 6,216,114, 6,223,167, 6,230,146,
6,230,147, 6,285,989, 6,408,283, 6,499,018, 6,564,192, 6,584,451,
6,606,603, 6,714,939, 6,871,191, 6,952,682, 7,010,511, 7,047,318,
7,072,061, 7,084,998; 7,117,165; 7,225,145; 7,324,936; 7,536,362;
8,364,577; and 8,392,317. Patents pending.
Certain Ariba products may include third party software or other
intellectual property licensed from a third party. For information
regarding software or other intellectual property licensed from a
third party, go to http://www.ariba.com/copyrights.cfm.