IB Sql to get extract for given party and ship to country

SELECT a.*
  FROM (SELECT (SELECT segment1
                  FROM apps.mtl_system_items_b
                 WHERE     inventory_item_id = CIS.inventory_item_id
                       AND organization_id = CIS.inv_master_organization_id)
                  "Item",
               (SELECT description
                  FROM apps.mtl_system_items_b
                 WHERE     inventory_item_id = CIS.inventory_item_id
                       AND organization_id = CIS.inv_master_organization_id)
                  "Item Description",
               INSTANCE_NUMBER "Item Instance #",
               cis.serial_number "Serial Number",
               cis.EXTERNAL_REFERENCE "External Reference",
               (SELECT NAME
                  FROM APPS.CSI_INSTANCE_STATUSES
                 WHERE INSTANCE_STATUS_ID = CIS.instance_status_id)
                  "Status",
               (SELECT TO_CHAR (order_number)
                  FROM apps.oe_order_headers_all b, apps.oe_order_lines_all c
                 WHERE     b.header_id = c.header_id
                       AND line_id = CIS.last_oe_order_line_id)
                  "Sales Order Number",
               (SELECT b.cust_po_number
                  FROM apps.oe_order_headers_all b, apps.oe_order_lines_all c
                 WHERE     b.header_id = c.header_id
                       AND line_id = CIS.last_oe_order_line_id)
                  "Purchase Order Number",
               (SELECT TO_CHAR (c.actual_shipment_date,
                                'DD-MON-YYYY HH24:MI:SS')
                  FROM apps.oe_order_headers_all b, apps.oe_order_lines_all c
                 WHERE     b.header_id = c.header_id
                       AND line_id = CIS.last_oe_order_line_id)
                  "Shipped On Date",
               (SELECT party_name
                  FROM apps.hz_parties
                 WHERE party_id = CIS.owner_party_id)
                  "Owner Party Name",
               (SELECT party_number
                  FROM apps.hz_parties
                 WHERE party_id = CIS.owner_party_id)
                  "Owner Party Number",
               (CASE
                   WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
                   THEN
                      (SELECT ADDRESS1
                         FROM apps.hz_locations
                        WHERE LOCATION_ID =
                                 ( (SELECT hl1.LOCATION_ID
                                      FROM apps.hz_party_sites hps1,
                                           apps.hz_locations hl1
                                     WHERE     hps1.party_site_id =
                                                  hps.party_site_id
                                           AND hps1.party_site_id =
                                                  cis.install_location_id
                                           AND hps1.location_id =
                                                  hl1.location_id)))
                   WHEN CIS.location_type_code = 'HZ_LOCATIONS'
                   THEN
                      (SELECT ADDRESS1
                         FROM apps.hz_locations
                        WHERE location_id = cis.install_location_id)
                   ELSE
                      NULL
                END)
                  "Installed At Line1",
               (CASE
                   WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
                   THEN
                      (SELECT ADDRESS2
                         FROM apps.hz_locations
                        WHERE LOCATION_ID IN
                                 ( (SELECT hl1.LOCATION_ID
                                      FROM apps.hz_party_sites hps1,
                                           apps.hz_locations hl1
                                     WHERE     hps1.party_site_id =
                                                  hps.party_site_id
                                           AND hps1.party_site_id =
                                                  cis.install_location_id
                                           AND hps1.location_id =
                                                  hl1.location_id)))
                   WHEN CIS.location_type_code = 'HZ_LOCATIONS'
                   THEN
                      (SELECT ADDRESS2
                         FROM apps.hz_locations
                        WHERE location_id = cis.install_location_id)
                   ELSE
                      NULL
                END)
                  "Installed At Line2",
               (CASE
                   WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
                   THEN
                      (SELECT ADDRESS3
                         FROM apps.hz_locations
                        WHERE LOCATION_ID IN
                                 ( (SELECT hl1.LOCATION_ID
                                      FROM apps.hz_party_sites hps1,
                                           apps.hz_locations hl1
                                     WHERE     hps1.party_site_id =
                                                  hps.party_site_id
                                           AND hps1.party_site_id =
                                                  cis.install_location_id
                                           AND hps1.location_id =
                                                  hl1.location_id)))
                   WHEN CIS.location_type_code = 'HZ_LOCATIONS'
                   THEN
                      (SELECT ADDRESS3
                         FROM apps.hz_locations
                        WHERE location_id = cis.install_location_id)
                   ELSE
                      NULL
                END)
                  "Installed At Line3",
               (CASE
                   WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
                   THEN
                      (SELECT city
                         FROM apps.hz_locations
                        WHERE LOCATION_ID IN
                                 ( (SELECT hl1.LOCATION_ID
                                      FROM apps.hz_party_sites hps1,
                                           apps.hz_locations hl1
                                     WHERE     hps1.party_site_id =
                                                  hps.party_site_id
                                           AND hps1.party_site_id =
                                                  cis.install_location_id
                                           AND hps1.location_id =
                                                  hl1.location_id)))
                   WHEN CIS.location_type_code = 'HZ_LOCATIONS'
                   THEN
                      (SELECT CITY
                         FROM apps.hz_locations
                        WHERE location_id = cis.install_location_id)
                   ELSE
                      NULL
                END)
                  "City",
               (CASE
                   WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
                   THEN
                      (SELECT POSTAL_CODE
                         FROM apps.hz_locations
                        WHERE LOCATION_ID IN
                                 ( (SELECT hl1.LOCATION_ID
                                      FROM apps.hz_party_sites hps1,
                                           apps.hz_locations hl1
                                     WHERE     hps1.party_site_id =
                                                  hps.party_site_id
                                           AND hps1.party_site_id =
                                                  cis.install_location_id
                                           AND hps1.location_id =
                                                  hl1.location_id)))
                   WHEN CIS.location_type_code = 'HZ_LOCATIONS'
                   THEN
                      (SELECT postal_code
                         FROM apps.hz_locations
                        WHERE location_id = cis.install_location_id)
                   ELSE
                      NULL
                END)
                  "Postal Code",
               (CASE
                   WHEN CIS.location_type_code = 'HZ_PARTY_SITES'
                   THEN
                      (SELECT country
                         FROM apps.hz_locations
                        WHERE LOCATION_ID IN
                                 ( (SELECT hl1.LOCATION_ID
                                      FROM apps.hz_party_sites hps1,
                                           apps.hz_locations hl1
                                     WHERE     hps1.party_site_id =
                                                  hps.party_site_id
                                           AND hps1.party_site_id =
                                                  cis.install_location_id
                                           AND hps1.location_id =
                                                  hl1.location_id)))
                   WHEN CIS.location_type_code = 'HZ_LOCATIONS'
                   THEN
                      (SELECT country
                         FROM apps.hZ_locations
                        WHERE location_id = cis.install_location_id)
                   ELSE
                      NULL
                END)
                  "Country",
               HROU.NAME HROU_NAME
          FROM apps.oe_order_lines_all ool,
               apps.hz_locations hzl,
               apps.hz_cust_site_uses_all hcsua,
               apps.hz_party_sites hps,
               apps.hz_cust_acct_sites_all hcasa,
               apps.fnd_territories fndt,
               apps.fnd_territories_tl fndtl,
               apps.csi_item_instances cis,
               apps.hr_operating_units hrou
         WHERE     hzl.location_id = hps.location_id
               AND hcsua.site_use_code = 'SHIP_TO'
               AND hps.party_site_id = hcasa.party_site_id
               AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id
               AND ool.line_id = cis.last_oe_order_line_id          
               AND ool.ship_to_org_id = hcsua.site_use_id
               AND fndtl.territory_code = fndt.territory_code
               AND fndt.territory_code = hzl.country
               AND hrou.organization_ID = OOL.ORG_ID(+)
               AND EXISTS
                      (SELECT inventory_item_ID
                         FROM apps.mtl_system_items_b msib
                        WHERE     organization_ID IN
                                     (SELECT ORGANIZATION_ID
                                        FROM apps.hr_operating_units hrou
                                       WHERE    hrou.name LIKE
                                                   'Brazil ')
                              AND msib.inventory_item_id =
                                     cis.inventory_item_id)
               AND TERRITORY_SHORT_NAME IN
                      ('Armenia',
                       'Australia',
)) A

