DBA Data[Home] [Help]

APPS.INV_PR_PICK_SLIP_NUMBER SQL Statements

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

Line: 87

  PROCEDURE insert_key
  ( l_hash_value        IN   NUMBER
  , l_insert_key_rec    IN   keyrectyp
  , x_pick_slip_number  OUT  NOCOPY  NUMBER
  , x_error_message     OUT  NOCOPY  VARCHAR2
  ) IS
  BEGIN
    SELECT wsh_pick_slip_numbers_s.NEXTVAL
      INTO x_pick_slip_number
      FROM DUAL;
Line: 98

    g_pskey_table(l_hash_value)                   := l_insert_key_rec;
Line: 103

      x_error_message  := 'Error occurred in INV_PR_PICK_NUMBER.INSERT_KEY';
Line: 105

         inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.INSERT_KEY',3);
Line: 107

  END insert_key;
Line: 163

  , x_insert_key_rec    OUT    NOCOPY keyrectyp
  , x_error_message     OUT    NOCOPY VARCHAR2
  , p_dest_subinventory IN     VARCHAR2 DEFAULT NULL
  , p_dest_locator_id   IN     NUMBER   DEFAULT NULL
  , p_project_id        IN     NUMBER   DEFAULT NULL
  , p_task_id           IN     NUMBER   DEFAULT NULL
) IS
    l_hash_string VARCHAR2(2000) := NULL;
Line: 173

    x_insert_key_rec.grouping_rule_id  := g_rule_table(p_rule_index).grouping_rule_id;
Line: 177

      x_insert_key_rec.header_id  := p_header_id;
Line: 182

      x_insert_key_rec.customer_id  := p_customer_id;
Line: 187

      x_insert_key_rec.ship_method_code  := p_ship_method_code;
Line: 192

      x_insert_key_rec.ship_to_loc_id  := p_ship_to_loc_id;
Line: 197

      x_insert_key_rec.shipment_priority  := p_shipment_priority;
Line: 202

      x_insert_key_rec.trip_stop_id  := p_trip_stop_id;
Line: 207

      x_insert_key_rec.delivery_id  := p_delivery_id;
Line: 212

      x_insert_key_rec.subinventory  := p_subinventory;
Line: 217

      x_insert_key_rec.locator_id  := p_locator_id;
Line: 222

      x_insert_key_rec.inventory_item_id  := p_inventory_item_id;
Line: 227

      x_insert_key_rec.lot_number  := p_lot_number;
Line: 232

      x_insert_key_rec.revision  := p_revision;
Line: 239

      x_insert_key_rec.dest_subinventory := p_dest_subinventory;
Line: 244

      x_insert_key_rec.dest_locator_id   := p_dest_locator_id;
Line: 249

      x_insert_key_rec.project_id  := p_project_id;
Line: 254

      x_insert_key_rec.task_id  := p_task_id;
Line: 258

    x_insert_key_rec.organization_id   := p_org_id;
Line: 340

      SELECT NVL(order_number_flag, 'N')
           , NVL(customer_flag, 'N')
           , NVL(ship_to_flag, 'N')
           , NVL(carrier_flag, 'N')
           , NVL(shipment_priority_flag, 'N')
           , NVL(trip_stop_flag, 'N')
           , NVL(delivery_flag, 'N')
           , NVL(subinventory_flag, 'N')
           , NVL(locator_flag, 'N')
           , NVL(dest_sub_flag, 'N')
           , NVL(dest_loc_flag, 'N')
           , NVL(project_flag, 'N')
           , NVL(task_flag, 'N')
           , NVL(item_flag, 'N')
           , NVL(revision_flag, 'N')
           , NVL(lot_flag, 'N')
           , NVL(pick_method, '-99')
        FROM wsh_pick_grouping_rules
       WHERE pick_grouping_rule_id = v_pgr_id;
Line: 362

      SELECT NVL(pick_slip_lines, -1)
        FROM wsh_shipping_parameters
       WHERE organization_id = v_org_id;
Line: 367

    l_insert_key_rec keyrectyp;
Line: 406

      SELECT wsh_pick_slip_numbers_s.NEXTVAL
      INTO x_pick_slip_number
      FROM dual;
Line: 427

      SELECT wsh_pick_slip_numbers_s.NEXTVAL
        INTO x_pick_slip_number
        FROM DUAL;
Line: 474

        SELECT wsh_pick_slip_numbers_s.NEXTVAL
          INTO x_pick_slip_number
          FROM DUAL;
