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