[oliver]

the vsor has both sorts of shipments asfar as i know, so sell and buy. B and S are saved in the same table, differentiated by the field "perspective" in the shipment table

[DDhiren]

Here is the query (Replace RELEASE1 in the below query with your Order Release ID).

SELECT S.SHIPMENT_GID FROM SHIPMENT S, SHIPMENT_STOP_D SD, S_SHIP_UNIT_LINE SL WHERE S.SHIPMENT_GID = SD.SHIPMENT_GID AND SD.S_SHIP_UNIT_GID = SL.S_SHIP_UNIT_GID AND SL.ORDER_RELEASE_GID ='RELEASE1' AND SD.STOP_NUM = 1

[gashoks]

Hi Friends,

I have done it today. I have come up with query to link the buy and sell side shipment.

Thanks to Hrishikesh and Oliver.

With the solution, my requirement was also includeing the audit data has to be included like Order Creation Date and Shipment Creation Date. Have anybody have idea on the same.

I am able to see the Estimated Delivery Date and Pick up date.

Regards

Ashok G

[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;

[ravintag]

You may use this query to find the same:
SELECT SHIPMENT_GID, PERSPECTIVE FROM SHIPMENT WHERE SHIPMENT_GID IN (SELECT SHIPMENT_GID FROM SHIPMENT_STOP_D WHERE S_SHIP_UNIT_GID IN(SELECT S_SHIP_UNIT_GID FROM S_SHIP_UNIT_LINE WHERE ORDER_RELEASE_GID LIKE 'XYZ.ABCD'))

I hope this query had been of help to you.

[Srivathsana]

Hi,

Assuming you are talking about external statuses,You can get this done by joining the below tables :

SHIPMENT_STATUS - CONTAINS THE SHIPMENT STATUS (SELL AND BUY)
VIEW_SHIPMENT_ORDER_RELEASE - CONTAINS BUY AND SELL SHIPMENT ID'S
ORDER RELEASE - CONTAINS THE ORDER RELEASE DETAILS AND ORDER BASE ID'S
OB_ORDER_BASE - CONTAINS OB INSERT DATE
SHIPMENT - CONTAINS SHIPMENT INSERT DATE

Please repost should you require more help on this