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