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