APPS.INV_PHY_INV_LOVS SQL Statements

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

Line: 44

     SELECT physical_inventory_name,
     physical_inventory_id,
     description,
     freeze_date,
     adjustments_posted,
     approval_required,
     cost_variance_neg,
     cost_variance_pos,
     approval_tolerance_neg,
     approval_tolerance_pos,
     all_subinventories_flag,
     dynamic_tag_entry_flag
     FROM mtl_physical_inventories_v
     WHERE organization_id = p_organization_id
     AND snapshot_complete = 1
     AND adjustments_posted <> 1
     AND physical_inventory_name LIKE (p_phy_inv)
     ORDER BY physical_inventory_name;

Line: 107

   SELECT COUNT(*)
     INTO x_number
     FROM mtl_physical_inventory_tags
     WHERE physical_inventory_id = p_physical_inventory_id
     AND organization_id = p_organization_id
     AND serial_num = p_serial_number
     AND inventory_item_id = p_inventory_item_id
     AND tag_quantity IS NOT NULL
     AND tag_quantity <> 0
     AND void_flag = 2
     AND adjustment_id IN
     (SELECT adjustment_id
      FROM mtl_physical_adjustments
      WHERE physical_inventory_id = p_physical_inventory_id
      AND organization_id = p_organization_id
      AND approval_status IS NULL);

Line: 126

      SELECT all_subinventories_flag
        INTO l_all_sub_flag
        FROM mtl_physical_inventories
        WHERE physical_inventory_id = p_physical_inventory_id
        AND organization_id = p_organization_id;

Line: 141

         SELECT NVL(current_subinventory_code, '@@@@@')
           INTO l_serial_sub
           FROM mtl_serial_numbers
           WHERE inventory_item_id = p_inventory_item_id
           AND serial_number = p_serial_number
           AND current_organization_id = p_organization_id;

Line: 152

         SELECT COUNT(*)
           INTO x_serial_in_scope
           FROM mtl_physical_subinventories
           WHERE organization_id = p_organization_id
           AND physical_inventory_id = p_physical_inventory_id
           AND subinventory = l_serial_sub;

Line: 191

   SELECT *
     FROM mtl_physical_inventory_tags
     WHERE physical_inventory_id = p_physical_inventory_id
     AND organization_id = p_organization_id
     AND subinventory = p_subinventory
     AND NVL(locator_id, -99999) = NVL(p_locator_id, -99999)
     AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
     AND inventory_item_id = p_inventory_item_id
     AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
     AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
     AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
     -- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
     AND void_flag = 2
     AND adjustment_id IN
     (SELECT adjustment_id
      FROM mtl_physical_adjustments
      WHERE physical_inventory_id = p_physical_inventory_id
      AND organization_id = p_organization_id
      AND approval_status IS NULL);

Line: 211

   SELECT *
     FROM mtl_physical_inventory_tags
     WHERE physical_inventory_id = p_physical_inventory_id
     AND organization_id = p_organization_id
     AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999)
     AND inventory_item_id = p_inventory_item_id
     AND NVL(revision, '@@@@@') = NVL(p_revision, '@@@@@')
     AND NVL(lot_number, '@@@@@') = NVL(p_lot_number, '@@@@@')
     AND NVL(serial_num, '@@@@@') = NVL(l_current_serial, '@@@@@')
     -- AND NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999)
     AND void_flag = 2
     AND adjustment_id IN
     (SELECT adjustment_id
      FROM mtl_physical_adjustments
      WHERE physical_inventory_id = p_physical_inventory_id
      AND organization_id = p_organization_id
      AND approval_status IS NULL);

Line: 334

            update_row( p_tag_id                  =>  tag_record.tag_id,
                        p_physical_inventory_id   =>  p_physical_inventory_id,
                        p_organization_id         =>  p_organization_id,
                        p_subinventory            =>  p_subinventory,
                        p_locator_id              =>  p_locator_id,
                        p_parent_lpn_id           =>  p_parent_lpn_id,
                        p_inventory_item_id       =>  p_inventory_item_id,
                        p_revision                =>  p_revision,
                        p_lot_number              =>  p_lot_number,
                        p_serial_number           =>  l_current_serial,
                        p_tag_quantity            =>  p_tag_quantity,
                        p_tag_uom                 =>  p_tag_uom,
                        p_user_id                 =>  p_user_id,
                        p_cost_group_id           =>  tag_record.cost_group_id,
                        p_adjustment_id           =>  l_adjustment_id
                        );

