CREATE OR REPLACE PACKAGE BODY admin.pkg_sequencing
IS
    -----
    -- Retrieve the next available ID for the supplied combination
    -- of Table Owner, Table Name, and Key ID
    -----
    FUNCTION next_id (
         aOwner     IN VARCHAR2
        ,aTableName IN VARCHAR2
        ,aKeyID     IN VARCHAR2
        )
      RETURN NUMBER
    IS
	  newvalue    NUMBER := 0;
    BEGIN
             
        UPDATE admin.next_ids
           SET value = value + 1
         WHERE owner = UPPER(aOwner)
           AND table_name = UPPER(aTableName)
           AND key_id = UPPER(aKeyID)
         RETURNING value 
		  INTO newvalue
        ;        
        COMMIT;
        RETURN newvalue;

    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            ROLLBACK;
            RETURN -1;
        WHEN OTHERS THEN
            ROLLBACK;
            RETURN -2;

    END next_id;

END;
/