[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_SPA_TERMS_PKG
Source
1 PACKAGE BODY igs_en_spa_terms_pkg AS
2 /* $Header: IGSEI76B.pls 120.8 2005/10/07 03:06:20 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_spa_terms%ROWTYPE;
6 new_references igs_en_spa_terms%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_term_record_id IN NUMBER,
12 x_person_id IN NUMBER,
13 x_program_cd IN VARCHAR2,
14 x_program_version IN NUMBER,
15 x_acad_cal_type IN VARCHAR2,
16 x_term_cal_type IN VARCHAR2,
17 x_term_sequence_number IN NUMBER,
18 x_key_program_flag IN VARCHAR2,
19 x_location_cd IN VARCHAR2,
20 x_attendance_mode IN VARCHAR2,
21 x_attendance_type IN VARCHAR2,
22 x_fee_cat IN VARCHAR2,
23 x_coo_id IN NUMBER,
24 x_class_standing_id IN NUMBER,
25 x_attribute_category IN VARCHAR2,
26 x_attribute1 IN VARCHAR2,
27 x_attribute2 IN VARCHAR2,
28 x_attribute3 IN VARCHAR2,
29 x_attribute4 IN VARCHAR2,
30 x_attribute5 IN VARCHAR2,
31 x_attribute6 IN VARCHAR2,
32 x_attribute7 IN VARCHAR2,
33 x_attribute8 IN VARCHAR2,
34 x_attribute9 IN VARCHAR2,
35 x_attribute10 IN VARCHAR2,
36 x_attribute11 IN VARCHAR2,
37 x_attribute12 IN VARCHAR2,
38 x_attribute13 IN VARCHAR2,
39 x_attribute14 IN VARCHAR2,
40 x_attribute15 IN VARCHAR2,
41 x_attribute16 IN VARCHAR2,
42 x_attribute17 IN VARCHAR2,
43 x_attribute18 IN VARCHAR2,
44 x_attribute19 IN VARCHAR2,
45 x_attribute20 IN VARCHAR2,
46 x_creation_date IN DATE,
47 x_created_by IN NUMBER,
48 x_last_update_date IN DATE,
49 x_last_updated_by IN NUMBER,
50 x_last_update_login IN NUMBER,
51 x_plan_sht_status IN VARCHAR2
52 ) AS
53 /*
54 || Created By : ckasu
55 || Created On : 18-NOV-2003
56 || Purpose : Initialises the Old and New references for the columns of the table.
57 || Known limitations, enhancements or remarks :
58 || Change History :
59 || Who When What
60 || (reverse chronological order - newest change first)
61 */
62
63 CURSOR cur_old_ref_values IS
64 SELECT *
65 FROM igs_en_spa_terms
66 WHERE rowid = x_rowid;
67
68 BEGIN
69
70 l_rowid := x_rowid;
71
72 -- Code for setting the Old and New Reference Values.
73 -- Populate Old Values.
74 OPEN cur_old_ref_values;
75 FETCH cur_old_ref_values INTO old_references;
76 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
77 CLOSE cur_old_ref_values;
78 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
79 igs_ge_msg_stack.add;
80 app_exception.raise_exception;
81 RETURN;
82 END IF;
83 CLOSE cur_old_ref_values;
84
85 -- Populate New Values.
86 new_references.term_record_id := x_term_record_id;
87 new_references.person_id := x_person_id;
88 new_references.program_cd := x_program_cd;
89 new_references.program_version := x_program_version;
90 new_references.acad_cal_type := x_acad_cal_type;
91 new_references.term_cal_type := x_term_cal_type;
92 new_references.term_sequence_number := x_term_sequence_number;
93 new_references.key_program_flag := NVL(x_key_program_flag,'N');
94 new_references.location_cd := x_location_cd;
95 new_references.attendance_mode := x_attendance_mode;
96 new_references.attendance_type := x_attendance_type;
97 new_references.fee_cat := x_fee_cat;
98 new_references.coo_id := x_coo_id;
99 new_references.class_standing_id := x_class_standing_id;
100 new_references.attribute_category := x_attribute_category;
101 new_references.attribute1 := x_attribute1;
102 new_references.attribute2 := x_attribute2;
103 new_references.attribute3 := x_attribute3;
104 new_references.attribute4 := x_attribute4;
105 new_references.attribute5 := x_attribute5;
106 new_references.attribute6 := x_attribute6;
107 new_references.attribute7 := x_attribute7;
108 new_references.attribute8 := x_attribute8;
109 new_references.attribute9 := x_attribute9;
110 new_references.attribute10 := x_attribute10;
111 new_references.attribute11 := x_attribute11;
112 new_references.attribute12 := x_attribute12;
113 new_references.attribute13 := x_attribute13;
114 new_references.attribute14 := x_attribute14;
115 new_references.attribute15 := x_attribute15;
116 new_references.attribute16 := x_attribute16;
117 new_references.attribute17 := x_attribute17;
118 new_references.attribute18 := x_attribute18;
119 new_references.attribute19 := x_attribute19;
120 new_references.attribute20 := x_attribute20;
121 new_references.plan_sht_status := x_plan_sht_status;
122
123 IF (p_action = 'UPDATE') THEN
124 new_references.creation_date := old_references.creation_date;
125 new_references.created_by := old_references.created_by;
126 ELSE
127 new_references.creation_date := x_creation_date;
128 new_references.created_by := x_created_by;
129 END IF;
130
131 new_references.last_update_date := x_last_update_date;
132 new_references.last_updated_by := x_last_updated_by;
133 new_references.last_update_login := x_last_update_login;
134
135 END set_column_values;
136
137 PROCEDURE Check_Parent_Existance AS
138 /*
139 || Created By : ckasu
140 || Created On : 04-DEC-2003
141 || Purpose : Checks for the existance of Parent record.
142 || Known limitations, enhancements or remarks :
143 || Change History :
144 || Who When What
145 || (reverse chronological order - newest change first)
146 */
147 BEGIN
148
149
150 IF (((old_references.person_id = new_references.person_id) AND
151 (old_references.program_cd = new_references.program_cd)) OR
152 ((new_references.person_id IS NULL) OR
153 (new_references.program_cd IS NULL))) THEN
154 NULL;
155 ELSIF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
156 new_references.person_id,
157 new_references.program_cd
158 ) THEN
159 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
160 IGS_GE_MSG_STACK.ADD;
161 APP_EXCEPTION.RAISE_EXCEPTION;
162 END IF;
163
164 IF (((old_references.TERM_CAL_TYPE = new_references.TERM_CAL_TYPE) AND
165 (old_references.TERM_SEQUENCE_NUMBER = new_references.TERM_SEQUENCE_NUMBER)) OR
166 ((new_references.TERM_CAL_TYPE IS NULL) OR
167 (new_references.TERM_SEQUENCE_NUMBER IS NULL))) THEN
168 NULL;
169 ELSIF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
170 new_references.TERM_CAL_TYPE,
171 new_references.TERM_SEQUENCE_NUMBER
172 ) THEN
173 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END IF;
177
178 IF (((old_references.coo_id = new_references.coo_id)) OR
179 ((new_references.coo_id IS NULL))) THEN
180 NULL;
181 ELSE
182 IF NOT IGS_PS_OFR_OPT_PKG.Get_UK_For_Validation (
183 new_references.coo_id
184 ) THEN
185 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
186 IGS_GE_MSG_STACK.ADD;
187 App_Exception.Raise_Exception;
188 END IF;
189 END IF;
190
191 IF (((old_references.fee_cat = new_references.fee_cat)) OR
192 ((new_references.fee_cat IS NULL))) THEN
193 NULL;
194 ELSIF NOT IGS_FI_FEE_CAT_PKG.Get_PK_For_Validation (
195 new_references.fee_cat
196 ) THEN
197 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 END IF;
201
202 IF ((old_references.CLASS_STANDING_ID = new_references.CLASS_STANDING_ID)OR
203 (new_references.CLASS_STANDING_ID IS NULL)) THEN
204 NULL;
205 ELSIF NOT IGS_PR_CLASS_STD_PKG.Get_PK_For_Validation (
206 new_references.CLASS_STANDING_ID
207 ) THEN
208 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF;
212
213
214 END Check_Parent_Existance;
215
216 PROCEDURE GET_FK_IGS_CA_INST (
217 X_CAL_TYPE IN VARCHAR2,
218 X_SEQUENCE_NUMBER IN NUMBER
219 ) AS
220 /*
221 || Created By : ckasu
222 || Created On : 04-DEC-2003
223 || Purpose : Validates the Foreign Key of the table.
224 || Known limitations, enhancements or remarks :
225 || Change History :
226 || (reverse chronological order - newest change first)
227 || Who When What
228 || ckasu 26-May-2004 Modified Message name from IGS_EN_ SPAT _CI_FK
229 || to IGS_EN_SPAT_CI_FK.
230 ||
231 */
232 CURSOR cur_rowid IS
233 SELECT ROWID
234 FROM IGS_EN_SPA_TERMS
235 WHERE TERM_CAL_TYPE = X_CAL_TYPE AND
236 TERM_SEQUENCE_NUMBER = X_SEQUENCE_NUMBER ;
237 lv_rowid cur_rowid%ROWTYPE;
238
239 BEGIN
240 OPEN cur_rowid;
241 FETCH cur_rowid INTO lv_rowid;
242 IF (cur_rowid%FOUND) THEN
243 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SPAT_CI_FK');
244 Igs_Ge_Msg_Stack.ADD;
245 CLOSE cur_rowid;
246 App_Exception.Raise_Exception;
247 RETURN;
248 END IF;
249 CLOSE cur_rowid;
250 END GET_FK_IGS_CA_INST;
251
252 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
253 x_person_id IN NUMBER,
254 x_course_cd IN VARCHAR2
255 ) AS
256 /*
257 || Created By : ckasu
258 || Created On : 04-DEC-2003
259 || Purpose : Validates the Foreign Key of the table.
260 || Known limitations, enhancements or remarks :
261 || Change History :
262 || Who When What
263 || (reverse chronological order - newest change first)
264 */
265 CURSOR cur_rowid IS
266 SELECT ROWID
267 FROM IGS_EN_SPA_TERMS
268 WHERE person_id = x_person_id
269 AND program_cd = x_course_cd ;
270 lv_rowid cur_rowid%ROWTYPE;
271 BEGIN
272 OPEN cur_rowid;
273 FETCH cur_rowid INTO lv_rowid;
274 IF (cur_rowid%FOUND) THEN
275 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SPAT_SPA_FK');
276 IGS_GE_MSG_STACK.ADD;
277 CLOSE cur_rowid;
278 APP_EXCEPTION.RAISE_EXCEPTION;
279 RETURN;
280 END IF;
281 CLOSE cur_rowid;
282 END GET_FK_IGS_EN_STDNT_PS_ATT;
283
284 PROCEDURE GET_FK_IGS_PR_CLASS_STD(
285 X_IGS_PR_CLASS_STD_ID IN NUMBER
286 ) AS
287 /*
288 || Created By : ckasu
289 || Created On : 04-DEC-2003
290 || Purpose : Validates the Foreign Key of the table.
291 || Known limitations, enhancements or remarks :
292 || Change History :
293 || Who When What
294 || (reverse chronological order - newest change first)
295 */
296 CURSOR cur_rowid IS
297 SELECT ROWID
298 FROM IGS_EN_SPA_TERMS
299 WHERE CLASS_STANDING_ID = X_IGS_PR_CLASS_STD_ID;
300
301 lv_cur cur_rowid%ROWTYPE;
302
303 BEGIN
304 OPEN cur_rowid;
305 FETCH cur_rowid INTO lv_cur;
306 IF (cur_rowid%FOUND) THEN
307 CLOSE cur_rowid;
308 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SPAT_CS_FK');
309 IGS_GE_MSG_STACK.ADD;
310 App_Exception.Raise_Exception;
311 RETURN;
312 END IF;
313 CLOSE cur_rowid;
314 END GET_FK_IGS_PR_CLASS_STD;
315
316 PROCEDURE GET_FK_IGS_FI_FEE_CAT (
317 x_fee_cat IN VARCHAR2
318 ) AS
319 /*
320 || Created By : ckasu
321 || Created On : 04-DEC-2003
322 || Purpose : Validates the Foreign Key of the table.
323 || Known limitations, enhancements or remarks :
324 || Change History :
325 || Who When What
326 || (reverse chronological order - newest change first)
327 */
328 CURSOR cur_rowid IS
329 SELECT ROWID
330 FROM IGS_EN_SPA_TERMS
331 WHERE fee_cat = x_fee_cat ;
332 lv_rowid cur_rowid%ROWTYPE;
333 BEGIN
334 OPEN cur_rowid;
335 FETCH cur_rowid INTO lv_rowid;
336 IF (cur_rowid%FOUND) THEN
337 CLOSE cur_rowid;
338 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SPAT_FC_FK');
339 IGS_GE_MSG_STACK.ADD;
340 App_Exception.Raise_Exception;
341 RETURN;
342 END IF;
343 CLOSE cur_rowid;
344 END GET_FK_IGS_FI_FEE_CAT;
345
346 PROCEDURE GET_UFK_IGS_PS_OFR_OPT (
347 x_coo_id IN VARCHAR2
348 ) AS
349 /*
350 || Created By : ckasu
351 || Created On : 04-DEC-2003
352 || Purpose : Validates the Foreign Key of the table.
353 || Known limitations, enhancements or remarks :
354 || Change History :
355 || Who When What
356 || (reverse chronological order - newest change first)
357 */
358 CURSOR cur_rowid IS
359 SELECT ROWID
360 FROM IGS_EN_SPA_TERMS
361 WHERE coo_id = x_coo_id;
362 lv_rowid cur_rowid%ROWTYPE;
363 BEGIN
364 OPEN cur_rowid;
365 FETCH cur_rowid INTO lv_rowid;
366 IF (cur_rowid%FOUND) THEN
367 CLOSE cur_rowid;
368 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SPAT_COO_FK');
369 IGS_GE_MSG_STACK.ADD;
370 App_Exception.Raise_Exception;
371 RETURN;
372 END IF;
373 CLOSE cur_rowid;
374 END GET_UFK_IGS_PS_OFR_OPT;
375
376
377 FUNCTION get_pk_for_validation (
378 x_term_record_id IN NUMBER
379 ) RETURN BOOLEAN AS
380 /*
381 || Created By : ckasu
382 || Created On : 18-NOV-2003
383 || Purpose : Validates the Primary Key of the table.
384 || Known limitations, enhancements or remarks :
385 || Change History :
386 || Who When What
387 || (reverse chronological order - newest change first)
388 */
389 CURSOR cur_rowid IS
390 SELECT rowid
391 FROM igs_en_spa_terms
392 WHERE term_record_id = x_term_record_id
393 FOR UPDATE NOWAIT;
394
395 lv_rowid cur_rowid%RowType;
396
397 BEGIN
398
399 OPEN cur_rowid;
400 FETCH cur_rowid INTO lv_rowid;
401 IF (cur_rowid%FOUND) THEN
402 CLOSE cur_rowid;
403 RETURN(TRUE);
404 ELSE
405 CLOSE cur_rowid;
406 RETURN(FALSE);
407 END IF;
408
409 END get_pk_for_validation;
410
411 FUNCTION get_uk_for_validation (
412 x_person_id IN NUMBER,
413 x_program_cd IN VARCHAR2,
414 x_term_cal_type IN VARCHAR2,
415 x_term_sequence_number IN NUMBER
416
417 ) RETURN BOOLEAN AS
418 /*
419 || Created By : ckasu
420 || Created On : 18-NOV-2003
421 || Purpose : Validates the Unique Key of the table.
422 || Known limitations, enhancements or remarks :
423 || Change History :
424 || Who When What
425 || (reverse chronological order - newest change first)
426 */
427
428 CURSOR cur_rowid is
429 SELECT rowid
430 FROM igs_en_spa_terms
431 WHERE x_person_id = person_id AND
432 x_program_cd = program_cd AND
433 x_term_cal_type = term_cal_type AND
434 x_term_sequence_number = term_sequence_number
435 FOR UPDATE NOWAIT;
436
437 lv_rowid cur_rowid%RowType;
438
439 BEGIN
440
441 OPEN cur_rowid;
442 FETCH cur_rowid into lv_rowid;
443
444 IF (cur_rowid%FOUND) THEN
445 CLOSE cur_rowid;
446 RETURN TRUE;
447 ELSE
448 CLOSE cur_rowid;
449 RETURN FALSE;
450 END IF;
451
452 END get_uk_for_validation;
453
454 -- code added by ckasu as a part of bug no#3631488
455 PROCEDURE BeforeRowInsertUpdate(
456 p_inserting IN BOOLEAN,
457 p_updating IN BOOLEAN
458 ) AS
459 /*
460 || Created By : ckasu
461 || Created On : 26-MAY-2004
462 || Change History :
463 || (reverse chronological order - newest change first)
464 || Who When What
465 ||ckasu 26-May-2004 Procedure Created inorder to encapsulate logic
466 || to create TODO record in master and detail table
467 || as a part of bug#3631488
468 */
469 v_sequence_number NUMBER;
470 BEGIN
471 -- Log an entry in the IGS_PE_STD_TODO table, indicating that a fee re-assessment
472 -- is required.
473 IF p_inserting OR p_updating THEN
474
475 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
476 new_references.person_id,
477 'FEE_RECALC',
478 SYSDATE,
479 'Y');
480 ELSE
481
482 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
483 old_references.person_id,
484 'FEE_RECALC',
485 SYSDATE,
486 'Y');
487
488
489 END IF;
490 -- creates a entry in Child Table.
491 IGS_GE_GEN_003.GENP_INS_TODO_REF(
492 p_person_id => new_references.person_id,
493 p_s_student_todo_type => 'FEE_RECALC',
494 p_sequence_number => v_sequence_number,
495 p_cal_type => new_references.term_cal_type,
496 p_ci_sequence_number =>new_references.term_sequence_number,
497 p_course_cd => new_references.program_cd,
498 p_unit_cd => null,
499 p_uoo_id => null,
500 p_other_reference => null
501 );
502
503
504
505 END BeforeRowInsertUpdate;
506 -- end of code added by ckasu
507
508
509 PROCEDURE before_dml (
510 p_action IN VARCHAR2,
511 x_rowid IN VARCHAR2,
512 x_term_record_id IN NUMBER,
513 x_person_id IN NUMBER,
514 x_program_cd IN VARCHAR2,
515 x_program_version IN NUMBER,
516 x_acad_cal_type IN VARCHAR2,
517 x_term_cal_type IN VARCHAR2,
518 x_term_sequence_number IN NUMBER,
519 x_key_program_flag IN VARCHAR2,
520 x_location_cd IN VARCHAR2,
521 x_attendance_mode IN VARCHAR2,
522 x_attendance_type IN VARCHAR2,
523 x_fee_cat IN VARCHAR2,
524 x_coo_id IN NUMBER,
525 x_class_standing_id IN NUMBER,
526 x_attribute_category IN VARCHAR2,
527 x_attribute1 IN VARCHAR2,
528 x_attribute2 IN VARCHAR2,
529 x_attribute3 IN VARCHAR2,
530 x_attribute4 IN VARCHAR2,
531 x_attribute5 IN VARCHAR2,
532 x_attribute6 IN VARCHAR2,
533 x_attribute7 IN VARCHAR2,
534 x_attribute8 IN VARCHAR2,
535 x_attribute9 IN VARCHAR2,
536 x_attribute10 IN VARCHAR2,
537 x_attribute11 IN VARCHAR2,
538 x_attribute12 IN VARCHAR2,
539 x_attribute13 IN VARCHAR2,
540 x_attribute14 IN VARCHAR2,
541 x_attribute15 IN VARCHAR2,
542 x_attribute16 IN VARCHAR2,
543 x_attribute17 IN VARCHAR2,
544 x_attribute18 IN VARCHAR2,
545 x_attribute19 IN VARCHAR2,
546 x_attribute20 IN VARCHAR2,
547 x_creation_date IN DATE,
548 x_created_by IN NUMBER,
549 x_last_update_date IN DATE,
550 x_last_updated_by IN NUMBER,
551 x_last_update_login IN NUMBER,
552 x_plan_sht_status IN VARCHAR2
553 ) AS
554 /*
555 || Created By : ckasu
556 || Created On : 18-NOV-2003
557 || Purpose : Initialises the columns, Checks Constraints, Calls the
558 || Trigger Handlers for the table, before any DML operation.
559 || Known limitations, enhancements or remarks :
560 || Change History :
561 || (reverse chronological order - newest change first)
562 || Who When What
563 ||
564 || ckasu 26-May-2004 Modified before_dml Procedure by adding call to
565 || beforeinsertupdate Procedure ,inorder to create
566 || TODO record in master and detail table as a part
567 || of bug#3631488
568 ||
569 */
570
571 Cursor cur_rowid(p_coo_id NUMBER,p_term_cal_type VARCHAR2,p_term_sequence_number VARCHAR2) is
572
573 SELECT 'x'
574 FROM igs_ps_ofr_opt cop,
575 igs_ca_inst_rel cr
576 WHERE cop.cal_type = cr.sup_cal_type AND
577 cop.coo_id = p_coo_id AND
578 cr.sub_cal_type = p_term_cal_type AND
579 cr.sub_ci_sequence_number = p_term_sequence_number;
580
581
582 l_val cur_rowid%ROWTYPE;
583
584 BEGIN
585
586 set_column_values (
587 p_action,
588 x_rowid,
589 x_term_record_id,
590 x_person_id,
591 x_program_cd,
592 x_program_version,
593 x_acad_cal_type,
594 x_term_cal_type,
595 x_term_sequence_number,
596 x_key_program_flag,
597 x_location_cd,
598 x_attendance_mode,
599 x_attendance_type,
600 x_fee_cat,
601 x_coo_id,
602 x_class_standing_id,
603 x_attribute_category,
604 x_attribute1,
605 x_attribute2,
606 x_attribute3,
607 x_attribute4,
608 x_attribute5,
609 x_attribute6,
610 x_attribute7,
611 x_attribute8,
612 x_attribute9,
613 x_attribute10,
614 x_attribute11,
615 x_attribute12,
616 x_attribute13,
617 x_attribute14,
618 x_attribute15,
619 x_attribute16,
620 x_attribute17,
621 x_attribute18,
622 x_attribute19,
623 x_attribute20,
624 x_creation_date,
625 x_created_by,
626 x_last_update_date,
627 x_last_updated_by,
628 x_last_update_login,
629 x_plan_sht_status
630 );
631
632 IF (p_action = 'INSERT') THEN
633 -- Call all the procedures related to Before Insert.
634
635 IF ( get_pk_for_validation(
636 new_references.term_record_id
637 )
638 ) THEN
639 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
640 igs_ge_msg_stack.add;
641 app_exception.raise_exception;
642 END IF;
643
644 -- Code added by ckasu as a part of
645 -- bug no #3631488 inorder to create TODO record in both Parent
646 -- and child table when Term Record is Created
647
648 BeforeRowInsertUpdate ( p_inserting => TRUE,
649 p_updating => FALSE
650 );
651 -- end of code added by ckasu
652 ELSIF (p_action = 'VALIDATE_INSERT') THEN
653 -- Call all the procedures related to Before Insert.
654 IF ( get_pk_for_validation (
655 new_references.term_record_id
656 )
657 ) THEN
658 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
659 igs_ge_msg_stack.add;
660 app_exception.raise_exception;
661 END IF;
662 ELSIF (p_action = 'DELETE') THEN
663 check_child_existence(new_references.person_id,new_references.program_cd);
664
665 ELSIF (p_action = 'VALIDATE_DELETE') THEN
666 check_child_existence(new_references.person_id,new_references.program_cd);
667
668 ELSIF (p_action = 'UPDATE') THEN
669 -- Call all the procedures related to Before Insert.
670
671 -- Code added by ckasu as a part of
672 -- bug no #3631488 inorder to create TODO record in both Parent
673 -- and child table when Term Record is Updated
674 BeforeRowInsertUpdate ( p_inserting => FALSE,
675 p_updating => TRUE
676 );
677 -- end of code added by ckasu
678 IF( new_references.coo_id <> old_references.coo_id) THEN
679 OPEN cur_rowid(new_references.coo_id ,new_references.term_cal_type,new_references.term_sequence_number);
680 FETCH cur_rowid INTO l_val;
681 IF (cur_rowid%NOTFOUND) THEN
682 CLOSE cur_rowid;
683 fnd_message.set_name('IGS','IGS_AD_NOMINATED_PRG_NOTEXIST');
684 igs_ge_msg_stack.add;
685 app_exception.raise_exception;
686 END IF;
687 CLOSE cur_rowid;
688 END IF;
689
690 END IF;
691
692
693 END before_dml;
694
695
696 PROCEDURE insert_row (
697 x_rowid IN OUT NOCOPY VARCHAR2,
698 x_term_record_id IN OUT NOCOPY NUMBER,
699 x_person_id IN NUMBER,
700 x_program_cd IN VARCHAR2,
701 x_program_version IN NUMBER,
702 x_acad_cal_type IN VARCHAR2,
703 x_term_cal_type IN VARCHAR2,
704 x_term_sequence_number IN NUMBER,
705 x_key_program_flag IN VARCHAR2,
706 x_location_cd IN VARCHAR2,
707 x_attendance_mode IN VARCHAR2,
708 x_attendance_type IN VARCHAR2,
709 x_fee_cat IN VARCHAR2,
710 x_coo_id IN NUMBER,
711 x_class_standing_id IN NUMBER,
712 x_attribute_category IN VARCHAR2,
713 x_attribute1 IN VARCHAR2,
714 x_attribute2 IN VARCHAR2,
715 x_attribute3 IN VARCHAR2,
716 x_attribute4 IN VARCHAR2,
717 x_attribute5 IN VARCHAR2,
718 x_attribute6 IN VARCHAR2,
719 x_attribute7 IN VARCHAR2,
720 x_attribute8 IN VARCHAR2,
721 x_attribute9 IN VARCHAR2,
722 x_attribute10 IN VARCHAR2,
723 x_attribute11 IN VARCHAR2,
724 x_attribute12 IN VARCHAR2,
725 x_attribute13 IN VARCHAR2,
726 x_attribute14 IN VARCHAR2,
727 x_attribute15 IN VARCHAR2,
728 x_attribute16 IN VARCHAR2,
729 x_attribute17 IN VARCHAR2,
730 x_attribute18 IN VARCHAR2,
731 x_attribute19 IN VARCHAR2,
732 x_attribute20 IN VARCHAR2,
733 x_plan_sht_status IN VARCHAR2,
734 x_mode IN VARCHAR2
735 ) AS
736 /*
737 || Created By : ckasu
738 || Created On : 18-NOV-2003
739 || Purpose : Handles the INSERT DML logic for the table.
740 || Known limitations, enhancements or remarks :
741 || Change History :
742 || Who When What
743 || (reverse chronological order - newest change first)
744 */
745
746 x_last_update_date DATE;
747 x_last_updated_by NUMBER;
748 x_last_update_login NUMBER;
749 x_request_id NUMBER;
750 x_program_id NUMBER;
751 x_program_application_id NUMBER;
752 x_program_update_date DATE;
753
754 BEGIN
755
756 x_last_update_date := sysdate;
757 IF (x_mode = 'I') THEN
758 x_last_updated_by := 1;
759 x_last_update_login := 0;
760 ELSIF (X_MODE IN ('R', 'S')) THEN
761 x_last_updated_by := fnd_global.user_id;
762 IF (x_last_updated_by IS NULL) THEN
763 x_last_updated_by := -1;
764 END IF;
765 x_last_update_login := fnd_global.login_id;
766 IF (x_last_update_login IS NULL) THEN
767 x_last_update_login := -1;
768 END IF;
769 x_request_id := fnd_global.conc_request_id;
770 x_program_id := fnd_global.conc_program_id;
771 x_program_application_id := fnd_global.prog_appl_id;
772
773 IF (x_request_id = -1) THEN
774 x_request_id := NULL;
775 x_program_id := NULL;
776 x_program_application_id := NULL;
777 x_program_update_date := NULL;
778 ELSE
779 x_program_update_date := sysdate;
780 END IF;
781 ELSE
782 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
783 fnd_message.set_token ('ROUTINE', 'IGS_EN_SPA_TERMS_PKG.INSERT_ROW');
784 igs_ge_msg_stack.add;
785 app_exception.raise_exception;
786 END IF;
787
788 x_term_record_id := NULL;
789
790 before_dml(
791 p_action => 'INSERT',
792 x_rowid => x_rowid,
793 x_term_record_id => x_term_record_id,
794 x_person_id => x_person_id,
795 x_program_cd => x_program_cd,
796 x_program_version => x_program_version,
797 x_acad_cal_type => x_acad_cal_type,
798 x_term_cal_type => x_term_cal_type,
799 x_term_sequence_number => x_term_sequence_number,
800 x_key_program_flag => x_key_program_flag,
801 x_location_cd => x_location_cd,
802 x_attendance_mode => x_attendance_mode,
803 x_attendance_type => x_attendance_type,
804 x_fee_cat => x_fee_cat,
805 x_coo_id => x_coo_id,
806 x_class_standing_id => x_class_standing_id,
807 x_attribute_category => x_attribute_category,
808 x_attribute1 => x_attribute1,
809 x_attribute2 => x_attribute2,
810 x_attribute3 => x_attribute3,
811 x_attribute4 => x_attribute4,
812 x_attribute5 => x_attribute5,
813 x_attribute6 => x_attribute6,
814 x_attribute7 => x_attribute7,
815 x_attribute8 => x_attribute8,
816 x_attribute9 => x_attribute9,
817 x_attribute10 => x_attribute10,
818 x_attribute11 => x_attribute11,
819 x_attribute12 => x_attribute12,
820 x_attribute13 => x_attribute13,
821 x_attribute14 => x_attribute14,
822 x_attribute15 => x_attribute15,
823 x_attribute16 => x_attribute16,
824 x_attribute17 => x_attribute17,
825 x_attribute18 => x_attribute18,
826 x_attribute19 => x_attribute19,
827 x_attribute20 => x_attribute20,
828 x_creation_date => x_last_update_date,
829 x_created_by => x_last_updated_by,
830 x_last_update_date => x_last_update_date,
831 x_last_updated_by => x_last_updated_by,
832 x_last_update_login => x_last_update_login,
833 x_plan_sht_status => x_plan_sht_status
834 );
835
836 IF (x_mode = 'S') THEN
837 igs_sc_gen_001.set_ctx('R');
838 END IF;
839 INSERT INTO igs_en_spa_terms (
840 term_record_id,
841 person_id,
842 program_cd,
843 program_version,
844 acad_cal_type,
845 term_cal_type,
846 term_sequence_number,
847 key_program_flag,
848 location_cd,
849 attendance_mode,
850 attendance_type,
851 fee_cat,
852 coo_id,
853 class_standing_id,
854 attribute_category,
855 attribute1,
856 attribute2,
857 attribute3,
858 attribute4,
859 attribute5,
860 attribute6,
861 attribute7,
862 attribute8,
863 attribute9,
864 attribute10,
865 attribute11,
866 attribute12,
867 attribute13,
868 attribute14,
869 attribute15,
870 attribute16,
871 attribute17,
872 attribute18,
873 attribute19,
874 attribute20,
875 creation_date,
876 created_by,
877 last_update_date,
878 last_updated_by,
879 last_update_login,
880 request_id,
881 program_id,
882 program_application_id,
883 program_update_date,
884 plan_sht_status
885 ) VALUES (
886 igs_en_spa_terms_s.NEXTVAL,
887 new_references.person_id,
888 new_references.program_cd,
889 new_references.program_version,
890 new_references.acad_cal_type,
891 new_references.term_cal_type,
892 new_references.term_sequence_number,
893 new_references.key_program_flag,
894 new_references.location_cd,
895 new_references.attendance_mode,
896 new_references.attendance_type,
897 new_references.fee_cat,
898 new_references.coo_id,
899 new_references.class_standing_id,
900 new_references.attribute_category,
901 new_references.attribute1,
902 new_references.attribute2,
903 new_references.attribute3,
904 new_references.attribute4,
905 new_references.attribute5,
906 new_references.attribute6,
907 new_references.attribute7,
908 new_references.attribute8,
909 new_references.attribute9,
910 new_references.attribute10,
911 new_references.attribute11,
912 new_references.attribute12,
913 new_references.attribute13,
914 new_references.attribute14,
915 new_references.attribute15,
916 new_references.attribute16,
917 new_references.attribute17,
918 new_references.attribute18,
919 new_references.attribute19,
920 new_references.attribute20,
921 x_last_update_date,
922 x_last_updated_by,
923 x_last_update_date,
924 x_last_updated_by,
925 x_last_update_login ,
926 x_request_id,
927 x_program_id,
928 x_program_application_id,
929 x_program_update_date,
930 new_references.plan_sht_status
931 ) RETURNING ROWID, term_record_id INTO x_rowid, x_term_record_id;
932 IF (x_mode = 'S') THEN
933 igs_sc_gen_001.unset_ctx('R');
934 END IF;
935
936
937
938 EXCEPTION
939 WHEN OTHERS THEN
940 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
941 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
942 fnd_message.set_token ('ERR_CD', SQLCODE);
943 igs_ge_msg_stack.add;
944 igs_sc_gen_001.unset_ctx('R');
945 app_exception.raise_exception;
946 ELSE
947 igs_sc_gen_001.unset_ctx('R');
948 RAISE;
949 END IF;
950 END insert_row;
951
952
953 PROCEDURE lock_row (
954 x_rowid IN VARCHAR2,
955 x_term_record_id IN NUMBER,
956 x_person_id IN NUMBER,
957 x_program_cd IN VARCHAR2,
958 x_program_version IN NUMBER,
959 x_acad_cal_type IN VARCHAR2,
960 x_term_cal_type IN VARCHAR2,
961 x_term_sequence_number IN NUMBER,
962 x_key_program_flag IN VARCHAR2,
963 x_location_cd IN VARCHAR2,
964 x_attendance_mode IN VARCHAR2,
965 x_attendance_type IN VARCHAR2,
966 x_fee_cat IN VARCHAR2,
967 x_coo_id IN NUMBER,
968 x_class_standing_id IN NUMBER,
969 x_attribute_category IN VARCHAR2,
970 x_attribute1 IN VARCHAR2,
971 x_attribute2 IN VARCHAR2,
972 x_attribute3 IN VARCHAR2,
973 x_attribute4 IN VARCHAR2,
974 x_attribute5 IN VARCHAR2,
975 x_attribute6 IN VARCHAR2,
976 x_attribute7 IN VARCHAR2,
977 x_attribute8 IN VARCHAR2,
978 x_attribute9 IN VARCHAR2,
979 x_attribute10 IN VARCHAR2,
980 x_attribute11 IN VARCHAR2,
981 x_attribute12 IN VARCHAR2,
982 x_attribute13 IN VARCHAR2,
983 x_attribute14 IN VARCHAR2,
984 x_attribute15 IN VARCHAR2,
985 x_attribute16 IN VARCHAR2,
986 x_attribute17 IN VARCHAR2,
987 x_attribute18 IN VARCHAR2,
988 x_attribute19 IN VARCHAR2,
989 x_attribute20 IN VARCHAR2,
990 x_plan_sht_status IN VARCHAR2
991 ) AS
992 /*
993 || Created By : ckasu
994 || Created On : 18-NOV-2003
995 || Purpose : Handles the LOCK mechanism for the table.
996 || Known limitations, enhancements or remarks :
997 || Change History :
998 || Who When What
999 || (reverse chronological order - newest change first)
1000 */
1001 CURSOR c1 IS
1002 SELECT
1003 person_id,
1004 program_cd,
1005 program_version,
1006 acad_cal_type,
1007 term_cal_type,
1008 term_sequence_number,
1009 key_program_flag,
1010 location_cd,
1011 attendance_mode,
1012 attendance_type,
1013 fee_cat,
1014 coo_id,
1015 class_standing_id,
1016 attribute_category,
1017 attribute1,
1018 attribute2,
1019 attribute3,
1020 attribute4,
1021 attribute5,
1022 attribute6,
1023 attribute7,
1024 attribute8,
1025 attribute9,
1026 attribute10,
1027 attribute11,
1028 attribute12,
1029 attribute13,
1030 attribute14,
1031 attribute15,
1032 attribute16,
1033 attribute17,
1034 attribute18,
1035 attribute19,
1036 attribute20,
1037 plan_sht_status
1038 FROM igs_en_spa_terms
1039 WHERE rowid = x_rowid
1040 FOR UPDATE NOWAIT;
1041
1042 tlinfo c1%ROWTYPE;
1043
1044 BEGIN
1045
1046 OPEN c1;
1047 FETCH c1 INTO tlinfo;
1048 IF (c1%notfound) THEN
1049 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1050 igs_ge_msg_stack.add;
1051 CLOSE c1;
1052 app_exception.raise_exception;
1053 RETURN;
1054 END IF;
1055 CLOSE c1;
1056
1057 IF (
1058 (tlinfo.person_id = x_person_id)
1059 AND (tlinfo.program_cd = x_program_cd)
1060 AND (tlinfo.program_version = x_program_version)
1061 AND (tlinfo.acad_cal_type = x_acad_cal_type)
1062 AND (tlinfo.term_cal_type = x_term_cal_type)
1063 AND (tlinfo.term_sequence_number = x_term_sequence_number)
1064 AND (tlinfo.key_program_flag = x_key_program_flag)
1065 AND (tlinfo.location_cd = x_location_cd)
1066 AND (tlinfo.attendance_mode = x_attendance_mode)
1067 AND (tlinfo.attendance_type = x_attendance_type)
1068 AND ((tlinfo.fee_cat = x_fee_cat) OR ((tlinfo.fee_cat IS NULL) AND (X_fee_cat IS NULL)))
1069 AND (tlinfo.coo_id = x_coo_id)
1070 AND ((tlinfo.class_standing_id = x_class_standing_id) OR ((tlinfo.class_standing_id IS NULL) AND (X_class_standing_id IS NULL)))
1071 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1072 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1073 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1074 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1075 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1076 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1077 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1078 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1079 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1080 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1081 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1082 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1083 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1084 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1085 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1086 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1087 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1088 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1089 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1090 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1091 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1092 AND (tlinfo.plan_sht_status = x_plan_sht_status)
1093 ) THEN
1094 NULL;
1095 ELSE
1096 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1097 igs_ge_msg_stack.add;
1098 app_exception.raise_exception;
1099 END IF;
1100
1101 RETURN;
1102
1103 END lock_row;
1104
1105
1106 PROCEDURE update_row (
1107 x_rowid IN VARCHAR2,
1108 x_term_record_id IN NUMBER,
1109 x_person_id IN NUMBER,
1110 x_program_cd IN VARCHAR2,
1111 x_program_version IN NUMBER,
1112 x_acad_cal_type IN VARCHAR2,
1113 x_term_cal_type IN VARCHAR2,
1114 x_term_sequence_number IN NUMBER,
1115 x_key_program_flag IN VARCHAR2,
1116 x_location_cd IN VARCHAR2,
1117 x_attendance_mode IN VARCHAR2,
1118 x_attendance_type IN VARCHAR2,
1119 x_fee_cat IN VARCHAR2,
1120 x_coo_id IN NUMBER,
1121 x_class_standing_id IN NUMBER,
1122 x_attribute_category IN VARCHAR2,
1123 x_attribute1 IN VARCHAR2,
1124 x_attribute2 IN VARCHAR2,
1125 x_attribute3 IN VARCHAR2,
1126 x_attribute4 IN VARCHAR2,
1127 x_attribute5 IN VARCHAR2,
1128 x_attribute6 IN VARCHAR2,
1129 x_attribute7 IN VARCHAR2,
1130 x_attribute8 IN VARCHAR2,
1131 x_attribute9 IN VARCHAR2,
1132 x_attribute10 IN VARCHAR2,
1133 x_attribute11 IN VARCHAR2,
1134 x_attribute12 IN VARCHAR2,
1135 x_attribute13 IN VARCHAR2,
1136 x_attribute14 IN VARCHAR2,
1137 x_attribute15 IN VARCHAR2,
1138 x_attribute16 IN VARCHAR2,
1139 x_attribute17 IN VARCHAR2,
1140 x_attribute18 IN VARCHAR2,
1141 x_attribute19 IN VARCHAR2,
1142 x_attribute20 IN VARCHAR2,
1143 x_plan_sht_status IN VARCHAR2,
1144 x_mode IN VARCHAR2
1145 ) AS
1146 /*
1147 || Created By : ckasu
1148 || Created On : 18-NOV-2003
1149 || Purpose : Handles the UPDATE DML logic for the table.
1150 || Known limitations, enhancements or remarks :
1151 || Change History :
1152 || Who When What
1153 || (reverse chronological order - newest change first)
1154 */
1155 x_last_update_date DATE ;
1156 x_last_updated_by NUMBER;
1157 x_last_update_login NUMBER;
1158 x_request_id NUMBER;
1159 x_program_id NUMBER;
1160 x_program_application_id NUMBER;
1161 x_program_update_date DATE;
1162
1163 BEGIN
1164
1165 x_last_update_date := sysdate;
1166 IF (X_MODE = 'I') THEN
1167 x_last_updated_by := 1;
1168 x_last_update_login := 0;
1169 ELSIF (X_MODE IN ('R', 'S')) THEN
1170 x_last_updated_by := fnd_global.user_id;
1171 IF x_last_updated_by IS NULL THEN
1172 x_last_updated_by := -1;
1173 END IF;
1174 x_last_update_login := fnd_global.login_id;
1175 IF (x_last_update_login IS NULL) THEN
1176 x_last_update_login := -1;
1177 END IF;
1178 ELSE
1179 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1180 fnd_message.set_token ('ROUTINE', 'IGS_EN_SPA_TERMS_PKG.UPDATE_ROW');
1181 igs_ge_msg_stack.add;
1182 app_exception.raise_exception;
1183 END IF;
1184
1185 before_dml(
1186 p_action => 'UPDATE',
1187 x_rowid => x_rowid,
1188 x_term_record_id => x_term_record_id,
1189 x_person_id => x_person_id,
1190 x_program_cd => x_program_cd,
1191 x_program_version => x_program_version,
1192 x_acad_cal_type => x_acad_cal_type,
1193 x_term_cal_type => x_term_cal_type,
1194 x_term_sequence_number => x_term_sequence_number,
1195 x_key_program_flag => x_key_program_flag,
1196 x_location_cd => x_location_cd,
1197 x_attendance_mode => x_attendance_mode,
1198 x_attendance_type => x_attendance_type,
1199 x_fee_cat => x_fee_cat,
1200 x_coo_id => x_coo_id,
1201 x_class_standing_id => x_class_standing_id,
1202 x_attribute_category => x_attribute_category,
1203 x_attribute1 => x_attribute1,
1204 x_attribute2 => x_attribute2,
1205 x_attribute3 => x_attribute3,
1206 x_attribute4 => x_attribute4,
1207 x_attribute5 => x_attribute5,
1208 x_attribute6 => x_attribute6,
1209 x_attribute7 => x_attribute7,
1210 x_attribute8 => x_attribute8,
1211 x_attribute9 => x_attribute9,
1212 x_attribute10 => x_attribute10,
1213 x_attribute11 => x_attribute11,
1214 x_attribute12 => x_attribute12,
1215 x_attribute13 => x_attribute13,
1216 x_attribute14 => x_attribute14,
1217 x_attribute15 => x_attribute15,
1218 x_attribute16 => x_attribute16,
1219 x_attribute17 => x_attribute17,
1220 x_attribute18 => x_attribute18,
1221 x_attribute19 => x_attribute19,
1222 x_attribute20 => x_attribute20,
1223 x_creation_date => x_last_update_date,
1224 x_created_by => x_last_updated_by,
1225 x_last_update_date => x_last_update_date,
1226 x_last_updated_by => x_last_updated_by,
1227 x_last_update_login => x_last_update_login,
1228 x_plan_sht_status => x_plan_sht_status
1229 );
1230
1231 IF (X_MODE IN ('R', 'S')) THEN
1232 x_request_id := fnd_global.conc_request_id;
1233 x_program_id := fnd_global.conc_program_id;
1234 x_program_application_id := fnd_global.prog_appl_id;
1235 IF (x_request_id = -1) THEN
1236 x_request_id := old_references.request_id;
1237 x_program_id := old_references.program_id;
1238 x_program_application_id := old_references.program_application_id;
1239 x_program_update_date := old_references.program_update_date;
1240 ELSE
1241 x_program_update_date := sysdate;
1242 END IF;
1243 END IF;
1244
1245 IF (x_mode = 'S') THEN
1246 igs_sc_gen_001.set_ctx('R');
1247 END IF;
1248 UPDATE igs_en_spa_terms
1249 SET
1250 person_id = new_references.person_id,
1251 program_cd = new_references.program_cd,
1252 program_version = new_references.program_version,
1253 acad_cal_type = new_references.acad_cal_type,
1254 term_cal_type = new_references.term_cal_type,
1255 term_sequence_number = new_references.term_sequence_number,
1256 key_program_flag = new_references.key_program_flag,
1257 location_cd = new_references.location_cd,
1258 attendance_mode = new_references.attendance_mode,
1259 attendance_type = new_references.attendance_type,
1260 fee_cat = new_references.fee_cat,
1261 coo_id = new_references.coo_id,
1262 class_standing_id = new_references.class_standing_id,
1263 attribute_category = new_references.attribute_category,
1264 attribute1 = new_references.attribute1,
1265 attribute2 = new_references.attribute2,
1266 attribute3 = new_references.attribute3,
1267 attribute4 = new_references.attribute4,
1268 attribute5 = new_references.attribute5,
1269 attribute6 = new_references.attribute6,
1270 attribute7 = new_references.attribute7,
1271 attribute8 = new_references.attribute8,
1272 attribute9 = new_references.attribute9,
1273 attribute10 = new_references.attribute10,
1274 attribute11 = new_references.attribute11,
1275 attribute12 = new_references.attribute12,
1276 attribute13 = new_references.attribute13,
1277 attribute14 = new_references.attribute14,
1278 attribute15 = new_references.attribute15,
1279 attribute16 = new_references.attribute16,
1280 attribute17 = new_references.attribute17,
1281 attribute18 = new_references.attribute18,
1282 attribute19 = new_references.attribute19,
1283 attribute20 = new_references.attribute20,
1284 last_update_date = x_last_update_date,
1285 last_updated_by = x_last_updated_by,
1286 last_update_login = x_last_update_login ,
1287 request_id = x_request_id,
1288 program_id = x_program_id,
1289 program_application_id = x_program_application_id,
1290 program_update_date = x_program_update_date,
1291 plan_sht_status = new_references.plan_sht_status
1292 WHERE rowid = x_rowid;
1293
1294 IF (SQL%NOTFOUND) THEN
1295 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1296 igs_ge_msg_stack.add;
1297 igs_sc_gen_001.unset_ctx('R');
1298 app_exception.raise_exception;
1299 END IF;
1300 IF (x_mode = 'S') THEN
1301 igs_sc_gen_001.unset_ctx('R');
1302 END IF;
1303
1304
1305
1306 EXCEPTION
1307 WHEN OTHERS THEN
1308 IF (SQLCODE = (-28115)) THEN
1309 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1310 fnd_message.set_token ('ERR_CD', SQLCODE);
1311 igs_ge_msg_stack.add;
1312 igs_sc_gen_001.unset_ctx('R');
1313 app_exception.raise_exception;
1314 ELSE
1315 igs_sc_gen_001.unset_ctx('R');
1316 RAISE;
1317 END IF;
1318 END update_row;
1319
1320
1321 PROCEDURE add_row (
1322 x_rowid IN OUT NOCOPY VARCHAR2,
1323 x_term_record_id IN OUT NOCOPY NUMBER,
1324 x_person_id IN NUMBER,
1325 x_program_cd IN VARCHAR2,
1326 x_program_version IN NUMBER,
1327 x_acad_cal_type IN VARCHAR2,
1328 x_term_cal_type IN VARCHAR2,
1329 x_term_sequence_number IN NUMBER,
1330 x_key_program_flag IN VARCHAR2,
1331 x_location_cd IN VARCHAR2,
1332 x_attendance_mode IN VARCHAR2,
1333 x_attendance_type IN VARCHAR2,
1334 x_fee_cat IN VARCHAR2,
1335 x_coo_id IN NUMBER,
1336 x_class_standing_id IN NUMBER,
1337 x_attribute_category IN VARCHAR2,
1338 x_attribute1 IN VARCHAR2,
1339 x_attribute2 IN VARCHAR2,
1340 x_attribute3 IN VARCHAR2,
1341 x_attribute4 IN VARCHAR2,
1342 x_attribute5 IN VARCHAR2,
1343 x_attribute6 IN VARCHAR2,
1344 x_attribute7 IN VARCHAR2,
1345 x_attribute8 IN VARCHAR2,
1346 x_attribute9 IN VARCHAR2,
1347 x_attribute10 IN VARCHAR2,
1348 x_attribute11 IN VARCHAR2,
1349 x_attribute12 IN VARCHAR2,
1350 x_attribute13 IN VARCHAR2,
1351 x_attribute14 IN VARCHAR2,
1352 x_attribute15 IN VARCHAR2,
1353 x_attribute16 IN VARCHAR2,
1354 x_attribute17 IN VARCHAR2,
1355 x_attribute18 IN VARCHAR2,
1356 x_attribute19 IN VARCHAR2,
1357 x_attribute20 IN VARCHAR2,
1358 x_plan_sht_status IN VARCHAR2,
1359 x_mode IN VARCHAR2
1360 ) AS
1361 /*
1362 || Created By : ckasu
1363 || Created On : 18-NOV-2003
1364 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1365 || Known limitations, enhancements or remarks :
1366 || Change History :
1367 || Who When What
1368 || (reverse chronological order - newest change first)
1369 */
1370 CURSOR c1 IS
1371 SELECT rowid
1372 FROM igs_en_spa_terms
1373 WHERE term_record_id = x_term_record_id;
1374
1375 BEGIN
1376
1377 OPEN c1;
1378 FETCH c1 INTO x_rowid;
1379 IF (c1%NOTFOUND) THEN
1380 CLOSE c1;
1381
1382 insert_row (
1383 x_rowid,
1384 x_term_record_id,
1385 x_person_id,
1386 x_program_cd,
1387 x_program_version,
1388 x_acad_cal_type,
1389 x_term_cal_type,
1390 x_term_sequence_number,
1391 x_key_program_flag,
1392 x_location_cd,
1393 x_attendance_mode,
1394 x_attendance_type,
1395 x_fee_cat,
1396 x_coo_id,
1397 x_class_standing_id,
1398 x_attribute_category,
1399 x_attribute1,
1400 x_attribute2,
1401 x_attribute3,
1402 x_attribute4,
1403 x_attribute5,
1404 x_attribute6,
1405 x_attribute7,
1406 x_attribute8,
1407 x_attribute9,
1408 x_attribute10,
1409 x_attribute11,
1410 x_attribute12,
1411 x_attribute13,
1412 x_attribute14,
1413 x_attribute15,
1414 x_attribute16,
1415 x_attribute17,
1416 x_attribute18,
1417 x_attribute19,
1418 x_attribute20,
1419 x_plan_sht_status,
1420 x_mode
1421 );
1422 RETURN;
1423 END IF;
1424 CLOSE c1;
1425
1426 update_row (
1427 x_rowid,
1428 x_term_record_id,
1429 x_person_id,
1430 x_program_cd,
1431 x_program_version,
1432 x_acad_cal_type,
1433 x_term_cal_type,
1434 x_term_sequence_number,
1435 x_key_program_flag,
1436 x_location_cd,
1437 x_attendance_mode,
1438 x_attendance_type,
1439 x_fee_cat,
1440 x_coo_id,
1441 x_class_standing_id,
1442 x_attribute_category,
1443 x_attribute1,
1444 x_attribute2,
1445 x_attribute3,
1446 x_attribute4,
1447 x_attribute5,
1448 x_attribute6,
1449 x_attribute7,
1450 x_attribute8,
1451 x_attribute9,
1452 x_attribute10,
1453 x_attribute11,
1454 x_attribute12,
1455 x_attribute13,
1456 x_attribute14,
1457 x_attribute15,
1458 x_attribute16,
1459 x_attribute17,
1460 x_attribute18,
1461 x_attribute19,
1462 x_attribute20,
1463 x_plan_sht_status,
1464 x_mode
1465 );
1466
1467 END add_row;
1468
1469
1470 PROCEDURE delete_row (
1471 x_rowid IN VARCHAR2,
1472 x_mode IN VARCHAR2
1473 ) AS
1474 /*
1475 || Created By : ckasu
1476 || Created On : 18-NOV-2003
1477 || Purpose : Handles the DELETE DML logic for the table.
1478 || Known limitations, enhancements or remarks :
1479 || Change History :
1480 || Who When What
1481 || (reverse chronological order - newest change first)
1482 */
1483 BEGIN
1484
1485 before_dml (
1486 p_action => 'DELETE',
1487 x_rowid => x_rowid
1488 );
1489
1490 IF (x_mode = 'S') THEN
1491 igs_sc_gen_001.set_ctx('R');
1492 END IF;
1493 DELETE FROM igs_en_spa_terms
1494 WHERE rowid = x_rowid;
1495
1496 IF (SQL%NOTFOUND) THEN
1497 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1498 igs_ge_msg_stack.add;
1499 igs_sc_gen_001.unset_ctx('R');
1500 app_exception.raise_exception;
1501 END IF;
1502 IF (x_mode = 'S') THEN
1503 igs_sc_gen_001.unset_ctx('R');
1504 END IF;
1505
1506
1507 END delete_row;
1508
1509 PROCEDURE check_child_existence (
1510 x_person_id IN NUMBER,
1511 x_course_cd IN VARCHAR2
1512 ) AS
1513 BEGIN
1514 igs_en_plan_units_pkg.get_fk_igs_en_stdnt_ps_att(x_person_id,x_course_cd);
1515 END check_child_existence;
1516
1517 END igs_en_spa_terms_pkg;