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