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_AS_EXM_LOC_SPVSR_PKG

Source


1 package body IGS_AS_EXM_LOC_SPVSR_PKG AS
2  /* $Header: IGSDI25B.pls 115.6 2003/10/30 13:27:53 rghosh ship $ */
3 
4    l_rowid VARCHAR2(25);
5   old_references IGS_AS_EXM_LOC_SPVSR%RowType;
6   new_references IGS_AS_EXM_LOC_SPVSR%RowType;
7 
8    PROCEDURE Set_Column_Values (
9     p_action IN VARCHAR2,
10     x_rowid IN VARCHAR2 DEFAULT NULL,
11     x_person_id IN NUMBER DEFAULT NULL,
12     x_exam_location_cd 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   ) AS
19 
20     CURSOR cur_old_ref_values IS
21       SELECT   *
22       FROM     IGS_AS_EXM_LOC_SPVSR
23       WHERE    rowid = x_rowid;
24 
25   BEGIN
26 
27     l_rowid := x_rowid;
28 
29     -- Code for setting the Old and New Reference Values.
30     -- Populate Old Values.
31     Open cur_old_ref_values;
32     Fetch cur_old_ref_values INTO old_references;
33     IF (cur_old_ref_values%NOTFOUND) AND (p_action  NOT IN ('INSERT','VALIDATE_INSERT')) THEN
34       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
35       IGS_GE_MSG_STACK.ADD;
36 	       Close cur_old_ref_values;
37       APP_EXCEPTION.RAISE_EXCEPTION;
38 
39       Return;
40     END IF;
41     Close cur_old_ref_values;
42 
43     -- Populate New Values.
44     new_references.person_id := x_person_id;
45     new_references.exam_location_cd := x_exam_location_cd;
46     IF (p_action = 'UPDATE') THEN
47       new_references.creation_date := old_references.creation_date;
48       new_references.created_by := old_references.created_by;
49     ELSE
50       new_references.creation_date := x_creation_date;
51       new_references.created_by := x_created_by;
52     END IF;
53     new_references.last_update_date := x_last_update_date;
54     new_references.last_updated_by := x_last_updated_by;
55     new_references.last_update_login := x_last_update_login;
56 
57   END Set_Column_Values;
58 
59 PROCEDURE BeforeRowInsert1(
60     p_inserting IN BOOLEAN DEFAULT FALSE,
61     p_updating IN BOOLEAN DEFAULT FALSE,
62     p_deleting IN BOOLEAN DEFAULT FALSE
63     ) AS
64 	v_message_name	VARCHAR2(30);
65   BEGIN
66 	-- Validate that the location is an exam location (ie. type of 'EXAM_CTR')
67 	IF IGS_AS_VAL_ELS.assp_val_ve_lot(new_references.exam_location_cd,
68 					v_message_name) = FALSE THEN
69 		FND_MESSAGE.SET_NAME('IGS',v_message_name);
70 		IGS_GE_MSG_STACK.ADD;
71 			APP_EXCEPTION.RAISE_EXCEPTION;
72 	END IF;
73 	-- Validate that the exam location is not closed.
74 	IF IGS_AS_VAL_ELS.orgp_val_loc_closed(new_references.exam_location_cd,
75 					v_message_name) = FALSE THEN
76 		FND_MESSAGE.SET_NAME('IGS',v_message_name);
77 		IGS_GE_MSG_STACK.ADD;
78 			APP_EXCEPTION.RAISE_EXCEPTION;
79 	END IF;
80 
81 
82   END BeforeRowInsert1;
83 
84   PROCEDURE Check_Parent_Existance AS
85   BEGIN
86 
87     IF (((old_references.person_id = new_references.person_id)) OR
88         ((new_references.person_id IS NULL))) THEN
89       NULL;
90     ELSIF NOT IGS_AS_EXM_SUPRVISOR_PKG.Get_PK_For_Validation (
91         new_references.person_id
92         )	THEN
93 	    Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
94 	    IGS_GE_MSG_STACK.ADD;
95 	    APP_EXCEPTION.RAISE_EXCEPTION;
96 
97     END IF;
98 
99     IF (((old_references.exam_location_cd = new_references.exam_location_cd)) OR
100         ((new_references.exam_location_cd IS NULL))) THEN
101       NULL;
102     ELSIF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
103         new_references.exam_location_cd ,
104             'N'
105         )	THEN
106 	    Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
107 	    IGS_GE_MSG_STACK.ADD;
108 	    APP_EXCEPTION.RAISE_EXCEPTION;
109 
110     END IF;
111 
112   END Check_Parent_Existance;
113 
114   FUNCTION Get_PK_For_Validation (
115     x_person_id IN NUMBER,
116     x_exam_location_cd IN VARCHAR2
117     ) RETURN BOOLEAN AS
118 
119     CURSOR cur_rowid IS
120       SELECT   rowid
121       FROM     IGS_AS_EXM_LOC_SPVSR
122       WHERE    person_id = x_person_id
123       AND      exam_location_cd = x_exam_location_cd
124       FOR UPDATE NOWAIT;
125 
126     lv_rowid cur_rowid%RowType;
127 
128   BEGIN
129 
130     Open cur_rowid;
131     Fetch cur_rowid INTO lv_rowid;
132 
133 	IF (cur_rowid%FOUND) THEN
134 	      Close cur_rowid;
135 	      Return (TRUE);
136 	ELSE
137 	      Close cur_rowid;
138 	      Return (FALSE);
139 	END IF;
140 
141   END Get_PK_For_Validation;
142 
143   PROCEDURE GET_FK_IGS_AS_EXM_SUPRVISOR (
144     x_person_id IN NUMBER
145     ) AS
146 
147     CURSOR cur_rowid IS
148       SELECT   rowid
149       FROM     IGS_AS_EXM_LOC_SPVSR
150       WHERE    person_id = x_person_id ;
151 
152     lv_rowid cur_rowid%RowType;
153 
154   BEGIN
155 
156     Open cur_rowid;
157     Fetch cur_rowid INTO lv_rowid;
158     IF (cur_rowid%FOUND) THEN
159       Fnd_Message.Set_Name ('IGS', 'IGS_AS_ELS_ESU_FK');
160       IGS_GE_MSG_STACK.ADD;
161 	        Close cur_rowid;
162       APP_EXCEPTION.RAISE_EXCEPTION;
163 
164       Return;
165     END IF;
166     Close cur_rowid;
167 
168   END GET_FK_IGS_AS_EXM_SUPRVISOR;
169 
170   PROCEDURE GET_FK_IGS_AD_LOCATION (
171     x_location_cd IN VARCHAR2
172     ) AS
173 
174     CURSOR cur_rowid IS
175       SELECT   rowid
176       FROM     IGS_AS_EXM_LOC_SPVSR
177       WHERE    exam_location_cd = x_location_cd ;
178 
179     lv_rowid cur_rowid%RowType;
180 
181   BEGIN
182 
183     Open cur_rowid;
184     Fetch cur_rowid INTO lv_rowid;
185     IF (cur_rowid%FOUND) THEN
186       Fnd_Message.Set_Name ('IGS', 'IGS_AS_ELS_LOC_FK');
187       IGS_GE_MSG_STACK.ADD;
188 	        Close cur_rowid;
189       APP_EXCEPTION.RAISE_EXCEPTION;
190 
191       Return;
192     END IF;
193     Close cur_rowid;
194 
195   END GET_FK_IGS_AD_LOCATION;
196 
197   PROCEDURE Before_DML (
198     p_action IN VARCHAR2,
199     x_rowid IN  VARCHAR2 DEFAULT NULL,
200     x_person_id IN NUMBER DEFAULT NULL,
201     x_exam_location_cd IN VARCHAR2 DEFAULT NULL,
202     x_creation_date IN DATE DEFAULT NULL,
203     x_created_by IN NUMBER DEFAULT NULL,
204     x_last_update_date IN DATE DEFAULT NULL,
205     x_last_updated_by IN NUMBER DEFAULT NULL,
206     x_last_update_login IN NUMBER DEFAULT NULL
207   ) AS
208   BEGIN
209 
210     Set_Column_Values (
211       p_action,
212       x_rowid,
213       x_person_id,
214       x_exam_location_cd,
215       x_creation_date,
216       x_created_by,
217       x_last_update_date,
218       x_last_updated_by,
219       x_last_update_login
220     );
221 
222     IF (p_action = 'INSERT') THEN
223       -- Call all the procedures related to Before Insert.
224       BeforeRowInsert1 ( p_inserting => TRUE );
225 
226 	IF  Get_PK_For_Validation (
227 	         new_references.PERSON_ID,
228 	         new_references.EXAM_LOCATION_CD) THEN
229          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
230          IGS_GE_MSG_STACK.ADD;
231 	         APP_EXCEPTION.RAISE_EXCEPTION;
232 	     END IF;
233 	     Check_Constraints;
234 
235       Check_Parent_Existance;
236     ELSIF (p_action = 'UPDATE') THEN
237       -- Call all the procedures related to Before Update.
238 
239 Check_Constraints;
240       Check_Parent_Existance;
241 
242 	ELSIF (p_action = 'VALIDATE_INSERT') THEN
243 	     IF  Get_PK_For_Validation ( new_references.PERSON_ID,
244 	         new_references.EXAM_LOCATION_CD
245 	         ) THEN
246          Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
247          IGS_GE_MSG_STACK.ADD;
248 	         APP_EXCEPTION.RAISE_EXCEPTION;
249 	     END IF;
250 
251 	     Check_Constraints;
252 	ELSIF (p_action = 'VALIDATE_UPDATE') THEN
253 
254 	      Check_Constraints;
255 
256 
257 
258     END IF;
259 
260 /*
261 The (L_ROWID := null) was added by ijeddy on the 12-apr-2003 as
262 part of the bug fix for bug no 2868726, (Uniqueness Check at Item Level)
263 */
264 L_ROWID := null;
265 
266   END Before_DML;
267 
268 procedure INSERT_ROW (
269   X_ROWID in out NOCOPY VARCHAR2,
270   X_PERSON_ID in NUMBER,
271   X_EXAM_LOCATION_CD in VARCHAR2,
272   X_MODE in VARCHAR2 default 'R'
273   ) AS
274     cursor C is select ROWID from IGS_AS_EXM_LOC_SPVSR
275       where PERSON_ID = X_PERSON_ID
276       and EXAM_LOCATION_CD = X_EXAM_LOCATION_CD;
277     X_LAST_UPDATE_DATE DATE;
278     X_LAST_UPDATED_BY NUMBER;
279     X_LAST_UPDATE_LOGIN NUMBER;
280 begin
281   X_LAST_UPDATE_DATE := SYSDATE;
282   if(X_MODE = 'I') then
283     X_LAST_UPDATED_BY := 1;
284     X_LAST_UPDATE_LOGIN := 0;
285   elsif (X_MODE = 'R') then
286     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
287     if X_LAST_UPDATED_BY is NULL then
288       X_LAST_UPDATED_BY := -1;
289     end if;
290     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
291     if X_LAST_UPDATE_LOGIN is NULL then
292       X_LAST_UPDATE_LOGIN := -1;
293     end if;
294   else
295     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
296     IGS_GE_MSG_STACK.ADD;
297     APP_EXCEPTION.RAISE_EXCEPTION;
298   end if;
299 
300   Before_DML(
301  p_action=>'INSERT',
302  x_rowid=>X_ROWID,
303  x_exam_location_cd=>X_EXAM_LOCATION_CD,
304  x_person_id=>X_PERSON_ID,
305  x_creation_date=>X_LAST_UPDATE_DATE,
306  x_created_by=>X_LAST_UPDATED_BY,
307  x_last_update_date=>X_LAST_UPDATE_DATE,
308  x_last_updated_by=>X_LAST_UPDATED_BY,
309  x_last_update_login=>X_LAST_UPDATE_LOGIN
310  );
311   insert into IGS_AS_EXM_LOC_SPVSR (
312     PERSON_ID,
313     EXAM_LOCATION_CD,
314     CREATION_DATE,
315     CREATED_BY,
316     LAST_UPDATE_DATE,
317     LAST_UPDATED_BY,
318     LAST_UPDATE_LOGIN
319   ) values (
320     NEW_REFERENCES.PERSON_ID,
321     NEW_REFERENCES.EXAM_LOCATION_CD,
322     X_LAST_UPDATE_DATE,
323     X_LAST_UPDATED_BY,
324     X_LAST_UPDATE_DATE,
325     X_LAST_UPDATED_BY,
326     X_LAST_UPDATE_LOGIN
327   );
328 
329   open c;
330   fetch c into X_ROWID;
331   if (c%notfound) then
332     close c;
333     raise no_data_found;
334   end if;
335   close c;
336 
337 end INSERT_ROW;
338 
339 procedure LOCK_ROW (
340   X_ROWID in  VARCHAR2,
341   X_PERSON_ID in NUMBER,
342   X_EXAM_LOCATION_CD in VARCHAR2
343 ) AS
344   cursor c1 is select
345     ROWID
346     from IGS_AS_EXM_LOC_SPVSR
347     where ROWID = X_ROWID  for update  nowait;
348   tlinfo c1%rowtype;
349 
350 begin
351   open c1;
352   fetch c1 into tlinfo;
353   if (c1%notfound) then
354     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
355     IGS_GE_MSG_STACK.ADD;
356     APP_EXCEPTION.RAISE_EXCEPTION;
357     close c1;
358     return;
359   end if;
360   close c1;
361   return;
362 end LOCK_ROW;
363 
364 procedure DELETE_ROW (
365   X_ROWID in VARCHAR2) AS
366 begin
367 Before_DML(
368   p_action => 'DELETE',
369   x_rowid => X_ROWID
370   );
371   delete from IGS_AS_EXM_LOC_SPVSR
372  where ROWID = X_ROWID;
373   if (sql%notfound) then
374     raise no_data_found;
375   end if;
376 
377 end DELETE_ROW;
378 
379 	PROCEDURE Check_Constraints (
380 	Column_Name	IN	VARCHAR2	DEFAULT NULL,
381 	Column_Value 	IN	VARCHAR2	DEFAULT NULL
382 	)
383 	AS
384 	BEGIN
385 	IF  column_name is null then
386 	    NULL;
387 	ELSIF upper(Column_name) = 'EXAM_LOCATION_CD' then
388 	    new_references.EXAM_LOCATION_CD := column_value;
389       END IF;
390 
391       IF upper(column_name) = 'EXAM_LOCATION_CD' OR
392      column_name is null Then
393      IF new_references.EXAM_LOCATION_CD <> UPPER(new_references.EXAM_LOCATION_CD) Then
394        Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
395        IGS_GE_MSG_STACK.ADD;
396        APP_EXCEPTION.RAISE_EXCEPTION;
397                    END IF;
398               END IF;
399 
400 	END Check_Constraints;
401 
402 end IGS_AS_EXM_LOC_SPVSR_PKG;