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