[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNIT_LVL_PKG
Source
1 package body IGS_PS_UNIT_LVL_PKG AS
2 /* $Header: IGSPI40B.pls 115.10 2003/11/07 12:55:30 nalkumar ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_UNIT_LVL_ALL%RowType;
6 new_references IGS_PS_UNIT_LVL_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_unit_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_course_type IN VARCHAR2 DEFAULT NULL,
14 x_unit_level IN VARCHAR2 DEFAULT NULL,
15 x_wam_weighting IN NUMBER 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 x_org_id IN NUMBER DEFAULT NULL ,
22 X_COURSE_CD VARCHAR2 DEFAULT NULL,
23 X_COURSE_VERSION_NUMBER NUMBER DEFAULT NULL
24 ) AS
25
26 CURSOR cur_old_ref_values IS
27 SELECT *
28 FROM IGS_PS_UNIT_LVL_ALL
29 WHERE rowid = x_rowid;
30
31 BEGIN
32
33 l_rowid := x_rowid;
34
35 -- Code for setting the Old and New Reference Values.
36 -- Populate Old Values.
37 Open cur_old_ref_values;
38 Fetch cur_old_ref_values INTO old_references;
39 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
40 Close cur_old_ref_values;
41 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
42 IGS_GE_MSG_STACK.ADD;
43 App_Exception.Raise_Exception;
44 Return;
45 END IF;
46 Close cur_old_ref_values;
47
48 -- Populate New Values.
49 new_references.unit_cd := x_unit_cd;
50 new_references.version_number := x_version_number;
51 new_references.unit_level := x_unit_level;
52 new_references.wam_weighting := x_wam_weighting;
53 new_references.course_type := x_course_type;
54 new_references.course_type := x_course_type;
55 new_references.course_cd := x_course_cd;
56 new_references.course_version_number := x_course_version_number;
57
58 IF (p_action = 'UPDATE') THEN
59 new_references.creation_date := old_references.creation_date;
60 new_references.created_by := old_references.created_by;
61 ELSE
62 new_references.creation_date := x_creation_date;
63 new_references.created_by := x_created_by;
64 END IF;
65 new_references.last_update_date := x_last_update_date;
66 new_references.last_updated_by := x_last_updated_by;
67 new_references.last_update_login := x_last_update_login;
68 new_references.org_id := x_org_id;
69
70 END Set_Column_Values;
71
72 PROCEDURE BeforeRowInsertUpdateDelete1(
73 p_inserting IN BOOLEAN DEFAULT FALSE,
74 p_updating IN BOOLEAN DEFAULT FALSE,
75 p_deleting IN BOOLEAN DEFAULT FALSE
76 ) AS
77 v_unit_cd IGS_PS_UNIT_LVL_ALL.unit_cd%TYPE;
78 v_version_number IGS_PS_UNIT_LVL_ALL.version_number%TYPE;
79 v_unit_level IGS_PS_UNIT_LVL_ALL.unit_level%TYPE;
80 v_wam_weighting IGS_PS_UNIT_LVL_ALL.wam_weighting%TYPE;
81 v_message_name VARCHAR2(30);
82 BEGIN
83 -- Set variables.
84 IF p_deleting THEN
85 v_unit_cd := old_references.unit_cd;
86 v_version_number := old_references.version_number;
87 ELSE -- p_inserting or p_updating
88 v_unit_cd := new_references.unit_cd;
89 v_version_number := new_references.version_number;
90 END IF;
91 -- Validate the insert/update/delete.
92 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl (
93 v_unit_cd,
94 v_version_number,
95 v_message_name) = FALSE THEN
96 FND_MESSAGE.SET_NAME('IGS',v_message_name);
97 IGS_GE_MSG_STACK.ADD;
98 APP_EXCEPTION.RAISE_EXCEPTION;
99 END IF;
100 -- Validate IGS_PS_UNIT level.
101 IF p_inserting OR (p_updating AND (old_references.unit_level <> new_references.unit_level)) THEN
102 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_CUL.crsp_val_unit_lvl
103 IF IGS_PS_VAL_UV.crsp_val_unit_lvl (
104 new_references.unit_level,
105 v_message_name) = FALSE THEN
106 FND_MESSAGE.SET_NAME('IGS',v_message_name);
107 IGS_GE_MSG_STACK.ADD;
108 APP_EXCEPTION.RAISE_EXCEPTION;
109 END IF;
110 END IF;
111
112 -- Insert history record on update.
113 IF p_updating THEN
114 IF old_references.unit_level <> new_references.unit_level OR
115 NVL(old_references.wam_weighting,999999) <> NVL(new_references.wam_weighting,999999) THEN
116 SELECT
117 DECODE(old_references.unit_level,new_references.unit_level,NULL,old_references.unit_level),
118 DECODE(NVL(old_references.wam_weighting,999999),NVL(new_references.wam_weighting,999999),
119 NULL,old_references.wam_weighting)
120 INTO v_unit_level,
121 v_wam_weighting
122 FROM dual;
123 -- Create history record for update
124 IGS_PS_GEN_007.CRSP_INS_CUL_HIST(
125 p_unit_cd => old_references.unit_cd,
126 p_version_number => old_references.version_number,
127 p_course_type => NULL,
128 p_last_update_on => old_references.last_update_date,
129 p_update_on => new_references.last_update_date,
130 p_last_update_who => old_references.last_updated_by,
131 p_unit_level => v_unit_level,
132 p_wam_weighting => v_wam_weighting,
133 p_course_cd => old_references.course_cd,
134 p_course_version_number => old_references.course_version_number);
135 END IF;
136 END IF;
137
138 IF p_deleting THEN
139 -- Create history record for deletion
140 IGS_PS_GEN_007.CRSP_INS_CUL_HIST(
141 p_unit_cd => old_references.unit_cd,
142 p_version_number => old_references.version_number,
143 p_course_type => NULL,
144 p_last_update_on => old_references.last_update_date,
145 p_update_on => SYSDATE,
146 p_last_update_who => old_references.last_updated_by,
147 p_unit_level => old_references.unit_level,
148 p_wam_weighting => old_references.wam_weighting,
149 p_course_cd => old_references.course_cd,
150 p_course_version_number => old_references.course_version_number);
151 END IF;
152 END BeforeRowInsertUpdateDelete1;
153
154 PROCEDURE get_fk_igs_ps_ver (
155 x_course_cd IN VARCHAR2,
156 x_course_version_number IN NUMBER
157 ) AS
158 CURSOR cur_rowid IS
159 SELECT rowid
160 FROM igs_ps_unit_lvl_all
161 WHERE course_cd = x_course_cd
162 AND course_version_number = x_course_version_number;
163 lv_rowid cur_rowid%RowType;
164 BEGIN
165 OPEN cur_rowid;
166 FETCH cur_rowid INTO lv_rowid;
167 IF cur_rowid%FOUND THEN
168 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PR_PRA_CRV_FK');
169 IGS_GE_MSG_STACK.ADD;
170 CLOSE CUR_ROWID;
171 APP_EXCEPTION.RAISE_EXCEPTION;
172 RETURN;
173 END IF;
174 CLOSE cur_rowid;
175 END get_fk_igs_ps_ver;
176
177 PROCEDURE Check_Constraints (
178 Column_Name IN VARCHAR2 DEFAULT NULL,
179 Column_Value IN VARCHAR2 DEFAULT NULL
180 ) IS
181 BEGIN
182 IF column_name is null THEN
183 NULL;
184 ELSIF upper(column_name) = 'UNIT_CD' THEN
185 new_references.unit_cd := column_value;
186 ELSIF upper(column_name) = 'UNIT_LEVEL' THEN
187 new_references.unit_level:= column_value;
188 ELSIF upper(column_name) = 'WAM_WEIGHTING' THEN
189 new_references.wam_weighting:= igs_ge_number.to_num(column_value);
190 END IF;
191
192 IF upper(column_name)= 'UNIT_CD' OR
193 column_name is null THEN
194 IF new_references.unit_cd <> UPPER(new_references.unit_cd)
195 THEN
196 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
197 IGS_GE_MSG_STACK.ADD;
198 App_Exception.Raise_Exception;
199 END IF;
200 END IF;
201
202 IF upper(column_name)= 'UNIT_LEVEL' OR
203 column_name is null THEN
204 IF new_references.unit_level <> UPPER(new_references.unit_level)
205 THEN
206 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 END IF;
210 END IF;
211
212 IF upper(column_name)= 'WAM_WEIGHTING' OR
213 column_name is null THEN
214 IF new_references.wam_weighting < 0 OR
215 new_references.wam_weighting > 99999.99
216 THEN
217 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
218 IGS_GE_MSG_STACK.ADD;
219 App_Exception.Raise_Exception;
220 END IF;
221 END IF;
222
223 END Check_Constraints;
224
225 PROCEDURE Check_Parent_Existance AS
226 BEGIN
227
228 IF (((old_references.unit_level = new_references.unit_level)) OR
229 ((new_references.unit_level IS NULL))) THEN
230 NULL;
231 ELSE
232 IF NOT IGS_PS_UNIT_LEVEL_PKG.Get_PK_For_Validation (
233 new_references.unit_level
234 )THEN
235 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
236 IGS_GE_MSG_STACK.ADD;
237 App_Exception.Raise_Exception;
238 END IF;
239 END IF;
240
241 IF (((old_references.unit_cd = new_references.unit_cd) AND
242 (old_references.version_number = new_references.version_number)) OR
243 ((new_references.unit_cd IS NULL) OR
244 (new_references.version_number IS NULL))) THEN
245 NULL;
246 ELSE
247 IF NOT IGS_PS_UNIT_VER_PKG.Get_PK_For_Validation (
248 new_references.unit_cd,
249 new_references.version_number
250 )THEN
251 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
252 IGS_GE_MSG_STACK.ADD;
253 App_Exception.Raise_Exception;
254 END IF;
255 END IF;
256
257 IF (((old_references.course_cd = new_references.course_cd) AND
258 (old_references.course_version_number = new_references.course_version_number)) OR
259 ((new_references.course_cd IS NULL) OR (new_references.course_version_number IS NULL))) THEN
260 NULL;
261 ELSE
262 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
263 new_references.course_cd,
264 new_references.course_version_number) THEN
265 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
266 IGS_GE_MSG_STACK.ADD;
267 APP_EXCEPTION.RAISE_EXCEPTION;
268 END IF;
269 END IF;
270 END Check_Parent_Existance;
271
272 FUNCTION Get_PK_For_Validation (
273 x_unit_cd IN VARCHAR2,
274 x_version_number IN NUMBER,
275 x_course_cd IN VARCHAR2,
276 x_course_version_number IN NUMBER
277 ) RETURN BOOLEAN AS
278
279 CURSOR cur_rowid IS
280 SELECT rowid
281 FROM IGS_PS_UNIT_LVL_ALL
282 WHERE unit_cd = x_unit_cd
283 AND version_number = x_version_number
284 AND course_cd = x_course_cd
285 AND course_version_number = x_course_version_number
286 FOR UPDATE NOWAIT;
287
288 lv_rowid cur_rowid%RowType;
289
290 BEGIN
291
292 Open cur_rowid;
293 Fetch cur_rowid INTO lv_rowid;
294 IF (cur_rowid%FOUND) THEN
295 Close cur_rowid;
296 Return(TRUE);
297 ELSE
298 Close cur_rowid;
299 Return(FALSE);
300 END IF;
301 END Get_PK_For_Validation;
302
303
304 PROCEDURE GET_FK_IGS_PS_UNIT_VER (
305 x_unit_cd IN VARCHAR2,
306 x_version_number IN NUMBER
307 ) AS
308
309 CURSOR cur_rowid IS
310 SELECT rowid
311 FROM IGS_PS_UNIT_LVL_ALL
312 WHERE unit_cd = x_unit_cd
313 AND version_number = x_version_number ;
314
315 lv_rowid cur_rowid%RowType;
316
317 BEGIN
318
319 Open cur_rowid;
320 Fetch cur_rowid INTO lv_rowid;
321 IF (cur_rowid%FOUND) THEN
322 Close cur_rowid;
323 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CUL_UV_FK');
324 IGS_GE_MSG_STACK.ADD;
325 App_Exception.Raise_Exception;
326 Return;
327 END IF;
328 Close cur_rowid;
329
330 END GET_FK_IGS_PS_UNIT_VER;
331
332 PROCEDURE Before_DML (
333 p_action IN VARCHAR2,
334 x_rowid IN VARCHAR2 DEFAULT NULL,
335 x_unit_cd IN VARCHAR2 DEFAULT NULL,
336 x_version_number IN NUMBER DEFAULT NULL,
337 x_course_type IN VARCHAR2 DEFAULT NULL,
338 x_unit_level IN VARCHAR2 DEFAULT NULL,
339 x_wam_weighting IN NUMBER DEFAULT NULL,
340 x_creation_date IN DATE DEFAULT NULL,
341 x_created_by IN NUMBER DEFAULT NULL,
342 x_last_update_date IN DATE DEFAULT NULL,
343 x_last_updated_by IN NUMBER DEFAULT NULL,
344 x_last_update_login IN NUMBER DEFAULT NULL,
345 x_org_id IN NUMBER DEFAULT NULL,
346 x_course_cd IN VARCHAR2 DEFAULT NULL,
347 x_course_version_number IN NUMBER DEFAULT NULL
348 ) AS
349 BEGIN
350 Set_Column_Values (
351 p_action,
352 x_rowid,
353 x_unit_cd,
354 x_version_number,
355 x_course_type,
356 x_unit_level,
357 x_wam_weighting,
358 x_creation_date,
359 x_created_by,
360 x_last_update_date,
361 x_last_updated_by,
362 x_last_update_login,
363 x_org_id,
364 x_course_cd,
365 x_course_version_number
366 );
367
368 IF (p_action = 'INSERT') THEN
369 -- Call all the procedures related to Before Insert.
370 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
371 IF Get_PK_For_Validation(
372 new_references.unit_cd,
373 new_references.version_number,
374 new_references.course_cd,
375 new_references.course_version_number) THEN
376 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
377 IGS_GE_MSG_STACK.ADD;
378 App_Exception.Raise_Exception;
379 END IF;
380 Check_Constraints;
381 Check_Parent_Existance;
382 ELSIF (p_action = 'UPDATE') THEN
383 -- Call all the procedures related to Before Update.
384 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
385 Check_Constraints;
386 Check_Parent_Existance;
387 ELSIF (p_action = 'DELETE') THEN
388 -- Call all the procedures related to Before Delete.
389 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
390 ELSIF (p_action = 'VALIDATE_INSERT') THEN
391 IF Get_PK_For_Validation(
392 new_references.unit_cd,
393 new_references.version_number,
394 new_references.course_cd,
395 new_references.course_version_number) THEN
396 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
397 IGS_GE_MSG_STACK.ADD;
398 App_Exception.Raise_Exception;
399 END IF;
400 Check_Constraints;
401 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
402 Check_Constraints;
403 END IF;
404 END Before_DML;
405
406 PROCEDURE After_DML (
407 p_action IN VARCHAR2,
408 x_rowid IN VARCHAR2
409 ) AS
410 BEGIN
411
412 l_rowid := x_rowid;
413
414
415 END After_DML;
416
417 PROCEDURE insert_row (
418 X_ROWID in out NOCOPY VARCHAR2,
419 X_UNIT_CD in VARCHAR2,
420 X_VERSION_NUMBER in NUMBER,
421 X_COURSE_TYPE in VARCHAR2 DEFAULT NULL,
422 X_UNIT_LEVEL in VARCHAR2,
423 X_WAM_WEIGHTING in NUMBER,
424 X_MODE in VARCHAR2 default 'R',
425 X_ORG_ID in NUMBER,
426 X_COURSE_CD IN VARCHAR2,
427 X_COURSE_VERSION_NUMBER IN NUMBER
428 ) AS
429 CURSOR C IS
430 SELECT ROWID
431 FROM igs_ps_unit_lvl_all
432 WHERE unit_cd = x_unit_cd
433 AND version_number = x_version_number
434 AND course_cd = x_course_cd
435 AND course_version_number = x_course_version_number;
436
437 X_LAST_UPDATE_DATE DATE;
438 X_LAST_UPDATED_BY NUMBER;
439 X_LAST_UPDATE_LOGIN NUMBER;
440 begin
441 X_LAST_UPDATE_DATE := SYSDATE;
442 if(X_MODE = 'I') then
443 X_LAST_UPDATED_BY := 1;
444 X_LAST_UPDATE_LOGIN := 0;
445 elsif (X_MODE = 'R') then
446 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
447 if X_LAST_UPDATED_BY is NULL then
448 X_LAST_UPDATED_BY := -1;
449 end if;
450 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
451 if X_LAST_UPDATE_LOGIN is NULL then
452 X_LAST_UPDATE_LOGIN := -1;
453 end if;
454 else
455 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
456 IGS_GE_MSG_STACK.ADD;
457 app_exception.raise_exception;
458 end if;
459 Before_DML( p_action => 'INSERT',
460 x_rowid => X_ROWID,
461 x_unit_cd => X_UNIT_CD,
462 x_version_number => X_VERSION_NUMBER,
463 x_course_type => X_COURSE_TYPE,
464 x_unit_level => X_UNIT_LEVEL,
465 x_wam_weighting => X_WAM_WEIGHTING,
466 x_creation_date => X_LAST_UPDATE_DATE,
467 x_created_by => X_LAST_UPDATED_BY,
468 x_last_update_date => X_LAST_UPDATE_DATE,
469 x_last_updated_by => X_LAST_UPDATED_BY,
470 x_last_update_login => X_LAST_UPDATE_LOGIN,
471 x_org_id => igs_ge_gen_003.get_org_id,
472 x_course_cd => X_COURSE_CD,
473 x_course_version_number => X_COURSE_VERSION_NUMBER
474 );
475
476 insert into IGS_PS_UNIT_LVL_ALL (
477 UNIT_CD,
478 VERSION_NUMBER,
479 UNIT_LEVEL,
480 WAM_WEIGHTING,
481 CREATION_DATE,
482 CREATED_BY,
483 LAST_UPDATE_DATE,
484 LAST_UPDATED_BY,
485 LAST_UPDATE_LOGIN,
486 ORG_ID,
487 COURSE_CD ,
488 COURSE_VERSION_NUMBER
489 ) values (
490 NEW_REFERENCES.UNIT_CD,
491 NEW_REFERENCES.VERSION_NUMBER,
492 NEW_REFERENCES.UNIT_LEVEL,
493 NEW_REFERENCES.WAM_WEIGHTING,
494 X_LAST_UPDATE_DATE,
495 X_LAST_UPDATED_BY,
496 X_LAST_UPDATE_DATE,
497 X_LAST_UPDATED_BY,
498 X_LAST_UPDATE_LOGIN,
499 NEW_REFERENCES.ORG_ID,
500 NEW_REFERENCES.COURSE_CD,
501 NEW_REFERENCES.COURSE_VERSION_NUMBER
502 );
503
504 open c;
505 fetch c into X_ROWID;
506 if (c%notfound) then
507 close c;
508 raise no_data_found;
509 end if;
510 close c;
511 After_DML(
512 p_action => 'INSERT',
513 x_rowid => X_ROWID
514 );
515
516 end INSERT_ROW;
517
518 procedure LOCK_ROW (
519 X_ROWID in VARCHAR2,
520 X_UNIT_CD in VARCHAR2,
521 X_VERSION_NUMBER in NUMBER,
522 X_COURSE_TYPE in VARCHAR2 DEFAULT NULL,
523 X_UNIT_LEVEL in VARCHAR2,
524 X_WAM_WEIGHTING in NUMBER,
525 X_COURSE_CD IN VARCHAR2,
526 X_COURSE_VERSION_NUMBER IN NUMBER
527 ) AS
528 cursor c1 is select
529 UNIT_LEVEL,
530 WAM_WEIGHTING
531 from IGS_PS_UNIT_LVL_ALL
532 where ROWID = X_ROWID for update nowait;
533 tlinfo c1%rowtype;
534
535 begin
536 open c1;
537 fetch c1 into tlinfo;
538 if (c1%notfound) then
539 close c1;
540 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
541 app_exception.raise_exception;
542 return;
543 end if;
544 close c1;
545
546 if ( (tlinfo.UNIT_LEVEL = X_UNIT_LEVEL)
547 AND ((tlinfo.WAM_WEIGHTING = X_WAM_WEIGHTING)
548 OR ((tlinfo.WAM_WEIGHTING is null)
549 AND (X_WAM_WEIGHTING is null)))
550
551 ) then
552 null;
553 else
554 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
555 app_exception.raise_exception;
556 end if;
557 return;
558 end LOCK_ROW;
559
560 procedure UPDATE_ROW (
561 X_ROWID in VARCHAR2,
562 X_UNIT_CD in VARCHAR2,
563 X_VERSION_NUMBER in NUMBER,
564 X_COURSE_TYPE in VARCHAR2 DEFAULT NULL,
565 X_UNIT_LEVEL in VARCHAR2,
566 X_WAM_WEIGHTING in NUMBER,
567 X_MODE in VARCHAR2 default 'R',
568 X_COURSE_CD VARCHAR2,
569 X_COURSE_VERSION_NUMBER NUMBER
570 ) AS
571
572 X_LAST_UPDATE_DATE DATE;
573 X_LAST_UPDATED_BY NUMBER;
574 X_LAST_UPDATE_LOGIN NUMBER;
575 begin
576 X_LAST_UPDATE_DATE := SYSDATE;
577 if(X_MODE = 'I') then
578 X_LAST_UPDATED_BY := 1;
579 X_LAST_UPDATE_LOGIN := 0;
580 elsif (X_MODE = 'R') then
581 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
582 if X_LAST_UPDATED_BY is NULL then
583 X_LAST_UPDATED_BY := -1;
584 end if;
585 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
586 if X_LAST_UPDATE_LOGIN is NULL then
587 X_LAST_UPDATE_LOGIN := -1;
588 end if;
589 else
590 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
591 IGS_GE_MSG_STACK.ADD;
592 app_exception.raise_exception;
593 end if;
594 Before_DML( p_action => 'UPDATE',
595 x_rowid => X_ROWID,
596 x_unit_cd => X_UNIT_CD,
597 x_version_number => X_VERSION_NUMBER,
598 x_course_type => X_COURSE_TYPE,
599 x_unit_level => X_UNIT_LEVEL,
600 x_wam_weighting => X_WAM_WEIGHTING,
601 x_creation_date => X_LAST_UPDATE_DATE,
602 x_created_by => X_LAST_UPDATED_BY,
603 x_last_update_date => X_LAST_UPDATE_DATE,
604 x_last_updated_by => X_LAST_UPDATED_BY,
605 x_last_update_login => X_LAST_UPDATE_LOGIN,
606 x_course_cd => X_COURSE_CD,
607 x_course_version_number => X_COURSE_VERSION_NUMBER
608 );
609 update IGS_PS_UNIT_LVL_ALL set
610 UNIT_LEVEL = NEW_REFERENCES.UNIT_LEVEL,
611 WAM_WEIGHTING = NEW_REFERENCES.WAM_WEIGHTING,
612 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
613 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
614 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
615
616 where ROWID = X_ROWID ;
617 if (sql%notfound) then
618 raise no_data_found;
619 end if;
620 After_DML(
621 p_action => 'UPDATE',
622 x_rowid => X_ROWID
623 );
624
625 end UPDATE_ROW;
626
627 procedure ADD_ROW (
628 X_ROWID in out NOCOPY VARCHAR2,
629 X_UNIT_CD in VARCHAR2,
630 X_VERSION_NUMBER in NUMBER,
631 X_COURSE_TYPE in VARCHAR2 DEFAULT NULL,
632 X_UNIT_LEVEL in VARCHAR2,
633 X_WAM_WEIGHTING in NUMBER,
634 X_MODE in VARCHAR2 DEFAULT 'R',
635 X_ORG_ID in NUMBER,
636 X_COURSE_CD VARCHAR2,
637 X_COURSE_VERSION_NUMBER NUMBER
638 ) AS
639 CURSOR c1 IS
640 SELECT rowid
641 FROM igs_ps_unit_lvl_all
642 WHERE unit_cd = x_unit_cd
643 AND version_number = x_version_number
644 AND course_cd = x_course_cd
645 AND course_version_number = x_course_version_number;
646 begin
647 open c1;
648 fetch c1 into X_ROWID;
649 if (c1%notfound) then
650 close c1;
651 INSERT_ROW (
652 X_ROWID,
653 X_UNIT_CD,
654 X_VERSION_NUMBER,
655 X_COURSE_TYPE,
656 X_UNIT_LEVEL,
657 X_WAM_WEIGHTING,
658 X_MODE,
659 X_ORG_ID,
660 X_COURSE_CD,
661 X_COURSE_VERSION_NUMBER);
662 return;
663 end if;
664 close c1;
665 UPDATE_ROW (
666 X_ROWID,
667 X_UNIT_CD,
668 X_VERSION_NUMBER,
669 X_COURSE_TYPE,
670 X_UNIT_LEVEL,
671 X_WAM_WEIGHTING,
672 X_MODE,
673 X_COURSE_CD,
674 X_COURSE_VERSION_NUMBER);
675 end ADD_ROW;
676
677 procedure DELETE_ROW (
678 X_ROWID in VARCHAR2
679 ) AS
680 begin
681 Before_DML( p_action => 'DELETE',
682 x_rowid => X_ROWID
683 );
684 delete from IGS_PS_UNIT_LVL_ALL
685 where ROWID = X_ROWID;
686 if (sql%notfound) then
687 raise no_data_found;
688 end if;
689 After_DML(
690 p_action => 'DELETE',
691 x_rowid => X_ROWID
692 );
693
694 END delete_row;
695
696 END igs_ps_unit_lvl_pkg;