用友商貿(mào)寶進(jìn)銷存平衡SQL驗(yàn)證

2015/8/10 23:22:03山東用友

select
ISNULL(q.prodCode,w.prodCode) as prodCode,
w.calcStock,q.prodQuantity
from
(
select x.prodCode,sum(x.prodQuantity) as prodQuantity from
(
select
'$stockDate$' as stockDate, --庫存日期(非空)
t.StorCode + ' ' + t.StorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Prod_ID as prodCode, --產(chǎn)品編碼(非空)
t.ProdName as prodName, --產(chǎn)品名稱(非空)
t.PDWName as prodUnit, --產(chǎn)品單位(非空)
t.Prod_Number1 as prodQuantity, --產(chǎn)品數(shù)量(非空)(負(fù)數(shù)為缺貨)
'[數(shù)量2]' + cast(t.Prod_Number2 as varchar) as remark --備注
from
b_vw_Storage t
) x
group by x.prodCode
) q
full join
(
select ISNULL(g.prodCode,h.prodCode) as prodCode, isnull(g.other,0) + isnull(h.num,0) as calcStock
from
(
select
y.prodCode,SUM(y.prodQuantity) as other
from
(
(select
t.BillSN as billCode, --單據(jù)編號(非空)
t.BillDate as billDate, --單據(jù)日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據(jù)類型(非空)(非空!!!取單據(jù)類型ID+單據(jù)類型名稱組合 )
t.Prod_ID as prodCode, --產(chǎn)品編碼(非空)
t.ProdName as prodName, --產(chǎn)品名稱(非空)
t.pDWname as prodUnit, --產(chǎn)品單位(非空)
-t.Prod_Number * t.pDW_Ratio as prodQuantity, --產(chǎn)品數(shù)量(非空)(退貨為負(fù)數(shù))
t.OutStorCode + ' ' + t.OutStorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據(jù)發(fā)生時(shí)間]' + t.BillTime as remark--備注
from
c_vw_BillOther t
where t.InorOut is null
)
union all
(select
t.BillSN as billCode, --單據(jù)編號(非空)
t.BillDate as billDate, --單據(jù)日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據(jù)類型(非空)(非空!!!取單據(jù)類型ID+單據(jù)類型名稱組合 )
t.Prod_ID as prodCode, --產(chǎn)品編碼(非空)
t.ProdName as prodName, --產(chǎn)品名稱(非空)
t.pDWname as prodUnit, --產(chǎn)品單位(非空)
t.Prod_Number * t.pDW_Ratio as prodQuantity, --產(chǎn)品數(shù)量(非空)(退貨為負(fù)數(shù))
t.InStorCode + ' ' + t.InStorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據(jù)發(fā)生時(shí)間]' + t.BillTime as remark--備注
from
c_vw_BillOther t
where t.InorOut is null
)
union all
(
select
t.BillSN as billCode, --單據(jù)編號(非空)
t.BillDate as billDate, --單據(jù)日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據(jù)類型(非空)(非空!!!取單據(jù)類型ID+單據(jù)類型名稱組合 )
t.Prod_ID as prodCode, --產(chǎn)品編碼(非空)
t.ProdName as prodName, --產(chǎn)品名稱(非空)
t.pDWname as prodUnit, --產(chǎn)品單位(非空)
case
when t.InorOut = 2
then t.Prod_Number * t.pDW_Ratio
else -t.Prod_Number * t.pDW_Ratio
end as prodQuantity, --產(chǎn)品數(shù)量(非空)(退貨為負(fù)數(shù))
t.StorCode + ' ' + t.StorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據(jù)發(fā)生時(shí)間]' + t.BillTime as remark--備注
from
c_vw_BillOther t
where t.InorOut is not null
)
) y
group by y.prodCode
) g

