eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_AD_OS_SEC_EDU_QF_PKG

Source


1 package body IGS_AD_OS_SEC_EDU_QF_PKG as
2 /* $Header: IGSAI41B.pls 115.4 2003/10/30 13:12:50 akadam ship $ */
3 
4 
5   l_rowid VARCHAR2(25);
6   old_references IGS_AD_OS_SEC_EDU_QF%RowType;
7   new_references IGS_AD_OS_SEC_EDU_QF%RowType;
8 
9   PROCEDURE Set_Column_Values (
10     p_action IN VARCHAR2,
11     x_rowid IN VARCHAR2 DEFAULT NULL,
12     x_os_scndry_edu_qualification IN VARCHAR2 DEFAULT NULL,
13     x_description IN VARCHAR2 DEFAULT NULL,
14     x_country_cd IN VARCHAR2 DEFAULT NULL,
15     x_closed_ind IN VARCHAR2 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   ) as
22 
23     CURSOR cur_old_ref_values IS
24       SELECT   *
25       FROM     IGS_AD_OS_SEC_EDU_QF
26       WHERE    rowid = x_rowid;
27 
28   BEGIN
29 
30     l_rowid := x_rowid;
31 
32     -- Code for setting the Old and New Reference Values.
33     -- Populate Old Values.
34     Open cur_old_ref_values;
35     Fetch cur_old_ref_values INTO old_references;
36     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
37       Close cur_old_ref_values;
38       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39       IGS_GE_MSG_STACK.ADD;
40       App_Exception.Raise_Exception;
41       Return;
42     END IF;
43     Close cur_old_ref_values;
44 
45     -- Populate New Values.
46     new_references.os_scndry_edu_qualification := x_os_scndry_edu_qualification;
47     new_references.description := x_description;
48     new_references.country_cd  := x_country_cd;
49     new_references.closed_ind := x_closed_ind;
50     IF (p_action = 'UPDATE') THEN
51       new_references.creation_date := old_references.creation_date;
52       new_references.created_by := old_references.created_by;
53     ELSE
54       new_references.creation_date := x_creation_date;
55       new_references.created_by := x_created_by;
56     END IF;
57     new_references.last_update_date := x_last_update_date;
58     new_references.last_updated_by := x_last_updated_by;
59     new_references.last_update_login := x_last_update_login;
60 
61   END Set_Column_Values;
62 
63   procedure Check_Constraints (
64     Column_Name IN VARCHAR2 DEFAULT NULL,
65     Column_Value IN VARCHAR2 DEFAULT NULL
66   )
67   as
68   BEGIN
69 	IF Column_Name is null then
70 		NULL;
71 	ELSIF upper(Column_Name) = 'OS_SCNDRY_EDU_QUALIFICATION' then
72 		new_references.os_scndry_edu_qualification := column_value;
73 	ELSIF upper(Column_Name) = 'COUNTRY_CD' then
74 		new_references.country_cd := column_value;
75 	ELSIF upper(Column_Name) = 'CLOSED_IND' then
76 		new_references.closed_ind := column_value;
77 	END IF;
78 
79 	IF upper(Column_Name) = 'OS_SCNDRY_EDU_QUALIFICATION' OR Column_Name IS NULL THEN
80 		IF new_references.os_scndry_edu_qualification <> UPPER(new_references.os_scndry_edu_qualification) THEN
81 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
82 			IGS_GE_MSG_STACK.ADD;
83 			App_Exception.Raise_Exception;
84 		END IF;
85 	END IF;
86 	IF upper(Column_Name) = 'COUNTRY_CD' OR Column_Name IS NULL THEN
87 		IF new_references.country_cd <> UPPER(new_references.country_cd) THEN
88 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
89 			IGS_GE_MSG_STACK.ADD;
90 			App_Exception.Raise_Exception;
91 		END IF;
92 	END IF;
93 	IF upper(Column_Name) = 'CLOSED_IND' OR Column_Name IS NULL THEN
94 		IF new_references.closed_ind  NOT IN ('Y','N') THEN
95 			Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
96 			IGS_GE_MSG_STACK.ADD;
97 			App_Exception.Raise_Exception;
98 		END IF;
99 	END IF;
100 
101   END Check_Constraints;
102 
103   PROCEDURE Check_Parent_Existance as
104   BEGIN
105 
106     IF (((old_references.country_cd = new_references.country_cd)) OR
107         ((new_references.country_cd IS NULL))) THEN
108       NULL;
109     ELSE
110       IF NOT IGS_PE_COUNTRY_CD_PKG.Get_PK_For_Validation (
111         new_references.country_cd
112 	) THEN
113 	Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
114 	IGS_GE_MSG_STACK.ADD;
115 	App_Exception.Raise_Exception;
116 	END IF;
117     END IF;
118 
119   END Check_Parent_Existance;
120 
121   PROCEDURE Check_Child_Existance as
122   BEGIN
123 
124     IGS_AD_OS_SEC_EDU_PKG.GET_FK_IGS_AD_OS_SEC_EDU_QF (
125       old_references.os_scndry_edu_qualification
126       );
127 
128   END Check_Child_Existance;
129 
130 FUNCTION Get_PK_For_Validation (
131     x_os_scndry_edu_qualification IN VARCHAR2,
132     x_closed_ind IN VARCHAR2
133 )return BOOLEAN as
134 
135     CURSOR cur_rowid IS
136       SELECT   rowid
137       FROM     IGS_AD_OS_SEC_EDU_QF
138       WHERE    os_scndry_edu_qualification = x_os_scndry_edu_qualification AND
139                closed_ind = NVL(x_closed_ind,closed_ind)
140       FOR UPDATE NOWAIT;
141 
142     lv_rowid cur_rowid%RowType;
143 
144   BEGIN
145 
146     Open cur_rowid;
147     Fetch cur_rowid INTO lv_rowid;
148     IF (cur_rowid%FOUND) THEN
149       Close cur_rowid;
150       Return(TRUE);
151     ELSE
152       Close cur_rowid;
153       Return(FALSE);
154     END IF;
155 
156   END Get_PK_For_Validation;
157 
158   PROCEDURE GET_FK_IGS_PE_COUNTRY_CD (
159     x_country_cd IN VARCHAR2
160     ) as
161 
162     CURSOR cur_rowid IS
163       SELECT   rowid
164       FROM     IGS_AD_OS_SEC_EDU_QF
165       WHERE    country_cd = x_country_cd ;
166 
167     lv_rowid cur_rowid%RowType;
168 
169   BEGIN
170 
171     Open cur_rowid;
172     Fetch cur_rowid INTO lv_rowid;
173     IF (cur_rowid%FOUND) THEN
174       Close cur_rowid;
175       Fnd_Message.Set_Name ('IGS', 'IGS_AD_OSEQ_CNC_FK');
176       IGS_GE_MSG_STACK.ADD;
177       App_Exception.Raise_Exception;
178       Return;
179     END IF;
180     Close cur_rowid;
181 
182   END GET_FK_IGS_PE_COUNTRY_CD;
183 
184   PROCEDURE Before_DML (
185     p_action IN VARCHAR2,
186     x_rowid IN VARCHAR2 DEFAULT NULL,
187     x_os_scndry_edu_qualification IN VARCHAR2 DEFAULT NULL,
188     x_description IN VARCHAR2 DEFAULT NULL,
189     x_country_cd IN VARCHAR2 DEFAULT NULL,
190     x_closed_ind IN VARCHAR2 DEFAULT NULL,
191     x_creation_date IN DATE DEFAULT NULL,
192     x_created_by IN NUMBER DEFAULT NULL,
193     x_last_update_date IN DATE DEFAULT NULL,
194     x_last_updated_by IN NUMBER DEFAULT NULL,
195     x_last_update_login IN NUMBER DEFAULT NULL
196   ) as
197   BEGIN
198 
199     Set_Column_Values (
200       p_action,
201       x_rowid,
202       x_os_scndry_edu_qualification,
203       x_description,
204       x_country_cd,
205       x_closed_ind,
206       x_creation_date,
207       x_created_by,
208       x_last_update_date,
209       x_last_updated_by,
210       x_last_update_login
211     );
212 
213     IF (p_action = 'INSERT') THEN
214 	IF Get_PK_For_Validation (
215 		new_references.os_scndry_edu_qualification
216 	) THEN
217 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
218 		IGS_GE_MSG_STACK.ADD;
219 		App_Exception.Raise_Exception;
220 	END IF;
221 	Check_Constraints;
222       Check_Parent_Existance;
223     ELSIF (p_action = 'UPDATE') THEN
224 	Check_Constraints;
225       Check_Parent_Existance;
226     ELSIF (p_action = 'DELETE') THEN
227       Check_Child_Existance;
228     ELSIF (p_action = 'VALIDATE_INSERT') THEN
229 	IF Get_PK_For_Validation (
230 		new_references.os_scndry_edu_qualification
231 	) THEN
232 		Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
233 		IGS_GE_MSG_STACK.ADD;
234 		App_Exception.Raise_Exception;
235 	END IF;
236 	Check_Constraints;
237     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
238 	Check_Constraints;
239     ELSIF (p_action = 'VALIDATE_DELETE') THEN
240       Check_Child_Existance;
241     END IF;
242 
243   END Before_DML;
244 
245   PROCEDURE After_DML (
246     p_action IN VARCHAR2,
247     x_rowid IN VARCHAR2
248   ) as
249   BEGIN
250 
251     l_rowid := x_rowid;
252 
253   END After_DML;
254 
255 
256 procedure INSERT_ROW (
257   X_ROWID in out NOCOPY VARCHAR2,
258   X_OS_SCNDRY_EDU_QUALIFICATION in VARCHAR2,
259   X_DESCRIPTION in VARCHAR2,
260   X_COUNTRY_CD in VARCHAR2,
261   X_CLOSED_IND in VARCHAR2,
262   X_MODE in VARCHAR2 default 'R'
263   ) as
264     cursor C is select ROWID from IGS_AD_OS_SEC_EDU_QF
265       where OS_SCNDRY_EDU_QUALIFICATION = X_OS_SCNDRY_EDU_QUALIFICATION;
266     X_LAST_UPDATE_DATE DATE;
267     X_LAST_UPDATED_BY NUMBER;
268     X_LAST_UPDATE_LOGIN NUMBER;
269 begin
270   X_LAST_UPDATE_DATE := SYSDATE;
271   if(X_MODE = 'I') then
272     X_LAST_UPDATED_BY := 1;
273     X_LAST_UPDATE_LOGIN := 0;
274   elsif (X_MODE = 'R') then
275     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
276     if X_LAST_UPDATED_BY is NULL then
277       X_LAST_UPDATED_BY := -1;
278     end if;
279     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
280     if X_LAST_UPDATE_LOGIN is NULL then
281       X_LAST_UPDATE_LOGIN := -1;
282     end if;
283   else
284     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
285     IGS_GE_MSG_STACK.ADD;
286     app_exception.raise_exception;
287   end if;
288 
289 Before_DML(
290     p_action=>'INSERT' ,
291     x_rowid=>X_ROWID ,
292     x_os_scndry_edu_qualification => X_OS_SCNDRY_EDU_QUALIFICATION ,
293     x_description => X_DESCRIPTION ,
294     x_country_cd => X_COUNTRY_CD ,
295     x_closed_ind => NVL(X_CLOSED_IND,'N') ,
296     x_creation_date=>X_LAST_UPDATE_DATE ,
297     x_created_by=>X_LAST_UPDATED_BY   ,
298     x_last_update_date=>X_LAST_UPDATE_DATE ,
299     x_last_updated_by=>X_LAST_UPDATED_BY ,
300     x_last_update_login=> X_LAST_UPDATE_LOGIN
301        );
302 
303 
304   insert into IGS_AD_OS_SEC_EDU_QF (
305     OS_SCNDRY_EDU_QUALIFICATION,
306     DESCRIPTION,
307     COUNTRY_CD,
308     CLOSED_IND,
309     CREATION_DATE,
310     CREATED_BY,
311     LAST_UPDATE_DATE,
312     LAST_UPDATED_BY,
313     LAST_UPDATE_LOGIN
314   ) values (
315     NEW_REFERENCES.OS_SCNDRY_EDU_QUALIFICATION,
316     NEW_REFERENCES.DESCRIPTION,
317     NEW_REFERENCES.COUNTRY_CD,
318     NEW_REFERENCES.CLOSED_IND,
319     X_LAST_UPDATE_DATE,
320     X_LAST_UPDATED_BY,
321     X_LAST_UPDATE_DATE,
322     X_LAST_UPDATED_BY,
323     X_LAST_UPDATE_LOGIN
324   );
325 
326   open c;
327   fetch c into X_ROWID;
328   if (c%notfound) then
329     close c;
330     raise no_data_found;
331   end if;
332   close c;
333 
334 After_DML(
335   p_action=>'INSERT',
336   x_rowid=> X_ROWID
337          );
338 
339 
340 end INSERT_ROW;
341 
342 procedure LOCK_ROW (
343   X_ROWID in VARCHAR2 ,
344   X_OS_SCNDRY_EDU_QUALIFICATION in VARCHAR2,
345   X_DESCRIPTION in VARCHAR2,
346   X_COUNTRY_CD in VARCHAR2,
347   X_CLOSED_IND in VARCHAR2
348 ) as
349   cursor c1 is select
350       DESCRIPTION,
351       COUNTRY_CD,
352       CLOSED_IND
353     from IGS_AD_OS_SEC_EDU_QF
354     WHERE  ROWID = X_ROWID  for update nowait ;
355   tlinfo c1%rowtype;
356 
357 begin
358   open c1;
359   fetch c1 into tlinfo;
360   if (c1%notfound) then
361     close c1;
362     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
363     IGS_GE_MSG_STACK.ADD;
364     app_exception.raise_exception;
365     return;
366   end if;
367   close c1;
368 
369   if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
370       AND ((tlinfo.COUNTRY_CD = X_COUNTRY_CD)
371            OR ((tlinfo.COUNTRY_CD is null)
372                AND (X_COUNTRY_CD is null)))
373       AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
374   ) then
375     null;
376   else
377     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
378     IGS_GE_MSG_STACK.ADD;
379     app_exception.raise_exception;
380   end if;
381   return;
382 end LOCK_ROW;
383 
384 procedure UPDATE_ROW (
385   X_ROWID in VARCHAR2 ,
386   X_OS_SCNDRY_EDU_QUALIFICATION in VARCHAR2,
387   X_DESCRIPTION in VARCHAR2,
388   X_COUNTRY_CD in VARCHAR2,
389   X_CLOSED_IND in VARCHAR2,
390   X_MODE in VARCHAR2 default 'R'
391   ) as
392     X_LAST_UPDATE_DATE DATE;
393     X_LAST_UPDATED_BY NUMBER;
394     X_LAST_UPDATE_LOGIN NUMBER;
395 begin
396   X_LAST_UPDATE_DATE := SYSDATE;
397   if(X_MODE = 'I') then
398     X_LAST_UPDATED_BY := 1;
399     X_LAST_UPDATE_LOGIN := 0;
400   elsif (X_MODE = 'R') then
401     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
402     if X_LAST_UPDATED_BY is NULL then
403       X_LAST_UPDATED_BY := -1;
404     end if;
405     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
406     if X_LAST_UPDATE_LOGIN is NULL then
407       X_LAST_UPDATE_LOGIN := -1;
408     end if;
409   else
410     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
411     IGS_GE_MSG_STACK.ADD;
412     app_exception.raise_exception;
413   end if;
414 
415  Before_DML(
416     p_action=>'UPDATE' ,
417     x_rowid=>X_ROWID ,
418     x_os_scndry_edu_qualification => X_OS_SCNDRY_EDU_QUALIFICATION ,
419     x_description => X_DESCRIPTION ,
420     x_country_cd => X_COUNTRY_CD ,
421     x_closed_ind => X_CLOSED_IND ,
422     x_creation_date=>X_LAST_UPDATE_DATE ,
423     x_created_by=>X_LAST_UPDATED_BY  ,
424     x_last_update_date=>X_LAST_UPDATE_DATE ,
425     x_last_updated_by=>X_LAST_UPDATED_BY ,
426     x_last_update_login=> X_LAST_UPDATE_LOGIN
427        );
428 
429 
430   update IGS_AD_OS_SEC_EDU_QF set
431     DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
432     COUNTRY_CD = NEW_REFERENCES.COUNTRY_CD,
433     CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
434     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
435     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
436     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
437   where ROWID = X_ROWID
438   ;
439   if (sql%notfound) then
440     raise no_data_found;
441   end if;
442 
443 After_DML(
444   p_action=>'UPDATE',
445   x_rowid=> X_ROWID
446          );
447 
448 end UPDATE_ROW;
449 
450 procedure ADD_ROW (
451   X_ROWID in out NOCOPY VARCHAR2,
452   X_OS_SCNDRY_EDU_QUALIFICATION in VARCHAR2,
453   X_DESCRIPTION in VARCHAR2,
454   X_COUNTRY_CD in VARCHAR2,
455   X_CLOSED_IND in VARCHAR2,
456   X_MODE in VARCHAR2 default 'R'
457   ) as
458   cursor c1 is select rowid from IGS_AD_OS_SEC_EDU_QF
459      where OS_SCNDRY_EDU_QUALIFICATION = X_OS_SCNDRY_EDU_QUALIFICATION
460   ;
461 
462 begin
463   open c1;
464   fetch c1 into X_ROWID;
465   if (c1%notfound) then
466     close c1;
467     INSERT_ROW (
468      X_ROWID,
469      X_OS_SCNDRY_EDU_QUALIFICATION,
470      X_DESCRIPTION,
471      X_COUNTRY_CD,
472      X_CLOSED_IND,
473      X_MODE);
474     return;
475   end if;
476   close c1;
477   UPDATE_ROW (
478    X_ROWID,
479    X_OS_SCNDRY_EDU_QUALIFICATION,
480    X_DESCRIPTION,
481    X_COUNTRY_CD,
482    X_CLOSED_IND,
483    X_MODE);
484 end ADD_ROW;
485 
486 procedure DELETE_ROW (
487 X_ROWID in VARCHAR2
488 ) as
489 begin
490 
491 Before_DML(
492   p_action=>'DELETE',
493   x_rowid=> X_ROWID
494          );
495 
496   delete from IGS_AD_OS_SEC_EDU_QF
497   where ROWID = X_ROWID;
498   if (sql%notfound) then
499     raise no_data_found;
500   end if;
501 
502 After_DML(
503   p_action=>'DELETE',
504   x_rowid=> X_ROWID
505          );
506 
507 
508 end DELETE_ROW;
509 
510 end IGS_AD_OS_SEC_EDU_QF_PKG;