set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[STPEditDocRow] @docDetailCode int, @AmountIn money, @AmountOut money, @Comment nvarchar(1000)='', @Account Int, @SeccondAccount Int, @IgnorCreditLimit int , @linkFrom int AS declare @credit money , @rem money,@OldAmountIn money,@OldLinkTo int,@OldAmountout money,@OldLinkFrom int --Lock doc if exists(SELECT DocDetailTbl.Syscode FROM DocTbl INNER JOIN DocDetailTbl ON DocTbl.SysCode = DocDetailTbl.Fk_docSysCode WHERE (DocTbl.Lock <> 0) AND (DocDetailTbl.Syscode = @docDetailCode)) begin raiserror (50204,16,1) --doc is lock return 1 end /* find out credit limit permision ------------------- */ if @IgnorCreditLimit=0 and @AmountIn>0 begin SELECT @rem = SUM(AmmountIN - AmmountOUT) FROM DocDetailTbl WHERE (Fk_AccountSyscode = @Account and syscode<>@docDetailCode); select @OldAmountIn=ammountin FROM DocDetailTbl WHERE (Fk_AccountSyscode = @Account and syscode=@docDetailCode); if @OldAmountIn is null set @OldAmountIn=0 if @rem is null set @rem=0 sELECT @credit=CreditLimit FROM CodeMNGDWNTbl WHERE (AccountSysCode = @Account) if (@credit < (@rem+ @AmountIn)) and (@OldAmountIn < @AmountIn) begin raiserror(50201,16,1) --pass throw credit limit return 1 end end --Check old Row -- check if is link to any where cant edit amount SELECT @oldLinkTo=DocLinkTBL_1.FK_SndDocDetail,@OldAmountout= DocDetailTbl.AmmountOUT, @OldAmountin=DocDetailTbl.AmmountIN FROM DocDetailTbl INNER JOIN DocLinkTBL AS DocLinkTBL_1 ON DocDetailTbl.Syscode = DocLinkTBL_1.FK_FstDocDetail WHERE (DocDetailTbl.Syscode = @docDetailCode) if not @oldLinkTo is null if @OldAmountout<> @AmountOut or @OldAmountin <>@AmountIn begin raiserror (50206,16,1) return 1 end --doc has link and cant edit amount --if is going to be link check to row amount if @linkfrom <>0 begin select @OldAmountout= ammountout ,@OldAmountin=ammountin from DocDetailTbl where syscode=@linkfrom if @OldAmountout<> @amountin or @OldAmountin<>@amountout begin raiserror (50205,16,1) return 1 end end if @@error<>0 return 1 begin tran t1 delete from DocLinkTBL where FK_SndDocDetail=@docDetailCode if @linkfrom <> 0 exec dbo.STPCreateDocLink @linkfrom, @docDetailCode UPDATE DocDetailTbl SET Fk_AccountSyscode = @Account, AmmountIN = @AmountIn, AmmountOUT = @AmountOut, SndFk_AccountSyscode = @SeccondAccount, Comment = @Comment WHERE (Syscode = @docDetailCode) if @@error <>0 rollback tran t1 else commit tran t1