View: POS_VIEW_ASN

Product:	POS - iSupplier Portal
Description:	This View is used to Display ASN's in the Buyers System
Implementation/DBA Data:	APPS.POS_VIEW_ASN

View Text

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

Columns