SQL Server Performance

Dead Lock Issue

Discussion in 'T-SQL Performance Tuning for Developers' started by anilsaritha, May 11, 2006.

  1. anilsaritha New Member

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

  3. anilsaritha New Member

    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
  4. mmarovic Active Member

    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?
  5. anilsaritha New Member

    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
  6. mmarovic Active Member

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

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

    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.
  9. Twan New Member

    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
  10. anilsaritha New Member

    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
  11. mmarovic Active Member

    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.
  12. anilsaritha New Member

    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



  13. mmarovic Active Member

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

    Cheers,
    Mirko.
  14. anilsaritha New Member

    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
  15. Twan New Member

    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
  16. mmarovic Active Member

  17. anilsaritha New Member

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

    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
  19. mmarovic Active Member

    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?
  20. anilsaritha New Member

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

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" 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"></font id="quote"></blockquote id="quote"><br />As Twan already mentioned you should include in your trace sp<img src='/community/emoticons/emotion-7.gif' alt=':s' />tatementCompleted event.<br /><br />
  22. anilsaritha New Member

    Thanks Mirko

    I will use the statment and let you know the execution time of each Statements inside the Sp


    Deep Regards
    Anil

  23. anilsaritha New Member

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

    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.
  25. mmarovic Active Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by anilsaritha</i><br />Frankly I don't know how to use the sp<img src='/community/emoticons/emotion-7.gif' alt=':s' />tatementCompleted .<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />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.<br />
  26. mmarovic Active Member

    Do you have two positions where you have deadlocks (position1 and position2) or you have deadlock in code between them?
  27. mmarovic Active Member

    Anil,<br /><br />let's try to avoid deadlock at the first position you mentioned. Replace the code:<br /><br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT @m_MinPK_ID = MIN(PKID),<br />@m_MaxPK_ID = Max(PKID)<br />From #TempGENERAL<br />WherePurpose='DEP'<br /><br />While@m_MinPK_ID&lt;=@m_MaxPK_ID<br />Begin --2<br /><br />Select@p_BrID= BT.BrID, @m_PrdID = BT.PrdID, <br />@m_AcctID= BT.AcctID, @m_NxtInsGrpID= BT.InsGrpID, <br />@m_InsGrpSlNo= BT.InsGrpSlNo, @m_TxnType = BT.TxnType,<br />@m_Realise= BT.Realize, <br />@M_BusnsDt= BT.BusnsDt, @m_OprBrID= BT.OprBrID,<br />@M_TransAmt = TxnAmt, @m_ValueDt = ValDt,<br />@m_OptionId=GlHeadId,@m_TxnPrdid=OPPPrdid,@m_OppAcctId=OppAcctId<br />From#TempGENERAL BT<br />WherePKID = @m_MinPK_ID<br /><br />If @m_TxnType='C'<br />Begin<br /><br />--Position1<br />--Here is were I am getting The Lock Issue<br />UpdateDEP_tAcctBaln --with (UPDLock)<br />SetDEP_tAcctBaln.AvailedAmt=DEP_tAcctBaln.AvailedAmt + @M_TransAmt,<br />DEP_tAcctBaln.TotOS=DEP_tAcctBaln.TotOS + @M_TransAmt,<br />DEP_tAcctBaln.BusnsDt=@M_BusnsDt,<br />DEP_tAcctBaln.ValueDt=@M_BusnsDt,<br />DEP_tAcctBaln.LstApprvdDt=@M_BusnsDt,<br />DEP_tAcctBaln.NoofPendTxn=0<br />WhereDEP_tAcctBaln.BrID=@p_BrID<br />AndDEP_tAcctBaln.PrdID=@m_PrdID<br />AndDEP_tAcctBaln.AcctID=@m_AcctID<br /><br />UpdateDEP_tAcctBalnDetl --with (UPDLock)<br />SetDEP_tAcctBalnDetl.OSAmt=DEP_tAcctBalnDetl.OSAmt + @M_TransAmt,<br />DEP_tAcctBalnDetl.AmtDueTo=DEP_tAcctBalnDetl.AmtDueTo + @M_TransAmt,<br />DEP_tAcctBalnDetl.ValueDt=@M_BusnsDt<br />WhereBalnHdr_FK= (SelectPk_Id<br />FromDEP_tAcctBalnwith (NoLock)<br />WhereDEP_tAcctBaln.BrID=@p_BrID<br />AndDEP_tAcctBaln.PrdID=@m_PrdID<br />AndDEP_tAcctBaln.AcctID=@m_AcctID)<br /><br /><br />IF@m_TxnPrdid=@NOSBOPPPrdID And @m_OppAcctId=@NOSBOPPAcctId<br />Begin<br />Set@m_TransAmt_NOSB=@m_TransAmt_NOSB+@M_TransAmt<br />End<br /><br />IF@m_TxnPrdid=@DDOPPPrdID And @m_OppAcctId=@DDOPPAcctId<br />Begin<br />Set@m_TransAmt_DD=@m_TransAmt_DD+@M_TransAmt<br />End<br /><br />IF@m_TxnPrdid=@HBOPPPrdID And @m_OppAcctId=@HBOPPAcctId<br />Begin<br />Set@m_TransAmt_HB=@m_TransAmt_HB+@M_TransAmt<br />End<br /><br /><br />End<br /><br /><br /><br />--Position2<br />--Here is were I am getting The Lock Issue<br /><br />EXEC SAM_sValBaln_UPD @p_BrID, @m_PrdID,<br />@m_AcctID, @m_ValueDt,<br />@M_TransAmt, @m_TxnType,'1'<br /><br />If@m_OptionId=@NOSBGLHeadId<br />Begin<br />If @m_TxnType='D'<br />Begin<br />Set@m_DrAmt_NOSB =@m_DrAmt_NOSB +@M_TransAmt<br />Set@m_DrAmt_DD =@m_DrAmt_DD +@M_TransAmt<br />Set@m_DrAmt_HB =@m_DrAmt_HB +@M_TransAmt<br />End<br />Else<br />Begin<br />Set@m_CrAmt_NOSB =@m_CrAmt_NOSB +@M_TransAmt<br />Set@m_CrAmt_DD =@m_CrAmt_DD +@M_TransAmt<br />Set@m_CrAmt_HB =@m_CrAmt_HB +@M_TransAmt<br /><br />End<br />End<br />If @m_OptionId=@DDGLHeadId<br />Begin<br />If @m_TxnType='D'<br />Begin<br />Set@m_DrAmt_DD =@m_DrAmt_DD +@M_TransAmt<br /><br />End<br />Else<br />Begin<br />Set@m_CrAmt_DD =@m_CrAmt_DD +@M_TransAmt<br />End<br />End<br /><br />Set@m_MinPK_ID=@m_MinPK_ID+1<br /><br />End--2<br /></font id="code"></pre id="code"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />with<br /><pre id="code"><font face="courier" size="2" id="code"><br />declare @pk_id &lt<img src='/community/emoticons/emotion-4.gif' alt=';p' />k_id type&gt;<br /><br />While@m_MinPK_ID&lt;=@m_MaxPK_ID<br />Begin --2<br /><br />Select@p_BrID= BT.BrID, @m_PrdID = BT.PrdID, <br />@m_AcctID= BT.AcctID, @m_NxtInsGrpID= BT.InsGrpID, <br />@m_InsGrpSlNo= BT.InsGrpSlNo, @m_TxnType = BT.TxnType,<br />@m_Realise= BT.Realize, <br />@M_BusnsDt= BT.BusnsDt, @m_OprBrID= BT.OprBrID,<br />@M_TransAmt = TxnAmt, @m_ValueDt = ValDt,<br />@m_OptionId=GlHeadId,@m_TxnPrdid=OPPPrdid,@m_OppAcctId=OppAcctId<br />From#TempGENERAL BT<br />WherePKID = @m_MinPK_ID<br /><br />If @m_TxnType='C'<br />Begin<br /><br />--Position1<br />--Here is were I am getting The Lock Issue<br />UpdateDEP_tAcctBaln --with (UPDLock)<br />SetDEP_tAcctBaln.AvailedAmt=DEP_tAcctBaln.AvailedAmt + @M_TransAmt,<br />DEP_tAcctBaln.TotOS=DEP_tAcctBaln.TotOS + @M_TransAmt,<br />DEP_tAcctBaln.BusnsDt=@M_BusnsDt,<br />DEP_tAcctBaln.ValueDt=@M_BusnsDt,<br />DEP_tAcctBaln.LstApprvdDt=@M_BusnsDt,<br />DEP_tAcctBaln.NoofPendTxn=0,<br />@pk_id= pk_Id<br />WhereDEP_tAcctBaln.BrID=@p_BrID<br />AndDEP_tAcctBaln.PrdID=@m_PrdID<br />AndDEP_tAcctBaln.AcctID=@m_AcctID<br /><br />UpdateDEP_tAcctBalnDetl --with (UPDLock)<br />SetDEP_tAcctBalnDetl.OSAmt=DEP_tAcctBalnDetl.OSAmt + @M_TransAmt,<br />DEP_tAcctBalnDetl.AmtDueTo=DEP_tAcctBalnDetl.AmtDueTo + @M_TransAmt,<br />DEP_tAcctBalnDetl.ValueDt=@M_BusnsDt<br />WhereBalnHdr_FK= @pk_id<br /><br /><br />IF@m_TxnPrdid=@NOSBOPPPrdID And @m_OppAcctId=@NOSBOPPAcctId<br />Begin<br />Set@m_TransAmt_NOSB=@m_TransAmt_NOSB+@M_TransAmt<br />End<br /><br />IF@m_TxnPrdid=@DDOPPPrdID And @m_OppAcctId=@DDOPPAcctId<br />Begin<br />Set@m_TransAmt_DD=@m_TransAmt_DD+@M_TransAmt<br />End<br /><br />IF@m_TxnPrdid=@HBOPPPrdID And @m_OppAcctId=@HBOPPAcctId<br />Begin<br />Set@m_TransAmt_HB=@m_TransAmt_HB+@M_TransAmt<br />End<br /><br /><br />End<br /><br /><br /><br />--Position2<br />--Here is were I am getting The Lock Issue<br /><br />EXEC SAM_sValBaln_UPD @p_BrID, @m_PrdID,<br />@m_AcctID, @m_ValueDt,<br />@M_TransAmt, @m_TxnType,'1'<br /><br />If@m_OptionId=@NOSBGLHeadId<br />Begin<br />If @m_TxnType='D'<br />Begin<br />Set@m_DrAmt_NOSB =@m_DrAmt_NOSB +@M_TransAmt<br />Set@m_DrAmt_DD =@m_DrAmt_DD +@M_TransAmt<br />Set@m_DrAmt_HB =@m_DrAmt_HB +@M_TransAmt<br />End<br />Else<br />Begin<br />Set@m_CrAmt_NOSB =@m_CrAmt_NOSB +@M_TransAmt<br />Set@m_CrAmt_DD =@m_CrAmt_DD +@M_TransAmt<br />Set@m_CrAmt_HB =@m_CrAmt_HB +@M_TransAmt<br /><br />End<br />End<br />If @m_OptionId=@DDGLHeadId<br />Begin<br />If @m_TxnType='D'<br />Begin<br />Set@m_DrAmt_DD =@m_DrAmt_DD +@M_TransAmt<br /><br />End<br />Else<br />Begin<br />Set@m_CrAmt_DD =@m_CrAmt_DD +@M_TransAmt<br />End<br />End<br /><br />Set@m_MinPK_ID=@m_MinPK_ID+1<br /><br />End--2<br /></font id="code"></pre id="code"><br />test it and let me know if it helped.
  28. mmarovic Active Member

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

    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,
    DEP_tAcctBaln.BusnsDt=@P_BUSNSDT,
    DEP_tAcctBaln.ValueDt=@P_BUSNSDT,
    DEP_tAcctBaln.LstApprvdDt=@P_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)
    SetDEP_tAcctBalnDetl.OSAmt=DEP_tAcctBalnDetl.OSAmt+@M_TransAmt,
    DEP_tAcctBalnDetl.AmtDueTo=DEP_tAcctBalnDetl.AmtDueTo+@M_TransAmt,
    DEP_tAcctBalnDetl.ValueDt=@P_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
  30. mmarovic Active Member

    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.
  31. anilsaritha New Member

    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

Share This Page