eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WIP_SERIAL_NUMBER_CLEANUP SQL Statements

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

Line: 4


  procedure fetch_and_delete(
    p_grp_id  in     number,
    p_status  in     number,
    p_serials in out nocopy mtl_serial_numbers_rec) is

    i number := 0;

Line: 14


    select
    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,
    ATTRIBUTE_CATEGORY,
    ATTRIBUTE1,
    ATTRIBUTE2,
    ATTRIBUTE3,
    ATTRIBUTE4,
    ATTRIBUTE5,
    ATTRIBUTE6,
    ATTRIBUTE7,
    ATTRIBUTE8,
    ATTRIBUTE9,
    ATTRIBUTE10,
    ATTRIBUTE11,
    ATTRIBUTE12,
    ATTRIBUTE13,
    ATTRIBUTE14,
    ATTRIBUTE15,
    GROUP_MARK_ID,
    LINE_MARK_ID,
    LOT_LINE_MARK_ID
    from mtl_serial_numbers
    where group_mark_id = c_grp_id
    and current_status = c_status;

Line: 96


      p_serials.LAST_UPDATE_DATE(i) := serial_rec.LAST_UPDATE_DATE;

Line: 97


      p_serials.LAST_UPDATED_BY(i) := serial_rec.LAST_UPDATED_BY;

Line: 100


      p_serials.LAST_UPDATE_LOGIN(i) := serial_rec.LAST_UPDATE_LOGIN;

Line: 104


      p_serials.PROGRAM_UPDATE_DATE(i) := serial_rec.PROGRAM_UPDATE_DATE;

Line: 152


      delete mtl_serial_numbers
      where group_mark_id = p_grp_id
      and current_status = p_status;

Line: 156


  end fetch_and_delete;

Line: 158


  procedure insert_rows(
    p_serials in mtl_serial_numbers_rec) is
    i number := 1;

Line: 163


      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,
        ATTRIBUTE_CATEGORY,
        ATTRIBUTE1,
        ATTRIBUTE2,
        ATTRIBUTE3,
        ATTRIBUTE4,
        ATTRIBUTE5,
        ATTRIBUTE6,
        ATTRIBUTE7,
        ATTRIBUTE8,
        ATTRIBUTE9,
        ATTRIBUTE10,
        ATTRIBUTE11,
        ATTRIBUTE12,
        ATTRIBUTE13,
        ATTRIBUTE14,
        ATTRIBUTE15,
        GROUP_MARK_ID,
        LINE_MARK_ID,
        LOT_LINE_MARK_ID
      ) values (
        p_serials.INVENTORY_ITEM_ID(i),
        p_serials.SERIAL_NUMBER(i),
        p_serials.LAST_UPDATE_DATE(i),
        p_serials.LAST_UPDATED_BY(i),
        p_serials.CREATION_DATE(i),
        p_serials.CREATED_BY(i),
        p_serials.LAST_UPDATE_LOGIN(i),
        p_serials.REQUEST_ID(i),
        p_serials.PROGRAM_APPLICATION_ID(i),
        p_serials.PROGRAM_ID(i),
        p_serials.PROGRAM_UPDATE_DATE(i),
        p_serials.INITIALIZATION_DATE(i),
        p_serials.COMPLETION_DATE(i),
        p_serials.SHIP_DATE(i),
        p_serials.CURRENT_STATUS(i),
        p_serials.REVISION(i),
        p_serials.LOT_NUMBER(i),
        p_serials.FIXED_ASSET_TAG(i),
        p_serials.RESERVED_ORDER_ID(i),
        p_serials.PARENT_ITEM_ID(i),
        p_serials.PARENT_SERIAL_NUMBER(i),
        p_serials.ORIGINAL_WIP_ENTITY_ID(i),
        p_serials.ORIGINAL_UNIT_VENDOR_ID(i),
        p_serials.VENDOR_SERIAL_NUMBER(i),
        p_serials.VENDOR_LOT_NUMBER(i),
        p_serials.LAST_TXN_SOURCE_TYPE_ID(i),
        p_serials.LAST_TRANSACTION_ID(i),
        p_serials.LAST_RECEIPT_ISSUE_TYPE(i),
        p_serials.LAST_TXN_SOURCE_NAME(i),
        p_serials.LAST_TXN_SOURCE_ID(i),
        p_serials.DESCRIPTIVE_TEXT(i),
        p_serials.CURRENT_SUBINVENTORY_CODE(i),
        p_serials.CURRENT_LOCATOR_ID(i),
        p_serials.CURRENT_ORGANIZATION_ID(i),
        p_serials.ATTRIBUTE_CATEGORY(i),
        p_serials.ATTRIBUTE1(i),
        p_serials.ATTRIBUTE2(i),
        p_serials.ATTRIBUTE3(i),
        p_serials.ATTRIBUTE4(i),
        p_serials.ATTRIBUTE5(i),
        p_serials.ATTRIBUTE6(i),
        p_serials.ATTRIBUTE7(i),
        p_serials.ATTRIBUTE8(i),
        p_serials.ATTRIBUTE9(i),
        p_serials.ATTRIBUTE10(i),
        p_serials.ATTRIBUTE11(i),
        p_serials.ATTRIBUTE12(i),
        p_serials.ATTRIBUTE13(i),
        p_serials.ATTRIBUTE14(i),
        p_serials.ATTRIBUTE15(i),
        p_serials.GROUP_MARK_ID(i),
        p_serials.LINE_MARK_ID(i),
        p_serials.LOT_LINE_MARK_ID(i)
      );

Line: 275


  end insert_rows;

Line: 283


    select
    SERIAL_NUMBER,
    INVENTORY_ITEM_ID,
    GROUP_MARK_ID,
    LINE_MARK_ID,
    LOT_LINE_MARK_ID
    from mtl_serial_numbers
    where group_mark_id = c_hdr_id;

Line: 322


      update mtl_serial_numbers
      set group_mark_id = null,
          line_mark_id = null,
          lot_line_mark_id = null
      where group_mark_id = p_hdr_id;

Line: 339


    select 'x'
    from mtl_serial_numbers
    where inventory_item_id = c_item_id
    and serial_number = c_serial
    for update nowait;

Line: 353


         serial numbers.  If cannot update, then some other session has
         locked this serial number => error out. */
      open lock_rows(
        c_item_id => p_serials.INVENTORY_ITEM_ID(i),
        c_serial  => p_serials.SERIAL_NUMBER(i));

Line: 361


      update mtl_serial_numbers
      set group_mark_id = p_serials.GROUP_MARK_ID(i),
          line_mark_id = p_serials.LINE_MARK_ID(i),
          lot_line_mark_id = p_serials.LOT_LINE_MARK_ID(i)
      where inventory_item_id = p_serials.INVENTORY_ITEM_ID(i)
      and serial_number = p_serials.SERIAL_NUMBER(i);