{"id":4545,"date":"2014-02-18T15:28:23","date_gmt":"2014-02-18T09:58:23","guid":{"rendered":"http:\/\/erpschools.com\/?p=4545"},"modified":"2014-07-13T18:11:01","modified_gmt":"2014-07-13T18:11:01","slug":"item-import-r12-item-conversion","status":"publish","type":"post","link":"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion","title":{"rendered":"Item import (Item conversion)"},"content":{"rendered":"

Overview:<\/em><\/strong> Item import program can be used to create new Inventory items or to update existing inventory items. \u00a0We can import items with all attributes information or\u00a0use item templates to set attributes and import items linking with\u00a0item template.<\/p>\n

Uses:<\/em><\/strong><\/p>\n

    \n
  1. Import New Inventory Items in Master Organization<\/li>\n
  2. Assign Items to child Organization (Step1 has to be finished before this task can be executed)<\/li>\n
  3. Update existing Inventory item attributes information<\/li>\n
  4. Assign Item Category Assignments<\/li>\n
  5. Lot numbers can be imported along with items<\/li>\n
  6. Serial numbers can be imported along with items<\/li>\n<\/ol>\n

    Execution Methods:<\/em><\/strong><\/p>\n

      \n
    1. \u00a0<\/em>Run \u00a0“Import Items” concurrent program from inventory responsibility manually.<\/li>\n
    2. Use PLSQL wrapper code to kick of the “Import Items” Concurrent program from backend.<\/li>\n<\/ol>\n

      Parameters:<\/em><\/strong><\/p>\n

      All Organizations: Yes \/ No (Select Yes if you want to import items belonging to various organizations reglardless of \u00a0current selection in form)<\/p>\n

      Validate Items: Yes \/ No (Select Yes if you would like to validate Items. With out validation we can not proceed to Processing)<\/p>\n

      Process Items: Yes \/ No (Select Yes to process \/ import items)<\/p>\n

      Delete Processed Items: Yes \/ No (Select Yes to purge data in interface tables for the processed records)<\/p>\n

      Process Set: You can use this parameter to process records in batches. It will use SET_PROCESS_ID column in interface table.<\/p>\n

      Create or Update Items: 1 \/ 2\/ 3. (1- Create New Items, 2 – Update Existing Items, 3 – Sync Items. Option 3 should be used if we have some items to be created and some to be updated)<\/p>\n

      Gather Statistics: Yes \/ No. (Helps to increase performance of SQL queries based on items table)<\/p>\n

      Pre-requisites:<\/em><\/strong><\/p>\n

        \n
      1. Master\u00a0Organization should\u00a0be defined.<\/li>\n
      2. Child Organization should\u00a0be defined.<\/li>\n
      3. Code Combinations\u00a0should\u00a0be defined.<\/li>\n
      4. Item Templates (If using item templates in interface table)<\/li>\n
      5. Defining Item Status Codes<\/li>\n
      6. Item Types should be defined.<\/li>\n<\/ol>\n

        Common Issues:<\/em><\/strong><\/p>\n

          \n
        1. Don’t try to load both master item record and child item record at a time. If you want to do that make sure you use\u00a0process set column.<\/li>\n
        2. Revision numbers have to be in chronological order, meaning it can be like AA, BB, CC … or 1,2,3,….but i can’t be AA, 1, BB, 2 etc.<\/li>\n
        3. Category assingments, Lot numbers, serial numbers has to be linked properly to the respective tables.<\/li>\n<\/ol>\n

          Interface tables:<\/em><\/strong><\/p>\n

          MTL_SYSTEM_ITEMS_INTERFACE
          \nMTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
          \nMTL_ITEM_CATEGORIES_INTERFACE (If importing categories)<\/p>\n

          Error Tracking Tables:<\/em><\/strong><\/p>\n

          MTL_INTERFACE_ERRORS\u00a0(View errors after import)<\/p>\n

          Base tables:<\/em><\/strong>
          \nMTL_SYSTEM_ITEMS_B
          \nMTL_ITEM_REVISIONS_B
          \nMTL_CATEGORIES_B
          \nMTL_CATEGORY_SETS_B
          \nMTL_ITEM_STATUS
          \nMTL_ITEM_TEMPLATES<\/p>\n

           <\/p>\n

          MTL_SYSTEM_ITEMS_INTERFACE:<\/span> (Important columns to be populated)<\/p>\n

          PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete,3= Assign\/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded)
          \nTRANSACTION_TYPE = \u2018CREATE\u2019, \u2018UPDATE\u2019
          \nSET_PROCESS_ID = 1
          \nORGANIZATION_ID
          \nDESCRIPTION
          \nITEM_NUMBER and\/or SEGMENT (n)
          \nMATERIAL_COST
          \nREVISION
          \nTEMPLATE_ID
          \nSUMMARY_FLAG
          \nENABLED_FLAG
          \nPURCHASING_ITEM_FLAG
          \nSALES_ACCOUNT (defaulted from MTL_PARAMETERS.SALES_ACCOUNT)
          \nCOST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)<\/p>\n

           <\/p>\n

          MTL_ITEM_CATEGORIES_INTERFACE:<\/span> (Mandatory columns to be populated)<\/p>\n

          INVENTORY_ITEM_ID or ITEM_NUMBER.
          \nORGANIZATION_ID or ORGANIZATION_CODE or both.
          \nTRANSACTION_TYPE = \u2018CREATE\u2019 (\u2018UPDATE\u2019 or \u2018DELETE\u2019 is not possible through Item Import).
          \nCATEGORY_SET_ID or CATEGORY_SET_NAME or both.
          \nCATEGORY_ID or CATEGORY_NAME or both.
          \nPROCESS_FLAG = 1
          \nSET_PROCESS_ID (The item and category interface records should have the same set_process_id, if you are importing item and category assignment together)<\/p>\n

          MTL_ITEM_REVISIONS_INTERFACE:<\/span> (Mandatory columns to be populated)<\/p>\n

          INVENTORY_ITEM_ID or ITEM_NUMBER (Must match the item_number in mtl_system_items_interface table)
          \nORGANIZATION_ID or ORGANIZATION_CODE or both
          \nREVISION
          \nCHANGE_NOTICE
          \nECN_INITIATION_DATE
          \nIMPLEMENTATION_DATE
          \nIMPLEMENTED_SERIAL_NUMBER
          \nEFFECTIVITY_DATE
          \nATTRIBUTE_CATEGORY
          \nATTRIBUTEn
          \nREVISED_ITEM_SEQUENCE_ID
          \nDESCRIPTION
          \nPROCESS_FLAG = 1
          \nTRANSACTION_TYPE = \u2018CREATE\u2019
          \nSET_PROCESS_ID = 1<\/p>\n

          Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.<\/p>\n

          Notes:<\/em><\/p>\n

          Through the import process, we can only create item category assignment(s). Updating or Deletion of item category assignment is not supported.<\/p>\n

          Sample Script<\/strong><\/span><\/p>\n

          Staging Table:<\/em>
          \n
          \nCREATE TABLE erps_item_import
          \n(segment1 varchar2(100),
          \norganization_id number,
          \nprocess_flag varchar2(100),
          \nset_process_id number,
          \ntransaction_type varchar2(100),
          \ndescription varchar2(100),
          \nprimary_UOM_code varchar2(100),
          \nvalidation_flag varchar2(10),
          \nvalidation_errors varchar2(100)
          \n);
          \n<\/code><\/p>\n


          \n-- SCRIPT FOR CUSTOM VALIDATIONS AND INSERT DATA FROM STAGING TABLE INTO INTERFACE TABLE
          \ndeclare
          \nv_organization_check NUMBER := 0;
          \nBegin
          \n--CUSTOM VALIDATIONS
          \nbegin
          \nfor i in (select * from APPS.erps_item_import) -- Staging table
          \nLoop
          \nSELECT 1
          \nINTO v_organization_check
          \nFROM mtl_parameters mp
          \nWHERE mp.organization_id = i.organization_id ;
          \nupdate erps_item_import
          \nset validation_flag = 'Y'
          \nwhere organization_id = i.organization_id;
          \nend loop;
          \nexception when others then
          \nnull;
          \nend;
          \n-- INSERT DATA FROM STAGING INTO INTERFACE TABLE
          \nINSERT INTO mtl_system_items_interface
          \n(segment1,
          \norganization_id,
          \nprocess_flag,
          \nset_process_id,
          \ntransaction_type,
          \ndescription,
          \nprimary_UOM_code
          \n)
          \nSELECT
          \nsegment1,
          \norganization_id,
          \nprocess_flag,
          \nset_process_id,
          \ntransaction_type,
          \ndescription,
          \nprimary_UOM_code
          \nFROM APPS.erps_item_import
          \nWHERE VALIDATION_FLAG ='Y'
          \n;
          \nCOMMIT;
          \nEXCEPTION
          \nWHEN OTHERS THEN
          \ndbms_output.put_line('Error while inserting record in interface table '||SUBSTR(SQLERRM,1,200));
          \nEND;
          \n<\/code><\/p>\n

           <\/p>\n

          If you have any questions let us know in comments section.<\/p>\n","protected":false},"excerpt":{"rendered":"

          Overview: Item import program can be used to create new Inventory items or to update existing inventory items. \u00a0We can import items with all attributes information or\u00a0use item templates to set attributes and import items linking with\u00a0item template. Uses: Import New Inventory Items in Master Organization Assign Items to child Organization (Step1 has to be […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[530,189,190,140,302,303],"class_list":["post-4545","post","type-post","status-publish","format-standard","hentry","category-interface","tag-api","tag-conversion","tag-interface-2","tag-inventory-2","tag-item-import","tag-validations"],"yoast_head":"\nitem import in oracle apps r12<\/title>\n<meta name=\"description\" content=\"item import in oracle apps r12\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Prudhvi\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion#article\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion\"},\"author\":{\"name\":\"Prudhvi\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512\"},\"headline\":\"Item import (Item conversion)\",\"datePublished\":\"2014-02-18T09:58:23+00:00\",\"dateModified\":\"2014-07-13T18:11:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion\"},\"wordCount\":693,\"commentCount\":6,\"publisher\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\"},\"keywords\":[\"API\",\"conversion\",\"interface\",\"inventory\",\"item import\",\"validations\"],\"articleSection\":[\"Interface\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion\",\"url\":\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion\",\"name\":\"item import in oracle apps r12\",\"isPartOf\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#website\"},\"datePublished\":\"2014-02-18T09:58:23+00:00\",\"dateModified\":\"2014-07-13T18:11:01+00:00\",\"description\":\"item import in oracle apps r12\",\"breadcrumb\":{\"@id\":\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/erpschools.com\/erps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Item import (Item conversion)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/erpschools.com\/erps\/#website\",\"url\":\"https:\/\/erpschools.com\/erps\/\",\"name\":\"erpSchools\",\"description\":\"Oracle Apps\",\"publisher\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/erpschools.com\/erps\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/erpschools.com\/erps\/#organization\",\"name\":\"erpSchools\",\"url\":\"https:\/\/erpschools.com\/erps\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png\",\"contentUrl\":\"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png\",\"width\":250,\"height\":60,\"caption\":\"erpSchools\"},\"image\":{\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"http:\/\/facebook.com\/erpschools\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512\",\"name\":\"Prudhvi\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/erpschools.com\/erps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g\",\"caption\":\"Prudhvi\"},\"sameAs\":[\"http:\/\/www.erpschools.com\"],\"url\":\"https:\/\/erpschools.com\/erps\/author\/prudhvi\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"item import in oracle apps r12","description":"item import in oracle apps r12","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion","twitter_misc":{"Written by":"Prudhvi","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion#article","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion"},"author":{"name":"Prudhvi","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512"},"headline":"Item import (Item conversion)","datePublished":"2014-02-18T09:58:23+00:00","dateModified":"2014-07-13T18:11:01+00:00","mainEntityOfPage":{"@id":"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion"},"wordCount":693,"commentCount":6,"publisher":{"@id":"https:\/\/erpschools.com\/erps\/#organization"},"keywords":["API","conversion","interface","inventory","item import","validations"],"articleSection":["Interface"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion#respond"]}]},{"@type":"WebPage","@id":"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion","url":"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion","name":"item import in oracle apps r12","isPartOf":{"@id":"https:\/\/erpschools.com\/erps\/#website"},"datePublished":"2014-02-18T09:58:23+00:00","dateModified":"2014-07-13T18:11:01+00:00","description":"item import in oracle apps r12","breadcrumb":{"@id":"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/erpschools.com\/erps\/interface\/item-import-r12-item-conversion#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/erpschools.com\/erps"},{"@type":"ListItem","position":2,"name":"Item import (Item conversion)"}]},{"@type":"WebSite","@id":"https:\/\/erpschools.com\/erps\/#website","url":"https:\/\/erpschools.com\/erps\/","name":"erpSchools","description":"Oracle Apps","publisher":{"@id":"https:\/\/erpschools.com\/erps\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/erpschools.com\/erps\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/erpschools.com\/erps\/#organization","name":"erpSchools","url":"https:\/\/erpschools.com\/erps\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/","url":"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png","contentUrl":"https:\/\/erpschools.com\/erps\/wp-content\/uploads\/img\/erps_logo7.png","width":250,"height":60,"caption":"erpSchools"},"image":{"@id":"https:\/\/erpschools.com\/erps\/#\/schema\/logo\/image\/"},"sameAs":["http:\/\/facebook.com\/erpschools"]},{"@type":"Person","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/dbed9bb7fb66aa7a700fc565da024512","name":"Prudhvi","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/erpschools.com\/erps\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/488cec3605845b95cb20e60c67a8f5c7e74b65a305525c8006315d524f120db9?s=96&d=blank&r=g","caption":"Prudhvi"},"sameAs":["http:\/\/www.erpschools.com"],"url":"https:\/\/erpschools.com\/erps\/author\/prudhvi"}]}},"_links":{"self":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts\/4545","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/comments?post=4545"}],"version-history":[{"count":0,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/posts\/4545\/revisions"}],"wp:attachment":[{"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/media?parent=4545"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/categories?post=4545"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/erpschools.com\/erps\/wp-json\/wp\/v2\/tags?post=4545"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}