APPS.INV_VALIDATE SQL Statements

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

Line: 21

    FND_API.g_attr_tbl.DELETE;

Line: 44

    FND_API.g_attr_tbl(I).name     := 'last_updated_by';

Line: 46

    FND_API.g_attr_tbl(I).name     := 'last_update_date';

Line: 48

    FND_API.g_attr_tbl(I).name     := 'last_update_login';

Line: 56

    FND_API.g_attr_tbl(I).name     := 'program_update_date';

Line: 156

function check_creation_updation(p_created_updated_by in number,
             p_is_creation in number)
  RETURN NUMBER
  is
     l_dummy varchar2(10);

Line: 164

       IF p_created_updated_by IS NULL OR
    p_created_updated_by = FND_API.G_MISS_NUM
    then
     return p_is_creation;

Line: 170

       SELECT  'VALID'
    INTO    l_dummy
    FROM    FND_USER
    WHERE   USER_ID = p_created_updated_by;

Line: 205

        ,   'Last_Updated_By'
        );

Line: 280

   SELECT set_of_books_id
     INTO l_sob_id
     FROM org_organization_definitions
     WHERE organization_id = from_org;

Line: 285

   SELECT set_of_books_id
     INTO l_xfr_sob_id
     FROM org_organization_definitions
     WHERE organization_id = to_org;

Line: 290

   SELECT currency_code
     INTO l_currency_code
     FROM gl_sets_of_books
     WHERE set_of_books_id = l_sob_id;

Line: 295

   SELECT currency_code
     INTO l_xfr_currency_code
     FROM gl_sets_of_books
     WHERE set_of_books_id = l_xfr_sob_id;

Line: 357

      SELECT last_name,full_name
   INTO p_last_name,p_full_name
   FROM mtl_employees_current_view
   WHERE employee_id = p_employee_id
   AND organization_id = p_org.organization_id;

Line: 364

        SELECT employee_id,last_name,full_name
      INTO p_employee_id,p_last_name,p_full_name
      FROM mtl_employees_current_view
      WHERE organization_id = p_org.organization_id
      -- Bug 4951746, following where clause voided the index use
      -- therefore cauased performance issue
      -- changed to avoid the NVL and DECODE
      --       AND (NVL(last_name,'@@@@') = DECODE(last_name,NULL,'@@@@',p_last_name)
      --        OR NVL(full_name,'@@@@') = DECODE(full_name,NULL,'@@@@',p_full_name));

Line: 454

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_ITEM_SUB_TRK_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND INVENTORY_ITEM_ID = p_item.inventory_item_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 461

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_SUBINVENTORIES_TRK_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 470

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_ITEM_SUB_TRK_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND INVENTORY_ITEM_ID = p_item.inventory_item_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 477

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_SUBINVENTORIES_TRK_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 489

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_ITEM_SUB_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND INVENTORY_ITEM_ID = p_item.inventory_item_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 496

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_SUBINVENTORIES_TRK_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 505

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_ITEM_SUB_TRK_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND INVENTORY_ITEM_ID = p_item.inventory_item_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 512

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_SUBINVENTORIES_TRK_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 520

    SELECT *
      INTO p_sub
      FROM MTL_SECONDARY_INVENTORIES
      WHERE ORGANIZATION_ID = p_org.organization_id
        AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 621

FUNCTION Last_Updated_By ( p_last_updated_by IN NUMBER )
RETURN NUMBER
IS
l_dummy                            VARCHAR2(10);

Line: 627

   return check_creation_updation(p_last_updated_by, F);

Line: 629

END Last_Updated_By;

Line: 634

FUNCTION Last_Update_Date ( p_last_update_date IN DATE )
RETURN NUMBER
IS
BEGIN

      return check_date(p_last_update_date, 'DATE');

Line: 641

END Last_Update_Date;

Line: 646

FUNCTION Last_Update_Login ( p_last_update_login IN NUMBER )
RETURN NUMBER
IS
l_dummy                       VARCHAR2(10);

Line: 652

    IF p_last_update_login IS NULL OR
        p_last_update_login = FND_API.G_MISS_NUM
    THEN
        RETURN T;

Line: 668

            FND_MESSAGE.SET_TOKEN('ATTRIBUTE','last_update_login');

Line: 681

            ,   'Last_Update_Login'
            );

Line: 687

END Last_Update_Login;

Line: 705

       SELECT  *
    INTO    p_org
    FROM    MTL_PARAMETERS MP
    WHERE   ORGANIZATION_ID = p_org.organization_id;

Line: 711

       SELECT *
    INTO p_org
    FROM MTL_PARAMETERS MP
    WHERE MP.ORGANIZATION_CODE = p_org.organization_code
    AND MP.ORGANIZATION_ID = p_org.ORGANIZATION_ID;

