1 PACKAGE BODY igs_pr_prout_lgcy_pub AS
2 /* $Header: IGSPPR1B.pls 120.1 2006/02/21 02:27:32 ijeddy noship $ */
3 --
4 -- MODIFICATION HISTORY:
5 -- Kalyan Dande 03-Sep-2003 Bug# 3102377: Added a check for NOT NULL for
6 -- Organization Unit validation
7 -- Kalyan Dande 02-Jan-2003 Bug# 2732563: Changed the logic to check for the
8 -- decision_dt instead of decision_status.
9 -- Bug# 2732564: Changed the logic to check for the
10 -- decision_dt instead of decision_status.
11 -- Bug# 2732567: Changed the logic to check for the
12 -- progression_outcome_type before deriving the
13 -- Hold Effects and return FALSE when the system
14 -- progression outcome type is not in AWARD, MANUAL,
15 -- NOPENALTY, REPEATYR, ADVANCE and hold effect type
16 -- is not set and changed the logic to set the
17 -- x_return_value to FALSE when the Progression
18 -- Outcome Type is not already defined in the system.
19 -- Bug# 2732568; Removed the call to
20 -- FND_MSG_PUB.Add_Exc_Msg to avoid message repeatition.
21 -- Kalyan Dande 24-Dec-2002 Bug# 2717485. Added the following IF condition
22 -- to create the record only if the hold effect
23 -- type is either 'SUS_COURSE' or 'EXC_COURSE'
24 -- Kalyan Dande 24-Dec-2002 Bug# 2717616. Changed the OR condition to AND
25 -- for the following IF condition
26 -- Kalyan Dande 11-Nov-2002 Created
27 --
28 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_PR_PROUT_LGCY_PUB';
29 --
30 -- Validate the elements in the record that is to be processed and return
31 -- FALSE if the validation fails else return TRUE.
32 --
33 FUNCTION validate_parameters (
34 p_lgcy_prout_rec IN OUT NOCOPY lgcy_prout_rec_type
35 ) RETURN BOOLEAN IS
36 --
37 l_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
38 --
39 BEGIN
40 --
41 -- If the Person Number is not passed then log an error and continue.
42 --
43 IF (p_lgcy_prout_rec.person_number IS NULL) THEN
44 l_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
45 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PER_NUM_NULL');
46 FND_MSG_PUB.ADD;
47 END IF;
48 --
49 -- If the Program Code is not passed then log an error and continue.
50 --
51 IF (p_lgcy_prout_rec.program_cd IS NULL) THEN
52 l_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
53 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_PRGM_CD_NULL');
54 FND_MSG_PUB.ADD;
55 END IF;
56 --
57 -- If the Alternate Code of the Progression Calendar is not passed then
58 -- log an error and continue.
59 --
60 IF (p_lgcy_prout_rec.prg_cal_alternate_code IS NULL) THEN
61 l_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
62 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PRG_ALT_CODE_NULL');
63 FND_MSG_PUB.ADD;
64 END IF;
65 --
66 -- If the Decision Status is not passed then log an error and continue.
67 --
68 IF (p_lgcy_prout_rec.decision_status IS NULL) THEN
69 l_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
70 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRG_DECI_STAT_NULL');
71 FND_MSG_PUB.ADD;
72 END IF;
73 --
74 -- Return the status of the validation.
75 -- TRUE if all validations are passed else FALSE.
76 --
77 RETURN (l_return_value);
78 --
79 END validate_parameters;
80 --
81 --
82 --
83 PROCEDURE derive_pr_stnd_lvl_data (
84 p_lgcy_prout_rec IN OUT NOCOPY lgcy_prout_rec_type,
85 p_person_id OUT NOCOPY igs_pe_person.person_id%TYPE,
86 p_prg_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
87 p_prg_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
88 p_outcome_sequence_number OUT NOCOPY igs_pr_stdnt_pr_ou_all.sequence_number%TYPE,
89 p_hold_effect_type OUT NOCOPY igs_fi_enc_dflt_eft.s_encmb_effect_type%TYPE,
90 p_org_start_dt OUT NOCOPY igs_pe_hz_parties.ou_start_dt%TYPE,
91 x_return_value OUT NOCOPY BOOLEAN
92 ) IS
93 --
94 CURSOR cur_sequence_number IS
95 SELECT sequence_number
96 FROM igs_pr_stdnt_pr_ou_all
97 WHERE person_id = p_person_id
98 AND progression_outcome_type = p_lgcy_prout_rec.progression_outcome_type
99 AND course_cd = p_lgcy_prout_rec.program_cd
100 AND prg_cal_type = p_prg_cal_type
101 AND prg_ci_sequence_number = p_prg_sequence_number;
102 --
103 CURSOR cur_hold_effect_type IS
104 SELECT s_encmb_effect_type, s_progression_outcome_type
105 FROM igs_fi_enc_dflt_eft dft,
106 igs_pr_ou_type ou
107 WHERE dft.encumbrance_type = ou.encumbrance_type
108 AND ou.progression_outcome_type = p_lgcy_prout_rec.progression_outcome_type;
109 --
110 l_return_status VARCHAR2(10);
111 l_start_dt DATE;
112 l_end_dt DATE;
113 l_hold_effect_type VARCHAR2(10);
114 l_s_progression_outcome_type igs_pr_ou_type.s_progression_outcome_type%TYPE;
115 --
116 BEGIN
117 --
118 -- Initialise the return value
119 --
120 x_return_value := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
121 --
122 -- Derive Person ID from Person Number
123 --
124 p_person_id := igs_ge_gen_003.get_person_id (p_lgcy_prout_rec.person_number);
125 --
126 -- Error out if the Person is not found in OSS
127 --
128 IF (p_person_id IS NULL) THEN
129 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
130 FND_MSG_PUB.ADD;
131 x_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
132 RETURN;
133 END IF;
134 --
135 -- Derive the Progression Calendar Information for the Alternate Code passed
136 --
137 igs_ge_gen_003.get_calendar_instance (
138 p_lgcy_prout_rec.prg_cal_alternate_code,
139 NULL,
140 p_prg_cal_type,
141 p_prg_sequence_number,
142 l_start_dt,
143 l_end_dt,
144 l_return_status
145 );
146 --
147 -- Error out if the Calendar is not defined or defined more than once in OSS
148 --
149 IF ((p_prg_cal_type IS NULL) OR (p_prg_sequence_number IS NULL)) THEN
150 --
151 -- Raise an error if no Calendar definition is found in OSS
152 --
153 IF (l_return_status = 'INVALID') THEN
154 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_ALT_CODE');
155 FND_MSG_PUB.ADD;
156 --
157 -- Raise an error if more than one Calendar definition is found in OSS
158 --
159 ELSIF (l_return_status = 'MULTIPLE') THEN
160 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_MULTI_ALT_CODE');
161 FND_MSG_PUB.ADD;
162 END IF;
163 x_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
164 RETURN;
165 END IF;
166 --
167 -- Derive the Outcome Sequence Number.
168 -- Check for the existance of Student Progression Outcome in OSS which
169 -- matches with the record in interface table. If the record exists then
170 -- assign p_outcome_sequence_number with
171 -- igs_pr_stdnt_pr_ou_all.sequence_number else assign the new sequence value
172 -- from igs_pr_spo_seq_num_s sequence.
173 --
174 OPEN cur_sequence_number;
175 FETCH cur_sequence_number INTO p_outcome_sequence_number;
176 IF (cur_sequence_number%NOTFOUND) THEN
177 CLOSE cur_sequence_number;
178 SELECT igs_pr_spo_seq_num_s.NEXTVAL
179 INTO p_outcome_sequence_number
180 FROM dual
181 WHERE ROWNUM = 1;
182 ELSE
183 CLOSE cur_sequence_number;
184 END IF;
185 --
186 -- Derive the Organization Unit Start Date
187 --
188 IF (p_lgcy_prout_rec.decision_org_unit_cd IS NOT NULL) THEN
189 IF (NOT igs_re_val_rsup.get_org_unit_dtls (
190 p_org_unit_cd => p_lgcy_prout_rec.decision_org_unit_cd,
191 p_start_dt => p_org_start_dt
192 )) THEN
193 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_ORG_START_DT');
194 FND_MSG_PUB.ADD;
195 x_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
196 END IF;
197 END IF;
198 --
199 -- Derive the Hold Effect Type if Progression Outcome Type is specified.
200 -- kdande; 02-Jan-2003; Bug# 2732567; Changed the logic to check for the
201 -- progression_outcome_type before deriving the Hold Effects and return
202 -- FALSE when the system progression outcome type is not in AWARD, MANUAL,
203 -- NOPENALTY, REPEATYR, ADVANCE and hold effect type is not set.
204 --
205 IF (p_lgcy_prout_rec.progression_outcome_type IS NOT NULL) THEN
206 OPEN cur_hold_effect_type;
207 FETCH cur_hold_effect_type INTO l_hold_effect_type, l_s_progression_outcome_type;
208 IF (cur_hold_effect_type%NOTFOUND) THEN
209 IF (l_s_progression_outcome_type NOT IN ('AWARD', 'MANUAL', 'NOPENALTY', 'REPEATYR', 'ADVANCE')) THEN
210 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_HOLD_EFCT');
211 FND_MSG_PUB.ADD;
212 x_return_value := FND_API.TO_BOOLEAN (FND_API.G_FALSE);
213 END IF;
214 END IF;
215 --
216 p_hold_effect_type := l_hold_effect_type;
217 --
218 LOOP
219 FETCH cur_hold_effect_type INTO l_hold_effect_type, l_s_progression_outcome_type;
220 EXIT WHEN (cur_hold_effect_type%NOTFOUND);
221 p_hold_effect_type := p_hold_effect_type || ',' || l_hold_effect_type;
222 END LOOP;
223 CLOSE cur_hold_effect_type;
224 END IF;
225 --
226 END derive_pr_stnd_lvl_data;
227 --
228 -- This function performs all the data integrity validations and keeps
229 -- adding error message to stack as an when a validation fails.
230 --
231 FUNCTION validate_spo_db_cons (
232 p_lgcy_prout_rec IN lgcy_prout_rec_type,
233 p_person_id IN igs_pe_person.person_id%TYPE,
234 p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE,
235 p_org_start_dt IN igs_pe_hz_parties.ou_start_dt%TYPE
236
237 ) RETURN BOOLEAN IS
238 --
239 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
240 --
241 BEGIN
242 --
243 -- Foreign Key checks (Parent Existence)
244 --
245 -- Check for Attendance Type Existence
246 --
247 IF (p_lgcy_prout_rec.restricted_attendance_type IS NOT NULL) THEN
248 IF (NOT igs_en_atd_type_pkg.get_pk_for_validation (
249 p_lgcy_prout_rec.restricted_attendance_type
250 )) THEN
251 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_ATD_TYP_FK');
252 FND_MSG_PUB.ADD;
253 x_return_value := FALSE;
254 END IF;
255 END IF;
256 --
257 -- Check for Program Group Existence
258 --
259 IF (p_lgcy_prout_rec.encmb_program_group_cd IS NOT NULL) THEN
260 IF (NOT igs_ps_grp_pkg.get_pk_for_validation (
261 p_lgcy_prout_rec.encmb_program_group_cd
262 )) THEN
263 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PS_GRP_FK');
264 FND_MSG_PUB.ADD;
265 x_return_value := FALSE;
266 END IF;
267 END IF;
268 --
269 -- Check for Organization Unit Existence
270 --
271 IF (p_lgcy_prout_rec.decision_org_unit_cd IS NOT NULL) THEN
272 IF (NOT igs_or_unit_pkg.get_pk_for_validation (
273 p_lgcy_prout_rec.decision_org_unit_cd,
274 p_org_start_dt
275 )) THEN
276 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_OR_UNIT_FK');
277 FND_MSG_PUB.ADD;
278 x_return_value := FALSE;
279 END IF;
280 END IF;
281 --
282 -- Check for Outcome Type Existence
283 -- kdande; 02-Jan-2003; Bug# 2732567; Changed the logic to set the
284 -- x_return_value to FALSE when the Progression Outcome Type is not
285 -- already defined in the system.
286 --
287 IF (p_lgcy_prout_rec.progression_outcome_type IS NOT NULL) THEN
288 IF (NOT igs_pr_ou_type_pkg.get_pk_for_validation (
289 p_lgcy_prout_rec.progression_outcome_type
290 )) THEN
291 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_OU_TYPE_FK');
292 FND_MSG_PUB.ADD;
293 x_return_value := FALSE;
294 END IF;
295 END IF;
296 --
297 -- Primary Key validation
298 --
299 IF (igs_pr_stdnt_pr_ou_pkg.get_pk_for_validation (
300 p_person_id,
301 p_lgcy_prout_rec.program_cd,
302 p_sequence_number
303 )) THEN
304 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_SPO_EXIST');
305 FND_MSG_PUB.ADD;
306 x_return_value := FALSE;
307 END IF;
308 --
309 -- Valid Value Checks
310 --
311 IF (p_lgcy_prout_rec.duration IS NOT NULL) THEN
312 BEGIN
313 igs_pr_stdnt_pr_ou_pkg.check_constraints (
314 'DURATION',
315 p_lgcy_prout_rec.duration
316 );
317 EXCEPTION
318 WHEN OTHERS THEN
319 --
320 -- Delete the top message and add a new message in place of it
321 --
322 FND_MSG_PUB.Delete_Msg (FND_MSG_PUB.Count_Msg);
323 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_DURATION_FORMAT');
324 FND_MSG_PUB.ADD;
325 x_return_value := FALSE;
326 END;
327 END IF;
328 --
329 IF (p_lgcy_prout_rec.restricted_enrolment_cp IS NOT NULL) THEN
330 BEGIN
331 igs_pr_stdnt_pr_ou_pkg.check_constraints (
332 'RESTRICTED_ENROLMENT_CP',
333 p_lgcy_prout_rec.restricted_enrolment_cp
334 );
335 EXCEPTION
336 WHEN OTHERS THEN
337 --
338 -- Delete the top message and add a new message in place of it
339 --
340 FND_MSG_PUB.Delete_Msg (FND_MSG_PUB.Count_Msg);
341 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_RESENR_FORMAT');
342 FND_MSG_PUB.ADD;
343 x_return_value := FALSE;
344 END;
345 END IF;
346 --
347 IF (p_lgcy_prout_rec.duration_type IS NOT NULL) THEN
348 BEGIN
349 igs_pr_stdnt_pr_ou_pkg.check_constraints (
350 'DURATION_TYPE',
351 p_lgcy_prout_rec.duration_type
352 );
353 EXCEPTION
354 WHEN OTHERS THEN
355 --
356 -- Delete the top message and add a new message in place of it
357 --
358 FND_MSG_PUB.Delete_Msg (FND_MSG_PUB.Count_Msg);
359 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_DURTYP_VALID');
360 FND_MSG_PUB.ADD;
361 x_return_value := FALSE;
362 END;
363 END IF;
364 --
365 IF (p_lgcy_prout_rec.decision_status IS NOT NULL) THEN
366 BEGIN
367 igs_pr_stdnt_pr_ou_pkg.check_constraints (
368 'DECISION_STATUS',
369 p_lgcy_prout_rec.decision_status
370 );
371 EXCEPTION
372 WHEN OTHERS THEN
373 --
374 -- Delete the top message and add a new message in place of it
375 --
376 FND_MSG_PUB.Delete_Msg (FND_MSG_PUB.Count_Msg);
377 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_DURSTAT_VALID');
378 FND_MSG_PUB.ADD;
379 x_return_value := FALSE;
380 END;
381 END IF;
382 --
383 IF (p_lgcy_prout_rec.show_cause_outcome_type IS NOT NULL) THEN
384 BEGIN
385 igs_pr_stdnt_pr_ou_pkg.check_constraints (
386 'SHOW_CAUSE_OUTCOME_TYPE',
387 p_lgcy_prout_rec.show_cause_outcome_type
388 );
389 EXCEPTION
390 WHEN OTHERS THEN
391 --
392 -- Delete the top message and add a new message in place of it
393 --
394 FND_MSG_PUB.Delete_Msg (FND_MSG_PUB.Count_Msg);
395 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SHOWCAUSE_VALID');
396 FND_MSG_PUB.ADD;
397 x_return_value := FALSE;
398 END;
399 END IF;
400 --
401 IF (p_lgcy_prout_rec.appeal_outcome_type IS NOT NULL) THEN
402 BEGIN
403 igs_pr_stdnt_pr_ou_pkg.check_constraints (
404 'APPEAL_OUTCOME_TYPE',
405 p_lgcy_prout_rec.appeal_outcome_type
406 );
407 EXCEPTION
408 WHEN OTHERS THEN
409 --
410 -- Delete the top message and add a new message in place of it
411 --
412 FND_MSG_PUB.Delete_Msg (FND_MSG_PUB.Count_Msg);
413 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_APPEAL_VALID');
414 FND_MSG_PUB.ADD;
415 x_return_value := FALSE;
416 END;
417 END IF;
418 --
419 RETURN (x_return_value);
420 --
421 END validate_spo_db_cons;
422 --
423 -- This function validates all the business rules before inserting a record in
424 -- the table IGS_PR_STDNT_PR_OU.
425 --
426 FUNCTION validate_stdnt_prg_otcm (
427 p_lgcy_prout_rec IN lgcy_prout_rec_type,
428 p_person_id IN igs_pe_person.person_id%TYPE,
429 p_prg_cal_type IN igs_ca_inst.cal_type%TYPE,
430 p_prg_sequence_number IN igs_ca_inst.sequence_number%TYPE,
431 p_outcome_sequence_number IN igs_pr_stdnt_pr_ou_all.sequence_number%TYPE,
432 p_hold_effect_type IN VARCHAR2,
433 p_decision_ou_start_dt IN igs_pe_hz_parties.ou_start_dt%TYPE
434 ) RETURN BOOLEAN IS
435 --
436 CURSOR cur_sca IS
437 SELECT course_attempt_status
438 FROM igs_en_stdnt_ps_att sca
439 WHERE person_id = p_person_id
440 AND course_cd = p_lgcy_prout_rec.program_cd
441 AND course_attempt_status = 'UNCONFIRM';
442 --
443 CURSOR cur_pot IS
444 SELECT s_progression_outcome_type
445 FROM igs_pr_ou_type pot
446 WHERE pot.progression_outcome_type = p_lgcy_prout_rec.progression_outcome_type;
447 --
448 rec_sca cur_sca%ROWTYPE;
449 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
450 x_message_name fnd_new_messages.message_name%TYPE;
451 v_s_progression_outcome_type igs_pr_ou_type.s_progression_outcome_type%TYPE;
452 l_where_clause VARCHAR2(2000);
453 TYPE ref_cur IS REF CURSOR;
454 l_ref_cur ref_cur;
455 l_record_found VARCHAR2(1) := 'N';
456 curr_stat VARCHAR2(2000);
457 l_func_name VARCHAR2(30);
458 --
459 BEGIN
460 --
461 -- Check whether the Program Attempt Status is Unconfirmed
462 --
463 OPEN cur_sca;
464 FETCH cur_sca INTO rec_sca;
465 IF (cur_sca%FOUND) THEN
466 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRATMTSTAT_UNCONFIRM');
467 FND_MSG_PUB.ADD;
468 x_return_value := FALSE;
469 END IF;
470 CLOSE cur_sca;
471 --
472 -- Validate the Progression Calendar Instance to check whether it is Active
473 --
474 IF (NOT igs_pr_val_spo.prgp_val_prg_ci (
475 p_prg_cal_type,
476 p_prg_sequence_number,
477 x_message_name
478 )) THEN
479 FND_MESSAGE.SET_NAME ('IGS', x_message_name);
480 FND_MSG_PUB.ADD;
481 x_return_value := FALSE;
482 END IF;
483 --
484 -- Validate the Expiry Date to check that it is not a future date
485 --
486 IF (NOT igs_pr_val_spo.prgp_val_spo_exp_dt (
487 p_lgcy_prout_rec.expiry_dt,
488 x_message_name
489 )) THEN
490 FND_MESSAGE.SET_NAME ('IGS', x_message_name);
491 FND_MSG_PUB.ADD;
492 x_return_value := FALSE;
493 END IF;
494 --
495 -- Validate whether the Decision Date and Decision Organization Unit Code is
496 -- entered when the Decision Status is 'PENDING'
497 --
498 IF (p_lgcy_prout_rec.decision_status = 'PENDING') THEN
499 IF ((p_lgcy_prout_rec.decision_dt IS NOT NULL) OR
500 (p_lgcy_prout_rec.decision_org_unit_cd IS NOT NULL) OR
501 (p_decision_ou_start_dt IS NOT NULL)) THEN
502 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_DEDT_DORG_CNT_DEST_PEN');
503 FND_MSG_PUB.ADD;
504 x_return_value := FALSE;
505 END IF;
506 END IF;
507 --
508 -- Validate whether the Decision Organization Unit Code or Decision Date are
509 -- Null when the Decision Status is 'APPROVED' or 'WAIVED'
510 --
511 IF (p_lgcy_prout_rec.decision_status IN ('APPROVED', 'WAIVED')) THEN
512 IF ((p_lgcy_prout_rec.decision_dt IS NULL) OR
513 (p_lgcy_prout_rec.decision_org_unit_cd IS NULL) OR
514 (p_decision_ou_start_dt IS NULL)) THEN
515 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_DEDT_DEOR_CNT_DEST_PEN');
516 FND_MSG_PUB.ADD;
517 x_return_value := FALSE;
518 END IF;
519 END IF;
520 --
521 -- Validate the Decision Date and check that it should not be a future date
522 --
523 IF (NOT igs_pr_val_spo.prgp_val_spo_dcsn_dt (
524 p_lgcy_prout_rec.decision_dt,
525 x_message_name
526 )) THEN
527 FND_MESSAGE.SET_NAME ('IGS', x_message_name);
528 FND_MSG_PUB.ADD;
529 x_return_value := FALSE;
530 END IF;
531 --
532 -- Validate that the Student Progression Outcome Program Code cannot be
533 -- specified if the hold effect type does not contains one (or more than
534 -- one) of 'SUS_COURSE, EXC_COURSE'
535 --
536 -- kdande; 24-Dec-2002; Bug# 2717616. Changed the OR condition to AND for
537 -- the following IF condition
538 --
539 IF ((INSTR (p_hold_effect_type, 'SUS_COURSE') = 0) AND
540 (INSTR (p_hold_effect_type, 'EXC_COURSE') = 0)) THEN
541 IF (p_lgcy_prout_rec.spo_program_cd IS NOT NULL) THEN
542 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRGOTCM_NOT_IN_SUS_EXC');
543 FND_MSG_PUB.ADD;
544 x_return_value := FALSE;
545 END IF;
546 END IF;
547 --
548 -- Validate that the Unit Set Code cannot be specified if the hold effect
549 -- type is not set to 'EXC_CRS_US' - Exclusion from a Unit set
550 --
551 IF (INSTR (p_hold_effect_type, 'EXC_CRS_US') = 0) THEN
552 IF (p_lgcy_prout_rec.unit_set_cd IS NOT NULL) THEN
553 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_USTOTCM_NOT_IN_PROG_US');
554 FND_MSG_PUB.ADD;
555 x_return_value := FALSE;
556 END IF;
557 END IF;
558 --
559 -- Validate that the Unit of type EXCLUDED cannot be specified if the hold
560 -- effect type is not set to 'EXC_CRS_U' - Exclude Unit
561 --
562 IF (INSTR (p_hold_effect_type, 'EXC_CRS_U') = 0) THEN
563 IF (p_lgcy_prout_rec.s_unit_type = 'EXCLUDED') THEN
564 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_UOTCM_NOT_IN_PROG_U');
565 FND_MSG_PUB.ADD;
566 x_return_value := FALSE;
567 END IF;
568 END IF;
569 --
570 -- Validate that the Unit of type REQUIRED cannot be specified if the hold
571 -- effect type is not set to 'RQRD_CRS_U' - Required Unit
572 --
573 IF (INSTR (p_hold_effect_type, 'RQRD_CRS_U') = 0) THEN
574 IF (p_lgcy_prout_rec.s_unit_type = 'REQUIRED') THEN
575 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_UOTCM_NOT_IN_RQRD');
576 FND_MSG_PUB.ADD;
577 x_return_value := FALSE;
578 END IF;
579 END IF;
580 --
581 -- When the Progression Outcome Type is related to hold of type 'RSTR_GE_CP'
582 -- or 'RSTR_LE_CP' validate whether the Restricted Enrolment CP is set
583 --
584 IF ((INSTR (p_hold_effect_type, 'RSTR_GE_CP') > 0) OR
585 (INSTR (p_hold_effect_type, 'RSTR_LE_CP') > 0)) THEN
586 IF (p_lgcy_prout_rec.restricted_enrolment_cp IS NULL) THEN
587 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_RERN_CPO_MEN_URE');
588 FND_MSG_PUB.ADD;
589 x_return_value := FALSE;
590 END IF;
591 END IF;
592 --
593 -- When the Progression Outcome Type is related to hold of type 'RSTR_AT_TY'
594 -- validate whether the Restricted Attendance Type is set
595 --
596 IF (INSTR (p_hold_effect_type, 'RSTR_AT_TY') > 0) THEN
597 IF (p_lgcy_prout_rec.restricted_attendance_type IS NULL) THEN
598 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_REATY_MEN_PROT_ATRES');
599 FND_MSG_PUB.ADD;
600 x_return_value := FALSE;
601 END IF;
602 END IF;
603 --
604 -- When the Progression Outcome Type is related to hold of type 'EXC_CRS_GP'
605 -- validate whether the Encumbrance Program Group Code is set
606 --
607 IF (INSTR (p_hold_effect_type, 'EXC_CRS_GP') > 0) THEN
608 IF (p_lgcy_prout_rec.encmb_program_group_cd IS NULL) THEN
609 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_ENCUM_CGP_MEN_EXC');
610 FND_MSG_PUB.ADD;
611 x_return_value := FALSE;
612 END IF;
613 END IF;
614 --
615 -- Check that the Excluded Program Group can be set only when the Progression
616 -- Outcome Type is related to a hold with the 'EXC_CRS_GP' effect
617 --
618 IF (NOT igs_pr_val_spo.prgp_val_spo_cgr (
619 p_lgcy_prout_rec.progression_outcome_type,
620 p_lgcy_prout_rec.encmb_program_group_cd,
621 x_message_name
622 )) THEN
623 FND_MESSAGE.SET_NAME ('IGS', x_message_name);
624 FND_MSG_PUB.ADD;
625 x_return_value := FALSE;
626 END IF;
627 --
628 -- Check that the Restricted Attendance Type can be set only when the
629 -- Progression Outcome Type is related to a hold with the 'RSTR_AT_TY' effect
630 --
631 IF (NOT igs_pr_val_spo.prgp_val_spo_att (
632 p_lgcy_prout_rec.progression_outcome_type,
633 p_lgcy_prout_rec.restricted_attendance_type,
634 x_message_name
635 )) THEN
636 FND_MESSAGE.SET_NAME ('IGS', x_message_name);
637 FND_MSG_PUB.ADD;
638 x_return_value := FALSE;
639 END IF;
640 --
641 -- Check that the Restricted Enrollment CP can be set only when the
642 -- Progression Outcome Type is related to a hold with the 'RSTR_GE_CP' or
643 -- 'RSTR_LE_CP' effect
644 --
645 IF (NOT igs_pr_val_spo.prgp_val_spo_cp (
646 p_lgcy_prout_rec.progression_outcome_type,
647 p_lgcy_prout_rec.restricted_enrolment_cp,
648 x_message_name
649 )) THEN
650 FND_MESSAGE.SET_NAME ('IGS', x_message_name);
651 FND_MSG_PUB.ADD;
652 x_return_value := FALSE;
653 END IF;
654 --
655 -- Check that when Duration is set, the Duration Type must also be set and vice versa
656 --
657 IF (NOT igs_pr_val_spo.prgp_val_spo_rqrd (
658 p_lgcy_prout_rec.progression_outcome_type,
659 p_lgcy_prout_rec.duration,
660 p_lgcy_prout_rec.duration_type,
661 x_message_name
662 )) THEN
663 FND_MESSAGE.SET_NAME ('IGS', x_message_name);
664 FND_MSG_PUB.ADD;
665 x_return_value := FALSE;
666 END IF;
667 --
668 -- When the System Progression Outcome Type corresponding to the Progression
669 -- Outcome Type is 'SUSPENSION' then Duration must be set
670 --
671 OPEN cur_pot;
672 FETCH cur_pot INTO v_s_progression_outcome_type;
673 CLOSE cur_pot;
674 --
675 IF ((v_s_progression_outcome_type = 'SUSPENSION') AND
676 (p_lgcy_prout_rec.duration IS NULL)) THEN
677 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_DU_DUTY_SUS');
678 FND_MSG_PUB.ADD;
679 x_return_value := FALSE;
680 END IF;
681 --
682 -- When the System Progression Outcome Type corresponding to the Progression
683 -- Outcome Type is one of 'EXCLUSION', 'EXPULSION', 'EX_FUND' or 'NOPENALTY'
684 -- then Duration must not be set
685 --
686 IF ((v_s_progression_outcome_type IN ('EXCLUSION', 'EXPULSION', 'NOPENALTY', 'EX_FUND')) AND
687 (p_lgcy_prout_rec.duration IS NOT NULL)) THEN
688 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_DUTY_PRTY_EXC_NOP');
689 FND_MSG_PUB.ADD;
690 x_return_value := FALSE;
691 END IF;
692 --
693 -- When the System Progression Outcome Type corresponding to the Progression
694 -- Outcome Type is not in 'PROBATION' or 'MANUAL' then Duration Type cannot
695 -- be 'EFFECTIVE'
696 --
697 IF ((v_s_progression_outcome_type NOT IN ('PROBATION', 'MANUAL')) AND
698 (p_lgcy_prout_rec.duration_type = 'EFFECTIVE')) THEN
699 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_DTYP_CNTEF_PRO_MAN');
700 FND_MSG_PUB.ADD;
701 x_return_value := FALSE;
702 END IF;
703 --
704 -- When the Show Cause Date is Null then the Show Cause Outcome Date and
705 -- Show Cause Outcome Type cannot be Not Null
706 --
707 IF ((p_lgcy_prout_rec.show_cause_dt IS NULL) AND
708 ((p_lgcy_prout_rec.show_cause_outcome_dt IS NOT NULL) OR
709 (p_lgcy_prout_rec.show_cause_outcome_type IS NOT NULL))) THEN
710 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SHCA_OTY_ODTCT_SCDT_NT');
711 FND_MSG_PUB.ADD;
712 x_return_value := FALSE;
713 END IF;
714 --
715 -- Check that Show Cause Outcome Date is not greater than System Date
716 --
717 IF ((p_lgcy_prout_rec.show_cause_outcome_dt IS NOT NULL) AND
718 (TRUNC(p_lgcy_prout_rec.show_cause_outcome_dt) > TRUNC(SYSDATE))) THEN
719 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SHCA_OUDT_CNT_FUT');
720 FND_MSG_PUB.ADD;
721 x_return_value := FALSE;
722 END IF;
723 --
724 -- Check that when the Show Cause Outcome Date is set then the Show Cause
725 -- Outcome Type should also be set and vice versa
726 --
727 IF ((p_lgcy_prout_rec.show_cause_outcome_dt IS NULL) AND
728 (p_lgcy_prout_rec.show_cause_outcome_type IS NOT NULL)) THEN
729 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SCADT_MST_SHOTY_ST');
730 FND_MSG_PUB.ADD;
731 x_return_value := FALSE;
732 END IF;
733 --
734 IF ((p_lgcy_prout_rec.show_cause_outcome_dt IS NOT NULL) AND
735 (p_lgcy_prout_rec.show_cause_outcome_type IS NULL)) THEN
736 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SHOT_TYMST_SCO_DTST');
737 FND_MSG_PUB.ADD;
738 x_return_value := FALSE;
739 END IF;
740 --
741 -- Check that the Show Cause Date is not a future date
742 --
743 IF ((p_lgcy_prout_rec.show_cause_dt IS NOT NULL) AND
744 (TRUNC(p_lgcy_prout_rec.show_cause_dt) > TRUNC(SYSDATE))) THEN
745 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SHCA_DT_CNT_FUT');
746 FND_MSG_PUB.ADD;
747 x_return_value := FALSE;
748 END IF;
749 --
750 -- Check that Show Cause Date is not set when the Decision Date is Null
751 --
752 IF ((p_lgcy_prout_rec.show_cause_dt IS NOT NULL) AND
753 (p_lgcy_prout_rec.decision_dt IS NULL)) THEN
754 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SHCDT_CNT_ST_DEDT_NST');
755 FND_MSG_PUB.ADD;
756 x_return_value := FALSE;
757 END IF;
758 --
759 -- Check that Show Cause Date is not set when Show Cause Expiry Date is Null
760 --
761 IF ((p_lgcy_prout_rec.show_cause_expiry_dt IS NULL) AND
762 (p_lgcy_prout_rec.show_cause_dt IS NOT NULL)) THEN
763 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SCADT_CNT_SHEX_DTNST');
764 FND_MSG_PUB.ADD;
765 x_return_value := FALSE;
766 END IF;
767 --
768 -- Check that Show Cause Expiry Date is not set when decision is not made.
769 -- kdande; 02-Jan-2003; Bug# 2732563; Changed the logic to check for the
770 -- decision_dt instead of decision_status.
771 --
772 IF ((p_lgcy_prout_rec.show_cause_expiry_dt IS NOT NULL) AND
773 (p_lgcy_prout_rec.decision_dt IS NULL)) THEN
774 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SHCA_EXPDT_CNTB_DSTNA');
775 FND_MSG_PUB.ADD;
776 x_return_value := FALSE;
777 END IF;
778 --
779 -- Check that Show Cause Expiry Date is not greater than the Appeal Expiry Date
780 --
781 IF (TRUNC (NVL (p_lgcy_prout_rec.show_cause_expiry_dt,
782 igs_ge_date.igsdate('0001/01/01'))) >
783 TRUNC (NVL (p_lgcy_prout_rec.appeal_expiry_dt,
784 igs_ge_date.igsdate('9999/01/01')))) THEN
785 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_SH_EXPDT_CNT_APEXDT');
786 FND_MSG_PUB.ADD;
787 x_return_value := FALSE;
788 END IF;
789 --
790 -- Check that when Appeal Date is Null then Appeal Outcome Date and
791 -- Appeal Outcome Type cannot be Not Null.
792 --
793 IF ((p_lgcy_prout_rec.appeal_dt IS NULL) AND
794 ((p_lgcy_prout_rec.appeal_outcome_dt IS NOT NULL) OR
795 (p_lgcy_prout_rec.appeal_outcome_type IS NOT NULL))) THEN
796 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_APOTY_DTCNT_APDT_NST');
797 FND_MSG_PUB.ADD;
798 x_return_value := FALSE;
799 END IF;
800 --
801 -- Check that Appeal Outcome Date not greater than System date
802 --
803 IF ((p_lgcy_prout_rec.appeal_outcome_dt IS NOT NULL) AND
804 (TRUNC(p_lgcy_prout_rec.appeal_outcome_dt) > TRUNC(SYSDATE))) THEN
805 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_APOUT_DT_CNT_FUT');
806 FND_MSG_PUB.ADD;
807 x_return_value := FALSE;
808 END IF;
809 --
810 -- Check that Appeal Outcome Date is set then the Appeal Outcome Type should
811 -- also be set and vice versa
812 --
813 IF ((p_lgcy_prout_rec.appeal_outcome_dt IS NULL) AND
814 (p_lgcy_prout_rec.appeal_outcome_type IS NOT NULL)) THEN
815 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_APODT_MST_AOTY_ST');
816 FND_MSG_PUB.ADD;
817 x_return_value := FALSE;
818 END IF;
819 --
820 IF ((p_lgcy_prout_rec.appeal_outcome_dt IS NOT NULL) AND
821 (p_lgcy_prout_rec.appeal_outcome_type IS NULL)) THEN
822 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_APOTY_MST_AODT_ST');
823 FND_MSG_PUB.ADD;
824 x_return_value := FALSE;
825 END IF;
826 --
827 -- Check that the Appeal Date is not a future date
828 --
829 IF ((p_lgcy_prout_rec.appeal_dt IS NOT NULL) AND
830 (TRUNC(p_lgcy_prout_rec.appeal_dt) > TRUNC(SYSDATE))) THEN
831 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_APOUT_DT_CNT_FUT');
832 FND_MSG_PUB.ADD;
833 x_return_value := FALSE;
834 END IF;
835 --
836 -- Check that Appeal Date is not lesser than the Show Cause Date
837 --
838 IF (TRUNC (NVL (p_lgcy_prout_rec.appeal_dt,
839 igs_ge_date.igsdate('9999/01/01'))) <
840 TRUNC (NVL (p_lgcy_prout_rec.show_cause_dt,
841 igs_ge_date.igsdate('0001/01/01')))) THEN
842 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_APDT_CNTS_BSHDT');
843 FND_MSG_PUB.ADD;
844 x_return_value := FALSE;
845 END IF;
846 --
847 -- Check that Appeal Expiry Date is not set when decision is not made
848 -- kdande; 02-Jan-2003; Bug# 2732564; Changed the logic to check for the
849 -- decision_dt instead of decision_status.
850 --
851 IF ((p_lgcy_prout_rec.appeal_expiry_dt IS NOT NULL) AND
852 (p_lgcy_prout_rec.decision_dt IS NULL)) THEN
853 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_AEXDT_CNT_ST_DECST_NTAP');
854 FND_MSG_PUB.ADD;
855 x_return_value := FALSE;
856 END IF;
857 --
858 -- Organization Unit Filter Integration Validation
859 --
860 -- kdande; 03-Sep-2003; Bug# 3102377; Validate only when the Decision
861 -- Organization Unit Code is NOT NULL
862 --
863 IF (p_lgcy_prout_rec.decision_org_unit_cd IS NOT NULL) THEN
864 l_func_name := 'PROG_OUTCOME_LGCY';
865 igs_or_gen_012_pkg.get_where_clause_api (l_func_name,l_where_clause);
866
867 IF l_where_clause IS NOT NULL THEN
868 curr_stat := 'SELECT ''x'' FROM igs_or_unit WHERE org_unit_cd = :1 AND '||l_where_clause;
869 OPEN l_ref_cur FOR curr_stat USING p_lgcy_prout_rec.decision_org_unit_cd,l_func_name;
870 ELSE
871 curr_stat := 'SELECT ''x'' FROM igs_or_unit WHERE org_unit_cd = :1 ';
872 OPEN l_ref_cur FOR curr_stat USING p_lgcy_prout_rec.decision_org_unit_cd;
873 END IF;
874
875 FETCH l_ref_cur INTO l_record_found;
876 IF (l_ref_cur%NOTFOUND) THEN
877 FND_MESSAGE.SET_NAME ('IGS', 'IGS_EN_INV');
878 FND_MESSAGE.SET_TOKEN ('PARAM', p_lgcy_prout_rec.decision_org_unit_cd);
879 FND_MSG_PUB.ADD;
880 x_return_value := FALSE;
881 END IF;
882 CLOSE l_ref_cur;
883 END IF;
884 --
885 RETURN (x_return_value);
886 --
887 END validate_stdnt_prg_otcm;
888 --
889 -- This function performs all the data integrity validation and keeps adding
890 -- error message to stack as an when it encounters one.
891 --
892 FUNCTION validate_prg_db_cons (
893 p_lgcy_prout_rec IN lgcy_prout_rec_type,
894 p_person_id IN igs_pe_person.person_id%TYPE,
895 p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
896 ) RETURN BOOLEAN IS
897 --
898 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
899 --
900 BEGIN
901 --
902 -- Foreign Key Checks (Checking Parent Existence)
903 -- Course Code Existence
904 --
905 IF ((p_lgcy_prout_rec.program_cd IS NOT NULL) AND
906 (NOT igs_ps_course_pkg.get_pk_for_validation (
907 p_lgcy_prout_rec.program_cd
908 ))) THEN
909 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PS_COURSE_FK');
910 FND_MSG_PUB.ADD;
911 x_return_value := FALSE;
912 END IF;
913 --
914 -- Student Progression Outcome Existence
915 --
916 IF ((p_person_id IS NOT NULL) AND
917 (p_lgcy_prout_rec.program_cd IS NOT NULL) AND
918 (p_sequence_number IS NOT NULL) AND
919 (NOT igs_pr_stdnt_pr_ou_pkg.get_pk_for_validation (
920 p_person_id,
921 p_lgcy_prout_rec.program_cd,
922 p_sequence_number
923 ))) THEN
924 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRG_SPO_FK');
925 FND_MSG_PUB.ADD;
926 x_return_value := FALSE;
927 END IF;
928 --
929 -- Primary Key Validation
930 --
931 IF ((p_person_id IS NOT NULL) AND
932 (p_lgcy_prout_rec.program_cd IS NOT NULL) AND
933 (p_sequence_number IS NOT NULL) AND
934 (p_lgcy_prout_rec.spo_program_cd IS NOT NULL) AND
935 (igs_pr_stdnt_pr_ps_pkg.get_pk_for_validation (
936 p_person_id,
937 p_lgcy_prout_rec.program_cd,
938 p_sequence_number,
939 p_lgcy_prout_rec.spo_program_cd
940 ))) THEN
941 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_SPPRG_EXIST');
942 FND_MSG_PUB.ADD;
943 x_return_value := FALSE;
944 END IF;
945 --
946 RETURN (x_return_value);
947 --
948 END validate_prg_db_cons;
949 --
950 -- This function validates all the business rules before inserting a record
951 -- in the table IGS_PR_STDNT_PR_PS
952 --
953 FUNCTION validate_progression (
954 p_lgcy_prout_rec IN lgcy_prout_rec_type,
955 p_hold_effect_type IN VARCHAR2
956 ) RETURN BOOLEAN IS
957 --
958 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
959 --
960 BEGIN
961 --
962 -- Check that spo_program_cd is Not Null only when the hold effect type is
963 -- 'SUS_COURSE' or 'EXC_COURSE'
964 --
965 -- kdande; 24-Dec-2002; Bug# 2717616. Changed the OR condition to AND for
966 -- the following IF condition
967 --
968 IF ((INSTR (p_hold_effect_type, 'SUS_COURSE') = 0) AND
969 (INSTR (p_hold_effect_type, 'EXC_COURSE') = 0)) THEN
970 IF (p_lgcy_prout_rec.spo_program_cd IS NOT NULL) THEN
971 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRGOTCM_NOT_IN_SUS_EXC');
972 FND_MSG_PUB.ADD;
973 x_return_value := FALSE;
974 END IF;
975 END IF;
976 --
977 RETURN (x_return_value);
978 --
979 END validate_progression;
980 --
981 -- This function performs all the data integrity validation and keeps
982 -- adding error message to stack as an when it encounters one
983 --
984 FUNCTION validate_uset_db_cons (
985 p_lgcy_prout_rec IN lgcy_prout_rec_type,
986 p_person_id IN igs_pe_person.person_id%TYPE,
987 p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
988 ) RETURN BOOLEAN IS
989 --
990 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
991 --
992 BEGIN
993 --
994 -- Foreign Key Checks (Checking Parent Existence)
995 -- Unit Set Existence
996 --
997 IF ((p_lgcy_prout_rec.unit_set_cd IS NOT NULL) AND
998 (p_lgcy_prout_rec.us_version_number IS NOT NULL) AND
999 (NOT igs_en_unit_set_pkg.get_pk_for_validation (
1000 p_lgcy_prout_rec.unit_set_cd,
1001 p_lgcy_prout_rec.us_version_number
1002 ))) THEN
1003 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_USET_UNITSET_FK');
1004 FND_MSG_PUB.ADD;
1005 x_return_value := FALSE;
1006 END IF;
1007 --
1008 -- Student Progression Outcome Existence
1009 --
1010 IF ((p_person_id IS NOT NULL) AND
1011 (p_lgcy_prout_rec.program_cd IS NOT NULL) AND
1012 (p_sequence_number IS NOT NULL) AND
1013 (NOT igs_pr_stdnt_pr_ou_pkg.get_pk_for_validation (
1014 p_person_id,
1015 p_lgcy_prout_rec.program_cd,
1016 p_sequence_number
1017 ))) THEN
1018 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRG_SPO_FK');
1019 FND_MSG_PUB.ADD;
1020 x_return_value := FALSE;
1021 END IF;
1022 --
1023 -- Primary Key Validation
1024 --
1025 IF ((p_person_id IS NOT NULL) AND
1026 (p_lgcy_prout_rec.program_cd IS NOT NULL) AND
1027 (p_sequence_number IS NOT NULL) AND
1028 (p_lgcy_prout_rec.unit_set_cd IS NOT NULL) AND
1029 (p_lgcy_prout_rec.us_version_number IS NOT NULL) AND
1030 (igs_pr_sdt_pr_unt_st_pkg.get_pk_for_validation (
1031 p_person_id,
1032 p_lgcy_prout_rec.program_cd,
1033 p_sequence_number,
1034 p_lgcy_prout_rec.unit_set_cd,
1035 p_lgcy_prout_rec.us_version_number
1036 ))) THEN
1037 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_SPUSET_EXIST');
1038 FND_MSG_PUB.ADD;
1039 x_return_value := FALSE;
1040 END IF;
1041 --
1042 RETURN (x_return_value);
1043 --
1044 END validate_uset_db_cons;
1045 --
1046 -- This function validates all the business rules before inserting a record
1047 -- in the table IGS_PR_SDT_PR_UNT_ST
1048 --
1049 FUNCTION validate_unit_set (
1050 p_lgcy_prout_rec IN lgcy_prout_rec_type,
1051 p_person_id IN igs_pe_person.person_id%TYPE,
1052 p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE,
1053 p_hold_effect_type IN VARCHAR2
1054 ) RETURN BOOLEAN IS
1055 --
1056 CURSOR cur_us_uss IS
1057 SELECT 'Y'
1058 FROM igs_en_unit_set us,
1059 igs_en_unit_set_stat uss
1060 WHERE us.unit_set_cd = p_lgcy_prout_rec.unit_set_cd
1061 AND us.version_number = p_lgcy_prout_rec.us_version_number
1062 AND us.unit_set_status = uss.unit_set_status
1063 AND uss.s_unit_set_status <> 'PLANNED';
1064 --
1065 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1066 x_message_name fnd_new_messages.message_name%TYPE;
1067 l_record_found VARCHAR2(1) := 'N';
1068 --
1069 BEGIN
1070 --
1071 -- Check that Unit Set is Not Null only when the hold effect type is 'EXC_CRS_US'
1072 --
1073 IF ((INSTR (p_hold_effect_type, 'EXC_CRS_US') = 0) AND
1074 (p_lgcy_prout_rec.unit_set_cd IS NOT NULL) AND
1075 (p_lgcy_prout_rec.us_version_number IS NOT NULL)) THEN
1076 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_STPR_OUT_EXC_CRS_US');
1077 FND_MSG_PUB.ADD;
1078 x_return_value := FALSE;
1079 END IF;
1080 IF ((p_lgcy_prout_rec.unit_set_cd IS NOT NULL) AND
1081 (p_lgcy_prout_rec.us_version_number IS NOT NULL)) THEN
1082 IF (NOT igs_pr_val_spus.prgp_val_spus_spo (
1083 p_person_id,
1084 p_lgcy_prout_rec.program_cd,
1085 p_sequence_number,
1086 x_message_name
1087 )) THEN
1088 FND_MESSAGE.SET_NAME ('IGS', x_message_name);
1089 FND_MSG_PUB.ADD;
1090 x_return_value := FALSE;
1091 END IF;
1092 END IF;
1093 --
1094 -- Check that Unit Set is not Planned
1095 --
1096 IF ((p_lgcy_prout_rec.unit_set_cd IS NOT NULL) AND
1097 (p_lgcy_prout_rec.us_version_number IS NOT NULL)) THEN
1098 OPEN cur_us_uss;
1099 FETCH cur_us_uss INTO l_record_found;
1100 IF (cur_us_uss%NOTFOUND) THEN
1101 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_UNIT_SET_NOT_PLANNED');
1102 FND_MSG_PUB.ADD;
1103 x_return_value := FALSE;
1104 END IF;
1105 CLOSE cur_us_uss;
1106 END IF;
1107 --
1108 RETURN (x_return_value);
1109 --
1110 END validate_unit_set;
1111 --
1112 -- This function performs all the data integrity validation and keeps
1113 -- adding error message to stack as an when it encounters one
1114 --
1115 FUNCTION validate_unit_db_cons (
1116 p_lgcy_prout_rec IN lgcy_prout_rec_type,
1117 p_person_id IN igs_pe_person.person_id%TYPE,
1118 p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
1119 ) RETURN BOOLEAN IS
1120 --
1121 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1122 --
1123 BEGIN
1124 --
1125 -- Foreign Key Checks (Checking Parent Existence)
1126 -- Unit Existence
1127 --
1128 IF ((p_lgcy_prout_rec.unit_cd IS NOT NULL) AND
1129 (NOT igs_ps_unit_pkg.get_pk_for_validation (
1130 p_lgcy_prout_rec.unit_cd
1131 ))) THEN
1132 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_UNIT_UEXIST_FK');
1133 FND_MSG_PUB.ADD;
1134 x_return_value := FALSE;
1135 END IF;
1136 --
1137 -- Student Progression Outcome Existence
1138 --
1139 IF ((p_person_id IS NOT NULL) AND
1140 (p_lgcy_prout_rec.program_cd IS NOT NULL) AND
1141 (p_sequence_number IS NOT NULL) AND
1142 (NOT igs_pr_stdnt_pr_ou_pkg.get_pk_for_validation (
1143 p_person_id,
1144 p_lgcy_prout_rec.program_cd,
1145 p_sequence_number
1146 ))) THEN
1147 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRG_SPO_FK');
1148 FND_MSG_PUB.ADD;
1149 x_return_value := FALSE;
1150 END IF;
1151 --
1152 -- Primary Key Validation
1153 --
1154 IF ((p_person_id IS NOT NULL) AND
1155 (p_lgcy_prout_rec.program_cd IS NOT NULL) AND
1156 (p_sequence_number IS NOT NULL) AND
1157 (p_lgcy_prout_rec.unit_cd IS NOT NULL) AND
1158 (igs_pr_stdnt_pr_unit_pkg.get_pk_for_validation (
1159 p_person_id,
1160 p_lgcy_prout_rec.program_cd,
1161 p_sequence_number,
1162 p_lgcy_prout_rec.unit_cd
1163 ))) THEN
1164 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_SPUNIT_EXIST');
1165 FND_MSG_PUB.ADD;
1166 x_return_value := FALSE;
1167 END IF;
1168 --
1169 -- Valid Value Checks
1170 --
1171 IF (p_lgcy_prout_rec.s_unit_type IS NOT NULL) THEN
1172 BEGIN
1173 igs_pr_stdnt_pr_unit_pkg.check_constraints (
1174 'S_UNIT_TYPE',
1175 p_lgcy_prout_rec.s_unit_type
1176 );
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 --
1180 -- Delete the top message and add a new message in place of it
1181 --
1182 FND_MSG_PUB.Delete_Msg (FND_MSG_PUB.Count_Msg);
1183 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_UNIT_TYPE_VALID');
1184 FND_MSG_PUB.ADD;
1185 x_return_value := FALSE;
1186 END;
1187 END IF;
1188 --
1189 RETURN (x_return_value);
1190 --
1191 END validate_unit_db_cons;
1192 --
1193 -- This function validates all the business rules before inserting a record
1194 -- in the table IGS_PR_STDNT_PR_UNIT
1195 --
1196 FUNCTION validate_unit (
1197 p_lgcy_prout_rec IN lgcy_prout_rec_type,
1198 p_hold_effect_type IN VARCHAR2
1199 ) RETURN BOOLEAN IS
1200 --
1201 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1202 --
1203 BEGIN
1204 --
1205 -- Check that Unit is Not Null with s_unit_type as 'EXCLUDED' only when the
1206 -- hold effect type is 'EXC_CRS_U'
1207 --
1208 IF ((p_lgcy_prout_rec.unit_cd IS NOT NULL) AND
1209 (p_lgcy_prout_rec.s_unit_type = 'EXCLUDED') AND
1210 (INSTR (p_hold_effect_type, 'EXC_CRS_U') = 0)) THEN
1211 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_STPR_OUT_EXC_CRS_U');
1212 FND_MSG_PUB.ADD;
1213 x_return_value := FALSE;
1214 END IF;
1215 --
1216 -- Check that Unit is Not Null with s_unit_type as 'REQUIRED' only when the
1217 -- hold effect type is 'RQRD_CRS_U'
1218 --
1219 IF ((p_lgcy_prout_rec.unit_cd IS NOT NULL) AND
1220 (p_lgcy_prout_rec.s_unit_type = 'REQUIRED') AND
1221 (INSTR (p_hold_effect_type, 'RQRD_CRS_U') = 0)) THEN
1222 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_STPR_OUT_RQRD_CRS_U');
1223 FND_MSG_PUB.ADD;
1224 x_return_value := FALSE;
1225 END IF;
1226 --
1227 RETURN (x_return_value);
1228 --
1229 END validate_unit;
1230 --
1231 -- This function performs all the data integrity validation and keeps
1232 -- adding error message to stack as an when it encounters one
1233 --
1234 FUNCTION validate_awd_db_cons (
1235 p_lgcy_prout_rec IN lgcy_prout_rec_type,
1236 p_person_id IN igs_pe_person.person_id%TYPE,
1237 p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
1238 ) RETURN BOOLEAN IS
1239 --
1240 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1241 --
1242 BEGIN
1243 --
1244 -- Foreign Key Checks (Checking Parent Existence)
1245 -- Award Existence
1246 --
1247 IF ((p_lgcy_prout_rec.award_cd IS NOT NULL) AND
1248 (NOT igs_ps_awd_pkg.get_pk_for_validation (
1249 p_lgcy_prout_rec.award_cd
1250 ))) THEN
1251 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_AWD_AWARD_FK');
1252 FND_MSG_PUB.ADD;
1253 x_return_value := FALSE;
1254 END IF;
1255 --
1256 -- Student Progression Outcome Existence
1257 --
1258 IF ((p_person_id IS NOT NULL) AND
1259 (p_lgcy_prout_rec.program_cd IS NOT NULL) AND
1260 (p_sequence_number IS NOT NULL) AND
1261 (NOT igs_pr_stdnt_pr_ou_pkg.get_pk_for_validation (
1262 p_person_id,
1263 p_lgcy_prout_rec.program_cd,
1264 p_sequence_number
1265 ))) THEN
1266 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRG_SPO_FK');
1267 FND_MSG_PUB.ADD;
1268 x_return_value := FALSE;
1269 END IF;
1270 --
1271 -- Primary Key Validation
1272 --
1273 IF ((p_person_id IS NOT NULL) AND
1274 (p_lgcy_prout_rec.program_cd IS NOT NULL) AND
1275 (p_sequence_number IS NOT NULL) AND
1276 (p_lgcy_prout_rec.award_cd IS NOT NULL) AND
1277 (igs_pr_stdnt_pr_awd_pkg.get_pk_for_validation (
1278 p_person_id,
1279 p_lgcy_prout_rec.program_cd,
1280 p_sequence_number,
1281 p_lgcy_prout_rec.award_cd
1282 ))) THEN
1283 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_SPAWD_EXIST');
1284 FND_MSG_PUB.ADD;
1285 x_return_value := FALSE;
1286 END IF;
1287 --
1288 RETURN (x_return_value);
1289 --
1290 END validate_awd_db_cons;
1291 --
1292 -- This function validates all the business rules before inserting a record
1293 -- in the table IGS_PR_STDNT_PR_AWD
1294 --
1295 FUNCTION validate_award (
1296 p_lgcy_prout_rec IN lgcy_prout_rec_type
1297 ) RETURN BOOLEAN IS
1298 --
1299 CURSOR cur_award IS
1300 SELECT s_award_type
1301 FROM igs_ps_awd
1302 WHERE award_cd = p_lgcy_prout_rec.award_cd;
1303 --
1304 CURSOR cur_positive_otcm IS
1305 SELECT positive_outcome_ind
1306 FROM igs_pr_ou_type
1307 WHERE progression_outcome_type = p_lgcy_prout_rec.progression_outcome_type;
1308 --
1309 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1310 rec_award cur_award%ROWTYPE;
1311 rec_positive_otcm cur_positive_otcm%ROWTYPE;
1312 --
1313 BEGIN
1314 --
1315 -- Check that the Award must be a medal or prize
1316 --
1317 IF (p_lgcy_prout_rec.award_cd IS NOT NULL) THEN
1318 OPEN cur_award;
1319 FETCH cur_award INTO rec_award;
1320 CLOSE cur_award;
1321 IF (rec_award.s_award_type NOT IN ('MEDAL','PRIZE')) THEN
1322 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_AWD_MED_PRIZE');
1323 FND_MSG_PUB.ADD;
1324 x_return_value := FALSE;
1325 END IF;
1326 END IF;
1327 --
1328 -- Check that Award Code must be specified only for Positive Outcomes
1329 --
1330 IF (p_lgcy_prout_rec.award_cd IS NOT NULL) THEN
1331 OPEN cur_positive_otcm;
1332 FETCH cur_positive_otcm INTO rec_positive_otcm;
1333 CLOSE cur_positive_otcm;
1334 IF (rec_positive_otcm.positive_outcome_ind <> 'Y') THEN
1335 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_AWD_NOT_POS');
1336 FND_MSG_PUB.ADD;
1337 x_return_value := FALSE;
1338 END IF;
1339 END IF;
1340 --
1341 RETURN (x_return_value);
1342 --
1343 END validate_award;
1344 --
1345 --
1346 -- anilk, Bug# 3021236, adding fund_code to interface table.
1347 -- Hence creating this procedure.
1348 --
1349 -- This function performs all the data integrity validation and keeps
1350 -- adding error message to stack as an when it encounters one
1351 --
1352 FUNCTION validate_fnd_db_cons (
1353 p_lgcy_prout_rec IN lgcy_prout_rec_type,
1354 p_person_id IN igs_pe_person.person_id%TYPE,
1355 p_sequence_number IN igs_pr_stdnt_pr_ou.sequence_number%TYPE
1356 ) RETURN BOOLEAN IS
1357 --
1358 x_return_value BOOLEAN := FND_API.TO_BOOLEAN (FND_API.G_TRUE);
1359 --
1360 BEGIN
1361 --
1362 -- Foreign Key Checks (Checking Parent Existence)
1363 -- Fund Code Existence
1364 --
1365 IF ((p_lgcy_prout_rec.fund_code IS NOT NULL) AND
1366 (NOT igf_aw_fund_cat_pkg.get_uk_for_validation (
1367 p_lgcy_prout_rec.fund_code
1368 ))) THEN
1369 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_FUND_CODE_FK');
1370 FND_MSG_PUB.ADD;
1371 x_return_value := FALSE;
1372 END IF;
1373 --
1374 -- Primary Key Validation
1375 --
1376 IF ((p_person_id IS NOT NULL) AND
1377 (p_lgcy_prout_rec.program_cd IS NOT NULL) AND
1378 (p_sequence_number IS NOT NULL) AND
1379 (p_lgcy_prout_rec.fund_code IS NOT NULL) AND
1380 (igs_pr_stdnt_pr_fnd_pkg.get_pk_for_validation (
1381 p_person_id,
1382 p_lgcy_prout_rec.program_cd,
1383 p_sequence_number,
1384 p_lgcy_prout_rec.fund_code
1385 ))) THEN
1386 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_NO_SPFUND_EXIST');
1387 FND_MSG_PUB.ADD;
1388 x_return_value := FALSE;
1389 END IF;
1390 --
1391 RETURN (x_return_value);
1392 --
1393 END validate_fnd_db_cons;
1394 --
1395 PROCEDURE create_outcome (
1396 p_api_version IN NUMBER,
1397 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1398 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1399 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
1400 x_return_status OUT NOCOPY VARCHAR2,
1401 x_msg_count OUT NOCOPY NUMBER,
1402 x_msg_data OUT NOCOPY VARCHAR2,
1403 p_lgcy_prout_rec IN OUT NOCOPY lgcy_prout_rec_type
1404 ) IS
1405 --
1406 l_api_name CONSTANT VARCHAR2(30) := 'create_outcome';
1407 l_api_version CONSTANT NUMBER := 1.0;
1408 -- Local params
1409 l_person_id igs_pe_person.person_id%TYPE;
1410 l_prg_cal_type igs_ca_inst.cal_type%TYPE;
1411 l_prg_sequence_number igs_ca_inst.sequence_number%TYPE;
1412 l_outcome_sequence_number igs_pr_stdnt_pr_ou_all.sequence_number%TYPE;
1413 l_hold_effect_type VARCHAR2(2000);
1414 l_org_start_dt igs_pe_hz_parties.ou_start_dt%TYPE;
1415 l_return_value VARCHAR2(1);
1416 l_return_boolean_value BOOLEAN;
1417 l_rowid VARCHAR2(25);
1418 --
1419 BEGIN
1420 --
1421 -- Standard start of API savepoint
1422 --
1423 SAVEPOINT create_outcome;
1424 --
1425 -- Standard call to check for call compatibility.
1426 --
1427 IF (NOT FND_API.Compatible_API_Call (
1428 l_api_version,
1429 p_api_version,
1430 l_api_name,
1431 g_pkg_name)) THEN
1432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1433 END IF;
1434 --
1435 -- Initialize message list if p_init_msg_list is set to TRUE.
1436 --
1437 IF (FND_API.to_Boolean (p_init_msg_list)) THEN
1438 FND_MSG_PUB.Initialize;
1439 END IF;
1440 --
1441 -- Initialize API return status to success.
1442 --
1443 x_return_status := FND_API.G_RET_STS_SUCCESS;
1444 --
1445 -- API body
1446 --
1447 -- Validate the params passed to this API
1448 --
1449 IF (NOT validate_parameters (p_lgcy_prout_rec)) THEN
1450 x_return_status := FND_API.G_RET_STS_ERROR;
1451 RAISE FND_API.G_EXC_ERROR;
1452 END IF;
1453 --
1454 -- Derive Progression Outcome Type data
1455 --
1456 derive_pr_stnd_lvl_data (
1457 p_lgcy_prout_rec => p_lgcy_prout_rec,
1458 p_person_id => l_person_id,
1459 p_prg_cal_type => l_prg_cal_type,
1460 p_prg_sequence_number => l_prg_sequence_number,
1461 p_outcome_sequence_number => l_outcome_sequence_number,
1462 p_hold_effect_type => l_hold_effect_type,
1463 p_org_start_dt => l_org_start_dt,
1464 x_return_value => l_return_boolean_value
1465 );
1466 IF (NOT l_return_boolean_value) THEN
1467 x_return_status := FND_API.G_RET_STS_ERROR;
1468 RAISE FND_API.G_EXC_ERROR;
1469 END IF;
1470 --
1471 -- Check Student Progression Outcome Database Constraints
1472 --
1473 IF (NOT validate_spo_db_cons (
1474 p_lgcy_prout_rec => p_lgcy_prout_rec,
1475 p_person_id => l_person_id,
1476 p_sequence_number => l_outcome_sequence_number,
1477 p_org_start_dt => l_org_start_dt
1478 )) THEN
1479 x_return_status := FND_API.G_RET_STS_ERROR;
1480 RAISE FND_API.G_EXC_ERROR;
1481 END IF;
1482 --
1483 -- Validate all the business rules before inserting a record IGS_PR_STDNT_PR_OU
1484 --
1485 IF (NOT validate_stdnt_prg_otcm (
1486 p_lgcy_prout_rec => p_lgcy_prout_rec,
1487 p_person_id => l_person_id,
1488 p_prg_cal_type => l_prg_cal_type,
1489 p_prg_sequence_number => l_prg_sequence_number,
1490 p_outcome_sequence_number => l_outcome_sequence_number,
1491 p_hold_effect_type => l_hold_effect_type,
1492 p_decision_ou_start_dt => l_org_start_dt
1493 )) THEN
1494 x_return_status := FND_API.G_RET_STS_ERROR;
1495 RAISE FND_API.G_EXC_ERROR;
1496 END IF;
1497 --
1498 -- Create an entry in the Student Program Outcome table is does not already exist
1499 --
1500 IF (NOT igs_pr_stdnt_pr_ou_pkg.get_pk_for_validation (
1501 l_person_id,
1502 p_lgcy_prout_rec.program_cd,
1503 l_outcome_sequence_number
1504 )) THEN
1505 l_rowid := NULL;
1506 igs_pr_stdnt_pr_ou_pkg.insert_row (
1507 x_rowid => l_rowid,
1508 x_person_id => l_person_id,
1509 x_course_cd => p_lgcy_prout_rec.program_cd,
1510 x_sequence_number => l_outcome_sequence_number,
1511 x_prg_cal_type => l_prg_cal_type,
1512 x_prg_ci_sequence_number => l_prg_sequence_number,
1513 x_rule_check_dt => NULL,
1514 x_progression_rule_cat => NULL,
1515 x_pra_sequence_number => NULL,
1516 x_pro_sequence_number => NULL,
1517 x_progression_outcome_type => p_lgcy_prout_rec.progression_outcome_type,
1518 x_duration => p_lgcy_prout_rec.duration,
1519 x_duration_type => p_lgcy_prout_rec.duration_type,
1520 x_decision_status => p_lgcy_prout_rec.decision_status,
1521 x_decision_dt => p_lgcy_prout_rec.decision_dt,
1522 x_decision_org_unit_cd => p_lgcy_prout_rec.decision_org_unit_cd,
1523 x_decision_ou_start_dt => l_org_start_dt,
1524 x_applied_dt => NULL,
1525 x_show_cause_expiry_dt => p_lgcy_prout_rec.show_cause_expiry_dt,
1526 x_show_cause_dt => p_lgcy_prout_rec.show_cause_dt,
1527 x_show_cause_outcome_dt => p_lgcy_prout_rec.show_cause_outcome_dt,
1528 x_show_cause_outcome_type => p_lgcy_prout_rec.show_cause_outcome_type,
1529 x_appeal_expiry_dt => p_lgcy_prout_rec.appeal_expiry_dt,
1530 x_appeal_dt => p_lgcy_prout_rec.appeal_dt,
1531 x_appeal_outcome_dt => p_lgcy_prout_rec.appeal_outcome_dt,
1532 x_appeal_outcome_type => p_lgcy_prout_rec.appeal_outcome_type,
1533 x_encmb_course_group_cd => p_lgcy_prout_rec.encmb_program_group_cd,
1534 x_restricted_enrolment_cp => p_lgcy_prout_rec.restricted_enrolment_cp,
1535 x_restricted_attendance_type => p_lgcy_prout_rec.restricted_attendance_type,
1536 x_comments => p_lgcy_prout_rec.comments,
1537 x_show_cause_comments => p_lgcy_prout_rec.show_cause_comments,
1538 x_appeal_comments => p_lgcy_prout_rec.appeal_comments,
1539 x_expiry_dt => p_lgcy_prout_rec.expiry_dt,
1540 x_pro_pra_sequence_number => NULL,
1541 x_mode => 'R',
1542 x_org_id => igs_ge_gen_003.get_org_id
1543 );
1544 END IF;
1545 --
1546 -- Perform all the data integrity validations
1547 --
1548 IF (NOT validate_prg_db_cons (
1549 p_lgcy_prout_rec => p_lgcy_prout_rec,
1550 p_person_id => l_person_id,
1551 p_sequence_number => l_outcome_sequence_number
1552 )) THEN
1553 x_return_status := FND_API.G_RET_STS_ERROR;
1554 RAISE FND_API.G_EXC_ERROR;
1555 END IF;
1556 --
1557 -- Validate all the business rules before inserting a record into IGS_PR_STDNT_PR_PS
1558 --
1559 IF (NOT validate_progression (
1560 p_lgcy_prout_rec => p_lgcy_prout_rec,
1561 p_hold_effect_type => l_hold_effect_type
1562 )) THEN
1563 x_return_status := FND_API.G_RET_STS_ERROR;
1564 RAISE FND_API.G_EXC_ERROR;
1565 END IF;
1566 --
1567 -- Create an entry in the igs_pr_stdnt_pr_ps if the hold effect type is
1568 -- either 'SUS_COURSE' or 'EXC_COURSE'
1569 --
1570 -- kdande; 24-Dec-2002; Bug# 2717485. Added the following IF condition to
1571 -- create the record only if the hold effect type is either 'SUS_COURSE' or
1572 -- 'EXC_COURSE'
1573 --
1574 l_rowid := NULL;
1575 IF ((INSTR (l_hold_effect_type, 'SUS_COURSE') > 0) OR
1576 (INSTR (l_hold_effect_type, 'EXC_COURSE') > 0)) THEN
1577 IF (p_lgcy_prout_rec.spo_program_cd IS NOT NULL AND
1578 l_outcome_sequence_number IS NOT NULL AND
1579 p_lgcy_prout_rec.program_cd IS NOT NULL) THEN
1580 igs_pr_stdnt_pr_ps_pkg.insert_row (
1581 x_rowid => l_rowid,
1582 x_person_id => l_person_id,
1583 x_spo_course_cd => p_lgcy_prout_rec.spo_program_cd,
1584 x_spo_sequence_number => l_outcome_sequence_number,
1585 x_course_cd => p_lgcy_prout_rec.program_cd,
1586 x_mode => 'R'
1587 );
1588 END IF;
1589 END IF;
1590 --
1591 IF (INSTR (l_hold_effect_type, 'EXC_CRS_US') > 0) THEN
1592 --
1593 -- Perform all the data integrity validations
1594 --
1595 IF (NOT validate_uset_db_cons (
1596 p_lgcy_prout_rec => p_lgcy_prout_rec,
1597 p_person_id => l_person_id,
1598 p_sequence_number => l_outcome_sequence_number
1599 )) THEN
1600 x_return_status := FND_API.G_RET_STS_ERROR;
1601 RAISE FND_API.G_EXC_ERROR;
1602 END IF;
1603 --
1604 -- Validate all the business rules before inserting a record into IGS_PR_SDT_PR_UNT_ST
1605 --
1606 IF (NOT validate_unit_set (
1607 p_lgcy_prout_rec => p_lgcy_prout_rec,
1608 p_person_id => l_person_id,
1609 p_sequence_number => l_outcome_sequence_number,
1610 p_hold_effect_type => l_hold_effect_type
1611 )) THEN
1612 x_return_status := FND_API.G_RET_STS_ERROR;
1613 RAISE FND_API.G_EXC_ERROR;
1614 END IF;
1615 --
1616 -- Create an entry in the igs_pr_sdt_pr_unt_st if the hold effect type is 'EXC_CRS_US'
1617 --
1618 l_rowid := NULL;
1619 igs_pr_sdt_pr_unt_st_pkg.insert_row (
1620 x_rowid => l_rowid,
1621 x_person_id => l_person_id,
1622 x_course_cd => p_lgcy_prout_rec.program_cd,
1623 x_spo_sequence_number => l_outcome_sequence_number,
1624 x_unit_set_cd => p_lgcy_prout_rec.unit_set_cd,
1625 x_version_number => p_lgcy_prout_rec.us_version_number,
1626 x_mode => 'R'
1627 );
1628 END IF;
1629 --
1630 IF ((INSTR (l_hold_effect_type, 'EXC_CRS_U') > 0) OR
1631 (INSTR (l_hold_effect_type, 'RQRD_CRS_U') > 0)) THEN
1632 l_rowid := NULL;
1633 IF ((p_lgcy_prout_rec.unit_cd IS NOT NULL) AND
1634 (p_lgcy_prout_rec.s_unit_type IS NOT NULL)) THEN
1635 --
1636 -- Perform all the data integrity validations
1637 --
1638 IF (NOT validate_unit_db_cons (
1639 p_lgcy_prout_rec => p_lgcy_prout_rec,
1640 p_person_id => l_person_id,
1641 p_sequence_number => l_outcome_sequence_number
1642 )) THEN
1643 x_return_status := FND_API.G_RET_STS_ERROR;
1644 RAISE FND_API.G_EXC_ERROR;
1645 END IF;
1646 --
1647 -- Validate all the business rules before inserting a record into IGS_PR_STDNT_PR_UNIT
1648 --
1649 IF (NOT validate_unit (
1650 p_lgcy_prout_rec => p_lgcy_prout_rec,
1651 p_hold_effect_type => l_hold_effect_type
1652 )) THEN
1653 x_return_status := FND_API.G_RET_STS_ERROR;
1654 RAISE FND_API.G_EXC_ERROR;
1655 END IF;
1656 --
1657 -- Create an entry in the igs_pr_stdnt_pr_unit if the hold effect type is
1658 -- either 'EXC_CRS_U' or 'RQRD_CRS_U'
1659 --
1660 l_rowid := NULL;
1661 igs_pr_stdnt_pr_unit_pkg.insert_row (
1662 x_rowid => l_rowid,
1663 x_person_id => l_person_id,
1664 x_course_cd => p_lgcy_prout_rec.program_cd,
1665 x_spo_sequence_number => l_outcome_sequence_number,
1666 x_unit_cd => p_lgcy_prout_rec.unit_cd,
1667 x_s_unit_type => p_lgcy_prout_rec.s_unit_type,
1668 x_mode => 'R'
1669 );
1670 END IF;
1671 END IF;
1672 --
1673 IF (p_lgcy_prout_rec.award_cd IS NOT NULL) THEN
1674 --
1675 -- Perform all the data integrity validations
1676 --
1677 IF (NOT validate_awd_db_cons (
1678 p_lgcy_prout_rec => p_lgcy_prout_rec,
1679 p_person_id => l_person_id,
1680 p_sequence_number => l_outcome_sequence_number
1681 )) THEN
1682 x_return_status := FND_API.G_RET_STS_ERROR;
1683 RAISE FND_API.G_EXC_ERROR;
1684 END IF;
1685 --
1686 -- Validate all the business rules before inserting a record into IGS_PR_STDNT_PR_AWD
1687 --
1688 IF (NOT validate_award (
1689 p_lgcy_prout_rec => p_lgcy_prout_rec
1690 )) THEN
1691 x_return_status := FND_API.G_RET_STS_ERROR;
1692 RAISE FND_API.G_EXC_ERROR;
1693 END IF;
1694 --
1695 -- Create an entry in the Student Program Awards table if the outcome is positive
1696 --
1697 l_rowid := NULL;
1698 igs_pr_stdnt_pr_awd_pkg.insert_row (
1699 x_rowid => l_rowid,
1700 x_person_id => l_person_id,
1701 x_course_cd => p_lgcy_prout_rec.program_cd,
1702 x_spo_sequence_number => l_outcome_sequence_number,
1703 x_award_cd => p_lgcy_prout_rec.award_cd,
1704 x_mode => 'R'
1705 );
1706 END IF;
1707
1708 IF (p_lgcy_prout_rec.fund_code IS NOT NULL) THEN
1709 --anilk, Bug# 3021236, adding fund_code
1710 --
1711 -- Perform all the data integrity validations
1712 --
1713 IF (NOT validate_fnd_db_cons (
1714 p_lgcy_prout_rec => p_lgcy_prout_rec,
1715 p_person_id => l_person_id,
1716 p_sequence_number => l_outcome_sequence_number
1717 )) THEN
1718 x_return_status := FND_API.G_RET_STS_ERROR;
1719 RAISE FND_API.G_EXC_ERROR;
1720 END IF;
1721 --
1722 -- Create an entry in the igs_pr_stdnt_pr_fnd table
1723 --
1724 l_rowid := NULL;
1725 igs_pr_stdnt_pr_fnd_pkg.insert_row (
1726 x_rowid => l_rowid,
1727 x_person_id => l_person_id,
1728 x_course_cd => p_lgcy_prout_rec.program_cd,
1729 x_spo_sequence_number => l_outcome_sequence_number,
1730 x_fund_code => p_lgcy_prout_rec.fund_code,
1731 x_mode => 'R'
1732 );
1733 END IF;
1734
1735 --
1736 -- End of API body
1737 --
1738 -- Standard check of p_commit.
1739 --
1740 IF FND_API.To_Boolean (p_commit) THEN
1741 COMMIT WORK;
1742 END IF;
1743 --
1744 -- Standard call to get message count and if count is 1, get message info.
1745 --
1746 FND_MSG_PUB.Count_And_Get (
1747 p_count => x_msg_count,
1748 p_data => x_msg_data
1749 );
1750 --
1751 EXCEPTION
1752 WHEN FND_API.G_EXC_ERROR THEN
1753 ROLLBACK TO create_outcome;
1754 x_return_status := FND_API.G_RET_STS_ERROR;
1755 FND_MSG_PUB.Count_And_Get (
1756 p_count => x_msg_count,
1757 p_data => x_msg_data
1758 );
1759 --
1760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1761 ROLLBACK TO create_outcome;
1762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1763 FND_MSG_PUB.Count_And_Get (
1764 p_count => x_msg_count,
1765 p_data => x_msg_data
1766 );
1767 --
1768 WHEN OTHERS THEN
1769 ROLLBACK TO create_outcome;
1770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1771 --
1772 -- kdande; 02-Jan-2003; Bug# 2732568; Removed the call to
1773 -- FND_MSG_PUB.Add_Exc_Msg to avoid message repeatition.
1774 --
1775 FND_MSG_PUB.Count_And_Get (
1776 p_count => x_msg_count,
1777 p_data => x_msg_data
1778 );
1779 END create_outcome;
1780
1781 END igs_pr_prout_lgcy_pub;
[Home] [Help]