How to develop Open Replenishment Interface

 From Oracle Manufacturing APIs and Open Interfaces Manual

How to develop Open Replenishment Interface

            Oracle Inventory provides an open interface for you to easily load replenishment requests from external systems such as a bar-code application. Such requests may be in the form of stock-take counts or requisition requests for subinventories in which you do not track quantities.

You may also use the Replenishment Interface to process requisition requests generated by external applications for tracked subinventories.

Functional Overview

            You must write the load program that inserts a single row for each replenishment count/request into the MTL_REPLENISH_HEADERS_INT table. A record for each item included in the count header must be inserted into the MTL_REPLENISH_LINES_INT table. There are two modes you can use to send your replenishment counts through the interface. These are Concurrent and Background modes.

            Under Concurrent mode processing, you populate the interface tables for a specific replenishment count and then call the replenishment validator from the Oracle Inventory menu (Counting/Replenishment Counts/Process Interface). The validator processes the replenishment count specified as a parameter at process submission, validating rows in both the MTL_REPLENISH_HEADER_INT and MTL_REPLENISH_LINES_INT tables. The validator derives any additional columns and updates the error flag if an error is detected.

            For Background mode processing, you populate the interface tables and then let the Replenishment Validator asynchronously poll the tables for replenishment counts to process.

            If the replenishment count, both header and lines, passes all required validation, the records are inserted into the MTL_REPLENISH_HEADERS and MTL_REPLENISH_LINES tables and are deleted from the interface tables. If an error is detected during the validation process, the header and corresponding replenishment lines will be left in the interface table.

            Once the lines are in the internal replenishment tables, you use the Replenishment Processor as described in the Oracle Inventory User’s Guide to process the counts and create requisitions. See: Entering and Processing Replenishment Counts, Oracle Inventory User’s Guide.

Setting Up the Replenishment Interface

            Access the Replenishment Interface through the Oracle Inventory menu (Counting/Replenishment Counts/Process Interface). Select the type of request by choosing Single Request. In the Request Name field, select Validate Replenishment Interface. In the Parameters window, select Concurrent or Background as the Processing Mode and select the Count Name for processing. Select Submit Request to begin processing. You can also use the Schedule button to specify resubmission parameters that will control how frequently the Replenishment Validator polls for records in the interface tables.

Inserting into the Replenishment Interface Tables

            This section provides a chart for each interface table that lists all columns, followed by a section giving a brief description of a subset of columns requiring further explanation. The chart identifies each column’s datatype and whether it is Required, Derived, or Optional.

            Several of the attributes in the interface tables can be populated using either the user-friendly values or the internal identifiers. For example, you have the choice of specifying either the flexfield segment representation or the internal identifier (e.g. INVENTORY_ITEM_ID) for the required value. When specifying the organization, you may either use the organization code or the internal identifier (e.g. ORGANIZATION_ID).

            If you populate the user friendly values, the Replenishment Validator will validate them and will derive the internal identifiers. If the translation is available to the external system, it may be advantageous to use the internal identifiers to improve performance.

Replenishment Headers Interface Tables

The following graphic describes the MTL_REPLENISH_HEADERS_INT table:

Table 7–8 Oracle Inventory Replenishment Headers Interface

Column Name Type Required Derived Optional

REPLENISHMENT_HEADER_ID Number 3

REPLENISHMENT_COUNT_NAME Varchar2(10) 3

COUNT_DATE Date 3

LAST_UPDATE_DATE Date 3

ERROR_FLAG

If a validation error occurs, the replenishment validator populates this column with an error code. The error flag for a replenishment header will be set if either the validation of the header fails or if the validation of any of the lines of the header fails.

ORGANIZATION_ID

This column identifies the internal identifier of the organization from which the

replenishment count originated. You must enter either the internal organization identifier or the user friendly organization code.

CREATION_DATE Date 3

CREATED_BY Number 3

LAST_UPDATE_LOGIN Number 3

LAST_UPDATED_BY Number 3

ORGANIZATION_ID Number 3

ORGANIZATION_CODE Varchar2(3) 3

SUBINVENTORY_CODE Varchar2(10) 3

SUPPLY_CUTOFF_DATE Date 3

PROCESS_STATUS Number 3

PROCESS_MODE Number 3

ERROR_FLAG Number 3

REQUEST_ID Number 3

PROGRAM_APPLICATION_ID Number 3

PROGRAM_ID Number 3

PROGRAM_UPDATE_DATE Date 3

DELIVERY_LOCATION_ID Number 3

DELIVERY_LOCATION_CODE Varchar2(20) 3

Table 7–8 Oracle Inventory Replenishment Headers Interface

Column Name Type Required Derived Optional

