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]

Oracle Alerts- Few FAQs

What are Oracle Alerts?

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want.

What they are capable of?

• Keep you informed of database exception conditions, as they occur.
• Take predefined actions when it finds exceptions in your database, without user intervention.
• Take the actions you specify, depending upon your response to an alert message.
• Perform routine database tasks automatically, according to the schedule you define.
• Keep you informed of exception conditions through Email.

What are the types of Alert?

You can define one of two types of alerts: an event alert or a periodic alert.

• An event alert immediately notifies you of activity in your database as it occurs.
• A periodic alert, on the other hand, checks the database for information according to a schedule you define.

What are the alert actions available?

• Sending the retrieved information to someone in an Email.
• Run a concurrent program
• Run an operating script
• Run a SQL Statement script

What is On-Demand periodic alert?

It is a periodic alert with frequency as ‘On-Demand’. That means there is no specific period assigned to this alert and you can run this alert at any time you want using Request Periodic Alert Check form.

What you specify in the Alert Details window?

• Default values for inputs variables
• Additional characteristics for output variables
• Application installations information you want the alert to run against

What are the Action Levels for your alert actions?

There are three types of level for your action: Detail, Summary and No Exception.

During an alert check, a detail action performs once for each individual exception found, a summary action performs once for all exceptions found, and a no exception action performs when no exceptions are found.

What is Action Set?

An action set can include an unlimited number of actions and any combination of actions and action groups for your alert. You can define as many action sets as you want for each alert. Oracle Alert executes the alert Select statement once for each action set you define. During each action set check, Oracle Alert executes each action set member in the sequence you specify.

What is Distribution List in Oracle Alert?

Distribution lists let you predefine a set of message recipients for use on many actions. If a recipient changes, you need only adjust it in the distribution list, not in the individual message actions.

What is Summary Threshold?

• Oracle Alert can automatically determine whether to perform a detail or summary action, depending upon the number of exceptions found by the alert Select statement.
• If you define a summary threshold, Oracle Alert performs a detail action for each exception found by the Select statement, but if the number of exceptions found exceeds the summary threshold, Oracle Alert performs a summary action.
• You need to first define a detail and a summary action, include them in a threshold group, and then specify a summary threshold.

What is Periodic Set?

You can create a set of periodic alerts that Oracle Alert checks simultaneously. Use the Request Periodic Alert Check window to check the periodic set. Note that each periodic alert you include in a periodic set continues to run according to its individually defined frequency.

What is Response Processing in Alert?

Oracle Alert can process responses to your alert messages. When Oracle Alert receives a response to a specific alert message, it automatically performs the actions you define. Optionally, respondents can supply values that Oracle Alert uses to perform these actions. Response processing lets you automate routine user-entry transactions, streamlining your organization’s operations.

Note: To enable response processing, ensure that you have performed the required setup steps.

What is Action Escalation?

You can define a set of escalating detail actions, called an escalation group, for Oracle Alert to perform when it finds the same exceptions during consecutive alert checks. Oracle Alert performs a different detail action each time it encounters the same exception, so you can define actions that correspond to increasing severity levels.

How event alert works?

Once you define an event alert to monitor a table for inserts and/or updates, any insert or update to the table will trigger the event alert. When an insert or update to an event table occurs, Oracle Alert submits to the concurrent manager, a request to run a concurrent program called Check Event Alert (ALECTC). The concurrent manager runs this request according to its priority in the concurrent queue. When the request is run, Check Event Alert executes the alert Select statement. If the Select statement finds exceptions, Check Event Alert performs the actions defined in the enabled action set(s) for the alert. If the Select statement does not find any exceptions, Check Event Alert performs the No Exception actions in the enabled action set(s) for the alert.

My Experiments with Light

A beautiful day!

One day, with few of my friends visited an Orphanage near pune, which is purely dedicated to take care about 200 children. We spent a memorable evening with them as we saw them how they are happy with small thing in life. Here are few pics of them.

This slideshow requires JavaScript.