SELECT A.BARCODEID ,
GDID ,
OPNAME ,
A.SPEC ,
A.UNITNAME ,
A.DEPTLEVEL2_ID ,
A.DEPTLEVEL2_NAME ,
A.DEPTLEVEL4_ID ,
A.DEPTLEVEL4_NAME ,
A.SHOPID ,
SHOPNAME ,
VENDERID ,
VENDERNAME ,
FACING ,
A.LOGISTICS ,
PURWEEKDAY01 ,
PURWEEKDAY02 ,
PURWEEKDAYALL ,
GOODSSTATUS ,
SPURNUM ,
SRATNUM ,(?{E_SDATE}-?{S_SDATE})+1 daycount,
QTY1,QTY35,IT.STOCKQTY GOODSSTOCKQTY FROM DM.DM_TB_NONAUTOPURCHASE_GOODS A
LEFT JOIN
(
SELECT /*+ parallel(k, 4) */ SHOPID,GOODSID,SUM(CASE WHEN SDATE2 between TO_TIMESTAMP(?{S_SDATE}) AND TO_TIMESTAMP(?{E_SDATE}) THEN QTY END)QTY1,
SUM(CASE WHEN SDATE2 between TO_TIMESTAMP(?{E_SDATE}-34) AND TO_TIMESTAMP(?{E_SDATE}) THEN QTY END)QTY35 FROM DM.DM_TB_MANAGE_SALEGOODSDETAIL2 k
GROUP BY SHOPID,GOODSID) B ON A.SHOPID=B.SHOPID AND A.GDID=B.GOODSID INNER JOIN (SELECT /*+ parallel(P, 4) */* FROM DM.DM_TB_ITEMTABLEINFOR P
WHERE SDATE=TO_CHAR(?{E_SDATE},'YYYYMMDD')) IT ON A.SHOPID=IT.SHOPID AND A.GDID=IT.GOODSID ; |