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;