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