可用库存查询
查询条件，必填 GOODSID GOODSBATCH
总库存（头表）
SELECT E.SCMLOCK SCM锁,
E.REPORTLOCK 报告锁,
A.OWNERID,
A.COMPANYID 分公司编号,
NVL(K.SNAME, '-') AS COMPANYNAME ,
H.NAME AS GOODSNAME ,
H.MODEL 规格,
H.TRADEMARK 厂牌,
H.MATYPE 是否麻精,
NVL(G.SNAME, '-') AS MATYPENAME ,
H.SUMTYPE,
H.LICENSENUMBER 批准文号,
A.MID1,
A.MID2,
A.MID3,
A.MID4,
A.Billid,
A.GOODSID ,
A.GOODSBATCH ,
E.PRODUCTDATE 生产日期,
E.EXPIREDATE 失效日期,
A.QUALITYFLG 品质ID,
J.SNAME AS 品质标记,
H.GOODSTOPCATEGORYID,
C.SNAME AS 商品类别,
H.GOODSCATEGORYID,
D.SNAME AS 流转类型,
SUM(NVL(A.BIGUNITNUM, 0) * E.BIGPERNUM + NVL(A.STANDUNITNUM, 0)) AS 库存总数,
SUM(NVL(A.LOCKEDBIGUNITNUM1, 0) * E.BIGPERNUM +
NVL(A.LOCKEDSTANDUNITNUM1, 0)) AS 锁定总数,
SUM(NVL(B.PICKBIGUNITNUM, 0) * E.BIGPERNUM +
NVL(B.PICKSTANDUNITNUM, 0)) AS 拣选占用总数,
SUM(NVL(B.MOVEBIGUNITNUM, 0) * E.BIGPERNUM +
NVL(B.MOVESTANDUNITNUM, 0)) AS 移库占用总数,
SUM(NVL(B.REPBIGUNITNUM, 0) * E.BIGPERNUM +
NVL(B.REPSTANDUNITNUM, 0)) AS 补货占用总数,
SUM(NVL(A.BIGUNITNUM, 0) * E.BIGPERNUM + NVL(A.STANDUNITNUM, 0) -
NVL(A.LOCKEDBIGUNITNUM1, 0) * E.BIGPERNUM -
NVL(A.LOCKEDSTANDUNITNUM1, 0) -
NVL(B.PICKBIGUNITNUM, 0) * E.BIGPERNUM -
NVL(B.PICKSTANDUNITNUM, 0) -
NVL(B.MOVEBIGUNITNUM, 0) * E.BIGPERNUM -
NVL(B.MOVESTANDUNITNUM, 0) -
NVL(B.REPBIGUNITNUM, 0) * E.BIGPERNUM -
NVL(B.REPSTANDUNITNUM, 0)) AS 可用总数,
CASE
WHEN TO_DATE(E.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE > 180 AND
SYSDATE - TO_DATE(E.PRODUCTDATE, 'yyyy-mm-dd') < 365 THEN
'促销'
WHEN TO_DATE(E.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE > 180 AND
SYSDATE - TO_DATE(E.PRODUCTDATE, 'yyyy-mm-dd') >= 365 THEN
'促销'
WHEN TO_DATE(E.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE <= 180 AND
TO_DATE(E.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE > 0 THEN
'近效'
WHEN TO_DATE(E.EXPIREDATE, 'yyyy-mm-dd') - SYSDATE <= 0 THEN
'失效'
END 效期区分
FROM (SELECT P.*,
CASE
WHEN P.ISALLOWOUT = '1' THEN
P.BIGUNITNUM
ELSE
0
END AS LOCKEDBIGUNITNUM1,
CASE
WHEN P.ISALLOWOUT = '1' THEN
P.MIDUNITNUM
ELSE
0
END AS LOCKEDMIDUNITNUM1,
CASE
WHEN P.ISALLOWOUT = '1' THEN
P.STANDUNITNUM
ELSE
0
END AS LOCKEDSTANDUNITNUM1
FROM STOCKGOODSTBL P) A,
V_STOCKOCCUPY_ONLY B,
(SELECT CODEVALUE, SNAME
FROM BASEDETAILMST
WHERE CODEID = 'SYS_GOODSTOPCATEGORY') C,
(SELECT CODEVALUE, SNAME
FROM BASEDETAILMST
WHERE CODEID = 'SYS_GOODSCATEGORY') D,
GOODSBATCHMST E,
(SELECT CODEVALUE
FROM BASEDETAILMST
WHERE BASEDETAILMST.CODEID = 'SYS_SUMTYPE') F,
BASEDETAILMST G,
BASEDETAILMST J,
GOODSMST H,
GOODSAREAMST I,
COMPANYMST K
WHERE A.goodsid = b.goodsid(+)
and A.goodsbatch = b.goodsbatch(+)
and A.qualityflg = b.qualityflg(+)
and A.ownerid = b.ownerid(+)
and A.stockid = b.stockid(+)
and A.goodsareaid = b.goodsareaid(+)
and A.goodsposid = b.goodsposid(+)
and A.mid1 = b.mid1(+)
and A.mid2 = b.mid2(+)
and A.mid3 = b.mid3(+)
and A.mid4 = b.mid4(+)
and A.mid5 = b.mid5(+)
AND A.GOODSID = H.GOODSID
AND A.GOODSID = E.GOODSID
AND A.GOODSBATCH = E.GOODSBATCH
AND H.SUMTYPE = F.CODEVALUE(+)
AND H.MATYPE = G.CODEVALUE
AND G.CODEID = 'SYS_MATYPE'
AND G.ACTIONFLG = '1'
AND A.QUALITYFLG = J.CODEVALUE
AND J.CODEID = 'SYS_QUALITYFLAG'
AND J.ACTIONFLG = '1'
AND H.GOODSTOPCATEGORYID = C.CODEVALUE
AND H.GOODSCATEGORYID = D.CODEVALUE
AND A.STOCKID = I.STOCKID(+)
AND A.STOCKAREAID = I.STOCKAREAID(+)
AND A.GOODSAREAID = I.GOODSAREAID(+)
AND A.COMPANYID = K.COMPANYID(+)
AND A.GOODSID = '1110247012'
AND A.GOODSBATCH = 'L2122031'
GROUP BY A.OWNERID,
E.SCMLOCK,
E.REPORTLOCK,
A.GOODSID,
H.NAME,
H.MODEL,
H.TRADEMARK,
H.MATYPE,
G.SNAME,
H.SUMTYPE,
A.MID1,
A.MID2,
A.MID3,
A.MID4,
A.Billid,
E.PRODUCTDATE,
A.GOODSBATCH,
A.QUALITYFLG,
E.EXPIREDATE,
H.GOODSTOPCATEGORYID,
C.SNAME,
H.LICENSENUMBER,
H.GOODSCATEGORYID,
D.SNAME,
J.SNAME,
A.COMPANYID,
K.SNAME
ORDER BY GOODSID, GOODSBATCH
货位库存 细单
SELECT H.FZDW AS ,
H.BZXS,
SUM(F_GETGOODS_ZSSL(H.GOODSID, A.STANDUNITNUM)) AS ZSSL,
A.OWNERID,
A.STOCKID,
A.COMPANYID,
A.GOODSID,
A.GOODSBATCH,
A.BOXID,
A.MID1,
A.MID2,
A.MID3,
A.MID4,
A.MID5,
A.PURCHASENO,
A.INBATCHCODE,
A.ISCROSSSTOCK,
A.QUALITYFLG,
A.PRODUCTDATE,
A.BILLID,
A.STOCKAREAID,
A.GOODSAREAID 货区,
A.GOODSPOSID AS 货位,
F.SNAME AS 库区,
SUM(A.BIGUNITNUM) 库存箱数,
SUM(A.STANDUNITNUM) 库存个数,
NVL(SUM(A.BIGUNITNUM), 0) * E.BIGPERNUM +
NVL(SUM(A.STANDUNITNUM), 0) AS 库存总数,
SUM(A.LOCKEDBIGUNITNUM1) 锁定箱数,
SUM(A.LOCKEDSTANDUNITNUM1) 锁定个数,
NVL(SUM(A.LOCKEDBIGUNITNUM1), 0) * E.BIGPERNUM +
NVL(SUM(A.LOCKEDSTANDUNITNUM1), 0) AS 锁定总数,
SUM(NVL(C.PICKBIGUNITNUM, 0)) 拣选占用箱数,
SUM(NVL(C.PICKSTANDUNITNUM, 0)) 拣选占用个数,
SUM(NVL(C.PICKBIGUNITNUM, 0)) * E.BIGPERNUM +
SUM(NVL(C.PICKSTANDUNITNUM, 0)) AS 拣选占用总数,
SUM(NVL(C.MOVEBIGUNITNUM, 0)) 移库占用箱数,
SUM(NVL(C.MOVESTANDUNITNUM, 0)) 移库占用个数,
SUM(NVL(C.MOVEBIGUNITNUM, 0)) * E.BIGPERNUM +
SUM(NVL(C.MOVESTANDUNITNUM, 0)) AS 移库占用总数,
SUM(NVL(C.REPBIGUNITNUM, 0)) 补货占用箱数,
SUM(NVL(C.REPSTANDUNITNUM, 0)) 补货占用个数,
SUM(NVL(C.REPBIGUNITNUM, 0)) * E.BIGPERNUM +
SUM(NVL(C.REPSTANDUNITNUM, 0)) AS 补货占用总数,
NVL(SUM(A.BIGUNITNUM), 0) - NVL(SUM(A.LOCKEDBIGUNITNUM1), 0) -
NVL(SUM(C.PICKBIGUNITNUM), 0) - NVL(SUM(C.MOVEBIGUNITNUM), 0) -
NVL(SUM(C.REPBIGUNITNUM), 0) 可用箱数,
NVL(SUM(A.STANDUNITNUM), 0) - NVL(SUM(A.LOCKEDSTANDUNITNUM1), 0) -
NVL(SUM(C.PICKSTANDUNITNUM), 0) - NVL(SUM(C.MOVESTANDUNITNUM), 0) -
NVL(SUM(C.REPSTANDUNITNUM), 0) 可用个数,
NVL(SUM(A.BIGUNITNUM), 0) * E.BIGPERNUM +
NVL(SUM(A.STANDUNITNUM), 0) -
NVL(SUM(A.LOCKEDBIGUNITNUM1), 0) * E.BIGPERNUM -
NVL(SUM(A.LOCKEDSTANDUNITNUM1), 0) -
SUM(NVL(C.PICKBIGUNITNUM, 0)) * E.BIGPERNUM -
SUM(NVL(C.PICKSTANDUNITNUM, 0)) -
SUM(NVL(C.MOVEBIGUNITNUM, 0)) * E.BIGPERNUM -
SUM(NVL(C.MOVESTANDUNITNUM, 0)) -
SUM(NVL(C.REPBIGUNITNUM, 0)) * E.BIGPERNUM -
SUM(NVL(C.REPSTANDUNITNUM, 0)) AS 可用总数
FROM (SELECT P.*,
CASE
WHEN P.ISALLOWOUT = '1' THEN
P.BIGUNITNUM
ELSE
0
END AS LOCKEDBIGUNITNUM1,
CASE
WHEN P.ISALLOWOUT = '1' THEN
P.MIDUNITNUM
ELSE
0
END AS LOCKEDMIDUNITNUM1,
CASE
WHEN P.ISALLOWOUT = '1' THEN
P.STANDUNITNUM
ELSE
0
END AS LOCKEDSTANDUNITNUM1
FROM STOCKGOODSTBL P) A,
GOODSAREAMST B,
V_STOCKOCCUPY_ONLY C,
GOODSBATCHMST E,
BASEDETAILMST F,
GOODSMST H
WHERE A.STOCKID = B.STOCKID(+)
AND A.STOCKAREAID = B.STOCKAREAID(+)
AND A.GOODSAREAID = B.GOODSAREAID(+)
AND A.GOODSID = C.GOODSID(+)
AND A.GOODSBATCH = C.GOODSBATCH(+)
AND A.GOODSAREAID = C.GOODSAREAID(+)
AND A.GOODSPOSID = C.GOODSPOSID(+)
AND A.STOCKID = C.STOCKID(+)
AND A.OWNERID = C.OWNERID(+)
AND A.QUALITYFLG = C.QUALITYFLG(+)
AND A.MID1 = C.MID1(+)
AND A.MID2 = C.MID2(+)
AND A.MID3 = C.MID3(+)
AND A.MID4 = C.MID4(+)
AND A.MID5 = C.MID5(+)
AND A.GOODSID = H.GOODSID
AND A.GOODSID = E.GOODSID
AND A.GOODSBATCH = E.GOODSBATCH
AND A.STOCKAREAID = F.CODEVALUE
AND F.CODEID = 'SYS_STOCKAREAID'
AND F.ACTIONFLG = '1'
AND NVL(A.BIGUNITNUM, 0) * E.BIGPERNUM + NVL(A.STANDUNITNUM, 0) > 0
AND A.GOODSID = '1110247012'
AND A.GOODSBATCH = 'L2122031'
GROUP BY H.FZDW,
H.BZXS,
A.OWNERID,
A.STOCKID,
A.COMPANYID,
A.GOODSID,
A.GOODSBATCH,
A.BOXID,
A.MID1,
A.MID2,
A.MID3,
A.MID4,
A.MID5,
A.PURCHASENO,
A.INBATCHCODE,
A.ISCROSSSTOCK,
A.QUALITYFLG,
A.PRODUCTDATE,
A.BILLID,
A.STOCKAREAID,
A.GOODSAREAID,
A.GOODSPOSID,
E.BIGPERNUM,
F.SNAME
文档更新时间: 2023-06-26 09:43 作者：周骏
