REM
REM CREATE stored functions required FOR the applications discussed IN 
REM "Developing Oracle Forms Applications."
REM
 
PROMPT
PROMPT CREATE the functions used BY the applicationS.
PROMPT 
 
CREATE OR REPLACE FUNCTION  Days_Between (first_dt   DATE,
                                  second_dt  DATE)
RETURN NUMBER IS
 dt_one NUMBER;
 dt_two NUMBER;
BEGIN
 dt_one := TO_NUMBER(TO_CHAR(first_dt, 'DDD'));
 dt_two := TO_NUMBER(TO_CHAR(second_dt, 'DDD'));
 
 RETURN (dt_two - dt_one);
END Days_Between;
 
/
 
 
CREATE OR REPLACE FUNCTION Compute_Movie_Revenue (from_dt  DATE,
                           to_dt    DATE,
                           movie_id NUMBER)
RETURN NUMBER IS
  days     NUMBER;
  revenue     NUMBER := 0;
 
  CURSOR movie_rentals IS
    SELECT RETURN_DT, RENT_DT, DAILY_RATE
    FROM RENTALS
    WHERE RENT_DT >= from_dt AND
      RETURN_DT <= to_dt AND
      RENTALS.TAPE_ID IN (
        SELECT TAPES.TAPE_ID
        FROM TAPES
        WHERE TAPES.MOVIE_ID = movie_id);
BEGIN
  FOR rental IN movie_rentals LOOP
    days := Days_Between(rental.RENT_DT, rental.RETURN_DT);
    revenue := revenue + days * rental.DAILY_RATE;  
  END LOOP;
 
  RETURN revenue;
 
  EXCEPTION
    WHEN OTHERS THEN 
    RAISE_APPLICATION_ERROR(-20002,
                 'Movie revenue cannot be computed.');
END Compute_Movie_Revenue;
 
/
 
CREATE OR REPLACE FUNCTION Compute_Customer_Revenue (from_dt     DATE,
                              to_dt          DATE,
                              customer_id NUMBER)
RETURN NUMBER IS
  days     NUMBER;
  revenue     NUMBER := 0;
 
  CURSOR customer_rentals IS
    SELECT RETURN_DT, RENT_DT, DAILY_RATE
    FROM RENTALS
    WHERE RENT_DT >= from_dt AND
      RETURN_DT <= to_dt AND
      RENTALS.CUSTOMER_ID = customer_id;
BEGIN
  FOR rental IN customer_rentals LOOP
    days := Days_Between(rental.RETURN_DT, rental.RENT_DT);
    revenue := revenue + days * rental.DAILY_RATE;  
  END LOOP;
 
  RETURN revenue;
 
  EXCEPTION
    WHEN OTHERS THEN 
    RAISE_APPLICATION_ERROR(-20001,
      Customer revenue cannot be computed.');
END Compute_Customer_Revenue;
 
/
 
CREATE OR REPLACE FUNCTION Get_Sequence_Id RETURN NUMBER IS
  seq_id     NUMBER;
BEGIN
  SELECT MRD_SEQ.NEXTVAL
  INTO seq_id
  FROM DUAL;
 
  RETURN seq_id;
END;
 
/