pago de facturas

Se hizo el SP para pagar los intereses ya que eso lo solicita la tarea programada, este se complico porque nos costo determinar la factura mas vieja.

Codigo:
CREATE PROCEDURE [dbo].[ARSP_PayBill]
-- Add the parameters for the stored procedure here
@Loan INT,
@PaymentMethod INT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
INSERT INTO dbo.AppliedBalanceMovement(Date,Amortization,Interest,FK_Loan,FK_AppliedBalanceMovementType,Amount)
SELECT GETDATE(), NABM.Amortization, NABM.Interests,NABM.FK_Loan,2,-NABM.Amount
FROM dbo.NonAppliedBalanceMovement NABM
INNER JOIN dbo.Bill B ON ((NABM.FK_Bill = B.ID) AND (NABM.FK_Loan = B.FK_Loan) AND (B.Date = dbo.FN_GetOldestBillDate(B.FK_Loan)) AND (B.FK_BillStatus = 1))
WHERE NABM.FK_Loan = @Loan

UPDATE dbo.Loan SET AppliedBalance = AppliedBalance - NABM.Amount
FROM dbo.NonAppliedBalanceMovement NABM
INNER JOIN dbo.Bill B ON ((NABM.FK_Bill = B.ID) AND (NABM.FK_Loan = B.FK_Loan) AND (B.Date = dbo.FN_GetOldestBillDate(B.FK_Loan)) AND (B.FK_BillStatus = 1))
WHERE NABM.FK_Loan = @Loan

UPDATE dbo.Bill SET FK_PaymentMethod = @PaymentMethod
FROM dbo.Bill B
WHERE ((B.FK_Loan = @Loan) AND (B.Date = dbo.FN_GetOldestBillDate(B.FK_Loan) ) AND (B.FK_BillStatus = 1))

UPDATE dbo.Bill SET FK_BillStatus = 2
FROM dbo.Bill B
WHERE ((B.FK_Loan = @Loan) AND (B.Date = dbo.FN_GetOldestBillDate(B.FK_Loan) ) AND (B.FK_BillStatus = 1))

END

GO

Horas de realización: 5 horas de trabajo y 2 de investigación