title: PL/SQL program that uses a Loop for Payment Schedule with COLLECTION
body: <p>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:</p>
  
  <p>anonymous block completed</p>
  
  <hr>
  
  <p>Pay #: 1 Due: 10/01/2012 Amt: $62.50 Bal: $1,437.50</p>
  
  <hr>
  
  <p>Pay #: 2 Due: 11/01/2012 Amt: $62.50 Bal: $1,375.00</p>
  
  <hr>
  
  <p>... Pay #: 23 Due: 08/01/2014 Amt: $62.50 Bal: $62.50</p>
  
  <hr>
  
  <p>Pay #: 24 Due: 09/01/2014 Amt: $62.50 Bal: $.00</p>
  
  <p>The startdate for the program is'01-OCT-2012' and the payment amount is 62.50 for 24 payments.</p>
  
  <p>I'm using a RECORD and LOOP for this program but it's not working.    </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>    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
  </code></pre>
  
  <p>Here is the Table info I will be using to input that Balance from the Donation Table into the LOOP.</p>
  
  <p>TABLE dd_pledge info is </p>
  
  <pre><code>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
  </code></pre>
  
  <p>Here is the section on Collections, Records, which is what I'm working on for others to learn more about PL/SQL COLLECTIONS. <a href="http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CHDBHJEI" rel="nofollow">http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#CHDBHJEI</a></p>