select A.pluno,A.pluname,A.qty,
CASE WHEN b.QTY IS NULL THEN 0 ELSE B.QTY END AS XSQTY ,
case when c.tgqty is null then 0 else c.tgqty end as TGQTY,
case when d.jhqty is null then 0 else d.jhqty end as JHQTY,
case when E.jhqty is null then 0 else d.jhqty end as TCQTY,
A.qty+case when d.jhqty is null then 0 else d.jhqty end
-CASE WHEN b.QTY IS NULL THEN 0 ELSE B.QTY END
-case when c.tgqty is null then 0 else c.tgqty end
-case when E.jhqty is null then 0 else E.jhqty end
AS 'SSKC'
--结存库存
from (select pluno,pluname,sum(qty) qty from getstock()
group by pluno,pluname) A
--未审核POS销售
left join (SELECT PLUNO=A.PLUNO, QTY=SUM(A.QTY)
FROM
(select PLUNO=A.PLUNO, PLUNAME=A.PLUNAME, SPEC=A.SPEC, QTY=A.QTY, NET=A.NET, DISAMT=A.DISAMT,SLAMT=A.NET+A.DISAMT
from (select A.PLUNO, A.PLUNAME, A.SPEC, D.QTY, D.NET, D.DISAMT, D.PLUID, A.OPTID, A.DPTID, A.BNDID
from BASPLUCRTC A,
(
select B.PLUID, QTY=SUM(B.QTY), NET=SUM(B.AMT - B.DISAMT-ISNULL(B.DISC,0)), DISAMT=SUM(B.DISAMT+ISNULL(B.DISC,0))
from POSSRLBF C(NOLOCK), POSSRLDT B(NOLOCK)
where C.UID = B.UID and c.valid=0 and (C.FLAG = 1 or C.FLAG = 5) and B.ISDEL = 0
group by B.PLUID
) D
where A.PLUID = D.PLUID) A, BASPLUCRTC B, BASDEPT E
where A.PLUID=B.PLUID AND B.DPTID=E.ID
UNION ALL
SELECT PLUNO=A.PLUNO, PLUNAME=MAX(A.PLUNAME), SPEC=MAX(A.SPEC), QTY=SUM(B.QTY), NET=SUM(B.SLNET), DISAMT=SUM(B.DISAMT),SLAMT=SUM(B.SLAMT)
FROM DOC21BF C, DOC21DT B, BASPLUCRTC A, BASDEPT E
WHERE C.DOCNO=B.DOCNO AND B.PLUID=A.PLUID AND A.DPTID=E.ID
AND C.CTYP=1 AND ISNULL(C.RSV,0)=0 AND C.VALID=0
GROUP BY A.PLUNO
) A
GROUP BY A.PLUNO) b
on a.pluno=b.pluno
--未审核团购
left join (select c.pluno,c.pluname,sum(b.qty) tgqty
from doc21bf a,doc21dt b,basplumain c
where a.docno=b.docno and b.pluid=c.pluid and a.valid = '0'
group by c.pluno,c.pluname) c
on a.pluno=c.pluno
--未审核进货
left join (select c.pluno,c.pluname,sum(b.qty) jhqty from doc01bf a,doc01dt b,basplumain c
where a.docno=b.docno and b.pluid=c.pluid and a.valid='0' and (a.ctyp!='6')
group by c.pluno,c.pluname) d
on a.pluno=d.pluno
--未审核调出
left join (select c.pluno,c.pluname,sum(b.qty) jhqty from doc01bf a,doc01dt b,basplumain c
where a.docno=b.docno and b.pluid=c.pluid and a.valid='0' and (a.ctyp='6')
group by c.pluno,c.pluname) E
on a.pluno=E.pluno
where A.pluname not like '+%'