API Script to Expire the Instance in Install Base (IB)

DECLARE
   l_record_status                                   VARCHAR2 (1);
   l_sv_status                                       VARCHAR2 (2000);
   l_msg_data                                        VARCHAR2 (2000);
   l_mesg                                            VARCHAR2 (4000);
   l_mesg_len                                        NUMBER;
   l_mesg_count                                      NUMBER;
   -- Get Item Instance parameters
   l_instance_query_rec                              csi_datastructures_pub.instance_query_rec;
   l_party_query_rec                                 csi_datastructures_pub.party_query_rec;
   l_account_query_rec                               csi_datastructures_pub.party_account_query_rec;
   l_instance_header_tbl                             csi_datastructures_pub.instance_header_tbl;
   -- Expire Item Instance parameters
   l_instance_rec                                    csi_datastructures_pub.instance_rec;
   l_txn_rec                                         csi_datastructures_pub.transaction_rec;
   l_instance_id_lst                                 csi_datastructures_pub.id_tbl;
   l_return_status                                   VARCHAR2 (1) := okl_api.g_ret_sts_success;
   l_overall_status                                  VARCHAR2 (1) := okl_api.g_ret_sts_success;
   l_api_name                               CONSTANT VARCHAR2 (30) := 'expire_item';
   l_api_version                            CONSTANT NUMBER := 1;
   l_msg_count                                       NUMBER := fnd_api.g_miss_num;
   l_mtl_transaction_id                              NUMBER;
   l_expire_date                                     DATE;
   l_max_trx_date                                    DATE;
BEGIN
   l_instance_query_rec.instance_id                         := 3966591;
   l_mtl_transaction_id                                     := &p_mtl_transaction_id;   -- can be null if no mtl transaction is available

   IF l_mtl_transaction_id = 0
   THEN
      l_mtl_transaction_id                                     := NULL;
   END IF;

   csi_item_instance_pub.get_item_instances (p_api_version                 => l_api_version
                                           , p_commit                      => fnd_api.g_false
                                           , p_init_msg_list               => fnd_api.g_false
                                           , p_validation_level            => fnd_api.g_valid_level_full
                                           , p_instance_query_rec          => l_instance_query_rec
                                           , p_party_query_rec             => l_party_query_rec
                                           , p_account_query_rec           => l_account_query_rec
                                           , p_transaction_id              => NULL
                                           , p_resolve_id_columns          => fnd_api.g_false
                                           , p_active_instance_only        => fnd_api.g_true
                                           , x_instance_header_tbl         => l_instance_header_tbl
                                           , x_return_status               => l_return_status
                                           , x_msg_count                   => l_msg_count
                                           , x_msg_data                    => l_msg_data
                                            );

   -- *************************************
   -- Initialize parameters to be passed in
   -- *************************************
   IF l_mtl_transaction_id IS NOT NULL
   THEN
      SELECT MAX (transaction_date)
        INTO l_expire_date
        FROM mtl_material_transactions
       WHERE transaction_id = l_mtl_transaction_id;
   ELSE
      l_expire_date                                            := SYSDATE;
   END IF;

   SELECT MAX (transaction_date)
     INTO l_max_trx_date
     FROM csi_inst_transactions_v
    WHERE instance_id = l_instance_query_rec.instance_id;

   IF l_expire_date < l_max_trx_date
   THEN
      l_expire_date                                            := l_max_trx_date;
   END IF;

   l_instance_rec.instance_id                               := l_instance_header_tbl (1).instance_id;
   l_instance_rec.object_version_number                     := l_instance_header_tbl (1).object_version_number;
   l_instance_rec.active_end_date                           := l_expire_date;
   l_txn_rec.transaction_date                               := SYSDATE;
   l_txn_rec.source_transaction_date                        := l_expire_date;
   l_txn_rec.transaction_id                                 := NULL;
   l_txn_rec.transaction_type_id                            := 1;

   IF l_mtl_transaction_id IS NOT NULL
   THEN
      l_txn_rec.inv_material_transaction_id                    := l_mtl_transaction_id;
   END IF;

   -- **************************************
   -- Call Installed Base API to expire item
   -- **************************************
   csi_item_instance_pub.expire_item_instance (p_api_version                 => l_api_version
                                             , p_commit                      => fnd_api.g_false
                                             , p_init_msg_list               => fnd_api.g_false
                                             , p_validation_level            => fnd_api.g_valid_level_full
                                             , p_instance_rec                => l_instance_rec
                                             , p_expire_children             => fnd_api.g_true
                                             , p_txn_rec                     => l_txn_rec
                                             , x_instance_id_lst             => l_instance_id_lst
                                             , x_return_status               => l_return_status
                                             , x_msg_count                   => l_msg_count
                                             , x_msg_data                    => l_msg_data
                                              );

   IF l_return_status = fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('Instance ' || l_instance_rec.instance_id || ' Expired Successfully');

      -- **************************************
      -- updating the IB error if there is one
      -- **************************************
      IF l_mtl_transaction_id IS NOT NULL
      THEN
         UPDATE csi_txn_errors
         SET processed_flag = 'P'
           , ERROR_TEXT = 'updated for datafix' || processed_flag
         WHERE  inv_material_transaction_id = l_mtl_transaction_id;
      END IF;
   -- **************************************
   -- Display errors encounted for the expiration
   -- **************************************
   ELSE
      l_mesg_count                                             := fnd_msg_pub.count_msg;

      IF l_mesg_count > 0
      THEN
         l_mesg                                                   :=
                                          CHR (10)
                                          || SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_first, fnd_api.g_false), 1, 512);

         FOR i IN 1 .. 2
         LOOP
            l_mesg                                                   :=
                                 l_mesg || CHR (10)
                                 || SUBSTR (fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false), 1, 512);
         END LOOP;

         fnd_msg_pub.delete_msg ();
         l_mesg_len                                               := LENGTH (l_mesg);

         FOR i IN 1 .. CEIL (l_mesg_len / 255)
         LOOP
            DBMS_OUTPUT.put_line (SUBSTR (l_mesg, ((i * 255) - 254), 255));
         END LOOP;
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;
/