VIEW: APPS.POR_RCV_INTERNAL_RECEIPTS_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), PRL.QUANTITY, MUOM.UNIT_OF_MEASURE_TL, RT.UNIT_OF_MEASURE, RT.SHIPMENT_HEADER_ID, RT.SHIPMENT_LINE_ID, 'REQ', 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.DESTINATION_CONTEXT, RT.LOCATOR_ATTRIBUTE, RT.SOURCE_DOC_UNIT_OF_MEASURE, RSL.GOVERNMENT_CONTEXT, 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, PO_INQ_SV.GET_SO_NUMBER(TO_CHAR(PRL.REQUISITION_HEADER_ID), PRL.REQUISITION_LINE_ID) PO_NUMBER, TO_NUMBER(NULL), TO_NUMBER(NULL), TO_NUMBER(NULL), OOD.NAME SOURCE_ORGANIZATION, NULL, RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER, RSH.BILL_OF_LADING, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER, RSH.WAYBILL_AIRBILL_NUM, NULL, RSL.QUANTITY_SHIPPED ORDERED_QTY, RSL.UNIT_OF_MEASURE ORDERED_UOM, TO_DATE(NULL) DUE_DATE_VENDOR, RSH.EXPECTED_RECEIPT_DATE DUE_DATE_INTERNAL, 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, NULL, RT.MOVEMENT_ID, RT.INSPECTION_QUALITY_CODE, RT.SUBSTITUTE_UNORDERED_CODE, NULL, OSH.HEADER_ID, OSH.ORDER_NUMBER, OOD.NAME SOURCE_ORGANIZATION, NULL, PRL.CLOSED_CODE, PRL.CANCEL_FLAG FROM RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, PO_REQUISITION_LINES PRL, PO_REQUISITION_HEADERS PRH, PER_ALL_PEOPLE_F HRE, HR_ALL_ORGANIZATION_UNITS_TL OOD, RCV_TRANSACTIONS RT, MTL_UNITS_OF_MEASURE MUOM, OE_ORDER_HEADERS_ALL OSH, PO_SYSTEM_PARAMETERS POSP WHERE OSH.ORDER_SOURCE_ID = POSP.ORDER_SOURCE_ID AND OSH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1 AND OSH.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID AND RT.TRANSACTION_TYPE = 'DELIVER' AND RT.SOURCE_DOCUMENT_CODE = 'REQ' AND (RT.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE) AND (RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID) AND (RSL.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 RT.ROUTING_HEADER_ID = 3 AND HRE.PERSON_ID (+) = RT.EMPLOYEE_ID AND (HRE.EFFECTIVE_END_DATE = (SELECT MAX(PF.EFFECTIVE_END_DATE) FROM PER_PEOPLE_F PF WHERE PF.PERSON_ID = HRE.PERSON_ID) or (HRE.PERSON_ID is null)) AND OOD.ORGANIZATION_ID = RSL.FROM_ORGANIZATION_ID AND OOD.LANGUAGE = USERENV('LANG')

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)
, PRL.QUANTITY
, MUOM.UNIT_OF_MEASURE_TL
, RT.UNIT_OF_MEASURE
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, 'REQ'
, 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.DESTINATION_CONTEXT
, RT.LOCATOR_ATTRIBUTE
, RT.SOURCE_DOC_UNIT_OF_MEASURE
, RSL.GOVERNMENT_CONTEXT
, 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
, PO_INQ_SV.GET_SO_NUMBER(TO_CHAR(PRL.REQUISITION_HEADER_ID)
, PRL.REQUISITION_LINE_ID) PO_NUMBER
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, OOD.NAME SOURCE_ORGANIZATION
, NULL
, RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER
, RSH.BILL_OF_LADING
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER
, RSH.WAYBILL_AIRBILL_NUM
, NULL
, RSL.QUANTITY_SHIPPED ORDERED_QTY
, RSL.UNIT_OF_MEASURE ORDERED_UOM
, TO_DATE(NULL) DUE_DATE_VENDOR
, RSH.EXPECTED_RECEIPT_DATE DUE_DATE_INTERNAL
, 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
, NULL
, RT.MOVEMENT_ID
, RT.INSPECTION_QUALITY_CODE
, RT.SUBSTITUTE_UNORDERED_CODE
, NULL
, OSH.HEADER_ID
, OSH.ORDER_NUMBER
, OOD.NAME SOURCE_ORGANIZATION
, NULL
, PRL.CLOSED_CODE
, PRL.CANCEL_FLAG
FROM RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, PO_REQUISITION_LINES PRL
, PO_REQUISITION_HEADERS PRH
, PER_ALL_PEOPLE_F HRE
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, RCV_TRANSACTIONS RT
, MTL_UNITS_OF_MEASURE MUOM
, OE_ORDER_HEADERS_ALL OSH
, PO_SYSTEM_PARAMETERS POSP
WHERE OSH.ORDER_SOURCE_ID = POSP.ORDER_SOURCE_ID
AND OSH.ORIG_SYS_DOCUMENT_REF = PRH.SEGMENT1
AND OSH.SOURCE_DOCUMENT_ID = PRH.REQUISITION_HEADER_ID
AND RT.TRANSACTION_TYPE = 'DELIVER'
AND RT.SOURCE_DOCUMENT_CODE = 'REQ'
AND (RT.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE)
AND (RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID)
AND (RSL.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 RT.ROUTING_HEADER_ID = 3
AND HRE.PERSON_ID (+) = RT.EMPLOYEE_ID
AND (HRE.EFFECTIVE_END_DATE = (SELECT MAX(PF.EFFECTIVE_END_DATE)
FROM PER_PEOPLE_F PF
WHERE PF.PERSON_ID = HRE.PERSON_ID) OR (HRE.PERSON_ID IS NULL))
AND OOD.ORGANIZATION_ID = RSL.FROM_ORGANIZATION_ID
AND OOD.LANGUAGE = USERENV('LANG')