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