用友商贸宝进销存平衡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)