Fusion: SQL: Query to Extract the Business Units and its related Inventory Organizations, Business Units and Costing Organizations

 

 

SELECT hou.organization_id
       , hou.name organization_name
       , iop.organization_code organization_code
       , lgr.ledger_id set_of_book_id
       , lgr.chart_of_accounts_id chart_of_account_id
       , lgr.currency_code
       , lgr.period_set_name
       , Decode(hoi.status, 'A', 'Y', 'N') inv_enabled_flag
       , bu.bu_name business_unit_name
       , iop.business_unit_id business_unit_id
       , iop.legal_entity_id legal_entity
       , hou.type organization_type
  FROM hr_all_organization_units_x hou
       , hr_org_unit_classifications_x hoi
       , inv_org_parameters iop
       , gl_ledgers lgr
       , fun_all_business_units_v bu
 WHERE hou.organization_id = hoi.organization_id
   AND hou.organization_id = iop.organization_id
   AND hoi.classification_code = 'INV'
   AND bu.primary_ledger_id = lgr.ledger_id(+)
   AND lgr.object_type_code(+) = 'L'
   AND NVL(lgr.complete_flag, 'Y') = 'Y'
   AND bu.bu_id(+) = iop.business_unit_id;  

SQL : Get Business Unit Details

===================================

 SELECT hauft.NAME BusinessUnit, hauft.organization_id
  FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date    AND houcf.effective_end_date
   AND hauft.language = 'US'
   AND hauft.effective_start_date = haouf.effective_start_date
   AND hauft.effective_end_date = haouf.effective_end_date
   AND houcf.classification_code = 'FUN_BUSINESS_UNIT'
   AND sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
 ORDER BY hauft.NAME ASC nulls first ; 

SQL : Get Costing Organization Details

===================================

 SELECT hauft.NAME BusinessUnit
  FROM hr_org_unit_classifications_f houcf,
       hr_all_organization_units_f haouf,
       hr_organization_units_f_tl hauft
 WHERE haouf.organization_id = houcf.organization_id
   AND haouf.organization_id = hauft.organization_id
   AND haouf.effective_start_date BETWEEN houcf.effective_start_date AND houcf.effective_end_date
   AND hauft.language = 'US'
   AND hauft.effective_start_date = haouf.effective_start_date
   AND hauft.effective_end_date = haouf.effective_end_date
   AND houcf.classification_code = 'CST'
   AND sysdate BETWEEN hauft.effective_start_date AND hauft.effective_end_date
 ORDER BY hauft.NAME ASC nulls first; 

Fusion:SCM:MFG: SQL to Validat the Componenet Issue and WO Completion transactions against Work Order

SELECT imt.transaction_id,iop.organization_code warehouse_code
     , TO_CHAR(imt.creation_date,'DD-MON-YYYY') txn_creation_date
     , ittv.transaction_type_name
     , flva.meaning transaction_action
     , itst.transaction_source_type_name
     , esi.item_number
     , imt.subinventory_code
     , imt.transaction_quantity
     , imt.transaction_uom
     , imt.transaction_date
     , imt.transaction_source_name     
     , iut.serial_number   
  FROM fusion.inv_material_txns imt
     , fusion.inv_org_parameters iop
     , fusion.egp_system_items_b esi
     , fusion.inv_transaction_types_vl ittv
     , fusion.fnd_lookup_values_vl flva
     , fusion.inv_txn_source_types_vl itst
     , fusion.inv_unit_transactions iut
 WHERE imt.organization_id = iop.organization_id
   AND imt.organization_id = esi.organization_id
   AND imt.inventory_item_id = esi.inventory_item_id
   AND imt.transaction_type_id = ittv.transaction_type_id
   AND ittv.transaction_action_id = flva.lookup_code
   AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
   AND flva.enabled_flag = 'Y'
   AND ittv.transaction_source_type_id = itst.transaction_source_type_id
   AND imt.transaction_id = iut.transaction_id(+)
   AND UPPER(ittv.transaction_type_name) IN ('WORK IN PROCESS MATERIAL ISSUE', 'WORK IN PROCESS PRODUCT COMPLETION')
   AND UPPER(itst.transaction_source_type_name) = 'WORK ORDER'
   AND esi.item_number = NVL(:p_item_number,esi.item_number)
   AND iop.organization_code = NVL(:p_org_code,iop.organization_code)
   AND imt.transaction_source_name = NVL(:p_wo_number, imt.transaction_source_name)
   AND esi.item_number = NVL(:p_item_number, esi.item_number)
   ORDER BY imt.creation_date desc;

