半成品生产领料

--半成品生产领料
select a.月,a.年,a.一类编码,a.存货一类,
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.计量单位名称,
sum(重量) as 重量,sum(金额) as 金额,
sum(合格数量) as 合格数量,sum(合格金额) as 合格金额,
sum(不合格数量) 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 毛重,
--修改加工费单价
(select case when (inv.cInvName like '%***%' or inv.cInvName like '%***%') and inv.cInvName like '%***%' then '***'
when (inv.cInvName like '%***%' or inv.cInvName like '%***%') and inv.cInvName not like '%***%' then '***'
when inv.cInvName = '***' then '***'
when inv.cInvName like '***%' then '***'
else '***' end ) 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') *数量)
when 存货一类 in ('***') and 计量单位名称 in ('kg') then sum(数量)
else 0 end) as 重量,sum(金额) as 金额
from (
select year(rd.dDate) as 年,month(rd.dDate) 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 inv.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(case when 仓库编码 in ('***') and 收发单据号 not in ('***') then 数量 end ) as 合格数量,
sum(case when 仓库编码 in ('***') and 收发单据号 not in ('***') then 金额 end ) as 合格金额,
sum(case when 仓库编码 in ('***') or 收发单据号 in ('***') then 数量 end ) as 不合格数量,
sum(case when 仓库编码 in ('***') or 收发单据号 in ('***') then 金额 end ) as 不合格金额,
sum(case when 仓库编码 not in ('***') then 数量 end ) as 其他数量,
sum(case when 仓库编码 not in ('***') then 金额 end ) as 其他金额
from (
select year(rd10.dDate) as 年,month(rd10.dDate) as 月,rd10.cCode as 收发单据号,
rd10.cRdCode as 收发类别编码,rd10.cWhCode as 仓库编码,war.cWhName as 仓库,
invc1.cInvCName as 存货一类,invc2.cInvCCode as 大类编码,invc2.cInvCName as 存货大类,
invc.cInvCName as 存货分类,inv.cInvCode as 存货编码,
inv.cInvName as 存货名称,inv1.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 inv1 on rdr10.cInvCode = inv1.cInvCode
left join Inventory inv on
(case when inv1.cInvName like '%***' then stuff(inv1.cInvName,CHARINDEX('**',inv1.cInvName),LEN(inv1.cInvName),'***')
when inv1.cInvName like '%***' then stuff(inv1.cInvName,CHARINDEX('***',inv1.cInvName),LEN(inv1.cInvName),'***')
else '***' end) = inv.cInvName
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 or 不合格数量 <> 0 or 不合格金额 <> 0 or 其他数量 <> 0)
and a.一类编码 != '***'
and a.大类编码 != '***'
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.计量单位名称
order by a.存货编码

这是一个记录~~~