TIPTOP成本计算逻辑axcp500DECODING

1、成本阶：99 原料无工单(采购料件) ima08=p/v/z98 原料有工单有bom97 成品或半成品有工单无bom0-96 成品或半成品有工单有bomcca_file 库存成本期初ccb_file 库存成本当月入库调整金额ccc_file 库存月加权成本ccg_file 每月工单主件在制成本（依工单别的分批在制成本） 即工单上阶成本cch_file 每月工单元件在制成本 即工单下阶成本计算范围：有下列情况之一的料件，均纳入计算范围：1 tlf档当期有异动的料件 tlf2当期有投入工时的料件 ccj3有工单备料且有投入工时的料件 sfa/sfb/ccj4当期有上阶在制成本的料件(ccg有余额)

2、5当期有下阶在制成本的料件（cch未余额）6有库存期初开帐的料件cca7当期工单有期初在制开帐金额的料件 ccf8当期工单有在制调整金额的料件ccl9当期工单有工单发退料异动的料件sfb/tlf select 成本阶ima57,料号ima01,须计算重工否ccd03,低阶码ima16,来源码ima08,分群码ima06,其他分群码ima09-11,成本分群码ima12,where ima料号=tlf 料号 and tlf异动日期为当前期间 and tlf902仓库不为jit仓库 and 仓库不为0 #tlf档当期有异动的料件unionwhere ima料号=工单档sfb料号 and 投入工时c

3、cj01在当期期间 and ccj04工单号=工单档工单号sfb01 #当期有投入工时的料件unionwhere ima料号=备料档料号sfa03 and 投入工时ccj01在当期期间 and ccj04工单号=工单档工单号sfb01 and sfa备料档料号=sfb工单档料号 #有工单备料且有投入工时的料件unionwhere ima料号=在制成本档料号ccg and 在制成本为当期 and ccg91/92当期在制成本数量或单价不为零 #当期有上阶在制成本的料件unionwhere ima料号=在制成本元件档cch料号 and 在制成本为当期 and cch91/92当期在制成本数量或单价

4、不为零 #当期有下阶在制成本的料件unionwhere ima料号=库存期初开帐料号cca #有库存期初开帐的料件unionwhere ccf 期初在制开帐金额档料号=工单档料号sfb and ima料号=sfb工单料号 and 期初在制开帐金额不为零 #当期工单有期初在制开帐金额的料件unionwhere ccl当期在制调整金额料号=工单料号ima and ima 料号=工单料号sfb #当期工单有在制调整金额的料件unionwhere (未结案工单sfb38 is null or结案日在当前期间内) and ima料号=工单料号sfb05 and tlf单据日期在当前期间内 and tlf

5、工单单号=sfb工单单号 and tlf13异动命令代号为“asfi5*” #当期工单有工单发退料异动的料件例如：select ima57,ima01,ccd03,ima16,ima08,ima06, ima09,ima10,ima11,ima12from ima_file,tlf_file,outer ccd_filewhere ima57=ccd01 and ima01 matches * and ima01 not matches misc* and ima01 = tlf01 and tlf06 between 00/01/01 and 00/01/31 and tlf902 not

6、in (select jce02 from jce_file) and tlf907 != 0 group by 1,2,3,4,5,6,7,8,9,10unionselect ima57,ima01,ccd03,ima16,ima08,ima06, ima09,ima10,ima11,ima12from ima_file, ccj_file,sfb_file,outer ccd_filewhere ima57=ccd01 and ima01 matches * and ima01 not matches misc* and ima01 = sfb05 and ccj01 between 00

7、/01/01 and 00/01/31 and ccj04 = sfb01 group by 1,2,3,4,5,6,7,8,9,10unionselect ima57,ima01,ccd03,ima16,ima08,ima06, ima09,ima10,ima11,ima12from ima_file, ccj_file,sfb_file,sfa_file,outer ccd_filewhere ima57=ccd01 and ima01 matches * and ima01 not matches misc* and ima01 = sfa03 and ccj01 between 00/

8、01/01 and 00/01/31 and ccj04 = sfb01 and sfb01 = sfa01 group by 1,2,3,4,5,6,7, 8,9,10unionselect ima57,ima01,ccd03,ima16,ima08,ima06, ima09,ima10,ima11,ima12from ima_file, ccg_file,outer ccd_filewhere ima57=ccd01 and ima01 matches * and ima01 not matches misc* and ima01 = ccg04 and ccg02= 1999 and c

