VIEW: APPS.RCV_MSH_V

Source

View Text - Preformatted

SELECT RSH.ROWID ROW_ID , RSH.SHIPMENT_HEADER_ID , RSH.LAST_UPDATED_BY , RSH.LAST_UPDATE_DATE , RSH.LAST_UPDATE_LOGIN , RSH.CREATED_BY , RSH.CREATION_DATE , RSH.PROGRAM_APPLICATION_ID , RSH.PROGRAM_ID , RSH.PROGRAM_UPDATE_DATE , RSH.REQUEST_ID , RSH.USSGL_TRANSACTION_CODE , RSH.GOVERNMENT_CONTEXT , RSH.COMMENTS , RSH.BILL_OF_LADING , RSH.EXPECTED_RECEIPT_DATE , RSH.FREIGHT_CARRIER_CODE , ORG.ORGANIZATION_NAME FROM_ORGANIZATION_NAME , RSH.NUM_OF_CONTAINERS , RSH.ORGANIZATION_ID FROM_ORGANIZATION_ID ,NULL ,NULL , RSH.PACKING_SLIP , RSH.EMPLOYEE_ID , RSH.RECEIPT_NUM , RSH.RECEIPT_SOURCE_CODE , RSH.SHIPMENT_NUM , RSH.SHIPPED_DATE , HR.LOCATION_CODE SHIP_TO_LOCATION , RSH.SHIP_TO_LOCATION_ID , RSH.WAYBILL_AIRBILL_NUM ,RSH.ASN_TYPE , RSH.ATTRIBUTE_CATEGORY , RSH.ATTRIBUTE1 , RSH.ATTRIBUTE2 , RSH.ATTRIBUTE3 , RSH.ATTRIBUTE4 , RSH.ATTRIBUTE5 , RSH.ATTRIBUTE6 , RSH.ATTRIBUTE7 , RSH.ATTRIBUTE8 , RSH.ATTRIBUTE9 , RSH.ATTRIBUTE10 , RSH.ATTRIBUTE11 , RSH.ATTRIBUTE12 , RSH.ATTRIBUTE13 , RSH.ATTRIBUTE14 , RSH.ATTRIBUTE15 , RSH.VENDOR_ID , RSH.SHIP_TO_ORG_ID , TO_NUMBER(NULL) FROM RCV_SHIPMENT_HEADERS RSH, HR_LOCATIONS_ALL_TL HR, ORG_ORGANIZATION_DEFINITIONS ORG WHERE RECEIPT_SOURCE_CODE IN ('INVENTORY', 'INTERNAL ORDER') AND HR.LOCATION_ID (+)= RSH.SHIP_TO_LOCATION_ID AND HR.LANGUAGE (+) = USERENV('LANG') AND ORG.ORGANIZATION_ID (+)= RSH.ORGANIZATION_ID UNION ALL SELECT RSH.ROWID ROW_ID , RSH.SHIPMENT_HEADER_ID , RSH.LAST_UPDATED_BY , RSH.LAST_UPDATE_DATE , RSH.LAST_UPDATE_LOGIN , RSH.CREATED_BY , RSH.CREATION_DATE , RSH.PROGRAM_APPLICATION_ID , RSH.PROGRAM_ID , RSH.PROGRAM_UPDATE_DATE , RSH.REQUEST_ID , RSH.USSGL_TRANSACTION_CODE , RSH.GOVERNMENT_CONTEXT , RSH.COMMENTS , RSH.BILL_OF_LADING , RSH.EXPECTED_RECEIPT_DATE , RSH.FREIGHT_CARRIER_CODE , NULL , RSH.NUM_OF_CONTAINERS , TO_NUMBER( NULL) , POV.VENDOR_NAME VENDOR_NAME , POVS.VENDOR_SITE_CODE VENDOR_SITE , RSH.PACKING_SLIP , RSH.EMPLOYEE_ID , RSH.RECEIPT_NUM , RSH.RECEIPT_SOURCE_CODE , RSH.SHIPMENT_NUM , RSH.SHIPPED_DATE , HR.LOCATION_CODE SHIP_TO_LOCATION , RSH.SHIP_TO_LOCATION_ID , RSH.WAYBILL_AIRBILL_NUM , RSH.ASN_TYPE , RSH.ATTRIBUTE_CATEGORY , RSH.ATTRIBUTE1 , RSH.ATTRIBUTE2 , RSH.ATTRIBUTE3 , RSH.ATTRIBUTE4 , RSH.ATTRIBUTE5 , RSH.ATTRIBUTE6 , RSH.ATTRIBUTE7 , RSH.ATTRIBUTE8 , RSH.ATTRIBUTE9 , RSH.ATTRIBUTE10 , RSH.ATTRIBUTE11 , RSH.ATTRIBUTE12 , RSH.ATTRIBUTE13 , RSH.ATTRIBUTE14 , RSH.ATTRIBUTE15 , POV.VENDOR_ID , RSH.SHIP_TO_ORG_ID , RSH.VENDOR_SITE_ID FROM RCV_SHIPMENT_HEADERS RSH, HR_LOCATIONS_ALL_TL HR, PO_VENDORS POV, PO_VENDOR_SITES POVS WHERE (RECEIPT_SOURCE_CODE = 'VENDOR' AND RSH.ASN_TYPE IN ('ASN','ASBN')) AND HR.LOCATION_ID (+) = RSH.SHIP_TO_LOCATION_ID AND HR.LANGUAGE (+) = USERENV('LANG') AND POV.VENDOR_ID = RSH.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID AND (EXISTS (SELECT 1 FROM RCV_SHIPMENT_LINES RSL WHERE RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_STATUS_CODE IN ('EXPECTED','PARTIALLY RECEIVED','FULLY RECEIVED') AND NOT EXISTS (SELECT 1 FROM RCV_TRANSACTIONS_INTERFACE RTI WHERE RTI.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID)))

