[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_OFR_OPT_NOTE_PKG
Source
1 package body IGS_PS_OFR_OPT_NOTE_PKG AS
2 /* $Header: IGSPI22B.pls 115.4 2002/11/29 02:12:21 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_OFR_OPT_NOTE%RowType;
6 new_references IGS_PS_OFR_OPT_NOTE%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_location_cd IN VARCHAR2 DEFAULT NULL,
15 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
16 x_attendance_type IN VARCHAR2 DEFAULT NULL,
17 x_reference_number IN NUMBER DEFAULT NULL,
18 x_coo_id IN NUMBER DEFAULT NULL,
19 x_crs_note_type IN VARCHAR2 DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM IGS_PS_OFR_OPT_NOTE
30 WHERE rowid = x_rowid;
31
32 BEGIN
33
34 l_rowid := x_rowid;
35
36 -- Code for setting the Old and New Reference Values.
37 -- Populate Old Values.
38 Open cur_old_ref_values;
39 Fetch cur_old_ref_values INTO old_references;
40 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
41 Close cur_old_ref_values;
42 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
43 IGS_GE_MSG_STACK.ADD;
44 App_Exception.Raise_Exception;
45 Return;
46 END IF;
47 Close cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.course_cd := x_course_cd;
51 new_references.version_number := x_version_number;
52 new_references.cal_type := x_cal_type;
53 new_references.location_cd := x_location_cd;
54 new_references.attendance_mode := x_attendance_mode;
55 new_references.attendance_type := x_attendance_type;
56 new_references.reference_number := x_reference_number;
57 new_references.coo_id := x_coo_id;
58 new_references.crs_note_type := x_crs_note_type;
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END Set_Column_Values;
71
72 -- Trigger description :-
73 -- "OSS_TST".trg_coon_br_iud
74 -- BEFORE INSERT OR DELETE OR UPDATE
75 -- ON IGS_PS_OFR_OPT_NOTE
76 -- FOR EACH ROW
77
78 PROCEDURE BeforeRowInsertUpdateDelete1(
79 p_inserting IN BOOLEAN DEFAULT FALSE,
80 p_updating IN BOOLEAN DEFAULT FALSE,
81 p_deleting IN BOOLEAN DEFAULT FALSE
82 ) AS
83 v_course_cd IGS_PS_OFR_OPT_NOTE.course_cd%TYPE;
84 v_version_number IGS_PS_OFR_OPT_NOTE.version_number%TYPE;
85 v_message_name VARCHAR2(30);
86 BEGIN
87
88 -- Set IGS_PS_OFR_OPT key.
89 IF p_inserting THEN
90 IGS_PS_GEN_003.CRSP_GET_COO_KEY (
91 new_references.coo_id,
92 new_references.course_cd,
93 new_references.version_number,
94 new_references.cal_type,
95 new_references.location_cd,
96 new_references.attendance_mode,
97 new_references.attendance_type);
98 END IF;
99 -- Set variables.
100 IF p_deleting THEN
101 v_course_cd := old_references.course_cd;
102 v_version_number := old_references.version_number;
103 ELSE -- p_inserting or p_updating
104 v_course_cd := new_references.course_cd;
105 v_version_number := new_references.version_number;
106 END IF;
107 -- Validate the insert/update/delete.
108 IF IGS_PS_VAL_CRS.crsp_val_iud_crv_dtl (
109 v_course_cd,
110 v_version_number,
111 v_message_name) = FALSE THEN
112 Fnd_Message.Set_Name('IGS',v_message_name);
113 IGS_GE_MSG_STACK.ADD;
114 App_Exception.Raise_Exception;
115 END IF;
116
117
118 END BeforeRowInsertUpdateDelete1;
119
120 PROCEDURE Check_Constraints (
121 Column_Name IN VARCHAR2 DEFAULT NULL,
122 Column_Value IN VARCHAR2 DEFAULT NULL
123 )
124 AS
125 BEGIN
126
127 IF column_name is null then
128 NULL;
129 ELSIF upper(Column_name) = 'ATTENDANCE_MODE' then
130 new_references.attendance_mode := column_value;
131 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' then
132 new_references.attendance_type := column_value;
133 ELSIF upper(Column_name) = 'CAL_TYPE' then
134 new_references.cal_type := column_value;
135 ELSIF upper(Column_name) = 'COURSE_CD' then
136 new_references.course_cd := column_value;
137 ELSIF upper(Column_name) = 'LOCATION_CD' then
138 new_references.location_cd := column_value;
139 ELSIF upper(Column_name) = 'CRS_NOTE_TYPE' then
140 new_references.crs_note_type := column_value;
141 END IF;
142
143 IF upper(column_name) = 'ATTENDANCE_MODE' OR
144 column_name is null Then
145 IF ( new_references.attendance_mode <> UPPER(new_references.attendance_mode) ) Then
146 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END IF;
150 END IF;
151
152 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
153 column_name is null Then
154 IF ( new_references.attendance_type <> UPPER(new_references.attendance_type) ) Then
155 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
156 IGS_GE_MSG_STACK.ADD;
157 App_Exception.Raise_Exception;
158 END IF;
159 END IF;
160
161 IF upper(column_name) = 'CAL_TYPE' OR
162 column_name is null Then
163 IF ( new_references.cal_type <> UPPER(new_references.cal_type) ) Then
164 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169
170 IF upper(column_name) = 'COURSE_CD' OR
171 column_name is null Then
172 IF ( new_references.course_cd <> UPPER(new_references.course_cd) ) Then
173 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
174 IGS_GE_MSG_STACK.ADD;
175 App_Exception.Raise_Exception;
176 END IF;
177 END IF;
178
179 IF upper(column_name) = 'LOCATION_CD' OR
180 column_name is null Then
181 IF ( new_references.location_cd <> UPPER(new_references.location_cd) ) Then
182 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END IF;
186 END IF;
187
188 IF upper(column_name) = 'CRS_NOTE_TYPE' OR
189 column_name is null Then
190 IF ( new_references.crs_note_type <> UPPER(new_references.crs_note_type) ) Then
191 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
192 IGS_GE_MSG_STACK.ADD;
193 App_Exception.Raise_Exception;
194 END IF;
195 END IF;
196
197 END Check_Constraints;
198
199 PROCEDURE Check_Parent_Existance AS
200 BEGIN
201
202 IF (((old_references.crs_note_type = new_references.crs_note_type)) OR
203 ((new_references.crs_note_type IS NULL))) THEN
204 NULL;
205 ELSE
206 IF NOT IGS_PS_NOTE_TYPE_PKG.Get_PK_For_Validation (
207 new_references.crs_note_type
208 ) THEN
209 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
210 IGS_GE_MSG_STACK.ADD;
211 App_Exception.Raise_Exception;
212 END IF;
213 END IF;
214
215 IF (((old_references.course_cd = new_references.course_cd) AND
216 (old_references.version_number = new_references.version_number) AND
217 (old_references.cal_type = new_references.cal_type) AND
218 (old_references.location_cd = new_references.location_cd) AND
219 (old_references.attendance_mode = new_references.attendance_mode) AND
220 (old_references.attendance_type = new_references.attendance_type)) OR
221 ((new_references.course_cd IS NULL) OR
222 (new_references.version_number IS NULL) OR
223 (new_references.cal_type IS NULL) OR
224 (new_references.location_cd IS NULL) OR
225 (new_references.attendance_mode IS NULL) OR
226 (new_references.attendance_type IS NULL))) THEN
227 NULL;
228 ELSE
229 IF NOT IGS_PS_OFR_OPT_PKG.Get_PK_For_Validation (
230 new_references.course_cd,
231 new_references.version_number,
232 new_references.cal_type,
233 new_references.location_cd,
234 new_references.attendance_mode,
235 new_references.attendance_type
236 ) THEN
237 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
238 IGS_GE_MSG_STACK.ADD;
239 App_Exception.Raise_Exception;
240 END IF;
241 END IF;
242
243 IF (((old_references.coo_id = new_references.coo_id)) OR
244 ((new_references.coo_id IS NULL))) THEN
245 NULL;
246 ELSE
247 IF NOT IGS_PS_OFR_OPT_PKG.Get_UK_For_Validation (
248 new_references.coo_id
249 ) THEN
250 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 END IF;
254 END IF;
255
256 IF (((old_references.reference_number = new_references.reference_number)) OR
257 ((new_references.reference_number IS NULL))) THEN
258 NULL;
259 ELSE
260 IF NOT IGS_GE_NOTE_PKG.Get_PK_For_Validation (
261 new_references.reference_number
262 ) THEN
263 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
264 IGS_GE_MSG_STACK.ADD;
265 App_Exception.Raise_Exception;
266 END IF;
267 END IF;
268
269 END Check_Parent_Existance;
270
271 FUNCTION Get_PK_For_Validation (
272 x_course_cd IN VARCHAR2,
273 x_version_number IN NUMBER,
274 x_cal_type IN VARCHAR2,
275 x_location_cd IN VARCHAR2,
276 x_attendance_mode IN VARCHAR2,
277 x_attendance_type IN VARCHAR2,
278 x_reference_number IN NUMBER
279 )
280 RETURN BOOLEAN AS
281
282 CURSOR cur_rowid IS
283 SELECT rowid
284 FROM IGS_PS_OFR_OPT_NOTE
285 WHERE course_cd = x_course_cd
286 AND version_number = x_version_number
287 AND cal_type = x_cal_type
288 AND location_cd = x_location_cd
289 AND attendance_mode = x_attendance_mode
290 AND attendance_type = x_attendance_type
291 AND reference_number = x_reference_number
292 FOR UPDATE NOWAIT;
293
294 lv_rowid cur_rowid%RowType;
295
296 BEGIN
297
298 Open cur_rowid;
299 Fetch cur_rowid INTO lv_rowid;
300 IF (cur_rowid%FOUND) THEN
301 Close cur_rowid;
302 Return (TRUE);
303 ELSE
304 Close cur_rowid;
305 Return (FALSE);
306 END IF;
307
308 END Get_PK_For_Validation;
309
310 PROCEDURE GET_FK_IGS_PS_NOTE_TYPE (
311 x_crs_note_type IN VARCHAR2
312 ) AS
313
314 CURSOR cur_rowid IS
315 SELECT rowid
316 FROM IGS_PS_OFR_OPT_NOTE
317 WHERE crs_note_type = x_crs_note_type ;
318
319 lv_rowid cur_rowid%RowType;
320
321 BEGIN
322
323 Open cur_rowid;
324 Fetch cur_rowid INTO lv_rowid;
325 IF (cur_rowid%FOUND) THEN
326 Close cur_rowid;
327 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COON_CNT_FK');
328 IGS_GE_MSG_STACK.ADD;
329 App_Exception.Raise_Exception;
330 Return;
331 END IF;
332 Close cur_rowid;
333
334 END GET_FK_IGS_PS_NOTE_TYPE;
335
336 PROCEDURE GET_FK_IGS_PS_OFR_OPT (
337 x_course_cd IN VARCHAR2,
338 x_version_number IN NUMBER,
339 x_cal_type IN VARCHAR2,
340 x_location_cd IN VARCHAR2,
341 x_attendance_mode IN VARCHAR2,
342 x_attendance_type IN VARCHAR2
343 ) AS
344
345 CURSOR cur_rowid IS
346 SELECT rowid
347 FROM IGS_PS_OFR_OPT_NOTE
348 WHERE course_cd = x_course_cd
349 AND version_number = x_version_number
350 AND cal_type = x_cal_type
351 AND location_cd = x_location_cd
352 AND attendance_mode = x_attendance_mode
353 AND attendance_type = x_attendance_type ;
354
355 lv_rowid cur_rowid%RowType;
356
357 BEGIN
358
359 Open cur_rowid;
360 Fetch cur_rowid INTO lv_rowid;
361 IF (cur_rowid%FOUND) THEN
362 Close cur_rowid;
363 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COON_COO_FK');
364 IGS_GE_MSG_STACK.ADD;
365 App_Exception.Raise_Exception;
366 Return;
367 END IF;
368 Close cur_rowid;
369
370 END GET_FK_IGS_PS_OFR_OPT;
371
372 PROCEDURE GET_UFK_IGS_PS_OFR_OPT (
373 x_coo_id IN NUMBER
374 ) AS
375
376 CURSOR cur_rowid IS
377 SELECT rowid
378 FROM IGS_PS_OFR_OPT_NOTE
379 WHERE coo_id = x_coo_id ;
380
381 lv_rowid cur_rowid%RowType;
382
383 BEGIN
384
385 Open cur_rowid;
386 Fetch cur_rowid INTO lv_rowid;
387 IF (cur_rowid%FOUND) THEN
388 Close cur_rowid;
389 Fnd_Message.Set_Name ('IGS', 'IGS_PS_COON_COO_FK');
390 IGS_GE_MSG_STACK.ADD;
391 App_Exception.Raise_Exception;
392 Return;
393 END IF;
394 Close cur_rowid;
395
396 END GET_UFK_IGS_PS_OFR_OPT;
397
398 PROCEDURE GET_FK_IGS_GE_NOTE (
399 x_reference_number IN NUMBER
400 ) AS
401
402 CURSOR cur_rowid IS
403 SELECT rowid
404 FROM IGS_PS_OFR_OPT_NOTE
405 WHERE reference_number = x_reference_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_COON_NOTE_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_GE_NOTE;
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_location_cd IN VARCHAR2 DEFAULT NULL,
431 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
432 x_attendance_type IN VARCHAR2 DEFAULT NULL,
433 x_reference_number IN NUMBER DEFAULT NULL,
434 x_coo_id IN NUMBER DEFAULT NULL,
435 x_crs_note_type IN VARCHAR2 DEFAULT NULL,
436 x_creation_date IN DATE DEFAULT NULL,
437 x_created_by IN NUMBER DEFAULT NULL,
438 x_last_update_date IN DATE DEFAULT NULL,
439 x_last_updated_by IN NUMBER DEFAULT NULL,
440 x_last_update_login IN NUMBER DEFAULT NULL
441 ) AS
442 BEGIN
443
444 Set_Column_Values (
445 p_action,
446 x_rowid,
447 x_course_cd,
448 x_version_number,
449 x_cal_type,
450 x_location_cd,
451 x_attendance_mode,
452 x_attendance_type,
453 x_reference_number,
454 x_coo_id,
455 x_crs_note_type,
456 x_creation_date,
457 x_created_by,
458 x_last_update_date,
459 x_last_updated_by,
460 x_last_update_login
461 );
462
463 IF (p_action = 'INSERT') THEN
464 -- Call all the procedures related to Before Insert.
465 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
466 IF Get_PK_For_Validation (
467 new_references.course_cd,
468 new_references.version_number,
469 new_references.cal_type,
470 new_references.location_cd,
471 new_references.attendance_mode,
472 new_references.attendance_type,
473 new_references.reference_number ) THEN
474 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
475 IGS_GE_MSG_STACK.ADD;
476 App_Exception.Raise_Exception;
477 END IF;
478 Check_Constraints;
479 Check_Parent_Existance;
480 ELSIF (p_action = 'UPDATE') THEN
481 -- Call all the procedures related to Before Update.
482 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
483 Check_Constraints;
484 Check_Parent_Existance;
485 ELSIF (p_action = 'DELETE') THEN
486 -- Call all the procedures related to Before Delete.
487 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
488 ELSIF (p_action = 'VALIDATE_INSERT') THEN
489 IF Get_PK_For_Validation (
490 new_references.course_cd,
491 new_references.version_number,
492 new_references.cal_type,
493 new_references.location_cd,
494 new_references.attendance_mode,
495 new_references.attendance_type,
496 new_references.reference_number ) THEN
497 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
498 IGS_GE_MSG_STACK.ADD;
499 App_Exception.Raise_Exception;
500 END IF;
501 Check_Constraints;
502 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
503 Check_Constraints;
504 END IF;
505
506 END Before_DML;
507
508 PROCEDURE After_DML (
509 p_action IN VARCHAR2,
510 x_rowid IN VARCHAR2
511 ) AS
512 BEGIN
513
514 l_rowid := x_rowid;
515
516 END After_DML;
517
518 procedure INSERT_ROW (
519 X_ROWID in out NOCOPY VARCHAR2,
520 X_COURSE_CD in VARCHAR2,
521 X_VERSION_NUMBER in NUMBER,
522 X_CAL_TYPE in VARCHAR2,
523 X_ATTENDANCE_MODE in VARCHAR2,
524 X_REFERENCE_NUMBER in NUMBER,
525 X_ATTENDANCE_TYPE in VARCHAR2,
526 X_LOCATION_CD in VARCHAR2,
527 X_COO_ID in NUMBER,
528 X_CRS_NOTE_TYPE in VARCHAR2,
529 X_MODE in VARCHAR2 default 'R'
530 ) AS
531 cursor C is select ROWID from IGS_PS_OFR_OPT_NOTE
532 where COURSE_CD = X_COURSE_CD
533 and VERSION_NUMBER = X_VERSION_NUMBER
534 and CAL_TYPE = X_CAL_TYPE
535 and ATTENDANCE_MODE = X_ATTENDANCE_MODE
536 and REFERENCE_NUMBER = X_REFERENCE_NUMBER
537 and ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
538 and LOCATION_CD = X_LOCATION_CD;
539 X_LAST_UPDATE_DATE DATE;
540 X_LAST_UPDATED_BY NUMBER;
541 X_LAST_UPDATE_LOGIN NUMBER;
542 begin
543 X_LAST_UPDATE_DATE := SYSDATE;
544 if(X_MODE = 'I') then
545 X_LAST_UPDATED_BY := 1;
546 X_LAST_UPDATE_LOGIN := 0;
547 elsif (X_MODE = 'R') then
548 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
549 if X_LAST_UPDATED_BY is NULL then
550 X_LAST_UPDATED_BY := -1;
551 end if;
552 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
553 if X_LAST_UPDATE_LOGIN is NULL then
554 X_LAST_UPDATE_LOGIN := -1;
555 end if;
556 else
557 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
558 IGS_GE_MSG_STACK.ADD;
559 app_exception.raise_exception;
560 end if;
561 Before_DML (
562 p_action => 'INSERT',
563 x_rowid => X_ROWID,
564 x_course_cd => X_COURSE_CD,
565 x_version_number => X_VERSION_NUMBER,
566 x_cal_type => X_CAL_TYPE,
567 x_location_cd => X_LOCATION_CD,
568 x_attendance_mode => X_ATTENDANCE_MODE,
569 x_attendance_type => X_ATTENDANCE_TYPE,
570 x_reference_number => X_REFERENCE_NUMBER,
571 x_coo_id => X_COO_ID,
572 x_crs_note_type => X_CRS_NOTE_TYPE,
573 x_creation_date => X_LAST_UPDATE_DATE ,
574 x_created_by => X_LAST_UPDATED_BY ,
575 x_last_update_date => X_LAST_UPDATE_DATE ,
576 x_last_updated_by => X_LAST_UPDATED_BY ,
577 x_last_update_login => X_LAST_UPDATE_LOGIN
578 );
579
580 insert into IGS_PS_OFR_OPT_NOTE (
581 COURSE_CD,
582 VERSION_NUMBER,
583 CAL_TYPE,
584 LOCATION_CD,
585 ATTENDANCE_MODE,
586 ATTENDANCE_TYPE,
587 REFERENCE_NUMBER,
588 COO_ID,
589 CRS_NOTE_TYPE,
590 CREATION_DATE,
591 CREATED_BY,
592 LAST_UPDATE_DATE,
593 LAST_UPDATED_BY,
594 LAST_UPDATE_LOGIN
595 ) values (
596 NEW_REFERENCES.COURSE_CD,
597 NEW_REFERENCES.VERSION_NUMBER,
598 NEW_REFERENCES.CAL_TYPE,
599 NEW_REFERENCES.LOCATION_CD,
600 NEW_REFERENCES.ATTENDANCE_MODE,
601 NEW_REFERENCES.ATTENDANCE_TYPE,
602 NEW_REFERENCES.REFERENCE_NUMBER,
603 NEW_REFERENCES.COO_ID,
604 NEW_REFERENCES.CRS_NOTE_TYPE,
605 X_LAST_UPDATE_DATE,
606 X_LAST_UPDATED_BY,
607 X_LAST_UPDATE_DATE,
608 X_LAST_UPDATED_BY,
609 X_LAST_UPDATE_LOGIN
610 );
611
612 open c;
613 fetch c into X_ROWID;
614 if (c%notfound) then
615 close c;
616 raise no_data_found;
617 end if;
618 close c;
619 After_DML (
620 p_action => 'INSERT',
621 x_rowid => X_ROWID
622 );
623 end INSERT_ROW;
624
625 procedure LOCK_ROW (
626 X_ROWID IN VARCHAR2,
627 X_COURSE_CD in VARCHAR2,
628 X_VERSION_NUMBER in NUMBER,
629 X_CAL_TYPE in VARCHAR2,
630 X_ATTENDANCE_MODE in VARCHAR2,
631 X_REFERENCE_NUMBER in NUMBER,
632 X_ATTENDANCE_TYPE in VARCHAR2,
633 X_LOCATION_CD in VARCHAR2,
634 X_COO_ID in NUMBER,
635 X_CRS_NOTE_TYPE in VARCHAR2
636 ) AS
637 cursor c1 is select
638 COO_ID,
639 CRS_NOTE_TYPE
640 from IGS_PS_OFR_OPT_NOTE
641 where ROWID = X_ROWID
642 for update nowait;
643 tlinfo c1%rowtype;
644
645 begin
646 open c1;
647 fetch c1 into tlinfo;
648 if (c1%notfound) then
649 close c1;
650 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
651 IGS_GE_MSG_STACK.ADD;
652 app_exception.raise_exception;
653 return;
654 end if;
655 close c1;
656
657 if ( (tlinfo.COO_ID = X_COO_ID)
658 AND (tlinfo.CRS_NOTE_TYPE = X_CRS_NOTE_TYPE)
659 ) then
660 null;
661 else
662 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
663 IGS_GE_MSG_STACK.ADD;
664 app_exception.raise_exception;
665 end if;
666 return;
667 end LOCK_ROW;
668
669 procedure UPDATE_ROW (
670 X_ROWID IN VARCHAR2,
671 X_COURSE_CD in VARCHAR2,
672 X_VERSION_NUMBER in NUMBER,
673 X_CAL_TYPE in VARCHAR2,
674 X_ATTENDANCE_MODE in VARCHAR2,
675 X_REFERENCE_NUMBER in NUMBER,
676 X_ATTENDANCE_TYPE in VARCHAR2,
677 X_LOCATION_CD in VARCHAR2,
678 X_COO_ID in NUMBER,
679 X_CRS_NOTE_TYPE in VARCHAR2,
680 X_MODE in VARCHAR2 default 'R'
681 ) AS
682 X_LAST_UPDATE_DATE DATE;
683 X_LAST_UPDATED_BY NUMBER;
684 X_LAST_UPDATE_LOGIN NUMBER;
685 begin
686 X_LAST_UPDATE_DATE := SYSDATE;
687 if(X_MODE = 'I') then
688 X_LAST_UPDATED_BY := 1;
689 X_LAST_UPDATE_LOGIN := 0;
690 elsif (X_MODE = 'R') then
691 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
692 if X_LAST_UPDATED_BY is NULL then
693 X_LAST_UPDATED_BY := -1;
694 end if;
695 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
696 if X_LAST_UPDATE_LOGIN is NULL then
697 X_LAST_UPDATE_LOGIN := -1;
698 end if;
699 else
700 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
701 IGS_GE_MSG_STACK.ADD;
702 app_exception.raise_exception;
703 end if;
704 Before_DML (
705 p_action => 'UPDATE',
706 x_rowid => X_ROWID,
707 x_course_cd => X_COURSE_CD,
708 x_version_number => X_VERSION_NUMBER,
709 x_cal_type => X_CAL_TYPE,
710 x_location_cd => X_LOCATION_CD,
711 x_attendance_mode => X_ATTENDANCE_MODE,
712 x_attendance_type => X_ATTENDANCE_TYPE,
713 x_reference_number => X_REFERENCE_NUMBER,
714 x_coo_id => X_COO_ID,
715 x_crs_note_type => X_CRS_NOTE_TYPE,
716 x_creation_date => X_LAST_UPDATE_DATE ,
717 x_created_by => X_LAST_UPDATED_BY ,
718 x_last_update_date => X_LAST_UPDATE_DATE ,
719 x_last_updated_by => X_LAST_UPDATED_BY ,
720 x_last_update_login => X_LAST_UPDATE_LOGIN
721 );
722
723 update IGS_PS_OFR_OPT_NOTE set
724 COO_ID = NEW_REFERENCES.COO_ID,
725 CRS_NOTE_TYPE = NEW_REFERENCES.CRS_NOTE_TYPE,
726 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
727 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
728 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
729 where ROWID = X_ROWID
730 ;
731 if (sql%notfound) then
732 raise no_data_found;
733 end if;
734 After_DML (
735 p_action => 'UPDATE',
736 x_rowid => X_ROWID
737 );
738 end UPDATE_ROW;
739
740 procedure ADD_ROW (
741 X_ROWID in out NOCOPY VARCHAR2,
742 X_COURSE_CD in VARCHAR2,
743 X_VERSION_NUMBER in NUMBER,
744 X_CAL_TYPE in VARCHAR2,
745 X_ATTENDANCE_MODE in VARCHAR2,
746 X_REFERENCE_NUMBER in NUMBER,
747 X_ATTENDANCE_TYPE in VARCHAR2,
748 X_LOCATION_CD in VARCHAR2,
749 X_COO_ID in NUMBER,
750 X_CRS_NOTE_TYPE in VARCHAR2,
751 X_MODE in VARCHAR2 default 'R'
752 ) AS
753 cursor c1 is select rowid from IGS_PS_OFR_OPT_NOTE
754 where COURSE_CD = X_COURSE_CD
755 and VERSION_NUMBER = X_VERSION_NUMBER
756 and CAL_TYPE = X_CAL_TYPE
757 and ATTENDANCE_MODE = X_ATTENDANCE_MODE
758 and REFERENCE_NUMBER = X_REFERENCE_NUMBER
759 and ATTENDANCE_TYPE = X_ATTENDANCE_TYPE
760 and LOCATION_CD = X_LOCATION_CD
761 ;
762 begin
763 open c1;
764 fetch c1 into X_ROWID;
765 if (c1%notfound) then
766 close c1;
767 INSERT_ROW (
768 X_ROWID,
769 X_COURSE_CD,
770 X_VERSION_NUMBER,
771 X_CAL_TYPE,
772 X_ATTENDANCE_MODE,
773 X_REFERENCE_NUMBER,
774 X_ATTENDANCE_TYPE,
775 X_LOCATION_CD,
776 X_COO_ID,
777 X_CRS_NOTE_TYPE,
778 X_MODE);
779 return;
780 end if;
781 close c1;
782 UPDATE_ROW (
783 X_ROWID,
784 X_COURSE_CD,
785 X_VERSION_NUMBER,
786 X_CAL_TYPE,
787 X_ATTENDANCE_MODE,
788 X_REFERENCE_NUMBER,
789 X_ATTENDANCE_TYPE,
790 X_LOCATION_CD,
791 X_COO_ID,
792 X_CRS_NOTE_TYPE,
793 X_MODE);
794 end ADD_ROW;
795
796 procedure DELETE_ROW (
797 X_ROWID in VARCHAR2
798 ) AS
799 begin
800 Before_DML (
801 p_action => 'DELETE',
802 x_rowid => X_ROWID
803 );
804 delete from IGS_PS_OFR_OPT_NOTE
805 where ROWID = X_ROWID;
806 if (sql%notfound) then
807 raise no_data_found;
808 end if;
809 After_DML (
810 p_action => 'DELETE',
811 x_rowid => X_ROWID
812 );
813 end DELETE_ROW;
814
815 end IGS_PS_OFR_OPT_NOTE_PKG;