Nolem的部落格

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

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

declare
        l_api_version         NUMBER := 1.0;
        l_init_msg_list         VARCHAR2(2) := FND_API.G_TRUE;
        l_commit         VARCHAR2(2) := FND_API.G_TRUE;
        x_message_list         Error_Handler.Error_Tbl_Type;
        x_return_status         VARCHAR2(2);
        x_msg_count         NUMBER := 0;
    
        l_user_id        NUMBER := -1;
        l_resp_id        NUMBER := -1;
        l_application_id    NUMBER := -1;

        l_user_name        VARCHAR2(30) := 'NOLEM';
        l_resp_name        VARCHAR2(30) := 'YENS_INV_ALL_SUPERUSER';    
   cursor cur is select msi.INVENTORY_ITEM_ID ,msi.segment1,msi.attribute28,msi.attribute29,
                      msi_org.INVENTORY_ITEM_ID old_INVENTORY_ITEM_ID,msi_org.ORGANIZATION_ID
                      ,msi_org.PRIMARY_UOM_CODE
               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_org
               where msi.attribute29=msi_org.INVENTORY_ITEM_ID
                 and msi_org.ORGANIZATION_ID not in (101,103,104,105,110);     

BEGIN
 
      -- Get the user_id
      SELECT user_id
      INTO l_user_id
      FROM fnd_user
      WHERE user_name = l_user_name;
    
      -- Get the application_id and responsibility_id
      SELECT application_id, responsibility_id
      INTO l_application_id, l_resp_id
      FROM fnd_responsibility
      WHERE responsibility_key = l_resp_name;
    
      FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id);  -- NOLEM / YENS_INV_ALL_SUPERUSER / INV
       
     -- call API to assign Items
   
    for rec in cur loop
      EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG(
             P_API_VERSION          => l_api_version
          ,  P_INIT_MSG_LIST        => l_init_msg_list
          ,  P_COMMIT               => l_commit
          ,  P_INVENTORY_ITEM_ID    => rec.INVENTORY_ITEM_ID   
          ,  P_ITEM_NUMBER          => NULL
          ,  P_ORGANIZATION_ID      => rec.ORGANIZATION_ID
          ,  P_ORGANIZATION_CODE    => NULL
          ,  P_PRIMARY_UOM_CODE     => rec.PRIMARY_UOM_CODE
          ,  X_RETURN_STATUS        => x_return_status
          ,  X_MSG_COUNT            => x_msg_count
        );
      


       IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
          DBMS_OUTPUT.PUT_LINE('Error Messages :');
          Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
          FOR i IN 1..x_message_list.COUNT LOOP
             DBMS_OUTPUT.PUT_LINE(x_message_list(i).message_text);
          END LOOP;
       END IF;
  
    end loop;    
EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Exception Occured :' ||SQLERRM);
END ;