APPS.WMS_UT_PKG SQL Statements

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

Line: 308

  ' SELECT * '			 ||
  ' FROM wms_ut_tab'		 ||
  ' WHERE test_id = :p_test_id ' ||
    ' AND ROWNUM = 1 '
   INTO c_test_rec
   USING p_test_id;

Line: 316

 ' INSERT INTO wms_ut_tab '	||
 ' (FLOW_TYPE_ID,'		||
 ' TESTSET_ID,'			||
 ' TESTSET,'			||
 ' TEST_ID,'			||
 ' TESTNAME,'			||
 ' TEXT,'			||
 ' DATATYPE,'			||
 ' IN_OUT,'			||
 ' RUNID)'			||
 ' values '			||
 ' (:flow_type_id, :testset_id, :testset, :p_test_id, :testname, :p_text, :p_datatype, :p_out, :p_runid) '
 using c_test_rec.flow_type_id, c_test_rec.testset_id, c_test_rec.testset, p_test_id, c_test_rec.testname, p_text, p_datatype, 'OUT', p_runid;

Line: 345

        SELECT inventory_item_id
         INTO g_item_id
        FROM mtl_system_items
        WHERE organization_id = p_org_id
          AND segment1 = p_item;

Line: 371

        select inventory_location_id
        into l_loc_id
        from mtl_item_locations
        where organization_id = p_org_id
          and subinventory_code = p_sub_code
          and segment1 =  l_segs(1)
          and segment2 =  l_segs(2)
          and segment3 =  l_segs(3);

Line: 393

  SELECT lpn_id
  INTO l_lpn_id
  FROM wms_license_plate_numbers
  WHERE license_plate_number = p_lpn
    AND organization_id = p_org_id;

Line: 458

     SELECT MTL_MATERIAL_TRANSACTIONS_S.nextval
     INTO l_trx_hdr_id
     FROM DUAL;

Line: 475

     retval := INV_TRX_UTIL_PUB.INSERT_LINE_TRX(
                                p_trx_hdr_id => l_trx_hdr_id,
                                p_item_id => l_item_id,
                                p_revision => l_revision,
                                p_org_id => p_org_id,
                                p_trx_action_id => l_trx_action_id,
                                p_subinv_code => l_subinv_code,
                                p_tosubinv_code => l_tosubinv_code,
                                p_locator_id => l_locator_id,
                                p_tolocator_id => l_tolocator_id,
                                p_xfr_org_id => NULL,
                                p_trx_type_id => l_trx_type_id,
                                p_trx_src_type_id => l_trx_src_type_id,
                                p_trx_qty => l_pri_qty,
                                p_pri_qty => l_pri_qty,
                                p_uom => l_uom,
                                p_date =>  sysdate,
                                p_reason_id => NULL,
                                p_user_id => p_user_id,
                                x_trx_tmp_id => trxid,
                                x_proc_msg => l_ret_msg);

Line: 546

  select *
  into moqdrec
  from mtl_onhand_quantities_detail
  where creation_date > sysdate - 800
    and organization_id = p_org_id
    and rownum = 1;

Line: 553

  select *
  into lotrec
  from mtl_lot_numbers
  where creation_date > sysdate - 800
    and rownum = 1;

Line: 569

        SELECT inventory_item_id, SERIAL_NUMBER_CONTROL_CODE, lot_control_code
        INTO l_item_id, l_ser_ctrl, l_lot_ctrl
        FROM mtl_system_items
        WHERE organization_id = p_org_id
          AND segment1 = l_item;

Line: 599

     SELECT MTL_ONHAND_QUANTITIES_S.nextval INTO l_oh_id FROM DUAL;

Line: 603

     insert into mtl_onhand_quantities_detail
       (INVENTORY_ITEM_ID,
        ORGANIZATION_ID,
        DATE_RECEIVED,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY,
        CREATION_DATE,
        CREATED_BY,
        LAST_UPDATE_LOGIN,
        PRIMARY_TRANSACTION_QUANTITY,
        SUBINVENTORY_CODE,
        REVISION,
        LOCATOR_ID,
        CREATE_TRANSACTION_ID,
        UPDATE_TRANSACTION_ID,
        LOT_NUMBER,
        ORIG_DATE_RECEIVED,
        COST_GROUP_ID,
        CONTAINERIZED_FLAG,
                PROJECT_ID,
                TASK_ID,
                ONHAND_QUANTITIES_ID,
                ORGANIZATION_TYPE,
                OWNING_ORGANIZATION_ID,
                OWNING_TP_TYPE,
                PLANNING_ORGANIZATION_ID,
                PLANNING_TP_TYPE,
                TRANSACTION_UOM_CODE,
                TRANSACTION_QUANTITY,
                SECONDARY_UOM_CODE,
                SECONDARY_TRANSACTION_QUANTITY,
        IS_CONSIGNED)
                values
                (
                moqdrec.INVENTORY_ITEM_ID,
                moqdrec.ORGANIZATION_ID,
                sysdate,
                sysdate,
                moqdrec.LAST_UPDATED_BY,
                sysdate,
                moqdrec.CREATED_BY,
                moqdrec.LAST_UPDATE_LOGIN,
                moqdrec.TRANSACTION_QUANTITY, -- CHECK
                moqdrec.SUBINVENTORY_CODE,
                moqdrec.REVISION,
                moqdrec.LOCATOR_ID,
                moqdrec.CREATE_TRANSACTION_ID,
                moqdrec.UPDATE_TRANSACTION_ID,
                moqdrec.LOT_NUMBER,
                moqdrec.ORIG_DATE_RECEIVED,
                moqdrec.COST_GROUP_ID,
                moqdrec.CONTAINERIZED_FLAG,
                moqdrec.PROJECT_ID,
                moqdrec.TASK_ID,
                l_oh_id,
                moqdrec.ORGANIZATION_TYPE,
                moqdrec.OWNING_ORGANIZATION_ID,
                moqdrec.OWNING_TP_TYPE,
                moqdrec.PLANNING_ORGANIZATION_ID,
                moqdrec.PLANNING_TP_TYPE,
                moqdrec.TRANSACTION_UOM_CODE,
                moqdrec.TRANSACTION_QUANTITY,
        moqdrec.SECONDARY_UOM_CODE,
        moqdrec.SECONDARY_TRANSACTION_QUANTITY,
        moqdrec.IS_CONSIGNED);

