July 10, 2003 at 8:39 am
Script runs but l don't seem to to get any values after running it as per my procedure? How do l test at each point to ensure that l'm carrying the values as per proc? Where am l going wrong? need help
IF OBJECT_ID('dbo.prcRepaymentFacilityLatest') IS NOT NULL
DROP PROC prcRepaymentFacilitylatest
GO
SET NOCOUNT ON
GO
CREATE PROCEDURE dbo.prcRepaymentFacilitylatest
AS
Begin Tran
Update repayment_table
Set
paid_penalty_On_interest = 0,
paid_Penalty_On_principal = 0,
-- Due_penalty_On_interest = 4.22,
-- Due_Penalty_On_principal = 5.33,
Overs = 0,
Paid_Interest = 0,
Paid_Principal = 0,
Due_penalty_On_Fee = 7.10,
paid_penalty_On_Fee = 0,
-- Due_Upfront_fee = 5.00,
-- Due_Monthly_fee = 6.50
Paid_Upfront_fee = 0,
Paid_Monthly_fee = 0
--Declare a Table Variable And Consolidate All payments and group by AccountNo
Declare @Transactions Table
(
AccountNo Varchar(15)
,TotalAmount Money
)
Insert Into @Transactions
Select
AccountNo
,Sum(TotalAmount) TotalAmount
FROM ZA15100P t
GROUP BY AccountNo
--BEGIN TRAN
--These variables store our working values
Declare @T_AccountNo Varchar(15)
Declare @T_TotalUnallocated Money
Declare @T_RepaymentNumber Int
--These variables store data values
Declare @D_totalamount Money
Declare @D_object_key Varchar(15)
Declare @D_repay_no Int
Declare @D_due_princ Money
Declare @D_due_int Money
Declare @D_paid_princ Money
Declare @D_paid_int Money
Declare @D_overs Money
--C1:- These variables store data values Modification
Declare @D_due_Pen_On_princ Money
Declare @D_due_Pen_On_int Money
Declare @D_paid_pen_On_princ Money
Declare @D_paid_Pen_On_int Money
Declare @D_Due_Pen_On_Fee Money
Declare @D_Due_Upfront_Fee Money
Declare @D_Due_Monthly_Fee Money
Declare @D_paid_Pen_On_Fee Money
Declare @D_Paid_Upfront_Fee Money
--Declare @D_Paid_Monthly_Fee Money
--Initialize Variables
Set @T_AccountNo = ''
Set @T_TotalUnallocated = 0
--Declare Cursor ---to add the new fields from C1
Declare RepayFacility_cursor CURSOR FOR
Select
c.totalamount,
r.object_key,
r.repayment_number,
r.due_principal,
r.due_interest,
r.paid_principal,
r.paid_interest,
r.Due_UpFront_Fee,
r.Due_Monthly_Fee,
r.Paid_UpFront_Fee,
r.Paid_Monthly_Fee,
r.Due_Penalty_On_Interest,
r.Due_Penalty_On_Principal,
r.Paid_Penalty_On_Interest,
r.Paid_Penalty_On_Principal,
r.Due_Penalty_On_Fee,
r.Paid_Penalty_On_Fee,
r.overs
From
Repayment_Table r
Left Join
@Transactions C
On r.object_key = c.accountno
Where Not c.accountno Is Null
Order By r.object_key, r.repayment_number
OPEN RepayFacility_cursor
--Read first record
FETCH NEXT FROM RepayFacility_cursor
INTO
@D_totalamount,
@D_object_key,
@D_repay_no,
@D_due_princ,
@D_due_int,
@D_paid_princ,
@D_paid_int,
@D_Due_UpFront_Fee,
@D_Due_Monthly_Fee,
@D_Paid_UpFront_Fee,
@D_Paid_Monthly_Fee,
@D_due_Pen_On_int,
@D_due_Pen_On_princ,
@D_paid_Pen_On_int,
@D_paid_pen_On_princ,
@D_Due_Pen_On_Fee,
@D_paid_Pen_On_Fee,
@D_overs
--Row was beyond the result set
WHILE (@@Fetch_Status <> -1)
BEGIN
--Row fetched is missing
IF (@@Fetch_Status <> -2)
BEGIN
--First time is because it is not initialized
IF not @T_AccountNo = @D_object_key
BEGIN
--Add The OVERS
IF not @T_TotalUnallocated = 0
BEGIN
Set @D_Overs = @T_TotalUnallocated
Update Repayment_Table
Set overs = @D_overs
Where
object_key = @T_AccountNo AND
repayment_number = @T_RepaymentNumber
END
Set @T_AccountNo = @D_object_key
--Remove the -ve by multiplying by -1
Set @T_TotalUnallocated = - @D_totalamount
END
END
Set @T_RepaymentNumber = @D_repay_no
---------------------------------------------------------------------------------
----------1.This calcs Paid_Penalty_On_interest
IF @D_due_Pen_On_int - @D_paid_Pen_On_int > @T_TotalUnallocated
--And Tran_Type = 'ACCI'
BEGIN
--Payment is short
Set @D_paid_Pen_On_int = @T_TotalUnallocated
END
ELSE
BEGIN
-- All Paid_Penalty_On_interest
Set @D_paid_Pen_On_int = @D_due_Pen_On_int - @D_paid_Pen_On_int
END
Set @T_TotalUnallocated = @T_TotalUnallocated - @D_paid_Pen_On_int
----------------------------------------------------------------------------------
----------2.This calcs paid_Penalty_On_principal
IF @D_due_Pen_On_princ - @D_paid_pen_On_princ > @T_TotalUnallocated
BEGIN
--Payment is short
Set @D_paid_pen_On_princ = @T_TotalUnallocated
END
ELSE
BEGIN
-- All paid_Penalty_On_principal
Set @D_paid_pen_On_princ = @D_due_Pen_On_princ - @D_paid_pen_On_princ
END
Set @T_TotalUnallocated = @T_TotalUnallocated - @D_paid_pen_On_princ
-------------------------------------------------------------------------------------
----------3.This calcs Paid_penalty_On_Fee
IF @D_Due_Pen_On_Fee - @D_paid_Pen_On_Fee > @T_TotalUnallocated
BEGIN
--Payment is short
Set @D_paid_Pen_On_Fee = @T_TotalUnallocated
END
ELSE
BEGIN
-- All paid_Penalty_On_principal
Set @D_paid_Pen_On_Fee = @D_Due_Pen_On_Fee - @D_paid_Pen_On_Fee
END
Set @T_TotalUnallocated = @T_TotalUnallocated - @D_paid_Pen_On_Fee
--------------------------------------------------------------------------------------
----------4. This calcs paid_Monthly_Fee
IF @D_Due_Monthly_Fee - @D_Paid_Monthly_Fee > @T_TotalUnallocated
BEGIN
--Payment is short
Set @D_Paid_Monthly_Fee = @T_TotalUnallocated
END
ELSE
BEGIN
-- All Fees paid
Set @D_Paid_Monthly_Fee = @D_Due_Monthly_Fee - @D_Paid_Monthly_Fee
END
Set @T_TotalUnallocated = @T_TotalUnallocated - @D_Paid_Monthly_Fee
-------------------------------------------------------------------------------------
----------5. This calcs paid_Upfront_Fee
IF @D_Due_Upfront_Fee - @D_Paid_Upfront_Fee > @T_TotalUnallocated
BEGIN
--Payment is short
Set @D_Paid_Upfront_Fee = @T_TotalUnallocated
END
ELSE
BEGIN
-- All Fees paid
Set @D_Paid_Upfront_Fee = @D_Due_Upfront_Fee - @D_Paid_Upfront_Fee
END
Set @T_TotalUnallocated = @T_TotalUnallocated - @D_Paid_Upfront_Fee
-------------------------------------------------------------------------------------
-------6. This calcs interest paid
IF @D_due_int - @D_paid_int > @T_TotalUnallocated
BEGIN
--Payment is short
Set @D_paid_int = @T_TotalUnallocated
END
ELSE
BEGIN
-- All interest paid
Set @D_paid_int = @D_due_int - @D_paid_int
END
Set @T_TotalUnallocated = @T_TotalUnallocated - @D_paid_int
--------7. This calcs principal paid
IF @D_due_princ - @D_paid_princ > @T_TotalUnallocated
BEGIN
--Payment is short
Set @D_paid_princ = @T_TotalUnallocated
END
ELSE
BEGIN
-- All interest paid
Set @D_paid_princ = @D_due_princ - @D_paid_princ
END
Set @T_TotalUnallocated = @T_TotalUnallocated - @D_paid_princ
--End Add Other Allocation Here
------Update Allocations ------------------------------------------------
-----------Update paid_Penalty_On_Interest And paid_Penalty_On_principal
Update Repayment_Table
Set paid_penalty_On_interest = @D_paid_Pen_On_int,
paid_Penalty_On_principal = @D_paid_pen_On_princ
Where
object_key = @T_AccountNo AND
repayment_number = @T_RepaymentNumber And Repayment_Number > 0
-------------------------------------------------------------------------
---------Update Paid_Monthly_Fee And Paid_UpFrontpenalty_Fee
Update Repayment_Table
Set paid_penalty_On_Fee = @D_paid_Pen_On_Fee,
Paid_Upfront_Fee = @D_Paid_Upfront_Fee
Where
object_key = @T_AccountNo AND
repayment_number = @T_RepaymentNumber And Repayment_Number > 0
--------------------------------------------------------------------------
--Update Paid_Monthly_Fee And Paid_UpFront_Fee
Update Repayment_Table
Set paid_penalty_On_Fee = @D_paid_Pen_On_Fee,
Paid_Upfront_Fee = @D_Paid_Upfront_Fee
Where
object_key = @T_AccountNo AND
repayment_number = @T_RepaymentNumber
-------------------------------------------------------------------------
--------Update paid_principal And paid_interest
Update Repayment_Table
Set paid_interest = @D_paid_int,
paid_principal = @D_paid_princ
Where
object_key = @T_AccountNo AND
repayment_number = @T_RepaymentNumber And Repayment_Number > 0
----All Allocation Updates Done End Of Report ***
FETCH NEXT FROM RepayFacility_cursor
INTO @D_totalamount,
@D_object_key,
@D_repay_no,
--@D_Due_Date,
@D_due_princ,
@D_due_int,
@D_paid_princ,
@D_paid_int,
@D_Due_UpFront_Fee,
@D_Due_Monthly_Fee,
@D_Paid_UpFront_Fee,
@D_Paid_Monthly_Fee,
@D_due_Pen_On_int,
@D_due_Pen_On_princ,
@D_paid_Pen_On_int,
@D_paid_pen_On_princ,
@D_Due_Pen_On_Fee,
@D_paid_Pen_On_Fee,
@D_overs
END
-- Handle last record
IF Not @T_TotalUnallocated = 0
BEGIN
Set @D_overs = @T_TotalUnallocated
--Update Overs Record
Update Repayment_Table
Set overs = @D_overs
Where
object_key = @T_AccountNo AND
repayment_number = @T_RepaymentNumber
END
CLOSE RepayFacility_cursor
DEALLOCATE RepayFacility_cursor
COMMIT TRAN
July 14, 2003 at 8:00 am
This was removed by the editor as SPAM
July 14, 2003 at 12:28 pm
I'd toss this mess in to Query Analyzer, comment out the Create Proc part, add code for anything that is passed in (not an issue here?), and start using the print statement.
Along the same lines, if I suspect that I'll have to tweak a sproc in the future, I'll leave a commented section in the sproc with Query Analyzer friendly variabls (QA variables) and values. That way, whenever I need to modify the sproc, I can throw it into Query Analyzer, comment out the Create part, uncomment the QA variabls, and start modifying the sproc.
Everett
Everett Wilson
ewilson10@yahoo.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply