[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_TARGET_GRP_PKG
Source
1 PACKAGE BODY igf_aw_target_grp_pkg AS
2 /* $Header: IGFWI07B.pls 120.1 2005/09/01 00:10:29 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_target_grp_all%ROWTYPE;
6 new_references igf_aw_target_grp_all%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 ,
11 x_group_cd IN VARCHAR2 ,
12 x_description IN VARCHAR2 ,
13 x_max_grant_amt IN NUMBER ,
14 x_max_grant_perct IN NUMBER ,
15 x_max_grant_perct_fact IN VARCHAR2 ,
16 x_max_loan_amt IN NUMBER ,
17 x_max_loan_perct IN NUMBER ,
18 x_max_loan_perct_fact IN VARCHAR2 ,
19 x_max_work_amt IN NUMBER ,
20 x_max_work_perct IN NUMBER ,
21 x_max_work_perct_fact IN VARCHAR2 ,
22 x_max_shelp_amt IN NUMBER ,
23 x_max_shelp_perct IN NUMBER ,
24 x_max_shelp_perct_fact IN VARCHAR2 ,
25 x_max_gap_amt IN NUMBER ,
26 x_max_gap_perct IN NUMBER ,
27 x_max_gap_perct_fact IN VARCHAR2 ,
28 x_use_fixed_costs IN VARCHAR2 ,
29 x_max_aid_pkg IN NUMBER ,
30 x_max_gift_amt IN NUMBER ,
31 x_max_gift_perct IN NUMBER ,
32 x_max_gift_perct_fact IN VARCHAR2 ,
33 x_max_schlrshp_amt IN NUMBER ,
34 x_max_schlrshp_perct IN NUMBER ,
35 x_max_schlrshp_perct_fact IN VARCHAR2 ,
36 x_cal_type IN VARCHAR2 ,
37 x_sequence_number IN NUMBER ,
38 x_rule_order IN NUMBER ,
39 x_s_rule_call_cd IN VARCHAR2 ,
40 x_rul_sequence_number IN NUMBER ,
41 x_active IN VARCHAR2 ,
42 x_tgrp_id IN NUMBER ,
43 x_adplans_id IN NUMBER,
44 x_creation_date IN DATE ,
45 x_created_by IN NUMBER ,
46 x_last_update_date IN DATE ,
47 x_last_updated_by IN NUMBER ,
48 x_last_update_login IN NUMBER
49 ) AS
50 /*
51 || Created By : prchandr
52 || Created On : 13-JUL-2001
53 || Purpose : Initialises the Old and New references for the columns of the table.
54 || Known limitations, enhancements or remarks :
55 || Change History :
56 || Who When What
57 || (reverse chronological order - newest change first)
58 || veramach 06-NOV-2003 FA 125 Multiple distr methods
59 || Added new column adplans_id
60 */
61
62 CURSOR cur_old_ref_values IS
63 SELECT *
64 FROM IGF_AW_TARGET_GRP_ALL
65 WHERE rowid = x_rowid;
66
67 BEGIN
68
69 l_rowid := x_rowid;
70
71 -- Code for setting the Old and New Reference Values.
72 -- Populate Old Values.
73 OPEN cur_old_ref_values;
74 FETCH cur_old_ref_values INTO old_references;
75 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
76 CLOSE cur_old_ref_values;
77 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
78 igs_ge_msg_stack.add;
79 app_exception.raise_exception;
80 RETURN;
81 END IF;
82 CLOSE cur_old_ref_values;
83
84 -- Populate New Values.
85 new_references.group_cd := x_group_cd;
86 new_references.description := x_description;
87 new_references.max_grant_amt := x_max_grant_amt;
88 new_references.max_grant_perct := x_max_grant_perct;
89 new_references.max_grant_perct_fact := x_max_grant_perct_fact;
90 new_references.max_loan_amt := x_max_loan_amt;
91 new_references.max_loan_perct := x_max_loan_perct;
92 new_references.max_loan_perct_fact := x_max_loan_perct_fact;
93 new_references.max_work_amt := x_max_work_amt;
94 new_references.max_work_perct := x_max_work_perct;
95 new_references.max_work_perct_fact := x_max_work_perct_fact;
96 new_references.max_shelp_amt := x_max_shelp_amt;
97 new_references.max_shelp_perct := x_max_shelp_perct;
98 new_references.max_shelp_perct_fact := x_max_shelp_perct_fact;
99 new_references.max_gap_amt := x_max_gap_amt;
100 new_references.max_gap_perct := x_max_gap_perct;
101 new_references.max_gap_perct_fact := x_max_gap_perct_fact;
102 new_references.use_fixed_costs := x_use_fixed_costs;
103 new_references.max_aid_pkg := x_max_aid_pkg;
104 new_references.max_gift_amt := x_max_gift_amt;
105 new_references.max_gift_perct := x_max_gift_perct;
106 new_references.max_gift_perct_fact := x_max_gift_perct_fact;
107 new_references.max_schlrshp_amt := x_max_schlrshp_amt;
108 new_references.max_schlrshp_perct := x_max_schlrshp_perct;
109 new_references.max_schlrshp_perct_fact := x_max_schlrshp_perct_fact;
110 new_references.cal_type := x_cal_type;
111 new_references.sequence_number := x_sequence_number;
112 new_references.rule_order := x_rule_order;
113 new_references.s_rule_call_cd := x_s_rule_call_cd;
114 new_references.rul_sequence_number := x_rul_sequence_number;
115 new_references.active := x_active;
116 new_references.tgrp_id := x_tgrp_id;
117 new_references.adplans_id := x_adplans_id;
118
119 IF (p_action = 'UPDATE') THEN
120 new_references.creation_date := old_references.creation_date;
121 new_references.created_by := old_references.created_by;
122 ELSE
123 new_references.creation_date := x_creation_date;
124 new_references.created_by := x_created_by;
125 END IF;
126
127 new_references.last_update_date := x_last_update_date;
128 new_references.last_updated_by := x_last_updated_by;
129 new_references.last_update_login := x_last_update_login;
130
131 END set_column_values;
132
133 PROCEDURE check_parent_existance AS
134 ------------------------------------------------------------------
135 --Created by : veramach, Oracle India
136 --Date created:
137 --
138 --Purpose:
139 --
140 --
141 --Known limitations/enhancements and/or remarks:
142 --
143 --Change History:
144 --Who When What
145 -------------------------------------------------------------------
146
147 BEGIN
148 IF ((old_references.adplans_id = new_references.adplans_id)) OR
149 ((new_references.adplans_id IS NULL)) THEN
150 NULL;
151 ELSIF NOT igf_aw_awd_dist_plans_pkg.get_pk_for_validation(new_references.adplans_id) THEN
152 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
153 igs_ge_msg_stack.add;
154 app_exception.raise_exception;
155 END IF;
156 END check_parent_existance;
157
158 PROCEDURE check_uniqueness AS
159 /*
160 || Created By : prchandr
161 || Created On : 13-JUL-2001
162 || Purpose : Handles the Unique Constraint logic defined for the columns.
163 || Known limitations, enhancements or remarks :
164 || Change History :
165 || Who When What
166 || (reverse chronological order - newest change first)
167 */
168 BEGIN
169
170 IF ( get_uk_for_validation (
171 new_references.group_cd,
172 new_references.cal_type,
173 new_references.sequence_number,
174 new_references.org_id
175 )
176 ) THEN
177 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
178 igs_ge_msg_stack.add;
179 app_exception.raise_exception;
180 END IF;
181
182 END check_uniqueness;
183
184
185 PROCEDURE check_child_existance IS
186 /*
187 || Created By : prchandr
188 || Created On : 13-JUL-2001
189 || Purpose : Checks for the existance of Child records.
190 || Known limitations, enhancements or remarks :
191 || Change History :
192 || Who When What
193 || adhawan 24-oct-2002 igf_aw_awd_frml_pkg.get_ufk_igf_aw_target_grp removed
194 || (reverse chronological order - newest change first)
195 */
196 BEGIN
197
198 igf_aw_awd_frml_det_pkg.get_ufk_igf_aw_target_grp (
199 old_references.group_cd,
200 old_references.cal_type,
201 old_references.sequence_number
202 );
203
204 END check_child_existance;
205
206 FUNCTION get_pk_for_validation (
207 x_tgrp_id IN NUMBER
208 ) RETURN BOOLEAN AS
209 /*
210 || Created By : prchandr
211 || Created On : 13-JUL-2001
212 || Purpose : Validates the Primary Key of the table.
213 || Known limitations, enhancements or remarks :
214 || Change History :
215 || Who When What
216 || (reverse chronological order - newest change first)
217 */
218 CURSOR cur_rowid IS
219 SELECT rowid
220 FROM igf_aw_target_grp_all
221 WHERE tgrp_id = x_tgrp_id
222 FOR UPDATE NOWAIT;
223
224 lv_rowid cur_rowid%RowType;
225
226 BEGIN
227
228 OPEN cur_rowid;
229 FETCH cur_rowid INTO lv_rowid;
230 IF (cur_rowid%FOUND) THEN
231 CLOSE cur_rowid;
232 RETURN(TRUE);
233 ELSE
234 CLOSE cur_rowid;
235 RETURN(FALSE);
236 END IF;
237
238 END get_pk_for_validation;
239
240
241 FUNCTION get_uk_for_validation (
242 x_group_cd IN VARCHAR2,
243 x_cal_type IN VARCHAR2,
244 x_sequence_number IN NUMBER,
245 x_org_id IN NUMBER
246 ) RETURN BOOLEAN AS
247 /*
248 || Created By : prchandr
249 || Created On : 13-JUL-2001
250 || Purpose : Validates the Unique Keys of the table.
251 || Known limitations, enhancements or remarks :
252 || Change History : 2166848
253 || Who When What
254 || adhawan 23-feb-02' made the UNIQUE key incasesensitive by adding the UPPER clause
255 || (reverse chronological order - newest change first)
256 */
257 -- Change History:
258 -- Who When What
259 -- ridas 31-Aug-2005 Removed the UPPER function from parameter x_group_cd in Cursor 'cur_rowid'.
260
261 CURSOR cur_rowid IS
262 SELECT rowid
263 FROM igf_aw_target_grp
264 WHERE group_cd = x_group_cd
265 AND cal_type = x_cal_type
266 AND sequence_number = x_sequence_number
267 AND ((l_rowid IS NULL) OR (rowid <> l_rowid))
268 FOR UPDATE NOWAIT;
269
270 lv_rowid cur_rowid%RowType;
271
272 BEGIN
273
274 OPEN cur_rowid;
275 FETCH cur_rowid INTO lv_rowid;
276 IF (cur_rowid%FOUND) THEN
277 CLOSE cur_rowid;
278 RETURN (true);
279 ELSE
280 CLOSE cur_rowid;
281 RETURN(FALSE);
282 END IF;
283
284 END get_uk_for_validation ;
285
286
287 PROCEDURE before_dml (
288 p_action IN VARCHAR2,
289 x_rowid IN VARCHAR2 ,
290 x_group_cd IN VARCHAR2 ,
291 x_description IN VARCHAR2 ,
292 x_max_grant_amt IN NUMBER ,
293 x_max_grant_perct IN NUMBER ,
294 x_max_grant_perct_fact IN VARCHAR2 ,
295 x_max_loan_amt IN NUMBER ,
296 x_max_loan_perct IN NUMBER ,
297 x_max_loan_perct_fact IN VARCHAR2 ,
298 x_max_work_amt IN NUMBER ,
299 x_max_work_perct IN NUMBER ,
300 x_max_work_perct_fact IN VARCHAR2 ,
301 x_max_shelp_amt IN NUMBER ,
302 x_max_shelp_perct IN NUMBER ,
303 x_max_shelp_perct_fact IN VARCHAR2 ,
304 x_max_gap_amt IN NUMBER ,
305 x_max_gap_perct IN NUMBER ,
306 x_max_gap_perct_fact IN VARCHAR2 ,
307 x_use_fixed_costs IN VARCHAR2 ,
308 x_max_aid_pkg IN NUMBER ,
309 x_max_gift_amt IN NUMBER ,
310 x_max_gift_perct IN NUMBER ,
311 x_max_gift_perct_fact IN VARCHAR2 ,
312 x_max_schlrshp_amt IN NUMBER ,
313 x_max_schlrshp_perct IN NUMBER ,
314 x_max_schlrshp_perct_fact IN VARCHAR2 ,
315 x_cal_type IN VARCHAR2 ,
316 x_sequence_number IN NUMBER ,
317 x_rule_order IN NUMBER ,
318 x_s_rule_call_cd IN VARCHAR2 ,
319 x_rul_sequence_number IN NUMBER ,
320 x_active IN VARCHAR2 ,
321 x_tgrp_id IN NUMBER ,
322 x_adplans_id IN NUMBER,
323 x_creation_date IN DATE ,
324 x_created_by IN NUMBER ,
325 x_last_update_date IN DATE ,
326 x_last_updated_by IN NUMBER ,
327 x_last_update_login IN NUMBER
328 ) AS
329 /*
330 || Created By : prchandr
331 || Created On : 13-JUL-2001
332 || Purpose : Initialises the columns, Checks Constraints, Calls the
333 || Trigger Handlers for the table, before any DML operation.
334 || Known limitations, enhancements or remarks :
335 || Change History :
336 || Who When What
337 || (reverse chronological order - newest change first)
341 || from the Check for the UPDATE and VALIDATE_UPDATE
338 || veramach 06-NOV-2003 FA 125 Multiple distr methods
339 || Added new column adplans_id
340 || kpadiyar 11-SEP-2001 Removed the call to check_uk_child_existance
342 || call which is not required.
343 || Bug No # 1978576 - WRONG ERROR IS PROMPTING OUT NOCOPY
344 */
345 BEGIN
346
347 set_column_values (
348 p_action,
349 x_rowid,
350 x_group_cd,
351 x_description,
352 x_max_grant_amt,
353 x_max_grant_perct,
354 x_max_grant_perct_fact,
355 x_max_loan_amt,
356 x_max_loan_perct,
357 x_max_loan_perct_fact,
358 x_max_work_amt,
359 x_max_work_perct,
360 x_max_work_perct_fact,
361 x_max_shelp_amt,
362 x_max_shelp_perct,
363 x_max_shelp_perct_fact,
364 x_max_gap_amt,
365 x_max_gap_perct,
366 x_max_gap_perct_fact,
367 x_use_fixed_costs,
368 x_max_aid_pkg,
369 x_max_gift_amt,
370 x_max_gift_perct ,
371 x_max_gift_perct_fact,
372 x_max_schlrshp_amt,
373 x_max_schlrshp_perct ,
374 x_max_schlrshp_perct_fact,
375 x_cal_type,
376 x_sequence_number,
377 x_rule_order,
378 x_s_rule_call_cd,
379 x_rul_sequence_number,
380 x_active,
381 x_tgrp_id,
382 x_adplans_id,
383 x_creation_date,
384 x_created_by,
385 x_last_update_date,
386 x_last_updated_by,
387 x_last_update_login
388 );
389
390 IF (p_action = 'INSERT') THEN
391 -- Call all the procedures related to Before Insert.
392 IF ( get_pk_for_validation(
393 new_references.tgrp_id
394 )
395 ) THEN
396 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
397 igs_ge_msg_stack.add;
398 app_exception.raise_exception;
399 END IF;
400 check_uniqueness;
401 check_parent_existance;
402 ELSIF (p_action = 'UPDATE') THEN
403 -- Call all the procedures related to Before Update.
404 check_uniqueness;
405 check_parent_existance;
406 ELSIF (p_action = 'DELETE') THEN
407 -- Call all the procedures related to Before Delete.
408 check_child_existance;
409 ELSIF (p_action = 'VALIDATE_INSERT') THEN
410 -- Call all the procedures related to Before Insert.
411 IF ( get_pk_for_validation (
412 new_references.tgrp_id
413 )
414 ) THEN
415 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
416 igs_ge_msg_stack.add;
417 app_exception.raise_exception;
418 END IF;
419 check_uniqueness;
420 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
421 check_uniqueness;
422 ELSIF (p_action = 'VALIDATE_DELETE') THEN
423 check_child_existance;
424 END IF;
425
426 END before_dml;
427
428 PROCEDURE get_fk_igf_aw_awd_dist_plans(
429 x_adplans_id IN NUMBER
430 ) AS
431 ------------------------------------------------------------------
432 --Created by : veramach, Oracle India
433 --Date created: 06-NOV-2003
434 --
435 --Purpose:
436 --
437 --
438 --Known limitations/enhancements and/or remarks:
439 --
440 --Change History:
441 --Who When What
442 -------------------------------------------------------------------
443
444 CURSOR cur_rowid IS
445 SELECT rowid
446 FROM igf_aw_awd_frml_det_all
447 WHERE ((adplans_id = x_adplans_id));
448
449 lv_rowid cur_rowid%ROWTYPE;
450
451 BEGIN
452
453 OPEN cur_rowid;
454 FETCH cur_rowid INTO lv_rowid;
455 IF (cur_rowid%FOUND) THEN
456 CLOSE cur_rowid;
457 fnd_message.set_name ('IGF', 'IGF_AW_TGRP_ADPLANS_FK');
458 igs_ge_msg_stack.add;
459 app_exception.raise_exception;
460 RETURN;
461 END IF;
462 CLOSE cur_rowid;
463
464 END get_fk_igf_aw_awd_dist_plans;
465
466 PROCEDURE insert_row (
467 x_rowid IN OUT NOCOPY VARCHAR2,
468 x_group_cd IN VARCHAR2,
469 x_description IN VARCHAR2,
470 x_max_grant_amt IN NUMBER,
471 x_max_grant_perct IN NUMBER,
472 x_max_grant_perct_fact IN VARCHAR2,
473 x_max_loan_amt IN NUMBER,
474 x_max_loan_perct IN NUMBER,
475 x_max_loan_perct_fact IN VARCHAR2,
476 x_max_work_amt IN NUMBER,
477 x_max_work_perct IN NUMBER,
478 x_max_work_perct_fact IN VARCHAR2,
479 x_max_shelp_amt IN NUMBER,
480 x_max_shelp_perct IN NUMBER,
481 x_max_shelp_perct_fact IN VARCHAR2,
482 x_max_gap_amt IN NUMBER,
483 x_max_gap_perct IN NUMBER,
487 x_max_gift_amt IN NUMBER,
484 x_max_gap_perct_fact IN VARCHAR2,
485 x_use_fixed_costs IN VARCHAR2,
486 x_max_aid_pkg IN NUMBER,
488 x_max_gift_perct IN NUMBER,
489 x_max_gift_perct_fact IN VARCHAR2,
490 x_max_schlrshp_amt IN NUMBER,
491 x_max_schlrshp_perct IN NUMBER,
492 x_max_schlrshp_perct_fact IN VARCHAR2,
493 x_cal_type IN VARCHAR2 ,
494 x_sequence_number IN NUMBER ,
495 x_rule_order IN NUMBER,
496 x_s_rule_call_cd IN VARCHAR2,
497 x_rul_sequence_number IN NUMBER,
498 x_active IN VARCHAR2,
499 x_tgrp_id IN OUT NOCOPY NUMBER,
500 x_mode IN VARCHAR2,
501 x_adplans_id IN NUMBER
502 ) AS
503 /*
504 || Created By : prchandr
505 || Created On : 13-JUL-2001
506 || Purpose : Handles the INSERT DML logic for the table.
507 || Known limitations, enhancements or remarks :
508 || Change History :
509 || Who When What
510 || (reverse chronological order - newest change first)
511 || veramach 06-NOV-2003 FA 125 Multiple distr methods
512 || Added new column adplans_id
513 */
514 CURSOR c IS
515 SELECT rowid
516 FROM igf_aw_target_grp_all
517 WHERE tgrp_id = x_tgrp_id;
518
519 x_last_update_date DATE;
520 x_last_updated_by NUMBER;
521 x_last_update_login NUMBER;
522
523 BEGIN
524
525 x_last_update_date := SYSDATE;
526 IF (x_mode = 'I') THEN
527 x_last_updated_by := 1;
528 x_last_update_login := 0;
529 ELSIF (x_mode = 'R') THEN
530 x_last_updated_by := fnd_global.user_id;
531 IF (x_last_updated_by IS NULL) THEN
532 x_last_updated_by := -1;
533 END IF;
534 x_last_update_login := fnd_global.login_id;
535 IF (x_last_update_login IS NULL) THEN
536 x_last_update_login := -1;
537 END IF;
538 ELSE
539 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
540 igs_ge_msg_stack.add;
541 app_exception.raise_exception;
542 END IF;
543
544 SELECT igf_aw_target_grp_all_s.NEXTVAL
545 INTO x_tgrp_id
546 FROM dual;
547
548 new_references.org_id := igs_ge_gen_003.get_org_id;
549
550 before_dml(
551 p_action => 'INSERT',
552 x_rowid => x_rowid,
553 x_group_cd => x_group_cd,
554 x_description => x_description,
555 x_max_grant_amt => x_max_grant_amt,
556 x_max_grant_perct => x_max_grant_perct,
557 x_max_grant_perct_fact => x_max_grant_perct_fact,
558 x_max_loan_amt => x_max_loan_amt,
559 x_max_loan_perct => x_max_loan_perct,
560 x_max_loan_perct_fact => x_max_loan_perct_fact,
561 x_max_work_amt => x_max_work_amt,
562 x_max_work_perct => x_max_work_perct,
563 x_max_work_perct_fact => x_max_work_perct_fact,
564 x_max_shelp_amt => x_max_shelp_amt,
565 x_max_shelp_perct => x_max_shelp_perct,
566 x_max_shelp_perct_fact => x_max_shelp_perct_fact,
567 x_max_gap_amt => x_max_gap_amt,
568 x_max_gap_perct => x_max_gap_perct,
569 x_max_gap_perct_fact => x_max_gap_perct_fact,
570 x_use_fixed_costs => x_use_fixed_costs,
571 x_max_aid_pkg => x_max_aid_pkg,
572 x_max_gift_amt => x_max_gift_amt,
573 x_max_gift_perct => x_max_gift_perct,
574 x_max_gift_perct_fact => x_max_gift_perct_fact,
575 x_max_schlrshp_amt => x_max_schlrshp_amt,
576 x_max_schlrshp_perct => x_max_schlrshp_perct,
577 x_max_schlrshp_perct_fact => x_max_schlrshp_perct_fact,
578 x_cal_type => x_cal_type,
579 x_sequence_number => x_sequence_number,
580 x_rule_order => x_rule_order,
581 x_s_rule_call_cd => x_s_rule_call_cd,
582 x_rul_sequence_number => x_rul_sequence_number,
583 x_active => x_active,
584 x_tgrp_id => x_tgrp_id,
585 x_adplans_id => x_adplans_id,
586 x_creation_date => x_last_update_date,
587 x_created_by => x_last_updated_by,
588 x_last_update_date => x_last_update_date,
589 x_last_updated_by => x_last_updated_by,
590 x_last_update_login => x_last_update_login
591 );
592
593 INSERT INTO igf_aw_target_grp_all (
594 group_cd,
595 description,
599 max_loan_amt,
596 max_grant_amt,
597 max_grant_perct,
598 max_grant_perct_fact,
600 max_loan_perct,
601 max_loan_perct_fact,
602 max_work_amt,
603 max_work_perct,
604 max_work_perct_fact,
605 max_shelp_amt,
606 max_shelp_perct,
607 max_shelp_perct_fact,
608 max_gap_amt,
609 max_gap_perct,
610 max_gap_perct_fact,
611 use_fixed_costs,
612 max_aid_pkg,
613 max_gift_amt,
614 max_gift_perct,
615 max_gift_perct_fact,
616 max_schlrshp_amt,
617 max_schlrshp_perct,
618 max_schlrshp_perct_fact,
619 cal_type,
620 sequence_number,
621 rule_order,
622 s_rule_call_cd,
623 rul_sequence_number,
624 active,
625 org_id,
626 tgrp_id,
627 adplans_id,
628 creation_date,
629 created_by,
630 last_update_date,
631 last_updated_by,
632 last_update_login
633 ) VALUES (
634 new_references.group_cd,
635 new_references.description,
636 new_references.max_grant_amt,
637 new_references.max_grant_perct,
638 new_references.max_grant_perct_fact,
639 new_references.max_loan_amt,
640 new_references.max_loan_perct,
641 new_references.max_loan_perct_fact,
642 new_references.max_work_amt,
643 new_references.max_work_perct,
644 new_references.max_work_perct_fact,
645 new_references.max_shelp_amt,
646 new_references.max_shelp_perct,
647 new_references.max_shelp_perct_fact,
648 new_references.max_gap_amt,
649 new_references.max_gap_perct,
650 new_references.max_gap_perct_fact,
651 new_references.use_fixed_costs,
652 new_references.max_aid_pkg,
653 new_references.max_gift_amt,
654 new_references.max_gift_perct,
655 new_references.max_gift_perct_fact,
656 new_references.max_schlrshp_amt,
657 new_references.max_schlrshp_perct,
658 new_references.max_schlrshp_perct_fact,
659 new_references.cal_type,
660 new_references.sequence_number,
661 new_references.rule_order,
662 new_references.s_rule_call_cd,
663 new_references.rul_sequence_number,
664 new_references.active,
665 new_references.org_id,
666 new_references.tgrp_id,
667 new_references.adplans_id,
668 x_last_update_date,
669 x_last_updated_by,
670 x_last_update_date,
671 x_last_updated_by,
672 x_last_update_login
673 );
674
675 OPEN c;
676 FETCH c INTO x_rowid;
677 IF (c%NOTFOUND) THEN
678 CLOSE c;
679 RAISE NO_DATA_FOUND;
680 END IF;
681 CLOSE c;
682
683 END insert_row;
684
685
686 PROCEDURE lock_row (
687 x_rowid IN VARCHAR2,
688 x_group_cd IN VARCHAR2,
689 x_description IN VARCHAR2,
690 x_max_grant_amt IN NUMBER,
691 x_max_grant_perct IN NUMBER,
692 x_max_grant_perct_fact IN VARCHAR2,
693 x_max_loan_amt IN NUMBER,
694 x_max_loan_perct IN NUMBER,
695 x_max_loan_perct_fact IN VARCHAR2,
696 x_max_work_amt IN NUMBER,
697 x_max_work_perct IN NUMBER,
698 x_max_work_perct_fact IN VARCHAR2,
699 x_max_shelp_amt IN NUMBER,
700 x_max_shelp_perct IN NUMBER,
701 x_max_shelp_perct_fact IN VARCHAR2,
702 x_max_gap_amt IN NUMBER,
703 x_max_gap_perct IN NUMBER,
704 x_max_gap_perct_fact IN VARCHAR2,
705 x_use_fixed_costs IN VARCHAR2,
706 x_max_aid_pkg IN NUMBER,
707 x_max_gift_amt IN NUMBER,
708 x_max_gift_perct IN NUMBER,
709 x_max_gift_perct_fact IN VARCHAR2,
710 x_max_schlrshp_amt IN NUMBER,
711 x_max_schlrshp_perct IN NUMBER,
712 x_max_schlrshp_perct_fact IN VARCHAR2,
713 x_cal_type IN VARCHAR2 ,
714 x_sequence_number IN NUMBER ,
715 x_rule_order IN NUMBER,
716 x_s_rule_call_cd IN VARCHAR2,
717 x_rul_sequence_number IN NUMBER,
718 x_active IN VARCHAR2,
719 x_tgrp_id IN NUMBER,
720 x_adplans_id IN NUMBER
721 ) AS
722 /*
723 || Created By : prchandr
724 || Created On : 13-JUL-2001
725 || Purpose : Handles the LOCK mechanism for the table.
726 || Known limitations, enhancements or remarks :
727 || Change History :
728 || Who When What
729 || (reverse chronological order - newest change first)
730 || veramach 06-NOV-2003 FA 125 Multiple distr methods
731 || Added new column adplans_id
732 */
733 CURSOR c1 IS
734 SELECT
738 max_grant_perct,
735 group_cd,
736 description,
737 max_grant_amt,
739 max_grant_perct_fact,
740 max_loan_amt,
741 max_loan_perct,
742 max_loan_perct_fact,
743 max_work_amt,
744 max_work_perct,
745 max_work_perct_fact,
746 max_shelp_amt,
747 max_shelp_perct,
748 max_shelp_perct_fact,
749 max_gap_amt,
750 max_gap_perct,
751 max_gap_perct_fact,
752 use_fixed_costs,
753 max_aid_pkg,
754 max_gift_amt,
755 max_gift_perct ,
756 max_gift_perct_fact,
757 max_schlrshp_amt,
758 max_schlrshp_perct ,
759 max_schlrshp_perct_fact,
760 cal_type,
761 sequence_number,
762 rule_order,
763 s_rule_call_cd,
764 rul_sequence_number,
765 active,
766 adplans_id
767 FROM igf_aw_target_grp_all
768 WHERE rowid = x_rowid
769 FOR UPDATE NOWAIT;
770
771 tlinfo c1%ROWTYPE;
772
773 BEGIN
774
775 OPEN c1;
776 FETCH c1 INTO tlinfo;
777 IF (c1%notfound) THEN
778 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
779 igs_ge_msg_stack.add;
780 CLOSE c1;
781 app_exception.raise_exception;
782 RETURN;
783 END IF;
784 CLOSE c1;
785
786 IF (
787 (tlinfo.group_cd = x_group_cd)
788 AND (tlinfo.description = x_description)
789 AND ((tlinfo.max_grant_amt = x_max_grant_amt) OR ((tlinfo.max_grant_amt IS NULL) AND (X_max_grant_amt IS NULL)))
790 AND ((tlinfo.max_grant_perct = x_max_grant_perct) OR ((tlinfo.max_grant_perct IS NULL) AND (X_max_grant_perct IS NULL)))
791 AND ((tlinfo.max_grant_perct_fact = x_max_grant_perct_fact) OR ((tlinfo.max_grant_perct_fact IS NULL) AND (X_max_grant_perct_fact IS NULL)))
792 AND ((tlinfo.max_loan_amt = x_max_loan_amt) OR ((tlinfo.max_loan_amt IS NULL) AND (X_max_loan_amt IS NULL)))
793 AND ((tlinfo.max_loan_perct = x_max_loan_perct) OR ((tlinfo.max_loan_perct IS NULL) AND (X_max_loan_perct IS NULL)))
794 AND ((tlinfo.max_loan_perct_fact = x_max_loan_perct_fact) OR ((tlinfo.max_loan_perct_fact IS NULL) AND (X_max_loan_perct_fact IS NULL)))
795 AND ((tlinfo.max_work_amt = x_max_work_amt) OR ((tlinfo.max_work_amt IS NULL) AND (X_max_work_amt IS NULL)))
796 AND ((tlinfo.max_work_perct = x_max_work_perct) OR ((tlinfo.max_work_perct IS NULL) AND (X_max_work_perct IS NULL)))
797 AND ((tlinfo.max_work_perct_fact = x_max_work_perct_fact) OR ((tlinfo.max_work_perct_fact IS NULL) AND (X_max_work_perct_fact IS NULL)))
798 AND ((tlinfo.max_shelp_amt = x_max_shelp_amt) OR ((tlinfo.max_shelp_amt IS NULL) AND (X_max_shelp_amt IS NULL)))
799 AND ((tlinfo.max_shelp_perct = x_max_shelp_perct) OR ((tlinfo.max_shelp_perct IS NULL) AND (X_max_shelp_perct IS NULL)))
800 AND ((tlinfo.max_shelp_perct_fact = x_max_shelp_perct_fact) OR ((tlinfo.max_shelp_perct_fact IS NULL) AND (X_max_shelp_perct_fact IS NULL)))
801 AND ((tlinfo.max_gap_amt = x_max_gap_amt) OR ((tlinfo.max_gap_amt IS NULL) AND (X_max_gap_amt IS NULL)))
802 AND ((tlinfo.max_gap_perct = x_max_gap_perct) OR ((tlinfo.max_gap_perct IS NULL) AND (X_max_gap_perct IS NULL)))
803 AND ((tlinfo.max_gap_perct_fact = x_max_gap_perct_fact) OR ((tlinfo.max_gap_perct_fact IS NULL) AND (X_max_gap_perct_fact IS NULL)))
804 AND ((tlinfo.use_fixed_costs = x_use_fixed_costs) OR ((tlinfo.use_fixed_costs IS NULL) AND (X_use_fixed_costs IS NULL)))
805 AND ((tlinfo.max_aid_pkg = x_max_aid_pkg) OR ((tlinfo.max_aid_pkg IS NULL) AND (X_max_aid_pkg IS NULL)))
806 -- AND ((tlinfo.max_gift_amt = x_max_gift_amt) OR ((tlinfo.max_gift_amt IS NULL) AND (X_max_gift_amt IS NULL)))
807 -- AND ((tlinfo.max_gift_perct = x_max_gift_perct) OR ((tlinfo.max_gift_perct IS NULL) AND (X_max_gift_perct IS NULL)))
808 -- AND ((tlinfo.max_gift_perct_fact = x_max_gift_perct_fact) OR ((tlinfo.max_gift_perct_fact IS NULL) AND (X_max_gift_perct_fact IS NULL)))
809 -- AND ((tlinfo.max_schlrshp_amt = x_max_schlrshp_amt) OR ((tlinfo.max_schlrshp_amt IS NULL) AND (X_max_schlrshp_amt IS NULL)))
810 -- AND ((tlinfo.max_schlrshp_perct = x_max_schlrshp_perct) OR ((tlinfo.max_schlrshp_perct IS NULL) AND (X_max_schlrshp_perct IS NULL)))
811 -- AND ((tlinfo.max_schlrshp_perct_fact = x_max_schlrshp_perct_fact) OR ((tlinfo.max_schlrshp_perct_fact IS NULL) AND (X_max_schlrshp_perct_fact IS NULL)))
812 -- AND ((tlinfo.rule_order = x_rule_order) OR ((tlinfo.rule_order IS NULL) AND (X_rule_order IS NULL)))
813 -- AND ((tlinfo.s_rule_call_cd = x_s_rule_call_cd) OR ((tlinfo.s_rule_call_cd IS NULL) AND (X_s_rule_call_cd IS NULL)))
814 -- AND ((tlinfo.rul_sequence_number = x_rul_sequence_number) OR ((tlinfo.rul_sequence_number IS NULL) AND (X_rul_sequence_number IS NULL)))
815 AND (tlinfo.active = x_active)
816 AND ((tlinfo.adplans_id = x_adplans_id) OR ((tlinfo.adplans_id IS NULL) AND (x_adplans_id IS NULL)))
817 ) THEN
818 NULL;
819 ELSE
820 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
821 igs_ge_msg_stack.add;
822 app_exception.raise_exception;
823 END IF;
824
825 RETURN;
826
827 END lock_row;
828
829
830 PROCEDURE update_row (
831 x_rowid IN VARCHAR2,
832 x_group_cd IN VARCHAR2,
833 x_description IN VARCHAR2,
837 x_max_loan_amt IN NUMBER,
834 x_max_grant_amt IN NUMBER,
835 x_max_grant_perct IN NUMBER,
836 x_max_grant_perct_fact IN VARCHAR2,
838 x_max_loan_perct IN NUMBER,
839 x_max_loan_perct_fact IN VARCHAR2,
840 x_max_work_amt IN NUMBER,
841 x_max_work_perct IN NUMBER,
842 x_max_work_perct_fact IN VARCHAR2,
843 x_max_shelp_amt IN NUMBER,
844 x_max_shelp_perct IN NUMBER,
845 x_max_shelp_perct_fact IN VARCHAR2,
846 x_max_gap_amt IN NUMBER,
847 x_max_gap_perct IN NUMBER,
848 x_max_gap_perct_fact IN VARCHAR2,
849 x_use_fixed_costs IN VARCHAR2,
850 x_max_aid_pkg IN NUMBER,
851 x_max_gift_amt IN NUMBER,
852 x_max_gift_perct IN NUMBER,
853 x_max_gift_perct_fact IN VARCHAR2,
854 x_max_schlrshp_amt IN NUMBER,
855 x_max_schlrshp_perct IN NUMBER,
856 x_max_schlrshp_perct_fact IN VARCHAR2,
857 x_cal_type IN VARCHAR2 ,
858 x_sequence_number IN NUMBER ,
859 x_rule_order IN NUMBER,
860 x_s_rule_call_cd IN VARCHAR2,
861 x_rul_sequence_number IN NUMBER,
862 x_active IN VARCHAR2,
863 x_tgrp_id IN NUMBER,
864 x_mode IN VARCHAR2,
865 x_adplans_id IN NUMBER
866 ) AS
867 /*
868 || Created By : prchandr
869 || Created On : 13-JUL-2001
870 || Purpose : Handles the UPDATE DML logic for the table.
871 || Known limitations, enhancements or remarks :
872 || Change History :
873 || Who When What
874 || (reverse chronological order - newest change first)
875 || veramach 06-NOV-2003 FA 125 Multiple distr methods
876 || Added new column adplans_id
877 */
878 x_last_update_date DATE ;
879 x_last_updated_by NUMBER;
880 x_last_update_login NUMBER;
881
882 BEGIN
883
884 x_last_update_date := SYSDATE;
885 IF (X_MODE = 'I') THEN
886 x_last_updated_by := 1;
887 x_last_update_login := 0;
888 ELSIF (x_mode = 'R') THEN
889 x_last_updated_by := fnd_global.user_id;
890 IF x_last_updated_by IS NULL THEN
891 x_last_updated_by := -1;
892 END IF;
893 x_last_update_login := fnd_global.login_id;
894 IF (x_last_update_login IS NULL) THEN
895 x_last_update_login := -1;
896 END IF;
897 ELSE
898 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
899 igs_ge_msg_stack.add;
900 app_exception.raise_exception;
901 END IF;
902
903 before_dml(
904 p_action => 'UPDATE',
905 x_rowid => x_rowid,
906 x_group_cd => x_group_cd,
907 x_description => x_description,
908 x_max_grant_amt => x_max_grant_amt,
909 x_max_grant_perct => x_max_grant_perct,
910 x_max_grant_perct_fact => x_max_grant_perct_fact,
911 x_max_loan_amt => x_max_loan_amt,
912 x_max_loan_perct => x_max_loan_perct,
913 x_max_loan_perct_fact => x_max_loan_perct_fact,
914 x_max_work_amt => x_max_work_amt,
915 x_max_work_perct => x_max_work_perct,
916 x_max_work_perct_fact => x_max_work_perct_fact,
917 x_max_shelp_amt => x_max_shelp_amt,
918 x_max_shelp_perct => x_max_shelp_perct,
919 x_max_shelp_perct_fact => x_max_shelp_perct_fact,
920 x_max_gap_amt => x_max_gap_amt,
921 x_max_gap_perct => x_max_gap_perct,
922 x_max_gap_perct_fact => x_max_gap_perct_fact,
923 x_use_fixed_costs => x_use_fixed_costs,
924 x_max_aid_pkg => x_max_aid_pkg,
925 x_max_gift_amt => x_max_gift_amt,
926 x_max_gift_perct => x_max_gift_perct,
927 x_max_gift_perct_fact => x_max_gift_perct_fact,
928 x_max_schlrshp_amt => x_max_schlrshp_amt,
929 x_max_schlrshp_perct => x_max_schlrshp_perct,
930 x_max_schlrshp_perct_fact => x_max_schlrshp_perct_fact,
931 x_cal_type => x_cal_type,
932 x_sequence_number => x_sequence_number,
933 x_rule_order => x_rule_order,
934 x_s_rule_call_cd => x_s_rule_call_cd,
935 x_rul_sequence_number => x_rul_sequence_number,
936 x_active => x_active,
937 x_tgrp_id => x_tgrp_id,
938 x_adplans_id => x_adplans_id,
942 x_last_updated_by => x_last_updated_by,
939 x_creation_date => x_last_update_date,
940 x_created_by => x_last_updated_by,
941 x_last_update_date => x_last_update_date,
943 x_last_update_login => x_last_update_login
944 );
945
946 UPDATE igf_aw_target_grp_all
947 SET
948 group_cd = new_references.group_cd,
949 description = new_references.description,
950 max_grant_amt = new_references.max_grant_amt,
951 max_grant_perct = new_references.max_grant_perct,
952 max_grant_perct_fact = new_references.max_grant_perct_fact,
953 max_loan_amt = new_references.max_loan_amt,
954 max_loan_perct = new_references.max_loan_perct,
955 max_loan_perct_fact = new_references.max_loan_perct_fact,
956 max_work_amt = new_references.max_work_amt,
957 max_work_perct = new_references.max_work_perct,
958 max_work_perct_fact = new_references.max_work_perct_fact,
959 max_shelp_amt = new_references.max_shelp_amt,
960 max_shelp_perct = new_references.max_shelp_perct,
961 max_shelp_perct_fact = new_references.max_shelp_perct_fact,
962 max_gap_amt = new_references.max_gap_amt,
963 max_gap_perct = new_references.max_gap_perct,
964 max_gap_perct_fact = new_references.max_gap_perct_fact,
965 use_fixed_costs = new_references.use_fixed_costs,
966 max_aid_pkg = new_references.max_aid_pkg,
967 max_gift_amt = new_references.max_gift_amt,
968 max_gift_perct = new_references.max_gift_perct,
969 max_gift_perct_fact = new_references.max_gift_perct_fact,
970 max_schlrshp_amt = new_references.max_schlrshp_amt,
971 max_schlrshp_perct = new_references.max_schlrshp_perct,
972 max_schlrshp_perct_fact = new_references.max_schlrshp_perct_fact,
973 cal_type = new_references.cal_type,
974 sequence_number = new_references.sequence_number,
975 rule_order = new_references.rule_order,
976 s_rule_call_cd = new_references.s_rule_call_cd,
977 rul_sequence_number = new_references.rul_sequence_number,
978 active = new_references.active,
979 adplans_id = new_references.adplans_id,
980 last_update_date = x_last_update_date,
981 last_updated_by = x_last_updated_by,
982 last_update_login = x_last_update_login
983 WHERE rowid = x_rowid;
984
985 IF (SQL%NOTFOUND) THEN
986 RAISE NO_DATA_FOUND;
987 END IF;
988
989 END update_row;
990
991
992 PROCEDURE add_row (
993 x_rowid IN OUT NOCOPY VARCHAR2,
994 x_group_cd IN VARCHAR2,
995 x_description IN VARCHAR2,
996 x_max_grant_amt IN NUMBER,
997 x_max_grant_perct IN NUMBER,
998 x_max_grant_perct_fact IN VARCHAR2,
999 x_max_loan_amt IN NUMBER,
1000 x_max_loan_perct IN NUMBER,
1001 x_max_loan_perct_fact IN VARCHAR2,
1002 x_max_work_amt IN NUMBER,
1003 x_max_work_perct IN NUMBER,
1004 x_max_work_perct_fact IN VARCHAR2,
1005 x_max_shelp_amt IN NUMBER,
1006 x_max_shelp_perct IN NUMBER,
1007 x_max_shelp_perct_fact IN VARCHAR2,
1008 x_max_gap_amt IN NUMBER,
1009 x_max_gap_perct IN NUMBER,
1010 x_max_gap_perct_fact IN VARCHAR2,
1011 x_use_fixed_costs IN VARCHAR2,
1012 x_max_aid_pkg IN NUMBER,
1013 x_max_gift_amt IN NUMBER,
1014 x_max_gift_perct IN NUMBER,
1015 x_max_gift_perct_fact IN VARCHAR2,
1016 x_max_schlrshp_amt IN NUMBER,
1017 x_max_schlrshp_perct IN NUMBER,
1018 x_max_schlrshp_perct_fact IN VARCHAR2,
1019 x_cal_type IN VARCHAR2 ,
1020 x_sequence_number IN NUMBER ,
1021 x_rule_order IN NUMBER,
1022 x_s_rule_call_cd IN VARCHAR2,
1023 x_rul_sequence_number IN NUMBER,
1024 x_active IN VARCHAR2,
1025 x_tgrp_id IN OUT NOCOPY NUMBER,
1026 x_mode IN VARCHAR2,
1027 x_adplans_id IN NUMBER
1028 ) AS
1029 /*
1030 || Created By : prchandr
1031 || Created On : 13-JUL-2001
1032 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1033 || Known limitations, enhancements or remarks :
1034 || Change History :
1035 || Who When What
1036 || (reverse chronological order - newest change first)
1037 || veramach 06-NOV-2003 FA 125 Multiple distr methods
1038 || Added new column adplans_id
1039 */
1040 CURSOR c1 IS
1041 SELECT rowid
1042 FROM igf_aw_target_grp_all
1043 WHERE tgrp_id = x_tgrp_id;
1044
1045 BEGIN
1046
1047 OPEN c1;
1048 FETCH c1 INTO x_rowid;
1049 IF (c1%NOTFOUND) THEN
1050 CLOSE c1;
1051
1052 insert_row (
1053 x_rowid,
1054 x_group_cd,
1055 x_description,
1056 x_max_grant_amt,
1057 x_max_grant_perct,
1058 x_max_grant_perct_fact,
1059 x_max_loan_amt,
1060 x_max_loan_perct,
1061 x_max_loan_perct_fact,
1062 x_max_work_amt,
1063 x_max_work_perct,
1064 x_max_work_perct_fact,
1065 x_max_shelp_amt,
1066 x_max_shelp_perct,
1067 x_max_shelp_perct_fact,
1068 x_max_gap_amt,
1069 x_max_gap_perct,
1070 x_max_gap_perct_fact,
1071 x_use_fixed_costs,
1072 x_max_aid_pkg,
1073 x_max_gift_amt,
1074 x_max_gift_perct ,
1075 x_max_gift_perct_fact,
1076 x_max_schlrshp_amt,
1077 x_max_schlrshp_perct ,
1078 x_max_schlrshp_perct_fact,
1079 x_cal_type,
1080 x_sequence_number,
1081 x_rule_order,
1082 x_s_rule_call_cd,
1083 x_rul_sequence_number,
1084 x_active,
1085 x_tgrp_id,
1086 x_mode,
1087 x_adplans_id
1088 );
1089 RETURN;
1090 END IF;
1091 CLOSE c1;
1092
1093 update_row (
1094 x_rowid,
1095 x_group_cd,
1096 x_description,
1097 x_max_grant_amt,
1098 x_max_grant_perct,
1099 x_max_grant_perct_fact,
1100 x_max_loan_amt,
1101 x_max_loan_perct,
1102 x_max_loan_perct_fact,
1103 x_max_work_amt,
1104 x_max_work_perct,
1105 x_max_work_perct_fact,
1106 x_max_shelp_amt,
1107 x_max_shelp_perct,
1108 x_max_shelp_perct_fact,
1109 x_max_gap_amt,
1110 x_max_gap_perct,
1111 x_max_gap_perct_fact,
1112 x_use_fixed_costs,
1113 x_max_aid_pkg,
1114 x_max_gift_amt,
1115 x_max_gift_perct ,
1116 x_max_gift_perct_fact,
1117 x_max_schlrshp_amt,
1118 x_max_schlrshp_perct ,
1119 x_max_schlrshp_perct_fact,
1120 x_cal_type,
1121 x_sequence_number,
1122 x_rule_order,
1123 x_s_rule_call_cd,
1124 x_rul_sequence_number,
1125 x_active,
1126 x_tgrp_id,
1127 x_mode,
1128 x_adplans_id
1129 );
1130
1131 END add_row;
1132
1133
1134 PROCEDURE delete_row (
1135 x_rowid IN VARCHAR2
1136 ) AS
1137 /*
1138 || Created By : prchandr
1139 || Created On : 13-JUL-2001
1140 || Purpose : Handles the DELETE DML logic for the table.
1141 || Known limitations, enhancements or remarks :
1142 || Change History :
1143 || Who When What
1144 || (reverse chronological order - newest change first)
1145 */
1146 BEGIN
1147
1148 before_dml (
1149 p_action => 'DELETE',
1150 x_rowid => x_rowid
1151 );
1152
1153 DELETE FROM igf_aw_target_grp_all
1154 WHERE rowid = x_rowid;
1155
1156 IF (SQL%NOTFOUND) THEN
1157 RAISE NO_DATA_FOUND;
1158 END IF;
1159
1160 END delete_row;
1161
1162
1163 END igf_aw_target_grp_pkg;