[Home] [Help]
View: MTL_OPSM_LOT_EXPORT_V
View Text
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'
)