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.RCV_VIEW_INTERFACE_V

Source

View Text - Preformatted


SELECT RTI.ROWID, RTI.CREATION_DATE, RTI.CREATED_BY, RTI.LAST_UPDATE_LOGIN, RTI.LAST_UPDATE_DATE, RTI.LAST_UPDATED_BY, RTI.INTERFACE_TRANSACTION_ID, RTI.TRANSACTION_TYPE, RTI.TRANSACTION_DATE, RTI.QUANTITY, RTI.UNIT_OF_MEASURE, RTI.SHIPMENT_HEADER_ID, RTI.SHIPMENT_LINE_ID, RTI.SOURCE_DOCUMENT_CODE, RTI.DESTINATION_TYPE_CODE, RTI.PRIMARY_UNIT_OF_MEASURE, RTI.PARENT_TRANSACTION_ID, RTI.PO_HEADER_ID, RTI.PO_RELEASE_ID, RTI.PO_LINE_ID, RTI.PO_LINE_LOCATION_ID, RTI.PO_DISTRIBUTION_ID, RTI.PO_REVISION_NUM, RTI.REQUISITION_LINE_ID, RTI.PO_UNIT_PRICE, RTI.CURRENCY_CODE, RTI.CURRENCY_CONVERSION_TYPE, RTI.CURRENCY_CONVERSION_RATE, RTI.CURRENCY_CONVERSION_DATE, RTI.ROUTING_HEADER_ID, RTI.ROUTING_STEP_ID, RTI.DELIVER_TO_PERSON_ID, RTI.DELIVER_TO_LOCATION_ID, RTI.VENDOR_ID, RTI.VENDOR_SITE_ID, RTI.TO_ORGANIZATION_ID, RTI.SUBINVENTORY, RTI.LOCATOR_ID, RTI.LOCATION_ID, RTI.RECEIPT_EXCEPTION_FLAG, RTI.INSPECTION_STATUS_CODE, RTI.VENDOR_LOT_NUM, RTI.COMMENTS, RTI.ATTRIBUTE_CATEGORY, RTI.ATTRIBUTE1, RTI.ATTRIBUTE2, RTI.ATTRIBUTE3, RTI.ATTRIBUTE4, RTI.ATTRIBUTE5, RTI.ATTRIBUTE6, RTI.ATTRIBUTE7, RTI.ATTRIBUTE8, RTI.ATTRIBUTE9, RTI.ATTRIBUTE10, RTI.ATTRIBUTE11, RTI.ATTRIBUTE12, RTI.ATTRIBUTE13, RTI.ATTRIBUTE14, RTI.ATTRIBUTE15, RTI.REQ_DISTRIBUTION_ID, RTI.REASON_ID, RTI.DESTINATION_CONTEXT, RTI.SOURCE_DOC_UNIT_OF_MEASURE, PLC1.DISPLAYED_FIELD TRANSACTION_TYPE_DSP, RSL.GOVERNMENT_CONTEXT, RSL.USSGL_TRANSACTION_CODE, PRL.REQUISITION_HEADER_ID, RSH.RECEIPT_NUM, RSH.SHIPMENT_NUM, RSL.LINE_NUM, RSL.PACKING_SLIP, RTI.ITEM_ID, RTI.ITEM_DESCRIPTION, RTI.CATEGORY_ID, RTI.ITEM_REVISION, RTI.RECEIPT_SOURCE_CODE, MSI.ALLOWED_UNITS_LOOKUP_CODE, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CC, MSI.REVISION_QTY_CONTROL_CODE REVISION_QTY_CONTROL_CODE, MSI.LOT_CONTROL_CODE, NVL(MSI.SHELF_LIFE_CODE, 1) SHELF_LIFE, NVL(MSI.SHELF_LIFE_DAYS, 0) SHELF_DAYS, MSI.ORGANIZATION_ID ITEM_ORGANIZATION_ID, PLC2.DISPLAYED_FIELD INSPECTION_STATUS_DSP, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', NULL, 'PO', PH.SEGMENT1, RSH.SHIPMENT_NUM) PO_NUMBER, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', NULL, PL.LINE_NUM) PO_LINE_NUMBER, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', OEL.SHIPMENT_NUMBER, PLL.SHIPMENT_NUM) PO_SHIPMENT_NUMBER, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', NULL, PR.RELEASE_NUM) PO_RELEASE_NUM, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', NULL, NVL(PL.HAZARD_CLASS_ID, MSI.HAZARD_CLASS_ID)) HAZARD_CLASS_ID, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', NULL, NVL(PL.UN_NUMBER_ID, MSI.UN_NUMBER_ID)) UN_NUMBER_ID, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', NULL, 'PO', POV.VENDOR_NAME, OOD.ORGANIZATION_NAME) VENDOR, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', OETL.NAME, PLC4.DISPLAYED_FIELD) ORDER_TYPE, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', NULL, PH.NOTE_TO_RECEIVER) NOTE_TO_RECEIVER, RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER, PLC3.DISPLAYED_FIELD DESTINATION_TYPE_DSP, HRL.LOCATION_CODE, RSH.BILL_OF_LADING, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER, RSH.WAYBILL_AIRBILL_NUM, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', NULL, PL.VENDOR_PRODUCT_NUM) VENDOR_ITEM_NUMBER, RRH.ROUTING_NAME, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', OEL.ORDERED_QUANTITY, 'PO', PLL.QUANTITY, RSL.QUANTITY_SHIPPED) ORDERED_QTY, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', OEL.ORDER_QUANTITY_UOM, 'PO', MUM3.UNIT_OF_MEASURE, RSL.UNIT_OF_MEASURE) ORDERED_UOM, TO_DATE(DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', NULL, NVL(PLL.NEED_BY_DATE, PLL.PROMISED_DATE)),'DD-MON-YYYY') DUE_DATE_VENDOR, RSH.EXPECTED_RECEIPT_DATE DUE_DATE_INTERNAL, MTR.REASON_NAME REASON_CODE, HRE.FULL_NAME, RTI.WIP_ENTITY_ID, RTI.WIP_LINE_ID, RTI.WIP_REPETITIVE_SCHEDULE_ID, RTI.WIP_OPERATION_SEQ_NUM, RTI.WIP_RESOURCE_SEQ_NUM, RTI.DEPARTMENT_CODE, RTI.BOM_RESOURCE_ID, PLL.QTY_RCV_EXCEPTION_CODE, RTI.MOVEMENT_ID, RTI.INSPECTION_QUALITY_CODE, RTI.SUBSTITUTE_UNORDERED_CODE, PLC5.DISPLAYED_FIELD, PLC6.DISPLAYED_FIELD, RTI.QA_COLLECTION_ID, RTI.EMPLOYEE_ID, RTI.OE_ORDER_HEADER_ID, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', OEH.ORDER_NUMBER, NULL) OE_ORDER_NUM, RTI.OE_ORDER_LINE_ID, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', OEL.LINE_NUMBER, NULL) OE_ORDER_LINE_NUM, RTI.CUSTOMER_ID, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', OEC.NAME , NULL) CUSTOMER, RTI.CUSTOMER_SITE_ID, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', OEL.SCHEDULE_ARRIVAL_DATE, NULL) DUE_DATE_CUSTOMER, RTI.SECONDARY_QUANTITY, RTI.SECONDARY_UNIT_OF_MEASURE, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', OEL.ORDERED_QUANTITY2, 'PO', PLL.SECONDARY_QUANTITY, RSL.SECONDARY_QUANTITY_SHIPPED) SECONDARY_ORDERED_QTY, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'RMA', MUM5.UNIT_OF_MEASURE, 'PO', MUM4.UNIT_OF_MEASURE, RSL.SECONDARY_UNIT_OF_MEASURE) SECONDARY_ORDERED_UOM, RTI.QC_GRADE QC_GRADE, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'PO', PLL.MATCHING_BASIS,NULL) MATCHING_BASIS, RTI.AMOUNT, FCV.NAME, PJ.NAME, PJA.JOB_DESCRIPTION, DECODE(RTI.ORG_ID, NULL, DECODE(RTI.SOURCE_DOCUMENT_CODE, 'PO', PH.ORG_ID, 'REQ', PRL.ORG_ID, 'RMA', OEH.ORG_ID, NULL), RTI.ORG_ID) ORG_ID, RTI.LCM_SHIPMENT_LINE_ID, RTI.UNIT_LANDED_COST, RTI.PROCESSING_STATUS_CODE FROM RCV_TRANSACTIONS_INTERFACE RTI, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, PO_REQUISITION_LINES PRL, MTL_SYSTEM_ITEMS MSI, MTL_UNITS_OF_MEASURE MUM1, MTL_UNITS_OF_MEASURE MUM2, MTL_UNITS_OF_MEASURE MUM3, MTL_UNITS_OF_MEASURE MUM4, MTL_UNITS_OF_MEASURE MUM5, PO_LOOKUP_CODES PLC1, PO_LOOKUP_CODES PLC2, PO_LOOKUP_CODES PLC3, PO_LOOKUP_CODES PLC4, PO_LOOKUP_CODES PLC5, PO_LOOKUP_CODES PLC6, OE_SOLD_TO_ORGS_V OEC, OE_ORDER_LINES_ALL OEL, OE_ORDER_HEADERS_ALL OEH, OE_TRANSACTION_TYPES_TL OETL, OE_TRANSACTION_TYPES_ALL OET, PO_HEADERS_ALL PH, PO_LINES_ALL PL, PO_LINE_LOCATIONS PLL, PO_RELEASES_ALL PR, PO_VENDORS POV, HR_LOCATIONS_ALL_TL HRL, MTL_TRANSACTION_REASONS MTR, RCV_ROUTING_HEADERS RRH, PER_ALL_PEOPLE_F HRE, ORG_ORGANIZATION_DEFINITIONS OOD, FND_CURRENCIES_VL FCV, PER_JOBS PJ, PO_JOB_ASSOCIATIONS PJA WHERE (RTI.TRANSACTION_TYPE = PLC1.LOOKUP_CODE AND PLC1.LOOKUP_TYPE = 'RCV TRANSACTION TYPE') AND (RTI.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+)) AND (RTI.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+)) AND (RTI.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)) AND (MSI.INVENTORY_ITEM_ID(+) = RTI.ITEM_ID AND NVL(MSI.ORGANIZATION_ID, RTI.TO_ORGANIZATION_ID) = RTI.TO_ORGANIZATION_ID AND MSI.PRIMARY_UNIT_OF_MEASURE = MUM1.UNIT_OF_MEASURE(+)) AND (RTI.UNIT_OF_MEASURE = MUM2.UNIT_OF_MEASURE(+)) AND (PLC2.LOOKUP_TYPE(+) = 'INSPECTION STATUS' AND PLC2.LOOKUP_CODE(+) = RTI.INSPECTION_STATUS_CODE) AND OEL.LINE_ID(+) = RTI.OE_ORDER_LINE_ID AND OEH.HEADER_ID(+) = RTI.OE_ORDER_HEADER_ID AND OEC.CUSTOMER_ID(+) = RSH.CUSTOMER_ID AND OET.TRANSACTION_TYPE_ID (+) = OEH.ORDER_TYPE_ID AND OET.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+) AND OETL.LANGUAGE(+) = USERENV('LANG') AND OET.TRANSACTION_TYPE_CODE(+) = 'ORDER' AND PH.PO_HEADER_ID(+) = RTI.PO_HEADER_ID AND (PL.PO_LINE_ID (+) = RTI.PO_LINE_ID) AND (PLL.LINE_LOCATION_ID (+) = RTI.PO_LINE_LOCATION_ID) AND (PR.PO_RELEASE_ID(+) = RTI.PO_RELEASE_ID) AND POV.VENDOR_ID(+) = RSH.VENDOR_ID AND PLC3.LOOKUP_TYPE(+) = 'RCV DESTINATION TYPE' AND PLC3.LOOKUP_CODE(+) = RTI.DESTINATION_TYPE_CODE AND HRL.LOCATION_ID(+) = NVL(RTI.DELIVER_TO_LOCATION_ID, RTI.LOCATION_ID) AND HRL.LANGUAGE(+) = USERENV('LANG') AND RRH.ROUTING_HEADER_ID(+) = RTI.ROUTING_HEADER_ID AND MUM3.UNIT_OF_MEASURE(+) = PL.UNIT_MEAS_LOOKUP_CODE AND MUM4.UNIT_OF_MEASURE(+) = PLL.SECONDARY_UNIT_OF_MEASURE AND MUM5.UOM_CODE(+) =OEL.ORDERED_QUANTITY_UOM2 AND MTR.REASON_ID (+) = RTI.REASON_ID AND HRE.PERSON_ID (+) = RTI.DELIVER_TO_PERSON_ID AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE (+) AND HRE.EFFECTIVE_END_DATE (+) AND DECODE(HR_SECURITY.VIEW_ALL,'Y','TRUE', HR_SECURITY.SHOW_PERSON (HRE.PERSON_ID (+), HRE.CURRENT_APPLICANT_FLAG (+), HRE.CURRENT_EMPLOYEE_FLAG (+), HRE.CURRENT_NPW_FLAG (+),HRE.EMPLOYEE_NUMBER (+), HRE.APPLICANT_NUMBER (+), HRE.NPW_NUMBER(+))) = 'TRUE' AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', HRE.BUSINESS_GROUP_ID (+), HR_GENERAL.GET_BUSINESS_GROUP_ID) = HRE.BUSINESS_GROUP_ID (+) AND OOD.ORGANIZATION_ID(+) = RTI.FROM_ORGANIZATION_ID AND DECODE(RTI.SOURCE_DOCUMENT_CODE, 'PO', 'PO TYPE', 'SHIPMENT SOURCE TYPE') = PLC4.LOOKUP_TYPE AND DECODE(RTI.TRANSACTION_TYPE, 'UNORDERED', 'STANDARD', DECODE(RTI.SOURCE_DOCUMENT_CODE, 'PO', PH.TYPE_LOOKUP_CODE, RSH.RECEIPT_SOURCE_CODE)) = PLC4.LOOKUP_CODE AND PLC5.LOOKUP_TYPE = 'TRANSACTION STATUS' AND PLC5.LOOKUP_CODE = RTI.TRANSACTION_STATUS_CODE AND PLC6.LOOKUP_TYPE = 'RCV PROCESSING MODE' AND PLC6.LOOKUP_CODE = RTI.PROCESSING_MODE_CODE AND FCV.CURRENCY_CODE (+) = RTI.CURRENCY_CODE AND PJ.JOB_ID (+) = RTI.JOB_ID AND PJA.JOB_ID (+) = RTI.JOB_ID AND NVL(PH.ORG_ID, -99) = NVL(PLL.ORG_ID, -99)

