[Home] [Help]
526:
527: -- To find tasks information for visit
528: CURSOR c_task (x_id IN NUMBER) IS
529: SELECT T1.PROJECT_ID, T1.VISIT_NUMBER, T2.*
530: FROM AHL_VISITS_VL T1, AHL_VISIT_TASKS_VL T2
531: WHERE VISIT_TASK_ID = x_id
532: AND T1.VISIT_ID = T2.VISIT_ID;
533:
534: c_task_rec c_task%ROWTYPE;
853:
854: -- Define local Cursors
855: -- To find all tasks related information
856: CURSOR c_Task (x_id IN NUMBER) IS
857: SELECT * FROM Ahl_Visit_Tasks_VL
858: WHERE Visit_Task_ID = x_id;
859: c_task_rec c_Task%ROWTYPE;
860:
861: -- To find visit related information
1069: c_visit_rec c_visit%ROWTYPE;
1070:
1071: -- To find count for tasks for visit
1072: CURSOR c_task_ct (x_id IN NUMBER) IS
1073: SELECT count(*) FROM AHL_VISIT_TASKS_VL
1074: WHERE VISIT_ID = x_id
1075: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
1076:
1077: -- To find tasks information for visit
1075: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
1076:
1077: -- To find tasks information for visit
1078: CURSOR c_task (x_id IN NUMBER) IS
1079: SELECT * FROM AHL_VISIT_TASKS_VL
1080: WHERE VISIT_ID = x_id
1081: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
1082: c_task_rec c_task%ROWTYPE;
1083:
1082: c_task_rec c_task%ROWTYPE;
1083:
1084: -- To find tasks information for visit
1085: CURSOR c_task_OVN (x_id IN NUMBER, x_task_num IN NUMBER) IS
1086: SELECT OBJECT_VERSION_NUMBER FROM AHL_VISIT_TASKS_VL
1087: WHERE VISIT_ID = x_id AND VISIT_TASK_NUMBER = x_task_num;
1088:
1089: -- To get the cost group for the Org
1090: CURSOR c_cost_group(p_org_id IN NUMBER) IS
1107: SELECT SUBSTR(NVL(ar.route_no,avt.visit_task_name),1,20) task_name,
1108: SUBSTR(NVL(ar.title,avt.visit_task_name),1,250) description,
1109: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
1110: avt.end_date_time task_end_date
1111: FROM ahl_routes_vl ar,ahl_visit_tasks_vl avt, ahl_mr_routes mrr
1112: WHERE avt.visit_id = p_visit_id
1113: AND NVL(avt.status_code,'Y') = 'PLANNING'
1114: AND avt.task_type_code NOT IN ('SUMMARY','UNASSOCIATED')
1115: AND avt.mr_route_id = mrr.mr_route_id (+)
1117: UNION ALL
1118: SELECT SUBSTR(NVL(amh.title,avt.visit_task_name),1,20) task_name, NVL(amh.title,avt.visit_task_name) description,
1119: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
1120: avt.end_date_time task_end_date
1121: FROM ahl_mr_headers_v amh,ahl_visit_tasks_vl avt
1122: WHERE avt.visit_id = p_visit_id
1123: AND NVL(avt.status_code,'Y') = 'PLANNING'
1124: AND avt.task_type_code = 'SUMMARY'
1125: AND avt.summary_task_flag = 'N'
1127: UNION ALL
1128: SELECT SUBSTR(avt.visit_task_name,1,20) task_name, avt.visit_task_name description,
1129: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
1130: avt.end_date_time task_end_date
1131: FROM ahl_visit_tasks_vl avt
1132: WHERE avt.visit_id = p_visit_id
1133: AND NVL(avt.status_code,'Y') = 'PLANNING'
1134: AND ((avt.task_type_code = 'SUMMARY' AND avt.summary_task_flag = 'Y')
1135: OR (avt.task_type_code ='UNASSOCIATED'))
1579: c_visit_rec c_visit%ROWTYPE;
1580:
1581: -- To find count for tasks for visit
1582: CURSOR c_task_ct (x_id IN NUMBER) IS
1583: SELECT count(*) FROM AHL_VISIT_TASKS_VL
1584: WHERE VISIT_ID = x_id
1585: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
1586:
1587: -- To find tasks information for visit
1585: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
1586:
1587: -- To find tasks information for visit
1588: CURSOR c_task (x_id IN NUMBER) IS
1589: SELECT * FROM AHL_VISIT_TASKS_VL
1590: WHERE VISIT_ID = x_id
1591: AND NVL(status_code, 'Y') <> NVL ('DELETED', 'X') ;
1592: c_task_rec c_task%ROWTYPE;
1593:
1635: SELECT SUBSTR(NVL(ar.route_no,avt.visit_task_name),1,20) task_name,
1636: SUBSTR(NVL(ar.title,avt.visit_task_name),1,250) description,
1637: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
1638: avt.end_date_time task_end_date, avt.project_Task_id project_task_id
1639: FROM ahl_routes_vl ar,ahl_visit_tasks_vl avt, ahl_mr_routes mrr
1640: WHERE avt.visit_id = p_visit_id
1641: AND NVL(avt.status_code,'Y') = 'PLANNING'
1642: AND avt.task_type_code NOT IN ('SUMMARY','UNASSOCIATED')
1643: AND avt.mr_route_id = mrr.mr_route_id (+)
1645: UNION ALL
1646: SELECT SUBSTR(NVL(amh.title,avt.visit_task_name),1,20) task_name, NVL(amh.title,avt.visit_task_name) description,
1647: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
1648: avt.end_date_time task_end_date, avt.project_Task_id project_task_id
1649: FROM ahl_mr_headers_v amh,ahl_visit_tasks_vl avt
1650: WHERE avt.visit_id = p_visit_id
1651: AND NVL(avt.status_code,'Y') = 'PLANNING'
1652: AND avt.task_type_code = 'SUMMARY'
1653: AND avt.summary_task_flag = 'N'
1655: UNION ALL
1656: SELECT SUBSTR(avt.visit_task_name,1,20) task_name, avt.visit_task_name description,
1657: avt.visit_task_name, avt.visit_task_number, avt.start_date_time task_start_date,
1658: avt.end_date_time task_end_date, avt.project_Task_id project_task_id
1659: FROM ahl_visit_tasks_vl avt
1660: WHERE avt.visit_id = p_visit_id
1661: AND NVL(avt.status_code,'Y') = 'PLANNING'
1662: AND ((avt.task_type_code = 'SUMMARY' AND avt.summary_task_flag = 'Y')
1663: OR (avt.task_type_code ='UNASSOCIATED'))
2437: /* Begin Changes by Shkalyan */
2438: -- To find count for tasks for visit
2439: -- Added where condition for checking DELETED status in 11.5.10
2440: CURSOR c_task_count (x_id IN NUMBER) IS
2441: SELECT count(*) FROM AHL_VISIT_TASKS_VL
2442: WHERE VISIT_ID = x_id
2443: AND NVL(STATUS_CODE,'X') <> 'DELETED'
2444: AND (TASK_TYPE_CODE <> 'SUMMARY' OR
2445: (TASK_TYPE_CODE = 'SUMMARY' AND
2449: /* Begin Changes by Shkalyan */
2450: -- To find task related information
2451: -- Added where condition for checking DELETED status in 11.5.10
2452: CURSOR c_task (x_visit_id IN NUMBER) IS
2453: SELECT * FROM AHL_VISIT_TASKS_VL
2454: WHERE VISIT_ID = x_visit_id
2455: AND NVL(STATUS_CODE,'X') not in ('DELETED','RELEASED')
2456: AND (TASK_TYPE_CODE <> 'SUMMARY' OR
2457: (TASK_TYPE_CODE = 'SUMMARY' AND
3997:
3998: /* Begin Changes by VSUNDARA For SR Integration*/
3999: -- To find task related information
4000: CURSOR c_task (x_id IN NUMBER) IS
4001: SELECT * FROM AHL_VISIT_TASKS_VL
4002: WHERE VISIT_ID = x_id
4003: AND NVL(STATUS_CODE, 'X') = 'PLANNING'
4004: AND (TASK_TYPE_CODE <> 'SUMMARY' OR
4005: (TASK_TYPE_CODE = 'SUMMARY' AND
4011:
4012: /* Begin Changes by Shkalyan */
4013: -- To find count for tasks for visit
4014: CURSOR c_task_ct (x_id IN NUMBER) IS
4015: SELECT count(*) FROM AHL_VISIT_TASKS_VL
4016: WHERE VISIT_ID = x_id
4017: AND NVL(STATUS_CODE, 'X') = 'PLANNING'
4018: AND (TASK_TYPE_CODE <> 'SUMMARY' OR
4019: (TASK_TYPE_CODE = 'SUMMARY' AND
4073:
4074: --Post11510. Added to get summary task start, end time
4075: CURSOR get_summary_task_times_csr(x_task_id IN NUMBER)IS
4076: SELECT min(start_date_time), max(end_date_time)
4077: FROM ahl_visit_tasks_vl VST
4078: START WITH visit_task_id = x_task_id
4079: -- anraj changed coz the nvl on the RHS is not required
4080: -- AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
4081: AND NVL(VST.status_code, 'Y') <> 'DELETED'
4104: -- anraj: Modified the cursor definition to exclude the tasks in status DELETED
4105: CURSOR get_task_inst_dtls(c_visit_id IN NUMBER)
4106: IS
4107: SELECT inventory_item_id,instance_id
4108: FROM ahl_visit_tasks_vl
4109: WHERE visit_id = c_visit_id
4110: AND NVL(status_code, 'Y') <> 'DELETED'
4111: AND ROWNUM = 1;
4112: get_task_inst_rec get_task_inst_dtls%ROWTYPE;
5876: K NUMBER := 0;
5877: m NUMBER := 0;
5878: L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_tasks_bef_production';
5879: L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || L_API_NAME;
5880: lp_originating_task_id ahl_visit_tasks_vl.originating_task_id%TYPE;
5881: curr_task BOOLEAN := FALSE;
5882: parent_task BOOLEAN := FALSE;
5883: parent_task_found_flag BOOLEAN := FALSE;
5884: child_task BOOLEAN := FALSE;
6237: c_visit_dtl_rec c_visit_dtl%ROWTYPE;
6238:
6239: --fetch task details
6240: CURSOR c_visit_task_dtl (x_visit_task_id IN NUMBER) IS
6241: SELECT * FROM ahl_visit_tasks_vl
6242: WHERE visit_task_id = x_visit_task_id;
6243:
6244: c_visit_tsk_dtl_rec c_visit_task_dtl%ROWTYPE;
6245:
6974: l_instance_id NUMBER;
6975:
6976: CURSOR c_get_tasknumbers (x_visit_id IN NUMBER) IS
6977: SELECT visit_task_number,instance_id
6978: FROM ahl_visit_tasks_vl
6979: WHERE visit_id = p_visit_id
6980: AND NVL(STATUS_CODE,'X') NOT IN ('DELETED','RELEASED')
6981: AND TASK_TYPE_CODE <> 'SUMMARY';
6982: BEGIN
7748: c_visit_rec c_visit%ROWTYPE;
7749:
7750: -- To get all the tasks for the given UE
7751: CURSOR c_task (x_visit_id IN NUMBER, x_unit_effectivity_id IN NUMBER) IS
7752: SELECT * FROM AHL_VISIT_TASKS_VL
7753: WHERE VISIT_ID = x_visit_id
7754: AND NVL( STATUS_CODE, 'X' ) = 'PLANNING'
7755: /*NR-MR Changes*/
7756: START WITH unit_effectivity_id = x_unit_effectivity_id
7780:
7781: --To get summary task start, end time
7782: CURSOR get_summary_task_times_csr(x_task_id IN NUMBER)IS
7783: SELECT min(start_date_time), max(end_date_time)
7784: FROM ahl_visit_tasks_vl VST
7785: START WITH visit_task_id = x_task_id
7786: AND NVL(VST.status_code, 'Y') <> 'DELETED'
7787: CONNECT BY originating_task_id = PRIOR visit_task_id;
7788:
8204: c_visit_rec c_visit%ROWTYPE;
8205:
8206: -- To get all the tasks for the given UE
8207: CURSOR c_task (x_visit_id IN NUMBER, x_unit_effectivity_id IN NUMBER) IS
8208: SELECT * FROM AHL_VISIT_TASKS_VL
8209: WHERE VISIT_ID = x_visit_id
8210: AND NVL( STATUS_CODE, 'X' ) = 'PLANNING'
8211: START WITH unit_effectivity_id = x_unit_effectivity_id
8212: AND (( originating_task_id is null AND 'SR' <> p_module_type)
8223: -- Second part is for the corresponding NR and MR summary tasks.
8224: CURSOR get_prj_task_dtls_cur (c_visit_task_id IN NUMBER) IS
8225: SELECT SUBSTR(NVL(ar.route_no, avt.visit_task_name), 1, 20) task_name,
8226: SUBSTR(NVL(ar.title, avt.visit_task_name), 1, 250) description
8227: FROM ahl_routes_vl ar, ahl_visit_tasks_vl avt,
8228: ahl_mr_routes mrr
8229: WHERE avt.visit_task_id = c_visit_task_id
8230: AND NVL(avt.status_code, 'X') = 'PLANNING'
8231: AND avt.task_type_code NOT IN ('SUMMARY', 'UNASSOCIATED')
8233: AND mrr.route_id = ar.route_id (+)
8234: UNION ALL
8235: SELECT SUBSTR(NVL(amh.title, avt.visit_task_name), 1, 20) task_name,
8236: NVL(amh.title, avt.visit_task_name) description
8237: FROM ahl_mr_headers_v amh, ahl_visit_tasks_vl avt
8238: WHERE avt.visit_task_id = c_visit_task_id
8239: AND NVL(avt.status_code, 'X') = 'PLANNING'
8240: AND avt.task_type_code = 'SUMMARY'
8241: AND avt.summary_task_flag = 'N'
9073: c_visit_rec c_visit%ROWTYPE;
9074:
9075: -- To find task related information
9076: CURSOR c_task (x_id IN NUMBER) IS
9077: SELECT * FROM AHL_VISIT_TASKS_VL
9078: WHERE VISIT_TASK_ID = x_id ;
9079: c_task_rec c_task%ROWTYPE;
9080:
9081: -- To find count for jobs tasks
9101:
9102: --Post11510. Added to get summary task start, end time
9103: CURSOR get_summary_task_times_csr(p_task_id IN NUMBER)IS
9104: SELECT min(start_date_time), max(end_date_time)
9105: FROM ahl_visit_tasks_vl VST
9106: START WITH visit_task_id = p_task_id
9107: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
9108: CONNECT BY originating_task_id = PRIOR visit_task_id;
9109: