1 PACKAGE BODY igs_ge_ref_cd_pkg AS
2 /* $Header: IGSMI16B.pls 120.1 2005/07/11 03:07:27 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ge_ref_cd%RowType;
5 new_references igs_ge_ref_cd%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_reference_code_id IN NUMBER DEFAULT NULL,
11 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
12 x_reference_cd IN VARCHAR2 DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_default_flag 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 /*************************************************************
23 Created By : sbeerell
24 Date Created By : 10-MAY-2000
25 Purpose :
26 Know limitations, enhancements or remarks
27 Change History
28 Who When What
29
30 (reverse chronological order - newest change first)
31 ***************************************************************/
32
33 CURSOR cur_old_ref_values IS
34 SELECT *
35 FROM IGS_GE_REF_CD
36 WHERE rowid = x_rowid;
37
38 BEGIN
39
40 l_rowid := x_rowid;
41
42 -- Code for setting the Old and New Reference Values.
43 -- Populate Old Values.
44 Open cur_old_ref_values;
45 Fetch cur_old_ref_values INTO old_references;
46 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
47 Close cur_old_ref_values;
48 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
49 IGS_GE_MSG_STACK.ADD;
50 App_Exception.Raise_Exception;
51 Return;
52 END IF;
53 Close cur_old_ref_values;
54
55 -- Populate New Values.
56 new_references.reference_code_id := x_reference_code_id;
57 new_references.reference_cd_type := x_reference_cd_type;
58 new_references.reference_cd := x_reference_cd;
59 new_references.description := x_description;
60 new_references.default_flag := x_default_flag;
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 new_references.last_update_date := x_last_update_date;
69 new_references.last_updated_by := x_last_updated_by;
70 new_references.last_update_login := x_last_update_login;
71
72 END Set_Column_Values;
73
74 PROCEDURE Check_Constraints (
75 Column_Name IN VARCHAR2 DEFAULT NULL,
76 Column_Value IN VARCHAR2 DEFAULT NULL ) AS
77 /*************************************************************
78 Created By : sbeerell
79 Date Created By : 10-MAY-2000
80 Purpose :
81 Know limitations, enhancements or remarks
82 Change History
83 Who When What
84
85 (reverse chronological order - newest change first)
86 ***************************************************************/
87
88 BEGIN
89
90 IF column_name IS NULL THEN
91 NULL;
92 NULL;
93 END IF;
94
95
96
97
98 END Check_Constraints;
99
100 PROCEDURE Check_Uniqueness AS
101 /*************************************************************
102 Created By : sbeerell
103 Date Created By :10-MAY-2000
104 Purpose :
105 Know limitations, enhancements or remarks
106 Change History
107 Who When What
108
109 (reverse chronological order - newest change first)
110 ***************************************************************/
111
112 begin
113 IF Get_Uk_For_Validation (
114 new_references.reference_cd_type
115 ,new_references.reference_cd
116 ) THEN
117 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
118 IGS_GE_MSG_STACK.ADD;
119 app_exception.raise_exception;
120 END IF;
121 END Check_Uniqueness ;
122 PROCEDURE Check_Parent_Existance AS
123 /*************************************************************
124 Created By : sbeerell
125 Date Created By : 10-MAY-2000
126 Purpose :
127 Know limitations, enhancements or remarks
128 Change History
129 Who When What
130
131 (reverse chronological order - newest change first)
132 ***************************************************************/
133
134 BEGIN
135
136 IF (((old_references.reference_cd_type = new_references.reference_cd_type)) OR
137 ((new_references.reference_cd_type IS NULL))) THEN
138 NULL;
139 ELSIF NOT Igs_Ge_Ref_Cd_Type_Pkg.Get_PK_For_Validation (
140 new_references.reference_cd_type
141 ) THEN
142 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END IF;
146
147 END Check_Parent_Existance;
148
149 PROCEDURE Check_Child_Existance IS
150 /*************************************************************
151 Created By : sbeerell
152 Date Created By : 10-MAY-2000
153 Purpose :
154 Know limitations, enhancements or remarks
155 Change History
156 Who When What
157 swaghmar 11-JULY-2005 Bug#4327987 Added a call to
158 igs_as_sua_ref_cds_pkg.get_UFK_Igs_As_Sua_Ref_Cds
159 to check for existing child
160 Student Unit Reference Codes
161 (reverse chronological order - newest change first)
162 ***************************************************************/
163
164 BEGIN
165 igs_ps_ref_cd_pkg.Get_UFK_Igs_Ge_Ref_Cd (
166 old_references.reference_cd_type,
167 old_references.reference_cd);
168
169 igs_ps_ent_pt_ref_cd_pkg.Get_UFK_Igs_Ge_Ref_Cd (
170 old_references.reference_cd_type,
171 old_references.reference_cd);
172
173 Igs_Ps_Usec_Ref_Cd_Pkg.Get_UFK_Igs_Ge_Ref_Cd (
174 old_references.reference_cd_type,
175 old_references.reference_cd);
176
177 igs_ps_unitreqref_cd_pkg.get_UFK_Igs_Ge_Ref_Cd (
178 old_references.reference_cd_type,
179 old_references.reference_cd
180 );
181 igs_ps_us_req_ref_cd_pkg.get_UFK_Igs_Ge_Ref_Cd (
182 old_references.reference_cd_type,
183 old_references.reference_cd);
184
185 igs_ps_usec_ocur_ref_pkg.get_UFK_Igs_Ge_Ref_Cd (
186 old_references.reference_cd_type,
187 old_references.reference_cd);
188
189 igs_ps_unit_ref_cd_pkg.get_UFK_Igs_Ge_Ref_Cd (
190 old_references.reference_cd_type,
191 old_references.reference_cd);
192
193 -- swaghmar 11-JULY-2005 Bug#4327987
194 -- Added a call to igs_as_sua_ref_cds_pkg.get_UFK_Igs_As_Sua_Ref_Cds to check for existing
195 -- child Student Unit Reference Codes
196
197 igs_as_sua_ref_cds_pkg.get_UFK_Igs_As_Sua_Ref_Cds (
198 old_references.reference_cd_type,
199 old_references.reference_cd);
200
201 END Check_Child_Existance;
202
203 FUNCTION Get_PK_For_Validation (
204 x_reference_code_id IN NUMBER
205 ) RETURN BOOLEAN AS
206
207 /*************************************************************
208 Created By : sbeerell
209 Date Created By : 10-MAY-2000
210 Purpose :
211 Know limitations, enhancements or remarks
212 Change History
213 Who When What
214
215 (reverse chronological order - newest change first)
216 ***************************************************************/
217
218 CURSOR cur_rowid IS
219 SELECT rowid
220 FROM igs_ge_ref_cd
221 WHERE reference_code_id = x_reference_code_id
222 FOR UPDATE NOWAIT;
223
224 lv_rowid cur_rowid%RowType;
225
226 BEGIN
227
228 Open cur_rowid;
229 Fetch cur_rowid INTO lv_rowid;
230 IF (cur_rowid%FOUND) THEN
231 Close cur_rowid;
232 Return(TRUE);
233 ELSE
234 Close cur_rowid;
235 Return(FALSE);
236 END IF;
237 END Get_PK_For_Validation;
238
239 FUNCTION Get_UK_For_Validation (
240 x_reference_cd_type IN VARCHAR2,
241 x_reference_cd IN VARCHAR2
242 ) RETURN BOOLEAN AS
243
244 /*************************************************************
245 Created By : sbeerell
246 Date Created By : 10-MAY-2000
247 Purpose :
248 Know limitations, enhancements or remarks
249 Change History
250 Who When What
251
252 (reverse chronological order - newest change first)
253 ***************************************************************/
254
255 CURSOR cur_rowid IS
256 SELECT rowid
257 FROM igs_ge_ref_cd
258 WHERE reference_cd_type = x_reference_cd_type
259 AND reference_cd = x_reference_cd and ((l_rowid is null) or (rowid <> l_rowid))
260
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 return (true);
271 ELSE
272 close cur_rowid;
273 return(false);
274 END IF;
275 END Get_UK_For_Validation ;
276 PROCEDURE Get_FK_Igs_Ge_Ref_Cd_Type (
277 x_reference_cd_type IN VARCHAR2
278 ) AS
279
280 /*************************************************************
281 Created By : sbeerell
282 Date Created By : 10-MAY-2000
283 Purpose :
284 Know limitations, enhancements or remarks
285 Change History
286 Who When What
287
288 (reverse chronological order - newest change first)
289 ***************************************************************/
290
291 CURSOR cur_rowid IS
292 SELECT rowid
293 FROM igs_ge_ref_cd
294 WHERE reference_cd_type = x_reference_cd_type ;
295
296 lv_rowid cur_rowid%RowType;
297
298 BEGIN
299
300 Open cur_rowid;
301 Fetch cur_rowid INTO lv_rowid;
302 IF (cur_rowid%FOUND) THEN
303 Close cur_rowid;
304 Fnd_Message.Set_Name ('IGS', 'IGS_PS_RC_RCT_FK');
305 IGS_GE_MSG_STACK.ADD;
306 App_Exception.Raise_Exception;
307 Return;
308 END IF;
309 Close cur_rowid;
310
311 END Get_FK_Igs_Ge_Ref_Cd_Type;
312
313 PROCEDURE Before_DML (
314 p_action IN VARCHAR2,
315 x_rowid IN VARCHAR2 DEFAULT NULL,
316 x_reference_code_id IN NUMBER DEFAULT NULL,
317 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
318 x_reference_cd IN VARCHAR2 DEFAULT NULL,
319 x_description IN VARCHAR2 DEFAULT NULL,
320 x_default_flag IN VARCHAR2 DEFAULT NULL,
321 x_creation_date IN DATE DEFAULT NULL,
322 x_created_by IN NUMBER DEFAULT NULL,
323 x_last_update_date IN DATE DEFAULT NULL,
324 x_last_updated_by IN NUMBER DEFAULT NULL,
325 x_last_update_login IN NUMBER DEFAULT NULL
326 ) AS
327 /*************************************************************
328 Created By : sbeerell
329 Date Created By : 10-MAY-2000
330 Purpose :
331 Know limitations, enhancements or remarks
332 Change History
333 Who When What
334
335 (reverse chronological order - newest change first)
336 ***************************************************************/
337
338 BEGIN
339
340 Set_Column_Values (
341 p_action,
342 x_rowid,
343 x_reference_code_id,
344 x_reference_cd_type,
345 x_reference_cd,
346 x_description,
347 x_default_flag,
348 x_creation_date,
349 x_created_by,
350 x_last_update_date,
351 x_last_updated_by,
352 x_last_update_login
353 );
354
355 IF (p_action = 'INSERT') THEN
356 -- Call all the procedures related to Before Insert.
357 Null;
358 IF Get_Pk_For_Validation(
359 new_references.reference_code_id) THEN
360 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
361 IGS_GE_MSG_STACK.ADD;
362 App_Exception.Raise_Exception;
363 END IF;
364 Check_Uniqueness;
365 Check_Constraints;
366 Check_Parent_Existance;
367 ELSIF (p_action = 'UPDATE') THEN
368 -- Call all the procedures related to Before Update.
369 Null;
370 Check_Uniqueness;
371 Check_Constraints;
372 Check_Parent_Existance;
373 ELSIF (p_action = 'DELETE') THEN
374 -- Call all the procedures related to Before Delete.
375 Null;
376 Check_Child_Existance;
377 ELSIF (p_action = 'VALIDATE_INSERT') THEN
378 -- Call all the procedures related to Before Insert.
379 IF Get_PK_For_Validation (
380 new_references.reference_code_id) THEN
381 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
382 IGS_GE_MSG_STACK.ADD;
383 App_Exception.Raise_Exception;
384 END IF;
385 Check_Uniqueness;
386 Check_Constraints;
387 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
388 Check_Uniqueness;
389 Check_Constraints;
390 ELSIF (p_action = 'VALIDATE_DELETE') THEN
391 Check_Child_Existance;
392 END IF;
393
394 l_rowid :=NULL;
395
396 END Before_DML;
397
398 PROCEDURE After_DML (
399 p_action IN VARCHAR2,
400 x_rowid IN VARCHAR2
401 ) IS
402 /*************************************************************
403 Created By : sbeerell
404 Date Created By : 10-MAY-2000
405 Purpose :
406 Know limitations, enhancements or remarks
407 Change History
408 Who When What
409
410 (reverse chronological order - newest change first)
411 ***************************************************************/
412
413 BEGIN
414
415 l_rowid := x_rowid;
416
417 IF (p_action = 'INSERT') THEN
418 -- Call all the procedures related to After Insert.
419 Null;
420 ELSIF (p_action = 'UPDATE') THEN
421 -- Call all the procedures related to After Update.
422 Null;
423 ELSIF (p_action = 'DELETE') THEN
424 -- Call all the procedures related to After Delete.
425 Null;
426 END IF;
427
428 l_rowid :=NULL;
429
430 END After_DML;
431
432 procedure INSERT_ROW (
433 X_ROWID in out NOCOPY VARCHAR2,
434 x_REFERENCE_CODE_ID IN OUT NOCOPY NUMBER,
435 x_REFERENCE_CD_TYPE IN VARCHAR2,
436 x_REFERENCE_CD IN VARCHAR2,
437 x_DESCRIPTION IN VARCHAR2,
438 x_DEFAULT_FLAG IN VARCHAR2,
439 X_MODE in VARCHAR2 default 'R'
440 ) AS
441 /*************************************************************
442 Created By : sbeerell
443 Date Created By : 10-MAY-2000
444 Purpose :
445 Know limitations, enhancements or remarks
446 Change History
447 Who When What
448
449 (reverse chronological order - newest change first)
450 ***************************************************************/
451
452 cursor C is select ROWID from IGS_GE_REF_CD
453 where REFERENCE_CODE_ID= X_REFERENCE_CODE_ID
454 ;
455 X_LAST_UPDATE_DATE DATE ;
456 X_LAST_UPDATED_BY NUMBER ;
457 X_LAST_UPDATE_LOGIN NUMBER ;
458 begin
459 X_LAST_UPDATE_DATE := SYSDATE;
460 if(X_MODE = 'I') then
461 X_LAST_UPDATED_BY := 1;
462 X_LAST_UPDATE_LOGIN := 0;
463 elsif (X_MODE = 'R') then
464 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
465 if X_LAST_UPDATED_BY is NULL then
466 X_LAST_UPDATED_BY := -1;
467 end if;
468 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
469 if X_LAST_UPDATE_LOGIN is NULL then
470 X_LAST_UPDATE_LOGIN := -1;
471 end if;
472 else
473 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
474 IGS_GE_MSG_STACK.ADD;
475 app_exception.raise_exception;
476 end if;
477 SELECT IGS_GE_REF_CD_S.NEXTVAL
478 INTO X_REFERENCE_CODE_ID FROM Dual;
479 Before_DML(
480 p_action=>'INSERT',
481 x_rowid=>X_ROWID,
482 x_reference_code_id=>X_REFERENCE_CODE_ID,
483 x_reference_cd_type=>X_REFERENCE_CD_TYPE,
484 x_reference_cd=>X_REFERENCE_CD,
485 x_description=>X_DESCRIPTION,
486 x_default_flag=>X_DEFAULT_FLAG,
487 x_creation_date=>X_LAST_UPDATE_DATE,
488 x_created_by=>X_LAST_UPDATED_BY,
489 x_last_update_date=>X_LAST_UPDATE_DATE,
490 x_last_updated_by=>X_LAST_UPDATED_BY,
491 x_last_update_login=>X_LAST_UPDATE_LOGIN);
492 insert into IGS_GE_REF_CD (
493 REFERENCE_CODE_ID
494 ,REFERENCE_CD_TYPE
495 ,REFERENCE_CD
496 ,DESCRIPTION
497 ,DEFAULT_FLAG
498 ,CREATION_DATE
499 ,CREATED_BY
500 ,LAST_UPDATE_DATE
501 ,LAST_UPDATED_BY
502 ,LAST_UPDATE_LOGIN
503 ) values (
504 NEW_REFERENCES.REFERENCE_CODE_ID
505 ,NEW_REFERENCES.REFERENCE_CD_TYPE
506 ,NEW_REFERENCES.REFERENCE_CD
507 ,NEW_REFERENCES.DESCRIPTION
508 ,NEW_REFERENCES.DEFAULT_FLAG
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 );
515 open c;
516 fetch c into X_ROWID;
517 if (c%notfound) then
518 close c;
519 raise no_data_found;
520 end if;
521 close c;
522 After_DML (
523 p_action => 'INSERT' ,
524 x_rowid => X_ROWID );
525 end INSERT_ROW;
526 procedure LOCK_ROW (
527 X_ROWID in VARCHAR2,
528 x_REFERENCE_CODE_ID IN NUMBER,
529 x_REFERENCE_CD_TYPE IN VARCHAR2,
530 x_REFERENCE_CD IN VARCHAR2,
531 x_DESCRIPTION IN VARCHAR2,
532 x_DEFAULT_FLAG IN VARCHAR2 ) AS
533 /*************************************************************
534 Created By : sbeerell
535 Date Created By : 10-MAY-2000
536 Purpose :
537 Know limitations, enhancements or remarks
538 Change History
539 Who When What
540
541 (reverse chronological order - newest change first)
542 ***************************************************************/
543
544 cursor c1 is select
545 REFERENCE_CD_TYPE
546 , REFERENCE_CD
547 , DESCRIPTION
548 , DEFAULT_FLAG
549 from IGS_GE_REF_CD
550 where ROWID = X_ROWID
551 for update nowait;
552 tlinfo c1%rowtype;
553 begin
554 open c1;
555 fetch c1 into tlinfo;
556 if (c1%notfound) then
557 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
558 IGS_GE_MSG_STACK.ADD;
559 close c1;
560 app_exception.raise_exception;
561 return;
562 end if;
563 close c1;
564 if ( ( tlinfo.REFERENCE_CD_TYPE = X_REFERENCE_CD_TYPE)
565 AND (tlinfo.REFERENCE_CD = X_REFERENCE_CD)
566 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
567 AND (tlinfo.DEFAULT_FLAG = X_DEFAULT_FLAG)
568 ) then
569 null;
570 else
571 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
572 IGS_GE_MSG_STACK.ADD;
573 app_exception.raise_exception;
574 end if;
575 return;
576 end LOCK_ROW;
577 Procedure UPDATE_ROW (
578 X_ROWID in VARCHAR2,
579 x_REFERENCE_CODE_ID IN NUMBER,
580 x_REFERENCE_CD_TYPE IN VARCHAR2,
581 x_REFERENCE_CD IN VARCHAR2,
582 x_DESCRIPTION IN VARCHAR2,
583 x_DEFAULT_FLAG IN VARCHAR2,
584 X_MODE in VARCHAR2 default 'R'
585 ) AS
586 /*************************************************************
587 Created By : sbeerell
588 Date Created By : 10-MAY-2000
589 Purpose :
590 Know limitations, enhancements or remarks
591 Change History
592 Who When What
593
594 (reverse chronological order - newest change first)
595 ***************************************************************/
596
597 X_LAST_UPDATE_DATE DATE ;
598 X_LAST_UPDATED_BY NUMBER ;
599 X_LAST_UPDATE_LOGIN NUMBER ;
600 begin
601 X_LAST_UPDATE_DATE := SYSDATE;
602 if(X_MODE = 'I') then
603 X_LAST_UPDATED_BY := 1;
604 X_LAST_UPDATE_LOGIN := 0;
605 elsif (X_MODE = 'R') then
606 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
607 if X_LAST_UPDATED_BY is NULL then
608 X_LAST_UPDATED_BY := -1;
609 end if;
610 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
611 if X_LAST_UPDATE_LOGIN is NULL then
612 X_LAST_UPDATE_LOGIN := -1;
613 end if;
614 else
615 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
616 IGS_GE_MSG_STACK.ADD;
617 app_exception.raise_exception;
618 end if;
619 Before_DML(
620 p_action=>'UPDATE',
621 x_rowid=>X_ROWID,
622 x_reference_code_id=>X_REFERENCE_CODE_ID,
623 x_reference_cd_type=>X_REFERENCE_CD_TYPE,
624 x_reference_cd=>X_REFERENCE_CD,
625 x_description=>X_DESCRIPTION,
626 x_default_flag=>X_DEFAULT_FLAG,
627 x_creation_date=>X_LAST_UPDATE_DATE,
628 x_created_by=>X_LAST_UPDATED_BY,
629 x_last_update_date=>X_LAST_UPDATE_DATE,
630 x_last_updated_by=>X_LAST_UPDATED_BY,
631 x_last_update_login=>X_LAST_UPDATE_LOGIN);
632 update IGS_GE_REF_CD set
633 REFERENCE_CD_TYPE = NEW_REFERENCES.REFERENCE_CD_TYPE,
634 REFERENCE_CD = NEW_REFERENCES.REFERENCE_CD,
635 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
636 DEFAULT_FLAG = NEW_REFERENCES.DEFAULT_FLAG,
637 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
638 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
639 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
640 where ROWID = X_ROWID;
641 if (sql%notfound) then
642 raise no_data_found;
643 end if;
644
645 After_DML (
646 p_action => 'UPDATE' ,
647 x_rowid => X_ROWID
648 );
649 end UPDATE_ROW;
650 procedure ADD_ROW (
651 X_ROWID in out NOCOPY VARCHAR2,
652 x_REFERENCE_CODE_ID IN OUT NOCOPY NUMBER,
653 x_REFERENCE_CD_TYPE IN VARCHAR2,
654 x_REFERENCE_CD IN VARCHAR2,
655 x_DESCRIPTION IN VARCHAR2,
656 x_DEFAULT_FLAG IN VARCHAR2,
657 X_MODE in VARCHAR2 default 'R'
658 ) AS
659 /*************************************************************
660 Created By : sbeerell
661 Date Created By : 10-MAY-2000
662 Purpose :
663 Know limitations, enhancements or remarks
664 Change History
665 Who When What
666
667 (reverse chronological order - newest change first)
668 ***************************************************************/
669
670 cursor c1 is select ROWID from IGS_GE_REF_CD
671 where REFERENCE_CODE_ID= X_REFERENCE_CODE_ID
672 ;
673 begin
674 open c1;
675 fetch c1 into X_ROWID;
676 if (c1%notfound) then
677 close c1;
678 INSERT_ROW (
679 X_ROWID,
680 X_REFERENCE_CODE_ID,
681 X_REFERENCE_CD_TYPE,
682 X_REFERENCE_CD,
683 X_DESCRIPTION,
684 X_DEFAULT_FLAG,
685 X_MODE );
686 return;
687 end if;
688 close c1;
689 UPDATE_ROW (
690 X_ROWID,
691 X_REFERENCE_CODE_ID,
692 X_REFERENCE_CD_TYPE,
693 X_REFERENCE_CD,
694 X_DESCRIPTION,
695 X_DEFAULT_FLAG,
696 X_MODE );
697 end ADD_ROW;
698 procedure DELETE_ROW (
699 X_ROWID in VARCHAR2
700 ) AS
701 /*************************************************************
702 Created By : sbeerell
703 Date Created By : 10-MAY-2000
704 Purpose :
705 Know limitations, enhancements or remarks
706 Change History
707 Who When What
708
709 (reverse chronological order - newest change first)
710 ***************************************************************/
711
712 begin
713 Before_DML (
714 p_action => 'DELETE',
715 x_rowid => X_ROWID
716 );
717 delete from IGS_GE_REF_CD
718 where ROWID = X_ROWID;
719 if (sql%notfound) then
720 raise no_data_found;
721 end if;
722 After_DML (
723 p_action => 'DELETE',
724 x_rowid => X_ROWID
725 );
726 end DELETE_ROW;
727 END igs_ge_ref_cd_pkg;