Line: 677

        insert into mtl_lot_numbers(
                INVENTORY_ITEM_ID,
                 ORGANIZATION_ID,
                 LOT_NUMBER,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN,
                 EXPIRATION_DATE,
                 DISABLE_FLAG,
                 ATTRIBUTE_CATEGORY,
                 ATTRIBUTE1,
                 ATTRIBUTE2,
                 ATTRIBUTE3,
                 ATTRIBUTE4,
                 ATTRIBUTE5,
                 ATTRIBUTE6,
                 ATTRIBUTE7,
                 ATTRIBUTE8,
                 ATTRIBUTE9,
                 ATTRIBUTE10,
                 ATTRIBUTE11,
                 ATTRIBUTE12,
                 ATTRIBUTE13,
                 ATTRIBUTE14,
                 ATTRIBUTE15,
                 REQUEST_ID,
                 PROGRAM_APPLICATION_ID,
                 PROGRAM_ID,
                 PROGRAM_UPDATE_DATE,
                 GEN_OBJECT_ID,
                 DESCRIPTION,
                 VENDOR_NAME,
                 SUPPLIER_LOT_NUMBER,
                 GRADE_CODE,
                 ORIGINATION_DATE,
                 DATE_CODE,
                 STATUS_ID,
                 CHANGE_DATE,
                 AGE,
                 RETEST_DATE,
                 MATURITY_DATE,
                 LOT_ATTRIBUTE_CATEGORY,
                 ITEM_SIZE,
                 COLOR,
                 VOLUME,
                 VOLUME_UOM,
                 PLACE_OF_ORIGIN,
                 BEST_BY_DATE,
                 LENGTH,
                 LENGTH_UOM,
                 RECYCLED_CONTENT,
                 THICKNESS,
                 THICKNESS_UOM,
                 WIDTH,
                 WIDTH_UOM,
                 CURL_WRINKLE_FOLD,
                 VENDOR_ID,
                 TERRITORY_CODE)
( SELECT
 lotrec.INVENTORY_ITEM_ID,
 lotrec.ORGANIZATION_ID,
 lotrec.LOT_NUMBER,
 sysdate,
 lotrec.LAST_UPDATED_BY,
 sysdate,
 lotrec.CREATED_BY,
 lotrec.LAST_UPDATE_LOGIN,
 null,
 lotrec.DISABLE_FLAG,
 lotrec.ATTRIBUTE_CATEGORY,
 lotrec.ATTRIBUTE1,
 lotrec.ATTRIBUTE2,
 lotrec.ATTRIBUTE3,
 lotrec.ATTRIBUTE4,
 lotrec.ATTRIBUTE5,
 lotrec.ATTRIBUTE6,
 lotrec.ATTRIBUTE7,
 lotrec.ATTRIBUTE8,
 lotrec.ATTRIBUTE9,
 lotrec.ATTRIBUTE10,
 lotrec.ATTRIBUTE11,
 lotrec.ATTRIBUTE12,
 lotrec.ATTRIBUTE13,
 lotrec.ATTRIBUTE14,
 lotrec.ATTRIBUTE15,
 lotrec.REQUEST_ID,
 lotrec.PROGRAM_APPLICATION_ID,
 lotrec.PROGRAM_ID,
 lotrec.PROGRAM_UPDATE_DATE,
 MTL_GEN_OBJECT_ID_S.nextval,
 lotrec.DESCRIPTION,
 lotrec.VENDOR_NAME,
 lotrec.SUPPLIER_LOT_NUMBER,
 lotrec.GRADE_CODE,
 lotrec.ORIGINATION_DATE,
 lotrec.DATE_CODE,
 lotrec.STATUS_ID,
 lotrec.CHANGE_DATE,
 lotrec.AGE,
 lotrec.RETEST_DATE,
 lotrec.MATURITY_DATE,
 lotrec.LOT_ATTRIBUTE_CATEGORY,
 lotrec.ITEM_SIZE,
 lotrec.COLOR,
 lotrec.VOLUME,
 lotrec.VOLUME_UOM,
 lotrec.PLACE_OF_ORIGIN,
 lotrec.BEST_BY_DATE,
 lotrec.LENGTH,
 lotrec.LENGTH_UOM,
 lotrec.RECYCLED_CONTENT,
 lotrec.THICKNESS,
 lotrec.THICKNESS_UOM,
 lotrec.WIDTH,
 lotrec.WIDTH_UOM,
 lotrec.CURL_WRINKLE_FOLD,
 lotrec.VENDOR_ID,
 lotrec.TERRITORY_CODE FROM DUAL);

Line: 837

           print_debug('Inserting Serial : ' || serrec.serial_number || ' - item : ' || serrec.inventory_item_id);

Line: 840

           INSERT INTO mtl_serial_numbers
              ( INVENTORY_ITEM_ID
                ,SERIAL_NUMBER
                , LAST_UPDATE_DATE
                , LAST_UPDATED_BY
                , CREATION_DATE
                , CREATED_BY
                , LAST_UPDATE_LOGIN
                , REQUEST_ID
                , PROGRAM_APPLICATION_ID
                , PROGRAM_ID
                , PROGRAM_UPDATE_DATE
                , INITIALIZATION_DATE
                , COMPLETION_DATE
                , SHIP_DATE
                , CURRENT_STATUS
                , REVISION
                , LOT_NUMBER
                , FIXED_ASSET_TAG
                , RESERVED_ORDER_ID
                , PARENT_ITEM_ID
                , PARENT_SERIAL_NUMBER
                , ORIGINAL_WIP_ENTITY_ID
                , ORIGINAL_UNIT_VENDOR_ID
                , VENDOR_SERIAL_NUMBER
                , VENDOR_LOT_NUMBER
                , LAST_TXN_SOURCE_TYPE_ID
                , LAST_TRANSACTION_ID
                , LAST_RECEIPT_ISSUE_TYPE
                , LAST_TXN_SOURCE_NAME
                , LAST_TXN_SOURCE_ID
                , DESCRIPTIVE_TEXT
                , CURRENT_SUBINVENTORY_CODE
                , CURRENT_LOCATOR_ID
                , CURRENT_ORGANIZATION_ID
                , GEN_OBJECT_ID
                , LPN_ID
                , COST_GROUP_ID
                , ONHAND_QUANTITIES_ID)