Line: 350

            update_adjustment
              (p_adjustment_id           =>  l_adjustment_id,
               p_physical_inventory_id   =>  p_physical_inventory_id,
               p_organization_id         =>  p_organization_id,
               p_user_id                 =>  p_user_id
               );

Line: 360

               print_debug('Dynamic serial tag entry to be inserted');

Line: 395

                  update_row( p_tag_id                  =>  tag_record.tag_id,
                              p_physical_inventory_id   =>  p_physical_inventory_id,
                              p_organization_id         =>  p_organization_id,
                              p_subinventory            =>  tag_record.subinventory,
                              p_locator_id              =>  tag_record.locator_id,
                              p_parent_lpn_id           =>  p_parent_lpn_id,
                              p_inventory_item_id       =>  p_inventory_item_id,
                              p_revision                =>  p_revision,
                              p_lot_number              =>  p_lot_number,
                              p_serial_number           =>  l_current_serial,
                              p_tag_quantity            =>  0,
                              p_tag_uom                 =>  p_tag_uom,
                              p_user_id                 =>  p_user_id,
                              p_cost_group_id           =>  tag_record.cost_group_id,
                              p_adjustment_id           =>  l_adjustment_id
                              );

Line: 411

                  update_adjustment
                    (p_adjustment_id           =>  l_adjustment_id,
                     p_physical_inventory_id   =>  p_physical_inventory_id,
                     p_organization_id         =>  p_organization_id,
                     p_user_id                 =>  p_user_id
                     );

Line: 469

               insert_row( p_physical_inventory_id   =>  p_physical_inventory_id,
                           p_organization_id         =>  p_organization_id,
                           p_subinventory            =>  p_subinventory,
                           p_locator_id              =>  p_locator_id,
                           p_parent_lpn_id           =>  p_parent_lpn_id,
                           p_inventory_item_id       =>  p_inventory_item_id,
                           p_revision                =>  p_revision,
                           p_lot_number              =>  p_lot_number,
                           p_serial_number           =>  l_current_serial,
                           p_tag_quantity            =>  p_tag_quantity,
                           p_tag_uom                 =>  p_tag_uom,
                           p_user_id                 =>  p_user_id,
                           p_cost_group_id           =>  l_cost_group_id,
                           p_adjustment_id           =>  l_adjustment_id
                           );

Line: 484

               update_adjustment
                 (p_adjustment_id           =>  l_adjustment_id,
                  p_physical_inventory_id   =>  p_physical_inventory_id,
                  p_organization_id         =>  p_organization_id,
                  p_user_id                 =>  p_user_id
                  );

Line: 537

         update_row( p_tag_id                  =>  tag_record.tag_id,
                     p_physical_inventory_id   =>  p_physical_inventory_id,
                     p_organization_id         =>  p_organization_id,
                     p_subinventory            =>  p_subinventory,
                     p_locator_id              =>  p_locator_id,
                     p_parent_lpn_id           =>  p_parent_lpn_id,
                     p_inventory_item_id       =>  p_inventory_item_id,
                     p_revision                =>  p_revision,
                     p_lot_number              =>  p_lot_number,
                     p_serial_number           =>  NULL,
                     p_tag_quantity            =>  p_tag_quantity,
                     p_tag_uom                 =>  p_tag_uom,
                     p_user_id                 =>  p_user_id,
                     p_cost_group_id           =>  tag_record.cost_group_id,
                     p_adjustment_id           =>  l_adjustment_id
                    ,p_tag_sec_quantity        =>  p_tag_sec_quantity    --INVCONV, NSRIVAST
                     );

