In This Post I want to show how create a reservations using API Oracle
Some times it’s necessary to use API for work with integrations.
The script below can be used for create a reservation.
DECLARE
l_rsv_rec INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
l_new_rsv_rec INV_RESERVATION_GLOBAL.MTL_RESERVATION_REC_TYPE;
l_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_new_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
x_serial_number INV_RESERVATION_GLOBAL.SERIAL_NUMBER_TBL_TYPE;
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := FND_API.G_TRUE;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (255);
l_partial_reservation_flag VARCHAR2 (2) := FND_API.G_FALSE;
l_force_reservation_flag VARCHAR2 (2) := FND_API.G_FALSE;
l_validation_flag VARCHAR2 (2) := FND_API.G_TRUE;
l_partial_reservation_exists BOOLEAN := FALSE;
x_quantity_reserved NUMBER := 0;
x_reservation_id NUMBER := 0;
x_secondary_quantity_reserved NUMBER;
v_sales_order_id NUMBER;
v_reservation_id NUMBER;
v_tem_reserva NUMBER;
v_item VARCHAR2 (40);
v_demand_source_name VARCHAR2 (400);
v_tem_distribuicao NUMBER;
v_quantidade NUMBER;
BEGIN
FND_GLOBAL.apps_initialize (FND_PROFILE.VALUE ('USER_ID'), 53377, 401); -- pass in user_id, responsibility_id, and application_id
MO_GLOBAL.set_policy_context ('S', FND_PROFILE.VALUE ('ORG_ID'));
MO_GLOBAL.set_org_context (FND_GLOBAL.ORG_ID, NULL, 'ONT');
MO_GLOBAL.init ('ONT');
l_rsv_rec.requirement_date := SYSDATE;
l_rsv_rec.organization_id := pi_organization_id;
l_rsv_rec.inventory_item_id := pi_inventory_item_id;
l_rsv_rec.demand_source_type_id := 2; -- Sales Order
l_rsv_rec.demand_source_name := NULL;
l_rsv_rec.demand_source_header_id := v_sales_order_id; -- mtl_sales_orders sales_order_id
l_rsv_rec.demand_source_line_id := pi_line_id; -- line_id oe_order_lines_all
l_rsv_rec.supply_source_type_id := 13; --reservas por inventario
l_rsv_rec.primary_reservation_quantity := pi_quantidade;
l_rsv_rec.subinventory_code := pi_subinventario;
l_rsv_rec.locator_id := pi_locator_id;
l_rsv_rec.primary_uom_code := 'PC';
l_rsv_rec.supply_source_header_id := NULL;
l_rsv_rec.supply_source_line_id := NULL;
l_rsv_rec.ship_ready_flag := NULL;
l_rsv_rec.reservation_uom_code := NULL;
l_rsv_rec.reservation_quantity := NULL;
l_rsv_rec.supply_source_header_id := NULL;
l_rsv_rec.supply_source_line_id := NULL;
l_rsv_rec.supply_source_name := NULL;
l_rsv_rec.supply_source_line_detail := NULL;
l_rsv_rec.lot_number := NULL;
l_rsv_rec.serial_number := NULL;
l_rsv_rec.ship_ready_flag := NULL;
l_rsv_rec.attribute15 := NULL;
l_rsv_rec.attribute14 := NULL;
l_rsv_rec.attribute13 := NULL;
l_rsv_rec.attribute12 := NULL;
l_rsv_rec.attribute11 := NULL;
l_rsv_rec.attribute10 := NULL;
l_rsv_rec.attribute9 := NULL;
l_rsv_rec.attribute8 := NULL;
l_rsv_rec.attribute7 := NULL;
l_rsv_rec.attribute6 := NULL;
l_rsv_rec.attribute5 := NULL;
l_rsv_rec.attribute4 := NULL;
l_rsv_rec.attribute3 := NULL;
l_rsv_rec.attribute2 := NULL;
l_rsv_rec.attribute1 := NULL;
l_rsv_rec.attribute_category := NULL;
l_rsv_rec.lpn_id := NULL;
l_rsv_rec.pick_slip_number := NULL;
l_rsv_rec.lot_number_id := NULL;
l_rsv_rec.subinventory_id := NULL;
l_rsv_rec.revision := NULL;
l_rsv_rec.external_source_line_id := NULL;
l_rsv_rec.external_source_code := NULL;
l_rsv_rec.autodetail_group_id := NULL;
l_rsv_rec.reservation_uom_id := NULL;
l_rsv_rec.primary_uom_id := NULL;
l_rsv_rec.demand_source_delivery := NULL;
INV_RESERVATION_PUB.create_reservation (p_api_version_number => l_api_version,
p_init_msg_lst => l_init_msg_list,
p_rsv_rec => l_rsv_rec,
p_serial_number => l_serial_number,
p_partial_reservation_flag => l_partial_reservation_flag,
p_force_reservation_flag => l_force_reservation_flag,
p_partial_rsv_exists => l_partial_reservation_exists,
p_validation_flag => l_validation_flag,
x_serial_number => x_serial_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_quantity_reserved => x_quantity_reserved,
x_reservation_id => x_reservation_id);
IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
pi_retorno := x_msg_data;
END IF;
IF (x_return_status = fnd_api.g_ret_sts_success) THEN
COMMIT;
END IF;
EXCEPTION WHEN OTHERS THEN
pi_retorno := DBMS_UTILITY.FORMAT_ERROR_STACK || ' - ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
END;
For update a existence record:
SELECT reservation_quantity, demand_source_name INTO v_quantidade, v_demand_source_name FROM mtl_reservations WHERE reservation_id = v_reservation_id; l_rsv_rec.reservation_id := pi_reservation_id; l_rsv_rec.reservation_quantity := v_quantidade; l_rsv_rec.demand_source_name := v_demand_source_name; INV_RESERVATION_PUB.update_reservation (p_api_version_number => l_api_version, p_init_msg_lst => l_init_msg_list, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_original_rsv_rec => l_rsv_rec, p_to_rsv_rec => l_new_rsv_rec, p_original_serial_number => l_serial_number, p_to_serial_number => l_new_serial_number, p_validation_flag => fnd_api.g_true, p_check_availability => fnd_api.g_false);
For delete a existence record:
l_rsv_rec.reservation_id := v_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 => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_rsv_rec => l_rsv_rec, p_serial_number => l_serial_number);
The APIs is always a good practice
I holp this help.
Deixe uma resposta