SELECT RTI.INTERFACE_TRANSACTION_ID,
RTI.HEADER_INTERFACE_ID,
MIF.ITEM_NUMBER,
RTI.ITEM_ID,
RTI.GROUP_ID,
RTI.TRANSACTION_TYPE,
RTI.TRANSACTION_DATE,
RTI.PROCESSING_STATUS_CODE,
RTI.PROCESSING_MODE_CODE,
RTI.TRANSACTION_STATUS_CODE,
RTI.QUANTITY,
RTI.UNIT_OF_MEASURE,
RTI.AUTO_TRANSACT_CODE,
RTI.RECEIPT_SOURCE_CODE,
RTI.DESTINATION_TYPE_CODE,
RTI.SOURCE_DOCUMENT_CODE,
RTI.CURRENCY_CODE,
RTI.DOCUMENT_NUM,
RTI.SHIP_TO_LOCATION_ID,
HL.LOCATION_CODE,
RTI.PARENT_TRANSACTION_ID,
RTI.PO_HEADER_ID,
RTI.PO_LINE_ID,
RTI.PO_RELEASE_ID,
POR.RELEASE_NUM,
POH.SEGMENT1,
RTI.VENDOR_ID,
RTI.VENDOR_SITE_ID,
RTI.OE_ORDER_HEADER_ID,
RTI.OE_ORDER_LINE_ID,
RTI.VALIDATION_FLAG,
RTI.SUBINVENTORY,
POL.LINE_NUM,
PIE.COLUMN_NAME,
PIE.ERROR_MESSAGE
FROM APPS.RCV_TRANSACTIONS_INTERFACE RTI,
APPS.PO_INTERFACE_ERRORS PIE,
APPS.MTL_ITEM_FLEXFIELDS MIF,
APPS.PO_HEADERS_ALL POH,
APPS.PO_LINES_ALL POL,
APPS.PO_RELEASES_ALL POR,
APPS.HR_LOCATIONS_ALL HL
WHERE TO_ORGANIZATION_ID IN (‘ ‘)
AND TRANSACTION_DATE <= :I_PERIOD_END_DATE AND DESTINATION_TYPE_CODE IN (‘INVENTORY’, ‘SHOP FLOOR’) AND RTI.PO_HEADER_ID = POH.PO_HEADER_ID(+) AND RTI.PO_LINE_ID = POL.PO_LINE_ID(+) AND RTI.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND RTI.TO_ORGANIZATION_ID = MIF.ORGANIZATION_ID (+) AND RTI.ITEM_ID = MIF.INVENTORY_ITEM_ID (+) AND RTI.INTERFACE_TRANSACTION_ID = PIE.INTERFACE_TRANSACTION_ID(+) AND RTI.SHIP_TO_LOCATION_ID = HL.LOCATION_ID (+) AND TRUNC(RTI.TRANSACTION_DATE) >= TRUNC(SYSDATE -30)
ORDER BY RTI.TRANSACTION_DATE, RTI.INTERFACE_TRANSACTION_ID;