分公司库存查询

分公司库存查询

查询条件，必填 GOODSID GOODSBATCH

--分公司库存查询
SELECT A.STOCKID, A.COMPANYID, A.BOXID, A.MID1, A.MID2, A.MID3, A.MID4, A.MID5, A.GOODSID 商品编码, B.LICENSENUMBER 批准文号, B.NAME AS 商品名称, B.MODEL 规格, B.TRADEMARK 厂牌, A.GOODSBATCH 批号, NVL(M.SNAME, '-') AS 分公司, A.BILLID, C.PRODUCTDATE 生产日期, C.EXPIREDATE 过期日期, SUM(TRUNC(A.TOTALNUM / C.BIGPERNUM)) AS 件数, 0 AS MIDUNITNUM, SUM(MOD（A.TOTALNUM, C.BIGPERNUM)) AS 零品, SUM(A.TOTALNUM) AS NUM, B.STANDUNIT 小包装, B.BIGUNIT 大包装, A.QUALITYFLG, A.ISALLOWOUT, A.ISCROSSSTOCK, B.GOODSTOPCATEGORYID 商品类别, F_GET_BASEDATA_NAME('SYS_GOODSTOPCATEGORY', B.GOODSTOPCATEGORYID) GOODSTOPCATEGORYNM, B.GOODSCATEGORYID, F_GET_BASEDATA_NAME('SYS_GOODSCATEGORY', B.GOODSCATEGORYID) 流转类型, CASE WHEN TO_DATE(C.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE > 180 AND SYSDATE - TO_DATE(C.PRODUCTDATE, 'yyyy-mm-dd') < 365 THEN '促销' WHEN TO_DATE(C.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE > 180 AND SYSDATE - TO_DATE(C.PRODUCTDATE, 'yyyy-mm-dd') >= 365 THEN '促销' WHEN TO_DATE(C.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE <= 180 AND TO_DATE(C.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE > 0 THEN '近效' WHEN TO_DATE(C.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE <= 0 THEN '失效' END 效期区分, NVL(F_GET_BASEDATA_NAME('SYS_MATYPE', B.MATYPE, 1), '-') AS 麻精类型, A.PURCHASENO, C.SCMLOCK , C.REPORTLOCK, MIN(NVL(P.POSLOCK, 0)) POSLOCK 
FROM STOCKGOODSTBL_COMPANY A, GOODSBATCHMST C, (SELECT CODEVALUE FROM BASEDETAILMST WHERE BASEDETAILMST.CODEID = 'SYS_SUMTYPE') H, BASEDETAILMST F, GOODSMST B, GOODSAREAMST K, COMPANYMST M, GOODSPOSMST P 
WHERE A.GOODSID = B.GOODSID 
  AND A.GOODSID = C.GOODSID 
  AND A.GOODSBATCH = C.GOODSBATCH 
  AND B.SUMTYPE = H.CODEVALUE(+) 
  AND A.QUALITYFLG = F.CODEVALUE 
  AND F.CODEID = 'SYS_QUALITYFLAG' 
  AND F.ACTIONFLG = '1' 
  AND A.STOCKID = K.STOCKID(+) 
  AND A.STOCKAREAID = K.STOCKAREAID(+) 
  AND A.GOODSAREAID = K.GOODSAREAID(+) 
  AND A.COMPANYID = M.COMPANYID(+) 
  AND A.STOCKID = P.STOCKID(+) 
  AND A.STOCKAREAID = P.STOCKAREAID(+) 
  AND A.GOODSAREAID = P.GOODSAREAID(+) 
  AND A.GOODSPOSID = P.GOODSPOSID(+) 
  AND A.GOODSID = '1000005822' 
  AND A.GOODSBATCH = 'LYF-ADT-230505-01'
 GROUP BY A.GOODSID, A.COMPANYID, A.BOXID, A.STOCKID, A.MID1, A.MID2, A.MID3, A.MID4, A.MID5, B.NAME, B.MODEL, B.TRADEMARK, A.GOODSBATCH, B.LICENSENUMBER, M.SNAME, A.BILLID, C.PRODUCTDATE, C.EXPIREDATE, B.STANDUNIT, B.BIGUNIT, A.QUALITYFLG, A.ISALLOWOUT, A.ISCROSSSTOCK, B.GOODSTOPCATEGORYID, B.GOODSCATEGORYID, B.MATYPE, A.PURCHASENO, C.SCMLOCK, C.REPORTLOCK ORDER BY A.GOODSID, A.GOODSBATCH, A.STOCKID, C.PRODUCTDATE, C.EXPIREDATE, B.LICENSENUMBER, A.BILLID