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_TEST_TYPE_PKG

Source


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