VIEW: APPS.POR_RCV_RETURN_TO_VENDOR_V

Source

View Text - Preformatted

SELECT RT.TRANSACTION_ID, RT.CREATION_DATE, RT.TRANSACTION_TYPE, RT.TRANSACTION_DATE, RT.EMPLOYEE_ID, RT.QUANTITY, TO_NUMBER(NULL), PD.QUANTITY_ORDERED, MUOM.UNIT_OF_MEASURE_TL, RT.UNIT_OF_MEASURE, RT.SHIPMENT_HEADER_ID, RT.SHIPMENT_LINE_ID, RT.SOURCE_DOCUMENT_CODE, RT.DESTINATION_TYPE_CODE, RT.PRIMARY_UNIT_OF_MEASURE, 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, PRL.LINE_NUM, RT.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, RT.DELIVER_TO_PERSON_ID, RT.DELIVER_TO_LOCATION_ID, RT.VENDOR_ID, RT.VENDOR_SITE_ID, RT.ORGANIZATION_ID, RT.SUBINVENTORY, RT.LOCATOR_ID, RT.LOCATION_ID, RT.RECEIPT_EXCEPTION_FLAG, RT.INSPECTION_STATUS_CODE, RT.VENDOR_LOT_NUM, 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.REASON_ID, RT.DESTINATION_CONTEXT, RT.LOCATOR_ATTRIBUTE, RT.SOURCE_DOC_UNIT_OF_MEASURE, PLC1.DISPLAYED_FIELD TRANSACTION_TYPE_DSP, RSL.GOVERNMENT_CONTEXT, RSL.USSGL_TRANSACTION_CODE, PRL.REQUISITION_HEADER_ID, PRH.SEGMENT1, RSH.RECEIPT_NUM, RSH.SHIPMENT_NUM, RSL.LINE_NUM, RSL.PACKING_SLIP, RSL.ITEM_ID, RSL.ITEM_DESCRIPTION, RSL.CATEGORY_ID, RSL.ITEM_REVISION, RSH.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(RT.SOURCE_DOCUMENT_CODE, 'PO', DECODE(RT.PO_RELEASE_ID, NULL, PH.SEGMENT1, PH.SEGMENT1 || '-' || TO_CHAR(PR.RELEASE_NUM)), RSH.SHIPMENT_NUM) PO_NUMBER, PL.LINE_NUM PO_LINE_NUMBER, PLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER, PR.RELEASE_NUM PO_RELEASE_NUM, NVL(PL.HAZARD_CLASS_ID, MSI.HAZARD_CLASS_ID) HAZARD_CLASS_ID, NVL(PL.UN_NUMBER_ID, MSI.UN_NUMBER_ID) UN_NUMBER_ID, POV.VENDOR_NAME VENDOR, PLC4.DISPLAYED_FIELD ORDER_TYPE, 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, PL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER, RRH.ROUTING_NAME, DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', PLL.QUANTITY, RSL.QUANTITY_SHIPPED) ORDERED_QTY, DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', PL.UNIT_MEAS_LOOKUP_CODE, RSL.UNIT_OF_MEASURE) ORDERED_UOM, 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, RT.WIP_ENTITY_ID, RT.WIP_LINE_ID, RT.WIP_REPETITIVE_SCHEDULE_ID, RT.WIP_OPERATION_SEQ_NUM, RT.WIP_RESOURCE_SEQ_NUM, RT.DEPARTMENT_CODE, RT.BOM_RESOURCE_ID, PLL.QTY_RCV_EXCEPTION_CODE, RT.MOVEMENT_ID, RT.INSPECTION_QUALITY_CODE, RT.SUBSTITUTE_UNORDERED_CODE, PH.SEGMENT1 PO_NO_RELEASE_NUMBER FROM PO_HEADERS PH, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, PO_REQUISITION_LINES PRL, PO_REQUISITION_HEADERS PRH, MTL_SYSTEM_ITEMS MSI, PO_LOOKUP_CODES PLC1, PO_LOOKUP_CODES PLC2, PO_LOOKUP_CODES PLC3, PO_LINES_TRX_V PL, PO_LINE_LOCATIONS_TRX_V PLL, PO_DISTRIBUTIONS_TRX_V PD, PO_RELEASES PR, PO_VENDORS POV, HR_LOCATIONS_ALL HRL, MTL_TRANSACTION_REASONS MTR, RCV_ROUTING_HEADERS RRH, PER_PEOPLE_F HRE, ORG_ORGANIZATION_DEFINITIONS OOD, PO_LOOKUP_CODES PLC4, RCV_TRANSACTIONS RT, MTL_UNITS_OF_MEASURE MUOM WHERE (RT.TRANSACTION_TYPE = 'DELIVER' AND RT.SOURCE_DOCUMENT_CODE = 'PO') AND NOT EXISTS (SELECT 'purchase order shipment cancelled or fc' FROM PO_LINE_LOCATIONS_TRX_V 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')) 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 (RT.TRANSACTION_TYPE = PLC1.LOOKUP_CODE AND PLC1.LOOKUP_TYPE = 'RCV TRANSACTION TYPE') AND (RT.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE) AND (RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID) AND (RT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+)) AND (PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID(+)) AND (RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID) AND (MSI.INVENTORY_ITEM_ID(+) = RSL.ITEM_ID AND NVL(MSI.ORGANIZATION_ID, RT.ORGANIZATION_ID) = RT.ORGANIZATION_ID) AND (PLC2.LOOKUP_TYPE(+) = 'INSPECTION STATUS' AND PLC2.LOOKUP_CODE(+) = RT.INSPECTION_STATUS_CODE) 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 (PD.PO_DISTRIBUTION_ID = RT.PO_DISTRIBUTION_ID) AND (PR.PO_RELEASE_ID(+) = RT.PO_RELEASE_ID) AND POV.VENDOR_ID = RSH.VENDOR_ID AND PLC3.LOOKUP_TYPE(+) = 'RCV DESTINATION TYPE' AND PLC3.LOOKUP_CODE(+) = RT.DESTINATION_TYPE_CODE AND HRL.LOCATION_ID(+) = NVL(RT.DELIVER_TO_LOCATION_ID, RT.LOCATION_ID) AND RRH.ROUTING_HEADER_ID(+) = RT.ROUTING_HEADER_ID AND RT.ROUTING_HEADER_ID = 3 AND MTR.REASON_ID (+) = RT.REASON_ID AND HRE.PERSON_ID (+) = RT.DELIVER_TO_PERSON_ID AND ((TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND HRE.EMPLOYEE_NUMBER IS NOT NULL AND HRE.PERSON_ID IS NOT NULL ) OR HRE.PERSON_ID IS NULL) AND OOD.ORGANIZATION_ID(+) = RSL.FROM_ORGANIZATION_ID AND DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', 'PO TYPE', 'SHIPMENT SOURCE TYPE') = PLC4.LOOKUP_TYPE AND DECODE(RT.TRANSACTION_TYPE, 'UNORDERED', 'STANDARD', DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', PH.TYPE_LOOKUP_CODE, RSH.RECEIPT_SOURCE_CODE)) = PLC4.LOOKUP_CODE

View Text - HTML Formatted

SELECT RT.TRANSACTION_ID
, RT.CREATION_DATE
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.EMPLOYEE_ID
, RT.QUANTITY
, TO_NUMBER(NULL)
, PD.QUANTITY_ORDERED
, MUOM.UNIT_OF_MEASURE_TL
, RT.UNIT_OF_MEASURE
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, RT.SOURCE_DOCUMENT_CODE
, RT.DESTINATION_TYPE_CODE
, RT.PRIMARY_UNIT_OF_MEASURE
, 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
, PRL.LINE_NUM
, RT.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
, RT.DELIVER_TO_PERSON_ID
, RT.DELIVER_TO_LOCATION_ID
, RT.VENDOR_ID
, RT.VENDOR_SITE_ID
, RT.ORGANIZATION_ID
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, RT.LOCATION_ID
, RT.RECEIPT_EXCEPTION_FLAG
, RT.INSPECTION_STATUS_CODE
, RT.VENDOR_LOT_NUM
, 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.REASON_ID
, RT.DESTINATION_CONTEXT
, RT.LOCATOR_ATTRIBUTE
, RT.SOURCE_DOC_UNIT_OF_MEASURE
, PLC1.DISPLAYED_FIELD TRANSACTION_TYPE_DSP
, RSL.GOVERNMENT_CONTEXT
, RSL.USSGL_TRANSACTION_CODE
, PRL.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RSL.LINE_NUM
, RSL.PACKING_SLIP
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, RSL.CATEGORY_ID
, RSL.ITEM_REVISION
, RSH.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(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, DECODE(RT.PO_RELEASE_ID
, NULL
, PH.SEGMENT1
, PH.SEGMENT1 || '-' || TO_CHAR(PR.RELEASE_NUM))
, RSH.SHIPMENT_NUM) PO_NUMBER
, PL.LINE_NUM PO_LINE_NUMBER
, PLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, PR.RELEASE_NUM PO_RELEASE_NUM
, NVL(PL.HAZARD_CLASS_ID
, MSI.HAZARD_CLASS_ID) HAZARD_CLASS_ID
, NVL(PL.UN_NUMBER_ID
, MSI.UN_NUMBER_ID) UN_NUMBER_ID
, POV.VENDOR_NAME VENDOR
, PLC4.DISPLAYED_FIELD ORDER_TYPE
, 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
, PL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER
, RRH.ROUTING_NAME
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PLL.QUANTITY
, RSL.QUANTITY_SHIPPED) ORDERED_QTY
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PL.UNIT_MEAS_LOOKUP_CODE
, RSL.UNIT_OF_MEASURE) ORDERED_UOM
, 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
, RT.WIP_ENTITY_ID
, RT.WIP_LINE_ID
, RT.WIP_REPETITIVE_SCHEDULE_ID
, RT.WIP_OPERATION_SEQ_NUM
, RT.WIP_RESOURCE_SEQ_NUM
, RT.DEPARTMENT_CODE
, RT.BOM_RESOURCE_ID
, PLL.QTY_RCV_EXCEPTION_CODE
, RT.MOVEMENT_ID
, RT.INSPECTION_QUALITY_CODE
, RT.SUBSTITUTE_UNORDERED_CODE
, PH.SEGMENT1 PO_NO_RELEASE_NUMBER
FROM PO_HEADERS PH
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, PO_REQUISITION_LINES PRL
, PO_REQUISITION_HEADERS PRH
, MTL_SYSTEM_ITEMS MSI
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3
, PO_LINES_TRX_V PL
, PO_LINE_LOCATIONS_TRX_V PLL
, PO_DISTRIBUTIONS_TRX_V PD
, PO_RELEASES PR
, PO_VENDORS POV
, HR_LOCATIONS_ALL HRL
, MTL_TRANSACTION_REASONS MTR
, RCV_ROUTING_HEADERS RRH
, PER_PEOPLE_F HRE
, ORG_ORGANIZATION_DEFINITIONS OOD
, PO_LOOKUP_CODES PLC4
, RCV_TRANSACTIONS RT
, MTL_UNITS_OF_MEASURE MUOM
WHERE (RT.TRANSACTION_TYPE = 'DELIVER'
AND RT.SOURCE_DOCUMENT_CODE = 'PO')
AND NOT EXISTS (SELECT 'PURCHASE ORDER SHIPMENT CANCELLED OR FC'
FROM PO_LINE_LOCATIONS_TRX_V 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'))
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 (RT.TRANSACTION_TYPE = PLC1.LOOKUP_CODE
AND PLC1.LOOKUP_TYPE = 'RCV TRANSACTION TYPE')
AND (RT.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE)
AND (RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID)
AND (RT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+))
AND (PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID(+))
AND (RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID)
AND (MSI.INVENTORY_ITEM_ID(+) = RSL.ITEM_ID
AND NVL(MSI.ORGANIZATION_ID
, RT.ORGANIZATION_ID) = RT.ORGANIZATION_ID)
AND (PLC2.LOOKUP_TYPE(+) = 'INSPECTION STATUS'
AND PLC2.LOOKUP_CODE(+) = RT.INSPECTION_STATUS_CODE)
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 (PD.PO_DISTRIBUTION_ID = RT.PO_DISTRIBUTION_ID)
AND (PR.PO_RELEASE_ID(+) = RT.PO_RELEASE_ID)
AND POV.VENDOR_ID = RSH.VENDOR_ID
AND PLC3.LOOKUP_TYPE(+) = 'RCV DESTINATION TYPE'
AND PLC3.LOOKUP_CODE(+) = RT.DESTINATION_TYPE_CODE
AND HRL.LOCATION_ID(+) = NVL(RT.DELIVER_TO_LOCATION_ID
, RT.LOCATION_ID)
AND RRH.ROUTING_HEADER_ID(+) = RT.ROUTING_HEADER_ID
AND RT.ROUTING_HEADER_ID = 3
AND MTR.REASON_ID (+) = RT.REASON_ID
AND HRE.PERSON_ID (+) = RT.DELIVER_TO_PERSON_ID
AND ((TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND HRE.EMPLOYEE_NUMBER IS NOT NULL
AND HRE.PERSON_ID IS NOT NULL ) OR HRE.PERSON_ID IS NULL)
AND OOD.ORGANIZATION_ID(+) = RSL.FROM_ORGANIZATION_ID
AND DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, 'PO TYPE'
, 'SHIPMENT SOURCE TYPE') = PLC4.LOOKUP_TYPE
AND DECODE(RT.TRANSACTION_TYPE
, 'UNORDERED'
, 'STANDARD'
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, PH.TYPE_LOOKUP_CODE
, RSH.RECEIPT_SOURCE_CODE)) = PLC4.LOOKUP_CODE