Item Conversion – A Technical Note


This conversion is done for import items from any source into Oracle Inventory and Oracle Engineering.

Description:

When you import items through the Item Interface, you create new items in your item master organization or assign existing items to additional organizations. You can specify values for all the item attributes, or you can specify just a few attributes and let the remainder default or remain null. Here we are importing items with template.

Interface Table:

  • The interface table for Item Import is MTL_SYSTEM_ITEMS_INTERFACE and it contains every column in the Inventory item master table, MTL_SYSTEM_ITEMS.
  • The columns in the item interface correspond directly to those in the item master table.
  • Except for ITEM_NUMBER or SEGMENTn columns, ORGANIZATION_CODE or ORGANIZATION_ID, DESCRIPTION, PROCESS_FLAG, and TRANSACTION_TYPE, all other columns are optional, either because they have defaults that can be derived, or because the corresponding attributes are optional and may be left null.
  • If you are importing revision details for your new items, you can use the MTL_ITEM_REVISIONS_INTERFACE table. This table is used only for revision information, and is not mandatory.

Required Data:

ITEM_NUMBER or SEGMENTn columns

Every row in the item interface table must identify the item and organization. To identify the item when importing it, you may specify either the ITEM_NUMBER or SEGMENTn columns—the Item Interface generates the INVENTORY_ITEM_ID for you.

ORGANIZATION_ID or ORGANIZATION_CODE

You need to specify either the ORGANIZATION_ID or ORGANIZATION_CODE that identifies the organization.

DESCRIPTION

When you import a new item, you are also required to specify the DESCRIPTION.

TRANSACTION_TYPE & PROCESS_FLAG

There are two other columns the Item Interface uses to manage processing. They are TRANSACTION_TYPE, which tells the Item Interface how to handle the row, and PROCESS_FLAG, which indicates the current status of the row.

Always set the TRANSACTION_TYPE column to CREATE, to create an item record (true when both importing a new item and assigning an already existing item to another organization). This is the only value currently supported by the Item Interface. The Item Interface uses the PROCESS_FLAG to indicate whether processing of the row succeeded or failed. When a row is ready to be processed, give the PROCESS_FLAG a value of 1 (Pending), so that the Item Interface can pick up the row and process it into the production tables.

Meaning of PROCESS_FLAG Values:

Code Code Meaning
1 Pending
2 Assign complete
3 Assign/validation failed
4 Validation succeeded; import failed
5 Import in process
7 Import succeeded

Note:

When more than one of these columns has been entered and they conflict, ITEM_NUMBER overrides SEGMENTn and ORGANIZATION_ID overrides ORGANIZATION_CODE. It is strongly recommended that you use SEGMENT column instead of ITEM_NUMBER.

For performance purpose, it is advised to batch set of records using set_process_id column and then run import program for that set_process_id. The item import (IOI) program can be run in parallel if separate set_process_ids are passed while submitting.

Derived Data:

Many columns have defaults that the Item Interface uses when you leave that column null in the item interface table. Please refer Oracle Manufacturing APIs and Open Interfaces Manual for more details.

Validations:

1] Validation for organization code (standard table: ORG_ORGANIZATION_DEFINITIONS)

2] Validation for Item_number (standard table: mtl_system_items_b)

3] Validation for Description

4] Validation for Primary Unit of Measure (standard table: MTL_UNITS_OF_MEASURE)

5] Validation for Template Name (standard table: mtl_item_templates)

6] Validation for Item Type (standard table: FND_COMMON_LOOKUPS)

7] Validation for ENCUMBRANCE_ACCOUNT (standard table: gl_code_combinations)

8] Validation for EXPENSE_ACCOUNT (standard table: gl_code_combinations)

Note: You can add more validation as per your business requirement.

Record Insertion:

Through your custom program you can insert the below columns in the interface table. Again the list is a sample one; you can add additional columns as your business requirement.

