4 -----------------------------------------------------------------
1 PACKAGE BODY AHL_VWP_TIMES_PVT AS
2 /* $Header: AHLVTMSB.pls 120.7.12020000.2 2012/12/07 14:58:40 sareepar ship $ */
3
5 -- Define Global CONSTANTS --
6 -----------------------------------------------------------------
7 G_APP_NAME CONSTANT VARCHAR2(3) := 'AHL';
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_VWP_TIMES_PVT';
9
10 G_SECS_IN_DAY CONSTANT NUMBER := 86400; --Seconds in a day
11 G_HOLIDAY_TYPE CONSTANT NUMBER := 2;
12
13 -- Package level Global Variables used in time calculations
14 G_CURRENT_DEPT_ID NUMBER := NULL; -- Cached department id, so to know whether to reload
15
16 G_CAL_START DATE := NULL; -- Calendar start date for shift data
17 G_CAL_END DATE := NULL; -- Calendar end date for shift data
18 G_SHIFT_START NUMBER := NULL; -- Shift start in .decimal value of days
19 G_SHIFT_END NUMBER := NULL; -- Shift end in .decimal value of days
20 G_DAYS_ON NUMBER := NULL; -- Number of Days on
21 G_DAYS_OFF NUMBER := NULL; -- Number of Days off
22 G_VISIT_START_DATE DATE := NULL; -- The visit start date
23 G_VISIT_DEPT_ID NUMBER := NULL; -- The visit department
24 G_VISIT_STATUS VARCHAR2(30):= NULL; --The visit status
25 G_RESET_SYSDATE_FLAG VARCHAR2(1);
26
27 /*Added by sowsubra*/
28 G_ASSOC_TYPE_ROUTE CONSTANT VARCHAR2(30) := 'ROUTE';
29 G_ASSOC_TYPE_OPERATION CONSTANT VARCHAR2(30) := 'OPERATION';
30
31 -- Table type for all the Dates while deriving visit and task times
32 TYPE Dates_Tbl_Type IS TABLE OF DATE
33 INDEX BY BINARY_INTEGER;
34 TYPE Number_Tbl_Type IS TABLE OF NUMBER
35 INDEX BY BINARY_INTEGER;
36
37 G_EXCEPTION_DATES_TBL Dates_Tbl_Type; -- Stores the holidays for dept
38 G_STAGES_TBL Number_Tbl_Type; -- Stores the stage offsets for visit
39 ------------------------------------
40 -- Common constants and variables --
41 ------------------------------------
42 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
43 l_log_statement NUMBER := fnd_log.level_statement;
44 l_log_procedure NUMBER := fnd_log.level_procedure;
45 l_log_error NUMBER := fnd_log.level_error;
46 l_log_unexpected NUMBER := fnd_log.level_unexpected;
47 -----------------------------------------------------------------------
48 /*procedure adbug(p_msg_txt in varchar2 , p_message in date)
49 is
50 begin
51 if (true) then
52 dbms_output.put_line(p_msg_txt || to_char(p_message,'dd:mm:yyyy hh24:mi:ss'));
53 end if;
54 end adbug;
55
56 procedure adbug(p_message in varchar2)
57 is
58 begin
59 if (true) then
60 dbms_output.put_line(p_message);
61 end if;
62 end adbug;
63 */
64 --------------------------------------------------------------------
65 -- Define local procedures signature --
66 --------------------------------------------------------------------
67 --------------------------------------------------------------------
68 --Initializes the global variables based on the department id
69 --------------------------------------------------------------------
73 --Internal recursion code for setting task times
70 PROCEDURE Init_Shift_Data(p_department_id IN number);
71
72 --------------------------------------------------------------------
74 --------------------------------------------------------------------
75 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
76 -- Added the optional in param p_task_start_date
77 -- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063 :: Commented the below procedure
78 /*
79 PROCEDURE Adjust_Task_Times_Internal(p_task_id IN NUMBER,
80 p_task_start_date IN DATE default NULL);*/
81
82 --------------------------------------------------------------------
83 -- Define local functions signature --
84 --------------------------------------------------------------------
85
86 ----------------------------------------------------------------------
87 -- Gets the duration of a Route from its timespan column
88 ---------------------------------------------------------------------
89 -- Determines if a specific date is a holiday
90 FUNCTION Is_Dept_Holiday(l_curr_date DATE) RETURN BOOLEAN;
91
92 ----------------------------------------------------------
93 -- Derive the shift start date based on shift timing.
94 ------------------------------------------------------------
95 FUNCTION get_shift_start_date(p_date IN DATE)
96 RETURN DATE
97 IS
98 BEGIN
99 --If it's an overnight shift and time is before shift ends on the same day,
100 --the shift started a day earlier
101 IF (G_SHIFT_END < G_SHIFT_START AND
102 p_date <= trunc(p_date)+G_SHIFT_END) THEN --Fixed bug :: PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
103 RETURN TRUNC(p_date) -1;
104 ELSE
105 RETURN TRUNC(p_date);
106 END IF;
107 END get_shift_start_date;
108
109 -- PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
110 ----------------------------------------------------------
111 -- Derive the shift end date based on shift timing.
112 ------------------------------------------------------------
113 FUNCTION get_shift_end_date(p_date IN DATE)
114 RETURN DATE
115 IS
116 BEGIN
117 --If it's an overnight shift and time is before shift ends on the same day,
118 --the shift started a day earlier
119 IF (G_SHIFT_END < G_SHIFT_START AND
120 p_date >= trunc(p_date)+G_SHIFT_START) THEN --Fixed bug
121 RETURN TRUNC(p_date)+1;
122 ELSE
123 RETURN TRUNC(p_date);
124 END IF;
125 END get_shift_end_date;
126
127 --------------------------------------------------------------------
128 -- Define procedures body --
129 --------------------------------------------------------------------
130 --------------------------------------------------------------------
131 -- Function name : Get_Visit_Start_Time
132 -- Type : Public
133 -- Purpose : Fetches Master Work Order Actual Start Date if the
134 -- Visit is Closed, else RETURNs the Visit Start Date.
135 --
136 -- Parameters :
137 -- p_visit_id Visit ID to fetch the data
138 --
139 -- Version :
140 -- 17 September, 2007 RNAHATA Initial Version - 1.0
141 --
142 -- Added for Bug 6430038
143 --------------------------------------------------------------------
144 function Get_Visit_Start_Time(
145 p_visit_id IN NUMBER
146 )
147 RETURN DATE
148 IS
149 --
150 -- To get the Start Date and Status of the Visit
154 WHERE VISIT_ID = c_visit_id;
151 CURSOR c_visit_csr (c_visit_id IN NUMBER) IS
152 SELECT START_DATE_TIME, STATUS_CODE
153 FROM AHL_VISITS_B
155
156 -- Actual Visit Start Date
160 WHERE VISIT_ID = c_visit_id
157 CURSOR get_actual_start_date_csr(c_visit_id IN NUMBER) IS
158 SELECT ACTUAL_START_DATE
159 FROM AHL_WORKORDERS
161 AND VISIT_TASK_ID IS NULL
162 AND MASTER_WORKORDER_FLAG = 'Y';
163
164 --
165 l_actual_start_date DATE := null;
166 c_visit_rec c_visit_csr%ROWTYPE;
167 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_Start_Time';
168 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
169 --
170 BEGIN
171
172 IF (l_log_procedure >= l_log_current_level) THEN
173 fnd_log.string(l_log_procedure,
174 L_DEBUG_KEY ||'.begin',
175 'At the start of PL SQL function. Visit Id = ' || p_visit_id);
176 END IF;
177
178 OPEN c_visit_csr(p_visit_id);
179 FETCH c_visit_csr INTO c_visit_rec;
180 CLOSE c_visit_csr;
181
182 -- Closed
183 IF c_visit_rec.STATUS_CODE ='CLOSED' THEN
184 OPEN get_actual_start_date_csr (p_visit_id);
185 FETCH get_actual_start_date_csr INTO l_actual_start_date;
186 CLOSE get_actual_start_date_csr;
187 IF (l_log_procedure >= l_log_current_level) THEN
188 fnd_log.string(l_log_procedure,
189 L_DEBUG_KEY ||'.end',
190 'At the end of PL SQL function. Visit Start Date = ' || l_actual_start_date);
191 END IF;
192 RETURN l_actual_start_date;
193
194 -- Planning/Released/Partially Released/Cancelled
195 ELSE
196 IF (l_log_procedure >= l_log_current_level) THEN
197 fnd_log.string(l_log_procedure,
198 L_DEBUG_KEY ||'.end',
199 'At the end of PL SQL function. Visit Start Date = ' || c_visit_rec.START_DATE_TIME);
200 END IF;
201 RETURN c_visit_rec.START_DATE_TIME;
202 END IF;
203
204 END Get_Visit_Start_Time;
205
206 -- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063 :: Completely changed the logic of this procedure
207 --------------------------------------------------------------------
208 -- Define procedures body
209 --------------------------------------------------------------------
210 --------------------------------------------------------------------
211 -- Function name : Get_Visit_End_Time
212 -- Type : Public
213 -- Purpose : To RETURN the End Date for the visit.
214 -- The end date of the visit is the scheduled completion date
215 -- of its visit MWO
216 --
217 -- Parameters :
218 -- p_visit_id Visit ID to fetch the data
219 -- the call is made internally from VWP.
220 -- VWPE: ER:12424063 :: p_use_actuals is no more used
221 --------------------------------------------------------------------
222 FUNCTION Get_Visit_End_Time(
223 p_visit_id IN NUMBER,
224 p_use_actuals IN VARCHAR2 := FND_API.G_TRUE
225 )
226 RETURN DATE
227 IS
228 --
229
230 --PRAKKUM :: 18/04/2012 :: Bug 13095151
231 -- To find visit master WO end date
232 CURSOR c_visit_mwo_end_date (c_id IN NUMBER) IS
233 /*SELECT WIP.scheduled_completion_date
234 FROM WIP_DISCRETE_JOBS WIP, AHL_WORKORDERS WO
235 WHERE WO.VISIT_ID = c_id
236 AND WO.visit_task_id IS NULL
237 AND WO.master_workorder_flag = 'Y'
238 AND WO.wip_entity_id = WIP.wip_entity_id;*/
239 SELECT max(WIP.scheduled_completion_date) AS scheduled_completion_date
240 FROM WIP_DISCRETE_JOBS WIP, AHL_WORKORDERS WO
241 WHERE WO.VISIT_ID = c_id
242 AND WO.STATUS_CODE NOT IN ('7', '22') -- Exclude canceled and deleted work orders
243 AND WO.wip_entity_id = WIP.wip_entity_id;
244
245
246 l_end_date DATE := null; --The visit end date
247 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Visit_End_Time';
248 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
249 --
250 BEGIN
251
252 IF (l_log_procedure >= l_log_current_level) THEN
253 fnd_log.string(l_log_procedure,
254 L_DEBUG_KEY ||'.begin',
255 'At the start of PL SQL function. Visit Id = ' || p_visit_id );
256 END IF;
257
258 --Fetch the visit master WO end date
259 OPEN c_visit_mwo_end_date(p_visit_id);
260 FETCH c_visit_mwo_end_date INTO l_end_date;
261 CLOSE c_visit_mwo_end_date;
262
263
264 RETURN l_end_date;
265 END Get_Visit_End_Time;
266
267 -- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063 :: The logic of this procedure is completely changed
268 -- Going forward visit tasks' dates will be dependent on stage planned dates
269 -------------------------------------------------------------------
270 -- Procedure name : Calculate_Task_Times
271 -- Type : Private
272 -- Function : Derive the start and end times/hours of tasks
273 -- and the end_date_time of the visit
274 -- Parameters :
275 --
276 -- Standard OUT Parameters :
277 -- x_return_status OUT VARCHAR2 Required
278 -- x_msg_count OUT NUMBER Required
279 -- x_msg_data OUT VARCHAR2 Required
280 --
281 -- Calculate_Task_Times Parameters:
282 -- p_visit_id IN NUMBER Required
283 -- The id of the visit whose associated tasks' start and end times or hours
284 -- need to be derived
285 -- Version :
286 -- Initial Version 1.0
287 --
288 -------------------------------------------------------------------
289 PROCEDURE Calculate_Task_Times
290 (
294 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
291 p_api_version IN NUMBER,
292 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
293 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_count OUT NOCOPY NUMBER,
297 x_msg_data OUT NOCOPY VARCHAR2,
298 p_visit_id IN NUMBER)
299 IS
300 --
301 -- PRAKKUM :: BUG Internal (also fixed #13595460):: 19/01/2012 :: Task Time Adjustments Not Required For Tasks those that have workorders
302 -- already since scheduling will be taken care of by PS if the task has a WO :: START
303 --Cursor for fetching task ids for all the tasks in a visit
304 /*CURSOR get_visit_tasks(x_visit_id IN NUMBER) IS
305 SELECT visit_task_id, stage_id
306 FROM AHL_VISIT_TASKS_B
307 WHERE visit_id = x_visit_id
308 AND status_code = 'PLANNING';*/
309 CURSOR get_visit_tasks(x_visit_id IN NUMBER) IS
313 WHERE tsk.visit_id = x_visit_id
310 SELECT tsk.visit_task_id visit_task_id, tsk.stage_id stage_id
311 FROM AHL_VISIT_TASKS_B tsk
312 , ahl_workorders wo
314 AND tsk.status_code = 'PLANNING'
315 AND tsk.visit_task_id = wo.visit_task_id(+)
316 AND ((wo.workorder_id is NULL)
317 -- SKPATHAK :: Bug 13890788 :: Do schedule the future dated planning tasks
318 OR (tsk.start_date_time > SYSDATE));
319 -- PRAKKUM :: BUG Internal (also fixed #13595460):: 19/01/2012 :: Task Time Adjustments Not Required For Tasks those that have workorders
320 -- already since scheduling will be taken care of by PS if the task has a WO :: END
321
322 -- SKPATHAK :: 12-MAY-2011 :: VWPE: ER:12424063 :: Modified the below cursor
323 --Cursor for fetching visit start and end dates
324 CURSOR get_visit_dates(x_visit_id IN NUMBER) IS
325 SELECT past_dated_visit_flag, start_date_time, close_date_time
326 FROM AHL_VISITS_B
327 WHERE visit_id = x_visit_id;
328 visit_dates_rec get_visit_dates%ROWTYPE;
329
330 -- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063
331 --Cursor for deriving the task's stage dates
332 CURSOR get_stage_details(c_visit_id IN number)
333 IS
334 SELECT planned_start_date, planned_end_date, stage_id
335 from ahl_vwp_stages_b
336 where visit_id = c_visit_id;
337
338
339 -- Define local variables
340 l_api_version CONSTANT NUMBER := 1.0;
341 L_API_NAME CONSTANT VARCHAR2(30) := 'Calculate_Task_Times';
342 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
343 l_task_start_date DATE; --Task start date
344 l_task_end_date DATE; --Task end date
345 --MANESING::Component Maintenance Planning Project, 07-Aug-2011, added local variable l_is_comp_visit
346 --l_is_comp_visit VARCHAR2(1) := AHL_CMP_UTIL_PKG.Is_Comp_Visit (p_visit_id);--PRAKKUM :: 20/01/2012 :: After necessary changes
347
348 -- Associative array of visit task id to project task ref
349 TYPE Ref_Tbl_Type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
350 l_stage_id_st_dt_map_Tbl Ref_Tbl_Type;
351 l_stage_id_end_dt_map_Tbl Ref_Tbl_Type;
352 --
353 BEGIN
354
355 SAVEPOINT calculate_task_Times_pvt;
356
357 IF (l_log_procedure >= l_log_current_level) THEN
358 fnd_log.string(l_log_procedure,
359 L_DEBUG_KEY ||'.begin',
360 'At the start of PL SQL procedure. Visit Id = ' || p_visit_id);
361 END IF;
362
363 -- Standard call to check for call compatibility
364 IF NOT Fnd_Api.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
365 G_PKG_NAME) THEN
366 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
367 END IF;
368
369 -- Initialize message list if p_init_msg_list is set to TRUE
370 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
371 Fnd_Msg_Pub.Initialize;
372 END IF;
373
374 -- Initialize API RETURN status to success
375 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
376
377 OPEN get_visit_dates (p_visit_id);
378 FETCH get_visit_dates INTO visit_dates_rec;
379 CLOSE get_visit_dates;
380
381 -- Get all the stages' dates in associate arrays indexed by stage id
382 FOR l_stage_rec IN get_stage_details( p_visit_id ) LOOP
383 l_stage_id_st_dt_map_Tbl(l_stage_rec.stage_id) := l_stage_rec.planned_start_date;
384 l_stage_id_end_dt_map_Tbl(l_stage_rec.stage_id) := l_stage_rec.planned_end_date;
385 END LOOP;
386
387 IF (l_log_statement >= l_log_current_level) THEN
388 fnd_log.string(l_log_statement, L_DEBUG_KEY , 'p_visit_id = ' || p_visit_id);
389 END IF;
390
394 /* MANESING::Component Maintenance Planning Project, 07-Aug-2011
391 --Loop through all the tasks of the visit
392 FOR l_task_rec IN get_visit_tasks( p_visit_id ) LOOP
393
395 * For Component Visits, Task Start and End date should be defaulted to SYSDATE, and will
396 * be taken care later off by PS.
397 */
398 /*IF (l_is_comp_visit = 'Y' AND
399 NVL(visit_dates_rec.past_dated_visit_flag, 'N') = 'N') THEN
400 l_task_start_date := SYSDATE;
401 l_task_end_date := SYSDATE;
402 ELSE*/
403 IF l_task_rec.stage_id IS NOT NULL THEN
404 --If the task has a stage associated to it, then get the stage dates stored above in associative arrays
405 l_task_start_date := l_stage_id_st_dt_map_Tbl(l_task_rec.stage_id);
406 l_task_end_date := l_stage_id_end_dt_map_Tbl(l_task_rec.stage_id);
407 ELSE
408 l_task_start_date := visit_dates_rec.start_date_time;
409 l_task_end_date := visit_dates_rec.close_date_time;
410 END IF;
411 /*END IF;*/
412
413 IF (l_log_statement >= l_log_current_level) THEN
414 fnd_log.string(l_log_statement,
415 L_DEBUG_KEY , 'l_task_start_date = ' || to_char(l_task_start_date,'DD-MON-YY hh24:mi:ss'));
416 fnd_log.string(l_log_statement,
417 L_DEBUG_KEY , 'l_task_end_date = ' || to_char(l_task_end_date,'DD-MON-YY hh24:mi:ss'));
418 END IF;
419
420 UPDATE AHL_VISIT_TASKS_B
421 SET START_DATE_TIME = l_task_start_date,
422 END_DATE_TIME = l_task_end_date
423 WHERE visit_task_id = l_task_rec.visit_task_id
424 AND PAST_TASK_START_DATE IS NULL;--Line Added by jrotich for fixing bug #13518519
425 END LOOP;
426
427 -- Standard check of p_commit
428 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
429 COMMIT WORK;
430 END IF;
431
432 -- Standard call to get message count and if count is 1, get message info
433 Fnd_Msg_Pub.Count_And_Get
434 ( p_count => x_msg_count,
435 p_data => x_msg_data,
436 p_encoded => Fnd_Api.g_false
437 );
438
439 IF (l_log_procedure >= l_log_current_level) THEN
440 fnd_log.string(l_log_procedure,
441 L_DEBUG_KEY ||'.end',
442 'At the end of PL SQL procedure. RETURN Status = ' || x_return_status);
443 END IF;
444
445 EXCEPTION
446 WHEN Fnd_Api.G_EXC_ERROR THEN
447 ROLLBACK TO calculate_task_Times_pvt;
448 x_return_status := Fnd_Api.G_RET_STS_ERROR;
449 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
450 p_data => x_msg_data,
451 p_encoded => Fnd_Api.g_false);
452
453 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
454 ROLLBACK TO calculate_task_Times_pvt;
455 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
456 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
457 p_data => x_msg_data,
458 p_encoded => Fnd_Api.g_false);
459
460 WHEN OTHERS THEN
461 ROLLBACK TO calculate_task_Times_pvt;
465 p_procedure_name => 'Calculate_Task_Times',
462 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
463 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
464 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
466 p_error_text => SUBSTR(SQLERRM,1,240));
467 END IF;
468 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
469 p_data => x_msg_data,
470 p_encoded => Fnd_Api.g_false);
474 -- Going forward visit tasks' dates will be dependent on stage planned dates
471 END Calculate_Task_Times;
472
473 -- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063 :: The logic of this procedure is completely changed
475 --------------------------------------------------------------------
476 -- Procedure name : Adjust_task_times
477 -- Type : Private
478 -- Purpose : Adjusts tasks times and all dependent task times
479 -- Parameters :
480 --
481 -- Standard OUT Parameters :
482 -- x_return_status OUT VARCHAR2 Required,
483 --
484 -- Adjust_task_Times IN Parameters :
485 -- p_task_id IN NUMBER Required
486 -- VWPE: ER:12424063 : parameters p_reset_sysdate_flag and p_task_start_date will no longer be used
487 -- Version :
488 -- Initial Version 1.0
489 --
490 --------------------------------------------------------------------
491 PROCEDURE Adjust_Task_Times
492 (
493 p_api_version IN NUMBER,
494 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
495 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
496 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
497 x_return_status OUT NOCOPY VARCHAR2,
498 x_msg_count OUT NOCOPY NUMBER,
499 x_msg_data OUT NOCOPY VARCHAR2,
500 p_task_id IN NUMBER,
501 p_reset_sysdate_flag IN VARCHAR2 := FND_API.G_FALSE,
502 p_task_start_date IN DATE := NULL)
503 IS
504 --
505 -- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063 :: Modified the below cursor
506 --Cursor for fetching visit start and end dates
507 -- PRAKKUM :: BUG Internal (also fixed #13595460):: 19/01/2012 :: Task Time Adjustments Not Required For Tasks those that have workorders
508 -- already since scheduling will be taken care of by PS if the task has a WO :: START
509 /*CURSOR get_visit_dets(p_task_id IN NUMBER) IS
510 SELECT v.visit_id, v.past_dated_visit_flag, v.status_code, v.start_date_time, v.close_date_time
511 FROM AHL_VISITS_B v, AHL_VISIT_TASKS_B vt
512 WHERE v.visit_id = vt.visit_id
513 AND vt.visit_task_id = p_task_id;*/
514 CURSOR get_visit_dets(p_task_id IN NUMBER) IS
515 SELECT v.visit_id, v.past_dated_visit_flag, v.status_code, v.start_date_time, v.close_date_time
516 FROM AHL_VISITS_B v, AHL_VISIT_TASKS_B vt
517 , ahl_workorders wo
518 WHERE v.visit_id = vt.visit_id
519 AND vt.visit_task_id = p_task_id
520 AND vt.visit_task_id = wo.visit_task_id(+)
521 AND ((wo.workorder_id is NULL)
522 -- SKPATHAK :: Bug 13890788 :: Do schedule the future dated planning tasks
523 OR (vt.start_date_time > SYSDATE));
524 -- PRAKKUM :: BUG Internal (also fixed #13595460):: 19/01/2012 :: Task Time Adjustments Not Required For Tasks those that have workorders
525 -- already since scheduling will be taken care of by PS if the task has a WO :: END
526
527 visit_dets_rec get_visit_dets%ROWTYPE;
528 --
529 -- SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063
530 --Cursor for deriving all the stage dates
531 CURSOR get_stage_dates(p_task_ID IN number)
532 IS
533 SELECT st.planned_start_date, st.planned_end_date
534 from ahl_vwp_stages_b st, ahl_visit_tasks_b vt
535 where st.stage_id = vt.stage_id
536 AND vt.visit_task_id = p_task_id;
537
538 -- Define local variables
539 l_api_version CONSTANT NUMBER := 1.0;
540 L_API_NAME CONSTANT VARCHAR2(30) := 'Adjust_Task_Times';
541 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
542 l_visit_id NUMBER;
543 l_task_start_date DATE;
544 l_task_end_date DATE;
545 --
546 BEGIN
547
548 SAVEPOINT Adjust_Task_Times_pvt;
549
550 IF (l_log_procedure >= l_log_current_level) THEN
551 fnd_log.string(l_log_procedure,
552 L_DEBUG_KEY ||'.begin',
553 'At the start of PL SQL procedure. Task Id = ' || p_task_id);
554 END IF;
555
556 -- Standard call to check for call compatibility
557 IF NOT Fnd_Api.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
558 G_PKG_NAME) THEN
559 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
560 END IF;
561
562 -- Initialize message list if p_init_msg_list is set to TRUE
563 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
564 Fnd_Msg_Pub.Initialize;
565 END IF;
566
567 -- Initialize API RETURN status to success
568 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
569
570 OPEN get_visit_dets (p_task_id);
571 FETCH get_visit_dets INTO visit_dets_rec;
572 CLOSE get_visit_dets;
573
574 -- PRAKKUM :: BUG Internal (also fixed #13595460):: 19/01/2012 :: Task Time Adjustments Not Required For Tasks those that have workorders
575 -- already since scheduling will be taken care of by PS if the task has a WO
576 IF visit_dets_rec.visit_id IS NOT NULL THEN
577
578 /* MANESING::Component Maintenance Planning Project, 07-Aug-2011
579 * For Component Visits, Task Start and End date should be defaulted to SYSDATE, and will
580 * be taken care later off by PS.
581 */
582 /*IF (AHL_CMP_UTIL_PKG.Is_Comp_Visit (visit_dets_rec.visit_id) = 'Y' AND
583 NVL(visit_dets_rec.past_dated_visit_flag, 'N') = 'N') THEN
584 l_task_start_date := SYSDATE;
585 l_task_end_date := SYSDATE;
586 ELSE*/
587 --Start Processing here
588 OPEN get_stage_dates (p_task_id);
589 FETCH get_stage_dates INTO l_task_start_date, l_task_end_date;
590 IF get_stage_dates%NOTFOUND THEN
594 l_task_end_date := visit_dets_rec.close_date_time;
591 -- If for this task, a corresponding stage record does not exist
592 -- Task start and end dates will be the visit's start and end dates
593 l_task_start_date := visit_dets_rec.start_date_time;
595 -- SKPATHAK :: 07-SEP-2011 :: VWPE ER: 12856858
596 -- If the visit is not a past dated visit and is not in Draft status, task's start date should be the sysdate
597 IF ((visit_dets_rec.status_code IN ('PLANNING', 'PARTIALLY RELEASED', 'RELEASED'))
598 AND (NVL(visit_dets_rec.past_dated_visit_flag, 'N') <> 'Y')) THEN
602 END IF;
599 IF (l_task_start_date < sysdate AND p_task_start_date IS NULL) THEN
600 l_task_start_date := sysdate;
601 END IF;
603 END IF;
604 CLOSE get_stage_dates;
605 /*END IF;*/
606
607 IF (p_task_start_date IS NOT NULL) THEN
608 l_task_start_date := p_task_start_date;
609 END IF;
610
611 UPDATE AHL_VISIT_TASKS_B
612 SET START_DATE_TIME = l_task_start_date,
616 END IF; -- End if visit_dets_rec.visit_id
613 END_DATE_TIME = l_task_end_date
614 WHERE visit_task_id = p_task_id;
615
617
618 -- Standard check of p_commit
619 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
620 COMMIT WORK;
621 END IF;
622
623 -- Standard call to get message count and if count is 1, get message info
624 Fnd_Msg_Pub.Count_And_Get
625 ( p_count => x_msg_count,
626 p_data => x_msg_data,
627 p_encoded => Fnd_Api.g_false
628 );
629
630 IF (l_log_procedure >= l_log_current_level) THEN
631 fnd_log.string(l_log_procedure,
632 L_DEBUG_KEY ||'.end',
633 'At the end of PL SQL procedure. RETURN Status = ' || x_return_status);
634 END IF;
635
636 EXCEPTION
637 WHEN Fnd_Api.G_EXC_ERROR THEN
638 ROLLBACK TO Adjust_Task_Times_pvt;
639 x_return_status := Fnd_Api.G_RET_STS_ERROR;
640 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
641 p_data => x_msg_data,
642 p_encoded => Fnd_Api.g_false);
643
644 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
645 ROLLBACK TO Adjust_Task_Times_pvt;
646 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
647 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
648 p_data => x_msg_data,
649 p_encoded => Fnd_Api.g_false);
650
651 WHEN OTHERS THEN
652 ROLLBACK TO Adjust_Task_Times_pvt;
653 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
654 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
655 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
656 p_procedure_name => 'Adjust_Task_Times',
657 p_error_text => SUBSTR(SQLERRM,1,240));
658 END IF;
659 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
660 p_data => x_msg_data,
661 p_encoded => Fnd_Api.g_false);
662 END Adjust_Task_Times;
663
664 --SKPATHAK :: 02-MAY-2011 :: VWPE: ER:12424063
665 -- Commented out the below procedure
666 -----------------------------------------------------------------------------
667 --Function: Private Method for recursion of task derivation
668 ------------------------------------------------------------------------------
669
670 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009
671 -- Added the optional in param p_task_start_date
672 /*PROCEDURE Adjust_Task_Times_Internal(p_task_id IN NUMBER,
673 p_task_start_date IN DATE default NULL)
674 IS
675 --Fetch basic visit data
676 CURSOR get_task_data_csr(p_task_id IN NUMBER) IS
677 SELECT vt.start_date_time, vt.end_date_time,
678 nvl(vt.department_id, v.department_id),
679 nvl(vt.duration, NVL(Get_task_duration(vt.quantity, routes.route_id), 0)) duration,
680 vt.stage_id,
681 nvl(vt.start_from_hour, 0)
682 FROM AHL_VISIT_TASKS_B vt, AHL_VISITS_B v,
683 ahl_routes_app_v routes, ahl_mr_routes_app_v mr
684 WHERE vt.visit_id = v.visit_id
685 AND routes.route_id (+) = mr.route_id
686 AND mr.mr_route_id (+)= vt.mr_route_id
687 AND vt.visit_task_id = p_task_id
688 AND vt.task_type_code <> 'SUMMARY'
689 AND (VT.STATUS_CODE IS NULL OR VT.STATUS_CODE <> 'DELETED');
690 --
691 --This cursor uses the technical dependencies and finds the parent task
692 -- that finishes last.
693 CURSOR get_tech_dependency_csr(p_task_id IN NUMBER) IS
694 SELECT max(vt.end_date_time)
695 FROM AHL_VISIT_TASKS_B vt, AHL_TASK_LINKS tl
696 WHERE vt.visit_task_id = tl.parent_task_id
697 AND tl.visit_task_id = p_task_id;
698 --
699 --Cursor which fetches all child technical dependent tasks
700 CURSOR get_child_dependency_csr(p_task_id IN NUMBER) IS
701 SELECT visit_task_id
702 FROM AHL_TASK_LINKS
703 WHERE parent_task_id = p_task_id;
704 --
705 l_old_task_start DATE; --Existing task start time
706 l_old_task_end DATE; --Existing task end time
707 l_task_dept_id NUMBER; --Existing task department
708 l_task_duration NUMBER; --Tasks duration
709 l_task_stage_id NUMBER; --Tasks stage id
710 l_task_offset NUMBER; --Number of hours offset based on stage.
711 l_start_date DATE; --Newly derived task start date
712 l_end_date DATE; --Newly derived task end date
713 l_temp_date DATE; --Temporary date for comparison purposes
714 l_child_task_id NUMBER; --Child task id
715 L_API_VERSION CONSTANT NUMBER := 1.0;
716 L_API_NAME CONSTANT VARCHAR2(30) := 'Adjust_Task_Times_Internal';
717 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
718 --
719 BEGIN
720 IF (l_log_procedure >= l_log_current_level) THEN
721 fnd_log.string(l_log_procedure,
722 L_DEBUG_KEY ||'.begin',
723 'At the start of PL SQL procedure. Visit Task Id = ' || p_task_id);
724 END IF;
725
726 -- 1. Fetch the task's old info and task related info
727 OPEN get_task_data_csr(p_task_id);
728 FETCH get_task_data_csr INTO l_old_task_start, l_old_task_end, l_task_dept_id,
729 l_task_duration, l_task_stage_id , l_task_offset;
730
731 --If it is a valid task
732 IF (get_task_data_csr%FOUND) THEN
733
734 --2. Check the task stage time value
735 --Get stage offset if stage id is not null
736 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009 :: Begin
737 -- If a task start date has been passed (for Non Routines), use that date as the baseline
738 -- to calculate the actual start time of the task.
739 IF (p_task_start_date IS NOT NULL) THEN
740 l_start_date := p_task_start_date;
741 ELSE
745 l_start_date := Compute_Date (l_start_date, G_VISIT_DEPT_ID, G_STAGES_TBL(l_task_stage_id));
742 l_start_date := G_VISIT_START_DATE;
743 END IF;
744 IF (l_task_stage_id IS NOT NULL) THEN
746 END IF;
747 -- SKPATHAK :: Bug 8343599 :: 14-APR-2009 :: End
748
749
750 --Adjust the start date to the task offset
751 l_start_date := Compute_Date (l_start_date, nvl(l_task_dept_id, G_VISIT_DEPT_ID), l_task_offset);
752
756 IF (get_tech_dependency_csr%FOUND) THEN
753 --3. Find the max end time of the dependent parents. Adjust based on parent
754 OPEN get_tech_dependency_csr(p_task_id);
755 FETCH get_tech_dependency_csr INTO l_temp_date;
757 l_temp_date := Compute_Date (l_temp_date, nvl(l_task_dept_id, G_VISIT_DEPT_ID),0);
758
759 --If shift adjusted dependency date is later than stage offset date
760 IF (l_temp_date > l_start_date) THEN
761 l_start_date := l_temp_date;
762 END IF;
763 END IF;
764 CLOSE get_tech_dependency_csr;
765
766 --Adjust start date for partially released/released tasks to sysdate start date
767 IF ((G_VISIT_STATUS = 'PARTIALLY RELEASED' OR G_VISIT_STATUS = 'RELEASED')
768 AND Fnd_Api.TO_BOOLEAN(G_RESET_SYSDATE_FLAG)) THEN
769 IF (l_start_date < sysdate AND p_task_start_date IS NULL) THEN
770 l_start_date := COMPUTE_DATE(sysdate, nvl(l_task_dept_id, G_VISIT_DEPT_ID),0);
771 END IF;
772 END IF;
773
774 --4. Now derive the end date
775 l_end_date := compute_date(l_start_date, l_task_dept_id, l_task_duration);
776 IF (l_log_statement >= l_log_current_level) THEN
777 fnd_log.string(l_log_statement,
778 L_DEBUG_KEY,
779 'Start Date = ' || l_start_date ||
780 '. End Date = ' || l_end_date);
781 END IF;
782
783 --5. Update the record if it has been changed and call child tasks
784 IF (l_old_task_start IS NULL OR
785 l_old_task_end IS NULL OR
786 l_start_date <> l_old_task_start OR
787 l_end_date <> l_old_task_end) THEN
788
789 --3d Update the tasks table with the new dates and times.
790 UPDATE AHL_VISIT_TASKS_B
791 SET START_DATE_TIME = l_start_date,
792 END_DATE_TIME = l_end_date
793 WHERE visit_task_id = p_task_id;
794
795 --If the end date has changed, recursively call the child tasks
796 -- To see if they need to be adjusted. Potentially expensive.
797 IF (l_old_task_end IS NULL OR
798 l_end_date <> l_old_task_end) THEN
799 OPEN get_child_dependency_csr(p_task_id);
800 LOOP
801 FETCH get_child_dependency_csr INTO l_child_task_id;
802 EXIT WHEN get_child_dependency_csr%NOTFOUND;
803 IF (l_log_statement >= l_log_current_level) THEN
804 fnd_log.string(l_log_statement,
805 L_DEBUG_KEY,
806 'Calling child task: ' || l_child_task_id);
807 END IF;
808 Adjust_Task_Times_Internal(l_child_task_id);
809 END LOOP;
810 CLOSE get_child_dependency_csr;
811 END IF;
812 END IF; --start time has changed;
813
814 END IF; --end found
815 CLOSE get_task_data_csr;
816
817 IF (l_log_procedure >= l_log_current_level) THEN
818 fnd_log.string(l_log_procedure,
819 L_DEBUG_KEY ||'.end',
820 'At the end of PL SQL procedure.');
821 END IF;
822
823 END Adjust_Task_Times_Internal;*/
824 -------------------------------------------------------------------
825 -- Procedure name : Calculate_Task_Times_For_Dept
826 -- Type : Private
827 -- Function : Recalculate all Visits for Dept for Task Times
828 -- Parameters :
829 --
830 -- Standard OUT Parameters :
831 -- x_return_status OUT VARCHAR2 Required
832 -- x_msg_count OUT NUMBER Required
833 -- x_msg_data OUT VARCHAR2 Required
834 --
835 -- Derive_Visit_Task_Times Parameters:
836 -- p_dept_id IN NUMBER Required
837 -- The dept id which need to have all its visits recalculated.
838 -- Need to be called from concurrent program due to performance issues.
839 --
840 -------------------------------------------------------------------
841 PROCEDURE Calculate_Task_Times_For_Dept
842 (
843 p_api_version IN NUMBER,
844 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
845 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
846 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
847 x_return_status OUT NOCOPY VARCHAR2,
851 IS
848 x_msg_count OUT NOCOPY NUMBER,
849 x_msg_data OUT NOCOPY VARCHAR2,
850 p_dept_id IN NUMBER)
852 --
853 CURSOR get_all_visits_csr(p_dept_id IN NUMBER) IS
854 SELECT visit_id
855 FROM AHL_VISITS_B
856 WHERE DEPARTMENT_ID = p_dept_id
857 UNION
858 SELECT visit_id
859 FROM AHL_VISIT_TASKS_B
860 WHERE department_id = p_dept_id;
861 --
862 -- Define local variables
863 l_api_version CONSTANT NUMBER := 1.0;
864 L_API_NAME CONSTANT VARCHAR2(30) := 'Calculate_Task_Times_For_Dept';
865 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
866 l_visit_id NUMBER;
867 --
868 BEGIN
869 SAVEPOINT Calculate_Times_for_dept_pvt;
870
871 IF (l_log_procedure >= l_log_current_level) THEN
872 fnd_log.string(l_log_procedure,
873 L_DEBUG_KEY ||'.begin',
874 'At the start of PL SQL procedure. Dept Id = ' || p_dept_id);
875 END IF;
876
877 -- Standard call to check for call compatibility
878 IF NOT Fnd_Api.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
879 G_PKG_NAME) THEN
880 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
881 END IF;
882
883 -- Initialize message list if p_init_msg_list is set to TRUE
884 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
885 Fnd_Msg_Pub.Initialize;
886 END IF;
887
888 -- Initialize API RETURN status to success
889 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
890
891 OPEN get_all_visits_csr(p_dept_id);
892 LOOP
893 FETCH get_all_visits_csr INTO l_visit_id;
894 EXIT WHEN get_all_visits_csr%NOTFOUND;
895
896 IF (l_log_statement >= l_log_current_level) THEN
897 fnd_log.string(l_log_statement,
898 L_DEBUG_KEY,
899 'Before calling CALCULATE_TASK_TIMES.');
900 END IF;
901
902 Calculate_Task_Times(p_api_version => 1.0,
903 p_init_msg_list => Fnd_Api.G_FALSE,
904 p_commit => Fnd_Api.G_FALSE,
905 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
906 x_return_status => x_return_status,
907 x_msg_count => x_msg_count,
911 IF (l_log_statement >= l_log_current_level) THEN
908 x_msg_data => x_msg_data,
909 p_visit_id => l_visit_id);
910
912 fnd_log.string(l_log_statement,
913 L_DEBUG_KEY,
914 'After calling CALCULATE_TASK_TIMES. RETURN Status = ' ||
915 x_return_status);
916 END IF;
917
918 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
919 IF (l_log_statement >= l_log_current_level) THEN
920 fnd_log.string(l_log_statement,
921 L_DEBUG_KEY,
922 'Errors from CALCULATE_TASK_TIMES. Message count: ' ||
923 x_msg_count || ', message data: ' || x_msg_data);
924 END IF;
925 CLOSE get_all_visits_csr;
926 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
927 RAISE FND_API.G_EXC_ERROR;
928 ELSE
929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 END IF;
931 END IF;
932 END LOOP;
933 CLOSE get_all_visits_csr;
934
935 -- Standard check of p_commit
936 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
937 COMMIT WORK;
938 END IF;
939
940 -- Standard call to get message count and if count is 1, get message info
941 Fnd_Msg_Pub.Count_And_Get
942 ( p_count => x_msg_count,
943 p_data => x_msg_data,
944 p_encoded => Fnd_Api.g_false
945 );
946
947 IF (l_log_procedure >= l_log_current_level) THEN
948 fnd_log.string(l_log_procedure,
949 L_DEBUG_KEY ||'.end',
950 'At the end of PL SQL procedure. RETURN Status = ' || x_return_status);
951 END IF;
952
953 EXCEPTION
954 WHEN Fnd_Api.G_EXC_ERROR THEN
955 ROLLBACK TO calculate_Times_for_dept_pvt;
956 x_return_status := Fnd_Api.G_RET_STS_ERROR;
957 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
958 p_data => x_msg_data,
959 p_encoded => Fnd_Api.g_false);
960
961 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
962 ROLLBACK TO calculate_Times_for_dept_pvt;
963 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
964 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
968 WHEN OTHERS THEN
965 p_data => x_msg_data,
966 p_encoded => Fnd_Api.g_false);
967
969 ROLLBACK TO calculate_Times_for_dept_pvt;
970 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
971 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
972 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
973 p_procedure_name => 'Calculate_Task_Times_For_Dept',
974 p_error_text => SUBSTR(SQLERRM,1,240));
975 END IF;
976 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
977 p_data => x_msg_data,
978 p_encoded => Fnd_Api.g_false);
979 END Calculate_Task_Times_For_Dept;
980
981 ----------------END OF TASK/VISIT SECTION ----------------------------------------
982
983
984 ----------------START OF SHIFT DATE TIME SECTION------------------------------------
985
986 -----------------------------------------------------------
987 -- Function Name: Compute_Date
988 -- Type: Private
989 -- Function: Converts date+duration and department into end date/time
990 -- Has to be super-efficient as this is called repeatedly.
991 -- Performance tuning is top priority for this method.
992 -- - no debug code or debug checks.
993 -- - code inlined as much as possible, use memory instead of queries
994 --
995 -- Compute_Date Parameters:
996 -- p_start_date IN NUMBER Required
997 -- The start date of the compute date value
998 -- p_dept_id IN NUMBER Required
999 -- The department id to calculate the date for
1000 -- p_duration IN NUMBER
1001 -- The duration in hours of the tasks offset
1002 -- RETURNs the department shift adjusted end date of p_start_date+p_duration
1003 --
1004 ---------------------------------------------------------
1005 FUNCTION Compute_Date(
1006 p_start_date date,
1007 p_dept_id number,
1008 p_duration number)
1009 RETURN DATE
1010 IS
1011 --
1012 --Get the number of holiday days that falls under the duration
1013 CURSOR get_holiday_csr(p_department_id IN NUMBER,
1014 p_start_date IN DATE,
1015 p_end_date IN DATE) IS
1016 SELECT COUNT(ex.EXCEPTION_DATE)
1017 FROM bom_calendar_exceptions ex, ahl_department_shifts dept
1018 WHERE ex.CALENDAR_CODE = dept.calendar_code
1019 AND EXCEPTION_TYPE = G_HOLIDAY_TYPE
1020 AND dept.department_id = p_department_id
1021 AND ex.exception_date > p_start_date
1022 AND ex.exception_date <= p_end_date;
1023 --
1024 l_start_date DATE; -- The p_start_date adjusted for different department.
1025 l_shift_start_date DATE; --The date that the shift started.
1026 l_end_date DATE; --The end date that is being calculated.
1027 l_end_hour NUMBER; --Ending hour
1028 l_curr_wday NUMBER; --current wday
1029 l_num_of_weekends NUMBER; --Number of weekends (multiple by off days)
1030 l_mod_days NUMBER; --Modulo workdays for given weekend
1031 l_days_to_add NUMBER; --Number of holiday days to add.
1032 l_shift_duration NUMBER; --The shift duration in UOM of day
1033 l_shift_off_time NUMBER; --The duration of shift off-time in UOM of day :: PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
1034 l_end_date_wo_offset DATE; --The end date calculated without hours part added to it. :: PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
1035 L_API_NAME CONSTANT VARCHAR2(30) := 'Compute_Date';
1036 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1037 --
1038 BEGIN
1039
1040 IF (l_log_procedure >= l_log_current_level) THEN
1041 fnd_log.string(l_log_procedure,
1042 L_DEBUG_KEY ||'.begin',
1043 'At the start of PL SQL function. Start Date = ' || p_start_date ||
1044 ', Department Id = ' || p_dept_id || ', Duration = ' || p_duration);
1045 END IF;
1046
1047 --1. Re-init the department shift time data if not the same
1048 -- as the current cached department
1049 IF (G_CURRENT_DEPT_ID IS NULL or
1050 p_dept_id <> G_CURRENT_DEPT_ID) THEN
1051 Init_Shift_Data(p_dept_id);
1052 G_CURRENT_DEPT_ID := p_dept_id;
1053 END IF;
1054
1055 --2. Adjust the start date to department shift
1056 l_start_date := p_start_date;
1057
1058 --Reset the start time to shift start hour if shift starts after current time.
1059 IF (G_SHIFT_END < G_SHIFT_START) THEN
1060 IF (l_start_date < TRUNC(l_start_date) + G_SHIFT_START AND
1061 l_start_date > TRUNC(l_start_date) + G_SHIFT_END) THEN-- Equal changed to special case handle :: PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
1062 l_start_date := trunc(l_start_date)+ G_SHIFT_START;
1063 ELSIF (l_start_date = TRUNC(l_start_date) + G_SHIFT_END) THEN
1064 l_start_date := trunc(l_start_date)+ G_SHIFT_START;
1065 END IF;
1066 ELSE
1067 IF (l_start_date < TRUNC(l_start_date) + G_SHIFT_START) THEN
1068 l_start_date := trunc(l_start_date)+ G_SHIFT_START;
1069 ELSIF (l_start_date >= TRUNC(l_start_date) + G_SHIFT_END) THEN
1070 --Add another day if shift starts tomorrow
1071 l_start_date := trunc(l_start_date)+ 1 + G_SHIFT_START;
1072 END IF;
1073 END IF;
1074
1075 --If it's an overnight shift and time is before shift ends on the same day,
1076 --the shift started a day earlier
1077 l_shift_start_date := get_shift_start_date(l_start_date);
1078
1079 l_curr_wday := MOD((TRUNC(l_shift_start_date) - G_CAL_START), (G_DAYS_ON + G_DAYS_OFF));
1080
1081 -- Add to a day_on (basically, passed the weekend)
1085
1082 IF(l_curr_wday +1 > G_DAYS_ON) THEN
1083 l_start_date := TRUNC(l_shift_start_date + (G_DAYS_ON+G_DAYS_OFF-l_curr_wday))+G_SHIFT_START;
1084 END IF;
1086 --If it's an overnight shift and time is before shift ends on the same day,
1087 --the shift started a day earlier
1088 l_shift_start_date := get_shift_start_date(l_start_date);
1089
1090 -- Not Day Off: Check if holiday and adjust based on holiday
1091 WHILE (Is_Dept_Holiday(l_shift_start_date)) LOOP
1092 l_shift_start_date := l_shift_start_date+1;
1093
1094 l_curr_wday := MOD((l_shift_start_date - G_CAL_START), (G_DAYS_ON + G_DAYS_OFF));
1095 -- Add to a day_on (basically, passed the weekend)
1096 IF(l_curr_wday +1 > G_DAYS_ON) THEN
1097 l_shift_start_date := l_shift_start_date + (G_DAYS_ON+G_DAYS_OFF-l_curr_wday);
1098 END IF;
1099
1100 l_start_date := TRUNC(l_shift_start_date) + G_SHIFT_START;
1101 END LOOP;
1102
1103 IF (p_duration = 0) THEN
1104 -- If duration is 0, RETURN the adjusted start date.
1105 IF (l_log_procedure >= l_log_current_level) THEN
1106 fnd_log.string(l_log_procedure,
1107 L_DEBUG_KEY ||'.end',
1108 'At the end of PL SQL function. Start Date = ' || l_start_date);
1109 END IF;
1110 RETURN l_start_date;
1111
1112 ELSE
1113
1114 --1. derive shift duration
1115 IF(G_SHIFT_END < G_SHIFT_START) THEN
1116 l_shift_duration := (24*G_SHIFT_END + 24 - 24*G_SHIFT_START)/24;
1117 l_shift_off_time := (G_SHIFT_START-G_SHIFT_END);-- PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
1118 ELSE
1119 l_shift_duration := (24*G_SHIFT_END - 24*G_SHIFT_START)/24;
1120 l_shift_off_time := 1-(G_SHIFT_END-G_SHIFT_START);-- PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
1121 END IF;
1122
1123 --2. Calculate the end date based on the start and duration. p_duration is in hours
1124 -- so must divide duration/24 to get fractions of a day
1125 --Both the p_duration/24 and the l_shift_duration are stored as fraction of a day
1126
1127 --If the border condition of p_duration/shift_duration is an exact number of days and
1128 -- l_start_date equals the shift start date. Then set end date to shift end time
1129 -- and date to n-1 days from start date
1130 --SKPATHAK :: Bug 8322149 :: 20-APR-2009
1131 --Changed the condition from (MOD(p_duration/24, l_shift_duration)=0 to (MOD(p_duration, l_shift_duration*24)=0
1132 -- PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
1133 IF (MOD(p_duration, l_shift_duration*24) =0
1134 AND l_start_date-TRUNC(l_start_date)= G_SHIFT_START) THEN
1135 --The end date calculated without hours part added to it.
1136
1137 l_end_date_wo_offset := l_start_date + (TRUNC((p_duration)/(l_shift_duration*24)))-1;
1138 --l_end_date_wo_offset := l_start_date + (TRUNC((p_duration/24)/l_shift_duration));
1139
1140 --The end date calculated with hours part added to it.
1141 l_end_date := l_end_date_wo_offset+l_shift_duration;
1142 ELSE
1143 --The end date calculated without hours part added to it.
1144 l_end_date_wo_offset := l_start_date + TRUNC((p_duration/24)/l_shift_duration);
1145 --The end date calculated with hours part added to it.
1146 l_end_date := l_end_date_wo_offset + MOD(p_duration/24, l_shift_duration);
1147 END IF;
1148
1149 --Get the end hour as fraction of a day
1150 l_end_hour := l_end_date - TRUNC(l_end_date);
1151
1152 /*
1153 --3. If new end hours goes beyond shift end hour, adjust til next shift
1154 IF (l_end_hour > G_SHIFT_END) THEN
1155 --Adjust end hour into the next shift. So if end_hour is 9pm and shift end is 5pm.
1156 -- Then must add 4 hours to shift start time. (all in fraction of a day)
1157 --This value could be > 1 for overnight shifts
1158 l_end_hour := G_SHIFT_START + (l_end_hour-G_SHIFT_END);
1159
1160 --Add a day to the end date if the shift ends after start time. (Regular shift)
1161 IF (G_SHIFT_START < G_SHIFT_END) THEN
1162 l_end_date := TRUNC(l_end_date)+1+l_end_hour;
1163 ELSE
1164 l_end_date := TRUNC(l_end_date)+l_end_hour;
1165 END IF;
1166 END IF;*/-- PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
1167
1168 --If end date that calculated after adding hours part falls beyond the shift end, then
1169 --add off time hours to shift the end time.
1170 -- PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
1171 IF l_end_date > (get_shift_start_date(l_end_date_wo_offset)+G_SHIFT_END) THEN
1172 l_end_date := l_end_date + l_shift_off_time;
1173 END IF;
1174
1175 --Add in the days off
1176 --First calculate number of days off
1177 l_num_of_weekends := TRUNC((get_shift_start_date(l_end_date)-get_shift_start_date(l_start_date))/G_DAYS_ON);
1178
1179 --This gets the number of extra days over week so that we can check if we get an extra weekend.
1180 l_mod_days :=MOD(get_shift_start_date(l_end_date)- get_shift_start_date(l_start_date), G_DAYS_ON);
1181
1182 --This is the weekday of the start day.
1183 l_curr_wday := MOD(get_shift_start_date(l_start_date) - G_CAL_START, G_DAYS_ON + G_DAYS_OFF);
1184
1185 --If the extra days pushes into an extra week, add 1 more weekend.
1186 IF(l_curr_wday+l_mod_days+1>G_DAYS_ON) THEN
1187 l_num_of_weekends := l_num_of_weekends +1;
1188 END IF;
1189 l_end_date := l_end_date + G_DAYS_OFF * l_num_of_weekends;
1190
1191 --Add in the holidays if not already added
1192 l_days_to_add := 0;
1193 OPEN get_holiday_csr(p_dept_id, get_shift_start_date(l_start_date),
1197
1194 get_shift_start_date(l_end_date));
1195 FETCH get_holiday_csr INTO l_days_to_add;
1196 CLOSE get_holiday_csr;
1198 WHILE (l_days_to_add > 0) LOOP
1199 --Increment and decrement the days
1200 l_end_date := l_end_date +1;
1201 l_days_to_add := l_days_to_add -1;
1202
1203 --Skip the weekends and the additional holidays
1204 l_curr_wday := MOD(get_shift_start_date(l_end_date)- G_CAL_START, G_DAYS_ON + G_DAYS_OFF);
1205
1206 IF (l_curr_wday+1 > G_DAYS_ON) THEN
1207 l_days_to_add := l_days_to_add + 1;
1208 ELSIF(Is_Dept_Holiday(get_shift_start_date(l_end_date))) THEN
1209 l_days_to_add := l_days_to_add + 1;
1210 END IF;
1211 END LOOP;
1212
1213 -- PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS
1214 --If end time is exact match to shift start time then based on shift timings move it to before day end time.
1215 IF (l_end_date = trunc(l_end_date)+G_SHIFT_START) THEN
1216 IF G_SHIFT_START < G_SHIFT_END THEN--Regular Shift
1217 l_end_date := trunc(l_end_date) -1 + G_SHIFT_END;
1218 ELSE -- Night Shift
1219 l_end_date := trunc(l_end_date) + G_SHIFT_END;
1220 END IF;
1221 END IF;
1222
1223 --RETURN the derived end date.
1224 IF (l_log_procedure >= l_log_current_level) THEN
1225 fnd_log.string(l_log_procedure,
1226 L_DEBUG_KEY ||'.end',
1227 'At the end of PL SQL function. End Date = ' || l_end_date);
1228 END IF;
1229 RETURN l_end_date;
1230
1231 END IF;
1232
1233 END Compute_Date;
1234
1235 ------------------------------------------------------------------------------
1236 -- Private function that initializes global variables for computing date/time
1237 ------------------------------------------------------------------------------
1238 PROCEDURE Init_Shift_Data(p_department_id number)
1239 IS
1240 -- Define local cursors
1241 --Find all shift information into the local variables
1242 CURSOR l_shift_info_csr(p_department_id IN NUMBER) IS
1243 SELECT TRUNC(cal.CALENDAR_START_DATE), TRUNC(cal.CALENDAR_END_DATE),
1244 times.FROM_TIME/G_SECS_IN_DAY, times.TO_TIME/G_SECS_IN_DAY,
1245 pattern.DAYS_ON, pattern.DAYS_OFF
1246 FROM bom_shift_times times, bom_workday_patterns pattern,
1247 bom_calendars cal, ahl_department_shifts dept
1248 WHERE dept.calendar_code = times.calendar_code
1249 AND dept.shift_num = times.shift_num
1250 AND pattern.calendar_code = dept.calendar_code
1251 AND pattern.shift_num = dept.shift_num
1252 AND pattern.seq_num = dept.seq_num -- PRAKKUM :: 01/03/2012 :: Bug 13793787
1253 AND cal.calendar_code = dept.calendar_code
1254 AND dept.department_id = p_department_id;
1255 --
1256 -- Fetch the holidays for the given department
1257 CURSOR l_exceptions_csr(p_department_id IN NUMBER) IS
1258 SELECT ex.EXCEPTION_DATE
1259 FROM bom_calendar_exceptions ex, ahl_department_shifts dept
1260 WHERE ex.CALENDAR_CODE = dept.calendar_code
1261 AND EXCEPTION_TYPE = G_HOLIDAY_TYPE
1262 AND dept.department_id = p_department_id
1263 ORDER BY EXCEPTION_DATE;
1264 --
1265 l_temp_index NUMBER;
1266 l_temp_date DATE;
1267 L_API_NAME CONSTANT VARCHAR2(30) := 'Init_Shift_Data';
1268 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1269 --
1270 BEGIN
1271
1272 IF (l_log_procedure >= l_log_current_level) THEN
1273 fnd_log.string(l_log_procedure,
1274 L_DEBUG_KEY ||'.begin',
1275 'At the start of PL SQL procedure. Department Id = ' || p_department_id);
1276 END IF;
1277 -- Get the department informations
1278 OPEN l_shift_info_csr(p_department_id);
1279 FETCH l_shift_info_csr INTO G_CAL_START, G_CAL_END,
1280 G_SHIFT_START, G_SHIFT_END,
1281 G_DAYS_ON, G_DAYS_OFF;
1282 IF (l_shift_info_csr%NOTFOUND) THEN
1283 --Fnd_Message.Set_Name('AHL','AHL_LTP_NO_SHIFT_FOR_DEPT');
1284 --Fnd_Message.Set_Token('DEPT_ID', p_department_id);
1285 --Fnd_Msg_Pub.ADD;
1286 CLOSE l_shift_info_csr;
1287 RETURN; --RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1291 -- Get the Exception (Holidays) days
1288 END IF;
1289 CLOSE l_shift_info_csr;
1290
1292 OPEN l_exceptions_csr(p_department_id);
1293 l_temp_index := 1;
1294 LOOP
1295 FETCH l_exceptions_csr INTO l_temp_date;
1296 EXIT WHEN l_exceptions_csr%NOTFOUND;
1297 G_EXCEPTION_DATES_TBL(l_temp_index) := TRUNC(l_temp_date);
1298 l_temp_index := l_temp_index + 1;
1299 END LOOP;
1300 CLOSE l_exceptions_csr;
1301
1302 IF (l_log_procedure >= l_log_current_level) THEN
1303 fnd_log.string(l_log_procedure,
1304 L_DEBUG_KEY ||'.end',
1305 'At the end of PL SQL procedure.');
1306 END IF;
1307
1308 END Init_shift_Data;
1309
1310 ----------------------------------------
1311 -- Function to determine if a specific date is a holiday
1312 ----------------------------------------
1313 FUNCTION Is_Dept_Holiday(l_curr_date DATE) RETURN BOOLEAN
1314 IS
1315 l_temp_date DATE := TRUNC(l_curr_date);
1316 BEGIN
1317 IF (G_EXCEPTION_DATES_TBL.COUNT = 0) THEN
1318 RETURN FALSE;
1319 END IF;
1320 --Iterate through the exception dates to make sure it's not a holiday.
1321 FOR i IN G_EXCEPTION_DATES_TBL.FIRST .. G_EXCEPTION_DATES_TBL.LAST LOOP
1322 IF (l_temp_date = G_EXCEPTION_DATES_TBL(i)) THEN
1323 RETURN TRUE;
1324 --Assumes the exception table is sorted by date
1325 ELSIF (l_temp_date < G_EXCEPTION_DATES_TBL(i)) THEN
1326 RETURN FALSE;
1327 END IF;
1328 END LOOP;
1329
1330 RETURN FALSE;
1331
1332 END Is_Dept_Holiday;
1333
1334 --------------------------------------------------------------------
1335 -- Function name : Get_task_duration
1336 -- Type : Public
1337 -- Purpose : To return the total duration of the task
1338 -- based on the resource requirements defined
1339 -- at the route/operation level.
1340 --
1341 -- Parameters :
1342 -- p_vst_task_qty : Visit task quantity
1343 -- p_route_id : Route id
1344 --
1345 -- 27/Nov/2007 Initial Version Sowmya
1346 --------------------------------------------------------------------
1347 Function Get_task_duration(
1348 p_vst_task_qty IN NUMBER,
1349 p_route_id IN NUMBER
1350 )
1351 RETURN NUMBER IS
1352
1356 WHERE OBJECT_ID = c_route_id
1353 CURSOR c_get_route_level_res_reqs (c_route_id IN NUMBER) IS
1354 SELECT COST_BASIS_ID, DURATION, RT_OPER_RESOURCE_ID
1355 FROM ahl_rt_oper_resources
1357 AND NVL(SCHEDULED_TYPE_ID,1) <> 2
1358 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_ROUTE;
1359
1360 get_route_level_res_reqs_rec c_get_route_level_res_reqs%ROWTYPE;
1361
1362 CURSOR c_get_op_level_res_reqs (c_oprn_id IN NUMBER) IS
1363 SELECT COST_BASIS_ID, DURATION, RT_OPER_RESOURCE_ID
1364 FROM ahl_rt_oper_resources
1365 WHERE OBJECT_ID = c_oprn_id
1366 AND NVL(SCHEDULED_TYPE_ID,1) <> 2
1367 AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION;
1368
1369 get_op_level_res_reqs_rec c_get_op_level_res_reqs%ROWTYPE;
1370
1371 CURSOR c_get_oprns (c_route_id IN NUMBER) IS
1372 SELECT RO.operation_id
1373 FROM ahl_operations_vl O, ahl_route_operations RO
1374 WHERE O.operation_id = RO.operation_id
1375 AND RO.route_id = c_route_id
1376 AND O.revision_status_code = 'COMPLETE'
1377 AND O.revision_number IN (SELECT max(revision_number)
1378 FROM ahl_operations_b_kfv
1379 WHERE concatenated_segments = O.concatenated_segments
1380 AND trunc(sysdate) between trunc(start_date_active) and
1381 trunc(NVL(end_date_active,SYSDATE+1))
1382 );
1383
1384 get_oprns_rec c_get_oprns%ROWTYPE;
1385
1386 CURSOR c_get_route_time_span (c_route_id IN NUMBER) IS
1387 SELECT time_span
1388 FROM ahl_routes_b
1389 WHERE ROUTE_ID = c_route_id;
1390
1391 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_task_duration';
1392 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
1393 l_vst_task_qty NUMBER := p_vst_task_qty;
1394 max_duration NUMBER := 0;
1395 ro_duration NUMBER := 0;
1396 total_opr_duration NUMBER := 0;
1397 rt_final_duration NUMBER := 0;
1398 rt_time_span NUMBER := 0;
1399
1400 BEGIN
1401 -- Log API entry point
1402 IF (l_log_procedure >= l_log_current_level) THEN
1403 fnd_log.string(l_log_procedure,
1404 L_DEBUG_KEY ||'.begin',
1405 'At the start of function. Route Id = ' || p_route_id || ' and Task Quantity = ' || p_vst_task_qty);
1406 END IF;
1407
1408 IF (p_route_id IS NULL) THEN
1409 IF (l_log_statement >= l_log_current_level)THEN
1410 fnd_log.string(l_log_statement,
1414 RETURN 0;
1411 L_DEBUG_KEY,
1412 'Route Id is null.');
1413 END IF;
1415 END IF;
1416
1417 OPEN c_get_route_time_span (p_route_id);
1418 FETCH c_get_route_time_span INTO rt_time_span;
1419 CLOSE c_get_route_time_span;
1420
1421 IF (l_log_statement >= l_log_current_level)THEN
1422 fnd_log.string(l_log_statement,
1423 L_DEBUG_KEY,
1424 'Route time span = ' || rt_time_span || ', Visit task Quantity = ' || l_vst_task_qty);
1425 END IF;
1426
1427 IF (nvl(l_vst_task_qty,1) = 1) THEN --serialized items
1428 IF (l_log_statement >= l_log_current_level)THEN
1429 fnd_log.string(l_log_statement,
1430 L_DEBUG_KEY,
1431 'serialized item. rt_time_span = ' || rt_time_span);
1432 END IF;
1433 RETURN rt_time_span;
1434 ELSIF (l_vst_task_qty > 1) THEN --non-serialized items
1435 OPEN c_get_route_level_res_reqs (p_route_id);
1436 FETCH c_get_route_level_res_reqs INTO get_route_level_res_reqs_rec;
1437 IF (c_get_route_level_res_reqs%FOUND) THEN --requirements exist at route level
1438 LOOP
1439 EXIT WHEN c_get_route_level_res_reqs%NOTFOUND;
1440 IF (nvl(get_route_level_res_reqs_rec.cost_basis_id,2) = 1) THEN --item based resource
1441 ro_duration := get_route_level_res_reqs_rec.duration * l_vst_task_qty;
1442 ELSE --lot based resource
1443 ro_duration := get_route_level_res_reqs_rec.duration ;
1444 END IF;
1445
1446 IF (ro_duration > max_duration) THEN
1447 max_duration := ro_duration;
1448 END IF;
1449
1450 FETCH c_get_route_level_res_reqs INTO get_route_level_res_reqs_rec;
1451 END LOOP;
1452 CLOSE c_get_route_level_res_reqs;
1453 rt_final_duration := max_duration;
1454 ELSE --requirements exist at operation level
1455 CLOSE c_get_route_level_res_reqs;
1456 OPEN c_get_oprns (p_route_id);
1457 LOOP
1458 FETCH c_get_oprns INTO get_oprns_rec;
1459 EXIT WHEN c_get_oprns%NOTFOUND;
1460
1461 max_duration := 0;
1462
1463 OPEN c_get_op_level_res_reqs (get_oprns_rec.operation_id);
1464 LOOP
1465 FETCH c_get_op_level_res_reqs INTO get_op_level_res_reqs_Rec;
1466 EXIT WHEN c_get_op_level_res_reqs%NOTFOUND;
1467 IF (nvl(get_op_level_res_reqs_Rec.cost_basis_id,2) = 1) THEN --item based resource
1468 ro_duration := get_op_level_res_reqs_Rec.duration * l_vst_task_qty;
1469 ELSE --lot based resource
1470 ro_duration := get_op_level_res_reqs_Rec.duration ;
1471 END IF;
1472
1473 IF (ro_duration > max_duration) THEN
1474 max_duration := ro_duration;
1475 END IF;
1476 END LOOP;
1477 CLOSE c_get_op_level_res_reqs;
1478 /*max durations of all operations are summed up since they are assumed to be performed sequentially.*/
1479 total_opr_duration := total_opr_duration + max_duration;
1480 END LOOP;
1481 CLOSE c_get_oprns;
1482
1483 rt_final_duration := total_opr_duration;
1484 END IF; --requirements exist at route level
1485
1486 IF (l_log_statement >= l_log_current_level)THEN
1487 fnd_log.string(l_log_statement,
1488 L_DEBUG_KEY,
1489 'Route Duration - ' ||rt_final_duration);
1490 END IF;
1491
1492 IF (rt_time_span >= rt_final_duration) THEN
1493 IF (l_log_procedure >= l_log_current_level) THEN
1494 fnd_log.string(l_log_procedure,
1495 L_DEBUG_KEY ||'.end',
1496 'At the end of PL SQL function. non-serialized item. rt_time_span = ' || rt_time_span);
1497 END IF;
1498 RETURN rt_time_span;
1499 ELSE
1500 IF (l_log_procedure >= l_log_current_level) THEN
1501 fnd_log.string(l_log_procedure,
1502 L_DEBUG_KEY ||'.end',
1503 'At the end of PL SQL function. non-serialized item. rt_final_duration = ' || rt_final_duration);
1504 END IF;
1505 RETURN rt_final_duration;
1506 END IF;
1507 END IF; --non-serialized items
1508
1509 END Get_task_duration;
1510
1514 p_end_date date,
1511 -- PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS :: START :: New function to calculate start date from given department,
1512 -- end date considering department shifts and holidays
1513 FUNCTION Compute_Start_Date(
1515 p_dept_id number,
1516 p_duration number)
1517 RETURN DATE
1518 IS
1519 --
1520 --Get the number of holiday days that falls under the duration
1521 CURSOR get_holiday_csr(c_department_id IN NUMBER,
1525 FROM bom_calendar_exceptions ex, ahl_department_shifts dept
1522 c_start_date IN DATE,
1523 c_end_date IN DATE) IS
1524 SELECT COUNT(ex.EXCEPTION_DATE)
1526 WHERE ex.CALENDAR_CODE = dept.calendar_code
1527 AND EXCEPTION_TYPE = G_HOLIDAY_TYPE
1528 AND dept.department_id = c_department_id
1529 AND ex.exception_date > c_start_date
1530 AND ex.exception_date <= c_end_date;
1531
1532 --
1533 l_start_date DATE; --The start date that is being calculated.
1534 l_shift_start_date DATE; --The date that the shift started.
1535 l_shift_end_date DATE; --The date that the shift ended.
1536 l_end_date DATE; --The p_start_date adjusted for different department.
1537 l_start_hour NUMBER; --Starting hour
1538 l_curr_wday NUMBER; --current wday
1539 l_num_of_weekends NUMBER; --Number of weekends (multiple by off days)
1540 l_mod_days NUMBER; --Modulo workdays for given weekend
1541 l_days_to_add NUMBER; --Number of holiday days to add.
1542 l_shift_duration NUMBER; --The shift duration in UOM of day
1543 l_shift_off_time NUMBER; --The duration of shift off-time in UOM of day
1544 l_start_date_wo_offset DATE; --The start date calculated without hours part subtracted from it.
1545 L_API_NAME CONSTANT VARCHAR2(30) := 'Compute_Start_Date';
1546 L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
1547 --
1548 BEGIN
1549
1550 IF (l_log_procedure >= l_log_current_level) THEN
1551 fnd_log.string(l_log_procedure,
1552 L_DEBUG_KEY ||'.start',
1553 'At the start of PL SQL function. ');
1554 END IF;
1555
1556 IF (l_log_statement >= l_log_current_level) THEN
1557 fnd_log.string(l_log_statement,
1558 L_DEBUG_KEY , ' Params: p_dept_id : '||p_dept_id||
1559 ', p_duration : '||p_duration||
1560 ', p_end_date : '||to_char(p_end_date,'dd:mm:yyyy hh24:mi:ss'));
1561 END IF;
1562
1563 --1. Re-init the department shift time data if not the same
1564 -- as the current cached department
1565 IF (G_CURRENT_DEPT_ID IS NULL or
1566 p_dept_id <> G_CURRENT_DEPT_ID) THEN
1567 Init_Shift_Data(p_dept_id);
1568 G_CURRENT_DEPT_ID := p_dept_id;
1569 END IF;
1570
1571 --2. Adjust the end date to department shift
1572 l_end_date := p_end_date;
1573
1574 --Reset the end time to shift end hour if shift ends after current time.
1575 IF (G_SHIFT_END < G_SHIFT_START) THEN
1576 IF (l_end_date <= TRUNC(l_end_date) + G_SHIFT_START AND
1577 l_end_date > TRUNC(l_end_date) + G_SHIFT_END) THEN
1578 l_end_date := trunc(l_end_date)+ G_SHIFT_END;
1579 END IF;
1580 ELSE
1581 IF (l_end_date <= TRUNC(l_end_date) + G_SHIFT_START) THEN
1582 l_end_date := trunc(l_end_date) - 1 + G_SHIFT_END;
1583 ELSIF (l_end_date > TRUNC(l_end_date) + G_SHIFT_END) THEN
1584 l_end_date := trunc(l_end_date)+ G_SHIFT_END;
1585 END IF;
1586 END IF;
1587
1588 --If it's an overnight shift and time is before shift ends on the same day,
1589 --the shift started a day earlier
1590 l_shift_start_date := get_shift_start_date(l_end_date);
1591 l_shift_end_date := get_shift_end_date(l_end_date);
1592
1593 l_curr_wday := MOD((TRUNC(l_shift_start_date) - G_CAL_START), (G_DAYS_ON + G_DAYS_OFF));
1594
1595 -- Subtract to a day_on (basically, passed the weekend)
1596 IF(l_curr_wday +1 > G_DAYS_ON) THEN
1597 l_end_date := TRUNC(l_shift_end_date - (l_curr_wday+1-G_DAYS_ON))+G_SHIFT_END;
1598 END IF;
1599
1600 --If it's an overnight shift and time is before shift ends on the same day,
1601 --the shift started a day earlier
1602 l_shift_start_date := get_shift_start_date(l_end_date);
1603 l_shift_end_date := get_shift_end_date(l_end_date);
1604
1605 -- Not Day Off: Check if holiday and adjust based on holiday
1606 WHILE (Is_Dept_Holiday(l_shift_start_date)) LOOP
1607 l_shift_start_date := l_shift_start_date - 1;
1608 l_shift_end_date := l_shift_end_date - 1;
1609
1610 l_curr_wday := MOD((l_shift_start_date - G_CAL_START), (G_DAYS_ON + G_DAYS_OFF));
1611 -- subtract to a day_on (basically, passed the weekend)
1612 IF(l_curr_wday +1 > G_DAYS_ON) THEN
1613 l_shift_start_date := l_shift_end_date - (l_curr_wday+1-G_DAYS_ON);
1614 END IF;
1615
1616 l_end_date := TRUNC(l_shift_start_date) + G_SHIFT_END;
1617 END LOOP;
1618
1619 IF (p_duration = 0) THEN
1620 -- If duration is 0, RETURN the adjusted start date.
1621 IF (l_log_procedure >= l_log_current_level) THEN
1622 fnd_log.string(l_log_procedure,
1623 L_DEBUG_KEY ||'.end',
1624 'At the end of PL SQL function. Start Date = ' || to_char(l_end_date,'dd:mm:yyyy hh24:mi:ss'));
1625 END IF;
1626 RETURN l_end_date;
1627
1628 ELSE
1629
1630 --1. Derive shift duration and shift off time.
1631 IF(G_SHIFT_END < G_SHIFT_START) THEN
1632 l_shift_duration := (24*G_SHIFT_END + 24 - 24*G_SHIFT_START)/24;
1633 l_shift_off_time := (G_SHIFT_START-G_SHIFT_END);
1634 ELSE
1635 l_shift_duration := (24*G_SHIFT_END - 24*G_SHIFT_START)/24;
1636 l_shift_off_time := 1-(G_SHIFT_END-G_SHIFT_START);
1637 END IF;
1638
1639 IF (l_log_statement >= l_log_current_level) THEN
1640 fnd_log.string(l_log_statement,
1641 L_DEBUG_KEY , ' l_shift_off_time : '||l_shift_off_time||' l_shift_duration:'||l_shift_duration);
1642 END IF;
1643
1647 --The start date calculated without hours part subtracted from it.
1644 --Subtract duration days considering shift timings from end date
1645 IF (MOD(p_duration, l_shift_duration*24) =0
1646 AND l_end_date-TRUNC(l_end_date)= G_SHIFT_END) THEN
1648 l_start_date_wo_offset := l_end_date - (TRUNC((p_duration)/(l_shift_duration*24)))+1;
1649 --The start date calculated with hours part subtracted from it.
1650 l_start_date := l_start_date_wo_offset - l_shift_duration;
1651 ELSE
1652 --The start date calculated without hours part subtracted from it.
1653 l_start_date_wo_offset := l_end_date - TRUNC((p_duration/24)/l_shift_duration);
1657
1654 --The start date calculated with hours part subtracted from it.
1655 l_start_date := l_start_date_wo_offset - MOD(p_duration/24, l_shift_duration);
1656 END IF;
1658 IF (l_log_statement >= l_log_current_level) THEN
1659 fnd_log.string(l_log_statement,
1660 L_DEBUG_KEY , ' l_start_date : '||to_char(l_start_date,'dd:mm:yyyy hh24:mi:ss')
1661 ||' l_start_date_wo_offset : '||to_char(l_start_date_wo_offset,'dd:mm:yyyy hh24:mi:ss'));
1662 END IF;
1663
1664 --Get the start hour as fraction of a day
1665 l_start_hour := l_start_date - TRUNC(l_start_date);
1666
1667 --If start date that calculated subtracting hours part falls beyond the shift start, then
1668 --subtract off time hours to shift the start time.
1669 IF l_start_date < (get_shift_start_date(l_start_date_wo_offset)+G_SHIFT_START) THEN
1670 l_start_date := l_start_date - l_shift_off_time;
1671 END IF;
1672
1673 --Add in the days off
1674 --First calculate number of days off in terms of weekends
1675 l_num_of_weekends := TRUNC((get_shift_start_date(l_end_date)-get_shift_start_date(l_start_date))/G_DAYS_ON);
1676
1677 --This gets the number of extra days over week so that we can check if we get an extra weekend.
1678 l_mod_days :=MOD(get_shift_start_date(l_end_date)- get_shift_start_date(l_start_date), G_DAYS_ON);
1679
1680 --Subtract weekends days_off
1681 l_start_date := l_start_date - ( G_DAYS_OFF * l_num_of_weekends );
1682
1683 l_curr_wday := (MOD(get_shift_start_date(l_start_date) - G_CAL_START, G_DAYS_ON + G_DAYS_OFF));
1684
1685 --If the extra days pushes into an extra week, subtract 1 more weekend.
1686 IF(l_curr_wday+l_mod_days+1>G_DAYS_ON) THEN
1687 l_start_date := l_start_date - ( G_DAYS_OFF * 1 );
1688 END IF;
1689
1690 --Subtract in the holidays if not already subtracted
1691 l_days_to_add := 0;
1692 OPEN get_holiday_csr(p_dept_id, get_shift_start_date(l_start_date),get_shift_start_date(l_end_date));
1693 FETCH get_holiday_csr INTO l_days_to_add;
1694 CLOSE get_holiday_csr;
1695
1696 IF (l_log_statement >= l_log_current_level) THEN
1697 fnd_log.string(l_log_statement,
1698 L_DEBUG_KEY , ' l_days_to_add : '||l_days_to_add);
1699 END IF;
1700
1701 WHILE (l_days_to_add > 0) LOOP
1702 --Increment and decrement the days
1703 l_start_date := l_start_date - 1;
1704 l_days_to_add := l_days_to_add - 1;
1705
1706 --Skip the weekends and the additional holidays
1707 l_curr_wday := MOD(get_shift_start_date(l_start_date)- G_CAL_START, G_DAYS_ON + G_DAYS_OFF);
1708
1709 IF (l_curr_wday+1 > G_DAYS_ON) THEN
1710 l_days_to_add := l_days_to_add + 1;
1711 ELSIF(Is_Dept_Holiday(get_shift_start_date(l_start_date))) THEN
1712 l_days_to_add := l_days_to_add + 1;
1713 END IF;
1714 END LOOP;
1715
1716 IF (l_log_statement >= l_log_current_level) THEN
1717 fnd_log.string(l_log_statement,
1718 L_DEBUG_KEY , ' Before exact match correction l_start_date : '||to_char(l_start_date,'dd:mm:yyyy hh24:mi:ss')||
1719 ' trunc(l_start_date)+G_SHIFT_END : '||to_char(trunc(l_start_date)+G_SHIFT_END,'dd:mm:yyyy hh24:mi:ss'));
1720 END IF;
1721
1722 --If start time is exact match to shift end time then based on shift timings move it to next day start date time.
1723 IF (l_start_date = trunc(l_start_date)+G_SHIFT_END) THEN
1727 l_start_date := trunc(l_start_date) + G_SHIFT_START;
1724 IF G_SHIFT_START < G_SHIFT_END THEN--Regular Shift
1725 l_start_date := trunc(l_start_date) + 1 + G_SHIFT_START;
1726 ELSE -- Night Shift
1728 END IF;
1729 END IF;
1730
1731 IF (l_log_procedure >= l_log_current_level) THEN
1732 fnd_log.string(l_log_procedure,
1733 L_DEBUG_KEY ||'.end',
1734 'At the end of PL SQL function. Start Date = ' || to_char(l_start_date,'dd:mm:yyyy hh24:mi:ss'));
1735 END IF;
1736
1737 RETURN l_start_date;
1738
1739 END IF;
1740
1741 END Compute_Start_Date;
1742 -- PRAKKUM :: AVF :: 15/11/2011 :: Bug# 13416865 - AVF ENHANCEMENTS :: END
1743
1744 END AHL_VWP_TIMES_PVT;
[Home] [Help]