[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select VISIT_ID, start_date_time as START_DATE, close_date_time as PLANNED_END_DATE from AHL_VISITS_VL
where VISIT_ID = p_visit_id;
select SPA.SPACE_NAME SPACE_NAME,VST.start_date_time, start_from, VST.close_date_time, end_to
from ahl_space_assignments SPASSIGN,
AHL_VISITS_VL VST,ahl_spaces_vl SPA
WHERE
VST.VISIT_ID = SPASSIGN.VISIT_ID
AND SPA.SPACE_ID = SPASSIGN.SPACE_ID
AND
( start_from is not null AND end_to is not null
AND ( start_fromVST.close_date_time )
)
AND VST.VISIT_ID = vst_id;
SELECT lookup_code INTO x_lookup_code
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND SYSDATE BETWEEN start_date_active
AND NVL(end_date_active,SYSDATE);
SELECT lookup_code INTO x_lookup_code
FROM FND_LOOKUP_VALUES_VL
WHERE lookup_type = p_lookup_type
AND meaning = p_meaning
AND SYSDATE BETWEEN start_date_active
AND NVL(end_date_active,SYSDATE);
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 BOM_DEPARTMENTS
WHERE organization_id = p_organization_id
AND department_id = p_department_id
AND EXISTS ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = BOM_DEPARTMENTS.DEPARTMENT_ID);
SELECT department_id
INTO x_department_id
FROM BOM_DEPARTMENTS
WHERE organization_id = p_organization_id
AND description = p_dept_description
AND EXISTS ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = BOM_DEPARTMENTS.DEPARTMENT_ID);
SELECT space_id
INTO x_space_id
FROM AHL_SPACES_VL
WHERE space_name = p_space_name;
SELECT space_id
INTO x_space_id
FROM AHL_SPACES_VL
WHERE SPACE_ID = p_space_id;
SELECT visit_id
INTO x_visit_id
FROM AHL_VISITS_VL
WHERE visit_id = p_visit_id;
SELECT visit_id
INTO x_visit_id
FROM AHL_VISITS_VL
WHERE visit_number = p_visit_number;
SELECT ROWID ROW_ID,
SPACE_ASSIGNMENT_ID,
SPACE_ID,
VISIT_ID,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
START_FROM, -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
END_TO -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
FROM ahl_space_assignments
WHERE space_assignment_id = p_space_assign_rec.space_assignment_id;
SELECT space_assignment_id
FROM AHL_SPACE_ASSIGNMENTS
WHERE VISIT_ID = p_space_assign_rec.visit_id
AND SPACE_ID = p_space_assign_rec.space_id
AND space_assignment_id <> p_space_assign_rec.space_assignment_id
AND (( c_start_from IS NULL AND c_end_to IS NULL ) OR
( c_start_from IS NOT NULL AND c_end_to IS NOT NULL AND
( c_start_from between START_FROM AND END_TO OR c_end_to between START_FROM AND END_TO OR
START_FROM between c_start_from AND c_end_to OR END_TO between c_start_from AND c_end_to
)));
SELECT visit_type_code,
inventory_item_id,
trunc(start_date_time) start_date_time,
trunc(close_date_time)
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
SELECT trunc(start_date) start_date,
trunc(end_date) end_date
FROM ahl_space_unavailable_b
WHERE space_id = c_space_id;
SELECT space_capability_id
FROM ahl_space_capabilities
WHERE space_id = c_space_id
AND visit_type = c_visit_type
AND inventory_item_id = c_inventory_item_id;
SELECT trunc(start_date),trunc(end_date)
FROM ahl_space_unavailable_b
WHERE space_id = c_space_id
-- AND ((c_start_date between trunc(start_date) and trunc(end_date))
-- or (c_end_date between trunc(start_date) and trunc(end_date)));
SELECT trunc(start_date),trunc(end_date)
FROM ahl_space_unavailable_b
WHERE space_id = c_space_id
AND (((trunc(start_date) between trunc(c_start_date) and trunc(c_end_date))
OR (trunc(end_date) between trunc(c_start_date) and trunc(c_end_date)))
OR ((trunc(c_start_date) between trunc(start_date) and trunc(end_date))
or (trunc(c_end_date) between trunc(start_date) and trunc(end_date))));
SELECT AHL_SPACE_ASSIGNMENTS_S.NEXTVAL
FROM dual;
SELECT 1
FROM dual
WHERE EXISTS (SELECT 1
FROM ahl_space_assignments
WHERE space_assignment_id = x_id);
DELETE FROM AHL_SPACE_ASSIGNMENTS
WHERE visit_id = p_x_space_assign_rec.visit_id;
Ahl_Debug_Pub.debug( 'Before insert state'||l_space_assignment_id);
INSERT INTO AHL_SPACE_ASSIGNMENTS
(
SPACE_ASSIGNMENT_ID,
SPACE_ID,
VISIT_ID,
OBJECT_VERSION_NUMBER,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
START_FROM, -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
END_TO -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
)
VALUES
(
l_space_assignment_id,
l_space_assign_rec.space_id,
l_space_assign_rec.visit_id,
1,
l_space_assign_rec.attribute_category,
l_space_assign_rec.attribute1,
l_space_assign_rec.attribute2,
l_space_assign_rec.attribute3,
l_space_assign_rec.attribute4,
l_space_assign_rec.attribute5,
l_space_assign_rec.attribute6,
l_space_assign_rec.attribute7,
l_space_assign_rec.attribute8,
l_space_assign_rec.attribute9,
l_space_assign_rec.attribute10,
l_space_assign_rec.attribute11,
l_space_assign_rec.attribute12,
l_space_assign_rec.attribute13,
l_space_assign_rec.attribute14,
l_space_assign_rec.attribute15,
SYSDATE,
Fnd_Global.user_id,
SYSDATE,
Fnd_Global.user_id,
Fnd_Global.login_id,
l_space_assign_rec.start_from, -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
l_space_assign_rec.end_to -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
);
PROCEDURE Update_Space_Assignment (
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_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_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) := 'UPDATE_SPACE_ASSIGNMENT';
SAVEPOINT update_space_assignment;
AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Update Space Assignment','+SPANT+');
UPDATE AHL_SPACE_ASSIGNMENTS
SET visit_id = l_Space_assign_rec.visit_id,
space_id = l_Space_assign_rec.space_id,
object_version_number = l_Space_assign_rec.object_version_number+1,
attribute_category = l_Space_assign_rec.attribute_category,
attribute1 = l_Space_assign_rec.attribute1,
attribute2 = l_Space_assign_rec.attribute2,
attribute3 = l_Space_assign_rec.attribute3,
attribute4 = l_Space_assign_rec.attribute4,
attribute5 = l_Space_assign_rec.attribute5,
attribute6 = l_Space_assign_rec.attribute6,
attribute7 = l_Space_assign_rec.attribute7,
attribute8 = l_Space_assign_rec.attribute8,
attribute9 = l_Space_assign_rec.attribute9,
attribute10 = l_Space_assign_rec.attribute10,
attribute11 = l_Space_assign_rec.attribute11,
attribute12 = l_Space_assign_rec.attribute12,
attribute13 = l_Space_assign_rec.attribute13,
attribute14 = l_Space_assign_rec.attribute14,
attribute15 = l_Space_assign_rec.attribute15,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.user_id,
last_update_login = Fnd_Global.login_id,
start_from = l_Space_assign_rec.start_from, -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
end_to = l_Space_assign_rec.end_to -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011
WHERE space_assignment_id = p_space_assign_rec.space_assignment_id;
Ahl_Debug_Pub.debug( 'End of private api Update Space assignment','+SPANT+');
ROLLBACK TO update_space_assignment;
AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Update Space Assignment','+SPANT+');
ROLLBACK TO update_space_assignment;
AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Update Space Assignment','+SPANT+');
ROLLBACK TO update_space_assignment;
p_procedure_name => 'UPDATE_SPACE_ASSIGNMENT',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Update Space Assignemnt','+SPANT+');
END Update_Space_Assignment;
PROCEDURE Delete_Space_Assignment (
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_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_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_ASSIGNMENT';
SELECT space_assignment_id,object_version_number
FROM ahl_space_assignments
WHERE space_assignment_id = c_space_assignment_id;
SELECT sa.space_assignment_id,
sa.space_id,
sa.visit_id,
trunc(vt.start_date_time) start_date_time,
trunc(vt.close_date_time) close_date_time,
vt.organization_id,
vt.department_id,
sp.organization_id sporg_id,
sp.bom_department_id spdept_id,
sa.start_from space_start_date,
sa.end_to space_end_date
FROM ahl_space_assignments sa,
ahl_visits_vl vt,
ahl_spaces_b sp
WHERE sa.visit_id = vt.visit_id
AND sp.space_id = sa.space_id
AND vt.visit_id = c_visit_id;
SELECT 1
FROM ahl_space_unavailable_b
WHERE space_id = space_id
AND ((trunc(c_start_date) between trunc(start_date) and trunc(end_date)
OR
trunc(c_end_date) between trunc(start_date) and trunc(end_date))
OR (trunc(start_date) between trunc(c_start_date) and trunc(c_end_date)
OR
trunc(end_date) between trunc(c_start_date) and trunc(c_end_date)));
SAVEPOINT delete_space_assignment;
AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
DELETE FROM AHL_SPACE_ASSIGNMENTS
WHERE space_assignment_id = l_visit_spaces_rec.space_assignment_id;
DELETE FROM AHL_SPACE_ASSIGNMENTS
WHERE space_Assignment_id = l_visit_spaces_rec.space_assignment_id;
DELETE FROM AHL_SPACE_ASSIGNMENTS
WHERE SPACE_ASSIGNMENT_ID = p_space_assign_rec.space_assignment_id;
Ahl_Debug_Pub.debug( 'End of private api Delete Space Assignment','+SPANT+');
ROLLBACK TO delete_space_assignment;
AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
ROLLBACK TO delete_space_assignment;
AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
ROLLBACK TO delete_space_assignment;
p_procedure_name => 'DELETE_SPACE_ASSIGNMENT',
p_error_text => SUBSTR(SQLERRM,1,240));
AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
END Delete_Space_Assignment;
SELECT visit_id,
object_version_number,
status_code
FROM AHL_VISITS_B
WHERE VISIT_ID = c_visit_id;
SELECT space_assignment_id,
object_version_number
FROM AHL_SPACE_ASSIGNMENTS A
WHERE VISIT_ID = c_visit_id;
SELECT 1
FROM AHL_VISITS_VL
WHERE VISIT_ID = c_visit_id
AND (organization_id IS NULL
OR department_id IS NULL
OR start_date_time IS NULL );
SELECT organization_id,
trunc(start_date_time),
visit_name
FROM ahl_visits_vl
WHERE visit_id = p_x_schedule_visit_rec.visit_id;
SELECT organization_id,
organization_name,
department_id,
department_name,
visit_type_code
FROM ahl_visits_info_v
WHERE VISIT_ID = p_x_schedule_visit_rec.visit_id;
-- anraj : Commented the following block as Impelmented/Partially Implemented visits can also be updated.
/* IF (l_schedule_visit_rec.status_code <> 'PLANNING' )THEN
Fnd_Message.SET_NAME('AHL','AHL_VISIT_NOT_PLANNED');
DELETE FROM ahl_space_assignments
WHERE space_assignment_id = l_space_assign_rec.space_assignment_id;
SELECT visit_id, status_code,
object_version_number
FROM AHL_VISITS_B
WHERE VISIT_ID = c_visit_id;
SELECT space_assignment_id,
object_version_number
FROM AHL_SPACE_ASSIGNMENTS
WHERE VISIT_ID = c_visit_id;
SELECT scheduled_material_id,
object_version_number
FROM ahl_schedule_materials
WHERE visit_id = c_visit_id;
SELECT scheduled_date,scheduled_quantity
FROM ahl_visit_task_matrl_v
WHERE schedule_material_id = c_sch_mat_id;
UPDATE ahl_schedule_materials
SET requested_quantity = 0,
object_version_number = l_object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.user_id,
last_update_login = Fnd_Global.login_id
WHERE scheduled_material_id = l_schedule_material_id;
DELETE FROM AHL_SPACE_ASSIGNMENTS
WHERE space_assignment_id = l_space_assignment_id;
/* changes made by mpothuku on 12/20/04 for calling the VWP API to make the visit update instead of directly
updating the visit. */
-- Changes by mpothuku start
/*
UPDATE AHL_VISITS_B
SET organization_id = NULL,
department_id = NULL,
start_date_time = NULL,
close_date_time = NULL,
any_task_chg_flag = 'Y',
object_version_number = l_object_version_number + 1,
last_update_date = SYSDATE,
last_updated_by = Fnd_Global.user_id,
last_update_login = Fnd_Global.login_id
WHERE visit_id = l_visit_id;
SELECT visit_name INTO l_visit_name
FROM AHL_VISITS_VL WHERE VISIT_ID = l_visit_id;