PL/SQL :: Determine Next Due Date

I have a table that holds the definition of schedules, a schedule defines when a document should be submitted to a specific party. The schedule definition contains a start date, and end date, a recurrence type (is this submitted one time, or on a recurring schedule) and the frequency at which the document should be submitted. The second table provides a history of the submissions, this stores when it was due and when it was received. At the beginning of the month we pre-populate the submittal table with a list of records that will be due for the month. For instance, on September 1 we look through all of the schedules and determine which ones would have a record due at some point in 9/12 and then create a record in the submittal table.

I am having issues getting the calculated list of submittal records to work properly.

The DDL and DML will be in a follow-up post

Here is the query that I am currently using and fails to work properly.

     with schedules as (
       -- generate a list of valid permit schedules
       select s.schedule_id,s.submittal_frequency_months,s.recurrence_type,
       s.first_due_date,s.requires_approval,
       round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) recurrence_number
 
[Code]....

     -- create a list of all potential due dates for these schedules

     select submittal_id_seq.nextval,schedule_Id,8,requires_approval,
     case
      when recurrence_type='One Time'
        then first_due_date
      when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')

[Code]....

      -- exclude those that already have a submittal record ;

Basically I found all possible records in the schedule table that could have a record due in September, then generate a result for all possible instances and then look at only those whose calculated due date is 09/01/2012. I've determined that the root problem I have right now is this line:

(select level iteration from dual connect by level <= (select max(schedules.recurrence_number) from schedules)) d

Schedule ID 469907 has a start date of 05/15/1992 and a frequency of every 2 months. I calculate what I call the recurrence number, which is the number of times the schedule has happened since its start date to now. I use that to do an add_months calculation from the start date and then eventually compare these calculted start dates with my target month (09/12). In this one records case the calculated recurrence number is 122. So when I generate the connect by level is does 122 records for every schedule, so I end up with duplicate records in the submittal table for many of the schedules. This current query could probably work if I could figure out a way to make the level be schedule ID specific, but I've failed at that thus far.

Aside from the fact that this is returning the wrong results, I am thinking there must be a better more efficient method to determine which records are due for a given month. I was thinking there is probably some cool way to use the model clause here, but I haven't got a grasp on that one yet.

If you run the following insert statement you'll see that it inserts over 2400 records:

insert into submittal (submittal_id,schedule_id,submittal_status_type_id,requires_approval,due_date,created_by,created_date,modified_by,modified_Date)
     with schedules as (
       -- generate a list of valid permit schedules
       select s.schedule_id,s.submittal_frequency_months,s.recurrence_type,
       s.first_due_date,s.requires_approval,
  
[Code]....

-- only submittals whose last due date has not passed, null last date included

       and trunc(s.first_due_date,'mm') <= to_date('09/01/2012','mm/dd/yyyy') -- only valid start dates
       --and round(round(months_between(to_date('09/01/2012','mm/dd/yyyy'),s.first_due_date))/decode(s.submittal_frequency_months,0,1,s.submittal_frequency_months)) >0
       )

     -- create a list of all potential due dates for these schedules

     select submittal_id_seq.nextval,schedule_Id,8,requires_approval,
     case
      when recurrence_type='One Time'
        then first_due_date
      when recurrence_type='Recurring' and trunc(first_due_date)=to_date('09/01/2012','mm/dd/yyyy')
  
[Code]...

You can see the problem after words:

select schedule_id,count(0)
from submittal
where trunc(due_date,'mm')=to_date('09/01/2012','mm/dd/yyyy')
and submittal_status_type_id=8
having count(0) >1
group by schedule_id;Tony