Columns inserted:

  • ORGANIZATION_ID                     –>Taken from ORG_ORGANIZATION_DEFINITIONS table
  • ORGANIZATION_CODE               –>Taken from Staging table
  • LAST_UPDATE_DATE                 –>sysdate
  • LAST_UPDATED_BY                   –>fnd_global.user_id
  • CREATION_DATE                        –>sysdate
  • CREATED_BY                             –>fnd_global.user_id
  • LAST_UPDATE_LOGIN                 –>fnd_global.login_id
  • DESCRIPTION                             –>Taken from Staging table
  • SEGMENT1                                 –>Taken from Staging table
  • PRIMARY_UOM_CODE                 –>Taken from Staging table
  • PRIMARY_UNIT_OF_MEASURE     –>Taken from MTL_UNITS_OF_MEASURE
  • ITEM_TYPE                                  –>NULL
  • TEMPLATE_NAME                       –>Taken from Staging table
  • TEMPLATE_ID                             –>Taken from mtl_item_templates        
  • MIN_MINMAX_QUANTITY             –>Taken from Staging table
  • MAX_MINMAX_QUANTITY            –>Taken from Staging table
  • LIST_PRICE_PER_UNIT                –>Taken from Staging table
  • ITEM_CATALOG_GROUP_ID        –>Taken from Staging table
  • SET_PROCESS_ID                       –>1
  • PROCESS_FLAG                          –>1
  • TRANSACTION_TYPE                  –>‘CREATE’

Standard Concurrent Program:

After you insert valid data into Interface table, you can go to Items > Import > Import Items and run the standard concurrent program. Here is the parameter form.

1] All Organizations:

  • Yes: Run the interface for all organization codes in the item interface table.
  • No: Run the interface only for the organization you are currently in. Item interface rows for organizations other than your current organization are ignored.

2] Validate Items:

  • Yes: Validate all items and their data residing in the interface table that have not yet been validated. If items are not validated, they will not be processed into Oracle Inventory.
  • No:  Do not validate items in the interface table.

3] Process Items:

  • Yes: All qualifying items in the interface table are inserted into Oracle Inventory.
  • No: Do not insert items into Oracle Inventory.

4] Delete Processed Rows:

  • Yes: Delete successfully processed items from the item interface tables.
  • No: Leave all rows in the item interface tables.

5] Process Set:

Enter a number for the set id for the set of rows you want to process. The program picks up the rows marked with that id in the SET_PROCESS_ID column. If you leave this field blank, all rows are picked up for processing regardless of the SET_PROCESS_ID column value.

Working with failed interface rows:

If a row fails validation, the Item Interface sets the PROCESS_FLAG to 3 (Assign/validation failed) and inserts a row in the interface errors table, MTL_INTERFACE_ERRORS. To identify the error message for the failed row, the program automatically populates the TRANSACTION_ID column in this table with the TRANSACTION_ID value from the corresponding item interface table.

The UNIQUE_ID column in MTL_INTERFACE_ERRORS is populated from the sequence MTL_SYSTEM_ITEMS_INTERFACE_S. Thus, for a given row, the sequence of errors can be determined by examining UNIQUE_ID for a given TRANSACTION_ID.

You should resolve errors in the sequence that they were found by the interface, that is, in increasing order of UNIQUE_ID for any TRANSACTION_ID.

Resubmitting an Errored Row:

During Item Interface processing, rows can error out either due to validation (indicated by PROCESS_FLAG = 3 in MTL_SYSTEM_ITEMS_INTERFACE and the corresponding error in MTL_INTERFACE_ERRORS) or due to an Oracle Error.

When an Oracle Error is encountered, the processing is stopped and everything is rolled back to the previous save point. This could be at PROCESS_FLAG = 1, 2, 3, or 4.

When you encounter rows errored out due to validations, you must first fix the row corresponding to the error with the appropriate value. Then reset PROCESS_FLAG = 1, INVENTORY_ITEM_ID = null, and TRANSACTION_ID = null. Then resubmit the row for reprocessing.

Useful Query:

Select
  SEGMENT1,
   DESCRIPTION,
   PROCESS_FLAG,
   SET_PROCESS_ID,
   INVENTORY_ITEM_ID,
   ORGANIZATION_ID,
   ORGANIZATION_CODE,
   CREATION_DATE,
   ITEM_TYPE,
   UNIT_OF_ISSUE,
   TEMPLATE_ID,
   TEMPLATE_NAME,
   EXPENSE_ACCOUNT,
   ENCUMBRANCE_ACCOUNT,
   PRIMARY_UOM_CODE,
   PRIMARY_UNIT_OF_MEASURE,
   MIN_MINMAX_QUANTITY,
   MAX_MINMAX_QUANTITY,
   TAX_CODE,
   REQUEST_ID
from
    MTL_SYSTEM_ITEMS_INTERFACE
