FP变更物料编码（增加尾缀）

FP物料编码增加尾缀的程序，标准物料编码是18位，目前程序中增加尾缀的有三种1.DUMMY 2.SP开头 3.P开头 4.C开头 5.BP
对应处理程序如下：
1.DUMMY       SAP_MATERIAL_SO
2.SP开头      SAP_SO_BOM_PROC
3.P开头       SAP_SO_BOM_PROC
4.C开头  SAP_SO_QM_CUSTOMER   在IN_MO中
5.BP  SAP_OUTSHOP_PROC

 

增加C尾续涉及的表如下，存储过程为SAP_SO_BOM_OUTSOURCING_SEMI

 

select * from IN_BOM_HEADER WHERE BOM_ID LIKE '%C%'; --BOM_ID  写入新数
SELECT * FROM IN_ITEM WHERE ITEM_ID LIKE '%C%';--ITEM_ID  写入新数
select * from IN_ITEM_SITE WHERE ITEM_ID LIKE '%C%';--ITEM_ID 写入新数
select * from IN_BOM_HEADER WHERE BOM_ID LIKE '%C%';--BOM_ID 写入新数
select * from IN_BOM_DETAILS WHERE BOM_ID LIKE '%C%'; --BOM_ID 写入新数
select * from IN_ROUTINGOPERATION WHERE ROUTING_ID LIKE '%C%';--ROUTING_ID 写入新数
select * from IN_ITEMBOMROUTING WHERE BOM_ID LIKE '%C%';--BOM_ID 写入新数
select substr(ITEM_ID,1,18),substr(BOMID,1,16),A.* from IN_SALES_ORDER A WHERE ITEM_ID LIKE '%C%' or BOMID LIKE 'C%';---直接更新加尾续
select * from IN_MO WHERE ITEM_ID LIKE '%C%';---PRODUCT_ID 直接更新加尾续
select * from IN_SFCHEADER WHERE PRODUCT_ID LIKE '%C%';---直接更新加尾续
select * from IN_SFCLINE WHERE ITEM_ID LIKE '%C%';-- ITEM_ID -直接更新加尾续
select * from IN_MO_SHORT WHERE PRODUCT_ID LIKE '%C%';---PRODUCT_ID 直接更新加尾续

delete from IN_BOM_HEADER WHERE BOM_ID LIKE '%C%';
delete from IN_ITEM WHERE ITEM_ID LIKE '%C%';
delete from IN_ITEM_SITE WHERE ITEM_ID LIKE '%C%';
delete from IN_BOM_HEADER WHERE BOM_ID LIKE '%C%';
delete from iN_BOM_DETAILS WHERE BOM_ID LIKE '%C%';
delete from IN_ROUTINGOPERATION WHERE ROUTING_ID LIKE '%C%';
delete from  IN_ITEMBOMROUTING WHERE BOM_ID LIKE '%C%';
update  IN_SALES_ORDER SET ITEM_ID=substr(ITEM_ID,1,18) WHERE ITEM_ID LIKE '%C%' ;
update  IN_SALES_ORDER SET BOMID=substr(BOMID,1,16) WHERE BOMID LIKE '%C%' ;
update IN_MO SET  ITEM_ID=substr(ITEM_ID,1,18)  WHERE ITEM_ID LIKE '%C%';
update IN_SFCHEADER SET PRODUCT_ID= substr(PRODUCT_ID,1,18)   WHERE PRODUCT_ID LIKE '%C%';
update IN_SFCLINE SET ITEM_ID=substr(ITEM_ID,1,18)   WHERE ITEM_ID LIKE '%C%';
update  IN_MO_SHORT SET PRODUCT_ID= substr(PRODUCT_ID,1,18)  WHERE PRODUCT_ID LIKE '%C%';

 

 

C开头

 

--C_SO
SELECT SO.SO_ID,
                       SO.SO_LINE_ID,
                       QC.MATNR AS SEMI_ITEM,
                       BH.PRODUCT_ID AS ITEM_ID,
                       QC.ROW_ID AS CTYPE,
                       SO.CUSTOMER_ID,
                       SUBSTR (TRIM (BD.LOC_ID), 1, 4) AS SITE_ID
                  FROM SAP_QM_CUSTOMER QC,
                       IN_SALES_ORDER SO,
                       IN_BOM_DETAILS BD,
                       IN_BOM_HEADER BH
                 WHERE     TRIM (BH.BOM_ID) = TRIM (BD.BOM_ID)
                       AND SO.ITEM_ID = BH.PRODUCT_ID
                       AND TRIM (SO.BOMID) = TRIM (BH.BOM_ID)
                       AND QC.MATNR = BD.ITEM_ID
                       AND QC.KUNNR = SO.CUSTOMER_ID
                       AND EXISTS
                              (SELECT NULL
                                 FROM SAP_MAPL
                                WHERE QC.MATNR = SAP_MAPL.MATNR)
                       AND EXISTS
                              (SELECT NULL
                                 FROM SAP_MAPL
                                WHERE BH.PRODUCT_ID = SAP_MAPL.MATNR)

--更新SO的ITEM_ID
      UPDATE IN_SALES_ORDER SET IN_SALES_ORDER.ITEM_ID = IN_SALES_ORDER.ITEM_ID||C_SO.CTYPE,IN_SALES_ORDER.BOMID = TRIM(IN_SALES_ORDER.BOMID)||C_SO.CTYPE
      WHERE IN_SALES_ORDER.SO_ID = C_SO.SO_ID
        AND IN_SALES_ORDER.SO_LINE_ID = C_SO.SO_LINE_ID
        AND IN_SALES_ORDER.ITEM_ID NOT LIKE '%C%';