[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_AWD_HIST_PKG
Source
1 PACKAGE BODY igf_aw_awd_hist_pkg AS
2 /* $Header: IGFWI69B.pls 120.0 2005/06/02 15:52:47 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_aw_awd_hist%ROWTYPE;
6 new_references igf_aw_awd_hist%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_awdh_id IN NUMBER,
12 x_award_id IN NUMBER,
13 x_tran_date IN DATE,
14 x_operation_txt IN VARCHAR2,
15 x_offered_amt_num IN NUMBER,
16 x_off_adj_num IN NUMBER,
17 x_accepted_amt_num IN NUMBER,
18 x_acc_adj_num IN NUMBER,
19 x_paid_amt_num IN NUMBER,
20 x_paid_adj_num IN NUMBER,
21 x_creation_date IN DATE,
22 x_created_by IN NUMBER,
23 x_last_update_date IN DATE,
24 x_last_updated_by IN NUMBER,
25 x_last_update_login IN NUMBER
26 ) AS
27 /*
28 || Created By : cdcruz
29 || Created On : 16-NOV-2004
30 || Purpose : Initialises the Old and New references for the columns of the table.
31 || Known limitations, enhancements or remarks :
32 || Change History :
33 || Who When What
34 || (reverse chronological order - newest change first)
35 */
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM igf_aw_awd_hist
40 WHERE rowid = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 OPEN cur_old_ref_values;
49 FETCH cur_old_ref_values INTO old_references;
50 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
51 CLOSE cur_old_ref_values;
52 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
53 igs_ge_msg_stack.add;
54 app_exception.raise_exception;
55 RETURN;
56 END IF;
57 CLOSE cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.awdh_id := x_awdh_id;
61 new_references.award_id := x_award_id;
62 new_references.tran_date := x_tran_date;
63 new_references.operation_txt := x_operation_txt;
64 new_references.offered_amt_num := x_offered_amt_num;
65 new_references.off_adj_num := x_off_adj_num;
66 new_references.accepted_amt_num := x_accepted_amt_num;
67 new_references.acc_adj_num := x_acc_adj_num;
68 new_references.paid_amt_num := x_paid_amt_num;
69 new_references.paid_adj_num := x_paid_adj_num;
70
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78
79 new_references.last_update_date := x_last_update_date;
80 new_references.last_updated_by := x_last_updated_by;
81 new_references.last_update_login := x_last_update_login;
82
83 END set_column_values;
84
85
86 FUNCTION get_pk_for_validation (
87 x_awdh_id IN NUMBER
88 ) RETURN BOOLEAN AS
89 /*
90 || Created By : cdcruz
91 || Created On : 16-NOV-2004
92 || Purpose : Validates the Primary Key of the table.
93 || Known limitations, enhancements or remarks :
94 || Change History :
95 || Who When What
96 || (reverse chronological order - newest change first)
97 */
98 CURSOR cur_rowid IS
99 SELECT rowid
100 FROM igf_aw_awd_hist
101 WHERE awdh_id = x_awdh_id
102 FOR UPDATE NOWAIT;
103
104 lv_rowid cur_rowid%RowType;
105
106 BEGIN
107
108 OPEN cur_rowid;
109 FETCH cur_rowid INTO lv_rowid;
110 IF (cur_rowid%FOUND) THEN
111 CLOSE cur_rowid;
112 RETURN(TRUE);
113 ELSE
114 CLOSE cur_rowid;
115 RETURN(FALSE);
116 END IF;
117
118 END get_pk_for_validation;
119
120
121 PROCEDURE before_dml (
122 p_action IN VARCHAR2,
123 x_rowid IN VARCHAR2,
124 x_awdh_id IN NUMBER,
125 x_award_id IN NUMBER,
126 x_tran_date IN DATE,
127 x_operation_txt IN VARCHAR2,
128 x_offered_amt_num IN NUMBER,
129 x_off_adj_num IN NUMBER,
130 x_accepted_amt_num IN NUMBER,
131 x_acc_adj_num IN NUMBER,
132 x_paid_amt_num IN NUMBER,
133 x_paid_adj_num IN NUMBER,
134 x_creation_date IN DATE,
135 x_created_by IN NUMBER,
136 x_last_update_date IN DATE,
137 x_last_updated_by IN NUMBER,
138 x_last_update_login IN NUMBER
139 ) AS
140 /*
141 || Created By : cdcruz
142 || Created On : 16-NOV-2004
143 || Purpose : Initialises the columns, Checks Constraints, Calls the
144 || Trigger Handlers for the table, before any DML operation.
145 || Known limitations, enhancements or remarks :
146 || Change History :
147 || Who When What
148 || (reverse chronological order - newest change first)
149 */
150 BEGIN
151
152 set_column_values (
153 p_action,
154 x_rowid,
155 x_awdh_id,
156 x_award_id,
157 x_tran_date,
158 x_operation_txt,
159 x_offered_amt_num,
160 x_off_adj_num,
161 x_accepted_amt_num,
162 x_acc_adj_num,
163 x_paid_amt_num,
164 x_paid_adj_num,
165 x_creation_date,
166 x_created_by,
167 x_last_update_date,
168 x_last_updated_by,
169 x_last_update_login
170 );
171
172 IF (p_action = 'INSERT') THEN
173 -- Call all the procedures related to Before Insert.
174 IF ( get_pk_for_validation(
175 new_references.awdh_id
176 )
177 ) THEN
178 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
179 igs_ge_msg_stack.add;
180 app_exception.raise_exception;
181 END IF;
182 ELSIF (p_action = 'VALIDATE_INSERT') THEN
183 -- Call all the procedures related to Before Insert.
184 IF ( get_pk_for_validation (
185 new_references.awdh_id
186 )
187 ) THEN
188 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
189 igs_ge_msg_stack.add;
190 app_exception.raise_exception;
191 END IF;
192 END IF;
193
194 END before_dml;
195
196
197 PROCEDURE insert_row (
198 x_rowid IN OUT NOCOPY VARCHAR2,
199 x_awdh_id IN OUT NOCOPY NUMBER,
200 x_award_id IN NUMBER,
201 x_tran_date IN DATE,
202 x_operation_txt IN VARCHAR2,
203 x_offered_amt_num IN NUMBER,
204 x_off_adj_num IN NUMBER,
205 x_accepted_amt_num IN NUMBER,
206 x_acc_adj_num IN NUMBER,
207 x_paid_amt_num IN NUMBER,
208 x_paid_adj_num IN NUMBER,
209 x_mode IN VARCHAR2
210 ) AS
211 /*
212 || Created By : cdcruz
213 || Created On : 16-NOV-2004
214 || Purpose : Handles the INSERT DML logic for the table.
215 || Known limitations, enhancements or remarks :
216 || Change History :
217 || Who When What
218 || (reverse chronological order - newest change first)
219 */
220
221 x_last_update_date DATE;
222 x_last_updated_by NUMBER;
223 x_last_update_login NUMBER;
224 x_request_id NUMBER;
225 x_program_id NUMBER;
226 x_program_application_id NUMBER;
227 x_program_update_date DATE;
228
229 BEGIN
230
231 x_last_update_date := SYSDATE;
232 IF (x_mode = 'I') THEN
233 x_last_updated_by := 1;
234 x_last_update_login := 0;
235 ELSIF (x_mode = 'R') THEN
236 x_last_updated_by := fnd_global.user_id;
237 IF (x_last_updated_by IS NULL) THEN
238 x_last_updated_by := -1;
239 END IF;
240 x_last_update_login := fnd_global.login_id;
241 IF (x_last_update_login IS NULL) THEN
242 x_last_update_login := -1;
243 END IF;
244 x_request_id := fnd_global.conc_request_id;
245 x_program_id := fnd_global.conc_program_id;
246 x_program_application_id := fnd_global.prog_appl_id;
247
248 IF (x_request_id = -1) THEN
249 x_request_id := NULL;
250 x_program_id := NULL;
251 x_program_application_id := NULL;
252 x_program_update_date := NULL;
253 ELSE
254 x_program_update_date := SYSDATE;
255 END IF;
256 ELSE
257 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
258 fnd_message.set_token ('ROUTINE', 'IGF_AW_AWD_HIST_PKG.INSERT_ROW');
259 igs_ge_msg_stack.add;
260 app_exception.raise_exception;
261 END IF;
262
263 x_awdh_id := NULL;
264
265 before_dml(
266 p_action => 'INSERT',
267 x_rowid => x_rowid,
268 x_awdh_id => x_awdh_id,
269 x_award_id => x_award_id,
270 x_tran_date => x_tran_date,
271 x_operation_txt => x_operation_txt,
272 x_offered_amt_num => x_offered_amt_num,
273 x_off_adj_num => x_off_adj_num,
274 x_accepted_amt_num => x_accepted_amt_num,
275 x_acc_adj_num => x_acc_adj_num,
276 x_paid_amt_num => x_paid_amt_num,
277 x_paid_adj_num => x_paid_adj_num,
278 x_creation_date => x_last_update_date,
279 x_created_by => x_last_updated_by,
280 x_last_update_date => x_last_update_date,
281 x_last_updated_by => x_last_updated_by,
282 x_last_update_login => x_last_update_login
283 );
284
285 INSERT INTO igf_aw_awd_hist (
286 awdh_id,
287 award_id,
288 tran_date,
289 operation_txt,
290 offered_amt_num,
291 off_adj_num,
292 accepted_amt_num,
293 acc_adj_num,
294 paid_amt_num,
295 paid_adj_num,
296 creation_date,
297 created_by,
298 last_update_date,
299 last_updated_by,
300 last_update_login,
301 request_id,
302 program_id,
303 program_application_id,
304 program_update_date
305 ) VALUES (
306 igf_aw_awd_hist_s.NEXTVAL,
307 new_references.award_id,
308 new_references.tran_date,
309 new_references.operation_txt,
310 new_references.offered_amt_num,
311 new_references.off_adj_num,
312 new_references.accepted_amt_num,
313 new_references.acc_adj_num,
314 new_references.paid_amt_num,
315 new_references.paid_adj_num,
316 x_last_update_date,
317 x_last_updated_by,
318 x_last_update_date,
319 x_last_updated_by,
320 x_last_update_login ,
321 x_request_id,
322 x_program_id,
323 x_program_application_id,
324 x_program_update_date
325 ) RETURNING ROWID, awdh_id INTO x_rowid, x_awdh_id;
326
327 END insert_row;
328
329
330 PROCEDURE lock_row (
331 x_rowid IN VARCHAR2,
332 x_awdh_id IN NUMBER,
333 x_award_id IN NUMBER,
334 x_tran_date IN DATE,
335 x_operation_txt IN VARCHAR2,
336 x_offered_amt_num IN NUMBER,
337 x_off_adj_num IN NUMBER,
338 x_accepted_amt_num IN NUMBER,
339 x_acc_adj_num IN NUMBER,
340 x_paid_amt_num IN NUMBER,
341 x_paid_adj_num IN NUMBER
342 ) AS
343 /*
344 || Created By : cdcruz
345 || Created On : 16-NOV-2004
346 || Purpose : Handles the LOCK mechanism for the table.
347 || Known limitations, enhancements or remarks :
348 || Change History :
349 || Who When What
350 || (reverse chronological order - newest change first)
351 */
352 CURSOR c1 IS
353 SELECT
354 award_id,
355 tran_date,
356 operation_txt,
357 offered_amt_num,
358 off_adj_num,
359 accepted_amt_num,
360 acc_adj_num,
361 paid_amt_num,
362 paid_adj_num
363 FROM igf_aw_awd_hist
364 WHERE rowid = x_rowid
365 FOR UPDATE NOWAIT;
366
367 tlinfo c1%ROWTYPE;
368
369 BEGIN
370
371 OPEN c1;
372 FETCH c1 INTO tlinfo;
373 IF (c1%notfound) THEN
374 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
375 igs_ge_msg_stack.add;
376 CLOSE c1;
377 app_exception.raise_exception;
378 RETURN;
379 END IF;
380 CLOSE c1;
381
382 IF (
383 ((tlinfo.award_id = x_award_id) OR ((tlinfo.award_id IS NULL) AND (X_award_id IS NULL)))
384 AND ((tlinfo.tran_date = x_tran_date) OR ((tlinfo.tran_date IS NULL) AND (X_tran_date IS NULL)))
385 AND ((tlinfo.operation_txt = x_operation_txt) OR ((tlinfo.operation_txt IS NULL) AND (X_operation_txt IS NULL)))
386 AND ((tlinfo.offered_amt_num = x_offered_amt_num) OR ((tlinfo.offered_amt_num IS NULL) AND (X_offered_amt_num IS NULL)))
387 AND ((tlinfo.off_adj_num = x_off_adj_num) OR ((tlinfo.off_adj_num IS NULL) AND (X_off_adj_num IS NULL)))
388 AND ((tlinfo.accepted_amt_num = x_accepted_amt_num) OR ((tlinfo.accepted_amt_num IS NULL) AND (X_accepted_amt_num IS NULL)))
389 AND ((tlinfo.acc_adj_num = x_acc_adj_num) OR ((tlinfo.acc_adj_num IS NULL) AND (X_acc_adj_num IS NULL)))
390 AND ((tlinfo.paid_amt_num = x_paid_amt_num) OR ((tlinfo.paid_amt_num IS NULL) AND (X_paid_amt_num IS NULL)))
391 AND ((tlinfo.paid_adj_num = x_paid_adj_num) OR ((tlinfo.paid_adj_num IS NULL) AND (X_paid_adj_num IS NULL)))
392 ) THEN
393 NULL;
394 ELSE
395 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
396 igs_ge_msg_stack.add;
397 app_exception.raise_exception;
398 END IF;
399
400 RETURN;
401
402 END lock_row;
403
404
405 PROCEDURE update_row (
406 x_rowid IN VARCHAR2,
407 x_awdh_id IN NUMBER,
408 x_award_id IN NUMBER,
409 x_tran_date IN DATE,
410 x_operation_txt IN VARCHAR2,
411 x_offered_amt_num IN NUMBER,
415 x_paid_amt_num IN NUMBER,
412 x_off_adj_num IN NUMBER,
413 x_accepted_amt_num IN NUMBER,
414 x_acc_adj_num IN NUMBER,
416 x_paid_adj_num IN NUMBER,
417 x_mode IN VARCHAR2
418 ) AS
419 /*
420 || Created By : cdcruz
421 || Created On : 16-NOV-2004
422 || Purpose : Handles the UPDATE DML logic for the table.
423 || Known limitations, enhancements or remarks :
424 || Change History :
425 || Who When What
426 || (reverse chronological order - newest change first)
427 */
428 x_last_update_date DATE ;
429 x_last_updated_by NUMBER;
430 x_last_update_login NUMBER;
431 x_request_id NUMBER;
432 x_program_id NUMBER;
433 x_program_application_id NUMBER;
434 x_program_update_date DATE;
435
436 BEGIN
437
438 x_last_update_date := SYSDATE;
439 IF (X_MODE = 'I') THEN
440 x_last_updated_by := 1;
441 x_last_update_login := 0;
442 ELSIF (x_mode = 'R') THEN
443 x_last_updated_by := fnd_global.user_id;
444 IF x_last_updated_by IS NULL THEN
445 x_last_updated_by := -1;
446 END IF;
447 x_last_update_login := fnd_global.login_id;
448 IF (x_last_update_login IS NULL) THEN
449 x_last_update_login := -1;
450 END IF;
451 ELSE
452 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
453 fnd_message.set_token ('ROUTINE', 'IGF_AW_AWD_HIST_PKG.UPDATE_ROW');
454 igs_ge_msg_stack.add;
455 app_exception.raise_exception;
456 END IF;
457
458 before_dml(
459 p_action => 'UPDATE',
460 x_rowid => x_rowid,
461 x_awdh_id => x_awdh_id,
462 x_award_id => x_award_id,
463 x_tran_date => x_tran_date,
464 x_operation_txt => x_operation_txt,
465 x_offered_amt_num => x_offered_amt_num,
466 x_off_adj_num => x_off_adj_num,
467 x_accepted_amt_num => x_accepted_amt_num,
468 x_acc_adj_num => x_acc_adj_num,
469 x_paid_amt_num => x_paid_amt_num,
470 x_paid_adj_num => x_paid_adj_num,
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,
475 x_last_update_login => x_last_update_login
476 );
477
478 IF (x_mode = 'R') THEN
479 x_request_id := fnd_global.conc_request_id;
480 x_program_id := fnd_global.conc_program_id;
481 x_program_application_id := fnd_global.prog_appl_id;
482 IF (x_request_id = -1) THEN
483 x_request_id := old_references.request_id;
484 x_program_id := old_references.program_id;
485 x_program_application_id := old_references.program_application_id;
486 x_program_update_date := old_references.program_update_date;
487 ELSE
488 x_program_update_date := SYSDATE;
489 END IF;
490 END IF;
491
492 UPDATE igf_aw_awd_hist
493 SET
494 award_id = new_references.award_id,
495 tran_date = new_references.tran_date,
496 operation_txt = new_references.operation_txt,
497 offered_amt_num = new_references.offered_amt_num,
498 off_adj_num = new_references.off_adj_num,
499 accepted_amt_num = new_references.accepted_amt_num,
500 acc_adj_num = new_references.acc_adj_num,
501 paid_amt_num = new_references.paid_amt_num,
502 paid_adj_num = new_references.paid_adj_num,
503 last_update_date = x_last_update_date,
504 last_updated_by = x_last_updated_by,
505 last_update_login = x_last_update_login ,
506 request_id = x_request_id,
507 program_id = x_program_id,
508 program_application_id = x_program_application_id,
509 program_update_date = x_program_update_date
510 WHERE rowid = x_rowid;
511
512 IF (SQL%NOTFOUND) THEN
513 RAISE NO_DATA_FOUND;
514 END IF;
515
516 END update_row;
517
518
519 PROCEDURE add_row (
520 x_rowid IN OUT NOCOPY VARCHAR2,
521 x_awdh_id IN OUT NOCOPY NUMBER,
522 x_award_id IN NUMBER,
523 x_tran_date IN DATE,
524 x_operation_txt IN VARCHAR2,
525 x_offered_amt_num IN NUMBER,
526 x_off_adj_num IN NUMBER,
527 x_accepted_amt_num IN NUMBER,
528 x_acc_adj_num IN NUMBER,
529 x_paid_amt_num IN NUMBER,
530 x_paid_adj_num IN NUMBER,
531 x_mode IN VARCHAR2
532 ) AS
533 /*
534 || Created By : cdcruz
535 || Created On : 16-NOV-2004
536 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
537 || Known limitations, enhancements or remarks :
541 */
538 || Change History :
539 || Who When What
540 || (reverse chronological order - newest change first)
542 CURSOR c1 IS
543 SELECT rowid
544 FROM igf_aw_awd_hist
545 WHERE awdh_id = x_awdh_id;
546
547 BEGIN
548
549 OPEN c1;
550 FETCH c1 INTO x_rowid;
551 IF (c1%NOTFOUND) THEN
552 CLOSE c1;
553
554 insert_row (
555 x_rowid,
556 x_awdh_id,
557 x_award_id,
558 x_tran_date,
559 x_operation_txt,
560 x_offered_amt_num,
561 x_off_adj_num,
562 x_accepted_amt_num,
563 x_acc_adj_num,
564 x_paid_amt_num,
565 x_paid_adj_num,
566 x_mode
567 );
568 RETURN;
569 END IF;
570 CLOSE c1;
571
572 update_row (
573 x_rowid,
574 x_awdh_id,
575 x_award_id,
576 x_tran_date,
577 x_operation_txt,
578 x_offered_amt_num,
579 x_off_adj_num,
580 x_accepted_amt_num,
581 x_acc_adj_num,
582 x_paid_amt_num,
583 x_paid_adj_num,
584 x_mode
585 );
586
587 END add_row;
588
589
590 PROCEDURE delete_row (
591 x_rowid IN VARCHAR2
592 ) AS
593 /*
594 || Created By : cdcruz
595 || Created On : 16-NOV-2004
596 || Purpose : Handles the DELETE DML logic for the table.
597 || Known limitations, enhancements or remarks :
598 || Change History :
599 || Who When What
600 || (reverse chronological order - newest change first)
601 */
602 BEGIN
603
604 before_dml (
605 p_action => 'DELETE',
606 x_rowid => x_rowid
607 );
608
609 DELETE FROM igf_aw_awd_hist
610 WHERE rowid = x_rowid;
611
612 IF (SQL%NOTFOUND) THEN
613 RAISE NO_DATA_FOUND;
614 END IF;
615
616 END delete_row;
617
618
619 END igf_aw_awd_hist_pkg;