How to Load Images Into BLOB Columns

1. Create a table as mentioned below.

CREATE  TABLE xx_load_images_into_blobs
 ( ID    NUMBER,
   blob_col BLOB
 );

2. Create a logical directory in the database to the physical file system:

 create or replace directory GET_FILES as '/usr/tmp';

3. Create a procedure to load the blobs from the file system using the logical directory.  The gif "xyz.gif" must exist in /usr/tmp directory. 

declare
   f_lob                                             BFILE;
   b_lob                                             BLOB;
BEGIN
   INSERT INTO xx_load_images_into_blobs        
VALUES (1
              , EMPTY_BLOB ()
               )
        RETURN blob_col
          INTO b_lob;

   f_lob                                                    := BFILENAME ('GET_FILES', 'xyz.gif');
   DBMS_LOB.fileopen (f_lob, DBMS_LOB.file_readonly);
   DBMS_LOB.loadfromfile (b_lob, f_lob, DBMS_LOB.getlength (f_lob));
   DBMS_LOB.fileclose (f_lob);
   COMMIT;
END;
/

4. After executing the above block run the Query mentioned below and check the result.

SELECT * FROM xx_load_images_into_blobs;

Script to Create the Instance in Install Base and Creation of relationship With Other Instance In Install base

DECLARE
   x_instance_rec                                    csi_datastructures_pub.instance_rec;
   x_ext_attrib_values                               csi_datastructures_pub.extend_attrib_values_tbl;
   x_party_tbl                                       csi_datastructures_pub.party_tbl;
   x_account_tbl                                     csi_datastructures_pub.party_account_tbl;
   x_pricing_attrib_tbl                              csi_datastructures_pub.pricing_attribs_tbl;
   x_org_assignments_tbl                             csi_datastructures_pub.organization_units_tbl;
   x_asset_assignment_tbl                            csi_datastructures_pub.instance_asset_tbl;
   x_txn_rec                                         csi_datastructures_pub.transaction_rec;
   x2_txn_rec                                        csi_datastructures_pub.transaction_rec;
   x_return_status                                   VARCHAR2 (100);
   x_msg_count                                       NUMBER;
   x_msg_data                                        VARCHAR2 (2000);
   x_created_manually_flag                           VARCHAR2 (100);
   l_org_id                                          NUMBER := 41;
   n                                                 NUMBER := 1;
   v_instance_id                                     NUMBER;
   p_commit                                          VARCHAR2 (5);
   p2_commit                                         VARCHAR2 (5);
   p_validation_level                                NUMBER;
   p_init_msg_lst                                    VARCHAR2 (500);
   v_instance_party_id                               NUMBER;
   v_ip_account_id                                   NUMBER;
   x_relationship_tbl                                csi_datastructures_pub.ii_relationship_tbl;
   v_relationship_id                                 NUMBER;
   v_success                                         VARCHAR2 (1) := 'T';
   x2_return_status                                  VARCHAR2 (100);
   x2_msg_count                                      NUMBER;
   x2_msg_data                                       VARCHAR2 (2000);
   p2_validation_level                               NUMBER;
   p2_init_msg_lst                                   VARCHAR2 (500);
