Example how to use IB API to update Ownership

This script is only for Reference purpose and should be used with all proper testing .


DECLARE
   ln_order_num                   NUMBER;
   lc_p_sno                       VARCHAR2 (30); -- Variable for printer serial no.
   ----
   CURSOR ib_cur
   IS
        SELECT cii.instance_id,
               cii.serial_number,
               cii.inventory_item_id,
               cii.object_version_number
          FROM csi_item_instances cii
         WHERE cii.instance_id = &instance_id
      ORDER BY 1;
   TYPE ib_rec_tbl_type IS TABLE OF ib_cur%ROWTYPE;
   ib_rec_tbl                     ib_rec_tbl_type;
   --
   -- Variables needed to call the Item Instance update API
   l_api_version         CONSTANT NUMBER := 1.0;
   l_msg_count                    NUMBER;
   l_msg_data                     VARCHAR2 (2000);
   l_msg_index                    NUMBER;
   l_instance_id_lst              csi_datastructures_pub.id_tbl;
   l_instance_header_rec          csi_datastructures_pub.instance_header_rec;
   l_party_header_tbl             csi_datastructures_pub.party_header_tbl;
   l_party_acct_header_tbl        csi_datastructures_pub.party_account_header_tbl;
   l_org_unit_header_tbl          csi_datastructures_pub.org_units_header_tbl;
   l_instance_rec                 csi_datastructures_pub.instance_rec;
   l_party_tbl                    csi_datastructures_pub.party_tbl;
   l_account_tbl                  csi_datastructures_pub.party_account_tbl;
   l_pricing_attrib_tbl           csi_datastructures_pub.pricing_attribs_tbl;
   l_org_assignments_tbl          csi_datastructures_pub.organization_units_tbl;
   l_asset_assignment_tbl         csi_datastructures_pub.instance_asset_tbl;
   l_ext_attrib_values_tbl        csi_datastructures_pub.extend_attrib_values_tbl;
   l_pricing_attribs_tbl          csi_datastructures_pub.pricing_attribs_tbl;
   l_ext_attrib_tbl               csi_datastructures_pub.extend_attrib_values_tbl;
   l_ext_attrib_def_tbl           csi_datastructures_pub.extend_attrib_tbl;
   l_asset_header_tbl             csi_datastructures_pub.instance_asset_header_tbl;
   l_txn_rec                      csi_datastructures_pub.transaction_rec;
   l_install_location_id          NUMBER;
   l_return_status                VARCHAR2 (5);
   lc_init_msg_lst                VARCHAR2 (1) := 'T';
   ln_validation_level            NUMBER;
   lc_error_text                  VARCHAR2 (4000);
   l_install_location_type_code   csi_item_instances.install_location_type_code%TYPE;
   j                              BINARY_INTEGER := 0;
   l_party_tbl_idx                BINARY_INTEGER;
