1 PACKAGE BODY igs_pt_gen_pkg AS
2 /* $Header: IGSPT01B.pls 115.4 2003/12/11 16:03:12 amuthu noship $ */
3
4 -------------------------------------------------------------------------------
5 --Created by : msrinivi ( Oracle IDC)
6 --Date created: 29-Jun-2002
7 --
8 --Purpose:
9 --
10 --Known limitations/enhancements and/or remarks:
11 --
12 --Change History:
13 --Who When What
14 --stutta 27-NOV-2003 Modified call to igs_ss_enr_details.enrp_get_prgm_for_career
15 -- in function get_program_info by passing in two new parameters
16 -- term_cal_type, term_sequence_number. term record build 2829263
17 --amuthu 11-dec-2003 replaced IGS_SS_ENR_DETAILS1 with IGS_SS_ENR_DETAILS
18 ------------------------------------------------------------------------------
19 /*
20 This function returns the enrolled program info for a student in a term as a concat string.
21 This is used in portal where only the first 5 enrolled units are displayed
22 Career info sets to be set in the session so that when the user navigates
23 to the SS screen, the schedule is queried already.
24 The logic to find the pri prog for the first 5 enrolled units is as follows :
25
26 1. Select list of programs with units for the displayed term in the portlet
27 2. If single program
28 a. Always use it
29 else
30 b. If key program in the list
31 - Use it
32 else
33 - Use the program from the list with the latest commencement date
34 */
35
36 FUNCTION get_program_info(
37 p_person_id IN VARCHAR2,
38 p_load_cal_type IN VARCHAR2,
39 p_load_sequence_number IN VARCHAR2,
40 p_num_units IN NUMBER DEFAULT 5
41 ) RETURN VARCHAR2 AS
42
43 -- This cursor is to be used if the career_centric model is enabled
44
45 CURSOR c_enr_progs_in_prog_cntr IS
46 SELECT course_cd
47 FROM igs_en_su_attempt
48 WHERE person_id = p_person_id
49 AND unit_attempt_status = 'ENROLLED'
50 AND (cal_type,ci_sequence_number) IN
51 (
52 SELECT teach_cal_type,teach_ci_sequence_number FROM
53 igs_ca_load_to_teach_v where load_cal_type = p_load_cal_type
54 AND load_ci_sequence_number = p_load_sequence_number
55 )
56 AND rownum <= p_num_units
57 ORDER BY unit_cd;
58
59 -- This cursor is to be used if the career_centric model is enabled
60 -- It has the additional check to fetch enrolled units from Pri Prog
61
62 CURSOR c_enr_progs_in_car_cntr IS
63 SELECT course_cd
64 FROM igs_en_su_attempt
65 WHERE person_id = p_person_id
66 AND unit_attempt_status = 'ENROLLED'
67 AND (cal_type,ci_sequence_number) IN
68 (
69 SELECT teach_cal_type,teach_ci_sequence_number FROM
70 igs_ca_load_to_teach_v where load_cal_type = p_load_cal_type
71 AND load_ci_sequence_number = p_load_sequence_number
72 )
73 AND course_cd IN (
74 SELECT course_cd FROM igs_en_stdnt_ps_att
75 WHERE primary_program_type = 'PRIMARY'
76 AND person_id = p_person_id
77 AND course_attempt_status IN ('INACTIVE','ENROLLED')
78 )
79 AND rownum <= p_num_units
80 ORDER BY unit_cd;
81
82 CURSOR c_key_program(p_prog_cd VARCHAR2) IS
83 SELECT NVL(key_program,'N') , version_number, title
84 FROM igs_en_sca_v
85 WHERE COURSE_CD = p_prog_cd
86 AND PERSON_ID = p_person_id
87 ORDER BY commencement_dt DESC;
88
89 CURSOR c_career(p_prog_cd igs_ps_ver_all.course_cd%TYPE,p_ver_num igs_ps_ver_all.version_number%TYPE) IS
90 SELECT course_type
91 FROM igs_ps_ver_all
92 WHERE course_cd = p_prog_cd
93 AND version_number = p_ver_num ;
94
95
96 l_key_prog_cd igs_en_stdnt_ps_att_all.course_cd%TYPE;
97 l_key_prog_ind igs_en_stdnt_ps_att_all.key_program%TYPE DEFAULT 'N';
98 l_single_prog_exists BOOLEAN DEFAULT TRUE;
99 l_curr_prog_cd igs_en_stdnt_ps_att_all.course_cd%TYPE;
100
101 l_primary_program igs_en_stdnt_ps_att_all.course_cd%TYPE;
102 l_primary_program_version igs_en_stdnt_ps_att_all.version_number%TYPE;
103 l_programlist VARCHAR2(2000);
104
105 p_program_cd igs_en_stdnt_ps_att_all.course_cd%TYPE;
106 p_program_ver igs_en_stdnt_ps_att_all.version_number%TYPE;
107 p_career igs_ps_ver.course_type%TYPE;
108 p_program_list VARCHAR2(2000);
109 p_program_title VARCHAR2(200);
110 l_curr_prog_cd_tmp igs_en_stdnt_ps_att_all.course_cd%TYPE;
111
112 BEGIN
113
114 IF fnd_profile.value('CAREER_MODEL_ENABLED') = 'Y' THEN
115 FOR c_enr_progs_in_term_rec IN c_enr_progs_in_car_cntr LOOP -- If career centric model
116 EXIT WHEN c_enr_progs_in_car_cntr%NOTFOUND;
117
118 IF c_enr_progs_in_car_cntr%rowcount = 1 THEN
119 -- Storing the first prog in a var
120 l_curr_prog_cd := c_enr_progs_in_term_rec.course_cd;
121 END IF;
122
123 OPEN c_key_program(c_enr_progs_in_term_rec.course_cd);
124 FETCH c_key_program INTO l_key_prog_ind, p_program_ver,p_program_title;
125 IF l_key_prog_ind = 'Y' THEN
126 l_key_prog_cd := c_enr_progs_in_term_rec.course_cd;
127 END IF;
128 CLOSE c_key_program;
129 END LOOP;
130
131 ELSE -- If program centric model
132 FOR c_enr_progs_in_term_rec IN c_enr_progs_in_prog_cntr LOOP
133 EXIT WHEN c_enr_progs_in_prog_cntr%NOTFOUND;
134
135 IF c_enr_progs_in_prog_cntr%rowcount = 1 THEN
136 -- Storing the first prog in a var
137 l_curr_prog_cd := c_enr_progs_in_term_rec.course_cd;
138 END IF;
139
140 OPEN c_key_program(c_enr_progs_in_term_rec.course_cd);
141 FETCH c_key_program INTO l_key_prog_ind, p_program_ver,p_program_title;
142 IF l_key_prog_ind = 'Y' THEN
143 l_key_prog_cd := c_enr_progs_in_term_rec.course_cd;
144 END IF;
145 CLOSE c_key_program;
146 END LOOP;
147 END IF;
148
149 IF l_key_prog_cd IS NOT NULL THEN
150 p_program_cd := l_key_prog_cd;
151 ELSE
152 p_program_cd := l_curr_prog_cd;
153 END IF;
154
155 IF p_program_cd IS NOT NULL THEN
156 -- to get the program version number
157 OPEN c_key_program(p_program_cd);
158 FETCH c_key_program INTO l_curr_prog_cd_tmp, p_program_ver, p_program_title;
159 CLOSE c_key_program ;
160 END IF;
161
162 IF NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N') = 'Y' THEN
163
164 --To get career information
165 OPEN c_career(p_program_cd,p_program_ver);
166 FETCH c_career INTO p_career;
167 CLOSE c_career;
168
169 IF p_career IS NOT NULL THEN
170 IGS_SS_ENR_DETAILS.ENRP_GET_PRGM_FOR_CAREER(
171 p_primary_program => p_program_cd,
172 p_primary_program_version => p_program_ver,
173 p_programlist => p_program_list,
174 p_person_id => p_person_id,
175 p_carrer => p_career,
176 p_term_cal_type => p_load_cal_type,
177 p_term_sequence_number => p_load_sequence_number
178 );
179 END IF;
180 END IF;
181 RETURN NVL(p_program_cd,'') ||'*'|| NVL(p_program_ver,'') || '*'|| NVL(p_program_title,'') || '*'|| NVL(p_career,'') || '*'|| NVL(p_program_list,'') ;
182
183 END get_program_info;
184
185
186
187 END igs_pt_gen_pkg ;
[Home] [Help]