[Home] [Help]
264: select vt.stage_id,
265: --sum(s.duration) over(order by s.stage_num) CUMUL_DURATION,
266: --min(vt.start_date_time) start_date_time,
267: max(vt.end_date_time) end_date_time
268: from ahl_visit_tasks_b vt
269: where vt.stage_id = C_STAGE_ID
270: AND nvl(vt.status_code,'X') <> 'DELETED'
271: group by vt.stage_id;
272:
889: /*CURSOR C_JOB(C_VISIT_ID NUMBER , C_STAGE_NUM NUMBER )
890: IS
891: select 'x' from ahl_workorders_v
892: where visit_task_id in
893: (select DISTINCT VISIT_TASK_ID from AHL_VISIT_TASKS_B
894: where visit_id = C_VISIT_ID
895: and STAGE_ID IN (SELECT STAGE_ID FROM AHL_VWP_STAGES_B WHERE stage_num > C_STAGE_NUM
896: AND VISIT_ID = C_VISIT_ID))
897: and ( job_status_code =3 or firm_planned_flag = 1 );
920: l_visit_dets c_visit%RowType;
921:
922: CURSOR get_past_task_details (c_visit_id NUMBER)
923: IS
924: SELECT past_task_start_date, stage_id FROM AHL_VISIT_TASKS_B
925: WHERE visit_id = c_visit_id
926: AND past_task_start_date IS NOT NULL;
927:
928: l_past_task_start_date DATE;
1552:
1553: --Remove stage task
1554: /*IF l_stage_task_dets.VISIT_TASK_ID <> NULL THEN
1555: DELETE FROM AHL_VISIT_TASKS_TL WHERE VISIT_TASK_ID = l_stage_task_dets.VISIT_TASK_ID;
1556: DELETE FROM AHL_VISIT_TASKS_B WHERE VISIT_TASK_ID = l_stage_task_dets.VISIT_TASK_ID;
1557: END IF;*/
1558: IF (l_log_statement >= l_log_current_level) THEN
1559: fnd_log.string(l_log_statement,L_DEBUG,'l_stage_task_dets.VISIT_TASK_ID :'||l_stage_task_dets.VISIT_TASK_ID);
1560: END IF;
1598: -- Remove all stage assciations on tasks
1599: IF (l_log_statement >= l_log_current_level) THEN
1600: fnd_log.string(l_log_statement,L_DEBUG,p_x_stages_tbl(i).STAGE_ID||' stage id updated');
1601: END IF;
1602: update AHL_VISIT_TASKS_B set OBJECT_VERSION_NUMBER=OBJECT_VERSION_NUMBER+1,
1603: STAGE_ID = null,
1604: LAST_UPDATE_DATE = SYSDATE,
1605: LAST_UPDATED_BY = Fnd_Global.USER_ID,
1606: LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
1846: l_stage_rec c_stage_data%rowtype;
1847:
1848: cursor c_stage_task(c_stage_id number)
1849: IS
1850: select 'x' from ahl_visit_tasks_b
1851: where stage_id = c_stage_id
1852: and nvl(status_code,'X')<>'DELETED';
1853:
1854:
2087:
2088: --Get stage id for a given task
2089: CURSOR c_get_task_stage_id(c_task_id IN NUMBER)
2090: IS
2091: SELECT stage_id FROM ahl_visit_tasks_b
2092: WHERE visit_task_id = c_task_id;
2093:
2094:
2095: -- For the passed stage id, get all its successive parent stages
2353:
2354: -- Commented by amagrawa based on review comments.
2355: -- To find all departments from a visit's tasks table
2356: /* CURSOR c_task (x_id IN NUMBER) IS
2357: SELECT DEPARTMENT_ID FROM AHL_VISIT_TASKS_B WHERE VISIT_ID = x_id
2358: AND NVL(STATUS_CODE,'X') <> 'DELETED' AND DEPARTMENT_ID IS NOT NULL;
2359: c_task_rec c_task%ROWTYPE;
2360: */
2361: -- To find only those routes which are there in tasks table but not in route table for a visit
2360: */
2361: -- To find only those routes which are there in tasks table but not in route table for a visit
2362: -- Changed by amagrawa to improve performance.
2363: CURSOR c_route_chk(x_id IN NUMBER) IS
2364: SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_VISIT_TASKS_B TSK
2365: WHERE VISIT_ID = x_id AND MR_Route_ID IS NOT NULL
2366: AND NOT EXISTS
2367: (SELECT DISTINCT(MR_Route_ID) "ROUTE_ID" FROM AHL_MR_ROUTES_V MR
2368: where MR.mr_route_id =TSK.mr_route_id) and rownum=1;
2372: -- Added by amagrawa based on review comments
2373: -- To find if the all visit tasks dept has department shifts defined
2374: CURSOR c_task_dep_exist (x_visit_id IN NUMBER) IS
2375: SELECT 1 from dual WHERE exists(
2376: SELECT visit_task_id from ahl_visit_tasks_b
2377: Where department_id is not null
2378: and visit_id = x_visit_id
2379: and nvl(status_code,'X')<>'DELETED'
2380: and department_id not in (select department_id from ahl_department_shifts)
2564: --Cursor to validate whether task rules does not violate stage rules
2565: CURSOR c_validate_stage_tasks (p_subject_id IN NUMBER, p_object_id IN NUMBER) IS
2566: SELECT 'X'
2567: FROM ahl_task_links
2568: WHERE parent_task_id IN (SELECT visit_task_id FROM ahl_visit_tasks_b WHERE stage_id = p_object_id)
2569: START WITH visit_task_id IN (SELECT visit_task_id FROM ahl_visit_tasks_b WHERE stage_id = p_subject_id)
2570: CONNECT BY PRIOR parent_task_id = visit_task_id;
2571:
2572: --Cursor to get stage task
2565: CURSOR c_validate_stage_tasks (p_subject_id IN NUMBER, p_object_id IN NUMBER) IS
2566: SELECT 'X'
2567: FROM ahl_task_links
2568: WHERE parent_task_id IN (SELECT visit_task_id FROM ahl_visit_tasks_b WHERE stage_id = p_object_id)
2569: START WITH visit_task_id IN (SELECT visit_task_id FROM ahl_visit_tasks_b WHERE stage_id = p_subject_id)
2570: CONNECT BY PRIOR parent_task_id = visit_task_id;
2571:
2572: --Cursor to get stage task
2573: CURSOR c_get_stage_tasks (p_stage_id IN NUMBER) IS
2571:
2572: --Cursor to get stage task
2573: CURSOR c_get_stage_tasks (p_stage_id IN NUMBER) IS
2574: SELECT 'X'
2575: FROM ahl_visit_tasks_b
2576: WHERE stage_id = p_stage_id
2577: AND task_type_code <> 'STAGE';
2578:
2579: -- Get the passed stage parent id and all its successive parent stages
2905: -- Here in the cursor, we are adding additional condition that "stage_id should be null";
2906: -- This is because we dont want to update those tasks for which user has associated the task to the stage manually.
2907: CURSOR c_visit_tasks (p_visit_id IN NUMBER, p_stage_type_code IN VARCHAR2) IS
2908: SELECT visit_task_id
2909: FROM ahl_visit_tasks_b
2910: WHERE visit_id = p_visit_id
2911: AND stage_type_code = p_stage_type_code
2912: AND STATUS_CODE = 'PLANNING'
2913: AND STAGE_ID IS NULL;
3034: RAISE Fnd_Api.g_exc_unexpected_error;
3035: END IF;
3036: END IF;
3037:
3038: UPDATE AHL_VISIT_TASKS_B
3039: SET STAGE_ID = p_stage_type_assoc_tbl(i).STAGE_ID
3040: WHERE visit_task_id = l_tasks_rec.visit_task_id;
3041:
3042: END LOOP; -- Loop through all the tasks
3050: END IF;
3051:
3052: --We need to update all the tasks (that are in PLANNING) of this visit which have stage type that matches with the passed stage type.
3053: --Nullify the stage Ids for all such tasks
3054: UPDATE AHL_VISIT_TASKS_B
3055: SET STAGE_ID = NULL
3056: WHERE visit_id = l_visit_id
3057: AND stage_type_code = p_stage_type_assoc_tbl(i).stage_type_code
3058: AND status_code = 'PLANNING';
3186:
3187: --To get visit tasks of a given stage type
3188: CURSOR c_visit_tasks (p_visit_id IN NUMBER, p_stage_type_code IN VARCHAR2) IS
3189: SELECT visit_task_id
3190: FROM ahl_visit_tasks_b
3191: WHERE visit_id = p_visit_id
3192: AND stage_type_code = p_stage_type_code
3193: AND STATUS_CODE = 'PLANNING';
3194:
3387: RAISE Fnd_Api.g_exc_unexpected_error;
3388: END IF;
3389: END IF;
3390:
3391: UPDATE AHL_VISIT_TASKS_B
3392: SET STAGE_ID = l_stage_id
3393: WHERE visit_task_id = l_tasks_rec.visit_task_id;
3394:
3395: END LOOP; -- Loop through all the tasks.