Oracle Fusion SQL : SQL to Extract Transfer Order details

SELECT d.header_number
       , e.line_number
       , so.organization_code source_inv_org
       , do.organization_code dest_inv_org
       , sku.item_number
       , e.requested_qty
       , e.shipped_qty
       , e.received_qty
       , e.delivered_qty
       , e.source_subinventory_code source_subinventory
       , e.destination_subinventory_code dest_subinventory
       , d.fulfill_orchestration_required
       , d.source_type_lookup  hdr_source_type_lookup
       , e.source_organization_id
       , e.destination_organization_id
       , e.line_id
       , e.interface_status_lookup
       , e.source_type_lookup
       , e.destination_type_lookup
       , e.destination_location_id
       , e.status_lookup
       , e.need_by_date
       , e.scheduled_ship_date
       , e.qty_uom_code
       , e.unit_price
       , e.req_bu_id
       , e.supply_order_reference_number
       , e.supply_order_ref_line_number
       , d.header_id
       , sku.inventory_item_id
       , d.creation_date
  FROM fusion.inv_transfer_order_headers d
       , fusion.inv_transfer_order_lines e
       , fusion.inv_org_parameters so
       , fusion.inv_org_parameters do
       , fusion.egp_system_items_b sku
 WHERE 1 = 1
   AND sku.inventory_item_id = e.inventory_item_id
   AND sku.organization_id = e.source_organization_id
   AND do.organization_id = e.destination_organization_id
   AND so.organization_id = e.source_organization_id
   AND d.header_id = e.header_id
   AND so.organization_code = :p_from_organization
   AND do.organization_code = :p_to_organization
   AND sku.item_number = NVL(:p_part_number, sku.item_number)
   AND e.supply_order_reference_number = Nvl(:p_supply_order, e.supply_order_reference_number)
 ORDER BY d.creation_date DESC
          , 1
          , Lpad(e.line_number, 2);

Oracle Fusion SQL: OSQL to Extract Inventory Pending Transactions (Records stuck in Oracle inventory Interface table)

 SELECT iop.organization_code,
    iop.organization_name,
    ittv.transaction_type_name,
    flva.meaning transaction_action,
    mti.tRANSACTION_SOURCE_NAME,
    itst.transaction_source_type_name,
    mti.transaction_interface_id txn_iface_id,
    mti.transaction_header_id txn_hdr_id,
    mti.inventory_item_id inv_item_id,
    egp.item_number,
    mti.subinventory_code subinv_code,
    mti.transaction_quantity txn_qty,
    mti.transaction_uom uom,
    mti.transaction_type_id txn_typ_id,
    mti.transaction_action_id txn_act_id,
    mti.transaction_source_type_id txn_sty_id,
    mti.transaction_source_id txn_src_id,
    TO_CHAR(mti.transaction_date, 'DD-MON-YYYY HH24:MI:SS') txn_date,
    mti.source_code src_code,
    mti.process_flag pflag,
    decode(to_char(nvl(mti.process_flag, 0)),'1', 'Ready', '2', 'Not Ready', '3', 'Error', to_char(mti.process_flag) ) pflag_desc,
    mti.transaction_mode tmode,
    decode( mti.transaction_mode, '2', 'Immediate', '3', 'Background', to_char(mti.transaction_mode) ) tmode_desc,
    NVL(mti.lock_flag, 'N') lflag,
    decode(mti.lock_flag, '1', 'Locked', '2', 'Not Locked', 'Not Locked') lflag_desc,
    mti.error_code error_code,
    mti.error_explanation error_explanation
FROM fusion.inv_transactions_interface mti,
    fusion.inv_transaction_types_vl ittv,
    fusion.fnd_lookup_values_vl flva,
    fusion.inv_txn_source_types_vl itst,
    fusion.egp_system_items_b egp,
    fusion.inv_organization_definitions_v iop
