APPS.AHL_VWP_TIMES_PVT SQL Statements

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

Line: 152

 SELECT START_DATE_TIME, STATUS_CODE
 FROM AHL_VISITS_B
 WHERE VISIT_ID = c_visit_id;

Line: 158

 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: 233

/*SELECT WIP.scheduled_completion_date
FROM WIP_DISCRETE_JOBS WIP, AHL_WORKORDERS WO
WHERE WO.VISIT_ID = c_id
AND WO.visit_task_id IS NULL
AND WO.master_workorder_flag = 'Y'
AND WO.wip_entity_id = WIP.wip_entity_id;*/

Line: 239

SELECT max(WIP.scheduled_completion_date) AS scheduled_completion_date
FROM WIP_DISCRETE_JOBS WIP, AHL_WORKORDERS WO
WHERE WO.VISIT_ID = c_id
AND WO.STATUS_CODE NOT IN ('7', '22') -- Exclude canceled and deleted work orders
AND WO.wip_entity_id = WIP.wip_entity_id;

Line: 305

SELECT visit_task_id, stage_id
FROM AHL_VISIT_TASKS_B
WHERE visit_id =  x_visit_id
AND status_code = 'PLANNING';*/

Line: 310

SELECT tsk.visit_task_id visit_task_id, tsk.stage_id stage_id
FROM AHL_VISIT_TASKS_B tsk
, ahl_workorders wo
WHERE tsk.visit_id =  x_visit_id
AND tsk.status_code = 'PLANNING'
AND tsk.visit_task_id = wo.visit_task_id(+)
AND ((wo.workorder_id is NULL)
-- SKPATHAK :: Bug 13890788 :: Do schedule the future dated planning tasks
    OR (tsk.start_date_time > SYSDATE));

Line: 325

SELECT past_dated_visit_flag, start_date_time, close_date_time
FROM AHL_VISITS_B
WHERE visit_id = x_visit_id;

Line: 334

SELECT planned_start_date, planned_end_date, stage_id
from ahl_vwp_stages_b
where visit_id = c_visit_id;

Line: 420

     UPDATE AHL_VISIT_TASKS_B
     SET START_DATE_TIME = l_task_start_date,
         END_DATE_TIME = l_task_end_date
     WHERE visit_task_id = l_task_rec.visit_task_id
     AND PAST_TASK_START_DATE IS NULL;--Line Added by jrotich for fixing bug #13518519

Line: 510

SELECT v.visit_id, v.past_dated_visit_flag, v.status_code, v.start_date_time, v.close_date_time
FROM AHL_VISITS_B v, AHL_VISIT_TASKS_B vt
WHERE v.visit_id = vt.visit_id
AND vt.visit_task_id = p_task_id;*/

Line: 515

SELECT v.visit_id, v.past_dated_visit_flag, v.status_code, v.start_date_time, v.close_date_time
FROM AHL_VISITS_B v, AHL_VISIT_TASKS_B vt
, ahl_workorders wo
WHERE v.visit_id = vt.visit_id
AND vt.visit_task_id = p_task_id
AND vt.visit_task_id = wo.visit_task_id(+)
AND ((wo.workorder_id is NULL)
-- SKPATHAK :: Bug 13890788 :: Do schedule the future dated planning tasks
    OR (vt.start_date_time > SYSDATE));

Line: 533

SELECT st.planned_start_date, st.planned_end_date
from ahl_vwp_stages_b st, ahl_visit_tasks_b vt
where st.stage_id = vt.stage_id
AND vt.visit_task_id = p_task_id;

Line: 611

     UPDATE AHL_VISIT_TASKS_B
     SET START_DATE_TIME = l_task_start_date,
       END_DATE_TIME = l_task_end_date
     WHERE visit_task_id = p_task_id;

Line: 677

SELECT vt.start_date_time, vt.end_date_time,
   nvl(vt.department_id, v.department_id),
   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: 694

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: 701

SELECT visit_task_id
FROM AHL_TASK_LINKS
WHERE parent_task_id = p_task_id;

Line: 790

       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: 854

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: 1016

    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: 1243

   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 pattern.seq_num = dept.seq_num -- PRAKKUM :: 01/03/2012 :: Bug 13793787
     AND cal.calendar_code = dept.calendar_code
     AND dept.department_id = p_department_id;

Line: 1258

    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: 1354

  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: 1363

  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: 1372

  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: 1387

  SELECT time_span
  FROM ahl_routes_b
  WHERE ROUTE_ID = c_route_id;

Line: 1524

    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 = c_department_id
    AND ex.exception_date > c_start_date
    AND ex.exception_date <= c_end_date;