ORACLE中存储过程二

CREATE OR REPLACE PROCEDURE p_sap_data_import_2
Authid Current_User is
   v_table_count   NUMBER;
   v_sql           VARCHAR2 (1000);
BEGIN

begin
    begin
--下面开始去重，编新SAP物料号
      BEGIN
      SELECT COUNT (table_name)
        INTO v_table_count
        FROM tabs
       WHERE table_name = 'UE_ALLMATERIAL_TEMP_1_0724';
      IF (v_table_count > 0)
      THEN
         BEGIN
            v_sql := 'drop table ims.UE_ALLMATERIAL_TEMP_1_0724';
            EXECUTE IMMEDIATE v_sql;
         END;
      END IF;
      v_sql :=
         'create table UE_ALLMATERIAL_TEMP_1_0724 as (select * from ims.UE_ALLMATERIAL_TEMP_TEST where 物料号 is null)';
      EXECUTE IMMEDIATE v_sql;   
      End;
     
      begin
      SELECT COUNT (table_name)
        INTO v_table_count
        FROM tabs
       WHERE table_name = 'UE_ALLMATERIAL_TEMP_2_0724';
      IF (v_table_count > 0)
      THEN
         v_sql := 'drop table ims.UE_ALLMATERIAL_TEMP_2_0724';
         EXECUTE IMMEDIATE v_sql;
      END IF;
     
      v_sql:='create table UE_ALLMATERIAL_TEMP_2_0724 as (select * from ims.UE_ALLMATERIAL_TEMP_1_0724) ';
       execute immediate v_sql;
      end;--第一步去重  
begin                     
delete from ims.UE_ALLMATERIAL_TEMP_2_0724  where 序号 IN ( select 序号 from (select a.序号, row_number () over (partition by QC1UNION ORDER BY 序号) n from ims.UE_ALLMATERIAL_TEMP_2_0724 a)where n>1);
end;

select count(table_name) into v_table_count from tabs where table_name ='UE_SAP_QC1_0724';
if(v_table_count>0)
then
begin
v_sql:='drop table ims.UE_SAP_QC1_0724';
execute immediate v_sql;
end ;
end if ;

v_sql:='create table UE_SAP_QC1_0724 as
(select b.序号 QC1temp,a.序号, a.旧物料号,a.物料类型,a.物料号,a.QC3UNION,a.QC1UNION from ims.UE_ALLMATERIAL_TEMP_1_0724 a left join ims.UE_ALLMATERIAL_TEMP_2_0724 b on  a.QC1UNION  =  b.QC1UNION)';
execute immediate v_sql;

delete from UE_ALLMATERIAL_TEMP_2_0724  where 序号 IN
( select 序号 from
    (select a.序号, row_number () over (partition by 旧物料号 ORDER BY 序号) n from UE_ALLMATERIAL_TEMP_2_0724 a
        WHERE 旧物料号 IS NOT NULL AND f_subnum_alpher (旧物料号) = 'True' AND 物料类型 = 'C120 材料')
where n>1);

end;
-------------创建UE_SAP_QC2表---------------------------
 begin
select count(table_name) into v_table_count from tabs where table_name ='UE_SAP_QC2_0724';
if(v_table_count>0)
then
v_sql:='drop table UE_SAP_QC2_0724';
execute immediate v_sql;
end if ;

 v_sql:='create table UE_SAP_QC2_0724 as
(select b.序号 QC2temp, a.* from UE_SAP_QC1_0724 a left join
 (select * from
    (select a.序号,a.旧物料号,a.物料类型, row_number () over (partition by 旧物料号 ORDER BY 序号) n from UE_ALLMATERIAL_TEMP_2_0724 a
        WHERE 旧物料号 IS NOT NULL AND f_subnum_alpher (旧物料号) = ''True'' AND 物料类型 = ''C120 材料'')
       where n=1) b
on a.旧物料号||a.物料类型 = b.旧物料号||b.物料类型)';

execute immediate v_sql;

 v_sql:='update UE_SAP_QC2_0724 set QC2temp = QC1temp where QC2temp is null';
 execute immediate v_sql;
 
--第三步去重
delete from ims.UE_ALLMATERIAL_TEMP_2_0724  where 序号 IN
( select 序号 from
    (select a.序号, row_number () over (partition by QC3UNION ORDER BY 序号) n from ims.UE_ALLMATERIAL_TEMP_2_0724 a
        WHERE 规格型号 is not null and length(功能位置码)>8 and  功能位置码 IS NOT NULL)
where n>1);
end;

    begin
