help a newbie with stored procedures please

[gbo01]

I need to create a stored procedure from which I can pull the data into crystal 8.5. Am I on the right track? Any assistance is greatly appreciated.

CREATE OR REPLACE PACKAGE cnpkg_amxstr02
AS
TYPE get_cursor IS REF CURSOR;

PROCEDURE cnp_get_managers (
get_cursor IN OUT cnpkg_amxstr02.get_cursor,
p_manager_id OUT tp2.cnv_report_employees.ID%TYPE,
p_manager_no OUT tp2.cnv_report_employees.employee_no%TYPE,
p_manager OUT VARCHAR2,
p_section_id OUT tp2.cnv_report_employees.ss_no%TYPE,
p_manager_bu OUT tp2.tpv_pub_business_unit.name2%TYPE,
p_learner_id OUT tp2.cnv_report_employees.ID%TYPE,
p_learner_no OUT tp2.cnv_report_employees.employee_no%TYPE,
p_learner OUT VARCHAR2,
p_learner_bu OUT tp2.tpv_pub_business_unit.name2%TYPE,
p_sub_id OUT tp2.cnv_report_employees.ID%TYPE,
p_sub_no OUT tp2.cnv_report_employees.employee_no%TYPE,
p_sub OUT VARCHAR2,
p_sub_bu OUT tp2.tpv_pub_business_unit.name2%TYPE
);
END cnpkg_amxstr02;
/

/*FMT(25) NOT FORMATTED DUE TO ERROR !!! */
CREATE OR REPLACE PACKAGE BODY cnpkg_amxstr02 AS

PROCEDURE Cnp_Get_Managers (
get_cursor IN OUT cnpkg_AMXSTR02.get_cursor,
p_manager_id OUT tp2.cnv_report_employees.ID%TYPE,
p_manager_no OUT tp2.cnv_report_employees.employee_no%TYPE,
p_manager OUT VARCHAR2,
p_section_id OUT tp2.cnv_report_employees.SS_NO%TYPE,
p_manager_bu OUT tp2.tpv_pub_business_unit.name2%TYPE,
p_learner_id OUT tp2.cnv_report_employees.ID%TYPE ,
p_learner_no OUT tp2.cnv_report_employees.employee_no%TYPE,
p_learner OUT VARCHAR2,
p_learner_bu OUT tp2.tpv_pub_business_unit.name2%TYPE,
p_sub_id OUT tp2.cnv_report_employees.ID%TYPE,
p_sub_no OUT tp2.cnv_report_employees.employee_no%TYPE,
p_sub OUT VARCHAR2,
p_sub_bu OUT tp2.tpv_pub_business_unit.name2%TYPE)
IS
v_num NUMBER DEFAULT 0;
v_numsub NUMBER DEFAULT 0;

CURSOR cur_managers IS
SELECT DISTINCT
e.ID MANAGER_ID,
e.employee_no MANAGER_NO,
e.FNAME|| ' ' ||e.LNAME MANAGER,
e.SS_NO SECTION_ID,
b.NAME2 MANAGER_BU
FROM tp2.cnv_report_employees e,
tp2.tpv_pub_business_unit b
WHERE e.company_id = b.ID
AND e.ID IN (SELECT manager_id FROM tp2.cnv_report_employees);

CURSOR cur_learners (cv_manager_id IN VARCHAR2) IS
SELECT e.ID learner_id,
e.employee_no learner_no,
e.FNAME|| ' ' ||e.LNAME learner,
b.NAME2 learner_bu
FROM tp2.cnv_report_employees e,
tp2.tpv_pub_business_unit b
WHERE e.company_id = b.ID
AND e.manager_id = cv_manager_id
AND e.ID IN (SELECT manager_id FROM tp2.cnv_report_employees);

CURSOR cur_subs (cv_learner_id IN VARCHAR2) IS
SELECT e.ID sub_id,
e.employee_no sub_no,
e.FNAME|| ' ' ||e.LNAME sub,
b.NAME2 sub_bu
FROM tp2.cnv_report_employees e,
tp2.tpv_pub_business_unit b
WHERE e.company_id = b.ID
AND e.manager_id = cv_learner_id
AND e.ID IN (SELECT manager_id FROM tp2.cnv_report_employees);

BEGIN

FOR rec_manager IN cur_managers
LOOP
FOR rec_learner IN cur_learners(rec_manager.manager_id)
LOOP
SELECT COUNT(a.ID)
INTO v_num
FROM tp2.cnv_report_employees a
WHERE a.ID IN (SELECT DISTINCT b.student_id
FROM tp2.tpv_pub_ed_prod_completed b)
CONNECT BY PRIOR a.ID = a.manager_id
START WITH a.ID = rec_learner.learner_id;


IF v_num > 0 THEN
p_manager_id := rec_manager.manager_id;
p_manager_no := rec_manager.manager_no;
p_manager := rec_manager.manager;
p_section_id := rec_manager.section_id;
p_manager_bu := rec_manager.manager_bu;
p_learner_id := rec_learner.learner_id;
p_learner_no := rec_learner.learner_no;
p_learner := rec_learner.learner;
p_learner_bu := rec_learner.learner_bu;


FOR rec_sub IN cur_subs(rec_learner.learner_id)
LOOP
SELECT COUNT(a.ID)
INTO v_numsub
FROM tp2.cnv_report_employees a
WHERE a.ID IN (SELECT DISTINCT b.student_id
FROM tp2.tpv_pub_ed_prod_completed b)
CONNECT BY PRIOR a.ID = a.manager_id
START WITH a.ID = rec_sub.sub_id;


IF v_num > 0 THEN
p_sub_id := rec_sub.sub_id;
p_sub_no := rec_sub.sub_no;
p_sub := rec_sub.sub;
p_sub_bu := rec_sub.sub_bu;
INSERT INTO temp_table ( manager_id, manager_no, manager, section_id, manager_bu,
learner_id, learner_no, learner, learner_bu,
sub_id, sub_no, sub, sub_bu) VALUES
(p_manager_id, p_manager_no, p_manager, p_section_id, p_manager_bu,
p_learner_id, p_learner_no, p_learner, p_learner_bu,
p_sub_id, p_sub_no, p_sub, p_sub_bu);
END IF;
END IF;

END LOOP;
END LOOP;
END LOOP;
BEGIN
OPEN get_cursor FOR
SELECT *
FROM temp_table
END;
END;

 

[gbo01]

An explanation might be useful.

I need to list all managers, going down two levels. who have subordinates in their entire downline that taken a course.

 

[lyanch]

The last thing you need to do in your SP is to:

OPEN get_cursor for select * from Your temp table END

Then when the cursor is returned it contains the data. I didn't comletely go through your SP, but it looks like that is the part that is missing..

Lisa

 

[gbo01]

I have the open after I'm looping to gather the data. Is it placed incorrectly? Thank you for your assistance

 

[synapsevampire]

Perhaps Lisa means that you should do:

OPEN get_cursor FOR
SELECT *
FROM temp_table
END;

But it seems like it should work the way it is to me, though it's been over a year since I was on an Oracle box, are you getting an error, or?

-k

 

[gbo01]

It's not giving me an error code. just saying that crystal has a fatal error and needs to close.

 

[AnnetteH]

I haven't looked at all of your logic, but one error is immediately apparent. You can only have one OUT (or IN OUT) parameter and that has to be the reference cursor itself. Any other parameters have to be IN parameters.