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