用友商貿寶進銷存平衡SQL驗證

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, --產品編碼(非空)
t.ProdName as prodName, --產品名稱(非空)
t.PDWName as prodUnit, --產品單位(非空)
t.Prod_Number1 as prodQuantity, --產品數量(非空)(負數為缺貨)
'[數量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, --單據編號(非空)
t.BillDate as billDate, --單據日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據類型(非空)(非空!!!取單據類型ID+單據類型名稱組合 )
t.Prod_ID as prodCode, --產品編碼(非空)
t.ProdName as prodName, --產品名稱(非空)
t.pDWname as prodUnit, --產品單位(非空)
-t.Prod_Number * t.pDW_Ratio as prodQuantity, --產品數量(非空)(退貨為負數)
t.OutStorCode + ' ' + t.OutStorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據發生時間]' + t.BillTime as remark--備注
from
c_vw_BillOther t
where t.InorOut is null
)
union all
(select
t.BillSN as billCode, --單據編號(非空)
t.BillDate as billDate, --單據日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據類型(非空)(非空!!!取單據類型ID+單據類型名稱組合 )
t.Prod_ID as prodCode, --產品編碼(非空)
t.ProdName as prodName, --產品名稱(非空)
t.pDWname as prodUnit, --產品單位(非空)
t.Prod_Number * t.pDW_Ratio as prodQuantity, --產品數量(非空)(退貨為負數)
t.InStorCode + ' ' + t.InStorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據發生時間]' + t.BillTime as remark--備注
from
c_vw_BillOther t
where t.InorOut is null
)
union all
(
select
t.BillSN as billCode, --單據編號(非空)
t.BillDate as billDate, --單據日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據類型(非空)(非空!!!取單據類型ID+單據類型名稱組合 )
t.Prod_ID as prodCode, --產品編碼(非空)
t.ProdName as prodName, --產品名稱(非空)
t.pDWname as prodUnit, --產品單位(非空)
case
when t.InorOut = 2
then t.Prod_Number * t.pDW_Ratio
else -t.Prod_Number * t.pDW_Ratio
end as prodQuantity, --產品數量(非空)(退貨為負數)
t.StorCode + ' ' + t.StorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據發生時間]' + 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, --產品編碼(非空)
t.ProdName as prodName, --產品名稱(非空)
t.PDWName as prodUnit, --產品單位(非空)
t.Prod_Number1 as prodQuantity, --產品數量(非空)(負數為缺貨)
'[數量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, --單據編號(非空)
t.BillDate as billDate, --單據日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據類型(非空)(非空!!!取單據類型ID+單據類型名稱組合 )
t.UnitName as storeName, --門店名稱(非空)
t.Unit_ID as storeCode, --門店編碼(非空)
t.Prod_ID as prodCode, --產品編碼(非空)
t.ProdName as prodName, --產品名稱(非空)
t.pDWname as prodUnit, --產品單位(非空)
case
when t.InorOut = 1
then t.Prod_Number * t.pDW_Ratio
else -t.Prod_Number * t.pDW_Ratio
end as prodQuantity, --產品數量(非空)(退貨為負數)
t.DisPrice / pDW_Ratio as prodPrice, --產品價格(非空)(實際售價)
t.DisMoney as prodAmount, --合計金額(非空)
t.StorCode + ' ' + t.StorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據發生時間]' + 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, --單據編號(非空)
t.BillDate as billDate, --單據日期(非空)(審核日期,庫存變動日期)
cast(t.BillType as varchar)
+ ' '
+ t.BillName as billType, --單據類型(非空)(非空!!!取單據類型ID+單據類型名稱組合 )
t.Unit_ID as supplierCode, --供應商編碼(非空)(新增)
t.Unit_ID as supplierName, --供應商名稱(非空)(新增)
t.Prod_ID as prodCode, --產品編碼(非空)
t.ProdName as prodName, --產品名稱(非空)
t.pDWname as prodUnit, --產品單位(非空)
case
when t.InorOut = 2
then t.Prod_Number * t.pDW_Ratio
else -t.Prod_Number * t.pDW_Ratio
end as prodQuantity, --產品數量(非空)(退貨為負數)
t.DisPrice / pDW_Ratio as prodPrice, --產品價格(非空)(實際售價)
t.DisMoney as prodAmount, --合計金額(非空)
t.StorCode + ' ' + t.StorName as stockType, --倉庫類型(非空!!!倉庫編號+倉庫名稱組合)
t.Abst + ' [單據發生時間]' + 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)

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