Nolem的部落格

由於我方料號編碼整個調整,所以直接複製舊料號的給新料號使用

mtl_system_items.attribute29  存的是舊料號ID ,讓新料號直接對串資料~

-- MTL_CROSS_REFERENCES_PUB SPEC 120.0.12010000.2  欄位長度定義有錯,TYPE XRef_Rec_Type 以下兩個欄位必須自己放大到255
--上patch p14138918_R12.ITM.C_R12_GENERIC 更新 MTL_CROSS_REFERENCES_PVT body 到 120.0.12010000.5
-- patch p14138918_R12 fixed 1OFF:12800632:INV.K:12.1.3:INVALID VALUE FOR UOM_CODE OR REVISION_ID
--  ,Cross_Reference_Type                   VARCHAR2(25)         DEFAULT      FND_API.G_MISS_CHAR
--  ,Cross_Reference                        VARCHAR2(25)         DEFAULT      FND_API.G_MISS_CHAR

DECLARE
   /* sql 1 , 抓要轉換的料號參照除了舊料號外的CROSS_REFERENCE */
   CURSOR cur
   IS
      SELECT   msi.INVENTORY_ITEM_ID,
               msi.segment1,
               msi.attribute28,
               msi.attribute29,
               crss.INVENTORY_ITEM_ID old_INVENTORY_ITEM_ID,
               crss.ORGANIZATION_ID,
               crss.CROSS_REFERENCE_TYPE,
               crss.CROSS_REFERENCE
        FROM   (SELECT   INVENTORY_ITEM_ID,
                         segment1,
                         attribute28,
                         attribute29
                  FROM   mtl_system_items
                 WHERE       ORGANIZATION_ID = 101
                         AND attribute30 = 'Y'
                         AND attribute29 IS NOT NULL) msi,
               mtl_cross_references crss
       WHERE   msi.attribute29 = crss.INVENTORY_ITEM_ID
               AND crss.CROSS_REFERENCE_TYPE <> '舊料號'
      UNION ALL
      /* sql 2 , 調整舊料號 CROSS_REFERENCE 為舊ERP 14碼料號*/
      SELECT   msi.INVENTORY_ITEM_ID,
               msi.segment1,
               msi.attribute28,
               msi.attribute29,
               msi_old.INVENTORY_ITEM_ID old_INVENTORY_ITEM_ID,
               NULL ORGANIZATION_ID,
               '舊料號' CROSS_REFERENCE_TYPE,
               msi_old.segment1 CROSS_REFERENCE
        FROM   (SELECT   INVENTORY_ITEM_ID,
                         segment1,
                         attribute28,
                         attribute29
                  FROM   mtl_system_items
                 WHERE       ORGANIZATION_ID = 101
                         AND attribute30 = 'Y'
                         AND attribute29 IS NOT NULL) msi,
               mtl_system_items msi_old
       WHERE   msi.attribute29 = msi_old.INVENTORY_ITEM_ID
               AND msi_old.ORGANIZATION_ID = 101;

   l_api_version      NUMBER := 1.0;
   l_init_msg_list    VARCHAR2 (2) := FND_API.G_TRUE;
   l_commit           VARCHAR2 (2) := FND_API.G_TRUE;
   l_user_id          NUMBER := 3314;
   l_resp_id          NUMBER := 50658;
   l_application_id   NUMBER := 401;
   l_XRef_tbl         MTL_CROSS_REFERENCES_PUB.XRef_Tbl_Type;
   x_message_list     Error_Handler.Error_Tbl_Type;
   x_return_status    VARCHAR2 (2);
   x_msg_count        NUMBER := 0;
   reccnt pls_integer :=0;
BEGIN
   APPS.FND_GLOBAL.APPS_INITIALIZE (l_user_id, l_resp_id, l_application_id);
 --  set serveroutput on buffer 2560000;
   FOR rec IN cur
   LOOP
      reccnt := reccnt +1 ;    
      l_XRef_tbl(reccnt).Transaction_Type := 'CREATE';
      l_XRef_tbl(reccnt).Cross_Reference_Type := rec.CROSS_REFERENCE_TYPE;
      l_XRef_tbl(reccnt).Cross_Reference := rec.CROSS_REFERENCE;
      l_XRef_tbl(reccnt).Inventory_Item_Id := rec.INVENTORY_ITEM_ID;
      l_XRef_tbl(reccnt).Organization_Id := rec.ORGANIZATION_ID;
      l_XRef_tbl (reccnt).org_independent_flag := 'Y';
      
   END LOOP;
        DBMS_OUTPUT.PUT_LINE('Total pop plsql table record number = ' || to_char(reccnt));
      MTL_CROSS_REFERENCES_PUB.Process_XRef (
         p_api_version     => l_api_version,
         p_init_msg_list   => l_init_msg_list,
         p_commit          => l_commit,
         p_XRef_Tbl        => l_XRef_tbl,
         x_return_status   => x_return_status,
         x_msg_count       => x_msg_count,
         x_message_list    => x_message_list
      );

      IF x_return_status = FND_API.g_RET_STS_ERROR
      THEN
         --DBMS_OUTPUT.PUT_LINE('Error Message Count :'||x_message_list.COUNT);
         FOR i IN 1 .. x_message_list.COUNT
         LOOP
            DBMS_OUTPUT.PUT_LINE(   TO_CHAR (i)
                                 || '. Err Rec No : '
                                 || x_message_list (i).entity_index
                                 || ' Table Name : '
                                 || x_message_list (i).table_name);
         END LOOP;
      END IF;


      DBMS_OUTPUT.PUT_LINE(x_return_status);

END;