BEGIN
   --Create a savepoint
   --  SAVEPOINT dcrd_csi_upd_ib_snm;
   OPEN ib_cur;
   FETCH ib_cur
   BULK COLLECT INTO ib_rec_tbl;
   CLOSE ib_cur;
   IF ib_rec_tbl.COUNT > 0
   THEN
      --fnd_file.put_line(fnd_file.log, 'Begin loop');
      DBMS_OUTPUT.put_line ('Begin loop');
      FOR i IN ib_rec_tbl.FIRST .. ib_rec_tbl.LAST
      LOOP
         --Set savepoint before processing record.
         --SAVEPOINT dcrd_csi_upd_ib_snm;
         l_instance_header_rec.instance_id := ib_rec_tbl (i).instance_id;
         csi_item_instance_pub.get_item_instance_details (
            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_header_rec,
            p_get_parties             => fnd_api.g_true,
            p_party_header_tbl        => l_party_header_tbl,
            p_get_accounts            => fnd_api.g_true,
            p_account_header_tbl      => l_party_acct_header_tbl,
            p_get_org_assignments     => fnd_api.g_true,
            p_org_header_tbl          => l_org_unit_header_tbl,
            p_get_pricing_attribs     => fnd_api.g_false,
            p_pricing_attrib_tbl      => l_pricing_attribs_tbl,
            p_get_ext_attribs         => fnd_api.g_false,
            p_ext_attrib_tbl          => l_ext_attrib_tbl,
            p_ext_attrib_def_tbl      => l_ext_attrib_def_tbl,
            p_get_asset_assignments   => fnd_api.g_false,
            p_asset_header_tbl        => l_asset_header_tbl,
            p_resolve_id_columns      => fnd_api.g_false,
            p_time_stamp              => SYSDATE,
            x_return_status           => l_return_status,
            x_msg_count               => l_msg_count,
            x_msg_data                => l_msg_data);
         lc_error_text := NULL;
         l_instance_rec.instance_id := l_instance_header_rec.instance_id;
         l_instance_rec.install_date := '12-MAR-2008';
         l_instance_rec.install_location_type_code := 'HZ_PARTY_SITES';
         l_instance_rec.install_location_id := &location;
         l_instance_rec.location_id := &location;
         l_instance_rec.location_type_code := 'HZ_PARTY_SITES';
         l_instance_rec.object_version_number :=
            l_instance_header_rec.object_version_number;
         l_txn_rec.transaction_id := fnd_api.g_miss_num;
         l_txn_rec.transaction_date := SYSDATE;
         l_txn_rec.source_transaction_date := SYSDATE;
         l_txn_rec.transaction_type_id := &trans; --Id for DATA_CORRECTION transaction type
         --Change Owner party details
         FOR i IN l_party_header_tbl.FIRST .. l_party_header_tbl.LAST
         LOOP
            IF l_party_header_tbl (i).relationship_type_code = 'OWNER'
            THEN
               l_party_tbl (j).instance_party_id :=
                  l_party_header_tbl (i).instance_party_id;
               l_party_tbl (j).relationship_type_code :=
                  l_party_header_tbl (i).relationship_type_code;
               l_party_tbl (j).party_id := &party;
               l_party_tbl (j).contact_flag := 'N';
               l_party_tbl (j).object_version_number :=
                  l_party_header_tbl (i).object_version_number;
               l_party_tbl_idx := j;
               j := j + 1;
            END IF;
         END LOOP;
         DBMS_OUTPUT.put_line ('l_party_tbl count is ' || l_party_tbl.COUNT);
         j := 0;
         DBMS_OUTPUT.put_line (
               'l_party_acct_header_tbl count is '
            || l_party_acct_header_tbl.COUNT);
         --Change Owner party account details
         FOR i IN l_party_acct_header_tbl.FIRST ..
                  l_party_acct_header_tbl.LAST
         LOOP
            IF l_party_acct_header_tbl (i).relationship_type_code = 'OWNER'
            THEN
               l_account_tbl (j).ip_account_id :=
                  l_party_acct_header_tbl (i).ip_account_id;
               l_account_tbl (j).instance_party_id :=
                  l_party_acct_header_tbl (i).instance_party_id;
               l_account_tbl (j).party_account_id := &accountid;
               l_account_tbl (j).object_version_number :=
                  l_party_acct_header_tbl (i).object_version_number;
               l_account_tbl (j).bill_to_address := &billtoaddress;
               l_account_tbl (j).ship_to_address := &shiptoaddress;
               l_account_tbl (j).parent_tbl_index := 1;--l_party_tbl_idx;
               j := j + 1;
            END IF;
         END LOOP;
         DBMS_OUTPUT.put_line (
            'l_account_tbl count is ' || l_account_tbl.COUNT);
         j := 0;
         --Change Operating Unit details
         FOR i IN l_org_unit_header_tbl.FIRST .. l_org_unit_header_tbl.LAST
         LOOP
            IF l_org_unit_header_tbl (i).relationship_type_code = 'SOLD_FROM'
            THEN
               l_org_assignments_tbl (j).instance_ou_id :=
                  l_org_unit_header_tbl (i).instance_ou_id;
               l_org_assignments_tbl (j).instance_id :=
                  l_org_unit_header_tbl (i).instance_id;
               l_org_assignments_tbl (j).relationship_type_code :=
                  l_org_unit_header_tbl (i).relationship_type_code;
               l_org_assignments_tbl (j).active_start_date := '12-MAR-2008';
               l_org_assignments_tbl (j).operating_unit_id := &orgid;
               l_org_assignments_tbl (j).object_version_number :=
                  l_org_unit_header_tbl (i).object_version_number;
            END IF;
         END LOOP;
         -- Call instance update API if a serial no. is to be updated
         /*fnd_file.put_line(fnd_file.log
         ,'Updating IB record for IB# ' || ib_rec_tbl(i).instance_id);*/
         DBMS_OUTPUT.put_line (
            'Updating IB record for IB# ' || ib_rec_tbl (i).instance_id);
         csi_item_instance_pub.update_item_instance (
            p_api_version             => l_api_version,
            p_commit                  => 'F'             --Handled outside API
                                            ,
            p_init_msg_list           => lc_init_msg_lst,
            p_validation_level        => ln_validation_level,
            p_instance_rec            => l_instance_rec,
            p_ext_attrib_values_tbl   => l_ext_attrib_values_tbl        --Null
                                                                ,
            p_party_tbl               => l_party_tbl                    --Null
                                                    ,
            p_account_tbl             => l_account_tbl                  --Null
                                                      ,
            p_pricing_attrib_tbl      => l_pricing_attrib_tbl           --Null
                                                             ,
            p_org_assignments_tbl     => l_org_assignments_tbl          --Null
                                                              ,
            p_asset_assignment_tbl    => l_asset_assignment_tbl         --Null
                                                               ,
            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);
