染料行业ERP配方成本算法及其存储过程实现

1、染料行业ERP配方本钱算法及其存储过程实现摘要：配方本钱算法是染料行业ERP的重要组成局部，是制定染料产品销售价格的基矗通过分析染料配方所具有的非线性特性，设计了与产品配方相关的数据库表构造，在此根底上提出了配方本钱的算法。针对配方中存在自包含和互相包含现象，讨论并证明了算法的收敛性，并提出了在本钱容许误差范围内进步算法速度的方法。算法使用存储过程加以实现，给出了相应的代码。通过大量实际配方数据的测试实验，说明在修改配方材料的单价后，该算法可以稳定地对配方本钱数据进展实时更新。关键词：ERP，染料配方，本钱，存储过程PresriptinstAlgrithfrERPfDyeIndustryand

2、ItsIpleentatinbyStredPredureYingHngEillegefEletrniandInfratinEngineering,NingbUniversityfTehnlgy,Ningb315016Abstrat?Presriptinstalgrith,upnhihthepriesfdyeprdutsarebased,isaniprtantpartftheEnterpriseResurePlan(ERP)fdyeindustry.Afteranalyzingthennlinearharateristifdyepresriptins,sedatabasetablesrelate

3、diththeprdutpresriptinsaredesigned,andastalgrithisprpsedinthispaper.Alsthispaperdisussesthenvergenefthealgrithtdealiththeself-ntainingandutualntainingrelatinshipsexistinginsepresriptins,andprpsesaethdtspeedupthealgrithithinthetleraneerrrfthest.Thealgrithisipleentedbystredpredure.Experientalresultsit

4、hanabundantfpratialpresriptinsshthattheprpsedalgrithanupdatethestsfpresriptinstielyandstablyhenthepriefaterialishanged.Keyrds?ERP,presriptinfdye,st,stredpredure1染料制造业和染料配方的特点染料制造行业属于过程制造业，通过对原材料进展粉碎、混合、搅拌、加热等物理或化学方法，制成染料成品。通常以批量或连续型的方式进展消费。染料行业施行专用的ERPEnterpriseResurePlan，企业资源方案管理软件可以对企业的资源进展整合并进步了工

5、作效率，可以对企业的客户、采购、研发、消费、仓储、销售等各方面进展有效的管理。特定行业ERP软件在设计过程中必然遇到该行业所特有的问题。染料产品配方的本钱算法是染料制造行业ERP的重要组成局部。产品的销售价格主要取决于实验室的配方本钱，而产品是由原材料组成，目前该行业的原材料市场价格变化频率较高，这就要求企业具有快速的应变才能，及时掌握最新的原材料采购价格并实时反映到产品的配方本钱中去。一个染料产品，其配方往往不止一个。例如对于产品“R015，某染料制造企业就有“R015(BL)、“R015A、“R015B、“R015(RN015)和“R015(try)5种配方。配方的组成材料来源比拟广泛：大

6、多是从上游厂家采购的原材料；一局部是企业自己消费的中间体通常归属于产品类别；此外，考虑实际消费的特点，在某些产品的配方中也包括一局部其它的终端产品以改善产品性状。因此，染料配方的配方明细单B，物料装配单并非呈单一的树形构造而通常呈网状构造?不仅有表达上下层关系的树形构造，还存在子件中使用母件的自包含和互相包含的网状构造。此外，化学品配方所特有的非线性现象，也并非传统的树形构造所能描绘。转贴于论文联盟.ll.目前，对于B的研究，在数据构造上常限于树形构造1,3-6，在本钱算法上常采用遍历、搜索和递归的方法以获得最底层的原材料及组成比例，进而推算出配方的本钱3-6。树形的数据构造比拟合适用于机械制

7、品、电子产品的物料清单，可以将一个大型产品分解成根本的元器件。但是对于化学品配方中存在的非线性现象并不适用。例如：1000克产品A由400克中间体B和600克原材料组成；400克B是由200克和200克D所组成。上述的两个配方不能简单的合并为：1000克产品A由800克和200克D组成，因为这样的配方消费出来的产品A极有可能是废品。因此染料配方在数据构造上的问题是不能确定从产品到根本原材料之间的层数。文献2的数据构造设置了产品层和生成层，但它需要预知产品的类型终端产品还是中间体，而且要求配方中的所有产品不能出现自包含和互相包含问题，而它们在染料企业实际配方中是必定存在的。针对上述问题，必须设计

8、合理的数据构造解决染料配方构造上所具有的非线性、自包含和互相包含问题，然后设计高效的算法解决染料配方本钱的计算问题。2本钱算法的数据构造实际情况中一个染料产品不止有一个配方，因此设置了3层固定构造?产品层、配方层、配方明细层以解决配方构造上的非线性问题。如图1所示，相关的表共有4个，为Ra、Prdut、Presriptin、Detail。表1中列举了和本钱配方算法有关系的各表字段；表2说明了各表的主键与外部键设置。图1产品配方构造示意图表1各表与主要字段说明表2各表主键与外部键设置说明由于企业的特殊需求?要求每个配方的配方编号为唯一，所以只设置pp_id为Presriptin表的主键。而非由p

