按单生产方案外修改内容

一、案例二：000000102004000033 （只有SO号且无库存，工单MO还没开）

000000102004000033CA    成品编码000000101004002267CA  5000163999_10
000000102004000033      成品编码000000101004010853     6900002438_180

1、98现时数据

2、117修改后第一版效果图如下（有问题）：只有SO没有库存的没有MFG000和MFG001产生出来

3、PAUL回复需要修改：沒帶批量就只要新增MST_ITEMBOMROUTING_P 多數據等於200（FP_MFG）

 --方案外：沒帶批量就只要新增MST_ITEMBOMROUTING_P 多數據等於200 add by landor on 20180522 20180601
     INSERT INTO ABPPMGR.MST_ITEMBOMROUTING_P    (SCENARIO_ID,
                                                 ENTERPRISE,
                                                 SITEID,
                                                 ENGINE_ID,
                                                 BOMID,
                                                 ROUTINGID,
                                                 ITEM,
                                                 ATTRIBUTE,
                                                 ATTRIBUTECLASS,
                                                 RELATIONSHIP,
                                                 VALUE,
                                                 SYS_CREATED_BY)
    SELECT DISTINCT   AM.SCENARIO_ID
                     ,AM.ENTERPRISE
                     ,AM.SITEID
                     ,AM.ENGINE_ID
                     ,Am.Bomid
                     ,CASE WHEN AM.ROUTINGID LIKE '%ORDER_BOM%' THEN AP.ROUTINGID||'_ORDER_BOM'
                      ELSE AP.ROUTINGID
                      END AS ROUTINGID
                     ,Am.Item
                     ,'ORDER_PLANNING' ATTRIBUTE
                     , SUBSTR(AP.ATTRIBUTECLASS,1,INSTR(AP.ATTRIBUTECLASS,':')-1)||':200' AS ATTRIBUTECLASS
                     ,'EQ'
                     , '200' AS VALUE
                     ,'AnDan'
      From  Abppmgr.Mst_Itembomrouting Am
         join Abppmgr.Mst_Itembomrouting_P Ap on Am.item=Ap.item and Am.bomid=ap.bomid 
         WHERE  MAXLOTSIZE IS NULL AND MINLOTSIZE IS NULL  ;--代表无批量数据，只需增加200
      COMMIT;

 修改后效果图如下：有异常（按单生产的真验货客户使用了24个非限制库存）

 4、去掉XXX后OK

 二、非限制MO被按单使用

针对非限制MO不能被按单生产使用，只需要改MST_ITEMBOMROUTING_P表。当當IN_MO中的MO是非限制的MO則使用此ROUTINGID去MST_ITEMBOMROUTING_P中找相同的ROUTINGID，然後在找當ATTRIBUTE=ORDER_PLANNING，把此筆記錄中的value改為0。

MST_ITEMBOMROUTING_P中ATTRIBUTE=ORDER_PLANNING且VALUE=200表示只可以給按單生產訂單使用,当VALUE=0表示只能給非按單生產訂單使用,当VALUE=100表示給沒批量按單生產使用

三、MTL场景

案例五：成品MO REL+半成品库存(98 OK的，117测试环境不OK)
5000166118/820_1
000000101005006567CZ   001100923737;
000000102005000263CZ   none
MTL_001100923737_2023/000000102005000263CZ   2023/2007

 

原因是MTL的訂單當屬於那個訂單對應工單的短缺料需要指定到訂單號+行號，在salesorderline_P中修改attributeclasss(當attribute=ORDER_PLAIING)。

MTL需求編號中MTL後面帶的是MOID+廠別+編號，可以用MOID去in_mo及in_sfcheader找到對應的SO就可以知道是否為按單生產訂單

修改后：另外需要将0的去掉

以下是PAUL在2018.6.19提示需要删除

 四、库存绑定不成功

案例七：成品（CRTD）MO+半成品库存
5000164149/810_1
000000101011012231CZ   001100939675;
000000102011001139CZ   2022/2016;

注意以下图的LOCATION、LEVEL1、LEVEL2都需要跟supplydmdpegging保持一致

 可参考如下图

上述处理完后还无法吃库存,作以下处理

 上述处理完后仍无法吃库存，还需要作以下修改（PAUL口中的方案一）

更正下图中的OPEATIONSEQ必须为空，

  PAUL口中的方案二

案例八、成品MFG+半成品MO+半成品库存（问题描述，MFG无法绑定库存）

5000173100/10_1 
000000101001027737CA 　　 5000173100/10_1-MFG001
000000102001001646CA 　　 2023/2010，001201241504，001201238002

SELECT locationid,lotlevel1,lotlevel2,ITEM,SERIALNUM, DEMANDORDERID,DEMANDLINEID,DEMANDTYPE,OPERATIONSEQ,QTYINMOVE,SOURCEDATE,SUPPLYORDERID,SUPPLYTYPE，QTYALLOCATED
 FROM ABPPMGR.supplydmdpegging WHERE (DEMANDORDERID='5000173100' AND DEMANDLINEID='10_1') OR DEMANDORDERID='5000173100/10_1-MFG000' OR DEMANDORDERID='5000173100/10_1-MFG001'
 OR DEMANDORDERID IN('001201241504','001201238002') OR SUPPLYORDERID IN ('001201241504','001201238002') ;

下图为错误数据

7月4日修改后仍然有误

7月4日修改后仍然有误，如下图

SELECT locationid,lotlevel1,lotlevel2,ITEM,SERIALNUM, DEMANDORDERID,DEMANDLINEID,DEMANDTYPE,OPERATIONSEQ,QTYINMOVE,SOURCEDATE,SUPPLYORDERID,SUPPLYTYPE，QTYALLOCATED
 FROM ABPPMGR.supplydmdpegging WHERE (DEMANDORDERID='5000173100' AND DEMANDLINEID='10_1') OR DEMANDORDERID='5000173100/10_1-MFG000' OR DEMANDORDERID='5000173100/10_1-MFG001'
 OR DEMANDORDERID IN('001201241504','001201238002') OR SUPPLYORDERID IN ('001201241504','001201238002') ;
 
 SELECT A.* FROM ABPPMGR.supplydmdpeg_p A   
 WHERE  ITEM='000000102001001646' AND (DEMANDORDERID LIKE '5000173100%' or DEMANDORDERID LIKE '%1201241504%' or DEMANDORDERID LIKE '%1201238002%') ORDER BY ATTRIBUTE;

 7月5号修改为

 

 

案例二：成品REL-MO（产生MTL）+CRTD MO

5000172861/1160
000000101001030648  001100977723(REL)
000000102001001967  001201263574(CRTD)

 

 

十、解决成品MFG+半成品库存+工单

5000179765/110_1
000000101001020315 5000179765/110_1-MFG001;
000000102001001205 001201256950; 5000179765110_2023/2010

十一、解决非限制MO分配错误问题

 十二、 解决按单真验货吃非限制库存的问题，清理需求表的属性值  2018.7.29切换上线

 

 

十三、解决AUTO的工单报short问题

十四、外购半成品报short的问题

十五、MTL加工辅助件报short问题修复

十六、非限制MO分配MTL工单后不再分配给SO的需求

新建表IN_MO_SFCHEADER
存储过程FP_MOSFCHEADER
修改存储过程SAP_SO_BOM_PROC

修改存储过程FP_MFG

