[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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,
status = 'DELETED',
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;
AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
X_RETURN_STATUS => X_RETURN_STATUS,
P_VISIT_ID => p_visit_id);
' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
);
PROCEDURE Insert_Planned_Matrls(
p_visit_id IN NUMBER,
p_visit_task_id IN NUMBER,
p_task_start_date IN DATE,
p_inventory_item_id IN NUMBER,
p_requested_quantity IN NUMBER,
p_uom_code IN VARCHAR2,
p_item_group_id IN NUMBER,
p_rt_oper_material_id IN NUMBER,
p_position_path_id IN NUMBER,
p_relationship_id IN NUMBER,
p_mr_route_id IN NUMBER default null,
p_item_comp_detail_id IN NUMBER default null,
p_inv_master_org_id IN NUMBER default null,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Check for record already exists
CURSOR check_matrl_cur (c_visit_id IN NUMBER,
c_visit_task_id IN NUMBER,
c_rt_oper_mat_id IN NUMBER)
IS
-- yazhou 17-May-2006 starts
-- bug fix#5232544
-- yazhou 03-JUL-2006 starts
-- bug fix#5303378
SELECT scheduled_material_id
FROM AHL_SCHEDULE_MATERIALS
WHERE visit_id = c_visit_id
AND visit_task_id = c_visit_task_id
-- AND requested_quantity <> 0
AND NVL(status,'') = 'ACTIVE'
AND rt_oper_material_id = c_rt_oper_mat_id;
SELECT organization_id
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
SELECT it.inventory_item_id,
it.priority,
it.uom_code,
it.quantity
FROM ahl_item_associations_vl it,
mtl_system_items_vl mt
WHERE it.inventory_item_id = mt.inventory_item_id
AND item_group_id = C_ITEM_GROUP_ID
AND mt.organization_id = C_ORG_ID
-- Fix for bug # 4109330
AND it.interchange_type_code in ('1-WAY INTERCHANGEABLE', '2-WAY INTERCHANGEABLE')
ORDER BY priority;
SELECT route_id
FROM ahl_mr_routes mr,
ahl_visit_tasks_b vt
WHERE mr.mr_route_id = vt.mr_route_id
AND visit_task_id = c_visit_task_id;
SELECT ro.step,
ro.operation_id,
ro.concatenated_segments
FROM ahl_route_operations_v ro,
ahl_rt_oper_materials rm
WHERE ro.operation_id = rm.object_id
AND ro.route_id = c_route_id
AND rm.rt_oper_material_id = c_rt_oper_mat_id
AND rm.association_type_code = 'OPERATION';
SELECT inventory_item_id,
primary_uom_code
FROM mtl_system_items_vl
WHERE inventory_item_id = C_ITEM_ID
AND organization_id = C_ORG_ID;
SELECT quantity,
in_service, --B5865210 - sowsubra
replace_percent,
association_type_code
FROM ahl_rt_oper_materials
WHERE rt_oper_material_id = c_rt_oper_mat_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Planned_Materials';
'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Insert_Planned_Matrls',
'At the start of PLSQL procedure'
);
SAVEPOINT Insert_Planned_Matrls;
SELECT ahl_schedule_materials_s.nextval INTO l_schedule_material_id
FROM DUAL;
SELECT TASK_TYPE_CODE INTO l_task_type_code
FROM ahl_visit_tasks_vl
WHERE visit_task_id = p_visit_task_id;
'Before Insert Schedule Materials for Visit Id : '|| p_visit_id
);
'Before Insert Schedule Materials for Visit Task Id : '|| p_visit_task_id
);
'Before Insert Schedule Materials for Schedule Material Id : '|| l_schedule_material_id
);
INSERT INTO AHL_SCHEDULE_MATERIALS
(SCHEDULED_MATERIAL_ID,
OBJECT_VERSION_NUMBER,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
INVENTORY_ITEM_ID,
SCHEDULE_DESIGNATOR,
VISIT_ID,
VISIT_START_DATE,
VISIT_TASK_ID,
ORGANIZATION_ID,
SCHEDULED_DATE,
REQUESTED_QUANTITY,
REQUEST_ID,
REQUESTED_DATE,
SCHEDULED_QUANTITY,
PROCESS_STATUS,
ERROR_MESSAGE,
TRANSACTION_ID,
UOM,
RT_OPER_MATERIAL_ID,
OPERATION_CODE,
ITEM_GROUP_ID,
OPERATION_SEQUENCE,
POSITION_PATH_ID,
RELATIONSHIP_ID,
MR_ROUTE_ID,
MATERIAL_REQUEST_TYPE,
STATUS,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15
)
VALUES
(l_schedule_material_id,
1,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
l_inventory_item_id,
NULL,
p_visit_id,
NULL,
p_visit_task_id,
l_organization_id,
NULL,
l_prim_quantity,
NULL,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
trunc(p_task_start_date),
l_sched_prim_quantity, -- yazhou 04Aug2005
NULL,
NULL,
NULL,
l_uom_code,
p_rt_oper_material_id,
l_operation_code,
p_item_group_id,
l_step,
p_position_path_id,
p_relationship_id,
p_mr_route_id,
l_material_request_type,
l_mat_status, --Added by sowsubra for Issue 105
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL);
'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Insert Planned Matrls.end',
'At the end of PLSQL procedure'
);
ROLLBACK TO Insert_Planned_Matrls;
ROLLBACK TO Insert_Planned_Matrls;
ROLLBACK TO Insert_Planned_Matrls;
p_procedure_name => 'INSERT_PLANNED_MATRLS',
p_error_text => SUBSTR(SQLERRM,1,500));
END Insert_Planned_Matrls;
PROCEDURE Update_Planned_Materials (
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_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR Get_Planned_Items_cur (c_sched_mat_id IN NUMBER)
IS
SELECT scheduled_material_id,
object_version_number,
inventory_item_id,
requested_quantity,
visit_task_id,
organization_id,
completed_quantity,
requested_date,
visit_id
FROM ahl_schedule_materials
WHERE scheduled_material_id = c_sched_mat_id;
SELECT inventory_item_id
FROM mtl_system_items_vl
WHERE concatenated_segments = c_item_desc
AND organization_id = c_org_id;
SELECT start_date_time, close_date_time
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Planned_Materials';
'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update_Planned_Materials',
'At the start of PLSQL procedure'
);
SAVEPOINT Update_Planned_Materials;
'Request for Update Material Number of Records : '|| l_planned_materials_tbl.COUNT
);
SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
INTO l_rsvd_quantity
FROM mtl_reservations MR,
ahl_schedule_materials SM
WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
AND MR.external_source_code = 'AHL'
AND MR.demand_source_line_detail = SM.scheduled_material_id
AND MR.organization_id = SM.organization_id
AND MR.requirement_date = SM.requested_date
AND MR.inventory_item_id = SM.inventory_item_id;
SELECT SUM(MR.PRIMARY_RESERVATION_QUANTITY)
INTO l_rsvd_quantity
FROM mtl_reservations MR,
ahl_schedule_materials SM
WHERE MR.DEMAND_SOURCE_LINE_DETAIL = l_planned_materials_tbl(i).schedule_material_id
AND MR.external_source_code = 'AHL'
AND MR.demand_source_line_detail = SM.scheduled_material_id
AND MR.organization_id = SM.organization_id
AND MR.requirement_date = SM.requested_date
AND MR.inventory_item_id = SM.inventory_item_id;
AHL_RSV_RESERVATIONS_PVT.UPDATE_RESERVATION(
P_API_VERSION => 1.0,
/*P_INIT_MSG_LIST
P_COMMIT
P_VALIDATION_LEVEL */
P_MODULE_TYPE => NULL,
X_RETURN_STATUS => l_return_Status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => X_MSG_DATA,
P_SCHEDULED_MATERIAL_ID => l_planned_materials_tbl(i).schedule_material_id,
P_REQUESTED_DATE => l_planned_materials_tbl(i).requested_date);
UPDATE ahl_schedule_materials
SET inventory_item_id = l_planned_materials_tbl(i).inventory_item_id,
requested_quantity = l_planned_materials_tbl(i).quantity,
-- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
requested_date = trunc(l_planned_materials_tbl(i).requested_date),
object_version_number = l_planned_materials_tbl(i).object_version_number + 1
WHERE scheduled_material_id = l_planned_materials_tbl(i).schedule_material_id;
'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update Planned Materials.end',
'At the end of PLSQL procedure'
);
ROLLBACK TO Update_Planned_Materials;
ROLLBACK TO Update_Planned_Materials;
ROLLBACK TO Update_Planned_Materials;
p_procedure_name => 'Update_Planned_Materials',
p_error_text => SUBSTR(SQLERRM,1,500));
END Update_Planned_Materials;
SELECT vs.visit_id,
vs.organization_id,
vt.visit_task_id,
vt.mr_route_id,
vt.instance_id,
vt.start_date_time
FROM ahl_visits_b vs,
ahl_visit_tasks_b vt
WHERE vs.visit_id = vt.visit_id
AND vt.visit_task_id = C_VISIT_TASK_ID;
SELECT mr.route_id
FROM ahl_mr_routes_app_v mr
WHERE mr.mr_route_id = C_MR_ROUTE_ID;
SELECT 1
FROM ahl_visits_vl
WHERE visit_id = C_VISIT_ID
AND (organization_id IS NULL
OR start_date_time IS NULL);
Insert_Planned_Matrls(
p_visit_id => p_visit_id,
p_visit_task_id => l_visit_tasks_rec.visit_task_id,
p_task_start_date => l_visit_tasks_rec.start_date_time,
p_inventory_item_id => l_route_mtl_req_tbl(i).inventory_item_id,
p_requested_quantity => l_route_mtl_req_tbl(i).quantity,
p_uom_code => l_route_mtl_req_tbl(i).uom_code,
p_item_group_id => l_route_mtl_req_tbl(i).item_group_id,
p_rt_oper_material_id => l_route_mtl_req_tbl(i).rt_oper_material_id,
p_position_path_id => l_route_mtl_req_tbl(i).position_path_id,
p_relationship_id => l_route_mtl_req_tbl(i).relationship_id,
p_mr_route_id => l_visit_tasks_rec.mr_route_id,
p_item_comp_detail_id => l_route_mtl_req_tbl(i).item_comp_detail_id,
p_inv_master_org_id => l_visit_tasks_rec.organization_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
'After calling Insert Planned Materials, Return Status : '|| l_return_status
);
SELECT vs.visit_id,
vs.organization_id,
vt.visit_task_id,
vt.mr_route_id,
vt.instance_id,
nvl(vt.start_date_time,vs.start_date_time) start_date_time,
mr.route_id
FROM ahl_visits_vl vs,
ahl_visit_tasks_vl vt,
ahl_mr_routes_app_v mr
WHERE vs.visit_id = vt.visit_id
AND vt.mr_route_id = mr.mr_route_id
AND vs.visit_id = C_VISIT_ID
-- Modified by amagrawa based on Enhancement
AND vt.status_code = 'PLANNING';
SELECT mr.route_id,
vt.instance_id,
vt.start_date_time
FROM ahl_visit_tasks_vl vt,
ahl_mr_routes_app_v mr
WHERE vt.mr_route_id = mr.mr_route_id
AND mr.mr_route_id = C_MR_ROUTE_ID;
CURSOR Deleted_Items_Cur (c_visit_id IN NUMBER)
IS
SELECT asm.visit_id,
asm.scheduled_material_id scheduled_material_id,
asm.object_version_number,
asm.scheduled_quantity,
asm.scheduled_date
FROM ahl_visit_tasks_b tsk,ahl_schedule_materials asm
WHERE asm.visit_id = C_VISIT_ID
AND asm.visit_task_id = tsk.visit_task_id
AND tsk.status_code ='DELETED'
AND asm.status <> 'DELETED';
SELECT requested_quantity,
scheduled_material_id,
object_version_number
FROM ahl_schedule_materials
WHERE visit_task_id = c_visit_task_id
AND rt_oper_material_id = c_rt_oper_material_id
AND NVL(STATUS, 'X') = 'ACTIVE';
l_Deleted_Items_Rec Deleted_Items_Cur%rowtype;
SELECT organization_id
FROM ahl_visits_b
WHERE visit_id = C_VISIT_ID
AND ( organization_id IS NOT NULL
OR start_date_time IS NOT NULL
OR department_id IS NOT NULL
);
OPEN Deleted_Items_cur(p_visit_id);
FETCH Deleted_Items_cur INTO l_Deleted_Items_Rec;
EXIT WHEN Deleted_Items_cur%NOTFOUND;
IF l_Deleted_Items_Rec.scheduled_material_id IS NOT NULL THEN
IF (l_log_procedure >= l_log_current_level)THEN
fnd_log.string
(
l_log_procedure,
'AHL.PLSQL.AHL_LTP_REQST_MATRL_PVT.MODIFY_VISIT_TASK_MATRLS',
'Updating the status to DELETED for Material Requirement' || l_Deleted_Items_Rec.scheduled_material_id
);
UPDATE ahl_schedule_materials
SET requested_quantity =0,
status = 'DELETED',
object_version_number = l_Deleted_Items_Rec.object_version_number + 1
WHERE scheduled_material_id = l_Deleted_Items_Rec.scheduled_material_id ;
CLOSE Deleted_Items_cur;
'Updating the status to DELETED for Material Requirement' || l_Planned_Items_Rec.scheduled_material_id
);
UPDATE ahl_schedule_materials
SET requested_quantity =0,
status = 'DELETED',
object_version_number = l_Planned_Items_Rec.object_version_number + 1
WHERE scheduled_material_id = l_Planned_Items_Rec.scheduled_material_id ;
Insert_Planned_Matrls(
p_visit_id => p_visit_id,
p_visit_task_id => l_Visit_Task_Route_Tbl(i).visit_task_id,
p_task_start_date => l_Visit_Task_Route_Tbl(i).task_start_date,
p_inventory_item_id => l_route_mtl_req_tbl(j).inventory_item_id,
p_requested_quantity => l_requested_qty,
p_uom_code => l_route_mtl_req_tbl(j).uom_code,
p_item_group_id => l_route_mtl_req_tbl(j).item_group_id,
p_rt_oper_material_id => l_route_mtl_req_tbl(j).rt_oper_material_id,
p_position_path_id => l_route_mtl_req_tbl(j).position_path_id,
p_relationship_id => l_route_mtl_req_tbl(j).relationship_id,
p_mr_route_id => l_Visit_Task_Route_Tbl(i).mr_route_id,
p_item_comp_detail_id => l_route_mtl_req_tbl(j).item_comp_detail_id,
-- AnRaj: changed the paramter, for fixing bug where org id was being incorrectly updated
p_inv_master_org_id => l_visit_org_id ,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data );
'After calling Insert Planned Materials, Return Status : '|| l_return_status
);
SELECT ASMT.visit_id,
ASMT.visit_task_id,
ASMT.scheduled_material_id schedule_material_id,
decode(sign( trunc(ASMT.scheduled_date) - trunc(requested_date)), 1, ASMT.scheduled_date, null) SCHEDULED_DATE,
ASMT.SCHEDULED_QUANTITY
FROM AHL_SCHEDULE_MATERIALS ASMT,
AHL_VISIT_TASKS_B VTSK
WHERE ASMT.STATUS <> 'DELETED'
AND EXISTS ( Select 1
from AHL_RT_OPER_MATERIALS RTOM
where RTOM.RT_OPER_MATERIAL_ID = ASMT.RT_OPER_MATERIAL_ID)
AND VTSK.VISIT_ID = ASMT.VISIT_ID
AND VTSK.VISIT_TASK_ID = ASMT.VISIT_TASK_ID
AND NVL(VTSK.STATUS_CODE,'X') <> 'DELETED'
AND ASMT.VISIT_ID = C_VISIT_ID
AND scheduled_date IS NOT NULL;
SELECT visit_id,visit_task_id,schedule_material_id,
scheduled_date,scheduled_quantity
FROM ahl_visit_task_matrl_v
WHERE visit_id = C_VISIT_ID
AND scheduled_date IS NOT NULL;
SELECT asso_primary_visit_id
FROM ahl_visits_b
WHERE visit_id = c_visit_id;
'Request for Visit Closed or Cancelled Update Unplanned materials for Visit Id : '|| p_visit_id
);
'Before calling Update Unplanned Visit Materials'
);
Update_Unplanned_Matrls (
p_api_version => l_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => l_commit,
p_validation_level => p_validation_level,
p_visit_id => p_visit_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'After calling Update Unplanned Materials, Return Status : '|| l_return_status
);
SELECT vs.visit_id,
vs.organization_id,
vt.visit_task_id
FROM ahl_visits_vl vs,
ahl_visit_tasks_vl vt
WHERE vs.visit_id = vt.visit_id
AND vs.visit_id = c_visit_id
AND vt.visit_task_id = c_visit_task_id;
SELECT visit_id,
visit_task_id,
schedule_material_id,
object_version_number,
inventory_item_id,
scheduled_date,
scheduled_quantity
FROM ahl_visit_task_matrl_v
WHERE visit_task_id = c_visit_task_id;
SELECT visit_id,
visit_task_id,
schedule_material_id,
object_version_number,
inventory_item_id,
scheduled_date,
scheduled_quantity
FROM ahl_visit_task_matrl_v
WHERE visit_id = c_visit_id;
UPDATE ahl_schedule_materials
SET requested_quantity = 0,
status = 'DELETED',
object_version_number = l_visit_task_mtrls_rec.object_version_number + 1
WHERE scheduled_material_id = l_visit_task_mtrls_rec.schedule_material_id;
UPDATE ahl_schedule_materials
SET requested_quantity = 0,
status = 'DELETED',
object_version_number = l_visit_mtrls_rec.object_version_number + 1
WHERE scheduled_material_id = l_visit_mtrls_rec.schedule_material_id;
AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
X_RETURN_STATUS => X_RETURN_STATUS,
P_VISIT_ID => p_visit_id);
' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
);
PROCEDURE Update_Unplanned_Matrls (
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_visit_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
CURSOR visit_mtrls_cur (c_visit_id IN NUMBER)
IS
SELECT visit_id,
visit_task_id,
scheduled_material_id,
object_version_number
FROM ahl_schedule_materials
WHERE visit_id = c_visit_id
AND status = 'ACTIVE';
l_api_name CONSTANT VARCHAR2(30) := 'Update_Unplanned_Matrls';
'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update_Unplanned_Matrls',
'At the start of PLSQL procedure'
);
SAVEPOINT Update_Unplanned_Matrls;
'Request for Update Materials for Visit Id : '|| p_visit_id
);
UPDATE ahl_schedule_materials
SET STATUS = 'HISTORY',
OBJECT_VERSION_NUMBER = l_visit_mtrls_rec.object_version_number
WHERE scheduled_material_id = l_visit_mtrls_rec.scheduled_material_id;
AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
X_RETURN_STATUS => X_RETURN_STATUS,
P_VISIT_ID => p_visit_id);
' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
);
'ahl.plsql.AHL_LTP_REQST_MATRL_PVT.Update Unplanned Matrls.end',
'At the end of PLSQL procedure'
);
ROLLBACK TO Update_Unplanned_Matrls;
ROLLBACK TO Update_Unplanned_Matrls;
ROLLBACK TO Update_Unplanned_Matrls;
p_procedure_name => 'UPDATE_UNPLANNED_MATRLS',
p_error_text => SUBSTR(SQLERRM,1,500));
END Update_Unplanned_Matrls;
SELECT mat.scheduled_material_id
FROM ahl_schedule_materials mat,
ahl_visit_tasks_b vt
WHERE vt.visit_id = c_visit_id
AND vt.status_code = 'DELETED'
AND vt.visit_task_id = mat.visit_task_id
AND EXISTS (SELECT reservation_id
FROM mtl_reservations RSV
WHERE RSV.external_source_code = 'AHL'
AND RSV.demand_source_line_detail = mat.scheduled_material_id
AND RSV.organization_id = mat.organization_id
AND RSV.requirement_date = mat.requested_date
AND RSV.inventory_item_id = mat.inventory_item_id );
SELECT organization_id
FROM ahl_visits_b
WHERE visit_id = p_visit_id;
SELECT organization_id
FROM mtl_reservations
WHERE external_source_code = 'AHL'
AND demand_source_header_id in ( SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id);
SELECT count(distinct organization_id)
INTO l_org_count
FROM mtl_reservations
WHERE external_source_code = 'AHL'
AND demand_source_header_id in ( SELECT visit_task_id
FROM ahl_visit_tasks_b
WHERE visit_id = p_visit_id);
AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS(
X_RETURN_STATUS => X_RETURN_STATUS,
P_VISIT_ID => p_visit_id);
'After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS:X_RETURN_STATUS '||X_RETURN_STATUS
);
AHL_RSV_RESERVATIONS_PVT.Delete_Reservation(
p_module_type => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_scheduled_material_id => l_scheduled_material_id
);
'After calling AHL_RSV_RESERVATIONS_PVT.Delete_Reservation:l_return_status '||l_return_status
);
END LOOP; -- For all the material requirements of the deleted tasks
AHL_RSV_RESERVATIONS_PVT.Update_Visit_Reservations(
X_RETURN_STATUS => x_return_status,
P_VISIT_ID => p_visit_id);
'After calling AHL_RSV_RESERVATIONS_PVT.Update_Visit_Reservations:x_return_status '||x_return_status
);
PROCEDURE Update_Material_Reqrs_status
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_module_type IN VARCHAR2,
p_visit_task_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'Update_Material_Reqrs_status';
SAVEPOINT Update_Material_Reqrs_sts;
UPDATE ahl_Schedule_materials
SET STATUS = 'HISTORY',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1,
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = Fnd_Global.USER_ID,
LAST_UPDATE_LOGIN = Fnd_Global.LOGIN_ID
WHERE visit_task_id = p_visit_task_id
AND STATUS = 'ACTIVE';
'No of rows updated - '||SQL%ROWCOUNT);
ROLLBACK TO Update_Material_Reqrs_sts;
ROLLBACK TO Update_Material_Reqrs_sts;
ROLLBACK TO Update_Material_Reqrs_sts;
END Update_Material_Reqrs_status;