APIs in R12 (OPM,INV,OM,WMS Modules)

Thursday, January 17, 2013 by Ajay Atre


R12 APIs

OPM APIs
OPM production involves batch creation and batch execution. The Batch flows through various statuses; each of the status has significance during the production batch process.
Batch status
Create document (Batch creation)
Pending
Batch release
WIP
Complete the batch
Completed
Close the batch
Closed
Cancel the batch
cancelled

1.   Release the batch (WIP)
DECLARE
   g_debug_point                          VARCHAR2(100);  
   l_batch_header                          gme_batch_header%ROWTYPE;
   x_batch_header                          gme_batch_header%ROWTYPE;
   p_batch_header                          gme_batch_header%ROWTYPE;
   x_message_count                         NUMBER;
   x_message_list                          VARCHAR2 (2000);
   x_return_status                         VARCHAR2 (1);
   l_msg_index_out                         NUMBER;
   x_exception_material_tbl                gme_common_pvt.exceptions_tab;
BEGIN
   -- +------------------------------------------------------------+
   -- + Release the batch from pending to WIP
   -- +------------------------------------------------------------+
   DBMS_OUTPUT.put_line ('...Executing Batch Release API');
   l_batch_header.batch_type                                         := 0;
   l_batch_header.plan_start_date                                    := SYSDATE;
   l_batch_header.plan_cmplt_date                                    := SYSDATE;
   l_batch_header.due_date                                           := SYSDATE;
   l_batch_header.update_inventory_ind                               := 'Y';
   l_batch_header.actual_start_date                                  := SYSDATE;
   l_batch_header.batch_id                     := <batch_id>;
   l_batch_header.batch_no                     := <batch_no>;
   x_return_status                                                   := NULL;
   x_message_count                                                   := NULL;
   x_message_list                                                    := NULL;
   gme_api_pub.release_batch (p_api_version =>                 2.0   -- R12 changes
                             ,p_validation_level =>            1000,
                              --P_init_msg_list             => TRUE,
                              --P_commit                    => FALSE,
                              x_message_count =>               x_message_count
                             ,x_message_list =>                x_message_list
                             ,x_return_status =>               x_return_status
                             ,p_batch_header_rec =>            l_batch_header
                             ,x_batch_header_rec =>            x_batch_header
                             ,x_exception_material_tbl =>      x_exception_material_tbl
                             ,p_org_code                    => <WHS_CODE>,                          
                              p_ignore_exception =>            'T'   -- R12 changes
                             );

   IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      p_batch_header.batch_id                               := x_batch_header.batch_id;
      -- +------------------------------------------------------------+
      -- + SAVE THE CHANGES
      -- +------------------------------------------------------------+
      g_debug_point                                         := 'STS_1060';
      gme_api_pub.save_batch (p_header_id =>                   x_batch_header.batch_id, x_return_status => x_return_status);
      DBMS_OUTPUT.put_line ('Batch# ' || batch_no || '...Batch Status changed to    
               RELEASE');
   ELSE
      DBMS_OUTPUT.put_line ('Batch# ' || batch_no || '... Batch Release Call Failed');
   END IF;
END;

2.   Complete the batch

DECLARE
   g_debug_point                          VARCHAR2(100);
   l_batch_header                          gme_batch_header%ROWTYPE;
   x_batch_header                          gme_batch_header%ROWTYPE;
   p_batch_header                          gme_batch_header%ROWTYPE;
   x_message_count                         NUMBER;
   x_message_list                          VARCHAR2 (2000);
   x_return_status                         VARCHAR2 (1);
   l_msg_index_out                         NUMBER;
   x_exception_material_tbl                gme_common_pvt.exceptions_tab;
BEGIN
   -- +------------------------------------------------------------+
   -- + Release the batch from pending to WIP
   -- +------------------------------------------------------------+
   DBMS_OUTPUT.put_line ('...Executing Batch Release API');
   l_batch_header.batch_type                                         := 0;
   l_batch_header.plan_start_date                                    := SYSDATE;
   l_batch_header.plan_cmplt_date                                    := SYSDATE;
   l_batch_header.due_date                                           := SYSDATE;
   l_batch_header.update_inventory_ind                               := 'Y';
   l_batch_header.actual_start_date                                  := SYSDATE;
   l_batch_header.batch_id                     := <batch_id>;
   l_batch_header.batch_no                     := <batch_no>;
   x_return_status                                                   := NULL;
   x_message_count                                                   := NULL;
   x_message_list                                                    := NULL;
   gme_api_pub. complete_batch (p_api_version =>                 2.0   -- R12 changes
                             ,p_validation_level =>            1000,
                              --P_init_msg_list             => TRUE,
                              --P_commit                    => FALSE,
                              x_message_count =>               x_message_count
                             ,x_message_list =>                x_message_list
                             ,x_return_status =>               x_return_status
                             ,p_batch_header_rec =>            l_batch_header
                             ,x_batch_header_rec =>            x_batch_header
                             ,x_exception_material_tbl =>      x_exception_material_tbl
                             ,p_org_code                    => <WHS_CODE>,                          
                              p_ignore_exception =>            'T'   -- R12 changes
                             );

   IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      p_batch_header.batch_id                               := x_batch_header.batch_id;
      -- +------------------------------------------------------------+
      -- + SAVE THE CHANGES
      -- +------------------------------------------------------------+
      g_debug_point                                         := 'STS_1060';
      gme_api_pub.save_batch (p_header_id =>                   x_batch_header.batch_id, x_return_status => x_return_status);
      DBMS_OUTPUT.put_line ('Batch# ' || batch_no || '...Batch Status changed to    
               COMPLETE);
   ELSE
      DBMS_OUTPUT.put_line ('Batch# ' || batch_no || '... Batch Complete Call Failed');
   END IF;
END;

3.   Product allocation (WIP Completion)
DECLARE
   g_debug_point                           VARCHAR2 (100);
   l_mmti_rec                              mtl_transactions_interface%ROWTYPE;
   l_mmli_tbl                              gme_common_pvt.mtl_trans_lots_inter_tbl;
   x_mmt_rec                               mtl_material_transactions%ROWTYPE;
   x_mmln_tbl                              gme_common_pvt.mtl_trans_lots_num_tbl;
   l_transaction_interface_id              NUMBER := NULL;
   l_error_message                         VARCHAR2 (1000) := NULL;
   l_error_msg                             VARCHAR2 (2000);
   l_validate_level                        NUMBER := gme_common_pvt.g_max_errors;
   l_msg_index_out                         NUMBER;
   x_message_count                         NUMBER;
   x_message_list                          VARCHAR2 (2000);
   x_return_status                         VARCHAR2 (1);
BEGIN
   SELECT mtl_material_transactions_s.NEXTVAL
     INTO l_transaction_interface_id
     FROM DUAL;

   -- +------------------------------------------------------------+
   -- + Material recod assignment
   -- +------------------------------------------------------------+
   g_debug_point                                                         := 'PA_1080';
   l_mmti_rec.transaction_interface_id                                   := l_transaction_interface_id;
   l_mmti_rec.transaction_header_id                                      := l_transaction_interface_id;
   l_mmti_rec.process_flag                                               := 1;
   l_mmti_rec.validation_required                                        := 1;
   l_mmti_rec.transaction_mode                                           := 3;
   l_mmti_rec.lock_flag                                                  := 2;
   l_mmti_rec.last_update_date                                           := SYSDATE;
   l_mmti_rec.last_updated_by                                            := <user_id>;
   l_mmti_rec.creation_date                                              := SYSDATE;
   l_mmti_rec.created_by                                                 := <user_id>;
   l_mmti_rec.last_update_login                                          := -1;
   l_mmti_rec.transaction_date                                           := SYSDATE;
   l_mmti_rec.subinventory_code                                          := <subinventory_code>;
   l_mmti_rec.locator_id                                                 := <locator_id>;
   l_mmti_rec.transaction_source_id                                      := l_transaction_interface_id;
   l_mmti_rec.transaction_source_type_id                                 := 5;
   l_mmti_rec.transaction_action_id                                      := 1;
   l_mmti_rec.transaction_type_id                                        := 44;
   -- +------------------------------------------------------------+
   --Lot Records -- (for parent batch assign lot number as parent batch# )
   -- +------------------------------------------------------------+
   l_mmli_tbl (1).transaction_interface_id                               := l_transaction_interface_id;
   l_mmli_tbl (1).source_line_id                                         := l_mmti_rec.trx_source_line_id;
   l_mmli_tbl (1).last_update_date                                       := SYSDATE;
   l_mmli_tbl (1).last_updated_by                                        := l_user_id;
   l_mmli_tbl (1).creation_date                                          := SYSDATE;
   l_mmli_tbl (1).created_by                                             := l_user_id;
   l_mmli_tbl (1).last_update_login                                      := -1;
   l_mmli_tbl (1).transaction_quantity                                   := <transaction_quantity>;
   l_mmli_tbl (1).process_flag                                           := 1;
   g_debug_point                                                         := 'PA_1090';
   DBMS_OUTPUT.put_line ('...Calling create_material_txn for WIP completion ...');
   gme_api_pub.create_material_txn (p_api_version =>                 2.0
                                   ,p_validation_level =>            l_validate_level
                                   ,p_init_msg_list =>               'F'
                                   ,p_commit =>                      'F'
                                   ,x_message_count =>               x_message_count
                                   ,x_message_list =>                x_message_list
                                   ,x_return_status =>               x_return_status
                                   ,p_org_code =>                    g_organization_code
                                   ,p_mmti_rec =>                    l_mmti_rec
                                   ,p_mmli_tbl =>                    l_mmli_tbl
                                   ,p_batch_no =>                    l_batch_no
                                   ,p_line_no =>                     l_line_no
                                   ,p_line_type =>                   l_line_type
                                   ,p_create_lot =>                  'T'
                                   ,p_generate_lot =>                'F'
                                   ,p_generate_parent_lot =>         'F'
                                   ,x_mmt_rec =>                     x_mmt_rec
                                   ,x_mmln_tbl =>                    x_mmln_tbl
                                   );
   DBMS_OUTPUT.put_line ('...End of create_material_txn for WIP completion ...return status' || x_return_status);

   IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('... Batch# ' || l_batch_no || ' PRODUCT Allocation call Successful');
      -- +------------------------------------------------------------+
      -- + SAVE THE CHANGES for product allocation of parent batch
      -- +------------------------------------------------------------+
      g_debug_point                               := 'PA_1100';
      gme_api_pub.save_batch (p_header_id =>                   l_batch_id, x_return_status => x_return_status);
   ELSE
      DBMS_OUTPUT.put_line ('Batch# ' || batch_no || '... PRODUCT Allocation Call Failed');
   END IF;
END;

4.   Ingredient allocation (WIP ISSUE)
DECLARE
   g_debug_point                           VARCHAR2 (100);
   l_mmti_rec                              mtl_transactions_interface%ROWTYPE;
   l_mmli_tbl                              gme_common_pvt.mtl_trans_lots_inter_tbl;
   x_mmt_rec                               mtl_material_transactions%ROWTYPE;
   x_mmln_tbl                              gme_common_pvt.mtl_trans_lots_num_tbl;
   l_transaction_interface_id              NUMBER := NULL;
   l_error_message                         VARCHAR2 (1000) := NULL;
   l_error_msg                             VARCHAR2 (2000);
   l_validate_level                        NUMBER := gme_common_pvt.g_max_errors;
   l_msg_index_out                         NUMBER;
   x_message_count                         NUMBER;
   x_message_list                          VARCHAR2 (2000);
   x_return_status                         VARCHAR2 (1);
BEGIN
   SELECT mtl_material_transactions_s.NEXTVAL
     INTO l_transaction_interface_id
     FROM DUAL;

   -- +------------------------------------------------------------+
   -- + Material record assignment
   -- +------------------------------------------------------------+
   g_debug_point                                                         := 'PA_1080';
   l_mmti_rec.transaction_interface_id                                   := l_transaction_interface_id;
   l_mmti_rec.transaction_header_id                                      := l_transaction_interface_id;
   l_mmti_rec.process_flag                                               := 1;
   l_mmti_rec.validation_required                                        := 1;
   l_mmti_rec.transaction_mode                                           := 3;
   l_mmti_rec.lock_flag                                                  := 2;
   l_mmti_rec.last_update_date                                           := SYSDATE;
   l_mmti_rec.last_updated_by                                            := <user_id>;
   l_mmti_rec.creation_date                                              := SYSDATE;
   l_mmti_rec.created_by                                                 := <user_id>;
   l_mmti_rec.last_update_login                                          := -1;
   l_mmti_rec.transaction_date                                           := SYSDATE;
   l_mmti_rec.subinventory_code                                          := <subinventory_code>;
   l_mmti_rec.locator_id                                                 := <locator_id>;
   l_mmti_rec.transaction_source_id                                      := l_transaction_interface_id;
   l_mmti_rec.transaction_source_type_id                                 := 5;
   l_mmti_rec.transaction_action_id                                      := 1;
   l_mmti_rec.transaction_type_id                                        := 35;
   -- +------------------------------------------------------------+
   --Lot Records -- (for parent batch assign lot number as parent batch# )
   -- +------------------------------------------------------------+
   l_mmli_tbl (1).transaction_interface_id                               := l_transaction_interface_id;
   l_mmli_tbl (1).source_line_id                                         := l_mmti_rec.trx_source_line_id;
   l_mmli_tbl (1).last_update_date                                       := SYSDATE;
   l_mmli_tbl (1).last_updated_by                                        := l_user_id;
   l_mmli_tbl (1).creation_date                                          := SYSDATE;
   l_mmli_tbl (1).created_by                                             := l_user_id;
   l_mmli_tbl (1).last_update_login                                      := -1;
   l_mmli_tbl (1).transaction_quantity                                   <transaction_quantity>;
   l_mmli_tbl (1).process_flag                                           := 1;
   g_debug_point                                                         := 'PA_1090';
   DBMS_OUTPUT.put_line ('...Calling create_material_txn for WIP completion ...');
   gme_api_pub.create_material_txn (p_api_version =>                 2.0
                                   ,p_validation_level =>            l_validate_level
                                   ,p_init_msg_list =>               'F'
                                   ,p_commit =>                      'F'
                                   ,x_message_count =>               x_message_count
                                   ,x_message_list =>                x_message_list
                                   ,x_return_status =>               x_return_status
                                   ,p_org_code =>                    g_organization_code
                                   ,p_mmti_rec =>                    l_mmti_rec
                                   ,p_mmli_tbl =>                    l_mmli_tbl
                                   ,p_batch_no =>                    l_batch_no
                                   ,p_line_no =>                     l_line_no
                                   ,p_line_type =>                   l_line_type
                                   ,p_create_lot =>                  'T'
                                   ,p_generate_lot =>                'F'
                                   ,p_generate_parent_lot =>         'F'
                                   ,x_mmt_rec =>                     x_mmt_rec
                                   ,x_mmln_tbl =>                    x_mmln_tbl
                                   );
   DBMS_OUTPUT.put_line ('...End of create_material_txn for WIP completion ...return status' || x_return_status);

   IF x_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('... Batch# ' || l_batch_no || ' Ingredient Allocation call Successful');
      -- +------------------------------------------------------------+
      -- + SAVE THE CHANGES for product allocation of parent batch
      -- +------------------------------------------------------------+
      g_debug_point                               := 'PA_1100';
      gme_api_pub.save_batch (p_header_id =>                   l_batch_id, x_return_status => x_return_status);
   ELSE
      DBMS_OUTPUT.put_line ('Batch# ' || batch_no || '... Ingredient Allocation Call Failed');
   END IF;
END;

5.   Get onhand
   FUNCTION get_lot_onhand (
      p_inventory_item_id                           NUMBER
     ,p_organization_id                    IN       NUMBER
     ,p_lot_number                                  VARCHAR2
   )
      RETURN NUMBER
   IS
      l_onhand_qty                            NUMBER := NULL;
      l_api_return_status                     VARCHAR2 (1);
      l_qty_res_oh                            NUMBER;
      l_qty_res                               NUMBER;
      l_qty_sug                               NUMBER;
      l_qty_att                               NUMBER;
      l_qty_atr                               NUMBER;
      l_sqoh                                  NUMBER;
      l_srqoh                                 NUMBER;
      l_sqr                                   NUMBER;
      l_sqs                                   NUMBER;
      l_satt                                  NUMBER;
      l_sqtr                                  NUMBER;
      l_msg_count                             NUMBER;
      l_msg_data                              VARCHAR2 (1000);
      l_error_message                         VARCHAR2 (1000);
     g_debug_point                           VARCHAR2 (100);
   BEGIN
      g_debug_point                     := 'LO_1000';

      BEGIN
         g_debug_point                       := 'LO_1010';
         l_error_message                     := NULL;
         apps.inv_quantity_tree_grp.clear_quantity_cache;
         apps.inv_quantity_tree_pub.query_quantities (p_api_version_number          => 1.0
                                                     ,p_init_msg_lst                => apps.fnd_api.g_false
                                                     ,x_return_status               => l_api_return_status
                                                     ,x_msg_count                   => l_msg_count
                                                     ,x_msg_data                    => l_msg_data
                                                     ,p_organization_id             => p_organization_id
                                                     ,p_inventory_item_id           => p_inventory_item_id
                                                     ,p_tree_mode                   => 1
                                                     --apps.inv_quantity_tree_pub.g_transaction_mode
         ,                                            p_is_revision_control         => FALSE
                                                     ,p_is_lot_control              => TRUE
                                                     ,p_is_serial_control           => FALSE
                                                     ,p_revision                    => NULL
                                                     ,p_grade_code                  => NULL
                                                     ,p_lot_number                  => TRIM (p_lot_number)
                                                     ,p_subinventory_code           => g_subinventory_code
                                                     ,p_locator_id                  => g_locator_id
                                                     ,p_lpn_id                      => NULL
                                                     ,x_qoh                         => l_onhand_qty
                                                     ,x_rqoh                        => l_qty_res_oh
                                                     ,x_qr                          => l_qty_res
                                                     ,x_qs                          => l_qty_sug
                                                     ,x_att                         => l_qty_att
                                                     ,x_atr                         => l_qty_atr
                                                     ,x_sqoh                        => l_sqoh
                                                     ,x_srqoh                       => l_srqoh
                                                     ,x_sqr                         => l_sqr
                                                     ,x_sqs                         => l_sqs
                                                     ,x_satt                        => l_satt
                                                     ,x_satr                        => l_sqtr
                                                     );

         IF l_api_return_status != fnd_api.g_ret_sts_success
         THEN
            l_onhand_qty                     := 0;
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_qty_res_oh                     := 0;
            l_qty_res                        := 0;
            l_qty_sug                        := 0;
            l_qty_att                        := 0;
            l_qty_atr                        := 0;
            l_onhand_qty                     := 0;
      END;

      IF l_qty_att IS NULL
      THEN
         l_onhand_qty                     := 0;
      END IF;

      fnd_file.put_line (fnd_file.output
                        ,    '    Checking item onhand inventory_item_id: '
                          || p_inventory_item_id
                          || ' organization_id: '
                          || p_organization_id
                          || ' lot_number: '
                          || p_lot_number
                          || ' subinventory_code: '
                          || g_subinventory_code
                          || ' locator_id: '
                          || g_locator_id
                          || ' ONHAND: '
                          || l_onhand_qty
                        );
      RETURN (l_qty_att);
   END get_lot_onhand;




OPM Quality APIS
1.   Sample group creation
BEGIN
g_debug_point                                                 := 'FS_SG-0040';
l_sampling_events_rec.original_spec_vr_id                     := p_spec_vr_id;
-- +------------------------------------------------------------+
-- + sampling group creation API gmd_sampling_events_pvt.insert_row
-- +------------------------------------------------------------+
l_sampling_event_status                                       :=
                                                                    gmd_sampling_events_pvt.insert_row (l_sampling_events_rec, x_sampling_events_rec);

IF l_sampling_event_status
THEN
    DBMS_OUTPUT.put_line (   'Creating new Sample Group creation is successful
    Sampling Event ID:- '|| x_sampling_events_rec.sampling_event_id
                 );
ELSE
    DBMS_OUTPUT.put_line ('Sampling Group Creation Failed for Batch#: ' || p_batch_no
    || ' ' || SQLERRM);
END IF;
    END;

2.   Sample Creation API
            -- +------------------------------------------------------------+
            -- + Call the Sample Creation API
            -- +------------------------------------------------------------+
            gmd_samples_pub.create_samples (l_api_version
                                           ,NULL
                                           ,'F'
                                           ,NULL
                                           ,l_qc_samples_rec
                                           ,r_temp_hdr_rec.user_name
                                           ,'Y'   -- Find Matching Spec
                                           ,NULL
                                           ,NULL
                                           ,x_qc_samples_out_rec
                                           ,l_sampling_events_out
                                           ,l_sample_spec_disp
                                           ,l_event_spec_disp_rec
                                           ,l_results_tab
                                           ,l_spec_results_tab
                                           ,l_return_status
                                           ,x_message_count
                                           ,x_message_data
                                           );
3.   Record Test Results to the sample
            -- + ---------------------------------------------------+
            -- + Record Test Results to the sample
            -- + ---------------------------------------------------+
               gmd_results_pub.record_results (3.0
                                              ,NULL
                                              ,'F'
                                              ,NULL
                                              ,l_results_rec
                                              ,l_user_name
                                              ,x_results_rec
                                              ,x_spec_results_rec
                                              ,l_return_status
                                              ,x_message_count
                                              ,x_message_data
                                              );
4.   Validate the sample disposition
            -- + ---------------------------------------------------+
            -- + Validate the sample disposition created
            -- + Disposition details (ACCEPT-4A, CANCEL-7CN, COMPLETE-3C, REJECT-6RJ)
            -- + ---------------------------------------------------+
               gmd_disp_grp.update_sample_comp_disp (p_update_disp_rec             => l_update_disp_rec
                                                    ,p_to_disposition              => r_temp_hdr_rec.sample_status
                                                    ,x_return_status               => l_return_status
                                                    ,x_message_data                => x_message_data
                                                    );




OM APIs
1.   Order Import API
            -- +------------------------------------------------------------------+
            -- + Importing order                                                  +
            -- +------------------------------------------------------------------+
            l_msg_data                                                       := NULL;
            l_msg_count                                                      := NULL;
            g_debug_point                                                    := 'IM_3100';
            oe_order_pub.process_order (p_api_version_number =>          p_api_version_number
                                       ,p_init_msg_list =>               fnd_api.g_false
                                       ,p_return_values =>               fnd_api.g_false
                                       ,p_action_commit =>               fnd_api.g_false
                                       ,x_return_status =>               l_return_status
                                       ,x_msg_count =>                   l_msg_count
                                       ,x_msg_data =>                    l_msg_data
                                       ,p_header_rec =>                  l_header_rec
                                       ,p_header_adj_tbl =>              l_header_adj_tbl
                                       ,p_line_tbl =>                    l_line_tbl
                                       ,p_line_adj_tbl =>                l_line_adj_tbl
                                       ,p_action_request_tbl =>          l_action_request_tbl
                                       ,x_header_rec =>                  x_header_rec
                                       ,x_header_val_rec =>              x_header_val_rec
                                       ,x_header_adj_tbl =>              x_header_adj_tbl
                                       ,x_header_adj_val_tbl =>          x_header_adj_val_tbl
                                       ,x_header_price_att_tbl =>        x_header_price_att_tbl
                                       ,x_header_adj_att_tbl =>          x_header_adj_att_tbl
                                       ,x_header_adj_assoc_tbl =>        x_header_adj_assoc_tbl
                                       ,x_header_scredit_tbl =>          x_header_scredit_tbl
                                       ,x_header_scredit_val_tbl =>      x_header_scredit_val_tbl
                                       ,x_line_tbl =>                    x_line_tbl
                                       ,x_line_val_tbl =>                x_line_val_tbl
                                       ,x_line_adj_tbl =>                x_line_adj_tbl
                                       ,x_line_adj_val_tbl =>            x_line_adj_val_tbl
                                       ,x_line_price_att_tbl =>          x_line_price_att_tbl
                                       ,x_line_adj_att_tbl =>            x_line_adj_att_tbl
                                       ,x_line_adj_assoc_tbl =>          x_line_adj_assoc_tbl
                                       ,x_line_scredit_tbl =>            x_line_scredit_tbl
                                       ,x_line_scredit_val_tbl =>        x_line_scredit_val_tbl
                                       ,x_lot_serial_tbl =>              x_lot_serial_tbl
                                       ,x_lot_serial_val_tbl =>          x_lot_serial_val_tbl
                                       ,x_action_request_tbl =>          x_action_request_tbl


2.   Create reservation
   -- +------------------------------------------------------------+
   -- + Function to create reservation on order
   -- +------------------------------------------------------------+
   FUNCTION create_reservation (
      p_created_res                        IN OUT   inv_reservation_global.mtl_reservation_rec_type
     ,p_error_message                      OUT      VARCHAR2
   )
      RETURN NUMBER
   IS
      l_api_return_status                     VARCHAR2 (1);
      l_msg_count                             NUMBER;
      l_msg_data                              VARCHAR2 (1000);
      l_serial_no                             inv_reservation_global.serial_number_tbl_type;
      l_actual_sn                             inv_reservation_global.serial_number_tbl_type;
      l_over_reservation_flag                 NUMBER;
      l_quantity_reserved                     NUMBER;
      x_reservation_id                        NUMBER := NULL;
      l_partial_rsv_exists                    BOOLEAN DEFAULT FALSE;
      l_substitute_flag                       BOOLEAN DEFAULT FALSE;
      l_error_message                         VARCHAR2 (1000);
   BEGIN
      g_debug_point                       := 'LO_1000';

      BEGIN
         g_debug_point                                                  := 'LO_1010';
         l_error_message                                                := NULL;
         --p_created_res.demand_source_type_id                            := inv_reservation_global.g_source_type_oe;
         p_created_res.supply_source_type_id                            := inv_reservation_global.g_source_type_inv;
         p_created_res.ship_ready_flag                                  := NULL;
         p_created_res.attribute1                                       := NULL;
         p_created_res.attribute2                                       := NULL;
         p_created_res.attribute3                                       := NULL;
         p_created_res.attribute4                                       := NULL;
         p_created_res.attribute5                                       := NULL;
         p_created_res.attribute6                                       := NULL;
         p_created_res.attribute7                                       := NULL;
         p_created_res.attribute8                                       := NULL;
         p_created_res.attribute9                                       := NULL;
         p_created_res.attribute10                                      := NULL;
         p_created_res.attribute11                                      := NULL;
         p_created_res.attribute12                                      := NULL;
         p_created_res.attribute13                                      := NULL;
         p_created_res.attribute14                                      := NULL;
         p_created_res.attribute15                                      := NULL;
         p_created_res.attribute_category                               := NULL;
         p_created_res.pick_slip_number                                 := NULL;
         p_created_res.lot_number_id                                    := NULL;
         p_created_res.subinventory_id                                  := NULL;
         p_created_res.revision                                         := NULL;
         p_created_res.supply_source_line_detail                        := NULL;
         p_created_res.supply_source_name                               := NULL;
         p_created_res.supply_source_line_id                            := NULL;
         p_created_res.supply_source_header_id                          := NULL;
         p_created_res.external_source_line_id                          := NULL;
         p_created_res.external_source_code                             := NULL;
         p_created_res.autodetail_group_id                              := NULL;
         p_created_res.reservation_uom_id                               := NULL;
         p_created_res.secondary_uom_id                                 := NULL;
         p_created_res.primary_uom_id                                   := NULL;
         p_created_res.staged_flag                                      := NULL;
         p_created_res.detailed_quantity                                := 0;
         p_created_res.demand_source_delivery                           := NULL;
         p_created_res.demand_source_name                               := NULL;
         p_created_res.orig_supply_source_type_id                       := NULL;
         --p_created_res.orig_demand_source_type_id                       := NULL;
         --p_created_res.orig_demand_source_type_id                       := 8;
         --p_created_res.DEMAND_SOURCE_TYPE_ID                            := 8;
         p_created_res.orig_demand_source_header_id                     := NULL;
         p_created_res.orig_demand_source_line_id                       := NULL;
         p_created_res.supply_source_type_id                            := 13;
         p_created_res.crossdock_flag                                   := 'N';
         inv_quantity_tree_pvt.clear_quantity_cache;
         inv_reservation_pub.create_reservation (p_api_version_number          => 1.0
                                                ,p_init_msg_lst                => fnd_api.g_true
                                                ,x_return_status               => l_api_return_status
                                                ,x_msg_count                   => l_msg_count
                                                ,x_msg_data                    => l_msg_data
                                                ,p_rsv_rec                     => p_created_res
                                                ,p_serial_number               => l_serial_no
                                                ,x_serial_number               => l_actual_sn
                                                ,p_partial_reservation_flag    => fnd_api.g_false
                                                ,p_force_reservation_flag      => fnd_api.g_false
                                                ,p_validation_flag             => fnd_api.g_true
                                                ,p_over_reservation_flag       => l_over_reservation_flag
                                                ,x_quantity_reserved           => l_quantity_reserved
                                                ,x_reservation_id              => x_reservation_id
                                                ,p_partial_rsv_exists          => l_partial_rsv_exists
                                                ,p_substitute_flag             => l_substitute_flag
                                                );

         IF l_api_return_status != fnd_api.g_ret_sts_success
         THEN
            get_api_message (l_msg_count, l_error_message);

            IF l_error_message IS NULL
            THEN
               l_error_message                     := 'Error at reservation creation with API inv_reservation_pub.create_reservation';
            END IF;
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            x_reservation_id                     := NULL;
            l_error_message                      :=
                                                 'Error at reservation creation with API inv_reservation_pub.create_reservation SQLERRM: ' || SQLERRM;
      END;

      p_error_message                     := l_error_message;
      RETURN (x_reservation_id);
   END create_reservation;


3.   Delete reservation


   -- +------------------------------------------------------------+
   -- | GLOBAL PROCEDURE Delete_Reservation_opm
   -- | delete Allocations for OPM Orders
   -- +------------------------------------------------------------+
   PROCEDURE delete_reservation (
      p_order_header_id                    IN       NUMBER
     ,p_error_message                      OUT      VARCHAR2
   )
   IS
      -- +---------------------------------------------------------------------+
      -- + Cursors to get reservations for OPM orders                          +
      -- +---------------------------------------------------------------------+
      CURSOR c_reserv_opm (
         p_order_header_id                             NUMBER
      )
      IS
         SELECT mr.reservation_id
               ,ol.line_id
               ,ol.ship_from_org_id organization_id
               ,mr.primary_uom_code
               ,lot_number
               ,ol.order_quantity_uom
               ,ol.ordered_item
               ,ol.inventory_item_id
           FROM mtl_reservations mr
               ,oe_order_lines_all ol
          WHERE mr.demand_source_line_id = ol.line_id AND ol.line_id IN (SELECT line_id
                                                                           FROM oe_order_lines_all
                                                                          WHERE header_id = p_order_header_id);

      l_delete_rec                            inv_reservation_global.mtl_reservation_rec_type;
      l_serial_rec                            inv_reservation_global.serial_number_tbl_type;
      e_delete_res                            EXCEPTION;
      xout_return_status                      VARCHAR2 (2000);
      xout_msg_count                          NUMBER;
      xout_msg_data                           VARCHAR2 (2000);
   BEGIN
      g_debug_point                     := 'DRO_0010';

      FOR r_reserv IN c_reserv_opm (p_order_header_id)
      LOOP
         l_delete_rec.reservation_id                        := r_reserv.reservation_id;
         l_delete_rec.organization_id                       := r_reserv.organization_id;
         l_delete_rec.inventory_item_id                     := r_reserv.inventory_item_id;
         p_error_message                                    := NULL;
         g_msg_count                                        := NULL;
         xout_return_status                                 := NULL;
         xout_msg_count                                     := NULL;
         xout_msg_data                                      := NULL;
         inv_reservation_pub.delete_reservation (p_api_version_number          => 1.0
                                                ,p_init_msg_lst                => fnd_api.g_false
                                                ,x_return_status               => xout_return_status
                                                ,x_msg_count                   => xout_msg_count
                                                ,x_msg_data                    => xout_msg_data
                                                ,p_rsv_rec                     => l_delete_rec
                                                ,p_serial_number               => l_serial_rec
                                                );
         --end
         COMMIT;

         IF xout_return_status <> fnd_api.g_ret_sts_success
         THEN
            get_api_message (xout_msg_count, p_error_message);

            IF p_error_message IS NOT NULL
            THEN
               fnd_file.put_line (fnd_file.output
                                 , '    DELETE reservation error for ordered_item: ' || r_reserv.ordered_item || ' Error:- ' || p_error_message
                                 );
               RAISE e_delete_res;
            END IF;
         ELSE
            fnd_file.put_line (fnd_file.output, '    DELETE reservation success for ordered_item: ' || r_reserv.ordered_item);
         END IF;
      END LOOP;
   EXCEPTION
      WHEN e_delete_res
      THEN
         NULL;
   END delete_reservation;

4.   Shipping attributes API

               wsh_delivery_details_pub.update_shipping_attributes (p_api_version_number          => 1.0
                                                                   ,p_init_msg_list               => fnd_api.g_true
                                                                   ,p_commit                      => fnd_api.g_false
                                                                   ,x_return_status               => xout_return_status
                                                                   ,x_msg_count                   => xout_msg_count
                                                                   ,x_msg_data                    => xout_msg_data
                                                                   ,p_changed_attributes          => l_changed_attributes
                                                                   ,p_source_code                 => 'OE'
                                                                   );

5.   Confirm the delivery
            wsh_deliveries_pub.delivery_action (p_api_version_number          => 1.0
                                               ,p_init_msg_list               => fnd_api.g_true
                                               ,x_return_status               => xout_return_status
                                               ,x_msg_count                   => xout_msg_count
                                               ,x_msg_data                    => xout_msg_data
                                               ,p_action_code                 => 'CONFIRM'
                                               ,p_delivery_id                 => r_order.delivery_id
                                               ,p_sc_action_flag              => 'S'
                                               ,p_sc_intransit_flag           => 'Y'
                                               ,p_sc_close_trip_flag          => 'Y'
                                               ,p_sc_actual_dep_date  => NVL (TRUNC (r_order.date_shipped), TRUNC (SYSDATE))
                                               ,x_trip_id                     => l_trip_id
                                               ,x_trip_name                   => l_trip_name
                                               );
6.   Close the delivery
            wsh_ship_confirm_actions.interface_all (errbuf                        => l_errbuf
                                                   ,retcode                       => l_retcode
                                                   ,p_mode                        => 'ALL'
                                                   ,p_stop_id                     => NULL
                                                   ,p_delivery_id                 => l_delivery_id
                                                   ,p_log_level                   => 1
                                                   );

Inventory API

1.   UOM Conversion API
Once can use below API to get the UOM conversion working.

select decode (inv_convert.inv_um_convert ((select inventory_item_id FROM apps.mtl_system_items_b msib
                                           where segment1=  <Item> and rownum <2),
                                              3,
                                              1,                                
                                            <FROM_UM>,
                             (select primary_uom_code FROM apps.mtl_system_items_b msib
                                           where segment1=  <Item>  and rownum <2),
                                              NULL,
                                              NULL
                                             )
,-99999
,null
,inv_convert.inv_um_convert ((select inventory_item_id FROM apps.mtl_system_items_b msib
                                           where segment1 =  <Item>  and rownum <2),
                                              3,
                                              1,                                
                                           <FROM_UM>,
                             (select primary_uom_code FROM apps.mtl_system_items_b msib
                                           where segment1 =  <Item>  and rownum <2),
                                              NULL,
                                              NULL
                                             ))a
from dual  
2.   Inventory adjustment API

Identify the transaction (for example misc issue,receipt ), then
            SELECT mtl_material_transactions_s.NEXTVAL
              INTO l_transaction_header_id
              FROM DUAL;

            SELECT wip_job_number_s.NEXTVAL
              INTO l_schedule_number
              FROM DUAL;

            SELECT inv.mtl_material_transactions_s.NEXTVAL
              INTO l_transaction_interface_id
              FROM DUAL;
INSERT INTO mtl_transactions_interface
INSERT INTO mtl_transaction_lots_interface

Once inserted call the INVENTORY TRANSACTION WORKER
fnd_file.put_line (fnd_file.output, '  Calling INVENTORY TRANSACTION WORKER with transaction_header_id: ' || l_transaction_header_id);
            l_request_id                      :=
               fnd_request.submit_request ('INV'   -- Appl short name
                                          ,'INCTCW'   -- Inventory transaction worker
                                          ,NULL   -- Description
                                          ,NULL   -- Date and Time
                                          ,FALSE   -- Sub-request
                                          ,l_transaction_header_id
                                          ,3
                                          ,NULL
                                          ,NULL
                                          );
fnd_file.put_line (fnd_file.output, '  INVENTORY TRANSACTION WORKER request_id: ' || l_request_id);
            COMMIT;

IF NVL (l_request_id, -1) <= 0
THEN
fnd_file.put_line (fnd_file.output, 'ERROR-While submitting the INVENTORY TRANSACTION WORKER');
ELSE
fnd_file.put_line (fnd_file.LOG, '  INVENTORY TRANSACTION WORKER Waiting. The request ID is : ' || l_request_id);
               l_wait_status                                              :=
                  fnd_concurrent.wait_for_request (request_id                    => l_request_id
                                                  ,INTERVAL                      => 05
                                                  ,phase                         => l_phase
                                                  ,status                        => l_status
                                                  ,dev_phase                     => l_dev_phase
                                                  ,dev_status                    => l_dev_status
                                                  ,MESSAGE                       => l_message
                                                  );
END IF;



WMS APIS

1.   Generate LPNS- Oracle WMS Provides the LPN Generation program though sharing the API way to get the LPN generated.

   -- +------------------------------------------------------------+
   -- + get the lpn
   -- +------------------------------------------------------------+
   FUNCTION generate_lpn (
      p_organization_id                             NUMBER
     ,p_lpn_prefix                                  VARCHAR2
     ,p_lpn_out                            OUT      VARCHAR2
     ,p_error_message                      OUT      VARCHAR2
   )
      RETURN NUMBER
   IS
      l_lpn_id_out                            NUMBER;
      l_lpn_out                               VARCHAR2 (100) := NULL;
      l_process_id                            NUMBER;
      x_msg_count                             NUMBER;
      x_msg_data                              VARCHAR2 (2000) := NULL;
      x_return_status                         VARCHAR2 (100);
      l_error_message                         VARCHAR2 (2000) := NULL;
      e_lpn_exception                         EXCEPTION;
   BEGIN
      BEGIN
         g_debug_point                       := 'GNL_0010';
         l_lpn_id_out                        := NULL;
         fnd_file.put_line (fnd_file.output
                           , '    Generated LPN paremeters p_organization_id: ' || p_organization_id || ' p_lpn_prefix : ' || p_lpn_prefix
                           );
         -- +------------------------------------------------------------+
         -- + generate the lpn
         -- +------------------------------------------------------------+
         wms_container_pvt.generate_lpn (p_api_version                 => 1.0
                                        ,p_init_msg_list               => fnd_api.g_false
                                        ,p_commit                      => fnd_api.g_false
                                        ,x_return_status               => x_return_status
                                        ,x_msg_count                   => x_msg_count
                                        ,x_msg_data                    => x_msg_data
                                        ,p_organization_id             => p_organization_id
                                        ,p_container_item_id           => NULL
                                        ,p_revision                    => NULL
                                        ,p_lot_number                  => NULL
                                        ,p_from_serial_number          => NULL
                                        ,p_to_serial_number            => NULL
                                        ,p_subinventory                => NULL
                                        ,p_locator_id                  => NULL
                                        ,p_lpn_prefix                  => p_lpn_prefix
                                        ,p_lpn_suffix                  => NULL
                                        ,p_starting_num                => NULL
                                        ,p_quantity                    => 1
                                        ,p_source                      => NULL
                                        ,p_cost_group_id               => NULL
                                        ,p_source_type_id              => NULL
                                        ,p_source_header_id            => NULL
                                        ,p_source_name                 => NULL
                                        ,p_source_line_id              => NULL
                                        ,p_source_line_detail_id       => NULL
                                        ,p_lpn_id_out                  => l_lpn_id_out
                                        ,p_lpn_out                     => l_lpn_out
                                        ,p_process_id                  => l_process_id
                                        ,p_total_length                => NULL
                                        ,p_ucc_128_suffix_flag         => 2
                                        );

         IF x_return_status <> fnd_api.g_ret_sts_success
         THEN
            xxpom_edi945_pkg.get_api_message (x_msg_count, l_error_message);

            IF l_error_message IS NOT NULL
            THEN
               fnd_file.put_line (fnd_file.output, '    LPN Generation error:- ' || l_error_message);
               p_error_message                     := LPN Generation error:- ' || l_error_message;
               RAISE e_lpn_exception;
            END IF;
         ELSE
            fnd_file.put_line (fnd_file.output
                              ,    '    Generated LPN sucessfully with prefix: '
                                || p_lpn_prefix
                                || ' l_lpn_out: '
                                || l_lpn_out
                                || ' l_lpn_id_out: '
                                || l_lpn_id_out
                              );
         END IF;

         -- +------------------------------------------------------------+
         -- + validate the lpn
         -- +------------------------------------------------------------+
         g_debug_point                       := 'GNL_0020';
         inv_rcv_common_apis.create_lpn (p_organization_id, l_lpn_out, l_lpn_id_out, x_return_status, x_msg_data);
         l_error_message                     := NULL;

         IF x_return_status <> fnd_api.g_ret_sts_success
         THEN
            xxpom_edi945_pkg.get_api_message (x_msg_count, l_error_message);

            IF l_error_message IS NOT NULL
            THEN
               fnd_file.put_line (fnd_file.output, '    Generate LPN Vaidation err:- ' || l_error_message);
               p_error_message                     := 'LPN Vaidation err:- ' || l_error_message;
               RAISE e_lpn_exception;
            END IF;
         ELSE
            fnd_file.put_line (fnd_file.output
                              ,    '    Generated LPN validated sucessfully with prefix: '
                                || p_lpn_prefix
                                || ' l_lpn_out: '
                                || l_lpn_out
                                || ' l_lpn_id_out: '
                                || l_lpn_id_out
                              );
         END IF;
      EXCEPTION
         WHEN e_lpn_exception
         THEN
            l_lpn_id_out                     := NULL;
            l_lpn_out                        := NULL;
         WHEN OTHERS
         THEN
            l_lpn_id_out                     := NULL;
      END;

      p_lpn_out                     := l_lpn_out;
      RETURN (l_lpn_id_out);
   END generate_lpn;


2.   LPN SPLIT API- with WMS LPNS utilized as full qty below API can be used to split the lpn for desired qty. Other option can be loose all the LPN qty and create new lpns with desired qty.
   PROCEDURE split_lpn (
      p_organization_id                    IN       NUMBER
     ,p_from_lpn_number                    IN       VARCHAR2
     ,p_from_lpn_id                        IN       NUMBER
     ,p_to_lpn_number                      IN       VARCHAR2
     ,p_to_lpn_id                          IN       NUMBER
     ,p_inventory_item_id                  IN       NUMBER
     ,p_subinventory_code                  IN       VARCHAR2
     ,p_locator_id                         IN       NUMBER
     ,p_lot_number                         IN       VARCHAR2
     ,p_uom                                IN       VARCHAR2
     ,p_quantity                           IN       NUMBER
     ,x_error_message                      OUT      VARCHAR
   )
   IS
      l_container_item_id                     NUMBER;
      l_trx_hdr_id                            NUMBER;
      l_trx_tmp_id                            NUMBER;
      l_ser_trx_id                            NUMBER;
      l_api_return                            NUMBER;
      l_msg_count                             NUMBER;
      l_return_status                         VARCHAR2 (30);
      l_msg_data                              VARCHAR2 (2000) := NULL;
      l_error_message                         VARCHAR2 (2000) := NULL;
      e_split_exception                       EXCEPTION;
      l_sec_uom                               VARCHAR2 (30) := NULL;
      l_sec_qty                               NUMBER := NULL;
   BEGIN
      g_debug_point                       := 'SPL_0010';
      fnd_file.put_line (fnd_file.output
                        ,    '    SPLIT LPN API PARAM p_organization_id: '
                          || p_organization_id
                          || ' p_from_lpn_number '
                          || p_from_lpn_number
                          || ' p_from_lpn_id '
                          || p_from_lpn_id
                          || ' p_to_lpn_number '
                          || p_to_lpn_number
                          || ' p_to_lpn_id '
                          || p_to_lpn_id
                          || ' p_inventory_item_id '
                          || p_inventory_item_id
                          || ' p_subinventory_code '
                          || p_subinventory_code
                          || ' p_locator_id '
                          || p_locator_id
                          || ' p_lot_number '
                          || p_lot_number
                          || ' p_uom '
                          || p_uom
                          || ' p_quantity '
                          || p_quantity
                        );
      l_error_message                     := NULL;
      g_debug_point                       := 'SPL_0012';

      FOR l_sec_uom_rec IN (SELECT msi.secondary_uom_code
                              FROM apps.mtl_system_items_b msi
                             WHERE -1 = -1
                               AND msi.secondary_uom_code IS NOT NULL
                               AND msi.tracking_quantity_ind = 'PS'
                               AND msi.dual_uom_control = 2
                               AND msi.organization_id = p_organization_id
                               AND msi.inventory_item_id = p_inventory_item_id)
      LOOP
         g_debug_point                     := 'SPL_0012';
         l_sec_uom                         := l_sec_uom_rec.secondary_uom_code;
         l_sec_qty                         :=
            inv_convert.inv_um_convert (item_id                       => p_inventory_item_id
                                       ,lot_number                    => p_lot_number
                                       ,organization_id               => p_organization_id
                                       ,PRECISION                     => NULL
                                       ,from_quantity                 => p_quantity
                                       ,from_unit                     => p_uom
                                       ,to_unit                       => l_sec_uom
                                       ,from_name                     => NULL
                                       ,to_name                       => NULL
                                       );

         IF l_sec_qty = -99999
         THEN
            l_error_message                     := ' Error while pulling the Secondory Qty during split lpn inv_convert.inv_um_convert Error';
            fnd_file.put_line (fnd_file.output, '    ' || l_error_message);
            RAISE e_split_exception;
         ELSE
            fnd_file.put_line (fnd_file.output, '    SPLIT LPN API PARAM l_sec_qty: ' || l_sec_qty || ' l_sec_uom: ' || l_sec_uom);
         END IF;
      END LOOP;

      g_debug_point                       := 'SPL_0015';

      SELECT wlpn.inventory_item_id
        INTO l_container_item_id
        FROM apps.wms_license_plate_numbers wlpn
       WHERE -1 = -1 AND wlpn.lpn_id = p_from_lpn_id;

      l_msg_data                          := NULL;
      apps.wms_container_pub.modify_lpn_wrapper (p_api_version                 => 1.0
                                                ,p_init_msg_list               => apps.fnd_api.g_true
                                                ,p_commit                      => apps.fnd_api.g_false
                                                ,p_validation_level            => apps.fnd_api.g_valid_level_full
                                                ,x_return_status               => l_return_status
                                                ,x_msg_count                   => l_msg_count
                                                ,x_msg_data                    => l_msg_data
                                                ,p_lpn_id                      => p_to_lpn_id
                                                ,p_inventory_item_id           => l_container_item_id
                                                );

      IF l_return_status <> fnd_api.g_ret_sts_success
      THEN
         get_api_message (l_msg_count, l_error_message);

         IF l_error_message IS NOT NULL
         THEN
            fnd_file.put_line (fnd_file.output, '    API wms_container_pub.modify_lpn_wrapper ERR:- ' || l_error_message);
            x_error_message                     := 'API wms_container_pub.modify_lpn_wrapper ERR:- ' || l_error_message;
            RAISE e_split_exception;
         END IF;
      ELSE
         fnd_file.put_line (fnd_file.output, '    API wms_container_pub.modify_lpn_wrapper success ');
      END IF;

      l_error_message                     := NULL;

      SELECT apps.mtl_material_transactions_s.NEXTVAL
        INTO l_trx_hdr_id
        FROM DUAL;

      g_debug_point                       := 'SPL_0020';
      l_api_return                        :=
         apps.inv_trx_util_pub.insert_line_trx (p_trx_hdr_id                  => l_trx_hdr_id
                                               ,p_trx_type_id                 => 89
                                               ,   -- Container Split
                                                p_trx_src_type_id             => 13
                                               ,   -- Inventory
                                                p_trx_action_id               => 52
                                               ,   -- Container Split
                                                p_org_id                      => p_organization_id
                                               ,p_item_id                     => p_inventory_item_id
                                               ,p_subinv_code                 => p_subinventory_code
                                               ,p_locator_id                  => p_locator_id
                                               ,p_from_lpn_id                 => p_from_lpn_id
                                               ,p_xfr_lpn_id                  => p_to_lpn_id
                                               ,p_uom                         => p_uom
                                               ,p_trx_qty                     => p_quantity
                                               ,p_pri_qty                     => p_quantity
                                               ,p_secondary_uom               => l_sec_uom
                                               ,p_secondary_trx_qty           => l_sec_qty
                                               ,p_date                        => SYSDATE
                                               ,p_user_id                     => gk_user_id
                                               ,x_trx_tmp_id                  => l_trx_tmp_id
                                               ,x_proc_msg                    => l_error_message
                                               ,p_planning_org_id             => NULL
                                               ,p_planning_tp_type            => NULL
                                               ,p_owning_org_id               => NULL
                                               ,p_owning_tp_type              => NULL
                                               ,p_move_order_header_id        => NULL
                                               ,p_serial_allocated_flag       => NULL
                                               ,p_transaction_status          => NULL
                                               ,p_ship_to_location_id         => NULL
                                               ,p_opm_org_in_xfer             => NULL
                                               );

      IF l_api_return != 0
      THEN
         l_error_message                     := ' Error While inserting insert_line_trx for split LPN: ' || l_error_message;
         RAISE e_split_exception;
      END IF;

      IF p_lot_number IS NOT NULL
      THEN
         g_debug_point                     := 'SPL_0030';
         l_api_return                      :=
            apps.inv_trx_util_pub.insert_lot_trx (p_trx_tmp_id                  => l_trx_tmp_id
                                                 ,p_user_id                     => gk_user_id
                                                 ,p_lot_number                  => p_lot_number
                                                 ,p_trx_qty                     => p_quantity
                                                 ,p_pri_qty                     => p_quantity
                                                 ,p_secondary_uom               => l_sec_uom
                                                 ,p_secondary_qty               => l_sec_qty
                                                 ,x_ser_trx_id                  => l_ser_trx_id
                                                 ,x_proc_msg                    => l_error_message
                                                 );

         IF l_api_return != 0
         THEN
            l_error_message                     := ' Inserting into insert_lot_trx Err-' || l_error_message;
            RAISE e_split_exception;
         END IF;
      END IF;

      g_debug_point                       := 'SPL_0040';
      l_api_return                        :=
         apps.inv_lpn_trx_pub.process_lpn_trx (p_trx_hdr_id                  => l_trx_hdr_id
                                              ,p_business_flow_code          => 20
                                              ,p_commit                      => fnd_api.g_false
                                              ,p_proc_mode                   => NULL
                                              ,p_process_trx                 => fnd_api.g_true
                                              ,p_atomic                      => fnd_api.g_false
                                              ,p_init_msg_list               => TRUE
                                              ,x_proc_msg                    => l_error_message
                                              );

      IF l_api_return != 0
      THEN
         l_error_message                     := ' Inserting into inv_lpn_trx_pub.process_lpn_trx ' || l_error_message;
         RAISE e_split_exception;
      END IF;
   EXCEPTION
      WHEN e_split_exception
      THEN
         x_error_message                     := l_error_message;
      WHEN OTHERS
      THEN
         x_error_message                     := 'LPN SPLIT Error : ' || SQLERRM;
   END split_lpn;


To get the API errors use below procedure
   PROCEDURE get_api_message (
      p_msg_count                          IN       NUMBER
     ,p_api_message                        OUT      VARCHAR2
   )
   IS
      x_msg_data                              VARCHAR2 (2000);
   BEGIN
      IF p_msg_count > 0
      THEN
         FOR i IN 1 .. p_msg_count
         LOOP
            fnd_msg_pub.get (p_msg_index => i, p_data => x_msg_data, p_encoded => fnd_api.g_false, p_msg_index_out => g_dummy);

            IF i = 1
            THEN
               p_api_message                     := x_msg_data;
            ELSE
               p_api_message                     := p_api_message || CHR (10) || x_msg_data;
            END IF;
         END LOOP;
      ELSE
         p_api_message                     := NULL;
      END IF;
   END get_api_message;



3.   Pick release API –
Pick release in WMS happens in three steps process_lpn, process_line and loan_lpn

               wsh_delivery_details_pub.autocreate_deliveries (p_api_version_number          => 1.0
                                                              ,p_init_msg_list               => fnd_api.g_false
                                                              ,p_commit                      => fnd_api.g_false
                                                              ,x_return_status               => l_return_status
                                                              ,x_msg_count                   => l_msg_count
                                                              ,x_msg_data                    => l_msg_data
                                                              ,p_line_rows                   => l_dd_create_ids
                                                              ,x_del_rows                    => l_delivery_ids
                                                              );

            -- +------------------------------------------------------+
            -- + Call the WMS_DIRECT_SHIP_PVT.PROCESS_LPN
            -- +------------------------------------------------------+
            fnd_file.put_line (fnd_file.output
                              ,    '    .. Call WMS_DIRECT_SHIP_PVT.PROCESS_LPN LPN# '
                                || l_processing_lpn
                                || ' LPN ID: '
                                || l_processing_lpn_id
                                || ' p_dock_locator_id: '
                                || p_dock_locator_id
                              );
            l_api_message                       := NULL;
            x_return_status                     := NULL;
            x_msg_count                         := NULL;
            x_msg_data                          := NULL;
            g_debug_point                       := 'WLPN_0200';
            wms_direct_ship_pvt.process_lpn (p_lpn_id                      => l_processing_lpn_id
                                            ,p_org_id                      => p_organization_id
                                            ,p_dock_door_id                => p_dock_locator_id   --p_locator_id
                                            ,x_remaining_qty               => x_remaining_qty
                                            ,x_num_line_processed          => x_num_line_processed
                                            ,x_project_id                  => x_project_id
                                            ,x_task_id                     => x_task_id
                                            ,x_cross_project_allowed       => x_cross_project_allowed
                                            ,x_cross_unit_allowed          => x_cross_unit_allowed
                                            ,x_group_by_customer_flag      => x_group_by_customer_flag
                                            ,x_group_by_fob_flag           => x_group_by_fob_flag
                                            ,x_group_by_freight_terms_flag => x_group_by_freight_terms_flag
                                            ,x_group_by_intmed_ship_flag   => x_group_by_intmed_ship_flag
                                            ,x_group_by_ship_method_flag   => x_group_by_ship_method_flag
                                            ,x_group_by_ship_to_loc_value  => x_group_by_ship_to_loc_value
                                            ,x_group_by_ship_from_loc_value => x_group_by_ship_from_loc_value
                                            ,x_group_by_customer_value     => x_group_by_customer_value
                                            ,x_group_by_fob_value          => x_group_by_fob_value
                                            ,x_group_by_freight_terms_value => x_group_by_freight_terms_value
                                            ,x_group_by_intmed_value       => x_group_by_intmed_value
                                            ,x_group_by_ship_method_value  => x_group_by_ship_method_value
                                            ,x_ct_wt_enabled               => x_ct_wt_enabled
                                            ,x_return_status               => x_return_status
                                            ,x_msg_count                   => x_msg_count
                                            ,x_msg_data                    => x_msg_data
                                            );

            IF UPPER (x_return_status) <> 'S'
            THEN
               g_debug_point                           := 'WLPN_0210';
               get_api_message (NVL (x_msg_count, 1), l_api_message);
               r_lpn.error_message                     := 'WMS_DIRECT_SHIP_PVT.PROCESS_LPN ERR- ' || l_api_message;
               r_lpn.status                            := g_error_stage_status;
               RAISE e_lpn_process;
            ELSE
               fnd_file.put_line (fnd_file.output, '    .. WMS_DIRECT_SHIP_PVT.PROCESS_LPN success');
            END IF;

            g_debug_point                       := 'WLPN_0220';
            -- +------------------------------------------------------------+
            -- + Process the LPN which is loaded against a dock doar and assign order information (header id/line id) against the LPN
            -- + this process will do the reservations on the order
            -- +------------------------------------------------------------+
            fnd_file.put_line (fnd_file.output, '    .. Call WMS_DIRECT_SHIP_PVT.PROCESS_LINE ');
            l_api_message                       := NULL;
            x_return_status                     := NULL;
            x_msg_count                         := NULL;
            x_msg_data                          := NULL;
            g_debug_point                       := 'WLPN_0230';
            wms_direct_ship_pvt.process_line (p_lpn_id                      => l_processing_lpn_id
                                             ,p_org_id                      => p_organization_id
                                             ,p_dock_door_id                => p_dock_locator_id   --p_locator_id
                                             ,p_order_header_id             => p_order_header_id
                                             ,p_order_line_id               => r_lpn.line_id
                                             ,p_inventory_item_id           => r_lpn.inventory_item_id
                                             ,p_revision                    => NULL
                                             ,p_end_item_unit_number        => NULL
                                             ,p_ordered_quantity            => r_lpn.order_quantity
                                             ,p_processed_quantity          => r_lpn.quantity_shipped
                                             ,p_date_requested              => SYSDATE
                                             ,p_primary_uom_code            => r_lpn.order_quantity_uom
                                             ,x_return_status               => x_return_status
                                             ,x_msg_count                   => x_msg_count
                                             ,x_msg_data                    => x_msg_data
                                             ,x_remaining_quantity          => x_remaining_qty
                                             );

            IF UPPER (x_return_status) <> 'S'
            THEN
               g_debug_point                           := 'WLPN_0240';
               get_api_message (NVL (x_msg_count, 1), l_api_message);
               r_lpn.error_message                     := 'WMS_DIRECT_SHIP_PVT.PROCESS_LINE ERR- ' || l_api_message;
               r_lpn.status                            := g_error_stage_status;
               RAISE e_lpn_process;
            ELSE
               fnd_file.put_line (fnd_file.output, '    .. WMS_DIRECT_SHIP_PVT.PROCESS_LINE success');
            END IF;

            g_debug_point                       := 'WLPN_0250';
            -- +------------------------------------------------------------+
            -- + Stage the LPN and finally pick confirm the order
            -- +------------------------------------------------------------+
            fnd_file.put_line (fnd_file.output, '    .. Call WMS_DIRECT_SHIP_PVT.LOAD_LPN ');
            l_api_message                       := NULL;
            x_return_status                     := NULL;
            x_msg_count                         := NULL;
            x_msg_data                          := NULL;
            g_debug_point                       := 'WLPN_0260';
            wms_direct_ship_pvt.load_lpn (p_lpn_id                      => l_processing_lpn_id
                                         ,p_org_id                      => p_organization_id
                                         ,p_dock_door_id                => p_dock_locator_id   --p_locator_id
                                         ,x_return_status               => x_return_status
                                         ,x_msg_count                   => x_msg_count
                                         ,x_msg_data                    => x_msg_data
                                         );

            IF UPPER (x_return_status) <> 'S'
            THEN
               g_debug_point                           := 'WLPN_0270';
               get_api_message (NVL (x_msg_count, 1), l_api_message);
               r_lpn.error_message                     := 'WMS_DIRECT_SHIP_PVT.LOAD_LPN ERR- ' || l_api_message;
               r_lpn.status                            := g_error_stage_status;
               RAISE e_lpn_process;
            ELSE
               fnd_file.put_line (fnd_file.output, '    .. WMS_DIRECT_SHIP_PVT.LOAD_LPN success');
            END IF;



4.    Backorder the LPN - Unload the LPN API

   PROCEDURE process_lpn_unload_truck (
      p_organization_id                    IN       NUMBER
     ,p_lpn_id                             IN       NUMBER
     ,p_lpn_number                         IN       VARCHAR2
     ,p_api_message                        OUT      VARCHAR2
   )
   IS
      l_api_message                           VARCHAR2 (2000) := NULL;
      x_return_status                         VARCHAR2 (2000);
      x_msg_count                             NUMBER;
      x_msg_data                              VARCHAR2 (2000);
   BEGIN
      g_debug_point                       := 'WLPNUT_0400';
      fnd_file.put_line (fnd_file.output
                        ,    '     There exists error while LPN LOAD hence calling WMS_DIRECT_SHIP_PVT.unload_truck  LPN# '
                          || p_lpn_number
                          || ' p_lpn_id: '
                          || p_lpn_id
                          || ' p_organization_id: '
                          || p_organization_id
                        );
      g_debug_point                       := 'WLPNUT_0410';
      l_api_message                       := NULL;
      x_return_status                     := NULL;
      x_msg_count                         := NULL;
      x_msg_data                          := NULL;
      g_debug_point                       := 'WLPNUT_0420';
      wms_direct_ship_pvt.unload_truck (p_outermost_lpn_id            => p_lpn_id
                                       ,p_org_id                      => p_organization_id
                                       ,p_relieve_rsv                 => 'Y'
                                       ,x_return_status               => x_return_status
                                       ,x_msg_count                   => x_msg_count
                                       ,x_msg_data                    => x_msg_data
                                       );

      IF UPPER (x_return_status) <> 'S'
      THEN
         g_debug_point                     := 'WLPNUT_0430';
         get_api_message (NVL (x_msg_count, 1), l_api_message);

         IF l_api_message IS NOT NULL
         THEN
            fnd_file.put_line (fnd_file.output, '    .. WMS_DIRECT_SHIP_PVT.unload_truck ERR- ' || l_api_message);
         END IF;
      ELSE
         fnd_file.put_line (fnd_file.output, '    .. WMS_DIRECT_SHIP_PVT.unload_truck success');
      END IF;

      p_api_message                       := l_api_message;
   END process_lpn_unload_truck;





Thanks,
Ajay

Filed under , , , , , having

11 comments:

Marriswamy said...

Hi ajay,

Very Nice Blog,It is very Useful to every One, Thanks Ajay for giving nice stuff...
Thanks
Swamy

Marriswamy said...

But One thing is, i am unable to copy

Anonymous said...

Nice blog but you could have left it to be able to be copied. Remember that you may have had copied it from somewhere too.

Ravindra Tripathi said...

Free Webinar : Oracle E-Business Suite - Salesforce.com Integration via Oracle's API on Aug 26th, 2015 at 3PM EST.

Visit:  http://blog.optiosys.com

Anonymous said...

Ajay, you went through a lot of effort to stop cut and paste. But remember where you cut and paste your code from. If you think your code is so good, then don't share. Keep it to yourself

Unknown said...

How to Split Delivery Lines into 2 , creating a new Delivery and assigning Items to the LPN and new Delivery for the splitted lines

arif said...

Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com for details.

Brian Günter Haugen said...

Nama..Brian Günter Haugen
Tinggal di ..... Rhine-Westphalia
Utara Profesion ...... ahli perniagaan
Jumlah pinjaman .... € 250,000
Faedah pinjaman .... 1%
Agama: Katolik E-mel saya untuk setiap ekuire; brianhuagen101@gmail.com Dikatakan bahawa hidup hanya dapat dinikmati sepenuhnya jika ada penderitaan dan keperitan. Saya gembira dan bersyukur kerana saya bersama Mizuho Bank, Ltd. Branch Düsseldorf menerima pinjaman sebanyak € 250,000 dalam akaun bank. Adakah anda seorang lelaki atau wanita yang telah memohon pinjaman dan telah ditolak oleh bank anda? Saya ingin memberitahu anda hari ini bahawa anda boleh mendapatkan pinjaman dari syarikat dalam talian terbaik dan anda tidak perlu mengisi banyak dokumen, mereka juga mempunyai kadar faedah 1% yang baik. Saya diperkenalkan dengan SYARIKAT PINJAMAN AASIMAHA ADILA oleh seorang rakan yang telah menerima pinjaman daripada mereka. Saya memerlukan wang tunai dan pergi ke bank saya. Mereka memberitahu saya bahawa kerana kredit buruk saya tidak layak mendapat pinjaman, dan kadar faedah bank tinggi. Saya gembira sekarang kerana dapat membina perniagaan besar impian saya. Pembayaran saya akan bermula tidak lama lagi. Berhentilah membuang masa dengan bank perdagangan dengan kadar faedah yang tinggi dan terapkan AASIMAHA ADILA AHMED LOAN FIRM untuk mendapatkan dana pinjaman anda. Mereka sangat pantas mendapatkan pinjaman yang diluluskan dan memberi kredit ke akaun anda pada hari yang sama dengan permohonan. Tidak, terlalu banyak proses dan prosedur yang terlibat, seperti halnya bank. Syarikat pinjaman tidak tahu bahawa saya berkongsi berita baik ini. Anda boleh menghubungi mereka melalui e-mel. Mohon sekarang ..... (aasimahaadilaahmed.loanfirm@gmail.com) E-mel saya untuk setiap ekuire; brianhuagen101@gmail.com

Fitriani Suhesti said...

Saya Fitriani Suhesti dari kota Medan. Saya melihat postingan ibu Yanti Ari dengan detil bagaimana dia meminjam dari Guaranty Trust Loans untuk memulihkan usahanya dan juga mengembangkan usahanya. Ibu berkata dia tinggal di kota Medan dan saya tinggal di kota itu jadi saya memutuskan untuk menghubunginya karena saya telah kehilangan 16 juta karena penipuan hanya karena saya membutuhkan pinjaman untuk mengembangkan bisnis saya.

Di bawah ini adalah informasinya yang saya lihat dan yang saya hubungi.

nama ibu: Yanti Ari
nomor telepon: +62821-1644-0184
Nomor Whatsapp: +62821-1644-0184
Kota: Medan
email ibu: ariy6261@gmail.com
Setelah saya menghubungi ibu yanti dia menceritakan semua yang perlu saya ketahui dan dia berkata kepada saya bahwa setelah saya menghubungi Guaranty Trust Loans dan menerima pinjaman saya, saya harus mencoba untuk berbagi cerita saya agar orang lain yang membutuhkan pinjaman dapat diandalkan perusahaan dapat diselamatkan dari orang-orang curang yang bukan pemberi pinjaman.

Jadi saya mengajukan pinjaman sejumlah 500 juta Rupiah, dan mereka meminta kredensial saya yang saya serahkan dan setelah mereka selesai memverifikasi detail saya, pinjaman itu disetujui untuk saya dan saya pikir itu adalah lelucon dan mungkin itu salah satu penipuan yang membuat saya kehilangan uang, tetapi karena ibu Yanti berasal dari Medan dan saya melihat buktinya jadi saya mempercayai mereka, saya kagum ketika saya mendapatkan pinjaman dalam waktu kurang dari 24 jam dengan bunga rendah 2% tanpa jaminan. Saya sangat senang bahwa Allah menggunakan ibu yanti yang menghubungi mereka dan memperkenalkan saya kepada mereka dan karena saya diselamatkan dari pemberi pinjaman palsu dan juga akan mewujudkan impian bisnis saya.

Jadi saya menyarankan semua orang yang tinggal di Indonesia, Malaysia dan belahan dunia lain yang membutuhkan pinjaman untuk satu tujuan atau yang lain untuk menghubungi Guaranty Trust Loans dan di bawah ini adalah detail mereka
email: (anamichaelguarantytrustloans@gmail.com)
Anda juga dapat menghubungi perusahaan dengan nomor ini: +1(470)481-0039
jangan ragu untuk menghubungi saya jika Anda memerlukan informasi lebih lanjut melalui email: (fitrianisuhesti90@gmail.com)
Terima kasih telah membaca cerita saya, dan semoga Allah terus melindungi dan memberkati kita semua bahkan di masa-masa sulit karena pandemi virus corona.

Manis Ibu said...

BAGAIMANA SAYA MENDAPATKAN PINJAMAN SAYA
Saya Abdullah Nofia yang tinggal di Papua, saya di sini untuk memberi kesaksian seorang Lender Pinjaman yang baik yang menunjukkan cahaya kepada saya setelah ditipu 3 kali berbeda di Internet oleh beberapa pemberi pinjaman yang tidak lain adalah palsu,
Mereka semua berjanji untuk memberi saya pinjaman setelah membuat saya membayar banyak kepada mereka sebagai biaya yang tidak menghasilkan apa-apa dan sebesar tidak ada hasil positif. saya kehilangan uang hasil kerja keras saya dan itu sangat membuat frustrasi.

Suatu hari ketika menelusuri internet saya menemukan kesaksian seorang wanita yang juga ditipu dan akhirnya dikaitkan dengan perusahaan pinjaman yang sah, ONE BILLION RISING FUND  dan di mana dia akhirnya mendapatkan pinjamannya, saya memutuskan untuk menghubungi dia dan menjelaskan tekanan keuangan saya. kepadanya dan jika dia bisa membantu saya untuk berbicara dengan perusahaan,

 Saya kemudian menceritakan kepada mereka kisah saya tentang bagaimana saya ditipu oleh 3 pemberi pinjaman yang berbeda yang tidak melakukan apa pun selain merampok uang saya. Saya menjelaskan kepada perusahaan melalui surat dan semua yang mereka katakan kepada saya adalah untuk tidak menangis lagi karena saya akan mendapatkan pinjaman saya di perusahaan mereka dan saya juga telah membuat pilihan yang tepat dengan menghubungi mereka. Saya mengisi formulir permohonan pinjaman dan melanjutkan dengan semua yang diperlukan dari saya dan saya terkejut, saya diberi pinjaman sebesar Rp 200 juta oleh perusahaan besar ini, ONE BILLION RISING FUND, semua berkat JAMES (direktur transfer), seorang yang takut akan Tuhan. pria dan mentor dan di sini saya hari ini bahagia karena perusahaan ini telah memberi saya pinjaman jadi saya bersumpah pada diri sendiri bahwa saya akan terus bersaksi tentang pekerjaan baik mereka dalam hidup saya tentang bagaimana saya mendapatkan pinjaman saya.
 Jika Anda mencari cara mendapatkan pinjaman, silakan hubungi

NAMA ::ONE BILLION RISING FUNDGmail ::: onebillionrisingfund@gmail.com

                                          HUBUNGI SAYA

Namaku ::::::::: Abdullah Nofia
Gmail::::::::::::::abdullahnofia@gmail.com
Instagram::::::::Abdullah_Nofia
Facebook:::::::::Abdullah Nofia

Bijaksanalah saat Anda mencari pinjaman online

semoga harimu menyenangkan

Unknown said...

Hi Sir,

This blog is very helpfull,
can you please post opm api for quality specification and customer vaidity rules

Post a Comment