Line: 554

         update_adjustment
           (p_adjustment_id           =>  l_adjustment_id,
            p_physical_inventory_id   =>  p_physical_inventory_id,
            p_organization_id         =>  p_organization_id,
            p_user_id                 =>  p_user_id
            );

Line: 563

            print_debug('Dynamic non-serial tag entry to be inserted');

Line: 615

            insert_row( p_physical_inventory_id   =>  p_physical_inventory_id,
                        p_organization_id         =>  p_organization_id,
                        p_subinventory            =>  p_subinventory,
                        p_locator_id              =>  p_locator_id,
                        p_parent_lpn_id           =>  p_parent_lpn_id,
                        p_inventory_item_id       =>  p_inventory_item_id,
                        p_revision                =>  p_revision,
                        p_lot_number              =>  p_lot_number,
                        p_serial_number           =>  NULL,
                        p_tag_quantity            =>  p_tag_quantity,
                        p_tag_uom                 =>  p_tag_uom,
                        p_user_id                 =>  p_user_id,
                        p_cost_group_id           =>  l_cost_group_id,
                        p_adjustment_id           =>  l_adjustment_id
                        --INVCONV, NSRIVAST, START
                        ,p_tag_sec_quantity       =>    p_tag_sec_quantity
                        ,p_tag_sec_uom            =>    p_tag_sec_uom
                        --INVCONV, NSRIVAST, END

                        );

Line: 635

            update_adjustment
              (p_adjustment_id           =>  l_adjustment_id,
               p_physical_inventory_id   =>  p_physical_inventory_id,
               p_organization_id         =>  p_organization_id,
               p_user_id                 =>  p_user_id
               );

Line: 655

PROCEDURE insert_row
  (p_physical_inventory_id   IN    NUMBER,
   p_organization_id         IN    NUMBER,
   p_subinventory            IN    VARCHAR2,
   p_locator_id              IN    NUMBER,
   p_parent_lpn_id           IN    NUMBER,
   p_inventory_item_id       IN    NUMBER,
   p_revision                IN    VARCHAR2,
   p_lot_number              IN    VARCHAR2,
   p_serial_number           IN    VARCHAR2,
   p_tag_quantity            IN    NUMBER,
   p_tag_uom                 IN    VARCHAR2,
   p_user_id                 IN    NUMBER,
   p_cost_group_id           IN    NUMBER,
   p_adjustment_id           IN    NUMBER
   --INVCONV, NSRIVAST, START
   ,p_tag_sec_quantity       IN    NUMBER   := NULL
   ,p_tag_sec_uom            IN    VARCHAR2 := NULL
   --INVCONV, NSRIVAST, END
   )
IS
l_tag_id                        NUMBER;

Line: 682

   SELECT next_tag_number
     FROM mtl_physical_inventories
     WHERE physical_inventory_id = p_physical_inventory_id
     AND organization_id = p_organization_id;

Line: 704

      print_debug('***insert_row***');

Line: 707

   SELECT mtl_physical_inventory_tags_s.nextval
     INTO l_tag_id
     FROM dual;

Line: 720

      SELECT MAX(tag_number)
        INTO l_tag_number
        FROM mtl_physical_inventory_tags
        WHERE physical_inventory_id = p_physical_inventory_id
        AND organization_id = p_organization_id;

Line: 757

   UPDATE MTL_PHYSICAL_INVENTORIES
     SET next_tag_number = l_next_tag_number
     WHERE physical_inventory_id = p_physical_inventory_id
     AND organization_id = p_organization_id;

Line: 762

      print_debug('Update physical inventory with next tag number: ' || l_next_tag_number);

Line: 766

   SELECT primary_uom_code
     INTO l_item_standard_uom
     FROM mtl_system_items
     WHERE inventory_item_id = p_inventory_item_id
     AND organization_id = p_organization_id;

Line: 801

     SELECT  outermost_lpn_id
       INTO  l_outermost_lpn_id
       FROM  WMS_LICENSE_PLATE_NUMBERS
       WHERE lpn_id = p_parent_lpn_id;

Line: 823

     SELECT fus.employee_id
     INTO   l_employee_id
     FROM   mtl_employees_current_view mec, fnd_user fus
     WHERE  fus.user_id = p_user_id
     AND    mec.employee_id = fus.employee_id
     AND    mec.organization_id = p_organization_id;

