Item Instance Expire API

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;

   SUBTYPE extend_attrib_values_tbl   IS     csi_datastructures_pub.extend_attrib_values_tbl;

   SUBTYPE party_tbl         IS     csi_datastructures_pub.party_tbl;

   SUBTYPE party_account_tbl    IS     csi_datastructures_pub.party_account_tbl;

   SUBTYPE pricing_attribs_tbl     IS     csi_datastructures_pub.pricing_attribs_tbl;

   SUBTYPE organization_units_tbl  IS     csi_datastructures_pub.organization_units_tbl;

   SUBTYPE instance_asset_tbl      IS     csi_datastructures_pub.instance_asset_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 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;

  

  l_mtl_transaction_id  NUMBER;

  l_expire_date     DATE;

  l_max_trx_date    DATE;

BEGIN

 l_instance_query_rec.instance_id  := &p_instance_id;

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

-- dbms_output.put_line('ret sts 2.. '||l_return_status);

  IF l_return_status = 'S' 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_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;

 /

 commit;

 /