库存对账

————库存对账-含退货—
select

isnull(inv.whna,isnull(tro.pfwh,isnull(tri.powh,isnull(pro.whna,rpi.whna)))) as ‘仓库名称’
,isnull(inv.whid,isnull(tro.whid,isnull(tri.whid,isnull(pro.whid,rpi.whid)))) as ‘仓库代码’
,isnull(inv.ewid,isnull(tro.ewid,isnull(tri.ewid,isnull(pro.ewid,rpi.ewid)))) as ‘识别码’
,isnull(inv.code,isnull(tro.code,isnull(tri.code,isnull(pro.code,rpi.code)))) as ‘辅助编码’
,isnull(inv.inco,isnull(tro.inco,isnull(tri.inco,isnull(pro.inco,rpi.inco)))) as ‘产品’
,isnull(inv.ewid,isnull(tro.ewid,isnull(tri.ewid,isnull(pro.ewid,rpi.ewid))))+isnull(inv.inco,isnull(tro.inco,isnull(tri.inco,isnull(pro.inco,rpi.inco)))) as ‘仓-品组合’
,isnull(inv.qty,0) as ‘账面数量’
,isnull(tro.outqty,0) as ‘调拨出’
,isnull(tri.inqty,0) as ‘调拨入’
,isnull(pro.fqty,0) AS ‘采退出’
,isnull(rpi.rqty,0) AS ‘销退入’
,isnull(inv.qty,0)+isnull(tro.outqty,0)-isnull(tri.inqty,0)+isnull(pro.fqty,0)- isnull(rpi.rqty,0) as ‘理论数量’
from —库存明细汇总，取库存视图表，来源库存表bain的几个维度汇总
(
SELECT x.whna as whna ,x.whid as whid,x.ewid as ewid,x.code as code, a.inco as inco ,sum(a.qty) AS qty
FROM stquo a

LEFT JOIN waho b ON a.whid=b.whid
LEFT JOIN waho x ON a.whco=x.whid and x.whty=1
WHERE 1=1 and b.orid like ‘0%’ and b.whty in (‘4’,’5’,’6’,’7’,’9’,’13’,’15’,’96’,’98’,’99’)
group by a.cotl, a.inco,x.whid ,x.whna ,x.ewid,x.code
)inv
full join—调出汇总
(
SELECT w.whna as pfwh,w.whid as whid,w.ewid as ewid,w.code as code,b.inco as inco,sum(b.oqty) outqty
FROM ppma a
LEFT JOIN ppde b on a.biid=b.biid

LEFT join waho w on w.whid = a.pfwh
where 1=1 and a.flag in (‘21’,’22’)

GROUP BY w.whna ,w.whid,b.inco,w.ewid,w.code

) tro on inv.whid=tro.whid and inv.inco=tro.inco
full join—调入汇总
(
SELECT h.whna as powh,h.whid as whid,h.ewid as ewid,h.code as code,b.inco as inco,sum(b.iqty) AS inqty
FROM ppma a
LEFT JOIN ppde b on a.biid=b.biid

LEFT join waho h on h.whid = a.powh

where 1=1 and a.flag in (‘21’,’22’)
GROUP BY h.whna ,h.whid,b.inco,h.ewid,h.code
)tri on inv.whid=tri.whid and inv.inco=tri.inco

FULL JOIN—采退汇总

(
SELECT d.whna AS whna,d.whid AS whid,d.ewid AS ewid, d.code AS code,pd.inco,sum(pd.qty) as fqty

FROM prbm a
—left join prbd b on a.biid=b.biid
left join waho d on a.whid = d.whid
LEFT JOIN prma pr ON a.biid=pr.soco
LEFT JOIN prde pd ON pr.biid=pd.biid
WHERE a.orid like ‘0%’ and a.flag in (‘21’)—‘11’正式单据，’21’退货中a.biid like ‘PN2020071500000079’
AND pr.flag IN (‘11’) —采购退货出库单已审核
GROUP BY d.whna,d.whid,d.ewid, d.code,pd.inco
)pro on inv.whid=pro.whid and inv.inco=pro.inco
FULL JOIN —销退汇总
(
select wa.whna AS whna,a.whid AS whid,wa.ewid AS ewid, wa.code AS code,b.inco,sum(b.rqty) as rqty

FROM rpma a

left JOIN rpde b ON a.biid=b.biid

left join waho wa on a.whid = wa.whid
where a.orid like ‘0%’ and a.flag in (‘21’) AND b.rqty>0
GROUP BY wa.whna,a.whid,wa.ewid, wa.code,b.inco
)rpi on inv.whid=rpi.whid and inv.inco=rpi.inco




WHERE


isnull(inv.whid,isnull(tro.whid,isnull(tri.whid,isnull(pro.whid,rpi.whid)))) IN(‘U01’)

AND isnull(inv.inco,isnull(tro.inco,isnull(tri.inco,isnull(pro.inco,rpi.inco)))) IN (‘’)




order by inv.whid