VALUES
(
 serrec.INVENTORY_ITEM_ID
,serrec.SERIAL_NUMBER
, sysdate
, p_user_id
, sysdate
, p_user_id
, p_user_id
, serrec.REQUEST_ID
, serrec.PROGRAM_APPLICATION_ID
, serrec.PROGRAM_ID
, serrec.PROGRAM_UPDATE_DATE
, SYSDATE
, SYSDATE
, NULL
, 3
, serrec.REVISION
, serrec.LOT_NUMBER
, serrec.FIXED_ASSET_TAG
, NULL
, NULL
, NULL
, NULL
, NULL
, serrec.VENDOR_SERIAL_NUMBER
, serrec.VENDOR_LOT_NUMBER
, NULL
, NULL
, NULL
, NULL
, NULL
, 'UT Serial Number'
, serrec.CURRENT_SUBINVENTORY_CODE
, serrec.CURRENT_LOCATOR_ID
, serrec.CURRENT_ORGANIZATION_ID
, MTL_GEN_OBJECT_ID_S.nextval
, serrec.LPN_ID
, serrec.cost_group_id
, serrec.ONHAND_QUANTITIES_ID);

Line: 985

     SELECT inventory_item_id
     INTO l_inventory_item_id
     FROM mtl_system_items
     WHERE organization_id = p_org_id
       AND segment1 = l_rsvs_lst(i)(2);

Line: 1075

  SELECT ROWNUM || ' : ' ||
         mr.reservation_id || ', ' ||
         mp.organization_code || ', ' ||
         msi.segment1 || ', ' ||
         mr.revision || ', ' ||
         mr.lot_number || ', ' ||
         mr.subinventory_code || ', ' ||
         mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || ', ' ||
         wlpn.LICENSE_PLATE_NUMBER || ', ' ||
         mr.primary_reservation_quantity || ', ' ||
         mr.primary_uom_code || ', ' ||
         Nvl(mr.detailed_quantity,0) || ', ' ||
         mr.secondary_reservation_quantity || ', ' ||
         mr.secondary_uom_code || ', ' ||
         Nvl(mr.secondary_detailed_quantity,0) as p_text
     INTO l_rt
     FROM mtl_reservations mr, mtl_parameters mp, mtl_system_items msi, wms_license_plate_numbers wlpn, mtl_item_locations mil
     WHERE reservation_id = l_new_reservation_id
       AND msi.organization_id = mr.organization_id
       AND msi.inventory_item_id = mr.inventory_item_id
       AND mp.organization_id = mr.organization_id
       AND wlpn.lpn_id (+) = mr.lpn_id
       AND mil.inventory_location_id (+) = mr.locator_id;

Line: 1150

     SELECT mso.sales_order_id, ol.line_id, nvl(oh.source_document_type_id,11), oh.header_id, oh.order_type_id
     into l_demand_source_header_id, l_demand_source_line_id, l_source_doc_type, l_o_header_id, l_o_type_id
     FROM oe_order_headers_all oh, oe_order_lines_all ol, mtl_sales_orders mso
     WHERE ol.header_id = oh.header_id
       AND oh.order_number = to_number(l_so_orders(i)(2))
       AND ol.line_number = to_number(l_so_orders(i)(3))
       AND mso.segment1 = to_char(oh.order_number)
       AND ROWNUM < 2;

Line: 1164

     SELECT transaction_source_type_id
     INTO l_demand_source_type_id
     FROM mtl_transaction_types
     WHERE transaction_type_id = l_transaction_type_id;

Line: 1220

  l_update_rsv_rec      inv_reservation_global.mtl_reservation_rec_type;

Line: 1253

   SELECT inventory_item_id
   INTO l_inventory_item_id
   FROM mtl_system_items
   WHERE segment1 = l_item
     AND organization_id = p_org_id;

Line: 1266

          l_trohdr_rec.last_updated_by  := p_user_id;

Line: 1267

          l_trohdr_rec.last_update_date := l_date;

Line: 1268

          l_trohdr_rec.last_update_login := p_user_id;

Line: 1312

   l_trolin_tbl(l_ix).last_updated_by    := p_user_id;

Line: 1313

   l_trolin_tbl(l_ix).last_update_date   := l_date;

Line: 1314

   l_trolin_tbl(l_ix).last_update_login  := p_user_id;

Line: 1383

   UPDATE mtl_txn_request_lines
   SET txn_source_line_id = l_trolin_tbl(l_ix).line_id
   WHERE line_id = l_trolin_tbl(l_ix).line_id;

Line: 1473

    SELECT * --order_source_id
           --,ORIG_SYS_DOCUMENT_REF
    FROM oe_headers_interface
    WHERE request_id=l_request_id;

Line: 1479

     SELECT mso.sales_order_id as header_id, ol.line_id, nvl(oh.source_document_type_id,11) as source_doc_type_id
     FROM oe_order_headers_all oh, oe_order_lines_all ol, mtl_sales_orders mso
     WHERE ol.header_id = oh.header_id
       AND oh.header_id = p_header_id
       AND mso.segment1 = to_char(oh.order_number);

Line: 1490

  SELECT to_char(sysdate, 'MM/DD/RR hh:mi:ss')
  INTO l_start_req_date FROM dual;

Line: 1498

         select order_type_id
         into l_order_type_id
         from OE_ORDER_TYPES_V
         where upper(name) = 'ORDER ONLY';

