API to End Date (Expire) Instance Item in Oracle Instance Base

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;


   SUBTYPE instance_rec     IS csi_datastructures_pub.instance_rec;
   SUBTYPE transaction_rec IS csi_datastructures_pub.transaction_rec;
   SUBTYPE id_tbl                IS csi_datastructures_pub.id_tbl;
   SUBTYPE instance_query_rec IS csi_datastructures_pub.instance_query_rec;
   SUBTYPE party_query_rec      IS csi_datastructures_pub.party_query_rec;
   SUBTYPE party_account_query_rec IS csi_datastructures_pub.party_account_query_rec;
   SUBTYPE instance_header_tbl         IS csi_datastructures_pub.instance_header_tbl;

   -- Get Item Instance parameters
   l_instance_query_rec      instance_query_rec;
   l_party_query_rec           party_query_rec;
   l_account_query_rec      party_account_query_rec;
   l_instance_header_tbl    instance_header_tbl;

   -- Expire(End Date) Item Instance parameters
   l_instance_rec        instance_rec;
   l_txn_rec                transaction_rec;
   l_instance_id_lst    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;
  
--   CURSOR c1
--   IS
--   select *
--     from xx_installbase_end_date
--     ;
BEGIN
-- FOR i IN c1
-- LOOP
--   DBMS_OUTPUT.put_line('End Dating Instance Number: '||i.instance_id);
  --l_instance_query_rec.instance_id := i.instance_id;
   l_instance_query_rec.instance_id := 121212;

   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_column   => 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
);


   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 := sysdate;--Pass the End Date

   l_txn_rec.transaction_date := sysdate;
   l_txn_rec.source_transaction_date := sysdate;
--   l_txn_rec.transaction_id := NULL;
   l_txn_rec.transaction_type_id := 1;

   -- **************************************
   -- 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 = 'S'
   THEN
      DBMS_OUTPUT.put_line(   'Instance '
                           || l_instance_rec.instance_id
                           || ' Expired successfully');
   -- **************************************--
   -- 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 .. (l_mesg_count - 1)
         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;
  
-- END LOOP;
  
END;
/

Note:

• Pass the Transaction Date & source_transaction_date in above script otherwise API will through "No Transaction date passed"
• Pass the transaction_type_id in above script otherwise API will through "API programming Error (&API_NAME):The Create Transaction API failed. The transaction Id parameter (9.990000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000E+125) is invalid"
• Some times we will get error message, "The Instance (121212), cannot be expired. This Instance has transactions with transaction date (15-JAN-2014 11:59:59), which is later than the date passed for expiration.". For this follow the Metalink Note: 1270184.1  or pass the end date greater than the Transaction Date which was thrown by API.