[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(sum(requested_quantity),0)
INTO l_requested_qty
FROM wsh_delivery_details
WHERE source_line_id = p_demand_source_line_id
AND delivery_detail_id = p_demand_source_line_detail;
SELECT ordered_quantity
INTO l_requested_qty
FROM oe_order_lines_all
WHERE line_id = p_demand_source_line_id
AND nvl(project_id, -99) = nvl(p_project_id, -99)
AND nvl(task_id, -99) = nvl(p_task_id, -99);
SELECT inventory_item_id, ordered_quantity, order_quantity_uom, ship_from_org_id,
ship_tolerance_above
INTO l_line_rec_inventory_item_id,
l_line_rec_ordered_quantity,
l_line_rec_order_quantity_uom,
l_line_rec_org_id,
l_ship_tolerance_above
FROM oe_order_lines_all
WHERE line_id = p_demand_line_id;
select primary_uom_code
into l_primary_uom_code
from mtl_system_items
where organization_id = l_line_rec_org_id
and inventory_item_id = l_line_rec_inventory_item_id;
SELECT primary_uom_code, secondary_uom_code,tracking_quantity_ind
INTO px_rsv_rec.primary_uom_code, px_rsv_rec.secondary_uom_code,l_tracking_quantity_ind
FROM mtl_system_items
WHERE inventory_item_id = px_rsv_rec.inventory_item_id
AND organization_id = px_rsv_rec.organization_id;
SELECT lot_control_code, lot_divisible_flag INTO
l_lot_control_code, l_lot_divisible_flag FROM mtl_system_items
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id;
SELECT we.entity_type, wdj.maintenance_object_source INTO
l_wip_entity_id, l_maintenance_object_source FROM wip_entities we,
wip_discrete_jobs wdj WHERE we.wip_entity_id = p_source_header_id
AND we.wip_entity_id = wdj.wip_entity_id(+);
PROCEDURE update_serial_rsv_quantity(
x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_reservation_id IN NUMBER
, p_update_serial_qty IN NUMBER)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_serial_rsv_quantity';
l_update_count NUMBER := 0;
debug_print('In update_serial_rsv_quantity');
debug_print('update_serial_qty = ' || p_update_serial_qty);
update mtl_reservations
set serial_reservation_quantity = serial_reservation_quantity + p_update_serial_qty
where reservation_id = p_reservation_id;
l_update_count := SQL%ROWCOUNT;
debug_print('Number of rows update: ' || l_update_count);
END update_serial_rsv_quantity;
SELECT reservation_id, serial_number
INTO x_serial_number_tbl(l_index).reservation_id, x_serial_number_tbl(l_index).serial_number
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number_tbl(i).serial_number
AND inventory_item_id = p_serial_number_tbl(i).inventory_item_id
AND reservation_id is not null;
SELECT reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
FROM mtl_reservations
WHERE reservation_id = p_reservation_id;
SELECT reservation_id, serial_number
INTO x_serial_number_tbl(l_index).reservation_id, x_serial_number_tbl(l_index).serial_number
FROM mtl_serial_numbers
WHERE serial_number = p_serial_number_tbl(i).serial_number
AND inventory_item_id = p_serial_number_tbl(i).inventory_item_id
AND reservation_id is not null;
ELSIF p_action = 'DELETE' THEN
IF (l_debug = 1) THEN
debug_print('DELETE');
inv_quantity_tree_pvt.update_quantities(
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_tree_id => p_tree_id
, p_revision => p_revision
, p_lot_number => p_lot_number
, p_subinventory_code => p_subinventory_code
, p_locator_id => p_locator_id
, p_primary_quantity => l_net_qty
, p_secondary_quantity => l_secondary_net_qty -- INVCONV
, p_quantity_type => l_quantity_type
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh -- INVCONV
, x_srqoh => l_srqoh -- INVCONV
, x_sqr => l_sqr -- INVCONV
, x_sqs => l_sqs -- INVCONV
, x_satt => l_satt -- INVCONV
, x_satr => l_satr -- INVCONV
, p_lpn_id => p_lpn_id
);
PROCEDURE modify_tree_for_update_xfer(
x_return_status OUT NOCOPY VARCHAR2
, x_quantity_reserved OUT NOCOPY NUMBER
, x_secondary_quantity_reserved OUT NOCOPY NUMBER --INVCONV
, p_from_tree_id IN NUMBER
, p_from_supply_source_type_id IN NUMBER
, p_from_revision IN VARCHAR2
, p_from_lot_number IN VARCHAR2
, p_from_subinventory_code IN VARCHAR2
, p_from_locator_id IN NUMBER
, p_from_lpn_id IN NUMBER
, p_from_primary_rsv_quantity IN NUMBER
, p_from_second_rsv_quantity IN NUMBER
, p_from_detailed_quantity IN NUMBER
, p_from_sec_detailed_quantity IN NUMBER
, p_to_tree_id IN NUMBER
, p_to_supply_source_type_id IN NUMBER
, p_to_revision IN VARCHAR2
, p_to_lot_number IN VARCHAR2
, p_to_subinventory_code IN VARCHAR2
, p_to_locator_id IN NUMBER
, p_to_lpn_id IN NUMBER
, p_to_primary_rsv_quantity IN NUMBER
, p_to_second_rsv_quantity IN NUMBER
, p_to_detailed_quantity IN NUMBER
, p_to_second_detailed_quantity IN NUMBER
, p_to_revision_control IN BOOLEAN
, p_to_lot_control IN BOOLEAN
, p_action IN VARCHAR2
, p_lot_divisible_flag IN VARCHAR2 -- INVCONV
, p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
, p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
) IS
l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
IF p_action = 'UPDATE' THEN
l_net_qty1 := -p_from_primary_rsv_quantity + NVL(p_from_detailed_quantity, 0);
inv_quantity_tree_pvt.update_quantities(
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_tree_id => p_from_tree_id
, p_revision => p_from_revision
, p_lot_number => p_from_lot_number
, p_subinventory_code => p_from_subinventory_code
, p_locator_id => p_from_locator_id
, p_primary_quantity => l_net_qty1
, p_secondary_quantity => l_secondary_net_qty1
, p_quantity_type => inv_quantity_tree_pvt.g_qr_other_demand
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_from_lpn_id
);
inv_quantity_tree_pvt.update_quantities(
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_tree_id => p_to_tree_id
, p_revision => l_tmp_revision
, p_lot_number => l_tmp_lot_number
, p_subinventory_code => p_from_subinventory_code
, p_locator_id => p_from_locator_id
, p_primary_quantity => l_net_qty1
, p_secondary_quantity => l_secondary_net_qty1
, p_quantity_type => inv_quantity_tree_pvt.g_qr_other_demand
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_from_lpn_id
);
inv_quantity_tree_pvt.update_quantities(
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_tree_id => p_from_tree_id
, p_revision => p_from_revision
, p_lot_number => p_from_lot_number
, p_subinventory_code => p_from_subinventory_code
, p_locator_id => p_from_locator_id
, p_primary_quantity => -l_net_qty1
, p_secondary_quantity => -l_secondary_net_qty1
, p_quantity_type => inv_quantity_tree_pvt.g_qr_same_demand
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_from_lpn_id
);
IF p_action='UPDATE' AND p_from_supply_source_type_id = inv_reservation_global.g_source_type_inv then
IF (l_debug = 1) THEN
debug_print('Validate Qty :Action Update');
AND p_action='UPDATE'
AND p_check_availability = fnd_api.g_true )THEN
--rollback quantity tree changes .
IF l_modify_tree1 THEN
-- INVCONV Incorporate secondaries
inv_quantity_tree_pvt.update_quantities(
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_tree_id => p_from_tree_id
, p_revision => p_from_revision
, p_lot_number => p_from_lot_number
, p_subinventory_code => p_from_subinventory_code
, p_locator_id => p_from_locator_id
, p_primary_quantity => -l_net_qty1
, p_secondary_quantity => -l_secondary_net_qty1
, p_quantity_type => inv_quantity_tree_pvt.g_qr_same_demand
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_from_lpn_id
);
inv_quantity_tree_pvt.update_quantities(
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_tree_id => p_from_tree_id
, p_revision => p_from_revision
, p_lot_number => p_from_lot_number
, p_subinventory_code => p_from_subinventory_code
, p_locator_id => p_from_locator_id
, p_primary_quantity => l_net_qty2
, p_secondary_quantity => l_secondary_net_qty2
, p_quantity_type => inv_quantity_tree_pvt.g_qr_other_demand
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_from_lpn_id
);
inv_quantity_tree_pvt.update_quantities(
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_tree_id => p_to_tree_id
, p_revision => p_to_revision
, p_lot_number => p_to_lot_number
, p_subinventory_code => p_to_subinventory_code
, p_locator_id => p_to_locator_id
, p_primary_quantity => l_net_qty2
, p_secondary_quantity => l_secondary_net_qty2
, p_quantity_type => inv_quantity_tree_pvt.g_qr_same_demand
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_to_lpn_id
);
inv_quantity_tree_pvt.update_quantities(
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_tree_id => p_from_tree_id
, p_revision => p_from_revision
, p_lot_number => p_from_lot_number
, p_subinventory_code => p_from_subinventory_code
, p_locator_id => p_from_locator_id
, p_primary_quantity => -l_net_qty1
, p_secondary_quantity => -l_secondary_net_qty1
, p_quantity_type => inv_quantity_tree_pvt.g_qr_same_demand
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_from_lpn_id
);
inv_quantity_tree_pvt.update_quantities(
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_tree_id => p_to_tree_id
, p_revision => l_tmp_revision
, p_lot_number => l_tmp_lot_number
, p_subinventory_code => p_from_subinventory_code
, p_locator_id => p_from_locator_id
, p_primary_quantity => -l_net_qty1
, p_secondary_quantity => -l_secondary_net_qty1
, p_quantity_type => inv_quantity_tree_pvt.g_qr_other_demand
, x_qoh => l_qoh
, x_rqoh => l_rqoh
, x_qr => l_qr
, x_qs => l_qs
, x_att => l_att
, x_atr => l_atr
, x_sqoh => l_sqoh
, x_srqoh => l_srqoh
, x_sqr => l_sqr
, x_sqs => l_sqs
, x_satt => l_satt
, x_satr => l_satr
, p_lpn_id => p_from_lpn_id
);
fnd_msg_pub.add_exc_msg(g_pkg_name, 'modify_tree_for_update_xfer');
END modify_tree_for_update_xfer;
l_update BOOLEAN := FALSE;
CURSOR c_res_id_update IS
SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes. Adding new
-- columns to query reservations. id passed for update}}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE reservation_id = p_query_input.reservation_id
FOR UPDATE --NOWAIT
ORDER BY NVL(revision, ' '), NVL(lot_number, ' '), NVL(subinventory_code, ' '), NVL(locator_id, 0);
SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes. Adding new
-- columns to query reservations. id passed no update}}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE reservation_id = p_query_input.reservation_id
ORDER BY NVL(revision, ' '), NVL(lot_number, ' '), NVL(subinventory_code, ' '), NVL(locator_id, 0);
CURSOR c_demand_update IS
SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes. Adding new
-- columns to query reservations. demand passed }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE demand_source_line_id = p_query_input.demand_source_line_id
AND (p_query_input.lpn_id = l_miss_num
OR p_query_input.lpn_id IS NULL
AND lpn_id IS NULL
OR p_query_input.lpn_id = lpn_id
)
AND
(p_query_input.organization_id = l_miss_num
OR organization_id = p_query_input.organization_id
)
AND (p_query_input.inventory_item_id = l_miss_num
OR inventory_item_id = p_query_input.inventory_item_id
)
AND (p_query_input.requirement_date = l_miss_date
OR requirement_date = p_query_input.requirement_date
)
AND (p_query_input.demand_source_type_id = l_miss_num
OR demand_source_type_id = p_query_input.demand_source_type_id
)
AND (p_query_input.demand_source_header_id = l_miss_num
OR p_query_input.demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR p_query_input.demand_source_header_id = demand_source_header_id
)
AND (p_query_input.demand_source_name = l_miss_char
OR p_query_input.demand_source_name IS NULL
AND demand_source_name IS NULL
OR p_query_input.demand_source_name = demand_source_name
)
AND (p_query_input.demand_source_delivery = l_miss_num
OR p_query_input.demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR p_query_input.demand_source_delivery = demand_source_delivery
)
AND (p_query_input.primary_uom_code = l_miss_char
OR p_query_input.primary_uom_code IS NULL
AND primary_uom_code IS NULL
OR p_query_input.primary_uom_code = primary_uom_code
)
AND (p_query_input.primary_uom_id = l_miss_num
OR p_query_input.primary_uom_id IS NULL
AND primary_uom_id IS NULL
OR p_query_input.primary_uom_id = primary_uom_id
)
-- INVCONV BEGIN
AND (p_query_input.secondary_uom_code = l_miss_char
OR p_query_input.secondary_uom_code IS NULL
AND secondary_uom_code IS NULL
OR p_query_input.secondary_uom_code = secondary_uom_code
)
AND (p_query_input.secondary_uom_id = l_miss_num
OR p_query_input.secondary_uom_id IS NULL
AND secondary_uom_id IS NULL
OR p_query_input.secondary_uom_id = secondary_uom_id
)
-- INVCONV END
AND (p_query_input.reservation_uom_code = l_miss_char
OR p_query_input.reservation_uom_code IS NULL
AND reservation_uom_code IS NULL
OR p_query_input.reservation_uom_code = reservation_uom_code
)
AND (p_query_input.reservation_uom_id = l_miss_num
OR p_query_input.reservation_uom_id IS NULL
AND reservation_uom_id IS NULL
OR p_query_input.reservation_uom_id = reservation_uom_id
)
AND (p_query_input.autodetail_group_id = l_miss_num
OR p_query_input.autodetail_group_id IS NULL
AND autodetail_group_id IS NULL
OR p_query_input.autodetail_group_id = autodetail_group_id
)
AND (p_query_input.external_source_code = l_miss_char
OR p_query_input.external_source_code IS NULL
AND external_source_code IS NULL
OR p_query_input.external_source_code = external_source_code
)
AND (p_query_input.external_source_line_id = l_miss_num
OR p_query_input.external_source_line_id IS NULL
AND external_source_line_id IS NULL
OR p_query_input.external_source_line_id = external_source_line_id
)
AND (p_query_input.supply_source_type_id = l_miss_num
OR supply_source_type_id = p_query_input.supply_source_type_id
)
AND (p_query_input.supply_source_header_id = l_miss_num
OR p_query_input.supply_source_header_id IS NULL
AND supply_source_header_id IS NULL
OR p_query_input.supply_source_header_id = supply_source_header_id
)
AND (p_query_input.supply_source_line_id = l_miss_num
OR p_query_input.supply_source_line_id IS NULL
AND supply_source_line_id IS NULL
OR p_query_input.supply_source_line_id = supply_source_line_id
)
AND (p_query_input.supply_source_name = l_miss_char
OR p_query_input.supply_source_name IS NULL
AND supply_source_name IS NULL
OR p_query_input.supply_source_name = supply_source_name
)
AND (p_query_input.supply_source_line_detail = l_miss_num
OR p_query_input.supply_source_line_detail IS NULL
AND supply_source_line_detail IS NULL
OR p_query_input.supply_source_line_detail = supply_source_line_detail
)
AND (p_query_input.revision = l_miss_char
OR p_query_input.revision IS NULL
AND revision IS NULL
OR p_query_input.revision = revision
)
AND (p_query_input.subinventory_code = l_miss_char
OR p_query_input.subinventory_code IS NULL
AND subinventory_code IS NULL
OR p_query_input.subinventory_code = subinventory_code
)
AND (p_query_input.subinventory_id = l_miss_num
OR p_query_input.subinventory_id IS NULL
AND subinventory_id IS NULL
OR p_query_input.subinventory_id = subinventory_id
)
AND (p_query_input.locator_id = l_miss_num
OR p_query_input.locator_id IS NULL
AND locator_id IS NULL
OR p_query_input.locator_id = locator_id
)
AND (p_query_input.lot_number = l_miss_char
OR p_query_input.lot_number IS NULL
AND lot_number IS NULL
OR p_query_input.lot_number = lot_number
)
AND (p_query_input.lot_number_id = l_miss_num
OR p_query_input.lot_number_id IS NULL
AND lot_number_id IS NULL
OR p_query_input.lot_number_id = lot_number_id
)
AND (p_query_input.ship_ready_flag = l_miss_num
OR (p_query_input.ship_ready_flag IS NULL OR p_query_input.ship_ready_flag = 2)
AND (ship_ready_flag IS NULL OR ship_ready_flag = 2)
OR p_query_input.ship_ready_flag = ship_ready_flag
)
AND (p_query_input.staged_flag = l_miss_char
OR (p_query_input.staged_flag IS NULL OR p_query_input.staged_flag = 'N')
AND (staged_flag IS NULL OR staged_flag = 'N')
OR p_query_input.staged_flag = staged_flag
)
AND (p_query_input.attribute_category = l_miss_char
OR p_query_input.attribute_category IS NULL
AND attribute_category IS NULL
OR p_query_input.attribute_category = attribute_category
)
AND (p_query_input.attribute1 = l_miss_char
OR p_query_input.attribute1 IS NULL
AND attribute1 IS NULL
OR p_query_input.attribute1 = attribute1
)
AND (p_query_input.attribute2 = l_miss_char
OR p_query_input.attribute2 IS NULL
AND attribute2 IS NULL
OR p_query_input.attribute2 = attribute2
)
AND (p_query_input.attribute3 = l_miss_char
OR p_query_input.attribute3 IS NULL
AND attribute3 IS NULL
OR p_query_input.attribute3 = attribute3
)
AND (p_query_input.attribute4 = l_miss_char
OR p_query_input.attribute4 IS NULL
AND attribute4 IS NULL
OR p_query_input.attribute4 = attribute4
)
AND (p_query_input.attribute5 = l_miss_char
OR p_query_input.attribute5 IS NULL
AND attribute5 IS NULL
OR p_query_input.attribute5 = attribute5
)
AND (p_query_input.attribute6 = l_miss_char
OR p_query_input.attribute6 IS NULL
AND attribute6 IS NULL
OR p_query_input.attribute6 = attribute6
)
AND (p_query_input.attribute7 = l_miss_char
OR p_query_input.attribute7 IS NULL
AND attribute7 IS NULL
OR p_query_input.attribute7 = attribute7
)
AND (p_query_input.attribute8 = l_miss_char
OR p_query_input.attribute8 IS NULL
AND attribute8 IS NULL
OR p_query_input.attribute8 = attribute8
)
AND (p_query_input.attribute9 = l_miss_char
OR p_query_input.attribute9 IS NULL
AND attribute9 IS NULL
OR p_query_input.attribute9 = attribute9
)
AND (p_query_input.attribute10 = l_miss_char
OR p_query_input.attribute10 IS NULL
AND attribute10 IS NULL
OR p_query_input.attribute10 = attribute10
)
AND (p_query_input.attribute11 = l_miss_char
OR p_query_input.attribute11 IS NULL
AND attribute11 IS NULL
OR p_query_input.attribute11 = attribute11
)
AND (p_query_input.attribute12 = l_miss_char
OR p_query_input.attribute12 IS NULL
AND attribute12 IS NULL
OR p_query_input.attribute12 = attribute12
)
AND (p_query_input.attribute13 = l_miss_char
OR p_query_input.attribute13 IS NULL
AND attribute13 IS NULL
OR p_query_input.attribute13 = attribute13
)
AND (p_query_input.attribute14 = l_miss_char
OR p_query_input.attribute14 IS NULL
AND attribute14 IS NULL
OR p_query_input.attribute14 = attribute14
)
AND (p_query_input.attribute15 = l_miss_char
OR p_query_input.attribute15 IS NULL
AND attribute15 IS NULL
OR p_query_input.attribute15 = attribute15
)
/**** {{ R12 Enhanced reservations code changes. query reservation
-- where clause}}****/
AND (p_query_input.crossdock_flag = l_miss_char
OR p_query_input.crossdock_flag IS NULL
AND crossdock_flag IS NULL
OR p_query_input.crossdock_flag = crossdock_flag
)
AND (p_query_input.crossdock_criteria_id = l_miss_num
OR p_query_input.crossdock_criteria_id IS NULL
AND crossdock_criteria_id IS NULL
OR p_query_input.crossdock_criteria_id = crossdock_criteria_id
)
AND (p_query_input.demand_source_line_detail = l_miss_num
OR p_query_input.demand_source_line_detail IS NULL
AND demand_source_line_detail IS NULL
OR p_query_input.demand_source_line_detail = demand_source_line_detail
)
AND (p_query_input.supply_receipt_date = l_miss_date
OR p_query_input.supply_receipt_date IS NULL
AND supply_receipt_date IS NULL
OR p_query_input.supply_receipt_date = supply_receipt_date
)
AND (p_query_input.demand_ship_date = l_miss_date
OR p_query_input.demand_ship_date IS NULL
AND demand_ship_date IS NULL
OR p_query_input.demand_ship_date = demand_ship_date
)
AND (p_query_input.project_id = l_miss_num
OR p_query_input.project_id IS NULL
AND project_id IS NULL
OR p_query_input.project_id = project_id
)
AND (p_query_input.task_id = l_miss_num
OR p_query_input.task_id IS NULL
AND task_id IS NULL
OR p_query_input.task_id = task_id
)
/***** End R12 ***/
FOR UPDATE NOWAIT
ORDER BY NVL(revision, ' '), NVL(lot_number, ' '), NVL(subinventory_code, ' '), NVL(locator_id, 0);
SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes. Adding new
-- columns to query reservations. demand passed }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE demand_source_line_id = p_query_input.demand_source_line_id
AND (p_query_input.requirement_date = l_miss_date
OR p_query_input.requirement_date IS NULL
AND requirement_date IS NULL
OR p_query_input.requirement_date = requirement_date
)
AND (p_query_input.organization_id = l_miss_num
OR p_query_input.organization_id IS NULL
AND organization_id IS NULL
OR p_query_input.organization_id = organization_id
)
AND (p_query_input.inventory_item_id = l_miss_num
OR p_query_input.inventory_item_id IS NULL
AND inventory_item_id IS NULL
OR p_query_input.inventory_item_id = inventory_item_id
)
AND (p_query_input.demand_source_type_id = l_miss_num
OR p_query_input.demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR p_query_input.demand_source_type_id = demand_source_type_id
)
AND (p_query_input.demand_source_header_id = l_miss_num
OR p_query_input.demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR p_query_input.demand_source_header_id = demand_source_header_id
)
AND (p_query_input.demand_source_name = l_miss_char
OR p_query_input.demand_source_name IS NULL
AND demand_source_name IS NULL
OR p_query_input.demand_source_name = demand_source_name
)
AND (p_query_input.demand_source_delivery = l_miss_num
OR p_query_input.demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR p_query_input.demand_source_delivery = demand_source_delivery
)
AND (p_query_input.primary_uom_code = l_miss_char
OR p_query_input.primary_uom_code IS NULL
AND primary_uom_code IS NULL
OR p_query_input.primary_uom_code = primary_uom_code
)
AND (p_query_input.primary_uom_id = l_miss_num
OR p_query_input.primary_uom_id IS NULL
AND primary_uom_id IS NULL
OR p_query_input.primary_uom_id = primary_uom_id
)
-- INVCONV BEGIN
AND (p_query_input.secondary_uom_code = l_miss_char
OR p_query_input.secondary_uom_code IS NULL
AND secondary_uom_code IS NULL
OR p_query_input.secondary_uom_code = secondary_uom_code
)
AND (p_query_input.secondary_uom_id = l_miss_num
OR p_query_input.secondary_uom_id IS NULL
AND secondary_uom_id IS NULL
OR p_query_input.secondary_uom_id = secondary_uom_id
)
-- INVCONV END
AND (p_query_input.reservation_uom_code = l_miss_char
OR p_query_input.reservation_uom_code IS NULL
AND reservation_uom_code IS NULL
OR p_query_input.reservation_uom_code = reservation_uom_code
)
AND (p_query_input.reservation_uom_id = l_miss_num
OR p_query_input.reservation_uom_id IS NULL
AND reservation_uom_id IS NULL
OR p_query_input.reservation_uom_id = reservation_uom_id
)
AND (p_query_input.autodetail_group_id = l_miss_num
OR p_query_input.autodetail_group_id IS NULL
AND autodetail_group_id IS NULL
OR p_query_input.autodetail_group_id = autodetail_group_id
)
AND (p_query_input.external_source_code = l_miss_char
OR p_query_input.external_source_code IS NULL
AND external_source_code IS NULL
OR p_query_input.external_source_code = external_source_code
)
AND (p_query_input.external_source_line_id = l_miss_num
OR p_query_input.external_source_line_id IS NULL
AND external_source_line_id IS NULL
OR p_query_input.external_source_line_id = external_source_line_id
)
AND (p_query_input.supply_source_type_id = l_miss_num
OR p_query_input.supply_source_type_id IS NULL
AND supply_source_type_id IS NULL
OR p_query_input.supply_source_type_id = supply_source_type_id
)
AND (p_query_input.supply_source_header_id = l_miss_num
OR p_query_input.supply_source_header_id IS NULL
AND supply_source_header_id IS NULL
OR p_query_input.supply_source_header_id = supply_source_header_id
)
AND (p_query_input.supply_source_line_id = l_miss_num
OR p_query_input.supply_source_line_id IS NULL
AND supply_source_line_id IS NULL
OR p_query_input.supply_source_line_id = supply_source_line_id
)
AND (p_query_input.supply_source_name = l_miss_char
OR p_query_input.supply_source_name IS NULL
AND supply_source_name IS NULL
OR p_query_input.supply_source_name = supply_source_name
)
AND (p_query_input.supply_source_line_detail = l_miss_num
OR p_query_input.supply_source_line_detail IS NULL
AND supply_source_line_detail IS NULL
OR p_query_input.supply_source_line_detail = supply_source_line_detail
)
AND (p_query_input.revision = l_miss_char
OR p_query_input.revision IS NULL
AND revision IS NULL
OR p_query_input.revision = revision
)
AND (p_query_input.subinventory_code = l_miss_char
OR p_query_input.subinventory_code IS NULL
AND subinventory_code IS NULL
OR p_query_input.subinventory_code = subinventory_code
)
AND (p_query_input.subinventory_id = l_miss_num
OR p_query_input.subinventory_id IS NULL
AND subinventory_id IS NULL
OR p_query_input.subinventory_id = subinventory_id
)
AND (p_query_input.locator_id = l_miss_num
OR p_query_input.locator_id IS NULL
AND locator_id IS NULL
OR p_query_input.locator_id = locator_id
)
AND (p_query_input.lot_number = l_miss_char
OR p_query_input.lot_number IS NULL
AND lot_number IS NULL
OR p_query_input.lot_number = lot_number
)
AND (p_query_input.lot_number_id = l_miss_num
OR p_query_input.lot_number_id IS NULL
AND lot_number_id IS NULL
OR p_query_input.lot_number_id = lot_number_id
)
AND (p_query_input.lpn_id = l_miss_num
OR p_query_input.lpn_id IS NULL
AND lpn_id IS NULL
OR p_query_input.lpn_id = lpn_id
)
AND (p_query_input.ship_ready_flag = l_miss_num
OR (p_query_input.ship_ready_flag IS NULL OR p_query_input.ship_ready_flag = 2)
AND (ship_ready_flag IS NULL OR ship_ready_flag = 2)
OR p_query_input.ship_ready_flag = ship_ready_flag
)
AND (p_query_input.staged_flag = l_miss_char
OR (p_query_input.staged_flag IS NULL OR p_query_input.staged_flag = 'N')
AND (staged_flag IS NULL OR staged_flag = 'N')
OR p_query_input.staged_flag = staged_flag
)
AND (p_query_input.attribute_category = l_miss_char
OR p_query_input.attribute_category IS NULL
AND attribute_category IS NULL
OR p_query_input.attribute_category = attribute_category
)
AND (p_query_input.attribute1 = l_miss_char
OR p_query_input.attribute1 IS NULL
AND attribute1 IS NULL
OR p_query_input.attribute1 = attribute1
)
AND (p_query_input.attribute2 = l_miss_char
OR p_query_input.attribute2 IS NULL
AND attribute2 IS NULL
OR p_query_input.attribute2 = attribute2
)
AND (p_query_input.attribute3 = l_miss_char
OR p_query_input.attribute3 IS NULL
AND attribute3 IS NULL
OR p_query_input.attribute3 = attribute3
)
AND (p_query_input.attribute4 = l_miss_char
OR p_query_input.attribute4 IS NULL
AND attribute4 IS NULL
OR p_query_input.attribute4 = attribute4
)
AND (p_query_input.attribute5 = l_miss_char
OR p_query_input.attribute5 IS NULL
AND attribute5 IS NULL
OR p_query_input.attribute5 = attribute5
)
AND (p_query_input.attribute6 = l_miss_char
OR p_query_input.attribute6 IS NULL
AND attribute6 IS NULL
OR p_query_input.attribute6 = attribute6
)
AND (p_query_input.attribute7 = l_miss_char
OR p_query_input.attribute7 IS NULL
AND attribute7 IS NULL
OR p_query_input.attribute7 = attribute7
)
AND (p_query_input.attribute8 = l_miss_char
OR p_query_input.attribute8 IS NULL
AND attribute8 IS NULL
OR p_query_input.attribute8 = attribute8
)
AND (p_query_input.attribute9 = l_miss_char
OR p_query_input.attribute9 IS NULL
AND attribute9 IS NULL
OR p_query_input.attribute9 = attribute9
)
AND (p_query_input.attribute10 = l_miss_char
OR p_query_input.attribute10 IS NULL
AND attribute10 IS NULL
OR p_query_input.attribute10 = attribute10
)
AND (p_query_input.attribute11 = l_miss_char
OR p_query_input.attribute11 IS NULL
AND attribute11 IS NULL
OR p_query_input.attribute11 = attribute11
)
AND (p_query_input.attribute12 = l_miss_char
OR p_query_input.attribute12 IS NULL
AND attribute12 IS NULL
OR p_query_input.attribute12 = attribute12
)
AND (p_query_input.attribute13 = l_miss_char
OR p_query_input.attribute13 IS NULL
AND attribute13 IS NULL
OR p_query_input.attribute13 = attribute13
)
AND (p_query_input.attribute14 = l_miss_char
OR p_query_input.attribute14 IS NULL
AND attribute14 IS NULL
OR p_query_input.attribute14 = attribute14
)
AND (p_query_input.attribute15 = l_miss_char
OR p_query_input.attribute15 IS NULL
AND attribute15 IS NULL
OR p_query_input.attribute15 = attribute15
)
/**** {{ R12 Enhanced reservations code changes }}****/
AND (p_query_input.crossdock_flag = l_miss_char
OR p_query_input.crossdock_flag IS NULL
AND crossdock_flag IS NULL
OR p_query_input.crossdock_flag = crossdock_flag
)
AND (p_query_input.crossdock_criteria_id = l_miss_num
OR p_query_input.crossdock_criteria_id IS NULL
AND crossdock_criteria_id IS NULL
OR p_query_input.crossdock_criteria_id = crossdock_criteria_id
)
AND (p_query_input.demand_source_line_detail = l_miss_num
OR p_query_input.demand_source_line_detail IS NULL
AND demand_source_line_detail IS NULL
OR p_query_input.demand_source_line_detail = demand_source_line_detail
)
AND (p_query_input.supply_receipt_date = l_miss_date
OR p_query_input.supply_receipt_date IS NULL
AND supply_receipt_date IS NULL
OR p_query_input.supply_receipt_date = supply_receipt_date
)
AND (p_query_input.demand_ship_date = l_miss_date
OR p_query_input.demand_ship_date IS NULL
AND demand_ship_date IS NULL
OR p_query_input.demand_ship_date = demand_ship_date
)
AND (p_query_input.project_id = l_miss_num
OR p_query_input.project_id IS NULL
AND project_id IS NULL
OR p_query_input.project_id = project_id
)
AND (p_query_input.task_id = l_miss_num
OR p_query_input.task_id IS NULL
AND task_id IS NULL
OR p_query_input.task_id = task_id
)
/***** End R12 ***/
ORDER BY NVL(revision, ' '), NVL(lot_number, ' '), NVL(subinventory_code, ' '), NVL(locator_id, 0);
l_lock_stmt := ' FOR UPDATE NOWAIT';
l_update := TRUE;
IF l_update then
debug_print('l_update is true ');
debug_print('l_update is false ');
IF l_update THEN
IF (l_debug = 1) then
debug_print(' Open res cursor for update');
OPEN c_res_id_update;
debug_print(' Open res cursor for NO update');
OPEN l_cursor_ref FOR 'SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes. Adding new
-- columns for query reservations }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE
:reservation_id = reservation_id '
|| l_lock_stmt
|| l_sort_stmt USING p_query_input.reservation_id;
IF l_update THEN
l_demand_cursor := TRUE;
debug_print(' Open demand cursor for update');
OPEN c_demand_update;
debug_print(' Open demand cursor for NO update');
OPEN l_cursor_ref FOR 'SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes. Adding new
-- columns for query reservations }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE
demand_source_line_id = :demand_source_line_id
AND
(:requirement_date = :l_miss_date
OR :requirement_date IS NULL
AND requirement_date IS NULL
OR :requirement_date
= requirement_date
)
AND
(:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id
)
AND
(:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULL
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id
)
AND
(:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id
= demand_source_type_id
)
AND
(:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id
= demand_source_header_id
)
AND
(:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name
)
AND
(:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery
)
AND
(:primary_uom_code = :l_miss_char
OR :primary_uom_code IS NULL
AND primary_uom_code IS NULL
OR :primary_uom_code = primary_uom_code
)
AND
(:primary_uom_id = :l_miss_num
OR :primary_uom_id IS NULL
AND primary_uom_id IS NULL
OR :primary_uom_id = primary_uom_id
)
-- INVCONV BEGIN
AND
(:secondary_uom_code = :l_miss_char
OR :secondary_uom_code IS NULL
AND secondary_uom_code IS NULL
OR :secondary_uom_code = secondary_uom_code
)
AND
(:secondary_uom_id = :l_miss_num
OR :secondary_uom_id IS NULL
AND secondary_uom_id IS NULL
OR :secondary_uom_id = secondary_uom_id
)
-- INVCONV END
AND
(:reservation_uom_code = :l_miss_char
OR :reservation_uom_code IS NULL
AND reservation_uom_code IS NULL
OR :reservation_uom_code = reservation_uom_code
)
AND
(:reservation_uom_id = :l_miss_num
OR :reservation_uom_id IS NULL
AND reservation_uom_id IS NULL
OR :reservation_uom_id = reservation_uom_id
)
AND
(:autodetail_group_id = :l_miss_num
OR :autodetail_group_id IS NULL
AND autodetail_group_id IS NULL
OR :autodetail_group_id = autodetail_group_id
)
AND
(:external_source_code = :l_miss_char
OR :external_source_code IS NULL
AND external_source_code IS NULL
OR :external_source_code = external_source_code
)
AND
(:external_source_line_id = :l_miss_num
OR :external_source_line_id IS NULL
AND external_source_line_id IS NULL
OR :external_source_line_id = external_source_line_id
)
AND
(:supply_source_type_id = :l_miss_num
OR :supply_source_type_id IS NULL
AND supply_source_type_id IS NULL
OR :supply_source_type_id = supply_source_type_id
)
AND
(:supply_source_header_id = :l_miss_num
OR :supply_source_header_id IS NULL
AND supply_source_header_id IS NULL
OR :supply_source_header_id
= supply_source_header_id
)
AND
(:supply_source_line_id = :l_miss_num
OR :supply_source_line_id IS NULL
AND supply_source_line_id IS NULL
OR :supply_source_line_id = supply_source_line_id
)
AND
(:supply_source_name = :l_miss_char
OR :supply_source_name IS NULL
AND supply_source_name IS NULL
OR :supply_source_name = supply_source_name
)
AND
(:supply_source_line_detail = :l_miss_num
OR :supply_source_line_detail IS NULL
AND supply_source_line_detail IS NULL
OR :supply_source_line_detail
= supply_source_line_detail
)
AND
(:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision
)
AND
(:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code
)
AND
(:subinventory_id = :l_miss_num
OR :subinventory_id IS NULL
AND subinventory_id IS NULL
OR :subinventory_id = subinventory_id
)
AND
(:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id
)
AND
(:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number
)
AND
(:lot_number_id = :l_miss_num
OR :lot_number_id IS NULL
AND lot_number_id IS NULL
OR :lot_number_id = lot_number_id
)
AND
(:lpn_id = :l_miss_num
OR :lpn_id IS NULL
AND lpn_id IS NULL
OR :lpn_id = lpn_id
)
AND
(:ship_ready_flag = :l_miss_num
OR (:ship_ready_flag IS NULL OR :ship_ready_flag = 2)
AND (ship_ready_flag IS NULL OR ship_ready_flag = 2)
OR :ship_ready_flag = ship_ready_flag
)
AND
(:staged_flag = :l_miss_char
OR (:staged_flag IS NULL OR :staged_flag = ''N'')
AND (staged_flag IS NULL OR staged_flag = ''N'')
OR :staged_flag = staged_flag
)
AND
(:attribute_category = :l_miss_char
OR :attribute_category IS NULL
AND attribute_category IS NULL
OR :attribute_category = attribute_category
)
AND
(:attribute1 = :l_miss_char
OR :attribute1 IS NULL
AND attribute1 IS NULL
OR :attribute1 = attribute1
)
AND
(:attribute2 = :l_miss_char
OR :attribute2 IS NULL
AND attribute2 IS NULL
OR :attribute2 = attribute2
)
AND
(:attribute3 = :l_miss_char
OR :attribute3 IS NULL
AND attribute3 IS NULL
OR :attribute3 = attribute3
)
AND
(:attribute4 = :l_miss_char
OR :attribute4 IS NULL
AND attribute4 IS NULL
OR :attribute4 = attribute4
)
AND
(:attribute5 = :l_miss_char
OR :attribute5 IS NULL
AND attribute5 IS NULL
OR :attribute5 = attribute5
)
AND
(:attribute6 = :l_miss_char
OR :attribute6 IS NULL
AND attribute6 IS NULL
OR :attribute6 = attribute6
)
AND
(:attribute7 = :l_miss_char
OR :attribute7 IS NULL
AND attribute7 IS NULL
OR :attribute7 = attribute7
)
AND
(:attribute8 = :l_miss_char
OR :attribute8 IS NULL
AND attribute8 IS NULL
OR :attribute8 = attribute8
)
AND
(:attribute9 = :l_miss_char
OR :attribute9 IS NULL
AND attribute9 IS NULL
OR :attribute9 = attribute9
)
AND
(:attribute10 = :l_miss_char
OR :attribute10 IS NULL
AND attribute10 IS NULL
OR :attribute10 = attribute10
)
AND
(:attribute11 = :l_miss_char
OR :attribute11 IS NULL
AND attribute11 IS NULL
OR :attribute11 = attribute11
)
AND
(:attribute12 = :l_miss_char
OR :attribute12 IS NULL
AND attribute12 IS NULL
OR :attribute12 = attribute12
)
AND
(:attribute13 = :l_miss_char
OR :attribute13 IS NULL
AND attribute13 IS NULL
OR :attribute13 = attribute13
)
AND
(:attribute14 = :l_miss_char
OR :attribute14 IS NULL
AND attribute14 IS NULL
OR :attribute14 = attribute14
)
AND
(:attribute15 = :l_miss_char
OR :attribute15 IS NULL
AND attribute15 IS NULL
OR :attribute15 = attribute15
)
/**** {{ R12 Enhanced reservations code changes }}****/
AND
(:crossdock_flag = :l_miss_char
OR :crossdock_flag IS NULL
AND crossdock_flag IS NULL
OR :crossdock_flag = crossdock_flag
)
AND
(:crossdock_criteria_id = :l_miss_num
OR :crossdock_criteria_id IS NULL
AND crossdock_criteria_id IS NULL
OR :crossdock_criteria_id = crossdock_criteria_id
)
AND
(:demand_source_line_detail = :l_miss_num
OR :demand_source_line_detail IS NULL
AND demand_source_line_detail IS NULL
OR :demand_source_line_detail = demand_source_line_detail
)
AND
(:supply_receipt_date = :l_miss_date
OR :supply_receipt_date IS NULL
AND supply_receipt_date IS NULL
OR :supply_receipt_date = supply_receipt_date
)
AND
(:demand_ship_date = :l_miss_date
OR :demand_ship_date IS NULL
AND demand_ship_date IS NULL
OR :demand_ship_date = demand_ship_date
)
AND
(:project_id = :l_miss_num
OR :project_id IS NULL
AND project_id IS NULL
OR :project_id = project_id
)
AND
(:task_id = :l_miss_num
OR :task_id IS NULL
AND task_id IS NULL
OR :task_id = task_id
)
/***** End R12 ***/
'
|| l_lock_stmt
|| l_sort_stmt
USING p_query_input.demand_source_line_id
, p_query_input.requirement_date
, l_miss_date
, p_query_input.requirement_date
, p_query_input.requirement_date
, p_query_input.organization_id
, l_miss_num
, p_query_input.organization_id
, p_query_input.organization_id
, p_query_input.inventory_item_id
, l_miss_num
, p_query_input.inventory_item_id
, p_query_input.inventory_item_id
, p_query_input.demand_source_type_id
, l_miss_num
, p_query_input.demand_source_type_id
, p_query_input.demand_source_type_id
, p_query_input.demand_source_header_id
, l_miss_num
, p_query_input.demand_source_header_id
, p_query_input.demand_source_header_id
, p_query_input.demand_source_name
, l_miss_char
, p_query_input.demand_source_name
, p_query_input.demand_source_name
, p_query_input.demand_source_delivery
, l_miss_num
, p_query_input.demand_source_delivery
, p_query_input.demand_source_delivery
, p_query_input.primary_uom_code
, l_miss_char
, p_query_input.primary_uom_code
, p_query_input.primary_uom_code
, p_query_input.primary_uom_id
, l_miss_num
, p_query_input.primary_uom_id
, p_query_input.primary_uom_id
-- INVCONV BEGIN
, p_query_input.secondary_uom_code
, l_miss_char
, p_query_input.secondary_uom_code
, p_query_input.secondary_uom_code
, p_query_input.secondary_uom_id
, l_miss_num
, p_query_input.secondary_uom_id
, p_query_input.secondary_uom_id
-- INVCONV END
, p_query_input.reservation_uom_code
, l_miss_char
, p_query_input.reservation_uom_code
, p_query_input.reservation_uom_code
, p_query_input.reservation_uom_id
, l_miss_num
, p_query_input.reservation_uom_id
, p_query_input.reservation_uom_id
, p_query_input.autodetail_group_id
, l_miss_num
, p_query_input.autodetail_group_id
, p_query_input.autodetail_group_id
, p_query_input.external_source_code
, l_miss_char
, p_query_input.external_source_code
, p_query_input.external_source_code
, p_query_input.external_source_line_id
, l_miss_num
, p_query_input.external_source_line_id
, p_query_input.external_source_line_id
, p_query_input.supply_source_type_id
, l_miss_num
, p_query_input.supply_source_type_id
, p_query_input.supply_source_type_id
, p_query_input.supply_source_header_id
, l_miss_num
, p_query_input.supply_source_header_id
, p_query_input.supply_source_header_id
, p_query_input.supply_source_line_id
, l_miss_num
, p_query_input.supply_source_line_id
, p_query_input.supply_source_line_id
, p_query_input.supply_source_name
, l_miss_char
, p_query_input.supply_source_name
, p_query_input.supply_source_name
, p_query_input.supply_source_line_detail
, l_miss_num
, p_query_input.supply_source_line_detail
, p_query_input.supply_source_line_detail
, p_query_input.revision
, l_miss_char
, p_query_input.revision
, p_query_input.revision
, p_query_input.subinventory_code
, l_miss_char
, p_query_input.subinventory_code
, p_query_input.subinventory_code
, p_query_input.subinventory_id
, l_miss_num
, p_query_input.subinventory_id
, p_query_input.subinventory_id
, p_query_input.locator_id
, l_miss_num
, p_query_input.locator_id
, p_query_input.locator_id
, p_query_input.lot_number
, l_miss_char
, p_query_input.lot_number
, p_query_input.lot_number
, p_query_input.lot_number_id
, l_miss_num
, p_query_input.lot_number_id
, p_query_input.lot_number_id
, p_query_input.lpn_id
, l_miss_num
, p_query_input.lpn_id
, p_query_input.lpn_id
, p_query_input.ship_ready_flag
, l_miss_num
, p_query_input.ship_ready_flag
, p_query_input.ship_ready_flag
, p_query_input.ship_ready_flag
, p_query_input.staged_flag
, l_miss_char
, p_query_input.staged_flag
, p_query_input.staged_flag
, p_query_input.staged_flag
, p_query_input.attribute_category
, l_miss_char
, p_query_input.attribute_category
, p_query_input.attribute_category
, p_query_input.attribute1
, l_miss_char
, p_query_input.attribute1
, p_query_input.attribute1
, p_query_input.attribute2
, l_miss_char
, p_query_input.attribute2
, p_query_input.attribute2
, p_query_input.attribute3
, l_miss_char
, p_query_input.attribute3
, p_query_input.attribute3
, p_query_input.attribute4
, l_miss_char
, p_query_input.attribute4
, p_query_input.attribute4
, p_query_input.attribute5
, l_miss_char
, p_query_input.attribute5
, p_query_input.attribute5
, p_query_input.attribute6
, l_miss_char
, p_query_input.attribute6
, p_query_input.attribute6
, p_query_input.attribute7
, l_miss_char
, p_query_input.attribute7
, p_query_input.attribute7
, p_query_input.attribute8
, l_miss_char
, p_query_input.attribute8
, p_query_input.attribute8
, p_query_input.attribute9
, l_miss_char
, p_query_input.attribute9
, p_query_input.attribute9
, p_query_input.attribute10
, l_miss_char
, p_query_input.attribute10
, p_query_input.attribute10
, p_query_input.attribute11
, l_miss_char
, p_query_input.attribute11
, p_query_input.attribute11
, p_query_input.attribute12
, l_miss_char
, p_query_input.attribute12
, p_query_input.attribute12
, p_query_input.attribute13
, l_miss_char
, p_query_input.attribute13
, p_query_input.attribute13
, p_query_input.attribute14
, l_miss_char
, p_query_input.attribute14
, p_query_input.attribute14
, p_query_input.attribute15
, l_miss_char
, p_query_input.attribute15
, p_query_input.attribute15
/**** {{ R12 Enhanced reservations code changes }}****/
, p_query_input.crossdock_flag
, l_miss_char
, p_query_input.crossdock_flag
, p_query_input.crossdock_flag
, p_query_input.crossdock_criteria_id
, l_miss_num
, p_query_input.crossdock_criteria_id
, p_query_input.crossdock_criteria_id
, p_query_input.demand_source_line_detail
, l_miss_num
, p_query_input.demand_source_line_detail
, p_query_input.demand_source_line_detail
, p_query_input.supply_receipt_date
, l_miss_date
, p_query_input.supply_receipt_date
, p_query_input.supply_receipt_date
, p_query_input.demand_ship_date
, l_miss_date
, p_query_input.demand_ship_date
, p_query_input.demand_ship_date
, p_query_input.project_id
, l_miss_num
, p_query_input.project_id
, p_query_input.project_id
, p_query_input.task_id
, l_miss_num
, p_query_input.task_id
, p_query_input.task_id
/***** End R12 ***/
;
OPEN l_cursor_ref FOR 'SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes. Adding new
-- columns for query reservations }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE
inventory_item_id = :inventory_item_id
and organization_id = :organization_id
AND
(:requirement_date = :l_miss_date
OR :requirement_date IS NULL
AND requirement_date IS NULL
OR :requirement_date
= requirement_date
)
AND
(:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id
= demand_source_type_id
)
AND
(:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id
= demand_source_header_id
)
AND
(:demand_source_line_id = :l_miss_num
OR :demand_source_line_id IS NULL
AND demand_source_line_id IS NULL
OR :demand_source_line_id = demand_source_line_id
)
AND
(:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name
)
AND
(:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery
)
AND
(:primary_uom_code = :l_miss_char
OR :primary_uom_code IS NULL
AND primary_uom_code IS NULL
OR :primary_uom_code = primary_uom_code
)
AND
(:primary_uom_id = :l_miss_num
OR :primary_uom_id IS NULL
AND primary_uom_id IS NULL
OR :primary_uom_id = primary_uom_id
)
-- INVCONV BEGIN
AND
(:secondary_uom_code = :l_miss_char
OR :secondary_uom_code IS NULL
AND secondary_uom_code IS NULL
OR :secondary_uom_code = secondary_uom_code
)
AND
(:secondary_uom_id = :l_miss_num
OR :secondary_uom_id IS NULL
AND secondary_uom_id IS NULL
OR :secondary_uom_id = secondary_uom_id
)
-- INVCONV END
AND
(:reservation_uom_code = :l_miss_char
OR :reservation_uom_code IS NULL
AND reservation_uom_code IS NULL
OR :reservation_uom_code = reservation_uom_code
)
AND
(:reservation_uom_id = :l_miss_num
OR :reservation_uom_id IS NULL
AND reservation_uom_id IS NULL
OR :reservation_uom_id = reservation_uom_id
)
AND
(:autodetail_group_id = :l_miss_num
OR :autodetail_group_id IS NULL
AND autodetail_group_id IS NULL
OR :autodetail_group_id = autodetail_group_id
)
AND
(:external_source_code = :l_miss_char
OR :external_source_code IS NULL
AND external_source_code IS NULL
OR :external_source_code = external_source_code
)
AND
(:external_source_line_id = :l_miss_num
OR :external_source_line_id IS NULL
AND external_source_line_id IS NULL
OR :external_source_line_id = external_source_line_id
)
AND
(:supply_source_type_id = :l_miss_num
OR :supply_source_type_id IS NULL
AND supply_source_type_id IS NULL
OR :supply_source_type_id = supply_source_type_id
)
AND
(:supply_source_header_id = :l_miss_num
OR :supply_source_header_id IS NULL
AND supply_source_header_id IS NULL
OR :supply_source_header_id
= supply_source_header_id
)
AND
(:supply_source_line_id = :l_miss_num
OR :supply_source_line_id IS NULL
AND supply_source_line_id IS NULL
OR :supply_source_line_id = supply_source_line_id
)
AND
(:supply_source_name = :l_miss_char
OR :supply_source_name IS NULL
AND supply_source_name IS NULL
OR :supply_source_name = supply_source_name
)
AND
(:supply_source_line_detail = :l_miss_num
OR :supply_source_line_detail IS NULL
AND supply_source_line_detail IS NULL
OR :supply_source_line_detail
= supply_source_line_detail
)
AND
(:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision
)
AND
(:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code
)
AND
(:subinventory_id = :l_miss_num
OR :subinventory_id IS NULL
AND subinventory_id IS NULL
OR :subinventory_id = subinventory_id
)
AND
(:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id
)
AND
(:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number
)
AND
(:lot_number_id = :l_miss_num
OR :lot_number_id IS NULL
AND lot_number_id IS NULL
OR :lot_number_id = lot_number_id
)
AND
(:lpn_id = :l_miss_num
OR :lpn_id IS NULL
AND lpn_id IS NULL
OR :lpn_id = lpn_id
)
AND
(:ship_ready_flag = :l_miss_num
OR (:ship_ready_flag IS NULL OR :ship_ready_flag = 2)
AND (ship_ready_flag IS NULL OR ship_ready_flag = 2)
OR :ship_ready_flag = ship_ready_flag
)
AND
(:staged_flag = :l_miss_char
OR (:staged_flag IS NULL OR :staged_flag = ''N'')
AND (staged_flag IS NULL OR staged_flag = ''N'')
OR :staged_flag = staged_flag
)
AND
(:attribute_category = :l_miss_char
OR :attribute_category IS NULL
AND attribute_category IS NULL
OR :attribute_category = attribute_category
)
AND
(:attribute1 = :l_miss_char
OR :attribute1 IS NULL
AND attribute1 IS NULL
OR :attribute1 = attribute1
)
AND
(:attribute2 = :l_miss_char
OR :attribute2 IS NULL
AND attribute2 IS NULL
OR :attribute2 = attribute2
)
AND
(:attribute3 = :l_miss_char
OR :attribute3 IS NULL
AND attribute3 IS NULL
OR :attribute3 = attribute3
)
AND
(:attribute4 = :l_miss_char
OR :attribute4 IS NULL
AND attribute4 IS NULL
OR :attribute4 = attribute4
)
AND
(:attribute5 = :l_miss_char
OR :attribute5 IS NULL
AND attribute5 IS NULL
OR :attribute5 = attribute5
)
AND
(:attribute6 = :l_miss_char
OR :attribute6 IS NULL
AND attribute6 IS NULL
OR :attribute6 = attribute6
)
AND
(:attribute7 = :l_miss_char
OR :attribute7 IS NULL
AND attribute7 IS NULL
OR :attribute7 = attribute7
)
AND
(:attribute8 = :l_miss_char
OR :attribute8 IS NULL
AND attribute8 IS NULL
OR :attribute8 = attribute8
)
AND
(:attribute9 = :l_miss_char
OR :attribute9 IS NULL
AND attribute9 IS NULL
OR :attribute9 = attribute9
)
AND
(:attribute10 = :l_miss_char
OR :attribute10 IS NULL
AND attribute10 IS NULL
OR :attribute10 = attribute10
)
AND
(:attribute11 = :l_miss_char
OR :attribute11 IS NULL
AND attribute11 IS NULL
OR :attribute11 = attribute11
)
AND
(:attribute12 = :l_miss_char
OR :attribute12 IS NULL
AND attribute12 IS NULL
OR :attribute12 = attribute12
)
AND
(:attribute13 = :l_miss_char
OR :attribute13 IS NULL
AND attribute13 IS NULL
OR :attribute13 = attribute13
)
AND
(:attribute14 = :l_miss_char
OR :attribute14 IS NULL
AND attribute14 IS NULL
OR :attribute14 = attribute14
)
AND
(:attribute15 = :l_miss_char
OR :attribute15 IS NULL
AND attribute15 IS NULL
OR :attribute15 = attribute15
)
/**** {{ R12 Enhanced reservations code changes }}****/
AND
(:crossdock_flag = :l_miss_char
OR :crossdock_flag IS NULL
AND crossdock_flag IS NULL
OR :crossdock_flag = crossdock_flag
)
AND
(:crossdock_criteria_id = :l_miss_num
OR :crossdock_criteria_id IS NULL
AND crossdock_criteria_id IS NULL
OR :crossdock_criteria_id = crossdock_criteria_id
)
AND
(:demand_source_line_detail = :l_miss_num
OR :demand_source_line_detail IS NULL
AND demand_source_line_detail IS NULL
OR :demand_source_line_detail = demand_source_line_detail
)
AND
(:supply_receipt_date = :l_miss_date
OR :supply_receipt_date IS NULL
AND supply_receipt_date IS NULL
OR :supply_receipt_date = supply_receipt_date
)
AND
(:demand_ship_date = :l_miss_date
OR :demand_ship_date IS NULL
AND demand_ship_date IS NULL
OR :demand_ship_date = demand_ship_date
)
AND
(:project_id = :l_miss_num
OR :project_id IS NULL
AND project_id IS NULL
OR :project_id = project_id
)
AND
(:task_id = :l_miss_num
OR :task_id IS NULL
AND task_id IS NULL
OR :task_id = task_id
)
/***** End R12 ***/
'
|| l_lock_stmt
|| l_sort_stmt
USING p_query_input.inventory_item_id
, p_query_input.organization_id
, p_query_input.requirement_date
, l_miss_date
, p_query_input.requirement_date
, p_query_input.requirement_date
, p_query_input.demand_source_type_id
, l_miss_num
, p_query_input.demand_source_type_id
, p_query_input.demand_source_type_id
, p_query_input.demand_source_header_id
, l_miss_num
, p_query_input.demand_source_header_id
, p_query_input.demand_source_header_id
, p_query_input.demand_source_line_id
, l_miss_num
, p_query_input.demand_source_line_id
, p_query_input.demand_source_line_id
, p_query_input.demand_source_name
, l_miss_char
, p_query_input.demand_source_name
, p_query_input.demand_source_name
, p_query_input.demand_source_delivery
, l_miss_num
, p_query_input.demand_source_delivery
, p_query_input.demand_source_delivery
, p_query_input.primary_uom_code
, l_miss_char
, p_query_input.primary_uom_code
, p_query_input.primary_uom_code
, p_query_input.primary_uom_id
, l_miss_num
, p_query_input.primary_uom_id
, p_query_input.primary_uom_id
-- INVCONV BEGIN
, p_query_input.secondary_uom_code
, l_miss_char
, p_query_input.secondary_uom_code
, p_query_input.secondary_uom_code
, p_query_input.secondary_uom_id
, l_miss_num
, p_query_input.secondary_uom_id
, p_query_input.secondary_uom_id
-- INVCONV END
, p_query_input.reservation_uom_code
, l_miss_char
, p_query_input.reservation_uom_code
, p_query_input.reservation_uom_code
, p_query_input.reservation_uom_id
, l_miss_num
, p_query_input.reservation_uom_id
, p_query_input.reservation_uom_id
, p_query_input.autodetail_group_id
, l_miss_num
, p_query_input.autodetail_group_id
, p_query_input.autodetail_group_id
, p_query_input.external_source_code
, l_miss_char
, p_query_input.external_source_code
, p_query_input.external_source_code
, p_query_input.external_source_line_id
, l_miss_num
, p_query_input.external_source_line_id
, p_query_input.external_source_line_id
, p_query_input.supply_source_type_id
, l_miss_num
, p_query_input.supply_source_type_id
, p_query_input.supply_source_type_id
, p_query_input.supply_source_header_id
, l_miss_num
, p_query_input.supply_source_header_id
, p_query_input.supply_source_header_id
, p_query_input.supply_source_line_id
, l_miss_num
, p_query_input.supply_source_line_id
, p_query_input.supply_source_line_id
, p_query_input.supply_source_name
, l_miss_char
, p_query_input.supply_source_name
, p_query_input.supply_source_name
, p_query_input.supply_source_line_detail
, l_miss_num
, p_query_input.supply_source_line_detail
, p_query_input.supply_source_line_detail
, p_query_input.revision
, l_miss_char
, p_query_input.revision
, p_query_input.revision
, p_query_input.subinventory_code
, l_miss_char
, p_query_input.subinventory_code
, p_query_input.subinventory_code
, p_query_input.subinventory_id
, l_miss_num
, p_query_input.subinventory_id
, p_query_input.subinventory_id
, p_query_input.locator_id
, l_miss_num
, p_query_input.locator_id
, p_query_input.locator_id
, p_query_input.lot_number
, l_miss_char
, p_query_input.lot_number
, p_query_input.lot_number
, p_query_input.lot_number_id
, l_miss_num
, p_query_input.lot_number_id
, p_query_input.lot_number_id
, p_query_input.lpn_id
, l_miss_num
, p_query_input.lpn_id
, p_query_input.lpn_id
, p_query_input.ship_ready_flag
, l_miss_num
, p_query_input.ship_ready_flag
, p_query_input.ship_ready_flag
, p_query_input.ship_ready_flag
, p_query_input.staged_flag
, l_miss_char
, p_query_input.staged_flag
, p_query_input.staged_flag
, p_query_input.staged_flag
, p_query_input.attribute_category
, l_miss_char
, p_query_input.attribute_category
, p_query_input.attribute_category
, p_query_input.attribute1
, l_miss_char
, p_query_input.attribute1
, p_query_input.attribute1
, p_query_input.attribute2
, l_miss_char
, p_query_input.attribute2
, p_query_input.attribute2
, p_query_input.attribute3
, l_miss_char
, p_query_input.attribute3
, p_query_input.attribute3
, p_query_input.attribute4
, l_miss_char
, p_query_input.attribute4
, p_query_input.attribute4
, p_query_input.attribute5
, l_miss_char
, p_query_input.attribute5
, p_query_input.attribute5
, p_query_input.attribute6
, l_miss_char
, p_query_input.attribute6
, p_query_input.attribute6
, p_query_input.attribute7
, l_miss_char
, p_query_input.attribute7
, p_query_input.attribute7
, p_query_input.attribute8
, l_miss_char
, p_query_input.attribute8
, p_query_input.attribute8
, p_query_input.attribute9
, l_miss_char
, p_query_input.attribute9
, p_query_input.attribute9
, p_query_input.attribute10
, l_miss_char
, p_query_input.attribute10
, p_query_input.attribute10
, p_query_input.attribute11
, l_miss_char
, p_query_input.attribute11
, p_query_input.attribute11
, p_query_input.attribute12
, l_miss_char
, p_query_input.attribute12
, p_query_input.attribute12
, p_query_input.attribute13
, l_miss_char
, p_query_input.attribute13
, p_query_input.attribute13
, p_query_input.attribute14
, l_miss_char
, p_query_input.attribute14
, p_query_input.attribute14
, p_query_input.attribute15
, l_miss_char
, p_query_input.attribute15
, p_query_input.attribute15
/**** {{ R12 Enhanced reservations code changes }}****/
, p_query_input.crossdock_flag
, l_miss_char
, p_query_input.crossdock_flag
, p_query_input.crossdock_flag
, p_query_input.crossdock_criteria_id
, l_miss_num
, p_query_input.crossdock_criteria_id
, p_query_input.crossdock_criteria_id
, p_query_input.demand_source_line_detail
, l_miss_num
, p_query_input.demand_source_line_detail
, p_query_input.demand_source_line_detail
, p_query_input.supply_receipt_date
, l_miss_date
, p_query_input.supply_receipt_date
, p_query_input.supply_receipt_date
, p_query_input.demand_ship_date
, l_miss_date
, p_query_input.demand_ship_date
, p_query_input.demand_ship_date
, p_query_input.project_id
, l_miss_num
, p_query_input.project_id
, p_query_input.project_id
, p_query_input.task_id
, l_miss_num
, p_query_input.task_id
, p_query_input.task_id
/***** End R12 ***/
;
l_qry_stmt:= 'SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE
supply_source_header_id = :supply_source_header_id
and supply_source_type_id = :supply_source_type_id
AND
(:requirement_date = :l_miss_date
OR :requirement_date IS NULL
AND requirement_date IS NULL
OR :requirement_date
= requirement_date
)
AND
(:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id
)
AND
(:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULL
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id
)
AND
(:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id
= demand_source_type_id
)
AND
(:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id
= demand_source_header_id
)
AND
(:demand_source_line_id = :l_miss_num
OR :demand_source_line_id IS NULL
AND demand_source_line_id IS NULL
OR :demand_source_line_id = demand_source_line_id
)
AND
(:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name
)
AND
(:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery
)
AND
(:primary_uom_code = :l_miss_char
OR :primary_uom_code IS NULL
AND primary_uom_code IS NULL
OR :primary_uom_code = primary_uom_code
)
AND
(:primary_uom_id = :l_miss_num
OR :primary_uom_id IS NULL
AND primary_uom_id IS NULL
OR :primary_uom_id = primary_uom_id
)
-- INVCONV BEGIN
AND
(:secondary_uom_code = :l_miss_char
OR :secondary_uom_code IS NULL
AND secondary_uom_code IS NULL
OR :secondary_uom_code = secondary_uom_code
)
AND
(:secondary_uom_id = :l_miss_num
OR :secondary_uom_id IS NULL
AND secondary_uom_id IS NULL
OR :secondary_uom_id = secondary_uom_id
)
-- INVCONV END
AND
(:reservation_uom_code = :l_miss_char
OR :reservation_uom_code IS NULL
AND reservation_uom_code IS NULL
OR :reservation_uom_code = reservation_uom_code
)
AND
(:reservation_uom_id = :l_miss_num
OR :reservation_uom_id IS NULL
AND reservation_uom_id IS NULL
OR :reservation_uom_id = reservation_uom_id
)
AND
(:autodetail_group_id = :l_miss_num
OR :autodetail_group_id IS NULL
AND autodetail_group_id IS NULL
OR :autodetail_group_id = autodetail_group_id
)
AND
(:external_source_code = :l_miss_char
OR :external_source_code IS NULL
AND external_source_code IS NULL
OR :external_source_code = external_source_code
)
AND
(:external_source_line_id = :l_miss_num
OR :external_source_line_id IS NULL
AND external_source_line_id IS NULL
OR :external_source_line_id = external_source_line_id
)
AND
(:supply_source_name = :l_miss_char
OR :supply_source_name IS NULL
AND supply_source_name IS NULL
OR :supply_source_name = supply_source_name
)
AND
(:supply_source_line_detail = :l_miss_num
OR :supply_source_line_detail IS NULL
AND supply_source_line_detail IS NULL
OR :supply_source_line_detail
= supply_source_line_detail
)
AND
(:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision
)
AND
(:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code
)
AND
(:subinventory_id = :l_miss_num
OR :subinventory_id IS NULL
AND subinventory_id IS NULL
OR :subinventory_id = subinventory_id
)
AND
(:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id
)
AND
(:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number
)
AND
(:lot_number_id = :l_miss_num
OR :lot_number_id IS NULL
AND lot_number_id IS NULL
OR :lot_number_id = lot_number_id
)
AND
(:lpn_id = :l_miss_num
OR :lpn_id IS NULL
AND lpn_id IS NULL
OR :lpn_id = lpn_id
)
AND
(:ship_ready_flag = :l_miss_num
OR (:ship_ready_flag IS NULL OR :ship_ready_flag = 2)
AND (ship_ready_flag IS NULL OR ship_ready_flag = 2)
OR :ship_ready_flag = ship_ready_flag
)
AND
(:staged_flag = :l_miss_char
OR (:staged_flag IS NULL OR :staged_flag = ''N'')
AND (staged_flag IS NULL OR staged_flag = ''N'')
OR :staged_flag = staged_flag
)
AND
(:attribute_category = :l_miss_char
OR :attribute_category IS NULL
AND attribute_category IS NULL
OR :attribute_category = attribute_category
)
AND
(:attribute1 = :l_miss_char
OR :attribute1 IS NULL
AND attribute1 IS NULL
OR :attribute1 = attribute1
)
AND
(:attribute2 = :l_miss_char
OR :attribute2 IS NULL
AND attribute2 IS NULL
OR :attribute2 = attribute2
)
AND
(:attribute3 = :l_miss_char
OR :attribute3 IS NULL
AND attribute3 IS NULL
OR :attribute3 = attribute3
)
AND
(:attribute4 = :l_miss_char
OR :attribute4 IS NULL
AND attribute4 IS NULL
OR :attribute4 = attribute4
)
AND
(:attribute5 = :l_miss_char
OR :attribute5 IS NULL
AND attribute5 IS NULL
OR :attribute5 = attribute5
)
AND
(:attribute6 = :l_miss_char
OR :attribute6 IS NULL
AND attribute6 IS NULL
OR :attribute6 = attribute6
)
AND
(:attribute7 = :l_miss_char
OR :attribute7 IS NULL
AND attribute7 IS NULL
OR :attribute7 = attribute7
)
AND
(:attribute8 = :l_miss_char
OR :attribute8 IS NULL
AND attribute8 IS NULL
OR :attribute8 = attribute8
)
AND
(:attribute9 = :l_miss_char
OR :attribute9 IS NULL
AND attribute9 IS NULL
OR :attribute9 = attribute9
)
AND
(:attribute10 = :l_miss_char
OR :attribute10 IS NULL
AND attribute10 IS NULL
OR :attribute10 = attribute10
)
AND
(:attribute11 = :l_miss_char
OR :attribute11 IS NULL
AND attribute11 IS NULL
OR :attribute11 = attribute11
)
AND
(:attribute12 = :l_miss_char
OR :attribute12 IS NULL
AND attribute12 IS NULL
OR :attribute12 = attribute12
)
AND
(:attribute13 = :l_miss_char
OR :attribute13 IS NULL
AND attribute13 IS NULL
OR :attribute13 = attribute13
)
AND
(:attribute14 = :l_miss_char
OR :attribute14 IS NULL
AND attribute14 IS NULL
OR :attribute14 = attribute14
)
AND
(:attribute15 = :l_miss_char
OR :attribute15 IS NULL
AND attribute15 IS NULL
OR :attribute15 = attribute15
)
/**** {{ R12 Enhanced reservations code changes }}****/
AND
(:crossdock_flag = :l_miss_char
OR :crossdock_flag IS NULL
AND crossdock_flag IS NULL
OR :crossdock_flag = crossdock_flag
)
AND
(:crossdock_criteria_id = :l_miss_num
OR :crossdock_criteria_id IS NULL
AND crossdock_criteria_id IS NULL
OR :crossdock_criteria_id = crossdock_criteria_id
)
AND
(:demand_source_line_detail = :l_miss_num
OR :demand_source_line_detail IS NULL
AND demand_source_line_detail IS NULL
OR :demand_source_line_detail = demand_source_line_detail
)
AND
(:supply_receipt_date = :l_miss_date
OR :supply_receipt_date IS NULL
AND supply_receipt_date IS NULL
OR :supply_receipt_date = supply_receipt_date
)
AND
(:demand_ship_date = :l_miss_date
OR :demand_ship_date IS NULL
AND demand_ship_date IS NULL
OR :demand_ship_date = demand_ship_date
)
AND
(:project_id = :l_miss_num
OR :project_id IS NULL
AND project_id IS NULL
OR :project_id = project_id
)
AND
(:task_id = :l_miss_num
OR :task_id IS NULL
AND task_id IS NULL
OR :task_id = task_id
)
';
OPEN l_cursor_ref FOR 'SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE
(:requirement_date = :l_miss_date
OR :requirement_date IS NULL
AND requirement_date IS NULL
OR :requirement_date
= requirement_date
)
AND
(:organization_id = :l_miss_num
OR :organization_id IS NULL
AND organization_id IS NULL
OR :organization_id = organization_id
)
AND
(:inventory_item_id = :l_miss_num
OR :inventory_item_id IS NULL
AND inventory_item_id IS NULL
OR :inventory_item_id = inventory_item_id
)
AND
(:demand_source_type_id = :l_miss_num
OR :demand_source_type_id IS NULL
AND demand_source_type_id IS NULL
OR :demand_source_type_id
= demand_source_type_id
)
AND
(:demand_source_header_id = :l_miss_num
OR :demand_source_header_id IS NULL
AND demand_source_header_id IS NULL
OR :demand_source_header_id
= demand_source_header_id
)
AND
(:demand_source_line_id = :l_miss_num
OR :demand_source_line_id IS NULL
AND demand_source_line_id IS NULL
OR :demand_source_line_id = demand_source_line_id
)
AND
(:demand_source_name = :l_miss_char
OR :demand_source_name IS NULL
AND demand_source_name IS NULL
OR :demand_source_name = demand_source_name
)
AND
(:demand_source_delivery = :l_miss_num
OR :demand_source_delivery IS NULL
AND demand_source_delivery IS NULL
OR :demand_source_delivery = demand_source_delivery
)
AND
(:primary_uom_code = :l_miss_char
OR :primary_uom_code IS NULL
AND primary_uom_code IS NULL
OR :primary_uom_code = primary_uom_code
)
AND
(:primary_uom_id = :l_miss_num
OR :primary_uom_id IS NULL
AND primary_uom_id IS NULL
OR :primary_uom_id = primary_uom_id
)
-- INVCONV BEGIN
AND
(:secondary_uom_code = :l_miss_char
OR :secondary_uom_code IS NULL
AND secondary_uom_code IS NULL
OR :secondary_uom_code = secondary_uom_code
)
AND
(:secondary_uom_id = :l_miss_num
OR :secondary_uom_id IS NULL
AND secondary_uom_id IS NULL
OR :secondary_uom_id = secondary_uom_id
)
-- INVCONV END
AND
(:reservation_uom_code = :l_miss_char
OR :reservation_uom_code IS NULL
AND reservation_uom_code IS NULL
OR :reservation_uom_code = reservation_uom_code
)
AND
(:reservation_uom_id = :l_miss_num
OR :reservation_uom_id IS NULL
AND reservation_uom_id IS NULL
OR :reservation_uom_id = reservation_uom_id
)
AND
(:autodetail_group_id = :l_miss_num
OR :autodetail_group_id IS NULL
AND autodetail_group_id IS NULL
OR :autodetail_group_id = autodetail_group_id
)
AND
(:external_source_code = :l_miss_char
OR :external_source_code IS NULL
AND external_source_code IS NULL
OR :external_source_code = external_source_code
)
AND
(:external_source_line_id = :l_miss_num
OR :external_source_line_id IS NULL
AND external_source_line_id IS NULL
OR :external_source_line_id = external_source_line_id
)
AND
(:supply_source_type_id = :l_miss_num
OR :supply_source_type_id IS NULL
AND supply_source_type_id IS NULL
OR :supply_source_type_id = supply_source_type_id
)
AND
(:supply_source_header_id = :l_miss_num
OR :supply_source_header_id IS NULL
AND supply_source_header_id IS NULL
OR :supply_source_header_id
= supply_source_header_id
)
AND
(:supply_source_line_id = :l_miss_num
OR :supply_source_line_id IS NULL
AND supply_source_line_id IS NULL
OR :supply_source_line_id = supply_source_line_id
)
AND
(:supply_source_name = :l_miss_char
OR :supply_source_name IS NULL
AND supply_source_name IS NULL
OR :supply_source_name = supply_source_name
)
AND
(:supply_source_line_detail = :l_miss_num
OR :supply_source_line_detail IS NULL
AND supply_source_line_detail IS NULL
OR :supply_source_line_detail
= supply_source_line_detail
)
AND
(:revision = :l_miss_char
OR :revision IS NULL
AND revision IS NULL
OR :revision = revision
)
AND
(:subinventory_code = :l_miss_char
OR :subinventory_code IS NULL
AND subinventory_code IS NULL
OR :subinventory_code = subinventory_code
)
AND
(:subinventory_id = :l_miss_num
OR :subinventory_id IS NULL
AND subinventory_id IS NULL
OR :subinventory_id = subinventory_id
)
AND
(:locator_id = :l_miss_num
OR :locator_id IS NULL
AND locator_id IS NULL
OR :locator_id = locator_id
)
AND
(:lot_number = :l_miss_char
OR :lot_number IS NULL
AND lot_number IS NULL
OR :lot_number = lot_number
)
AND
(:lot_number_id = :l_miss_num
OR :lot_number_id IS NULL
AND lot_number_id IS NULL
OR :lot_number_id = lot_number_id
)
AND
(:lpn_id = :l_miss_num
OR :lpn_id IS NULL
AND lpn_id IS NULL
OR :lpn_id = lpn_id
)
AND
(:ship_ready_flag = :l_miss_num
OR (:ship_ready_flag IS NULL OR :ship_ready_flag = 2)
AND (ship_ready_flag IS NULL OR ship_ready_flag = 2)
OR :ship_ready_flag = ship_ready_flag
)
AND
(:staged_flag = :l_miss_char
OR (:staged_flag IS NULL OR :staged_flag = ''N'')
AND (staged_flag IS NULL OR staged_flag = ''N'')
OR :staged_flag = staged_flag
)
AND
(:attribute_category = :l_miss_char
OR :attribute_category IS NULL
AND attribute_category IS NULL
OR :attribute_category = attribute_category
)
AND
(:attribute1 = :l_miss_char
OR :attribute1 IS NULL
AND attribute1 IS NULL
OR :attribute1 = attribute1
)
AND
(:attribute2 = :l_miss_char
OR :attribute2 IS NULL
AND attribute2 IS NULL
OR :attribute2 = attribute2
)
AND
(:attribute3 = :l_miss_char
OR :attribute3 IS NULL
AND attribute3 IS NULL
OR :attribute3 = attribute3
)
AND
(:attribute4 = :l_miss_char
OR :attribute4 IS NULL
AND attribute4 IS NULL
OR :attribute4 = attribute4
)
AND
(:attribute5 = :l_miss_char
OR :attribute5 IS NULL
AND attribute5 IS NULL
OR :attribute5 = attribute5
)
AND
(:attribute6 = :l_miss_char
OR :attribute6 IS NULL
AND attribute6 IS NULL
OR :attribute6 = attribute6
)
AND
(:attribute7 = :l_miss_char
OR :attribute7 IS NULL
AND attribute7 IS NULL
OR :attribute7 = attribute7
)
AND
(:attribute8 = :l_miss_char
OR :attribute8 IS NULL
AND attribute8 IS NULL
OR :attribute8 = attribute8
)
AND
(:attribute9 = :l_miss_char
OR :attribute9 IS NULL
AND attribute9 IS NULL
OR :attribute9 = attribute9
)
AND
(:attribute10 = :l_miss_char
OR :attribute10 IS NULL
AND attribute10 IS NULL
OR :attribute10 = attribute10
)
AND
(:attribute11 = :l_miss_char
OR :attribute11 IS NULL
AND attribute11 IS NULL
OR :attribute11 = attribute11
)
AND
(:attribute12 = :l_miss_char
OR :attribute12 IS NULL
AND attribute12 IS NULL
OR :attribute12 = attribute12
)
AND
(:attribute13 = :l_miss_char
OR :attribute13 IS NULL
AND attribute13 IS NULL
OR :attribute13 = attribute13
)
AND
(:attribute14 = :l_miss_char
OR :attribute14 IS NULL
AND attribute14 IS NULL
OR :attribute14 = attribute14
)
AND
(:attribute15 = :l_miss_char
OR :attribute15 IS NULL
AND attribute15 IS NULL
OR :attribute15 = attribute15
)
/**** {{ R12 Enhanced reservations code changes }}****/
AND
(:crossdock_flag = :l_miss_char
OR :crossdock_flag IS NULL
AND crossdock_flag IS NULL
OR :crossdock_flag = crossdock_flag
)
AND
(:crossdock_criteria_id = :l_miss_num
OR :crossdock_criteria_id IS NULL
AND crossdock_criteria_id IS NULL
OR :crossdock_criteria_id = crossdock_criteria_id
)
AND
(:demand_source_line_detail = :l_miss_num
OR :demand_source_line_detail IS NULL
AND demand_source_line_detail IS NULL
OR :demand_source_line_detail = demand_source_line_detail
)
AND
(:supply_receipt_date = :l_miss_date
OR :supply_receipt_date IS NULL
AND supply_receipt_date IS NULL
OR :supply_receipt_date = supply_receipt_date
)
AND
(:demand_ship_date = :l_miss_date
OR :demand_ship_date IS NULL
AND demand_ship_date IS NULL
OR :demand_ship_date = demand_ship_date
)
AND
(:project_id = :l_miss_num
OR :project_id IS NULL
AND project_id IS NULL
OR :project_id = project_id
)
AND
(:task_id = :l_miss_num
OR :task_id IS NULL
AND task_id IS NULL
OR :task_id = task_id
)
/***** End R12 ***/
'
|| l_lock_stmt
|| l_sort_stmt
USING p_query_input.requirement_date
, l_miss_date
, p_query_input.requirement_date
, p_query_input.requirement_date
, p_query_input.organization_id
, l_miss_num
, p_query_input.organization_id
, p_query_input.organization_id
, p_query_input.inventory_item_id
, l_miss_num
, p_query_input.inventory_item_id
, p_query_input.inventory_item_id
, p_query_input.demand_source_type_id
, l_miss_num
, p_query_input.demand_source_type_id
, p_query_input.demand_source_type_id
, p_query_input.demand_source_header_id
, l_miss_num
, p_query_input.demand_source_header_id
, p_query_input.demand_source_header_id
, p_query_input.demand_source_line_id
, l_miss_num
, p_query_input.demand_source_line_id
, p_query_input.demand_source_line_id
, p_query_input.demand_source_name
, l_miss_char
, p_query_input.demand_source_name
, p_query_input.demand_source_name
, p_query_input.demand_source_delivery
, l_miss_num
, p_query_input.demand_source_delivery
, p_query_input.demand_source_delivery
, p_query_input.primary_uom_code
, l_miss_char
, p_query_input.primary_uom_code
, p_query_input.primary_uom_code
, p_query_input.primary_uom_id
, l_miss_num
, p_query_input.primary_uom_id
, p_query_input.primary_uom_id
-- INVCONV BEGIN
, p_query_input.secondary_uom_code
, l_miss_char
, p_query_input.secondary_uom_code
, p_query_input.secondary_uom_code
, p_query_input.secondary_uom_id
, l_miss_num
, p_query_input.secondary_uom_id
, p_query_input.secondary_uom_id
-- INVCONV END
, p_query_input.reservation_uom_code
, l_miss_char
, p_query_input.reservation_uom_code
, p_query_input.reservation_uom_code
, p_query_input.reservation_uom_id
, l_miss_num
, p_query_input.reservation_uom_id
, p_query_input.reservation_uom_id
, p_query_input.autodetail_group_id
, l_miss_num
, p_query_input.autodetail_group_id
, p_query_input.autodetail_group_id
, p_query_input.external_source_code
, l_miss_char
, p_query_input.external_source_code
, p_query_input.external_source_code
, p_query_input.external_source_line_id
, l_miss_num
, p_query_input.external_source_line_id
, p_query_input.external_source_line_id
, p_query_input.supply_source_type_id
, l_miss_num
, p_query_input.supply_source_type_id
, p_query_input.supply_source_type_id
, p_query_input.supply_source_header_id
, l_miss_num
, p_query_input.supply_source_header_id
, p_query_input.supply_source_header_id
, p_query_input.supply_source_line_id
, l_miss_num
, p_query_input.supply_source_line_id
, p_query_input.supply_source_line_id
, p_query_input.supply_source_name
, l_miss_char
, p_query_input.supply_source_name
, p_query_input.supply_source_name
, p_query_input.supply_source_line_detail
, l_miss_num
, p_query_input.supply_source_line_detail
, p_query_input.supply_source_line_detail
, p_query_input.revision
, l_miss_char
, p_query_input.revision
, p_query_input.revision
, p_query_input.subinventory_code
, l_miss_char
, p_query_input.subinventory_code
, p_query_input.subinventory_code
, p_query_input.subinventory_id
, l_miss_num
, p_query_input.subinventory_id
, p_query_input.subinventory_id
, p_query_input.locator_id
, l_miss_num
, p_query_input.locator_id
, p_query_input.locator_id
, p_query_input.lot_number
, l_miss_char
, p_query_input.lot_number
, p_query_input.lot_number
, p_query_input.lot_number_id
, l_miss_num
, p_query_input.lot_number_id
, p_query_input.lot_number_id
, p_query_input.lpn_id
, l_miss_num
, p_query_input.lpn_id
, p_query_input.lpn_id
, p_query_input.ship_ready_flag
, l_miss_num
, p_query_input.ship_ready_flag
, p_query_input.ship_ready_flag
, p_query_input.ship_ready_flag
, p_query_input.staged_flag
, l_miss_char
, p_query_input.staged_flag
, p_query_input.staged_flag
, p_query_input.staged_flag
, p_query_input.attribute_category
, l_miss_char
, p_query_input.attribute_category
, p_query_input.attribute_category
, p_query_input.attribute1
, l_miss_char
, p_query_input.attribute1
, p_query_input.attribute1
, p_query_input.attribute2
, l_miss_char
, p_query_input.attribute2
, p_query_input.attribute2
, p_query_input.attribute3
, l_miss_char
, p_query_input.attribute3
, p_query_input.attribute3
, p_query_input.attribute4
, l_miss_char
, p_query_input.attribute4
, p_query_input.attribute4
, p_query_input.attribute5
, l_miss_char
, p_query_input.attribute5
, p_query_input.attribute5
, p_query_input.attribute6
, l_miss_char
, p_query_input.attribute6
, p_query_input.attribute6
, p_query_input.attribute7
, l_miss_char
, p_query_input.attribute7
, p_query_input.attribute7
, p_query_input.attribute8
, l_miss_char
, p_query_input.attribute8
, p_query_input.attribute8
, p_query_input.attribute9
, l_miss_char
, p_query_input.attribute9
, p_query_input.attribute9
, p_query_input.attribute10
, l_miss_char
, p_query_input.attribute10
, p_query_input.attribute10
, p_query_input.attribute11
, l_miss_char
, p_query_input.attribute11
, p_query_input.attribute11
, p_query_input.attribute12
, l_miss_char
, p_query_input.attribute12
, p_query_input.attribute12
, p_query_input.attribute13
, l_miss_char
, p_query_input.attribute13
, p_query_input.attribute13
, p_query_input.attribute14
, l_miss_char
, p_query_input.attribute14
, p_query_input.attribute14
, p_query_input.attribute15
, l_miss_char
, p_query_input.attribute15
, p_query_input.attribute15
/**** {{ R12 Enhanced reservations code changes }}****/
, p_query_input.crossdock_flag
, l_miss_char
, p_query_input.crossdock_flag
, p_query_input.crossdock_flag
, p_query_input.crossdock_criteria_id
, l_miss_num
, p_query_input.crossdock_criteria_id
, p_query_input.crossdock_criteria_id
, p_query_input.demand_source_line_detail
, l_miss_num
, p_query_input.demand_source_line_detail
, p_query_input.demand_source_line_detail
, p_query_input.supply_receipt_date
, l_miss_date
, p_query_input.supply_receipt_date
, p_query_input.supply_receipt_date
, p_query_input.demand_ship_date
, l_miss_date
, p_query_input.demand_ship_date
, p_query_input.demand_ship_date
, p_query_input.project_id
, l_miss_num
, p_query_input.project_id
, p_query_input.project_id
, p_query_input.task_id
, l_miss_num
, p_query_input.task_id
, p_query_input.task_id
/***** End R12 ***/
;
IF l_update THEN
-- IF (l_debug = 1) then
-- debug_print(' Inside res cursor for update...');
FETCH c_res_id_update INTO l_rsv_rec;
EXIT WHEN c_res_id_update%NOTFOUND;
-- debug_print(' Inside res cursor no update...');
IF l_update THEN
-- IF (l_debug = 1) then
-- debug_print (' Inside demand cursor for update...');
FETCH c_demand_update INTO l_rsv_rec;
EXIT WHEN c_demand_update%NOTFOUND;
-- debug_print (' Inside demand cursor no update...');
ELSIF c_res_id_update%ISOPEN THEN
CLOSE c_res_id_update;
ELSIF c_demand_update%ISOPEN THEN
CLOSE c_demand_update;
SELECT reservation_id, serial_number bulk collect INTO
l_serial_number_table FROM mtl_serial_numbers
WHERE reservation_id = l_mtl_reservation_tbl(i).reservation_id;
SELECT reservation_id INTO l_reservation_id FROM
mtl_serial_numbers WHERE serial_number = l_serial_number AND
current_organization_id = l_mtl_reservation_tbl(l_serial_table_index).organization_id AND
inventory_item_id = l_mtl_reservation_tbl(l_serial_table_index).inventory_item_id;
l_rsv_updated BOOLEAN :=FALSE;
SELECT *
FROM mtl_system_items
WHERE inventory_Item_Id = p_inventory_item_id;
-- numbers are passed. the calling api should call update
-- reservations and not create reservations
IF l_debug=1 THEN
debug_print('Serial numbers are passed with partial flag exists. error out');
debug_print('Need to update reservation record');
debug_print('Reservation record that needs to be updated');
debug_print('Calling update reservations to update reservation record');
inv_reservation_pvt.update_reservation
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_quantity_reserved => l_quantity_reserved,
x_secondary_quantity_reserved => l_secondary_quantity_reserved, -- INVCONV
p_original_rsv_rec => l_mtl_reservation_tbl(i),
p_to_rsv_rec => l_to_rsv_rec,
p_original_serial_number => p_serial_number,
p_to_serial_number => l_dummy_serial_array,
p_validation_flag => p_validation_flag, -- BUG 4705409
p_partial_reservation_flag => p_partial_reservation_flag,
p_check_availability => fnd_api.g_true,
p_over_reservation_flag => p_over_reservation_flag
);
l_rsv_updated := TRUE;
IF ((NOT l_rsv_updated) OR (l_mtl_reservation_tbl.COUNT = 0)) THEN
/*** End R12 ***/
/**** {{ R12 Enhanced reservations code changes.Calling the reservation
-- lock API to create a user-defined lock for non-inventory supplies }} *****/
-- Bug 5199672: Should pass null to supply and demand line detail as
-- we will have to lock the records at the document level and not at
-- the line level. Also, for ASN, pass the source type as PO so that the
-- the lock name would be the same as the PO's
IF (l_rsv_rec.supply_source_type_id =
inv_reservation_global.g_source_type_asn) THEN
l_supply_source_type_id :=
inv_reservation_global.g_source_type_po;
debug_print('Primary_reservation_qty before inserting (create)= '
||To_char(l_rsv_rec.primary_reservation_quantity) );
debug_print('Secondary_reservation_qty before inserting (create)= '
||To_char(l_rsv_rec.secondary_reservation_quantity) ); -- INVCONV
debug_print('Reservation_qty before inserting (create)= '
|| To_char(l_rsv_rec.reservation_quantity) );
-- user should use update instead.
query_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_false
, x_return_status => l_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_true
, x_mtl_reservation_tbl => l_tmp_rsv_tbl
, x_mtl_reservation_tbl_count => l_tmp_rsv_tbl_count
, x_error_code => l_error_code
);
SELECT project_reference_enabled
INTO l_pjm_enabled
FROM mtl_parameters
WHERE organization_id = l_rsv_rec.organization_id;
SELECT project_id, task_id INTO l_project_id, l_task_id
FROM oe_order_lines_all WHERE
line_id = l_rsv_rec.demand_source_line_id;
-- Pre Insert CTO Validation
IF l_rsv_rec.demand_source_type_id IN (inv_reservation_global.g_source_type_oe, inv_reservation_global.g_source_type_internal_ord, inv_reservation_global.g_source_type_rma) THEN
--
cto_workflow_api_pk.inventory_reservation_check(p_order_line_id => l_rsv_rec.demand_source_line_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
/*** move to before insert_row
IF l_serial_number.COUNT > 0 THEN
l_rsv_rec.serial_reservation_quantity := l_serial_number.COUNT;
debug_print('Primary_reservation_qty before inserting (create)= '
|| To_char(l_rsv_rec.primary_reservation_quantity) );
debug_print('Secondary_reservation_qty before inserting (create)= '
|| To_char(l_rsv_rec.secondary_reservation_quantity) ); -- INVCONV
debug_print('Reservation_qty before inserting (create)= '
|| To_char(l_rsv_rec.reservation_quantity) );
--Bug 5535030 Selecting the sequence value has been moved to the table handler in
-- INVRSV6B.pls
/* -- create reservation id
SELECT mtl_demand_s.NEXTVAL
INTO l_reservation_id
FROM DUAL;*/
debug_print(' Before inserting record');
debug_print(' Create: Before inserting record');
mtl_reservations_pkg.insert_row
(
x_rowid => l_rowid
, x_reservation_id => l_reservation_id
, x_requirement_date => l_rsv_rec.requirement_date
, x_organization_id => l_rsv_rec.organization_id
, x_inventory_item_id => l_rsv_rec.inventory_item_id
, x_demand_source_type_id => l_rsv_rec.demand_source_type_id
, x_demand_source_name => l_rsv_rec.demand_source_name
, x_demand_source_header_id => l_rsv_rec.demand_source_header_id
, x_demand_source_line_id => l_rsv_rec.demand_source_line_id
, x_demand_source_delivery => l_rsv_rec.demand_source_delivery
, x_primary_uom_code => l_rsv_rec.primary_uom_code
, x_primary_uom_id => l_rsv_rec.primary_uom_id
, x_secondary_uom_code => l_rsv_rec.secondary_uom_code
, x_secondary_uom_id => l_rsv_rec.secondary_uom_id
, x_reservation_uom_code => l_rsv_rec.reservation_uom_code
, x_reservation_uom_id => l_rsv_rec.reservation_uom_id
, x_reservation_quantity => l_rsv_rec.reservation_quantity
, x_primary_reservation_quantity=> l_rsv_rec.primary_reservation_quantity
, x_second_reservation_quantity=> l_rsv_rec.secondary_reservation_quantity
, x_detailed_quantity => l_rsv_rec.detailed_quantity
, x_secondary_detailed_quantity=> l_rsv_rec.secondary_detailed_quantity
, x_autodetail_group_id => l_rsv_rec.autodetail_group_id
, x_external_source_code => l_rsv_rec.external_source_code
, x_external_source_line_id => l_rsv_rec.external_source_line_id
, x_supply_source_type_id => l_rsv_rec.supply_source_type_id
, x_supply_source_header_id => l_rsv_rec.supply_source_header_id
, x_supply_source_line_id => l_rsv_rec.supply_source_line_id
, x_supply_source_line_detail => l_rsv_rec.supply_source_line_detail
, x_supply_source_name => l_rsv_rec.supply_source_name
, x_revision => l_rsv_rec.revision
, x_subinventory_code => l_rsv_rec.subinventory_code
, x_subinventory_id => l_rsv_rec.subinventory_id
, x_locator_id => l_rsv_rec.locator_id
, x_lot_number => l_rsv_rec.lot_number
, x_lot_number_id => l_rsv_rec.lot_number_id
, x_serial_number => NULL
, x_serial_number_id => NULL
, x_partial_quantities_allowed => NULL
, x_auto_detailed => NULL
, x_pick_slip_number => l_rsv_rec.pick_slip_number
, x_lpn_id => l_rsv_rec.lpn_id
, x_last_update_date => l_date
, x_last_updated_by => l_user_id
, x_creation_date => l_date
, x_created_by => l_user_id
, x_last_update_login => l_login_id
, x_request_id => l_request_id
, x_program_application_id => l_prog_appl_id
, x_program_id => l_program_id
, x_program_update_date => l_date
, x_attribute_category => l_rsv_rec.attribute_category
, x_attribute1 => l_rsv_rec.attribute1
, x_attribute2 => l_rsv_rec.attribute2
, x_attribute3 => l_rsv_rec.attribute3
, x_attribute4 => l_rsv_rec.attribute4
, x_attribute5 => l_rsv_rec.attribute5
, x_attribute6 => l_rsv_rec.attribute6
, x_attribute7 => l_rsv_rec.attribute7
, x_attribute8 => l_rsv_rec.attribute8
, x_attribute9 => l_rsv_rec.attribute9
, x_attribute10 => l_rsv_rec.attribute10
, x_attribute11 => l_rsv_rec.attribute11
, x_attribute12 => l_rsv_rec.attribute12
, x_attribute13 => l_rsv_rec.attribute13
, x_attribute14 => l_rsv_rec.attribute14
, x_attribute15 => l_rsv_rec.attribute15
, x_ship_ready_flag => l_rsv_rec.ship_ready_flag
, x_staged_flag => l_rsv_rec.staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, x_crossdock_flag => l_rsv_rec.crossdock_flag
, x_crossdock_criteria_id => l_rsv_rec.crossdock_criteria_id
, x_demand_source_line_detail => l_rsv_rec.demand_source_line_detail
, x_serial_reservation_quantity => l_rsv_rec.serial_reservation_quantity
, x_supply_receipt_date => l_rsv_rec.supply_receipt_date
, x_demand_ship_date => l_rsv_rec.demand_ship_date
, x_project_id => l_rsv_rec.project_id
, x_task_id => l_rsv_rec.task_id
, x_orig_supply_type_id => l_rsv_rec.supply_source_type_id
, x_orig_supply_header_id => l_rsv_rec.supply_source_header_id
, x_orig_supply_line_id => l_rsv_rec.supply_source_line_id
, x_orig_supply_line_detail => l_rsv_rec.supply_source_line_detail
, x_orig_demand_type_id => l_rsv_rec.demand_source_type_id
, x_orig_demand_header_id => l_rsv_rec.demand_source_header_id
, x_orig_demand_line_id => l_rsv_rec.demand_source_line_id
, x_orig_demand_line_detail => l_rsv_rec.demand_source_line_detail
/*** End R12 ***/
);
debug_print(' After call to insert_row reservation_id = ' || l_reservation_id);
inv_rsv_synch.for_insert(p_reservation_id => l_reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
SELECT group_mark_id INTO l_group_mark_id FROM
mtl_serial_numbers WHERE
serial_number = l_serial_number(l_serial_index).serial_number AND
inventory_item_id = l_rsv_rec.inventory_item_id AND
current_organization_id = l_rsv_rec.organization_id;
debug_print('Errow while selecting the serial number. serial Number ' || l_serial_number(l_serial_index).serial_number);
UPDATE mtl_serial_numbers SET reservation_id =
l_reservation_id, group_mark_id =
l_reservation_id WHERE
serial_number = l_serial_number(l_serial_index).serial_number AND
inventory_item_id = l_rsv_rec.inventory_item_id AND
current_organization_id = l_rsv_rec.organization_id;
-- Post Insert CTO Validation
IF l_rsv_rec.demand_source_type_id IN (inv_reservation_global.g_source_type_oe, inv_reservation_global.g_source_type_internal_ord, inv_reservation_global.g_source_type_rma) THEN
--
cto_workflow_api_pk.wf_update_after_inv_reserv(p_order_line_id => l_rsv_rec.demand_source_line_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
insert_rsv_temp
(
p_organization_id => l_rsv_rec.organization_id
, p_inventory_item_id => l_rsv_rec.inventory_item_id
, p_primary_reservation_quantity=> l_rsv_rec.primary_reservation_quantity
, p_tree_id => l_tree_id
, p_reservation_id => l_reservation_id
, x_return_status => l_return_status
, p_demand_source_line_id => l_rsv_rec.demand_source_line_id
, p_demand_source_header_id => l_rsv_rec.demand_source_header_id
, p_demand_source_name => l_rsv_rec.demand_source_name
);
debug_print('After insert into rsv temp ' || l_return_status);
PROCEDURE update_reservation
(
p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
, p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
, p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
, p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
, p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
, p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
, p_over_reservation_flag IN NUMBER DEFAULT 0
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Reservation';
debug_print('Calling the overloaded procedure update_reservation');
inv_reservation_pvt.update_reservation
(p_api_version_number => 1.0,
p_init_msg_lst => fnd_api.g_false,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_quantity_reserved => l_quantity_reserved,
x_secondary_quantity_reserved => l_secondary_quantity_reserved,
p_original_rsv_rec => p_original_rsv_rec,
p_to_rsv_rec => p_to_rsv_rec,
p_original_serial_number => p_original_serial_number ,
p_to_serial_number => p_to_serial_number,
p_validation_flag => p_validation_flag,
p_partial_reservation_flag => fnd_api.g_false,
p_check_availability => p_check_availability,
p_over_reservation_flag => p_over_reservation_flag
);
END update_reservation;
PROCEDURE update_reservation
(
p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_quantity_reserved OUT NOCOPY NUMBER
, x_secondary_quantity_reserved OUT NOCOPY NUMBER
, p_original_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
, p_to_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
, p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
, p_to_serial_number IN inv_reservation_global.serial_number_tbl_type
, p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
, p_partial_reservation_flag IN VARCHAR2 DEFAULT fnd_api.g_false
, p_check_availability IN VARCHAR2 DEFAULT fnd_api.g_false
, p_over_reservation_flag IN NUMBER DEFAULT 0
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Update_Reservation';
block and there we have 'Rollback to update_reservation_sa' statement which was causing issue.
*/
SAVEPOINT update_reservation_sa;
debug_print('Inside update reservation...');
debug_print('Primary_reservation_qty before inserting (update)= '
|| To_char(p_to_rsv_rec.primary_reservation_quantity));
debug_print('Secondary_reservation_qty before inserting (update)= '
|| To_char(p_to_rsv_rec.secondary_reservation_quantity)); -- INVCONV
debug_print('Reservation_qty before inserting (update)= '
|| To_char(p_to_rsv_rec.reservation_quantity));
delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => p_init_msg_lst
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_original_rsv_rec
, p_original_serial_number => p_original_serial_number
, p_validation_flag => p_validation_flag
);
debug_print('Cannot update reservation ID');
fnd_message.set_name('INV', 'CANNOT_UPDATE_RESERVATION_ID');
debug_print('Cannot update organization ID');
fnd_message.set_name('INV', 'CANNOT_UPDATE_ORGANIZATION_ID');
debug_print('Cannot update Inventory Item ID');
fnd_message.set_name('INV', 'CANNOT_UPDATE_INVENTORY_ITEM');
SELECT project_reference_enabled
INTO l_pjm_enabled
FROM mtl_parameters
WHERE organization_id = l_to_rsv_rec.organization_id;
SELECT project_id, task_id INTO l_project_id, l_task_id
FROM oe_order_lines_all WHERE
line_id = l_to_rsv_rec.demand_source_line_id;
, p_rsv_action_name => 'UPDATE'
, x_orig_item_cache_index => l_orig_item_cache_index
, x_orig_org_cache_index => l_orig_org_cache_index
, x_orig_demand_cache_index => l_orig_demand_cache_index
, x_orig_supply_cache_index => l_orig_supply_cache_index
, x_orig_sub_cache_index => l_orig_sub_cache_index
, x_to_item_cache_index => l_to_item_cache_index
, x_to_org_cache_index => l_to_org_cache_index
, x_to_demand_cache_index => l_to_demand_cache_index
, x_to_supply_cache_index => l_to_supply_cache_index
, x_to_sub_cache_index => l_to_sub_cache_index
);
debug_print('Update scenario and lot divisible is '||l_lot_divisible_flag);
* would have different tree_ids. We don't need to update the same
* tree twice, so we don't need to create the same tree twice.
* Instead, pass NULL to the modify_tree procedure.
*inv_quantity_tree_pvt.create_tree
* (
* p_api_version_number => 1.0
* , p_init_msg_lst => fnd_api.g_true
* , x_return_status => l_return_status
* , x_msg_count => x_msg_count
* , x_msg_data => x_msg_data
* , p_organization_id => l_to_rsv_rec.organization_id
* , p_inventory_item_id => l_to_rsv_rec.inventory_item_id
* , p_tree_mode => inv_quantity_tree_pvt.g_reservation_mode
* , p_is_revision_control => is_revision_control(l_to_item_cache_index)
* , p_is_lot_control => is_lot_control(l_to_item_cache_index)
* , p_is_serial_control => is_serial_control(l_to_item_cache_index)
* , p_asset_sub_only => FALSE
* , p_include_suggestion => TRUE
* , p_demand_source_type_id => l_to_rsv_rec.demand_source_type_id
* , p_demand_source_header_id => l_to_rsv_rec.demand_source_header_id
* , p_demand_source_line_id => l_to_rsv_rec.demand_source_line_id
* , p_demand_source_name => l_to_rsv_rec.demand_source_name
* , p_demand_source_delivery => l_to_rsv_rec.demand_source_delivery
* , p_lot_expiration_date => NULL
* , x_tree_id => l_tree_id2
*);
modify_tree_for_update_xfer(
x_return_status => l_return_status
, x_quantity_reserved => l_quantity_reserved
, x_secondary_quantity_reserved => l_secondary_quantity_reserved
, p_from_tree_id => l_tree_id1
, p_from_supply_source_type_id => l_orig_rsv_tbl(1).supply_source_type_id
, p_from_revision => l_orig_rsv_tbl(1).revision
, p_from_lot_number => l_orig_rsv_tbl(1).lot_number
, p_from_subinventory_code => l_orig_rsv_tbl(1).subinventory_code
, p_from_locator_id => l_orig_rsv_tbl(1).locator_id
, p_from_lpn_id => l_orig_rsv_tbl(1).lpn_id
, p_from_primary_rsv_quantity => l_orig_rsv_tbl(1).primary_reservation_quantity
, p_from_second_rsv_quantity => l_orig_rsv_tbl(1).secondary_reservation_quantity
, p_from_detailed_quantity => l_orig_rsv_tbl(1).detailed_quantity
, p_from_sec_detailed_quantity => l_orig_rsv_tbl(1).secondary_detailed_quantity
, p_to_tree_id => l_tree_id2
, p_to_supply_source_type_id => l_to_rsv_rec.supply_source_type_id
, p_to_revision => l_to_rsv_rec.revision
, p_to_lot_number => l_to_rsv_rec.lot_number
, p_to_subinventory_code => l_to_rsv_rec.subinventory_code
, p_to_locator_id => l_to_rsv_rec.locator_id
, p_to_lpn_id => l_to_rsv_rec.lpn_id
, p_to_primary_rsv_quantity => l_to_rsv_rec.primary_reservation_quantity
, p_to_second_rsv_quantity => l_to_rsv_rec.secondary_reservation_quantity
, p_to_detailed_quantity => l_to_rsv_rec.detailed_quantity
, p_to_second_detailed_quantity => l_to_rsv_rec.secondary_detailed_quantity
, p_to_revision_control => is_revision_control(l_to_item_cache_index)
, p_to_lot_control => is_lot_control(l_to_item_cache_index)
, p_action => 'UPDATE'
, p_lot_divisible_flag => l_lot_divisible_flag -- INVCONV
, p_partial_reservation_flag => p_partial_reservation_flag
, p_check_availability => p_check_availability
);
debug_print('modify_tree_for_update_xfer returned error');
debug_print('modify_tree_for_update_xfer returned unexpected error');
debug_print('modify_tree_for_update_xfer returned success ');
SELECT open_flag, booked_flag
INTO l_open_flag,
l_booked_flag
FROM oe_order_lines_all
WHERE line_id = l_to_rsv_rec.demand_source_line_id;
debug_print('Pre Update CTO validation');
debug_print('Pre Update CTO validation returned error');
debug_print('Pre Update CTO validation returned unexpected error');
debug_print('Primary_reservation_qty before inserting (update)= '
|| To_char(l_to_rsv_rec.primary_reservation_quantity));
debug_print('Secondary_reservation_qty before inserting (update)= '
|| To_char(l_to_rsv_rec.secondary_reservation_quantity));
debug_print('Reservation_qty before inserting (update)= '
|| To_char(l_to_rsv_rec.reservation_quantity)); -- INVCONV
SELECT inventory_item_id, serial_number bulk collect INTO
l_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
, p_rsv_action_name => 'UPDATE'
, p_orig_rsv_rec => l_dummy_rsv_rec
, p_to_rsv_rec => l_to_rsv_rec
, p_orig_serial_array => l_dummy_serial_array
, p_to_serial_array => l_serial_number_table
);
UPDATE mtl_serial_numbers SET reservation_id = NULL,
group_mark_id = NULL, line_mark_id = NULL,
lot_line_mark_id = NULL WHERE
serial_number = l_serial_number_table(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
-- update the serial reservation quantity to be the primary
-- reservation quantity, as it is in excess.
l_to_rsv_rec.serial_reservation_quantity := l_to_rsv_rec.primary_reservation_quantity;
UPDATE mtl_serial_numbers SET reservation_id = l_orig_rsv_tbl(1).reservation_id,
group_mark_id = l_orig_rsv_tbl(1).reservation_id WHERE
serial_number = l_serial_number_table(l_serial_index).serial_number
AND current_organization_id = l_orig_rsv_tbl(1).organization_id
AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
-- update the serial reservation quantity
l_to_rsv_rec.serial_reservation_quantity := l_total_serials_reserved;
SELECT reservation_id, group_mark_id INTO
l_reservation_id,l_group_mark_id
FROM mtl_serial_numbers WHERE
serial_number = p_to_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = NULL,
group_mark_id = NULL, line_mark_id = NULL,
lot_line_mark_id = NULL WHERE
serial_number = p_original_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = l_orig_rsv_tbl(1).reservation_id,
group_mark_id = l_orig_rsv_tbl(1).reservation_id WHERE
serial_number = p_to_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT COUNT(1) INTO l_total_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT inventory_item_id, serial_number bulk collect INTO
l_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = NULL,
group_mark_id = NULL, line_mark_id = NULL,
lot_line_mark_id = NULL WHERE
serial_number = l_serial_number_table(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
-- update the serial reservation quantity to be the primary
-- reservation quantity, as it is in excess.
l_to_rsv_rec.serial_reservation_quantity := l_to_rsv_rec.primary_reservation_quantity;
debug_print('Calling mtl_reservations_pkg.update_row');
debug_print(' Update: Before updating record');
mtl_reservations_pkg.update_row
(
x_reservation_id => l_orig_rsv_tbl(1).reservation_id
, x_requirement_date => l_to_rsv_rec.requirement_date
, x_organization_id => l_to_rsv_rec.organization_id
, x_inventory_item_id => l_to_rsv_rec.inventory_item_id
, x_demand_source_type_id => l_to_rsv_rec.demand_source_type_id
, x_demand_source_name => l_to_rsv_rec.demand_source_name
, x_demand_source_header_id => l_to_rsv_rec.demand_source_header_id
, x_demand_source_line_id => l_to_rsv_rec.demand_source_line_id
, x_demand_source_delivery => l_to_rsv_rec.demand_source_delivery
, x_primary_uom_code => l_to_rsv_rec.primary_uom_code
, x_primary_uom_id => l_to_rsv_rec.primary_uom_id
, x_secondary_uom_code => l_to_rsv_rec.secondary_uom_code
, x_secondary_uom_id => l_to_rsv_rec.secondary_uom_id
, x_reservation_uom_code => l_to_rsv_rec.reservation_uom_code
, x_reservation_uom_id => l_to_rsv_rec.reservation_uom_id
, x_reservation_quantity => l_to_rsv_rec.reservation_quantity
, x_primary_reservation_quantity=> l_to_rsv_rec.primary_reservation_quantity
, x_second_reservation_quantity=> l_to_rsv_rec.secondary_reservation_quantity
, x_detailed_quantity => l_to_rsv_rec.detailed_quantity
, x_secondary_detailed_quantity=> l_to_rsv_rec.secondary_detailed_quantity
, x_autodetail_group_id => l_to_rsv_rec.autodetail_group_id
, x_external_source_code => l_to_rsv_rec.external_source_code
, x_external_source_line_id => l_to_rsv_rec.external_source_line_id
, x_supply_source_type_id => l_to_rsv_rec.supply_source_type_id
, x_supply_source_header_id => l_to_rsv_rec.supply_source_header_id
, x_supply_source_line_id => l_to_rsv_rec.supply_source_line_id
, x_supply_source_line_detail => l_to_rsv_rec.supply_source_line_detail
, x_supply_source_name => l_to_rsv_rec.supply_source_name
, x_revision => l_to_rsv_rec.revision
, x_subinventory_code => l_to_rsv_rec.subinventory_code
, x_subinventory_id => l_to_rsv_rec.subinventory_id
, x_locator_id => l_to_rsv_rec.locator_id
, x_lot_number => l_to_rsv_rec.lot_number
, x_lot_number_id => l_to_rsv_rec.lot_number_id
, x_serial_number => NULL
, x_serial_number_id => NULL
, x_partial_quantities_allowed => NULL
, x_auto_detailed => NULL
, x_pick_slip_number => l_to_rsv_rec.pick_slip_number
, x_lpn_id => l_to_rsv_rec.lpn_id
, x_last_update_date => l_date
, x_last_updated_by => l_user_id
, x_last_update_login => l_login_id
, x_request_id => l_request_id
, x_program_application_id => l_prog_appl_id
, x_program_id => l_program_id
, x_program_update_date => l_date
, x_attribute_category => l_to_rsv_rec.attribute_category
, x_attribute1 => l_to_rsv_rec.attribute1
, x_attribute2 => l_to_rsv_rec.attribute2
, x_attribute3 => l_to_rsv_rec.attribute3
, x_attribute4 => l_to_rsv_rec.attribute4
, x_attribute5 => l_to_rsv_rec.attribute5
, x_attribute6 => l_to_rsv_rec.attribute6
, x_attribute7 => l_to_rsv_rec.attribute7
, x_attribute8 => l_to_rsv_rec.attribute8
, x_attribute9 => l_to_rsv_rec.attribute9
, x_attribute10 => l_to_rsv_rec.attribute10
, x_attribute11 => l_to_rsv_rec.attribute11
, x_attribute12 => l_to_rsv_rec.attribute12
, x_attribute13 => l_to_rsv_rec.attribute13
, x_attribute14 => l_to_rsv_rec.attribute14
, x_attribute15 => l_to_rsv_rec.attribute15
, x_ship_ready_flag => l_to_rsv_rec.ship_ready_flag
, x_staged_flag => l_to_rsv_rec.staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, x_crossdock_flag => l_to_rsv_rec.crossdock_flag
, x_crossdock_criteria_id => l_to_rsv_rec.crossdock_criteria_id
, x_demand_source_line_detail => l_to_rsv_rec.demand_source_line_detail
, x_serial_reservation_quantity => l_to_rsv_rec.serial_reservation_quantity
, x_supply_receipt_date => l_to_rsv_rec.supply_receipt_date
, x_demand_ship_date => l_to_rsv_rec.demand_ship_date
, x_project_id => l_to_rsv_rec.project_id
, x_task_id => l_to_rsv_rec.task_id
/*** End R12 ***/
);
debug_print('Calling inv_rsv_synch.for_update');
inv_rsv_synch.for_update(p_reservation_id => l_orig_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
debug_print('Calling cto_workflow_api_pk.wf_update_after_inv_unreserv');
cto_workflow_api_pk.wf_update_after_inv_unreserv
(
p_order_line_id => l_to_rsv_rec.demand_source_line_id
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
debug_print('Calling cto_workflow_api_pk.wf_update_after_inv_reserv');
cto_workflow_api_pk.wf_update_after_inv_reserv
(
p_order_line_id => l_to_rsv_rec.demand_source_line_id
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
ROLLBACK TO update_reservation_sa;
ROLLBACK TO update_reservation_sa;
ROLLBACK TO update_reservation_sa;
END update_reservation;
debug_print('calling inv_rsv_synch.for_delete');
inv_rsv_synch.for_delete
(
p_reservation_id => l_reservation_id
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
debug_print('error calling inv_rsv_synch.for_delete');
debug_print('unexpected error calling inv_rsv_synch.for_delete');
update mtl_serial_numbers
set reservation_id = NULL,
group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
where reservation_id = l_reservation_id;
debug_print('calling mtl_reservations_pkg.delete_row');
mtl_reservations_pkg.delete_row(x_reservation_id => l_reservation_id);
debug_print('Primary_reservation_qty before inserting (relieve)= '
|| To_char(l_tmp_rsv_tbl(1).primary_reservation_quantity) );
debug_print('Secondary_reservation_qty before inserting (relieve)= '
|| To_char(l_tmp_rsv_tbl(1).secondary_reservation_quantity) );
debug_print('Reservation_qty before inserting (relieve)= '
|| To_char(l_tmp_rsv_tbl(1).reservation_quantity) );
UPDATE mtl_serial_numbers
SET reservation_id = NULL,
group_mark_id = NULL, line_mark_id = NULL,
lot_line_mark_id = NULL
WHERE serial_number = p_original_serial_number(i).serial_number
AND inventory_item_id =
l_tmp_rsv_tbl(1).inventory_item_id
AND current_organization_id = l_tmp_rsv_tbl(1).organization_id;
SELECT inventory_item_id,serial_number BULK COLLECT
INTO l_serial_number_table
FROM mtl_serial_numbers
WHERE reservation_id = l_reservation_id;
UPDATE mtl_serial_numbers
SET reservation_id = NULL,
group_mark_id = NULL, line_mark_id = NULL,
lot_line_mark_id = NULL
WHERE serial_number = l_serial_number_table(i).serial_number
AND inventory_item_id =
l_serial_number_table(i).inventory_item_id AND
current_organization_id = l_tmp_rsv_tbl(1).organization_id;
SELECT inventory_item_id, serial_number BULK COLLECT
INTO l_serial_number_table
FROM mtl_serial_numbers
WHERE reservation_id = l_reservation_id;
UPDATE mtl_serial_numbers
SET reservation_id = NULL,
group_mark_id = NULL, line_mark_id = NULL,
lot_line_mark_id = NULL
WHERE serial_number = l_serial_number_table(i).serial_number
AND inventory_item_id = l_serial_number_table(i).inventory_item_id;
debug_print('update reservation');
UPDATE mtl_reservations
SET primary_reservation_quantity = l_tmp_rsv_tbl(1).primary_reservation_quantity
, secondary_reservation_quantity = l_tmp_rsv_tbl(1).secondary_reservation_quantity
, reservation_quantity = l_tmp_rsv_tbl(1).reservation_quantity
, last_update_date = l_date
, last_updated_by = l_user_id
, last_update_login = l_login_id
, request_id = l_request_id
, program_application_id = l_prog_appl_id
, program_id = l_program_id
, program_update_date = l_date
/*** {{ R12 Enhanced reservations code changes ***/
, serial_reservation_quantity =
l_tmp_rsv_tbl(1).serial_reservation_quantity
/*** End R12 }} ***/
WHERE reservation_id = l_reservation_id;
PROCEDURE delete_reservation(
p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_rsv_rec IN inv_reservation_global.mtl_reservation_rec_type
, p_original_serial_number IN inv_reservation_global.serial_number_tbl_type
, p_validation_flag IN VARCHAR2 DEFAULT fnd_api.g_true
) IS
l_api_version_number CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Reservation';
debug_print('Inside delete reservation...');
debug_print('No of rows to be deleted '|| l_tmp_rsv_tbl_count);
fnd_message.set_name('INV', 'INV-DELETE MORE THAN ONE ROW');
, p_rsv_action_name => 'DELETE'
, x_orig_item_cache_index => l_orig_item_cache_index
, x_orig_org_cache_index => l_orig_org_cache_index
, x_orig_demand_cache_index => l_orig_demand_cache_index
, x_orig_supply_cache_index => l_orig_supply_cache_index
, x_orig_sub_cache_index => l_orig_sub_cache_index
, x_to_item_cache_index => l_to_item_cache_index
, x_to_org_cache_index => l_to_org_cache_index
, x_to_demand_cache_index => l_to_demand_cache_index
, x_to_supply_cache_index => l_to_supply_cache_index
, x_to_sub_cache_index => l_to_sub_cache_index
);
, p_action => 'DELETE'
, x_qty_changed => l_qty_changed
, x_secondary_qty_changed => l_secondary_qty_changed
);
inv_rsv_synch.for_delete(p_reservation_id => l_reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
update mtl_serial_numbers
set reservation_id = NULL,
group_mark_id = NULL, line_mark_id = NULL,
lot_line_mark_id = NULL
where reservation_id = l_reservation_id;
mtl_reservations_pkg.delete_row(x_reservation_id => l_reservation_id);
cto_workflow_api_pk.wf_update_after_inv_unreserv(
p_order_line_id => l_tmp_rsv_tbl(1).demand_source_line_id
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
debug_print('After calling wf_update_after_inv_unreserv...' || l_return_status);
END delete_reservation;
SELECT project_reference_enabled
INTO l_pjm_enabled
FROM mtl_parameters
WHERE organization_id = l_to_rsv_rec.organization_id;
SELECT project_id, task_id INTO l_project_id, l_task_id
FROM oe_order_lines_all WHERE
line_id = l_to_rsv_rec.demand_source_line_id;
modify_tree_for_update_xfer
(
x_return_status => l_return_status
, x_quantity_reserved => l_quantity_reserved
, x_secondary_quantity_reserved => l_secondary_quantity_reserved
, p_from_tree_id => l_tree_id1
, p_from_supply_source_type_id => l_orig_rsv_tbl(1).supply_source_type_id
, p_from_revision => l_orig_rsv_tbl(1).revision
, p_from_lot_number => l_orig_rsv_tbl(1).lot_number
, p_from_subinventory_code => l_orig_rsv_tbl(1).subinventory_code
, p_from_locator_id => l_orig_rsv_tbl(1).locator_id
, p_from_lpn_id => l_orig_rsv_tbl(1).lpn_id
, p_from_primary_rsv_quantity => l_orig_rsv_tbl(1).primary_reservation_quantity
, p_from_second_rsv_quantity => l_orig_rsv_tbl(1).secondary_reservation_quantity
, p_from_detailed_quantity => l_orig_rsv_tbl(1).detailed_quantity
, p_from_sec_detailed_quantity => l_orig_rsv_tbl(1).secondary_detailed_quantity
, p_to_tree_id => l_tree_id2
, p_to_supply_source_type_id => l_to_rsv_rec.supply_source_type_id
, p_to_revision => l_to_rsv_rec.revision
, p_to_lot_number => l_to_rsv_rec.lot_number
, p_to_subinventory_code => l_to_rsv_rec.subinventory_code
, p_to_locator_id => l_to_rsv_rec.locator_id
, p_to_lpn_id => l_to_rsv_rec.lpn_id
, p_to_primary_rsv_quantity => l_to_rsv_rec.primary_reservation_quantity
, p_to_second_rsv_quantity => l_to_rsv_rec.secondary_reservation_quantity
, p_to_detailed_quantity => l_to_rsv_rec.detailed_quantity
, p_to_second_detailed_quantity => l_to_rsv_rec.secondary_detailed_quantity
, p_to_revision_control => is_revision_control(l_to_item_cache_index)
, p_to_lot_control => is_lot_control(l_to_item_cache_index)
, p_action => 'TRANSFER'
, p_lot_divisible_flag => l_lot_divisible_flag -- INVCONV
, p_partial_reservation_flag => fnd_api.g_false
, p_check_availability => fnd_api.g_false
);
** Include detailed quantity too in the update...
*/
IF (l_orig_rsv_tbl(1).detailed_quantity IS NULL) THEN
l_detailed_quantity := NULL;
debug_print('Primary_reservation_qty before inserting (xfer)= '
|| To_char(l_new_orig_prim_qty) );
debug_print('Secondary_reservation_qty before inserting (xfer)= '
|| To_char(l_orig_second_rsv_qty) ); -- INVCONV
debug_print('Reservation_qty before inserting (xfer)= '
|| To_char(l_new_orig_rsv_qty) );
UPDATE mtl_reservations
SET primary_reservation_quantity = l_new_orig_prim_qty
, secondary_reservation_quantity = l_orig_second_rsv_qty
, reservation_quantity = l_new_orig_rsv_qty
, detailed_quantity = l_detailed_quantity
, last_update_date = l_date
, last_updated_by = l_user_id
, last_update_login = l_login_id
, request_id = l_request_id
, program_application_id = l_prog_appl_id
, program_id = l_program_id
, program_update_date = l_date
WHERE reservation_id = l_orig_rsv_tbl(1).reservation_id;
inv_rsv_synch.for_update(p_reservation_id => l_orig_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
* update to the workflow that was causing problems.
* Bug 2073768
* This code was not interacting correctly with CTO.
* No longer call the wf_update_after_inv_unreserv api from
* transfer_reservation
*-- Post Update CTO Validation
* IF l_orig_rsv_tbl(1).demand_source_type_id in (
* inv_reservation_global.g_source_type_oe
* ,inv_reservation_global.g_source_type_internal_ord
* ,inv_reservation_global.g_source_type_rma) THEN
* --
* cto_workflow_api_pk.wf_update_after_inv_unreserv(
* p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
* , x_return_status => l_return_status
* , x_msg_count => x_msg_count
* , x_msg_data => x_msg_data
* );
inv_rsv_synch.for_delete(p_reservation_id => l_orig_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
mtl_reservations_pkg.delete_row
(x_reservation_id => l_orig_rsv_tbl(1).reservation_id
,x_to_reservation_id => l_to_rsv_tbl(1).reservation_id);
* No longer call the wf_update_after_inv_unreserv api from
* transfer_reservation
*-- Post Delete CTO Validation
* IF l_orig_rsv_tbl(1).demand_source_type_id in (
* inv_reservation_global.g_source_type_oe
* ,inv_reservation_global.g_source_type_internal_ord
* ,inv_reservation_global.g_source_type_rma) THEN
* --
* cto_workflow_api_pk.wf_update_after_inv_unreserv(
* p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
* , x_return_status => l_return_status
* , x_msg_count => x_msg_count
* , x_msg_data => x_msg_data
* );
debug_print('Primary_reservation_qty before inserting (xfer)= '
|| To_char(l_to_rsv_rec.primary_reservation_quantity) );
debug_print('Secondary_reservation_qty before inserting (xfer)= '
|| To_char(l_to_rsv_rec.secondary_reservation_quantity) ); -- INVCONV
debug_print('Reservation_qty before inserting (xfer)= '
|| To_char(l_to_rsv_rec.reservation_quantity) );
mtl_reservations_pkg.update_row
(
x_reservation_id => l_orig_rsv_tbl(1).reservation_id
, x_requirement_date => l_to_rsv_rec.requirement_date
, x_organization_id => l_to_rsv_rec.organization_id
, x_inventory_item_id => l_to_rsv_rec.inventory_item_id
, x_demand_source_type_id => l_to_rsv_rec.demand_source_type_id
, x_demand_source_header_id => l_to_rsv_rec.demand_source_header_id
, x_demand_source_line_id => l_to_rsv_rec.demand_source_line_id
, x_demand_source_name => l_to_rsv_rec.demand_source_name
, x_demand_source_delivery => l_to_rsv_rec.demand_source_delivery
, x_primary_uom_code => l_to_rsv_rec.primary_uom_code
, x_primary_uom_id => l_to_rsv_rec.primary_uom_id
, x_secondary_uom_code => l_to_rsv_rec.secondary_uom_code
, x_secondary_uom_id => l_to_rsv_rec.secondary_uom_id
, x_reservation_uom_code => l_to_rsv_rec.reservation_uom_code
, x_reservation_uom_id => l_to_rsv_rec.reservation_uom_id
, x_reservation_quantity => l_to_rsv_rec.reservation_quantity
, x_primary_reservation_quantity=> l_to_rsv_rec.primary_reservation_quantity
, x_second_reservation_quantity=> l_to_rsv_rec.secondary_reservation_quantity
, x_detailed_quantity => l_to_rsv_rec.detailed_quantity
, x_secondary_detailed_quantity=> l_to_rsv_rec.secondary_detailed_quantity
, x_autodetail_group_id => l_to_rsv_rec.autodetail_group_id
, x_external_source_code => l_to_rsv_rec.external_source_code
, x_external_source_line_id => l_to_rsv_rec.external_source_line_id
, x_supply_source_type_id => l_to_rsv_rec.supply_source_type_id
, x_supply_source_header_id => l_to_rsv_rec.supply_source_header_id
, x_supply_source_line_id => l_to_rsv_rec.supply_source_line_id
, x_supply_source_name => l_to_rsv_rec.supply_source_name
, x_supply_source_line_detail => l_to_rsv_rec.supply_source_line_detail
, x_revision => l_to_rsv_rec.revision
, x_subinventory_code => l_to_rsv_rec.subinventory_code
, x_subinventory_id => l_to_rsv_rec.subinventory_id
, x_locator_id => l_to_rsv_rec.locator_id
, x_lot_number => l_to_rsv_rec.lot_number
, x_lot_number_id => l_to_rsv_rec.lot_number_id
, x_serial_number => NULL
, x_serial_number_id => NULL
, x_partial_quantities_allowed => NULL
, x_auto_detailed => NULL
, x_pick_slip_number => l_to_rsv_rec.pick_slip_number
, x_lpn_id => l_to_rsv_rec.lpn_id
, x_last_update_date => l_date
, x_last_updated_by => l_user_id
, x_last_update_login => l_login_id
, x_request_id => l_request_id
, x_program_application_id => l_prog_appl_id
, x_program_id => l_program_id
, x_program_update_date => l_date
, x_attribute_category => l_to_rsv_rec.attribute_category
, x_attribute1 => l_to_rsv_rec.attribute1
, x_attribute2 => l_to_rsv_rec.attribute2
, x_attribute3 => l_to_rsv_rec.attribute3
, x_attribute4 => l_to_rsv_rec.attribute4
, x_attribute5 => l_to_rsv_rec.attribute5
, x_attribute6 => l_to_rsv_rec.attribute6
, x_attribute7 => l_to_rsv_rec.attribute7
, x_attribute8 => l_to_rsv_rec.attribute8
, x_attribute9 => l_to_rsv_rec.attribute9
, x_attribute10 => l_to_rsv_rec.attribute10
, x_attribute11 => l_to_rsv_rec.attribute11
, x_attribute12 => l_to_rsv_rec.attribute12
, x_attribute13 => l_to_rsv_rec.attribute13
, x_attribute14 => l_to_rsv_rec.attribute14
, x_attribute15 => l_to_rsv_rec.attribute15
, x_ship_ready_flag => l_to_rsv_rec.ship_ready_flag
, x_staged_flag => l_to_rsv_rec.staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, x_crossdock_flag => l_to_rsv_rec.crossdock_flag
, x_crossdock_criteria_id => l_to_rsv_rec.crossdock_criteria_id
, x_demand_source_line_detail => l_to_rsv_rec.demand_source_line_detail
, x_serial_reservation_quantity => l_to_rsv_rec.serial_reservation_quantity
, x_supply_receipt_date => l_to_rsv_rec.supply_receipt_date
, x_demand_ship_date => l_to_rsv_rec.demand_ship_date
, x_project_id => l_to_rsv_rec.project_id
, x_task_id => l_to_rsv_rec.task_id
/*** End R12 ***/
);
inv_rsv_synch.for_update(p_reservation_id => l_orig_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
cto_workflow_api_pk.wf_update_after_inv_unreserv(
p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
* No longer call the wf_update_after_inv_unreserv api from
* transfer_reservation
*-- Post Update CTO Validation
* IF l_orig_rsv_tbl(1).demand_source_type_id in (
* inv_reservation_global.g_source_type_oe
* ,inv_reservation_global.g_source_type_internal_ord
* ,inv_reservation_global.g_source_type_rma) THEN
* --
* cto_workflow_api_pk.wf_update_after_inv_unreserv(
* p_order_line_id => l_orig_rsv_tbl(1).demand_source_line_id
* , x_return_status => l_return_status
* , x_msg_count => x_msg_count
* , x_msg_data => x_msg_data
* );
SELECT primary_reservation_quantity, secondary_reservation_quantity, reservation_quantity
INTO l_primary_rsv_quantity, l_secondary_rsv_quantity, l_rsv_quantity
FROM mtl_reservations
WHERE reservation_id = l_to_rsv_tbl(1).reservation_id;
debug_print('Primary_reservation_qty before inserting (xfer)= ' || To_char(l_primary_rsv_quantity) );
debug_print('Secondary_reservation_qty before inserting (xfer)= ' || To_char(l_secondary_rsv_quantity) ); --INVCONV
debug_print('Rreservation_qty before inserting (xfer)= ' ||To_char(l_rsv_quantity));
UPDATE mtl_reservations
SET primary_reservation_quantity = Round((primary_reservation_quantity + l_to_rsv_rec.primary_reservation_quantity),5)
, secondary_reservation_quantity = Round((secondary_reservation_quantity + l_to_rsv_rec.secondary_reservation_quantity),5)
, reservation_quantity = Round((reservation_quantity + l_to_rsv_rec.reservation_quantity),5)
, detailed_quantity = Round(NVL(detailed_quantity, 0) + NVL(l_to_rsv_rec.detailed_quantity, 0),5)
, last_update_date = l_date
, last_updated_by = l_user_id
, last_update_login = l_login_id
, request_id = l_request_id
, program_application_id = l_prog_appl_id
, program_id = l_program_id
, program_update_date = l_date
WHERE reservation_id = l_to_rsv_tbl(1).reservation_id;
inv_rsv_synch.for_update(p_reservation_id => l_to_rsv_tbl(1).reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
* No longer call the wf_update_after_inv_unreserv api from
* transfer_reservation
*-- Post Update CTO Validation
* IF l_to_rsv_rec.demand_source_type_id in (
* inv_reservation_global.g_source_type_oe
* ,inv_reservation_global.g_source_type_internal_ord
* ,inv_reservation_global.g_source_type_rma) THEN
* --
* cto_workflow_api_pk.wf_update_after_inv_unreserv(
* p_order_line_id => l_to_rsv_rec.demand_source_line_id
* , x_return_status => l_return_status
* , x_msg_count => x_msg_count
* , x_msg_data => x_msg_data
* );
debug_print('Primary_reservation_qty before inserting (xfer)= '
|| To_char(l_to_rsv_rec.primary_reservation_quantity) );
debug_print('Secondary_reservation_qty before inserting (xfer)= '
|| To_char(l_to_rsv_rec.secondary_reservation_quantity) ); --INVCONV
debug_print('Reservation_qty before inserting (xfer)= '
|| To_char(l_to_rsv_rec.reservation_quantity) );
/* SELECT mtl_demand_s.NEXTVAL
INTO l_reservation_id
FROM DUAL; */
-- Bug 55350300 --Selecting the sequence value has been moved to the table handler
-- in INVRSV6B.pls
l_reservation_id := NULL;
mtl_reservations_pkg.insert_row
(
x_rowid => l_rowid
, x_reservation_id => l_reservation_id
, x_requirement_date => l_to_rsv_rec.requirement_date
, x_organization_id => l_to_rsv_rec.organization_id
, x_inventory_item_id => l_to_rsv_rec.inventory_item_id
, x_demand_source_type_id => l_to_rsv_rec.demand_source_type_id
, x_demand_source_name => l_to_rsv_rec.demand_source_name
, x_demand_source_header_id => l_to_rsv_rec.demand_source_header_id
, x_demand_source_line_id => l_to_rsv_rec.demand_source_line_id
, x_demand_source_delivery => l_to_rsv_rec.demand_source_delivery
, x_primary_uom_code => l_to_rsv_rec.primary_uom_code
, x_primary_uom_id => l_to_rsv_rec.primary_uom_id
, x_secondary_uom_code => l_to_rsv_rec.secondary_uom_code
, x_secondary_uom_id => l_to_rsv_rec.secondary_uom_id
, x_reservation_uom_code => l_to_rsv_rec.reservation_uom_code
, x_reservation_uom_id => l_to_rsv_rec.reservation_uom_id
, x_reservation_quantity => l_to_rsv_rec.reservation_quantity
, x_primary_reservation_quantity=> l_to_rsv_rec.primary_reservation_quantity
, x_second_reservation_quantity=> l_to_rsv_rec.secondary_reservation_quantity
, x_detailed_quantity => l_to_rsv_rec.detailed_quantity
, x_secondary_detailed_quantity => l_to_rsv_rec.secondary_detailed_quantity
, x_autodetail_group_id => l_to_rsv_rec.autodetail_group_id
, x_external_source_code => l_to_rsv_rec.external_source_code
, x_external_source_line_id => l_to_rsv_rec.external_source_line_id
, x_supply_source_type_id => l_to_rsv_rec.supply_source_type_id
, x_supply_source_header_id => l_to_rsv_rec.supply_source_header_id
, x_supply_source_line_id => l_to_rsv_rec.supply_source_line_id
, x_supply_source_line_detail => l_to_rsv_rec.supply_source_line_detail
, x_supply_source_name => l_to_rsv_rec.supply_source_name
, x_revision => l_to_rsv_rec.revision
, x_subinventory_code => l_to_rsv_rec.subinventory_code
, x_subinventory_id => l_to_rsv_rec.subinventory_id
, x_locator_id => l_to_rsv_rec.locator_id
, x_lot_number => l_to_rsv_rec.lot_number
, x_lot_number_id => l_to_rsv_rec.lot_number_id
, x_serial_number => NULL
, x_serial_number_id => NULL
, x_partial_quantities_allowed => NULL
, x_auto_detailed => NULL
, x_pick_slip_number => l_to_rsv_rec.pick_slip_number
, x_lpn_id => l_to_rsv_rec.lpn_id
, x_last_update_date => l_date
, x_last_updated_by => l_user_id
, x_creation_date => l_date
, x_created_by => l_user_id
, x_last_update_login => l_login_id
, x_request_id => l_request_id
, x_program_application_id => l_prog_appl_id
, x_program_id => l_program_id
, x_program_update_date => l_date
, x_attribute_category => l_to_rsv_rec.attribute_category
, x_attribute1 => l_to_rsv_rec.attribute1
, x_attribute2 => l_to_rsv_rec.attribute2
, x_attribute3 => l_to_rsv_rec.attribute3
, x_attribute4 => l_to_rsv_rec.attribute4
, x_attribute5 => l_to_rsv_rec.attribute5
, x_attribute6 => l_to_rsv_rec.attribute6
, x_attribute7 => l_to_rsv_rec.attribute7
, x_attribute8 => l_to_rsv_rec.attribute8
, x_attribute9 => l_to_rsv_rec.attribute9
, x_attribute10 => l_to_rsv_rec.attribute10
, x_attribute11 => l_to_rsv_rec.attribute11
, x_attribute12 => l_to_rsv_rec.attribute12
, x_attribute13 => l_to_rsv_rec.attribute13
, x_attribute14 => l_to_rsv_rec.attribute14
, x_attribute15 => l_to_rsv_rec.attribute15
, x_ship_ready_flag => l_to_rsv_rec.ship_ready_flag
, x_staged_flag => l_to_rsv_rec.staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, x_crossdock_flag => l_to_rsv_rec.crossdock_flag
, x_crossdock_criteria_id => l_to_rsv_rec.crossdock_criteria_id
, x_demand_source_line_detail => l_to_rsv_rec.demand_source_line_detail
, x_serial_reservation_quantity => l_to_rsv_rec.serial_reservation_quantity
, x_supply_receipt_date => l_to_rsv_rec.supply_receipt_date
, x_demand_ship_date => l_to_rsv_rec.demand_ship_date
, x_project_id => l_to_rsv_rec.project_id
, x_task_id => l_to_rsv_rec.task_id
, x_orig_supply_type_id => l_orig_supply_type_id
, x_orig_supply_header_id => l_to_rsv_rec.supply_source_header_id
, x_orig_supply_line_id => l_to_rsv_rec.supply_source_line_id
, x_orig_supply_line_detail => l_to_rsv_rec.supply_source_line_detail
, x_orig_demand_type_id => l_to_rsv_rec.demand_source_type_id
, x_orig_demand_header_id => l_to_rsv_rec.demand_source_header_id
, x_orig_demand_line_id => l_to_rsv_rec.demand_source_line_id
, x_orig_demand_line_detail => l_to_rsv_rec.demand_source_line_detail
/*** End R12 ***/
);
debug_print(' After call to insert_row : reservation_id : ' || l_reservation_id);
inv_rsv_synch.for_insert(p_reservation_id => l_reservation_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
cto_workflow_api_pk.wf_update_after_inv_reserv(p_order_line_id => l_to_rsv_rec.demand_source_line_id, x_return_status => l_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data);
SELECT inventory_item_id, serial_number bulk collect INTO
l_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT primary_reservation_quantity INTO
l_from_primary_reservation_qty FROM mtl_reservations WHERE
reservation_id = l_orig_rsv_tbl(1).reservation_id;
debug_print('This is case 1. The from reservation recordhas been deleted. Id: ' || l_orig_rsv_tbl(1).reservation_id);
SELECT primary_reservation_quantity INTO
l_to_primary_reservation_qty FROM mtl_reservations WHERE
reservation_id = l_to_reservation_id;
debug_print('This is case 2. The to reservation record has been deleted. Id: ' || l_to_reservation_id);
UPDATE mtl_reservations SET serial_reservation_quantity = 0
WHERE reservation_id = l_orig_rsv_tbl(1).reservation_id;
-- to do nothing as it is the same row that is being updated.
--3. If transfer_all is false, then move the excess serials to the
-- to record
IF l_debug=1 THEN
debug_print('Inside param 1');
UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
serial_number = l_serial_number_table(l_serial_index).serial_number AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
current_organization_id = l_orig_rsv_tbl(1).organization_id;
-- update the serial reservation quantity
update_serial_rsv_quantity
(x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reservation_id => l_to_reservation_id
, p_update_serial_qty => l_total_serials_reserved
);
debug_print('After calling update serial reservations ' || l_return_status);
UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
serial_number = l_serial_number_table(i).serial_number AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
current_organization_id = l_orig_rsv_tbl(1).organization_id;
-- update the serial reservation quantity to be the primary
-- reservation quantity, as it is in excess.
-- update the serial reservation quantity
update_serial_rsv_quantity
(x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reservation_id => l_orig_rsv_tbl(1).reservation_id
, p_update_serial_qty => -l_serials_tobe_unreserved
);
debug_print('After calling update serial reservations ' || l_return_status);
update_serial_rsv_quantity
(x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reservation_id => l_to_reservation_id
, p_update_serial_qty => l_serials_tobe_unreserved
);
debug_print('After calling update serial reservations ' || l_return_status);
SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_to_reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT reservation_id INTO l_from_reservation_id FROM mtl_serial_numbers WHERE
serial_number = p_original_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = NULL,
group_mark_id = NULL, line_mark_id = NULL,
lot_line_mark_id = NULL WHERE
serial_number = p_original_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT inventory_item_id, serial_number bulk collect INTO
l_validate_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
reservation_id = l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_to_reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
-- update with whatever is being moved to the to record
update_serial_rsv_quantity
(x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reservation_id => l_to_reservation_id
, p_update_serial_qty => l_validate_serial_number_table.count
);
debug_print('After calling update serial reservations ' || l_return_status);
SELECT inventory_item_id, serial_number bulk collect INTO
l_validate_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
serial_number = l_validate_serial_number_table(i).serial_number AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
current_organization_id = l_orig_rsv_tbl(1).organization_id;
SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_to_reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
update_serial_rsv_quantity
(x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reservation_id => l_to_reservation_id
, p_update_serial_qty => l_serials_tobe_unreserved
);
debug_print('After calling update serial reservations ' || l_return_status);
-- now update the from reservation record as we have to
-- update the unreserved serial and the transferred serial
l_serials_unreserved := -(p_original_serial_number.COUNT + Nvl(l_serials_tobe_unreserved,0));
-- update the serial reservation quantity to be the primary
-- reservation quantity, as it is in excess.
-- update the serial reservation quantity
update_serial_rsv_quantity
(x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reservation_id => l_orig_rsv_tbl(1).reservation_id
, p_update_serial_qty => l_serials_unreserved
);
debug_print('After calling update serial reservation qty ' || l_return_status);
-- working on the same record, we will have to update
-- the serial reservation quantity
IF (l_debug = 1) THEN
debug_print('Inside param 2. transfer_all and not to_row_exist');
SELECT inventory_item_id, serial_number bulk collect INTO
l_validate_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
update_serial_rsv_quantity
(x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_reservation_id => l_orig_rsv_tbl(1).reservation_id
, p_update_serial_qty => -p_original_serial_number.COUNT
);
debug_print('After calling update serial reservation qty ' || l_return_status);
SELECT reservation_id, group_mark_id INTO
l_from_reservation_id, l_group_mark_id FROM mtl_serial_numbers WHERE
serial_number = p_to_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
serial_number = p_to_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
-- else update from to to
IF (l_debug = 1) THEN
debug_print('Inside param 3. transfer_all and to_row_exist');
UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
serial_number = l_serial_number_table(l_serial_index).serial_number AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
current_organization_id = l_orig_rsv_tbl(1).organization_id;
SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_to_reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
-- update the to serial reservation quantity. dont have
-- to update from as the from record is deleted.
BEGIN
UPDATE mtl_reservations SET serial_reservation_quantity
= l_total_to_serials_reserved WHERE reservation_id =
l_to_reservation_id;
debug_print('Update failed for from reservation record. id: ' || l_to_reservation_id);
-- else update from to to
IF (l_debug = 1) THEN
debug_print('Inside param 3. transfer_all and not to_row_exist');
-- working on the same record, we will have to update
-- the serial reservation quantity. check to see if the
-- count exceeds
BEGIN
SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id = l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_reservations SET serial_reservation_quantity
= l_total_to_serials_reserved WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id;
debug_print('Update failed for from reservation record. id: ' || l_to_reservation_id);
debug_print('After calling update serial reservation qty ' || l_return_status);
SELECT inventory_item_id, serial_number bulk collect INTO
l_validate_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET
reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
serial_number = l_validate_serial_number_table(i).serial_number AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
current_organization_id = l_orig_rsv_tbl(1).organization_id;
SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_to_reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_reservations SET serial_reservation_quantity
= l_total_to_serials_reserved WHERE reservation_id =
l_to_reservation_id;
debug_print('Update failed for to reservation record. id: ' || l_to_reservation_id);
SELECT COUNT(1) INTO l_total_from_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_reservations SET serial_reservation_quantity
= l_total_from_serials_reserved WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id;
debug_print('Update failed for from reservation record. id: ' || l_orig_rsv_tbl(1).reservation_id);
SELECT reservation_id INTO l_from_reservation_id FROM mtl_serial_numbers WHERE
serial_number = p_original_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = NULL,
group_mark_id = NULL, line_mark_id = NULL,
lot_line_mark_id = NULL WHERE
serial_number = p_original_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT reservation_id, group_mark_id INTO
l_from_reservation_id, l_group_mark_id FROM mtl_serial_numbers WHERE
serial_number = p_to_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
serial_number = p_to_serial_number(i).serial_number AND
current_organization_id = l_orig_rsv_tbl(1).organization_id AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT inventory_item_id, serial_number bulk collect INTO
l_validate_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
reservation_id = l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_to_reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_reservations SET serial_reservation_quantity
= l_total_to_serials_reserved WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id;
-- else update from to to
IF (l_debug = 1) THEN
debug_print('Inside param 4. transfer_all and not to_row_exist');
SELECT inventory_item_id, serial_number bulk collect INTO
l_validate_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_reservations SET serial_reservation_quantity
= l_total_to_serials_reserved WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id;
-- then count and update the serial count
IF (l_debug = 1) THEN
debug_print('Inside param 4. not transfer_all');
SELECT inventory_item_id, serial_number bulk collect INTO
l_validate_serial_number_table FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_serial_numbers SET reservation_id = l_to_reservation_id,
group_mark_id = l_to_reservation_id WHERE
serial_number = l_validate_serial_number_table(i).serial_number AND
inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id AND
current_organization_id = l_orig_rsv_tbl(1).organization_id;
SELECT COUNT(1) INTO l_total_to_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_to_reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_reservations SET serial_reservation_quantity
= l_total_to_serials_reserved WHERE reservation_id =
l_to_reservation_id;
SELECT COUNT(1) INTO l_total_from_serials_reserved FROM
mtl_serial_numbers WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id AND current_organization_id =
l_orig_rsv_tbl(1).organization_id AND inventory_item_id = l_orig_rsv_tbl(1).inventory_item_id;
UPDATE mtl_reservations SET serial_reservation_quantity
= l_total_from_serials_reserved WHERE reservation_id =
l_orig_rsv_tbl(1).reservation_id;
SELECT
mr.reservation_id
, mr.requirement_date
, mr.organization_id
, mr.inventory_item_id
, mr.demand_source_type_id
, mr.demand_source_name
, mr.demand_source_header_id
, mr.demand_source_line_id
, mr.demand_source_delivery
, mr.primary_uom_code
, mr.primary_uom_id
, mr.secondary_uom_code
, mr.secondary_uom_id
, mr.reservation_uom_code
, mr.reservation_uom_id
, mr.reservation_quantity
, mr.primary_reservation_quantity
, mr.secondary_reservation_quantity
, mr.detailed_quantity
, mr.secondary_detailed_quantity
, mr.autodetail_group_id
, mr.external_source_code
, mr.external_source_line_id
, mr.supply_source_type_id
, mr.supply_source_header_id
, mr.supply_source_line_id
, mr.supply_source_name
, mr.supply_source_line_detail
, mr.revision
, mr.subinventory_code
, mr.subinventory_id
, mr.locator_id
, mr.lot_number
, mr.lot_number_id
, mr.pick_slip_number
, mr.lpn_id
, mr.attribute_category
, mr.attribute1
, mr.attribute2
, mr.attribute3
, mr.attribute4
, mr.attribute5
, mr.attribute6
, mr.attribute7
, mr.attribute8
, mr.attribute9
, mr.attribute10
, mr.attribute11
, mr.attribute12
, mr.attribute13
, mr.attribute14
, mr.attribute15
, mr.ship_ready_flag
, mr.staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations mr
WHERE demand_source_type_id IN (inv_reservation_global.g_source_type_oe
, inv_reservation_global.g_source_type_internal_ord
, inv_reservation_global.g_source_type_rma
)
AND demand_source_header_id = dmd_source_header_id
AND demand_source_line_id = dmd_source_line_id
ORDER BY NVL(mr.revision, ' ')
, NVL(mr.lot_number, ' ')
, NVL(mr.subinventory_code, ' ')
, NVL(mr.locator_id, 0);
SELECT
mr.reservation_id
, mr.requirement_date
, mr.organization_id
, mr.inventory_item_id
, mr.demand_source_type_id
, mr.demand_source_name
, mr.demand_source_header_id
, mr.demand_source_line_id
, mr.demand_source_delivery
, mr.primary_uom_code
, mr.primary_uom_id
, mr.reservation_uom_code
, mr.reservation_uom_id
, mr.secondary_uom_code
, mr.secondary_uom_id
, mr.reservation_quantity
, mr.primary_reservation_quantity
, mr.secondary_reservation_quantity
, mr.detailed_quantity
, mr.secondary_detailed_quantity
, mr.autodetail_group_id
, mr.external_source_code
, mr.external_source_line_id
, mr.supply_source_type_id
, mr.supply_source_header_id
, mr.supply_source_line_id
, mr.supply_source_name
, mr.supply_source_line_detail
, mr.revision
, mr.subinventory_code
, mr.subinventory_id
, mr.locator_id
, mr.lot_number
, mr.lot_number_id
, mr.pick_slip_number
, mr.lpn_id
, mr.attribute_category
, mr.attribute1
, mr.attribute2
, mr.attribute3
, mr.attribute4
, mr.attribute5
, mr.attribute6
, mr.attribute7
, mr.attribute8
, mr.attribute9
, mr.attribute10
, mr.attribute11
, mr.attribute12
, mr.attribute13
, mr.attribute14
, mr.attribute15
, mr.ship_ready_flag
, mr.staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations mr
WHERE demand_source_type_id IN (inv_reservation_global.g_source_type_oe
, inv_reservation_global.g_source_type_internal_ord
, inv_reservation_global.g_source_type_rma
)
AND demand_source_header_id = dmd_source_header_id
AND demand_source_line_id = dmd_source_line_id
ORDER BY mr.requirement_date asc
, NVL(mr.revision, ' ')
, NVL(mr.lot_number, ' ')
, NVL(mr.subinventory_code, ' ')
, NVL(mr.locator_id, 0);
SELECT
mr.reservation_id
, mr.requirement_date
, mr.organization_id
, mr.inventory_item_id
, mr.demand_source_type_id
, mr.demand_source_name
, mr.demand_source_header_id
, mr.demand_source_line_id
, mr.demand_source_delivery
, mr.primary_uom_code
, mr.primary_uom_id
, mr.secondary_uom_code
, mr.secondary_uom_id
, mr.reservation_uom_code
, mr.reservation_uom_id
, mr.reservation_quantity
, mr.primary_reservation_quantity
, mr.secondary_reservation_quantity
, mr.detailed_quantity
, mr.secondary_detailed_quantity
, mr.autodetail_group_id
, mr.external_source_code
, mr.external_source_line_id
, mr.supply_source_type_id
, mr.supply_source_header_id
, mr.supply_source_line_id
, mr.supply_source_name
, mr.supply_source_line_detail
, mr.revision
, mr.subinventory_code
, mr.subinventory_id
, mr.locator_id
, mr.lot_number
, mr.lot_number_id
, mr.pick_slip_number
, mr.lpn_id
, mr.attribute_category
, mr.attribute1
, mr.attribute2
, mr.attribute3
, mr.attribute4
, mr.attribute5
, mr.attribute6
, mr.attribute7
, mr.attribute8
, mr.attribute9
, mr.attribute10
, mr.attribute11
, mr.attribute12
, mr.attribute13
, mr.attribute14
, mr.attribute15
, mr.ship_ready_flag
, mr.staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations mr
WHERE demand_source_type_id IN (inv_reservation_global.g_source_type_oe
, inv_reservation_global.g_source_type_internal_ord
, inv_reservation_global.g_source_type_rma
)
AND demand_source_header_id = dmd_source_header_id
AND demand_source_line_id = dmd_source_line_id
ORDER BY mr.requirement_date DESC
, NVL(mr.revision, ' ')
, NVL(mr.lot_number, ' ')
, NVL(mr.subinventory_code, ' ')
, NVL(mr.locator_id, 0);
SELECT
reservation_id
, requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, demand_source_delivery
, primary_uom_code
, primary_uom_id
, secondary_uom_code
, secondary_uom_id
, reservation_uom_code
, reservation_uom_id
, reservation_quantity
, primary_reservation_quantity
, secondary_reservation_quantity
, detailed_quantity
, secondary_detailed_quantity
, autodetail_group_id
, external_source_code
, external_source_line_id
, supply_source_type_id
, supply_source_header_id
, supply_source_line_id
, supply_source_name
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, pick_slip_number
, lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, ship_ready_flag
, staged_flag
/**** {{ R12 Enhanced reservations code changes }}****/
, crossdock_flag
, crossdock_criteria_id
, demand_source_line_detail
, serial_reservation_quantity
, supply_receipt_date
, demand_ship_date
, project_id
, task_id
, orig_supply_source_type_id
, orig_supply_source_header_id
, orig_supply_source_line_id
, orig_supply_source_line_detail
, orig_demand_source_type_id
, orig_demand_source_header_id
, orig_demand_source_line_id
, orig_demand_source_line_detail
, serial_number
/***** End R12 ***/
FROM mtl_reservations
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND (p_revision IS NULL OR revision = p_revision)
AND demand_source_header_id = l_demand_source_header_id
AND demand_source_line_id = p_demand_source_line_id
AND subinventory_code = p_from_subinventory_code
AND locator_id = p_from_locator_id
AND (p_lot_number IS NULL OR lot_number = p_lot_number)
-- NVL added to prevent dependency on TM to not break existing code
-- by resulting in a no records found if p_source_lpn_id is null.
-- All new calls to this api starting with this bug
-- fix (4016953/3871066) pass the p_source_lpn_id
AND (lpn_id = NVL(p_source_lpn_id, lpn_id) OR lpn_id IS NULL)
ORDER BY lpn_id asc;
fnd_message.set_name('INV', 'INV_UPDATE_RSV_FAILED');
SELECT lot_number
, transaction_quantity
FROM mtl_transaction_lots_temp
WHERE transaction_temp_id = p_transaction_temp_id;
SELECT wdd.source_header_id
, wdd.source_line_id
, wdd.inventory_item_id
, wdd.revision
, wdd.lot_number
, wdd.requested_quantity
, wdd.requested_quantity_uom
, wdd.delivery_detail_id
, wdd2.lpn_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_delivery_details wdd2
WHERE wdd.container_flag = 'N' --bug4639858 Only want wdd lines that represent items
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
-- Workaround for performance issue 3631133
-- lpn_id for the LPN and it's child lpns will be stored in this global
-- temp table under the line_id. this is to avoid a connect by statment
-- in this cursor
-- Note: Repeating the where condition below to force the db optimizer
-- to drive the main-query from sub-query. bug: 4145360
AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
AND wdd2.lpn_id IN ( SELECT line_id
FROM WMS_TXN_CONTEXT_TEMP
WHERE txn_source_name = 'XFER_LPN_RES'
AND txn_source_name = 'XFER_LPN_RES' );
SELECT source_header_id
, source_line_id
FROM wsh_delivery_details
WHERE inventory_item_id = p_inventory_item_id
AND NVL(subinventory, '@@@@') = NVL(p_from_subinventory_code, '@@@@')
AND NVL(revision, '@@@@') = NVL(p_revision, '@@@@')
AND NVL(lot_number, '@@@@') = NVL(p_lot, '@@@@')
AND delivery_detail_id IN
(SELECT wda.delivery_detail_id
FROM wsh_delivery_assignments wda,
wsh_delivery_details wdd2,
wms_license_plate_numbers wlpn
WHERE wda.parent_delivery_detail_id = wdd2.delivery_detail_id
AND wdd2.lpn_id = wlpn.lpn_id
AND wlpn.outermost_lpn_id = p_lpn_id
AND wdd2.released_status = 'X'); -- For LPN reuse ER : 6845650
INSERT INTO WMS_TXN_CONTEXT_TEMP ( line_id, txn_source_name )
SELECT distinct lpn_id, 'XFER_LPN_RES'
FROM wms_license_plate_numbers
START WITH lpn_id = p_lpn_id
CONNECT BY parent_lpn_id = PRIOR lpn_id;
DELETE FROM WMS_TXN_CONTEXT_TEMP
WHERE txn_source_name = 'XFER_LPN_RES';
PROCEDURE insert_rsv_temp(
p_organization_id NUMBER
, p_inventory_item_id NUMBER
, p_primary_reservation_quantity NUMBER
, p_tree_id NUMBER
, p_reservation_id NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, p_demand_source_line_id NUMBER
, p_demand_source_header_id NUMBER
, p_demand_source_name VARCHAR2
) IS
l_api_name VARCHAR2(100) := 'Insert_rsv_temp';
INSERT INTO rsv_temp
(
p_organization_id
, p_inventory_item_id
, p_primary_reservation_quantity
, p_tree_id
, p_reservation_id
, p_demand_source_line_id
, p_demand_source_header_id
, p_demand_source_name
)
VALUES (
p_organization_id
, p_inventory_item_id
, p_primary_reservation_quantity
, p_tree_id
, p_reservation_id
, p_demand_source_line_id
, p_demand_source_header_id
, p_demand_source_name
);
debug_print('Inserted rsv_temp org='||p_organization_id||' item='||p_inventory_item_id||', p_tree_id='||p_tree_id||', p_reservation_id='||p_reservation_id||', p_demand_source_line_id ='||p_demand_source_line_id);
END insert_rsv_temp;
SELECT p_tree_id
FROM rsv_temp
ORDER BY p_organization_id, p_inventory_item_id;
SELECT p_reservation_id
, p_organization_id
, p_inventory_item_id
, p_demand_source_line_id
, p_demand_source_header_id
, p_demand_source_name
FROM rsv_temp
WHERE p_tree_id = l_tree_id;
delete_reservation(
p_api_version_number => 1.0
, p_init_msg_lst => p_init_msg_lst
, x_return_status => l_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => l_rsv_rec
, p_original_serial_number => p_original_serial_number
, p_validation_flag => fnd_api.g_false
);
Select Reservation_ID
From Mtl_Serial_Numbers
Where Inventory_Item_Id = v_Inventory_Item_Id
and current_organization_id = v_Organization_id
and Serial_Number = v_Serial_Number;
SELECT msn.reservation_id,
msn.serial_number,
msn.inventory_item_id
FROM mtl_reservations mr,
mtl_serial_numbers msn
WHERE mr.organization_id = p_organization_id
AND mr.inventory_item_id = p_inventory_item_id
AND mr.reservation_id = msn.reservation_id
AND mr.lpn_id = null
AND msn.lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_outermost_lpn_id)
GROUP BY msn.reservation_id, msn.serial_number, msn.inventory_item_id;
SELECT msn.reservation_id,
msn.serial_number,
msn.inventory_item_id
FROM mtl_reservations mr,
mtl_serial_numbers msn
WHERE mr.organization_id = p_organization_id
AND mr.reservation_id = msn.reservation_id
AND mr.lpn_id = null
AND msn.lpn_id IN (SELECT lpn_id
FROM wms_license_plate_numbers
WHERE outermost_lpn_id = p_outermost_lpn_id)
GROUP BY msn.reservation_id, msn.serial_number, msn.inventory_item_id;
SELECT msn.reservation_id,
msn.serial_number,
msn.inventory_item_id
FROM mtl_reservations mr,
mtl_serial_numbers msn
WHERE mr.organization_id = p_organization_id
AND mr.inventory_item_id = p_inventory_item_id
AND mr.reservation_id = msn.reservation_id
AND mr.lpn_id = null
AND msn.lpn_id = p_lpn_id
GROUP BY msn.reservation_id, msn.serial_number, msn.inventory_item_id;
SELECT msn.reservation_id,
msn.serial_number,
msn.inventory_item_id
FROM mtl_reservations mr,
mtl_serial_numbers msn
WHERE mr.organization_id = p_organization_id
AND mr.reservation_id = msn.reservation_id
AND mr.lpn_id = null
AND msn.lpn_id = p_lpn_id
GROUP BY msn.reservation_id, msn.serial_number, msn.inventory_item_id;
l_serial_number_tbl.DELETE;