Line: 1518

/*     select oe_perf_orig_sys_doc_ref_s.nextval
     into l_request_id from dual;

Line: 1521

     select oe_perf_orig_sys_doc_ref_s.nextval
     into l_order_source_id from dual;*/

Line: 1550

        select MTL_MATERIAL_TRANSACTIONS_S.nextval
        into l_orig_sys_document_ref from dual;

Line: 1555

      INSERT INTO OE_HEADERS_IFACE_ALL (
         creation_date
        , created_by
        , last_update_date
        , last_updated_by
        , orig_sys_document_ref
        , order_type_id
        , sold_to_org_id
        , sold_to_contact_id
        , order_source_id
        , ordered_date
        , transactional_curr_code
        , invoice_customer_number
        , invoice_to_org_id
        , ship_to_org_id
        , ship_to_customer_number
        , price_list_id
        , request_date
        , invoice_to_contact_id
        , ship_to_contact_id
        , shipment_priority_code
        , shipping_method_code
        , freight_terms_code
        , fob_point_code
        , accounting_rule_id
        , invoicing_rule_id
        , operation_code
        , request_id
        , salesrep_id			-- SALESREP_ID
        , payment_term_id		-- PAYMENT_TERM_ID
        , tax_exempt_flag		-- Tax_Exempt_Flag
        , attribute10
        , attribute1
        , attribute2
        , batch_id
        , header_id
        )
        VALUES(
         SYSDATE	-- creation_date
         , p_user_id  	-- created_by constant
        , SYSDATE	-- last_update_date
        , p_user_id	-- last_updated_by constant
        , l_orig_sys_document_ref	-- orig_sys_document_ref
        , l_order_type_id --'Order Only'2539 	-- order_type
        , cust_id	-- sold_to_org_id
        , sold_to_contact_id	-- sold_to_contact_id
        , l_order_source_id	-- order_source_id
        , sysdate	-- ordered_date
        , 'USD'		-- transactional_curr_code
        , null    -- cust_id	-- invoice_customer_number
        , invoice_to_id  	-- invoice_to_org_id
        , ship_to_id	-- ship_to_org_id
        , null -- cust_id	-- ship_to_customer_number
        , 1000		-- price_list_id
        , sysdate	-- request_date
        , invoice_to_contact_id		-- invoice_to_contact_id
        , ship_to_contact_id		-- ship_to_contact_id
        , 'Standard'		-- shipment_priority_code
        , null		-- shipping_method_code DHL
        , NULL		-- freight_terms_code
        , NULL		-- fob_point_code
        , 1		-- accounting_rule_id
        , '-2'		-- invoicing_rule_id
        , 'INSERT' --OE_GLOBALS.G_OPR_CREATE   --'INSERT'      -- 'INSERT'	-- operation_code
        , l_request_id
        , 1000		-- salesrep_id
        , 4		-- PAYMENT_TERM_ID
        , 'S'		-- Tax_Exempt_Flag
        , '11'
        , sysdate + 10
        , 'G'
        , l_request_id
        , oe_order_headers_s.nextval -- header_id
        );

Line: 1635

	INSERT INTO OE_LINES_IFACE_ALL (
	 creation_date
	, created_by
	, last_update_date
	, last_updated_by
	, orig_sys_document_ref
	, orig_sys_line_ref
	, sold_to_org_id
	, line_number
	, line_type_id
	, order_quantity_uom
	, ordered_quantity
	, unit_list_price
	, unit_selling_price
	, customer_item_id_type
	, inventory_item_id
	, customer_item_id
	, schedule_date
	, ship_to_contact_id
	, shipment_priority_code
	, shipping_method_code
	, price_list_id
	, accounting_rule_id
	, invoicing_rule_id
	, calculate_price_flag
	, order_source_id
	, pricing_date
	, promise_date
	, TAX_CODE
	, operation_code
	, request_id
	, salesrep_id
	, payment_term_id
	, tax_exempt_flag
	, invoice_to_org_id
	, invoice_to_contact_id
	, ship_from_org_id
        , line_id
        , request_date
        , schedule_ship_date
	)
	VALUES(
	  SYSDATE		-- creation_date
        , -1			-- created_by
        , SYSDATE		-- last_update_date
        , -1			-- last_updated_by
        , l_orig_sys_document_ref	-- orig_sys_document_ref
        , loop_counter2		-- orig_sys_line_ref
	, cust_id		-- sold_to_org_id
        , loop_counter2		-- line_number
        , 1427   --null			-- line_type_id
        , 'Ea'			-- order_quantity_uom
        , c_quantity			-- ordered_quantity
        , 27.34 --null         --5354.15		-- unit_list_price
        , 27.34 --null         --5354.15		-- unit_selling_price
	   ,'INT'			-- item_identifier_type
        , c_item_id			-- inventory_item_id
        , c_item_id			-- item_id
        , SYSDATE		-- schedule_date
        , ship_to_contact_id		-- ship_to_contact_id
        , 'Standard'			-- shipment_priority_code
        , null		-- shipping_method_code DHL
        , 1000			-- price_list_id
        , 1			-- accounting_rule_id
        , '-2'			-- invoicing_rule_id
        , 'N'			-- calculate_price_flag
        , l_order_source_id              -- order_source_id
        , SYSDATE		-- pricing_date
        , SYSDATE		-- promise_date
        , NULL			-- 'Sales Tax' tax code
        , 'INSERT' --OE_GLOBALS.G_OPR_CREATE  --'INSERT'		-- operation_code
        , l_request_id
	, 1000			-- salesrep_id
	, 4			-- payment term : '30 Net'
	, 'S'			-- tax exempt: 'Standard'
	, invoice_to_id	-- invoice_to_org_id
	, invoice_to_contact_id		-- invoice_to_contact_id
	, p_org_id                         -- ship_from_org_id
        , oe_order_lines_s.nextval -- line_id
        , sysdate      --request_date
        , sysdate + 1
        );