select count(table_name) into v_table_count from tabs where table_name ='UE_SAP_QC3_0724';
if(v_table_count>0)
then
v_sql:='drop table UE_SAP_QC3_0724';
execute immediate v_sql;
end if ;
 
v_sql:='create table UE_SAP_QC3_0724 as
(select b.序号 QC3temp, a.* from ims.UE_SAP_QC2_0724 a left join
 (select * from
    (select a.序号,a.QC3UNION, row_number () over (partition by QC3UNION ORDER BY 序号) n from ims.UE_ALLMATERIAL_TEMP_2_0724 a
        WHERE 规格型号 is not null and length(功能位置码)>8 and  功能位置码 IS NOT NULL)
where n=1)
 b on  a.QC3UNION  =  b.QC3UNION)';
 
execute immediate v_sql;
end;

    begin
update ims.UE_SAP_QC3_0724 set QC3temp = QC2temp where QC3temp is null ;
--编新SAP物料号
update ims.UE_ALLMATERIAL_TEMP_2_0724 set 物料号 =  '000000000011'||Lpad(C120.nextval,6,'0')   where 物料类型 like '%C120%'; --and 项目编码 ='AA'  --and 物料号 is null

update ims.UE_ALLMATERIAL_TEMP_2_0724 set 物料号 =  '00000000001'||Lpad(C110.nextval,7,'0')  where 物料类型 like '%C110%';

update ims.UE_ALLMATERIAL_TEMP_2_0724 set 物料号 =  '00000000003'||Lpad(C210.nextval,7,'0')  where 物料类型 like '%C210%';

update ims.UE_ALLMATERIAL_TEMP_2_0724 set 物料号 =  '00000000004'||Lpad(C220.nextval,7,'0')  where 物料类型 like '%C220%' ;

update ims.UE_ALLMATERIAL_TEMP_2_0724 set 物料号 =  '00000000005'||Lpad(C240.nextval,7,'0')  where 物料类型 like '%C240%' ;

update ims.UE_ALLMATERIAL_TEMP_2_0724 set 物料号 =  '00000000007'||Lpad(C310.nextval,7,'0')  where 物料类型 like '%C310%';

--更新UE_SAP_QC3中全部物料号，将物料号信息写入UE_ALLMATERIAL_TEMP
update ims.UE_SAP_QC3_0724 a set 物料号 = (select 物料号 from ims.UE_ALLMATERIAL_TEMP_2_0724 b  where a.QC3temp = b.序号 ) ;

update ims.UE_ALLMATERIAL_TEMP_TEST a set 物料号 = (select 物料号 from ims.UE_SAP_QC3_0724 b where a.序号=b.序号) where a.物料号 is null;
end;

----------------------------更新字典表--------------------------
    begin
--更新UE_ALLMATERIAL
INSERT INTO IMS.UE_ALLMATERIAL_TEST (
   序号, 流水号, 项目编码,
   通讯渠道号, 到货批次号, 箱号,
   合同号, 箱内物资描述, 存储级别,
   清洁等级, 净重_KG, 包装类型,
   提货单号, 发票号, 计划交货时间,
   供应商, LOT号, 箱内物项序号,
   合同物项序号, 功能位置码, 机组,
   岛别, 系统号, 旧物料号,
   UP号, 中文长文本_原始描述, 英文长文本_原始描述,
   制造号, 补充信息, 有效期,
   订购数量, 交付批次, 交付数量,
   剩余数量, 单位, 图纸号,
   类型, 是否安装备件, 是否运行备件,
   是否工具, 合格证号, 安全分级,
   质保分级, 材质, 规格型号,
   录入人, 箱单录入时间, 物料号,
   中文物料描述_处理后, 英文描述_处理后, 物料组,
   属性类, 物料类型, 采购组,
   QC1, QC2, QC3,
   WBS, 单价)
(SELECT
U.序号, U.流水号, U.项目编码,
   U.通讯渠道号, U.到货批次号, U.箱号,
   U.合同号, U.箱内物资描述, U.存储级别,
   U.清洁等级, U.净重_KG, U.包装类型,
   U.提货单号, U.发票号, U.计划交货时间,
   U.供应商, U.LOT号, U.箱内物项序号,
   U.合同物项序号, U.功能位置码, U.机组,
   U.岛别, U.系统号, U.旧物料号,
   U.UP号, U.中文长文本_原始描述, U.英文长文本_原始描述,
   U.制造号, U.补充信息, U.有效期,
   U.订购数量, U.交付批次, U.交付数量,
   U.剩余数量, U.单位, U.图纸号,
   U.类型, U.是否安装备件, U.是否运行备件,
   U.是否工具, U.合格证号, U.安全分级,
   U.质保分级, U.材质, U.规格型号,
   U.录入人, U.箱单录入时间, U.物料号,
   U.中文物料描述_处理后, U.英文描述_处理后, U.物料组,
   U.属性类, U.物料类型, U.采购组,
   U.QC1, U.QC2, U.QC3,
   U.WBS,
   U.单价
FROM IMS.UE_ALLMATERIAL_TEMP_TEST U);

