Thursday, June 3, 2010

Item category assignment open interface in R12

How to import item category assignment through interface?

Program:  Item category assignment open interface

Interface Table:              MTL_ITEM_CATEGORIES_INTERFACE

Interface Error Table:  MTL_INTERFACE_ERRORS

Base Tables:                    MTL_ITEM_CATEGORIES


Mandatory Columns:

  • The user needs to populate the following mandatory columns in item categories interface table:
    Either INVENTORY_ITEM_ID or ITEM_NUMBER. When item and category are being imported together the user can only specify the ITEM_NUMBER since the import process will generate INVENTORY_ITEM_ID.
  • Either ORGANIZATION_ID or ORGANIZATION_CODE or both
  • The TRANSACTION_TYPE column should be 'CREATE' or 'DELETE' or ‘UPDATE’
  • Either CATEGORY_SET_ID or CATEGORY_SET_NAME or both.
  • Either CATEGORY_ID or CATEGORY_NAME or both
  • PROCESS_FLAG column as 1. 
  • Populate the SET_PROCESS_ID column.
  • If TRANSACTION_TYPE = ‘UPDATE’ then we need to populate OLD_CATEGORY_ID or OLD_CATEGORY_NAME column also.
Parameters:

Record Set Id
Enter a set id number for the set of records in the interface table that you want to process. The program picks up the records having that id in the SET_PROCESS_ID column. This column value cannot be NULL. In order to avoid set id value conflicts between record sets imported by different users, you should use the sequence MTL_SYSTEM_ITEMS_INTF_SETS_S when populating the SET_PROCESS_ID column.

Upload Processed Records
Yes --> All qualifying item category assignment records in the interface table are inserted into Oracle Inventory.
No --> Do not insert item category assignments into Oracle Inventory. Use this option if you want to validate items without any processing.

Delete Processed Records
Yes --> Delete successfully processed item category assignment records from the item categories interface table.
No --> Leave all records in the item categories interface table.

Program Navigation:




Script for Inserting Data into Interface Tables:

INSERT
   INTO MTL_ITEM_CATEGORIES_INTERFACE
  (
    INVENTORY_ITEM_ID ,
    CATEGORY_SET_ID   ,
    CATEGORY_ID       ,
    PROCESS_FLAG      ,
    ORGANIZATION_ID   ,
    SET_PROCESS_ID    ,
    TRANSACTION_TYPE
  )
  VALUES
  (
    26092           ,
    1               ,
    9233            ,
    1               ,
    103             ,
    1               ,
    'DELETE'
  );
 
  INSERT
   INTO MTL_ITEM_CATEGORIES_INTERFACE
  (
    INVENTORY_ITEM_ID ,
    CATEGORY_SET_ID   ,
    CATEGORY_ID       ,
    PROCESS_FLAG      ,
    ORGANIZATION_ID   ,
    SET_PROCESS_ID    ,
    TRANSACTION_TYPE
  )
  VALUES
  (
    26092           ,
    1               ,
    9233            ,
    1               ,
    103             ,
    1               ,
    'INSERT'
  );
 
  INSERT
   INTO MTL_ITEM_CATEGORIES_INTERFACE
  (
    INVENTORY_ITEM_ID ,
    CATEGORY_SET_ID   ,
    OLD_CATEGORY_ID   ,
    CATEGORY_ID       ,
    PROCESS_FLAG      ,
    ORGANIZATION_ID   ,
    SET_PROCESS_ID    ,
    TRANSACTION_TYPE
  )
  VALUES
  (
    26092           ,
    1               ,
    4567            ,
    9233            ,
    1               ,
    103             ,
    1               ,
    'UPDATE'
  );

3 Responses to “Item category assignment open interface in R12”

Anonymous said...
March 24, 2013 at 6:56 AM

Tks


Anonymous said...
October 5, 2018 at 1:22 AM

just want to know what this item category interface does when we provide transaction_type as update


George said...
August 31, 2020 at 12:40 PM

FYI - a Typo in the above: 'INSERT' should be 'CREATE'


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.