View: RCV_VIEW_INTERFACE_V

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

View Text

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'
, NULL
, 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)
, 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
, DECODE(RTI.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, NVL(PLL.NEED_BY_DATE
, PLL.PROMISED_DATE)) 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
, 'RMA'
, NULL
, PL.MATCHING_BASIS) MATCHING_BASIS
, RTI.AMOUNT
, FCV.NAME
, PJ.NAME
, PJA.JOB_DESCRIPTION
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 PH
, PO_LINES PL
, PO_LINE_LOCATIONS PLL
, PO_RELEASES 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 HRE.EMPLOYEE_NUMBER (+) IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE (+)
AND HRE.EFFECTIVE_END_DATE (+)
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, HRE.PERSON_ID (+)
, HRE.PERSON_TYPE_ID (+)
, HRE.EMPLOYEE_NUMBER (+)
, HRE.APPLICANT_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

Columns