修改存储过程FP_MFG(2018.8.28增加，2018.8.29删除掉了)

 

    --方案外：按单且沒帶批量就只要新增MST_ITEMBOMROUTING_P 多數據等於200 add by landor on 20180522 20180601
     INSERT INTO ABPPMGR.MST_ITEMBOMROUTING_P    (SCENARIO_ID,
                                                 ENTERPRISE,
                                                 SITEID,
                                                 ENGINE_ID,
                                                 BOMID,
                                                 ROUTINGID,
                                                 ITEM,
                                                 ATTRIBUTE,
                                                 ATTRIBUTECLASS,
                                                 RELATIONSHIP,
                                                 VALUE,
                                                 SYS_CREATED_BY)
     SELECT DISTINCT   AM.SCENARIO_ID
                     ,AM.ENTERPRISE
                     ,AM.SITEID
                     ,AM.ENGINE_ID
                     ,AM.BOMID  
                     ,CASE WHEN AM.ROUTINGID LIKE '%ORDER_BOM%' THEN AP.ROUTINGID||'_ORDER_BOM'
                      ELSE AP.ROUTINGID
                      END AS ROUTINGID
                     ,AM.ITEM
                     ,'ORDER_PLANNING' ATTRIBUTE
                     , SUBSTR(AP.ATTRIBUTECLASS,1,INSTR(AP.ATTRIBUTECLASS,':')-1)||':200' AS ATTRIBUTECLASS
                     ,'EQ'
                     , '200' AS VALUE
                     ,'AnDan' 
      FROM  ABPPMGR.MST_ITEMBOMROUTING AM
      JOIN ABPPMGR.MST_ITEMBOMROUTING_P AP on AM.ITEM=AP.ITEM and AM.ENGINE_ID=AP.ENGINE_ID AND   AM.BOMID=AP.BOMID --增加BOMID关联 add by landor on 20180828
      JOIN IN_MO_SFCHEADER D ON D.ROUTING_ID= AP.ROUTINGID --判断是否存在MO与SO绑定关系，并且存在IN_ORDER_PLANNING
      WHERE  MAXLOTSIZE IS NULL AND MINLOTSIZE IS NULL  --代表无批量数据，只需增加200
      AND SUBSTR(AP.ROUTINGID,1,3)='001' 
      AND EXISTS(SELECT  NULL FROM IN_ORDER_PLANNING M WHERE D.ITEM_ID=M.SEMI_ITEM_ID AND M.SO_ID=D.SO_ID) 
      AND  EXISTS (SELECT NULL FROM IN_ORDER_PLANNING C WHERE C.SEMI_ITEM_ID=AM.ITEM)--增加按单条件add by landor on 20180828
     -- AND AM.ITEM='000000102011000515'  
      
      UNION
       SELECT DISTINCT   AM.SCENARIO_ID
                     ,AM.ENTERPRISE
                     ,AM.SITEID
                     ,AM.ENGINE_ID
                     ,AM.BOMID
                     ,CASE WHEN AM.ROUTINGID LIKE '%ORDER_BOM%' THEN AP.ROUTINGID||'_ORDER_BOM'
                      ELSE AP.ROUTINGID
                      END AS ROUTINGID
                     ,AM.ITEM
                     ,'ORDER_PLANNING' ATTRIBUTE
                     , SUBSTR(AP.ATTRIBUTECLASS,1,INSTR(AP.ATTRIBUTECLASS,':')-1)||':200' AS ATTRIBUTECLASS
                     ,'EQ'
                     , '200' AS VALUE
                     ,'AnDan'
      FROM  ABPPMGR.MST_ITEMBOMROUTING AM
      JOIN ABPPMGR.MST_ITEMBOMROUTING_P AP on AM.ITEM=AP.ITEM and AM.ENGINE_ID=AP.ENGINE_ID  AND   AM.BOMID=AP.BOMID --增加BOMID关联add by landor on 20180828
      WHERE  MAXLOTSIZE IS NULL AND MINLOTSIZE IS NULL  --代表无批量数据，只需增加200
       AND SUBSTR(AP.ROUTINGID,1,3)!='001'
      AND  EXISTS (SELECT NULL FROM IN_ORDER_PLANNING C WHERE C.SEMI_ITEM_ID=AM.ITEM)--增加按单条件add by landor on 20180828
     -- AND AM.ITEM='000000102011000515'
      ;                                                 
  /*  SELECT DISTINCT   AM.SCENARIO_ID
                     ,AM.ENTERPRISE
                     ,AM.SITEID
                     ,AM.ENGINE_ID
                     ,Am.Bomid
                     ,CASE WHEN AM.ROUTINGID LIKE '%ORDER_BOM%' THEN AP.ROUTINGID||'_ORDER_BOM'
                      ELSE AP.ROUTINGID
                      END AS ROUTINGID
                     ,Am.Item
                     ,'ORDER_PLANNING' ATTRIBUTE
                     , SUBSTR(AP.ATTRIBUTECLASS,1,INSTR(AP.ATTRIBUTECLASS,':')-1)||':200' AS ATTRIBUTECLASS
                     ,'EQ'
                     , '200' AS VALUE
                     ,'AnDan'
      From  Abppmgr.Mst_Itembomrouting Am
         join Abppmgr.Mst_Itembomrouting_P Ap on Am.item=Ap.item and Am.bomid=ap.bomid 
         WHERE  MAXLOTSIZE IS NULL AND MINLOTSIZE IS NULL  ;--代表无批量数据，只需增加200
         */
      COMMIT;

 

FP_POST_PROCESS（保留）

CREATE OR REPLACE PROCEDURE STG.FP_POST_PROCESS (EXITCODE OUT NUMBER)
IS
   /***********************************************************************
          **  存储过程名称:  FP_POST_PROCESS
         **  存储过程创建日期: 2014/7/21
         **  存储过程创建人: Tanxiang
         **  目的:  处理一些附加逻辑
         1.    检验时间:目前所有採購前置時間及在途到料時間都不包含檢驗時間，需要把檢驗時間加上去。
        2.    动态安全库存: Calendar based Safety Lead Times for Procured Parts
        3.    保税非保税处理/验货客户处理: 原材設定屬性讓IN_ITEM_SITE.PROC_TYPE是E70或F71可以使用非保稅庫存
        4.   原材没有供货商主数据:当IN_ITEM_SITE.PROC_TYPE为外购，可是在IN_SUPPLIER_ITEM没数据时需要处理
        5    工艺路线及虚拟车间不全:当MST_OPRESOURCE.WORKCENTERNAME中没有对应到数据做处理

         **  输入参数:
         **  输出参数:EXITCODE 预留接口给外部调用者，让其知道存储过程的执行结果，0.预定逻辑执行完成; 1.遇到异常，预定逻辑未执行完成
         **  返回值:
         **  用法:
         **
         ** 修订版本:
         **  版本号     修订时间    修订人        描述
         **  ---------  ----------  ------------  ------------------------
         **    1.0      2014/7/10        Tanxiang 1. 创建存储过程.
         **   2.0      2014/12/02       Huangyangxiong 2.VMI物料的采购周期由0天修改为SAP系统维护的时间
         **   3.0      2016/12/22      Landor   2.取消物化视图
         **     4.0     2018.05.22      Landor  增加按单生产业务
    **********************************************************************/
   ----------------------- 以下定义存储过程使用的变量---------------------------------
   V_PKGName          VARCHAR2 (40) := 'PKG_FP';
   V_ProName          VARCHAR2 (40) := 'FP_POST_PROCESS';
   V_Action           VARCHAR2 (1);
   V_Step             NUMBER := 0;
   V_MainTable        VARCHAR2 (40);
   E_ErrMessage       VARCHAR2 (3000);
   V_Engine           NUMBER;
   V_Enterprise       VARCHAR2 (40);
   V_SiteID           VARCHAR2 (40);
   V_ScenarioID       NUMBER;
   V_OrganizationID   VARCHAR2 (40);
   --   V_SupplierOrganization   VARCHAR2 (40);
   --   V_PurOrganizationID      VARCHAR2 (40);
   --   V_SupplierEnterprise     VARCHAR2 (40);
   --   V_CalendarName           VARCHAR2 (40);
   --   V_ProcureBucketName      VARCHAR2 (40);
   ---   V_ItemGroup              VARCHAR2 (40);
   V_LocationID       VARCHAR2 (40) := 'DUMMY_LOC';
   V_CalendarType     VARCHAR2 (40) := 'PERCENTAGE_SUPPLY';
   V_WorkcenterName   VARCHAR2 (40) := 'DUMMY_RESOURCE';
   V_CurrentTime      DATE := SYSDATE;
   V_SupplierItemCount number;
----------------------- 以下是存储过程的主体------------------------------------------