BEGIN
   SELECT csi_item_instances_s.NEXTVAL
     INTO v_instance_id
     FROM SYS.DUAL;

   x_instance_rec.instance_id                               := v_instance_id;
   x_instance_rec.instance_number                           := v_instance_id;
   x_instance_rec.inventory_item_id                         := '<inventory_item_id>';
   x_instance_rec.inv_master_organization_id                := '<inv_master_organization_id>';
   x_instance_rec.serial_number                             := '<serial_number>';
   x_instance_rec.mfg_serial_number_flag                    := 'N';
   x_instance_rec.quantity                                  := 1;
   x_instance_rec.unit_of_measure                           := 'EA';
   x_instance_rec.accounting_class_code                     := '<accounting_class_code>';
   x_instance_rec.instance_status_id                        := 3;
   x_instance_rec.customer_view_flag                        := NULL;   --N
   x_instance_rec.merchant_view_flag                        := NULL;   --Y
   x_instance_rec.sellable_flag                             := NULL;   --N
   x_instance_rec.active_start_date                         := TRUNC (SYSDATE);
   x_instance_rec.location_type_code                        := 'HZ_LOCATIONS';
   x_instance_rec.location_id                               := '<location_id>';
   x_instance_rec.install_date                              := TRUNC (SYSDATE);
   x_instance_rec.creation_complete_flag                    := 'Y';
   x_instance_rec.version_label                             := '<version_label>';
   x_instance_rec.last_oe_po_number                         := '<so_po_number >';
   x_instance_rec.object_version_number                     := 1;

   -- ************* FOR PARTIES **********************************************************
   SELECT csi_i_parties_s.NEXTVAL
     INTO v_instance_party_id
     FROM SYS.DUAL;

   x_party_tbl (1).instance_party_id                        := v_instance_party_id;
   x_party_tbl (1).instance_id                              := v_instance_id;
   x_party_tbl (1).party_source_table                       := 'HZ_PARTIES';
   x_party_tbl (1).party_id                                 := '<party_id>';
   x_party_tbl (1).relationship_type_code                   := 'OWNER';
   x_party_tbl (1).contact_flag                             := 'N';
   x_party_tbl (1).active_start_date                        := SYSDATE;
   x_party_tbl (1).object_version_number                    := 1;

   -- *********** FOR PARTY ACCOUNT *****************************************************
   SELECT csi_ip_accounts_s.NEXTVAL
     INTO v_ip_account_id
     FROM SYS.DUAL;

   x_account_tbl (1).ip_account_id                          := v_ip_account_id;
   x_account_tbl (1).instance_party_id                      := v_instance_party_id;
   x_account_tbl (1).party_account_id                       := '<party_account_id>';
   x_account_tbl (1).relationship_type_code                 := 'OWNER';
   x_account_tbl (1).bill_to_address                        := '<bill_to_address>';
   x_account_tbl (1).ship_to_address                        := '<ship_to_address>';
   x_account_tbl (1).active_start_date                      := SYSDATE;
   x_account_tbl (1).object_version_number                  := 1;
   x_account_tbl (1).parent_tbl_index                       := 1;
   x_account_tbl (1).call_contracts                         := 'Y';
   -- ************************** TRANSACTION REC *****************************************
   x_txn_rec.transaction_date                               := TRUNC (SYSDATE);
   x_txn_rec.source_transaction_date                        := TRUNC (SYSDATE);
   x_txn_rec.transaction_type_id                            := 1;
   x_txn_rec.object_version_number                          := 1;
   csi_item_instance_pub.create_item_instance (p_api_version                 => 1.0
                                             , p_commit                      => p_commit
                                             , p_init_msg_list               => p_init_msg_lst
                                             , p_validation_level            => p_validation_level
                                             , p_instance_rec                => x_instance_rec
                                             , p_ext_attrib_values_tbl       => x_ext_attrib_values
                                             , p_party_tbl                   => x_party_tbl
                                             , p_account_tbl                 => x_account_tbl
                                             , p_pricing_attrib_tbl          => x_pricing_attrib_tbl
                                             , p_org_assignments_tbl         => x_org_assignments_tbl
                                             , p_asset_assignment_tbl        => x_asset_assignment_tbl
                                             , p_txn_rec                     => x_txn_rec
                                             , x_return_status               => x_return_status
                                             , x_msg_count                   => x_msg_count
                                             , x_msg_data                    => x_msg_data
                                              );

   IF x_return_status != apps.fnd_api.g_ret_sts_success
   THEN
      DBMS_OUTPUT.put_line ('failed. printing error msg...');
      DBMS_OUTPUT.put_line (apps.fnd_msg_pub.get (p_msg_index                   => apps.fnd_msg_pub.g_last
                                                , p_encoded                     => apps.fnd_api.g_false));
      v_success                                                := 'F';
      ROLLBACK;
      RETURN;
   ELSE
      DBMS_OUTPUT.put_line ('Inserted Install base data');
      DBMS_OUTPUT.put_line (' The instance Id is#: ' || v_instance_id);
      COMMIT;

      -- ****************** create relation ship *****************************************
      SELECT csi_ii_relationships_s.NEXTVAL
        INTO v_relationship_id
        FROM SYS.DUAL;

      x_relationship_tbl (1).relationship_id                   := v_relationship_id;
      x_relationship_tbl (1).relationship_type_code            := 'COMPONENT_OF';
      x_relationship_tbl (1).object_id                         := '<v_child_instance_id>';
      x_relationship_tbl (1).subject_id                        := v_instance_id;
      x_relationship_tbl (1).subject_has_child                 := 'N';
      x_relationship_tbl (1).position_reference                := NULL;
      x_relationship_tbl (1).active_start_date                 := SYSDATE;
      x_relationship_tbl (1).active_end_date                   := SYSDATE;
      x_relationship_tbl (1).display_order                     := NULL;
      x_relationship_tbl (1).mandatory_flag                    := 'N';
      x_relationship_tbl (1).CONTEXT                           := NULL;
      x_relationship_tbl (1).attribute1                        := NULL;
      x_relationship_tbl (1).attribute2                        := NULL;
      x_relationship_tbl (1).attribute3                        := NULL;
      x_relationship_tbl (1).attribute4                        := NULL;
      x_relationship_tbl (1).attribute5                        := NULL;
      x_relationship_tbl (1).attribute6                        := NULL;
      x_relationship_tbl (1).attribute7                        := NULL;
      x_relationship_tbl (1).attribute8                        := NULL;
      x_relationship_tbl (1).attribute9                        := NULL;
      x_relationship_tbl (1).attribute10                       := NULL;
      x_relationship_tbl (1).attribute11                       := NULL;
      x_relationship_tbl (1).attribute12                       := NULL;
      x_relationship_tbl (1).attribute13                       := NULL;
      x_relationship_tbl (1).attribute14                       := NULL;
      x_relationship_tbl (1).attribute15                       := NULL;
      x_relationship_tbl (1).object_version_number             := 1;
      x2_txn_rec.transaction_date                              := TRUNC (SYSDATE);
      x2_txn_rec.source_transaction_date                       := TRUNC (SYSDATE);
      x2_txn_rec.transaction_type_id                           := 1;
      x2_txn_rec.object_version_number                         := 1;
      csi_ii_relationships_pub.create_relationship (p_api_version                 => 1.0
                                                  , p_commit                      => p2_commit
                                                  , p_init_msg_list               => p2_init_msg_lst
                                                  , p_validation_level            => p2_validation_level
                                                  , p_relationship_tbl            => x_relationship_tbl
                                                  , p_txn_rec                     => x2_txn_rec
                                                  , x_return_status               => x2_return_status
                                                  , x_msg_count                   => x2_msg_count
                                                  , x_msg_data                    => x2_msg_data
                                                   );

      IF x2_return_status != apps.fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line ('failed. printing error msg...');
         DBMS_OUTPUT.put_line (apps.fnd_msg_pub.get (p_msg_index                   => apps.fnd_msg_pub.g_last
                                                   , p_encoded                     => apps.fnd_api.g_false
                                                    )
                              );
         v_success                                                := 'F';
         ROLLBACK;
         RETURN;
      ELSE
         DBMS_OUTPUT.put_line ('Inserted Install base Relationship data');
         DBMS_OUTPUT.put_line (' The instance Id is#: ' || v_instance_id);
         COMMIT;
      END IF;
   END IF;
END;
/

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