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