Oracle

CREATE OR REPLACE PROCEDURE xx_relieve_reservation (
p_segment1 IN VARCHAR2,
p_organization_code IN VARCHAR2,
p_order_number IN VARCHAR2
)
AS
-- Common Declarations
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);
x_error_code NUMBER := 0;
-- WHO columns
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_row_cnt NUMBER := 1;
l_user_name VARCHAR2 (30) := 'SMOHIDEENPITCHAI';
l_resp_name VARCHAR2 (30) := 'INVENTORY';
-- API specific declarations
l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
l_serial_number inv_reservation_global.serial_number_tbl_type;
l_primary_relieved_qty NUMBER := 0;
l_validation_flag VARCHAR2 (2) := fnd_api.g_true;
x_mtl_reservation_tbl inv_reservation_global.mtl_reservation_tbl_type;
x_mtl_reservation_tbl_count NUMBER := 0;
x_primary_relieved_qty NUMBER := 0;
x_primary_remain_qty NUMBER := 0;

-- Get the reservation to be relieved
CURSOR c_item_reservations
IS
SELECT msi.organization_id,
msi.inventory_item_id,
msi.segment1,
msi.primary_uom_code,
res.reservation_id
FROM mtl_system_items_b msi,
mtl_parameters mp,
mtl_reservations res,
mtl_sales_orders mso
WHERE msi.segment1 = p_segment1 -- Item Name ---
AND msi.organization_id = mp.organization_id
AND mp.organization_code = p_organization_code -- Organization Short code --
AND res.organization_id = msi.organization_id
AND res.inventory_item_id = msi.inventory_item_id
AND res.demand_source_header_id = mso.sales_order_id
AND mso.segment1 = p_order_number; -- Sales Order Number --
BEGIN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;

-- Get the application_id and responsibility_id
SELECT application_id,
responsibility_id
INTO l_application_id,
l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;

fnd_global.apps_initialize (l_user_id,
l_resp_id,
l_application_id
);
DBMS_OUTPUT.put_line ( 'Initialized applications context: '
|| l_user_id
|| ' '
|| l_resp_id
|| ' '
|| l_application_id);

-- Get the first row
FOR ir IN c_item_reservations
LOOP
l_rsv_rec.organization_id := ir.organization_id;
l_rsv_rec.inventory_item_id := ir.inventory_item_id;
l_rsv_rec.reservation_id := ir.reservation_id;
DBMS_OUTPUT.put_line ( 'c_item_reservations'
|| ': '
|| ir.reservation_id);
--EXIT;

