Procedure Error - SQL ORACLE
===

I am trying to create the procedure for this situation:

> Write a transaction that adds a movie to a customer’s queue so  that
> the newly added movie will be the next movie to a customer’s receives.



I have a table call movie_queue where all the requested movies from all users are recorded.
A sample of the table movie_queue is below:

      QUEUE_ID    USER_ID   MOVIE_ID MOVIE_REQUESTED_DATE            MOVIE_QUEUE_STATUS 
    ---------- ---------- ---------- ------------------------------- ------------------ ------------------- -------------------
         1          1          1 09-JUL-12 04.40.12.843000000 PM                  0                                         
         2          1          2 09-JUL-12 04.40.12.846000000 PM                  1                                         
         3          1          4 09-JUL-12 04.40.12.849000000 PM                  4                                         
         4          1          3 09-JUL-12 04.40.12.852000000 PM                  0                                         
         5          2          1 09-JUL-12 04.40.12.855000000 PM                  1                                         
         6          2          2 09-JUL-12 04.40.12.858000000 PM                  0     

                                    


status id numbers mean:
0 - requested,
1 - ready to ship,
2 - shipped,
3 - returned,
4 - deleted,

The trigger I wrote it is compiled “With warnings”. And even though it is compiled when I try to execute it, it shows some errors that I have not been able to find.

This is my procedure:

    CREATE OR REPLACE PROCEDURE add_movie_queue(
       queue_id_arg  in NUMBER,
       USER_ID_arg   in NUMBER,
       MOVIE_ID_arg  in NUMBER,
    )
    IS 
    DECLARE           
       STATUS_ID_arg    VARCHAR2;
       MEMBSHIP_ID_arg  NUMBER;
    BEGIN
       -- Find how many movies at a time can have the member, by 
       -- finding out his/her membership id (1 at a time, 2 at a time)
       MEMBSHIP_ID_arg:=  select * from (SELECT MEMBSHIP_ID
                          FROM user_payment WHERE user_id=USER_ID_arg order by paym_date desc) 
                          WHERE rownum=1;
    
       -- Find all movies on member's queue
       -- that have status requested, ready to ship or shipped
       CURSOR movies_inque is 
         select * from (SELECT * 
                          FROM movie_queue  WHERE user_id=user_id_arg) 
                          WHERE movie_queue_status=0 or movie_queue_status=1 or movie_queue_status=2;
    
       -- SET STATUS OF MOVIE (REQUESTED OR READY TO SEND)
       -- DEPENDING ON THE MEMBERSHIP TYPE AND THE NUMBER OF
       -- CURRENT MOVIES ON QUEUE
       IF MEMBSHIP_ID_arg = 1 --membership only allows 1 movie at a time
         IF movies_inque%ROWCOUNT >= 1 AND THEN
           STATUS_ID_arg:=0;   
         ELSE
           STATUS_ID_arg:=1;
         END IF;
    
       ELSIF MEMBSHIP_ID_arg = 2 --membership only allows 2 movies at a time
         IF movies_inque%ROWCOUNT >= 2 AND THEN
           STATUS_ID_arg:=0;   
         ELSE
           STATUS_ID_arg:=1;
         END IF;
    
       ELSE MEMBSHIP_ID_arg = 3 ----membership only allows 3 movie at a time
         IF movies_inque%ROWCOUNT >= 3 AND THEN
           STATUS_ID_arg:=0;   
         ELSE
           STATUS_ID_arg:=1;
         END IF;
       END IF;
    
    
    
       INSERT INTO movie_queue
        (QUEUE_ID, USER_ID, MOVIE_ID, MOVIE_REQUESTED_DATE, STATUS_ID)
        VALUES
        (QUEUE_ID_arg, USER_ID_arg, MOVIE_ID_arg, systimestamp, STATUS_ID_arg);
        
    END;
    /


Thank you very much for any suggestion!