1 PACKAGE BODY igf_sp_std_unit_pkg AS
2 /* $Header: IGFPI07B.pls 115.2 2003/03/19 08:50:47 smadathi noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igf_sp_std_unit_all%ROWTYPE;
6 new_references igf_sp_std_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_std_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.unit_cd,
90 new_references.fee_cls_prg_id,
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_std_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_std_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_unit_cd IN VARCHAR2,
179 x_fee_cls_prg_id IN NUMBER,
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_std_unit_all
194 WHERE unit_cd = x_unit_cd
195 AND fee_cls_prg_id = x_fee_cls_prg_id
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_std_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_std_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_STDU_SDFCP_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_std_prg;
249
250 PROCEDURE get_fk_igs_ps_unit_ver (
251 x_unit_cd IN VARCHAR2,
252 x_version_number IN NUMBER
253 ) AS
254 /*
255 || Created By :
256 || Created On : 28-DEC-2001
257 || Purpose : Validates the Foreign Keys for the table.
258 || Known limitations, enhancements or remarks :
259 || Change History :
260 || Who When What
261 || (reverse chronological order - newest change first)
262 */
263 CURSOR cur_rowid IS
264 SELECT rowid
265 FROM igf_sp_STD_unit_all
266 WHERE ((unit_cd = x_unit_cd
267 AND version_number = x_version_number));
268
269 lv_rowid cur_rowid%RowType;
270
271 BEGIN
272
273 OPEN cur_rowid;
274 FETCH cur_rowid INTO lv_rowid;
275 IF (cur_rowid%FOUND) THEN
276 CLOSE cur_rowid;
277 fnd_message.set_name ('IGF', 'IGF_SP_STDU_UV_FK');
278 igs_ge_msg_stack.add;
279 app_exception.raise_exception;
280 RETURN;
281 END IF;
282 CLOSE cur_rowid;
283 END get_fk_igs_ps_unit_ver;
284
285
286 PROCEDURE before_dml (
287 p_action IN VARCHAR2,
288 x_rowid IN VARCHAR2 DEFAULT NULL,
289 x_fee_cls_unit_id IN NUMBER DEFAULT NULL,
290 x_fee_cls_prg_id IN NUMBER DEFAULT NULL,
291 x_unit_cd IN VARCHAR2 DEFAULT NULL,
292 x_version_number IN NUMBER DEFAULT NULL,
293 x_max_amount IN NUMBER DEFAULT NULL,
294 x_creation_date IN DATE DEFAULT NULL,
295 x_created_by IN NUMBER DEFAULT NULL,
296 x_last_update_date IN DATE DEFAULT NULL,
297 x_last_updated_by IN NUMBER DEFAULT NULL,
298 x_last_update_login IN NUMBER DEFAULT NULL
299 ) AS
300 /*
301 || Created By :
302 || Created On : 28-DEC-2001
303 || Purpose : Initialises the columns, Checks Constraints, Calls the
304 || Trigger Handlers for the table, before any DML operation.
305 || Known limitations, enhancements or remarks :
306 || Change History :
307 || Who When What
308 || (reverse chronological order - newest change first)
309 || smadathi 18-FEB-2003 Bug 2473845. Added logic to re initialize l_rowid to null.
310 */
311 BEGIN
312
313 set_column_values (
314 p_action,
315 x_rowid,
316 x_fee_cls_unit_id,
317 x_fee_cls_prg_id,
318 x_unit_cd,
319 x_version_number,
320 x_max_amount,
321 x_creation_date,
322 x_created_by,
323 x_last_update_date,
324 x_last_updated_by,
325 x_last_update_login
326 );
327
328 IF (p_action = 'INSERT') THEN
329 -- Call all the procedures related to Before Insert.
330 IF ( get_pk_for_validation(
331 new_references.fee_cls_unit_id
332 )
333 ) THEN
334 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
335 igs_ge_msg_stack.add;
336 app_exception.raise_exception;
337 END IF;
338 check_uniqueness;
339 check_parent_existance;
340 ELSIF (p_action = 'UPDATE') THEN
341 -- Call all the procedures related to Before Update.
342 check_uniqueness;
343 check_parent_existance;
344 ELSIF (p_action = 'VALIDATE_INSERT') THEN
345 -- Call all the procedures related to Before Insert.
346 IF ( get_pk_for_validation (
347 new_references.fee_cls_unit_id
348 )
349 ) THEN
350 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
351 igs_ge_msg_stack.add;
352 app_exception.raise_exception;
353 END IF;
354 check_uniqueness;
355 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
356 check_uniqueness;
357 END IF;
358 l_rowid := NULL;
359 END before_dml;
360
361
362 PROCEDURE insert_row (
363 x_rowid IN OUT NOCOPY VARCHAR2,
364 x_fee_cls_unit_id IN OUT NOCOPY NUMBER,
365 x_fee_cls_prg_id IN NUMBER,
366 x_unit_cd IN VARCHAR2,
367 x_version_number IN NUMBER,
368 x_max_amount IN NUMBER,
369 x_mode IN VARCHAR2 DEFAULT 'R'
370 ) AS
371 /*
372 || Created By :
373 || Created On : 28-DEC-2001
374 || Purpose : Handles the INSERT DML logic for the table.
375 || Known limitations, enhancements or remarks :
376 || Change History :
377 || Who When What
378 || (reverse chronological order - newest change first)
379 */
380 CURSOR c IS
381 SELECT rowid
382 FROM igf_sp_std_unit_all
383 WHERE fee_cls_unit_id = x_fee_cls_unit_id;
384
385 x_last_update_date DATE;
386 x_last_updated_by NUMBER;
387 x_last_update_login NUMBER;
388
389 BEGIN
390
391 x_last_update_date := SYSDATE;
392 IF (x_mode = 'I') THEN
393 x_last_updated_by := 1;
394 x_last_update_login := 0;
395 ELSIF (x_mode = 'R') THEN
396 x_last_updated_by := fnd_global.user_id;
397 IF (x_last_updated_by IS NULL) THEN
398 x_last_updated_by := -1;
399 END IF;
400 x_last_update_login := fnd_global.login_id;
401 IF (x_last_update_login IS NULL) THEN
402 x_last_update_login := -1;
403 END IF;
404 ELSE
405 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
406 igs_ge_msg_stack.add;
407 app_exception.raise_exception;
408 END IF;
409
410 SELECT igf_sp_std_unit_s.NEXTVAL
411 INTO x_fee_cls_unit_id
412 FROM dual;
413
414 new_references.org_id := igs_ge_gen_003.get_org_id;
415
416 before_dml(
417 p_action => 'INSERT',
418 x_rowid => x_rowid,
419 x_fee_cls_unit_id => x_fee_cls_unit_id,
420 x_fee_cls_prg_id => x_fee_cls_prg_id,
421 x_unit_cd => x_unit_cd,
422 x_version_number => x_version_number,
423 x_max_amount => x_max_amount,
424 x_creation_date => x_last_update_date,
425 x_created_by => x_last_updated_by,
426 x_last_update_date => x_last_update_date,
427 x_last_updated_by => x_last_updated_by,
428 x_last_update_login => x_last_update_login
429 );
430
431 INSERT INTO igf_sp_std_unit_all (
432 fee_cls_unit_id,
433 fee_cls_prg_id,
434 unit_cd,
435 version_number,
436 max_amount,
437 org_id,
438 creation_date,
439 created_by,
440 last_update_date,
441 last_updated_by,
442 last_update_login
443 ) VALUES (
444 new_references.fee_cls_unit_id,
445 new_references.fee_cls_prg_id,
446 new_references.unit_cd,
447 new_references.version_number,
448 new_references.max_amount,
449 new_references.org_id,
450 x_last_update_date,
451 x_last_updated_by,
452 x_last_update_date,
453 x_last_updated_by,
454 x_last_update_login
455 );
456
457 OPEN c;
458 FETCH c INTO x_rowid;
459 IF (c%NOTFOUND) THEN
460 CLOSE c;
461 RAISE NO_DATA_FOUND;
462 END IF;
463 CLOSE c;
464
465 END insert_row;
466
467
468 PROCEDURE lock_row (
469 x_rowid IN VARCHAR2,
470 x_fee_cls_unit_id IN NUMBER,
471 x_fee_cls_prg_id IN NUMBER,
472 x_unit_cd IN VARCHAR2,
473 x_version_number IN NUMBER,
474 x_max_amount IN NUMBER
475 ) AS
476 /*
477 || Created By :
478 || Created On : 28-DEC-2001
479 || Purpose : Handles the LOCK mechanism for the table.
480 || Known limitations, enhancements or remarks :
481 || Change History :
482 || Who When What
483 || (reverse chronological order - newest change first)
484 */
485 CURSOR c1 IS
486 SELECT
487 fee_cls_prg_id,
488 unit_cd,
489 version_number,
490 max_amount
491 FROM igf_sp_std_unit_all
492 WHERE rowid = x_rowid
493 FOR UPDATE NOWAIT;
494
495 tlinfo c1%ROWTYPE;
496
497 BEGIN
498
499 OPEN c1;
500 FETCH c1 INTO tlinfo;
501 IF (c1%notfound) THEN
502 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
503 igs_ge_msg_stack.add;
504 CLOSE c1;
505 app_exception.raise_exception;
506 RETURN;
507 END IF;
508 CLOSE c1;
509
510 IF (
511 (tlinfo.fee_cls_prg_id = x_fee_cls_prg_id)
512 AND (tlinfo.unit_cd = x_unit_cd)
513 AND (tlinfo.version_number = x_version_number)
514 AND (tlinfo.max_amount = x_max_amount)
515 ) THEN
516 NULL;
517 ELSE
518 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
519 igs_ge_msg_stack.add;
520 app_exception.raise_exception;
521 END IF;
522
523 RETURN;
524
525 END lock_row;
526
527
528 PROCEDURE update_row (
529 x_rowid IN VARCHAR2,
530 x_fee_cls_unit_id IN NUMBER,
531 x_fee_cls_prg_id IN NUMBER,
532 x_unit_cd IN VARCHAR2,
533 x_version_number IN NUMBER,
534 x_max_amount IN NUMBER,
535 x_mode IN VARCHAR2 DEFAULT 'R'
536 ) AS
537 /*
538 || Created By :
539 || Created On : 28-DEC-2001
540 || Purpose : Handles the UPDATE DML logic for the table.
541 || Known limitations, enhancements or remarks :
542 || Change History :
543 || Who When What
544 || (reverse chronological order - newest change first)
545 */
546 x_last_update_date DATE ;
547 x_last_updated_by NUMBER;
548 x_last_update_login NUMBER;
549
550 BEGIN
551
552 x_last_update_date := SYSDATE;
553 IF (X_MODE = 'I') THEN
554 x_last_updated_by := 1;
555 x_last_update_login := 0;
556 ELSIF (x_mode = 'R') THEN
557 x_last_updated_by := fnd_global.user_id;
558 IF x_last_updated_by IS NULL THEN
559 x_last_updated_by := -1;
560 END IF;
561 x_last_update_login := fnd_global.login_id;
562 IF (x_last_update_login IS NULL) THEN
563 x_last_update_login := -1;
564 END IF;
565 ELSE
566 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
567 igs_ge_msg_stack.add;
568 app_exception.raise_exception;
569 END IF;
570
571 before_dml(
572 p_action => 'UPDATE',
573 x_rowid => x_rowid,
574 x_fee_cls_unit_id => x_fee_cls_unit_id,
575 x_fee_cls_prg_id => x_fee_cls_prg_id,
576 x_unit_cd => x_unit_cd,
577 x_version_number => x_version_number,
578 x_max_amount => x_max_amount,
579 x_creation_date => x_last_update_date,
580 x_created_by => x_last_updated_by,
581 x_last_update_date => x_last_update_date,
582 x_last_updated_by => x_last_updated_by,
583 x_last_update_login => x_last_update_login
584 );
585
586 UPDATE igf_sp_std_unit_all
587 SET
588 fee_cls_prg_id = new_references.fee_cls_prg_id,
589 unit_cd = new_references.unit_cd,
590 version_number = new_references.version_number,
591 max_amount = new_references.max_amount,
592 last_update_date = x_last_update_date,
593 last_updated_by = x_last_updated_by,
594 last_update_login = x_last_update_login
595 WHERE rowid = x_rowid;
596
597 IF (SQL%NOTFOUND) THEN
598 RAISE NO_DATA_FOUND;
599 END IF;
600
601 END update_row;
602
603
604 PROCEDURE add_row (
605 x_rowid IN OUT NOCOPY VARCHAR2,
606 x_fee_cls_unit_id IN OUT NOCOPY NUMBER,
607 x_fee_cls_prg_id IN NUMBER,
608 x_unit_cd IN VARCHAR2,
609 x_version_number IN NUMBER,
610 x_max_amount IN NUMBER,
611 x_mode IN VARCHAR2 DEFAULT 'R'
612 ) AS
613 /*
614 || Created By :
615 || Created On : 28-DEC-2001
616 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
617 || Known limitations, enhancements or remarks :
618 || Change History :
619 || Who When What
620 || (reverse chronological order - newest change first)
621 */
622 CURSOR c1 IS
623 SELECT rowid
624 FROM igf_sp_std_unit_all
625 WHERE fee_cls_unit_id = x_fee_cls_unit_id;
626
627 BEGIN
628
629 OPEN c1;
630 FETCH c1 INTO x_rowid;
631 IF (c1%NOTFOUND) THEN
632 CLOSE c1;
633
634 insert_row (
635 x_rowid,
636 x_fee_cls_unit_id,
637 x_fee_cls_prg_id,
638 x_unit_cd,
639 x_version_number,
640 x_max_amount,
641 x_mode
642 );
643 RETURN;
644 END IF;
645 CLOSE c1;
646
647 update_row (
648 x_rowid,
649 x_fee_cls_unit_id,
650 x_fee_cls_prg_id,
651 x_unit_cd,
652 x_version_number,
653 x_max_amount,
654 x_mode
655 );
656
657 END add_row;
658
659
660 PROCEDURE delete_row (
661 x_rowid IN VARCHAR2
662 ) AS
663 /*
664 || Created By :
665 || Created On : 28-DEC-2001
666 || Purpose : Handles the DELETE DML logic for the table.
667 || Known limitations, enhancements or remarks :
668 || Change History :
669 || Who When What
670 || (reverse chronological order - newest change first)
671 */
672 BEGIN
673
674 before_dml (
675 p_action => 'DELETE',
676 x_rowid => x_rowid
677 );
678
679 DELETE FROM igf_sp_std_unit_all
680 WHERE rowid = x_rowid;
681
682 IF (SQL%NOTFOUND) THEN
683 RAISE NO_DATA_FOUND;
684 END IF;
685
686 END delete_row;
687
688
689 END igf_sp_std_unit_pkg;
[Home] [Help]