ORGANIZATION_CODE

This column is the user friendly code for the organization that is the source of the replenishment count. It may be used instead of the internal identifier, in which case the internal identifier will be derived.

PROCESS_MODE

This column determines how the interfaced replenishment count will be processed.

The valid options are:

2 - Concurrent

3 - Background

Interface replenishment counts marked for Background processing will be picked up by the replenishment validator polling process. The validator will pick up and process all replenishment counts with a process mode of Background each time it runs.

You use Concurrent processing mode if you want to launch a dedicated

replenishment validator process to explicitly process a single replenishment count, identified as a parameter to the program, from the interface table.

PROCESS_STATUS

This column is used to identify the current processing status of the replenishment count. You should insert rows that you intend to be processed with a value of 2 (Pending). The valid values for this column are:

1. Hold

2. Pending

3. Processing

4. Error

5. Completed

If you want to insert records into the interface tables but temporarily prevent them from being processed, you can use this column by setting the value to 1 (Hold). After the validator has run, it will set the value of this column to 5 (Completed). This status is used whenever the process completes, whether validation errors were detected or not.

A status of 4 (Error) indicates an internal error occurred. This error indicates an exceptional condition and should not occur.

REPLENISH_HEADER_ID

Enter a unique identifier for the replenishment count. This column is used to group the lines of a replenishment count with the header. You may use the sequence MTL_REPLENISH_HEADERS_S to obtain a unique identifier.

REPLENISH_COUNT_NAME

Enter a unique name for the replenishment count.

SUBINVENTORY_CODE

This column identifies the subinventory that is the source of the replenishment

count.

SUPPLY_CUTOFF_DATE

Enter the date after which planned supply will not be considered in available

quantity calculations. A null value here indicates that you do not want to consider planned supply when performing replenishment calculations.

DELIVERY_LOCATION_ID

Enter the internal identifier for the location to which the replenishment should be delivered. You may enter the delivery location identifier, the user friendly delivery location code or neither. If neither is specified, the default delivery location for the organization from which the replenishment originated is defaulted.

DELIVERY_LOCATION_CODE

Enter the user friendly code for the delivery location of the replenishment. You may enter this code instead of the internal identifier, in which case the internal identifier will be derived. You may specify neither the code or the identifier, in which case the default delivery location of the organization originating the replenishment will be used.

The following graphic describes the MTL_REPLENISH_LINES_INT table:

Table 7–9 Oracle Inventory Replenishment Lines Interface

Column Name Type Required Derived Optional

REPLENISHMENT_HEADER_ID Number 3

REPLENISHMENT_LINE_ID Number 3

ORGANIZATION_ID Number 3

Open Replenishment Interface

Oracle Inventory Open Interfaces and APIs 7-33

REPLENISHMENT_HEADER_ID

Enter the unique identifier of the replenishment count. The identifier entered here is the foreign key reference which links the header table with the lines table to associate a group of lines with a single header.

REPLENISHMENT_LINE_ID

Enter the identifier for the line within the replenishment count. You may use the sequence MTL_REPLENISH_LINES_S to obtain a unique identifier for the line.

INVENTORY_ITEM_ID

Enter the internal identifier for the item to be replenished.

LAST_UPDATE_DATE Date 3

CREATION_DATE Date 3

CREATED_BY Number 3

LAST_UPDATE_LOGIN Number 3

LAST_UPDATED_BY Number 3

INVENTORY_ITEM_ID Number 3

SEGMENT {1-20} Varchar2(40) 3

COUNT_TYPE_CODE Number 3

COUNT_QUANTITY Number 3

REFERENCE Varchar2(240) 3

ERROR_FLAG Number 3

REQUEST_ID Number 3

PROGRAM_APPLICATION_ID Number 3

PROGRAM_ID Number 3

PROGRAM_UPDATE_DATE Date 3

COUNT_UNIT_OF_MEASURE Varchar2(25) 3

COUNT_UOM_CODE Varchar2(3) 3

Table 7–9 Oracle Inventory Replenishment Lines Interface

Column Name Type Required Derived Optional

Open Replenishment Interface

7-34 Oracle Manufacturing APIs and Open Interfaces Manual

SEGMENT{1-20}

You may use these flexfield columns instead of INVENTORY_ITEM_ID to enter the item identifier in a more user-friendly form.

ORGANIZATION_ID

This column identifies the internal identifier of the organization from which the

replenishment count originated. If you do not enter a value here, the organization dentifier will be derived from the replenishment header.

COUNT_TYPE_CODE

Enter the type of the replenishment count entry. The valid count types are:

1. On-hand Quantity

2. Order Quantity

3. Order Maximum

