API Script To Create Instance in Install Base and Link instance to Existing Asset

DECLARE
   p_instance_rec                                    csi_datastructures_pub.instance_rec;
   p_ext_attrib_values_tbl                           csi_datastructures_pub.extend_attrib_values_tbl;
   p_party_tbl                                       csi_datastructures_pub.party_tbl;
   p_account_tbl                                     csi_datastructures_pub.party_account_tbl;
   p_pricing_attrib_tbl                              csi_datastructures_pub.pricing_attribs_tbl;
   p_org_assignments_tbl                             csi_datastructures_pub.organization_units_tbl;
   p_asset_assignment_tbl                            csi_datastructures_pub.instance_asset_tbl;
   p_txn_rec                                         csi_datastructures_pub.transaction_rec;
   x_instance_id                                     csi_datastructures_pub.id_tbl;
   x_return_status                                   VARCHAR2 (2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (2000);
   t_output                                          VARCHAR2 (2000);
   t_msg_dummy                                       NUMBER;
   p_commit                                          VARCHAR2 (5);
   x_instance_id_lst                                 csi_datastructures_pub.id_tbl;
   l_instance_id                                     NUMBER;
   l_instance_party_id                               NUMBER;
   l_instance_party_id_contact                       NUMBER;
   l_ip_account_id                                   NUMBER;
   l_internal_party_id                               NUMBER;
   l_book_type_code                                  VARCHAR2 (240);
   l_asset_id                                        NUMBER;
   l_location_id                                     NUMBER;
   l_location_id1                                    NUMBER;
   l_fa_location_id                                  NUMBER;
   l_organization_id                                 NUMBER;
BEGIN
   SELECT fdh.book_type_code
        , fdh.asset_id
        , fdh.location_id
     INTO l_book_type_code
        , l_asset_id
        , l_location_id
     FROM fa_distribution_history fdh
        , fa_additions_b fab
    WHERE fab.asset_id = fdh.asset_id
      AND fab.serial_number = '<your serial number>'
      AND date_ineffective IS NULL;

   SELECT location_id
        , fa_location_id
     INTO l_location_id1
        , l_fa_location_id
     FROM csi_a_locations
    WHERE fa_location_id = l_location_id
      AND active_end_date IS NULL;

   SELECT csi_item_instances_s.NEXTVAL
     INTO l_instance_id
     FROM DUAL;

   DBMS_OUTPUT.put_line ('instance id ' || l_instance_id);
   p_instance_rec.instance_id                               := l_instance_id;
   p_instance_rec.instance_number                           := l_instance_id;
   p_instance_rec.inv_master_organization_id                := l_organization_id;
   p_instance_rec.quantity                                  := 1;
   p_instance_rec.unit_of_measure                           := 'Ea';
   p_instance_rec.customer_view_flag                        := 'N';
   p_instance_rec.merchant_view_flag                        := 'Y';
   p_instance_rec.location_type_code                        := 'HZ_LOCATIONS';
   p_instance_rec.location_id                               := 950;
   p_instance_rec.vld_organization_id                       := 204;
   p_instance_rec.inventory_item_id                         := 271883;
   p_instance_rec.serial_number                             := 'DD4389XZ';
   p_instance_rec.instance_status_id                        := 510;
   p_instance_rec.object_version_number                     := 1;
   p_instance_rec.active_start_date                         := TO_DATE ('01-JAN-2003');
   p_instance_rec.instance_usage_code                       := 'IN_SERVICE';

   SELECT internal_party_id
     INTO l_internal_party_id
     FROM csi_install_parameters;

   p_party_tbl (1).instance_id                              := l_instance_id;
   p_party_tbl (1).relationship_type_code                   := 'OWNER';
   p_party_tbl (1).party_id                                 := l_internal_party_id;
   p_party_tbl (1).party_source_table                       := 'HZ_PARTIES';
   p_party_tbl (1).contact_flag                             := 'N';
   p_party_tbl (1).object_version_number                    := 1;
   ------------------------------------------------------------------------
   p_asset_assignment_tbl (1).instance_id                   := l_instance_id;
   p_asset_assignment_tbl (1).fa_asset_id                   := l_asset_id;
   p_asset_assignment_tbl (1).fa_location_id                := l_fa_location_id;
   p_asset_assignment_tbl (1).fa_book_type_code             := l_book_type_code;
   p_asset_assignment_tbl (1).asset_quantity                := 1;
   p_asset_assignment_tbl (1).update_status                 := 'IN_SERVICE';
   -------------------------------------------------------------
   p_txn_rec.transaction_date                               := TO_DATE ('01-JAN-2003');
   p_txn_rec.source_transaction_date                        := TO_DATE ('01-JAN-2003');
   p_txn_rec.transaction_type_id                            := 1;
   fnd_msg_pub.initialize;
   csi_item_instance_pub.create_item_instance (p_api_version                 => 1.0
                                             , p_commit                      => p_commit
                                             , p_init_msg_list               => fnd_api.g_false
                                             , p_validation_level            => fnd_api.g_valid_level_full
                                             , p_instance_rec                => p_instance_rec
                                             , p_ext_attrib_values_tbl       => p_ext_attrib_values_tbl
                                             , p_party_tbl                   => p_party_tbl
                                             , p_account_tbl                 => p_account_tbl
                                             , p_pricing_attrib_tbl          => p_pricing_attrib_tbl
                                             , p_org_assignments_tbl         => p_org_assignments_tbl
                                             , p_asset_assignment_tbl        => p_asset_assignment_tbl
                                             , p_txn_rec                     => p_txn_rec
                                             , x_return_status               => x_return_status
                                             , x_msg_count                   => x_msg_count
                                             , x_msg_data                    => x_msg_data
                                              );
   DBMS_OUTPUT.put_line ('Return Status is ' || x_return_status);

   IF x_msg_count > 0
   THEN
      FOR j IN 1 .. x_msg_count
      LOOP
         fnd_msg_pub.get (j, fnd_api.g_false, x_msg_data, t_msg_dummy);
         t_output                                                 := ('Msg ' || TO_CHAR (j) || ': ' || x_msg_data);
         DBMS_OUTPUT.put_line (SUBSTR (t_output, 1, 255));
      END LOOP;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;

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

Script to create reservation For a Item in the Subinventory using API

DECLARE
   l_rsv_rec                                         inv_reservation_global.mtl_reservation_rec_type;
   l_serial_number                                   inv_reservation_global.serial_number_tbl_type;
   l_partial_reservation_flag                        VARCHAR2 (1) := fnd_api.g_false;
   l_force_reservation_flag                          VARCHAR2 (1) := fnd_api.g_false;
   l_validation_flag                                 VARCHAR2 (1) := fnd_api.g_true;
   l_partial_reservation_exists                      BOOLEAN := FALSE;
   l_primary_reservation_qty                         NUMBER;
   l_subinventory_code                               VARCHAR2 (40);
   l_error_message                                   VARCHAR2 (2000);
   l_msg_index_out                                   NUMBER;
   x_serial_number                                   inv_reservation_global.serial_number_tbl_type;
   x_quantity_reserved                               NUMBER := 0;
   x_reservation_id                                  NUMBER := 0;
   x_return_status                                   VARCHAR2 (2);
   x_msg_count                                       NUMBER := 0;
   x_msg_data                                        VARCHAR2 (250);
BEGIN
   l_rsv_rec.organization_id                                := '<organization_id>';
   l_rsv_rec.inventory_item_id                              := '<inventory_item_id>';
   l_rsv_rec.requirement_date                               := SYSDATE + 1;
   l_rsv_rec.demand_source_type_id                          := inv_reservation_global.g_source_type_inv;
   l_rsv_rec.supply_source_type_id                          := inv_reservation_global.g_source_type_inv;
   l_rsv_rec.demand_source_name                             := '<segment1>';
   l_rsv_rec.primary_reservation_quantity                   := '<primary_reservation_qty>';
   l_rsv_rec.primary_uom_code                               := '<primary_uom_code>';
   l_rsv_rec.subinventory_code                              := '<subinventory_code>';
   -- call API to create reservation
   inv_reservation_pub.create_reservation (p_api_version_number          => 1.0
                                         , p_init_msg_lst                => fnd_api.g_true
                                         , p_rsv_rec                     => l_rsv_rec
                                         , p_serial_number               => l_serial_number
                                         , p_partial_reservation_flag    => l_partial_reservation_flag
                                         , p_force_reservation_flag      => l_force_reservation_flag
                                         , p_partial_rsv_exists          => l_partial_reservation_exists
                                         , p_validation_flag             => l_validation_flag
                                         , x_serial_number               => x_serial_number
                                         , x_return_status               => x_return_status
                                         , x_msg_count                   => x_msg_count
                                         , x_msg_data                    => x_msg_data
                                         , x_quantity_reserved           => x_quantity_reserved
                                         , x_reservation_id              => x_reservation_id
                                          );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Reservation Created Successfully ');
      DBMS_OUTPUT.put_line ('Reservation ID :' || x_reservation_id);
      DBMS_OUTPUT.put_line (' Quantity Reserved:' || x_quantity_reserved);
      DBMS_OUTPUT.put_line ('Serial Reserved:' || x_serial_number);
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;

Script tot Create an account site use Using TCA API

DECLARE
   p_cust_site_use_rec                               hz_cust_account_site_v2pub.cust_site_use_rec_type;
   p_customer_profile_rec                            hz_customer_profile_v2pub.customer_profile_rec_type;
   x_site_use_id                                     NUMBER;
   x_return_status                                   VARCHAR2 (2000);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (2000);
   l_created_by_module                               VARCHAR2 (240);
   l_error_message                                   VARCHAR2 (2000);
   l_msg_index_out                                   NUMBER;
BEGIN
   DBMS_APPLICATION_INFO.set_client_info ('&ORG_ID');

   SELECT lookup_code
     INTO l_created_by_module
     FROM fnd_lookup_values
    WHERE lookup_type = 'HZ_CREATED_BY_MODULES'
      AND ROWNUM = 1
      AND enabled_flag = 'Y'
      AND NVL (end_date_active, SYSDATE) >= SYSDATE;

   p_cust_site_use_rec.cust_acct_site_id                    := '<cust_acct_site_id>';
   p_cust_site_use_rec.site_use_code                        := '<BILL_TO/SHIP_TO>';
   p_cust_site_use_rec.created_by_module                    := l_created_by_module;
   hz_cust_account_site_v2pub.create_cust_site_use (p_init_msg_list               => fnd_api.g_true
                                                  , p_cust_site_use_rec           => p_cust_site_use_rec
                                                  , p_customer_profile_rec        => p_customer_profile_rec
                                                  , p_create_profile              => fnd_api.g_false
                                                  , p_create_profile_amt          => fnd_api.g_false
                                                  , x_site_use_id                 => x_site_use_id
                                                  , x_return_status               => x_return_status
                                                  , x_msg_count                   => x_msg_count
                                                  , x_msg_data                    => x_msg_data
                                                   );

   IF x_return_status <> fnd_api.g_ret_sts_success
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         apps.fnd_msg_pub.get (p_msg_index                   => i
                             , p_encoded                     => fnd_api.g_false
                             , p_data                        => x_msg_data
                             , p_msg_index_out               => l_msg_index_out
                              );

         IF l_error_message IS NULL
         THEN
            l_error_message                                          := SUBSTR (x_msg_data, 1, 250);
         ELSE
            l_error_message                                          :=
                                                                      l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
         END IF;
      END LOOP;

      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
      DBMS_OUTPUT.put_line ('*****************************************');
      ROLLBACK;
   ELSE
      DBMS_OUTPUT.put_line ('*****************************************');
      DBMS_OUTPUT.put_line ('Account Site Use Created Successfully ');
      DBMS_OUTPUT.put_line ('Site Use id : ' || x_site_use_id);
      DBMS_OUTPUT.put_line ('*****************************************');
      COMMIT;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Unexpected Error ' || SUBSTR (SQLERRM, 1, 250));
END;