9、cg03= 12 and (ccg91 !=0 or ccg92 !=0 or ccg92a !=0 or ccg92b !=0 or ccg92c!=0 or ccg92d !=0 or ccg92e !=0 ) group by 1,2,3,4,5,6,7,8,9,10unionselect ima57,ima01,ccd03,ima16,ima08,ima06, ima09,ima10,ima11,ima12from ima_file,cch_file,outer ccd_filewhere ima57=ccd01 and ima01 matches * and ima01 not ma

10、tches misc* and ima01 = cch04 and cch02=1999 and cch03=12 and (cch91!=0 or cch92 !=0 or cch92a!=0 or cch92b !=0 or cch92c!=0 or cch92d !=0 or cch92e !=0 ) group by 1,2,3,4,5,6,7,8,9,10unionselect ima57,ima01,ccd03,ima16,ima08,ima06, ima09,ima10,ima11,ima12from ima_file,cca_file,outer ccd_file where

11、ima57=ccd01 and ima01 matches * and ima01 not matches misc* and ima01=cca01 and cca02= 1999 and cca03= 12 group by 1,2,3,4,5,6,7,8,9,10unionselect ima57,ima01,ccd03,ima16,ima08,ima06, ima09,ima10,ima11,ima12from ima_file,ccf_file,sfb_file,outer ccd_filewhere ima57=ccd01 and ima01 matches * and ima01

12、 not matches misc* and ccf01=sfb01 and ima01 = sfb05 and ccf02=1999 and ccf03=12and (ccf11 !=0 or ccf12!=0 or ccf12a !=0 or ccf12b !=0 or ccf12c !=0 or ccf12d !=0 or ccf12e !=0 ) group by 1,2,3,4,5,6,7,8,9,10unionselect ima57,ima01,ccd03,ima16,ima08,ima06, ima09,ima10,ima11,ima12from ima_file,ccl_fi

13、le,sfb_file,outer ccd_filewhere ima57=ccd01 and ima01 matches * and ima01 not matches misc* and ccl01 = sfb01 and ima01 = sfb05 and ccl02= 2000 and ccl03= 1 group by 1,2,3,4,5,6,7,8,9,10unionselect ima57,ima01,ccd03,ima16,ima08,ima06,ima09,ima10,ima11,ima12from ima_file,tlf_file,sfb_file,outer ccd_f

14、ilewhere ima57=ccd01 and ima01 matches * and ima01 not matches misc* and (sfb38 between 00/01/01 and 00/01/31 or sfb38 is null ) and ima01 = sfb05 and tlf06 between 00/01/01 and 00/01/31 and tlf62 = sfb01 and tlf131,5 = asfi5 group by 1,2,3,4,5,6,7,8,9,10into temp tlf_temp with no log计算过程：排序方式：uniqu

15、e ima57,ima01,ccd03,ima16 ima57 desc/ima16/ima01成本阶为99 ：1、ccc清零，取上期结存转本期期初2、由tlf 计算入出库数量，由apb101计算入库（采购）成本3、加上入库调整金额4、计算所有出库成本及结存5、更新ccc-file成本阶不为99：1、处理当前成本阶所有料件重工2、ccc清零，取上期结存转本期期初3、处理wip在制成本4、由tlf 计算入出库数量，入库成本(外购料件)5、加上入库调整金额6、加上wip入库金额7、计算所有出库成本及结存8、更新ccc-file处理重工重工处理过程：以当前成本阶的所有料件为计算范围，1、p500_r

16、ework1先处理重工wip及完成品入库， p500_wip_rework处理重工在制成本（以重工工单sfb99=y为范围），步骤与处理一般在制相同：wip_1计算主件部分成本；wip_2计算元件投入成本；wip_3计算元件转出成本；wip_4处理主件sum成本。2、p500_rework2计算所有重工出库成本及结存， p500_ccg2_cost加上wip重工入库成本（ccg32在制转出金额ccc28）； p500_ccc_tot(2) 计算所有出库成本及结存；3、记录拆件式工单的投入及转出（sfb02=11） 找所有拆件式工单； wipx_1计算拆件式wip主件部分成本； wipx_2计算

17、拆件式wip元件投入成本； wipx_3计算拆件式wip元件转出成本； wipx_4计算拆件式wip主件sum成本； p500_ccc_tot(2)计算所有出库成本及结存；main删除本期已有资料，取上期结存转本期期初p500_last0找计算范围（取本期有异动之资料计算tlf）抓资料到 tlf_tmpfetch 成本阶/料号/重工否/低阶码 （按成本阶降序排列） 来计算for each if 当前料件成本阶不同push 料号p500_rework 重工处理 (本成本阶所有料件)pop 料号 end if 保存当前料件料号g_ima01_t 将当前料件当期成本档归零 ccc_file p500

