Oracle EBS inventory item import (inventory material import)

 

Import inventory item

The main purpose of material import/export is to import/export data between different systems, and to obtain data in a certain data information format and a certain transmission mode. The most important problem that must be solved during data import/export between different systems is data validation to ensure data availability between different systems. Based on the two-week study, we will summarize some problems encountered during item import and solutions.

The data import process is roughly divided into three parts:

(1) processing data files and inserting a custom interface table;

(2) It is used to process custom interface table data and insert standard interface table;

(3) Submit a standard request to import the standard table.

There are three possible import types:

(1) create materials

(2) Update Materials

(3) Organization allocation

APIS mainly involve the connection between custom and standard systems. Therefore, it is very important to be familiar with standard system tables, interface tables, and custom tables, the API also calls the system's Standard import request. The standard import data must comply with the system's data. Therefore, an important step in the API is to verify the data and process the data. Use the import of a master item as an example.

1. API call and output relationship

The starting point for importing an inventory master item to an API is the custom interface table, and the ending point is the standard table. The data sources of the custom interface table can be various, depending on the overall architecture of the Interface Program (Platform, whether in the form of files or information flows, after proper processing, import the custom interface table, and there must be a unique identifier of the Data batch and a unique identifier at the data row level. The concurrent program defined by the import program should be called after data processing. The required parameter is the batch ID of the data, concurrent Programs should return the data import processing status and necessary error/warning information (if any ).

2. API pseudocode

Begin

Obtain parameters;

Obtain data from the custom interface table based on the batch number;

For each record

Verify data;

Identify data creation, update, and allocation;

Import the system interface table;

End;

Submit standard system import requests;

Print the error report;

End

3. Main Tables/views and descriptions involved in master item import

Table Name	Select	Insert	Update	Delete	Description
Org_organization_definitions	X				System Organization definition
Mtl_system_items_ B	X	X	X		System Standard Material Base table
Mtl_system_items_ B _kfv					System Standard Material key elastic region View
Mtl_item_revisions_ B	X	X	X		System Standard Material revision base table
Mtl_item_categories_v	X				System Standard Material category View
Mtl_item_categories	X	X	X		System standard material Category Table
Mtl_system_items_interface	X	X		X	System Standard Material Interface table
Mtl_item_revisions_interface	X	X		X	System Standard Material revision form
Mtl_item_categories_interface	X	X		X	System standard material Category Table
Xxinv_item_int	X		X		Custom interface table
Mtl_interface_errors	X	X			System Standard Interface error table
Mtl_units_of_measure_vl	X				System standard item measurement unit Multi-Language View
Mtl_parameters	X				System Standard Organization parameter table
Mtl_default_category_sets_fk_v	X				System Standard category View
Mtl_category_sets	X				System Standard category table
Mtl_categories_ B _kfv	X				System Standard category View
Mtl_categories_kfv	X				Key elastic region view of system standard categories

4. Special Logic

(1) Verify the custom interface table data

The item import API obtains the corresponding data from the custom interface table based on the batch number of the data. The verification process is basically consistent with the standard of standard materials, although when a standard request is submitted, the system also verifies the data, but the standard request only has the input and output, and the process is invisible. Therefore, the validation in the Custom interface table can ensure the controllability of data errors. On the other hand, system requests are generally slow, especially scheduled periodic requests, and multiple batches are also easy to confuse. Therefore, if the verification in the custom table does not meet the requirements, return directly, saves the resources required for standard requests.

Major verifications include: non-empty verification, Organization verification, Organization verification, revision number verification, category verification, and so on. The tables or views involved are at the forefront.

(2) Whether it is a new material (creating materials/updating materials/organizing and allocating)

The judgment here determines how many times the import program needs to insert data to the standard interface table. The main reason is that if a new material does not exist in the system, you need to insert a piece of data into the main organization of the corresponding organization of this item, that is, the standard interface table has two identical materials with different organizations.

1. Create a material: the system standard material table does not exist and the standard interface table does not exist (The reason for judging the intermediate table of the standard interface table is that if the same batch of data is different from the same material and is a new material, if the system interface table is not determined, there will be multiple primary organization data in the system interface table. When a standard request is submitted, repeat errors will occur.)
2. Update material: the same material exists in the same organization.
3. Organization allocation: a uniform item exists in the main organization and does not exist in the sub-organization.

(3) standard requests

Material import request: item import (including item and revision Information)

CATEGORY allocation request: item category assignment open interface (category Information)

(4) Revision Processing

If revision information exists in the records of the custom interface table, revision must be processed. The new/old/allocation judgment is basically the same as (2. During this part, we found a problem where revision is of the string type in the standard system, so when the old revision number and the new revision number are compared, pay special attention to the number of digits. For example, the string '9' is larger than the string '10 ", but the '09' string is "smaller" than the '10' string ". After the verification is passed, import the Code directly to the standard interface table, and obtain the largest revision number.

The standard request for revision is combined in item import ).

(5) category Processing

The category is used to process the stored standard request separately. It is called the item category assignment open interface. The special logic here is summarized as follows:

1. New item with no category information: Default category assigned to the current category set
2. New material with category information: allocate this category to the main organization and the current sub-organization.
3. Update, no category information: not processed
4. Update with category information: update the category information of the current organization
5. The organization is assigned with the category information. Allocate the category information to the suborganization.
6. Organization allocation, no category information, not processed

When processing the category standard request, unlike the material information, this standard request does not have the "1" or "2" identifier to create or update the operation mode, the order for submitting standard requests is to first submit the item import request, then the category request (Category assignment). After testing the standard request and the standard request for material information, after the standard request for material information is complete, the category information is included in the standard category table view (mtl_item_categories_v). The category information is the default category. Therefore, the category is submitted at this time.
The standard request of assignment can only be updated (this is the case in the above A-F 6), and the update operation must have the "old_category" information, therefore, you need to obtain the old category of this item before update. If it is a new item, update the default category. (Default category is the default category under the current category set)

(6) Organization allocation attribute conflict

A common error in the import program is that "Master child conflict" may occur during material organization allocation ". In the attribute control of standard items, there are two types: "master level" and "org level". If a property is set to "master level", during the import, either you do not import this attribute information, or the imported attribute information must be consistent with that of the primary organization. Otherwise, this error is reported (standard system function ). It is necessary to judge in the import program API that, when the Organization allocates, some custom interface tables are not included in the Custom interface tables during the import from the custom interface table to the standard interface table, however, if the attribute is "master ",
The attribute value assigned to the main organization.