DBA Data[Home] [Help]

APPS.INV_RESERVATIONS_INTERFACE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 21

** 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;
Line: 85

     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;
Line: 371

** 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) := ':';
Line: 450

   l_delete_rows         boolean;
Line: 457

     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 */;
Line: 518

    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;
Line: 528

      l_delete_rows := true;
Line: 585

                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;
Line: 592

		l_delete_rows := false;
Line: 603

                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;
Line: 615

      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;
Line: 690

** 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';
Line: 880

  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;
Line: 1130

           select subinventory into l_oe_line_subinventory from oe_order_lines_all  where
     	   line_id = l_rsv_rec.demand_source_line_id;
Line: 1210

  ** 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;
Line: 1222

  ** 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);
Line: 1286

	-- 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
                                         );
Line: 1331

	-- Delete reservation
        l_rsv_rec.reservation_id := fnd_api.g_miss_num;
Line: 1334

	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);
Line: 1424

      update mtl_reservations_interface
      set row_status_code = 2 /* Completed */
      where reservation_interface_id = p_reservation_interface_id;