full join
(
select ISNULL(m.prodCode,n.prodCode) as prodCode, isnull(m.prodQuantity,0) + isnull(n.num,0) as num
from
(
select x.prodCode,sum(x.prodQuantity) as prodQuantity from
(
select
'$stockDate$' as stockDate, --庫存日期(非空)
t.StorCode + ' ' + t.StorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Prod_ID as prodCode, --產(chǎn)品編碼(非空)
t.ProdName as prodName, --產(chǎn)品名稱(非空)
t.PDWName as prodUnit, --產(chǎn)品單位(非空)
t.Prod_Number1 as prodQuantity, --產(chǎn)品數(shù)量(非空)(負(fù)數(shù)為缺貨)
'[數(shù)量2]' + cast(t.Prod_Number2 as varchar) as remark --備注
from
b_vw_StorageIni t
) x
group by x.prodCode
) m
full join
(
select ISNULL(i.prodCode,j.prodCode) as prodCode, isnull(j.purchase,0) - isnull(i.sale,0) as num
from
(
select
x.prodCode,SUM(x.prodQuantity) as sale
from
(
select
t.BillSN as billCode, --單據(jù)編號(非空)
t.BillDate as billDate, --單據(jù)日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據(jù)類型(非空)(非空!!!取單據(jù)類型ID+單據(jù)類型名稱組合 )
t.UnitName as storeName, --門店名稱(非空)
t.Unit_ID as storeCode, --門店編碼(非空)
t.Prod_ID as prodCode, --產(chǎn)品編碼(非空)
t.ProdName as prodName, --產(chǎn)品名稱(非空)
t.pDWname as prodUnit, --產(chǎn)品單位(非空)
case
when t.InorOut = 1
then t.Prod_Number * t.pDW_Ratio
else -t.Prod_Number * t.pDW_Ratio
end as prodQuantity, --產(chǎn)品數(shù)量(非空)(退貨為負(fù)數(shù))
t.DisPrice / pDW_Ratio as prodPrice, --產(chǎn)品價(jià)格(非空)(實(shí)際售價(jià))
t.DisMoney as prodAmount, --合計(jì)金額(非空)
t.StorCode + ' ' + t.StorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據(jù)發(fā)生時(shí)間]' + t.BillTime as remark--備注
from
c_vw_BillSale t
--where t.DisPrice <> 0
) x
group by x.prodCode
) i full join
(
select
y.prodCode,SUM(y.prodQuantity) as purchase
from
(
select
t.BillSN as billCode, --單據(jù)編號(非空)
t.BillDate as billDate, --單據(jù)日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據(jù)類型(非空)(非空!!!取單據(jù)類型ID+單據(jù)類型名稱組合 )
t.Unit_ID as supplierCode, --供應(yīng)商編碼(非空)(新增)
t.Unit_ID as supplierName, --供應(yīng)商名稱(非空)(新增)
t.Prod_ID as prodCode, --產(chǎn)品編碼(非空)
t.ProdName as prodName, --產(chǎn)品名稱(非空)
t.pDWname as prodUnit, --產(chǎn)品單位(非空)
case
when t.InorOut = 2
then t.Prod_Number * t.pDW_Ratio
else -t.Prod_Number * t.pDW_Ratio
end as prodQuantity, --產(chǎn)品數(shù)量(非空)(退貨為負(fù)數(shù))
t.DisPrice / pDW_Ratio as prodPrice, --產(chǎn)品價(jià)格(非空)(實(shí)際售價(jià))
t.DisMoney as prodAmount, --合計(jì)金額(非空)
t.StorCode + ' ' + t.StorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據(jù)發(fā)生時(shí)間]' + t.BillTime as remark--備注
from
c_vw_BillBuy t
) y
group by y.prodCode
) j
on i.prodCode = j.prodCode
) n
on m.prodCode = n.prodCode
) h
on g.prodCode = h.prodCode
) w
on q.prodCode = w.prodCode
where q.prodQuantity <> w.calcStock or (q.prodQuantity is null and w.calcStock <>0)

濟(jì)南用友主要服務(wù)于濟(jì)南地區(qū)的中型、小微型企業(yè)客戶，是山東用友軟件金牌經(jīng)銷商,公司主要代理用友暢捷通T+、T1商貿(mào)寶、T3用友通、T6暢捷通ERP,用友U8等企業(yè)管理軟件。(濟(jì)南用友軟件咨詢熱線：0531-82825553)