[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_PECRS_OFOP_DT_PKG
Source
1 package body IGS_AD_PECRS_OFOP_DT_PKG AS
2 /* $Header: IGSAI35B.pls 115.7 2003/10/30 13:19:57 rghosh ship $*/
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_PECRS_OFOP_DT%RowType;
5 new_references IGS_AD_PECRS_OFOP_DT%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 ,
9 x_adm_cal_type IN VARCHAR2 ,
10 x_adm_ci_sequence_number IN NUMBER ,
11 x_admission_cat IN VARCHAR2 ,
12 x_dt_alias IN VARCHAR2 ,
13 x_dai_sequence_number IN NUMBER ,
14 x_sequence_number IN NUMBER ,
15 x_s_admission_process_type IN VARCHAR2 ,
16 x_course_cd IN VARCHAR2 ,
17 x_version_number IN NUMBER ,
18 x_acad_cal_type IN VARCHAR2 ,
19 x_location_cd IN VARCHAR2 ,
20 x_attendance_mode IN VARCHAR2 ,
21 x_attendance_type IN VARCHAR2 ,
22 x_rollover_inclusion_ind IN VARCHAR2 ,
23 x_creation_date IN DATE ,
24 x_created_by IN NUMBER ,
25 x_last_update_date IN DATE ,
26 x_last_updated_by IN NUMBER ,
27 x_last_update_login IN NUMBER
28 ) AS
29
30 CURSOR cur_old_ref_values IS
31 SELECT *
32 FROM IGS_AD_PECRS_OFOP_DT
33 WHERE rowid = x_rowid;
34
35 BEGIN
36
37 l_rowid := x_rowid;
38
39 -- Code for setting the Old and New Reference Values.
40 -- Populate Old Values.
41 Open cur_old_ref_values;
42 Fetch cur_old_ref_values INTO old_references;
43 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
44 Close cur_old_ref_values;
45 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
46 IGS_GE_MSG_STACK.ADD;
47 App_Exception.Raise_Exception;
48 Return;
49 END IF;
50 Close cur_old_ref_values;
51
52 -- Populate New Values.
53 new_references.adm_cal_type := x_adm_cal_type;
54 new_references.adm_ci_sequence_number := x_adm_ci_sequence_number;
55 new_references.admission_cat := x_admission_cat;
56 new_references.dt_alias := x_dt_alias;
57 new_references.dai_sequence_number := x_dai_sequence_number;
58 new_references.sequence_number := x_sequence_number;
59 new_references.s_admission_process_type := x_s_admission_process_type;
60 new_references.course_cd := x_course_cd;
61 new_references.version_number := x_version_number;
62 new_references.acad_cal_type := x_acad_cal_type;
63 new_references.location_cd := x_location_cd;
64 new_references.attendance_mode := x_attendance_mode;
65 new_references.attendance_type := x_attendance_type;
66 new_references.rollover_inclusion_ind := x_rollover_inclusion_ind;
67 IF (p_action = 'UPDATE') THEN
68 new_references.creation_date := old_references.creation_date;
69 new_references.created_by := old_references.created_by;
70 ELSE
71 new_references.creation_date := x_creation_date;
72 new_references.created_by := x_created_by;
73 END IF;
74 new_references.last_update_date := x_last_update_date;
75 new_references.last_updated_by := x_last_updated_by;
76 new_references.last_update_login := x_last_update_login;
77
78 END Set_Column_Values;
79
80 PROCEDURE BeforeRowInsertUpdateDelete1(
81 p_inserting IN BOOLEAN ,
82 p_updating IN BOOLEAN ,
83 p_deleting IN BOOLEAN
84 ) AS
85 v_message_name VARCHAR2(30);
86 v_cal_type IGS_CA_INST.cal_type%TYPE;
87 v_sequence_number IGS_CA_INST.sequence_number%TYPE;
88 v_start_dt IGS_CA_INST.start_dt%TYPE;
89 v_end_dt IGS_CA_INST.end_dt%TYPE;
90 v_alternate_code IGS_CA_INST.alternate_code%TYPE;
91 BEGIN
92 -- Validate that the admission period is not inactive
93 IF p_inserting OR p_updating THEN
94 v_cal_type := new_references.adm_cal_type;
95 v_sequence_number := new_references.adm_ci_sequence_number;
96 ELSE -- must be p_deleting
97 v_cal_type := old_references.adm_cal_type;
98 v_sequence_number := old_references.adm_ci_sequence_number;
99 END IF;
100 IF IGS_AD_VAL_APAC.admp_val_adm_ci(
101 v_cal_type,
102 v_sequence_number,
103 v_start_dt,
104 v_end_dt,
105 v_alternate_code,
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 IF p_inserting OR p_updating THEN
112 /* Partial rollover is now handled
113 -- Set rollover inclusion indicator to 'N', this functionality is
114 -- currently not available
115 IF new_references.rollover_inclusion_ind = 'Y' THEN
116 new_references.rollover_inclusion_ind := 'N';
117 END IF;*/
118 -- Validate admission period date alias
119 IF IGS_AD_VAL_APCOOD.admp_val_apcood_da(
120 new_references.dt_alias,
121 v_message_name) = FALSE THEN
122 Fnd_Message.Set_Name('IGS',v_message_name);
123 IGS_GE_MSG_STACK.ADD;
124 App_Exception.Raise_Exception;
125 END IF;
126 -- Validate admission period date course offering
127 IF new_references.course_cd IS NOT NULL THEN
128 IF IGS_AD_VAL_APCOOD.admp_val_apcood_co(
129 new_references.course_cd,
130 new_references.version_number,
131 new_references.acad_cal_type,
132 new_references.admission_cat,
133 new_references.s_admission_process_type,
134 new_references.adm_cal_type,
135 new_references.adm_ci_sequence_number,
136 v_message_name) = FALSE THEN
137 Fnd_Message.Set_Name('IGS',v_message_name);
138 IGS_GE_MSG_STACK.ADD;
139 App_Exception.Raise_Exception;
140 END IF;
141 END IF;
142 -- Validate admission period date course offering option components
143 IF IGS_AD_VAL_APCOOD.admp_val_apcood_opt(
144 new_references.course_cd,
145 new_references.version_number,
146 new_references.acad_cal_type,
147 new_references.location_cd,
148 new_references.attendance_mode,
149 new_references.attendance_type,
150 new_references.adm_cal_type,
151 new_references.adm_ci_sequence_number,
152 new_references.admission_cat,
153 new_references.s_admission_process_type,
154 v_message_name) = FALSE THEN
155 Fnd_Message.Set_Name('IGS',v_message_name);
156 IGS_GE_MSG_STACK.ADD;
157 App_Exception.Raise_Exception;
158 END IF;
159 END IF;
160
161
162 END BeforeRowInsertUpdateDelete1;
163
164 PROCEDURE AfterRowInsertUpdate2(
165 p_inserting IN BOOLEAN ,
166 p_updating IN BOOLEAN ,
167 p_deleting IN BOOLEAN
168 ) AS
169 v_message_name VARCHAR2(30);
170 BEGIN
171 IF p_inserting OR p_updating THEN
172 -- Validate inserting/updating the admission period IGS_PS_COURSE offering option
173 -- date
174 IF IGS_AD_VAL_APCOOD.admp_val_apcood_ins(
175 new_references.adm_cal_type,
176 new_references.adm_ci_sequence_number,
177 new_references.admission_cat,
178 new_references.dt_alias,
179 new_references.dai_sequence_number,
180 new_references.sequence_number,
181 v_message_name) = FALSE THEN
182 Fnd_Message.Set_Name('IGS',v_message_name);
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END IF;
186 IF IGS_AD_VAL_APCOOD.admp_val_apcood_link (
187 new_references.adm_cal_type,
188 new_references.adm_ci_sequence_number,
189 new_references.admission_cat,
190 new_references.dt_alias,
191 new_references.dai_sequence_number,
192 new_references.sequence_number,
193 new_references.s_admission_process_type,
194 new_references.course_cd,
195 new_references.version_number,
196 new_references.acad_cal_type,
197 new_references.location_cd,
198 new_references.attendance_mode,
199 new_references.attendance_type,
200 v_message_name) = FALSE THEN
201 Fnd_Message.Set_Name('IGS',v_message_name);
202 IGS_GE_MSG_STACK.ADD;
203 App_Exception.Raise_Exception;
204 END IF;
205 END IF;
206 END AfterRowInsertUpdate2;
207
208 PROCEDURE Check_Constraints (
209 Column_Name IN VARCHAR2 ,
210 Column_Value IN VARCHAR2
211 )
212 AS
213 BEGIN
214 IF column_name is null then
215 NULL;
216 ELSIF upper(Column_name) = 'DT_ALIAS' then
217 new_references.dt_alias := column_value;
218 ELSIF upper(Column_name) = 'ADM_CAL_TYPE' then
219 new_references.adm_cal_type := column_value;
220 ELSIF upper(Column_name) = 'ADMISSION_CAT' then
221 new_references.admission_cat := column_value;
222 ELSIF upper(Column_name) = 'S_ADMISSION_PROCESS_TYPE' then
223 new_references.s_admission_process_type := column_value;
224 ELSIF upper(Column_name) = 'COURSE_CD' then
225 new_references.course_cd := column_value;
226 ELSIF upper(Column_name) = 'ACAD_CAL_TYPE' then
227 new_references.acad_cal_type := column_value;
228 ELSIF upper(Column_name) = 'LOCATION_CD' then
229 new_references.location_cd := column_value;
230 ELSIF upper(Column_name) = 'ATTENDANCE_MODE' then
231 new_references.attendance_mode := column_value;
232 ELSIF upper(Column_name) = 'ATTENDANCE_TYPE' then
233 new_references.attendance_type := column_value;
234 ELSIF upper(Column_name) = 'ADM_CI_SEQUENCE_NUMBER' then
235 new_references.adm_ci_sequence_number := igs_ge_number.to_num(column_value);
236 ELSIF upper(Column_name) = 'DAI_SEQUENCE_NUMBER' then
237 new_references.dai_sequence_number := igs_ge_number.to_num(column_value);
238 ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' then
239 new_references.sequence_number := igs_ge_number.to_num(column_value);
240 ELSIF upper(Column_name) = 'VERSION_NUMBER' then
241 new_references.version_number := igs_ge_number.to_num(column_value);
242 ELSIF upper(Column_name) = 'ROLLOVER_INCLUSION_IND' then
243 new_references.rollover_inclusion_ind := column_value;
244 END IF;
245
246 IF upper(column_name) = 'DT_ALIAS' OR
247 column_name is null Then
248 IF new_references.dt_alias <> UPPER(new_references.dt_alias) Then
249 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END IF;
253 END IF;
254
255 IF upper(column_name) = 'ADM_CAL_TYPE' OR
256 column_name is null Then
257 IF new_references.adm_cal_type <>
258 UPPER(new_references.adm_cal_type) Then
259 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
260 IGS_GE_MSG_STACK.ADD;
261 App_Exception.Raise_Exception;
262 END IF;
263 END IF;
264
265 IF upper(column_name) = 'ADMISSION_CAT' OR
266 column_name is null Then
267 IF new_references.admission_cat <>
268 UPPER(new_references.admission_cat) Then
269 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
270 IGS_GE_MSG_STACK.ADD;
271 App_Exception.Raise_Exception;
272 END IF;
273 END IF;
274
275 IF upper(column_name) = 'S_ADMISSION_PROCESS_TYPE' OR
276 column_name is null Then
277 IF new_references.s_admission_process_type <>
278 UPPER(new_references.s_admission_process_type) Then
279 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
280 IGS_GE_MSG_STACK.ADD;
281 App_Exception.Raise_Exception;
282 END IF;
283 END IF;
284
285 IF upper(column_name) = 'COURSE_CD' OR
286 column_name is null Then
287 IF new_references.course_cd <>
288 UPPER(new_references.course_cd) Then
289 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
290 IGS_GE_MSG_STACK.ADD;
291 App_Exception.Raise_Exception;
292 END IF;
293 END IF;
294
295 IF upper(column_name) = 'ACAD_CAL_TYPE' OR
296 column_name is null Then
297 IF new_references.acad_cal_type <>
298 UPPER(new_references.acad_cal_type) Then
299 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
300 IGS_GE_MSG_STACK.ADD;
301 App_Exception.Raise_Exception;
302 END IF;
303 END IF;
304
305 IF upper(column_name) = 'LOCATION_CD' OR
306 column_name is null Then
307 IF new_references.location_cd <>
308 UPPER(new_references.location_cd) Then
309 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
310 IGS_GE_MSG_STACK.ADD;
311 App_Exception.Raise_Exception;
312 END IF;
313 END IF;
314
315 IF upper(column_name) = 'ATTENDANCE_MODE' OR
316 column_name is null Then
317 IF new_references.attendance_mode <>
318 UPPER(new_references.attendance_mode) Then
319 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
320 IGS_GE_MSG_STACK.ADD;
321 App_Exception.Raise_Exception;
322 END IF;
323 END IF;
324
325 IF upper(column_name) = 'ATTENDANCE_TYPE' OR
326 column_name is null Then
327 IF new_references.attendance_type <>
328 UPPER(new_references.attendance_type) Then
329 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 END IF;
333 END IF;
334
335 IF upper(column_name) = 'ADM_CI_SEQUENCE_NUMBER' OR
336 column_name is null Then
337 IF new_references.adm_ci_sequence_number < 1 OR
338 new_references.adm_ci_sequence_number > 999999 Then
339 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
340 IGS_GE_MSG_STACK.ADD;
341 App_Exception.Raise_Exception;
342 END IF;
343 END IF;
344
345 IF upper(column_name) = 'DAI_SEQUENCE_NUMBER' OR
346 column_name is null Then
347 IF new_references.dai_sequence_number < 1 OR
348 new_references.dai_sequence_number > 999999 Then
349 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
350 IGS_GE_MSG_STACK.ADD;
351 App_Exception.Raise_Exception;
352 END IF;
353 END IF;
354
355 IF upper(column_name) = 'SEQUENCE_NUMBER' OR
356 column_name is null Then
357 IF new_references.sequence_number < 1 OR
358 new_references.sequence_number > 999999 Then
359 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
360 IGS_GE_MSG_STACK.ADD;
361 App_Exception.Raise_Exception;
362 END IF;
363 END IF;
364
365 IF upper(column_name) = 'VERSION_NUMBER' OR
366 column_name is null Then
367 IF new_references.version_number < 1 OR
368 new_references.version_number > 999 Then
369 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
370 IGS_GE_MSG_STACK.ADD;
371 App_Exception.Raise_Exception;
372 END IF;
373 END IF;
374
375 IF upper(column_name) = 'ROLLOVER_INCLUSION_IND' OR
376 column_name is null Then
377 IF new_references.rollover_inclusion_ind NOT IN ('Y','N') Then
378 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
379 IGS_GE_MSG_STACK.ADD;
380 App_Exception.Raise_Exception;
381 END IF;
382 END IF;
383
384 END Check_Constraints;
385
386 PROCEDURE Check_Parent_Existance AS
387 BEGIN
388
389 IF (((old_references.attendance_mode = new_references.attendance_mode)) OR
390 ((new_references.attendance_mode IS NULL))) THEN
391 NULL;
392 ELSE
393 IF NOT IGS_EN_ATD_MODE_PKG.Get_PK_For_Validation (
394 new_references.attendance_mode
395 ) THEN
396 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
397 IGS_GE_MSG_STACK.ADD;
398 App_Exception.Raise_Exception;
399 END IF;
400 END IF;
401
402 IF (((old_references.adm_cal_type = new_references.adm_cal_type) AND
403 (old_references.adm_ci_sequence_number = new_references.adm_ci_sequence_number) AND
404 (old_references.admission_cat = new_references.admission_cat)) OR
405 ((new_references.adm_cal_type IS NULL) OR
406 (new_references.adm_ci_sequence_number IS NULL) OR
407 (new_references.admission_cat IS NULL))) THEN
408 NULL;
409 ELSE
410 IF NOT IGS_AD_PERD_AD_CAT_PKG.Get_PK_For_Validation (
411 new_references.adm_cal_type,
412 new_references.adm_ci_sequence_number,
413 new_references.admission_cat
414 ) THEN
415 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
416 IGS_GE_MSG_STACK.ADD;
417 App_Exception.Raise_Exception;
418 END IF;
419 END IF;
420
421 IF (((old_references.adm_cal_type = new_references.adm_cal_type) AND
422 (old_references.adm_ci_sequence_number = new_references.adm_ci_sequence_number) AND
423 (old_references.admission_cat = new_references.admission_cat) AND
424 (old_references.s_admission_process_type = new_references.s_admission_process_type)) OR
425 ((new_references.adm_cal_type IS NULL) OR
426 (new_references.adm_ci_sequence_number IS NULL) OR
427 (new_references.admission_cat IS NULL) OR
428 (new_references.s_admission_process_type IS NULL))) THEN
429 NULL;
430 ELSE
431 IF NOT IGS_AD_PRD_AD_PRC_CA_PKG.Get_PK_For_Validation (
432 new_references.adm_cal_type,
433 new_references.adm_ci_sequence_number,
434 new_references.admission_cat,
435 new_references.s_admission_process_type ,
436 'N'
437 ) THEN
438 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
439 IGS_GE_MSG_STACK.ADD;
440 App_Exception.Raise_Exception;
441 END IF;
442 END IF;
443
444 IF (((old_references.attendance_type = new_references.attendance_type)) OR
445 ((new_references.attendance_type IS NULL))) THEN
446 NULL;
447 ELSE
448 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
449 new_references.attendance_type
450 ) THEN
451 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
452 IGS_GE_MSG_STACK.ADD;
453 App_Exception.Raise_Exception;
454 END IF;
455 END IF;
456
457 IF (((old_references.course_cd = new_references.course_cd) AND
458 (old_references.version_number = new_references.version_number) AND
459 (old_references.acad_cal_type = new_references.acad_cal_type)) OR
460 ((new_references.course_cd IS NULL) OR
461 (new_references.version_number IS NULL) OR
462 (new_references.acad_cal_type IS NULL))) THEN
463 NULL;
464 ELSE
465 IF NOT IGS_PS_OFR_PKG.Get_PK_For_Validation (
466 new_references.course_cd,
467 new_references.version_number,
468 new_references.acad_cal_type
469 ) THEN
470 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
471 IGS_GE_MSG_STACK.ADD;
472 App_Exception.Raise_Exception;
473 END IF;
474 END IF;
475
476 IF (((old_references.dt_alias = new_references.dt_alias) AND
477 (old_references.dai_sequence_number = new_references.dai_sequence_number) AND
478 (old_references.adm_cal_type = new_references.adm_cal_type) AND
479 (old_references.adm_ci_sequence_number = new_references.adm_ci_sequence_number)) OR
480 ((new_references.dt_alias IS NULL) OR
481 (new_references.dai_sequence_number IS NULL) OR
482 (new_references.adm_cal_type IS NULL) OR
483 (new_references.adm_ci_sequence_number IS NULL))) THEN
484 NULL;
485 ELSE
486 IF NOT IGS_CA_DA_INST_PKG.Get_PK_For_Validation (
487 new_references.dt_alias,
488 new_references.dai_sequence_number,
489 new_references.adm_cal_type,
490 new_references.adm_ci_sequence_number
491 )THEN
492 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
493 IGS_GE_MSG_STACK.ADD;
494 App_Exception.Raise_Exception;
495 END IF;
496
497 END IF;
498
499 IF (((old_references.location_cd = new_references.location_cd)) OR
500 ((new_references.location_cd IS NULL))) THEN
501 NULL;
502 ELSE
503 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
504 new_references.location_cd , 'N'
505 )THEN
506 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
507 IGS_GE_MSG_STACK.ADD;
508 App_Exception.Raise_Exception;
509 END IF;
510 END IF;
511
512 IF (((old_references.s_admission_process_type = new_references.s_admission_process_type)) OR
513 ((new_references.s_admission_process_type IS NULL))) THEN
514 NULL;
515 ELSE
516 IF NOT IGS_LOOKUPS_VIEW_PKG.Get_PK_For_Validation(
517 'ADMISSION_PROCESS_TYPE',
518 new_references.s_admission_process_type
519 )THEN
520 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
521 IGS_GE_MSG_STACK.ADD;
522 App_Exception.Raise_Exception;
523 END IF;
524 END IF;
525
526 END Check_Parent_Existance;
527
528 FUNCTION Get_PK_For_Validation (
529 x_adm_cal_type IN VARCHAR2,
530 x_adm_ci_sequence_number IN NUMBER,
531 x_admission_cat IN VARCHAR2,
532 x_dt_alias IN VARCHAR2,
533 x_dai_sequence_number IN NUMBER,
534 x_sequence_number IN NUMBER
535 )
536 RETURN BOOLEAN
537 AS
538
539 CURSOR cur_rowid IS
540 SELECT rowid
541 FROM IGS_AD_PECRS_OFOP_DT
542 WHERE adm_cal_type = x_adm_cal_type
543 AND adm_ci_sequence_number = x_adm_ci_sequence_number
544 AND admission_cat = x_admission_cat
545 AND dt_alias = x_dt_alias
546 AND dai_sequence_number = x_dai_sequence_number
547 AND sequence_number = x_sequence_number
548 FOR UPDATE NOWAIT;
549
550 lv_rowid cur_rowid%RowType;
551
552 BEGIN
553
554 Open cur_rowid;
555 Fetch cur_rowid INTO lv_rowid;
556 IF (cur_rowid%FOUND) THEN
557 Close cur_rowid;
558 Return (TRUE);
559 ELSE
560 Close cur_rowid;
561 Return (FALSE);
562 END IF;
563 END Get_PK_For_Validation;
564
565 PROCEDURE GET_FK_IGS_EN_ATD_MODE (
566 x_attendance_mode IN VARCHAR2
567 ) AS
568
569 CURSOR cur_rowid IS
570 SELECT rowid
571 FROM IGS_AD_PECRS_OFOP_DT
572 WHERE attendance_mode = x_attendance_mode ;
573
574 lv_rowid cur_rowid%RowType;
575
576 BEGIN
577
578 Open cur_rowid;
579 Fetch cur_rowid INTO lv_rowid;
580 IF (cur_rowid%FOUND) THEN
581 Close cur_rowid;
582 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOOD_AM_FK');
583 IGS_GE_MSG_STACK.ADD;
584 App_Exception.Raise_Exception;
585 Return;
586 END IF;
587 Close cur_rowid;
588
589 END GET_FK_IGS_EN_ATD_MODE;
590
591 PROCEDURE GET_FK_IGS_AD_PERD_AD_CAT (
592 x_adm_cal_type IN VARCHAR2,
593 x_adm_ci_sequence_number IN NUMBER,
594 x_admission_cat IN VARCHAR2
595 ) AS
596
597 CURSOR cur_rowid IS
598 SELECT rowid
599 FROM IGS_AD_PECRS_OFOP_DT
600 WHERE adm_cal_type = x_adm_cal_type
601 AND adm_ci_sequence_number = x_adm_ci_sequence_number
602 AND admission_cat = x_admission_cat ;
603
604 lv_rowid cur_rowid%RowType;
605
606 BEGIN
607
608 Open cur_rowid;
609 Fetch cur_rowid INTO lv_rowid;
610 IF (cur_rowid%FOUND) THEN
611 Close cur_rowid;
612 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOOD_APAC_FK');
613 IGS_GE_MSG_STACK.ADD;
614 App_Exception.Raise_Exception;
615 Return;
616 END IF;
617 Close cur_rowid;
618
619 END GET_FK_IGS_AD_PERD_AD_CAT;
620
621 PROCEDURE GET_FK_IGS_AD_PRD_AD_PRC_CA (
622 x_adm_cal_type IN VARCHAR2,
623 x_adm_ci_sequence_number IN NUMBER,
624 x_admission_cat IN VARCHAR2,
625 x_s_admission_process_type IN VARCHAR2
626 ) AS
627
628 CURSOR cur_rowid IS
629 SELECT rowid
630 FROM IGS_AD_PECRS_OFOP_DT
631 WHERE adm_cal_type = x_adm_cal_type
632 AND adm_ci_sequence_number = x_adm_ci_sequence_number
633 AND admission_cat = x_admission_cat
634 AND s_admission_process_type = x_s_admission_process_type ;
635
636 lv_rowid cur_rowid%RowType;
637
638 BEGIN
639
640 Open cur_rowid;
641 Fetch cur_rowid INTO lv_rowid;
642 IF (cur_rowid%FOUND) THEN
643 Close cur_rowid;
644 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOOD_APAPC_FK');
645 IGS_GE_MSG_STACK.ADD;
646 App_Exception.Raise_Exception;
647 Return;
648 END IF;
649 Close cur_rowid;
650
651 END GET_FK_IGS_AD_PRD_AD_PRC_CA;
652
653 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
654 x_attendance_type IN VARCHAR2
655 ) AS
656
657 CURSOR cur_rowid IS
658 SELECT rowid
659 FROM IGS_AD_PECRS_OFOP_DT
660 WHERE attendance_type = x_attendance_type ;
661
662 lv_rowid cur_rowid%RowType;
663
664 BEGIN
665
666 Open cur_rowid;
667 Fetch cur_rowid INTO lv_rowid;
668 IF (cur_rowid%FOUND) THEN
669 Close cur_rowid;
670 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOOD_ATT_FK');
671 IGS_GE_MSG_STACK.ADD;
672 App_Exception.Raise_Exception;
673 Return;
674 END IF;
675 Close cur_rowid;
676
677 END GET_FK_IGS_EN_ATD_TYPE;
678
679 PROCEDURE GET_FK_IGS_PS_OFR (
680 x_course_cd IN VARCHAR2,
681 x_version_number IN NUMBER,
682 x_cal_type IN VARCHAR2
683 ) AS
684
685 CURSOR cur_rowid IS
686 SELECT rowid
687 FROM IGS_AD_PECRS_OFOP_DT
688 WHERE course_cd = x_course_cd
689 AND version_number = x_version_number
690 AND acad_cal_type = x_cal_type ;
691
692 lv_rowid cur_rowid%RowType;
693
694 BEGIN
695
696 Open cur_rowid;
697 Fetch cur_rowid INTO lv_rowid;
698 IF (cur_rowid%FOUND) THEN
699 Close cur_rowid;
700 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOOD_CO_FK');
701 IGS_GE_MSG_STACK.ADD;
702 App_Exception.Raise_Exception;
703 Return;
704 END IF;
705 Close cur_rowid;
706
707 END GET_FK_IGS_PS_OFR;
708
709 PROCEDURE GET_FK_IGS_CA_DA_INST (
710 x_dt_alias IN VARCHAR2,
711 x_sequence_number IN NUMBER,
712 x_cal_type IN VARCHAR2,
713 x_ci_sequence_number IN NUMBER
714 ) AS
715
716 CURSOR cur_rowid IS
717 SELECT rowid
718 FROM IGS_AD_PECRS_OFOP_DT
719 WHERE dt_alias = x_dt_alias
720 AND dai_sequence_number = x_sequence_number
721 AND adm_cal_type = x_cal_type
722 AND adm_ci_sequence_number = x_ci_sequence_number ;
723
724 lv_rowid cur_rowid%RowType;
725
726 BEGIN
727
728 Open cur_rowid;
729 Fetch cur_rowid INTO lv_rowid;
730 IF (cur_rowid%FOUND) THEN
731 Close cur_rowid;
732 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOOD_DAI_FK');
733 IGS_GE_MSG_STACK.ADD;
734 App_Exception.Raise_Exception;
735 Return;
736 END IF;
737 Close cur_rowid;
738
739 END GET_FK_IGS_CA_DA_INST;
740
741 PROCEDURE GET_FK_IGS_AD_LOCATION (
742 x_location_cd IN VARCHAR2
743 ) AS
744
745 CURSOR cur_rowid IS
746 SELECT rowid
747 FROM IGS_AD_PECRS_OFOP_DT
748 WHERE location_cd = x_location_cd ;
749
750 lv_rowid cur_rowid%RowType;
751
752 BEGIN
753
754 Open cur_rowid;
755 Fetch cur_rowid INTO lv_rowid;
756 IF (cur_rowid%FOUND) THEN
757 Close cur_rowid;
758 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOOD_LOC_FK');
759 IGS_GE_MSG_STACK.ADD;
760 App_Exception.Raise_Exception;
761 Return;
762 END IF;
763 Close cur_rowid;
764
765 END GET_FK_IGS_AD_LOCATION;
766
767 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW(
768 x_s_admission_process_type IN VARCHAR2
769 ) AS
770
771 CURSOR cur_rowid IS
772 SELECT rowid
773 FROM IGS_AD_PECRS_OFOP_DT
774 WHERE s_admission_process_type = x_s_admission_process_type ;
775
776 lv_rowid cur_rowid%RowType;
777
778 BEGIN
779
780 Open cur_rowid;
781 Fetch cur_rowid INTO lv_rowid;
782 IF (cur_rowid%FOUND) THEN
783 Close cur_rowid;
784 Fnd_Message.Set_Name ('IGS', 'IGS_AD_APCOOD_SLV_FK');
785 IGS_GE_MSG_STACK.ADD;
786 App_Exception.Raise_Exception;
787 Return;
788 END IF;
789 Close cur_rowid;
790
791 END GET_FK_IGS_LOOKUPS_VIEW;
792
793 PROCEDURE Before_DML (
794 p_action IN VARCHAR2,
795 x_rowid IN VARCHAR2 ,
796 x_adm_cal_type IN VARCHAR2 ,
797 x_adm_ci_sequence_number IN NUMBER ,
798 x_admission_cat IN VARCHAR2 ,
799 x_dt_alias IN VARCHAR2 ,
800 x_dai_sequence_number IN NUMBER ,
801 x_sequence_number IN NUMBER ,
802 x_s_admission_process_type IN VARCHAR2 ,
803 x_course_cd IN VARCHAR2 ,
804 x_version_number IN NUMBER ,
805 x_acad_cal_type IN VARCHAR2 ,
806 x_location_cd IN VARCHAR2 ,
807 x_attendance_mode IN VARCHAR2 ,
808 x_attendance_type IN VARCHAR2 ,
809 x_rollover_inclusion_ind IN VARCHAR2 ,
810 x_creation_date IN DATE ,
811 x_created_by IN NUMBER ,
812 x_last_update_date IN DATE ,
813 x_last_updated_by IN NUMBER ,
814 x_last_update_login IN NUMBER
815 ) AS
816 BEGIN
817
818 Set_Column_Values (
819 p_action,
820 x_rowid,
821 x_adm_cal_type,
822 x_adm_ci_sequence_number,
823 x_admission_cat,
824 x_dt_alias,
825 x_dai_sequence_number,
826 x_sequence_number,
827 x_s_admission_process_type,
828 x_course_cd,
829 x_version_number,
830 x_acad_cal_type,
831 x_location_cd,
832 x_attendance_mode,
833 x_attendance_type,
834 x_rollover_inclusion_ind,
835 x_creation_date,
836 x_created_by,
837 x_last_update_date,
838 x_last_updated_by,
839 x_last_update_login
840 );
841
842 IF (p_action = 'INSERT') THEN
843 -- Call all the procedures related to Before Insert.
844 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE, p_updating => FALSE, p_deleting => FALSE);
845 IF Get_PK_For_Validation (
846 new_references.adm_cal_type,
847 new_references.adm_ci_sequence_number,
848 new_references.admission_cat,
849 new_references.dt_alias,
850 new_references.dai_sequence_number,
851 new_references.sequence_number
852 ) THEN
853 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
854 IGS_GE_MSG_STACK.ADD;
855 App_Exception.Raise_Exception;
856 END IF;
857 Check_Constraints;
858 Check_Parent_Existance;
859 ELSIF (p_action = 'UPDATE') THEN
860 -- Call all the procedures related to Before Update.
861 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating => TRUE, p_deleting => FALSE);
862 Check_Constraints;
863 Check_Parent_Existance;
864 ELSIF (p_action = 'DELETE') THEN
865 -- Call all the procedures related to Before Delete.
866 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating => FALSE, p_deleting => TRUE);
867 ELSIF (p_action = 'VALIDATE_INSERT') THEN
868 IF Get_PK_For_Validation (
869 new_references.adm_cal_type,
870 new_references.adm_ci_sequence_number,
871 new_references.admission_cat,
872 new_references.dt_alias,
873 new_references.dai_sequence_number,
874 new_references.sequence_number
875 ) THEN
876 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
877 IGS_GE_MSG_STACK.ADD;
878 App_Exception.Raise_Exception;
879 END IF;
880 Check_Constraints;
881 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
882 Check_Constraints;
883 END IF;
884 END Before_DML;
885
886 PROCEDURE After_DML (
887 p_action IN VARCHAR2,
888 x_rowid IN VARCHAR2
889 ) AS
890 BEGIN
891
892 l_rowid := x_rowid;
893
894 IF (p_action = 'INSERT') THEN
895 AfterRowInsertUpdate2 ( p_inserting => TRUE, p_updating => FALSE , p_deleting => FALSE );
896 ELSIF (p_action = 'UPDATE') THEN
897 AfterRowInsertUpdate2 ( p_inserting => FALSE, p_updating => TRUE , p_deleting => FALSE);
898 END IF;
899
900 END After_DML;
901
902 procedure INSERT_ROW (
903 X_ROWID in out NOCOPY VARCHAR2,
904 X_ADM_CAL_TYPE in VARCHAR2,
905 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
906 X_ADMISSION_CAT in VARCHAR2,
907 X_DT_ALIAS in VARCHAR2,
908 X_DAI_SEQUENCE_NUMBER in NUMBER,
909 X_SEQUENCE_NUMBER in NUMBER,
910 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
911 X_COURSE_CD in VARCHAR2,
912 X_VERSION_NUMBER in NUMBER,
913 X_ACAD_CAL_TYPE in VARCHAR2,
914 X_LOCATION_CD in VARCHAR2,
915 X_ATTENDANCE_MODE in VARCHAR2,
916 X_ATTENDANCE_TYPE in VARCHAR2,
917 X_ROLLOVER_INCLUSION_IND in VARCHAR2,
918 X_MODE in VARCHAR2
919 ) AS
920 cursor C is select ROWID from IGS_AD_PECRS_OFOP_DT
921 where ADM_CAL_TYPE = X_ADM_CAL_TYPE
922 and ADM_CI_SEQUENCE_NUMBER = X_ADM_CI_SEQUENCE_NUMBER
923 and ADMISSION_CAT = X_ADMISSION_CAT
924 and DT_ALIAS = X_DT_ALIAS
925 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
926 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
927 X_LAST_UPDATE_DATE DATE;
928 X_LAST_UPDATED_BY NUMBER;
929 X_LAST_UPDATE_LOGIN NUMBER;
930 begin
931 X_LAST_UPDATE_DATE := SYSDATE;
932 if(X_MODE = 'I') then
933 X_LAST_UPDATED_BY := 1;
934 X_LAST_UPDATE_LOGIN := 0;
935 elsif (X_MODE = 'R') then
936 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
937 if X_LAST_UPDATED_BY is NULL then
938 X_LAST_UPDATED_BY := -1;
939 end if;
940 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
941 if X_LAST_UPDATE_LOGIN is NULL then
942 X_LAST_UPDATE_LOGIN := -1;
943 end if;
944 else
945 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
946 IGS_GE_MSG_STACK.ADD;
947 app_exception.raise_exception;
948 end if;
949 Before_DML(p_action =>'INSERT',
950 x_rowid =>X_ROWID,
951 x_adm_cal_type => X_ADM_CAL_TYPE,
952 x_adm_ci_sequence_number => X_ADM_CI_SEQUENCE_NUMBER,
953 x_admission_cat => X_ADMISSION_CAT,
954 x_dt_alias => X_DT_ALIAS,
955 x_dai_sequence_number => X_DAI_SEQUENCE_NUMBER,
956 x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
957 x_course_cd =>X_COURSE_CD,
958 x_version_number=> X_VERSION_NUMBER,
959 x_acad_cal_type => X_ACAD_CAL_TYPE ,
960 x_sequence_number=> X_SEQUENCE_NUMBER,
961 x_location_cd => X_LOCATION_CD ,
962 x_attendance_mode=> X_ATTENDANCE_MODE,
963 x_attendance_type => X_ATTENDANCE_TYPE ,
964 x_rollover_inclusion_ind=> NVL(X_ROLLOVER_INCLUSION_IND,'N'),
965 x_creation_date => X_LAST_UPDATE_DATE,
966 x_created_by => X_LAST_UPDATED_BY,
967 x_last_update_date => X_LAST_UPDATE_DATE,
968 x_last_updated_by => X_LAST_UPDATED_BY,
969 x_last_update_login => X_LAST_UPDATE_LOGIN
970 );
971
972 insert into IGS_AD_PECRS_OFOP_DT (
973 ADM_CAL_TYPE,
974 ADM_CI_SEQUENCE_NUMBER,
975 ADMISSION_CAT,
976 DT_ALIAS,
977 DAI_SEQUENCE_NUMBER,
978 SEQUENCE_NUMBER,
979 S_ADMISSION_PROCESS_TYPE,
980 COURSE_CD,
981 VERSION_NUMBER,
982 ACAD_CAL_TYPE,
983 LOCATION_CD,
984 ATTENDANCE_MODE,
985 ATTENDANCE_TYPE,
986 ROLLOVER_INCLUSION_IND,
987 CREATION_DATE,
988 CREATED_BY,
989 LAST_UPDATE_DATE,
990 LAST_UPDATED_BY,
991 LAST_UPDATE_LOGIN
992 ) values (
993 NEW_REFERENCES.ADM_CAL_TYPE,
994 NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER,
995 NEW_REFERENCES.ADMISSION_CAT,
996 NEW_REFERENCES.DT_ALIAS,
997 NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
998 NEW_REFERENCES.SEQUENCE_NUMBER,
999 NEW_REFERENCES.S_ADMISSION_PROCESS_TYPE,
1000 NEW_REFERENCES.COURSE_CD,
1001 NEW_REFERENCES.VERSION_NUMBER,
1002 NEW_REFERENCES.ACAD_CAL_TYPE,
1003 NEW_REFERENCES.LOCATION_CD,
1004 NEW_REFERENCES.ATTENDANCE_MODE,
1005 NEW_REFERENCES.ATTENDANCE_TYPE,
1006 NEW_REFERENCES.ROLLOVER_INCLUSION_IND,
1007 X_LAST_UPDATE_DATE,
1008 X_LAST_UPDATED_BY,
1009 X_LAST_UPDATE_DATE,
1010 X_LAST_UPDATED_BY,
1011 X_LAST_UPDATE_LOGIN
1012 );
1013
1014 open c;
1015 fetch c into X_ROWID;
1016 if (c%notfound) then
1017 close c;
1018 raise no_data_found;
1019 end if;
1020 close c;
1021 After_DML(
1022 p_action =>'INSERT',
1023 x_rowid => X_ROWID
1024 );
1025
1026 end INSERT_ROW;
1027
1028 procedure LOCK_ROW (
1029 X_ROWID in VARCHAR2,
1030 X_ADM_CAL_TYPE in VARCHAR2,
1031 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1032 X_ADMISSION_CAT in VARCHAR2,
1033 X_DT_ALIAS in VARCHAR2,
1034 X_DAI_SEQUENCE_NUMBER in NUMBER,
1035 X_SEQUENCE_NUMBER in NUMBER,
1036 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
1037 X_COURSE_CD in VARCHAR2,
1038 X_VERSION_NUMBER in NUMBER,
1039 X_ACAD_CAL_TYPE in VARCHAR2,
1040 X_LOCATION_CD in VARCHAR2,
1041 X_ATTENDANCE_MODE in VARCHAR2,
1042 X_ATTENDANCE_TYPE in VARCHAR2,
1043 X_ROLLOVER_INCLUSION_IND in VARCHAR2
1044 ) AS
1045 cursor c1 is select
1046 S_ADMISSION_PROCESS_TYPE,
1047 COURSE_CD,
1048 VERSION_NUMBER,
1049 ACAD_CAL_TYPE,
1050 LOCATION_CD,
1051 ATTENDANCE_MODE,
1052 ATTENDANCE_TYPE,
1053 ROLLOVER_INCLUSION_IND
1054 from IGS_AD_PECRS_OFOP_DT
1055 where ROWID = X_ROWID for update nowait;
1056 tlinfo c1%rowtype;
1057
1058 begin
1059 open c1;
1060 fetch c1 into tlinfo;
1061 if (c1%notfound) then
1062 close c1;
1063 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1064 IGS_GE_MSG_STACK.ADD;
1065 app_exception.raise_exception;
1066 return;
1067 end if;
1068 close c1;
1069
1070 if ( ((tlinfo.S_ADMISSION_PROCESS_TYPE = X_S_ADMISSION_PROCESS_TYPE)
1071 OR ((tlinfo.S_ADMISSION_PROCESS_TYPE is null)
1072 AND (X_S_ADMISSION_PROCESS_TYPE is null)))
1073 AND ((tlinfo.COURSE_CD = X_COURSE_CD)
1074 OR ((tlinfo.COURSE_CD is null)
1075 AND (X_COURSE_CD is null)))
1076 AND ((tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
1077 OR ((tlinfo.VERSION_NUMBER is null)
1078 AND (X_VERSION_NUMBER is null)))
1079 AND ((tlinfo.ACAD_CAL_TYPE = X_ACAD_CAL_TYPE)
1080 OR ((tlinfo.ACAD_CAL_TYPE is null)
1081 AND (X_ACAD_CAL_TYPE is null)))
1082 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
1083 OR ((tlinfo.LOCATION_CD is null)
1084 AND (X_LOCATION_CD is null)))
1085 AND ((tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
1086 OR ((tlinfo.ATTENDANCE_MODE is null)
1087 AND (X_ATTENDANCE_MODE is null)))
1088 AND ((tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
1089 OR ((tlinfo.ATTENDANCE_TYPE is null)
1090 AND (X_ATTENDANCE_TYPE is null)))
1091 AND (tlinfo.ROLLOVER_INCLUSION_IND = X_ROLLOVER_INCLUSION_IND)
1092 ) then
1093 null;
1094 else
1095 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1096 IGS_GE_MSG_STACK.ADD;
1097 app_exception.raise_exception;
1098 end if;
1099 return;
1100 end LOCK_ROW;
1101
1102 procedure UPDATE_ROW (
1103 X_ROWID in VARCHAR2,
1104 X_ADM_CAL_TYPE in VARCHAR2,
1105 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1106 X_ADMISSION_CAT in VARCHAR2,
1107 X_DT_ALIAS in VARCHAR2,
1108 X_DAI_SEQUENCE_NUMBER in NUMBER,
1109 X_SEQUENCE_NUMBER in NUMBER,
1110 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
1111 X_COURSE_CD in VARCHAR2,
1112 X_VERSION_NUMBER in NUMBER,
1113 X_ACAD_CAL_TYPE in VARCHAR2,
1114 X_LOCATION_CD in VARCHAR2,
1115 X_ATTENDANCE_MODE in VARCHAR2,
1116 X_ATTENDANCE_TYPE in VARCHAR2,
1117 X_ROLLOVER_INCLUSION_IND in VARCHAR2,
1118 X_MODE in VARCHAR2
1119 ) AS
1120 X_LAST_UPDATE_DATE DATE;
1121 X_LAST_UPDATED_BY NUMBER;
1122 X_LAST_UPDATE_LOGIN NUMBER;
1123 begin
1124 X_LAST_UPDATE_DATE := SYSDATE;
1125 if(X_MODE = 'I') then
1126 X_LAST_UPDATED_BY := 1;
1127 X_LAST_UPDATE_LOGIN := 0;
1128 elsif (X_MODE = 'R') then
1129 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1130 if X_LAST_UPDATED_BY is NULL then
1131 X_LAST_UPDATED_BY := -1;
1132 end if;
1133 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1134 if X_LAST_UPDATE_LOGIN is NULL then
1135 X_LAST_UPDATE_LOGIN := -1;
1136 end if;
1137 else
1138 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1139 IGS_GE_MSG_STACK.ADD;
1140 app_exception.raise_exception;
1141 end if;
1142
1143 Before_DML(p_action =>'UPDATE',
1144 x_rowid =>X_ROWID,
1145 x_adm_cal_type => X_ADM_CAL_TYPE,
1146 x_adm_ci_sequence_number => X_ADM_CI_SEQUENCE_NUMBER,
1147 x_admission_cat => X_ADMISSION_CAT,
1148 x_dt_alias => X_DT_ALIAS,
1149 x_dai_sequence_number => X_DAI_SEQUENCE_NUMBER,
1150 x_sequence_number => X_SEQUENCE_NUMBER,
1151 x_s_admission_process_type => X_S_ADMISSION_PROCESS_TYPE,
1152 x_course_cd =>X_COURSE_CD ,
1153 x_version_number=> X_VERSION_NUMBER,
1154 x_acad_cal_type => X_ACAD_CAL_TYPE ,
1155 x_location_cd => X_LOCATION_CD ,
1156 x_attendance_mode=> X_ATTENDANCE_MODE,
1157 x_attendance_type => X_ATTENDANCE_TYPE ,
1158 x_rollover_inclusion_ind=> X_ROLLOVER_INCLUSION_IND,
1159 x_creation_date => X_LAST_UPDATE_DATE,
1160 x_created_by => X_LAST_UPDATED_BY,
1161 x_last_update_date => X_LAST_UPDATE_DATE,
1162 x_last_updated_by => X_LAST_UPDATED_BY,
1163 x_last_update_login => X_LAST_UPDATE_LOGIN
1164 );
1165
1166 update IGS_AD_PECRS_OFOP_DT set
1167 S_ADMISSION_PROCESS_TYPE = NEW_REFERENCES.S_ADMISSION_PROCESS_TYPE,
1168 COURSE_CD = NEW_REFERENCES.COURSE_CD,
1169 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
1170 DAI_SEQUENCE_NUMBER = NEW_REFERENCES.DAI_SEQUENCE_NUMBER,
1171 ACAD_CAL_TYPE = NEW_REFERENCES.ACAD_CAL_TYPE,
1172 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
1173 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
1174 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
1175 ROLLOVER_INCLUSION_IND = NEW_REFERENCES.ROLLOVER_INCLUSION_IND,
1176 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1177 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1178 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1179 where ROWID = X_ROWID
1180 ;
1181 if (sql%notfound) then
1182 raise no_data_found;
1183 end if;
1184 After_DML(
1185 p_action =>'UPDATE',
1186 x_rowid => X_ROWID
1187 );
1188 end UPDATE_ROW;
1189
1190 procedure ADD_ROW (
1191 X_ROWID in out NOCOPY VARCHAR2,
1192 X_ADM_CAL_TYPE in VARCHAR2,
1193 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1194 X_ADMISSION_CAT in VARCHAR2,
1195 X_DT_ALIAS in VARCHAR2,
1196 X_DAI_SEQUENCE_NUMBER in NUMBER,
1197 X_SEQUENCE_NUMBER in NUMBER,
1198 X_S_ADMISSION_PROCESS_TYPE in VARCHAR2,
1199 X_COURSE_CD in VARCHAR2,
1200 X_VERSION_NUMBER in NUMBER,
1201 X_ACAD_CAL_TYPE in VARCHAR2,
1202 X_LOCATION_CD in VARCHAR2,
1203 X_ATTENDANCE_MODE in VARCHAR2,
1204 X_ATTENDANCE_TYPE in VARCHAR2,
1205 X_ROLLOVER_INCLUSION_IND in VARCHAR2,
1206 X_MODE in VARCHAR2
1207 ) AS
1208 cursor c1 is select rowid from IGS_AD_PECRS_OFOP_DT
1209 where ADM_CAL_TYPE = X_ADM_CAL_TYPE
1210 and ADM_CI_SEQUENCE_NUMBER = X_ADM_CI_SEQUENCE_NUMBER
1211 and ADMISSION_CAT = X_ADMISSION_CAT
1212 and DT_ALIAS = X_DT_ALIAS
1213 and DAI_SEQUENCE_NUMBER = X_DAI_SEQUENCE_NUMBER
1214 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1215 ;
1216 begin
1217 open c1;
1218 fetch c1 into X_ROWID;
1219 if (c1%notfound) then
1220 close c1;
1221 INSERT_ROW (
1222 X_ROWID,
1223 X_ADM_CAL_TYPE,
1224 X_ADM_CI_SEQUENCE_NUMBER,
1225 X_ADMISSION_CAT,
1226 X_DT_ALIAS,
1227 X_DAI_SEQUENCE_NUMBER,
1228 X_SEQUENCE_NUMBER,
1229 X_S_ADMISSION_PROCESS_TYPE,
1230 X_COURSE_CD,
1231 X_VERSION_NUMBER,
1232 X_ACAD_CAL_TYPE,
1233 X_LOCATION_CD,
1234 X_ATTENDANCE_MODE,
1235 X_ATTENDANCE_TYPE,
1236 X_ROLLOVER_INCLUSION_IND,
1237 X_MODE);
1238 return;
1239 end if;
1240 close c1;
1241 UPDATE_ROW (
1242 X_ROWID,
1243 X_ADM_CAL_TYPE,
1244 X_ADM_CI_SEQUENCE_NUMBER,
1245 X_ADMISSION_CAT,
1246 X_DT_ALIAS,
1247 X_DAI_SEQUENCE_NUMBER,
1248 X_SEQUENCE_NUMBER,
1249 X_S_ADMISSION_PROCESS_TYPE,
1250 X_COURSE_CD,
1251 X_VERSION_NUMBER,
1252 X_ACAD_CAL_TYPE,
1253 X_LOCATION_CD,
1254 X_ATTENDANCE_MODE,
1255 X_ATTENDANCE_TYPE,
1256 X_ROLLOVER_INCLUSION_IND,
1257 X_MODE);
1258 end ADD_ROW;
1259
1260 procedure DELETE_ROW (
1261 X_ROWID in VARCHAR2
1262 ) AS
1263 begin
1264 Before_DML(
1265 p_action =>'DELETE',
1266 x_rowid => X_ROWID
1267 );
1268 delete from IGS_AD_PECRS_OFOP_DT
1269 where ROWID = X_ROWID;
1270 if (sql%notfound) then
1271 raise no_data_found;
1272 end if;
1273 After_DML(
1274 p_action =>'DELETE',
1275 x_rowid => X_ROWID
1276 );
1277 end DELETE_ROW;
1278
1279 end IGS_AD_PECRS_OFOP_DT_PKG;