[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_STD_WARNINGS_PKG
Source
1 PACKAGE BODY IGS_EN_STD_WARNINGS_PKG AS
2 /* $Header: IGSEI58B.pls 120.0 2005/09/13 10:01:24 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_en_std_warnings%ROWTYPE;
6 new_references igs_en_std_warnings%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_warning_id IN NUMBER,
12 x_person_id IN NUMBER,
13 x_course_cd IN VARCHAR2,
14 x_uoo_id IN NUMBER,
15 x_term_cal_type IN VARCHAR2,
16 x_term_ci_sequence_number IN NUMBER,
17 x_message_for IN VARCHAR2,
18 x_message_icon IN VARCHAR2,
19 x_message_name IN VARCHAR2,
20 x_message_text IN VARCHAR2,
21 x_message_action IN VARCHAR2,
22 x_destination IN VARCHAR2,
23 x_p_parameters IN VARCHAR2,
24 x_step_type IN VARCHAR2,
25 x_session_id IN NUMBER,
26 x_creation_date IN DATE,
27 x_created_by IN NUMBER,
28 x_last_update_date IN DATE,
29 x_last_updated_by IN NUMBER,
30 x_last_update_login IN NUMBER
31 ) AS
32 /*
33 || Created By :
34 || Created On : 20-MAY-2005
35 || Purpose : Initialises the Old and New references for the columns of the table.
36 || Known limitations, enhancements or remarks :
37 || Change History :
38 || Who When What
39 || (reverse chronological order - newest change first)
40 */
41
42 CURSOR cur_old_ref_values IS
43 SELECT *
44 FROM igs_en_std_warnings
45 WHERE rowid = x_rowid;
46
47 BEGIN
48
49 l_rowid := x_rowid;
50
51 -- Code for setting the Old and New Reference Values.
52 -- Populate Old Values.
53 OPEN cur_old_ref_values;
54 FETCH cur_old_ref_values INTO old_references;
55 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
56 CLOSE cur_old_ref_values;
57 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
58 igs_ge_msg_stack.add;
59 app_exception.raise_exception;
60 RETURN;
61 END IF;
62 CLOSE cur_old_ref_values;
63
64 -- Populate New Values.
65 new_references.warning_id := x_warning_id;
66 new_references.person_id := x_person_id;
67 new_references.course_cd := x_course_cd;
68 new_references.uoo_id := x_uoo_id;
69 new_references.term_cal_type := x_term_cal_type;
70 new_references.term_ci_sequence_number := x_term_ci_sequence_number;
71 new_references.message_for := x_message_for;
72 new_references.message_icon := x_message_icon;
73 new_references.message_name := x_message_name;
74 new_references.message_text := x_message_text;
75 new_references.message_action := x_message_action;
76 new_references.destination := x_destination;
77 new_references.p_parameters := x_p_parameters;
78 new_references.step_type := x_step_type;
79 new_references.session_id := x_session_id;
80
81 IF (p_action = 'UPDATE') THEN
82 new_references.creation_date := old_references.creation_date;
83 new_references.created_by := old_references.created_by;
84 ELSE
85 new_references.creation_date := x_creation_date;
86 new_references.created_by := x_created_by;
87 END IF;
88
89 new_references.last_update_date := x_last_update_date;
90 new_references.last_updated_by := x_last_updated_by;
91 new_references.last_update_login := x_last_update_login;
92
93 END set_column_values;
94
95
96 FUNCTION get_pk_for_validation (
97 x_warning_id IN NUMBER
98 ) RETURN BOOLEAN AS
99 /*
100 || Created By :
101 || Created On : 20-MAY-2005
102 || Purpose : Validates the Primary Key of the table.
103 || Known limitations, enhancements or remarks :
104 || Change History :
105 || Who When What
106 || (reverse chronological order - newest change first)
107 */
108 CURSOR cur_rowid IS
109 SELECT rowid
110 FROM igs_en_std_warnings
111 WHERE warning_id = x_warning_id
112 FOR UPDATE NOWAIT;
113
114 lv_rowid cur_rowid%RowType;
115
116 BEGIN
117
118 OPEN cur_rowid;
119 FETCH cur_rowid INTO lv_rowid;
120 IF (cur_rowid%FOUND) THEN
121 CLOSE cur_rowid;
122 RETURN(TRUE);
123 ELSE
124 CLOSE cur_rowid;
125 RETURN(FALSE);
126 END IF;
127
128 END get_pk_for_validation;
129
130
131 PROCEDURE before_dml (
132 p_action IN VARCHAR2,
133 x_rowid IN VARCHAR2,
134 x_warning_id IN NUMBER,
135 x_person_id IN NUMBER,
136 x_course_cd IN VARCHAR2,
137 x_uoo_id IN NUMBER,
138 x_term_cal_type IN VARCHAR2,
139 x_term_ci_sequence_number IN NUMBER,
140 x_message_for IN VARCHAR2,
141 x_message_icon IN VARCHAR2,
142 x_message_name IN VARCHAR2,
143 x_message_text IN VARCHAR2,
144 x_message_action IN VARCHAR2,
145 x_destination IN VARCHAR2,
146 x_p_parameters IN VARCHAR2,
147 x_step_type IN VARCHAR2,
148 x_session_id IN NUMBER,
149 x_creation_date IN DATE,
150 x_created_by IN NUMBER,
151 x_last_update_date IN DATE,
152 x_last_updated_by IN NUMBER,
153 x_last_update_login IN NUMBER
154 ) AS
155 /*
156 || Created By :
157 || Created On : 20-MAY-2005
158 || Purpose : Initialises the columns, Checks Constraints, Calls the
159 || Trigger Handlers for the table, before any DML operation.
160 || Known limitations, enhancements or remarks :
161 || Change History :
162 || Who When What
163 || (reverse chronological order - newest change first)
164 */
165 BEGIN
166
167 set_column_values (
168 p_action,
169 x_rowid,
170 x_warning_id,
171 x_person_id,
172 x_course_cd,
173 x_uoo_id,
174 x_term_cal_type,
175 x_term_ci_sequence_number,
176 x_message_for,
177 x_message_icon,
178 x_message_name,
179 x_message_text,
180 x_message_action,
181 x_destination,
182 x_p_parameters,
183 x_step_type,
184 x_session_id,
185 x_creation_date,
186 x_created_by,
187 x_last_update_date,
188 x_last_updated_by,
189 x_last_update_login
190 );
191
192 IF (p_action = 'INSERT') THEN
193 -- Call all the procedures related to Before Insert.
194 IF ( get_pk_for_validation(
195 new_references.warning_id
196 )
197 ) THEN
198 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
199 igs_ge_msg_stack.add;
200 app_exception.raise_exception;
201 END IF;
202 ELSIF (p_action = 'VALIDATE_INSERT') THEN
203 -- Call all the procedures related to Before Insert.
204 IF ( get_pk_for_validation (
205 new_references.warning_id
206 )
207 ) THEN
208 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
209 igs_ge_msg_stack.add;
210 app_exception.raise_exception;
211 END IF;
212 END IF;
213
214 END before_dml;
215
216
217 PROCEDURE insert_row (
218 x_rowid IN OUT NOCOPY VARCHAR2,
219 x_warning_id IN OUT NOCOPY NUMBER,
220 x_person_id IN NUMBER,
221 x_course_cd IN VARCHAR2,
222 x_uoo_id IN NUMBER,
223 x_term_cal_type IN VARCHAR2,
224 x_term_ci_sequence_number IN NUMBER,
225 x_message_for IN VARCHAR2,
226 x_message_icon IN VARCHAR2,
227 x_message_name IN VARCHAR2,
228 x_message_text IN VARCHAR2,
229 x_message_action IN VARCHAR2,
230 x_destination IN VARCHAR2,
231 x_p_parameters IN VARCHAR2,
232 x_step_type IN VARCHAR2,
233 x_session_id IN NUMBER,
234 x_mode IN VARCHAR2
235 ) AS
236 /*
237 || Created By :
238 || Created On : 20-MAY-2005
239 || Purpose : Handles the INSERT DML logic for the table.
240 || Known limitations, enhancements or remarks :
241 || Change History :
242 || Who When What
243 || (reverse chronological order - newest change first)
244 */
245
246 x_last_update_date DATE;
247 x_last_updated_by NUMBER;
248 x_last_update_login NUMBER;
249
250 BEGIN
251
252 x_last_update_date := SYSDATE;
253 IF (x_mode = 'I') THEN
254 x_last_updated_by := 1;
255 x_last_update_login := 0;
256 ELSIF (x_mode = 'R') THEN
257 x_last_updated_by := fnd_global.user_id;
258 IF (x_last_updated_by IS NULL) THEN
259 x_last_updated_by := -1;
260 END IF;
261 x_last_update_login := fnd_global.login_id;
262 IF (x_last_update_login IS NULL) THEN
263 x_last_update_login := -1;
264 END IF;
265 ELSE
266 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
267 fnd_message.set_token ('ROUTINE', 'igs_en_std_warnings_PKG.INSERT_ROW');
268 igs_ge_msg_stack.add;
269 app_exception.raise_exception;
270 END IF;
271
272 x_warning_id := NULL;
273
274 before_dml(
275 p_action => 'INSERT',
276 x_rowid => x_rowid,
277 x_warning_id => x_warning_id,
278 x_person_id => x_person_id,
279 x_course_cd => x_course_cd,
280 x_uoo_id => x_uoo_id,
284 x_message_icon => x_message_icon,
281 x_term_cal_type => x_term_cal_type,
282 x_term_ci_sequence_number => x_term_ci_sequence_number,
283 x_message_for => x_message_for,
285 x_message_name => x_message_name,
286 x_message_text => x_message_text,
287 x_message_action => x_message_action,
288 x_destination => x_destination,
289 x_p_parameters => x_p_parameters,
290 x_step_type => x_step_type,
291 x_session_id => x_session_id,
292 x_creation_date => x_last_update_date,
293 x_created_by => x_last_updated_by,
294 x_last_update_date => x_last_update_date,
295 x_last_updated_by => x_last_updated_by,
296 x_last_update_login => x_last_update_login
297 );
298
299 INSERT INTO igs_en_std_warnings (
300 warning_id,
301 person_id,
302 course_cd,
303 uoo_id,
304 term_cal_type,
305 term_ci_sequence_number,
306 message_for,
307 message_icon,
308 message_name,
309 message_text,
310 message_action,
311 destination,
312 p_parameters,
313 step_type,
314 session_id,
315 creation_date,
316 created_by,
317 last_update_date,
318 last_updated_by,
319 last_update_login
320 ) VALUES (
321 igs_en_std_warnings_s.NEXTVAL,
322 new_references.person_id,
323 new_references.course_cd,
324 new_references.uoo_id,
325 new_references.term_cal_type,
326 new_references.term_ci_sequence_number,
327 new_references.message_for,
328 new_references.message_icon,
329 new_references.message_name,
330 new_references.message_text,
331 new_references.message_action,
332 new_references.destination,
333 new_references.p_parameters,
334 new_references.step_type,
335 new_references.session_id,
336 x_last_update_date,
337 x_last_updated_by,
338 x_last_update_date,
339 x_last_updated_by,
340 x_last_update_login
341 ) RETURNING ROWID, warning_id INTO x_rowid, x_warning_id;
342
343 END insert_row;
344
345
346 PROCEDURE lock_row (
347 x_rowid IN VARCHAR2,
348 x_warning_id IN NUMBER,
349 x_person_id IN NUMBER,
350 x_course_cd IN VARCHAR2,
351 x_uoo_id IN NUMBER,
352 x_term_cal_type IN VARCHAR2,
353 x_term_ci_sequence_number IN NUMBER,
354 x_message_for IN VARCHAR2,
355 x_message_icon IN VARCHAR2,
356 x_message_name IN VARCHAR2,
357 x_message_text IN VARCHAR2,
358 x_message_action IN VARCHAR2,
359 x_destination IN VARCHAR2,
360 x_p_parameters IN VARCHAR2,
361 x_step_type IN VARCHAR2,
362 x_session_id IN NUMBER
363 ) AS
364 /*
365 || Created By :
366 || Created On : 20-MAY-2005
367 || Purpose : Handles the LOCK mechanism for the table.
368 || Known limitations, enhancements or remarks :
369 || Change History :
370 || Who When What
371 || (reverse chronological order - newest change first)
372 */
373 CURSOR c1 IS
374 SELECT
375 person_id,
376 course_cd,
377 uoo_id,
378 term_cal_type,
379 term_ci_sequence_number,
380 message_for,
381 message_icon,
382 message_name,
383 message_text,
384 message_action,
385 destination,
386 p_parameters,
387 step_type,
388 session_id
389 FROM igs_en_std_warnings
390 WHERE rowid = x_rowid
391 FOR UPDATE NOWAIT;
392
393 tlinfo c1%ROWTYPE;
394
395 BEGIN
396
397 OPEN c1;
398 FETCH c1 INTO tlinfo;
399 IF (c1%notfound) THEN
400 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
401 igs_ge_msg_stack.add;
402 CLOSE c1;
403 app_exception.raise_exception;
404 RETURN;
405 END IF;
406 CLOSE c1;
407
408 IF (
409 (tlinfo.person_id = x_person_id)
410 AND (tlinfo.course_cd = x_course_cd)
411 AND (tlinfo.uoo_id = x_uoo_id)
412 AND (tlinfo.term_cal_type = x_term_cal_type)
413 AND (tlinfo.term_ci_sequence_number = x_term_ci_sequence_number)
414 AND (tlinfo.message_for = x_message_for)
415 AND (tlinfo.message_icon = x_message_icon)
416 AND (tlinfo.message_name = x_message_name)
417 AND (tlinfo.message_text = x_message_text)
418 AND ((tlinfo.message_action = x_message_action) OR ((tlinfo.message_action IS NULL) AND (X_message_action IS NULL)))
419 AND ((tlinfo.destination = x_destination) OR ((tlinfo.destination IS NULL) AND (X_destination IS NULL)))
420 AND ((tlinfo.p_parameters = x_p_parameters) OR ((tlinfo.p_parameters IS NULL) AND (X_p_parameters IS NULL)))
421 AND (tlinfo.step_type = x_step_type)
422 AND (tlinfo.session_id = x_session_id)
423 ) THEN
424 NULL;
425 ELSE
429 END IF;
426 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
427 igs_ge_msg_stack.add;
428 app_exception.raise_exception;
430
431 RETURN;
432
433 END lock_row;
434
435
436 PROCEDURE update_row (
437 x_rowid IN VARCHAR2,
438 x_warning_id IN NUMBER,
439 x_person_id IN NUMBER,
440 x_course_cd IN VARCHAR2,
441 x_uoo_id IN NUMBER,
442 x_term_cal_type IN VARCHAR2,
443 x_term_ci_sequence_number IN NUMBER,
444 x_message_for IN VARCHAR2,
445 x_message_icon IN VARCHAR2,
446 x_message_name IN VARCHAR2,
447 x_message_text IN VARCHAR2,
448 x_message_action IN VARCHAR2,
449 x_destination IN VARCHAR2,
450 x_p_parameters IN VARCHAR2,
451 x_step_type IN VARCHAR2,
452 x_session_id IN NUMBER,
453 x_mode IN VARCHAR2
454 ) AS
455 /*
456 || Created By :
457 || Created On : 20-MAY-2005
458 || Purpose : Handles the UPDATE DML logic for the table.
459 || Known limitations, enhancements or remarks :
460 || Change History :
461 || Who When What
462 || (reverse chronological order - newest change first)
463 */
464 x_last_update_date DATE ;
465 x_last_updated_by NUMBER;
466 x_last_update_login NUMBER;
467
468 BEGIN
469
470 x_last_update_date := SYSDATE;
471 IF (X_MODE = 'I') THEN
472 x_last_updated_by := 1;
473 x_last_update_login := 0;
474 ELSIF (x_mode = 'R') THEN
475 x_last_updated_by := fnd_global.user_id;
476 IF x_last_updated_by IS NULL THEN
477 x_last_updated_by := -1;
478 END IF;
479 x_last_update_login := fnd_global.login_id;
480 IF (x_last_update_login IS NULL) THEN
481 x_last_update_login := -1;
482 END IF;
483 ELSE
484 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
485 fnd_message.set_token ('ROUTINE', 'igs_en_std_warnings_PKG.UPDATE_ROW');
486 igs_ge_msg_stack.add;
487 app_exception.raise_exception;
488 END IF;
489
490 before_dml(
491 p_action => 'UPDATE',
492 x_rowid => x_rowid,
493 x_warning_id => x_warning_id,
494 x_person_id => x_person_id,
495 x_course_cd => x_course_cd,
496 x_uoo_id => x_uoo_id,
497 x_term_cal_type => x_term_cal_type,
498 x_term_ci_sequence_number => x_term_ci_sequence_number,
499 x_message_for => x_message_for,
500 x_message_icon => x_message_icon,
501 x_message_name => x_message_name,
502 x_message_text => x_message_text,
503 x_message_action => x_message_action,
504 x_destination => x_destination,
505 x_p_parameters => x_p_parameters,
506 x_step_type => x_step_type,
507 x_session_id => x_session_id,
508 x_creation_date => x_last_update_date,
509 x_created_by => x_last_updated_by,
510 x_last_update_date => x_last_update_date,
511 x_last_updated_by => x_last_updated_by,
512 x_last_update_login => x_last_update_login
513 );
514
515 UPDATE igs_en_std_warnings
516 SET
517 person_id = new_references.person_id,
518 course_cd = new_references.course_cd,
519 uoo_id = new_references.uoo_id,
520 term_cal_type = new_references.term_cal_type,
521 term_ci_sequence_number = new_references.term_ci_sequence_number,
522 message_for = new_references.message_for,
523 message_icon = new_references.message_icon,
524 message_name = new_references.message_name,
525 message_text = new_references.message_text,
526 message_action = new_references.message_action,
527 destination = new_references.destination,
528 p_parameters = new_references.p_parameters,
529 step_type = new_references.step_type,
530 session_id = new_references.session_id,
531 last_update_date = x_last_update_date,
532 last_updated_by = x_last_updated_by,
533 last_update_login = x_last_update_login
534 WHERE rowid = x_rowid;
535
536 IF (SQL%NOTFOUND) THEN
537 RAISE NO_DATA_FOUND;
538 END IF;
539
540 END update_row;
541
542
543 PROCEDURE add_row (
544 x_rowid IN OUT NOCOPY VARCHAR2,
545 x_warning_id IN OUT NOCOPY NUMBER,
546 x_person_id IN NUMBER,
547 x_course_cd IN VARCHAR2,
548 x_uoo_id IN NUMBER,
549 x_term_cal_type IN VARCHAR2,
553 x_message_name IN VARCHAR2,
550 x_term_ci_sequence_number IN NUMBER,
551 x_message_for IN VARCHAR2,
552 x_message_icon IN VARCHAR2,
554 x_message_text IN VARCHAR2,
555 x_message_action IN VARCHAR2,
556 x_destination IN VARCHAR2,
557 x_p_parameters IN VARCHAR2,
558 x_step_type IN VARCHAR2,
559 x_session_id IN NUMBER,
560 x_mode IN VARCHAR2
561 ) AS
562 /*
563 || Created By :
564 || Created On : 20-MAY-2005
565 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
566 || Known limitations, enhancements or remarks :
567 || Change History :
568 || Who When What
569 || (reverse chronological order - newest change first)
570 */
571 CURSOR c1 IS
572 SELECT rowid
573 FROM igs_en_std_warnings
574 WHERE warning_id = x_warning_id;
575
576 BEGIN
577
578 OPEN c1;
579 FETCH c1 INTO x_rowid;
580 IF (c1%NOTFOUND) THEN
581 CLOSE c1;
582
583 insert_row (
584 x_rowid,
585 x_warning_id,
586 x_person_id,
587 x_course_cd,
588 x_uoo_id,
589 x_term_cal_type,
590 x_term_ci_sequence_number,
591 x_message_for,
592 x_message_icon,
593 x_message_name,
594 x_message_text,
595 x_message_action,
596 x_destination,
597 x_p_parameters,
598 x_step_type,
599 x_session_id,
600 x_mode
601 );
602 RETURN;
603 END IF;
604 CLOSE c1;
605
606 update_row (
607 x_rowid,
608 x_warning_id,
609 x_person_id,
610 x_course_cd,
611 x_uoo_id,
612 x_term_cal_type,
613 x_term_ci_sequence_number,
614 x_message_for,
615 x_message_icon,
616 x_message_name,
617 x_message_text,
618 x_message_action,
619 x_destination,
620 x_p_parameters,
621 x_step_type,
622 x_session_id,
623 x_mode
624 );
625
626 END add_row;
627
628
629 PROCEDURE delete_row (
630 x_rowid IN VARCHAR2
631 ) AS
632 /*
633 || Created By :
634 || Created On : 20-MAY-2005
635 || Purpose : Handles the DELETE DML logic for the table.
636 || Known limitations, enhancements or remarks :
637 || Change History :
638 || Who When What
639 || (reverse chronological order - newest change first)
640 */
641 BEGIN
642
643 before_dml (
644 p_action => 'DELETE',
645 x_rowid => x_rowid
646 );
647
648 DELETE FROM igs_en_std_warnings
649 WHERE rowid = x_rowid;
650
651 IF (SQL%NOTFOUND) THEN
652 RAISE NO_DATA_FOUND;
653 END IF;
654
655 END delete_row;
656
657
658 END igs_en_std_warnings_pkg;