2 AIP Integration

The AIP solution uses the following Oracle Retail integration tools to integrate within AIP and also with other Oracle Retail products:

• Oracle Retail Integration Bus (RIB)

• Oracle Retail Extract, Transform, and Load (RETL)

AIP is distributed across two platforms: the RPAS and Java/Oracle platforms. There are two types of integration in AIP: external and internal integration.

External Integration

The AIP solution integrates with the Oracle Retail Merchandising System (RMS), Oracle Retail Demand Forecasting (RDF), Oracle Retail Analytic Science (ORASE) and Oracle Retail Replenishment Optimization products.

Figure 2-1 AIP External Integration

Table 2-1 describes the external integration between AIP and other products as shown in Figure 2-1.

Table 2-1 External Integration Between AIP and Other Products

Phase	Integration between...	Description
1	RMS and AIP using RETL	The data required by AIP from RMS is extracted. The data from RMS Oracle tables are transformed into an AIP compatible format (text files) using the custom script that calls the Oracle Retail Extract, Transform, and Load (RETL). The extracted text files are placed in the common directory for AIP batch to access these files. AIP batch scripts further processes these text files and loads them into the RPAS platform and AIP Online platform. Note: Part of the RMS data is loaded into DM Online during the internal integration of AIP.
2	RDF and AIP	The retailer should run the RDF extract scripts to create the forecasting measure data flat files in the format AIP expects. Then the AIP batch scripts load the RDF measures into the RPAS database.
3	AIP and RMS using RIB	After AIP has completed a replenishment plan across the retailer's supply chain the plan is passed to OM. OM determines which purchase orders and transfers have met their lead time and therefore must be passed to RMS for execution. The purchase orders that are manually created in OM Online must also be passed to RMS. All purchase orders and transfers that are to be executed in RMS are passed to RMS using the Oracle Retail Integration Bus (RIB). RIB is a near real-time data synchronization solution used by AIP for publishing orders to RMS. AIP publishes two sets of order messages to RIB: purchase orders and transfers. RMS subscribes to the RIB messages and inserts the orders into the appropriate RMS purchase order and transfer tables.
4	AIP and RO	RO is a replenishment optimization system and the integration of AIP and RO is an optional setup for Retailers. Lead Time, Review Time and Preferred Pack Size data is exported out of AIP in flat files for use when optimizing the replenishment settings. AIP then loads the optimized replenishment parameters from flat files provided by RO. The integration with RO occurs infrequently such as monthly or quarterly. Retailers who do not run RO should exclude the related AIP batch scripts from their job scheduler tasks.
5	AIP and ORASE	The integration of AIP and Oracle Retail Analytic Science Engine (ORASE) is optional set up for Retailers. Demand Transfer Percentage data is exported from ORASE to AIP for use in substitution.

Internal Integration

The process of integrating the AIP Java/Oracle platform with the AIP RPAS platform is called internal integration.

Figure 2-2 AIP Internal Integration

AIP uses RETL integration tool to integrate the AIP modules that are spread across both the platforms. RETL is used because it provides the flexibility to handle large amounts of data flow across the RPAS database and the AIP Online (Oracle) platforms.

Table 2-2 describes the internal integration between AIP components as shown in Figure 2-2.

Table 2-2 Internal Integration Between AIP Components

Phase	Integration	Description
1	Supply Chain Configuration	The AIP Planner uses the DM Online application to initially set up and maintain the supply chain by establishing the connectivity between the stores, warehouses, and suppliers. This is done after the initial batch of AIP is run. The DM Online application is also used for other configurations that are used during the AIP batch execution. For example, the retailer introduces new warehouses and stores in RMS. They need to be configured in the DM Online application to establish the sources of the warehouses and stores as well as the many other configurations that the AIP solution needs to know prior to the AIP replenishment planning calculations. Note: The RMS information is passed into DM Online during the AIP Batch execution (cron_import).
2	RETL Extract Scripts	The RETL extract scripts are executed during the batch process to move the DM Online parameters from the AIP Oracle database to an outbound staging directory. The client's job scheduler FTPs or copies the files in the directory to the inbound directory of the AIP RPAS platform.
3	SRP and WRP	The store receipt plan and the warehouse receipt plan are exported out of the AIP RPAS platform for transfer by a client's job scheduling process to the Order Management module in the Java/Oracle platform. Also, RMS data required in DM on the Java/Oracle platform are also exported out of the RPAS platform for transfer to the Oracle database.

Internal Integration Architecture

The internal integration allows the two AIP databases to communicate data that must be shared by both. The interface into the Oracle database uses the following technologies:

• Oracle Retail Extract, Transform, and Load (RETL) scripts

• Oracle PL/SQL packages

• Shell scripts

The interface to the RPAS database uses the following technologies:

• Shell scripts

• RPAS utilities

As shown in Figure 2-3, the data is communicated between the databases on a nightly basis. Once data is exported the client's scheduler should transfer the outbound files to the receiving platform/directory. Then, another scheduled job in client's scheduler executes the shell scripts that trigger the import processing of the files.

Figure 2-3 AIP Interfaces

Exporting

The export process involves running the export RETL scripts to create the outbound data files. The process compresses them into two files, and then places the files in a staging area where they can be moved to the RPAS platform, through file copy or FTP, by a job scheduling application. RETL can export the data directly from the Oracle tables, with no additional processing.

Note: When RETL creates outbound data files directly from an Oracle database with multi-byte characters, warning messages are written to the log file regarding a mismatch between input and output field lengths. This is due to a difference in how RETL and Oracle interpret multi-byte characters and does not represent a problem with the export process.

Figure 2-4 Exporting from the Oracle Database

Importing

The import process is slightly more complex than the export process. The data files must be moved through file copy or FTP by a job scheduling application from the RPAS server location. The files are then decompressed before they are processed through RETL. Due to the required additional pre-insert processing, RETL is not able to perform a direct import to the database. For instance, duplicate entries must be checked so that updates are performed, rather than inserted. This introduces another layer of processing in the form of PL/SQL packages embedded in the database. Data is first imported to the staging tables by RETL, and then the PL/SQL logic is executed to update the master tables.

Figure 2-5 Importing into the Oracle Database

RETL Configuration

The interface process is designed to be fully automated once configured, with support from a retailer's custom process for moving the files between the AIP Oracle and AIP RPAS server locations. The config.xml needs to be configured to the specific environment. This file is located under the root integration directory (integration/).

Note: Refer to the Oracle Retail Advanced Inventory Planning Installation Guide for installation instructions. Refer to the Oracle Retail Advanced Inventory Planning Implementation Guide for configuration details.