View Text - HTML Formatted

SELECT RTI.ROWID
, RTI.CREATION_DATE
, RTI.CREATED_BY
, RTI.LAST_UPDATE_LOGIN
, RTI.LAST_UPDATE_DATE
, RTI.LAST_UPDATED_BY
, RTI.INTERFACE_TRANSACTION_ID
, RTI.TRANSACTION_TYPE
, RTI.TRANSACTION_DATE
, RTI.QUANTITY
, RTI.UNIT_OF_MEASURE
, RTI.SHIPMENT_HEADER_ID
, RTI.SHIPMENT_LINE_ID
, RTI.SOURCE_DOCUMENT_CODE
, RTI.DESTINATION_TYPE_CODE
, RTI.PRIMARY_UNIT_OF_MEASURE
, RTI.PARENT_TRANSACTION_ID
, RTI.PO_HEADER_ID
, RTI.PO_RELEASE_ID
, RTI.PO_LINE_ID
, RTI.PO_LINE_LOCATION_ID
, RTI.PO_DISTRIBUTION_ID
, RTI.PO_REVISION_NUM
, RTI.REQUISITION_LINE_ID
, RTI.PO_UNIT_PRICE
, RTI.CURRENCY_CODE
, RTI.CURRENCY_CONVERSION_TYPE
, RTI.CURRENCY_CONVERSION_RATE
, RTI.CURRENCY_CONVERSION_DATE
, RTI.ROUTING_HEADER_ID
, RTI.ROUTING_STEP_ID
, RTI.DELIVER_TO_PERSON_ID
, RTI.DELIVER_TO_LOCATION_ID
, RTI.VENDOR_ID
, RTI.VENDOR_SITE_ID
, RTI.TO_ORGANIZATION_ID
, RTI.SUBINVENTORY
, RTI.LOCATOR_ID
, RTI.LOCATION_ID
, RTI.RECEIPT_EXCEPTION_FLAG
, RTI.INSPECTION_STATUS_CODE
, RTI.VENDOR_LOT_NUM
, RTI.COMMENTS
, RTI.ATTRIBUTE_CATEGORY
, RTI.ATTRIBUTE1
, RTI.ATTRIBUTE2
, RTI.ATTRIBUTE3
, RTI.ATTRIBUTE4
, RTI.ATTRIBUTE5
, RTI.ATTRIBUTE6
, RTI.ATTRIBUTE7
, RTI.ATTRIBUTE8
, RTI.ATTRIBUTE9
, RTI.ATTRIBUTE10
, RTI.ATTRIBUTE11
, RTI.ATTRIBUTE12
, RTI.ATTRIBUTE13
, RTI.ATTRIBUTE14
, RTI.ATTRIBUTE15
, RTI.REQ_DISTRIBUTION_ID
, RTI.REASON_ID
, RTI.DESTINATION_CONTEXT
, RTI.SOURCE_DOC_UNIT_OF_MEASURE
, PLC1.DISPLAYED_FIELD TRANSACTION_TYPE_DSP
, RSL.GOVERNMENT_CONTEXT
, RSL.USSGL_TRANSACTION_CODE
, PRL.REQUISITION_HEADER_ID
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RSL.LINE_NUM
, RSL.PACKING_SLIP
, RTI.ITEM_ID
, RTI.ITEM_DESCRIPTION
, RTI.CATEGORY_ID
, RTI.ITEM_REVISION
, RTI.RECEIPT_SOURCE_CODE
, MSI.ALLOWED_UNITS_LOOKUP_CODE
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CC
, MSI.REVISION_QTY_CONTROL_CODE REVISION_QTY_CONTROL_CODE
, MSI.LOT_CONTROL_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_DAYS
, MSI.ORGANIZATION_ID ITEM_ORGANIZATION_ID
, PLC2.DISPLAYED_FIELD INSPECTION_STATUS_DSP
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, 'PO'
, PH.SEGMENT1
, RSH.SHIPMENT_NUM) PO_NUMBER
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.LINE_NUM) PO_LINE_NUMBER
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPMENT_NUMBER
, PLL.SHIPMENT_NUM) PO_SHIPMENT_NUMBER
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PR.RELEASE_NUM) PO_RELEASE_NUM
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, NVL(PL.HAZARD_CLASS_ID
, MSI.HAZARD_CLASS_ID)) HAZARD_CLASS_ID
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, NVL(PL.UN_NUMBER_ID
, MSI.UN_NUMBER_ID)) UN_NUMBER_ID
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, 'PO'
, POV.VENDOR_NAME
, OOD.ORGANIZATION_NAME) VENDOR
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, OETL.NAME
, PLC4.DISPLAYED_FIELD) ORDER_TYPE
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PH.NOTE_TO_RECEIVER) NOTE_TO_RECEIVER
, RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER
, PLC3.DISPLAYED_FIELD DESTINATION_TYPE_DSP
, HRL.LOCATION_CODE
, RSH.BILL_OF_LADING
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER
, RSH.WAYBILL_AIRBILL_NUM
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.VENDOR_PRODUCT_NUM) VENDOR_ITEM_NUMBER
, RRH.ROUTING_NAME
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY
, 'PO'
, PLL.QUANTITY
, RSL.QUANTITY_SHIPPED) ORDERED_QTY
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDER_QUANTITY_UOM
, 'PO'
, MUM3.UNIT_OF_MEASURE
, RSL.UNIT_OF_MEASURE) ORDERED_UOM
, TO_DATE(DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, NVL(PLL.NEED_BY_DATE
, PLL.PROMISED_DATE))
, 'DD-MON-YYYY') DUE_DATE_VENDOR
, RSH.EXPECTED_RECEIPT_DATE DUE_DATE_INTERNAL
, MTR.REASON_NAME REASON_CODE
, HRE.FULL_NAME
, RTI.WIP_ENTITY_ID
, RTI.WIP_LINE_ID
, RTI.WIP_REPETITIVE_SCHEDULE_ID
, RTI.WIP_OPERATION_SEQ_NUM
, RTI.WIP_RESOURCE_SEQ_NUM
, RTI.DEPARTMENT_CODE
, RTI.BOM_RESOURCE_ID
, PLL.QTY_RCV_EXCEPTION_CODE
, RTI.MOVEMENT_ID
, RTI.INSPECTION_QUALITY_CODE
, RTI.SUBSTITUTE_UNORDERED_CODE
, PLC5.DISPLAYED_FIELD
, PLC6.DISPLAYED_FIELD
, RTI.QA_COLLECTION_ID
, RTI.EMPLOYEE_ID
, RTI.OE_ORDER_HEADER_ID
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEH.ORDER_NUMBER
, NULL) OE_ORDER_NUM
, RTI.OE_ORDER_LINE_ID
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.LINE_NUMBER
, NULL) OE_ORDER_LINE_NUM
, RTI.CUSTOMER_ID
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEC.NAME
, NULL) CUSTOMER
, RTI.CUSTOMER_SITE_ID
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SCHEDULE_ARRIVAL_DATE
, NULL) DUE_DATE_CUSTOMER
, RTI.SECONDARY_QUANTITY
, RTI.SECONDARY_UNIT_OF_MEASURE
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY2
, 'PO'
, PLL.SECONDARY_QUANTITY
, RSL.SECONDARY_QUANTITY_SHIPPED) SECONDARY_ORDERED_QTY
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, MUM5.UNIT_OF_MEASURE
, 'PO'
, MUM4.UNIT_OF_MEASURE
, RSL.SECONDARY_UNIT_OF_MEASURE) SECONDARY_ORDERED_UOM
, RTI.QC_GRADE QC_GRADE
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'PO'
, PLL.MATCHING_BASIS
, NULL) MATCHING_BASIS
, RTI.AMOUNT
, FCV.NAME
, PJ.NAME
, PJA.JOB_DESCRIPTION
, DECODE(RTI.ORG_ID
, NULL
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'PO'
, PH.ORG_ID
, 'REQ'
, PRL.ORG_ID
, 'RMA'
, OEH.ORG_ID
, NULL)
, RTI.ORG_ID) ORG_ID
, RTI.LCM_SHIPMENT_LINE_ID
, RTI.UNIT_LANDED_COST
, RTI.PROCESSING_STATUS_CODE
FROM RCV_TRANSACTIONS_INTERFACE RTI
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, PO_REQUISITION_LINES PRL
, MTL_SYSTEM_ITEMS MSI
, MTL_UNITS_OF_MEASURE MUM1
, MTL_UNITS_OF_MEASURE MUM2
, MTL_UNITS_OF_MEASURE MUM3
, MTL_UNITS_OF_MEASURE MUM4
, MTL_UNITS_OF_MEASURE MUM5
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, PO_LOOKUP_CODES PLC4
, PO_LOOKUP_CODES PLC5
, PO_LOOKUP_CODES PLC6
, OE_SOLD_TO_ORGS_V OEC
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, OE_TRANSACTION_TYPES_TL OETL
, OE_TRANSACTION_TYPES_ALL OET
, PO_HEADERS_ALL PH
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS PLL
, PO_RELEASES_ALL PR
, PO_VENDORS POV
, HR_LOCATIONS_ALL_TL HRL
, MTL_TRANSACTION_REASONS MTR
, RCV_ROUTING_HEADERS RRH
, PER_ALL_PEOPLE_F HRE
, ORG_ORGANIZATION_DEFINITIONS OOD
, FND_CURRENCIES_VL FCV
, PER_JOBS PJ
, PO_JOB_ASSOCIATIONS PJA
WHERE (RTI.TRANSACTION_TYPE = PLC1.LOOKUP_CODE
AND PLC1.LOOKUP_TYPE = 'RCV TRANSACTION TYPE')
AND (RTI.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+))
AND (RTI.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+))
AND (RTI.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+))
AND (MSI.INVENTORY_ITEM_ID(+) = RTI.ITEM_ID
AND NVL(MSI.ORGANIZATION_ID
, RTI.TO_ORGANIZATION_ID) = RTI.TO_ORGANIZATION_ID
AND MSI.PRIMARY_UNIT_OF_MEASURE = MUM1.UNIT_OF_MEASURE(+))
AND (RTI.UNIT_OF_MEASURE = MUM2.UNIT_OF_MEASURE(+))
AND (PLC2.LOOKUP_TYPE(+) = 'INSPECTION STATUS'
AND PLC2.LOOKUP_CODE(+) = RTI.INSPECTION_STATUS_CODE)
AND OEL.LINE_ID(+) = RTI.OE_ORDER_LINE_ID
AND OEH.HEADER_ID(+) = RTI.OE_ORDER_HEADER_ID
AND OEC.CUSTOMER_ID(+) = RSH.CUSTOMER_ID
AND OET.TRANSACTION_TYPE_ID (+) = OEH.ORDER_TYPE_ID
AND OET.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV('LANG')
AND OET.TRANSACTION_TYPE_CODE(+) = 'ORDER'
AND PH.PO_HEADER_ID(+) = RTI.PO_HEADER_ID
AND (PL.PO_LINE_ID (+) = RTI.PO_LINE_ID)
AND (PLL.LINE_LOCATION_ID (+) = RTI.PO_LINE_LOCATION_ID)
AND (PR.PO_RELEASE_ID(+) = RTI.PO_RELEASE_ID)
AND POV.VENDOR_ID(+) = RSH.VENDOR_ID
AND PLC3.LOOKUP_TYPE(+) = 'RCV DESTINATION TYPE'
AND PLC3.LOOKUP_CODE(+) = RTI.DESTINATION_TYPE_CODE
AND HRL.LOCATION_ID(+) = NVL(RTI.DELIVER_TO_LOCATION_ID
, RTI.LOCATION_ID)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND RRH.ROUTING_HEADER_ID(+) = RTI.ROUTING_HEADER_ID
AND MUM3.UNIT_OF_MEASURE(+) = PL.UNIT_MEAS_LOOKUP_CODE
AND MUM4.UNIT_OF_MEASURE(+) = PLL.SECONDARY_UNIT_OF_MEASURE
AND MUM5.UOM_CODE(+) =OEL.ORDERED_QUANTITY_UOM2
AND MTR.REASON_ID (+) = RTI.REASON_ID
AND HRE.PERSON_ID (+) = RTI.DELIVER_TO_PERSON_ID
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE (+)
AND HRE.EFFECTIVE_END_DATE (+)
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_PERSON (HRE.PERSON_ID (+)
, HRE.CURRENT_APPLICANT_FLAG (+)
, HRE.CURRENT_EMPLOYEE_FLAG (+)
, HRE.CURRENT_NPW_FLAG (+)
, HRE.EMPLOYEE_NUMBER (+)
, HRE.APPLICANT_NUMBER (+)
, HRE.NPW_NUMBER(+))) = 'TRUE'
AND DECODE(HR_GENERAL.GET_XBG_PROFILE
, 'Y'
, HRE.BUSINESS_GROUP_ID (+)
, HR_GENERAL.GET_BUSINESS_GROUP_ID) = HRE.BUSINESS_GROUP_ID (+)
AND OOD.ORGANIZATION_ID(+) = RTI.FROM_ORGANIZATION_ID
AND DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'PO'
, 'PO TYPE'
, 'SHIPMENT SOURCE TYPE') = PLC4.LOOKUP_TYPE
AND DECODE(RTI.TRANSACTION_TYPE
, 'UNORDERED'
, 'STANDARD'
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'PO'
, PH.TYPE_LOOKUP_CODE
, RSH.RECEIPT_SOURCE_CODE)) = PLC4.LOOKUP_CODE
AND PLC5.LOOKUP_TYPE = 'TRANSACTION STATUS'
AND PLC5.LOOKUP_CODE = RTI.TRANSACTION_STATUS_CODE
AND PLC6.LOOKUP_TYPE = 'RCV PROCESSING MODE'
AND PLC6.LOOKUP_CODE = RTI.PROCESSING_MODE_CODE
AND FCV.CURRENCY_CODE (+) = RTI.CURRENCY_CODE
AND PJ.JOB_ID (+) = RTI.JOB_ID
AND PJA.JOB_ID (+) = RTI.JOB_ID
AND NVL(PH.ORG_ID
, -99) = NVL(PLL.ORG_ID
, -99)