The following lines contain the word 'select', 'insert', 'update' or 'delete':
** Create Reservations, Update Reservations, Delete Reservations and
** Transfer Reservations can write their specific requests with details such
** as item, organization, demand, supply, inventory controls and quantity
** information into MTL_RESERVATIONS_INTERFACE table.
** rsv interface manager thru another program, rsv
** batch processor, processes records from MTL_RESERVATIONS_INTERFACE table
** into MTL_RESERVATIONS table, one or more reservation batch id(s) at a time.
** A reservation batch id consists of one or more reservations processing
** requests in MTL_RESERVATIONS_INTERFACE table. Processing includes data
** validation, executions of appropriate reservation APIs, thereby writing
** into MTL_RESERVATIONS table and finally deleting successfuly processed
** records from MTL_RESERVATIONS_INTERFACE table.
**
** Input Parameters:
** p_api_version_number
** parameter to compare API version
** p_init_msg_lst
** flag indicating if message list should be initialized
** p_form_mode
** 'Y','y' - called from form
** 'N','n' - not called from form
**
** Output Parameters:
** x_errbuf
** mandatory concurrent program parameter
** x_retcode
** mandatory concurrent program parameter
**
** Tables Used:
** MTL_RESERVATIONS_INTERFACE for Read and Update.
**
** Current Version 1.0
** Initial Version 1.0
** ===========================================================================
*/
PROCEDURE rsv_interface_manager(
x_errbuf OUT NOCOPY VARCHAR2
, x_retcode OUT NOCOPY NUMBER
, p_api_version_number IN NUMBER DEFAULT 1.0
, p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
, p_form_mode IN VARCHAR2 DEFAULT 'N') as
-- Constants
c_max_numof_lines constant number := 3;
select
reservation_batch_id
, count(*) total_num
from mtl_reservations_interface mri1
where mri1.transaction_mode = 3 /* Background */
and mri1.row_status_code = 1 /* Active */
and mri1.error_code is null /* No errors */
and mri1.error_explanation is null /* No errors */
and mri1.lock_flag = 2 /* No */
and not exists(
select 1 from mtl_reservations_interface mri2
where mri1.reservation_batch_id = mri2.reservation_batch_id
and mri2.transaction_mode = 3
and (mri2.row_status_code <> 1 or
mri2.error_code is not null or
mri2.error_explanation is not null or
mri2.lock_flag = 1)
)
group by reservation_batch_id
order by reservation_batch_id; --Bug 13744134
** Create Reservations, Update Reservations, Delete Reservations and
** Transfer Reservations can write their specific requests with details such
** as item, organization, demand, supply, inventory controls and quantity
** information into MTL_RESERVATIONS_INTERFACE table.
** rsv interface batch processor, processes records from
** MTL_RESERVATIONS_INTERFACE table into MTL_RESERVATIONS table, one or more
** reservation batch id(s) at a time. A reservation batch id consists of one
** or more reservations processing requests in MTL_RESERVATIONS_INTERFACE table.
** A reservations request in MTL_RESERVATIONS_INTERFACE table is uniquely
** determined by a reservations interface id.
** rsv interface batch processor in turn calls another program,
** rsv interface line processor repetitively, passing each time a
** reservations interafce id under the current reservations batch id.
** reservations interface line processor performs the actual reservations
** processing.
** rsv interface batch processor deletes successfully processed
** rows from MTL_RESERVATIONS_INTERFACE table.
**
** Input Parameters:
** p_api_version_number
** parameter to compare API version
** p_init_msg_lst
** flag indicating if message list should be initialized
** p_reservation_batches
** reservation batch ids stringed together and separated by
** delimiter.Eg: 163:716:987:
** p_process_mode
** 1 = Online 2 = Concurrent 3 = Background
** p_partial_batch_processing_flag
** 1 - If a line in reservation batch fails, continue
** 2 - If a line in reservation batch fails, exit
** p_commit_flag
** 'Y','y' - Commit
** not('Y','y') - Do not commit
** p_form_mode
** 'Y','y' - called from form
** 'N','n' - not called from form
**
** Output Parameters:
** x_return_status
** return status indicating success, error, unexpected error
** x_msg_count
** number of messages in message list
** x_msg_data
** if the number of messages in message list is 1, contains
** message text
**
** Tables Used:
** MTL_RESERVATIONS_INTERFACE for Read, Update and Delete.
**
** Current Version 1.0
** Initial Version 1.0
** ===========================================================================
*/
PROCEDURE rsv_interface_batch_processor (
p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
, p_reservation_batches IN VARCHAR2
, p_process_mode IN NUMBER DEFAULT 1
, p_partial_batch_process_flag IN NUMBER DEFAULT 1
, p_commit_flag IN VARCHAR2 DEFAULT 'Y'
, p_form_mode IN VARCHAR2 DEFAULT 'N'
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2) as
-- Constants
c_delimiter constant varchar2(1) := ':';
l_delete_rows boolean;
select reservation_interface_id
from mtl_reservations_interface mri
where mri.reservation_batch_id = batch_id
and mri.row_status_code = 1 /* Active */
and mri.transaction_mode = process_mode
and mri.error_code is null
and mri.error_explanation is null
and mri.lock_flag = 1 /* Yes */;
update mtl_reservations_interface
set lock_flag = 1 /* Yes */
where reservation_batch_id = l_batch_id
and row_status_code = 1 /* Active */
and lock_flag = 2 /* No */
and transaction_mode = p_process_mode
and error_code is null
and error_explanation is null;
l_delete_rows := true;
update mtl_reservations_interface
set
row_status_code = 3 /* Error */
, error_code = l_error_code
, error_explanation = substrb(l_error_text,1,240) -- Bug 5529609
where reservation_interface_id = l_interface_id;
l_delete_rows := false;
update mtl_reservations_interface
set
row_status_code = 3 /* Error */
, lock_flag = 2 /* No */
, error_code = l_error_code
, error_explanation = substrb(l_error_text,1,240) -- Bug 5529609
where reservation_interface_id = l_interface_id;
if (l_delete_rows = true) then
delete mtl_reservations_interface
where reservation_batch_id = l_batch_id
and transaction_mode = p_process_mode
and row_status_code = 2 /* Completed */
and error_code is null
and error_explanation is null;
** Create Reservations, Update Reservations, Delete Reservations and
** Transfer Reservations can write their specific requests with details such
** as item, organization, demand, supply, inventory controls and quantity
** information into MTL_RESERVATIONS_INTERFACE table.
** rsv interface line processor processes the reservations
** request line in MTL_RESERVATIONS_INTERFACE, pointed by a given
** reservations interface id. Processing includes data validation and
** performing the requested reservation function by executing the appropriate
** reservations API.
**
** Input Parameters:
** p_api_version_number
** parameter to compare API version
** p_init_msg_lst
** flag indicating if message list should be initialized
** p_reservation interface id
** identifies reservations request line in
** MTL_RESERVATIONS_INTERFACE table.
** p_form_mode
** 'Y','y' - called from form
** 'N','n' - not called from form
**
** Output Parameters:
** x_error_code
** error code
** x_error_text
** error explanation text
** x_return_status
** return status indicating success, error, unexpected error
** x_msg_count
** number of messages in message list
** x_msg_data
** if the number of messages in message list is 1, contains
** message text
**
** Tables Used:
** MTL_RESERVATIONS_INTERFACE for Read and Update.
**
** Current Version 1.0
** Initial Version 1.0
** ===========================================================================
*/
PROCEDURE rsv_interface_line_processor (
p_api_version_number IN NUMBER
, p_init_msg_lst IN VARCHAR2 DEFAULT fnd_api.g_false
, p_reservation_interface_id IN NUMBER
, p_form_mode IN VARCHAR2 DEFAULT 'N'
, x_error_code OUT NOCOPY NUMBER
, x_error_text OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2) as
-- Constants
c_api_name constant varchar2(30):= 'rsv_interface_line_processor';
select
requirement_date
, organization_id
, inventory_item_id
, demand_source_type_id
, demand_source_name
, demand_source_header_id
, demand_source_line_id
, 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
, supply_source_type_id
, supply_source_name
, supply_source_header_id
, supply_source_line_id
, supply_source_line_detail
, revision
, subinventory_code
, subinventory_id
, locator_id
, lot_number
, lot_number_id
, to_organization_id
, to_demand_source_type_id
, to_demand_source_name
, to_demand_source_header_id
, to_demand_source_line_id
, to_supply_source_type_id
, to_supply_source_name
, to_supply_source_header_id
, to_supply_source_line_id
, to_supply_source_line_detail
, to_revision
, to_subinventory_code
, to_subinventory_id
, to_locator_id
, to_lot_number
, to_lot_number_id
, reservation_action_code
, validation_flag
, partial_quantities_allowed
, ship_ready_flag
, lpn_id
, to_lpn_id
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
into
l_requirement_date
, l_organization_id
, l_inventory_item_id
, l_demand_source_type_id
, l_demand_source_name
, l_demand_source_header_id
, l_demand_source_line_id
, l_primary_uom_code
, l_primary_uom_id
, l_secondary_uom_code
, l_secondary_uom_id
, l_reservation_uom_code
, l_reservation_uom_id
, l_reservation_quantity
, l_primary_rsv_quantity
, l_secondary_rsv_quantity
, l_supply_source_type_id
, l_supply_source_name
, l_supply_source_header_id
, l_supply_source_line_id
, l_supply_source_line_detail
, l_revision
, l_subinventory_code
, l_subinventory_id
, l_locator_id
, l_lot_number
, l_lot_number_id
, l_to_organization_id
, l_to_demand_source_type_id
, l_to_demand_source_name
, l_to_demand_source_header_id
, l_to_demand_source_line_id
, l_to_supply_source_type_id
, l_to_supply_source_name
, l_to_supply_source_header_id
, l_to_supply_source_line_id
, l_to_supply_source_line_detail
, l_to_revision
, l_to_subinventory_code
, l_to_subinventory_id
, l_to_locator_id
, l_to_lot_number
, l_to_lot_number_id
, l_reservation_action_code
, l_validation_flag
, l_partial_quantities_allowed
, l_ship_ready_flag
, l_lpn_id
, l_to_lpn_id
, l_attribute_category
, l_attribute1
, l_attribute2
, l_attribute3
, l_attribute4
, l_attribute5
, l_attribute6
, l_attribute7
, l_attribute8
, l_attribute9
, l_attribute10
, l_attribute11
, l_attribute12
, l_attribute13
, l_attribute14
, l_attribute15
from mtl_reservations_interface
where reservation_interface_id = p_reservation_interface_id
and row_status_code = 1 /* Active */
and error_code is null
and error_explanation is null;
select subinventory into l_oe_line_subinventory from oe_order_lines_all where
line_id = l_rsv_rec.demand_source_line_id;
select sales_order_id into l_mso_sales_order_id from mtl_sales_orders
where sales_order_id = l_rsv_rec.demand_source_header_id;
Select Header_ID into l_oe_order_header_id from OE_Order_Lines_All
Where line_id = l_rsv_rec.demand_source_line_id;
** For not(insert) requests quantity should be a value of don't care
** for a successful retrieval of source record.
*/
if (l_reservation_action_code in (2,3,4)) then
l_rsv_rec.reservation_quantity := fnd_api.g_miss_num;
** 2. Update reservation
** 3. Delete reservation
** 4. Transfer reservation
*/
if (l_reservation_action_code = 1) then
-- Create reservation
inv_reservation_pub.create_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_rsv_rec => l_rsv_rec
, p_serial_number => l_serial_number
, x_serial_number => l_serial_number
, p_partial_reservation_flag => l_partial_reservation_flag
, p_force_reservation_flag => fnd_api.g_false
, p_validation_flag => fnd_api.g_true
, x_quantity_reserved => l_quantity_reserved
, x_secondary_quantity_reserved => l_secondary_quantity_reserved --INVCONV
, x_reservation_id => l_reservation_id);
-- Update reservation
-- Adeed the extra parameter for call to update_reservations
-- Bug Number 3392957
inv_reservation_pub.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
, p_original_rsv_rec => l_rsv_rec
, p_to_rsv_rec => l_to_rsv_rec
, p_original_serial_number => l_serial_number
, p_to_serial_number => l_serial_number
, p_validation_flag => fnd_api.g_true
, p_check_availability => fnd_api.g_true
);
-- Delete reservation
l_rsv_rec.reservation_id := fnd_api.g_miss_num;
inv_reservation_pub.delete_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_rsv_rec => l_rsv_rec
, p_serial_number => l_serial_number);
update mtl_reservations_interface
set row_status_code = 2 /* Completed */
where reservation_interface_id = p_reservation_interface_id;