搜索
T6只启用存货核算模块,现存量(可用量)计算不正确,也无法使用软件(库存)自带的现存量整理功能,需在RdRecords中添加触发器,使RdRecords表有变动时自动整理CurrentStock表的理存量。本触发器已经过测试,是为客户写的,大家只要修改数据库名后在“查询分析器”中运行就可以了,不会变更单据数据,放心使用。注意：触发器新建年度账时不能继承,每年都有运行一下哦。下面是代码：USE[UFDATA_004_2015]GO/******对象:Trigger[dbo].[RdRecords_ccgc]脚本日期:06/30/201508:33:16******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOALTERtrigger[dbo].[RdRecords_ccgc]on[dbo].[RdRecords]forinsert,update,DELETEasbeginDeleteFromCurrentStockifexists(select*fromsysobjectswherename='tempcurrentstock')droptableTempCurrentStockSELECTcWhCode,cInvCode,(CASEWHENcFree1=''THENNULLELSEcFree1END)AScFree1,(CASEWHENcFree2=''THENNULLELSEcFree2END)AScFree2,iainquantityASiQuantity,CAST(0ASFLOAT)ASfOutQuantity,CAST(0ASFLOAT)ASfInQuantity,0asinumINTOTempCurrentStockFromia_subsidiaryWherecvoutype='34'groupbyia_subsidiary.cWhCode,ia_subsidiary.cInvCode,ia_subsidiary.cFree1,ia_subsidiary.cFree2,ia_subsidiary.iainquantityINSERTINTOTempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity,inum)SELECTcWhCode,cInvCode,(CASEWHENcFree1=''THENNULLELSEcFree1END)AScFree1,(CASEWHENcFree2=''THENNULLELSEcFree2END)AScFree2,(CASEWHENbRdFlag0THENiQuantityELSE-iQuantityEND)ASiQuantity,0asinumFROMRdRecordINNERJOINRdRecordsONRdRecord.ID=RdRecords.IDWHEREcVouchType'33'ANDcVouchType'34'ANDRdRecord.dDate='2015-01-01'INSERTINTOTempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity,inum)SELECTEnDispatchs.cWhCode,EnDispatchs.cInvCode,(CASEWHENEnDispatchs.cFree1=''THENNULLELSEEnDispatchs.cFree1END)AScFree1,(CASEWHENEnDispatchs.cFree2=''THENNULLELSEEnDispatchs.cFree2END)AScFree2,(ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0))ASfOutQuantity,0asinumFROMEnDispatchINNERJOINEnDispatchsONEnDispatch.EDID=EnDispatchs.EDIDWHEREISNULL(EnDispatchs.cWhCode,'')''ANDISNULL(EnDispatch.cSTCode,'')''ANDEnDispatchs.iQuantity0ANDbReturnFlag=0INSERTINTOTempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity,inum)SELECTDispatchLists.cWhCodeascWhCode,DispatchLists.cInvCodeascInvCode,(CASEWHENDispatchLists.cFree1=''THENNULLELSEDispatchLists.cFree1END)AScFree1,(CASEWHENDispatchLists.cFree2=''THENNULLELSEDispatchLists.cFree2END)AScFree2,(ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0))ASfOutQuantity,0asinumFROMDispatchListsINNERJOINDispatchListONDispatchLists.DLID=DispatchList.DLIDWHEREDispatchList.cVouchType='05'ANDISNULL(DispatchLists.cWhCode,'')''ANDISNULL(DispatchList.cSTCode,'')''AND(DispatchList.iSale=0orDispatchList.iSaleISNULL)ANDDispatchLists.iQuantity0INSERTINTOTempCurrentStock(cWhCode,cInvCode,cFree1,cFree2,fOutQuantity,inum)SELECTcWhCode,cInvCode,(CASEWHENcFree1=''THENNULLELSEcFree1END)AScFree1,(CASEWHENcFree2=''THENNULLELSEcFree2END)AScFree2,(ISNULL(iQuantity,0)-ISNULL(fOutQuantity,0))ASfOutQuantity,0asinumFROMSaleBillVouchsINNERJOINSaleBillVouchONSaleBillVouch.SBVID=SaleBillVouchs.SBVIDWHEREISNULL(SaleBillVouchs.cWhCode,'')''AND(SaleBillVouch.iDisp=0ORSaleBillVouch.iDispISNULL)ANDISNULL(cBusType,'')'委托'ANDISNULL(cSTCode,'')''ANDISNULL(cInvalider,'')=''ANDSaleBillVouchs.iQuantity0INSERTINTOCurrentStock(cWhCode,cInvCode,cFree1,cFree2,iQuantity,fOutQuantity,fInQuantity,inum)SELECTcWhCode,cInvCode,cFree1,cFree2,sum(iQuantity)ASiQuantity,sum(ISNULL(fOutQuantity,0))ASfOutQuantity,sum(ISNULL(fInQuantity,0))ASfInQuantity,0asinumFROMTempCurrentStockGROUPBYcWhCode,cInvCode,cFree1,cFree2DeleteFromCurrentStockwhereiquantityisnullandfoutquantity=0andfinquantity=0updatecurrentstocksetcFree1='',cFree2=''end;