[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_GET_SCAEH
Source
1 PACKAGE BODY IGS_FI_GET_SCAEH AS
2 /* $Header: IGSFI06B.pls 115.5 2002/11/29 00:15:15 nsidana ship $ */
3 --
4 -- Routine to save SCA effective history data in a PL/SQL RECORD.
5 PROCEDURE FINP_GET_SCAEH(
6 p_person_id IN NUMBER ,
7 p_course_cd IN IGS_PS_COURSE.course_cd%TYPE ,
8 p_effective_dt IN DATE ,
9 p_data_found OUT NOCOPY BOOLEAN ,
10 p_scaeh_dtl IN OUT NOCOPY IGS_AS_SC_ATTEMPT_H_ALL%ROWTYPE )
11 AS
12 gv_other_detail VARCHAR2(255);
13 BEGIN
14 DECLARE
15 -- cursor to get the current student IGS_PS_COURSE attempt status
16 CURSOR c_sca IS
17 SELECT *
18 FROM IGS_EN_STDNT_PS_ATT sca
19 WHERE sca.person_id = p_person_id AND
20 sca.course_cd = p_course_cd AND
21 sca.logical_delete_dt IS NULL;
22 CURSOR c_scah (cp_effective_dt DATE) IS
23 SELECT *
24 FROM IGS_AS_SC_ATTEMPT_H scah
25 WHERE scah.person_id = p_person_id AND
26 scah.course_cd = p_course_cd AND
27 cp_effective_dt BETWEEN scah.hist_start_dt AND
28 scah.hist_end_dt;
29 r_sca IGS_EN_STDNT_PS_ATT%ROWTYPE;
30 r_scah IGS_AS_SC_ATTEMPT_H%ROWTYPE;
31 v_scah_found BOOLEAN;
32 -------------------------------------------------------------------------------
33 PROCEDURE finpl_ins_sca_rec(
34 p_hist_start_dt IGS_AS_SC_ATTEMPT_H.hist_start_dt%TYPE,
35 p_hist_end_dt IGS_AS_SC_ATTEMPT_H.hist_end_dt%TYPE,
36 p_sca_rec IGS_EN_STDNT_PS_ATT%ROWTYPE)
37 AS
38 BEGIN
39 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('IN finpl_ins_sca_rec');
40 gr_scaeh.r_scah.person_id := p_sca_rec.person_id;
41 gr_scaeh.r_scah.course_cd := p_sca_rec.course_cd;
42 gr_scaeh.r_scah.hist_start_dt := p_hist_start_dt;
43 gr_scaeh.r_scah.hist_end_dt := p_hist_end_dt;
44 gr_scaeh.r_scah.hist_who := p_sca_rec.LAST_UPDATED_BY;
45 gr_scaeh.r_scah.version_number := p_sca_rec.version_number;
46 gr_scaeh.r_scah.cal_type := p_sca_rec.cal_type;
47 gr_scaeh.r_scah.location_cd := p_sca_rec.location_cd;
48 gr_scaeh.r_scah.attendance_mode := p_sca_rec.attendance_mode;
49 gr_scaeh.r_scah.attendance_type := p_sca_rec.attendance_type;
50 gr_scaeh.r_scah.student_confirmed_ind := p_sca_rec.student_confirmed_ind;
51 gr_scaeh.r_scah.commencement_dt := p_sca_rec.commencement_dt;
52 gr_scaeh.r_scah.course_attempt_status := p_sca_rec.course_attempt_status;
53 gr_scaeh.r_scah.progression_status := p_sca_rec.progression_status;
54 gr_scaeh.r_scah.derived_att_type := p_sca_rec.derived_att_type;
55 gr_scaeh.r_scah.derived_att_mode := p_sca_rec.derived_att_mode;
56 gr_scaeh.r_scah.provisional_ind := p_sca_rec.provisional_ind;
57 gr_scaeh.r_scah.discontinued_dt := p_sca_rec.discontinued_dt;
58 gr_scaeh.r_scah.discontinuation_reason_cd :=
59 p_sca_rec.discontinuation_reason_cd ;
60 gr_scaeh.r_scah.lapsed_dt := p_sca_rec.lapsed_dt;
61 gr_scaeh.r_scah.funding_source := p_sca_rec.funding_source ;
62 -- gr_scaeh.r_scah.fs_description :=
63 gr_scaeh.r_scah.exam_location_cd := p_sca_rec.exam_location_cd;
64 -- gr_scaeh.r_scah.elo_description :=
65 gr_scaeh.r_scah.derived_completion_yr := p_sca_rec.derived_completion_yr;
66 gr_scaeh.r_scah.derived_completion_perd := p_sca_rec.derived_completion_perd;
67 gr_scaeh.r_scah.nominated_completion_yr := p_sca_rec.nominated_completion_yr;
68 gr_scaeh.r_scah.nominated_completion_perd :=
69 p_sca_rec.nominated_completion_perd;
70 gr_scaeh.r_scah.rule_check_ind := p_sca_rec.rule_check_ind;
71 gr_scaeh.r_scah.waive_option_check_ind := p_sca_rec.waive_option_check_ind;
72 gr_scaeh.r_scah.last_rule_check_dt := p_sca_rec.last_rule_check_dt;
73 gr_scaeh.r_scah.publish_outcomes_ind := p_sca_rec.publish_outcomes_ind;
74 gr_scaeh.r_scah.course_rqrmnt_complete_ind :=
75 p_sca_rec.course_rqrmnt_complete_ind;
76 gr_scaeh.r_scah.course_rqrmnts_complete_dt :=
77 p_sca_rec.course_rqrmnts_complete_dt;
78 gr_scaeh.r_scah.s_completed_source_type := p_sca_rec.s_completed_source_type;
79 gr_scaeh.r_scah.override_time_limitation :=
80 p_sca_rec.override_time_limitation;
81 gr_scaeh.r_scah.advanced_standing_ind := p_sca_rec.advanced_standing_ind;
82 gr_scaeh.r_scah.fee_cat:= p_sca_rec.fee_cat;
83 gr_scaeh.r_scah.correspondence_cat:= p_sca_rec.correspondence_cat;
84 gr_scaeh.r_scah.self_help_group_ind := p_sca_rec.self_help_group_ind;
85 gr_scaeh.r_scah.adm_admission_appl_number :=
86 p_sca_rec.adm_admission_appl_number;
87 gr_scaeh.r_scah.adm_nominated_course_cd := p_sca_rec.adm_nominated_course_cd;
88 gr_scaeh.r_scah.adm_sequence_number := p_sca_rec.adm_sequence_number;
89 gr_scaeh.r_scah.LAST_UPDATED_BY := p_sca_rec.LAST_UPDATED_BY;
90 gr_scaeh.r_scah.LAST_UPDATE_DATE := p_sca_rec.LAST_UPDATE_DATE;
91 p_data_found := TRUE;
92 p_scaeh_dtl := gr_scaeh.r_scah;
93 EXCEPTION
94 WHEN OTHERS THEN
95 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
96 Fnd_Message.Set_Token('NAME','IGS_FI_GET_SCAEH.FINPL_INS_SCA_REC');
97 IGS_GE_MSG_STACK.ADD;
98 APP_EXCEPTION.RAISE_EXCEPTION;
99 END finpl_ins_sca_rec;
100 -------------------------------------------------------------------------------
101 PROCEDURE finpl_ins_scah_rec (
102 p_scah_rec IGS_AS_SC_ATTEMPT_H%ROWTYPE,
103 p_sca_rec IGS_EN_STDNT_PS_ATT%ROWTYPE)
104 AS
105 BEGIN
106 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('IN finpl_ins_scah_rec');
107 gr_scaeh.r_scah.person_id := p_scah_rec.person_id;
108 gr_scaeh.r_scah.course_cd := p_scah_rec.course_cd;
109 gr_scaeh.r_scah.hist_start_dt := p_scah_rec.hist_start_dt;
110 gr_scaeh.r_scah.hist_end_dt := p_scah_rec.hist_end_dt;
111 gr_scaeh.r_scah.hist_who := p_scah_rec.hist_who;
112 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL1');
113 gr_scaeh.r_scah.version_number :=
114 NVL(p_scah_rec.version_number,
115 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('VERSION_NUMBER',
116 p_scah_rec.person_id,
117 p_scah_rec.course_cd,
118 p_scah_rec.hist_end_dt)),
119 p_sca_rec.version_number));
120 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL1');
121 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL2');
122 gr_scaeh.r_scah.cal_type:=
123 NVL(p_scah_rec.cal_type,
124 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('CAL_TYPE',
125 p_scah_rec.person_id,
126 p_scah_rec.course_cd,
127 p_scah_rec.hist_end_dt),
128 p_scah_rec.cal_type));
129 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL2');
130 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL3');
131 gr_scaeh.r_scah.location_cd :=
132 NVL(p_scah_rec.location_cd,
133 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('LOCATION_CD',
134 p_scah_rec.person_id,
135 p_scah_rec.course_cd,
136 p_scah_rec.hist_end_dt),
137 p_sca_rec.location_cd));
138 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL3');
139 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL4');
140 gr_scaeh.r_scah.attendance_mode :=
141 NVL(p_scah_rec.attendance_mode,
142 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ATTENDANCE_MODE',
143 p_scah_rec.person_id,
144 p_scah_rec.course_cd,
145 p_scah_rec.hist_end_dt),
146 p_sca_rec.ATTENDANCE_MODE));
147 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL4');
148 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL5');
149 gr_scaeh.r_scah.attendance_type :=
150 NVL(p_scah_rec.attendance_type,
151 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ATTENDANCE_TYPE',
152 p_scah_rec.person_id,
153 p_scah_rec.course_cd,
154 p_scah_rec.hist_end_dt),
155 p_sca_rec.attendance_mode));
156 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL5');
157 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL6');
158 gr_scaeh.r_scah.student_confirmed_ind :=
159 NVL(p_scah_rec.student_confirmed_ind,
160 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('STUDENT_CONFIRMED_IND',
161 p_scah_rec.person_id,
162 p_scah_rec.course_cd,
163 p_scah_rec.hist_end_dt),
164 p_sca_rec.student_confirmed_ind));
165 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL6');
166 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL7');
167 gr_scaeh.r_scah.commencement_dt :=
168 NVL(p_scah_rec.commencement_dt,
169 NVL(igs_ge_date.igsdate(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COMMENCEMENT_DT',
170 p_scah_rec.person_id,
171 p_scah_rec.course_cd,
172 p_scah_rec.hist_end_dt)),
173 p_sca_rec.commencement_dt));
174 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL7');
175 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL8');
176 gr_scaeh.r_scah.course_attempt_status :=
177 NVL(p_scah_rec.course_attempt_status,
178 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COURSE_ATTEMPT_STATUS',
179 p_scah_rec.person_id,
180 p_scah_rec.course_cd,
181 p_scah_rec.hist_end_dt),
182 p_sca_rec.course_attempt_status));
183 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL8');
184 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL9');
185 gr_scaeh.r_scah.progression_status :=
186 NVL(p_scah_rec.progression_status,
187 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('PROGRESSION_STATUS',
188 p_scah_rec.person_id,
189 p_scah_rec.course_cd,
190 p_scah_rec.hist_end_dt),
191 p_sca_rec.progression_status));
192 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL9');
193 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL10');
194 gr_scaeh.r_scah.derived_att_type :=
195 NVL(p_scah_rec.derived_att_type,
196 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DERIVED_ATT_TYPE',
197 p_scah_rec.person_id,
198 p_scah_rec.course_cd,
199 p_scah_rec.hist_end_dt),
200 p_sca_rec.derived_att_type));
201 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL10');
202 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL11');
203 gr_scaeh.r_scah.derived_att_mode :=
204 NVL(p_scah_rec.derived_att_mode,
205 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DERIVED_ATT_MODE',
206 p_scah_rec.person_id,
207 p_scah_rec.course_cd,
208 p_scah_rec.hist_end_dt),
209 p_sca_rec.derived_att_mode));
210 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL11');
211 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL12');
212 gr_scaeh.r_scah.provisional_ind :=
213 NVL(p_scah_rec.provisional_ind,
214 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('PROVISIONAL_IND',
215 p_scah_rec.person_id,
216 p_scah_rec.course_cd,
217 p_scah_rec.hist_end_dt),
218 p_sca_rec.provisional_ind));
219 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL12');
220 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL13');
221 gr_scaeh.r_scah.discontinued_dt :=
222 NVL(p_scah_rec.discontinued_dt,
223 NVL(igs_ge_date.igsdate(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DISCONTINUED_DT',
224 p_scah_rec.person_id,
225 p_scah_rec.course_cd,
226 p_scah_rec.hist_end_dt)),
227 p_sca_rec.discontinued_dt));
228 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL13');
229 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL14');
230 gr_scaeh.r_scah.discontinuation_reason_cd :=
231 NVL(p_scah_rec.discontinuation_reason_cd,
232 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DISCONTINUATION_REASON_CD',
233 p_scah_rec.person_id,
234 p_scah_rec.course_cd,
235 p_scah_rec.hist_end_dt),
236 p_sca_rec.discontinuation_reason_cd));
237 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL14');
238 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL15');
239 gr_scaeh.r_scah.lapsed_dt :=
240 NVL(p_scah_rec.lapsed_dt,
241 NVL(igs_ge_date.igsdate(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('LAPSED_DT',
242 p_scah_rec.person_id,
243 p_scah_rec.course_cd,
244 p_scah_rec.hist_end_dt)),
245 p_sca_rec.lapsed_dt));
246 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL15');
247 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL16');
248 gr_scaeh.r_scah.funding_source :=
249 NVL(p_scah_rec.funding_source,
250 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('FUNDING_SOURCE',
251 p_scah_rec.person_id,
252 p_scah_rec.course_cd,
253 p_scah_rec.hist_end_dt),
254 p_sca_rec.funding_source));
255 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL16');
256 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL17');
257 gr_scaeh.r_scah.exam_location_cd :=
258 NVL(p_scah_rec.exam_location_cd,
259 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('EXAM_LOCATION_CD',
260 p_scah_rec.person_id,
261 p_scah_rec.course_cd,
262 p_scah_rec.hist_end_dt),
263 p_sca_rec.exam_location_cd));
264 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL17');
265 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL18');
266 gr_scaeh.r_scah.derived_completion_yr :=
267 NVL(p_scah_rec.derived_completion_yr,
268 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DERIVED_COMPLETION_YR',
269 p_scah_rec.person_id,
270 p_scah_rec.course_cd,
271 p_scah_rec.hist_end_dt)),
272 p_sca_rec.derived_completion_yr));
273 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL18');
274 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL19');
275 gr_scaeh.r_scah.derived_completion_perd :=
276 NVL(p_scah_rec.derived_completion_perd,
277 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('DERIVED_COMPLETION_PERD',
278 p_scah_rec.person_id,
279 p_scah_rec.course_cd,
280 p_scah_rec.hist_end_dt),
281 p_sca_rec.derived_completion_perd));
282 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL19');
283 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL20');
284 gr_scaeh.r_scah.nominated_completion_yr :=
285 NVL(p_scah_rec.nominated_completion_yr,
286 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('NOMINATED_COMPLETION_YR',
287 p_scah_rec.person_id,
288 p_scah_rec.course_cd,
289 p_scah_rec.hist_end_dt)),
290 p_sca_rec.nominated_completion_yr));
291 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL20');
292 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL21');
293 gr_scaeh.r_scah.nominated_completion_perd :=
294 NVL(p_scah_rec.nominated_completion_perd,
295 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('NOMINATED_COMPLETION_PERD',
296 p_scah_rec.person_id,
297 p_scah_rec.course_cd,
298 p_scah_rec.hist_end_dt),
299 p_sca_rec.nominated_completion_perd));
300 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL21');
301 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL22');
302 gr_scaeh.r_scah.rule_check_ind :=
303 NVL(p_scah_rec.rule_check_ind,
304 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('RULE_CHECK_IND',
305 p_scah_rec.person_id,
306 p_scah_rec.course_cd,
307 p_scah_rec.hist_end_dt),
308 p_sca_rec.rule_check_ind));
309 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL22');
310 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL23');
311 gr_scaeh.r_scah.waive_option_check_ind :=
312 NVL(p_scah_rec.waive_option_check_ind,
313 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('WAIVE_OPTION_CHECK_IND',
314 p_scah_rec.person_id,
315 p_scah_rec.course_cd,
316 p_scah_rec.hist_end_dt),
317 p_sca_rec.waive_option_check_ind));
318 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL23');
319 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL24');
320 gr_scaeh.r_scah.last_rule_check_dt :=
321 NVL(p_scah_rec.last_rule_check_dt,
322 NVL(igs_ge_date.igsdate(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('LAST_RUL_CHECK_DT',
323 p_scah_rec.person_id,
324 p_scah_rec.course_cd,
325 p_scah_rec.hist_end_dt)),
326 p_sca_rec.last_rule_check_dt));
327 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL24');
328 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL25');
329 gr_scaeh.r_scah.publish_outcomes_ind :=
330 NVL(p_scah_rec.publish_outcomes_ind,
331 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('PUBLISH_OUTCOMES_IND',
332 p_scah_rec.person_id,
333 p_scah_rec.course_cd,
334 p_scah_rec.hist_end_dt),
335 p_sca_rec.publish_outcomes_ind));
336 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL25');
337 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL26');
338 gr_scaeh.r_scah.course_rqrmnt_complete_ind :=
339 NVL(p_scah_rec.course_rqrmnt_complete_ind,
340 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COURSE_RQRMNT_COMPLETE_IND',
341 p_scah_rec.person_id,
342 p_scah_rec.course_cd,
343 p_scah_rec.hist_end_dt),
344 p_sca_rec.course_rqrmnt_complete_ind));
345 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL26');
346 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL27');
347 gr_scaeh.r_scah.course_rqrmnts_complete_dt :=
348 NVL(p_scah_rec.course_rqrmnts_complete_dt,
349 NVL(igs_ge_date.igsdate(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COURSE_RQRMNTS_COMPLETE_DT',
350 p_scah_rec.person_id,
351 p_scah_rec.course_cd,
352 p_scah_rec.hist_end_dt)),
353 p_sca_rec.course_rqrmnts_complete_dt));
354 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL27');
355 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL28');
356 gr_scaeh.r_scah.s_completed_source_type :=
357 NVL(p_scah_rec.s_completed_source_type,
358 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('S_COMPLETED_SOURCE_TYPE',
359 p_scah_rec.person_id,
360 p_scah_rec.course_cd,
361 p_scah_rec.hist_end_dt),
362 p_sca_rec.s_completed_source_type));
363 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL28');
364 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL29');
365 gr_scaeh.r_scah.override_time_limitation :=
366 NVL(p_scah_rec.override_time_limitation,
367 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('OVERRIDE_TIME_LIMITATION',
368 p_scah_rec.person_id,
369 p_scah_rec.course_cd,
370 p_scah_rec.hist_end_dt)),
371 p_sca_rec.override_time_limitation));
372 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL29');
373 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL30');
374 gr_scaeh.r_scah.advanced_standing_ind :=
375 NVL(p_scah_rec.advanced_standing_ind,
376 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ADVANCED_STANDING_IND',
377 p_scah_rec.person_id,
378 p_scah_rec.course_cd,
379 p_scah_rec.hist_end_dt),
380 p_sca_rec.advanced_standing_ind));
381 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL30');
382 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL31');
383 gr_scaeh.r_scah.fee_cat :=
384 NVL(p_scah_rec.fee_cat,
385 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('FEE_CAT',
386 p_scah_rec.person_id,
387 p_scah_rec.course_cd,
388 p_scah_rec.hist_end_dt),
389 p_sca_rec.fee_cat));
390 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL31');
391 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL32');
392 gr_scaeh.r_scah.correspondence_cat:=
393 NVL(p_scah_rec.correspondence_cat,
394 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('CORRESPONDENCE_CAT',
395 p_scah_rec.person_id,
396 p_scah_rec.course_cd,
397 p_scah_rec.hist_end_dt),
398 p_sca_rec.correspondence_cat));
399 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL33');
400 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL34');
401 --gr_scaeh.r_scah.cc_description :=
402 gr_scaeh.r_scah.self_help_group_ind :=
403 NVL(p_scah_rec.self_help_group_ind,
404 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('SELF_HELP_GROUP_IND',
405 p_scah_rec.person_id,
406 p_scah_rec.course_cd,
407 p_scah_rec.hist_end_dt),
408 p_sca_rec.self_help_group_ind));
409 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL34');
410 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL35');
411 gr_scaeh.r_scah.adm_admission_appl_number :=
412 NVL(p_scah_rec.adm_admission_appl_number,
413 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ADM_ADMISSION_APPL_NUMBER',
414 p_scah_rec.person_id,
415 p_scah_rec.course_cd,
416 p_scah_rec.hist_end_dt)),
417 p_sca_rec.adm_admission_appl_number));
418 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL35');
419 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL36');
420 gr_scaeh.r_scah.adm_nominated_course_cd :=
421 NVL(p_scah_rec.adm_nominated_course_cd,
422 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ADM_NOMINATED_COURSE_CD',
423 p_scah_rec.person_id,
424 p_scah_rec.course_cd,
425 p_scah_rec.hist_end_dt),
426 p_sca_rec.adm_nominated_course_cd));
427 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL36');
428 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL37');
429 gr_scaeh.r_scah.adm_sequence_number :=
430 NVL(p_scah_rec.adm_sequence_number,
431 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('ADM_SEQUENCE_NUMBER',
432 p_scah_rec.person_id,
433 p_scah_rec.course_cd,
434 p_scah_rec.hist_end_dt)),
435 p_sca_rec.adm_sequence_number));
436 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL37');
437 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL38');
438 gr_scaeh.r_scah.LAST_UPDATED_BY := p_scah_rec.LAST_UPDATED_BY;
439 gr_scaeh.r_scah.LAST_UPDATE_DATE := p_scah_rec.LAST_UPDATE_DATE;
440 p_data_found := TRUE;
441 p_scaeh_dtl := gr_scaeh.r_scah;
442 EXCEPTION
443 WHEN OTHERS THEN
444 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
445 Fnd_Message.Set_Token('NAME','IGS_FI_GET_SCAEH.FINPL_INS_SCAH_REC');
446 IGS_GE_MSG_STACK.ADD;
447 APP_EXCEPTION.RAISE_EXCEPTION;
448 END finpl_ins_scah_rec;
449 -------------------------------------------------------------------------------
450 PROCEDURE finpl_ins_scahv_rec (
451 p_hist_start_dt IGS_AS_SC_ATTEMPT_H.hist_start_dt%TYPE,
452 p_scahv_rec IGS_AS_SCA_H_V%ROWTYPE)
453 AS
454 BEGIN
455 gr_scaeh.r_scah.person_id := p_scahv_rec.person_id;
456 gr_scaeh.r_scah.course_cd := p_scahv_rec.course_cd;
457 gr_scaeh.r_scah.hist_start_dt := p_hist_start_dt;
458 gr_scaeh.r_scah.hist_end_dt := p_scahv_rec.hist_end_dt;
459 gr_scaeh.r_scah.hist_who := p_scahv_rec.hist_who;
460 gr_scaeh.r_scah.version_number := p_scahv_rec.version_number;
461 gr_scaeh.r_scah.cal_type := p_scahv_rec.cal_type;
462 gr_scaeh.r_scah.location_cd := p_scahv_rec.location_cd;
463 gr_scaeh.r_scah.attendance_mode := p_scahv_rec.attendance_mode;
464 gr_scaeh.r_scah.attendance_type := p_scahv_rec.attendance_type;
465 gr_scaeh.r_scah.student_confirmed_ind := p_scahv_rec.student_confirmed_ind;
466 gr_scaeh.r_scah.commencement_dt := p_scahv_rec.commencement_dt;
467 gr_scaeh.r_scah.course_attempt_status := p_scahv_rec.course_attempt_status;
468 gr_scaeh.r_scah.progression_status := p_scahv_rec.progression_status;
469 gr_scaeh.r_scah.derived_att_type := p_scahv_rec.derived_att_type;
470 gr_scaeh.r_scah.derived_att_mode := p_scahv_rec.derived_att_mode;
471 gr_scaeh.r_scah.provisional_ind := p_scahv_rec.provisional_ind;
472 gr_scaeh.r_scah.discontinued_dt := p_scahv_rec.discontinued_dt;
473 gr_scaeh.r_scah.discontinuation_reason_cd :=
474 p_scahv_rec.discontinuation_reason_cd;
475 gr_scaeh.r_scah.lapsed_dt := p_scahv_rec.lapsed_dt;
476 gr_scaeh.r_scah.funding_source:= p_scahv_rec.funding_source;
477 gr_scaeh.r_scah.exam_location_cd := p_scahv_rec.exam_location_cd;
478 gr_scaeh.r_scah.derived_completion_yr := p_scahv_rec.derived_completion_yr;
479 gr_scaeh.r_scah.derived_completion_perd :=
480 p_scahv_rec.derived_completion_perd;
481 gr_scaeh.r_scah.nominated_completion_yr :=
482 p_scahv_rec.nominated_completion_yr;
483 gr_scaeh.r_scah.nominated_completion_perd :=
484 p_scahv_rec.nominated_completion_perd;
485 gr_scaeh.r_scah.rule_check_ind := p_scahv_rec.rule_check_ind;
486 gr_scaeh.r_scah.waive_option_check_ind := p_scahv_rec.waive_option_check_ind;
487 gr_scaeh.r_scah.last_rule_check_dt := p_scahv_rec.last_rule_check_dt;
488 gr_scaeh.r_scah.publish_outcomes_ind := p_scahv_rec.publish_outcomes_ind;
489 gr_scaeh.r_scah.course_rqrmnt_complete_ind :=
490 p_scahv_rec.course_rqrmnt_complete_ind;
491 gr_scaeh.r_scah.course_rqrmnts_complete_dt :=
492 p_scahv_rec.course_rqrmnts_complete_dt;
493 gr_scaeh.r_scah.s_completed_source_type :=
494 p_scahv_rec.s_completed_source_type;
495 gr_scaeh.r_scah.override_time_limitation :=
496 p_scahv_rec.override_time_limitation;
497 gr_scaeh.r_scah.advanced_standing_ind := p_scahv_rec.advanced_standing_ind;
498 gr_scaeh.r_scah.fee_cat := p_scahv_rec.fee_cat;
499 -- gr_scaeh.r_scah.fc_description :=
500 gr_scaeh.r_scah.correspondence_cat:= p_scahv_rec.correspondence_cat;
501 --gr_scaeh.r_scah.cc_description :=
502 gr_scaeh.r_scah.self_help_group_ind := p_scahv_rec.self_help_group_ind;
503 gr_scaeh.r_scah.adm_admission_appl_number :=
504 p_scahv_rec.adm_admission_appl_number;
505 gr_scaeh.r_scah.adm_nominated_course_cd :=
506 p_scahv_rec.adm_nominated_course_cd;
507 gr_scaeh.r_scah.adm_sequence_number := p_scahv_rec.adm_sequence_number;
508 gr_scaeh.r_scah.LAST_UPDATED_BY := p_scahv_rec.LAST_UPDATED_BY;
509 gr_scaeh.r_scah.LAST_UPDATE_DATE := p_scahv_rec.LAST_UPDATE_DATE;
510 p_data_found := TRUE;
511 p_scaeh_dtl := gr_scaeh.r_scah;
512 EXCEPTION
513 WHEN OTHERS THEN
514 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
515 Fnd_Message.Set_Token('NAME','IGS_FI_GET_SCAEH.FINPL_INS_SCAHV_REC');
516 IGS_GE_MSG_STACK.ADD;
517 APP_EXCEPTION.RAISE_EXCEPTION;
518 END finpl_ins_scahv_rec;
519 -------------------------------------------------------------------------------
520 PROCEDURE finpl_prc_sca_enrhist(
521 p_sca_rec IN OUT NOCOPY IGS_EN_STDNT_PS_ATT%ROWTYPE)
522 AS
523 BEGIN
524 DECLARE
525 CURSOR c_scahv_last_enr (cp_effective_dt DATE) IS
526 SELECT *
527 FROM IGS_AS_SCA_H_V scahv
528 WHERE scahv.person_id = p_person_id AND
529 scahv.course_cd = p_course_cd AND
530 scahv.course_attempt_status = 'ENROLLED' AND
531 cp_effective_dt <= scahv.hist_start_dt
532 ORDER BY scahv.hist_start_dt desc;
533 r_scahv IGS_AS_SCA_H_V%ROWTYPE;
534 BEGIN
535 -- check the last enrolled history for a match
536 OPEN c_scahv_last_enr(p_sca_rec.commencement_dt);
537 FETCH c_scahv_last_enr INTO r_scahv;
538 IF (c_scahv_last_enr%FOUND) THEN
539 CLOSE c_scahv_last_enr;
540 IF TRUNC(gv_effective_dt) <= TRUNC(r_scahv.hist_end_dt) THEN
541 -- save the sca history data when last enrolled
542 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_scahv_rec');
543 finpl_ins_scahv_rec(
544 p_sca_rec.commencement_dt,
545 r_scahv);
546 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_scahv_rec');
547 ELSE
548 -- save the current student IGS_PS_UNIT attempt data
549 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
550 finpl_ins_sca_rec(
551 r_scahv.hist_end_dt,
552 SYSDATE,
553 p_sca_rec);
554 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
555 END IF;
556 ELSE -- missing enrolment history
557 CLOSE c_scahv_last_enr;
558 -- check if the effective date falls within the period
559 -- of the current student IGS_PS_UNIT attempt values
560 IF TRUNC(gv_effective_dt) >= TRUNC(p_sca_rec.LAST_UPDATE_DATE) THEN
561 -- save the current sca data
562 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
563 finpl_ins_sca_rec(
564 p_sca_rec.LAST_UPDATE_DATE,
565 SYSDATE,
566 p_sca_rec);
567 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
568 ELSE
569 -- simulate an enrolment history
570 p_sca_rec.course_attempt_status := 'ENROLLED';
571 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
572 finpl_ins_sca_rec(
573 p_sca_rec.commencement_dt,
574 p_sca_rec.LAST_UPDATE_DATE,
575 p_sca_rec);
576 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
577 END IF;
578 END IF;
579 END;
580 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('out finpl_prc_sca_enrhist');
581 EXCEPTION
582 WHEN OTHERS THEN
583 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
584 Fnd_Message.Set_Token('NAME','IGS_FI_GET_SCAEH.FINPL_PRC_SCA_ENRHIST');
585 IGS_GE_MSG_STACK.ADD;
586 APP_EXCEPTION.RAISE_EXCEPTION;
587 END finpl_prc_sca_enrhist;
588 -------------------------------------------------------------------------------
589 BEGIN -- finp_get_scaeh
590 -- effective history logic is based upon the following assumptions -
591 -- the transitions between IGS_PS_COURSE status's are;
592 -- UNCONFIRM -> ENROLLED
593 -- -> DELETED
594 -- -> INACTIVE
595 --
596 -- DELETED -> UNCONFIRM
597 --
598 -- ENROLLED -> UNCONFIRM
599 -- -> DISCONTIN
600 -- -> LAPSED
601 -- -> INACTIVE
602 -- -> INTERMIT
603 -- -> COMPLETED
604 --
605 -- LAPSED -> ENROLLED
606 -- -> DISCONTIN
607 -- -> INACTIVE
608 --
609 -- INACTIVE -> UNCONFIRM
610 -- -> ENROLLED
611 -- -> DISCONTIN
612 -- -> COMPLETED
613 -- -> INTERMIT
614 -- -> LAPSED
615 --
616 -- INTERMIT -> ENROLLED
617 -- -> DISCONTIN
618 -- -> COMPLETED
619 -- -> INACTIVE
620 --
621 -- DISCONTIN -> ENROLLED
622 -- -> LAPSED
623 -- -> INACTIVE
624 -- -> INTERMIT
625 --
626 -- COMPLETE -> ENROLLED
627 -- -> INACTIVE
628 --
629 -- the effective history transitions are
630 -- UNCONFIRM -> ENROLLED -> INTERMIT -> ENROLLED -> COMPLETED
631 -- UNCONFIRM -> ENROLLED -> DISCONTIN
632 -- check parameters
633 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('in procedure : IGS_FI_GET_SCAEH.FINP_GET_SCAEH');
634 IF p_person_id IS NULL OR
635 p_course_cd IS NULL OR
636 p_effective_dt IS NULL THEN
637 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
638 IGS_GE_MSG_STACK.ADD;
639 App_Exception.Raise_Exception;
640 END IF;
641 -- check if the effective history has already been captured
642 IF gv_person_id IS NOT NULL AND
643 gv_course_cd IS NOT NULL AND
644 gv_effective_dt IS NOT NULL THEN
645 IF gv_person_id = p_person_id AND
646 gv_course_cd = p_course_cd AND
647 TRUNC(gv_effective_dt) = TRUNC(p_effective_dt) THEN
648 p_data_found := TRUE;
649 p_scaeh_dtl := gr_scaeh.r_scah;
650 RETURN;
651 END IF;
652 END IF;
653 gv_person_id := p_person_id;
654 gv_course_cd := p_course_cd;
655 gv_effective_dt :=igs_ge_date.igsdate(igs_ge_date.igschar(p_effective_dt)|| '23:59:59');
656 p_data_found := FALSE;
657 -- get the current student IGS_PS_COURSE attempt details
658 OPEN c_sca;
659 FETCH c_sca INTO r_sca;
660 IF (c_sca%NOTFOUND) THEN
661 CLOSE c_sca;
662 RETURN;
663 END IF;
664 CLOSE c_sca;
665 -- check if effective date is set today or into the future
666 IF TRUNC(gv_effective_dt) >= TRUNC(SYSDATE) THEN
667 IF r_sca.course_attempt_status IN (
668 'COMPLETED',
669 'ENROLLED',
670 'INTERMIT',
671 'LAPSED',
672 'INACTIVE') THEN
673 -- check if commencing on or before the effective date
674 IF TRUNC(r_sca.commencement_dt) <= TRUNC(gv_effective_dt) THEN
675 -- save the current student IGS_PS_COURSE attempt data
676 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
677 finpl_ins_sca_rec(
678 r_sca.commencement_dt,
679 gv_effective_dt,
680 r_sca);
681 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
682 ELSE
683 -- save the current student IGS_PS_COURSE attempt data as an
684 -- unconfirmed history
685 r_sca.course_attempt_status := 'UNCONFIRM';
686 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
687 finpl_ins_sca_rec(
688 gv_effective_dt,
689 r_sca.commencement_dt,
690 r_sca);
691 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
692 END IF;
693 ELSIF r_sca.course_attempt_status = 'DISCONTIN' THEN
694 -- save the current student IGS_PS_COURSE attempt data
695 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
696 finpl_ins_sca_rec(
697 r_sca.discontinued_dt,
698 gv_effective_dt,
699 r_sca);
700 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
701 ELSIF r_sca.course_attempt_status = 'UNCONFIRM' THEN
702 -- save the current student IGS_PS_COURSE attempt data
703 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
704 finpl_ins_sca_rec(
705 r_sca.LAST_UPDATE_DATE,
706 gv_effective_dt,
707 r_sca);
708 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
709 ELSE -- unrecognised status
710 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UNRECOG_SPA_STATUS');
711 IGS_GE_MSG_STACK.ADD;
712 App_Exception.Raise_Exception;
713 END IF;
714 RETURN;
715 END IF;
716 -- process history effective up until the current day
717 -- check if effective date falls within a student IGS_PS_COURSE attempt history
718 OPEN c_scah(gv_effective_dt);
719 FETCH c_scah INTO r_scah;
720 IF (c_scah%FOUND) THEN
721 v_scah_found := TRUE;
722 IF r_scah.course_attempt_status IS NULL THEN
723 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before IGS_AU_GEN_003.AUDP_GET_SCAH_COL');
724 r_scah.course_attempt_status :=
725 NVL(IGS_AU_GEN_003.AUDP_GET_SCAH_COL('COURSE_ATTEMPT_STATUS',
726 r_scah.person_id,
727 r_scah.course_cd,
728 r_scah.hist_end_dt),
729 r_sca.course_attempt_status);
730 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after IGS_AU_GEN_003.AUDP_GET_SCAH_COL');
731 END IF;
732 ELSE
733 v_scah_found := FALSE;
734 END IF;
735 CLOSE c_scah;
736 IF r_sca.course_attempt_status = 'ENROLLED' THEN
737 -- check if the effective date falls within the effective
738 -- enrolled period
739 IF TRUNC(gv_effective_dt) >= TRUNC(r_sca.commencement_dt) THEN
740 IF v_scah_found = TRUE THEN
741 IF r_scah.course_attempt_status IN (
742 'ENROLLED',
743 'INTERMIT') THEN
744 -- save the student IGS_PS_COURSE attempt history data
745 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_scah_rec');
746 finpl_ins_scah_rec(
747 r_scah,
748 r_sca);
749 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_scah_rec');
750 ELSE -- enrolment overrides history
751 -- save the current student IGS_PS_COURSE attempt data
752 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
753 finpl_ins_sca_rec(r_sca.commencement_dt,
754 SYSDATE,
755 r_sca);
756 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
757 END IF;
758 ELSE -- no matching history
759 -- save the current student IGS_PS_COURSE attempt data
760 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
761 finpl_ins_sca_rec(r_sca.commencement_dt,
762 SYSDATE,
763 r_sca);
764 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
765 END IF;
766 ELSE -- prior to student IGS_PS_COURSE attempt commencement
767 IF v_scah_found = TRUE THEN
768 IF r_scah.course_attempt_status IN ('UNCONFIRM') THEN
769 -- save the student IGS_PS_COURSE attempt history data
770 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_scah_rec');
771 finpl_ins_scah_rec(
772 r_scah,
773 r_sca);
774 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_scah_rec');
775 ELSE
776 RETURN;
777 END IF;
778 ELSE
779 RETURN;
780 END IF;
781 END IF;
782 ELSIF r_sca.course_attempt_status = 'COMPLETED' THEN
783 -- check if the effective date falls within the effective
784 -- enrolled period
785 IF TRUNC(gv_effective_dt) >= TRUNC(r_sca.commencement_dt) THEN
786 IF v_scah_found = TRUE THEN
787 IF r_scah.course_attempt_status IN (
788 'COMPLETED',
789 'ENROLLED',
790 'INTERMIT') THEN
791 -- save the student IGS_PS_COURSE attempt history data
792
793 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_scah_rec');
794 finpl_ins_scah_rec(
795 r_scah,
796 r_sca);
797 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_scah_rec');
798 ELSE -- not an expected history
799 -- assume ENROLLED -> COMPLETED
800 -- use the last enrolled history
801 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_prc_sca_enrhist');
802 finpl_prc_sca_enrhist(r_sca);
803 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_prc_sca_enrhist');
804 END IF;
805 ELSE -- no matching history
806 -- assume ENROLLED -> COMPLETED
807 -- use the last enrolled history
808 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_prc_sca_enrhist');
809 finpl_prc_sca_enrhist(r_sca);
810 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_prc_sca_enrhist');
811 END IF;
812 ELSE -- prior to student IGS_PS_COURSE attempt commencement
813 IF v_scah_found = TRUE THEN
814 IF r_scah.course_attempt_status IN ('UNCONFIRM') THEN
815 -- save the student IGS_PS_COURSE attempt history data
816 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_scah_rec');
817 finpl_ins_scah_rec(
818 r_scah,
819 r_sca);
820 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_scah_rec');
821 ELSE
822 RETURN;
823 END IF;
824 ELSE
825 RETURN;
826 END IF;
827 END IF;
828 ELSIF r_sca.course_attempt_status = 'DISCONTIN' THEN
829 -- check if the effective date falls within the effective
830 -- discontinuation period
831 IF TRUNC(gv_effective_dt) >= TRUNC(r_sca.discontinued_dt) THEN
832 IF v_scah_found = TRUE THEN
833 IF r_scah.course_attempt_status = 'DISCONTIN' THEN
834 -- save the student IGS_PS_COURSE attempt history data
835 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_scah_rec');
836 finpl_ins_scah_rec(
837 r_scah,
838 r_sca);
839 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_scah_rec');
840 ELSE -- discontinuation overrides the history
841 -- save the current student IGS_PS_COURSE attempt data
842 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
843 finpl_ins_sca_rec(r_sca.discontinued_dt,
844 SYSDATE,
845 r_sca);
846 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
847 END IF;
848 ELSE -- no matching history
849 -- save the current student IGS_PS_COURSE attempt data
850 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_sca_rec');
851 finpl_ins_sca_rec(r_sca.discontinued_dt,
852 SYSDATE,
853 r_sca);
854 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_sca_rec');
855 END IF;
856 ELSE -- prior to student IGS_PS_COURSE attempt discontinuation
857 IF gv_effective_dt >= TRUNC(r_sca.commencement_dt) THEN
858 -- within the enrolled period
859 IF v_scah_found = TRUE THEN
860 IF r_scah.course_attempt_status IN (
861 'ENROLLED',
862 'LAPSED',
863 'INACTIVE',
864 'INTERMIT') THEN
865 -- save the student IGS_PS_COURSE attempt history data
866 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_scah_rec');
867 finpl_ins_scah_rec(
868 r_scah,
869 r_sca);
870 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_scah_rec');
871 ELSE -- not an expected history
872 -- assume ENROLLED -> DISCONTIN
873 -- use the last enrolled history
874 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_prc_sca_enrhist');
875 finpl_prc_sca_enrhist(r_sca);
876 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_prc_sca_enrhist');
877 END IF;
878 ELSE -- no matching history
879 -- assume ENROLLED -> DISCONTIN
880 -- use the last enrolled history
881 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_prc_sca_enrhist');
882 finpl_prc_sca_enrhist(r_sca);
883 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_prc_sca_enrhist');
884 END IF;
885 ELSE -- prior to student IGS_PS_COURSE attempt commencement
886 IF v_scah_found = TRUE THEN
887 IF r_scah.course_attempt_status IN ('UNCONFIRM') THEN
888 -- save the student IGS_PS_COURSE attempt history data
889 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_scah_rec');
890 finpl_ins_scah_rec(
891 r_scah,
892 r_sca);
893 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('after finpl_ins_scah_rec');
894 ELSE
895 RETURN;
896 END IF;
897 ELSE
898 RETURN;
899 END IF;
900 END IF;
901 END IF;
902 ELSIF r_sca.course_attempt_status = 'UNCONFIRM' THEN
903 IF v_scah_found = TRUE THEN
904 IF r_scah.course_attempt_status = 'UNCONFIRM' THEN
905 -- save the student IGS_PS_COURSE attempt history data
906 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('before finpl_ins_scah_rec');
907 finpl_ins_scah_rec(
908 r_scah,
909 r_sca);
910 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_ins_scah_rec');
911 ELSE -- unconfirm overrides history
912 -- save the current student IGS_PS_COURSE attempt data
913 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('b finpl_ins_sca_rec');
914 finpl_ins_sca_rec(gv_effective_dt,
915 SYSDATE,
916 r_sca);
917 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_ins_sca_rec');
918 END IF;
919 ELSE -- no matching history
920 -- check if the effective date falls within the period the
921 -- student IGS_PS_COURSE attempt was created and today
922 IF TRUNC(gv_effective_dt) >= TRUNC(r_sca.LAST_UPDATE_DATE) THEN
923 -- save the current student IGS_PS_COURSE attempt data
924 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('b finpl_ins_sca_rec');
925 finpl_ins_sca_rec(r_sca.LAST_UPDATE_DATE,
926 SYSDATE,
927 r_sca);
928 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_ins_sca_rec');
929 ELSE -- prior to student IGS_PS_COURSE attempt record creation
930 RETURN;
931 END IF;
932 END IF;
933 ELSIF r_sca.course_attempt_status IN (
934 'LAPSED',
935 'INACTIVE',
936 'INTERMIT') THEN
937 -- check if the effective date falls within the effective
938 -- enrolled period
939 IF TRUNC(gv_effective_dt) >= TRUNC(r_sca.commencement_dt) THEN
940 IF v_scah_found = TRUE THEN
941 IF r_scah.course_attempt_status IN (
942 'ENROLLED',
943 'LAPSED',
944 'INACTIVE',
945 'INTERMIT') THEN
946 -- save the student IGS_PS_COURSE attempt history data
947 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('b finpl_ins_scah_rec');
948 finpl_ins_scah_rec(
949 r_scah,
950 r_sca);
951 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_ins_scah_rec');
952 ELSE -- not an expected history
953 -- assume ENROLLED -> LAPSED/INACTIVE/INTERMIT
954 -- use the last enrolled history
955 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('b finpl_prc_sca_enrhist');
956 finpl_prc_sca_enrhist(r_sca);
957 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_prc_sca_enrhist');
958 END IF;
959 ELSE -- no matching history
960 -- assume ENROLLED -> LAPSED/INACTIVE/INTERMIT
961 -- use the last enrolled history
962 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('b finpl_prc_sca_enrhist');
963 finpl_prc_sca_enrhist(r_sca);
964 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_prc_sca_enrhist');
965 END IF;
966 ELSE -- prior to student IGS_PS_COURSE attempt commencement
967 IF v_scah_found = TRUE THEN
968 IF r_scah.course_attempt_status IN ('UNCONFIRM') THEN
969 -- save the student IGS_PS_COURSE attempt history data
970 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('b finpl_ins_scah_rec');
971 finpl_ins_scah_rec(
972 r_scah,
973 r_sca);
974 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_ins_scah_rec');
975 ELSE
976 RETURN;
977 END IF;
978 ELSE
979 RETURN;
980 END IF;
981 END IF;
982 ELSIF r_sca.course_attempt_status = 'DELETED' THEN
983 IF v_scah_found = TRUE THEN
984 IF r_scah.course_attempt_status IN ('DELETED',
985 'UNCONFIRM') THEN
986 -- save the student IGS_PS_COURSE attempt history data
987 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('b finpl_ins_scah_rec');
988 finpl_ins_scah_rec(
989 r_scah,
990 r_sca);
991 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_ins_scah_rec');
992 ELSE -- delete overrides history
993 -- save the current student IGS_PS_COURSE attempt data
994 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('b finpl_ins_sca_rec');
995 finpl_ins_sca_rec( gv_effective_dt,
996 SYSDATE,
997 r_sca);
998 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_ins_sca_rec');
999 END IF;
1000 ELSE
1001 -- check if the effective date falls within the period the
1002 -- student IGS_PS_COURSE attempt was created and today
1003 IF TRUNC(gv_effective_dt) >= TRUNC(r_sca.LAST_UPDATE_DATE) THEN
1004 -- save the current student IGS_PS_COURSE attempt data
1005 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('b finpl_ins_sca_rec');
1006 finpl_ins_sca_rec( r_sca.LAST_UPDATE_DATE,
1007 SYSDATE,
1008 r_sca);
1009 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('a finpl_ins_sca_rec');
1010 ELSE -- prior to student IGS_PS_COURSE attempt record creation
1011 RETURN;
1012 END IF;
1013 END IF;
1014 ELSE -- unrecognised status
1015 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UNRECOG_SPA_STATUS');
1016 IGS_GE_MSG_STACK.ADD;
1017 App_Exception.Raise_Exception;
1018 END IF;
1019 END;
1020 --commented by syam to avoid adchkdrv errors -dbms_output.put_line('out procedure : IGS_FI_GET_SCAEH.FINP_GET_SCAEH');
1021 EXCEPTION
1022 WHEN OTHERS THEN
1023 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1024 Fnd_Message.Set_Token('NAME','IGS_FI_GET_SCAEH.FINP_GET_SCAEH');
1025 IGS_GE_MSG_STACK.ADD;
1026 APP_EXCEPTION.RAISE_EXCEPTION;
1027 END finp_get_scaeh;
1028 END IGS_FI_GET_SCAEH;