Line: 771

FUNCTION Program_Update_Date ( p_program_update_date IN DATE )
RETURN NUMBER
IS
l_dummy                       VARCHAR2(10);

Line: 777

END Program_Update_Date;

Line: 794

    SELECT  'VALID'
    INTO     l_dummy
    FROM     GL_CODE_COMBINATIONS
    WHERE    CODE_COMBINATION_ID = p_to_account_id;

Line: 856

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_ITEM_SUB_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND INVENTORY_ITEM_ID = p_item.inventory_item_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 863

            SELECT 'VALID'
            INTO   l_dummy
            FROM MTL_SUBINVENTORIES_VAL_V
            WHERE ORGANIZATION_ID = p_org.organization_id
              AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 877

                   SELECT 'VALID'
                   INTO   l_dummy
                   FROM MTL_ITEM_SUB_VAL_V
                   WHERE ORGANIZATION_ID = p_org.organization_id
                     AND INVENTORY_ITEM_ID = p_item.inventory_item_id
                     AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 885

                   SELECT 'VALID'
                   INTO   l_dummy
                   FROM MTL_ITEM_SUB_EXP_VAL_V
                   WHERE ORGANIZATION_ID = p_org.organization_id
                     AND INVENTORY_ITEM_ID = p_item.inventory_item_id
                     AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 899

               SELECT 'VALID'
               INTO   l_dummy
               FROM MTL_ITEM_SUB_VAL_V
               WHERE ORGANIZATION_ID = p_org.organization_id
                 AND INVENTORY_ITEM_ID = p_item.inventory_item_id
                 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 911

                  SELECT 'VALID'
                  INTO   l_dummy
                  FROM MTL_SUBINVENTORIES_VAL_V
                  WHERE ORGANIZATION_ID = p_org.organization_id
                    AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 918

                  SELECT 'VALID'
                  INTO   l_dummy
                  FROM MTL_SUB_EXP_VAL_V
                  WHERE ORGANIZATION_ID = p_org.organization_id
                    AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 931

               SELECT 'VALID'
               INTO   l_dummy
               FROM MTL_SUBINVENTORIES_VAL_V
               WHERE ORGANIZATION_ID = p_org.organization_id
                 AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 941

    SELECT *
      INTO p_sub
      FROM MTL_SECONDARY_INVENTORIES
     WHERE ORGANIZATION_ID = p_org.organization_id
       AND SECONDARY_INVENTORY_NAME = p_sub.secondary_inventory_name;

Line: 1052

    SELECT transaction_action_id,transaction_source_type_id
      INTO x_transaction_action_id,x_transaction_source_type_id
      FROM mtl_transaction_types
      WHERE transaction_type_id = p_transaction_type_id;

Line: 1114

    SELECT *
      INTO x_transaction
      FROM mtl_transaction_types
      WHERE transaction_type_id = x_transaction.transaction_type_id;

Line: 1193

          select *
          INTO   p_locator
          FROM   MTL_ITEM_LOCATIONS
          WHERE ORGANIZATION_ID = p_org.organization_id
            AND INVENTORY_LOCATION_ID = p_locator.inventory_location_id
            AND SUBINVENTORY_CODE = p_sub.secondary_inventory_name
            AND (DISABLE_DATE > SYSDATE OR DISABLE_DATE IS NULL)
            AND INVENTORY_LOCATION_ID IN
                  (SELECT  SECONDARY_LOCATOR
                     FROM  MTL_SECONDARY_LOCATORS
                    WHERE INVENTORY_ITEM_ID = p_item.inventory_item_id
                      AND ORGANIZATION_ID = p_org.organization_id
                      AND NVL(PROJECT_ID,-1) = NVL(p_project_id,-1)
                      AND NVL(TASK_ID,-1) = NVL(p_task_id,-1)
                      AND SUBINVENTORY_CODE = p_sub.secondary_inventory_name);

Line: 1209

         SELECT *
         INTO   p_locator
         FROM   MTL_ITEM_LOCATIONS
         WHERE ORGANIZATION_ID = p_org.organization_id
           AND INVENTORY_LOCATION_ID = p_locator.inventory_location_id
           AND (NVL(SUBINVENTORY_CODE,p_sub.secondary_inventory_name) =
                      p_sub.secondary_inventory_name)
           AND (DISABLE_DATE > SYSDATE OR DISABLE_DATE IS NULL)
           AND NVL(PROJECT_ID,-1) = NVL(p_project_id,-1)
           AND NVL(TASK_ID,-1) = NVL(p_task_id,-1);

