1 PACKAGE BODY igs_en_cpd_ext_pkg AS
2 /* $Header: IGSEI50B.pls 115.5 2003/06/11 06:37:05 rnirwani ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_cpd_ext_all%ROWTYPE;
6 new_references igs_en_cpd_ext_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_igs_en_cpd_ext_id IN NUMBER ,
12 x_enrolment_cat IN VARCHAR2 ,
13 x_enr_method_type IN VARCHAR2 ,
14 x_s_student_comm_type IN VARCHAR2 ,
15 x_step_order_num IN NUMBER ,
16 x_s_enrolment_step_type IN VARCHAR2 ,
17 x_notification_flag IN VARCHAR2 ,
18 x_s_rule_call_cd IN VARCHAR2 ,
19 x_rul_sequence_number IN NUMBER ,
20 x_creation_date IN DATE ,
21 x_created_by IN NUMBER ,
22 x_last_update_date IN DATE ,
23 x_last_updated_by IN NUMBER ,
24 x_last_update_login IN NUMBER ,
25 x_STUD_AUDIT_LIM IN NUMBER
26 ) AS
27 /*
28 || Created By : [email protected]
29 || Created On : 22-JUN-2001
30 || Purpose : Initialises the Old and New references for the columns of the table.
31 || Known limitations, enhancements or remarks :
32 || Change History :
33 || Who When What
34 || (reverse chronological order - newest change first)
35 */
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM IGS_EN_CPD_EXT_ALL
40 WHERE rowid = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 OPEN cur_old_ref_values;
49 FETCH cur_old_ref_values INTO old_references;
50 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
51 CLOSE cur_old_ref_values;
52 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
53 igs_ge_msg_stack.add;
54 app_exception.raise_exception;
55 RETURN;
56 END IF;
57 CLOSE cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.igs_en_cpd_ext_id := x_igs_en_cpd_ext_id;
61 new_references.enrolment_cat := x_enrolment_cat;
62 new_references.enr_method_type := x_enr_method_type;
63 new_references.s_student_comm_type := x_s_student_comm_type;
64 new_references.step_order_num := x_step_order_num;
65 new_references.s_enrolment_step_type := x_s_enrolment_step_type;
66 new_references.notification_flag := x_notification_flag;
67 new_references.s_rule_call_cd := x_s_rule_call_cd;
68 new_references.rul_sequence_number := x_rul_sequence_number;
69 new_references.stud_audit_lim := x_stud_audit_lim;
70
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78
79 new_references.last_update_date := x_last_update_date;
80 new_references.last_updated_by := x_last_updated_by;
81 new_references.last_update_login := x_last_update_login;
82
83 END set_column_values;
84
85
86 PROCEDURE check_uniqueness AS
87 /*
88 || Created By : [email protected]
89 || Created On : 22-JUN-2001
90 || Purpose : Handles the Unique Constraint logic defined for the columns.
91 || Known limitations, enhancements or remarks :
92 || Change History :
93 || Who When What
94 || (reverse chronological order - newest change first)
95 */
96 BEGIN
97
98 IF ( get_uk_for_validation (
99 new_references.enrolment_cat,
100 new_references.enr_method_type,
101 new_references.org_id,
102 new_references.s_enrolment_step_type,
103 new_references.s_student_comm_type
104 )
105 ) THEN
106 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
107 igs_ge_msg_stack.add;
108 app_exception.raise_exception;
109 END IF;
110
111 END check_uniqueness;
112
113
114 PROCEDURE check_parent_existance AS
115 /*
116 || Created By : [email protected]
117 || Created On : 22-JUN-2001
118 || Purpose : Checks for the existance of Parent records.
119 || Known limitations, enhancements or remarks :
120 || Change History :
121 || Who When What
122 || (reverse chronological order - newest change first)
123 */
124 BEGIN
125
126 IF (((old_references.enrolment_cat = new_references.enrolment_cat)) OR
127 ((new_references.enrolment_cat IS NULL))) THEN
128 NULL;
129 ELSIF NOT igs_en_enrolment_cat_pkg.get_pk_for_validation (
130 new_references.enrolment_cat
131 ) THEN
132 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
133 igs_ge_msg_stack.add;
134 app_exception.raise_exception;
135 END IF;
136
137 IF (((old_references.enr_method_type = new_references.enr_method_type)) OR
138 ((new_references.enr_method_type IS NULL))) THEN
139 NULL;
140 ELSIF NOT igs_en_method_type_pkg.get_pk_for_validation (
141 new_references.enr_method_type
142 ) THEN
143 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
144 igs_ge_msg_stack.add;
145 app_exception.raise_exception;
146 END IF;
147
148 IF (((old_references.s_rule_call_cd = new_references.s_rule_call_cd)) OR
149 ((new_references.s_rule_call_cd IS NULL))) THEN
150 NULL;
151 ELSIF NOT igs_ru_call_pkg.get_pk_for_validation (
152 new_references.s_rule_call_cd
153 ) THEN
154 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
155 igs_ge_msg_stack.add;
156 app_exception.raise_exception;
157 END IF;
158
159 IF (((old_references.rul_sequence_number = new_references.rul_sequence_number)) OR
160 ((new_references.rul_sequence_number IS NULL))) THEN
161 NULL;
162 ELSIF NOT igs_ru_rule_pkg.get_pk_for_validation (
163 new_references.rul_sequence_number
164 ) THEN
165 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
166 igs_ge_msg_stack.add;
167 app_exception.raise_exception;
168 END IF;
169
170 --*****
171 IF (((old_references.s_student_comm_type =
172 new_references.s_student_comm_type)) OR
173 ((new_references.s_student_comm_type IS NULL))) THEN
174 NULL;
175 ELSE
176 IF NOT IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation ('VS_EN_COMMENCE',
177 new_references.s_student_comm_type) THEN
178 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
179 Igs_Ge_Msg_Stack.Add;
180 App_Exception.Raise_Exception;
181 END IF;
182 END IF;
183
184
185 IF (((old_references.s_enrolment_step_type =
186 new_references.s_enrolment_step_type)) OR
187 ((new_references.s_enrolment_step_type IS NULL))) THEN
188 NULL;
189 ELSE
190 IF NOT IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation ('ENROLMENT_STEP_TYPE_EXT',
191 new_references.s_enrolment_step_type) THEN
192 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
193 Igs_Ge_Msg_Stack.Add;
194 App_Exception.Raise_Exception;
195 END IF;
196 END IF;
197 --*****
198
199 END check_parent_existance;
200
201
202 FUNCTION get_pk_for_validation (
203 x_igs_en_cpd_ext_id IN NUMBER
204 ) RETURN BOOLEAN AS
205 /*
206 || Created By : [email protected]
207 || Created On : 22-JUN-2001
208 || Purpose : Validates the Primary Key of the table.
209 || Known limitations, enhancements or remarks :
210 || Change History :
211 || Who When What
212 || (reverse chronological order - newest change first)
213 */
214 CURSOR cur_rowid IS
215 SELECT rowid
216 FROM igs_en_cpd_ext_all
217 WHERE igs_en_cpd_ext_id = x_igs_en_cpd_ext_id
218 FOR UPDATE NOWAIT;
219
220 lv_rowid cur_rowid%RowType;
221
222 BEGIN
223
224 OPEN cur_rowid;
225 FETCH cur_rowid INTO lv_rowid;
226 IF (cur_rowid%FOUND) THEN
227 CLOSE cur_rowid;
228 RETURN(TRUE);
229 ELSE
230 CLOSE cur_rowid;
231 RETURN(FALSE);
232 END IF;
233
234 END get_pk_for_validation;
235
236
237 FUNCTION get_uk_for_validation (
238 x_enrolment_cat IN VARCHAR2,
239 x_enr_method_type IN VARCHAR2,
240 x_org_id IN NUMBER,
241 x_s_enrolment_step_type IN VARCHAR2,
242 x_s_student_comm_type IN VARCHAR2
243 ) RETURN BOOLEAN AS
244 /*
245 || Created By : [email protected]
246 || Created On : 22-JUN-2001
247 || Purpose : Validates the Unique Keys of the table.
248 || Known limitations, enhancements or remarks :
249 || Change History :
250 || Who When What
251 || (reverse chronological order - newest change first)
252 */
253 CURSOR cur_rowid IS
254 SELECT rowid
255 FROM igs_en_cpd_ext
256 WHERE enrolment_cat = x_enrolment_cat
257 AND enr_method_type = x_enr_method_type
258 AND s_enrolment_step_type = x_s_enrolment_step_type
259 AND s_student_comm_type = x_s_student_comm_type
260 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
261
262 lv_rowid cur_rowid%RowType;
263
264 BEGIN
265
266 OPEN cur_rowid;
267 FETCH cur_rowid INTO lv_rowid;
268 IF (cur_rowid%FOUND) THEN
269 CLOSE cur_rowid;
270 RETURN (true);
271 ELSE
272 CLOSE cur_rowid;
273 RETURN(FALSE);
274 END IF;
275
276 END get_uk_for_validation ;
277
278
279 PROCEDURE get_fk_igs_en_enrolment_cat (
280 x_enrolment_cat IN VARCHAR2
281 ) AS
282 /*
283 || Created By : [email protected]
284 || Created On : 22-JUN-2001
285 || Purpose : Validates the Foreign Keys for the table.
286 || Known limitations, enhancements or remarks :
287 || Change History :
288 || Who When What
289 || (reverse chronological order - newest change first)
290 */
291 CURSOR cur_rowid IS
292 SELECT rowid
293 FROM igs_en_cpd_ext_all
294 WHERE ((enrolment_cat = x_enrolment_cat));
295
296 lv_rowid cur_rowid%RowType;
297
298 BEGIN
299
300 OPEN cur_rowid;
301 FETCH cur_rowid INTO lv_rowid;
302 IF (cur_rowid%FOUND) THEN
303 CLOSE cur_rowid;
304 fnd_message.set_name ('IGS', 'IGS_EN_CPDE_EC_FK');
305 igs_ge_msg_stack.add;
306 app_exception.raise_exception;
307 RETURN;
308 END IF;
309 CLOSE cur_rowid;
310
311 END get_fk_igs_en_enrolment_cat;
312
313
314 PROCEDURE get_fk_igs_en_method_type (
315 x_enr_method_type IN VARCHAR2
316 ) AS
317 /*
318 || Created By : [email protected]
319 || Created On : 22-JUN-2001
320 || Purpose : Validates the Foreign Keys for the table.
321 || Known limitations, enhancements or remarks :
322 || Change History :
323 || Who When What
324 || (reverse chronological order - newest change first)
325 */
326 CURSOR cur_rowid IS
327 SELECT rowid
328 FROM igs_en_cpd_ext_all
329 WHERE ((enr_method_type = x_enr_method_type));
330
331 lv_rowid cur_rowid%RowType;
332
333 BEGIN
334
335 OPEN cur_rowid;
336 FETCH cur_rowid INTO lv_rowid;
337 IF (cur_rowid%FOUND) THEN
338 CLOSE cur_rowid;
339 fnd_message.set_name ('IGS', 'IGS_EN_CPDE_EMT_FK');
340 igs_ge_msg_stack.add;
341 app_exception.raise_exception;
342 RETURN;
343 END IF;
344 CLOSE cur_rowid;
345
346 END get_fk_igs_en_method_type;
347
348
349 PROCEDURE get_fk_igs_ru_call (
350 x_s_rule_call_cd IN VARCHAR2
351 ) AS
352 /*
353 || Created By : [email protected]
354 || Created On : 22-JUN-2001
355 || Purpose : Validates the Foreign Keys for the table.
356 || Known limitations, enhancements or remarks :
357 || Change History :
358 || Who When What
359 || (reverse chronological order - newest change first)
360 */
361 CURSOR cur_rowid IS
362 SELECT rowid
363 FROM igs_en_cpd_ext_all
364 WHERE ((s_rule_call_cd = x_s_rule_call_cd));
365
366 lv_rowid cur_rowid%RowType;
367
368 BEGIN
369
370 OPEN cur_rowid;
371 FETCH cur_rowid INTO lv_rowid;
372 IF (cur_rowid%FOUND) THEN
373 CLOSE cur_rowid;
374 fnd_message.set_name ('IGS', 'IGS_EN_CPDE_SRC_FK');
375 igs_ge_msg_stack.add;
376 app_exception.raise_exception;
377 RETURN;
378 END IF;
379 CLOSE cur_rowid;
380
381 END get_fk_igs_ru_call;
382
383
384 PROCEDURE get_fk_igs_ru_rule (
385 x_sequence_number IN NUMBER
386 ) AS
387 /*
388 || Created By : [email protected]
389 || Created On : 22-JUN-2001
390 || Purpose : Validates the Foreign Keys for the table.
391 || Known limitations, enhancements or remarks :
392 || Change History :
393 || Who When What
394 || (reverse chronological order - newest change first)
395 */
396 CURSOR cur_rowid IS
397 SELECT rowid
398 FROM igs_en_cpd_ext_all
399 WHERE ((rul_sequence_number = x_sequence_number));
400
401 lv_rowid cur_rowid%RowType;
402
403 BEGIN
404
405 OPEN cur_rowid;
406 FETCH cur_rowid INTO lv_rowid;
407 IF (cur_rowid%FOUND) THEN
408 CLOSE cur_rowid;
409 fnd_message.set_name ('IGS', 'IGS_EN_CPDE_RUL_FK');
410 igs_ge_msg_stack.add;
411 app_exception.raise_exception;
412 RETURN;
413 END IF;
414 CLOSE cur_rowid;
415
416 END get_fk_igs_ru_rule;
417
418 --*****
419
420
421 PROCEDURE get_fk_igs_lookups_view_1 (
422 x_s_student_comm_type IN VARCHAR2
423 ) AS
424 /*
425 || Created By : [email protected]
426 || Created On : 22-JUN-2001
427 || Purpose : Validates the Foreign Keys for the table.
428 || Known limitations, enhancements or remarks :
429 || Change History :
430 || Who When What
431 || (reverse chronological order - newest change first)
432 */
433 CURSOR cur_rowid IS
434 SELECT rowid
435 FROM IGS_EN_CPD_EXT_ALL
436 WHERE s_student_comm_type = x_s_student_comm_type;
437 lv_rowid cur_rowid%RowType;
438
439 BEGIN
440 Open cur_rowid;
441 Fetch cur_rowid INTO lv_rowid;
442 IF (cur_rowid%FOUND) THEN
443 Fnd_Message.Set_Name ('IGS', 'IGS_EN_CPDE_LVAL_FK');
444 Igs_Ge_Msg_Stack.Add;
445 Close cur_rowid;
446 App_Exception.Raise_Exception;
447 Return;
448 END IF;
449 Close cur_rowid;
450 END get_fk_igs_lookups_view_1;
451
452
453 PROCEDURE get_fk_igs_lookups_view_2 (
454 x_s_enrolment_step_type IN VARCHAR2
455 ) AS
456 /*
457 || Created By : [email protected]
458 || Created On : 22-JUN-2001
459 || Purpose : Validates the Foreign Keys for the table.
460 || Known limitations, enhancements or remarks :
461 || Change History :
462 || Who When What
463 || (reverse chronological order - newest change first)
464 */
465 CURSOR cur_rowid IS
466 SELECT rowid
467 FROM IGS_EN_CPD_EXT_ALL
468 WHERE s_enrolment_step_type = x_s_enrolment_step_type;
469 lv_rowid cur_rowid%RowType;
470
471 BEGIN
472 Open cur_rowid;
473 Fetch cur_rowid INTO lv_rowid;
474 IF (cur_rowid%FOUND) THEN
475 Fnd_Message.Set_Name ('IGS', 'IGS_EN_CPDE_LVAL_FK');
476 Igs_Ge_Msg_Stack.Add;
477 Close cur_rowid;
478 App_Exception.Raise_Exception;
479 Return;
480 END IF;
481 Close cur_rowid;
482
483 END get_fk_igs_lookups_view_2;
484
485 --*****
486
487 PROCEDURE before_dml (
488 p_action IN VARCHAR2,
489 x_rowid IN VARCHAR2 ,
490 x_igs_en_cpd_ext_id IN NUMBER ,
491 x_enrolment_cat IN VARCHAR2 ,
492 x_enr_method_type IN VARCHAR2 ,
493 x_s_student_comm_type IN VARCHAR2 ,
494 x_step_order_num IN NUMBER ,
495 x_s_enrolment_step_type IN VARCHAR2 ,
496 x_notification_flag IN VARCHAR2 ,
497 x_s_rule_call_cd IN VARCHAR2 ,
498 x_rul_sequence_number IN NUMBER ,
499 x_creation_date IN DATE ,
500 x_created_by IN NUMBER ,
501 x_last_update_date IN DATE ,
502 x_last_updated_by IN NUMBER ,
503 x_last_update_login IN NUMBER ,
504 x_STUD_AUDIT_LIM IN NUMBER
505 ) AS
506 /*
507 || Created By : [email protected]
508 || Created On : 22-JUN-2001
509 || Purpose : Initialises the columns, Checks Constraints, Calls the
510 || Trigger Handlers for the table, before any DML operation.
511 || Known limitations, enhancements or remarks :
512 || Change History :
513 || Who When What
514 || (reverse chronological order - newest change first)
515 */
516 BEGIN
517
518 set_column_values (
519 p_action,
520 x_rowid,
521 x_igs_en_cpd_ext_id,
522 x_enrolment_cat,
523 x_enr_method_type,
524 x_s_student_comm_type,
525 x_step_order_num,
526 x_s_enrolment_step_type,
527 x_notification_flag,
528 x_s_rule_call_cd,
529 x_rul_sequence_number,
530 x_creation_date,
531 x_created_by,
532 x_last_update_date,
533 x_last_updated_by,
534 x_last_update_login,
535 x_stud_audit_lim
536 );
537
538 IF (p_action = 'INSERT') THEN
539 -- Call all the procedures related to Before Insert.
540 IF ( get_pk_for_validation(
541 new_references.igs_en_cpd_ext_id
542 )
543 ) THEN
544 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
545 igs_ge_msg_stack.add;
546 app_exception.raise_exception;
547 END IF;
548 check_uniqueness;
549 check_parent_existance;
550 ELSIF (p_action = 'UPDATE') THEN
551 -- Call all the procedures related to Before Update.
552 check_uniqueness;
553 check_parent_existance;
554 ELSIF (p_action = 'VALIDATE_INSERT') THEN
555 -- Call all the procedures related to Before Insert.
556 IF ( get_pk_for_validation (
557 new_references.igs_en_cpd_ext_id
558 )
559 ) THEN
560 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
561 igs_ge_msg_stack.add;
562 app_exception.raise_exception;
563 END IF;
564 check_uniqueness;
565 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
566 check_uniqueness;
567 END IF;
568
569 END before_dml;
570
571 PROCEDURE AfterStmtInsertUpdateDelete(
572 p_inserting IN BOOLEAN ,
573 p_updating IN BOOLEAN ,
574 p_deleting IN BOOLEAN
575 ) AS
576 CURSOR c_cpdext (cp_enr_cat igs_en_cpd_ext_all.enrolment_cat%TYPE,
577 cp_enr_mth igs_en_cpd_ext_all.enr_method_type%TYPE,
578 cp_comm_type igs_en_cpd_ext_all.s_student_comm_type%TYPE,
579 cp_step_type igs_en_cpd_ext_all.s_enrolment_Step_type%TYPE) IS
580 SELECT 'x'
581 FROM igs_en_cpd_ext
582 WHERE enrolment_cat = cp_enr_cat
583 AND enr_method_type = cp_enr_mth
584 AND s_student_comm_type = cp_comm_type
585 AND s_enrolment_Step_type = cp_step_type;
586
587 CURSOR c_catprc (cp_enr_cat igs_en_cpd_ext_all.enrolment_cat%TYPE,
588 cp_enr_mth igs_en_cpd_ext_all.enr_method_type%TYPE,
589 cp_comm_type igs_en_cpd_ext_all.s_student_comm_type%TYPE) IS
590 SELECT rowid row_id,
591 enrolment_cat,
592 s_student_comm_type,
593 enr_method_type,
594 person_add_allow_ind,
595 course_add_allow_ind
596 FROM igs_en_cat_prc_dtl
597 WHERE enrolment_cat = cp_enr_cat
598 AND s_student_comm_type = cp_comm_type
599 AND enr_method_type = cp_enr_mth;
600
601 l_step_type igs_en_cpd_ext_all.s_enrolment_Step_type%TYPE;
602 l_record_exist varchar2(1);
603 l_catprc c_catprc%ROWTYPE;
604
605 BEGIN
606 -- If trigger has not been disabled, perform required processing
607 IF p_deleting THEN
608 IF old_references.s_enrolment_step_type IN ('FMIN_CRDT','FATD_TYPE') THEN
609
610 IF old_references.s_enrolment_step_type = 'FMIN_CRDT' THEN
611 l_step_type := 'FATD_TYPE';
612 ELSE
613 l_step_type := 'FMIN_CRDT';
614 END IF;
615
616 OPEN c_cpdext (old_references.enrolment_cat, old_references.enr_method_type, old_references.s_student_comm_type, l_step_type);
617 FETCH c_cpdext INTO l_record_exist;
618 IF c_cpdext%NOTFOUND THEN
619
620 OPEN c_catprc (old_references.enrolment_cat, old_references.enr_method_type, old_references.s_student_comm_type);
621 FETCH c_catprc INTO l_catprc;
622 CLOSE c_catprc;
623
624 igs_en_cat_prc_dtl_pkg.update_row (
625 x_mode => 'R',
626 x_rowid => l_catprc.row_id,
627 x_enrolment_cat => l_catprc.enrolment_cat,
628 x_s_student_comm_type => l_catprc.s_student_comm_type,
629 x_enr_method_type => l_catprc.enr_method_type,
630 x_person_add_allow_ind => l_catprc.person_add_allow_ind,
631 x_course_add_allow_ind => l_catprc.course_add_allow_ind,
632 x_enforce_date_alias => NULL,
633 x_config_min_cp_valdn => NULL
634 );
635
636 END IF;
637 CLOSE c_cpdext;
638
639 END IF;
640
641 END IF;
642 END AfterStmtInsertUpdateDelete;
643
644 PROCEDURE After_DML (
645 p_action IN VARCHAR2,
646 x_rowid IN VARCHAR2
647 ) AS
648 BEGIN
649
650 l_rowid := x_rowid;
651
652 IF (p_action = 'INSERT') THEN
653 -- Call all the procedures related to After Insert.
654 Null;
655 ELSIF (p_action = 'UPDATE') THEN
656 -- Call all the procedures related to After Update.
657 Null;
658 ELSIF (p_action = 'DELETE') THEN
659 -- Call all the procedures related to After Delete.
660 AfterStmtInsertUpdateDelete(FALSE,FALSE,TRUE);
661 END IF;
662
663 END After_DML;
664
665
666 PROCEDURE insert_row (
667 x_rowid IN OUT NOCOPY VARCHAR2,
668 x_igs_en_cpd_ext_id IN OUT NOCOPY NUMBER,
669 x_enrolment_cat IN VARCHAR2,
670 x_enr_method_type IN VARCHAR2,
671 x_s_student_comm_type IN VARCHAR2,
672 x_step_order_num IN NUMBER,
673 x_s_enrolment_step_type IN VARCHAR2,
674 x_notification_flag IN VARCHAR2,
675 x_s_rule_call_cd IN VARCHAR2,
676 x_rul_sequence_number IN NUMBER,
677 x_mode IN VARCHAR2 ,
678 x_STUD_AUDIT_LIM IN NUMBER
679 ) AS
680 /*
681 || Created By : [email protected]
682 || Created On : 22-JUN-2001
683 || Purpose : Handles the INSERT DML logic for the table.
684 || Known limitations, enhancements or remarks :
685 || Change History :
686 || Who When What
687 || (reverse chronological order - newest change first)
688 */
689 CURSOR c IS
690 SELECT rowid
691 FROM igs_en_cpd_ext_all
692 WHERE igs_en_cpd_ext_id = x_igs_en_cpd_ext_id;
693
694 x_last_update_date DATE;
695 x_last_updated_by NUMBER;
696 x_last_update_login NUMBER;
697
698 BEGIN
699
700 x_last_update_date := SYSDATE;
701 IF (x_mode = 'I') THEN
702 x_last_updated_by := 1;
703 x_last_update_login := 0;
704 ELSIF (x_mode = 'R') THEN
705 x_last_updated_by := fnd_global.user_id;
706 IF (x_last_updated_by IS NULL) THEN
707 x_last_updated_by := -1;
708 END IF;
709 x_last_update_login := fnd_global.login_id;
710 IF (x_last_update_login IS NULL) THEN
711 x_last_update_login := -1;
712 END IF;
713 ELSE
714 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
715 igs_ge_msg_stack.add;
716 app_exception.raise_exception;
717 END IF;
718
719 SELECT igs_en_cpd_ext_s.NEXTVAL
720 INTO x_igs_en_cpd_ext_id
721 FROM dual;
722
723 new_references.org_id := igs_ge_gen_003.get_org_id;
724
725 before_dml(
726 p_action => 'INSERT',
727 x_rowid => x_rowid,
728 x_igs_en_cpd_ext_id => x_igs_en_cpd_ext_id,
729 x_enrolment_cat => x_enrolment_cat,
730 x_enr_method_type => x_enr_method_type,
731 x_s_student_comm_type => x_s_student_comm_type,
732 x_step_order_num => x_step_order_num,
733 x_s_enrolment_step_type => x_s_enrolment_step_type,
734 x_notification_flag => x_notification_flag,
735 x_s_rule_call_cd => x_s_rule_call_cd,
736 x_rul_sequence_number => x_rul_sequence_number,
737 x_creation_date => x_last_update_date,
738 x_created_by => x_last_updated_by,
739 x_last_update_date => x_last_update_date,
740 x_last_updated_by => x_last_updated_by,
741 x_last_update_login => x_last_update_login,
742 x_stud_audit_lim => x_stud_audit_lim
743 );
744
745 INSERT INTO igs_en_cpd_ext_all (
746 igs_en_cpd_ext_id,
747 org_id,
748 enrolment_cat,
749 enr_method_type,
750 s_student_comm_type,
751 step_order_num,
752 s_enrolment_step_type,
753 notification_flag,
754 s_rule_call_cd,
755 rul_sequence_number,
756 stud_audit_lim,
757 creation_date,
758 created_by,
759 last_update_date,
760 last_updated_by,
761 last_update_login
762 ) VALUES (
763 new_references.igs_en_cpd_ext_id,
764 new_references.org_id,
765 new_references.enrolment_cat,
766 new_references.enr_method_type,
767 new_references.s_student_comm_type,
768 new_references.step_order_num,
769 new_references.s_enrolment_step_type,
770 new_references.notification_flag,
771 new_references.s_rule_call_cd,
772 new_references.rul_sequence_number,
773 new_references.stud_audit_lim,
774 x_last_update_date,
775 x_last_updated_by,
776 x_last_update_date,
777 x_last_updated_by,
778 x_last_update_login
779 );
780
781 OPEN c;
782 FETCH c INTO x_rowid;
783 IF (c%NOTFOUND) THEN
784 CLOSE c;
785 RAISE NO_DATA_FOUND;
786 END IF;
787 CLOSE c;
788
789 After_DML(
790 p_action => 'INSERT',
791 x_rowid => X_ROWID
792 );
793
794 END insert_row;
795
796
797 PROCEDURE lock_row (
798 x_rowid IN VARCHAR2,
799 x_igs_en_cpd_ext_id IN NUMBER,
800 x_enrolment_cat IN VARCHAR2,
801 x_enr_method_type IN VARCHAR2,
802 x_s_student_comm_type IN VARCHAR2,
803 x_step_order_num IN NUMBER,
804 x_s_enrolment_step_type IN VARCHAR2,
805 x_notification_flag IN VARCHAR2,
806 x_s_rule_call_cd IN VARCHAR2,
807 x_rul_sequence_number IN NUMBER,
808 x_STUD_AUDIT_LIM IN NUMBER
809 ) AS
810 /*
811 || Created By : [email protected]
812 || Created On : 22-JUN-2001
813 || Purpose : Handles the LOCK mechanism for the table.
814 || Known limitations, enhancements or remarks :
815 || Change History :
816 || Who When What
817 || (reverse chronological order - newest change first)
818 */
819 CURSOR c1 IS
820 SELECT
821 enrolment_cat,
822 enr_method_type,
823 s_student_comm_type,
824 step_order_num,
825 s_enrolment_step_type,
826 notification_flag,
827 s_rule_call_cd,
828 rul_sequence_number,
829 stud_audit_lim
830 FROM igs_en_cpd_ext_all
831 WHERE rowid = x_rowid
832 FOR UPDATE NOWAIT;
833
834 tlinfo c1%ROWTYPE;
835
836 BEGIN
837
838 OPEN c1;
839 FETCH c1 INTO tlinfo;
840 IF (c1%notfound) THEN
841 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
842 igs_ge_msg_stack.add;
843 CLOSE c1;
844 app_exception.raise_exception;
845 RETURN;
846 END IF;
847 CLOSE c1;
848
849 IF (
850 (tlinfo.enrolment_cat = x_enrolment_cat)
851 AND (tlinfo.enr_method_type = x_enr_method_type)
852 AND (tlinfo.s_student_comm_type = x_s_student_comm_type)
853 AND (tlinfo.step_order_num = x_step_order_num)
854 AND (tlinfo.s_enrolment_step_type = x_s_enrolment_step_type)
855 AND (tlinfo.notification_flag = x_notification_flag)
856 AND ((tlinfo.s_rule_call_cd = x_s_rule_call_cd) OR ((tlinfo.s_rule_call_cd IS NULL) AND (X_s_rule_call_cd IS NULL)))
857 AND ((tlinfo.rul_sequence_number = x_rul_sequence_number) OR ((tlinfo.rul_sequence_number IS NULL) AND (X_rul_sequence_number IS NULL)))
858 AND ((tlinfo.stud_audit_lim = x_stud_audit_lim) OR ((tlinfo.stud_audit_lim IS NULL) AND (X_stud_audit_lim IS NULL)))
859 ) THEN
860 NULL;
861 ELSE
862 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
863 igs_ge_msg_stack.add;
864 app_exception.raise_exception;
865 END IF;
866
867 RETURN;
868
869 END lock_row;
870
871
872 PROCEDURE update_row (
873 x_rowid IN VARCHAR2,
874 x_igs_en_cpd_ext_id IN NUMBER,
875 x_enrolment_cat IN VARCHAR2,
876 x_enr_method_type IN VARCHAR2,
877 x_s_student_comm_type IN VARCHAR2,
878 x_step_order_num IN NUMBER,
879 x_s_enrolment_step_type IN VARCHAR2,
880 x_notification_flag IN VARCHAR2,
881 x_s_rule_call_cd IN VARCHAR2,
882 x_rul_sequence_number IN NUMBER,
883 x_mode IN VARCHAR2 ,
884 x_STUD_AUDIT_LIM IN NUMBER
885 ) AS
886 /*
887 || Created By : [email protected]
888 || Created On : 22-JUN-2001
889 || Purpose : Handles the UPDATE DML logic for the table.
890 || Known limitations, enhancements or remarks :
891 || Change History :
892 || Who When What
893 || (reverse chronological order - newest change first)
894 */
895 x_last_update_date DATE ;
896 x_last_updated_by NUMBER;
897 x_last_update_login NUMBER;
898
899 BEGIN
900
901 x_last_update_date := SYSDATE;
902 IF (X_MODE = 'I') THEN
903 x_last_updated_by := 1;
904 x_last_update_login := 0;
905 ELSIF (x_mode = 'R') THEN
906 x_last_updated_by := fnd_global.user_id;
907 IF x_last_updated_by IS NULL THEN
908 x_last_updated_by := -1;
909 END IF;
910 x_last_update_login := fnd_global.login_id;
911 IF (x_last_update_login IS NULL) THEN
912 x_last_update_login := -1;
913 END IF;
914 ELSE
915 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
916 igs_ge_msg_stack.add;
917 app_exception.raise_exception;
918 END IF;
919
920 before_dml(
921 p_action => 'UPDATE',
922 x_rowid => x_rowid,
923 x_igs_en_cpd_ext_id => x_igs_en_cpd_ext_id,
924 x_enrolment_cat => x_enrolment_cat,
925 x_enr_method_type => x_enr_method_type,
926 x_s_student_comm_type => x_s_student_comm_type,
927 x_step_order_num => x_step_order_num,
928 x_s_enrolment_step_type => x_s_enrolment_step_type,
929 x_notification_flag => x_notification_flag,
930 x_s_rule_call_cd => x_s_rule_call_cd,
931 x_rul_sequence_number => x_rul_sequence_number,
932 x_creation_date => x_last_update_date,
933 x_created_by => x_last_updated_by,
934 x_last_update_date => x_last_update_date,
935 x_last_updated_by => x_last_updated_by,
936 x_last_update_login => x_last_update_login,
937 x_stud_audit_lim => x_stud_audit_lim
938 );
939
940 UPDATE igs_en_cpd_ext_all
941 SET
942 enrolment_cat = new_references.enrolment_cat,
943 enr_method_type = new_references.enr_method_type,
944 s_student_comm_type = new_references.s_student_comm_type,
945 step_order_num = new_references.step_order_num,
946 s_enrolment_step_type = new_references.s_enrolment_step_type,
947 notification_flag = new_references.notification_flag,
948 s_rule_call_cd = new_references.s_rule_call_cd,
949 rul_sequence_number = new_references.rul_sequence_number,
950 stud_audit_lim = new_references.stud_audit_lim,
951 last_update_date = x_last_update_date,
952 last_updated_by = x_last_updated_by,
953 last_update_login = x_last_update_login
954 WHERE rowid = x_rowid;
955
956 IF (SQL%NOTFOUND) THEN
957 RAISE NO_DATA_FOUND;
958 END IF;
959
960 After_DML(
961 p_action => 'UPDATE',
962 x_rowid => X_ROWID
963 );
964
965 END update_row;
966
967
968 PROCEDURE add_row (
969 x_rowid IN OUT NOCOPY VARCHAR2,
970 x_igs_en_cpd_ext_id IN OUT NOCOPY NUMBER,
971 x_enrolment_cat IN VARCHAR2,
972 x_enr_method_type IN VARCHAR2,
973 x_s_student_comm_type IN VARCHAR2,
974 x_step_order_num IN NUMBER,
975 x_s_enrolment_step_type IN VARCHAR2,
976 x_notification_flag IN VARCHAR2,
977 x_s_rule_call_cd IN VARCHAR2,
978 x_rul_sequence_number IN NUMBER,
979 x_mode IN VARCHAR2 ,
980 x_STUD_AUDIT_LIM IN NUMBER
981 ) AS
982 /*
983 || Created By : [email protected]
984 || Created On : 22-JUN-2001
985 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
986 || Known limitations, enhancements or remarks :
987 || Change History :
988 || Who When What
989 || (reverse chronological order - newest change first)
990 */
991 CURSOR c1 IS
992 SELECT rowid
993 FROM igs_en_cpd_ext_all
994 WHERE igs_en_cpd_ext_id = x_igs_en_cpd_ext_id;
995
996 BEGIN
997
998 OPEN c1;
999 FETCH c1 INTO x_rowid;
1000 IF (c1%NOTFOUND) THEN
1001 CLOSE c1;
1002
1003 insert_row (
1004 x_rowid,
1005 x_igs_en_cpd_ext_id,
1006 x_enrolment_cat,
1007 x_enr_method_type,
1008 x_s_student_comm_type,
1009 x_step_order_num,
1010 x_s_enrolment_step_type,
1011 x_notification_flag,
1012 x_s_rule_call_cd,
1013 x_rul_sequence_number,
1014 x_mode,
1015 x_stud_audit_lim
1016 );
1017 RETURN;
1018 END IF;
1019 CLOSE c1;
1020
1021 update_row (
1022 x_rowid,
1023 x_igs_en_cpd_ext_id,
1024 x_enrolment_cat,
1025 x_enr_method_type,
1026 x_s_student_comm_type,
1027 x_step_order_num,
1028 x_s_enrolment_step_type,
1029 x_notification_flag,
1030 x_s_rule_call_cd,
1031 x_rul_sequence_number,
1032 x_mode,
1033 x_stud_audit_lim
1034 );
1035
1036 END add_row;
1037
1038
1039 PROCEDURE delete_row (
1040 x_rowid IN VARCHAR2
1041 ) AS
1042 /*
1043 || Created By : [email protected]
1044 || Created On : 22-JUN-2001
1045 || Purpose : Handles the DELETE DML logic for the table.
1046 || Known limitations, enhancements or remarks :
1047 || Change History :
1048 || Who When What
1049 || (reverse chronological order - newest change first)
1050 */
1051 BEGIN
1052
1053 before_dml (
1054 p_action => 'DELETE',
1055 x_rowid => x_rowid
1056 );
1057
1058 DELETE FROM igs_en_cpd_ext_all
1059 WHERE rowid = x_rowid;
1060
1061 IF (SQL%NOTFOUND) THEN
1062 RAISE NO_DATA_FOUND;
1063 END IF;
1064
1065 After_DML(
1066 p_action => 'DELETE',
1067 x_rowid => X_ROWID
1068 );
1069
1070 END delete_row;
1071
1072 END igs_en_cpd_ext_pkg;
[Home] [Help]