K3cloud-計算成本分析DEmo

業務背景

用于分析各月計劃與實際物料及費用偏差。
費用根據成本計算表分攤邏輯需要計算后按產線、車間、公司等費用掛的成本中心，按價值法進行均攤。
此含稅價取最近一次采購價格，按月會發生變化。

代碼示例

–建立臨時表
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