[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_AWD_FRML_DET_PKG
Source
1 PACKAGE BODY igf_aw_awd_frml_det_pkg AS
2 /* $Header: IGFWI13B.pls 120.0 2005/06/02 15:46:43 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_awd_frml_det_all%ROWTYPE;
6 new_references igf_aw_awd_frml_det_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_formula_code IN VARCHAR2 ,
12 x_ci_cal_type IN VARCHAR2 ,
13 x_ci_sequence_number IN NUMBER ,
14 x_seq_no IN NUMBER ,
15 x_fund_id IN NUMBER ,
16 x_min_award_amt IN NUMBER ,
17 x_max_award_amt IN NUMBER ,
18 x_replace_fc IN VARCHAR2 ,
19 x_pe_group_id IN NUMBER ,
20 x_adplans_id IN NUMBER ,
21 x_lock_award_flag IN VARCHAR2 ,
22 x_creation_date IN DATE ,
23 x_created_by IN NUMBER ,
24 x_last_update_date IN DATE ,
25 x_last_updated_by IN NUMBER ,
26 x_last_update_login IN NUMBER
27 ) AS
28 /*
29 || Created By : avenkatr
30 || Created On : 08-NOV-2000
31 || Purpose : Initialises the Old and New references for the columns of the table.
32 || Known limitations, enhancements or remarks :
33 || Change History :
34 || Who When What
35 || (reverse chronological order - newest change first)
36 || veramach 06-NOV-2003 FA 125 Multiple distr methods
37 || Added new column adplans_id
38 */
39
40 CURSOR cur_old_ref_values IS
41 SELECT *
42 FROM IGF_AW_AWD_FRML_DET_ALL
43 WHERE rowid = x_rowid;
44
45 BEGIN
46
47 l_rowid := x_rowid;
48
49 -- Code for setting the Old and New Reference Values.
50 -- Populate Old Values.
51 OPEN cur_old_ref_values;
52 FETCH cur_old_ref_values INTO old_references;
53 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
54 CLOSE cur_old_ref_values;
55 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
56 igs_ge_msg_stack.add;
57 app_exception.raise_exception;
58 RETURN;
59 END IF;
60 CLOSE cur_old_ref_values;
61
62 -- Populate New Values.
63 new_references.formula_code := x_formula_code;
64 new_references.ci_cal_type := x_ci_cal_type;
65 new_references.ci_sequence_number := x_ci_sequence_number;
66 new_references.seq_no := x_seq_no;
67 new_references.fund_id := x_fund_id;
68 new_references.min_award_amt := x_min_award_amt;
69 new_references.max_award_amt := x_max_award_amt;
70 new_references.replace_fc := x_replace_fc;
71 new_references.pe_group_id := x_pe_group_id;
72 new_references.adplans_id := x_adplans_id;
73 new_references.lock_award_flag := x_lock_award_flag;
74
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82
83 new_references.last_update_date := x_last_update_date;
84 new_references.last_updated_by := x_last_updated_by;
85 new_references.last_update_login := x_last_update_login;
86
87 END set_column_values;
88
89
90 PROCEDURE check_parent_existance AS
91 /*
92 || Created By : avenkatr
93 || Created On : 08-NOV-2000
94 || Purpose : Checks for the existance of Parent records.
95 || Known limitations, enhancements or remarks :
96 || Change History :
97 || Who When What
98 || veramach 06-Nov-2003 FA 125 - Added call for igf_aw_awd_dist_plans_pkg.get_pk_for_validation(adplans_id)
99 || adhawan 24-oct-2002 igf_aw_awd_frml_pkg.get_pk_for_validation removed
100 || (reverse chronological order - newest change first)
101 */
102 BEGIN
103
104 IF (((old_references.formula_code = new_references.formula_code) AND
105 (old_references.ci_cal_type = new_references.ci_cal_type) AND
106 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
107 ((new_references.formula_code IS NULL) OR
108 (new_references.ci_cal_type IS NULL) OR
109 (new_references.ci_sequence_number IS NULL))) THEN
110 NULL;
111 END IF;
112 IF (((old_references.fund_id = new_references.fund_id)) OR
113 ((new_references.fund_id IS NULL))) THEN
114 NULL;
115 ELSIF NOT igf_aw_fund_mast_pkg.get_pk_for_validation (
116 new_references.fund_id
117 ) THEN
118 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
119 igs_ge_msg_stack.add;
120 app_exception.raise_exception;
121 END IF;
122
123 IF ((old_references.adplans_id = new_references.adplans_id)) OR
124 ((new_references.adplans_id IS NULL)) THEN
125 NULL;
126 ELSIF NOT igf_aw_awd_dist_plans_pkg.get_pk_for_validation(new_references.adplans_id) THEN
127 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
128 igs_ge_msg_stack.add;
129 app_exception.raise_exception;
130 END IF;
131
132 END check_parent_existance;
133
134
135 FUNCTION get_pk_for_validation (
136 x_formula_code IN VARCHAR2,
137 x_ci_cal_type IN VARCHAR2,
138 x_ci_sequence_number IN NUMBER,
139 x_seq_no IN NUMBER
140 ) RETURN BOOLEAN AS
141 /*
142 || Created By : avenkatr
143 || Created On : 08-NOV-2000
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 igf_aw_awd_frml_det_all
153 WHERE formula_code = x_formula_code
154 AND ci_cal_type = x_ci_cal_type
155 AND ci_sequence_number = x_ci_sequence_number
156 AND seq_no = x_seq_no
157 FOR UPDATE NOWAIT;
158
159 lv_rowid cur_rowid%RowType;
160
161 BEGIN
162
163 OPEN cur_rowid;
164 FETCH cur_rowid INTO lv_rowid;
165 IF (cur_rowid%FOUND) THEN
166 CLOSE cur_rowid;
167 RETURN(TRUE);
168 ELSE
169 CLOSE cur_rowid;
170 RETURN(FALSE);
171 END IF;
172
173 END get_pk_for_validation;
174
175
176 PROCEDURE get_fk_igf_aw_fund_mast (
177 x_fund_id IN NUMBER
178 ) AS
179 /*
180 || Created By : avenkatr
181 || Created On : 08-NOV-2000
182 || Purpose : Validates the Foreign Keys for the table.
183 || Known limitations, enhancements or remarks :
184 || Change History :
185 || Who When What
186 || (reverse chronological order - newest change first)
187 */
188 CURSOR cur_rowid IS
189 SELECT rowid
190 FROM igf_aw_awd_frml_det_all
191 WHERE ((fund_id = x_fund_id));
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 ('IGF', 'IGF_AW_FMDET_FMAST_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_igf_aw_fund_mast;
209
210 PROCEDURE get_fk_igf_aw_awd_dist_plans(
211 x_adplans_id IN NUMBER
212 ) AS
213 ------------------------------------------------------------------
214 --Created by : veramach, Oracle India
215 --Date created: 06-NOV-2003
216 --
217 --Purpose:
218 --
219 --
220 --Known limitations/enhancements and/or remarks:
221 --
222 --Change History:
223 --Who When What
224 -------------------------------------------------------------------
225
226 CURSOR cur_rowid IS
227 SELECT rowid
228 FROM igf_aw_awd_frml_det_all
229 WHERE ((adplans_id = x_adplans_id));
230
231 lv_rowid cur_rowid%ROWTYPE;
232
233 BEGIN
234
235 OPEN cur_rowid;
236 FETCH cur_rowid INTO lv_rowid;
237 IF (cur_rowid%FOUND) THEN
238 CLOSE cur_rowid;
239 fnd_message.set_name ('IGF', 'IGF_AW_FMDET_ADPLANS_FK');
240 igs_ge_msg_stack.add;
241 app_exception.raise_exception;
242 RETURN;
243 END IF;
244 CLOSE cur_rowid;
245
246 END get_fk_igf_aw_awd_dist_plans;
247
248 PROCEDURE get_ufk_igf_aw_target_grp (
249 x_formula_code IN VARCHAR2,
250 x_ci_cal_type IN VARCHAR2,
251 x_ci_sequence_number IN NUMBER
252 ) AS
253 /*
254 || Created By : CDCRUZ
255 || Created On : 31-OCT-2002
256 || Purpose : Validates the Foreign Keys for the table.
257 || Known limitations, enhancements or remarks :
258 || Change History :
259 || Who When What
260 || (reverse chronological order - newest change first)
261 */
262 CURSOR cur_rowid IS
263 SELECT rowid
264 FROM igf_aw_awd_frml_det_all
265 WHERE (formula_code = x_formula_code and
266 ci_cal_type = x_ci_cal_type and
267 ci_sequence_number = x_ci_sequence_number);
268
269 lv_rowid cur_rowid%RowType;
270
271 BEGIN
272
273 OPEN cur_rowid;
274 FETCH cur_rowid INTO lv_rowid;
275 IF (cur_rowid%FOUND) THEN
276 CLOSE cur_rowid;
277 fnd_message.set_name ('FND', 'FND-CANNOT DELETE MASTER');
278 igs_ge_msg_stack.add;
279 app_exception.raise_exception;
280 RETURN;
281 END IF;
282 CLOSE cur_rowid;
283
284 END get_ufk_igf_aw_target_grp;
285
286
287 PROCEDURE before_dml (
288 p_action IN VARCHAR2,
289 x_rowid IN VARCHAR2 ,
290 x_formula_code IN VARCHAR2 ,
291 x_ci_cal_type IN VARCHAR2 ,
292 x_ci_sequence_number IN NUMBER ,
293 x_seq_no IN NUMBER ,
294 x_fund_id IN NUMBER ,
295 x_min_award_amt IN NUMBER ,
296 x_max_award_amt IN NUMBER ,
297 x_replace_fc IN VARCHAR2 ,
298 x_pe_group_id IN NUMBER ,
299 x_adplans_id IN NUMBER,
300 x_lock_award_flag IN VARCHAR2 ,
301 x_creation_date IN DATE ,
302 x_created_by IN NUMBER ,
303 x_last_update_date IN DATE ,
304 x_last_updated_by IN NUMBER ,
305 x_last_update_login IN NUMBER
306 ) AS
307 /*
308 || Created By : avenkatr
309 || Created On : 08-NOV-2000
310 || Purpose : Initialises the columns, Checks Constraints, Calls the
311 || Trigger Handlers for the table, before any DML operation.
312 || Known limitations, enhancements or remarks :
313 || Change History :
314 || Who When What
315 || (reverse chronological order - newest change first)
316 || veramach 06-NOV-2003 FA 125 Multiple distr methods
317 || Added new column adplans_id
318 */
319 BEGIN
320
321 set_column_values (
322 p_action,
323 x_rowid,
324 x_formula_code,
325 x_ci_cal_type,
326 x_ci_sequence_number,
327 x_seq_no,
328 x_fund_id,
329 x_min_award_amt,
330 x_max_award_amt,
331 x_replace_fc,
332 x_pe_group_id,
333 x_adplans_id,
334 x_lock_award_flag,
335 x_creation_date,
336 x_created_by,
337 x_last_update_date,
338 x_last_updated_by,
339 x_last_update_login
340 );
341
342 IF (p_action = 'INSERT') THEN
343 -- Call all the procedures related to Before Insert.
344 IF ( get_pk_for_validation(
345 new_references.formula_code,
346 new_references.ci_cal_type,
347 new_references.ci_sequence_number,
348 new_references.seq_no
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_parent_existance;
356 ELSIF (p_action = 'UPDATE') THEN
357 -- Call all the procedures related to Before Update.
358 check_parent_existance;
359 ELSIF (p_action = 'VALIDATE_INSERT') THEN
360 -- Call all the procedures related to Before Insert.
361 IF ( get_pk_for_validation (
362 new_references.formula_code,
363 new_references.ci_cal_type,
364 new_references.ci_sequence_number,
365 new_references.seq_no
366 )
367 ) THEN
368 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
369 igs_ge_msg_stack.add;
370 app_exception.raise_exception;
371 END IF;
372 END IF;
373
374 END before_dml;
375
376
377 PROCEDURE insert_row (
378 x_rowid IN OUT NOCOPY VARCHAR2,
379 x_formula_code IN VARCHAR2,
380 x_ci_cal_type IN VARCHAR2,
381 x_ci_sequence_number IN NUMBER,
382 x_seq_no IN NUMBER,
383 x_fund_id IN NUMBER,
384 x_min_award_amt IN NUMBER,
385 x_max_award_amt IN NUMBER,
386 x_replace_fc IN VARCHAR2,
387 x_pe_group_id IN NUMBER ,
388 x_mode IN VARCHAR2,
389 x_adplans_id IN NUMBER,
390 x_lock_award_flag IN VARCHAR2
391 ) AS
392 /*
393 || Created By : avenkatr
394 || Created On : 08-NOV-2000
395 || Purpose : Handles the INSERT DML logic for the table.
396 || Known limitations, enhancements or remarks :
397 || Change History :
398 || Who When What
399 || (reverse chronological order - newest change first)
400 || veramach 06-NOV-2003 FA 125 Multiple distr methods
401 || Added new column adplans_id
402 */
403 CURSOR c IS
404 SELECT rowid
405 FROM igf_aw_awd_frml_det_all
409 AND seq_no = x_seq_no;
406 WHERE formula_code = x_formula_code
407 AND ci_cal_type = x_ci_cal_type
408 AND ci_sequence_number = x_ci_sequence_number
410
411 x_last_update_date DATE;
412 x_last_updated_by NUMBER;
413 x_last_update_login NUMBER;
414 l_org_id igf_aw_awd_frml_det_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
415
416 BEGIN
417
418 x_last_update_date := SYSDATE;
419 IF (x_mode = 'I') THEN
420 x_last_updated_by := 1;
421 x_last_update_login := 0;
422 ELSIF (x_mode = 'R') THEN
423 x_last_updated_by := fnd_global.user_id;
424 IF (x_last_updated_by IS NULL) THEN
425 x_last_updated_by := -1;
426 END IF;
427 x_last_update_login := fnd_global.login_id;
428 IF (x_last_update_login IS NULL) THEN
429 x_last_update_login := -1;
430 END IF;
431 ELSE
432 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
433 igs_ge_msg_stack.add;
434 app_exception.raise_exception;
435 END IF;
436
437 before_dml(
438 p_action => 'INSERT',
439 x_rowid => x_rowid,
440 x_formula_code => x_formula_code,
441 x_ci_cal_type => x_ci_cal_type,
442 x_ci_sequence_number => x_ci_sequence_number,
443 x_seq_no => x_seq_no,
444 x_fund_id => x_fund_id,
445 x_min_award_amt => x_min_award_amt,
446 x_max_award_amt => x_max_award_amt,
447 x_replace_fc => x_replace_fc,
448 x_pe_group_id => x_pe_group_id,
449 x_adplans_id => x_adplans_id,
450 x_lock_award_flag => x_lock_award_flag,
451 x_creation_date => x_last_update_date,
452 x_created_by => x_last_updated_by,
453 x_last_update_date => x_last_update_date,
454 x_last_updated_by => x_last_updated_by,
455 x_last_update_login => x_last_update_login
456 );
457
458 INSERT INTO igf_aw_awd_frml_det_all (
459 formula_code,
460 ci_cal_type,
461 ci_sequence_number,
462 seq_no,
463 fund_id,
464 min_award_amt,
465 max_award_amt,
466 replace_fc,
467 pe_group_id,
468 adplans_id,
469 lock_award_flag,
470 creation_date,
471 created_by,
472 last_update_date,
473 last_updated_by,
474 last_update_login,
475 org_id
476 ) VALUES (
477 new_references.formula_code,
478 new_references.ci_cal_type,
479 new_references.ci_sequence_number,
480 new_references.seq_no,
481 new_references.fund_id,
482 new_references.min_award_amt,
483 new_references.max_award_amt,
484 new_references.replace_fc,
485 new_references.pe_group_id,
486 new_references.adplans_id,
487 new_references.lock_award_flag,
488 x_last_update_date,
489 x_last_updated_by,
490 x_last_update_date,
491 x_last_updated_by,
492 x_last_update_login,
493 l_org_id
494 );
495
496 OPEN c;
497 FETCH c INTO x_rowid;
498 IF (c%NOTFOUND) THEN
499 CLOSE c;
500 RAISE NO_DATA_FOUND;
501 END IF;
502 CLOSE c;
503
504 END insert_row;
505
506
507 PROCEDURE lock_row (
508 x_rowid IN VARCHAR2,
509 x_formula_code IN VARCHAR2,
510 x_ci_cal_type IN VARCHAR2,
511 x_ci_sequence_number IN NUMBER,
512 x_seq_no IN NUMBER,
513 x_fund_id IN NUMBER,
514 x_min_award_amt IN NUMBER,
515 x_max_award_amt IN NUMBER,
516 x_replace_fc IN VARCHAR2,
517 x_pe_group_id IN NUMBER,
518 x_adplans_id IN NUMBER,
519 x_lock_award_flag IN VARCHAR2
520 ) AS
521 /*
522 || Created By : avenkatr
523 || Created On : 08-NOV-2000
524 || Purpose : Handles the LOCK mechanism for the table.
525 || Known limitations, enhancements or remarks :
526 || Change History :
527 || Who When What
528 || (reverse chronological order - newest change first)
529 || veramach 06-NOV-2003 FA 125 Multiple distr methods
530 || Added new column adplans_id
531 */
532 CURSOR c1 IS
533 SELECT
534 fund_id,
535 min_award_amt,
536 max_award_amt,
537 replace_fc,
538 adplans_id,
539 lock_award_flag
540 FROM igf_aw_awd_frml_det_all
544 tlinfo c1%ROWTYPE;
541 WHERE rowid = x_rowid
542 FOR UPDATE NOWAIT;
543
545
546 BEGIN
547
548 OPEN c1;
549 FETCH c1 INTO tlinfo;
550 IF (c1%notfound) THEN
551 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
552 igs_ge_msg_stack.add;
553 CLOSE c1;
554 app_exception.raise_exception;
555 RETURN;
556 END IF;
557 CLOSE c1;
558
559 IF (
560 (tlinfo.fund_id = x_fund_id)
561 AND ((tlinfo.min_award_amt = x_min_award_amt) OR ((tlinfo.min_award_amt IS NULL) AND (X_min_award_amt IS NULL)))
562 AND ((tlinfo.max_award_amt = x_max_award_amt) OR ((tlinfo.max_award_amt IS NULL) AND (X_max_award_amt IS NULL)))
563 AND ((tlinfo.replace_fc = x_replace_fc) OR ((tlinfo.replace_fc IS NULL) AND (X_replace_fc IS NULL)))
564 AND ((tlinfo.adplans_id = x_adplans_id) OR ((tlinfo.adplans_id IS NULL) AND (x_adplans_id IS NULL)))
565 AND ((tlinfo.lock_award_flag = x_lock_award_flag) OR (tlinfo.lock_award_flag IS NULL)) ) THEN
566 NULL;
567 ELSE
568 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
569 igs_ge_msg_stack.add;
570 app_exception.raise_exception;
571 END IF;
572
573 RETURN;
574
575 END lock_row;
576
577
578 PROCEDURE update_row (
579 x_rowid IN VARCHAR2,
580 x_formula_code IN VARCHAR2,
581 x_ci_cal_type IN VARCHAR2,
582 x_ci_sequence_number IN NUMBER,
583 x_seq_no IN NUMBER,
584 x_fund_id IN NUMBER,
585 x_min_award_amt IN NUMBER,
586 x_max_award_amt IN NUMBER,
587 x_replace_fc IN VARCHAR2,
588 x_pe_group_id IN NUMBER ,
589 x_mode IN VARCHAR2,
590 x_adplans_id IN NUMBER,
591 x_lock_award_flag IN VARCHAR2
592 ) AS
593 /*
594 || Created By : avenkatr
595 || Created On : 08-NOV-2000
596 || Purpose : Handles the UPDATE DML logic for the table.
597 || Known limitations, enhancements or remarks :
598 || Change History :
599 || Who When What
600 || (reverse chronological order - newest change first)
601 || veramach 06-NOV-2003 FA 125 Multiple distr methods
602 || Added new column adplans_id
603 */
604 x_last_update_date DATE ;
605 x_last_updated_by NUMBER;
606 x_last_update_login NUMBER;
607
608 BEGIN
609
610 x_last_update_date := SYSDATE;
611 IF (X_MODE = 'I') THEN
612 x_last_updated_by := 1;
613 x_last_update_login := 0;
614 ELSIF (x_mode = 'R') THEN
615 x_last_updated_by := fnd_global.user_id;
616 IF x_last_updated_by IS NULL THEN
617 x_last_updated_by := -1;
618 END IF;
619 x_last_update_login := fnd_global.login_id;
620 IF (x_last_update_login IS NULL) THEN
621 x_last_update_login := -1;
622 END IF;
623 ELSE
624 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
625 igs_ge_msg_stack.add;
626 app_exception.raise_exception;
627 END IF;
628
629 before_dml(
630 p_action => 'UPDATE',
631 x_rowid => x_rowid,
632 x_formula_code => x_formula_code,
633 x_ci_cal_type => x_ci_cal_type,
634 x_ci_sequence_number => x_ci_sequence_number,
635 x_seq_no => x_seq_no,
636 x_fund_id => x_fund_id,
637 x_min_award_amt => x_min_award_amt,
638 x_max_award_amt => x_max_award_amt,
639 x_replace_fc => x_replace_fc,
640 x_pe_group_id => x_pe_group_id ,
641 x_adplans_id => x_adplans_id,
642 x_lock_award_flag => x_lock_award_flag,
643 x_creation_date => x_last_update_date,
644 x_created_by => x_last_updated_by,
645 x_last_update_date => x_last_update_date,
646 x_last_updated_by => x_last_updated_by,
647 x_last_update_login => x_last_update_login
648 );
649
650 UPDATE igf_aw_awd_frml_det_all
651 SET
652 seq_no = new_references.seq_no,
653 fund_id = new_references.fund_id,
654 min_award_amt = new_references.min_award_amt,
655 max_award_amt = new_references.max_award_amt,
656 replace_fc = new_references.replace_fc,
657 pe_group_id = new_references.pe_group_id,
658 adplans_id = new_references.adplans_id,
659 lock_award_flag = new_references.lock_award_flag,
660 last_update_date = x_last_update_date,
661 last_updated_by = x_last_updated_by,
662 last_update_login = x_last_update_login
663 WHERE rowid = x_rowid;
664
665 IF (SQL%NOTFOUND) THEN
666 RAISE NO_DATA_FOUND;
667 END IF;
668
669 END update_row;
670
671
672 PROCEDURE add_row (
673 x_rowid IN OUT NOCOPY VARCHAR2,
674 x_formula_code IN VARCHAR2,
675 x_ci_cal_type IN VARCHAR2,
676 x_ci_sequence_number IN NUMBER,
677 x_seq_no IN NUMBER,
678 x_fund_id IN NUMBER,
679 x_min_award_amt IN NUMBER,
680 x_max_award_amt IN NUMBER,
681 x_replace_fc IN VARCHAR2,
682 x_pe_group_id IN NUMBER ,
683 x_mode IN VARCHAR2,
684 x_adplans_id IN NUMBER,
685 x_lock_award_flag IN VARCHAR2
686 ) AS
687 /*
688 || Created By : avenkatr
689 || Created On : 08-NOV-2000
690 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
691 || Known limitations, enhancements or remarks :
692 || Change History :
693 || Who When What
694 || (reverse chronological order - newest change first)
695 || veramach 06-NOV-2003 FA 125 Multiple distr methods
696 || Added new column adplans_id
697 */
698 CURSOR c1 IS
699 SELECT rowid
700 FROM igf_aw_awd_frml_det_all
701 WHERE formula_code = x_formula_code
702 AND ci_cal_type = x_ci_cal_type
703 AND ci_sequence_number = x_ci_sequence_number
704 AND seq_no = x_seq_no;
705
706 BEGIN
707
708 OPEN c1;
709 FETCH c1 INTO x_rowid;
710 IF (c1%NOTFOUND) THEN
711 CLOSE c1;
712
713 insert_row (
714 x_rowid,
715 x_formula_code,
716 x_ci_cal_type,
717 x_ci_sequence_number,
718 x_seq_no,
719 x_fund_id,
720 x_min_award_amt,
721 x_max_award_amt,
722 x_replace_fc,
723 x_pe_group_id,
724 x_mode,
725 x_adplans_id,
726 x_lock_award_flag
727 );
728 RETURN;
729 END IF;
730 CLOSE c1;
731
732 update_row (
733 x_rowid,
734 x_formula_code,
735 x_ci_cal_type,
736 x_ci_sequence_number,
737 x_seq_no,
738 x_fund_id,
739 x_min_award_amt,
740 x_max_award_amt,
741 x_replace_fc,
742 x_pe_group_id,
743 x_mode,
744 x_adplans_id,
745 x_lock_award_flag
746 );
747
748 END add_row;
749
750
751 PROCEDURE delete_row (
752 x_rowid IN VARCHAR2
753 ) AS
754 /*
755 || Created By : avenkatr
756 || Created On : 08-NOV-2000
757 || Purpose : Handles the DELETE DML logic for the table.
758 || Known limitations, enhancements or remarks :
759 || Change History :
760 || Who When What
761 || (reverse chronological order - newest change first)
762 */
763 BEGIN
764
765 before_dml (
766 p_action => 'DELETE',
767 x_rowid => x_rowid
768 );
769
770 DELETE FROM igf_aw_awd_frml_det_all
771 WHERE rowid = x_rowid;
772
773 IF (SQL%NOTFOUND) THEN
774 RAISE NO_DATA_FOUND;
775 END IF;
776
777 END delete_row;
778
779 END igf_aw_awd_frml_det_pkg;