APPS.INV_UPDATE_ONHAND_STATUS SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 15

  PROCEDURE update_onhand_status(
                              x_errbuf            OUT NOCOPY VARCHAR2
                             ,x_retcode           OUT NOCOPY NUMBER
                             ,p_from_org_code     IN  VARCHAR2
                             ,p_to_org_code       IN  VARCHAR2
                             ,p_default_status    IN  VARCHAR2 )  IS

      l_ret BOOLEAN ;

Line: 37

      SELECT rowid  FROM mtl_onhand_quantities_detail moqd
      WHERE moqd.organization_id = org_id
      AND  moqd.status_id is NULL
      AND  rownum < l_bulk_limit
      AND EXISTS(
                   select 1 from mtl_system_items msi
                   WHERE moqd.inventory_item_id = msi.inventory_item_id
                   AND moqd.organization_id = msi.organization_id
                   AND msi.serial_number_control_code in (1,6)
                )
      ORDER BY subinventory_code
      FOR UPDATE NOWAIT ;

Line: 51

      SELECT organization_id FROM mtl_parameters
      WHERE  organization_code BETWEEN l_from_org_code AND l_to_org_code ;

Line: 62

       orgid_list.DELETE;

Line: 68

         print_debug('No organization selected for given range. Please choose valid range');

Line: 100

       UPDATE mtl_material_statuses_b
          SET onhand_control = 1
          WHERE Nvl(onhand_control,2) <> 1;

Line: 121

      SELECT  NVL(default_status_id, -1)
      INTO   l_default_status_id
      FROM   mtl_parameters
      WHERE  organization_id = orgid_list(i);

Line: 131

             rowid_list.DELETE;

Line: 138

                print_debug('No more onhand records to be updated for orgid');

Line: 143

                UPDATE mtl_onhand_quantities_detail moqd
                   SET status_id = inv_material_status_grp.get_default_status_conc(moqd.organization_id,
                                                                                   moqd.inventory_item_id,
                                                                                   moqd.subinventory_code,
                                                                                   moqd.locator_id,
                                                                                   moqd.lot_number,
                                                                                   moqd.lpn_id)
                 WHERE  rowid = rowid_list(j);

Line: 161

         print_debug('Updated :'||l_record_count||' rows in MOQD for orgid = ' || orgid_list(i));

Line: 164

          SELECT  Count(1)
          INTO  l_count
          FROM  mtl_onhand_quantities_detail moqd, mtl_system_items_b msi
          WHERE moqd.inventory_item_id = msi.inventory_item_id
          AND moqd.organization_id = msi.organization_id
          AND moqd.organization_id = orgid_list(i)
          AND msi.serial_number_control_code in (1,6)
          AND moqd.status_id is null
          AND  rownum = 1;

Line: 176

             UPDATE mtl_parameters
                SET  default_status_id = (SELECT  status_id
                                          FROM  mtl_material_statuses_vl
                                          WHERE status_code = p_default_status)
                WHERE  organization_id = orgid_list(i);

Line: 181

             print_debug('Updated Default Material Status at organization level for org: '|| orgid_list(i));

Line: 210

  END update_onhand_status;