[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_GET_SUAEH
Source
1 PACKAGE BODY IGS_FI_GET_SUAEH AS
2 /* $Header: IGSFI07B.pls 115.9 2003/05/23 06:53:57 knaraset ship $ */
3 -- Routine to save SUA effective history data in a PL/SQL TABLE.
4 -- Who When What
5 -- knaraset 29-Apr-03 Modified calls to IGS_AU_GEN_003.AUDP_GET_SUAH_COL to add uoo_id, as part of MUS build bug 2829262
6 --
7
8 PROCEDURE FINP_GET_SUAEH(
9 p_person_id IN NUMBER ,
10 p_course_cd IN IGS_PS_COURSE.course_cd%TYPE ,
11 p_unit_cd IN IGS_PS_UNIT.unit_cd%TYPE ,
12 p_effective_dt IN DATE ,
13 p_table_index IN OUT NOCOPY BINARY_INTEGER ,
14 p_suaeh_table IN OUT NOCOPY IGS_FI_GET_SUAEH.t_suaeh_dtl)
15 AS
16 gv_other_detail VARCHAR2(255);
17 lv_param_values VARCHAR2(1080);
18 BEGIN
19 DECLARE
20 -- cursor to get the current student IGS_PS_UNIT attempt status
21 CURSOR c_sua IS
22 SELECT *
23 FROM IGS_EN_SU_ATTEMPT sua
24 WHERE sua.person_id = p_person_id AND
25 (p_course_cd IS NULL OR
26 sua.course_cd = p_course_cd) AND
27 (p_unit_cd IS NULL OR
28 sua.unit_cd = p_unit_cd);
29 CURSOR c_suah ( cp_course_cd IGS_PS_COURSE.course_cd%TYPE,
30 cp_unit_cd IGS_PS_UNIT.unit_cd%TYPE,
31 cp_effective_dt DATE) IS
32 SELECT PERSON_ID ,
33 COURSE_CD ,
34 UNIT_CD ,
35 VERSION_NUMBER ,
36 CAL_TYPE ,
37 CI_SEQUENCE_NUMBER ,
38 HIST_START_DT ,
39 HIST_END_DT ,
40 HIST_WHO ,
41 LOCATION_CD ,
42 UNIT_CLASS ,
43 ENROLLED_DT ,
44 UNIT_ATTEMPT_STATUS ,
45 ADMINISTRATIVE_UNIT_STATUS ,
46 AUS_DESCRIPTION ,
47 DISCONTINUED_DT ,
48 RULE_WAIVED_DT ,
49 RULE_WAIVED_PERSON_ID ,
50 NO_ASSESSMENT_IND ,
51 EXAM_LOCATION_CD ,
52 ELO_DESCRIPTION ,
53 SUP_UNIT_CD ,
54 SUP_VERSION_NUMBER ,
55 ALTERNATIVE_TITLE ,
56 OVERRIDE_ENROLLED_CP ,
57 OVERRIDE_EFTSU ,
58 OVERRIDE_ACHIEVABLE_CP ,
59 OVERRIDE_OUTCOME_DUE_DT ,
60 OVERRIDE_CREDIT_REASON ,
61 CREATED_BY ,
62 CREATION_DATE ,
63 LAST_UPDATED_BY ,
64 LAST_UPDATE_DATE ,
65 LAST_UPDATE_LOGIN ,
66 DCNT_REASON_CD ,
67 ORG_ID ,
68 UOO_ID
69 FROM IGS_EN_SU_ATTEMPT_H suah
70 WHERE suah.person_id = p_person_id AND
71 suah.course_cd = cp_course_cd AND
72 suah.unit_cd = cp_unit_cd AND
73 cp_effective_dt BETWEEN suah.hist_start_dt AND
74 suah.hist_end_dt;
75 r_sua IGS_EN_SU_ATTEMPT%ROWTYPE;
76 r_suah r_t_suaeh_dtl;-- record type
77 v_suah_found BOOLEAN;
78 -------------------------------------------------------------------------------
79 PROCEDURE finpl_ins_sua_rec(
80 p_hist_start_dt IGS_EN_SU_ATTEMPT_H.hist_start_dt%TYPE,
81 p_hist_end_dt IGS_EN_SU_ATTEMPT_H.hist_end_dt%TYPE,
82 p_sua_rec IGS_EN_SU_ATTEMPT%ROWTYPE)
83 AS
84 BEGIN
85 DECLARE
86 v_suah_rec r_t_suaeh_dtl;
87 BEGIN
88 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) START : IGS_FI_GET_SUAEH.finpl_ins_sua_rec');
89
90 v_suah_rec.person_id := p_sua_rec.person_id;
91 v_suah_rec.course_cd := p_sua_rec.course_cd;
92 v_suah_rec.unit_cd := p_sua_rec.unit_cd;
93 v_suah_rec.version_number := p_sua_rec.version_number;
94 v_suah_rec.cal_type := p_sua_rec.cal_type;
95 v_suah_rec.ci_sequence_number := p_sua_rec.ci_sequence_number;
96 v_suah_rec.hist_start_dt := p_hist_start_dt;
97 v_suah_rec.hist_end_dt := p_hist_end_dt;
98 v_suah_rec.hist_who := p_sua_rec.LAST_UPDATED_BY;
99 v_suah_rec.location_cd := p_sua_rec.location_cd;
100 v_suah_rec.unit_class := p_sua_rec.unit_class;
101 v_suah_rec.enrolled_dt := p_sua_rec.enrolled_dt;
102 v_suah_rec.unit_attempt_status := p_sua_rec.unit_attempt_status;
103 v_suah_rec.administrative_unit_status :=
104 p_sua_rec.administrative_unit_status ;
105 v_suah_rec.discontinued_dt := p_sua_rec.discontinued_dt;
106 v_suah_rec.rule_waived_dt := p_sua_rec.rule_waived_dt;
107 v_suah_rec.rule_waived_person_id := p_sua_rec.rule_waived_person_id;
108 v_suah_rec.no_assessment_ind := p_sua_rec.no_assessment_ind;
109 v_suah_rec.exam_location_cd := p_sua_rec.exam_location_cd;
110 v_suah_rec.sup_unit_cd := p_sua_rec.sup_unit_cd;
111 v_suah_rec.sup_version_number := p_sua_rec.sup_version_number;
112 v_suah_rec.alternative_title := p_sua_rec.alternative_title;
113 v_suah_rec.override_enrolled_cp := p_sua_rec.override_enrolled_cp;
114 v_suah_rec.override_eftsu := p_sua_rec.override_eftsu;
115 v_suah_rec.override_achievable_cp := p_sua_rec.override_achievable_cp;
116 v_suah_rec.override_outcome_due_dt := p_sua_rec.override_outcome_due_dt;
117 v_suah_rec.LAST_UPDATED_BY := p_sua_rec.LAST_UPDATED_BY;
118 v_suah_rec.LAST_UPDATE_DATE := p_sua_rec.LAST_UPDATE_DATE;
119 v_suah_rec.override_credit_reason := p_sua_rec.override_credit_reason;
120 gv_table_index := gv_table_index + 1;
121 gt_suaeh_table(gv_table_index) := v_suah_rec;
122 p_table_index := gv_table_index;
123 p_suaeh_table(p_table_index) := v_suah_rec;
124 v_suah_rec.uoo_id := p_sua_rec.uoo_id;
125 END;
126
127 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) END : IGS_FI_GET_SUAEH.finpl_ins_sua_rec');
128 EXCEPTION
129 WHEN OTHERS THEN
130 if SQLCODE <> -20001 then
131 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
132 Fnd_Message.Set_Token('NAME','IGS_FI_GET_SUAEH.FINPL_INS_SUA_REC');
133 IGS_GE_MSG_STACK.ADD;
134 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
135 else
136 RAISE;
137 end if;
138 END finpl_ins_sua_rec;
139 -------------------------------------------------------------------------------
140 PROCEDURE finpl_ins_suah_rec (
141 p_suah_rec r_t_suaeh_dtl,
142 p_sua_rec IGS_EN_SU_ATTEMPT%ROWTYPE)
143 AS
144 BEGIN
145 DECLARE
146 v_suah_rec r_t_suaeh_dtl;
147 BEGIN
148
149 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) START : IGS_FI_GET_SUAEH.finpl_ins_suah_rec');
150 v_suah_rec.person_id := p_suah_rec.person_id;
151 v_suah_rec.course_cd := p_suah_rec.course_cd;
152 v_suah_rec.unit_cd := p_suah_rec.unit_cd;
153 v_suah_rec.version_number := p_suah_rec.version_number;
154 v_suah_rec.cal_type := p_suah_rec.cal_type;
155 v_suah_rec.ci_sequence_number := p_suah_rec.ci_sequence_number;
156 v_suah_rec.hist_start_dt := p_suah_rec.hist_start_dt;
157 v_suah_rec.hist_end_dt := p_suah_rec.hist_end_dt;
158 v_suah_rec.hist_who := p_suah_rec.hist_who;
159 v_suah_rec.location_cd :=
160 NVL(p_suah_rec.location_cd,
161 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('LOCATION_CD',
162 p_suah_rec.person_id,
163 p_suah_rec.course_cd,
164 p_suah_rec.hist_end_dt,
165 p_suah_rec.uoo_id),
166 p_sua_rec.location_cd));
167 v_suah_rec.unit_class :=
168 NVL(p_suah_rec.unit_class ,
169 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('UNIT_CLASS',
170 p_suah_rec.person_id,
171 p_suah_rec.course_cd,
172 p_suah_rec.hist_end_dt,
173 p_suah_rec.uoo_id),
174 p_sua_rec.unit_class ));
175 v_suah_rec.enrolled_dt :=
176 NVL(p_suah_rec.enrolled_dt,
177 NVL(igs_ge_date.igsdate(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('ENROLLED_DT',
178 p_suah_rec.person_id,
179 p_suah_rec.course_cd,
180 p_suah_rec.hist_end_dt,
181 p_suah_rec.uoo_id)),
182 p_sua_rec.enrolled_dt));
183 v_suah_rec.unit_attempt_status :=
184 NVL(p_suah_rec.unit_attempt_status,
185 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('UNIT_ATTEMPT_STATUS',
186 p_suah_rec.person_id,
187 p_suah_rec.course_cd,
188 p_suah_rec.hist_end_dt,
189 p_suah_rec.uoo_id),
190 p_sua_rec.unit_attempt_status));
191 v_suah_rec.administrative_unit_status :=
192 NVL(p_suah_rec.administrative_unit_status,
193 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('ADMINISTRATIVE_UNIT_STATUS',
194 p_suah_rec.person_id,
195 p_suah_rec.course_cd,
196 p_suah_rec.hist_end_dt,
197 p_suah_rec.uoo_id),
198 p_sua_rec.administrative_unit_status));
199 v_suah_rec.discontinued_dt :=
200 NVL(p_suah_rec.discontinued_dt,
201 NVL(igs_ge_date.igsdate(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('DISCONTINUED_DT',
202 p_suah_rec.person_id,
203 p_suah_rec.course_cd,
204 p_suah_rec.hist_end_dt,
205 p_suah_rec.uoo_id)),
206 p_sua_rec.discontinued_dt));
207 v_suah_rec.rule_waived_dt :=
208 NVL(p_suah_rec.rule_waived_dt,
209 NVL(igs_ge_date.igsdate(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('RULE_WAIVED_DT',
210 p_suah_rec.person_id,
211 p_suah_rec.course_cd,
212 p_suah_rec.hist_end_dt,
213 p_suah_rec.uoo_id)),
214 p_sua_rec.rule_waived_dt));
215 v_suah_rec.rule_waived_person_id :=
216 NVL(p_suah_rec.rule_waived_person_id,
217 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('RULE_WAIVED_PERSON_ID',
218 p_suah_rec.person_id,
219 p_suah_rec.course_cd,
220 p_suah_rec.hist_end_dt,
221 p_suah_rec.uoo_id)),
222 p_sua_rec.rule_waived_person_id));
223 v_suah_rec.no_assessment_ind :=
224 NVL(p_suah_rec.no_assessment_ind,
225 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('NO_ASSESSMENT_IND',
226 p_suah_rec.person_id,
227 p_suah_rec.course_cd,
228 p_suah_rec.hist_end_dt,
229 p_suah_rec.uoo_id),
230 p_sua_rec.no_assessment_ind));
231 v_suah_rec.exam_location_cd :=
232 NVL(p_suah_rec.exam_location_cd,
233 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('EXAM_LOCATION_CD',
234 p_suah_rec.person_id,
235 p_suah_rec.course_cd,
236 p_suah_rec.hist_end_dt,
237 p_suah_rec.uoo_id),
238 p_sua_rec.exam_location_cd));
239 v_suah_rec.sup_unit_cd :=
240 NVL(p_suah_rec.sup_unit_cd,
241 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('SUP_UNIT_CD',
242 p_suah_rec.person_id,
243 p_suah_rec.course_cd,
244 p_suah_rec.hist_end_dt,
245 p_suah_rec.uoo_id),
246 p_sua_rec.sup_unit_cd));
247 v_suah_rec.sup_version_number :=
248 NVL(p_suah_rec.sup_version_number,
249 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('SUP_VERSION_NUMBER',
250 p_suah_rec.person_id,
251 p_suah_rec.course_cd,
252 p_suah_rec.hist_end_dt,
253 p_suah_rec.uoo_id)),
254 p_sua_rec.sup_version_number));
255 v_suah_rec.alternative_title :=
256 NVL(p_suah_rec.alternative_title,
257 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('ALTERNATIVE_TITLE',
258 p_suah_rec.person_id,
259 p_suah_rec.course_cd,
260 p_suah_rec.hist_end_dt,
261 p_suah_rec.uoo_id),
262 p_sua_rec.alternative_title));
263 v_suah_rec.override_enrolled_cp :=
264 NVL(p_suah_rec.override_enrolled_cp,
265 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('OVERRIDE_ENROLLED_CP',
266 p_suah_rec.person_id,
267 p_suah_rec.course_cd,
268 p_suah_rec.hist_end_dt,
269 p_suah_rec.uoo_id)),
270 p_sua_rec.override_enrolled_cp));
271 v_suah_rec.override_eftsu :=
272 NVL(p_suah_rec.override_eftsu,
273 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('OVERRIDE_EFTSU',
274 p_suah_rec.person_id,
275 p_suah_rec.course_cd,
276 p_suah_rec.hist_end_dt,
277 p_suah_rec.uoo_id)),
278 p_sua_rec.override_eftsu));
279 v_suah_rec.override_achievable_cp :=
280 NVL(p_suah_rec.override_achievable_cp,
281 NVL(TO_NUMBER(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('OVERRIDE_ACHIEVABLE_CP',
282 p_suah_rec.person_id,
283 p_suah_rec.course_cd,
284 p_suah_rec.hist_end_dt,
285 p_suah_rec.uoo_id)),
286 p_sua_rec.override_achievable_cp));
287 v_suah_rec.override_outcome_due_dt :=
288 NVL(p_suah_rec.override_outcome_due_dt,
289 NVL(igs_ge_date.igsdate(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('OVERRIDE_OUTCOME_DUE_DT',
290 p_suah_rec.person_id,
291 p_suah_rec.course_cd,
292 p_suah_rec.hist_end_dt,
293 p_suah_rec.uoo_id)),
294 p_sua_rec.override_outcome_due_dt));
295 v_suah_rec.LAST_UPDATED_BY := p_suah_rec.LAST_UPDATED_BY;
296 v_suah_rec.LAST_UPDATE_DATE := p_suah_rec.LAST_UPDATE_DATE;
297 v_suah_rec.override_credit_reason :=
298 NVL(p_suah_rec.override_credit_reason,
299 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('OVERRIDE_CREDIT_REASON',
300 p_suah_rec.person_id,
301 p_suah_rec.course_cd,
302 p_suah_rec.hist_end_dt,
303 p_suah_rec.uoo_id),
304 p_sua_rec.override_credit_reason));
305 v_suah_rec.uoo_id := p_suah_rec.uoo_id;
306 gv_table_index := gv_table_index + 1;
307 gt_suaeh_table(gv_table_index) := v_suah_rec;
308 p_table_index := gv_table_index;
309 p_suaeh_table(p_table_index) := v_suah_rec;
310 END;
311
312 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) END : IGS_FI_GET_SUAEH.finpl_ins_suah_rec');
313 EXCEPTION
314 WHEN OTHERS THEN
315 if SQLCODE <> -20001 then
316 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
320 else
317 Fnd_Message.Set_Token('NAME','IGS_FI_GET_SUAEH.FINP_INS_SUAH_REC');
318 IGS_GE_MSG_STACK.ADD;
319 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
321 RAISE;
322 end if;
323 END finpl_ins_suah_rec;
324 -------------------------------------------------------------------------------
325 PROCEDURE finpl_ins_suahv_rec (
326 p_hist_start_dt IGS_EN_SU_ATTEMPT_H.hist_start_dt%TYPE,
327 p_suahv_rec IGS_AS_SUA_H_V%ROWTYPE)
328 AS
329 BEGIN
330 DECLARE
331 v_suah_rec r_t_suaeh_dtl;
332 BEGIN
333
334 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) START : IGS_FI_GET_SUAEH.finpl_ins_suahv_rec');
335
336 v_suah_rec.person_id := p_suahv_rec.person_id;
337 v_suah_rec.course_cd := p_suahv_rec.course_cd;
338 v_suah_rec.unit_cd := p_suahv_rec.unit_cd;
339 v_suah_rec.version_number := p_suahv_rec.version_number;
340 v_suah_rec.cal_type := p_suahv_rec.cal_type;
341 v_suah_rec.ci_sequence_number := p_suahv_rec.ci_sequence_number;
342 v_suah_rec.hist_start_dt := p_hist_start_dt;
343 v_suah_rec.hist_end_dt := p_suahv_rec.hist_end_dt;
344 v_suah_rec.hist_who := p_suahv_rec.hist_who;
345 v_suah_rec.location_cd := p_suahv_rec.location_cd;
346 v_suah_rec.unit_class:= p_suahv_rec.unit_class;
347 v_suah_rec.enrolled_dt := p_suahv_rec.enrolled_dt;
348 v_suah_rec.unit_attempt_status := p_suahv_rec.unit_attempt_status;
349 v_suah_rec.administrative_unit_status :=
350 p_suahv_rec.administrative_unit_status;
351 -- v_suah_rec.aus_description :=
352 v_suah_rec.discontinued_dt := p_suahv_rec.discontinued_dt;
353 v_suah_rec.rule_waived_dt := p_suahv_rec.rule_waived_dt;
354 v_suah_rec.rule_waived_person_id := p_suahv_rec.rule_waived_person_id;
355 v_suah_rec.no_assessment_ind := p_suahv_rec.no_assessment_ind;
356 v_suah_rec.exam_location_cd := p_suahv_rec.exam_location_cd;
357 -- v_suah_rec.elo_description :=
358 v_suah_rec.sup_unit_cd := p_suahv_rec.sup_unit_cd;
359 v_suah_rec.sup_version_number := p_suahv_rec.sup_version_number;
360 v_suah_rec.alternative_title := p_suahv_rec.alternative_title;
361 v_suah_rec.override_enrolled_cp := p_suahv_rec.override_enrolled_cp;
362 v_suah_rec.override_eftsu := p_suahv_rec.override_eftsu;
363 v_suah_rec.override_achievable_cp := p_suahv_rec.override_achievable_cp;
364 v_suah_rec.override_outcome_due_dt := p_suahv_rec.override_outcome_due_dt;
365 v_suah_rec.LAST_UPDATED_BY := p_suahv_rec.LAST_UPDATED_BY;
366 v_suah_rec.LAST_UPDATE_DATE := p_suahv_rec.LAST_UPDATE_DATE;
367 v_suah_rec.override_credit_reason := p_suahv_rec.override_credit_reason;
368 v_suah_rec.uoo_id := p_suahv_rec.uoo_id;
369 gv_table_index := gv_table_index + 1;
370 gt_suaeh_table(gv_table_index) := v_suah_rec;
371 p_table_index := gv_table_index;
372 p_suaeh_table(p_table_index) := v_suah_rec;
373 END;
374
375 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) END : IGS_FI_GET_SUAEH.finpl_ins_suahv_rec');
376 EXCEPTION
377 WHEN OTHERS THEN
378 if SQLCODE <> -20001 then
379 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
380 Fnd_Message.Set_Token('NAME','IGS_FI_GET_SUAEH.FINPL_INS_SUAHV_REC');
381 IGS_GE_MSG_STACK.ADD;
382 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
383 else
384 RAISE;
385 end if;
386 END finpl_ins_suahv_rec;
387 -------------------------------------------------------------------------------
388 PROCEDURE finpl_prc_sua_enrhist(
389 p_sua_rec IN OUT NOCOPY IGS_EN_SU_ATTEMPT%ROWTYPE)
390 AS
391 BEGIN
392 DECLARE
393 CURSOR c_suahv_last_enr (
394 cp_course_cd IGS_PS_COURSE.course_cd%TYPE,
395 cp_unit_cd IGS_PS_UNIT.unit_cd%TYPE,
396 cp_effective_dt DATE) IS
397 SELECT *
398 FROM IGS_AS_SUA_H_V suahv
399 WHERE suahv.person_id = p_person_id AND
400 suahv.course_cd = cp_course_cd AND
401 suahv.unit_cd = cp_unit_cd AND
402 suahv.unit_attempt_status = 'ENROLLED' AND
403 cp_effective_dt <= suahv.hist_start_dt
404 ORDER BY suahv.hist_start_dt desc;
405 r_suahv IGS_AS_SUA_H_V%ROWTYPE;
406 BEGIN
407 -- check the last enrolled history for a match
408 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) START : IGS_FI_GET_SUAEH.finpl_prc_sua_enrhist');
409
410 OPEN c_suahv_last_enr(
411 p_sua_rec.course_cd,
412 p_sua_rec.unit_cd,
413 p_sua_rec.enrolled_dt);
414 FETCH c_suahv_last_enr INTO r_suahv;
415 IF (c_suahv_last_enr%FOUND) THEN
416 CLOSE c_suahv_last_enr;
417 IF TRUNC(gv_effective_dt) <= TRUNC(r_suahv.hist_end_dt) THEN
418 -- save the SUA history data when last enrolled
419 finpl_ins_suahv_rec(
420 p_sua_rec.enrolled_dt,
421 r_suahv);
422 ELSE
423 -- save the current student IGS_PS_UNIT attempt data
424 finpl_ins_sua_rec(
425 r_suahv.hist_end_dt,
426 SYSDATE,
427 p_sua_rec);
428 END IF;
429 ELSE -- missing enrolment history
430 CLOSE c_suahv_last_enr;
431 -- check if the effective date falls within the period
432 -- of the current student IGS_PS_UNIT attempt values
436 p_sua_rec.LAST_UPDATE_DATE,
433 IF TRUNC(gv_effective_dt) >= TRUNC(p_sua_rec.LAST_UPDATE_DATE) THEN
434 -- save the current sua data
435 finpl_ins_sua_rec(
437 SYSDATE,
438 p_sua_rec);
439 ELSE
440 -- simulate an enrolment history
441 p_sua_rec.unit_attempt_status := 'ENROLLED';
442 finpl_ins_sua_rec(
443 p_sua_rec.enrolled_dt,
444 p_sua_rec.LAST_UPDATE_DATE,
445 p_sua_rec);
446 END IF;
447 END IF;
448 END;
449
450 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE (DECLARE) END : IGS_FI_GET_SUAEH.finpl_prc_sua_enrhist');
451 EXCEPTION
452 WHEN OTHERS THEN
453 if SQLCODE <> -20001 then
454 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
455 Fnd_Message.Set_Token('NAME','IGS_FI_GET_SUAEH.FINPL_PRC_SUA_ENRHIST');
456 IGS_GE_MSG_STACK.ADD;
457 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
458 else
459 RAISE;
460 end if;
461 END finpl_prc_sua_enrhist;
462 -------------------------------------------------------------------------------
463 BEGIN -- finp_get_suaeh
464 -- effective history logic is based upon the following assumptions -
465 -- the transitions between IGS_PS_UNIT status's are;
466 -- UNCONFIRM -> ENROLLED
467 --
468 -- ENROLLED -> UNCONFIRM
469 -- -> DISCONTIN
470 -- -> INVALID
471 -- -> COMPLETED
472 --
473 -- INVALID -> ENROLLED
474 -- -> DISCONTIN
475 --
476 -- DISCONTIN -> ENROLLED
477 -- -> DUPLICATE (transfer from another SCA)
478 --
479 -- COMPLETED -> ENROLLED
480 -- -> DUPLICATE (transfer from another SCA)
481 --
482 -- DUPLICATE -> DUPLICATE
483 --
484 -- the effective history transitions are;
485 -- UNCONFIRM -> ENROLLED -> COMPLETED
486 -- UNCONFIRM -> ENROLLED -> DISCONTIN
487 -- check parameters
488
489 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE START : IGS_FI_GET_SUAEH.finp_get_suaeh');
490
491 IF p_person_id IS NULL OR
492 p_effective_dt IS NULL THEN
493 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
494 IGS_GE_MSG_STACK.ADD;
495 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
496 END IF;
497 -- check if the effective history has already been captured
498 IF gv_person_id IS NOT NULL AND
499 gv_effective_dt IS NOT NULL THEN
500 IF gv_table_index > 0 THEN
501 IF gv_person_id = p_person_id AND
502 NVL(gv_course_cd,'NULL') = NVL(p_course_cd,'NULL') AND
503 NVL(gv_unit_cd,'NULL') = NVL(p_unit_cd,'NULL') AND
504 TRUNC(gv_effective_dt) = TRUNC(p_effective_dt) THEN
505 FOR p_table_index IN 1..gv_table_index
506 LOOP
507 p_suaeh_table(p_table_index) := gt_suaeh_table(p_table_index);
508 END LOOP;
509 p_table_index := gv_table_index;
510 RETURN;
511 END IF;
512 END IF;
513 END IF;
514 -- initialise data
515 gt_suaeh_table := gt_empty_table;
516 gv_table_index := 0;
517 gv_person_id := p_person_id;
518 gv_course_cd := p_course_cd;
519 gv_unit_cd := p_unit_cd;
520 gv_effective_dt := igs_ge_date.igsdate(igs_ge_date.igschar(p_effective_dt)|| '23:59:59');
521 -- get the current student IGS_PS_UNIT attempt detail
522 FOR v_sua_rec IN c_sua
523 LOOP
524 r_sua := v_sua_rec;
525 -- check if effective date is set today or into the future
526 IF TRUNC(gv_effective_dt) >= TRUNC(SYSDATE) THEN
527 IF r_sua.unit_attempt_status IN (
528 'COMPLETED',
529 'ENROLLED',
530 'INVALID') THEN
531 -- check if commencing on or before the effective date
532 IF TRUNC(r_sua.enrolled_dt) <= TRUNC(gv_effective_dt) THEN
533 -- save the current student IGS_PS_UNIT attempt data
534 finpl_ins_sua_rec(
535 r_sua.enrolled_dt,
536 gv_effective_dt,
537 r_sua);
538 ELSE
539 -- save the current student IGS_PS_UNIT attempt data as an
540 -- unconfirmed history
541 r_sua.unit_attempt_status := 'UNCONFIRM';
542 finpl_ins_sua_rec(
543 gv_effective_dt,
544 r_sua.enrolled_dt,
545 r_sua);
546 END IF;
547 ELSIF r_sua.unit_attempt_status = 'DISCONTIN' THEN
548 -- save the current student IGS_PS_UNIT attempt data
549 finpl_ins_sua_rec(
550 r_sua.discontinued_dt,
551 gv_effective_dt,
552 r_sua);
553 ELSIF r_sua.unit_attempt_status = 'UNCONFIRM' THEN
554 -- save the current student IGS_PS_UNIT attempt data
555 finpl_ins_sua_rec(
556 r_sua.LAST_UPDATE_DATE,
557 gv_effective_dt,
558 r_sua);
559 ELSE -- unrecognised status
560 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UNRECOG_SUA_STATUS');
561 IGS_GE_MSG_STACK.ADD;
562 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
563 END IF;
564 ELSE -- processing history effective up until the current day
565 -- check if effective date falls within the current SUA history
566 OPEN c_suah( r_sua.course_cd,
567 r_sua.unit_cd,
568 gv_effective_dt);
572 IF r_suah.unit_attempt_status IS NULL THEN
569 FETCH c_suah INTO r_suah;
570 IF (c_suah%FOUND) THEN
571 v_suah_found := TRUE;
573 r_suah.unit_attempt_status :=
574 NVL(IGS_AU_GEN_003.AUDP_GET_SUAH_COL('UNIT_ATTEMPT_STATUS',
575 r_suah.person_id,
576 r_suah.course_cd,
577 r_suah.hist_end_dt,
578 r_suah.uoo_id),
579 r_sua.unit_attempt_status);
580 END IF;
581 ELSE
582 v_suah_found := FALSE;
583 END IF;
584 CLOSE c_suah;
585 IF r_sua.unit_attempt_status = 'ENROLLED' THEN
586 -- check if the effective date falls within the effective
587 -- enrolled period
588 IF TRUNC(gv_effective_dt) >= TRUNC(r_sua.enrolled_dt) THEN
589 IF v_suah_found = TRUE THEN
590 IF r_suah.unit_attempt_status IN ('ENROLLED') THEN
591 -- save the sua history data
592 finpl_ins_suah_rec(
593 r_suah,
594 r_sua);
595 ELSE -- enrolment overrides history
596 -- save the current sua data
597 finpl_ins_sua_rec(r_sua.enrolled_dt,
598 SYSDATE,
599 r_sua);
600 END IF;
601 ELSE -- no matching history
602 -- save the current sua data
603 finpl_ins_sua_rec(r_sua.enrolled_dt,
604 SYSDATE,
605 r_sua);
606 END IF;
607 ELSE -- prior to student IGS_PS_UNIT attempt enrolment
608 IF v_suah_found = TRUE THEN
609 IF r_suah.unit_attempt_status IN ('UNCONFIRM') THEN
610 -- save the sua history data
611 finpl_ins_suah_rec(
612 r_suah,
613 r_sua);
614 ELSE
615 RETURN;
616 END IF;
617 ELSE
618 RETURN;
619 END IF;
620 END IF;
621 ELSIF r_sua.unit_attempt_status = 'COMPLETED' THEN
622 -- check if the effective date falls within the effective
623 -- enrolled period
624 IF TRUNC(gv_effective_dt) >= TRUNC(r_sua.enrolled_dt) THEN
625 IF v_suah_found = TRUE THEN
626 IF r_suah.unit_attempt_status IN (
627 'COMPLETED',
628 'ENROLLED') THEN
629 -- save the sua history data
630 finpl_ins_suah_rec(
631 r_suah,
632 r_sua);
633 ELSE -- not an expected history
634 -- assume ENROLLED -> COMPLETED
635 -- use the last enrolled history
636 finpl_prc_sua_enrhist(r_sua);
637 END IF;
638 ELSE -- no matching history
639 -- assume ENROLLED -> COMPLETED
640 -- use the last enrolled history
641 finpl_prc_sua_enrhist(r_sua);
642 END IF;
643 ELSE -- prior to student IGS_PS_UNIT attempt enrolment
644 IF v_suah_found = TRUE THEN
645 IF r_suah.unit_attempt_status IN ('UNCONFIRM') THEN
646 -- save the sua history data
647 finpl_ins_suah_rec(
648 r_suah,
649 r_sua);
650 ELSE
651 RETURN;
652 END IF;
653 ELSE
654 RETURN; -- no history
655 END IF;
656 END IF;
657 ELSIF r_sua.unit_attempt_status = 'DISCONTIN' THEN
658 -- check if the effective date falls within the effective
659 -- discontinuation period
660 IF TRUNC(gv_effective_dt) >= TRUNC(r_sua.discontinued_dt) THEN
661 IF v_suah_found = TRUE THEN
662 IF r_suah.unit_attempt_status = 'DISCONTIN' THEN
663 -- save the sua history data
664 finpl_ins_suah_rec(
665 r_suah,
666 r_sua);
667 ELSE -- discontinuation overrides the history
668 -- save the current sua data
669 finpl_ins_sua_rec(r_sua.discontinued_dt,
670 SYSDATE,
671 r_sua);
672 END IF;
673 ELSE -- no matching history
674 -- save the current sua data
675 finpl_ins_sua_rec(r_sua.discontinued_dt,
676 SYSDATE,
677 r_sua);
678 END IF;
679 ELSE -- prior to student IGS_PS_UNIT attempt discontinuation
680 IF gv_effective_dt >= TRUNC(r_sua.enrolled_dt) THEN
681 -- within the enrolled period
682 IF v_suah_found = TRUE THEN
683 IF r_suah.unit_attempt_status IN (
684 'ENROLLED',
685 'INVALID') THEN
686 -- save the SUA history data
687 finpl_ins_suah_rec(
688 r_suah,
689 r_sua);
690 ELSE -- not an expected history
691 -- assume ENROLLED -> DISCONTIN
692 -- use the last enrolled history
693 finpl_prc_sua_enrhist(r_sua);
694 END IF;
695 ELSE -- no matching history
696 -- assume ENROLLED -> DISCONTIN
697 -- use the last enrolled history
698 finpl_prc_sua_enrhist(r_sua);
699 END IF;
700 ELSE -- prior to student IGS_PS_UNIT attempt enrolment
701 IF v_suah_found = TRUE THEN
702 IF r_suah.unit_attempt_status IN (
703 'UNCONFIRM') THEN
704 -- save the SUA history data
705 finpl_ins_suah_rec(
706 r_suah,
707 r_sua);
708 ELSE
709 RETURN;
713 END IF;
710 END IF;
711 ELSE
712 RETURN; -- no history
714 END IF;
715 END IF;
716 ELSIF r_sua.unit_attempt_status = 'UNCONFIRM' THEN
717 IF v_suah_found = TRUE THEN
718 IF r_suah.unit_attempt_status = 'UNCONFIRM' THEN
719 -- save the sua history data
720 finpl_ins_suah_rec(
721 r_suah,
722 r_sua);
723 ELSE -- unconfirm overrides history
724 -- save the current sua data
725 finpl_ins_sua_rec(
726 gv_effective_dt,
727 SYSDATE,
728 r_sua);
729 END IF;
730 ELSE -- no matching history
731 -- check if the effective date falls within the
732 -- period the sua was created and today
733 IF TRUNC(gv_effective_dt) >= TRUNC(r_sua.LAST_UPDATE_DATE) THEN
734 -- save the current sua data
735 finpl_ins_sua_rec(
736 r_sua.LAST_UPDATE_DATE,
737 SYSDATE,
738 r_sua);
739 ELSE -- prior to student IGS_PS_UNIT attempt record creation
740 RETURN;
741 END IF;
742 END IF;
743 ELSIF r_sua.unit_attempt_status = 'INVALID' THEN
744 -- check if the effective date falls within the effective
745 -- enrolled period
746 IF TRUNC(gv_effective_dt) >= TRUNC(r_sua.enrolled_dt) THEN
747 IF v_suah_found = TRUE THEN
748 IF r_suah.unit_attempt_status IN (
749 'INVALID',
750 'ENROLLED') THEN
751 -- save the sua history data
752 finpl_ins_suah_rec(
753 r_suah,
754 r_sua);
755 ELSE -- not an expected history
756 -- assume ENROLLED -> INVALID
757 -- use the last enrolled history
758 finpl_prc_sua_enrhist(r_sua);
759 END IF;
760 ELSE -- no matching history
761 -- assume ENROLLED -> INVALID
762 -- use the last enrolled history
763 finpl_prc_sua_enrhist(r_sua);
764 END IF;
765 ELSE -- prior to student IGS_PS_UNIT attempt enrolment
766 IF v_suah_found = TRUE THEN
767 IF r_suah.unit_attempt_status IN ('UNCONFIRM') THEN
768 -- save the SUA history data
769 finpl_ins_suah_rec(
770 r_suah,
771 r_sua);
772 ELSE
773 RETURN;
774 END IF;
775 ELSE
776 RETURN;
777 END IF;
778 END IF;
779 ELSIF r_sua.unit_attempt_status = 'DUPLICATE' THEN
780 -- check if the effective date falls within the original
781 -- effective discontinuation or enrolled period
782 IF TRUNC(gv_effective_dt) >= NVL(TRUNC(r_sua.discontinued_dt),
783 TRUNC(r_sua.enrolled_dt)) THEN
784 IF v_suah_found = TRUE THEN
785 IF r_suah.unit_attempt_status IN (
786 'DUPLICATE') THEN
787 -- save the sua history data
788 finpl_ins_suah_rec(
789 r_suah,
790 r_sua);
791 ELSE -- not an expected history
792 RETURN; -- no history
793 END IF;
794 ELSE -- no matching history
795 -- save the current sua data
796 finpl_ins_sua_rec(
797 NVL(r_sua.discontinued_dt, r_sua.enrolled_dt),
798 SYSDATE,
799 r_sua);
800 END IF;
801 ELSE -- prior to original student IGS_PS_UNIT attempt
802 -- dicontinuation/enrolment
803 RETURN; -- no history
804 END IF;
805 ELSE -- unrecognised status
806 Fnd_Message.Set_Name ('IGS', 'IGS_FI_UNRECOG_SUA_STATUS');
807 IGS_GE_MSG_STACK.ADD;
808 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
809 END IF;
810 END IF;
811 END LOOP;
812 END;
813
814 --commented by syam to avoid adchkdrv errors -dbms_output.put_line(' IN PROCEDURE END : IGS_FI_GET_SUAEH.finp_get_suaeh');
815 EXCEPTION
816 WHEN OTHERS THEN
817 if SQLCODE <> -20001 then
818 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
819 FND_MESSAGE.SET_TOKEN('NAME','IGS_FI_GET_SUAEH.FINP_GET_SUAEH');
820 IGS_GE_MSG_STACK.ADD;
821 lv_param_values := to_char(p_person_id)||','||
822 p_course_cd||','||p_unit_cd||','||
823 igs_ge_date.igschardt(p_effective_dt);
824 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PARAMETERS');
825 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
826 IGS_GE_MSG_STACK.ADD;
827 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
828 else
829 RAISE;
830 end if;
831 END finp_get_suaeh;
832 END IGS_FI_GET_SUAEH;