批量更新物料成本科目

 

--查询物料成本科目与库存组织不一致的物料

   select b.segment1,
        b.organization_id,
        mp.organization_code,
        b.inventory_item_status_code,
        gcck.concatenated_segments,--物料上的销售成本账户
        gcck2.concatenated_segments--组织参数上的销售成本账户
   from mtl_system_items_b b, gl_code_combinations_kfv gcck,mtl_parameters mp,gl_code_combinations_kfv gcck2
  where gcck.code_combination_id = b.cost_of_sales_account
  and b.organization_id=mp.organization_id
  and gcck2.code_combination_id = mp.cost_of_sales_account
  and b.cost_of_sales_account<>mp.cost_of_sales_account
 

--====================================

--批量更新逻辑

 

--1、Drop temp table
DROP TABLE secom_item_temp;

--2、Create item temp table

create table secom_item_temp(
organization_id number,
segment1 varchar2,
cost_of_sales_account_dsp
inventory_item_status_code varchar2,
process_flag varchar2,
cost_of_sales_account  number,
error_message varchar2)

--3 用PLSQL插入数据
select * from from secom_item_temp for update

--4 检查和获取销售成本账户ID
--4.1检查是否有重复的数据
Check two sames record  
 DECLARE
   CURSOR c
   IS
        SELECT segment1, 
       organization_id, 
       COUNT (*)
  FROM secom_item_temp
GROUP BY segment1, organization_id
  HAVING COUNT (*) > 1;
BEGIN
   FOR c1 IN c
   LOOP
      UPDATE secom_item_temp
         SET process_flag = 3,
             error_message = 'There are two same records!'
       where segment1 = c1.segment1;
   END LOOP;
commit；
END;


--4.2、将销售成本账户的ID插入到临时表

 update secom_item_temp dit
    set dit.cost_of_sales_account = 
      (select gcc.code_combination_id
        from gl_code_combinations gcc
       where gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4||'.'||gcc.segment5 = dit.cost_of_sales_account_dsp);

  commit;--提交
--4.3检查销售成本账户组合都找到了账户ID

select *
  from dsp_item_temp a
  where a.cost_of_sales_account is null 
    and a.cost_of_sales_account_dsp is not null;

--4.4 检查物料接口表,接口有数据则需要删除，目的是看到本次导入是否成功

select * 
  from mtl_system_items_interface ;
  
select * from mtl_interface_errors  ;

delete from mtl_system_items_interface;
delete from mtl_interface_errors;


--5 将数据插入到物料接口表


declare

cursor d is select * from secom_item_temp t where t.process_flag =1;

begin 
for d1 in d
loop

 INSERT INTO mtl_system_items_interface
                  (organization_id                  , 
                   segment1                         , 
		   cost_of_sales_account,
 		   inventory_item_status_code,
                   process_flag                     , 
                   transaction_type                 ,
                   set_process_id)
         SELECT d1.organization_id                  , 
                d1.segment1                       , 
                d1.cost_of_sales_account          ,
                d1.inventory_item_status_code
                1                                 ,
                'UPDATE'                          ,
                9999
           FROM DUAL;
COMMIT;
   END LOOP;
END;

--在系统中执行物料导入的请求，查看请求完成情况