The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT organization_id
INTO x_organization_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE organization_id = p_organization_id;
SELECT organization_id
INTO x_organization_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE NAME = p_org_name;
SELECT department_id
INTO x_department_id
FROM HR_ALL_ORGANIZATION_UNITS HAOU, BOM_DEPARTMENTS BMDP
WHERE HAOU.organization_id = BMDP.organization_id
AND BMDP.organization_id = p_organization_id
AND BMDP.description = p_dept_description;
SELECT department_id
INTO x_department_id
FROM HR_ALL_ORGANIZATION_UNITS HAOU, BOM_DEPARTMENTS BMDP
WHERE HAOU.organization_id = BMDP.organization_id
AND BMDP.organization_id = (SELECT organization_id
FROM HR_ALL_ORGANIZATION_UNITS
WHERE NAME = p_org_name)
AND BMDP.description = p_dept_description;
SELECT space_id
INTO x_space_id
FROM AHL_SPACES_VL
WHERE space_id = p_space_id;
SELECT space_id
INTO x_space_id
FROM AHL_SPACES_VL
WHERE SPACE_NAME = p_space_name;
SELECT ROW_ID,
SPACE_ID,
START_DATE,
END_DATE,
DESCRIPTION,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
FROM ahl_space_unavailable_vl
WHERE space_unavailability_id = p_space_restriction_rec.space_unavailability_id
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SELECT a.visit_id,
space_id,
trunc(start_date_time) start_date_time
FROM ahl_space_Assignments a,
ahl_visits_b b
WHERE a.visit_id = b.visit_id
AND a.space_id = c_space_id;
SELECT start_date, end_date
FROM ahl_space_unavailable_b
WHERE space_unavailability_id = c_space_unavailability_id;
SELECT AHL_SPACE_UNAVAILABLE_B_S.NEXTVAL
FROM dual;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM ahl_space_unavailable_b
WHERE space_unavailability_id = x_id);
SELECT space_id
FROM AHL_SPACE_UNAVAILABLE_VL
WHERE space_id = c_space_id
AND trunc(start_date) = c_start_date
AND trunc(end_date) = c_end_date;
AHL_SPACE_UNAVAILABLE_PKG.INSERT_ROW (
X_ROWID => l_rowid,
X_SPACE_UNAVAILABILITY_ID => l_space_unavaialability_id,
X_SPACE_ID => p_x_space_restriction_rec.space_id,
X_START_DATE => p_x_space_restriction_rec.start_date,
X_END_DATE => p_x_space_restriction_rec.end_date,
X_OBJECT_VERSION_NUMBER => 1,
X_ATTRIBUTE_CATEGORY => l_space_restriction_rec.attribute_category,
X_ATTRIBUTE1 => l_space_restriction_rec.attribute1,
X_ATTRIBUTE2 => l_space_restriction_rec.attribute2,
X_ATTRIBUTE3 => l_space_restriction_rec.attribute3,
X_ATTRIBUTE4 => l_space_restriction_rec.attribute4,
X_ATTRIBUTE5 => l_space_restriction_rec.attribute5,
X_ATTRIBUTE6 => l_space_restriction_rec.attribute6,
X_ATTRIBUTE7 => l_space_restriction_rec.attribute7,
X_ATTRIBUTE8 => l_space_restriction_rec.attribute8,
X_ATTRIBUTE9 => l_space_restriction_rec.attribute9,
X_ATTRIBUTE10 => l_space_restriction_rec.attribute10,
X_ATTRIBUTE11 => l_space_restriction_rec.attribute11,
X_ATTRIBUTE12 => l_space_restriction_rec.attribute12,
X_ATTRIBUTE13 => l_space_restriction_rec.attribute13,
X_ATTRIBUTE14 => l_space_restriction_rec.attribute14,
X_ATTRIBUTE15 => l_space_restriction_rec.attribute15,
X_DESCRIPTION => l_space_restriction_rec.description,
X_CREATION_DATE => SYSDATE,
X_CREATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
PROCEDURE Update_Space_Restriction (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
p_module_type IN VARCHAR2 := 'JSP',
p_space_restriction_rec IN ahl_appr_space_unavl_pub.Space_Restriction_Rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
--
CURSOR check_duplicate_cur (c_space_id IN NUMBER,
c_start_date IN DATE,
c_end_date IN DATE)
IS
SELECT space_id
FROM AHL_SPACE_UNAVAILABLE_VL
WHERE space_id = c_space_id
AND trunc(start_date) = c_start_date
AND trunc(end_date) = c_end_date;
SELECT start_date,end_date
FROM AHL_SPACE_UNAVAILABLE_VL
WHERE space_unavailability_id = c_space_unavabl_id;
SELECT organization_id,bom_department_id
FROM ahl_spaces_b
WHERE space_id = c_space_id;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SPACE_RESTRICTION';
SAVEPOINT update_space_restriction;
AHL_DEBUG_PUB.debug( 'enter ahl_appr_space_unavl_pvt.Update Space Restriction','+SUAVL+');
AHL_SPACE_UNAVAILABLE_PKG.UPDATE_ROW
(
X_SPACE_UNAVAILABILITY_ID => l_Aspace_restriction_rec.space_unavailability_id,
X_SPACE_ID => l_Aspace_restriction_rec.space_id,
X_START_DATE => l_Aspace_restriction_rec.start_date,
X_END_DATE => l_Aspace_restriction_rec.end_date,
X_OBJECT_VERSION_NUMBER => l_Aspace_restriction_rec.object_version_number+1,
X_ATTRIBUTE_CATEGORY => l_Aspace_restriction_rec.attribute_category,
X_ATTRIBUTE1 => l_Aspace_restriction_rec.attribute1,
X_ATTRIBUTE2 => l_Aspace_restriction_rec.attribute2,
X_ATTRIBUTE3 => l_Aspace_restriction_rec.attribute3,
X_ATTRIBUTE4 => l_Aspace_restriction_rec.attribute4,
X_ATTRIBUTE5 => l_Aspace_restriction_rec.attribute5,
X_ATTRIBUTE6 => l_Aspace_restriction_rec.attribute6,
X_ATTRIBUTE7 => l_Aspace_restriction_rec.attribute7,
X_ATTRIBUTE8 => l_Aspace_restriction_rec.attribute8,
X_ATTRIBUTE9 => l_Aspace_restriction_rec.attribute9,
X_ATTRIBUTE10 => l_Aspace_restriction_rec.attribute10,
X_ATTRIBUTE11 => l_Aspace_restriction_rec.attribute11,
X_ATTRIBUTE12 => l_Aspace_restriction_rec.attribute12,
X_ATTRIBUTE13 => l_Aspace_restriction_rec.attribute13,
X_ATTRIBUTE14 => l_Aspace_restriction_rec.attribute14,
X_ATTRIBUTE15 => l_Aspace_restriction_rec.attribute15,
X_DESCRIPTION => l_Aspace_restriction_rec.description,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => Fnd_Global.USER_ID,
X_LAST_UPDATE_LOGIN => Fnd_Global.LOGIN_ID);
Ahl_Debug_Pub.debug( 'End of private api Update Space Restriction','+SUAVL+');
ROLLBACK TO update_space_restriction;
AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Update Space Restriction','+SUAVL+');
ROLLBACK TO update_space_restriction;
AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Update Space Restriction','+SUAVL+');
ROLLBACK TO update_space_restriction;
p_procedure_name => 'UPDATE_SPACE_RESTRICTION',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Update Space Restriction','+SUAVL+');
END Update_Space_Restriction;
PROCEDURE Delete_Space_Restriction (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.g_false,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validation_level IN NUMBER := FND_API.g_valid_level_full,
p_space_restriction_rec IN ahl_appr_space_unavl_pub.Space_Restriction_Rec,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_SPACE_RESTRICTION';
SELECT space_unavailability_id,object_version_number
FROM ahl_space_unavailable_vl
WHERE space_unavailability_id = c_space_unavailability_id
FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
SAVEPOINT delete_space_restriction;
AHL_DEBUG_PUB.debug( 'enter ahl_appr_space_unavl_pvt.Delete Space Restriction','+SUAVL+');
AHL_SPACE_UNAVAILABLE_PKG.DELETE_ROW (
X_SPACE_UNAVAILABILITY_ID => l_space_unavailability_id
);
Ahl_Debug_Pub.debug( 'End of private api Delete Space Restriction','+SUAVL+');
ROLLBACK TO delete_space_restriction;
AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Delete Space Restriction','+SUAVL+');
ROLLBACK TO delete_space_restriction;
AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Delete Space Restriction','+SUAVL+');
ROLLBACK TO delete_space_restriction;
p_procedure_name => 'DELETE_SPACE_RESTRICTION',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_appr_space_unavl_pvt.Delete Space Restriction','+SUAVL+');
END Delete_Space_Restriction;