[Home] [Help]
33:
34: CURSOR CurRoute
35: is
36: SELECT C.*,D.VISIT_NUMBER
37: FROM AHL_ROUTES_APP_V A,AHL_MR_ROUTES B,AHL_VISIT_TASKS_B C,AHL_VISITS_VL D
38: WHERE A.ROUTE_ID=p_activity_id
39: AND A.ROUTE_ID=B.ROUTE_ID
40: AND B.MR_ROUTE_ID=C.MR_ROUTE_ID
41: AND C.VISIT_ID=D.VISIT_ID
53:
54: CURSOR CurOper
55: is
56: SELECT C.*,D.VISIT_NUMBER
57: FROM AHL_ROUTES_APP_V A,AHL_MR_ROUTES B,AHL_VISIT_TASKS_B C,AHL_VISITS_VL D,AHL_ROUTE_OPERATIONS E
58: WHERE A.ROUTE_ID=B.ROUTE_ID
59: AND A.ROUTE_ID=E.ROUTE_ID
60: AND B.MR_ROUTE_ID=C.MR_ROUTE_ID
61: AND C.VISIT_ID=D.VISIT_ID
852: AND trunc(nvl(AHL_ROUTE_MR_V.EFFECTIVE_TO,SYSDATE))>=trunc(SYSDATE);*/
853:
854: --AMSRINIV : Bug 4913294. Tuned above commented query.
855: SELECT COUNT(*) into l_counter
856: from ahl_mr_routes a, ahl_mr_headers_b b
857: where
858: a.route_id = l_route_rec.route_id and
859: a.mr_header_id = b.mr_header_id and
860: b.application_usg_code = rtrim(ltrim(fnd_profile.value('AHL_APPLN_USAGE'))) and
1075:
1076: -- Routes
1077: CURSOR CurGetRoutedet(C_ROUTE_ID NUMBER)
1078: IS
1079: SELECT * from AHL_MR_ROUTES
1080: WHERE ROUTE_ID=C_ROUTE_ID;
1081: l_mr_route_rec CurGetRoutedet%rowtype;
1082:
1083: l_new_mr_ROUTE_ID NUMBER:=0;
1252: fetch CurGetRoutedet into l_mr_route_rec;
1253: IF CurGetRoutedet%FOUND
1254: THEN
1255:
1256: SELECT AHL_MR_ROUTES_S.NEXTVAL
1257: INTO l_new_mr_ROUTE_ID
1258: FROM DUAL;
1259:
1260: l_old_mr_route_id:=l_mr_route_Rec.mr_route_id;
1258: FROM DUAL;
1259:
1260: l_old_mr_route_id:=l_mr_route_Rec.mr_route_id;
1261:
1262: AHL_MR_ROUTES_PKG.INSERT_ROW (
1263: -- X_ROWID =>L_ROWID,
1264: X_MR_ROUTE_ID =>l_new_mr_ROUTE_ID,
1265: X_MR_HEADER_ID =>l_mr_route_Rec.mr_header_id,
1266: X_ROUTE_ID =>P_ROUTE_ID,