APPS.AHL_VWP_TIMES_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 128

 SELECT START_DATE_TIME, STATUS_CODE
 FROM AHL_VISITS_B
 WHERE VISIT_ID = c_visit_id;

Line: 134

 SELECT ACTUAL_START_DATE
 FROM AHL_WORKORDERS
 WHERE VISIT_ID = c_visit_id
  AND VISIT_TASK_ID IS NULL
  AND MASTER_WORKORDER_FLAG = 'Y';

Line: 212

SELECT CLOSE_DATE_TIME, START_DATE_TIME, DEPARTMENT_ID, status_code
FROM AHL_VISITS_B
WHERE VISIT_ID = c_id;

Line: 217

 SELECT MAX(end_date_time)
 FROM AHL_VISIT_TASKS_B
 WHERE VISIT_ID = c_visit_id
 AND STATUS_CODE NOT IN ('RELEASED','DELETED'); -- Modified by rnahata for Bug 6369279

Line: 225

    SELECT MAX(WIP.SCHEDULED_COMPLETION_DATE)
    FROM AHL_WORKORDERS WO,
         WIP_DISCRETE_JOBS WIP,
         AHL_VISIT_TASKS_B VT
    WHERE vt.status_code = 'RELEASED'
    AND vt.VISIT_ID = c_visit_id
    AND vt.visit_task_id = wo.VISIT_TASK_ID
--  AND wo.MASTER_WORKORDER_FLAG = 'N'
    AND WIP.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
    AND wo.status_code not in ('22','7');

Line: 239

 SELECT ACTUAL_END_DATE
 FROM AHL_WORKORDERS
 WHERE VISIT_ID = c_visit_id
  AND VISIT_TASK_ID IS NULL
  AND MASTER_WORKORDER_FLAG = 'Y';

Line: 246

 SELECT count(*)
 FROM AHL_VISIT_TASKS_B
 WHERE VISIT_ID = c_visit_id
 AND STATUS_CODE <>'DELETED';

Line: 378

SELECT v.start_date_time, v.department_id
FROM AHL_VISITS_B v, AHL_DEPARTMENT_SHIFTS shifts
WHERE v.department_id = shifts.department_id
AND v.visit_id = p_visit_id;

Line: 386

SELECT stage_id, duration
--, sum(duration) over(order by stage_num) CUMUL_DURATION
from ahl_vwp_stages_vl
where visit_id = p_visit_id
order by stage_num;

Line: 395

SELECT dtl.visit_task_id,
       max(dtl.task_level) task_level,
       max(NVL(vtsk.start_from_hour,0)) start_from_hour,
       /*sowsubra*/
       max(NVL(vtsk.duration, NVL(Get_task_duration(vtsk.quantity, routes.route_id), 0))) duration,
       max(vtsk.stage_id) stage_id,
       max(vtsk.department_id) department_id
FROM AHL_VISIT_TASKS_B vtsk,
         ahl_routes_app_v routes, ahl_mr_routes_app_v mr,
   (SELECT visit_task_id, level+1 task_level
     FROM ahl_task_links tl
     WHERE visit_task_id in (
       SELECT visit_task_id from ahl_visit_tasks_b vt
         where (VT.STATUS_CODE IS NULL OR (VT.STATUS_CODE  <> 'DELETED' AND VT.STATUS_CODE  <> 'RELEASED')))
     START WITH tl.parent_task_id in
      (SELECT visit_task_id from ahl_visit_tasks_b vt
         where  vt.visit_id=p_visit_id
           AND  vt.visit_task_id not in (SELECT visit_task_id from ahl_task_links)
           and (VT.STATUS_CODE IS NULL OR (VT.STATUS_CODE  <> 'DELETED' AND VT.STATUS_CODE  <> 'RELEASED'))
           AND vt.TASK_TYPE_CODE <> 'SUMMARY')
     CONNECT BY tl.parent_task_id = prior tl.visit_task_id
     union
     SELECT vt.visit_task_id, 1 task_level
     FROM ahl_visit_tasks_b vt
     WHERE vt.visit_task_id not in (SELECT visit_task_id from ahl_task_links)
     AND vt.visit_id =p_visit_id
     AND (VT.STATUS_CODE IS NULL OR (VT.STATUS_CODE  <> 'DELETED' AND VT.STATUS_CODE  <> 'RELEASED'))
     AND vt.TASK_TYPE_CODE <> 'SUMMARY'
 ) dtl
 WHERE dtl.visit_task_id = vtsk.visit_task_id
   AND routes.route_id (+)= mr.route_id
   AND mr.mr_route_id (+) = vtsk.mr_route_id
 group by dtl.visit_task_id
 order by task_level;

Line: 434

SELECT max(vt.end_date_time)
FROM AHL_VISIT_TASKS_B vt, AHL_TASK_LINKS tl
WHERE vt.visit_task_id = tl.parent_task_id
AND tl.visit_task_id = p_task_id;

Line: 443

    SELECT vt.VISIT_TASK_ID,
          WIP.SCHEDULED_START_DATE,
          WIP.SCHEDULED_COMPLETION_DATE
    FROM AHL_WORKORDERS WO,
         WIP_DISCRETE_JOBS WIP,
         AHL_VISIT_TASKS_B VT
    WHERE vt.status_code = 'RELEASED'
    AND   vt.VISIT_ID = P_VISIT_ID
    AND   vt.visit_task_id = wo.VISIT_TASK_ID(+)
--  AND   wo.MASTER_WORKORDER_FLAG = 'N'
    AND   WIP.WIP_ENTITY_ID(+) = WO.WIP_ENTITY_ID
    AND   wo.status_code not in ('22','7');

Line: 499

       UPDATE AHL_VISIT_TASKS_B
       SET START_DATE_TIME = Null,
           END_DATE_TIME = Null,
           object_version_number = object_version_number +1
       WHERE visit_id = p_visit_id
       AND nvl(Status_Code, 'X') <>'DELETED';

Line: 529

       UPDATE AHL_VISIT_TASKS_B
       SET START_DATE_TIME = Null,
           END_DATE_TIME = Null,
           object_version_number = object_version_number +1
       WHERE visit_id = p_visit_id
       AND nvl(Status_Code, 'X') <>'DELETED';

Line: 579

      UPDATE AHL_VISIT_TASKS_B
      SET START_DATE_TIME = l_start_date,
          END_DATE_TIME = l_end_date
      WHERE visit_task_id = l_task_data_rec.visit_task_id;

Line: 591

       UPDATE AHL_VISIT_TASKS_B
       SET START_DATE_TIME = l_wo_data_rec.SCHEDULED_START_DATE,
           END_DATE_TIME = l_wo_data_rec.SCHEDULED_COMPLETION_DATE
       WHERE visit_task_id = l_wo_data_rec.visit_task_id;

Line: 675

SELECT v.start_date_time, v.department_id, v.visit_id, v.status_code
FROM AHL_VISITS_B v, AHL_VISIT_TASKS_B vt, AHL_DEPARTMENT_SHIFTS dept
WHERE v.visit_id = vt.visit_id
AND dept.department_id = v.department_id
AND vt.visit_task_id = p_task_id;

Line: 684

SELECT st.stage_id, st.duration
--,  sum(st.duration) over(order by st.stage_num) CUMUL_DURATION
from ahl_vwp_stages_vl st, ahl_visit_tasks_b vt
where st.visit_id = vt.visit_id
AND vt.visit_task_id = p_task_id
order by st.stage_num;

Line: 811

SELECT vt.start_date_time, vt.end_date_time,
   nvl(vt.department_id, v.department_id),
   /*B6182718 - sowsubra*/
   nvl(vt.duration, NVL(Get_task_duration(vt.quantity, routes.route_id), 0)) duration,
   vt.stage_id,
   nvl(vt.start_from_hour, 0)
FROM AHL_VISIT_TASKS_B vt, AHL_VISITS_B v,
     ahl_routes_app_v routes, ahl_mr_routes_app_v mr
