VIEW: APPS.POS_VIEW_ASN

Source

View Text - Preformatted

SELECT DISTINCT RHI.SHIPMENT_NUM, RHI.SHIPPED_DATE, RHI.EXPECTED_RECEIPT_DATE, RHI.FREIGHT_CARRIER_CODE, RHI.INVOICE_NUM, RHI.WAYBILL_AIRBILL_NUM, RHI.NUM_OF_CONTAINERS, RHI.FREIGHT_TERMS, RHI.BILL_OF_LADING, RHI.PACKING_SLIP, RHI.PACKAGING_CODE, POV.VENDOR_NAME, POV.SEGMENT1 VENDOR_NUMBER, PVS.VENDOR_SITE_CODE, RHI.SPECIAL_HANDLING_CODE, RHI.TAR_WEIGHT, RHI.TAR_WEIGHT_UOM_CODE, RHI.NET_WEIGHT, RHI.NET_WEIGHT_UOM_CODE, RHI.COMMENTS, '' CANCELLATION_STATUS, '' PROCESSING_STATUS, RHI.VENDOR_ID, RHI.VENDOR_SITE_ID, NULL PAYMENT_STATUS_FLAG, RHI.CREATION_DATE, RHI.HEADER_INTERFACE_ID HEADER_ID , RHI.SHIP_FROM_LOCATION_ID, substr(PS.party_site_number,1,instr(PS.party_site_number,'|')) location_code FROM RCV_HEADERS_INTERFACE RHI, RCV_TRANSACTIONS_INTERFACE RTI, PO_VENDORS POV, PO_VENDOR_SITES_ALL PVS, HZ_PARTY_SITES PS WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID AND POV.VENDOR_ID = RHI.VENDOR_ID AND PVS.VENDOR_SITE_ID (+) = RHI.VENDOR_SITE_ID AND RHI.ASN_TYPE IN ('ASN', 'ASBN') AND RHI.TRANSACTION_TYPE <> 'CANCEL' AND RHI.SHIP_FROM_LOCATION_ID = PS.LOCATION_ID (+) UNION ALL SELECT DISTINCT RSH.SHIPMENT_NUM, RSH.SHIPPED_DATE, RSH.EXPECTED_RECEIPT_DATE, RSH.FREIGHT_CARRIER_CODE, RSH.INVOICE_NUM, RSH.WAYBILL_AIRBILL_NUM, RSH.NUM_OF_CONTAINERS, RSH.FREIGHT_TERMS, RSH.BILL_OF_LADING, RSH.PACKING_SLIP, RSH.PACKAGING_CODE, POV.VENDOR_NAME, POV.SEGMENT1 VENDOR_NUMBER, PVS.VENDOR_SITE_CODE, RSH.SPECIAL_HANDLING_CODE, RSH.TAR_WEIGHT, RSH.TAR_WEIGHT_UOM_CODE, RSH.NET_WEIGHT, RSH.NET_WEIGHT_UOM_CODE, RSH.COMMENTS, '' CANCELLATION_STATUS, '' PROCESSING_STATUS, RSH.VENDOR_ID, RSH.VENDOR_SITE_ID, API.PAYMENT_STATUS_FLAG, RSH.CREATION_DATE, RSH.SHIPMENT_HEADER_ID HEADER_ID , RSH.SHIP_FROM_LOCATION_ID, substr(PS.party_site_number,1,instr(PS.party_site_number,'|')) location_code FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, AP_INVOICES_ALL API, PO_VENDORS POV, PO_VENDOR_SITES_ALL PVS, HZ_PARTY_SITES PS WHERE RSH.RECEIPT_SOURCE_CODE = 'VENDOR' AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND RSH.ASN_TYPE IN ('ASN', 'ASBN') AND API.INVOICE_NUM (+) = RSH.INVOICE_NUM AND API.VENDOR_ID (+) = RSH.VENDOR_ID AND POV.VENDOR_ID = RSH.VENDOR_ID AND PVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID AND RSH.SHIP_FROM_LOCATION_ID = PS.LOCATION_ID (+) ORDER BY CREATION_DATE DESC, SHIPMENT_NUM DESC

View Text - HTML Formatted

