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