VIEW: APPS.MTL_OPSM_LOT_EXPORT_V

Source

View Text - Preformatted

SELECT msibk.inventory_item_id ,
    msibk.concatenated_segments ,
    msibk.description ,
    msibk.primary_uom_code ,
    msibk.lot_control_code ,
    msibk.serial_number_control_code ,
    msibk.organization_id ,
    mp.organization_code ,
    mln.age ,
    mln.best_by_date ,
    mln.change_date ,
    mln.expiration_date ,
    mln.grade_code ,
    mln.length ,
    mln.length_uom ,
    mln.description ,
    mln.lot_number ,
    mln.status_id ,
    mln.maturity_date ,
    mln.origination_date ,
    mln.place_of_origin ,
    mln.recycled_content ,
    mln.retest_date ,
    mln.thickness ,
    mln.thickness_uom ,
    mln.volume ,
    mln.volume_uom ,
    mln.width ,
    mln.width_uom ,
    mln.expiration_action_code ,
    mln.expiration_action_date ,
    mln.parent_lot_number ,
    mln.reservable_type ,
    mln.origination_type ,
    mln.creation_date ,
    mln.created_by ,
    mln.last_update_date ,
    mln.last_updated_by ,
    mln.last_update_login
FROM mtl_lot_numbers mln ,
    mtl_system_items_b_kfv msibk ,
    mtl_cross_references_vl mcr ,
    mtl_parameters mp
WHERE msibk.inventory_item_id = mcr.inventory_item_id
  AND mln.inventory_item_id     = msibk.inventory_item_id
  AND mln.organization_id       = msibk.organization_id
  AND mp.organization_id        = msibk.organization_id
  AND mcr.cross_reference_type  = 'OPSM INTEGRATED'
  AND mcr.cross_reference       = 'YES'
  AND mp.master_organization_id = mcr.organization_id
  AND mp.opsm_enabled_flag      = 'Y'
  AND msibk.lot_control_code    = 2
  AND NOT EXISTS
    (SELECT 'Y'
    FROM mtl_cross_references_vl
    WHERE cross_reference_type = 'OPSM INTEGRATED'
    AND organization_id        = msibk.organization_id
    AND inventory_item_id      = msibk.inventory_item_id
    AND UPPER(cross_reference) = 'NO'
    )
   

View Text - HTML Formatted

SELECT MSIBK.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, MSIBK.PRIMARY_UOM_CODE
, MSIBK.LOT_CONTROL_CODE
, MSIBK.SERIAL_NUMBER_CONTROL_CODE
, MSIBK.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, MLN.AGE
, MLN.BEST_BY_DATE
, MLN.CHANGE_DATE
, MLN.EXPIRATION_DATE
, MLN.GRADE_CODE
, MLN.LENGTH
, MLN.LENGTH_UOM
, MLN.DESCRIPTION
, MLN.LOT_NUMBER
, MLN.STATUS_ID
, MLN.MATURITY_DATE
, MLN.ORIGINATION_DATE
, MLN.PLACE_OF_ORIGIN
, MLN.RECYCLED_CONTENT
, MLN.RETEST_DATE
, MLN.THICKNESS
, MLN.THICKNESS_UOM
, MLN.VOLUME
, MLN.VOLUME_UOM
, MLN.WIDTH
, MLN.WIDTH_UOM
, MLN.EXPIRATION_ACTION_CODE
, MLN.EXPIRATION_ACTION_DATE
, MLN.PARENT_LOT_NUMBER
, MLN.RESERVABLE_TYPE
, MLN.ORIGINATION_TYPE
, MLN.CREATION_DATE
, MLN.CREATED_BY
, MLN.LAST_UPDATE_DATE
, MLN.LAST_UPDATED_BY
, MLN.LAST_UPDATE_LOGIN FROM MTL_LOT_NUMBERS MLN
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_CROSS_REFERENCES_VL MCR
, MTL_PARAMETERS MP WHERE MSIBK.INVENTORY_ITEM_ID = MCR.INVENTORY_ITEM_ID
AND MLN.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND MLN.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID
AND MCR.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
AND MCR.CROSS_REFERENCE = 'YES'
AND MP.MASTER_ORGANIZATION_ID = MCR.ORGANIZATION_ID
AND MP.OPSM_ENABLED_FLAG = 'Y'
AND MSIBK.LOT_CONTROL_CODE = 2
AND NOT EXISTS (SELECT 'Y'
FROM MTL_CROSS_REFERENCES_VL
WHERE CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
AND ORGANIZATION_ID = MSIBK.ORGANIZATION_ID
AND INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND UPPER(CROSS_REFERENCE) = 'NO' )