Having trouble displaying a user approval route for all users and their approval limits
Al reports to Bill, Bill reports to Chris, Chris reports to Dean.
I am trying to show a report that summarizes all user ids.
The error is ORA-01422: exact fetch returns more than requested number of rows.
Using Oracle 8i.
--------------------------
Al - $0 limit
Bill - $10 limit
Chris - $20 limit
Dean - $40 limit
-----
Bill - $10 limit
Chris - $20 limit
Dean - $40 limit
----
Chris - $20 limit
Dean - $40 limit
----
Dean - $40 limit
-------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE APPROVAL_ROUTES
IS

CURSOR USERX
IS
SELECT USER_ID
FROM CREDIT_USER_PROFILE CUP, ADP_EMPLOYEES adp
WHERE CUP.USER_ID = ADP.FILE_NBR
ORDER BY ADP.LAST_NAME, ADP.FIRST_NAME;

CR CHAR(1) := chr(13);
v_user_id varchar2(40);
v_user varchar2(40);
v_approver_id varchar2(40);
v_approval_amt number;
v_dlevel varchar2(2);

BEGIN

DBMS_OUTPUT.ENABLE(1000000);

BEGIN
FOR ROUTE IN USERX
LOOP
SELECT ADP.FILE_NBR, ADP.LAST_NAME || ', ' || ADP.FIRST_NAME,
APPROVER_ID, CAL.AMOUNT, AC.DLEVEL
INTO
v_user_id,v_user, v_approver_id, v_approval_amt, v_dlevel
FROM
(SELECT USER_ID, APPROVER_ID, APPROVAL_LEVEL, LEVEL AS DLEVEL
FROM CREDIT_USER_PROFILE
START WITH user_id = ROUTE.USER_ID
CONNECT BY USER_ID = PRIOR APPROVER_ID) AC,
ADP_EMPLOYEES ADP,
CREDIT_APPROVAL_LEVEL CAL
WHERE ADP.FILE_NBR = AC.USER_ID
AND AC.APPROVAL_LEVEL = CAL.LEVEL_ID
ORDER BY AC.DLEVEL;

DBMS_OUTPUT.PUT_LINE( v_user_id || CR);
DBMS_OUTPUT.PUT_LINE( '==========================' || CR);

END LOOP;
END;
RETURN;
END APPROVAL_ROUTES;
/