WHERE 1 = 1
  AND ittv.transaction_source_type_id = itst.transaction_source_type_id
  AND mti.transaction_type_id = ittv.transaction_type_id
  AND ittv.transaction_action_id = flva.lookup_code
  AND flva.lookup_type = 'INV_TRANSACTION_ACTION'
  AND flva.enabled_flag = 'Y'
  AND iop.organization_id = egp.organization_id
  AND mti.organization_id = egp.organization_id
  AND mti.inventory_item_id = egp.inventory_item_id
  AND iop.organization_code = NVL(:p_organization_code, iop.organization_code)
  AND egp.item_number = NVL(:p_item_number, egp.item_number)
  AND mti.error_code IS NOT NULL
ORDER BY transaction_date;

Oracle Fusion: SQL to extract Inventory Organization Parameters Details

 

SELECT a.organization_id,
       a.organization_code,
       g.name organization_name,
       a.master_organization_id,
       b.organization_code master_organization_code,
       a.business_unit_id,
       a.legal_entity_id,
       a.negative_inv_receipt_code,
       e.meaning neg_meaning,
       a.stock_locator_control_code,
       c.meaning stock_locator_meaning,
       a.serial_number_type,
       d.meaning serial_number_type_meaning,
       a.lot_number_uniqueness,
       f.meaning lot_uniq_meaning
FROM inv_org_parameters a,
     inv_org_parameters b,
     fnd_lookups c,
     fnd_lookups d,
     fnd_lookups e,
     fnd_lookups f,
     hr_organization_units_f_tl g
WHERE a.master_organization_id = b.organization_id
  AND c.lookup_type = 'INV_LOCATION_CONTROL'
  AND a.stock_locator_control_code = c.lookup_code (+)
  AND d.lookup_type = 'INV_SERIAL_NUMBER'
  AND a.serial_number_type = d.lookup_code (+)
  AND e.lookup_type = 'INV_YES_NO_NUMERIC'
  AND a.negative_inv_receipt_code = e.lookup_code (+)
  AND f.lookup_type = 'INV_LOT_UNIQUENESS'
  AND a.lot_number_uniqueness = f.lookup_code (+)
  AND g.organization_id = a.organization_id
  AND g.language = 'US'
ORDER BY a.organization_id

Oralce Fusion: SQL to Get Concatenated Address example

Inventory Organization Location

=================================

SELECT z.organization_code,
       z.organization_name,
       b.location_id,
       b.active_status,
       b.effective_start_date,
       b.effective_end_date,
       b.internal_location_code,
       b.location_use,
       b.location_code,
       b.location_name,
       b.description,
       b.style,
       Concat(Concat(Concat(Concat(Concat(Concat(
                     Concat(Concat(b.address_line_1 || ', ',
                     Nvl2(b.address_line_2, b.address_line_2 || ', ', b.address_line_2)),
                     Nvl2(b.address_line_3, b.address_line_3 || ', ', b.address_line_3)),
                     Nvl2(b.address_line_4, b.address_line_4 || ', ', b.address_line_4)),
                     Nvl2(b.town_or_city, b.town_or_city || ', ', b.town_or_city)),
                     Nvl2(b.region_1, b.region_1 || ', ', b.region_1)),
                     Nvl2(b.region_2, b.region_2 || ', ', b.region_2)),
                     Nvl2(b.country, country || ', ', b.country)), b.postal_code) concatenated_address
FROM   inv_organization_definitions_v z,
       hr_locations b
WHERE  z.location_id = b.location_id;

 

Party/Customer/HZ Location

=================================

SELECT  concat(concat(concat(concat(concat(concat(concat(concat(address1||', ',NVL2(address2,address2||', ',address2)),NVL2(address3,address3||', ',address3)),NVL2(address4,address4||', ',address4)),NVL2(city,city||', ',city)),NVL2(county,county||', ',county)),NVL2(state,state||', ',state)),NVL2(province, province||', ',province)),postal_code) concatenated_address
  FROM hz_locations
 WHERE country ='US';  

