oracle ebs期间api,[转载]Oracle EBS 常见API上

Order Import Interface (Sales Order

Conversion)

Interface tables

· OE_HEADERS_IFACE_ALL

· OE_LINES_IFACE_ALL

· OE_ACTIONS_IFACE_ALL

· OE_ORDER_SOURCES

· OE_CUSTOMER_INFO_IFACE_ALL

· OE_PRICE_ADJS_IFACE_ALL

· OE_PRICE_ATTS_IFACE_ALL

Base tables

· OE_ORDER_HEADERS_ALL

· OE_ORDER_LINES_ALL

Pricing tables

· QP_PRICING_ATTRIBUTES

During import of orders, shipping tables are not populated.

If importing customers together with the order,

OE_ORDER_CUST_IFACE_ALL

Base Tables: HZ_PARTIES HZ_LOCATIONS

Orders can be categorized based on their

status:

1. Entered orders

2. Booked orders

3. Closed orders

Concurrent Program: Order Import

Validations:

· Check for sold_to_org_id. If does not exist,

create new customer by calling create_new_cust_info API.

· Check for sales_rep_id. Should exist for a

booked order.

· Ordered_date should exist. -------- header

level

· Delivery_lead_time should exist. -----------

line level

· Earliest_acceptable_date should exist.

· Freight_terms should exist

Order Import API OE_ORDER_PUB.GET_ORDER,

PROCESS_ORDER

Concurrent programs will in turn call APIs.

2 APIs are called during order import process.

· OE_CNCL_ORDER_IMPORT_PVT (cancelled

orders)

· ORDER_IMPORT_PRIVATE

Procedure: import_order()

Item import (Item conversion)

Always import master and child records.

Interface tables:

· MTL_SYSTEM_ITEMS_INTERFACE

· MTL_ITEM_REVISIONS_INTERFACE( If importing

revisions, populate)

· MTL_ITEM_CATEGORIES_INTERFACE(If importing

categories, populate)

· MTL_INTERFACE_ERRORS

Item import can be run in create mode or update

mode.

Running the Item Open Interface In Create

Mode:

Populate the mtl_system_items_interface with the following

minimum required columns when creating new

items:

PROCESS_FLAG = 1

TRANSACTION_TYPE = 'CREATE'

SET_PROCESS_ID = 1

ORGANIZATION_ID = Master Org id.

DESCRIPTION = 'Description of the item'

ITEM_NUMBER and/or SEGMENT(n)

Note: Using the ITEM_NUMBER

column in the mtl_system_items_interface is required if you are

populating revision history data into the mtl_item_revisions_interface

table. The value of the ITEM_NUMBER must equal the concatenated

segments(n) of the item being imported plus the segment

separator. If you are not importing revision

history you can populate either ITEM_NUMBER or the SEGMENT(n)

column(s) or both.

Running the Item Open Interface In Update

Mode:

To update existing item(s), set TRANSACTION_TYPE = 'UPDATE'.

For best performance, use inventory_item_id when updating

items.

Functionality

Every attribute updateable from the Item form is updateable

through the interface, and all required validations are performed

to enforce:

· Item Attribute Interdependencies

· Master - Child Attribute Dependencies

· Status Controlled Attributes

· Templates can be applied to existing Items.

For best results, use template_id and template_name.

· The Item status can be changed for existing

Items, and the proper attributes are Defaulted / Set accordingly.

The Status change is recorded in

MTL_PENDING_ITEM_STATUS.

· To populate material costs from

IOI: Populate the LIST_PRICE_PER_UNIT column with a

value while importing items and you will see your material cost for

your item in MTL_SYSTEM_ITEMS after running the Item Import

process. (CREATE transaction_type only)

· When launching items into the Master Item

Org, the Child records are copied into MTL_SYSTEM_ITEMS_INTERFACE

for validation, and are identified with transaction_type of

'AUTO_CHILD'. These records are deleted if the parameter 'Delete

Processed Rows' has been passed as 'Yes', and remain for diagnostic

purposes if the parameter is passed as 'No'. When the defining

attribute for a Functional area is enabled, the proper default

category set and category is assigned to the Item.

· Master Items were loaded before child

records in MTL_SYSTEM_ITEMS.

Not Supported Issues

=========================================

· Item Costs cannot be UPDATED (using "UPDATE"

transaction_type) through the interface.

· New Item revisions cannot be added to

existing Items.

· Current functionality does not support

updates to a PTO MODEL ITEM through

the IOI update feature. See notes: 1076412.6 and

2121870.6 Updating Item Attributes to NULL The method to update

these columns to NULL is to use the following

values:

1. for Numeric fields: insert

-999999

2. for Character fields:

insert '!'

3. for Date fields: the above

list does not include any updateable date fields.

