APPS.WMS_UNLOAD_UTILS_PVT SQL Statements

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

Line: 56

      SELECT mmtt.transaction_temp_id
           , ABS(mmtt.transaction_quantity) --mmtt.primary_quantity
        FROM mtl_material_transactions_temp mmtt
       WHERE mmtt.move_order_line_id = mol_id
         AND NOT EXISTS(
              SELECT wdt.transaction_temp_id
                FROM wms_dispatched_tasks wdt
               WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
                 AND wdt.transaction_temp_id IS NOT NULL
                 AND wdt.transaction_temp_id <> p_temp_id);

Line: 68

      SELECT serial_transaction_temp_id
        FROM mtl_transaction_lots_temp
       WHERE transaction_temp_id = p_tmp_id;

Line: 73

      SELECT fm_serial_number
           , to_serial_number
        FROM mtl_serial_numbers_temp
       WHERE transaction_temp_id = p_temp_id;

Line: 79

      SELECT fm_serial_number
           , to_serial_number
        FROM mtl_serial_numbers_temp msnt
       WHERE msnt.transaction_temp_id = p_sn_temp_id;

Line: 85

      SELECT serial_transaction_temp_id
        FROM mtl_transaction_lots_temp
       WHERE transaction_temp_id = p_temp_id;

Line: 100

    SELECT COUNT(transaction_temp_id)
      INTO cnt
      FROM wms_dispatched_tasks
     WHERE transaction_temp_id = p_temp_id;

Line: 120

      SELECT move_order_line_id
           , organization_id
           , inventory_item_id
           , content_lpn_id
           , transfer_lpn_id
           , wms_task_type
        INTO mol_id
           , l_org_id
           , l_item_id
           , l_content_lpn_id
           , l_transfer_lpn_id
           , l_wms_task_types
        FROM mtl_material_transactions_temp
       WHERE transaction_temp_id = p_temp_id;

Line: 155

        SELECT line_status
          INTO line_status
          FROM mtl_txn_request_lines
         WHERE line_id = mol_id;

Line: 201

        , p_quantity_to_delete         => l_quantity
        );

Line: 221

        mydebug(' alloc quantity deleted ' || l_del_quantity);

Line: 224

      UPDATE mtl_txn_request_lines
         SET quantity_detailed =(quantity_detailed - l_del_quantity)
       WHERE line_id = mol_id;

Line: 229

        mydebug('updated mol:' || mol_id);

Line: 232

      DELETE      wms_dispatched_tasks
            WHERE transaction_temp_id = p_temp_id;

Line: 236

        mydebug('deleted from wms_dispatched_tasks ');

Line: 239

      SELECT COUNT(transaction_temp_id)
        INTO cnt
        FROM mtl_material_transactions_temp mmtt
       WHERE mmtt.move_order_line_id = mol_id;

Line: 250

        UPDATE mtl_txn_request_lines
           SET line_status = inv_globals.g_to_status_closed
         WHERE line_id = mol_id;

Line: 255

          mydebug(' updated the mo line status to ' || inv_globals.g_to_status_closed);

Line: 268

      SELECT msi.lot_control_code
           , msi.serial_number_control_code
           , mmtt.serial_allocated_flag
        INTO v_lot_control_code
           , v_serial_control_code
           , v_allocate_serial_flag
        FROM mtl_system_items                msi
           , mtl_material_transactions_temp  mmtt
       WHERE msi.inventory_item_id    = mmtt.inventory_item_id
         AND msi.organization_id      = mmtt.organization_id
         AND mmtt.transaction_temp_id = p_temp_id;

Line: 316

            UPDATE mtl_serial_numbers
               SET group_mark_id = NULL
              WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
              --Bug 2940878 fix added org and item restriction
              AND current_organization_id = l_org_id
              AND inventory_item_id = l_item_id;

Line: 327

          DELETE      mtl_serial_numbers_temp
                WHERE transaction_temp_id = p_temp_id;

