[Home] [Help]
193: -- cursor to find spaces whose dates not fall between visit start date and end dates.
194: cursor c_validate_space_dates (vst_id IN NUMBER)
195: IS
196: select SPA.SPACE_NAME SPACE_NAME,VST.start_date_time, start_from, VST.close_date_time, end_to
197: from ahl_space_assignments SPASSIGN,
198: AHL_VISITS_VL VST,ahl_spaces_vl SPA
199: WHERE
200: VST.VISIT_ID = SPASSIGN.VISIT_ID
201: AND SPA.SPACE_ID = SPASSIGN.SPACE_ID
582: ATTRIBUTE14,
583: ATTRIBUTE15,
584: START_FROM, -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
585: END_TO -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
586: FROM ahl_space_assignments
587: WHERE space_assignment_id = p_space_assign_rec.space_assignment_id;
588: --
589: -- This is the only exception for using %ROWTYPE.
590: l_space_assign_rec c_space_assign_rec%ROWTYPE;
739: c_start_from IN DATE,
740: c_end_to IN DATE)
741: IS
742: SELECT space_assignment_id
743: FROM AHL_SPACE_ASSIGNMENTS
744: WHERE VISIT_ID = p_space_assign_rec.visit_id
745: AND SPACE_ID = p_space_assign_rec.space_id
746: AND space_assignment_id <> p_space_assign_rec.space_assignment_id
747: AND (( c_start_from IS NULL AND c_end_to IS NULL ) OR
1074: -- PURPOSE
1075: -- Create Space Assignment Record
1076: --
1077: -- PARAMETERS
1078: -- p_x_space_assign_rec: the record representing AHL_SPACE_ASSIGNMENTS..
1079: --
1080: -- NOTES
1081: --
1082: PROCEDURE Create_Space_Assignment (
1105: l_space_assign_rec Space_Assignment_Rec;
1106: --
1107: CURSOR c_seq
1108: IS
1109: SELECT AHL_SPACE_ASSIGNMENTS_S.NEXTVAL
1110: FROM dual;
1111: --
1112: CURSOR c_id_exists (x_id IN NUMBER) IS
1113: SELECT 1
1112: CURSOR c_id_exists (x_id IN NUMBER) IS
1113: SELECT 1
1114: FROM dual
1115: WHERE EXISTS (SELECT 1
1116: FROM ahl_space_assignments
1117: WHERE space_assignment_id = x_id);
1118: --
1119: BEGIN
1120: --------------------Initialize ----------------------------------
1156: p_x_space_assign_rec.visit_id <> FND_API.G_MISS_NUM ) THEN
1157: --
1158: AHL_DEBUG_PUB.debug( 'inside schedule flag:'||p_x_space_assign_rec.space_name);
1159: --
1160: DELETE FROM AHL_SPACE_ASSIGNMENTS
1161: WHERE visit_id = p_x_space_assign_rec.visit_id;
1162: --
1163: END IF;
1164: --
1433: Ahl_Debug_Pub.debug( 'Before insert state'||l_space_assignment_id);
1434:
1435: ----------------------------DML Operation---------------------------------
1436: --insert the record
1437: INSERT INTO AHL_SPACE_ASSIGNMENTS
1438: (
1439: SPACE_ASSIGNMENT_ID,
1440: SPACE_ID,
1441: VISIT_ID,
1702: x_msg_data => l_msg_data );
1703:
1704: ----------------------------DML Operation---------------------------------
1705: --Call table handler generated package to update a record
1706: UPDATE AHL_SPACE_ASSIGNMENTS
1707: SET visit_id = l_Space_assign_rec.visit_id,
1708: space_id = l_Space_assign_rec.space_id,
1709: object_version_number = l_Space_assign_rec.object_version_number+1,
1710: attribute_category = l_Space_assign_rec.attribute_category,
1844: CURSOR c_space_assign_cur
1845: (c_space_assignment_id IN NUMBER)
1846: IS
1847: SELECT space_assignment_id,object_version_number
1848: FROM ahl_space_assignments
1849: WHERE space_assignment_id = c_space_assignment_id;
1850:
1851: CURSOR c_visit_spaces_cur
1852: (c_visit_id IN NUMBER)
1861: sp.organization_id sporg_id,
1862: sp.bom_department_id spdept_id,
1863: sa.start_from space_start_date,
1864: sa.end_to space_end_date
1865: FROM ahl_space_assignments sa,
1866: ahl_visits_vl vt,
1867: ahl_spaces_b sp
1868: WHERE sa.visit_id = vt.visit_id
1869: AND sp.space_id = sa.space_id
1938: IF (nvl(l_visit_spaces_rec.organization_id,-1) <> l_visit_spaces_rec.sporg_id
1939: OR nvl(l_visit_spaces_rec.department_id,-1) <> l_visit_spaces_rec.spdept_id )
1940: THEN
1941: --Remove space assignments
1942: DELETE FROM AHL_SPACE_ASSIGNMENTS
1943: WHERE space_assignment_id = l_visit_spaces_rec.space_assignment_id;
1944: ELSE
1945: --Check for space Unnavailabilty condition
1946: /*
1958: l_visit_spaces_rec.start_date_time)));
1959: FETCH c_check_unavail_cur INTO l_dummy;
1960: IF c_check_unavail_cur%FOUND THEN
1961: --Remove space assignments
1962: DELETE FROM AHL_SPACE_ASSIGNMENTS
1963: WHERE space_Assignment_id = l_visit_spaces_rec.space_assignment_id;
1964: END IF;
1965: CLOSE c_check_unavail_cur;
1966: END IF;
2002: FND_MSG_PUB.add;
2003: RAISE FND_API.g_exc_error;
2004: END IF;
2005: -------------------Call Table handler generated procedure------------
2006: DELETE FROM AHL_SPACE_ASSIGNMENTS
2007: WHERE SPACE_ASSIGNMENT_ID = p_space_assign_rec.space_assignment_id;
2008: END IF;
2009: ---------------------------End of Body---------------------------------------
2010: --Standard check to count messages
2124: CURSOR c_space_assign_cur (c_visit_id IN NUMBER)
2125: IS
2126: SELECT space_assignment_id,
2127: object_version_number
2128: FROM AHL_SPACE_ASSIGNMENTS A
2129: WHERE VISIT_ID = c_visit_id;
2130: */
2131: -- anraj: commented, issue number 144
2132: /*
2429: LOOP
2430: FETCH c_space_assign_cur INTO l_space_assign_rec;
2431: EXIT WHEN c_space_assign_cur%NOTFOUND;
2432: --
2433: DELETE FROM ahl_space_assignments
2434: WHERE space_assignment_id = l_space_assign_rec.space_assignment_id;
2435: --
2436: END LOOP;
2437: CLOSE c_space_assign_cur;
2737: CURSOR c_space_assign_cur (c_visit_id IN NUMBER)
2738: IS
2739: SELECT space_assignment_id,
2740: object_version_number
2741: FROM AHL_SPACE_ASSIGNMENTS
2742: WHERE VISIT_ID = c_visit_id;
2743: */
2744: --
2745: /*
2895: LOOP
2896: FETCH c_space_assign_cur INTO l_space_assignment_id,l_space_version_number;
2897: EXIT WHEN c_space_assign_cur%NOTFOUND;
2898: -- Remove space assingment record
2899: DELETE FROM AHL_SPACE_ASSIGNMENTS
2900: WHERE space_assignment_id = l_space_assignment_id;
2901: --
2902: END LOOP;
2903: CLOSE c_space_assign_cur;