update
	aggintel
join
	wb_transaction_work
		on wb_transaction_work.id = aggintel.SourceID
		and aggintel.SourceCode = 'WBD'
join
	wb_transaction
		on wb_transaction.id = wb_transaction_work.id
join
	wb_vessel
		on wb_vessel.id = wb_transaction_work.vessel_id
join
	trade_ships
		on trade_ships.LR_IMO_Ship_No = wb_vessel.imo_number
left join
	wb_supply_plant
		on wb_supply_plant.id = wb_transaction_work.supply_plant_id
left join
	TTN_ports.portsdata originportsdata
		on wb_supply_plant.MINT_PortID = originportsdata.PortID
left join
	wb_destination_port
		on wb_destination_port.id = wb_transaction_work.destination_port_id
left join
	TTN_ports.portsdata destinationportsdata
		on wb_destination_port.MINT_PortID = destinationportsdata.PortID
left join
	wb_seller
		on wb_seller.id = wb_transaction_work.seller_id
left join
	wb_destination_country
		on wb_destination_country.id = wb_transaction_work.destination_country_id
left join
	wb_source_country
		on wb_source_country.id = wb_transaction_work.source_country_id
left join
	LNGVoyageTimes
		on LNGVoyageTimes.OriginCountry = case
			when originportsdata.Country is null then wb_source_country.MINT_Country
			else originportsdata.Country end
		and LNGVoyageTimes.DestinationCountry = case
			when destinationportsdata.Country is null then wb_destination_country.MINT_Country
			else destinationportsdata.Country end
set
	aggintel.OriginPortAISDepartureDate =
		case
		when aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No
			or aggintel.OriginPortID != originportsdata.PortID
			or originportsdata.PortID is null
			or (aggintel.DepartureDate != date_add(wb_transaction_work.date_arrived, interval (LNGVoyageTimes.MinDays * -1) DAY)
				and LNGVoyageTimes.MinDays is not null)
			then null
		else aggintel.OriginPortAISDepartureDate end,
	aggintel.OriginAISVerfied =
		case
		when aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No
			or aggintel.OriginPortID != originportsdata.PortID
			or originportsdata.PortID is null
			or (aggintel.DepartureDate != date_add(wb_transaction_work.date_arrived, interval (LNGVoyageTimes.MinDays * -1) DAY)
				and LNGVoyageTimes.MinDays is not null)
			then null
		else aggintel.OriginAISVerfied end,
	aggintel.DepartureDate =
		case
		when (aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No
			or aggintel.OriginPortID != originportsdata.PortID
			or originportsdata.PortID is null
			or aggintel.DepartureDate != date_add(wb_transaction_work.date_arrived, interval -7 DAY))
			and LNGVoyageTimes.MinDays is null
			then date_add(wb_transaction_work.date_arrived, interval -7 DAY) #setting departure date to default 7 days prior to arrival
		when (aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No
			or aggintel.OriginPortID != originportsdata.PortID
			or originportsdata.PortID is null
			or aggintel.DepartureDate != date_add(wb_transaction_work.date_arrived, interval (LNGVoyageTimes.MinDays * -1) DAY))
			and LNGVoyageTimes.MinDays is not null
			then date_add(wb_transaction_work.date_arrived, interval (LNGVoyageTimes.MinDays * -1) DAY)
		else aggintel.DepartureDate end,
	aggintel.DestinationPortAISArrivalDate =
		case
		when aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No
			or aggintel.DestinationPortID != destinationportsdata.PortID
			or destinationportsdata.PortID is null
			then null
		else aggintel.DestinationPortAISArrivalDate end,
	aggintel.DestinationAISVerified =
		case
		when aggintel.LR_IMO_Ship_No != trade_ships.LR_IMO_Ship_No
			or aggintel.DestinationPortID != destinationportsdata.PortID
			or destinationportsdata.PortID is null
			then null
		else aggintel.DestinationAISVerified end,
	aggintel.LR_IMO_Ship_No = trade_ships.LR_IMO_Ship_No,
	aggintel.Ship_Name = trade_ships.Ship_Name,
	aggintel.grade_id = 35, #LNG
	aggintel.DateReported = wb_transaction_work.date_modified,
	aggintel.OriginPort = originportsdata.portname,
	aggintel.OriginPortID = originportsdata.PortID,
	aggintel.OriginCountry = 
		case
		when originportsdata.Country is null then wb_source_country.MINT_Country
		else originportsdata.Country end,
	aggintel.DestinationPort = destinationportsdata.portname,
	aggintel.DestinationPortID = destinationportsdata.PortID,
	aggintel.DestinationCountry = 
		case
		when destinationportsdata.Country is null then wb_destination_country.MINT_Country
		else destinationportsdata.Country end,
	aggintel.ArrivalDate = wb_transaction_work.date_arrived,
	aggintel.CargoVolM3 = round((wb_transaction_work.bcf / v_Factor), 0),
	aggintel.CargoWeightMT = wb_transaction_work.mt,
	aggintel.SpecificGradeName = 'LNG',
	aggintel.Charterer = wb_seller.name,
	aggintel.Seller = wb_seller.name,
	aggintel.Price = wb_transaction_work.spot_price_reported
where
#	wb_transaction_work.action = 'Update'
#and
	wb_transaction_work.date_modified >= wb_transaction.date_modified
and
	wb_transaction_work.date_modified in (select max(wtw2.date_modified) from wb_transaction_work wtw2
																				where wb_transaction_work.id = wtw2.id);