[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_AWARD_PKG
Source
1 PACKAGE BODY igf_aw_award_pkg AS
2 /* $Header: IGFWI22B.pls 120.5 2006/08/03 12:14:46 tsailaja ship $ */
3 /*=======================================================================+
4 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, CalIFornia, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | |
8 | DESCRIPTION |
9 | PL/SQL body for package: IGF_AW_AWARD_PKG
10 | |
11 | NOTES |
12 | |
13 | This package has a flag on the end of some of the procedures called |
14 | X_MODE. Pass either 'R' for runtime, or 'I' for Install-time. |
15 | This will control how the who columns are filled in; If you are |
16 | running in runtime mode, they are taken from the profiles, whereas in |
17 | install-time mode they get defaulted with special values to indicate |
18 | that they were inserted by datamerge. |
19 | |
20 | The ADD_ROW routine will see whether a row exists by selecting |
21 | based on the primary key, and updates the row IF it exists, |
22 | or inserts the row IF it doesn't already exist. |
23 | |
24 | This module is called by AutoInstall (afplss.drv) on install and |
25 | upgrade. The WHENEVER SQLERROR and EXIT (at bottom) are required. |
26 | |
27 | HISTORY |
28 |-----------------------------------------------------------------------|
29 |tsailaja 03-Aug-2006 Bug #5337555 |
30 | Included 'GPLUSFL' fund code. |
31 | so that any change to GPLUSFL awards gets logged |
32 | bvisvana 24-May-2005 FA 157 - Bug # 4382371 |
33 | Award History changes. Added procedures |
34 | set_award_source_change, |
35 | update_award_history,check_award_history |
36 | isChangeIn_AwardAttribute |
37 |-----------------------------------------------------------------------|
38 | smadathi 13-Oct-2004 Bug 3416936 ModIFied as per the TD |
39 | Added AfterRowInsertUpdateDelete1 and after_dml |
40 | procedures |
41 |-----------------------------------------------------------------------|
42 | bannamal 28-Sep-2004 Bug 3416863 - FA149 COD XML |
43 | Added check child existance for igf_gr_cod_dtls |
44 |-----------------------------------------------------------------------|
45 | sjadhav 1-Dec-2003 Bug 3252832 - FA 131 Build |
46 | Added two new columns for this build |
47 |-----------------------------------------------------------------------|
48 | veramach 1-NOV-2003 #3160568 Added adplans_id in the tbh calls |
49 |-----------------------------------------------------------------------|
50 | brajendr 21-Jul-2003 Bug 2991359 |
51 | Added check child existance for igf_gr_rfms |
52 |-----------------------------------------------------------------------|
53 | sjadhav 03-Jul-2003 Bug 3029739 |
54 | ModIFied igf_aw_gen.update_fmast call for |
55 | INSERT routine |
56 |-----------------------------------------------------------------------|
57 | bkkumar 04-jun-2003 Bug 2858504 Added award_ number _txt and |
58 | legacy_ record_flagin the tbh calls |
59 |-----------------------------------------------------------------------|
60 | adhawan 25-oct-2002 Bug 2613546. Added alt_pell_schedule in the |
61 | table handler calls gscc warnings fixed |
62 *=======================================================================*/
63
64 l_rowid VARCHAR2(25);
65 old_references igf_aw_award_all%ROWTYPE;
66 new_references igf_aw_award_all%ROWTYPE;
67 g_v_called_from VARCHAR2(30);
68 -- FA 157 - Global variables for Award change Source and Award history transaction id
69 g_award_change_source igf_aw_award_level_hist.AWARD_CHANGE_SOURCE_CODE%TYPE := 'CONCURRENT_PROCESS';
70 g_award_hist_tran_id igf_aw_award_level_hist.AWARD_HIST_TRAN_ID%TYPE;
71
72
73 PROCEDURE set_column_values (
74 p_action IN VARCHAR2,
75 x_rowid IN VARCHAR2,
76 x_award_id IN NUMBER,
77 x_fund_id IN NUMBER,
78 x_base_id IN NUMBER,
79 x_offered_amt IN NUMBER,
80 x_accepted_amt IN NUMBER,
81 x_paid_amt IN NUMBER,
82 x_packaging_type IN VARCHAR2,
83 x_batch_id IN VARCHAR2,
84 x_manual_update IN VARCHAR2,
85 x_rules_override IN VARCHAR2,
86 x_award_date IN DATE,
87 x_award_status IN VARCHAR2,
88 x_attribute_category IN VARCHAR2,
89 x_attribute1 IN VARCHAR2,
90 x_attribute2 IN VARCHAR2,
91 x_attribute3 IN VARCHAR2,
92 x_attribute4 IN VARCHAR2,
93 x_attribute5 IN VARCHAR2,
94 x_attribute6 IN VARCHAR2,
95 x_attribute7 IN VARCHAR2,
96 x_attribute8 IN VARCHAR2,
97 x_attribute9 IN VARCHAR2,
98 x_attribute10 IN VARCHAR2,
99 x_attribute11 IN VARCHAR2,
100 x_attribute12 IN VARCHAR2,
101 x_attribute13 IN VARCHAR2,
102 x_attribute14 IN VARCHAR2,
103 x_attribute15 IN VARCHAR2,
104 x_attribute16 IN VARCHAR2,
105 x_attribute17 IN VARCHAR2,
106 x_attribute18 IN VARCHAR2,
107 x_attribute19 IN VARCHAR2,
108 x_attribute20 IN VARCHAR2,
109 x_rvsn_id IN NUMBER,
110 x_alt_pell_schedule IN VARCHAR2,
111 x_award_number_txt IN VARCHAR2,
112 x_legacy_record_flag IN VARCHAR2,
113 x_adplans_id IN NUMBER,
114 x_lock_award_flag IN VARCHAR2,
115 x_app_trans_num_txt IN VARCHAR2,
116 x_awd_proc_status_code IN VARCHAR2,
117 x_notification_status_code IN VARCHAR2,
118 x_notification_status_date IN DATE,
119 x_creation_date IN DATE,
120 x_created_by IN NUMBER,
121 x_last_update_date IN DATE,
122 x_last_updated_by IN NUMBER,
123 x_last_update_login IN NUMBER,
124 x_publish_in_ss_flag IN VARCHAR2
125 ) AS
126 /*
127 || Created By : avenkatr
128 || Created On : 06-DEC-2000
129 || Purpose : Initialises the Old and New references for the columns of the table.
130 || Known limitations, enhancements or remarks :
131 || Change History :
132 || Who When What
133 || veramach 1-NOV-2003 #3160568 Added adplans_id to the procedure signature
134 || (reverse chronological order - newest change first)
135 */
136
137 CURSOR cur_old_ref_values IS
138 SELECT *
139 FROM IGF_AW_AWARD_ALL
140 WHERE rowid = x_rowid;
141
142 BEGIN
143
144 l_rowid := x_rowid;
145
146 -- Code for setting the Old and New Reference Values.
147 -- Populate Old Values.
148 OPEN cur_old_ref_values;
149 FETCH cur_old_ref_values INTO old_references;
150 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
151 CLOSE cur_old_ref_values;
152 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
153 igs_ge_msg_stack.add;
154 app_exception.raise_exception;
155 RETURN;
156 END IF;
157 CLOSE cur_old_ref_values;
158
159 -- Populate New Values.
160 new_references.award_id := x_award_id;
161 new_references.fund_id := x_fund_id;
162 new_references.base_id := x_base_id;
163 new_references.offered_amt := x_offered_amt;
164 new_references.accepted_amt := x_accepted_amt;
165 new_references.paid_amt := x_paid_amt;
166 new_references.packaging_type := x_packaging_type;
167 new_references.batch_id := x_batch_id;
168 new_references.manual_update := x_manual_update;
169 new_references.rules_override := x_rules_override;
170 new_references.award_date := x_award_date;
171 new_references.award_status := x_award_status;
172 new_references.attribute_category := x_attribute_category;
173 new_references.attribute1 := x_attribute1;
174 new_references.attribute2 := x_attribute2;
175 new_references.attribute3 := x_attribute3;
176 new_references.attribute4 := x_attribute4;
177 new_references.attribute5 := x_attribute5;
178 new_references.attribute6 := x_attribute6;
179 new_references.attribute7 := x_attribute7;
180 new_references.attribute8 := x_attribute8;
181 new_references.attribute9 := x_attribute9;
182 new_references.attribute10 := x_attribute10;
183 new_references.attribute11 := x_attribute11;
184 new_references.attribute12 := x_attribute12;
185 new_references.attribute13 := x_attribute13;
186 new_references.attribute14 := x_attribute14;
187 new_references.attribute15 := x_attribute15;
188 new_references.attribute16 := x_attribute16;
189 new_references.attribute17 := x_attribute17;
190 new_references.attribute18 := x_attribute18;
191 new_references.attribute19 := x_attribute19;
192 new_references.attribute20 := x_attribute20;
193 new_references.rvsn_id := x_rvsn_id;
194 new_references.alt_pell_schedule := x_alt_pell_schedule;
195 new_references.award_number_txt := x_award_number_txt;
196 new_references.legacy_record_flag := x_legacy_record_flag;
197 new_references.adplans_id := x_adplans_id;
198 new_references.lock_award_flag := x_lock_award_flag;
199 new_references.app_trans_num_txt := x_app_trans_num_txt;
200 new_references.awd_proc_status_code := x_awd_proc_status_code;
201 new_references.notification_status_code := x_notification_status_code;
202 new_references.notification_status_date := x_notification_status_date;
203 new_references.publish_in_ss_flag := x_publish_in_ss_flag;
204
205
206 IF (p_action = 'UPDATE') THEN
207 new_references.creation_date := old_references.creation_date;
208 new_references.created_by := old_references.created_by;
209 ELSE
210 new_references.creation_date := x_creation_date;
211 new_references.created_by := x_created_by;
212 END IF;
213
214 new_references.last_update_date := x_last_update_date;
215 new_references.last_updated_by := x_last_updated_by;
216 new_references.last_update_login := x_last_update_login;
217
218 END set_column_values;
219
220 PROCEDURE AfterRowInsertUpdateDelete1(
221 p_inserting IN BOOLEAN ,
222 p_updating IN BOOLEAN ,
223 p_deleting IN BOOLEAN
224 ) AS
225 /*-----------------------------------------------------------------
226 || Created By : Sanil Madathil
227 || Created On : 13-Oct-2004
228 || Purpose :
229 || Known limitations, enhancements or remarks :
230 || Change History :
231 || Who When What
232 || (reverse chronological order - newest change first)
233 || tsailaja 03/08/2006 Bug #5337555 FA 163 Include 'GPLUSFL' fund code.
234 --------------------------------------------------------------------*/
235 CURSOR c_sl_clchsn_dtls (
236 cp_n_award_id igf_aw_award_all.award_id%TYPE
237 ) IS
238 SELECT chdt.ROWID row_id,chdt.*
239 FROM igf_sl_clchsn_dtls chdt
240 WHERE chdt.award_id = cp_n_award_id
241 AND chdt.status_code IN ('R','N','D')
242 AND chdt.response_status_code IS NULL
243 AND chdt.cl_version_code = 'RELEASE-4';
244
245 rec_c_sl_clchsn_dtls c_sl_clchsn_dtls%ROWTYPE;
246
247 l_v_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE;
248 l_v_message_name fnd_new_messages.message_name%TYPE;
249 l_b_return_status BOOLEAN;
250 BEGIN
251 IF p_updating THEN
252 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
253 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'inside AfterRowInsertUpdateDelete1 ' );
254 END IF;
255 l_v_fed_fund_code := igf_sl_gen.get_fed_fund_code (p_n_award_id => new_references.award_id,
256 p_v_message_name => l_v_message_name
257 );
258 IF l_v_message_name IS NOT NULL THEN
259 fnd_message.set_name ('IGS',l_v_message_name);
260 igs_ge_msg_stack.add;
261 app_exception.raise_exception;
262 END IF;
263 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
264 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'fund code = '||l_v_fed_fund_code );
265 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'g_v_called_from = '||g_v_called_from );
266 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'old_references.award_status = '||old_references.award_status );
267 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'new_references.award_status = '||new_references.award_status );
268 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'old_references.accepted_amt = '||old_references.accepted_amt );
269 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'new_references.accepted_amt = '||new_references.accepted_amt );
270 END IF;
271 -- tsailaja -FA 163 -Bug 5337555
272 IF l_v_fed_fund_code NOT IN ('FLS','FLU','FLP','ALT','GPLUSFL') THEN
273 RETURN;
274 END IF;
275 IF g_v_called_from NOT IN ('IGFAW016','IGFAW038') THEN
276 RETURN;
277 END IF;
278 IF ((new_references.award_status <> old_references.award_status) AND
279 new_references.award_status = 'CANCELLED' AND
280 new_references.accepted_amt = 0 AND
281 g_v_called_from = 'IGFAW016' ) THEN
282 -- delete all the change records created for this award id
283 FOR rec_c_sl_clchsn_dtls IN c_sl_clchsn_dtls (cp_n_award_id => new_references.award_id)
284 LOOP
285 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
286 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.clchgsnd_id : '||rec_c_sl_clchsn_dtls.clchgsnd_id );
287 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.loan_number_txt : '||rec_c_sl_clchsn_dtls.loan_number_txt );
288 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.change_field_code : '||rec_c_sl_clchsn_dtls.change_field_code );
289 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.change_record_type_txt : '||rec_c_sl_clchsn_dtls.change_record_type_txt );
290 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.change_code_txt : '||rec_c_sl_clchsn_dtls.change_code_txt );
291 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'rec_c_sl_clchsn_dtls.status_code : '||rec_c_sl_clchsn_dtls.status_code );
292 END IF;
293 igf_sl_clchsn_dtls_pkg.delete_row(x_rowid => rec_c_sl_clchsn_dtls.row_id);
294 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
295 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Change Send Record deleted successfully ');
296 END IF;
297 END LOOP;
298 END IF;
299 IF ((new_references.award_status <> old_references.award_status) AND
300 new_references.award_status = 'CANCELLED' AND
301 new_references.accepted_amt = 0)
302 THEN
303 -- invoke the procedure to create loan cancellation change record in igf_sl_clchsn_dtls table
304 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
305 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Loan Cancellation. ' );
306 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_cl_create_chg.create_awd_chg_rec. ' );
307 END IF;
308 igf_sl_cl_create_chg.create_awd_chg_rec
309 (
310 p_n_award_id => new_references.award_id,
311 p_n_old_amount => old_references.accepted_amt ,
312 p_n_new_amount => 0,
313 p_v_chg_type =>'LC',
314 p_b_return_status => l_b_return_status,
315 p_v_message_name => l_v_message_name
316 );
317 -- IF the above call out returns false and error message is returned,
318 -- add the message to the error stack and error message test should be displayed
319 -- in the calling form
320 IF (NOT (l_b_return_status) AND l_v_message_name IS NOT NULL )
321 THEN
322 -- substring of the out bound parameter l_v_message_name is carried
323 -- out since it can expect either IGS OR IGF message
324 fnd_message.set_name(SUBSTR(l_v_message_name,1,3),l_v_message_name);
325 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
326 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Call to igf_sl_cl_create_chg.create_awd_chg_rec returned error '|| l_v_message_name);
327 END IF;
328 igs_ge_msg_stack.add;
329 app_exception.raise_exception;
330 END IF;
331 END IF;
332 -- invoke the procedure to create reinstatement change record in igf_sl_clchsn_dtls table
333 IF ((old_references.accepted_amt <> new_references.accepted_amt) AND
334 new_references.award_status = 'ACCEPTED') THEN
335 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
336 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Loan reinstatement/loan increase. ' );
337 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'invoking igf_sl_cl_create_chg.create_awd_chg_rec. ' );
338 END IF;
339 igf_sl_cl_create_chg.create_awd_chg_rec
340 (
341 p_n_award_id => new_references.award_id,
342 p_n_old_amount => old_references.accepted_amt ,
343 p_n_new_amount => new_references.accepted_amt,
344 p_v_chg_type =>'RIDC',
345 p_b_return_status => l_b_return_status,
346 p_v_message_name => l_v_message_name
347 );
348 -- IF the above call out returns false and error message is returned,
349 -- add the message to the error stack and error message test should be displayed
350 -- in the calling form
351 IF (NOT (l_b_return_status) AND l_v_message_name IS NOT NULL )
352 THEN
353 -- substring of the out bound parameter l_v_message_name is carried
354 -- out since it can expect either IGS OR IGF message
355 fnd_message.set_name(SUBSTR(l_v_message_name,1,3),l_v_message_name);
356 igs_ge_msg_stack.add;
357 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
358 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.AfterRowInsertUpdateDelete1 ', 'Call to igf_sl_cl_create_chg.create_awd_chg_rec returned error '|| l_v_message_name);
359 END IF;
360 app_exception.raise_exception;
361 END IF;
362 END IF;
363 END IF;
364 END AfterRowInsertUpdateDelete1;
365
366 PROCEDURE AfterRowInsertUpdateDelete2(
367 p_inserting IN BOOLEAN ,
368 p_updating IN BOOLEAN ,
369 p_deleting IN BOOLEAN
370 ) AS
371 /*-----------------------------------------------------------------
372 || Created By : veramach
373 || Created On : 16-Nov-2004
374 || Purpose :
375 || Known limitations, enhancements or remarks :
376 || Change History :
377 || Who When What
378 || (reverse chronological order - newest change first)
379 --------------------------------------------------------------------*/
380 lv_rowid ROWID;
381 l_awdh_id igf_aw_awd_hist.awdh_id%TYPE;
382
383 BEGIN
384 IF p_updating THEN
385 lv_rowid := NULL;
386 l_awdh_id := NULL;
387 igf_aw_awd_hist_pkg.insert_row(
388 x_rowid => lv_rowid,
389 x_awdh_id => l_awdh_id,
390 x_award_id => new_references.award_id,
391 x_tran_date => SYSDATE,
392 x_operation_txt => 'UPDATE',
393 x_offered_amt_num => old_references.offered_amt,
394 x_off_adj_num => (new_references.offered_amt - old_references.offered_amt),
395 x_accepted_amt_num => old_references.accepted_amt,
396 x_acc_adj_num => (new_references.accepted_amt - old_references.accepted_amt),
397 x_paid_amt_num => old_references.paid_amt,
398 x_paid_adj_num => (new_references.paid_amt - old_references.paid_amt),
399 x_mode => 'R'
400 );
401 END IF;
402 END AfterRowInsertUpdateDelete2;
403
404 PROCEDURE check_parent_existance AS
405 /*
406 || Created By : avenkatr
407 || Created On : 06-DEC-2000
408 || Purpose : Checks for the existance of Parent records.
409 || Known limitations, enhancements or remarks :
410 || Change History :
411 || Who When What
412 || (reverse chronological order - newest change first)
413 || veramach 05-Jul-2004 bug 3682032 ModIFied chech_parent_existance
414 || so that the procedure tries to acquire lock on the fund record
415 || before throwing an error message
416 || veramach 10-NOV-2003 FA 125 Multiple Distr methods
417 || Added adplans_id as a foreign key
418 */
419 x_lock BOOLEAN := FALSE;
420 BEGIN
421
422 IF (((old_references.rvsn_id = new_references.rvsn_id)) OR
423 ((new_references.rvsn_id IS NULL))) THEN
424 NULL;
425 ELSIF NOT igf_aw_awd_rvsn_rsn_pkg.get_pk_for_validation (
426 new_references.rvsn_id
427 ) THEN
428 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
429 igs_ge_msg_stack.add;
430 app_exception.raise_exception;
431 END IF;
432
433 IF (((old_references.fund_id = new_references.fund_id)) OR ((new_references.fund_id IS NULL))) THEN
434 FOR i IN 1..200 LOOP
435 BEGIN
436 x_lock := igf_aw_fund_mast_pkg.get_pk_for_validation (new_references.fund_id);
437 EXCEPTION
438 WHEN others THEN
439 x_lock := FALSE;
440 END;
441
442 IF x_lock THEN
443 EXIT;
444 ELSE
445 DBMS_LOCK.SLEEP(0.1);
446 END IF;
447 END LOOP;
448
449 IF NOT x_lock THEN
450 fnd_message.set_name ('IGF', 'IGF_AW_FUND_LOCK_ERR');
451 igs_ge_msg_stack.add;
452 app_exception.raise_exception;
453 END IF;
454 ELSE
455
456 FOR i IN 1..200 LOOP
457 BEGIN
458 x_lock := igf_aw_fund_mast_pkg.get_pk_for_validation (new_references.fund_id);
459 EXCEPTION
460 WHEN others THEN
461 x_lock := FALSE;
462 END;
463
464 IF x_lock THEN
465 EXIT;
466 ELSE
467 DBMS_LOCK.SLEEP(0.1);
468 END IF;
469 END LOOP;
470
471 IF NOT x_lock THEN
472 fnd_message.set_name ('IGF', 'IGF_AW_FUND_LOCK_ERR');
473 igs_ge_msg_stack.add;
474 app_exception.raise_exception;
475 END IF;
476 END IF;
477
478 IF (((old_references.base_id = new_references.base_id)) OR
479 ((new_references.base_id IS NULL))) THEN
480 NULL;
481 ELSIF NOT igf_ap_fa_base_rec_pkg.get_pk_for_validation (
482 new_references.base_id
483 ) THEN
484 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
485 igs_ge_msg_stack.add;
486 app_exception.raise_exception;
487 END IF;
488
489 IF (((old_references.adplans_id = new_references.adplans_id)) OR
490 ((new_references.adplans_id IS NULL))) THEN
491 NULL;
492 ELSIF NOT igf_aw_awd_dist_plans_pkg.get_pk_for_validation (
493 new_references.adplans_id
494 ) THEN
495 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
496 igs_ge_msg_stack.add;
497 app_exception.raise_exception;
498 END IF;
499
500 END check_parent_existance;
501
502
503 PROCEDURE check_child_existance IS
504 /*
505 || Created By : avenkatr
506 || Created On : 06-DEC-2000
507 || Purpose : Checks for the existance of Child records.
508 || Known limitations, enhancements or remarks :
509 || Change History :
510 || Who When What
511 || pssahni 21-Oct-2004 Added check child for IGF_GR_COD_HISTORY
512 || bannamal 28-Sep-2004 Bug # 3416863 FA149 COD XML
513 || Added check child for igf_gr_cod_dtls
514 || brajendr 21-Jul-2003 Bug # 2991359 Legacy Part II
515 || Added check child for igf_gr_rfms
516 || (reverse chronological order - newest change first)
517 */
518 BEGIN
519
520
521 igf_aw_awd_disb_pkg.get_fk_igf_aw_award (
522 old_references.award_id
523 );
524
525
526 igf_sl_awd_disb_loc_pkg.get_fk_igf_aw_award (
527 old_references.award_id
528 );
529
530 igf_sl_loans_pkg.get_fk_igf_aw_award (
531 old_references.award_id
532 );
533
534 igf_gr_rfms_pkg.get_fk_igf_aw_award (
535 old_references.award_id
536 );
537
538 igf_gr_cod_dtls_pkg.get_fk_igf_aw_award(
539 old_references.award_id
540 );
541
542 igf_sl_lor_loc_pkg.get_fk_igf_aw_award(
543 old_references.award_id
544 );
545
546 igf_aw_db_chg_dtls_pkg.get_fk_igf_aw_award(
547 old_references.award_id
548 );
549
550 igf_aw_db_cod_dtls_pkg.get_fk_igf_aw_award(
551 old_references.award_id
552 );
553
554 END check_child_existance;
555
556
557 FUNCTION get_pk_for_validation (
558 x_award_id IN NUMBER
559 ) RETURN BOOLEAN AS
560 /*
561 || Created By : avenkatr
562 || Created On : 06-DEC-2000
563 || Purpose : Validates the Primary Key of the table.
564 || Known limitations, enhancements or remarks :
565 || Change History :
566 || Who When What
567 || (reverse chronological order - newest change first)
568 */
569 CURSOR cur_rowid IS
570 SELECT rowid
571 FROM igf_aw_award_all
572 WHERE award_id = x_award_id
573 FOR UPDATE NOWAIT;
574
575 lv_rowid cur_rowid%RowType;
576
577 BEGIN
578
579 OPEN cur_rowid;
580 FETCH cur_rowid INTO lv_rowid;
581 IF (cur_rowid%FOUND) THEN
582 CLOSE cur_rowid;
583 RETURN(TRUE);
584 ELSE
585 CLOSE cur_rowid;
586 RETURN(FALSE);
587 END IF;
588
589 END get_pk_for_validation;
590
591
592 PROCEDURE get_fk_igf_aw_awd_rvsn_rsn (
593 x_rvsn_id IN NUMBER
594 ) AS
595 /*
596 || Created By : prchandr
597 || Created On : 01-JUN-2001
598 || Purpose : Validates the Foreign Keys for the table.
599 || Known limitations, enhancements or remarks :
600 || Change History :
601 || Who When What
602 || (reverse chronological order - newest change first)
603 */
604 CURSOR cur_rowid IS
605 SELECT rowid
606 FROM igf_aw_award_all
607 WHERE ((rvsn_id = x_rvsn_id));
608
609 lv_rowid cur_rowid%RowType;
610
611 BEGIN
612
613 OPEN cur_rowid;
614 FETCH cur_rowid INTO lv_rowid;
615 IF (cur_rowid%FOUND) THEN
616 CLOSE cur_rowid;
617 fnd_message.set_name ('IGF', 'IGF_AW_RVSN_AWD_FK');
618 igs_ge_msg_stack.add;
619 app_exception.raise_exception;
620 RETURN;
621 END IF;
622 CLOSE cur_rowid;
623
624 END get_fk_igf_aw_awd_rvsn_rsn;
625
626
627 PROCEDURE get_fk_igf_aw_fund_mast (
628 x_fund_id IN NUMBER
629 ) AS
630 /*
631 || Created By : avenkatr
632 || Created On : 06-DEC-2000
633 || Purpose : Validates the Foreign Keys for the table.
634 || Known limitations, enhancements or remarks :
635 || Change History :
636 || Who When What
637 || (reverse chronological order - newest change first)
638 */
639 CURSOR cur_rowid IS
640 SELECT rowid
641 FROM igf_aw_award_all
642 WHERE ((fund_id = x_fund_id));
643
644 lv_rowid cur_rowid%RowType;
645
646 BEGIN
647
648 OPEN cur_rowid;
649 FETCH cur_rowid INTO lv_rowid;
650 IF (cur_rowid%FOUND) THEN
651 CLOSE cur_rowid;
652 fnd_message.set_name ('IGF', 'IGF_AW_AWD_FMAST_FK');
653 igs_ge_msg_stack.add;
654 app_exception.raise_exception;
655 RETURN;
656 END IF;
657 CLOSE cur_rowid;
658
659 END get_fk_igf_aw_fund_mast;
660
661
662 PROCEDURE get_fk_igf_ap_fa_base_rec (
663 x_base_id IN NUMBER
664 ) AS
665 /*
666 || Created By : avenkatr
667 || Created On : 06-DEC-2000
668 || Purpose : Validates the Foreign Keys for the table.
669 || Known limitations, enhancements or remarks :
670 || Change History :
671 || Who When What
672 || (reverse chronological order - newest change first)
673 */
674 CURSOR cur_rowid IS
675 SELECT rowid
676 FROM igf_aw_award_all
677 WHERE ((base_id = x_base_id));
678
679 lv_rowid cur_rowid%RowType;
680
681 BEGIN
682
683 OPEN cur_rowid;
684 FETCH cur_rowid INTO lv_rowid;
685 IF (cur_rowid%FOUND) THEN
686 CLOSE cur_rowid;
687 fnd_message.set_name ('IGF', 'IGF_AW_AWD_FA_DETAIL_FK');
688 igs_ge_msg_stack.add;
689 app_exception.raise_exception;
690 RETURN;
691 END IF;
692 CLOSE cur_rowid;
693
694 END get_fk_igf_ap_fa_base_rec;
695
696 PROCEDURE get_fk_igf_aw_awd_dist_plans(
697 x_adplans_id IN NUMBER
698 ) AS
699 ------------------------------------------------------------------
700 --Created by : veramach, Oracle India
701 --Date created: 10-NOV-2003
702 --
703 --Purpose:
704 --
705 --
706 --Known limitations/enhancements and/or remarks:
707 --
708 --Change History:
709 --Who When What
710 -------------------------------------------------------------------
711
712 CURSOR cur_rowid IS
713 SELECT rowid
714 FROM igf_aw_award_all
715 WHERE ((adplans_id = x_adplans_id));
716
717 lv_rowid cur_rowid%ROWTYPE;
718
719 BEGIN
720 OPEN cur_rowid;
721 FETCH cur_rowid INTO lv_rowid;
722 IF (cur_rowid%FOUND) THEN
723 CLOSE cur_rowid;
724 fnd_message.set_name ('IGF', 'IGF_AW_AWD_ADPLANS_FK');
725 igs_ge_msg_stack.add;
726 app_exception.raise_exception;
727 RETURN;
728 END IF;
729 CLOSE cur_rowid;
730 END get_fk_igf_aw_awd_dist_plans;
731
732 PROCEDURE before_dml (
733 p_action IN VARCHAR2,
734 x_rowid IN VARCHAR2 ,
735 x_award_id IN NUMBER ,
736 x_fund_id IN NUMBER ,
737 x_base_id IN NUMBER ,
738 x_offered_amt IN NUMBER ,
739 x_accepted_amt IN NUMBER ,
740 x_paid_amt IN NUMBER ,
741 x_packaging_type IN VARCHAR2 ,
742 x_batch_id IN VARCHAR2 ,
743 x_manual_update IN VARCHAR2 ,
744 x_rules_override IN VARCHAR2 ,
745 x_award_date IN DATE ,
746 x_award_status IN VARCHAR2 ,
747 x_attribute_category IN VARCHAR2 ,
748 x_attribute1 IN VARCHAR2 ,
749 x_attribute2 IN VARCHAR2 ,
750 x_attribute3 IN VARCHAR2 ,
751 x_attribute4 IN VARCHAR2 ,
752 x_attribute5 IN VARCHAR2 ,
753 x_attribute6 IN VARCHAR2 ,
754 x_attribute7 IN VARCHAR2 ,
755 x_attribute8 IN VARCHAR2 ,
756 x_attribute9 IN VARCHAR2 ,
757 x_attribute10 IN VARCHAR2 ,
758 x_attribute11 IN VARCHAR2 ,
759 x_attribute12 IN VARCHAR2 ,
760 x_attribute13 IN VARCHAR2 ,
761 x_attribute14 IN VARCHAR2 ,
762 x_attribute15 IN VARCHAR2 ,
763 x_attribute16 IN VARCHAR2 ,
764 x_attribute17 IN VARCHAR2 ,
765 x_attribute18 IN VARCHAR2 ,
766 x_attribute19 IN VARCHAR2 ,
767 x_attribute20 IN VARCHAR2 ,
768 x_rvsn_id IN NUMBER ,
769 x_alt_pell_schedule IN VARCHAR2 ,
770 x_award_number_txt IN VARCHAR2 ,
771 x_legacy_record_flag IN VARCHAR2 ,
772 x_adplans_id IN NUMBER ,
773 x_lock_award_flag IN VARCHAR2 ,
774 x_app_trans_num_txt IN VARCHAR2 ,
775 x_awd_proc_status_code IN VARCHAR2 ,
776 x_notification_status_code IN VARCHAR2 ,
777 x_notification_status_date IN DATE ,
778 x_creation_date IN DATE ,
779 x_created_by IN NUMBER ,
780 x_last_update_date IN DATE ,
781 x_last_updated_by IN NUMBER ,
782 x_last_update_login IN NUMBER ,
783 x_publish_in_ss_flag IN VARCHAR2
784 ) AS
785 /*
786 || Created By : avenkatr
787 || Created On : 06-DEC-2000
788 || Purpose : Initialises the columns, Checks Constraints, Calls the
789 || Trigger Handlers for the table, before any DML operation.
790 || Known limitations, enhancements or remarks :
791 || Change History :
792 || Who When What
793 || veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure signature
794 || (reverse chronological order - newest change first)
795 */
796 BEGIN
797
798 set_column_values (
799 p_action,
800 x_rowid,
801 x_award_id,
802 x_fund_id,
803 x_base_id,
804 x_offered_amt,
805 x_accepted_amt,
806 x_paid_amt,
807 x_packaging_type,
808 x_batch_id,
809 x_manual_update,
810 x_rules_override,
811 x_award_date,
812 x_award_status,
813 x_attribute_category,
814 x_attribute1,
815 x_attribute2,
816 x_attribute3,
817 x_attribute4,
818 x_attribute5,
819 x_attribute6,
820 x_attribute7,
821 x_attribute8,
822 x_attribute9,
823 x_attribute10,
824 x_attribute11,
825 x_attribute12,
826 x_attribute13,
827 x_attribute14,
828 x_attribute15,
829 x_attribute16,
830 x_attribute17,
831 x_attribute18,
832 x_attribute19,
833 x_attribute20,
834 x_rvsn_id,
835 x_alt_pell_schedule,
836 x_award_number_txt,
837 x_legacy_record_flag,
838 x_adplans_id,
839 x_lock_award_flag,
840 x_app_trans_num_txt,
841 x_awd_proc_status_code,
842 x_notification_status_code,
843 x_notification_status_date,
844 x_creation_date,
845 x_created_by,
846 x_last_update_date,
847 x_last_updated_by,
848 x_last_update_login,
849 x_publish_in_ss_flag
850 );
851
852 IF (p_action = 'INSERT') THEN
853 -- Call all the procedures related to Before Insert.
854 IF ( get_pk_for_validation(
855 new_references.award_id
856 )
857 ) THEN
858 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
859 igs_ge_msg_stack.add;
860 app_exception.raise_exception;
861 END IF;
862 check_parent_existance;
863 ELSIF (p_action = 'UPDATE') THEN
864 -- Call all the procedures related to Before Update.
865 check_parent_existance;
866 ELSIF (p_action = 'DELETE') THEN
867 -- Call all the procedures related to Before Delete.
868 check_child_existance;
869 ELSIF (p_action = 'VALIDATE_INSERT') THEN
870 -- Call all the procedures related to Before Insert.
871 IF ( get_pk_for_validation (
872 new_references.award_id
873 )
874 ) THEN
875 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
876 igs_ge_msg_stack.add;
877 app_exception.raise_exception;
878 END IF;
879 ELSIF (p_action = 'VALIDATE_DELETE') THEN
880 check_child_existance;
881 END IF;
882
883 END before_dml;
884
885 /*
886 Created By : bvisvana
887 Created On : 24-May-2005
888 Purpose : Identifies whether the award attribute is changed or not.
889 Known limitations, enhancements or remarks :
890 Change History :
891 Who When What
892 -------------------------------------
893 bvisvana 18-Oct-2005 Bug # 4635941 - NVL check for amount field are done against 0 instead of -1
894 -------------------------------------
895 (reverse chronological order - newest change first)
896 */
897
898 FUNCTION isChangeIn_AwardAttribute(p_award_atrr_code IN IGF_AW_AWARD_LEVEL_HIST.AWARD_ATTRIB_CODE%TYPE)
899 return BOOLEAN
900 AS
901 l_changed BOOLEAN := FALSE;
902 BEGIN
903 -- For offered amount change
904 IF (p_award_atrr_code = 'IGF_AW_AMOUNT_OFFERED') THEN
905 IF(NVL(old_references.OFFERED_AMT,0) <> NVL(new_references.OFFERED_AMT,0)) THEN
906 l_changed := TRUE;
907 END IF;
908 END IF;
909 -- For Accepted amount change
910 IF (p_award_atrr_code = 'IGF_AW_AMOUNT_ACCEPTED') THEN
911 IF (NVL(old_references.ACCEPTED_AMT,0) <> NVL(new_references.ACCEPTED_AMT,0)) THEN
912 l_changed := TRUE;
913 END IF;
914 END IF;
915 -- For Paid amount change
916 IF (p_award_atrr_code = 'IGF_AW_AMOUNT_PAID') THEN
917 IF (NVL(old_references.PAID_AMT ,0) <> NVL(new_references.PAID_AMT ,0)) THEN
918 l_changed := TRUE;
919 END IF;
920 END IF;
921 -- For Award Status change
922 IF (p_award_atrr_code = 'IGF_AW_AWARD_STATUS') THEN
923 IF (NVL(old_references.AWARD_STATUS ,'*') <> NVL(new_references.AWARD_STATUS,'*')) THEN
924 l_changed := TRUE;
925 END IF;
926 END IF;
927 -- For Award Distribution plan change
928 IF (p_award_atrr_code = 'IGF_AW_DIST_PLAN') THEN
929 IF (NVL(old_references.ADPLANS_ID ,-1) <> NVL(new_references.ADPLANS_ID,-1)) THEN
930 l_changed := TRUE;
931 END IF;
932 END IF;
933 -- For lock award change
934 IF (p_award_atrr_code = 'IGF_AW_LOCK_STATUS') THEN
935 IF (NVL(old_references.LOCK_AWARD_FLAG ,'*') <> NVL(new_references.LOCK_AWARD_FLAG,'*')) THEN
936 l_changed := TRUE;
937 END IF;
938 END IF;
939
940 RETURN l_changed;
941 END isChangeIn_AwardAttribute;
942
943 /*
944 Created By : bvisvana
945 Created On : 24-May-2005
946 Purpose : Updates the award history for an given combination of award id,transaction id and attribute type
947 Known limitations, enhancements or remarks :
948 Change History :
949 Who When What
950 -------------------------------------
951 -------------------------------------
952 (reverse chronological order - newest change first)
953 */
954
955 PROCEDURE update_award_history AS
956
957 CURSOR c_lookup_attribute is
958 SELECT lookup_code from igf_lookups_view
959 WHERE lookup_type = 'IGF_AW_AWARD_ATTRIBUTES';
960
961 l_award_atrr_code IGF_AW_AWARD_LEVEL_HIST.AWARD_ATTRIB_CODE%TYPE;
962 l_awd_attr_changed BOOLEAN := FALSE;
963 l_row_id VARCHAR2(30) ;
964
965 BEGIN
966 -- update for the 6 attributes IF any change
967 l_row_id := null;
968 open c_lookup_attribute;
969 LOOP
970 l_awd_attr_changed := FALSE;
971 FETCH c_lookup_attribute INTO l_award_atrr_code;
972 EXIT WHEN c_lookup_attribute%NOTFOUND;
973 l_awd_attr_changed := isChangeIn_AwardAttribute(l_award_atrr_code);
974 l_row_id := null;
975 /* If award attributes Change, then insert / update */
976 IF (l_awd_attr_changed) THEN
977 igf_aw_award_level_hist_pkg.add_row
978 (
979 x_rowid => l_row_id,
980 x_award_id => old_references.AWARD_ID,
981 x_award_hist_tran_id => g_award_hist_tran_id,
982 x_award_attrib_code => l_award_atrr_code,
983 x_award_change_source_code => g_award_change_source,
984 x_old_offered_amt => old_references.OFFERED_AMT,
985 x_new_offered_amt => new_references.OFFERED_AMT,
986 x_old_accepted_amt => old_references.ACCEPTED_AMT,
987 x_new_accepted_amt => new_references.ACCEPTED_AMT,
988 x_old_paid_amt => old_references.PAID_AMT,
989 x_new_paid_amt => new_references.PAID_AMT,
990 x_old_lock_award_flag => old_references.LOCK_AWARD_FLAG,
991 x_new_lock_award_flag => new_references.LOCK_AWARD_FLAG,
992 x_old_award_status_code => old_references.AWARD_STATUS,
993 x_new_award_status_code => new_references.AWARD_STATUS,
994 x_old_adplans_id => old_references.ADPLANS_ID,
995 x_new_adplans_id => new_references.ADPLANS_ID,
996 x_mode => 'R'
997 );
998 END IF;
999 END LOOP;
1000 CLOSE c_lookup_attribute;
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1004 FND_MESSAGE.SET_TOKEN('NAME','igf_aw_award_pkg.update_award_history' || SQLERRM);
1005 IGS_GE_MSG_STACK.ADD;
1006 App_Exception.Raise_Exception;
1007 END update_award_history;
1008
1009
1010 PROCEDURE after_dml (
1011 p_action IN VARCHAR2,
1012 x_rowid IN VARCHAR2
1013 ) AS
1014 /*-----------------------------------------------------------------
1015 || Created By : Sanil Madathil
1016 || Created On : 13 October 2004
1017 || Purpose : Invoke the proceduers related to after update
1018 || Known limitations, enhancements or remarks :
1019 || Change History :
1020 || Who When What
1021 || (reverse chronological order - newest change first)
1022 --------------------------------------------------------------------*/
1023 BEGIN
1024 l_rowid := NULL;
1025 IF (p_action = 'UPDATE') THEN
1026 -- Call all the procedures related to After Update.
1027 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1028 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.after_dml ', 'before call to AfterRowInsertUpdateDelete1 ' );
1029 END IF;
1030 AfterRowInsertUpdateDelete1
1031 (
1032 p_inserting => FALSE,
1033 p_updating => TRUE ,
1034 p_deleting => FALSE
1035 );
1036
1037 --R4 - FA 157 Award Level History
1038 update_award_history;
1039
1040 IF NVL(old_references.offered_amt,-1) <> NVL(new_references.offered_amt,-1) OR
1041 NVL(old_references.accepted_amt,-1) <> NVL(new_references.accepted_amt,-1) OR
1042 NVL(old_references.paid_amt,-1) <> NVL(new_references.paid_amt,-1) THEN
1043 AfterRowInsertUpdateDelete2(
1044 p_inserting => FALSE,
1045 p_updating => TRUE ,
1046 p_deleting => FALSE
1047 );
1048 END IF;
1049 END IF;
1050 END after_dml;
1051
1052
1053
1054 PROCEDURE insert_row (
1055 x_rowid IN OUT NOCOPY VARCHAR2,
1056 x_award_id IN OUT NOCOPY NUMBER,
1057 x_fund_id IN NUMBER,
1058 x_base_id IN NUMBER,
1059 x_offered_amt IN NUMBER,
1060 x_accepted_amt IN NUMBER,
1061 x_paid_amt IN NUMBER,
1062 x_packaging_type IN VARCHAR2,
1063 x_batch_id IN VARCHAR2,
1064 x_manual_update IN VARCHAR2,
1065 x_rules_override IN VARCHAR2,
1066 x_award_date IN DATE,
1067 x_award_status IN VARCHAR2,
1068 x_attribute_category IN VARCHAR2,
1069 x_attribute1 IN VARCHAR2,
1070 x_attribute2 IN VARCHAR2,
1071 x_attribute3 IN VARCHAR2,
1072 x_attribute4 IN VARCHAR2,
1073 x_attribute5 IN VARCHAR2,
1074 x_attribute6 IN VARCHAR2,
1075 x_attribute7 IN VARCHAR2,
1076 x_attribute8 IN VARCHAR2,
1077 x_attribute9 IN VARCHAR2,
1078 x_attribute10 IN VARCHAR2,
1079 x_attribute11 IN VARCHAR2,
1080 x_attribute12 IN VARCHAR2,
1081 x_attribute13 IN VARCHAR2,
1082 x_attribute14 IN VARCHAR2,
1083 x_attribute15 IN VARCHAR2,
1084 x_attribute16 IN VARCHAR2,
1085 x_attribute17 IN VARCHAR2,
1086 x_attribute18 IN VARCHAR2,
1087 x_attribute19 IN VARCHAR2,
1088 x_attribute20 IN VARCHAR2,
1089 x_rvsn_id IN NUMBER,
1090 x_alt_pell_schedule IN VARCHAR2,
1091 x_mode IN VARCHAR2,
1092 x_award_number_txt IN VARCHAR2,
1093 x_legacy_record_flag IN VARCHAR2,
1094 x_adplans_id IN NUMBER,
1095 x_lock_award_flag IN VARCHAR2,
1096 x_app_trans_num_txt IN VARCHAR2,
1097 x_awd_proc_status_code IN VARCHAR2,
1098 x_notification_status_code IN VARCHAR2,
1099 x_notification_status_date IN DATE,
1100 x_publish_in_ss_flag IN VARCHAR2
1101 ) AS
1102 /*
1103 || Created By : avenkatr
1104 || Created On : 06-DEC-2000
1105 || Purpose : Handles the INSERT DML logic for the table.
1106 || Known limitations, enhancements or remarks :
1107 || Change History :
1108 || Who When What
1109 || veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure signature
1110 || (reverse chronological order - newest change first)
1111 */
1112 CURSOR c IS
1113 SELECT rowid
1114 FROM igf_aw_award_all
1115 WHERE award_id = x_award_id;
1116
1117 x_last_update_date DATE;
1118 x_last_updated_by NUMBER;
1119 x_last_update_login NUMBER;
1120 x_request_id NUMBER;
1121 x_program_id NUMBER;
1122 x_program_application_id NUMBER;
1123 x_program_update_date DATE;
1124 l_org_id igf_aw_award_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
1125
1126 BEGIN
1127
1128 x_last_update_date := SYSDATE;
1129 IF (x_mode = 'I') THEN
1130 x_last_updated_by := 1;
1131 x_last_update_login := 0;
1132 ELSIF (x_mode = 'R') THEN
1133 x_last_updated_by := fnd_global.user_id;
1134 IF (x_last_updated_by IS NULL) THEN
1135 x_last_updated_by := -1;
1136 END IF;
1137 x_last_update_login := fnd_global.login_id;
1138 IF (x_last_update_login IS NULL) THEN
1139 x_last_update_login := -1;
1140 END IF;
1141 x_request_id := fnd_global.conc_request_id;
1142 x_program_id := fnd_global.conc_program_id;
1143 x_program_application_id := fnd_global.prog_appl_id;
1144
1145 IF (x_request_id = -1) THEN
1146 x_request_id := NULL;
1147 x_program_id := NULL;
1148 x_program_application_id := NULL;
1149 x_program_update_date := NULL;
1150 ELSE
1151 x_program_update_date := SYSDATE;
1152 END IF;
1153 ELSE
1154 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
1155 igs_ge_msg_stack.add;
1156 app_exception.raise_exception;
1157 END IF;
1158
1159 SELECT igf_aw_award_s.nextval INTO x_award_id FROM dual;
1160
1161 before_dml(
1162 p_action => 'INSERT',
1163 x_rowid => x_rowid,
1164 x_award_id => x_award_id,
1165 x_fund_id => x_fund_id,
1166 x_base_id => x_base_id,
1167 x_offered_amt => x_offered_amt,
1168 x_accepted_amt => x_accepted_amt,
1169 x_paid_amt => x_paid_amt,
1170 x_packaging_type => x_packaging_type,
1171 x_batch_id => x_batch_id,
1172 x_manual_update => x_manual_update,
1173 x_rules_override => x_rules_override,
1174 x_award_date => x_award_date,
1175 x_award_status => x_award_status,
1176 x_attribute_category => x_attribute_category,
1177 x_attribute1 => x_attribute1,
1178 x_attribute2 => x_attribute2,
1179 x_attribute3 => x_attribute3,
1180 x_attribute4 => x_attribute4,
1181 x_attribute5 => x_attribute5,
1182 x_attribute6 => x_attribute6,
1183 x_attribute7 => x_attribute7,
1184 x_attribute8 => x_attribute8,
1185 x_attribute9 => x_attribute9,
1186 x_attribute10 => x_attribute10,
1187 x_attribute11 => x_attribute11,
1188 x_attribute12 => x_attribute12,
1189 x_attribute13 => x_attribute13,
1190 x_attribute14 => x_attribute14,
1191 x_attribute15 => x_attribute15,
1192 x_attribute16 => x_attribute16,
1193 x_attribute17 => x_attribute17,
1194 x_attribute18 => x_attribute18,
1195 x_attribute19 => x_attribute19,
1196 x_attribute20 => x_attribute20,
1197 x_rvsn_id => x_rvsn_id,
1198 x_alt_pell_schedule => x_alt_pell_schedule,
1199 x_award_number_txt => x_award_number_txt,
1200 x_legacy_record_flag => x_legacy_record_flag,
1201 x_adplans_id => x_adplans_id,
1202 x_lock_award_flag => x_lock_award_flag,
1203 x_app_trans_num_txt => x_app_trans_num_txt,
1204 x_awd_proc_status_code => x_awd_proc_status_code,
1205 x_notification_status_code => x_notification_status_code,
1206 x_notification_status_date => x_notification_status_date,
1207 x_creation_date => x_last_update_date,
1208 x_created_by => x_last_updated_by,
1209 x_last_update_date => x_last_update_date,
1210 x_last_updated_by => x_last_updated_by,
1211 x_last_update_login => x_last_update_login,
1212 x_publish_in_ss_flag => x_publish_in_ss_flag
1213 );
1214
1215 INSERT INTO igf_aw_award_all (
1216 award_id,
1217 fund_id,
1218 base_id,
1219 offered_amt,
1220 accepted_amt,
1221 paid_amt,
1222 packaging_type,
1223 batch_id,
1224 manual_update,
1225 rules_override,
1226 award_date,
1227 award_status,
1228 attribute_category,
1229 attribute1,
1230 attribute2,
1231 attribute3,
1232 attribute4,
1233 attribute5,
1234 attribute6,
1235 attribute7,
1236 attribute8,
1237 attribute9,
1238 attribute10,
1239 attribute11,
1240 attribute12,
1241 attribute13,
1242 attribute14,
1243 attribute15,
1244 attribute16,
1245 attribute17,
1246 attribute18,
1247 attribute19,
1248 attribute20,
1249 rvsn_id,
1250 alt_pell_schedule,
1251 award_number_txt,
1252 legacy_record_flag,
1253 adplans_id,
1254 lock_award_flag,
1255 app_trans_num_txt,
1256 awd_proc_status_code,
1257 notification_status_code,
1258 notification_status_date,
1259 creation_date,
1260 created_by,
1261 last_update_date,
1262 last_updated_by,
1263 last_update_login,
1264 request_id,
1265 program_id,
1266 program_application_id,
1267 program_update_date,
1268 org_id,
1269 publish_in_ss_flag
1270 ) VALUES (
1271 new_references.award_id,
1272 new_references.fund_id,
1273 new_references.base_id,
1274 new_references.offered_amt,
1275 new_references.accepted_amt,
1276 new_references.paid_amt,
1277 new_references.packaging_type,
1278 new_references.batch_id,
1279 new_references.manual_update,
1280 new_references.rules_override,
1281 new_references.award_date,
1282 new_references.award_status,
1283 new_references.attribute_category,
1284 new_references.attribute1,
1285 new_references.attribute2,
1286 new_references.attribute3,
1287 new_references.attribute4,
1288 new_references.attribute5,
1289 new_references.attribute6,
1290 new_references.attribute7,
1291 new_references.attribute8,
1292 new_references.attribute9,
1293 new_references.attribute10,
1294 new_references.attribute11,
1295 new_references.attribute12,
1296 new_references.attribute13,
1297 new_references.attribute14,
1298 new_references.attribute15,
1299 new_references.attribute16,
1300 new_references.attribute17,
1301 new_references.attribute18,
1302 new_references.attribute19,
1303 new_references.attribute20,
1304 new_references.rvsn_id,
1305 new_references.alt_pell_schedule,
1306 new_references.award_number_txt,
1307 new_references.legacy_record_flag,
1308 new_references.adplans_id,
1309 new_references.lock_award_flag,
1310 new_references.app_trans_num_txt,
1311 new_references.awd_proc_status_code,
1312 new_references.notification_status_code,
1313 new_references.notification_status_date,
1314 x_last_update_date,
1315 x_last_updated_by,
1316 x_last_update_date,
1317 x_last_updated_by,
1318 x_last_update_login ,
1319 x_request_id,
1320 x_program_id,
1321 x_program_application_id,
1322 x_program_update_date ,
1323 l_org_id,
1324 new_references.publish_in_ss_flag
1325 );
1326
1327 OPEN c;
1328 FETCH c INTO x_rowid;
1329 IF (c%NOTFOUND) THEN
1330 CLOSE c;
1331 RAISE NO_DATA_FOUND;
1332 END IF;
1333 CLOSE c;
1334
1335 /* Update Fund Master */
1336 --
1337 -- Bug 3029739
1338 -- As the variable old_referecnes is a package
1339 -- variable it is retaining the old value
1340 -- For insert routine, we need not have this value so it
1341 -- is being replaced with new_ref
1342 -- This is done so that the following routine would
1343 -- correctly update fund manager totals
1344 --
1345 igf_aw_gen.update_fmast( new_references,
1346 new_references,
1347 'INSERT'
1348 ) ;
1349 EXCEPTION
1350 WHEN OTHERS THEN
1351 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1352 FND_MESSAGE.SET_TOKEN('NAME','igf_aw_award_pkg.insert_row' || SQLERRM);
1353 IGS_GE_MSG_STACK.ADD;
1354 App_Exception.Raise_Exception;
1355 END insert_row;
1356
1357
1358 PROCEDURE lock_row (
1359 x_rowid IN VARCHAR2,
1360 x_award_id IN NUMBER,
1361 x_fund_id IN NUMBER,
1362 x_base_id IN NUMBER,
1363 x_offered_amt IN NUMBER,
1364 x_accepted_amt IN NUMBER,
1365 x_paid_amt IN NUMBER,
1366 x_packaging_type IN VARCHAR2,
1367 x_batch_id IN VARCHAR2,
1368 x_manual_update IN VARCHAR2,
1369 x_rules_override IN VARCHAR2,
1370 x_award_date IN DATE,
1371 x_award_status IN VARCHAR2,
1372 x_attribute_category IN VARCHAR2,
1373 x_attribute1 IN VARCHAR2,
1374 x_attribute2 IN VARCHAR2,
1375 x_attribute3 IN VARCHAR2,
1376 x_attribute4 IN VARCHAR2,
1377 x_attribute5 IN VARCHAR2,
1378 x_attribute6 IN VARCHAR2,
1379 x_attribute7 IN VARCHAR2,
1380 x_attribute8 IN VARCHAR2,
1381 x_attribute9 IN VARCHAR2,
1382 x_attribute10 IN VARCHAR2,
1383 x_attribute11 IN VARCHAR2,
1384 x_attribute12 IN VARCHAR2,
1385 x_attribute13 IN VARCHAR2,
1386 x_attribute14 IN VARCHAR2,
1387 x_attribute15 IN VARCHAR2,
1388 x_attribute16 IN VARCHAR2,
1389 x_attribute17 IN VARCHAR2,
1390 x_attribute18 IN VARCHAR2,
1391 x_attribute19 IN VARCHAR2,
1392 x_attribute20 IN VARCHAR2,
1393 x_rvsn_id IN NUMBER,
1394 x_alt_pell_schedule IN VARCHAR2,
1395 x_award_number_txt IN VARCHAR2,
1396 x_legacy_record_flag IN VARCHAR2,
1397 x_adplans_id IN NUMBER,
1398 x_lock_award_flag IN VARCHAR2,
1399 x_app_trans_num_txt IN VARCHAR2,
1400 x_awd_proc_status_code IN VARCHAR2,
1401 x_notification_status_code IN VARCHAR2,
1402 x_notification_status_date IN DATE,
1403 x_publish_in_ss_flag IN VARCHAR2
1404 ) AS
1405 /*
1406 || Created By : avenkatr
1407 || Created On : 06-DEC-2000
1408 || Purpose : Handles the LOCK mechanism for the table.
1409 || Known limitations, enhancements or remarks :
1410 || Change History :
1411 || Who When What
1412 || veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure signature
1413 || (reverse chronological order - newest change first)
1414 */
1415 CURSOR c1 IS
1416 SELECT
1417 fund_id,
1418 base_id,
1419 offered_amt,
1420 accepted_amt,
1421 paid_amt,
1422 packaging_type,
1423 batch_id,
1424 manual_update,
1425 rules_override,
1426 award_date,
1427 award_status,
1428 attribute_category,
1429 attribute1,
1430 attribute2,
1431 attribute3,
1432 attribute4,
1433 attribute5,
1434 attribute6,
1435 attribute7,
1436 attribute8,
1437 attribute9,
1438 attribute10,
1439 attribute11,
1440 attribute12,
1441 attribute13,
1442 attribute14,
1443 attribute15,
1444 attribute16,
1445 attribute17,
1446 attribute18,
1447 attribute19,
1448 attribute20,
1449 rvsn_id,
1450 alt_pell_schedule,
1451 award_number_txt,
1452 legacy_record_flag,
1453 adplans_id,
1454 lock_award_flag,
1455 app_trans_num_txt,
1456 awd_proc_status_code,
1457 notification_status_code,
1458 notification_status_date,
1459 publish_in_ss_flag
1460 FROM igf_aw_award_all
1461 WHERE rowid = x_rowid
1462 FOR UPDATE NOWAIT;
1463
1464 tlinfo c1%ROWTYPE;
1465
1466 BEGIN
1467
1468 OPEN c1;
1469 FETCH c1 INTO tlinfo;
1470 IF (c1%notfound) THEN
1471 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1472 igs_ge_msg_stack.add;
1473 CLOSE c1;
1474 app_exception.raise_exception;
1475 RETURN;
1476 END IF;
1477 CLOSE c1;
1478
1479 IF (
1480 (tlinfo.fund_id = x_fund_id)
1481 AND (tlinfo.base_id = x_base_id)
1482 AND ((tlinfo.offered_amt = x_offered_amt) OR ((tlinfo.offered_amt IS NULL) AND (X_offered_amt IS NULL)))
1483 AND ((tlinfo.accepted_amt = x_accepted_amt) OR ((tlinfo.accepted_amt IS NULL) AND (X_accepted_amt IS NULL)))
1484 AND ((tlinfo.paid_amt = x_paid_amt) OR ((tlinfo.paid_amt IS NULL) AND (X_paid_amt IS NULL)))
1485 AND ((tlinfo.packaging_type = x_packaging_type) OR ((tlinfo.packaging_type IS NULL) AND (X_packaging_type IS NULL)))
1486 AND ((tlinfo.batch_id = x_batch_id) OR ((tlinfo.batch_id IS NULL) AND (X_batch_id IS NULL)))
1487 AND ((tlinfo.manual_update = x_manual_update) OR ((tlinfo.manual_update IS NULL) AND (X_manual_update IS NULL)))
1488 AND ((tlinfo.rules_override = x_rules_override) OR ((tlinfo.rules_override IS NULL) AND (X_rules_override IS NULL)))
1489 AND ((tlinfo.award_date = x_award_date) OR ((tlinfo.award_date IS NULL) AND (X_award_date IS NULL)))
1490 AND ((tlinfo.award_status = x_award_status) OR ((tlinfo.award_status IS NULL) AND (X_award_status IS NULL)))
1491 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
1492 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
1493 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
1494 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
1495 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
1496 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
1497 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
1498 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
1499 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
1500 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
1501 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
1502 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
1503 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
1504 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
1505 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
1506 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
1507 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
1508 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
1509 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
1510 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
1511 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
1512 AND ((tlinfo.rvsn_id = x_rvsn_id) OR ((tlinfo.rvsn_id IS NULL) AND (x_rvsn_id IS NULL)))
1513 AND ((tlinfo.alt_pell_schedule = x_alt_pell_schedule) OR ((tlinfo.alt_pell_schedule IS NULL) AND (x_alt_pell_schedule IS NULL)))
1514 AND ((tlinfo.award_number_txt = x_award_number_txt) OR ((tlinfo.award_number_txt IS NULL) AND (x_award_number_txt IS NULL)))
1515 AND ((tlinfo.legacy_record_flag = x_legacy_record_flag) OR ((tlinfo.legacy_record_flag IS NULL) AND (x_legacy_record_flag IS NULL)))
1516 AND ((tlinfo.adplans_id = x_adplans_id) OR ((tlinfo.adplans_id IS NULL) AND (x_adplans_id IS NULL)))
1517 AND ((tlinfo.lock_award_flag = x_lock_award_flag) OR ((tlinfo.lock_award_flag IS NULL ) AND (x_lock_award_flag IS NULL)))
1518 AND ((tlinfo.app_trans_num_txt = x_app_trans_num_txt) OR ((tlinfo.app_trans_num_txt IS NULL ) AND (x_app_trans_num_txt IS NULL)))
1519 AND ((tlinfo.awd_proc_status_code = x_awd_proc_status_code) OR ((tlinfo.awd_proc_status_code IS NULL ) AND (x_awd_proc_status_code IS NULL)))
1520 AND ((tlinfo.notification_status_code= x_notification_status_code) OR ((tlinfo.notification_status_code IS NULL ) AND (x_notification_status_code IS NULL)))
1521 AND ((tlinfo.notification_status_date= x_notification_status_date) OR ((tlinfo.notification_status_date IS NULL ) AND (x_notification_status_date IS NULL)))
1522 AND ((tlinfo.publish_in_ss_flag = x_publish_in_ss_flag) OR ((tlinfo.publish_in_ss_flag IS NULL ) AND (x_publish_in_ss_flag IS NULL)))
1523 ) THEN
1524 NULL;
1525 ELSE
1526 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1527 igs_ge_msg_stack.add;
1528 app_exception.raise_exception;
1529 END IF;
1530
1531 RETURN;
1532
1533 END lock_row;
1534
1535 /*
1536 Created By : bvisvana
1537 Created On : 24-May-2005
1538 Purpose : Checks the award history for a given award id.
1539 If award history record does not exists, then it create a record history
1540 Known limitations, enhancements or remarks :
1541 Change History :
1542 Who When What
1543 -------------------------------------
1544 -------------------------------------
1545 (reverse chronological order - newest change first)
1546 */
1547
1548 PROCEDURE check_award_history
1549 AS
1550 CURSOR c_lookup_attribute is
1551 SELECT lookup_code from igf_lookups_view
1552 WHERE lookup_type = 'IGF_AW_AWARD_ATTRIBUTES';
1553
1554 l_award_atrr_code IGF_AW_AWARD_LEVEL_HIST.AWARD_ATTRIB_CODE%TYPE;
1555 l_row_id VARCHAR2(30) ;
1556
1557 BEGIN
1558 IF g_award_hist_tran_id IS NULL THEN
1559 SELECT IGF_AW_AWARD_LEVEL_HIST_S.NEXTVAL INTO g_award_hist_tran_id from dual;
1560 END IF;
1561 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1562 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.check_award_history ', 'Transaction Id = '||g_award_hist_tran_id );
1563 END IF;
1564 END check_award_history;
1565
1566
1567 PROCEDURE update_row (
1568 x_rowid IN VARCHAR2,
1569 x_award_id IN NUMBER,
1570 x_fund_id IN NUMBER,
1571 x_base_id IN NUMBER,
1572 x_offered_amt IN NUMBER,
1573 x_accepted_amt IN NUMBER,
1574 x_paid_amt IN NUMBER,
1575 x_packaging_type IN VARCHAR2,
1576 x_batch_id IN VARCHAR2,
1577 x_manual_update IN VARCHAR2,
1578 x_rules_override IN VARCHAR2,
1579 x_award_date IN DATE,
1580 x_award_status IN VARCHAR2,
1581 x_attribute_category IN VARCHAR2,
1582 x_attribute1 IN VARCHAR2,
1583 x_attribute2 IN VARCHAR2,
1584 x_attribute3 IN VARCHAR2,
1585 x_attribute4 IN VARCHAR2,
1586 x_attribute5 IN VARCHAR2,
1587 x_attribute6 IN VARCHAR2,
1588 x_attribute7 IN VARCHAR2,
1589 x_attribute8 IN VARCHAR2,
1590 x_attribute9 IN VARCHAR2,
1591 x_attribute10 IN VARCHAR2,
1592 x_attribute11 IN VARCHAR2,
1593 x_attribute12 IN VARCHAR2,
1594 x_attribute13 IN VARCHAR2,
1595 x_attribute14 IN VARCHAR2,
1596 x_attribute15 IN VARCHAR2,
1597 x_attribute16 IN VARCHAR2,
1598 x_attribute17 IN VARCHAR2,
1599 x_attribute18 IN VARCHAR2,
1600 x_attribute19 IN VARCHAR2,
1601 x_attribute20 IN VARCHAR2,
1602 x_rvsn_id IN NUMBER,
1603 x_alt_pell_schedule IN VARCHAR2,
1604 x_mode IN VARCHAR2,
1605 x_award_number_txt IN VARCHAR2,
1606 x_legacy_record_flag IN VARCHAR2,
1607 x_adplans_id IN NUMBER,
1608 x_lock_award_flag IN VARCHAR2,
1609 x_app_trans_num_txt IN VARCHAR2,
1610 x_awd_proc_status_code IN VARCHAR2,
1611 x_notification_status_code IN VARCHAR2,
1612 x_notification_status_date IN DATE,
1613 x_called_from IN VARCHAR2,
1614 x_publish_in_ss_flag IN VARCHAR2
1615 ) AS
1616 /*
1617 || Created By : avenkatr
1618 || Created On : 06-DEC-2000
1619 || Purpose : Handles the UPDATE DML logic for the table.
1620 || Known limitations, enhancements or remarks :
1621 || Change History :
1622 || Who When What
1623 || veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure signature
1624 || (reverse chronological order - newest change first)
1625 */
1626 x_last_update_date DATE ;
1627 x_last_updated_by NUMBER;
1628 x_last_update_login NUMBER;
1629 x_request_id NUMBER;
1630 x_program_id NUMBER;
1631 x_program_application_id NUMBER;
1632 x_program_update_date DATE;
1633
1634 BEGIN
1635 x_last_update_date := SYSDATE;
1636 IF (X_MODE = 'I') THEN
1637 x_last_updated_by := 1;
1638 x_last_update_login := 0;
1639 ELSIF (x_mode = 'R') THEN
1640 x_last_updated_by := fnd_global.user_id;
1641 IF x_last_updated_by IS NULL THEN
1642 x_last_updated_by := -1;
1643 END IF;
1644 x_last_update_login := fnd_global.login_id;
1645 IF (x_last_update_login IS NULL) THEN
1646 x_last_update_login := -1;
1647 END IF;
1648 ELSE
1649 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1650 igs_ge_msg_stack.add;
1651 app_exception.raise_exception;
1652 END IF;
1653
1654 before_dml(
1655 p_action => 'UPDATE',
1656 x_rowid => x_rowid,
1657 x_award_id => x_award_id,
1658 x_fund_id => x_fund_id,
1659 x_base_id => x_base_id,
1660 x_offered_amt => x_offered_amt,
1661 x_accepted_amt => x_accepted_amt,
1662 x_paid_amt => x_paid_amt,
1663 x_packaging_type => x_packaging_type,
1664 x_batch_id => x_batch_id,
1665 x_manual_update => x_manual_update,
1666 x_rules_override => x_rules_override,
1667 x_award_date => x_award_date,
1668 x_award_status => x_award_status,
1669 x_attribute_category => x_attribute_category,
1670 x_attribute1 => x_attribute1,
1671 x_attribute2 => x_attribute2,
1672 x_attribute3 => x_attribute3,
1673 x_attribute4 => x_attribute4,
1674 x_attribute5 => x_attribute5,
1675 x_attribute6 => x_attribute6,
1676 x_attribute7 => x_attribute7,
1677 x_attribute8 => x_attribute8,
1678 x_attribute9 => x_attribute9,
1679 x_attribute10 => x_attribute10,
1680 x_attribute11 => x_attribute11,
1681 x_attribute12 => x_attribute12,
1682 x_attribute13 => x_attribute13,
1683 x_attribute14 => x_attribute14,
1684 x_attribute15 => x_attribute15,
1685 x_attribute16 => x_attribute16,
1686 x_attribute17 => x_attribute17,
1687 x_attribute18 => x_attribute18,
1688 x_attribute19 => x_attribute19,
1689 x_attribute20 => x_attribute20,
1690 x_rvsn_id => x_rvsn_id,
1691 x_alt_pell_schedule => x_alt_pell_schedule,
1692 x_award_number_txt => x_award_number_txt,
1693 x_legacy_record_flag => x_legacy_record_flag,
1694 x_adplans_id => x_adplans_id,
1695 x_lock_award_flag => x_lock_award_flag,
1696 x_app_trans_num_txt => x_app_trans_num_txt,
1697 x_awd_proc_status_code => x_awd_proc_status_code,
1698 x_notification_status_code => x_notification_status_code,
1699 x_notification_status_date => x_notification_status_date,
1700 x_creation_date => x_last_update_date,
1701 x_created_by => x_last_updated_by,
1702 x_last_update_date => x_last_update_date,
1703 x_last_updated_by => x_last_updated_by,
1704 x_last_update_login => x_last_update_login,
1705 x_publish_in_ss_flag => x_publish_in_ss_flag
1706 );
1707
1708 IF (x_mode = 'R') THEN
1709 x_request_id := fnd_global.conc_request_id;
1710 x_program_id := fnd_global.conc_program_id;
1711 x_program_application_id := fnd_global.prog_appl_id;
1712 IF (x_request_id = -1) THEN
1713 x_request_id := old_references.request_id;
1714 x_program_id := old_references.program_id;
1715 x_program_application_id := old_references.program_application_id;
1716 x_program_update_date := old_references.program_update_date;
1717 ELSE
1718 x_program_update_date := SYSDATE;
1719 END IF;
1720 END IF;
1721
1722 -- R4 - Award History FA 157
1723 check_award_history;
1724
1725 UPDATE igf_aw_award_all
1726 SET
1727 fund_id = new_references.fund_id,
1728 base_id = new_references.base_id,
1729 offered_amt = new_references.offered_amt,
1730 accepted_amt = new_references.accepted_amt,
1731 paid_amt = new_references.paid_amt,
1732 packaging_type = new_references.packaging_type,
1733 batch_id = new_references.batch_id,
1734 manual_update = new_references.manual_update,
1735 rules_override = new_references.rules_override,
1736 award_date = new_references.award_date,
1737 award_status = new_references.award_status,
1738 attribute_category = new_references.attribute_category,
1739 attribute1 = new_references.attribute1,
1740 attribute2 = new_references.attribute2,
1741 attribute3 = new_references.attribute3,
1742 attribute4 = new_references.attribute4,
1743 attribute5 = new_references.attribute5,
1744 attribute6 = new_references.attribute6,
1745 attribute7 = new_references.attribute7,
1746 attribute8 = new_references.attribute8,
1747 attribute9 = new_references.attribute9,
1748 attribute10 = new_references.attribute10,
1749 attribute11 = new_references.attribute11,
1750 attribute12 = new_references.attribute12,
1751 attribute13 = new_references.attribute13,
1752 attribute14 = new_references.attribute14,
1753 attribute15 = new_references.attribute15,
1754 attribute16 = new_references.attribute16,
1755 attribute17 = new_references.attribute17,
1756 attribute18 = new_references.attribute18,
1757 attribute19 = new_references.attribute19,
1758 attribute20 = new_references.attribute20,
1759 rvsn_id = new_references.rvsn_id,
1760 alt_pell_schedule = new_references.alt_pell_schedule,
1761 award_number_txt = new_references.award_number_txt,
1762 legacy_record_flag = new_references.legacy_record_flag,
1763 adplans_id = new_references.adplans_id,
1764 lock_award_flag = new_references.lock_award_flag,
1765 app_trans_num_txt = new_references.app_trans_num_txt,
1766 awd_proc_status_code = new_references.awd_proc_status_code,
1767 notification_status_code = new_references.notification_status_code,
1768 notification_status_date = new_references.notification_status_date,
1769 last_update_date = x_last_update_date,
1770 last_updated_by = x_last_updated_by,
1771 last_update_login = x_last_update_login ,
1772 request_id = x_request_id,
1773 program_id = x_program_id,
1774 program_application_id = x_program_application_id,
1775 program_update_date = x_program_update_date,
1776 publish_in_ss_flag = new_references.publish_in_ss_flag
1777 WHERE rowid = x_rowid;
1778
1779 IF (SQL%NOTFOUND) THEN
1780 RAISE NO_DATA_FOUND;
1781 END IF;
1782
1783 -- Update the Fund Master
1784 igf_aw_gen.update_fmast( old_references,
1785 new_references,
1786 'UPDATE'
1787 );
1788 g_v_called_from := x_called_from;
1789 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1790 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.update_row ', 'g_v_called_from '||g_v_called_from );
1791 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.update_row ', 'before invoking after_dml ' );
1792 END IF;
1793 after_dml(
1794 p_action =>'UPDATE',
1795 x_rowid => x_rowid
1796 );
1797 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1798 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.update_row ', 'after invoking after_dml ' );
1799 END IF;
1800 g_v_called_from := NULL;
1801 END update_row;
1802
1803
1804 PROCEDURE add_row (
1805 x_rowid IN OUT NOCOPY VARCHAR2,
1806 x_award_id IN OUT NOCOPY NUMBER,
1807 x_fund_id IN NUMBER,
1808 x_base_id IN NUMBER,
1809 x_offered_amt IN NUMBER,
1810 x_accepted_amt IN NUMBER,
1811 x_paid_amt IN NUMBER,
1812 x_packaging_type IN VARCHAR2,
1813 x_batch_id IN VARCHAR2,
1814 x_manual_update IN VARCHAR2,
1815 x_rules_override IN VARCHAR2,
1816 x_award_date IN DATE,
1817 x_award_status IN VARCHAR2,
1818 x_attribute_category IN VARCHAR2,
1819 x_attribute1 IN VARCHAR2,
1820 x_attribute2 IN VARCHAR2,
1821 x_attribute3 IN VARCHAR2,
1822 x_attribute4 IN VARCHAR2,
1823 x_attribute5 IN VARCHAR2,
1824 x_attribute6 IN VARCHAR2,
1825 x_attribute7 IN VARCHAR2,
1826 x_attribute8 IN VARCHAR2,
1827 x_attribute9 IN VARCHAR2,
1828 x_attribute10 IN VARCHAR2,
1829 x_attribute11 IN VARCHAR2,
1830 x_attribute12 IN VARCHAR2,
1831 x_attribute13 IN VARCHAR2,
1832 x_attribute14 IN VARCHAR2,
1833 x_attribute15 IN VARCHAR2,
1834 x_attribute16 IN VARCHAR2,
1835 x_attribute17 IN VARCHAR2,
1836 x_attribute18 IN VARCHAR2,
1837 x_attribute19 IN VARCHAR2,
1838 x_attribute20 IN VARCHAR2,
1839 x_rvsn_id IN NUMBER,
1840 x_alt_pell_schedule IN VARCHAR2,
1841 x_mode IN VARCHAR2,
1842 x_award_number_txt IN VARCHAR2,
1843 x_legacy_record_flag IN VARCHAR2,
1844 x_adplans_id IN NUMBER,
1845 x_lock_award_flag IN VARCHAR2,
1846 x_app_trans_num_txt IN VARCHAR2,
1847 x_awd_proc_status_code IN VARCHAR2,
1848 x_notification_status_code IN VARCHAR2,
1849 x_notification_status_date IN DATE,
1850 x_publish_in_ss_flag IN VARCHAR2
1851 ) AS
1852 /*
1853 || Created By : avenkatr
1854 || Created On : 06-DEC-2000
1855 || Purpose : Adds a row IF there is no existing row, otherwise updates existing row in the table.
1856 || Known limitations, enhancements or remarks :
1857 || Change History :
1858 || Who When What
1859 || veramach 1-NOV-2003 #3160568 Added adplans_id in the procedure call
1860 || (reverse chronological order - newest change first)
1861 */
1862 CURSOR c1 IS
1863 SELECT rowid
1864 FROM igf_aw_award_all
1865 WHERE award_id = x_award_id;
1866
1867 BEGIN
1868
1869 OPEN c1;
1870 FETCH c1 INTO x_rowid;
1871 IF (c1%NOTFOUND) THEN
1872 CLOSE c1;
1873
1874 insert_row (
1875 x_rowid,
1876 x_award_id,
1877 x_fund_id,
1878 x_base_id,
1879 x_offered_amt,
1880 x_accepted_amt,
1881 x_paid_amt,
1882 x_packaging_type,
1883 x_batch_id,
1884 x_manual_update,
1885 x_rules_override,
1886 x_award_date,
1887 x_award_status,
1888 x_attribute_category,
1889 x_attribute1,
1890 x_attribute2,
1891 x_attribute3,
1892 x_attribute4,
1893 x_attribute5,
1894 x_attribute6,
1895 x_attribute7,
1896 x_attribute8,
1897 x_attribute9,
1898 x_attribute10,
1899 x_attribute11,
1900 x_attribute12,
1901 x_attribute13,
1902 x_attribute14,
1903 x_attribute15,
1904 x_attribute16,
1905 x_attribute17,
1906 x_attribute18,
1907 x_attribute19,
1908 x_attribute20,
1909 x_rvsn_id,
1910 x_alt_pell_schedule,
1911 x_mode,
1912 x_award_number_txt,
1913 x_legacy_record_flag,
1914 x_adplans_id,
1915 x_lock_award_flag,
1916 x_app_trans_num_txt,
1917 x_awd_proc_status_code,
1918 x_notification_status_code,
1919 x_notification_status_date,
1920 x_publish_in_ss_flag
1921 );
1922 RETURN;
1923 END IF;
1924 CLOSE c1;
1925
1926 update_row (
1927 x_rowid,
1928 x_award_id,
1929 x_fund_id,
1930 x_base_id,
1931 x_offered_amt,
1932 x_accepted_amt,
1933 x_paid_amt,
1934 x_packaging_type,
1935 x_batch_id,
1936 x_manual_update,
1937 x_rules_override,
1938 x_award_date,
1939 x_award_status,
1940 x_attribute_category,
1941 x_attribute1,
1942 x_attribute2,
1943 x_attribute3,
1944 x_attribute4,
1945 x_attribute5,
1946 x_attribute6,
1947 x_attribute7,
1948 x_attribute8,
1949 x_attribute9,
1950 x_attribute10,
1951 x_attribute11,
1952 x_attribute12,
1953 x_attribute13,
1954 x_attribute14,
1955 x_attribute15,
1956 x_attribute16,
1957 x_attribute17,
1958 x_attribute18,
1959 x_attribute19,
1960 x_attribute20,
1961 x_rvsn_id,
1962 x_alt_pell_schedule,
1963 x_mode,
1964 x_award_number_txt,
1965 x_legacy_record_flag,
1966 x_adplans_id,
1967 x_lock_award_flag,
1968 x_app_trans_num_txt,
1969 x_awd_proc_status_code,
1970 x_notification_status_code,
1971 x_notification_status_date,
1972 x_publish_in_ss_flag
1973 );
1974
1975 END add_row;
1976
1977
1978 PROCEDURE delete_row (
1979 x_rowid IN VARCHAR2
1980 ) AS
1981 /*
1982 || Created By : avenkatr
1983 || Created On : 06-DEC-2000
1984 || Purpose : Handles the DELETE DML logic for the table.
1985 || Known limitations, enhancements or remarks :
1986 || Change History :
1987 || Who When What
1988 || (reverse chronological order - newest change first)
1989 */
1990 BEGIN
1991
1992 before_dml (
1993 p_action => 'DELETE',
1994 x_rowid => x_rowid
1995 );
1996
1997 DELETE FROM igf_aw_award_all
1998 WHERE rowid = x_rowid;
1999
2000 IF (SQL%NOTFOUND) THEN
2001 RAISE NO_DATA_FOUND;
2002 END IF;
2003
2004 -- Update Fund Master
2005 igf_aw_gen.update_fmast( old_references,
2006 new_references,
2007 'DELETE'
2008 ) ;
2009
2010 END delete_row;
2011
2012
2013 /*
2014 Created By : bvisvana
2015 Created On : 24-May-2005
2016 Purpose : Sets the award change source
2017 Known limitations, enhancements or remarks :
2018 Change History :
2019 Who When What
2020 -------------------------------------
2021 -------------------------------------
2022 (reverse chronological order - newest change first)
2023 */
2024
2025 PROCEDURE set_award_change_source (
2026 p_award_change_source IN igf_aw_award_level_hist.AWARD_CHANGE_SOURCE_CODE%TYPE
2027 ) AS
2028 BEGIN
2029 g_award_change_source := p_award_change_source ;
2030 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2031 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_aw_award_pkg.set_award_change_source ', 'g_award_source_change= '||g_award_change_source);
2032 END IF;
2033 END set_award_change_source;
2034
2035 /*
2036 Created By : bvisvana
2037 Created On : 15-June-2005
2038 Purpose : Reset the Award History Transaction
2039 Known limitations, enhancements or remarks :
2040 Change History :
2041 Who When What
2042 -------------------------------------
2043 -------------------------------------
2044 (reverse chronological order - newest change first)
2045 */
2046 PROCEDURE reset_awd_hist_trans_id AS
2047 BEGIN
2048 g_award_hist_tran_id := NULL;
2049 END reset_awd_hist_trans_id;
2050
2051 END igf_aw_award_pkg;