1 PACKAGE BODY IGS_RE_GEN_002 AS
2 /* $Header: IGSRE02B.pls 120.1 2005/11/24 04:34:42 appldev ship $ */
3 /*******************************************************************************
4 Created by :
5 Date created:
6
7 Known limitations/enhancements/remarks:
8
9 Change History: (who, when, what)
10 Who When What
11 sarakshi 02-Sep-2004 Bug#3815825, modified procedure RESP_GET_RSUP_START to add one else condition.
12 vkarthik 26-Apr-2004 Changed function resp_ins_dflt_mil for EN303 Milestone build Enh#3577974
13 rnirwani 13-Sep-2004 changed cursor c_intmsn_details to not consider logically deleted records and
14 also to avoid un-approved intermission records. Bug# 3885804
15 *******************************************************************************/
16
17 PROCEDURE resp_get_ca_exists(
18 p_person_id IN NUMBER ,
19 p_ca_sequence_number IN NUMBER ,
20 p_effective_dt IN DATE ,
21 p_check_thesis IN BOOLEAN ,
22 p_check_field_of_study IN BOOLEAN ,
23 p_check_seo_class_cd IN BOOLEAN ,
24 p_check_supervisor IN BOOLEAN ,
25 p_check_milestone IN BOOLEAN ,
26 p_check_scholarship IN BOOLEAN ,
27 p_thesis_exists OUT NOCOPY BOOLEAN ,
28 p_field_of_study_exists OUT NOCOPY BOOLEAN ,
29 p_seo_class_cd_exists OUT NOCOPY BOOLEAN ,
30 p_supervisor_exists OUT NOCOPY BOOLEAN ,
31 p_milestone_exists OUT NOCOPY BOOLEAN ,
32 p_scholarship_exists OUT NOCOPY BOOLEAN )
33 AS
34 BEGIN -- resp_get_ca_exists
35 -- Description: This module returns output parameters indicating whether
36 -- or not data exists on IGS_RE_CANDIDATURE detail tables for the specified
37 -- IGS_RE_CANDIDATURE.person_id /sequence_number.
38 DECLARE
39 v_the_dummy_rec VARCHAR2(1);
40 v_cafos_dummy_rec VARCHAR2(1);
41 v_csc_dummy_rec VARCHAR2(1);
42 v_rsup_dummy_rec VARCHAR2(1);
43 v_mil_dummy_rec VARCHAR2(1);
44 v_sch_dummy_rec VARCHAR2(1);
45 CURSOR c_the IS
46 SELECT 'X'
47 FROM IGS_RE_THESIS the
48 WHERE the.person_id = p_person_id AND
49 the.ca_sequence_number = p_ca_sequence_number AND
50 the.logical_delete_dt IS NULL;
51 CURSOR c_cafos IS
52 SELECT 'X'
53 FROM IGS_RE_CDT_FLD_OF_SY cafos
54 WHERE cafos.person_id = p_person_id AND
55 cafos.ca_sequence_number = p_ca_sequence_number;
56 CURSOR c_csc IS
57 SELECT 'X'
58 FROM IGS_RE_CAND_SEO_CLS csc
59 WHERE csc.person_id = p_person_id AND
60 csc.ca_sequence_number = p_ca_sequence_number;
61 CURSOR c_rsup IS
62 SELECT 'X'
63 FROM IGS_RE_SPRVSR rsup
64 WHERE rsup.ca_person_id = p_person_id AND
65 rsup.ca_sequence_number = p_ca_sequence_number AND
66 rsup.start_dt <= p_effective_dt AND
67 NVL(rsup.end_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) >= p_effective_dt;
68 CURSOR c_mil IS
69 SELECT 'X'
70 FROM IGS_PR_MILESTONE mil
71 WHERE mil.person_id = p_person_id AND
72 mil.ca_sequence_number = p_ca_sequence_number;
73 CURSOR c_sch IS
74 SELECT 'X'
75 FROM IGS_RE_SCHOLARSHIP sch
76 WHERE sch.person_id = p_person_id AND
77 sch.ca_sequence_number = p_ca_sequence_number AND
78 sch.start_dt <= p_effective_dt AND
79 NVL(sch.end_dt, IGS_GE_DATE.IGSDATE('9999/01/01')) >= p_effective_dt;
80 BEGIN
81 p_thesis_exists := FALSE;
82 p_field_of_study_exists := FALSE;
83 p_seo_class_cd_exists := FALSE;
84 p_supervisor_exists := FALSE;
85 p_milestone_exists := FALSE;
86 p_scholarship_exists := FALSE;
87 IF p_check_thesis THEN
88 --Validate for the existence of IGS_RE_THESIS details
89 OPEN c_the;
90 FETCH c_the INTO v_the_dummy_rec;
91 IF (c_the%FOUND) THEN
92 CLOSE c_the;
93 p_thesis_exists := TRUE;
94 ELSE
95 CLOSE c_the;
96 END IF;
97 END IF;
98 IF p_check_field_of_study THEN
99 --Validate for existence of IGS_RE_CANDIDATURE field of study
100 OPEN c_cafos;
101 FETCH c_cafos INTO v_cafos_dummy_rec;
102 IF (c_cafos%FOUND) THEN
103 CLOSE c_cafos;
104 p_field_of_study_exists := TRUE;
105 ELSE
106 CLOSE c_cafos;
107 END IF;
108 END IF;
109 IF p_check_seo_class_cd THEN
110 --Validate for existence of IGS_RE_CANDIDATURE socio-economic classification code
111 OPEN c_csc;
112 FETCH c_csc INTO v_csc_dummy_rec;
113 IF (c_csc%FOUND) THEN
114 CLOSE c_csc;
115 p_seo_class_cd_exists := TRUE;
116 ELSE
117 CLOSE c_csc;
118 END IF;
119 END IF;
120 IF p_check_supervisor THEN
121 --Validate for existence of research supervisor
122 OPEN c_rsup;
123 FETCH c_rsup INTO v_rsup_dummy_rec;
124 IF (c_rsup%FOUND) THEN
125 CLOSE c_rsup;
126 p_supervisor_exists := TRUE;
127 ELSE
128 CLOSE c_rsup;
129 END IF;
130 END IF;
131 IF p_check_milestone THEN
132 --Validate for existence of research IGS_RE_CANDIDATURE milestones
133 OPEN c_mil;
134 FETCH c_mil INTO v_mil_dummy_rec;
135 IF (c_mil%FOUND) THEN
136 CLOSE c_mil;
137 p_milestone_exists := TRUE;
138 ELSE
139 CLOSE c_mil;
140 END IF;
141 END IF;
142 IF p_check_scholarship THEN
143 --Validate for existence of research IGS_RE_CANDIDATURE IGS_RE_SCHOLARSHIP
144 OPEN c_sch;
145 FETCH c_sch INTO v_sch_dummy_rec;
146 IF (c_sch%FOUND) THEN
147 CLOSE c_sch;
148 p_scholarship_exists := TRUE;
149 ELSE
150 CLOSE c_sch;
151 END IF;
152 END IF;
153 EXCEPTION
154 WHEN OTHERS THEN
155 IF (c_the%ISOPEN) THEN
156 CLOSE c_the;
157 END IF;
158 IF (c_cafos%ISOPEN) THEN
159 CLOSE c_cafos;
160 END IF;
161 IF (c_csc%ISOPEN) THEN
162 CLOSE c_csc;
163 END IF;
164 IF (c_rsup%ISOPEN) THEN
165 CLOSE c_rsup;
166 END IF;
167 IF (c_mil%ISOPEN) THEN
168 CLOSE c_mil;
169 END IF;
170 IF (c_sch%ISOPEN) THEN
171 CLOSE c_sch;
172 END IF;
173 END;
174 EXCEPTION
175 WHEN OTHERS THEN
176 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
177 IGS_GE_MSG_STACK.ADD;
178 App_Exception.Raise_Exception;
179 END resp_get_ca_exists;
180
181
182 FUNCTION resp_get_rsup_start(
183 p_person_id IN NUMBER ,
184 p_ca_sequence_number IN NUMBER ,
185 p_sca_course_cd IN VARCHAR2 ,
186 p_acai_admission_appl_number IN NUMBER ,
187 p_acai_nominated_course_cd IN VARCHAR2 ,
188 p_acai_sequence_number IN NUMBER ,
189 p_parent IN VARCHAR2 )
190 RETURN DATE AS
191 BEGIN -- resp_get_rsup_start
192 -- This module gets the date that supervision is required from for a
193 -- IGS_RE_CANDIDATURE.
194 DECLARE
195 cst_sca CONSTANT VARCHAR2(10) := 'SCA';
196 cst_acai CONSTANT VARCHAR2(10) := 'ACAI';
197 cst_rsup CONSTANT VARCHAR2(10) := 'RSUP';
198 v_start_dt DATE;
199 v_research_type_ind VARCHAR2(1) DEFAULT 'N';
200 v_s_adm_outcome_status IGS_AD_OU_STAT.s_adm_outcome_status%TYPE;
201 v_s_admission_process_type IGS_AD_APPL.s_admission_process_type%TYPE;
202 v_research_unit_start_dt DATE;
203 CURSOR c_ca IS
204 SELECT ca.sca_course_cd,
205 ca.acai_admission_appl_number,
206 ca.acai_nominated_course_cd,
207 ca.acai_sequence_number
208 FROM IGS_RE_CANDIDATURE ca
209 WHERE ca.person_id = p_person_id AND
210 ca.sequence_number = p_ca_sequence_number;
211 v_ca_rec c_ca%ROWTYPE;
212 CURSOR c_sca(
213 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE)
214 IS
215 SELECT sca.course_attempt_status,
216 sca.version_number,
217 sca.commencement_dt
218 FROM IGS_EN_STDNT_PS_ATT sca
219 WHERE sca.person_id = p_person_id AND
220 sca.course_cd = cp_course_cd;
221 v_sca_rec c_sca%ROWTYPE;
222 v_sca_rec1 c_sca%ROWTYPE;
223 CURSOR c_acai(
224 cp_adm_appl_number IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE,
225 cp_nom_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE,
226 cp_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE)
227 IS
228 SELECT acai.course_cd,
229 acai.crv_version_number,
230 acai.adm_outcome_status,
231 acai.prpsd_commencement_dt
232 FROM IGS_AD_PS_APPL_INST acai
233 WHERE acai.person_id = p_person_id AND
234 acai.admission_appl_number = cp_adm_appl_number AND
235 acai.nominated_course_cd = cp_nom_course_cd AND
236 acai.sequence_number = cp_sequence_number;
237 v_acai_rec c_acai%ROWTYPE;
238 CURSOR c_apcs (
239 cp_adm_appl_num IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE)
240 IS
241 SELECT aa.s_admission_process_type
242 FROM IGS_AD_APPL aa,
243 IGS_AD_PRCS_CAT_STEP apcs
244 WHERE aa.person_id = p_person_id AND
245 aa.admission_appl_number = cp_adm_appl_num AND
246 aa.admission_cat = apcs.admission_cat AND
247 aa.s_admission_process_type =
248 apcs.s_admission_process_type AND
249 apcs.s_admission_step_type = 'RESEARCH' AND
250 apcs.mandatory_step_ind = 'Y' AND
251 apcs.step_group_type <> 'TRACK'; --2402377
252 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
253 -- Local procedure used to get the research IGS_PS_COURSE type.
254 PROCEDURE respl_get_rsch_cty (
255 p_course_cd IGS_PS_VER.course_cd%TYPE,
256 p_version_number IGS_PS_VER.version_number%TYPE,
257 p_research_type_ind OUT NOCOPY VARCHAR2)
258 AS
259 BEGIN -- respl_get_rsch_cty
260 -- Determine if IGS_PS_COURSE version is a research IGS_PS_COURSE type,
261 -- this impliessupervision is required from IGS_PS_COURSE
262 -- commencement date.
263 DECLARE
264 CURSOR c_crv IS
265 SELECT cty.research_type_ind
266 FROM IGS_PS_VER crv,
267 IGS_PS_TYPE cty
268 WHERE crv.course_cd = p_course_cd AND
269 crv.version_number = p_version_number AND
270 crv.course_type = cty.course_type;
271 BEGIN
272 p_research_type_ind := NULL;
273 OPEN c_crv;
274 FETCH c_crv INTO p_research_type_ind;
275 CLOSE c_crv;
276 EXCEPTION
277 WHEN OTHERS THEN
278 IF c_crv%ISOPEN THEN
279 CLOSE c_crv;
280 END IF;
281 RAISE;
282 END;
283 END respl_get_rsch_cty;
284 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
285 -- Local function used to set start date to earliest research IGS_PS_UNIT enrolment
286
287 FUNCTION respl_get_rsch_enrlmnt (
288 p_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
289 p_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE)
290 RETURN DATE AS
291 BEGIN -- respl_get_rsch_enrlmnt
292 -- Set the start date to earliest research IGS_PS_UNIT enrolment.
293 DECLARE
294 v_start_dt DATE;
295 v_end_dt DATE;
296 v_research_unit_start_dt DATE;
297 v_teach_days NUMBER;
298 CURSOR c_sua IS
299 SELECT sua.cal_type,
300 sua.ci_sequence_number
301 FROM IGS_EN_SU_ATTEMPT sua,
302 IGS_PS_UNIT_VER uv
303 WHERE sua.person_id = p_person_id AND
304 sua.course_cd = p_course_cd AND
305 sua.unit_cd = uv.unit_cd AND
306 sua.version_number = uv.version_number AND
307 uv.research_unit_ind = 'Y' AND
308 sua.unit_attempt_status IN (
309 'ENROLLED',
310 'COMPLETED',
311 'DISCONTIN');
312 BEGIN
313 v_research_unit_start_dt := NULL;
314 FOR v_sua_rec IN c_sua LOOP
315 v_teach_days := resp_get_teach_days(
316 v_sua_rec.cal_type,
317 v_sua_rec.ci_sequence_number,
318 v_start_dt,
319 v_end_dt);
320 IF v_research_unit_start_dt IS NULL OR
321 v_research_unit_start_dt >= v_start_dt THEN
322 v_research_unit_start_dt := v_start_dt;
323 END IF;
324 END LOOP;
325 RETURN v_research_unit_start_dt;
326 EXCEPTION
327 WHEN OTHERS THEN
328 IF c_sua%ISOPEN THEN
329 CLOSE c_sua;
330 END IF;
331 RAISE;
332 END;
333 END respl_get_rsch_enrlmnt;
334 -- +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++--
335 BEGIN -- main routine.
336 IF p_parent = cst_rsup OR
337 (p_sca_course_cd IS NULL AND
338 (p_acai_admission_appl_number IS NULL OR
339 p_acai_nominated_course_cd IS NULL OR
340 p_acai_sequence_number IS NULL)) THEN
341 OPEN c_ca;
342 FETCH c_ca INTO v_ca_rec;
343 IF c_ca%NOTFOUND THEN
344 -- Something is wrong, handled elsewhere.
345 CLOSE c_ca;
346 RETURN IGS_GE_DATE.IGSDATE(NULL);
347 END IF;
348 CLOSE c_ca;
349 ELSE
350 v_ca_rec.sca_course_cd := p_sca_course_cd;
351 v_ca_rec.acai_admission_appl_number := p_acai_admission_appl_number;
352 v_ca_rec.acai_nominated_course_cd := p_acai_nominated_course_cd;
353 v_ca_rec.acai_sequence_number := p_acai_sequence_number;
354 END IF;
355 IF v_ca_rec.acai_admission_appl_number IS NULL AND
356 v_ca_rec.acai_nominated_course_cd IS NULL AND
357 v_ca_rec.acai_sequence_number IS NULL THEN
358 -- IGS_RE_CANDIDATURE has been added through ENRF3000.
359 OPEN c_sca(v_ca_rec.sca_course_cd);
360 FETCH c_sca INTO v_sca_rec;
361 IF c_sca%NOTFOUND THEN
362 CLOSE c_sca;
363 RETURN IGS_GE_DATE.IGSDATE(NULL);
364 END IF;
365 CLOSE c_sca;
366 IF v_sca_rec.course_attempt_status = 'UNCONFIRM' THEN
367 -- There is no start date requirement for
368 -- the IGS_RE_CANDIDATURE.
369 RETURN IGS_GE_DATE.IGSDATE(NULL);
370 ELSE
371 -- Get research IGS_PS_COURSE type
372 respl_get_rsch_cty (
373 v_ca_rec.sca_course_cd,
374 v_sca_rec.version_number,
375 v_research_type_ind);
376 IF v_research_type_ind = 'Y' THEN
377 -- Set start date to
378 -- IGS_EN_STDNT_PS_ATT.commencement_dt
379 RETURN v_sca_rec.commencement_dt;
380 ELSE
381 -- Set start date to earliest research IGS_PS_UNIT
382 -- enrolment.
383 v_research_unit_start_dt := respl_get_rsch_enrlmnt(
384 p_person_id,
385 v_ca_rec.sca_course_cd);
386 RETURN v_research_unit_start_dt;
387 END IF;
388 END IF;
389 ELSE
390 OPEN c_acai(
391 v_ca_rec.acai_admission_appl_number,
392 v_ca_rec.acai_nominated_course_cd,
393 v_ca_rec.acai_sequence_number);
394 FETCH c_acai INTO v_acai_rec;
395 IF c_acai%NOTFOUND THEN
396 CLOSE c_acai;
397 RETURN IGS_GE_DATE.IGSDATE(NULL);
398 END IF;
399 CLOSE c_acai;
400 v_s_adm_outcome_status := IGS_AD_GEN_008.admp_get_saos (
401 v_acai_rec.adm_outcome_status);
402 IF v_s_adm_outcome_status IN ('OFFER','COND-OFFER') OR
403 p_parent = cst_acai THEN
404
405 respl_get_rsch_cty (
406 v_acai_rec.course_cd,
407 v_acai_rec.crv_version_number,
408 v_research_type_ind);
409 IF v_research_type_ind = 'N' THEN
410 -- Determine if research is a mandatory step
411 OPEN c_apcs (
412 v_ca_rec.acai_admission_appl_number);
413 FETCH c_apcs INTO v_s_admission_process_type;
414 IF c_apcs%FOUND AND
415 v_s_admission_process_type <> 'RE-ADMIT' THEN
416 CLOSE c_apcs;
417
418 --If program attempt exists then starts date should be compared against the program attempt start date
419 --else proposed commencement date of application instance should be taken into consideration
420 --This validation is added as a part of bug#3815825.
421 OPEN c_sca(v_ca_rec.sca_course_cd);
422 FETCH c_sca INTO v_sca_rec1;
423 IF c_sca%FOUND THEN
424 CLOSE c_sca;
425 RETURN v_sca_rec1.commencement_dt;
426 END IF;
427 CLOSE c_sca;
428
429 IF v_acai_rec.prpsd_commencement_dt IS NULL THEN
430 v_sca_rec.commencement_dt :=
431 IGS_EN_GEN_002.enrp_get_acad_comm (
432 NULL,
433 NULL,
434 p_person_id,
435 v_acai_rec.course_cd,
436 v_ca_rec.acai_admission_appl_number,
437 v_ca_rec.acai_nominated_course_cd,
438 v_ca_rec.acai_sequence_number,
439 'N');
440 ELSE
441 v_sca_rec.commencement_dt :=
442 v_acai_rec.prpsd_commencement_dt;
443 END IF;
444 RETURN v_sca_rec.commencement_dt;
445 ELSE
446 IF c_apcs%NOTFOUND THEN
447 CLOSE c_apcs;
448 END IF;
449 v_research_unit_start_dt := respl_get_rsch_enrlmnt(
450 p_person_id,
451 v_acai_rec.course_cd);
452 RETURN v_research_unit_start_dt;
453 END IF;
454 ELSE
455 --If program attempt exists then starts date should be compared against the program attempt start date
456 --else proposed commencement date of application instance should be taken into consideration
457 --This validation is added as a part of bug#3815825.
458 OPEN c_sca(v_ca_rec.sca_course_cd);
459 FETCH c_sca INTO v_sca_rec1;
460 IF c_sca%FOUND THEN
461 CLOSE c_sca;
462 RETURN v_sca_rec1.commencement_dt;
463 END IF;
464 CLOSE c_sca;
465
466 IF v_acai_rec.prpsd_commencement_dt IS NULL THEN
467 v_sca_rec.commencement_dt :=
468 IGS_EN_GEN_002.enrp_get_acad_comm (
469 NULL,
470 NULL,
471 p_person_id,
472 v_acai_rec.course_cd,
473 v_ca_rec.acai_admission_appl_number,
474 v_ca_rec.acai_nominated_course_cd,
475 v_ca_rec.acai_sequence_number,
476 'N');
477 ELSE
478 v_sca_rec.commencement_dt :=
479 v_acai_rec.prpsd_commencement_dt;
480 END IF;
481 RETURN v_sca_rec.commencement_dt;
482 END IF;
483 ELSE
484 -- There is no start date requirement yet.
485 RETURN IGS_GE_DATE.IGSDATE(NULL);
486 END IF;
487 END IF;
488 EXCEPTION
489 WHEN OTHERS THEN
490 IF c_ca%ISOPEN THEN
491 CLOSE c_ca;
492 END IF;
493 IF c_sca%ISOPEN THEN
494 CLOSE c_sca;
495 END IF;
496 IF c_acai%ISOPEN THEN
497 CLOSE c_acai;
498 END IF;
499 IF c_apcs%ISOPEN THEN
500 CLOSE c_apcs;
501 END IF;
502 RAISE;
503 END;
504 END resp_get_rsup_start;
505
506
507 PROCEDURE resp_get_sca_ca_acai(
508 p_person_id IN NUMBER ,
509 p_course_cd IN VARCHAR2 ,
510 p_admission_appl_number IN NUMBER ,
511 p_nominated_course_cd IN VARCHAR2 ,
512 p_acai_sequence_number IN NUMBER ,
513 p_out_admission_appl_number OUT NOCOPY NUMBER ,
514 p_out_nominated_course_cd OUT NOCOPY VARCHAR2 ,
515 p_out_acai_sequence_number OUT NOCOPY NUMBER )
516 AS
517 BEGIN -- resp_get_sca_ca_acai
518 -- Return the admission IGS_PS_COURSE application instance to be used for
519 -- the reasearch IGS_RE_CANDIDATURE.
520 DECLARE
521 CURSOR c_sca IS
522 SELECT sca.adm_admission_appl_number,
523 sca.adm_nominated_course_cd,
524 sca.adm_sequence_number
525 FROM IGS_EN_STDNT_PS_ATT sca,
526 IGS_RE_CANDIDATURE ca
527 WHERE sca.person_id = p_person_id AND
528 sca.course_cd = p_course_cd AND
529 ca.person_id = sca.person_id AND
530 ca.sca_course_cd = sca.course_cd;
531 v_sca_rec c_sca%ROWTYPE;
532 BEGIN
533 OPEN c_sca;
534 FETCH c_sca INTO v_sca_rec;
535 IF c_sca%FOUND THEN
536 CLOSE c_sca;
537 p_out_admission_appl_number := v_sca_rec.adm_admission_appl_number;
538 p_out_nominated_course_cd := v_sca_rec.adm_nominated_course_cd;
539 p_out_acai_sequence_number := v_sca_rec.adm_sequence_number;
540 ELSE
541 CLOSE c_sca;
542 p_out_admission_appl_number := p_admission_appl_number;
543 p_out_nominated_course_cd := p_nominated_course_cd;
544 p_out_acai_sequence_number := p_acai_sequence_number;
545 END IF;
546 END;
547 EXCEPTION
548 WHEN OTHERS THEN
549 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
550 IGS_GE_MSG_STACK.ADD;
551 App_Exception.Raise_Exception;
552 END resp_get_sca_ca_acai;
553
554
555 FUNCTION RESP_GET_SUA_EFTD(
556 p_person_id IN NUMBER ,
557 p_course_cd IN VARCHAR2 ,
558 p_unit_cd IN VARCHAR2 ,
559 p_unit_version_number IN NUMBER ,
560 p_cal_type IN VARCHAR2 ,
561 p_ci_sequence_number IN NUMBER ,
562 p_effective_dt IN DATE ,
563 p_load_cal_type IN VARCHAR2 ,
564 p_load_ci_sequence_number IN NUMBER ,
565 p_cal_type_eftd OUT NOCOPY NUMBER )
566 RETURN NUMBER AS
567 BEGIN -- resp_get_sua_eftd
568 -- Get the EFTD (Effective Full Time Day) figure for a nominated student
569 -- IGS_PS_UNIT attempt. This is calculated as :
570 -- The number of research days * The attendance percentage as at the
571 -- effective date.
572 -- add Days at a higher attendance percentage which are prior to the
573 -- effective date
574 -- less Days at a lower attendance percentage which are prior to the
575 -- effective date
576 -- less Days student is on intermission at the default rate for their
577 -- attendance type
578 -- less Days prior to the IGS_PS_COURSE commencement date, where the date is
579 -- within the teaching period
580 DECLARE
581 v_research_unit_ind IGS_PS_UNIT_VER.research_unit_ind%TYPE;
582 v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
583 v_acai_admission_appl_number IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE;
584 v_acai_nominated_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE;
585 v_acai_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE;
586 v_ca_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
587 v_commencement_dt DATE;
588 v_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
589 v_sca_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
590 v_record_found BOOLEAN DEFAULT FALSE;
591 v_cah2_hist_end_dt IGS_RE_CDT_ATT_HIST.hist_end_dt%TYPE;
592 v_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
593 v_tp_days NUMBER;
594 v_effective_start_dt DATE;
595 v_effective_end_dt DATE;
596 v_attendance_percentage NUMBER;
597 v_baseline_eftd NUMBER;
598 v_removal_days NUMBER;
599 v_hist_start_dt DATE;
600 v_hist_end_dt DATE;
601 v_diff_days NUMBER;
602 v_intermit_start_dt DATE;
603 v_intermit_end_dt DATE;
604 v_remove_start_dt DATE;
605 v_remove_end_dt DATE;
606 v_last_hist_end_dt DATE;
607 v_diff_percentage NUMBER;
608 CURSOR c_uv IS
609 SELECT uv.research_unit_ind
610 FROM IGS_PS_UNIT_VER uv
611 WHERE uv.unit_cd = p_unit_cd AND
612 uv.version_number = p_unit_version_number;
613 CURSOR c_ca IS
614 SELECT ca.sequence_number,
615 ca.attendance_percentage,
616 ca.sca_course_cd,
617 ca.acai_admission_appl_number,
618 ca.acai_nominated_course_cd,
619 ca.acai_sequence_number
620 FROM IGS_RE_CANDIDATURE ca
621 WHERE ca.person_id = p_person_id AND
622 ca.sca_course_cd = p_course_cd;
623 CURSOR c_sca IS
624 SELECT sca.attendance_type,
625 sca.commencement_dt
626 FROM IGS_EN_STDNT_PS_ATT sca,
627 IGS_PS_VER crv
628 WHERE sca.person_id = p_person_id AND
629 sca.course_cd = p_course_cd;
630 CURSOR c_cah1 (
631 cp_effective_start_dt DATE,
632 cp_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE)
633 IS
634 SELECT cah.hist_start_dt,
635 cah.hist_end_dt,
636 cah.attendance_percentage
637 FROM IGS_RE_CDT_ATT_HIST cah
638 WHERE cah.person_id = p_person_id AND
639 cah.ca_sequence_number = v_ca_sequence_number AND
640 cah.hist_end_dt >= cp_effective_start_dt AND
641 cah.hist_start_dt <= p_effective_dt
642 ORDER BY cah.hist_start_dt;
643 CURSOR c_sci (
644 cp_effective_start_dt DATE)
645 IS
646 SELECT sci.start_dt,
647 sci.end_dt
648 FROM IGS_EN_STDNT_PS_INTM sci,
649 IGS_EN_INTM_TYPES eit
650 WHERE sci.person_id = p_person_id AND
651 sci.course_cd = p_course_cd AND
652 sci.start_dt <= p_effective_dt AND
653 sci.end_dt >= cp_effective_start_dt AND
654 sci.logical_delete_date = TO_DATE('31-12-4712','DD-MM-YYYY') AND
655 sci.approved = eit.appr_reqd_ind AND
656 eit.intermission_type = sci.intermission_type
657 ORDER BY sci.start_dt ASC;
658
659 CURSOR c_cah2 (
660 cp_ca_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE,
661 cp_intermit_end_dt DATE,
662 cp_intermit_start_dt DATE)
663 IS
664 SELECT cah.hist_start_dt,
665 cah.hist_end_dt,
666 cah.attendance_percentage
667 FROM IGS_RE_CDT_ATT_HIST cah
668 WHERE cah.person_id = p_person_id AND
669 cah.ca_sequence_number = cp_ca_sequence_number AND
670 cah.hist_start_dt <= cp_intermit_end_dt AND
671 cah.hist_start_dt <= p_effective_dt AND
672 cah.hist_end_dt >= cp_intermit_start_dt
673 ORDER BY cah.hist_start_dt;
674 BEGIN
675 --1. Load details from the IGS_PS_UNIT version
676 OPEN c_uv;
677 FETCH c_uv INTO v_research_unit_ind;
678 IF c_uv%NOTFOUND THEN
679 CLOSE c_uv;
680 RETURN 0;
681 END IF;
682 CLOSE c_uv;
683 IF v_research_unit_ind = 'N' THEN
684 --IGS_PS_UNIT is not research load 'derivable'
685 RETURN 0;
686 END IF;
687 --2. Load IGS_RE_CANDIDATURE details
688 OPEN c_ca;
689 FETCH c_ca INTO
690 v_ca_sequence_number,
691 v_ca_attendance_percentage,
692 v_sca_course_cd,
693 v_acai_admission_appl_number,
694 v_acai_nominated_course_cd,
695 v_acai_sequence_number;
696 IF c_ca%NOTFOUND THEN
697 CLOSE c_ca;
698 --If no IGS_RE_CANDIDATURE then the student is not a research student
699 RETURN 0;
700 END IF;
701 CLOSE c_ca;
702 --3. Get the commencement date - this routine handles both enrolled students
703 -- and students still in the admission stage.
704 v_commencement_dt := IGS_RE_GEN_001.resp_get_ca_comm(
705 p_person_id,
706 v_sca_course_cd,
707 v_acai_admission_appl_number,
708 v_acai_nominated_course_cd,
709 v_acai_sequence_number);
710 --4. Load details from the student IGS_PS_COURSE attempt
711 OPEN c_sca;
712 FETCH c_sca INTO
713 v_attendance_type,
714 v_sca_commencement_dt;
715 CLOSE c_sca;
716 -- 5. Call routine to determine the number of effective days in the teaching
717 -- period. If zero, then EFTSU cannot be calculated.
718 v_tp_days := resp_get_teach_days(
719 p_cal_type,
720 p_ci_sequence_number,
721 v_effective_start_dt,
722 v_effective_end_dt);
723 IF v_tp_days = 0 THEN
724 p_cal_type_eftd := 0;
725 RETURN 0;
726 END IF;
727 p_cal_type_eftd := v_tp_days;
728 -- 6. Calculate the students attendance percentage as at the effective date
729 v_attendance_percentage := IGS_RE_GEN_001.resp_get_ca_att(
730 p_person_id,
731 p_course_cd,
732 p_effective_dt,
733 v_ca_sequence_number,
734 v_attendance_type,
735 v_ca_attendance_percentage);
736 IF v_attendance_percentage IS NULL THEN
737 --Null attendance percentage indicates value could not be determined
738 RETURN 0;
739 END IF;
740 -- 7. Calculate the 'baseline' EFTD figure using the attendance type as at
741 -- the effective date.
742 v_baseline_eftd := v_tp_days * ( v_attendance_percentage / 100 );
743 -- 8. Subtract any commencement period from the figure.
744 IF v_commencement_dt BETWEEN v_effective_start_dt AND
745 v_effective_end_dt THEN
746 v_removal_days := (v_commencement_dt - v_effective_start_dt);
747 v_baseline_eftd := v_baseline_EFTD -
748 (v_removal_days * (v_attendance_percentage / 100));
749 -- Bring the effective start date up to the commencement date. All further
750 -- calculations will exclude this period.
751 v_effective_start_dt := v_commencement_dt;
752 END IF;
753 --9. Subtract/Add any periods of higher/lower percentage. Loop through
754 -- attendance history to determine the values. Only consider histories
755 -- which are between the earliest of commencement date / effective start
756 -- date and the effective date of the calculation.
757 FOR v_cah1_rec IN c_cah1(
758 v_effective_start_dt,
759 v_ca_sequence_number) LOOP
760 IF v_cah1_rec.hist_start_dt >= v_effective_start_dt THEN
761 v_hist_start_dt := v_cah1_rec.hist_start_dt;
762 ELSE
763 v_hist_start_dt := v_effective_start_dt;
764 END IF;
765 IF v_cah1_rec.hist_end_dt <= p_effective_dt THEN
766 v_hist_end_dt := v_cah1_rec.hist_end_dt;
767 ELSE
768 v_hist_end_dt := p_effective_dt;
769 END IF;
770 v_diff_days := TRUNC(v_hist_end_dt) - TRUNC(v_hist_start_dt) + 1 ;
771 IF v_cah1_rec.attendance_percentage > v_attendance_percentage THEN
772 -- As history is higher add the difference in percentage to the total
773 v_baseline_eftd := v_baseline_eftd +
774 (((v_cah1_rec.attendance_percentage - v_attendance_percentage)
775 / 100) * v_diff_days);
776 ELSE
777 -- As history is lower subtract the difference in percentage from the total
778 v_baseline_eftd := v_baseline_eftd -
779 (((v_attendance_percentage - v_cah1_rec.attendance_percentage)
780 / 100) * v_diff_days);
781 END IF;
782 END LOOP;
783 -- 10. During periods of intermission remove EFTD from the baseline figure
784 -- to the equivalent of a 0% attendance. Only consider intermission up until
785 -- the teaching period effective end date - anything beyond remains at the
786 -- relevant attendance percentage.
787 FOR v_sci_rec IN c_sci(
788 v_effective_start_dt) LOOP
789 IF v_sci_rec.start_dt >= v_effective_start_dt THEN
790 v_intermit_start_dt := v_sci_rec.start_dt;
791 ELSE
792 v_intermit_start_dt := v_effective_start_dt;
793 END IF;
794 --Determine effective end date - this can be up until the effective end date
795 -- of the teaching period.
796 IF (v_sci_rec.end_dt ) <= v_effective_end_dt THEN
797 v_intermit_end_dt := (v_sci_rec.end_dt );
798 ELSE
799 v_intermit_end_dt := v_effective_end_dt;
800 END IF;
801 FOR v_cah2_rec IN c_cah2 (
802 v_ca_sequence_number,
803 v_intermit_end_dt,
804 v_intermit_start_dt) LOOP
805 v_record_found := TRUE;
806 v_cah2_hist_end_dt := v_cah2_rec.hist_end_dt;
807 IF v_intermit_start_dt >= v_cah2_rec.hist_start_dt THEN
808 v_remove_start_dt := v_intermit_start_dt;
809 ELSE
810 v_remove_start_dt := v_cah2_rec.hist_start_dt;
811 END IF;
812 IF v_intermit_end_dt <= v_cah2_rec.hist_end_dt THEN
813 v_remove_end_dt := v_intermit_end_dt;
814 ELSE
815 v_remove_end_dt := v_cah2_rec.hist_end_dt;
816 END IF;
817 v_baseline_eftd := v_baseline_eftd -
818 (( TRUNC(v_remove_end_dt) - TRUNC(v_remove_start_dt) + 1 )) *
819 (v_cah2_rec.attendance_percentage / 100 );
820 END LOOP;
821 IF v_record_found = FALSE THEN
822 v_last_hist_end_dt := v_intermit_start_dt - 1;
823 --(-1 allows for exclusive logic)
824 ELSE
825 v_last_hist_end_dt := v_cah2_hist_end_dt; -- (if last record processed)
826 END IF;
827 IF v_last_hist_end_dt < v_intermit_end_dt THEN
828 v_diff_percentage := IGS_RE_GEN_001.resp_get_ca_att(
829 p_person_id,
830 p_course_cd,
831 v_last_hist_end_dt,
832 v_ca_sequence_number,
833 v_attendance_type,
834 v_ca_attendance_percentage);
835 -- If the effective history did not cover until the end of the intermission
836 -- period then subtract based on the load as at the effective date.
837 v_baseline_eftd := v_baseline_eftd -
838 (( v_intermit_end_dt - v_last_hist_end_dt - 1 ) *
839 ( v_diff_percentage / 100));
840 END IF;
841 END LOOP;
842 RETURN v_baseline_eftd;
843 EXCEPTION
844 WHEN OTHERS THEN
845 IF (c_uv%ISOPEN) THEN
846 CLOSE c_uv;
847 END IF;
848 IF (c_ca%ISOPEN) THEN
849 CLOSE c_ca;
850 END IF;
851 IF (c_sca%ISOPEN) THEN
852 CLOSE c_sca;
853 END IF;
854 IF (c_cah1%ISOPEN) THEN
855 CLOSE c_cah1;
856 END IF;
857 IF (c_sci%ISOPEN) THEN
858 CLOSE c_sci;
859 END IF;
860 IF (c_cah2%ISOPEN) THEN
861 CLOSE c_cah2;
862 END IF;
863 RAISE;
864 END;
865 END resp_get_sua_eftd;
866
867
868 FUNCTION RESP_GET_TEACH_DAYS(
869 p_cal_type IN VARCHAR2 ,
870 p_ci_sequence_number IN NUMBER ,
871 p_start_dt OUT NOCOPY DATE ,
872 p_end_dt OUT NOCOPY DATE )
873 RETURN NUMBER AS
874 BEGIN -- resp_get_teach_days
875 -- Get the number of days which apply to research students in a nominated
876 -- teaching calendar instance.
877 -- This figure is determined from the research effective date aliases, of
878 -- which there should be a single start date and a single end date.
879 -- If zero is returned, it indicates that the structure is not correctly
880 -- set up for the nominated teaching period to be a research teaching
881 -- period.
882 DECLARE
883 E_NO_RESEARCH_CAL_CONFIG EXCEPTION;
884 v_effective_strt_dt_alias IGS_RE_S_RES_CAL_CON.effective_strt_dt_alias%TYPE;
885 v_effective_end_dt_alias IGS_RE_S_RES_CAL_CON.effective_end_dt_alias%TYPE;
886 v_start_alias_val IGS_CA_DA_INST.absolute_val%TYPE DEFAULT NULL;
887 v_end_alias_val IGS_CA_DA_INST.absolute_val%TYPE DEFAULT NULL;
888 CURSOR c_srcc IS
889 SELECT srcc.effective_strt_dt_alias,
890 srcc.effective_end_dt_alias
891 FROM IGS_RE_S_RES_CAL_CON srcc
892 WHERE srcc.s_control_num = 1;
893 FUNCTION resp_get_alias_val(
894 p_cal_type IGS_CA_INST.cal_type%TYPE,
895 p_ci_sequence_number IGS_CA_INST.sequence_number%TYPE,
896 p_effective_dt_alias IGS_CA_DA_INST.dt_alias%TYPE)
897 RETURN DATE aS
898 BEGIN
899 DECLARE
900 v_num_recs_flag BOOLEAN DEFAULT FALSE;
901 v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;
902 CURSOR c_dai IS
903 SELECT NVL(
904 dai.absolute_val,
905 IGS_CA_GEN_001.calp_get_alias_val(
906 dai.dt_alias,
907 dai.sequence_number,
908 dai.cal_type,
909 dai.ci_sequence_number)) AS v_absolute_val
910 FROM IGS_CA_DA_INST dai
911 WHERE dai.cal_type = p_cal_type AND
912 dai.ci_sequence_number = p_ci_sequence_number AND
913 dai.dt_alias = p_effective_dt_alias;
914 BEGIN
915 -- Function selects a date from the teaching period, if zero or multiple
916 -- values exist it returns null
917 FOR v_dai_rec IN c_dai LOOP
918 IF c_dai%ROWCOUNT = 1 THEN
919 v_alias_val := v_dai_rec.v_absolute_val;
920 v_num_recs_flag := TRUE;
921 ELSE
922 v_num_recs_flag := FALSE;
923 EXIT;
924 END IF;
925 END LOOP;
926 IF v_num_recs_flag THEN
927 RETURN v_alias_val;
928 END IF;
929 RETURN NULL;
930 EXCEPTION
931 WHEN OTHERS THEN
932 IF c_dai%ISOPEN THEN
933 CLOSE c_dai;
934 END IF;
935 RAISE;
936 END;
937 END resp_get_alias_val;
938 BEGIN
939 OPEN c_srcc;
940 FETCH c_srcc INTO v_effective_strt_dt_alias,
941 v_effective_end_dt_alias;
942 IF c_srcc%NOTFOUND THEN
943 CLOSE c_srcc;
944 RAISE NO_DATA_FOUND;
945 END IF;
946 CLOSE c_srcc;
947 -- Select the start date from the teaching period
948 v_start_alias_val := resp_get_alias_val(
949 p_cal_type,
950 p_ci_sequence_number,
951 v_effective_strt_dt_alias);
952 IF v_start_alias_val IS NULL THEN
953 RETURN 0;
954 END IF;
955 -- Select the end date from the teaching period
956 v_end_alias_val := resp_get_alias_val(
957 p_cal_type,
958 p_ci_sequence_number,
959 v_effective_end_dt_alias);
960 IF v_end_alias_val IS NULL THEN
961 RETURN 0;
962 END IF;
963 -- Check that the end date is after the start date
964 IF v_start_alias_val > v_end_alias_val THEN
965 RETURN 0;
966 END IF;
967 p_start_dt := v_start_alias_val;
968 p_end_dt := v_end_alias_val;
969 RETURN ( v_end_alias_val - v_start_alias_val + 1 );
970 EXCEPTION
971 WHEN OTHERS THEN
972 IF c_srcc%ISOPEN THEN
973 CLOSE c_srcc;
974 END IF;
975 RAISE;
976 END;
977 END resp_get_teach_days;
978
979
980 PROCEDURE RESP_GET_THE_EXISTS(
981 p_person_id IN NUMBER ,
982 p_ca_sequence_number IN NUMBER ,
983 p_the_sequence_number IN NUMBER ,
984 p_check_thesis_exam IN boolean ,
985 p_check_milestone IN boolean ,
986 p_thesis_exam_exists OUT NOCOPY boolean ,
987 p_milestone_exists OUT NOCOPY boolean )
988 AS
989 BEGIN -- resp_get_the_exists
990 -- This modulew returns output oarameters indicating whether
991 -- or not data exists on IGS_RE_CANDIDATURE detail tables for the
992 -- specified IGS_RE_THESIS.
993 DECLARE
994 v_the_found VARCHAR2(1);
995 v_mil_found VARCHAR2(1);
996 CURSOR c_the IS
997 SELECT 'x'
998 FROM IGS_RE_THESIS_EXAM tex
999 WHERE tex.person_id = p_person_id AND
1000 tex.ca_sequence_number = p_ca_sequence_number AND
1001 tex.the_sequence_number = p_the_sequence_number;
1002 CURSOR c_mil IS
1003 SELECT 'x'
1004 FROM IGS_PR_MILESTONE mil
1005 WHERE mil.person_id = p_person_id AND
1006 mil.ca_sequence_number = p_ca_sequence_number;
1007 BEGIN
1008 -- Initialise output parameters.
1009 p_thesis_exam_exists := FALSE;
1010 p_milestone_exists := FALSE;
1011 -- Check IGS_RE_THESIS exam
1012 IF p_check_thesis_exam THEN
1013 OPEN c_the;
1014 FETCH c_the INTO v_the_found;
1015 IF c_the%FOUND THEN
1016 CLOSE c_the;
1017 p_thesis_exam_exists := TRUE;
1018 ELSE
1019 CLOSE c_the;
1020 END IF;
1021 END IF;
1022 -- Check IGS_PR_MILESTONE
1023 IF p_check_milestone THEN
1024 OPEN c_mil;
1025 FETCH c_mil INTO v_mil_found;
1026 IF c_mil%FOUND THEN
1027 CLOSE c_mil;
1028 p_milestone_exists := TRUE;
1029 ELSE
1030 CLOSE c_mil;
1031 END IF;
1032 END IF;
1033 EXCEPTION
1034 WHEN OTHERS THEN
1035 IF c_the%ISOPEN THEN
1036 CLOSE c_the;
1037 END IF;
1038 IF c_mil%ISOPEN THEN
1039 CLOSE c_mil;
1040 END IF;
1041 RAISE;
1042 END;
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1046 IGS_GE_MSG_STACK.ADD;
1047 App_Exception.Raise_Exception;
1048 END resp_get_the_exists;
1049
1050
1051 FUNCTION RESP_GET_THE_STATUS(
1052 p_person_id IN NUMBER ,
1053 p_ca_sequence_number IN NUMBER ,
1054 p_the_sequence_number IN NUMBER ,
1055 p_details_passed_ind IN VARCHAR2 ,
1056 p_logical_delete_dt IN DATE ,
1057 p_thesis_result_cd IN VARCHAR2 )
1058 RETURN VARCHAR2 AS
1059 BEGIN -- resp_get_the_status
1060 -- Get the IGS_RE_THESIS status.
1061 -- Values are:
1062 -- PENDING - IGS_RE_THESIS detail has been keyed, but has not yet been submitted
1063 -- SUBMITTED - IGS_RE_THESIS has been submitted and is being processed in some way
1064 -- EXAMINED - IGS_RE_THESIS has been submitted and examined and a final outcome
1065 -- entered.
1066 -- DELETED - IGS_RE_THESIS has been logically deleted and no longer applies to the
1067 -- research
1068 DECLARE
1069 cst_deleted CONSTANT VARCHAR2(10) := 'DELETED';
1070 cst_examined CONSTANT VARCHAR2(10) := 'EXAMINED';
1071 cst_submitted CONSTANT VARCHAR2(10) := 'SUBMITTED';
1072 cst_pending CONSTANT VARCHAR2(10) := 'PENDING';
1073 CURSOR c_the IS
1074 SELECT thes.logical_delete_dt,
1075 thes.thesis_result_cd
1076 FROM IGS_RE_THESIS thes
1077 WHERE thes.person_id = p_person_id AND
1078 thes.ca_sequence_number = p_ca_sequence_number AND
1079 thes.sequence_number = p_the_sequence_number;
1080 v_the_rec c_the%ROWTYPE;
1081 CURSOR c_tex IS
1082 SELECT 'x'
1083 FROM IGS_RE_THESIS_EXAM tex
1084 WHERE person_id = p_person_id AND
1085 ca_sequence_number = p_ca_sequence_number AND
1086 the_sequence_number = p_the_sequence_number AND
1087 submission_dt IS NOT NULL;
1088 v_tex_exists VARCHAR2(1);
1089 BEGIN
1090 IF p_details_passed_ind = 'N' THEN
1091 --Select details from the IGS_RE_THESIS table.
1092 OPEN c_the;
1093 FETCH c_the INTO v_the_rec;
1094 IF c_the%NOTFOUND THEN
1095 CLOSE c_the;
1096 RETURN NULL;
1097 END IF;
1098 CLOSE c_the;
1099 ELSE
1100 v_the_rec.logical_delete_dt := p_logical_delete_dt;
1101 v_the_rec.thesis_result_cd := p_thesis_result_cd;
1102 END IF;
1103 --Logical deletion will always take priority over all other statuses
1104 IF v_the_rec.logical_delete_dt IS NOT NULL THEN
1105 RETURN cst_deleted;
1106 END IF;
1107 --If final outcome exists then return examined-any submissions are irrelevant
1108 IF v_the_rec.thesis_result_cd IS NOT NULL THEN
1109 RETURN cst_examined;
1110 END IF;
1111 --Select detail from the IGS_RE_THESIS examinations section
1112 OPEN c_tex;
1113 FETCH c_tex INTO v_tex_exists;
1114 IF c_tex%FOUND THEN
1115 CLOSE c_tex;
1116 RETURN cst_submitted;
1117 END IF;
1118 CLOSE c_tex;
1119 --If none of the above, then 'PENDING'
1120 RETURN cst_pending;
1121 END;
1122 END resp_get_the_status;
1123
1124
1125 FUNCTION resp_ins_ca_cah(
1126 p_person_id IN NUMBER ,
1127 p_ca_sequence_number IN NUMBER ,
1128 p_sca_course_cd IN VARCHAR2 ,
1129 p_old_attendance_percentage IN NUMBER ,
1130 p_message_name OUT NOCOPY VARCHAR2 )
1131 RETURN BOOLEAN AS
1132 BEGIN -- resp_ins_ca_cah
1133 -- This modules inserts into IGS_RE_CDT_ATT_HIST when
1134 -- IGS_RE_CANDIDATURE.attendance_percentage is changed. The following is validated:
1135 -- IGS_RE_CANDIDATURE requires attendance history details to be retained.
1136 DECLARE
1137 v_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
1138 v_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
1139 v_message_name VARCHAR2(30);
1140 v_hist_end_dt IGS_RE_CDT_ATT_HIST.hist_end_dt%TYPE;
1141 v_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
1142 v_hist_start_dt IGS_RE_CDT_ATT_HIST.hist_start_dt%TYPE;
1143 v_sequence_number IGS_RE_CDT_ATT_HIST.sequence_number%TYPE;
1144
1145 LV_ROWID VARCHAR2(25);
1146 v_org_id IGS_RE_CDT_ATT_HIST.org_id%TYPE := IGS_GE_GEN_003.Get_Org_Id;
1147 CURSOR c_cah IS
1148 SELECT cah.hist_end_dt
1149 FROM IGS_RE_CDT_ATT_HIST cah
1150 WHERE cah.person_id = p_person_id AND
1151 cah.ca_sequence_number = p_ca_sequence_number
1152 ORDER BY cah.hist_end_dt DESC;
1153 CURSOR c_cah2 IS
1154 SELECT NVL(max(cah2.sequence_number),0)+1
1155 FROM IGS_RE_CDT_ATT_HIST cah2
1156 WHERE cah2.person_id = p_person_id AND
1157 cah2.ca_sequence_number = p_ca_sequence_number;
1158 BEGIN
1159 p_message_name := NULL;
1160 -- Validate that IGS_RE_CANDIDATURE attendance history is required
1161 IF IGS_RE_VAL_CAH.resp_val_cah_ca_ins(
1162 p_person_id,
1163 p_ca_sequence_number,
1164 p_sca_course_cd,
1165 v_commencement_dt,
1166 v_attendance_type,
1167 v_message_name) = FALSE THEN
1168 --IGS_RE_CANDIDATURE attendance history is not retained yet
1169 RETURN TRUE;
1170 END IF;
1171 -- Determine attendance percentage
1172 v_attendance_percentage := IGS_RE_GEN_001.resp_get_ca_att(
1173 p_person_id,
1174 p_sca_course_cd,
1175 TRUNC(SYSDATE),
1176 p_ca_sequence_number,
1177 v_attendance_type,
1178 p_old_attendance_percentage);
1179 --By bayadav as a part of bug 2399877
1180 --First history inserted, attendance % should be set the student
1181 -- to the histury table as it is a mandatory column in IGS_RE_CDT_ATT_HIST table
1182 IF v_attendance_percentage IS NULL THEN
1183 p_message_name := 'IGS_RE_ATT_PER_NOT_EXIST';
1184 RETURN FALSE;
1185 END IF;
1186
1187
1188 OPEN c_cah;
1189 FETCH c_cah INTO v_hist_end_dt;
1190 IF c_cah%NOTFOUND THEN
1191 CLOSE c_cah;
1192 --First history inserted, start date should be set the student
1193 -- IGS_PS_COURSE attempt Commencement date
1194 IF v_commencement_dt IS NULL THEN
1195 p_message_name := 'IGS_RE_FIRST_HIST_CANT_INSERT';
1196 RETURN FALSE;
1197 ELSE
1198 v_hist_start_dt := v_commencement_dt;
1199 END IF;
1200 ELSE
1201 CLOSE c_cah;
1202 -- History start date should be set to latest history end date plus a day
1203 v_hist_start_dt := v_hist_end_dt + 1;
1204 END IF;
1205 IF v_hist_start_dt >= TRUNC(SYSDATE) THEN
1206 -- Changes not required in history, more than one change in a day or
1207 -- Commencement date has not been reached
1208 RETURN TRUE;
1209 END IF;
1210 -- Get next sequence in parent
1211 OPEN c_cah2;
1212 FETCH c_cah2 INTO v_sequence_number;
1213 CLOSE c_cah2;
1214
1215 IGS_RE_CDT_ATT_HIST_PKG.INSERT_ROW( X_ROWID => LV_ROWID,
1216 X_PERSON_ID => p_person_id,
1217 X_CA_SEQUENCE_NUMBER => p_ca_sequence_number,
1218 X_SEQUENCE_NUMBER => v_sequence_number,
1219 X_HIST_START_DT => v_hist_start_dt,
1220 X_HIST_END_DT => TRUNC(SYSDATE) - 1,
1221 X_ATTENDANCE_TYPE => v_attendance_type,
1222 X_ATTENDANCE_PERCENTAGE => v_attendance_percentage,
1223 X_ORG_ID => v_org_id,
1224 X_MODE => 'R');
1225
1226 -- Warn that IGS_RE_CANDIDATURE attendance history has been inserted
1227 p_message_name := 'IGS_RE_CAND_ATT_HIST_INSERTED';
1228 RETURN TRUE;
1229 EXCEPTION
1230 WHEN OTHERS THEN
1231 IF (c_cah%ISOPEN) THEN
1232 CLOSE c_cah;
1233 END IF;
1234 RAISE;
1235 END;
1236 EXCEPTION
1237 WHEN OTHERS THEN
1238 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1239 IGS_GE_MSG_STACK.ADD;
1240 App_Exception.Raise_Exception;
1241 END resp_ins_ca_cah;
1242
1243
1244 PROCEDURE resp_ins_ca_hist(
1245 p_person_id IN NUMBER ,
1246 p_sequence_number IN NUMBER ,
1247 p_old_sca_course_cd IN VARCHAR2 ,
1248 p_new_sca_course_cd IN VARCHAR2 ,
1249 p_old_acai_adm_appl_num IN NUMBER ,
1250 p_new_acai_adm_appl_num IN NUMBER ,
1251 p_old_acai_nominated_course_cd IN VARCHAR2 ,
1252 p_new_acai_nominated_course_cd IN VARCHAR2 ,
1253 p_old_acai_sequence_number IN NUMBER ,
1254 p_new_acai_sequence_number IN NUMBER ,
1255 p_old_attendance_percentage IN NUMBER ,
1256 p_new_attendance_percentage IN NUMBER ,
1257 p_old_govt_type_of_activity_cd IN VARCHAR2 ,
1258 p_new_govt_type_of_activity_cd IN VARCHAR2 ,
1259 p_old_max_submission_dt IN DATE ,
1260 p_new_max_submission_dt IN DATE ,
1261 p_old_min_submission_dt IN DATE ,
1262 p_new_min_submission_dt IN DATE ,
1263 p_old_research_topic IN VARCHAR2 ,
1264 p_new_research_topic IN VARCHAR2 ,
1265 p_old_industry_links IN VARCHAR2 ,
1266 p_new_industry_links IN VARCHAR2 ,
1267 p_old_update_who IN NUMBER ,
1268 p_new_update_who IN NUMBER ,
1269 p_old_update_on IN DATE ,
1270 p_new_update_on IN DATE )
1271 AS
1272 LV_ROWID VARCHAR2(25);
1273 v_org_id igs_re_cdt_att_hist.org_id%TYPE;
1274 BEGIN -- resp_ins_ca_hist
1275 -- Description: Insert IGS_RE_CANDIDATURE history (IGS_RE_CDT_HIST)
1276 v_org_id := IGS_GE_GEN_003.Get_Org_Id;
1277 DECLARE
1278 r_ch IGS_RE_CDT_HIST%ROWTYPE;
1279 v_create_history BOOLEAN := FALSE;
1280
1281 BEGIN
1282 IF NVL(p_new_sca_course_cd, 'NULL') <>
1283 NVL(p_old_sca_course_cd, 'NULL') THEN
1284 r_ch.sca_course_cd := p_old_sca_course_cd;
1285 v_create_history := TRUE;
1286 END IF;
1287 IF NVL(p_new_acai_adm_appl_num, -1) <>
1288 NVL(p_old_acai_adm_appl_num, -1) THEN
1289 r_ch.acai_admission_appl_number := p_old_acai_adm_appl_num;
1290 v_create_history := TRUE;
1291 END IF;
1292 IF NVL(p_new_acai_nominated_course_cd, 'NULL') <>
1293 NVL(p_old_acai_nominated_course_cd, 'NULL') THEN
1294 r_ch.acai_nominated_course_cd := p_old_acai_nominated_course_cd;
1295 v_create_history := TRUE;
1296 END IF;
1297 IF NVL(p_new_acai_sequence_number, -1) <>
1298 NVL(p_old_acai_sequence_number, -1) THEN
1299 r_ch.acai_sequence_number := p_old_acai_sequence_number;
1300 v_create_history := TRUE;
1301 END IF;
1302 IF NVL(p_new_attendance_percentage, -1) <>
1303 NVL(p_old_attendance_percentage, -1) THEN
1304 r_ch.attendance_percentage := p_old_attendance_percentage;
1305 v_create_history := TRUE;
1306 END IF;
1307 IF NVL(p_new_govt_type_of_activity_cd, 'NULL') <>
1308 NVL(p_old_govt_type_of_activity_cd, 'NULL') THEN
1309 r_ch.govt_type_of_activity_cd := p_old_govt_type_of_activity_cd;
1310 v_create_history := TRUE;
1311 END IF;
1312 IF NVL(p_new_max_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
1313 NVL(p_old_max_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
1314 r_ch.max_submission_dt := p_old_max_submission_dt;
1315 v_create_history := TRUE;
1316 END IF;
1317 IF NVL(p_new_min_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
1318 NVL(p_old_min_submission_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
1319 r_ch.min_submission_dt := p_old_min_submission_dt;
1320 v_create_history := TRUE;
1321 END IF;
1322 IF NVL(p_new_research_topic, 'NULL') <>
1323 NVL(p_old_research_topic, 'NULL') THEN
1324 r_ch.research_topic := p_old_research_topic;
1325 v_create_history := TRUE;
1326 END IF;
1327 IF NVL(p_new_industry_links, 'NULL') <>
1328 NVL(p_old_industry_links, 'NULL') THEN
1329 r_ch.industry_links := p_old_industry_links;
1330 v_create_history := TRUE;
1331 END IF;
1332 IF v_create_history = TRUE THEN
1333 r_ch.person_id := p_person_id;
1334 r_ch.sequence_number := p_sequence_number;
1335 r_ch.hist_start_dt := p_old_update_on;
1336 r_ch.hist_end_dt := p_new_update_on;
1337 r_ch.hist_who := p_old_update_who;
1338 IGS_RE_CDT_HIST_PKG.INSERT_ROW(
1339 X_ROWID => LV_ROWID,
1340 X_person_id => r_ch.person_id,
1341 X_sequence_number => r_ch.sequence_number,
1342 X_hist_start_dt => r_ch.hist_start_dt,
1343 X_hist_end_dt => r_ch.hist_end_dt,
1344 X_hist_who => r_ch.hist_who,
1345 X_sca_course_cd => r_ch.sca_course_cd,
1346 X_acai_admission_appl_number => r_ch.acai_admission_appl_number,
1347 X_acai_nominated_course_cd => r_ch.acai_nominated_course_cd,
1348 X_acai_sequence_number => r_ch.acai_sequence_number,
1349 X_attendance_percentage => r_ch.attendance_percentage,
1350 X_govt_type_of_activity_cd => r_ch.govt_type_of_activity_cd,
1351 X_max_submission_dt => r_ch.max_submission_dt,
1352 X_min_submission_dt => r_ch.min_submission_dt,
1353 X_research_topic => r_ch.research_topic,
1354 X_industry_links => r_ch.industry_links,
1355 X_ORG_ID => v_org_id,
1356 X_MODE => 'R');
1357
1358 END IF;
1359 END;
1360 EXCEPTION
1361 WHEN OTHERS THEN
1362 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1363 IGS_GE_MSG_STACK.ADD;
1364 App_Exception.Raise_Exception;
1365 END resp_ins_ca_hist;
1366
1367
1368 FUNCTION RESP_INS_DFLT_MIL(
1369 p_person_id IN NUMBER ,
1370 p_ca_sequence_number IN NUMBER ,
1371 p_message_name OUT NOCOPY VARCHAR2 )
1372 RETURN boolean AS
1373 /*******************************************************************************
1374 Created by : vkarthik, ORACLE IDC
1375 Date created: 26-Apr-2004
1376
1377 Known limitations/enhancements/remarks:
1378
1379 Change History: (who, when, what: NO CREATION RECORDS HERE!)
1380 Who When What
1381 vkarthik 26-Apr-2004 Removed the condition that inserts records from milestone
1382 set only if due date is in the present or future for
1383 EN303 Milestone build Enh#3577974
1384 *******************************************************************************/
1385 LV_ROWID VARCHAR2(25);
1386 v_org_id igs_re_cdt_att_hist.org_id%TYPE;
1387 BEGIN -- resp_ins_dflt_mil
1388 -- Insert default milestones against a IGS_RE_CANDIDATURE based on their
1389 -- IGS_PS_COURSE version.
1390 v_org_id := IGS_GE_GEN_003.Get_Org_Id;
1391
1392 DECLARE
1393 cst_planned CONSTANT VARCHAR2(10) := 'PLANNED';
1394 v_dummy VARCHAR2(1);
1395 v_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE;
1396 v_acai_admission_appl_number IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE;
1397 v_acai_nominated_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE;
1398 v_acai_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE;
1399 v_crv_version_number IGS_EN_STDNT_PS_ATT.version_number%TYPE;
1400 v_attendance_type IGS_EN_STDNT_PS_ATT.attendance_type%TYPE;
1401 v_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE;
1402 v_milestone_status IGS_PR_MS_STAT.milestone_status%TYPE;
1403 v_milestone_type IGS_RE_DFLT_MS_SET.milestone_type%TYPE;
1404 v_offset_days IGS_RE_DFLT_MS_SET.offset_days%TYPE;
1405 v_mil_sequence_number IGS_PR_MILESTONE.sequence_number%TYPE;
1406 v_commencement_dt DATE;
1407 v_records_inserted NUMBER;
1408
1409 CURSOR c_mil_exists (
1410 cp_milestone_type IGS_PR_MILESTONE.milestone_type%TYPE,
1411 cp_due_dt IGS_PR_MILESTONE.due_dt%TYPE) IS
1412 SELECT 'x'
1413 FROM IGS_PR_MILESTONE mil
1414 WHERE mil.person_id = p_person_id AND
1415 mil.ca_sequence_number = p_ca_sequence_number AND
1416 mil.milestone_type = cp_milestone_type AND
1417 mil.due_dt = cp_due_dt;
1418 CURSOR c_ca_detail IS
1419 SELECT ca.sca_course_cd,
1420 ca.acai_admission_appl_number,
1421 ca.acai_nominated_course_cd,
1422 ca.acai_sequence_number
1423 FROM IGS_RE_CANDIDATURE ca
1424 WHERE ca.person_id = p_person_id AND
1425 ca.sequence_number = p_ca_sequence_number;
1426 CURSOR c_sca (
1427 cp_sca_course_cd IGS_RE_CANDIDATURE.sca_course_cd%TYPE) IS
1428 SELECT sca.version_number,
1429 sca.attendance_type
1430 FROM IGS_EN_STDNT_PS_ATT sca
1431 WHERE sca.person_id = p_person_id AND
1432 sca.course_cd = cp_sca_course_cd;
1433 CURSOR c_acai (
1434 cp_acai_admission_appl_number IGS_RE_CANDIDATURE.acai_admission_appl_number%TYPE,
1435 cp_acai_nominated_course_cd IGS_RE_CANDIDATURE.acai_nominated_course_cd%TYPE,
1436 cp_acai_sequence_number IGS_RE_CANDIDATURE.acai_sequence_number%TYPE) IS
1437 SELECT acai.course_cd,
1438 acai.crv_version_number,
1439 acai.attendance_type
1440 FROM IGS_AD_PS_APPL_INST acai
1441 WHERE acai.person_id = p_person_id AND
1442 acai.admission_appl_number = cp_acai_admission_appl_number AND
1443 acai.nominated_course_cd = cp_acai_nominated_course_cd AND
1444 acai.sequence_number = cp_acai_sequence_number;
1445 CURSOR c_mst_planned IS
1446 SELECT mst.milestone_status
1447 FROM IGS_PR_MS_STAT mst
1448 WHERE mst.s_milestone_status = cst_planned
1449 AND mst.closed_ind = 'N'
1450 ORDER BY mst.milestone_status;
1451 CURSOR c_dms (
1452 cp_course_cd IGS_RE_DFLT_MS_SET.course_cd%TYPE,
1453 cp_crv_version_number IGS_RE_DFLT_MS_SET.version_number%TYPE,
1454 cp_attendance_type IGS_RE_DFLT_MS_SET.attendance_type%TYPE) IS
1455 SELECT dms.milestone_type,
1456 dms.offset_days
1457 FROM IGS_RE_DFLT_MS_SET dms,
1458 IGS_PR_MILESTONE_TYP mst
1459 WHERE dms.course_cd = cp_course_cd AND
1460 dms.version_number = cp_crv_version_number AND
1461 dms.attendance_type = cp_attendance_type AND
1462 mst.milestone_type = dms.milestone_type AND
1463 mst.closed_ind = 'N'
1464 ORDER BY offset_days;
1465 CURSOR c_mil_seq_num IS
1466 SELECT IGS_PR_MILESTONE_SEQ_NUM_S.NEXTVAL
1467 FROM DUAL;
1468 BEGIN
1469 -- Set default value
1470 p_message_name := null;
1471 v_records_inserted := 0;
1472 -- 2. Load details from the IGS_RE_CANDIDATURE table.
1473 OPEN c_ca_detail;
1474 FETCH c_ca_detail INTO v_sca_course_cd,
1475 v_acai_admission_appl_number,
1476 v_acai_nominated_course_cd,
1477 v_acai_sequence_number;
1478 IF c_ca_detail%NOTFOUND THEN
1479 p_message_name := 'IGS_RE_CANT_LOCATE_CAND_DET';
1480 CLOSE c_ca_detail;
1481 RETURN FALSE;
1482 END IF;
1483 CLOSE c_ca_detail;
1484 -- 3. Get the commencement date of the research student.
1485 v_commencement_dt := IGS_RE_GEN_001.resp_get_ca_comm(
1486 p_person_id,
1487 v_sca_course_cd,
1488 v_acai_admission_appl_number,
1489 v_acai_nominated_course_cd,
1490 v_acai_sequence_number);
1491 IF v_commencement_dt IS NULL THEN
1492 v_commencement_dt := SYSDATE;
1493 END IF;
1494 -- 4. Select the IGS_PS_COURSE version number and attendance type
1495 -- from the appropriate source.
1496 IF v_sca_course_cd IS NOT NULL THEN
1497 OPEN c_sca (
1498 v_sca_course_cd);
1499 FETCH c_sca INTO v_crv_version_number,
1500 v_attendance_type;
1501 CLOSE c_sca;
1502 v_course_cd := v_sca_course_cd;
1503 ELSIF v_acai_admission_appl_number IS NOT NULL THEN
1504 OPEN c_acai (
1505 v_acai_admission_appl_number,
1506 v_acai_nominated_course_cd,
1507 v_acai_sequence_number);
1508 FETCH c_acai INTO v_course_cd,
1509 v_crv_version_number,
1510 v_attendance_type;
1511 CLOSE c_acai;
1512 ELSE
1513 v_crv_version_number := 0;
1514 END IF;
1515 -- 5. Select the planned IGS_PR_MILESTONE status (pick the first)
1516 OPEN c_mst_planned;
1517 FETCH c_mst_planned INTO v_milestone_status;
1518 IF c_mst_planned%NOTFOUND THEN
1519 p_message_name := 'IGS_RE_CANT_LOCATE_MILST_STAT';
1520 CLOSE c_mst_planned;
1521 RETURN FALSE;
1522 END IF;
1523 CLOSE c_mst_planned;
1524 -- 6. Loop through the default milestones and add records.
1525 OPEN c_dms (
1526 v_course_cd,
1527 v_crv_version_number,
1528 v_attendance_type);
1529 FETCH c_dms INTO v_milestone_type,
1530 v_offset_days;
1531 IF c_dms%FOUND THEN
1532 LOOP
1533 OPEN c_mil_exists( v_milestone_type,
1534 v_commencement_dt + v_offset_days);
1535 FETCH c_mil_exists INTO v_dummy;
1536 IF c_mil_exists%NOTFOUND THEN
1537 CLOSE c_mil_exists;
1538 OPEN c_mil_seq_num;
1539 FETCH c_mil_seq_num INTO v_mil_sequence_number;
1540 CLOSE c_mil_seq_num;
1541 -- if condition removed for Enh#3577974 for milestone validation build EN303
1542 IGS_PR_MILESTONE_PKG.INSERT_ROW(
1543 X_ROWID => LV_ROWID,
1544 X_PERSON_ID => p_person_id,
1545 X_ca_sequence_number => p_ca_sequence_number,
1546 X_sequence_number => v_mil_sequence_number,
1547 X_milestone_type => v_milestone_type,
1548 X_milestone_status => v_milestone_status,
1549 X_due_dt => v_commencement_dt + v_offset_days,
1550 X_DESCRIPTION => NULL,
1551 X_ACTUAL_REACHED_DT => NULL,
1552 X_PRECED_SEQUENCE_NUMBER => NULL,
1553 X_OVRD_NTFCTN_IMMINENT_DAYS => NULL,
1554 X_OVRD_NTFCTN_REMINDER_DAYS => NULL,
1555 X_OVRD_NTFCTN_RE_REMINDER_DAYS => NULL,
1556 X_COMMENTS => NULL,
1557 X_ORG_ID => v_org_id,
1558 X_MODE => 'R');
1559
1560 v_records_inserted := v_records_inserted + 1;
1561 ELSE
1562 CLOSE c_mil_exists;
1563 END IF;
1564 FETCH c_dms INTO v_milestone_type,
1565 v_offset_days;
1566 EXIT WHEN c_dms%NOTFOUND;
1567 END LOOP;
1568 CLOSE c_dms;
1569 ELSE
1570 CLOSE c_dms;
1571 END IF;
1572 IF v_records_inserted = 0 THEN
1573 -- If no records found
1574 p_message_name := 'IGS_RE_NO_DFLT_MILSTN_EXIST';
1575 RETURN FALSE;
1576 END IF;
1577 -- Commit Changes
1578 COMMIT;
1579 RETURN TRUE;
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 IF c_mil_exists%ISOPEN THEN
1583 CLOSE c_mil_exists;
1584 END IF;
1585 IF c_ca_detail%ISOPEN THEN
1586 CLOSE c_ca_detail;
1587 END IF;
1588 IF c_sca%ISOPEN THEN
1589 CLOSE c_sca;
1590 END IF;
1591 IF c_acai%ISOPEN THEN
1592 CLOSE c_acai;
1593 END IF;
1594 IF c_mst_planned%ISOPEN THEN
1595 CLOSE c_mst_planned;
1596 END IF;
1597 IF c_dms%ISOPEN THEN
1598 CLOSE c_dms;
1599 END IF;
1600 IF c_mil_seq_num%ISOPEN THEN
1601 CLOSE c_mil_seq_num;
1602 END IF;
1603 RAISE;
1604 END;
1605 EXCEPTION
1606 WHEN OTHERS THEN
1607 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
1608 IGS_GE_MSG_STACK.ADD;
1609 App_Exception.Raise_Exception;
1610 END resp_ins_dflt_mil;
1611
1612
1613 END IGS_RE_GEN_002 ;
[Home] [Help]