1 PACKAGE BODY PA_CALENDAR_UTILS as
2 /* $Header: PARGCALB.pls 120.6 2011/10/18 22:39:21 skkoppul ship $ */
3
4 l_empty_tab_record EXCEPTION; -- Variable to raise the exception if
5 -- the passing table of records is empty
6
7 -- This procedure gets the calendar shift on the basis of the calendar id and store its work
8 -- pattern in the calendar type table of records.
9 -- Input parameters
10 -- Parameters Type Required Description
11 -- P_Calendar_Id NUMBER YES Id for that calendar to which you want to have schedule
12 -- Out parameters
13 -- X_Cal_Record_Tab CALENDARTABTYP YES It stores shift assign to the calendar
14 --
15
16 /* Added nocopy for this variable for bug#2674619 */
17
18 PROCEDURE get_calendar_shifts ( p_calendar_id IN NUMBER,
19 x_cal_record_tab OUT NOCOPY PA_SCHEDULE_GLOB.CalendarTabTyp, x_return_status OUT VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
21 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
22 IS
23 l_t_shift_id JTF_CAL_SHIFT_ASSIGN.shift_id%TYPE;-- assigning the variable to store the shift id
24 -- for getting the shifts
25 l_I NUMBER;
26
27 -- This cursor will select the shifts assign to the calendar and the effective period
28 CURSOR C1 IS SELECT shift_sequence_number,shift_id,(shift_end_date - shift_start_date ) duration
29 FROM JTF_CAL_SHIFT_ASSIGN
30 WHERE calendar_id = p_calendar_id;
31
32 -- This cursor will select the work patern of the assigning shift to the calendar
33 -- Bug 2181241: Modified the rounding to be two decimals.
34 CURSOR C2 IS SELECT unit_of_time_value,round(SUM(end_time - begin_time)*24, 2) day_hours
35 FROM JTF_CAL_SHIFT_CONSTRUCTS
36 WHERE shift_id = l_t_shift_id
37 GROUP BY unit_of_time_value;
38
39 BEGIN
40 l_I := 1;
41 -- Storing the status for error handling
42 x_return_status := FND_API.G_RET_STS_SUCCESS;
43
44 -- Setup the cursor fetch loop
45 FOR v_c1 IN C1 LOOP
46 x_cal_record_tab(l_I).seq_num := v_c1.shift_sequence_number;
47 x_cal_record_tab(l_I).duration := v_c1.duration;
48 l_t_shift_id := v_c1.shift_id;
49
50 -- Inilizing the calendar record with zero
51 x_cal_record_tab(l_I).Monday_hours := 0.00;
52 x_cal_record_tab(l_I).Tuesday_hours := 0.00;
53 x_cal_record_tab(l_I).Wednesday_hours := 0.00;
54 x_cal_record_tab(l_I).Thursday_hours := 0.00;
55 x_cal_record_tab(l_I).Friday_hours := 0.00;
56 x_cal_record_tab(l_I).Saturday_hours := 0.00;
57 x_cal_record_tab(l_I).Sunday_hours := 0.00;
58
59 -- Setup the cursor fetch loop
60 FOR v_c2 IN C2 LOOP
61 -- if the time is monday ,tuesday,wednaesday,thursday,friday,saturday and sunday then store its no of hours
62 IF (v_c2.unit_of_time_value = '2') THEN
63 x_cal_record_tab(l_I).Monday_hours :=v_c2.day_hours;
64 ELSIF (v_c2.unit_of_time_value = '3' ) THEN
65 x_cal_record_tab(l_I).Tuesday_hours := v_c2.day_hours;
66 ELSIF (v_c2.unit_of_time_value = '4' ) THEN
67 x_cal_record_tab(l_I).Wednesday_hours :=v_c2.day_hours;
68 ELSIF (v_c2.unit_of_time_value = '5' ) THEN
69 x_cal_record_tab(l_I).Thursday_hours := v_c2.day_hours;
70 ELSIF (v_c2.unit_of_time_value = '6' ) THEN
71 x_cal_record_tab(l_I).Friday_hours := v_c2.day_hours;
72 ELSIF (v_c2.unit_of_time_value ='7' ) THEN
73 x_cal_record_tab(l_I).Saturday_hours := v_c2.day_hours;
74 ELSIF (v_c2.unit_of_time_value = '1' ) THEN
75 x_cal_record_tab(l_I).Sunday_hours := v_c2.day_hours;
76 END IF;
77 END LOOP;
78
79 l_I := l_I + 1;
80 END LOOP;
81
82 -- if the cursor does not have any records i.e. no shift is assign to
83 -- the calendar then default work pattern is created
84 IF (x_cal_record_tab.count = 0 ) THEN
85 x_cal_record_tab(1).seq_num := 1;
86 x_cal_record_tab(1).duration := 0;
87 x_cal_record_tab(1).Monday_hours := 0.00;
88 x_cal_record_tab(1).Tuesday_hours := 0.00;
89 x_cal_record_tab(1).Wednesday_hours := 0.00;
90 x_cal_record_tab(1).Thursday_hours := 0.00;
91 x_cal_record_tab(1).Friday_hours := 0.00;
92 x_cal_record_tab(1).Saturday_hours := 0.00;
93 x_cal_record_tab(1).Sunday_hours := 0.00;
94 END IF;
95
96 EXCEPTION
97 WHEN OTHERS THEN
98 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
99 x_msg_count := 1 ; -- 4537865
100 x_msg_data := SUBSTRB(SQLERRM,1,240); -- 4537865
101 FND_MSG_PUB.add_exc_msg(p_pkg_name =>'PA_CALENDAR_UTILS',
102 p_procedure_name => 'get_calendar_shifts');
103 raise;
104 END get_calendar_shifts;
105
106 -- This procedure gets the exception assign to the calendar
107 -- Input parameters
108 -- Parameters Type Required Description
109 -- P_Calendar_Id NUMBER YES Id for that calendar to which you want to have schedule
110 -- Out parameters
111 -- X_Cal_Except_Record_Tab CALEXCEPTIONTABTYP YES It stores the exception assign to the calendar
112 --
113
114 /* Added nocopy for this variable for bug#2674619 */
115 PROCEDURE get_calendar_except ( p_calendar_id IN NUMBER,
116 x_cal_except_record_tab OUT NOCOPY PA_SCHEDULE_GLOB.CalExceptionTabTyp,
117 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
118 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
119 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
120 IS
121 l_I NUMBER;
122
123 -- This cursor will select the exceptions assign to the pased calendar
124 /* Added trunc for calexp.start_Date_time bug 4176843 */
125 CURSOR C1 IS SELECT calexp.exception_id, trunc(calexp.start_date_time) start_date_time,
126 trunc(calexp.end_date_time) end_date_time,calexp.exception_category
127 FROM JTF_CAL_EXCEPTIONS_B CALEXP, JTF_CAL_EXCEPTION_ASSIGN CALASN
128 WHERE calasn.calendar_id = p_calendar_id
129 AND calasn.exception_id = calexp.exception_id;
130 BEGIN
131 l_I := 1;
132
133 -- Storing the status for error handling
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135
136 -- Setup the cursor fetch loop
137 FOR v_c1 IN C1 LOOP
138 x_cal_except_record_tab(l_I).exception_id := v_c1.exception_id;
139 x_cal_except_record_tab(l_I).except_start_date := v_c1.start_date_time;
140 x_cal_except_record_tab(l_I).except_end_date := v_c1.end_date_time; --Added For Bug 4870111
141 x_cal_except_record_tab(l_I).exception_category := v_c1.exception_category;
142
143 l_I := l_I + 1;
144 END LOOP;
145
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150 x_msg_count := 1 ; -- 4537865
151 x_msg_data := SUBSTRB(SQLERRM,1,240); -- 4537865
152 FND_MSG_PUB.add_exc_msg(p_pkg_name =>'PA_CALENDAR_UTILS',
153 p_procedure_name => 'get_calendar_except');
154 raise;
155
156 END get_calendar_except;
157
158 -- This procedure will generate the calendar schedule on the basis of shifts assign to the calendar
159 -- Input parameters
160 -- Parameters Type Required Description
161 -- P_Calendar_Id NUMBER YES Id for that calendar to which you want to have schedule
162 -- P_Cal_record_Tab CALENDARTABTYP YES This variable having the shift assign to the calendar
163 -- Out parameters
164 -- X_Sch_Record_Tab SCHEDULETABTYP YES It stores the generated schedule of the calendar
165 --
166
167 /* Added nocopy for this variable for bug#2674619 */
168 PROCEDURE gen_calendar_sch ( p_calendar_id IN NUMBER,
169 p_cal_record_tab IN PA_SCHEDULE_GLOB.CalendarTabTyp,
170 x_sch_record_tab OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
171 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
172 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
173 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
174 IS
175 l_I NUMBER;
176 l_J NUMBER;
177 l_t_start_date DATE; -- to store the active date of the calendar
178 l_t_end_date DATE; -- to store the end date of the calendar
179 l_temp_start_date DATE; -- temporary variable to store the start date
180 l_temp_end_date DATE; -- Temporary variable to store the end date
181 l_shift_done BOOLEAN; -- variable to check if the shift is generated or not
182
183
184 BEGIN
185 l_J := 1;
186
187 -- Storing the status for error handling
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189
190 -- if the passing calendar table type record does not have any record
191 IF (p_cal_record_tab.count = 0) THEN
192 Raise l_empty_tab_record;
193 ELSE
194 l_I := p_cal_record_tab.first;
195 END IF;
196
197 -- Taking the active date of the calendar if the ending date of the calendar is not
198 -- defined then taking the specified date
199 SELECT start_date_active ,NVL(end_date_active,TO_DATE('01/01/2050','DD/MM/YYYY'))
200 INTO l_t_start_date,l_t_end_date
201 FROM JTF_CALENDARS_B
202 WHERE calendar_id = p_calendar_id;
203
204 -- if only one shift is assign to the calendar
205 IF (p_cal_record_tab.count = 1 ) THEN
206 x_sch_record_tab(1).start_date := l_t_start_date;
207 x_sch_record_tab(1).end_date := l_t_end_date;
208 x_sch_record_tab(1).Monday_hours := p_cal_record_tab(l_I).Monday_hours;
209 x_sch_record_tab(1).Tuesday_hours := p_cal_record_tab(l_I).Tuesday_hours;
210 x_sch_record_tab(1).Wednesday_hours := p_cal_record_tab(l_I).Wednesday_hours;
211 x_sch_record_tab(1).Thursday_hours := p_cal_record_tab(l_I).Thursday_hours;
212 x_sch_record_tab(1).Friday_hours := p_cal_record_tab(l_I).Friday_hours;
213 x_sch_record_tab(1).Saturday_hours := p_cal_record_tab(l_I).Saturday_hours;
214 x_sch_record_tab(1).Sunday_hours := p_cal_record_tab(l_I).Sunday_hours;
215 x_sch_record_tab(1).calendar_id := p_calendar_id;
216 x_sch_record_tab(1).assignment_id := NULL;
217 x_sch_record_tab(1).project_id := NULL;
218 x_sch_record_tab(1).assignment_status_code := NULL ;
219 x_sch_record_tab(1).schedule_type_code := 'CALENDAR' ;
220
221 RETURN; /* Schedule is done */
222 END IF;
223
224 -- if more than one shift is assign and duration of the last shift is less than the calendar end date then the
225 -- first shift will again assign to the calendar till its duration and then other shift till the calendar end date
226 l_temp_start_date := l_t_start_date;
227 l_shift_done := FALSE;
228
229 LOOP
230 l_temp_end_date := l_temp_start_date + p_cal_record_tab(l_I).duration;
231
232 -- if the shift end date is greater than the calendar end date then shift is done
233 IF (l_temp_end_date >= l_t_end_date ) THEN
234 l_temp_end_date := l_t_end_date;
235 l_shift_done := TRUE;
236 END IF;
237 -- storing the schedule with ythe given work pattern
238 x_sch_record_tab(l_J).start_date := l_temp_start_date;
239 x_sch_record_tab(l_J).end_date := l_temp_end_date;
240 x_sch_record_tab(l_J).Monday_hours := p_cal_record_tab(l_I).Monday_hours;
241 x_sch_record_tab(l_J).Tuesday_hours := p_cal_record_tab(l_I).Tuesday_hours;
242 x_sch_record_tab(l_J).Wednesday_hours := p_cal_record_tab(l_I).Wednesday_hours;
243 x_sch_record_tab(l_J).Thursday_hours := p_cal_record_tab(l_I).Thursday_hours;
244 x_sch_record_tab(l_J).Friday_hours := p_cal_record_tab(l_I).Friday_hours;
245 x_sch_record_tab(l_J).Saturday_hours := p_cal_record_tab(l_I).Saturday_hours;
246 x_sch_record_tab(l_J).Sunday_hours := p_cal_record_tab(l_I).Sunday_hours;
247 x_sch_record_tab(l_J).calendar_id := p_calendar_id;
248 x_sch_record_tab(l_J).assignment_id := NULL;
249 x_sch_record_tab(l_J).project_id := NULL;
250 x_sch_record_tab(l_J).assignment_status_code := NULL ;
251 x_sch_record_tab(l_J).schedule_type_code := 'CALENDAR' ;
252
253 IF (l_shift_done) THEN
254 EXIT;
255 END IF;
256 -- incrementing for the next start date for the new schedule
257 l_temp_start_date := l_temp_end_date + 1;
258 -- if the last shift has come and calendar end date is not reached then it will assign the work pattern
259 -- first shift till the end os its duration then other it is periodicaly process
260 IF ( l_I = p_cal_record_tab.last ) THEN
261 l_I := p_cal_record_tab.first;
262 ELSE
263 l_I := p_cal_record_tab.next(l_I);
264 END IF;
265
266 l_J := l_J + 1;
267 END LOOP;
268
269 EXCEPTION
270 WHEN l_empty_tab_record THEN -- the calendar table of record does not have any record
271 x_return_status := FND_API.G_RET_STS_SUCCESS;
272 NULL;
273 WHEN NO_DATA_FOUND THEN -- if the passed calendar does not exist in the table
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 x_msg_data := 'Invalid Calendar Id '||p_calendar_id;
276 NULL;
277 WHEN OTHERS THEN
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 x_msg_count := 1 ; -- 4537865
280 x_msg_data := SUBSTRB(SQLERRM,1,240); -- 4537865
281 FND_MSG_PUB.add_exc_msg(p_pkg_name =>'PA_CALENDAR_UTILS',
282 p_procedure_name => 'gen_calendar_sch');
283 raise;
284
285 END gen_calendar_sch;
286
287 -- This procedure will apply the exceptions to the generated schedule of the
288 -- calendar and then generate the new schedule
289 -- Input parameters
290 -- Parameters Type Required Description
291 -- P_Calendar_Id NUMBER YES Id for that calendar to which you want to have schedule
292 -- P_Cal_Except_Record_tab CALEXCEPTIONTABTYP YES This variable having the exception assign to the calendar
293 -- P_Sch_Record_Tab SCHEDULETABTYP YES It contains the schedule for that calendar
294 -- In Out parameters
295 -- X_Sch_Record_Tab SCHEDULETABTYP YES It stores the generated schedule after applying
296 -- the exception to the calendar
297 --
298
299 /* Added nocopy for this variable for bug#2674619 */
300 PROCEDURE apply_calendar_except ( p_calendar_id IN NUMBER,
301 p_cal_except_record_tab IN PA_SCHEDULE_GLOB.CalExceptionTabTyp,
302 p_sch_record_tab IN PA_SCHEDULE_GLOB.ScheduleTabTyp,
303 x_sch_record_tab IN OUT NOCOPY PA_SCHEDULE_GLOB.ScheduleTabTyp,
304 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
305 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
306 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
307 IS
308
309 l_temp_exp_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp; -- temporary variable
310 l_temp_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp; -- temporary variable
311 l_out_sch_record_tab PA_SCHEDULE_GLOB.ScheduleTabTyp; -- temporary variable
312 l_I NUMBER;
313 l_J NUMBER;
314 l_temp_first NUMBER; -- temporary variable
315 l_temp_last NUMBER; -- temporary variable
316 l_temp_next NUMBER; -- temporary variable
317 l_shift_change BOOLEAN;-- variable if the shift is changed
318 l_x_return_status VARCHAR2(50);
319 excep_days_count NUMBER;
320 BEGIN
321
322 -- Storing the status for error handling
323 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
324 x_return_status := FND_API.G_RET_STS_SUCCESS; -- 4537865
325 -- Storing the record for using this table of record in forther calculation
326 PA_SCHEDULE_UTILS.copy_schedule_rec_tab(p_sch_record_tab,p_sch_record_tab.first,p_sch_record_tab.last,
327 x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
328
329 -- deleting sothat no previos appended record exist
330 l_out_sch_record_tab.delete;
331
332 -- if exceptions/exception exist for the given calendar
333 IF ((p_cal_except_record_tab.count) >= 1 ) THEN
334 l_I := p_cal_except_record_tab.first;
335 LOOP
336 /* Checking for the empty table of record */
337 IF ( x_sch_record_tab.count = 0 ) THEN
338 Raise l_empty_tab_record;
339 END IF;
340 /* Added for bug 4870111
341 Find the number of days in the exception from the start date and end date
342 and for each day add the exceptions in a loop. */
343 excep_days_count := (p_cal_except_record_tab(l_I).except_end_date-p_cal_except_record_tab(l_I).except_start_date);
344 for i in 0..excep_days_count -- Loop Added for bug 4870111
345 Loop
346 l_J := x_sch_record_tab.first;
347 l_shift_change := FALSE;
348 PA_SCHEDULE_UTILS.log_message(1,'EXCPT : '||to_char(p_cal_except_record_tab(l_I).except_start_date+i,'dd/mm/yyyy'));
349 LOOP
350 PA_SCHEDULE_UTILS.log_message(1,'SCH : '||to_char(x_sch_record_tab(l_J).start_date,'dd/mm/yyyy')||' '
351 ||to_char(x_sch_record_tab(l_J).end_date,'dd/mm/yyyy'));
352
353 /* Exception date is falling either before the start date of schedule or after end date of schedule */
354 IF (( p_cal_except_record_tab(l_I).except_start_date+i < x_sch_record_tab(l_J).start_date) OR
355 (p_cal_except_record_tab(l_I).except_start_date+i > x_sch_record_tab(l_J).end_date) ) THEN
356
357 -- PA_SCHEDULE_UTILS.log_message(1,'inside exp_dt < sch_st_dt or exp_dt > sch_end_dt ');
358 -- storing this record in temp variable to copy in final variable
359 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
360 PA_SCHEDULE_UTILS.add_schedule_rec_tab(x_sch_record_tab,l_j,l_j,l_out_sch_record_tab,l_x_return_status,
361 x_msg_count,x_msg_data);
362 END IF;
363
364 /* Exception date is falling between the start date of schedule and end date of schedule */
365 ELSIF ((p_cal_except_record_tab(l_I).except_start_date+i >= x_sch_record_tab(l_J).start_date) AND
366 (p_cal_except_record_tab(l_I).except_start_date+i <= x_sch_record_tab(l_J).end_date)) THEN
367
368 PA_SCHEDULE_UTILS.log_message(1,'inside exp_dt >= sch_st_dt AND exp_dt <= sch_end_dt ');
369
370 l_shift_change := TRUE;
371 -- storing the value in temp variable and using in updation of schedule record which are without exception
372 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
373 PA_SCHEDULE_UTILS.copy_schedule_rec_tab(x_sch_record_tab,l_J,l_J,l_temp_sch_record_tab,
374 l_x_return_status,x_msg_count,x_msg_data );
375 END IF;
376
377 -- storing the value in temp variable and using in updation of schedule record which are with exception
378 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
379 PA_SCHEDULE_UTILS.copy_schedule_rec_tab(x_sch_record_tab,l_J,l_J,l_temp_exp_sch_record_tab,
380 l_x_return_status,x_msg_count,x_msg_data );
381 END IF;
382
383 l_temp_first := l_temp_sch_record_tab.first;
384 l_temp_last := l_temp_sch_record_tab.last;
385
386 /* Exception date is on the start date and end date */
387 IF((x_sch_record_tab(l_J).start_date = p_cal_except_record_tab(l_I).except_start_date+i AND
388 x_sch_record_tab(l_J).end_date = p_cal_except_record_tab(l_I).except_start_date+i )) THEN
389
390 -- updating that schedule record with zero hours and keeping start date and end date is same
391 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
392 PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab =>l_temp_exp_sch_record_tab,
393 p_start_date => p_cal_except_record_tab(l_I).except_start_date+i
394 ,p_end_date =>p_cal_except_record_tab(l_I).except_start_date+i,p_monday_hours =>0,p_tuesday_hours =>0,
395 p_wednesday_hours =>0,p_thursday_hours =>0,p_friday_hours =>0,p_saturday_hours =>0,p_sunday_hours =>0,
396 x_return_status =>l_x_return_status,x_msg_count =>x_msg_count,x_msg_data =>x_msg_data);
397 END IF;
398 -- adding in other temp variable just to copy in final variable
399 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
400 PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_exp_sch_record_tab,l_temp_first,l_temp_last,
401 l_out_sch_record_tab,l_x_return_status, x_msg_count,x_msg_data);
402 END IF;
403
404 /* Exception date is on the start date and breaking that record in two records one with the zero
405 schedule and other with the defined schedule */
406 ELSIF ( x_sch_record_tab(l_J).start_date = p_cal_except_record_tab(l_I).except_start_date+i ) THEN
407
408 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
409 PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab =>l_temp_exp_sch_record_tab,
410 p_start_date => p_cal_except_record_tab(l_I).except_start_date+i
411 ,p_end_date =>p_cal_except_record_tab(l_I).except_start_date+i,p_monday_hours =>0,p_tuesday_hours =>0,
412 p_wednesday_hours =>0,p_thursday_hours =>0,p_friday_hours =>0,p_saturday_hours =>0,
413 p_sunday_hours =>0,x_return_status =>l_x_return_status,x_msg_count =>x_msg_count,
414 x_msg_data =>x_msg_data);
415 END IF;
416
417 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
418 PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_exp_sch_record_tab,l_temp_first,l_temp_last,
419 l_out_sch_record_tab,l_x_return_status, x_msg_count,x_msg_data);
420 END IF;
421
422 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
423 PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab =>l_temp_sch_record_tab,
424 p_start_date =>p_cal_except_record_tab(l_I).except_start_date+i + 1 ,
425 p_end_date =>x_sch_record_tab(l_J).end_date,x_return_status =>l_x_return_status,
426 x_msg_count =>x_msg_count,x_msg_data =>x_msg_data);
427 END IF;
428
429 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
430 PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_sch_record_tab,l_temp_first,l_temp_last,
431 l_out_sch_record_tab,l_x_return_status, x_msg_count,x_msg_data);
432 END IF;
433
434 /* Exception date is under the end date and breaking into three records one with strating date as
435 it is and ending date is just before the exception date then second starting date is exception date
436 and ending date is also exception but with zero hours and last having start date with just after
437 the exception date and end date is passed end date */
438 ELSIF (p_cal_except_record_tab(l_I).except_start_date+i < x_sch_record_tab(l_J).end_date ) THEN
439
440 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
441 PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab =>l_temp_sch_record_tab,
442 p_start_date =>x_sch_record_tab(l_J).start_date ,
443 p_end_date =>p_cal_except_record_tab(l_I).except_start_date+i - 1,
444 x_return_status =>l_x_return_status,x_msg_count =>x_msg_count,x_msg_data =>x_msg_data);
445 END IF;
446
447 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
448 PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_sch_record_tab,l_temp_first,l_temp_last,
449 l_out_sch_record_tab,l_x_return_status,
450 x_msg_count,x_msg_data);
451 END IF;
452
453 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
454 PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab =>l_temp_exp_sch_record_tab,
455 p_start_date => p_cal_except_record_tab(l_I). except_start_date+i
456 ,p_end_date =>p_cal_except_record_tab(l_I).except_start_date+i,p_monday_hours =>0,p_tuesday_hours =>0,
457 p_wednesday_hours =>0,p_thursday_hours =>0,p_friday_hours =>0,p_saturday_hours =>0,
458 p_sunday_hours =>0,x_return_status =>l_x_return_status,x_msg_count =>x_msg_count,
459 x_msg_data =>x_msg_data);
460 END IF;
461
462 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
463 PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_exp_sch_record_tab,l_temp_first,l_temp_last,
464 l_out_sch_record_tab,l_x_return_status, x_msg_count,x_msg_data);
465 END IF;
466
467 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
468 PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab =>l_temp_sch_record_tab,
469 p_start_date =>p_cal_except_record_tab(l_I).except_start_date+i + 1 ,
470 p_end_date =>x_sch_record_tab(l_J).end_date,
471 x_return_status =>l_x_return_status,x_msg_count =>x_msg_count,x_msg_data =>x_msg_data);
472 END IF;
473
474 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
475 PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_sch_record_tab,l_temp_first,l_temp_last,
476 l_out_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
477 END IF;
478
479 /* Exception date is on the end date and breaking into two records one with start date is as
480 it is and end date is just before the exception date the other having start date is
481 the exception date and end date is also the exception date i.e. end date of the previous schedule*/
482 ELSIF (p_cal_except_record_tab(l_I).except_start_date+i = x_sch_record_tab(l_J).end_date) THEN
483
484 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
485 PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab =>l_temp_sch_record_tab,
486 p_start_date =>x_sch_record_tab(l_J).start_date ,
487 p_end_date =>p_cal_except_record_tab(l_I).except_start_date+i - 1,
488 x_return_status =>l_x_return_status,x_msg_count =>x_msg_count,x_msg_data =>x_msg_data);
489 END IF;
490
491 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
492 PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_sch_record_tab,l_temp_first,l_temp_last,
493 l_out_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
494 END IF;
495
496 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
497 PA_SCHEDULE_UTILS.update_sch_rec_tab(px_sch_record_tab =>l_temp_exp_sch_record_tab,
498 p_start_date => p_cal_except_record_tab(l_I). except_start_date+i
499 ,p_end_date =>p_cal_except_record_tab(l_I).except_start_date+i,p_monday_hours =>0,p_tuesday_hours =>0,
500 p_wednesday_hours =>0,p_thursday_hours =>0,p_friday_hours =>0,p_saturday_hours =>0,
501 p_sunday_hours =>0,x_return_status =>l_x_return_status,x_msg_count =>x_msg_count,
502 x_msg_data =>x_msg_data);
503 END IF;
504
505 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
506 PA_SCHEDULE_UTILS.add_schedule_rec_tab(l_temp_exp_sch_record_tab,l_temp_first,l_temp_last,
507 l_out_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
508 END IF;
509
510 END IF;
511
512 IF (l_J <> x_sch_record_tab.last ) THEN
513 l_temp_next := x_sch_record_tab.next(l_J);
514 l_temp_last := x_sch_record_tab.last;
515
516 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
517 PA_SCHEDULE_UTILS.add_schedule_rec_tab(x_sch_record_tab,l_temp_next,l_temp_last,
518 l_out_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data );
519 END IF;
520 END IF;
521 EXIT;
522 END IF;
523
524 IF (l_J <> x_sch_record_tab.last) THEN
525 l_J := x_sch_record_tab.next(l_J);
526 ELSE
527 EXIT;
528 END IF;
529
530 END LOOP;
531
532 IF (l_shift_change) THEN
533 IF (l_x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
534 -- finaly copying the records from temp variable to the actual variable
535 PA_SCHEDULE_UTILS.copy_schedule_rec_tab(l_out_sch_record_tab,l_out_sch_record_tab.first,
536 l_out_sch_record_tab.last,x_sch_record_tab,l_x_return_status,x_msg_count,x_msg_data);
537 END IF;
538
539 END IF;
540
541 l_out_sch_record_tab.delete;
542 END loop; -- Loop Added for bug 4870111
543 excep_days_count := 0;
544 IF (l_I <> p_cal_except_record_tab.last) THEN
545 l_I := p_cal_except_record_tab.next(l_I);
546 ELSE
547 EXIT;
548 END IF;
549
550 END LOOP;
551
552 END IF;
553
554 x_return_status := l_x_return_status;
555
556 EXCEPTION
557 WHEN l_empty_tab_record THEN
558 NULL;
559 WHEN OTHERS THEN
560 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
561 x_msg_count := 1 ; -- 4537865
562 x_msg_data := SUBSTRB(SQLERRM,1,240); -- 4537865
563 FND_MSG_PUB.add_exc_msg(p_pkg_name =>'PA_CALENDAR_UTILS',
564 p_procedure_name => 'apply_calendar_except');
565 raise;
566
567
568 END apply_calendar_except;
569
570 -- This procedure will validate the passing name or Id with the existing name or id in the table
571 -- Input parameters
572 -- Parameters Type Required Description
573 -- P_Calendar_Id NUMBER YES Id for that calendar to which you want to have schedule
574 -- P_Calendar_Name VARCHAR2 YES This variable having the name of the calendar
575 -- p_check_id_flag VARCHAR2 NO check id flag
576 -- Out parameters
577 -- X_Calendar_Id NUMBER YES It stores the validated calenda id
578 --
579 PROCEDURE Check_Calendar_Name_Or_Id
580 ( p_calendar_id IN JTF_CALENDARS_VL.calendar_id%TYPE
581 ,p_calendar_name IN JTF_CALENDARS_VL.calendar_name%TYPE
582 ,p_check_id_flag IN VARCHAR2 := 'A'
583 ,x_calendar_id OUT NOCOPY JTF_CALENDARS_VL.calendar_id%TYPE --File.Sql.39 bug 4440895
584 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
585 ,x_error_message_code OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
586
587 l_current_id NUMBER := NULL;
588 l_num_ids NUMBER := 0;
589 l_id_found_flag VARCHAR(1) := 'N';
590
591 CURSOR c_ids IS
592 SELECT calendar_id
593 FROM jtf_calendars_vl
594 WHERE calendar_name = p_calendar_name;
595 BEGIN
596
597 /* Bug 2887390 : Added the following condition */
598 IF (p_calendar_id is NULL and p_calendar_name is NULL) THEN
599 x_calendar_id := NULL;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 x_error_message_code := 'PA_CALENDAR_ID_INVALID';
602 return;
603 END IF;
604
605 IF (p_calendar_id IS NOT NULL) THEN
606 IF (p_check_id_flag = 'Y') THEN
607 -- Validate ID
608 SELECT calendar_id
609 INTO x_calendar_id
610 FROM jtf_calendars_b -- 4370086: FROM jtf_calendars_vl
611 WHERE calendar_id = p_calendar_id;
612 ELSIF (p_check_id_flag = 'N') THEN
613 -- No ID validation necessary
614 x_calendar_id := p_calendar_id;
615 ELSIF (p_check_id_flag = 'A') THEN
616 IF (p_calendar_name IS NULL) THEN
617 -- Return a null ID since the name is null.
618 x_calendar_id := NULL;
619 ELSE
620 -- Find the ID which matches the Name passed
621 OPEN c_ids;
622 LOOP
623 FETCH c_ids INTO l_current_id;
624 EXIT WHEN c_ids%NOTFOUND;
625 IF (l_current_id = p_calendar_id) THEN
626 l_id_found_flag := 'Y';
627 x_calendar_id := p_calendar_id;
628 END IF;
629 END LOOP;
630 l_num_ids := c_ids%ROWCOUNT;
631 CLOSE c_ids;
632
633 IF (l_num_ids = 0) THEN
634 -- No IDs for name
635 RAISE NO_DATA_FOUND;
636 ELSIF (l_num_ids = 1) THEN
637 -- Since there is only one ID for the name use it.
638 x_calendar_id := l_current_id;
639 ELSIF (l_id_found_flag = 'N') THEN
640 -- More than one ID for the name and none of the IDs matched
641 -- the ID passed in.
642 RAISE TOO_MANY_ROWS;
643 END IF;
644 END IF;
645 END IF;
646 ELSE -- Find ID since it was not passed.
647 IF (p_calendar_name IS NOT NULL) THEN
648 SELECT calendar_id
649 INTO x_calendar_id
650 FROM jtf_calendars_vl
651 WHERE calendar_name = p_calendar_name;
652 ELSE
653 x_calendar_id := NULL;
654 END IF;
655 END IF;
656
657 x_return_status:= FND_API.G_RET_STS_SUCCESS;
658
659 EXCEPTION
660 WHEN NO_DATA_FOUND THEN
661 x_calendar_id := NULL;
662 x_return_status := FND_API.G_RET_STS_ERROR;
663 x_error_message_code := 'PA_CALENDAR_ID_INVALID';
664 WHEN TOO_MANY_ROWS THEN
665 x_calendar_id := NULL;
666 x_return_status := FND_API.G_RET_STS_ERROR;
667 x_error_message_code := 'PA_CALENDAR_NOT_UNIQUE';
668 WHEN OTHERS THEN
669 x_calendar_id := NULL;
670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
671 -- 4537865 : reset x_error_message_code also
672 x_error_message_code := SQLCODE ;
673
674 FND_MSG_PUB.add_exc_msg(p_pkg_name =>'PA_CALENDAR_UTILS',
675 p_procedure_name => 'Check_Calendar_Name_Or_Id');
676 RAISE;
677 END Check_Calendar_Name_Or_Id;
678
679 --
680 END PA_CALENDAR_UTILS;
[Home] [Help]