--2、更新UE_SAPNOZIDIAN
INSERT INTO IMS.UE_SAPNOZIDIAN_TEST (
   物料号, 序号, 流水号,
   物料类型, 旧物料号, 中文物料描述_处理后,
   英文描述_处理后, 单位, 物料组,
   项目编码, 功能位置码, LOT号,
   机组, 岛别, 质保分级,
   安全分级, 系统号, 材质,
   工厂代码, 采购组, 属性类,
   英文长文本_原始描述, 中文长文本_原始描述, WBS,
   规格型号, QC1UNION, 移动平均价,
   QC3UNION, 单价)
 (  SELECT
   物料号, 序号, 流水号,
   物料类型, 旧物料号, 中文物料描述_处理后,
   英文描述_处理后, 单位, 物料组,
   项目编码, 功能位置码, LOT号,
   机组, 岛别, 质保分级,
   安全分级, 系统号, 材质,
   项目编码, 采购组, 属性类,
   英文长文本_原始描述, 中文长文本_原始描述, WBS,
   规格型号, QC1UNION, '',
   QC3UNION, 单价
    FROM IMS.UE_ALLMATERIAL_TEMP_2_0724 );
   
    end;
    begin
   
--3、更新UE_CAIGOUZIDIAN
select count(table_name) into v_table_count from tabs where table_name ='UE_CAIGOUZIDIAN_TEST';
if(v_table_count>0)
then
begin
v_sql:='drop table ims.UE_CAIGOUZIDIAN_TEST';
execute immediate v_sql;
end ;
end if ;

v_sql:='CREATE TABLE UE_CAIGOUZIDIAN_TEST AS (SELECT DISTINCT lot号, 采购组 FROM UE_SAPNOZIDIAN_TEST WHERE lot号 NOT LIKE ''LOT120%'')';
execute immediate v_sql;

--v_sql:='CREATE INDEX lot号SAP ON UE_CAIGOUZIDIAN_TEST (lot号)';
--execute immediate v_sql;

--4、更新UE_ZONGZIDIAN
select count(table_name) into v_table_count from tabs where table_name ='UE_ZONGZIDIAN_TEST';
if(v_table_count>0)
then
begin
v_sql:='drop table ims.UE_ZONGZIDIAN_TEST';
execute immediate v_sql;
end ;
end if ;

v_sql:='Create table UE_ZONGZIDIAN_TEST  as (select * from UE_ALLMATERIAL_TEST)';
execute immediate v_sql;

--v_sql:='CREATE INDEX index序号 ON UE_ZONGZIDIAN_TEST (序号)';
--execute immediate v_sql;

delete FROM  UE_ZONGZIDIAN_TEST
      WHERE 序号 IN (
               SELECT 序号
                 FROM (SELECT 序号,
                              ROW_NUMBER () OVER (PARTITION BY 中文长文本_原始描述, LOT号  ORDER BY 中文长文本_原始描述 DESC)
                                                                           rk,
                              中文长文本_原始描述, LOT号
                         FROM  UE_ZONGZIDIAN_TEST)
                WHERE rk > 1);
          end;        
          ------------------------------begin----------------------
   --输出装箱单表数据
  
   delete from  IMS.UE_XIANGDAN;
  
INSERT INTO IMS.UE_XIANGDAN (
   "序号", "到货批次号", "合同号",
   "LOT号", "箱号", "箱内物项序号",
   "采购单号", "项目", "物料号",
   "中文物料描述_处理后", "功能位置码", "UP号",
   "制造号", "补充信息", "有效期",
   "订购数量", "交付批次", "交付数量",
   "剩余数量", "单位", "图纸号",
   类型, "合格证号",WBS, "单价")
 (SELECT
   "序号", "到货批次号", "合同号",
   "LOT号", "箱号", "箱内物项序号",
   '', '', substr(物料号,11,8),
   "中文物料描述_处理后", "功能位置码", "UP号",
   "制造号", "补充信息", "有效期",
   "订购数量", "交付批次", "交付数量",
   "剩余数量", "单位", "图纸号",
   类型, "合格证号", WBS, "单价"
FROM IMS.UE_ALLMATERIAL_TEMP_test  );

