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