[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_ANON_ID_US_PKG
Source
1 PACKAGE BODY igs_as_anon_id_us_pkg AS
2 /* $Header: IGSDI62B.pls 115.2 2003/05/20 05:20:48 svanukur noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_as_anon_id_us%ROWTYPE;
6 new_references igs_as_anon_id_us%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_anonymous_id IN VARCHAR2 DEFAULT NULL,
13 x_system_generated_ind IN VARCHAR2 DEFAULT NULL,
14 x_course_cd IN VARCHAR2 DEFAULT NULL,
15 x_unit_cd IN VARCHAR2 DEFAULT NULL,
16 x_teach_cal_type IN VARCHAR2 DEFAULT NULL,
17 x_teach_ci_sequence_number IN NUMBER DEFAULT NULL,
18 x_uoo_id IN NUMBER DEFAULT NULL,
19 x_load_cal_type IN VARCHAR2 DEFAULT NULL,
20 x_load_ci_sequence_number IN NUMBER DEFAULT NULL,
21 x_creation_date IN DATE DEFAULT NULL,
22 x_created_by IN NUMBER DEFAULT NULL,
23 x_last_update_date IN DATE DEFAULT NULL,
24 x_last_updated_by IN NUMBER DEFAULT NULL,
25 x_last_update_login IN NUMBER DEFAULT NULL
26 ) AS
27 /*
28 || Created By : cdcruz
29 || Created On : 29-JAN-2002
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_as_anon_id_us
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.person_id := x_person_id;
61 new_references.anonymous_id := x_anonymous_id;
62 new_references.system_generated_ind := x_system_generated_ind;
63 new_references.course_cd := x_course_cd;
64 new_references.unit_cd := x_unit_cd;
65 new_references.teach_cal_type := x_teach_cal_type;
66 new_references.teach_ci_sequence_number := x_teach_ci_sequence_number;
67 new_references.uoo_id := x_uoo_id;
68 new_references.load_cal_type := x_load_cal_type;
69 new_references.load_ci_sequence_number := x_load_ci_sequence_number;
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 FUNCTION get_uk_for_validation (
86 x_anonymous_id IN VARCHAR2,
87 x_load_cal_type IN VARCHAR2,
88 x_load_ci_sequence_number IN NUMBER
89 ) RETURN BOOLEAN AS
90 /*
91 || Created By : cdcruz
92 || Created On : 29-JAN-2002
93 || Purpose : Validates the Unique Keys of the table.
94 || Known limitations, enhancements or remarks :
95 || Change History :
96 || Who When What
97 || (reverse chronological order - newest change first)
98 */
99 CURSOR cur_rowid IS
100 SELECT rowid
101 FROM igs_as_anon_id_us
102 WHERE anonymous_id = x_anonymous_id
103 AND load_cal_type = x_load_cal_type
104 AND load_ci_sequence_number = x_load_ci_sequence_number
105 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
106
107 lv_rowid cur_rowid%RowType;
108
109 BEGIN
110
111 OPEN cur_rowid;
112 FETCH cur_rowid INTO lv_rowid;
113 IF (cur_rowid%FOUND) THEN
114 CLOSE cur_rowid;
115 RETURN (true);
116 ELSE
117 CLOSE cur_rowid;
118 RETURN(FALSE);
119 END IF;
120
121 END get_uk_for_validation ;
122
123
124 PROCEDURE check_uniqueness AS
125 /*
126 || Created By : cdcruz
127 || Created On : 29-JAN-2002
128 || Purpose : Handles the Unique Constraint logic defined for the columns.
129 || Known limitations, enhancements or remarks :
130 || Change History :
131 || Who When What
132 || (reverse chronological order - newest change first)
133 */
134 BEGIN
135
136
137 IF ( get_uk_for_validation (
138 new_references.anonymous_id,
139 new_references.load_cal_type,
140 new_references.load_ci_sequence_number
141 )) THEN
142 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
143 igs_ge_msg_stack.add;
144 app_exception.raise_exception;
145 END IF;
146
147 END check_uniqueness;
148
149
150 PROCEDURE check_parent_existance AS
151 /*
152 || Created By : cdcruz
153 || Created On : 29-JAN-2002
154 || Purpose : Checks for the existance of Parent records.
155 || Known limitations, enhancements or remarks :
156 || Change History :
157 || Who When What
158 || (reverse chronological order - newest change first)
159 || svanukur 29-APR-03 changed igs_en_su_attempt_pkg.get_pk_for_validation as part of MUS build # 2829262
160 */
161 BEGIN
162
163 IF (((old_references.load_cal_type = new_references.load_cal_type) AND
164 (old_references.load_ci_sequence_number = new_references.load_ci_sequence_number)) OR
165 ((new_references.load_cal_type IS NULL) OR
166 (new_references.load_ci_sequence_number IS NULL))) THEN
167 NULL;
168 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
169 new_references.load_cal_type,
170 new_references.load_ci_sequence_number
171 ) THEN
172 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
173 igs_ge_msg_stack.add;
174 app_exception.raise_exception;
175 END IF;
176
177 IF (((old_references.person_id = new_references.person_id) AND
178 (old_references.course_cd = new_references.course_cd) AND
179 (old_references.uoo_id = new_references.uoo_id)) OR
180 ((new_references.person_id IS NULL) OR
181 (new_references.course_cd IS NULL) OR
182 (new_references.uoo_id IS NULL))) THEN
183 NULL;
184 ELSIF NOT igs_en_su_attempt_pkg.get_pk_for_validation (
185 new_references.person_id,
186 new_references.course_cd,
187 new_references.uoo_id
188 ) THEN
189 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
190 igs_ge_msg_stack.add;
191 app_exception.raise_exception;
192 END IF;
193
194 IF (((old_references.person_id = new_references.person_id) AND
195 (old_references.course_cd = new_references.course_cd)) OR
196 ((new_references.person_id IS NULL) OR
197 (new_references.course_cd IS NULL))) THEN
198 NULL;
199 ELSIF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
200 new_references.person_id,
201 new_references.course_cd
202 ) THEN
203 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
204 igs_ge_msg_stack.add;
205 app_exception.raise_exception;
206 END IF;
207
208 IF (((old_references.uoo_id = new_references.uoo_id)) OR
209 ((new_references.uoo_id IS NULL))) THEN
210 NULL;
211 ELSIF NOT igs_ps_unit_ofr_opt_pkg.get_uk_For_validation (
212 new_references.uoo_id
213 ) THEN
214 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
215 igs_ge_msg_stack.add;
216 app_exception.raise_exception;
217 END IF;
218
219 END check_parent_existance;
220
221
222 FUNCTION get_pk_for_validation (
223 x_person_id IN NUMBER,
224 x_course_cd IN VARCHAR2,
225 x_uoo_id IN NUMBER
226 ) RETURN BOOLEAN AS
227 /*
228 || Created By : cdcruz
229 || Created On : 29-JAN-2002
230 || Purpose : Validates the Primary Key of the table.
231 || Known limitations, enhancements or remarks :
232 || Change History :
233 || Who When What
234 || (reverse chronological order - newest change first)
235 ||svanukur 29-APR-03 changed the PK columns as part of MUS build # 2829262
236 */
237 CURSOR cur_rowid IS
238 SELECT rowid
239 FROM igs_as_anon_id_us
240 WHERE person_id = x_person_id
241 AND course_cd = x_course_cd
242 AND uoo_id = x_uoo_id
243 FOR UPDATE NOWAIT;
244
245 lv_rowid cur_rowid%RowType;
246
247 BEGIN
248
249 OPEN cur_rowid;
250 FETCH cur_rowid INTO lv_rowid;
251 IF (cur_rowid%FOUND) THEN
252 CLOSE cur_rowid;
253 RETURN(TRUE);
254 ELSE
255 CLOSE cur_rowid;
256 RETURN(FALSE);
257 END IF;
258
259 END get_pk_for_validation;
260
261
262 PROCEDURE get_fk_igs_ca_inst (
263 x_cal_type IN VARCHAR2,
264 x_sequence_number IN NUMBER
265 ) AS
266 /*
267 || Created By : cdcruz
268 || Created On : 29-JAN-2002
269 || Purpose : Validates the Foreign Keys for the table.
270 || Known limitations, enhancements or remarks :
271 || Change History :
272 || Who When What
273 || (reverse chronological order - newest change first)
274 */
275 CURSOR cur_rowid IS
276 SELECT rowid
277 FROM igs_as_anon_id_us
278 WHERE ((load_cal_type = x_cal_type) AND
279 (load_ci_sequence_number = x_sequence_number));
280
281 lv_rowid cur_rowid%RowType;
282
283 BEGIN
284
285 OPEN cur_rowid;
286 FETCH cur_rowid INTO lv_rowid;
287 IF (cur_rowid%FOUND) THEN
288 CLOSE cur_rowid;
289 fnd_message.set_name ('IGS', 'IGS_AS_ASEC_CI_FK');
290 igs_ge_msg_stack.add;
291 app_exception.raise_exception;
292 RETURN;
293 END IF;
294 CLOSE cur_rowid;
295
296 END get_fk_igs_ca_inst;
297
298
299 PROCEDURE get_fk_igs_en_su_attempt (
300 x_person_id IN NUMBER,
301 x_course_cd IN VARCHAR2,
302 x_uoo_id IN NUMBER
303 ) AS
304 /*
305 || Created By : cdcruz
306 || Created On : 29-JAN-2002
307 || Purpose : Validates the Foreign Keys for the table.
308 || Known limitations, enhancements or remarks :
309 || Change History :
310 || Who When What
311 || (reverse chronological order - newest change first)
312 ||svanukur 29-APR-03 changed the FK columns as part of MUS build # 2829262
313 */
314 CURSOR cur_rowid IS
315 SELECT rowid
316 FROM igs_as_anon_id_us
317 WHERE ((course_cd = x_course_cd) AND
318 (person_id = x_person_id) AND
319 (uoo_id = x_uoo_id));
320
321 lv_rowid cur_rowid%RowType;
322
323 BEGIN
324
325 OPEN cur_rowid;
326 FETCH cur_rowid INTO lv_rowid;
327 IF (cur_rowid%FOUND) THEN
328 CLOSE cur_rowid;
329 fnd_message.set_name ('IGS', 'IGS_AS_ASEC_SUA_FK');
330 igs_ge_msg_stack.add;
331 app_exception.raise_exception;
332 RETURN;
333 END IF;
334 CLOSE cur_rowid;
335
336 END get_fk_igs_en_su_attempt;
337
338
339 PROCEDURE get_fk_igs_en_stdnt_ps_att (
340 x_person_id IN NUMBER,
341 x_course_cd IN VARCHAR2
342 ) AS
343 /*
344 || Created By : cdcruz
345 || Created On : 29-JAN-2002
346 || Purpose : Validates the Foreign Keys for the table.
347 || Known limitations, enhancements or remarks :
348 || Change History :
349 || Who When What
350 || (reverse chronological order - newest change first)
351 */
352 CURSOR cur_rowid IS
353 SELECT rowid
354 FROM igs_as_anon_id_us
355 WHERE ((course_cd = x_course_cd) AND
359
356 (person_id = x_person_id));
357
358 lv_rowid cur_rowid%RowType;
360 BEGIN
361
362 OPEN cur_rowid;
363 FETCH cur_rowid INTO lv_rowid;
364 IF (cur_rowid%FOUND) THEN
365 CLOSE cur_rowid;
366 fnd_message.set_name ('IGS', 'IGS_AS_ASEC_SCA_FK');
367 igs_ge_msg_stack.add;
368 app_exception.raise_exception;
369 RETURN;
370 END IF;
371 CLOSE cur_rowid;
372
373 END get_fk_igs_en_stdnt_ps_att;
374
375
376 PROCEDURE get_ufk_igs_ps_unit_ofr_opt (
377 x_uoo_id IN NUMBER
378 ) AS
379 /*
380 || Created By : cdcruz
381 || Created On : 29-JAN-2002
382 || Purpose : Validates the Foreign Keys for the table.
383 || Known limitations, enhancements or remarks :
384 || Change History :
385 || Who When What
386 || (reverse chronological order - newest change first)
387 */
388 CURSOR cur_rowid IS
389 SELECT rowid
390 FROM igs_as_anon_id_us
391 WHERE ((uoo_id = x_uoo_id));
392
393 lv_rowid cur_rowid%RowType;
394
395 BEGIN
396
397 OPEN cur_rowid;
398 FETCH cur_rowid INTO lv_rowid;
399 IF (cur_rowid%FOUND) THEN
400 CLOSE cur_rowid;
401 fnd_message.set_name ('IGS', 'IGS_AS_ASEC_UOO_FK');
402 igs_ge_msg_stack.add;
403 app_exception.raise_exception;
404 RETURN;
405 END IF;
406 CLOSE cur_rowid;
407
408 END get_ufk_igs_ps_unit_ofr_opt;
409
410
411 PROCEDURE before_dml (
412 p_action IN VARCHAR2,
413 x_rowid IN VARCHAR2 DEFAULT NULL,
414 x_person_id IN NUMBER DEFAULT NULL,
415 x_anonymous_id IN VARCHAR2 DEFAULT NULL,
416 x_system_generated_ind IN VARCHAR2 DEFAULT NULL,
417 x_course_cd IN VARCHAR2 DEFAULT NULL,
418 x_unit_cd IN VARCHAR2 DEFAULT NULL,
419 x_teach_cal_type IN VARCHAR2 DEFAULT NULL,
420 x_teach_ci_sequence_number IN NUMBER DEFAULT NULL,
421 x_uoo_id IN NUMBER DEFAULT NULL,
422 x_load_cal_type IN VARCHAR2 DEFAULT NULL,
423 x_load_ci_sequence_number IN NUMBER DEFAULT NULL,
424 x_creation_date IN DATE DEFAULT NULL,
425 x_created_by IN NUMBER DEFAULT NULL,
426 x_last_update_date IN DATE DEFAULT NULL,
427 x_last_updated_by IN NUMBER DEFAULT NULL,
428 x_last_update_login IN NUMBER DEFAULT NULL
429 ) AS
430 /*
431 || Created By : cdcruz
432 || Created On : 29-JAN-2002
433 || Purpose : Initialises the columns, Checks Constraints, Calls the
434 || Trigger Handlers for the table, before any DML operation.
435 || Known limitations, enhancements or remarks :
436 || Change History :
437 || Who When What
438 || (reverse chronological order - newest change first)
439 */
440 BEGIN
441
442 set_column_values (
443 p_action,
444 x_rowid,
445 x_person_id,
446 x_anonymous_id,
447 x_system_generated_ind,
448 x_course_cd,
449 x_unit_cd,
450 x_teach_cal_type,
451 x_teach_ci_sequence_number,
452 x_uoo_id,
453 x_load_cal_type,
454 x_load_ci_sequence_number,
455 x_creation_date,
456 x_created_by,
457 x_last_update_date,
458 x_last_updated_by,
459 x_last_update_login
460 );
461
462 IF (p_action = 'INSERT') THEN
463 -- Call all the procedures related to Before Insert.
464 IF ( get_pk_for_validation(
465 new_references.person_id,
466 new_references.course_cd,
467 new_references.uoo_id
468 )) THEN
469 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
470 igs_ge_msg_stack.add;
471 app_exception.raise_exception;
472 END IF;
473 check_uniqueness;
474 check_parent_existance;
475 ELSIF (p_action = 'UPDATE') THEN
476 -- Call all the procedures related to Before Update.
477 check_uniqueness;
478 check_parent_existance;
479 ELSIF (p_action = 'VALIDATE_INSERT') THEN
480 -- Call all the procedures related to Before Insert.
481 IF ( get_pk_for_validation (
482 new_references.person_id,
483 new_references.course_cd,
484 new_references.uoo_id
485 ) ) THEN
486 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
487 igs_ge_msg_stack.add;
488 app_exception.raise_exception;
489 END IF;
490 check_uniqueness;
491 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
492 check_uniqueness;
493 END IF;
494
495 END before_dml;
496
497
498 PROCEDURE insert_row (
502 x_system_generated_ind IN VARCHAR2,
499 x_rowid IN OUT NOCOPY VARCHAR2,
500 x_person_id IN NUMBER,
501 x_anonymous_id IN VARCHAR2,
503 x_course_cd IN VARCHAR2,
504 x_unit_cd IN VARCHAR2,
505 x_teach_cal_type IN VARCHAR2,
506 x_teach_ci_sequence_number IN NUMBER,
507 x_uoo_id IN NUMBER,
508 x_load_cal_type IN VARCHAR2,
509 x_load_ci_sequence_number IN NUMBER,
510 x_mode IN VARCHAR2 DEFAULT 'R'
511 ) AS
512 /*
513 || Created By : cdcruz
514 || Created On : 29-JAN-2002
515 || Purpose : Handles the INSERT DML logic for the table.
516 || Known limitations, enhancements or remarks :
517 || Change History :
518 || Who When What
519 || (reverse chronological order - newest change first)
520 */
521 CURSOR c IS
522 SELECT rowid
523 FROM igs_as_anon_id_us
524 WHERE person_id = x_person_id
525 AND course_cd = x_course_cd
526 AND uoo_id = x_uoo_id;
527
528
529 x_last_update_date DATE;
530 x_last_updated_by NUMBER;
531 x_last_update_login NUMBER;
532 x_request_id NUMBER;
533 x_program_id NUMBER;
534 x_program_application_id NUMBER;
535 x_program_update_date DATE;
536
537 BEGIN
538
539 x_last_update_date := SYSDATE;
540 IF (x_mode = 'I') THEN
541 x_last_updated_by := 1;
542 x_last_update_login := 0;
543 ELSIF (x_mode = 'R') THEN
544 x_last_updated_by := fnd_global.user_id;
545 IF (x_last_updated_by IS NULL) THEN
546 x_last_updated_by := -1;
547 END IF;
548 x_last_update_login := fnd_global.login_id;
549 IF (x_last_update_login IS NULL) THEN
550 x_last_update_login := -1;
551 END IF;
552 x_request_id := fnd_global.conc_request_id;
553 x_program_id := fnd_global.conc_program_id;
554 x_program_application_id := fnd_global.prog_appl_id;
555
556 IF (x_request_id = -1) THEN
557 x_request_id := NULL;
558 x_program_id := NULL;
559 x_program_application_id := NULL;
560 x_program_update_date := NULL;
561 ELSE
562 x_program_update_date := SYSDATE;
563 END IF;
564 ELSE
565 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
566 igs_ge_msg_stack.add;
567 app_exception.raise_exception;
568 END IF;
569
570 before_dml(
571 p_action => 'INSERT',
572 x_rowid => x_rowid,
573 x_person_id => x_person_id,
574 x_anonymous_id => x_anonymous_id,
575 x_system_generated_ind => x_system_generated_ind,
576 x_course_cd => x_course_cd,
577 x_unit_cd => x_unit_cd,
578 x_teach_cal_type => x_teach_cal_type,
579 x_teach_ci_sequence_number => x_teach_ci_sequence_number,
580 x_uoo_id => x_uoo_id,
581 x_load_cal_type => x_load_cal_type,
582 x_load_ci_sequence_number => x_load_ci_sequence_number,
583 x_creation_date => x_last_update_date,
584 x_created_by => x_last_updated_by,
585 x_last_update_date => x_last_update_date,
586 x_last_updated_by => x_last_updated_by,
587 x_last_update_login => x_last_update_login
588 );
589
590 INSERT INTO igs_as_anon_id_us (
591 person_id,
592 anonymous_id,
593 system_generated_ind,
594 course_cd,
595 unit_cd,
596 teach_cal_type,
597 teach_ci_sequence_number,
598 uoo_id,
599 load_cal_type,
600 load_ci_sequence_number,
601 creation_date,
602 created_by,
603 last_update_date,
604 last_updated_by,
605 last_update_login,
606 request_id,
607 program_id,
608 program_application_id,
609 program_update_date
610 ) VALUES (
611 new_references.person_id,
612 new_references.anonymous_id,
613 new_references.system_generated_ind,
614 new_references.course_cd,
615 new_references.unit_cd,
616 new_references.teach_cal_type,
617 new_references.teach_ci_sequence_number,
618 new_references.uoo_id,
619 new_references.load_cal_type,
620 new_references.load_ci_sequence_number,
621 x_last_update_date,
622 x_last_updated_by,
623 x_last_update_date,
624 x_last_updated_by,
625 x_last_update_login ,
626 x_request_id,
627 x_program_id,
631
628 x_program_application_id,
629 x_program_update_date
630 );
632 OPEN c;
633 FETCH c INTO x_rowid;
634 IF (c%NOTFOUND) THEN
635 CLOSE c;
636 RAISE NO_DATA_FOUND;
637 END IF;
638 CLOSE c;
639
640 END insert_row;
641
642
643 PROCEDURE lock_row (
644 x_rowid IN VARCHAR2,
645 x_person_id IN NUMBER,
646 x_anonymous_id IN VARCHAR2,
647 x_system_generated_ind IN VARCHAR2,
648 x_course_cd IN VARCHAR2,
649 x_unit_cd IN VARCHAR2,
650 x_teach_cal_type IN VARCHAR2,
651 x_teach_ci_sequence_number IN NUMBER,
652 x_uoo_id IN NUMBER,
653 x_load_cal_type IN VARCHAR2,
654 x_load_ci_sequence_number IN NUMBER
655 ) AS
656 /*
657 || Created By : cdcruz
658 || Created On : 29-JAN-2002
659 || Purpose : Handles the LOCK mechanism for the table.
660 || Known limitations, enhancements or remarks :
661 || Change History :
662 || Who When What
663 || (reverse chronological order - newest change first)
664 */
665 CURSOR c1 IS
666 SELECT
667 anonymous_id,
668 system_generated_ind,
669 uoo_id,
670 load_cal_type,
671 load_ci_sequence_number
672 FROM igs_as_anon_id_us
673 WHERE rowid = x_rowid
674 FOR UPDATE NOWAIT;
675
676 tlinfo c1%ROWTYPE;
677
678 BEGIN
679
680 OPEN c1;
681 FETCH c1 INTO tlinfo;
682 IF (c1%notfound) THEN
683 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
684 igs_ge_msg_stack.add;
685 CLOSE c1;
686 app_exception.raise_exception;
687 RETURN;
688 END IF;
689 CLOSE c1;
690
691 IF (
692 (tlinfo.anonymous_id = x_anonymous_id)
693 AND (tlinfo.system_generated_ind = x_system_generated_ind)
694 AND (tlinfo.uoo_id = x_uoo_id)
695 AND (tlinfo.load_cal_type = x_load_cal_type)
696 AND (tlinfo.load_ci_sequence_number = x_load_ci_sequence_number)
697 ) THEN
698 NULL;
699 ELSE
700 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
701 igs_ge_msg_stack.add;
702 app_exception.raise_exception;
703 END IF;
704
705 RETURN;
706
707 END lock_row;
708
709
710 PROCEDURE update_row (
711 x_rowid IN VARCHAR2,
712 x_person_id IN NUMBER,
713 x_anonymous_id IN VARCHAR2,
714 x_system_generated_ind IN VARCHAR2,
715 x_course_cd IN VARCHAR2,
716 x_unit_cd IN VARCHAR2,
717 x_teach_cal_type IN VARCHAR2,
718 x_teach_ci_sequence_number IN NUMBER,
719 x_uoo_id IN NUMBER,
720 x_load_cal_type IN VARCHAR2,
721 x_load_ci_sequence_number IN NUMBER,
722 x_mode IN VARCHAR2 DEFAULT 'R'
723 ) AS
724 /*
725 || Created By : cdcruz
726 || Created On : 29-JAN-2002
727 || Purpose : Handles the UPDATE DML logic for the table.
728 || Known limitations, enhancements or remarks :
729 || Change History :
730 || Who When What
731 || (reverse chronological order - newest change first)
732 */
733 x_last_update_date DATE ;
734 x_last_updated_by NUMBER;
735 x_last_update_login NUMBER;
736 x_request_id NUMBER;
737 x_program_id NUMBER;
738 x_program_application_id NUMBER;
739 x_program_update_date DATE;
740
741 BEGIN
742
743 x_last_update_date := SYSDATE;
744 IF (X_MODE = 'I') THEN
745 x_last_updated_by := 1;
746 x_last_update_login := 0;
747 ELSIF (x_mode = 'R') THEN
748 x_last_updated_by := fnd_global.user_id;
749 IF x_last_updated_by IS NULL THEN
750 x_last_updated_by := -1;
751 END IF;
752 x_last_update_login := fnd_global.login_id;
753 IF (x_last_update_login IS NULL) THEN
754 x_last_update_login := -1;
755 END IF;
756 ELSE
757 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
758 igs_ge_msg_stack.add;
759 app_exception.raise_exception;
760 END IF;
761
762 before_dml(
763 p_action => 'UPDATE',
764 x_rowid => x_rowid,
765 x_person_id => x_person_id,
766 x_anonymous_id => x_anonymous_id,
767 x_system_generated_ind => x_system_generated_ind,
768 x_course_cd => x_course_cd,
769 x_unit_cd => x_unit_cd,
770 x_teach_cal_type => x_teach_cal_type,
774 x_load_ci_sequence_number => x_load_ci_sequence_number,
771 x_teach_ci_sequence_number => x_teach_ci_sequence_number,
772 x_uoo_id => x_uoo_id,
773 x_load_cal_type => x_load_cal_type,
775 x_creation_date => x_last_update_date,
776 x_created_by => x_last_updated_by,
777 x_last_update_date => x_last_update_date,
778 x_last_updated_by => x_last_updated_by,
779 x_last_update_login => x_last_update_login
780 );
781
782 IF (x_mode = 'R') THEN
783 x_request_id := fnd_global.conc_request_id;
784 x_program_id := fnd_global.conc_program_id;
785 x_program_application_id := fnd_global.prog_appl_id;
786 IF (x_request_id = -1) THEN
787 x_request_id := old_references.request_id;
788 x_program_id := old_references.program_id;
789 x_program_application_id := old_references.program_application_id;
790 x_program_update_date := old_references.program_update_date;
791 ELSE
792 x_program_update_date := SYSDATE;
793 END IF;
794 END IF;
795
796 UPDATE igs_as_anon_id_us
797 SET
798 anonymous_id = new_references.anonymous_id,
799 system_generated_ind = new_references.system_generated_ind,
800 load_cal_type = new_references.load_cal_type,
801 load_ci_sequence_number = new_references.load_ci_sequence_number,
802 last_update_date = x_last_update_date,
803 last_updated_by = x_last_updated_by,
804 last_update_login = x_last_update_login ,
805 request_id = x_request_id,
806 program_id = x_program_id,
807 program_application_id = x_program_application_id,
808 program_update_date = x_program_update_date
809 WHERE rowid = x_rowid;
810
811 IF (SQL%NOTFOUND) THEN
812 RAISE NO_DATA_FOUND;
813 END IF;
814
815 END update_row;
816
817
818 PROCEDURE add_row (
819 x_rowid IN OUT NOCOPY VARCHAR2,
820 x_person_id IN NUMBER,
821 x_anonymous_id IN VARCHAR2,
822 x_system_generated_ind IN VARCHAR2,
823 x_course_cd IN VARCHAR2,
824 x_unit_cd IN VARCHAR2,
825 x_teach_cal_type IN VARCHAR2,
826 x_teach_ci_sequence_number IN NUMBER,
827 x_uoo_id IN NUMBER,
828 x_load_cal_type IN VARCHAR2,
829 x_load_ci_sequence_number IN NUMBER,
830 x_mode IN VARCHAR2 DEFAULT 'R'
831 ) AS
832 /*
833 || Created By : cdcruz
834 || Created On : 29-JAN-2002
835 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
836 || Known limitations, enhancements or remarks :
837 || Change History :
838 || Who When What
839 || (reverse chronological order - newest change first)
840 */
841 CURSOR c1 IS
842 SELECT rowid
843 FROM igs_as_anon_id_us
844 WHERE person_id = x_person_id
845 AND course_cd = x_course_cd
846 AND uoo_id = x_uoo_id;
847
848
849 BEGIN
850
851 OPEN c1;
852 FETCH c1 INTO x_rowid;
853 IF (c1%NOTFOUND) THEN
854 CLOSE c1;
855
856 insert_row (
857 x_rowid,
858 x_person_id,
859 x_anonymous_id,
860 x_system_generated_ind,
861 x_course_cd,
862 x_unit_cd,
863 x_teach_cal_type,
864 x_teach_ci_sequence_number,
865 x_uoo_id,
866 x_load_cal_type,
867 x_load_ci_sequence_number,
868 x_mode
869 );
870 RETURN;
871 END IF;
872 CLOSE c1;
873
874 update_row (
875 x_rowid,
876 x_person_id,
877 x_anonymous_id,
878 x_system_generated_ind,
879 x_course_cd,
880 x_unit_cd,
881 x_teach_cal_type,
882 x_teach_ci_sequence_number,
883 x_uoo_id,
884 x_load_cal_type,
885 x_load_ci_sequence_number,
886 x_mode
887 );
888
889 END add_row;
890
891
892 PROCEDURE delete_row (
893 x_rowid IN VARCHAR2
894 ) AS
895 /*
896 || Created By : cdcruz
897 || Created On : 29-JAN-2002
898 || Purpose : Handles the DELETE DML logic for the table.
899 || Known limitations, enhancements or remarks :
900 || Change History :
901 || Who When What
902 || (reverse chronological order - newest change first)
903 */
904 BEGIN
905
906 before_dml (
907 p_action => 'DELETE',
908 x_rowid => x_rowid
909 );
910
911 DELETE FROM igs_as_anon_id_us
912 WHERE rowid = x_rowid;
913
917
914 IF (SQL%NOTFOUND) THEN
915 RAISE NO_DATA_FOUND;
916 END IF;
918 END delete_row;
919
920
921 END igs_as_anon_id_us_pkg;