铁煤《项目客户化开发方案》

客户化开发方案

begin

update sfc_prouting

set sfc_prouting.define11 = sfc_prouting.versiondesc from inserted,sfc_prouting

where inserted.proutingid = sfc_prouting.proutingid end

2.5.3 Inventory表触发器

? 触发器名称：

inventory-cinvdefine2-insert

? 作用：

在增加和修改存货档案时，自动根据存货所属的分类，将存货划分为一类、二类、三类、配件、设备和全委采购，并将分类信息记录到存货自定义项2上。 ? 创建语句：

create TRIGGER [dbo].[inventory-cinvdefine2-insert] ON [dbo].[Inventory] after INSERT,update AS begin

update inventory set cInvDefine2 = case when (inserted.cinvccode) like '02A%' then '一类' when (inserted.cinvccode) like '02B%' then '二类' when ((inserted.cinvccode) like '02C%') or (inserted.cinvccode like '02D302006%') then '三类' when (inserted.cinvccode) like '03%' then '配件' when ((inserted.cinvccode) like '02D%') and (inserted.cinvcode <> '02D302006') then '设备' when inserted.bself = 1 or inserted.bProxyForeign = 1 then '全委采购' end

from inserted,inventory where inserted.cinvcode = inventory.cinvcode; end

2.5.4 Inventory_Sub表触发器

? 触发器名称：

inventory_sub-fbuyexcess-insert

? 作用：

在增加存货时，自动将“请购超额上限 ”赋值为“1000”，这样在生成采购订单时就

沈阳用友软件有限公司 25 ERP咨询实施部

客户化开发方案

可以超所需量订货了。 ? 创建语句：

create TRIGGER [dbo].[inventory_sub-fbuyexcess-insert] ON [dbo].[Inventory_Sub] after INSERT AS begin

update inventory_sub set fbuyexcess =1000

from inserted,inventory_sub where inserted.cinvsubcode = inventory_sub.cinvsubcode

and inserted.fbuyexcess is null end

2.5.5 PO_Podetails表触发器

? 触发器名称：

po_podetails-update-计划价

? 作用：

在录入和修改采购订单时，自动将采购订单上的存货对应的计划价写入到采购订单的“原币无税单价”上，将根据计划价和订单数量计算出原币无税金额、原币税额 、原币价税合计 、原币含税单价、本币税额 、本币价税合计 。 ? 创建语句：

create TRIGGER [dbo].[po_podetails-update-计划价] ON [dbo].[PO_Podetails] FOR INSERT, UPDATE AS begin

update po_podetails

set iunitprice = convert(decimal(18,2),inventory.iinvrcost), imoney=po_podetails.iquantity*

convert(decimal(18,2),inventory.iinvrcost),

itax=po_podetails.iquantity*convert(decimal(18,2),inventory.iinvrcost)*po_pomain.itaxrate/100,

isum=po_podetails.iquantity*convert(decimal(18,2),inventory.iinvrcost)+po_podetails.iquantity*convert(decimal(18,2),inventory.iinvrcost)*po_pomain.itaxrate/100,

inattax=po_podetails.iquantity*convert(decimal(18,2),inventory.iinvrcost)*po_pomain.itaxrate/100,

inatsum=po_podetails.iquantity*convert(decimal(18,2),inventory.iinvrcost)+po_podetails.iquantity*convert(decimal(18,2),inventory.iinvrcost)*po_pomain.itaxrate/100,

itaxprice=(po_podetails.iquantity*convert(decimal(18,2),inventory.iinvr

沈阳用友软件有限公司 26 ERP咨询实施部

客户化开发方案

cost)+po_podetails.iquantity*convert(decimal(18,2),inventory.iinvrcost)*po_pomain.itaxrate/100)/po_podetails.iquantity from inserted,po_podetails,inventory,po_pomain where inserted.poid=po_podetails.poid and po_podetails.cinvcode=inventory.cinvcode and po_pomain.poid=po_podetails.poid and po_podetails.iunitprice is null end

2.5.6 检修入库单生成检修出库的存储过程

? 存储过程名称：

inventory_sub-fbuyexcess-insert

? 作用：

铁煤机械厂的检修入出库是没有库存的，检修的部件生产完成马上就会被领走使用。但需求统计入出库数量。如果每个存货都要做入出库这样比较麻烦。基于这种情况，车间生产完成后需要在系统中做入库类别为“检修入库”的产品入库单，而出库这部分就不用手工录入了，而是在月末结账前，通过这个存储过程自动生成每张入库单对应的材料出库单，出库类别为“检修出库”。

