VIEW: APPS.POS_ASN_VIEW_FOR_SEARCH

Source

View Text - Preformatted

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')

View Text - HTML Formatted

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')