Line: 1718

        print_debug('Inserted Order Line ' || loop_counter2);

Line: 1722

       INSERT INTO OE_ACTIONS_INTERFACE (
        order_source_id
       , orig_sys_document_ref
       , operation_code
       , request_id
        )
       VALUES(
        l_order_source_id         -- order_source_id
       , l_orig_sys_document_ref   -- orig_sys_document_ref
       , OE_GLOBALS.G_BOOK_ORDER   -- operation_code
       , l_request_id
       );

Line: 1794

  SELECT to_char(sysdate, 'MM/DD/RR hh:mi:ss')
  INTO l_end_req_date FROM dual;

Line: 1803

     update oe_headers_interface
       set error_flag = 'N'
      where request_id = l_request_id
       and order_source_id = l_order_source_id
       and orig_sys_document_ref = l_orig_sys_document_ref;

Line: 1809

     select order_number, header_id
     into l_order_number, l_o_header_id
     from oe_order_headers_all
     where ORIG_SYS_DOCUMENT_REF = L_ORIG_SYS_DOCUMENT_REF
          and creation_date > sysdate - 1 ;

Line: 1825

        SELECT transaction_source_type_id
        INTO l_demand_source_type_id
        FROM mtl_transaction_types
        WHERE transaction_type_id = l_transaction_type_id;

Line: 1908

cursor c1 is select distinct a.batch_id
       from wsh_new_deliveries a, wsh_delivery_details b,wsh_delivery_assignments c
       where c.delivery_id = a.delivery_id
         and c.delivery_detail_id = b.delivery_detail_id
         and b.batch_id =l_X_BATCH_ID;

Line: 1932

WSH_PICKING_BATCHES_PKG.Insert_Row(
X_ROWID => l_X_ROWID
,X_BATCH_ID => l_X_BATCH_ID
,P_CREATION_DATE => sysdate
,P_CREATED_BY => fnd_global.user_id
,P_LAST_UPDATE_DATE => sysdate
,P_LAST_UPDATED_BY => fnd_global.user_id
,P_LAST_UPDATE_LOGIN => fnd_global.user_id
--,P_batch_name_prefix => NULL
,X_NAME => l_X_NAME
,P_BACKORDERS_ONLY_FLAG => 'I'
,P_DOCUMENT_SET_ID => NULL
,P_EXISTING_RSVS_ONLY_FLAG => NULL
,P_SHIPMENT_PRIORITY_CODE => NULL
,P_SHIP_METHOD_CODE => NULL
,P_CUSTOMER_ID => NULL
,P_ORDER_HEADER_ID => l_header_id
,P_SHIP_SET_NUMBER => NULL
,P_INVENTORY_ITEM_ID => NULL
,P_ORDER_TYPE_ID => l_order_type_id
,P_FROM_REQUESTED_DATE => l_from_req_date
,P_TO_REQUESTED_DATE => l_to_req_date
,P_FROM_SCHEDULED_SHIP_DATE => NULL
,P_TO_SCHEDULED_SHIP_DATE => NULL --SYSDATE
,P_SHIP_TO_LOCATION_ID => NULL
,P_SHIP_FROM_LOCATION_ID => NULL
,P_TRIP_ID => NULL
,P_DELIVERY_ID => NULL
,P_INCLUDE_PLANNED_LINES => NULL
,P_PICK_GROUPING_RULE_ID => NULL
,P_PICK_SEQUENCE_RULE_ID => NULL
,P_AUTOCREATE_DELIVERY_FLAG => 'N'
,P_ATTRIBUTE_CATEGORY => NULL
,P_ATTRIBUTE1 => NULL
,P_ATTRIBUTE2 => NULL
,P_ATTRIBUTE3 => NULL
,P_ATTRIBUTE4 => NULL
,P_ATTRIBUTE5 => NULL
,P_ATTRIBUTE6 => NULL
,P_ATTRIBUTE7 => NULL
,P_ATTRIBUTE8 => NULL
,P_ATTRIBUTE9 => NULL
,P_ATTRIBUTE10 => NULL
,P_ATTRIBUTE11 => NULL
,P_ATTRIBUTE12 => NULL
,P_ATTRIBUTE13 => NULL
,P_ATTRIBUTE14 => NULL
,P_ATTRIBUTE15 => NULL
,P_AUTODETAIL_PR_FLAG => 'Y'
,P_CARRIER_ID => NULL
,P_TRIP_STOP_ID => NULL
,P_DEFAULT_STAGE_SUBINVENTORY => NULL
,P_DEFAULT_STAGE_LOCATOR_ID => NULL
,P_PICK_FROM_SUBINVENTORY => NULL
,P_PICK_FROM_LOCATOR_ID => NULL
,P_AUTO_PICK_CONFIRM_FLAG => 'N'
,P_DELIVERY_DETAIL_ID => NULL
,P_PROJECT_ID => NULL
,P_TASK_ID => NULL
,P_ORGANIZATION_ID => NULL -- p_org_id
,P_SHIP_CONFIRM_RULE_ID => NULL
,P_AUTOPACK_FLAG => 'N' --NULL
,P_AUTOPACK_LEVEL => 0
,P_TASK_PLANNING_FLAG => NULL
,P_NON_PICKING_FLAG => NULL
,p_regionID => NULL
,p_zoneId => NULL
,p_categoryID => NULL
,p_categorySetID => NULL
,p_acDelivCriteria => NULL
,p_RelSubinventory => NULL
,p_Append_FLAG => 'N' --NULL
,p_task_priority => NULL
,P_ALLOCATION_METHOD => 'I'
,P_CROSSDOCK_CRITERIA_ID => NULL
);

Line: 2145

      select sysdate into l_start from dual;

Line: 2147

      delete wms_transactions_temp;