SELECT hzp.party_name
       || ' '
       || hzp.party_number,
       hzp.party_id,
       hzps.party_site_number "ShipToPartySiteNumber",
       HZA.account_number,
       HZA.account_name,
       hza.cust_account_id,
       HZA.status "Account Status",
       hzp.status "Party Status",
       hzps.status  "Party Site Status",
       hzps.party_site_id  "PARTY SITE ID - for SHIP_TO",
       hzcasa.status "Account Site Status",
       hzcsua.site_use_id "Account Site ID - for BILL_TO",
       hzcasa.start_date,
       hzcasa.end_date,
       hzcsua.site_use_code,
       hzcasa.bill_to_flag,
       hzcasa.ship_to_flag,
       hzcsua.primary_flag,
       hzcsua.status "Account Site USE Status",
       hzcsua.location,
     concat(concat(concat(concat(concat(concat(concat(concat(hzl.address1||', ', NVL2(hzl.address2, hzl.address2||', ', hzl.address2)), NVL2(hzl.address3, hzl.address3||', ', hzl.address3)), NVL2(hzl.address4, hzl.address4||', ', hzl.address4)), NVL2(hzl.city, hzl.city||', ', hzl.city)), NVL2(hzl.county, hzl.county||', ', hzl.county)), NVL2(hzl.state, hzl.state||', ', hzl.state)), NVL2(hzl.province, hzl.province||', ', hzl.province)), hzl.postal_code) concatenated_address,
       hzl.location_id
FROM   fusion.hz_parties HZP,
       fusion.hz_party_sites hzps,
       fusion.hz_cust_accounts HZA,
       fusion.hz_cust_acct_sites_all hzcasa,
       fusion.hz_cust_site_uses_all hzcsua,
       fusion.hz_locations HZL
WHERE  hzP.party_id = HZA.party_id (+)
       AND hza.cust_account_id = hzcasa.cust_account_id (+)
       AND hzcasa.party_site_id = hzps.party_site_id (+)
       AND hzcasa.cust_acct_site_id = hzcsua.cust_acct_site_id (+)
       AND hzps.location_id = hzl.location_id (+)
       AND hzp.party_number = :p_party_number
       AND hzcsua.primary_flag = 'Y'
ORDER  BY hzp.party_number,
          hza.account_number,
          hzl.location_id

Script to get the Inventory transaction types/Source/Action Name

Script to get the Inventory transaction types/Source/Action Name

  SELECT a.transaction_type_id
       , a.transaction_type_name
       , a.transaction_source_type_id
       , b.transaction_source_type_name
       , a.transaction_action_id
       , c.meaning
    FROM mtl_transaction_types a
       , mtl_txn_source_types b
       , mfg_lookups c
   WHERE a.transaction_source_type_id = b.transaction_source_type_id
     AND a.transaction_action_id = c.lookup_code
     AND c.lookup_type = 'MTL_TRANSACTION_ACTION'
ORDER BY transaction_type_id;

Sample script to update the Item attributes (Inventory)

DECLARE
   CURSOR c1
   IS
      SELECT inventory_item_id
           , segment1
        FROM mtl_system_items_b
       WHERE organization_id = 103
         AND inventory_item_status_code = 'PreRelease';

   TYPE sku_tbl_typ IS TABLE OF c1%ROWTYPE
      INDEX BY SIMPLE_INTEGER;

   sku_tbl                                      sku_tbl_typ;

   l_api_version                                NUMBER := 1.0;
   l_init_msg_list                              VARCHAR2(2) := fnd_api.g_true;
   l_commit                                     VARCHAR2(2) := fnd_api.g_false;
   l_item_tbl                                   ego_item_pub.item_tbl_type;
   l_role_grant_tbl                             ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
   x_item_tbl                                   ego_item_pub.item_tbl_type;
   x_message_list                               error_handler.error_tbl_type;
   x_return_status                              VARCHAR2(2);
   x_msg_count                                  NUMBER := 0;
   l_user_id                                    NUMBER := -1;
   l_resp_id                                    NUMBER := -1;
   l_application_id                             NUMBER := -1;
   l_rowcnt                                     NUMBER := 1;
   l_user_name                                  VARCHAR2(30) := '<USER_NAME>';
   l_resp_name                                  VARCHAR2(30) := 'EGO_DEVELOPMENT_MANAGER';
   i                                            NUMBER;