Line: 352

              UPDATE mtl_serial_numbers
                 SET group_mark_id = NULL
                WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
                --Bug 2940878 fix added org and item restriction
              AND current_organization_id = l_org_id
              AND inventory_item_id = l_item_id;

Line: 362

            DELETE FROM mtl_serial_numbers_temp
             WHERE transaction_temp_id = l_serial_transaction_temp_id;

Line: 368

          DELETE      mtl_serial_numbers_temp
                WHERE transaction_temp_id IN(SELECT mtlt.serial_transaction_temp_id
                                               FROM mtl_transaction_lots_temp mtlt
                                              WHERE mtlt.transaction_temp_id = p_temp_id);

Line: 377

          UPDATE mtl_transaction_lots_temp
             SET serial_transaction_temp_id = NULL
           WHERE transaction_temp_id = p_temp_id;

Line: 382

            mydebug(' update done ');

Line: 418

      UPDATE mtl_material_transactions_temp
         SET lpn_id = NULL
           , content_lpn_id = NULL
           , transfer_lpn_id = NULL
           , wms_task_status = 1 -- Bug4185621: update mmtt task status back to pending
       WHERE transaction_temp_id = p_temp_id;

Line: 425

      DELETE      wms_dispatched_tasks
            WHERE transaction_temp_id = p_temp_id;

Line: 429

        mydebug('deleted WDT with temp_id ' || p_temp_id);

Line: 433

        DELETE FROM mtl_material_transactions_temp
              WHERE transaction_temp_id = p_temp_id;

Line: 449

      SELECT COUNT(1)
        INTO l_count
        FROM mtl_material_transactions_temp
       WHERE transfer_lpn_id = l_transfer_lpn_id;

Line: 457

	    SELECT lpn_context INTO l_lpn_context
	      FROM wms_license_plate_numbers
	      WHERE lpn_id = l_transfer_lpn_id;

Line: 593

      SELECT 'CANCELLED'
           , mmtt.transaction_temp_id
           , ABS(mmtt.transaction_quantity)
           , mmtt.transaction_uom
           , mmtt.move_order_line_id
           , mtrl.uom_code
        FROM mtl_material_transactions_temp  mmtt
           , mtl_txn_request_lines           mtrl
       WHERE mmtt.parent_line_id = p_temp_id
         AND mtrl.line_id        = mmtt.move_order_line_id
         AND mtrl.line_status    = 9
       UNION ALL
      SELECT 'OVERPICKED'
           , mmtt.transaction_temp_id
           , ABS(mmtt.transaction_quantity)
           , mmtt.transaction_uom
           , to_number(NULL)
           , to_char(NULL)
        FROM mtl_material_transactions_temp  mmtt
       WHERE mmtt.parent_line_id        = p_temp_id
         AND mmtt.transaction_temp_id  <> mmtt.parent_line_id
         AND mmtt.transaction_action_id = 2
         AND mmtt.move_order_line_id   IS NULL;

Line: 619

      SELECT serial_transaction_temp_id
        FROM mtl_transaction_lots_temp
       WHERE transaction_temp_id = p_temp_id;

Line: 625

      SELECT fm_serial_number
           , to_serial_number
        FROM mtl_serial_numbers_temp
       WHERE transaction_temp_id = p_temp_id;

Line: 632

      SELECT fm_serial_number
           , to_serial_number
        FROM mtl_serial_numbers_temp msnt
       WHERE msnt.transaction_temp_id = p_sn_temp_id;

Line: 639

      SELECT serial_transaction_temp_id
        FROM mtl_transaction_lots_temp
       WHERE transaction_temp_id = p_temp_id;