Line: 2206

  SELECT ROWNUM || ' : ' ||
         mp.organization_code || ', ' ||
         msi.segment1 || ', ' ||
         mr.revision || ', ' ||
         mr.lot_number || ', ' ||
         mr.subinventory_code || ', ' ||
         mil.segment1 || '.' ||
         mil.segment2 || '.' ||
         mil.segment3 || ', ' ||
         wlpn.LICENSE_PLATE_NUMBER || ', ' ||
         mr.primary_reservation_quantity || ', ' ||
         mr.primary_uom_code || ', ' ||
         Nvl(mr.detailed_quantity,0) || ', ' ||
         mr.secondary_reservation_quantity || ', ' ||
         mr.secondary_uom_code || ', ' ||
         Nvl(mr.secondary_detailed_quantity,0) as p_text,
         mr.reservation_id as reservation_id
     FROM mtl_reservations mr, mtl_parameters mp, mtl_system_items msi, wms_license_plate_numbers wlpn, mtl_item_locations mil
     WHERE
       Nvl(mr.supply_source_type_id, 13) = 13
       AND g_demand_tbl(p_di).demand_source_type_id = mr.demand_source_type_id
       AND nvl(g_demand_tbl(p_di).demand_source_header_id,-9999) = nvl(mr.demand_source_header_id, -9999)
       AND Nvl(g_demand_tbl(p_di).demand_source_line_id, -9999) = Nvl(mr.demand_source_line_id,-9999)
       AND Nvl(g_demand_tbl(p_di).demand_source_name, '@@@###@@#') = Nvl(mr.demand_source_name,'@@@###@@#')
       AND mr.organization_id = mp.organization_id
       AND mr.organization_id = msi.organization_id
       AND mr.inventory_item_id = msi.inventory_item_id
       AND wlpn.lpn_id (+) = mr.lpn_id
       AND mil.inventory_location_id (+) = mr.locator_id;

Line: 2237

  SELECT serial_number
   FROM  mtl_serial_numbers;

Line: 2277

  SELECT DECODE(mmtt.type_code,1,'PUT :: ','PICK :: ') || mmtt.LOT_NUMBER || mmtt.FROM_SUBINVENTORY_CODE || ', ' || mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || ', ' ||
         mmtt.TRANSACTION_QUANTITY || ', ' || 'UOM' || ', ' || RESERVATION_ID || ', ' || wr1.name || ', ' || wr2.name as ptext,
    mmtt.transaction_temp_id as transaction_temp_id
  FROM wms_transactions_temp mmtt, wms_rules wr1, wms_rules wr2,
       mtl_item_locations mil
  WHERE --mmtt.move_order_line_id = g_demand_tbl(p_di).mo_line_id
        mmtt.line_type_code = 2
    AND wr1.rule_id (+) = mmtt.rule_id  --pick_rule_id
    AND wr2.rule_id (+) = mmtt.rule_id  --putaway_rule_id
    AND mil.inventory_location_id (+) = mmtt.from_locator_id;

Line: 2289

  SELECT mmtt.TRANSACTION_TEMP_ID || ', ' || mmtt.LOT_NUMBER || mmtt.FROM_SUBINVENTORY_CODE || ', ' || mmtt.TRANSFER_SUBINVENTORY || ', ' || mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || ', ' ||
         mmtt.QUANTITY || ', ' || 'UOM' || ', ' || 'RESERVATION_ID' || ', ' || wr1.name || ', ' || wr2.name as ptext,
    mmtt.transaction_temp_id as transaction_temp_id
  FROM wms_transactions_temp mmtt, wms_rules wr1, wms_rules wr2,
       mtl_item_locations mil
  WHERE --mmtt.move_order_line_id = g_demand_tbl(p_di).mo_line_id
        mmtt.line_type_code = 2
    AND wr1.rule_id (+) = mmtt.rule_id  --pick_rule_id
    AND wr2.rule_id (+) = mmtt.rule_id  --putaway_rule_id
    AND mil.inventory_location_id (+) = mmtt.from_locator_id;*/

Line: 2301

  SELECT mmtt.TRANSACTION_TEMP_ID || ', ' || mmtt.SUBINVENTORY_CODE || ', ' || mmtt.TRANSFER_SUBINVENTORY || ', ' ||
         mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || ', ' || mil2.segment1 || '.' || mil2.segment2 || '.' || mil2.segment3 || ', ' ||
         mmtt.TRANSACTION_QUANTITY || ', ' || mmtt.TRANSACTION_UOM || ', ' || mmtt.RESERVATION_ID || ', ' || wr1.name || ', ' || wr2.name as ptext,
     nvl(mtlt.LOT_NUMBER,mmtt.lot_number) || ', ' || nvl(mtlt.TRANSACTION_QUANTITY,mmtt.transaction_quantity) as ltext,
    mmtt.transaction_temp_id as transaction_temp_id, mtlt.lot_number as lot_number
  FROM mtl_material_transactions_temp mmtt, wms_rules wr1, wms_rules wr2, mtl_transaction_lots_temp mtlt,
       mtl_item_locations mil, mtl_item_locations mil2
  WHERE (p_use_mol = 'Y' AND (mmtt.move_order_line_id = g_demand_tbl(p_di).mo_line_id)
        OR (p_use_mol = 'N' AND g_demand_tbl(p_di).demand_source_type_id = mmtt.transaction_source_type_id
            AND nvl(g_demand_tbl(p_di).demand_source_header_id,-9999) = nvl(mmtt.transaction_source_id, -9999)
            AND Nvl(g_demand_tbl(p_di).demand_source_line_id, -9999) = Nvl(mmtt.trx_source_line_id,-9999)
            AND Nvl(g_demand_tbl(p_di).demand_source_name, '@@@###@@#') = Nvl(mmtt.transaction_source_name,'@@@###@@#')))
    AND wr1.rule_id (+) = mmtt.pick_rule_id
    AND wr2.rule_id (+) = mmtt.put_away_rule_id
    AND mmtt.creation_date > sysdate - 1
    AND mtlt.transaction_temp_id (+) = mmtt.transaction_temp_id
    AND mil.inventory_location_id (+) = mmtt.locator_id
    AND mil2.inventory_location_id (+) = mmtt.transfer_to_location;

