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