BEGIN
   -- Get the user_id
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

   -- Get the application_id and responsibility_id
   SELECT application_id
        , responsibility_id
     INTO l_application_id
        , l_resp_id
     FROM fnd_responsibility
    WHERE responsibility_key = l_resp_name;

   fnd_global.apps_initialize(l_user_id
                            , l_resp_id
                            , l_application_id);
   DBMS_OUTPUT.put_line(   'Initialized applications context: '
                        || l_user_id
                        || ' '
                        || l_resp_id
                        || ' '
                        || l_application_id);

   OPEN c1;

   FETCH c1 BULK COLLECT INTO sku_tbl;

   CLOSE c1;

   IF sku_tbl.COUNT > 0
   THEN
      i                                                                            := sku_tbl.FIRST;

      LOOP
         l_item_tbl( i).transaction_type                                              := 'UPDATE';
         l_item_tbl( i).inventory_item_id                                             := sku_tbl(i).inventory_item_id;
         l_item_tbl( i).inventory_item_status_code                                    := 'Active';
         l_item_tbl( i).organization_id                                               := 103;
         EXIT WHEN i = sku_tbl.LAST;
         i                                                                            := sku_tbl.NEXT( i);
      END LOOP;
   END IF;

   -- call API to load Items
   DBMS_OUTPUT.put_line( '=====================================');
   DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.Process_Items API');
   ego_item_pub.process_items(p_api_version                             => l_api_version
                            , p_init_msg_list                           => l_init_msg_list
                            , p_commit                                  => l_commit
                            , p_item_tbl                                => l_item_tbl
                            , p_role_grant_tbl                          => l_role_grant_tbl
                            , x_item_tbl                                => x_item_tbl
                            , x_return_status                           => x_return_status
                            , x_msg_count                               => x_msg_count);

   DBMS_OUTPUT.put_line( '=====================================');
   DBMS_OUTPUT.put_line(   'Return Status: '
                        || x_return_status);

   IF x_return_status = fnd_api.g_ret_sts_success
   THEN
            FOR i IN 1 .. x_item_tbl.COUNT
            LOOP
               DBMS_OUTPUT.put_line(   'Inventory Item Id :'
                                    || TO_CHAR( x_item_tbl( i).inventory_item_id));
               DBMS_OUTPUT.put_line(   'Organization Id   :'
                                    || TO_CHAR( x_item_tbl( i).organization_id));
            END LOOP;

      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line( 'Error Messages :');
      error_handler.get_message_list( x_message_list => x_message_list);

      FOR i IN 1 .. x_message_list.COUNT
      LOOP
         DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
      END LOOP;
   END IF;

   DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line( 'Exception Occured :');
      DBMS_OUTPUT.put_line(   SQLCODE
                           || ':'
                           || SQLERRM);
      DBMS_OUTPUT.put_line( '=====================================');
END;

Script to create New Item copying existing SKU, it's attributes and all categories and also Assign it to all Inventory Organization.

Please test the script first in NON PROD instance.

