-- -----------------------------------------------
-- USING SQL TO DEVELOP TIME ORIENTED APPLICATIONS
-- By : Richard T. Snodgrass
-- -----------------------------------------------
--
-- Fill holes, in general case
-- By   : Bruce C. Huang
-- Date : Dec. 12, 1997
--
-- Oracle 7.3 cannot handle COALESCE with a selection.
-- So this complex INSERTION can be performed with 
-- a PL/SQL procedure.  The PL/SQL procedure is executed
-- with the Oracle command EXECUTE on the SQLPLUS command
-- line.
-- -----------------------------------------------
REM
REM Code Fragment 7.6 in Oracle 7.3
REM
CREATE OR REPLACE PROCEDURE CF7_6 ( in_pcn IN varchar2 )
IS

-- The WHERE clause of the INSERT is represented as a cursur --

CURSOR holes(pcn2 varchar2) IS
    SELECT end_date
    FROM   positions p2
    WHERE  p2.pcn = pcn2
       AND p2.end_date > SYSDATE
       AND p2.end_date < TO_DATE('3000-01-01','YYYY-MM-DD')
       AND NOT EXISTS ( SELECT *
                        FROM   positions p3
                        WHERE  p3.pcn = pcn2
                           AND p3.start_date <= p2.end_date
                           AND p2.end_date < p3.end_date );
new_start_date   date;
new_end_date     date;

BEGIN
   OPEN holes(in_pcn);
   LOOP
     -- For each "hole" found in the time frame, fill it.
     BEGIN
       FETCH holes INTO new_start_date;
       EXIT WHEN holes%NOTFOUND;  -- When no more holes, end LOOP.
       
       -- Inside of COALSCE():  SQL92 COALSCE() replaced by Oracle NVL() --

       SELECT NVL(MIN(p2.start_date),TO_DATE('3000-01-01','YYYY-MM-DD'))
       INTO   new_end_date
       FROM   positions p2
       WHERE  p2.pcn = in_pcn
          AND p2.start_date > new_start_date;

       INSERT INTO POSITIONS
          ( pcn , JOB_TITLE_CODE1 , START_DATE , END_DATE )
       VALUES 
          ( in_pcn , NULL , new_start_date , new_end_date );
     END;
   END LOOP;
   CLOSE holes;
END;
/

EXECUTE cf7_6('341288');
