[Home] [Help]
73: SELECT customer_id FROM cs_incidents_all_b
74: WHERE incident_id = p_service_request_id;
75:
76: CURSOR visit_tasks_csr(p_visit_id IN NUMBER) IS
77: SELECT 'x' FROM ahl_visit_tasks_b
78: WHERE visit_id = p_visit_id
79: and nvl(status_code, 'x') <>'DELETED'
80: and task_type_code <> 'STAGE'; -- added the condition by TCHIMIRA on 12-JUL-2011 -VWP: ER:12673125
81:
476: FROM ahl_visits_b
477: WHERE visit_id = p_visit_id;
478:
479: CURSOR visit_tasks_csr(p_visit_id IN NUMBER) IS
480: SELECT 'x' FROM ahl_visit_tasks_b
481: WHERE visit_id = p_visit_id
482: and nvl(status_code, 'x') <>'DELETED'
483: and task_type_code <> 'STAGE'; -- added the condition by TCHIMIRA on 12-JUL-2011 -VWP: ER:12673125
484:
1167:
1168: -- Get all the root MR in the SR
1169:
1170: CURSOR sr_summary_tasks_csr(p_task_id IN NUMBER)IS
1171: SELECT visit_task_id FROM ahl_visit_tasks_b VST
1172: WHERE VST.task_type_code = 'SUMMARY'
1173: AND VST.originating_task_id =p_task_id
1174: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1175: AND VST.mr_id IS NOT NULL;
1176:
1177: -- Get other planned tasks in the SR
1178:
1179: CURSOR sr_other_tasks_csr(p_task_id IN NUMBER)IS
1180: SELECT visit_task_id, start_date_time, end_date_time FROM ahl_visit_tasks_b VST
1181: WHERE VST.task_type_code = 'PLANNED'
1182: AND VST.originating_task_id =p_task_id
1183: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X');
1184:
1184:
1185: --Get min(start_time), max(end_time) for summary tasks
1186: CURSOR get_summary_task_times_csr(p_task_id IN NUMBER)IS
1187: SELECT min(start_date_time), max(end_date_time)
1188: FROM ahl_visit_tasks_b VST
1189: START WITH visit_task_id = p_task_id
1190: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1191: CONNECT BY originating_task_id = PRIOR visit_task_id;
1192:
1362: END LOOP;
1363:
1364:
1365: --update task table with SR price
1366: Update AHL_VISIT_TASKS_B
1367: set actual_price=l_actual_price,
1368: estimated_price=l_estimated_price
1369: where visit_task_id=l_cost_price_rec.visit_task_id;
1370:
1409: l_actual_price NUMBER;
1410: l_estimated_price NUMBER;
1411: l_z number:=0;
1412: l_flag varchar2(1);
1413: l_visit_task_id AHL_VISIT_TASKS_B.visit_task_id%TYPE;
1414:
1415: -- Standard in/out parameters
1416: l_api_name VARCHAR2(30) := 'ESTIMATE_VISIT_PRICE ';
1417: l_msg_data VARCHAR2(2000);
1440:
1441: -- Get all the root MR in the visit
1442:
1443: CURSOR summary_tasks_csr(p_visit_id IN NUMBER)IS
1444: SELECT visit_task_id FROM ahl_visit_tasks_b VST
1445: WHERE VST.visit_id = p_visit_id
1446: AND VST.task_type_code = 'SUMMARY'
1447: AND VST.originating_task_id IS NULL
1448: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1452: -- yazhou 18-Feb-2005
1453: -- Get all the root SR in the visit
1454:
1455: CURSOR SR_tasks_csr(p_visit_id IN NUMBER)IS
1456: SELECT visit_task_id FROM ahl_visit_tasks_b VST
1457: WHERE VST.visit_id = p_visit_id
1458: AND VST.task_type_code = 'SUMMARY'
1459: AND VST.originating_task_id IS NULL
1460: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1463:
1464: --Get min(start_time), max(end_time) for summary tasks
1465: CURSOR get_summary_task_times_csr(p_task_id IN NUMBER)IS
1466: SELECT min(start_date_time), max(end_date_time)
1467: FROM ahl_visit_tasks_b VST
1468: START WITH visit_task_id = p_task_id
1469: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1470: CONNECT BY originating_task_id = PRIOR visit_task_id;
1471:
1473: -- yazhou 18-Feb-2005
1474: -- Get all the unassociated tasks
1475:
1476: CURSOR other_tasks_csr(p_visit_id IN NUMBER)IS
1477: SELECT visit_task_id, start_date_time, end_date_time FROM ahl_visit_tasks_b VST
1478: WHERE VST.visit_id = p_visit_id
1479: AND NVL(VST.status_code, 'Y') <> NVL ('DELETED', 'X')
1480: AND VST.task_type_code = 'UNASSOCIATED';
1481:
2217:
2218: CURSOR other_tasks_snapshot_csr(p_visit_id IN NUMBER) IS
2219:
2220: SELECT VT.visit_task_id, VT.mr_id,VT.estimated_price,VT.actual_price,VT.visit_task_number, V.visit_number
2221: FROM ahl_visit_tasks_b VT, ahl_visits_b V
2222: WHERE VT.VISIT_ID = V.VISIT_ID
2223: AND NOT (task_type_code = 'SUMMARY' AND mr_id IS NULL)
2224: AND V.visit_id = p_visit_id
2225: AND nvl(VT.status_code,'x') <> 'DELETED'
2224: AND V.visit_id = p_visit_id
2225: AND nvl(VT.status_code,'x') <> 'DELETED'
2226: and VT.visit_task_id not in (
2227: select VST.visit_task_id
2228: from ahl_visit_tasks_b VST,
2229: AHL_MR_HEADERS_APP_V mr
2230: where vst.mr_id = mr.mr_header_id
2231: and vst.visit_id =p_visit_id
2232: AND nvl(VST.status_code,'x') <> 'DELETED'