DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_PS_OCCUP_TITLES_PKG

Source


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