K3cloud成本计算Demo

破晓之翼

发布于 2023-12-02 10:40:34

1350

发布于 2023-12-02 10:40:34

文章被收录于专栏：产品能力产品能力

业务描述

用于制药品种法的成本计算，逻辑要点：1. 自定义开发人工单耗，计算人工工资的PV差异。 2. 制造费用根据产线、车间、公司 3种比例使用价值法进行分摊费用。 3. 材料按实际对应产品的成本进行计算，联查生产订单进行产品的定位。

SQL示例：

CREATE TABLE #KD_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, [换算] 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, [不明确折旧] 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 #CJ_qty (
    [车间]           varchar(60)    null,
	[产品产量]            decimal(18, 6) default 0
	)
			  CREATE TABLE #Cx_qty (
    [产线]           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_ylcb (
	[公司编码]           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_sdq (
	[公司编码]           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_fy (
	[公司编码]           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
	)


	  select *from #KD_XS 
	  drop table #CJ_qty 
	  drop table #KD_XS
	  drop table #Cx_qty
	  drop table #hsd_dwcb
	  drop table #hsd_ylcb
	  drop table #hsd_sdq
	   drop table #hsd_fy

–基表计算 insert into #KD_XS([公司编码],[公司名称],[车间],[产线],[生产车间编码],[生产车间名称],[产品代码],[产品名称],[规格],[单位编码],[单位名称],[本月产量(瓶/支)],[产品产量]) select gs.fnumber gano,gsa.fname gsname, 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 convert(varchar(12),b.fdate,120)>=‘2023-09-01’ and convert(varchar(12),b.fdate,120)<=‘2023-09-30’ and b.FPRDORGID like ‘1%’

group by gs.fnumber,gsa.fname,dep.fnumber,dept.fname,wla.fnumber,wl.fname,wl.FSPECIFICATION,unit.fnumber,unita.fname

–插入车间产量

insert into #CJ_qty([车间],[产品产量]) select [车间] cj,sum([产品产量]) cl from #KD_XS group by [车间]

update #KD_XS set [车间产量]= (select [产品产量] from #CJ_qty where #KD_XS.[车间]=#CJ_qty.[车间])

select *from #CJ_qty –插入产线产量

insert into #Cx_qty([产线],[产品产量]) select [产线] cx,sum([产品产量]) cl from #KD_XS group by [产线]

update #KD_XS set [产线产量]= (select [产品产量] from #Cx_qty where #KD_XS.[产线]=#Cx_qty.[产线]) –插入公司总产量 update #KD_XS set [总产量]= (select sum([产品产量]) from #Cx_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 group by gs.fnumber,gsa.fname,cha.fyear ,cha.FPERIOD ,wla.fnumber ,wl.fname

select * from #hsd_dwcb select * from #KD_XS

–计算产值 update #KD_XS set [上年度实际成本(瓶/支)] =(select [单价] from #hsd_dwcb where #KD_XS.[产品代码]=#hsd_dwcb.[产品代码] and #KD_XS.[公司编码]=#hsd_dwcb.[公司编码]) update #KD_XS set [产品产值]=[产品产量][上年度实际成本(瓶/支)] update #KD_XS set [产线产值]=[产线产量][上年度实际成本(瓶/支)] update #KD_XS set [车间产值]=[车间产量][上年度实际成本(瓶/支)] update #KD_XS set [总产值]=[总产量][上年度实际成本(瓶/支)]

–计算分摊比例

update #KD_XS set [产线比重]=[产品产值]/[产线产值] where [产线产值]<> 0 update #KD_XS set [车间比重]=[产品产值]/[车间产值] where [车间产值]<> 0 update #KD_XS set [总比重]=[产品产值]/[总产值] where [总产值]<> 0

–插入物料消耗成本

select * from #hsd_ylcb insert into #hsd_ylcb([公司编码],[公司名称],[小分组编码],[分组编码],[分组名称], [原料代码], [原料名称],[产品代码],[产品名称],[数量],[金额]) select gs.fnumber gsno,gsa.fname gsname,substring(wla.fnumber,0,9)fzzno,fz.FNUMBER fzno,fza.fname fzname,wla.fnumber wlno,wl.fname wlname,wld.fnumber cpno,wlc.fname cpname,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_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 convert(varchar(12),b.fdate,120)>=‘2023-09-01’ and convert(varchar(12),b.fdate,120)<=‘2023-09-30’ and b.FPRDORGID like ‘1%’ group by gs.fnumber,gsa.fname,fz.FNUMBER,fza.FName,wla.fnumber,wl.fname,wld.fnumber,wlc.fname

–更新原料成本 update #KD_XS set [原材料]=(select isnull(sum([金额]),0) from #hsd_ylcb where #KD_XS.[产品代码]=#hsd_ylcb.[产品代码] and #KD_XS.[公司编码]=#hsd_ylcb.[公司编码] and #hsd_ylcb.[分组编码]=‘11’ and #hsd_ylcb.[小分组编码] in (‘11.01.01’,‘11.02.01’) ) update #KD_XS set [辅料]=(select isnull(sum([金额]),0) from #hsd_ylcb where #KD_XS.[产品代码]=#hsd_ylcb.[产品代码] and #KD_XS.[公司编码]=#hsd_ylcb.[公司编码] and (#hsd_ylcb.[分组编码]=‘11’ and #hsd_ylcb.[小分组编码] in (‘11.01.02’,‘11.02.02’,‘11.02.03’) or #hsd_ylcb.[分组编码]=‘13’) ) update #KD_XS set [中药材]=(select isnull(sum([金额]),0) from #hsd_ylcb where #KD_XS.[产品代码]=#hsd_ylcb.[产品代码] and #KD_XS.[公司编码]=#hsd_ylcb.[公司编码] and #hsd_ylcb.[分组编码]=‘14’ ) update #KD_XS set [半成品]=(select isnull(sum( [金额]),0) from #hsd_ylcb where #KD_XS.[产品代码]=#hsd_ylcb.[产品代码] and #KD_XS.[公司编码]=#hsd_ylcb.[公司编码]and #hsd_ylcb.[分组编码]=‘15’ ) update #KD_XS set [浸膏]=(select isnull(sum([金额]),0) from #hsd_ylcb where #KD_XS.[产品代码]=#hsd_ylcb.[产品代码] and #KD_XS.[公司编码]=#hsd_ylcb.[公司编码] and #hsd_ylcb.[分组编码]=‘10’ ) update #KD_XS set [包材]=(select isnull(sum([金额]),0) from #hsd_ylcb where #KD_XS.[产品代码]=#hsd_ylcb.[产品代码] and #KD_XS.[公司编码]=#hsd_ylcb.[公司编码] and #hsd_ylcb.[分组编码]=‘12’ )

select * from #KD_XS

—插入水电气费用

insert into #hsd_sdq([公司编码], [公司名称],[年], [月], [部门代码],[部门名称],[项目],[科目代码],[科目名称],[金额]) 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 convert(varchar(12),pz.fdate,120)>=‘2023-09-01’ and convert(varchar(12),pz.fdate,120)<=‘2023-09-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_sdq set [部门名称]=‘中药提取车间’ where [部门名称]=‘提取车间’ update #KD_XS set [水总额]=(select isnull(sum( [金额]),0) from #hsd_sdq where #KD_XS.[车间]=#hsd_sdq.[部门名称] and #KD_XS.[公司编码]=#hsd_sdq.[公司编码] and #hsd_sdq.[项目]=‘水’ ) update #KD_XS set [电总额]=(select isnull(sum([金额]),0) from #hsd_sdq where #KD_XS.[车间]=#hsd_sdq.[部门名称] and #KD_XS.[公司编码]=#hsd_sdq.[公司编码] and #hsd_sdq.[项目]=‘电’ ) update #KD_XS set [汽总额]=(select isnull(sum([金额]),0) from #hsd_sdq where #KD_XS.[车间]=#hsd_sdq.[部门名称] and #KD_XS.[公司编码]=#hsd_sdq.[公司编码] and #hsd_sdq.[项目]=‘气’ ) update #KD_XS set [水]=[水总额][车间比重] update #KD_XS set [电]=[电总额][车间比重] update #KD_XS set [汽]=[汽总额]*[车间比重] select * from #hsd_sdq

—计算费用 insert into #hsd_fy([公司编码], [公司名称],[年], [月], [部门代码], [部门名称],[车间名称],[产线名称],[项目],[类型编码],[科目代码], [科目名称],[金额]) 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 convert(varchar(12),pz.fdate,120)>=‘2023-09-01’ and convert(varchar(12),pz.fdate,120)<=‘2023-09-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 #KD_XS set [直接人工产线]=(select isnull(sum( [金额]),0) from #hsd_fy where #KD_XS.[产线]=#hsd_fy.[产线名称] and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘直接人工工资’ ) update #KD_XS set [直接人工车间]=(select isnull(sum([金额]),0) from #hsd_fy where #KD_XS.[车间]=#hsd_fy.[车间名称] and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘直接人工工资’ ) update #KD_XS set [直接人工公摊]=(select isnull(sum([金额]),0) from #hsd_fy where #hsd_fy.[车间名称]=‘公共车间’ and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘直接人工工资’ ) update #KD_XS set [产线分摊]=[直接人工产线][产线比重] update #KD_XS set [车间分摊]=[直接人工车间][车间比重] update #KD_XS set [总共用分摊]=[直接人工公摊][总比重] update #KD_XS set [工资合计]=[产线分摊]+[车间分摊]+[总共用分摊] –更新药检费 update #KD_XS set [药检费车间总额]=(select isnull(sum([金额]),0) from #hsd_fy where #KD_XS.[车间]=#hsd_fy.[车间名称] and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘药检费’ ) update #KD_XS set [维修费公摊总额]=(select isnull(sum([金额]),0) from #hsd_fy where #hsd_fy.[车间名称]=‘公共车间’ and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘药检费’ ) update #KD_XS set [明确药检费]=[药检费车间总额][车间比重] update #KD_XS set [不明确药检费]=[维修费公摊总额]*[总比重] update #KD_XS set [药检费合计]=[明确药检费]+[不明确药检费]

–更新维修费 update #KD_XS set [维修费车间总额]=(select isnull(sum([金额]),0) from #hsd_fy where #KD_XS.[车间]=#hsd_fy.[车间名称] and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘维修费’ ) update #KD_XS set [维修费公摊总额]=(select isnull(sum([金额]),0) from #hsd_fy where #hsd_fy.[车间名称]=‘公共车间’ and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘维修费’ ) update #KD_XS set [明确维修费]=[维修费车间总额][车间比重] update #KD_XS set [不明确维修费]=[维修费公摊总额][总比重] update #KD_XS set [维修费合计]=[明确维修费]+[不明确维修费]

–更新低耗费 update #KD_XS set [低耗费车间总额]=(select isnull(sum([金额]),0) from #hsd_fy where #KD_XS.[车间]=#hsd_fy.[车间名称] and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘低耗费’ ) update #KD_XS set [低耗费公摊总额]=(select isnull(sum([金额]),0) from #hsd_fy where #hsd_fy.[车间名称]=‘公共车间’ and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘低耗费’ ) update #KD_XS set [明确低耗费]=[低耗费车间总额][车间比重] update #KD_XS set [不明确低耗费]=[低耗费公摊总额][总比重] update #KD_XS set [低耗费合计]=[明确低耗费]+[不明确低耗费]

–更新折旧费 update #KD_XS set [折旧车间总额]=(select isnull(sum([金额]),0) from #hsd_fy where #KD_XS.[车间]=#hsd_fy.[车间名称] and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘折旧’ ) update #KD_XS set [折旧公摊总额]=(select isnull(sum([金额]),0) from #hsd_fy where #hsd_fy.[车间名称]=‘公共车间’ and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘折旧’ ) update #KD_XS set [明确折旧]=[折旧车间总额][车间比重] update #KD_XS set [不明确折旧]=[折旧公摊总额][总比重] update #KD_XS set [折旧合计]=[明确折旧]+[不明确折旧]

--更新折旧费

update #KD_XS set [车间管理费车间总额]=(select isnull(sum([金额]),0) from #hsd_fy where #KD_XS.[车间]=#hsd_fy.[车间名称] and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘车间管理费用’ ) update #KD_XS set [车间管理费公摊总额]=(select isnull(sum([金额]),0) from #hsd_fy where #hsd_fy.[车间名称]=‘公共车间’ and #KD_XS.[公司编码]=#hsd_fy.[公司编码] and #hsd_fy.[项目]=‘车间管理费用’ ) update #KD_XS set [明确车间管理费]=[车间管理费车间总额][车间比重] update #KD_XS set [车间管理费折旧]=[车间管理费公摊总额][总比重] update #KD_XS set [车间管理费合计]=[明确车间管理费]+[车间管理费折旧]

–总额计算 update #KD_XS set [制造费用小计]=[车间管理费合计]+[折旧合计]+[低耗费合计]+[维修费合计]+[药检费合计]+[工资合计]+[水]+[电]+[汽] update #KD_XS set [成本合计]=[制造费用小计]+[原材料]+[辅料]+[中药材]+[半成品]+[浸膏]+[包材] update #KD_XS set [实际成本瓶/盒]=[成本合计]/[产品产量] 车间名称 select * from #KD_XS select* from #hsd_fy where [项目]=‘车间管理费用’

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2023-12-01，如有侵权请联系 cloudcommunity@tencent.com 删除

decimal