[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT asmt.organization_id, asmt.requested_date, asmt.uom,
nvl(asmt.requested_quantity,0) requested_quantity, asmt.inventory_item_id,
asmt.object_version_number, nvl(asmt.reserved_quantity,0) reserved_quantity,
nvl(asmt.completed_quantity,0) completed_quantity
FROM ahl_material_requirements_v asmt
WHERE asmt.schedule_material_id = c_scheduled_material_id;
SELECT csi.instance_id,
msn.serial_number,
csi.inv_subinventory_name subinventory_code
FROM csi_item_instances csi,
mtl_serial_numbers msn
WHERE trunc(sysdate) >= trunc(nvl(CSI.active_start_date,sysdate))
AND trunc(sysdate) < trunc(nvl(CSI.active_end_date,sysdate+1))
AND msn.current_status = 3 -- inventory
AND msn.reservation_id is null
AND (msn.group_mark_id is null or msn.group_mark_id = -1)
AND csi.inventory_item_id = c_inventory_itme_id
AND csi.serial_number = c_serial_number
AND csi.last_vld_organization_id = c_organization_id
AND csi.inventory_item_id = msn.inventory_item_id
AND csi.serial_number = msn.serial_number;
SELECT mrsv.reservation_id, mrsv.primary_reservation_quantity
FROM mtl_reservations mrsv,ahl_schedule_materials asmt
WHERE mrsv.demand_source_line_detail = c_scheduled_material_id
AND external_source_code = 'AHL'
AND subinventory_code = c_subinventory_code
AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
AND mrsv.organization_id = asmt.organization_id
AND mrsv.requirement_date = asmt.requested_date
AND mrsv.inventory_item_id = asmt.inventory_item_id;
l_serial_number_tbl.DELETE;
'Calling WMS api:inv_reservation_pub.update_reservation'
);
inv_reservation_pub.update_reservation
(
p_api_version_number => l_api_version,
p_init_msg_lst => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_original_rsv_rec => l_from_rsv_rec,
p_to_rsv_rec => l_to_rsv_rec,
p_original_serial_number => l_serial_number_tbl,
p_to_serial_number => l_to_serial_number_tbl
);
PROCEDURE UPDATE_RESERVATION(
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_scheduled_material_id IN NUMBER ,
p_requested_date IN DATE)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'update_reservation';
SELECT asmt.requested_date
FROM ahl_schedule_materials asmt
WHERE asmt.scheduled_material_id = c_scheduled_material_id;
SELECT reservation_id
FROM mtl_reservations mrsv,ahl_schedule_materials asmt
WHERE mrsv.demand_source_line_detail = c_scheduled_material_id
AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
AND mrsv.organization_id = asmt.organization_id
AND mrsv.requirement_date = asmt.requested_date
AND mrsv.inventory_item_id = asmt.inventory_item_id
AND mrsv.external_source_code = 'AHL';
SAVEPOINT UPDATE_RESERVATION_PVT;
'Calling WMS api:inv_reservation_pub.update_reservation'
);
inv_reservation_pub.update_reservation
(
p_api_version_number => l_api_version,
p_init_msg_lst => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_original_rsv_rec => l_from_rsv_rec,
p_to_rsv_rec => l_to_rsv_rec,
p_original_serial_number => l_from_serial_number_tbl,
p_to_serial_number => l_to_serial_number_tbl
);
ROLLBACK TO UPDATE_RESERVATION_PVT;
ROLLBACK TO UPDATE_RESERVATION_PVT;
ROLLBACK TO UPDATE_RESERVATION_PVT;
p_procedure_name => 'update_reservation',
p_error_text => SUBSTR(SQLERRM,1,240)
);
END UPDATE_RESERVATION;
PROCEDURE DELETE_RESERVATION(
p_api_version IN NUMBER := 1.0,
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,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_scheduled_material_id IN NUMBER ,
p_sub_inventory_code IN VARCHAR2 := NULL,
p_serial_number IN VARCHAR2 := NULL
)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'delete_reservation';
SELECT 1
FROM ahl_schedule_materials asmt
WHERE asmt.scheduled_material_id = c_scheduled_material_id;
SELECT reservation_id
FROM mtl_reservations mrsv,ahl_schedule_materials asmt
WHERE mrsv.demand_source_line_detail = c_scheduled_material_id
AND mrsv.external_source_code = 'AHL'
AND (c_subinventory_code IS NULL OR mrsv.subinventory_code = c_subinventory_code)
AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
AND mrsv.organization_id = asmt.organization_id
AND mrsv.requirement_date = asmt.requested_date
AND mrsv.inventory_item_id = asmt.inventory_item_id;
SAVEPOINT DELETE_RESERVATION_PVT;
SELECT 1
INTO l_temp
FROM ahl_schedule_materials
WHERE scheduled_material_id = p_scheduled_material_id;
'About to Call inv_reservation_pub.delete_reservation with l_reservation_id: ' || l_reservation_id);
inv_reservation_pub.delete_reservation
(
p_api_version_number => l_api_version,
p_init_msg_lst => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rsv_rec => l_rsv_rec,
p_serial_number => l_serial_number_tbl
);
'Returned from inv_reservation_pub.delete_reservation, l_return_status: ' || l_return_status);
'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_UNEXP_ERROR');
'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_ERROR');
'Going to Call inv_reservation_pub.delete_reservation l_reservation_id :' || l_reservation_id
);
inv_reservation_pub.delete_reservation
(
p_api_version_number => l_api_version,
p_init_msg_lst => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rsv_rec => l_rsv_rec,
p_serial_number => l_serial_number_tbl
);
'After call inv_reservation_pub.delete_reservation,l_return_status :' || l_return_status
);
'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_UNEXP_ERROR'
);
'inv_reservation_pub.delete_reservation returned FND_API.G_RET_STS_ERROR'
);
'delete reservation COMMITTED'
);
ROLLBACK TO DELETE_RESERVATION_PVT;
ROLLBACK TO DELETE_RESERVATION_PVT;
ROLLBACK TO DELETE_RESERVATION_PVT;
p_procedure_name => 'delete_reservation',
p_error_text => SUBSTR(SQLERRM,1,240)
);
END DELETE_RESERVATION;
SELECT asmt.organization_id,
asmt.requested_date,
asmt.uom,
asmt.requested_quantity,
asmt.inventory_item_id,
asmt.object_version_number
FROM ahl_schedule_materials asmt,
ahl_visit_tasks_b avtl
WHERE asmt.status = 'ACTIVE'
AND asmt.requested_quantity <>0
AND asmt.scheduled_material_id = c_scheduled_material_id
AND asmt.visit_task_id = avtl.visit_task_id
AND ( avtl.status_code='PLANNING'
OR
( avtl.status_code='RELEASED'
AND
EXISTS ( SELECT awo.visit_task_id
FROM ahl_workorders awo
WHERE avtl.visit_task_id = awo.visit_task_id
AND (awo.status_code = '1' OR awo.status_code='3') -- 1:Unreleased,3:Released
)
)
);
SELECT reservation_id, primary_reservation_quantity
FROM mtl_reservations mrsv,ahl_schedule_materials asmt
WHERE mrsv.demand_source_line_detail = c_scheduled_material_id
AND mrsv.external_source_code = 'AHL'
AND mrsv.SUBINVENTORY_CODE = c_SUBINVENTORY_CODE
AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
AND mrsv.organization_id = asmt.organization_id
AND mrsv.requirement_date = asmt.requested_date
AND mrsv.inventory_item_id = asmt.inventory_item_id;
SELECT csi.instance_id, msn.serial_number,csi.inv_subinventory_name subinventory_code
FROM csi_item_instances csi,mtl_serial_numbers msn
WHERE csi.inventory_item_id = c_inventory_itme_id
AND csi.serial_number = c_serial_number
AND csi.last_vld_organization_id = c_organization_id
AND csi.inventory_item_id = msn.inventory_item_id;
'Serial Number to be Deleted:' || p_serial_number );
delete_reservation(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
p_module_type => p_module_type,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_scheduled_material_id => p_scheduled_material_id,
-- Begin Changes by skpathak on 12-NOV-2008 for bug 7241925
/**
p_sub_inventory_code => l_get_instance_dtls_rec.subinventory_code
**/
p_sub_inventory_code => null,
p_serial_number => p_serial_number
-- End Changes by skpathak on 12-NOV-2008 for bug 7241925
);
'After Relieve/Delete FND_API.G_EXC_UNEXPECTED_ERROR'
);
'After Relieve/Delete FND_API.G_EXC_ERROR'
);
SELECT mat.visit_task_id, mat.scheduled_material_id, mat.workorder_operation_id, mat.operation_sequence
FROM ahl_schedule_materials mat,
ahl_visit_tasks_b vt
WHERE mat.status = 'ACTIVE'
AND mat.requested_quantity <>0
AND vt.status_code = 'PLANNING'
AND vt.visit_task_id = mat.visit_task_id
AND vt.visit_id = c_visit_ID;
SELECT reservation_id
FROM mtl_reservations mrsv,ahl_schedule_materials asmt
WHERE demand_source_line_detail = c_scheduled_material_id
AND external_source_code = 'AHL'
AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
AND mrsv.organization_id = asmt.organization_id
AND mrsv.requirement_date = asmt.requested_date
AND mrsv.inventory_item_id = asmt.inventory_item_id;
SELECT aw.wip_entity_id
FROM ahl_workorders aw
WHERE aw.status_code in ('1','3') -- 1:Unreleased,3:Released
AND aw.visit_task_id = c_visit_task_id;
PROCEDURE UPDATE_VISIT_RESERVATIONS(
x_return_status OUT NOCOPY VARCHAR2,
p_visit_id IN NUMBER)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'update_visit_reservations';
SELECT mrsv.reservation_id, mrsv.demand_source_header_id, mrsv.demand_source_line_id, mrsv.inventory_item_id,mrsv.organization_id
FROM ahl_schedule_materials asmt,
ahl_visit_tasks_b vt,
mtl_reservations mrsv
WHERE vt.status_code = 'PLANNING'
AND vt.visit_task_id = asmt.visit_task_id
AND vt.visit_id = c_visit_id
AND mrsv.external_source_code = 'AHL'
AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
AND mrsv.organization_id = asmt.organization_id
AND mrsv.requirement_date = asmt.requested_date
AND mrsv.inventory_item_id = asmt.inventory_item_id;
SELECT scheduled_material_id, requested_date
FROM ahl_schedule_materials
WHERE visit_task_id = c_visit_task_id
AND rt_oper_material_id = c_rt_oper_material_id
AND inventory_item_id = c_inventory_item_id
AND status = 'ACTIVE';
SAVEPOINT UPDATE_VISIT_RESERVATIONS_PVT;
'Reservation ID to be updated:' || l_reservation_id
);
SELECT serial_number
BULK COLLECT INTO serial_num_tbl
FROM mtl_serial_numbers
WHERE reservation_id = l_reservation_id
AND INVENTORY_ITEM_ID = l_get_upd_rsv_rec.inventory_item_id
AND CURRENT_ORGANIZATION_ID = l_get_upd_rsv_rec.organization_id;
'Number of Serial Numbers to be updated: ' || l_to_rsv_rec.primary_reservation_quantity
);
inv_reservation_pub.update_reservation
(
p_api_version_number => l_api_version,
p_init_msg_lst => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_original_rsv_rec => l_from_rsv_rec,
p_to_rsv_rec => l_to_rsv_rec,
p_original_serial_number => l_from_serial_number_tbl,
p_to_serial_number => l_to_serial_number_tbl--,
);
'inv_reservation_pub.update_reservation returned UNEXPECTED ERROR'
);
'inv_reservation_pub.update_reservation returned EXPECTED ERROR'
);
END UPDATE_VISIT_RESERVATIONS;
PROCEDURE DELETE_VISIT_RESERVATIONS(
x_return_status OUT NOCOPY VARCHAR2,
p_visit_id IN NUMBER)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'delete_visit_reservations';
SELECT mrsv.reservation_id
FROM ahl_schedule_materials asmt,
ahl_visit_tasks_b avt,
mtl_reservations mrsv
WHERE avt.status_code in ( 'PLANNING','DELETED')
AND avt.visit_task_id = asmt.visit_task_id
AND avt.visit_id = c_visit_ID
AND avt.visit_id = asmt.visit_id
AND mrsv.external_source_code = 'AHL'
AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
AND mrsv.organization_id = asmt.organization_id
AND mrsv.requirement_date = asmt.requested_date
AND mrsv.inventory_item_id = asmt.inventory_item_id;
SAVEPOINT DELETE_VISIT_RESERVATIONS_PVT;
inv_reservation_pub.delete_reservation
(
p_api_version_number => l_api_version,
p_init_msg_lst => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_rsv_rec => l_rsv_rec,
p_serial_number => l_serial_number_tbl
);
END DELETE_VISIT_RESERVATIONS;
SELECT mat.scheduled_material_id , mat.organization_id,
mat.requested_date, mat.uom, mat.inventory_item_id,
mat. workorder_operation_id, vt.status_code task_status_code,
vt.visit_task_number, v.visit_number, mat.operation_sequence,
mat.visit_task_id, mat.rt_oper_material_id
FROM ahl_schedule_materials mat,
ahl_visits_b v,
ahl_visit_tasks_b vt
WHERE vt.visit_task_id = mat.visit_task_id
AND vt.visit_id = v.visit_id
AND mat.scheduled_material_id = c_scheduled_material_id;
SELECT aw.wip_entity_id
FROM ahl_workorders aw
WHERE aw.status_code in ('1','3') -- 1:Unreleased,3:Released
AND aw.visit_task_id = c_visit_task_id;
SELECT inv_subinventory_name,inv_locator_id,inventory_revision,lot_number
FROM csi_item_instances
WHERE serial_number = c_serial_number
AND inventory_item_id = c_inventory_item_id;
SELECT mrsv.reservation_id, mrsv.primary_reservation_quantity
FROM mtl_reservations mrsv, ahl_schedule_materials asmt, mtl_serial_numbers msn
WHERE mrsv.demand_source_line_detail = p_scheduled_material_id
AND mrsv.external_source_code = 'AHL'
AND msn.serial_number = p_serial_number
AND mrsv.organization_id = msn.current_organization_id
AND mrsv.inventory_item_id = msn.inventory_item_id
AND NVL(mrsv.subinventory_code, '@@@') = NVL(msn.current_subinventory_code, '@@@')
AND NVL(mrsv.locator_id, -99) = NVL(msn.current_locator_id, -99)
AND NVL(mrsv.revision, '@@@') = NVL(msn.revision, '@@@')
AND NVL(mrsv.lot_number, '@@@') = NVL(msn.lot_number, '@@@')
AND NVL(mrsv.lpn_id, -99) = NVL(msn.lpn_id, -99)
AND ((p_match_serial = 'N') OR (mrsv.reservation_id = msn.reservation_id))
AND mrsv.demand_source_line_detail = asmt.scheduled_material_id
AND mrsv.organization_id = asmt.organization_id
AND mrsv.requirement_date = asmt.requested_date
AND mrsv.inventory_item_id = asmt.inventory_item_id;