[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
794: AND trunc(nvl(AHL_ROUTE_MR_V.EFFECTIVE_TO,SYSDATE))>=trunc(SYSDATE);*/
795:
796: --AMSRINIV : Bug 4913294. Tuned above commented query.
797: SELECT COUNT(*) into l_counter
798: from ahl_mr_routes a, ahl_mr_headers_b b
799: where
800: a.route_id = l_route_rec.route_id and
801: a.mr_header_id = b.mr_header_id and
802: b.application_usg_code = rtrim(ltrim(fnd_profile.value('AHL_APPLN_USAGE'))) and
1017:
1018: -- Routes
1019: CURSOR CurGetRoutedet(C_ROUTE_ID NUMBER)
1020: IS
1021: SELECT * from AHL_MR_ROUTES
1022: WHERE ROUTE_ID=C_ROUTE_ID;
1023: l_mr_route_rec CurGetRoutedet%rowtype;
1024:
1025: l_new_mr_ROUTE_ID NUMBER:=0;
1194: fetch CurGetRoutedet into l_mr_route_rec;
1195: IF CurGetRoutedet%FOUND
1196: THEN
1197:
1198: SELECT AHL_MR_ROUTES_S.NEXTVAL
1199: INTO l_new_mr_ROUTE_ID
1200: FROM DUAL;
1201:
1202: l_old_mr_route_id:=l_mr_route_Rec.mr_route_id;
1200: FROM DUAL;
1201:
1202: l_old_mr_route_id:=l_mr_route_Rec.mr_route_id;
1203:
1204: AHL_MR_ROUTES_PKG.INSERT_ROW (
1205: -- X_ROWID =>L_ROWID,
1206: X_MR_ROUTE_ID =>l_new_mr_ROUTE_ID,
1207: X_MR_HEADER_ID =>l_mr_route_Rec.mr_header_id,
1208: X_ROUTE_ID =>P_ROUTE_ID,