数据库直接抽取存货汇总表跳过审核

微相阵

--用友u8批次存货汇总表跳过审核直接计算
select 日期,仓库编码,仓库,存货一类,存货大类,存货分类,存货编码,存货名称,规格型号,
主计量单位编码,计量单位名称,批号,净重,毛重,sum(入库数量) as 入库数量,sum(出库数量) as 出库数量
from(
--库存期初表
select  convert(varchar(11),rd34.dDate,120) as 日期,rd34.cCode as 收发单据号,
rd34.cRdCode as 收发类别编码,rd34.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr34.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr34.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 入库数量,0 as 出库数量
from dbo.rdrecord34 rd34
left join dbo.rdrecords34 rdr34 on rd34.ID = rdr34.ID
left join dbo.Inventory inv on rdr34.cInvCode = inv.cInvCode
left join dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join dbo.Warehouse war on rd34.cWhCode = war.cWhCode
union all
--材料出库单
select convert(varchar(11),rd.dDate,120) as 日期,rd.cCode as 收发单据号,
rd.cRdCode as 收发类别编码,rd.cWhCode as 仓库编码,war.cWhName as 仓库,
--invcf1.cInvCName as 父项存货一类,invcf2.cInvCName as 父项存货大类,
--invcf.cInvCName as 父项存货分类,rd.cPsPcode as 父项产品编码,invf.cInvName as 父项存货名称,invf.cInvStd as 父项规格型号,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
0 as 入库数量,iQuantity as 出库数量
from dbo.rdrecord11 rd
left join dbo.rdrecords11 rdr on rd.ID = rdr.ID
left join dbo.Inventory inv on rdr.cInvCode = inv.cInvCode
left join dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join dbo.Inventory invf on rd.cPsPcode = invf.cInvCode
left join dbo.InventoryClass invcf on invf.cInvCCode = invcf.cInvCCode
left join dbo.InventoryClass invcf1 on left(invf.cInvCCode,2) = invcf1.cInvCCode
left join dbo.InventoryClass invcf2 on left(invf.cInvCCode,4) = invcf2.cInvCCode
left join dbo.Warehouse war on rd.cWhCode = war.cWhCode
union all
--产成品入库
select  convert(varchar(11),rd10.dDate,120) as 日期,rd10.cCode as 收发单据号,
rd10.cRdCode as 收发类别编码,rd10.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr10.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr10.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 入库数量,0 as 出库数量
from dbo.rdrecord10 rd10
left join dbo.rdrecords10 rdr10 on rd10.ID = rdr10.ID
left join dbo.Inventory inv on rdr10.cInvCode = inv.cInvCode
left join dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join dbo.Warehouse war on rd10.cWhCode = war.cWhCode
union all
--采购入库单
select  convert(varchar(11),rd01.dDate,120) as 日期,rd01.cCode as 收发单据号,
rd01.cRdCode as 收发类别编码,rd01.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr01.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr01.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 入库数量,0 as 出库数量
from dbo.rdrecord01 rd01
left join dbo.rdrecords01 rdr01 on rd01.ID = rdr01.ID
left join dbo.Inventory inv on rdr01.cInvCode = inv.cInvCode
left join dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join dbo.Warehouse war on rd01.cWhCode = war.cWhCode
union all
--其他入库单
select  convert(varchar(11),rd08.dDate,120) as 日期,rd08.cCode as 收发单据号,
rd08.cRdCode as 收发类别编码,rd08.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr08.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr08.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 入库数量,0 as 出库数量
from dbo.rdrecord08 rd08
left join dbo.rdrecords08 rdr08 on rd08.ID = rdr08.ID
left join dbo.Inventory inv on rdr08.cInvCode = inv.cInvCode
left join dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join dbo.Warehouse war on rd08.cWhCode = war.cWhCode
union all
--其他出库单
select  convert(varchar(11),rd09.dDate,120) as 日期,rd09.cCode as 收发单据号,
rd09.cRdCode as 收发类别编码,rd09.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr09.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr09.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
0 as 入库数量,iQuantity as 出库数量
from dbo.rdrecord09 rd09
left join dbo.rdrecords09 rdr09 on rd09.ID = rdr09.ID
left join dbo.Inventory inv on rdr09.cInvCode = inv.cInvCode
left join dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join dbo.Warehouse war on rd09.cWhCode = war.cWhCode
union all
--销售出库单
select  convert(varchar(11),rd32.dDate,120) as 日期,rd32.cCode as 收发单据号,
rd32.cRdCode as 收发类别编码,rd32.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,rdr32.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,rdr32.cBatch as 批号,
inv.iInvWeight as 净重,inv.fGrossW as 毛重,
0 as 入库数量,iQuantity as 出库数量
from dbo.rdrecord32 rd32
left join dbo.rdrecords32 rdr32 on rd32.ID = rdr32.ID
left join dbo.Inventory inv on rdr32.cInvCode = inv.cInvCode
left join dbo.InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join dbo.InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join dbo.InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join dbo.ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join dbo.Warehouse war on rd32.cWhCode = war.cWhCode
) a
group by 日期,仓库编码,仓库,存货一类,存货大类,存货分类,存货编码,存货名称,规格型号,
主计量单位编码,计量单位名称,批号,净重,毛重