How Order Release is linked with Buy Shipment and Sell Shipment?

[gashoks]

Hi,

I was working through getting Buy Shipment and Sell Shipment data for a particular Order Release, but unable to link.

Kindly advice.

Ashok G

[hrishikesh1976]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

shipments are related to order release in view table view_shipment_order_release

[oliver]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

try the "view_shipment_order_release table"

Rgds
Oliver

[bmj_23]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

That view obviously provides that information.

Otherwise, take a look at the s_ship_unit_line table. That has the link from the shipment ship unit (the ship unit on a shipment) and the associated order base, order release and order release line. The ship unit line is associated with a ship unit which is linked to a shipment equipment which in turn is linked to a shipment.

[gashoks]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

I checked the S_SHIP_UNIT_LINE and VIEW_SHIPMENT_ORDER_RELEASE, but it is not useful for my requirement.

My Problem is a order release will be having both SELL_SHIPMENT and BUY_SHIPMENT.

When i get a SELL_SHIPMENT, I should be able to obtain the Order relase detail and the orders BUY_SHIPMENT. and also the vice versa that if i have a BUY_SHIPMENT ID, I should be able to find the SELL_SHIPMENT Details.

[bmj_23]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

So what exactly are you looking for ? The sql code to find the buy and sell shipment ID's for a particular order release ID ?

[gashoks]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

Its like, I will provide the status of an Sell Shipment, based on the status, we have to fetch the orders whose sell shipment has those status and then its Order Release and its Buy Shipment.

[krishna.kk]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

Ashok,

Try creating a job from the oreder relese it will show you complete details of the buy and sellshipments that are build from the order release .

Rgds
kishore

[krishna.kk]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

Ashok,

Sorry i got you wrong .

Use the smart links i.e after searching the sell shipments by a statys use the smartlinks(Right click + symbol near the shipment there you can find the option view releated order release that will tkae you to the order release and again go to the smartlinks of the order release u can find the option view releated buy shipments .

Rgds

[gashoks]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

Hi Krishna,

Thanks for the info. But my problem is how to retrive the data from back-end.

Regards

Ashok G

[oliver]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

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]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

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]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

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]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

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]

Re: How Order Release is linked with Buy Shipment and Sell Shipment?

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.