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