18、_ccc_0 取料件上期结存p500_last if 当前料件不是原料（成本阶数不为99）处理wip在制成本 p500_wip end if由tlf_file 计算入出库数量及采购成本 p500_tlf加入出库调整金额 p500_ccb_cost if 当前料件不是原料（成本阶数不为99）加上wip入库金额 p500_ccg_cost end if 计算所有出库成本及结存 p500_ccc_tot(1) 计算结果写入当期成本档p500_ccc_insend foreachif 上一料号为null 则 上一料号置零if 上一成本阶为null 则上一成本阶置零第二次处理重工p500_reworke

19、nd mainp500_last0()delete 本期成本资料 delete from ccc_file where ccc02= 2000 and ccc03= 1 and ccc01 in(select ima01 from ima_file where ima01 matches * and ima01 not matches misc*)取上期及开帐成本 select ima01 from ccc_file,ima_file where ccc02=1999 and ccc03=12 and ccc01=ima01 and ima01 matches * and ima01 not

20、matches misc* union select ima01 from cca_file,ima_file where cca02= 1999 and cca03= 12 and cca01=ima01 and ima01 matches * and ima01 not matches misc* call p500_ccc_0() 清零 select * into g_ccc.* from ccc_file where ccc01 = l_ima01 and ccc02=last_yy and ccc03=last_mm 先取上月结存 select * into l_cca.* from

21、 cca_file where cca01=g_ccc.ccc01 and cca02=last_yy and cca03=last_mm 再取期初开帐p500_rework()取当前料件成本阶的ccd03 （须计算重工否）if须重工先计算wip及完成品入库p500_rework1再计算所有出库成本及结存p500_rework2记录wip-拆件式工单在制成本（工单性质为11）目的为取重工后单价 p500_wipx0end ifend p500_rework2p500_rework1()在须计算的资料中抓当前料件异动资料 tlf_tmpforeach 料件从ccc_file 中抓当期资料，否则置

22、零处理wip重工成本（重工sfb99=y -工单档sfb_file 重工否） p500_wip_reworkend foreachend p500_rework1p500_rework2()在须计算的资料中抓当前成本阶所有料件异动资料 tlf_tmpforeach 料件从ccc_file 中抓当期资料，否则置零加上wip重工入库金额p500_ccg2_cost计算所有出库成本及结存 p500_ccc_tot(2)更新ccc_file p500_ccc_updend endp500_wipx0()在须计算的资料中抓当前料件异动资料 tlf_tmpforeach 料件从ccc_file 中抓当期资

23、料，否则置零 p500_wipxend endp500_last0()取上期结存转本月期初p500_tlf对某一料件由tlf档分类统计入出库数量并分别取入库成本，出库成本暂未处理（由tlf 取异动单类型xxx/单号xxx1/项次xxx2） when tlf131,4=axmt or tlf13 = aomt800 销货领出let g_ccc.ccc61=g_ccc.ccc61+q_tlf.tlf10*u_sign call p500_ccc63_cost(u_sign)if u_sign = 1 then let g_ccc.ccc64=g_ccc.ccc64+q_tlf.tlf10*u_si

24、gn end ifwhen q_tlf.tlf13=aimt301 or q_tlf.tlf13=aimt311 #报废异动(aimt303,aimt313)应列入杂项异动中 or q_tlf.tlf13 = aimt303 or q_tlf.tlf13 =aimt313let g_ccc.ccc41=g_ccc.ccc41+q_tlf.tlf10*u_signwhen q_tlf.tlf131,5=asfi5 #工单发料 if g_sfb99=y then #重工领出 let g_ccc.ccc25=g_ccc.ccc25+q_tlf.tlf10*u_sign else #一般工单领出 le

25、t g_ccc.ccc31=g_ccc.ccc31+q_tlf.tlf10*u_sign end ifwhen q_tlf.tlf131,5=asft6 #工单入库 if q_tlf.tlf02 = 65 or q_tlf.tlf03 = 65 then #拆件工单（成本由wip处理） let g_ccc.ccc31=g_ccc.ccc31+q_tlf.tlf10*u_sign else if g_sfb99=y then #重工入库（成本由重工wip转出） let g_ccc.ccc27=g_ccc.ccc27+q_tlf.tlf10*u_sign else #一般入库 let g_ccc.