Some Tables ,API Commonly required SQL for IB

Base Table for all IB :

csi.csi_item_instances

Table that hold CSI Transaction error :

csi.csi_txn_errors.

How OM is linked to CSI :

last_oe_order_line_id from csi_item_instances is line_id from oe_order_lines_all

If Transactions are created for the Order but still there is no Install base created then check if there is any error for the Transaction in the Error Table.

You can use the following Query to get Error Information.

select * from csi.csi_txn_errors where TRANSACTION_ID IN (SELECT transaction_id--, trx_source_line_id
FROM mtl_material_transactions
WHERE trx_source_line_id IN (select line_id from oe_order_lines_all
where header_id IN (select header_id from oe_order_headers_all
where order_number = 'Your Order Number'))
AND transaction_type_id = 33);
-----API FOR IB Creation/Update ----------------

csi_item_instance_pub.create_instance
csi_item_instance_pub.update_instance

Script for IB Creation for Order lines that are not converted .

This package is for refernce only and should be tested as required .

CREATE OR REPLACE PACKAGE ib_pkg
IS
PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER);
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2);
END ib_pkg;
/

CREATE OR REPLACE PACKAGE BODY ib_pkg
IS

PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER)
IS
/************************************************************************
Purpose : This procedure creates IB for all the order which are missed.
*************************************************************************/
l_header_id NUMBER;
l_mtl_txn_id NUMBER;
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;