Line: 1221

         SELECT *
         INTO   p_locator
         FROM   MTL_ITEM_LOCATIONS
         WHERE  ORGANIZATION_ID = p_org.organization_id
           AND INVENTORY_LOCATION_ID = p_locator.inventory_location_id
           AND (NVL(SUBINVENTORY_CODE,p_sub.secondary_inventory_name) =
                                p_sub.secondary_inventory_name)
           AND (DISABLE_DATE > SYSDATE OR DISABLE_DATE IS NULL)
           AND NVL(PROJECT_ID,-1) = NVL(p_project_id,-1)
           AND NVL(TASK_ID,-1) = NVL(p_task_id,-1);

Line: 1426

    SELECT  *
      INTO    p_item
      FROM    MTL_SYSTEM_ITEMS
      WHERE   ORGANIZATION_ID = p_org.organization_id
      AND   INVENTORY_ITEM_ID = p_item.inventory_item_id;

Line: 1546

       UPDATE mtl_item_locations
         SET subinventory_code = p_sub.secondary_inventory_name
         ,project_id = p_locator.project_id
         ,task_id = p_locator.task_id
         ,physical_location_id = p_locator.physical_location_id
         ,inventory_location_type = p_locator.inventory_location_type
         WHERE organization_id = p_org.organization_id
         AND inventory_location_id = p_locator.inventory_location_id;

Line: 1559

   SELECT *
     INTO p_locator
     FROM mtl_item_locations
     WHERE organization_id = p_org.organization_id
     AND subinventory_code = p_sub.secondary_inventory_name
     AND inventory_location_id = p_locator.inventory_location_id
     AND NVL(disable_date,SYSDATE) >= SYSDATE;

Line: 1611

     SELECT mil.*
       INTO p_locator
       FROM mtl_item_locations mil,mtl_secondary_locators msl
       WHERE mil.organization_id = p_org.organization_id
       AND mil.subinventory_code = p_sub.secondary_inventory_name
       AND mil.inventory_location_id = p_locator.inventory_location_id
       AND NVL(disable_date,SYSDATE) >= SYSDATE
       AND mil.organization_id = msl.organization_id
       AND mil.subinventory_code = msl.subinventory_code
       AND mil.inventory_location_id = msl.secondary_locator
       AND msl.inventory_item_id = p_item.inventory_item_id;

Line: 1739

    SELECT mln.*
      INTO p_lot
      FROM MTL_LOT_NUMBERS MLN
     WHERE MLN.INVENTORY_ITEM_ID = p_item.inventory_item_id
       AND MLN.ORGANIZATION_ID = p_org.organization_id
       AND MLN.LOT_NUMBER = p_lot.lot_number
       AND MLN.LOT_NUMBER IN (SELECT LOT_NUMBER
                                FROM MTL_ONHAND_QUANTITIES_DETAIL MOQ
                               WHERE MOQ.INVENTORY_ITEM_ID = p_item.inventory_item_id
                                 AND MOQ.ORGANIZATION_ID = p_org.organization_id
                                 AND MOQ.LOT_NUMBER = p_lot.lot_number
                                 AND MOQ.SUBINVENTORY_CODE =
                                      NVL(p_from_sub.secondary_inventory_name,'##')
                                 AND NVL(MOQ.REVISION,'##') = NVL(p_revision,'##')
                                 AND NVL(MOQ.LOCATOR_ID,-1) = NVL(p_loc.inventory_location_id,-1)
                                 AND ROWNUM < 2);

Line: 1871

   SELECT  'VALID'
     INTO     l_dummy
     FROM    PJM_PROJECTS_V
     WHERE   PROJECT_ID = p_project_id;

Line: 1941

    SELECT  'VALID'
    INTO    l_dummy
    FROM    MTL_TRANSACTION_REASONS
    WHERE   NVL(DISABLE_DATE,SYSDATE) >= SYSDATE
      AND   REASON_ID = p_reason_id;

Line: 2005

      SELECT  'VALID'
      INTO    l_dummy
      FROM    MTL_KANBAN_CARDS
      WHERE   EXISTS (SELECT 1
                      FROM    MFG_LOOKUPS
                      WHERE   LOOKUP_TYPE = 'MTL_TXN_REQUEST_SOURCE'
                        AND   LOOKUP_CODE = p_reference_type_code)
        AND  KANBAN_CARD_ID = p_reference_id;

Line: 2060

    SELECT  'VALID'
    INTO    l_dummy
    FROM    MFG_LOOKUPS
    WHERE   LOOKUP_TYPE = 'MTL_TXN_REQUEST_SOURCE'
      AND   LOOKUP_CODE = p_reference_type_code;

