[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_FUND_MAST_PKG
Source
1 PACKAGE BODY igf_aw_fund_mast_pkg AS
2 /* $Header: IGFWI11B.pls 120.3 2005/07/15 09:58:43 appldev ship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: IGF_AW_FUND_MAST_PKG
11 | |
12 | NOTES |
13 | |
14 | This package has a flag on the end of some of the procedures called |
15 | X_MODE. Pass either 'R' for runtime, or 'I' for Install-time. |
16 | This will control how the who columns are filled in; If you are |
17 | running in runtime mode, they are taken from the profiles, whereas in |
18 | install-time mode they get defaulted with special values to indicate |
19 | that they were inserted by datamerge. |
20 | |
21 | The ADD_ROW routine will see whether a row exists by selecting |
22 | based on the primary key, and updates the row if it exists, |
23 | or inserts the row if it doesn't already exist. |
24 | |
25 | This module is called by AutoInstall (afplss.drv) on install and |
26 | upgrade. The WHENEVER SQLERROR and EXIT (at bottom) are required. |
27 | |
28 | HISTORY |
29 | WHO WHEN WHAT |
30 | museshad 14-Jul-2005 Build FA 140. |
31 | Modified TBH procs for the new columns |
32 | |
33 | museshad 25-May-2005 Build# FA157 - Bug# 4382371. |
34 | New column 'DISB_ROUNDING_CODE' has been added|
35 | to the table 'IGF_AW_FUND_MAST_ALL'. Added |
36 | this to all TBH procs. |
37 | |
38 | brajendr 14-Oct-2004 Added the new columns for FA152 - Repackaging |
39 | and COA Build. |
40 | |
41 | veramach July 2004 FA 151 HR Integration(bug#3709292) |
42 | Obsoletes min_hr_rate,max_hr_rate,salary_based|
43 | govt_share_perct |
44 | veramach 3-NOV-2003 FA 125 Multiple Distr Methods |
45 | Removed elig_criteria from all assginments, |
46 | select,insert,update statements |
47 | pathipat 12-Feb-2003 Enh 2747325 - Locking Issues build |
48 | Removed proc get_fk_igs_fi_fee_types() |
49 | SMVK 09-Feb-2003 Bug # 2758812. Added send_without_doc column. |
50 | adhawan 10-dec-2002 #Bug Id 2676394 |
51 | fm_fc_meth has been obsoleted , removed it from|
52 | lock_row. |
53 | adhawan 06-nov-2002 #Bug id 2613536 |
54 | Obsoletion of SAP_TYPE |
55 | ADHAWAN 31-OCT-2002 #Bug 2613546 added gift_aid to the Tbh calls |
56 | PROCEDURE get_fk_igs_ca_inst was referrring to igf_aw_pkg_run_all |
57 | changed it to igf_aw_fund_mast_all |
58 | vchappid 02-Apr-2002 Enh# bug2293676, modified check child |
59 | vvutukur 19-feb-2002 modification done in check_parent_existance |
60 | reg. org_id for bug:2222272 as part of SWSCR006|
61 *=======================================================================*/
62
63 l_rowid VARCHAR2(25);
64 old_references igf_aw_fund_mast_all%ROWTYPE;
65 new_references igf_aw_fund_mast_all%ROWTYPE;
66
67 PROCEDURE set_column_values (
68 p_action IN VARCHAR2,
69 x_rowid IN VARCHAR2,
70 x_fund_id IN NUMBER,
71 x_fund_code IN VARCHAR2,
72 x_ci_cal_type IN VARCHAR2,
73 x_ci_sequence_number IN NUMBER,
74 x_description IN VARCHAR2,
75 x_discontinue_fund IN VARCHAR2,
76 x_entitlement IN VARCHAR2,
77 x_auto_pkg IN VARCHAR2,
78 x_self_help IN VARCHAR2,
79 x_allow_man_pkg IN VARCHAR2,
80 x_update_need IN VARCHAR2,
81 x_disburse_fund IN VARCHAR2,
82 x_available_amt IN NUMBER,
83 x_offered_amt IN NUMBER,
84 x_pending_amt IN NUMBER,
85 x_accepted_amt IN NUMBER,
86 x_declined_amt IN NUMBER,
87 x_cancelled_amt IN NUMBER,
88 x_remaining_amt IN NUMBER,
89 x_enrollment_status IN VARCHAR2,
90 x_prn_award_letter IN VARCHAR2,
91 x_over_award_amt IN NUMBER,
92 x_over_award_perct IN NUMBER,
93 x_min_award_amt IN NUMBER,
94 x_max_award_amt IN NUMBER,
95 x_max_yearly_amt IN NUMBER,
96 x_max_life_amt IN NUMBER,
97 x_max_life_term IN NUMBER,
98 x_fm_fc_methd IN VARCHAR2,
99 x_roundoff_fact IN VARCHAR2,
100 x_replace_fc IN VARCHAR2,
101 x_allow_overaward IN VARCHAR2,
102 x_pckg_awd_stat IN VARCHAR2,
103 x_org_record_req IN VARCHAR2,
104 x_disb_record_req IN VARCHAR2,
105 x_prom_note_req IN VARCHAR2,
106 x_min_num_disb IN NUMBER,
107 x_max_num_disb IN NUMBER,
108 x_fee_type IN VARCHAR2,
109 x_total_offered IN NUMBER,
110 x_total_accepted IN NUMBER,
111 x_total_declined IN NUMBER,
112 x_total_revoked IN NUMBER,
113 x_total_cancelled IN NUMBER,
114 x_total_disbursed IN NUMBER,
115 x_total_committed IN NUMBER,
116 x_committed_amt IN NUMBER,
117 x_disbursed_amt IN NUMBER,
118 x_awd_notice_txt IN VARCHAR2,
119 x_attribute_category IN VARCHAR2,
120 x_attribute1 IN VARCHAR2,
121 x_attribute2 IN VARCHAR2,
122 x_attribute3 IN VARCHAR2,
123 x_attribute4 IN VARCHAR2,
124 x_attribute5 IN VARCHAR2,
125 x_attribute6 IN VARCHAR2,
126 x_attribute7 IN VARCHAR2,
127 x_attribute8 IN VARCHAR2,
128 x_attribute9 IN VARCHAR2,
129 x_attribute10 IN VARCHAR2,
130 x_attribute11 IN VARCHAR2,
131 x_attribute12 IN VARCHAR2,
132 x_attribute13 IN VARCHAR2,
133 x_attribute14 IN VARCHAR2,
134 x_attribute15 IN VARCHAR2,
135 x_attribute16 IN VARCHAR2,
136 x_attribute17 IN VARCHAR2,
137 x_attribute18 IN VARCHAR2,
138 x_attribute19 IN VARCHAR2,
139 x_attribute20 IN VARCHAR2,
140 x_creation_date IN DATE,
141 x_created_by IN NUMBER,
142 x_last_update_date IN DATE,
143 x_last_updated_by IN NUMBER,
144 x_last_update_login IN NUMBER,
145 x_disb_verf_da IN VARCHAR2,
146 x_fund_exp_da IN VARCHAR2,
147 x_nslds_disb_da IN VARCHAR2,
148 x_disb_exp_da IN VARCHAR2,
149 x_fund_recv_reqd IN VARCHAR2,
150 x_show_on_bill IN VARCHAR2,
151 x_bill_desc IN VARCHAR2,
152 x_credit_type_id IN NUMBER,
153 x_spnsr_ref_num IN VARCHAR2,
154 x_party_id IN VARCHAR2,
155 x_spnsr_fee_type IN VARCHAR2,
156 x_min_credit_points IN NUMBER,
157 x_group_id IN NUMBER,
158 x_spnsr_attribute_category IN VARCHAR2,
159 x_spnsr_attribute1 IN VARCHAR2,
160 x_spnsr_attribute2 IN VARCHAR2,
161 x_spnsr_attribute3 IN VARCHAR2,
162 x_spnsr_attribute4 IN VARCHAR2,
163 x_spnsr_attribute5 IN VARCHAR2,
164 x_spnsr_attribute6 IN VARCHAR2,
165 x_spnsr_attribute7 IN VARCHAR2,
166 x_spnsr_attribute8 IN VARCHAR2,
167 x_spnsr_attribute9 IN VARCHAR2,
168 x_spnsr_attribute10 IN VARCHAR2,
169 x_spnsr_attribute11 IN VARCHAR2,
170 x_spnsr_attribute12 IN VARCHAR2,
171 x_spnsr_attribute13 IN VARCHAR2,
172 x_spnsr_attribute14 IN VARCHAR2,
173 x_spnsr_attribute15 IN VARCHAR2,
174 x_spnsr_attribute16 IN VARCHAR2,
175 x_spnsr_attribute17 IN VARCHAR2,
176 x_spnsr_attribute18 IN VARCHAR2,
177 x_spnsr_attribute19 IN VARCHAR2,
178 x_spnsr_attribute20 IN VARCHAR2,
179 x_threshold_perct IN NUMBER,
180 x_threshold_value IN NUMBER,
181 x_ver_app_stat_override IN VARCHAR2 ,
182 x_gift_aid IN VARCHAR2,
183 x_send_without_doc IN VARCHAR2, -- Added as part of Bug # 2758812
184 x_re_pkg_verif_flag IN VARCHAR2,
185 x_donot_repkg_if_code IN VARCHAR2,
186 x_lock_award_flag IN VARCHAR2,
187 x_disb_rounding_code IN VARCHAR2 DEFAULT NULL,
188 x_view_only_flag IN VARCHAR2 DEFAULT NULL,
189 x_accept_less_amt_flag IN VARCHAR2 DEFAULT NULL,
190 x_allow_inc_post_accept_flag IN VARCHAR2 DEFAULT NULL,
191 x_min_increase_amt IN NUMBER DEFAULT NULL,
192 x_allow_dec_post_accept_flag IN VARCHAR2 DEFAULT NULL,
193 x_min_decrease_amt IN NUMBER DEFAULT NULL,
194 x_allow_decln_post_accept_flag IN VARCHAR2 DEFAULT NULL,
195 x_status_after_decline IN VARCHAR2 DEFAULT NULL,
196 x_fund_information_txt IN VARCHAR2 DEFAULT NULL
197 ) AS /*
198 || Created By : cdcruz
199 || Created On : 14-NOV-2000
200 || Purpose : Initialises the Old and New references for the columns of the table.
201 || Known limitations, enhancements or remarks :
202 || Change History :
203 || Who When What
204 || museshad 14-Jul-2005 Build FA 140.
205 || Changes relating to the addition of new
206 || columns.
207 || museshad 25-May-2005 Build# FA157 - Bug# 4382371.
208 || New column 'DISB_ROUNDING_CODE' has been added
209 || to the table 'IGF_AW_FUND_MAST_ALL'.
210 || Modified the TBH to include this.
211 || veramach 1-NOV-2003 FA 125 - Removed elig_criteria from assigments
212 || (reverse chronological order - newest change first)
213 */
214
215 CURSOR cur_old_ref_values IS
216 SELECT *
217 FROM IGF_AW_FUND_MAST_ALL
218 WHERE rowid = x_rowid;
219
220 BEGIN
221
222 l_rowid := x_rowid;
223
224 -- Code for setting the Old and New Reference Values.
225 -- Populate Old Values.
226 OPEN cur_old_ref_values;
227 FETCH cur_old_ref_values INTO old_references;
228 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
229 CLOSE cur_old_ref_values;
230 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
231 igs_ge_msg_stack.add;
232 app_exception.raise_exception;
233 RETURN;
234 END IF;
235 CLOSE cur_old_ref_values;
236
237 -- Populate New Values.
238 new_references.fund_id := x_fund_id;
239 new_references.fund_code := x_fund_code;
240 new_references.ci_cal_type := x_ci_cal_type;
241 new_references.ci_sequence_number := x_ci_sequence_number;
242 new_references.description := x_description;
243 new_references.discontinue_fund := x_discontinue_fund;
244 new_references.entitlement := x_entitlement;
245 new_references.auto_pkg := x_auto_pkg;
246 new_references.self_help := x_self_help;
247 new_references.allow_man_pkg := x_allow_man_pkg;
248 new_references.update_need := x_update_need;
249 new_references.disburse_fund := x_disburse_fund;
250 new_references.available_amt := x_available_amt;
251 new_references.offered_amt := x_offered_amt;
252 new_references.pending_amt := x_pending_amt;
253 new_references.accepted_amt := x_accepted_amt;
254 new_references.declined_amt := x_declined_amt;
255 new_references.cancelled_amt := x_cancelled_amt;
256 new_references.remaining_amt := x_remaining_amt;
257 new_references.enrollment_status := x_enrollment_status;
258 new_references.prn_award_letter := x_prn_award_letter;
259 new_references.over_award_amt := x_over_award_amt;
260 new_references.over_award_perct := x_over_award_perct;
261 new_references.min_award_amt := x_min_award_amt;
262 new_references.max_award_amt := x_max_award_amt;
263 new_references.max_yearly_amt := x_max_yearly_amt;
264 new_references.max_life_amt := x_max_life_amt;
265 new_references.max_life_term := x_max_life_term;
266 new_references.fm_fc_methd := x_fm_fc_methd;
267 new_references.roundoff_fact := x_roundoff_fact;
268 new_references.replace_fc := x_replace_fc;
269 new_references.allow_overaward := x_allow_overaward;
270 new_references.pckg_awd_stat := x_pckg_awd_stat;
271 new_references.org_record_req := x_org_record_req;
272 new_references.disb_record_req := x_disb_record_req;
273 new_references.prom_note_req := x_prom_note_req;
274 new_references.min_num_disb := x_min_num_disb;
275 new_references.max_num_disb := x_max_num_disb;
276 new_references.fee_type := x_fee_type;
277 new_references.total_offered := x_total_offered;
278 new_references.total_accepted := x_total_accepted;
279 new_references.total_declined := x_total_declined;
280 new_references.total_revoked := x_total_revoked;
281 new_references.total_cancelled := x_total_cancelled;
282 new_references.total_disbursed := x_total_disbursed;
283 new_references.total_committed := x_total_committed;
284 new_references.committed_amt := x_committed_amt;
285 new_references.disbursed_amt := x_disbursed_amt;
286 new_references.awd_notice_txt := x_awd_notice_txt;
287 new_references.attribute_category := x_attribute_category;
288 new_references.attribute1 := x_attribute1;
289 new_references.attribute2 := x_attribute2;
290 new_references.attribute3 := x_attribute3;
291 new_references.attribute4 := x_attribute4;
292 new_references.attribute5 := x_attribute5;
293 new_references.attribute6 := x_attribute6;
294 new_references.attribute7 := x_attribute7;
295 new_references.attribute8 := x_attribute8;
296 new_references.attribute9 := x_attribute9;
297 new_references.attribute10 := x_attribute10;
298 new_references.attribute11 := x_attribute11;
299 new_references.attribute12 := x_attribute12;
300 new_references.attribute13 := x_attribute13;
301 new_references.attribute14 := x_attribute14;
302 new_references.attribute15 := x_attribute15;
303 new_references.attribute16 := x_attribute16;
304 new_references.attribute17 := x_attribute17;
305 new_references.attribute18 := x_attribute18;
306 new_references.attribute19 := x_attribute19;
307 new_references.attribute20 := x_attribute20;
308 new_references.disb_verf_da := x_disb_verf_da;
309 new_references.fund_exp_da := x_fund_exp_da;
310 new_references.nslds_disb_da := x_nslds_disb_da;
311 new_references.disb_exp_da := x_disb_exp_da;
312 new_references.fund_recv_reqd := x_fund_recv_reqd;
313 new_references.show_on_bill := x_show_on_bill;
314 new_references.bill_desc := x_bill_desc;
315 new_references.credit_type_id := x_credit_type_id;
316 new_references.spnsr_ref_num := x_spnsr_ref_num;
317 new_references.party_id := x_party_id;
318 new_references.spnsr_fee_type := x_spnsr_fee_type;
319 new_references.min_credit_points := x_min_credit_points;
320 new_references.group_id := x_group_id;
321 new_references.spnsr_attribute_category := x_spnsr_attribute_category;
322 new_references.spnsr_attribute1 := x_spnsr_attribute1;
323 new_references.spnsr_attribute2 := x_spnsr_attribute2;
324 new_references.spnsr_attribute3 := x_spnsr_attribute3;
325 new_references.spnsr_attribute4 := x_spnsr_attribute4;
326 new_references.spnsr_attribute5 := x_spnsr_attribute5;
327 new_references.spnsr_attribute6 := x_spnsr_attribute6;
328 new_references.spnsr_attribute7 := x_spnsr_attribute7;
329 new_references.spnsr_attribute8 := x_spnsr_attribute8;
330 new_references.spnsr_attribute9 := x_spnsr_attribute9;
331 new_references.spnsr_attribute10 := x_spnsr_attribute10;
332 new_references.spnsr_attribute11 := x_spnsr_attribute11;
333 new_references.spnsr_attribute12 := x_spnsr_attribute12;
334 new_references.spnsr_attribute13 := x_spnsr_attribute13;
335 new_references.spnsr_attribute14 := x_spnsr_attribute14;
336 new_references.spnsr_attribute15 := x_spnsr_attribute15;
337 new_references.spnsr_attribute16 := x_spnsr_attribute16;
338 new_references.spnsr_attribute17 := x_spnsr_attribute17;
339 new_references.spnsr_attribute18 := x_spnsr_attribute18;
340 new_references.spnsr_attribute19 := x_spnsr_attribute19;
341 new_references.spnsr_attribute20 := x_spnsr_attribute20;
342 new_references.threshold_perct := x_threshold_perct ;
343 new_references.threshold_value := x_threshold_value ;
344 new_references.ver_app_stat_override := x_ver_app_stat_override;
345 new_references.gift_aid := x_gift_aid;
346 new_references.send_without_doc := x_send_without_doc;
347 new_references.re_pkg_verif_flag := x_re_pkg_verif_flag;
348 new_references.donot_repkg_if_code := x_donot_repkg_if_code;
349 new_references.lock_award_flag := x_lock_award_flag;
350 new_references.disb_rounding_code := x_disb_rounding_code;
351 new_references.view_only_flag := x_view_only_flag;
352 new_references.accept_less_amt_flag := x_accept_less_amt_flag;
353 new_references.allow_inc_post_accept_flag := x_allow_inc_post_accept_flag;
354 new_references.min_increase_amt := x_min_increase_amt;
355 new_references.allow_dec_post_accept_flag := x_allow_dec_post_accept_flag;
356 new_references.min_decrease_amt := x_min_decrease_amt;
357 new_references.allow_decln_post_accept_flag := x_allow_decln_post_accept_flag;
358 new_references.status_after_decline := x_status_after_decline;
359 new_references.fund_information_txt := x_fund_information_txt;
360
361 IF (p_action = 'UPDATE') THEN
362 new_references.creation_date := old_references.creation_date;
363 new_references.created_by := old_references.created_by;
364 ELSE
365 new_references.creation_date := x_creation_date;
366 new_references.created_by := x_created_by;
367 END IF;
368
369 new_references.last_update_date := x_last_update_date;
370 new_references.last_updated_by := x_last_updated_by;
371 new_references.last_update_login := x_last_update_login;
372
373 END set_column_values;
374
375 FUNCTION get_uk_for_validation (
376 x_fund_code IN VARCHAR2,
377 x_ci_cal_type IN VARCHAR2,
378 x_ci_sequence_number IN NUMBER
379 ) RETURN BOOLEAN AS
380 /*
381 || Created By :
382 || Created On : 28-DEC-2001
383 || Purpose : Validates the Unique Keys of the table.
384 || Known limitations, enhancements or remarks :
385 || Change History :
386 || Who When What
387 || (reverse chronological order - newest change first)
388 */
389 CURSOR cur_rowid IS
390 SELECT rowid
391 FROM igf_aw_fund_mast_all
392 WHERE fund_code = x_fund_code
393 AND ci_cal_type = x_ci_cal_type
394 AND ci_sequence_number = x_ci_sequence_number
395 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
396
397 lv_rowid cur_rowid%RowType;
398
399 BEGIN
400 OPEN cur_rowid;
401 FETCH cur_rowid INTO lv_rowid;
402 IF (cur_rowid%FOUND) THEN
403 CLOSE cur_rowid;
404 RETURN (true);
405 ELSE
406 CLOSE cur_rowid;
407 RETURN(FALSE);
408 END IF;
409 END get_uk_for_validation ;
410
411 PROCEDURE check_uniqueness AS
412 /*
413 || Created By : rasingh
414 || Created On : 28-DEC-2001
415 || Purpose : Handles the Unique Constraint logic defined for the columns.
416 || Known limitations, enhancements or remarks :
417 || Change History :
418 || Who When What
419 || (reverse chronological order - newest change first)
420 */
421 BEGIN
422
423 IF ( get_uk_for_validation (
424 new_references.fund_code,
425 new_references.ci_cal_type,
426 new_references.ci_sequence_number
427 )
428 ) THEN
429 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
430 igs_ge_msg_stack.add;
431 app_exception.raise_exception;
432 END IF;
433
434 END check_uniqueness;
435
436
437 PROCEDURE check_parent_existance AS
438 /*
439 || Created By : cdcruz
440 || Created On : 14-NOV-2000
441 || Purpose : Checks for the existance of Parent records.
442 || Known limitations, enhancements or remarks :
443 || Change History :
444 || Who When What
445 || vvutukur 19-feb-2002 created new local variable l_org_id and modified the check with old_references.org_id
446 || for bug:2222272 as part of SWSCR006(MO).
447 || (reverse chronological order - newest change first)
448 */
449
450 l_org_id igf_aw_fund_mast_all.org_id%TYPE := igf_aw_gen.get_org_id; --bug:2222272
451
452 BEGIN
453
454 IF (((old_references.fund_code = new_references.fund_code) AND
455 (NVL(old_references.org_id,-99) = NVL(l_org_id,-99)) AND --bug : 2222272
456 (old_references.ci_cal_type = new_references.ci_cal_type) AND
457 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
458 ((new_references.fund_code IS NULL) OR
459 (l_org_id IS NULL) OR --bug 2222272
460 (new_references.ci_cal_type IS NULL) OR
461 (new_references.ci_sequence_number IS NULL))) THEN
462 NULL;
463 ELSIF NOT igf_aw_fund_cat_pkg.get_uk_for_validation (
464 new_references.fund_code
465 ) THEN
466 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
467 igs_ge_msg_stack.add;
468 app_exception.raise_exception;
469 END IF;
470
471 IF (((old_references.ci_cal_type = new_references.ci_cal_type) AND
472 (old_references.ci_sequence_number = new_references.ci_sequence_number)) OR
473 ((new_references.ci_cal_type IS NULL) OR
474 (new_references.ci_sequence_number IS NULL))) THEN
475 NULL;
476 ELSIF NOT igs_ca_inst_pkg.get_pk_for_validation (
477 new_references.ci_cal_type,
478 new_references.ci_sequence_number
479 ) THEN
480 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
481 igs_ge_msg_stack.add;
482 app_exception.raise_exception;
483 END IF;
484
485 --
486 -- Added Get PK Calls for Person ID Groups
487 --
488 IF ((old_references.group_id = new_references.group_id )
489 OR
490 (new_references.group_id IS NULL)) THEN
491 NULL;
492 ELSIF NOT igs_pe_persid_group_pkg.get_pk_for_validation (
493 new_references.group_id) THEN
494 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
495 igs_ge_msg_stack.add;
496 app_exception.raise_exception;
497 END IF;
498
499 --
500 -- Added Get PK Calls for Fee Types
501 --
502 IF ((old_references.fee_type = new_references.fee_type)
503 OR
504 (new_references.fee_type IS NULL)) THEN
505 NULL;
506 ELSIF NOT igs_fi_fee_type_pkg.get_pk_for_validation (
507 new_references.fee_type) THEN
508 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
509 igs_ge_msg_stack.add;
510 app_exception.raise_exception;
511 END IF;
512
513 IF ((old_references.spnsr_fee_type = new_references.spnsr_fee_type)
514 OR
515 (new_references.spnsr_fee_type IS NULL)) THEN
516 NULL;
517 ELSIF NOT igs_fi_fee_type_pkg.get_pk_for_validation (
518 new_references.spnsr_fee_type) THEN
519 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
520 igs_ge_msg_stack.add;
521 app_exception.raise_exception;
522 END IF;
523
524 END check_parent_existance;
525
526
527 PROCEDURE check_child_existance IS
528 /*
529 || Created By : cdcruz
530 || Created On : 14-NOV-2000
531 || Purpose : Checks for the existance of Child records.
532 || Known limitations, enhancements or remarks :
533 || Change History :
534 || Who When What
535 || veramach 3-NOV-2003 removed call to igf_aw_fund_tp_pkg.get_fk_igf_aw_fund_mast
536 || vchappid 02-Apr-2002 Enh# 2293676, Added igs_fi_bill_pln_crd_pkg.get_fk_igf_aw_fund_mast
537 || (reverse chronological order - newest change first)
538 */
539 BEGIN
540
541 igf_aw_award_pkg.get_fk_igf_aw_fund_mast (
542 old_references.fund_id );
543
544 igf_aw_fund_excl_pkg.get_fk_igf_aw_fund_mast (
545 old_references.fund_id );
546
547 igf_aw_fund_incl_pkg.get_fk_igf_aw_fund_mast (
548 old_references.fund_id );
549
550
551 --
552 -- added fk checks for sponsor setups
553 --
554 igf_sp_fc_pkg.get_fk_igf_aw_fund_mast(
555 old_references.fund_id);
556
557 igf_sp_stdnt_rel_pkg.get_fk_igf_aw_fund_mast(
558 old_references.fund_id);
559
560 igs_fi_bill_pln_crd_pkg.get_fk_igf_aw_fund_mast(
561 old_references.fund_id);
562
563 igf_aw_fund_feeclas_pkg.get_fk_igf_aw_fund_mast(
564 old_references.fund_id);
565
566 igf_aw_fund_unit_pkg.get_fk_igf_aw_fund_mast(
567 old_references.fund_id);
568
569 igf_aw_fund_prg_pkg.get_fk_igf_aw_fund_mast(
570 old_references.fund_id);
571
572 igf_aw_fund_td_map_pkg.get_fk_igf_aw_fund_mast(
573 old_references.fund_id);
574
575 END check_child_existance;
576
577
578 FUNCTION get_pk_for_validation (
579 x_fund_id IN NUMBER
580 ) RETURN BOOLEAN AS
581 /*
582 || Created By : cdcruz
583 || Created On : 14-NOV-2000
584 || Purpose : Validates the Primary Key of the table.
585 || Known limitations, enhancements or remarks :
586 || Change History :
587 || Who When What
588 || (reverse chronological order - newest change first)
589 */
590 CURSOR cur_rowid IS
591 SELECT rowid
592 FROM igf_aw_fund_mast_all
593 WHERE fund_id = x_fund_id
594 FOR UPDATE NOWAIT;
595
596 lv_rowid cur_rowid%RowType;
597
598 BEGIN
599
600 OPEN cur_rowid;
601 FETCH cur_rowid INTO lv_rowid;
602 IF (cur_rowid%FOUND) THEN
603 CLOSE cur_rowid;
604 RETURN(TRUE);
605 ELSE
606 CLOSE cur_rowid;
607 RETURN(FALSE);
608 END IF;
609
610 END get_pk_for_validation;
611
612 PROCEDURE get_ufk_igf_aw_fund_cat (
613 x_fund_code IN VARCHAR2,
614 x_org_id IN NUMBER
615 ) AS
616 /*
617 || Created By : cdcruz
618 || Created On : 14-NOV-2000
619 || Purpose : Validates the Foreign Keys for the table.
620 || Known limitations, enhancements or remarks :
621 || Change History :
622 || Who When What
623 || (reverse chronological order - newest change first)
624 */
625 l_org_id igf_aw_fund_mast_all.org_id%TYPE := igf_aw_gen.get_org_id;
626 CURSOR cur_rowid IS
627 SELECT rowid
628 FROM igf_aw_fund_mast_all
629 WHERE fund_code = x_fund_code
630 AND NVL(org_id, NVL(l_org_id, -99)) = NVL(l_org_id, -99);
631
632 lv_rowid cur_rowid%RowType;
633
634 BEGIN
635
636 OPEN cur_rowid;
637 FETCH cur_rowid INTO lv_rowid;
638 IF (cur_rowid%FOUND) THEN
639 CLOSE cur_rowid;
640 fnd_message.set_name ('IGF', 'IGF_AW_FMAST_FCAT_FK');
641 igs_ge_msg_stack.add;
642 app_exception.raise_exception;
643 RETURN;
644 END IF;
645 CLOSE cur_rowid;
646
647 END get_ufk_igf_aw_fund_cat;
648
649 PROCEDURE get_fk_igs_ca_inst (
650 x_ci_cal_type IN VARCHAR2,
651 x_ci_sequence_number IN NUMBER
652 ) AS
653 /*
654 || Created By : cdcruz
655 || Created On : 14-NOV-2000
656 || Purpose : Validates the Foreign Keys for the table.
657 || Known limitations, enhancements or remarks :
658 || Change History :
659 || Who When What
660 || (reverse chronological order - newest change first)
661 */
662 CURSOR cur_rowid IS
663 SELECT rowid
664 FROM igf_aw_fund_mast_all
665 WHERE ((ci_cal_type = x_ci_cal_type) AND
666 (ci_sequence_number = x_ci_sequence_number));
667
668 lv_rowid cur_rowid%RowType;
669
670 BEGIN
671
672 OPEN cur_rowid;
673 FETCH cur_rowid INTO lv_rowid;
674 IF (cur_rowid%FOUND) THEN
675 CLOSE cur_rowid;
676 fnd_message.set_name ('IGF', 'IGF_AW_CI_FMAST_FK');
677 igs_ge_msg_stack.add;
678 app_exception.raise_exception;
679 RETURN;
680 END IF;
681 CLOSE cur_rowid;
682
683 END get_fk_igs_ca_inst;
684
685 PROCEDURE get_fk_igs_fi_cr_types (
686 x_credit_type_id IN NUMBER
687 ) AS
688 /*
689 || Created By : cdcruz
690 || Created On : 14-NOV-2000
691 || Purpose : Validates the Foreign Keys for the table.
692 || Known limitations, enhancements or remarks :
693 || Change History :
694 || Who When What
695 || (reverse chronological order - newest change first)
696 */
697 CURSOR cur_rowid IS
698 SELECT rowid
699 FROM igf_aw_fund_mast
700 WHERE ((credit_type_id = x_credit_type_id));
701
702 lv_rowid cur_rowid%RowType;
703
704 BEGIN
705
706 OPEN cur_rowid;
707 FETCH cur_rowid INTO lv_rowid;
708 IF (cur_rowid%FOUND) THEN
709 CLOSE cur_rowid;
710 fnd_message.set_name ('IGF', 'IGF_AW_FMAST_CT_FK');
711 igs_ge_msg_stack.add;
712 app_exception.raise_exception;
713 RETURN;
714 END IF;
715 CLOSE cur_rowid;
716
717 END get_fk_igs_fi_cr_types ;
718
719 PROCEDURE before_dml (
720 p_action IN VARCHAR2,
721 x_rowid IN VARCHAR2,
722 x_fund_id IN NUMBER ,
723 x_fund_code IN VARCHAR2,
724 x_ci_cal_type IN VARCHAR2,
725 x_ci_sequence_number IN NUMBER ,
726 x_description IN VARCHAR2,
727 x_discontinue_fund IN VARCHAR2,
728 x_entitlement IN VARCHAR2,
729 x_auto_pkg IN VARCHAR2,
730 x_self_help IN VARCHAR2,
731 x_allow_man_pkg IN VARCHAR2,
732 x_update_need IN VARCHAR2,
733 x_disburse_fund IN VARCHAR2,
734 x_available_amt IN NUMBER ,
735 x_offered_amt IN NUMBER ,
736 x_pending_amt IN NUMBER ,
737 x_accepted_amt IN NUMBER ,
738 x_declined_amt IN NUMBER ,
739 x_cancelled_amt IN NUMBER ,
740 x_remaining_amt IN NUMBER ,
741 x_enrollment_status IN VARCHAR2,
742 x_prn_award_letter IN VARCHAR2,
743 x_over_award_amt IN NUMBER ,
744 x_over_award_perct IN NUMBER ,
745 x_min_award_amt IN NUMBER ,
746 x_max_award_amt IN NUMBER ,
747 x_max_yearly_amt IN NUMBER ,
748 x_max_life_amt IN NUMBER ,
749 x_max_life_term IN NUMBER ,
750 x_fm_fc_methd IN VARCHAR2,
751 x_roundoff_fact IN VARCHAR2,
752 x_replace_fc IN VARCHAR2,
753 x_allow_overaward IN VARCHAR2,
754 x_pckg_awd_stat IN VARCHAR2,
755 x_org_record_req IN VARCHAR2,
756 x_disb_record_req IN VARCHAR2,
757 x_prom_note_req IN VARCHAR2,
758 x_min_num_disb IN NUMBER ,
759 x_max_num_disb IN NUMBER ,
760 x_fee_type IN VARCHAR2,
761 x_total_offered IN NUMBER ,
762 x_total_accepted IN NUMBER ,
763 x_total_declined IN NUMBER ,
764 x_total_revoked IN NUMBER ,
765 x_total_cancelled IN NUMBER ,
766 x_total_disbursed IN NUMBER ,
767 x_total_committed IN NUMBER ,
768 x_committed_amt IN NUMBER ,
769 x_disbursed_amt IN NUMBER ,
770 x_awd_notice_txt IN VARCHAR2,
771 x_attribute_category IN VARCHAR2,
772 x_attribute1 IN VARCHAR2,
773 x_attribute2 IN VARCHAR2,
774 x_attribute3 IN VARCHAR2,
775 x_attribute4 IN VARCHAR2,
776 x_attribute5 IN VARCHAR2,
777 x_attribute6 IN VARCHAR2,
778 x_attribute7 IN VARCHAR2,
779 x_attribute8 IN VARCHAR2,
780 x_attribute9 IN VARCHAR2,
781 x_attribute10 IN VARCHAR2,
782 x_attribute11 IN VARCHAR2,
783 x_attribute12 IN VARCHAR2,
784 x_attribute13 IN VARCHAR2,
785 x_attribute14 IN VARCHAR2,
786 x_attribute15 IN VARCHAR2,
787 x_attribute16 IN VARCHAR2,
788 x_attribute17 IN VARCHAR2,
789 x_attribute18 IN VARCHAR2,
790 x_attribute19 IN VARCHAR2,
791 x_attribute20 IN VARCHAR2,
792 x_creation_date IN DATE ,
793 x_created_by IN NUMBER ,
794 x_last_update_date IN DATE ,
795 x_last_updated_by IN NUMBER ,
796 x_last_update_login IN NUMBER ,
797 x_disb_verf_da IN VARCHAR2,
798 x_fund_exp_da IN VARCHAR2,
799 x_nslds_disb_da IN VARCHAR2,
800 x_disb_exp_da IN VARCHAR2,
801 x_fund_recv_reqd IN VARCHAR2,
802 x_show_on_bill IN VARCHAR2,
803 x_bill_desc IN VARCHAR2,
804 x_credit_type_id IN NUMBER ,
805 x_spnsr_ref_num IN VARCHAR2,
806 x_party_id IN VARCHAR2,
807 x_spnsr_fee_type IN VARCHAR2,
808 x_min_credit_points IN NUMBER ,
809 x_group_id IN NUMBER ,
810 x_spnsr_attribute_category IN VARCHAR2,
811 x_spnsr_attribute1 IN VARCHAR2,
812 x_spnsr_attribute2 IN VARCHAR2,
813 x_spnsr_attribute3 IN VARCHAR2,
814 x_spnsr_attribute4 IN VARCHAR2,
815 x_spnsr_attribute5 IN VARCHAR2,
816 x_spnsr_attribute6 IN VARCHAR2,
817 x_spnsr_attribute7 IN VARCHAR2,
818 x_spnsr_attribute8 IN VARCHAR2,
819 x_spnsr_attribute9 IN VARCHAR2,
820 x_spnsr_attribute10 IN VARCHAR2,
821 x_spnsr_attribute11 IN VARCHAR2,
822 x_spnsr_attribute12 IN VARCHAR2,
823 x_spnsr_attribute13 IN VARCHAR2,
824 x_spnsr_attribute14 IN VARCHAR2,
825 x_spnsr_attribute15 IN VARCHAR2,
826 x_spnsr_attribute16 IN VARCHAR2,
827 x_spnsr_attribute17 IN VARCHAR2,
828 x_spnsr_attribute18 IN VARCHAR2,
829 x_spnsr_attribute19 IN VARCHAR2,
830 x_spnsr_attribute20 IN VARCHAR2,
831 x_threshold_perct IN NUMBER ,
832 x_threshold_value IN NUMBER ,
833 x_ver_app_stat_override IN VARCHAR2,
834 x_gift_aid IN VARCHAR2,
835 x_send_without_doc IN VARCHAR2, -- Added as part of Bug # 2758812
836 x_re_pkg_verif_flag IN VARCHAR2,
837 x_donot_repkg_if_code IN VARCHAR2,
838 x_lock_award_flag IN VARCHAR2,
839 x_disb_rounding_code IN VARCHAR2 DEFAULT NULL,
840 x_view_only_flag IN VARCHAR2 DEFAULT NULL,
841 x_accept_less_amt_flag IN VARCHAR2 DEFAULT NULL,
842 x_allow_inc_post_accept_flag IN VARCHAR2 DEFAULT NULL,
843 x_min_increase_amt IN NUMBER DEFAULT NULL,
844 x_allow_dec_post_accept_flag IN VARCHAR2 DEFAULT NULL,
845 x_min_decrease_amt IN NUMBER DEFAULT NULL,
846 x_allow_decln_post_accept_flag IN VARCHAR2 DEFAULT NULL,
847 x_status_after_decline IN VARCHAR2 DEFAULT NULL,
848 x_fund_information_txt IN VARCHAR2 DEFAULT NULL
849
850 ) AS
851 /*
852 || Created By : prchandr
853 || Created On : 01-JUN-2001
854 || Purpose : Initialises the columns, Checks Constraints, Calls the
855 || Trigger Handlers for the table, before any DML operation.
856 || Known limitations, enhancements or remarks :
857 || Change History :
858 || Who When What
859 || museshad 14-Jul-2005 Build FA 140 - Changes relating to the
860 || addition of new columns.
861 || museshad 25-May-2005 Build# FA157 - Bug# 4382371.
862 || New column 'DISB_ROUNDING_CODE' has been added
863 || to the table 'IGF_AW_FUND_MAST_ALL'.
864 || Modified the TBH to include this.
865 || (reverse chronological order - newest change first)
866 */
867 BEGIN
868
869 set_column_values (
870 p_action,
871 x_rowid,
872 x_fund_id,
873 x_fund_code,
874 x_ci_cal_type,
875 x_ci_sequence_number,
876 x_description,
877 x_discontinue_fund,
878 x_entitlement,
879 x_auto_pkg,
880 x_self_help,
881 x_allow_man_pkg,
882 x_update_need,
883 x_disburse_fund,
884 x_available_amt,
885 x_offered_amt,
886 x_pending_amt,
887 x_accepted_amt,
888 x_declined_amt,
889 x_cancelled_amt,
890 x_remaining_amt,
891 x_enrollment_status,
892 x_prn_award_letter,
893 x_over_award_amt,
894 x_over_award_perct,
895 x_min_award_amt,
896 x_max_award_amt,
897 x_max_yearly_amt,
898 x_max_life_amt,
899 x_max_life_term,
900 x_fm_fc_methd,
901 x_roundoff_fact,
902 x_replace_fc,
903 x_allow_overaward,
904 x_pckg_awd_stat,
905 x_org_record_req,
906 x_disb_record_req,
907 x_prom_note_req,
908 x_min_num_disb,
909 x_max_num_disb,
910 x_fee_type,
911 x_total_offered,
912 x_total_accepted,
913 x_total_declined,
914 x_total_revoked,
915 x_total_cancelled,
916 x_total_disbursed,
917 x_total_committed,
918 x_committed_amt,
919 x_disbursed_amt,
920 x_awd_notice_txt,
921 x_attribute_category,
922 x_attribute1,
923 x_attribute2,
924 x_attribute3,
925 x_attribute4,
926 x_attribute5,
927 x_attribute6,
928 x_attribute7,
929 x_attribute8,
930 x_attribute9,
931 x_attribute10,
932 x_attribute11,
933 x_attribute12,
934 x_attribute13,
935 x_attribute14,
936 x_attribute15,
937 x_attribute16,
938 x_attribute17,
939 x_attribute18,
940 x_attribute19,
941 x_attribute20,
942 x_creation_date,
943 x_created_by,
944 x_last_update_date,
945 x_last_updated_by,
946 x_last_update_login,
947 x_disb_verf_da,
948 x_fund_exp_da,
949 x_nslds_disb_da,
950 x_disb_exp_da,
951 x_fund_recv_reqd,
952 x_show_on_bill,
953 x_bill_desc,
954 x_credit_type_id,
955 x_spnsr_ref_num,
956 x_party_id,
957 x_spnsr_fee_type,
958 x_min_credit_points,
959 x_group_id,
960 x_spnsr_attribute_category,
961 x_spnsr_attribute1,
962 x_spnsr_attribute2,
963 x_spnsr_attribute3,
964 x_spnsr_attribute4,
965 x_spnsr_attribute5,
966 x_spnsr_attribute6,
967 x_spnsr_attribute7,
968 x_spnsr_attribute8,
969 x_spnsr_attribute9,
970 x_spnsr_attribute10,
971 x_spnsr_attribute11,
972 x_spnsr_attribute12,
973 x_spnsr_attribute13,
974 x_spnsr_attribute14,
975 x_spnsr_attribute15,
976 x_spnsr_attribute16,
977 x_spnsr_attribute17,
978 x_spnsr_attribute18,
979 x_spnsr_attribute19,
980 x_spnsr_attribute20,
981 x_threshold_perct,
982 x_threshold_value,
983 x_ver_app_stat_override,
984 x_gift_aid,
985 x_send_without_doc,
986 x_re_pkg_verif_flag,
987 x_donot_repkg_if_code,
988 x_lock_award_flag,
989 x_disb_rounding_code,
990 x_view_only_flag,
991 x_accept_less_amt_flag,
992 x_allow_inc_post_accept_flag,
993 x_min_increase_amt,
994 x_allow_dec_post_accept_flag,
995 x_min_decrease_amt,
996 x_allow_decln_post_accept_flag,
997 x_status_after_decline,
998 x_fund_information_txt
999 );
1000
1001 IF (p_action = 'INSERT') THEN
1002 -- Call all the procedures related to Before Insert.
1003 IF ( get_pk_for_validation(
1004 new_references.fund_id
1005 )
1006 ) THEN
1007 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1008 igs_ge_msg_stack.add;
1009 app_exception.raise_exception;
1010 END IF;
1011 check_parent_existance;
1012 check_uniqueness;
1013 ELSIF (p_action = 'UPDATE') THEN
1014 -- Call all the procedures related to Before Update.
1015 check_parent_existance;
1016 check_uniqueness;
1017 ELSIF (p_action = 'DELETE') THEN
1018 -- Call all the procedures related to Before Delete.
1019 check_child_existance;
1020 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1021 -- Call all the procedures related to Before Insert.
1022 IF ( get_pk_for_validation (
1023 new_references.fund_id
1024 )
1025 ) THEN
1026 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
1027 igs_ge_msg_stack.add;
1028 app_exception.raise_exception;
1029 END IF;
1030 check_uniqueness;
1031 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1032 check_uniqueness;
1033 ELSIF (p_action = 'VALIDATE_DELETE') THEN
1034 check_child_existance;
1035 END IF;
1036
1037 END before_dml;
1038
1039
1040 PROCEDURE insert_row (
1041 x_rowid IN OUT NOCOPY VARCHAR2,
1042 x_fund_id IN OUT NOCOPY NUMBER,
1043 x_fund_code IN VARCHAR2,
1044 x_ci_cal_type IN VARCHAR2,
1045 x_ci_sequence_number IN NUMBER,
1046 x_description IN VARCHAR2,
1047 x_discontinue_fund IN VARCHAR2,
1048 x_entitlement IN VARCHAR2,
1049 x_auto_pkg IN VARCHAR2,
1050 x_self_help IN VARCHAR2,
1051 x_allow_man_pkg IN VARCHAR2,
1052 x_update_need IN VARCHAR2,
1053 x_disburse_fund IN VARCHAR2,
1054 x_available_amt IN NUMBER,
1055 x_offered_amt IN NUMBER,
1056 x_pending_amt IN NUMBER,
1057 x_accepted_amt IN NUMBER,
1058 x_declined_amt IN NUMBER,
1059 x_cancelled_amt IN NUMBER,
1060 x_remaining_amt IN NUMBER,
1061 x_enrollment_status IN VARCHAR2,
1062 x_prn_award_letter IN VARCHAR2,
1063 x_over_award_amt IN NUMBER,
1064 x_over_award_perct IN NUMBER,
1065 x_min_award_amt IN NUMBER,
1066 x_max_award_amt IN NUMBER,
1067 x_max_yearly_amt IN NUMBER,
1068 x_max_life_amt IN NUMBER,
1069 x_max_life_term IN NUMBER,
1070 x_fm_fc_methd IN VARCHAR2,
1071 x_roundoff_fact IN VARCHAR2,
1072 x_replace_fc IN VARCHAR2,
1073 x_allow_overaward IN VARCHAR2,
1074 x_pckg_awd_stat IN VARCHAR2,
1075 x_org_record_req IN VARCHAR2,
1076 x_disb_record_req IN VARCHAR2,
1077 x_prom_note_req IN VARCHAR2,
1078 x_min_num_disb IN NUMBER,
1079 x_max_num_disb IN NUMBER,
1080 x_fee_type IN VARCHAR2,
1081 x_total_offered IN NUMBER,
1082 x_total_accepted IN NUMBER,
1083 x_total_declined IN NUMBER,
1084 x_total_revoked IN NUMBER,
1085 x_total_cancelled IN NUMBER,
1086 x_total_disbursed IN NUMBER,
1087 x_total_committed IN NUMBER,
1088 x_committed_amt IN NUMBER,
1089 x_disbursed_amt IN NUMBER,
1090 x_awd_notice_txt IN VARCHAR2,
1091 x_attribute_category IN VARCHAR2,
1092 x_attribute1 IN VARCHAR2,
1093 x_attribute2 IN VARCHAR2,
1094 x_attribute3 IN VARCHAR2,
1095 x_attribute4 IN VARCHAR2,
1096 x_attribute5 IN VARCHAR2,
1097 x_attribute6 IN VARCHAR2,
1098 x_attribute7 IN VARCHAR2,
1099 x_attribute8 IN VARCHAR2,
1100 x_attribute9 IN VARCHAR2,
1101 x_attribute10 IN VARCHAR2,
1102 x_attribute11 IN VARCHAR2,
1103 x_attribute12 IN VARCHAR2,
1104 x_attribute13 IN VARCHAR2,
1105 x_attribute14 IN VARCHAR2,
1106 x_attribute15 IN VARCHAR2,
1107 x_attribute16 IN VARCHAR2,
1108 x_attribute17 IN VARCHAR2,
1109 x_attribute18 IN VARCHAR2,
1110 x_attribute19 IN VARCHAR2,
1111 x_attribute20 IN VARCHAR2,
1112 x_disb_verf_da IN VARCHAR2,
1113 x_fund_exp_da IN VARCHAR2,
1114 x_nslds_disb_da IN VARCHAR2,
1115 x_disb_exp_da IN VARCHAR2,
1116 x_fund_recv_reqd IN VARCHAR2,
1117 x_show_on_bill IN VARCHAR2,
1118 x_bill_desc IN VARCHAR2,
1119 x_credit_type_id IN NUMBER,
1120 x_spnsr_ref_num IN VARCHAR2,
1121 x_party_id IN VARCHAR2,
1122 x_spnsr_fee_type IN VARCHAR2,
1123 x_min_credit_points IN NUMBER,
1124 x_group_id IN NUMBER,
1125 x_spnsr_attribute_category IN VARCHAR2,
1126 x_spnsr_attribute1 IN VARCHAR2,
1127 x_spnsr_attribute2 IN VARCHAR2,
1128 x_spnsr_attribute3 IN VARCHAR2,
1129 x_spnsr_attribute4 IN VARCHAR2,
1130 x_spnsr_attribute5 IN VARCHAR2,
1131 x_spnsr_attribute6 IN VARCHAR2,
1132 x_spnsr_attribute7 IN VARCHAR2,
1133 x_spnsr_attribute8 IN VARCHAR2,
1134 x_spnsr_attribute9 IN VARCHAR2,
1135 x_spnsr_attribute10 IN VARCHAR2,
1136 x_spnsr_attribute11 IN VARCHAR2,
1137 x_spnsr_attribute12 IN VARCHAR2,
1138 x_spnsr_attribute13 IN VARCHAR2,
1139 x_spnsr_attribute14 IN VARCHAR2,
1140 x_spnsr_attribute15 IN VARCHAR2,
1141 x_spnsr_attribute16 IN VARCHAR2,
1142 x_spnsr_attribute17 IN VARCHAR2,
1143 x_spnsr_attribute18 IN VARCHAR2,
1144 x_spnsr_attribute19 IN VARCHAR2,
1145 x_spnsr_attribute20 IN VARCHAR2,
1146 x_threshold_perct IN NUMBER,
1147 x_threshold_value IN NUMBER,
1148 x_ver_app_stat_override IN VARCHAR2,
1149 x_gift_aid IN VARCHAR2,
1150 x_send_without_doc IN VARCHAR2, -- Added as part of Bug # 2758812
1151 x_mode IN VARCHAR2,
1152 x_re_pkg_verif_flag IN VARCHAR2,
1153 x_donot_repkg_if_code IN VARCHAR2,
1154 x_lock_award_flag IN VARCHAR2,
1155 x_disb_rounding_code IN VARCHAR2 DEFAULT NULL,
1156 x_view_only_flag IN VARCHAR2 DEFAULT NULL,
1157 x_accept_less_amt_flag IN VARCHAR2 DEFAULT NULL,
1158 x_allow_inc_post_accept_flag IN VARCHAR2 DEFAULT NULL,
1159 x_min_increase_amt IN NUMBER DEFAULT NULL,
1160 x_allow_dec_post_accept_flag IN VARCHAR2 DEFAULT NULL,
1161 x_min_decrease_amt IN NUMBER DEFAULT NULL,
1162 x_allow_decln_post_accept_flag IN VARCHAR2 DEFAULT NULL,
1163 x_status_after_decline IN VARCHAR2 DEFAULT NULL,
1164 x_fund_information_txt IN VARCHAR2 DEFAULT NULL
1165 ) AS
1166 /*
1167 || Created By : cdcruz
1168 || Created On : 14-NOV-2000
1169 || Purpose : Handles the INSERT DML logic for the table.
1170 || Known limitations, enhancements or remarks :
1171 || Change History :
1172 || Who When What
1173 || museshad 14-Jul-2005 FA 140 - Changes relating to the addition of new columns
1174 || museshad 25-May-2005 Build# FA157 - Bug# 4382371.
1175 || New column 'DISB_ROUNDING_CODE' has been added
1176 || to the table 'IGF_AW_FUND_MAST_ALL'.
1177 || Modified the TBH to include this.
1178 || veramach 1-NOV-2003 FA 125 - Removed elig_criteria from assigments,insert statements
1179 || (reverse chronological order - newest change first)
1180 */
1181 CURSOR c IS
1182 SELECT rowid
1183 FROM igf_aw_fund_mast_all
1184 WHERE fund_id = x_fund_id;
1185
1186 x_last_update_date DATE;
1187 x_last_updated_by NUMBER;
1188 x_last_update_login NUMBER;
1189 x_request_id NUMBER;
1190 x_program_id NUMBER;
1191 x_program_application_id NUMBER;
1192 x_program_update_date DATE;
1193 l_org_id igf_aw_fund_mast_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
1194
1195 BEGIN
1196
1197 x_last_update_date := SYSDATE;
1198 IF (x_mode = 'I') THEN
1199 x_last_updated_by := 1;
1200 x_last_update_login := 0;
1201 ELSIF (x_mode = 'R') THEN
1202 x_last_updated_by := fnd_global.user_id;
1203 IF (x_last_updated_by IS NULL) THEN
1204 x_last_updated_by := -1;
1205 END IF;
1206 x_last_update_login := fnd_global.login_id;
1207 IF (x_last_update_login IS NULL) THEN
1208 x_last_update_login := -1;
1209 END IF;
1210 x_request_id := fnd_global.conc_request_id;
1211 x_program_id := fnd_global.conc_program_id;
1212 x_program_application_id := fnd_global.prog_appl_id;
1213
1214 IF (x_request_id = -1) THEN
1215 x_request_id := NULL;
1216 x_program_id := NULL;
1217 x_program_application_id := NULL;
1218 x_program_update_date := NULL;
1219 ELSE
1220 x_program_update_date := SYSDATE;
1221 END IF;
1222 ELSE
1223 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1224 igs_ge_msg_stack.add;
1225 app_exception.raise_exception;
1226 END IF;
1227
1228 select IGF_AW_FUND_MAST_S.NEXTVAL into x_fund_id from dual ;
1229
1230
1231 before_dml(
1232 p_action => 'INSERT',
1233 x_rowid => x_rowid,
1234 x_fund_id => x_fund_id,
1235 x_fund_code => x_fund_code,
1236 x_ci_cal_type => x_ci_cal_type,
1237 x_ci_sequence_number => x_ci_sequence_number,
1238 x_description => x_description,
1239 x_discontinue_fund => x_discontinue_fund,
1240 x_entitlement => x_entitlement,
1241 x_auto_pkg => x_auto_pkg,
1242 x_self_help => x_self_help,
1243 x_allow_man_pkg => x_allow_man_pkg,
1244 x_update_need => x_update_need,
1245 x_disburse_fund => x_disburse_fund,
1246 x_available_amt => x_available_amt,
1247 x_offered_amt => x_offered_amt,
1248 x_pending_amt => x_pending_amt,
1249 x_accepted_amt => x_accepted_amt,
1250 x_declined_amt => x_declined_amt,
1251 x_cancelled_amt => x_cancelled_amt,
1252 x_remaining_amt => x_remaining_amt,
1253 x_enrollment_status => x_enrollment_status,
1254 x_prn_award_letter => x_prn_award_letter,
1255 x_over_award_amt => x_over_award_amt,
1256 x_over_award_perct => x_over_award_perct,
1257 x_min_award_amt => x_min_award_amt,
1258 x_max_award_amt => x_max_award_amt,
1259 x_max_yearly_amt => x_max_yearly_amt,
1260 x_max_life_amt => x_max_life_amt,
1261 x_max_life_term => x_max_life_term,
1262 x_fm_fc_methd => x_fm_fc_methd,
1263 x_roundoff_fact => x_roundoff_fact,
1264 x_replace_fc => x_replace_fc,
1265 x_allow_overaward => x_allow_overaward,
1266 x_pckg_awd_stat => x_pckg_awd_stat,
1267 x_org_record_req => x_org_record_req,
1268 x_disb_record_req => x_disb_record_req,
1269 x_prom_note_req => x_prom_note_req,
1270 x_min_num_disb => x_min_num_disb,
1271 x_max_num_disb => x_max_num_disb,
1272 x_fee_type => x_fee_type,
1273 x_total_offered => x_total_offered,
1274 x_total_accepted => x_total_accepted,
1275 x_total_declined => x_total_declined,
1276 x_total_revoked => x_total_revoked,
1277 x_total_cancelled => x_total_cancelled,
1278 x_total_disbursed => x_total_disbursed,
1279 x_total_committed => x_total_committed,
1280 x_committed_amt => x_committed_amt,
1281 x_disbursed_amt => x_disbursed_amt,
1282 x_awd_notice_txt => x_awd_notice_txt,
1283 x_attribute_category => x_attribute_category,
1284 x_attribute1 => x_attribute1,
1285 x_attribute2 => x_attribute2,
1286 x_attribute3 => x_attribute3,
1287 x_attribute4 => x_attribute4,
1288 x_attribute5 => x_attribute5,
1289 x_attribute6 => x_attribute6,
1290 x_attribute7 => x_attribute7,
1291 x_attribute8 => x_attribute8,
1292 x_attribute9 => x_attribute9,
1293 x_attribute10 => x_attribute10,
1294 x_attribute11 => x_attribute11,
1295 x_attribute12 => x_attribute12,
1296 x_attribute13 => x_attribute13,
1297 x_attribute14 => x_attribute14,
1298 x_attribute15 => x_attribute15,
1299 x_attribute16 => x_attribute16,
1300 x_attribute17 => x_attribute17,
1301 x_attribute18 => x_attribute18,
1302 x_attribute19 => x_attribute19,
1303 x_attribute20 => x_attribute20,
1304 x_disb_verf_da => x_disb_verf_da,
1305 x_fund_exp_da => x_fund_exp_da,
1306 x_nslds_disb_da => x_nslds_disb_da,
1307 x_disb_exp_da => x_disb_exp_da,
1308 x_fund_recv_reqd => x_fund_recv_reqd,
1309 x_show_on_bill => x_show_on_bill,
1310 x_bill_desc => x_bill_desc,
1311 x_credit_type_id => x_credit_type_id,
1312 x_spnsr_ref_num => x_spnsr_ref_num,
1313 x_party_id => x_party_id,
1314 x_spnsr_fee_type => x_spnsr_fee_type,
1315 x_min_credit_points => x_min_credit_points,
1316 x_group_id => x_group_id,
1317 x_spnsr_attribute_category => x_spnsr_attribute_category,
1318 x_spnsr_attribute1 => x_spnsr_attribute1,
1319 x_spnsr_attribute2 => x_spnsr_attribute2,
1320 x_spnsr_attribute3 => x_spnsr_attribute3,
1321 x_spnsr_attribute4 => x_spnsr_attribute4,
1322 x_spnsr_attribute5 => x_spnsr_attribute5,
1323 x_spnsr_attribute6 => x_spnsr_attribute6,
1324 x_spnsr_attribute7 => x_spnsr_attribute7,
1325 x_spnsr_attribute8 => x_spnsr_attribute8,
1326 x_spnsr_attribute9 => x_spnsr_attribute9,
1327 x_spnsr_attribute10 => x_spnsr_attribute10,
1328 x_spnsr_attribute11 => x_spnsr_attribute11,
1329 x_spnsr_attribute12 => x_spnsr_attribute12,
1330 x_spnsr_attribute13 => x_spnsr_attribute13,
1331 x_spnsr_attribute14 => x_spnsr_attribute14,
1332 x_spnsr_attribute15 => x_spnsr_attribute15,
1333 x_spnsr_attribute16 => x_spnsr_attribute16,
1334 x_spnsr_attribute17 => x_spnsr_attribute17,
1335 x_spnsr_attribute18 => x_spnsr_attribute18,
1336 x_spnsr_attribute19 => x_spnsr_attribute19,
1337 x_spnsr_attribute20 => x_spnsr_attribute20,
1338 x_threshold_perct => x_threshold_perct ,
1339 x_threshold_value => x_threshold_value,
1340 x_ver_app_stat_override => x_ver_app_stat_override,
1341 x_gift_aid => x_gift_aid ,
1342 x_send_without_doc => x_send_without_doc,
1343 x_creation_date => x_last_update_date,
1344 x_created_by => x_last_updated_by,
1345 x_last_update_date => x_last_update_date,
1346 x_last_updated_by => x_last_updated_by,
1347 x_last_update_login => x_last_update_login,
1348 x_re_pkg_verif_flag => x_re_pkg_verif_flag,
1349 x_donot_repkg_if_code => x_donot_repkg_if_code,
1350 x_lock_award_flag => x_lock_award_flag,
1351 x_disb_rounding_code => x_disb_rounding_code,
1352 x_view_only_flag => x_view_only_flag,
1353 x_accept_less_amt_flag => x_accept_less_amt_flag,
1354 x_allow_inc_post_accept_flag => x_allow_inc_post_accept_flag,
1355 x_min_increase_amt => x_min_increase_amt,
1356 x_allow_dec_post_accept_flag => x_allow_dec_post_accept_flag,
1357 x_min_decrease_amt => x_min_decrease_amt,
1358 x_allow_decln_post_accept_flag => x_allow_decln_post_accept_flag,
1359 x_status_after_decline => x_status_after_decline,
1360 x_fund_information_txt => x_fund_information_txt
1361 );
1362
1363 INSERT INTO igf_aw_fund_mast_all (
1364 fund_id,
1365 fund_code,
1366 ci_cal_type,
1367 ci_sequence_number,
1368 description,
1369 discontinue_fund,
1370 entitlement,
1371 auto_pkg,
1372 self_help,
1373 allow_man_pkg,
1374 update_need,
1375 disburse_fund,
1376 available_amt,
1377 offered_amt,
1378 pending_amt,
1379 accepted_amt,
1380 declined_amt,
1381 cancelled_amt,
1382 remaining_amt,
1383 enrollment_status,
1384 prn_award_letter,
1385 over_award_amt,
1386 over_award_perct,
1387 min_award_amt,
1388 max_award_amt,
1389 max_yearly_amt,
1390 max_life_amt,
1391 max_life_term,
1392 fm_fc_methd,
1393 roundoff_fact,
1394 replace_fc,
1395 allow_overaward,
1396 pckg_awd_stat,
1397 org_record_req,
1398 disb_record_req,
1399 prom_note_req,
1400 min_num_disb,
1401 max_num_disb,
1402 fee_type,
1403 total_offered,
1404 total_accepted,
1405 total_declined,
1406 total_revoked,
1407 total_cancelled,
1408 total_disbursed,
1409 total_committed,
1410 committed_amt,
1411 disbursed_amt,
1412 awd_notice_txt,
1413 attribute_category,
1414 attribute1,
1415 attribute2,
1416 attribute3,
1417 attribute4,
1418 attribute5,
1419 attribute6,
1420 attribute7,
1421 attribute8,
1422 attribute9,
1423 attribute10,
1424 attribute11,
1425 attribute12,
1426 attribute13,
1427 attribute14,
1428 attribute15,
1429 attribute16,
1430 attribute17,
1431 attribute18,
1432 attribute19,
1433 attribute20,
1434 disb_verf_da,
1435 fund_exp_da,
1436 nslds_disb_da,
1437 disb_exp_da,
1438 fund_recv_reqd,
1439 show_on_bill,
1440 bill_desc,
1441 credit_type_id,
1442 spnsr_ref_num,
1443 party_id,
1444 spnsr_fee_type,
1445 min_credit_points,
1446 group_id,
1447 spnsr_attribute_category,
1448 spnsr_attribute1 ,
1449 spnsr_attribute2 ,
1450 spnsr_attribute3 ,
1451 spnsr_attribute4 ,
1452 spnsr_attribute5 ,
1453 spnsr_attribute6 ,
1454 spnsr_attribute7 ,
1455 spnsr_attribute8 ,
1456 spnsr_attribute9 ,
1457 spnsr_attribute10 ,
1458 spnsr_attribute11 ,
1459 spnsr_attribute12 ,
1460 spnsr_attribute13 ,
1461 spnsr_attribute14 ,
1462 spnsr_attribute15 ,
1463 spnsr_attribute16 ,
1464 spnsr_attribute17 ,
1465 spnsr_attribute18 ,
1466 spnsr_attribute19 ,
1467 spnsr_attribute20 ,
1468 threshold_perct,
1469 threshold_value,
1470 ver_app_stat_override,
1471 gift_aid,
1472 send_without_doc,
1473 re_pkg_verif_flag,
1474 donot_repkg_if_code,
1475 lock_award_flag,
1476 creation_date,
1477 created_by,
1478 last_update_date,
1479 last_updated_by,
1480 last_update_login,
1481 request_id,
1482 program_id,
1483 program_application_id,
1484 program_update_date,
1485 org_id,
1486 disb_rounding_code,
1487 view_only_flag,
1488 accept_less_amt_flag,
1489 allow_inc_post_accept_flag,
1490 min_increase_amt,
1491 allow_dec_post_accept_flag,
1492 min_decrease_amt,
1493 allow_decln_post_accept_flag,
1494 status_after_decline,
1495 fund_information_txt
1496 ) VALUES (
1497 new_references.fund_id,
1498 new_references.fund_code,
1499 new_references.ci_cal_type,
1500 new_references.ci_sequence_number,
1501 new_references.description,
1502 new_references.discontinue_fund,
1503 new_references.entitlement,
1504 new_references.auto_pkg,
1505 new_references.self_help,
1506 new_references.allow_man_pkg,
1507 new_references.update_need,
1508 new_references.disburse_fund,
1509 new_references.available_amt,
1510 new_references.offered_amt,
1511 new_references.pending_amt,
1512 new_references.accepted_amt,
1513 new_references.declined_amt,
1514 new_references.cancelled_amt,
1515 new_references.remaining_amt,
1516 new_references.enrollment_status,
1517 new_references.prn_award_letter,
1518 new_references.over_award_amt,
1519 new_references.over_award_perct,
1520 new_references.min_award_amt,
1521 new_references.max_award_amt,
1522 new_references.max_yearly_amt,
1523 new_references.max_life_amt,
1524 new_references.max_life_term,
1525 new_references.fm_fc_methd,
1526 new_references.roundoff_fact,
1527 new_references.replace_fc,
1528 new_references.allow_overaward,
1529 new_references.pckg_awd_stat,
1530 new_references.org_record_req,
1531 new_references.disb_record_req,
1532 new_references.prom_note_req,
1533 new_references.min_num_disb,
1534 new_references.max_num_disb,
1535 new_references.fee_type,
1536 new_references.total_offered,
1537 new_references.total_accepted,
1538 new_references.total_declined,
1539 new_references.total_revoked,
1540 new_references.total_cancelled,
1541 new_references.total_disbursed,
1542 new_references.total_committed,
1543 new_references.committed_amt,
1544 new_references.disbursed_amt,
1545 new_references.awd_notice_txt,
1546 new_references.attribute_category,
1547 new_references.attribute1,
1548 new_references.attribute2,
1549 new_references.attribute3,
1550 new_references.attribute4,
1551 new_references.attribute5,
1552 new_references.attribute6,
1553 new_references.attribute7,
1554 new_references.attribute8,
1555 new_references.attribute9,
1556 new_references.attribute10,
1557 new_references.attribute11,
1558 new_references.attribute12,
1559 new_references.attribute13,
1560 new_references.attribute14,
1561 new_references.attribute15,
1562 new_references.attribute16,
1563 new_references.attribute17,
1564 new_references.attribute18,
1565 new_references.attribute19,
1566 new_references.attribute20,
1567 new_references.disb_verf_da,
1568 new_references.fund_exp_da,
1569 new_references.nslds_disb_da,
1570 new_references.disb_exp_da,
1571 new_references.fund_recv_reqd,
1572 new_references.show_on_bill,
1573 new_references.bill_desc,
1574 new_references.credit_type_id,
1575
1576 new_references.spnsr_ref_num,
1577 new_references.party_id,
1578 new_references.spnsr_fee_type,
1579 new_references.min_credit_points,
1580 new_references.group_id,
1581 new_references.spnsr_attribute_category,
1582 new_references.spnsr_attribute1,
1583 new_references.spnsr_attribute2,
1584 new_references.spnsr_attribute3,
1585 new_references.spnsr_attribute4,
1586 new_references.spnsr_attribute5,
1587 new_references.spnsr_attribute6,
1588 new_references.spnsr_attribute7,
1589 new_references.spnsr_attribute8,
1590 new_references.spnsr_attribute9,
1591 new_references.spnsr_attribute10,
1592 new_references.spnsr_attribute11,
1593 new_references.spnsr_attribute12,
1594 new_references.spnsr_attribute13,
1595 new_references.spnsr_attribute14,
1596 new_references.spnsr_attribute15,
1597 new_references.spnsr_attribute16,
1598 new_references.spnsr_attribute17,
1599 new_references.spnsr_attribute18,
1600 new_references.spnsr_attribute19,
1601 new_references.spnsr_attribute20,
1602 new_references.threshold_perct,
1603 new_references.threshold_value,
1604 new_references.ver_app_stat_override,
1605 new_references.gift_aid,
1606 new_references.send_without_doc,
1607 new_references.re_pkg_verif_flag,
1608 new_references.donot_repkg_if_code,
1609 new_references.lock_award_flag,
1610 x_last_update_date,
1611 x_last_updated_by,
1612 x_last_update_date,
1613 x_last_updated_by,
1614 x_last_update_login ,
1615 x_request_id,
1616 x_program_id,
1617 x_program_application_id,
1618 x_program_update_date ,
1619 l_org_id,
1620 new_references.disb_rounding_code,
1621 new_references.view_only_flag,
1622 new_references.accept_less_amt_flag,
1623 new_references.allow_inc_post_accept_flag,
1624 new_references.min_increase_amt,
1625 new_references.allow_dec_post_accept_flag,
1626 new_references.min_decrease_amt,
1627 new_references.allow_decln_post_accept_flag,
1628 new_references.status_after_decline,
1629 new_references.fund_information_txt
1630 );
1631
1632 OPEN c;
1633 FETCH c INTO x_rowid;
1634 IF (c%NOTFOUND) THEN
1635 CLOSE c;
1636 RAISE NO_DATA_FOUND;
1637 END IF;
1638 CLOSE c;
1639
1640 END insert_row;
1641
1642
1643 PROCEDURE lock_row (
1644 x_rowid IN VARCHAR2,
1645 x_fund_id IN NUMBER,
1646 x_fund_code IN VARCHAR2,
1647 x_ci_cal_type IN VARCHAR2,
1648 x_ci_sequence_number IN NUMBER,
1649 x_description IN VARCHAR2,
1650 x_discontinue_fund IN VARCHAR2,
1651 x_entitlement IN VARCHAR2,
1652 x_auto_pkg IN VARCHAR2,
1653 x_self_help IN VARCHAR2,
1654 x_allow_man_pkg IN VARCHAR2,
1655 x_update_need IN VARCHAR2,
1656 x_disburse_fund IN VARCHAR2,
1657 x_available_amt IN NUMBER,
1658 x_offered_amt IN NUMBER,
1659 x_pending_amt IN NUMBER,
1660 x_accepted_amt IN NUMBER,
1661 x_declined_amt IN NUMBER,
1662 x_cancelled_amt IN NUMBER,
1663 x_remaining_amt IN NUMBER,
1664 x_enrollment_status IN VARCHAR2,
1665 x_prn_award_letter IN VARCHAR2,
1666 x_over_award_amt IN NUMBER,
1667 x_over_award_perct IN NUMBER,
1668 x_min_award_amt IN NUMBER,
1669 x_max_award_amt IN NUMBER,
1670 x_max_yearly_amt IN NUMBER,
1671 x_max_life_amt IN NUMBER,
1672 x_max_life_term IN NUMBER,
1673 x_fm_fc_methd IN VARCHAR2,
1674 x_roundoff_fact IN VARCHAR2,
1675 x_replace_fc IN VARCHAR2,
1676 x_allow_overaward IN VARCHAR2,
1677 x_pckg_awd_stat IN VARCHAR2,
1678 x_org_record_req IN VARCHAR2,
1679 x_disb_record_req IN VARCHAR2,
1680 x_prom_note_req IN VARCHAR2,
1681 x_min_num_disb IN NUMBER,
1682 x_max_num_disb IN NUMBER,
1683 x_fee_type IN VARCHAR2,
1684 x_total_offered IN NUMBER,
1685 x_total_accepted IN NUMBER,
1686 x_total_declined IN NUMBER,
1687 x_total_revoked IN NUMBER,
1688 x_total_cancelled IN NUMBER,
1689 x_total_disbursed IN NUMBER,
1690 x_total_committed IN NUMBER,
1691 x_committed_amt IN NUMBER,
1692 x_disbursed_amt IN NUMBER,
1693 x_awd_notice_txt IN VARCHAR2,
1694 x_attribute_category IN VARCHAR2,
1695 x_attribute1 IN VARCHAR2,
1696 x_attribute2 IN VARCHAR2,
1697 x_attribute3 IN VARCHAR2,
1698 x_attribute4 IN VARCHAR2,
1699 x_attribute5 IN VARCHAR2,
1700 x_attribute6 IN VARCHAR2,
1701 x_attribute7 IN VARCHAR2,
1702 x_attribute8 IN VARCHAR2,
1703 x_attribute9 IN VARCHAR2,
1704 x_attribute10 IN VARCHAR2,
1705 x_attribute11 IN VARCHAR2,
1706 x_attribute12 IN VARCHAR2,
1707 x_attribute13 IN VARCHAR2,
1708 x_attribute14 IN VARCHAR2,
1709 x_attribute15 IN VARCHAR2,
1710 x_attribute16 IN VARCHAR2,
1711 x_attribute17 IN VARCHAR2,
1712 x_attribute18 IN VARCHAR2,
1713 x_attribute19 IN VARCHAR2,
1714 x_attribute20 IN VARCHAR2,
1715 x_disb_verf_da IN VARCHAR2,
1716 x_fund_exp_da IN VARCHAR2,
1717 x_nslds_disb_da IN VARCHAR2,
1718 x_disb_exp_da IN VARCHAR2,
1719 x_fund_recv_reqd IN VARCHAR2,
1720 x_show_on_bill IN VARCHAR2,
1721 x_bill_desc IN VARCHAR2,
1722 x_credit_type_id IN NUMBER ,
1723 x_spnsr_ref_num IN VARCHAR2,
1724 x_party_id IN VARCHAR2,
1725 x_spnsr_fee_type IN VARCHAR2,
1726 x_min_credit_points IN NUMBER ,
1727 x_group_id IN NUMBER ,
1728 x_spnsr_attribute_category IN VARCHAR2,
1729 x_spnsr_attribute1 IN VARCHAR2,
1730 x_spnsr_attribute2 IN VARCHAR2,
1731 x_spnsr_attribute3 IN VARCHAR2,
1732 x_spnsr_attribute4 IN VARCHAR2,
1733 x_spnsr_attribute5 IN VARCHAR2,
1734 x_spnsr_attribute6 IN VARCHAR2,
1735 x_spnsr_attribute7 IN VARCHAR2,
1736 x_spnsr_attribute8 IN VARCHAR2,
1737 x_spnsr_attribute9 IN VARCHAR2,
1738 x_spnsr_attribute10 IN VARCHAR2,
1739 x_spnsr_attribute11 IN VARCHAR2,
1740 x_spnsr_attribute12 IN VARCHAR2,
1741 x_spnsr_attribute13 IN VARCHAR2,
1742 x_spnsr_attribute14 IN VARCHAR2,
1743 x_spnsr_attribute15 IN VARCHAR2,
1744 x_spnsr_attribute16 IN VARCHAR2,
1745 x_spnsr_attribute17 IN VARCHAR2,
1746 x_spnsr_attribute18 IN VARCHAR2,
1747 x_spnsr_attribute19 IN VARCHAR2,
1748 x_spnsr_attribute20 IN VARCHAR2,
1749 x_threshold_perct IN NUMBER,
1750 x_threshold_value IN NUMBER,
1751 x_ver_app_stat_override IN VARCHAR2,
1752 x_gift_aid IN VARCHAR2,
1753 x_send_without_doc IN VARCHAR2, -- Added as part of Bug # 2758812
1754 x_re_pkg_verif_flag IN VARCHAR2,
1755 x_donot_repkg_if_code IN VARCHAR2,
1756 x_lock_award_flag IN VARCHAR2,
1757 x_disb_rounding_code IN VARCHAR2 DEFAULT NULL,
1758 x_view_only_flag IN VARCHAR2 DEFAULT NULL,
1759 x_accept_less_amt_flag IN VARCHAR2 DEFAULT NULL,
1760 x_allow_inc_post_accept_flag IN VARCHAR2 DEFAULT NULL,
1761 x_min_increase_amt IN NUMBER DEFAULT NULL,
1762 x_allow_dec_post_accept_flag IN VARCHAR2 DEFAULT NULL,
1763 x_min_decrease_amt IN NUMBER DEFAULT NULL,
1764 x_allow_decln_post_accept_flag IN VARCHAR2 DEFAULT NULL,
1765 x_status_after_decline IN VARCHAR2 DEFAULT NULL,
1766 x_fund_information_txt IN VARCHAR2 DEFAULT NULL
1767
1768 ) AS
1769 /*
1770 || Created By : cdcruz
1771 || Created On : 14-NOV-2000
1772 || Purpose : Handles the LOCK mechanism for the table.
1773 || Known limitations, enhancements or remarks :
1774 || Change History :
1775 || Who When What
1776 || museshad 14-Jul-2005 Build FA 140 - Changes relating to the addition
1777 || of new columns
1778 || museshad 25-May-2005 Build# FA157 - Bug# 4382371.
1779 || New column 'DISB_ROUNDING_CODE' has been added
1780 || to the table 'IGF_AW_FUND_MAST_ALL'.
1781 || Modified the TBH to include this.
1782 || veramach 1-NOV-2003 FA 125 - Removed elig_criteria from assigments,select statements
1783 || (reverse chronological order - newest change first)
1784 */
1785 CURSOR c1 IS
1786 SELECT
1787 fund_code,
1788 ci_cal_type,
1789 ci_sequence_number,
1790 description,
1791 discontinue_fund,
1792 entitlement,
1793 auto_pkg,
1794 self_help,
1795 allow_man_pkg,
1796 update_need,
1797 disburse_fund,
1798 available_amt,
1799 offered_amt,
1800 pending_amt,
1801 accepted_amt,
1802 declined_amt,
1803 cancelled_amt,
1804 remaining_amt,
1805 enrollment_status,
1806 prn_award_letter,
1807 over_award_amt,
1808 over_award_perct,
1809 min_award_amt,
1810 max_award_amt,
1811 max_yearly_amt,
1812 max_life_amt,
1813 max_life_term,
1814 fm_fc_methd,
1815 roundoff_fact,
1816 replace_fc,
1817 allow_overaward,
1818 pckg_awd_stat,
1819 org_record_req,
1820 disb_record_req,
1821 prom_note_req,
1822 min_num_disb,
1823 max_num_disb,
1824 fee_type,
1825 total_offered,
1826 total_accepted,
1827 total_declined,
1828 total_revoked,
1829 total_cancelled,
1830 total_disbursed,
1831 total_committed,
1832 committed_amt,
1833 disbursed_amt,
1834 awd_notice_txt,
1835 attribute_category,
1836 attribute1,
1837 attribute2,
1838 attribute3,
1839 attribute4,
1840 attribute5,
1841 attribute6,
1842 attribute7,
1843 attribute8,
1844 attribute9,
1845 attribute10,
1846 attribute11,
1847 attribute12,
1848 attribute13,
1849 attribute14,
1850 attribute15,
1851 attribute16,
1852 attribute17,
1853 attribute18,
1854 attribute19,
1855 attribute20,
1856 disb_verf_da,
1857 fund_exp_da,
1858 nslds_disb_da,
1859 disb_exp_da,
1860 fund_recv_reqd,
1861 show_on_bill,
1862 bill_desc,
1863 credit_type_id,
1864 spnsr_ref_num,
1865 party_id,
1866 spnsr_fee_type,
1867 min_credit_points,
1868 group_id,
1869 spnsr_attribute_category,
1870 spnsr_attribute1 ,
1871 spnsr_attribute2 ,
1872 spnsr_attribute3 ,
1873 spnsr_attribute4 ,
1874 spnsr_attribute5 ,
1875 spnsr_attribute6 ,
1876 spnsr_attribute7 ,
1877 spnsr_attribute8 ,
1878 spnsr_attribute9 ,
1879 spnsr_attribute10 ,
1880 spnsr_attribute11 ,
1881 spnsr_attribute12 ,
1882 spnsr_attribute13 ,
1883 spnsr_attribute14 ,
1884 spnsr_attribute15 ,
1885 spnsr_attribute16 ,
1886 spnsr_attribute17 ,
1887 spnsr_attribute18 ,
1888 spnsr_attribute19 ,
1889 spnsr_attribute20 ,
1890 threshold_perct,
1891 threshold_value,
1892 ver_app_stat_override ,
1893 gift_aid,
1894 send_without_doc,
1895 re_pkg_verif_flag,
1896 donot_repkg_if_code,
1897 lock_award_flag,
1898 disb_rounding_code,
1899 view_only_flag,
1900 accept_less_amt_flag,
1901 allow_inc_post_accept_flag,
1902 min_increase_amt,
1903 allow_dec_post_accept_flag,
1904 min_decrease_amt,
1905 allow_decln_post_accept_flag,
1906 status_after_decline,
1907 fund_information_txt
1908 FROM igf_aw_fund_mast_all
1909 WHERE rowid = x_rowid
1910 FOR UPDATE NOWAIT;
1911
1912 tlinfo c1%ROWTYPE;
1913
1914 BEGIN
1915
1916 OPEN c1;
1917 FETCH c1 INTO tlinfo;
1918 IF (c1%notfound) THEN
1919 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1920 igs_ge_msg_stack.add;
1921 CLOSE c1;
1922 app_exception.raise_exception;
1923 RETURN;
1924 END IF;
1925 CLOSE c1;
1926
1927 IF (
1928 (tlinfo.fund_code = x_fund_code)
1929 AND (tlinfo.ci_cal_type = x_ci_cal_type)
1930 AND (tlinfo.ci_sequence_number = x_ci_sequence_number)
1931 AND ((tlinfo.description = x_description) OR ((tlinfo.description IS NULL) AND (X_description IS NULL)))
1932 AND (tlinfo.discontinue_fund = x_discontinue_fund)
1933 AND ((tlinfo.entitlement = x_entitlement) OR ((tlinfo.entitlement IS NULL) AND (X_entitlement IS NULL)))
1934 AND (tlinfo.auto_pkg = x_auto_pkg)
1935 AND ((tlinfo.self_help = x_self_help) OR ((tlinfo.self_help IS NULL) AND (X_self_help IS NULL)))
1936 AND (tlinfo.allow_man_pkg = x_allow_man_pkg)
1937 AND (tlinfo.update_need = x_update_need)
1938 AND (tlinfo.disburse_fund = x_disburse_fund)
1939 AND ((tlinfo.available_amt = x_available_amt) OR ((tlinfo.available_amt IS NULL) AND (X_available_amt IS NULL)))
1940 AND ((tlinfo.offered_amt = x_offered_amt) OR ((tlinfo.offered_amt IS NULL) AND (X_offered_amt IS NULL)))
1941 AND ((tlinfo.pending_amt = x_pending_amt) OR ((tlinfo.pending_amt IS NULL) AND (X_pending_amt IS NULL)))
1942 AND ((tlinfo.accepted_amt = x_accepted_amt) OR ((tlinfo.accepted_amt IS NULL) AND (X_accepted_amt IS NULL)))
1943 AND ((tlinfo.declined_amt = x_declined_amt) OR ((tlinfo.declined_amt IS NULL) AND (X_declined_amt IS NULL)))
1944 AND ((tlinfo.cancelled_amt = x_cancelled_amt) OR ((tlinfo.cancelled_amt IS NULL) AND (X_cancelled_amt IS NULL)))
1945 AND ((tlinfo.remaining_amt = x_remaining_amt) OR ((tlinfo.remaining_amt IS NULL) AND (X_remaining_amt IS NULL)))
1946 AND ((tlinfo.enrollment_status = x_enrollment_status) OR ((tlinfo.enrollment_status IS NULL) AND (X_enrollment_status IS NULL)))
1947 AND ((tlinfo.prn_award_letter = x_prn_award_letter) OR ((tlinfo.prn_award_letter IS NULL) AND (X_prn_award_letter IS NULL)))
1948 AND ((tlinfo.over_award_amt = x_over_award_amt) OR ((tlinfo.over_award_amt IS NULL) AND (X_over_award_amt IS NULL)))
1949 AND ((tlinfo.over_award_perct = x_over_award_perct) OR ((tlinfo.over_award_perct IS NULL) AND (X_over_award_perct IS NULL)))
1950 AND ((tlinfo.min_award_amt = x_min_award_amt) OR ((tlinfo.min_award_amt IS NULL) AND (X_min_award_amt IS NULL)))
1951 AND ((tlinfo.max_award_amt = x_max_award_amt) OR ((tlinfo.max_award_amt IS NULL) AND (X_max_award_amt IS NULL)))
1952 AND ((tlinfo.max_yearly_amt = x_max_yearly_amt) OR ((tlinfo.max_yearly_amt IS NULL) AND (X_max_yearly_amt IS NULL)))
1953 AND ((tlinfo.max_life_amt = x_max_life_amt) OR ((tlinfo.max_life_amt IS NULL) AND (X_max_life_amt IS NULL)))
1954 AND ((tlinfo.max_life_term = x_max_life_term) OR ((tlinfo.max_life_term IS NULL) AND (X_max_life_term IS NULL)))
1955 AND ((tlinfo.roundoff_fact = x_roundoff_fact) OR ((tlinfo.roundoff_fact IS NULL) AND (X_roundoff_fact IS NULL)))
1956 AND ((tlinfo.replace_fc = x_replace_fc) OR ((tlinfo.replace_fc IS NULL) AND (X_replace_fc IS NULL)))
1957 AND ((tlinfo.allow_overaward = x_allow_overaward) OR ((tlinfo.allow_overaward IS NULL) AND (X_allow_overaward IS NULL)))
1958 AND ((tlinfo.pckg_awd_stat = x_pckg_awd_stat) OR ((tlinfo.pckg_awd_stat IS NULL) AND (X_pckg_awd_stat IS NULL)))
1959 AND ((tlinfo.org_record_req = x_org_record_req) OR ((tlinfo.org_record_req IS NULL) AND (X_org_record_req IS NULL)))
1960 AND ((tlinfo.disb_record_req = x_disb_record_req) OR ((tlinfo.disb_record_req IS NULL) AND (X_disb_record_req IS NULL)))
1961 AND ((tlinfo.prom_note_req = x_prom_note_req) OR ((tlinfo.prom_note_req IS NULL) AND (X_prom_note_req IS NULL)))
1962 AND ((tlinfo.min_num_disb = x_min_num_disb) OR ((tlinfo.min_num_disb IS NULL) AND (X_min_num_disb IS NULL)))
1963 AND ((tlinfo.max_num_disb = x_max_num_disb) OR ((tlinfo.max_num_disb IS NULL) AND (X_max_num_disb IS NULL)))
1964 AND ((tlinfo.fee_type = x_fee_type) OR ((tlinfo.fee_type IS NULL) AND (X_fee_type IS NULL)))
1965 AND ((tlinfo.total_offered = x_total_offered) OR ((tlinfo.total_offered IS NULL) AND (X_total_offered IS NULL)))
1966 AND ((tlinfo.total_accepted = x_total_accepted) OR ((tlinfo.total_accepted IS NULL) AND (X_total_accepted IS NULL)))
1967 AND ((tlinfo.total_declined = x_total_declined) OR ((tlinfo.total_declined IS NULL) AND (X_total_declined IS NULL)))
1968 AND ((tlinfo.total_revoked = x_total_revoked) OR ((tlinfo.total_revoked IS NULL) AND (X_total_revoked IS NULL)))
1969 AND ((tlinfo.total_cancelled = x_total_cancelled) OR ((tlinfo.total_cancelled IS NULL) AND (X_total_cancelled IS NULL)))
1970 AND ((tlinfo.total_disbursed = x_total_disbursed) OR ((tlinfo.total_disbursed IS NULL) AND (X_total_disbursed IS NULL)))
1971 AND ((tlinfo.total_committed = x_total_committed) OR ((tlinfo.total_committed IS NULL) AND (X_total_committed IS NULL)))
1972 AND ((tlinfo.committed_amt = x_committed_amt) OR ((tlinfo.committed_amt IS NULL) AND (X_committed_amt IS NULL)))
1973 AND ((tlinfo.disbursed_amt = x_disbursed_amt) OR ((tlinfo.disbursed_amt IS NULL) AND (X_disbursed_amt IS NULL)))
1974 AND ((tlinfo.awd_notice_txt = x_awd_notice_txt) OR ((tlinfo.awd_notice_txt IS NULL) AND (X_awd_notice_txt IS NULL)))
1975 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1976 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1977 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1978 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1979 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1980 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1981 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1982 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1983 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1984 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1985 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1986 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1987 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1988 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1989 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1990 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1991 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1992 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1993 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1994 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1995 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1996 AND ((tlinfo.disb_verf_da = x_disb_verf_da) OR ((tlinfo.disb_verf_da IS NULL) AND (x_disb_verf_da IS NULL)))
1997 AND ((tlinfo.fund_exp_da = x_fund_exp_da) OR ((tlinfo.fund_exp_da IS NULL) AND (x_fund_exp_da IS NULL)))
1998 AND ((tlinfo.nslds_disb_da = x_nslds_disb_da) OR ((tlinfo.nslds_disb_da IS NULL) AND (x_nslds_disb_da IS NULL)))
1999 AND ((tlinfo.disb_exp_da = x_disb_exp_da) OR ((tlinfo.disb_exp_da IS NULL) AND (x_disb_exp_da IS NULL)))
2000 AND ((tlinfo.fund_recv_reqd = x_fund_recv_reqd) OR ((tlinfo.fund_recv_reqd IS NULL) AND (x_fund_recv_reqd IS NULL)))
2001 AND ((tlinfo.show_on_bill = x_show_on_bill) OR ((tlinfo.show_on_bill IS NULL) AND (x_show_on_bill IS NULL)))
2002 AND ((tlinfo.bill_desc = x_bill_desc) OR ((tlinfo.bill_desc IS NULL) AND (x_bill_desc IS NULL)))
2003 AND ((tlinfo.credit_type_id = x_credit_type_id) OR ((tlinfo.credit_type_id IS NULL) AND (x_credit_type_id IS NULL)))
2004 AND ((tlinfo.spnsr_ref_num = x_spnsr_ref_num) OR ((tlinfo.spnsr_ref_num IS NULL) AND (x_spnsr_ref_num IS NULL)))
2005 AND ((tlinfo.party_id = x_party_id) OR ((tlinfo.party_id IS NULL) AND (x_party_id IS NULL)))
2006 AND ((tlinfo.spnsr_fee_type = x_spnsr_fee_type) OR ((tlinfo.spnsr_fee_type IS NULL) AND (x_spnsr_fee_type IS NULL)))
2007 AND ((tlinfo.min_credit_points = x_min_credit_points) OR ((tlinfo.min_credit_points IS NULL) AND (x_min_credit_points IS NULL)))
2008 AND ((tlinfo.group_id = x_group_id) OR ((tlinfo.group_id IS NULL) AND (x_group_id IS NULL)))
2009 AND ((tlinfo.spnsr_attribute_category = x_spnsr_attribute_category) OR ((tlinfo.spnsr_attribute_category IS NULL) AND (X_spnsr_attribute_category IS NULL)))
2010 AND ((tlinfo.spnsr_attribute1 = x_spnsr_attribute1) OR ((tlinfo.spnsr_attribute1 IS NULL) AND (X_spnsr_attribute1 IS NULL)))
2011 AND ((tlinfo.spnsr_attribute2 = x_spnsr_attribute2) OR ((tlinfo.spnsr_attribute2 IS NULL) AND (X_spnsr_attribute2 IS NULL)))
2012 AND ((tlinfo.spnsr_attribute3 = x_spnsr_attribute3) OR ((tlinfo.spnsr_attribute3 IS NULL) AND (X_spnsr_attribute3 IS NULL)))
2013 AND ((tlinfo.spnsr_attribute4 = x_spnsr_attribute4) OR ((tlinfo.spnsr_attribute4 IS NULL) AND (X_spnsr_attribute4 IS NULL)))
2014 AND ((tlinfo.spnsr_attribute5 = x_spnsr_attribute5) OR ((tlinfo.spnsr_attribute5 IS NULL) AND (X_spnsr_attribute5 IS NULL)))
2015 AND ((tlinfo.spnsr_attribute6 = x_spnsr_attribute6) OR ((tlinfo.spnsr_attribute6 IS NULL) AND (X_spnsr_attribute6 IS NULL)))
2016 AND ((tlinfo.spnsr_attribute7 = x_spnsr_attribute7) OR ((tlinfo.spnsr_attribute7 IS NULL) AND (X_spnsr_attribute7 IS NULL)))
2017 AND ((tlinfo.spnsr_attribute8 = x_spnsr_attribute8) OR ((tlinfo.spnsr_attribute8 IS NULL) AND (X_spnsr_attribute8 IS NULL)))
2018 AND ((tlinfo.spnsr_attribute9 = x_spnsr_attribute9) OR ((tlinfo.spnsr_attribute9 IS NULL) AND (X_spnsr_attribute9 IS NULL)))
2019 AND ((tlinfo.spnsr_attribute10 = x_spnsr_attribute10) OR ((tlinfo.spnsr_attribute10 IS NULL) AND (X_spnsr_attribute10 IS NULL)))
2020 AND ((tlinfo.spnsr_attribute11 = x_spnsr_attribute11) OR ((tlinfo.spnsr_attribute11 IS NULL) AND (X_spnsr_attribute11 IS NULL)))
2021 AND ((tlinfo.spnsr_attribute12 = x_spnsr_attribute12) OR ((tlinfo.spnsr_attribute12 IS NULL) AND (X_spnsr_attribute12 IS NULL)))
2022 AND ((tlinfo.spnsr_attribute13 = x_spnsr_attribute13) OR ((tlinfo.spnsr_attribute13 IS NULL) AND (X_spnsr_attribute13 IS NULL)))
2023 AND ((tlinfo.spnsr_attribute14 = x_spnsr_attribute14) OR ((tlinfo.spnsr_attribute14 IS NULL) AND (X_spnsr_attribute14 IS NULL)))
2024 AND ((tlinfo.spnsr_attribute15 = x_spnsr_attribute15) OR ((tlinfo.spnsr_attribute15 IS NULL) AND (X_spnsr_attribute15 IS NULL)))
2025 AND ((tlinfo.spnsr_attribute16 = x_spnsr_attribute16) OR ((tlinfo.spnsr_attribute16 IS NULL) AND (X_spnsr_attribute16 IS NULL)))
2026 AND ((tlinfo.spnsr_attribute17 = x_spnsr_attribute17) OR ((tlinfo.spnsr_attribute17 IS NULL) AND (X_spnsr_attribute17 IS NULL)))
2027 AND ((tlinfo.spnsr_attribute18 = x_spnsr_attribute18) OR ((tlinfo.spnsr_attribute18 IS NULL) AND (X_spnsr_attribute18 IS NULL)))
2028 AND ((tlinfo.spnsr_attribute19 = x_spnsr_attribute19) OR ((tlinfo.spnsr_attribute19 IS NULL) AND (X_spnsr_attribute19 IS NULL)))
2029 AND ((tlinfo.spnsr_attribute20 = x_spnsr_attribute20) OR ((tlinfo.spnsr_attribute20 IS NULL) AND (X_spnsr_attribute20 IS NULL)))
2030 AND ((tlinfo.threshold_perct = x_threshold_perct) OR ((tlinfo.threshold_perct IS NULL) AND (x_threshold_perct IS NULL)))
2031 AND ((tlinfo.threshold_value = x_threshold_value) OR ((tlinfo.threshold_value IS NULL) AND (x_threshold_value IS NULL)))
2032 AND ((tlinfo.ver_app_stat_override = x_ver_app_stat_override) OR ((tlinfo.ver_app_stat_override IS NULL) AND (x_ver_app_stat_override IS NULL)))
2033 AND ((tlinfo.gift_aid = x_gift_aid) OR ((tlinfo.gift_aid IS NULL) AND (x_gift_aid IS NULL)))
2034 AND ((tlinfo.send_without_doc = x_send_without_doc) OR ((tlinfo.send_without_doc IS NULL) AND (x_send_without_doc IS NULL)))
2035 AND ((tlinfo.re_pkg_verif_flag = x_re_pkg_verif_flag) OR ((tlinfo.re_pkg_verif_flag IS NULL) AND (x_re_pkg_verif_flag IS NULL)))
2036 AND ((tlinfo.donot_repkg_if_code = x_donot_repkg_if_code) OR ((tlinfo.donot_repkg_if_code IS NULL) AND (x_donot_repkg_if_code IS NULL)))
2037 AND ((tlinfo.lock_award_flag = x_lock_award_flag) OR ((tlinfo.lock_award_flag IS NULL) AND (x_lock_award_flag IS NULL)))
2038 AND ((tlinfo.disb_rounding_code = x_disb_rounding_code) OR ((tlinfo.disb_rounding_code IS NULL) AND (x_disb_rounding_code IS NULL)))
2039 AND ((tlinfo.view_only_flag = x_view_only_flag) OR ((tlinfo.view_only_flag IS NULL) AND (x_view_only_flag IS NULL)))
2040 AND ((tlinfo.accept_less_amt_flag = x_accept_less_amt_flag) OR ((tlinfo.accept_less_amt_flag IS NULL) AND (x_accept_less_amt_flag IS NULL)))
2041 AND ((tlinfo.allow_inc_post_accept_flag = x_allow_inc_post_accept_flag) OR ((tlinfo.allow_inc_post_accept_flag IS NULL) AND (x_allow_inc_post_accept_flag IS NULL)))
2042 AND ((tlinfo.min_increase_amt = x_min_increase_amt) OR ((tlinfo.min_increase_amt IS NULL) AND (x_min_increase_amt IS NULL)))
2043 AND ((tlinfo.allow_dec_post_accept_flag = x_allow_dec_post_accept_flag) OR ((tlinfo.allow_dec_post_accept_flag IS NULL) AND (x_allow_dec_post_accept_flag IS NULL)))
2044 AND ((tlinfo.min_decrease_amt = x_min_decrease_amt) OR ((tlinfo.min_decrease_amt IS NULL) AND (x_min_decrease_amt IS NULL)))
2045 AND ((tlinfo.allow_decln_post_accept_flag = x_allow_decln_post_accept_flag) OR ((tlinfo.allow_decln_post_accept_flag IS NULL) AND (x_allow_decln_post_accept_flag IS NULL)))
2046 AND ((tlinfo.status_after_decline = x_status_after_decline) OR ((tlinfo.status_after_decline IS NULL) AND (x_status_after_decline IS NULL)))
2047 AND ((tlinfo.fund_information_txt = x_fund_information_txt) OR ((tlinfo.fund_information_txt IS NULL) AND (x_fund_information_txt IS NULL)))
2048 ) THEN
2049 NULL;
2050 ELSE
2051 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
2052 igs_ge_msg_stack.add;
2053 app_exception.raise_exception;
2054 END IF;
2055
2056 RETURN;
2057 END lock_row;
2058
2059
2060 PROCEDURE update_row (
2061 x_rowid IN VARCHAR2,
2062 x_fund_id IN NUMBER,
2063 x_fund_code IN VARCHAR2,
2064 x_ci_cal_type IN VARCHAR2,
2065 x_ci_sequence_number IN NUMBER,
2066 x_description IN VARCHAR2,
2067 x_discontinue_fund IN VARCHAR2,
2068 x_entitlement IN VARCHAR2,
2069 x_auto_pkg IN VARCHAR2,
2070 x_self_help IN VARCHAR2,
2071 x_allow_man_pkg IN VARCHAR2,
2072 x_update_need IN VARCHAR2,
2073 x_disburse_fund IN VARCHAR2,
2074 x_available_amt IN NUMBER,
2075 x_offered_amt IN NUMBER,
2076 x_pending_amt IN NUMBER,
2077 x_accepted_amt IN NUMBER,
2078 x_declined_amt IN NUMBER,
2079 x_cancelled_amt IN NUMBER,
2080 x_remaining_amt IN NUMBER,
2081 x_enrollment_status IN VARCHAR2,
2082 x_prn_award_letter IN VARCHAR2,
2083 x_over_award_amt IN NUMBER,
2084 x_over_award_perct IN NUMBER,
2085 x_min_award_amt IN NUMBER,
2086 x_max_award_amt IN NUMBER,
2087 x_max_yearly_amt IN NUMBER,
2088 x_max_life_amt IN NUMBER,
2089 x_max_life_term IN NUMBER,
2090 x_fm_fc_methd IN VARCHAR2,
2091 x_roundoff_fact IN VARCHAR2,
2092 x_replace_fc IN VARCHAR2,
2093 x_allow_overaward IN VARCHAR2,
2094 x_pckg_awd_stat IN VARCHAR2,
2095 x_org_record_req IN VARCHAR2,
2096 x_disb_record_req IN VARCHAR2,
2097 x_prom_note_req IN VARCHAR2,
2098 x_min_num_disb IN NUMBER,
2099 x_max_num_disb IN NUMBER,
2100 x_fee_type IN VARCHAR2,
2101 x_total_offered IN NUMBER,
2102 x_total_accepted IN NUMBER,
2103 x_total_declined IN NUMBER,
2104 x_total_revoked IN NUMBER,
2105 x_total_cancelled IN NUMBER,
2106 x_total_disbursed IN NUMBER,
2107 x_total_committed IN NUMBER,
2108 x_committed_amt IN NUMBER,
2109 x_disbursed_amt IN NUMBER,
2110 x_awd_notice_txt IN VARCHAR2,
2111 x_attribute_category IN VARCHAR2,
2112 x_attribute1 IN VARCHAR2,
2113 x_attribute2 IN VARCHAR2,
2114 x_attribute3 IN VARCHAR2,
2115 x_attribute4 IN VARCHAR2,
2116 x_attribute5 IN VARCHAR2,
2117 x_attribute6 IN VARCHAR2,
2118 x_attribute7 IN VARCHAR2,
2119 x_attribute8 IN VARCHAR2,
2120 x_attribute9 IN VARCHAR2,
2121 x_attribute10 IN VARCHAR2,
2122 x_attribute11 IN VARCHAR2,
2123 x_attribute12 IN VARCHAR2,
2124 x_attribute13 IN VARCHAR2,
2125 x_attribute14 IN VARCHAR2,
2126 x_attribute15 IN VARCHAR2,
2127 x_attribute16 IN VARCHAR2,
2128 x_attribute17 IN VARCHAR2,
2129 x_attribute18 IN VARCHAR2,
2130 x_attribute19 IN VARCHAR2,
2131 x_attribute20 IN VARCHAR2,
2132 x_disb_verf_da IN VARCHAR2,
2133 x_fund_exp_da IN VARCHAR2,
2134 x_nslds_disb_da IN VARCHAR2,
2135 x_disb_exp_da IN VARCHAR2,
2136 x_fund_recv_reqd IN VARCHAR2,
2137 x_show_on_bill IN VARCHAR2,
2138 x_bill_desc IN VARCHAR2,
2139 x_credit_type_id IN NUMBER,
2140 x_spnsr_ref_num IN VARCHAR2,
2141 x_party_id IN VARCHAR2,
2142 x_spnsr_fee_type IN VARCHAR2,
2143 x_min_credit_points IN NUMBER,
2144 x_group_id IN NUMBER,
2145 x_spnsr_attribute_category IN VARCHAR2,
2146 x_spnsr_attribute1 IN VARCHAR2,
2147 x_spnsr_attribute2 IN VARCHAR2,
2148 x_spnsr_attribute3 IN VARCHAR2,
2149 x_spnsr_attribute4 IN VARCHAR2,
2150 x_spnsr_attribute5 IN VARCHAR2,
2151 x_spnsr_attribute6 IN VARCHAR2,
2152 x_spnsr_attribute7 IN VARCHAR2,
2153 x_spnsr_attribute8 IN VARCHAR2,
2154 x_spnsr_attribute9 IN VARCHAR2,
2155 x_spnsr_attribute10 IN VARCHAR2,
2156 x_spnsr_attribute11 IN VARCHAR2,
2157 x_spnsr_attribute12 IN VARCHAR2,
2158 x_spnsr_attribute13 IN VARCHAR2,
2159 x_spnsr_attribute14 IN VARCHAR2,
2160 x_spnsr_attribute15 IN VARCHAR2,
2161 x_spnsr_attribute16 IN VARCHAR2,
2162 x_spnsr_attribute17 IN VARCHAR2,
2163 x_spnsr_attribute18 IN VARCHAR2,
2164 x_spnsr_attribute19 IN VARCHAR2,
2165 x_spnsr_attribute20 IN VARCHAR2,
2166 x_threshold_perct IN NUMBER,
2167 x_threshold_value IN NUMBER,
2168 x_ver_app_stat_override IN VARCHAR2,
2169 x_gift_aid IN VARCHAR2,
2170 x_send_without_doc IN VARCHAR2, -- Added as part of Bug # 2758812
2171 x_mode IN VARCHAR2,
2172 x_re_pkg_verif_flag IN VARCHAR2,
2173 x_donot_repkg_if_code IN VARCHAR2,
2174 x_lock_award_flag IN VARCHAR2,
2175 x_disb_rounding_code IN VARCHAR2 DEFAULT NULL,
2176 x_view_only_flag IN VARCHAR2 DEFAULT NULL,
2177 x_accept_less_amt_flag IN VARCHAR2 DEFAULT NULL,
2178 x_allow_inc_post_accept_flag IN VARCHAR2 DEFAULT NULL,
2179 x_min_increase_amt IN NUMBER DEFAULT NULL,
2180 x_allow_dec_post_accept_flag IN VARCHAR2 DEFAULT NULL,
2181 x_min_decrease_amt IN NUMBER DEFAULT NULL,
2182 x_allow_decln_post_accept_flag IN VARCHAR2 DEFAULT NULL,
2183 x_status_after_decline IN VARCHAR2 DEFAULT NULL,
2184 x_fund_information_txt IN VARCHAR2 DEFAULT NULL
2185
2186 ) AS
2187 /*
2188 || Created By : cdcruz
2189 || Created On : 14-NOV-2000
2190 || Purpose : Handles the UPDATE DML logic for the table.
2191 || Known limitations, enhancements or remarks :
2192 || Change History :
2193 || Who When What
2194 || museshad 14-Jul-2005 Build FA 140 - Changes relating to the
2195 || addition of new columns
2196 || museshad 25-May-2005 Build# FA157 - Bug# 4382371.
2197 || New column 'DISB_ROUNDING_CODE' has been added
2198 || to the table 'IGF_AW_FUND_MAST_ALL'.
2199 || Modified the TBH to include this.
2200 || veramach 1-NOV-2003 FA 125 - Removed elig_criteria from assigments,update statements
2201 || mesriniv 06-JUL-2001 Added code to calculate the
2202 || Committed Amount
2203 || (reverse chronological order - newest change first)
2204 */
2205 x_last_update_date DATE ;
2206 x_last_updated_by NUMBER;
2207 x_last_update_login NUMBER;
2208 x_request_id NUMBER;
2209 x_program_id NUMBER;
2210 x_program_application_id NUMBER;
2211 x_program_update_date DATE;
2212
2213 BEGIN
2214
2215 x_last_update_date := SYSDATE;
2216 IF (X_MODE = 'I') THEN
2217 x_last_updated_by := 1;
2218 x_last_update_login := 0;
2219 ELSIF (x_mode = 'R') THEN
2220 x_last_updated_by := fnd_global.user_id;
2221 IF x_last_updated_by IS NULL THEN
2222 x_last_updated_by := -1;
2223 END IF;
2224 x_last_update_login := fnd_global.login_id;
2225 IF (x_last_update_login IS NULL) THEN
2226 x_last_update_login := -1;
2227 END IF;
2228 ELSE
2229 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
2230 igs_ge_msg_stack.add;
2231 app_exception.raise_exception;
2232 END IF;
2233
2234 before_dml(
2235 p_action => 'UPDATE',
2236 x_rowid => x_rowid,
2237 x_fund_id => x_fund_id,
2238 x_fund_code => x_fund_code,
2239 x_ci_cal_type => x_ci_cal_type,
2240 x_ci_sequence_number => x_ci_sequence_number,
2241 x_description => x_description,
2242 x_discontinue_fund => x_discontinue_fund,
2243 x_entitlement => x_entitlement,
2244 x_auto_pkg => x_auto_pkg,
2245 x_self_help => x_self_help,
2246 x_allow_man_pkg => x_allow_man_pkg,
2247 x_update_need => x_update_need,
2248 x_disburse_fund => x_disburse_fund,
2249 x_available_amt => x_available_amt,
2250 x_offered_amt => x_offered_amt,
2251 x_pending_amt => x_pending_amt,
2252 x_accepted_amt => x_accepted_amt,
2253 x_declined_amt => x_declined_amt,
2254 x_cancelled_amt => x_cancelled_amt,
2255 x_remaining_amt => x_remaining_amt,
2256 x_enrollment_status => x_enrollment_status,
2257 x_prn_award_letter => x_prn_award_letter,
2258 x_over_award_amt => x_over_award_amt,
2259 x_over_award_perct => x_over_award_perct,
2260 x_min_award_amt => x_min_award_amt,
2261 x_max_award_amt => x_max_award_amt,
2262 x_max_yearly_amt => x_max_yearly_amt,
2263 x_max_life_amt => x_max_life_amt,
2264 x_max_life_term => x_max_life_term,
2265 x_fm_fc_methd => x_fm_fc_methd,
2266 x_roundoff_fact => x_roundoff_fact,
2267 x_replace_fc => x_replace_fc,
2268 x_allow_overaward => x_allow_overaward,
2269 x_pckg_awd_stat => x_pckg_awd_stat,
2270 x_org_record_req => x_org_record_req,
2271 x_disb_record_req => x_disb_record_req,
2272 x_prom_note_req => x_prom_note_req,
2273 x_min_num_disb => x_min_num_disb,
2274 x_max_num_disb => x_max_num_disb,
2275 x_fee_type => x_fee_type,
2276 x_total_offered => x_total_offered,
2277 x_total_accepted => x_total_accepted,
2278 x_total_declined => x_total_declined,
2279 x_total_revoked => x_total_revoked,
2280 x_total_cancelled => x_total_cancelled,
2281 x_total_disbursed => x_total_disbursed,
2282 x_total_committed => x_total_committed,
2283 x_committed_amt => x_committed_amt,
2284 x_disbursed_amt => x_disbursed_amt,
2285 x_awd_notice_txt => x_awd_notice_txt,
2286 x_attribute_category => x_attribute_category,
2287 x_attribute1 => x_attribute1,
2288 x_attribute2 => x_attribute2,
2289 x_attribute3 => x_attribute3,
2290 x_attribute4 => x_attribute4,
2291 x_attribute5 => x_attribute5,
2292 x_attribute6 => x_attribute6,
2293 x_attribute7 => x_attribute7,
2294 x_attribute8 => x_attribute8,
2295 x_attribute9 => x_attribute9,
2296 x_attribute10 => x_attribute10,
2297 x_attribute11 => x_attribute11,
2298 x_attribute12 => x_attribute12,
2299 x_attribute13 => x_attribute13,
2300 x_attribute14 => x_attribute14,
2301 x_attribute15 => x_attribute15,
2302 x_attribute16 => x_attribute16,
2303 x_attribute17 => x_attribute17,
2304 x_attribute18 => x_attribute18,
2305 x_attribute19 => x_attribute19,
2306 x_attribute20 => x_attribute20,
2307 x_disb_verf_da => x_disb_verf_da,
2308 x_fund_exp_da => x_fund_exp_da,
2309 x_nslds_disb_da => x_nslds_disb_da,
2310 x_disb_exp_da => x_disb_exp_da,
2311 x_fund_recv_reqd => x_fund_recv_reqd,
2312 x_show_on_bill => x_show_on_bill,
2313 x_bill_desc => x_bill_desc,
2314 x_credit_type_id => x_credit_type_id,
2315 x_spnsr_ref_num => x_spnsr_ref_num,
2316 x_party_id => x_party_id ,
2317 x_spnsr_fee_type => x_spnsr_fee_type,
2318 x_min_credit_points => x_min_credit_points,
2319 x_group_id => x_group_id,
2320 x_spnsr_attribute_category => x_spnsr_attribute_category,
2321 x_spnsr_attribute1 => x_spnsr_attribute1,
2322 x_spnsr_attribute2 => x_spnsr_attribute2,
2323 x_spnsr_attribute3 => x_spnsr_attribute3,
2324 x_spnsr_attribute4 => x_spnsr_attribute4,
2325 x_spnsr_attribute5 => x_spnsr_attribute5,
2326 x_spnsr_attribute6 => x_spnsr_attribute6,
2327 x_spnsr_attribute7 => x_spnsr_attribute7,
2328 x_spnsr_attribute8 => x_spnsr_attribute8,
2329 x_spnsr_attribute9 => x_spnsr_attribute9,
2330 x_spnsr_attribute10 => x_spnsr_attribute10,
2331 x_spnsr_attribute11 => x_spnsr_attribute11,
2332 x_spnsr_attribute12 => x_spnsr_attribute12,
2333 x_spnsr_attribute13 => x_spnsr_attribute13,
2334 x_spnsr_attribute14 => x_spnsr_attribute14,
2335 x_spnsr_attribute15 => x_spnsr_attribute15,
2336 x_spnsr_attribute16 => x_spnsr_attribute16,
2337 x_spnsr_attribute17 => x_spnsr_attribute17,
2338 x_spnsr_attribute18 => x_spnsr_attribute18,
2339 x_spnsr_attribute19 => x_spnsr_attribute19,
2340 x_spnsr_attribute20 => x_spnsr_attribute20,
2341 x_threshold_perct => x_threshold_perct ,
2342 x_threshold_value => x_threshold_value,
2343 x_ver_app_stat_override => x_ver_app_stat_override,
2344 x_gift_aid => x_gift_aid ,
2345 x_send_without_doc => x_send_without_doc, -- Added as part of Bug # 2758812
2346 x_creation_date => x_last_update_date,
2347 x_created_by => x_last_updated_by,
2348 x_last_update_date => x_last_update_date,
2349 x_last_updated_by => x_last_updated_by,
2350 x_last_update_login => x_last_update_login,
2351 x_re_pkg_verif_flag => x_re_pkg_verif_flag,
2352 x_donot_repkg_if_code => x_donot_repkg_if_code,
2353 x_lock_award_flag => x_lock_award_flag,
2354 x_disb_rounding_code => x_disb_rounding_code,
2355 x_view_only_flag => x_view_only_flag,
2356 x_accept_less_amt_flag => x_accept_less_amt_flag,
2357 x_allow_inc_post_accept_flag => x_allow_inc_post_accept_flag,
2358 x_min_increase_amt => x_min_increase_amt,
2359 x_allow_dec_post_accept_flag => x_allow_dec_post_accept_flag,
2360 x_min_decrease_amt => x_min_decrease_amt,
2361 x_allow_decln_post_accept_flag => x_allow_decln_post_accept_flag,
2362 x_status_after_decline => x_status_after_decline,
2363 x_fund_information_txt => x_fund_information_txt
2364
2365 );
2366
2367 IF (x_mode = 'R') THEN
2368 x_request_id := fnd_global.conc_request_id;
2369 x_program_id := fnd_global.conc_program_id;
2370 x_program_application_id := fnd_global.prog_appl_id;
2371 IF (x_request_id = -1) THEN
2372 x_request_id := old_references.request_id;
2373 x_program_id := old_references.program_id;
2374 x_program_application_id := old_references.program_application_id;
2375 x_program_update_date := old_references.program_update_date;
2376 ELSE
2377 x_program_update_date := SYSDATE;
2378 END IF;
2379 END IF;
2380
2381 -- Update the remaining amt
2382
2383 new_references.remaining_amt := NVL(new_references.available_amt,0 )
2384 - NVL(new_references.offered_amt,0 )
2385 + NVL(new_references.declined_amt,0 )
2386 + NVL(new_references.cancelled_amt,0 ) ;
2387
2388 -- Update the Committed amt
2389 -- Corrected wrt. Bug 2310222
2390
2391 new_references.committed_amt := NVL(new_references.accepted_amt,0)
2392 - NVL(new_references.disbursed_amt,0) ;
2393
2394
2395
2396 UPDATE igf_aw_fund_mast_all
2397 SET
2398 fund_code = new_references.fund_code,
2399 ci_cal_type = new_references.ci_cal_type,
2400 ci_sequence_number = new_references.ci_sequence_number,
2401 description = new_references.description,
2402 discontinue_fund = new_references.discontinue_fund,
2403 entitlement = new_references.entitlement,
2404 auto_pkg = new_references.auto_pkg,
2405 self_help = new_references.self_help,
2406 allow_man_pkg = new_references.allow_man_pkg,
2407 update_need = new_references.update_need,
2408 disburse_fund = new_references.disburse_fund,
2409 available_amt = new_references.available_amt,
2410 offered_amt = new_references.offered_amt,
2411 pending_amt = new_references.pending_amt,
2412 accepted_amt = new_references.accepted_amt,
2413 declined_amt = new_references.declined_amt,
2414 cancelled_amt = new_references.cancelled_amt,
2415 remaining_amt = new_references.remaining_amt,
2416 enrollment_status = new_references.enrollment_status,
2417 prn_award_letter = new_references.prn_award_letter,
2418 over_award_amt = new_references.over_award_amt,
2419 over_award_perct = new_references.over_award_perct,
2420 min_award_amt = new_references.min_award_amt,
2421 max_award_amt = new_references.max_award_amt,
2422 max_yearly_amt = new_references.max_yearly_amt,
2423 max_life_amt = new_references.max_life_amt,
2424 max_life_term = new_references.max_life_term,
2425 fm_fc_methd = new_references.fm_fc_methd,
2426 roundoff_fact = new_references.roundoff_fact,
2427 replace_fc = new_references.replace_fc,
2428 allow_overaward = new_references.allow_overaward,
2429 pckg_awd_stat = new_references.pckg_awd_stat,
2430 org_record_req = new_references.org_record_req,
2431 disb_record_req = new_references.disb_record_req,
2432 prom_note_req = new_references.prom_note_req,
2433 min_num_disb = new_references.min_num_disb,
2434 max_num_disb = new_references.max_num_disb,
2435 fee_type = new_references.fee_type,
2436 total_offered = new_references.total_offered,
2437 total_accepted = new_references.total_accepted,
2438 total_declined = new_references.total_declined,
2439 total_revoked = new_references.total_revoked,
2440 total_cancelled = new_references.total_cancelled,
2441 total_disbursed = new_references.total_disbursed,
2442 total_committed = new_references.total_committed,
2443 committed_amt = new_references.committed_amt,
2444 disbursed_amt = new_references.disbursed_amt,
2445 awd_notice_txt = new_references.awd_notice_txt,
2446 attribute_category = new_references.attribute_category,
2447 attribute1 = new_references.attribute1,
2448 attribute2 = new_references.attribute2,
2449 attribute3 = new_references.attribute3,
2450 attribute4 = new_references.attribute4,
2451 attribute5 = new_references.attribute5,
2452 attribute6 = new_references.attribute6,
2453 attribute7 = new_references.attribute7,
2454 attribute8 = new_references.attribute8,
2455 attribute9 = new_references.attribute9,
2456 attribute10 = new_references.attribute10,
2457 attribute11 = new_references.attribute11,
2458 attribute12 = new_references.attribute12,
2459 attribute13 = new_references.attribute13,
2460 attribute14 = new_references.attribute14,
2461 attribute15 = new_references.attribute15,
2462 attribute16 = new_references.attribute16,
2463 attribute17 = new_references.attribute17,
2464 attribute18 = new_references.attribute18,
2465 attribute19 = new_references.attribute19,
2466 attribute20 = new_references.attribute20,
2467 disb_verf_da = new_references.disb_verf_da,
2468 fund_exp_da = new_references.fund_exp_da,
2469 nslds_disb_da = new_references.nslds_disb_da,
2470 disb_exp_da = new_references.disb_exp_da,
2471 fund_recv_reqd = new_references.fund_recv_reqd,
2472 show_on_bill = new_references.show_on_bill,
2473 bill_desc = new_references.bill_desc,
2474 credit_type_id = new_references.credit_type_id,
2475 spnsr_ref_num = new_references.spnsr_ref_num,
2476 party_id = new_references.party_id,
2477 spnsr_fee_type = new_references.spnsr_fee_type,
2478 min_credit_points = new_references.min_credit_points,
2479 group_id = new_references.group_id,
2480 spnsr_attribute_category = new_references.spnsr_attribute_category,
2481 spnsr_attribute1 = new_references.spnsr_attribute1,
2482 spnsr_attribute2 = new_references.spnsr_attribute2,
2483 spnsr_attribute3 = new_references.spnsr_attribute3,
2484 spnsr_attribute4 = new_references.spnsr_attribute4,
2485 spnsr_attribute5 = new_references.spnsr_attribute5,
2486 spnsr_attribute6 = new_references.spnsr_attribute6,
2487 spnsr_attribute7 = new_references.spnsr_attribute7,
2488 spnsr_attribute8 = new_references.spnsr_attribute8,
2489 spnsr_attribute9 = new_references.spnsr_attribute9,
2490 spnsr_attribute10 = new_references.spnsr_attribute10,
2491 spnsr_attribute11 = new_references.spnsr_attribute11,
2492 spnsr_attribute12 = new_references.spnsr_attribute12,
2493 spnsr_attribute13 = new_references.spnsr_attribute13,
2494 spnsr_attribute14 = new_references.spnsr_attribute14,
2495 spnsr_attribute15 = new_references.spnsr_attribute15,
2496 spnsr_attribute16 = new_references.spnsr_attribute16,
2497 spnsr_attribute17 = new_references.spnsr_attribute17,
2498 spnsr_attribute18 = new_references.spnsr_attribute18,
2499 spnsr_attribute19 = new_references.spnsr_attribute19,
2500 spnsr_attribute20 = new_references.spnsr_attribute20,
2501 threshold_perct = new_references.threshold_perct,
2502 threshold_value = new_references.threshold_value,
2503 ver_app_stat_override = new_references.ver_app_stat_override,
2504 gift_aid = new_references.gift_aid ,
2505 send_without_doc = new_references.send_without_doc,
2506 re_pkg_verif_flag = new_references.re_pkg_verif_flag,
2507 donot_repkg_if_code = new_references.donot_repkg_if_code,
2508 lock_award_flag = new_references.lock_award_flag,
2509 last_update_date = x_last_update_date,
2510 last_updated_by = x_last_updated_by,
2511 last_update_login = x_last_update_login ,
2512 request_id = x_request_id,
2513 program_id = x_program_id,
2514 program_application_id = x_program_application_id,
2515 program_update_date = x_program_update_date,
2516 disb_rounding_code = new_references.disb_rounding_code,
2517 view_only_flag = new_references.view_only_flag,
2518 accept_less_amt_flag = new_references.accept_less_amt_flag,
2519 allow_inc_post_accept_flag = new_references.allow_inc_post_accept_flag,
2520 min_increase_amt = new_references.min_increase_amt,
2521 allow_dec_post_accept_flag = new_references.allow_dec_post_accept_flag,
2522 min_decrease_amt = new_references.min_decrease_amt,
2523 allow_decln_post_accept_flag = new_references.allow_decln_post_accept_flag,
2524 status_after_decline = new_references.status_after_decline,
2525 fund_information_txt = new_references.fund_information_txt
2526
2527 WHERE rowid = x_rowid;
2528
2529 IF (SQL%NOTFOUND) THEN
2530 RAISE NO_DATA_FOUND;
2531 END IF;
2532
2533 END update_row;
2534
2535
2536 PROCEDURE add_row (
2537 x_rowid IN OUT NOCOPY VARCHAR2,
2538 x_fund_id IN OUT NOCOPY NUMBER,
2539 x_fund_code IN VARCHAR2,
2540 x_ci_cal_type IN VARCHAR2,
2541 x_ci_sequence_number IN NUMBER,
2542 x_description IN VARCHAR2,
2543 x_discontinue_fund IN VARCHAR2,
2544 x_entitlement IN VARCHAR2,
2545 x_auto_pkg IN VARCHAR2,
2546 x_self_help IN VARCHAR2,
2547 x_allow_man_pkg IN VARCHAR2,
2548 x_update_need IN VARCHAR2,
2549 x_disburse_fund IN VARCHAR2,
2550 x_available_amt IN NUMBER,
2551 x_offered_amt IN NUMBER,
2552 x_pending_amt IN NUMBER,
2553 x_accepted_amt IN NUMBER,
2554 x_declined_amt IN NUMBER,
2555 x_cancelled_amt IN NUMBER,
2556 x_remaining_amt IN NUMBER,
2557 x_enrollment_status IN VARCHAR2,
2558 x_prn_award_letter IN VARCHAR2,
2559 x_over_award_amt IN NUMBER,
2560 x_over_award_perct IN NUMBER,
2561 x_min_award_amt IN NUMBER,
2562 x_max_award_amt IN NUMBER,
2563 x_max_yearly_amt IN NUMBER,
2564 x_max_life_amt IN NUMBER,
2565 x_max_life_term IN NUMBER,
2566 x_fm_fc_methd IN VARCHAR2,
2567 x_roundoff_fact IN VARCHAR2,
2568 x_replace_fc IN VARCHAR2,
2569 x_allow_overaward IN VARCHAR2,
2570 x_pckg_awd_stat IN VARCHAR2,
2571 x_org_record_req IN VARCHAR2,
2572 x_disb_record_req IN VARCHAR2,
2573 x_prom_note_req IN VARCHAR2,
2574 x_min_num_disb IN NUMBER,
2575 x_max_num_disb IN NUMBER,
2576 x_fee_type IN VARCHAR2,
2577 x_total_offered IN NUMBER,
2578 x_total_accepted IN NUMBER,
2579 x_total_declined IN NUMBER,
2580 x_total_revoked IN NUMBER,
2581 x_total_cancelled IN NUMBER,
2582 x_total_disbursed IN NUMBER,
2583 x_total_committed IN NUMBER,
2584 x_committed_amt IN NUMBER,
2585 x_disbursed_amt IN NUMBER,
2586 x_awd_notice_txt IN VARCHAR2,
2587 x_attribute_category IN VARCHAR2,
2588 x_attribute1 IN VARCHAR2,
2589 x_attribute2 IN VARCHAR2,
2590 x_attribute3 IN VARCHAR2,
2591 x_attribute4 IN VARCHAR2,
2592 x_attribute5 IN VARCHAR2,
2593 x_attribute6 IN VARCHAR2,
2594 x_attribute7 IN VARCHAR2,
2595 x_attribute8 IN VARCHAR2,
2596 x_attribute9 IN VARCHAR2,
2597 x_attribute10 IN VARCHAR2,
2598 x_attribute11 IN VARCHAR2,
2599 x_attribute12 IN VARCHAR2,
2600 x_attribute13 IN VARCHAR2,
2601 x_attribute14 IN VARCHAR2,
2602 x_attribute15 IN VARCHAR2,
2603 x_attribute16 IN VARCHAR2,
2604 x_attribute17 IN VARCHAR2,
2605 x_attribute18 IN VARCHAR2,
2606 x_attribute19 IN VARCHAR2,
2607 x_attribute20 IN VARCHAR2,
2608 x_disb_verf_da IN VARCHAR2,
2609 x_fund_exp_da IN VARCHAR2,
2610 x_nslds_disb_da IN VARCHAR2,
2611 x_disb_exp_da IN VARCHAR2,
2612 x_fund_recv_reqd IN VARCHAR2,
2613 x_show_on_bill IN VARCHAR2,
2614 x_bill_desc IN VARCHAR2,
2615 x_credit_type_id IN NUMBER,
2616 x_spnsr_ref_num IN VARCHAR2,
2617 x_party_id IN VARCHAR2,
2618 x_spnsr_fee_type IN VARCHAR2,
2619 x_min_credit_points IN NUMBER,
2620 x_group_id IN NUMBER,
2621 x_spnsr_attribute_category IN VARCHAR2,
2622 x_spnsr_attribute1 IN VARCHAR2,
2623 x_spnsr_attribute2 IN VARCHAR2,
2624 x_spnsr_attribute3 IN VARCHAR2,
2625 x_spnsr_attribute4 IN VARCHAR2,
2626 x_spnsr_attribute5 IN VARCHAR2,
2627 x_spnsr_attribute6 IN VARCHAR2,
2628 x_spnsr_attribute7 IN VARCHAR2,
2629 x_spnsr_attribute8 IN VARCHAR2,
2630 x_spnsr_attribute9 IN VARCHAR2,
2631 x_spnsr_attribute10 IN VARCHAR2,
2632 x_spnsr_attribute11 IN VARCHAR2,
2633 x_spnsr_attribute12 IN VARCHAR2,
2634 x_spnsr_attribute13 IN VARCHAR2,
2635 x_spnsr_attribute14 IN VARCHAR2,
2636 x_spnsr_attribute15 IN VARCHAR2,
2637 x_spnsr_attribute16 IN VARCHAR2,
2638 x_spnsr_attribute17 IN VARCHAR2,
2639 x_spnsr_attribute18 IN VARCHAR2,
2640 x_spnsr_attribute19 IN VARCHAR2,
2641 x_spnsr_attribute20 IN VARCHAR2,
2642 x_threshold_perct IN NUMBER,
2643 x_threshold_value IN NUMBER,
2644 x_ver_app_stat_override IN VARCHAR2,
2645 x_gift_aid IN VARCHAR2,
2646 x_send_without_doc IN VARCHAR2, -- Added as part of Bug # 2758812
2647 x_mode IN VARCHAR2,
2648 x_re_pkg_verif_flag IN VARCHAR2,
2649 x_donot_repkg_if_code IN VARCHAR2,
2650 x_lock_award_flag IN VARCHAR2,
2651 x_disb_rounding_code IN VARCHAR2 DEFAULT NULL,
2652 x_view_only_flag IN VARCHAR2 DEFAULT NULL,
2653 x_accept_less_amt_flag IN VARCHAR2 DEFAULT NULL,
2654 x_allow_inc_post_accept_flag IN VARCHAR2 DEFAULT NULL,
2655 x_min_increase_amt IN NUMBER DEFAULT NULL,
2656 x_allow_dec_post_accept_flag IN VARCHAR2 DEFAULT NULL,
2657 x_min_decrease_amt IN NUMBER DEFAULT NULL,
2658 x_allow_decln_post_accept_flag IN VARCHAR2 DEFAULT NULL,
2659 x_status_after_decline IN VARCHAR2 DEFAULT NULL,
2660 x_fund_information_txt IN VARCHAR2 DEFAULT NULL
2661
2662 ) AS
2663 /*
2664 || Created By : cdcruz
2665 || Created On : 14-NOV-2000
2666 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
2667 || Known limitations, enhancements or remarks :
2668 || Change History :
2669 || Who When What
2670 || museshad 14-Jul-2005 FA 140 - Changes relating to the addition of new columns
2671 || museshad 25-May-2005 The order of the parameters in the call to update_row()
2672 || procedure was incorrect. Corrected this.
2673 || veramach 1-NOV-2003 FA 125 - Removed elig_criteria from assigments
2674 || (reverse chronological order - newest change first)
2675 */
2676 CURSOR c1 IS
2677 SELECT rowid
2678 FROM igf_aw_fund_mast_all
2679 WHERE fund_id = x_fund_id;
2680
2681 BEGIN
2682
2683 OPEN c1;
2684 FETCH c1 INTO x_rowid;
2685 IF (c1%NOTFOUND) THEN
2686 CLOSE c1;
2687
2688 insert_row (
2689 x_rowid,
2690 x_fund_id,
2691 x_fund_code,
2692 x_ci_cal_type,
2693 x_ci_sequence_number,
2694 x_description,
2695 x_discontinue_fund,
2696 x_entitlement,
2697 x_auto_pkg,
2698 x_self_help,
2699 x_allow_man_pkg,
2700 x_update_need,
2701 x_disburse_fund,
2702 x_available_amt,
2703 x_offered_amt,
2704 x_pending_amt,
2705 x_accepted_amt,
2706 x_declined_amt,
2707 x_cancelled_amt,
2708 x_remaining_amt,
2709 x_enrollment_status,
2710 x_prn_award_letter,
2711 x_over_award_amt,
2712 x_over_award_perct,
2713 x_min_award_amt,
2714 x_max_award_amt,
2715 x_max_yearly_amt,
2716 x_max_life_amt,
2717 x_max_life_term,
2718 x_fm_fc_methd,
2719 x_roundoff_fact,
2720 x_replace_fc,
2721 x_allow_overaward,
2722 x_pckg_awd_stat,
2723 x_org_record_req,
2724 x_disb_record_req,
2725 x_prom_note_req,
2726 x_min_num_disb,
2727 x_max_num_disb,
2728 x_fee_type,
2729 x_total_offered,
2730 x_total_accepted,
2731 x_total_declined,
2732 x_total_revoked,
2733 x_total_cancelled,
2734 x_total_disbursed,
2735 x_total_committed,
2736 x_committed_amt,
2737 x_disbursed_amt,
2738 x_awd_notice_txt,
2739 x_attribute_category,
2740 x_attribute1,
2741 x_attribute2,
2742 x_attribute3,
2743 x_attribute4,
2744 x_attribute5,
2745 x_attribute6,
2746 x_attribute7,
2747 x_attribute8,
2748 x_attribute9,
2749 x_attribute10,
2750 x_attribute11,
2751 x_attribute12,
2752 x_attribute13,
2753 x_attribute14,
2754 x_attribute15,
2755 x_attribute16,
2756 x_attribute17,
2757 x_attribute18,
2758 x_attribute19,
2759 x_attribute20,
2760 x_disb_verf_da,
2761 x_fund_exp_da,
2762 x_nslds_disb_da,
2763 x_disb_exp_da,
2764 x_fund_recv_reqd,
2765 x_show_on_bill,
2766 x_bill_desc,
2767 x_credit_type_id,
2768 x_spnsr_ref_num,
2769 x_party_id,
2770 x_spnsr_fee_type,
2771 x_min_credit_points,
2772 x_group_id,
2773 x_spnsr_attribute_category,
2774 x_spnsr_attribute1,
2775 x_spnsr_attribute2,
2776 x_spnsr_attribute3,
2777 x_spnsr_attribute4,
2778 x_spnsr_attribute5,
2779 x_spnsr_attribute6,
2780 x_spnsr_attribute7,
2781 x_spnsr_attribute8,
2782 x_spnsr_attribute9,
2783 x_spnsr_attribute10,
2784 x_spnsr_attribute11,
2785 x_spnsr_attribute12,
2786 x_spnsr_attribute13,
2787 x_spnsr_attribute14,
2788 x_spnsr_attribute15,
2789 x_spnsr_attribute16,
2790 x_spnsr_attribute17,
2791 x_spnsr_attribute18,
2792 x_spnsr_attribute19,
2793 x_spnsr_attribute20,
2794 x_threshold_perct,
2795 x_threshold_value,
2796 x_ver_app_stat_override,
2797 x_gift_aid,
2798 x_send_without_doc,
2799 x_mode,
2800 x_re_pkg_verif_flag,
2801 x_donot_repkg_if_code,
2802 x_lock_award_flag,
2803 x_disb_rounding_code,
2804 x_view_only_flag,
2805 x_accept_less_amt_flag,
2806 x_allow_inc_post_accept_flag,
2807 x_min_increase_amt,
2808 x_allow_dec_post_accept_flag,
2809 x_min_decrease_amt,
2810 x_allow_decln_post_accept_flag,
2811 x_status_after_decline,
2812 x_fund_information_txt
2813 );
2814 RETURN;
2815 END IF;
2816 CLOSE c1;
2817
2818 update_row (
2819 x_rowid,
2820 x_fund_id,
2821 x_fund_code,
2822 x_ci_cal_type,
2823 x_ci_sequence_number,
2824 x_description,
2825 x_discontinue_fund,
2826 x_entitlement,
2827 x_auto_pkg,
2828 x_self_help,
2829 x_allow_man_pkg,
2830 x_update_need,
2831 x_disburse_fund,
2832 x_available_amt,
2833 x_offered_amt,
2834 x_pending_amt,
2835 x_accepted_amt,
2836 x_declined_amt,
2837 x_cancelled_amt,
2838 x_remaining_amt,
2839 x_enrollment_status,
2840 x_prn_award_letter,
2841 x_over_award_amt,
2842 x_over_award_perct,
2843 x_min_award_amt,
2844 x_max_award_amt,
2845 x_max_yearly_amt,
2846 x_max_life_amt,
2847 x_max_life_term,
2848 x_fm_fc_methd,
2849 x_roundoff_fact,
2850 x_replace_fc,
2851 x_allow_overaward,
2852 x_pckg_awd_stat,
2853 x_org_record_req,
2854 x_disb_record_req,
2855 x_prom_note_req,
2856 x_min_num_disb,
2857 x_max_num_disb,
2858 x_fee_type,
2859 x_total_offered,
2860 x_total_accepted,
2861 x_total_declined,
2862 x_total_revoked,
2863 x_total_cancelled,
2864 x_total_disbursed,
2865 x_total_committed,
2866 x_committed_amt,
2867 x_disbursed_amt,
2868 x_awd_notice_txt,
2869 x_attribute_category,
2870 x_attribute1,
2871 x_attribute2,
2872 x_attribute3,
2873 x_attribute4,
2874 x_attribute5,
2875 x_attribute6,
2876 x_attribute7,
2877 x_attribute8,
2878 x_attribute9,
2879 x_attribute10,
2880 x_attribute11,
2881 x_attribute12,
2882 x_attribute13,
2883 x_attribute14,
2884 x_attribute15,
2885 x_attribute16,
2886 x_attribute17,
2887 x_attribute18,
2888 x_attribute19,
2889 x_attribute20,
2890 x_disb_verf_da,
2891 x_fund_exp_da,
2892 x_nslds_disb_da,
2893 x_disb_exp_da,
2894 x_fund_recv_reqd,
2895 x_show_on_bill,
2896 x_bill_desc,
2897 x_credit_type_id,
2898 x_spnsr_ref_num,
2899 x_party_id,
2900 x_spnsr_fee_type,
2901 x_min_credit_points,
2902 x_group_id,
2903 x_spnsr_attribute_category,
2904 x_spnsr_attribute1,
2905 x_spnsr_attribute2,
2906 x_spnsr_attribute3,
2907 x_spnsr_attribute4,
2908 x_spnsr_attribute5,
2909 x_spnsr_attribute6,
2910 x_spnsr_attribute7,
2911 x_spnsr_attribute8,
2912 x_spnsr_attribute9,
2913 x_spnsr_attribute10,
2914 x_spnsr_attribute11,
2915 x_spnsr_attribute12,
2916 x_spnsr_attribute13,
2917 x_spnsr_attribute14,
2918 x_spnsr_attribute15,
2919 x_spnsr_attribute16,
2920 x_spnsr_attribute17,
2921 x_spnsr_attribute18,
2922 x_spnsr_attribute19,
2923 x_spnsr_attribute20,
2924 x_threshold_perct ,
2925 x_threshold_value ,
2926 x_ver_app_stat_override,
2927 x_gift_aid,
2928 x_send_without_doc,
2929 x_mode,
2930 x_re_pkg_verif_flag,
2931 x_donot_repkg_if_code,
2932 x_lock_award_flag,
2933 x_disb_rounding_code,
2934 x_view_only_flag,
2935 x_accept_less_amt_flag,
2936 x_allow_inc_post_accept_flag,
2937 x_min_increase_amt,
2938 x_allow_dec_post_accept_flag,
2939 x_min_decrease_amt,
2940 x_allow_decln_post_accept_flag,
2941 x_status_after_decline,
2942 x_fund_information_txt
2943 );
2944
2945 END add_row;
2946
2947
2948 PROCEDURE delete_row (
2949 x_rowid IN VARCHAR2
2950 ) AS
2951 /*
2952 || Created By : cdcruz
2953 || Created On : 14-NOV-2000
2954 || Purpose : Handles the DELETE DML logic for the table.
2955 || Known limitations, enhancements or remarks :
2956 || Change History :
2957 || Who When What
2958 || (reverse chronological order - newest change first)
2959 */
2960 BEGIN
2961
2962 before_dml (
2963 p_action => 'DELETE',
2964 x_rowid => x_rowid
2965 );
2966
2967 DELETE FROM igf_aw_fund_mast_all
2968 WHERE rowid = x_rowid;
2969
2970 IF (SQL%NOTFOUND) THEN
2971 RAISE NO_DATA_FOUND;
2972 END IF;
2973
2974 END delete_row;
2975
2976
2977 END igf_aw_fund_mast_pkg;