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