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