[Home] [Help]
View: RCV_OPSM_LOT_SERIAL_V
View Text
SELECT RT.SHIPMENT_HEADER_ID
,
RT.SHIPMENT_LINE_ID
,
RT.GROUP_ID
,
RLT.TRANSACTION_ID
,
MLN.PARENT_LOT_NUMBER
,
RLT.LOT_NUM
,
MP.ORGANIZATION_CODE
,
RLT.QUANTITY LOTQTY
,
RT.UOM_CODE LOTQTYUOM
,
RLT.PRIMARY_QUANTITY
,
MSIBK.PRIMARY_UOM_CODE
,
RLT.SECONDARY_QUANTITY
,
RT.SECONDARY_UOM_CODE
,
RLT.QC_GRADE
,
RLT.REASON_CODE
,
MLN.ORIGINATION_DATE
,
MLN.BEST_BY_DATE
,
MLN.RETEST_DATE
,
RLT.EXPIRATION_DATE
,
RST.SERIAL_NUM
FROM RCV_SHIPMENT_LINES RSL
,
MTL_SYSTEM_ITEMS_B_KFV MSIBK
,
MTL_LOT_NUMBERS MLN
,
MTL_PARAMETERS MP
,
RCV_LOT_TRANSACTIONS RLT
,
RCV_TRANSACTIONS RT
,
RCV_SERIAL_TRANSACTIONS RST
WHERE RT.TRANSACTION_TYPE='DELIVER'
AND RT.SHIPMENT_HEADER_ID =RSL.SHIPMENT_HEADER_ID
AND RT.SHIPMENT_LINE_ID =RSL.SHIPMENT_LINE_ID
AND RT.OE_ORDER_HEADER_ID =RSL.OE_ORDER_HEADER_ID
AND RT.OE_ORDER_LINE_ID =RSL.OE_ORDER_LINE_ID
AND RT.TRANSACTION_ID =RLT.TRANSACTION_ID
AND RLT.LOT_NUM =MLN.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID =RSL.ITEM_ID
AND MLN.ORGANIZATION_ID =RSL.TO_ORGANIZATION_ID
AND RSL.TO_ORGANIZATION_ID =MP.ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID=RSL.ITEM_ID
AND MSIBK.ORGANIZATION_ID =RSL.TO_ORGANIZATION_ID
AND RSL.SHIPMENT_LINE_ID =RLT.SHIPMENT_LINE_ID
AND RLT.LOT_NUM =RST.LOT_NUM(+)
AND RLT.SHIPMENT_LINE_ID = RST.SHIPMENT_LINE_ID(+)
AND RLT.TRANSACTION_ID =RST.TRANSACTION_ID(+)