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