1 PACKAGE BODY igs_ps_us_req_ref_cd_pkg AS
2 /* $Header: IGSPI2TB.pls 115.6 2003/05/09 06:42:28 sarakshi ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_ps_us_req_ref_cd%ROWTYPE;
6 new_references igs_ps_us_req_ref_cd%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_unit_section_req_ref_cd_id IN NUMBER DEFAULT NULL,
12 x_unit_section_reference_id IN NUMBER DEFAULT NULL,
13 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL ,
19 x_reference_code IN VARCHAR2,
20 x_reference_code_desc IN VARCHAR2
21 ) AS
22 /*
23 || Created By : [email protected]
24 || Created On : 25-MAY-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 IGS_PS_US_REQ_REF_CD
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.unit_section_req_ref_cd_id := x_unit_section_req_ref_cd_id;
56 new_references.unit_section_reference_id := x_unit_section_reference_id;
57 new_references.reference_cd_type := x_reference_cd_type;
58 new_references.reference_code := x_reference_code;
59 new_references.reference_code_desc := x_reference_code_desc;
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 : [email protected]
79 || Created On : 25-MAY-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_section_reference_id,
90 new_references.reference_cd_type,
91 new_references.reference_code
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 : [email protected]
105 || Created On : 25-MAY-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
113 CURSOR cur_reference_cd_chk(cp_reference_cd_type igs_ge_ref_cd_type_all.reference_cd_type%TYPE) IS
114 SELECT 'X'
115 FROM igs_ge_ref_cd_type_all
116 WHERE restricted_flag='Y'
117 AND reference_cd_type=cp_reference_cd_type;
118 l_var VARCHAR2(1);
119
120 BEGIN
121
122 --Enh#2858431, added as a part of PSP Enhancement build
123 OPEN cur_reference_cd_chk(new_references.reference_cd_type);
124 FETCH cur_reference_cd_chk INTO l_var;
125 IF cur_reference_cd_chk%FOUND THEN
126 IF (((old_references.reference_cd_type = new_references.reference_cd_type) AND
127 (old_references.reference_code = new_references.reference_code)) OR
128 ((new_references.reference_cd_type IS NULL) OR
129 (new_references.reference_code IS NULL))) THEN
130 NULL;
131 ELSIF NOT igs_ge_ref_cd_pkg.get_uk_For_validation (
132 new_references.reference_cd_type,
133 new_references.reference_code
134 ) THEN
135 CLOSE cur_reference_cd_chk;
136 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
137 igs_ge_msg_stack.add;
138 app_exception.raise_exception;
139 END IF;
140 END IF;
141 CLOSE cur_reference_cd_chk;
142
143
144 IF (((old_references.reference_cd_type = new_references.reference_cd_type)) OR
145 ((new_references.reference_cd_type IS NULL))) THEN
146 NULL;
147 ELSIF NOT igs_ge_ref_cd_type_pkg.get_pk_for_validation (
148 new_references.reference_cd_type
149 ) THEN
150 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
151 igs_ge_msg_stack.add;
152 app_exception.raise_exception;
153 END IF;
154
155 IF (((old_references.unit_section_reference_id = new_references.unit_section_reference_id)) OR
156 ((new_references.unit_section_reference_id IS NULL))) THEN
157 NULL;
158 ELSIF NOT igs_ps_usec_ref_pkg.get_pk_for_validation (
159 new_references.unit_section_reference_id
160 ) THEN
161 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
162 igs_ge_msg_stack.add;
163 app_exception.raise_exception;
164 END IF;
165
166 END check_parent_existance;
167
168
169 FUNCTION get_pk_for_validation (
170 x_unit_section_req_ref_cd_id IN NUMBER
171 ) RETURN BOOLEAN AS
172 /*
173 || Created By : [email protected]
174 || Created On : 25-MAY-2001
175 || Purpose : Validates the Primary Key of the table.
176 || Known limitations, enhancements or remarks :
177 || Change History :
178 || Who When What
179 || (reverse chronological order - newest change first)
180 */
181 CURSOR cur_rowid IS
182 SELECT rowid
183 FROM igs_ps_us_req_ref_cd
184 WHERE unit_section_req_ref_cd_id = x_unit_section_req_ref_cd_id
185 FOR UPDATE NOWAIT;
186
187 lv_rowid cur_rowid%RowType;
188
189 BEGIN
190
191 OPEN cur_rowid;
192 FETCH cur_rowid INTO lv_rowid;
193 IF (cur_rowid%FOUND) THEN
194 CLOSE cur_rowid;
195 RETURN(TRUE);
196 ELSE
197 CLOSE cur_rowid;
198 RETURN(FALSE);
199 END IF;
200
201 END get_pk_for_validation;
202
203
204 FUNCTION get_uk_for_validation (
205 x_unit_section_reference_id IN NUMBER,
206 x_reference_cd_type IN VARCHAR2,
207 x_reference_code IN VARCHAR2
208 ) RETURN BOOLEAN AS
209 /*
210 || Created By : [email protected]
211 || Created On : 25-MAY-2001
212 || Purpose : Validates the Unique Keys of the table.
213 || Known limitations, enhancements or remarks :
214 || Change History :
215 || Who When What
216 || (reverse chronological order - newest change first)
217 */
218 CURSOR cur_rowid IS
219 SELECT rowid
220 FROM igs_ps_us_req_ref_cd
221 WHERE unit_section_reference_id = x_unit_section_reference_id
222 AND reference_cd_type = x_reference_cd_type
223 AND reference_code = x_reference_code
224 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
225
226 lv_rowid cur_rowid%RowType;
227
228 BEGIN
229
230 OPEN cur_rowid;
231 FETCH cur_rowid INTO lv_rowid;
232 IF (cur_rowid%FOUND) THEN
233 CLOSE cur_rowid;
234 RETURN (true);
235 ELSE
236 CLOSE cur_rowid;
237 RETURN(FALSE);
238 END IF;
239
240 END get_uk_for_validation ;
241
242
243 PROCEDURE get_ufk_igs_ge_ref_cd (
244 x_reference_cd_type IN VARCHAR2,
245 x_reference_code IN VARCHAR2
246 ) AS
247 /*
248 || Created By : [email protected]
249 || Created On : 25-MAY-2001
250 || Purpose : Validates the Foreign Keys for the table.
251 || Known limitations, enhancements or remarks :
252 || Change History :
253 || Who When What
254 || (reverse chronological order - newest change first)
255 */
256 CURSOR cur_rowid IS
257 SELECT rowid
258 FROM igs_ps_us_req_ref_cd
259 WHERE reference_cd_type = x_reference_cd_type
260 AND reference_code= x_reference_code;
261
262 lv_rowid cur_rowid%RowType;
263
264 BEGIN
265
266 OPEN cur_rowid;
267 FETCH cur_rowid INTO lv_rowid;
268 IF (cur_rowid%FOUND) THEN
269 CLOSE cur_rowid;
270 fnd_message.set_name ('IGS', 'IGS_PS_RC_USRRC_FK1');
271 igs_ge_msg_stack.add;
272 app_exception.raise_exception;
273 RETURN;
274 END IF;
275 CLOSE cur_rowid;
276
277 END get_ufk_igs_ge_ref_cd;
278
279
280 PROCEDURE get_fk_igs_ge_ref_cd_type (
281 x_reference_cd_type IN VARCHAR2
282 ) AS
283 /*
284 || Created By : [email protected]
285 || Created On : 25-MAY-2001
286 || Purpose : Validates the Foreign Keys for the table.
287 || Known limitations, enhancements or remarks :
288 || Change History :
289 || Who When What
290 || (reverse chronological order - newest change first)
291 */
292 CURSOR cur_rowid IS
293 SELECT rowid
294 FROM igs_ps_us_req_ref_cd
295 WHERE ((reference_cd_type = x_reference_cd_type));
296
297 lv_rowid cur_rowid%RowType;
298
299 BEGIN
300
301 OPEN cur_rowid;
302 FETCH cur_rowid INTO lv_rowid;
303 IF (cur_rowid%FOUND) THEN
304 CLOSE cur_rowid;
305 fnd_message.set_name ('IGS', 'IGS_PS_RCT_USRRC_FK2');
306 igs_ge_msg_stack.add;
307 app_exception.raise_exception;
308 RETURN;
309 END IF;
310 CLOSE cur_rowid;
311
312 END get_fk_igs_ge_ref_cd_type;
313
314
315 PROCEDURE get_fk_igs_ps_usec_ref (
316 x_unit_section_reference_id IN NUMBER
317 ) AS
318 /*
319 || Created By : [email protected]
320 || Created On : 25-MAY-2001
321 || Purpose : Validates the Foreign Keys for the table.
322 || Known limitations, enhancements or remarks :
323 || Change History :
324 || Who When What
325 || (reverse chronological order - newest change first)
326 */
327 CURSOR cur_rowid IS
328 SELECT rowid
329 FROM igs_ps_us_req_ref_cd
330 WHERE ((unit_section_reference_id = x_unit_section_reference_id));
331
332 lv_rowid cur_rowid%RowType;
333
334 BEGIN
335
336 OPEN cur_rowid;
337 FETCH cur_rowid INTO lv_rowid;
338 IF (cur_rowid%FOUND) THEN
339 CLOSE cur_rowid;
340 fnd_message.set_name ('IGS', 'IGS_PS_USR_USRRC_FK3');
341 igs_ge_msg_stack.add;
342 app_exception.raise_exception;
343 RETURN;
344 END IF;
345 CLOSE cur_rowid;
346
347 END get_fk_igs_ps_usec_ref;
348
349
350 PROCEDURE before_dml (
351 p_action IN VARCHAR2,
352 x_rowid IN VARCHAR2 DEFAULT NULL,
353 x_unit_section_req_ref_cd_id IN NUMBER DEFAULT NULL,
354 x_unit_section_reference_id IN NUMBER DEFAULT NULL,
355 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
356 x_creation_date IN DATE DEFAULT NULL,
357 x_created_by IN NUMBER DEFAULT NULL,
358 x_last_update_date IN DATE DEFAULT NULL,
359 x_last_updated_by IN NUMBER DEFAULT NULL,
360 x_last_update_login IN NUMBER DEFAULT NULL,
361 x_reference_code IN VARCHAR2,
362 x_reference_code_desc IN VARCHAR2
363 ) AS
364 /*
365 || Created By : [email protected]
366 || Created On : 25-MAY-2001
367 || Purpose : Initialises the columns, Checks Constraints, Calls the
368 || Trigger Handlers for the table, before any DML operation.
369 || Known limitations, enhancements or remarks :
370 || Change History :
371 || Who When What
372 || (reverse chronological order - newest change first)
373 */
374 BEGIN
375
376 set_column_values (
377 p_action,
378 x_rowid,
379 x_unit_section_req_ref_cd_id,
380 x_unit_section_reference_id,
381 x_reference_cd_type,
382 x_creation_date,
383 x_created_by,
384 x_last_update_date,
385 x_last_updated_by,
386 x_last_update_login ,
387 x_reference_code,
388 x_reference_code_desc
389 );
390
391 IF (p_action = 'INSERT') THEN
392 -- Call all the procedures related to Before Insert.
393 IF ( get_pk_for_validation(
394 new_references.unit_section_req_ref_cd_id
395 )
396 ) THEN
397 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
398 igs_ge_msg_stack.add;
399 app_exception.raise_exception;
400 END IF;
401 check_uniqueness;
402 check_parent_existance;
403 ELSIF (p_action = 'UPDATE') THEN
404 -- Call all the procedures related to Before Update.
405 check_uniqueness;
406 check_parent_existance;
407 ELSIF (p_action = 'VALIDATE_INSERT') THEN
408 -- Call all the procedures related to Before Insert.
409 IF ( get_pk_for_validation (
410 new_references.unit_section_req_ref_cd_id
411 )
412 ) THEN
413 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
414 igs_ge_msg_stack.add;
415 app_exception.raise_exception;
416 END IF;
417 check_uniqueness;
418 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
419 check_uniqueness;
420 END IF;
421
422 l_rowid:=NULL;
423
424 END before_dml;
425
426
427 PROCEDURE insert_row (
428 x_rowid IN OUT NOCOPY VARCHAR2,
429 x_unit_section_req_ref_cd_id IN OUT NOCOPY NUMBER,
430 x_unit_section_reference_id IN NUMBER,
431 x_reference_cd_type IN VARCHAR2,
432 x_mode IN VARCHAR2 DEFAULT 'R' ,
433 x_reference_code IN VARCHAR2,
434 x_reference_code_desc IN VARCHAR2
435 ) AS
436 /*
437 || Created By : [email protected]
438 || Created On : 25-MAY-2001
439 || Purpose : Handles the INSERT DML logic for the table.
440 || Known limitations, enhancements or remarks :
441 || Change History :
442 || Who When What
443 || (reverse chronological order - newest change first)
444 */
445 CURSOR c IS
446 SELECT rowid
447 FROM igs_ps_us_req_ref_cd
448 WHERE unit_section_req_ref_cd_id = x_unit_section_req_ref_cd_id;
449
450 x_last_update_date DATE;
451 x_last_updated_by NUMBER;
452 x_last_update_login NUMBER;
453
454 BEGIN
455
456 x_last_update_date := SYSDATE;
457 IF (x_mode = 'I') THEN
458 x_last_updated_by := 1;
459 x_last_update_login := 0;
460 ELSIF (x_mode = 'R') THEN
461 x_last_updated_by := fnd_global.user_id;
462 IF (x_last_updated_by IS NULL) THEN
463 x_last_updated_by := -1;
464 END IF;
465 x_last_update_login := fnd_global.login_id;
466 IF (x_last_update_login IS NULL) THEN
467 x_last_update_login := -1;
468 END IF;
469 ELSE
470 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
471 igs_ge_msg_stack.add;
472 app_exception.raise_exception;
473 END IF;
474
475 SELECT igs_ps_us_req_ref_cd_s.NEXTVAL
476 INTO x_unit_section_req_ref_cd_id
477 FROM dual;
478
479 before_dml(
480 p_action => 'INSERT',
481 x_rowid => x_rowid,
482 x_unit_section_req_ref_cd_id => x_unit_section_req_ref_cd_id,
483 x_unit_section_reference_id => x_unit_section_reference_id,
484 x_reference_cd_type => x_reference_cd_type,
485 x_creation_date => x_last_update_date,
486 x_created_by => x_last_updated_by,
487 x_last_update_date => x_last_update_date,
488 x_last_updated_by => x_last_updated_by,
489 x_last_update_login => x_last_update_login,
490 x_reference_code => x_reference_code,
491 x_reference_code_desc => x_reference_code_desc
492 );
493
494 INSERT INTO igs_ps_us_req_ref_cd (
495 unit_section_req_ref_cd_id,
496 unit_section_reference_id,
497 reference_cd_type,
498 creation_date,
499 created_by,
500 last_update_date,
501 last_updated_by,
502 last_update_login,
503 reference_code,
504 reference_code_desc
505 ) VALUES (
506 new_references.unit_section_req_ref_cd_id,
507 new_references.unit_section_reference_id,
508 new_references.reference_cd_type,
509 x_last_update_date,
510 x_last_updated_by,
511 x_last_update_date,
512 x_last_updated_by,
513 x_last_update_login ,
514 new_references.reference_code,
515 new_references.reference_code_desc
516 );
517
518 OPEN c;
519 FETCH c INTO x_rowid;
520 IF (c%NOTFOUND) THEN
521 CLOSE c;
522 RAISE NO_DATA_FOUND;
523 END IF;
524 CLOSE c;
525
526 END insert_row;
527
528
529 PROCEDURE lock_row (
530 x_rowid IN VARCHAR2,
531 x_unit_section_req_ref_cd_id IN NUMBER,
532 x_unit_section_reference_id IN NUMBER,
533 x_reference_cd_type IN VARCHAR2,
534 x_reference_code IN VARCHAR2,
535 x_reference_code_desc IN VARCHAR2
536 ) AS
537 /*
538 || Created By : [email protected]
539 || Created On : 25-MAY-2001
540 || Purpose : Handles the LOCK mechanism 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 CURSOR c1 IS
547 SELECT
548 unit_section_reference_id,
549 reference_cd_type,
550 reference_code,
551 reference_code_desc
552 FROM igs_ps_us_req_ref_cd
553 WHERE rowid = x_rowid
554 FOR UPDATE NOWAIT;
555
556 tlinfo c1%ROWTYPE;
557
558 BEGIN
559
560 OPEN c1;
561 FETCH c1 INTO tlinfo;
562 IF (c1%notfound) THEN
563 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
564 igs_ge_msg_stack.add;
565 CLOSE c1;
566 app_exception.raise_exception;
567 RETURN;
568 END IF;
569 CLOSE c1;
570
571 IF (
572 (tlinfo.unit_section_reference_id = x_unit_section_reference_id)
573 AND (tlinfo.reference_cd_type = x_reference_cd_type)
574 AND ((tlinfo.reference_code= x_reference_code)
575 OR ((tlinfo.reference_code IS NULL)
576 AND (x_reference_code IS NULL)))
577 AND ((tlinfo.reference_code_desc= x_reference_code_desc)
578 OR ((tlinfo.reference_code_desc IS NULL)
579 AND (x_reference_code_desc IS NULL)))
580 ) THEN
581 NULL;
582 ELSE
583 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
584 igs_ge_msg_stack.add;
585 app_exception.raise_exception;
586 END IF;
587
588 RETURN;
589
590 END lock_row;
591
592
593 PROCEDURE update_row (
594 x_rowid IN VARCHAR2,
595 x_unit_section_req_ref_cd_id IN NUMBER,
596 x_unit_section_reference_id IN NUMBER,
597 x_reference_cd_type IN VARCHAR2,
598 x_mode IN VARCHAR2 DEFAULT 'R' ,
599 x_reference_code IN VARCHAR2,
600 x_reference_code_desc IN VARCHAR2
601 ) AS
602 /*
603 || Created By : [email protected]
604 || Created On : 25-MAY-2001
605 || Purpose : Handles the UPDATE DML logic for the table.
606 || Known limitations, enhancements or remarks :
607 || Change History :
608 || Who When What
609 || (reverse chronological order - newest change first)
610 */
611 x_last_update_date DATE ;
612 x_last_updated_by NUMBER;
613 x_last_update_login NUMBER;
614
615 BEGIN
616
617 x_last_update_date := SYSDATE;
618 IF (X_MODE = 'I') THEN
619 x_last_updated_by := 1;
620 x_last_update_login := 0;
621 ELSIF (x_mode = 'R') THEN
622 x_last_updated_by := fnd_global.user_id;
623 IF x_last_updated_by IS NULL THEN
624 x_last_updated_by := -1;
625 END IF;
626 x_last_update_login := fnd_global.login_id;
627 IF (x_last_update_login IS NULL) THEN
628 x_last_update_login := -1;
629 END IF;
630 ELSE
631 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
632 igs_ge_msg_stack.add;
633 app_exception.raise_exception;
634 END IF;
635
636 before_dml(
637 p_action => 'UPDATE',
638 x_rowid => x_rowid,
639 x_unit_section_req_ref_cd_id => x_unit_section_req_ref_cd_id,
640 x_unit_section_reference_id => x_unit_section_reference_id,
641 x_reference_cd_type => x_reference_cd_type,
642 x_creation_date => x_last_update_date,
643 x_created_by => x_last_updated_by,
644 x_last_update_date => x_last_update_date,
645 x_last_updated_by => x_last_updated_by,
646 x_last_update_login => x_last_update_login ,
647 x_reference_code => x_reference_code,
648 x_reference_code_desc => x_reference_code_desc
649 );
650
651 UPDATE igs_ps_us_req_ref_cd
652 SET
653 unit_section_reference_id = new_references.unit_section_reference_id,
654 reference_cd_type = new_references.reference_cd_type,
655 last_update_date = x_last_update_date,
656 last_updated_by = x_last_updated_by,
657 last_update_login = x_last_update_login,
658 reference_code = x_reference_code,
659 reference_code_desc = x_reference_code_desc
660 WHERE rowid = x_rowid;
661
662 IF (SQL%NOTFOUND) THEN
663 RAISE NO_DATA_FOUND;
664 END IF;
665
666 END update_row;
667
668
669 PROCEDURE add_row (
670 x_rowid IN OUT NOCOPY VARCHAR2,
671 x_unit_section_req_ref_cd_id IN OUT NOCOPY NUMBER,
672 x_unit_section_reference_id IN NUMBER,
673 x_reference_cd_type IN VARCHAR2,
674 x_mode IN VARCHAR2 DEFAULT 'R' ,
675 x_reference_code IN VARCHAR2,
676 x_reference_code_desc IN VARCHAR2
677 ) AS
678 /*
679 || Created By : [email protected]
680 || Created On : 25-MAY-2001
681 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
682 || Known limitations, enhancements or remarks :
683 || Change History :
684 || Who When What
685 || (reverse chronological order - newest change first)
686 */
687 CURSOR c1 IS
688 SELECT rowid
689 FROM igs_ps_us_req_ref_cd
690 WHERE unit_section_req_ref_cd_id = x_unit_section_req_ref_cd_id;
691
692 BEGIN
693
694 OPEN c1;
695 FETCH c1 INTO x_rowid;
696 IF (c1%NOTFOUND) THEN
697 CLOSE c1;
698
699 insert_row (
700 x_rowid,
701 x_unit_section_req_ref_cd_id,
702 x_unit_section_reference_id,
703 x_reference_cd_type,
704 x_mode ,
705 x_reference_code,
706 x_reference_code_desc
707 );
708 RETURN;
709 END IF;
710 CLOSE c1;
711
712 update_row (
713 x_rowid,
714 x_unit_section_req_ref_cd_id,
715 x_unit_section_reference_id,
716 x_reference_cd_type,
717 x_mode,
718 x_reference_code,
719 x_reference_code_desc
720 );
721
722 END add_row;
723
724
725 PROCEDURE delete_row (
726 x_rowid IN VARCHAR2
727 ) AS
728 /*
729 || Created By : [email protected]
730 || Created On : 25-MAY-2001
731 || Purpose : Handles the DELETE DML logic for the table.
732 || Known limitations, enhancements or remarks :
733 || Change History :
734 || Who When What
735 || (reverse chronological order - newest change first)
736 */
737 BEGIN
738
739 before_dml (
740 p_action => 'DELETE',
741 x_rowid => x_rowid
742 );
743
744 DELETE FROM igs_ps_us_req_ref_cd
745 WHERE rowid = x_rowid;
746
747 IF (SQL%NOTFOUND) THEN
748 RAISE NO_DATA_FOUND;
749 END IF;
750
751 END delete_row;
752
753
754 END igs_ps_us_req_ref_cd_pkg;
[Home] [Help]