View: QA_ERES_RCV_TRANS_INTERFACE_V

Product:	QA - Quality
Description:	electronic record view for receiving transaction integration
Implementation/DBA Data:	APPS.QA_ERES_RCV_TRANS_INTERFACE_V

View Text

SELECT RT.INTERFACE_TRANSACTION_ID
, RT.PARENT_TRANSACTION_ID
, RT.TO_ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HRORG.NAME
, RT.SHIPMENT_HEADER_ID
, RSHV.RECEIPT_NUM
, RT.TRANSACTION_TYPE
, RT.SOURCE_DOCUMENT_CODE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, 'RMA'
, OOH.ORDER_NUMBER
, RSHV.SHIPMENT_NUM) ORDER_NUMBER
, PRHA.SEGMENT1
, RT.ITEM_ID
, MSIK1.CONCATENATED_SEGMENTS
, RT.ITEM_REVISION
, RSL.ITEM_DESCRIPTION
, RT.LOCATION_ID
, HL.LOCATION_CODE
, HL.DESCRIPTION
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, MILK1.CONCATENATED_SEGMENTS
, RT.QUANTITY
, RT.UNIT_OF_MEASURE
, RT.SECONDARY_QUANTITY
, RT.SECONDARY_UNIT_OF_MEASURE
, RT.DESTINATION_TYPE_CODE
, RT.ROUTING_HEADER_ID
, RRH.ROUTING_NAME
, RT.TRANSACTION_DATE
, RT.VENDOR_ID
, PV.VENDOR_NAME
, RSL.VENDOR_LOT_NUM
, RSL.VENDOR_ITEM_NUM
, RT.CUSTOMER_ID
, RAC.CUSTOMER_NAME
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, DECODE(OEL.ITEM_IDENTIFIER_TYPE
, 'CUST'
, MCI.CUSTOMER_ITEM_NUMBER
, '')
, RSL.CUSTOMER_ITEM_NUM)
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.HAZARD_CLASS_ID
, NVL(POL.HAZARD_CLASS_ID
, MSI.HAZARD_CLASS_ID)) HAZARD_CLASS_ID
, QA_ERES_PKG.DECODE_PO_HAZARD_CLASS(RT.INTERFACE_TRANSACTION_ID)
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.UN_NUMBER_ID
, NVL(POL.UN_NUMBER_ID
, MSI.UN_NUMBER_ID)) UN_NUMBER_ID
, QA_ERES_PKG.DECODE_PO_UN_NUMBER(RT.INTERFACE_TRANSACTION_ID)
, RCV_PROJECT_GRP.GET_PROJECT_NUMBER(1.0
, 'F'
, RT.PARENT_TRANSACTION_ID)
, RCV_PROJECT_GRP.GET_TASK_NUMBER(1.0
, 'F'
, RT.PARENT_TRANSACTION_ID)
, PRA.RELEASE_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.LINE_NUMBER
, 'PO'
, POL.LINE_NUM
, RSL.LINE_NUM) LINE_NUMBER
, PLL.SHIPMENT_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY
, 'PO'
, POL.QUANTITY
, RSL.QUANTITY_SHIPPED) ORDERED_QUANTITY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDER_QUANTITY_UOM
, 'PO'
, MUM2.UNIT_OF_MEASURE
, RSL.UNIT_OF_MEASURE) ORDERED_UOM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SCHEDULE_ARRIVAL_DATE
, 'PO'
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, RSHV.EXPECTED_RECEIPT_DATE) DUE_DATE
, WE.WIP_ENTITY_NAME
, WL.LINE_CODE
, RT.WIP_OPERATION_SEQ_NUM
, RT.DEPARTMENT_CODE
, MKC.KANBAN_CARD_NUMBER
, RT.QA_COLLECTION_ID
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.LAST_UPDATE_LOGIN
, RT.CREATION_DATE
, RT.CREATED_BY
FROM RCV_TRANSACTIONS_INTERFACE RT
, MTL_PARAMETERS MP
, HR_ALL_ORGANIZATION_UNITS_TL HRORG
, RCV_SHIPMENT_HEADERS_V RSHV
, MTL_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS_KFV MSIK1
, HR_LOCATIONS_ALL_TL HL
, MTL_ITEM_LOCATIONS_KFV MILK1
, RCV_ROUTING_HEADERS RRH
, PO_RELEASES_ALL PRA
, PO_HEADERS_ALL POH
, OE_ORDER_HEADERS OOH
, QA_CUSTOMERS_LOV_V RAC
, PO_LINES_ALL POL
, OE_ORDER_LINES_ALL OEL
, RCV_SHIPMENT_LINES RSL
, MTL_UNITS_OF_MEASURE MUM2
, PO_LINE_LOCATIONS_ALL PLL
, PO_REQUISITION_LINES_ALL PRLA
, PO_REQUISITION_HEADERS_ALL PRHA
, PO_DISTRIBUTIONS_ALL PDA
, WIP_ENTITIES WE
, WIP_LINES WL
, MTL_KANBAN_CARDS MKC
, PO_VENDORS PV
, MTL_CUSTOMER_ITEMS MCI
WHERE RT.TO_ORGANIZATION_ID = MP.ORGANIZATION_ID(+)
AND USERENV('LANG') = HRORG.LANGUAGE(+)
AND RT.TO_ORGANIZATION_ID = HRORG.ORGANIZATION_ID(+)
AND RT.SHIPMENT_HEADER_ID = RSHV.SHIPMENT_HEADER_ID(+)
AND RT.ITEM_ID = MSIK1.INVENTORY_ITEM_ID(+)
AND RT.TO_ORGANIZATION_ID = MSIK1.ORGANIZATION_ID(+)
AND RT.LOCATION_ID = HL.LOCATION_ID(+)
AND USERENV('LANG') = HL.LANGUAGE(+)
AND RT.LOCATOR_ID = MILK1.INVENTORY_LOCATION_ID(+)
AND RT.TO_ORGANIZATION_ID = MILK1.ORGANIZATION_ID(+)
AND RT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID(+)
AND RT.PO_RELEASE_ID = PRA.PO_RELEASE_ID(+)
AND RT.PO_HEADER_ID = POH.PO_HEADER_ID(+)
AND RT.OE_ORDER_HEADER_ID = OOH.HEADER_ID(+)
AND RT.CUSTOMER_ID = RAC.CUSTOMER_ID(+)
AND RT.TO_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND RT.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND RT.PO_LINE_ID = POL.PO_LINE_ID(+)
AND RT.OE_ORDER_LINE_ID = OEL.LINE_ID(+)
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+)
AND POL.UNIT_MEAS_LOOKUP_CODE = MUM2.UNIT_OF_MEASURE(+)
AND RT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)
AND RT.REQUISITION_LINE_ID = PRLA.REQUISITION_LINE_ID(+)
AND PRLA.REQUISITION_HEADER_ID = PRHA.REQUISITION_HEADER_ID(+)
AND RT.PO_DISTRIBUTION_ID = PDA.PO_DISTRIBUTION_ID(+)
AND RT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID(+)
AND RT.WIP_LINE_ID = WL.LINE_ID(+)
AND RT.TO_ORGANIZATION_ID = WL.ORGANIZATION_ID(+)
AND PDA.KANBAN_CARD_ID = MKC.KANBAN_CARD_ID(+)
AND RT.VENDOR_ID = PV.VENDOR_ID(+)
AND OEL.ORDERED_ITEM_ID = MCI.CUSTOMER_ITEM_ID(+)

Columns