order by CREATION_DATE;
----------------------------
Select
   ORGANIZATION_ID,
   UNIQUE_ID,
   REQUEST_ID ,
   TABLE_NAME ,
   COLUMN_NAME,
   ERROR_MESSAGE
   CREATION_DATE,
   MESSAGE_TYPE
from
    MTL_INTERFACE_ERRORS
order by CREATION_DATE;

Useful Metalink Note:

  1. FAQ for Item Import [ID 109628.1]
  2. Error in Validating MTL_SYSTEM_ITEMS_INTERFACE [ID 1057175.6]
  3. A Guideline to IOI Error Messages and Solutions [ID 52746.1]
  4. How to Import Item Costs Using the Item Open Interface (IOI) [ID 191376.1]
  5. Item Import Performance Tips [ID 66496.1]
  6. INCOIN: Basic Steps for Researching Failed Item Imports [ID 552683.1]
  7. Troubleshooting Guide for INV_IOI_MASTER_CHILD Errors in Item Import [ID 429924.1]
  8. How To Prepare to Run Parallel Runs of the Item Import Interface INCOIN [ID 842767.1]
  9. Item Import Gives Error: “The Default Primary Unit of Measure Is Invalid” [ID 789927.1]
  10. Understanding Item Import and Debugging Problems with Item Import [ID 268968.1]
  11. How to create a Category Set and Assign Items to Categories [ID 423551.1]
  12. Item API Or Concurrent Manager, Which One Is Best For Item Creation? [ID 760498.1]
Advertisements

GL Budget Interface


GL Budget Interface

This program lets you prepare and analyze your budget outside of General Ledger and then transfer your budget information into General Ledger. This enables you to perform your budgeting in the environment you choose, and still maintain the integrity of your database.

Interface Table:

GL_BUDGET_INTERFACE is used to upload budget data into your Oracle General Ledger application from an external source. Each row includes one fiscal year’s worth of budget amounts for an account. When you load this table, you must supply all NOT NULL columns with data. In addition, you must supply a valid account combination in the SEGMENT columns appropriate to your chart of accounts. Finally, you must supply the budget amounts in the appropriate AMOUNT columns.

The mandatory (not null) columns of the Interface table are:

  • BUDGET_NAME
  • BUDGET_ENTITY_NAME (the budget organization)
  • CURRENCY_CODE
  • FISCAL_YEAR
  • UPDATE_LOGIC_TYPE (A for Add, R for Replace)

Other important columns are:

  • SET_OF_BOOKS_ID
  • BUDGET_VERSION_ID
  • PERIOD1_AMOUNT through PERIOD60_AMOUNT
  • SEGMENT1 through SEGMENT30

Validations:

  • Budget Name and Budget Entity Name
  • Currency Code
  • Account Segments(Code Combination in GL_CODE_COMBINATIONS Table)

To upload a budget:

  1. Navigate to the Upload Budget window (Budgets > Enter > Upload).
  2. Enter the Budget and Budget Organization.
  3. click Upload.

General Ledger submits a concurrent process to upload budget information from the GL_BUDGET_INTERFACE table.

Budget Upload Validation:

Budget Upload validates all of your budget information for compatibility with General Ledger. Budget Upload also checks to make sure that the following conditions are true:

• Your account is assigned to a budget organization

• The budget entry type for your account is Entered

• Your budget is not Frozen

• Your budget organization is not Frozen

• Your budget fiscal year is open for your budget

Once updated, General Ledger automatically deletes the rows of budget records in the Budget Interface table.

Base Tables:

  • GL_BUDGETS
  • GL_BUDGET_ASSIGNMENTS
  • GL_BUDGET_TYPES

For more information see Oracle General Ledger User Guide

Data Migration vs. Data conversion


Data Migration vs. Data conversion

When we need to enter data into oracle Apps, following are the few techniques:

• The Data can be entered using the application Screens (for small amount of data, like creating PO, entering sales orders using Oracle Apps screens).
• The data can be entered using Oracle’s Open System Interface (for regular operations e.g. for moving data from one module to another).
• The data can be stored in the database table directly (Not recommended by oracle and can be very risky, because on any event data is going to be stored in many tables and data should be validated before inserting into tables that may cause data integrity and inconsistency problem, sometimes it may corrupt the data completely.).
• Using third party tools like data loader (It is also can be used when data is relatively small (25-200 records) because it captures the keystrokes and works like manually entering the data into Oracle form but much faster as process is automated).

