性别保密
UID157642
库银 枚
好友
积分60
阅读权限10
在线时间 小时
最后登录1970-1-1
注册时间2013-4-12
|
发表于 2013-4-16 14:12:01
|
显示全部楼层
CREATE or REPLACE VIEW scm_rpt_o_0270_vw /*t4品番表(SCM_MTL_MATERIAL_TBL)
t1库存明细单(SCM_WMS_INV_PROC_DTL_TBL) t2库存处理单(SCM_WMS_INV_PROC_TBL)
t3采购订单(SCM_PO_ORDER_TBL)*/
AS
SELECT t4.RETROFIT_TYPE ,t4.DIST_TYPE,
DATE_FORMAT(CONVERT(CONCAT(t1.ACT_RCPT_DATE,'01'),datetime),'%Y') as YYYY,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_01,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_01,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '01' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_01,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_02,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_02,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '02' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_02,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_03,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_03,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '03' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_03,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_04,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_04,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '04' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_04,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_05,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_05,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '05' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_05,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_06,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_06,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '06' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_06,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_07,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_07,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '07' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_07,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_08,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_08,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '08' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_08,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_09,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_09,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '09' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_09,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_10,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_10,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '10' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_10,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_11,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_11,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '11' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_11,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_WITHIN_12,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END AS
SELL_BEYOND_12,
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END /
CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)<t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
+CASE WHEN mid(t1.ACT_RCPT_DATE,5,2) = '12' THEN (CASE WHEN DATE_SUB(t3.CREATE_DATE,INTERVAL 30 DAY)>=t1.ACT_RCPT_DATE THEN t1.PO_RECONCIL_QTY ELSE NULL END) ELSE NULL END
AS SUPPLY_RATE_12,
t1.ID
FROM SCM_WMS_INV_PROC_DTL_TBL t1 INNER JOIN
SCM_WMS_INV_PROC_TBL t2 ON t1.BILL_ID=t2.ID
INNER JOIN SCM_PO_ORDER_TBL t3 ON t1.DATA_SOURCE_ID=t3.ID
INNER JOIN SCM_MTL_MATERIAL_TBL t4 ON t1.ITEM_ID=t4.ID
WHERE t1.DATA_SOURCE_BILL='PO_ORDER'
|
|