Hi all!
Im struggling with writing a SQL query for an automation agent in OTM Cloud.
What I want to achieve is that, based on the value of the corrospondig location refnum, a value is inserted on that shipment stop's attribute1.
So I have a shipment which has 3 stops, 2 pickup stops and 1 delivery stop. For the first pickup stop the location has 2 refnums: SLOT_BOOKING_LOADING and SLOT_BOOKING_UNLOADING. Because that location is assigned to be a pickup stop (P) we want to look at the SLOT_BOOKING_LOADING refnum instead of SLOT_BOOKING_UNLOADING and use that value to determine if a R or Y should be inserted in attribute1 for that shipment stop.
So if that location, which for this shipment is the first pickup stop, has the value for SLOT_BOOKING_LOADING: SLOT_BOOKING_LOADING_REQUIRED then we want to assign a Y to attribute1 for that shipment stop.
For the second shipment stop, which is also a pickup stop, we again look at the corrosponding location. Because this stop is also P we again want to look at the SLOT_BOOKING_LOADING refnum on the location. This refnum now has the value SLOT_BOOKING_LOADING_NOT_REQUIRED, so we want to assign a R to attribute1 for that shipment stop.
Last but not least for the last stop, which is a delivery stop, we again look at the corropsonding location. Now, because this stop is a D we want to look at the SLOT_BOOKING_UNLOADING refnum on that location. This refnum appears to hold SLOT_BOOKING_UNLOADING_NOT_REQUIRED, so we want to assign a R to attribute1 for that shipment stop.
I hope I made my issue clear and look forward to what kind of solutions could be applied here :)
I preferably want to solve this using a single query