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