APPS.INV_MGD_POSITIONS_PROC SQL Statements

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

Line: 68

  SELECT COUNT(*)
    INTO l_count
    FROM mtl_mgd_inventory_positions
    WHERE data_set_name = p_data_set_name;

Line: 118

  SELECT
    name
  INTO
    x_context_rec.hierarchy_name
  FROM per_organization_structures
  WHERE organization_structure_id = p_hierarchy_id;

Line: 126

  SELECT
    org_structure_version_id
  INTO
    x_context_rec.hierarchy_version_id
  FROM per_org_structure_versions
  WHERE organization_structure_id = p_hierarchy_id
    AND SYSDATE BETWEEN date_from AND NVL(date_to, SYSDATE);

Line: 135

  SELECT
    organization_id
  INTO
    x_context_rec.parent_organization_id
  FROM mtl_parameters
  WHERE organization_code = p_parent_org_code;

Line: 194

    MTL_MGD_INV_POSITIONS_PKG.Insert_Row
    ( p_data_set_name             => p_data_set_name
    , p_bucket_name               => 'LOCK'
    , p_organization_code         => 'LCK'
    , p_inventory_item_code       => 'LOCK'
    , p_hierarchy_id              => -1
    , p_hierarchy_name            => -1
    , p_parent_organization_code  => 'LCK'
    , p_parent_organization_id    => -1
    , p_bucket_size_code          => 'LOCK'
    , p_bucket_start_date         => SYSDATE
    , p_bucket_end_date           => SYSDATE
    , p_inventory_item_id         => -1
    , p_organization_id           => -1
    , p_hierarchy_delta_qty       => 0
    , p_hierarchy_end_on_hand_qty => 0
    , p_org_received_qty          => 0
    , p_org_issued_qty            => 0
    , p_org_delta_qty             => 0
    , p_org_end_on_hand_qty       => 0
    );

Line: 251

  DELETE FROM mtl_mgd_inventory_positions
    WHERE data_set_name     = p_data_set_name
      AND organization_id   = -1
      AND bucket_name       = 'LOCK'
      AND inventory_item_id = -1;

Line: 284

  SELECT master_organization_id
    INTO l_master_org_id
    FROM mtl_parameters
    WHERE organization_id = p_hierarchy_level;

Line: 321

  SELECT organization_id_child
    FROM per_org_structure_elements
    WHERE org_structure_version_id   = p_hierarchy_version_id
      AND organization_id_parent     = p_organization_id;

Line: 451

  SELECT NVL(SUM(primary_transaction_quantity),0)
  INTO
     l_current_qty
  FROM mtl_onhand_quantities_detail
  WHERE organization_id   = p_organization_id
    AND inventory_item_id = p_item_id;

Line: 460

  SELECT
    NVL(SUM(primary_quantity), 0)
  INTO
    l_rollback_qty
  FROM mtl_material_transactions
  WHERE organization_id    = p_organization_id
    AND inventory_item_id  = p_item_id
    AND transaction_date   >= p_date
    AND transaction_action_id NOT IN (24,30,50,51,52,36); /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */

Line: 525

  SELECT
    NVL(SUM(primary_quantity), 0)
  INTO
    l_org_received_qty
  FROM mtl_material_transactions
  WHERE organization_id       = p_item_rec.organization_id
    AND inventory_item_id     = p_item_rec.item_id
    AND transaction_date     >= p_bucket_rec.start_date
    AND transaction_date      < p_bucket_rec.end_date
    AND transaction_quantity  > 0
    AND transaction_action_id NOT IN (24,30,50,51,52);  /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */

Line: 538

  SELECT
    NVL(SUM(-primary_quantity), 0)
  INTO
    l_org_issued_qty
  FROM mtl_material_transactions
  WHERE organization_id       = p_item_rec.organization_id
    AND inventory_item_id     = p_item_rec.item_id
    AND transaction_date     >= p_bucket_rec.start_date
    AND transaction_date      < p_bucket_rec.end_date
    AND transaction_quantity  < 0
    AND transaction_action_id NOT IN (24,30,50,51,52);  /* Bug #3194333 (Container Pack (50),Unpack(51) and Split(52) txns to be excluded) */

Line: 550

  MTL_MGD_INV_POSITIONS_PKG.Insert_Row
  ( p_data_set_name             => p_context_rec.data_set_name
  , p_bucket_name               => p_bucket_rec.name
  , p_organization_code         => p_item_rec.organization_code
  , p_inventory_item_code       => p_item_rec.item_code
  , p_hierarchy_id              => p_context_rec.hierarchy_id
  , p_hierarchy_name            => p_context_rec.hierarchy_name
  , p_parent_organization_code  => p_context_rec.parent_organization_code
  , p_parent_organization_id    => p_context_rec.parent_organization_id
  , p_bucket_size_code          => p_bucket_rec.bucket_size
  , p_bucket_start_date         => p_bucket_rec.start_date
  , p_bucket_end_date           => p_bucket_rec.end_date
  , p_inventory_item_id         => p_item_rec.item_id
  , p_organization_id           => p_item_rec.organization_id
  , p_hierarchy_delta_qty       => 0
  , p_hierarchy_end_on_hand_qty => 0
  , p_org_received_qty          => l_org_received_qty
  , p_org_issued_qty            => l_org_issued_qty
  , p_org_delta_qty             => l_org_received_qty - l_org_issued_qty
  , p_org_end_on_hand_qty       => NVL(p_begin_qty,0) +
                                   l_org_received_qty - l_org_issued_qty
  );

Line: 690

  SELECT
    org_delta_qty
  , org_end_on_hand_qty
  FROM mtl_mgd_inventory_positions
  WHERE data_set_name     = p_data_set_name
    AND organization_id   = p_organization_id
    AND bucket_name       = p_bucket_name
    AND inventory_item_id = p_inventory_item_id;

Line: 705

  SELECT
    hierarchy_delta_qty
  , hierarchy_end_on_hand_qty
  FROM mtl_mgd_inventory_positions
  WHERE data_set_name     = p_data_set_name
    AND organization_id   = p_organization_id
    AND bucket_name       = p_bucket_name
    AND inventory_item_id = p_inventory_item_id;

Line: 953

          MTL_MGD_INV_POSITIONS_PKG.Update_Hierarchy_Data
          ( p_data_set_name             => p_context_rec.data_set_name
          , p_bucket_name               => p_bucket_tbl(l_bkt_idx).name
          , p_organization_id           => p_item_tbl(l_itm_idx).organization_id
          , p_inventory_item_id         => p_item_tbl(l_itm_idx).item_id
          , p_hierarchy_delta_qty       => l_delta_qty
          , p_hierarchy_end_on_hand_qty => l_end_on_hand_qty
          );

Line: 1335

    MTL_MGD_INV_POSITIONS_PKG.Delete_All;

Line: 1339

    MTL_MGD_INV_POSITIONS_PKG.Delete
    ( p_data_set_name  => p_data_set_name
    , p_created_by     => p_created_by
    , p_creation_date  => FND_DATE.canonical_to_date(p_creation_date)
    );