VIEW: APPS.RCV_OPSM_LOT_SERIAL_V

Source

View Text - Preformatted

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(+)
   

View Text - HTML Formatted

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(+)