Line: 840

      print_debug('Inserting the new record here');

Line: 842

   INSERT INTO MTL_PHYSICAL_INVENTORY_TAGS
     (tag_id,
      physical_inventory_id,
      organization_id,
      last_update_date,
      last_updated_by,
      creation_date,
      created_by,
      last_update_login,
      void_flag,
      tag_number,
      adjustment_id,
      inventory_item_id,
      tag_quantity,
      tag_uom,
      tag_quantity_at_standard_uom,
      standard_uom,
      subinventory,
      locator_id,
      lot_number,
      revision,
      serial_num,
      counted_by_employee_id,
      parent_lpn_id,
      outermost_lpn_id,
      cost_group_id
      --INVCONV, NSRIVAST, START
      ,tag_secondary_uom
      ,tag_secondary_quantity
      --INVCONV, NSRIVAST, END
      ) VALUES
     (l_tag_id,
      p_physical_inventory_id,
      p_organization_id,
      SYSDATE,
      p_user_id,
      SYSDATE,
      p_user_id,
      p_user_id,
      2,
      l_tag_number,
      p_adjustment_id,
      p_inventory_item_id,
      p_tag_quantity,
      p_tag_uom,
      l_tag_qty_at_standard_uom,
      l_item_standard_uom,
      p_subinventory,
      p_locator_id,
      p_lot_number,
      p_revision,
      p_serial_number,
      l_employee_id,
      p_parent_lpn_id,
      l_outermost_lpn_id,
      p_cost_group_id
      --INVCONV, NSRIVAST, START
     ,p_tag_sec_uom
     ,p_tag_sec_quantity
      --INVCONV, NSRIVAST, END
      );

Line: 911

END insert_row;

Line: 914

PROCEDURE update_row
  (p_tag_id                  IN    NUMBER,
   p_physical_inventory_id   IN    NUMBER,
   p_organization_id         IN    NUMBER,
   p_subinventory            IN    VARCHAR2,
   p_locator_id              IN    NUMBER,
   p_parent_lpn_id           IN    NUMBER,
   p_inventory_item_id       IN    NUMBER,
   p_revision                IN    VARCHAR2,
   p_lot_number              IN    VARCHAR2,
   p_serial_number           IN    VARCHAR2,
   p_tag_quantity            IN    NUMBER,
   p_tag_uom                 IN    VARCHAR2,
   p_user_id                 IN    NUMBER,
   p_cost_group_id           IN    NUMBER,
   p_adjustment_id           IN    NUMBER
   ,p_tag_sec_quantity       IN    NUMBER   := NULL     --INVCONV, NSRIVAST, START
   )
IS
l_tag_qty_at_standard_uom       NUMBER;

Line: 945

      print_debug('***update_row***');

Line: 948

   SELECT primary_uom_code
     INTO l_item_standard_uom
     FROM mtl_system_items
     WHERE inventory_item_id = p_inventory_item_id
     AND organization_id = p_organization_id;

Line: 983

     SELECT  outermost_lpn_id
       INTO  l_outermost_lpn_id
       FROM  WMS_LICENSE_PLATE_NUMBERS
       WHERE lpn_id = p_parent_lpn_id;

Line: 1005

   SELECT fus.employee_id
   INTO   l_employee_id
   FROM   mtl_employees_current_view mec, fnd_user fus
   WHERE  fus.user_id = p_user_id
   AND    mec.employee_id = fus.employee_id
   AND    mec.organization_id = p_organization_id;