BEGIN
   EXITCODE := 0;
   V_Engine := PKG_CONSTANT.CONS_FP_ENGINE;
   V_Enterprise := PKG_CONSTANT.CONS_ENTERPRISE;
   V_SiteID := PKG_CONSTANT.CONS_SITE_ZHUHAI;
   V_ScenarioID := 0;
   V_OrganizationID := 'ORG';
   --V_SupplierOrganization := 'ORG';
   --V_PurOrganizationID := 'ORG';
   --V_SupplierEnterprise := PKG_CONSTANT.CONS_ENTERPRISE;
   --V_ProcureBucketName := 'FP_PLAN_BUCKET';
   --V_ItemGroup := 'PROC';

   V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_UPDATE;
   V_MainTable := 'MST_APPRSUPITEM,PURCHORDLINE';
   --检验时间:目前所有採購前置時間及在途到料時間都不包含檢驗時間，需要把檢驗時間加上去
   PKG_UTIL.CREATELOG (
      V_PKGName,                                       --Put package name here
      V_ProName,                                     --Put procedure name here
      V_Step,                                       --Step seqence No. if have
      V_Action,                                       --Action 'I','D','U','T'
      PKG_CONSTANT.CONS_MSG_INFO,     --Message tppe, 'SUCCESS' or 'EXCEPTION'
      'add IN_ITEM.EXAMINE_TIME to MST_APPRSUPITEM and PURCHORDLINE', --Any generic information here
      V_MainTable);                               --Major table name processed

   MERGE INTO ABPPMGR.MST_APPRSUPITEM M
        USING IN_ITEM IM
           ON (IM.ITEM_ID = M.ITEM)
   WHEN MATCHED
   THEN
      UPDATE SET
         M.AVGLEADTIME = NVL (M.AVGLEADTIME, 0) + NVL (IM.EXAMINE_TIME, 0),
         M.SYS_LAST_MODIFIED_BY = V_ProName,
         M.SYS_LAST_MODIFIED_DATE = V_CurrentTime;

   MERGE INTO ABPPMGR.PURCHORDLINE M
        USING IN_ITEM IM
           ON (IM.ITEM_ID = M.ITEM)
   WHEN MATCHED
   THEN
      UPDATE SET
         M.SCHEDULEDDELRYDATE =
            M.SCHEDULEDDELRYDATE + NVL (IM.EXAMINE_TIME, 0),
         M.SYS_LAST_MODIFIED_BY = V_ProName,
         M.SYS_LAST_MODIFIED_DATE = V_CurrentTime;

   COMMIT;
   V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_UPDATE;
   V_MainTable := 'MST_APPRSUPITEM,PURCHORDLINE';
   --检验时间:目前所有採購前置時間及在途到料時間都不包含檢驗時間，需要把檢驗時間加上去
   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here
                       V_ProName,                    --Put procedure name here
                       V_Step,                      --Step seqence No. if have
                       V_Action,                      --Action 'I','D','U','T'
                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'
                       'process IN_ITEM.EXAMINE_TIME success.', --Any generic information here
                       V_MainTable);              --Major table name processed

   V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_UPDATE;
   V_MainTable := 'MST_APPRSUPITEM,PURCHORDLINE';
   --检验时间:目前所有採購前置時間及在途到料時間都不包含檢驗時間，需要把檢驗時間加上去
   PKG_UTIL.CREATELOG (
      V_PKGName,                                       --Put package name here
      V_ProName,                                     --Put procedure name here
      V_Step,                                       --Step seqence No. if have
      V_Action,                                       --Action 'I','D','U','T'
      PKG_CONSTANT.CONS_MSG_INFO,     --Message tppe, 'SUCCESS' or 'EXCEPTION'
      'add IN_ITEM.EXAMINE_TIME to MST_APPRSUPITEM and PURCHORDLINE', --Any generic information here
      V_MainTable);                               --Major table name processed

     DELETE FROM ABPPMGR.MST_ITEMSITELTPARM ;
    COMMIT;
    INSERT INTO ABPPMGR.MST_ITEMSITELTPARM(SCENARIO_ID,
                  ENTERPRISE,
                  ENGINE_ID,
                  SITEID,
                  ITEM,
                  ESTIMATEDTIME,
                  TIMEUOM,
                  SYS_CREATED_BY)
    SELECT V_SCENARIOID,
                  V_ENTERPRISE,
                  V_ENGINE,
                  V_SITEID,
                   IIS.ITEM_ID, 
                   MAX(IIS.ESTIMATEDTIME) ESTIMATEDTIME,
                   'DAYS' TIMEUOM,
                   V_PRONAME
       FROM IN_ITEM_SITE IIS where IIS.ESTIMATEDTIME IS NOT NULL GROUP BY IIS.ITEM_ID;
      COMMIT;
IF 1=0 THEN
--start remark 2014.10.14 动态安全库存实现方式错误，修改为写入MST_ITEMSITELTPARM
  MERGE INTO ABPPMGR.MST_CALENDARMASTER M
        USING (SELECT 'CSLT' || LTRIM (T2.ITEM_ID,'0') || '_' ||T2.SUPPLIER_ID || REPLACE(T2.LOCATION_ID,T1.SITE_ID,NULL)
                         CALENDARNAME
              from   IN_ITEM_SITE T1,
               IN_SUPPLIER_ITEM T2
         WHERE T1.SITE_ID = SUBSTR(T2.SUPPLIER_ID,1,4) AND T1.ITEM_ID = T2.ITEM_ID AND T1.ESTIMATEDTIME IS NOT NULL) IM
           ON (M.CALENDARNAME = IM.CALENDARNAME)
   WHEN MATCHED
   THEN
      UPDATE SET
         M.SYS_ENT_STATE = 'ACTIVE',
         M.SYS_LAST_MODIFIED_BY = V_ProName,
         M.SYS_LAST_MODIFIED_DATE = V_CurrentTime
   WHEN NOT MATCHED
   THEN
      INSERT     (SCENARIO_ID,
                  ENTERPRISE,
                  ENGINE_ID,
                  CALENDARNAME,
                  SYS_CREATED_BY)
          VALUES (V_ScenarioID,
                  V_ENTERPRISE,
                  V_ENGINE,
                  IM.CALENDARNAME,
                  V_ProName);

   V_Step := V_Step + 1;                                                 -- 0;
   V_Action := PKG_CONSTANT.CONS_ACTION_MERGE;
   V_MainTable := 'MST_CALENDARMASTER';
   --记录MST_CALENDARMASTER导入日志
   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here
                       V_ProName,                    --Put procedure name here
                       V_Step,                      --Step seqence No. if have
                       V_Action,                      --Action 'I','D','U','T'
                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'
                       'Success: ' || SQL%ROWCOUNT || ' rows imported.', --Any generic information here
                       V_MainTable);              --Major table name processed
   COMMIT;

   SELECT COUNT(*) INTO V_SUPPLIERITEMCOUNT FROM ABPPMGR.MST_CALENDARDETAIL;
   COMMIT;
   V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_INSERT;
   V_MainTable := 'MST_CALENDARDETAIL,MST_CALBASEDATTR';
   --3.    动态安全库存: Calendar based Safety Lead Times for Procured Parts
   PKG_UTIL.CREATELOG (
      V_PKGName,                                       --Put package name here
      V_ProName,                                     --Put procedure name here
      V_Step,                                       --Step seqence No. if have
      V_Action,                                       --Action 'I','D','U','T'
      PKG_CONSTANT.CONS_MSG_INFO,     --Message tppe, 'SUCCESS' or 'EXCEPTION'
      'prepare attribute data for IN_ITEM_SITE.ESTIMATEDTIME into MST_CALENDARDETAIL, MST_CALBASEDATTR', --Any generic information here
      V_MainTable);                               --Major table name processed
   V_CalendarType := 'PROCURE_LEAD_TIME';

     DELETE FROM ABPPMGR.MST_CALBASEDATTR WHERE SYS_CREATED_BY=V_PRONAME;
    COMMIT;
    DELETE FROM ABPPMGR.MST_CALENDARDETAIL WHERE SYS_CREATED_BY=V_PRONAME;
    COMMIT;
   INSERT INTO ABPPMGR.MST_CALENDARDETAIL (SCENARIO_ID,
                                           ENTERPRISE,
                                           ENGINE_ID,
                                           CALENDARNAME,
                                           CALENDARTYPE,
                                           EFFSTARTDATE,
                                           EFFENDDATE,
                                           SYS_LAST_MODIFIED_BY,
                                           PATTERNSEQ,
                                           SHIFTNUMBER,
                                           SYS_CREATED_BY)
        SELECT V_ScenarioID,
               V_ENTERPRISE,
               V_ENGINE,
               'CSLT' || LTRIM (M.ITEM_ID,'0') || '_' ||M.SUPPLIER_ID || REPLACE(M.LOCATION_ID,IM.SITE_ID,NULL) CALENDARNAME,
               V_CalendarType,
               IM.EFFECTIVE_START_DATE,
               IM.EFFECTIVE_END_DATE,
               IM.ITEM_ID||IM.SITE_ID SYS_LAST_MODIFIED_BY,
               V_SUPPLIERITEMCOUNT+ROWNUM PATTERNSEQ,
               1 SHIFTNUMBER,
               V_ProName SYS_CREATED_BY
          FROM IN_ITEM_SITE IM,
               IN_SUPPLIER_ITEM M
         WHERE IM.SITE_ID = substr(M.SUPPLIER_ID,1,4) AND IM.ITEM_ID = M.ITEM_ID AND IM.ESTIMATEDTIME IS NOT NULL;
   COMMIT;

    INSERT INTO ABPPMGR.MST_CALBASEDATTR (SCENARIO_ID,
                                          ENTERPRISE,
                                          ENGINE_ID,
                                          ATTRIBUTE,
                                          CALENDARNAME,
                                          PATTERNSEQ,
                                          SHIFTNUMBER,
                                          VALUE,
                                          VALUEUOM,
                                          SYS_CREATED_BY)
       SELECT CD.SCENARIO_ID,
              CD.ENTERPRISE,
              CD.ENGINE_ID,
              'PROCURE_LEAD_TIME' ATTRIBUTE,
              CD.CALENDARNAME,
              CD.PATTERNSEQ,
              CD.SHIFTNUMBER,
              IM.ESTIMATEDTIME * 24 * 60 * 60,
              'SECONDS' VALUEUOM,
              V_ProName SYS_CREATED_BY
         FROM ABPPMGR.MST_CALENDARDETAIL CD, IN_ITEM_SITE IM
        WHERE CD.SYS_LAST_MODIFIED_BY = IM.ITEM_ID || IM.SITE_ID
              AND IM.ESTIMATEDTIME IS NOT NULL;


   V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_INSERT;
   V_MainTable := 'MST_CALENDARDETAIL,MST_CALBASEDATTR';
   --3.    动态安全库存: Calendar based Safety Lead Times for Procured Parts
   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here
                       V_ProName,                    --Put procedure name here
                       V_Step,                      --Step seqence No. if have
                       V_Action,                      --Action 'I','D','U','T'
                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'
                       'Success: ' || SQL%ROWCOUNT || ' rows imported.', --Any generic information here
                       V_MainTable);
    COMMIT;
    DELETE FROM ABPPMGR.MST_SUPPLIERCALENDAR M WHERE M.SYS_CREATED_BY=V_PRONAME;
    COMMIT;
    INSERT INTO ABPPMGR.MST_SUPPLIERCALENDAR (SCENARIO_ID,
                                             ENTERPRISE,
                                             SITEID,
                                             ENGINE_ID,
                                             --PURORGANIZATIONID,
                                             CALENDARNAME,
                                             CALENDARTYPE,
                                             ITEM,
                                             --ITEMGROUP,
                                             SUPPLIERID,
                                             SYS_CREATED_BY)
      SELECT V_ScenarioID,
               V_ENTERPRISE,
               V_SiteID,
               V_ENGINE,
               'CSLT' || LTRIM (M.ITEM_ID,'0') || '_' ||M.SUPPLIER_ID || REPLACE(M.LOCATION_ID,IM.SITE_ID,NULL)  CALENDARNAME,
               V_CALENDARTYPE CALENDARTYPE,
               M.ITEM_ID ITEM,
              M.SUPPLIER_ID SUPPLIERID,
              V_PRONAME SYS_CREATED_BY
          FROM IN_ITEM_SITE IM,
               IN_SUPPLIER_ITEM M
         WHERE IM.SITE_ID = SUBSTR(M.SUPPLIER_ID,1,4) AND IM.ITEM_ID = M.ITEM_ID AND IM.ESTIMATEDTIME IS NOT NULL;

   V_Step := V_Step + 1;                                                 -- 0;
   V_Action := PKG_CONSTANT.CONS_ACTION_INSERT;
   V_MainTable := 'MST_SUPPLIERCALENDAR';
   --记录MST_SUPPLIERCALENDAR导入日志
   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here
                       V_ProName,                    --Put procedure name here
                       V_Step,                      --Step seqence No. if have
                       V_Action,                      --Action 'I','D','U','T'
                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'
                       'Success: ' || SQL%ROWCOUNT || ' rows imported.', --Any generic information here
                       V_MainTable);              --Major table name processed
   COMMIT;
