WMS仓储管理系统实现"收发存日报表"数据查询功能Sql语句--Oracle
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_INFROM (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,(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_INFROM 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.CUSTOMERorder by AA.CUSTOMER