Line: 2321

  SELECT msnt.fm_serial_number || ',' || msnt.to_serial_number as stext
  FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_transactions_temp mmtt
  WHERE mmtt.transaction_temp_id = p_tid
   AND msnt.transaction_temp_id = mmtt.transaction_temp_id
   AND msn.serial_number = msnt.fm_serial_number
   AND msn.inventory_item_id = mmtt.inventory_item_id;

Line: 2329

  SELECT msnt.fm_serial_number || ',' || msnt.to_serial_number as stext
  FROM mtl_serial_numbers_temp msnt, mtl_serial_numbers msn, mtl_material_transactions_temp mmtt
  WHERE mmtt.transaction_temp_id = p_tid
   AND msnt.transaction_temp_id = mmtt.transaction_temp_id
   AND msn.serial_number = msnt.fm_serial_number
   AND msn.lot_number (+) = nvl(p_lot_number,'###')
   AND msn.inventory_item_id = mmtt.inventory_item_id;

Line: 2338

  SELECT 'MTLT ' || ROWNUM || ' : ' || TRANSACTION_TEMP_ID || ', ' || LOT_NUMBER || ', ' || TRANSACTION_QUANTITY as ptext
  FROM mtl_transaction_lots_temp
  WHERE transaction_temp_id = p_tid;

Line: 2406

   EXECUTE IMMEDIATE  ' SELECT max(runid) FROM wms_ut_tab '  INTO l_r ;

Line: 2445

   ' INSERT into wms_ut_run (RUNID, TESTSET_ID, TEST_ID, START_DATE, END_DATE, USER_ID, FILE_NAME, FILE_PATH)'||
   ' VALUES ( :p_run_id, :p_testset_id, :p_test_id, :p_start_time, :p_end_time, :p_user_id, :p_file_name, :p_log_dir)'
    using p_run_id, p_testset_id, p_test_id, g_start_time, g_end_time, p_user_id, p_file_name, p_log_dir;

Line: 2508

    UPDATE wms_license_plate_numbers
    SET parent_lpn_id = lpn_id
    WHERE parent_lpn_id = l_lpn_id;

Line: 2512

    UPDATE wms_license_plate_numbers
    SET outermost_lpn_id = parent_lpn_id
    WHERE outermost_lpn_id = l_lpn_id;

Line: 2534

  SELECT status_id
  INTO l_status_id
  FROM mtl_material_statuses
  WHERE status_code = p_params(3);

Line: 2550

        UPDATE mtl_lot_numbers
        SET status_id = l_status_id
        WHERE organization_id = p_org_id
          AND lot_number = l_obj
          AND inventory_item_id = l_item_id;

Line: 2557

        UPDATE mtl_secondary_inventories
        SET status_id = l_status_id
        WHERE organization_id = p_org_id
          AND secondary_inventory_name = l_obj;

Line: 2563

        UPDATE mtl_secondary_inventories
        SET status_id = l_status_id
        WHERE organization_id = p_org_id
          AND secondary_inventory_name = l_obj;

Line: 2569

        UPDATE mtl_serial_numbers
        SET status_id = l_status_id
        WHERE current_organization_id = p_org_id
          AND serial_number = l_obj
          AND inventory_item_id = l_item_id;

Line: 2586

  SELECT line_id
  FROM MTL_TXN_REQUEST_LINES
  WHERE organization_id = p_org_id
    AND inventory_item_id = p_item_id;

Line: 2592

  SELECT transaction_temp_id
  FROM MTL_MATERIAL_TRANSACTIONS_TEMP
  WHERE organization_id = p_org_id
    AND inventory_item_id = p_item_id;

Line: 2598

  SELECT line_id
  FROM oe_order_lines_all
  WHERE inventory_item_id = p_item_id
    AND org_id = p_org_id;

Line: 2623

     SELECT sum(requested_quantity), max(delivery_detail_id)
     INTO l_req_qty, l_max_del_det_id
     FROM wsh_delivery_details
     WHERE source_line_id = s_rec.line_id;

Line: 2628

     DELETE FROM wsh_delivery_details
     WHERE  source_line_id = s_rec.line_id
      AND delivery_detail_id <> l_max_del_det_id;

Line: 2633

     UPDATE wsh_delivery_details
       SET requested_quantity = l_req_qty
      WHERE delivery_detail_id =  l_max_del_det_id;

Line: 2647

    UPDATE wsh_delivery_details set move_order_line_id = NULL, released_status = 'R'
    WHERE move_order_line_id = c_rec.line_id;

Line: 2653

  DELETE FROM mtl_txn_request_lines
  WHERE organization_id = p_org_id
    AND inventory_item_id = l_item_id;

Line: 2656

  print_debug('Deleted All MOLs');

Line: 2658

     DELETE FROM mtl_serial_numbers_temp
     WHERE transaction_temp_id = c_rec.transaction_temp_id;

Line: 2661

     DELETE FROM mtl_transaction_lots_temp
     WHERE transaction_temp_id = c_rec.transaction_temp_id;

Line: 2665

  DELETE FROM mtl_material_transactions_temp
  WHERE organization_id = p_org_id
    AND inventory_item_id = l_item_id;

Line: 2669

  DELETE FROM mtl_reservations
  WHERE organization_id = p_org_id
    AND inventory_item_id = l_item_id;

Line: 2673

  DELETE FROM mtl_serial_numbers
  WHERE current_organization_id = p_org_id
    AND inventory_item_id = l_item_id;

Line: 2677

  DELETE FROM mtl_lot_numbers
  WHERE organization_id = p_org_id
    AND inventory_item_id = l_item_id;

Line: 2681

  DELETE FROM mtl_onhand_quantities_detail
  WHERE organization_id = p_org_id
    AND inventory_item_id = l_item_id;

Line: 2684

  print_debug('Deleted ALL Onhand');

Line: 2811

  ||'   Select distinct testset_id, test_id, flow_type_id, testname'
  ||'   From wms_ut_tab'
  ||'   Where test_id = nvl(p_test_id,test_id)'
  ||'     and testset_id = nvl(p_testset_id, testset_id); '

