, , , , , ,

API to create Item Category

The item categories can be created using item import when Items are created. They can also be created using APIs discussed below:
Following are the steps to Check and/or Create Item Categories
1) Create Category Segment combinations
2) If the enforce list is checked for category then insert category combinations in the enforce list
3) Create/Update/Delete Category Assignments on Item

In Detail

Create Category Segment combinations
Following APIs can be used to create/update/delete category combinations.
INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
  X_CATEGORY_ID  OUT    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_REC  IN    INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER
)
Use following API for assigning a category to a category set. 
A category will be available in the list of valid categoies for a category set only if it is assigned to the category set. This is a required step if for categories enforce list is checked on.
INV_ITEM_CATEGORY_PUB.CREATE_VALID_CATEGORY(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_CATEGORY_ID  IN    NUMBER,
  P_PARENT_CATEGORY_ID  IN    NUMBER,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2
)
Following APIs can be used to create/update/delete Item category assignments.
INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)

INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_DESCRIPTION(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_DESCRIPTION  IN    VARCHAR2
)

INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT(
  P_API_VERSION  IN    NUMBER,
  P_INIT_MSG_LIST  IN    VARCHAR2 := FND_API.G_FALSE,
  P_COMMIT  IN    VARCHAR2 := FND_API.G_FALSE,
  X_RETURN_STATUS  OUT    VARCHAR2,
  X_ERRORCODE  OUT    NUMBER,
  X_MSG_COUNT  OUT    NUMBER,
  X_MSG_DATA  OUT    VARCHAR2,
  P_CATEGORY_ID  IN    NUMBER,
  P_CATEGORY_SET_ID  IN    NUMBER,
  P_INVENTORY_ITEM_ID  IN    NUMBER,
  P_ORGANIZATION_ID  IN    NUMBER
)
/by
, , , , , ,

Item Import (Item Conversion)

The Item import Interface(IOI) reads data from following tables for importing items and item details. The MTL_SYSTEMS_ITEM_INTERFACE table is used for new item numbers
and all item attributes. This is the main item interface table, and can be
the only table used to import items. MTL_ITEM_REVISIONS_INTERFACE is used if Item revisions history is also loaded with items. Item categories can be imported using MTL_ITEM_CATEGORIES_INTERFACE.
The import error can be tracked using MTL_INTERFACE_ERRORS table. The transaction_id and request_id populated by the import program can be used to link interface table and error table.

Required columns in MTL_SYSTEM_ITEMS_INTERFACE
PROCESS_FLAG = 1 (The column is used to identify status of record)
TRANSACTION_TYPE = ‘CREATE’ or ‘UPDATE’
SET_PROCESS_ID = any numeric value (This is not a required column but for performance it is advised to use this column and then run import program for the value entered here)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
DESCRIPTION = ‘Description of the item’
ITEM_NUMBER and/or SEGMENT(n) = If using item_number then each segment value should be entered concatenated by segment seperator. If Item revisions history is also being loaded then Item_number should be populated.
LIST_PRICE_PER_UNIT = If material cost is to be populated for an item along with item import .

Required columns in MTL_ITEM_REVISIONS_INTERFACE table. The table is only used if Item revision is to be loaded in the same run with IOI. If this table is not used then items are created with the default revision setup for an organization.
PROCESS_FLAG = 1
TRANSACTION_TYPE = ‘CREATE’
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = Same as 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.

Required columns for MTL_ITEM_CATEGORIES_INTERFACE table.
TRANSACTION_TYPE = ‘CREATE’
SET_PROCESS_ID = any numeric value(Should be same for the item in MTL_SYSTEM_ITEMS_INTERFACE table)
ORGANIZATION_ID/ORGANIZATION_CODE = Master/Child Org
ITEM_NUMBER/INVENTORY_ITEM_ID or both
CATEGORY_SET_NAME or CATEGORY_SET_NAME or both
CATEGORY_ID or CATEGORY_NAME or both

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 seperate set_process_ids are passed while submitting. The IOI automatically separates Master records from Child, and
processes Master records first. However, as one IOI process is not aware of
other IOI processes running in parallel, do not split a given item’s separate
Organization records into two different SET_PROCESS_IDs that are being run in
parallel.

Item import program can be run in 2 modes INSERT & UPDATE.
The method to update Item attribute columns to NULL is to use the following values:
· for Numeric fields: insert -999999
· for Character fields: insert ‘!’

/by
,

What Actions are supported in import standard purchase order ?

What Actions are supported?

ORIGINAL – create a new document
ADD – add new lines onto an existing document
UPDATE – update existing line information for a document
REPLACE – Replace the existing document
Which interface tables are involved?

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE(Used for Standard PO only)

/by
, ,

How is data deleted from the interface tables after it has been loaded ?

After loading the data from the interface tables into the system successfully,  the data is not being removed (cleaned) from the interfaces.
They will have process_code = ‘ACCEPT’ in the interface tables.

To remove the processed data from the interface, a concurrent program is available.  The program name is : purge purchasing open interface processed data(POXPOIPR)

Run this program with paramenter purge accepted data = Yes
Then the process_code = ‘ACCEPTED’ records will be removed from the interface tables.

/by
, , , , , ,

How to view AP Invoice Interface from the Oracle R12 Front end?

Let’s say Invoices are imported from External Systems – irrespective of the transportation layer / method. Instead of viewing the Interface tables in the backend – say AP_INVOICES_INTERFACE , AP_INVOICE_LINES_INTERFACE, they can be viewed in the front-end.
Responsibility – Payables Manager
Navigation – Invoices – Entry – Open Interface Invoices

Clicking on the above link, opens the below form ..

Open the Query-mode to view the Invoice in the Interface table

Above is the header line. Click on Lines to view the lines information.

This would avoid looking for Invoices by querying in the back-end.
/by