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