-
Getting No Values
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
-
you expect us to read this?
Just kidding
Run the procedure in debug mode. you can step through it
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|