Use On-hand Quantity to identify counts that are the result of stock-takes of

subinventories in which you do not track on-hand quantities.

Use Order Quantity when you want to specify the quantity to be ordered. This

count type may be used with either tracked or non-tracked subinventories.

Use Order Maximum when you want to place an order for the min-max maximum

quantity specified for item in the subinventory specified. This count type may be

used with either tracked or non-tracked subinventories.

COUNT_QUANTITY

This column is used to specify the count quantity that corresponds to the count type

entered for the line. When the count type is On-hand Quantity, the count quantity

is the on-hand balance determined during the stock-take. When the count type is

Order Quantity, the count quantity represents the quantity to be ordered. This

column is not used when the count type is Order Maximum.

REFERENCE

Use this column to enter any replenishment count reference information.

Open Replenishment Interface

Oracle Inventory Open Interfaces and APIs 7-35

COUNT_UNIT_OF_MEASURE

Enter the count unit of measure identifier. This column may be used to specify the

full name for the unit of measure. This column is meaningful only when a value is

entered in the COUNT_QUANTITY columns.

COUNT_UOM_CODE

This column is represents the unit of measure code used for the count. You may

specify the code when populating this table or you may use the full name for the

unit of measure, in which case this column will be derived. This column is

meaningful only when a value is entered in the COUNT_QUANTITY columns.

ERROR_FLAG

This flag indicates the error status of the validation of a replenishment line. The

replenishment validator populates this column with a line corresponding to the

error detected during validation.

Validation

Oracle Inventory validates the following conditions:

¦ The value of REPLENISH_HEADER_ID must be unique among existing

replenishment counts

¦ The value of REPLENISH_COUNT_NAME must be unique among existing

count headers

¦ The value of LAST_UPDATED_BY must be a valid user name

¦ ORGANIZATION_ID must be a valid identifier of an organization

¦ SUBINVENTORY_CODE must refer to an existing subinventory

¦ DELIVERY_LOCATION_ID must be a valid identifier of a location associated

with the organization generating the replenishment

¦ There must be at least one line per header

¦ The ORGANIZATION_ID at the header level must be the same as that at the line level

¦ COUNT_TYPE_CODE must be either 1, 2, or 3 and must be consistent with

whether the subinventory is tracked or non-tracked

¦ The value of COUNT_QUANTITY must be consistent with COUNT_TYPE_

CODE and must be greater than zero

Open Replenishment Interface

7-36 Oracle Manufacturing APIs and Open Interfaces Manual

¦ INVENTORY_ITEM_ID must refer to a transactable item in the organization

specified

¦ The item must exist in the subinventory and must be min-max planned in that

subinventory

¦ The COUNT_UOM_CODE must be valid and conversions to primary UOM

must exist

¦ Each line must correspond to a header

Viewing Failed Transactions

Replenishment counts that fail the validation process will remain in the MTL_

REPLENISH_HEADERS_INT and MTL_REPLENISH_LINES_INT tables. You may

use SQL*PLUS to identify the headers that have failed by selecting those rows with

a process_status of 5 (Complete). The reason for the failure will be reflected in the

ERROR_FLAG column.

Possible values for the ERROR_FLAG column in the MTL_REPLENISH_

HEADERS_INT table are:

1 - Non-unique replenishment header id

2 - Non-unique replenishment count name

3 - Invalid user name

4 - Invalid organization identifier

5 - Invalid subinventory

7 - Header with no corresponding replenishment lines

10 - Header failed because line failed

18 - Delivery location is not valid

Possible values for the ERROR_FLAG column in the MTL_REPLENISH_LINES_

INT table are:

1 - No corresponding header id

3 - Invalid user name

8 - Invalid item identifier or item isn’t transactable

9 - Invalid unit of measure or no conversion to primary unit of measure exists

11 - No item specified in either identifier or segments

Open Replenishment Interface

12 - Invalid count type

13 - On-hand count type used for tracked subinventory

14 - Invalid count quantity

15 - Lines organization header does not match header organization identifier

17 - Item is not specified in the subinventory or is not min-max planned in the

subinventory

Fixing Failed Transactions

Frequently, errors in the interface are caused by problems external to the

replenishment count itself. For example, there may be validation that failed becausean entity that was being validated had the wrong status (i.e. disabled), or the failure could even be the result of a system error, such as running out of space. In these cases, the resolution is simple; once you have made the necessary changes, you simply need to resubmit the replenishment validator process.

If, however, you need to make changes to the data in the interface table, you need to either delete the failed records, correct them in the external feeder system and resubmit them, or update the interface record in the interface table using SQL*PLUS. When you resubmit updated transactions for processing, all validation will be performed again.