DECLARE
   k                                            NUMBER;
   l_api_version                                NUMBER := 1.0;
   l_init_msg_list       VARCHAR2(2) := fnd_api.g_true;
   l_commit              VARCHAR2(2) := fnd_api.g_false;
   l_item_tbl            ego_item_pub.item_tbl_type;
   l_role_grant_tbl                             ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
   x_item_tbl        ego_item_pub.item_tbl_type;
   x_message_list    error_handler.error_tbl_type;
   x_return_status                              VARCHAR2(2);
   x_msg_count                                  NUMBER := 0;

   l_user_id                                    NUMBER := -1;
   l_resp_id                                    NUMBER := -1;
   l_application_id                             NUMBER := -1;
   l_rowcnt                                     NUMBER := 1;
   l_user_name         VARCHAR2(30) := '<USER_NAME>';
   l_resp_name         VARCHAR2(30) := '<EGO_DEVELOPMENT_MANAGER>';

   CURSOR c_query_org
   IS
      SELECT organization_id FROM mtl_parameters;

   TYPE organization_id_tbl_typ IS TABLE OF NUMBER
      INDEX BY SIMPLE_INTEGER;

   organization_id_tbl       organization_id_tbl_typ;

   PROCEDURE item_assignment(p_organization_id                            IN NUMBER
                           , p_inventory_item_id                          IN NUMBER)
   IS
      p_api_version                                NUMBER;
      p_init_msg_list                              VARCHAR2(32767);
      p_commit                                     VARCHAR2(32767);
      p_item_number                                VARCHAR2(32767);
      p_organization_code                          VARCHAR2(32767);
      p_primary_uom_code                           VARCHAR2(32767);
      x_return_status                              VARCHAR2(32767);
      x_msg_count                                  NUMBER;
      l_rowcnt                                     NUMBER := 1;
      x_message_list                               error_handler.error_tbl_type;
   BEGIN
      p_api_version             := 1.0;
      p_init_msg_list           := fnd_api.g_true;
      p_commit                  := fnd_api.g_false;
      DBMS_OUTPUT.put_line( '=====================================');
      DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.assign_item_to_org API');
      apps.ego_item_pub.assign_item_to_org(p_api_version
                                         , p_init_msg_list
                                         , p_commit
                                         , p_inventory_item_id
                                         , p_item_number
                                         , p_organization_id
                                         , p_organization_code
                                         , p_primary_uom_code
                                         , x_return_status
                                         , x_msg_count);
      DBMS_OUTPUT.put_line( '=====================================');
      DBMS_OUTPUT.put_line(   'Return Status: '
                           || x_return_status);

      IF (x_return_status = fnd_api.g_ret_sts_success)
      THEN
         DBMS_OUTPUT.put_line(   'Inventory Item Id :'
                              || TO_CHAR( p_inventory_item_id));
         DBMS_OUTPUT.put_line(   'Organization Id   :'
                              || TO_CHAR( p_organization_id));
      ELSE
         DBMS_OUTPUT.put_line( 'Error Messages :');
         error_handler.get_message_list( x_message_list => x_message_list);

         FOR i IN 1 .. x_message_list.COUNT
         LOOP
            DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
         END LOOP;
      END IF;

      COMMIT;
      DBMS_OUTPUT.put_line( '=====================================');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line( 'Exception Occured :');
         DBMS_OUTPUT.put_line(   SQLCODE
                              || ':'
                              || SQLERRM);
         DBMS_OUTPUT.put_line( '=====================================');
         COMMIT;
   END;

   PROCEDURE process_item_cat_assignment(
             p_source_item_id           IN NUMBER
           , p_desti_item_id            IN NUMBER)
   IS
      CURSOR c1
      IS
         SELECT /*+ CARDINALITY (MSI 1)*/
               msi.organization_id
              , msi.inventory_item_id
              , mic.category_id
              , mic.category_set_id
           FROM mtl_item_categories_v mic
              , mtl_system_items_b msi
          WHERE 1 = 1
            AND msi.organization_id = mic.organization_id
            AND msi.inventory_item_id = mic.inventory_item_id
            AND msi.inventory_item_id = p_source_item_id
            AND msi.organization_id = &&&Mas_org_id;

      l_api_version                                NUMBER := 1.0;
      l_init_msg_list          VARCHAR2(2) := fnd_api.g_true;
      l_commit                 VARCHAR2(2) := fnd_api.g_false;
      l_item_tbl               ego_item_pub.item_tbl_type;
      l_role_grant_tbl                    ego_item_pub.role_grant_tbl_type := ego_item_pub.g_miss_role_grant_tbl;
      x_item_tbl           ego_item_pub.item_tbl_type;
      x_message_list       error_handler.error_tbl_type;
      x_return_status                              VARCHAR2(2);
      x_msg_data                                   VARCHAR2(2000);
      x_msg_count                                  NUMBER := 0;
      x_errorcode                                  NUMBER;
   BEGIN
      FOR z IN c1
      LOOP
         -- call API to load Items
         DBMS_OUTPUT.put_line( '=====================================');
         DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.process_item_cat_assignment API');
         ego_item_pub.process_item_cat_assignment(p_api_version                             => 1.0
         , p_init_msg_list               => fnd_api.g_true
         , p_commit                      => fnd_api.g_true
         , p_category_id                 => z.category_id
         , p_category_set_id             => z.category_set_id
         , p_old_category_id             => NULL
         , p_inventory_item_id           => p_desti_item_id
         , p_organization_id             => z.organization_id
         , p_transaction_type            => 'CREATE'
         , x_return_status               => x_return_status
         , x_errorcode                   => x_errorcode
         , x_msg_count                   => x_msg_count
         , x_msg_data                    => x_msg_data);

         DBMS_OUTPUT.put_line( '=====================================');
         DBMS_OUTPUT.put_line(   'Return Status: '
                              || x_return_status);

         IF (x_return_status = fnd_api.g_ret_sts_success)
         THEN
            FOR i IN 1 .. x_item_tbl.COUNT
            LOOP
               DBMS_OUTPUT.put_line(   'Inventory Item Id :'
                                    || TO_CHAR( x_item_tbl( i).inventory_item_id));
               DBMS_OUTPUT.put_line(   'Organization Id   :'
                                    || TO_CHAR( x_item_tbl( i).organization_id));
            END LOOP;
         ELSE
            DBMS_OUTPUT.put_line( 'Error Messages :');
            error_handler.get_message_list( x_message_list => x_message_list);

            FOR i IN 1 .. x_message_list.COUNT
            LOOP
               DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
            END LOOP;
         END IF;
      END LOOP;

      COMMIT;
      DBMS_OUTPUT.put_line( '=====================================');
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line( 'Exception Occured :');
         DBMS_OUTPUT.put_line(   SQLCODE
                              || ':'
                              || SQLERRM);
         DBMS_OUTPUT.put_line( '=====================================');
   END;
