业务背景
- 用于分析各月计划与实际物料及费用偏差。
- 费用根据成本计算表分摊逻辑需要计算后按产线、车间、公司等费用挂的成本中心，按价值法进行均摊。
- 此含税价取最近一次采购价格，按月会发生变化。
代码示例
–建立临时表
CREATE TABLE #KD_CB (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[存货类别编码] varchar(60) null,
[存货类别名称] varchar(60) null,
[产品代码] varchar(1000) null,
[产品名称] varchar(100) null,
[产品规格] varchar(150) null,
[产品单位编码] varchar(60) null,
[产品单位名称] varchar(60) null,
[成本项目编码] varchar(60) null,
[成本项目名称] varchar(60) null,
[规格] varchar(150) null,
[单位编码] varchar(60) null,
[单位名称] varchar(60) null,
[税率] decimal(18, 6) default 0,
[预算总耗用额（元）] decimal(18, 6) default 0,
[单位考核含税成本（元/瓶）] decimal(18, 6) default 0,
[处方量(瓶)] decimal(18, 6) default 0,
[损耗率%] decimal(18, 6) default 0,
[实际单价] decimal(18, 6) default 0,
[含税价] decimal(18, 6) default 0,
[生产入库数量] decimal(18, 6) default 0,
[产品总成本] decimal(18, 6) default 0,
[产品单位加权成本] decimal(18, 6) default 0,
[单位含税成本] decimal(18, 6) default 0,
[计划耗额] decimal(18, 6) default 0,
[实际领用量] decimal(18, 6) default 0,
[实际耗额] decimal(18, 6) default 0,
[平衡] decimal(18, 6) default 0,
[总生产入库数量] decimal(18, 6) default 0,
[总产品总成本] decimal(18, 6) default 0,
[总产品单位加权成本] decimal(18, 6) default 0,
[总单位含税成本] decimal(18, 6) default 0,
[总计划耗额] decimal(18, 6) default 0,
[总实际领用量] decimal(18, 6) default 0,
[总实际耗额] decimal(18, 6) default 0,
[总平衡] decimal(18, 6) default 0
)
CREATE TABLE #KD_CBA (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[产品代码] varchar(1000) null,
[产品名称] varchar(100) null,
[产品规格] varchar(150) null,
[产品单位编码] varchar(60) null,
[产品单位名称] varchar(60) null,
[生产入库数量] decimal(18, 6) default 0,
[产品总成本] decimal(18, 6) default 0
)
CREATE TABLE #KD_CBB (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[产品代码] varchar(1000) null,
[产品名称] varchar(100) null,
[产品规格] varchar(150) null,
[产品单位编码] varchar(60) null,
[产品单位名称] varchar(60) null,
[成本项目编码] varchar(60) null,
[成本项目名称] varchar(60) null,
[规格] varchar(150) null,
[单位编码] varchar(60) null,
[单位名称] varchar(60) null,
[预算数量] decimal(18, 6) default 0,
[预算总耗用额（元）] decimal(18, 6) default 0,
[单位考核含税成本（元/瓶）] decimal(18, 6) default 0,
[处方量(瓶)] decimal(18, 6) default 0,
[损耗率%] decimal(18, 6) default 0)
CREATE TABLE #hsd_XS (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[车间] varchar(60) null,
[产线] varchar(60) null,
[生产车间编码] varchar(60) null,
[生产车间名称] varchar(60) null,
[产品代码] varchar(60) null,
[产品名称] varchar(60) null,
[规格] varchar(60) null,
[单位编码] varchar(60) null,
[单位名称] varchar(60) null,
[上年度实际成本(瓶/支)] decimal(18, 6) default 0,
[本月产量(瓶/支)] decimal(18, 6) default 0,
[本月产量(瓶/盒)] decimal(18, 6) default 0,
[产品产量] decimal(18, 6) default 0,
[产线产量] decimal(18, 6) default 0,
[车间产量] decimal(18, 6) default 0,
[总产量] decimal(18, 6) default 0,
[产品产值] decimal(18, 6) default 0,
[产线产值] decimal(18, 6) default 0,
[车间产值] decimal(18, 6) default 0,
[总产值] decimal(18, 6) default 0,
[产线比重] decimal(18, 6) default 0,
[车间比重] decimal(18, 6) default 0,
[总比重] decimal(18, 6) default 0,
[直接人工产线] decimal(18, 6) default 0,
[直接人工车间] decimal(18, 6) default 0,
[直接人工公摊] decimal(18, 6) default 0,
[标准人工] decimal(18, 6) default 0,
[产量工资] decimal(18, 6) default 0,
[差额] decimal(18, 6) default 0,
[产线分摊] decimal(18, 6) default 0,
[车间分摊] decimal(18, 6) default 0,
[总共用分摊] decimal(18, 6) default 0,
[工资合计] decimal(18, 6) default 0,
[水总额] decimal(18, 6) default 0,
[电总额] decimal(18, 6) default 0,
[汽总额] decimal(18, 6) default 0,
[水] decimal(18, 6) default 0,
[电] decimal(18, 6) default 0,
[汽] decimal(18, 6) default 0,
[药检费车间总额] decimal(18, 6) default 0,
[药检费公摊总额] decimal(18, 6) default 0,
[明确药检费] decimal(18, 6) default 0,
[不明确药检费] decimal(18, 6) default 0,
[药检费合计] decimal(18, 6) default 0,
[维修费车间总额] decimal(18, 6) default 0,
[维修费公摊总额] decimal(18, 6) default 0,
[明确维修费] decimal(18, 6) default 0,
[不明确维修费] decimal(18, 6) default 0,
[维修费合计] decimal(18, 6) default 0,
[低耗费车间总额] decimal(18, 6) default 0,
[低耗费公摊总额] decimal(18, 6) default 0,
[明确低耗费] decimal(18, 6) default 0,
[不明确低耗费] decimal(18, 6) default 0,
[低耗费合计] decimal(18, 6) default 0,
[折旧车间总额] decimal(18, 6) default 0,
[折旧公摊总额] decimal(18, 6) default 0,
[明确折旧] decimal(18, 6) default 0,
[不明确折旧] decimal(18, 6) default 0,
[折旧合计] decimal(18, 6) default 0,
[车间管理费车间总额] decimal(18, 6) default 0,
[车间管理费公摊总额] decimal(18, 6) default 0,
[明确车间管理费] decimal(18, 6) default 0,
[不明确车间管理费] decimal(18, 6) default 0,
[车间管理费合计] decimal(18, 6) default 0,
[制造费用小计] decimal(18, 6) default 0
)
CREATE TABLE #hsd_qty (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[车间] varchar(60) null,
[产品产量] decimal(18, 6) default 0
)
CREATE TABLE #hsd_qtya (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[产线] varchar(60) null,
[产品产量] decimal(18, 6) default 0
)
CREATE TABLE #hsd_dwcb (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[产品代码] varchar(60) null,
[产品名称] varchar(60) null,
[单价] decimal(18, 6) default 0,
[数量] decimal(18, 6) default 0,
[金额] decimal(18, 6) default 0
)
CREATE TABLE #hsd_ylcba (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[小分组编码] varchar(60) null,
[分组编码] varchar(60) null,
[分组名称] varchar(60) null,
[原料代码] varchar(60) null,
[原料名称] varchar(60) null,
[产品代码] varchar(60) null,
[产品名称] varchar(60) null,
[数量] decimal(18, 6) default 0,
[金额] decimal(18, 6) default 0
)
CREATE TABLE #hsd_sdqa (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[部门代码] varchar(60) null,
[部门名称] varchar(60) null,
[项目] varchar(60) null,
[科目代码] varchar(60) null,
[科目名称] varchar(60) null,
[金额] decimal(18, 6) default 0
)
CREATE TABLE #hsd_fya (
[公司编码] varchar(60) null,
[公司名称] varchar(60) null,
[年] varchar(60) null,
[月] varchar(60) null,
[部门代码] varchar(60) null,
[部门名称] varchar(60) null,
[车间名称] varchar(60) null,
[产线名称] varchar(60) null,
[项目] varchar(60) null,
[类型编码] varchar(60) null,
[科目代码] varchar(60) null,
[科目名称] varchar(60) null,
[金额] decimal(18, 6) default 0
)
CREATE TABLE #hsd_rate (
[产品代码] varchar(60) null,
[税率] decimal(18, 6) default 0
)
CREATE TABLE #hsd_taxprice (
[采购日期] datetime,
[产品代码] varchar(60) null,
[产品名称] varchar(60) null,
[含税单价] decimal(18, 6) default 0
)
–费用均摊计算过程
–基表计算
insert into #hsd_XS([公司编码],[公司名称],[年],[月],[车间],[产线],[生产车间编码],[生产车间名称],[产品代码],[产品名称],[规格],[单位编码],[单位名称],[本月产量(瓶/支)],[产品产量])
select
gs.fnumber gano,gsa.fname gsname,str(year(b.fdate))year,str(month(b.fdate)) mon,
case when dep.fnumber in (‘20.0026’) then ‘中药提取车间’ when dep.fnumber in (‘20.0062’,‘20.0063’,‘20.0064’,‘20.0065’,‘20.0066’,‘20.0067’,‘20.0068’,‘20.0069’,‘20.0076’,‘20.0031’,‘20.0056’) then ‘输液车间’ when dep.fnumber in (‘20.0070’,‘20.0083’,‘20.0030’) then ‘口服液车间’ else ‘公共车间’ end cjname,
case when dep.fnumber in (‘20.0062’,‘20.0069’) then ‘玻瓶生产线’ when dep.fnumber in (‘20.0066’,‘20.0068’) then ‘塑瓶生产线’ when dep.fnumber in (‘20.0067’) then ‘塑瓶直立袋软袋生产线’ when dep.fnumber in (‘20.0070’) then ‘口服液线’ else ‘无’ end scxname,
dep.fnumber cxno,dept.fname cxname,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh ,unit.fnumber,unita.fname,sum(a.FBaseRealQty) baseqty,sum(a.FRealQty) qty
from T_PRD_INSTOCKENTRY a
left join T_PRD_INSTOCK b on a.fid =b.fid left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID
left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=a.FWorkShopId
left join T_BD_DEPARTMENT dep on dep.FDEPTID=a.FWorkShopId
left join T_BD_Material_L wl on a.FMaterialId=wl.FMATERIALID left join T_BD_Material wla on a.FMaterialId=wla.FMATERIALID
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FPRDORGID left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FPRDORGID and gsa.FNAME like ‘%华世%’
where b.fdate>='2023-01-01’and b.fdate<=‘2023-11-30’ and b.FPRDORGID like ‘1%’
group by gs.fnumber,gsa.fname,str(year(b.fdate)),str(month(b.fdate)),dep.fnumber,dept.fname,wla.fnumber,wl.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname
–插入车间产量
insert into #hsd_qty([公司编码],[公司名称],[年],[月],[车间],[产品产量])
select [公司编码],[公司名称],[年],[月],[车间] cj,sum([产品产量]) cl from #hsd_XS group by [公司编码],[公司名称],[年],[月],[车间]
update #hsd_XS set [车间产量]= (select [产品产量] from #hsd_qty where #hsd_XS.[车间]=#hsd_qty.[车间] and #hsd_XS.[公司编码]=#hsd_qty.[公司编码] and #hsd_XS.[年]=#hsd_qty.[年] and #hsd_XS.[月]=#hsd_qty.[月])
–插入产线产量
insert into #hsd_qtya([公司编码],[公司名称],[年],[月],[产线],[产品产量])
select [公司编码],[公司名称],[年],[月],[产线] cx,sum([产品产量]) cl from #hsd_XS group by [公司编码],[公司名称],[年],[月],[产线]
update #hsd_XS set [产线产量]= (select [产品产量] from #hsd_qtya where #hsd_XS.[产线]=#hsd_qtya.[产线] and #hsd_XS.[公司编码]=#hsd_qtya.[公司编码] and #hsd_XS.[年]=#hsd_qtya.[年] and #hsd_XS.[月]=#hsd_qtya.[月])
–插入公司总产量
update #hsd_XS set [总产量]= (select sum([产品产量]) from #hsd_qty where #hsd_XS.[公司编码]=#hsd_qty.[公司编码] and #hsd_XS.[年]=#hsd_qty.[年] and #hsd_XS.[月]=#hsd_qty.[月])
–插入年初单位成本
insert into #hsd_dwcb([公司编码], [公司名称],[年],[月], [产品代码], [产品名称],[单价],[数量],[金额])
select gs.fnumber,gsa.fname,cha.fyear year,cha.FPERIOD mon ,wla.fnumber wlno,wl.fname wlname,case when isnull(sum(ch.FQTY),0)=0 then 0 else sum(ch.FAMOUNT)/sum(ch.fqty) end price,sum(ch.fqty) qty,sum(ch.FAMOUNT) amount
from T_HS_STOCKDIMENSION chb left join t_hs_balance_h ch on chb.FENTRYID=ch.FDIMEENTRYID
left join T_HS_OUTACCTG cha on cha.fid=ch.fid left join T_BD_Material_L wl on chb.FMASTERID=wl.FMATERIALID
left join T_BD_Material wla on chb.FMASTERID=wla.FMATERIALID left join t_BD_Stock kf on kf.FMASTERID=chb.FSTOCKID
left join T_HS_CALDIMENSIONS org on cha.FDIMENSIONID=org.FDIMENSIONID left join T_ORG_ORGANIZATIONS gs on gs.FORGID=org.FFINORGID left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=org.FFINORGID and gsa.FNAME like ‘%华世%’
where cha.fyear=2023 and cha.FPERIOD=1 and ( wla.fnumber like ‘10%’ or wla.fnumber like ‘15%’ or wla.fnumber like ‘20%’) and org.FFINORGID=1 and gsa.FNAME is not null and ch.FEndInitKey=0
group by gs.fnumber,gsa.fname,cha.fyear ,cha.FPERIOD ,wla.fnumber ,wl.fname
–计算产值
update #hsd_XS set [上年度实际成本(瓶/支)] =(select isnull([单价],0) from #hsd_dwcb where #hsd_XS.[产品代码]=#hsd_dwcb.[产品代码] and #hsd_XS.[公司编码]=#hsd_dwcb.[公司编码] )
update #hsd_XS set [产品产值]=[产品产量][上年度实际成本(瓶/支)]
update #hsd_XS set [产线产值]=[产线产量][上年度实际成本(瓶/支)]
update #hsd_XS set [车间产值]=[车间产量][上年度实际成本(瓶/支)]
update #hsd_XS set [总产值]=[总产量][上年度实际成本(瓶/支)]
–计算分摊比例
update #hsd_XS set [产线比重]=[产品产值]/[产线产值] where [产线产值]<> 0
update #hsd_XS set [车间比重]=[产品产值]/[车间产值] where [车间产值]<> 0
update #hsd_XS set [总比重]=[产品产值]/[总产值] where [总产值]<> 0
—插入水电气费用
insert into #hsd_sdqa([公司编码], [公司名称],[年], [月], [部门代码],[部门名称],[项目],[科目代码],[科目名称],[金额])
select gs.fnumber gsno,gsa.fname gsname,pz.fyear year,pz.FPERIOD mon, dep.fnumber bmno,acct.FNAME bmname,case when substring(accta.fnumber,0,14)=‘5001.01.03.01’ then ‘水’ when substring(accta.fnumber,0,14)=‘5001.01.03.02’ then ‘电’ else ‘气’ end xm, substring(accta.fnumber,0,14) acctno,acct.FNAME acctname,sum(a.FDEBIT) amount
from T_GL_VOUCHERENTRY a left join T_GL_VOUCHER pz on a.FVOUCHERID=pz.FVOUCHERID left join T_BD_FLEXITEMPROPERTY b on a.FDetailID=b.fid
left join T_BD_FLEXITEMDETAILV c on c.fid=a.FDETAILID left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=c.FFLEX5 left join T_BD_DEPARTMENT dep on dep.FDEPTID=c.FFLEX5
left join T_BD_ACCOUNT_L acct on acct.FACCTID=a.FACCOUNTID and acct.FLOCALEID=‘2052’ left join T_BD_ACCOUNT accta on accta.FACCTID=a.FACCOUNTID
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=pz.FACCTORGID left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=pz.FACCTORGID and gsa.FNAME like ‘%华世%’
where pz.fdate>='2023-01-01’and pz.fdate<=‘2023-11-30’ and pz.FACCTORGID=1 and( accta.FNUMBER like ‘5001.01.03.01%’ or accta.FNUMBER like ‘5001.01.03.02%’ or accta.FNUMBER like ‘5001.01.03.03%’)
group by gs.fnumber,gsa.fname,pz.fyear,pz.FPERIOD,dep.fnumber,dept.fname,substring(accta.fnumber,0,14),acct.FNAME
update #hsd_sdqa set [部门名称]=‘中药提取车间’ where [部门名称]=‘提取车间’
update #hsd_XS set [水总额]=(select isnull(sum( [金额]),0) from #hsd_sdqa where #hsd_XS.[车间]=#hsd_sdqa.[部门名称] and #hsd_sdqa.[项目]=‘水’ and #hsd_XS.[月]=str(#hsd_sdqa.[月]) )
update #hsd_XS set [电总额]=(select isnull(sum([金额]),0) from #hsd_sdqa where #hsd_XS.[车间]=#hsd_sdqa.[部门名称] and #hsd_XS.[公司编码]=#hsd_sdqa.[公司编码] and #hsd_sdqa.[项目]=‘电’ and #hsd_XS.[月]=str(#hsd_sdqa.[月]) )
update #hsd_XS set [汽总额]=(select isnull(sum([金额]),0) from #hsd_sdqa where #hsd_XS.[车间]=#hsd_sdqa.[部门名称] and #hsd_XS.[公司编码]=#hsd_sdqa.[公司编码] and #hsd_sdqa.[项目]=‘气’ and #hsd_XS.[月]=str(#hsd_sdqa.[月]))
update #hsd_XS set [水]=[水总额][车间比重]
update #hsd_XS set [电]=[电总额][车间比重]
update #hsd_XS set [汽]=[汽总额]*[车间比重]
—计算费用
insert into #hsd_fya([公司编码], [公司名称],[年], [月], [部门代码], [部门名称],[车间名称],[产线名称],[项目],[类型编码],[科目代码], [科目名称],[金额])
select gs.fnumber gsno,gsa.fname gsname,pz.fyear year,pz.FPERIOD mon, dep.fnumber bmno,dept.FNAME bmname,
case when dep.fnumber in (‘20.0026’) then ‘中药提取车间’ when dep.fnumber in (‘20.0062’,‘20.0063’,‘20.0064’,‘20.0065’,‘20.0066’,‘20.0067’,‘20.0068’,‘20.0069’,‘20.0076’,‘20.0031’,‘20.0056’) then ‘输液车间’ when dep.fnumber in (‘20.0070’,‘20.0083’,‘20.0030’) then ‘口服液车间’ else ‘公共车间’ end cjname,
case when dep.fnumber in (‘20.0062’,‘20.0069’) then ‘玻瓶生产线’ when dep.fnumber in (‘20.0066’,‘20.0068’) then ‘塑瓶生产线’ when dep.fnumber in (‘20.0067’) then ‘塑瓶直立袋软袋生产线’ when dep.fnumber in (‘20.0070’) then ‘口服液线’ else ‘无’ end scxname,
case when accta.fnumber in (‘5001.01.04’,‘5101.05.08’,‘5101.05.26’) then ‘直接人工’ when (accta.fnumber like ‘5101.01%’ and accta.fnumber not like ‘5101.01.08%’) then ‘药检费’ when (accta.fnumber like ‘5101.03%’ or accta.fnumber like ‘5101.01.08%’) then ‘维修费’ when (accta.fnumber like ‘5101.04%’ ) then ‘低耗费’ when (accta.fnumber like ‘5101.02%’ ) then ‘折旧’ when (accta.fnumber like ‘5101.05%’ and accta.fnumber not like ‘5101.05.08%’ and accta.fnumber not like ‘5101.05.26%’) then '车间管理费用’else ‘其他’ end xm,
substring(accta.fnumber,0,8) typeno,accta.fnumber acctno,acct.FNAME acctname,sum(a.FDEBIT) amount
from T_GL_VOUCHERENTRY a left join T_GL_VOUCHER pz on a.FVOUCHERID=pz.FVOUCHERID left join T_BD_FLEXITEMPROPERTY b on a.FDetailID=b.fid
left join T_BD_FLEXITEMDETAILV c on c.fid=a.FDETAILID left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=c.FFLEX5 left join T_BD_DEPARTMENT dep on dep.FDEPTID=c.FFLEX5
left join T_BD_ACCOUNT_L acct on acct.FACCTID=a.FACCOUNTID and acct.FLOCALEID=‘2052’ left join T_BD_ACCOUNT accta on accta.FACCTID=a.FACCOUNTID
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=pz.FACCTORGID left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=pz.FACCTORGID and gsa.FNAME like ‘%华世%’
where pz.fdate>='2023-01-01’and pz.fdate<=‘2023-11-30’ and pz.FACCTORGID=1 and( accta.FNUMBER like ‘5001.01.04%’ or accta.FNUMBER like ‘5101.01%’ or accta.FNUMBER like ‘5101.03%’ or accta.FNUMBER like ‘5101.04%’ or accta.FNUMBER like ‘5101.02%’ or accta.FNUMBER like ‘5101.05%’)
group by gs.fnumber,gsa.fname,pz.fyear,pz.FPERIOD,dep.fnumber,dept.fname,substring(accta.fnumber,0,8),accta.fnumber,acct.FNAME
–更新人工工资
update #hsd_XS set [直接人工产线]=(select isnull(sum( [金额]),0) from #hsd_fya where #hsd_XS.[产线]=#hsd_fya.[产线名称] and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘直接人工’ and #hsd_XS.[月]=str(#hsd_fya.[月]) )
update #hsd_XS set [直接人工车间]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_XS.[车间]=#hsd_fya.[车间名称] and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘直接人工’ and #hsd_fya.[产线名称]=‘无’ and #hsd_XS.[月]=str(#hsd_fya.[月]) )
update #hsd_XS set [直接人工公摊]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_fya.[车间名称]=‘公共车间’ and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘直接人工’ and #hsd_XS.[月]=str(#hsd_fya.[月]) )
update #hsd_XS set [产线分摊]=[直接人工产线][产线比重]
update #hsd_XS set [车间分摊]=[直接人工车间][车间比重]
update #hsd_XS set [总共用分摊]=[直接人工公摊]*[总比重]
update #hsd_XS set [工资合计]=[产线分摊]+[车间分摊]+[总共用分摊]
–更新药检费
update #hsd_XS set [药检费车间总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_XS.[车间]=#hsd_fya.[车间名称] and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘药检费’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [维修费公摊总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_fya.[车间名称]=‘公共车间’ and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘药检费’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [明确药检费]=[药检费车间总额][车间比重]
update #hsd_XS set [不明确药检费]=[维修费公摊总额][总比重]
update #hsd_XS set [药检费合计]=[明确药检费]+[不明确药检费]
–更新维修费
update #hsd_XS set [维修费车间总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_XS.[车间]=#hsd_fya.[车间名称] and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘维修费’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [维修费公摊总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_fya.[车间名称]=‘公共车间’ and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘维修费’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [明确维修费]=[维修费车间总额][车间比重]
update #hsd_XS set [不明确维修费]=[维修费公摊总额][总比重]
update #hsd_XS set [维修费合计]=[明确维修费]+[不明确维修费]
–更新低耗费
update #hsd_XS set [低耗费车间总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_XS.[车间]=#hsd_fya.[车间名称] and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘低耗费’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [低耗费公摊总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_fya.[车间名称]=‘公共车间’ and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘低耗费’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [明确低耗费]=[低耗费车间总额][车间比重]
update #hsd_XS set [不明确低耗费]=[低耗费公摊总额][总比重]
update #hsd_XS set [低耗费合计]=[明确低耗费]+[不明确低耗费]
–更新折旧费
update #hsd_XS set [折旧车间总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_XS.[车间]=#hsd_fya.[车间名称] and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘折旧’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [折旧公摊总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_fya.[车间名称]=‘公共车间’ and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘折旧’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [明确折旧]=[折旧车间总额][车间比重]
update #hsd_XS set [不明确折旧]=[折旧公摊总额][总比重]
update #hsd_XS set [折旧合计]=[明确折旧]+[不明确折旧]
--更新车间管理费
update #hsd_XS set [车间管理费车间总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_XS.[车间]=#hsd_fya.[车间名称] and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘车间管理费用’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [车间管理费公摊总额]=(select isnull(sum([金额]),0) from #hsd_fya where #hsd_fya.[车间名称]=‘公共车间’ and #hsd_XS.[公司编码]=#hsd_fya.[公司编码] and #hsd_fya.[项目]=‘车间管理费用’ and #hsd_XS.[月]=str(#hsd_fya.[月]) and #hsd_XS.[年]=str(#hsd_fya.[年]) )
update #hsd_XS set [明确车间管理费]=[车间管理费车间总额][车间比重]
update #hsd_XS set [不明确车间管理费]=[车间管理费公摊总额][总比重]
update #hsd_XS set [车间管理费合计]=[明确车间管理费]+[不明确车间管理费]
–列转行
insert into #KD_CB ([公司编码],[公司名称],[年],[月],[产品代码],[产品名称],[成本项目名称],[实际耗额])
SELECT [公司编码],[公司名称],[年],[月],[产品代码],[产品名称],[成本项目名称],[实际耗额]
FROM
(
select [公司编码],[公司名称],[年],[月],[产品代码],[产品名称],[水],[电],[汽],[工资合计],[药检费合计],[维修费合计],[低耗费合计],[折旧合计],[车间管理费合计] from #hsd_XS
)T
UNPIVOT
(
[实际耗额] FOR [成本项目名称] IN
([水],[电],[汽],[工资合计],[药检费合计],[维修费合计],[低耗费合计],[折旧合计],[车间管理费合计] )
) P
–更新项目名称
update #KD_CB set [成本项目名称]=‘直接人工’,[成本项目编码]=‘01’ where [成本项目名称]=‘工资合计’
update #KD_CB set [成本项目名称]=‘药检费’,[成本项目编码]=‘05’ where [成本项目名称]=‘药检费合计’
update #KD_CB set [成本项目名称]=‘维修费’,[成本项目编码]=‘06’ where [成本项目名称]=‘维修费合计’
update #KD_CB set [成本项目名称]=‘低耗费’,[成本项目编码]=‘07’ where [成本项目名称]=‘低耗费合计’
update #KD_CB set [成本项目名称]=‘车间管理费用’,[成本项目编码]=‘09’ where [成本项目名称]=‘车间管理费合计’
update #KD_CB set [成本项目名称]=‘折旧’,[成本项目编码]=‘08’ where [成本项目名称]=‘折旧合计’
update #KD_CB set [成本项目编码]=‘02’ where [成本项目名称]=‘水’
update #KD_CB set [成本项目编码]=‘03’ where [成本项目名称]=‘电’
update #KD_CB set [成本项目编码]=‘04’ where [成本项目名称]=‘汽’
–计算本年实际领用及原材料耗额，插入基表
insert into #KD_CB ([公司编码],[公司名称],[年],[月],[存货类别编码],[存货类别名称],[成本项目编码],[成本项目名称],[产品代码],[产品名称],[规格],[单位编码],[单位名称],[实际领用量],[实际耗额])
select gs.fnumber gsno,gsa.fname gsname,str(year(b.fdate))year,str(month(b.fdate)) mon,fz.FNUMBER fzno,fza.fname fzname,wla.fnumber wlno,wl.fname wlname,wld.fnumber cpno,wlc.fname cpname,wl.FSPECIFICATION ggxh,unit.fnumber unitno,unita.fname unitname,sum(a.FActualQty) qty,sum(a.FAmount) amount
from T_PRD_PICKMTRLDATA a left join T_PRD_PICKMTRL b on a.fid =b.fid left join T_BD_Material_L wl on a.FMATERIALID=wl.FMATERIALID left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_Material wla on a.FMATERIALID=wla.FMATERIALID left join T_PRD_MOENTRY dden on dden.FENTRYID=a.FMOENTRYID
left join T_BD_Material_L wlc on dden.FMATERIALID=wlc.FMATERIALID left join T_BD_Material wld on dden.FMATERIALID=wld.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FPRDORGID left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FPRDORGID and gsa.FNAME like ‘%华世%’
where b.fdate>='2023-01-01’and b.fdate<=‘2023-11-30’ and b.FPRDORGID like ‘1%’
group by gs.fnumber,gsa.fname,str(year(fdate)),str(month(fdate)),fz.FNUMBER,fza.FName,wla.fnumber,wl.fname,wld.fnumber,wlc.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname
–更新产品产量和成本
insert into #KD_CBA ([公司编码],[公司名称], [年], [月],[产品代码], [产品名称],[产品规格],[产品单位编码],[产品单位名称],[生产入库数量],[产品总成本])
select
gs.fnumber gano,gsa.fname gsname,str(year(b.fdate))year,str(month(b.fdate)) mon,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh,unit.fnumber unitno,unita.fname unitname,sum(a.FBaseRealQty) scrkbaseqty,sum(a.FAMOUNT) scrkamount
from T_PRD_INSTOCKENTRY a
left join T_PRD_INSTOCK b on a.fid =b.fid left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=a.FWorkShopId
left join T_BD_DEPARTMENT dep on dep.FDEPTID=a.FWorkShopId
left join T_BD_Material_L wl on a.FMaterialId=wl.FMATERIALID left join T_BD_Material wla on a.FMaterialId=wla.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FPRDORGID left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FPRDORGID and gsa.FNAME like ‘%华世%’
where b.fdate>=‘2023-01-01’ and b.fdate<='2023-11-30’and b.FPRDORGID like ‘1%’
group by gs.fnumber,gsa.fname,str(year(b.fdate)),str(month(b.fdate)),wla.fnumber,wl.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname
update #KD_CB set [产品规格]=(select [产品规格] from #KD_CBA where #KD_CBA.[公司编码]=#KD_CB.[公司编码] and #KD_CBA.[年]=#KD_CB.[年] and #KD_CBA.[月]=#KD_CB.[月] and #KD_CBA.[产品代码]=#KD_CB.[产品代码])
update #KD_CB set [产品单位编码]=(select [产品单位编码] from #KD_CBA where #KD_CBA.[公司编码]=#KD_CB.[公司编码] and #KD_CBA.[年]=#KD_CB.[年] and #KD_CBA.[月]=#KD_CB.[月] and #KD_CBA.[产品代码]=#KD_CB.[产品代码])
update #KD_CB set [产品单位名称]=(select [产品单位名称] from #KD_CBA where #KD_CBA.[公司编码]=#KD_CB.[公司编码] and #KD_CBA.[年]=#KD_CB.[年] and #KD_CBA.[月]=#KD_CB.[月] and #KD_CBA.[产品代码]=#KD_CB.[产品代码])
update #KD_CB set [生产入库数量]=(select [生产入库数量] from #KD_CBA where #KD_CBA.[公司编码]=#KD_CB.[公司编码] and #KD_CBA.[年]=#KD_CB.[年] and #KD_CBA.[月]=#KD_CB.[月] and #KD_CBA.[产品代码]=#KD_CB.[产品代码])
update #KD_CB set [产品总成本]=(select [产品总成本] from #KD_CBA where #KD_CBA.[公司编码]=#KD_CB.[公司编码] and #KD_CBA.[年]=#KD_CB.[年] and #KD_CBA.[月]=#KD_CB.[月] and #KD_CBA.[产品代码]=#KD_CB.[产品代码])
–计算并插入预算单耗
insert into #KD_CBB ([年],[产品代码], [产品名称],[产品单位编码],[产品单位名称],[规格],[成本项目编码],[成本项目名称],[单位考核含税成本（元/瓶）],[预算数量],[预算总耗用额（元）],[处方量(瓶)],[损耗率%])
select
temp.year [年度],
temp.wlno [产品编码],
temp.wlname [产品名称],
temp.unitno [计量单位编码],
temp.unitname [计量单位名称],
temp.ggxh [规格型号],
temp.xmno [项目编码],
temp.xmname [项目名称],
temp.price [单耗],
temp.qty [预算数量],
temp.amount [预算金额],
temp.cfqty [处方量],
temp.shl [损耗率]
from
(
select
‘生产费用’ type,
str(year(a.F_BGP_DATE)) year,
xm.fnumber xmno,
xma.fname xmname,
wla.fnumber wlno,
wl.fname wlname,
unit.fnumber unitno,
unita.fname unitname,
wl.FSPECIFICATION ggxh,
b.price price,
b.qty qty,
b.price*b.qty amount,
0 cfqty,
0 shl
from BGP_t_Cust100014 a left join cr_xmysentry b on a.fid=b.fid left join T_BD_Material_L wl on a.wl=wl.FMATERIALID
left join T_BD_Material wla on a.wl=wla.FMATERIALID left join T_BD_MATERIALBASE wld on wld.FENTRYID=wl.FPKID
left join T_BD_UNIT unit on wld.FBASEUNITID=unit.FUNITID left join T_BD_UNIT_l unita on wld.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052 left join BGP_t_Cust100012 xm on xm.fid=b.xm left join BGP_t_Cust100012_L xma on xma.fid=b.xm
union all
select
‘物料消耗’ type,
str(year(a.F_BGP_DATE)) year,
xm.fnumber xmno,
xma.fname xmname,
wla.fnumber wlno,
wl.fname wlname,
unit.fnumber unitno,
unita.fname unitname,
wl.FSPECIFICATION ggxh,
case when b.cfqty =0 then 0 else b.YSAMOUNT/b.cfqty end price,
b.ysqty qty,
b.YSAMOUNT amount,
b.cfqty cfqty,
b.shl shl
from BGP_t_Cust100011 a left join ct_cpys_Entry b on a.fid=b.fid left join T_BD_Material_L wl on a.cp=wl.FMATERIALID
left join T_BD_Material wla on a.cp=wla.FMATERIALID left join T_BD_MATERIALBASE wld on wld.FENTRYID=wl.FPKID
left join T_BD_UNIT unit on wld.FBASEUNITID=unit.FUNITID left join T_BD_UNIT_l unita on wld.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_Material xm on xm.FMATERIALID=b.item left join T_BD_Material_L xma on xma.FMATERIALID=b.item
) temp
where temp.wlno is not null
update #KD_CBB set [年]=‘2023’
update #KD_CBB set [公司编码]=20
update #KD_CB set [单位考核含税成本（元/瓶）]=(select isnull([单位考核含税成本（元/瓶）],0) from #KD_CBB where #KD_CBB.[公司编码]=#KD_CB.[公司编码] and #KD_CBB.[成本项目编码]=#KD_CB.[成本项目编码] and #KD_CBB.[产品代码]=#KD_CB.[产品代码])
update #KD_CB set [预算总耗用额（元）]=(select isnull([预算总耗用额（元）],0) from #KD_CBB where #KD_CB.[公司编码]=#KD_CB.[公司编码] and #KD_CBB.[成本项目编码]=#KD_CB.[成本项目编码] and #KD_CBB.[产品代码]=#KD_CB.[产品代码])
update #KD_CB set [处方量(瓶)]=(select isnull([处方量(瓶)],0) from #KD_CBB where #KD_CB.[公司编码]=#KD_CB.[公司编码] and #KD_CBB.[成本项目编码]=#KD_CB.[成本项目编码] and #KD_CBB.[产品代码]=#KD_CB.[产品代码])
update #KD_CB set [损耗率%]=(select isnull([损耗率%],0) from #KD_CBB where #KD_CB.[公司编码]=#KD_CB.[公司编码] and #KD_CBB.[成本项目编码]=#KD_CB.[成本项目编码] and #KD_CBB.[产品代码]=#KD_CB.[产品代码])
update #KD_CB set [产品总成本]=(select isnull([单位考核含税成本（元/瓶）],0) from #KD_CBB where #KD_CB.[公司编码]=#KD_CB.[公司编码] and #KD_CBB.[成本项目编码]=#KD_CB.[成本项目编码] and #KD_CBB.[产品代码]=#KD_CB.[产品代码])
–空值处理
update #KD_CB set [单位考核含税成本（元/瓶）]= isnull([单位考核含税成本（元/瓶）],0) where [单位考核含税成本（元/瓶）] is null
update #KD_CB set [预算总耗用额（元）]= isnull([预算总耗用额（元）],0) where [预算总耗用额（元）] is null
update #KD_CB set [处方量(瓶)]= isnull([处方量(瓶)],0) where [处方量(瓶)] is null
update #KD_CB set [损耗率%]=isnull([损耗率%],0) where [损耗率%] is null
update #KD_CB set [产品总成本]=isnull([产品总成本],0) where [产品总成本] is null
update #KD_CB set [存货类别名称]=‘生产费用’,[存货类别编码]=‘80’ where [存货类别名称] is null
–计算物料税率
insert into #hsd_rate ([产品代码],[税率])
select wla.fnumber wlno,b.ftaxrate taxrate from T_BD_Material_L wl left join T_BD_Material wla on wl.FMaterialId=wla.FMATERIALID left join T_BD_MATERIALBASE a on a.FMaterialId=wla.FMATERIALID left join T_BD_TAXRATE b on a.FTaxRateId=b.fid left join T_BD_TAXRATE_L c on a.FTaxRateId=c.fid
group by wla.fnumber,b.ftaxrate
–更新税率
update #KD_CB set [税率]= (select isnull([税率],0) from #hsd_rate where #hsd_rate.[产品代码]=#KD_CB.[成本项目编码])
update #KD_CB set [税率]= isnull([税率],0)
–最近一次采购含税单价
insert into #hsd_taxprice([采购日期],[产品代码],[产品名称],[含税单价])
select
max(tempa.date) date,
tempa.wlno,
tempa.wlname,
max(tempa.price)
from
(
select
b.fdate date,gs.fnumber gano,gsa.fname gsname,wla.fnumber wlno,wl.fname wlname,wl.FSPECIFICATION ggxh,unit.fnumber unitno,unita.fname unitname,d.FTAXPRICE price
from T_STK_INSTOCKENTRY a left join T_STK_INSTOCKFIN c on a.fid=c.fid left join T_STK_INSTOCKENTRY_F d on d.FENTRYID=a.FENTRYID
left join T_STK_INSTOCK b on a.fid =b.fid left join T_BD_UNIT unit on a.FBaseUnitId=unit.FUNITID left join T_BD_UNIT_l unita on a.FBaseUnitId=unita.FUNITID and unita.FLOCALEID=2052
left join T_BD_DEPARTMENT_L dept on dept.FDEPTID=b.FStockDeptId
left join T_BD_DEPARTMENT dep on dep.FDEPTID=b.FStockDeptId
left join T_BD_Material_L wl on a.FMaterialId=wl.FMATERIALID left join T_BD_Material wla on a.FMaterialId=wla.FMATERIALID
left join T_BD_MATERIALGROUP fz on substring(wla.fnumber,0,3)=fz.fnumber left join T_BD_MATERIALGROUP_L fza on fz.FID=fza.fid
left join T_ORG_ORGANIZATIONS gs on gs.FORGID=b.FPurchaseOrgId left join T_ORG_ORGANIZATIONS_l gsa on gsa.FORGID=b.FPurchaseOrgId and gsa.FNAME like ‘%华世%’
where b.fdate>=‘2023-01-01’ and b.fdate<='2023-11-30’and b.FPurchaseOrgId=1
)tempa
group by tempa.wlno,tempa.wlname
–更新含税价格，计算计划耗额
update #hsd_price set [含税单价]=(select isnull([含税单价],0) from #hsd_taxprice where #hsd_taxprice.[产品代码]=#KD_CB.[产品代码])
update #KD_CB set [含税价]=(select isnull([含税单价],0)from #hsd_taxprice where #hsd_taxprice.[产品代码]=#KD_CB.[成本项目编码])
update #KD_CB set [含税价]=isnull([含税价],0) where [含税价] is null
update #KD_CB set [计划耗额]=[处方量(瓶)](100+[损耗率%])/100[生产入库数量]*[含税价]
select * from #KD_CB