CURSOR c_ib
IS
SELECT -- oeh.order_number,
oel1.line_id
-- oeh.creation_date,
-- msib1.inventory_item_id,
-- msib1.segment1
FROM oe_order_lines_all oel1,
mtl_system_items_b msib1,
oe_order_headers_all oeh
WHERE oel1.ordered_item = msib1.segment1
AND msib1.comms_nl_trackable_flag = 'Y'
AND msib1.shippable_item_flag = 'Y'
AND msib1.organization_id = &orgid
AND oeh.header_id = oel1.header_id
AND oel1.flow_status_code = 'CLOSED'
AND oel1.line_category_code = 'ORDER'
AND oeh.order_type_id = &ordtyp
-- AND oeh.order_number IN ( )
AND oeh.shipping_method_code IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM csi.csi_item_instances cii
WHERE cii.last_oe_order_line_id = oel1.line_id);
BEGIN
FOR i IN c_ib
LOOP
BEGIN
SELECT transaction_id
INTO l_mtl_txn_id
FROM mtl_material_transactions
WHERE trx_source_line_id = i.line_id
AND transaction_type_id = &transtyp;

-- dbms_output.put_line(i.order_number||' '||i.line_id);
csi_inv_txn_hook_pkg.posttransaction
(p_header_id => l_header_id,
p_transaction_id => l_mtl_txn_id,
x_return_status => l_return_status
);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
WRITE ('L', 'No Transaction ID for the Line ID : ' || i.line_id );
WHEN TOO_MANY_ROWS THEN
WRITE ('L','More then one Transaction ID for the Line ID : ' || i.line_id );
WHEN OTHERS THEN
WRITE ('L',
'Error in LineID :' || i.line_id || CHR (10) || SQLCODE || ' : ' || SQLERRM
);
END;
END LOOP;
END create_install_base;

/************************************************************************/
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : This procedure writes to the output file or log file depending
on the parameter p_type passed.
*************************************************************************/
BEGIN
IF p_type = 'L'
THEN
fnd_file.put_line (fnd_file.LOG, p_message);
ELSIF p_type = 'O'
THEN
fnd_file.put_line (fnd_file.output, p_message);
END IF;
END WRITE;
END alloracletech_ib_pkg;
/

Use the following to Execute the Procedure.

begin
ib_pkg.create_install_base;
end;
/

The following query can be used to find out whether Install Base (IB) created or not for the Order line.

select * from csi.csi_item_instances
where last_oe_order_line_id IN (Your Order Line ID);

