[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_LOCKBOX_INTS_PKG
Source
1 PACKAGE BODY igs_fi_lockbox_ints_pkg AS
2 /* $Header: IGSSID7B.pls 115.0 2003/06/19 06:38:00 agairola noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_fi_lockbox_ints%ROWTYPE;
6 new_references igs_fi_lockbox_ints%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_lockbox_interface_id IN NUMBER,
12 x_record_identifier_cd IN VARCHAR2,
13 x_record_status IN VARCHAR2,
14 x_deposit_date IN DATE,
15 x_transmission_record_count IN NUMBER,
16 x_transmission_amt IN NUMBER,
17 x_lockbox_name IN VARCHAR2,
18 x_lockbox_batch_count IN NUMBER,
19 x_lockbox_record_count IN NUMBER,
20 x_lockbox_amt IN NUMBER,
21 x_batch_name IN VARCHAR2,
22 x_batch_amt IN NUMBER,
23 x_batch_record_count IN NUMBER,
24 x_item_number IN NUMBER,
25 x_receipt_amt IN NUMBER,
26 x_check_cd IN VARCHAR2,
27 x_party_number IN VARCHAR2,
28 x_payer_name IN VARCHAR2,
29 x_charge_cd1 IN VARCHAR2,
30 x_charge_cd2 IN VARCHAR2,
31 x_charge_cd3 IN VARCHAR2,
32 x_charge_cd4 IN VARCHAR2,
33 x_charge_cd5 IN VARCHAR2,
34 x_charge_cd6 IN VARCHAR2,
35 x_charge_cd7 IN VARCHAR2,
36 x_charge_cd8 IN VARCHAR2,
37 x_applied_amt1 IN NUMBER,
38 x_applied_amt2 IN NUMBER,
39 x_applied_amt3 IN NUMBER,
40 x_applied_amt4 IN NUMBER,
41 x_applied_amt5 IN NUMBER,
42 x_applied_amt6 IN NUMBER,
43 x_applied_amt7 IN NUMBER,
44 x_applied_amt8 IN NUMBER,
45 x_credit_type_cd IN VARCHAR2,
46 x_fee_cal_instance_cd IN VARCHAR2,
47 x_adm_application_id IN NUMBER,
48 x_attribute_category IN VARCHAR2,
49 x_attribute1 IN VARCHAR2,
50 x_attribute2 IN VARCHAR2,
51 x_attribute3 IN VARCHAR2,
52 x_attribute4 IN VARCHAR2,
53 x_attribute5 IN VARCHAR2,
54 x_attribute6 IN VARCHAR2,
55 x_attribute7 IN VARCHAR2,
56 x_attribute8 IN VARCHAR2,
57 x_attribute9 IN VARCHAR2,
58 x_attribute10 IN VARCHAR2,
59 x_attribute11 IN VARCHAR2,
60 x_attribute12 IN VARCHAR2,
61 x_attribute13 IN VARCHAR2,
62 x_attribute14 IN VARCHAR2,
63 x_attribute15 IN VARCHAR2,
64 x_attribute16 IN VARCHAR2,
65 x_attribute17 IN VARCHAR2,
66 x_attribute18 IN VARCHAR2,
67 x_attribute19 IN VARCHAR2,
68 x_attribute20 IN VARCHAR2,
69 x_creation_date IN DATE,
70 x_created_by IN NUMBER,
71 x_last_update_date IN DATE,
72 x_last_updated_by IN NUMBER,
73 x_last_update_login IN NUMBER
74 ) AS
75 /*
76 || Created By : [email protected]
77 || Created On : 18-JUN-2003
78 || Purpose : Initialises the Old and New references for the columns of the table.
79 || Known limitations, enhancements or remarks :
80 || Change History :
81 || Who When What
82 || (reverse chronological order - newest change first)
83 */
84
85 CURSOR cur_old_ref_values IS
86 SELECT *
87 FROM igs_fi_lockbox_ints
88 WHERE rowid = x_rowid;
89
90 BEGIN
91
92 l_rowid := x_rowid;
93
94 -- Code for setting the Old and New Reference Values.
95 -- Populate Old Values.
96 OPEN cur_old_ref_values;
97 FETCH cur_old_ref_values INTO old_references;
98 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
99 CLOSE cur_old_ref_values;
100 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
101 igs_ge_msg_stack.add;
102 app_exception.raise_exception;
103 RETURN;
104 END IF;
105 CLOSE cur_old_ref_values;
106
107 -- Populate New Values.
108 new_references.lockbox_interface_id := x_lockbox_interface_id;
109 new_references.record_identifier_cd := x_record_identifier_cd;
110 new_references.record_status := x_record_status;
111 new_references.deposit_date := x_deposit_date;
112 new_references.transmission_record_count := x_transmission_record_count;
113 new_references.transmission_amt := x_transmission_amt;
114 new_references.lockbox_name := x_lockbox_name;
115 new_references.lockbox_batch_count := x_lockbox_batch_count;
116 new_references.lockbox_record_count := x_lockbox_record_count;
117 new_references.lockbox_amt := x_lockbox_amt;
118 new_references.batch_name := x_batch_name;
119 new_references.batch_amt := x_batch_amt;
120 new_references.batch_record_count := x_batch_record_count;
121 new_references.item_number := x_item_number;
122 new_references.receipt_amt := x_receipt_amt;
123 new_references.check_cd := x_check_cd;
124 new_references.party_number := x_party_number;
125 new_references.payer_name := x_payer_name;
126 new_references.charge_cd1 := x_charge_cd1;
127 new_references.charge_cd2 := x_charge_cd2;
128 new_references.charge_cd3 := x_charge_cd3;
129 new_references.charge_cd4 := x_charge_cd4;
130 new_references.charge_cd5 := x_charge_cd5;
131 new_references.charge_cd6 := x_charge_cd6;
132 new_references.charge_cd7 := x_charge_cd7;
133 new_references.charge_cd8 := x_charge_cd8;
134 new_references.applied_amt1 := x_applied_amt1;
135 new_references.applied_amt2 := x_applied_amt2;
136 new_references.applied_amt3 := x_applied_amt3;
137 new_references.applied_amt4 := x_applied_amt4;
138 new_references.applied_amt5 := x_applied_amt5;
139 new_references.applied_amt6 := x_applied_amt6;
140 new_references.applied_amt7 := x_applied_amt7;
141 new_references.applied_amt8 := x_applied_amt8;
142 new_references.credit_type_cd := x_credit_type_cd;
143 new_references.fee_cal_instance_cd := x_fee_cal_instance_cd;
144 new_references.adm_application_id := x_adm_application_id;
145 new_references.attribute_category := x_attribute_category;
146 new_references.attribute1 := x_attribute1;
147 new_references.attribute2 := x_attribute2;
148 new_references.attribute3 := x_attribute3;
149 new_references.attribute4 := x_attribute4;
150 new_references.attribute5 := x_attribute5;
151 new_references.attribute6 := x_attribute6;
152 new_references.attribute7 := x_attribute7;
153 new_references.attribute8 := x_attribute8;
154 new_references.attribute9 := x_attribute9;
155 new_references.attribute10 := x_attribute10;
156 new_references.attribute11 := x_attribute11;
157 new_references.attribute12 := x_attribute12;
158 new_references.attribute13 := x_attribute13;
159 new_references.attribute14 := x_attribute14;
160 new_references.attribute15 := x_attribute15;
161 new_references.attribute16 := x_attribute16;
162 new_references.attribute17 := x_attribute17;
163 new_references.attribute18 := x_attribute18;
164 new_references.attribute19 := x_attribute19;
165 new_references.attribute20 := x_attribute20;
166
167 IF (p_action = 'UPDATE') THEN
168 new_references.creation_date := old_references.creation_date;
169 new_references.created_by := old_references.created_by;
170 ELSE
171 new_references.creation_date := x_creation_date;
172 new_references.created_by := x_created_by;
173 END IF;
174
175 new_references.last_update_date := x_last_update_date;
176 new_references.last_updated_by := x_last_updated_by;
177 new_references.last_update_login := x_last_update_login;
178
179 END set_column_values;
180
181
182 FUNCTION get_pk_for_validation (
183 x_lockbox_interface_id IN NUMBER
184 ) RETURN BOOLEAN AS
185 /*
186 || Created By : [email protected]
187 || Created On : 18-JUN-2003
188 || Purpose : Validates the Primary Key of the table.
189 || Known limitations, enhancements or remarks :
190 || Change History :
191 || Who When What
192 || (reverse chronological order - newest change first)
193 */
194 CURSOR cur_rowid IS
195 SELECT rowid
196 FROM igs_fi_lockbox_ints
197 WHERE lockbox_interface_id = x_lockbox_interface_id
198 FOR UPDATE NOWAIT;
199
200 lv_rowid cur_rowid%RowType;
201
202 BEGIN
203
204 OPEN cur_rowid;
205 FETCH cur_rowid INTO lv_rowid;
206 IF (cur_rowid%FOUND) THEN
207 CLOSE cur_rowid;
208 RETURN(TRUE);
209 ELSE
210 CLOSE cur_rowid;
211 RETURN(FALSE);
212 END IF;
213
214 END get_pk_for_validation;
215
216
217 PROCEDURE before_dml (
218 p_action IN VARCHAR2,
219 x_rowid IN VARCHAR2,
220 x_lockbox_interface_id IN NUMBER,
221 x_record_identifier_cd IN VARCHAR2,
222 x_record_status IN VARCHAR2,
223 x_deposit_date IN DATE,
224 x_transmission_record_count IN NUMBER,
225 x_transmission_amt IN NUMBER,
226 x_lockbox_name IN VARCHAR2,
227 x_lockbox_batch_count IN NUMBER,
228 x_lockbox_record_count IN NUMBER,
229 x_lockbox_amt IN NUMBER,
230 x_batch_name IN VARCHAR2,
231 x_batch_amt IN NUMBER,
232 x_batch_record_count IN NUMBER,
233 x_item_number IN NUMBER,
234 x_receipt_amt IN NUMBER,
235 x_check_cd IN VARCHAR2,
236 x_party_number IN VARCHAR2,
237 x_payer_name IN VARCHAR2,
238 x_charge_cd1 IN VARCHAR2,
239 x_charge_cd2 IN VARCHAR2,
240 x_charge_cd3 IN VARCHAR2,
241 x_charge_cd4 IN VARCHAR2,
242 x_charge_cd5 IN VARCHAR2,
243 x_charge_cd6 IN VARCHAR2,
244 x_charge_cd7 IN VARCHAR2,
245 x_charge_cd8 IN VARCHAR2,
246 x_applied_amt1 IN NUMBER,
247 x_applied_amt2 IN NUMBER,
248 x_applied_amt3 IN NUMBER,
249 x_applied_amt4 IN NUMBER,
250 x_applied_amt5 IN NUMBER,
251 x_applied_amt6 IN NUMBER,
252 x_applied_amt7 IN NUMBER,
253 x_applied_amt8 IN NUMBER,
254 x_credit_type_cd IN VARCHAR2,
255 x_fee_cal_instance_cd IN VARCHAR2,
256 x_adm_application_id IN NUMBER,
257 x_attribute_category IN VARCHAR2,
258 x_attribute1 IN VARCHAR2,
259 x_attribute2 IN VARCHAR2,
260 x_attribute3 IN VARCHAR2,
261 x_attribute4 IN VARCHAR2,
262 x_attribute5 IN VARCHAR2,
263 x_attribute6 IN VARCHAR2,
264 x_attribute7 IN VARCHAR2,
265 x_attribute8 IN VARCHAR2,
266 x_attribute9 IN VARCHAR2,
267 x_attribute10 IN VARCHAR2,
268 x_attribute11 IN VARCHAR2,
269 x_attribute12 IN VARCHAR2,
270 x_attribute13 IN VARCHAR2,
271 x_attribute14 IN VARCHAR2,
272 x_attribute15 IN VARCHAR2,
273 x_attribute16 IN VARCHAR2,
274 x_attribute17 IN VARCHAR2,
275 x_attribute18 IN VARCHAR2,
276 x_attribute19 IN VARCHAR2,
277 x_attribute20 IN VARCHAR2,
278 x_creation_date IN DATE,
279 x_created_by IN NUMBER,
280 x_last_update_date IN DATE,
281 x_last_updated_by IN NUMBER,
282 x_last_update_login IN NUMBER
283 ) AS
284 /*
285 || Created By : [email protected]
286 || Created On : 18-JUN-2003
287 || Purpose : Initialises the columns, Checks Constraints, Calls the
288 || Trigger Handlers for the table, before any DML operation.
289 || Known limitations, enhancements or remarks :
290 || Change History :
291 || Who When What
292 || (reverse chronological order - newest change first)
293 */
294 BEGIN
295
296 set_column_values (
297 p_action,
298 x_rowid,
299 x_lockbox_interface_id,
300 x_record_identifier_cd,
301 x_record_status,
302 x_deposit_date,
303 x_transmission_record_count,
304 x_transmission_amt,
305 x_lockbox_name,
306 x_lockbox_batch_count,
307 x_lockbox_record_count,
308 x_lockbox_amt,
309 x_batch_name,
310 x_batch_amt,
311 x_batch_record_count,
312 x_item_number,
313 x_receipt_amt,
314 x_check_cd,
315 x_party_number,
316 x_payer_name,
317 x_charge_cd1,
318 x_charge_cd2,
319 x_charge_cd3,
320 x_charge_cd4,
321 x_charge_cd5,
322 x_charge_cd6,
323 x_charge_cd7,
324 x_charge_cd8,
325 x_applied_amt1,
326 x_applied_amt2,
327 x_applied_amt3,
328 x_applied_amt4,
329 x_applied_amt5,
330 x_applied_amt6,
331 x_applied_amt7,
332 x_applied_amt8,
333 x_credit_type_cd,
334 x_fee_cal_instance_cd,
335 x_adm_application_id,
336 x_attribute_category,
340 x_attribute4,
337 x_attribute1,
338 x_attribute2,
339 x_attribute3,
341 x_attribute5,
342 x_attribute6,
343 x_attribute7,
344 x_attribute8,
345 x_attribute9,
346 x_attribute10,
347 x_attribute11,
348 x_attribute12,
349 x_attribute13,
350 x_attribute14,
351 x_attribute15,
352 x_attribute16,
353 x_attribute17,
354 x_attribute18,
355 x_attribute19,
356 x_attribute20,
357 x_creation_date,
358 x_created_by,
359 x_last_update_date,
360 x_last_updated_by,
361 x_last_update_login
362 );
363
364 IF (p_action = 'INSERT') THEN
365 -- Call all the procedures related to Before Insert.
366 IF ( get_pk_for_validation(
367 new_references.lockbox_interface_id
368 )
369 ) THEN
370 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
371 igs_ge_msg_stack.add;
372 app_exception.raise_exception;
373 END IF;
374 ELSIF (p_action = 'VALIDATE_INSERT') THEN
375 -- Call all the procedures related to Before Insert.
376 IF ( get_pk_for_validation (
377 new_references.lockbox_interface_id
378 )
379 ) THEN
380 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
381 igs_ge_msg_stack.add;
382 app_exception.raise_exception;
383 END IF;
384 END IF;
385
386 END before_dml;
387
388
389 PROCEDURE insert_row (
390 x_rowid IN OUT NOCOPY VARCHAR2,
391 x_lockbox_interface_id IN OUT NOCOPY NUMBER,
392 x_record_identifier_cd IN VARCHAR2,
393 x_record_status IN VARCHAR2,
394 x_deposit_date IN DATE,
395 x_transmission_record_count IN NUMBER,
396 x_transmission_amt IN NUMBER,
397 x_lockbox_name IN VARCHAR2,
398 x_lockbox_batch_count IN NUMBER,
399 x_lockbox_record_count IN NUMBER,
400 x_lockbox_amt IN NUMBER,
401 x_batch_name IN VARCHAR2,
402 x_batch_amt IN NUMBER,
403 x_batch_record_count IN NUMBER,
404 x_item_number IN NUMBER,
405 x_receipt_amt IN NUMBER,
406 x_check_cd IN VARCHAR2,
407 x_party_number IN VARCHAR2,
408 x_payer_name IN VARCHAR2,
409 x_charge_cd1 IN VARCHAR2,
410 x_charge_cd2 IN VARCHAR2,
411 x_charge_cd3 IN VARCHAR2,
412 x_charge_cd4 IN VARCHAR2,
413 x_charge_cd5 IN VARCHAR2,
414 x_charge_cd6 IN VARCHAR2,
415 x_charge_cd7 IN VARCHAR2,
416 x_charge_cd8 IN VARCHAR2,
417 x_applied_amt1 IN NUMBER,
418 x_applied_amt2 IN NUMBER,
419 x_applied_amt3 IN NUMBER,
420 x_applied_amt4 IN NUMBER,
421 x_applied_amt5 IN NUMBER,
422 x_applied_amt6 IN NUMBER,
423 x_applied_amt7 IN NUMBER,
424 x_applied_amt8 IN NUMBER,
425 x_credit_type_cd IN VARCHAR2,
426 x_fee_cal_instance_cd IN VARCHAR2,
427 x_adm_application_id IN NUMBER,
428 x_attribute_category IN VARCHAR2,
429 x_attribute1 IN VARCHAR2,
430 x_attribute2 IN VARCHAR2,
431 x_attribute3 IN VARCHAR2,
432 x_attribute4 IN VARCHAR2,
433 x_attribute5 IN VARCHAR2,
434 x_attribute6 IN VARCHAR2,
435 x_attribute7 IN VARCHAR2,
436 x_attribute8 IN VARCHAR2,
437 x_attribute9 IN VARCHAR2,
438 x_attribute10 IN VARCHAR2,
439 x_attribute11 IN VARCHAR2,
440 x_attribute12 IN VARCHAR2,
441 x_attribute13 IN VARCHAR2,
442 x_attribute14 IN VARCHAR2,
443 x_attribute15 IN VARCHAR2,
444 x_attribute16 IN VARCHAR2,
445 x_attribute17 IN VARCHAR2,
446 x_attribute18 IN VARCHAR2,
447 x_attribute19 IN VARCHAR2,
448 x_attribute20 IN VARCHAR2,
449 x_mode IN VARCHAR2
450 ) AS
451 /*
452 || Created By : [email protected]
453 || Created On : 18-JUN-2003
454 || Purpose : Handles the INSERT DML logic for the table.
455 || Known limitations, enhancements or remarks :
456 || Change History :
457 || Who When What
458 || (reverse chronological order - newest change first)
459 */
460
461 x_last_update_date DATE;
462 x_last_updated_by NUMBER;
463 x_last_update_login NUMBER;
464 x_request_id NUMBER;
465 x_program_id NUMBER;
466 x_program_application_id NUMBER;
467 x_program_update_date DATE;
468
469 BEGIN
470
471 x_last_update_date := SYSDATE;
472 IF (x_mode = 'I') THEN
473 x_last_updated_by := 1;
474 x_last_update_login := 0;
475 ELSIF (x_mode = 'R') THEN
476 x_last_updated_by := fnd_global.user_id;
477 IF (x_last_updated_by IS NULL) THEN
478 x_last_updated_by := -1;
479 END IF;
480 x_last_update_login := fnd_global.login_id;
481 IF (x_last_update_login IS NULL) THEN
482 x_last_update_login := -1;
483 END IF;
484 x_request_id := fnd_global.conc_request_id;
485 x_program_id := fnd_global.conc_program_id;
486 x_program_application_id := fnd_global.prog_appl_id;
487
488 IF (x_request_id = -1) THEN
489 x_request_id := NULL;
490 x_program_id := NULL;
491 x_program_application_id := NULL;
492 x_program_update_date := NULL;
493 ELSE
494 x_program_update_date := SYSDATE;
495 END IF;
496 ELSE
497 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
498 fnd_message.set_token ('ROUTINE', 'IGS_FI_LOCKBOX_INTS_PKG.INSERT_ROW');
499 igs_ge_msg_stack.add;
500 app_exception.raise_exception;
501 END IF;
502
503 x_lockbox_interface_id := NULL;
504
505 before_dml(
506 p_action => 'INSERT',
507 x_rowid => x_rowid,
508 x_lockbox_interface_id => x_lockbox_interface_id,
509 x_record_identifier_cd => x_record_identifier_cd,
510 x_record_status => x_record_status,
511 x_deposit_date => x_deposit_date,
512 x_transmission_record_count => x_transmission_record_count,
513 x_transmission_amt => x_transmission_amt,
514 x_lockbox_name => x_lockbox_name,
515 x_lockbox_batch_count => x_lockbox_batch_count,
516 x_lockbox_record_count => x_lockbox_record_count,
517 x_lockbox_amt => x_lockbox_amt,
518 x_batch_name => x_batch_name,
519 x_batch_amt => x_batch_amt,
520 x_batch_record_count => x_batch_record_count,
521 x_item_number => x_item_number,
522 x_receipt_amt => x_receipt_amt,
523 x_check_cd => x_check_cd,
524 x_party_number => x_party_number,
525 x_payer_name => x_payer_name,
526 x_charge_cd1 => x_charge_cd1,
527 x_charge_cd2 => x_charge_cd2,
528 x_charge_cd3 => x_charge_cd3,
529 x_charge_cd4 => x_charge_cd4,
530 x_charge_cd5 => x_charge_cd5,
531 x_charge_cd6 => x_charge_cd6,
532 x_charge_cd7 => x_charge_cd7,
533 x_charge_cd8 => x_charge_cd8,
534 x_applied_amt1 => x_applied_amt1,
535 x_applied_amt2 => x_applied_amt2,
536 x_applied_amt3 => x_applied_amt3,
537 x_applied_amt4 => x_applied_amt4,
538 x_applied_amt5 => x_applied_amt5,
539 x_applied_amt6 => x_applied_amt6,
540 x_applied_amt7 => x_applied_amt7,
541 x_applied_amt8 => x_applied_amt8,
542 x_credit_type_cd => x_credit_type_cd,
543 x_fee_cal_instance_cd => x_fee_cal_instance_cd,
544 x_adm_application_id => x_adm_application_id,
545 x_attribute_category => x_attribute_category,
546 x_attribute1 => x_attribute1,
547 x_attribute2 => x_attribute2,
548 x_attribute3 => x_attribute3,
549 x_attribute4 => x_attribute4,
550 x_attribute5 => x_attribute5,
551 x_attribute6 => x_attribute6,
552 x_attribute7 => x_attribute7,
553 x_attribute8 => x_attribute8,
554 x_attribute9 => x_attribute9,
555 x_attribute10 => x_attribute10,
556 x_attribute11 => x_attribute11,
557 x_attribute12 => x_attribute12,
558 x_attribute13 => x_attribute13,
559 x_attribute14 => x_attribute14,
560 x_attribute15 => x_attribute15,
561 x_attribute16 => x_attribute16,
562 x_attribute17 => x_attribute17,
563 x_attribute18 => x_attribute18,
564 x_attribute19 => x_attribute19,
565 x_attribute20 => x_attribute20,
566 x_creation_date => x_last_update_date,
567 x_created_by => x_last_updated_by,
568 x_last_update_date => x_last_update_date,
569 x_last_updated_by => x_last_updated_by,
570 x_last_update_login => x_last_update_login
571 );
572
573 INSERT INTO igs_fi_lockbox_ints (
574 lockbox_interface_id,
575 record_identifier_cd,
576 record_status,
577 deposit_date,
578 transmission_record_count,
579 transmission_amt,
580 lockbox_name,
581 lockbox_batch_count,
582 lockbox_record_count,
583 lockbox_amt,
584 batch_name,
585 batch_amt,
586 batch_record_count,
587 item_number,
588 receipt_amt,
589 check_cd,
590 party_number,
591 payer_name,
592 charge_cd1,
593 charge_cd2,
594 charge_cd3,
595 charge_cd4,
596 charge_cd5,
597 charge_cd6,
598 charge_cd7,
599 charge_cd8,
600 applied_amt1,
601 applied_amt2,
602 applied_amt3,
603 applied_amt4,
604 applied_amt5,
605 applied_amt6,
606 applied_amt7,
607 applied_amt8,
608 credit_type_cd,
609 fee_cal_instance_cd,
610 adm_application_id,
611 attribute_category,
612 attribute1,
613 attribute2,
614 attribute3,
615 attribute4,
616 attribute5,
617 attribute6,
618 attribute7,
619 attribute8,
620 attribute9,
621 attribute10,
622 attribute11,
623 attribute12,
624 attribute13,
625 attribute14,
626 attribute15,
627 attribute16,
628 attribute17,
629 attribute18,
630 attribute19,
631 attribute20,
632 creation_date,
633 created_by,
634 last_update_date,
635 last_updated_by,
636 last_update_login,
637 request_id,
638 program_id,
639 program_application_id,
640 program_update_date
641 ) VALUES (
642 igs_fi_lockbox_ints_s.NEXTVAL,
643 new_references.record_identifier_cd,
644 new_references.record_status,
645 new_references.deposit_date,
646 new_references.transmission_record_count,
647 new_references.transmission_amt,
648 new_references.lockbox_name,
649 new_references.lockbox_batch_count,
650 new_references.lockbox_record_count,
651 new_references.lockbox_amt,
652 new_references.batch_name,
653 new_references.batch_amt,
654 new_references.batch_record_count,
655 new_references.item_number,
656 new_references.receipt_amt,
657 new_references.check_cd,
658 new_references.party_number,
659 new_references.payer_name,
660 new_references.charge_cd1,
661 new_references.charge_cd2,
662 new_references.charge_cd3,
663 new_references.charge_cd4,
664 new_references.charge_cd5,
665 new_references.charge_cd6,
666 new_references.charge_cd7,
667 new_references.charge_cd8,
668 new_references.applied_amt1,
669 new_references.applied_amt2,
670 new_references.applied_amt3,
671 new_references.applied_amt4,
672 new_references.applied_amt5,
673 new_references.applied_amt6,
674 new_references.applied_amt7,
675 new_references.applied_amt8,
676 new_references.credit_type_cd,
677 new_references.fee_cal_instance_cd,
678 new_references.adm_application_id,
679 new_references.attribute_category,
680 new_references.attribute1,
681 new_references.attribute2,
682 new_references.attribute3,
683 new_references.attribute4,
684 new_references.attribute5,
685 new_references.attribute6,
686 new_references.attribute7,
687 new_references.attribute8,
688 new_references.attribute9,
689 new_references.attribute10,
690 new_references.attribute11,
691 new_references.attribute12,
692 new_references.attribute13,
693 new_references.attribute14,
694 new_references.attribute15,
695 new_references.attribute16,
696 new_references.attribute17,
697 new_references.attribute18,
698 new_references.attribute19,
699 new_references.attribute20,
700 x_last_update_date,
701 x_last_updated_by,
702 x_last_update_date,
703 x_last_updated_by,
704 x_last_update_login ,
705 x_request_id,
706 x_program_id,
707 x_program_application_id,
708 x_program_update_date
709 ) RETURNING ROWID, lockbox_interface_id INTO x_rowid, x_lockbox_interface_id;
710
711 END insert_row;
712
713
714 PROCEDURE lock_row (
715 x_rowid IN VARCHAR2,
716 x_lockbox_interface_id IN NUMBER,
717 x_record_identifier_cd IN VARCHAR2,
718 x_record_status IN VARCHAR2,
719 x_deposit_date IN DATE,
720 x_transmission_record_count IN NUMBER,
721 x_transmission_amt IN NUMBER,
722 x_lockbox_name IN VARCHAR2,
723 x_lockbox_batch_count IN NUMBER,
724 x_lockbox_record_count IN NUMBER,
725 x_lockbox_amt IN NUMBER,
726 x_batch_name IN VARCHAR2,
727 x_batch_amt IN NUMBER,
728 x_batch_record_count IN NUMBER,
729 x_item_number IN NUMBER,
735 x_charge_cd2 IN VARCHAR2,
730 x_receipt_amt IN NUMBER,
731 x_check_cd IN VARCHAR2,
732 x_party_number IN VARCHAR2,
733 x_payer_name IN VARCHAR2,
734 x_charge_cd1 IN VARCHAR2,
736 x_charge_cd3 IN VARCHAR2,
737 x_charge_cd4 IN VARCHAR2,
738 x_charge_cd5 IN VARCHAR2,
739 x_charge_cd6 IN VARCHAR2,
740 x_charge_cd7 IN VARCHAR2,
741 x_charge_cd8 IN VARCHAR2,
742 x_applied_amt1 IN NUMBER,
743 x_applied_amt2 IN NUMBER,
744 x_applied_amt3 IN NUMBER,
745 x_applied_amt4 IN NUMBER,
746 x_applied_amt5 IN NUMBER,
747 x_applied_amt6 IN NUMBER,
748 x_applied_amt7 IN NUMBER,
749 x_applied_amt8 IN NUMBER,
750 x_credit_type_cd IN VARCHAR2,
751 x_fee_cal_instance_cd IN VARCHAR2,
752 x_adm_application_id IN NUMBER,
753 x_attribute_category IN VARCHAR2,
754 x_attribute1 IN VARCHAR2,
755 x_attribute2 IN VARCHAR2,
756 x_attribute3 IN VARCHAR2,
757 x_attribute4 IN VARCHAR2,
758 x_attribute5 IN VARCHAR2,
759 x_attribute6 IN VARCHAR2,
760 x_attribute7 IN VARCHAR2,
761 x_attribute8 IN VARCHAR2,
762 x_attribute9 IN VARCHAR2,
763 x_attribute10 IN VARCHAR2,
764 x_attribute11 IN VARCHAR2,
765 x_attribute12 IN VARCHAR2,
766 x_attribute13 IN VARCHAR2,
767 x_attribute14 IN VARCHAR2,
768 x_attribute15 IN VARCHAR2,
769 x_attribute16 IN VARCHAR2,
770 x_attribute17 IN VARCHAR2,
771 x_attribute18 IN VARCHAR2,
772 x_attribute19 IN VARCHAR2,
773 x_attribute20 IN VARCHAR2
774 ) AS
775 /*
776 || Created By : [email protected]
777 || Created On : 18-JUN-2003
778 || Purpose : Handles the LOCK mechanism for the table.
779 || Known limitations, enhancements or remarks :
780 || Change History :
781 || Who When What
782 || (reverse chronological order - newest change first)
783 */
784 CURSOR c1 IS
785 SELECT
786 record_identifier_cd,
787 record_status,
788 deposit_date,
789 transmission_record_count,
790 transmission_amt,
791 lockbox_name,
792 lockbox_batch_count,
793 lockbox_record_count,
794 lockbox_amt,
795 batch_name,
796 batch_amt,
797 batch_record_count,
798 item_number,
799 receipt_amt,
800 check_cd,
804 charge_cd2,
801 party_number,
802 payer_name,
803 charge_cd1,
805 charge_cd3,
806 charge_cd4,
807 charge_cd5,
808 charge_cd6,
809 charge_cd7,
810 charge_cd8,
811 applied_amt1,
812 applied_amt2,
813 applied_amt3,
814 applied_amt4,
815 applied_amt5,
816 applied_amt6,
817 applied_amt7,
818 applied_amt8,
819 credit_type_cd,
823 attribute1,
820 fee_cal_instance_cd,
821 adm_application_id,
822 attribute_category,
824 attribute2,
825 attribute3,
826 attribute4,
827 attribute5,
828 attribute6,
829 attribute7,
830 attribute8,
831 attribute9,
832 attribute10,
833 attribute11,
834 attribute12,
835 attribute13,
836 attribute14,
837 attribute15,
838 attribute16,
839 attribute17,
840 attribute18,
841 attribute19,
842 attribute20
843 FROM igs_fi_lockbox_ints
844 WHERE rowid = x_rowid
845 FOR UPDATE NOWAIT;
846
847 tlinfo c1%ROWTYPE;
848
849 BEGIN
850
851 OPEN c1;
852 FETCH c1 INTO tlinfo;
853 IF (c1%notfound) THEN
854 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
855 igs_ge_msg_stack.add;
856 CLOSE c1;
857 app_exception.raise_exception;
858 RETURN;
859 END IF;
860 CLOSE c1;
861
862 IF (
863 (tlinfo.record_identifier_cd = x_record_identifier_cd)
864 AND (tlinfo.record_status = x_record_status)
865 AND ((tlinfo.deposit_date = x_deposit_date) OR ((tlinfo.deposit_date IS NULL) AND (X_deposit_date IS NULL)))
866 AND ((tlinfo.transmission_record_count = x_transmission_record_count) OR ((tlinfo.transmission_record_count IS NULL) AND (X_transmission_record_count IS NULL)))
867 AND ((tlinfo.transmission_amt = x_transmission_amt) OR ((tlinfo.transmission_amt IS NULL) AND (X_transmission_amt IS NULL)))
868 AND ((tlinfo.lockbox_name = x_lockbox_name) OR ((tlinfo.lockbox_name IS NULL) AND (X_lockbox_name IS NULL)))
869 AND ((tlinfo.lockbox_batch_count = x_lockbox_batch_count) OR ((tlinfo.lockbox_batch_count IS NULL) AND (X_lockbox_batch_count IS NULL)))
870 AND ((tlinfo.lockbox_record_count = x_lockbox_record_count) OR ((tlinfo.lockbox_record_count IS NULL) AND (X_lockbox_record_count IS NULL)))
871 AND ((tlinfo.lockbox_amt = x_lockbox_amt) OR ((tlinfo.lockbox_amt IS NULL) AND (X_lockbox_amt IS NULL)))
872 AND ((tlinfo.batch_name = x_batch_name) OR ((tlinfo.batch_name IS NULL) AND (X_batch_name IS NULL)))
873 AND ((tlinfo.batch_amt = x_batch_amt) OR ((tlinfo.batch_amt IS NULL) AND (X_batch_amt IS NULL)))
874 AND ((tlinfo.batch_record_count = x_batch_record_count) OR ((tlinfo.batch_record_count IS NULL) AND (X_batch_record_count IS NULL)))
875 AND ((tlinfo.item_number = x_item_number) OR ((tlinfo.item_number IS NULL) AND (X_item_number IS NULL)))
876 AND ((tlinfo.receipt_amt = x_receipt_amt) OR ((tlinfo.receipt_amt IS NULL) AND (X_receipt_amt IS NULL)))
877 AND ((tlinfo.check_cd = x_check_cd) OR ((tlinfo.check_cd IS NULL) AND (X_check_cd IS NULL)))
878 AND ((tlinfo.party_number = x_party_number) OR ((tlinfo.party_number IS NULL) AND (X_party_number IS NULL)))
879 AND ((tlinfo.payer_name = x_payer_name) OR ((tlinfo.payer_name IS NULL) AND (X_payer_name IS NULL)))
880 AND ((tlinfo.charge_cd1 = x_charge_cd1) OR ((tlinfo.charge_cd1 IS NULL) AND (X_charge_cd1 IS NULL)))
881 AND ((tlinfo.charge_cd2 = x_charge_cd2) OR ((tlinfo.charge_cd2 IS NULL) AND (X_charge_cd2 IS NULL)))
882 AND ((tlinfo.charge_cd3 = x_charge_cd3) OR ((tlinfo.charge_cd3 IS NULL) AND (X_charge_cd3 IS NULL)))
883 AND ((tlinfo.charge_cd4 = x_charge_cd4) OR ((tlinfo.charge_cd4 IS NULL) AND (X_charge_cd4 IS NULL)))
884 AND ((tlinfo.charge_cd5 = x_charge_cd5) OR ((tlinfo.charge_cd5 IS NULL) AND (X_charge_cd5 IS NULL)))
885 AND ((tlinfo.charge_cd6 = x_charge_cd6) OR ((tlinfo.charge_cd6 IS NULL) AND (X_charge_cd6 IS NULL)))
886 AND ((tlinfo.charge_cd7 = x_charge_cd7) OR ((tlinfo.charge_cd7 IS NULL) AND (X_charge_cd7 IS NULL)))
887 AND ((tlinfo.charge_cd8 = x_charge_cd8) OR ((tlinfo.charge_cd8 IS NULL) AND (X_charge_cd8 IS NULL)))
888 AND ((tlinfo.applied_amt1 = x_applied_amt1) OR ((tlinfo.applied_amt1 IS NULL) AND (X_applied_amt1 IS NULL)))
889 AND ((tlinfo.applied_amt2 = x_applied_amt2) OR ((tlinfo.applied_amt2 IS NULL) AND (X_applied_amt2 IS NULL)))
893 AND ((tlinfo.applied_amt6 = x_applied_amt6) OR ((tlinfo.applied_amt6 IS NULL) AND (X_applied_amt6 IS NULL)))
890 AND ((tlinfo.applied_amt3 = x_applied_amt3) OR ((tlinfo.applied_amt3 IS NULL) AND (X_applied_amt3 IS NULL)))
891 AND ((tlinfo.applied_amt4 = x_applied_amt4) OR ((tlinfo.applied_amt4 IS NULL) AND (X_applied_amt4 IS NULL)))
892 AND ((tlinfo.applied_amt5 = x_applied_amt5) OR ((tlinfo.applied_amt5 IS NULL) AND (X_applied_amt5 IS NULL)))
894 AND ((tlinfo.applied_amt7 = x_applied_amt7) OR ((tlinfo.applied_amt7 IS NULL) AND (X_applied_amt7 IS NULL)))
895 AND ((tlinfo.applied_amt8 = x_applied_amt8) OR ((tlinfo.applied_amt8 IS NULL) AND (X_applied_amt8 IS NULL)))
896 AND ((tlinfo.credit_type_cd = x_credit_type_cd) OR ((tlinfo.credit_type_cd IS NULL) AND (X_credit_type_cd IS NULL)))
897 AND ((tlinfo.fee_cal_instance_cd = x_fee_cal_instance_cd) OR ((tlinfo.fee_cal_instance_cd IS NULL) AND (X_fee_cal_instance_cd IS NULL)))
898 AND ((tlinfo.adm_application_id = x_adm_application_id) OR ((tlinfo.adm_application_id IS NULL) AND (X_adm_application_id IS NULL)))
899 AND ((tlinfo.attribute_category = x_attribute_category) OR ((tlinfo.attribute_category IS NULL) AND (X_attribute_category IS NULL)))
900 AND ((tlinfo.attribute1 = x_attribute1) OR ((tlinfo.attribute1 IS NULL) AND (X_attribute1 IS NULL)))
901 AND ((tlinfo.attribute2 = x_attribute2) OR ((tlinfo.attribute2 IS NULL) AND (X_attribute2 IS NULL)))
902 AND ((tlinfo.attribute3 = x_attribute3) OR ((tlinfo.attribute3 IS NULL) AND (X_attribute3 IS NULL)))
903 AND ((tlinfo.attribute4 = x_attribute4) OR ((tlinfo.attribute4 IS NULL) AND (X_attribute4 IS NULL)))
904 AND ((tlinfo.attribute5 = x_attribute5) OR ((tlinfo.attribute5 IS NULL) AND (X_attribute5 IS NULL)))
905 AND ((tlinfo.attribute6 = x_attribute6) OR ((tlinfo.attribute6 IS NULL) AND (X_attribute6 IS NULL)))
906 AND ((tlinfo.attribute7 = x_attribute7) OR ((tlinfo.attribute7 IS NULL) AND (X_attribute7 IS NULL)))
907 AND ((tlinfo.attribute8 = x_attribute8) OR ((tlinfo.attribute8 IS NULL) AND (X_attribute8 IS NULL)))
908 AND ((tlinfo.attribute9 = x_attribute9) OR ((tlinfo.attribute9 IS NULL) AND (X_attribute9 IS NULL)))
909 AND ((tlinfo.attribute10 = x_attribute10) OR ((tlinfo.attribute10 IS NULL) AND (X_attribute10 IS NULL)))
910 AND ((tlinfo.attribute11 = x_attribute11) OR ((tlinfo.attribute11 IS NULL) AND (X_attribute11 IS NULL)))
911 AND ((tlinfo.attribute12 = x_attribute12) OR ((tlinfo.attribute12 IS NULL) AND (X_attribute12 IS NULL)))
912 AND ((tlinfo.attribute13 = x_attribute13) OR ((tlinfo.attribute13 IS NULL) AND (X_attribute13 IS NULL)))
913 AND ((tlinfo.attribute14 = x_attribute14) OR ((tlinfo.attribute14 IS NULL) AND (X_attribute14 IS NULL)))
914 AND ((tlinfo.attribute15 = x_attribute15) OR ((tlinfo.attribute15 IS NULL) AND (X_attribute15 IS NULL)))
915 AND ((tlinfo.attribute16 = x_attribute16) OR ((tlinfo.attribute16 IS NULL) AND (X_attribute16 IS NULL)))
916 AND ((tlinfo.attribute17 = x_attribute17) OR ((tlinfo.attribute17 IS NULL) AND (X_attribute17 IS NULL)))
917 AND ((tlinfo.attribute18 = x_attribute18) OR ((tlinfo.attribute18 IS NULL) AND (X_attribute18 IS NULL)))
918 AND ((tlinfo.attribute19 = x_attribute19) OR ((tlinfo.attribute19 IS NULL) AND (X_attribute19 IS NULL)))
919 AND ((tlinfo.attribute20 = x_attribute20) OR ((tlinfo.attribute20 IS NULL) AND (X_attribute20 IS NULL)))
920 ) THEN
921 NULL;
922 ELSE
923 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
924 igs_ge_msg_stack.add;
925 app_exception.raise_exception;
926 END IF;
927
928 RETURN;
929
930 END lock_row;
931
932
933 PROCEDURE update_row (
934 x_rowid IN VARCHAR2,
935 x_lockbox_interface_id IN NUMBER,
936 x_record_identifier_cd IN VARCHAR2,
937 x_record_status IN VARCHAR2,
938 x_deposit_date IN DATE,
939 x_transmission_record_count IN NUMBER,
940 x_transmission_amt IN NUMBER,
941 x_lockbox_name IN VARCHAR2,
942 x_lockbox_batch_count IN NUMBER,
943 x_lockbox_record_count IN NUMBER,
944 x_lockbox_amt IN NUMBER,
948 x_item_number IN NUMBER,
945 x_batch_name IN VARCHAR2,
946 x_batch_amt IN NUMBER,
947 x_batch_record_count IN NUMBER,
949 x_receipt_amt IN NUMBER,
950 x_check_cd IN VARCHAR2,
951 x_party_number IN VARCHAR2,
952 x_payer_name IN VARCHAR2,
953 x_charge_cd1 IN VARCHAR2,
954 x_charge_cd2 IN VARCHAR2,
955 x_charge_cd3 IN VARCHAR2,
956 x_charge_cd4 IN VARCHAR2,
957 x_charge_cd5 IN VARCHAR2,
958 x_charge_cd6 IN VARCHAR2,
959 x_charge_cd7 IN VARCHAR2,
960 x_charge_cd8 IN VARCHAR2,
961 x_applied_amt1 IN NUMBER,
962 x_applied_amt2 IN NUMBER,
963 x_applied_amt3 IN NUMBER,
964 x_applied_amt4 IN NUMBER,
965 x_applied_amt5 IN NUMBER,
966 x_applied_amt6 IN NUMBER,
967 x_applied_amt7 IN NUMBER,
968 x_applied_amt8 IN NUMBER,
969 x_credit_type_cd IN VARCHAR2,
970 x_fee_cal_instance_cd IN VARCHAR2,
971 x_adm_application_id IN NUMBER,
972 x_attribute_category IN VARCHAR2,
973 x_attribute1 IN VARCHAR2,
974 x_attribute2 IN VARCHAR2,
975 x_attribute3 IN VARCHAR2,
976 x_attribute4 IN VARCHAR2,
977 x_attribute5 IN VARCHAR2,
978 x_attribute6 IN VARCHAR2,
979 x_attribute7 IN VARCHAR2,
980 x_attribute8 IN VARCHAR2,
981 x_attribute9 IN VARCHAR2,
982 x_attribute10 IN VARCHAR2,
983 x_attribute11 IN VARCHAR2,
984 x_attribute12 IN VARCHAR2,
985 x_attribute13 IN VARCHAR2,
986 x_attribute14 IN VARCHAR2,
987 x_attribute15 IN VARCHAR2,
988 x_attribute16 IN VARCHAR2,
989 x_attribute17 IN VARCHAR2,
990 x_attribute18 IN VARCHAR2,
991 x_attribute19 IN VARCHAR2,
992 x_attribute20 IN VARCHAR2,
993 x_mode IN VARCHAR2
994 ) AS
995 /*
996 || Created By : [email protected]
997 || Created On : 18-JUN-2003
998 || Purpose : Handles the UPDATE DML logic for the table.
999 || Known limitations, enhancements or remarks :
1000 || Change History :
1001 || Who When What
1002 || (reverse chronological order - newest change first)
1003 */
1004 x_last_update_date DATE ;
1005 x_last_updated_by NUMBER;
1006 x_last_update_login NUMBER;
1007 x_request_id NUMBER;
1008 x_program_id NUMBER;
1009 x_program_application_id NUMBER;
1010 x_program_update_date DATE;
1011
1012 BEGIN
1013
1014 x_last_update_date := SYSDATE;
1015 IF (X_MODE = 'I') THEN
1016 x_last_updated_by := 1;
1017 x_last_update_login := 0;
1018 ELSIF (x_mode = 'R') THEN
1019 x_last_updated_by := fnd_global.user_id;
1020 IF x_last_updated_by IS NULL THEN
1021 x_last_updated_by := -1;
1022 END IF;
1023 x_last_update_login := fnd_global.login_id;
1024 IF (x_last_update_login IS NULL) THEN
1025 x_last_update_login := -1;
1026 END IF;
1027 ELSE
1028 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
1029 fnd_message.set_token ('ROUTINE', 'IGS_FI_LOCKBOX_INTS_PKG.UPDATE_ROW');
1030 igs_ge_msg_stack.add;
1031 app_exception.raise_exception;
1032 END IF;
1033
1034 before_dml(
1035 p_action => 'UPDATE',
1036 x_rowid => x_rowid,
1037 x_lockbox_interface_id => x_lockbox_interface_id,
1038 x_record_identifier_cd => x_record_identifier_cd,
1039 x_record_status => x_record_status,
1040 x_deposit_date => x_deposit_date,
1041 x_transmission_record_count => x_transmission_record_count,
1042 x_transmission_amt => x_transmission_amt,
1043 x_lockbox_name => x_lockbox_name,
1044 x_lockbox_batch_count => x_lockbox_batch_count,
1045 x_lockbox_record_count => x_lockbox_record_count,
1046 x_lockbox_amt => x_lockbox_amt,
1047 x_batch_name => x_batch_name,
1048 x_batch_amt => x_batch_amt,
1049 x_batch_record_count => x_batch_record_count,
1050 x_item_number => x_item_number,
1051 x_receipt_amt => x_receipt_amt,
1052 x_check_cd => x_check_cd,
1053 x_party_number => x_party_number,
1054 x_payer_name => x_payer_name,
1055 x_charge_cd1 => x_charge_cd1,
1056 x_charge_cd2 => x_charge_cd2,
1057 x_charge_cd3 => x_charge_cd3,
1058 x_charge_cd4 => x_charge_cd4,
1059 x_charge_cd5 => x_charge_cd5,
1063 x_applied_amt1 => x_applied_amt1,
1060 x_charge_cd6 => x_charge_cd6,
1061 x_charge_cd7 => x_charge_cd7,
1062 x_charge_cd8 => x_charge_cd8,
1064 x_applied_amt2 => x_applied_amt2,
1065 x_applied_amt3 => x_applied_amt3,
1066 x_applied_amt4 => x_applied_amt4,
1067 x_applied_amt5 => x_applied_amt5,
1068 x_applied_amt6 => x_applied_amt6,
1069 x_applied_amt7 => x_applied_amt7,
1070 x_applied_amt8 => x_applied_amt8,
1071 x_credit_type_cd => x_credit_type_cd,
1072 x_fee_cal_instance_cd => x_fee_cal_instance_cd,
1073 x_adm_application_id => x_adm_application_id,
1074 x_attribute_category => x_attribute_category,
1075 x_attribute1 => x_attribute1,
1076 x_attribute2 => x_attribute2,
1077 x_attribute3 => x_attribute3,
1078 x_attribute4 => x_attribute4,
1079 x_attribute5 => x_attribute5,
1080 x_attribute6 => x_attribute6,
1081 x_attribute7 => x_attribute7,
1082 x_attribute8 => x_attribute8,
1083 x_attribute9 => x_attribute9,
1084 x_attribute10 => x_attribute10,
1085 x_attribute11 => x_attribute11,
1086 x_attribute12 => x_attribute12,
1087 x_attribute13 => x_attribute13,
1088 x_attribute14 => x_attribute14,
1089 x_attribute15 => x_attribute15,
1090 x_attribute16 => x_attribute16,
1091 x_attribute17 => x_attribute17,
1092 x_attribute18 => x_attribute18,
1093 x_attribute19 => x_attribute19,
1094 x_attribute20 => x_attribute20,
1095 x_creation_date => x_last_update_date,
1096 x_created_by => x_last_updated_by,
1097 x_last_update_date => x_last_update_date,
1098 x_last_updated_by => x_last_updated_by,
1099 x_last_update_login => x_last_update_login
1100 );
1101
1102 IF (x_mode = 'R') THEN
1103 x_request_id := fnd_global.conc_request_id;
1104 x_program_id := fnd_global.conc_program_id;
1105 x_program_application_id := fnd_global.prog_appl_id;
1106 IF (x_request_id = -1) THEN
1107 x_request_id := old_references.request_id;
1108 x_program_id := old_references.program_id;
1109 x_program_application_id := old_references.program_application_id;
1110 x_program_update_date := old_references.program_update_date;
1111 ELSE
1112 x_program_update_date := SYSDATE;
1113 END IF;
1114 END IF;
1115
1116 UPDATE igs_fi_lockbox_ints
1124 lockbox_batch_count = new_references.lockbox_batch_count,
1117 SET
1118 record_identifier_cd = new_references.record_identifier_cd,
1119 record_status = new_references.record_status,
1120 deposit_date = new_references.deposit_date,
1121 transmission_record_count = new_references.transmission_record_count,
1122 transmission_amt = new_references.transmission_amt,
1123 lockbox_name = new_references.lockbox_name,
1125 lockbox_record_count = new_references.lockbox_record_count,
1126 lockbox_amt = new_references.lockbox_amt,
1127 batch_name = new_references.batch_name,
1128 batch_amt = new_references.batch_amt,
1129 batch_record_count = new_references.batch_record_count,
1130 item_number = new_references.item_number,
1131 receipt_amt = new_references.receipt_amt,
1132 check_cd = new_references.check_cd,
1133 party_number = new_references.party_number,
1134 payer_name = new_references.payer_name,
1135 charge_cd1 = new_references.charge_cd1,
1136 charge_cd2 = new_references.charge_cd2,
1137 charge_cd3 = new_references.charge_cd3,
1138 charge_cd4 = new_references.charge_cd4,
1142 charge_cd8 = new_references.charge_cd8,
1139 charge_cd5 = new_references.charge_cd5,
1140 charge_cd6 = new_references.charge_cd6,
1141 charge_cd7 = new_references.charge_cd7,
1143 applied_amt1 = new_references.applied_amt1,
1144 applied_amt2 = new_references.applied_amt2,
1145 applied_amt3 = new_references.applied_amt3,
1146 applied_amt4 = new_references.applied_amt4,
1150 applied_amt8 = new_references.applied_amt8,
1147 applied_amt5 = new_references.applied_amt5,
1148 applied_amt6 = new_references.applied_amt6,
1149 applied_amt7 = new_references.applied_amt7,
1151 credit_type_cd = new_references.credit_type_cd,
1152 fee_cal_instance_cd = new_references.fee_cal_instance_cd,
1153 adm_application_id = new_references.adm_application_id,
1154 attribute_category = new_references.attribute_category,
1155 attribute1 = new_references.attribute1,
1156 attribute2 = new_references.attribute2,
1157 attribute3 = new_references.attribute3,
1158 attribute4 = new_references.attribute4,
1159 attribute5 = new_references.attribute5,
1160 attribute6 = new_references.attribute6,
1161 attribute7 = new_references.attribute7,
1162 attribute8 = new_references.attribute8,
1163 attribute9 = new_references.attribute9,
1164 attribute10 = new_references.attribute10,
1165 attribute11 = new_references.attribute11,
1166 attribute12 = new_references.attribute12,
1167 attribute13 = new_references.attribute13,
1168 attribute14 = new_references.attribute14,
1169 attribute15 = new_references.attribute15,
1170 attribute16 = new_references.attribute16,
1171 attribute17 = new_references.attribute17,
1172 attribute18 = new_references.attribute18,
1173 attribute19 = new_references.attribute19,
1174 attribute20 = new_references.attribute20,
1175 last_update_date = x_last_update_date,
1176 last_updated_by = x_last_updated_by,
1177 last_update_login = x_last_update_login ,
1178 request_id = x_request_id,
1179 program_id = x_program_id,
1180 program_application_id = x_program_application_id,
1181 program_update_date = x_program_update_date
1182 WHERE rowid = x_rowid;
1183
1184 IF (SQL%NOTFOUND) THEN
1185 RAISE NO_DATA_FOUND;
1186 END IF;
1187
1188 END update_row;
1189
1190
1191 PROCEDURE add_row (
1192 x_rowid IN OUT NOCOPY VARCHAR2,
1193 x_lockbox_interface_id IN OUT NOCOPY NUMBER,
1194 x_record_identifier_cd IN VARCHAR2,
1195 x_record_status IN VARCHAR2,
1196 x_deposit_date IN DATE,
1197 x_transmission_record_count IN NUMBER,
1198 x_transmission_amt IN NUMBER,
1202 x_lockbox_amt IN NUMBER,
1199 x_lockbox_name IN VARCHAR2,
1200 x_lockbox_batch_count IN NUMBER,
1201 x_lockbox_record_count IN NUMBER,
1203 x_batch_name IN VARCHAR2,
1204 x_batch_amt IN NUMBER,
1205 x_batch_record_count IN NUMBER,
1206 x_item_number IN NUMBER,
1210 x_payer_name IN VARCHAR2,
1207 x_receipt_amt IN NUMBER,
1208 x_check_cd IN VARCHAR2,
1209 x_party_number IN VARCHAR2,
1211 x_charge_cd1 IN VARCHAR2,
1212 x_charge_cd2 IN VARCHAR2,
1216 x_charge_cd6 IN VARCHAR2,
1213 x_charge_cd3 IN VARCHAR2,
1214 x_charge_cd4 IN VARCHAR2,
1215 x_charge_cd5 IN VARCHAR2,
1217 x_charge_cd7 IN VARCHAR2,
1218 x_charge_cd8 IN VARCHAR2,
1219 x_applied_amt1 IN NUMBER,
1220 x_applied_amt2 IN NUMBER,
1221 x_applied_amt3 IN NUMBER,
1222 x_applied_amt4 IN NUMBER,
1223 x_applied_amt5 IN NUMBER,
1224 x_applied_amt6 IN NUMBER,
1225 x_applied_amt7 IN NUMBER,
1226 x_applied_amt8 IN NUMBER,
1227 x_credit_type_cd IN VARCHAR2,
1228 x_fee_cal_instance_cd IN VARCHAR2,
1229 x_adm_application_id IN NUMBER,
1230 x_attribute_category IN VARCHAR2,
1231 x_attribute1 IN VARCHAR2,
1232 x_attribute2 IN VARCHAR2,
1233 x_attribute3 IN VARCHAR2,
1234 x_attribute4 IN VARCHAR2,
1235 x_attribute5 IN VARCHAR2,
1236 x_attribute6 IN VARCHAR2,
1237 x_attribute7 IN VARCHAR2,
1238 x_attribute8 IN VARCHAR2,
1239 x_attribute9 IN VARCHAR2,
1240 x_attribute10 IN VARCHAR2,
1241 x_attribute11 IN VARCHAR2,
1242 x_attribute12 IN VARCHAR2,
1243 x_attribute13 IN VARCHAR2,
1244 x_attribute14 IN VARCHAR2,
1245 x_attribute15 IN VARCHAR2,
1246 x_attribute16 IN VARCHAR2,
1247 x_attribute17 IN VARCHAR2,
1248 x_attribute18 IN VARCHAR2,
1249 x_attribute19 IN VARCHAR2,
1250 x_attribute20 IN VARCHAR2,
1251 x_mode IN VARCHAR2
1252 ) AS
1253 /*
1254 || Created By : [email protected]
1255 || Created On : 18-JUN-2003
1256 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
1257 || Known limitations, enhancements or remarks :
1258 || Change History :
1259 || Who When What
1260 || (reverse chronological order - newest change first)
1261 */
1262 CURSOR c1 IS
1263 SELECT rowid
1264 FROM igs_fi_lockbox_ints
1265 WHERE lockbox_interface_id = x_lockbox_interface_id;
1266
1267 BEGIN
1268
1269 OPEN c1;
1270 FETCH c1 INTO x_rowid;
1271 IF (c1%NOTFOUND) THEN
1272 CLOSE c1;
1273
1274 insert_row (
1275 x_rowid,
1276 x_lockbox_interface_id,
1277 x_record_identifier_cd,
1278 x_record_status,
1279 x_deposit_date,
1280 x_transmission_record_count,
1281 x_transmission_amt,
1282 x_lockbox_name,
1283 x_lockbox_batch_count,
1284 x_lockbox_record_count,
1285 x_lockbox_amt,
1286 x_batch_name,
1287 x_batch_amt,
1288 x_batch_record_count,
1289 x_item_number,
1290 x_receipt_amt,
1291 x_check_cd,
1292 x_party_number,
1293 x_payer_name,
1294 x_charge_cd1,
1295 x_charge_cd2,
1296 x_charge_cd3,
1297 x_charge_cd4,
1298 x_charge_cd5,
1299 x_charge_cd6,
1300 x_charge_cd7,
1301 x_charge_cd8,
1302 x_applied_amt1,
1303 x_applied_amt2,
1304 x_applied_amt3,
1305 x_applied_amt4,
1306 x_applied_amt5,
1307 x_applied_amt6,
1308 x_applied_amt7,
1309 x_applied_amt8,
1310 x_credit_type_cd,
1311 x_fee_cal_instance_cd,
1312 x_adm_application_id,
1313 x_attribute_category,
1314 x_attribute1,
1315 x_attribute2,
1316 x_attribute3,
1317 x_attribute4,
1318 x_attribute5,
1319 x_attribute6,
1320 x_attribute7,
1321 x_attribute8,
1322 x_attribute9,
1323 x_attribute10,
1324 x_attribute11,
1325 x_attribute12,
1326 x_attribute13,
1327 x_attribute14,
1328 x_attribute15,
1329 x_attribute16,
1330 x_attribute17,
1331 x_attribute18,
1332 x_attribute19,
1333 x_attribute20,
1334 x_mode
1335 );
1336 RETURN;
1337 END IF;
1338 CLOSE c1;
1339
1340 update_row (
1341 x_rowid,
1342 x_lockbox_interface_id,
1343 x_record_identifier_cd,
1344 x_record_status,
1345 x_deposit_date,
1346 x_transmission_record_count,
1347 x_transmission_amt,
1348 x_lockbox_name,
1349 x_lockbox_batch_count,
1350 x_lockbox_record_count,
1351 x_lockbox_amt,
1352 x_batch_name,
1353 x_batch_amt,
1354 x_batch_record_count,
1355 x_item_number,
1356 x_receipt_amt,
1357 x_check_cd,
1358 x_party_number,
1359 x_payer_name,
1360 x_charge_cd1,
1361 x_charge_cd2,
1362 x_charge_cd3,
1363 x_charge_cd4,
1364 x_charge_cd5,
1365 x_charge_cd6,
1366 x_charge_cd7,
1367 x_charge_cd8,
1368 x_applied_amt1,
1369 x_applied_amt2,
1370 x_applied_amt3,
1371 x_applied_amt4,
1372 x_applied_amt5,
1373 x_applied_amt6,
1374 x_applied_amt7,
1375 x_applied_amt8,
1376 x_credit_type_cd,
1377 x_fee_cal_instance_cd,
1378 x_adm_application_id,
1379 x_attribute_category,
1380 x_attribute1,
1381 x_attribute2,
1382 x_attribute3,
1383 x_attribute4,
1384 x_attribute5,
1385 x_attribute6,
1386 x_attribute7,
1387 x_attribute8,
1388 x_attribute9,
1389 x_attribute10,
1390 x_attribute11,
1391 x_attribute12,
1392 x_attribute13,
1393 x_attribute14,
1394 x_attribute15,
1395 x_attribute16,
1396 x_attribute17,
1397 x_attribute18,
1398 x_attribute19,
1399 x_attribute20,
1400 x_mode
1401 );
1402
1403 END add_row;
1404
1405
1406 PROCEDURE delete_row (
1407 x_rowid IN VARCHAR2
1408 ) AS
1409 /*
1410 || Created By : [email protected]
1411 || Created On : 18-JUN-2003
1412 || Purpose : Handles the DELETE DML logic for the table.
1413 || Known limitations, enhancements or remarks :
1414 || Change History :
1415 || Who When What
1416 || (reverse chronological order - newest change first)
1417 */
1418 BEGIN
1419
1420 before_dml (
1421 p_action => 'DELETE',
1422 x_rowid => x_rowid
1423 );
1424
1425 DELETE FROM igs_fi_lockbox_ints
1426 WHERE rowid = x_rowid;
1427
1428 IF (SQL%NOTFOUND) THEN
1429 RAISE NO_DATA_FOUND;
1430 END IF;
1431
1432 END delete_row;
1433
1434
1435 END igs_fi_lockbox_ints_pkg;