[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_OFR_PKG
Source
1 package body IGS_PS_OFR_PKG AS
2 /* $Header: IGSPI19B.pls 115.6 2002/11/29 02:04:15 nsidana ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGS_PS_OFR_ALL%RowType;
7 new_references IGS_PS_OFR_ALL%RowType;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2 DEFAULT NULL,
12 x_course_cd IN VARCHAR2 DEFAULT NULL,
13 x_version_number IN NUMBER DEFAULT NULL,
14 x_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_attribute_category IN VARCHAR2 DEFAULT NULL,
16 x_attribute1 IN VARCHAR2 DEFAULT NULL,
17 x_attribute2 IN VARCHAR2 DEFAULT NULL,
18 x_attribute3 IN VARCHAR2 DEFAULT NULL,
19 x_attribute4 IN VARCHAR2 DEFAULT NULL,
20 x_attribute5 IN VARCHAR2 DEFAULT NULL,
21 x_attribute6 IN VARCHAR2 DEFAULT NULL,
22 x_attribute7 IN VARCHAR2 DEFAULT NULL,
23 x_attribute8 IN VARCHAR2 DEFAULT NULL,
24 x_attribute9 IN VARCHAR2 DEFAULT NULL,
25 x_attribute10 IN VARCHAR2 DEFAULT NULL,
26 x_attribute11 IN VARCHAR2 DEFAULT NULL,
27 x_attribute12 IN VARCHAR2 DEFAULT NULL,
28 x_attribute13 IN VARCHAR2 DEFAULT NULL,
29 x_attribute14 IN VARCHAR2 DEFAULT NULL,
30 x_attribute15 IN VARCHAR2 DEFAULT NULL,
31 x_attribute16 IN VARCHAR2 DEFAULT NULL,
32 x_attribute17 IN VARCHAR2 DEFAULT NULL,
33 x_attribute18 IN VARCHAR2 DEFAULT NULL,
34 x_attribute19 IN VARCHAR2 DEFAULT NULL,
35 x_attribute20 IN VARCHAR2 DEFAULT NULL,
36 x_creation_date IN DATE DEFAULT NULL,
37 x_created_by IN NUMBER DEFAULT NULL,
38 x_last_update_date IN DATE DEFAULT NULL,
39 x_last_updated_by IN NUMBER DEFAULT NULL,
40 x_last_update_login IN NUMBER DEFAULT NULL,
41 x_org_id in NUMBER DEFAULT NULL
42 ) AS
43 /*************************************************************
44 Created By :
45 Date Created By :
46 Purpose :
47 Know limitations, enhancements or remarks
48 Change History
49 Who When What
50 sbeerell 09-MAY-2000 Changed according to DLD version 2
51 (reverse chronological order - newest change first)
52 ***************************************************************/
53 CURSOR cur_old_ref_values IS
54 SELECT *
55 FROM IGS_PS_OFR_ALL
56 WHERE rowid = x_rowid;
57
58 BEGIN
59
60 l_rowid := x_rowid;
61
62 -- Code for setting the Old and New Reference Values.
63 -- Populate Old Values.
64 Open cur_old_ref_values;
65 Fetch cur_old_ref_values INTO old_references;
66 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
67 Close cur_old_ref_values;
68 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
69 IGS_GE_MSG_STACK.ADD;
70 App_Exception.Raise_Exception;
71 Return;
72 END IF;
73 Close cur_old_ref_values;
74
75 -- Populate New Values.
76 new_references.course_cd := x_course_cd;
77 new_references.version_number := x_version_number;
78 new_references.cal_type := x_cal_type;
79 new_references.attribute_category := x_attribute_category;
80 new_references.attribute1 := x_attribute1;
81 new_references.attribute2 := x_attribute2;
82 new_references.attribute3 := x_attribute3;
83 new_references.attribute4 := x_attribute4;
84 new_references.attribute5 := x_attribute5;
85 new_references.attribute6 := x_attribute6;
86 new_references.attribute7 := x_attribute7;
87 new_references.attribute8 := x_attribute8;
88 new_references.attribute9 := x_attribute9;
89 new_references.attribute10 := x_attribute10;
90 new_references.attribute11 := x_attribute11;
91 new_references.attribute12 := x_attribute12;
92 new_references.attribute13 := x_attribute13;
93 new_references.attribute14 := x_attribute14;
94 new_references.attribute15 := x_attribute15;
95 new_references.attribute16 := x_attribute16;
96 new_references.attribute17 := x_attribute17;
97 new_references.attribute18 := x_attribute18;
98 new_references.attribute19 := x_attribute19;
99 new_references.attribute20 := x_attribute20;
100 new_references.org_id:=x_org_id;
101 IF (p_action = 'UPDATE') THEN
102 new_references.creation_date := old_references.creation_date;
103 new_references.created_by := old_references.created_by;
104 ELSE
105 new_references.creation_date := x_creation_date;
106 new_references.created_by := x_created_by;
107 END IF;
108 new_references.last_update_date := x_last_update_date;
109 new_references.last_updated_by := x_last_updated_by;
110 new_references.last_update_login := x_last_update_login;
111
112 END Set_Column_Values;
113
114 -- Trigger description :-
115 -- "OSS_TST".TRG_CO_BR_IUD
116 -- BEFORE INSERT OR UPDATE OR DELETE ON IGS_PS_OFR
117 -- REFERENCING
118 -- NEW AS NEW
119 -- OLD AS OLD
120 -- FOR EACH ROW
121
122 PROCEDURE BeforeRowInsertUpdateDelete1(
123 p_inserting IN BOOLEAN DEFAULT FALSE,
124 p_updating IN BOOLEAN DEFAULT FALSE,
125 p_deleting IN BOOLEAN DEFAULT FALSE
126 ) AS
127 /*************************************************************
128 Created By :
129 Date Created By :
130 Purpose :
131 Know limitations, enhancements or remarks
132 Change History
133 Who When What
134 (reverse chronological order - newest change first)
135 ***************************************************************/
136 v_message_name varchar2(30);
137 v_course_cd IGS_PS_VER.course_cd%TYPE;
138 v_version_number IGS_PS_VER.version_number%TYPE;
139 BEGIN
140
141 -- Set variables
142 IF p_inserting OR p_updating THEN
143 v_course_cd := new_references.course_cd;
144 v_version_number := new_references.version_number;
145 ELSE -- p_deleting
146 v_course_cd := old_references.course_cd;
147 v_version_number := old_references.version_number;
148 END IF;
149 -- Validate that updates are allowed
150 IF IGS_PS_VAL_CRS.CRSP_VAL_IUD_CRV_DTL(v_course_cd,
151 v_version_number,
152 v_message_name) = FALSE THEN
153 Fnd_Message.Set_Name('IGS',v_message_name);
154 IGS_GE_MSG_STACK.ADD;
155 App_Exception.Raise_Exception;
156 END IF;
157 -- Validate calendar type.
158 IF p_inserting OR
159 (p_updating AND old_references.cal_type <> new_references.cal_type) THEN
160 IF IGS_PS_VAL_CO.crsp_val_co_cal_type (
161 new_references.cal_type,
162 v_message_name) = FALSE THEN
163 Fnd_Message.Set_Name('IGS',v_message_name);
164 IGS_GE_MSG_STACK.ADD;
165 App_Exception.Raise_Exception;
166 END IF;
167 END IF;
168 END BeforeRowInsertUpdateDelete1;
169
170 PROCEDURE Check_Constraints (
171 Column_Name IN VARCHAR2 DEFAULT NULL,
172 Column_Value IN VARCHAR2 DEFAULT NULL
173 )
174 AS
175 /*************************************************************
176 Created By :
177 Date Created By :
178 Purpose :
179 Know limitations, enhancements or remarks
180 Change History
181 Who When What
182 (reverse chronological order - newest change first)
183 ***************************************************************/
184 BEGIN
185
186 IF column_name is null then
187 NULL;
188 ELSIF upper(Column_name) = 'CAL_TYPE' then
189 new_references.cal_type := column_value;
190 ELSIF upper(Column_name) = 'COURSE_CD' then
191 new_references.course_cd := column_value;
192 END IF;
193
194 IF upper(column_name) = 'CAL_TYPE' OR
195 column_name is null Then
196 IF ( new_references.cal_type <> UPPER(new_references.cal_type) ) Then
197 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 END IF;
201 END IF;
202
203 IF upper(column_name) = 'COURSE_CD' OR
204 column_name is null Then
205 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) 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 END Check_Constraints;
213
214 PROCEDURE Check_Parent_Existance AS
215 /*************************************************************
216 Created By :
217 Date Created By :
218 Purpose :
219 Know limitations, enhancements or remarks
220 Change History
221 Who When What
222 (reverse chronological order - newest change first)
223 ***************************************************************/
224 BEGIN
225
226 IF (((old_references.cal_type = new_references.cal_type)) OR
227 ((new_references.cal_type IS NULL))) THEN
228 NULL;
229 ELSE
230 IF NOT IGS_CA_TYPE_PKG.Get_PK_For_Validation (
231 new_references.cal_type
232 ) THEN
233 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
234 IGS_GE_MSG_STACK.ADD;
235 App_Exception.Raise_Exception;
236 END IF;
237 END IF;
238
239 IF (((old_references.course_cd = new_references.course_cd) AND
240 (old_references.version_number = new_references.version_number)) OR
241 ((new_references.course_cd IS NULL) OR
242 (new_references.version_number IS NULL))) THEN
243 NULL;
244 ELSE
245 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
246 new_references.course_cd,
247 new_references.version_number
248 ) THEN
249 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END IF;
253 END IF;
254
255 END Check_Parent_Existance;
256
257 PROCEDURE Check_Child_Existance AS
258 /*************************************************************
259 Created By :
260 Date Created By :
261 Purpose :
262 Know limitations, enhancements or remarks
263 Change History
264 Who When What
265 sbeerell 09-MAY-2000 Changed according to DLD version 2
266 (reverse chronological order - newest change first)
267 ***************************************************************/
268 BEGIN
269
270 IGS_AD_PRD_PS_OF_OPT_PKG.GET_FK_IGS_PS_OFR (
271 old_references.course_cd,
272 old_references.version_number,
273 old_references.cal_type
274 );
275
276 IGS_AD_PECRS_OFOP_DT_PKG.GET_FK_IGS_PS_OFR (
277 old_references.course_cd,
278 old_references.version_number,
279 old_references.cal_type
280 );
281
282 IGS_PS_OFR_INST_PKG.GET_FK_IGS_PS_OFR (
283 old_references.course_cd,
284 old_references.version_number,
285 old_references.cal_type
286 );
287
288 IGS_PS_OFR_NOTE_PKG.GET_FK_IGS_PS_OFR (
289 old_references.course_cd,
290 old_references.version_number,
291 old_references.cal_type
292 );
293
294 IGS_PS_OFR_OPT_PKG.GET_FK_IGS_PS_OFR (
295 old_references.course_cd,
296 old_references.version_number,
297 old_references.cal_type
298 );
299
300 IGS_PS_OFR_UNIT_SET_PKG.GET_FK_IGS_PS_OFR (
301 old_references.course_cd,
302 old_references.version_number,
303 old_references.cal_type
304 );
305
306 IGS_PS_PAT_OF_STUDY_PKG.GET_FK_IGS_PS_OFR (
307 old_references.course_cd,
308 old_references.version_number,
309 old_references.cal_type
310 );
311
312 END Check_Child_Existance;
313
314 FUNCTION Get_PK_For_Validation (
315 x_course_cd IN VARCHAR2,
316 x_version_number IN NUMBER,
317 x_cal_type IN VARCHAR2
318 )
319 /*************************************************************
320 Created By :
321 Date Created By :
322 Purpose :
323 Know limitations, enhancements or remarks
324 Change History
325 Who When What
326 (reverse chronological order - newest change first)
327 ***************************************************************/
328 RETURN BOOLEAN AS
329
330 CURSOR cur_rowid IS
331 SELECT rowid
332 FROM IGS_PS_OFR_ALL
333 WHERE course_cd = x_course_cd
334 AND version_number = x_version_number
335 AND cal_type = x_cal_type
336 FOR UPDATE NOWAIT;
337
338 lv_rowid cur_rowid%RowType;
339
340 BEGIN
341
342 Open cur_rowid;
343 Fetch cur_rowid INTO lv_rowid;
344 IF (cur_rowid%FOUND) THEN
345 Close cur_rowid;
346 Return (TRUE);
347 ELSE
348 Close cur_rowid;
349 Return (FALSE);
350 END IF;
351
352 END Get_PK_For_Validation;
353
354 PROCEDURE GET_FK_IGS_CA_TYPE (
355 x_cal_type IN VARCHAR2
356 ) AS
357 /*************************************************************
358 Created By :
359 Date Created By :
360 Purpose :
361 Know limitations, enhancements or remarks
362 Change History
363 Who When What
364 (reverse chronological order - newest change first)
365 ***************************************************************/
366 CURSOR cur_rowid IS
367 SELECT rowid
368 FROM IGS_PS_OFR_ALL
369 WHERE cal_type = x_cal_type ;
370
371 lv_rowid cur_rowid%RowType;
372
373 BEGIN
374
375 Open cur_rowid;
376 Fetch cur_rowid INTO lv_rowid;
377 IF (cur_rowid%FOUND) THEN
378 Close cur_rowid;
379 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CO_CAT_FK');
380 IGS_GE_MSG_STACK.ADD;
381 App_Exception.Raise_Exception;
382 Return;
383 END IF;
384 Close cur_rowid;
385
386 END GET_FK_IGS_CA_TYPE;
387
388 PROCEDURE GET_FK_IGS_PS_VER (
389 x_course_cd IN VARCHAR2,
390 x_version_number IN NUMBER
391 ) AS
392 /*************************************************************
393 Created By :
394 Date Created By :
395 Purpose :
396 Know limitations, enhancements or remarks
397 Change History
398 Who When What
399 (reverse chronological order - newest change first)
400 ***************************************************************/
401 CURSOR cur_rowid IS
402 SELECT rowid
403 FROM IGS_PS_OFR_ALL
404 WHERE course_cd = x_course_cd
405 AND version_number = x_version_number ;
406
407 lv_rowid cur_rowid%RowType;
408
409 BEGIN
410
411 Open cur_rowid;
412 Fetch cur_rowid INTO lv_rowid;
413 IF (cur_rowid%FOUND) THEN
414 Close cur_rowid;
415 Fnd_Message.Set_Name ('IGS', 'IGS_PS_CO_CRV_FK');
416 IGS_GE_MSG_STACK.ADD;
417 App_Exception.Raise_Exception;
418 Return;
419 END IF;
420 Close cur_rowid;
421
422 END GET_FK_IGS_PS_VER;
423
424 PROCEDURE Before_DML (
425 p_action IN VARCHAR2,
426 x_rowid IN VARCHAR2 DEFAULT NULL,
427 x_course_cd IN VARCHAR2 DEFAULT NULL,
428 x_version_number IN NUMBER DEFAULT NULL,
429 x_cal_type IN VARCHAR2 DEFAULT NULL,
430 x_attribute_category IN VARCHAR2 DEFAULT NULL,
431 x_attribute1 IN VARCHAR2 DEFAULT NULL,
432 x_attribute2 IN VARCHAR2 DEFAULT NULL,
433 x_attribute3 IN VARCHAR2 DEFAULT NULL,
434 x_attribute4 IN VARCHAR2 DEFAULT NULL,
435 x_attribute5 IN VARCHAR2 DEFAULT NULL,
436 x_attribute6 IN VARCHAR2 DEFAULT NULL,
437 x_attribute7 IN VARCHAR2 DEFAULT NULL,
438 x_attribute8 IN VARCHAR2 DEFAULT NULL,
439 x_attribute9 IN VARCHAR2 DEFAULT NULL,
440 x_attribute10 IN VARCHAR2 DEFAULT NULL,
441 x_attribute11 IN VARCHAR2 DEFAULT NULL,
442 x_attribute12 IN VARCHAR2 DEFAULT NULL,
443 x_attribute13 IN VARCHAR2 DEFAULT NULL,
444 x_attribute14 IN VARCHAR2 DEFAULT NULL,
445 x_attribute15 IN VARCHAR2 DEFAULT NULL,
446 x_attribute16 IN VARCHAR2 DEFAULT NULL,
447 x_attribute17 IN VARCHAR2 DEFAULT NULL,
448 x_attribute18 IN VARCHAR2 DEFAULT NULL,
449 x_attribute19 IN VARCHAR2 DEFAULT NULL,
450 x_attribute20 IN VARCHAR2 DEFAULT NULL,
451 x_creation_date IN DATE DEFAULT NULL,
452 x_created_by IN NUMBER DEFAULT NULL,
453 x_last_update_date IN DATE DEFAULT NULL,
454 x_last_updated_by IN NUMBER DEFAULT NULL,
455 x_last_update_login IN NUMBER DEFAULT NULL,
456 x_org_id IN NUMBER DEFAULT NULL
457 ) AS
458 /*************************************************************
459 Created By :
460 Date Created By :
461 Purpose :
462 Know limitations, enhancements or remarks
463 Change History
464 Who When What
465 sbeerell 09-MAY-2000 Changed according to DLD version 2
466 (reverse chronological order - newest change first)
467 ***************************************************************/
468 BEGIN
469
470 Set_Column_Values (
471 p_action,
472 x_rowid,
473 x_course_cd,
474 x_version_number,
475 x_cal_type,
476 x_attribute_category,
477 x_attribute1,
478 x_attribute2,
479 x_attribute3,
480 x_attribute4,
481 x_attribute5,
482 x_attribute6,
483 x_attribute7,
484 x_attribute8,
485 x_attribute9,
486 x_attribute10,
487 x_attribute11,
488 x_attribute12,
489 x_attribute13,
490 x_attribute14,
491 x_attribute15,
492 x_attribute16,
493 x_attribute17,
494 x_attribute18,
495 x_attribute19,
496 x_attribute20,
497 x_creation_date,
498 x_created_by,
499 x_last_update_date,
500 x_last_updated_by,
501 x_last_update_login,
502 x_org_id
503 );
504
505 IF (p_action = 'INSERT') THEN
506 -- Call all the procedures related to Before Insert.
507 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
508 IF Get_PK_For_Validation (
509 new_references.course_cd,
510 new_references.version_number,
511 new_references.cal_type) THEN
512 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
513 IGS_GE_MSG_STACK.ADD;
514 App_Exception.Raise_Exception;
515 END IF;
516 Check_Constraints;
517 Check_Parent_Existance;
518 ELSIF (p_action = 'UPDATE') THEN
519 -- Call all the procedures related to Before Update.
520 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
521 Check_Constraints;
522 Check_Parent_Existance;
523 ELSIF (p_action = 'DELETE') THEN
524 -- Call all the procedures related to Before Delete.
525 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
526 Check_Child_Existance;
527 ELSIF (p_action = 'VALIDATE_INSERT') THEN
528 IF Get_PK_For_Validation (
529 new_references.course_cd,
530 new_references.version_number,
531 new_references.cal_type) THEN
532 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
533 IGS_GE_MSG_STACK.ADD;
534 App_Exception.Raise_Exception;
535 END IF;
536 Check_Constraints;
537 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
538 Check_Constraints;
539 ELSIF (p_action = 'VALIDATE_DELETE') THEN
540 Check_Child_Existance;
541 END IF;
542
543 END Before_DML;
544
545 PROCEDURE After_DML (
546 p_action IN VARCHAR2,
547 x_rowid IN VARCHAR2
548 ) AS
549 BEGIN
550
551 l_rowid := x_rowid;
552
553
554 END After_DML;
555
556 procedure INSERT_ROW (
557 X_ROWID in out NOCOPY VARCHAR2,
558 X_COURSE_CD in VARCHAR2,
559 X_VERSION_NUMBER in NUMBER,
560 X_CAL_TYPE in VARCHAR2,
561 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
562 x_ATTRIBUTE1 IN VARCHAR2,
563 x_ATTRIBUTE2 IN VARCHAR2,
564 x_ATTRIBUTE3 IN VARCHAR2,
565 x_ATTRIBUTE4 IN VARCHAR2,
566 x_ATTRIBUTE5 IN VARCHAR2,
567 x_ATTRIBUTE6 IN VARCHAR2,
568 x_ATTRIBUTE7 IN VARCHAR2,
569 x_ATTRIBUTE8 IN VARCHAR2,
570 x_ATTRIBUTE9 IN VARCHAR2,
571 x_ATTRIBUTE10 IN VARCHAR2,
572 x_ATTRIBUTE11 IN VARCHAR2,
573 x_ATTRIBUTE12 IN VARCHAR2,
574 x_ATTRIBUTE13 IN VARCHAR2,
575 x_ATTRIBUTE14 IN VARCHAR2,
576 x_ATTRIBUTE15 IN VARCHAR2,
577 x_ATTRIBUTE16 IN VARCHAR2,
578 x_ATTRIBUTE17 IN VARCHAR2,
579 x_ATTRIBUTE18 IN VARCHAR2,
580 x_ATTRIBUTE19 IN VARCHAR2,
581 x_ATTRIBUTE20 IN VARCHAR2,
582 X_MODE in VARCHAR2 default 'R',
583 X_ORG_ID IN NUMBER
584 ) AS
585 /*************************************************************
586 Created By :
587 Date Created By :
588 Purpose :
589 Know limitations, enhancements or remarks
590 Change History
591 Who When What
592 sbeerell 09-MAY-2000 Changed according to DLD version 2
593 (reverse chronological order - newest change first)
594 ***************************************************************/
595 cursor C is select ROWID from IGS_PS_OFR_ALL
596 where COURSE_CD = X_COURSE_CD
597 and VERSION_NUMBER = X_VERSION_NUMBER
598 and CAL_TYPE = X_CAL_TYPE;
599 X_LAST_UPDATE_DATE DATE;
600 X_LAST_UPDATED_BY NUMBER;
601 X_LAST_UPDATE_LOGIN NUMBER;
602 begin
603 X_LAST_UPDATE_DATE := SYSDATE;
604 if(X_MODE = 'I') then
605 X_LAST_UPDATED_BY := 1;
606 X_LAST_UPDATE_LOGIN := 0;
607 elsif (X_MODE = 'R') then
608 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
609 if X_LAST_UPDATED_BY is NULL then
610 X_LAST_UPDATED_BY := -1;
611 end if;
612 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
613 if X_LAST_UPDATE_LOGIN is NULL then
614 X_LAST_UPDATE_LOGIN := -1;
615 end if;
616 else
617 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
618 IGS_GE_MSG_STACK.ADD;
619 app_exception.raise_exception;
620 end if;
621 Before_DML (
622 p_action => 'INSERT',
623 x_rowid => X_ROWID,
624 x_course_cd => X_COURSE_CD,
625 x_version_number => X_VERSION_NUMBER,
626 x_cal_type => X_CAL_TYPE,
627 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
628 x_attribute1=>X_ATTRIBUTE1,
629 x_attribute2=>X_ATTRIBUTE2,
630 x_attribute3=>X_ATTRIBUTE3,
631 x_attribute4=>X_ATTRIBUTE4,
632 x_attribute5=>X_ATTRIBUTE5,
633 x_attribute6=>X_ATTRIBUTE6,
634 x_attribute7=>X_ATTRIBUTE7,
635 x_attribute8=>X_ATTRIBUTE8,
636 x_attribute9=>X_ATTRIBUTE9,
637 x_attribute10=>X_ATTRIBUTE10,
638 x_attribute11=>X_ATTRIBUTE11,
639 x_attribute12=>X_ATTRIBUTE12,
640 x_attribute13=>X_ATTRIBUTE13,
641 x_attribute14=>X_ATTRIBUTE14,
642 x_attribute15=>X_ATTRIBUTE15,
643 x_attribute16=>X_ATTRIBUTE16,
644 x_attribute17=>X_ATTRIBUTE17,
645 x_attribute18=>X_ATTRIBUTE18,
646 x_attribute19=>X_ATTRIBUTE19,
647 x_attribute20=>X_ATTRIBUTE20,
648 x_creation_date => X_LAST_UPDATE_DATE ,
649 x_created_by => X_LAST_UPDATED_BY ,
650 x_last_update_date => X_LAST_UPDATE_DATE ,
651 x_last_updated_by => X_LAST_UPDATED_BY ,
652 x_last_update_login => X_LAST_UPDATE_LOGIN,
653 x_org_id => igs_ge_gen_003.get_org_id
654 );
655
656 insert into IGS_PS_OFR_ALL (
657 COURSE_CD,
658 VERSION_NUMBER,
659 CAL_TYPE,
660 ATTRIBUTE_CATEGORY,
661 ATTRIBUTE1,
662 ATTRIBUTE2,
663 ATTRIBUTE3,
664 ATTRIBUTE4,
665 ATTRIBUTE5,
666 ATTRIBUTE6,
667 ATTRIBUTE7,
668 ATTRIBUTE8,
669 ATTRIBUTE9,
670 ATTRIBUTE10,
671 ATTRIBUTE11,
672 ATTRIBUTE12,
673 ATTRIBUTE13,
674 ATTRIBUTE14,
675 ATTRIBUTE15,
676 ATTRIBUTE16,
677 ATTRIBUTE17,
678 ATTRIBUTE18,
679 ATTRIBUTE19,
680 ATTRIBUTE20,
681 CREATION_DATE,
682 CREATED_BY,
683 LAST_UPDATE_DATE,
684 LAST_UPDATED_BY,
685 LAST_UPDATE_LOGIN,
686 ORG_ID
687 ) values (
688 NEW_REFERENCES.COURSE_CD,
689 NEW_REFERENCES.VERSION_NUMBER,
690 NEW_REFERENCES.CAL_TYPE,
691 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
692 NEW_REFERENCES.ATTRIBUTE1,
693 NEW_REFERENCES.ATTRIBUTE2,
694 NEW_REFERENCES.ATTRIBUTE3,
695 NEW_REFERENCES.ATTRIBUTE4,
696 NEW_REFERENCES.ATTRIBUTE5,
697 NEW_REFERENCES.ATTRIBUTE6,
698 NEW_REFERENCES.ATTRIBUTE7,
699 NEW_REFERENCES.ATTRIBUTE8,
700 NEW_REFERENCES.ATTRIBUTE9,
701 NEW_REFERENCES.ATTRIBUTE10,
702 NEW_REFERENCES.ATTRIBUTE11,
703 NEW_REFERENCES.ATTRIBUTE12,
704 NEW_REFERENCES.ATTRIBUTE13,
705 NEW_REFERENCES.ATTRIBUTE14,
706 NEW_REFERENCES.ATTRIBUTE15,
707 NEW_REFERENCES.ATTRIBUTE16,
708 NEW_REFERENCES.ATTRIBUTE17,
709 NEW_REFERENCES.ATTRIBUTE18,
710 NEW_REFERENCES.ATTRIBUTE19,
711 NEW_REFERENCES.ATTRIBUTE20,
712 X_LAST_UPDATE_DATE,
713 X_LAST_UPDATED_BY,
714 X_LAST_UPDATE_DATE,
715 X_LAST_UPDATED_BY,
716 X_LAST_UPDATE_LOGIN,
717 NEW_REFERENCES.ORG_ID
718 );
719
720
721 open c;
722 fetch c into X_ROWID;
723 if (c%notfound) then
724 close c;
725 raise no_data_found;
726 end if;
727 close c;
728 After_DML (
729 p_action => 'INSERT',
730 x_rowid => X_ROWID
731 );
732 end INSERT_ROW;
733
734 procedure LOCK_ROW (
735 X_ROWID IN VARCHAR2,
736 X_COURSE_CD in VARCHAR2,
737 X_VERSION_NUMBER in NUMBER,
738 X_CAL_TYPE in VARCHAR2,
739 x_ATTRIBUTE_CATEGORY IN VARCHAR2,
740 x_ATTRIBUTE1 IN VARCHAR2,
741 x_ATTRIBUTE2 IN VARCHAR2,
742 x_ATTRIBUTE3 IN VARCHAR2,
743 x_ATTRIBUTE4 IN VARCHAR2,
744 x_ATTRIBUTE5 IN VARCHAR2,
745 x_ATTRIBUTE6 IN VARCHAR2,
746 x_ATTRIBUTE7 IN VARCHAR2,
747 x_ATTRIBUTE8 IN VARCHAR2,
748 x_ATTRIBUTE9 IN VARCHAR2,
749 x_ATTRIBUTE10 IN VARCHAR2,
750 x_ATTRIBUTE11 IN VARCHAR2,
751 x_ATTRIBUTE12 IN VARCHAR2,
752 x_ATTRIBUTE13 IN VARCHAR2,
753 x_ATTRIBUTE14 IN VARCHAR2,
754 x_ATTRIBUTE15 IN VARCHAR2,
755 x_ATTRIBUTE16 IN VARCHAR2,
756 x_ATTRIBUTE17 IN VARCHAR2,
757 x_ATTRIBUTE18 IN VARCHAR2,
758 x_ATTRIBUTE19 IN VARCHAR2,
759 x_ATTRIBUTE20 IN VARCHAR2
760 ) AS
761 /*************************************************************
762 Created By :
763 Date Created By :
764 Purpose :
765 Know limitations, enhancements or remarks
766 Change History
767 Who When What
768 sbeerell 09-MAY-2000 Changed according to DLD version 2
769 (reverse chronological order - newest change first)
770 ***************************************************************/
771 cursor c1 is select ROWID
772 from IGS_PS_OFR_ALL
773 where ROWID = X_ROWID
774 for update nowait;
775 tlinfo c1%rowtype;
776
777 begin
778 open c1;
779 fetch c1 into tlinfo;
780 if (c1%notfound) then
781 close c1;
782 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
783 IGS_GE_MSG_STACK.ADD;
784 app_exception.raise_exception;
785 return;
786 end if;
787 close c1;
788
789 return;
790 end LOCK_ROW;
791
792 procedure DELETE_ROW (
793 X_ROWID in VARCHAR2
794 ) AS
795 /*************************************************************
796 Created By :
797 Date Created By :
798 Purpose :
799 Know limitations, enhancements or remarks
800 Change History
801 Who When What
802 (reverse chronological order - newest change first)
803 ***************************************************************/
804 begin
805 Before_DML (
806 p_action => 'DELETE',
807 x_rowid => X_ROWID
808 );
809 delete from IGS_PS_OFR_ALL
810 where ROWID = X_ROWID;
811 if (sql%notfound) then
812 raise no_data_found;
813 end if;
814 After_DML (
815 p_action => 'DELETE',
816 x_rowid => X_ROWID
817 );
818 end DELETE_ROW;
819
820 end IGS_PS_OFR_PKG;