1 PACKAGE BODY igs_ps_usec_ref_cd_pkg AS
2 /* $Header: IGSPI1HB.pls 115.9 2003/05/09 06:40:16 sarakshi ship $ */
3 /* CAHNGE HISTORY
4 WHO WHEN WAHT
5 ayedubat 24-MAY-2001 modified the Before_Dml to a new validation according to the DLD,PSP001-US */
6
7 l_rowid VARCHAR2(25);
8 old_references igs_ps_usec_ref_cd%ROWTYPE;
9 new_references igs_ps_usec_ref_cd%ROWTYPE;
10
11 PROCEDURE set_column_values (
12 p_action IN VARCHAR2,
13 x_rowid IN VARCHAR2 DEFAULT NULL,
14 x_unit_section_reference_cd_id IN NUMBER DEFAULT NULL,
15 x_unit_section_reference_id 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 x_reference_code_type IN VARCHAR2 ,
22 x_reference_code IN VARCHAR2 ,
23 x_reference_code_desc IN VARCHAR2
24 ) AS
25
26 /*************************************************************
27 Created By :
28 Date Created By :
29 Purpose :
30 Know limitations, enhancements or remarks
31 Change History
32 Who When What
33
34 (reverse chronological order - newest change first)
35 ***************************************************************/
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM igs_ps_usec_ref_cd
40 WHERE ROWID = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 OPEN cur_old_ref_values;
49 FETCH cur_old_ref_values INTO old_references;
50 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
51 CLOSE cur_old_ref_values;
52 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
53 igs_ge_msg_stack.add;
54 app_exception.raise_exception;
55 RETURN;
56 END IF;
57 CLOSE cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.unit_section_reference_cd_id := x_unit_section_reference_cd_id;
61 new_references.unit_section_reference_id := x_unit_section_reference_id;
62 new_references.reference_code_type :=x_reference_code_type;
63 new_references.reference_code :=x_reference_code;
64 new_references.reference_code_desc :=x_reference_code_desc;
65 IF (p_action = 'UPDATE') THEN
66 new_references.creation_date := old_references.creation_date;
67 new_references.created_by := old_references.created_by;
68 ELSE
69 new_references.creation_date := x_creation_date;
70 new_references.created_by := x_created_by;
71 END IF;
72 new_references.last_update_date := x_last_update_date;
73 new_references.last_updated_by := x_last_updated_by;
74 new_references.last_update_login := x_last_update_login;
75
76 END set_column_values;
77
78 PROCEDURE check_constraints (
79 column_name IN VARCHAR2 DEFAULT NULL,
80 column_value IN VARCHAR2 DEFAULT NULL ) AS
81 /*************************************************************
82 Created By :
83 Date Created By :
84 Purpose :
85 Know limitations, enhancements or remarks
86 Change History
87 Who When What
88
89 (reverse chronological order - newest change first)
90 ***************************************************************/
91
92 BEGIN
93
94 IF column_name IS NULL THEN
95 NULL;
96 NULL;
97 END IF;
98
99 END check_constraints;
100
101 PROCEDURE check_uniqueness AS
102 /*************************************************************
103 Created By :
104 Date Created By :
105 Purpose :
106 Know limitations, enhancements or remarks
107 Change History
108 Who When What
109
110 (reverse chronological order - newest change first)
111 ***************************************************************/
112
113 BEGIN
114 IF get_uk_for_validation (
115 new_references.unit_section_reference_id,
116 new_references.reference_code_type,
117 new_references.reference_code
118 ) THEN
119 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
120 igs_ge_msg_stack.add;
121 app_exception.raise_exception;
122 END IF;
123 END check_uniqueness ;
124
125 PROCEDURE check_parent_existance AS
126 /*************************************************************
127 Created By :
128 Date Created By :
129 Purpose :
130 Know limitations, enhancements or remarks
131 Change History
132 Who When What
133
134 (reverse chronological order - newest change first)
135 ***************************************************************/
136 CURSOR cur_reference_cd_chk(cp_reference_cd_type igs_ge_ref_cd_type_all.reference_cd_type%TYPE) IS
137 SELECT 'X'
138 FROM igs_ge_ref_cd_type_all
139 WHERE restricted_flag='Y'
140 AND reference_cd_type=cp_reference_cd_type;
141 l_var VARCHAR2(1);
142
143 BEGIN
144
145 IF (((old_references.unit_section_reference_id = new_references.unit_section_reference_id)) OR
146 ((new_references.unit_section_reference_id IS NULL))) THEN
147 NULL;
148 ELSIF NOT igs_ps_usec_ref_pkg.get_pk_for_validation (
149 new_references.unit_section_reference_id
150 ) THEN
151 fnd_message.set_name ('FND','FORM_RECORD_DELETED');
152 igs_ge_msg_stack.add;
153 app_exception.raise_exception;
154 END IF;
155
156 OPEN cur_reference_cd_chk(new_references.reference_code_type);
157 FETCH cur_reference_cd_chk INTO l_var;
158 IF cur_reference_cd_chk%FOUND THEN
159 IF (((old_references.reference_code_type = new_references.reference_code_type) AND
160 (old_references.reference_code= new_references.reference_code)) OR
161 ((new_references.reference_code_type IS NULL) OR
162 (new_references.reference_code IS NULL))) THEN
163 NULL;
164 ELSIF NOT igs_ge_ref_cd_pkg.get_uk_For_validation (
165 new_references.reference_code_type,
166 new_references.reference_code
167 ) THEN
168 CLOSE cur_reference_cd_chk;
169 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
170 igs_ge_msg_stack.add;
171 app_exception.raise_exception;
172 END IF;
173 END IF;
174 CLOSE cur_reference_cd_chk;
175
176 END check_parent_existance;
177
178 FUNCTION get_pk_for_validation (
179 x_unit_section_reference_cd_id IN NUMBER
180 ) RETURN BOOLEAN AS
181
182 /*************************************************************
183 Created By :
184 Date Created By :
185 Purpose :
186 Know limitations, enhancements or remarks
187 Change History
188 Who When What
189
190 (reverse chronological order - newest change first)
191 ***************************************************************/
192
193 CURSOR cur_rowid IS
194 SELECT ROWID
195 FROM igs_ps_usec_ref_cd
196 WHERE unit_section_reference_cd_id = x_unit_section_reference_cd_id
197 FOR UPDATE NOWAIT;
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 END get_pk_for_validation;
213
214 FUNCTION get_uk_for_validation (
215 x_unit_section_reference_id IN NUMBER,
216 x_reference_code_type IN VARCHAR2,
217 x_reference_code IN VARCHAR2
218 ) RETURN BOOLEAN AS
219
220 /*************************************************************
221 Created By :
222 Date Created By :
223 Purpose :
224 Know limitations, enhancements or remarks
225 Change History
226 Who When What
227
228 (reverse chronological order - newest change first)
229 ***************************************************************/
230
231 CURSOR cur_rowid IS
232 SELECT ROWID
233 FROM igs_ps_usec_ref_cd
234 WHERE reference_code_type = x_reference_code_type
235 AND reference_code = x_reference_code
236 AND unit_section_reference_id = x_unit_section_reference_id
237 AND ((l_rowid IS NULL) OR (ROWID <> l_rowid));
238
239 lv_rowid cur_rowid%ROWTYPE;
240
241 BEGIN
242
243 OPEN cur_rowid;
244 FETCH cur_rowid INTO lv_rowid;
245 IF (cur_rowid%FOUND) THEN
246 CLOSE cur_rowid;
247 RETURN (TRUE);
248 ELSE
249 CLOSE cur_rowid;
250 RETURN(FALSE);
251 END IF;
252 END get_uk_for_validation ;
253 PROCEDURE get_fk_igs_ps_usec_ref (
254 x_unit_section_reference_id IN NUMBER
255 ) AS
256
257 /*************************************************************
258 Created By :
259 Date Created By :
260 Purpose :
261 Know limitations, enhancements or remarks
262 Change History
263 Who When What
264
265 (reverse chronological order - newest change first)
266 ***************************************************************/
267
268 CURSOR cur_rowid IS
269 SELECT ROWID
270 FROM igs_ps_usec_ref_cd
271 WHERE unit_section_reference_id = x_unit_section_reference_id ;
272
273 lv_rowid cur_rowid%ROWTYPE;
274
275 BEGIN
276
277 OPEN cur_rowid;
278 FETCH cur_rowid INTO lv_rowid;
279 IF (cur_rowid%FOUND) THEN
280 CLOSE cur_rowid;
281 fnd_message.set_name ('IGS', 'IGS_PS_USRCD_USREF_FK');
282 igs_ge_msg_stack.add;
283 app_exception.raise_exception;
284 RETURN;
285 END IF;
286 CLOSE cur_rowid;
287
288 END get_fk_igs_ps_usec_ref;
289
290 PROCEDURE get_fk_igs_ge_ref_cd_type(
291 x_reference_code_type IN VARCHAR2
292 ) AS
293
294 /*************************************************************
295 Created By :sarakshi
296 Date Created By :28-Apr-2003
297 Purpose :
298 Know limitations, enhancements or remarks
299 Change History
300 Who When What
301
302 (reverse chronological order - newest change first)
303 ***************************************************************/
304
305 CURSOR cur_rowid IS
306 SELECT ROWID
307 FROM igs_ps_usec_ref_cd
308 WHERE reference_code_type = x_reference_code_type;
309
310 lv_rowid cur_rowid%ROWTYPE;
311 BEGIN
312
313 OPEN cur_rowid;
314 FETCH cur_rowid INTO lv_rowid;
315 IF (cur_rowid%FOUND) THEN
316 CLOSE cur_rowid;
317 fnd_message.set_name ('IGS', 'IGS_PS_USRCD_RCT_FK');
318 igs_ge_msg_stack.add;
319 app_exception.raise_exception;
320 RETURN;
321 END IF;
322 CLOSE cur_rowid;
323
324 END get_fk_igs_ge_ref_cd_type;
325
326 PROCEDURE get_ufk_igs_ge_ref_cd (
327 x_reference_code_type IN VARCHAR2,
328 x_reference_code IN VARCHAR2
329 ) AS
330
331 /*************************************************************
332 Created By :
333 Date Created By :
334 Purpose :
335 Know limitations, enhancements or remarks
336 Change History
337 Who When What
338
339 (reverse chronological order - newest change first)
340 ***************************************************************/
341
342 CURSOR cur_rowid IS
343 SELECT ROWID
344 FROM igs_ps_usec_ref_cd
345 WHERE reference_code_type = x_reference_code_type
346 AND reference_code = x_reference_code ;
347
348 lv_rowid cur_rowid%ROWTYPE;
349
350 BEGIN
351
352 OPEN cur_rowid;
353 FETCH cur_rowid INTO lv_rowid;
354 IF (cur_rowid%FOUND) THEN
355 CLOSE cur_rowid;
356 fnd_message.set_name ('IGS', 'IGS_PS_USRCD_RC_FK');
357 igs_ge_msg_stack.add;
358 app_exception.raise_exception;
359 RETURN;
360 END IF;
361 CLOSE cur_rowid;
362
363 END get_ufk_igs_ge_ref_cd;
364
365 PROCEDURE before_dml (
366 p_action IN VARCHAR2,
367 x_rowid IN VARCHAR2 DEFAULT NULL,
368 x_unit_section_reference_cd_id IN NUMBER DEFAULT NULL,
369 x_unit_section_reference_id IN NUMBER DEFAULT NULL,
370 x_creation_date IN DATE DEFAULT NULL,
371 x_created_by IN NUMBER DEFAULT NULL,
372 x_last_update_date IN DATE DEFAULT NULL,
373 x_last_updated_by IN NUMBER DEFAULT NULL,
374 x_last_update_login IN NUMBER DEFAULT NULL,
375 x_reference_code_type IN VARCHAR2 ,
376 x_reference_code IN VARCHAR2 ,
377 x_reference_code_desc IN VARCHAR2
378 ) AS
379 /*************************************************************
380 Created By :
381 Date Created By :
382 Purpose :
383 Know limitations, enhancements or remarks
384 Change History
385 Who When What
386
387 (reverse chronological order - newest change first)
388 ***************************************************************/
389 CURSOR cur_ps_usec_ref_cd_v IS
390 SELECT reference_code_type
391 FROM igs_ps_usec_ref_cd_v
392 WHERE unit_section_reference_cd_id = x_unit_section_reference_cd_id;
393 lv_reference_code_type cur_ps_usec_ref_cd_v%ROWTYPE;
394 BEGIN
395
396 set_column_values (
397 p_action,
398 x_rowid,
399 x_unit_section_reference_cd_id,
400 x_unit_section_reference_id,
401 x_creation_date,
402 x_created_by,
403 x_last_update_date,
404 x_last_updated_by,
405 x_last_update_login ,
406 x_reference_code_type ,
407 x_reference_code ,
408 x_reference_code_desc
409 );
410
411 IF (p_action = 'INSERT') THEN
412 -- Call all the procedures related to Before Insert.
413 NULL;
414 IF get_pk_for_validation(
415 new_references.unit_section_reference_cd_id) THEN
416 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
417 igs_ge_msg_stack.add;
418 app_exception.raise_exception;
419 END IF;
420 check_uniqueness;
421 check_constraints;
422 check_parent_existance;
423 ELSIF (p_action = 'UPDATE') THEN
424 -- Call all the procedures related to Before Update.
425 NULL;
426 check_uniqueness;
427 check_constraints;
428 check_parent_existance;
429 ELSIF (p_action = 'DELETE') THEN
430 -- Call all the procedures related to Before Delete.
431 NULL;
432 OPEN cur_ps_usec_ref_cd_v;
433 FETCH cur_ps_usec_ref_cd_v INTO lv_reference_code_type;
434 IF igs_ps_val_atl.chk_mandatory_ref_cd(lv_reference_code_type.reference_code_type) THEN
435 fnd_message.set_name ('IGS', 'IGS_PS_REF_CD_MANDATORY');
436 igs_ge_msg_stack.add;
437 app_exception.raise_exception;
438 END IF;
439 CLOSE cur_ps_usec_ref_cd_v;
440
441 ELSIF (p_action = 'VALIDATE_INSERT') THEN
442 -- Call all the procedures related to Before Insert.
443 IF get_pk_for_validation (
444 new_references.unit_section_reference_cd_id) THEN
445 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
446 igs_ge_msg_stack.add;
447 app_exception.raise_exception;
448 END IF;
449 check_uniqueness;
450 check_constraints;
451 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
452 check_uniqueness;
453 check_constraints;
454 ELSIF (p_action = 'VALIDATE_DELETE') THEN
455 NULL;
456 END IF;
457
458 END before_dml;
459
460 PROCEDURE after_dml (
461 p_action IN VARCHAR2,
462 x_rowid IN VARCHAR2
463 ) IS
464 /*************************************************************
465 Created By :
466 Date Created By :
467 Purpose :
468 Know limitations, enhancements or remarks
469 Change History
470 Who When What
471
472 (reverse chronological order - newest change first)
473 ***************************************************************/
474
475 BEGIN
476
477 l_rowid := x_rowid;
478
479 IF (p_action = 'INSERT') THEN
480 -- Call all the procedures related to After Insert.
481 NULL;
482 ELSIF (p_action = 'UPDATE') THEN
483 -- Call all the procedures related to After Update.
484 NULL;
485 ELSIF (p_action = 'DELETE') THEN
486 -- Call all the procedures related to After Delete.
487 NULL;
488 END IF;
489 l_rowid:=NULL;
490 END after_dml;
491
492 PROCEDURE insert_row (
493 x_rowid IN OUT NOCOPY VARCHAR2,
494 x_unit_section_reference_cd_id IN OUT NOCOPY NUMBER,
495 x_unit_section_reference_id IN NUMBER,
496 x_mode IN VARCHAR2 DEFAULT 'R' ,
497 x_reference_code_type IN VARCHAR2 ,
498 x_reference_code IN VARCHAR2 ,
499 x_reference_code_desc IN VARCHAR2
500 ) AS
501 /*************************************************************
502 Created By :
503 Date Created By :
504 Purpose :
505 Know limitations, enhancements or remarks
506 Change History
507 Who When What
508
509 (reverse chronological order - newest change first)
510 ***************************************************************/
511
512 CURSOR c IS SELECT ROWID FROM igs_ps_usec_ref_cd
513 WHERE unit_section_reference_cd_id= x_unit_section_reference_cd_id;
514 x_last_update_date DATE ;
515 x_last_updated_by NUMBER ;
516 x_last_update_login NUMBER ;
517 BEGIN
518 x_last_update_date := SYSDATE;
519 IF(x_mode = 'I') THEN
520 x_last_updated_by := 1;
521 x_last_update_login := 0;
522 ELSIF (x_mode = 'R') THEN
523 x_last_updated_by := fnd_global.user_id;
524 IF x_last_updated_by IS NULL THEN
525 x_last_updated_by := -1;
526 END IF;
527 x_last_update_login :=fnd_global.login_id;
528 IF x_last_update_login IS NULL THEN
529 x_last_update_login := -1;
530 END IF;
531 ELSE
532 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
533 igs_ge_msg_stack.add;
534 app_exception.raise_exception;
535 END IF;
536 SELECT igs_ps_usec_ref_cd_s.nextval
537 INTO x_unit_section_reference_cd_id
538 FROM dual;
539 before_dml(
540 p_action=>'INSERT',
541 x_rowid=>x_rowid,
542 x_unit_section_reference_cd_id=>x_unit_section_reference_cd_id,
543 x_unit_section_reference_id=>x_unit_section_reference_id,
544 x_creation_date=>x_last_update_date,
545 x_created_by=>x_last_updated_by,
546 x_last_update_date=>x_last_update_date,
547 x_last_updated_by=>x_last_updated_by,
548 x_last_update_login=>x_last_update_login,
549 x_reference_code_type=>x_reference_code_type);
550 INSERT INTO igs_ps_usec_ref_cd (
551 unit_section_reference_cd_id
552 ,unit_section_reference_id
553 ,creation_date
554 ,created_by
555 ,last_update_date
556 ,last_updated_by
557 ,last_update_login
558 ,reference_code_type
559 ,reference_code
560 ,reference_code_desc
561
562 ) VALUES (
563 new_references.unit_section_reference_cd_id
564 ,new_references.unit_section_reference_id
565 ,x_last_update_date
566 ,x_last_updated_by
567 ,x_last_update_date
568 ,x_last_updated_by
569 ,x_last_update_login
570 ,x_reference_code_type
571 ,x_reference_code
572 ,x_reference_code_desc
573 );
574 OPEN c;
575 FETCH c INTO x_rowid;
576 IF (c%NOTFOUND) THEN
577 CLOSE c;
578 RAISE NO_DATA_FOUND;
579 END IF;
580 CLOSE c;
581 after_dml (
582 p_action => 'INSERT' ,
583 x_rowid => x_rowid );
584 END insert_row;
585
586 PROCEDURE lock_row (
587 x_rowid IN VARCHAR2,
588 x_unit_section_reference_cd_id IN NUMBER,
589 x_unit_section_reference_id IN NUMBER ,
590 x_reference_code_type IN VARCHAR2 ,
591 x_reference_code IN VARCHAR2 ,
592 x_reference_code_desc IN VARCHAR2
593 ) AS
594 /*************************************************************
595 Created By :
596 Date Created By :
597 Purpose :
598 Know limitations, enhancements or remarks
599 Change History
600 Who When What
601
602 (reverse chronological order - newest change first)
603 ***************************************************************/
604
605 CURSOR c1 IS SELECT
606 unit_section_reference_id,
607 reference_code_type,
608 reference_code,
609 reference_code_desc
610 FROM igs_ps_usec_ref_cd
611 WHERE ROWID = x_rowid
612 FOR UPDATE NOWAIT;
613 tlinfo c1%ROWTYPE;
614 BEGIN
615 OPEN c1;
616 FETCH c1 INTO tlinfo;
617 IF (c1%NOTFOUND) THEN
618 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
619 igs_ge_msg_stack.add;
620 CLOSE c1;
621 app_exception.raise_exception;
622 RETURN;
623 END IF;
624 CLOSE c1;
625 IF ( (tlinfo.unit_section_reference_id = x_unit_section_reference_id)
626 AND ((tlinfo.reference_code_type= x_reference_code_type)
627 OR ((tlinfo.reference_code_type IS NULL)
628 AND (x_reference_code_type IS NULL)))
629 AND ((tlinfo.reference_code= x_reference_code)
630 OR ((tlinfo.reference_code IS NULL)
631 AND (x_reference_code IS NULL)))
632 AND ((tlinfo.reference_code_desc= x_reference_code_desc)
633 OR ((tlinfo.reference_code_desc IS NULL)
634 AND (x_reference_code_desc IS NULL)))
635 ) THEN
636 NULL;
637 ELSE
638 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
639 igs_ge_msg_stack.add;
640 app_exception.raise_exception;
641 END IF;
642 RETURN;
643 END lock_row;
644 PROCEDURE update_row (
645 x_rowid IN VARCHAR2,
646 x_unit_section_reference_cd_id IN NUMBER,
647 x_unit_section_reference_id IN NUMBER,
648 x_mode IN VARCHAR2 DEFAULT 'R' ,
649 x_reference_code_type IN VARCHAR2,
650 x_reference_code IN VARCHAR2 ,
651 x_reference_code_desc IN VARCHAR2
652 ) AS
653 /*************************************************************
654 Created By :
655 Date Created By :
656 Purpose :
657 Know limitations, enhancements or remarks
658 Change History
659 Who When What
660
661 (reverse chronological order - newest change first)
662 ***************************************************************/
663
664 x_last_update_date DATE ;
665 x_last_updated_by NUMBER ;
666 x_last_update_login NUMBER ;
667 BEGIN
668 x_last_update_date := SYSDATE;
669 IF(x_mode = 'I') THEN
670 x_last_updated_by := 1;
671 x_last_update_login := 0;
672 ELSIF (x_mode = 'R') THEN
673 x_last_updated_by := fnd_global.user_id;
674 IF x_last_updated_by IS NULL THEN
675 x_last_updated_by := -1;
676 END IF;
677 x_last_update_login :=fnd_global.login_id;
678 IF x_last_update_login IS NULL THEN
679 x_last_update_login := -1;
680 END IF;
681 ELSE
682 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
683 igs_ge_msg_stack.add;
684 app_exception.raise_exception;
685 END IF;
686 before_dml(
687 p_action=>'UPDATE',
688 x_rowid=>x_rowid,
689 x_unit_section_reference_cd_id=>x_unit_section_reference_cd_id,
690 x_unit_section_reference_id=>x_unit_section_reference_id,
691 x_creation_date=>x_last_update_date,
692 x_created_by=>x_last_updated_by,
693 x_last_update_date=>x_last_update_date,
694 x_last_updated_by=>x_last_updated_by,
695 x_last_update_login=>x_last_update_login,
696 x_reference_code_type=>x_reference_code_type,
697 x_reference_code=>x_reference_code,
698 x_reference_code_desc=>x_reference_code_desc
699 );
700 UPDATE igs_ps_usec_ref_cd SET
701 unit_section_reference_id = new_references.unit_section_reference_id,
702 last_update_date = x_last_update_date,
703 last_updated_by = x_last_updated_by,
704 last_update_login = x_last_update_login,
705 reference_code_type= x_reference_code_type,
706 reference_code = x_reference_code,
707 reference_code_desc = x_reference_code_desc
708
709 WHERE ROWID = x_rowid;
710 IF (SQL%NOTFOUND) THEN
711 RAISE NO_DATA_FOUND;
712 END IF;
713
714 after_dml (
715 p_action => 'UPDATE' ,
716 x_rowid => x_rowid
717 );
718 END update_row;
719 PROCEDURE add_row (
720 x_rowid IN OUT NOCOPY VARCHAR2,
721 x_unit_section_reference_cd_id IN OUT NOCOPY NUMBER,
722 x_unit_section_reference_id IN NUMBER,
723 x_mode IN VARCHAR2 DEFAULT 'R' ,
724 x_reference_code_type IN VARCHAR2 ,
725 x_reference_code IN VARCHAR2 ,
726 x_reference_code_desc IN VARCHAR2
727 ) AS
728 /*************************************************************
729 Created By :
730 Date Created By :
731 Purpose :
732 Know limitations, enhancements or remarks
733 Change History
734 Who When What
735
736 (reverse chronological order - newest change first)
737 ***************************************************************/
738
739 CURSOR c1 IS SELECT ROWID FROM igs_ps_usec_ref_cd
740 WHERE unit_section_reference_cd_id= x_unit_section_reference_cd_id;
741 BEGIN
742 OPEN c1;
743 FETCH c1 INTO x_rowid;
744 IF (c1%NOTFOUND) THEN
745 CLOSE c1;
746 insert_row (
747 x_rowid,
748 x_unit_section_reference_cd_id,
749 x_unit_section_reference_id,
750 x_mode ,
751 x_reference_code_type,
752 x_reference_code,
753 x_reference_code_desc);
754 RETURN;
755 END IF;
756 CLOSE c1;
757 update_row (
758 x_rowid,
759 x_unit_section_reference_cd_id,
760 x_unit_section_reference_id,
761 x_mode,
762 x_reference_code_type,
763 x_reference_code,
764 x_reference_code_desc);
765 END add_row;
766 PROCEDURE delete_row (
767 x_rowid IN VARCHAR2
768 ) AS
769 /*************************************************************
770 Created By :
771 Date Created By :
772 Purpose :
773 Know limitations, enhancements or remarks
774 Change History
775 Who When What
776
777 (reverse chronological order - newest change first)
778 ***************************************************************/
779 BEGIN
780 before_dml (
781 p_action => 'DELETE',
782 x_rowid => x_rowid
783 );
784 DELETE FROM igs_ps_usec_ref_cd
785 WHERE ROWID = x_rowid;
786 IF (SQL%NOTFOUND) THEN
787 RAISE NO_DATA_FOUND;
788 END IF;
789 after_dml (
790 p_action => 'DELETE',
791 x_rowid => x_rowid
792 );
793 END delete_row;
794
795 END igs_ps_usec_ref_cd_pkg;
[Home] [Help]