Line: 495

        , x_insert_key_rec             => l_insert_key_rec
        , x_error_message              => x_error_message
        , p_dest_subinventory          => p_dest_subinventory
        , p_dest_locator_id            => p_dest_locator_id
        , p_project_id                 => p_project_id
        , p_task_id                    => p_task_id
        );
Line: 502

        insert_key(
          l_hash_value                 => l_hash_value
        , l_insert_key_rec             => l_insert_key_rec
        , x_pick_slip_number           => x_pick_slip_number
        , x_error_message              => x_error_message
        );
Line: 533

    , x_insert_key_rec             => l_insert_key_rec
    , x_error_message              => x_error_message
    , p_dest_subinventory          => p_dest_subinventory
    , p_dest_locator_id            => p_dest_locator_id
    , p_project_id                 => p_project_id
    , p_task_id                    => p_task_id
    );
Line: 552

          g_pskey_table.DELETE(l_hash_value);
Line: 557

      insert_key(
        l_hash_value                 => l_hash_value
      , l_insert_key_rec             => l_insert_key_rec
      , x_pick_slip_number           => x_pick_slip_number
      , x_error_message              => x_error_message
      );
Line: 590

  PROCEDURE insert_key
  ( l_hash_value        IN   NUMBER
  , l_insert_key_rec    IN   wipkeyrectyp
  , x_pick_slip_number  OUT  NOCOPY  NUMBER
  , x_error_message     OUT  NOCOPY  VARCHAR2
  ) IS
  BEGIN
    SELECT wsh_pick_slip_numbers_s.NEXTVAL
      INTO x_pick_slip_number
      FROM DUAL;
Line: 601

    g_wip_pskey_table(l_hash_value)                   := l_insert_key_rec;
Line: 605

      x_error_message  := 'Error occurred in INV_PR_PICK_NUMBER.INSERT_KEY';
Line: 607

         inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.INSERT_KEY',3);
Line: 609

  END insert_key;
Line: 665

  , x_insert_key_rec      OUT    NOCOPY wipkeyrectyp
  , x_error_message       OUT    NOCOPY VARCHAR2
  ) IS
    l_hash_string VARCHAR2(2000) := NULL;
Line: 671

    x_insert_key_rec.grouping_rule_id  := g_rule_table(p_rule_index).grouping_rule_id;
Line: 675

      x_insert_key_rec.wip_entity_id    := p_wip_entity_id;
Line: 676

      x_insert_key_rec.rep_schedule_id  := p_rep_schedule_id;
Line: 681

      x_insert_key_rec.operation_seq_num  := p_operation_seq_num;
Line: 686

      x_insert_key_rec.dept_id  := p_dept_id;
Line: 691

      x_insert_key_rec.push_or_pull  := p_push_or_pull;
Line: 696

      x_insert_key_rec.supply_subinventory  := p_supply_subinventory;
Line: 701

      x_insert_key_rec.supply_locator_id  := p_supply_locator_id;
Line: 706

      x_insert_key_rec.project_id  := p_project_id;
Line: 711

      x_insert_key_rec.task_id  := p_task_id;
Line: 716

      x_insert_key_rec.src_subinventory  := p_src_subinventory;
Line: 721

      x_insert_key_rec.src_locator_id  := p_src_locator_id;
Line: 726

      x_insert_key_rec.inventory_item_id  := p_inventory_item_id;
Line: 731

      x_insert_key_rec.lot_number  := p_lot_number;
Line: 736

      x_insert_key_rec.revision  := p_revision;
Line: 739

    x_insert_key_rec.organization_id   := p_org_id;
Line: 804

      SELECT NVL(job_schedule_flag, 'N')
           , NVL(operation_flag, 'N')
           , NVL(department_flag, 'N')
           , NVL(push_vs_pull_flag, 'N')
           , NVL(dest_sub_flag, 'N')
           , NVL(dest_loc_flag, 'N')
           , NVL(project_flag, 'N')
           , NVL(task_flag, 'N')
           , NVL(subinventory_flag, 'N')
           , NVL(locator_flag, 'N')
           , NVL(item_flag, 'N')
           , NVL(revision_flag, 'N')
           , NVL(lot_flag, 'N')
           , NVL(pick_method, '-99')
        FROM wsh_pick_grouping_rules
       WHERE pick_grouping_rule_id = v_pgr_id;
Line: 821

    l_insert_key_rec wipkeyrectyp;
Line: 837

      SELECT wsh_pick_slip_numbers_s.NEXTVAL
        INTO x_pick_slip_number
        FROM DUAL;
Line: 877

        SELECT wsh_pick_slip_numbers_s.NEXTVAL
          INTO x_pick_slip_number
          FROM DUAL;
