[Home] [Help]
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: --
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'
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:
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: -------------------------------------------------------------------
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 (
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
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')
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'
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:
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'
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
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
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:
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:
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: --
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
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
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
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:
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;