What is the need of Migration/Conversion?

Migration/Conversion are required when we are upgrading to one version to another (e.g. Oracle Apps 11.5.7 to Oracle 11.5.10) or moving data from some legacy system to Oracle Apps. There will be bulk  of data (sometimes millions or even more than that) that needs to be moved from one system to another  and  before moving the data it should be validated and only valid records should be entered into Oracle Apps.

If both the systems (Target and source) are not having same structure for data (Tables are not same/Table Structure is not same/The data is being stored in database is not same), it needs to be translated (e.g. upgrading from Oracle 11i to R12 where table structures are not same) then we say it as conversion (any kind of translation of data on Source data to make it suitable for Target system) otherwise migration.

 

What is Migration?

 

Migration of data means moving the data from one system to another using Interface Programs/APIs where both the systems have same structure of data.

Process of Migrating of data:
• Identify the data to be imported to new system (Business requirement).
• Extract the data into flat file/Staging table
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface)

What is Conversion?

Conversion of data means translating the data to suite target system (data should be formatted according to target system )  and then move the translated data using Interface Programs/APIs.
• Identify the data to be imported to new system (Business requirement).
• Extract into flat file/Staging table
• Translate/Convert/Format the data
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface) and then launch standard Interface concurrent program to load the data to Oracle Apps Base Tables
• If using API, fetch the data, validate it and then call API to import the data

 

How conversion/Migration and interface differ?

 

There are good numbers of parameter on which they can be categorized. Take few of them:
Frequency
• Conversions/Migration are a one time event
• interfaces are ongoing
Occurrence in the project timeline
• conversions/Migration executed before production
• interfaces executed during production
Manner of execution
• Conversions/Migration are batch
• Interfaces may be batch or real time
Complexity
• Conversion/Migration does have very complex, it’s totally depends upon the data mapping activity.
• Coordinating with other systems make interfaces more complex
Maintenance
• Maintenance of interface is bit cost intensive task.

 

ABOUT INTERFACES


 

ABOUT INTERFACES:

In Oracle Apps Interfaces are generally tables, which act as a medium to transfer the data from one module to another module or to transfer the data from legacy system into Oracle Applications. There are 352 tables provided by the Oracle Package. Each module has its own Interface Tables.

A typical path to transfer the data from Legacy System to Oracle Apps:

What is Interfacing?

It is the process of converting the records from one format to another format. The main components of this interfacing are

• Transfer Program

• Interface Table and

• Import Program

A] Transfer Program:

If the source modules data are implemented in Oracle Applications then the Transfer Programs are integrated with the Package. If the source modules are implemented in external system (i.e. other than Oracle Applications) then we have to develop our own Transfer Programs. Generally these Transfer Programs are developed using PL/SQL, JAVA or SQL Loader.

What they do?

  • It maps the columns of source table with the columns of Interface Tables.
  • It performs Row Level and Column Level validations.
  • It transfers the data from Source to the Interface Table.

B] Interface Tables:

The Interface tables basically have 4 types of columns.

  1. Mandatory Columns.
  2. Conditionally Required Columns.
  3. Optional Columns.
  4. Internal Processing Columns.

Mandatory Columns:

These are the main columns which are required in the destination tables (i.e. Oracle Application Module Tables). With the help of mandatory columns only the Import Program will converts the records from source to destination.

Conditionally Required Columns:

The values for these columns are based on the values of Mandatory columns. For Example: If you are converting foreign currency transactions to INR then it as compulsory to provide conditionally required columns like Currency conversion rate, Conversion Time and Conversion Date.

Optional Columns:

These are used when a client wanted to transfer some additional information from source to destination. These are based on client’s requirement.

Internal Processing Columns:

Status and Error Message columns are called Internal Processing Columns. These are specific only to Interface Table. These columns are going to be used by the Import Program to update the status and error message, if the record fails its validation while importing from Interface Table to the Destination Table.

C] Import Program:

For all Interface Tables, Oracle Application Package is going to provide Import Programs. These are generally registered with destination modules. These Import Programs are designed using PL/SQL, JAVA, C, C++, etc.

What they do?

  • It maps the columns of the Interface Table with one or more columns in the destination table.
  • It performs row level and column level validation.
  • It imports the data from Interface Table to the Destination tables, if the records validated successfully.
  • It deletes all the successfully validated records from Interface Table.
  • If the record fails its validation then the Import Program will update the status and error message columns of Interface Table.