Line: 1024

   UPDATE MTL_PHYSICAL_INVENTORY_TAGS
     SET
     last_update_date                  =     SYSDATE,
     last_updated_by                   =     p_user_id,
     last_update_login                 =     p_user_id,
     adjustment_id                     =     p_adjustment_id,
     inventory_item_id                 =     p_inventory_item_id,
     tag_quantity                      =     p_tag_quantity,
     tag_uom                           =     p_tag_uom,
     tag_quantity_at_standard_uom      =     l_tag_qty_at_standard_uom,
     standard_uom                      =     l_item_standard_uom,
     subinventory                      =     p_subinventory,
     locator_id                        =     p_locator_id,
     lot_number                        =     p_lot_number,
     revision                          =     p_revision,
     serial_num                        =     p_serial_number,
     counted_by_employee_id            =     l_employee_id,
     parent_lpn_id                     =     p_parent_lpn_id,
     outermost_lpn_id                  =     l_outermost_lpn_id,
     cost_group_id                     =     p_cost_group_id
     ,tag_secondary_quantity           =     p_tag_sec_quantity  --INVCONV, NSRIVAST, START
     WHERE tag_id = p_tag_id;

Line: 1051

END update_row;

Line: 1054

PROCEDURE update_adjustment
  (p_adjustment_id           IN   NUMBER,
   p_physical_inventory_id   IN   NUMBER,
   p_organization_id         IN   NUMBER,
   p_user_id                 IN   NUMBER
   )
IS
l_adj_count_quantity    NUMBER;

Line: 1080

      print_debug('***update_adjustment***');

Line: 1082

   SELECT NVL(SUM(tag_quantity_at_standard_uom),0)
     INTO l_adj_count_quantity
     FROM mtl_physical_inventory_tags
     WHERE adjustment_id = p_adjustment_id
     AND organization_id = p_organization_id
     AND physical_inventory_id = p_physical_inventory_id
     AND void_flag = 2;

Line: 1093

   UPDATE mtl_physical_adjustments
     SET last_update_date = SYSDATE,
     last_updated_by = NVL(p_user_id, -1),
     count_quantity = l_adj_count_quantity,
     adjustment_quantity = NVL(l_adj_count_quantity, NVL(system_quantity,0))
     - NVL(system_quantity,0),
     approval_status = NULL,
     approved_by_employee_id = NULL
     WHERE adjustment_id = p_adjustment_id
     AND physical_inventory_id = p_physical_inventory_id
     AND organization_id = p_organization_id;

Line: 1109

   SELECT inventory_item_id, lot_number, serial_number, parent_lpn_id,
     subinventory_name, locator_id, NVL(adjustment_quantity, 0)
     INTO l_inventory_item_id, l_lot_number, l_serial_number,
     l_lpn_id, l_subinventory, l_locator_id, l_adjustment_quantity
     FROM mtl_physical_adjustments
     WHERE adjustment_id = p_adjustment_id
     AND physical_inventory_id = p_physical_inventory_id
     AND organization_id = p_organization_id;

Line: 1122

   SELECT primary_uom_code
     INTO l_standard_uom_code
     FROM mtl_system_items
     WHERE inventory_item_id = l_inventory_item_id
     AND organization_id = p_organization_id;

Line: 1196

END update_adjustment;

Line: 1237

   SELECT stock_locator_control_code
     INTO l_org_locator_type
     FROM mtl_parameters
     WHERE organization_id = p_organization_id;

Line: 1243

   SELECT locator_type
     INTO l_sub_locator_type
     FROM mtl_secondary_inventories
     WHERE secondary_inventory_name = p_subinventory
     AND organization_id = p_organization_id;

Line: 1251

   SELECT revision_qty_control_code, location_control_code,
     lot_control_code, serial_number_control_code
     INTO l_rev_code, l_location_control_code,
     l_lot_control_code, l_serial_control_code
     FROM mtl_system_items
     WHERE inventory_item_id = p_inventory_item_id
     AND organization_id = p_organization_id;

