eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.QA_ERES_RCV_TRANS_INTERFACE_V

Source

View Text - Preformatted


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', NVL(POH.CLM_DOCUMENT_NUMBER, 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(+) AND NVL(POL.CLM_INFO_FLAG,'N') = 'N' AND (NVL(POL.clm_option_indicator,'B') <> 'O' OR NVL(POL.CLM_EXERCISED_FLAG,'N') = 'Y' )

View Text - HTML Formatted

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'
, NVL(POH.CLM_DOCUMENT_NUMBER
, 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(+)
AND NVL(POL.CLM_INFO_FLAG
, 'N') = 'N'
AND (NVL(POL.CLM_OPTION_INDICATOR
, 'B') <> 'O' OR NVL(POL.CLM_EXERCISED_FLAG
, 'N') = 'Y' )