9、p_id和p_id共同组成主键。3本钱算法的设计与实现3.1算法的总体设计本钱算法的设计必需要考虑导致产品本钱变化的原因。原因有以下3个方面：1原材料表Ra中某一个原材料的价格发生变化，导致Detail中相应的原材料单价变化，从而使得配方本钱、产品本钱发生一系列变化；2由于客户的个性化需求，导致产品的当前配方被置换使得产品本钱发生改变；3产品的当前配方的组成材料种类、比例发生改变，导致当前配方本钱变化，从而也改变了产品的本钱。配方本钱的算法可分为两类?自上而下的遍历、递归和自下而上循环算法。前一种方法遍历每一种产品和它的配方明细。假如遇到明细是产品的情况，那么递归调用遍历算法，直到将每种产品分

10、解成最根本的原材料，最后累加这些原材料的本钱，就是产品配方的本钱。这种算法在使用SQL语句编程时，需要使用效率较低的基于行的游标语句7，而客户的个性化需求导致染料企业产品库很庞大。考虑到这个现实。应该尽可能选择使用执行效率较高的基于集合的SQL语句，抛弃过程化的习惯编程方法?防止使用基于行的游标语句。本文中采用了后一种方法，可以防止使用游标而全部使用高效的基于集合的SQL语句。自下而上的循环算法的步骤是：在原材料表Ra中某一个原材料或产品表Prdut中某一个产品假设为1的价格发生变化时，使得配方明细表Detail中配方明细为1的价格prie也得到修正，该价格乘以数量quantity除于1000

11、得到1的本钱st，并和其它的配方明细本钱累加得到对应配方假设编号为E1的新的金额并更新配方表Presriptin中st配方本钱的值。假如被更新的配方正好是其对应产品假设编号为P1的当前配方，那么还要更新产品表Prdut中st产品本钱的值。由于Detail表中包含有产品P1，所以继续调整Detail中P1的价格，并计算该配方的累加金额再次更新Presriptin中对应配方的本钱。假如数据库中存在Prdut的本钱和它的当前配方的本钱不相符合的情况，那么再次修改Prdut中的本钱。由于被修改本钱的产品有可能在Detail中被当作材料使用，因此继续循环计算各配方的本钱。该算法的流程图如图2所示：图2程

12、序算法流程图流程图中一个重要的步骤是更新Presriptin表中配方假设有E1En这n个配方的本钱st1stn，而该n个配方的新本钱来自于Detail表中E1En各配方所对应的配方名细的累加金额。如图3所示。图3Presriptin和Detail本钱关系示意图由于配方的新本钱需要用配方明细累加金额来更新，而这个更新过程在流程图循环体中反复被执行，因此设计了一个视图，该视图命名为detail_st_su，它是由detail表经过聚合查询而形成的。该视图的代码是：useDyeERP/*数据库名称*/ifexists(seletnaefrsysbjetsherenae=detail_st_suand

13、type=V)drpviedetail_st_sugREATEVIEdetail_st_suASSELETpp_id,su(st)assustfrdetailgrupbypp_idg因此该视图共有2个字段，分别是配方编号pp_id、配方明细本钱合计sust即配方的本钱。这样设计的优点是：当Detail表中有一个材料价格发生变化时，该视图可以即时反映最新的配方本钱。在更新presriptin表的本钱时，只需要将视图detail_st_su中的sust的值赋于presriptin中对应的配方编号。对于图2所示的循环体终止循环的条件?是否存在产品本钱和当前配方本钱不相符的情况？只要统计出Prdut表

14、中的本钱与当前配方本钱不相符的产品的数量，假如该数量大于0，那么不但要更新产品的本钱为其当前配方本钱，而且还要继续循环，因为那些本钱发生变化的产品有可能在配方明细表Detail中被当作材料使用。3.2具有自包含、互相包含性质配方的算法分析由图1可知，产品P1是P2的组成材料，P1、P2是P3的材料，这种关系称为嵌套；假如P1的组成材料中含有P2，P2的组成材料中还含有P1，这种关系就被称为互相包含；从图1中还可看出，P4的组成材料中甚至还含有其本身，这样的关系称为自包含。在配方中，嵌套情况是大量存在的，主要表现为中间体，中间体在编号上是属于产品系列的，属于prdut表，但是中间体又是消费其他产

15、品的重要组成材料。在实际配方中，嵌套关系最多只有3层，表如今本钱算法上，也就是循环次数最大为3次。自包含和互相包含情况看似矛盾、不符常理，实际上在染料消费中也存在。主要表现为对产品性状的改进、对不合格产品的回收再利用等方面。由于这些情况的存在，有必要讨论算法的循环是否具有收敛性。考虑某互相包含的两个配方P1和P2：P1产品的当前配方为E1，E1配方含有配方明细R1、P2，其中R1单价为r1，所占比例为a，P2本钱为p2，所占比例为b。那么P1的本钱p1应为：p1=r1?a+p2?b其中a0，b0，a+b=1(1)假如此时P2产品更改了当前配方，P2的当前配方为E2，E2配方含有配方明细R2和P