View Text - HTML Formatted

SELECT RSH.ROWID ROW_ID
, RSH.SHIPMENT_HEADER_ID
, RSH.LAST_UPDATED_BY
, RSH.LAST_UPDATE_DATE
, RSH.LAST_UPDATE_LOGIN
, RSH.CREATED_BY
, RSH.CREATION_DATE
, RSH.PROGRAM_APPLICATION_ID
, RSH.PROGRAM_ID
, RSH.PROGRAM_UPDATE_DATE
, RSH.REQUEST_ID
, RSH.USSGL_TRANSACTION_CODE
, RSH.GOVERNMENT_CONTEXT
, RSH.COMMENTS
, RSH.BILL_OF_LADING
, RSH.EXPECTED_RECEIPT_DATE
, RSH.FREIGHT_CARRIER_CODE
, ORG.ORGANIZATION_NAME FROM_ORGANIZATION_NAME
, RSH.NUM_OF_CONTAINERS
, RSH.ORGANIZATION_ID FROM_ORGANIZATION_ID
, NULL
, NULL
, RSH.PACKING_SLIP
, RSH.EMPLOYEE_ID
, RSH.RECEIPT_NUM
, RSH.RECEIPT_SOURCE_CODE
, RSH.SHIPMENT_NUM
, RSH.SHIPPED_DATE
, HR.LOCATION_CODE SHIP_TO_LOCATION
, RSH.SHIP_TO_LOCATION_ID
, RSH.WAYBILL_AIRBILL_NUM
, RSH.ASN_TYPE
, RSH.ATTRIBUTE_CATEGORY
, RSH.ATTRIBUTE1
, RSH.ATTRIBUTE2
, RSH.ATTRIBUTE3
, RSH.ATTRIBUTE4
, RSH.ATTRIBUTE5
, RSH.ATTRIBUTE6
, RSH.ATTRIBUTE7
, RSH.ATTRIBUTE8
, RSH.ATTRIBUTE9
, RSH.ATTRIBUTE10
, RSH.ATTRIBUTE11
, RSH.ATTRIBUTE12
, RSH.ATTRIBUTE13
, RSH.ATTRIBUTE14
, RSH.ATTRIBUTE15
, RSH.VENDOR_ID
, RSH.SHIP_TO_ORG_ID
, TO_NUMBER(NULL)
FROM RCV_SHIPMENT_HEADERS RSH
, HR_LOCATIONS_ALL_TL HR
, ORG_ORGANIZATION_DEFINITIONS ORG
WHERE RECEIPT_SOURCE_CODE IN ('INVENTORY'
, 'INTERNAL ORDER')
AND HR.LOCATION_ID (+)= RSH.SHIP_TO_LOCATION_ID
AND HR.LANGUAGE (+) = USERENV('LANG')
AND ORG.ORGANIZATION_ID (+)= RSH.ORGANIZATION_ID UNION ALL SELECT RSH.ROWID ROW_ID
, RSH.SHIPMENT_HEADER_ID
, RSH.LAST_UPDATED_BY
, RSH.LAST_UPDATE_DATE
, RSH.LAST_UPDATE_LOGIN
, RSH.CREATED_BY
, RSH.CREATION_DATE
, RSH.PROGRAM_APPLICATION_ID
, RSH.PROGRAM_ID
, RSH.PROGRAM_UPDATE_DATE
, RSH.REQUEST_ID
, RSH.USSGL_TRANSACTION_CODE
, RSH.GOVERNMENT_CONTEXT
, RSH.COMMENTS
, RSH.BILL_OF_LADING
, RSH.EXPECTED_RECEIPT_DATE
, RSH.FREIGHT_CARRIER_CODE
, NULL
, RSH.NUM_OF_CONTAINERS
, TO_NUMBER( NULL)
, POV.VENDOR_NAME VENDOR_NAME
, POVS.VENDOR_SITE_CODE VENDOR_SITE
, RSH.PACKING_SLIP
, RSH.EMPLOYEE_ID
, RSH.RECEIPT_NUM
, RSH.RECEIPT_SOURCE_CODE
, RSH.SHIPMENT_NUM
, RSH.SHIPPED_DATE
, HR.LOCATION_CODE SHIP_TO_LOCATION
, RSH.SHIP_TO_LOCATION_ID
, RSH.WAYBILL_AIRBILL_NUM
, RSH.ASN_TYPE
, RSH.ATTRIBUTE_CATEGORY
, RSH.ATTRIBUTE1
, RSH.ATTRIBUTE2
, RSH.ATTRIBUTE3
, RSH.ATTRIBUTE4
, RSH.ATTRIBUTE5
, RSH.ATTRIBUTE6
, RSH.ATTRIBUTE7
, RSH.ATTRIBUTE8
, RSH.ATTRIBUTE9
, RSH.ATTRIBUTE10
, RSH.ATTRIBUTE11
, RSH.ATTRIBUTE12
, RSH.ATTRIBUTE13
, RSH.ATTRIBUTE14
, RSH.ATTRIBUTE15
, POV.VENDOR_ID
, RSH.SHIP_TO_ORG_ID
, RSH.VENDOR_SITE_ID
FROM RCV_SHIPMENT_HEADERS RSH
, HR_LOCATIONS_ALL_TL HR
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
WHERE (RECEIPT_SOURCE_CODE = 'VENDOR'
AND RSH.ASN_TYPE IN ('ASN'
, 'ASBN'))
AND HR.LOCATION_ID (+) = RSH.SHIP_TO_LOCATION_ID
AND HR.LANGUAGE (+) = USERENV('LANG')
AND POV.VENDOR_ID = RSH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID
AND (EXISTS (SELECT 1
FROM RCV_SHIPMENT_LINES RSL
WHERE RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_STATUS_CODE IN ('EXPECTED'
, 'PARTIALLY RECEIVED'
, 'FULLY RECEIVED')
AND NOT EXISTS (SELECT 1
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID)))