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