PROCEDURE create_reservation (
error_buf OUT VARCHAR2,
ret_code OUT VARCHAR2,
p_order_number IN NUMBER,
p_line_id IN NUMBER,
p_line_number IN NUMBER
)
IS
l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
l_serial_number inv_reservation_global.serial_number_tbl_type;
v_serial_number inv_reservation_global.serial_number_tbl_type;
l_return_status VARCHAR2 (50);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (250);
v_quantity_reserved NUMBER;
v_reservation_id NUMBER;
l_message VARCHAR2 (255);
l_msg_index_out NUMBER;
l_line_rec oe_order_pub.line_rec_type;
l_x_line_rec oe_order_pub.line_rec_type;
l_quantity2_to_reserve NUMBER;
l_requirement_date DATE; --:= SYSDATE;
l_organization_id NUMBER;
l_inventory_item_id NUMBER;
l_header_id NUMBER;
l_primary_uom_code VARCHAR2 (40);
l_reservation_uom_code VARCHAR2 (40) := 'EA';
l_reservation_quantity NUMBER := 1;
l_primary_reservation_quantity NUMBER := 1;
l_supply_source_type_id NUMBER;
l_supply_source_name VARCHAR2 (240);
--:= 'OE 1000274 reserv Test';
l_supply_source_line_detail VARCHAR2 (240); --:= NULL;
l_supply_source_line_id NUMBER; --:= NULL;
l_supply_source_header_id NUMBER; --:= NULL;
l_external_source_line_id NUMBER; --:= NULL;
l_external_source_code VARCHAR2 (240); --:= NULL;
l_autodetail_group_id NUMBER; --:= NULL;
l_reservation_uom_id NUMBER; --:= NULL;
l_primary_uom_id NUMBER; --:= NULL;
l_revision NUMBER; --:= NULL;
l_subinventory_code VARCHAR2 (240); --:= 'SINV01';
l_subinventory_id NUMBER; --:= NULL;
l_locator_id NUMBER; --:= 6;
l_lot_number NUMBER; --:= 987988;
l_lot_number_id NUMBER; --:= NULL;
l_crossdock_flag VARCHAR2 (40); --:= ''; -- 'N';
l_ship_ready_flag VARCHAR2 (40); --:= NULL;
l_demand_source_type_id NUMBER;
l_demand_source_delivery NUMBER; --:= NULL;
l_demand_source_name VARCHAR2 (240); --:= 'Sales order';
l_orig_supply_source_type_id NUMBER; --:= 13;
l_orig_demand_source_type_id NUMBER; --= 2;
l_orig_demand_source_header_id NUMBER;
--:= NULL; --2854;
l_orig_demand_source_line_id NUMBER; --:= 1998;
l_pick_slip_number NUMBER; --:= NULL;
l_lpn_id NUMBER; --:= NULL;
l_attribute_category VARCHAR2 (240); --:= NULL;
l_current_location VARCHAR2 (200);
l_record VARCHAR2 (240);
l_item VARCHAR2 (240);
CURSOR c_wdj
IS
SELECT wdj.attribute1, wdj.attribute2, wdj.attribute3
FROM wip_discrete_jobs wdj,
oe_order_lines_all oel,
oe_order_headers_all oeh
WHERE oel.line_id = NVL (p_line_id, TO_NUMBER (wdj.attribute3))
AND oel.line_id = NVL (p_line_id, oel.line_id)
AND oel.flow_status_code = 'AWAITING_SHIPPING'
AND oel.header_id = oeh.header_id
AND oeh.order_number = NVL (p_order_number, oeh.order_number)
AND oeh.order_number NVL (p_order_number, TO_NUMBER (wdj.attribute1))
AND wdj.attribute1 IS NOT NULL
AND wdj.attribute2 IS NOT NULL
AND wdj.attribute3 IS NOT NULL
AND TO_NUMBER (wdj.attribute1) NVL (p_order_number, oeh.order_number)
AND TO_NUMBER (wdj.attribute2) NVL (p_line_number, oel.line_number)
AND TO_NUMBER (wdj.attribute3) = NVL (p_line_id, oel.line_id)
AND oeh.open_flag = 'Y'
AND oel.open_flag = 'Y'
ORDER BY oeh.ordered_date, oeh.order_number ASC;
CURSOR c_txn (p_order_number NUMBER, p_line_id NUMBER)
IS
SELECT wdj.attribute1 --order umber
,
wdj.attribute2 --line_number
,
wdj.attribute3 --line_id
,
wdj.wip_entity_id, wdj.lot_number, mmt.organization_id,
mmt.inventory_item_id, mmt.transaction_uom,
mmt.transaction_id, mmt.subinventory_code, mmt.locator_id,
mmt.transaction_quantity
FROM wip_discrete_jobs wdj, mtl_material_transactions mmt
WHERE mmt.transaction_source_id = wdj.wip_entity_id
--and mmt.organization_id = 137
--and mmt.inventory_item_id = 17444
AND mmt.transaction_source_id IS NOT NULL
AND mmt.transaction_id NOT IN (SELECT transaction_id
FROM XX.XX_reservations)
AND TO_NUMBER (wdj.attribute1) = p_order_number
AND TO_NUMBER (wdj.attribute3) = p_line_id
AND mmt.transaction_quantity > 0
ORDER BY mmt.creation_date ASC;
BEGIN
ret_code := 0;
error_buf := NULL;
apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('=', 150, '='));
apps.fnd_file.put_line
(apps.fnd_file.output,
RPAD (' ', 45, ' ')
|| 'Sales Order Reservation for Non Stock Items on Date:'
|| SYSDATE
);
apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('=', 150, '='));
l_current_location := 'Start';
IF validate_parameters (p_order_number, p_line_id, p_line_number) = 'F'
THEN
apps.fnd_file.put_line
(apps.fnd_file.output,
RPAD
('Please confirm link among passed Order number,Line number and line Id',
100,
' '
)
);
ret_code := 1; -- warning
ELSE
l_record : RPAD ('Order Number', 15, ' ')
|| RPAD ('|Line Number', 15, ' ')
|| RPAD ('|Item', 20, ' ')
|| RPAD ('|API Status', 15, ' ')
|| RPAD ('|Message Count', 15, ' ')
|| RPAD ('|Qty Attempted', 15, ' ')
|| RPAD ('|Qty Reserved', 15, ' ')
|| RPAD ('|Message', 100, ' ');
apps.fnd_file.put_line (apps.fnd_file.output, l_record);
l_record := NULL;
l_current_location := 'Item is of type non stock';
apps.fnd_file.put_line (apps.fnd_file.output, RPAD ('*', 150, '*'));
FOR c_wdj_rec IN c_wdj
LOOP
l_current_location : 'Order Number: '
|| c_wdj_rec.attribute1
|| ' and Line Id: '
|| c_wdj_rec.attribute3
|| ' is under consideration';
IF XX_reservation.check_item_type
(TO_NUMBER (c_wdj_rec.attribute3)
) = 'T'
THEN
FOR c_txn_rec IN c_txn (TO_NUMBER (c_wdj_rec.attribute1),
TO_NUMBER (c_wdj_rec.attribute3)
)
LOOP
BEGIN
l_current_location := 'Querying OE_ORDER_LINES_ALL';
BEGIN
SELECT ship_from_org_id, inventory_item_id,
header_id, request_date
INTO l_organization_id, l_inventory_item_id,
l_header_id, l_requirement_date
FROM oe_order_lines_all
WHERE line_id = TO_NUMBER (c_txn_rec.attribute3);
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.output,
'Exception while '
|| l_current_location
);
END;
IF l_inventory_item_id IS NOT NULL
THEN
BEGIN
l_current_location : 'Querying mtl_system_items_b for Primary UOM';
SELECT primary_uom_code, segment1
INTO l_primary_uom_code, l_item
FROM mtl_system_items_b
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.output,
'Exception while '
|| l_current_location
);
END;
END IF;
BEGIN
l_current_location : 'Querying mtl_txn_source_types for mtl_txn_source_type_id';
SELECT transaction_source_type_id
INTO l_demand_source_type_id
FROM mtl_txn_source_types
WHERE transaction_source_type_name = 'Sales order'
AND ROWNUM = 1;
IF l_demand_source_type_id = 2
OR l_demand_source_type_id = 8
THEN
l_demand_source_name := NULL;
ELSE
l_demand_source_name := 'Sales order';
END IF;
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.output,
'Exception while '
|| l_current_location
);
END;
oe_line_util.query_row (TO_NUMBER (c_wdj_rec.attribute3),
l_line_rec
);
l_quantity2_to_reserve := l_line_rec.ordered_quantity2;
oe_schedule_util.load_inv_request
(p_line_rec => l_line_rec,
p_quantity_to_reserve => c_txn_rec.transaction_quantity,
p_quantity2_to_reserve => l_quantity2_to_reserve,
x_reservation_rec => l_rsv_rec
);
--apps.fnd_file.put_line(APPS.FND_FILE.OUTPUT, 'Before API call for Quntity to Reserve = '||l_rsv_rec.reservation_quantity);
inv_reservation_pub.create_reservation
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_true,
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,
x_serial_number => v_serial_number,
p_partial_reservation_flag => fnd_api.g_true,
p_force_reservation_flag => fnd_api.g_false,
p_validation_flag => fnd_api.g_true,
p_over_reservation_flag => 0,
x_quantity_reserved => v_quantity_reserved,
x_reservation_id => v_reservation_id,
p_partial_rsv_exists => FALSE
);
IF v_reservation_id IS NULL
THEN
v_reservation_id := 0;
END IF;
IF v_quantity_reserved IS NULL
THEN
v_quantity_reserved := 0;
END IF;
IF l_msg_count IS NULL
THEN
l_msg_count := 0;
END IF;
IF c_txn_rec.transaction_quantity IS NULL
THEN
c_txn_rec.transaction_quantity := 0;
END IF;
IF l_return_status <> 'S'
THEN
l_record : RPAD (TO_NUMBER (c_txn_rec.attribute1), 15,
' ')
|| RPAD (TO_NUMBER (c_txn_rec.attribute2), 15, ' ')
|| RPAD (l_item, 20, ' ')
|| RPAD (l_return_status, 15, ' ')
|| RPAD (l_msg_count, 15, ' ')
|| RPAD (c_txn_rec.transaction_quantity, 15, ' ')
|| RPAD (v_quantity_reserved, 17, ' ')
|| 'Reservation API Failure.';
apps.fnd_file.put_line (apps.fnd_file.output,
l_record);
FOR i IN 1 .. l_msg_count
LOOP
l_message := fnd_msg_pub.get (i, fnd_api.g_false);
--apps.fnd_file.put_line(APPS.FND_FILE.OUTPUT,SUBSTR (i||':'||l_message, 1, 255));
l_record : LPAD (' ', 112, ' ')
|| i
|| ':'
|| RPAD (l_message, 90, ' ');
apps.fnd_file.put_line (apps.fnd_file.output,
l_record
);
END LOOP;
ELSE
l_record : RPAD (TO_NUMBER (c_txn_rec.attribute1), 15,
' ')
|| RPAD (TO_NUMBER (c_txn_rec.attribute2), 15, ' ')
|| RPAD (l_item, 20, ' ')
|| RPAD (l_return_status, 15, ' ')
|| RPAD (l_msg_count, 14, ' ')
|| RPAD (c_txn_rec.transaction_quantity, 15, ' ')
|| RPAD (v_quantity_reserved, 17, ' ')
|| 'Success';
apps.fnd_file.put_line (apps.fnd_file.output,
l_record);
INSERT INTO XX.XX_reservations
(wip_entity_id,
transaction_id,
order_number,
line_id, processed,
creation_date, created_by,
last_updated_by, last_update_date
)
VALUES (c_txn_rec.wip_entity_id,
c_txn_rec.transaction_id,
TO_NUMBER (c_txn_rec.attribute1),
TO_NUMBER (c_txn_rec.attribute3), 'Y',
SYSDATE, fnd_global.user_id,
fnd_global.user_id, SYSDATE
);
COMMIT;
END IF;
--l_record:=rpad('-',150,'-');
--apps.fnd_file.put_line(APPS.FND_FILE.OUTPUT, l_record);
EXCEPTION
WHEN OTHERS
THEN
apps.fnd_file.put_line (apps.fnd_file.output,
'Exception while '
|| l_current_location
);
END;
END LOOP;
--apps.fnd_file.put_line(APPS.FND_FILE.OUTPUT, rpad('-',150,'-'));
END IF;
END LOOP;
IF l_record IS NULL
THEN
l_record : 'No qualifying transaction records found in WIP Completion transaction table';
apps.fnd_file.put_line (apps.fnd_file.output,
RPAD (' ', 45, ' ') || l_record
);
END IF;
l_record := RPAD ('=', 150, '=');
apps.fnd_file.put_line (apps.fnd_file.output, l_record);
apps.fnd_file.put_line
(apps.fnd_file.output,
RPAD (' ', 45, ' ')
|| 'End of the Sales Order Reservation for Non Stock Items'
);
l_record := RPAD ('=', 150, '=');
apps.fnd_file.put_line (apps.fnd_file.output, l_record);
END IF; -- of Validate_Parameters
EXCEPTION
WHEN OTHERS
THEN
NULL;
END create_reservation;