BEGIN
   -- Get the user_id
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

   -- Get the application_id and responsibility_id
   SELECT application_id
        , responsibility_id
     INTO l_application_id
        , l_resp_id
     FROM fnd_responsibility
    WHERE responsibility_key = l_resp_name;

   fnd_global.apps_initialize(l_user_id
                            , l_resp_id
                            , l_application_id);
   DBMS_OUTPUT.put_line(   'Initialized applications context: '
                        || l_user_id
                        || ' '
                        || l_resp_id
                        || ' '
                        || l_application_id);

   l_item_tbl( 1).transaction_type                          := 'CREATE';
   l_item_tbl( 1).copy_inventory_item_id                    := &Item_id_to_be_Copied;
   l_item_tbl( 1).segment1                                  := &New_item_name;
   l_item_tbl( 1).description                               := &New_item_Description;
   l_item_tbl( 1).organization_id                           := 103;

   -- call API to load Items
   DBMS_OUTPUT.put_line( '=====================================');
   DBMS_OUTPUT.put_line( 'Calling EGO_ITEM_PUB.Process_Items API');
   ego_item_pub.process_items(p_api_version                             => l_api_version
                            , p_init_msg_list                           => l_init_msg_list
                            , p_commit                                  => l_commit
                            , p_item_tbl                                => l_item_tbl
                            , p_role_grant_tbl                          => l_role_grant_tbl
                            , x_item_tbl                                => x_item_tbl
                            , x_return_status                           => x_return_status
                            , x_msg_count                               => x_msg_count);

   DBMS_OUTPUT.put_line( '=====================================');
   DBMS_OUTPUT.put_line(   'Return Status: '
                        || x_return_status);

   IF (x_return_status = fnd_api.g_ret_sts_success)
   THEN
      FOR i IN 1 .. x_item_tbl.COUNT
      LOOP
         DBMS_OUTPUT.put_line(   'Inventory Item Id :'
                              || TO_CHAR( x_item_tbl( i).inventory_item_id));
         DBMS_OUTPUT.put_line(   'Organization Id   :'
                              || TO_CHAR( x_item_tbl( i).organization_id));

         process_item_cat_assignment(l_item_tbl( 1).copy_inventory_item_id
                                   , x_item_tbl( i).inventory_item_id);

         OPEN c_query_org;

         FETCH c_query_org BULK COLLECT INTO organization_id_tbl;

         CLOSE c_query_org;

         k                                                        := organization_id_tbl.FIRST;

         LOOP
            item_assignment(p_organization_id                         => organization_id_tbl( k)
                          , p_inventory_item_id                       => x_item_tbl( i).inventory_item_id);
            EXIT WHEN k = organization_id_tbl.LAST;
            k                                                        := organization_id_tbl.NEXT( k);
         END LOOP;
      END LOOP;
   ELSE
      DBMS_OUTPUT.put_line( 'Error Messages :');
      error_handler.get_message_list( x_message_list => x_message_list);

      FOR i IN 1 .. x_message_list.COUNT
      LOOP
         DBMS_OUTPUT.put_line( x_message_list( i).MESSAGE_TEXT);
      END LOOP;
   END IF;

   COMMIT;
   DBMS_OUTPUT.put_line( '=====================================');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line( 'Exception Occured :');
      DBMS_OUTPUT.put_line(   SQLCODE
                           || ':'
                           || SQLERRM);
      DBMS_OUTPUT.put_line( '=====================================');
END;
/ 

API to get the On hand quantity details for Item in Inveontory