end if;
--end remark 2014.10.14 动态安全库存实现方式错误，修改为写入MST_ITEMSITELTPARM

   --工艺路线及虚拟车间不全:当MST_OPRESOURCE.WORKCENTERNAME中没有对应到数据做处理
   V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_INSERT;
   V_MainTable := 'MST_OPRESOURCE';
   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here
                       V_ProName,                    --Put procedure name here
                       V_Step,                      --Step seqence No. if have
                       V_Action,                      --Action 'I','D','U','T'
                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'
                       'Start: Prepare master data.', --Any generic information here
                       V_MainTable);

   MERGE INTO ABPPMGR.MST_WORKCENTERMASTER M
        USING DUAL IM
           ON (M.WORKCENTERNAME = V_WorkcenterName)
   WHEN MATCHED
   THEN
      UPDATE SET
         M.SYS_ENT_STATE = 'ACTIVE',
         M.SYS_LAST_MODIFIED_BY = V_ProName,
         M.SYS_LAST_MODIFIED_DATE = V_CurrentTime
   WHEN NOT MATCHED
   THEN
      INSERT     (SCENARIO_ID,
                  ENTERPRISE,
                  SITEID,
                  ENGINE_ID,
                  LOCATIONID,
                  WORKCENTERNAME,SYS_CREATED_BY)
          VALUES (V_ScenarioID,
                  V_Enterprise,
                  V_SiteID,
                  V_Engine,
                  V_LocationID,
                  V_WorkcenterName,
                  V_ProName);

   MERGE INTO ABPPMGR.MST_RESOURCEMASTER M
        USING DUAL IM
           ON (M.RESOURCENAME = V_WorkcenterName)
   WHEN MATCHED
   THEN
      UPDATE SET
         M.SYS_ENT_STATE = 'ACTIVE',
         M.SYS_LAST_MODIFIED_BY = V_ProName,
         M.SYS_LAST_MODIFIED_DATE = V_CurrentTime
   WHEN NOT MATCHED
   THEN
      INSERT     (SCENARIO_ID,
                  ENTERPRISE,
                  SITEID,
                  ENGINE_ID,
                  RESOURCENAME,
                  SYS_CREATED_BY)
          VALUES (V_ScenarioID,
                  V_Enterprise,
                  V_SiteID,
                  V_Engine,
                  V_WorkcenterName,
                  V_ProName);

   MERGE INTO ABPPMGR.MST_WORKCENTERDETAIL M
        USING DUAL IM
           ON (    M.SCENARIO_ID = V_ScenarioID
               AND M.ENTERPRISE = V_Enterprise
               AND M.SITEID = V_SiteID
               AND M.RESOURCENAME = V_WorkcenterName
               AND M.WORKCENTERNAME = V_WorkcenterName)
   WHEN MATCHED
   THEN
      UPDATE SET
         M.SYS_ENT_STATE = 'ACTIVE',
         M.SYS_LAST_MODIFIED_BY = V_ProName,
         M.SYS_LAST_MODIFIED_DATE = V_CurrentTime
   WHEN NOT MATCHED
   THEN
      INSERT     (SCENARIO_ID,
                  ENTERPRISE,
                  SITEID,
                  ENGINE_ID,
                  RESOURCENAME,
                  WORKCENTERNAME,
                  AGGWORKCENTERNAME,
                  AGGRESOURCENAME,
                  AGGSITEID,
                  LOCATIONID,
                  SYS_CREATED_BY)
          VALUES (V_ScenarioID,
                  V_Enterprise,
                  V_SiteID,
                  V_Engine,
                  V_WorkcenterName,
                  V_WorkcenterName,
                  V_WorkcenterName,
                  V_WorkcenterName,
                  V_SiteID,
                  V_LocationID,
                  V_ProName);

   COMMIT;

    DELETE FROM ABPPMGR.MST_OPRESOURCE WHERE SYS_CREATED_BY=V_PRONAME;
    COMMIT;
   INSERT INTO ABPPMGR.MST_OPRESOURCE (SCENARIO_ID,
                                       ENTERPRISE,
                                       SITEID,
                                       ENGINE_ID,
                                       ROUTINGID,
                                       OPERATIONSEQ,
                                       --RESOURCEGROUPNAME,
                                       --RESOURCECONFIGURATIONID,
                                       RESOURCENAME,
                                       WORKCENTERNAME,
                                       SYS_CREATED_BY)
      SELECT V_ScenarioID,
             V_Enterprise,
             V_SiteID,
             V_Engine,
             M.ROUTINGID,
             M.OPERATIONSEQ,
             --V_ResourceGroup,
             --ITEM_ID,
             V_WorkcenterName,
             V_WorkcenterName,
             V_ProName SYS_CREATED_BY
        FROM ABPPMGR.MST_ROUTINGOPERATION M 
       WHERE M.SYS_ENT_STATE='ACTIVE' AND NOT EXISTS
                    (SELECT NULL
                       FROM ABPPMGR.MST_OPRESOURCE M2
                      WHERE M.ROUTINGID = M2.ROUTINGID
                            AND M.OPERATIONSEQ = M2.OPERATIONSEQ
                            AND M2.SYS_ENT_STATE='ACTIVE');

   V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_INSERT;
   V_MainTable := 'MST_OPRESOURCE';
   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here
                       V_ProName,                    --Put procedure name here
                       V_Step,                      --Step seqence No. if have
                       V_Action,                      --Action 'I','D','U','T'
                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'
                       'Success: ' || SQL%ROWCOUNT || ' rows imported.', --Any generic information here
                       V_MainTable);
   COMMIT;
     --增加订单需求属性
    UPDATE ABPPMGR.SALESORDERLINE SOL SET SOL.PROPERTY=SOL.SALESORDERID|| '_' ||LTRIM(SOL.SOLINENUM,'0')
   WHERE NOT EXISTS(SELECT NULL FROM IN_SOURCING ISRC WHERE SOL.CUSTOMERID  = ISRC.CUSTOMER_ID
     AND SOL.ITEM = ISRC.FINISH_ITEM_ID);
    COMMIT;
   --DELETE FROM  ABPPMGR.SALESORDERLINE_P WHERE SYS_CREATED_BY LIKE '%' || V_PRONAME || '%';
  -- COMMIT;
   
   --ADD BY Landor ON 20180522 删除SYS_CREATED_BY='AnDan'
   --DELETE FROM  ABPPMGR.SALESORDERLINE_P ;--WHERE SYS_CREATED_BY='AnDan';
   EXECUTE IMMEDIATE 'TRUNCATE TABLE ABPPMGR.SALESORDERLINE_P';
   COMMIT;
   
   
     INSERT INTO ABPPMGR.SALESORDERLINE_P
       ( ENTERPRISE
       , ENGINE_ID
       , SALESORDERID
       , SOLINENUM
       , ATTRIBUTECLASS
       , ATTRIBUTE
       , RELATIONSHIP
       , VALUE
       , SYS_CREATED_BY
       )
  SELECT sol.ENTERPRISE
       , sol.ENGINE_ID
       , sol.SALESORDERID
       , sol.SOLINENUM
       , sol.PROPERTY  ATTRIBUTECLASS
       , 'FACTORY' -- ATTRIBUTE
       , 'EQ' --RELATIONSHIP
       , sol.LOCATIONID -- VALUE
       , V_ProName||'_'||V_Step
    FROM abppmgr.SALESORDERLINE sol
   WHERE not exists(select null from IN_SOURCING isrc where sol.CUSTOMERID  = isrc.CUSTOMER_ID
     AND sol.ITEM = isrc.FINISH_ITEM_ID )
  ; 
  
  -- start add
    ----ADD BY Landor ON 20180522 按单1.把原来的ATTRIBUTECLASS+:+ SALESORDERLINE_P.VALUE
    UPDATE ABPPMGR.SALESORDERLINE_P  SET ATTRIBUTECLASS=ATTRIBUTECLASS || ':'|| VALUE;
     commit;
     --add by Landor on 20180522 按单业务处理
     ----a.针对MTL订单处理
    INSERT INTO ABPPMGR.SALESORDERLINE_P
           ( ENTERPRISE
           , ENGINE_ID
           , SALESORDERID
           , SOLINENUM
           , ATTRIBUTECLASS
           , ATTRIBUTE
           , RELATIONSHIP
           , VALUE
           , SYS_CREATED_BY
           )
       --新增ATTRIBUTECLASS=ATTRIBUTECLASS:0 value=0
    SELECT   ENTERPRISE
           , ENGINE_ID
           , SALESORDERID
           , SOLINENUM
           , SUBSTR(ATTRIBUTECLASS,1,INSTR(ATTRIBUTECLASS,':')-1)||':0' ATTRIBUTECLASS 
           , 'ORDER_PLANNING'    ATTRIBUTE
           , RELATIONSHIP
           , '0'  VALUE
           , 'AnDan1'
       FROM ABPPMGR.SALESORDERLINE_P AP
        WHERE AP.SALESORDERID LIKE '%MTL%'
    UNION
    --新增ATTRIBUTECLASS=ATTRIBUTECLASS:100 value=100  alter by landor on 20180828
        SELECT   ENTERPRISE
           , ENGINE_ID
           , AP.SALESORDERID
           , AP.SOLINENUM
           , SUBSTR(AP.ATTRIBUTECLASS,1,INSTR(AP.ATTRIBUTECLASS,':')-1)||':100' ATTRIBUTECLASS 
           , 'ORDER_PLANNING'    ATTRIBUTE
           , AP.RELATIONSHIP
           , '100'  VALUE
           , 'AnDan2' 
       FROM ABPPMGR.SALESORDERLINE_P AP
       JOIN IN_SFCHEADER S ON S.MO_ID=SUBSTR(AP.SALESORDERID,5,12)
        WHERE AP.SALESORDERID LIKE '%MTL%' 
        AND EXISTS(SELECT NULL FROM IN_ORDER_PLANNING C WHERE C.SO_ID=S.SO_ID  AND  S.SO_LINE_ID= SUBSTR(C.SO_LINE_ID,1,INSTR(C.SO_LINE_ID,'_')-1) ) 
        AND EXISTS(SELECT NULL FROM IN_ORDER_PLANNING B WHERE B.SEMI_ITEM_ID=AP.SOLINENUM)
    UNION
    --如果是按单生产产生的MTL工单新增一笔ATTRIBUTECLASS=ATTRIBUTECLASS:订单号订单行项 value=订单号订单行项
    --取消使用IN_SEMI_MO_LOCKED，改为IN_MO和IN"_SFCHEADER alter by landor on 20180619
 --取消使用IN_SEMI_MO_LOCKED，改为IN_MO和IN"_SFCHEADER alter by landor on 20180619
    SELECT   ENTERPRISE
           , ENGINE_ID
           , SALESORDERID
           , SOLINENUM
           , SUBSTR(ATTRIBUTECLASS,1,INSTR(ATTRIBUTECLASS,':')-1)||':'||IL.SO_ID||IL.SO_LINE_ID ATTRIBUTECLASS 
           , 'ORDER_PLANNING'    ATTRIBUTE
           , RELATIONSHIP
           , IL.SO_ID||IL.SO_LINE_ID  VALUE
           , 'AnDan33'
       FROM ABPPMGR.SALESORDERLINE_P AP
       JOIN IN_SFCHEADER IL ON SUBSTR(AP.SALESORDERID,5,12) =IL.MO_ID
        WHERE  EXISTS(SELECT NULL FROM IN_ORDER_PLANNING C WHERE C.SO_ID=IL.SO_ID  AND  IL.SO_LINE_ID= SUBSTR(C.SO_LINE_ID,1,INSTR(C.SO_LINE_ID,'_')-1) ) --add by landor on 20180828
        AND EXISTS(SELECT NULL FROM IN_ORDER_PLANNING B WHERE B.SEMI_ITEM_ID=AP.SOLINENUM)
        AND  AP.SALESORDERID LIKE '%MTL%' AND IL.SO_ID IS NOT NULL
     UNION
     SELECT   ENTERPRISE
           , ENGINE_ID
           , SALESORDERID
           , SOLINENUM
           , SUBSTR(ATTRIBUTECLASS,1,INSTR(ATTRIBUTECLASS,':')-1)||':'||IL.SO_ID||IL.SO_LINE_ID ATTRIBUTECLASS 
           , 'ORDER_PLANNING'    ATTRIBUTE
           , RELATIONSHIP
           , IL.SO_ID||IL.SO_LINE_ID  VALUE
           , 'AnDan4'
       FROM ABPPMGR.SALESORDERLINE_P AP
       JOIN IN_MO IL
       ON SUBSTR(AP.SALESORDERID,INSTR(AP.SALESORDERID,'_')+1,INSTR(AP.SALESORDERID,'_',1,2)-INSTR(AP.SALESORDERID,'_')-1) 
              =IL.MO_ID
        WHERE AP.SALESORDERID LIKE '%MTL%'   AND IL.SO_ID IS NOT NULL
        ; 
    /*取消使用IN_SEMI_MO_LOCKED，改为IN_MO和IN"_SFCHEADER alter by landor on 20180619
    SELECT   ENTERPRISE 
           , ENGINE_ID
           , SALESORDERID
           , SOLINENUM
           , SUBSTR(ATTRIBUTECLASS,1,INSTR(ATTRIBUTECLASS,':')-1)||':'||IL.PEGGED_SO_ID||IL.PEGGED_SO_LINE_ID ATTRIBUTECLASS 
           , 'ORDER_PLANNING'    ATTRIBUTE
           , RELATIONSHIP
           , IL.PEGGED_SO_ID||IL.PEGGED_SO_LINE_ID  VALUE
           , 'AnDan'
       FROM ABPPMGR.SALESORDERLINE_P AP
       JOIN IN_SEMI_MO_LOCKED IL
       ON SUBSTR(AP.SALESORDERID,INSTR(AP.SALESORDERID,'_')+1,INSTR(AP.SALESORDERID,'_',1,2)-INSTR(AP.SALESORDERID,'_')-1)
              =IL.PEGGED_MO_ID
        WHERE AP.SALESORDERID LIKE '%MTL%';   */
     COMMIT;
     

     ---- 再把原来ATTRIBUTECLASS=ATTRIBUTECLASS:100改为=ATTRIBUTECLASS:200以及value=100改为200
     /*UPDATE ABPPMGR.SALESORDERLINE_P AP SET ATTRIBUTECLASS=SUBSTR(ATTRIBUTECLASS,1,INSTR(ATTRIBUTECLASS,':')-1)||':200',
                                                    VALUE='200'
         WHERE EXISTS(SELECT NULL FROM IN_MO IL
          WHERE SUBSTR(AP.SALESORDERID,INSTR(AP.SALESORDERID,'_')+1, INSTR(AP.SALESORDERID,'_',1,2) -INSTR(AP.SALESORDERID,'_')-1)=IL.MO_ID)
          AND AP.SALESORDERID LIKE '%MTL%'
          AND SUBSTR(AP.ATTRIBUTECLASS,-4)=':100';   
     COMMIT;   */
     
     UPDATE ABPPMGR.SALESORDERLINE_P AP SET ATTRIBUTECLASS=SUBSTR(ATTRIBUTECLASS,1,INSTR(ATTRIBUTECLASS,':')-1)||':200',
                                                    VALUE='200'
         WHERE EXISTS(SELECT NULL FROM IN_SFCHEADER IL
          WHERE SUBSTR(AP.SALESORDERID,INSTR(AP.SALESORDERID,'_')+1, INSTR(AP.SALESORDERID,'_',1,2) -INSTR(AP.SALESORDERID,'_')-1)=IL.MO_ID)
          AND AP.SALESORDERID LIKE '%MTL%'
          AND SUBSTR(AP.ATTRIBUTECLASS,-4)=':100';   
     COMMIT;    
     
     --按单生产中带SO号和行号的，同时带了：0的，需要将0删除掉，避免无法占用库存add by landor on 20180619
    DELETE FROM ABPPMGR.SALESORDERLINE_P B 
    WHERE ATTRIBUTE='ORDER_PLANNING' AND B.ATTRIBUTECLASS LIKE 'MTL%' AND LENGTH(ATTRIBUTECLASS)=36  --长度为36代表:0
    AND EXISTS(SELECT NULL FROM ABPPMGR.SALESORDERLINE_P A 
                WHERE SUBSTR(A.ATTRIBUTE,1,34)=SUBSTR(B.ATTRIBUTE,1,34) 
                AND A.ATTRIBUTE='ORDER_PLANNING'  AND A.ATTRIBUTECLASS LIKE 'MTL%'  AND LENGTH(A.ATTRIBUTECLASS)=48); --长度为48代表带上SO号行号
    COMMIT;

     /*取消使用IN_SEMI_MO_LOCKED，改为IN_MO和IN"_SFCHEADER alter by landor on 20180619 
     UPDATE ABPPMGR.SALESORDERLINE_P AP SET ATTRIBUTECLASS=SUBSTR(ATTRIBUTECLASS,1,INSTR(ATTRIBUTECLASS,':')-1)||':200',
                                                    VALUE='200'
         WHERE EXISTS(SELECT NULL FROM IN_SEMI_MO_LOCKED IL
          WHERE SUBSTR(AP.SALESORDERID,INSTR(AP.SALESORDERID,'_')+1,
                              INSTR(AP.SALESORDERID,'_',1,2)
                               -INSTR(AP.SALESORDERID,'_')-1)=IL.PEGGED_MO_ID)
          AND AP.SALESORDERID LIKE '%MTL%'
          AND SUBSTR(AP.ATTRIBUTECLASS,-4)=':100';   */
          
      
      
    --b.    非按单生产订单 不存在与INORDERPLANNING表代表非按单生产
     INSERT INTO ABPPMGR.SALESORDERLINE_P
           ( ENTERPRISE
           , ENGINE_ID
           , SALESORDERID
           , SOLINENUM
           , ATTRIBUTECLASS
           , ATTRIBUTE
           , RELATIONSHIP
           , VALUE
           , SYS_CREATED_BY
           )
    ----当INORDERPLANNING和SALESORDERLINE_P表的soid,solineid不相同 
      -- let ATTRIBUTECLASS=ATTRIBUTECLASS:0 value=0以及ATTRIBUTECLASS=ATTRIBUTECLASS:100 value=100
    SELECT DISTINCT ENTERPRISE
          ,ENGINE_ID 
          ,SALESORDERID
          ,SOLINENUM
          ,SUBSTR(AE.ATTRIBUTECLASS,1,INSTR(AE.ATTRIBUTECLASS,':')-1)||':0' ATTRIBUTECLASS
          ,'ORDER_PLANNING'        ATTRIBUTE
          ,RELATIONSHIP
          ,'0'  VALUE
          ,'AnDan5'
     FROM ABPPMGR.SALESORDERLINE_P AE 
        WHERE NOT EXISTS(SELECT NULL FROM  IN_ORDER_PLANNING IP
         WHERE IP.SO_ID      = AE.SALESORDERID
           AND IP.SO_LINE_ID = AE.SOLINENUM)
           AND AE.ATTRIBUTECLASS NOT LIKE '%MTL%'
    UNION
    ----当INORDERPLANNING和SALESORDERLINE_P表的soid,solineid不相同
      -- let ATTRIBUTECLASS=ATTRIBUTECLASS:0 value=0以及ATTRIBUTECLASS=ATTRIBUTECLASS:100 value=100
    SELECT DISTINCT ENTERPRISE
          ,ENGINE_ID 
          ,SALESORDERID
          ,SOLINENUM
          ,SUBSTR(AE.ATTRIBUTECLASS,1,INSTR(AE.ATTRIBUTECLASS,':')-1)||':100' ATTRIBUTECLASS
          ,'ORDER_PLANNING'        ATTRIBUTE
          ,RELATIONSHIP
          ,'100'  VALUE
          ,'AnDan6' 
     FROM ABPPMGR.SALESORDERLINE_P AE 
        WHERE NOT EXISTS(SELECT NULL FROM  IN_ORDER_PLANNING IP
         WHERE IP.SO_ID      = AE.SALESORDERID
           AND IP.SO_LINE_ID = AE.SOLINENUM)
           AND AE.ATTRIBUTECLASS NOT LIKE '%MTL%';
     COMMIT;  
      
    --d.    按单生产订单:从IN_ORDER_PLANNING找到按单生产订单，然后新增数据到SALESORDERLINE_P   1代表真验货 0为假验货
     
     INSERT INTO ABPPMGR.SALESORDERLINE_P
           ( ENTERPRISE
           , ENGINE_ID
           , SALESORDERID
           , SOLINENUM
           , ATTRIBUTECLASS
           , ATTRIBUTE
           , RELATIONSHIP
           , VALUE
           , SYS_CREATED_BY
           )
    ----新增ATTRIBUTECLASS=ATTRIBUTECLASS:订单号订单行项 当order_Planning=0,1 value=订单号订单行项
    SELECT DISTINCT ENTERPRISE
          ,ENGINE_ID 
          ,SALESORDERID
          ,SOLINENUM
          ,SUBSTR(AE.ATTRIBUTECLASS,1,INSTR(AE.ATTRIBUTECLASS,':')-1)||':'||IP.SO_ID||SUBSTR(IP.SO_LINE_ID,1,INSTR(IP.SO_LINE_ID,'_')-1) ATTRIBUTECLASS
          ,'ORDER_PLANNING'        ATTRIBUTE
          ,RELATIONSHIP
          , IP.SO_ID||SUBSTR(IP.SO_LINE_ID,1,INSTR(IP.SO_LINE_ID,'_')-1)VALUE
          ,'AnDan7'
     FROM ABPPMGR.SALESORDERLINE_P AE 
       JOIN IN_ORDER_PLANNING IP
         ON IP.SO_ID      = AE.SALESORDERID
         AND IP.SO_LINE_ID = AE.SOLINENUM
         AND AE.ATTRIBUTECLASS NOT LIKE '%MTL%'
         AND IP.ORDER_PLANNING IN(0,1)
    UNION
    ----新增ATTRIBUTECLASS=ATTRIBUTECLASS:200 当order_Planning=1 value=订单号订单行项 
    SELECT DISTINCT ENTERPRISE
          ,ENGINE_ID 
          ,SALESORDERID
          ,SOLINENUM
          ,SUBSTR(AE.ATTRIBUTECLASS,1,INSTR(AE.ATTRIBUTECLASS,':')-1)||':200' ATTRIBUTECLASS
          ,'ORDER_PLANNING'        ATTRIBUTE
          ,RELATIONSHIP
          ,'200'VALUE
          ,'AnDan8'
     FROM ABPPMGR.SALESORDERLINE_P AE 
       JOIN IN_ORDER_PLANNING IP
         ON IP.SO_ID      = AE.SALESORDERID
         AND IP.SO_LINE_ID = AE.SOLINENUM
         AND AE.ATTRIBUTECLASS NOT LIKE '%MTL%'
         AND IP.ORDER_PLANNING IN(1,0)
     UNION
    ----新增ATTRIBUTECLASS=ATTRIBUTECLASS:0 当order_Planning=0 value=0
    SELECT DISTINCT ENTERPRISE
          ,ENGINE_ID 
          ,SALESORDERID
          ,SOLINENUM
          ,SUBSTR(AE.ATTRIBUTECLASS,1,INSTR(AE.ATTRIBUTECLASS,':')-1)||':0' ATTRIBUTECLASS
          ,'ORDER_PLANNING'        ATTRIBUTE
          ,RELATIONSHIP
          ,'0'VALUE
          ,'AnDan9'
     FROM ABPPMGR.SALESORDERLINE_P AE 
       JOIN IN_ORDER_PLANNING IP
         ON IP.SO_ID      = AE.SALESORDERID
         AND IP.SO_LINE_ID = AE.SOLINENUM
         AND AE.ATTRIBUTECLASS NOT LIKE '%MTL%'
         AND IP.ORDER_PLANNING=0;     
     COMMIT;
    --高品质客户处理，多群组增新增相应的条数 ATTRIBUTECLASS=ATTRIBUTECLASS:10000-ID VALUE=10000-ID
    INSERT INTO ABPPMGR.SALESORDERLINE_P
           ( ENTERPRISE
           , ENGINE_ID
           , SALESORDERID
           , SOLINENUM
           , ATTRIBUTECLASS
           , ATTRIBUTE
           , RELATIONSHIP
           , VALUE
           , SYS_CREATED_BY
           )
    SELECT DISTINCT  ENTERPRISE
                    ,ENGINE_ID 
                    ,SALESORDERID
                    ,SOLINENUM
                    ,SUBSTR(AE.ATTRIBUTECLASS,1,INSTR(AE.ATTRIBUTECLASS,':')-1)||':'||TO_CHAR(10000-IC.ID) ATTRIBUTECLASS --优先级
                    ,'ORDER_PLANNING'        ATTRIBUTE
                    ,RELATIONSHIP
                    ,10000-IC.ID VALUE
                    ,'AnDan10'
                 FROM IN_ORDER_PLANNING IP
                  JOIN IN_STOCK_CUSTOMER IC --客户对应的群组数据表
                  ON IP.CUSTOMER_ID=IC.CUSTOMER_ID
                  JOIN ABPPMGR.SALESORDERLINE_P AE
                   ON IP.SO_ID      = AE.SALESORDERID
                  AND IP.SO_LINE_ID = AE.SOLINENUM;
     COMMIT;  
      
    --endt add

    --解决按单真验货吃非限制库存的问题，清理需求表的属性值 
    --按单真验货的ORDER_PLANNING的需求的属性值不能为0  add by landor on 20180729
    DELETE FROM ABPPMGR.SALESORDERLINE_P A  
     WHERE  ATTRIBUTE='ORDER_PLANNING' AND VALUE='0' AND 
            EXISTS (SELECT * FROM IN_ORDER_PLANNING B 
                            WHERE A.SALESORDERID=B.SO_ID AND B.SO_LINE_ID=A.SOLINENUM --按单
                            AND B.ORDER_PLANNING='1'); --真验货
    COMMIT;   
    

      V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_INSERT;
   V_MainTable := 'SALESORDERLINE_P';
   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here
                       V_ProName,                    --Put procedure name here
                       V_Step,                      --Step seqence No. if have
                       V_Action,                      --Action 'I','D','U','T'
                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'
                       'Success: ' || SQL%ROWCOUNT || ' rows imported.', --Any generic information here
                       V_MainTable);
   COMMIT;
   --ALTER BY HUANGYANGXIONG ON 2014/12/02  注释掉VMI物料的采购周期为0的设定
   /*
   UPDATE ABPPMGR.MST_APPRSUPITEM M SET M.AVGLEADTIME=0
    WHERE EXISTS (SELECT NULL FROM IN_SUPPLIER_ITEM IM
    WHERE  M.ITEM=IM.ITEM_ID AND M.SUPPLIERID=IM.SUPPLIER_ID and UDF_ITEM_TYPE_ID='1');
    COMMIT;
    */
   DELETE FROM ABPPMGR.MST_APPRSUPITEM_P;--删除供应商与物料对应关系属性数据
   COMMIT;
   UPDATE ABPPMGR.MST_APPRSUPITEM M SET M.PROPERTY=M.ITEM|| '_'|| SUBSTR(M.SUPPLIERID,1,4);--更新属性数据
   COMMIT;

    INSERT INTO ABPPMGR.MST_APPRSUPITEM_P (ENGINE_ID,
                                           SUPPLIERID,
                                           ITEM,
                                           ITEMGROUP,
                                           ATTRIBUTECLASS,
                                           ENTERPRISE,
                                           PURORGANIZATIONID,
                                           SITEID,
                                           ITEMENTERPRISE,
                                           EFFSTARTDATE,
                                           SCENARIO_ID,
                                           ATTRIBUTE,
                                           RELATIONSHIP,
                                           VALUE)
       SELECT ENGINE_ID,
              SUPPLIERID,
              ITEM,
              ITEMGROUP,
              PROPERTY ATTRIBUTECLASS,
              ENTERPRISE,
              PURORGANIZATIONID,
              SITEID,
              ITEMENTERPRISE,
              EFFSTARTDATE,
              SCENARIO_ID,
              'FACTORY' ATTRIBUTE,
              'EQ' RELATIONSHIP,
              SUBSTR(SUPPLIERID,1,4) VALUE 
         FROM ABPPMGR.MST_APPRSUPITEM M 
         WHERE NOT EXISTS(SELECT NULL FROM IN_ITEM_SITE IM
          WHERE IM.ITEM_ID=M.ITEM AND IM.SITE_ID=SUBSTR(M.SUPPLIERID,1,4)
          AND  TRIM(IM.PROC_TYPE) || TRIM(IM.SP_PROC_TYPE) IN ('E70'));
    COMMIT;

    EXECUTE IMMEDIATE 'truncate table TEMP_ITEM_UOM';
    INSERT INTO TEMP_ITEM_UOM(SITE_ID,parent_id, ITEM_ID, QTY_UOM,LOC_ID)
      SELECT SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4) SITE_ID,BH.product_id,BD2.ITEM_ID
      ,MAX(BD2.USAGE_QTY_UOM) QTY_UOM,MAX(BD2.LOC_ID) LOC_ID
    FROM IN_BOM_DETAILS BD, IN_BOM_HEADER BH,IN_BOM_DETAILS BD2, IN_BOM_HEADER BH2
     where BH.BOM_ID=BD.bom_id and BH.MO_BOM_MARK='0'  and BD.IS_PHANTOM='1'
     and BH2.BOM_ID=BD2.bom_id and BH2.MO_BOM_MARK='0' and BD.item_id=BH2.PRODUCT_ID
     and SUBSTR(BH2.BOM_ID,INSTR(BH2.BOM_ID,'_')+1,4) =SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4) 
    GROUP BY  BH.product_id,SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4),BD2.ITEM_ID;
    COMMIT;
        INSERT INTO TEMP_ITEM_UOM(SITE_ID,parent_id, ITEM_ID, QTY_UOM,LOC_ID)
      SELECT SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4) SITE_ID,BH.product_id,BD2.FOLLOW_UP_MATERIAL
      ,MAX(BD2.USAGE_QTY_UOM) QTY_UOM,MAX(BD2.LOC_ID) LOC_ID
    FROM IN_BOM_DETAILS BD, IN_BOM_HEADER BH,IN_BOM_DETAILS BD2, IN_BOM_HEADER BH2
     where BH.BOM_ID=BD.bom_id and BH.MO_BOM_MARK='0'  and BD.IS_PHANTOM='1'
     and BH2.BOM_ID=BD2.bom_id and BH2.MO_BOM_MARK='0' and BD.item_id=BH2.PRODUCT_ID
     and SUBSTR(BH2.BOM_ID,INSTR(BH2.BOM_ID,'_')+1,4) =SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4) 
         and not exists (select null from TEMP_ITEM_UOM TU where TU.SITE_ID=SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4) 
    and tu.parent_id=BH.product_id and  tu.ITEM_ID=BD2.FOLLOW_UP_MATERIAL)
     and exists( select null from in_item TM where Tm.item_id=BD2.FOLLOW_UP_MATERIAL)
    GROUP BY  BH.product_id,SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4),BD2.FOLLOW_UP_MATERIAL;
    COMMIT;
    INSERT INTO TEMP_ITEM_UOM(SITE_ID,parent_id, ITEM_ID, QTY_UOM,LOC_ID)
    SELECT SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4) SITE_ID,BH.product_id,BD.FOLLOW_UP_MATERIAL
    ,MAX(BD.USAGE_QTY_UOM) QTY_UOM,MAX(BD.LOC_ID) LOC_ID
    FROM IN_BOM_DETAILS BD, IN_BOM_HEADER BH where BH.BOM_ID=BD.bom_id and BH.MO_BOM_MARK='0' 
    and not exists (select null from TEMP_ITEM_UOM TU where TU.SITE_ID=SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4) 
    and tu.parent_id=BH.product_id and  tu.ITEM_ID=BD.FOLLOW_UP_MATERIAL)
    and exists( select null from in_item TM where Tm.item_id=BD.FOLLOW_UP_MATERIAL)
    GROUP BY  BH.product_id,SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4),BD.FOLLOW_UP_MATERIAL;
        COMMIT;
    INSERT INTO TEMP_ITEM_UOM(SITE_ID,parent_id, ITEM_ID, QTY_UOM,LOC_ID)
    SELECT SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4) SITE_ID,BH.product_id,BD.ITEM_ID
    ,MAX(BD.USAGE_QTY_UOM) QTY_UOM,MAX(BD.LOC_ID) LOC_ID
    FROM IN_BOM_DETAILS BD, IN_BOM_HEADER BH where BH.BOM_ID=BD.bom_id and BH.MO_BOM_MARK='0' 
    and not exists (select null from TEMP_ITEM_UOM TU where TU.SITE_ID=SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4) 
    and tu.parent_id=BH.product_id and  tu.ITEM_ID=BD.item_id)
    GROUP BY  BH.product_id,SUBSTR(BH.BOM_ID,INSTR(BH.BOM_ID,'_')+1,4),BD.ITEM_ID;
        COMMIT;
        
   
    --MTL加工辅助件报short问题修复 add by landor on 20180809
    DELETE FROM  ABPPMGR.SALESORDERLINE_P A 
    WHERE A.ATTRIBUTECLASS LIKE 'MTL%' AND A.ATTRIBUTE='ORDER_PLANNING' 
        AND EXISTS (SELECT NULL FROM IN_ITEM B WHERE B.ITEM_ID=A.SOLINENUM
        AND FAMILY_ID IN ('102018000','402068001','402071000','402068004','402068005','402068006','402068010','402068002','502003004','502502002')) ;
    COMMIT;    
   
   

   
   
   /*    取消刷新物化视图 add by Landor on 20161222 
        
   --refresh ODS data to Materialized View
   --add by huangyangxiong on 20150430  update service lock to 'NO' status
   UPDATE ABPPMGR.SERVICE_LOCK SET IS_LOCKED = 'NO';
   
   COMMIT;
   
ABPPMGR.UPDATE_SNAPSHOT_COMPLETE();
   COMMIT;
   
   */
   
      V_Step := V_Step + 1;
   V_Action := PKG_CONSTANT.CONS_ACTION_INSERT;
   V_MainTable := 'ALL';
   PKG_UTIL.CREATELOG (V_PKGName,                      --Put package name here
                       V_ProName,                    --Put procedure name here
                       V_Step,                      --Step seqence No. if have
                       V_Action,                      --Action 'I','D','U','T'
                       PKG_CONSTANT.CONS_MSG_INFO, --Message tppe, 'SUCCESS' or 'EXCEPTION'
                       'execute procedure UPDATE_SNAPSHOT_COMPLETE compeleted.', --Any generic information here
                       V_MainTable);
