SQL Server Performance Forum – Threads Archive

SP Execution Time Difference

Dear Friends I am having a typical problem
I am working in project that deals with a bank that has got 40 branches.Intially the bank has given us a single branch’s data to test.In this single branch data When I execute an Sp it takes 4minutes 38 seconds to complete.When the data for the remaining 39 branches are migrated the SP is taking 9minutes.51secs to finish.Can Somebody guide me in this. Deep Regards
Anil Never Give Up

We need more informations in order to help you. The code of the SP would be a good starter. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

Dear FrankKallis Thanks For Your Response.
I will Paste the code for you
This SP contains several Sub Sp’s out of whic i am placing the important Sp’s.I doubt the problem is in the last SP as it contains a loop. /*
begin tran
EXEC IRC_SPrdIntCalc’25’, ’14’, ’06-07-2004′, ’06-07-2004′,’06-07-2004′,”,’ACCRUAL’
rollback tran
*/
alter PROCEDURE dbo.IRC_sPrdIntCalc
(
@p_BrID UDVC10 = Null,
@p_PrdID UDVC16,
@p_CalcToDt UDVC25 = Null,
@p_BusnDt UDVC25 = Null,
@p_ValueDt UDVC25 = Null,
@p_LoginID UDVC25 = Null,
@p_Purpose UDVC25 = Null
)
AS
BEGIN
SET NOCOUNT ON
/**************************************************************************
‘SP NAME :IRC_sPrdIntCalc
‘Parameter :BrID, PrdID, AcctID, Calculation Date,Business Date,
Value Date, Login ID and Purpose
‘Output Parameter :
‘Created By :EDA
‘Created Date :01-06-2005
‘Description :This sp will be used for calculating the interest at the
product level.
‘Modified By :EDA(EDA0304200601)
‘Modified Date :03.04.2006 11:25(For Reversal purpose Demand added)
‘Modified By : RMA 19:28 >> RMA20060525 (TmpIrHdr,TmpIrDet not Cleared For Each Account)
‘Modified Date : 25.05.2006
‘**************************************************************************/
DECLARE @m_AcctID UDVC16,@m_MinSlNo UDINT,
@m_MaxSlNo UDINT,@HasintDepdn UDCH1,
@HasSplRates UDCH1,@HasIntParams UDCH1,
@AcctOpenDt UDDT,@AcctOpnAmt UDLDGRAMT,
@TenureDays UDINT, @TenureMonths UDINT,
@TenureYears UDINT,@p_Inttype UDVC16,
@m_CalcToDt UDDT,@m_BusnDt UDDT,
@m_HasFixedIntRt UDCH1,@m_HasZeroMinSlNo UDINT,
@m_HasZeroMaxSlNo UDINT,@m_ValueDt UDDT,
@m_IsReqMinIntDue UDCH1,@m_NxtDmdDt UDDT,
@m_NxtAccrDt UDDT,@m_NxtCaptDt UDDT,
@m_NxtDt UDDT,@m_NxtIntCalcDt UDDT,
@m_ErrMsgID UDVC10,@m_KeyValues UDVC50,
@m_AcctCnt UDBINT,@m_SetCurDtTime UDVC25,
@m_Freq UDVC10, @m_AccrFreq UDVC10,
@m_DmdFreq UDVC10, @m_IntCalcFreq UDVC10,
@m_CaptFreq UDVC10, @m_CmpFreq UDVC10,
@m_CapitalizeFreqType UDVC10,@m_AccrualFreqType UDVC10,
@m_DemandFreqType UDVC10,@m_IntProdDays UDINT –<< For the purpose of EOT >>–
SET @m_SetCurDtTime = dbo.fgetbusndate(GETDATE(),’DMY-‘) SET @m_CalcToDt = dbo.fSetBusnDate(@P_CalcToDt)
SET @m_BusnDt = dbo.fSetBusnDate(@p_BusnDt)
SET @m_ValueDt = dbo.fSetBusnDate(@p_ValueDt) CREATE TABLE #TmptCalcSlab
(
BrID VARCHAR(16),PrdID VARCHAR(16),AcctID VarChar(16),
FromDt DATETIME, ToDt DATETIME,TotOS UDLDGRAMT, TotPrinOD UDLDGRAMT,
TotIntOD UDLDGRAMT,TotOthOD UDLDGRAMT,
DurSlabID UDINT,AmtSlabID UDINT,PerSlabID UDINT,
DurSlabType UDVC10, AmtSlabType UDVC10,PerSlabType UDVC10,
ROI NUMERIC(12,4),PenalROI NUMERIC(12,4),PenaltyROI NUMERIC(12,4),IRID UDINT,
–OptnType VARCHAR(25), OPtnTypeID VARCHAR(10),
Principal UDLDGRAMT,Interest UDLDGRAMT, Penal UDLDGRAMT,
Penalty UDLDGRAMT, SlabNo BIGINT Identity(1,1),
Flag CHAR(1), DmdFlag CHAR(1), PreInt NUMERIC(12,4),AcctCyID VARCHAR(5),
InstallAmt UDLDGRAMT, PreClsROI NUMERIC(12,4),DelFlag Nvarchar(3)
) –<< FOR DISPLAYING THE FINAL OUTPUT
CREATE TABLE #TmptCalcSlabFinal
(
BrID VARCHAR(16),PrdID VARCHAR(16),AcctID VarChar(16),
FromDt DATETIME, ToDt DATETIME,TotOS UDLDGRAMT, TotPrinOD UDLDGRAMT,
TotIntOD UDLDGRAMT,TotOthOD UDLDGRAMT,
DurSlabID UDINT,AmtSlabID UDINT,PerSlabID UDINT,
DurSlabType UDVC10,AmtSlabType UDVC10,PerSlabType UDVC10,
ROI NUMERIC(12,4),PenalROI NUMERIC(12,4),PenaltyROI NUMERIC(12,4),IRID UDINT,
Principal UDLDGRAMT,Interest UDLDGRAMT, Penal UDLDGRAMT,
Penalty UDLDGRAMT, SlabNo BIGINT Identity(1,1),
Flag CHAR(1),PreInt NUMERIC(12,4),AcctCyID VARCHAR(5),InstallAmt UDLDGRAMT,
PreClsROI NUMERIC(12,4)
) CREATE TABLE #TmptAcctDetails
(
BrIDVARCHAR(16),PrdIDVARCHAR(16),AcctID VarChar(16),
AcctOpenDt DATETIME,AcctOpnAmt UDLDGRAMT,TenureDays UDINT,
TenureMonths UDINT,TenureYears UDINT,
HasintDepdn CHAR(1),HasSplRates CHAR(1),HasIntParams CHAR(1),
HasFixedIntRt CHAR(1),NxtDmdDt DATETIME, NxtAccrDt DATETIME,
NxtCaptDt DATETIME, NxtIntCalcDt DATETIME,IsRaiseCurrDem CHAR(1),
PrdSplRtID BIGINT, IntDev VARCHAR(5), IntDevPerc NUMERIC(12,4),
ExpiryDt DATETIME, AcctCyID VARCHAR(5),InstallAmt UDLDGRAMT,
LstDmdDt DATETIME, LstCaptDt DATETIME, LstAccrDt DATETIME,
LstIntCalcDt DATETIME, RoutExec UDCH1, ValueDt DATETIME,MatAmt UDLDGRAMT,
IntContinue UDCH1, ForeClsDt DATETIME, LstCompDt DATETIME,
TotOS UDLDGRAMT, ARCStat UDCH1, LstPenaltyCalcDt UDDT, SlNo INT IDENTITY(1,1)
) –<<< Temp. table for storing ledger records >>>—
CREATE TABLE #TmpCapitalize
(
BrID VARCHAR(16), PrdIDVARCHAR(16), AcctID VarChar(16),
CyID VARCHAR(16), MnLdgrInsGrpID VARCHAR(10), MnLdgrInsGrpSlNo VARCHAR(10),
OprType VARCHAR(25),OprBrID VARCHAR(10), JVNo VARCHAR(16),
FASTxnID VARCHAR(16), BusnDt DATETIME,OptnType VARCHAR(25),
OptnTypeID VARCHAR(10),SettlementID VARCHAR(10),
TXNID VARCHAR(25),TXNAMT UDLDGRAMT,
TXNBrID VARCHAR(10), TXNPrdID VARCHAR(10), TXNAcctID VARCHAR(16),
TXNJVNo VARCHAR(16)
) CREATE TABLE #TmptIntParamsDetl
(
BrID VARCHAR(16) NULL,PrdID VARCHAR(16),
AcctID VARCHAR(16) NULL,IntRateType VARCHAR(10),
IntCalcType VARCHAR(10), IntCompFreq VARCHAR(10),
CompFrqType VARCHAR(25), CalcFrqType VARCHAR(25),
IntCalcFreq VARCHAR(10), ProdDaysCalcType VARCHAR(10),
IntRepayFreq VARCHAR(10), IntRepayType VARCHAR(10),
IntPayMode VARCHAR(10), IntCapitalizeFreq VARCHAR(10),
IntAccrualFreq VARCHAR(10), IntProdDays INTEGER,
IntBaseTypeID CHAR(1), IntDeviation NUMERIC(12,4),
IntDemandFreq VARCHAR(10), CapitalizeFreqType VARCHAR(10),
AccrualFreqType VARCHAR(10), DemandFreqType VARCHAR(10)
) CREATE TABLE #TmptIRHdr
(
BrID VARCHAR(10) Null,PrdID VARCHAR(16),AcctID VARCHAR(10) Null,
IRID INTEGER, CyID VARCHAR(16),EffDate DATETIME,IRType VARCHAR(10),
DurSlabType VARCHAR(10),AmtSlabType VARCHAR(10),PerSlabType VARCHAR(10),
IRSlNo Int Identity(1,1)
) CREATE TABLE #TmptIRDetl
(
BrID VARCHAR(16),PrdID VARCHAR(16), AcctID VARCHAR(16),
IRID INTEGER,DurSlabID INTEGER, AmtSlabID INTEGER,
PercSlabID INTEGER, DurSlabUpperYrs INTEGER,
DurSlabLowerYrs INTEGER NULL,
DurSlabUpperMns INTEGER NULL,DurSlabLowerMns INTEGER NULL,
DurSlabUpperDays INTEGER NULL,DurSlabLowerDays INTEGER NULL,
AmtSlabUpper UDLDGRAMT,AmtSlabLower UDLDGRAMT,
PercSlabUpper NUMERIC(8,4),PercSlabLower NUMERIC(8,4),
ROI NUMERIC(8,4),PenalROI NUMERIC(8,4),Penalty NUMERIC(8,4),
ExpiryROI NUMERIC(8,4),PreClsROI NUMERIC(8,4),ForeClsROI NUMERIC(8,4)
) CREATE TABLE #TmptPrdParams
(IsReqMonthBaln UDCH1, ODGracePeriod UDSINT, PreClsDev NUMERIC(12,4), PrdType UDVC10,
PrdClosedDt UDDT,ForeClStat UDCH1,ODIntAllowPerd UDINT) INSERT INTO #TmptPrdParams
(IsReqMonthBaln,ODGracePeriod , PreClsDev, PrdType, PrdClosedDt,ForeClStat,ODIntAllowPerd)
SELECT IsReqMonthBaln, ODGracePeriod, IsNull(PreClsDev,0), PrdType, PrdClosedDt,
IsNull(ForeClStat,’N’),IsNull(ODIntAllowPerd,0)
FROMSAM_tProductMst PrdMst, SAM_tPrdParams PrdParams
WHEREPrdMst.PrdID= @p_PrdID
ANDPrdMst.PrdID = PrdParams.PrdID — BEGIN TRAN –<< Start the New Transaction
–<< GET THE ACCOUNT DETAILS
INSERT INTO #TmptAcctDetails
(BrID, PrdID, AcctID, AcctOpenDt, AcctOpnAmt, TenureDays, TenureMonths,
TenureYears,HasintDepdn,HasSplRates,HasIntParams,HasFixedIntRt,
NxtDmdDt,NxtAccrDt,NxtCaptDt,NxtIntCalcDt,IsRaiseCurrDem,PrdSplRtID,
IntDev,IntDevPerc,AcctCyID,ExpiryDt,InstallAmt,LstIntCalcDt,RoutExec,ValueDt,MatAmt,
IntContinue,ForeClsDt,LstCompDt,LstDmdDt,LstAccrDt,LstCaptDt,TotOS,ARCStat,LstPenaltyCalcDt)
SELECT BrID,PrdID,AcctID,AcctOpenDt,AcctOpnAmt,TenureDays,TenureMonths,
TenureYears,HasintDepdn,HasSplRates,HasIntParams,IsNull(HasFixedIntRt,’N’),
NxtDmdDt,NxtAccrDt,NxtCaptDt,NxtIntCalcDt,IsRaiseCurrDem,PrdSplRtID,
IntDev,IntDevPerc,AcctCyID,ExpiryDt,InstallAmt,LstIntCalcDt,RoutExec,
ValueDt,MatAmt,IntContinue,ForeClsDt,LstCompDt,LstDmdDt,LstAccrDt,
LstCaptDt,TotOS,ARCStat,LstPenaltyCalcDt
FROM dbo.IRC_fFetchAcctDetl(@p_BrID,@p_PrdID,Null,@m_ValueDt,@p_Purpose, 0,0) –<< For EOT Error Log Purpose >>–
SELECT @m_AcctCnt = COUNT(AcctID) FROM #TmptAcctDetails –<<Added by EDA on 20.09.2005(for validating the transacted account)
IF NOT EXISTS(SELECT ‘X’ FROM #TmptAcctDetails)
BEGIN
— ROLLBACK TRAN
–SET @m_ErrMsgID =’V070′
RETURN
— GOTO Error
END
SELECT @m_HasZeroMinSlNo = MIN(SlNo), @m_HasZeroMaxSlNo = MAX(SlNo)
FROM #TmptAcctDetails WHILE @m_HasZeroMinSlNo <= @m_HasZeroMaxSlNo
BEGIN
SELECT @m_AcctID = AcctID, @AcctOpenDt=AcctOpenDt ,@AcctOpnAmt=AcctOpnAmt,@TenureDays=TenureDays,
@TenureMonths=TenureMonths,@TenureYears=TenureYears,
@HasintDepdn=IsNull(HasintDepdn,0),@HasSplRates=HasSplRates,@HasIntParams=IsNull(HasIntParams,0),
@m_HasFixedIntRt = IsNull(HasFixedIntRt,’N’),
@m_NxtDmdDt = NxtDmdDt, @m_NxtAccrDt = NxtAccrDt,
@m_NxtCaptDt = NxtCaptDt, @m_NxtIntCalcDt = NxtIntCalcDt
FROM #TmptAcctDetails
WHERE SlNo = @m_HasZeroMinSlNo INSERT INTO #TmptIntParamsDetl
SELECT DISTINCT @p_BrID, @p_PrdID,AcctID, IntRateType,IntCalcType,IntCompFreq,
CompFrqType, CalcFrqType,IntCalcFreq,ProdDaysCalcType,IntRepayFreq,IntRepayType,IntPayMode,IntCapitalizeFreq,
IntAccrualFreq,IntProdDays,IntBaseTypeID,IntDeviation,IntDemandFreq,
CapitalizeFreqType, AccrualFreqType, DemandFreqType
FROM dbo.SAM_fFetchIntParams(@p_BrID,@p_PrdID,@m_AcctID,@HasIntParams) –<< GET THE INTEREST RATE HEADER INFO. — << RMA20060525 PS Change Begins
–Delete From #TmptIRHdr
Truncate Table#TmptIRHdr
— << RMA20060525 PS Change Ends INSERT INTO #TmptIRHdr
SELECT DISTINCT @p_BrID,@p_PrdID,AcctID,IRID,CyID,EffDate,
IRType,DurSlabType,AmtSlabType,PerSlabType
FROM dbo.SAM_fFetchIRDetl(@p_BrID,@p_PrdID,@m_AcctID,@HasintDepdn,@m_HasFixedIntRt) — IF NOT EXISTS(SELECT ‘x’ FROM #TmptIRHdr
— WHERE BrID = @p_BrID AND PrdID = @p_PrdID AND AcctID = @m_AcctID) –< GET THE INTEREST RATE DETAILS INFO. — << RMA20060525 PS Change Begins
–Delete From #TmptIRDetl
Truncate Table #TmptIRDetl
— << RMA20060525 PS Change Ends INSERT INTO #TmptIRDetl
SELECT DISTINCT @p_BrID, @p_PrdID, AcctID, IRID, DurSlabID, AmtSlabID, PercSlabID,
DurSlabUpperYrs, DurSlabLowerYrs, DurSlabUpperMns, DurSlabLowerMns,
DurSlabUpperDays, DurSlabLowerDays, AmtSlabUpper,
AmtSlabLower, PercSlabUpper, PercSlabLower,
ROI,PenalROI,Penalty,ExpiryROI,PreClsROI,ForeClsROI
FROM dbo.SAM_fFetchIRDetl(@p_BrID,@p_PrdID,@m_AcctID,@HasintDepdn,@m_HasFixedIntRt) –<<COMMENTED BY EDA ON 16.01.2006 NOT NECESSARY FOR PRODUCT LEVEL ROUTINE
— –<<< IF MIN DUE IS APPLICABLE THEN CALL THE FUNCTION >>>—
— SELECT @m_IsReqMinIntDue = IsReqMinIntDue FROMSAM_tProductMst WHERE PrdID = @p_PrdID
— IF @m_IsReqMinIntDue = ‘Y’ AND @p_Purpose = ‘DEMAND’ BEGIN –<<EDA16JAN06
— SET @m_NxtDt = (SELECT CASE WHEN @p_Purpose = ‘DEMAND’ THEN @m_NxtDmdDt
— WHEN @p_Purpose = ‘ACCRUAL’ THEN @m_NxtAccrDt
— WHEN @p_Purpose = ‘CAPITALISE’ THEN @m_NxtCaptDt
— WHEN @p_Purpose = ‘ONTXN’ THEN @m_NxtIntCalcDt
— END)
—
— SET @m_CalcToDt = dbo.IRC_fGetMinIntDue(@p_PrdID,@AcctOpenDt,@m_LstIntCalcDt,@m_CalcToDt)
— END — << CALCULATE INTEREST ACCOUNT WISE
EXEC IRC_sAcctIntCalc @CalcToDt = @m_CalcToDt,@BrID = @p_BrID,
@PrdID = @p_PrdID, @AcctID = @m_AcctID,
@BusnDt = @m_BusnDt,@LoginID = @p_LoginID,
@p_Purpose = @p_Purpose SET @m_HasZeroMinSlNo = @m_HasZeroMinSlNo + 1 INSERT INTO #TmptCalcSlabFinal
(BrID,PrdID,AcctID,FromDt,ToDt,TotOS,TotPrinOD,TotIntOD,TotOthOD,DurSlabID,
AmtSlabID,PerSlabID,DurSlabType,AmtSlabType,PerSlabType,ROI,PenalROI,
PenaltyROI,IRID, Principal,Interest,Penal,Penalty,AcctCyID)
SELECT BrID,PrdID,AcctID,FromDt,ToDt,TotOS,TotPrinOD,TotIntOD,TotOthOD,DurSlabID,
AmtSlabID,PerSlabID,DurSlabType,AmtSlabType,PerSlabType,ROI,PenalROI,PenaltyROI,IRID,
Principal,Interest,Penal,Penalty,AcctCyID
FROM#TmptCalcSlab
WHERE BrID = @p_BrID AND PrdID = @p_PrdID AND AcctID = @m_AcctID — IF NOT EXISTS(SELECT ‘X’ FROM #TmptCalcSlab
— WHERE BrID = @p_BrID AND PrdID = @p_PrdID AND AcctID = @m_AcctID)
— BEGIN
— — ROLLBACK TRAN
— SET @m_ErrMsgID =’V080′
— — RETURN
— –GOTO Error
— END IF @p_Purpose =’REVERSAL’ BEGIN
–<< FETCH TXNS FOR (FIRST DAY OF THE GIVEN LSTDMDDT – LSTDMDDT)
— –<<MODIFIED BY EDA ON 20.10.2005 (FOR LOAN REVERSAL)
DECLARE @m_IntAmt UDLDGRAMT SELECT @m_IntAmt = SUM(IsNull(Interest,0)) FROM #TmptCalcSlabFinal
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDAcctID = @m_AcctID UPDATE #TmptCalcSlabFinal
SET INTEREST = @m_IntAmt – DEMND.DemAmt,
PRINCIPAL = 0
FROM #TmptCalcSlabFinal Slab,
(SELECT SUM(IsNull(Interest,0)) As IntAmt, SUM(IsNull(DemAmt,0)) As DemAmt FROM LON_tDemandHdr DH, LON_tDemandDet DD,
#TmptCalcSlabFinal Slab
WHERE DH.BrID = @p_BrID AND DH.PrdID = @p_PrdID AND DH.AcctID = @m_AcctID
AND DH.PK_ID = DD.DemHdr_FK
AND Slab.BrID = DH.BrID AND Slab.PrdID = DH.PrdID AND Slab.AcctID = DH.AcctID
AND DH.BusnDt >= Slab.FromDt AND DH.BusnDt <= Slab.ToDt AND DD.OptnTypeID = ‘2’) DEMND
WHERESlab.BrID = @p_BrID
ANDSlab.PrdID = @p_PrdID
ANDSlab.AcctID = @m_AcctID
ANDSlab.SlabNo = (Select Max(SlabNo) From #TmptCalcSlabFinal
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDAcctID = @m_AcctID) UPDATE #TmptCalcSlabFinal
SETInterest = 0,
Principal = 0
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDAcctID = @m_AcctID
ANDSlabNo NOT IN (Select Max(SlabNo) From #TmptCalcSlabFinal
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDAcctID = @m_AcctID) END
END –> END OF LOOP SELECT @m_CmpFreq = IntCompFreq, @m_IntCalcFreq = IntCalcFreq,
@m_CaptFreq = IntCapitalizeFreq, @m_AccrFreq = IntAccrualFreq,
@m_DmdFreq = IntDemandFreq, @m_CapitalizeFreqType = CapitalizeFreqType,
@m_AccrualFreqType = AccrualFreqType, @m_DemandFreqType = DemandFreqType,
@m_IntProdDays = IntProdDays
FROM#TmptIntParamsDetl
WHERE BrID = @p_BrID
ANDPrdID= @p_PrdID IF @p_Purpose = ‘DEMAND’ BEGIN
EXEC SAM_sDemand_INS @p_BrID, @p_PrdID, @m_BusnDt, @p_LoginID,@m_ValueDt –<< CALCULATE THE NEXT DEMAND DATE AND UPDATE THE BALANCE TABLE
EXEC dbo.IRC_sNxtFreqDt_UPD @p_BrID, @p_PrdID, ”,@m_DmdFreq, @m_CalcToDt,
@m_ValueDt, @p_Purpose,@m_DemandFreqType,@m_IntProdDays,
@m_AccrFreq, @m_BusnDt — –<< ONCE CAPITALISE ROUTINE COMPLETE SET THE VALUE AS EMPTY
— UPDATE LON_tAcctBaln
— SET DmdRoutExec = CASE DmdRoutExec WHEN ” THEN ‘D’ WHEN ‘D’ THEN ‘S’ Else ” End
— WHEREBrID = @p_BrID
— ANDPrdID = @p_PrdID
UPDATE LON_tAcctBaln
SET DmdRoutExec = ”
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDDmdRoutExec IN (‘D’,’S’) –<< RE-INITIALISE THE ACCRUAL AFTER DEMAND OR CAPITALISE(ADDED BY EDA ON 05.12.2005)
EXEC IRC_sResetAccrual @p_BrID, @p_PrdID,Null,@m_ValueDt,@p_Purpose
END
ELSE IF @p_Purpose = ‘ACCRUAL’ BEGIN
EXEC IRC_sGetAccruDetls @p_BrID, @p_PrdID, Null –<< CALCULATE THE NEXT ACCRUAL DATE AND UPDATE THE BALANCE TABLE
EXEC dbo.IRC_sNxtFreqDt_UPD @p_BrID, @p_PrdID, ”, @m_AccrFreq, @m_CalcToDt,
@m_ValueDt, @p_Purpose,@m_AccrualFreqType,
@m_IntProdDays, @m_AccrFreq, @m_BusnDt
END
–<<< CALL THE CAPITALISE PROCEDURE IF CALCULATION DATE IS EQUAL TO CAPITALISE DATE >>>–
ELSE IF @p_Purpose = ‘CAPITALISE’ BEGIN
EXEC SAM_SCAPITALISE_INS @p_BrID, @p_PrdID, NULL, @m_BusnDt, @p_LoginID, @m_ValueDt, Null, Null– , @p_Purpose –<< CALCULATE THE NEXT CAPITALISE DATE AND UPDATE THE BALANCE TABLE
EXEC dbo.IRC_sNxtFreqDt_UPD @p_BrID, @p_PrdID, ”, @m_CaptFreq, @m_CalcToDt,
@m_ValueDt, @p_Purpose,@m_CapitalizeFreqType,
@m_IntProdDays, @m_AccrFreq, @m_BusnDt –<< ONCE CAPITALISE ROUTINE COMPLETE SET THE VALUE AS EMPTY
— UPDATE LON_tAcctBaln
— SET DmdRoutExec = CASE DmdRoutExec WHEN ” THEN ‘D’ WHEN ‘D’ THEN ‘S’ Else ” End
— WHEREBrID = @p_BrID
— ANDPrdID = @p_PrdID
UPDATE LON_tAcctBaln
SET DmdRoutExec = ”
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDDmdRoutExec IN (‘D’,’S’) –<< RE-INITIALISE THE ACCRUAL AFTER DEMAND OR CAPITALISE(ADDED BY EDA ON 05.12.2005)
EXEC IRC_sResetAccrual @p_BrID, @p_PrdID,Null,@m_ValueDt,@p_Purpose
RETURN
END
ELSE IF @p_Purpose = ‘REPORTS’ Or @p_Purpose = ‘ONSCR’ BEGIN
SELECT BrID, PrdID, AcctID, dbo.SAM_fCurrRndFormat(SUM(IsNull(Interest,0))) As INTEREST
FROM #TmptCalcSlabFinal
GROUP BY BrID, PrdID, AcctID
Order by BrID,PrdID,AcctID
END
ELSE IF @p_Purpose = ‘REVERSAL’ BEGIN
–<< FOR REVERSAL CASE DEMAND HAS TO BE DONE, BEFORE REVERSAL
EXEC SAM_sDemand_INS @p_BrID, @p_PrdID, @m_BusnDt, @p_LoginID,@m_ValueDt
EXEC SAM_sAutoReversal @p_BrID, @p_PrdID,Null,@m_BusnDt, @p_LoginID, @m_ValueDt –<< ONCE REVERSAL ROUTINE COMPLETE SET THE VALUE AS EMPTY
UPDATE LON_tAcctBaln
SET DmdRoutExec = ”
WHEREBrID = @p_BrID
ANDPrdID = @p_PrdID
ANDDmdRoutExec IN (‘D’,’S’)
END
ELSE IF @p_Purpose = ‘REPCALC’ BEGIN –<<EDA06FEB06
SELECT BrID, PrdID, AcctID, TotOS, Principal,
dbo.SAM_fCurrRndFormat(IsNull(Interest,0)) As INTEREST,
dbo.SAM_fCurrRndFormat(IsNull(Penalty,0)) As Penalty,
dbo.SAM_fCurrRndFormat(IsNull(Penal,0)) As Penal
FROM #TmptCalcSlabFinal
Order by BrID,PrdID,AcctID
END Error:
IF @@Error <> 0 Or IsNull(@m_ErrMsgID,”) <> ” BEGIN
–<< For EOT Error Log Purpose >>–
SET @m_KeyValues = @p_BrID + ‘|’ + @p_PrdID
EXEC SAM_sInsertTaskStatus @p_BatchType =’GRP’,@p_KeyFieldNames = ‘BrID|PrdID’,
@p_KeyFieldValues = @m_KeyValues, @p_Count = @m_AcctCnt,
@p_NthRecord = @m_HasZeroMinSlNo,@p_DateTime = @m_SetCurDtTime,
@p_Status=’FAILED’, @p_UserID = @p_LoginID
END
— ELSE BEGIN
— COMMIT TRAN –<< COMMIT THE COMPLETED TRANSACTION
— END
SET NOCOUNT OFF
END ->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
alter PROCEDURE dbo.IRC_sAcctIntCalc
(
@CalcToDt UDDT,
@BrID UDVC16,
@PrdID UDVC16,
@AcctID UDVC16,
@BusnDt UDDT,
@LoginID UDVC25,
@p_Purpose UDVC25,
@p_OnTxnAmt UDNU174 = Null,
@p_DmdFlag UDCH1 = Null
)
As
BEGIN
SET NOCOUNT ON
/**************************************************************************
‘SP NAME :IRC_sAcctIntCalc
‘Parameter :BrID,PrdID,AcctID,Calc.Date,Busn. Date,Value Date,Login Date,
Purpose, OnTxnAmt,Demand Flag
‘Output Parameter :
‘Created By :EDA
‘Created Date :15-07-2005
‘Description :This SP will execute and calculate the interest based on
purpose given.
‘Modified By :EDA (EDA1204200601)
‘Modified Date :12.04.2006 13:50(For Handling Advance collection during
expiry of accounts)
‘**************************************************************************/
–<< DELETE THE PREVIOUS RECORDS >>–
–DELETE FROM #TMPTCALCSLAB
Truncate table #TMPTCALCSLAB –<< ACCT. DETAILS VARIABLES >>–
DECLARE @AcctOpenDt UDDT,@AcctOpnAmt UDLDGRAMT,
@TenureDays UDINT, @TenureMonths UDINT,
@TenureYears UDINT,@HasFixedIntRt UDCH1,
@m_ErrMsgID UDVC10,@m_RtnVal UDINT,
@m_MatAmtUDLDGRAMT,@m_RtnVal1UDINT,
@m_RtnVal2UDINT,@m_RtnVal3UDINT –<< ACCT. PARAMAETERS VARIABLES >>–
[email protected] UDVC25, @IntCalcType UDVC10,
@IntCompFreq UDVC10,@IntCalcFreq UDVC10,
@ProdDaysCalcType UDVC25,@IntRepayType UDVC10,
@IntProdDays UDINT,@CompFreqType UDVC25,
@CalcFreqType UDVC25,@m_CalcFrq UDSINT,
@m_CalcFreqUDVC10,@DmdFreqUDVC5,
@DmdFreqTypeUDVC5,@m_ARCStatUDCH1 — << ACCT. BALANCE VARIABLES >>–
DECLARE @AvailedAmt UDLDGRAMT,@DisbAmt UDLDGRAMT,
@LstDemDt UDDT,@LstCaptDt UDDT,
@LstAccrDt UDDT,@LstCompDt UDDT,
@LstIntCalcDt UDDT,@m_IsReqMonthBaln UDCH1,
@m_LstCalcDt UDDT,@m_PrinDue UDLDGRAMT,
@m_NxtCalcDt UDDT,@m_GracePerdReqd UDCH1,
@m_ApplyPenal UDCH1,@m_ApplyPenalty UDCH1,
@m_ApplyInt UDCH1,@m_ODGracePerd UDSINT,
@m_SRID UDBINT,@m_PreClsDev UDLDGRAMT,
@m_IntDev UDVC5,@m_IntDevPerc UDLDGRAMT,
@m_ExpiryDt UDDT,@m_AcctCyIDUDVC5,
@m_InstallAmt UDNU174,@m_RoutExec UDCH1,
@m_RepayFreqUDVC10,@m_AcctValueDtUDDT DECLARE @m_IntDue UDLDGRAMT, @m_ActPrinDue UDLDGRAMT,
@m_ActIntDue UDLDGRAMT,@m_EMIAmt UDLDGRAMT,
@m_Div UDINT,@m_tmpBusnsDt UDDT,
@p_TmpOnTxnAmt UDLDGRAMT,@m_PrdType UDVC10,
@m_TotOS UDLDGRAMT,@m_TotPrinOD UDLDGRAMT DECLARE @m_BalnPrinDue UDLDGRAMT, @m_BalnIntDue UDLDGRAMT,
@m_BalnPenaltyDue UDLDGRAMT, @m_BalnPenalIntDue UDLDGRAMT,
@m_AccrPrinDue UDLDGRAMT, @m_AccrIntDue UDLDGRAMT,
@m_AccrPenaltyDue UDLDGRAMT, @m_AccrPenalIntDue UDLDGRAMT,
@m_IntContinue UDCH1,@m_PrdClosedDt UDDT,
@m_ForeClsDt UDDT,@m_ForeClStat UDCH1,
@NxtAccrDt UDDT, @NxtDmdDtUDDT,
@NxtCaptDt UDDT,@m_ODIntAllowPerd UDINT,
@m_LstPenaltyCalcDt UDDT,@m_TotPrinAdvColn UDLDGRAMT — << FETCH ACCT. PARAMETERS >> —
SELECT @AcctOpenDt=AcctOpenDt ,@AcctOpnAmt=AcctOpnAmt,
@TenureDays=IsNull(TenureDays,0),
@TenureMonths=TenureMonths,@TenureYears=IsNull(TenureYears,0),
@HasFixedIntRt = IsNull(HasFixedIntRt,’N’), @m_SRID = PrdSplRtID,
@m_IntDev = IntDev, @m_IntDevPerc=IntDevPerc,
@m_ExpiryDt = ExpiryDt, @m_AcctCyID = AcctCyID, @m_InstallAmt = InstallAmt,
@m_RoutExec = IsNull(RoutExec,”), @m_AcctValueDt = ValueDt,
@m_MatAmt = MatAmt, @m_IntContinue = IntContinue, @m_ForeClsDt = ForeClsDt,
@LstDemDt = LstDmdDt,@LstCaptDt = LstCaptDt, @LstAccrDt = LstAccrDt,
@LstCompDt = LstCompDt, @LstIntCalcDt= LstIntCalcDt,
@NxtAccrDt = NxtAccrDt, @NxtDmdDt = NxtDmdDt, @NxtCaptDt = NxtCaptDt,
@m_TotOS = TotOS, @m_ARCStat = ARCStat, @m_LstPenaltyCalcDt = IsNull(LstPenaltyCalcDt,LstIntCalcDt)
FROM #TmptAcctDetails
WHERE BrID = @BrID
AND PrdID = @PrdID
AND AcctID = @AcctID SELECT @m_IsReqMonthBaln = IsReqMonthBaln, @m_ODGracePerd = ODGracePeriod,
@m_PreClsDev = IsNull(PreClsDev,0), @m_PrdType = PrdType,
@m_PrdClosedDt = PrdClosedDt, @m_ForeClStat = ForeClStat,
@m_ODIntAllowPerd = ODIntAllowPerd –<<(EDA01503200601)
FROM#TmptPrdParams –<< FETCH ACCT. PARAMETERS INTO VARIABLES >>–
SELECT @IntRateType=IntRateType,@IntCalcType=IntCalcType,
@IntCompFreq=IntCompFreq,@IntCalcFreq=IntCalcFreq,
@ProdDaysCalcType=ProdDaysCalcType,@IntRepayType=IntRepayType,
@IntProdDays=IntProdDays,@CompFreqType = CompFrqType,
@CalcFreqType = CalcFrqType,@DmdFreq = IntDemandFreq,
@DmdFreqType = DemandFreqType, @m_RepayFreq = IntRepayFreq
FROM #TmptIntParamsDetl
WHERE PrdID = @PrdID
ANDBrID = @BrID
AND(AcctID = @AcctID Or AcctID Is Null) –<<ADDED BY EDA ON 08.FEB.2006(FOR PRECLS. OF RD FOR IRREGULAR MATURED A/C’S)
— IF @IntRepayType = ‘RDTYPE’ AND @CalcToDt > @m_ExpiryDt AND (@p_Purpose = ‘PRECLS’ Or @p_Purpose = ‘PRECLSUPD’)
IF @IntRepayType = ‘RDTYPE’ AND (@CalcToDt >= dbo.IRC_fCalcToDate(@m_ExpiryDt,0,0,-1)) AND (@p_Purpose = ‘PRECLS’ Or @p_Purpose = ‘PRECLSUPD’) –<<EDA3003200601
BEGIN
SET @m_PreClsDev = 0
END –<<ADDED BY EDA ON 11.11.2005(FOR EFIFR PRECLOSURE)
SELECT @p_Purpose = CASE WHEN @P_PURPOSE =’ONCLOSURE’ AND @IntRepayType=’EFIFR’ THEN ‘PRECLS’
WHEN @P_PURPOSE =’ONCLOSUREUPD’ AND @IntRepayType=’EFIFR’ THEN ‘PRECLSUPD’
ELSE @p_Purpose END IF @p_Purpose <> ‘ONSCR’ AND @p_Purpose <> ‘REPORTS’ AND @P_PURPOSE <> ‘ONACCRUAL’ BEGIN
–<< FETCH ACCT. BALANCE DETAILS >>–
SELECT @m_LstCalcDt = Case WHEN @p_Purpose = ‘DEMAND’ THEN @LstDemDt
WHEN @p_Purpose = ‘CAPITALISE’ THEN @LstCaptDt
–<<ADDED BY EDA ON 03-09-2004 17:10 FOR ACCRUAL ADJUST.
WHEN @p_Purpose = ‘ACCRUAL’ THEN @LstAccrDt –LstIntCalcDt
WHEN @p_Purpose = ‘ONTXN’ THEN @LstIntCalcDt
WHEN @p_Purpose = ‘PENALCALC’ THEN @LstIntCalcDt
WHEN @p_Purpose = ‘PENALCALCUPD’ THEN @LstIntCalcDt
WHEN (@p_Purpose = ‘PRECLS’ Or @p_Purpose = ‘PRECLSUPD’) THEN @m_AcctValueDt
–WHEN THEN @m_AcctValueDt
WHEN (@p_Purpose = ‘RDPENALTY’ Or @p_Purpose = ‘RDPENALTYUPD’) THEN @m_AcctValueDt –RD_SRN
WHEN @p_Purpose = ‘REPCALC’ Or @p_Purpose = ‘REPCALCEXPY’ THEN
CASE WHEN (@CalcToDt > @LstAccrDt) THEN @NxtAccrDt –<<EDA06FEB06
WHEN (@CalcToDt < @LstAccrDt) THEN CASE WHEN @DmdFreq <> ‘NA’ THEN dbo.IRC_fGetLstDmdDt(@BrID, @PrdID, @AcctID, @CalcToDt, @m_AcctValueDt, @m_PrdType) –<<EDA06FEB06
ELSE dbo.IRC_fGetLstTxnDt(@BrID, @PrdID, @AcctID, @CalcToDt, @m_PrdType) END END –<<EDA06FEB06 WHEN (@p_Purpose = ‘ONCLOSURE’ Or @p_Purpose = ‘ONCLOSUREUPD’) AND @IntRepayType =’DDTYPE’ THEN @LstAccrDt –<<EDA052006
WHEN (@p_Purpose = ‘CLSWODINT’ Or @p_Purpose = ‘CLSWODINTUPD’) THEN dbo.IRC_fCalcToDate(@m_ExpiryDt,0,0,1) [email protected]_ExpiryDt –<<EDA1603200601
ELSE @LstIntCalcDt
End,
@m_NxtCalcDt =
Case WHEN @p_Purpose = ‘DEMAND’ THEN @NxtDmdDt
WHEN @p_Purpose = ‘CAPITALISE’ THEN @NxtCaptDt
–WHEN @p_Purpose = ‘ACCRUAL’ THEN NxtAccrDt
WHEN @p_Purpose = ‘ACCRUAL’ AND @m_PrdType =’LON’ THEN @NxtAccrDt –<<EDA18JAN06
WHEN @p_Purpose = ‘ACCRUAL’ AND @m_PrdType =’DEP’ AND @IntRepayType <> ‘MINBALN’ THEN CASE WHEN @NxtAccrDt < @m_ExpiryDt THEN @NxtAccrDt Else @m_ExpiryDt End –<<EDA18JAN06
WHEN @p_Purpose = ‘ACCRUAL’ AND @m_PrdType =’DEP’ AND @IntRepayType =’MINBALN’ THEN @NxtAccrDt
WHEN @p_Purpose = ‘ONTXN’ AND @p_DmdFlag = ‘Y’ THEN @NxtDmdDt
— WHEN @p_Purpose = ‘PENALCALC’ AND @p_DmdFlag = ‘Y’ THEN NxtDmdDt
WHEN (@p_Purpose = ‘PENALCALCUPD’ Or @p_Purpose = ‘PENALCALC’) THEN @CalcToDt –<<EDA2503200601
–WHEN @p_Purpose = ‘PRECLS’ THEN @CalcToDt
–WHEN @p_Purpose = ‘PRECLS’ AND @IntRepayType <> ‘RDTYPE’ THEN @CalcToDt
WHEN @p_Purpose = ‘PRECLS’ AND @IntRepayType <> ‘RDTYPE’ AND @IntRepayType <> ‘MINBALN’ THEN @CalcToDt
WHEN @p_Purpose = ‘PRECLS’ AND @IntRepayType = ‘RDTYPE’ THEN CASE WHEN @CalcToDt > @m_ExpiryDt THEN @m_ExpiryDt ELSE @CalcToDt END –AND (@AcctOpnAmt <> TotOS) AND (@CalcToDt > @m_ExpiryDt) THEN @m_ExpiryDt
WHEN (@p_Purpose = ‘PRECLS’ Or @p_Purpose = ‘PRECLSUPD’) AND @IntRepayType = ‘MINBALN’ THEN @LstAccrDt
–WHEN @p_Purpose = ‘PRECLSUPD’ THEN @CalcToDt
WHEN @p_Purpose = ‘PRECLSUPD’ AND @IntRepayType <> ‘RDTYPE’ AND @IntRepayType <> ‘MINBALN’ THEN @CalcToDt
WHEN @p_Purpose = ‘PRECLSUPD’ AND @IntRepayType = ‘RDTYPE’ THEN CASE WHEN @CalcToDt > @m_ExpiryDt THEN @m_ExpiryDt ELSE @CalcToDt END –AND (@AcctOpnAmt <> TotOS) AND (@CalcToDt > @m_ExpiryDt) THEN @m_ExpiryDt
WHEN @p_Purpose = ‘RDPENALTY’ Or @p_Purpose = ‘RDPENALTYUPD’ Then
Case When @m_TotOS = @AcctOpnAmt THEN
@m_ExpiryDt
ELSE (SELECT MAX(BusnDt) FROM DEP_tInstLdgrHdr
WHERE [email protected] And [email protected] And AcctID [email protected] And Approve=’Y’) End
WHEN (@p_Purpose = ‘ONCLOSURE’ Or @p_Purpose = ‘ONCLOSUREUPD’) AND @IntRepayType =’MD’ THEN dbo.IRC_fCalcToDate(@m_ExpiryDt,0,0,-1)–<<@m_ExpiryDt (EDA01503200602)
–WHEN (@p_Purpose = ‘ONCLOSURE’ Or @p_Purpose = ‘ONCLOSUREUPD’) AND (@IntRepayType = ‘RDTYPE’ Or @IntRepayType = ‘DDTYPE’) THEN dbo.IRC_fCalcToDate(@m_ExpiryDt,0,0,-1) [email protected]_ExpiryDt –<<EDA052006
WHEN (@p_Purpose = ‘ONCLOSURE’ Or @p_Purpose = ‘ONCLOSUREUPD’) AND @IntRepayType = ‘RDTYPE’ THEN dbo.IRC_fCalcToDate(@m_ExpiryDt,0,0,-1) [email protected]_ExpiryDt –<<EDA052006
WHEN (@p_Purpose = ‘ONCLOSURE’ Or @p_Purpose = ‘ONCLOSUREUPD’) AND @IntRepayType = ‘DDTYPE’ THEN @LstAccrDt –<<EDA2003200601
WHEN (@p_Purpose = ‘ONCLOSURE’ Or @p_Purpose = ‘ONCLOSUREUPD’) AND @IntRepayType =’MINBALN’ THEN @LstAccrDt –>>DSRN
WHEN (@p_Purpose = ‘ONCLOSURE’ Or @p_Purpose = ‘ONCLOSUREUPD’) AND @m_PrdType = ‘LON’ THEN @CalcToDt
WHEN @p_Purpose = ‘REPCALC’ Or @p_Purpose = ‘REPCALCEXPY’ THEN @CalcToDt –<<EDA0402
WHEN (@p_Purpose = ‘CLSWODINT’ Or @p_Purpose = ‘CLSWODINTUPD’) THEN @CalcToDt –<<EDA1403200602
ELSE @LstIntCalcDt [email protected]_InstallAmt
END [email protected]_CalcFrq = dbo.IRC_fGetFreqVal(@IntCalcFreq,@IntProdDays) IF (@p_Purpose = ‘RDPENALTY’ Or @p_Purpose = ‘RDPENALTYUPD’ Or @p_Purpose = ‘REPCALC’ Or @p_Purpose = ‘REPCALCEXPY’
Or @p_Purpose = ‘PRECLS’ Or @p_Purpose = ‘PRECLSUPD’ Or @p_Purpose = ‘ONCLOSURE’ Or @p_Purpose = ‘ONCLOSUREUPD’) SET @CalcToDt = @m_NxtCalcDt –<<EDA0402 IF (@p_Purpose = ‘REPCALC’ Or @p_Purpose = ‘REPCALCEXPY’) AND (@CalcToDt < @LstAccrDt) BEGIN
SET @m_LstCalcDt = CASE WHEN @CalcToDt = @m_LstCalcDt THEN DATEADD(Day,1,@m_LstCalcDt) ELSE @m_LstCalcDt END
END IF (@m_PrdClosedDt IS NOT Null AND @m_IntContinue = ‘N’ AND @m_ForeClStat = ‘Y’) BEGIN –<<(EDA01603200602)
–SET @m_NxtCalcDt = @m_PrdClosedDt –<<EDA1703200601
SET @m_NxtCalcDt = CASE WHEN @m_ExpiryDt > @m_PrdClosedDt THEN
@m_PrdClosedDt
ELSE dbo.IRC_fCalcToDate(@m_ExpiryDt,0,0,-1) END –<<EDA1703200601 SET @CalcToDt = @m_NxtCalcDt
END IF @p_Purpose =’REVERSAL’ BEGIN
–<< FETCH TXNS FOR (FIRST DAY OF THE GIVEN LSTDMDDT – LSTDMDDT)
SET @CalcToDt = @m_LstCalcDt
SET @m_LstCalcDt = dbo.IRC_fCalcToDateWTHFrqTyp_REV(@m_LstCalcDt,@DmdFreq,@DmdFreqType,@IntProdDays)
END
ELSE IF @p_Purpose =’ONCLOSURE’ AND (@m_RoutExec = ‘D’ Or @m_RoutExec =’S’) BEGIN
–<< FETCH TXNS FOR (FIRST DAY OF THE GIVEN LSTDMDDT – LSTDMDDT)
SET @CalcToDt = @CalcToDt
SET @m_LstCalcDt = dbo.IRC_fCalcToDateWTHFrqTyp_REV(@m_LstCalcDt,@DmdFreq,@DmdFreqType,@IntProdDays)
END
ELSE IF @p_Purpose =’ONCLOSURE’ AND @IntRepayType =’MINBALN’ BEGIN
SET @CalcToDt = @LstAccrDt
END
–<<ADDED BY EDA ON 07.12.2005 APPLICABLE ONLY DEPOSIT TERM DEPOSITS
ELSE IF @m_RepayFreq =’ONE’ AND (@p_Purpose =’DEMAND’ or @p_Purpose =’ONCLOSURE’ or @P_PURPOSE =’ONCLOSUREUPD’) AND
(@IntRepayType =’MD’ Or @IntRepayType =’RDTYPE’) AND (@m_ForeClStat <> ‘Y’) BEGIN SET @CalcToDt = @m_NxtCalcDt — –<< IF FORE CLOSED ACCT. MATURED BEFORE PRODUCT CLOSING DATE
— IF (@m_ForeClStat = ‘Y’ AND @m_IntContinue = ‘N’) AND (@m_ExpiryDt > @m_PrdClosedDt) BEGIN –<<EDA2103200601
— RETURN
— END INSERT INTO #TmptCalcSlab (BrID,PrdID,AcctID,FromDt,ToDt,TotOS,TotPrinOD,
TotIntOD,TotOthOD,Interest,Principal)
SELECT @BrID, @PrdID, @AcctID, @m_LstCalcDt, @CalcToDt, @AcctOpnAmt, 0,
0,0,(@m_MatAmt – @AcctOpnAmt),0 RETURN
END
ELSE IF @m_RepayFreq =’ONE’ AND (@p_Purpose =’DEMAND’ or @p_Purpose =’ONCLOSURE’ or @P_PURPOSE =’ONCLOSUREUPD’ Or
@p_Purpose =’PRECLS’ or @P_PURPOSE =’PRECLSUPD’) AND(@IntRepayType =’MD’)
AND (@m_ForeClStat = ‘Y’) AND (@m_ExpiryDt <= @m_PrdClosedDt) BEGIN –<<EDA2703200601 INSERT INTO #TmptCalcSlab (BrID,PrdID,AcctID,FromDt,ToDt,TotOS,TotPrinOD,
TotIntOD,TotOthOD,Interest,Principal)
SELECT @BrID, @PrdID, @AcctID, @m_LstCalcDt, @CalcToDt, @AcctOpnAmt, 0,
0,0,(@m_MatAmt – @AcctOpnAmt),0
RETURN
END
ELSE IF @m_RepayFreq =’ONE’ AND (@p_Purpose =’ONCLOSURE’ or @P_PURPOSE =’ONCLOSUREUPD’) AND @IntRepayType =’DDTYPE’ BEGIN –<<EDA052006
SET @CalcToDt = @m_NxtCalcDt
END IF @p_Purpose =’REPCALC’ Or @p_Purpose = ‘REPCALCEXPY’ BEGIN –<<EDA06FEB06
–<< FETCH BALANCE DETAILS
IF @CalcToDt = @LstAccrDt BEGIN
–<< FETCH BALANCE DETAILS –<<EDA06FEB06
SELECT @m_BalnPrinDue = TotPrinDue, @m_BalnIntDue = TotIntDue,
@m_BalnPenaltyDue = TotPenaltyDue,@m_BalnPenalIntDue = TotPenalIntDue,
@m_TotOS = TotOS
FROM dbo.IRC_fFetchBalnDetl(@BrID, @PrdID, @AcctID) –<< FETCH ACCRUAL DETAILS –<<EDA06FEB06
SELECT @m_AccrPrinDue = TotPrinDue, @m_AccrIntDue = TotIntDue,
@m_AccrPenaltyDue = TotPenaltyDue,@m_AccrPenalIntDue = TotPenalIntDue
FROM dbo.IRC_fFetchAccruDetl(@BrID, @PrdID, @AcctID) INSERT INTO #TmptCalcSlab (BrID,PrdID,AcctID,FromDt,ToDt,TotOS,
Principal, Interest, Penalty, Penal)
SELECT @BrID, @PrdID, @AcctID, @m_LstCalcDt, @CalcToDt, @m_TotOS,
@m_BalnPrinDue, (@m_BalnIntDue + @m_AccrIntDue),
(@m_BalnPenaltyDue + @m_AccrPenaltyDue), (@m_BalnPenalIntDue + @m_AccrPenalIntDue)
RETURN
END
END IF (@AcctOpenDt <> @m_LstCalcDt) and (@IntRepayType =’MINBALN’)
BEGIN
set @m_LstCalcDt = DATEADD(DAY,1,@m_LstCalcDt)
END –<<ADDED BY EDA ON 18.01.2006 (TO AVOID CALCULATING ACCR. INT AFTER MATURITY)
–SELECT @m_TotOS = TotOS FROM dbo.IRC_fFetchBalnDetl(@BrID,@PrdID,@AcctID)
—-Condition @IntRepayType <>’MINBALN’ added by MSS for SB Accrue intcalc 27-01-2006
IF (@p_Purpose = ‘ACCRUAL’ AND @m_PrdType =’DEP’ AND @IntRepayType <>’MINBALN’) BEGIN
IF @CalcToDt > @m_ExpiryDt AND (@AcctOpnAmt = @m_TotOS) AND @m_RepayFreq = ‘ONE’ BEGIN INSERT INTO #TmptCalcSlab (BrID,PrdID,AcctID,FromDt,ToDt,TotOS,TotPrinOD,
TotIntOD,TotOthOD,Interest,Principal)
SELECT @BrID, @PrdID, @AcctID, @m_LstCalcDt, @m_NxtCalcDt, @AcctOpnAmt, 0,
0,0,(@m_MatAmt – @AcctOpnAmt),0
RETURN
END
ELSE IF (@CalcToDt > @m_ExpiryDt AND @IntRepayType =’DDTYPE’) BEGIN –<<EDA27JAN06
RETURN
END
SET @CalcToDt = @m_NxtCalcDt
END IF @m_LstCalcDt > @CalcToDt AND @p_Purpose <> ‘REPCALC’ AND @p_Purpose <> ‘REPCALCEXPY’ BEGIN
RETURN
END –<<MODIFIED BY EDA ON 03.04.2006 (FOR JL ACCRUAL INT.) –<<EDA0304200601
IF (@p_Purpose = ‘DEMAND’ Or @p_Purpose =’ACCRUAL’ Or @p_Purpose =’CAPITALISE’) AND (@m_AcctValueDt <> @m_LstCalcDt) AND (@IntRepayType <>’MINBALN’)
BEGIN
–<< FOR ONE DAY DEVIATION
SET @m_LstCalcDt = DATEADD(DAY,1,@m_LstCalcDt)
SET @LstCompDt = DATEADD(DAY,1,@LstCompDt)
SET @m_ExpiryDt = CASE WHEN @IntRepayType =’MD’ Or @IntRepayType =’RDTYPE’ THEN DATEADD(DAY,1,@m_ExpiryDt) ELSE @m_ExpiryDt END
END
–ELSE IF (@p_Purpose = ‘ONTXN’ Or @p_Purpose = ‘ONTXNUPD’ Or @p_Purpose =’ONCLOSURE’ Or @p_Purpose =’ONCLOSUREUPD’) AND (@AcctOpenDt <> @m_LstCalcDt) AND (@LstIntCalcDt = @LstCaptDt Or @LstIntCalcDt = @LstDemDt)
ELSE IF (@p_Purpose = ‘ONTXN’ Or @p_Purpose = ‘ONTXNUPD’ Or @p_Purpose =’ONCLOSURE’ Or @p_Purpose =’ONCLOSUREUPD’ Or @p_Purpose =’PENALCALC’ Or @p_Purpose =’PENALCALCUPD’) AND (@m_AcctValueDt <> @m_LstCalcDt) –<<EDA0603200601
AND (@LstIntCalcDt = @LstCaptDt Or @LstIntCalcDt = @LstDemDt) AND @IntRepayType <> ‘MINBALN’
BEGIN
–<< FOR ONE DAY DEVIATION
SET @m_LstCalcDt = DATEADD(DAY,1,@m_LstCalcDt)
SET @LstCompDt = DATEADD(DAY,1,@LstCompDt)
SET @m_ExpiryDt = CASE WHEN @IntRepayType =’MD’ Or @IntRepayType =’RDTYPE’ THEN DATEADD(DAY,1,@m_ExpiryDt) ELSE @m_ExpiryDt END
END –<< FETCH TXNS FOR GIVEN DATE AND INSERT INTO SLAB TABLE >>–
INSERT INTO #TmptCalcSlab (BrID,PrdID,AcctID,FromDt,ToDt,TotOS,TotPrinOD,
TotIntOD,TotOthOD)
SELECT DISTINCT BrID,PrdID,AcctID,BusnsDt,BusnsDt,TotOS,TotPrinOD,TotIntOD,TotOthOD
FROM dbo.IRC_fFetchTxns_V2(@BrID,@PrdID,@AcctID,@m_LstCalcDt,@CalcToDt,
@m_CalcFrq,@CalcFreqType,@IntRepayType,@m_PrdType) –IF (@p_Purpose = ‘DEMAND’ Or @p_Purpose =’ACCRUAL’ Or @p_Purpose =’CAPITALISE’) AND (@AcctOpenDt <> @m_LstCalcDt)
—-Condition @IntRepayType <>’MINBALN’ added by MSS for SB Accrue intcalc 27-01-2006
— IF (@p_Purpose = ‘DEMAND’ Or @p_Purpose =’ACCRUAL’ Or @p_Purpose =’CAPITALISE’) AND (@m_AcctValueDt <> @m_LstCalcDt) AND (@IntRepayType <>’MINBALN’)
— BEGIN
— –<< FOR ONE DAY DEVIATION
— UPDATE #TmptCalcSlab
— SET FromDt = DATEADD(DAY,1,FromDt)
— WHEREBrID = @BrID
— ANDPrdID = @PrdID
— ANDAcctID = @AcctID
— ANDFromDt = @m_LstCalcDt
—
— SET @LstCompDt = DATEADD(DAY,1,@LstCompDt)
— SET @m_ExpiryDt = CASE WHEN @IntRepayType =’MD’ Or @IntRepayType =’RDTYPE’ THEN DATEADD(DAY,1,@m_ExpiryDt) ELSE @m_ExpiryDt END
— END
— –ELSE IF (@p_Purpose = ‘ONTXN’ Or @p_Purpose = ‘ONTXNUPD’ Or @p_Purpose =’ONCLOSURE’ Or @p_Purpose =’ONCLOSUREUPD’) AND (@AcctOpenDt <> @m_LstCalcDt) AND (@LstIntCalcDt = @LstCaptDt Or @LstIntCalcDt = @LstDemDt)
— ELSE IF (@p_Purpose = ‘ONTXN’ Or @p_Purpose = ‘ONTXNUPD’ Or @p_Purpose =’ONCLOSURE’ Or @p_Purpose =’ONCLOSUREUPD’ Or @p_Purpose =’PENALCALC’ Or @p_Purpose =’PENALCALCUPD’) AND (@m_AcctValueDt <> @m_LstCalcDt) –<<EDA0603200601
— AND (@LstIntCalcDt = @LstCaptDt Or @LstIntCalcDt = @LstDemDt) AND @IntRepayType <> ‘MINBALN’
— BEGIN
— –<< FOR ONE DAY DEVIATION
— UPDATE #TmptCalcSlab
— SET FromDt = DATEADD(DAY,1,FromDt)
— WHEREBrID = @BrID
— ANDPrdID = @PrdID
— ANDAcctID = @AcctID
— ANDFromDt = @m_LstCalcDt
—
— SET @LstCompDt = DATEADD(DAY,1,@LstCompDt)
— SET @m_ExpiryDt = CASE WHEN @IntRepayType =’MD’ Or @IntRepayType =’RDTYPE’ THEN DATEADD(DAY,1,@m_ExpiryDt) ELSE @m_ExpiryDt END
— END –<<COMMENTED BY EDA & SRN ON 30.09.2005(FOR RAISING CURRENT DEMAND)
–IF @p_DmdFlag = ‘Y’ AND (@p_Purpose = ‘ONTXN’ Or @p_Purpose = ‘ONTXNUPD’ Or @p_Purpose = ‘PENALCALC’) BEGIN
IF @p_DmdFlag = ‘Y’ BEGIN
INSERT INTO #TmptCalcSlab (BrID,PrdID,AcctID,FromDt,ToDt,TotOS,TotPrinOD,
TotIntOD,TotOthOD,DmdFlag)
SELECTTop 1 BrID, PrdID, AcctID, @CalcToDt, @m_NxtCalcDt, TotOS, TotPrinOD,
TotIntOD,TotOthOD,@p_DmdFlag
FROM #TmptCalcSlab
ORDER BY FROMDT DESC
SET @CalcToDt = @m_NxtCalcDt
END
END
ELSE BEGIN
IF (@P_PURPOSE = ‘REPORTS’ Or @P_PURPOSE = ‘ONSCR’) BEGIN
SELECT @AcctOpenDt = CASE WHEN @P_PURPOSE =’ONSCR’ THEN @AcctOpenDt
WHEN @P_PURPOSE =’REPORTS’ THEN @LstIntCalcDt –(SELECT @LstIntCalcDt FROM dbo.IRC_fFetchBalnDetl(@BrID,@PrdID,@AcctID))
ELSE @AcctOpenDt END –<<ADDED BY EDA ON 18.01.2006(TO AVOID CALCULATING DEP. ACCR. INT AFTER MATURITY)
SET @CalcToDt = CASE WHEN (@P_PURPOSE =’REPORTS’ AND @m_PrdType =’DEP’ AND @CalcToDt > @m_ExpiryDt)
THEN @m_ExpiryDt ELSE @CalcToDt END INSERT INTO #TmptCalcSlab (BrID,PrdID,AcctID,FromDt,ToDt,TotOS)
VALUES(@BrID, @PrdID,@AcctID,@AcctOpenDt,@AcctOpenDt,@AcctOpnAmt)
END
–<<ADDED BY EDA ON 09.01.2006(FOR LOAN ON DEPOSIT CASES)
ELSE IF (@P_PURPOSE = ‘ONACCRUAL’ AND @m_RepayFreq =’ONE’) BEGIN
INSERT INTO #TmptCalcSlab (BrID,PrdID,AcctID,FromDt,ToDt,Interest)
SELECT @BrID,@PrdID,@AcctID,@AcctOpenDt,@CalcToDt,dbo.IRC_fGetAcctAccrual(@BrID,@PrdID,@AcctID)
RETURN
END
–<<ADDED BY EDA ON 09.01.2006(FOR LOAN ON DEPOSIT CASES)
ELSE IF (@P_PURPOSE = ‘ONACCRUAL’ AND @m_RepayFreq <> ‘ONE’) BEGIN
INSERT INTO #TmptCalcSlab (BrID,PrdID,AcctID,FromDt,ToDt,Interest)
— SELECT @BrID,@PrdID,@AcctID,@AcctOpenDt,@CalcToDt,dbo.IRC_fGetAcctAccrual(@BrID,@PrdID,@AcctID)
SELECT @BrID,@PrdID,@AcctID,@AcctOpenDt,@CalcToDt,0 As Interest
RETURN
END
END IF NOT EXISTS(SELECT ‘x’ FROM #TmptCalcSlab)
BEGIN
— Raiserror(‘ACCOUNT TRANSACTION DETAILS NOT DEFINED!’,16,1)
— SET @m_ErrMsgID = ‘V070’
— SELECT @m_ErrMsgID As ErrMsgID
RETURN 0
END –<<< SPLIT THE TRANSACTION DETAILS >>>–
EXEC IRC_SplitTXN @BrID, @PrdID, @AcctID, @CalcToDt, @p_DmdFlag, @m_NxtCalcDt –<< SPLIT THE EXPIRY RECORDS >>>– –<<EDA02030601
IF ((@m_PrdType =’LON’ AND (@m_ExpiryDt <> ” Or @m_ForeClsDt <> ”))
Or (@p_Purpose = ‘CLSWODINT’ Or @p_Purpose = ‘CLSWODINTUPD’) –<<EDA1403200602
Or (@m_PrdClosedDt IS NOT Null AND @m_IntContinue = ‘N’ AND @m_ForeClStat = ‘Y’)) –<<EDA1603200602
BEGIN
SET @m_ExpiryDt = CASE WHEN @m_ExpiryDt <> ” THEN @m_ExpiryDt ELSE @m_ForeClsDt END
EXEC IRC_sSplitExpiryRec @BrID, @PrdID, @AcctID, @m_ExpiryDt,
@m_RepayFreq, @IntRepayType, @m_PrdType, @m_PrdClosedDt,
@m_IntContinue, @p_Purpose, @p_OnTxnAmt
END IF @IntRepayType = ‘MINBALN’ Or @IntRepayType =’DDTYPE’ BEGIN
–<< GET THE MONTH BALANCE FROM VALUE BALANCE
EXEC IRC_sUpdateMonthBaln @BrID,@PrdID,@AcctID,@p_Purpose
END –<< GET THE RD BALANCE AMOUNT FROM BALACE HISTORY
IF @IntRepayType =’RDTYPE’ BEGIN
[email protected]_CalcFrq = dbo.IRC_fGetFreqVal(@ProdDaysCalcType,@IntProdDays)
EXEC IRC_sRDAcctCalc @BrID, @PrdID, @AcctID, @AcctOpnAmt, @TenureMonths,
@p_Purpose, @m_CalcFrq, @CalcFreqType,@m_InstallAmt,@AcctOpenDt
END IF (@p_Purpose = ‘PRECLS’ Or @p_Purpose = ‘PRECLSUPD’) AND @IntRepayType <> ‘EFIFR’ BEGIN
— SET @TenureYears = 0
— SET @TenureMonths = 0
— SET @TenureDays = CASE WHEN dbo.IRC_fCalcDuration(@AcctOpenDt,@CalcToDt,’D’) > 0 THEN dbo.IRC_fCalcDuration(@AcctOpenDt,@CalcToDt,’D’)
— ELSE 0 END
—
— SET @HasFixedIntRt =’N’
— –<<ADDED BY EDA ON 01.12.2005
— IF @TenureDays = 0 BEGIN
— RETURN
— END –<< ADDED BY SRN ON 10JAN2006 FOR TENNURE DAYS SOME DOES NOT TALLY WITH SLAB (>3 MONTHS)
IF dbo.IRC_fCalcDuration(@m_AcctValueDt,@CalcToDt,’M’)<=3BEGIN
SET @TenureYears = 0
SET @TenureMonths = 0
SET @TenureDays = CASE WHEN dbo.IRC_fCalcDuration(@m_AcctValueDt,@CalcToDt,’D’) > 0 THEN dbo.IRC_fCalcDuration(@m_AcctValueDt,@CalcToDt,’D’) + 1
ELSE 0 END
END
ELSEBEGIN
SET @TenureYears = 0
SET @TenureMonths = CASE WHEN dbo.IRC_fCalcDuration(@m_AcctValueDt,@CalcToDt,’M’) > 0 THEN dbo.IRC_fCalcDuration(@m_AcctValueDt,@CalcToDt,’M’)
ELSE 0 END
–<<ADDED BY EDA ON 02.03.2006(FOR CONSIDERING DAYS FOR PRECLOSURE) –<<EDA02030601
SET @TenureDays = dbo.IRC_fCalcDuration(dbo.IRC_fCalcToDate(@m_AcctValueDt,0,@TenureMonths,0),@CalcToDt,’D’) + 1 –<<EDA2303200602 –<<EDA02030601
IF @TenureDays < 0 BEGIN
SET @TenureDays = 0
END
END — IF @IntRepayType <> ‘RDTYPE’ AND (@CalcToDt < @m_ExpiryDt) BEGIN
— SET @HasFixedIntRt =’N’
— END –IF (@IntRepayType = ‘RDTYPE’ AND (@CalcToDt > @m_ExpiryDt)) Or @m_ForeClStat =’Y’ BEGIN
IF (@IntRepayType = ‘RDTYPE’ AND @m_PreClsDev = 0) Or @m_ForeClStat =’Y’ BEGIN –<<EDA2303200601
SET @HasFixedIntRt =’Y’
END
ELSE BEGIN
SET @HasFixedIntRt =’N’
END –<<ADDED BY EDA ON 01.12.2005
IF @TenureDays = 0 and @TenureMonths = 0 BEGIN
RETURN
END
–SRN 10Jan2006 Change End
END –<< TO CALCULATE THE PENAL AND PENALTY
IF @p_Purpose = ‘PENALCALC’ Or @p_Purpose = ‘CLSWOODINT’ Or @p_Purpose =’PENALCALCUPD’ BEGIN
–<< IN CASE OF PENALTY AND PENAL NOT NECESSARY TO CALC. INT.
–<< OR CLOSURE WITHOUT OVERDUE INT.
SET @m_ApplyInt = ‘N’
END
ELSE BEGIN
–<< OTHER CASES TO CALC. INT.
SET @m_ApplyInt = ‘Y’
END
–<< IF LAST DEMAND DATE – CURRENT BUSN DATE >= GRACEPERIOD –SET REQD-‘Y’
SET @m_GracePerdReqd = dbo.IRC_fGracePerdReqd(@LstDemDt,@BusnDt,@m_ODGracePerd)
—Commented and added BY MSS 28-12-2005
— SET @m_ApplyPenal = CASE WHEN @IntRepayType =’DBM’ THEN @m_GracePerdReqd Else ‘N’ End
SET @m_ApplyPenal = CASE WHEN @IntRepayType =’DBM’ Or @IntRepayType =’FINST’ THEN @m_GracePerdReqd Else ‘N’ End SET @m_ApplyPenalty = CASE WHEN @IntRepayType =’DBM’ Or @IntRepayType = ‘EFI’ Or @IntRepayType =’EFIFR’ Or @IntRepayType =’RDTYPE’
THEN @m_GracePerdReqd Else ‘N’ End IF @HasFixedIntRt <> ‘Y’ BEGIN
–<<< SPLIT THE RECORDS BASED ON THE EFFECTIVE DATE >>>—
EXEC @m_RtnVal = IRC_sSplitPeriods @BrID, @PrdID, @AcctID, @IntRateType, @m_AcctValueDt — << IF APPLICABLE, CALL THE DURATION SLAB PROCEDURE
— IF @m_RtnVal <> 0 BEGIN
EXEC @m_RtnVal = IRC_sSplitDurSlabs @m_AcctValueDt, @IntProdDays,’Y’,@TenureYears,@TenureMonths,
@TenureDays, @BrID, @PrdID, @AcctID
SET @m_RtnVal1 = @m_RtnVal
— END — << IF APPLICABLE, CALL THE SPLIT AMOUNT SLAB PROCEDURE
— IF @m_RtnVal <> 0 BEGIN
EXEC @m_RtnVal = IRC_sSplitAmtSlabs @AcctOpnAmt,@DisbAmt,’N’, @IntProdDays,’Y’,@BrID, @PrdID, @AcctID
SET @m_RtnVal2 = @m_RtnVal
— END — << IF APPLICABLE, CALL THE PERCENTAGE SLAB PROCEDURE
— IF @m_RtnVal <> 0 BEGIN
EXEC IRC_sSplitPercSlabs @AcctOpnAmt, @DisbAmt, @IntProdDays,’Y’,@BrID, @PrdID, @AcctID
SET @m_RtnVal3 = @m_RtnVal
— END UPDATE #TmptCalcSlab
SET ROI = CASE WHEN (@p_Purpose = ‘PRECLS’ Or @p_Purpose = ‘PRECLSUPD’) AND IRDetl.ROI > 0 THEN
CASE @m_IntDev WHEN ‘LT’ THEN (IRDetl.ROI – @m_IntDevPerc) – @m_PreClsDev
WHEN ‘GT’ THEN (IRDetl.ROI + @m_IntDevPerc) – @m_PreClsDev
ELSE (IRDetl.ROI – @m_PreClsDev) END
WHEN ToDt > @m_ExpiryDt THEN IRDetl.ExpiryROI
ELSE IRDetl.ROI End,
PenalROI = IRDetl.PenalROI,
PenaltyROI = IRDetl.Penalty,
AcctCyID= @m_AcctCyID
FROM#TmptCalcSlab Slab, #TmptIRDetl IRDetl
WHEREIRDetl.BrID= @BrID
ANDIRDetl.PrdID= @PrdID
ANDSlab.AcctID= @AcctID
ANDIRDetl.IRID = Slab.IRID
ANDIRDetl.DurSlabID = IsNull(Slab.DurSlabID,0)
ANDIRDetl.AmtSlabID = IsNull(Slab.AmtSlabID,0)
ANDIRDetl.BrID = Slab.BrID
ANDIRDetl.PrdID = Slab.PrdID
AND(IRDetl.AcctID = Slab.AcctID Or IRDetl.AcctID Is Null)
ANDIRDetl.ROI > 0
END
ELSEBEGIN UPDATE #TmptCalcSlab
SET ROI = CASE WHEN ToDt > @m_ExpiryDt AND @m_PrdType =’LON’ THEN IRDetl.ExpiryROI
WHEN ToDt > @m_ExpiryDt AND (@p_Purpose =’CLSWODINT’ Or @p_Purpose =’CLSWODINTUPD’) THEN IRDetl.ExpiryROI
ELSE IRDetl.ROI END,
PenalROI = IRDetl.PenalROI,
PenaltyROI = IRDetl.Penalty,
AcctCyID= @m_AcctCyID,
PreClsROI= IRDetl.PreClsROI
FROM#TmptCalcSlab Slab, #TmptIRDetl IRDetl
WHEREIRDetl.BrID= @BrID
ANDIRDetl.PrdID= @PrdID
ANDSlab.AcctID= @AcctID
ANDIRDetl.BrID= Slab.BrID
ANDIRDetl.PrdID= Slab.PrdID
AND(IRDetl.AcctID= Slab.AcctID Or IRDetl.AcctID Is Null)
ANDIRDetl.ROI > 0
END IF @p_Purpose = ‘PRECLS’ Or @p_Purpose = ‘PRECLSUPD’ Or @p_Purpose = ‘ONSCR’ BEGIN
UPDATE #TmptCalcSlab
SET ROI = CASE IsNull(SPL.SplRateDev,0)
WHEN ‘LT’ THEN
(Slab.ROI – SPL.SplRate)
WHEN ‘GT’ THEN
(Slab.ROI + SPL.SplRate)
ELSE Slab.ROI END
FROM#TmptCalcSlab Slab, dbo.IRC_fGetPrdSplRateDetl(@PrdID,@m_SRID) SPL
WHERESlab.BrID= @BrID
ANDSlab.PrdID= @PrdID
ANDSlab.AcctID= @AcctID
–<<MODIFIED BY EDA ON 22.10.2005( TO AVOID INT. CALC FOR BEFORE 30 DAYS CASE)
ANDSlab.ROI> 0 IF @m_PreClsDev <= 0 AND @IntRepayType <> ‘RDTYPE’ AND (@CalcToDt < @m_ExpiryDt) BEGIN
UPDATE #TmptCalcSlab
SET ROI = PreClsROI
FROM#TmptCalcSlab Slab
WHERESlab.BrID= @BrID
ANDSlab.PrdID= @PrdID
ANDSlab.AcctID= @AcctID
–<<MODIFIED BY EDA ON 22.10.2005( TO AVOID INT. CALC FOR BEFORE 30 DAYS CASE)
ANDSlab.ROI> 0
END
END IF @IntRepayType =’EFIFR’ AND @p_Purpose <> ‘PRECLS’ BEGIN
–<<< UPDATE THE PRINCIPAL DUE & INTERESTDUE IF EMI CALCULATION METHOD IS FLAT >>>–
–<<< MODIFIED BY EDA ON 22.10.2005
SET @m_ActPrinDue = 0
SET @m_ActIntDue = 0 SELECT @m_EMIAmt = IsNull(EMIAMT,0), @m_PrinDue = TotPrinDue, @m_IntDue = TotIntDue
FROM dbo.IRC_fPrdSchDetl_V2(@BrID, @PrdID, @AcctID,@BusnDt,@m_PrdType) IF @p_Purpose =’ONTXN’ AND @m_EMIAmt <> 0 BEGIN
IF IsNull(@p_OnTxnAmt,0) = 0 BEGIN
SET @m_ActPrinDue = @m_PrinDue
SET @m_ActIntDue = @m_IntDue
END
ELSE BEGIN
SET @m_Div = floor(@p_OnTxnAmt/@m_EMIAmt)
if @m_Div <1
BEGIN
SET @m_ActPrinDue = @m_PrinDue
SET @m_ActIntDue = @m_IntDue
–<<Commented by SRN on 07.11.2005(for HP-A Int Calc. issue)
— IF (@p_OnTxnAmt – (@m_EMIAmt * @m_Div)) – @m_IntDue < 0 BEGIN
— SET @m_ActPrinDue = @m_PrinDue * @m_Div
— SET @m_ActIntDue = @m_IntDue * @m_Div+ (@p_OnTxnAmt – (@m_EMIAmt * @m_Div))
— END
— ELSE BEGIN
— SET @m_ActPrinDue = @m_PrinDue * @m_Div + (@p_OnTxnAmt – (@m_EMIAmt * @m_Div)) – @m_IntDue
— SET @m_ActIntDue = @m_IntDue * (@m_Div + 1)
— END
END
ELSE
BEGIN
SET @m_ActPrinDue = @m_PrinDue
SET @m_ActIntDue = @m_IntDue
SET @m_Div = @m_Div-1
SET @m_tmpBusnsDt = DATEADD(month,1,@BusnDt)
SET @p_TmpOnTxnAmt = @p_OnTxnAmt – (@m_PrinDue + @m_IntDue) SELECT @m_EMIAmt = EMIAMT, @m_PrinDue = TotPrinDue, @m_IntDue = TotIntDue
FROM dbo.IRC_fPrdSchDetl_V2(@BrID, @PrdID, @AcctID,@m_tmpBusnsDt,@m_PrdType)
IF (@p_TmpOnTxnAmt – (@m_EMIAmt * @m_Div)) – @m_IntDue < 0 BEGIN
SET @m_ActPrinDue = @m_ActPrinDue + (@m_PrinDue * @m_Div)
SET @m_ActIntDue = @m_ActIntDue + (@m_IntDue * @m_Div + (@p_TmpOnTxnAmt – (@m_EMIAmt * @m_Div)))
END
ELSE BEGIN
SET @m_ActPrinDue = @m_ActPrinDue + (@m_PrinDue * @m_Div + (@p_TmpOnTxnAmt – (@m_EMIAmt * @m_Div)) – @m_IntDue )
SET @m_ActIntDue = @m_ActIntDue + (@m_IntDue * (@m_Div + 1))
END
END
END
END
ELSE IF @p_Purpose =’DEMAND’ AND @m_EMIAmt <> 0 BEGIN
–<<ADDED BY EDA ON 09.11.2005(FOR ROUTINE)
SET @m_ActPrinDue = @m_PrinDue
SET @m_ActIntDue = @m_IntDue
END UPDATE #TmptCalcSlab
SET PRINCIPAL = @m_ActPrinDue,
INTEREST = @m_ActIntDue
FROM#TmptCalcSlab Slab
WHERE Slab.BrID = @BrID
ANDSlab.PrdID = @PrdID
AND Slab.AcctID = @AcctID
ANDSlab.SlabNo = (Select Max(SlabNo) From #TmptCalcSlab) RETURN
END
ELSE IF (@IntRepayType =’DBM’ AND @p_Purpose <> ‘PENALCALC’) AND (@IntRepayType =’DBM’ AND @p_Purpose <> ‘PENALCALCUPD’) BEGIN
–<<< UPDATE THE PRINCIPAL DUE IF EMI CALCULATION METHOD IS DBM >>>–
UPDATE #TmptCalcSlab
SET PRINCIPAL = CASE WHEN Slab.DmdFlag = ‘Y’ THEN SCH.TotPrinDue ELSE 0 END
FROM#TmptCalcSlab Slab,dbo.IRC_fPrdSchDetl_V2(@BrID, @PrdID, @AcctID, @BusnDt, @m_PrdType) SCH
WHERE Slab.BrID = @BrID
ANDSlab.PrdID = @PrdID
AND Slab.AcctID = @AcctID
ANDSlab.BrID = SCH.BrID
ANDSlab.PrdID = SCH.PrdID
ANDSlab.AcctID = SCH.AcctID
–<<COMMENTED BY EDA ON 16.10.2005(FOR MONTHLY ROUTINE PURPOSE)
— ANDSlab.DmdFlag = ‘Y’
END IF UPPER(@p_Purpose) = ‘RDPENALTY’ Or UPPER(@p_Purpose) = ‘RDPENALTYUPD’ BEGIN
–<<< CALCULATE THE PENALTY FOR RD
EXEC IRC_sRDPenaltyCalc @p_BrID = @BrID, @p_PrdID = @PrdID, @p_AcctID = @AcctID,
@p_AcctOpenDt = @m_AcctValueDt , @p_InstallAmt = @m_InstallAmt,
@p_NoofDaysPerYr = @IntProdDays
RETURN
END –<< CALCULATE INTEREST FOR THE RECORDS AVAIL. IN THE #TMPTCALCSLAB TABLE
IF UPPER(@IntCalcType) = ‘SMP’ BEGIN
EXEC IRC_sCalcIntAmt @p_BrID = @BrID, @p_PrdID = @PrdID, @p_AcctID = @AcctID,
@p_IntCalcFreq = @IntCalcFreq, @p_IntPrdType = @ProdDaysCalcType,
@p_NoofDaysPerYr = @IntProdDays,@p_ApplyInt = @m_ApplyInt,
@p_ApplyPenal = @m_ApplyPenal, @p_ApplyPenalty = @m_ApplyPenalty,
@p_ONTxnAmt = @p_OnTxnAmt,@p_Purpose = @p_Purpose, @p_RepayType = @IntRepayType,
–<<ADDED BY SRN ON 28.10.2005(FOR REPAYTYPE PARAMETERS)
@[email protected], @p_OpenDt = @m_AcctValueDt, @p_LstPenaltyCalcDt = @m_LstPenaltyCalcDt –<<EDA20JAN06
END
–<<< CALCULATE THE COMPOUND INTEREST FOR THE RECORDS AVAIL. IN THE #TMPTCALCSLAB TABLE
ELSE IF UPPER(@IntCalcType) = ‘CMP’ BEGIN
–<< Split the records IF @p_Purpose =’ONSCR’ BEGIN
SET @LstCompDt = @AcctOpenDt
END
ELSE IF @p_Purpose =’REPCALCEXPY’ BEGIN
–<<ADDED BY EDA ON 23.02.2006 FOR ONEXPIRY CASES CALCDT < LSTACCRDT
SET @LstCompDt = @m_LstCalcDt
END EXEC IRC_sCompIntCalc @BrID,@PrdID, @AcctID,@IntCompFreq,@CompFreqType,
@IntCalcFreq, @ProdDaysCalcType, @IntProdDays,
@LstCompDt, @m_ApplyInt, @m_ApplyPenal,
@m_ApplyPenalty, @m_AcctValueDt –<<EDA20JAN06 END
ELSE IF UPPER(@IntCalcType) = ‘DSS’ BEGIN
–<<< CALCULATE THE MONTHLY INTEREST FOR FIXED DEPOSIT
EXEC IRC_sFDDiscCalc @p_BrID = @BrID, @p_PrdID = @PrdID, @p_AcctID = @AcctID,
@p_RepayFreq = @DmdFreq, @p_FreqType = @DmdFreqType,
@p_NoofDaysPerYr = @IntProdDays
END IF @p_Purpose =’REPCALC’ Or @p_Purpose = ‘REPCALCEXPY’ BEGIN –<<EDA06FEB06
IF @CalcToDt > @LstAccrDt BEGIN
–<< FETCH BALANCE DETAILS –<<EDA06FEB06
SELECT @m_BalnPrinDue = TotPrinDue, @m_BalnIntDue = TotIntDue,
@m_BalnPenaltyDue = TotPenaltyDue,@m_BalnPenalIntDue = TotPenalIntDue,
@m_TotOS = TotOS
FROM dbo.IRC_fFetchBalnDetl(@BrID, @PrdID, @AcctID) –<< FETCH ACCRUAL DETAILS –<<EDA06FEB06
SELECT @m_AccrPrinDue = TotPrinDue, @m_AccrIntDue = TotIntDue,
@m_AccrPenaltyDue = TotPenaltyDue,@m_AccrPenalIntDue = TotPenalIntDue
FROM dbo.IRC_fFetchAccruDetl_V2(@BrID, @PrdID, @AcctID,@m_PrdType) UPDATE #TmptCalcSlab
SET Principal = CASE WHEN @m_ARCStat = ‘Y’ THEN IsNull(@m_BalnPrinDue,0) ELSE (IntCalc.TotPrinAmt + IsNull(@m_BalnPrinDue,0)) End,
Interest = CASE WHEN @m_ARCStat = ‘Y’ THEN (@m_BalnIntDue + @m_AccrIntDue) ELSE (IntCalc.TotIntAmt + (@m_BalnIntDue + @m_AccrIntDue)) End,
Penalty = CASE WHEN @m_ARCStat = ‘Y’ THEN (@m_BalnPenaltyDue + @m_AccrPenaltyDue) ELSE (IntCalc.TotPenaltyAmt + (@m_BalnPenaltyDue + @m_AccrPenaltyDue)) END,
Penal = CASE WHEN @m_ARCStat = ‘Y’ THEN (@m_BalnPenalIntDue + @m_AccrPenalIntDue) ELSE (IntCalc.TotPenalAmt + (@m_BalnPenalIntDue + @m_AccrPenalIntDue)) END
FROM #TmptCalcSlab SLAB, (SELECT BrID, PrdID, AcctID, SUM(IsNull(Principal,0)) TotPrinAmt, SUM(IsNull(Interest,0)) AS TotIntAmt,
SUM(IsNull(Penalty,0)) As TotPenaltyAmt, SUM(IsNull(Penal,0)) As TotPenalAmt
FROM #TmptCalcSlab Group By BrID, PrdID, AcctID) IntCalc
WHERE SLAB.BrID = @BrID
ANDSLAB.PrdID = @PrdID
AND SLAB.AcctID = @AcctID
ANDSLAB.BrID = IntCalc.BrID
ANDSLAB.PrdID = IntCalc.PrdID
ANDSLAB.AcctID = IntCalc.AcctID
ANDSLAB.SlabNo = (Select Max(SlabNo) From #TmptCalcSlab) UPDATE #TmptCalcSlab
SET Principal = 0,
Interest = 0,
Penalty = 0,
Penal = 0
WHERE SlabNo NOT IN (Select Max(SlabNo) From #TmptCalcSlab)
RETURN
END
ELSE IF @CalcToDt < @LstAccrDt BEGIN –<<EDA06FEB06
–<< FETCH BALANCE HISTORY DETAILS
SELECT @m_BalnPrinDue = TotPrinDue, @m_BalnIntDue = TotIntDue,
@m_BalnPenaltyDue = TotPenaltyDue,@m_BalnPenalIntDue = TotPenalIntDue,
@m_TotOS = TotOS
FROM dbo.IRC_fFetchBalnHistDetl(@BrID, @PrdID, @AcctID,@CalcToDt,@m_PrdType) UPDATE #TmptCalcSlab
SET Principal = CASE WHEN @m_ARCStat = ‘Y’ THEN @m_BalnPrinDue ELSE (IntCalc.TotPrinAmt + @m_BalnPrinDue) END,
Interest = CASE WHEN @m_ARCStat = ‘Y’ THEN @m_BalnIntDue ELSE (IntCalc.TotIntAmt + @m_BalnIntDue) END,
Penalty = CASE WHEN @m_ARCStat = ‘Y’ THEN @m_BalnPenaltyDue ELSE (IntCalc.TotPenaltyAmt + @m_BalnPenaltyDue) END,
Penal = CASE WHEN @m_ARCStat = ‘Y’ THEN @m_BalnPenalIntDue ELSE (IntCalc.TotPenalAmt + @m_BalnPenalIntDue) END,
TotOS = @m_TotOS
FROM #TmptCalcSlab SLAB, (SELECT BrID, PrdID, AcctID, SUM(IsNull(Principal,0)) As TotPrinAmt, SUM(IsNull(Interest,0)) As TotIntAmt,
SUM(IsNull(Penalty,0)) As TotPenaltyAmt, SUM(IsNull(Penal,0)) As TotPenalAmt
FROM #TmptCalcSlab Group By BrID, PrdID, AcctID) IntCalc
WHERE SLAB.BrID = @BrID
ANDSLAB.PrdID = @PrdID
AND SLAB.AcctID = @AcctID
ANDSLAB.BrID = IntCalc.BrID
ANDSLAB.PrdID = IntCalc.PrdID
ANDSLAB.AcctID = IntCalc.AcctID
ANDSLAB.SlabNo = (Select Max(SlabNo) From #TmptCalcSlab) UPDATE #TmptCalcSlab
SET Principal = 0,
Interest = 0,
Penalty = 0,
Penal = 0
WHERE SlabNo NOT IN (Select Max(SlabNo) From #TmptCalcSlab) RETURN
END
END –<<EDA06FEB06 IF ((@IntRepayType =’EFI’ Or @IntRepayType = ‘DBM’) AND @p_Purpose <> ‘PENALCALC’ AND @p_Purpose <> ‘PENALCALCUPD’ AND @p_Purpose <> ‘ONCLOSURE’ AND @p_Purpose <> ‘ONCLOSUREUPD’ ) BEGIN
–<<< UPDATE THE PRINCIPAL DUE & INTERESTDUE IF EMI CALCULATION METHOD IS FLAT >>>– SELECT @m_TotPrinOD = TotPrinOD, @m_TotOS = TotOS, @m_TotPrinAdvColn = TotPrinAdvColn
FROM dbo.IRC_fFetchTxns_V2(@BrID,@PrdID,@AcctID,@m_LstCalcDt,@CalcToDt,
@m_CalcFrq,@CalcFreqType,@IntRepayType,@m_PrdType) IF EXISTS (SELECT ‘X’ FROM #TmptCalcSlab WHERE DmdFlag = ‘Y’)
BEGIN
IF @IntRepayType =’EFI’ BEGIN
UPDATE #TmptCalcSlab
–Following 2 lines added by SRN on 15-Mar-2006 for Current montn Demand Expiry OD
SET PRINCIPAL = Case WHEN ((MONTH(@CalcToDt) >= MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) >= YEAR(@m_ExpiryDt))
Or (MONTH(@CalcToDt) < MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) > YEAR(@m_ExpiryDt))) THEN (@m_TotOS – @m_TotPrinOD)+ @m_TotPrinAdvColn Else –<<EDA1204200601
CASE WHEN SCH.EMIAMT <= @p_OnTxnAmt THEN CASE WHEN (IsNull(IntCalc.TotPrinAmt,0) – IsNull(IntCalc.TotIntAmt,0)) > 0 THEN IsNull(IntCalc.TotPrinAmt,0) – IsNull(IntCalc.TotIntAmt,0) ELSE 0 END
WHEN SCH.EMIAMT > @p_OnTxnAmt THEN CASE WHEN (SCH.EMIAMT – IsNull(IntCalc.TotIntAmt,0)) > 0 THEN (SCH.EMIAMT – IsNull(IntCalc.TotIntAmt,0)) ELSE 0 END
ELSE 0 END
END
FROMdbo.IRC_fPrdSchDetl_V2(@BrID, @PrdID, @AcctID,@BusnDt,@m_PrdType) SCH,
— FROMdbo.IRC_fPrdSchDetl(@BrID, @PrdID, @AcctID,@CalcToDt) SCH,
#TmptCalcSlab SLAB,
(SELECT SUM(Principal) TotPrinAmt, SUM(INTEREST) AS TotIntAmt FROM #TmptCalcSlab) IntCalc
WHERE SLAB.BrID = @BrID
ANDSLAB.PrdID = @PrdID
AND SLAB.AcctID = @AcctID
ANDSCH.BrID = SLAB.BrID
ANDSCH.PrdID = SLAB.PrdID
ANDSCH.AcctID = SLAB.AcctID
–<<COMMENTED BY EDA ON 16.10.2005(FOR MONTHLY ROUTINE PURPOSE)
ANDSlab.DmdFlag = ‘Y’
END
ELSE IF @IntRepayType =’DBM’ BEGIN
UPDATE #TmptCalcSlab
–Following 2 lines added by SRN on 15-Mar-2006 for Current montn Demand Expiry OD
SET PRINCIPAL = CASE WHEN ((MONTH(@CalcToDt) >= MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) >= YEAR(@m_ExpiryDt))
Or (MONTH(@CalcToDt) < MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) > YEAR(@m_ExpiryDt))) THEN (@m_TotOS – @m_TotPrinOD)+ @m_TotPrinAdvColn –<<EDA1204200601
ELSE IsNull(SCH.EMIAMT,0) END
FROMdbo.IRC_fPrdSchDetl_V2(@BrID, @PrdID, @AcctID,@BusnDt,@m_PrdType) SCH,
#TmptCalcSlab SLAB,
(SELECT SUM(Principal) TotPrinAmt, SUM(INTEREST) AS TotIntAmt FROM #TmptCalcSlab) IntCalc
WHERE SLAB.BrID = @BrID
ANDSLAB.PrdID = @PrdID
AND SLAB.AcctID = @AcctID
ANDSCH.BrID = SLAB.BrID
ANDSCH.PrdID = SLAB.PrdID
ANDSCH.AcctID = SLAB.AcctID
–<<COMMENTED BY EDA ON 16.10.2005(FOR MONTHLY ROUTINE PURPOSE)
ANDSlab.DmdFlag = ‘Y’
END
END
ELSE BEGIN
IF @IntRepayType =’EFI’ BEGIN
UPDATE #TmptCalcSlab
–SET PRINCIPAL = Case WHEN IsNull(SCH.EMIAMT,0) – IsNull(IntCalc.TotIntAmt,0) > 0 THEN IsNull(SCH.EMIAMT,0) – IsNull(IntCalc.TotIntAmt,0)
–<<ADDED BY EDA ON 20.01.2006(AFTER EXPIRY CASES)
–SET PRINCIPAL = Case WHEN (MONTH(@CalcToDt) >= MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) >= YEAR(@m_ExpiryDt)) THEN (@m_TotOS – @m_TotPrinOD)
SET PRINCIPAL = Case WHEN ((MONTH(@CalcToDt) >= MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) >= YEAR(@m_ExpiryDt))
Or (MONTH(@CalcToDt) < MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) > YEAR(@m_ExpiryDt))) THEN (@m_TotOS – @m_TotPrinOD)+ @m_TotPrinAdvColn –<<EDA1204200601
ELSE Case WHEN IsNull(SCH.EMIAMT,0) – IsNull(IntCalc.TotIntAmt,0) > 0
THEN IsNull(SCH.EMIAMT,0) – IsNull(IntCalc.TotIntAmt,0) Else 0 End End
——–<<<<<<modified by log for Emi if dmdflag is null–>>>>>>———–
FROMdbo.IRC_fPrdSchDetl_V2(@BrID, @PrdID, @AcctID, @BusnDt, @m_PrdType) SCH,
#TmptCalcSlab SLAB,
(SELECT SUM(Principal) TotPrinAmt, SUM(INTEREST) AS TotIntAmt FROM #TmptCalcSlab) IntCalc
WHERE SLAB.BrID = @BrID
ANDSLAB.PrdID = @PrdID
AND SLAB.AcctID = @AcctID
ANDSCH.BrID = SLAB.BrID
ANDSCH.PrdID = SLAB.PrdID
ANDSCH.AcctID = SLAB.AcctID
ANDSlab.SlabNo = (Select Max(SlabNo) From #TmptCalcSlab)
–<<COMMENTED BY EDA ON 16.10.2005(FOR MONTHLY ROUTINE PURPOSE)
END
ELSE IF @IntRepayType =’DBM’ BEGIN
UPDATE #TmptCalcSlab
–<<ADDED BY EDA ON 20.01.2006(AFTER EXPIRY CASES)
–SET PRINCIPAL = IsNull(SCH.EMIAMT,0)
–SET PRINCIPAL = CASE WHEN (MONTH(@CalcToDt) >= MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) >= YEAR(@m_ExpiryDt)) THEN (@m_TotOS – @m_TotPrinOD) ELSE SCH.TotPrinDue END
–<<ADDED BY SRN ON 26.01.2006(SL PRIN. DUE ISSUE)
SETPRINCIPAL = CASE WHEN ((MONTH(@CalcToDt) >= MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) >= YEAR(@m_ExpiryDt))
Or (MONTH(@CalcToDt) < MONTH(@m_ExpiryDt) AND YEAR(@CalcToDt) > YEAR(@m_ExpiryDt))) THEN (@m_TotOS – @m_TotPrinOD) + @m_TotPrinAdvColn ELSE SCH.TotPrinDue END –<<EDA1204200601
FROMdbo.IRC_fPrdSchDetl_V2(@BrID, @PrdID, @AcctID,@BusnDt,@m_PrdType) SCH,
#TmptCalcSlab SLAB,
(SELECT SUM(Principal) TotPrinAmt, SUM(INTEREST) AS TotIntAmt FROM #TmptCalcSlab) IntCalc
WHERE SLAB.BrID = @BrID
ANDSLAB.PrdID = @PrdID
AND SLAB.AcctID = @AcctID
ANDSCH.BrID = SLAB.BrID
ANDSCH.PrdID = SLAB.PrdID
ANDSCH.AcctID = SLAB.AcctID
ANDSlab.SlabNo = (Select Max(SlabNo) From #TmptCalcSlab)
–<<COMMENTED BY EDA ON 16.10.2005(FOR MONTHLY ROUTINE PURPOSE)
END
END
END — Select FromDt, ToDt, TotOS, ROI, Flag,Interest, PreInt
— From #TmptCalcSlab
— Order by FromDt End –End –>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
–EXEC IRC_sCompIntCalc ’25’,’14’,’20’,3,’F’,’MONTHLY’,’MONTHLY’,365
alter PROCEDURE IRC_sCompIntCalc
(
@p_BrID UDVC16= Null,
@p_PrdID UDVC16,
@p_AcctID UDVC16= Null,
@p_CompFreq UDVC10,
@p_FreqType UDVC16,
@p_IntCalcFreq UDVC10,
@p_IntPrdType UDVC10,
@p_NoofDaysPerYr UDSINT,
@p_LstCompDt UDDT = Null,
@p_ApplyInt UDCH1 = Null,
@p_ApplyPenal UDCH1 = Null,
@p_ApplyPenalty UDCH1 = Null,
@p_AcctOpenDt UDDT = Null –<<ADDED BY EDA ON 20.01.2006(FOR ANI MONTH INT. CALC.)
)
As
BEGIN
SET NOCOUNT ON DECLARE @m_MinSlabNo UDINT, @m_MaxSlabNo UDINT,
@m_FromDt UDDT,@m_ToDt UDDT,
@m_ROI UDNU124,@m_CompFreq UDSINT,
@m_LstCompDt UDDT,@m_PrdCalcType UDSINT –//Declaration part added by SRN & SJA on 04-OCT-05 for RD Mat Amt Calc Issue//
DECLARE @m_CurrSlab UDINT,@m_MinSlabDt UDDT, @m_MaxSlabDt UDDT SELECT @m_MinSlabDt = MIN(FromDt), @m_MaxSlabDt = MAX(ToDt)
— FROM#TmptCalcSlab WHERE IsNull(Flag,’N’) = ‘N’
FROM#TmptCalcSlab WHERE IsNull(Flag,’N’) IN (‘N’,’E’) –<< Not Currently Used
SET @m_CompFreq = dbo.IRC_fGetFreqVal(Left(@P_CompFreq,1),@p_NoofDaysPerYr)
SET @m_PrdCalcType = dbo.IRC_fGetFreqVal(Left(@p_IntPrdType,1),@p_NoofDaysPerYr) SET @m_LstCompDt = @p_LstCompDt INSERT INTO #TMPTCALCSLAB
(BRID,PRDID,ACCTID,FROMDT,TODT,TOTOS,TOTPRINOD,TOTINTOD,TOTOTHOD,
DURSLABID,AMTSLABID,PERSLABID,DURSLABTYPE,AMTSLABTYPE,
PERSLABTYPE,ROI,PENALROI,PENALTYROI,IRID, PRINCIPAL,FLAG,AcctCyID
)
–<<COMMENTED BY EDA ON 24-MAY-2005 >>–
SELECT BrID,PrdID,AcctID,A.FromDt,A.ToDt,TotOS,TotPrinOD,TotIntOD,TotOthOD,
DurSlabID,AmtSlabID,PerSlabID,DurSlabType,AmtSlabType,
PerSlabType,ROI,PenalROI,PenaltyROI,IRID,Principal,
‘L’ As Flag, AcctCyID
FROM IRC_fGetDateSplit(@m_MinSlabDt ,@m_MaxSlabDt,@m_CompFreq,@p_FreqType,@m_MinSlabNo) A,
#TmptCalcSlab SlabInfo
— WHERE A.FromDt > SlabInfo.FromDt AND A.ToDt <= SlabInfo.ToDt
— WHERE A.FromDt > SlabInfo.FromDt AND A.FromDt <= SlabInfo.ToDt
— PS Start – Code Modified By RPR on 200605131154
WHERE A.FromDt > SlabInfo.FromDt AND A.FromDt < SlabInfo.ToDt
— PS End – Code Modified By RPR on 200605131154
Order by A.FromDt –//SET part added by SRN & SJA on 04-OCT-05 for RD Mat Amt Calc Issue//
SET @m_CurrSlab=SCOPE_IDENTITY() — << TO AVOID ONE DAY ADJUSTMENTS IN INTEREST CALC., THE FOLLOWING UPDATION IMPLEMENTED BY SRN & EDA ON 05-04-2005 >>–
–<<MODIFIED BY EDA & SRN ON 30.09.2005(FOR INT. CALC. TO DATE ADJUSTMENT) UPDATE A
SET A.ToDt = case when @p_FreqType=’ANI’ THEN IsNull((SELECT MIN(Slab.FromDt) FROM #TmptCalcSlab Slab
WHERE Slab.FromDt >A.FromDt),@m_MaxSlabDt)
else IsNull((SELECT DATEADD(Day,-1,MIN(Slab.FromDt)) FROM #TmptCalcSlab Slab
WHERE Slab.FromDt >A.FromDt),@m_MaxSlabDt)
end
FROM #TmptCalcSlab A EXEC IRC_sGetCompDtMark @p_BrID, @p_PrdID, @p_AcctID, @m_LstCompDt, @m_CompFreq, @p_FreqType, @p_CompFreq, @p_NoofDaysPerYr –<<< To Call the Compound Interest Calculation stored procedure >>>–
EXEC IRC_sGetCompInt @p_CompFreq, @p_IntPrdType, @p_NoofDaysPerYr,@p_ApplyInt,
@p_ApplyPenal,@p_ApplyPenalty,@p_AcctOpenDt –<<EDA20JAN2006
SET NOCOUNT OFF
END –>>>>>>>>>>>>>>>>>>>>>>>>>> alter PROCEDURE IRC_sGetCompInt
(
@p_IntCalcFreq UDVC10,
@p_IntPrdType UDVC10,
@p_NoofDaysPerYr UDSINT,
@p_ApplyInt UDCH1 = Null,
@p_ApplyPenal UDCH1 = Null,
@p_ApplyPenalty UDCH1 = Null,
@p_AcctOpenDt UDDT = Null –<<Added by EDA on 20.01.2006(For ANI Pattern Month Int. Calc)
)
As
BEGIN
SET NOCOUNT ON DECLARE @m_MinSlabNo UDINT, @m_MaxSlabNo UDINT,
@m_FromDt UDDT,@m_ToDt UDDT,
@m_ROI UDNU124,@m_IsMnthEnd UDCH1,
@m_PreInt UDNU124, @m_PenalROI UDNU124,
@m_TotIntOD UDNU154 –<< STARTS HERE ADDED by EDA & SRN on 31.12.2005
— SELECT * INTO #TmptCalcSlab_Comp FROM #TmptCalcSlab
— DELETE FROM #TmptCalcSlab INSERT INTO #TmptCalcSlab
(BRID,PRDID,ACCTID,FROMDT,TODT,TOTOS,TOTPRINOD,TOTINTOD,TOTOTHOD,
DURSLABID,AMTSLABID,PERSLABID,DURSLABTYPE,AMTSLABTYPE,
PERSLABTYPE,ROI,PENALROI,PENALTYROI,IRID, PRINCIPAL,FLAG,AcctCyID,
DelFlag
)
SELECT BRID,PRDID,ACCTID,FROMDT,TODT,TOTOS,TOTPRINOD,TOTINTOD,TOTOTHOD,
DURSLABID,AMTSLABID,PERSLABID,DURSLABTYPE,AMTSLABTYPE,
PERSLABTYPE,ROI,PENALROI,PENALTYROI,IRID, PRINCIPAL,FLAG,AcctCyID,
‘NEW’
FROM #TmptCalcSlab
ORDER BY FromDt Delete
From#TmptCalcSlab
WhereDelFlag is Null
–<< ENDS HERE ADDED by EDA & SRN on 31.12.2005 SELECT @m_MinSlabNo = MIN(SlabNo), @m_MaxSlabNo = MAX(SlabNo)
FROM#TmptCalcSlab
–Select * from #TmptCalcSlab
WHILE @m_MinSlabNo < = @m_MaxSlabNo
BEGIN
SELECT @m_FromDt = FromDt, @m_ToDt = ToDt, @m_ROI = ROI, @m_PenalROI = PenalROI,
@m_IsMnthEnd = Flag, @m_TotIntOD = IsNull(TotIntOD,0)
FROM#TmptCalcSlab
WHERE SlabNo = @m_MinSlabNo IF @m_IsMnthEnd = ‘Y’
BEGIN
SELECT @m_PreInt = SUM(INTEREST)
FROM#TmptCalcSlab
WHERE ToDt < @m_ToDt UPDATE #TmptCalcSlab
–SET PreInt = @m_PreInt
SET PreInt = IsNull(@m_PreInt,0) + @m_TotIntOD –<<EDA3103200601
WHERE SlabNo = @m_MinSlabNo
ANDFlag = ‘Y’ UPDATE #TmptCalcSlab
SET Interest = dbo.IRC_fCalcSimpleInt_Fixed(TotOS + IsNull(PreInt,0), @m_FromDt,
@m_ToDt,@m_ROI,@p_IntCalcFreq,@p_IntPrdType,@p_NoofDaysPerYr,@p_AcctOpenDt) ,
Penal = CASE @p_ApplyPenal WHEN ‘Y’ THEN
dbo.IRC_fCalcSimpleInt_Fixed((IsNull(TotPrinOD,0)), @m_FromDt, @m_ToDt,
@m_PenalROI,@p_IntCalcFreq,@p_IntPrdType,@p_NoofDaysPerYr,@p_AcctOpenDt)
— COMMENTED BY SRN ON 10-JAN-2006 FOR JL PENAL INT NOT APPLICABLE FOR INT OD
— + dbo.IRC_fCalcSimpleInt_Fixed((IsNull(TotIntOD,0)), @m_FromDt,
— @m_ToDt,@m_ROI,@p_IntCalcFreq,@p_IntPrdType,@p_NoofDaysPerYr)
ELSE 0 END,
Penalty = CASE @p_ApplyPenalty WHEN ‘Y’ THEN
dbo.IRC_fCalcSimpleInt_Fixed(IsNull(TotPrinOD,0) + IsNull(TotIntOD,0), @m_FromDt,
@m_ToDt,@m_ROI,@p_IntCalcFreq,@p_IntPrdType,@p_NoofDaysPerYr,@p_AcctOpenDt)
ELSE 0END
WHERE SlabNo = @m_MinSlabNo
END
ELSEBEGIN
UPDATE #TmptCalcSlab
SET PreInt = IsNull(@m_PreInt,0)
WHERE SlabNo = @m_MinSlabNo UPDATE #TmptCalcSlab
SET Interest = dbo.IRC_fCalcSimpleInt_Fixed(TotOS + PreInt, @m_FromDt,
@m_ToDt,@m_ROI,@p_IntCalcFreq,@p_IntPrdType,@p_NoofDaysPerYr,@p_AcctOpenDt),
Penal = CASE @p_ApplyPenal WHEN ‘Y’ THEN
dbo.IRC_fCalcSimpleInt_Fixed((IsNull(TotPrinOD,0)), @m_FromDt, @m_ToDt,
@m_PenalROI,@p_IntCalcFreq,@p_IntPrdType,@p_NoofDaysPerYr,@p_AcctOpenDt)
— COMMENTED BY SRN ON 10-JAN-2006 FOR JL PENAL INT NOT APPLICABLE FOR INT OD
— + dbo.IRC_fCalcSimpleInt_Fixed((IsNull(TotIntOD,0)), @m_FromDt,
— @m_ToDt,@m_ROI,@p_IntCalcFreq,@p_IntPrdType,@p_NoofDaysPerYr)
ELSE 0 END,
Penalty = CASE @p_ApplyPenalty WHEN ‘Y’ THEN
dbo.IRC_fCalcSimpleInt_Fixed(IsNull(TotPrinOD,0) + IsNull(TotIntOD,0), @m_FromDt,
@m_ToDt,@m_ROI,@p_IntCalcFreq,@p_IntPrdType,@p_NoofDaysPerYr,@p_AcctOpenDt)
ELSE 0END
WHERE SlabNo = @m_MinSlabNo
ANDFlag = ‘N’
END SET @m_MinSlabNo = @m_MinSlabNo + 1
END
SET NOCOUNT OFF
END Never Give Up

Does all branches have similar type of environment in terms of hardware, software & network too. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided Ã¯Â¿Â½AS ISÃ¯Â¿Â½ with no rights for the sake of knowledge sharing.

Dear Satya Thanks for the Response Almost all the branches have similar infrastructure Deep Regards
Anil Never Give Up

]]>