SELECT DISTINCT RHI.SHIPMENT_NUM
, RHI.SHIPPED_DATE
, RHI.EXPECTED_RECEIPT_DATE
, RHI.FREIGHT_CARRIER_CODE
, RHI.INVOICE_NUM
, RHI.WAYBILL_AIRBILL_NUM
, RHI.NUM_OF_CONTAINERS
, RHI.FREIGHT_TERMS
, RHI.BILL_OF_LADING
, RHI.PACKING_SLIP
, RHI.PACKAGING_CODE
, POV.VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, PVS.VENDOR_SITE_CODE
, RHI.SPECIAL_HANDLING_CODE
, RHI.TAR_WEIGHT
, RHI.TAR_WEIGHT_UOM_CODE
, RHI.NET_WEIGHT
, RHI.NET_WEIGHT_UOM_CODE
, RHI.COMMENTS
, '' CANCELLATION_STATUS
, '' PROCESSING_STATUS
, RHI.VENDOR_ID
, RHI.VENDOR_SITE_ID
, NULL PAYMENT_STATUS_FLAG
, RHI.CREATION_DATE
, RHI.HEADER_INTERFACE_ID HEADER_ID
, RHI.SHIP_FROM_LOCATION_ID
, SUBSTR(PS.PARTY_SITE_NUMBER
, 1
, INSTR(PS.PARTY_SITE_NUMBER
, '|')) LOCATION_CODE
FROM RCV_HEADERS_INTERFACE RHI
, RCV_TRANSACTIONS_INTERFACE RTI
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, HZ_PARTY_SITES PS
WHERE RHI.HEADER_INTERFACE_ID = RTI.HEADER_INTERFACE_ID
AND POV.VENDOR_ID = RHI.VENDOR_ID
AND PVS.VENDOR_SITE_ID (+) = RHI.VENDOR_SITE_ID
AND RHI.ASN_TYPE IN ('ASN'
, 'ASBN')
AND RHI.TRANSACTION_TYPE <> 'CANCEL'
AND RHI.SHIP_FROM_LOCATION_ID = PS.LOCATION_ID (+) UNION ALL SELECT DISTINCT RSH.SHIPMENT_NUM
, RSH.SHIPPED_DATE
, RSH.EXPECTED_RECEIPT_DATE
, RSH.FREIGHT_CARRIER_CODE
, RSH.INVOICE_NUM
, RSH.WAYBILL_AIRBILL_NUM
, RSH.NUM_OF_CONTAINERS
, RSH.FREIGHT_TERMS
, RSH.BILL_OF_LADING
, RSH.PACKING_SLIP
, RSH.PACKAGING_CODE
, POV.VENDOR_NAME
, POV.SEGMENT1 VENDOR_NUMBER
, PVS.VENDOR_SITE_CODE
, RSH.SPECIAL_HANDLING_CODE
, RSH.TAR_WEIGHT
, RSH.TAR_WEIGHT_UOM_CODE
, RSH.NET_WEIGHT
, RSH.NET_WEIGHT_UOM_CODE
, RSH.COMMENTS
, '' CANCELLATION_STATUS
, '' PROCESSING_STATUS
, RSH.VENDOR_ID
, RSH.VENDOR_SITE_ID
, API.PAYMENT_STATUS_FLAG
, RSH.CREATION_DATE
, RSH.SHIPMENT_HEADER_ID HEADER_ID
, RSH.SHIP_FROM_LOCATION_ID
, SUBSTR(PS.PARTY_SITE_NUMBER
, 1
, INSTR(PS.PARTY_SITE_NUMBER
, '|')) LOCATION_CODE
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, AP_INVOICES_ALL API
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, HZ_PARTY_SITES PS
WHERE RSH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSH.ASN_TYPE IN ('ASN'
, 'ASBN')
AND API.INVOICE_NUM (+) = RSH.INVOICE_NUM
AND API.VENDOR_ID (+) = RSH.VENDOR_ID
AND POV.VENDOR_ID = RSH.VENDOR_ID
AND PVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID
AND RSH.SHIP_FROM_LOCATION_ID = PS.LOCATION_ID (+) ORDER BY CREATION_DATE DESC
, SHIPMENT_NUM DESC