[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_APPL_STAT_PKG
Source
1 package body IGS_AD_APPL_STAT_PKG as
2 /* $Header: IGSAI12B.pls 115.8 2003/10/30 13:11:12 akadam ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_APPL_STAT%RowType;
6 new_references IGS_AD_APPL_STAT%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_adm_appl_status IN VARCHAR2 DEFAULT NULL,
12 x_description IN VARCHAR2 DEFAULT NULL,
13 x_s_adm_appl_status IN VARCHAR2 DEFAULT NULL,
14 x_system_default_ind IN VARCHAR2 DEFAULT NULL,
15 x_closed_ind 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_APPL_STAT
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 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 App_Exception.Raise_Exception;
40 Close cur_old_ref_values;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.adm_appl_status := x_adm_appl_status;
47 new_references.description := x_description;
48 new_references.s_adm_appl_status := x_s_adm_appl_status;
49 new_references.system_default_ind := x_system_default_ind;
50 new_references.closed_ind := x_closed_ind;
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 -- Trigger description :-
65 -- "OSS_TST".trg_aas_br_iu
66 -- BEFORE INSERT OR UPDATE
67 -- ON adm_appl_status
68 -- FOR EACH ROW
69
70 PROCEDURE BeforeRowInsertUpdate1(
71 p_inserting IN BOOLEAN DEFAULT FALSE,
72 p_updating IN BOOLEAN DEFAULT FALSE,
73 p_deleting IN BOOLEAN DEFAULT FALSE
74 ) AS
75 v_message_name VARCHAR2(30);
76 BEGIN
77 -- Validate Admission application status closed ind.
78 IF p_inserting OR ((old_references.s_adm_appl_status <>
79 new_references.s_adm_appl_status) OR
80 (old_references.closed_ind = 'Y' AND new_references.closed_ind = 'N')) THEN
81 IF IGS_AD_VAL_AAS.admp_val_saas_clsd(
82 new_references.s_adm_appl_status,
83 v_message_name) = FALSE THEN
84 Fnd_Message.Set_Name('IGS',v_message_name);
85 IGS_GE_MSG_STACK.ADD;
86 App_Exception.Raise_Exception;
87 END IF;
88 END IF;
89 IF (new_references.closed_ind = 'Y' AND new_references.system_default_ind = 'Y') THEN
90 Fnd_Message.Set_Name('IGS','IGS_AD_SYS_DFLT_IND_NOTSET_CLS');
91 IGS_GE_MSG_STACK.ADD;
92 App_Exception.Raise_Exception;
93 END IF;
94 END BeforeRowInsertUpdate1;
95
96 PROCEDURE Check_Constraints (
97
98 Column_Name IN VARCHAR2 DEFAULT NULL,
99 Column_Value IN VARCHAR2 DEFAULT NULL
100 ) AS
101
102 BEGIN
103 IF column_name is null then
104 NULL;
105 ELSIF upper(Column_name) = 'SYSTEM_DEFAULT_IND' Then
106 new_references.system_default_ind := column_value;
107 ELSIF upper(Column_name) = 'ADM_APPL_STATUS' Then
108 new_references.adm_appl_status := column_value;
109 ELSIF upper(Column_name) = 'CLOSED_IND' Then
110 new_references.closed_ind := column_value;
111 ELSIF upper(Column_name) = 'S_ADM_APPL_STATUS' Then
112 new_references.s_adm_appl_status := column_value;
113 END IF;
114
115 IF upper(column_name) = 'ADM_APPL_STATUS' OR column_name is null Then
116 IF new_references.adm_appl_status <> UPPER(new_references.adm_appl_status ) Then
117 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END IF;
121 END IF;
122 IF upper(column_name) = 'CLOSED_IND' OR column_name is null Then
123 IF new_references.closed_ind NOT IN ( 'Y' , 'N' ) Then
124 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception;
127 END IF;
128 END IF;
129 IF upper(column_name) = 'SYSTEM_DEFAULT_IND' OR column_name is null Then
130 IF new_references.system_default_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
137 IF upper(column_name) = 'S_ADM_APPL_STATUS' OR column_name is null Then
138
139 IF new_references.s_adm_appl_status <> UPPER(new_references.s_adm_appl_status ) Then
140
141 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
142 IGS_GE_MSG_STACK.ADD;
143 App_Exception.Raise_Exception;
144
145 END IF;
146 END IF;
147 END Check_Constraints;
148
149 PROCEDURE Check_Parent_Existance AS
150 BEGIN
151
152 IF (((old_references.s_adm_appl_status = new_references.s_adm_appl_status)) OR
153 ((new_references.s_adm_appl_status IS NULL))) THEN
154 NULL;
155 ELSE
156 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation (
157 'ADM_APPL_STATUS',
158 new_references.s_adm_appl_status ) THEN
159 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END IF;
163 END IF;
164
165 END Check_Parent_Existance;
166
167 PROCEDURE Check_Child_Existance AS
168 BEGIN
169
170 IGS_AD_APPL_PKG.GET_FK_IGS_AD_APPL_STAT (
171 old_references.adm_appl_status
172 );
173
174 END Check_Child_Existance;
175
176 Function Get_PK_For_Validation (
177 x_adm_appl_status IN VARCHAR2 ,
178 x_closed_ind IN VARCHAR2 )
179 RETURN BOOLEAN AS
180 CURSOR cur_rowid IS
181 SELECT rowid
182 FROM IGS_AD_APPL_STAT
183 WHERE adm_appl_status = x_adm_appl_status AND
184 closed_ind = NVL(x_closed_ind,closed_ind);
185
186 lv_rowid cur_rowid%RowType;
187 BEGIN -- Get_PK_For_Validation
188 Open cur_rowid;
189 Fetch cur_rowid INTO lv_rowid;
190 IF (cur_rowid%FOUND) THEN
191 Close cur_rowid;
192 Return (TRUE);
193 ELSE
194 Close cur_rowid;
195 Return (FALSE);
196 END IF;
197 END Get_PK_For_Validation;
198
199 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW(
200 x_s_adm_appl_status IN VARCHAR2
201 ) AS
202
203 CURSOR cur_rowid IS
204 SELECT rowid
205 FROM IGS_AD_APPL_STAT
206 WHERE s_adm_appl_status = x_s_adm_appl_status ;
207
208 lv_rowid cur_rowid%RowType;
209
210 BEGIN
211
212 Open cur_rowid;
213 Fetch cur_rowid INTO lv_rowid;
214 IF (cur_rowid%FOUND) THEN
215 Close cur_rowid;
216 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAS_SLV_FK');
217 IGS_GE_MSG_STACK.ADD;
218 App_Exception.Raise_Exception;
219 Return;
220 END IF;
221 Close cur_rowid;
222
223 END GET_FK_IGS_LOOKUPS_VIEW;
224
225 PROCEDURE Before_DML (
226 p_action IN VARCHAR2,
227 x_rowid IN VARCHAR2 DEFAULT NULL,
228 x_adm_appl_status IN VARCHAR2 DEFAULT NULL,
229 x_description IN VARCHAR2 DEFAULT NULL,
230 x_s_adm_appl_status IN VARCHAR2 DEFAULT NULL,
231 x_system_default_ind IN VARCHAR2 DEFAULT NULL,
232 x_closed_ind IN VARCHAR2 DEFAULT NULL,
233 x_creation_date IN DATE DEFAULT NULL,
234 x_created_by IN NUMBER DEFAULT NULL,
235 x_last_update_date IN DATE DEFAULT NULL,
236 x_last_updated_by IN NUMBER DEFAULT NULL,
237 x_last_update_login IN NUMBER DEFAULT NULL
238 ) AS
239 BEGIN
240
241 Set_Column_Values (
242 p_action,
243 x_rowid,
244 x_adm_appl_status,
245 x_description,
246 x_s_adm_appl_status,
247 x_system_default_ind,
248 x_closed_ind,
249 x_creation_date,
250 x_created_by,
251 x_last_update_date,
252 x_last_updated_by,
253 x_last_update_login
254 );
255
256 IF (p_action = 'INSERT') THEN
257 -- Call all the procedures related to Before Insert.
258 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
259 IF Get_PK_For_Validation (
260 new_references.adm_appl_status ) THEN
261 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
262 IGS_GE_MSG_STACK.ADD;
263 App_Exception.Raise_Exception;
264 END IF;
265 Check_Constraints;
266 Check_Parent_Existance;
267 ELSIF (p_action = 'UPDATE') THEN
268 -- Call all the procedures related to Before Update.
269 BeforeRowInsertUpdate1 ( p_updating => TRUE );
270 Check_Constraints;
271 Check_Parent_Existance;
272 ELSIF (p_action = 'DELETE') THEN
273 -- Call all the procedures related to Before Delete.
274 Check_Child_Existance;
275 ELSIF (p_action = 'VALIDATE_INSERT') THEN
276 IF Get_PK_For_Validation (
277 new_references.adm_appl_status ) THEN
278 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
279 IGS_GE_MSG_STACK.ADD;
280 App_Exception.Raise_Exception;
281 END IF;
282 Check_Constraints;
283 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
284 Check_Constraints;
285 ELSIF (p_action = 'VALIDATE_DELETE') THEN
286 Check_Child_Existance;
287 END IF;
288 END Before_DML;
289
290 PROCEDURE After_DML (
291 p_action IN VARCHAR2,
292 x_rowid IN VARCHAR2
293 ) AS
294 BEGIN
295
296 l_rowid := x_rowid;
297
298 IF (p_action = 'INSERT') THEN
299 -- Call all the procedures related to After Insert.
300 NULL;
301 ELSIF (p_action = 'UPDATE') THEN
302 -- Call all the procedures related to After Update.
303 NULL;
304 END IF;
305
306 END After_DML;
307
308 procedure INSERT_ROW (
309 X_ROWID in out NOCOPY VARCHAR2,
310 X_ADM_APPL_STATUS in VARCHAR2,
311 X_DESCRIPTION in VARCHAR2,
312 X_S_ADM_APPL_STATUS in VARCHAR2,
313 X_SYSTEM_DEFAULT_IND in VARCHAR2,
314 X_CLOSED_IND in VARCHAR2,
315 X_MODE in VARCHAR2 default 'R'
316 ) AS
317 cursor C is select ROWID from IGS_AD_APPL_STAT
318 where ADM_APPL_STATUS = X_ADM_APPL_STATUS;
319 X_LAST_UPDATE_DATE DATE;
320 X_LAST_UPDATED_BY NUMBER;
321 X_LAST_UPDATE_LOGIN NUMBER;
322 begin
323 X_LAST_UPDATE_DATE := SYSDATE;
324 if(X_MODE = 'I') then
325 X_LAST_UPDATED_BY := 1;
326 X_LAST_UPDATE_LOGIN := 0;
327 elsif (X_MODE = 'R') then
328 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
329 if X_LAST_UPDATED_BY is NULL then
330 X_LAST_UPDATED_BY := -1;
331 end if;
332 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
333 if X_LAST_UPDATE_LOGIN is NULL then
334 X_LAST_UPDATE_LOGIN := -1;
335 end if;
336 else
337 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
338 IGS_GE_MSG_STACK.ADD;
339 app_exception.raise_exception;
340 end if;
341
342 Before_DML(
343 p_action=>'INSERT',
344 x_rowid=>X_ROWID,
345 x_adm_appl_status=>X_ADM_APPL_STATUS,
346 x_closed_ind=>Nvl(X_CLOSED_IND, 'N'),
347 x_description=>X_DESCRIPTION,
348 x_s_adm_appl_status=>X_S_ADM_APPL_STATUS,
349 x_system_default_ind=>Nvl(X_SYSTEM_DEFAULT_IND, 'N'),
350 x_creation_date=>X_LAST_UPDATE_DATE,
351 x_created_by=>X_LAST_UPDATED_BY,
352 x_last_update_date=>X_LAST_UPDATE_DATE,
353 x_last_updated_by=>X_LAST_UPDATED_BY,
354 x_last_update_login=>X_LAST_UPDATE_LOGIN
355 );
356
357 insert into IGS_AD_APPL_STAT (
358 ADM_APPL_STATUS,
359 DESCRIPTION,
360 S_ADM_APPL_STATUS,
361 SYSTEM_DEFAULT_IND,
362 CLOSED_IND,
363 CREATION_DATE,
364 CREATED_BY,
365 LAST_UPDATE_DATE,
366 LAST_UPDATED_BY,
367 LAST_UPDATE_LOGIN
368 ) values (
369 NEW_REFERENCES.ADM_APPL_STATUS,
370 NEW_REFERENCES.DESCRIPTION,
371 NEW_REFERENCES.S_ADM_APPL_STATUS,
372 NEW_REFERENCES.SYSTEM_DEFAULT_IND,
373 NEW_REFERENCES.CLOSED_IND,
374 X_LAST_UPDATE_DATE,
375 X_LAST_UPDATED_BY,
376 X_LAST_UPDATE_DATE,
377 X_LAST_UPDATED_BY,
378 X_LAST_UPDATE_LOGIN
379 );
380
381 open c;
382 fetch c into X_ROWID;
383 if (c%notfound) then
384 close c;
385 raise no_data_found;
386 end if;
387 close c;
388
389 After_DML (
390 p_action => 'INSERT',
391 x_rowid => X_ROWID);
392
393 end INSERT_ROW;
394
395 procedure LOCK_ROW (
396 X_ROWID in VARCHAR2,
397 X_ADM_APPL_STATUS in VARCHAR2,
398 X_DESCRIPTION in VARCHAR2,
399 X_S_ADM_APPL_STATUS in VARCHAR2,
400 X_SYSTEM_DEFAULT_IND in VARCHAR2,
401 X_CLOSED_IND in VARCHAR2
402 ) AS
403 cursor c1 is select
404 DESCRIPTION,
405 S_ADM_APPL_STATUS,
406 SYSTEM_DEFAULT_IND,
407 CLOSED_IND
408 from IGS_AD_APPL_STAT
409 where ROWID = X_ROWID for update nowait;
410 tlinfo c1%rowtype;
411
412 begin
413 open c1;
414 fetch c1 into tlinfo;
415 if (c1%notfound) then
416 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
417 IGS_GE_MSG_STACK.ADD;
418 app_exception.raise_exception;
419 close c1;
420 return;
421 end if;
422 close c1;
423
424 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
425 AND (tlinfo.S_ADM_APPL_STATUS = X_S_ADM_APPL_STATUS)
426 AND (tlinfo.SYSTEM_DEFAULT_IND = X_SYSTEM_DEFAULT_IND)
427 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
428 ) then
429 null;
430 else
431 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
432 IGS_GE_MSG_STACK.ADD;
433 app_exception.raise_exception;
434 end if;
435 return;
436 end LOCK_ROW;
437
438 procedure UPDATE_ROW (
439 X_ROWID in VARCHAR2,
440 X_ADM_APPL_STATUS in VARCHAR2,
441 X_DESCRIPTION in VARCHAR2,
442 X_S_ADM_APPL_STATUS in VARCHAR2,
443 X_SYSTEM_DEFAULT_IND in VARCHAR2,
444 X_CLOSED_IND in VARCHAR2,
445 X_MODE in VARCHAR2 default 'R'
446 ) AS
447 X_LAST_UPDATE_DATE DATE;
448 X_LAST_UPDATED_BY NUMBER;
449 X_LAST_UPDATE_LOGIN NUMBER;
450 begin
451 X_LAST_UPDATE_DATE := SYSDATE;
452 if(X_MODE = 'I') then
453 X_LAST_UPDATED_BY := 1;
454 X_LAST_UPDATE_LOGIN := 0;
455 elsif (X_MODE = 'R') then
456 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
457 if X_LAST_UPDATED_BY is NULL then
458 X_LAST_UPDATED_BY := -1;
459 end if;
460 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
461 if X_LAST_UPDATE_LOGIN is NULL then
462 X_LAST_UPDATE_LOGIN := -1;
463 end if;
464 else
465 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
466 IGS_GE_MSG_STACK.ADD;
467 app_exception.raise_exception;
468 end if;
469
470 Before_DML(
471 p_action=>'UPDATE',
472 x_rowid=>X_ROWID,
473 x_adm_appl_status=>X_ADM_APPL_STATUS,
474 x_closed_ind=>X_CLOSED_IND,
475 x_description=>X_DESCRIPTION,
476 x_s_adm_appl_status=>X_S_ADM_APPL_STATUS,
477 x_system_default_ind=>X_SYSTEM_DEFAULT_IND,
478 x_creation_date=>X_LAST_UPDATE_DATE,
479 x_created_by=>X_LAST_UPDATED_BY,
480 x_last_update_date=>X_LAST_UPDATE_DATE,
481 x_last_updated_by=>X_LAST_UPDATED_BY,
482 x_last_update_login=>X_LAST_UPDATE_LOGIN
483 );
484
485 update IGS_AD_APPL_STAT set
486 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
487 S_ADM_APPL_STATUS = NEW_REFERENCES.S_ADM_APPL_STATUS,
488 SYSTEM_DEFAULT_IND = NEW_REFERENCES.SYSTEM_DEFAULT_IND,
489 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
490 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
491 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
492 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
493 where ROWID = X_ROWID
494 ;
495 if (sql%notfound) then
496 raise no_data_found;
497 end if;
498
499 After_DML (
500 p_action => 'UPDATE',
501 x_rowid => X_ROWID);
502
503 end UPDATE_ROW;
504
505 procedure ADD_ROW (
506 X_ROWID in out NOCOPY VARCHAR2,
507 X_ADM_APPL_STATUS in VARCHAR2,
508 X_DESCRIPTION in VARCHAR2,
509 X_S_ADM_APPL_STATUS in VARCHAR2,
510 X_SYSTEM_DEFAULT_IND in VARCHAR2,
511 X_CLOSED_IND in VARCHAR2,
512 X_MODE in VARCHAR2 default 'R'
513 ) AS
514 cursor c1 is select rowid from IGS_AD_APPL_STAT
515 where ADM_APPL_STATUS = X_ADM_APPL_STATUS
516 ;
517 begin
518 open c1;
519 fetch c1 into X_ROWID;
520 if (c1%notfound) then
521 close c1;
522 INSERT_ROW (
523 X_ROWID,
524 X_ADM_APPL_STATUS,
525 X_DESCRIPTION,
526 X_S_ADM_APPL_STATUS,
527 X_SYSTEM_DEFAULT_IND,
528 X_CLOSED_IND,
529 X_MODE);
530 return;
531 end if;
532 close c1;
533 UPDATE_ROW (
534 X_ROWID,
535 X_ADM_APPL_STATUS,
536 X_DESCRIPTION,
537 X_S_ADM_APPL_STATUS,
538 X_SYSTEM_DEFAULT_IND,
539 X_CLOSED_IND,
540 X_MODE);
541 end ADD_ROW;
542
543 procedure DELETE_ROW (
544 X_ROWID in VARCHAR2
545 ) AS
546 begin
547
548 Before_DML (
549 p_action => 'DELETE',
550 x_rowid => X_ROWID);
551
552 delete from IGS_AD_APPL_STAT
553 where ROWID = X_ROWID;
554 if (sql%notfound) then
555 raise no_data_found;
556 end if;
557
558 After_DML (
559 p_action => 'DELETE',
560 x_rowid => X_ROWID);
561
562 end DELETE_ROW;
563
564 end IGS_AD_APPL_STAT_PKG;