1 PACKAGE IGS_PR_CLASS_RANK AUTHID CURRENT_USER AS
2 /* $Header: IGSPR37S.pls 115.3 2002/11/29 02:53:02 nsidana noship $ */
3
4 /****************************************************************************************************************
5 || Created By : DDEY
6 || Created On : 28-OCT-2002
7 || Purpose : This Job Rankes the students in a cohert or in an orginization
8 || This process can be called from the concurrent manager or the form "Class Rank Cohort".
9 || Known limitations, enhancements or remarks :
10 || Change History :
11 || Who When What
12 || (reverse chronological order - newest change first)
13 ****************************************************************************************************************/
14
15 FUNCTION rulp_val_senna_res (
16 p_person_id IN igs_en_sca_v.person_id%TYPE,
17 p_course_cd IN igs_en_sca_v.course_cd%TYPE ,
18 p_course_version IN igs_en_sca_v.version_number%TYPE,
19 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
20 p_unit_version IN igs_en_su_attempt.version_number%TYPE,
21 p_cal_type IN igs_en_su_attempt.cal_type%TYPE,
22 p_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
23 p_rule_number IN igs_ru_call_v.rul_sequence_number%TYPE) RETURN VARCHAR2;
24
25
26 FUNCTION get_cum_gpa (
27 p_person_id IN igs_en_sca_v.person_id%TYPE,
28 p_course_cd IN igs_en_sca_v.course_cd%TYPE,
29 p_cohort_name IN igs_pr_cohort.cohort_name%TYPE,
30 p_cal_type IN igs_ca_inst.cal_type%TYPE,
31 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
32 p_stat_type IN VARCHAR2,
33 p_cumulative_ind IN VARCHAR2
34 ) RETURN NUMBER ;
35
36
37 PROCEDURE run_ranking_process (
38 errbuf OUT NOCOPY VARCHAR2, -- Standard Error Buffer Variable
39 retcode OUT NOCOPY NUMBER, -- Standard Concurrent Return code
40 p_cohort_name IN VARCHAR2, -- The Cohart or the Student Group Name
41 p_cal_period IN VARCHAR2, -- The Calendar Period ie the concation of term Calendar Type and the sequence Number
42 p_org_unit_cd IN VARCHAR2 -- Org Unit Code
43 );
44
45 TYPE new_population_record_type IS RECORD (
46 p_person_id NUMBER(30),
47 p_course_cd VARCHAR2(6),
48 p_cohort_name VARCHAR2(30),
49 p_load_cal_type VARCHAR2(10),
50 p_load_ci_sequence_number NUMBER(6),
51 p_as_of_rank_gpa NUMBER,
52 p_cohort_rank NUMBER(15),
53 p_cohort_override_rank NUMBER(15),
54 p_comments VARCHAR2(240),
55 p_deletion_indicator VARCHAR2(2)
56 );
57
58
59
60 TYPE old_population_record_type IS RECORD (
61 p_rowid VARCHAR2(2000),
62 p_person_id NUMBER(30),
63 p_course_cd VARCHAR2(6),
64 p_cohort_name VARCHAR2(30),
65 p_load_cal_type VARCHAR2(10),
66 p_load_ci_sequence_number NUMBER(6),
67 p_as_of_rank_gpa NUMBER,
68 p_cohort_rank NUMBER(15),
69 p_cohort_override_rank NUMBER(15),
70 p_comments VARCHAR2(240),
71 p_deletion_indicator VARCHAR2(2)
72 );
73
74
75 new_population_record_rec new_population_record_type;
76 old_population_record_rec old_population_record_type;
77
78
79 -- Table type for the record type NEW_POPULATION_RECORD
80
81 TYPE l_new_population_table_type IS TABLE OF new_population_record_rec%TYPE INDEX BY BINARY_INTEGER;
82 l_new_population_table_rec l_new_population_table_type;
83
84 -- Table type for the record type OLD_POPULATION_RECORD
85
86 TYPE l_old_population_table_type IS TABLE OF old_population_record_rec%TYPE INDEX BY BINARY_INTEGER;
87 l_old_population_table_rec l_old_population_table_type;
88
89
90 PROCEDURE raise_clsrank_be_cr001 (p_cohort_name IN VARCHAR2,
91 p_cohort_instance IN VARCHAR2,
92 p_new_cohort_status IN VARCHAR2,
93 p_new_rank_status IN VARCHAR2);
94
95 PROCEDURE raise_clsrank_be_cr002 (p_person_id IN NUMBER,
96 p_person_number IN VARCHAR2,
97 p_person_name IN VARCHAR2,
98 p_current_rank IN NUMBER,
99 p_override_rank IN NUMBER,
100 p_ovrby_person_id IN NUMBER,
101 p_ovrby_person_number IN VARCHAR2,
102 p_ovrby_person_name IN VARCHAR2) ;
103
104
105
106 PROCEDURE get_formatted_rank (p_cohort_name IN VARCHAR2,
107 p_cal_type IN VARCHAR2,
108 p_ci_sequence_number IN NUMBER,
109 p_person_id IN NUMBER,
110 p_disp_type IN VARCHAR2, /* pass lookup code */
111 p_program_cd IN VARCHAR2,
112 x_formatted_rank OUT NOCOPY VARCHAR2,
113 x_return_status OUT NOCOPY VARCHAR2,
114 x_msg_count OUT NOCOPY NUMBER,
115 x_msg_data OUT NOCOPY VARCHAR2) ;
116
117
118 PROCEDURE raise_clsrank_be_cr003 (p_cohort_name IN VARCHAR2,
119 p_cohort_instance IN VARCHAR2,
120 p_run_date IN VARCHAR2,
121 p_cohort_total_students IN VARCHAR2) ;
122
123 FUNCTION get_formatted_rank (p_cohort_name IN VARCHAR2,
124 p_cal_type IN VARCHAR2,
125 p_ci_sequence_number IN NUMBER,
126 p_person_id IN NUMBER,
127 p_disp_type IN VARCHAR2,
128 p_program_cd IN VARCHAR2)
129 RETURN VARCHAR2;
130 END igs_pr_class_rank;