--输出SAP物料主数据新物料号

delete from ims.UE_SAPMMINPUT;

INSERT INTO  ims.UE_SAPMMINPUT (
   /* Formatted on 2009/07/01 16:27 (Formatter Plus v4.8.6) */
SELECT 物料号, regexp_substr (物料类型, '[0-9a-zA-Z_]+') AS 物料类型,
       CASE
          WHEN 项目编码 = 'AA'
             THEN '2007'
          WHEN 项目编码 = 'AB'
             THEN '2005'
          WHEN 项目编码 = 'PY'
             THEN '2004'
          WHEN 项目编码 = 'PK'
             THEN '2003'
          ELSE 项目编码
       END 工厂,
       中文物料描述_处理后, regexp_substr (物料组, '[0-9a-zA-Z_]+') AS 物料组,
        单位, "旧物料号", 中文长文本_原始描述 长文本, 'X' 采购视图,
       'X' 会计视图, 'X' 成本视图, 'X' mrp视图, 'X' 仓储视图,
       regexp_substr (采购组, '[0-9a-zA-Z_]+') AS 采购组,
       CASE
          WHEN 物料类型 = 'C120 材料'
             THEN '1130'
          WHEN 物料类型 = 'C110 核燃料'
             THEN '1120'
          WHEN 物料类型 = 'C210 设备'
             THEN '1300'
          WHEN 物料类型 = 'C220 配件部件'
             THEN '1310'
          WHEN 物料类型 = 'C310 工具'
             THEN ''
          WHEN 物料类型 = 'C240 备品备件'
             THEN '1320'
       END 评估类,
       'V' 价格控制, '' mrp类型,
       CASE
          WHEN 物料类型 = 'C120 材料'
             THEN '402'
          WHEN 物料类型 = 'C110 核燃料'
             THEN '401'
          WHEN 物料类型 = 'C210 设备'
             THEN '403'
          WHEN 物料类型 = 'C220 配件部件'
             THEN '404'
          WHEN 物料类型 = 'C310 工具'
             THEN ''
          WHEN 物料类型 = 'C240 备品备件'
             THEN '405'
          WHEN 物料类型 = 'C320 运输工具'
             THEN '409'
       END mrp控制者,
       '' 批量大小, '' 计划交货时间, '' 收货处理时间, '' 计划边际码,
       '' 可用性检查, '' 移动平均价格,
       CASE
          WHEN 项目编码 = 'AA'
             THEN '2007001001'
          WHEN 项目编码 = 'AB'
             THEN '2005001001'
          WHEN 项目编码 = 'PY'
             THEN '2004001001'
          WHEN 项目编码 = 'PK'
             THEN '2003001001'
       END 利润中心,
         项目编码, "LOT号",  功能位置码 设备功能码, "机组", "岛别",
         系统号, "质保分级", "安全分级", "材质", 规格型号
  FROM UE_ALLMATERIAL_TEMP_2_0724 a );
         
          --------------------------------end-----------------------
         
  INSERT into ims.UE_ALLMATERIAL_TEMP_backup
   SELECT * FROM UE_ALLMATERIAL_TEMP_TEST;
  
             INSERT into ims.UE_ALLMATERIAL_TEMP_2backup
   SELECT * FROM UE_ALLMATERIAL_TEMP_2_0724;
  
   delete from ims.UE_ALLMATERIAL_TEMP_CurrDay;
  
   delete from ims.UE_ALLMATERIAL_TEMP_2CurrDay;
  
   insert into ims.UE_ALLMATERIAL_TEMP_CurrDay select * from ims.UE_ALLMATERIAL_TEMP_TEST;

   insert into ims.UE_ALLMATERIAL_TEMP_2CurrDay select * from ims.UE_ALLMATERIAL_TEMP_2_0724;
         
   delete from UE_ALLMATERIAL_TEMP_TEST;
         
  delete from UE_ALLMATERIAL_TEMP_2_0724;
     end; 
     commit;         
          EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         INSERT INTO ims.imscm_exception_msg
                     (exception_code, exception_msg
                     )
              VALUES (SYS_GUID (), '' || 'SAP数据处理创建表出错!'
                     );
                    
      WHEN OTHERS
      THEN
         INSERT INTO ims.imscm_exception_msg
                     (exception_code, exception_msg
                     )
              VALUES (SYS_GUID (), '' || 'SAP数据处理创建表出错!'
                     );   
END p_sap_data_import_2;