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,'yyyy-MM-dd HH24:mi:ss') ,'yyyy-MM-dd HH24:mi:ss') ?

? AND WMS_OUT_CONFIRM_DATE >SYSDATE ??

? AND BIZ_SOURCE ='2' ??

? THEN MASTER_UNIT_QTY ??

? ? ? ?ELSE 0 ?

? ? ? ? ?END AS QTY_OUT_TODAY, ?

CASE WHEN CONFIRM_DATE <= TO_DATE(TO_CHAR(SYSDATE,'yyyy-MM-dd HH24:mi:ss') ,'yyyy-MM-dd HH24:mi:ss') ?

? AND CONFIRM_DATE >SYSDATE ?

? AND BIZ_SOURCE ='0' ??

? 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 ='2' ??

? THEN MASTER_UNIT_QTY ??

? ? ELSE 0 ?

? ? ? END AS QTY_OUT, ?

CASE WHEN ? ?

? BIZ_SOURCE ='0' ??

? 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?
?