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