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