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