WHERE vt.visit_id = v.visit_id
AND routes.route_id (+) = mr.route_id
AND mr.mr_route_id (+)= vt.mr_route_id
AND  vt.visit_task_id = p_task_id
AND vt.task_type_code <> 'SUMMARY'
AND (VT.STATUS_CODE IS NULL OR VT.STATUS_CODE <> 'DELETED');

Line: 829

SELECT max(vt.end_date_time)
FROM AHL_VISIT_TASKS_B vt, AHL_TASK_LINKS tl
WHERE vt.visit_task_id = tl.parent_task_id
AND tl.visit_task_id = p_task_id;

Line: 836

SELECT visit_task_id
FROM AHL_TASK_LINKS
WHERE parent_task_id = p_task_id;

Line: 917

       UPDATE AHL_VISIT_TASKS_B
       SET START_DATE_TIME = l_start_date,
           END_DATE_TIME = l_end_date
       WHERE visit_task_id = p_task_id;

Line: 981

SELECT visit_id
FROM AHL_VISITS_B
WHERE DEPARTMENT_ID = p_dept_id
UNION
SELECT visit_id
FROM AHL_VISIT_TASKS_B
WHERE department_id = p_dept_id;

Line: 1143

    SELECT COUNT(ex.EXCEPTION_DATE)
    FROM bom_calendar_exceptions ex, ahl_department_shifts dept
    WHERE ex.CALENDAR_CODE = dept.calendar_code
    AND EXCEPTION_TYPE = G_HOLIDAY_TYPE
    AND dept.department_id = p_department_id
    AND ex.exception_date > p_start_date
    AND ex.exception_date <= p_end_date;

Line: 1334

   SELECT TRUNC(cal.CALENDAR_START_DATE), TRUNC(cal.CALENDAR_END_DATE),
          times.FROM_TIME/G_SECS_IN_DAY, times.TO_TIME/G_SECS_IN_DAY,
          pattern.DAYS_ON, pattern.DAYS_OFF
    FROM bom_shift_times times, bom_workday_patterns pattern,
         bom_calendars cal, ahl_department_shifts dept
     WHERE dept.calendar_code = times.calendar_code
     AND dept.shift_num = times.shift_num
     AND pattern.calendar_code = dept.calendar_code
     AND pattern.shift_num = dept.shift_num
     AND cal.calendar_code = dept.calendar_code
     AND dept.department_id = p_department_id;

Line: 1348

    SELECT ex.EXCEPTION_DATE
    FROM bom_calendar_exceptions ex, ahl_department_shifts dept
    WHERE ex.CALENDAR_CODE = dept.calendar_code
    AND EXCEPTION_TYPE = G_HOLIDAY_TYPE
    AND dept.department_id = p_department_id
    ORDER BY EXCEPTION_DATE;

Line: 1444

  SELECT COST_BASIS_ID, DURATION, RT_OPER_RESOURCE_ID
  FROM ahl_rt_oper_resources
  WHERE OBJECT_ID = c_route_id
  AND NVL(SCHEDULED_TYPE_ID,1) <> 2
  AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_ROUTE;

Line: 1453

  SELECT COST_BASIS_ID, DURATION, RT_OPER_RESOURCE_ID
  FROM ahl_rt_oper_resources
  WHERE OBJECT_ID = c_oprn_id
  AND NVL(SCHEDULED_TYPE_ID,1) <> 2
  AND ASSOCIATION_TYPE_CODE = G_ASSOC_TYPE_OPERATION;

Line: 1462

  SELECT RO.operation_id
  FROM ahl_operations_vl O, ahl_route_operations RO
  WHERE O.operation_id = RO.operation_id
  AND RO.route_id = c_route_id
  AND O.revision_status_code = 'COMPLETE'
  AND O.revision_number IN (SELECT max(revision_number)
                            FROM ahl_operations_b_kfv
                            WHERE concatenated_segments = O.concatenated_segments
                            AND trunc(sysdate) between trunc(start_date_active) and
                            trunc(NVL(end_date_active,SYSDATE+1))
                            );

Line: 1477

  SELECT time_span
  FROM ahl_routes_b
  WHERE ROUTE_ID = c_route_id;