Line: 649

    SELECT m.transaction_temp_id
         , 1                      dummy_sort
      FROM wms_dispatched_tasks            w
         , mtl_material_transactions_temp  m
     WHERE m.transfer_lpn_id      = p_xfer_lpn_id
       AND m.transaction_temp_id <> p_temp_id
       AND m.transaction_temp_id  = m.parent_line_id
       AND w.transaction_temp_id  = m.transaction_temp_id
       AND w.status               = 4
       AND EXISTS
         ( SELECT 'x'
             FROM mtl_material_transactions_temp  m2
            WHERE m2.transfer_lpn_id      = m.transfer_lpn_id
              AND m2.organization_id      = m.organization_id
              AND m2.transaction_temp_id  = m2.parent_line_id
              AND m2.transaction_temp_id <> m.transaction_temp_id
              AND m2.transaction_temp_id <> p_temp_id
              AND m2.content_lpn_id       = m.transfer_lpn_id
         )
     UNION ALL
    -- Content LPNs
    SELECT m.transaction_temp_id
         , 2                      dummy_sort
      FROM wms_dispatched_tasks            w
         , mtl_material_transactions_temp  m
     WHERE m.transfer_lpn_id      = p_xfer_lpn_id
       AND m.transaction_temp_id <> p_temp_id
       AND m.transaction_temp_id  = m.parent_line_id
       AND w.transaction_temp_id  = m.transaction_temp_id
       AND w.status               = 4
       AND m.content_lpn_id      IS NOT NULL
     UNION ALL
    -- Material unpacked from content LPNs
    SELECT m.transaction_temp_id
         , 3                      dummy_sort
      FROM wms_dispatched_tasks            w
         , mtl_material_transactions_temp  m
     WHERE m.transfer_lpn_id      = p_xfer_lpn_id
       AND m.transaction_temp_id <> p_temp_id
       AND m.transaction_temp_id  = m.parent_line_id
       AND w.transaction_temp_id  = m.transaction_temp_id
       AND w.status               = 4
       AND EXISTS
         ( SELECT 'x'
             FROM mtl_material_transactions_temp  m2
            WHERE m2.transfer_lpn_id      = m.transfer_lpn_id
              AND m2.organization_id      = m.organization_id
              AND m2.transaction_temp_id  = m2.parent_line_id
              AND m2.transaction_temp_id <> m.transaction_temp_id
              AND m2.transaction_temp_id <> p_temp_id
              AND m2.content_lpn_id       = m.lpn_id
         )
     UNION ALL
    -- All other picked material
    SELECT m.transaction_temp_id
         , 4                      dummy_sort
      FROM wms_dispatched_tasks            w
         , mtl_material_transactions_temp  m
     WHERE m.transfer_lpn_id      = p_xfer_lpn_id
       AND m.transaction_temp_id <> p_temp_id
       AND m.transaction_temp_id  = m.parent_line_id
       AND w.transaction_temp_id  = m.transaction_temp_id
       AND w.status               = 4
       AND m.content_lpn_id      IS NULL
       AND ( (m.lpn_id           IS NOT NULL
              AND NOT EXISTS
                ( SELECT 'x'
                    FROM mtl_material_transactions_temp  m2
                   WHERE m2.transfer_lpn_id      = m.transfer_lpn_id
                     AND m2.organization_id      = m.organization_id
                     AND m2.transaction_temp_id  = m2.parent_line_id
                     AND m2.transaction_temp_id <> m.transaction_temp_id
                     AND m2.transaction_temp_id <> p_temp_id
                     AND m2.content_lpn_id       = m.lpn_id
                )
             )
             OR m.lpn_id         IS NULL
           )
       AND NOT EXISTS
           ( SELECT 'x'
             FROM mtl_material_transactions_temp  m3
            WHERE m3.transfer_lpn_id      = m.transfer_lpn_id
              AND m3.organization_id      = m.organization_id
              AND m3.transaction_temp_id  = m3.parent_line_id
              AND m3.transaction_temp_id <> m.transaction_temp_id
              AND m3.transaction_temp_id <> p_temp_id
              AND m3.content_lpn_id       = m.transfer_lpn_id
           )
     ORDER BY dummy_sort;

Line: 741

    l_parent_deleted  BOOLEAN := FALSE;