注意：月末结账前，需要执行exec [redjxrkd] '004','105','205' 这段语句，用来运行存储过程的代码，才能生成检修材料出库单。 ? 创建语句：

create PROCEDURE [dbo].[redjxrkd] (@zth varchar(10), @rklb VARCHAR(50), @cklb VARCHAR(50)) AS

begin declare @maxcode varchar(20) declare @p5 int declare @p6 int declare @scount int declare @foid int declare @soid int set lock_timeout 30000 BEGIN TRAN T1 DECLARE MyCursorforF CURSOR FOR SELECT rdrecord.id FROM rdrecord WHERE crdcode = @rklb and

沈阳用友软件有限公司 27 ERP咨询实施部

客户化开发方案

cvouchtype = '10' and (ccode not in (select isnull(cDefine14,'') from rdrecord where rdrecord.crdcode = @cklb and rdrecord.cvouchtype = '11' )) OPEN MyCursorforF; FETCH NEXT FROM MyCursorforF INTO @foid; WHILE @@FETCH_STATUS = 0 BEGIN set @scount = (select count(*) from rdrecords where id = @foid) ; exec sp_GetId '',@zth,'rd',@scount,@p5 output,@p6 output set @maxcode = (select

convert(varchar(20),convert(int,cNumber) + 1) From VoucherHistory with (UPDLOCK) Where CardNumber='0412' and cContent is NULL) print(@maxcode) --向主表插入一条相同的记录 insert into RdRecord (ID, bRdFlag, cVouchType, cBusType, cSource, cBusCode, cWhCode, dDate, cCode, cRdCode, cDepCode, cPersonCode, cPTCode, cSTCode, cCusCode,

cVenCode, cOrderCode, cARVCode, cBillCode,

cDLCode, cProBatch, cHandler, cMemo, bTransFlag, cAccounter, cMaker, iNetLock, cDefine1, cDefine2,

cDefine3, cDefine4, cDefine5, cDefine6, cDefine7, cDefine8, cDefine9, cDefine10, dKeepDate, dVeriDate, bpufirst, biafirst, iMQuantity, dARVDate,

cChkCode, dChkDate, cChkPerson, VT_ID, bIsSTQc, cDefine11, cDefine12, cDefine13, cDefine14, cDefine15, cDefine16, cPsPcode, cMPoCode,

gspcheck, ipurorderid, ipurarriveid, iproorderid, iarriveid, isalebillid, iTaxRate, iExchRate, cExch_Name, cShipAddress, bOMFirst, bFromPreYear,

bIsLsQuery, bIsComplement, iDiscountTaxType, iBG_OverFlag, cBG_Auditor, cBG_AuditTime, ControlResult, caddcode, ireturncount, iverifystate,

iswfcontrolled, cModifyPerson, dModifyDate, dnmaketime, dnmodifytime, dnverifytime, bredvouch) select @p5, 0, '11', '领料','库存', null, cWhCode, dDate, REPLICATE('0',10 - len(@maxcode)) + @maxcode, @cklb, cDepCode, cPersonCode, cPTCode, cSTCode, cCusCode, cVenCode, cOrderCode, cARVCode, cBillCode, cDLCode,

cProBatch, null, cMemo, bTransFlag, null, cMaker, iNetLock, cDefine1, cDefine2,

cDefine3, cDefine4, cDefine5, cDefine6, cDefine7, cDefine8, cDefine9, cDefine10, null, null, bpufirst, biafirst, 0, dARVDate, cChkCode, dChkDate, cChkPerson, 65, bIsSTQc, cDefine11,

cDefine12, cDefine13, cCode, cDefine15, cDefine16, cPsPcode, cMPoCode, gspcheck, ipurorderid, ipurarriveid, iproorderid, iarriveid, isalebillid, rdrecord.iTaxRate, iExchRate, cExch_Name, cShipAddress, 0, bFromPreYear,

bIsLsQuery, bIsComplement, iDiscountTaxType, iBG_OverFlag, cBG_Auditor, cBG_AuditTime, ControlResult, caddcode, ireturncount, iverifystate,

iswfcontrolled, cModifyPerson, dModifyDate, dnmaketime, dnmodifytime, dnverifytime, bredvouch from rdrecord where rdrecord.id = @foid ; if @@error <> 0

沈阳用友软件有限公司 28 ERP咨询实施部