Line: 2117

    SELECT  'VALID'
    INTO     l_dummy
    FROM MTL_ITEM_REVISIONS
    WHERE ORGANIZATION_ID = p_org.organization_id
      AND INVENTORY_ITEM_ID = p_item.inventory_item_id
      AND REVISION = p_revision;

Line: 2189

      SELECT  *
      INTO  p_serial
      FROM    MTL_SERIAL_NUMBERS
      WHERE INVENTORY_ITEM_ID = p_item.inventory_item_id
      AND CURRENT_ORGANIZATION_ID = p_org.organization_id
      AND SERIAL_NUMBER = p_serial.serial_number
      AND ((NVL(CURRENT_SUBINVENTORY_CODE,'@@@') =
      NVL(p_from_sub.secondary_inventory_name,'@@@')
      AND NVL(CURRENT_LOCATOR_ID,-1)=NVL(p_loc.inventory_location_id,-1)
      AND NVL(LOT_NUMBER,'@@@') = NVL(p_lot.lot_number,'@@@')
      AND NVL(REVISION,'@@@') = NVL(p_revision,'@@@')
      AND CURRENT_STATUS = 3));

Line: 2212

      SELECT  *
   INTO  p_serial
   FROM    MTL_SERIAL_NUMBERS
   WHERE INVENTORY_ITEM_ID = p_item.inventory_item_id
   AND CURRENT_ORGANIZATION_ID = p_org.organization_id
   AND SERIAL_NUMBER = p_serial.serial_number
   AND (CURRENT_STATUS IN (1,6) OR NVL(LOT_NUMBER,'@@@') = NVL(p_lot.lot_number,'@@@'))
   AND (CURRENT_STATUS IN (1,6) OR NVL(REVISION,'@@@') = NVL(p_revision,'@@@'))
   AND CURRENT_STATUS IN (1, 3, 6);

Line: 2471

  x_errored_serials.DELETE;

Line: 2610

     SELECT *
       INTO p_sub
       FROM mtl_secondary_inventories
      WHERE secondary_inventory_name =  p_sub.secondary_inventory_name
        AND organization_id = p_org.organization_id
        AND NVL(disable_date,sysdate+1) > sysdate;

Line: 2658

     SELECT msi.*
       INTO p_sub
       FROM mtl_secondary_inventories msi,mtl_item_sub_inventories misi
       WHERE msi.secondary_inventory_name =  p_sub.secondary_inventory_name
       AND msi.organization_id = p_org.organization_id
       AND NVL(MSI.DISABLE_DATE,SYSDATE) >= SYSDATE
       AND msi.organization_id = misi.organization_id
       AND msi.secondary_inventorY_name = misi.secondary_inventory
       AND misi.inventory_item_id = p_item.inventory_item_id;

Line: 2709

    SELECT  'VALID'
    INTO    l_dummy
    FROM    PJM_TASKS_V
    WHERE   PROJECT_ID = p_project_id
      AND   TASK_ID = p_task_id;

Line: 2797

    SELECT 'valid'
      INTO l_dummy
      FROM HR_LOCATIONS
     WHERE LOCATION_ID = p_hr_location;

Line: 2805

          SELECT 'valid'
            INTO l_dummy
            FROM HZ_LOCATIONS
           WHERE LOCATION_ID = p_hr_location;

Line: 2862

    /* SELECT  'VALID'
    INTO    l_dummy
    FROM    MTL_ITEM_UOMS_VIEW
    WHERE   ORGANIZATION_ID = p_org.organization_id
      AND   INVENTORY_ITEM_ID = p_item.inventory_item_id
      AND   UOM_CODE = p_uom_code;

Line: 2944

   select 'VALID'
   into l_dummy
   from cst_cost_groups
   where cost_group_id = p_cost_group_id;

Line: 2969

   select 'valid'
   into l_dummy
   from wms_license_plate_numbers
     where lpn_id = p_lpn_id;

Line: 3020

    SELECT mln.*
      INTO p_lot
      FROM MTL_LOT_NUMBERS MLN
     WHERE MLN.INVENTORY_ITEM_ID = p_item.inventory_item_id
       AND MLN.ORGANIZATION_ID = p_org.organization_id
      AND MLN.LOT_NUMBER = p_lot.lot_number;

Line: 3097

  SELECT 1
  FROM DUAL
  WHERE EXISTS(
    SELECT 1
    FROM mtl_material_transactions_temp mmtt
    WHERE mmtt.transaction_type_id = p_transaction_type_id
    AND NVL(mmtt.transaction_status, 1) IN (1, 3));

Line: 3130

  SELECT NVL(location_required_flag, 'N') location_required_flag
  FROM mtl_transaction_types
  WHERE transaction_type_id = NVL(p_transaction_type_id, -1)
    AND user_defined_flag = 'Y';