Line: 761

      SELECT organization_id
           , inventory_item_id
           , content_lpn_id
           , transfer_lpn_id
        INTO l_org_id
           , l_item_id
           , l_content_lpn_id
           , l_transfer_lpn_id
        FROM mtl_material_transactions_temp
       WHERE transaction_temp_id = p_txn_temp_id;

Line: 843

         , p_quantity_to_delete  => l_quantity
         );

Line: 847

         inv_trx_util_pub.delete_transaction
         ( x_return_status       => l_return_status
         , x_msg_data            => l_msg_data
         , x_msg_count           => l_msg_count
         , p_transaction_temp_id => l_temp_id
         , p_update_parent       => TRUE
         );

Line: 883

         UPDATE mtl_txn_request_lines
            SET quantity_detailed = (quantity_detailed - l_conv_qty)
          WHERE line_id = l_mo_line_id;

Line: 888

            mydebug('Updated mol: ' || to_char(l_mo_line_id));

Line: 891

         SELECT COUNT(transaction_temp_id)
           INTO l_count
           FROM mtl_material_transactions_temp mmtt
          WHERE mmtt.move_order_line_id = l_mo_line_id;

Line: 901

           UPDATE mtl_txn_request_lines
              SET line_status = inv_globals.g_to_status_closed
            WHERE line_id = l_mo_line_id;

Line: 916

      SELECT msi.lot_control_code
           , msi.serial_number_control_code
           , mmtt.serial_allocated_flag
        INTO v_lot_control_code
           , v_serial_control_code
           , v_allocate_serial_flag
        FROM mtl_system_items                msi
           , mtl_material_transactions_temp  mmtt
       WHERE msi.inventory_item_id    = mmtt.inventory_item_id
         AND msi.organization_id      = mmtt.organization_id
         AND mmtt.transaction_temp_id = p_txn_temp_id;

Line: 930

           mydebug('Parent MMTT deleted when cancelled child tasks were processed');

Line: 932

        l_parent_deleted := TRUE;

Line: 938

    IF NOT l_parent_deleted THEN
       IF (l_debug = 1) THEN
          mydebug(' lot code ' || v_lot_control_code);

Line: 965

               UPDATE mtl_serial_numbers
                  SET group_mark_id = NULL
                WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
                  AND current_organization_id = l_org_id
                  AND inventory_item_id = l_item_id;

Line: 981

             DELETE mtl_serial_numbers_temp
              WHERE transaction_temp_id = p_txn_temp_id;

Line: 1005

                 UPDATE mtl_serial_numbers
                    SET group_mark_id = NULL
                  WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
                    AND current_organization_id = l_org_id
                    AND inventory_item_id = l_item_id;

Line: 1021

               DELETE FROM mtl_serial_numbers_temp
                WHERE transaction_temp_id = l_serial_transaction_temp_id;

Line: 1026

             UPDATE mtl_transaction_lots_temp
                SET serial_transaction_temp_id = NULL
              WHERE transaction_temp_id = p_txn_temp_id;

Line: 1031

                mydebug('Updated MTLT');

Line: 1040

       DELETE wms_dispatched_tasks
        WHERE transaction_temp_id = p_txn_temp_id;

Line: 1047

       UPDATE mtl_material_transactions_temp
          SET lpn_id          = NULL
            , content_lpn_id  = NULL
            , transfer_lpn_id = NULL
            , wms_task_status = 1 -- Bug4185621: update mmtt task status back to pending
        WHERE parent_line_id = p_txn_temp_id;

Line: 1054

    END IF; -- end if parent not deleted

Line: 1057

    SELECT COUNT(*)
      INTO l_count
      FROM mtl_material_transactions_temp
     WHERE transfer_lpn_id = l_transfer_lpn_id;

Line: 1069

	      SELECT lpn_context INTO l_lpn_context
		FROM wms_license_plate_numbers
		WHERE lpn_id = l_transfer_lpn_id;