[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INITIALIZE_WO_UPDATE_REC(
p_schedule_material_id IN NUMBER,
p_x_rsv_rec IN OUT NOCOPY mtl_reservation_rec_type,
x_return_status OUT NOCOPY VARCHAR2
);
SELECT asmt.organization_id, asmt.requested_date, asmt.uom,
nvl(asmt.requested_quantity,0) requested_quantity, asmt.inventory_item_id,
asmt.object_version_number, (SELECT SUM(mr.primary_reservation_quantity)
FROM mtl_reservations MR
WHERE mr .demand_source_line_detail = c_scheduled_material_id
AND mr.organization_id = asmt.organization_id
AND mr.requirement_date = asmt.requested_date
AND mr.inventory_item_id = asmt.inventory_item_id
AND mr.external_source_code = 'AHL'
GROUP BY mr.demand_source_line_detail
) reserved_quantity,
nvl(asmt.completed_quantity,0) completed_quantity, asmt.workorder_operation_id, asmt.operation_sequence,
aw.wip_entity_id,aw.workorder_id,wdj.project_id, wdj.task_id
FROM ahl_schedule_materials asmt, ahl_workorders aw, wip_discrete_jobs wdj
WHERE asmt.scheduled_material_id = c_scheduled_material_id
AND asmt.visit_task_id = aw.visit_task_id
AND aw.wip_entity_id = wdj.wip_entity_id
AND asmt.visit_id = aw.visit_id
AND aw.status_code in ('1','3') -- 1:Unreleased,3:Released
AND asmt.status = 'ACTIVE';
PROCEDURE UPDATE_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_TRUE,
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_reservation_id IN NUMBER ,
p_rsv_rec IN mtl_reservation_rec_type,
p_serial_number_tbl IN serial_number_tbl_type)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'update_reservation';
SELECT mrsv.primary_reservation_quantity,mrsv.inventory_item_id
FROM mtl_reservations mrsv
WHERE mrsv.reservation_id = c_resrv_id;
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_reservation_id IN NUMBER )
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'delete_reservation';
SAVEPOINT DELETE_RESERVATION_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
);
'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');
'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;
'Serial Number '||i||' to be Deleted: ' || l_serial_number_tbl(i).serial_number);
'After Relieve/Delete FND_API.G_EXC_UNEXPECTED_ERROR'
);
'After Relieve/Delete FND_API.G_EXC_ERROR'
);
SELECT subinventory_code,locator_id
FROM MTL_RESERVATIONS
WHERE reservation_id = c_rsrv_id;
SELECT current_subinventory_code,current_locator_id,lot_number,revision,reservation_id
FROM mtl_serial_numbers
WHERE serial_number = c_serial_number
AND inventory_item_id = c_inventory_item_id
AND current_organization_id = c_org_id;
'Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION : '
);
AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION
(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
p_module_type => G_PKG_NAME,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_reservation_id => l_create_rsv_rec.reservation_id,
p_rsv_rec => l_create_rsv_rec,
p_serial_number_tbl => l_serial_number_tbl
);
'After Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION: P_RESERVATION_ID := '|| l_x_reservation_id || ' and l_return_status = '||l_return_status
);
'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Unexpected Error'
);
'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Expected Error'
);
ELSE -- Check for Create/Update/Transfer
-- Validate to make sure that the Serial Number is not empty, when Supply is Inventory.
IF (l_serial_number_tbl.COUNT < 1) THEN
FND_MESSAGE.set_name( 'AHL', 'AHL_PP_SERIAL_MISSING' );
l_sub_inv_code_tbl.DELETE(l_index_tbl(k));
'Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION : Supply is Inventory'
);
AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
p_module_type => G_PKG_NAME,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
X_MSG_DATA => L_MSG_DATA,
P_RESERVATION_ID => l_x_reservation_id,
p_rsv_rec => l_create_rsv_rec,
p_serial_number_tbl => l_filter_srl_number_tbl);
'After Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION: P_RESERVATION_ID := '|| l_x_reservation_id || ' and l_return_status = '||l_return_status
);
'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Unexpected Error'
);
'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Expected Error'
);
l_filter_srl_number_tbl.delete;
l_index_tbl.delete;
'Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION : Supply is Workorder or Internal Requesition '
);
INITIALIZE_WO_UPDATE_REC(
p_schedule_material_id => l_create_rsv_rec.demand_source_line_detail,
p_x_rsv_rec => l_create_rsv_rec,
x_return_status => l_return_status
);
'After Calling INITIALIZE_WO_UPDATE_REC :l_return_status = '||l_return_status||
' And the project id is '||l_create_rsv_rec.project_id
);
AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION
(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
p_module_type => G_PKG_NAME,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_reservation_id => l_create_rsv_rec.reservation_id,
p_rsv_rec => l_create_rsv_rec,
p_serial_number_tbl => l_serial_number_tbl
);
'After Calling AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION: P_RESERVATION_ID := '|| l_x_reservation_id || ' and l_return_status = '||l_return_status
);
'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Unexpected Error'
);
'Call to AHL_MM_RESERVATIONS_PVT.UPDATE_RESERVATION returned Expected Error'
);
SELECT MR.inventory_item_id,MR.primary_reservation_quantity
FROM mtl_serial_numbers MSN, mtl_reservations MR
WHERE MSN.serial_number = c_serial_number
AND MSN.reservation_id = c_reservation_id
AND MR.reservation_id = MSN.reservation_id
AND MR.inventory_item_id = MSN.inventory_item_id
AND MR.external_source_code = 'AHL';
l_serial_tbl.DELETE;
AHL_MM_RESERVATIONS_PVT.DELETE_RESERVATION(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL, -- the validation level
p_module_type => G_PKG_NAME,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
X_MSG_DATA => L_MSG_DATA,
p_reservation_id => l_reservation_id);
'After Calling Relieve/Delete reservation for reservation_id := '|| l_reservation_id || ' and l_return_status = '||l_return_status
);
'Call to AHL_MM_RESERVATIONS_PVT.Relieve/Delete returned Unexpected Error'
);
'Call to AHL_MM_RESERVATIONS_PVT.Relieve/Delete returned Expected Error'
);
l_serial_tbl.DELETE;
l_tmp_serial_number_tbl.DELETE;
SELECT *
FROM mtl_reservations MR
WHERE MR.reservation_id = p_reservation_id
AND MR.external_source_code = 'AHL';
SELECT demand_source_line_detail
FROM mtl_reservations mrsv
WHERE reservation_id = c_reservation_id
AND mrsv.external_source_code = 'AHL';
SELECT mrsv.reservation_id, mrsv.primary_reservation_quantity
FROM mtl_reservations mrsv, ahl_schedule_materials asmt
WHERE mrsv.demand_source_line_detail = p_scheduled_material_id
AND mrsv.external_source_code = 'AHL'
AND NVL(mrsv.subinventory_code, '@@@') = NVL(p_sub_inv_code, '@@@')
AND NVL(mrsv.locator_id, -99) = NVL(p_locator_id, -99)
AND NVL(mrsv.revision, '@@@') = NVL(p_revision, '@@@')
AND NVL(mrsv.lot_number, '@@@') = NVL(p_lot_number, '@@@')
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.supply_source_type_id = p_supply_source_type_id
AND NVL(asmt.status,'') = 'ACTIVE';
PROCEDURE INITIALIZE_WO_UPDATE_REC(
p_schedule_material_id IN NUMBER,
p_x_rsv_rec IN OUT NOCOPY mtl_reservation_rec_type,
x_return_status OUT NOCOPY VARCHAR2
)
IS
-- Declare local variables
l_api_name CONSTANT VARCHAR2(30) := 'INITIALIZE_WO_UPDATE_REC';
'INITIALIZE_WO_UPDATE_REC, l_mtl_req_dtls_rec.project_id : ' || l_mtl_req_dtls_rec.PROJECT_ID ||
', l_mtl_req_dtls_rec.task_id : ' || l_mtl_req_dtls_rec.TASK_ID
);
END INITIALIZE_WO_UPDATE_REC;