View: POS_ASN_VIEW_FOR_SEARCH

Product:	POS - iSupplier Portal
Description:
Implementation/DBA Data:	APPS.POS_ASN_VIEW_FOR_SEARCH

View Text

SELECT DISTINCT RHI.SHIPMENT_NUM
, DECODE(POR.RELEASE_NUM
, NULL
, POH.SEGMENT1
, POH.SEGMENT1 || '-' || POR.RELEASE_NUM)
, MTL.CONCATENATED_SEGMENTS
, RTI.VENDOR_ITEM_NUM
, POH.VENDOR_CONTACT_ID
, NVL( HRL.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 40)) SHIP_TO_LOCATION_CODE
, RHI.HEADER_INTERFACE_ID
FROM PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, RCV_HEADERS_INTERFACE RHI
, RCV_TRANSACTIONS_INTERFACE RTI
, MTL_SYSTEM_ITEMS_KFV MTL
, HR_LOCATIONS_ALL HRL
, HZ_LOCATIONS HZ
WHERE RTI.PO_HEADER_ID = POH.PO_HEADER_ID
AND RTI.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND RTI.HEADER_INTERFACE_ID = RHI.HEADER_INTERFACE_ID
AND RTI.ITEM_ID = MTL.INVENTORY_ITEM_ID(+)
AND RHI.ASN_TYPE IN ('ASN'
, 'ASBN')
AND HRL.LOCATION_ID (+) = RTI.SHIP_TO_LOCATION_ID
AND HZ.LOCATION_ID(+) = RTI.SHIP_TO_LOCATION_ID UNION SELECT DISTINCT RSH.SHIPMENT_NUM
, DECODE(POR.RELEASE_NUM
, NULL
, POH.SEGMENT1
, POH.SEGMENT1 || '-' || POR.RELEASE_NUM)
, MTL.CONCATENATED_SEGMENTS
, RSL.VENDOR_ITEM_NUM
, POH.VENDOR_CONTACT_ID
, NVL( HRL.LOCATION_CODE
, SUBSTR(RTRIM(HZ.ADDRESS1)||'-'||RTRIM(HZ.CITY)
, 1
, 40)) SHIP_TO_LOCATION_CODE
, RSH.SHIPMENT_HEADER_ID
FROM PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, HR_LOCATIONS_ALL HRL
, MTL_SYSTEM_ITEMS_KFV MTL
, HZ_LOCATIONS HZ
WHERE RSL.PO_HEADER_ID = POH.PO_HEADER_ID
AND RSL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND RSL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID (+)
AND RSL.SHIP_TO_LOCATION_ID = HZ.LOCATION_ID (+)
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSL.ITEM_ID = MTL.INVENTORY_ITEM_ID(+)
AND RSH.ASN_TYPE IN ('ASN'
, 'ASBN')

Columns