Help with Arithmetic Overflow error

Hi. I could use some help troubleshooting an error in a stored
procedure. The SP was developed some time ago and recently started
thowing this error:
Arithmetic overflow error converting numeric to data type numeric.
This error doesn't seem to make much sense since it happens when
inserting data into a physical table from a temp table in the stored
procedure. I've been pulling my hair out trying to figure out a way to
fix it. I'm pasting the code below along with my print statement and
comment showing where the error occurs. Any help is GREATLY
appreciated. Thanks!
--ALTER PROCEDURE [dbo].SP_OCCalculationMVTriggerTest
declare @.dtAsOfdate DATETIME
set @.dtAsOfDate = '2006-04-16';
DECLARE @.RC INTEGER
--
-- 1) Eligible Investments:
--
-- Input: @.SPVId - SPV we are running process for
-- @.Yes - value of enum CCPEnum::eYesNoYes (get by
lookup).
-- Output: Recordset (temp table) of Collaterals that are eligible for
MV Test (#MVTriggerInvestments).
DECLARE @.Yes INTEGER
EXEC @.RC = [dbo].CPLookupVal 'YesNo', 'Yes', @.Yes OUTPUT
IF (@.RC<>0)BEGIN
RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes
enum', 16, 1) WITH SETERROR
END
drop table #MVTriggerInvestments
BEGIN
SELECT dbal.SPVId,
dbal.CusipId,
dbal.GroupId,
@.dtAsOfDate AS AsOfDate,
dbal.NormalOCRate,
dbal.SteppedUpOCRate,
dbal.AllocMarketValue AS MarketValue,
dbal.NbrDays,
dbal.PriceChangeRatio
INTO #MVTriggerInvestments
FROM DailyCollateralBalance dbal
JOIN CollateralGroupIncludeInOC gin
ON dbal.SPVId = 2
AND gin.SPVId = 2
AND dbal.AsOfDate = '2006-04-16'
AND @.dtAsOfDate BETWEEN gin.EffectiveFrom AND
gin.EffectiveTo
AND dbal.GroupId = gin.GroupId
AND gin.IncludeInOC = @.Yes
END
select * from #MVTriggerInvestments
print 'end #1'
--select * from #MVTriggerInvestments --looks ok
---
-- 2) Calculate Weighted Average Price change ratio Market Value (by
Group):
-- PCRMV - Price Change Ratio Market Value
---
-- Input : Recordset of collaterals (having New/Old prices, MarketValue
defined)
-- Output: Recordset Aggregated by Group (#GroupOCRate)
drop table #MVTriggerGroup
BEGIN
SELECT A.SPVId,
A.AsOfDate,
A.GroupId,
A.NormalOCRate,
A.SteppedUpOCRate,
A.MarketValue,
[dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue,
B.MarketValueForPeriod, 0.00) as PriceChangeRatio,
CAST (0 AS NUMERIC(12,9))
AS OCRate,
CAST ('' AS VARCHAR(6))
AS OCRateType,
CAST (0 AS NUMERIC(18,2))
AS DiscMarketValue,
CAST (0 AS NUMERIC(18,2))
AS InterestAccrued
INTO #MVTriggerGroup
FROM
(
SELECT SPVId,
AsOfDate,
GroupId,
NormalOCRate,
SteppedUpOCRate,
SUM(MarketValue) AS MarketValue
FROM #MVTriggerInvestments
GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate,
SteppedUpOCRate
) A --works up to here
JOIN
(SELECT SPVId,
SUM(AllocMarketValue) AS MarketValueForPeriod,
SUM(AllocMarketValue * PriceChangeRatio) as
PriceChangeRatioMarketValue,
GroupId
FROM T_DailyCollateralBalance
WHERE SPVId = 2
AND AsOfDate between '2006-03-17' and '2006-04-15'
AND IsBusinessDay = 1
GROUP BY SPVId, GroupId
) B
ON A.SPVId = B.SPVId
AND A.GroupId = B.GroupId
END
print 'end #2'
---
-- Calculate OCRate to apply for each group.
---
BEGIN
UPDATE #MVTriggerGroup
SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND
ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate
ELSE NormalOCRate
END),
OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND
ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup'
ELSE 'normal'
END)
END
print 'end #3'
--
-- Calculate discounted Market Value
--
UPDATE #MVTriggerGroup
SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01)
print 'end #4'
--
-- Insert data from temp tables
--
-- 1)
select * from #MVTriggerInvestments
print 'begin tran'
BEGIN TRAN
DELETE T_MVTriggerInvestments
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
print 'DELETE T_MVTriggerInvestments'
--error is
here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
INSERT T_MVTriggerInvestments
(
SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
OldPrice ,
NewPrice ,
PriceChangeRatio
)
SELECT SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
0.00 ,
0.00 ,
PriceChangeRatio
FROM #MVTriggerInvestments
print 'end mvtriggerinv select'
COMMIT TRAN
--end
error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!
-- 2)
print 'begin tran 2'
BEGIN TRAN
DELETE T_OCTestGroup
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
INSERT T_OCTestGroup
(
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
SectionA ,
CPFace ,
IntExpense ,
Fees ,
SectionB ,
Receivables ,
IntReceivables ,
CashBalance ,
Investments ,
SectionC ,
ExcessCollateral,
MaxCPAllowed
)
SELECT
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0
FROM #MVTriggerGroup
print 'end tran 2'
COMMIT TRANThis is what the data looks like:
2 3128HDZR3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .15469
2 3128JLT66 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 22764923.64 693 .40260
2 3128JLVQ9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 33325930.88 693 .12272
2 3128JMY50 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 480 -.37161
2 3128NCB86 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 3128NCNV2 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 19 .00000
2 31295KSK1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 -.10075
2 31295NBU1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .97381
2 31295NCW6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .23484
2 31295NDA3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3445254.54 956 .51738
2 31295NMK1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .18800
2 31295NNJ3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 4723304.57 956 -.06034
2 31295NUE6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 10974246.83 956 -.33549
2 31295NUG1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .09947
2 31295NUR7 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3840958.96 956 -.41991
2 31295NZ76 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 4679164.24 956 -.24881
2 31390YZH4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3333555.16 956 -.11334
2 31402C6D9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 46830311.23 689 .74464
2 31402DB25 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 689 .27197
2 31402DBZ2 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 689 .78266
2 31404EXL5 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 7785530.05 721 -.22302
2 31404LPV6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 7814559.22 755 .72999
2 31404MBN7 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 721 .03317
2 31405JJ21 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 480 -.26118
2 31409ARA9 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 31409BB71 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 31409BGD3 ARM 5-1 2006-04-16
00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000
2 31409DSH7 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000
2 31409DSM6 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 .00 7 3083.50000
2 31409DVN0 ARM 5-1 2006-04-16
00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000
2 31409UYN9 ARM 5-1 2006-04-16
00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000
2 36225CWV4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 1575644.70 956 -.14477
2 36225DAY0 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 8995207.86 693 -.17400
2 31337NND6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .00290
2 31349SEA7 ARM 3-1 2006-04-16
00:00:00.000 4.220000000 6.330000000 26777158.30 875 -.32595
2 31349SHH9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 19822156.78 840 -.22582
2 31387A3M5 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 956 .50317
2 31391BKL0 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 4267353.02 956 -.98672
2 31391CJA4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 7446628.34 956 -1.00060
2 31391NM65 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 2783766.24 956 -.33597
2 31404E2E5 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 23112826.15 689 -.16876
2 31404EEJ1 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 12078241.02 784 .10121
2 31404JRP2 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 16699637.14 755 .28834
2 31404JVR3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 10781379.91 755 .43313
2 31404JWZ4 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 11040810.52 662 .46652
2 31404JXW0 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 3883162.72 721 .27610
2 31404JXZ3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .06 662 .00905
2 31404KRS3 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 693 -.20539
2 36225C4Y9 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 784 -.04323
2 36225C6Y7 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 .00 721 -.13550
2 36225C7B6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 19935755.89 721 -.15476
2 36225DAV6 ARM 1-1 FI 2006-04-16
00:00:00.000 2.930000000 4.395000000 11457579.74 693 -.15476
Wish I could upload a spreadsheet...sql