26、ccc21=g_ccc.ccc21+q_tlf.tlf10*u_sign call p500_ccc22_cost() #取成本 end if end ifwhen q_tlf.tlf13 = aimt302 or q_tlf.tlf13 = aimt312 let g_ccc.ccc21=g_ccc.ccc21+q_tlf.tlf10 let g_ccc.ccc43=g_ccc.ccc43+q_tlf.tlf10 call p500_ccc22_cost()u_flag为入出库标志when u_flag=1 #一般工单入库、采购入、仓退 let g_ccc.ccc21=g_ccc.ccc21

27、+q_tlf.tlf10*u_sign call p500_ccc22_cost() #取入库成本when u_flag=5 #调整 let g_ccc.ccc51=g_ccc.ccc51+q_tlf.tlf10*u_signwhen u_flag=6 #盘差 let g_ccc.ccc71=g_ccc.ccc71+q_tlf.tlf10*u_sign otherwise continue foreachp500_ccc22_cost()取入库成本工单入库成本暂不处理 (tlf13 matches asf*) 工单入库成本应由wip转入，故加工费于wip_2_22()归入投入成本，再转入库如为

28、委外退库暂不处理if q_tlf.tlf13 matches apmt107* then let l_pmm02=null select pmm02 into l_pmm02 from pmm_file where pmm01=q_tlf.tlf036 and pmm18 x if l_pmm02=sub then return end if #-委外退库亦由wip转出 end ifwhen q_tlf.tlf13 matches aimt30* #杂项入zzzcall p500_ccc44_cost() let amt=l_ccc23 * q_tlf.tlf10 * u_signwhen q

29、_tlf.tlf13 = aimt720 or #调拨入zzzq_tlf.tlf13 = aimp700 or q_tlf.tlf13 = aimp701 let amt = g_ccc.ccc23 * q_tlf.tlf10 #直接取上月平均单价otherwise #取发发票请款立帐金额select apa44,sum(apb101) #帐款单身成本分摊本币金额为了配合成本分摊作业aapt900 故改apb10为取apb101 from apb_file,apa_filewhere apb21=xxx1 and apb22=xxx2 #xxx1为tlf单据号，xxx2为项次and apb01

30、=apa01 and apa00 = 11 #帐款性质为进货发票and apa75 != y and apa42 = n #非外购资料且未作废帐款 and apa02 between g_bdate and g_edate group by 1问题：为何不判断确认否？apa41=yselect apa44,sum(apb101) #扣除折让部分（退货） from apb_file,apa_file where apb21=xxx1 and apb22=xxx2 and apb01=apa01 and apa00 = 21 and apa58 = 2 and apa75 != y #帐款为退货折

31、让 and apa42 = n and apa02 between g_bdate and g_edategroup by 1金额取绝对值若为外购信用状入库则取到货分摊后金额sum(ale09)select alk72,sum(ale09) from ale_file ,alk_filewhere ale16=xxx1 and ale17=xxx2 and ale01=alk01group by 1更新tlf 档，其中杂收不更新，由axct500确定if q_tlf.tlf13 != aimt302 and q_tlf.tlf13 != aimt312 then update tlf_file

32、 set (tlf21,tlf221,tlf222,tlf2231,tlf2232,tlf224, tlf211,tlf212,tlf65) =(amt,amta,amtb,amtc,amtd,amte,today,g_time,l_apa44)where rowid=q_tlf_rowedp500_ccc44_cost()取杂项入库成本先取本月杂项入库金额档inb_file （手工维护）如无单价则取期初开帐金额 cca_file仍无单价则取上月平均ccc_file ccc23p500_ccc63_cost()本月销货收入p500_ccb_cost()加入库库调整金额p500_ccg_cost

33、()加上wip入库金额（工单主件在制成本）非重工的、非预测工单、非拆件式工单的工单主件在制成本p500_ccg2_cost()加上wip重工入库金额重工的、非预测工单、非拆件式工单的工单主件在制成本p500_ccc_tot(1 or 2) 计算所有出库成本及结存1第一阶段，先不处理重工2第二阶段，处理重工if 2 则处理重工 p500_ccc_ccc26 ccc26重工领出金额p500_ccc_ccc23 ccc23本月平均单价if 第一阶段且当前成本阶不须计算重工 或 第二阶段let 本月工单领用成本=0 g_ccc_ccc32let 本月杂项领用成本=0 42let 本月其他领用成本=0

