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