[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_AWARD_T_PKG
Source
1 PACKAGE BODY igf_aw_award_t_pkg AS
2 /* $Header: IGFWI26B.pls 120.0 2005/06/01 13:45:29 appldev noship $ */
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_AWARD_T_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 | veramach 12-Oct-2004 FA 152 Added award_id, |
31 | lock_award_flag |
32 | veramach 03-DEC-2003 FA 131 Added app_trans_num_txt |
33 | veramach 21-NOV-2003 FA 125 Added adplans_id to tbh |
34 *=======================================================================*/
35
36 l_rowid VARCHAR2(25);
37 old_references igf_aw_award_t_all%ROWTYPE;
38 new_references igf_aw_award_t_all%ROWTYPE;
39
40 PROCEDURE set_column_values (
41 p_action IN VARCHAR2,
42 x_rowid IN VARCHAR2 DEFAULT NULL,
43 x_process_id IN NUMBER DEFAULT NULL,
44 x_sl_number IN NUMBER DEFAULT NULL,
45 x_fund_id IN NUMBER DEFAULT NULL,
46 x_base_id IN NUMBER DEFAULT NULL,
47 x_offered_amt IN NUMBER DEFAULT NULL,
48 x_accepted_amt IN NUMBER DEFAULT NULL,
49 x_paid_amt IN NUMBER DEFAULT NULL,
50 x_need_reduction_amt IN NUMBER DEFAULT NULL,
51 x_flag IN VARCHAR2 DEFAULT NULL,
52 x_temp_num_val1 IN NUMBER DEFAULT NULL,
53 x_temp_num_val2 IN NUMBER DEFAULT NULL,
54 x_temp_char_val1 IN VARCHAR2 DEFAULT NULL,
55 x_tp_cal_type IN VARCHAR2 DEFAULT NULL,
56 x_tp_sequence_number IN NUMBER DEFAULT NULL,
57 x_ld_cal_type IN VARCHAR2 DEFAULT NULL,
58 x_ld_sequence_number IN NUMBER DEFAULT NULL,
59 x_adplans_id IN NUMBER DEFAULT NULL,
60 x_app_trans_num_txt IN VARCHAR2 DEFAULT NULL,
61 x_award_id IN NUMBER DEFAULT NULL,
62 x_lock_award_flag IN VARCHAR2 DEFAULT NULL,
63 x_temp_val3_num IN NUMBER DEFAULT NULL,
64 x_temp_val4_num IN NUMBER DEFAULT NULL,
65 x_temp_char2_txt IN VARCHAR2 DEFAULT NULL,
66 x_temp_char3_txt IN VARCHAR2 DEFAULT NULL,
67 x_creation_date IN DATE DEFAULT NULL,
68 x_created_by IN NUMBER DEFAULT NULL,
69 x_last_update_date IN DATE DEFAULT NULL,
70 x_last_updated_by IN NUMBER DEFAULT NULL,
71 x_last_update_login IN NUMBER DEFAULT NULL
72 ) AS
73 /*
74 || Created By : cdcruz
75 || Created On : 08-NOV-2000
76 || Purpose : Initialises the Old and New references for the columns of the table.
77 || Known limitations, enhancements or remarks :
78 || Change History :
79 || Who When What
80 || (reverse chronological order - newest change first)
81 || veramach 12-Oct-2004 FA 152 Added award_id,lock_award_flag
82 || veramach 03-DEC-2003 FA 131 Added app_trans_num_txt to tbh signature
83 || veramach 21-NOV-2003 FA 125 Added adplans_id to tbh signature
84 */
85
86 CURSOR cur_old_ref_values IS
87 SELECT *
88 FROM IGF_AW_AWARD_T_ALL
89 WHERE rowid = x_rowid;
90
91 BEGIN
92
93 l_rowid := x_rowid;
94
95 -- Code for setting the Old and New Reference Values.
96 -- Populate Old Values.
97 OPEN cur_old_ref_values;
98 FETCH cur_old_ref_values INTO old_references;
99 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
100 CLOSE cur_old_ref_values;
101 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
102 igs_ge_msg_stack.add;
103 app_exception.raise_exception;
104 RETURN;
105 END IF;
106 CLOSE cur_old_ref_values;
107
108 -- Populate New Values.
109 new_references.process_id := x_process_id;
110 new_references.sl_number := x_sl_number;
111 new_references.fund_id := x_fund_id;
112 new_references.base_id := x_base_id;
113 new_references.offered_amt := x_offered_amt;
114 new_references.accepted_amt := x_accepted_amt;
115 new_references.paid_amt := x_paid_amt;
116 new_references.need_reduction_amt := x_need_reduction_amt;
117 new_references.flag := x_flag;
118 new_references.temp_num_val1 := x_temp_num_val1;
119 new_references.temp_num_val2 := x_temp_num_val2;
120 new_references.temp_char_val1 := x_temp_char_val1;
121 new_references.tp_cal_type := x_tp_cal_type;
122 new_references.tp_sequence_number := x_tp_sequence_number;
123 new_references.ld_cal_type := x_ld_cal_type;
124 new_references.ld_sequence_number := x_ld_sequence_number;
125 new_references.adplans_id := x_adplans_id;
126 new_references.app_trans_num_txt := x_app_trans_num_txt;
127 new_references.award_id := x_award_id;
128 new_references.lock_award_flag := x_lock_award_flag;
129 new_references.temp_val3_num := x_temp_val3_num;
130 new_references.temp_val4_num := x_temp_val4_num;
131 new_references.temp_char2_txt := x_temp_char2_txt;
132 new_references.temp_char3_txt := x_temp_char3_txt;
133
134 IF (p_action = 'UPDATE') THEN
135 new_references.creation_date := old_references.creation_date;
136 new_references.created_by := old_references.created_by;
137 ELSE
138 new_references.creation_date := x_creation_date;
139 new_references.created_by := x_created_by;
140 END IF;
141
142 new_references.last_update_date := x_last_update_date;
143 new_references.last_updated_by := x_last_updated_by;
144 new_references.last_update_login := x_last_update_login;
145
146 END set_column_values;
147
148
149 FUNCTION get_pk_for_validation (
150 x_process_id IN NUMBER,
151 x_sl_number IN NUMBER
152 ) RETURN BOOLEAN AS
153 /*
154 || Created By : cdcruz
155 || Created On : 08-NOV-2000
156 || Purpose : Validates the Primary Key of the table.
157 || Known limitations, enhancements or remarks :
158 || Change History :
159 || Who When What
160 || (reverse chronological order - newest change first)
161 */
162 CURSOR cur_rowid IS
163 SELECT rowid
164 FROM igf_aw_award_t_all
165 WHERE process_id = x_process_id
166 AND sl_number = x_sl_number
167 FOR UPDATE NOWAIT;
168
169 lv_rowid cur_rowid%RowType;
170
171 BEGIN
172
173 OPEN cur_rowid;
174 FETCH cur_rowid INTO lv_rowid;
175 IF (cur_rowid%FOUND) THEN
176 CLOSE cur_rowid;
177 RETURN(TRUE);
178 ELSE
179 CLOSE cur_rowid;
180 RETURN(FALSE);
181 END IF;
182
183 END get_pk_for_validation;
184
185
186 PROCEDURE before_dml (
187 p_action IN VARCHAR2,
188 x_rowid IN VARCHAR2 DEFAULT NULL,
189 x_process_id IN NUMBER DEFAULT NULL,
190 x_sl_number IN NUMBER DEFAULT NULL,
191 x_fund_id IN NUMBER DEFAULT NULL,
192 x_base_id IN NUMBER DEFAULT NULL,
193 x_offered_amt IN NUMBER DEFAULT NULL,
194 x_accepted_amt IN NUMBER DEFAULT NULL,
195 x_paid_amt IN NUMBER DEFAULT NULL,
196 x_need_reduction_amt IN NUMBER DEFAULT NULL,
197 x_flag IN VARCHAR2 DEFAULT NULL,
198 x_temp_num_val1 IN NUMBER DEFAULT NULL,
199 x_temp_num_val2 IN NUMBER DEFAULT NULL,
200 x_temp_char_val1 IN VARCHAR2 DEFAULT NULL,
201 x_tp_cal_type IN VARCHAR2 DEFAULT NULL,
202 x_tp_sequence_number IN NUMBER DEFAULT NULL,
203 x_ld_cal_type IN VARCHAR2 DEFAULT NULL,
204 x_ld_sequence_number IN NUMBER DEFAULT NULL,
205 x_adplans_id IN NUMBER DEFAULT NULL,
206 x_app_trans_num_txt IN VARCHAR2 DEFAULT NULL,
207 x_award_id IN NUMBER DEFAULT NULL,
208 x_lock_award_flag IN VARCHAR2 DEFAULT NULL,
209 x_temp_val3_num IN NUMBER DEFAULT NULL,
210 x_temp_val4_num IN NUMBER DEFAULT NULL,
211 x_temp_char2_txt IN VARCHAR2 DEFAULT NULL,
212 x_temp_char3_txt IN VARCHAR2 DEFAULT NULL,
213 x_creation_date IN DATE DEFAULT NULL,
214 x_created_by IN NUMBER DEFAULT NULL,
215 x_last_update_date IN DATE DEFAULT NULL,
216 x_last_updated_by IN NUMBER DEFAULT NULL,
217 x_last_update_login IN NUMBER DEFAULT NULL
218 ) AS
219 /*
220 || Created By : cdcruz
221 || Created On : 08-NOV-2000
222 || Purpose : Initialises the columns, Checks Constraints, Calls the
223 || Trigger Handlers for the table, before any DML operation.
224 || Known limitations, enhancements or remarks :
225 || Change History :
226 || Who When What
227 || (reverse chronological order - newest change first)
228 || veramach 12-Oct-2004 FA 152 Added award_id,lock_award_flag
229 || veramach 03-DEC-2003 FA 131 Added app_trans_num_txt to tbh signature
230 || veramach 21-NOV-2003 FA 125 Added adplans_id to tbh signature
231 */
232 BEGIN
233 set_column_values (
234 p_action,
235 x_rowid,
236 x_process_id,
237 x_sl_number,
238 x_fund_id,
239 x_base_id,
240 x_offered_amt,
241 x_accepted_amt,
242 x_paid_amt,
243 x_need_reduction_amt,
244 x_flag,
245 x_temp_num_val1,
246 x_temp_num_val2,
247 x_temp_char_val1,
248 x_tp_cal_type,
249 x_tp_sequence_number,
250 x_ld_cal_type,
251 x_ld_sequence_number,
252 x_adplans_id,
253 x_app_trans_num_txt,
254 x_award_id,
255 x_lock_award_flag,
256 x_temp_val3_num,
257 x_temp_val4_num,
258 x_temp_char2_txt,
259 x_temp_char3_txt,
260 x_creation_date,
261 x_created_by,
262 x_last_update_date,
263 x_last_updated_by,
264 x_last_update_login
265 );
266 IF (p_action = 'INSERT') THEN
267 -- Call all the procedures related to Before Insert.
268 IF ( get_pk_for_validation(
269 new_references.process_id,
270 new_references.sl_number
271 )
272 ) THEN
273 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
274 igs_ge_msg_stack.add;
275 app_exception.raise_exception;
276 END IF;
277 ELSIF (p_action = 'VALIDATE_INSERT') THEN
278 -- Call all the procedures related to Before Insert.
279 IF ( get_pk_for_validation (
280 new_references.process_id,
281 new_references.sl_number
282 )
283 ) THEN
284 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
285 igs_ge_msg_stack.add;
286 app_exception.raise_exception;
287 END IF;
288 END IF;
289
290 END before_dml;
291
292
293 PROCEDURE insert_row (
294 x_rowid IN OUT NOCOPY VARCHAR2,
295 x_process_id IN NUMBER,
296 x_sl_number IN OUT NOCOPY NUMBER,
297 x_fund_id IN NUMBER,
298 x_base_id IN NUMBER,
299 x_offered_amt IN NUMBER,
300 x_accepted_amt IN NUMBER,
301 x_paid_amt IN NUMBER,
302 x_need_reduction_amt IN NUMBER,
303 x_flag IN VARCHAR2,
304 x_temp_num_val1 IN NUMBER,
305 x_temp_num_val2 IN NUMBER,
306 x_temp_char_val1 IN VARCHAR2,
307 x_tp_cal_type IN VARCHAR2,
308 x_tp_sequence_number IN NUMBER,
309 x_ld_cal_type IN VARCHAR2,
310 x_ld_sequence_number IN NUMBER,
311 x_mode IN VARCHAR2 DEFAULT 'R',
312 x_adplans_id IN NUMBER,
313 x_app_trans_num_txt IN VARCHAR2,
314 x_award_id IN NUMBER,
315 x_lock_award_flag IN VARCHAR2,
316 x_temp_val3_num IN NUMBER,
317 x_temp_val4_num IN NUMBER,
321 /*
318 x_temp_char2_txt IN VARCHAR2,
319 x_temp_char3_txt IN VARCHAR2
320 ) AS
322 || Created By : cdcruz
323 || Created On : 08-NOV-2000
324 || Purpose : Handles the INSERT DML logic for the table.
325 || Known limitations, enhancements or remarks :
326 || Change History :
327 || Who When What
328 || (reverse chronological order - newest change first)
329 || veramach 12-Oct-2004 FA 152 Added award_id,lock_award_flag
330 || veramach 03-DEC-2003 FA 131 Added app_trans_num_txt to tbh signature
331 || veramach 21-NOV-2003 FA 125 Added adplans_id to tbh signature
332 */
333 CURSOR c IS
334 SELECT rowid
335 FROM igf_aw_award_t_all
336 WHERE process_id = x_process_id
337 AND sl_number = x_sl_number;
338
339 x_last_update_date DATE;
340 x_last_updated_by NUMBER;
341 x_last_update_login NUMBER;
342 x_request_id NUMBER;
343 x_program_id NUMBER;
344 x_program_application_id NUMBER;
345 x_program_update_date DATE;
346 l_org_id igf_aw_award_t_all.org_id%TYPE DEFAULT igf_aw_gen.get_org_id;
347
348 BEGIN
349
350 x_last_update_date := SYSDATE;
351 IF (x_mode = 'I') THEN
352 x_last_updated_by := 1;
353 x_last_update_login := 0;
354 ELSIF (x_mode = 'R') THEN
355 x_last_updated_by := fnd_global.user_id;
356 IF (x_last_updated_by IS NULL) THEN
357 x_last_updated_by := -1;
358 END IF;
359 x_last_update_login := fnd_global.login_id;
360 IF (x_last_update_login IS NULL) THEN
361 x_last_update_login := -1;
362 END IF;
363 x_request_id := fnd_global.conc_request_id;
364 x_program_id := fnd_global.conc_program_id;
365 x_program_application_id := fnd_global.prog_appl_id;
366
367 IF (x_request_id = -1) THEN
368 x_request_id := NULL;
369 x_program_id := NULL;
370 x_program_application_id := NULL;
371 x_program_update_date := NULL;
372 ELSE
373 x_program_update_date := SYSDATE;
374 END IF;
375 ELSE
376 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
377 igs_ge_msg_stack.add;
378 app_exception.raise_exception;
379 END IF;
380
381 SELECT igf_aw_award_t_s.NEXTVAL INTO x_sl_number FROM dual ;
382 before_dml(
383 p_action => 'INSERT',
384 x_rowid => x_rowid,
385 x_process_id => x_process_id,
386 x_sl_number => x_sl_number,
387 x_fund_id => x_fund_id,
388 x_base_id => x_base_id,
389 x_offered_amt => x_offered_amt,
390 x_accepted_amt => x_accepted_amt,
391 x_paid_amt => x_paid_amt,
392 x_need_reduction_amt => x_need_reduction_amt,
393 x_flag => x_flag,
394 x_temp_num_val1 => x_temp_num_val1,
395 x_temp_num_val2 => x_temp_num_val2,
396 x_temp_char_val1 => x_temp_char_val1,
397 x_tp_cal_type => x_tp_cal_type,
398 x_tp_sequence_number => x_tp_sequence_number,
399 x_ld_cal_type => x_ld_cal_type,
400 x_ld_sequence_number => x_ld_sequence_number,
401 x_adplans_id => x_adplans_id,
402 x_app_trans_num_txt => x_app_trans_num_txt,
403 x_award_id => x_award_id,
404 x_temp_val3_num => x_temp_val3_num,
405 x_temp_val4_num => x_temp_val4_num,
406 x_temp_char2_txt => x_temp_char2_txt,
407 x_temp_char3_txt => x_temp_char3_txt,
408 x_lock_award_flag => x_lock_award_flag,
409 x_creation_date => x_last_update_date,
410 x_created_by => x_last_updated_by,
411 x_last_update_date => x_last_update_date,
412 x_last_updated_by => x_last_updated_by,
413 x_last_update_login => x_last_update_login
414 );
415 INSERT INTO igf_aw_award_t_all(
416 process_id,
417 sl_number,
418 fund_id,
419 base_id,
420 offered_amt,
421 accepted_amt,
422 paid_amt,
423 need_reduction_amt,
424 flag,
425 temp_num_val1,
426 temp_num_val2,
427 temp_char_val1,
428 tp_cal_type,
429 tp_sequence_number,
430 ld_cal_type,
431 ld_sequence_number,
432 adplans_id,
433 app_trans_num_txt,
434 award_id,
435 lock_award_flag,
436 temp_val3_num,
437 temp_val4_num,
438 temp_char2_txt,
439 temp_char3_txt,
440 creation_date,
441 created_by,
442 last_update_date,
443 last_updated_by,
447 program_application_id,
444 last_update_login,
445 request_id,
446 program_id,
448 program_update_date,
449 org_id
450 ) VALUES (
451 new_references.process_id,
452 new_references.sl_number,
453 new_references.fund_id,
454 new_references.base_id,
455 new_references.offered_amt,
456 new_references.accepted_amt,
457 new_references.paid_amt,
458 new_references.need_reduction_amt,
459 new_references.flag,
460 new_references.temp_num_val1,
461 new_references.temp_num_val2,
462 new_references.temp_char_val1,
463 new_references.tp_cal_type,
464 new_references.tp_sequence_number,
465 new_references.ld_cal_type,
466 new_references.ld_sequence_number,
467 new_references.adplans_id,
468 new_references.app_trans_num_txt,
469 new_references.award_id,
470 new_references.lock_award_flag,
471 new_references.temp_val3_num,
472 new_references.temp_val4_num,
473 new_references.temp_char2_txt,
474 new_references.temp_char3_txt,
475 x_last_update_date,
476 x_last_updated_by,
477 x_last_update_date,
478 x_last_updated_by,
479 x_last_update_login,
480 x_request_id,
481 x_program_id,
482 x_program_application_id,
483 x_program_update_date,
484 l_org_id
485 );
486
487 OPEN c;
488 FETCH c INTO x_rowid;
489 IF (c%NOTFOUND) THEN
490 CLOSE c;
491 RAISE NO_DATA_FOUND;
492 END IF;
493 CLOSE c;
494
495 END insert_row;
496
497
498 PROCEDURE lock_row (
499 x_rowid IN VARCHAR2,
500 x_process_id IN NUMBER,
501 x_sl_number IN NUMBER,
502 x_fund_id IN NUMBER,
503 x_base_id IN NUMBER,
504 x_offered_amt IN NUMBER,
505 x_accepted_amt IN NUMBER,
506 x_paid_amt IN NUMBER,
507 x_need_reduction_amt IN NUMBER,
508 x_flag IN VARCHAR2,
509 x_temp_num_val1 IN NUMBER,
510 x_temp_num_val2 IN NUMBER,
511 x_temp_char_val1 IN VARCHAR2,
512 x_tp_cal_type IN VARCHAR2,
513 x_tp_sequence_number IN NUMBER,
514 x_ld_cal_type IN VARCHAR2,
515 x_ld_sequence_number IN NUMBER,
516 x_adplans_id IN NUMBER,
517 x_app_trans_num_txt IN VARCHAR2,
518 x_award_id IN NUMBER,
519 x_lock_award_flag IN VARCHAR2,
520 x_temp_val3_num IN NUMBER,
521 x_temp_val4_num IN NUMBER,
522 x_temp_char2_txt IN VARCHAR2,
523 x_temp_char3_txt IN VARCHAR2
524 ) AS
525 /*
526 || Created By : cdcruz
527 || Created On : 08-NOV-2000
528 || Purpose : Handles the LOCK mechanism for the table.
529 || Known limitations, enhancements or remarks :
530 || Change History :
531 || Who When What
532 || (reverse chronological order - newest change first)
533 || veramach 12-Oct-2004 FA 152 Added award_id,lock_award_flag
534 || veramach 03-DEC-2003 FA 131 Added app_trans_num_txt to tbh signature
535 || veramach 21-NOV-2003 FA 125 Added adplans_id to tbh signature
536 */
537 CURSOR c1 IS
538 SELECT
539 fund_id,
540 base_id,
541 offered_amt,
542 accepted_amt,
543 paid_amt,
544 need_reduction_amt,
545 flag,
546 temp_num_val1,
547 temp_num_val2,
548 temp_char_val1,
549 tp_cal_type,
550 tp_sequence_number,
551 ld_cal_type,
552 ld_sequence_number,
553 adplans_id,
554 app_trans_num_txt,
555 award_id,
556 lock_award_flag,
557 temp_val3_num,
558 temp_val4_num,
559 temp_char2_txt,
560 temp_char3_txt
561 FROM igf_aw_award_t_all
562 WHERE rowid = x_rowid
563 FOR UPDATE NOWAIT;
564
565 tlinfo c1%ROWTYPE;
566 BEGIN
567
568 OPEN c1;
569 FETCH c1 INTO tlinfo;
570 IF (c1%notfound) THEN
571 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
572 igs_ge_msg_stack.add;
573 CLOSE c1;
574 app_exception.raise_exception;
575 RETURN;
576 END IF;
577 CLOSE c1;
578
579 IF (
580 ((tlinfo.fund_id = x_fund_id) OR ((tlinfo.fund_id IS NULL) AND (X_fund_id IS NULL)))
581 AND ((tlinfo.base_id = x_base_id) OR ((tlinfo.base_id IS NULL) AND (X_base_id IS NULL)))
582 AND ((tlinfo.offered_amt = x_offered_amt) OR ((tlinfo.offered_amt IS NULL) AND (X_offered_amt IS NULL)))
583 AND ((tlinfo.accepted_amt = x_accepted_amt) OR ((tlinfo.accepted_amt IS NULL) AND (X_accepted_amt IS NULL)))
587 AND ((tlinfo.temp_num_val1 = x_temp_num_val1) OR ((tlinfo.temp_num_val1 IS NULL) AND (X_temp_num_val1 IS NULL)))
584 AND ((tlinfo.paid_amt = x_paid_amt) OR ((tlinfo.paid_amt IS NULL) AND (X_paid_amt IS NULL)))
585 AND ((tlinfo.need_reduction_amt = x_need_reduction_amt) OR ((tlinfo.need_reduction_amt IS NULL) AND (X_need_reduction_amt IS NULL)))
586 AND ((tlinfo.flag = x_flag) OR ((tlinfo.flag IS NULL) AND (X_flag IS NULL)))
588 AND ((tlinfo.temp_num_val2 = x_temp_num_val2) OR ((tlinfo.temp_num_val2 IS NULL) AND (X_temp_num_val2 IS NULL)))
589 AND ((tlinfo.temp_char_val1 = x_temp_char_val1) OR ((tlinfo.temp_char_val1 IS NULL) AND (X_temp_char_val1 IS NULL)))
590 AND ((tlinfo.tp_cal_type = x_tp_cal_type) OR ((tlinfo.tp_cal_type IS NULL) AND (X_tp_cal_type IS NULL)))
591 AND ((tlinfo.tp_sequence_number = x_tp_sequence_number) OR ((tlinfo.tp_sequence_number IS NULL) AND (X_tp_sequence_number IS NULL)))
592 AND ((tlinfo.ld_cal_type = x_ld_cal_type) OR ((tlinfo.ld_cal_type IS NULL) AND (X_ld_cal_type IS NULL)))
593 AND ((tlinfo.ld_sequence_number = x_ld_sequence_number) OR ((tlinfo.ld_sequence_number IS NULL) AND (X_ld_sequence_number IS NULL)))
594 AND ((tlinfo.adplans_id = x_adplans_id) OR ((tlinfo.adplans_id IS NULL) AND (x_adplans_id IS NULL)))
595 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)))
596 AND ((tlinfo.award_id = x_award_id) OR ((tlinfo.award_id IS NULL) AND (x_award_id IS NULL)))
597 AND ((tlinfo.lock_award_flag = x_lock_award_flag) OR ((tlinfo.lock_award_flag IS NULL) AND (x_lock_award_flag IS NULL)))
598 AND ((tlinfo.temp_val3_num = x_temp_val3_num ) OR ((tlinfo.temp_val3_num IS NULL) AND (x_temp_val3_num IS NULL)))
599 AND ((tlinfo.temp_val4_num = x_temp_val4_num ) OR ((tlinfo.temp_val4_num IS NULL) AND (x_temp_val4_num IS NULL)))
600 AND ((tlinfo.temp_char2_txt = x_temp_char2_txt) OR ((tlinfo.temp_char2_txt IS NULL) AND (x_temp_char2_txt IS NULL)))
601 AND ((tlinfo.temp_char3_txt = x_temp_char3_txt) OR ((tlinfo.temp_char3_txt IS NULL) AND (x_temp_char3_txt IS NULL)))
602 ) THEN
603 NULL;
604 ELSE
605 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
606 igs_ge_msg_stack.add;
607 app_exception.raise_exception;
608 END IF;
609
610 RETURN;
611
612 END lock_row;
613
614
615 PROCEDURE update_row (
616 x_rowid IN VARCHAR2,
617 x_process_id IN NUMBER,
618 x_sl_number IN NUMBER,
619 x_fund_id IN NUMBER,
620 x_base_id IN NUMBER,
621 x_offered_amt IN NUMBER,
622 x_accepted_amt IN NUMBER,
623 x_paid_amt IN NUMBER,
624 x_need_reduction_amt IN NUMBER,
625 x_flag IN VARCHAR2,
626 x_temp_num_val1 IN NUMBER,
627 x_temp_num_val2 IN NUMBER,
628 x_temp_char_val1 IN VARCHAR2,
629 x_tp_cal_type IN VARCHAR2,
630 x_tp_sequence_number IN NUMBER,
631 x_ld_cal_type IN VARCHAR2,
632 x_ld_sequence_number IN NUMBER,
633 x_mode IN VARCHAR2 DEFAULT 'R',
634 x_adplans_id IN NUMBER,
635 x_app_trans_num_txt IN VARCHAR2,
636 x_award_id IN NUMBER,
637 x_lock_award_flag IN VARCHAR2,
638 x_temp_val3_num IN NUMBER,
639 x_temp_val4_num IN NUMBER,
640 x_temp_char2_txt IN VARCHAR2,
641 x_temp_char3_txt IN VARCHAR2
642 ) AS
643 /*
644 || Created By : cdcruz
645 || Created On : 08-NOV-2000
646 || Purpose : Handles the UPDATE DML logic for the table.
647 || Known limitations, enhancements or remarks :
648 || Change History :
649 || Who When What
650 || (reverse chronological order - newest change first)
651 || veramach 12-Oct-2004 FA 152 Added award_id,lock_award_flag
652 || veramach 03-DEC-2003 FA 131 Added app_trans_num_txt to tbh signature
653 || veramach 21-NOV-2003 FA 125 Added adplans_id to tbh signature
654 */
655 x_last_update_date DATE ;
656 x_last_updated_by NUMBER;
657 x_last_update_login NUMBER;
658 x_request_id NUMBER;
659 x_program_id NUMBER;
660 x_program_application_id NUMBER;
661 x_program_update_date DATE;
662
663 BEGIN
664
665 x_last_update_date := SYSDATE;
666 IF (X_MODE = 'I') THEN
667 x_last_updated_by := 1;
668 x_last_update_login := 0;
669 ELSIF (x_mode = 'R') THEN
670 x_last_updated_by := fnd_global.user_id;
671 IF x_last_updated_by IS NULL THEN
672 x_last_updated_by := -1;
673 END IF;
674 x_last_update_login := fnd_global.login_id;
675 IF (x_last_update_login IS NULL) THEN
676 x_last_update_login := -1;
677 END IF;
678 ELSE
679 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
680 igs_ge_msg_stack.add;
681 app_exception.raise_exception;
682 END IF;
683 before_dml(
684 p_action => 'UPDATE',
685 x_rowid => x_rowid,
686 x_process_id => x_process_id,
687 x_sl_number => x_sl_number,
688 x_fund_id => x_fund_id,
689 x_base_id => x_base_id,
690 x_offered_amt => x_offered_amt,
691 x_accepted_amt => x_accepted_amt,
692 x_paid_amt => x_paid_amt,
696 x_temp_num_val2 => x_temp_num_val2,
693 x_need_reduction_amt => x_need_reduction_amt,
694 x_flag => x_flag,
695 x_temp_num_val1 => x_temp_num_val1,
697 x_temp_char_val1 => x_temp_char_val1,
698 x_tp_cal_type => x_tp_cal_type,
699 x_tp_sequence_number => x_tp_sequence_number,
700 x_ld_cal_type => x_ld_cal_type,
701 x_ld_sequence_number => x_ld_sequence_number,
702 x_adplans_id => x_adplans_id,
703 x_app_trans_num_txt => x_app_trans_num_txt,
704 x_award_id => x_award_id,
705 x_lock_award_flag => x_lock_award_flag,
706 x_temp_val3_num => x_temp_val3_num,
707 x_temp_val4_num => x_temp_val4_num,
708 x_temp_char2_txt => x_temp_char2_txt,
709 x_temp_char3_txt => x_temp_char3_txt,
710 x_creation_date => x_last_update_date,
711 x_created_by => x_last_updated_by,
712 x_last_update_date => x_last_update_date,
713 x_last_updated_by => x_last_updated_by,
714 x_last_update_login => x_last_update_login
715 );
716
717 IF (x_mode = 'R') THEN
718 x_request_id := fnd_global.conc_request_id;
719 x_program_id := fnd_global.conc_program_id;
720 x_program_application_id := fnd_global.prog_appl_id;
721 IF (x_request_id = -1) THEN
722 x_request_id := old_references.request_id;
723 x_program_id := old_references.program_id;
724 x_program_application_id := old_references.program_application_id;
725 x_program_update_date := old_references.program_update_date;
726 ELSE
727 x_program_update_date := SYSDATE;
728 END IF;
729 END IF;
730
731 UPDATE igf_aw_award_t_all
732 SET
733 fund_id = new_references.fund_id,
734 base_id = new_references.base_id,
735 offered_amt = new_references.offered_amt,
736 accepted_amt = new_references.accepted_amt,
737 paid_amt = new_references.paid_amt,
738 need_reduction_amt = new_references.need_reduction_amt,
739 flag = new_references.flag,
740 temp_num_val1 = new_references.temp_num_val1,
741 temp_num_val2 = new_references.temp_num_val2,
742 temp_char_val1 = new_references.temp_char_val1,
743 tp_cal_type = new_references.tp_cal_type,
744 tp_sequence_number = new_references.tp_sequence_number,
745 ld_cal_type = new_references.ld_cal_type,
746 ld_sequence_number = new_references.ld_sequence_number,
747 adplans_id = new_references.adplans_id,
748 app_trans_num_txt = new_references.app_trans_num_txt,
749 award_id = new_references.award_id,
750 lock_award_flag = new_references.lock_award_flag,
751 temp_val3_num = new_references.temp_val3_num,
752 temp_val4_num = new_references.temp_val4_num,
753 temp_char2_txt = new_references.temp_char2_txt,
754 temp_char3_txt = new_references.temp_char3_txt,
755 last_update_date = x_last_update_date,
756 last_updated_by = x_last_updated_by,
757 last_update_login = x_last_update_login ,
758 request_id = x_request_id,
759 program_id = x_program_id,
760 program_application_id = x_program_application_id,
761 program_update_date = x_program_update_date
762 WHERE rowid = x_rowid;
763
764 IF (SQL%NOTFOUND) THEN
765 RAISE NO_DATA_FOUND;
766 END IF;
767
768 END update_row;
769
770
771 PROCEDURE add_row (
772 x_rowid IN OUT NOCOPY VARCHAR2,
773 x_process_id IN NUMBER,
774 x_sl_number IN OUT NOCOPY NUMBER,
775 x_fund_id IN NUMBER,
776 x_base_id IN NUMBER,
777 x_offered_amt IN NUMBER,
778 x_accepted_amt IN NUMBER,
779 x_paid_amt IN NUMBER,
780 x_need_reduction_amt IN NUMBER,
781 x_flag IN VARCHAR2,
782 x_temp_num_val1 IN NUMBER,
783 x_temp_num_val2 IN NUMBER,
784 x_temp_char_val1 IN VARCHAR2,
785 x_tp_cal_type IN VARCHAR2,
786 x_tp_sequence_number IN NUMBER,
787 x_ld_cal_type IN VARCHAR2,
788 x_ld_sequence_number IN NUMBER,
789 x_mode IN VARCHAR2 DEFAULT 'R',
790 x_adplans_id IN NUMBER,
791 x_app_trans_num_txt IN VARCHAR2,
792 x_award_id IN NUMBER,
793 x_lock_award_flag IN VARCHAR2,
794 x_temp_val3_num IN NUMBER,
795 x_temp_val4_num IN NUMBER,
796 x_temp_char2_txt IN VARCHAR2,
797 x_temp_char3_txt IN VARCHAR2
798 ) AS
799 /*
800 || Created By : cdcruz
801 || Created On : 08-NOV-2000
802 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
803 || Known limitations, enhancements or remarks :
804 || Change History :
805 || Who When What
806 || (reverse chronological order - newest change first)
807 || veramach 12-Oct-2004 FA 152 Added award_id,lock_award_flag
808 || veramach 03-DEC-2003 FA 131 Added app_trans_num_txt to tbh signature
809 || veramach 21-NOV-2003 FA 125 Added adplans_id to tbh signature
810 */
811 CURSOR c1 IS
812 SELECT rowid
813 FROM igf_aw_award_t_all
814 WHERE process_id = x_process_id
815 AND sl_number = x_sl_number;
816
817 BEGIN
818
819 OPEN c1;
820 FETCH c1 INTO x_rowid;
821 IF (c1%NOTFOUND) THEN
822 CLOSE c1;
823
824 insert_row (
825 x_rowid,
826 x_process_id,
827 x_sl_number,
828 x_fund_id,
829 x_base_id,
830 x_offered_amt,
831 x_accepted_amt,
832 x_paid_amt,
833 x_need_reduction_amt,
834 x_flag,
835 x_temp_num_val1,
836 x_temp_num_val2,
837 x_temp_char_val1,
838 x_tp_cal_type,
839 x_tp_sequence_number,
840 x_ld_cal_type,
841 x_ld_sequence_number,
842 x_mode,
843 x_adplans_id,
844 x_app_trans_num_txt,
845 x_award_id,
846 x_lock_award_flag,
847 x_temp_val3_num,
848 x_temp_val4_num,
849 x_temp_char2_txt,
850 x_temp_char3_txt
851 );
852 RETURN;
853 END IF;
854 CLOSE c1;
855
856 update_row (
857 x_rowid,
858 x_process_id,
859 x_sl_number,
860 x_fund_id,
861 x_base_id,
862 x_offered_amt,
863 x_accepted_amt,
864 x_paid_amt,
865 x_need_reduction_amt,
866 x_flag,
867 x_temp_num_val1,
868 x_temp_num_val2,
869 x_temp_char_val1,
870 x_tp_cal_type,
871 x_tp_sequence_number,
872 x_ld_cal_type,
873 x_ld_sequence_number,
874 x_mode,
875 x_adplans_id,
876 x_app_trans_num_txt,
877 x_award_id,
878 x_lock_award_flag,
879 x_temp_val3_num,
880 x_temp_val4_num,
881 x_temp_char2_txt,
882 x_temp_char3_txt
883 );
884
885
886 END add_row;
887
888
889 PROCEDURE delete_row (
890 x_rowid IN VARCHAR2
891 ) AS
892 /*
893 || Created By : cdcruz
894 || Created On : 08-NOV-2000
895 || Purpose : Handles the DELETE DML logic for the table.
896 || Known limitations, enhancements or remarks :
897 || Change History :
898 || Who When What
899 || (reverse chronological order - newest change first)
900 */
901 BEGIN
902
903 before_dml (
904 p_action => 'DELETE',
905 x_rowid => x_rowid
906 );
907
908 DELETE FROM igf_aw_award_t_all
909 WHERE rowid = x_rowid;
910
911 IF (SQL%NOTFOUND) THEN
912 RAISE NO_DATA_FOUND;
913 END IF;
914
915 END delete_row;
916
917
918 END igf_aw_award_t_pkg;