Line: 1263

   SELECT MIN(ADJUSTMENT_ID)
     INTO l_adj_id
     FROM MTL_PHYSICAL_ADJUSTMENTS
     WHERE ORGANIZATION_ID = p_organization_id
     AND PHYSICAL_INVENTORY_ID = p_physical_inventory_id
     AND INVENTORY_ITEM_ID = p_inventory_item_id
     AND SUBINVENTORY_NAME = p_subinventory
     AND ( NVL(REVISION,'@@@@@') = NVL(p_revision,'@@@@@')
           OR l_rev_code = 1 )
     AND NVL(parent_lpn_id, -99999) = NVL(p_parent_lpn_id, -99999) --Bug 6929248 Posted adjustments should not be allowed to enter tags
     AND (approval_status=3 OR NVL(cost_group_id, -99999) = NVL(p_cost_group_id, -99999))
     AND (NVL(LOCATOR_ID, -99999) = NVL(p_locator_id, -99999)
          OR l_org_locator_type = 1
          OR (l_org_locator_type = 4
              AND (l_sub_locator_type = 1
                   OR (l_sub_locator_type = 5
                       AND l_location_control_code = 1)))
          OR (l_location_control_code = 5
              AND l_location_control_code = 1))
     AND ( NVL(LOT_NUMBER,'@@@@@') = NVL(p_lot_number,'@@@@@')
           OR l_lot_control_code = 1 )
     AND ( NVL(SERIAL_NUMBER,'@@@@@') = NVL(p_serial_number,'@@@@@')
           OR l_serial_control_code = 1 )
     GROUP BY ORGANIZATION_ID,
     PHYSICAL_INVENTORY_ID,
     INVENTORY_ITEM_ID,
     SUBINVENTORY_NAME,
     REVISION,
     LOCATOR_ID,
     PARENT_LPN_ID,
     COST_GROUP_ID,
     LOT_NUMBER,
     SERIAL_NUMBER;

Line: 1300

       select approval_status
       into l_approval_status
       from mtl_physical_adjustments
       where adjustment_id = l_adj_id
       and physical_inventory_id = p_physical_inventory_id;

Line: 1322

         print_debug('No adjustment record found so insert a new one');

Line: 1346

SELECT mtl_physical_adjustments_s.NEXTVAL
  INTO l_adj_id
  FROM dual;

Line: 1368

     SELECT  outermost_lpn_id
       INTO  l_outermost_lpn_id
       FROM  WMS_LICENSE_PLATE_NUMBERS
       WHERE lpn_id = p_parent_lpn_id;

Line: 1388

   print_debug('Inserting the new physical adjustment record');

Line: 1390

INSERT INTO mtl_physical_adjustments
  (     adjustment_id,
        organization_id,
        physical_inventory_id,
        inventory_item_id,
        subinventory_name,
        system_quantity,
        last_update_date,
        last_updated_by,
        creation_date,
        created_by,
        last_update_login,
        count_quantity,
        adjustment_quantity,
        revision,
        locator_id,
        parent_lpn_id,
        outermost_lpn_id,
        cost_group_id,
        lot_number,
        serial_number,
        actual_cost )
  VALUES ( l_adj_id,
           p_organization_id,
           p_physical_inventory_id,
           p_inventory_item_id,
           p_subinventory,
           0,
           SYSDATE,
           p_user_id,
           SYSDATE,
           p_user_id,
           p_user_id,
           0,
           0,
           p_revision,
           p_locator_id,
           p_parent_lpn_id,
           l_outermost_lpn_id,
           p_cost_group_id,
           p_lot_number,
           p_serial_number,
           l_actual_cost);

Line: 1453

   SELECT *
     FROM WMS_LICENSE_PLATE_NUMBERS
     START WITH lpn_id = p_parent_lpn_id
     CONNECT BY parent_lpn_id = PRIOR lpn_id;

Line: 1458

   SELECT *
     FROM WMS_LPN_CONTENTS
     WHERE parent_lpn_id = l_current_lpn
     AND NVL(serial_summary_entry, 2) = 2;

Line: 1463

   SELECT *
     FROM MTL_SERIAL_NUMBERS
     WHERE lpn_id = l_current_lpn;

Line: 1480

         SELECT primary_uom_code
           INTO l_temp_uom_code
           FROM mtl_system_items
           WHERE inventory_item_id = v_lpn_id.inventory_item_id
           AND organization_id = v_lpn_id.organization_id;

Line: 1537

         SELECT primary_uom_code
           INTO l_temp_uom_code
           FROM mtl_system_items
           WHERE inventory_item_id = v_lpn_serial_content.inventory_item_id
           AND organization_id = v_lpn_serial_content.current_organization_id;

