[acuartero]

View_Shipment_order release is somewhat broken (at least in 5.0) in that it does not determine which orders are on which stops (at the ship unit level). Because of this you will have problems matching buys to their corresponding sells when you have more than a 1:1 relationship between the two. This SQL will join buys with sells correctly by stop (this is for 5.0 but should work in 5.5). I use this as a starting point for many reports. It also includes location info which you can take out if it's not needed. This will only show shipments that have both related buys and sells if one is missing then this query will not display it (I use a different query that shows buys missing sells).

-AC

select distinct ore.ORDER_RELEASE_GID order_release,
shbuy.SHIPMENT_GID Buy_Shipment,
shsel.SHIPMENT_GID Sell_Shipment,
shbuy.TRANSPORT_MODE_GID Buy_T_Mode,
shsel.TRANSPORT_MODE_GID Sell_T_Mode,
shbuy.SERVPROV_GID Buy__Carrier,
shsel.SERVPROV_GID Sell__Carrier,
sst1.STOP_NUM Buy_stop_num,
sst2.STOP_NUM Sell_stop_num,
orig.LOCATION_NAME Origin,
orig.CITY Orig_City,
orig.PROVINCE_CODE Orig_State,
orig.POSTAL_CODE Orig_Zip,
dest.LOCATION_NAME Consignee,
dest.CITY Dest_City,
dest.PROVINCE_CODE Dest_State,
dest.POSTAL_CODE Dest_Zip
from ship_unit su, s_ship_unit ssu1, s_ship_unit ssu2, shipment_stop_d ssd1,
shipment_stop ss1, shipment_stop_d ssd2, shipment_stop sst1,
shipment_stop sst2, shipment shbuy, shipment shsel,
order_release ore, location orig, location dest
where su.SHIP_UNIT_GID = ssu1.SHIP_UNIT_GID
and su.SHIP_UNIT_GID = ssu2.SHIP_UNIT_GID
and ssu1.S_SHIP_UNIT_GID = ssd1.S_SHIP_UNIT_GID
and ssu2.S_SHIP_UNIT_GID = ssd2.S_SHIP_UNIT_GID
and ssd1.SHIPMENT_GID = shbuy.SHIPMENT_GID
and ssd2.SHIPMENT_GID = shsel.SHIPMENT_GID
and su.ORDER_RELEASE_GID = ore.ORDER_RELEASE_GID
and shbuy.SHIPMENT_GID = sst1.SHIPMENT_GID
and shsel.SHIPMENT_GID = sst2.SHIPMENT_GID
and ssd1.STOP_NUM = sst1.STOP_NUM
and ssd2.STOP_NUM = sst2.STOP_NUM
and ssd1.STOP_NUM > 1
and ssd2.STOP_NUM > 1
and shbuy.PERSPECTIVE = 'B'
and shsel.PERSPECTIVE = 'S'
-- Common Area
and ss1.LOCATION_GID = orig.LOCATION_GID
and sst1.LOCATION_GID = dest.LOCATION_GID
and shbuy.SHIPMENT_GID = ss1.SHIPMENT_GID
and ss1.STOP_NUM = 1
order by buy_shipment, order_release;