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