SQL Server Performance Forum – Threads Archive

Dead Lock Issue

My Dear Folks I am facing a problem of dead locks. Obviously dead locks can be handled by well formed queries. To be very frank I did my level best to frame well organized queries. Scenario under which i am getting dead lock is mentioned below I working on project for which concurrent Insertion/updation is a must. I am using an SP(PBLMSP) for doing this insertion and updation. On executing of this SP records will be inserted into 10 tables and 1 table will be updated so total 11 eleven tables. Almost all the tables are in the header detail format. Execution of this SP can insert or update each of the 11 tables with a maximum of 2000 records. This is how my SP(PBLMSP) goes I have created 11 temp tables with same structure as that of my 11 base tables Through a loop (this loop will execute a maximum of 2000 times) I will insert the data needed for my base tables into the 11 temptables. So after the execution of the loop all my 11 temp tables will have a maximum of 2000 records in it. Once the loop is over the transaction begins and the structure is as follows Set Transaction Isloation Level Read Committed Begin Tran Insert IntoBaseHdrTable1With (RowLock) (Field1,Field2,…..Field2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Select(Field1,Field2,…..Field2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> From#TempBAseHdrTable1 Insert IntoBaseDetlTable1With (RowLock) (Field1,Field2,…..Field2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Select(Field1,Field2,…..Field2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> From#TempBAseDEtlTable1,BaseHdrTable1With (RowLock) Where#TempBAseDEtlTable1.LNKID=BaseHdrTable1.LNKID In this passion it goes on for 10 tables and an update happens for 1 table in between Commit Tran If @@Error<>0 Rollback Tran Kindly remember that all the 11 base tables are frequently accessed tables in some form or another by the user. When my above SP(PBLMSP) executes, if a user trying to do a transaction that involves any of the mentioned 11 tables chances are there for a dead lock. If I try to execute SP(PBLMSP) from different Query Windows also I will be getting a dead Lock. Hope some of you can help me by throwing some light into this issue. I could have avoided the Dead Lock ISsue if i have mentioned the transaction isolation level as REadUncommitted.This infact is suicidal Which i dare to do it.Kindly help me in finding a solution for this issue With Tons of Regards Anil

See if this helps
http://sql-server-performance.com/deadlocks.asp Madhivanan Failing to plan is Planning to fail

Dear Madhivanan I went thru the site mentioned by you.
Was useful but still I am having problems with dead locks.
Can you provide with some more useful solutions

1. Do you have an index on BaseHdrTable1.LNKID?
2. Try to access tables in each query in the same order.
3. How do you create temp tables?

Dear friends I don’t have an index on BasehdrTable1.LNKID
I am accessing tables in each query in the same order
I create temptables in at the beginning of the SP Apart from these things I would like to say I have moved a step ahead.
It is nothing but the SP(PBLMSP) mentioned in my previous post will not create a problem when only one instance of this SP is running.Other user will be able to do successful transaction when only one instance of this SP is running.Another thing, that is interesting is that other transactions will be completed only after the execution of PBLMSP.Is there any way to tackle this problem.Users doing some another transaction has to wait until PBLMSP gets executed.Can somebody throw some light into this issue Regards
Anil

Have you tried to create index on lnkID? Without that whole baseHdrTable will be scanned.

Dear mmarovic I created Index on the LnkId Field. But Still I am getting DeadLock error(mentioned below) Server: Msg 1205, Level 13, State 1, Procedure &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rocedureName>, Line 37 Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. The Following Statement is at Line No 37 Mentioned in the error message UPDATE <Table1> SETField1= ISNULL(Field1,0) + ISNULL(@p_Param1,0), Field2= ISNULL(Field2,0) + ISNULL(@p_Param2,0) From<Table1> With (UpdLock) WHEREBrId = @p_BrId ANDGlHeadId = @p_GlHeadId ANDBusnsDt = @p_BusnsDt Too many updates happen to a single record in this <Table1>.I am not in a position to avoid these much updaes as it affects the business logic. This error occurs when the procedure &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />rocedureName> is called from different query windows at the same time Kindly help with an alternative solution. Regards Anil

It is hard to understand what is going on when you use generic table names. Is <table1> from the code above one of BaseDetlTable1 and BaseHdrTable1? About temp tables: Do you create them using create table statement or select … into? What are indexes on the table1? I guess it is either one of tables mentioned or it is referenced by one of them.

Also how long does this proc take to run, do you know if it can be optimised further? Have you run profiler to see if any statement within the proc is taking more than say 50ms? The number one reason for deadlocks is performance. If everything happened in a millisecond, then the chance of two thngs happening at exactly the same time is very small. If you must avoid deadlocks at all cost, then you’d need to create a semaphore. The easiest way in sql2k may be to create a dummy table with a single row, and update that row just after you start the transaction. Then all other calls to this proc would wait in a single file queue Cheers
Twan

Dear mmarovic The table I mentioned by the name <Table1> is neither my header table nor my detail table.It is just another table that is updated as a part of the execution of my sp (PBLMSP) I create temp tables using Create Table Statement
<Table1> has got two indexes.One is a clustered index on One Field and Another is a non clustered index on 3 fields Hope this clears your doubts.
I am ready to provide with whatever information related to this issue I want to kill this DeadLock Regards
Anil

I’ve just noticed something I overlooked before:

quote:
In this passion it goes on for 10 tables and an update happens for 1 table in between
Commit Tran

Without knowing what are other tables mentioned and what is their relationship with table1 from another query, I can only repeat what Twan already said. Optimize the procedure and make sure you have proper indexes. I am sure index on lnkID speeded-up execution of insert mentioned unless it is really small table. You should make sure you have indexes on join columns on the permanent tables that are joined with your temp table for each insert that is a part of the transaction. About update statetment, you should find the code of the complete transaction this update is part of. It may be that previous statetments locked the table that the first procedure needs to access next.

quote:
<Table1> has got two indexes.One is a clustered index on One Field and Another is a non clustered index on 3 fields

That doesn’t help much. I need to know each index structure. I mean what are columns and thier order for each index.
For the update query index on 3 columns from where clause would be perfect.

Dear mmarovic The Columns that forms the part of index for <Table1> are
BrID Ascending
BusnsDt Ascending
GLHeadID Ascending As I already told the number of records varies.
The maximum can be 2000 and minimum can be 150.
If the number of records is 150 the execution time of the SP is 5 Secs.
If the number of records is 2000 the execution time of the SP varies between 1 mts 30 secs to 2 mts 30 secs. For 150 records itself execution of multiple instance of PBLMSP throws Lock Error. But Now we have reached a point in which the single execution of PBLMSP won’t affect other users transactions.Formerly other users might get a Lock Request Time-out Error.Now it is being addressed.Courtsy to good people like You Regards
Anil

Anil thank you for the kind words. I hope it was something we mentioned that helped you address the issue. Cheers,
Mirko.

Dear Mirko Very Nice Of You to comment.
Now I am after the task of executing Multiple Instances of PBLMSP while multiple users are performing Different transactions.I may be disturbing you furthur.Do help Me Thanks a Lot
Anil

Hi Anil, Can you use profiler to get a duration for each statement within the proc, especially any over 50ms. also ultimately (esp if profiler shows no statements > 50ms) you may have to post the entire proc, as there may well be better ways to do the whole thing (rather than us optimize one statement at a time) Cheers
Twan

Sure. That’s what we do here. As Twan said, the sp code and statetements duration and maybe number of reads/writes would give us information we can use to help you.
You can see what we are capable of from another thread where sp code was posted http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14354).

Dear Mirko And TWan I am Placing My PBLM SP so that you can have a closer look at it. alter PROCEDURE DDC_SUpdate ( @P_BrID UDVC25, @P_DDCID UDVC25, @P_SLNO UDVC250, @P_BUSNSDT UDVC25, @P_CRTDBY UDVC250 ) As Begin–1 SET NOCOUNT ON Declare @M_RecordCount as integer Declare @M_Loopvar as Integer Declare @M_AcctId as varchar(50) Declare @M_PrdId as varchar(50) Declare @M_TransAmt as varchar(50) Declare @M_BusnsDt as UDDT Declare @M_DDCID as varchar(50) [email protected]_InsgroupId as Varchar(50) Declare @M_ErrorFlag as UDCH1 Declare @M_UniqID as BIGINT Declare @M_CyID as varchar(50), [email protected]_NxtInsGrpID UDBINT, @m_NxtInsGrpID UDVC5, @m_InsGrpCurValUDBINT, @m_MaxPK_ID as BIGINT, @m_MinPK_ID as BIGINT, @m_MainPrdID as UDVC25, @m_OppPrdID as UDVC25, @m_FlagVal1 As UDVC10, @m_FlagVal2 As UDVC10, @m_MainAmt As UDLDGRAMT, @m_OppAmt As UDLDGRAMT, @m_NxtTxnID UDVC25, @m_TxnIDCurVal UDVC25, @m_TxnIDHdr UDVC25, @m_TxnIDSlNo BIGINT SET @M_CyID = 1 Declare @m_TempTxnId as UDBINT Declare @m_OperationType as UDVC25 Declare @m_OperationId as UDVC25 Declare @m_OptionType as UDVC25 [email protected]_OptnTypeID as Int Declare @m_OptionIdas Udvc16 [email protected]_SettlementId as Udvc16 [email protected]_SettlementOptionName as Udvc25 Declare @m_NarrationasNvarchar(100) [email protected]_TxnPrdidas Udvc16 [email protected]_ContraOperationId as Udvc16 Declare @m_TxnType as UDCH1 Declare @m_GLHeadIdCD as UDCH1 Declare @m_VoucherType as Nvarchar(5) Declare @m_DDCTrackId as numeric(24) Declare @m_BranchFKID as udvc16 Declare @m_ProductFKID as UDVC16 Declare @m_CurrencyFKID as UDVC16 Declare @m_OppositeGLHeadID as UDVC16 Declare @m_InsGrpSlNo as udvc16 Declare @m_Realise as UDCH1 declare @m_OprBrID as UDVC16 [email protected]_DrAmtas udnu124 [email protected]_CrAmtas udnu124 Declare @m_OppAcctId as UDVC16 Declare @m_OperationType_DD as UDVC25 Declare @m_OperationId_DD as UDVC25 Declare @m_OptionType_DD as UDVC25 [email protected]_OptnTypeID_DD as Int Declare @m_OptionId_DDas Udvc16 [email protected]_SettlementId_DD as Udvc16 [email protected]_SettlementOptionName_DD as Udvc25 Declare @m_Narration_DDasNvarchar(100) [email protected]_TxnPrdid_DDas Udvc16 Declare @m_OppAcctId_DD as UDVC16 [email protected]_ContraOperationId_DD as Udvc16 Declare @m_TxnType_DD as UDCH1 Declare @m_GLHeadIdCD_DD as UDCH1 Declare @m_VoucherType_DD as Nvarchar(5) Declare @m_OppositeGLHeadID_DD as UDVC16 [email protected]_TransAmt_DD as Udnu124 [email protected]_DrAmt_DD as Udnu124 [email protected]_CrAmt_DDas udnu124 Declare @HeadType_DD as Udvc5 Declare @m_OperationType_NOSB as UDVC25 Declare @m_OperationId_NOSB as UDVC25 Declare @m_OptionType_NOSB as UDVC25 [email protected]_OptnTypeID_NOSB as Int Declare @m_OptionId_NOSBas Udvc16 [email protected]_SettlementId_NOSB as Udvc16 [email protected]_SettlementOptionName_NOSB as Udvc25 Declare @m_Narration_NOSBasNvarchar(100) [email protected]_TxnPrdid_NOSBas Udvc16 Declare @m_OppAcctId_NOSB as UDVC16 [email protected]_ContraOperationId_NOSB as Udvc16 Declare @m_TxnType_NOSB as UDCH1 Declare @m_GLHeadIdCD_NOSB as UDCH1 Declare @m_VoucherType_NOSB as Nvarchar(5) Declare @m_OppositeGLHeadID_NOSB as UDVC16 [email protected]_TransAmt_NOSB as Udnu124 [email protected]_DrAmt_NOSB as Udnu124 [email protected]_CrAmt_NOSBas udnu124 Declare @HeadType_NOSB as Udvc5 Declare @m_OperationType_HB as UDVC25 Declare @m_OperationId_HB as UDVC25 Declare @m_OptionType_HB as UDVC25 [email protected]_OptnTypeID_HB as Int Declare @m_OptionId_HBas Udvc16 [email protected]_SettlementId_HB as Udvc16 [email protected]_SettlementOptionName_HB as Udvc25 Declare @m_Narration_HBasNvarchar(100) [email protected]_TxnPrdid_HBas Udvc16 Declare @m_OppAcctId_HB as UDVC16 [email protected]_ContraOperationId_HB as Udvc16 Declare @m_TxnType_HB as UDCH1 Declare @m_GLHeadIdCD_HB as UDCH1 Declare @m_VoucherType_HB as Nvarchar(5) Declare @m_OppositeGLHeadID_HB as UDVC16 [email protected]_TransAmt_HB as Udnu124 [email protected]_DrAmt_HB as Udnu124 [email protected]_CrAmt_HBas udnu124 Declare @HeadType_HB as Udvc5 Declare @NOSBOPPPrdID as udvc16 [email protected] as Udvc16 [email protected] as Udvc16 Declare @DDOPPPrdID as udvc16 [email protected] as Udvc16 [email protected] as Udvc16 Declare @HBOPPPrdID as udvc16 [email protected] as Udvc16 [email protected] as Udvc16 [email protected]=’733′ [email protected]=’1001′ [email protected]=’011.000′ [email protected]=’1028′ [email protected]=’1002′ [email protected]=’009.000′ [email protected] =” [email protected] =” [email protected]=” Create table #TempGENERAL ( PKIDInt Identity(1,1),BrIDUDvc16, PrdidUDVC25,Acctid Udvc25,InsGrpIdUDBINT, InsGrpSlno UDVC25,TxnType UDCH1, RealizeUDCH1 Default ‘A’,BusnsDtUDDT,OprBrIDUDVC16, TxnAmtUDLDGRAMT,ValDtUDDT, Purpose Nvarchar(5), RVERSIONUDRVER Default getdate(), GlHeadId nvarchar(25), OPPPrdidUDVC25, OppAcctIdUDVC16 ) CREATE TABLE#TempDEP_tInstLdgrHdr ( PKIDInt Identity(1,1),BrIDUDVC16,Br_FKID UDFKID, PrdIDUDVC16,Prd_FKID UDFKID, Cy_FKIDUDFKID,CyID UDVC10, OprBrIDUDVC16,OprBr_FKID UDFKID, TxnIDHdrUDVC5 ,TxnIDSlNoUDBINT , InsGrpIDUDIGrpID,AcctIDUDVC16, MnLdgrInsGrpIDUDIGrpID,MnLdgrInsGrpSlNoUDIGrpSlNo, OprTypeUDVC25,OprIDUDVC25, JVNoUDVC16,FASTxnIDUDBInt, FASSubTxnIDUDBINT,BusnDtUDDT, ApproveUDCH1,RejectUDCH1, SendToEditUDCH1,CrtdDtUDDT, CrtdByUDVC25,LstModDtUDDT, LstModByUDVC25,ApprvdDtUDDT, ApprvdByUDVC25,LstApprvdDtUDDT, LstApprvdByUDVC25,RVERSIONUDRVER, FrmIDHUDCH1,TxnSourceUDCH1, DDCIDUDVC16,DDCTrackId numeric(1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) Create table #TempDEP_tInstLdgrDet ( PKIDInt Identity(1,1),TxnIDSubSlNoUDBINT, OptnTypeUDVC100,OptnTypeIDUDVC25, OptnIDUDVC25,ContraOprtnIDUDVC10, SetlmntIDUDVC25,SetlmntNameUDVC100, InstrNoUDVC10,InstrDtUDDT, DrawnBnkUDVC50,DrawnBrUDVC50, InstrDepBnkUDVC50,TxnIDHdrUDVC5, TxnIDSlNoUDBINT,FASTxnIDUDBINT, FASSubTxnIDUDBINT,MnLdgrInsGrpIDUDIGrpID, MnLdgrInsGrpSlNo UDIGrpSlNo,RefTxnIDUDVC5, RefSubTxnIDUDBINT,TxnAmtUDLDGRAMT, ValueDtUDDT,AvailDtUDDT, ProcessingDtUDDT,NarrationUDTEXT, RealisedUDCH1,RealisedDtUDDT, TxnBrIDUDVC16,TxnPrdIDUDVC16, TxnAcctNoUDVC16,TxnJVNoUDVC16, InsGrpSlNoUDIGrpSlNo,RVERSIONUDRVER, TxnTypeUDCH1,TokenNoUDBINT,DDCTrackId numeric(1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) Create table #TempINT_tInstLdgrHdr ( BrIDUDVC16,Br_FKIDUDFKID, PrdIDUDVC16,Prd_FKIDUDFKID, Cy_FKIDUDFKID,CyIDUDVC5, OprBrIDUDVC16,OprBr_FKIDUDFKID, TxnIDHdrUDVC10,TxnIDSlNoUDVC10, InsGrpIDUDIGrpID,AcctIDUDVC16, MnLdgrInsGrpIDUDIGrpID,MnLdgrInsGrpSlNoUDIGrpSlNo, OprTypeUDVC16,OprIDUDVC16, JVNoUDVC16,FASTxnIDUDBINT, FASSubTxnIDUDVC25,BusnDtUDDT, FrmIDHUDCH1,ApproveUDCH1, RejectUDCH1,SendToEditUDCH1, CrtdDtUDDT,CrtdByUDVC25, LstModDtUDDT,LstModByUDVC25, ApprvdDtUDDT,ApprvdByUDVC25, LstApprvdDtUDDT,LstApprvdByUDVC25, RVERSIONUDRVER,TxnSourceUDCH1, SunRevBrIdUDVC16,SunRevBusnsDtUDDT, SunRevTxnIdUDIGrpID Default 0,SunReversedUDCH1, DDCTrackId numeric(1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) Create table #TempINT_tInstLdgrDet ( TxnInsHdr_FKIDUDFKID,TxnIDSubSlNoUDBINT, OptnTypeUDVC100,OptnTypeIDUDVC25, OptnIDUDVC25,ContraOprtnIDUDVC10, SetlmntIDUDVC25,SetlmntNameUDVC100, InstrNoUDVC10,InstrDtUDDT, DrawnBnkUDVC50,DrawnBrUDVC50, InstrDepBnkUDVC50,TxnIDHdrUDVC5, TxnIDSlNoUDBINT,FASTxnIDUDBINT, FASSubTxnIDUDVC25,MnLdgrInsGrpIDUDIGrpID, MnLdgrInsGrpSlNoUDIGrpSlNo,RefTxnIDUDVC25, RefSubTxnIDUDVC25,TxnAmtUDLDGRAMT, ValueDtUDDT,AvailDtUDDT, ProcessingDtUDDT,NarrationUDTEXT, RealisedUDCH1,RealisedDtUDDT, TxnBrIDUDVC16,TxnPrdIDUDVC16, TxnAcctNoUDVC16,TxnJVNoUDVC16, InsGrpSlNoUDIGrpSlNo,RVERSIONUDRVER, TxnTypeUDCH1,TokenNoUDBINT,DDCTrackId numeric(1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) Create table #TempFAS_TDAYTRANS ( BrIDUDVC16,Br_FKIDUDFKID, OprtngBrIDUDVC25,TxnIdHdrUDVC5, TxnIdSlNoUDBINT,TxnIdSubSlNo Int IDENTITY(1,1), VchTypeUDVC5,VchNoUDBINT, BusnsDtUDDT,PrdIDUDVC16, Prd_FKIDUDFKID,GLHeadIDUDVC25, GLHead_FKIDUDFKID,AcctIDUDVC25, AcctBrIDUDVC25,AcctBr_FKIDUDFKID, ValDtUDDT,AvalDtUDDT, TxnTypeUDCH1,DrAmtUDLDGRAMT, CrAmtUDLDGRAMT,CyIDUDVC5, Cy_FKIDUDFKID,NarrationUDTEXT, InitFromUDVC25,VrfydUDCH1, ReversedUDCH1,Re vTxnIDUDVC5, RevSrcDtUDDT,RevStatusUDVC10, ProcessingDtUDDT,SendToEditUDCH1, ApproveUDCH1,RejectUDCH1, CrtdByUDVC25,CrtdDtUDDT, LstModByUDVC25,LstModDtUDDT, ApprvdByUDVC25,ApprvdDtUDDT, LstApprvdByUDVC25,LstApprvdDtUDDT, RVERSIONUDRVER Default getdate() ) Create Table #TempFAS_tYearTrans ( BrIDUDVC25,Br_FKIDUDFKID, OprtngBrIDUDVC25,TxnIdHdrUDVC25, TxnIdSlNoUDBINT,TxnIdSubSlNoUDBINT, VchTypeUDVC5,VchNoUDBINT, BusnsDtUDDT,PrdIDUDVC16, Prd_FKIDUDFKID,GLHeadIDUDVC25, GLHead_FKIDUDFKID,AcctIDUDVC25, AcctBrIDUDVC25,AcctBr_FKIDUDFKID, ValDtUDDT,AvalDtUDDT, TxnTypeUDCH1,DrAmtUDLDGRAMT, CrAmtUDLDGRAMT,CyIDUDVC5, Cy_FKIDUDFKID,NarrationUDTEXT, InitFromUDVC25,VrfydUDCH1, ReversedUDCH1,RevTxnIDUDVC5, RevSrcDtUDDT,RevStatusUDVC10, ProcessingDtUDDT,SendToEditUDCH1, ApproveUDCH1,RejectUDCH1, CrtdByUDVC25,CrtdDtUDDT, LstModByUDVC25,LstModDtUDDT, ApprvdByUDVC25,ApprvdDtUDDT, LstApprvdByUDVC25,LstApprvdDtUDDT, RVERSIONUDRVER Default getdate() ) Createtable #TempDEP_tAcctBaln ( BrID_FKUDVC16,BrIDUDVC16,PrdID_FKUDVC16, PrdIDUDVC16,AcctIDUDVC16, LimitAmtUDNU124,AvailedAmtUDNU124,AvailableAmtUDNU124, BankOSClearUDNU124,CustOSClearUDNU124,TotOSUnClearUDNU124, TotOSUDNU124,TotPrinDueToUDNU124,TotIntDueToUDNU124, TotOthDueToUDNU124,TotPrinDueFrmUDNU124,TotIntDueFrmUDNU124, TotOthDueFrmUDNU124,ClosureBalUDNU124,BusnsDtUDDT, ValueDtUDDT,LstApprvdDtUDDT,LstApprvdByUDVC25, ApprvdByUDVC25,ApproveUDCH1,SendToEditUDCH1, RVersiontimestamp ,DDCTrackId numeric(1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) /* Createtable #TempDEP_tAcctBalnDetl ( OSAmtUDLDGRAMT, ValueDtUDDT,LstApprvdDtUDDT , LstApprvdByUDVC25,DDCTrackId numeric(1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) */ Createtable #TempDEP_tAcctBalnHisthdr ( BrID_FKUDVC16,BrIDUDVC16,PrdID_FKUDVC16, PrdIDUDVC16,AcctID_FKUDVC16,AcctIDUDVC16, LimitAmtUDNU124,AvailedAmtUDNU124,AvailableAmtUDNU124, BankOSClearUDNU124,CustOSClearUDNU124,TotOSUnClearUDNU124, TotOSUDNU124,TotPrinDueToUDNU124,TotIntDueToUDNU124, TotOthDueToUDNU124,TotPrinDueFrmUDNU124,TotIntDueFrmUDNU124, TotOthDueFrmUDNU124,ClosureBalUDNU124,BusnsDtUDDT, ValueDtUDDT,LstApprvdDtUDDT,LstApprvdByUDVC25, ApprvdByUDVC25,ApproveUDCH1,SendToEditUDCH1, NoofPendTxnUDBINT,RVersiontimestamp,InsGrpIDUDIGrpID, InsGrpSlNoUDIGrpSlNo,BalnSrcTypeUDVC5,LdgrBalnTypeUDCH5, OprBrIDUDVC16,DDCTrackId numeric(1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) Create table#TempDEP_tAcctBalnHistDetl ( BalnHHdr_FkUDFKID,OptnTypeUDVC100, OptnTypeIDUDVC25 ,InsGrpSlNoUDIGrpSlNo, OSAmt UDLDGRAMT,AmtDueToUDLDGRAMT, AmtDueFrmUDLDGRAMT,AdvnColnUDLDGRAMT, ValueDtUDDT,CrtdDtUDDT, CrtdByUDVC25,ApprvdDtUDDT, ApprvdByUDVC25,ApproveUDCH1, SendToEditUDCH1,RejectUDCH1, DDCTrackId numeric(1<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> ) DECLARE @m_DDCInsGrpID UDVC25 DECLARE @m_DDCTxnID UDVC25 DECLARE @m_TotRecCount BIGINT DECLARE @m_TempAcctID UDVC25 Declare @m_TempTxnAmt as UDNU124 SET @[email protected]_BrID + @P_DDCID + @P_SLNO + Left(Replace(@P_BUSNSDT,’-‘,”), 2) + SUBSTRING(Replace(@P_BUSNSDT,’-‘,”), 3, 2) + RIGHT(Replace(@P_BUSNSDT,’-‘,”), 2) DECLARE @m_ValueDt UDDT EXEC SAM_sGetNextVal ‘IGRP’, @P_BrID, @m_NxtInsGrpID Output, @m_InsGrpCurVal Output EXEC SeqInit_SetNextVal @m_NxtInsGrpID, ‘IGRP’ , @P_BrID EXEC SAM_sGetNextVal ‘DEPTXN’,@P_BrID, @m_NxtTxnID Output, @m_TxnIDCurVal Output EXEC SeqInit_SetNextVal @m_TxnIDCurVal, ‘DEPTXN’, @P_BrID SET @m_TxnIDHdr = dbo.FAS_fGetJulianDay(@m_NxtTxnID) SET @m_TxnIDSlNo = dbo.FAS_fGetTxnIDSlNo(@m_NxtTxnID) [email protected]_TempTxnId =Convert(Varchar(25),@m_TxnIDHdr) + Convert(Varchar(25), @m_TxnIDSlNo) set @m_BranchFKID =DBO.FGETBRFK(@p_BrID) [email protected]_CurrencyFKID=dbo.fgetCy(@m_CyID) [email protected]_TempAcctID=’XXX' [email protected]_OptionType=Dbo.SAM_fGetHCOptnTypeName(‘PRIN’) SELECT BrID, PrdID, AcctID, DDCID, TransDate, TransTime, TransAmt, Identity (Int, 1, 1) As UniqID INTO #TempData FROM Dep_tDDCData With (NoLock) WHERE TxnBrID = @p_BrID AND TxnDDCId = @P_DDCID AND SlNo = @P_SlNo AND BusnsDt = dbo.fSetBusnDate(@P_BusnsDt) AndApprove= ‘Y' –Andacctid in (1747)–,5844,6010) Order by UniqID,cast(prdid as int),cast(acctid as int) –select * from #TempData SELECT @m_MaxPK_ID = Max(Prdid), @m_MinPK_ID = MIN(Prdid) From #TempData While @m_MinPK_ID<[email protected]_MaxPK_ID Begin [email protected]_MinPK_ID=2 Begin [email protected]_PrdId=’2′ [email protected]_OperationType_NOSB=BOM.OprtnType , @m_OperationId_NOSB=BOP.MainBOID from SAM_tBOAutoPostDetls BOP With (NoLOCK), SAM_tBusnsOprtnMst BOM With (NoLOCK), SAM_tBusnsOprtnDetls BOD With (NoLOCK) Where BOP.MainPrdID = @M_PrdId And BOP.Purpose = ‘DDCCollection' AndBOP.MainBOID = BOM.OprtnID AndBOM.OprtnID = BOD.OprtnID AndBOP.OptnTypeID = BOD.OptnTypeID AndBOP.MainPrdID = BOM.PrdID Select @m_OptnTypeID_NOSB=BOP.OptnTypeID , @m_OptionId_NOSB=BOD.GLHeadID, @m_SettlementId_NOSB=BOP.SetlmntID , @m_SettlementOptionName_NOSB=BSO.SetlmntName , @m_Narration_NOSB=’Oprtn. ID ‘ + BOP.OppBOID + ‘ ‘, @m_TxnPrdid_NOSB=BOP.OppPrdID , @m_OppAcctId_NOSB=BOP.OppAcctID, @m_ContraOperationId_NOSB=BOP.OppBOID , @m_TxnType_NOSB=BOD.GLHeadIdCD, @m_GLHeadIdCD_NOSB=BOD.GLHeadIDCD, @m_VoucherType_NOSB =BSO.VchType From SAM_tBOAutoPostDetls BOP with (NoLock), SAM_tBusnsOprtnDetls BOD with (NoLock), SAM_tBusnsSetlmntOptns BSO with (NoLock) WhereBOP.MainPrdID= @M_PrdId And BOP.Purpose = ‘DDCCollection' AndBOP.OptnTypeID = BOD.OptnTypeID AndB OP.MainBOID = BOD.OprtnID AndBSO.OprtnID= BOP.MainBOID AndBSO.SetlmntId = BOP.SetlmntId Select @m_OppositeGLHeadID_NOSB=GLHEADID from SAM_tBusnsOprtnDetls With (NoLock) Where OprtnID [email protected]_ContraOperationId_NOSB [email protected]_OptnTypeID_NOSB [email protected]_MinPK_ID=6 –To ease the loop as I know the products are 2,7&69 End [email protected]_MinPK_ID=7 Begin [email protected]_PrdId=’7′ [email protected]_OperationType_DD=BOM.OprtnType , @m_OperationId_DD=BOP.MainBOID from SAM_tBOAutoPostDetls BOP With (NoLOCK), SAM_tBusnsOprtnMst BOM With (NoLOCK), SAM_tBusnsOprtnDetls BOD With (NoLOCK) Where BOP.MainPrdID = @M_PrdId And BOP.Purpose = ‘DDCCollection' AndBOP.MainBOID = BOM.OprtnID AndBOM.OprtnID = BOD.OprtnID AndBOP.OptnTypeID = BOD.OptnTypeID AndBOP.MainPrdID = BOM.PrdID Select @m_OptnTypeID_DD=BOP.OptnTypeID , @m_OptionId_DD=BOD.GLHeadID, @m_SettlementId_DD=BOP.SetlmntID , @m_SettlementOptionName_DD=BSO.SetlmntName , @m_Narration_DD=’Oprtn. ID ‘ + BOP.OppBOID + ‘ ‘, @m_TxnPrdid_DD=BOP.OppPrdID , @m_OppAcctId_DD=BOP.OppAcctID, @m_ContraOperationId_DD=BOP.OppBOID , @m_TxnType_DD=BOD.GLHeadIdCD, @m_GLHeadIdCD_DD=BOD.GLHeadIDCD, @m_VoucherType_DD =BSO.VchType From SAM_tBOAutoPostDetls BOP with (NoLock), SAM_tBusnsOprtnDetls BOD with (NoLock), SAM_tBusnsSetlmntOptns BSO with (NoLock) WhereBOP.MainPrdID= @M_PrdId And BOP.Purpose = ‘DDCCollection' AndBOP.OptnTypeID = BOD.OptnTypeID AndBOP.MainBOID = BOD.OprtnID AndBSO.OprtnID= BOP.MainBOID AndBSO.SetlmntId = BOP.SetlmntId Select @m_OppositeGLHeadID_DD=GLHEADID from SAM_tBusnsOprtnDetls With (NoLock) Where OprtnID [email protected]_ContraOperationId_DD [email protected]_OptnTypeID_DD [email protected]_MinPK_ID=68 –To ease the loop as I know the products are 2,7&69 End [email protected]_MinPK_ID=69 Begin [email protected]_PrdId=’69′ [email protected]_OperationType_HB=BOM.OprtnType , @m_OperationId_HB=BOP.MainBOID from SAM_tBOAutoPostDetls BOP With (NoLOCK), SAM_tBusnsOprtnMst BOM With (NoLOCK), SAM_tBusnsOprtnDetls BOD With (NoLOCK) Where BOP.MainPrdID = @M_PrdId And BOP.Purpose = ‘DDCCollection' AndBOP.MainBOID = BOM.OprtnID AndBOM.OprtnID = BOD.OprtnID AndBOP.OptnTypeID = BOD.OptnTypeID AndBOP.MainPrdID = BOM.PrdID Select @m_OptnTypeID_HB=BOP.OptnTypeID , @m_OptionId_HB=BOD.GLHeadID, @m_SettlementId_HB=BOP.SetlmntID , @m_SettlementOptionName_HB=BSO.SetlmntName , @m_Narration_HB=’Oprtn. ID ‘ + BOP.OppBOID + ‘ ‘, @m_TxnPrdid_HB=BOP.OppPrdID , @m_OppAcctId_HB=BOP.OppAcctID, @m_ContraOperationId_HB=BOP.OppBOID , @m_TxnType_HB=BOD.GLHeadIdCD, @m_GLHeadIdCD_HB=BOD.GLHeadIDCD, @m_VoucherType_HB =BSO.VchType From SAM_tBOAutoPostDetls BOP with (NoLock), SAM_tBusnsOprtnDetls BOD with (NoLock), SAM_tBusnsSetlmntOptns BSO with (NoLock) WhereBOP.MainPrdID= @M_PrdId And BOP.Purpose = ‘DDCCollection' AndBOP.OptnTypeID = BOD.OptnTypeID AndBOP.MainBOID = BOD.OprtnID AndBSO.OprtnID= BOP.MainBOID AndBSO.SetlmntId = BOP.SetlmntId Select @m_OppositeGLHeadID_HB=GLHEADID from SAM_tBusnsOprtnDetls With (NoLock) Where OprtnID [email protected]_ContraOperationId_HB [email protected]_OptnTypeID_HB End Set @[email protected]_MinPK_ID+1 End SELECT @m_MaxPK_ID = Max(UniqID), @m_MinPK_ID = MIN(UniqID) From #TempData SET @[email protected]_DDCInsGrpID SET @[email protected]_MaxPK_ID – @m_MinPK_ID + 1 WHILE (@m_MinPK_ID <= @m_MaxPK_ID) Begin–2 SELECT @M_AcctId = AcctID ,@M_PrdId = PrdID , @M_TransAmt= TransAmt ,@M_BusnsDt =dbo.fSetBusnDate(@P_BusnsDt), @M_DDCID = DDCID,@m_ValueDt = dbo.fSetBusnDate(TransDate), @M_UniqID= UniqID FROM #TempData WHERE UniqID = @m_MinPK_ID [email protected]_ProductFKID=DBO.FGETPRDFK(@M_PrdId) Set @m_DDCTrackId = cast(@[email protected][email protected]_SLNO +cast(@m_MinPK_ID as nvarchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />) as numeric(18,2)) [email protected]_PrdId=’2′ Begin [email protected][email protected]_OperationType_NOSB [email protected][email protected]_OperationId_NOSB [email protected] [email protected]_OptnTypeID_NOSB [email protected][email protected]_OptionId_NOSB [email protected][email protected]_SettlementId_NOSB [email protected][email protected]_SettlementOptionName_NOSB [email protected][email protected]_Narration_NOSB [email protected][email protected]_TxnPrdid_NOSB [email protected][email protected]_OppAcctId_NOSB [email protected][email protected]_ContraOperationId_NOSB [email protected][email protected]_TxnType_NOSB [email protected][email protected]_GLHeadIdCD_NOSB [email protected][email protected]_VoucherType_NOSB [email protected][email protected]_OppositeGLHeadID_NOSB End [email protected]_PrdId=’7′ Begin [email protected][email protected]_OperationType_DD [email protected][email protected]_OperationId_DD [email protected][email protected]_OptnTypeID_DD [email protected][email protected]_OptionId_DD [email protected][email protected]_SettlementId_DD [email protected][email protected]_SettlementOptionName_DD [email protected][email protected]_Narration_DD [email protected][email protected]_TxnPrdid_DD [email protected][email protected]_OppAcctId_DD [email protected][email protected]_ContraOperationId_DD [email protected][email protected]_TxnType_DD [email protected][email protected]_GLHeadIdCD_DD [email protected][email protected]_VoucherType_DD [email protected][email protected]_OppositeGLHeadID_DD End /* [email protected]_PrdId=’69′ Begin [email protected][email protected]_OperationType_HB [email protected][email protected]_OperationId_HB [email protected][email protected]_OptnTypeID_HB [email protected][email protected]_OptionId_HB [email protected][email protected]_SettlementId_HB [email protected][email protected]_SettlementOptionName_HB [email protected][email protected]_Narration_HB [email protected][email protected]_TxnPrdid_HB [email protected][email protected]_OppAcctId_HB [email protected][email protected]_ContraOperationId_HB [email protected][email protected]_TxnType_HB [email protected][email protected]_GLHeadIdCD_HB [email protected][email protected]_VoucherType_HB [email protected][email protected]_OppositeGLHeadID_HB End */ set @m_TxnType=’C' INSERTINTO #TempDEP_tInstLdgrHdr ( BrID,Br_FKID, PrdID,Prd_FKID, Cy_FKID,CyID , OprBrID,OprBr_FKID , TxnIDHdr,TxnIDSlNo, InsGrpID,AcctID, MnLdgrInsGrpID,MnLdgrInsGrpSlNo, RVERSION, OprType,OprID, JVNo,FASTxnID, BusnDt,Approve, Reject,SendToEdit, CrtdDt,CrtdBy, LstApprvdDt,LstApprvdBy, FrmIDH,TxnSource, DDCID,DDCTrackId ) [email protected]_BrIDBrID ,@m_BranchFKID BrFKID , @M_PrdID PrdID ,@m_ProductFKID PrdFKID , @M_CyIDCyID ,@m_CurrencyFKID CyFKID , @P_BrIDOprBrID ,@m_BranchFKID OprBrFKID, @m_TxnIDHdr TxnIDHdr,@m_TxnIDSlNo TxnIDSlNo, @m_NxtInsGrpID InsGrpID,@m_AcctID AcctID , ’0′ MnLdgrInsGrpID,’0′ MnLdgrInsGrpSlNo, getdate(), @m_OperationType OprType,@m_OperationId OprID, 0 As JVNo,@m_NxtInsGrpIDAs FASTxnID, @m_BusnsDtAs BusnsDt,’Y’ As Approve, ’N’ As Reject,’Y’As SendToEdit, @m_BusnsDtAs CrtdDt,@P_CrtdBy As CrtdBy, @m_BusnsDtAs LstApprvdDt, @P_CrtdBy As LstApprvdBy, ’N’As FrmIDH,’M’As TxnSrc, @P_DDCID As DDCID ,@m_DDCTrackId InsertInto #TempGENERAL ( BrID,Prdid,Acctid,InsGrpId, InsGrpSlno ,TxnType ,BusnsDt, OprBrID,TxnAmt,ValDt, Purpose ,GlHeadId ,OPPPrdid,OppAcctId ) [email protected]_BrIDBrID,@M_PrdID PrdID ,@m_AcctID Acctid,@m_NxtInsGrpID InsGrpID, ’0′ as InsGrpSlno,@m_TxnType TxnType,@m_BusnsDt BusnsDt, @P_BrIDOprBrID,@M_TransAmt as TxnAmt,@m_BusnsDt ValDt, ’DEP’,@m_OptionId,@m_TxnPrdid,@m_OppAcctId Insert Into #TempDEP_tInstLdgrDet ( TxnIDSubSlNo,OptnType,OptnTypeID, OptnID,ContraOprtnID,SetlmntID, SetlmntName,InstrNo,InstrDt, DrawnBnk,DrawnBr,InstrDepBnk, TxnIDHdr,TxnIDSlNo,/*FASTxnID,*/ FASSubTxnID,MnLdgrInsGrpID,MnLdgrInsGrpSlNo , RefTxnID,RefSubTxnID,TxnAmt, ValueDt,AvailDt,ProcessingDt, Narration,Realised,RealisedDt, TxnBrID,TxnPrdID,TxnAcctNo, TxnJVNo,InsGrpSlNo,RVERSION, TxnType,TokenNo,DDCTrackId ) Select0 as TxnIDSubSlNo,@m_OptionType as OptnType,@m_OptnTypeID as OptnTypeID, @m_OptionId as OptnID ,@m_ContraOperationId as ContraOprtnID,@m_SettlementId as SetlmntID, @m_SettlementOptionName as SetlmntName,” as InstrNo,” as InstrDt, ” as DrawnBnk,” as DrawnBr,” as InstrDepBnk, 0 as TxnIDHdr,0 as TxnIDSlNo/*,@m_NxtInsGrpID as FASTxnID*/, 0 as FASSubTxnID,0 as MnLdgrInsGrpID,0 as MnLdgrInsGrpSlNo, ” as RefTxnID,” as RefSubTxnID,@M_TransAmt as TxnAmt, @m_ValueDt as ValueDt,@m_ValueDt as AvailDt, @m_ValueDt as ProcessingDt ,@m_Narration as Narration, ’Y’ As Realised,@m_ValueDt As RealisedDt, @p_BrID As TxnBrID,@m_TxnPrdidas TxnPrdID, @P_DDCID as TxnAcctNo,0 as TxnJVNo, 1 as InsGrpSlNo,getdate(), @m_TxnType ,” as TokenNo,@m_DDCTrackId INSERT INTO #TempFAS_TDAYTRANS ( BrID ,Br_FKID, OprtngBrID,TxnIdHdr, TxnIdSlNo, VchType,VchNo, BusnsDt,PrdID, Prd_FKID,GLHeadID, GLHead_FKID,AcctID, AcctBrID,AcctBr_FKID, ValDt,AvalDt, TxnType,DrAmt, CrAmt,CyID, Cy_FKID,Narration, InitFrom,Vrfyd, Reversed, CrtdBy,CrtdDt ) [email protected]_BrID as BrID,@m_BranchFKID as Br_FKID, @P_BrID as OprtngBrID,@m_NxtInsGrpID as TxnIdHdr, 0 As TxnIDSlNo, @m_VoucherType as VchType,0 as VchNo, @m_BusnsDtas BusnsDt ,@M_PrdID as PrdID, @m_ProductFKID as Prd_FKID,@m_OptionId as GLHeadID, dbo.SAM_fGetGLHdFK(@m_OptionId) as GLHead_FKID,@m_AcctID AcctID , @P_BrID as AcctBrID, @m_BranchFKID as AcctBr_FKID, @m_BusnsDt as ValDt,@m_BusnsDt as AvalDt, @m_TxnType as TxnType,0 as DrAmt, @M_TransAmt as CrAmt,@M_CyID as Cy_FKID, @m_CurrencyFKID as Cy_FKID,@m_Narration as Narration, ’DEP’ as InitFrom,’N’ as Vrfyd, ’N’ as Reversed, @P_CrtdBy,@m_BusnsDt INSERT INTO #TempFAS_TYearTRANS ( BrID ,Br_FKID, OprtngBrID,TxnIdHdr, TxnIdSlNo, VchType,VchNo, BusnsDt,PrdID,&l t;br />Prd_FKID,GLHeadID, GLHead_FKID,AcctID, AcctBrID,AcctBr_FKID, ValDt,AvalDt, TxnType,DrAmt, CrAmt,CyID, Cy_FKID,Narration, InitFrom,Vrfyd, Reversed, CrtdBy,CrtdDt ) [email protected]_BrID as BrID,@m_BranchFKID as Br_FKID, @P_BrID as OprtngBrID,@m_NxtInsGrpID as TxnIdHdr, 0 As TxnIDSlNo, @m_VoucherType as VchType,0 as VchNo, @m_BusnsDtas BusnsDt ,@M_PrdID as PrdID, @m_ProductFKID as Prd_FKID,@m_OptionId as GLHeadID, dbo.SAM_fGetGLHdFK(@m_OptionId) as GLHead_FKID,@m_AcctID AcctID , @P_BrID as AcctBrID, @m_BranchFKID as AcctBr_FKID, @m_BusnsDt as ValDt,@m_BusnsDt as AvalDt, @m_TxnType as TxnType,0 as DrAmt, @M_TransAmt as CrAmt,@M_CyID as Cy_FKID, @m_CurrencyFKID as Cy_FKID,@m_Narration as Narration, ’DEP’ as InitFrom,’N’ as Vrfyd, ’N’ as Reversed, @P_CrtdBy,@m_BusnsDt set @m_TxnType=’D' INSERTINTO #TempINT_tInstLdgrHdr ( BrID,Br_FKID, PrdID,Prd_FKID, Cy_FKID,CyID , OprBrID,OprBr_FKID , TxnIDHdr,TxnIDSlNo, InsGrpID,AcctID, MnLdgrInsGrpID,MnLdgrInsGrpSlNo, RVERSION, OprType,OprID, JVNo,FASTxnID, BusnDt,Approve, Reject,SendToEdit, CrtdDt,CrtdBy, LstApprvdDt,LstApprvdBy, FrmIDH,TxnSource, DDCTrackId ) [email protected]_BrIDBrID ,@m_BranchFKIDBrFKID , @M_PrdID PrdID ,@m_ProductFKID PrdFKID , @M_CyIDCyID ,@m_CurrencyFKIDCyFKID , @P_BrIDOprBrID ,@m_BranchFKID OprBrFKID, @m_TxnIDHdr TxnIDHdr,@m_TxnIDSlNo TxnIDSlNo, @m_NxtInsGrpID InsGrpID,@m_AcctID AcctID , ’0′ MnLdgrInsGrpID,’0′ MnLdgrInsGrpSlNo, getdate(), @m_OperationType OprType,@m_OperationId OprID, 0 As JVNo,@m_NxtInsGrpIDAs FASTxnID, @m_BusnsDtAs BusnsDt,’Y’ As Approve, ’N’ As Reject,’Y’As SendToEdit, @m_BusnsDtAs CrtdDt,@P_CrtdBy As CrtdBy, @m_BusnsDtAs LstApprvdDt, @P_CrtdBy As LstApprvdBy, ’N’As FrmIDH,’M’As TxnSrc, @m_DDCTrackId InsertInto #TempGENERAL ( BrID,Prdid,Acctid,InsGrpId, InsGrpSlno ,TxnType ,BusnsDt, OprBrID,TxnAmt,ValDt, Purpose ,GlHeadId ,OPPPrdid,OppAcctId ) [email protected]_BrIDBrID,@M_PrdID PrdID ,@m_AcctID Acctid,@m_NxtInsGrpID InsGrpID, ’0′ as InsGrpSlno,@m_TxnType TxnType,@m_BusnsDt BusnsDt, @P_BrIDOprBrID,@M_TransAmt as TxnAmt,@m_BusnsDt ValDt, ’INT’,@m_OptionId,@m_TxnPrdid,@m_OppAcctId Insert Into #TempINT_tInstLdgrDet ( TxnIDSubSlNo,OptnType,OptnTypeID, OptnID,ContraOprtnID,SetlmntID, SetlmntName,InstrNo,InstrDt, DrawnBnk,DrawnBr,InstrDepBnk, TxnIDHdr,TxnIDSlNo,FASTxnID, FASSubTxnID,MnLdgrInsGrpID,MnLdgrInsGrpSlNo , RefTxnID,RefSubTxnID,TxnAmt, ValueDt,AvailDt,ProcessingDt, Narration,Realised,RealisedDt, TxnBrID,TxnPrdID,TxnAcctNo, TxnJVNo,InsGrpSlNo,RVERSION, TxnType,TokenNo,DDCTrackId ) Select0 as TxnIDSubSlNo,@m_OptionType as OptnType,@m_OptnTypeID as OptnTypeID, @m_OptionId as OptnID ,@m_ContraOperationId as ContraOprtnID,@m_SettlementId as SetlmntID, @m_SettlementOptionName as SetlmntName,” as InstrNo,” as InstrDt, ” as DrawnBnk,” as DrawnBr,” as InstrDepBnk, 0 as TxnIDHdr,0 as TxnIDSlNo,@m_NxtInsGrpID as FASTxnID, 0 as FASSubTxnID,0 as MnLdgrInsGrpID,0 as MnLdgrInsGrpSlNo, ” as RefTxnID,” as RefSubTxnID,@M_TransAmt as TxnAmt, @m_ValueDt as ValueDt,@m_ValueDt as AvailDt, @m_ValueDt as ProcessingDt ,@m_Narration as Narration, ’Y’ As Realised,@m_ValueDt As RealisedDt, @p_BrID As TxnBrID,@m_TxnPrdidas TxnPrdID, @P_DDCID as TxnAcctNo,0 as TxnJVNo, @m_NxtInsGrpID as InsGrpSlNo,getdate(), @m_TxnType ,” as TokenNo,@m_DDCTrackId INSERT INTO #TempFAS_TDAYTRANS ( BrID ,Br_FKID, OprtngBrID,TxnIdHdr, TxnIdSlNo, VchType,VchNo, BusnsDt,PrdID, Prd_FKID,GLHeadID, GLHead_FKID,AcctID, AcctBrID,AcctBr_FKID, ValDt,AvalDt, TxnType,DrAmt, CrAmt,CyID, Cy_FKID,Narration, InitFrom,Vrfyd, Reversed, CrtdBy,CrtdDt ) [email protected]_BrID as BrID,@m_BranchFKID as Br_FKID, @P_BrID as OprtngBrID,@m_NxtInsGrpID as TxnIdHdr, 0 As TxnIDSlNo, @m_VoucherType as VchType,0 as VchNo, @m_BusnsDtas BusnsDt ,@M_PrdID as PrdID, @m_ProductFKID as Prd_FKID,@m_OppositeGLHeadID as GLHeadID, dbo.SAM_fGetGLHdFK(@m_OppositeGLHeadID) as GLHead_FKID,@m_AcctID AcctID , @P_BrID as AcctBrID, @m_BranchFKID as AcctBr_FKID, @m_BusnsDt as ValDt,@m_BusnsDt as AvalDt, @m_TxnType as TxnType,@M_TransAmt as DrAmt, 0 as CrAmt,@M_CyID as Cy_FKID, @m_CurrencyFKID as Cy_FKID,@m_Narration as Narration, ’INT’ as InitFrom,’N’ as Vrfyd, ’N’ as Reversed, @P_CrtdBy,@m_BusnsDt INSERT INTO #TempFAS_TYearTRANS ( BrID ,Br_FKID, OprtngBrID,TxnIdHdr, TxnIdSlNo, VchType,VchNo, BusnsDt,PrdID, Prd_FKID,GLHeadID, GLHead_FKID,AcctID, AcctBrID,AcctBr_FKID, ValDt,AvalDt, TxnType,DrAmt, CrAmt,CyID, Cy_FKID,Narration, InitFrom,Vrfyd, Reversed, CrtdBy,CrtdDt ) [email protected]_BrID as BrID,@m_BranchFKID as Br_FKID, @P_BrID as OprtngBrID,@m_NxtInsGrpID as TxnIdHdr, 0 As TxnIDSlNo, @m_VoucherType as VchType,0 as VchNo, @m_BusnsDtas BusnsDt ,@M_PrdID as PrdID, @m_ProductFKID as Prd_FKID,@m_OppositeGLHeadID as GLHeadID, dbo.SAM_fGetGLHdFK(@m_OppositeGLHeadID) as GLHead_FKID,@m_AcctID AcctID , @P_BrID as AcctBrID, @m_BranchFKID as AcctBr_FKID, @m_BusnsDt as ValDt,@m_BusnsDt as AvalDt, @m_TxnType as TxnType,@M_TransAmt as DrAmt, 0 as CrAmt,@M_CyID as Cy_FKID, @m_CurrencyFKID as Cy_FKID,@m_Narration as Narration, ’INT’ as InitFrom,’N’ as Vrfyd, ’N’ as Reversed, @P_CrtdBy,@m_BusnsDt [email protected][email protected][email protected][email protected]_PrdID Begin [email&# 160;protected][email protected][email protected]_TransAmt Update#TempDEP_tAcctBaln [email protected]_TempTxnAmt, [email protected]_TempTxnAmt [email protected]_BrID [email protected]_PrdID [email protected]_AcctID End Else Begin [email protected]_TempTxnAmt=0 InsertInto #TempDEP_tAcctBaln ( BrID_FK,BrID,PrdID_FK, PrdID,AcctID, LimitAmt,AvailedAmt,AvailableAmt, BankOSClear,CustOSClear,TotOSUnClear, TotOS,TotPrinDueTo,TotIntDueTo, TotOthDueTo,TotPrinDueFrm,TotIntDueFrm, TotOthDueFrm,ClosureBal,BusnsDt, ValueDt,LstApprvdDt,LstApprvdBy, ApprvdBy,Approve,SendToEdit, DDCTrackId ) [email protected]_BranchFKID,@P_BrID,@m_ProductFKID, @M_PrdID,@m_AcctID, 0,@M_TransAmt,0, 0,0,0, @M_TransAmt,0,0, 0,0,0, 0,0,@m_BusnsDt, @m_BusnsDt,@m_BusnsDt,@P_CrtdBy, @P_CrtdBy,’A’,’N’, @m_DDCTrackId End InsertInto #TempDEP_tAcctBalnHisthdr ( BrID_FK,BrID,PrdID_FK, PrdID,AcctID, LimitAmt,AvailedAmt,AvailableAmt, BankOSClear,CustOSClear,TotOSUnClear, TotOS,TotPrinDueTo,TotIntDueTo, TotOthDueTo,TotPrinDueFrm,TotIntDueFrm, TotOthDueFrm,ClosureBal,BusnsDt, ValueDt,LstApprvdDt,LstApprvdBy, ApprvdBy,Approve,SendToEdit, DDCTrackId,InsGrpID,InsGrpSlNo ) [email protected]_BranchFKID,@P_BrID,@m_ProductFKID, @M_PrdID,@m_AcctID, 0,@M_TransAmt,0, 0,0,0, @M_TransAmt,0,0, 0,0,0, 0,0,@m_BusnsDt, @m_BusnsDt,@m_BusnsDt,@P_CrtdBy, @P_CrtdBy,’A’,’N’, @m_DDCTrackId,@m_NxtInsGrpID,1 Insert Into #TempDEP_tAcctBalnHistDetl ( OptnType, OptnTypeID,InsGrpSlNo, OSAmt ,AmtDueTo, AmtDueFrm,AdvnColn, ValueDt,CrtdDt, CrtdBy,ApprvdDt, ApprvdBy,Approve, SendToEdit,Reject, DDCTrackId ) [email protected]_OptionType , @m_OptnTypeID ,@m_NxtInsGrpID , @M_TransAmt,@M_TransAmt, 0 ,0, @m_BusnsDt ,@m_BusnsDt , @P_CrtdBy ,@m_BusnsDt, @P_CrtdBy ,@m_BusnsDt, ’N’ ,’N’, @m_DDCTrackId set @[email protected]_DDCTrackId+1 Set @m_MinPK_ID = @m_MinPK_ID + 1 End–2 end loop –Select * from #TempDEP_tAcctBaln SET TRANSACTION ISOLATION LEVEL READ COMMITTED Begin Tran INSERTINTO DEP_tInstLdgrHdr ( BrID,Br_FKID, PrdID,Prd_FKID, Cy_FKID,CyID , OprBrID,OprBr_FKID , TxnIDHdr,TxnIDSlNo, InsGrpID,AcctID, MnLdgrInsGrpID,MnLdgrInsGrpSlNo, OprType,OprID, JVNo,FASTxnID, BusnDt,Approve, Reject,SendToEdit, CrtdDt,CrtdBy, LstApprvdDt,LstApprvdBy, FrmIDH,TxnSource, DDCID,DDCTrackId ) SelectBrID ,Br_FKID, PrdID , Prd_FKID, Cy_FKID ,CyID, OprBrID ,OprBr_FKID, TxnIDHdr,TxnIDSlNo, InsGrpID, AcctID, MnLdgrInsGrpID, MnLdgrInsGrpSlNo, OprType, OprID, JVNo, FASTxnID, BusnDt, Approve, Reject, SendToEdit, CrtdDt, CrtdBy, LstApprvdDt, LstApprvdBy, FrmIDH, TxnSource, DDCID,DDCTrackId From#TempDEP_tInstLdgrHdr Insert Into DEP_tInstLdgrDet ( TxnInsHdr_FKID, TxnIDSubSlNo,OptnType,OptnTypeID, OptnID,ContraOprtnID,SetlmntID, SetlmntName,InstrNo,InstrDt, DrawnBnk,DrawnBr,InstrDepBnk, TxnIDHdr,TxnIDSlNo,FASTxnID, FASSubTxnID,MnLdgrInsGrpID,MnLdgrInsGrpSlNo , RefTxnID,RefSubTxnID,TxnAmt, ValueDt,AvailDt,ProcessingDt, Narration,Realised,RealisedDt, TxnBrID,TxnPrdID,TxnAcctNo, TxnJVNo,InsGrpSlNo,RVERSION, TxnType,TokenNo ) Select HDR.Pk_id, TxnIDSubSlNo,OptnType,OptnTypeID, OptnID,ContraOprtnID,SetlmntID, SetlmntName,InstrNo,InstrDt, DrawnBnk,DrawnBr,InstrDepBnk, DET.TxnIDHdr,DET.TxnIDSlNo,DET.FASTxnID, DET.FASSubTxnID,DET.MnLdgrInsGrpID,DET.MnLdgrInsGrpSlNo , RefTxnID,RefSubTxnID,TxnAmt, ValueDt,AvailDt,ProcessingDt, Narration,Realised,RealisedDt, TxnBrID,TxnPrdID,TxnAcctNo, TxnJVNo,InsGrpSlNo,DET.RVERSION, TxnType,TokenNo From #TempDEP_tInstLdgrDet DET,DEP_tInstLdgrHdr HDR with (NoLock) WhereDET.DDCTrackId=HDR.DDCTrackId INSERTINTO INT_tInstLdgrHdr ( BrID,Br_FKID, PrdID,Prd_FKID, Cy_FKID,CyID , OprBrID,OprBr_FKID , TxnIDHdr,TxnIDSlNo, InsGrpID,AcctID, MnLdgrInsGrpID,MnLdgrInsGrpSlNo, OprType,OprID, JVNo,FASTxnID, BusnDt,Approve, Reject,SendToEdit, CrtdDt,CrtdBy, LstApprvdDt,LstApprvdBy, FrmIDH,TxnSource, DDCTrackId ) SelectBrID ,Br_FKID, PrdID , Prd_FKID, Cy_FKID ,CyID, OprBrID ,OprBr_FKID, TxnIDHdr,TxnIDSlNo, InsGrpID, AcctID, MnLdgrInsGrpID, MnLdgrInsGrpSlNo, OprType, OprID, JVNo, FASTxnID, BusnDt, Approve, Reject, SendToEdit, CrtdDt, CrtdBy, LstApprvdDt, LstApprvdBy, FrmIDH, TxnSource, DDCTrackId From#TempInt_tInstLdgrHdr Insert Into Int_tInstLdgrDet ( TxnInsHdr_FKID, TxnIDSubSlNo,OptnType,OptnTypeID, OptnID,ContraOprtnID,SetlmntID, SetlmntName,InstrNo,InstrDt, DrawnBnk,DrawnBr,InstrDepBnk, TxnIDHdr,TxnIDSlNo,FASTxnID, FASSubTxnID,MnLdgrInsGrpID,MnLdgrInsGrpSlNo , RefTxnID,RefSubTxnID,TxnAmt, ValueDt,AvailDt,ProcessingDt, Narr ation,Realised,RealisedDt, TxnBrID,TxnPrdID,TxnAcctNo, TxnJVNo,InsGrpSlNo,RVERSION, TxnType,TokenNo ) Select HDR.Pk_id, TxnIDSubSlNo,OptnType,OptnTypeID, OptnID,ContraOprtnID,SetlmntID, SetlmntName,InstrNo,InstrDt, DrawnBnk,DrawnBr,InstrDepBnk, DET.TxnIDHdr,DET.TxnIDSlNo,DET.FASTxnID, DET.FASSubTxnID,DET.MnLdgrInsGrpID,DET.MnLdgrInsGrpSlNo , RefTxnID,RefSubTxnID,TxnAmt, ValueDt,AvailDt,ProcessingDt, Narration,Realised,RealisedDt, TxnBrID,TxnPrdID,TxnAcctNo, TxnJVNo,InsGrpSlNo,DET.RVERSION, TxnType,TokenNo From #TempInt_tInstLdgrDet DET,Int_tInstLdgrHdr HDR with (NoLock) WhereDET.DDCTrackId=HDR.DDCTrackId INSERT INTO FAS_TDAYTRANS ( BrID ,Br_FKID, OprtngBrID,TxnIdHdr, TxnIdSlNo, VchType,VchNo, BusnsDt,PrdID, Prd_FKID,GLHeadID, GLHead_FKID,AcctID, AcctBrID,AcctBr_FKID, ValDt,AvalDt, TxnType,DrAmt, CrAmt,CyID, Cy_FKID,Narration, InitFrom,Vrfyd, Reversed, CrtdBy,CrtdDt ) Select BrID ,Br_FKID, OprtngBrID,TxnIdHdr, TxnIdSlNo, VchType,VchNo, BusnsDt,PrdID, Prd_FKID,GLHeadID, GLHead_FKID,AcctID, AcctBrID,AcctBr_FKID, ValDt,AvalDt, TxnType,DrAmt, CrAmt,CyID, Cy_FKID,Narration, InitFrom,Vrfyd, Reversed, CrtdBy,CrtdDt from #TempFas_TdayTrans INSERT INTO FAS_TYearTRANS ( BrID ,Br_FKID, OprtngBrID,TxnIdHdr, TxnIdSlNo, VchType,VchNo, BusnsDt,PrdID, Prd_FKID,GLHeadID, GLHead_FKID,AcctID, AcctBrID,AcctBr_FKID, ValDt,AvalDt, TxnType,DrAmt, CrAmt,CyID, Cy_FKID,Narration, InitFrom,Vrfyd, Reversed, CrtdBy,CrtdDt ) Select BrID ,Br_FKID, OprtngBrID,TxnIdHdr, TxnIdSlNo, VchType,VchNo, BusnsDt,PrdID, Prd_FKID,GLHeadID, GLHead_FKID,AcctID, AcctBrID,AcctBr_FKID, ValDt,AvalDt, TxnType,DrAmt, CrAmt,CyID, Cy_FKID,Narration, InitFrom,Vrfyd, Reversed, CrtdBy,CrtdDt From #TempFAS_TYearTRANS InsertInto DEP_tAcctBalnHisthdr ( BrID_FK,BrID,PrdID_FK, PrdID,AcctID_FK,AcctID, LimitAmt,AvailedAmt,AvailableAmt, BankOSClear,CustOSClear,TotOSUnClear, TotOS,TotPrinDueTo,TotIntDueTo, TotOthDueTo,TotPrinDueFrm,TotIntDueFrm, TotOthDueFrm,ClosureBal,BusnsDt, ValueDt,LstApprvdDt,LstApprvdBy, ApprvdBy,Approve,SendToEdit, InsGrpID, InsGrpSlNo,BalnSrcType,LdgrBalnType, OprBrID,DDCTrackId ) SelectBrID_FK,BrID,PrdID_FK, PrdID,AcctID_FK,AcctID, LimitAmt,AvailedAmt,AvailableAmt, BankOSClear,CustOSClear,TotOSUnClear, TotOS,TotPrinDueTo,TotIntDueTo, TotOthDueTo,TotPrinDueFrm,TotIntDueFrm, TotOthDueFrm,ClosureBal,BusnsDt, ValueDt,LstApprvdDt,LstApprvdBy, ApprvdBy,Approve,SendToEdit, InsGrpID, InsGrpSlNo,BalnSrcType,LdgrBalnType, OprBrID,DDCTrackId From#TempDEP_tAcctBalnHisthdr InsertIntoDEP_tAcctBalnHistDetl ( OptnType, OptnTypeID,InsGrpSlNo, OSAmt ,AmtDueTo, AmtDueFrm,AdvnColn, ValueDt,CrtdDt, CrtdBy,ApprvdDt, ApprvdBy,Approve, SendToEdit,Reject, DDCTrackId ) SelectOptnType, OptnTypeID,InsGrpSlNo, OSAmt ,AmtDueTo, AmtDueFrm,AdvnColn, ValueDt,CrtdDt, CrtdBy,ApprvdDt, ApprvdBy,Approve, SendToEdit,Reject, DDCTrackId From#TempDEP_tAcctBalnHistDetl InsertInto INT_tAcctBalnHisthdr ( BrID_FK,BrID,PrdID_FK, PrdID,AcctID_FK,AcctID, LimitAmt,AvailedAmt,AvailableAmt, BankOSClear,CustOSClear,TotOSUnClear, TotOS,TotPrinDueTo,TotIntDueTo, TotOthDueTo,TotPrinDueFrm,TotIntDueFrm, TotOthDueFrm,ClosureBal,BusnsDt, ValueDt,LstApprvdDt,LstApprvdBy, ApprvdBy,Approve,SendToEdit, InsGrpID, InsGrpSlNo,BalnSrcType,LdgrBalnType, OprBrID,DDCTrackId ) SelectBrID_FK,BrID,PrdID_FK, PrdID,AcctID_FK,AcctID, LimitAmt,AvailedAmt,AvailableAmt, BankOSClear,CustOSClear,TotOSUnClear, TotOS,TotPrinDueTo,TotIntDueTo, TotOthDueTo,TotPrinDueFrm,TotIntDueFrm, TotOthDueFrm,ClosureBal,BusnsDt, ValueDt,LstApprvdDt,LstApprvdBy, ApprvdBy,Approve,SendToEdit, InsGrpID, InsGrpSlNo,BalnSrcType,LdgrBalnType, OprBrID,DDCTrackId From#TempDEP_tAcctBalnHisthdr InsertIntoINT_tAcctBalnHistDetl ( OptnType, OptnTypeID,InsGrpSlNo, OSAmt ,AmtDueTo, AmtDueFrm,AdvnColn, ValueDt,CrtdDt, CrtdBy,ApprvdDt, ApprvdBy,Approve, SendToEdit,Reject, DDCTrackId ) SelectOptnType, OptnTypeID,InsGrpSlNo, OSAmt ,AmtDueTo, AmtDueFrm,AdvnColn, ValueDt,CrtdDt, CrtdBy,ApprvdDt, ApprvdBy,Approve, SendToEdit,Reject, DDCTrackId From#TempDEP_tAcctBalnHistDetl SELECT @m_MinPK_ID = MIN(PKID), @m_MaxPK_ID = Max(PKID) From #TempGENERAL WherePurpose=’DEP' –select * from #TempGENERAL [email protected]_MinPK_ID<[email protected]_MaxPK_ID Begin –2 [email protected]_BrID= BT.BrID, @m_PrdID = BT.PrdID, @m_AcctID= BT.AcctID, @m_NxtInsGrpID= BT.InsGrpID, @m_InsGrpSlNo= BT.InsGrpSlNo, @m_TxnType = BT.TxnType, @m_Realise= BT.Realize, @M_BusnsDt= BT.BusnsDt, @m_OprBrID= BT.OprBrID, @M_TransAmt = TxnAmt, @m_ValueDt = ValDt, @m_OptionId=GlHeadId,@m_TxnPrdid=OPPPrdid,@m_OppAcctId=OppAcctId From#TempGENERAL BT WherePKID = @m_MinPK_ID If @m_TxnType=’C' Begin –Position1 –Here is were I am getting The Lock Issue UpdateDEP_tAcctBaln –with (UPDLock) [email protected]_TransAmt, [email protected]_TransAmt, [email protected]_BusnsDt, [email protected]_BusnsDt, [email protected]_BusnsDt, DEP_tAcctBaln.NoofPendTxn=0 [email protected]_BrID [email protected]_PrdID [email protected]_AcctID UpdateDEP_tAcctBalnDetl –with (UPDLock) [email protected]_TransAmt, [email protected]_TransAmt, [email protected]_BusnsDt WhereBalnHdr_FK=(SelectPk_Id FromDEP_tAcctBalnwith (NoLock) [email protected]_BrID [email protected]_PrdID [email protected]_AcctID) [email protected][email protected] And @[email protected] Begin [email protected][email protected][email protected]_TransAmt End [email protected][email protected] And @[email protected] Begin [email protected][email protected][email protected]_TransAmt End [email protected][email protected] And @[email protected] Begin [email protected][email protected][email protected]_TransAmt End End –Position2 –Here is were I am getting The Lock Issue EXEC SAM_sValBaln_UPD @p_BrID, @m_PrdID, @m_AcctID, @m_ValueDt, @M_TransAmt, @m_TxnType,’1′ [email protected][email protected] Begin If @m_TxnType=’D' Begin [email protected]_DrAmt_NOSB [email protected]_DrAmt_NOSB [email protected]_TransAmt [email protected]_DrAmt_DD [email protected]_DrAmt_DD [email protected]_TransAmt [email protected]_DrAmt_HB [email protected]_DrAmt_HB [email protected]_TransAmt End Else Begin [email protected]_CrAmt_NOSB [email protected]_CrAmt_NOSB [email protected]_TransAmt [email protected]_CrAmt_DD [email protected]_CrAmt_DD [email protected]_TransAmt [email protected]_CrAmt_HB [email protected]_CrAmt_HB [email protected]_TransAmt End End If @[email protected] Begin If @m_TxnType=’D' Begin [email protected]_Dr Amt_DD [email protected]_DrAmt_DD [email protected]_TransAmt End Else Begin [email protected]_CrAmt_DD [email protected]_CrAmt_DD [email protected]_TransAmt End End – [email protected][email protected] – Begin – If @m_TxnType=’D' – Begin – [email protected]_DrAmt_HB [email protected]_DrAmt_HB [email protected]_TransAmt – End – Else – Begin – [email protected]_CrAmt_HB [email protected]_CrAmt_HB [email protected]_TransAmt – End – End –exec FAS_sUpdDayBookCnt @p_BrID,@m_OptionId,@m_BusnsDt,@m_DrAmt,@m_CrAmt –exec FAS_sUpdGL @p_BrID,@m_OptionId,@m_BusnsDt,@m_DrAmt,@m_CrAmt [email protected][email protected]_MinPK_ID+1 End–2 UpdateINT_tAcctBaln with (UPDLock) [email protected]_TransAmt_NOSB, [email protected]_TransAmt_NOSB, [email protected]_BusnsDt, [email protected]_BusnsDt, [email protected]_BusnsDt, INT_tAcctBaln.NoofPendTxn=0 [email protected]_BrID [email protected]_TxnPrdid_NOSB [email protected]_OppAcctId_NOSB UpdateINT_tAcctBalnDetl with (UPDLock) [email protected]_TransAmt_NOSB, [email protected]_TransAmt_NOSB, [email protected]_BusnsDt WhereBalnHdr_FK=(SelectPk_Id FromINT_tAcctBalnwith (NoLock) [email protected]_BrID [email protected]_TxnPrdid_NOSB [email protected]_OppAcctId_NOSB) UpdateINT_tAcctBaln with (UPDLock) [email protected]_TransAmt_DD, [email protected]_TransAmt_DD, [email protected]_BusnsDt, [email protected]_BusnsDt, [email protected]_BusnsDt, INT_tAcctBaln.NoofPendTxn=0 [email protected]_BrID [email protected]_TxnPrdid_DD [email protected]_OppAcctId_DD UpdateINT_tAcctBalnDetl with (UPDLock) [email protected]_TransAmt_DD, [email protected]_TransAmt_DD, [email protected]_BusnsDt WhereBalnHdr_FK=(SelectPk_Id FromINT_tAcctBalnwith (NoLock) [email protected]_BrID [email protected]_TxnPrdid_DD [email protected]_OppAcctId_DD) UpdateINT_tAcctBaln with (UPDLock) [email protected]_TransAmt_HB, [email protected]_TransAmt_HB, [email protected]_BusnsDt, [email protected]_BusnsDt, [email protected]_BusnsDt, INT_tAcctBaln.NoofPendTxn=0 [email protected]_BrID [email protected]_TxnPrdid_HB [email protected]_OppAcctId_HB UpdateINT_tAcctBalnDetl with (UPDLock) [email protected]_TransAmt_HB, [email protected]_TransAmt_HB, [email protected]_BusnsDt WhereBalnHdr_FK=(SelectPk_Id FromINT_tAcctBalnwith (NoLock) [email protected]_BrID [email protected]_TxnPrdid_HB [email protected]_OppAcctId_HB) IF EXISTS(SELECT’X’ FROM FAS_tDayBookCnt With (NoLock) WHERE BrId = @p_BrId and GlHeadId = @NOSBGLHeadId and BusnsDt = dbo.fSetBusnDate(@p_BusnsDt)) BEGIN UPDATE FAS_tDayBookCnt SETDrAmt= ISNULL(DrAmt,0) + ISNULL(@m_DrAmt_NOSB ,0), CrAmt= ISNULL(CrAmt,0) + ISNULL(@m_CrAmt_NOSB ,0) FromFAS_tDayBookCnt With (UPDLock) WHEREBrId = @p_BrId ANDGlHeadId = @NOSBGLHeadId ANDBusnsDt = dbo.fSetBusnDate(@p_BusnsDt) End Else Begin INSERT INTO FAS_tDayBookCnt ( BusnsDt,BrID,Br_FKID, GLHeadID,GLHead_FKID,DrAmt, CrAmt ) VALUES ( dbo.fSetBusnDate(@p_BusnsDt), @p_BrId, dbo.SAM_FGETBRFK(@p_BrId), @NOSBGLHeadId, dbo.SAM_fGetGLHdFK(@NOSBGLHeadId),ISNULL(@m_DrAmt_NOSB,0), ISNULL(@m_CrAmt_NOSB,0) ) End IF EXISTS(SELECT’X’ FROM FAS_tDayBookCnt With (NoLock) WHERE BrId = @p_BrId and GlHeadId = @DDGLHeadId and BusnsDt = dbo.fSetBusnDate(@p_BusnsDt)) BEGIN UPDATE FAS_tDayBookCnt SETDrAmt= ISNULL(DrAmt,0) + ISNULL(@m_DrAmt_DD ,0), CrAmt= ISNULL(CrAmt,0) + ISNULL(@m_CrAmt_DD ,0) FromFAS_tDayBookCnt With (UPDLock) WHEREBrId = @p_BrId ANDGlHeadId = @DDGLHeadId ANDBusnsDt = dbo.fSetBusnDate(@p_BusnsDt) End Else Begin INSERT INTO FAS_tDayBookCnt ( BusnsDt,BrID,Br_FKID, GLHeadID,GLHead_FKID,DrAmt, CrAmt ) VALUES ( dbo.fSetBusnDate(@p_BusnsDt), @p_BrId, dbo.SAM_FGETBRFK(@p_BrId), @DDGLHeadId, dbo.SAM_fGetGLHdFK(@DDGLHeadId),ISNULL(@m_DrAmt_DD,0), ISNULL(@m_CrAmt_DD,0) ) End /* IF EXISTS(SELECT’X’ FROM FAS_tDayBookCnt With (NoLock) WHERE BrId = @p_BrId and GlHeadId = @HBGLHeadId and BusnsDt = dbo.fSetBusnDate(@p_BusnsDt)) BEGIN UPDATE FAS_tDayBookCnt SETDrAmt= ISNULL(DrAmt,0) + ISNULL(@m_DrAmt_HB ,0), CrAmt= ISNULL(CrAmt,0) + ISNULL(@m_CrAmt_HB ,0) FromFAS_tDayBookCnt With (RowLock) WHEREBrId = @p_BrId ANDGlHeadId = @HBGLHeadId ANDBusnsDt = dbo.fSetBusnDate(@p_BusnsDt) End Else Begin INSERT INTO FAS_tDayBookCnt ( BusnsDt,BrID,Br_FKID, GLHeadID,GLHead_FKID,DrAmt, CrAmt ) VALUES ( dbo.fSetBusnDate(@p_BusnsDt), @p_BrId, dbo.SAM_FGETBRFK(@p_BrId), @HBGLHeadId, dbo.SAM_fGetGLHdFK(@HBGLHeadId),ISNULL(@m_DrAmt_HB,0), ISNULL(@m_CrAmt_HB,0) ) End */ –Updation Of Fas_Tgl Begins [email protected]_NOSB = HeadType FROM FAS_tGLHead With (NoLock) WHERE GLHeadId = @m_GLHeadIdCD_NOSB IF @HeadType_NOSB = ‘D' BEGIN UPDATE FAS_tGLwith (UPDLOCK) SETDrAmt= ISNULL(DrAmt,0) + ISNULL(@m_DrAmt_NOSB,0), CrAmt= ISNULL(CrAmt,0) + ISNULL(@m_CrAmt_NOSB,0), BalAmt= ISNULL(BalAmt,0) + ISNULL(@m_DrAmt_NOSB,0) – ISNULL(@m_CrAmt_NOSB,0) WHERE BrId = @p_BrId AND GlHeadId= @m_GLHeadIdCD_NOSB AND BusnsDt = dbo.fSetBusnDate(@p_BusnsDt) END ELSE IF @HeadType_NOSB = ‘C' BEGIN UPDATE FAS_tGL with (UPDLOCK) SETDrAmt= ISNULL(DrAmt,0) + ISNULL(@m_DrAmt_NOSB,0), CrAmt= ISNULL(CrAmt,0) + ISNULL(@m_CrAmt_NOSB,0), BalAmt= ISNULL(BalAmt,0) – ISNULL(@m_DrAmt_NOSB,0) + ISNULL(@m_CrAmt_NOSB,0) WHERE BrId = @p_BrId AND GlHeadId= @m_GLHeadIdCD_NOSB AND BusnsDt = dbo.fSetBusnDate(@p_BusnsDt) END [email protected]_DD = HeadType FROM FAS_tGLHead With (NoLock) WHERE GLHeadId = @m_GLHeadIdCD_DD IF @HeadType_DD = ‘D' BEGIN UPDATE FAS_tGLwith (UPDLOCK) SETDrAmt= ISNULL(DrAmt,0) + ISNULL(@m_DrAmt_DD,0), CrAmt= ISNULL(CrAmt,0) + ISNULL(@m_CrAmt_DD,0), BalAmt= ISNULL(BalAmt,0) + ISNULL(@m_DrAmt_DD,0) – ISNULL(@m_CrAmt_DD,0) WHERE BrId = @p_BrId AND GlHeadId= @m_GLHeadIdCD_DD AND BusnsDt = dbo.fSetBusnDate(@p_BusnsDt) END ELSE IF @HeadType_DD = ‘C' BEGIN UPDATE FAS_tGL with (UPDLOCK) SETDrAmt= ISNULL(DrAmt,0) + ISNULL(@m_DrAmt_DD,0), CrAmt= ISNULL(CrAmt,0) + ISNULL(@m_CrAmt_DD,0), BalAmt= ISNULL(BalAmt,0) – ISNULL(@m_DrAmt_DD,0) + ISNULL(@m_CrAmt_DD,0) WHERE BrId = @p_BrId AND GlHeadId= @m_GLHeadIdCD_DD AND BusnsDt = dbo.fSetBusnDate(@p_BusnsDt) END /* [email protected]_HB = HeadType FROM FAS_tGLHead With (NoLock) WHERE GLHeadId = @m_GLHeadIdCD_HB IF @HeadType_HB = ‘D' BEGIN UPDATE FAS_tGLwith (UPDLOCK) SETDrAmt= ISNULL(DrAmt,0) + ISNULL(@m_DrAmt_HB,0), CrAmt= ISNULL(CrAmt,0) + ISNULL(@m_CrAmt_HB,0), BalAmt= ISNULL(BalAmt,0) + ISNULL(@m_DrAmt_HB,0) – ISNULL(@m_CrAmt_DD,0) WHERE BrId = @p_BrId AND GlHeadId= @m_GLHeadIdCD_HB AND BusnsDt = dbo.fSetBusnDate(@p_BusnsDt) END ELSE IF @HeadType_HB = ‘C' BEGIN UPDATE FAS_tGL with (UPDLOCK) SETDrAmt= ISNULL(DrAmt,0) + ISNULL(@m_DrAmt_HB,0), CrAmt= ISNULL(CrAmt,0) + ISNULL(@m_CrAmt_HB,0), BalAmt= ISNULL(BalAmt,0) – ISNULL(@m_DrAmt_HB,0) + ISNULL(@m_CrAmt_DD,0) WHERE BrId = @p_BrId AND GlHeadId= @m_GLHeadIdCD_HB AND BusnsDt = dbo.fSetBusnDate(@p_BusnsDt) END */ /* DELETE FROM Dep_tDDCData with (UpdLock) WHERE TxnBrID = @p_BrID AND TxnDDCId = @P_DDCID AND SlNo = @P_SlNo AND BusnsDt = dbo.fSetBusnDate(@P_BusnsDt) AND Approve= ‘Y’ UPDATE DEP_tDDCUpLoad WITH (UPDLOCK) SET IsPost = ‘Y' WHERE BrID = @p_BrID AND DDCId = @P_DDCID AND SlNo = @P_SlNo AND BusnsDt = dbo.fSetBusnDate(@P_BusnsDt) */ Commit Tran If @@error<>0 BEgin Rollback tran End Select -1 as ‘Msg' SET NOCOUNT OFF End–1 In the ABove SP I have marked Position1 and Position2 where the Dead Lock Occurs.Kindly Assist me with a solution. With Deep Regards Anil

do you have to issue 10 inserts before commit or you can commit each insert? if you can, use begin tran/commit/if @@error on each insert. Since you already set the isolation level to Read Committed and transaction, i suggest you avoid the hints on the statement (except in the SELECT but you have to check the tables of other insert). May the Almighty God bless us all!
www.empoweredinformation.com

Hi Anil. I’ve just started looking into your code. Since it is really lengthy it would be nice if you could mark long running parts.
For now, you have one select … into. I would start with replacing this select … into #tempData with create table, insert into. You will need to define identity column there. How many rows are inserted into #tempData table? Can you also post the code of SAM_sValBaln_UPD sp? Can you post the code of dbo.fSetBusnDate, dbo.SAM_FGETBRFK and SAM_fGetGLHdFK? So to recapitulate: 1. Replace select … into and test proc again.
2. Let as know which statement inside transaction takes the most time.
3. If any statetment involving udfs and SAM_sValBaln_UPD takes over 50 ms, post their code.
4. Let us know how many rows are typicaly inserted into #tempData table?

Dear Mirko & Twain I repalced the Select Into….Statement With Create Table Statement The number of rows that are typically inserted into #tempData varies between 100 and 800 Following is the code for SAM_sValBaln_UPD CREATE PROCEDURE SAM_sValBaln_UPD ( @p_BrID UDVC16, @p_PrdID UDVC16, @p_AcctID UDVC16, @p_ValueDt UDDT, @p_TxnAmtUDNU194, @p_TxnTypeUDVC10, @p_OptnTypeID UDVC5 ) As BEGIN SET NOCOUNT ON /************************************************************************** ’SP NAME <img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />AM_sValBaln_UPD ’Parameter :BrID,PrdID,AcctID, Value Date,Txn. Amt, TxnType, OptnTypeID ’Output Parameter : ’Created By :EDA ’Created Date :15-07-2005 ’Description :This SP will execute and calculate the interest based on purpose given. ’Modified By :EDA (EDA06FEB06) ’Modified Date :25.02.2006 17:02(For FD Preclosure Duration Slab) ’Modified By :RMA (RMA31Mar06) ’Modified Date :31.03.2006 21:08( For ValBaln – Advn Coln ) ’**************************************************************************/ DECLARE @m_PrdType UDVC10,@m_PrevValueBaln UDNU194, @m_BalnType UDVC10,@m_ReqMonthBaln UDCH1, @m_BalnDays UDSINT SELECT @m_PrdType = PrdType, @m_BalnType = BalnType, @m_ReqMonthBaln = IsReqMonthBaln, @m_BalnDays = MonthBalnDays FROM SAM_tProductMst WHERE Prdid = @p_PrdID IF @m_PrdType = ‘LON’ BEGIN IF NOT EXISTS(SELECT ‘x’ from LON_tValBaln WHERE BrID = @p_BrID AND PrdID = @p_PrdID And AcctID = @p_AcctID And ValueDt = @p_ValueDt) BEGIN – — IF NOT EXISTS(SELECT ‘X’ FROM LON_tValBaln WHERE BrID = @p_BrID AND PrdID = @p_PrdID – — And AcctID = @p_AcctID) – — BEGIN – — INSERT INTO LON_tValBaln – — (BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,ValueDt,ValueBaln,MonthBaln,TotPrinDueTo,TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal) – — SELECT @p_BrID,dbo.FGETBRFK(@p_BrID),@p_PrdID,dbo.FGETPRDFK(@p_PrdID), – — @p_AcctID,dbo.SAM_ffGetAcctFk(@p_BrID,@p_PrdID,@p_AcctID),@p_ValueDt,0,0,0,0,0,0,0,0,0 – — END – — ELSE BEGIN – — INSERT INTO LON_tValBaln – — (BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,ValueDt,ValueBaln,MonthBaln,TotPrinDueTo,TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal) – — SELECT TOP 1 BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,@p_ValueDt,ValueBaln,CASE WHEN (MONTH(@p_ValueDt) = MONTH(ValueDt)) AND (YEAR(@p_ValueDt) = YEAR(ValueDt)) THEN MonthBaln Else ValueBaln END As MonthBaln, – — TotPrinDueTo,TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal – — FROM LON_tValBaln – — WHERE BrID = @p_BrID – — And PrdID = @p_PrdID – — And AcctID = @p_AcctID – — And ValueDt <= @p_ValueDt – — ORDER BY ValueDt DESC – — END IF NOT EXISTS(SELECT ‘X’ FROM LON_tValBaln WHERE BrID = @p_BrID AND PrdID = @p_PrdID And AcctID = @p_AcctID) BEGIN INSERT INTO LON_tValBaln (BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,ValueDt,ValueBaln,MonthBaln, TotPrinDueTo,TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm, TotOthDueFrm,ClosureBal,TotPrinAdvColn,TotIntAdvColn,TotOthAdvColn) SELECT @p_BrID,dbo.FGETBRFK(@p_BrID),@p_PrdID,dbo.FGETPRDFK(@p_PrdID), @p_AcctID,dbo.SAM_ffGetAcctFk(@p_BrID,@p_PrdID,@p_AcctID), @p_ValueDt,0,0,0,0,0,0,0,0,0,0,0,0 END ELSE BEGIN INSERT INTO LON_tValBaln (BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,ValueDt,ValueBaln,MonthBaln,TotPrinDueTo, TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal, TotPrinAdvColn,TotIntAdvColn,TotOthAdvColn) SELECT TOP 1 BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,@p_ValueDt,ValueBaln, CASE WHEN (MONTH(@p_ValueDt) = MONTH(ValueDt)) AND (YEAR(@p_ValueDt) = YEAR(ValueDt)) THEN MonthBaln Else ValueBaln END As MonthBaln,TotPrinDueTo,TotIntDueTo,TotOthDueTo, TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal,TotPrinAdvColn,TotIntAdvColn,TotOthAdvColn FROM LON_tValBaln WHERE BrID = @p_BrID And PrdID = @p_PrdID And AcctID = @p_AcctID And ValueDt <= @p_ValueDt ORDER BY ValueDt DESC END END IF @m_BalnType =’D’ BEGIN UPDATE AVB SET ValueBaln = Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN Case When @P_TxnType = ‘D’ Then AVB.ValueBaln + @p_TxnAmt When @P_TxnType = ‘C’ Then AVB.ValueBaln – @p_TxnAmt When @P_TxnType = ‘DEM’ Then AVB.ValueBaln + @p_TxnAmt Else AVB.ValueBaln End Else AVB.ValueBaln End, AVB.TotPrinDueTo= 0, AVB.TotIntDueTo= 0, AVB.TotOthDueTo= 0, AVB.TotPrinDueFrm= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotPrinDueFrm + @p_TxnAmt)- IsNull(TotPrinAdvColn,0) >=0 THEN (AVB.TotPrinDueFrm + @p_TxnAmt)- IsNull(TotPrinAdvColn,0) ELSE 0 END ElseAVB.TotPrinDueFrm End, AVB.TotIntDueFrm= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotIntDueFrm + @p_TxnAmt)- IsNull(TotIntAdvColn,0) >= 0 THEN (AVB.TotIntDueFrm + @p_TxnAmt)- IsNull(TotIntAdvColn,0) ELSE 0 END ElseAVB.TotIntDueFrm End, AVB.TotOthDueFrm = CaseWhen @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotOthDueFrm + @p_TxnAmt)- IsNull(TotOthAdvColn,0) >=0 THEN (AVB.TotOthDueFrm + @p_TxnAmt)- IsNull(TotOthAdvColn,0) ELSE 0 END ElseAVB.TotOthDueFrm End, AVB.ClosureBal= CaseWhen @p_TxnType = ‘D’ Then IsNull(AVB.ClosureBal,0) + @p_TxnAmt When @p_TxnType = ‘C’ Then CaseWhen (IsNull(AVB.ClosureBal,0) – @p_TxnAmt) >= 0 Then IsNull(AVB.ClosureBal,0) – @p_TxnAmt Else0 End Else AVB.ClosureBal End, – AVB.TotPrinAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ THEN – CASE WHEN (AVB.TotPrinAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn – @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘C’ THEN – CASE WHEN (AVB.TotPrinAdvColn + @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn + @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – Else AVB.TotPrinAdvColn – END, – AVB.TotIntAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ THEN – CASE WHEN (AVB.TotIntAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn – @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnT ype = ‘C’ THEN – CASE WHEN (AVB.TotIntAdvColn + @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn + @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – Else AVB.TotIntAdvColn – END, – AVB.TotOthAdvColn = CASE WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then – CASE WHEN (AVB.TotOthAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn – @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then – CASE WHEN (AVB.TotOthAdvColn + @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn + @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – Else AVB.TotOthAdvColn – END –<< RMA31Mar06 — Update Valbaln – PrinAdvnColn, IntAdvnColn, OthAdvnColn — Begins >> AVB.TotPrinAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ THEN CASE WHEN (AVB.TotPrinAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn – @p_TxnAmt) –<<EDA1303200601 ELSE 0 END WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘C’ THEN CASE WHEN (AVB.TotPrinDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn) –<<EDA1303200601 ELSE AVB.TotPrinAdvColn + (@p_TxnAmt – AVB.TotPrinDueFrm)END Else AVB.TotPrinAdvColn END, AVB.TotIntAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ THEN CASE WHEN (AVB.TotIntAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn – @p_TxnAmt) –<<EDA1303200601 ELSE 0 END WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ THEN CASE WHEN (AVB.TotIntDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn) –<<EDA1303200601 ELSE AVB.TotIntAdvColn + (@p_TxnAmt – AVB.TotIntDueFrm)END Else AVB.TotIntAdvColn END, AVB.TotOthAdvColn = CASE WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotOthAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn – @p_TxnAmt) –<<EDA1303200601 ELSE 0 END WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then CASE WHEN (AVB.TotOthDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn) –<<EDA1303200601 ELSE AVB.TotOthAdvColn + (@p_TxnAmt – AVB.TotOthDueFrm)END Else AVB.TotOthAdvColn END –<< RMA31Mar06 — Update Valbaln – PrinAdvnColn, IntAdvnColn, OthAdvnColn — Ends >> FROM LON_tValBaln AVB WHERE BrID = @p_BrID ANDPrdID = @p_PrdID ANDAcctID = @p_AcctID ANDValueDt >= @p_ValueDt END –<<’D' ELSE IF @m_BalnType =’LON’ BEGIN UPDATE AVB SET ValueBaln = Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN CaseWhen @P_TxnType = ‘D’ Then AVB.ValueBaln + @p_TxnAmt When @P_TxnType = ‘C’ Then AVB.ValueBaln – @p_TxnAmt When @P_TxnType = ‘DEM’ Then AVB.ValueBaln –+ @p_TxnAmt Else AVB.ValueBaln End Else AVB.ValueBaln End, AVB.TotPrinDueTo= 0, AVB.TotIntDueTo= 0, AVB.TotOthDueTo= 0, AVB.TotPrinDueFrm = CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotPrinDueFrm + @p_TxnAmt) – IsNull(TotPrinAdvColn,0) >=0 THEN (AVB.TotPrinDueFrm + @p_TxnAmt) – IsNull(TotPrinAdvColn,0) ELSE 0 END When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘C’ Then CASE WHEN (AVB.TotPrinDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotPrinDueFrm – @p_TxnAmt) ELSE 0 END ElseAVB.TotPrinDueFrm End, AVB.TotIntDueFrm= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotIntDueFrm + @p_TxnAmt) – IsNull(TotIntAdvColn,0) >=0 THEN (AVB.TotIntDueFrm + @p_TxnAmt) – IsNull(TotIntAdvColn,0) ELSE 0 END When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then CASE WHEN (AVB.TotIntDueFrm – @p_TxnAmt)>=0 THEN (AVB.TotIntDueFrm – @p_TxnAmt) ELSE 0 END ElseAVB.TotIntDueFrm End, AVB.TotOthDueFrm = CaseWhen @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotOthDueFrm + @p_TxnAmt)- IsNull(TotOthAdvColn,0)>=0 THEN (AVB.TotOthDueFrm + @p_TxnAmt)- IsNull(TotOthAdvColn,0) ELSE 0 END When @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then CASE WHEN (AVB.TotOthDueFrm – @p_TxnAmt)>=0 THEN (AVB.TotOthDueFrm – @p_TxnAmt) ELSE 0 END ElseAVB.TotOthDueFrm End, AVB.ClosureBal= Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN CaseWhen @P_TxnType = ‘D’ Then CASE WHEN (AVB.ClosureBal + @p_TxnAmt) – IsNull(AVB.TotPrinAdvColn,0) >=0 THEN (AVB.ClosureBal + @p_TxnAmt) – IsNull(AVB.TotPrinAdvColn,0) ELSE 0 END When @P_TxnType = ‘C’ Then CASE WHEN (AVB.ClosureBal – @p_TxnAmt)>=0 THEN (AVB.ClosureBal – @p_TxnAmt) ELSE 0 END When @P_TxnType = ‘DEM’ Then AVB.ClosureBal –+ @p_TxnAmt Else AVB.ClosureBal End Else AVB.ClosureBal End, – AVB.TotPrinAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ THEN – CASE WHEN (AVB.TotPrinAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn – @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘C’ THEN – CASE WHEN (AVB.TotPrinAdvColn + @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn + @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – Else AVB.TotPrinAdvColn – END, – AVB.TotIntAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ THEN – CASE WHEN (AVB.TotIntAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn – @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ THEN – CASE WHEN (AVB.TotIntAdvColn + @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn + @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – Else AVB.TotIntAdvColn – END, – AVB.TotOthAdvColn = CASE WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then – CASE WHEN (AVB.TotOthAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn – @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – WHEN @p_OptnTypeID <& amp;gt; dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then – CASE WHEN (AVB.TotOthAdvColn + @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn + @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – Else AVB.TotOthAdvColn – END –<< RMA31Mar06 — Update Valbaln – PrinAdvnColn, IntAdvnColn, OthAdvnColn — Begins >> AVB.TotPrinAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ THEN CASE WHEN (AVB.TotPrinAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn – @p_TxnAmt) –<<EDA1303200601 ELSE 0 END WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘C’ THEN CASE WHEN (AVB.TotPrinDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn) –<<EDA1303200601 ELSE AVB.TotPrinAdvColn + (@p_TxnAmt – AVB.TotPrinDueFrm)END Else AVB.TotPrinAdvColn END, AVB.TotIntAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ THEN CASE WHEN (AVB.TotIntAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn – @p_TxnAmt) –<<EDA1303200601 ELSE 0 END WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ THEN CASE WHEN (AVB.TotIntDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn) –<<EDA1303200601 ELSE AVB.TotIntAdvColn + (@p_TxnAmt – AVB.TotIntDueFrm)END Else AVB.TotIntAdvColn END, AVB.TotOthAdvColn = CASE WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotOthAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn – @p_TxnAmt) –<<EDA1303200601 ELSE 0 END WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then CASE WHEN (AVB.TotOthDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn) –<<EDA1303200601 ELSE AVB.TotOthAdvColn + (@p_TxnAmt – AVB.TotOthDueFrm)END Else AVB.TotOthAdvColn END –<< RMA31Mar06 — Update Valbaln – PrinAdvnColn, IntAdvnColn, OthAdvnColn — Ends >> FROM LON_tValBaln AVB WHERE BrID = @p_BrID ANDPrdID = @p_PrdID ANDAcctID = @p_AcctID ANDValueDt >= @p_ValueDt END –<<’LON' ELSE IF @m_BalnType =’LLON’ BEGIN UPDATE AVB SET ValueBaln = Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN CaseWhen @P_TxnType = ‘D’ Then AVB.ValueBaln + @p_TxnAmt When @P_TxnType = ‘C’ Then AVB.ValueBaln – @p_TxnAmt When @P_TxnType = ‘DEM’ Then AVB.ValueBaln + @p_TxnAmt Else AVB.ValueBaln End Else AVB.ValueBaln End, AVB.TotPrinDueTo= 0, AVB.TotIntDueTo= 0, AVB.TotOthDueTo= 0, AVB.TotPrinDueFrm= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotPrinDueFrm + @p_TxnAmt)- IsNull(TotPrinAdvColn,0) >=0 THEN (AVB.TotPrinDueFrm + @p_TxnAmt) – IsNull(TotPrinAdvColn,0) ELSE 0 END When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘C’ Then CASE WHEN (AVB.TotPrinDueFrm – @p_TxnAmt)>=0 THEN (AVB.TotPrinDueFrm – @p_TxnAmt) ELSE 0 END ElseAVB.TotPrinDueFrm End, AVB.TotIntDueFrm= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotIntDueFrm + @p_TxnAmt) – IsNull(TotIntAdvColn,0) >= 0 THEN (AVB.TotIntDueFrm + @p_TxnAmt) – IsNull(TotIntAdvColn,0) ELSE 0 END When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then CASE WHEN (AVB.TotIntDueFrm – @p_TxnAmt)>= 0 THEN (AVB.TotIntDueFrm – @p_TxnAmt) ELSE 0 END ElseAVB.TotIntDueFrm End, AVB.TotOthDueFrm = CaseWhen @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotOthDueFrm + @p_TxnAmt) – IsNull(TotOthAdvColn,0) >=0 THEN (AVB.TotOthDueFrm + @p_TxnAmt) – IsNull(TotOthAdvColn,0) ELSE 0 END When @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then CASE WHEN (AVB.TotOthDueFrm – @p_TxnAmt)>=0 THEN (AVB.TotOthDueFrm – @p_TxnAmt) ELSE 0 END ElseAVB.TotOthDueFrm End, AVB.ClosureBal= Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN CaseWhen @P_TxnType = ‘D’ Then CASE WHEN (AVB.ClosureBal + @p_TxnAmt) – IsNull(TotPrinAdvColn,0) >=0 THEN (AVB.ClosureBal + @p_TxnAmt) – IsNull(TotPrinAdvColn,0) ELSE 0 END When @P_TxnType = ‘C’ Then AVB.ClosureBal – @p_TxnAmt When @P_TxnType = ‘DEM’ Then AVB.ClosureBal –+ @p_TxnAmt Else AVB.ClosureBal End Else AVB.ClosureBal End, – AVB.TotPrinAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ THEN – CASE WHEN (AVB.TotPrinAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn – @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘C’ THEN – CASE WHEN (AVB.TotPrinAdvColn + @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn + @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – Else AVB.TotPrinAdvColn – END, – AVB.TotIntAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ THEN – CASE WHEN (AVB.TotIntAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn – @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ THEN – CASE WHEN (AVB.TotIntAdvColn + @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn + @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – Else AVB.TotIntAdvColn – END, – AVB.TotOthAdvColn = CASE WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then – CASE WHEN (AVB.TotOthAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn – @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then – CASE WHEN (AVB.TotOthAdvColn + @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn + @p_TxnAmt) –<<EDA1303200601 – ELSE 0 END – Else AVB.TotOthAdvColn – END –<< RMA31Mar06 — Update Valbaln – PrinAdvnColn, IntAdvnColn, OthAdvnColn — Begins >> AVB.TotPrinAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ THEN CASE WHEN (AVB.TotPrinAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn – @p_TxnAmt) –&a mp;lt;<EDA1303200601 ELSE 0 END WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘C’ THEN CASE WHEN (AVB.TotPrinDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotPrinAdvColn) –<<EDA1303200601 ELSE AVB.TotPrinAdvColn + (@p_TxnAmt – AVB.TotPrinDueFrm)END Else AVB.TotPrinAdvColn END, AVB.TotIntAdvColn = CASE WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ THEN CASE WHEN (AVB.TotIntAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn – @p_TxnAmt) –<<EDA1303200601 ELSE 0 END WHEN @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ THEN CASE WHEN (AVB.TotIntDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotIntAdvColn) –<<EDA1303200601 ELSE AVB.TotIntAdvColn + (@p_TxnAmt – AVB.TotIntDueFrm)END Else AVB.TotIntAdvColn END, AVB.TotOthAdvColn = CASE WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then CASE WHEN (AVB.TotOthAdvColn – @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn – @p_TxnAmt) –<<EDA1303200601 ELSE 0 END WHEN @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘C’ Then CASE WHEN (AVB.TotOthDueFrm – @p_TxnAmt) >=0 THEN (AVB.TotOthAdvColn) –<<EDA1303200601 ELSE AVB.TotOthAdvColn + (@p_TxnAmt – AVB.TotOthDueFrm)END Else AVB.TotOthAdvColn END –<< RMA31Mar06 — Update Valbaln – PrinAdvnColn, IntAdvnColn, OthAdvnColn — Ends >> FROM LON_tValBaln AVB WHERE BrID = @p_BrID ANDPrdID = @p_PrdID ANDAcctID = @p_AcctID ANDValueDt >= @p_ValueDt END –<<’LLON' END –LOAN END ELSE IF @m_PrdType = ‘DEP’ BEGIN IF NOT EXISTS(SELECT ‘x’ from DEP_tValBaln WHERE BrID = @p_BrID AND PrdID = @p_PrdID And AcctID = @p_AcctID And ValueDt = @p_ValueDt) BEGIN – SELECT TOP 1 @m_PrevValueBaln = ValueBaln – FROM DEP_tValBaln – WHERE BrID = @p_BrID – And PrdID = @p_PrdID – And AcctID = @p_AcctID – And ValueDt <= @p_ValueDt – ORDER BY ValueDt DESC – INSERT INTO DEP_tValBaln – (BrID, Br_FKID, PrdID, Prd_FKID, AcctID, Acct_FKID, ValueDt, ValueBaln) – SELECT @p_BrID, dbo.FGETBRFK(@p_BrID), @p_PrdID,dbo.FGETPRDFK(@p_PrdID), – @p_AcctID, dbo.SAM_ffGetAcctFk(@p_BrID,@p_PrdID,@p_AcctID), @p_ValueDt, – IsNull(@m_PrevValueBaln,0) IF NOT EXISTS(SELECT ‘X’ FROM DEP_tValBaln WHERE BrID = @p_BrID AND PrdID = @p_PrdID And AcctID = @p_AcctID) BEGIN INSERT INTO DEP_tValBaln (BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,ValueDt,ValueBaln,MonthBaln,TotPrinDueTo,TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal) SELECT @p_BrID,dbo.FGETBRFK(@p_BrID),@p_PrdID,dbo.FGETPRDFK(@p_PrdID), @p_AcctID,dbo.SAM_ffGetAcctFk(@p_BrID,@p_PrdID,@p_AcctID),@p_ValueDt,0,0,0,0,0,0,0,0,0 END ELSE BEGIN INSERT INTO DEP_tValBaln (BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,ValueDt,ValueBaln,MonthBaln,TotPrinDueTo,TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal) SELECT TOP 1 BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,@p_ValueDt,ValueBaln,CASE WHEN (MONTH(@p_ValueDt) = MONTH(ValueDt)) AND (YEAR(@p_ValueDt) = YEAR(ValueDt)) THEN MonthBaln Else ValueBaln END As MonthBaln,TotPrinDueTo,TotIntDueTo, TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal FROM DEP_tValBaln WHERE BrID = @p_BrID And PrdID = @p_PrdID And AcctID = @p_AcctID And ValueDt <= @p_ValueDt ORDER BY ValueDt DESC END END –<< BALANCE TYPE –CREDIT IF @m_BalnType =’C’ BEGIN UPDATE AVB SET ValueBaln = Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN CaseWhen @P_TxnType = ‘C’ Then AVB.ValueBaln + @p_TxnAmt When @P_TxnType = ‘D’ Then AVB.ValueBaln – @p_TxnAmt When @P_TxnType = ‘DEM’ Then AVB.ValueBaln + @p_TxnAmt Else AVB.ValueBaln End Else AVB.ValueBaln End, AVB.TotPrinDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotPrinDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotPrinDueTo – @p_TxnAmt) >= 0 Then AVB.TotPrinDueTo – @p_TxnAmt Else0 End ElseAVB.TotPrinDueTo End ElseAVB.TotPrinDueTo End, AVB.TotIntDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotIntDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotIntDueTo – @p_TxnAmt) >= 0 Then AVB.TotIntDueTo – @p_TxnAmt Else0 End ElseAVB.TotIntDueToEnd ElseAVB.TotIntDueTo End, AVB.TotOthDueTo= CaseWhen @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotOthDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotOthDueTo – @p_TxnAmt) >= 0 Then AVB.TotOthDueTo – @p_TxnAmt Else0 End ElseAVB.TotOthDueToEnd ElseAVB.TotOthDueTo End, AVB.TotPrinDueFrm= 0, AVB.TotIntDueFrm= 0, AVB.TotOthDueFrm = 0, AVB.ClosureBal= CaseWhen @p_TxnType = ‘C’ Then IsNull(AVB.ClosureBal,0) + @p_TxnAmt When @p_TxnType = ‘D’ Then CaseWhen (IsNull(AVB.ClosureBal,0) – @p_TxnAmt) >= 0 Then IsNull(AVB.ClosureBal,0) – @p_TxnAmt Else0 End Else AVB.ClosureBal End FROM DEP_tValBaln AVB WHERE AVB.BrID = @p_BrID ANDAVB.PrdID = @p_PrdID ANDAVB.AcctID = @p_AcctID ANDAVB.ValueDt >= @p_ValueDt END –‘C' –<< BALANCE TYPE –DEPOSIT ELSE IF @m_BalnType =’DEP’ BEGIN UPDATE AVB SET ValueBaln = Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN CaseWhen @P_TxnType = ‘C’ Then AVB.ValueBaln + @p_TxnAmt When @P_TxnType = ‘D’ Then AVB.ValueBaln – @p_TxnAmt When @P_TxnType = ‘DEM’ Then –<<COMMENTED BY EDA ON 20.10.2005 AVB.ValueBaln –+ @p_TxnAmt Else AVB.ValueBaln End Else AVB.ValueBaln End, AVB.TotPrinDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotPrinDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotPrinDueTo – @p_TxnAmt) >= 0 Then AVB.TotPrinDueTo – @p_TxnAmt Else0 End ElseAVB.TotPrinDueTo End ElseAVB.TotPrinDueTo End, AVB.TotIntDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotIntDueTo + @p_TxnAmt Whe n @p_TxnType = ‘D’ Then Case When (AVB.TotIntDueTo – @p_TxnAmt) >= 0 Then AVB.TotIntDueTo – @p_TxnAmt Else0 End ElseAVB.TotIntDueToEnd ElseAVB.TotIntDueTo End, AVB.TotOthDueTo= CaseWhen @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotOthDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotOthDueTo – @p_TxnAmt) >= 0 Then AVB.TotOthDueTo – @p_TxnAmt Else0 End ElseAVB.TotOthDueToEnd ElseAVB.TotOthDueTo End, AVB.TotPrinDueFrm= 0, AVB.TotIntDueFrm= 0, AVB.TotOthDueFrm = 0, AVB.ClosureBal= CaseWhen @p_TxnType = ‘C’ Then IsNull(AVB.ClosureBal,0) + @p_TxnAmt When @p_TxnType = ‘D’ Then CaseWhen (IsNull(AVB.ClosureBal,0) – @p_TxnAmt) >= 0 Then IsNull(AVB.ClosureBal,0) – @p_TxnAmt Else0 End Else AVB.ClosureBal End FROM DEP_tValBaln AVB WHERE AVB.BrID = @p_BrID ANDAVB.PrdID = @p_PrdID ANDAVB.AcctID = @p_AcctID ANDAVB.ValueDt >= @p_ValueDt END –‘DEP' –<< BALANCE TYPE –DEPOSIT ELSE IF @m_BalnType =’RDDEP’ BEGIN UPDATE AVB SET ValueBaln = Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN CaseWhen @P_TxnType = ‘C’ Then AVB.ValueBaln + @p_TxnAmt When @P_TxnType = ‘D’ Then AVB.ValueBaln – @p_TxnAmt When @P_TxnType = ‘DEM’ Then AVB.ValueBaln + @p_TxnAmt Else AVB.ValueBaln End Else AVB.ValueBaln End, – AVB.TotPrinDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) Then – CaseWhen @p_TxnType = ‘DEM’ Then – AVB.TotPrinDueTo + @p_TxnAmt – When @p_TxnType = ‘D’ Then – Case When (AVB.TotPrinDueTo – @p_TxnAmt) >= 0 Then – AVB.TotPrinDueTo – @p_TxnAmt – Else0 End – ElseAVB.TotPrinDueTo End – ElseAVB.TotPrinDueTo End, AVB.TotIntDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotIntDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotIntDueTo – @p_TxnAmt) >= 0 Then AVB.TotIntDueTo – @p_TxnAmt Else0 End ElseAVB.TotIntDueToEnd ElseAVB.TotIntDueTo End, AVB.TotOthDueTo= CaseWhen @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotOthDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotOthDueTo – @p_TxnAmt) >= 0 Then AVB.TotOthDueTo – @p_TxnAmt Else0 End ElseAVB.TotOthDueToEnd ElseAVB.TotOthDueTo End, AVB.TotPrinDueFrm = CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_TxnType = ‘DEM’ Then AVB.TotPrinDueFrm + @p_TxnAmt ElseAVB.TotPrinDueFrm End, AVB.TotOthDueFrm = CaseWhen @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) And @p_TxnType = ‘DEM’ Then AVB.TotOthDueFrm + @p_TxnAmt ElseAVB.TotOthDueFrm End, AVB.ClosureBal= CaseWhen @p_TxnType = ‘C’ Then IsNull(AVB.ClosureBal,0) + @p_TxnAmt When @p_TxnType = ‘D’ Then CaseWhen (IsNull(AVB.ClosureBal,0) – @p_TxnAmt) >= 0 Then IsNull(AVB.ClosureBal,0) – @p_TxnAmt Else0 End Else AVB.ClosureBal End FROM DEP_tValBaln AVB WHERE AVB.BrID = @p_BrID ANDAVB.PrdID = @p_PrdID ANDAVB.AcctID = @p_AcctID ANDAVB.ValueDt >= @p_ValueDt END –‘RDDEP' ELSE IF @m_BalnType =’RUNAC’ BEGIN –<< ADDED STARTS HERE BY EDA ON 10-04-2005 FOR THE PURPOSE OF MONTH BALANCE UPDATION UPDATE AVB SET ValueBaln = Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN CaseWhen @P_TxnType = ‘C’ Then AVB.ValueBaln + @p_TxnAmt When @P_TxnType = ‘D’ Then AVB.ValueBaln – @p_TxnAmt When @P_TxnType = ‘DEM’ Then AVB.ValueBaln + @p_TxnAmt Else AVB.ValueBaln End Else AVB.ValueBaln End, AVB.TotPrinDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotPrinDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotPrinDueTo – @p_TxnAmt) >= 0 Then AVB.TotPrinDueTo – @p_TxnAmt Else0 End ElseAVB.TotPrinDueTo End ElseAVB.TotPrinDueTo End, AVB.TotIntDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotIntDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotIntDueTo – @p_TxnAmt) >= 0 Then AVB.TotIntDueTo – @p_TxnAmt Else0 End ElseAVB.TotIntDueToEnd ElseAVB.TotIntDueTo End, AVB.TotOthDueTo= CaseWhen @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @p_TxnType = ‘DEM’ Then AVB.TotOthDueTo + @p_TxnAmt When @p_TxnType = ‘D’ Then Case When (AVB.TotOthDueTo – @p_TxnAmt) >= 0 Then AVB.TotOthDueTo – @p_TxnAmt Else0 End ElseAVB.TotOthDueToEnd ElseAVB.TotOthDueTo End, AVB.TotPrinDueFrm = 0, AVB.TotIntDueFrm = 0, AVB.TotOthDueFrm = 0, AVB.ClosureBal= CaseWhen @p_TxnType = ‘C’ Then IsNull(AVB.ClosureBal,0) + @p_TxnAmt When @p_TxnType = ‘D’ Then CaseWhen (IsNull(AVB.ClosureBal,0) – @p_TxnAmt) >= 0 Then IsNull(AVB.ClosureBal,0) – @p_TxnAmt Else0 End Else AVB.ClosureBal End FROM DEP_tValBaln AVB WHERE AVB.BrID = @p_BrID ANDAVB.PrdID = @p_PrdID ANDAVB.AcctID = @p_AcctID ANDAVB.ValueDt >= @p_ValueDt –<<COMMENTED BY EDA ON 28.02.2006(FOR REPLACING TO COMMON AREA FOR ACCOUNT MONTHBALN UPDATION) – IF @m_ReqMonthBaln = ‘Y’ BEGIN – EXEC dbo.IRC_sAcctMnthBaln_UPD @p_BrID, @p_PrdID, @p_AcctID, @m_BalnDays,@p_ValueDt – END END –‘RUNAC' –<<ADDED BY EDA ON 28.02.2006(FOR REPLACING TO COMMON AREA FOR ACCOUNT MONTHBALN UPDATION) IF @m_ReqMonthBaln = ‘Y’ BEGIN EXEC dbo.IRC_sAcctMnthBaln_UPD @p_BrID, @p_PrdID, @p_AcctID, @m_BalnDays,@p_ValueDt END END –DEP END ELSE IF @m_PrdType = ‘CUS’ BEGIN –CUS STRT IF NOT EXISTS(SELECT ‘x’ from CUS_tValBaln WHERE BrID = @p_BrID AND PrdID = @p_PrdID And AcctID = @p_AcctID And ValueDt = @p_ValueDt) BEGIN IF NOT EXISTS(SELECT ‘X’ FROM CUS_tValBaln WHERE BrID = @p_BrID AND PrdID = @p_PrdID And AcctID = @p_AcctID) BEGIN INSERT INTO CUS_tValBaln (BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,ValueDt,ValueBaln,MonthBaln,TotPrinDueTo,TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal) SELECT @p_BrID,dbo.FGETBRFK(@p_BrID),@p _PrdID,dbo.FGETPRDFK(@p_PrdID), @p_AcctID,dbo.SAM_ffGetAcctFk(@p_BrID,@p_PrdID,@p_AcctID),@p_ValueDt,0,0,0,0,0,0,0,0,0 END ELSE BEGIN INSERT INTO CUS_tValBaln (BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,ValueDt,ValueBaln,MonthBaln,TotPrinDueTo,TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal) SELECT TOP 1 BrID,Br_FKID,PrdID,Prd_FKID,AcctID,Acct_FKID,@p_ValueDt,ValueBaln,CASE WHEN (MONTH(@p_ValueDt) = MONTH(ValueDt)) AND (YEAR(@p_ValueDt) = YEAR(ValueDt)) THEN MonthBaln Else ValueBaln END As MonthBaln,TotPrinDueTo, TotIntDueTo,TotOthDueTo,TotPrinDueFrm,TotIntDueFrm,TotOthDueFrm,ClosureBal FROM CUS_tValBaln WHERE BrID = @p_BrID And PrdID = @p_PrdID And AcctID = @p_AcctID And ValueDt <= @p_ValueDt ORDER BY ValueDt DESC END UPDATE AVB SETAVB.ValueBaln= Case When @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) THEN CASEWHEN @P_TxnType = ‘C’ Then AVB.ValueBaln + @p_TxnAmt WHEN @P_TxnType = ‘D’ Then AVB.ValueBaln – @p_TxnAmt ELSE AVB.ValueBaln END ELSE AVB.ValueBaln End, AVB.TotPrinDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘PRIN’) Then CaseWhen @P_TxnType = ‘DEM’ Then AVB.TotPrinDueTo + @p_TxnAmt When @P_TxnType = ‘D’ Then Case When (AVB.TotPrinDueTo – @p_TxnAmt) >= 0 Then AVB.TotPrinDueTo – @p_TxnAmt Else0 End ElseAVB.TotPrinDueTo End ElseAVB.TotPrinDueTo End, AVB.TotIntDueTo= CaseWhen @p_OptnTypeID = dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @P_TxnType = ‘DEM’ Then AVB.TotIntDueTo + @p_TxnAmt When @P_TxnType = ‘D’ Then Case When (AVB.TotIntDueTo – @p_TxnAmt) >= 0 Then AVB.TotIntDueTo – @p_TxnAmt Else0 End ElseAVB.TotIntDueToEnd ElseAVB.TotIntDueTo End, AVB.TotOthDueTo= CaseWhen @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘PRIN’) And @p_OptnTypeID <> dbo.SAM_fGetHCOptnTypeID(‘INT’) Then CaseWhen @P_TxnType = ‘DEM’ Then AVB.TotOthDueTo + @p_TxnAmt When @P_TxnType = ‘D’ Then Case When (AVB.TotOthDueTo – @p_TxnAmt) >= 0 Then AVB.TotOthDueTo – @p_TxnAmt Else0 End ElseAVB.TotOthDueToEnd ElseAVB.TotOthDueTo End, AVB.TotPrinDueFrm = 0, AVB.TotIntDueFrm = 0, AVB.TotOthDueFrm = 0, AVB.ClosureBal= CaseWhen @P_TxnType = ‘C’ Then IsNull(AVB.ClosureBal,0) + @p_TxnAmt When @P_TxnType = ‘D’ Then CaseWhen (IsNull(AVB.ClosureBal,0) – @p_TxnAmt) >= 0 Then IsNull(AVB.ClosureBal,0) – @p_TxnAmt Else0 End Else AVB.ClosureBal End FROM CUS_tValBaln AVB WHERE AVB.BrID = @p_BrID ANDAVB.PrdID = @p_PrdID ANDAVB.AcctID = @p_AcctID ANDAVB.ValueDt >= @p_ValueDt END END –CUS END SET NOCOUNT OFF END Following is the code for Text fSetBusnDate –Select dbo.fSetBusnDate(”) CREATE Function dbo.fSetBusnDate ( @p_BusnDt Varchar(23) ) returns Datetime As Begin Declare @m_RetBusnDt As Datetime IF @P_BUSNDT = ” BEGIN SET @P_BUSNDT = NULL END –SET @m_RetBusnDt = Cast(@p_BusnDt as Datetime) SET @m_RetBusnDt = Convert(datetime,@p_BusnDt,103) Return @m_RetBusnDt End Following is the code for SAM_FGETBRFK CREATE FUNCTION SAM_FGETBRFK ( @P_BRID UDVC16 ) RETURNS BIGINT AS BEGIN DECLARE @M_BRFK BIGINT SELECT @M_BRFK = PK_ID FROM SAM_tBrMst WHERE BRID = @P_BRID RETURN @M_BRFK END Following is the code for SAM_fGetGLHdFK CREATE FUNCTION dbo.SAM_fGetGLHdFK ( @p_GLHeadID UDVC25 ) RETURNS BIGINT AS BEGIN DECLARE @m_GLHeadFK UDBINT SELECT @m_GLHeadFK = PK_ID FROMFAS_tGLHead WHERE GLHeadID = @p_GLHeadID RETURN @m_GLHeadFK END How do I know know which statement inside transaction takes the most time.? Deep Regards Anil Whatever You Think,That You Will Be.If You Think YOurself Weak You WIll BE ,If You Think Yourself Strong Strong You WIll be

<blockquote id="quote">quote:<hr height="1" noshade id="quote">How do I know know which statement inside transaction takes the most time.?<hr height="1" noshade id="quote"></blockquote id="quote"> As Twan already mentioned you should include in your trace sp<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tatementCompleted event.

Thanks Mirko I will use the statment and let you know the execution time of each Statements inside the Sp
Deep Regards
Anil

Dear Mirko ANd Twan Frankly I don’t know how to use the sp<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tatementCompleted . It seems to be a method of SqlCommandObject in Dot net. As You mentioned I replaced the Select Into With Create Table. Apart from this do I need to make any other alteration in my SP? Can You plz grade my sP? Sp a good one? Does it follows Standards? Naming Conventions? One good news I would like to share. I relased my problem SP for testing as it is running fine when the number of records in the #Tempdata table is between 300 and 400. Other users are able to transact parallely now Now I am Assigned With Another Task Of Tuning Another SP. It seems I may need to disturb you for that too. I will be happy If you could send your valid feed backs on my PBLMSP Thanks a lot Anil Never Give Up

Hi Anil, you provided really a lot of information and as I promised I’ll analyze them and come back with suggestions. Unfortunatelly, right now I have a deadline on the problem I am paid to solve, so I can’t analyze the code you provided before the weekend. I hope it is not too late for you to recieve more suggestion from me on Monday. In the meantime, I guess Twan or someone else of regulare contributors, might offer useful hints. Cheers,
Mirko.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by anilsaritha Frankly I don’t know how to use the sp<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tatementCompleted . <hr height="1" noshade id="quote"></blockquote id="quote"> Sorry, I forgot about that part. sp<img src=’/community/emoticons/emotion-7.gif’ alt=’:s’ />tmtCompleted is trace event from "stored procedures" group used by sql server profiler.

Do you have two positions where you have deadlocks (position1 and position2) or you have deadlock in code between them?

Anil, let’s try to avoid deadlock at the first position you mentioned. Replace the code: <blockquote id="quote">quote:<hr height="1" noshade id="quote"> <pre id="code"> SELECT @m_MinPK_ID = MIN(PKID), @m_MaxPK_ID = Max(PKID) From #TempGENERAL WherePurpose=’DEP' [email protected]_MinPK_ID<[email protected]_MaxPK_ID Begin –2 [email protected]_BrID= BT.BrID, @m_PrdID = BT.PrdID, @m_AcctID= BT.AcctID, @m_NxtInsGrpID= BT.InsGrpID, @m_InsGrpSlNo= BT.InsGrpSlNo, @m_TxnType = BT.TxnType, @m_Realise= BT.Realize, @M_BusnsDt= BT.BusnsDt, @m_OprBrID= BT.OprBrID, @M_TransAmt = TxnAmt, @m_ValueDt = ValDt, @m_OptionId=GlHeadId,@m_TxnPrdid=OPPPrdid,@m_OppAcctId=OppAcctId From#TempGENERAL BT WherePKID = @m_MinPK_ID If @m_TxnType=’C' Begin –Position1 –Here is were I am getting The Lock Issue UpdateDEP_tAcctBaln –with (UPDLock) SetDEP_tAcctBaln.AvailedAmt=DEP_tAcctBaln.AvailedAmt + @M_TransAmt, DEP_tAcctBaln.TotOS=DEP_tAcctBaln.TotOS + @M_TransAmt, [email protected]_BusnsDt, [email protected]_BusnsDt, [email protected]_BusnsDt, DEP_tAcctBaln.NoofPendTxn=0 [email protected]_BrID [email protected]_PrdID [email protected]_AcctID UpdateDEP_tAcctBalnDetl –with (UPDLock) SetDEP_tAcctBalnDetl.OSAmt=DEP_tAcctBalnDetl.OSAmt + @M_TransAmt, DEP_tAcctBalnDetl.AmtDueTo=DEP_tAcctBalnDetl.AmtDueTo + @M_TransAmt, [email protected]_BusnsDt WhereBalnHdr_FK= (SelectPk_Id FromDEP_tAcctBalnwith (NoLock) [email protected]_BrID [email protected]_PrdID [email protected]_AcctID) [email protected][email protected] And @[email protected] Begin [email protected][email protected][email protected]_TransAmt End [email protected][email protected] And @[email protected] Begin [email protected][email protected][email protected]_TransAmt End [email protected][email protected] And @[email protected] Begin [email protected][email protected][email protected]_TransAmt End End –Position2 –Here is were I am getting The Lock Issue EXEC SAM_sValBaln_UPD @p_BrID, @m_PrdID, @m_AcctID, @m_ValueDt, @M_TransAmt, @m_TxnType,’1′ [email protected][email protected] Begin If @m_TxnType=’D' Begin [email protected]_DrAmt_NOSB [email protected]_DrAmt_NOSB [email protected]_TransAmt [email protected]_DrAmt_DD [email protected]_DrAmt_DD [email protected]_TransAmt [email protected]_DrAmt_HB [email protected]_ DrAmt_HB [email protected]_TransAmt End Else Begin [email protected]_CrAmt_NOSB [email protected]_CrAmt_NOSB [email protected]_TransAmt [email protected]_CrAmt_DD [email protected]_CrAmt_DD [email protected]_TransAmt [email protected]_CrAmt_HB [email protected]_CrAmt_HB [email protected]_TransAmt End End If @[email protected] Begin If @m_TxnType=’D' Begin [email protected]_DrAmt_DD [email protected]_DrAmt_DD [email protected]_TransAmt End Else Begin [email protected]_CrAmt_DD [email protected]_CrAmt_DD [email protected]_TransAmt End End [email protected][email protected]_MinPK_ID+1 End–2 </pre id="code"> <hr height="1" noshade id="quote"></blockquote id="quote"> with <pre id="code"> declare @pk_id &lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />k_id type> [email protected]_MinPK_ID<[email protected]_MaxPK_ID Begin –2 [email protected]_BrID= BT.BrID, @m_PrdID = BT.PrdID, @m_AcctID= BT.AcctID, @m_NxtInsGrpID= BT.InsGrpID, @m_InsGrpSlNo= BT.InsGrpSlNo, @m_TxnType = BT.TxnType, @m_Realise= BT.Realize, @M_BusnsDt= BT.BusnsDt, @m_OprBrID= BT.OprBrID, @M_TransAmt = TxnAmt, @m_ValueDt = ValDt, @m_OptionId=GlHeadId,@m_TxnPrdid=OPPPrdid,@m_OppAcctId=OppAcctId From#TempGENERAL BT WherePKID = @m_MinPK_ID If @m_TxnType=’C' Begin –Position1 –Here is were I am getting The Lock Issue UpdateDEP_tAcctBaln –with (UPDLock) SetDEP_tAcctBaln.AvailedAmt=DEP_tAcctBaln.AvailedAmt + @M_TransAmt, DEP_tAcctBaln.TotOS=DEP_tAcctBaln.TotOS + @M_TransAmt, [email protected]_BusnsDt, [email protected]_BusnsDt, [email protected]_BusnsDt, DEP_tAcctBaln.NoofPendTxn=0, @pk_id= pk_Id [email protected]_BrID [email protected]_PrdID [email protected]_AcctID UpdateDEP_tAcctBalnDetl –with (UPDLock) SetDEP_tAcctBalnDetl.OSAmt=DEP_tAcctBalnDetl.OSAmt + @M_TransAmt, DEP_tAcctBalnDetl.AmtDueTo=DEP_tAcctBalnDetl.AmtDueTo + @M_TransAmt, [email protected]_BusnsDt WhereBalnHdr_FK= @pk_id [email protected][email protected] And @[email protected] Begin [email protected][email protected][email protected]_TransAmt End [email protected][email protected] And @[email protected] Begin [email protected][email protected][email protected]_TransAmt End [email protected][email protected] And @[email protected] Begin [email protected][email protected][email protected]_TransAmt End End –Position2 –Here is were I am getting The Lock Issue EXEC SAM_sValBaln_UPD @p_BrID, @m_PrdID, @m_AcctID, @m_ValueDt, @M_TransAmt, @m_TxnType,’1′ [email protected][email protected] Begin If @m_TxnType=’D' Begin [email protected]_DrAmt_NOSB [email protected]_DrAmt_NOSB [email protected]_TransAmt [email protected]_DrAmt_DD [email protected]_DrAmt_DD [email protected]_TransAmt [email protected]_DrAmt_HB [email protected]_DrAmt_HB [email protected]_TransAmt End Else Begin [email protected]_CrAmt_NOSB [email protected]_CrAmt_NOSB [email protected]_TransAmt [email protected]_CrAmt_DD [email protected]_CrAmt_DD [email protected]_TransAmt [email protected]_CrAmt_HB [email protected]_CrAmt_HB [email protected]_TransAmt End End If @[email protected] Begin If @m_TxnType=’D' Begin [email protected]_DrAmt_DD [email protected]_DrAmt_DD [email protected]_TransAmt End Else Begin [email protected]_CrAmt_DD [email protected]_CrAmt_DD [email protected]_TransAmt End End [email protected][email protected]_MinPK_ID+1 End–2 </pre id="code"> test it and let me know if it helped.

For stored procedure SAM_sValBaln_UPD I really need from you the info about the critical part (the longest running statetment).

Dear Mirko Introduction of the new variable @pk_id was a very good logic.
I have a doubt.my earlier method of Updation of DEP_tAcctBaln table was through a loop.
Looping is better or direct updation from the temptable as shown below is better? UpdateDEP_tAcctBaln with (UPDLock)
SetDEP_tAcctBaln.AvailedAmt=DEP_tAcctBaln.AvailedAmt+#TempAccBalan.TxnAmt,
DEP_tAcctBaln.TotOS=DEP_tAcctBaln.TotOS+#TempAccBalan.TxnAmt,
[email protected]_BUSNSDT,
[email protected]_BUSNSDT,
[email protected]_BUSNSDT,
DEP_tAcctBaln.NoofPendTxn=0
From#TempAccBalan
WhereDEP_tAcctBaln.BrID=#TempAccBalan.BrID
AndDEP_tAcctBaln.PrdID=#TempAccBalan.PrdID
AndDEP_tAcctBaln.AcctID=#TempAccBalan.AcctID UpdateDEP_tAcctBalnDetl with (UPDLock)
[email protected]_TransAmt,
[email protected]_TransAmt,
[email protected]_BUSNSDT
FromDEP_tAcctBaln,#TempAccBalan
WhereDEP_tAcctBaln.BrID=#TempAccBalan.BrID
AndDEP_tAcctBaln.PrdID=#TempAccBalan.PrdID
AndDEP_tAcctBaln.AcctID=#TempAccBalan.AcctID
AndDEP_tAcctBaln.Pk_Id=DEP_tAcctBalnDetl.BalnHdr_FK Kindly Comment on the above updation.
Now I am not getting a Dead Lock Deep Regards
Anil Never Give Up

Anil, I’ll be again too busy for a couple of days. In the meantime I would like to know if you don’t have deadlocks any more at all, or just deadlock related to the position 1 is solved? About loop, in general the current solution is better, but I don’t have time right now to look at details. As long as you need to commit or rollback all changes at once the current method is better.

Dear Mirko I really Don’t Want To Disturb Your Work Schedule.
Kindly Reply Whenever You are Free.
I learned a lot from this discussion with you.
Only Request is to Keep In Touch When you are Free as I always need Help From People Like You Regarding Dead Lock,Currently I am Not getting Dead Locks. Deep Regards
Anil Never Give Up

]]>