Importing Master and Child Records

==================================

The user procedures are as follows :

1. Populate the item interface tables

(mtl_system_items_interface). This step is necessary if you are

creating items and categories in the same run. For importing item

category assignments for already existing items, you do not need to

populate item interface table.

2. Populate the item categories interface table

(mtl_item_categories_interface).

The user needs to populate the following mandatory columns in

item categories interface table:

A. Either inventory_item_id or item_number. When item and

category are being imported together, then user can only specify

the item_number, since item id will be generated by the import

process.

B. Either organization_id or organization_code or both.

C. The transaction_type column should be 'CREATE'. We do not

support 'UPDATE' or 'DELETE' for item category assignment.

D. Either category_set_id or category_set_name or both.

E. Either category_id or category_name or both.

F. Process_flag column as 1.

G. Populate the set_process_id column. The item and category

interface records should have the same set_process_id, if you are

importing item and category assignment together.

3. After populating the item and category interface tables,

launch the Item Import process from the applications. In the item import parameters form, for the

parameter 'set process id', specify the 'set process id' value

given in the mtl_item_categories_interface table. The parameter

'Create or Update' can have have any value. Through the import

process, we can only create item category assignment(s).

Updation or Deletion of item category assignment is not

supported.

4. Once the concurrent process completes, check the

mtl_interface_errors table for any error(s) during the item and

category import. Correct those error conditions in the interface

tables and run the item import again. If the process_flag is 7,

that means the item category interface records were successfully

imported.

Revisions

==============================

Note: Using the ITEM_NUMBER

column in the mtl_system_items_interface table is required if you

are populating revision data into the mtl_item_revisions_interface

table. The value of the ITEM_NUMBER must equal the concatenated

segments(n) of the item being imported, plus the segment separator.

If you are not importing revision history you can populate either

ITEM_NUMBER or the SEGMENT(n) column(s) or both. For historical

item revision data, do NOT populate the REVISION column in the

mtl_system_items_interface table. This column is used only if the

current revision of the item is being imported.

Populate these columns in the mtl_item_revisions_interface

table:

PROCESS_FLAG = 1

TRANSACTION_TYPE = 'CREATE'

SET_PROCESS_ID = 1

ORGANIZATION_ID = Master Org ID.

REVISION

EFFECTIVITY_DATE

IMPLEMENTATION_DATE

ITEM_NUMBER = (Must match the item_number in

mtl_system_items_interface table.)

Each row in the mtl_item_revisions_interface table must have the

REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and

chronological order.

Run the IOI process. Navigate -->

Inventory: Items: Import Items

There are 6 parameters to enter to begin the

process:

1. Specify one or all organizations.

2. Validate items, yes or no.

3. Process items, yes or no.

4. Delete processed rows, yes or no.

5. Process set (null for all)

6. Create or update items (1 for create, 2 for update)

Note: If you are importing

Master and Child records, insert them into the

mtl_system_items_interface and mtl_item_revisions_interface tables,

and run them at the same time by setting the 'All organizations'

parameter to 'Yes'. If you do not do this, then the Child revision

records will not be imported.

Error Checking:

======================================

When importing multiple revisions, if one record for an item

fails validation, all revisions for that item fail. Resolve failed

rows by checking the mtl_interface_errors table.

SELECT table_name, column_name, error_message, message_name

FROM mtl_interface_errors;

Base tables:

§ MTL_SYSTEM_ITEMS_B

§ MTL_ITEM_REVISIONS_B

§ MTL_CATEGORIES_B

§ MTL_CATEGORY_SETS_B

§ MTL_ITEM_STATUS

§ MTL_ITEM_TEMPLATES

Concurrent program: Item

Import

Validations: check for valid

item type.

Check for valid part_id/segment of the source table.

Validate part_id/segment1 for master org.

Validate and translate template id of the source table.

Check for valid template id. (attributes are already set for

items, default attributes for

that template, i.e., purchasable, stockable, etc)

Check for valid item status.

Validate primary uom of the source table.

Validate attribute values.

Validate other UOMs of the source table.

Check for unique item type. Discard the item, if part has

non-unique item type.

Check for description, inv_um uniqueness

Validate organization id.

Load master records and category records only if all validations

are passed.

Load child record if no error found.

Interface Tables Base

Tables

MTL_SYSTEM_ITEMS_INTERFACE MTL_SYSTEM_ITEMS

MTL_TRANSACTIONS_INTERFACE

MTL_ITEM_REVISION_INTERFACE MTL_ITEM_REVISIONS

MTL_DEMAND_INTERFACE

MTL_ITEM_CATEGORIES_INTERFACE MTL_ITEM_CATEGORIES

MTL_CROSS_REFERENCES_INTERFACE MTL_CROSS_REFERENCES