6 Replies Latest reply on Jun 12, 2012 3:58 PM by Comet

    Moving Inventory item master changes to iPro

    Comet
      We are an Oracle Financials 11i and Oracle 10g database site. Due to a bug in a data fix the item descriptions in the items master (ITM organization) do not always tie-out to the item description in our two inventory organizations INV and DEV. I wrote a script to copy the item master description to the inventory organizations for the affected items. The script works fine and when we look at the item description in Inventory item master in any of the orgs the descriptions are synchronized. We need to move the updated descriptions to iProcurement but when we run the catalog extract from the Purchasing module none of these changes are transferred. I inspected the columns in the base tables mtl_items_master_b & mt_item_master_tl and other than the last_update_date I cannot see any other column that would indicate a change occurred that needs to be transferred to iProcurement.

      When I set the last_update_date on the item master organization (ITM) row to the date of the data fix the catalog extract ends with an error. But when I only update the INV and DEV organization rows the catalog extract runs to completion with no errors. But, as I stated above, the item description is not moved to iProcurement.

      Are any log files affected when an Inventory item is changed which the catalog extract uses to select item changes to move to iProcurement?

      This is the output from the step called 'Catalog Data Extract - Items' in the catalog extract (the log is much longer and is available is necessary):

      DECLARE
      *
      ERROR at line 1:
      ORA-06510: PL/SQL: unhandled user-defined exception
      ORA-06512: at "APPS.ICX_POR_EXT_ITEM", line 8185
      ORA-06510: PL/SQL: unhandled user-defined exception
      ORA-06512: at "APPS.ICX_POR_EXT_ITEM", line 7924
      ORA-06510: PL/SQL: unhandled user-defined exception
      ORA-06512: at "APPS.ICX_POR_EXT_ITEM", line 5423
      ORA-06510: PL/SQL: unhandled user-defined exception
      ORA-06512: at "APPS.ICX_POR_EXT_ITEM", line 2954
      ORA-00001: unique constraint (ICX.ICX_CAT_CATEGORY_ITEMS_U1) violated
      ORA-06512: at line 20
        • 1. Re: Moving Inventory item master changes to iPro
          Sandeep Gandhi, Consultant
          I wrote a script to copy the item master description to the inventory organizations for the affected items.
          Did you update the item master directly?
          If so, then try using the item interface to update the description in ITM.

          Make sure that the item description attribute is controlled at master level.

          Sandeep Gandhi
          • 2. Re: Moving Inventory item master changes to iPro
            Comet
            I updated the item through SQL UPDATE command in a sql script. There are thousands of items with descriptions that are not synchronized so it is impractical to update the the items through the Inventory module, if that is what you mean. what do you mean by "Make sure that the item description attribute is controlled at master level."
            • 3. Re: Moving Inventory item master changes to iPro
              Sandeep Gandhi, Consultant
              I updated the item through SQL UPDATE command in a sql script.
              It is a bad idea to update item description via sql. It won't work. Do not do it.
              There are thousands of items with descriptions that are not synchronized so it is impractical to update the the items through the Inventory module,
              Agreed. But that is why Oracle has provided Item Interface.
              You can try the following
              INSERT INTO mtl_system_items_interface
                          (process_flag, set_process_id, segment1, description,
                           organization_code, transaction_type)
                 (SELECT 1, 123, msi.segment1, msi.description, mp.organization_code,
                         'Update'
                    FROM mtl_system_items_b msi, mtl_parameters mp
                   WHERE msi.organization_id = mp.master_organization_id
                     AND EXISTS (
                            SELECT 1
                              FROM mtl_system_items_b msi2
                             WHERE msi2.organization_id = mp.organization_id
                               AND msi2.inventory_item_id = msi.inventory_item_id
                               AND msi2.description != msi.description))
              And then run item import to make descriptions in child org = those in master
              what do you mean by "Make sure that the item description attribute is controlled at master level."
              Inventory > setup > items > attribute controls
              Look for description and make sure control is at master level
              (I assume you want same description for a given item in all orgs)
              1 person found this helpful
              • 4. Re: Moving Inventory item master changes to iPro
                Comet
                I know that updating the item through sql won't work. Someone updated item descriptions during an upgrade and that is why we have the problem. I never wanted to write a script like this because it was obvious there was a lot more going on under the hood besideds the two base tables I listed previously. I considered the possibility that something like the system items interface existed but did not know where to look. This looks very promising. Do you know where the Interface is documented? Thank you.
                • 5. Re: Moving Inventory item master changes to iPro
                  Sandeep Gandhi, Consultant
                  See http://docs.oracle.com/cd/E18727_01/doc.121/e13450/T291651T298833.htm#T298851 and http://kutuboracle.blogspot.com/2008/08/item-upload-thru-itenm-interface.html

                  Sandeep Gandhi
                  • 6. Re: Moving Inventory item master changes to iPro
                    Comet
                    The Mtl_System_Items_Interface & Import Items concurrent process are fine for updating the Inventory description but the catalog extract still does not move the changes to iProcurment.