Example:-

select * from csi.csi_item_instances
where last_oe_order_line_id IN (&lineid);
I hope you find the above information help full.

Note:- There are few things which are hard-corded like order_type_id etc,.

Note:- I have tested above script in the 11i Instances.

Prerequisites for IB creation as part of data flow .

Following is the summary of steps to be performed to setup Install Base

1. Set up Inventory attributes for IB trackable items

All items that need to be tracked in Oracle Install Base and Enterprise Install Base are set up as Oracle Install Base trackable, whether they are tangible or non-tangible. An item can be either Oracle Install Base trackable or a service item such as a contract. It cannot be both.

For an item to be set up as trackable in Oracle Install Base, the Install Base Tracking checkbox must be selected on the Service tabbed page of the Item window.

A Serviceable Item from Service Contracts automatically defaults the Install Base trackable flag.

2. Confirm the setup of Service Fulfillment Manager Event Queue

Oracle Install Base is dependent on the SFM Event Manager Queue Service in order to get data from source applications.

The SFM Event Manager Queue Service should have been set up prior to setting up Install Base. Proper operation of the queue service is essential for data to pass from E-Business Suite applications to Install Base. Monitor the status daily, and check it when expected item instances do not appear in Install Base.

3. Set up Order Management menus and workflows

This menu setup is required so that Installation Details and Maintain Systems are set up as part of the Action menu. Without this setup, for example, attempts to access the Transaction Details window produce an error message.

A workflow has to setup using the workflow builder. Once the workflow is defined, assign it to different transaction types in OM

4. Set up installation parameters

Oracle Install Base keeps a set of customer-specific installation parameters defined in a table at setup time. You use the Installed Parameters window to provide them. After you define them and select Freeze, the fields cannot be updated.

5. Set up extended attributes for IB instances

To set up extended attributes, the name and code of the attribute have to be set up in the pool of attributes. This is where users can define an attribute’s name, code, and description to be used in the LOV when the extended attribute is set up.

You can navigate to the Install Base Lookups window from the Oracle Installed Base Admin responsibility:

Type: CSI Lookup

Lookup Type: CSI_EXTEND_ATTRIB_POOL

Access Level: Extensible

Values are not seeded for this code.

6. Set up instance statuses

Instance statuses are used to describe the current state of an item instance. They are user-extendible and are defined through a combination of check boxes.

7. Set up transaction subtypes and use in Transactions Details

Before any source transaction can be used in the LOV for a transaction subtype, you must define the source transaction types. All integration sources and transaction types must be defined here before they can be used to update Oracle Install Base. Values can be seeded or user-defined.

You can navigate to the Source Transaction Types window from the Oracle Installed Base Admin responsibility.

8. Handling errors and using the transaction error processing program

A transaction error occurs when a transaction from an application that integrates with Install Base is not properly processed.

Schedule the Resubmit Interface Process to process the transactions in the Error processing table. This program can be set up to process selected lines or all lines in the table and can be run as needed or set up to run at a regular intervals.

9. Create business users

A Business user can view and perform limited updates to the equipment instances that belong to the business and accounts with which this type of user is associated.

Create business users as needed for Oracle Install Base. To do so, you must create business users and assign them the predefined CSI_END_USER role and the responsibility of an Oracle Install Base customer

10. Create agent users

Agent user is the user type for an enterprise's employees to support all the equipment instances in the application, regardless of its ownership. This user type has access to all the functionalities of the application.

Create agent users as needed for Oracle Install Base. To do so, you must create internal users and assign them the predefined CSI_NORMAL_USER role and the responsibility of an Oracle Install Base user.

11. Set up profile options and concurrent programs

