Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SQL Server Proc Peformance Issue

Author  Topic 

ankit1407
Starting Member

4 Posts

Posted - 2008-09-05 : 10:55:56
Hi guys this is the first time being here so in case if i post this at a wrong place or something just excuse me and guide me to the right place.

The problem is around 1 procedure taking hell lot of a time. It was modified a bit recently which seems to have caused it. for eg stuff which could happen in say 3 mins now takes some where in the region of 20mins or more.

Let me know if someone is ready to help and i can post the details and proc here.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-09-05 : 11:28:00
Post the code and post the tables DDL. Possible that you're being affected by "parameter sniffing" or poor database indices.....or maybe just even an increase in data volumes.
Go to Top of Page

ankit1407
Starting Member

4 Posts

Posted - 2008-09-05 : 11:40:18
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 too



CREATE 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

Go to Top of Page

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-09-08 : 06:30:01
oh sh*t. CURSORS???

No matter what I suggest will be nearly a waste of time while this code involves CURSORS...your performance is near doomed by their inclusion. The code is also very long for any remote analysis given lack of familiarity with the data and business processes involved.

I'd advise you read up on this website about CURSORS and performance (or lack thereof). Read too about SET-BASED processing.

I'd advise a re-write/simplification of this entire code to get away from the cursors.

Your use of the TRUNCATE statement also brings in the overhead of needing this code to be executed by a user by SA priviliges (thus creating security loophole/weakness)...which should be unnecessary with proper design.
Go to Top of Page

ankit1407
Starting Member

4 Posts

Posted - 2008-09-08 : 16:27:44
i understand cursors are waste of time and memory, but i guess you will understand some time systems are written years back and its not so easy to go and change each and every line which is written there.

I will try with using a data table and read what you advises.
Go to Top of Page
   

- Advertisement -