作者：手机用户2502898443 | 来源：互联网 | 2014-07-08 01:11
WMS仓储管理系统实现"收发存日报表"数据查询功能Sql语句--Oracle[sql]www.2cto.comSELECTAA.PRODUCT_CNAME,AA.PRODUCT_ID,AA.PRODUCT_CODE,AA.MODEL,AA.MASTER_UNIT,AA.OTHER_UNIT,AA.P
WMS仓储管理系统实现"收发存日报表"数据查询功能Sql语句--
Oracle
[sql] www.2cto.com
SELECT
AA.PRODUCT_CNAME,
AA.PRODUCT_ID,
AA.PRODUCT_CODE,
AA.MODEL,
AA.MASTER_UNIT,
AA.OTHER_UNIT,
AA.PACKAGE_RATIO,
AA.CUSTOMER,
SUM(QTY_OUT_TODAY) AS QTY_OUT_TODAY,
QTY_TODAY,SUM(QTY_IN_TODAY) AS QTY_IN_TODAY,
SUM(QTY_OUT) AS QTY_OUT,
SUM(QTY_IN) AS QTY_IN
FROM (SELECT CUSTOMER,
PRODUCT_CNAME,PRODUCT_ID,
PRODUCT_CODE,MODEL,
MASTER_UNIT,
OTHER_UNIT,
PACKAGE_RATIO,
CASE WHEN WMS_OUT_CONFIRM_DATE <= TO_DATE(TO_CHAR(SYSDATE,&＃39;yyyy-MM-dd HH24:mi:ss&＃39;) ,&＃39;yyyy-MM-dd HH24:mi:ss&＃39;)
AND WMS_OUT_CONFIRM_DATE >SYSDATE
AND BIZ_SOURCE =&＃39;2&＃39;
THEN MASTER_UNIT_QTY
ELSE 0
END AS QTY_OUT_TODAY,
CASE WHEN CONFIRM_DATE <= TO_DATE(TO_CHAR(SYSDATE,&＃39;yyyy-MM-dd HH24:mi:ss&＃39;) ,&＃39;yyyy-MM-dd HH24:mi:ss&＃39;)
AND CONFIRM_DATE >SYSDATE
AND BIZ_SOURCE =&＃39;0&＃39;
THEN MASTER_UNIT_QTY
ELSE 0
END AS QTY_IN_TODAY,
www.2cto.com
(SELECT SUM(MASTER_UNIT_QTY) from wms_inventory where wms_inventory.product_id = h.product_id) as qty_today,
CASE WHEN
BIZ_SOURCE =&＃39;2&＃39;
THEN MASTER_UNIT_QTY
ELSE 0
END AS QTY_OUT,
CASE WHEN
BIZ_SOURCE =&＃39;0&＃39;
THEN MASTER_UNIT_QTY
ELSE 0
END AS QTY_IN
FROM WMS_INVENTORY_HISTORY H ) AA
GROUP BY
AA.PRODUCT_CNAME,
AA.PRODUCT_ID,
AA.PRODUCT_CODE,
AA.MODEL,
AA.MASTER_UNIT,
AA.OTHER_UNIT,
AA.PACKAGE_RATIO,
AA.CUSTOMER
order by
AA.CUSTOMER
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 