select *
, case
when tbldash.pallet_number <> '' then tbldash.overall_weight
else fg.weight_per_lot
end as 'weight'
, case
when tbldash.pallet_number <> '' then tbldash.overall_volume
else fg.width_per_lot * fg.length_per_lot * fg.height_per_lot
end as 'volume'
, fgkr.criteria
from
(
select plt.pallet_number, plt.overall_weight, plt.overall_volume, temp.part_supply, temp.part_demand, temp.warehouse_code
from artemis_temporary.wo_req_temporaries as temp
left join pallets plt on plt.id = temp.pallet_id
) tbldash
left join artemis_prd.finished_goods fg on fg.part_supply = tbldash.part_supply and fg.part_demand = tbldash.part_demand and tbldash.pallet_number = ''
left join warehouses ware on ware.warehouse_code = tbldash.warehouse_code and ware.start_effective <= '2023/05/11' and (ware.end_effective >= '2023/05/11' or ware.end_effective is null)
left join artemis_prd.finished_goods fgkr on fgkr.part_supply = tbldash.part_supply and fgkr.part_demand = tbldash.part_demand