1 PACKAGE BODY IGS_PS_GEN_004 AS
2 /* $Header: IGSPS04B.pls 120.6 2006/05/16 00:40:35 sarakshi ship $ */
3 -------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smvk 08-Jul-2003 Bug # 3084602 , 3084615. Modified func recal_dl_date.
7 --smvk 29-Jul-2003 Bug: 3060089 - Modified func recal_dl_date
8 --pathipat 11-MAR-2003 Bug: 2822157 - Modified func recal_dl_date
9 --Nishikant 13Mar2003 Bug#2845730. The function recal_dl_date got a minor modification.
10 --------------------------------------------------------------------------
11
12
13 -- forward declaration of the function check_dl_date
14 FUNCTION check_dl_date( p_uooid IN igs_ps_usec_occurs.uoo_id%TYPE,
15 p_date IN DATE,
16 p_formula_method IN igs_en_nsu_dlstp.formula_method%TYPE,
17 p_last_meeting_date IN DATE DEFAULT NULL
18 )
19 RETURN DATE;
20
21 PROCEDURE log_to_fnd ( p_v_module IN VARCHAR2,
22 p_v_string IN VARCHAR2 ) IS
23 /***********************************************************************************************
24 Created By : smvk
25 Date Created By: 18-Sep-2004
26 Purpose : Procedure to log given String to fnd_log_messages.
27 Threshold level is STATEMENT. (i.e statement level logging)
28
29 Known limitations,enhancements,remarks:
30 Change History (in reverse chronological order)
31 Who When What
32 ********************************************************************************************** */
33
34 BEGIN
35 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
36
37 fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_ps_gen_004.' || p_v_module, p_v_string);
38
39 END IF;
40
41 END log_to_fnd;
42
43 PROCEDURE crsp_ins_fsr_hist(
44 p_course_cd IN VARCHAR2 ,
45 p_version_number IN NUMBER ,
46 p_funding_source IN VARCHAR2 ,
47 p_last_update_on IN DATE ,
48 p_update_on IN DATE ,
49 p_last_update_who IN VARCHAR2 ,
50 p_dflt_ind IN VARCHAR2,
51 p_restricted_ind IN VARCHAR2 )
52 AS
53
54 CURSOR c_course_status(
55 cp_course_cd IGS_PS_VER.course_cd%TYPE,
56 cp_version_number IGS_PS_VER.version_number%TYPE) IS
57 SELECT IGS_PS_STAT.s_course_status
58 FROM IGS_PS_STAT,IGS_PS_VER
59 WHERE IGS_PS_VER.course_cd = cp_course_cd AND
60 IGS_PS_VER.version_number = cp_version_number AND
61 IGS_PS_STAT.course_status = IGS_PS_VER.course_status;
62 cst_active CONSTANT VARCHAR2(8) DEFAULT 'ACTIVE';
63 v_course_status IGS_PS_STAT.s_course_status%TYPE;
64 x_rowid VARCHAR2(25);
65 l_org_id NUMBER(15);
66
67 BEGIN
68 OPEN c_course_status(
69 p_course_cd,
70 p_version_number);
71 FETCH c_course_status INTO v_course_status;
72 CLOSE c_course_status;
73 l_org_id := IGS_GE_GEN_003.GET_ORG_ID;
74 IF(v_course_status = cst_active) THEN
75 IGS_FI_FD_SRC_RSTN_H_pkg.Insert_Row(
76 X_ROWID => x_rowid,
77 X_COURSE_CD => p_course_cd,
78 X_HIST_START_DT => p_last_update_on,
79 X_VERSION_NUMBER => p_version_number,
80 X_FUNDING_SOURCE => p_funding_source,
81 X_HIST_END_DT => p_update_on,
82 X_HIST_WHO => p_last_update_who,
83 X_DFLT_IND => p_dflt_ind,
84 X_RESTRICTED_IND => p_restricted_ind,
85 X_MODE => 'R',
86 X_ORG_ID => l_org_id);
87 END IF;
88 EXCEPTION
89 WHEN OTHERS THEN
90 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
91 Fnd_Message.Set_Token('NAME','IGS_PS_GEN_004.crsp_ins_fsr_hist');
92 IGS_GE_MSG_STACK.ADD;
93 App_Exception.Raise_Exception;
94 END crsp_ins_fsr_hist;
95
96
97 FUNCTION crsp_val_call_nbr(
98 p_cal_type IN IGS_PS_UNIT_OFR_OPT_ALL.cal_type%TYPE ,
99 p_ci_sequence_number IN IGS_PS_UNIT_OFR_OPT_ALL.ci_sequence_number%TYPE,
100 p_call_number IN IGS_PS_UNIT_OFR_OPT_ALL.call_number%TYPE
101 )RETURN BOOLEAN AS
102 v_dummy VARCHAR2(1) DEFAULT NULL;
103 CURSOR c_val_call_nbr is
104 SELECT 'x'
105 FROM IGS_PS_UNIT_OFR_OPT uoo
106 WHERE uoo.cal_type = p_cal_type AND
107 uoo.ci_sequence_number = p_ci_sequence_number AND
108 uoo.call_number = p_call_number;
109 BEGIN
110 OPEN c_val_call_nbr;
111 FETCH c_val_call_nbr INTO v_dummy;
112 CLOSE c_val_call_nbr;
113 IF v_dummy IS NULL THEN
114 RETURN TRUE;
115 ELSE
116 RETURN FALSE;
117 END IF;
118 END crsp_val_call_nbr;
119
120 FUNCTION recal_dl_date (p_v_uoo_id IGS_PS_USEC_OCCURS_V.uoo_id%TYPE,
121 p_formula_method igs_en_nsu_dlstp.formula_method%TYPE,
122 p_durationdays IN OUT NOCOPY igs_en_nstd_usec_dl_v.ENR_DL_TOTAL_DAYS%TYPE,
123 p_round_method igs_en_nstd_usec_dl_v.round_method%TYPE,
124 p_OffsetDuration igs_en_nstd_usec_dl_v.offset_duration%TYPE,
125 p_offsetdays IN OUT NOCOPY NUMBER,
126 p_function_name igs_en_nstd_usec_dl.function_name%TYPE,
127 p_setup_id igs_en_nstd_usec_dl.non_std_usec_dls_id%TYPE,
128 p_offset_dt_code igs_en_nsu_dlstp.offset_dt_code%TYPE,
129 p_msg OUT NOCOPY VARCHAR2
130 )
131 RETURN DATE IS
132 ------------------------------------------------------------------
133 --Created by : ssomani ( Oracle IDC)
134 --Date created: 9-APR-2001
135 --
136 --Purpose: Created as part of the build for DLD Enrollment Setup : Calendar, Access, Timeslots (Version 1a)
137 -- Used for deadline date calculation for Enrollment setup.
138 -- Called from IGSPS101.pll and IGSPS083.pll
139 --
140 --Known limitations/enhancements and/or remarks:
141 --1. For the functions Variation_cuttoff, Record_cutoff and Grading_Schema,
142 --the parameter p_function_name = 'FUNCTION'
143 --For Discontinuation Dealdine calculation p_function_name = NULL
144 --2. The parameter p_setup_id is the corresponding setup id from tables IGS_EN_NSU_DLSTP (for FUNCTION) or
145 -- IGS_EN_NSD_DLSTP for Discontinuation.
146 --Change History:
147 --Who When What
148 --sarakshi 01-Sep-2005 bug#4114829, added cursor c_weekend_disc such that for discontinuation deadline it checks the respective tables for weekend inclusion
149 --sarakshi 23-Aug-2005 bug#4114488, prior to calculating the initial meeting days , removed the holidays from the
150 -- meeting days array, count of this array gives the offsetdays.
151 --sarakshi 22-Aug-2005 bug#4113948, when formula method is Meeting days thne passing last meeting days value to check_dl_date
152 --sarakshi 19-Aug-2005 Bug#4112602, removed the code added by pathipat for TBA occurrence deadline calculation.
153 --sarakshi 19-Aug-2005 Bug#4114992, Set the include weekend flag as Y for no occurrences and formula method Meeting days
154 --smvk 08-Jul-2003 Bug # 3084602. Calculated deadline date should n't be holiday / Weekend.
155 -- Bug # 3084615. Should return offset day of meeting day as deadline date.
156 --smvk 30-Jul-2003 Bug # 3060089. Calculating the holidays based on the data alias rather than holiday calendar instance duration days.
157 --smvk 29-Jul-2003 Bug: 3060089 - Modified code for meeting days,(i.e) For to be announced calculation taking its effective days
158 -- Modified the holiday calculation for meeting days and added into holidays table only when the holiday is a meeting day.
159 -- Sorting the meeting days and returning the deadline date from the meeting days table.
160 -- Modified the cursor cur_occur_unitsection and cal_inst.
161 --pathipat 11-MAR-2003 Bug: 2822157 - Modified code for formula_method = 'M'
162 -- Removed local variable no_meeting_day_checked, all its occurrences
163 -- replaced with l_no_meeting_day_checked, added cursor cur_teach_cal
164 -- Modified cursor c_weekdays
165 --Nishikant 13Mar2003 Bug#2845730. While calculating the offset days, if the round method is
166 -- Standard then it will simply apply round function of the Duration Days/Meeting Days.
167 -------------------------------------------------------------------
168
169 CURSOR c_call_cnstr IS
170 SELECT enr_dl_offset_cons_id FROM igs_en_dl_offset_cons
171 WHERE non_std_usec_dls_id = p_setup_id
172 AND deadline_type = 'E';
173 c_call_cnstr_rec c_call_cnstr%ROWTYPE;
174
175 CURSOR c_call_cnstr_disc IS
176 SELECT disc_dl_cons_id FROM igs_en_disc_dl_cons
177 WHERE non_std_disc_dl_stp_id = p_setup_id ;
178 c_call_cnstr_disc_rec c_call_cnstr_disc%ROWTYPE;
179
180 CURSOR c_st_end_dt IS
181 SELECT UNIT_SECTION_START_DATE, UNIT_SECTION_END_DATE
182 FROM igs_ps_unit_ofr_opt_all
183 WHERE uoo_id = p_v_uoo_id;
184 c_st_end_dt_rec c_st_end_dt%ROWTYPE;
185
186 weekends_count BOOLEAN := FALSE;
187 weekends NUMBER := 0;
188
189 --holiday cal
190 CURSOR cal_inst (cp_d_start_dt IN igs_ca_inst_all.start_dt%TYPE,
191 cp_d_end_dt IN igs_ca_inst_all.end_dt%TYPE) IS
192 SELECT
193 DISTINCT cv.alias_val
194 FROM
195 igs_ca_inst ci,
196 igs_ca_type ct,
197 igs_ca_stat cs,
198 igs_ca_da_inst_v cv
199 WHERE
200 ci.cal_type = ct.cal_type AND
201 ct.s_cal_cat = 'HOLIDAY' AND
202 ci.cal_status = cs.cal_status AND
203 cs.s_cal_status = 'ACTIVE' AND
204 ci.cal_type = cv.cal_type AND
205 ci.sequence_number = cv.ci_sequence_number AND
206 ci.start_dt < cp_d_end_dt AND
207 ci.end_dt > cp_d_start_dt AND
208 (cv.alias_val BETWEEN cp_d_start_dt AND cp_d_end_dt);
209
210 --Calculate the Actual Meeting Days.
211 --for formula method = 'M'
212
213 CURSOR cur_mon is
214 select 'TRUE'
215 from igs_ps_usec_occurs_v
216 where uoo_id = p_v_uoo_id and
217 monday = 'Y';
218
219 CURSOR cur_tue IS
220 select 'TRUE'
221 from igs_ps_usec_occurs_v
222 where uoo_id = p_v_uoo_id and
223 tuesday = 'Y';
224
225 CURSOR cur_wed IS
226 select 'TRUE'
227 from igs_ps_usec_occurs_v
228 where uoo_id = p_v_uoo_id and
229 wednesday = 'Y';
230
231 CURSOR cur_thu IS
232 select 'TRUE'
233 from igs_ps_usec_occurs_v
234 where uoo_id = p_v_uoo_id and
235 thursday = 'Y';
236
237 CURSOR cur_fri IS
238 select 'TRUE'
239 from igs_ps_usec_occurs_v
240 where uoo_id = p_v_uoo_id and
241 friday = 'Y';
242
243 CURSOR cur_sat IS
244 select 'TRUE'
245 from igs_ps_usec_occurs_v
246 where uoo_id = p_v_uoo_id and
247 saturday = 'Y';
248
249 CURSOR cur_sun IS
250 select 'TRUE'
251 from igs_ps_usec_occurs_v
252 where uoo_id = p_v_uoo_id and
253 sunday = 'Y';
254
255 meetingdays NUMBER DEFAULT 0;
256 l_mon_checked VARCHAR2(10) ;
257 l_tue_checked VARCHAR2(10) ;
258 l_wed_checked VARCHAR2(10) ;
259 l_thu_checked VARCHAR2(10) ;
260 l_fri_checked VARCHAR2(10) ;
261 l_sat_checked VARCHAR2(10) ;
262 l_sun_checked VARCHAR2(10) ;
263 l_unitsectionfound VARCHAR2(10) ;
264 total_meeting_days NUMBER ;
265
266 --Additions With respect to Enrollment Revision
267
268 TYPE meet_days IS TABLE OF DATE INDEX BY BINARY_INTEGER;
269 plsql_meet_days meet_days;
270 plsql_meet_days_temp meet_days;
271
272 -- new counter
273 cnt1 number DEFAULT 0;
274 --Cursor to fetch the UnitSection Occurrence Start ,End Dates and To Be Announced values (Formula Method='M')
275 -- not to inculde no set day unit section occurrence as a part of Bug # 3060089.
276 CURSOR cur_occur_unitsection
277 IS
278 SELECT start_date,end_date,to_be_announced,monday, tuesday,wednesday,thursday,friday,saturday,sunday
279 FROM igs_ps_usec_occurs
280 WHERE uoo_id=p_v_uoo_id
281 AND NO_SET_DAY_IND = 'N';
282
283 occur_unitsection_rec cur_occur_unitsection%ROWTYPE;
284
285
286 --end for formula method = 'M'
287 TYPE h_date IS TABLE OF DATE INDEX BY BINARY_INTEGER;
288 plsql_date_tab h_date;
289 holidays NUMBER DEFAULT 0;
290 cnt NUMBER DEFAULT 0;
291 l_date_hol DATE;
292 l_date DATE;
293 l_start_date DATE;
294 l_end_date DATE;
295 l_msg_name VARCHAR2(30);
296 date_found VARCHAR2(10);
297 deadlinedate DATE;
298 offsetdays NUMBER DEFAULT 0;
299 l_n_offsetdays NUMBER DEFAULT 0;
300 l_no_meeting_day_checked VARCHAR2(10);
301
302 formula_method igs_en_nsu_dlstp.formula_method%TYPE ;
303 durationdays igs_en_nstd_usec_dl_v.ENR_DL_TOTAL_DAYS%TYPE;
304 round_method igs_en_nstd_usec_dl_v.round_method%TYPE;
305 OffsetDuration igs_en_nstd_usec_dl_v.offset_duration%TYPE;
306 Meeting_day VARCHAR2(10) ;
307
308 -- Cursor to obtain the Teach Calendar end date
309 -- modifying the following cursor to select start date also. Also modified the from and where clause
310 CURSOR cur_teach_cal (cp_n_uoo_id IN igs_ps_unit_ofr_opt_all.uoo_id%TYPE) IS
311 SELECT ci.start_dt, ci.end_dt
312 FROM igs_ca_inst_all ci,
313 igs_ps_unit_ofr_opt_all uoo
314 WHERE uoo.uoo_id = cp_n_uoo_id
315 AND uoo.cal_type = ci.cal_type
316 AND uoo.ci_sequence_number = ci.sequence_number;
317
318 l_d_teach_end_dt igs_ps_unit_ofr_opt_v.cal_end_dt%TYPE;
319 l_d_teach_start_dt igs_ca_inst_all.start_dt%TYPE;
320 l_d_temp_end_dt igs_ca_inst_all.end_dt%TYPE;
321 l_d_temp_date igs_ca_inst_all.end_dt%TYPE;
322
323 --Cursor to get the check whether we have to include weekends or not
324 CURSOR c_weekend (cp_n_id IN NUMBER) IS
325 SELECT incl_wkend_duration_flag
326 FROM igs_en_nsu_dlstp_all
327 WHERE non_std_usec_dls_id = cp_n_id;
328
329 l_c_include_weekends igs_en_nsu_dlstp_all.incl_wkend_duration_flag%TYPE;
330
331 --Cursor to get the check whether we have to include weekends or not for the discontinuation deadline
332 CURSOR c_weekend_disc (cp_n_id IN NUMBER) IS
333 SELECT incl_wkend_duration_flag
334 FROM igs_en_nsd_dlstp_all
335 WHERE non_std_disc_dl_stp_id = cp_n_id;
336 BEGIN
337
338 -- Initialization
339 meetingdays := 0;
340 l_mon_checked := 'FALSE';
341 l_tue_checked := 'FALSE';
342 l_wed_checked := 'FALSE';
343 l_thu_checked := 'FALSE';
344 l_fri_checked := 'FALSE';
345 l_sat_checked := 'FALSE';
346 l_sun_checked := 'FALSE';
347 l_unitsectionfound := 'FALSE';
348 deadlinedate :=SYSDATE;
349 l_no_meeting_day_checked :='TRUE';
350 formula_method := p_formula_method;
351 durationdays := p_durationdays ;
352 round_method := p_round_method;
353 OffsetDuration := p_OffsetDuration;
354 Meeting_day := 'FALSE';
355
356
357 plsql_meet_days.DELETE; --Initialization
358 plsql_meet_days_temp.DELETE;
359 plsql_date_tab.DELETE;
360
361 p_msg := NULL;
362 OPEN c_st_end_dt;
363 FETCH c_st_end_dt INTO c_st_end_dt_rec;
364 IF c_st_end_dt_rec.UNIT_SECTION_START_DATE IS NULL THEN
365 Fnd_Message.Set_Name('IGS', 'IGS_EN_OFFSET_DT_NULL');
366 IGS_GE_MSG_STACK.ADD;
367 App_Exception.Raise_Exception;
368 END IF;
369 l_date := c_st_end_dt_rec.UNIT_SECTION_START_DATE;
370
371 IF p_offset_dt_code = 'USEC_EFFCT_ST_DT' THEN
372 l_start_date := c_st_end_dt_rec.UNIT_SECTION_START_DATE;
373 END IF;
374
375 l_end_date := c_st_end_dt_rec.UNIT_SECTION_END_DATE;
376
377 -- If Unit Section End Date is null, consider the Teach Calendar
378 -- End Date as the end_date for the calculation of Deadline Date
379 IF l_end_date IS NULL THEN
380 OPEN cur_teach_cal(p_v_uoo_id);
381 FETCH cur_teach_cal INTO l_d_teach_start_dt,l_d_teach_end_dt;
382 l_end_date := l_d_teach_end_dt;
383 CLOSE cur_teach_cal;
384 END IF;
385
386 CLOSE c_st_end_dt;
387
388 IF (upper(formula_method)='D' ) THEN
389 -- Retention Build
390 IF p_function_name='FUNCTION' THEN
391 OPEN c_weekend(p_setup_id);
392 FETCH c_weekend INTO l_c_include_weekends;
393 CLOSE c_weekend;
394 ELSE
395 OPEN c_weekend_disc(p_setup_id);
396 FETCH c_weekend_disc INTO l_c_include_weekends;
397 CLOSE c_weekend_disc;
398 END IF;
399 l_c_include_weekends := NVL(l_c_include_weekends,'Y');
400
401 IF l_c_include_weekends = 'Y' THEN
402 weekends_count := FALSE;
403 ELSE
404 weekends_count := TRUE;
405 WHILE (l_date<= l_end_date)
406 LOOP
407 IF TO_CHAR(l_date,'DY') IN ('SAT', 'SUN') THEN
408 Weekends := Weekends +1;
409 END IF ;
410 l_date := l_date +1;
411 END LOOP;
412 END IF;
413
414 --2. holiday calculation
415 -- Bug # 3060089. Calculating the holidays based on the data alias rather than holiday calendar instance duration days. -smvk
416 FOR cal_inst_rec IN cal_inst(c_st_end_dt_rec.UNIT_SECTION_START_DATE, NVL(c_st_end_dt_rec.UNIT_SECTION_END_DATE, l_d_teach_end_dt)) LOOP
417 l_date_hol := cal_inst_rec.alias_val;
418 IF (to_char (l_date_hol , 'DY') IN ('SAT', 'SUN') AND weekends_count) THEN
419 NULL;
420 ELSE
421 cnt := cnt + 1;
422 plsql_date_tab(cnt) := l_date_hol;
423 END IF;
424 END LOOP;
425
426 Holidays := plsql_date_tab.count;
427
428 --3
429 DurationDays := ((l_end_date - l_start_date) - (Weekends + Holidays)) + 1;
430 -- if duration days is null
431 DurationDays := nvl(DurationDays,0);
432
433 --4
434 IF ROUND_METHOD = 'A' THEN
435 SELECT ceil((DurationDays*OffsetDuration)/100) INTO offsetdays FROM dual;
436 ELSIF ROUND_METHOD = 'S' THEN
437 SELECT round((DurationDays*OffsetDuration)/100) INTO offsetdays FROM dual;
438 END IF;
439 -- if offsetdays is null
440 offsetdays := nvl(offsetdays,0);
441
442 --5
443 DeadlineDate := l_start_date + OffsetDays ; -- assuming offset_date = offsetdays
444
445 --Bug # 3084602. Calculated deadline date should n't be holiday / Weekend. -smvk
446 WHILE DeadlineDate <= l_end_date LOOP
447 IF to_char(DeadlineDate,'DY') NOT IN ('SAT', 'SUN') THEN
448 date_found := 'FALSE';
449 FOR L in 1..plsql_date_tab.count LOOP
450 IF plsql_date_tab(L) = DeadlineDate THEN
451 date_found := 'TRUE';
452 EXIT;
453 END IF;
454 END LOOP;
455 IF date_found = 'FALSE' THEN
456 EXIT;
457 END IF;
458 END IF;
459 DeadlineDate := DeadlineDate + 1;
460 END LOOP;
461
462 --6. Get the final deadline date after applying the Offset Constraints(see Appendix B for applying of Constraints) defined for the Setup data.
463
464 IF p_function_name='FUNCTION' THEN
465 OPEN c_call_cnstr;
466 FETCH c_call_cnstr INTO c_call_cnstr_rec;
467 calpl_constraint_resolve (
468 p_date_val => deadlineDate,
469 p_offset_cnstr_id => c_call_cnstr_rec.enr_dl_offset_cons_id,
470 p_type => p_function_name,
471 p_deadline_type => 'E',
472 p_msg_name => l_msg_name );
473 IF (l_msg_name IS NULL) THEN
474 p_offsetdays := offsetdays;
475 p_durationdays := DurationDays;
476 p_msg := l_msg_name;
477 -- check whether Deadline date is beyond usec end date or not
478 -- if found yes return usec end date else calculated deadline date
479 -- this check is done by the function check_dl_date
480 RETURN (check_dl_date (p_uooid => p_v_uoo_id,
481 p_date => DeadlineDate,
482 p_formula_method => p_formula_method));
483 END IF;
484 CLOSE c_call_cnstr;
485 ELSE
486 OPEN c_call_cnstr_disc;
487 FETCH c_call_cnstr_disc INTO c_call_cnstr_disc_rec;
488 calpl_constraint_resolve (
489 p_date_val => deadlineDate,
490 p_offset_cnstr_id => c_call_cnstr_disc_rec.disc_dl_cons_id,
491 p_type => p_function_name,
492 p_deadline_type => 'E',
493 p_msg_name => l_msg_name );
494 IF (l_msg_name IS NULL) THEN
495 p_offsetdays := offsetdays;
496 p_durationdays := DurationDays;
497 p_msg := l_msg_name;
498 -- check whether Deadline date is beyond usec end date or not
499 -- if found yes return usec end date else calculated deadline date
500 -- this check is done by the function check_dl_date
501 RETURN (check_dl_date (p_uooid => p_v_uoo_id,
502 p_date => DeadlineDate,
503 p_formula_method => p_formula_method));
504 END IF;
505 CLOSE c_call_cnstr_disc;
506 END IF;-- p_function_name='FUNCTION'
507
508 -------------------------------------------------
509 -- Formula Method = 'Meeting Days'
510 ELSIF (formula_method='M') THEN
511
512 --Additon of Code with respect to Enrollment Revision DLD
513 --1. calculating the meeting days
514 OPEN cur_mon;
515 FETCH cur_mon INTO l_mon_checked;
516 IF cur_mon%FOUND THEN
517 l_mon_checked:='TRUE';
518 l_no_meeting_day_checked:='FALSE';
519 END IF;
520 CLOSE cur_mon;
521
522 OPEN cur_tue;
523 FETCH cur_tue INTO l_tue_checked;
524 IF cur_tue%FOUND THEN
525 l_tue_checked:='TRUE';
526 l_no_meeting_day_checked:='FALSE';
527 END IF;
528 CLOSE cur_tue;
529
530 OPEN cur_wed;
531 FETCH cur_wed INTO l_wed_checked;
532 IF cur_wed%FOUND THEN
533 l_wed_checked:='TRUE';
534 l_no_meeting_day_checked:='FALSE';
535 END IF;
536 CLOSE cur_wed;
537
538 OPEN cur_thu;
539 FETCH cur_thu INTO l_thu_checked;
540 IF cur_thu%FOUND THEN
541 l_thu_checked:='TRUE';
542 l_no_meeting_day_checked:='FALSE';
543 END IF;
544 CLOSE cur_thu;
545
546 OPEN cur_fri;
547 FETCH cur_fri INTO l_fri_checked;
548 IF cur_fri%FOUND THEN
549 l_fri_checked:='TRUE';
550 l_no_meeting_day_checked:='FALSE';
551 END IF;
552 CLOSE cur_fri;
553
554 OPEN cur_sat;
555 FETCH cur_sat INTO l_sat_checked;
556 IF cur_sat%FOUND THEN
557 l_sat_checked:='TRUE';
558 l_no_meeting_day_checked:='FALSE';
559 END IF;
560 CLOSE cur_sat;
561
562 OPEN cur_sun;
563 FETCH cur_sun INTO l_sun_checked;
564 IF cur_sun%FOUND THEN
565 l_sun_checked:='TRUE';
566 l_no_meeting_day_checked:='FALSE';
567 END IF;
568 CLOSE cur_sun;
569
570 --Addition of Code with respect to Enrollment Revision
571
572
573 --Fetch the Unit Section Occurrence Start and End Dates and check if atleast one record is found
574
575 l_unitsectionfound:='FALSE';
576 OPEN cur_occur_unitsection;
577 FETCH cur_occur_unitsection INTO occur_unitsection_rec;
578 IF cur_occur_unitsection%NOTFOUND THEN
579 l_unitsectionfound:='FALSE';
580 ELSE
581 l_unitsectionfound:='TRUE';
582 END IF;
583 CLOSE cur_occur_unitsection;
584
585 -- if Unit Section Occurrence records are not fetched
586 -- consider this as duration days (Formula method) case with meeting days Monday to Friday
587
588 IF l_unitsectionfound = 'FALSE' THEN
589
590 --If non standard unit section does not have occurrence at all or have only NSD occurrences
591 --then deadline date is calculated using Duration days with include weekend flag checked
592 --Set the include weekend flag as Y for this setup, bug#4114992
593 l_c_include_weekends := 'Y';
594 weekends_count := FALSE;
595
596 -- calculate the holiday
597 -- Bug # 3060089. Calculating the holidays based on the data alias rather than holiday calendar instance duration days. -smvk
598 FOR cal_inst_rec IN cal_inst(c_st_end_dt_rec.UNIT_SECTION_START_DATE, NVL(c_st_end_dt_rec.UNIT_SECTION_END_DATE, l_d_teach_end_dt)) LOOP
599 l_date_hol := cal_inst_rec.alias_val;
600 IF (to_char (l_date_hol , 'DY') IN ('SAT', 'SUN') AND weekends_count) THEN
601 NULL;
602 ELSE
603 cnt := cnt + 1;
604 plsql_date_tab(cnt) := l_date_hol;
605 END IF;
606 END LOOP;
607
608 Holidays := plsql_date_tab.count;
609
610 --calculation of duration days
611 DurationDays := ((l_end_date - l_start_date) - (Weekends + Holidays)) + 1;
612 -- if Duration Days is null
613 DurationDays := nvl(DurationDays,0);
614
615
616 --applying round method
617 IF ROUND_METHOD = 'A' THEN
618 SELECT ceil((DurationDays*OffsetDuration)/100) INTO offsetdays FROM dual;
619 ELSIF ROUND_METHOD = 'S' THEN
620 SELECT round((DurationDays*OffsetDuration)/100) INTO offsetdays FROM dual;
621 END IF;
622 -- if offsetdays is null
623 offsetdays := nvl(offsetdays,0);
624
625 --calculate deadline date
626 -- The offset days are added blindly to the start date. Constraints are applied later.
627 DeadlineDate := l_start_date + OffsetDays ; -- assuming offset_date = offsetdays
628
629 --Bug # 3084602. Calculated deadline date should n't be holiday / Weekend. -smvk
630 WHILE DeadlineDate <= l_end_date LOOP
631 IF to_char(DeadlineDate,'DY') NOT IN ('SAT', 'SUN') THEN
632 date_found := 'FALSE';
633 FOR L in 1..plsql_date_tab.count LOOP
634 IF plsql_date_tab(L) = DeadlineDate THEN
635 date_found := 'TRUE';
636 EXIT;
637 END IF;
638 END LOOP;
639 IF date_found = 'FALSE' THEN
640 EXIT;
641 END IF;
642 END IF;
643 DeadlineDate := DeadlineDate + 1;
644 END LOOP;
645
646 -- Get the final deadline date after applying the Offset Constraints(see Appendix B for applying of Constraints)
647 -- defined for the Setup data.
648
649 IF p_function_name='FUNCTION' THEN
650 OPEN c_call_cnstr;
651 FETCH c_call_cnstr INTO c_call_cnstr_rec;
652 calpl_constraint_resolve (
653 p_date_val => deadlineDate,
654 p_offset_cnstr_id => c_call_cnstr_rec.enr_dl_offset_cons_id,
655 p_type => p_function_name,
656 p_deadline_type => 'E',
657 p_msg_name => l_msg_name );
658 IF (l_msg_name IS NULL) THEN
659 p_offsetdays := offsetdays;
660 p_durationdays := DurationDays;
661 p_msg := l_msg_name;
662 -- check whether Deadline date is beyond usec end date or not
663 -- if found yes return usec end date else calculated deadline date
664 -- this check is done by the function check_dl_date
665 RETURN (check_dl_date (p_uooid => p_v_uoo_id,
666 p_date => DeadlineDate,
667 p_formula_method => p_formula_method));
668 END IF;
669 CLOSE c_call_cnstr;
670 ELSE
671 OPEN c_call_cnstr_disc;
672 FETCH c_call_cnstr_disc INTO c_call_cnstr_disc_rec;
673 calpl_constraint_resolve (
674 p_date_val => deadlineDate,
675 p_offset_cnstr_id => c_call_cnstr_disc_rec.disc_dl_cons_id,
676 p_type => p_function_name,
677 p_deadline_type => 'E',
678 p_msg_name => l_msg_name );
679 IF (l_msg_name IS NULL) THEN
680 p_offsetdays := offsetdays;
681 p_durationdays := DurationDays;
682 p_msg := l_msg_name;
683 -- check whether Deadline date is beyond usec end date or not
684 -- if found yes return usec end date else calculated deadline date
685 -- this check is done by the function check_dl_date
686 RETURN (check_dl_date (p_uooid => p_v_uoo_id,
687 p_date => DeadlineDate,
688 p_formula_method => p_formula_method));
689 END IF;
690 CLOSE c_call_cnstr_disc;
691 END IF;-- p_function_name='FUNCTION'
692
693 -- over processing for the unit section occurrence if not found
694 --Only if the UnitSection Occurrence Records are fetched do we have to process
695 ELSIF l_unitsectionfound ='TRUE' THEN
696 --Loop Through all the Unit Section occurrences
697 FOR unitsection_occur IN cur_occur_unitsection
698 LOOP
699 IF unitsection_occur.to_be_announced='Y' THEN
700 -- For to be announced unit section occurrence user can optionally provide the start and end date.
701 -- need to consider the start date and end date in the following order 1) occurrence 2) section 3) teaching period.
702 l_date := NVL(unitsection_occur.start_date,NVL(c_st_end_dt_rec.unit_section_start_date,l_d_teach_start_dt));
703 l_d_temp_end_dt := NVL(unitsection_occur.end_date,NVL(c_st_end_dt_rec.unit_section_end_date,l_d_teach_end_dt));
704 WHILE ( l_date <= l_d_temp_end_dt )
705 LOOP
706 IF ( TO_CHAR(l_date,'DY') NOT IN ('SAT','SUN')) THEN
707 date_found:='FALSE';
708 FOR i IN 1..plsql_meet_days.count
709 LOOP
710 IF plsql_meet_days(i)=l_date THEN
711 date_found:='TRUE';
712 EXIT;
713 END IF;
714 END LOOP;
715 IF date_found='FALSE' THEN
716 cnt:=cnt+1;
717 plsql_meet_days(cnt):=l_date;
718 END IF;
719 END IF; -- For l_date not in (SAT,SUN)
720 l_date := l_date +1;
721 END LOOP; -- check for l_date bet start and end date of Unit Section Occurrence is over
722
723 --Check if to_be_announced is not 'Y'
724 ELSIF unitsection_occur.to_be_announced <> 'Y' THEN
725 IF (unitsection_occur.start_date IS NOT NULL AND unitsection_occur.end_date IS NOT NULL ) THEN
726 IF l_no_meeting_day_checked='TRUE' THEN
727 --Loop for l_date between Unit Section Occurrance Start and End Dates
728 l_date := unitsection_occur.start_date;
729 WHILE (l_date <= unitsection_occur.end_date AND l_date>=unitsection_occur.start_date) LOOP
730 IF (TO_CHAR(l_date,'DY') NOT IN ('SAT','SUN')) THEN
731 date_found:='FALSE';
732 FOR k IN 1..plsql_meet_days.count
733 LOOP
734 IF plsql_meet_days(k)=l_date THEN
735 date_found:='TRUE';
736 EXIT;
737 END IF;
738
739 END LOOP;
740 IF date_found='FALSE' THEN
741 cnt:=cnt+1;
742 plsql_meet_days(cnt):=l_date;
743 END IF;
744 END IF; -- For l_date not in (SAT,SUN)
745 l_date := l_date + 1;
746 END LOOP;
747
748 -- for no_meeting_day_checked
749
750 ELSIF l_No_meeting_day_checked <> 'TRUE' THEN
751 l_date:= unitsection_occur.start_date;
752 WHILE ( l_date <= unitsection_occur.end_date AND l_date>=unitsection_occur.start_date) LOOP
753
754 Meeting_day:='FALSE';
755 -- Bug # 3060089. Modifying the following conditions to refer the current occurrence meeting days.
756 -- and not the meeting days of other occurrences -smvk
757 IF ( TO_CHAR(l_date,'DY')='MON' AND unitsection_occur.monday = 'Y' ) THEN
758 Meeting_day:='TRUE';
759 ELSIF ( TO_CHAR(l_date,'DY')='TUE' AND unitsection_occur.tuesday = 'Y' ) THEN
760 Meeting_day:='TRUE';
761
762 ELSIF ( TO_CHAR(l_date,'DY')='WED' AND unitsection_occur.wednesday = 'Y' ) THEN
763 Meeting_day:='TRUE';
764
765 ELSIF ( TO_CHAR(l_date,'DY')='THU' AND unitsection_occur.thursday = 'Y' ) THEN
766 Meeting_day:='TRUE';
767
768 ELSIF ( TO_CHAR(l_date,'DY')='FRI' AND unitsection_occur.friday = 'Y' ) THEN
769 Meeting_day:='TRUE';
770
771 ELSIF ( TO_CHAR(l_date,'DY')='SAT' AND unitsection_occur.saturday = 'Y') THEN
772 Meeting_day:='TRUE';
773
774 ELSIF ( TO_CHAR(l_date,'DY')='SUN' AND unitsection_occur.sunday = 'Y') THEN
775 Meeting_day:='TRUE';
776
777 END IF;
778
779 IF Meeting_day ='TRUE' THEN
780 date_found:='FALSE';
781
782 FOR i IN 1..plsql_meet_days.count LOOP
783
784 IF plsql_meet_days(i)=l_date THEN
785
786 date_found:='TRUE';
787 EXIT;
788 END IF;
789
790 END LOOP;
791
792 IF date_found='FALSE' THEN
793 cnt:=cnt+1;
794 plsql_meet_days(cnt):=l_date;
795 END IF;
796
797 END IF;
798 l_date := l_date + 1;
799 END LOOP;
800
801 END IF; -- No_meeting_day_checked is over here.
802
803 ELSE -- If either of the occurrence dates is null or both are null
804 l_date := c_st_end_dt_rec.unit_section_start_date;
805 WHILE (l_date <= c_st_end_dt_rec.unit_section_end_date AND l_date >=c_st_end_dt_rec.unit_section_start_date) LOOP
806
807 Meeting_day:='FALSE';
808
809 IF ( TO_CHAR(l_date,'DY')='MON' AND l_mon_checked='TRUE' ) THEN
810 Meeting_day:='TRUE';
811 ELSIF ( TO_CHAR(l_date,'DY')='TUE' AND l_tue_checked='TRUE' ) THEN
812 Meeting_day:='TRUE';
813
814 ELSIF ( TO_CHAR(l_date,'DY')='WED' AND l_wed_checked='TRUE' ) THEN
815 Meeting_day:='TRUE';
816
817 ELSIF ( TO_CHAR(l_date,'DY')='THU' AND l_thu_checked='TRUE' ) THEN
818 Meeting_day:='TRUE';
819
820 ELSIF ( TO_CHAR(l_date,'DY')='FRI' AND l_fri_checked='TRUE' ) THEN
821 Meeting_day:='TRUE';
822
823 ELSIF ( TO_CHAR(l_date,'DY')='SAT' AND l_sat_checked='TRUE' ) THEN
824 Meeting_day:='TRUE';
825
826 ELSIF ( TO_CHAR(l_date,'DY')='SUN' AND l_sun_checked='TRUE' ) THEN
827 Meeting_day:='TRUE';
828
829 END IF;
830
831 IF Meeting_day='TRUE' THEN
832 date_found:='FALSE';
833
834 FOR i IN 1..plsql_meet_days.count LOOP
835
836 IF plsql_meet_days(i)=l_date THEN
837
838 date_found:='TRUE';
839 EXIT;
840 END IF;
841
842 END LOOP;
843
844 IF date_found='FALSE' THEN
845 cnt:=cnt+1;
846 plsql_meet_days(cnt):=l_date;
847 END IF;
848
849 END IF;
850 l_date := l_date + 1;
851 END LOOP;
852 END IF; -- for dates being not null
853 END IF; -- End of check against to_be_announced
854 END LOOP; --End of Records fetched
855 END IF; --If only any record is fetched
856
857
858 --End of Code addition w.r.to Revision DLD
859 --Removed the code as a part of the bug#4114488
860 /*Total_meeting_days := plsql_meet_days.COUNT;*/
861
862
863 --changed the validation here as specified in the Revision Enrollment DLD
864 --2. holiday calculation
865 -- Bug # 3060089. Calculating the holidays based on the data alias rather than holiday calendar instance duration days. -smvk
866 FOR cal_inst_rec IN cal_inst(c_st_end_dt_rec.UNIT_SECTION_START_DATE, NVL(c_st_end_dt_rec.UNIT_SECTION_END_DATE, l_d_teach_end_dt)) LOOP
867 l_date_hol := cal_inst_rec.alias_val;
868 -- Bug # 3060089. Modifying the logic of adding the holiday l_date_hol in holiday PLSQL table plsql_date_tab
869 -- only when the holiday is a meeting day (i.e present in the PLSQL table plsql_meet_days). - smvk
870
871 FOR i IN 1..plsql_meet_days.count LOOP
872 IF plsql_meet_days(i) = l_date_hol THEN
873 cnt1 := cnt1 + 1;
874 plsql_date_tab(cnt1) := l_date_hol;
875 EXIT;
876 END IF;
877 END LOOP;
878 END LOOP;
879
880 --Get non holiday meeting days in a temp array
881 FOR i IN 1..plsql_meet_days.count LOOP
882 date_found := 'FALSE';
883 FOR j in 1..plsql_date_tab.count LOOP
884 date_found := 'FALSE';
885 IF plsql_date_tab(j) = plsql_meet_days(i) THEN
886 date_found := 'TRUE';
887 EXIT;
888 END IF;
889 END LOOP;
890 IF date_found = 'FALSE' THEN
891 plsql_meet_days_temp(plsql_meet_days_temp.count+1):= plsql_meet_days(i);
892 END IF;
893 END LOOP;
894
895
896 --Assign the temp arrray to the main meeting days.
897 plsql_meet_days.delete;
898 plsql_meet_days:=plsql_meet_days_temp;
899
900 meetingdays := plsql_meet_days.COUNT;
901
902 --Removed the code as a part of the bug#4114488
903 /*Holidays := plsql_date_tab.count;
904
905 --3.final meeting days
906 -- Final meeting days is obtained by subtracting holidays from total meeting days (pathipat)
907 meetingdays := (Total_meeting_days - Holidays);*/
908
909
910
911 --4.
912 IF ROUND_METHOD = 'A' THEN
913 SELECT ceil((meetingdays*offsetduration)/100) INTO offsetdays FROM dual;
914 ELSIF ROUND_METHOD = 'S' THEN
915 SELECT round((meetingdays*offsetduration)/100) INTO offsetdays FROM dual;
916 END IF;
917 -- offsetdays is null
918 offsetdays := nvl(offsetdays,0);
919
920 -- The offsetdays is stored in another variable since it is decremented in the following loop
921 -- (pathipat)
922 l_n_offsetdays := offsetdays;
923
924 --5. deadline date = offset date + Offset days
925
926 l_date := l_start_date; --Offset Date(presently the only value can be Unit Section Start Date)
927
928
929 IF offsetdays > 0 THEN
930
931 -- Bug # 3060089. Sort the meeting days in the asc order.
932 FOR I in 1..plsql_meet_days.COUNT-1 LOOP
933 FOR J in I+1..plsql_meet_days.COUNT LOOP
934 IF plsql_meet_days(i) > plsql_meet_days(j) THEN
935 l_d_temp_date := plsql_meet_days(i);
936 plsql_meet_days(i) := plsql_meet_days(j);
937 plsql_meet_days(j) := l_d_temp_date;
938 END IF;
939 END LOOP;
940 END LOOP;
941
942 -- Bug # 3084615. if the offset date is 2 then the second meeting date should be considered as holiday (earlier (offsetdate +1) 3 meeting days was considered as deadline date).
943 -- Bug # 3084602. if the calculated deadline date is a holiday then find out next meeting day which is not holiday.
944 --Removed the code as a part of the bug#4114488
945 /*FOR K in offsetdays..plsql_meet_days.count LOOP
946 date_found := 'FALSE';
947 FOR L in 1..plsql_date_tab.count LOOP
948 IF plsql_date_tab(L) = plsql_meet_days(K) THEN
949 date_found := 'TRUE';
950 EXIT;
951 END IF;
952 END LOOP;
953 IF date_found = 'FALSE' THEN
954 l_date := plsql_meet_days(K);
955 EXIT;
956 END IF;
957 END LOOP;*/
958
959 l_date := plsql_meet_days(offsetdays);
960
961 END IF;
962
963 DeadlineDate := l_date ;
964 --6. apply offset constraint
965
966 IF p_function_name='FUNCTION' THEN
967 OPEN c_call_cnstr;
968 FETCH c_call_cnstr INTO c_call_cnstr_rec;
969 calpl_constraint_resolve (
970 p_date_val => deadlineDate,
971 p_offset_cnstr_id => c_call_cnstr_rec.enr_dl_offset_cons_id,
972 p_type => p_function_name,
973 p_deadline_type => 'E',
974 p_msg_name => l_msg_name );
975
976 IF (l_msg_name IS NULL) THEN
977 -- Pass back the non-decremented value of offset days (pathipat)
978 p_offsetdays := l_n_offsetdays;
979
980 -- For a formula_method of 'M' (Meeting days), the parameter p_durationdays holds
981 -- the value of meetingdays (pathipat)
982 p_durationdays := meetingdays;
983 p_msg := l_msg_name;
984 -- check whether Deadline date is beyond usec end date or not
985 -- if found yes return usec end date else calculated deadline date
986 -- this check is done by the function check_dl_date
987 RETURN (check_dl_date (p_uooid => p_v_uoo_id,
988 p_date => DeadlineDate,
989 p_formula_method => p_formula_method,
990 p_last_meeting_date => plsql_meet_days(plsql_meet_days.count) ));
991 END IF;
992 ELSE
993 OPEN c_call_cnstr_disc;
994 FETCH c_call_cnstr_disc INTO c_call_cnstr_disc_rec;
995 calpl_constraint_resolve (
996 p_date_val => deadlineDate,
997 p_offset_cnstr_id => c_call_cnstr_disc_rec.disc_dl_cons_id,
998 p_type => p_function_name,
999 p_deadline_type => 'E',
1000 p_msg_name => l_msg_name );
1001 IF (l_msg_name IS NULL) THEN
1002 -- Pass back the non-decremented value of offset days (pathipat)
1003 p_offsetdays := l_n_offsetdays;
1004 -- For a formula_method of 'M' (Meeting days), the parameter p_durationdays holds
1005 -- the value of meetingdays (pathipat)
1006 p_durationdays := meetingdays;
1007 p_msg := l_msg_name;
1008 -- check whether Deadline date is beyond usec end date or not
1009 -- if found yes return usec end date else calculated deadline date
1010 -- this check is done by the function check_dl_date
1011 RETURN (check_dl_date (p_uooid => p_v_uoo_id,
1012 p_date => DeadlineDate,
1013 p_formula_method => p_formula_method,
1014 p_last_meeting_date => plsql_meet_days(plsql_meet_days.count) ));
1015 END IF;
1016 CLOSE c_call_cnstr_disc;
1017 END IF;
1018
1019 END IF; -- formula_method
1020 -- check whether Deadline date is beyond usec end date or not
1021 -- if found yes return usec end date else calculated deadline date
1022 -- this check is done by the function check_dl_date
1023 RETURN (check_dl_date (p_uooid => p_v_uoo_id,
1024 p_date => DeadlineDate,
1025 p_formula_method => p_formula_method));
1026
1027 END recal_dl_date;
1028
1029
1030
1031 PROCEDURE calpl_constraint_resolve (
1032 p_date_val IN OUT NOCOPY IGS_EN_NSTD_USEC_DL.ENR_DL_DATE%TYPE,
1033 p_offset_cnstr_id IN IGS_EN_DL_OFFSET_CONS.ENR_DL_OFFSET_CONS_ID%TYPE,
1034 p_type IN IGS_EN_NSTD_USEC_DL.FUNCTION_NAME%TYPE,
1035 p_deadline_type IN VARCHAR2,
1036 p_msg_name OUT NOCOPY VARCHAR2 )
1037 AS
1038
1039 ------------------------------------------------------------------
1040 --Created by : pradhakr ( Oracle IDC)
1041 --Date created: 9-APR-2001
1042 --
1043 --Purpose: Created as part of the build for DLD Enrollment Setup : Calendar, Access, Timeslots (Version 1a)
1044 -- Used for deadline date calculation for Enrollment setup by applying Offset Constraints.
1045 -- Called from IGSPS101.pll and IGSPS083.pll
1046 --
1047 --Known limitations/enhancements and/or remarks:
1048 --
1049 --Change History:
1050 --Who When What
1051 --sarakshi 16-May-2006 bug#5190760,replaced ENR_DL_OFFSET_CONS_ID with NON_STD_USEC_DLS_ID in the cousor c_daoc_func
1052 -------------------------------------------------------------------
1053
1054
1055
1056 cst_must CONSTANT VARCHAR2(10) DEFAULT 'MUST';
1057 cst_must_not CONSTANT VARCHAR2(10) DEFAULT 'MUST NOT';
1058 cst_monday CONSTANT VARCHAR2(10) DEFAULT 'MONDAY';
1059 cst_tuesday CONSTANT VARCHAR2(10) DEFAULT 'TUESDAY';
1060 cst_wednesday CONSTANT VARCHAR2(10) DEFAULT 'WEDNESDAY';
1061 cst_thursday CONSTANT VARCHAR2(10) DEFAULT 'THURSDAY';
1062 cst_friday CONSTANT VARCHAR2(10) DEFAULT 'FRIDAY';
1063 cst_saturday CONSTANT VARCHAR2(10) DEFAULT 'SATURDAY';
1064 cst_sunday CONSTANT VARCHAR2(10) DEFAULT 'SUNDAY';
1065 cst_week_day CONSTANT VARCHAR2(10) DEFAULT 'WEEK DAY';
1066 cst_holiday CONSTANT VARCHAR2(10) DEFAULT 'HOLIDAY';
1067 cst_inst_break CONSTANT VARCHAR2(10) DEFAULT 'INST BREAK';
1068 cst_active CONSTANT VARCHAR2(10) DEFAULT 'ACTIVE';
1069 v_mod_count NUMBER(5);
1070 v_constraint_count NUMBER(5);
1071 v_loop_count NUMBER(5);
1072 v_message_name varchar2(30);
1073 v_alias_val IGS_CA_DA_INST.absolute_val%TYPE;
1074
1075
1076 v_msg_name VARCHAR2(30);
1077 v_changed BOOLEAN;
1078 p_constraint_count NUMBER(5);
1079 p_mod_count NUMBER(5);
1080 p_message_name VARCHAR2(30);
1081
1082 CURSOR c_daoc_func IS
1083 SELECT daoc.OFFSET_CONS_TYPE_CD,
1084 daoc.CONSTRAINT_CONDITION,
1085 daoc.CONSTRAINT_RESOLUTION
1086 FROM IGS_EN_DL_OFFSET_CONS daoc
1087 WHERE daoc.NON_STD_USEC_DLS_ID = p_offset_cnstr_id --bug#5190760,replaced ENR_DL_OFFSET_CONS_ID with NON_STD_USEC_DLS_ID
1088 AND daoc.deadline_type = p_deadline_type;
1089
1090 CURSOR c_daoc_adm IS
1091 SELECT daoc.OFFSET_CONS_TYPE_CD,
1092 daoc.CONSTRAINT_CONDITION,
1093 daoc.CONSTRAINT_RESOLUTION
1094 FROM IGS_EN_DISC_DL_CONS daoc
1095 WHERE daoc.DISC_DL_CONS_ID = p_offset_cnstr_id;
1096
1097
1098
1099 FUNCTION calpl_holiday_resolve (
1100
1101 p_date_val IN OUT NOCOPY DATE,
1102 p_cnstrt_condition IN IGS_EN_DL_OFFSET_CONS.constraint_condition%TYPE,
1103 p_cnstrt_resolution IN IGS_EN_DL_OFFSET_CONS.constraint_resolution%TYPE )
1104
1105 RETURN VARCHAR2
1106 AS
1107
1108 v_changed BOOLEAN;
1109 v_dummy VARCHAR2(1);
1110 v_tmp_mod_count NUMBER;
1111 v_tmp_date_val DATE;
1112 v_max_alias_val DATE DEFAULT NULL;
1113 v_min_alias_val DATE DEFAULT NULL;
1114
1115 CURSOR c_m_alias_val IS
1116 SELECT TRUNC(max(dai.absolute_val)), TRUNC(min(dai.absolute_val))
1117 FROM IGS_CA_DA_INST dai,
1118 IGS_CA_INST ci,
1119 IGS_CA_TYPE ct,
1120 IGS_CA_STAT cs
1121 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
1122 ct.S_CAL_CAT = cst_holiday AND
1123 cs.s_cal_status = ci.CAL_STATUS AND
1124 cs.s_cal_status = cst_active AND
1125 dai.CAL_TYPE = ci.CAL_TYPE;
1126
1127 CURSOR c_holiday (
1128 cp_date_val IGS_CA_DA_INST.absolute_val%TYPE) IS
1129 SELECT 'x'
1130 FROM IGS_CA_TYPE ct
1131 WHERE ct.S_CAL_CAT = cst_holiday AND
1132 EXISTS (SELECT 'x'
1133 FROM IGS_CA_INST ci,
1134 IGS_CA_STAT cs
1135 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
1136 ci.CAL_STATUS = cs.CAL_STATUS AND
1137 cs.s_cal_status = cst_active AND
1138 EXISTS (SELECT 'x'
1139 FROM IGS_CA_DA_INST dai
1140 WHERE dai.CAL_TYPE = ct.CAL_TYPE AND
1141 TRUNC(dai.absolute_val)= cp_date_val));
1142 BEGIN
1143 OPEN c_m_alias_val;
1144 FETCH c_m_alias_val INTO v_max_alias_val,
1145 v_min_alias_val;
1146 CLOSE c_m_alias_val;
1147 IF v_max_alias_val IS NULL AND
1148 v_min_alias_val IS NULL THEN
1149 -- No HOLIDAY date alias instances have been defined which can be resolved.
1150 IF p_cnstrt_condition = cst_must_not THEN
1151 -- constraint does not require resolving
1152 RETURN null;
1153 ELSE
1154 -- constraint cannot be resolved
1155 RETURN ('IGS_CA_HOLIDAY_CONST_UNRSLVD');
1156 END IF;
1157 ELSE
1158 IF p_cnstrt_condition = cst_must THEN
1159 IF (p_date_val > v_max_alias_val AND
1160 p_cnstrt_resolution >0 ) OR
1161 (p_date_val < v_min_alias_val AND
1162 p_cnstrt_resolution <0 ) THEN
1163 -- constraint cannot be resolved
1164 RETURN ('IGS_CA_HOLIDAY_CONS_UNRSVLD');
1165 END IF;
1166 END IF;
1167 v_tmp_date_val := p_date_val;
1168
1169 LOOP
1170 v_changed := FALSE;
1171 OPEN c_holiday (v_tmp_date_val);
1172 FETCH c_holiday INTO v_dummy;
1173 IF c_holiday%FOUND THEN
1174 CLOSE c_holiday;
1175 IF p_cnstrt_condition = cst_must_not THEN
1176 --update the date value and test again.
1177 v_tmp_date_val := v_tmp_date_val + p_cnstrt_resolution;
1178 v_changed := TRUE;
1179 END IF;
1180 ELSE -- record not found
1181 CLOSE c_holiday;
1182 IF p_cnstrt_condition = cst_must THEN
1183 --update the date value and test again.
1184 v_tmp_date_val := v_tmp_date_val + p_cnstrt_resolution;
1185
1186 IF (v_tmp_date_val > v_max_alias_val AND
1187 p_cnstrt_resolution >0 ) OR
1188 (v_tmp_date_val < v_min_alias_val AND
1189 p_cnstrt_resolution <0 ) THEN
1190 -- constraint cannot be resolved
1191 RETURN ('IGS_CA_HOLIDAY_CONS_UNRSVLD');
1192 END IF;
1193 v_changed := TRUE;
1194 END IF;
1195 END IF;
1196 EXIT WHEN v_changed = FALSE;
1197 END LOOP;
1198 -- resolve success or no resolving needed.
1199 p_date_val := v_tmp_date_val;
1200
1201 RETURN null;
1202 END IF;
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 IF c_m_alias_val%ISOPEN THEN
1206 CLOSE c_m_alias_val;
1207 END IF;
1208 IF c_holiday%ISOPEN THEN
1209 CLOSE c_holiday;
1210 END IF;
1211 RAISE;
1212
1213 END calpl_holiday_resolve;
1214
1215
1216
1217 FUNCTION calpl_inst_break_resolve (
1218 p_date_val IN OUT NOCOPY DATE,
1219 p_cnstrt_condition IN IGS_EN_DL_OFFSET_CONS.constraint_condition%TYPE,
1220 p_cnstrt_resolution IN IGS_EN_DL_OFFSET_CONS.constraint_resolution%TYPE )
1221
1222 RETURN varchar2
1223 AS
1224 v_changed BOOLEAN;
1225 v_dummy VARCHAR2(1);
1226 v_tmp_mod_count NUMBER;
1227 v_tmp_date_val DATE;
1228 v_max_alias_val DATE DEFAULT NULL;
1229 v_min_alias_val DATE DEFAULT NULL;
1230
1231 CURSOR c_m_alias_val2 IS
1232 SELECT TRUNC(MAX(dai2.absolute_val)), TRUNC(MIN(dai1.absolute_val))
1233 FROM IGS_CA_DA_INST dai1,
1234 IGS_CA_DA_INST dai2,
1235 IGS_CA_DA_INST_PAIR daip,
1236 IGS_CA_INST ci,
1237 IGS_CA_TYPE ct,
1238 IGS_CA_STAT cs
1239 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
1240 ct.S_CAL_CAT = cst_holiday AND
1241 cs.cal_status = ci.CAL_STATUS AND
1242 cs.s_cal_status = cst_active AND
1243 dai1.CAL_TYPE = ci.CAL_TYPE AND
1244 dai1.DT_ALIAS = daip.DT_ALIAS AND
1245 dai1.sequence_number = daip.dai_sequence_number AND
1246 dai1.CAL_TYPE = daip.CAL_TYPE AND
1247 dai1.ci_sequence_number = daip.ci_sequence_number AND
1248 dai2.DT_ALIAS = daip.related_dt_alias AND
1249 dai2.sequence_number = daip.related_dai_sequence_number AND
1250 dai2.CAL_TYPE = daip.related_cal_type AND
1251 dai2.ci_sequence_number = daip.related_ci_sequence_number;
1252
1253 CURSOR c_instbreak (
1254 cp_date_val IGS_CA_DA_INST.absolute_val%TYPE) IS
1255 SELECT 'x'
1256 FROM IGS_CA_TYPE ct
1257 WHERE ct.S_CAL_CAT = cst_holiday AND
1258 EXISTS (SELECT 'x'
1259 FROM IGS_CA_INST ci,
1260 IGS_CA_STAT cs
1261 WHERE ci.CAL_TYPE = ct.CAL_TYPE AND
1262 ci.CAL_STATUS = cs.CAL_STATUS AND
1263 cs.s_cal_status = cst_active AND
1264 EXISTS (SELECT 'x'
1265 FROM IGS_CA_DA_INST dai1,
1266 IGS_CA_DA_INST dai2,
1267 IGS_CA_DA_INST_PAIR daip
1268 WHERE dai1.CAL_TYPE = ct.CAL_TYPE AND
1269 dai1.DT_ALIAS = daip.DT_ALIAS AND
1270 dai1.sequence_number = daip.dai_sequence_number AND
1271 dai1.CAL_TYPE = daip.CAL_TYPE AND
1272 dai1.ci_sequence_number = daip.ci_sequence_number AND
1273 dai2.DT_ALIAS = daip.related_dt_alias AND
1274 dai2.sequence_number = daip.related_dai_sequence_number AND
1275 dai2.CAL_TYPE = daip.related_cal_type AND
1276 dai2.ci_sequence_number = daip.related_ci_sequence_number AND
1277 cp_date_val BETWEEN TRUNC(dai1.absolute_val) AND
1278 TRUNC(dai2.absolute_val)));
1279 BEGIN
1280 OPEN c_m_alias_val2;
1281 FETCH c_m_alias_val2 INTO v_max_alias_val,
1282 v_min_alias_val;
1283 CLOSE c_m_alias_val2;
1284 IF v_max_alias_val IS NULL AND
1285 v_min_alias_val IS NULL THEN
1286 -- No HOLIDAY date alias instances have been defined which can be resolved.
1287 IF p_cnstrt_condition = cst_must_not THEN
1288 -- constraint does not require resolving
1289 RETURN null;
1290 ELSE
1291 -- constraint cannot be resolved
1292 RETURN ('IGS_CA_INSTBREAK_CONST_UNRSLV');
1293 END IF;
1294 ELSE
1295 IF p_cnstrt_condition = cst_must THEN
1296 IF (p_date_val > v_max_alias_val AND
1297 p_cnstrt_resolution >0 ) OR
1298 (p_date_val < v_min_alias_val AND
1299 p_cnstrt_resolution <0 ) THEN
1300 -- constraint cannot be resolved
1301 RETURN ('IGS_CA_INSTBREAK_CONS_UNRSVLD');
1302 END IF;
1303 END IF;
1304 v_tmp_date_val := p_date_val;
1305
1306 LOOP
1307 v_changed := FALSE;
1308 OPEN c_instbreak (v_tmp_date_val);
1309 FETCH c_instbreak INTO v_dummy;
1310 IF c_instbreak%FOUND THEN
1311 CLOSE c_instbreak;
1312 IF p_cnstrt_condition = cst_must_not THEN
1313 --update the date value and test again.
1314 v_tmp_date_val := v_tmp_date_val + p_cnstrt_resolution;
1315
1316 v_changed := TRUE;
1317 END IF;
1318 ELSE -- record not found
1319 CLOSE c_instbreak;
1320 IF p_cnstrt_condition = cst_must THEN
1321 --update the date value and test again.
1322 v_tmp_date_val := v_tmp_date_val + p_cnstrt_resolution;
1323
1324 IF (v_tmp_date_val > v_max_alias_val AND
1325 p_cnstrt_resolution >0 ) OR
1326 (v_tmp_date_val < v_min_alias_val AND
1327 p_cnstrt_resolution <0 ) THEN
1328 -- constraint cannot be resolved
1329 RETURN ('IGS_CA_INSTBREAK_CONS_UNRSVLD');
1330 END IF;
1331 v_changed := TRUE;
1332 END IF;
1333 END IF;
1334 EXIT WHEN v_changed = FALSE;
1335 END LOOP;
1336 -- resolve success or no resolving needed.
1337 p_date_val := v_tmp_date_val;
1338
1339 RETURN null;
1340 END IF;
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 IF c_m_alias_val2%ISOPEN THEN
1344 CLOSE c_m_alias_val2;
1345 END IF;
1346 IF c_instbreak%ISOPEN THEN
1347 CLOSE c_instbreak;
1348 END IF;
1349 RAISE;
1350
1351 END calpl_inst_break_resolve;
1352
1353 BEGIN -- begin of calpl_constraint_resolve
1354
1355 v_msg_name := NULL;
1356
1357 IF p_type = 'FUNCTION' THEN
1358 FOR v_daoc_rec IN c_daoc_func LOOP
1359
1360 p_constraint_count := p_constraint_count + 1;
1361 IF v_daoc_rec.OFFSET_CONS_TYPE_CD IN ( cst_monday,
1362 cst_tuesday,
1363 cst_wednesday,
1364 cst_thursday,
1365 cst_friday,
1366 cst_saturday,
1367 cst_sunday) THEN
1368 IF v_daoc_rec.constraint_condition = cst_must THEN
1369 -- Use an inner loop to check and resolve any clash.
1370 WHILE RTRIM(TO_CHAR(p_date_val,'DAY')) <>
1371 v_daoc_rec.OFFSET_CONS_TYPE_CD LOOP
1372 p_date_val := p_date_val + v_daoc_rec.constraint_resolution;
1373
1374 END LOOP;
1375 ELSE -- NUST NOT
1376 -- Use an inner loop to check and resolve any clash.
1377 WHILE RTRIM(TO_CHAR(p_date_val,'DAY')) =
1378 v_daoc_rec.OFFSET_CONS_TYPE_CD LOOP
1379 p_date_val := p_date_val + v_daoc_rec.constraint_resolution;
1380
1381 END LOOP;
1382 END IF;
1383 ELSIF v_daoc_rec.OFFSET_CONS_TYPE_CD = cst_week_day THEN
1384 IF v_daoc_rec.constraint_condition = cst_must THEN
1385 -- Use an inner loop to check and resolve any clash.
1386 WHILE RTRIM(TO_CHAR(p_date_val,'DAY')) NOT IN (cst_monday,
1387 cst_tuesday,
1388 cst_wednesday,
1389 cst_thursday,
1390 cst_friday) LOOP
1391 p_date_val := p_date_val + v_daoc_rec.constraint_resolution;
1392
1393 END LOOP;
1394 ELSE -- MUST NOT
1395 -- Use an inner loop to check and resolve any clash.
1396 WHILE RTRIM(TO_CHAR(p_date_val,'DAY')) IN ( cst_monday,
1397 cst_tuesday,
1398 cst_wednesday,
1399 cst_thursday,
1400 cst_friday) LOOP
1401 p_date_val := p_date_val + v_daoc_rec.constraint_resolution;
1402
1403 END LOOP;
1404 END IF;
1405 ELSIF v_daoc_rec.OFFSET_CONS_TYPE_CD = cst_holiday THEN
1406 -- If the constraint type is 'HOLIDAY', check that the date does not clash
1407 -- against any date alias instance values in HOLIDAY calendars if the
1408 -- condition is 'MUST NOT' or that it matches a date alias instance value
1409 -- in a HOLIDAY calendar if the condition is 'MUST'.
1410
1411
1412 v_msg_name := calpl_holiday_resolve (
1413 p_date_val,
1414 v_daoc_rec.constraint_condition,
1415 v_daoc_rec.constraint_resolution);
1416
1417
1418 IF v_msg_name IS NOT NULL THEN
1419 p_message_name := v_msg_name;
1420 END IF;
1421 ELSIF v_daoc_rec.OFFSET_CONS_TYPE_CD = cst_inst_break THEN
1422 --If the constraint type is 'INST BREAK', check that the date does not fall
1423 -- between the dates defined by any date alias instance pairs in HOLIDAY
1424 -- calendars if the condition is 'MUST NOT' or that it does if the
1425 -- condition is 'MUST'.
1426 -- Use an inner loop to match the date against all defined DAIP's.
1427 -- Find the start and end dates of any DAI Pair.
1428 v_msg_name := calpl_inst_break_resolve (
1429 p_date_val,
1430 v_daoc_rec.constraint_condition,
1431 v_daoc_rec.constraint_resolution );
1432
1433 IF v_msg_name IS NOT NULL THEN
1434 p_message_name := v_msg_name;
1435 END IF;
1436 END IF;
1437 END LOOP; -- daoc_func loop
1438
1439 ELSE
1440 FOR v_daoc_rec IN c_daoc_adm LOOP
1441
1442 p_constraint_count := p_constraint_count + 1;
1443 IF v_daoc_rec.OFFSET_CONS_TYPE_CD IN ( cst_monday,
1444 cst_tuesday,
1445 cst_wednesday,
1446 cst_thursday,
1447 cst_friday,
1448 cst_saturday,
1449 cst_sunday) THEN
1450 IF v_daoc_rec.constraint_condition = cst_must THEN
1451 -- Use an inner loop to check and resolve any clash.
1452 WHILE RTRIM(TO_CHAR(p_date_val,'DAY')) <>
1453 v_daoc_rec.OFFSET_CONS_TYPE_CD LOOP
1454 p_date_val := p_date_val + v_daoc_rec.constraint_resolution;
1455
1456 END LOOP;
1457 ELSE -- NUST NOT
1458 -- Use an inner loop to check and resolve any clash.
1459 WHILE RTRIM(TO_CHAR(p_date_val,'DAY')) =
1460 v_daoc_rec.OFFSET_CONS_TYPE_CD LOOP
1461 p_date_val := p_date_val + v_daoc_rec.constraint_resolution;
1462
1463 END LOOP;
1464 END IF;
1465 ELSIF v_daoc_rec.OFFSET_CONS_TYPE_CD = cst_week_day THEN
1466 IF v_daoc_rec.constraint_condition = cst_must THEN
1467 -- Use an inner loop to check and resolve any clash.
1468 WHILE RTRIM(TO_CHAR(p_date_val,'DAY')) NOT IN (cst_monday,
1469 cst_tuesday,
1470 cst_wednesday,
1471 cst_thursday,
1472 cst_friday) LOOP
1473 p_date_val := p_date_val + v_daoc_rec.constraint_resolution;
1474
1475 END LOOP;
1476 ELSE -- MUST NOT
1477 -- Use an inner loop to check and resolve any clash.
1478 WHILE RTRIM(TO_CHAR(p_date_val,'DAY')) IN ( cst_monday,
1479 cst_tuesday,
1480 cst_wednesday,
1481 cst_thursday,
1482 cst_friday) LOOP
1483 p_date_val := p_date_val + v_daoc_rec.constraint_resolution;
1484
1485 END LOOP;
1486 END IF;
1487 ELSIF v_daoc_rec.OFFSET_CONS_TYPE_CD = cst_holiday THEN
1488 -- If the constraint type is 'HOLIDAY', check that the date does not clash
1489 -- against any date alias instance values in HOLIDAY calendars if the
1490 -- condition is 'MUST NOT' or that it matches a date alias instance value
1491 -- in a HOLIDAY calendar if the condition is 'MUST'.
1492
1493
1494 v_msg_name := calpl_holiday_resolve (
1495 p_date_val,
1496 v_daoc_rec.constraint_condition,
1497 v_daoc_rec.constraint_resolution);
1498
1499
1500 IF v_msg_name IS NOT NULL THEN
1501 p_message_name := v_msg_name;
1502 END IF;
1503 ELSIF v_daoc_rec.OFFSET_CONS_TYPE_CD = cst_inst_break THEN
1504 --If the constraint type is 'INST BREAK', check that the date does not fall
1505 -- between the dates defined by any date alias instance pairs in HOLIDAY
1506 -- calendars if the condition is 'MUST NOT' or that it does if the
1507 -- condition is 'MUST'.
1508 -- Use an inner loop to match the date against all defined DAIP's.
1509 -- Find the start and end dates of any DAI Pair.
1510 v_msg_name := calpl_inst_break_resolve (
1511 p_date_val,
1512 v_daoc_rec.constraint_condition,
1513 v_daoc_rec.constraint_resolution );
1514
1515 IF v_msg_name IS NOT NULL THEN
1516 p_message_name := v_msg_name;
1517 END IF;
1518 END IF;
1519 END LOOP; -- daoc_adm loop
1520
1521 END IF;
1522
1523
1524 EXCEPTION
1525 WHEN OTHERS THEN
1526 IF c_daoc_func%ISOPEN THEN
1527 CLOSE c_daoc_func;
1528 END IF;
1529
1530 IF c_daoc_func%ISOPEN THEN
1531 CLOSE c_daoc_func;
1532 END IF;
1533
1534 RAISE;
1535
1536 END calpl_constraint_resolve;
1537
1538 FUNCTION check_dl_date( p_uooid IN igs_ps_usec_occurs.uoo_id%TYPE,
1539 p_date IN DATE,
1540 p_formula_method IN igs_en_nsu_dlstp.formula_method%TYPE,
1541 p_last_meeting_date IN DATE
1542 )
1543 RETURN DATE
1544 ------------------------------------------------------------------
1545 --Created by : smanglm ( Oracle IDC)
1546 --Date created: 05-MAY-2001
1547 --
1548 --Purpose: created for checking whther the calculated dedaline is with unit section end date or not
1549 -- Called from procedure recal_dl_date
1550 --
1551 --Known limitations/enhancements and/or remarks:
1552 --
1553 --Change History:
1554 --Who When What
1555 --sarakshi 22-Aug-2005 bug#4113948, Return the last meeting date if p_date is greater than the last meeting date
1556 -------------------------------------------------------------------
1557 IS
1558
1559 l_cal_type igs_ca_inst.cal_type%TYPE;
1560 l_seq_number igs_ca_inst.sequence_number%TYPE;
1561 l_enddate DATE; -- new deadline date
1562
1563 l_return VARCHAR2(1) ; -- decide whether old date exists as deadline or not
1564 p_unit_sec_end_date DATE;
1565
1566 -- Cursor to get the cal type and sequence number for a given uoo_id
1567 CURSOR cur_cal_seq (cp_uoo_id igs_ps_usec_occurs.uoo_id%TYPE) IS
1568 SELECT cal_type,ci_sequence_number
1569 FROM igs_ps_unit_ofr_opt
1570 WHERE uoo_id = cp_uoo_id;
1571
1572 --Cursor to get unit_sec_end_date
1573 CURSOR cur_unit_sec_end_date (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1574 SELECT unit_section_end_date
1575 FROM igs_ps_unit_ofr_opt
1576 WHERE uoo_id = cp_uoo_id;
1577
1578
1579 --Cursor to fetch the Calendar Instance End Date
1580 CURSOR cur_calinst (cp_cal_type igs_ca_inst.cal_type%TYPE,
1581 cp_seq_number igs_ca_inst.sequence_number%TYPE)
1582 IS
1583 SELECT end_dt
1584 FROM igs_ca_inst
1585 WHERE cal_type=cp_cal_type
1586 AND sequence_number=cp_seq_number;
1587
1588
1589 BEGIN
1590 -- Initialization of plsql table
1591 l_return := 'N'; -- decide whether old date exists as deadline or not
1592
1593 -- get the cal type nad sequence number
1594 OPEN cur_cal_seq (p_uooid);
1595 FETCH cur_cal_seq INTO l_cal_type, l_seq_number;
1596 CLOSE cur_cal_seq;
1597
1598 -- get the unit section end date
1599 OPEN cur_unit_sec_end_date(p_uooid) ;
1600 FETCH cur_unit_sec_end_date INTO p_unit_sec_end_date;
1601 CLOSE cur_unit_sec_end_date;
1602 --Depending on the Formula Method select the end date.
1603 --IF Formula Method is Duration Days
1604
1605 l_enddate:=NULL;
1606
1607 IF p_formula_method='D' OR (p_formula_method='M' AND p_last_meeting_date IS NULL )THEN
1608 BEGIN
1609 IF p_unit_sec_end_date IS NULL THEN
1610 --Fetch the Cal Inst End Date
1611 OPEN cur_calinst (l_cal_type, l_seq_number);
1612 FETCH cur_calinst INTO l_enddate;
1613 CLOSE cur_calinst;
1614 ELSE
1615 l_enddate := p_unit_sec_end_date;
1616 END IF;
1617
1618 --If the DeaLine date calculated is > the End date then DeadLine Date is the End date
1619 IF p_date > l_enddate THEN
1620 l_return:='Y';
1621 ELSE
1622 l_return:='N';
1623 END IF;
1624 END;
1625 --If the Formula Method is Meeting Days
1626 ELSIF p_formula_method ='M' THEN
1627
1628 -- Return the last meeting date if p_date is greater than the last meeting date
1629 --Added this as a part of bug#4113948
1630 IF p_date > p_last_meeting_date THEN
1631 l_return:='Y';
1632 l_enddate:= p_last_meeting_date;
1633 END IF;
1634
1635 END IF; -- End of Formula Method Check
1636
1637 -- depending on the value of l_return either p_date will be deadline or l_enddate
1638 IF l_return = 'N' THEN
1639 return p_date;
1640 ELSE
1641 return l_enddate;
1642 END IF;
1643
1644 END check_dl_date;
1645
1646 FUNCTION f_retention_offset_date (
1647 p_n_uoo_id IN NUMBER, -- Unique Identifier for Unit Section
1648 p_c_formula_method IN VARCHAR2, -- Formula Method 'D' -> Duration Days, 'N' -> Meeting Days, 'P' -> Percentage of Duration Days, 'M' -> Percentage of Meeting Days.
1649 p_c_round_method IN VARCHAR2, -- Round Method 'S' -> Standard Round, 'A' -> Always Round
1650 p_c_incl_wkend_duration IN VARCHAR2, -- Include Weekend duration Flag 'Y' -> Duration days is calculated inclusive of weekends otherwise not.
1651 p_n_offset_value IN NUMBER) -- Offset value entered by user, may be days or percentages.
1652 RETURN DATE AS
1653
1654 /***********************************************************************************************
1655 Created By : smvk
1656 Date Created By: 18-Sep-2004
1657 Purpose : Function calculates and retuns Retention Offset Date
1658
1659 Known limitations,enhancements,remarks:
1660 Change History (in reverse chronological order)
1661 Who When What
1662 ********************************************************************************************** */
1663
1664 l_d_us_st_dt igs_ps_unit_ofr_opt_all.unit_section_start_date%TYPE; -- Holds Unit Section Start Date
1665 l_d_end_dt igs_ps_unit_ofr_opt_all.unit_section_end_date%TYPE; -- Holds Effection Unit Section End Date (i.e, if available, unit section end date otherwise teaching calendar end date)
1666
1667 CURSOR c_date(cp_n_uoo_id IN NUMBER) IS
1668 SELECT a.unit_section_start_date,
1669 NVL(a.unit_section_end_date,b.end_dt)
1670 FROM igs_ps_unit_ofr_opt_all a,
1671 igs_ca_inst_all b
1672 WHERE a.uoo_id = cp_n_uoo_id
1673 AND a.cal_type = b.cal_type
1674 AND a.ci_sequence_number = b.sequence_number;
1675
1676 l_c_msg VARCHAR2(30);
1677
1678 BEGIN
1679
1680 --LOGGING THE INPUT PARAMETERS
1681 log_to_fnd('f_retention_offset_date.parameter.p_n_uoo_id',p_n_uoo_id);
1682 log_to_fnd('f_retention_offset_date.parameter.p_c_formula_method',p_c_formula_method);
1683 log_to_fnd('f_retention_offset_date.parameter.p_c_round_method',p_c_round_method);
1684 log_to_fnd('f_retention_offset_date.parameter.p_c_incl_wkend_duration',p_c_incl_wkend_duration);
1685 log_to_fnd('f_retention_offset_date.parameter.p_n_offset_value',p_n_offset_value);
1686
1687 -- Populate the dates into local variable for further calculation
1688 OPEN c_date (p_n_uoo_id);
1689 FETCH c_date INTO l_d_us_st_dt, l_d_end_dt;
1690 CLOSE c_date;
1691
1692 -- Functional Validation : Unit Section Start Date is mandatory for Non Standard Unit Section.
1693 -- Otherwise calculation will not be done.
1694 IF l_d_us_st_dt IS NULL THEN
1695 l_c_msg := 'IGS_EN_OFFSET_DT_NULL';
1696 fnd_message.set_name ('IGS',l_c_msg);
1697 igs_ge_msg_stack.add;
1698 app_exception.raise_exception;
1699 log_to_fnd('f_retention_offset_date.l_d_us_st_dt','NULL');
1700
1701 -- Logging the error message (NEED TO REVISIT HERE).
1702 IF (fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
1703 fnd_log.string( fnd_log.level_exception, 'igs.plsql.igs_ps_gen_004.f_retention_offset_date.l_d_us_st_dt', fnd_message.get);
1704 END IF;
1705
1706 RETURN NULL;
1707 END IF;
1708
1709 log_to_fnd('f_retention_offset_date.l_d_us_st_dt',l_d_us_st_dt);
1710 log_to_fnd('f_retention_offset_date.l_d_end_dt',l_d_end_dt);
1711
1712 -- Check the whether it is duration days or meeting days and delegate the call to corresponding functions.
1713 IF p_c_formula_method IN ('D','P') THEN
1714
1715 RETURN(duration_days( p_n_uoo_id => p_n_uoo_id,
1716 p_d_us_st_dt => l_d_us_st_dt,
1717 p_d_end_dt => l_d_end_dt,
1718 p_c_formula_method => p_c_formula_method,
1719 p_c_round_method => p_c_round_method,
1720 p_c_incl_wkend_duration => p_c_incl_wkend_duration,
1721 p_n_offset_value => p_n_offset_value,
1722 p_c_msg => l_c_msg));
1723
1724 ELSIF p_c_formula_method IN ('N','M') THEN
1725 RETURN(meeting_days( p_n_uoo_id => p_n_uoo_id,
1726 p_d_us_st_dt => l_d_us_st_dt,
1727 p_d_end_dt => l_d_end_dt,
1728 p_c_formula_method => p_c_formula_method,
1729 p_c_round_method => p_c_round_method,
1730 p_c_incl_wkend_duration => p_c_incl_wkend_duration,
1731 p_n_offset_value => p_n_offset_value,
1732 p_c_msg => l_c_msg));
1733 END IF;
1734
1735 END f_retention_offset_date;
1736
1737 FUNCTION duration_days( p_n_uoo_id IN NUMBER, -- Unique Identifier for Unit Section
1738 p_d_us_st_dt IN DATE, -- Unit Section Start Date
1739 p_d_end_dt IN DATE, -- Unit Section End Date
1740 p_c_formula_method IN VARCHAR2, -- Formula Method 'D' -> Duration Days, 'P' -> Percentage of Duration Days
1741 p_c_round_method IN VARCHAR2, -- Round Method 'S' -> Standard Round, 'A' -> Always Round
1742 p_c_incl_wkend_duration IN VARCHAR2, -- Include Weekend duration Flag 'Y' -> Duration days is calculated inclusive of weekends otherwise not.
1743 p_n_offset_value IN NUMBER, -- Offset value entered by user, may be days or percentages.
1744 p_c_msg OUT NOCOPY VARCHAR2) RETURN DATE IS
1745 /***********************************************************************************************
1746 Created By : smvk
1747 Date Created By: 18-Sep-2004
1748 Purpose : Function calculates and retuns Retention Offset Date for formula methods duration days
1749
1750 Known limitations,enhancements,remarks:
1751 Change History (in reverse chronological order)
1752 Who When What
1753 ********************************************************************************************** */
1754
1755 l_n_duration_days NUMBER; -- Holds the number of duration days.
1756 l_n_weekends NUMBER; -- Holds the number of weekends (i.e Saturdays and Sundays).
1757 l_n_holidays NUMBER; -- Holds the number of holidays.
1758 l_n_total_days NUMBER; -- Holds the total number of (duration / meeting) days.
1759 l_n_offset_days NUMBER; -- Holds the offset days.
1760 l_d_init_retention_dt DATE; -- Holds the intial retention date.
1761 -- l_d_retention_dt DATE; -- Holds the final calculated retention date, returned to the form.
1762 l_tab_holidays tab_date_type; -- Holds the date's of Holidays.
1763 l_b_found BOOLEAN; -- Boolean variable holds true/false. contains value true if it finds a hit.
1764
1765 l_n_cons_id IGS_PS_NSUS_RTN.NON_STD_USEC_RTN_ID%TYPE;
1766
1767 FUNCTION is_holiday(p_d_date IN DATE) RETURN BOOLEAN IS
1768 /***********************************************************************************************
1769 Created By : smvk
1770 Date Created By: 18-Sep-2004
1771 Purpose : Function checks whether the input date is holiday or not.
1772 Returns true if holiday is found otherwise false.
1773
1774 Known limitations,enhancements,remarks:
1775 Change History (in reverse chronological order)
1776 Who When What
1777 ********************************************************************************************** */
1778
1779 l_b_found BOOLEAN;
1780
1781 BEGIN
1782 l_b_found := FALSE;
1783 FOR i IN 1..l_tab_holidays.COUNT LOOP
1784 IF l_tab_holidays(i) = p_d_date THEN
1785 l_b_found := TRUE;
1786 EXIT;
1787 END IF;
1788 END LOOP;
1789 RETURN l_b_found;
1790 END is_holiday;
1791
1792 PROCEDURE num_duration_days IS
1793 /***********************************************************************************************
1794 Created By : smvk
1795 Date Created By: 18-Sep-2004
1796 Purpose : Procedure to calculate initial retention date based on formula method "Number of Duration Days"
1797 l_d_init_retention_dt - holds the calculated initial retention date.
1798
1799 Known limitations,enhancements,remarks:
1800 Change History (in reverse chronological order)
1801 Who When What
1802 ********************************************************************************************** */
1803
1804 i NUMBER; -- local iteration variable.
1805 l_n_offset_value NUMBER; -- local variable to hold offet value.
1806 BEGIN
1807
1808 -- CALCULATION OF OFFFSET DAYS
1809 -- For formula method "Number of duration days" is offset value is offset days.
1810 l_n_offset_value := p_n_offset_value;
1811 log_to_fnd('num_duration_days.l_n_offset_days',l_n_offset_value);
1812
1813 -- CALCULATION OF INITIAL OFFSET RETENTION DATE
1814 -- Initialize the offset date as unit section start date
1815 -- Increment the date to number of days provided in offset value
1816 -- Holidays are ignored while incrementing the days.
1817 -- Weekends are ignored while incrementing if the include weekend in duration flag is unchecked.
1818 l_d_init_retention_dt := p_d_us_st_dt;
1819 i := 1;
1820 log_to_fnd('num_duration_days.before_round_up',l_n_offset_value);
1821 round_up(p_c_round_method , l_n_offset_value);
1822 log_to_fnd('num_duration_days.after_round_up',l_n_offset_value);
1823
1824 WHILE (i <= l_n_offset_value) LOOP
1825 l_d_init_retention_dt := l_d_init_retention_dt + 1;
1826 IF NOT (
1827 is_holiday(l_d_init_retention_dt) OR
1828 (p_c_incl_wkend_duration = 'N' AND
1829 TO_CHAR(l_d_init_retention_dt,'D','nls_date_language = american') IN ('1','7')
1830 )
1831 )THEN
1832 i := i +1;
1833 END IF;
1834 END LOOP;
1835 log_to_fnd('num_duration_days.end',l_d_init_retention_dt);
1836 END num_duration_days;
1837
1838 PROCEDURE per_duration_days IS
1839 /***********************************************************************************************
1840 Created By : smvk
1841 Date Created By: 18-Sep-2004
1842 Purpose : Procedure to calculate initial retention date based on formula method "Percent of Duration Days"
1843 l_d_init_retention_dt - holds the calculated initial retention date.
1844
1845 Known limitations,enhancements,remarks:
1846 Change History (in reverse chronological order)
1847 Who When What
1848 ********************************************************************************************** */
1849
1850 BEGIN
1851 -- CALCULATION OF DURATION DAYS: Effective End date - Start Date + 1
1852 l_n_duration_days := (p_d_end_dt - p_d_us_st_dt ) + 1;
1853 log_to_fnd('per_duration_days.l_n_duration_days',l_n_duration_days);
1854
1855
1856 -- CALCULATION OF WEEKENDS:
1857 IF p_c_incl_wkend_duration = 'N' THEN
1858
1859 -- Call the function get_weekends to get the total number of weekends (saturdays and sundays)
1860 l_n_weekends := get_weekends(p_d_us_st_dt,p_d_end_dt);
1861
1862 ELSE
1863
1864 -- Should consider weekends during the total duration days. so initializing it to zero.
1865 l_n_weekends := 0;
1866
1867 END IF;
1868 log_to_fnd('per_duration_days.l_n_weekends',l_n_weekends);
1869
1870
1871 --CALCULATION OF TOTAL NUMBER OF HOLIDAYS.
1872 l_n_holidays := l_tab_holidays.COUNT;
1873 log_to_fnd('per_duration_days.l_n_holidays',l_n_holidays);
1874
1875 -- CALCULATION OF TOTAL DURATION DAYS:
1876 l_n_total_days := l_n_duration_days - (l_n_weekends + l_n_holidays);
1877 log_to_fnd('per_duration_days.l_n_total_days',l_n_total_days);
1878
1879 -- CALCULATION OF OFFSET DAYS:
1880 -- Offset days is offset value percentage of total duration days.
1881 l_n_offset_days := l_n_total_days * p_n_offset_value / 100;
1882 log_to_fnd('per_duration_days.before_round_up',l_n_offset_days);
1883
1884 round_up(p_c_round_method,l_n_offset_days);
1885 log_to_fnd('per_duration_days.after_round_up',l_n_offset_days);
1886
1887 -- Initial Retention date is offset days from unit section start date.
1888 l_d_init_retention_dt := p_d_us_st_dt + l_n_offset_days;
1889 log_to_fnd('per_duration_days.end',l_d_init_retention_dt);
1890
1891 END per_duration_days;
1892
1893 BEGIN -- Begining of duration days functions.
1894 -- initialize the PL/SQL table.
1895 l_tab_holidays.DELETE;
1896
1897 -- CALCULATION OF HOLIDAYS: It containts 2 steps
1898 -- Step 1: Call the procedure populate_holidays to populate holidays dates (Date Alias instances defined for holiday calendar) in the PL/SQL table l_tab_holidays
1899 -- Step 2: Count of dates in holidays PL/SQL table.
1900 populate_holidays(p_d_us_st_dt, p_d_end_dt, p_c_incl_wkend_duration, l_tab_holidays);
1901
1902
1903 -- CALCULATION OF INITIAL RETENTION DATE:
1904 IF p_c_formula_method = 'D' THEN
1905 num_duration_days;
1906 ELSIF p_c_formula_method = 'P'THEN
1907 per_duration_days;
1908 END IF;
1909
1910
1911 -- Initial Retention date should not fall on Institution defined holidays or Weekend(saturday / sunday).
1912 WHILE (l_d_init_retention_dt <= p_d_end_dt) LOOP
1913
1914 IF (TO_CHAR(l_d_init_retention_dt,'D','nls_date_language = american')) NOT IN ('1','7') THEN
1915
1916 l_b_found := FALSE;
1917
1918 FOR i in 1..l_tab_holidays.count LOOP
1919
1920 IF l_tab_holidays(i) = l_d_init_retention_dt THEN
1921 l_b_found := TRUE;
1922 EXIT;
1923 END IF;
1924
1925 END LOOP;
1926
1927 IF NOT l_b_found THEN
1928 EXIT;
1929 END IF;
1930
1931 END IF;
1932
1933 l_d_init_retention_dt := l_d_init_retention_dt + 1;
1934
1935 END LOOP;
1936 log_to_fnd('duration_days.before_constraints',l_d_init_retention_dt);
1937
1938 -- APPLYING CONSTRAINTS
1939 l_n_cons_id := get_inst_constraint_id;
1940 IF l_n_cons_id IS NOT NULL THEN
1941 log_to_fnd('duration_days.l_n_cons_id',l_n_cons_id);
1942 calpl_constraint_resolve ( p_date_val => l_d_init_retention_dt,
1943 p_offset_cnstr_id => l_n_cons_id,
1944 p_type => 'FUNCTION' ,
1945 p_deadline_type => 'R',
1946 p_msg_name => p_c_msg );
1947 END IF;
1948 log_to_fnd('duration_days.after_constraints',l_d_init_retention_dt);
1949
1950 IF p_c_msg IS NULL THEN
1951 -- Final retention date should be less than unit section effective end date.
1952 -- Otherwise return unit section effective end date
1953 IF l_d_init_retention_dt <= p_d_end_dt THEN
1954 RETURN l_d_init_retention_dt;
1955 ELSE
1956 RETURN p_d_end_dt;
1957 END IF;
1958 ELSE
1959 log_to_fnd('duration_days.constraints_msg',p_c_msg);
1960 fnd_message.set_name ('IGS',p_c_msg);
1961 igs_ge_msg_stack.add;
1962 app_exception.raise_exception;
1963 END IF;
1964
1965 END duration_days;
1966
1967 FUNCTION meeting_days( p_n_uoo_id IN NUMBER, -- Unique Identifier for Unit Section
1968 p_d_us_st_dt IN DATE, -- Unit Section Start Date
1969 p_d_end_dt IN DATE, -- Unit Section End Date
1970 p_c_formula_method IN VARCHAR2, -- Formula Method 'N' -> Meeting Days, 'M' -> Percentage of Meeting Days.
1971 p_c_round_method IN VARCHAR2, -- Round Method 'S' -> Standard Round, 'A' -> Always Round
1972 p_c_incl_wkend_duration IN VARCHAR2, -- Include Weekend duration Flag 'Y' -> Duration days is calculated inclusive of weekends otherwise not.
1973 p_n_offset_value IN NUMBER, -- Offset value entered by user, may be days or percentages.
1974 p_c_msg OUT NOCOPY VARCHAR2) RETURN DATE IS
1975
1976 /***********************************************************************************************
1977 Created By : smvk
1978 Date Created By: 18-Sep-2004
1979 Purpose : Procedure to calculate and return retention date based on formula methods "Meeting Days"
1980
1981 Known limitations,enhancements,remarks:
1982 Change History (in reverse chronological order)
1983 Who When What
1984 ********************************************************************************************** */
1985
1986 l_b_uso_found BOOLEAN; -- Unit Section Occurrence is found for meeting days calcualation or not.
1987 l_b_date_found BOOLEAN;
1988 l_c_formula_method VARCHAR2(1); -- Local variable to hold formula method.
1989 l_d_st_dt DATE; -- local date variable for iteration between occurrences effective dates.
1990 l_d_ed_dt DATE; -- local date variable to hold occurrence effective end date.
1991 l_n_tot_meet_days NUMBER; -- local variable to hold total number of meeting days.
1992 l_n_meet_days NUMBER; -- local variable to hold number of meeting days.
1993 l_n_offset_days NUMBER; -- local variable to hold offset days.
1994 l_d_init_retention_dt DATE; -- local variable to hold initial retention date.
1995 -- l_d_retention_dt DATE; -- local variable to hold retention date.
1996 l_n_cons_id IGS_PS_NSUS_RTN.NON_STD_USEC_RTN_ID%TYPE;
1997
1998 CURSOR c_uso (cp_n_uoo_id IN NUMBER) IS
1999 SELECT start_date,end_date,to_be_announced,monday, tuesday,wednesday,thursday,friday,saturday,sunday
2000 FROM igs_ps_usec_occurs
2001 WHERE uoo_id=cp_n_uoo_id
2002 AND NO_SET_DAY_IND = 'N';
2003
2004 l_tab_meeting_days tab_date_type; -- local PL/SQL table to hold meeting dates of unit section
2005 l_tab_holidays tab_date_type; -- local PL/SQL table to hold holiday dates which are also meeting dates of unit section.
2006 l_temp_meeting_days tab_date_type; -- local PL/SQL table to hold meeting dates of unit section
2007
2008 FUNCTION is_exists(p_d_date IN DATE) RETURN BOOLEAN IS
2009 /***********************************************************************************************
2010 Created By : smvk
2011 Date Created By: 18-Sep-2004
2012 Purpose : Function to check whether the meeting date is already existing in the meeting days PL/SQL table.
2013 Returns true if exists otherwise false.
2014
2015 Known limitations,enhancements,remarks:
2016 Change History (in reverse chronological order)
2017 Who When What
2018 ********************************************************************************************** */
2019 BEGIN
2020 FOR i in 1..l_tab_meeting_days.count LOOP
2021 IF l_tab_meeting_days(i) = p_d_date THEN
2022 RETURN TRUE;
2023 END IF;
2024 END LOOP;
2025 RETURN FALSE;
2026 END is_exists;
2027
2028 FUNCTION is_holiday(p_d_date IN DATE) RETURN BOOLEAN IS
2029
2030 BEGIN
2031 FOR I IN 1 .. l_tab_holidays.COUNT LOOP
2032 IF l_tab_holidays.exists(I) AND l_tab_holidays(I) = p_d_date THEN
2033 RETURN TRUE;
2034 END IF;
2035 END LOOP;
2036 RETURN FALSE;
2037 END is_holiday;
2038
2039 PROCEDURE trim_meeting_days IS
2040
2041 BEGIN
2042
2043 l_temp_meeting_days.delete;
2044 FOR I in 1 .. l_tab_meeting_days.COUNT LOOP
2045 IF l_tab_meeting_days.exists(I) AND (NOT is_holiday(l_tab_meeting_days(i))) THEN
2046 l_temp_meeting_days(l_temp_meeting_days.count + 1) := l_tab_meeting_days(i);
2047 END IF;
2048 END LOOP;
2049 END trim_meeting_days;
2050
2051 PROCEDURE add_meeting_day (p_d_date IN DATE) IS
2052 /***********************************************************************************************
2053 Created By : smvk
2054 Date Created By: 18-Sep-2004
2055 Purpose : Procedure adds the date as a meeting day, if not already exist in meeting days PL/SQL Table.
2056
2057 Known limitations,enhancements,remarks:
2058 Change History (in reverse chronological order)
2059 Who When What
2060 ********************************************************************************************** */
2061
2062 BEGIN
2063 IF NOT is_exists(p_d_date) THEN
2064 l_tab_meeting_days (l_tab_meeting_days.count + 1) := p_d_date;
2065 log_to_fnd('add_meeting_day.l_tab_meeting_days (' ||l_tab_meeting_days.count || ')',l_tab_meeting_days(l_tab_meeting_days.count));
2066 END IF;
2067 END add_meeting_day;
2068
2069 PROCEDURE trim_holidays IS
2070 /***********************************************************************************************
2071 Created By : smvk
2072 Date Created By: 18-Sep-2004
2073 Purpose : Procedure removes the holidays which are not meeting days for this unit section.
2074
2075 Known limitations,enhancements,remarks:
2076 Change History (in reverse chronological order)
2077 Who When What
2078 ********************************************************************************************** */
2079 BEGIN
2080 FOR i IN 1 .. l_tab_holidays.COUNT LOOP
2081 IF NOT is_exists(l_tab_holidays(i)) THEN
2082 l_tab_holidays.delete(i);
2083 END IF;
2084 END LOOP;
2085
2086 END trim_holidays;
2087
2088 BEGIN -- Begining of the function meeting_days
2089 l_b_uso_found := FALSE;
2090
2091 -- Iterate through the occurrences of unit section and get the meeting dates.
2092 FOR rec_uso IN c_uso(p_n_uoo_id) LOOP
2093 l_b_uso_found := TRUE; -- Atleast one Non 'no set day' occurrence exists for unit section.
2094
2095 log_to_fnd('meeting_days.rec_uso.to_be_announced',rec_uso.to_be_announced);
2096 -- Check whether the occurrence is to be announced occurrence
2097 IF rec_uso.to_be_announced = 'Y' THEN
2098 -- Calculation for to be announced unit section occurrence
2099 l_d_st_dt := NVL(rec_uso.start_date,p_d_us_st_dt);
2100 l_d_ed_dt := NVL(rec_uso.end_date,p_d_end_dt);
2101 log_to_fnd('meeting_days.tba.l_d_st_dt',l_d_st_dt);
2102 log_to_fnd('meeting_days.tba.l_d_ed_dt',l_d_ed_dt);
2103 WHILE (l_d_st_dt <= l_d_ed_dt ) LOOP
2104 IF (TO_CHAR(l_d_st_dt,'D','nls_date_language = american')) NOT IN ('1','7') THEN
2105 add_meeting_day(l_d_st_dt);
2106 END IF;
2107 l_d_st_dt := l_d_st_dt + 1;
2108 END LOOP;
2109
2110 ELSE
2111
2112 -- Calculation for normal unit section occurrence.
2113 l_d_st_dt := NVL(rec_uso.start_date,p_d_us_st_dt);
2114 l_d_ed_dt := NVL(rec_uso.end_date,p_d_end_dt);
2115 log_to_fnd('meeting_days.uso.l_d_st_dt',l_d_st_dt);
2116 log_to_fnd('meeting_days.uso.l_d_ed_dt',l_d_ed_dt);
2117 WHILE (l_d_st_dt <= l_d_ed_dt ) LOOP
2118 IF(TO_CHAR(l_d_st_dt,'D','nls_date_language = american')) = '1' AND rec_uso.sunday = 'Y' THEN
2119 add_meeting_day(l_d_st_dt);
2120 ELSIF (TO_CHAR(l_d_st_dt,'D','nls_date_language = american')) = '2' AND rec_uso.monday = 'Y' THEN
2121 add_meeting_day(l_d_st_dt);
2122 ELSIF (TO_CHAR(l_d_st_dt,'D','nls_date_language = american')) = '3' AND rec_uso.tuesday = 'Y' THEN
2123 add_meeting_day(l_d_st_dt);
2124 ELSIF (TO_CHAR(l_d_st_dt,'D','nls_date_language = american')) = '4' AND rec_uso.wednesday = 'Y' THEN
2125 add_meeting_day(l_d_st_dt);
2126 ELSIF (TO_CHAR(l_d_st_dt,'D','nls_date_language = american')) = '5' AND rec_uso.thursday = 'Y' THEN
2127 add_meeting_day(l_d_st_dt);
2128 ELSIF (TO_CHAR(l_d_st_dt,'D','nls_date_language = american')) = '6' AND rec_uso.friday = 'Y' THEN
2129 add_meeting_day(l_d_st_dt);
2130 ELSIF (TO_CHAR(l_d_st_dt,'D','nls_date_language = american')) = '7' AND rec_uso.saturday = 'Y' THEN
2131 add_meeting_day(l_d_st_dt);
2132 END IF;
2133 l_d_st_dt := l_d_st_dt + 1;
2134 END LOOP;
2135
2136 END IF;
2137
2138 END LOOP;
2139
2140 IF l_b_uso_found THEN
2141 -- Flow of execution when the unit section has occurrences other than 'No Set Day' Occurrence.
2142 populate_holidays ( p_d_start_dt => p_d_us_st_dt,
2143 p_d_end_dt => p_d_end_dt,
2144 p_c_incl_weekends => 'Y',
2145 p_tab_holiday => l_tab_holidays
2146 );
2147 log_to_fnd('meeting_days.l_tab_holidays.count',l_tab_holidays.count);
2148 IF l_tab_holidays.count > 0 THEN
2149 -- Trim the holidays PL/SQL table to hold only holidays which are meeting days.
2150 trim_holidays;
2151 END IF;
2152
2153 log_to_fnd('meeting_days.l_tab_meeting_days.COUNT',l_tab_meeting_days.COUNT);
2154 log_to_fnd('meeting_days.l_tab_holidays.COUNT',l_tab_holidays.COUNT);
2155
2156 l_n_meet_days := l_tab_meeting_days.COUNT - l_tab_holidays.COUNT;
2157
2158 log_to_fnd('meeting_days.l_n_meet_days',l_n_meet_days);
2159
2160 IF p_c_formula_method = 'N' THEN
2161 l_n_offset_days := p_n_offset_value;
2162 ELSIF p_c_formula_method = 'M' THEN
2163 l_n_offset_days := l_n_meet_days * p_n_offset_value /100;
2164 END IF;
2165
2166 log_to_fnd('meeting_days.before_round',l_n_offset_days);
2167 round_up(p_c_round_method,l_n_offset_days);
2168 log_to_fnd('meeting_days.after_round',l_n_offset_days);
2169
2170 -- Sort the meeting dates in the ascending order;
2171 sort_date_array(l_tab_meeting_days);
2172 trim_meeting_days;
2173
2174 IF l_n_offset_days = 0 OR l_temp_meeting_days.count = 0 THEN
2175 l_d_init_retention_dt := p_d_us_st_dt;
2176 ELSIF l_n_offset_days >= l_temp_meeting_days.count THEN
2177 l_d_init_retention_dt := l_temp_meeting_days(l_temp_meeting_days.count);
2178 ELSE
2179 FOR k in l_n_offset_days..l_temp_meeting_days.count LOOP
2180 l_b_date_found := FALSE;
2181 FOR L in 1..l_tab_holidays.count LOOP
2182 IF l_tab_holidays.exists(L) AND l_tab_holidays(L) = l_temp_meeting_days(K) THEN
2183 l_b_date_found := TRUE;
2184 EXIT;
2185 END IF;
2186 END LOOP;
2187 IF NOT l_b_date_found THEN
2188 l_d_init_retention_dt := l_temp_meeting_days(K);
2189 EXIT;
2190 END IF;
2191 END LOOP;
2192 END IF;
2193
2194 log_to_fnd('meeting_days.l_d_init_retention_dt',l_d_init_retention_dt);
2195
2196
2197 -- APPLYING CONSTRAINTS
2198 l_n_cons_id := get_inst_constraint_id;
2199 IF l_n_cons_id IS NOT NULL THEN
2200 log_to_fnd('meeting_days.l_n_cons_id ',l_n_cons_id );
2201 calpl_constraint_resolve ( p_date_val => l_d_init_retention_dt,
2202 p_offset_cnstr_id => l_n_cons_id,
2203 p_type => 'FUNCTION' ,
2204 p_deadline_type => 'R',
2205 p_msg_name => p_c_msg );
2206 END IF;
2207
2208 log_to_fnd('meeting_days.after_constraints ',l_d_init_retention_dt );
2209 IF p_c_msg IS NULL THEN
2210 -- Final retention date should be less than unit section effective end date.
2211 -- Otherwise return unit section effective end date
2212 IF l_temp_meeting_days.count = 0 OR l_d_init_retention_dt <= l_temp_meeting_days(l_temp_meeting_days.count) THEN
2213 RETURN l_d_init_retention_dt;
2214 ELSE
2215 RETURN l_temp_meeting_days(l_temp_meeting_days.count);
2216 END IF;
2217 ELSE
2218 log_to_fnd('meeting_days.constraints_msg ',p_c_msg );
2219 -- Error occurred while applying constraints.
2220 fnd_message.set_name ('IGS',p_c_msg);
2221 igs_ge_msg_stack.add;
2222 app_exception.raise_exception;
2223 END IF;
2224
2225 ELSE
2226
2227 -- This particular piece of code will be executed in the following scenarios only
2228 -- 1. Unit Section has only no set day occurrences.
2229 -- 2. Unit Section has no occurrences.
2230 IF p_c_formula_method = 'N' THEN
2231 l_c_formula_method := 'D';
2232 ELSIF p_c_formula_method = 'M' THEN
2233 l_c_formula_method := 'P';
2234 END IF;
2235
2236 return ( duration_days(p_n_uoo_id,
2237 p_d_us_st_dt,
2238 p_d_end_dt,
2239 l_c_formula_method,
2240 p_c_round_method,
2241 'Y', -- Always include weekends in duration days, as meeting dates are counted if the day is on weekend.
2242 p_n_offset_value,
2243 p_c_msg ));
2244 END IF;
2245 END meeting_days;
2246
2247 FUNCTION get_weekends ( p_d_start_dt IN DATE,
2248 p_d_end_dt IN DATE
2249 ) RETURN NUMBER IS
2250 /***********************************************************************************************
2251 Created By : smvk
2252 Date Created By: 18-Sep-2004
2253 Purpose : Funtion retuns number of weekends (Saturday and Sunday) in the given period (p_d_start_dt, p_d_end_dt).
2254
2255 Known limitations,enhancements,remarks:
2256 Change History (in reverse chronological order)
2257 Who When What
2258 ********************************************************************************************** */
2259
2260 l_d_date DATE;
2261 l_n_weekend_count NUMBER;
2262 BEGIN
2263 l_n_weekend_count := 0;
2264 l_d_date := p_d_start_dt;
2265
2266 WHILE (l_d_date <=p_d_end_dt) LOOP
2267
2268 IF TO_CHAR(l_d_date,'D','nls_date_language = american') IN ('1','7') THEN
2269 l_n_weekend_count := l_n_weekend_count + 1;
2270 END IF;
2271
2272 l_d_date := l_d_date + 1;
2273
2274 END LOOP;
2275
2276 RETURN l_n_weekend_count;
2277
2278 END get_weekends;
2279
2280 PROCEDURE populate_holidays ( p_d_start_dt IN DATE,
2281 p_d_end_dt IN DATE,
2282 p_c_incl_weekends IN VARCHAR2,
2283 p_tab_holiday IN OUT NOCOPY tab_date_type
2284 ) IS
2285
2286 /***********************************************************************************************
2287 Created By : smvk
2288 Date Created By: 18-Sep-2004
2289 Purpose : Procedure to populate holiday dates in PL/SQL table p_tab_holiday for the given period (p_d_start_dt, p_d_end_dt).
2290 If the holiday is on saturday or sunday and include weekends flag is checked ('Y') then add them also in p_tab_holiday, Otherwise not.
2291
2292 Known limitations,enhancements,remarks:
2293 Change History (in reverse chronological order)
2294 Who When What
2295 ********************************************************************************************** */
2296
2297 CURSOR c_cal_inst (cp_d_start_dt IN igs_ca_inst_all.start_dt%TYPE,
2298 cp_d_end_dt IN igs_ca_inst_all.end_dt%TYPE) IS
2299 SELECT DISTINCT ai.absolute_val
2300 FROM igs_ca_da_inst ai,
2301 igs_ca_inst_all ci,
2302 igs_ca_type ct,
2303 igs_ca_stat cs
2304 WHERE ai.cal_type = ci.cal_type
2305 AND ai.ci_sequence_number = ci.sequence_number
2306 AND ci.cal_type = ct.cal_type
2307 AND ct.s_cal_cat = 'HOLIDAY'
2308 AND ci.CAL_STATUS = cs.CAL_STATUS
2309 AND cs.S_CAL_STATUS = 'ACTIVE'
2310 AND ct.closed_ind = 'N'
2311 AND cs.closed_ind = 'N'
2312 AND ci.start_dt < cp_d_end_dt
2313 AND ci.end_dt > cp_d_start_dt
2314 AND (ai.absolute_val between cp_d_start_dt AND cp_d_end_dt);
2315
2316 l_n_cnt NUMBER;
2317
2318 BEGIN
2319
2320 -- Set the initial counter as zero
2321 l_n_cnt := 0;
2322 p_tab_holiday.delete;
2323 -- For all the holiday calendar instance between given effective dates
2324 FOR rec_cal_inst IN c_cal_inst(p_d_start_dt, p_d_end_dt) LOOP
2325 log_to_fnd('populate_holidays.rec_cal_inst.absolute_val',rec_cal_inst.absolute_val);
2326 -- Add the holiday date into holidays table unless
2327 -- Holiday is on weekend (i.e saturday or sunday) and the weekends are not included duration days calculation
2328 IF NOT (TO_CHAR(rec_cal_inst.absolute_val, 'D','nls_date_language = american') IN ('1','7') AND p_c_incl_weekends = 'N') THEN
2329 l_n_cnt := l_n_cnt + 1;
2330 p_tab_holiday(l_n_cnt) := rec_cal_inst.absolute_val;
2331 log_to_fnd('populate_holidays.p_tab_holiday(' ||l_n_cnt || ')',p_tab_holiday(l_n_cnt));
2332 END IF;
2333
2334 END LOOP;
2335
2336 END populate_holidays;
2337
2338 -- Procedure to sort the date array.
2339 PROCEDURE sort_date_array (p_tab_array IN OUT NOCOPY tab_date_type) IS
2340 /***********************************************************************************************
2341 Created By : smvk
2342 Date Created By: 18-Sep-2004
2343 Purpose : Procedure to sort date values in the array.
2344
2345 Known limitations,enhancements,remarks:
2346 Change History (in reverse chronological order)
2347 Who When What
2348 ********************************************************************************************** */
2349
2350 l_d_temp DATE; -- temporary varible to hold date during swapping.
2351
2352 BEGIN
2353
2354 FOR i IN 1..p_tab_array.COUNT -1 LOOP
2355 FOR j in i+1 ..p_tab_array.COUNT LOOP
2356 IF p_tab_array(i) > p_tab_array(j) THEN
2357 l_d_temp := p_tab_array(i);
2358 p_tab_array(i) := p_tab_array(j);
2359 p_tab_array(j) := l_d_temp;
2360 END IF;
2361 END LOOP;
2362 END LOOP;
2363
2364 END sort_date_array;
2365
2366 PROCEDURE round_up( p_c_round_method IN VARCHAR2,
2367 p_n_value IN OUT NOCOPY NUMBER) IS
2368
2369 /***********************************************************************************************
2370 Created By : smvk
2371 Date Created By: 18-Sep-2004
2372 Purpose : Procedure to round up the value (p_n_value) based on rounding method (p_c_round_method).
2373
2374 Known limitations,enhancements,remarks:
2375 Change History (in reverse chronological order)
2376 Who When What
2377 ********************************************************************************************** */
2378
2379 BEGIN
2380
2381 -- if the round method is Standard then use normal rounding (ie. 4.2 = 4, 4.5 = 5, 4.6 = 5)
2382 IF p_c_round_method = 'S' THEN
2383 p_n_value := ROUND(p_n_value);
2384 ELSIF p_c_round_method ='A' THEN
2385 -- if the round method is Always Round up then use ceil value(ie. 4.2 = 5, 4.5 = 5, 4.6 = 5)
2386 p_n_value := CEIL(p_n_value);
2387 END IF;
2388
2389 END round_up;
2390
2391 FUNCTION get_inst_constraint_id RETURN NUMBER IS
2392 CURSOR c_call_cnstr IS
2393 SELECT non_std_usec_rtn_id
2394 FROM igs_ps_nsus_rtn
2395 WHERE definition_code = 'INSTITUTION'
2396 AND ROWNUM <2 ;
2397
2398 l_n_cons_id IGS_PS_NSUS_RTN.NON_STD_USEC_RTN_ID%TYPE;
2399
2400 BEGIN
2401 OPEN c_call_cnstr;
2402 FETCH c_call_cnstr INTO l_n_cons_id ;
2403 CLOSE c_call_cnstr;
2404 RETURN l_n_cons_id;
2405 END get_inst_constraint_id;
2406
2407 END IGS_PS_GEN_004;
2408
[Home] [Help]