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