[Home] [Help]
PACKAGE BODY: APPS.IGS_CA_INS_ROLL_CI
Source
1 PACKAGE BODY IGS_CA_INS_ROLL_CI AS
2 /* $Header: IGSCA03B.pls 120.1 2005/08/04 06:37:03 appldev ship $ */
3
4 /***** Bug No : 1956374
5 Task : Duplicated Procedures and functions
6 PROCEDURE admp_val_apcood_da is removed and reference is changed *****/
7 /***********************************************************************
8 --Enh Bug :- 2138560,Change Request for Calendar Instance
9 --Description to be added in IGS_CA_INST_ALL Table
10
11 Who When What
12 mesriniv 06-DEC-2001 A DESCRIPTION Column is ADDED in the
13 Insert Row Procedure
14 of the package call IGS_CA_INST_PKG
15 ssawhney 28dec GSCC standards change name of
16 REL_SUP_CI_SEQ_NUM sequence to end in S1
17 npalanis 23-NOV-2002 Bug : 2563531
18 For calendars load , teaching and academic the alternate
19 code is made unique . In rollover the alternate code of the
20 previous calendar and the rolled over calendar was retained same
21 now the alternate code is appended with sequence number
22 (igs_ca_inst_seq_num_s1) to make it unique
23 asbala 9-Feb-2004 ENCR039: New column TERM_INSTRUCTION_TIME of table IGS_CA_INST will also be rolled
24 over when the calendar is rolled over.
25 nsidnaa 9/21/2004 New function added to rollover retention schedules for a teaching calendar.
26 skpandey 4-AUG-2005 BUG : 4356272
27 Added parameteric values for SS_DISPLAYED, ADMIN_FLAG, PLANNING_FLAG, SCHEDULE_FLAG in IGS_CA_INST_PKG.INSERT_ROW procedure to
28 facilitate the adding of new checkboxes to the IGS_CA_INST_ALL table for enabling load calendars for self-service
29 **************************************************************************/
30
31 l_message_name VARCHAR2(30);
32 l_app VARCHAR2(50);
33 l_msg_txt varchar2(200);
34 l_prog_label VARCHAR2(100);
35 l_label VARCHAR2(100);
36 l_debug_str VARCHAR2(2000);
37 l_func_name VARCHAR2(80);
38 l_roll_seq NUMBER;
39
40
41
42 X_ROWID VARCHAR2(25);
43
44 FUNCTION chk_and_roll_ret(p_old_ca_type IN VARCHAR2,
45 p_old_ci_seq_num IN NUMBER,
46 p_old_da_alias IN VARCHAR2,
47 p_old_dai_seq_num IN NUMBER,
48 p_new_ci_seq_num IN NUMBER)
49 RETURN BOOLEAN
50 /***********************************************************************
51 Created by : nsidana
52 Created on : 9/21/2004
53 Change History:
54 Who When What
55 nsidnaa 9/21/2004 New function added to rollover retention
56 schedules for a teaching calendar.
57 **************************************************************************/
58
59 AS
60 CURSOR chk_da_used_ret(cp_cal_type VARCHAR2,cp_seq_num NUMBER,cp_dt_alias VARCHAR2,cp_dt_alias_seq_num NUMBER)
61 IS
62 SELECT tpret.ret_percentage, tpret.ret_amount
63 FROM IGS_FI_TP_RET_SCHD tpret
64 WHERE teach_cal_type = cp_cal_type AND
65 teach_ci_sequence_number = cp_seq_num AND
66 dt_alias = cp_dt_alias AND
67 dai_sequence_number = cp_dt_alias_seq_num AND
68 fee_cal_type IS NULL;
69
70 chk_da_used_ret_rec chk_da_used_ret%ROWTYPE;
71 lv_rowid ROWID;
72 l_ftci_teach_retention_id NUMBER;
73
74 BEGIN
75 -- Check if the date alias is used in any retention for the calendar being rolled over.
76
77 OPEN chk_da_used_ret(p_old_ca_type,p_old_ci_seq_num,p_old_da_alias,p_old_dai_seq_num);
78 FETCH chk_da_used_ret INTO chk_da_used_ret_rec;
79
80 IF (chk_da_used_ret%FOUND)
81 THEN
82 CLOSE chk_da_used_ret;
83 -- Call TBH to insert a record for the retention schedule for the new calendar instance.
84 IGS_FI_TP_RET_SCHD_PKG.insert_row(x_rowid => lv_rowid,
85 x_ftci_teach_retention_id => l_ftci_teach_retention_id,
86 x_teach_cal_type => p_old_ca_type,
87 x_teach_ci_sequence_number => p_new_ci_seq_num,
88 x_fee_cal_type => null,
89 x_fee_ci_sequence_number => null,
90 x_fee_type => null,
91 x_dt_alias => p_old_da_alias,
92 x_dai_sequence_number => p_old_dai_seq_num,
93 x_ret_percentage => chk_da_used_ret_rec.ret_percentage,
94 x_ret_amount => chk_da_used_ret_rec.ret_amount,
95 x_mode => 'R'
96 );
97 RETURN TRUE;
98
99 ELSE
100
101 -- date alias not used in the retention.
102 IF (chk_da_used_ret%ISOPEN)
103 THEN
104 CLOSE chk_da_used_ret;
105 END IF;
106
107 RETURN FALSE;
108
109 END IF;
110
111 EXCEPTION
112 WHEN OTHERS THEN
113 IF l_func_name IS NULL THEN
114 l_func_name := 'chk_and_roll_ret';
115 END IF;
116 App_Exception.Raise_Exception;
117 END chk_and_roll_ret;
118
119 -- To insert a date alias instance pair as part of the rollover process
120 FUNCTION calp_ins_rollvr_daip(
121 p_dt_alias IN VARCHAR2 ,
122 p_dai_sequence_number IN NUMBER ,
123 p_cal_type IN VARCHAR2 ,
124 p_ci_sequence_number IN NUMBER ,
125 p_diff_days IN NUMBER ,
126 p_diff_months IN NUMBER ,
127 p_val_dt_alias IN VARCHAR2 ,
128 p_val_dai_sequence_number IN NUMBER ,
129 p_val_cal_type IN VARCHAR2 ,
130 p_val_ci_sequence_number IN NUMBER ,
131 p_daip_related IN boolean ,
132 p_ci_rollover_sequence_number IN NUMBER ,
133 p_message_name OUT NOCOPY varchar2 )
134 RETURN boolean AS
135 lv_param_values VARCHAR2(1080);
136 BEGIN
137 DECLARE
138 X_ROWID VARCHAR2(25);
139
140 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
141 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
142 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
143 v_other_detail VARCHAR2(255);
144 token1_val VARCHAR2(255);
145 token2_val VARCHAR2(255);
146 v_cntr NUMBER;
147 v_dai_found BOOLEAN;
148 v_ci_found BOOLEAN;
149 v_related_dt_alias IGS_CA_DA_INST_PAIR.related_dt_alias%TYPE;
150 v_dt_alias IGS_CA_DA_INST_PAIR.DT_ALIAS%TYPE;
151 v_derived_cal_status IGS_CA_STAT.s_cal_status%TYPE;
152 v_val_start_dt IGS_CA_INST.start_dt%TYPE;
153 v_val_end_dt IGS_CA_INST.end_dt%TYPE;
154 v_dairn_start_dt IGS_CA_INST.start_dt%TYPE;
155 v_dairn_end_dt IGS_CA_INST.end_dt%TYPE;
156 v_new_dt_alias IGS_CA_DA_INST.DT_ALIAS%TYPE;
157 v_new_dai_sequence_number IGS_CA_DA_INST.sequence_number%TYPE;
158 v_new_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE;
159 v_new_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE;
160 v_ins_dt_alias IGS_CA_DA_INST.DT_ALIAS%TYPE;
161 v_ins_dai_sequence_number IGS_CA_DA_INST.sequence_number%TYPE;
162 v_ins_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE;
163 v_ins_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE;
164 v_ins_related_dt_alias IGS_CA_DA_INST.DT_ALIAS%TYPE;
165 v_ins_related_dai_seq_num IGS_CA_DA_INST.sequence_number%TYPE;
166 v_ins_related_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE;
167 v_ins_related_ci_seq_num IGS_CA_DA_INST.ci_sequence_number%TYPE;
168
169 CURSOR c_new_dt_alias_instance(
170 cp_dt_alias IGS_CA_DA_INST_V.DT_ALIAS%TYPE,
171 cp_cal_type IGS_CA_DA_INST_V.CAL_TYPE%TYPE,
172 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE) IS
173 SELECT *
174 FROM IGS_CA_DA_INST
175 WHERE DT_ALIAS = cp_dt_alias AND
176 CAL_TYPE = cp_cal_type AND
177 ci_sequence_number = cp_ci_sequence_number;
178 CURSOR c_cal_instance(
179 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
180 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
181 SELECT *
182 FROM IGS_CA_INST
183 WHERE CAL_TYPE = cp_cal_type AND
184 sequence_number = cp_sequence_number;
185 CURSOR c_derived_cal_instance(
186 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
187 cp_start_dt IGS_CA_INST.start_dt%TYPE,
188 cp_end_dt IGS_CA_INST.end_dt%TYPE) IS
189 SELECT *
190 FROM IGS_CA_INST
191 WHERE CAL_TYPE = cp_cal_type AND
192 start_dt = cp_start_dt AND
193 end_dt = cp_end_dt;
194 CURSOR c_derived_cal_status(
195 cp_cal_status IGS_CA_STAT.CAL_STATUS%TYPE) IS
196 SELECT *
197 FROM IGS_CA_STAT
198 WHERE CAL_STATUS = cp_cal_status;
199 CURSOR c_s_log_entry_dai(
200 cp_s_log_type IGS_GE_S_LOG_ENTRY.s_log_type%TYPE,
201 cp_creation_dt IGS_GE_S_LOG_ENTRY.creation_dt%TYPE,
202 cp_key IGS_GE_S_LOG_ENTRY.key%TYPE,
203 cp_text IGS_GE_S_LOG_ENTRY.text%TYPE) IS
204 SELECT *
205 FROM IGS_GE_S_LOG_ENTRY
206 WHERE s_log_type = cp_s_log_type
207 AND creation_dt = cp_creation_dt
208 AND key = cp_key
209 AND text = cp_text;
210 v_sled_rec IGS_GE_S_LOG_ENTRY%ROWTYPE;
211 CURSOR IGS_GE_S_LOG_ENTRY_CUR is
212 SELECT ROWID
213 FROM IGS_GE_S_LOG_ENTRY
214 WHERE s_log_type = gv_log_type
215 AND creation_dt = gv_log_creation_dt
216 AND key = gv_log_key
217 AND text = v_other_detail;
218 BEGIN
219
220 l_prog_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip';
221 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip.start';
222
223 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
224 l_debug_str := 'calp_ins_rollvr_daip : Starting';
225 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,l_roll_seq);
226 END IF;
227
228 -- Determine calendar instance match by adding rollover days difference
229 -- to start and end dates.
230 FOR v_cal_instance_rec IN c_cal_instance(
231 p_val_cal_type,
232 p_val_ci_sequence_number) LOOP
233 IF p_diff_days = 0 THEN
234 v_val_start_dt := add_months(v_cal_instance_rec.start_dt,p_diff_months);
235 v_val_end_dt := add_months(v_cal_instance_rec.end_dt,p_diff_months);
236 ELSE
237 v_val_start_dt := v_cal_instance_rec.start_dt + p_diff_days;
238 v_val_end_dt := v_cal_instance_rec.end_dt + p_diff_days;
239 END IF;
240 END LOOP;
241 v_ci_found := FALSE;
242 v_dai_found := FALSE;
243 -- Determine if the date alias instance pair calendar instance exists,
244 -- is active, and a date alias match exists.
245 FOR v_derived_cal_instance_rec IN c_derived_cal_instance(
246 p_val_cal_type,
247 v_val_start_dt,
248 v_val_end_dt) LOOP
249 v_ci_found := TRUE;
250 -- Obtain system calendar status of derived calendar instance.
251 FOR v_derived_cal_status_rec IN c_derived_cal_status(
252 v_derived_cal_instance_rec.CAL_STATUS) LOOP
253 v_derived_cal_status := v_derived_cal_status_rec.s_cal_status;
254 END LOOP;
255 v_dai_found := FALSE;
256 v_cntr := 0;
257 IF(v_derived_cal_status <> cst_inactive) THEN
258 FOR v_new_dt_alias_instance_rec IN c_new_dt_alias_instance(
259 p_val_dt_alias,
260 v_derived_cal_instance_rec.CAL_TYPE,
261 v_derived_cal_instance_rec.sequence_number) LOOP
262 IF v_new_dt_alias_instance_rec.CAL_TYPE = p_cal_type AND
263 v_new_dt_alias_instance_rec.ci_sequence_number = p_ci_sequence_number
264 THEN
265 IF ( v_new_dt_alias_instance_rec.sequence_number = p_val_dai_sequence_number)
266 THEN
267 v_cntr := 1;
268 v_dai_found := TRUE;
269 v_new_dt_alias := v_new_dt_alias_instance_rec.DT_ALIAS;
270 v_new_dai_sequence_number := v_new_dt_alias_instance_rec.sequence_number;
271 v_new_cal_type := v_new_dt_alias_instance_rec.CAL_TYPE;
272 v_new_ci_sequence_number := v_new_dt_alias_instance_rec.ci_sequence_number;
273 EXIT;
274 END IF;
275 ELSE
276 v_cntr := v_cntr + 1;
277 v_dai_found := TRUE;
278 v_new_dt_alias := v_new_dt_alias_instance_rec.DT_ALIAS;
279 v_new_dai_sequence_number := v_new_dt_alias_instance_rec.sequence_number;
280 v_new_cal_type := v_new_dt_alias_instance_rec.CAL_TYPE;
281 v_new_ci_sequence_number := v_new_dt_alias_instance_rec.ci_sequence_number;
282 END IF;
283 END LOOP;
284 END IF;
285 END LOOP;
286 IF(v_ci_found = FALSE) THEN
287
288 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_dt_alias||
289 '|' || p_dai_sequence_number||'|' ;
290
291 token2_val := p_val_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||'-'||
292 IGS_GE_DATE.IGSCHAR(v_val_end_dt);
293
294 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_REL_CAL_NOT_EXIST');
295 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
296 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
297 FND_MESSAGE.SET_TOKEN('TOKEN3',p_val_dt_alias);
298 v_other_detail:=FND_MESSAGE.GET;
299
300 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
301
302 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip.no_ci';
303 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daip Log Message: ' || v_other_detail;
304 fnd_log.string_with_context( fnd_log.level_procedure,
305 l_label,
306 l_debug_str, NULL,
307 NULL,NULL,NULL,NULL,l_roll_seq);
308 END IF;
309
310 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
311 gv_log_type,
312 gv_log_creation_dt,
313 gv_log_key,
314 NULL,
315 v_other_detail);
316
317
318 p_message_name := null;
319 RETURN FALSE;
320 ELSIF (v_derived_cal_status = cst_inactive) THEN
321
322
323 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_dt_alias||
324 '|' || p_dai_sequence_number||'|' ;
325
326 token2_val := p_val_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||'-'||
327 IGS_GE_DATE.IGSCHAR(v_val_end_dt);
328
329 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_REL_CAL_INACTIVE');
330 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
331 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
332 FND_MESSAGE.SET_TOKEN('TOKEN3',p_val_dt_alias);
333 v_other_detail:=FND_MESSAGE.GET;
334
335 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
336
337 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip.inactive_ci';
338 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daip Log Message: ' || v_other_detail;
339 fnd_log.string_with_context( fnd_log.level_procedure,
340 l_label,
341 l_debug_str, NULL,
342 NULL,NULL,NULL,NULL,l_roll_seq);
343 END IF;
344
345 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
346 gv_log_type,
347 gv_log_creation_dt,
348 gv_log_key,
349 NULL,
350 v_other_detail);
351
352
353 p_message_name :=null;
354 RETURN FALSE;
355 ELSIF (v_dai_found = FALSE) THEN
356
357
358
359 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_dt_alias||
360 '|' || p_dai_sequence_number||'|';
361
362 token2_val := p_val_dt_alias||'('||p_val_dai_sequence_number||')'||
363 ' '||p_val_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||
364 '-'||IGS_GE_DATE.IGSCHAR(v_val_end_dt);
365
366
367 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_NO_REL_DTALIAS_EXIST');
368 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
369 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
370 v_other_detail:=FND_MESSAGE.GET;
371
372
373 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
374
375 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip.no_dai';
376 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daip Log Message: ' || v_other_detail;
377 fnd_log.string_with_context( fnd_log.level_procedure,
378 l_label,
379 l_debug_str, NULL,
380 NULL,NULL,NULL,NULL,l_roll_seq);
381 END IF;
382
383 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
384 gv_log_type,
385 gv_log_creation_dt,
386 gv_log_key,
387 NULL,
388 v_other_detail);
389
390
391
392 p_message_name := null;
393 RETURN FALSE;
394 ELSIF (v_cntr > 1) THEN
395
396 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_dt_alias||
397 '|' || p_dai_sequence_number||'|' ;
398
399 token2_val := p_val_dt_alias||' '||p_val_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||
400 '-'||IGS_GE_DATE.IGSCHAR(v_val_end_dt)||'.';
401
402 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_NON_UNIQUE_REL_DTALIAS');
403 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
404 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
405 v_other_detail:=FND_MESSAGE.GET;
406
407 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
408
409 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip.multi_dai';
410 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daip Log Message: ' || v_other_detail;
411 fnd_log.string_with_context( fnd_log.level_procedure,
412 l_label,
413 l_debug_str, NULL,
414 NULL,NULL,NULL,NULL,l_roll_seq);
415 END IF;
416
417 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
418 gv_log_type,
419 gv_log_creation_dt,
420 gv_log_key,
421 NULL,
422 v_other_detail);
423
424
425
426 p_message_name := null;
427 RETURN FALSE;
428 END IF;
429 IF(p_daip_related) THEN
430 v_ins_dt_alias := p_dt_alias;
431 v_ins_dai_sequence_number := p_dai_sequence_number;
432 v_ins_cal_type := p_cal_type;
433 v_ins_ci_sequence_number := p_ci_sequence_number;
434 v_ins_related_dt_alias := v_new_dt_alias;
435 v_ins_related_dai_seq_num := v_new_dai_sequence_number;
436 v_ins_related_cal_type := v_new_cal_type;
437 v_ins_related_ci_seq_num := v_new_ci_sequence_number;
438 ELSE
439 v_ins_dt_alias := v_new_dt_alias;
440 v_ins_dai_sequence_number := v_new_dai_sequence_number;
441 v_ins_cal_type := v_new_cal_type;
442 v_ins_ci_sequence_number := v_new_ci_sequence_number;
443 v_ins_related_dt_alias := p_dt_alias;
444 v_ins_related_dai_seq_num := p_dai_sequence_number;
445 v_ins_related_cal_type := p_cal_type;
446 v_ins_related_ci_seq_num := p_ci_sequence_number;
447 END IF;
448
449 IGS_CA_DA_INST_PAIR_PKG.INSERT_ROW(
450 X_ROWID => X_ROWID,
451 X_DT_ALIAS=> v_ins_dt_alias,
452 X_dai_sequence_number => v_ins_dai_sequence_number,
453 X_CAL_TYPE =>v_ins_cal_type,
454 X_ci_sequence_number =>v_ins_ci_sequence_number,
455 X_related_dt_alias =>v_ins_related_dt_alias,
456 X_related_dai_sequence_number => v_ins_related_dai_seq_num,
457 X_related_cal_type => v_ins_related_cal_type,
458 X_related_ci_sequence_number => v_ins_related_ci_seq_num,
459 X_MODE => 'R');
460 -- Delete pair IGS_CA_DA discrepancy notes attached to pair
461 -- Firstly determine start and end dates of calendar
462 FOR v_cal_instance_rec IN c_cal_instance(
463 p_cal_type,
464 p_ci_sequence_number) LOOP
465 v_dairn_start_dt := v_cal_instance_rec.start_dt;
466 v_dairn_end_dt := v_cal_instance_rec.end_dt;
467 END LOOP;
468 -- Delete IGS_GE_S_LOG_ENTRY for DAIP if it exists.
469
470
471 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_val_dt_alias||
472 '|' || p_val_dai_sequence_number||'|';
473
474 token2_val := p_dt_alias||'('||p_dai_sequence_number||')'||
475 ' '||p_val_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||
476 '-'||IGS_GE_DATE.IGSCHAR(v_val_end_dt);
477
478
479 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_NO_REL_DTALIAS_EXIST');
480 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
481 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
482 v_other_detail:=FND_MESSAGE.GET;
483
484 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
485
486 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip.multi_dai';
487 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daip Log Message: ' || v_other_detail;
488 fnd_log.string_with_context( fnd_log.level_procedure,
489 l_label,
490 l_debug_str, NULL,
491 NULL,NULL,NULL,NULL,l_roll_seq);
492 END IF;
493
494 OPEN c_s_log_entry_dai(
495 gv_log_type,
496 gv_log_creation_dt,
497 gv_log_key,
498 v_other_detail);
499 FETCH c_s_log_entry_dai INTO v_sled_rec;
500 IF c_s_log_entry_dai%NOTFOUND THEN
501 CLOSE c_s_log_entry_dai;
502
503
504 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_val_dt_alias||
505 '|' || p_val_dai_sequence_number||'|' ;
506
507 token2_val := p_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_dairn_start_dt)||
508 '-'||IGS_GE_DATE.IGSCHAR(v_dairn_end_dt);
509
510 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_REL_CAL_NOT_EXIST');
511 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
512 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
513 FND_MESSAGE.SET_TOKEN('TOKEN3',p_val_dt_alias);
514 v_other_detail:=FND_MESSAGE.GET;
515
516 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
517
518 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip.multi_dai';
519 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daip Log Message: ' || v_other_detail;
520 fnd_log.string_with_context( fnd_log.level_procedure,
521 l_label,
522 l_debug_str, NULL,
523 NULL,NULL,NULL,NULL,l_roll_seq);
524 END IF;
525
526 OPEN c_s_log_entry_dai(
527 gv_log_type,
528 gv_log_creation_dt,
529 gv_log_key,
530 v_other_detail);
531 FETCH c_s_log_entry_dai INTO v_sled_rec;
532 IF c_s_log_entry_dai%NOTFOUND THEN
533 CLOSE c_s_log_entry_dai;
534 ELSE
535 CLOSE c_s_log_entry_dai;
536
537
538 for v_IGS_GE_S_LOG_ENTRY_CUR in IGS_GE_S_LOG_ENTRY_CUR loop
539 IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_IGS_GE_S_LOG_ENTRY_CUR.ROWID);
540 end loop;
541
542
543 END IF;
544 ELSE
545 CLOSE c_s_log_entry_dai;
546
547
548 for v_IGS_GE_S_LOG_ENTRY_CUR in IGS_GE_S_LOG_ENTRY_CUR loop
549 IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_IGS_GE_S_LOG_ENTRY_CUR.ROWID);
550 end loop;
551
552
553 END IF;
554 p_message_name := null;
555 RETURN TRUE;
556 EXCEPTION
557 WHEN OTHERS THEN
558 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
559 l_debug_str := 'DA ='||p_dt_alias||'Seq num ='||(to_char(p_dai_sequence_number))||
560 'Cal Type ='||p_cal_type||'CI Seq num ='||(to_char(p_ci_sequence_number))||
561 'Diff Days ='||(to_char(p_diff_days))||'Diff Months ='||(to_char(p_diff_months))||
562 'DAI val ='||p_val_dt_alias||'DAI seq num'||(to_char(p_val_dai_sequence_number))||
563 'Validating Cal Type ='||p_val_cal_type||'Val CI seq num ='||(to_char(p_val_ci_sequence_number ))||
564 'Rollover CI seq num ='||(to_char(p_ci_rollover_sequence_number));
565 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
566 END IF;
567 IF l_func_name IS NULL THEN
568 l_func_name := 'calp_ins_rollvr_daip';
569 END IF;
570 App_Exception.Raise_Exception;
571 END;
572 END calp_ins_rollvr_daip;
573 --
574 -- To insert a dt alias inst offset constraint as part of the rollover.
575 FUNCTION calp_ins_roll_daioc(
576 p_dt_alias IN VARCHAR2 ,
577 p_dai_sequence_number IN NUMBER ,
578 p_cal_type IN VARCHAR2 ,
579 p_ci_sequence_number IN NUMBER ,
580 p_offset_dt_alias IN VARCHAR2 ,
581 p_offset_dai_sequence_number IN NUMBER ,
582 p_offset_cal_type IN VARCHAR2 ,
583 p_offset_ci_sequence_number IN NUMBER ,
584 p_new_dt_alias IN VARCHAR2 ,
585 p_new_dai_sequence_number IN NUMBER ,
586 p_new_cal_type IN VARCHAR2 ,
587 p_new_ci_sequence_number IN NUMBER ,
588 p_new_offset_dt_alias IN VARCHAR2 ,
589 p_new_offset_dai_seq_number IN NUMBER ,
590 p_new_offset_cal_type IN VARCHAR2 ,
591 p_new_offset_ci_seq_number IN NUMBER ,
592 p_ci_rollover_sequence_number IN NUMBER ,
593 p_message_name OUT NOCOPY varchar2 )
594 RETURN boolean AS
595 BEGIN
596 DECLARE
597 X_ROWID VARCHAR2(25);
598
599 v_other_detail VARCHAR2(255);
600 v_ins_dt_alias IGS_CA_DA_INST.DT_ALIAS%TYPE;
601 v_ins_dai_sequence_number IGS_CA_DA_INST.sequence_number%TYPE;
602 v_ins_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE;
603 v_ins_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE;
604 v_ins_offset_dt_alias IGS_CA_DA_INST.DT_ALIAS%TYPE;
605 v_ins_offset_dai_seq_num IGS_CA_DA_INST.sequence_number%TYPE;
606 v_ins_offset_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE;
607 v_ins_offset_ci_seq_num IGS_CA_DA_INST.ci_sequence_number%TYPE;
608 CURSOR c_daio_cnstrt(
609 cp_dt_alias IGS_CA_DA_INST_OFCNT.DT_ALIAS%TYPE,
610 cp_dai_sequence_number IGS_CA_DA_INST_OFCNT.dai_sequence_number%TYPE,
611 cp_cal_type IGS_CA_DA_INST_OFCNT.CAL_TYPE%TYPE,
612 cp_ci_sequence_number IGS_CA_DA_INST_OFCNT.ci_sequence_number%TYPE,
613 cp_offset_dt_alias IGS_CA_DA_INST_OFCNT.offset_dt_alias%TYPE,
614 cp_offset_dai_sequence_number
615 IGS_CA_DA_INST_OFCNT.offset_dai_sequence_number%TYPE,
616 cp_offset_cal_type IGS_CA_DA_INST_OFCNT.offset_cal_type%TYPE,
617 cp_offset_ci_sequence_number
618 IGS_CA_DA_INST_OFCNT.offset_ci_sequence_number%TYPE) IS
619 SELECT *
620 FROM IGS_CA_DA_INST_OFCNT
621 WHERE DT_ALIAS = cp_dt_alias AND
622 dai_sequence_number = cp_dai_sequence_number AND
623 CAL_TYPE = cp_cal_type AND
624 ci_sequence_number = cp_ci_sequence_number AND
625 offset_dt_alias = cp_offset_dt_alias AND
626 offset_dai_sequence_number = cp_offset_dai_sequence_number AND
627 offset_cal_type = cp_offset_cal_type AND
628 offset_ci_sequence_number = cp_offset_ci_sequence_number;
629 BEGIN
630 -- Determine existing date alias instance offset constraints and
631 -- create new records based on the rolled DAIO details.
632 FOR v_daio_cnstrt_rec IN c_daio_cnstrt(
633 p_dt_alias,
634 p_dai_sequence_number,
635 p_cal_type,
636 p_ci_sequence_number,
637 p_offset_dt_alias,
638 p_offset_dai_sequence_number,
639 p_offset_cal_type,
640 p_offset_ci_sequence_number) LOOP
641
642 IGS_CA_DA_INST_OFCNT_PKG.INSERT_ROW(
643 X_ROWID => X_ROWID,
644 X_DT_ALIAS => p_new_dt_alias,
645 X_dai_sequence_number => p_new_dai_sequence_number,
646 X_CAL_TYPE => p_new_cal_type,
647 X_ci_sequence_number => p_new_ci_sequence_number,
648 X_offset_dt_alias => p_new_offset_dt_alias,
649 X_offset_dai_sequence_number => p_new_offset_dai_seq_number,
650 X_offset_cal_type => p_new_offset_cal_type,
651 X_offset_ci_sequence_number => p_new_offset_ci_seq_number,
652 X_S_DT_OFFSET_CONSTRAINT_TYPE => v_daio_cnstrt_rec.S_DT_OFFSET_CONSTRAINT_TYPE,
653 X_constraint_condition => v_daio_cnstrt_rec.constraint_condition,
654 X_constraint_resolution => v_daio_cnstrt_rec.constraint_resolution,
655 X_MODE => 'R');
656
657
658 END LOOP;
659 p_message_name := null;
660 RETURN TRUE;
661 EXCEPTION
662 WHEN OTHERS THEN
663 IF l_func_name IS NULL THEN
664 l_func_name := 'calp_ins_roll_daioc';
665 END IF;
666 App_Exception.Raise_Exception;
667 END;
668 END calp_ins_roll_daioc;
669 --
670 -- Validate the adm period IGS_PS_COURSE off option date date alias
671 --
672 -- Validate adm perd date override should be included in rollover.
673 FUNCTION calp_val_apcood_roll(
674 p_dt_alias IN VARCHAR2 ,
675 p_dai_sequence_number IN NUMBER ,
676 p_cal_type IN VARCHAR2 ,
677 p_ci_sequence_number IN NUMBER ,
678 p_message_name OUT NOCOPY varchar2 )
679 RETURN BOOLEAN AS
680 gv_other_detail VARCHAR2(255);
681 BEGIN --calp_val_apcood_roll
682 --This module validates if a date alias instance should roll as
683 --defined by IGS_AD_PECRS_OFOP_DT.rollover_inclusion_ind
684 DECLARE
685 v_apcood_exists BOOLEAN := FALSE;
686 v_apcood_rollover_exists BOOLEAN := FALSE;
687 v_message_name varchar2(30);
688 CURSOR c_apcood IS
689 SELECT rollover_inclusion_ind
690 FROM IGS_AD_PECRS_OFOP_DT
691 WHERE adm_cal_type = p_cal_type AND
692 adm_ci_sequence_number = p_ci_sequence_number AND
693 DT_ALIAS = p_dt_alias AND
694 dai_sequence_number = p_dai_sequence_number;
695 BEGIN
696 -- Only check dates that are valid for admission period date overrides
697 IF IGS_AD_VAL_APCOOD.admp_val_apcood_da(
698 p_dt_alias,
699 v_message_name) = FALSE THEN
700 p_message_name := null;
701 RETURN TRUE;
702 END IF;
703 -- Check that date is not an admission period date override OR
704 -- has been defined as a override, but is not to be included in calendar
705 -- rollovers
706 FOR v_apcood_rec IN c_apcood LOOP
707 v_apcood_exists := TRUE;
708 IF v_apcood_rec.rollover_inclusion_ind = 'Y' THEN
709 v_apcood_rollover_exists := TRUE;
710 EXIT;
711 END IF;
712 END LOOP;
713 IF v_apcood_exists THEN
714 IF NOT v_apcood_rollover_exists THEN
715 -- Admission Period Date Override is not to be rolled
716 p_message_name := 'IGS_AD_POO_DATE_OVERRIDE';
717 RETURN FALSE;
718 END IF;
719 END IF;
720 p_message_name :=null;
721 RETURN TRUE;
722
723 END;
724 END calp_val_apcood_roll;
725 --
726 -- To insert a date alias instance offset as part of the rollover process
727 FUNCTION calp_ins_rollvr_daio(
728 p_dt_alias IN VARCHAR2 ,
729 p_dai_sequence_number IN NUMBER ,
730 p_cal_type IN VARCHAR2 ,
731 p_ci_sequence_number IN NUMBER ,
732 p_diff_days IN NUMBER ,
733 p_diff_months IN NUMBER ,
734 p_val_dt_alias IN VARCHAR2 ,
735 p_val_dai_sequence_number IN NUMBER ,
736 p_val_cal_type IN VARCHAR2 ,
737 p_val_ci_sequence_number IN NUMBER ,
738 p_daio_offset IN boolean ,
739 p_day_offset IN NUMBER ,
740 p_week_offset IN NUMBER ,
741 p_month_offset IN NUMBER ,
742 p_year_offset IN NUMBER ,
743 p_ofst_override IN VARCHAR2,
744 p_ci_rollover_sequence_number IN NUMBER ,
745 p_old_ci_sequence_number IN NUMBER ,
746 p_message_name OUT NOCOPY varchar2 )
747 RETURN boolean AS
748 lv_param_values VARCHAR2(1080);
749 BEGIN
750
751 DECLARE
752 X_ROWID VARCHAR2(25);
753
754 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
755 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
756 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
757 v_other_detail VARCHAR2(255);
758 token1_val VARCHAR2(255);
759 token2_val VARCHAR2(255);
760 v_cntr NUMBER;
761 v_dai_found BOOLEAN;
762 v_ci_found BOOLEAN;
763 v_offset_dt_alias IGS_CA_DA_INST_OFST.offset_dt_alias%TYPE;
764 v_dt_alias IGS_CA_DA_INST_OFST.DT_ALIAS%TYPE;
765 v_derived_cal_status IGS_CA_STAT.s_cal_status%TYPE;
766 v_val_start_dt IGS_CA_INST.start_dt%TYPE;
767 v_val_end_dt IGS_CA_INST.end_dt%TYPE;
768 v_dairn_start_dt IGS_CA_INST.start_dt%TYPE;
769 v_dairn_end_dt IGS_CA_INST.end_dt%TYPE;
770 v_new_dt_alias IGS_CA_DA_INST.DT_ALIAS%TYPE;
771 v_new_dai_sequence_number IGS_CA_DA_INST.sequence_number%TYPE;
772 v_new_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE;
773 v_new_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE;
774 v_ins_dt_alias IGS_CA_DA_INST.DT_ALIAS%TYPE;
775 v_ins_dai_sequence_number IGS_CA_DA_INST.sequence_number%TYPE;
776 v_ins_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE;
777 v_ins_ci_sequence_number IGS_CA_DA_INST.ci_sequence_number%TYPE;
778 v_ins_offset_dt_alias IGS_CA_DA_INST.DT_ALIAS%TYPE;
779 v_ins_offset_dai_seq_num IGS_CA_DA_INST.sequence_number%TYPE;
780 v_ins_offset_cal_type IGS_CA_DA_INST.CAL_TYPE%TYPE;
781 v_ins_offset_ci_seq_num IGS_CA_DA_INST.ci_sequence_number%TYPE;
782 v_sled_rec IGS_GE_S_LOG_ENTRY%ROWTYPE;
783 CURSOR c_new_dt_alias_instance(
784 cp_dt_alias IGS_CA_DA_INST_V.DT_ALIAS%TYPE,
785 cp_cal_type IGS_CA_DA_INST_V.CAL_TYPE%TYPE,
786 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE) IS
787 SELECT *
788 FROM IGS_CA_DA_INST
789 WHERE DT_ALIAS = cp_dt_alias AND
790 CAL_TYPE = cp_cal_type AND
791 ci_sequence_number = cp_ci_sequence_number;
792 CURSOR c_cal_instance(
793 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
794 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
795 SELECT *
796 FROM IGS_CA_INST
797 WHERE CAL_TYPE = cp_cal_type AND
798 sequence_number = cp_sequence_number;
799 CURSOR c_derived_cal_instance(
800 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
801 cp_start_dt IGS_CA_INST.start_dt%TYPE,
802 cp_end_dt IGS_CA_INST.end_dt%TYPE) IS
803 SELECT *
804 FROM IGS_CA_INST
805 WHERE CAL_TYPE = cp_cal_type AND
806 start_dt = cp_start_dt AND
807 end_dt = cp_end_dt;
808 CURSOR c_derived_cal_status(
809 cp_cal_status IGS_CA_STAT.CAL_STATUS%TYPE) IS
810 SELECT *
811 FROM IGS_CA_STAT
812 WHERE CAL_STATUS = cp_cal_status;
813 CURSOR c_s_log_entry_dai(
814 cp_s_log_type IGS_GE_S_LOG_ENTRY.s_log_type%TYPE,
815 cp_creation_dt IGS_GE_S_LOG_ENTRY.creation_dt%TYPE,
816 cp_key IGS_GE_S_LOG_ENTRY.key%TYPE,
817 cp_text IGS_GE_S_LOG_ENTRY.text%TYPE) IS
818 SELECT *
819 FROM IGS_GE_S_LOG_ENTRY
820 WHERE s_log_type = cp_s_log_type
821 AND creation_dt = cp_creation_dt
822 AND key = cp_key
823 AND text = cp_text;
824 CURSOR IGS_GE_S_LOG_ENTRY_CUR is
825 SELECT ROWID
826 FROM IGS_GE_S_LOG_ENTRY
827 WHERE s_log_type = gv_log_type
828 AND creation_dt = gv_log_creation_dt
829 AND key = gv_log_key
830 AND text = v_other_detail;
831 BEGIN
832
833 l_prog_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daio';
834 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daio.start';
835
836
837 -- Determine calendar instance match by adding rollover days difference
838 -- to start and end dates.
839 FOR v_cal_instance_rec IN c_cal_instance(
840 p_val_cal_type,
841 p_val_ci_sequence_number) LOOP
842 IF p_diff_days = 0 THEN
843 v_val_start_dt := add_months(v_cal_instance_rec.start_dt,p_diff_months);
844 v_val_end_dt := add_months(v_cal_instance_rec.end_dt,p_diff_months);
845 ELSE
846 v_val_start_dt := v_cal_instance_rec.start_dt + p_diff_days;
847 v_val_end_dt := v_cal_instance_rec.end_dt + p_diff_days;
848 END IF;
849 END LOOP;
850 v_ci_found := FALSE;
851 v_dai_found := FALSE;
852 -- Determine if the date alias instance offset calendar instance exists,
853 -- is active, and a date alias match exists.
854 FOR v_derived_cal_instance_rec IN c_derived_cal_instance(
855 p_val_cal_type,
856 v_val_start_dt,
857 v_val_end_dt) LOOP
858 v_ci_found := TRUE;
859 -- Obtain system calendar status of derived calendar instance.
860 FOR v_derived_cal_status_rec IN c_derived_cal_status(
861 v_derived_cal_instance_rec.CAL_STATUS) LOOP
862 v_derived_cal_status := v_derived_cal_status_rec.s_cal_status;
863 END LOOP;
864 v_dai_found := FALSE;
865 v_cntr := 0;
866 IF(v_derived_cal_status <> cst_inactive) THEN
867 FOR v_new_dt_alias_instance_rec IN c_new_dt_alias_instance(
868 p_val_dt_alias,
869 v_derived_cal_instance_rec.CAL_TYPE,
870 v_derived_cal_instance_rec.sequence_number) LOOP
871 IF v_new_dt_alias_instance_rec.CAL_TYPE = p_cal_type AND
872 v_new_dt_alias_instance_rec.ci_sequence_number = p_ci_sequence_number
873 THEN
874 IF ( v_new_dt_alias_instance_rec.sequence_number = p_val_dai_sequence_number)
875 THEN
876 v_cntr := 1;
877 v_dai_found := TRUE;
878 v_new_dt_alias := v_new_dt_alias_instance_rec.DT_ALIAS;
879 v_new_dai_sequence_number := v_new_dt_alias_instance_rec.sequence_number;
880 v_new_cal_type := v_new_dt_alias_instance_rec.CAL_TYPE;
881 v_new_ci_sequence_number := v_new_dt_alias_instance_rec.ci_sequence_number;
882 EXIT;
883 END IF;
884 ELSE
885 v_cntr := v_cntr + 1;
886 v_dai_found := TRUE;
887 v_new_dt_alias := v_new_dt_alias_instance_rec.DT_ALIAS;
888 v_new_dai_sequence_number := v_new_dt_alias_instance_rec.sequence_number;
889 v_new_cal_type := v_new_dt_alias_instance_rec.CAL_TYPE;
890 v_new_ci_sequence_number := v_new_dt_alias_instance_rec.ci_sequence_number;
891 END IF;
892 END LOOP;
893 END IF;
894 END LOOP;
895 IF(v_ci_found = FALSE) THEN
896
897 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_dt_alias||
898 '|' || p_dai_sequence_number||'|';
899
900 token2_val := p_val_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||
901 '-'||IGS_GE_DATE.IGSCHAR(v_val_end_dt);
902
903
904 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_OFFSET_CAL_NOT_EXIST');
905 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
906 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
907 FND_MESSAGE.SET_TOKEN('TOKEN3',p_val_dt_alias);
908 v_other_detail:=FND_MESSAGE.GET;
909
910 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
911
912 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daio.no_caoff';
913 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daio Log Message: ' || v_other_detail;
914 fnd_log.string_with_context( fnd_log.level_procedure,
915 l_label,
916 l_debug_str, NULL,
917 NULL,NULL,NULL,NULL,l_roll_seq);
918 END IF;
919
920 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
921 gv_log_type,
922 gv_log_creation_dt,
923 gv_log_key,
924 NULL,
925 v_other_detail);
926
927
928 p_message_name := null;
929 RETURN FALSE;
930 ELSIF (v_derived_cal_status = cst_inactive) THEN
931
932
933 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_dt_alias||
934 '|' || p_dai_sequence_number||'|' ;
935
936 token2_val := p_val_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||'-'||
937 IGS_GE_DATE.IGSCHAR(v_val_end_dt);
938
939 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_OFFSET_CAL_INACTIVE');
940 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
941 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
942 FND_MESSAGE.SET_TOKEN('TOKEN3',p_val_dt_alias);
943 v_other_detail:=FND_MESSAGE.GET;
944
945 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
946
947 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daio.inactive_caoff';
948 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daio Log Message: ' || v_other_detail;
949 fnd_log.string_with_context( fnd_log.level_procedure,
950 l_label,
951 l_debug_str, NULL,
952 NULL,NULL,NULL,NULL,l_roll_seq);
953 END IF;
954
955 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
956 gv_log_type,
957 gv_log_creation_dt,
958 gv_log_key,
959 NULL,
960 v_other_detail);
961
962
963 p_message_name := null;
964 RETURN FALSE;
965 ELSIF (v_dai_found = FALSE) THEN
966
967 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_dt_alias||
968 '|' || p_dai_sequence_number||'|';
969
970 token2_val := p_val_dt_alias||'('||p_val_dai_sequence_number||')'||
971 ' '||p_val_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||
972 '-'||IGS_GE_DATE.IGSCHAR(v_val_end_dt);
973
974
975 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_NO_OFFSET_DTALIAS_EXIST');
976 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
977 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
978 v_other_detail:=FND_MESSAGE.GET;
979
980 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
981
982 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daio.no_offda';
983 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daio Log Message: ' || v_other_detail;
984 fnd_log.string_with_context( fnd_log.level_procedure,
985 l_label,
986 l_debug_str, NULL,
987 NULL,NULL,NULL,NULL,l_roll_seq);
988 END IF;
989
990 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
991 gv_log_type,
992 gv_log_creation_dt,
993 gv_log_key,
994 NULL,
995 v_other_detail);
996
997
998 p_message_name :=null;
999 RETURN FALSE;
1000 ELSIF (v_cntr > 1) THEN
1001
1002 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_dt_alias||
1003 '|' || p_dai_sequence_number||'|' ;
1004
1005 token2_val := p_val_dt_alias||' '||p_val_cal_type||
1006 ' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||
1007 '-'||IGS_GE_DATE.IGSCHAR(v_val_end_dt)||'.';
1008
1009 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_DUP_OFFSET_DTALIAS');
1010 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1011 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
1012 v_other_detail:=FND_MESSAGE.GET;
1013
1014 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1015
1016 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daio.dupp_off';
1017 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daio Log Message: ' || v_other_detail;
1018 fnd_log.string_with_context( fnd_log.level_procedure,
1019 l_label,
1020 l_debug_str, NULL,
1021 NULL,NULL,NULL,NULL,l_roll_seq);
1022 END IF;
1023
1024 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1025 gv_log_type,
1026 gv_log_creation_dt,
1027 gv_log_key,
1028 NULL,
1029 v_other_detail);
1030
1031
1032
1033 p_message_name := null;
1034 RETURN FALSE;
1035 END IF;
1036 IF(p_daio_offset) THEN
1037 v_ins_dt_alias := p_dt_alias;
1038 v_ins_dai_sequence_number := p_dai_sequence_number;
1039 v_ins_cal_type := p_cal_type;
1040 v_ins_ci_sequence_number := p_ci_sequence_number;
1041 v_ins_offset_dt_alias := v_new_dt_alias;
1042 v_ins_offset_dai_seq_num := v_new_dai_sequence_number;
1043 v_ins_offset_cal_type := v_new_cal_type;
1044 v_ins_offset_ci_seq_num := v_new_ci_sequence_number;
1045 ELSE
1046 v_ins_dt_alias := v_new_dt_alias;
1047 v_ins_dai_sequence_number := v_new_dai_sequence_number;
1048 v_ins_cal_type := v_new_cal_type;
1049 v_ins_ci_sequence_number := v_new_ci_sequence_number;
1050 v_ins_offset_dt_alias := p_dt_alias;
1051 v_ins_offset_dai_seq_num := p_dai_sequence_number;
1052 v_ins_offset_cal_type := p_cal_type;
1053 v_ins_offset_ci_seq_num := p_ci_sequence_number;
1054 END IF;
1055
1056 IGS_CA_DA_INST_OFST_PKG.INSERT_ROW(
1057 X_ROWID => X_ROWID,
1058 X_DT_ALIAS => v_ins_dt_alias,
1059 X_dai_sequence_number => v_ins_dai_sequence_number,
1060 X_CAL_TYPE => v_ins_cal_type,
1061 X_ci_sequence_number => v_ins_ci_sequence_number,
1062 X_offset_dt_alias => v_ins_offset_dt_alias,
1063 X_offset_dai_sequence_number => v_ins_offset_dai_seq_num,
1064 X_offset_cal_type => v_ins_offset_cal_type,
1065 X_offset_ci_sequence_number => v_ins_offset_ci_seq_num,
1066 X_day_offset => p_day_offset,
1067 X_week_offset => p_week_offset,
1068 X_month_offset => p_month_offset,
1069 X_year_offset => p_year_offset,
1070 X_ofst_override => p_ofst_override,
1071 X_MODE => 'R');
1072 -- Insert new date alias instance offset constraints
1073 IF(p_daio_offset) THEN
1074 IF(calp_ins_roll_daioc(
1075 p_dt_alias,
1076 p_dai_sequence_number,
1077 p_cal_type,
1078 p_old_ci_sequence_number,
1079 p_val_dt_alias,
1080 p_val_dai_sequence_number,
1081 p_val_cal_type,
1082 p_val_ci_sequence_number,
1083 v_ins_dt_alias,
1084 v_ins_dai_sequence_number,
1085 v_ins_cal_type,
1086 v_ins_ci_sequence_number,
1087 v_ins_offset_dt_alias,
1088 v_ins_offset_dai_seq_num,
1089 v_ins_offset_cal_type,
1090 v_ins_offset_ci_seq_num,
1091 p_ci_rollover_sequence_number,
1092 p_message_name) = TRUE) THEN
1093 NULL;
1094 END IF;
1095 ELSE
1096 IF(calp_ins_roll_daioc(
1097 p_val_dt_alias,
1098 p_val_dai_sequence_number,
1099 p_val_cal_type,
1100 p_val_ci_sequence_number,
1101 p_dt_alias,
1102 p_dai_sequence_number,
1103 p_cal_type,
1104 p_old_ci_sequence_number,
1105 v_ins_dt_alias,
1106 v_ins_dai_sequence_number,
1107 v_ins_cal_type,
1108 v_ins_ci_sequence_number,
1109 v_ins_offset_dt_alias,
1110 v_ins_offset_dai_seq_num,
1111 v_ins_offset_cal_type,
1112 v_ins_offset_ci_seq_num,
1113 p_ci_rollover_sequence_number,
1114 p_message_name) = TRUE) THEN
1115 NULL;
1116 END IF;
1117 END IF;
1118 -- Delete offset IGS_CA_DA discrepancy notes attached to offset
1119 -- Firstly determine start and end dates of calendar
1120 FOR v_cal_instance_rec IN c_cal_instance(
1121 p_cal_type,
1122 p_ci_sequence_number) LOOP
1123 v_dairn_start_dt := v_cal_instance_rec.start_dt;
1124 v_dairn_end_dt := v_cal_instance_rec.end_dt;
1125 END LOOP;
1126 -- Delete IGS_GE_S_LOG_ENTRY for DAI if it exists.
1127
1128 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_val_dt_alias||
1129 '|' || p_val_dai_sequence_number||'|';
1130
1131 token2_val := p_dt_alias||'('||p_dai_sequence_number||')'||
1132 ' '||p_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_val_start_dt)||
1133 '-'||IGS_GE_DATE.IGSCHAR(v_val_end_dt);
1134
1135 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_NO_OFFSET_DTALIAS_EXIST');
1136 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1137 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
1138 v_other_detail:=FND_MESSAGE.GET;
1139
1140 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1141
1142 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip.multi_dai';
1143 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daip Log Message: ' || v_other_detail;
1144 fnd_log.string_with_context( fnd_log.level_procedure,
1145 l_label,
1146 l_debug_str, NULL,
1147 NULL,NULL,NULL,NULL,l_roll_seq);
1148 END IF;
1149
1150 OPEN c_s_log_entry_dai(
1151 gv_log_type,
1152 gv_log_creation_dt,
1153 gv_log_key,
1154 v_other_detail);
1155 FETCH c_s_log_entry_dai INTO v_sled_rec;
1156 IF c_s_log_entry_dai%NOTFOUND THEN
1157 CLOSE c_s_log_entry_dai;
1158
1159
1160 token1_val := p_cal_type|| '|'||p_ci_sequence_number|| '|' ||p_dt_alias||
1161 '|' || p_dai_sequence_number||'|';
1162
1163 token2_val := p_cal_type||' '||IGS_GE_DATE.IGSCHAR(v_dairn_start_dt)||
1164 '-'||IGS_GE_DATE.IGSCHAR(v_dairn_end_dt);
1165
1166
1167 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_OFFSET_CAL_NOT_EXIST');
1168 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1169 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
1170 FND_MESSAGE.SET_TOKEN('TOKEN3',p_dt_alias);
1171 v_other_detail:=FND_MESSAGE.GET;
1172
1173 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1174
1175 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_daip.multi_dai';
1176 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_daip Log Message: ' || v_other_detail;
1177 fnd_log.string_with_context( fnd_log.level_procedure,
1178 l_label,
1179 l_debug_str, NULL,
1180 NULL,NULL,NULL,NULL,l_roll_seq);
1181 END IF;
1182
1183 OPEN c_s_log_entry_dai(
1184 gv_log_type,
1185 gv_log_creation_dt,
1186 gv_log_key,
1187 v_other_detail);
1188 FETCH c_s_log_entry_dai INTO v_sled_rec;
1189 IF c_s_log_entry_dai%NOTFOUND THEN
1190 CLOSE c_s_log_entry_dai;
1191 ELSE
1192 CLOSE c_s_log_entry_dai;
1193
1194
1195 for v_IGS_GE_S_LOG_ENTRY_CUR in IGS_GE_S_LOG_ENTRY_CUR loop
1196 IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_IGS_GE_S_LOG_ENTRY_CUR.ROWID);
1197 end loop;
1198
1199
1200
1201 END IF;
1202 ELSE
1203 CLOSE c_s_log_entry_dai;
1204
1205
1206 for v_IGS_GE_S_LOG_ENTRY_CUR in IGS_GE_S_LOG_ENTRY_CUR loop
1207 IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_IGS_GE_S_LOG_ENTRY_CUR.ROWID);
1208 end loop;
1209
1210
1211 END IF;
1212 p_message_name := null;
1213 RETURN TRUE;
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1217 l_debug_str := 'Date Alias ='||p_dt_alias||'DAI seq num ='||(to_char(p_dai_sequence_number))||
1218 'Cal Type ='||p_cal_type||'CI seq num ='||(to_char(p_ci_sequence_number))||
1219 'Diff days ='||(to_char(p_diff_days))||'Diff months ='||(to_char(p_diff_months))||
1220 'DAI val ='||p_val_dt_alias||'DAI seq num ='||(to_char(p_val_dai_sequence_number))||
1221 'Val Cal Type ='||p_val_cal_type||'Val CI seq num ='||(to_char(p_val_ci_sequence_number))||
1222 'Day offset'||(to_char(p_day_offset))||'Week offset ='||(to_char(p_week_offset))||'Month offset ='||(to_char(p_month_offset))||
1223 'Year offset ='||(to_char(p_year_offset))||'Rollover CI seq num ='||(to_char(p_ci_rollover_sequence_number))||
1224 'Old CI seq num ='||(to_char(p_old_ci_sequence_number));
1225 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
1226 END IF;
1227 IF l_func_name IS NULL THEN
1228 l_func_name := 'calp_ins_rollvr_daio';
1229 END IF;
1230 App_Exception.Raise_Exception;
1231 END;
1232 END calp_ins_rollvr_daio;
1233 --
1234 -- To insert a date alias instance as part of the rollover process
1235 FUNCTION calp_ins_rollvr_dai(
1236 p_cal_type IN VARCHAR2 ,
1237 p_ci_sequence_number IN NUMBER ,
1238 p_diff_days IN NUMBER ,
1239 p_diff_months IN NUMBER ,
1240 p_rollover_cal_type IN VARCHAR2 ,
1241 p_rollover_ci_sequence_number IN NUMBER ,
1242 p_ci_rollover_sequence_number IN NUMBER ,
1243 p_message_name OUT NOCOPY varchar2 )
1244 RETURN boolean AS
1245 lv_param_values VARCHAR2(1080);
1246 BEGIN
1247 DECLARE
1248 X_ROWID VARCHAR2(25);
1249
1250 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
1251 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
1252 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
1253 v_other_detail VARCHAR2(255);
1254 token1_val VARCHAR2(255);
1255 token2_val VARCHAR2(255);
1256 v_message_name varchar2(30);
1257 v_offset_dt_alias IGS_CA_DA_INST_OFST.offset_dt_alias%TYPE;
1258 v_dt_alias IGS_CA_DA_INST_OFST.DT_ALIAS%TYPE;
1259 v_related_dt_alias IGS_CA_DA_INST_PAIR.related_dt_alias%TYPE;
1260 v_new_dai_dt_alias IGS_CA_DA_INST_V.DT_ALIAS%TYPE;
1261 v_new_absolute_val IGS_CA_DA_INST_V.absolute_val%TYPE;
1262 v_new_sequence_number IGS_CA_DA_INST_V.sequence_number%TYPE;
1263 CURSOR c_dai_sequence_number IS
1264 SELECT IGS_CA_DA_INST_SEQ_NUM_S.nextval
1265 FROM DUAL;
1266 CURSOR c_dt_alias_instance(
1267 cp_cal_type IGS_CA_DA_INST_V.CAL_TYPE%TYPE,
1268 cp_ci_sequence_number IGS_CA_DA_INST_V.ci_sequence_number%TYPE) IS
1269 SELECT *
1270 FROM IGS_CA_DA_INST
1271 WHERE CAL_TYPE = cp_cal_type AND
1272 ci_sequence_number = cp_ci_sequence_number;
1273 CURSOR c_dt_alias_instance_offset(
1274 cp_dt_alias IGS_CA_DA_INST_OFST.DT_ALIAS%TYPE,
1275 cp_dai_sequence_number IGS_CA_DA_INST_OFST.dai_sequence_number%TYPE,
1276 cp_cal_type IGS_CA_DA_INST_OFST.CAL_TYPE%TYPE,
1277 cp_ci_sequence_number IGS_CA_DA_INST_OFST.ci_sequence_number%TYPE) IS
1278 SELECT *
1279 FROM IGS_CA_DA_INST_OFST
1280 WHERE DT_ALIAS = cp_dt_alias AND
1281 dai_sequence_number = cp_dai_sequence_number AND
1282 CAL_TYPE = cp_cal_type AND
1283 ci_sequence_number = cp_ci_sequence_number;
1284 CURSOR c_dt_alias_inst_offset_offset(
1285 cp_dt_alias IGS_CA_DA_INST_OFST.offset_dt_alias%TYPE,
1286 cp_dai_seq_num IGS_CA_DA_INST_OFST.offset_dai_sequence_number%TYPE,
1287 cp_cal_type IGS_CA_DA_INST_OFST.offset_cal_type%TYPE,
1288 cp_ci_seq IGS_CA_DA_INST_OFST.offset_ci_sequence_number%TYPE) IS
1289 SELECT *
1290 FROM IGS_CA_DA_INST_OFST
1291 WHERE offset_dt_alias = cp_dt_alias AND
1292 offset_dai_sequence_number = cp_dai_seq_num AND
1293 offset_cal_type = cp_cal_type AND
1294 offset_ci_sequence_number = cp_ci_seq;
1295 CURSOR c_dt_alias_instance_pair(
1296 cp_dt_alias IGS_CA_DA_INST_PAIR.DT_ALIAS%TYPE,
1297 cp_dai_sequence_number IGS_CA_DA_INST_PAIR.dai_sequence_number%TYPE,
1298 cp_cal_type IGS_CA_DA_INST_PAIR.CAL_TYPE%TYPE,
1299 cp_ci_sequence_number IGS_CA_DA_INST_PAIR.ci_sequence_number%TYPE) IS
1300 SELECT *
1301 FROM IGS_CA_DA_INST_PAIR
1302 WHERE DT_ALIAS = cp_dt_alias AND
1303 dai_sequence_number = cp_dai_sequence_number AND
1304 CAL_TYPE = cp_cal_type AND
1305 ci_sequence_number = cp_ci_sequence_number;
1306 CURSOR c_dt_alias_inst_pair_pair(
1307 cp_dt_alias IGS_CA_DA_INST_PAIR.related_dt_alias%TYPE,
1308 cp_dai_seq_num IGS_CA_DA_INST_PAIR.related_dai_sequence_number%TYPE,
1309 cp_cal_type IGS_CA_DA_INST_PAIR.related_cal_type%TYPE,
1310 cp_ci_seq IGS_CA_DA_INST_PAIR.related_ci_sequence_number%TYPE) IS
1311 SELECT *
1312 FROM IGS_CA_DA_INST_PAIR
1313 WHERE related_dt_alias = cp_dt_alias AND
1314 related_dai_sequence_number = cp_dai_seq_num AND
1315 related_cal_type = cp_cal_type AND
1316 related_ci_sequence_number = cp_ci_seq;
1317 BEGIN
1318
1319 l_prog_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_dai';
1320 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_dai.start';
1321
1322 -- Rollover date alias instances and their offsets as part of the calendar
1323 -- instance rollover process. Notes are inserted into IGS_GE_S_LOG_ENTRY
1324 -- so that if a date alias instance cannot be inserted, it can be reported.
1325 -- Insert new dt_alias_instances for the calendar instance.
1326 FOR c_dt_alias_instance_rec IN c_dt_alias_instance(
1327 p_cal_type,
1328 p_ci_sequence_number) LOOP
1329 -- Validate IGS_CA_DA
1330 IF IGS_CA_VAL_DAI.calp_val_dai_da(
1331 c_dt_alias_instance_rec.DT_ALIAS,
1332 p_rollover_cal_type,
1333 v_message_name) = FALSE THEN
1334 IF v_message_name = 'IGS_CA_DTALIAS_CLOSED' THEN
1335
1336 token1_val := p_rollover_cal_type|| '|' ||p_rollover_ci_sequence_number|| '|' ;
1337
1338 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_DTALIAS_INS_CLOSED');
1339 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1340 FND_MESSAGE.SET_TOKEN('TOKEN2',c_dt_alias_instance_rec.DT_ALIAS);
1341 v_other_detail:=FND_MESSAGE.GET;
1342
1343 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1344
1345 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_dai.dai_closed';
1346 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_dai Log Message: ' || v_other_detail;
1347 fnd_log.string_with_context( fnd_log.level_procedure,
1348 l_label,
1349 l_debug_str, NULL,
1350 NULL,NULL,NULL,NULL,l_roll_seq);
1351 END IF;
1352
1353 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1354 gv_log_type,
1355 gv_log_creation_dt,
1356 gv_log_key,
1357 NULL,
1358 v_other_detail);
1359 ELSIF v_message_name = 'IGS_CA_DTALIAS_CALCAT_NOMATCH' THEN
1360
1361 token1_val := p_rollover_cal_type|| '|' ||p_rollover_ci_sequence_number|| '|' ;
1362
1363 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_DTALIAS_CALCAT_MISMATCH');
1364 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1365 FND_MESSAGE.SET_TOKEN('TOKEN2',c_dt_alias_instance_rec.DT_ALIAS);
1366 v_other_detail:=FND_MESSAGE.GET;
1367 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1368
1369 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_dai.calcat';
1370 l_debug_str := 'igs_ca_ins_roll_ci.calp_ins_rollvr_dai Log Message: ' || v_other_detail;
1371 fnd_log.string_with_context( fnd_log.level_procedure,
1372 l_label,
1373 l_debug_str, NULL,
1374 NULL,NULL,NULL,NULL,l_roll_seq);
1375 END IF;
1376 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1377 gv_log_type,
1378 gv_log_creation_dt,
1379 gv_log_key,
1380 NULL,
1381 v_other_detail);
1382
1383 END IF;
1384 -- NEW CODE FROM JULIE
1385 ELSIF IGS_CA_INS_ROLL_CI.calp_val_apcood_roll(
1386 c_dt_alias_instance_rec.DT_ALIAS,
1387 c_dt_alias_instance_rec.sequence_number,
1388 c_dt_alias_instance_rec.CAL_TYPE,
1389 c_dt_alias_instance_rec.ci_sequence_number,
1390 v_message_name) = FALSE THEN
1391
1392 token1_val := p_rollover_cal_type|| '|' ||p_rollover_ci_sequence_number|| '|' ;
1393
1394 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_DT_ADMPRD_OVRIDE_DT');
1395 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1396 v_other_detail:=FND_MESSAGE.GET;
1397
1398 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1399 gv_log_type,
1400 gv_log_creation_dt,
1401 gv_log_key,
1402 NULL,
1403 v_other_detail);
1404
1405 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1406 v_other_detail := 'CIR' || '|' || 'E' || '|' ||p_rollover_cal_type|| '|' ||
1407 p_rollover_ci_sequence_number|| '|' ||'Date alias instance '||
1408 'not created because it is an '||'Admission Period override date.';
1409 fnd_log.string_with_context( fnd_log.level_statement,l_label,v_other_detail, NULL,NULL,NULL,NULL,NULL,l_roll_seq);
1410 END IF;
1411
1412 -- END NEW CODE FROM JULIE
1413 ELSE -- create IGS_CA_DA_INST
1414 v_new_dai_dt_alias := c_dt_alias_instance_rec.DT_ALIAS;
1415 v_new_sequence_number := c_dt_alias_instance_rec.sequence_number;
1416 IF(c_dt_alias_instance_rec.absolute_val IS NULL) THEN
1417 v_new_absolute_val := NULL;
1418 ELSE
1419 IF p_diff_days = 0 THEN
1420 v_new_absolute_val :=
1421 add_months(c_dt_alias_instance_rec.absolute_val,p_diff_months);
1422 ELSE
1423 v_new_absolute_val := c_dt_alias_instance_rec.absolute_val + p_diff_days;
1424 END IF;
1425 END IF;
1426 -- Get next available IGS_CA_DA instance sequence number
1427 /* Use old IGS_CA_DA sequence number for Student Finance functionality */
1428
1429
1430 IGS_CA_DA_INST_PKG.INSERT_ROW(
1431 X_ROWId => X_ROWID,
1432 X_DT_ALIAS => v_new_dai_dt_alias,
1433 X_sequence_number => v_new_sequence_number,
1434 X_CAL_TYPE => p_rollover_cal_type,
1435 X_ci_sequence_number => p_rollover_ci_sequence_number,
1436 X_absolute_val => v_new_absolute_val,
1437 X_MODE => 'R');
1438
1439 -- Call the function to rollover the retention schedules defined in the teaching calendar. Added as part of retention enhancements from SWS side.
1440
1441 IF (chk_and_roll_ret(p_cal_type, -- Cal type being rolled over.
1442 p_ci_sequence_number, -- Sequence number of calendar instance being rolled over.
1443 v_new_dai_dt_alias, -- Date Alias in the calendar instance being rolled over.
1444 v_new_sequence_number, -- Sequence number of the data alias. When a DAI is rolled over, the sequence number remains unchanged.
1445 p_rollover_ci_sequence_number -- Sequence number of the new calendar instance created.
1446 ) = TRUE)
1447 THEN
1448 null;
1449 END IF;
1450
1451
1452 -- Offset processing
1453 FOR v_dt_alias_instance_offset_rec IN c_dt_alias_instance_offset(
1454 c_dt_alias_instance_rec.DT_ALIAS,
1455 c_dt_alias_instance_rec.sequence_number,
1456 c_dt_alias_instance_rec.CAL_TYPE,
1457 c_dt_alias_instance_rec.ci_sequence_number) LOOP
1458 v_offset_dt_alias := v_dt_alias_instance_offset_rec.offset_dt_alias;
1459 IF( calp_ins_rollvr_daio(
1460 v_new_dai_dt_alias,
1461 v_new_sequence_number,
1462 p_rollover_cal_type,
1463 p_rollover_ci_sequence_number,
1464 p_diff_days,
1465 p_diff_months,
1466 v_dt_alias_instance_offset_rec.offset_dt_alias,
1467 v_dt_alias_instance_offset_rec.offset_dai_sequence_number,
1468 v_dt_alias_instance_offset_rec.offset_cal_type,
1469 v_dt_alias_instance_offset_rec.offset_ci_sequence_number,
1470 TRUE,
1471 v_dt_alias_instance_offset_rec.day_offset,
1472 v_dt_alias_instance_offset_rec.week_offset,
1473 v_dt_alias_instance_offset_rec.month_offset,
1474 v_dt_alias_instance_offset_rec.year_offset,
1475 v_dt_alias_instance_offset_rec.ofst_override,
1476 p_ci_rollover_sequence_number,
1477 p_ci_sequence_number, -- needed for DAIOC rollover
1478 v_message_name) = FALSE) THEN
1479 -- Do nothing, since function currently always
1480 -- returns true
1481 NULL;
1482 END IF;
1483 END LOOP;
1484 FOR v_dt_alias_instance_offset_rec IN c_dt_alias_inst_offset_offset(
1485 c_dt_alias_instance_rec.DT_ALIAS,
1486 c_dt_alias_instance_rec.sequence_number,
1487 c_dt_alias_instance_rec.CAL_TYPE,
1488 c_dt_alias_instance_rec.ci_sequence_number) LOOP
1489 v_dt_alias := v_dt_alias_instance_offset_rec.DT_ALIAS;
1490 IF(calp_ins_rollvr_daio(
1491 v_new_dai_dt_alias,
1492 v_new_sequence_number,
1493 p_rollover_cal_type,
1494 p_rollover_ci_sequence_number,
1495 p_diff_days,
1496 p_diff_months,
1497 v_dt_alias_instance_offset_rec.DT_ALIAS,
1498 v_dt_alias_instance_offset_rec.dai_sequence_number,
1499 v_dt_alias_instance_offset_rec.CAL_TYPE,
1500 v_dt_alias_instance_offset_rec.ci_sequence_number,
1501 FALSE,
1502 v_dt_alias_instance_offset_rec.day_offset,
1503 v_dt_alias_instance_offset_rec.week_offset,
1504 v_dt_alias_instance_offset_rec.month_offset,
1505 v_dt_alias_instance_offset_rec.year_offset,
1506 v_dt_alias_instance_offset_rec.ofst_override,
1507 p_ci_rollover_sequence_number,
1508 p_ci_sequence_number, -- needed for DAIOC rollover
1509 v_message_name) = FALSE) THEN
1510 -- Do nothing, since function currently always
1511 -- returns true
1512 NULL;
1513 END IF;
1514 END LOOP;
1515 -- Dt Alias Instance Pair processing
1516 FOR v_dt_alias_instance_pair_rec IN c_dt_alias_instance_pair(
1517 c_dt_alias_instance_rec.DT_ALIAS,
1518 c_dt_alias_instance_rec.sequence_number,
1519 c_dt_alias_instance_rec.CAL_TYPE,
1520 c_dt_alias_instance_rec.ci_sequence_number) LOOP
1521 v_related_dt_alias := v_dt_alias_instance_pair_rec.related_dt_alias;
1522 IF( calp_ins_rollvr_daip(
1523 v_new_dai_dt_alias,
1524 v_new_sequence_number,
1525 p_rollover_cal_type,
1526 p_rollover_ci_sequence_number,
1527 p_diff_days,
1528 p_diff_months,
1529 v_dt_alias_instance_pair_rec.related_dt_alias,
1530 v_dt_alias_instance_pair_rec.related_dai_sequence_number,
1531 v_dt_alias_instance_pair_rec.related_cal_type,
1532 v_dt_alias_instance_pair_rec.related_ci_sequence_number,
1533 TRUE,
1534 p_ci_rollover_sequence_number,
1535 v_message_name) = FALSE) THEN
1536 -- Do nothing, since function currently always
1537 -- returns true
1538 NULL;
1539 END IF;
1540 END LOOP;
1541 FOR v_dt_alias_instance_pair_rec IN c_dt_alias_inst_pair_pair(
1542 c_dt_alias_instance_rec.DT_ALIAS,
1543 c_dt_alias_instance_rec.sequence_number,
1544 c_dt_alias_instance_rec.CAL_TYPE,
1545 c_dt_alias_instance_rec.ci_sequence_number) LOOP
1546 v_dt_alias := v_dt_alias_instance_pair_rec.DT_ALIAS;
1547 IF(calp_ins_rollvr_daip(
1548 v_new_dai_dt_alias,
1549 v_new_sequence_number,
1550 p_rollover_cal_type,
1551 p_rollover_ci_sequence_number,
1552 p_diff_days,
1553 p_diff_months,
1554 v_dt_alias_instance_pair_rec.DT_ALIAS,
1555 v_dt_alias_instance_pair_rec.dai_sequence_number,
1556 v_dt_alias_instance_pair_rec.CAL_TYPE,
1557 v_dt_alias_instance_pair_rec.ci_sequence_number,
1558 FALSE,
1559 p_ci_rollover_sequence_number,
1560 v_message_name) = FALSE) THEN
1561 -- Do nothing, since function currently always
1562 -- returns true
1563 NULL;
1564 END IF;
1565 END LOOP;
1566 END IF;
1567 END LOOP;
1568 RETURN TRUE;
1569 EXCEPTION
1570 WHEN OTHERS THEN
1571
1572 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
1573 l_debug_str := 'Cal Type ='||p_cal_type||'CI seq num ='||(to_char(p_ci_sequence_number))||
1574 'Diff days ='||(to_char(p_diff_days))||'Diff months ='||(to_char(p_diff_months))||
1575 'Rollover cal type ='||p_rollover_cal_type||'Rollover CI seq num ='||(to_char(p_rollover_ci_sequence_number))||
1576 'CI rollover seq num ='||(to_char(p_ci_rollover_sequence_number));
1577 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
1578 END IF;
1579 IF l_func_name IS NULL THEN
1580 l_func_name := 'calp_ins_rollvr_dai';
1581 END IF;
1582 App_Exception.Raise_Exception;
1583 END;
1584 END calp_ins_rollvr_dai;
1585 --
1586 -- To insert a ci relationship as part of the rollover process..
1587 FUNCTION CALP_INS_ROLLVR_CIR(
1588 p_cal_type IN VARCHAR2 ,
1589 p_sequence_number IN NUMBER ,
1590 p_diff_days IN NUMBER ,
1591 p_diff_months IN NUMBER ,
1592 p_sub_cal_type IN VARCHAR2 ,
1593 p_sub_ci_sequence_number NUMBER ,
1594 p_sup_cal_type IN VARCHAR2 ,
1595 p_sup_ci_sequence_number IN NUMBER ,
1596 p_ci_rollover_sequence_number IN NUMBER ,
1597 p_message_name OUT NOCOPY varchar2 )
1598 RETURN boolean AS
1599 lv_param_values VARCHAR2(1080);
1600 gv_other_detail VARCHAR2(255);
1601 -------------------------- Used to test the module ------------------------
1602 -- gv_log_type VARCHAR2(8) := 'CAL-ROLL';
1603 -- gv_log_creation_dt DATE;
1604 -- gv_log_key VARCHAR2(13) := 'TEST ROLLOVER';
1605 ---------------------------------------------------------------------------
1606 BEGIN -- calp_ins_rollvr_cir
1607 -- Insert calendar instance relationships as part of the calendar instance
1608 -- rollover process. This function takes the relationships of the existing
1609 -- calendar instance and tries to duplicate them for the new calendar
1610 -- instances. Notes are inserted into IGS_GE_S_LOG_ENTRY so that
1611 -- relationships that cannot be inserted, can be reported.
1612 --Insert superior passed. This is the superior passed from CALF0320 and is
1613 -- validated in the form logic.
1614 DECLARE
1615 X_ROWID VARCHAR2(25);
1616
1617 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
1618 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
1619 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
1620 v_derived_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
1621 v_derived_start_dt IGS_CA_INST.start_dt%TYPE;
1622 v_derived_end_dt IGS_CA_INST.end_dt%TYPE;
1623 v_sup_load_res_percentage
1624 IGS_CA_INST_REL.load_research_percentage%TYPE;
1625 v_other_detail VARCHAR2(255);
1626 token1_val VARCHAR2(255);
1627 token2_val VARCHAR2(255);
1628 v_dummy VARCHAR2(1);
1629 e_resource_busy EXCEPTION;
1630 PRAGMA EXCEPTION_INIT(e_resource_busy, -54 );
1631 CURSOR c_cal_inst_rltsp_sup IS
1632 SELECT cir.sup_cal_type,
1633 cir.sup_ci_sequence_number,
1634 cir.load_research_percentage
1635 FROM IGS_CA_INST_REL cir
1636 WHERE cir.sub_cal_type = p_cal_type AND
1637 cir.sub_ci_sequence_number = p_sequence_number;
1638 CURSOR c_cal_instance(
1639 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1640 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
1641 SELECT ci.start_dt,
1642 ci.end_dt,
1643 ci.CAL_TYPE
1644 FROM IGS_CA_INST ci
1645 WHERE ci.CAL_TYPE = cp_cal_type AND
1646 ci.sequence_number = cp_sequence_number;
1647 v_cal_inst_rec c_cal_instance%ROWTYPE;
1648 CURSOR c_derived_cal_instance(
1649 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
1650 cp_start_dt IGS_CA_INST.start_dt%TYPE,
1651 cp_end_dt IGS_CA_INST.end_dt%TYPE) IS
1652 SELECT ci.CAL_TYPE,
1653 ci.sequence_number,
1654 cs.s_cal_status
1655 FROM IGS_CA_INST ci,
1656 IGS_CA_STAT cs
1657 WHERE ci.CAL_TYPE = cp_cal_type AND
1658 ci.start_dt = cp_start_dt AND
1659 ci.end_dt = cp_end_dt AND
1660 ci.CAL_STATUS = cs.CAL_STATUS;
1661 v_derived_cal_instance_rec c_derived_cal_instance%ROWTYPE;
1662 CURSOR c_cir(
1663 cp_sub_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE,
1664 cp_sub_ci_sequence_number
1665 IGS_CA_INST_REL.sub_ci_sequence_number%TYPE,
1666 cp_sup_cal_type IGS_CA_INST_REL.sup_cal_type%TYPE,
1667 cp_sup_ci_sequence_number
1668 IGS_CA_INST_REL.sup_ci_sequence_number%TYPE) IS
1669 SELECT 'x'
1670 FROM IGS_CA_INST_REL cir
1671 WHERE cir.sub_cal_type = cp_sub_cal_type AND
1672 cir.sub_ci_sequence_number = cp_sub_ci_sequence_number AND
1673 cir.sup_cal_type = cp_sup_cal_type AND
1674 cir.sup_ci_sequence_number = cp_sup_ci_sequence_number;
1675 p_rowid VARCHAR2(25);
1676 p_val VARCHAR2(1);
1677 CURSOR c_s_log_entry_cir(
1678 cp_s_log_type IGS_GE_S_LOG_ENTRY.s_log_type%TYPE,
1679 cp_creation_dt IGS_GE_S_LOG_ENTRY.creation_dt%TYPE,
1680 cp_key IGS_GE_S_LOG_ENTRY.key%TYPE,
1681 cp_text IGS_GE_S_LOG_ENTRY.text%TYPE) IS
1682 SELECT SLE.rowid
1683 FROM IGS_GE_S_LOG_ENTRY sle
1684 WHERE sle.s_log_type = cp_s_log_type AND
1685 sle.creation_dt = cp_creation_dt AND
1686 sle.key = cp_key AND
1687 sle.text = cp_text
1688 FOR UPDATE OF
1689 sle.s_log_type,
1690 sle.creation_dt,
1691 sle.sequence_number,
1692 sle.key,
1693 sle.message_name,
1694 sle.text NOWAIT;
1695 BEGIN
1696
1697 l_prog_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_cir';
1698 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_cir.start';
1699
1700 -- Default value.
1701 p_message_name := null;
1702 -------------------------- Used to test the module ------------------------
1703 -- IGS_GE_GEN_003.GENP_INS_LOG(gv_log_type,
1704 -- gv_log_key,
1705 -- gv_log_creation_dt);
1706 ---------------------------------------------------------------------------
1707 v_sup_load_res_percentage := NULL;
1708 -- Loop through calendar instance relationships of calendar instance being
1709 -- rolled and duplicate relationships against the new calendar instance if
1710 -- valid
1711 FOR v_cal_inst_rltsp_sup_rec IN c_cal_inst_rltsp_sup LOOP
1712 -- Calculate the calendar instance start and end dates of the
1713 -- rollover superior calendar instance.
1714 OPEN c_cal_instance( v_cal_inst_rltsp_sup_rec.sup_cal_type,
1715 v_cal_inst_rltsp_sup_rec.sup_ci_sequence_number);
1716 FETCH c_cal_instance INTO v_cal_inst_rec;
1717 -- This should not happen.
1718 IF c_cal_instance%NOTFOUND THEN
1719 CLOSE c_cal_instance;
1720 RAISE NO_DATA_FOUND;
1721 END IF;
1722 CLOSE c_cal_instance;
1723 IF p_diff_days = 0 THEN
1724 v_derived_start_dt := add_months(v_cal_inst_rec.start_dt,
1725 p_diff_months);
1726 v_derived_end_dt := add_months(v_cal_inst_rec.end_dt,
1727 p_diff_months);
1728 ELSE
1729 v_derived_start_dt := v_cal_inst_rec.start_dt + p_diff_days;
1730 v_derived_end_dt := v_cal_inst_rec.end_dt + p_diff_days;
1731 END IF;
1732 -- Check for the existence of the new superior calendar instance.
1733 OPEN c_derived_cal_instance ( v_cal_inst_rltsp_sup_rec.sup_cal_type,
1734 v_derived_start_dt,
1735 v_derived_end_dt);
1736 FETCH c_derived_cal_instance INTO v_derived_cal_instance_rec;
1737 IF c_derived_cal_instance%FOUND THEN
1738 CLOSE c_derived_cal_instance;
1739 IF p_sup_cal_type IS NOT NULL AND
1740 (v_derived_cal_instance_rec.CAL_TYPE = p_sup_cal_type AND
1741 v_derived_cal_instance_rec.sequence_number = p_sup_ci_sequence_number)
1742 THEN
1743 -- Do nothing, will be processed below...
1744 v_sup_load_res_percentage :=
1745 v_cal_inst_rltsp_sup_rec.load_research_percentage;
1746 ELSE
1747 -- Validate that superior calendar instance match is not inactive.
1748 IF v_derived_cal_instance_rec.s_cal_status = cst_inactive THEN
1749 -- Insert IGS_GE_NOTE into IGS_GE_S_LOG_ENTRY
1750 -- for reporting purposes
1751 token1_val := p_sub_cal_type || '|' ||p_sub_ci_sequence_number || '|' ;
1752 token2_val := v_derived_cal_instance_rec.CAL_TYPE ||' '||IGS_GE_DATE.IGSCHAR(v_derived_start_dt) ||
1753 '-'||IGS_GE_DATE.IGSCHAR(v_derived_end_dt);
1754
1755 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_SUP_CAL_INACTIVE');
1756 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1757 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
1758 v_other_detail:=FND_MESSAGE.GET;
1759 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1760
1761 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_cir.inact_supcal';
1762 l_debug_str := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_cir Log Message: ' || v_other_detail;
1763 fnd_log.string_with_context( fnd_log.level_procedure,
1764 l_label,
1765 l_debug_str, NULL,
1766 NULL,NULL,NULL,NULL,l_roll_seq);
1767 END IF;
1768
1769 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1770 gv_log_type,
1771 gv_log_creation_dt,
1772 gv_log_key,
1773 NULL,
1774 v_other_detail);
1775
1776
1777 ELSIF NOT IGS_CA_VAL_CIR.calp_val_cir_ci(
1778 p_sub_cal_type,
1779 p_sub_ci_sequence_number,
1780 v_derived_cal_instance_rec.CAL_TYPE,
1781 v_derived_cal_instance_rec.sequence_number,
1782 p_message_name) THEN
1783 -- Insert IGS_GE_NOTE into IGS_GE_S_LOG_ENTRY for reporting purposes
1784
1785 token1_val := p_sub_cal_type || '|' ||p_sub_ci_sequence_number || '|';
1786 token2_val := v_derived_cal_instance_rec.CAL_TYPE ||' '||IGS_GE_DATE.IGSCHAR(v_derived_start_dt) ||
1787 '-'||IGS_GE_DATE.IGSCHAR(v_derived_end_dt);
1788
1789 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_SUP_CAL_INVALID_REL');
1790 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1791 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
1792 v_other_detail:=FND_MESSAGE.GET;
1793
1794 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1795
1796 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_cir.invalid_rel';
1797 l_debug_str := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_cir Log Message: ' || v_other_detail;
1798 fnd_log.string_with_context( fnd_log.level_procedure,
1799 l_label,
1800 l_debug_str, NULL,
1801 NULL,NULL,NULL,NULL,l_roll_seq);
1802 END IF;
1803 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1804 gv_log_type,
1805 gv_log_creation_dt,
1806 gv_log_key,
1807 NULL,
1808 v_other_detail);
1809 ELSE
1810 -- Insert calendar instance relationship if it doesn't exist already.
1811 OPEN c_cir(
1812 p_sub_cal_type,
1813 p_sub_ci_sequence_number,
1814 v_derived_cal_instance_rec.CAL_TYPE,
1815 v_derived_cal_instance_rec.sequence_number);
1816 FETCH c_cir INTO v_dummy;
1817 IF c_cir%NOTFOUND THEN
1818 CLOSE c_cir;
1819
1820 IGS_CA_INST_REL_PKG.INSERT_ROW(
1821 X_ROWID => X_ROWID,
1822 X_sub_cal_type => p_sub_cal_type,
1823 X_sub_ci_sequence_number => p_sub_ci_sequence_number,
1824 X_sup_cal_type => v_derived_cal_instance_rec.CAL_TYPE,
1825 X_sup_ci_sequence_number => v_derived_cal_instance_rec.sequence_number,
1826 X_load_research_percentage => v_cal_inst_rltsp_sup_rec.load_research_percentage,
1827 X_MODE => 'R');
1828
1829 ELSE
1830 CLOSE c_cir;
1831 END IF;
1832 -- We don't want to try to delete a log entry if it doesn't exist.
1833 IF v_other_detail IS NOT NULL THEN
1834 BEGIN
1835 -- Remove any exception errors previously inserted.
1836 OPEN c_s_log_entry_cir(
1837 gv_log_type,
1838 gv_log_creation_dt,
1839 gv_log_key,
1840 v_other_detail);
1841 LOOP
1842
1843 fetch c_s_log_entry_cir INTO p_rowid;
1844 if c_s_log_entry_cir%Found Then
1845
1846 IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(p_rowid);
1847
1848 else
1849 close c_s_log_entry_cir;
1850 exit;
1851 end if;
1852 END LOOP;
1853 EXCEPTION
1854
1855 -- locking conflict exception.
1856 WHEN e_resource_busy THEN
1857 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_LOCKED');
1858 IGS_GE_MSG_STACK.ADD;
1859 WHEN OTHERS THEN
1860
1861 RAISE;
1862
1863 END;
1864 END IF;
1865 END IF;
1866 END IF;
1867 ELSE -- NOT c_derived_cal_instance%FOUND
1868 CLOSE c_derived_cal_instance;
1869 -- Insert IGS_GE_NOTE into IGS_GE_S_LOG_ENTRY
1870 -- for reporting purposes when superior calendar instance does
1871 -- not exist.
1872
1873 token1_val := p_sub_cal_type || '|' ||p_sub_ci_sequence_number || '|' ;
1874 token2_val := v_cal_inst_rec.CAL_TYPE||' '||IGS_GE_DATE.IGSCHAR(v_derived_start_dt) ||
1875 '-'||IGS_GE_DATE.IGSCHAR(v_derived_end_dt);
1876
1877 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_SUP_CAL_NOT_EXIST');
1878 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1879 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
1880 v_other_detail:=FND_MESSAGE.GET;
1881
1882 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1883 gv_log_type,
1884 gv_log_creation_dt,
1885 gv_log_key,
1886 NULL,
1887 v_other_detail);
1888
1889 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1890 v_other_detail := 'CIR|E|' ||p_sub_cal_type || '|' ||
1891 p_sub_ci_sequence_number || '|' ||' Superior calendar ' || v_cal_inst_rec.CAL_TYPE ||
1892 ' '||TO_CHAR(v_derived_start_dt,'DD/MM/YYYY') ||'-'||TO_CHAR(v_derived_end_dt,'DD/MM/YYYY') ||' does not exist.';
1893 fnd_log.string_with_context( fnd_log.level_statement,l_label,v_other_detail, NULL,NULL,NULL,NULL,NULL,l_roll_seq);
1894 END IF;
1895
1896 END IF;
1897 END LOOP; -- c_cal_inst_rltsp_sup
1898 -- Calendar instance relationship is required.
1899 IF p_sup_cal_type IS NOT NULL THEN
1900 -- Check that the calendar relationship does not already exist
1901 OPEN c_cir(
1902 p_sub_cal_type,
1903 p_sub_ci_sequence_number,
1904 p_sup_cal_type,
1905 p_sup_ci_sequence_number);
1906 FETCH c_cir INTO v_dummy;
1907 IF c_cir%NOTFOUND THEN
1908 CLOSE c_cir;
1909 -- Get start and end dates of superior calendar for exception reporting.
1910 OPEN c_cal_instance(
1911 p_sup_cal_type,
1912 p_sup_ci_sequence_number);
1913 FETCH c_cal_instance INTO v_cal_inst_rec;
1914 -- This should not happen.
1915 IF c_cal_instance%NOTFOUND THEN
1916 CLOSE c_cal_instance;
1917 RAISE NO_DATA_FOUND;
1918 END IF;
1919 CLOSE c_cal_instance;
1920 -- Validate IGS_CA_INST_REL
1921 IF NOT IGS_CA_VAL_CIR.calp_val_cir_ci (
1922 p_sub_cal_type,
1923 p_sub_ci_sequence_number,
1924 p_sup_cal_type,
1925 p_sup_ci_sequence_number,
1926 p_message_name) THEN
1927 -- Insert IGS_GE_NOTE into IGS_GE_S_LOG_ENTRY
1928 -- for reporting purposes
1929
1930 token1_val := p_sub_cal_type || '|' ||p_sub_ci_sequence_number || '|';
1931 token2_val := v_cal_inst_rec.CAL_TYPE ||' '||IGS_GE_DATE.IGSCHAR(v_derived_start_dt) ||
1932 '-'||IGS_GE_DATE.IGSCHAR(v_derived_end_dt);
1933
1934 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_SUP_CAL_INVALID_REL');
1935 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1936 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
1937 v_other_detail:=FND_MESSAGE.GET;
1938 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1939
1940 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_cir.inv_rel_supcal';
1941 l_debug_str := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_cir Log Message: ' || v_other_detail;
1942 fnd_log.string_with_context( fnd_log.level_procedure,
1943 l_label,
1944 l_debug_str, NULL,
1945 NULL,NULL,NULL,NULL,l_roll_seq);
1946 END IF;
1947
1948 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
1949 gv_log_type,
1950 gv_log_creation_dt,
1951 gv_log_key,
1952 NULL,
1953 v_other_detail);
1954 ELSE
1955 -- Insert calendar instance relationship
1956
1957 IGS_CA_INST_REL_PKG.INSERT_ROW(
1958 X_ROWID => X_ROWID,
1959 X_sub_cal_type => p_sub_cal_type,
1960 X_sub_ci_sequence_number => p_sub_ci_sequence_number,
1961 X_sup_cal_type => p_sup_cal_type,
1962 X_sup_ci_sequence_number => p_sup_ci_sequence_number,
1963 X_load_research_percentage => v_sup_load_res_percentage,
1964 X_MODE => 'R');
1965
1966 -- Remove calendar instance not exists exception reporting if it exists
1967
1968 token1_val := p_sub_cal_type || '|' ||p_sub_ci_sequence_number || '|' ;
1969 token2_val := v_cal_inst_rec.CAL_TYPE ||' '||IGS_GE_DATE.IGSCHAR(v_cal_inst_rec.start_dt) ||
1970 '-'||IGS_GE_DATE.IGSCHAR(v_cal_inst_rec.end_dt);
1971
1972 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_SUP_CAL_NOT_EXIST');
1973 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
1974 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
1975 v_other_detail:=FND_MESSAGE.GET;
1976 BEGIN
1977 -- Remove any exception errors previously inserted.
1978 FOR v_c_dummy_rec IN c_s_log_entry_cir(
1979 gv_log_type,
1980 gv_log_creation_dt,
1981 gv_log_key,
1982 v_other_detail) LOOP
1983
1984 IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_c_dummy_rec.ROWID);
1985
1986 END LOOP;
1987 EXCEPTION
1988 -- locking conflict exception.
1989 WHEN e_resource_busy THEN
1990 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_LOCKED');
1991 IGS_GE_MSG_STACK.ADD;
1992
1993 END;
1994
1995 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
1996 v_other_detail := 'CIR|E|' || p_sub_cal_type || '|' ||
1997 p_sub_ci_sequence_number || '|' ||' Superior calendar ' || v_cal_inst_rec.CAL_TYPE ||
1998 ' '||TO_CHAR( v_cal_inst_rec.start_dt,'DD/MM/YYYY') ||'-'||TO_CHAR( v_cal_inst_rec.end_dt,'DD/MM/YYYY') ||' does not exist.';
1999 fnd_log.string_with_context( fnd_log.level_statement,l_label,v_other_detail, NULL,NULL,NULL,NULL,NULL,l_roll_seq);
2000 END IF;
2001
2002 END IF;
2003 ELSE
2004 CLOSE c_cir;
2005 END IF;
2006 END IF;
2007 RETURN TRUE;
2008 EXCEPTION
2009 WHEN OTHERS THEN
2010 IF c_cal_inst_rltsp_sup%ISOPEN THEN
2011 CLOSE c_cal_inst_rltsp_sup;
2012 END IF;
2013 IF c_cal_instance%ISOPEN THEN
2014 CLOSE c_cal_instance;
2015 END IF;
2016 IF c_derived_cal_instance%ISOPEN THEN
2017 CLOSE c_derived_cal_instance;
2018 END IF;
2019 IF c_cir%ISOPEN THEN
2020 CLOSE c_cir;
2021 END IF;
2022 IF c_s_log_entry_cir%ISOPEN THEN
2023 CLOSE c_s_log_entry_cir;
2024 END IF;
2025 RAISE;
2026 END;
2027 EXCEPTION
2028 WHEN OTHERS THEN
2029 IF fnd_log.test(fnd_log.level_statement,l_prog_label) THEN
2030 l_debug_str := 'Cal Type ='||p_cal_type||'Seq num ='||(to_char(p_sequence_number))||
2031 'Diff days ='||(to_char(p_diff_days))||'Diff months ='||(to_char(p_diff_months))||
2032 'Sub cal type ='||p_sub_cal_type||'Sub CI seq num ='||(to_char(p_sub_ci_sequence_number ))||
2033 'Sup cal type ='||p_sup_cal_type||'Sup CI seq num ='||(to_char(p_sup_ci_sequence_number ))||
2034 'Rollover CI seq num ='||(to_char( p_ci_rollover_sequence_number));
2035 fnd_log.string_with_context( fnd_log.level_statement,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,null);
2036 END IF;
2037 IF l_func_name IS NULL THEN
2038 l_func_name := 'calp_ins_rollvr_cir';
2039 END IF;
2040 App_Exception.Raise_Exception;
2041 END calp_ins_rollvr_cir;
2042 --
2043 -- To insert a calendar instance as part of the rollover process
2044 FUNCTION calp_ins_rollvr_ci(
2045 p_cal_type IN VARCHAR2 ,
2046 p_sequence_number IN NUMBER ,
2047 p_diff_days IN NUMBER ,
2048 p_diff_months IN NUMBER ,
2049 p_rollover_cal_type IN VARCHAR2 ,
2050 p_rollover_sequence_number IN NUMBER ,
2051 p_message_name OUT NOCOPY varchar2 )
2052 RETURN boolean AS
2053 cst_planned CONSTANT VARCHAR2(8) := 'PLANNED';
2054 cst_active CONSTANT VARCHAR2(8) := 'ACTIVE';
2055 cst_inactive CONSTANT VARCHAR2(8) := 'INACTIVE';
2056 FUNCTION calpl_val_rolled_ci(
2057 p_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2058 p_sequence_number IGS_CA_INST.sequence_number%TYPE,
2059 p_start_dt OUT NOCOPY IGS_CA_INST.start_dt%TYPE,
2060 p_end_dt OUT NOCOPY IGS_CA_INST.end_dt%TYPE,
2061 p_message_name OUT NOCOPY varchar2)
2062 RETURN BOOLEAN AS
2063 BEGIN
2064 -- Validate calendar instance exists and is not inactive.
2065 DECLARE
2066 v_other_detail VARCHAR2(255);
2067 v_cal_instance_rec IGS_CA_INST%ROWTYPE;
2068 CURSOR c_cal_instance IS
2069 SELECT *
2070 FROM IGS_CA_INST
2071 WHERE CAL_TYPE = p_cal_type AND
2072 sequence_number = p_sequence_number;
2073 CURSOR c_cal_status(
2074 cp_cal_status IGS_CA_INST.CAL_STATUS%TYPE) IS
2075 SELECT *
2076 FROM IGS_CA_STAT
2077 WHERE CAL_STATUS = cp_cal_status;
2078 BEGIN
2079
2080 p_message_name:=null;
2081 IF(c_cal_instance%ISOPEN = FALSE) THEN
2082
2083 OPEN c_cal_instance;
2084 END IF;
2085 LOOP
2086 FETCH c_cal_instance INTO v_cal_instance_rec;
2087 IF(c_cal_instance%NOTFOUND) THEN
2088 CLOSE c_cal_instance;
2089 -- Invalid parameters IGS_CA_INST status cannot be
2090 -- determined
2091 p_message_name :='IGS_OR_LOC_TYPE_CLOSED';
2092 RETURN FALSE;
2093 END IF;
2094 p_start_dt := v_cal_instance_rec.start_dt;
2095 p_end_dt := v_cal_instance_rec.end_dt;
2096 EXIT;
2097 END LOOP;
2098
2099 IF(c_cal_instance%ISOPEN) THEN
2100 CLOSE c_cal_instance;
2101 END IF;
2102 FOR v_cal_status_rec IN c_cal_status(
2103 v_cal_instance_rec.CAL_STATUS) LOOP
2104 IF(v_cal_status_rec.s_cal_status = cst_inactive) THEN
2105 -- Inactive IGS_CA_INST cannot be rolled
2106 p_message_name :='IGS_OR_ADDR_TYPE_CLOSE';
2107 RETURN FALSE;
2108 END IF;
2109 END LOOP;
2110
2111 RETURN TRUE;
2112 EXCEPTION
2113 WHEN OTHERS THEN
2114 IF l_func_name IS NULL THEN
2115 l_func_name := 'calpl_val_rolled_ci';
2116 END IF;
2117 App_Exception.Raise_Exception;
2118 END;
2119 END calpl_val_rolled_ci;
2120 FUNCTION calpl_ins_rollvr_ci(
2121 p_cal_type IGS_CA_INST.CAL_TYPE%TYPE ,
2122 p_sequence_number IGS_CA_INST.sequence_number%TYPE ,
2123 p_diff_days NUMBER ,
2124 p_diff_months NUMBER,
2125 p_rollover_cal_type IGS_CA_INST.CAL_TYPE%TYPE ,
2126 p_rollover_sequence_number IGS_CA_INST.sequence_number%TYPE ,
2127 p_new_sequence_number OUT NOCOPY IGS_CA_INST.sequence_number%TYPE,
2128 p_ci_rollover_sequence_number IGS_CA_INST.sequence_number%TYPE,
2129 p_message_name OUT NOCOPY varchar2 )
2130 RETURN BOOLEAN AS
2131 BEGIN
2132 DECLARE
2133 X_ROWID VARCHAR2(25);
2134 v_other_detail VARCHAR2(255);
2135 token1_val VARCHAR2(255);
2136 token2_val VARCHAR2(255);
2137 v_old_start_dt IGS_CA_INST.start_dt%TYPE;
2138 v_old_end_dt IGS_CA_INST.end_dt%TYPE;
2139 v_new_sequence_number IGS_CA_INST.sequence_number%TYPE;
2140 v_new_start_dt IGS_CA_INST.start_dt%TYPE;
2141 v_new_end_dt IGS_CA_INST.end_dt%TYPE;
2142 v_new_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
2143 v_new_alternate_code IGS_CA_INST.alternate_code%TYPE;
2144 l_n_org_id IGS_CA_INST.ORG_ID%TYPE := igs_ge_gen_003.get_org_id;
2145 v_new_cal_status IGS_CA_STAT.CAL_STATUS%TYPE;
2146 v_message_name VARCHAR2(30);
2147 v_cal_instance_rec IGS_CA_INST%ROWTYPE;
2148 v_new_cal_instance_rec IGS_CA_INST%ROWTYPE;
2149 v_cal_type_rec IGS_CA_TYPE%ROWTYPE;
2150 v_unique_sequence IGS_CA_INST.sequence_number%TYPE; -- added for bug 2563531
2151 v_cal_instance_exists BOOLEAN;
2152 v_new_term_instruction_time IGS_CA_INST.term_instruction_time%TYPE;
2153
2154 CURSOR c_cal_instance IS
2155 SELECT *
2156 FROM IGS_CA_INST
2157 WHERE CAL_TYPE = p_cal_type AND
2158 sequence_number = p_sequence_number;
2159 CURSOR c_new_cal_instance(
2160 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE,
2161 cp_start_dt IGS_CA_INST.start_dt%TYPE,
2162 cp_end_dt IGS_CA_INST.end_dt%TYPE) IS
2163 SELECT *
2164 FROM IGS_CA_INST
2165 WHERE CAL_TYPE = cp_cal_type AND
2166 start_dt = cp_start_dt AND
2167 end_dt = cp_end_dt;
2168 CURSOR c_cal_status(
2169 cp_cal_status IGS_CA_INST.CAL_STATUS%TYPE) IS
2170 SELECT *
2171 FROM IGS_CA_STAT
2172 WHERE s_cal_status = cp_cal_status;
2173 CURSOR c_cal_type(
2174 cp_cal_type IGS_CA_INST.CAL_TYPE%TYPE) IS
2175 SELECT *
2176 FROM IGS_CA_TYPE
2177 WHERE CAL_TYPE = cp_cal_type;
2178 CURSOR c_ci_sequence_number IS
2179 SELECT IGS_CA_INST_SEQ_NUM_S.nextval
2180 FROM DUAL;
2181
2182 -- Sequence to get unique value for alternate codes bug - 2563531
2183 CURSOR alt_uniq_seq IS
2184 SELECT igs_ca_inst_seq_num_s1.nextval from DUAL;
2185 -- cursor to check whether the alternate code for academic calendar instance is already present.
2186 CURSOR alt_code_unique(p_alternate_code IGS_CA_INST.ALTERNATE_CODE%TYPE) IS
2187 SELECT count(*)
2188 FROM IGS_CA_INST CI , IGS_CA_TYPE CAT
2189 WHERE CAT.CAL_TYPE = CI.CAL_TYPE
2190 AND CAT.S_CAL_CAT IN ('ACADEMIC' ,'LOAD','TEACHING')
2191 AND CI.ALTERNATE_CODE = p_alternate_code;
2192 l_count NUMBER(3);
2193
2194 BEGIN
2195 -- Insert rollover calendar instance, its calendar instance relationships,
2196 -- related date alias instances, and offsets. Any probems with the
2197 -- rollover are inserted into IGS_GE_S_LOG tables for reporting.
2198 p_message_name :=null;
2199 p_new_sequence_number := 0;
2200 v_cal_instance_exists := FALSE;
2201 -- Validate that the calendar instance status is not inactive
2202 IF(calpl_val_rolled_ci(
2203 p_cal_type,
2204 p_sequence_number,
2205 v_old_start_dt,
2206 v_old_end_dt,
2207 v_message_name) = FALSE) THEN
2208 -- Insert message into cal_inst_rollover_note for reporting
2209 -- why the calendar instance was not rolled.
2210 IF(v_message_name = 'IGS_OR_LOC_TYPE_CLOSED') THEN
2211
2212
2213 token1_val := p_cal_type || '|' ||p_sequence_number || '|';
2214 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_SUB_CAL_NOT_EXISTS');
2215 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
2216 FND_MESSAGE.SET_TOKEN('TOKEN2',p_cal_type);
2217 v_other_detail:=FND_MESSAGE.GET;
2218
2219 ELSIF(v_message_name = 'IGS_OR_ADDR_TYPE_CLOSE') THEN
2220
2221 token1_val := p_cal_type || '|' ||p_sequence_number || '|';
2222 token2_val := p_cal_type||IGS_GE_DATE.IGSCHAR(v_new_start_dt) ||
2223 IGS_GE_DATE.IGSCHAR(v_new_end_dt) ;
2224 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_SUB_CAL_INACTIVE');
2225 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
2226 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
2227 v_other_detail:=FND_MESSAGE.GET;
2228
2229 END IF;
2230
2231 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2232
2233 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calpl_ins_rollvr_ci.val1';
2234 l_debug_str := 'igs.plsql.igs_ca_ins_roll_ci.calpl_ins_rollvr_ci Log Message: ' || v_other_detail;
2235 fnd_log.string_with_context( fnd_log.level_procedure,
2236 l_label,
2237 l_debug_str, NULL,
2238 NULL,NULL,NULL,NULL,l_roll_seq);
2239 END IF;
2240 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2241 gv_log_type,
2242 gv_log_creation_dt,
2243 gv_log_key,
2244 NULL,
2245 v_other_detail);
2246 p_message_name := 'IGS_CA_CANNOT_ROLLOVER';
2247 RETURN FALSE;
2248 END IF;
2249 -- Validate that the calendar instance calendar type is not closed
2250 IF(IGS_CA_GEN_001.CALP_GET_CAT_CLOSED(
2251 p_cal_type,
2252 p_message_name) = TRUE) THEN
2253 -- Insert message into IGS_GE_S_LOG_ENTRY for reporting
2254 -- why the calendar instance was not rolled.
2255 token1_val := p_cal_type || '|' ||p_sequence_number || '|';
2256 token2_val := p_cal_type||IGS_GE_DATE.IGSCHAR(v_new_start_dt) ||
2257 IGS_GE_DATE.IGSCHAR(v_new_end_dt) ;
2258
2259 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_SUB_CAL_CLOSED');
2260 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
2261 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
2262 v_other_detail:=FND_MESSAGE.GET;
2263
2264 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2265
2266 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calpl_ins_rollvr_ci.val2';
2267 l_debug_str := 'igs.plsql.igs_ca_ins_roll_ci.calpl_ins_rollvr_ci Log Message: ' || v_other_detail;
2268 fnd_log.string_with_context( fnd_log.level_procedure,
2269 l_label,
2270 l_debug_str, NULL,
2271 NULL,NULL,NULL,NULL,l_roll_seq);
2272 END IF;
2273 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2274 gv_log_type,
2275 gv_log_creation_dt,
2276 gv_log_key,
2277 NULL,
2278 v_other_detail);
2279
2280
2281
2282
2283 p_message_name := 'IGS_CA_NOTROL_CLS_CALTYPES';
2284 RETURN FALSE;
2285 END IF;
2286 -- Validate calendar category security
2287 -- Check that new calendar instance does not already exist
2288 IF p_diff_days = 0 THEN
2289 v_new_start_dt := add_months(v_old_start_dt,p_diff_months);
2290 v_new_end_dt := add_months(v_old_end_dt,p_diff_months);
2291 ELSE
2292 v_new_start_dt := v_old_start_dt + p_diff_days;
2293 v_new_end_dt := v_old_end_dt + p_diff_days;
2294 END IF;
2295 OPEN c_new_cal_instance(
2296 p_cal_type,
2297 v_new_start_dt,
2298 v_new_end_dt);
2299 FETCH c_new_cal_instance INTO v_new_cal_instance_rec;
2300 IF c_new_cal_instance%NOTFOUND THEN
2301 CLOSE c_new_cal_instance;
2302 ELSE
2303
2304 CLOSE c_new_cal_instance;
2305 -- Insert message into IGS_GE_S_LOG_ENTRY for reporting
2306 -- why the calendar instance was not rolled.
2307
2308
2309 token1_val := p_cal_type || '|' ||p_sequence_number || '|';
2310 token2_val := p_cal_type||IGS_GE_DATE.IGSCHAR(v_new_start_dt) ||
2311 IGS_GE_DATE.IGSCHAR(v_new_end_dt);
2312
2313 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_SUB_CAL_EXISTS');
2314 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
2315 FND_MESSAGE.SET_TOKEN('TOKEN2',token2_val);
2316 v_other_detail:=FND_MESSAGE.GET;
2317
2318 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2319
2320 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calpl_ins_rollvr_ci.val3';
2321 l_debug_str := 'igs.plsql.igs_ca_ins_roll_ci.calpl_ins_rollvr_ci Log Message: ' || v_other_detail;
2322 fnd_log.string_with_context( fnd_log.level_procedure,
2323 l_label,
2324 l_debug_str, NULL,
2325 NULL,NULL,NULL,NULL,l_roll_seq);
2326 END IF;
2327
2328 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2329 gv_log_type,
2330 gv_log_creation_dt,
2331 gv_log_key,
2332 NULL,
2333 v_other_detail);
2334
2335
2336 v_cal_instance_exists := TRUE;
2337 v_new_cal_type := p_cal_type;
2338 v_new_sequence_number :=
2339 v_new_cal_instance_rec.sequence_number;
2340 END IF;
2341
2342 IF NOT v_cal_instance_exists THEN
2343
2344 -- Obtain user-defined planned calendar status
2345 FOR v_cal_status_rec IN c_cal_status(
2346 cst_planned) LOOP
2347 v_new_cal_status := v_cal_status_rec.CAL_STATUS;
2348 EXIT;
2349 END LOOP;
2350 -- Get calendar instance start and end dates
2351 OPEN c_cal_instance;
2352 FETCH c_cal_instance INTO v_cal_instance_rec;
2353 IF c_cal_instance%NOTFOUND THEN
2354 -- This should not occur, invalid parameters
2355 CLOSE c_cal_instance;
2356 p_message_name := 'IGS_CA_ROLLOVER_NOT_DONE';
2357 Return FALSE;
2358 ELSE
2359 CLOSE c_cal_instance;
2360 v_old_start_dt := v_cal_instance_rec.start_dt;
2361 v_old_end_dt := v_cal_instance_rec.end_dt;
2362 END IF;
2363
2364 -- Get next available calendar instance sequence number
2365 OPEN c_ci_sequence_number;
2366 FETCH c_ci_sequence_number INTO v_new_sequence_number;
2367 CLOSE c_ci_sequence_number;
2368 v_new_cal_type := p_cal_type;
2369 -- Determine dates
2370 IF p_diff_days = 0 THEN
2371 v_new_start_dt := add_months(v_old_start_dt,p_diff_months);
2372 v_new_end_dt := add_months(v_old_end_dt,p_diff_months);
2373 ELSE
2374 v_new_start_dt := v_old_start_dt + p_diff_days;
2375 v_new_end_dt := v_old_end_dt + p_diff_days;
2376 END IF;
2377 -- added for bug 2563531
2378 OPEN alt_uniq_seq;
2379 FETCH alt_uniq_seq INTO v_unique_sequence;
2380 CLOSE alt_uniq_seq;
2381 -- Determine new alternate code
2382 OPEN c_cal_type(v_cal_instance_rec.CAL_TYPE);
2383 FETCH c_cal_type INTO v_cal_type_rec;
2384 IF c_cal_type%NOTFOUND THEN
2385
2386 CLOSE c_cal_type;
2387 p_message_name := 'IGS_CA_ROLLOVER_NOT_DONE';
2388 Return FALSE;
2389 ELSE
2390 CLOSE c_cal_type;
2391 IF v_cal_type_rec.S_CAL_CAT = 'ACADEMIC' THEN
2392
2393 --
2394 -- Bug ID 1951883 // IGSCA002: ROLLING OVER CALENDAR PRODUCES INCORRECT ALTERNATE CODE
2395 -- Modified the substr parameter from
2396 -- IGS_GE_DATE.IGSCHAR(v_new_start_dt),8,4 to IGS_GE_DATE.IGSCHAR(v_new_start_dt),1,4
2397 --
2398 v_new_alternate_code :=
2399 substr(IGS_GE_DATE.IGSCHAR(v_new_start_dt),1,4);
2400 -- added for bug 2563531
2401 OPEN alt_code_unique(v_new_alternate_code);
2402 FETCH alt_code_unique INTO l_count;
2403 IF l_count > 0 THEN
2404 v_new_alternate_code := concat(substr(v_new_alternate_code,1,(10-length(v_unique_sequence))),v_unique_sequence);
2405 END IF;
2406 CLOSE alt_code_unique;
2407 ELSE
2408
2409 v_new_alternate_code :=
2410 v_cal_instance_rec.alternate_code;
2411 -- added for bug 2563531
2412 IF v_cal_type_rec.S_CAL_CAT IN ('LOAD','TEACHING') THEN
2413 v_new_alternate_code := concat(substr(v_new_alternate_code,1,(10-length(v_unique_sequence))),v_unique_sequence);
2414 END IF;
2415 END IF;
2416 END IF;
2417 -- Insert new calendar instance
2418 v_new_term_instruction_time := v_cal_instance_rec.term_instruction_time;
2419 --A DESCRIPTION Column is ADDED in the Insert Row Procedure
2420 --Enh Bug :- 2138560,Change Request for Calendar Instance
2421 --Description to be added in IGS_CA_INST_ALL Table
2422 IGS_CA_INST_PKG.INSERT_ROW(
2423 X_ROWID => X_ROWID,
2424 X_CAL_TYPE => v_new_cal_type,
2425 X_sequence_number => v_new_sequence_number,
2426 X_start_dt => v_new_start_dt,
2427 X_end_dt => v_new_end_dt,
2428 X_CAL_STATUS => v_new_cal_status,
2429 X_SUP_CAL_STATUS_DIFFER_IND => NULL,
2430 X_alternate_code => v_new_alternate_code,
2431 X_prior_ci_sequence_number => p_sequence_number,
2432 X_MODE => 'R',
2433 X_SS_DISPLAYED => v_cal_instance_rec.ss_displayed,
2434 X_ORG_ID => l_n_org_id,
2435 X_DESCRIPTION => NULL,
2436 X_TERM_INSTRUCTION_TIME => v_new_term_instruction_time,
2437 X_PLANNING_FLAG => v_cal_instance_rec.planning_flag,
2438 X_SCHEDULE_FLAG => v_cal_instance_rec.schedule_flag,
2439 X_ADMIN_FLAG => v_cal_instance_rec.admin_flag
2440 );
2441 -- Write to rollover messages
2442 token1_val := v_new_cal_type|| '|' ||v_new_sequence_number|| '|';
2443
2444 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_ROLLOVER_CREATED');
2445 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
2446 v_other_detail:=FND_MESSAGE.GET;
2447
2448 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2449 gv_log_type,
2450 gv_log_creation_dt,
2451 gv_log_key,
2452 NULL,
2453 v_other_detail);
2454
2455 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2456 v_other_detail := 'CIR' || '|' || 'I' || '|' ||v_new_cal_type|| '|' ||v_new_sequence_number|| '|' ||'CREATED BY ROLLOVER';
2457 fnd_log.string_with_context( fnd_log.level_statement,l_label,v_other_detail, NULL,NULL,NULL,NULL,NULL,l_roll_seq);
2458 END IF;
2459
2460 END IF;
2461 -- Insert new calendar instance into calendar instance relationships
2462
2463 IF(calp_ins_rollvr_cir(
2464 p_cal_type,
2465 p_sequence_number,
2466 p_diff_days,
2467 p_diff_months,
2468 v_new_cal_type,
2469 v_new_sequence_number,
2470 p_rollover_cal_type,
2471 p_rollover_sequence_number,
2472 p_ci_rollover_sequence_number,
2473 p_message_name) = TRUE) THEN
2474 NULL;
2475 END IF;
2476
2477 IF v_cal_instance_exists THEN
2478 -- Do not create related date alias instances
2479 p_message_name :='IGS_CA_CAL_INSTANCE_EXISTS';
2480 Return FALSE;
2481 END IF;
2482 -- Insert new calendar instance date alias instances and related offsets.
2483
2484 IF(calp_ins_rollvr_dai(
2485 p_cal_type,
2486 p_sequence_number,
2487 p_diff_days,
2488 p_diff_months,
2489 v_new_cal_type,
2490 v_new_sequence_number,
2491 p_ci_rollover_sequence_number,
2492 p_message_name) = TRUE) THEN
2493 NULL;
2494 END IF;
2495
2496 p_message_name :=null;
2497 p_new_sequence_number := v_new_sequence_number;
2498 RETURN TRUE;
2499 /* EXCEPTION
2500 WHEN OTHERS THEN
2501
2502 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
2503
2504 IF l_message_name = 'IGS_PE_UNIQUE_PID' THEN
2505 l_error_code := 'E567';
2506 ELSE
2507 l_error_code := 'E322';
2508 END IF;
2509 fnd_message.set_name('IGS',l_message_name);
2510 app_exception.raise_exception;*/
2511 END;
2512 END calpl_ins_rollvr_ci;
2513 BEGIN
2514 DECLARE
2515 v_other_detail VARCHAR2(255);
2516 token1_val VARCHAR2(255);
2517 token2_val VARCHAR2(255);
2518 v_new_sequence_number IGS_CA_INST.sequence_number%TYPE;
2519 v_message_name VARCHAR2(30);
2520 v_rollover_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
2521 v_rollover_sequence_number IGS_CA_INST.sequence_number%TYPE;
2522 v_cir5_rec IGS_CA_INST_REL%ROWTYPE;
2523 v_ci_rollover_sequence_number
2524 IGS_CA_INST.sequence_number%TYPE;
2525 TYPE t1_rollover_ci_rec IS RECORD(
2526 CAL_TYPE IGS_CA_INST.CAL_TYPE%TYPE,
2527 sequence_number IGS_CA_INST.sequence_number%TYPE,
2528 rollover_sequence_number IGS_CA_INST.sequence_number%TYPE);
2529 TYPE t1_rollover_ci_table IS TABLE OF t1_rollover_ci_rec
2530 INDEX BY BINARY_INTEGER;
2531 t1_rollover_ci t1_rollover_ci_table;
2532 t1_rollover_ci_index BINARY_INTEGER;
2533 v_index BINARY_INTEGER;
2534 v2_index BINARY_INTEGER;
2535 TYPE t2_rollover_ci_rec IS RECORD(
2536 CAL_TYPE IGS_CA_INST.CAL_TYPE%TYPE,
2537 sequence_number IGS_CA_INST.sequence_number%TYPE,
2538 rollover_sequence_number IGS_CA_INST.sequence_number%TYPE);
2539 TYPE t2_rollover_ci_table IS TABLE OF t2_rollover_ci_rec
2540 INDEX BY BINARY_INTEGER;
2541 t2_rollover_ci t2_rollover_ci_table;
2542 t2_rollover_ci_index BINARY_INTEGER;
2543 CURSOR c_ci_rollover_sequence_number IS
2544 SELECT IGS_CA_INST_REL_SUP_CI_SNO_S1.NEXTVAL
2545 FROM dual;
2546 CURSOR c_cir1 IS
2547 SELECT *
2548 FROM IGS_CA_INST_REL
2549 WHERE sup_cal_type = p_cal_type AND
2550 sup_ci_sequence_number = p_sequence_number;
2551 CURSOR c_cir2 IS
2552 SELECT *
2553 FROM IGS_CA_INST_REL cir1
2554 WHERE (cir1.sup_cal_type,cir1.sup_ci_sequence_number) IN
2555 (SELECT DISTINCT cir2.sub_cal_type,cir2.sub_ci_sequence_number
2556 FROM IGS_CA_INST_REL cir2
2557 WHERE cir2.sup_cal_type = p_cal_type AND
2558 cir2.sup_ci_sequence_number = p_sequence_number);
2559 CURSOR c_cir3 IS
2560 SELECT *
2561 FROM IGS_CA_INST_REL cir1
2562 WHERE (cir1.sup_cal_type,cir1.sup_ci_sequence_number) IN
2563 (SELECT DISTINCT cir2.sub_cal_type,cir2.sub_ci_sequence_number
2564 FROM IGS_CA_INST_REL cir2
2565 WHERE (cir2.sup_cal_type,cir2.sup_ci_sequence_number) IN
2566 (SELECT DISTINCT cir3.sub_cal_type,cir3.sub_ci_sequence_number
2567 FROM IGS_CA_INST_REL cir3
2568 WHERE cir3.sup_cal_type = p_cal_type AND
2569 cir3.sup_ci_sequence_number = p_sequence_number));
2570 CURSOR c_cir4 IS
2571 SELECT *
2572 FROM IGS_CA_INST_REL cir1
2573 WHERE (cir1.sup_cal_type,cir1.sup_ci_sequence_number) IN
2574 (SELECT DISTINCT cir2.sub_cal_type,cir2.sub_ci_sequence_number
2575 FROM IGS_CA_INST_REL cir2
2576 WHERE (cir2.sup_cal_type,cir2.sup_ci_sequence_number) IN
2577 (SELECT DISTINCT cir3.sub_cal_type,cir3.sub_ci_sequence_number
2578 FROM IGS_CA_INST_REL cir3
2579 WHERE (cir3.sup_cal_type,cir3.sup_ci_sequence_number) IN
2580 (SELECT DISTINCT cir4.sub_cal_type,cir4.sub_ci_sequence_number
2581 FROM IGS_CA_INST_REL cir4
2582 WHERE cir4.sup_cal_type = p_cal_type AND
2583 cir4.sup_ci_sequence_number = p_sequence_number)));
2584 CURSOR c_cir5 IS
2585 SELECT *
2586 FROM IGS_CA_INST_REL cir1
2587 WHERE (cir1.sup_cal_type,cir1.sup_ci_sequence_number) IN
2588 (SELECT DISTINCT cir2.sub_cal_type,cir2.sub_ci_sequence_number
2589 FROM IGS_CA_INST_REL cir2
2590 WHERE (cir2.sup_cal_type,cir2.sup_ci_sequence_number) IN
2591 (SELECT DISTINCT cir3.sub_cal_type,cir3.sub_ci_sequence_number
2592 FROM IGS_CA_INST_REL cir3
2593 WHERE (cir3.sup_cal_type,cir3.sup_ci_sequence_number) IN
2594 (SELECT DISTINCT cir4.sub_cal_type,cir4.sub_ci_sequence_number
2595 FROM IGS_CA_INST_REL cir4
2596 WHERE (cir4.sup_cal_type,cir4.sup_ci_sequence_number) IN
2597 (SELECT DISTINCT cir5.sub_cal_type,cir5.sub_ci_sequence_number
2598 FROM IGS_CA_INST_REL cir5
2599 WHERE cir5.sup_cal_type = p_cal_type AND
2600 cir5.sup_ci_sequence_number = p_sequence_number))));
2601 BEGIN
2602 -- Insert rollover calendar instance, its calendar instance relationships
2603 -- related date alias instances, and offsets. Any probems with the rollover
2604 -- are inserted into IGS_GE_S_LOG tables for reporting.
2605 p_message_name :=null;
2606 -- Get next rollover sequence number.
2607
2608 OPEN c_ci_rollover_sequence_number;
2609 FETCH c_ci_rollover_sequence_number INTO v_ci_rollover_sequence_number;
2610 CLOSE c_ci_rollover_sequence_number;
2611 -- initiate a session log
2612 gv_log_type := 'CAL-ROLL';
2613 gv_log_key := p_cal_type || '|' ||
2614 p_sequence_number;
2615 gv_log_creation_dt := SYSDATE;
2616 IGS_GE_GEN_003.GENP_INS_LOG(
2617 gv_log_type,
2618 gv_log_key,
2619 gv_log_creation_dt);
2620 gv_cal_count := gv_cal_count + 1;
2621
2622 l_prog_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_ci';
2623 l_label := 'igs.plsql.igs_ca_ins_roll_ci.calp_ins_rollvr_ci.begin';
2624 l_roll_seq := v_ci_rollover_sequence_number;
2625 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
2626
2627 l_debug_str := 'Rollover Sequence : ' || v_ci_rollover_sequence_number;
2628
2629 fnd_log.string_with_context( fnd_log.level_procedure,
2630 l_label,
2631 l_debug_str, NULL,
2632 NULL,NULL,NULL,NULL,l_roll_seq);
2633 END IF;
2634
2635
2636 -- Insert rolled calendar instance
2637 IF calpl_ins_rollvr_ci(
2638 p_cal_type,
2639 p_sequence_number,
2640 p_diff_days,
2641 p_diff_months,
2642 p_rollover_cal_type,
2643 p_rollover_sequence_number,
2644 v_new_sequence_number,
2645 v_ci_rollover_sequence_number,
2646 v_message_name) = FALSE THEN
2647 p_message_name := v_message_name;
2648 Return FALSE;
2649 ELSE
2650 v_rollover_cal_type := p_cal_type;
2651 v_rollover_sequence_number := v_new_sequence_number;
2652 END IF;
2653
2654 -- Process first level calendar instance sub-ordinates
2655 t1_rollover_ci.DELETE;
2656 t1_rollover_ci_index := 0;
2657 FOR c_cir1_rec IN c_cir1 LOOP
2658 v_new_sequence_number := 0;
2659 IF calpl_ins_rollvr_ci(
2660 c_cir1_rec.sub_cal_type,
2661 c_cir1_rec.sub_ci_sequence_number,
2662 p_diff_days,
2663 p_diff_months,
2664 v_rollover_cal_type,
2665 v_rollover_sequence_number,
2666 v_new_sequence_number,
2667 v_ci_rollover_sequence_number,
2668 v_message_name) = TRUE THEN
2669 -- Save rolled calendar instance
2670 t1_rollover_ci_index := t1_rollover_ci_index + 1;
2671 t1_rollover_ci(t1_rollover_ci_index).CAL_TYPE :=
2672 c_cir1_rec.sub_cal_type;
2673 t1_rollover_ci(t1_rollover_ci_index).sequence_number:=
2674 c_cir1_rec.sub_ci_sequence_number;
2675 t1_rollover_ci(t1_rollover_ci_index).rollover_sequence_number:=
2676 v_new_sequence_number;
2677 END IF;
2678
2679 END LOOP;
2680 -- Process second level calendar instance sub-ordinates
2681 t2_rollover_ci.DELETE;
2682 t2_rollover_ci_index := 0;
2683
2684 IF t1_rollover_ci.COUNT <> 0 THEN -- sub-ordinates existed
2685 FOR c_cir2_rec IN c_cir2 LOOP
2686 FOR v_index IN t1_rollover_ci.FIRST..t1_rollover_ci.LAST
2687 LOOP
2688 IF c_cir2_rec.sup_cal_type =
2689 t1_rollover_ci(v_index).CAL_TYPE AND
2690 c_cir2_rec.sup_ci_sequence_number =
2691 t1_rollover_ci(v_index).sequence_number THEN
2692 v_new_sequence_number := 0;
2693 IF calpl_ins_rollvr_ci(
2694 c_cir2_rec.sub_cal_type,
2695 c_cir2_rec.sub_ci_sequence_number,
2696 p_diff_days,
2697 p_diff_months,
2698 t1_rollover_ci(v_index).CAL_TYPE,
2699 t1_rollover_ci(v_index).rollover_sequence_number,
2700 v_new_sequence_number,
2701 v_ci_rollover_sequence_number,
2702 v_message_name) = TRUE THEN
2703 -- Save rolled calendar instance
2704 t2_rollover_ci_index := t2_rollover_ci_index + 1;
2705 t2_rollover_ci(t2_rollover_ci_index).CAL_TYPE :=
2706 c_cir2_rec.sub_cal_type;
2707 t2_rollover_ci(t2_rollover_ci_index).sequence_number:=
2708 c_cir2_rec.sub_ci_sequence_number;
2709 t2_rollover_ci(t2_rollover_ci_index).rollover_sequence_number:=
2710 v_new_sequence_number;
2711 END IF;
2712 EXIT;
2713 END IF;
2714 END LOOP;
2715 END LOOP;
2716 END IF;
2717 -- Process third level calendar instance sub-ordinates
2718 t1_rollover_ci.DELETE;
2719 t1_rollover_ci_index := 0;
2720 IF t2_rollover_ci.COUNT <> 0 THEN -- sub-ordinates existed
2721 FOR c_cir3_rec IN c_cir3 LOOP
2722 FOR v_index IN t2_rollover_ci.FIRST..t2_rollover_ci.LAST
2723 LOOP
2724 IF c_cir3_rec.sup_cal_type =
2725 t2_rollover_ci(v_index).CAL_TYPE AND
2726 c_cir3_rec.sup_ci_sequence_number =
2727 t2_rollover_ci(v_index).sequence_number THEN
2728 v_new_sequence_number := 0;
2729 IF calpl_ins_rollvr_ci(
2730 c_cir3_rec.sub_cal_type,
2731 c_cir3_rec.sub_ci_sequence_number,
2732 p_diff_days,
2733 p_diff_months,
2734 t2_rollover_ci(v_index).CAL_TYPE,
2735 t2_rollover_ci(v_index).rollover_sequence_number,
2736 v_new_sequence_number,
2737 v_ci_rollover_sequence_number,
2738 v_message_name) = TRUE THEN
2739 -- Save rolled calendar instance
2740 t1_rollover_ci_index := t1_rollover_ci_index + 1;
2741 t1_rollover_ci(t1_rollover_ci_index).CAL_TYPE :=
2742 c_cir3_rec.sub_cal_type;
2743 t1_rollover_ci(t1_rollover_ci_index).sequence_number:=
2744 c_cir3_rec.sub_ci_sequence_number;
2745 t1_rollover_ci(t1_rollover_ci_index).rollover_sequence_number:=
2746 v_new_sequence_number;
2747 END IF;
2748 EXIT;
2749 END IF;
2750 END LOOP;
2751 END LOOP;
2752 END IF;
2753 -- Process fourth level calendar instance sub-ordinates
2754 t2_rollover_ci.DELETE;
2755 t2_rollover_ci_index := 0;
2756 IF t1_rollover_ci.COUNT <> 0 THEN -- sub-ordinates existed
2757 FOR c_cir4_rec IN c_cir4 LOOP
2758 FOR v_index IN t1_rollover_ci.FIRST..t1_rollover_ci.LAST
2759 LOOP
2760 IF c_cir4_rec.sup_cal_type =
2761 t1_rollover_ci(v_index).CAL_TYPE AND
2762 c_cir4_rec.sup_ci_sequence_number =
2763 t1_rollover_ci(v_index).sequence_number THEN
2764 v_new_sequence_number := 0;
2765 IF calpl_ins_rollvr_ci(
2766 c_cir4_rec.sub_cal_type,
2767 c_cir4_rec.sub_ci_sequence_number,
2768 p_diff_days,
2769 p_diff_months,
2770 t1_rollover_ci(v_index).CAL_TYPE,
2771 t1_rollover_ci(v_index).rollover_sequence_number,
2772 v_new_sequence_number,
2773 v_ci_rollover_sequence_number,
2774 v_message_name) = TRUE THEN
2775 -- Save rolled calendar instance
2776 t2_rollover_ci_index := t2_rollover_ci_index + 1;
2777 t2_rollover_ci(t2_rollover_ci_index).CAL_TYPE :=
2778 c_cir4_rec.sub_cal_type;
2779 t2_rollover_ci(t2_rollover_ci_index).sequence_number:=
2780 c_cir4_rec.sub_ci_sequence_number;
2781 t2_rollover_ci(t2_rollover_ci_index).rollover_sequence_number:=
2782 v_new_sequence_number;
2783 END IF;
2784 EXIT;
2785 END IF;
2786 END LOOP;
2787 END LOOP;
2788 END IF;
2789 OPEN c_cir5;
2790
2791 FETCH c_cir5 INTO v_cir5_rec;
2792 IF c_cir5%NOTFOUND THEN
2793 CLOSE c_cir5;
2794 ELSE
2795 CLOSE c_cir5;
2796 -- Write to rollover messages
2797
2798
2799 token1_val := p_rollover_cal_type|| '|' ||p_rollover_sequence_number|| '|';
2800
2801 FND_MESSAGE.SET_NAME('IGS', 'IGS_CA_ROLLOVER_NOT_CREATED');
2802 FND_MESSAGE.SET_TOKEN('TOKEN1',token1_val);
2803 v_other_detail:=FND_MESSAGE.GET;
2804
2805 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
2806 gv_log_type,
2807 gv_log_creation_dt,
2808 gv_log_key,
2809 NULL,
2810 v_other_detail);
2811
2812 END IF;
2813 -- Commit changes
2814 COMMIT;
2815 p_message_name := NULL;
2816 RETURN TRUE;
2817 EXCEPTION
2818 WHEN OTHERS THEN
2819 l_msg_txt := fnd_message.get;
2820 fnd_message.set_name('IGS','IGS_CA_GENERIC_MSG');
2821 IF l_func_name IS NULL THEN
2822 l_func_name := 'calp_ins_rollvr_ci';
2823 END IF;
2824 fnd_message.set_token('FUNC_NAME',l_func_name);
2825 fnd_message.set_token('ERR_MSG',l_msg_txt);
2826 App_Exception.Raise_Exception;
2827 END;
2828 END calp_ins_rollvr_ci;
2829
2830 END IGS_CA_INS_ROLL_CI;