商品批号维护

商品批号维护

头单

 SELECT A.*, B.NAME AS GOODSNAME, B.SNAME,B.TRADEMARK,B.MODEL,B.OWNERID, TO_NUMBER(B.BIOLOGYFLG) AS BIOLOGYFLG, B.REPORTFLG, B.HOLDDAYS,'' AS STOCKID,NVL(B.IMPORTFLG,0) AS IMPORTFLG , 
        CASE WHEN FLOOR(TO_DATE(A.EXPIREDATE,'YYYY-MM-DD')-SYSDATE)+1<=NVL(C.HOLDDAYS,180)
              AND FLOOR(TO_DATE(A.EXPIREDATE,'YYYY-MM-DD')-SYSDATE)+1>0 THEN '近效'
             WHEN FLOOR(TO_DATE(A.EXPIREDATE,'YYYY-MM-DD')-SYSDATE)+1<=0 THEN '失效'
             ELSE '促销' END AVATYPENM,
         CASE WHEN (B.TEMPTYPEID='1' OR B.TEMPTYPEID='4' ) THEN 1 ELSE 0 END ISCOLD
         ,B.MAH,B.FACTORYNAME,B.FZDW,B.BZXS
   FROM GOODSBATCHMST A,GOODSMST B,AVATYPEGOODSLIST C 
   WHERE 1=1 
  AND A.GOODSID = '1000127600'
  AND A.GOODSBATCH = 'wx202306301'
    AND A.GOODSID = B.GOODSID(+)
    AND A.GOODSID = C.GOODSID(+)
  ORDER BY A.GOODSID,A.CREATEDATE DESC

细单