FP_PR2SAP 除包材、半成品以外的半成品下层物料展望期7天更改为40日

 

 --除包材、半成品以外的半成品下层物料展望期7天更改为40日
      INSERT INTO OUT_PR (pr_id,
                          ITEM,
                          SUPPLIER_ID,
                          DUE_DATETIME,
                          QTY,
                          PROC_GROUP_ID,
                          PROC_GROUP_DESCR,
                          SITE_ID,
                          LOC_ID,
                          QTYUOM,
                          PLANNEDDELDATE)
           SELECT MAX (PR.ORDERID),
                  PR.ITEM,
                  SUBSTR (PR.RECOMMENDEDSUPID, 6),
                  TRUNC (PR.PORELEASEDATE),
                  SUM (PR.QTYPLANNED),
                  IT.PROC_GROUP_ID,
                  IT.PROC_GROUP_DESCR,
                  IT.SITE_ID,
                  SI.LOCATION_ID,
                  SI.QTY_UOM,
                  PR.PLANNEDDELDATE
             FROM ABPPMGR.PROCUREMENTPLAN PR,
                  IN_ITEM_SITE IT,
                  IN_SUPPLIER_ITEM SI
            WHERE     PR.ITEM = IT.ITEM_ID
                  AND IT.SITE_ID = SUBSTR (PR.RECOMMENDEDSUPID, 1, 4)
                  AND SI.ITEM_ID = IT.ITEM_ID
                  AND SI.SUPPLIER_ID = PR.RECOMMENDEDSUPID
                  AND SI.UDF_ITEM_TYPE_ID = '0'
                  AND TO_CHAR (TRUNC (PR.PORELEASEDATE), 'YYYY-MM-DD') >=
                         TO_CHAR (V_CurrentDate, 'YYYY-MM-DD')
                  AND (
                       --alter by lxf on 20161213  change 7 days to 40 days
                         TO_CHAR(TRUNC (PR.PORELEASEDATE),'YYYY-MM-DD') <= TO_CHAR(V_CurrentDate + DECODE (V_DAY,1, 40,2,40),'YYYY-MM-DD'))
                  AND PR.ORDERID LIKE '%PROC%'
                  --add chip family_id 物料组4041902与物料编码701*、702*开头的芯片
                  AND (    NOT EXISTS
                                  (SELECT NULL
                                     FROM IN_ITEM II
                                    WHERE     IT.ITEM_ID = II.ITEM_ID
                                          AND II.FAMILY_ID IN
                                                 ('104001000',
                                                  '104001001',
                                                  '104002000',
                                                  '104002001',
                                                  '104003000',
                                                  '104005000',
                                                  '104004000',
                                                  '104006000',
                                                  '104007000',
                                                  '104008000',
                                                  '104009000',
                                                  '104010000',
                                                  '104013000',
                                                  '104014000',
                                                  '403019001',
                                                  '404001000',
                                                  '404002000',
                                                  '404003000',
                                                  '404002001',
                                                  '404004000',
                                                  '404005000',
                                                  '404006000',
                                                  '404008000',
                                                  '404007000',
                                                  '405001000',
                                                  '404009000',
                                                  '103040000'))
                       AND SUBSTR (PR.ITEM, 1, 9) NOT IN
                              ('000000701', '000000702'))
                  AND SUBSTR(PR.RECOMMENDEDSUPID,INSTR(PR.RECOMMENDEDSUPID,'_')+5) NOT IN 
                  ( SELECT DISTINCT SUPPLIER_ID FROM SAP_SUPPLIER_MINQTY )
         GROUP BY PR.ITEM,
                  SUBSTR (PR.RECOMMENDEDSUPID, 6),
                  TRUNC (PR.PORELEASEDATE),
                  IT.PROC_GROUP_ID,
                  IT.PROC_GROUP_DESCR,
                  IT.SITE_ID,
                  SI.LOCATION_ID,
                  SI.QTY_UOM,
                  PR.PLANNEDDELDATE;

 

 DELETE FROM OUT_PR PR
            WHERE        (TO_CHAR (TRUNC (PR.DUE_DATETIME), 'YYYY-MM-DD') >
                             --alter by Lxf on 20161219  change 7 days to50 days
                             TO_CHAR (V_CurrentDate + DECODE (V_DAY, 1, 40,2,40),'YYYY-MM-DD')