PROCEDURE get_onhand_quantities(
   p_item_name              IN            VARCHAR2
 , p_organziation_id        IN            NUMBER
 , p_subinv                 IN            VARCHAR2 DEFAULT NULL
 , l_qty_oh                    OUT NOCOPY NUMBER
 , l_qty_res_oh                OUT NOCOPY NUMBER
 , l_qty_res                   OUT NOCOPY NUMBER
 , l_qty_sug                   OUT NOCOPY NUMBER
 , l_qty_att                   OUT NOCOPY NUMBER
 , l_qty_atr                   OUT NOCOPY NUMBER)
IS
   CURSOR c_get_item_id(
    c_item_name                                   VARCHAR2
  , c_organization_id                             NUMBER)
   IS
      SELECT inventory_item_id
           , organziation_id
        FROM mtl_system_items_b
       WHERE segment1 = c_item_name
         AND organization_id = c_organization_id;

   l_api_return_status                          VARCHAR2(1);
   l_qty_oh                                     NUMBER;
   l_qty_res_oh                                 NUMBER;
   l_qty_res                                    NUMBER;
   l_qty_sug                                    NUMBER;
   l_qty_att                                    NUMBER;
   l_qty_atr                                    NUMBER;
   l_msg_count                                  NUMBER;
   l_msg_data                                   VARCHAR2(250);
   x_return_msg                                 VARCHAR2(4000);
   l_msg_index_out                              NUMBER;
   l_organziation_id                            NUMBER;
   l_item_id                                    NUMBER;
BEGIN
   OPEN c_get_item_id(p_item_name
                    , p_organziation_id);

   LOOP
      FETCH c_get_item_id
         INTO l_item_id
            , l_organziation_id;

      EXIT WHEN c_get_item_id%NOTFOUND;
      apps.inv_quantity_tree_grp.clear_quantity_cache;

      apps.inv_quantity_tree_pub.query_quantities(
         p_api_version_number                      => 1.0
       , p_init_msg_lst                            => apps.fnd_api.g_false
       , x_return_status                           => l_api_return_status
       , x_msg_count                               => l_msg_count
       , x_msg_data                                => l_msg_data
       , p_organization_id                         => l_organziation_id
       , p_inventory_item_id                       => l_item_id
       , p_tree_mode                               => apps.inv_quantity_tree_pub.g_transaction_mode
       , p_onhand_source                           => 3
       , p_is_revision_control                     => FALSE
       , p_is_lot_control                          => FALSE
       , p_is_serial_control                       => FALSE
       , p_revision                                => NULL
       , p_lot_number                              => NULL
       , p_subinventory_code                       => p_subinv
       , p_locator_id                              => NULL
       , x_qoh                                     => l_qty_oh
       , x_rqoh                                    => l_qty_res_oh
       , x_qr                                      => l_qty_res
       , x_qs                                      => l_qty_sug
       , x_att                                     => l_qty_att
       , x_atr                                     => l_qty_atr);

      IF l_api_return_status = fnd_api.g_ret_sts_success
      THEN
         DBMS_OUTPUT.put_line(   'Total on hand quantity : '
                              || l_qty_oh
                              || ' Quantity res oh :'
                              || l_qty_res_oh
                              || ' Quantity reserved :'
                              || l_qty_res
                              || ' Quantity sug :'
                              || l_qty_sug
                              || ' Quantity Available to transat :'
                              || l_qty_att
                              || ' Quantity available to reserve :'
                              || l_qty_atr);
      ELSE
         FOR i IN 1 .. l_msg_count
         LOOP
            fnd_msg_pub.get(p_msg_index                               => i
                          , p_encoded                                 => fnd_api.g_false
                          , p_data                                    => l_msg_data
                          , p_msg_index_out                           => l_msg_index_out);

            IF x_return_msg IS NULL
            THEN
               x_return_msg                              :=
                  SUBSTR(l_msg_data
                       , 1
                       , 250);
            ELSE
               x_return_msg                              :=
                     x_return_msg
                  || ','
                  || SUBSTR(l_msg_data
                          , 1
                          , 250);
            END IF;
         END LOOP;

         DBMS_OUTPUT.put_line(   ' QUERY_QUANTITIES API Failure : '
                              || x_return_msg);
      END IF;
   END LOOP;

   CLOSE c_get_item_id;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line(   'UNEXP_ERROR : '
                           || SUBSTR(SQLERRM
                                   , 1
                                   , 250));
END;
/