[Home] [Help]
1086:
1087: -- To find tasks information for visit
1088: CURSOR c_task (x_id IN NUMBER) IS
1089: SELECT T1.PROJECT_ID, T1.VISIT_NUMBER, T2.*
1090: FROM AHL_VISITS_VL T1, AHL_VISIT_TASKS_VL T2
1091: WHERE VISIT_TASK_ID = x_id
1092: AND T1.VISIT_ID = T2.VISIT_ID;
1093:
1094: c_task_rec c_task%ROWTYPE;
1420:
1421: -- Define local Cursors
1422: -- To find all tasks related information
1423: CURSOR c_Task (x_id IN NUMBER) IS
1424: SELECT * FROM Ahl_Visit_Tasks_VL
1425: WHERE Visit_Task_ID = x_id;
1426: c_task_rec c_Task%ROWTYPE;
1427:
1428: -- To find visit related information
1745: c_visit_rec c_visit%ROWTYPE;
1746:
1747: -- To find count for tasks for visit
1748: CURSOR c_task_ct (x_id IN NUMBER) IS
1749: SELECT count(*) FROM AHL_VISIT_TASKS_VL
1750: WHERE VISIT_ID = x_id
1751: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
1752:
1753: -- To find tasks information for visit
1751: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
1752:
1753: -- To find tasks information for visit
1754: CURSOR c_task (x_id IN NUMBER) IS
1755: SELECT * FROM AHL_VISIT_TASKS_VL
1756: WHERE VISIT_ID = x_id
1757: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
1758: c_task_rec c_task%ROWTYPE;
1759:
1758: c_task_rec c_task%ROWTYPE;
1759:
1760: -- To find tasks information for visit
1761: CURSOR c_task_OVN (x_id IN NUMBER, x_task_num IN NUMBER) IS
1762: SELECT OBJECT_VERSION_NUMBER FROM AHL_VISIT_TASKS_VL
1763: WHERE VISIT_ID = x_id AND VISIT_TASK_NUMBER = x_task_num;
1764:
1765: -- To get the cost group for the Org
1766: CURSOR c_cost_group(p_org_id IN NUMBER) IS
1788: SUBSTR(NVL(ar.title,avt.visit_task_name),1,250) description,
1789: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date, avt.visit_task_id,
1790: avt.end_date_time task_end_date, avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.status_code -- PRAKKUM :: PIE :: 13-OCT-2010
1791: , avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
1792: FROM ahl_routes_vl ar,ahl_visit_tasks_vl avt, ahl_mr_routes mrr
1793: WHERE avt.visit_id = p_visit_id
1794: AND NVL(avt.status_code,'Y') = 'PLANNING'
1795: AND avt.task_type_code NOT IN ('SUMMARY','UNASSOCIATED','STAGE') ----SKPATHAK :: VWPE: ER:12424063 :: 13-JUN-2012 :: Need not create project task for a stage
1796: AND avt.mr_route_id = mrr.mr_route_id (+)
1799: SELECT SUBSTR(NVL(amh.title,avt.visit_task_name),1,20) task_name, NVL(amh.title,avt.visit_task_name) description,
1800: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date, avt.visit_task_id,
1801: avt.end_date_time task_end_date, avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.status_code -- PRAKKUM :: PIE :: 13-OCT-2010
1802: , avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
1803: FROM ahl_mr_headers_v amh,ahl_visit_tasks_vl avt
1804: WHERE avt.visit_id = p_visit_id
1805: AND NVL(avt.status_code,'Y') = 'PLANNING'
1806: AND avt.task_type_code = 'SUMMARY'
1807: AND avt.summary_task_flag = 'N'
1810: SELECT SUBSTR(avt.visit_task_name,1,20) task_name, avt.visit_task_name description,
1811: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date, avt.visit_task_id,
1812: avt.end_date_time task_end_date, avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.status_code -- PRAKKUM :: PIE :: 13-OCT-2010
1813: , avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
1814: FROM ahl_visit_tasks_vl avt
1815: WHERE avt.visit_id = p_visit_id
1816: AND NVL(avt.status_code,'Y') = 'PLANNING'
1817: AND ((avt.task_type_code = 'SUMMARY' AND avt.summary_task_flag = 'Y')
1818: OR (avt.task_type_code ='UNASSOCIATED'))
2460: WHERE CURR.PROJECT_ID = c_project_id
2461: AND CURR.TOP_TASK_ID not IN
2462: (
2463: SELECT DISTINCT TOP_TASK_ID
2464: FROM AHL_VISIT_TASKS_VL VT, PA_PROJ_ELEMENT_VERSIONS PPEV, PA_TASKS PTSKS
2465: WHERE PPEV.PROJECT_ID = c_project_id
2466: AND VT.VISIT_ID = c_visit_id -- PRAKKUM :: 17-JAN-2013 :: FPBug 16481709 for BaseBug 14828418
2467: AND PPEV.PROJ_ELEMENT_ID = VT.PROJECT_TASK_ID
2468: AND PTSKS.TASK_ID = PPEV.PROJ_ELEMENT_ID
2472: ORDER BY PPEV.DISPLAY_SEQUENCE;
2473:
2474: CURSOR get_vst_prj_top_task(c_project_id IN AHL_VISITS_B.PROJECT_ID%TYPE, c_visit_id IN AHL_VISITS_B.VISIT_ID%TYPE) IS
2475: SELECT distinct PTSKS.TOP_TASK_ID TOP_TASK_ID
2476: FROM AHL_VISIT_TASKS_VL VT, PA_PROJ_ELEMENT_VERSIONS PPEV, PA_TASKS PTSKS
2477: WHERE PPEV.PROJECT_ID = c_project_id
2478: AND PPEV.PROJ_ELEMENT_ID = VT.PROJECT_TASK_ID
2479: AND PTSKS.TASK_ID = PPEV.PROJ_ELEMENT_ID
2480: AND VT.VISIT_ID = c_visit_id;
2489: c_visit_rec c_visit%ROWTYPE;
2490:
2491: -- To find count for tasks for visit
2492: CURSOR c_task_ct (x_id IN NUMBER) IS
2493: SELECT count(*) FROM AHL_VISIT_TASKS_VL
2494: WHERE VISIT_ID = x_id
2495: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
2496:
2497: -- PRAKKUM :: PIE :: 13-OCT-2010 ::
2496:
2497: -- PRAKKUM :: PIE :: 13-OCT-2010 ::
2498: -- To find tasks information for visit
2499: CURSOR c_task (x_id IN NUMBER) IS
2500: -- SELECT * FROM AHL_VISIT_TASKS_VL
2501: -- WHERE VISIT_ID = x_id
2502: -- AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X');
2503: SELECT PPEV.DISPLAY_SEQUENCE, VT.*
2504: FROM AHL_VISIT_TASKS_VL VT, PA_PROJ_ELEMENT_VERSIONS PPEV
2500: -- SELECT * FROM AHL_VISIT_TASKS_VL
2501: -- WHERE VISIT_ID = x_id
2502: -- AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X');
2503: SELECT PPEV.DISPLAY_SEQUENCE, VT.*
2504: FROM AHL_VISIT_TASKS_VL VT, PA_PROJ_ELEMENT_VERSIONS PPEV
2505: WHERE VT.VISIT_ID = x_id
2506: AND NVL(VT.status_code, 'Y') <> NVL ('DELETED', 'X')
2507: AND PPEV.PROJ_ELEMENT_ID (+) = VT.PROJECT_TASK_ID;
2508: c_task_rec c_task%ROWTYPE;
2572: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
2573: avt.end_date_time task_end_date, avt.project_Task_id project_task_id, ppev.display_sequence,
2574: avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.visit_task_id
2575: , avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
2576: FROM ahl_routes_vl ar,ahl_visit_tasks_vl avt, ahl_mr_routes mrr, pa_proj_element_versions ppev
2577: WHERE avt.visit_id = p_visit_id
2578: --VWPE: ER:12424063 :: SKPATHAK :: 07-JUN-2012 :: Tasks should not be in deleted status
2579: AND NVL(avt.status_code,'Y') <> 'DELETED'
2580: AND avt.task_type_code NOT IN ('SUMMARY','UNASSOCIATED','STAGE') ----SKPATHAK :: VWPE: ER:12424063 :: 13-JUN-2012 :: Need not create project task for a stage
2586: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
2587: avt.end_date_time task_end_date, avt.project_Task_id project_task_id, ppev.display_sequence,
2588: avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.visit_task_id
2589: , avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
2590: FROM ahl_mr_headers_v amh,ahl_visit_tasks_vl avt, pa_proj_element_versions ppev
2591: WHERE avt.visit_id = p_visit_id
2592: --VWPE: ER:12424063 :: SKPATHAK :: 07-JUN-2012 :: Tasks should not be in deleted status
2593: AND NVL(avt.status_code,'Y') <> 'DELETED'
2594: AND avt.task_type_code = 'SUMMARY'
2600: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
2601: avt.end_date_time task_end_date, avt.project_Task_id project_task_id, ppev.display_sequence,
2602: avt.task_type_code, avt.cost_parent_id, avt.service_type_code, avt.visit_task_id
2603: , avt.repair_batch_name --PRAKKUM :: 11/06/2012 :: Bug 14068468
2604: FROM ahl_visit_tasks_vl avt, pa_proj_element_versions ppev
2605: WHERE avt.visit_id = p_visit_id
2606: --VWPE: ER:12424063 :: SKPATHAK :: 07-JUN-2012 :: Tasks should not be in deleted status
2607: AND NVL(avt.status_code,'Y') <> 'DELETED'
2608: AND ((avt.task_type_code = 'SUMMARY' AND avt.summary_task_flag = 'Y')
4492: /* MANESING::Component Maintenance Planning Project, 07-Aug-2011, added visit task CLOSED status
4493: * in cursor query to prevent fetching tasks that are Closed due to Repair Batch closure.
4494: */
4495: CURSOR c_task (x_visit_id IN NUMBER) IS
4496: SELECT * FROM AHL_VISIT_TASKS_VL
4497: WHERE VISIT_ID = x_visit_id
4498: AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED','CLOSED')
4499: AND (TASK_TYPE_CODE <> 'SUMMARY' OR
4500: (TASK_TYPE_CODE = 'SUMMARY' AND
6133:
6134: /* Begin Changes by VSUNDARA For SR Integration*/
6135: -- To find task related information
6136: CURSOR c_task (x_id IN NUMBER) IS
6137: SELECT * FROM AHL_VISIT_TASKS_VL
6138: WHERE VISIT_ID = x_id
6139: AND NVL(STATUS_CODE, 'X') = 'PLANNING'
6140: AND (TASK_TYPE_CODE <> 'SUMMARY' OR
6141: (TASK_TYPE_CODE = 'SUMMARY' AND
6147:
6148: /* Begin Changes by Shkalyan */
6149: -- To find count for tasks for visit
6150: CURSOR c_task_ct (x_id IN NUMBER) IS
6151: SELECT count(*) FROM AHL_VISIT_TASKS_VL
6152: WHERE VISIT_ID = x_id
6153: AND NVL(STATUS_CODE, 'X') = 'PLANNING'
6154: AND (TASK_TYPE_CODE <> 'SUMMARY' OR
6155: (TASK_TYPE_CODE = 'SUMMARY' AND
6245: -- anraj: Modified the cursor definition to exclude the tasks in status DELETED
6246: CURSOR get_task_inst_dtls(c_visit_id IN NUMBER)
6247: IS
6248: SELECT inventory_item_id,instance_id
6249: FROM ahl_visit_tasks_vl
6250: WHERE visit_id = c_visit_id
6251: AND NVL(status_code, 'Y') <> 'DELETED'
6252: AND ROWNUM = 1;
6253: get_task_inst_rec get_task_inst_dtls%ROWTYPE;
7776: -- Added this cursor to validate if any of the children UEs for a given visit is in EXCEPTION status
7777: -- and if yes, return the MR title corresponding to one of the UEs in EXCEPTION status
7778: CURSOR c_validate_ue_status (x_id IN NUMBER) IS
7779: SELECT mr.title
7780: FROM ahl_visit_tasks_vl avt, ahl_unit_effectivities_b ue, ahl_mr_headers_b mr
7781: WHERE avt.visit_id = x_id
7782: AND NVL(avt.status_code, 'X') = 'PLANNING'
7783: AND (avt.task_type_code <> 'SUMMARY' OR
7784: (avt.task_type_code = 'SUMMARY' AND
8816: K NUMBER := 0;
8817: m NUMBER := 0;
8818: L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_tasks_bef_production';
8819: L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
8820: lp_originating_task_id ahl_visit_tasks_vl.originating_task_id%TYPE;
8821: curr_task BOOLEAN := FALSE;
8822: parent_task BOOLEAN := FALSE;
8823: parent_task_found_flag BOOLEAN := FALSE;
8824: child_task BOOLEAN := FALSE;
9264: c_visit_dtl_rec c_visit_dtl%ROWTYPE;
9265:
9266: --fetch task details
9267: CURSOR c_visit_task_dtl (x_visit_task_id IN NUMBER) IS
9268: SELECT * FROM ahl_visit_tasks_vl
9269: WHERE visit_task_id = x_visit_task_id;
9270:
9271: c_visit_tsk_dtl_rec c_visit_task_dtl%ROWTYPE;
9272:
10290: * in cursor query to prevent fetching tasks that are Closed due to Repair Batch closure.
10291: */
10292: CURSOR c_get_tasknumbers (x_visit_id IN NUMBER) IS
10293: SELECT visit_task_number,instance_id
10294: FROM ahl_visit_tasks_vl
10295: WHERE visit_id = p_visit_id
10296: AND NVL(STATUS_CODE,'X') NOT IN ('DELETED','RELEASED','CLOSED')
10297: AND TASK_TYPE_CODE <> 'SUMMARY';
10298: BEGIN
11152: CURSOR c_task (x_visit_id IN NUMBER, x_unit_effectivity_id IN NUMBER) IS
11153: -- SKPATHAK :: Bug 8340436 :: 23-MAR-2009
11154: -- Fetch distinct rows to avoid duplicates
11155: -- Select individual cols instead of *
11156: --SELECT * FROM AHL_VISIT_TASKS_VL
11157: SELECT distinct visit_task_id, originating_task_id, visit_id, unit_effectivity_id,
11158: status_code, service_request_id, project_task_id, visit_task_number,
11159: visit_task_name, description, object_version_number, task_type_code,
11160: inventory_item_id, instance_id, mr_route_id, department_id, start_date_time,
11158: status_code, service_request_id, project_task_id, visit_task_number,
11159: visit_task_name, description, object_version_number, task_type_code,
11160: inventory_item_id, instance_id, mr_route_id, department_id, start_date_time,
11161: end_date_time, past_task_start_date -- SKPATHAK :: ER: 9147951 :: 11-JAN-2010 :: Fetch past date too
11162: FROM AHL_VISIT_TASKS_VL
11163: WHERE VISIT_ID = x_visit_id
11164: AND NVL( STATUS_CODE, 'X' ) = 'PLANNING'
11165: /*NR-MR Changes*/
11166: START WITH unit_effectivity_id = x_unit_effectivity_id
11817: CURSOR c_task (x_visit_id IN NUMBER, x_unit_effectivity_id IN NUMBER) IS
11818: -- SKPATHAK :: Bug 8340436 :: 23-MAR-2009
11819: -- Fetch distinct rows to avoid duplicates
11820: -- Select individual cols instead of *
11821: --SELECT * FROM AHL_VISIT_TASKS_VL
11822: SELECT distinct visit_task_id, originating_task_id, visit_id, unit_effectivity_id,
11823: status_code, service_request_id, project_task_id, visit_task_number,
11824: visit_task_name, description, object_version_number, start_date_time,
11825: end_date_time, task_type_code, cost_parent_id, service_type_code --PRAKKUM :: PIE :: 13-OCT-2010 :: Added three more columns
11822: SELECT distinct visit_task_id, originating_task_id, visit_id, unit_effectivity_id,
11823: status_code, service_request_id, project_task_id, visit_task_number,
11824: visit_task_name, description, object_version_number, start_date_time,
11825: end_date_time, task_type_code, cost_parent_id, service_type_code --PRAKKUM :: PIE :: 13-OCT-2010 :: Added three more columns
11826: FROM AHL_VISIT_TASKS_VL
11827: WHERE VISIT_ID = x_visit_id
11828: AND NVL( STATUS_CODE, 'X' ) = 'PLANNING'
11829: START WITH unit_effectivity_id = x_unit_effectivity_id
11830: AND (( originating_task_id is null AND 'SR' <> p_module_type)
11854: -- Second part is for the corresponding NR and MR summary tasks.
11855: CURSOR get_prj_task_dtls_cur (c_visit_task_id IN NUMBER) IS
11856: SELECT SUBSTR(NVL(ar.route_no, avt.visit_task_name), 1, 20) task_name,
11857: SUBSTR(NVL(ar.title, avt.visit_task_name), 1, 250) description
11858: FROM ahl_routes_vl ar, ahl_visit_tasks_vl avt,
11859: ahl_mr_routes mrr
11860: WHERE avt.visit_task_id = c_visit_task_id
11861: AND NVL(avt.status_code, 'X') = 'PLANNING'
11862: AND avt.task_type_code NOT IN ('SUMMARY', 'UNASSOCIATED')
11864: AND mrr.route_id = ar.route_id (+)
11865: UNION ALL
11866: SELECT SUBSTR(NVL(amh.title, avt.visit_task_name), 1, 20) task_name,
11867: NVL(amh.title, avt.visit_task_name) description
11868: FROM ahl_mr_headers_v amh, ahl_visit_tasks_vl avt
11869: WHERE avt.visit_task_id = c_visit_task_id
11870: AND NVL(avt.status_code, 'X') = 'PLANNING'
11871: AND avt.task_type_code = 'SUMMARY'
11872: AND avt.summary_task_flag = 'N'
13042: c_visit_rec c_visit%ROWTYPE;
13043:
13044: -- To find task related information
13045: CURSOR c_task (x_id IN NUMBER) IS
13046: SELECT * FROM AHL_VISIT_TASKS_VL
13047: WHERE VISIT_TASK_ID = x_id ;
13048: c_task_rec c_task%ROWTYPE;
13049:
13050: -- To find count for jobs tasks
14602: CURSOR get_stage_tasks (c_stage_id IN NUMBER)
14603: IS
14604: SELECT * FROM
14605: --For non-summary tasks
14606: (SELECT * FROM ahl_visit_tasks_vl
14607: WHERE stage_id = c_stage_id
14608: AND task_type_code <> 'STAGE'
14609: UNION ALL
14610: --For summary tasks
14607: WHERE stage_id = c_stage_id
14608: AND task_type_code <> 'STAGE'
14609: UNION ALL
14610: --For summary tasks
14611: SELECT DISTINCT * FROM ahl_visit_tasks_vl
14612: WHERE task_type_code = 'SUMMARY'
14613: START WITH visit_task_id IN (SELECT originating_task_id
14614: FROM ahl_visit_tasks_b
14615: WHERE stage_id = c_stage_id )