API Create Reservations

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

Powered by WordPress.com. Tema: Baskerville 2 por Anders Noren

Acima ↑

%d blogueiros gostam disto: