[Home] [Help]
71: SELECT customer_id FROM cs_incidents_all_b
72: WHERE incident_id = p_service_request_id;
73:
74: CURSOR visit_tasks_csr(p_visit_id IN NUMBER) IS
75: SELECT 'x' FROM ahl_visit_tasks_b
76: WHERE visit_id = p_visit_id
77: and nvl(status_code, 'x') <>'DELETED';
78:
79: CURSOR price_list_name_csr(p_price_list_id IN NUMBER) IS
430: FROM ahl_visits_b
431: WHERE visit_id = p_visit_id;
432:
433: CURSOR visit_tasks_csr(p_visit_id IN NUMBER) IS
434: SELECT 'x' FROM ahl_visit_tasks_b
435: WHERE visit_id = p_visit_id
436: and nvl(status_code, 'x') <>'DELETED';
437:
438: CURSOR price_list_dates_csr(p_price_list_id IN NUMBER)IS
1098:
1099: -- Get all the root MR in the SR
1100:
1101: CURSOR sr_summary_tasks_csr(p_task_id IN NUMBER)IS
1102: SELECT visit_task_id FROM ahl_visit_tasks_b VST
1103: WHERE VST.task_type_code = 'SUMMARY'
1104: AND VST.originating_task_id =p_task_id
1105: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1106: AND VST.mr_id IS NOT NULL;
1107:
1108: -- Get other planned tasks in the SR
1109:
1110: CURSOR sr_other_tasks_csr(p_task_id IN NUMBER)IS
1111: SELECT visit_task_id, start_date_time, end_date_time FROM ahl_visit_tasks_b VST
1112: WHERE VST.task_type_code = 'PLANNED'
1113: AND VST.originating_task_id =p_task_id
1114: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X');
1115:
1115:
1116: --Get min(start_time), max(end_time) for summary tasks
1117: CURSOR get_summary_task_times_csr(p_task_id IN NUMBER)IS
1118: SELECT min(start_date_time), max(end_date_time)
1119: FROM ahl_visit_tasks_b VST
1120: START WITH visit_task_id = p_task_id
1121: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1122: CONNECT BY originating_task_id = PRIOR visit_task_id;
1123:
1293: END LOOP;
1294:
1295:
1296: --update task table with SR price
1297: Update AHL_VISIT_TASKS_B
1298: set actual_price=l_actual_price,
1299: estimated_price=l_estimated_price
1300: where visit_task_id=l_cost_price_rec.visit_task_id;
1301:
1340: l_actual_price NUMBER;
1341: l_estimated_price NUMBER;
1342: l_z number:=0;
1343: l_flag varchar2(1);
1344: l_visit_task_id AHL_VISIT_TASKS_B.visit_task_id%TYPE;
1345:
1346: -- Standard in/out parameters
1347: l_api_name VARCHAR2(30) := 'ESTIMATE_VISIT_PRICE ';
1348: l_msg_data VARCHAR2(2000);
1371:
1372: -- Get all the root MR in the visit
1373:
1374: CURSOR summary_tasks_csr(p_visit_id IN NUMBER)IS
1375: SELECT visit_task_id FROM ahl_visit_tasks_b VST
1376: WHERE VST.visit_id = p_visit_id
1377: AND VST.task_type_code = 'SUMMARY'
1378: AND VST.originating_task_id IS NULL
1379: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1383: -- yazhou 18-Feb-2005
1384: -- Get all the root SR in the visit
1385:
1386: CURSOR SR_tasks_csr(p_visit_id IN NUMBER)IS
1387: SELECT visit_task_id FROM ahl_visit_tasks_b VST
1388: WHERE VST.visit_id = p_visit_id
1389: AND VST.task_type_code = 'SUMMARY'
1390: AND VST.originating_task_id IS NULL
1391: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1394:
1395: --Get min(start_time), max(end_time) for summary tasks
1396: CURSOR get_summary_task_times_csr(p_task_id IN NUMBER)IS
1397: SELECT min(start_date_time), max(end_date_time)
1398: FROM ahl_visit_tasks_b VST
1399: START WITH visit_task_id = p_task_id
1400: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1401: CONNECT BY originating_task_id = PRIOR visit_task_id;
1402:
1404: -- yazhou 18-Feb-2005
1405: -- Get all the unassociated tasks
1406:
1407: CURSOR other_tasks_csr(p_visit_id IN NUMBER)IS
1408: SELECT visit_task_id, start_date_time, end_date_time FROM ahl_visit_tasks_b VST
1409: WHERE VST.visit_id = p_visit_id
1410: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1411: AND VST.task_type_code = 'UNASSOCIATED';
1412:
2136:
2137: CURSOR other_tasks_snapshot_csr(p_visit_id IN NUMBER) IS
2138:
2139: SELECT VT.visit_task_id, VT.mr_id,VT.estimated_price,VT.actual_price,VT.visit_task_number, V.visit_number
2140: FROM ahl_visit_tasks_b VT, ahl_visits_b V
2141: WHERE VT.VISIT_ID = V.VISIT_ID
2142: AND NOT (task_type_code = 'SUMMARY' AND mr_id IS NULL)
2143: AND V.visit_id = p_visit_id
2144: AND nvl(VT.status_code,'x') <> 'DELETED'
2143: AND V.visit_id = p_visit_id
2144: AND nvl(VT.status_code,'x') <> 'DELETED'
2145: and VT.visit_task_id not in (
2146: select VST.visit_task_id
2147: from ahl_visit_tasks_b VST,
2148: AHL_MR_HEADERS_APP_V mr
2149: where vst.mr_id = mr.mr_header_id
2150: and vst.visit_id =p_visit_id
2151: AND nvl(VST.status_code,'x') <> 'DELETED'