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