View: RCV_OPSM_LINE_V

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.RCV_OPSM_LINE_V

View Text

SELECT RSL.SHIPMENT_HEADER_ID
, RSL.SHIPMENT_LINE_ID
, RSL.ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS ITEM
, MP.ORGANIZATION_CODE
, MP.ORGANIZATION_ID
, RSL.ITEM_DESCRIPTION
, DECODE((SELECT UPPER(MCR.CROSS_REFERENCE)
FROM MTL_CROSS_REFERENCES_VL MCR
WHERE MCR.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID+0
AND MCR.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID +0
AND TO_CHAR(MCR.LAST_UPDATE_DATE
, 'DD/MM/YYYY-HH24.MM.SS')= (SELECT (MAX(TO_CHAR(MCR1.LAST_UPDATE_DATE
, 'DD/MM/YYYY-HH24.MM.SS')))
FROM MTL_CROSS_REFERENCES_VL MCR1
WHERE MCR1.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID+0
AND MCR1.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID +0
AND MCR1.CROSS_REFERENCE_TYPE ='OPSM INTEGRATED' ))
, 'NO'
, 0
, 1) OPSM_INTEGRATED_FLAG
, (SELECT MCR.ATTRIBUTE1
FROM MTL_CROSS_REFERENCES_VL MCR
WHERE MCR.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID+0
AND MCR.ORGANIZATION_ID =(SELECT MASTER_ORGANIZATION_ID
FROM MTL_PARAMETERS
WHERE ORGANIZATION_ID=MSIBK.ORGANIZATION_ID+0)
AND MCR.CROSS_REFERENCE_TYPE ='OPSM INTEGRATED'
AND MCR.CROSS_REFERENCE = 'YES')SERIAL_TYPE
, RSL.QUANTITY_SHIPPED
, RSL.QUANTITY_RECEIVED
, RSL.UNIT_OF_MEASURE
, RSL.SHIPMENT_LINE_STATUS_CODE
, RSL.BAR_CODE_LABEL
, RSL.QC_GRADE
, RSL.ASN_LINE_FLAG
, RSL.DESTINATION_TYPE_CODE
, MSIBK.LOT_CONTROL_CODE
, MSIBK.SERIAL_NUMBER_CONTROL_CODE
, RSL.OE_ORDER_HEADER_ID
, OOHA.ORDER_NUMBER
, OTTT.NAME ORDER_TYPE
, OOLA.LINE_NUMBER
, OOLA.SOLD_TO_ORG_ID SOLDBYORGID
, OOHA.SOLD_TO_CONTACT_ID SOLDBYCONTACTID
, OOLA.SHIP_TO_ORG_ID SHIPFROMORGID
, OOLA.SHIP_TO_CONTACT_ID SHIPFROMCONTACTID
, OOLA.INVOICE_TO_ORG_ID BILLEDBYORGID
, OOLA.INVOICE_TO_CONTACT_ID BILLEDBYCONTACTID
, RT.PARENT_TRANSACTION_ID
, RT.TRANSACTION_ID
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.QUANTITY TRANSACTION_QTY
, RT.UOM_CODE TRANSACTION_UOM
, RT.PRIMARY_QUANTITY
, MSIBK.PRIMARY_UOM_CODE
, RT.SECONDARY_QUANTITY
, RT.SECONDARY_UOM_CODE
, 'RMA RECEIPT'
, RT.DESTINATION_TYPE_CODE TRANS_DESTINATION_TYPE_CODE
, RT.GROUP_ID
FROM RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_PARAMETERS MP
, OE_ORDER_HEADERS_ALL OOHA
, OE_ORDER_LINES_ALL OOLA
, OE_TRANSACTION_TYPES_TL OTTT
, FND_LANGUAGES FL
WHERE MSIBK.INVENTORY_ITEM_ID=RSL.ITEM_ID
AND MSIBK.ORGANIZATION_ID =RSL.TO_ORGANIZATION_ID
AND MSIBK.ORGANIZATION_ID =MP.ORGANIZATION_ID
AND RSL.SHIPMENT_HEADER_ID =RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID =RT.SHIPMENT_LINE_ID
AND RT.TRANSACTION_TYPE ='DELIVER'
AND RSL.OE_ORDER_HEADER_ID =OOLA.HEADER_ID
AND RSL.OE_ORDER_LINE_ID =OOLA.LINE_ID
AND OOHA.HEADER_ID =OOLA.HEADER_ID
AND OOHA.ORDER_TYPE_ID =OTTT.TRANSACTION_TYPE_ID
AND FL.INSTALLED_FLAG ='B'
AND OTTT.LANGUAGE = LANGUAGE_CODE