EXCEPTION
   --总程序异常处理部分
   WHEN OTHERS
   THEN
      BEGIN
         ROLLBACK;
         EXITCODE := 1;
         e_ErrMessage :=
            SUBSTR (SQLERRM, 1, 1000)
            || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE, 1, 2000);
         PKG_UTIL.CREATELOG (V_PKGName,
                             V_ProName,
                             V_Step,
                             V_Action,
                             PKG_CONSTANT.CONS_MSG_EXCEPTION,
                             e_ErrMessage,
                             V_MainTable);
      END;
END;
/

 十七、处理二分厂加工辅助件、加工后  报SHORT问题

 十八、 ----解决非限制MO分配给非验货的MTL后，不再往下分配。  10月9号开发，11号验证OK，切换上线

FP_POST_PROCESS

--解决非限制MO分配给非验货的MTL后，不再往下分配
---MTL成品工单下层的半成品为非验货的 FP_POST_PROCESS  add by landor on 20181009
INSERT INTO ABPPMGR.SALESORDERLINE_P
           ( ENTERPRISE
           , ENGINE_ID
           , SALESORDERID
           , SOLINENUM
           , ATTRIBUTECLASS
           , ATTRIBUTE
           , RELATIONSHIP
           , VALUE
           , SYS_CREATED_BY
           )
