I am learning about LOOPS, Records, and Collections. I have a program that I'm working on for a Payment Schedule for a Donor Pledge Amount of $1500. The PL/SQL program runs a payment schedule of 24 months, retrieves the balance from a donation Table called dd_pledge for donor with ID '111', shows the payments amount for each payment, and shows the 24 payments with the BALANCE after each payment down to a ZERO balance. I've included the Table info below, however, I'm only inserting one value from that table into my collection, and then performing calculations on it. This is the desired result for the payments:
anonymous block completed
Pay #: 1 Due: 10/01/2012 Amt: $62.50 Bal: $1,437.50
Pay #: 2 Due: 11/01/2012 Amt: $62.50 Bal: $1,375.00
... Pay #: 23 Due: 08/01/2014 Amt: $62.50 Bal: $62.50
Pay #: 24 Due: 09/01/2014 Amt: $62.50 Bal: $.00
The startdate for the program is'01-OCT-2012' and the payment amount is 62.50 for 24 payments.
I'm using a RECORD and LOOP for this program but it's not working.
I still could use some GUIDANCE on PAYMENT BALANCE iteration in the LOOP and the MONTH only adding 1 month for NOV and using that throughout. That is where I need to add the collection part to my coding and call those values for the LOOP.
Here is my updated coding that is a little simpler, but not sure about the BALANCE calculation entry into the LOOP, and also the payment date from 01-Oct-12 just goes to 01-NOV and uses that as the date. I'm still learning about COLLECTIONS.
SET SERVEROUTPUT ON
DECLARE
payment_num NUMBER(2) := 0;
d_loop_count NUMBER(2) := 24;
due_date DATE;
payment_amt NUMBER(8, 2) := 62.50;
donation_balance NUMBER(8,2);
pledgeamt NUMBER(8,2) := 1500;
---This pledgeamt is not correct, involved in Collection called value from TABLE
start_date DATE := '01-OCT-2012';
BEGIN
/*
SELECT pledgeamt INTO donation_balance
FROM dd_pledge
WHERE idpledge = '111'; --111 has a pledge amount of $1500
*/
FOR i IN 1..d_loop_count
LOOP
payment_num :=payment_num + 1;
due_date := add_months(start_date, 1);
donation_balance := (pledgeamt - payment_amt);
dbms_output.put_line
(
'Pay #: ' || payment_num
|| ' ' || 'Due: ' || due_date
|| ' ' || 'Amt: ' || payment_amt
|| ' ' || 'Bal: '|| donation_balance
);
END LOOP;
END;
/
This is what the spool gives me. It's coming along but need a little more help on the LOOP iterations and the calculations for Donation Payment Balance and the MONTH countdown there for the payment sequences.
anonymous block completed
Pay #: 1 Due: 01-NOV-12 Amt: 62.5 Bal: 1437.5
Pay #: 2 Due: 01-NOV-12 Amt: 62.5 Bal: 1437.5
Pay #: 3 Due: 01-NOV-12 Amt: 62.5 Bal: 1437.5
Pay #: 4 Due: 01-NOV-12 Amt: 62.5 Bal: 1437.5
...Pay #: 20 Due: 01-NOV-12 Amt: 62.5 Bal: 1437.5
Pay #: 21 Due: 01-NOV-12 Amt: 62.5 Bal: 1437.5
Pay #: 22 Due: 01-NOV-12 Amt: 62.5 Bal: 1437.5
Pay #: 23 Due: 01-NOV-12 Amt: 62.5 Bal: 1437.5
Pay #: 24 Due: 01-NOV-12 Amt: 62.5 Bal: 1437.5
Here is the Table info I will be using to input that Balance from the Donation Table into the LOOP.
TABLE dd_pledge info is
IDPLEDGE NUMBER(5,0) No 1
IDDONOR NUMBER(4,0) Yes 2
PLEDGEDATE DATE Yes 3
PLEDGEAMT NUMBER(8,2) Yes 4
IDPROJ NUMBER(5,0) Yes 5
IDSTATUS NUMBER(2,0) Yes 6
WRITEOFF NUMBER(8,2) Yes 7
PAYMONTHS NUMBER(3,0) Yes 8
CAMPAIGN NUMBER(4,0) Yes 9
FIRSTPLEDGE CHAR(1 BYTE) Yes 10
111 ROW equals:
IDPLEDGE IDDONOR PLEDGEDATE PLEDGEAMT
111 306 01-MAR-13 1500
etc...
504 20 0 756 Y
Here is the section on Collections, Records, which is what I'm working on for others to learn more about PL/SQL COLLECTIONS. http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CHDBHJEI
SET SERVEROUTPUT ON
DECLARE
payment_num NUMBER(2) := 0;
d_loop_count NUMBER(2) := 24;
payment_amt NUMBER(8, 2);
donation_balance NUMBER(8,2);
v_pledgeamt NUMBER(8,2);
start_date DATE := '01-OCT-2012';
due_date DATE;
v_due_date VARCHAR2(15);
BEGIN
SELECT pledgeamt INTO v_pledgeamt FROM dd_pledge
WHERE idpledge = &idpledge;
payment_amt := v_pledgeamt / d_loop_count;
due_date := start_date;
donation_balance := (v_pledgeamt - payment_amt);
FOR i IN 1..d_loop_count
LOOP
payment_num :=payment_num + 1;
v_due_date := TO_CHAR(due_date,'mm/dd/yyyy');
due_date := add_months(due_date, 1);
dbms_output.put_line
(
'Pay #: ' || payment_num
|| ' ' || 'Due: ' || v_due_date
|| ' ' || 'Amt: ' || payment_amt
|| ' ' || 'Bal: '|| to_char(donation_balance, '$9999.99')
);
donation_balance := (donation_balance - payment_amt);
END LOOP;
END;
/