Line: 2816

  ||'   Select distinct upper(datatype) datatype'
  ||'   From wms_ut_tab'
  ||'   Where test_id = p_tid'
  ||'     AND upper(IN_OUT) <> ''OUT'';'

Line: 2821

  ||'   Select text'
  ||'   From wms_ut_tab'
  ||'   Where test_id = p_tid'
  ||'     and upper(datatype) = p_dtype'
  ||'     AND upper(IN_OUT) <> ''OUT'';'

Line: 2861

  ||'      select organization_id'
  ||'      into l_org_id'
  ||'      from mtl_parameters'
  ||'      where organization_code = l_org_code;'

Line: 2913

         ' SELECT testset_id '		||
         ' FROM wms_ut_testset '	||
         ' WHERE testset = :l_testset '
         INTO l_testset_id
         USING l_testset;

Line: 2922

             EXECUTE IMMEDIATE  ' select wms_ut_tab_testset_s.nextval from dual' INTO l_testset_id ;

Line: 2923

             EXECUTE IMMEDIATE  ' INSERT INTO WMS_UT_TESTSET (TESTSET_ID, TESTSET)' ||
            ' VALUES (:l_testset_id, :l_testset)' USING l_testset_id, l_testset;

Line: 2940

             ' Select test_id ' 			||
             ' from wms_ut_test'			||
             ' where testset_id = :l_testset_id'	||
             '  and testname = :l_test'
              INTO l_test_id
              USING l_testset_id, l_test;

Line: 2950

               ' Update wms_ut_test' 			||
               ' Set testname = ''DEL-'' || testname'    ||
               ' WHERE test_id = l_test_id;';

Line: 2955

               ' Update wms_ut_tab' 			||
               ' Set testname = ''DEL-'' || testname' 	||
               ' WHERE test_id = l_test_id;';

Line: 2968

             EXECUTE IMMEDIATE 'select wms_ut_tab_test_s.nextval  from dual' INTO l_test_id;

Line: 2970

               ' INSERT INTO WMS_UT_TEST (testset_id, test_id, testname)' ||
               ' VALUES (:l_testset_id, :l_test_id, :l_test)' USING l_testset_id, l_test_id, l_test;

Line: 2977

       ' INSERT INTO WMS_UT_TAB ' 									||
         ' (FLOW_TYPE_ID, TESTSET_ID, TESTSET, TEST_ID, TESTNAME, TEXT, DATATYPE, IN_OUT, RUNID) ' 	||
       ' VALUES (:l_fl_type, :l_testset_id, :l_testset, :l_test_id, :l_test, :l_text, :l_pre_text, :l_in , :l_var)'
       USING l_fl_type, l_testset_id, l_testset, l_test_id, l_test, l_text, l_pre_text, 'IN', '';

Line: 3045

SELECT datatype || ' : ' || text as test_text, testset, testname, testset_id, test_id
FROM wms_ut_tab
WHERE in_out = 'IN'
  AND test_id = nvl(p_test_id,test_id)
  AND testset_id = nvl(p_testset_id, testset_id)
ORDER BY testset_id, test_id;

Line: 3093

  SELECT *
  FROM wms_ut_tab
  WHERE test_id = p_from_test_id
    AND in_out = 'IN';

Line: 3103

    ' SELECT testset_id, testset' 	||
    ' FROM wms_ut_testset' 		||
    ' WHERE testset = :l_testset'
    INTO x_new_testset_id, l_testset
    USING l_testset;

Line: 3111

       EXECUTE IMMEDIATE  'select wms_ut_tab_testset_s.nextval  from dual' INTO x_new_testset_id;

Line: 3113

        ' INSERT INTO WMS_UT_TESTSET (TESTSET_ID, TESTSET)' ||
        ' VALUES (:x_new_testset_id, :p_to_testset)'
        USING x_new_testset_id, p_to_testset;

Line: 3120

    ' SELECT testset_id' 		||
    ' FROM wms_ut_test' 		||
    ' WHERE test_id = :p_from_test_id'
      INTO x_new_testset_id
      USING p_from_test_id;

Line: 3126

  EXECUTE IMMEDIATE   'select wms_ut_tab_test_s.nextval  from dual' INTO x_new_test_id;

Line: 3128

    ' INSERT INTO WMS_UT_TEST (testset_id, test_id, testname)' ||
    ' VALUES (:x_new_testset_id, :x_new_test_id, :p_to_test)'
    USING x_new_testset_id, x_new_test_id, p_to_test;

Line: 3137

      ' INSERT INTO WMS_UT_TAB' 									||
      ' (FLOW_TYPE_ID, TESTSET_ID, TESTSET, TEST_ID, TESTNAME, TEXT, DATATYPE, IN_OUT, RUNID)' 	||
      ' VALUES (:flow_type_id, :x_new_testset_id, :l_testset, :x_new_test_id, :p_to_test, :text, :datatype, :l_in , :l_null)'
      USING test_rec.flow_type_id, x_new_testset_id, l_testset, x_new_test_id, p_to_test, test_rec.text, test_rec.datatype, 'IN', '';

Line: 3334

            ' INSERT INTO wms_ut_tab' 	||
            ' (FLOW_TYPE_ID, ' 		||
            ' TESTSET_ID,'		||
            ' TESTSET,' 		||
            ' TEST_ID,' 		||
            ' TESTNAME,' 		||
            ' TEXT,' 			||
            ' DATATYPE,'		||
            ' IN_OUT,'			||
            ' RUNID)' 			||
            '  values ' 		||
            '   (:l_flow_type_id, ' 	||
            '    :l_testset_id,' 	||
            '    :l_testset,'		||
            '    :l_test_id,'		||
            '    :l_testname,'		||
            '    :l_text,'		||
            '    :l_datatype,'		||
            '    :l_in,'		||
            '    :l_runid)'
            USING  l_flow_type_id,  l_testset_id, l_testset, l_test_id, l_testname, l_text, l_datatype,'IN', l_runid
            ;