SELECT ENTERPRISE  --SEINE
       ,ENGINE_ID --2
       ,A.SALESORDERID
       ,A.SOLINENUM
       ,PROPERTY || ':0' ATTRIBUTECLASS
       ,'ORDER_PLANNING' ATTRIBUTE
       ,'EQ' RELATIONSHIP
       ,'0' VALUE
       ,'AnDan30' SYS_CREATED_BY
FROM ABPPMGR.SALESORDERLINE A WHERE A.SALESORDERID LIKE 'MTL_0011%' AND LOCATIONID IN('2022','2023') ---MTL成品工单下层的半成品为非验货的
AND EXISTS (SELECT NULL FROM IN_ITEM B WHERE B.ITEM_ID=A.SOLINENUM AND EXT_FORTEXT1='Z002' )  --半成品
AND (
      NOT EXISTS  ---非验货的 
      (  
      select  IOP.* from IN_ORDER_PLANNING IOP,in_semi_mo_locked ISM  
      WHERE IOP.SO_ID=ISM.PEGGED_SO_ID
      AND SUBSTR(IOP.SO_LINE_ID,1,INSTR(IOP.SO_LINE_ID,'_')-1)=ISM.PEGGED_SO_LINE_ID
      AND ISM.PEGGED_MO_ID=SUBSTR(A.SALESORDERID,INSTR(A.SALESORDERID,'_',1,1)+1,INSTR(A.SALESORDERID,'_',1,2) -(INSTR(A.SALESORDERID,'_')+1) )
      )
      )
