[Home] [Help]
143: select vt.stage_id,
144: -- sum(s.duration) over(order by s.stage_num) CUMUL_DURATION,
145: -- min(vt.start_date_time) start_date_time,
146: max(vt.end_date_time) end_date_time
147: from ahl_visit_tasks_b vt
148: where vt.stage_id = C_STAGE_ID
149: AND nvl(vt.status_code,'X') <> 'DELETED'
150: group by vt.stage_id;
151:
589: /*CURSOR C_JOB(C_VISIT_ID NUMBER , C_STAGE_NUM NUMBER )
590: IS
591: select 'x' from ahl_workorders_v
592: where visit_task_id in
593: (select DISTINCT VISIT_TASK_ID from AHL_VISIT_TASKS_B
594: where visit_id = C_VISIT_ID
595: and STAGE_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B WHERE stage_num > C_STAGE_NUM
596: AND VISIT_ID = C_VISIT_ID))
597: and ( job_status_code =3 or firm_planned_flag = 1 );
1041: l_stage_rec c_stage_data%rowtype;
1042:
1043: cursor c_stage_task(c_stage_id number)
1044: IS
1045: select 'x' from ahl_visit_tasks_b
1046: where stage_id = c_stage_id
1047: and nvl(status_code,'X')<>'DELETED';
1048:
1049:
1264: ( SELECT
1265: nvl(max(stage_num),1) max_parent_stage_num
1266: FROM
1267: ahl_vwp_stages_b s,
1268: AHL_VISIT_TASKS_b t
1269: WHERE
1270: s.stage_id = t.stage_id and
1271: t.VISIT_task_id IN
1272: ( SELECT PARENT_TASK_ID FROM AHL_TASK_LINKS WHERE VISIT_TASK_ID = P_VISIT_TASK_ID )) ,
1273: ( SELECT
1274: nvl(min(stage_num),l_max_stage_num) min_child_stage_num
1275: FROM
1276: ahl_vwp_stages_b s,
1277: AHL_VISIT_TASKS_b t
1278: WHERE
1279: s.stage_id = t.stage_id and
1280: t.VISIT_task_id IN
1281: ( SELECT visit_TASK_ID FROM AHL_TASK_LINKS WHERE parent_TASK_ID = P_VISIT_TASK_ID ));
1469:
1470: -- Commented by amagrawa based on review comments.
1471: -- To find all departments from a visit's tasks table
1472: /* CURSOR c_task (x_id IN NUMBER) IS
1473: SELECT DEPARTMENT_ID FROM AHL_VISIT_TASKS_B WHERE VISIT_ID = x_id
1474: AND NVL(STATUS_CODE,'X') <> 'DELETED' AND DEPARTMENT_ID IS NOT NULL;
1475: c_task_rec c_task%ROWTYPE;
1476: */
1477: -- To find only those routes which are there in tasks table but not in route table for a visit
1476: */
1477: -- To find only those routes which are there in tasks table but not in route table for a visit
1478: -- Changed by amagrawa to improve performance.
1479: CURSOR c_route_chk(x_id IN NUMBER) IS
1480: SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_VISIT_TASKS_B TSK
1481: WHERE VISIT_ID = x_id AND MR_Route_ID IS NOT NULL
1482: AND NOT EXISTS
1483: (SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_MR_ROUTES_V MR
1484: where MR.mr_route_id =TSK.mr_route_id) and rownum=1;
1488: -- Added by amagrawa based on review comments
1489: -- To find if the all visit tasks dept has department shifts defined
1490: CURSOR c_task_dep_exist (x_visit_id IN NUMBER) IS
1491: SELECT 1 from dual WHERE exists(
1492: SELECT visit_task_id from ahl_visit_tasks_b
1493: Where department_id is not null
1494: and visit_id = x_visit_id
1495: and nvl(status_code,'X')<>'DELETED'
1496: and department_id not in (select department_id from ahl_department_shifts)