Interface Vs. Application Program Interface (API):

Interfaces are used to transfer the data from legacy system to Oracle Application system where as API is used to convert the data from one form to another form with in the Oracle Application Module.

Previous Post:

 

Interfaces in Oracle Application: An Introduction


Interfaces in Oracle Application: An Introduction

 

What are Interfaces?

  • Interfaces are used in Oracle Applications to integrate external systems and Data Conversion.
  • The interfaces are mainly used to either transfer data from Oracle Applications to a flat file or data from legacy system to Oracle Applications.
  • Used extensively at the time of Data Conversion from legacy/ old systems to a fresh implementation of Oracle Applications.
  • Used also at regular intervals when data transfer is from other live systems if the systems are not defined in Oracle Applications implementation.
  • Oracle provides flexible and flexible tools in the form of Interface programs to import the master and transactional data like Customers, Invoices, and Sales Orders etc from external systems into Oracle Applications.

Types of Interfaces

There are two major types of Interfaces:

  • Inbound Interface : These interfaces are used to transfer data from external systems to Oracle Applications.
  • Outbound Interface :  These interfaces are used to transfer data from Oracle Applications to external systems.

Two other distinctions of Interfaces:

  • Open Interface: If the interface logic is provided by Oracle Applications, it is called an Open Interface.
  • Custom Interface: If the interface logic needs to be developed by the implementation team, it is called a Custom Interface.

Interface Components

Open Interface Logic

  • First the data from the source application is loaded into a database table (called Interface table).
  • Then the provided validation program logic validates the records whether they are correct or not .
  • If the validation fails, the errors are transferred into another table (called Error Table).
  • If the validation succeeds, the correct records are transferred through a process into the destination application table.

Components of an Interface

a] Source Application:

You obtain data from a source application to pass on to a destination application for further processing and/or storage.

b] Source Data Issues:

Type of file, Size, Frequency of upload, Record Length (Variable or fixed), Delimiter, Datatype for each field, Any unwanted data, Naming convention and uniqueness of file, Location of the file, Access on the file.

c] Destination Application:

You send data to a destination application so that the application can perform further processing and/or storage.

d] Interface Table:

For inbound interfaces, the interface table is the intermediary table where the data from your source application temporarily resides until it is validated and processed into the destination application.

e] Identifier columns:

Uniquely identify rows in the interface table provide foreign key reference to both the source and destination applications.

f] Control Columns:

  • Control columns track the status of each row in the interface table, as it is inserted, validated, rejected, processed, and ultimately deleted.
  • WHO columns are also control columns.

g] Data Columns:

  • Stores the data that is being converted.
  • Required columns store the minimum information needed by the destination application to successfully process the interface row.

h] Derived Columns:

Derived columns are created by the destination application from information in the required columns.

i] Optional Columns:

Optional columns are not necessarily required by the destination application, but can be used by the destination application for additional value-added functionality beyond the basics.

j] Error Table:

  • For inbound interfaces, the errors table stores all errors found by the validation and processing functions.
  • In some cases, the errors table is a child of the interface table. This allows each row in the interface table to have many errors, so that you can easily manage multiple errors at once.
  • In other cases, the errors are stored in a column within the interface table, which requires you to fix each error independently.

Developing an Interface

1] Identification:

Find out if there exists an Open Interface to carry out the functionality.

2] Creation of Pre-Interface table ( staging Table):

A table in the format of the data file which can be pruned to load as clean a data into the Interface table.

3] Load data into Pre-Interface table:

SQL*LOADER can be used to load the flat file into the pre-interface table.

4] Validate data in the Pre-Interface table:

Basic validation of the data loaded into the Pre-Interface table can be carried out like:

  • For checking NULL values in required columns
  • Checking for Foreign Key and Quick Code values.
  • Duplication Validation
  • Business Rule validation

5] Mapping the values:

Generated fields in Oracle Applications can be mapped in this step to either default values or sequences.

6] Load data into Interface table:

  • Once the data is as clean as you can get it, the data can be inserted into the Interface table.
  • At such a time, certain columns, which are necessary in Applications but not found in legacy system, need to be populated accordingly like WHO columns.

7] Run the interface program

8] Check for Errors

9] Report on the Interface