Line: 1601

         UPDATE mtl_serial_numbers
         SET    group_mark_id = -1
         WHERE  inventory_item_id = p_item_id
         AND    serial_number in
             (SELECT DISTINCT serial_num
              FROM   mtl_physical_inventory_tags
              WHERE  organization_id      = p_organization_id
              AND   physical_inventory_id = p_physical_inventory_id
              AND   inventory_item_id     = p_item_id
              AND   serial_num is not null
              )
         AND nvl(group_mark_id,-1) <> -1;

Line: 1615

             print_debug('Updated ' || SQL%ROWCOUNT || ' Records in mtl_serial_numbers for the inventory_item_id ' || p_item_id);

Line: 1633

  SELECT	NVL(SERIAL_NUMBER_TYPE, 0)
  INTO		x_serial_number_type
  FROM		MTL_PARAMETERS
  WHERE		ORGANIZATION_ID = p_organization_id;

Line: 1673

    SELECT      1,MP.ORGANIZATION_CODE,ML.MEANING
    INTO        l_valid_serial,x_organization_code,x_current_status
    FROM        MTL_SERIAL_NUMBERS MSN , MTL_PARAMETERS MP, MFG_LOOKUPS ML
    WHERE       SERIAL_NUMBER like p_serial_num
    AND         MSN.INVENTORY_ITEM_ID = p_inventory_item_id
    AND         MSN.CURRENT_ORGANIZATION_ID = p_organization_id
    AND         MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
    AND         MSN.CURRENT_STATUS = ML.LOOKUP_CODE
    AND         MSN.CURRENT_SUBINVENTORY_CODE like p_subinventory_code
    AND         NVL(MSN.CURRENT_LOCATOR_ID,-9999) = NVL(p_locator_id, -9999)
    AND         ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
    AND         CURRENT_STATUS = 3;

Line: 1709

      SELECT    MP.ORGANIZATION_CODE,ML.MEANING
      INTO      x_organization_code,x_current_status
      FROM      MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
      WHERE     SERIAL_NUMBER like p_serial_num
      AND       MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
      AND       MSN.CURRENT_STATUS = ML.LOOKUP_CODE
      AND       ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
      AND       CURRENT_STATUS NOT IN (1,4);

Line: 1722

       SELECT    MP.ORGANIZATION_CODE,ML.MEANING
       INTO      x_organization_code,x_current_status
       FROM      MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
       WHERE     MSN.SERIAL_NUMBER like p_serial_num
       AND       MSN.INVENTORY_ITEM_ID = p_inventory_item_id
       AND       MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
       AND       MSN.CURRENT_STATUS = ML.LOOKUP_CODE
       AND       ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
       AND       CURRENT_STATUS NOT IN (1,4);

Line: 1741

        SELECT  MP.ORGANIZATION_CODE,ML.MEANING
        INTO    x_organization_code,x_current_status
        FROM    MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
        WHERE   MSN.SERIAL_NUMBER like p_serial_num
        AND     MSN.INVENTORY_ITEM_ID = p_inventory_item_id
        AND     MSN.CURRENT_ORGANIZATION_ID = MP.ORGANIZATION_ID
        AND     MSN.CURRENT_STATUS = ML.LOOKUP_CODE
        AND     ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
        AND     CURRENT_STATUS NOT IN (1,4);

Line: 1761

      SELECT    MP.ORGANIZATION_CODE,ML.MEANING
      INTO      x_organization_code,x_current_status
      FROM      MTL_SERIAL_NUMBERS MSN, MTL_PARAMETERS MP,MFG_LOOKUPS ML
      WHERE     MSN.SERIAL_NUMBER like p_serial_num
      AND       MSN.CURRENT_ORGANIZATION_ID = p_organization_id
      AND       MSN.CURRENT_ORGANIZATION_ID = MP. ORGANIZATION_ID
      AND       MSN.CURRENT_STATUS = ML.LOOKUP_CODE
      AND       ML.LOOKUP_TYPE like 'SERIAL_NUM_STATUS'
      AND       CURRENT_STATUS NOT IN (1,4);