ok i will try and paste both the procs...one before the change and one after the changes.not sure of how to paste so will post them code one quote: CREATE Procedure spUploadBankTransaction @FileEndChar char(1), @LastLoadedTimestamp datetime As insert into [proc] values('spUploadBankTransaction',@FileEndChar,@LastLoadedTimestamp,getdate(),'start') --******************************************************************************************* --Stored Procedure: spUploadBankTransaction --Purpose: This stored procedure uploads the bank transaction records to the database -- from the data in the temp tables --Input: None --Output: None --Developer: M P Raj --Creation Date: 28 June 2004 --Modified Date: 06 Dec 2005 --******************************************************************************************* --variables for retrieval DECLARE @BankAcntId numeric(9) DECLARE @AccountKey numeric(5) DECLARE @BankKey numeric(5) DECLARE @PostingDate datetime DECLARE @ValueDate datetime DECLARE @CreditAmt numeric(16,2) DECLARE @DebitAmt numeric(16,2) DECLARE @BankRefNum varchar(16) DECLARE @TransDesc varchar(35) DECLARE @DetailDesc varchar(900) DECLARE @CustRefNum varchar(35) DECLARE @TransCode numeric(12,0) DECLARE @SweepFlag char(1) DECLARE @SweepDesc varchar(35) DECLARE @Status varchar(2) --variables for updation of import table DECLARE @BalanceFlag char(1) DECLARE @TranFlag char(1) DECLARE @AccountClosed char(1) DECLARE @Count int DECLARE @MinDate datetime --variables for error loggin DECLARE @Err_No int DECLARE @Err_Msg varchar(200) --added on 11-Nov-2005 - eBank2005 DECLARE @BAL_DTL_COUNT INT DECLARE @Computed_Flag char(1) DECLARE @DUMMY_VAL int DECLARE @PostingDateBaln datetime DECLARE @ValueDateBaln datetime DECLARE @LedgerBal numeric(16,2) DECLARE @OpenAvailBal numeric(16,2) DECLARE @CollectedBal numeric(16,2) DECLARE @OneDayFloat numeric(16,2) DECLARE @TwoDayFloat numeric(16,2) DECLARE @OverTwoDayFloat numeric(16,2) DECLARE @TotalCredits numeric(16,2) DECLARE @TotalDebits numeric(16,2) DECLARE @FirstPrevDateInt int DECLARE @FirstPrevDate datetime --added on Dec01 DECLARE @BankAcntIdForSwipe numeric(9) DECLARE @AccountKeyForSwipe numeric(5) DECLARE @BankKeyForSwipe numeric(5) DECLARE @PostingDateForSwipe datetime DECLARE @StatusForSwipe varchar(2) DECLARE @ComputedFlagBalYCase char(1) DECLARE @SumOfCredits numeric(16,2) DECLARE @SumOfDebits numeric(16,2) SET @LedgerBal = 0 SET @OpenAvailBal = 0 SET @CollectedBal = 0 SET @OneDayFloat = 0 SET @TwoDayFloat = 0 SET @OverTwoDayFloat = 0 SET @TotalCredits = 0 SET @TotalDebits = 0 SET @SumOfCredits = 0 SET @SumOfDebits = 0 SET @DUMMY_VAL = -1 SET @BAL_DTL_COUNT = 0 SET @Computed_Flag = 'P' SET @ComputedFlagBalYCase = 'X' --initialize to non-existant value SET @FirstPrevDateInt = 0 --set the date format Set DateFormat 'mdy' --The temp table may contain the customer/bank ref number as null which needs to be made empty string --before inserting to actual table UPDATE TEMP_BANK_TRANSACTION_DETAIL SET CUST_REF_NUMBER = ' ' WHERE CUST_REF_NUMBER IS NULL UPDATE TEMP_BANK_TRANSACTION_DETAIL SET BANK_REF_NUMBER = ' ' WHERE BANK_REF_NUMBER IS NULL --get the min posting date for the bank transaction table SELECT @MinDate = min(POSTING_DATE) FROM BANK_TRANSACTION_DETAIL Set @MinDate = IsNull(@MinDate, '01/01/1900') --do not print the number of rows affected by a Transact-SQL statement from being returned as part of the results SET NOCOUNT ON --EJD - 7/6/2006 --Commented out the Current day file only logic to resolve the duplicate transactions --that occur when prior day transactions are loaded with no balance record. --IF (Upper(@FileEndChar) = 'C') Begin DECLARE SwipeReload_Cursor CURSOR FOR SELECT DISTINCT ACCOUNT_KEY, BANK_KEY, POSTING_DATE FROM TEMP_BANK_TRANSACTION_DETAIL WHERE ACCOUNT_KEY IS NOT NULL AND BANK_KEY IS NOT NULL AND POSTING_DATE IS NOT NULL AND VALUE_DATE IS NOT NULL AND POSTING_DATE >= @MinDate OPEN SwipeReload_Cursor FETCH NEXT FROM SwipeReload_Cursor INTO @AccountKeyForSwipe, @BankKeyForSwipe, @PostingDateForSwipe WHILE @@FETCH_STATUS = 0 BEGIN SELECT @BankAcntIdForSwipe = BANK_ACCOUNT_ID, @StatusForSwipe = STATUS_FLAG FROM BANK_ACCOUNTS WHERE ACCOUNT_KEY = @AccountKeyForSwipe AND BANK_KEY = @BankKeyForSwipe AND ACCOUNT_SOURCE_CODE = '01' If (@@ROWCOUNT > 0) Begin If (@StatusForSwipe = 'A') Begin --wipe reload --delete existing transaction records for that posting date DELETE FROM BANK_TRANSACTION_DETAIL WHERE BANK_ACCOUNT_ID = @BankAcntIdForSwipe AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDateForSwipe, 110) End End FETCH NEXT FROM SwipeReload_Cursor INTO @AccountKeyForSwipe, @BankKeyForSwipe, @PostingDateForSwipe END --end of while condition --close and deallocate cursor CLOSE SwipeReload_Cursor DEALLOCATE SwipeReload_Cursor End --Begin the actual process here --Declare a cursor on records from "TEMP_BANK_BALANCE_DETAIL" table based on criteria below DECLARE UploadBankTrans_Cursor CURSOR FOR SELECT ACCOUNT_KEY, BANK_KEY, POSTING_DATE, VALUE_DATE, CREDIT_AMOUNT, DEBIT_AMOUNT, BANK_REF_NUMBER, TRANSACTION_DESCRIPTION, DETAIL_DESCRIPTION, CUST_REF_NUMBER, TRANSACTION_CODE FROM TEMP_BANK_TRANSACTION_DETAIL WHERE ACCOUNT_KEY IS NOT NULL AND BANK_KEY IS NOT NULL AND POSTING_DATE IS NOT NULL AND VALUE_DATE IS NOT NULL AND POSTING_DATE >= @MinDate OPEN UploadBankTrans_Cursor FETCH NEXT FROM UploadBankTrans_Cursor INTO @AccountKey, @BankKey, @PostingDate, @ValueDate, @CreditAmt, @DebitAmt, @BankRefNum, @TransDesc, @DetailDesc, @CustRefNum, @TransCode WHILE @@FETCH_STATUS = 0 BEGIN --initialize variable SET @Err_No = 0 SET @FirstPrevDate = @PostingDate - 1 SET @Computed_Flag = 'P' SELECT @BankAcntId = BANK_ACCOUNT_ID, @Status = STATUS_FLAG FROM BANK_ACCOUNTS WHERE ACCOUNT_KEY = @AccountKey AND BANK_KEY = @BankKey AND ACCOUNT_SOURCE_CODE = '01' If (@@ROWCOUNT > 0) Begin If (@Status = 'A') Begin SET @SweepFlag = 'N' SELECT @SweepDesc = SWEEP_TRANS_DESCRIPTION FROM BANKS WHERE BANK_KEY = @BankKey AND SWEEP_TRANS_CODE = @TransCode If (@@ROWCOUNT > 0 ) Begin if (Len(@SweepDesc) = 0) Begin SET @SweepFlag = 'Y' End Else if ((Len(@SweepDesc) > 0 And patindex('%' + @SweepDesc + '%' , @TransDesc) <> 0) Or (Len(@SweepDesc) > 0 And patindex('%' + @SweepDesc + '%' , @DetailDesc) <> 0)) Begin SET @SweepFlag = 'Y' End End INSERT INTO BANK_TRANSACTION_DETAIL (BANK_ACCOUNT_ID, POSTING_DATE, VALUE_DATE, CREDIT_AMOUNT, DEBIT_AMOUNT, BANK_REF_NUMBER, TRANSACTION_DESCRIPTION, DETAIL_DESCRIPTION, CUST_REF_NUMBER, TRANSACTION_CODE, SWEEP_FLAG) VALUES (@BankAcntId, @PostingDate, @ValueDate, @CreditAmt, @DebitAmt, @BankRefNum, @TransDesc, @DetailDesc, @CustRefNum, @TransCode, @SweepFlag) --get the error number into the variable SELECT @Err_No = @@ERROR If (@Err_No <> 0 ) Begin Set @Err_Msg = 'Error while inserting into BANK_TRANSACTION_DETAIL table from transaction file for account id : ' + Cast(@BankAcntId as varchar) --insert to error log table INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End End --end of condition check for status (Active) if (Upper(@FileEndChar) = 'C') Begin --initialize variables before setting SET @SumOfCredits = 0 SET @SumOfDebits = 0 --Calculate the total credits and debits which may be needed during calculation of balances SELECT @SumOfCredits = SUM(CREDIT_AMOUNT), @SumOfDebits = SUM(DEBIT_AMOUNT) FROM TEMP_BANK_TRANSACTION_DETAIL WHERE ACCOUNT_KEY =@AccountKey AND BANK_KEY = @BankKey AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) End --Set the variable values SELECT @Count = Count(BANK_ACCOUNT_ID) FROM BANK_BALANCE_DETAIL WHERE BANK_ACCOUNT_ID = @BankAcntId AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) If (@Count > 0) Begin Set @BalanceFlag = 'Y' If (@Status = 'A') Begin SELECT @ComputedFlagBalYCase = COMPUTED_FLAG FROM BANK_BALANCE_DETAIL WHERE BANK_ACCOUNT_ID = @BankAcntId AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) if (Upper(@FileEndChar) = 'C' AND @ComputedFlagBalYCase <> 'P' AND @ComputedFlagBalYCase <> 'X') Begin --reset balance flag back to N Set @BalanceFlag = 'N' --Added on 10-Nov-05 --eBank2005 --to compute the balance record and update the necessary flag get previous day data SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT --previous data available. set computed flag to C .calculate balance record. If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else --previous data is not available. Begin SET @FirstPrevDate = @PostingDate - 1 SELECT @FirstPrevDateInt = DATEPART(weekday, @FirstPrevDate) if ( (@FirstPrevDateInt <> 1) and (@FirstPrevDateInt <> 7) ) --previous day is a business day Begin SET @Computed_Flag = 'E' End else --go to check 2 Begin SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @FirstPrevDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else Begin SET @FirstPrevDate = @FirstPrevDate - 1 SELECT @FirstPrevDateInt = DATEPART(weekday, @FirstPrevDate) if ( (@FirstPrevDateInt <> 1) and (@FirstPrevDateInt <> 7) ) --previous day is a business day Begin SET @Computed_Flag = 'E' End Else --go to check 3 Begin SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @FirstPrevDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else Begin SET @Computed_Flag = 'E' End End --end of else condition for check 3 End End --end of else conditin for check 2 End --at this stage your flag is set now do insert unofficialy balance record or leave with error if ( @Computed_Flag = 'C') Begin SET @LedgerBal = @LedgerBal + @SumOfCredits - @SumOfDebits SET @OpenAvailBal = @OpenAvailBal + @SumOfCredits - @SumOfDebits --update the values UPDATE BANK_BALANCE_DETAIL SET VALUE_DATE = @ValueDateBaln, LEDGER_BAL = @LedgerBal, COLLECTED_BAL = @CollectedBal, OPEN_AVAIL_BAL = @OpenAvailBal, ONE_DAY_FLOAT = @OneDayFloat, TWO_DAY_FLOAT = @TwoDayFloat, OVER_TWO_DAY_FLOAT = @OverTwoDayFloat, TOTAL_CREDITS = @SumOfCredits, TOTAL_DEBITS = @SumOfDebits, COMPUTED_FLAG = 'C', LAST_LOADED_TIMESTAMP = @LastLoadedTimestamp WHERE BANK_ACCOUNT_ID = @BankAcntId and Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) End Else if ( @Computed_Flag = 'E') Begin --insert the values UPDATE BANK_BALANCE_DETAIL SET VALUE_DATE = @ValueDate, LEDGER_BAL = @DUMMY_VAL, COLLECTED_BAL = @DUMMY_VAL, OPEN_AVAIL_BAL = @DUMMY_VAL, ONE_DAY_FLOAT = @DUMMY_VAL, TWO_DAY_FLOAT = @DUMMY_VAL, OVER_TWO_DAY_FLOAT = @DUMMY_VAL, TOTAL_CREDITS = @DUMMY_VAL, TOTAL_DEBITS = @DUMMY_VAL, COMPUTED_FLAG = 'E', LAST_LOADED_TIMESTAMP = @LastLoadedTimestamp WHERE BANK_ACCOUNT_ID = @BankAcntId and Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) --Insert into error log Set @Err_Msg = 'Bank Import : Prior Day Balance (Posting Date - ' + Convert(varchar, @PostingDate, 110) + ') for last calendar/business day not available.' + ' The Acct key/Bank Key/Acct Source is : ' + Cast(@AccountKey as varchar) + ' : ' + Cast(@BankKey as varchar) + ' : ' + '01.' +' Cannot compute Balance amounts.' INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End --re-initialize value SET @BAL_DTL_COUNT = 0 SET @FirstPrevDate = @PostingDate - 1 SET @Computed_Flag = 'P' SET @LedgerBal = 0 SET @OpenAvailBal = 0 SET @SumOfCredits = 0 SET @SumOfDebits = 0 SET @CollectedBal = 0 SET @ComputedFlagBalYCase = 'X' End --condition check if file end char is 'C' End --Condition check if account status = Active End Else Begin Set @BalanceFlag = 'N' if (Upper(@FileEndChar) = 'C' and @Status = 'A') Begin --Added on 10-Nov-05 --eBank2005 --to compute the balance record and update the necessary flag get previous day data SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT --previous data available. set computed flag to C .calculate balance record. If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End else --previous data is not available. Begin SET @FirstPrevDate = @PostingDate - 1 SELECT @FirstPrevDateInt = DATEPART(weekday, @FirstPrevDate) if ( (@FirstPrevDateInt <> 1) and (@FirstPrevDateInt <> 7) ) --previous day is a business day Begin SET @Computed_Flag = 'E' End else --go to check 2 Begin SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @FirstPrevDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else Begin SET @FirstPrevDate = @FirstPrevDate - 1 SELECT @FirstPrevDateInt = DATEPART(weekday, @FirstPrevDate) if ( (@FirstPrevDateInt <> 1) and (@FirstPrevDateInt <> 7) ) --previous day is a business day Begin SET @Computed_Flag = 'E' End Else --go to check 3 Begin SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @FirstPrevDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else Begin SET @Computed_Flag = 'E' End End --end of else condition for check 3 End End --end of else conditin for check 2 End --at this stage your flag is set now do insert unofficialy balance record or leave with error if ( @Computed_Flag = 'C') Begin SET @LedgerBal = @LedgerBal + @SumOfCredits - @SumOfDebits SET @OpenAvailBal = @OpenAvailBal + @SumOfCredits - @SumOfDebits --insert the values INSERT INTO BANK_BALANCE_DETAIL (BANK_ACCOUNT_ID, POSTING_DATE, VALUE_DATE, LEDGER_BAL, COLLECTED_BAL, OPEN_AVAIL_BAL, ONE_DAY_FLOAT, TWO_DAY_FLOAT, OVER_TWO_DAY_FLOAT, TOTAL_CREDITS, TOTAL_DEBITS, COMPUTED_FLAG, LAST_LOADED_TIMESTAMP ) VALUES (@BankAcntId, @PostingDate, @ValueDateBaln, @LedgerBal, @CollectedBal, @OpenAvailBal, @OneDayFloat, @TwoDayFloat, @OverTwoDayFloat, @SumOfCredits, @SumOfDebits, @Computed_Flag, @LastLoadedTimestamp) End else if ( @Computed_Flag = 'E') Begin --insert the values INSERT INTO BANK_BALANCE_DETAIL (BANK_ACCOUNT_ID, POSTING_DATE, VALUE_DATE, LEDGER_BAL, COLLECTED_BAL, OPEN_AVAIL_BAL, ONE_DAY_FLOAT, TWO_DAY_FLOAT, OVER_TWO_DAY_FLOAT, TOTAL_CREDITS, TOTAL_DEBITS, COMPUTED_FLAG, LAST_LOADED_TIMESTAMP ) VALUES (@BankAcntId, @PostingDate, @ValueDate, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @Computed_Flag, @LastLoadedTimestamp) --Insert into error log Set @Err_Msg = 'Bank Import : Prior Day Balance (Posting Date - ' + Convert(varchar, @PostingDate, 110) + ') for last calendar/business day not available.' + ' The Acct key/Bank Key/Acct Source is : ' + Cast(@AccountKey as varchar) + ' : ' + Cast(@BankKey as varchar) + ' : ' + '01.' +' Cannot compute Balance amounts.' INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End --re-initialize value SET @BAL_DTL_COUNT = 0 SET @FirstPrevDate = @PostingDate - 1 SET @Computed_Flag = 'P' SET @LedgerBal = 0 SET @OpenAvailBal = 0 SET @SumOfCredits = 0 SET @SumOfDebits = 0 End --end of condition check if file end char is 'C' and account status = Active End --end of else condition for Balance Flag N case Set @TranFlag = 'Y' If (@Status= 'C') Begin Set @AccountClosed = 'Y' End Else Begin Set @AccountClosed = 'N' End SELECT @Count = Count(BAI_IMPORT_ID) FROM BAI_DATA_IMPORT WHERE BANK_ACCOUNT_ID = @BankAcntId AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) If (@Count > 0) Begin UPDATE BAI_DATA_IMPORT SET VALUE_DATE = @ValueDate, BALANCE_FLAG = @BalanceFlag, TRANSACTION_FLAG = @TranFlag, ACCOUNT_CLOSED = @AccountClosed WHERE BANK_ACCOUNT_ID = @BankAcntId AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) End Else Begin INSERT INTO BAI_DATA_IMPORT (POSTING_DATE, BANK_ACCOUNT_ID, VALUE_DATE, BALANCE_FLAG, TRANSACTION_FLAG, ACCOUNT_CLOSED) VALUES (@PostingDate, @BankAcntId, @ValueDate, @BalanceFlag, @TranFlag, @AccountClosed) End --get the error number into the variable SELECT @Err_No = @@ERROR If (@Err_No <> 0 ) Begin Set @Err_Msg = 'Bank Import : Error while inserting/updating BAI_DATA_IMPORT table from transaction file for account id : ' + Cast(@BankAcntId as varchar) --insert to error log table INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End End --end of condition check for valid account Else Begin Set @Err_Msg = 'Bank Import : Invalid Account Key/Bank Key/Account Source combination in input transaction file :' + Cast(@AccountKey as varchar) + ' : ' + Cast(@BankKey as varchar) + ' : ' + '01' --insert to error log table INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End --fetch next row FETCH NEXT FROM UploadBankTrans_Cursor INTO @AccountKey, @BankKey, @PostingDate, @ValueDate, @CreditAmt, @DebitAmt, @BankRefNum, @TransDesc, @DetailDesc, @CustRefNum, @TransCode END --close and deallocate cursor CLOSE UploadBankTrans_Cursor DEALLOCATE UploadBankTrans_Cursor If (@MinDate > '01/01/1900') Begin --write to error log all the records in temp table that are less than min posting date INSERT INTO ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) SELECT GetDate(), 'Bank Import : Posting date in balance file is lesser than min date ' + Convert(varchar, @MinDate, 110) + ' for the following Account Key/Bank Key/Account Source combination ' + Convert(varchar, ACCOUNT_KEY) + ' : ' + Convert(varchar, BANK_KEY) + ' : 01 ' FROM TEMP_BANK_TRANSACTION_DETAIL WHERE POSTING_DATE < @MinDate End --delete the data in the temporary table TRUNCATE TABLE TEMP_BANK_TRANSACTION_DETAIL insert into [proc] values('spUploadBankTransaction',@FileEndChar,@LastLoadedTimestamp,getdate(),'End') SET NOCOUNT OFF
now this is the one modified one...look right at the bottom section where Saurabh is mention...that part was added..looking them in source safe may help tooCREATE Procedure spUploadBankTransaction @FileEndChar char(1), @LastLoadedTimestamp datetime with recompile As insert into [proc] values('spUploadBankTransaction',@FileEndChar,@LastLoadedTimestamp,getdate(),'start') --******************************************************************************************* --Stored Procedure: spUploadBankTransaction --Purpose: This stored procedure uploads the bank transaction records to the database -- from the data in the temp tables --Input: None --Output: None --Developer: M P Raj --Creation Date: 28 June 2004 --Modified Date: 06 Dec 2005 --******************************************************************************************* --variables for retrieval DECLARE @BankAcntId numeric(9) DECLARE @AccountKey numeric(5) DECLARE @BankKey numeric(5) DECLARE @PostingDate datetime DECLARE @ValueDate datetime DECLARE @CreditAmt numeric(16,2) DECLARE @DebitAmt numeric(16,2) DECLARE @BankRefNum varchar(16) DECLARE @TransDesc varchar(35) DECLARE @DetailDesc varchar(900) DECLARE @CustRefNum varchar(35) DECLARE @TransCode numeric(12,0) DECLARE @SweepFlag char(1) DECLARE @SweepDesc varchar(35) DECLARE @Status varchar(2) --variables for updation of import table DECLARE @BalanceFlag char(1) DECLARE @TranFlag char(1) DECLARE @AccountClosed char(1) DECLARE @Count int DECLARE @MinDate datetime --variables for error loggin DECLARE @Err_No int DECLARE @Err_Msg varchar(200) --added on 11-Nov-2005 - eBank2005 DECLARE @BAL_DTL_COUNT INT DECLARE @Computed_Flag char(1) DECLARE @DUMMY_VAL int DECLARE @PostingDateBaln datetime DECLARE @ValueDateBaln datetime DECLARE @LedgerBal numeric(16,2) DECLARE @OpenAvailBal numeric(16,2) DECLARE @CollectedBal numeric(16,2) DECLARE @OneDayFloat numeric(16,2) DECLARE @TwoDayFloat numeric(16,2) DECLARE @OverTwoDayFloat numeric(16,2) DECLARE @TotalCredits numeric(16,2) DECLARE @TotalDebits numeric(16,2) DECLARE @FirstPrevDateInt int DECLARE @FirstPrevDate datetime --added on Dec01 DECLARE @BankAcntIdForSwipe numeric(9) DECLARE @AccountKeyForSwipe numeric(5) DECLARE @BankKeyForSwipe numeric(5) DECLARE @PostingDateForSwipe datetime DECLARE @StatusForSwipe varchar(2) DECLARE @ComputedFlagBalYCase char(1) DECLARE @SumOfCredits numeric(16,2) DECLARE @SumOfDebits numeric(16,2) --Added by Saurabh as part of ER#4 for caputing multiple occurences of Bank Import DECLARE @BAI_Computed_Flag char(1) SET @LedgerBal = 0 SET @OpenAvailBal = 0 SET @CollectedBal = 0 SET @OneDayFloat = 0 SET @TwoDayFloat = 0 SET @OverTwoDayFloat = 0 SET @TotalCredits = 0 SET @TotalDebits = 0 SET @SumOfCredits = 0 SET @SumOfDebits = 0 SET @DUMMY_VAL = -1 SET @BAL_DTL_COUNT = 0 SET @Computed_Flag = 'P' SET @ComputedFlagBalYCase = 'X' --initialize to non-existant value SET @FirstPrevDateInt = 0 --set the date format Set DateFormat 'mdy' --The temp table may contain the customer/bank ref number as null which needs to be made empty string --before inserting to actual table UPDATE TEMP_BANK_TRANSACTION_DETAIL SET CUST_REF_NUMBER = ' ' WHERE CUST_REF_NUMBER IS NULL UPDATE TEMP_BANK_TRANSACTION_DETAIL SET BANK_REF_NUMBER = ' ' WHERE BANK_REF_NUMBER IS NULL --get the min posting date for the bank transaction table SELECT @MinDate = min(POSTING_DATE) FROM BANK_TRANSACTION_DETAIL Set @MinDate = IsNull(@MinDate, '01/01/1900') --do not print the number of rows affected by a Transact-SQL statement from being returned as part of the results SET NOCOUNT ON --EJD - 7/6/2006 --Commented out the Current day file only logic to resolve the duplicate transactions --that occur when prior day transactions are loaded with no balance record. --IF (Upper(@FileEndChar) = 'C') Begin DECLARE SwipeReload_Cursor CURSOR FOR SELECT DISTINCT ACCOUNT_KEY, BANK_KEY, POSTING_DATE FROM TEMP_BANK_TRANSACTION_DETAIL WHERE ACCOUNT_KEY IS NOT NULL AND BANK_KEY IS NOT NULL AND POSTING_DATE IS NOT NULL AND VALUE_DATE IS NOT NULL AND POSTING_DATE >= @MinDate OPEN SwipeReload_Cursor FETCH NEXT FROM SwipeReload_Cursor INTO @AccountKeyForSwipe, @BankKeyForSwipe, @PostingDateForSwipe WHILE @@FETCH_STATUS = 0 BEGIN SELECT @BankAcntIdForSwipe = BANK_ACCOUNT_ID, @StatusForSwipe = STATUS_FLAG FROM BANK_ACCOUNTS WHERE ACCOUNT_KEY = @AccountKeyForSwipe AND BANK_KEY = @BankKeyForSwipe AND ACCOUNT_SOURCE_CODE = '01' If (@@ROWCOUNT > 0) Begin If (@StatusForSwipe = 'A') Begin --wipe reload --delete existing transaction records for that posting date DELETE FROM BANK_TRANSACTION_DETAIL WHERE BANK_ACCOUNT_ID = @BankAcntIdForSwipe AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDateForSwipe, 110) End End FETCH NEXT FROM SwipeReload_Cursor INTO @AccountKeyForSwipe, @BankKeyForSwipe, @PostingDateForSwipe END --end of while condition --close and deallocate cursor CLOSE SwipeReload_Cursor DEALLOCATE SwipeReload_Cursor End --Begin the actual process here --Declare a cursor on records from "TEMP_BANK_BALANCE_DETAIL" table based on criteria below DECLARE UploadBankTrans_Cursor CURSOR FOR SELECT ACCOUNT_KEY, BANK_KEY, POSTING_DATE, VALUE_DATE, CREDIT_AMOUNT, DEBIT_AMOUNT, BANK_REF_NUMBER, TRANSACTION_DESCRIPTION, DETAIL_DESCRIPTION, CUST_REF_NUMBER, TRANSACTION_CODE FROM TEMP_BANK_TRANSACTION_DETAIL WHERE ACCOUNT_KEY IS NOT NULL AND BANK_KEY IS NOT NULL AND POSTING_DATE IS NOT NULL AND VALUE_DATE IS NOT NULL AND POSTING_DATE >= @MinDate OPEN UploadBankTrans_Cursor FETCH NEXT FROM UploadBankTrans_Cursor INTO @AccountKey, @BankKey, @PostingDate, @ValueDate, @CreditAmt, @DebitAmt, @BankRefNum, @TransDesc, @DetailDesc, @CustRefNum, @TransCode WHILE @@FETCH_STATUS = 0 BEGIN --initialize variable SET @Err_No = 0 SET @FirstPrevDate = @PostingDate - 1 SET @Computed_Flag = 'P' SELECT @BankAcntId = BANK_ACCOUNT_ID, @Status = STATUS_FLAG FROM BANK_ACCOUNTS WHERE ACCOUNT_KEY = @AccountKey AND BANK_KEY = @BankKey AND ACCOUNT_SOURCE_CODE = '01' If (@@ROWCOUNT > 0) Begin If (@Status = 'A') Begin SET @SweepFlag = 'N' SELECT @SweepDesc = SWEEP_TRANS_DESCRIPTION FROM BANKS WHERE BANK_KEY = @BankKey AND SWEEP_TRANS_CODE = @TransCode If (@@ROWCOUNT > 0 ) Begin if (Len(@SweepDesc) = 0) Begin SET @SweepFlag = 'Y' End Else if ((Len(@SweepDesc) > 0 And patindex('%' + @SweepDesc + '%' , @TransDesc) <> 0) Or (Len(@SweepDesc) > 0 And patindex('%' + @SweepDesc + '%' , @DetailDesc) <> 0)) Begin SET @SweepFlag = 'Y' End End INSERT INTO BANK_TRANSACTION_DETAIL (BANK_ACCOUNT_ID, POSTING_DATE, VALUE_DATE, CREDIT_AMOUNT, DEBIT_AMOUNT, BANK_REF_NUMBER, TRANSACTION_DESCRIPTION, DETAIL_DESCRIPTION, CUST_REF_NUMBER, TRANSACTION_CODE, SWEEP_FLAG) VALUES (@BankAcntId, @PostingDate, @ValueDate, @CreditAmt, @DebitAmt, @BankRefNum, @TransDesc, @DetailDesc, @CustRefNum, @TransCode, @SweepFlag) --get the error number into the variable SELECT @Err_No = @@ERROR If (@Err_No <> 0 ) Begin Set @Err_Msg = 'Error while inserting into BANK_TRANSACTION_DETAIL table from transaction file for account id : ' + Cast(@BankAcntId as varchar) --insert to error log table INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End End --end of condition check for status (Active) if (Upper(@FileEndChar) = 'C') Begin --initialize variables before setting SET @SumOfCredits = 0 SET @SumOfDebits = 0 --Calculate the total credits and debits which may be needed during calculation of balances SELECT @SumOfCredits = SUM(CREDIT_AMOUNT), @SumOfDebits = SUM(DEBIT_AMOUNT) FROM TEMP_BANK_TRANSACTION_DETAIL WHERE ACCOUNT_KEY =@AccountKey AND BANK_KEY = @BankKey AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) End --Set the variable values SELECT @Count = Count(BANK_ACCOUNT_ID) FROM BANK_BALANCE_DETAIL WHERE BANK_ACCOUNT_ID = @BankAcntId AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) If (@Count > 0) Begin Set @BalanceFlag = 'Y' If (@Status = 'A') Begin SELECT @ComputedFlagBalYCase = COMPUTED_FLAG FROM BANK_BALANCE_DETAIL WHERE BANK_ACCOUNT_ID = @BankAcntId AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) if (Upper(@FileEndChar) = 'C' AND @ComputedFlagBalYCase <> 'P' AND @ComputedFlagBalYCase <> 'X') Begin --reset balance flag back to N Set @BalanceFlag = 'N' --Added on 10-Nov-05 --eBank2005 --to compute the balance record and update the necessary flag get previous day data SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT --previous data available. set computed flag to C .calculate balance record. If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else --previous data is not available. Begin SET @FirstPrevDate = @PostingDate - 1 SELECT @FirstPrevDateInt = DATEPART(weekday, @FirstPrevDate) if ( (@FirstPrevDateInt <> 1) and (@FirstPrevDateInt <> 7) ) --previous day is a business day Begin SET @Computed_Flag = 'E' End else --go to check 2 Begin SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @FirstPrevDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else Begin SET @FirstPrevDate = @FirstPrevDate - 1 SELECT @FirstPrevDateInt = DATEPART(weekday, @FirstPrevDate) if ( (@FirstPrevDateInt <> 1) and (@FirstPrevDateInt <> 7) ) --previous day is a business day Begin SET @Computed_Flag = 'E' End Else --go to check 3 Begin SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @FirstPrevDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else Begin SET @Computed_Flag = 'E' End End --end of else condition for check 3 End End --end of else conditin for check 2 End --at this stage your flag is set now do insert unofficialy balance record or leave with error if ( @Computed_Flag = 'C') Begin SET @LedgerBal = @LedgerBal + @SumOfCredits - @SumOfDebits SET @OpenAvailBal = @OpenAvailBal + @SumOfCredits - @SumOfDebits --update the values UPDATE BANK_BALANCE_DETAIL SET VALUE_DATE = @ValueDateBaln, LEDGER_BAL = @LedgerBal, COLLECTED_BAL = @CollectedBal, OPEN_AVAIL_BAL = @OpenAvailBal, ONE_DAY_FLOAT = @OneDayFloat, TWO_DAY_FLOAT = @TwoDayFloat, OVER_TWO_DAY_FLOAT = @OverTwoDayFloat, TOTAL_CREDITS = @SumOfCredits, TOTAL_DEBITS = @SumOfDebits, COMPUTED_FLAG = 'C', LAST_LOADED_TIMESTAMP = @LastLoadedTimestamp WHERE BANK_ACCOUNT_ID = @BankAcntId and Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) End Else if ( @Computed_Flag = 'E') Begin --insert the values UPDATE BANK_BALANCE_DETAIL SET VALUE_DATE = @ValueDate, LEDGER_BAL = @DUMMY_VAL, COLLECTED_BAL = @DUMMY_VAL, OPEN_AVAIL_BAL = @DUMMY_VAL, ONE_DAY_FLOAT = @DUMMY_VAL, TWO_DAY_FLOAT = @DUMMY_VAL, OVER_TWO_DAY_FLOAT = @DUMMY_VAL, TOTAL_CREDITS = @DUMMY_VAL, TOTAL_DEBITS = @DUMMY_VAL, COMPUTED_FLAG = 'E', LAST_LOADED_TIMESTAMP = @LastLoadedTimestamp WHERE BANK_ACCOUNT_ID = @BankAcntId and Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) --Insert into error log Set @Err_Msg = 'Bank Import : Prior Day Balance (Posting Date - ' + Convert(varchar, @PostingDate, 110) + ') for last calendar/business day not available.' + ' The Acct key/Bank Key/Acct Source is : ' + Cast(@AccountKey as varchar) + ' : ' + Cast(@BankKey as varchar) + ' : ' + '01.' +' Cannot compute Balance amounts.' INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End --re-initialize value SET @BAL_DTL_COUNT = 0 SET @FirstPrevDate = @PostingDate - 1 SET @Computed_Flag = 'P' SET @LedgerBal = 0 SET @OpenAvailBal = 0 SET @SumOfCredits = 0 SET @SumOfDebits = 0 SET @CollectedBal = 0 SET @ComputedFlagBalYCase = 'X' End --condition check if file end char is 'C' End --Condition check if account status = Active End Else Begin Set @BalanceFlag = 'N' if (Upper(@FileEndChar) = 'C' and @Status = 'A') Begin --Added on 10-Nov-05 --eBank2005 --to compute the balance record and update the necessary flag get previous day data SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT --previous data available. set computed flag to C .calculate balance record. If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End else --previous data is not available. Begin SET @FirstPrevDate = @PostingDate - 1 SELECT @FirstPrevDateInt = DATEPART(weekday, @FirstPrevDate) if ( (@FirstPrevDateInt <> 1) and (@FirstPrevDateInt <> 7) ) --previous day is a business day Begin SET @Computed_Flag = 'E' End else --go to check 2 Begin SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @FirstPrevDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else Begin SET @FirstPrevDate = @FirstPrevDate - 1 SELECT @FirstPrevDateInt = DATEPART(weekday, @FirstPrevDate) if ( (@FirstPrevDateInt <> 1) and (@FirstPrevDateInt <> 7) ) --previous day is a business day Begin SET @Computed_Flag = 'E' End Else --go to check 3 Begin SELECT TOP 1 @PostingDateBaln = POSTING_DATE, @ValueDateBaln = VALUE_DATE, @LedgerBal = LEDGER_BAL, @OpenAvailBal= OPEN_AVAIL_BAL, @CollectedBal = COLLECTED_BAL , @OneDayFloat = ONE_DAY_FLOAT , @TwoDayFloat = TWO_DAY_FLOAT, @OverTwoDayFloat= OVER_TWO_DAY_FLOAT, @TotalCredits = TOTAL_CREDITS, @TotalDebits = TOTAL_DEBITS FROM BANK_BALANCE_DETAIL WHERE Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @FirstPrevDate - 1, 110) AND BANK_ACCOUNT_ID = @BankAcntId AND COMPUTED_FLAG = 'P' ORDER BY POSTING_DATE DESC SELECT @BAL_DTL_COUNT =@@ROWCOUNT If (@BAL_DTL_COUNT > 0) Begin SET @Computed_Flag = 'C' End Else Begin SET @Computed_Flag = 'E' End End --end of else condition for check 3 End End --end of else conditin for check 2 End --at this stage your flag is set now do insert unofficialy balance record or leave with error if ( @Computed_Flag = 'C') Begin SET @LedgerBal = @LedgerBal + @SumOfCredits - @SumOfDebits SET @OpenAvailBal = @OpenAvailBal + @SumOfCredits - @SumOfDebits --insert the values INSERT INTO BANK_BALANCE_DETAIL (BANK_ACCOUNT_ID, POSTING_DATE, VALUE_DATE, LEDGER_BAL, COLLECTED_BAL, OPEN_AVAIL_BAL, ONE_DAY_FLOAT, TWO_DAY_FLOAT, OVER_TWO_DAY_FLOAT, TOTAL_CREDITS, TOTAL_DEBITS, COMPUTED_FLAG, LAST_LOADED_TIMESTAMP ) VALUES (@BankAcntId, @PostingDate, @ValueDateBaln, @LedgerBal, @CollectedBal, @OpenAvailBal, @OneDayFloat, @TwoDayFloat, @OverTwoDayFloat, @SumOfCredits, @SumOfDebits, @Computed_Flag, @LastLoadedTimestamp) End else if ( @Computed_Flag = 'E') Begin --insert the values INSERT INTO BANK_BALANCE_DETAIL (BANK_ACCOUNT_ID, POSTING_DATE, VALUE_DATE, LEDGER_BAL, COLLECTED_BAL, OPEN_AVAIL_BAL, ONE_DAY_FLOAT, TWO_DAY_FLOAT, OVER_TWO_DAY_FLOAT, TOTAL_CREDITS, TOTAL_DEBITS, COMPUTED_FLAG, LAST_LOADED_TIMESTAMP ) VALUES (@BankAcntId, @PostingDate, @ValueDate, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @DUMMY_VAL, @Computed_Flag, @LastLoadedTimestamp) --Insert into error log Set @Err_Msg = 'Bank Import : Prior Day Balance (Posting Date - ' + Convert(varchar, @PostingDate, 110) + ') for last calendar/business day not available.' + ' The Acct key/Bank Key/Acct Source is : ' + Cast(@AccountKey as varchar) + ' : ' + Cast(@BankKey as varchar) + ' : ' + '01.' +' Cannot compute Balance amounts.' INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End --re-initialize value SET @BAL_DTL_COUNT = 0 SET @FirstPrevDate = @PostingDate - 1 SET @Computed_Flag = 'P' SET @LedgerBal = 0 SET @OpenAvailBal = 0 SET @SumOfCredits = 0 SET @SumOfDebits = 0 End --end of condition check if file end char is 'C' and account status = Active End --end of else condition for Balance Flag N case Set @TranFlag = 'Y' If (@Status= 'C') Begin Set @AccountClosed = 'Y' End Else Begin Set @AccountClosed = 'N' End --Added by Saurabh as part of ER#4 for capturing Multiple occurences in Bank Import SELECT @BAI_Computed_Flag = COMPUTED_FLAG FROM BANK_BALANCE_DETAIL WHERE BANK_ACCOUNT_ID = @BankAcntId AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) SELECT @Count = Count(BAI_IMPORT_ID) FROM BAI_DATA_IMPORT WHERE BANK_ACCOUNT_ID = @BankAcntId AND -- Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) POSTING_DATE =@PostingDate --Added by Saurabh as part of ER#4 AND BAI_COMPUTED_FLAG = @BAI_Computed_Flag If (@Count > 0) Begin UPDATE BAI_DATA_IMPORT SET VALUE_DATE = @ValueDate, BALANCE_FLAG = @BalanceFlag, TRANSACTION_FLAG = @TranFlag, ACCOUNT_CLOSED = @AccountClosed, --Added by Saurabh as part of ER#4 BAI_COMPUTED_FLAG = @BAI_Computed_Flag, LAST_LOADED_TIMESTAMP = @LastLoadedTimestamp WHERE BANK_ACCOUNT_ID = @BankAcntId AND Convert(varchar, POSTING_DATE, 110) = Convert(varchar, @PostingDate, 110) AND BAI_COMPUTED_FLAG = @BAI_Computed_Flag End Else Begin --Modified for adding BAI_DATA_IMPORT column INSERT INTO BAI_DATA_IMPORT (POSTING_DATE, BANK_ACCOUNT_ID, VALUE_DATE, BALANCE_FLAG, TRANSACTION_FLAG, ACCOUNT_CLOSED,BAI_COMPUTED_FLAG,LAST_LOADED_TIMESTAMP) VALUES (@PostingDate, @BankAcntId, @ValueDate, @BalanceFlag, @TranFlag, @AccountClosed,@BAI_Computed_Flag,@LastLoadedTimestamp) End --get the error number into the variable SELECT @Err_No = @@ERROR If (@Err_No <> 0 ) Begin Set @Err_Msg = 'Bank Import : Error while inserting/updating BAI_DATA_IMPORT table from transaction file for account id : ' + Cast(@BankAcntId as varchar) --insert to error log table INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End End --end of condition check for valid account Else Begin Set @Err_Msg = 'Bank Import : Invalid Account Key/Bank Key/Account Source combination in input transaction file :' + Cast(@AccountKey as varchar) + ' : ' + Cast(@BankKey as varchar) + ' : ' + '01' --insert to error log table INSERT ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) VALUES (GetDate(), @Err_Msg) End --fetch next row FETCH NEXT FROM UploadBankTrans_Cursor INTO @AccountKey, @BankKey, @PostingDate, @ValueDate, @CreditAmt, @DebitAmt, @BankRefNum, @TransDesc, @DetailDesc, @CustRefNum, @TransCode END --close and deallocate cursor CLOSE UploadBankTrans_Cursor DEALLOCATE UploadBankTrans_Cursor If (@MinDate > '01/01/1900') Begin --write to error log all the records in temp table that are less than min posting date INSERT INTO ERROR_LOG (ERROR_DATE, ERROR_DESCRIPTION) SELECT GetDate(), 'Bank Import : Posting date in balance file is lesser than min date ' + Convert(varchar, @MinDate, 110) + ' for the following Account Key/Bank Key/Account Source combination ' + Convert(varchar, ACCOUNT_KEY) + ' : ' + Convert(varchar, BANK_KEY) + ' : 01 ' FROM TEMP_BANK_TRANSACTION_DETAIL WHERE POSTING_DATE < @MinDate End --delete the data in the temporary table TRUNCATE TABLE TEMP_BANK_TRANSACTION_DETAIL insert into [proc] values('spUploadBankTransaction',@FileEndChar,@LastLoadedTimestamp,getdate(),'End') SET NOCOUNT OFF |