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