View: RCV_RETURNS_V

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

View Text

SELECT RT.TRANSACTION_ID
, RT.LAST_UPDATE_DATE
, RT.LAST_UPDATED_BY
, RT.CREATION_DATE
, RT.CREATED_BY
, RT.LAST_UPDATE_LOGIN
, RT.REQUEST_ID
, RT.PROGRAM_APPLICATION_ID
, RT.PROGRAM_ID
, RT.PROGRAM_UPDATE_DATE
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.QUANTITY
, RT.UNIT_OF_MEASURE
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, RT.USER_ENTERED_FLAG
, RT.INTERFACE_SOURCE_CODE
, RT.INTERFACE_SOURCE_LINE_ID
, RT.INV_TRANSACTION_ID
, RT.SOURCE_DOCUMENT_CODE
, RT.DESTINATION_TYPE_CODE
, RT.PRIMARY_QUANTITY
, RT.PRIMARY_UNIT_OF_MEASURE
, RT.UOM_CODE
, RT.EMPLOYEE_ID
, RT.PARENT_TRANSACTION_ID
, RT.PO_HEADER_ID
, RT.PO_RELEASE_ID
, RT.PO_LINE_ID
, RT.PO_LINE_LOCATION_ID
, RT.PO_DISTRIBUTION_ID
, RT.PO_REVISION_NUM
, RT.REQUISITION_LINE_ID
, NVL(PLL.PRICE_OVERRIDE
, PL.UNIT_PRICE) PO_UNIT_PRICE
, RT.CURRENCY_CODE
, RT.CURRENCY_CONVERSION_TYPE
, RT.CURRENCY_CONVERSION_RATE
, RT.CURRENCY_CONVERSION_DATE
, RT.ROUTING_HEADER_ID
, RT.ROUTING_STEP_ID
, RRH.ROUTING_NAME
, RT.DELIVER_TO_PERSON_ID
, RT.DELIVER_TO_LOCATION_ID
, DECODE(RT.DESTINATION_TYPE_CODE
, 'RECEIVING'
, RT.LOCATION_ID
, RT.DELIVER_TO_LOCATION_ID )
, RT.LOCATION_ID
, HRL.LOCATION_CODE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POV.VENDOR_ID) VENDOR_ID
, RT.VENDOR_SITE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, POV.VENDOR_NAME) VENDOR
, RT.ORGANIZATION_ID
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, RT.WIP_ENTITY_ID
, RT.WIP_LINE_ID
, RT.WIP_REPETITIVE_SCHEDULE_ID
, RT.WIP_OPERATION_SEQ_NUM
, RT.WIP_RESOURCE_SEQ_NUM
, RT.BOM_RESOURCE_ID
, RT.SUBSTITUTE_UNORDERED_CODE
, RT.RECEIPT_EXCEPTION_FLAG
, RT.INSPECTION_STATUS_CODE
, RT.ACCRUAL_STATUS_CODE
, RT.INSPECTION_QUALITY_CODE
, RT.VENDOR_LOT_NUM
, RT.RMA_REFERENCE
, RT.COMMENTS
, 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.REQ_DISTRIBUTION_ID
, RT.DEPARTMENT_CODE
, RT.REASON_ID
, RT.DESTINATION_CONTEXT
, RT.LOCATOR_ATTRIBUTE
, RT.CHILD_INSPECTION_FLAG
, RT.SOURCE_DOC_UNIT_OF_MEASURE
, RT.SOURCE_DOC_QUANTITY
, RT.MOVEMENT_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OETL.NAME
, NULL) ORDER_TYPE
, RSL.GOVERNMENT_CONTEXT
, RSL.USSGL_TRANSACTION_CODE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PRL.REQUISITION_HEADER_ID ) REQUISITION_HEADER_ID
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RSH.FREIGHT_CARRIER_CODE
, RSH.BILL_OF_LADING
, RSL.PACKING_SLIP
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, RSL.CATEGORY_ID
, RSL.ITEM_REVISION
, NVL(MSI.LOCATION_CONTROL_CODE
, 1)
, DECODE(MSI.RESTRICT_LOCATORS_CODE
, 1
, 'Y'
, 'N')
, DECODE(MSI.RESTRICT_SUBINVENTORIES_CODE
, 1
, 'Y'
, 'N')
, RSL.LINE_NUM
, RSH.RECEIPT_SOURCE_CODE
, NVL(MSI.ALLOWED_UNITS_LOOKUP_CODE
, 2)
, MSI.SERIAL_NUMBER_CONTROL_CODE
, 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
, DECODE (RT.TRANSACTION_TYPE
, 'UNORDERED'
, 'STANDARD'
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PH.TYPE_LOOKUP_CODE
, RSH.RECEIPT_SOURCE_CODE ) ) PO_TYPE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PH.SEGMENT1) PO_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.LINE_NUM) PO_LINE_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY
, PLL.QUANTITY ) ORDERED_QTY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDER_QUANTITY_UOM
, PL.UNIT_MEAS_LOOKUP_CODE ) ORDERED_UOM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.VENDOR_PRODUCT_NUM) VENDOR_ITEM_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PLL.SHIPMENT_NUM) PO_SHIPMENT_NUMBER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.HAZARD_CLASS_ID) PO_HAZARD_CLASS_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PL.UN_NUMBER_ID) PO_UN_NUMBER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, NULL
, PR.RELEASE_NUM) PO_RELEASE_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.HAZARD_CLASS_ID
, NVL(PL.HAZARD_CLASS_ID
, MSI.HAZARD_CLASS_ID) ) HAZARD_CLASS_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, MSI.UN_NUMBER_ID
, NVL(PL.UN_NUMBER_ID
, MSI.UN_NUMBER_ID) ) UN_NUMBER_ID
, NVL(MSI.OUTSIDE_OPERATION_FLAG
, 'N')
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SCHEDULE_ARRIVAL_DATE
, 'PO'
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE) ) EXPECTED_RECEIPT_DATE
, RT.OE_ORDER_HEADER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEH.ORDER_NUMBER
, NULL) OE_ORDER_NUMBER
, RT.OE_ORDER_LINE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.LINE_NUMBER
, NULL) OE_ORDER_LINE_NUMBER
, NVL(RT.CUSTOMER_ID
, RSH.CUSTOMER_ID) CUSTOMER_ID
, RT.CUSTOMER_SITE_ID
, NVL(RT.TRANSFER_LPN_ID
, RT.LPN_ID) LPN_ID
, DECODE(RT.LPN_ID
, NVL(RT.TRANSFER_LPN_ID
, RT.LPN_ID)
, TO_NUMBER(NULL)
, RT.LPN_ID ) TRANSFER_LPN_ID
, RT.SECONDARY_QUANTITY
, RT.SECONDARY_UNIT_OF_MEASURE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY2
, PLL.SECONDARY_QUANTITY ) SECONDARY_ORDERED_QTY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.ORDERED_QUANTITY_UOM2
, PLL.SECONDARY_UNIT_OF_MEASURE ) SECONDARY_ORDERED_UOM
, RT.QC_GRADE QC_GRADE
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, DECODE(OET.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL )
, PH.NOTE_TO_RECEIVER ) NOTE_TO_RECEIVER
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'RMA'
, OEL.SHIPPING_INSTRUCTIONS
, PLL.NOTE_TO_RECEIVER ) PLL_NOTE_TO_RECEIVER
, RT.CONSIGNED_FLAG CONSIGNED_FLAG
, DECODE(MSI.TRACKING_QUANTITY_IND
, 'PS'
, MSI.SECONDARY_DEFAULT_IND
, NULL) SECONDARY_DEFAULT_IND
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, PO_REQUISITION_LINES PRL
, HR_LOCATIONS_ALL_TL HRL
, MTL_SYSTEM_ITEMS MSI
, 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
, RCV_ROUTING_HEADERS RRH
, PO_VENDORS POV
WHERE ( ( ( ( RT.TRANSACTION_TYPE IN ('RECEIVE'
, 'TRANSFER'
, 'ACCEPT'
, 'REJECT'
, 'MATCH') ) OR ( RT.TRANSACTION_TYPE = 'UNORDERED'
AND NOT EXISTS ( SELECT 'PROCESSED MATCH ROWS'
FROM RCV_TRANSACTIONS RT2
WHERE RT2.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT2.TRANSACTION_TYPE = 'MATCH' )
AND NOT EXISTS ( SELECT 'UNPROCESSED MATCH ROWS'
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
AND RTI.TRANSACTION_TYPE = 'MATCH' ) ) )
AND EXISTS ( SELECT 'POSTIVE RCV SUPPLY'
FROM RCV_SUPPLY RS
WHERE RS.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RS.TO_ORG_PRIMARY_QUANTITY > ( SELECT NVL(SUM(RTI.PRIMARY_QUANTITY)
, 0)
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.PARENT_TRANSACTION_ID = RT.TRANSACTION_ID
AND RTI.TRANSACTION_STATUS_CODE = 'PENDING'
AND RTI.PROCESSING_STATUS_CODE = 'PENDING' ) ) ) OR ( RT.TRANSACTION_TYPE = 'DELIVER'
AND RT.SOURCE_DOCUMENT_CODE <> 'RMA' ) )
AND RT.SOURCE_DOCUMENT_CODE IN ('PO'
, 'RMA')
AND NOT EXISTS ( SELECT 'PURCHASE ORDER SHIPMENT CANCELLED OR FC'
FROM PO_LINE_LOCATIONS PLL
WHERE PLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
AND ( NVL(PLL.CANCEL_FLAG
, 'N') = 'Y' OR NVL(PLL.CLOSED_CODE
, 'OPEN') = 'FINALLY CLOSED' OR NVL(PLL.APPROVED_FLAG
, 'N') <> 'Y' ) )
AND NOT EXISTS ( SELECT 'REQUISITION LINE CANCELLED OR FC'
FROM PO_REQUISITION_LINES PRL
WHERE PRL.REQUISITION_LINE_ID = RT.REQUISITION_LINE_ID
AND ( NVL(PRL.CANCEL_FLAG
, 'N') = 'Y' OR NVL(PRL.CLOSED_CODE
, 'OPEN') = 'FINALLY CLOSED' ) )
AND ( MSI.INVENTORY_ITEM_ID(+) = RSL.ITEM_ID
AND NVL (MSI.ORGANIZATION_ID
, RT.ORGANIZATION_ID) = RT.ORGANIZATION_ID )
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+)
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND OEL.LINE_ID(+) = RT.OE_ORDER_LINE_ID
AND OEH.HEADER_ID(+) = RT.OE_ORDER_HEADER_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 (+) = RT.PO_HEADER_ID)
AND (PL.PO_LINE_ID (+) = RT.PO_LINE_ID)
AND (PLL.LINE_LOCATION_ID (+) = RT.PO_LINE_LOCATION_ID)
AND (PR.PO_RELEASE_ID(+) = RT.PO_RELEASE_ID)
AND RRH.ROUTING_HEADER_ID(+) = RT.ROUTING_HEADER_ID
AND POV.VENDOR_ID(+) = RSH.VENDOR_ID
AND RT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND NVL(PL.MATCHING_BASIS(+)
, 'QUANTITY') <> 'AMOUNT'

Columns