CSI Allow Install Parameter Update	Use this profile option with great caution. The default = N. Do not alter this setting unless instructed to do so as part of patching or by Oracle Customer Support or Development. Changing the default can cause irrecoverable data corruption. (This profile option is set to Y after migration to allow a one-time update to install parameters. Then it is set to N).
CSI Auto-Generate System Name	Auto-generate System Number at time of system creation (Y or N).
CSI Auto-Split Instances During Instantiation	Auto split instances with multiple quantity to 1 per instance at time of instance creation (Y or N).
CSI BOM Explosion Level	Number of BOM levels to explode for creation of component-of configuration from BOM setup (1, 2, 3.). Note that BOM explosion stops at any level where child is at quantity > 1.
CSI Cascade System Termination	Cascade system termination to instances (Y or N).
CSI Configurator Enabled	Default = Network Models Only. Used for integration with the TSO solution and the Oracle Configurator product.
CSI Contracts Enabled	Enable Oracle Install Base integration with Oracle Service Contracts (Y or N).
CSI Counters Enabled	Enable Oracle Install Base integration with Counters (Y or N).
CSI Debug Level	For Debug, set at 9 or 10 for Debug to start.
CSI Default Instance Status	Default Instance Status at time of instance creation. Pick one status from the LOV.
CSI Default Version Label	Default version label at time of instance creation. Pick one from the LOV.
CSI Display HTML UI	Option to use Oracle Install Base HTML for display (Internal). Used by other applications to use the Oracle Install Base HTML UI (Y or N).
CSI Enable Contracts for Open Interfaces	Default = N. A setting of Y means that the Oracle Service Contracts application creates Service Warranties where applicable during an Open Interface run. Setting the profile option to N imports data faster than when it is set to Y.
CSI Enable SQL Trace	For Debug (Y or N). Set to Y to start Debug.
CSI Explode BOM	Enable BOM explosion for top assembly with Oracle Install Base trackable components at time of shipment/fulfillment (Y or N).
CSI Filter Display of all Contracts	Default = N. If set to Y, the application displays only active contracts.
CSI Forms to SSWA Default Responsibility	Default user for applications to launch HTML from forms. Default Installed Base User or from the LOV.
CSI Instance Termination Status	Default Termination Status. Pick one from the LOV.
CSI Log File Name	The name of the log file for Debug.
CSI Log File Path	For Debug ('utl-file-dir' parameter in init.ora).
CSI OE Line Processing Delay	Delay between OE line processing. Time between order line processing to allow for Oracle Install Base update completion to avoid record locking. Recommended delay: 60 seconds.
CSI: Open Interface Commit Record Limit	Used to commit Open Interface transactions to the data base. Default = 1000.
CSI Propagate System Changes	Propagate system changes to instances (Y or N).
CSI Propagate Systems Changes - Window Display	Condition of propagating system changes to products when Propagate System Changes is set to 'Y': Always Display: Change only when the system info matches the product information. Always Change: Do not display and always change. Never Change: Do not display and do not change:
CSI Restrict LOV’s on Site Usage.	Default = N. Set at Site Level. For value = Y, the application restricts the Installed Locations LOV in the Systems page to customer addresses with locations identified as Install At.
CSI: Show Expired Instances	Default = N. Enables users to default the value of the Show Expired Products checkbox in the Advanced Search page. If the profile option value = Y, then the application shows all instances including expired products in the Oracle Install Base UI.
CSI Stop At Debug Errors	Set to Y to start debug (Y or N).
CSI System Name Update Allowed	System name update allowed after system name creation (Y or N)
CSI: UI Default Selection of View Details Dropdown	Defines the default link populated in the ‘speed menu’ for summary results displayed in the Search Products page.
SERVICE Master Inventory Validation Org	Replaces the ASO: Product Organization profile option obsolete in a prior release. During the upgrade process, the inventory organization specified in this profile is used to upgrade customer products manually created in Oracle Service. After upgrade, this inventory organization is used to validate whether an item is trackable in Oracle Install Base and to derive other item parameters such as serialization when you create new item instances using Oracle Install Base. Oracle strongly recommends that you set this to a master inventory organization.

Concurrent Programs:

The following list describes the Concurrent Programs used for Oracle Install Base.

1. Expire End Dated Instances Program
2. Install Base Open Interface Program
3. Initiate Mass Edit Program
4. Process Mass Edit Program
5. Process Old Order Lines-Fulfillable Only Program
6. Resubmit Interface Process Program
7. Resubmit Waiting Transactions Program

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;