--- we need to check if there are anything
--- So that there  are not way to by pass exception handling ...
         IF l_return_status = 'S'
         THEN
            /*fnd_file.put_line(fnd_file.log
            , 'Error updating the install base for IB# ' || ib_rec_tbl(i)
             .instance_id);*/
            DBMS_OUTPUT.put_line (
                  'Error updating the install base for IB# '
               || ib_rec_tbl (i).instance_id);
            FOR i IN 1 .. l_msg_count
            LOOP
               fnd_msg_pub.get (p_msg_index       => -1,
                                p_encoded         => 'F',
                                p_data            => l_msg_data,
                                p_msg_index_out   => l_msg_index);
               lc_error_text := lc_error_text || (SUBSTR (l_msg_data, 1, 255));
            END LOOP;
            DBMS_OUTPUT.put_line (lc_error_text);
         --Rollback the transaction if error occured.
         --ROLLBACK TO dcrd_csi_upd_ib_snm;
         ELSE
            /*fnd_file.put_line(fnd_file.log
            , 'Install base update successful for IB# ' || ib_rec_tbl(i)
             .instance_id);*/
            DBMS_OUTPUT.put_line (
                  'Install base update successful for IB# '
               || ib_rec_tbl (i).instance_id);
            lc_error_text := 'SUCCESS!';
         END IF;
      --Update the temporary table record status
      --update_status(ib_rec_tbl(i).snm_id, l_return_status, lc_error_text);
      END LOOP;
   --Commit transactions.
   --COMMIT;
   END IF;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      --fnd_file.put_line(fnd_file.log, 'No records to process');
      DBMS_OUTPUT.put_line ('No records to process');
   WHEN OTHERS
   THEN
      /*    fnd_file.put_line(fnd_file.log, 'Error in update_ib procedure');
          fnd_file.put_line(fnd_file.log, to_char(SQLCODE) || ' - ' || SQLERRM);
      */
      DBMS_OUTPUT.put_line ('Error in update_ib procedure');
      DBMS_OUTPUT.put_line (TO_CHAR (SQLCODE) || ' - ' || SQLERRM);
END;