[Home] [Help]
1172: CURSOR c_route_details(c_mr_route_id IN NUMBER) IS
1173: SELECT substrb(ar.title,1,80), ar.remarks,
1174: -- SKPATHAK :: 02-MAY-2011 :: VWPE :: Added below two more columns
1175: mrr.stage_type_code, ar.return_to_supply_flag
1176: FROM ahl_routes_vl ar, ahl_mr_routes mrr
1177: WHERE mrr.mr_route_id = c_mr_route_id
1178: AND mrr.route_id = ar.route_id;
1179:
1180: --VWP ER-12424063:: tchimira :: 17-FEB -2011 :: start
1180: --VWP ER-12424063:: tchimira :: 17-FEB -2011 :: start
1181: -- cursor to fetch the stage_id of the stage, which has stage type associated that matches with the stage type of the passed task's route-MR association.
1182: CURSOR c_stage_id (c_visit_id IN NUMBER, c_mr_route_id IN NUMBER) IS
1183: SELECT stage.stage_id
1184: FROM ahl_mr_routes mr, ahl_visit_stage_typ_asoc assoc, ahl_vwp_stages_b stage
1185: WHERE assoc.stage_id = stage.stage_id
1186: AND stage.visit_id = c_visit_id
1187: AND assoc.stage_type_code = mr.stage_type_code
1188: AND mr.mr_route_id = c_mr_route_id
1872:
1873: -- To find MR_ROUTE_ID for the particular MR_HEADER_ID
1874: CURSOR c_MR_route (x_mr_id IN NUMBER) IS
1875: SELECT T1.MR_ROUTE_ID
1876: FROM AHL_MR_ROUTES_V T1,
1877: AHL_ROUTES_B T2
1878: WHERE T1.MR_HEADER_ID = x_mr_id
1879: AND T1.ROUTE_ID = T2.ROUTE_ID
1880: AND T2.REVISION_STATUS_CODE = 'COMPLETE'
1881: -- Added as of Bug# 3562914
1882: -- By shbhanda 04/22/2004
1883: AND T1.ROUTE_REVISION_NUMBER
1884: IN ( SELECT MAX(T3.ROUTE_REVISION_NUMBER)
1885: FROM AHL_MR_ROUTES_V T3
1886: WHERE T3.MR_HEADER_ID = x_mr_id
1887: AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
1888: GROUP BY T3.ROUTE_NUMBER
1889: );
2197: -- AnRaj: Changes made for fixing bug#4919353, issue# 10
2198: SELECT T1.MR_ROUTE_ID,
2199: -- Added for 11.5.10 Changes done by Senthil.
2200: T1.STAGE
2201: FROM AHL_MR_ROUTES_V T1, AHL_ROUTES_B T2
2202: WHERE T1.MR_HEADER_ID = x_mr_id
2203: AND T1.ROUTE_ID = T2.ROUTE_ID
2204: AND T2.REVISION_STATUS_CODE = 'COMPLETE'
2205: -- Added as of Bug# 3562914
2205: -- Added as of Bug# 3562914
2206: -- By shbhanda 04/22/2004
2207: AND T1.ROUTE_REVISION_NUMBER
2208: IN (SELECT MAX(T3.ROUTE_REVISION_NUMBER)
2209: FROM AHL_MR_ROUTES_V T3
2210: WHERE T3.MR_HEADER_ID = x_mr_id
2211: AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
2212: GROUP BY T3.ROUTE_NUMBER
2213: );
2489: -- 11.5.10 Changes by Senthil.
2490: -- AnRaj: Changes made for fixing bug#4919353, issue# 11
2491: SELECT T1.MR_ROUTE_ID, T1.STAGE
2492: BULK COLLECT INTO MR_Route_Tbl,l_Stage_num_Tbl
2493: FROM AHL_MR_ROUTES_V T1, AHL_ROUTES_B T2
2494: WHERE T1.MR_HEADER_ID = l_MR_Id
2495: AND T1.ROUTE_ID = T2.ROUTE_ID
2496: AND T2.revision_status_code = 'COMPLETE'
2497: AND T1.ROUTE_REVISION_NUMBER IN
2495: AND T1.ROUTE_ID = T2.ROUTE_ID
2496: AND T2.revision_status_code = 'COMPLETE'
2497: AND T1.ROUTE_REVISION_NUMBER IN
2498: (SELECT MAX(T3.ROUTE_REVISION_NUMBER)
2499: FROM AHL_MR_ROUTES_V T3
2500: WHERE T3.MR_HEADER_ID = l_MR_Id
2501: AND T3.ROUTE_NUMBER = T1.ROUTE_NUMBER
2502: GROUP BY T3.ROUTE_NUMBER
2503: );
3840: --cursor to fetch warranty template associated at route
3841: CURSOR c_route_template (c_task_id IN NUMBER) IS
3842: SELECT ARV.WARRANTY_TEMPLATE_ID
3843: FROM AHL_VISIT_TASKS_VL ATSK,
3844: AHL_MR_ROUTES_V AMRR,
3845: AHL_ROUTES_B ARV
3846: WHERE ATSK.MR_ROUTE_ID = AMRR.MR_ROUTE_ID(+)
3847: AND AMRR.ROUTE_ID = ARV.ROUTE_ID (+)
3848: AND ATSK.VISIT_TASK_ID = c_task_id;
4293: SELECT route.route_no,
4294: route.route_type_code,
4295: kfv.concatenated_segments,
4296: route.process_code
4297: FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes mrt
4298: WHERE tsk.mr_route_id = mrt.mr_route_id
4299: AND mrt.route_id = route.route_id
4300: AND tsk.visit_task_id = p_task_id
4301: AND kfv.route_id = route.route_id;
4319:
4320: --To match the post route attributes with the route attributes of all the tasks that fall under the same stage.
4321: Cursor get_task_id (p_stage_id IN NUMBER, p_route_no IN VARCHAR2, p_route_type IN VARCHAR2, p_system_code IN VARCHAR2, p_process_code IN VARCHAR2) IS
4322: SELECT tsk.visit_task_id, tsk.visit_task_number
4323: FROM ahl_visit_tasks_b tsk, ahl_routes_b route, ahl_routes_b_kfv kfv, ahl_mr_routes assoc
4324: WHERE kfv.route_id = route.route_id
4325: AND NVL2(p_route_no, route.ROUTE_NO,'X') = NVL(p_route_no, 'X')
4326: AND NVL2(p_route_type,route.ROUTE_TYPE_CODE,'X') = NVL(p_route_type, 'X')
4327: AND NVL2(p_system_code,kfv.CONCATENATED_SEGMENTS,'X') = NVL(p_system_code, 'X')
5454: IS
5455: SELECT 'X'
5456: FROM AHL_VISIT_TASKS_B TSK,
5457: AHL_ROUTES_B ROUTE,
5458: AHL_MR_ROUTES AMRR
5459: WHERE TSK.VISIT_TASK_ID = c_task_id
5460: AND NVL(TSK.RETURN_TO_SUPPLY_FLAG, 'X') <> 'Y'
5461: AND AMRR.MR_ROUTE_ID = TSK.MR_ROUTE_ID
5462: AND AMRR.ROUTE_ID = ROUTE.ROUTE_ID
5467: IS
5468: SELECT 'X'
5469: FROM AHL_VISIT_TASKS_B TSK1,
5470: AHL_ROUTES_B ROUTE,
5471: AHL_MR_ROUTES AMRR1
5472: WHERE TSK1.VISIT_TASK_ID = c_task_id
5473: AND TSK1.STATUS_CODE = 'PLANNING'
5474: AND TSK1.TASK_TYPE_CODE IN ('PLANNED', 'UNPLANNED')
5475: AND TSK1.MR_ROUTE_ID = AMRR1.MR_ROUTE_ID
5477: AND ROUTE.DUPLICATE_FLAG = 'Y'
5478: AND EXISTS
5479: (SELECT 'X'
5480: FROM AHL_VISIT_TASKS_B TSK2,
5481: AHL_MR_ROUTES AMRR2
5482: WHERE TSK2.VISIT_ID = TSK1.VISIT_ID
5483: AND TSK2.STATUS_CODE IN ('RELEASED', 'PLANNING')
5484: AND TSK2.MR_ROUTE_ID = AMRR2.MR_ROUTE_ID
5485: AND AMRR2.ROUTE_ID = ROUTE.ROUTE_ID