UNION ALL
SELECT ENTERPRISE  --SEINE
       ,ENGINE_ID --2
       ,A.SALESORDERID
       ,A.SOLINENUM
       ,PROPERTY || ':100' ATTRIBUTECLASS
       ,'ORDER_PLANNING' ATTRIBUTE
       ,'EQ' RELATIONSHIP
       ,'100' VALUE
       ,'AnDan30' SYS_CREATED_BY
FROM ABPPMGR.SALESORDERLINE A WHERE A.SALESORDERID LIKE 'MTL_0011%' AND LOCATIONID IN('2022','2023') ---MTL成品工单下层的半成品为非验货的
AND EXISTS (SELECT NULL FROM IN_ITEM B WHERE B.ITEM_ID=A.SOLINENUM AND EXT_FORTEXT1='Z002' )  --半成品
AND       NOT EXISTS  ---非验货的 
      (  
      select  IOP.* from IN_ORDER_PLANNING IOP,in_semi_mo_locked ISM  
      WHERE IOP.SO_ID=ISM.PEGGED_SO_ID
      AND SUBSTR(IOP.SO_LINE_ID,1,INSTR(IOP.SO_LINE_ID,'_')-1)=ISM.PEGGED_SO_LINE_ID
      AND ISM.PEGGED_MO_ID=SUBSTR(A.SALESORDERID,INSTR(A.SALESORDERID,'_',1,1)+1,INSTR(A.SALESORDERID,'_',1,2) -(INSTR(A.SALESORDERID,'_')+1) )
      )

FP_MFG

--FP_MFG
 ----解决非限制MO分配给非验货的MTL后，不再往下分配。将非验货的半成品标准BOM属性值更新为0
 UPDATE ABPPMGR.MST_ITEMBOMROUTING_P A
    SET ATTRIBUTECLASS=SUBSTR(ATTRIBUTECLASS,1,INSTR(ATTRIBUTECLASS,':'))||'0'  ,VALUE='0'
 WHERE ATTRIBUTE='ORDER_PLANNING' AND VALUE='200' AND ITEM LIKE '000000102%'
    AND EXISTS (SELECT NULL FROM IN_ITEM B WHERE B.ITEM_ID=A.ITEM AND EXT_FORTEXT1='Z002' )  --半成品
    AND NOT EXISTS (SELECT NULL FROM IN_ORDER_PLANNING P WHERE P.SEMI_ITEM_ID=A.ITEM)  --非验货的
    AND ROUTINGID LIKE '50%' ; --标准BOM 
COMMIT;