Another code snippet from my archive.
On a number of projects, we have implemented approvals for service requests based on the HR supervisor hierarchy, and occasionally during testing the approval notification ends up in an unexpected place. If you are using AME, then you can run the live test based on the service request ID to find out who AME expects to notify, but if you are using an SR workflow then this becomes a little more complex.
The code sample below takes the SR number as the start point, and from the primary contact walks up the HR supervisor hierarchy until there are no more supervisors found.
SET SERVEROUTPUT ON SIZE 1000000 DEFINE sr_number = &1 DECLARE -- CURSOR c_per_info ( cp_party_id IN NUMBER ) IS SELECT full_name FROM apps.per_people_x WHERE person_id = cp_party_id; -- CURSOR c_get_manager ( cp_party_id IN NUMBER ) IS SELECT paaf.supervisor_id FROM apps.per_all_assignments_f paaf JOIN apps.per_assignment_status_types past ON ( paaf.assignment_status_type_id = past.assignment_status_type_id ) WHERE paaf.person_id = cp_party_id AND paaf.effective_start_date <= SYSDATE AND NVL(paaf.effective_end_date, SYSDATE) >= SYSDATE AND paaf.primary_flag = 'Y'; -- CURSOR c_party_from_sr_number ( cp_sr_number IN NUMBER ) IS SELECT party_id FROM apps.cs_hz_sr_contact_points WHERE incident_id = ( SELECT incident_id FROM apps.cs_incidents_all_b WHERE incident_number = cp_sr_number ) AND primary_flag = 'Y'; -- v_start PLS_INTEGER; v_curr_person per_people_x.full_name%TYPE; v_next_person per_people_x.full_name%TYPE; v_check BOOLEAN; -- BEGIN -- v_check := TRUE; -- OPEN c_party_from_sr_number ( cp_sr_number => &sr_number ); FETCH c_party_from_sr_number INTO v_start; CLOSE c_party_from_sr_number; -- IF v_start IS NULL THEN DBMS_OUTPUT.PUT_LINE('No SR found for SR number &sr_number'); v_check := FALSE; END IF; -- IF v_check THEN WHILE v_start IS NOT NULL LOOP OPEN c_per_info ( cp_party_id => v_start ); FETCH c_per_info INTO v_curr_person; CLOSE c_per_info; -- OPEN c_get_manager ( cp_party_id => v_start ); FETCH c_get_manager INTO v_start; CLOSE c_get_manager; -- OPEN c_per_info ( cp_party_id => v_start ); FETCH c_per_info INTO v_next_person; CLOSE c_per_info; -- IF v_start IS NULL THEN DBMS_OUTPUT.PUT_LINE ( 'Current person ' ||v_curr_person ||' has no supervisor' ); ELSE DBMS_OUTPUT.PUT_LINE('Current person ' ||v_curr_person ||' is supervised by ' ||v_start ||' / '||v_next_person ); END IF; END LOOP; END IF; END; /