1 PACKAGE BODY igs_pr_org_stat_pkg AS
2 /* $Header: IGSQI36B.pls 115.4 2002/11/29 03:23:57 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pr_org_stat%ROWTYPE;
6 new_references igs_pr_org_stat%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_stat_type IN VARCHAR2,
12 x_org_unit_cd IN VARCHAR2,
13 x_display_order IN NUMBER,
14 x_timeframe IN VARCHAR2,
15 x_standard_ind IN VARCHAR2,
16 x_display_ind IN VARCHAR2,
17 x_include_standard_ind IN VARCHAR2,
18 x_include_local_ind IN VARCHAR2,
19 x_include_other_ind IN VARCHAR2,
20 x_creation_date IN DATE,
21 x_created_by IN NUMBER,
22 x_last_update_date IN DATE,
23 x_last_updated_by IN NUMBER,
24 x_last_update_login IN NUMBER
25 ) AS
26 /*
27 || Created By : nbehera
28 || Created On : 02-NOV-2001
29 || Purpose : Initialises the Old and New references for the columns of the table.
30 || Known limitations, enhancements or remarks :
31 || Change History :
32 || Who When What
33 || (reverse chronological order - newest change first)
34 */
35
36 CURSOR cur_old_ref_values IS
37 SELECT *
38 FROM igs_pr_org_stat
39 WHERE rowid = x_rowid;
40
41 BEGIN
42
43 l_rowid := x_rowid;
44
45 -- Code for setting the Old and New Reference Values.
46 -- Populate Old Values.
47 OPEN cur_old_ref_values;
48 FETCH cur_old_ref_values INTO old_references;
49 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
50 CLOSE cur_old_ref_values;
51 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
52 igs_ge_msg_stack.add;
53 app_exception.raise_exception;
54 RETURN;
55 END IF;
56 CLOSE cur_old_ref_values;
57
58 -- Populate New Values.
59 new_references.stat_type := x_stat_type;
60 new_references.org_unit_cd := x_org_unit_cd;
61 new_references.display_order := x_display_order;
62 new_references.timeframe := x_timeframe;
63 new_references.standard_ind := x_standard_ind;
64 new_references.display_ind := x_display_ind;
65 new_references.include_standard_ind := x_include_standard_ind;
66 new_references.include_local_ind := x_include_local_ind;
67 new_references.include_other_ind := x_include_other_ind;
68
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80
81 END set_column_values;
82
83
84 PROCEDURE check_uniqueness AS
85 /*
86 || Created By : nbehera
87 || Created On : 02-NOV-2001
88 || Purpose : Handles the Unique Constraint logic defined for the columns.
89 || Known limitations, enhancements or remarks :
90 || Change History :
91 || Who When What
92 || (reverse chronological order - newest change first)
93 */
94 BEGIN
95
96 IF ( get_uk_for_validation (
97 new_references.org_unit_cd,
98 new_references.display_order
99 )
100 ) THEN
101 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
102 igs_ge_msg_stack.add;
103 app_exception.raise_exception;
104 END IF;
105
106 END check_uniqueness;
107
108
109 PROCEDURE check_parent_existance AS
110 /*
111 || Created By : nbehera
112 || Created On : 02-NOV-2001
113 || Purpose : Checks for the existance of Parent records.
114 || Known limitations, enhancements or remarks :
115 || Change History :
116 || Who When What
117 || (reverse chronological order - newest change first)
118 */
119 BEGIN
120
121 IF (((old_references.stat_type = new_references.stat_type)) OR
122 ((new_references.stat_type IS NULL))) THEN
123 NULL;
124 ELSIF NOT igs_pr_stat_type_pkg.get_pk_for_validation (
125 new_references.stat_type
126 ) THEN
127 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
128 igs_ge_msg_stack.add;
129 app_exception.raise_exception;
130 ELSIF NOT igs_or_unit_pkg.get_pk_for_str_validation (
131 new_references.org_unit_cd
132 ) THEN
133 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
134 igs_ge_msg_stack.add;
135 app_exception.raise_exception;
136 END IF;
137
138 END check_parent_existance;
139
140
141 PROCEDURE check_child_existance IS
142 /*
143 || Created By : nbehera
144 || Created On : 02-NOV-2001
145 || Purpose : Checks for the existance of Child records.
146 || Known limitations, enhancements or remarks :
147 || Change History :
148 || Who When What
149 || (reverse chronological order - newest change first)
150 */
151 BEGIN
152
153 igs_pr_org_stat_ref_pkg.get_fk_igs_pr_org_stat (
154 old_references.org_unit_cd,
155 old_references.stat_type
156 );
157
158 END check_child_existance;
159
160
161 FUNCTION get_pk_for_validation (
162 x_org_unit_cd IN VARCHAR2,
163 x_stat_type IN VARCHAR2
164 ) RETURN BOOLEAN AS
165 /*
166 || Created By : nbehera
167 || Created On : 02-NOV-2001
168 || Purpose : Validates the Primary Key of the table.
169 || Known limitations, enhancements or remarks :
170 || Change History :
171 || Who When What
172 || (reverse chronological order - newest change first)
173 */
174 CURSOR cur_rowid IS
175 SELECT rowid
176 FROM igs_pr_org_stat
177 WHERE org_unit_cd = x_org_unit_cd
178 AND stat_type = x_stat_type
179 FOR UPDATE NOWAIT;
180
181 lv_rowid cur_rowid%RowType;
182
183 BEGIN
184
185 OPEN cur_rowid;
186 FETCH cur_rowid INTO lv_rowid;
187 IF (cur_rowid%FOUND) THEN
188 CLOSE cur_rowid;
189 RETURN(TRUE);
190 ELSE
191 CLOSE cur_rowid;
192 RETURN(FALSE);
193 END IF;
194
195 END get_pk_for_validation;
196
197
198 FUNCTION get_uk_for_validation (
199 x_org_unit_cd IN VARCHAR2,
200 x_display_order IN NUMBER
201 ) RETURN BOOLEAN AS
202 /*
203 || Created By : nbehera
204 || Created On : 13-NOV-2001
205 || Purpose : Validates the Unique Keys of the table.
206 || Known limitations, enhancements or remarks :
207 || Change History :
208 || Who When What
209 || (reverse chronological order - newest change first)
210 */
211 CURSOR cur_rowid IS
212 SELECT rowid
213 FROM igs_pr_org_stat
214 WHERE org_unit_cd = x_org_unit_cd
215 AND display_order = x_display_order
216 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
217
218 lv_rowid cur_rowid%RowType;
219
220 BEGIN
221
222 OPEN cur_rowid;
223 FETCH cur_rowid INTO lv_rowid;
224 IF (cur_rowid%FOUND) THEN
225 CLOSE cur_rowid;
226 RETURN (true);
227 ELSE
228 CLOSE cur_rowid;
229 RETURN(FALSE);
230 END IF;
231
232 END get_uk_for_validation ;
233
234
235 PROCEDURE get_fk_igs_pr_stat_type (
236 x_stat_type IN VARCHAR2
237 ) AS
238 /*
239 || Created By : nbehera
240 || Created On : 02-NOV-2001
241 || Purpose : Validates the Foreign Keys for the table.
242 || Known limitations, enhancements or remarks :
243 || Change History :
244 || Who When What
245 || (reverse chronological order - newest change first)
246 */
247 CURSOR cur_rowid IS
248 SELECT rowid
249 FROM igs_pr_org_stat
250 WHERE ((stat_type = x_stat_type));
251
252 lv_rowid cur_rowid%RowType;
253
254 BEGIN
255
256 OPEN cur_rowid;
257 FETCH cur_rowid INTO lv_rowid;
258 IF (cur_rowid%FOUND) THEN
259 CLOSE cur_rowid;
260 fnd_message.set_name ('IGS', 'IGS_PR_ORST_STTY_FK');
261 igs_ge_msg_stack.add;
262 app_exception.raise_exception;
263 RETURN;
264 END IF;
265 CLOSE cur_rowid;
266
267 END get_fk_igs_pr_stat_type;
268
269
270 PROCEDURE before_dml (
271 p_action IN VARCHAR2,
272 x_rowid IN VARCHAR2,
273 x_stat_type IN VARCHAR2,
274 x_org_unit_cd IN VARCHAR2,
275 x_display_order IN NUMBER,
276 x_timeframe IN VARCHAR2,
277 x_standard_ind IN VARCHAR2,
278 x_display_ind IN VARCHAR2,
279 x_include_standard_ind IN VARCHAR2,
280 x_include_local_ind IN VARCHAR2,
281 x_include_other_ind IN VARCHAR2,
282 x_creation_date IN DATE,
283 x_created_by IN NUMBER,
284 x_last_update_date IN DATE,
285 x_last_updated_by IN NUMBER,
286 x_last_update_login IN NUMBER
287 ) AS
288 /*
289 || Created By : nbehera
290 || Created On : 02-NOV-2001
291 || Purpose : Initialises the columns, Checks Constraints, Calls the
292 || Trigger Handlers for the table, before any DML operation.
293 || Known limitations, enhancements or remarks :
294 || Change History :
295 || Who When What
296 || (reverse chronological order - newest change first)
297 */
298 BEGIN
299
300 set_column_values (
301 p_action,
302 x_rowid,
303 x_stat_type,
304 x_org_unit_cd,
305 x_display_order,
306 x_timeframe,
307 x_standard_ind,
308 x_display_ind,
309 x_include_standard_ind,
310 x_include_local_ind,
311 x_include_other_ind,
312 x_creation_date,
313 x_created_by,
314 x_last_update_date,
315 x_last_updated_by,
316 x_last_update_login
317 );
318
319 IF (p_action = 'INSERT') THEN
320 -- Call all the procedures related to Before Insert.
321 IF ( get_pk_for_validation(
322 new_references.org_unit_cd,
323 new_references.stat_type
324 )
325 ) THEN
326 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
327 igs_ge_msg_stack.add;
328 app_exception.raise_exception;
329 END IF;
330 check_uniqueness;
331 check_parent_existance;
332 ELSIF (p_action = 'UPDATE') THEN
333 -- Call all the procedures related to Before Update.
334 check_uniqueness;
335 check_parent_existance;
336 ELSIF (p_action = 'DELETE') THEN
337 -- Call all the procedures related to Before Delete.
338 check_child_existance;
339 ELSIF (p_action = 'VALIDATE_INSERT') THEN
340 -- Call all the procedures related to Before Insert.
341 IF ( get_pk_for_validation (
342 new_references.org_unit_cd,
343 new_references.stat_type
344 )
345 ) THEN
346 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
347 igs_ge_msg_stack.add;
348 app_exception.raise_exception;
349 END IF;
350 check_uniqueness;
351 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
352 check_uniqueness;
353 ELSIF (p_action = 'VALIDATE_DELETE') THEN
354 check_child_existance;
355 END IF;
356
357 END before_dml;
358
359
360 PROCEDURE insert_row (
361 x_rowid IN OUT NOCOPY VARCHAR2,
362 x_stat_type IN VARCHAR2,
363 x_org_unit_cd IN VARCHAR2,
364 x_display_order IN NUMBER,
365 x_timeframe IN VARCHAR2,
366 x_standard_ind IN VARCHAR2,
367 x_display_ind IN VARCHAR2,
368 x_include_standard_ind IN VARCHAR2,
369 x_include_local_ind IN VARCHAR2,
370 x_include_other_ind IN VARCHAR2,
371 x_mode IN VARCHAR2
372 ) AS
373 /*
374 || Created By : nbehera
375 || Created On : 02-NOV-2001
376 || Purpose : Handles the INSERT DML logic for the table.
377 || Known limitations, enhancements or remarks :
378 || Change History :
379 || Who When What
380 || (reverse chronological order - newest change first)
381 */
382 CURSOR c IS
383 SELECT rowid
384 FROM igs_pr_org_stat
385 WHERE org_unit_cd = x_org_unit_cd
386 AND stat_type = x_stat_type;
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 before_dml(
414 p_action => 'INSERT',
415 x_rowid => x_rowid,
416 x_stat_type => x_stat_type,
417 x_org_unit_cd => x_org_unit_cd,
418 x_display_order => x_display_order,
419 x_timeframe => x_timeframe,
420 x_standard_ind => x_standard_ind,
421 x_display_ind => x_display_ind,
422 x_include_standard_ind => x_include_standard_ind,
423 x_include_local_ind => x_include_local_ind,
424 x_include_other_ind => x_include_other_ind,
425 x_creation_date => x_last_update_date,
426 x_created_by => x_last_updated_by,
427 x_last_update_date => x_last_update_date,
428 x_last_updated_by => x_last_updated_by,
429 x_last_update_login => x_last_update_login
430 );
431
432 INSERT INTO igs_pr_org_stat (
433 stat_type,
434 org_unit_cd,
435 display_order,
436 timeframe,
437 standard_ind,
438 display_ind,
439 include_standard_ind,
440 include_local_ind,
441 include_other_ind,
442 creation_date,
443 created_by,
444 last_update_date,
445 last_updated_by,
446 last_update_login
447 ) VALUES (
448 new_references.stat_type,
449 new_references.org_unit_cd,
450 new_references.display_order,
451 new_references.timeframe,
452 new_references.standard_ind,
453 new_references.display_ind,
454 new_references.include_standard_ind,
455 new_references.include_local_ind,
456 new_references.include_other_ind,
457 x_last_update_date,
458 x_last_updated_by,
459 x_last_update_date,
460 x_last_updated_by,
461 x_last_update_login
462 );
463
464 OPEN c;
465 FETCH c INTO x_rowid;
466 IF (c%NOTFOUND) THEN
467 CLOSE c;
468 RAISE NO_DATA_FOUND;
469 END IF;
470 CLOSE c;
471
472 after_dml(x_org_unit_cd);
473 END insert_row;
474
475 PROCEDURE after_dml(
476 x_org_unit_cd IN VARCHAR2) AS
477 /*
478 || Created By : nbehera
479 || Created On : 15-NOV-2001
480 || Purpose : Checks for more than one record with the same indicator
481 || set cannot exist with the same Timeframe or if either has a
482 || Timeframe 'BOTH'.
483 || Known limitations, enhancements or remarks :
484 || Change History :
485 || Who When What
486 || (reverse chronological order - newest change first)
487 || kdande 20-Sep-2002 Removed the references to columns progression_ind and fin_aid_ind
488 || from the c1 and removed the cursors c3, c4 and their usage for Bug# 2560160.
489 */
490
491 CURSOR c1(l_org_unit_cd igs_pr_org_stat.org_unit_cd%TYPE) IS
492 SELECT orst.timeframe,
493 orst.standard_ind
494 FROM igs_pr_org_stat orst
495 WHERE orst.org_unit_cd = l_org_unit_cd
496 AND ( orst.standard_ind = 'Y');
497
498 CURSOR c2 IS
499 SELECT orst.timeframe
500 FROM igs_pr_org_stat orst
501 WHERE orst.standard_ind = 'Y'
502 AND orst.org_unit_cd = x_org_unit_cd
503 GROUP BY orst.timeframe
504 HAVING COUNT(orst.timeframe) > 1;
505
506 l_c1_rec c1%ROWTYPE;
507 l_std_ind VARCHAR2(1) :='N';
508 l_both_std_flag VARCHAR2(1) :='N';
509 dummy VARCHAR2(50);
510 BEGIN
511 FOR l_c1_rec IN c1(x_org_unit_cd) LOOP
512 IF l_c1_rec.standard_ind = 'Y' THEN
513 IF l_std_ind = 'N' THEN
514 l_std_ind := 'Y';
515 IF l_c1_rec.timeframe = 'BOTH' THEN
516 l_both_std_flag := 'Y';
517 END IF;
518 ELSIF l_std_ind = 'Y'
519 AND (l_c1_rec.timeframe = 'BOTH'
520 OR l_both_std_flag ='Y' ) THEN
521 fnd_message.set_name ('IGS','IGS_PR_STAT_TYPE_IND');
522 FND_MESSAGE.SET_TOKEN('STATTYPE_IND','Standard');
523 igs_ge_msg_stack.add;
524 app_exception.raise_exception;
525 END IF;
526 END IF;
527 END LOOP;
528 OPEN c2;
529 FETCH c2 INTO dummy;
530 IF c2%FOUND THEN
531 fnd_message.set_name ('IGS','IGS_PR_STAT_TYPE_IND');
532 FND_MESSAGE.SET_TOKEN('STATTYPE_IND','Standard');
533 igs_ge_msg_stack.add;
534 app_exception.raise_exception;
535 END IF;
536 CLOSE c2;
537 END after_dml;
538
539
540 PROCEDURE lock_row (
541 x_rowid IN VARCHAR2,
542 x_stat_type IN VARCHAR2,
543 x_org_unit_cd IN VARCHAR2,
544 x_display_order IN NUMBER,
545 x_timeframe IN VARCHAR2,
546 x_standard_ind IN VARCHAR2,
547 x_display_ind IN VARCHAR2,
548 x_include_standard_ind IN VARCHAR2,
549 x_include_local_ind IN VARCHAR2,
550 x_include_other_ind IN VARCHAR2
551 ) AS
552 /*
553 || Created By : nbehera
554 || Created On : 02-NOV-2001
555 || Purpose : Handles the LOCK mechanism for the table.
556 || Known limitations, enhancements or remarks :
557 || Change History :
558 || Who When What
559 || (reverse chronological order - newest change first)
560 || kdande 23-Sep-2002 Obsoleted the columns progression_ind, fin_aid_ind
561 || as per bug# 2560160 and removed the code from locking.
562 */
563 CURSOR c1 IS
564 SELECT
565 display_order,
566 timeframe,
567 standard_ind,
568 display_ind,
569 include_standard_ind,
570 include_local_ind,
571 include_other_ind
572 FROM igs_pr_org_stat
573 WHERE rowid = x_rowid
574 FOR UPDATE NOWAIT;
575
576 tlinfo c1%ROWTYPE;
577
578 BEGIN
579
580 OPEN c1;
581 FETCH c1 INTO tlinfo;
582 IF (c1%notfound) THEN
583 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
584 igs_ge_msg_stack.add;
585 CLOSE c1;
586 app_exception.raise_exception;
587 RETURN;
588 END IF;
589 CLOSE c1;
590
591 IF (
592 (tlinfo.display_order = x_display_order)
593 AND (tlinfo.timeframe = x_timeframe)
594 AND (tlinfo.standard_ind = x_standard_ind)
595 AND (tlinfo.display_ind = x_display_ind)
596 AND (tlinfo.include_standard_ind = x_include_standard_ind)
597 AND (tlinfo.include_local_ind = x_include_local_ind)
598 AND (tlinfo.include_other_ind = x_include_other_ind)
599 ) THEN
600 NULL;
601 ELSE
602 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
603 igs_ge_msg_stack.add;
604 app_exception.raise_exception;
605 END IF;
606
607 RETURN;
608
609 END lock_row;
610
611
612 PROCEDURE update_row (
613 x_rowid IN VARCHAR2,
614 x_stat_type IN VARCHAR2,
615 x_org_unit_cd IN VARCHAR2,
616 x_display_order IN NUMBER,
617 x_timeframe IN VARCHAR2,
618 x_standard_ind IN VARCHAR2,
619 x_display_ind IN VARCHAR2,
620 x_include_standard_ind IN VARCHAR2,
621 x_include_local_ind IN VARCHAR2,
622 x_include_other_ind IN VARCHAR2,
623 x_mode IN VARCHAR2
624 ) AS
625 /*
626 || Created By : nbehera
627 || Created On : 02-NOV-2001
628 || Purpose : Handles the UPDATE DML logic for the table.
629 || Known limitations, enhancements or remarks :
630 || Change History :
631 || Who When What
632 || (reverse chronological order - newest change first)
633 */
634 x_last_update_date DATE ;
635 x_last_updated_by NUMBER;
636 x_last_update_login NUMBER;
637
638 BEGIN
639
640 x_last_update_date := SYSDATE;
641 IF (X_MODE = 'I') THEN
642 x_last_updated_by := 1;
643 x_last_update_login := 0;
644 ELSIF (x_mode = 'R') THEN
645 x_last_updated_by := fnd_global.user_id;
646 IF x_last_updated_by IS NULL THEN
647 x_last_updated_by := -1;
648 END IF;
649 x_last_update_login := fnd_global.login_id;
650 IF (x_last_update_login IS NULL) THEN
651 x_last_update_login := -1;
652 END IF;
653 ELSE
654 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
655 igs_ge_msg_stack.add;
656 app_exception.raise_exception;
657 END IF;
658
659 before_dml(
660 p_action => 'UPDATE',
661 x_rowid => x_rowid,
662 x_stat_type => x_stat_type,
663 x_org_unit_cd => x_org_unit_cd,
664 x_display_order => x_display_order,
665 x_timeframe => x_timeframe,
666 x_standard_ind => x_standard_ind,
667 x_display_ind => x_display_ind,
668 x_include_standard_ind => x_include_standard_ind,
669 x_include_local_ind => x_include_local_ind,
670 x_include_other_ind => x_include_other_ind,
671 x_creation_date => x_last_update_date,
672 x_created_by => x_last_updated_by,
673 x_last_update_date => x_last_update_date,
674 x_last_updated_by => x_last_updated_by,
675 x_last_update_login => x_last_update_login
676 );
677
678 UPDATE igs_pr_org_stat
679 SET
680 display_order = new_references.display_order,
681 timeframe = new_references.timeframe,
682 standard_ind = new_references.standard_ind,
683 display_ind = new_references.display_ind,
684 include_standard_ind = new_references.include_standard_ind,
685 include_local_ind = new_references.include_local_ind,
686 include_other_ind = new_references.include_other_ind,
687 last_update_date = x_last_update_date,
688 last_updated_by = x_last_updated_by,
689 last_update_login = x_last_update_login
690 WHERE rowid = x_rowid;
691
692 IF (SQL%NOTFOUND) THEN
693 RAISE NO_DATA_FOUND;
694 END IF;
695 after_dml(x_org_unit_cd);
696 END update_row;
697
698
699 PROCEDURE add_row (
700 x_rowid IN OUT NOCOPY VARCHAR2,
701 x_stat_type IN VARCHAR2,
702 x_org_unit_cd IN VARCHAR2,
703 x_display_order IN NUMBER,
704 x_timeframe IN VARCHAR2,
705 x_standard_ind IN VARCHAR2,
706 x_display_ind IN VARCHAR2,
707 x_include_standard_ind IN VARCHAR2,
708 x_include_local_ind IN VARCHAR2,
709 x_include_other_ind IN VARCHAR2,
710 x_mode IN VARCHAR2
711 ) AS
712 /*
713 || Created By : nbehera
714 || Created On : 02-NOV-2001
715 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
716 || Known limitations, enhancements or remarks :
717 || Change History :
718 || Who When What
719 || (reverse chronological order - newest change first)
720 */
721 CURSOR c1 IS
722 SELECT rowid
723 FROM igs_pr_org_stat
724 WHERE org_unit_cd = x_org_unit_cd
725 AND stat_type = x_stat_type;
726
727 BEGIN
728
729 OPEN c1;
730 FETCH c1 INTO x_rowid;
731 IF (c1%NOTFOUND) THEN
732 CLOSE c1;
733
734 insert_row (
735 x_rowid,
736 x_stat_type,
737 x_org_unit_cd,
738 x_display_order,
739 x_timeframe,
740 x_standard_ind,
741 x_display_ind,
742 x_include_standard_ind,
743 x_include_local_ind,
744 x_include_other_ind,
745 x_mode
746 );
747 RETURN;
748 END IF;
749 CLOSE c1;
750
751 update_row (
752 x_rowid,
753 x_stat_type,
754 x_org_unit_cd,
755 x_display_order,
756 x_timeframe,
757 x_standard_ind,
758 x_display_ind,
759 x_include_standard_ind,
760 x_include_local_ind,
761 x_include_other_ind,
762 x_mode
763 );
764
765 END add_row;
766
767
768 PROCEDURE delete_row (
769 x_rowid IN VARCHAR2
770 ) AS
771 /*
772 || Created By : nbehera
773 || Created On : 02-NOV-2001
774 || Purpose : Handles the DELETE DML logic for the table.
775 || Known limitations, enhancements or remarks :
776 || Change History :
777 || Who When What
778 || (reverse chronological order - newest change first)
779 */
780 BEGIN
781
782 before_dml (
783 p_action => 'DELETE',
784 x_rowid => x_rowid
785 );
786
787 DELETE FROM igs_pr_org_stat
788 WHERE rowid = x_rowid;
789
790 IF (SQL%NOTFOUND) THEN
791 RAISE NO_DATA_FOUND;
792 END IF;
793
794 END delete_row;
795
796
797 END igs_pr_org_stat_pkg;
[Home] [Help]