-- Get all reservations that exist for this item
-- call API to get all the reservations for this item
DBMS_OUTPUT.put_line ('=======================================================');
DBMS_OUTPUT.put_line ('Calling INV_RESERVATION_PUB.Query_Reservation');
inv_reservation_pub.query_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_query_input => l_rsv_rec,
p_lock_records => fnd_api.g_false,
p_sort_by_req_date => inv_reservation_global.g_query_no_sort,
p_cancel_order_mode => inv_reservation_global.g_cancel_order_no,
x_mtl_reservation_tbl => x_mtl_reservation_tbl,
x_mtl_reservation_tbl_count => x_mtl_reservation_tbl_count,
x_error_code => x_error_code
);
DBMS_OUTPUT.put_line ('=======================================================');
DBMS_OUTPUT.put_line ( 'Return Status: '
|| x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ( 'Error Message :'
|| x_msg_data);
END IF;

IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
FOR i IN 1 .. x_mtl_reservation_tbl_count
LOOP
DBMS_OUTPUT.put_line ('=======================================================');
DBMS_OUTPUT.put_line ( 'reservation_id : '
|| TO_CHAR (x_mtl_reservation_tbl (i).reservation_id));
DBMS_OUTPUT.put_line ( 'requirement_date : '
|| TO_CHAR (x_mtl_reservation_tbl (i).requirement_date, 'YYYY/MM/DD'));
DBMS_OUTPUT.put_line ( 'organization_id : '
|| TO_CHAR (x_mtl_reservation_tbl (i).organization_id));
DBMS_OUTPUT.put_line ( 'inventory_item_id : '
|| TO_CHAR (x_mtl_reservation_tbl (i).inventory_item_id));
DBMS_OUTPUT.put_line ( 'demand_source_type_id : '
|| TO_CHAR (x_mtl_reservation_tbl (i).demand_source_type_id));
DBMS_OUTPUT.put_line ( 'demand_source_name : '
|| x_mtl_reservation_tbl (i).demand_source_name);
DBMS_OUTPUT.put_line ( 'demand_source_header_id : '
|| TO_CHAR (x_mtl_reservation_tbl (i).demand_source_header_id));
DBMS_OUTPUT.put_line ( 'demand_source_line_id : '
|| TO_CHAR (x_mtl_reservation_tbl (i).demand_source_line_id));
DBMS_OUTPUT.put_line ( 'demand_source_line_detail : '
|| TO_CHAR (x_mtl_reservation_tbl (i).demand_source_line_detail));
DBMS_OUTPUT.put_line ( 'primary_uom_code : '
|| x_mtl_reservation_tbl (i).primary_uom_code);
DBMS_OUTPUT.put_line ( 'reservation_uom_code : '
|| x_mtl_reservation_tbl (i).reservation_uom_code);
DBMS_OUTPUT.put_line ( 'reservation_quantity : '
|| TO_CHAR (x_mtl_reservation_tbl (i).reservation_quantity));
DBMS_OUTPUT.put_line ( 'primary_reservation_quantity: '
|| TO_CHAR (x_mtl_reservation_tbl (i).primary_reservation_quantity));
DBMS_OUTPUT.put_line ( 'detailed_quantity : '
|| TO_CHAR (x_mtl_reservation_tbl (i).detailed_quantity));
DBMS_OUTPUT.put_line ( 'supply_source_type_id : '
|| TO_CHAR (x_mtl_reservation_tbl (i).supply_source_type_id));
DBMS_OUTPUT.put_line ( 'supply_source_header_id : '
|| TO_CHAR (x_mtl_reservation_tbl (i).supply_source_header_id));
DBMS_OUTPUT.put_line ( 'supply_source_line_id : '
|| TO_CHAR (x_mtl_reservation_tbl (i).supply_source_line_id));
DBMS_OUTPUT.put_line ( 'supply_source_name : '
|| (x_mtl_reservation_tbl (i).supply_source_name));
DBMS_OUTPUT.put_line ( 'supply_source_line_detail : '
|| TO_CHAR (x_mtl_reservation_tbl (i).supply_source_line_detail));
DBMS_OUTPUT.put_line ( 'subinventory_code : '
|| x_mtl_reservation_tbl (i).subinventory_code);
DBMS_OUTPUT.put_line ( 'ship_ready_flag : '
|| TO_CHAR (x_mtl_reservation_tbl (i).ship_ready_flag));
DBMS_OUTPUT.put_line ( 'staged_flag : '
|| x_mtl_reservation_tbl (i).staged_flag);
DBMS_OUTPUT.put_line ('=======================================================');
END LOOP;
END IF;

-- call API to relieve all the queried reservations for this item
DBMS_OUTPUT.put_line ('=======================================================');
DBMS_OUTPUT.put_line ('Calling INV_RESERVATION_PUB.Relieve_Reservation');

FOR i IN 1 .. x_mtl_reservation_tbl_count
LOOP
l_primary_relieved_qty := x_mtl_reservation_tbl (i).reservation_quantity;
--- Passing whole quantity to relieve
-- Call the API to relieve reservations for the provided serial numbers
inv_reservation_pub.relieve_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 => x_mtl_reservation_tbl (i),
p_primary_relieved_quantity => l_primary_relieved_qty,
p_relieve_all => fnd_api.g_true
---- True for Quantity Relieves to Zero
,
p_original_serial_number => l_serial_number,
p_validation_flag => l_validation_flag,
x_primary_relieved_quantity => x_primary_relieved_qty,
x_primary_remain_quantity => x_primary_remain_qty
);
DBMS_OUTPUT.put_line ('=======================================================');
DBMS_OUTPUT.put_line ( 'Return Status: '
|| x_return_status);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ( 'Error Message :'
|| x_msg_data);
END IF;

IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line ('Item reserved quantity relieve details');
DBMS_OUTPUT.put_line ( 'Reservation ID:'
|| x_mtl_reservation_tbl (i).reservation_id);
DBMS_OUTPUT.put_line ( 'Item ID:'
|| x_mtl_reservation_tbl (i).inventory_item_id);
DBMS_OUTPUT.put_line ( 'Organization ID:'
|| x_mtl_reservation_tbl (i).organization_id);
DBMS_OUTPUT.put_line ( 'Primary Relieved Quantity: '
|| x_primary_relieved_qty);
DBMS_OUTPUT.put_line ( 'Primary Remain Quantity: '
|| x_primary_remain_qty);
END IF;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception Occured :');
DBMS_OUTPUT.put_line ( SQLCODE
|| ':'
|| SQLERRM);
DBMS_OUTPUT.put_line ('=======================================================');
END;

----------------------------------------------------------
BEGIN
xx_relieve_reservation ('EAE05209965',
'UKI',
'70033015'
); -- Item , Organization Code, Sales Order number respectively
END;

============================================================

Oracle - eBIZ

Wednesday, May 21, 2014

Delete Reservations based on Item , Organization Code and Sales Order number

Total Pageviews