PR合并回写

 

select out_pr.PO_RELEASE_DATE,out_pr.supplier_id,out_pr.site_id,sap_eord.ekorg,out_pr.proc_group_id,out_pr.item,out_pr.qty,out_pr.qtyuom,substr(loc_id,-4) as LGORT
,'SAPRFC' as ERNAM,out_pr.due_datetime,out_pr.so_id,out_pr.so_lineid,out_pr.sobsl 
from V_OUT_PR out_pr --其实是select * from tp_temp_pr
join sap_eord on out_pr.item = sap_eord.matnr and out_pr.supplier_id = sap_eord.lifnr and out_pr.site_id = sap_eord.werks
where out_pr.item in ('000000103041000298')

 查询

 

SELECT PR.RECOMMENDEDSUPID,PR.ITEM,TRUNC (PR.PORELEASEDATE),TRUNC(PR.PLANNEDDELDATE), PR.QTYPLANNED 
,IT.PR_CONSOLIDATE_DAYS--合并下PR的时间区间即SAP_MARC。DISLS
,IT.PROC_GROUP_ID,IT.PROC_GROUP_DESCR,IT.SITE_ID
,SI.LOCATION_ID,SI.QTY_UOM,IT.UDF_IS_JIT,SUBSTR(PR.RECOMMENDEDSUPID,INSTR(PR.RECOMMENDEDSUPID,'_')+5)
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 (TRUNC (PR.PORELEASEDATE) <=    V_CurrentDate + DECODE (V_DAY, 7, -1, 0)+V_PDAYS)
                  AND (TRUNC (PORELEASEDATE) <=    sysdate + DECODE (6, 7, -1, 0)+14)
                  AND PR.ORDERID LIKE '%PROC%' AND   SUBSTR(PR.RECOMMENDEDSUPID,INSTR(PR.RECOMMENDEDSUPID,'_')+5) NOT IN 
                  ( SELECT DISTINCT SUPPLIER_ID FROM SAP_SUPPLIER_MINQTY )
                  AND PR.item in ('000000103041000298','000000103011000004','000000103012000008' ) ;
                  
                  SELECT ITEM_ID,SITE_ID,PR_CONSOLIDATE_DAYS FROM IN_ITEM_SITE 
                    WHERE ITEM_ID in ('000000103041000298','000000103011000004','000000103012000008' ) ;
 SELECT MATNR,WERKS,DISLS FROM SAP_MARC WHERE MATNR in ('000000103041000298','000000103011000004','000000103012000008' ) ;
                  SELECT DISTINCT SUPPLIER_ID FROM SAP_SUPPLIER_MINQTY WHERE SUPPLIER_ID='810027';
                  

 

SELECT SUBSTR (CONFIG_VALUE, 1, INSTR (CONFIG_VALUE, ',') - 1) AS WERKS1,
          SUBSTR (CONFIG_VALUE, INSTR (CONFIG_VALUE, ',') + 1, 4) AS WERKS2,
          SUBSTR (CONFIG_VALUE, 11, 4) AS WERKS3,
          SUBSTR (CONFIG_VALUE, 16, 4) AS WERKS4,
          SUBSTR (CONFIG_VALUE, 21, 4) AS WERKS5,
          SUBSTR (CONFIG_VALUE, 26, 4) AS WERKS6,
          SUBSTR (CONFIG_VALUE, 31, 4) AS WERKS7
     /*INTO V_FACTORY1,
          V_FACTORY2,
          V_FACTORY3,
          V_FACTORY4,
          V_FACTORY5,
          V_FACTORY6,
          V_FACTORY7*/
     FROM STG.IN_FP_CONFIG
    WHERE CONFIG_ID = 'FP_FACTORY';

SELECT TRUNC (M.CURRENTDATE)
    -- INTO V_CurrentDate
     FROM ABPPMGR.MST_PLANMASTER M
    WHERE ROWNUM = 1;
    
    select --V_Day :=
      CASE
         WHEN TO_CHAR (sysdate, 'D') = '1' THEN 7
         ELSE TO_CHAR (sysdate, 'D') - 1
      END  from dual;
      
   select --    V_PDAYS :=
      CASE WHEN TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN '20171217' AND '20180126' THEN 30
           ELSE  14
      END from dual;
   select --  V_PDAYS1 :=
      CASE WHEN TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN '20171217' AND '20180126' THEN 30
           ELSE  7
      END from dual;
  --add by zhangguipeng on 20180120 包材类物料组 在20180122-20180228用60天的展望期天数，非该区间用回原来的
 select --   V_PDAYS2 :=
      CASE WHEN TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN '20180122' AND '20180228' THEN 60
           ELSE  14
      END from dual;
  select --   V_PDAYS3 :=
      CASE WHEN TO_CHAR(SYSDATE,'YYYYMMDD') BETWEEN '20180122' AND '20180228' THEN 60
           ELSE  30
      END from dual;