View: RCV_TRANSACTIONS_V

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

View Text

SELECT 'N' LINE_CHKBOX
, RSUP.FROM_ORGANIZATION_ID
, RSUP.TO_ORGANIZATION_ID
, RT.SOURCE_DOCUMENT_CODE
, RSH.RECEIPT_SOURCE_CODE
, RSUP.RCV_TRANSACTION_ID
, RT.TRANSACTION_DATE
, RT.TRANSACTION_TYPE
, PLC.DISPLAYED_FIELD TRANSACTION_TYPE_DSP
, RT.QUANTITY
, RT.UNIT_OF_MEASURE
, RT.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM
, RT.PRIMARY_QUANTITY
, MUM1.UOM_CLASS PRIMARY_UOM_CLASS
, MUM.UOM_CLASS UOM_CLASS
, RSUP.PO_HEADER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, 'RMA'
, OEH.ORDER_NUMBER
, RSH.SHIPMENT_NUM) ORDER_NUMBER
, RT.PO_REVISION_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEOT.ORDER_CATEGORY_CODE
, POH.TYPE_LOOKUP_CODE) TYPE_LOOKUP_CODE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OETL.NAME
, PLC3.DISPLAYED_FIELD) ORDER_TYPE
, RSUP.PO_RELEASE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PR.RELEASE_NUM) PO_RELEASE_NUMBER
, RSH.VENDOR_ID
, RT.VENDOR_SITE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POV.VENDOR_NAME
, 'RMA'
, SUBSTR(HZP.PARTY_NAME
, 1
, 50)
, OOD.NAME) SOURCE
, RSUP.PO_LINE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.LINE_NUMBER
, 'PO'
, POL.LINE_NUM
, RSL.LINE_NUM) LINE_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY
, 'PO'
, POL.QUANTITY
, RSL.QUANTITY_SHIPPED) ORDERED_QTY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDER_QUANTITY_UOM
, 'PO'
, MUM2.UNIT_OF_MEASURE
, RSL.UNIT_OF_MEASURE) ORDERED_UOM
, NVL(PLL.PRICE_OVERRIDE
, POL.UNIT_PRICE) PO_UNIT_PRICE
, RSL.CATEGORY_ID
, RSUP.SUPPLY_SOURCE_ID
, RSUP.ITEM_ID
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2) ITEM_ALLOWED_UNITS_LOOKUP_CODE
, NVL(MSI.LOCATION_CONTROL_CODE
, 1) ITEM_LOCATOR_CONTROL
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'N') RESTRICT_LOCATORS_CODE
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N') RESTRICT_SUBINVENTORIES_CODE
, NVL(MSI.SHELF_LIFE_CODE
, 1) SHELF_LIFE_CODE
, NVL(MSI.SHELF_LIFE_DAYS
, 0) SHELF_LIFE_DAYS
, MSI.SERIAL_NUMBER_CONTROL_CODE SERIAL_NUMBER_CONTROL_CODE
, MSI.LOT_CONTROL_CODE LOT_CONTROL_CODE
, DECODE(MSI.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'N') ITEM_REVISION_CONTROL_FLAG
, DECODE(MSI1.REVISION_QTY_CONTROL_CODE
, 1
, 'N'
, 2
, 'Y'
, 'N') REVISION_QTY_CONTROL_CODE
, RSL.ITEM_DESCRIPTION
, RSUP.ITEM_REVISION
, RSUP.PO_LINE_LOCATION_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PLL.SHIPMENT_NUM) PO_SHIPMENT_NUMBER
, RT.PO_DISTRIBUTION_ID
, NVL(RSL.EMPLOYEE_ID
, RT.EMPLOYEE_ID)
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, DECODE(OEOT1.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, POH.NOTE_TO_RECEIVER) NOTE_TO_RECEIVER
, RSL.COMMENTS
, RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SCHEDULE_ARRIVAL_DATE
, 'PO'
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, RSH.EXPECTED_RECEIPT_DATE) DUE_DATE
, RSUP.REQ_HEADER_ID
, RSUP.REQ_LINE_ID
, RSUP.SHIPMENT_HEADER_ID
, RSUP.SHIPMENT_LINE_ID
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM RCV_SHIPMENT_NUM
, RSH.PACKING_SLIP
, RSH.BILL_OF_LADING
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER
, RSH.WAYBILL_AIRBILL_NUM
, RSL.LINE_NUM RCV_LINE_NUM
, RSL.GOVERNMENT_CONTEXT
, RSL.USSGL_TRANSACTION_CODE
, RT.INSPECTION_STATUS_CODE
, PLC1.DISPLAYED_FIELD INSPECTION_STATUS_DSP
, RT.INSPECTION_QUALITY_CODE
, RT.VENDOR_LOT_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POL.VENDOR_PRODUCT_NUM) VENDOR_ITEM_NUMBER
, RT.RECEIPT_EXCEPTION_FLAG
, RT.SUBSTITUTE_UNORDERED_CODE
, RT.ROUTING_HEADER_ID ROUTING_ID
, RRH.ROUTING_NAME
, RT.ROUTING_STEP_ID
, RT.REASON_ID
, MTR.REASON_NAME REASON_CODE
, RT.CURRENCY_CODE
, RT.CURRENCY_CONVERSION_RATE
, RT.CURRENCY_CONVERSION_DATE
, RT.CURRENCY_CONVERSION_TYPE
, RSL.REQ_DISTRIBUTION_ID
, RSUP.DESTINATION_TYPE_CODE DESTINATION_TYPE_CODE_HOLD
, PLC2.DISPLAYED_FIELD DESTINATION_TYPE_DSP_HOLD
, RSL.DESTINATION_CONTEXT
, RT.LOCATION_ID
, RSL.DELIVER_TO_PERSON_ID
, RSL.DELIVER_TO_LOCATION_ID
, RSL.TO_SUBINVENTORY SUBINVENTORY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.UN_NUMBER_ID
, NVL(POL.UN_NUMBER_ID
, MSI.UN_NUMBER_ID)) UN_NUMBER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.HAZARD_CLASS_ID
, NVL(POL.HAZARD_CLASS_ID
, MSI.HAZARD_CLASS_ID)) HAZARD_CLASS_ID
, RSUP.CREATION_DATE
, RSUP.PROGRAM_APPLICATION_ID
, RSUP.PROGRAM_ID
, RSUP.PROGRAM_UPDATE_DATE
, RSUP.REQUEST_ID
, RSUP.LAST_UPDATE_DATE
, RSUP.LAST_UPDATED_BY
, RSUP.LAST_UPDATE_LOGIN
, RSUP.CREATED_BY
, HRL.LOCATION_CODE CURRENT_LOCATION
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
, RT.QA_COLLECTION_ID
, DCT.USER_CONVERSION_TYPE
, RT.MATCH_OPTION
, RSUP.OE_ORDER_HEADER_ID
, OEH.ORDER_NUMBER OE_ORDER_NUM
, RSUP.OE_ORDER_LINE_ID
, OEL.LINE_NUMBER OE_ORDER_LINE_NUM
, RSH.CUSTOMER_ID
, RSH.CUSTOMER_SITE_ID
, DECODE(OEL.ITEM_IDENTIFIER_TYPE
, 'CUST'
, MCI.CUSTOMER_ITEM_NUMBER
, '') CUSTOMER_ITEM_NUM
, RSL.BAR_CODE_LABEL
, RSL.CONTAINER_NUM
, RSL.TRUCK_NUM
, RT.SECONDARY_QUANTITY
, RT.SECONDARY_UNIT_OF_MEASURE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY2
, 'PO'
, POL.SECONDARY_QUANTITY
, RSL.SECONDARY_QUANTITY_SHIPPED) SECONDARY_ORDERED_QTY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MUM3.UNIT_OF_MEASURE
, 'PO'
, MUM4.UNIT_OF_MEASURE
, RSL.SECONDARY_UNIT_OF_MEASURE) SECONDARY_ORDERED_UOM
, RT.QC_GRADE QC_GRADE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPPING_INSTRUCTIONS
, PLL.NOTE_TO_RECEIVER) PLL_NOTE_TO_RECEIVER
, RSUP.LPN_ID
, DECODE(MSI.TRACKING_QUANTITY_IND
, 'PS'
, MSI.SECONDARY_DEFAULT_IND
, NULL)
FROM RCV_SUPPLY RSUP
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, MTL_UNITS_OF_MEASURE MUM
, MTL_UNITS_OF_MEASURE MUM1
, MTL_UNITS_OF_MEASURE MUM2
, MTL_UNITS_OF_MEASURE MUM3
, MTL_UNITS_OF_MEASURE MUM4
, MTL_SYSTEM_ITEMS MSI
, MTL_SYSTEM_ITEMS MSI1
, MTL_TRANSACTION_REASONS MTR
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES HZP
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, OE_TRANSACTION_TYPES_TL OETL
, OE_TRANSACTION_TYPES_TL OETL1
, OE_TRANSACTION_TYPES_ALL OEOT
, OE_TRANSACTION_TYPES_ALL OEOT1
, MTL_CUSTOMER_ITEMS MCI
, PO_LINES POL
, PO_HEADERS POH
, PO_LINE_LOCATIONS PLL
, PO_RELEASES PR
, PO_VENDORS POV
, RCV_ROUTING_HEADERS RRH
, HR_LOCATIONS_ALL_TL HRL
, GL_DAILY_CONVERSION_TYPES DCT
, HR_ALL_ORGANIZATION_UNITS_TL OOD
WHERE RSUP.SUPPLY_TYPE_CODE = 'RECEIVING'
AND RSL.SHIPMENT_LINE_ID = RSUP.SHIPMENT_LINE_ID
AND RT.TRANSACTION_ID = RSUP.RCV_TRANSACTION_ID
AND RT.TRANSACTION_TYPE != 'UNORDERED'
AND RRH.ROUTING_HEADER_ID(+) = RT.ROUTING_HEADER_ID
AND RSH.SHIPMENT_HEADER_ID = RSUP.SHIPMENT_HEADER_ID
AND RT.TRANSACTION_TYPE = PLC.LOOKUP_CODE
AND PLC.LOOKUP_TYPE = 'RCV TRANSACTION TYPE'
AND PLC1.LOOKUP_TYPE(+) = 'INSPECTION STATUS'
AND PLC1.LOOKUP_CODE(+) = RT.INSPECTION_STATUS_CODE
AND PLC2.LOOKUP_TYPE(+) = 'RCV DESTINATION TYPE'
AND PLC2.LOOKUP_CODE(+) = RSUP.DESTINATION_TYPE_CODE
AND OEL.LINE_ID(+) = RSUP.OE_ORDER_LINE_ID
AND OEH.HEADER_ID(+) = RSUP.OE_ORDER_HEADER_ID
AND OEH.ORDER_TYPE_ID = OEOT1.TRANSACTION_TYPE_ID(+)
AND OEL.LINE_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+)
AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OEOT1.TRANSACTION_TYPE_ID = OETL1.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV('LANG')
AND OETL1.LANGUAGE(+) = USERENV('LANG')
AND OEOT.TRANSACTION_TYPE_CODE(+) = 'LINE'
AND OEOT1.TRANSACTION_TYPE_CODE(+) = 'ORDER'
AND HZCA.CUST_ACCOUNT_ID(+) = RSH.CUSTOMER_ID
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
AND OEL.ORDERED_ITEM_ID = MCI.CUSTOMER_ITEM_ID(+)
AND POL.PO_LINE_ID(+) = RSUP.PO_LINE_ID
AND POH.PO_HEADER_ID(+) = RSUP.PO_HEADER_ID
AND POV.VENDOR_ID(+) = RSH.VENDOR_ID
AND PLL.LINE_LOCATION_ID(+) = RSUP.PO_LINE_LOCATION_ID
AND MUM.UNIT_OF_MEASURE = RSUP.UNIT_OF_MEASURE
AND MUM2.UNIT_OF_MEASURE(+) = POL.UNIT_MEAS_LOOKUP_CODE
AND (MUM4.UNIT_OF_MEASURE (+) = PLL.SECONDARY_UNIT_OF_MEASURE)
AND (MUM3.UOM_CODE (+) =OEL.ORDERED_QUANTITY_UOM2)
AND MSI.ORGANIZATION_ID (+) = RSUP.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = RSUP.ITEM_ID
AND MUM1.UNIT_OF_MEASURE (+) = MSI.PRIMARY_UNIT_OF_MEASURE
AND MSI1.ORGANIZATION_ID (+) = RSUP.FROM_ORGANIZATION_ID
AND MSI1.INVENTORY_ITEM_ID(+) = RSUP.ITEM_ID
AND PR.PO_RELEASE_ID (+) = RSUP.PO_RELEASE_ID
AND MTR.REASON_ID (+) = RT.REASON_ID
AND HRL.LOCATION_ID (+) = RT.LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND OOD.ORGANIZATION_ID(+) = RSL.FROM_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, 'PO TYPE'
, 'SHIPMENT SOURCE TYPE') = PLC3.LOOKUP_TYPE
AND DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.TYPE_LOOKUP_CODE
, RSH.RECEIPT_SOURCE_CODE) = PLC3.LOOKUP_CODE
AND DCT.CONVERSION_TYPE (+) = RT.CURRENCY_CONVERSION_TYPE
AND NVL(POL.MATCHING_BASIS(+)
, 'QUANTITY') != 'AMOUNT'

Columns