16、1，其中R2本钱为r2，所占比例为，P1所占比例为d,那么P2本钱p2为：p2=r2?+p1?d其中0，d0，+d=1(2)将(2)式代入(1)式，得到：p1=r1?a+r2?b+p1?d?b(3)令常数k1=r1?a+r2?b，k2=d?b。那么(3)式转变为：p1=k1+p1?k2(4)将(1)式代入(2)式，并令k3=r2?+r1?a?d得到：p2=k3+p2?k2(5)(4)式和(5)式是两个迭代算式，p1=k1/(1-k2)和p2=k3/(1-k2)是平衡点，此时迭代停顿。假如p1和p2初始值不在平衡点上。那么(4)式的迭代计算可以表示为：p1(1)=k1+k2?p1(0)p1(2)

17、=k1+k2?p1(1)p1(n)=k1+k2?p1(n-1)=(k1+k2?k1+k22?k1+k2n-1?k1)+k2n?p1(0)(6)由于k2必小于1，所以随着迭代次数n的增加，p1必趋向于平衡点，同理p2也必趋向于平衡点。由于计算机的字长有限导致计算机的精度有限，因此，只要迭代次数足够多，该算法的循环必然可以终止，即该算法具有收敛性。但是假如循环次数过多，将大量占用计算机资源，影响了其它用户对效劳器的使用，所以必须控制计算迭代循环的次数。以下2种方法可以有效地限制循环次数：1设置一个字段，对符合包含、自包含特征的配方进展标志，通过控制算法程序的here语句，使其不参与本钱算法的运算，

18、由人工来设定配方的本钱。2控制循环最大的次数，例如最多循环10次。由于化学工程师在实际工作中，随着配方数量的增长，未必能有精力去注意配方是否具有包含、自包含特性，因此方法1并不实用；方法2虽然在精度上有一定损失，但是只要合理设置最大循环次数，使得误差在一个可以承受的范围内，那么方法2还是可行的，毕竟产品最终的销售价格在本钱根底上还要有一个调整的过程。3.3算法的存储过程实现配方本钱算法是染料行业ERP软件的一局部，该ERP软件后台数据库管理系统是SSQLServer2000。配方算法使用存储过程作为载体，其优点在于处理数据的程序就在效劳器上，代码与数据之间的间隔 很短，由此降低了网络流量，而且

19、存储过程的SQL语句是预编译的，因此实际使用中减少了编译时间，进步了数据处理的速度和数据的完好性7。以下是名为sp_presriptin_st_hange存储过程的代码：useDyeERP/*数据库名称*/ifexists(seletnaefrsysbjetsherenae=sp_presriptin_st_hangeandtype=P)drppreduresp_presriptin_st_hangegreateprsp_presriptin_st_hangeaterial_idvarhar(20),ne_stnueri(12,2)asdelarep_hangeint/*标志位，得到产品本钱和

20、当前配方本钱不相符的产品数量*/setp_hange=0/*更新配方明细表的材料单价字段*/updatedetailsetprie=ne_sthereaterial_id=aterial_id/*更新配方明细表的本钱字段*/updatedetailsetst=prie*quantity/1000hereaterial_id=aterial_id/*更新配方表的本钱字段，使其等于配方明细累计金额视图的价格累加*/updatepresriptinsetpresriptin.st=detail_st_su.sustfrpresriptin,detail_st_suherepresriptin.pp_

21、id=detail_st_su.pp_id/*找出产品本钱和其当前配方本钱不一致的那些产品的数量，赋值给标志位p_hange*/seletp_hange=unt(prdut.p_id)frprdut,presriptinherepresriptin.pp_id=prdut.pp_idandprdut.stpresriptin.st/*假如产品本钱和其当前配方本钱不一致的产品数量大于0，那么循环*/hilep_hange0andp_hange=10begin/*更新产品表的价格，使其等于产品当前配方的价格*/updateprdutsetprdut.st=presriptin.stfrprdut,

22、presriptinhereprdut.pp_id=presriptin.pp_id/*更新配方明细表的材料单价字段，使之等于产品本钱*/updatedetailsetdetail.prie=prdut.stfrdetail,prduthereprdut.p_id=detail.aterial_id/*更新配方明细表的本钱字段*/updatedetailsetdetail.st=detail.prie*detail.quantity/1000frdetail,prduthereprdut.p_id=detail.aterial_id/*更新配方表的本钱字段，使其等于配方明细累计金额视图的价格累

23、加*/updatepresriptinsetpresriptin.st=detail_st_su.sustfrpresriptin,detail_st_suvherepresriptin.pp_id=detail_st_suv.pp_id/*找出产品本钱和其当前配方本钱不一致的那些产品的数量，赋值给标志位p_hange*/seletp_hange=unt(prdut.p_id)frprdut,presriptinhereprdut.pp_id=presriptin.pp_idandprdut.p_stpresriptin.stendg3.4算法的测试和评估该算法存储过程SQL语句都是基于集合的，因此执行效率高。在PU为IntelP4主频3.0GHZ、512内存的计算机上通过对某染料消费厂家1251种产品、3784种