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