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