Line: 900

        , x_insert_key_rec             => l_insert_key_rec
        , x_error_message              => x_error_message
        );
Line: 903

        insert_key(
          l_hash_value                 => l_hash_value
        , l_insert_key_rec             => l_insert_key_rec
        , x_pick_slip_number           => x_pick_slip_number
        , x_error_message              => x_error_message
        );
Line: 936

    , x_insert_key_rec             => l_insert_key_rec
    , x_error_message              => x_error_message
    );
Line: 945

      insert_key(
        l_hash_value                 => l_hash_value
      , l_insert_key_rec             => l_insert_key_rec
      , x_pick_slip_number           => x_pick_slip_number
      , x_error_message              => x_error_message
      );
Line: 1023

  PROCEDURE delete_wip_ps_tbl IS
  BEGIN
    g_wip_pskey_table.DELETE;
Line: 1028

      inv_log_util.trace('Exception: ' || SQLERRM,'INV_PR_PICK_SLIP_NUMBER.DELETE_WIP_PS_TBL', 3);
Line: 1029

  END delete_wip_ps_tbl;
Line: 1244

        INSERT INTO mtl_pick_slip_numbers
        ( id
        , pick_slip_batch_id
        , pick_slip_count
        , pick_slip_identifier
        , pick_slip_number
        , status
        ) VALUES ( mtl_pick_slip_numbers_s.nextval
                 , l_batch_id
                 , l_pick_slip_count
                 , p_hash_string
                 , wsh_pick_slip_numbers_s.nextval
                 , l_pick_slip_status
                 )
         RETURNING pick_slip_number
              INTO l_pick_slip_number;
Line: 1262

           inv_log_util.trace('Inserted pick slip # ' || l_pick_slip_number,
              'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
Line: 1270

              inv_log_util.trace('INSERT failed, row already exists',
                 'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
Line: 1274

              SELECT pick_slip_number
                   , pick_slip_count
                INTO l_pick_slip_number
                   , l_pick_slip_count
                FROM mtl_pick_slip_numbers
               WHERE pick_slip_identifier = p_hash_string
                 AND status = 1
                 FOR UPDATE WAIT 5;
Line: 1299

                    inv_log_util.trace('No data found, so retrying INSERT',
                       'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
Line: 1325

     SELECT wsh_pick_slip_numbers_s.nextval
       INTO l_pick_slip_number
       FROM dual;
Line: 1330

        inv_log_util.trace('Failed to INSERT or LOCK pick slip record.  Returning next value: '
           || l_pick_slip_number,
           'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
Line: 1338

     UPDATE mtl_pick_slip_numbers
        SET pick_slip_count = pick_slip_count + 1
          , status = l_pick_slip_status
      WHERE pick_slip_identifier = p_hash_string
        AND status = 1
            RETURNING pick_slip_count INTO l_pick_slip_count;
Line: 1345

        inv_log_util.trace('Updated count to ' || l_pick_slip_count ||
           ', status to ' || l_pick_slip_status,
           'INV_PR_PICK_SLIP_NUMBER.GEN_PARALLEL_PICK_SLIP_NUMBER', 3);
Line: 1432

      SELECT NVL(order_number_flag, 'N')
           , NVL(customer_flag, 'N')
           , NVL(ship_to_flag, 'N')
           , NVL(carrier_flag, 'N')
           , NVL(shipment_priority_flag, 'N')
           , NVL(trip_stop_flag, 'N')
           , NVL(delivery_flag, 'N')
           , NVL(subinventory_flag, 'N')
           , NVL(locator_flag, 'N')
           , NVL(dest_sub_flag, 'N')
           , NVL(dest_loc_flag, 'N')
           , NVL(project_flag, 'N')
           , NVL(task_flag, 'N')
           , NVL(item_flag, 'N')
           , NVL(revision_flag, 'N')
           , NVL(lot_flag, 'N')
           , NVL(pick_method, '-99')
        FROM wsh_pick_grouping_rules
       WHERE pick_grouping_rule_id = v_pgr_id;
Line: 1453

      SELECT NVL(pick_slip_lines, -1)
        FROM wsh_shipping_parameters
       WHERE organization_id = v_org_id;
Line: 1458

    l_insert_key_rec keyrectyp;
Line: 1491

      SELECT wsh_pick_slip_numbers_s.NEXTVAL
      INTO x_pick_slip_number
      FROM dual;
Line: 1539

      SELECT wsh_pick_slip_numbers_s.NEXTVAL
        INTO x_pick_slip_number
        FROM DUAL;
Line: 1588

	  --<< check if deletion from the table is required, if so we can delete instead of update >>
        END IF;