投料及生产耗用包材

--投料及生产耗用包材

select a.月,a.年,a.一类编码 as 父项一类编码,a.存货一类 as 父项存货一类,
a.大类编码 as 父项大类编码,a.存货大类 as 父项存货大类,a.分类编码 as 父项分类编码,a.存货分类 as 父项存货分类,
a.存货编码 as 父项存货编码,a.存货名称 as 父项存货名称,a.规格型号 as 父项规格型号,a.主计量单位编码 as 父项主计量单位编码,
a.计量单位名称 as 父项计量单位名称,a.净重 as 父项净重,a.毛重 as 父项毛重,
cl.存货一类,cl.存货大类,cl.存货分类,cl.存货编码,cl.存货名称,cl.规格型号,cl.主计量单位编码,cl.计量单位名称,
sum(产品数量*a.净重) as 产品重量,sum(产品金额) as 产品金额,sum(材料重量) as 材料重量,sum(材料金额) as 材料金额
from
(select ua.iId as 月,ua.iYear as 年,invc1.cInvCCode as 一类编码,invc1.cInvCName as 存货一类,invc2.cInvCCode as 大类编码,invc2.cInvCName as 存货大类,invc.cInvCCode as 分类编码,
invc.cInvCName as 存货分类,inv.cInvCode as 存货编码,inv.cInvName as 存货名称,inv.cInvStd as 规格型号,
inv.cComUnitCode as 主计量单位编码,com.cComUnitName as 计量单位名称,inv.iInvWeight as 净重,inv.fGrossW as 毛重
from Inventory inv
left join InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
cross join UA_Period ua
where ua.cAcc_Id ='***'
) a
--材料出库单
left join (
select 年,月,父项存货一类,父项存货大类,父项产品编码,父项存货名称,父项规格型号,
存货一类,存货大类,存货分类,存货编码,存货名称,规格型号,主计量单位编码,计量单位名称,
(select case when 存货一类 in ('***','***') and 计量单位名称 in ('***','***','***','***') then sum(isnull(c0.净重,'0') *数量)
else sum(数量) end) as 材料重量,sum(金额) as 材料金额
from (
select year(rd.dDate) as 年,
(select case when day(rd.dDate) >= '26' then month(rd.dDate)+1
else month(rd.dDate) end ) as 月,
rd.cCode as 收发单据号,rd.cRdCode 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 计量单位名称,inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 数量,rdr.iUnitCost as 单价,rdr.iPrice as 金额
from rdrecord11 rd
left join rdrecords11 rdr on rd.ID = rdr.ID
left join Inventory inv on rdr.cInvCode = inv.cInvCode
left join InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join Inventory invf on rd.cPsPcode = invf.cInvCode
left join InventoryClass invcf on invf.cInvCCode = invcf.cInvCCode
left join InventoryClass invcf1 on left(invf.cInvCCode,2) = invcf1.cInvCCode
left join InventoryClass invcf2 on left(invf.cInvCCode,4) = invcf2.cInvCCode
) c0
group by 年,月,父项存货一类,父项存货大类,父项产品编码,父项存货名称,父项规格型号,
存货一类,存货大类,存货分类,存货编码,存货名称,规格型号,主计量单位编码,计量单位名称
)cl on cl.父项产品编码 = a.存货编码 and cl.年 = a.年 and cl.月 = a.月
--产成品入库单
left join (
select 年,月,存货一类,存货大类,存货分类,存货编码,存货名称,规格型号,
计量单位名称,净重,毛重,sum(数量) as 产品数量,sum(金额) as 产品金额
from (
select year(rd10.dDate) as 年,
(select case when day(rd10.dDate) >= '26' then month(rd10.dDate)+1
else month(rd10.dDate) end ) 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 计量单位名称,inv.iInvWeight as 净重,inv.fGrossW as 毛重,
iQuantity as 数量,rdr10.iUnitCost as 单价,rdr10.iPrice as 金额
from rdrecord10 rd10
left join rdrecords10 rdr10 on rd10.ID = rdr10.ID
left join Inventory inv on rdr10.cInvCode = inv.cInvCode
left join InventoryClass invc on inv.cInvCCode = invc.cInvCCode
left join InventoryClass invc1 on left(inv.cInvCCode,2) = invc1.cInvCCode
left join InventoryClass invc2 on left(inv.cInvCCode,4) = invc2.cInvCCode
left join ComputationUnit com on inv.cComUnitCode = com.cComUnitCode
left join Warehouse war on rd10.cWhCode = war.cWhCode
) c1
group by 年,月,
存货一类,存货大类,存货分类,存货编码,存货名称,规格型号,计量单位名称,净重,毛重
)cc on cc.存货编码 = a.存货编码 and cc.年 = a.年 and cc.月 = a.月

where (材料重量 <> 0 or 材料金额 <> 0 or 产品数量 <> 0 or 产品金额 <> 0)
and a.一类编码 = '***产品'

group by a.月,a.年,a.一类编码,a.存货一类,
a.大类编码,a.存货大类,a.分类编码,a.存货分类,a.存货编码,a.存货名称,a.规格型号,
a.主计量单位编码,a.计量单位名称,a.净重,a.毛重,
cl.存货一类,cl.存货大类,cl.存货分类,cl.存货编码,cl.存货名称,cl.规格型号,cl.主计量单位编码,cl.计量单位名称
order by a.存货编码