34、52let 本月销货成本=0 62/62a/62b/62c/62d/62elet 本月盘盈亏金额=0 72let 本月销退成本=0 66/66a-ep500_tlf21_updend iflet 本月结存数量ccc91=上月结存数量ccc11+本月入库数量ccc21（采购入+工单入+委外入）+本月重工领出ccc25+本月重工入库数量ccc27+本月工单领用数量ccc31+本月杂项领用数量ccc41+本月其他调整数量ccc51+本月销货数量ccc61+本月盘盈亏数量ccc71let 本月结存金额ccc92=上月结存金额ccc12+本月入库金额ccc22（材料22a+人工22b+制费22c+加工2

35、2d+其他22e）+本月重工领出金额ccc26+本月重工入库金额ccc28+本月工单领用成本ccc32+本月杂项领用成本ccc42+本月其他调整成本ccc52+本月销货成本ccc62+本月盘盈亏金额ccc72销退数量ccc64和销退成本ccc66未考虑？let 本月结存材料金额=本月结存数量ccc91*本月平均单价-材料ccc223a人工制费委外其他差异金额ccc93p500_ccc_ccc26处理重工领出金额p500_ccc_ccc23()计算本月重工工单部分的平均单价先取期初开帐，再取上月余额进行计算平均单价p500_tlf_upd()更新tlf 档单价p500_ccc_ins()更新cc

36、c档成本金额p500_cch_0()将工单元件在在制成本档按上月期末转本月期初，其余归零p500_cch_01()将工单元件在在制成本档全部归零wip_del()删除工单在制成本档（ccg/cch）p500_wip()处理wip工单在制成本(工单性质1/7)select * from sfb_file #找当前料件在制工单（非重工） where sfb05 =g_ima01 and sfb02!= 13 and sfb02 != 11 and (sfb99 is null or sfb99 = or sfb99=n) and (sfb38 is null or sfb38 = g_bdate)

37、 # 工单成会结帐日 and (sfb81 is null or sfb81 = g_edate) # 工单开立日期 order by sfb01先delete ccg/cch wip_del计算每张工单的wip主件部分成本(ccg) wip_1()计算每张工单的wip元件投入成本(cch) wip_2()计算每张工单的wip元件转出成本(cch) wip_3()计算每张工单的wip主件sum成本(ccg) wip_4()p500_wip_rework()处理wip-rework在制成本（重工sfb99=y）先delete ccg/cch wip_del2计算每张工单的wip主件部分成本(cc

38、g) wip_1()计算每张工单的wip元件投入成本(cch) wip_2()计算每张工单的wip元件转出成本(cch) wip_3()计算每张工单的wip主件sum成本(ccg) wip_4()wip_1()计算每张工单的wip主件部分成本(ccg)将mccg归零p500_mccg_0wip上期期末数量转本期期初（先取在制工单成本数量ccg91,再取在制开帐档数量ccf11）工时统计wip_ccg20（取ccj档工单合计工时）计算每张工单wip主件转出数量wip_ccg31 （取tlf档成品入库-成品退库）更新ccg_filewip_ccg20()select sum(ccj05) into

39、 mccg.ccg20 from ccj_file #取ccj档工单合计工时 where ccj04=g_sfb.sfb01 and ccj01 between g_bdate and g_edatewip_ccg31()计算工单wip主件转出数量select sum(tlf10*tlf60) into g_make_qty from tlf_file # 当月成品入库量 where tlf62=g_sfb.sfb01 and tlf06 between g_bdate and g_edate and (tlf03 = 50) and tlf13 matches asft6*and (tlf0

40、1 = g_sfb.sfb05)select sum(tlf10*tlf60) into g_make_qty2 from tlf_file #当月成品退库量 where tlf62=g_sfb.sfb01 and tlf06 between g_bdate and g_edate and (tlf02 = 50) and tlf13 matches asft6*and (tlf01 = g_sfb.sfb05)转出数量（以负数表示）=退库量-入库量wip_ccg21()计算每张工单的wip主件投入数量抓工单发料底稿sfq的成套发料（sfp=1）-成套退料（sfp=6）wip_2()计算每张工单的wip元件期初/本期投入成本cchstep1. wip元件上期末转本期期初 wip_2_1step2-1 wip元件本期投入材料（依工单发料/退料档） wip_2_21计算每张工单的wip主件投入数量 wip_ccg21step2-2 wip元件 本期投入人工制费 wip_2_22step2-3 wip元件 本期投入调整成本 wip_2_23wip_2_1()先取cch档上期转本期期初再取ccf在制开帐档转本期期初wip_2_21()wip元件 本期投入材料（依工单发/退料档） 同时结出投入金额select tlf01,ima08,ima57,tlf02,tlf03,tlf13,s