Untitled
unknown
plain_text
7 months ago
6.7 kB
2
Indexable
Never
select `i1`.*, `i2`.`sumtmpvol`, `i2`.`sumtmpwei`, `i2`.`warehouse_code` as `wc`, `i2`.`bin_code` as `bc` from (select wb.warehouse_code , wb.warehouse_level , wb.bin_code , wb.bin_type , wb.zone , sum(wb.remaining_volume) , sum(wb.remaining_weight) , wb.criteria , dim.width , dim.height , gccrt.reff2 , bb.bin_code as 'blc_bin_code' , plt.pallet_type , IFNULL(gcplt.reff2, 0 ) as 'y' , IFNULL(gctgg.reff2, 0 ) as 'v' , IFNULL(cntbindet.count, 0 ) as 'z' , case when wb.bin_type like '%RACK%' then wb.remaining_volume - ((IFNULL(gcplt.reff2, 0 ) * (IFNULL(cntbindet.count, 0 ) + 1)) * IFNULL(dim.width, 0 ) * IFNULL(dim.height, 0 )) else wb.remaining_volume end as 'sisavol' , wb.remaining_weight as 'sisabrt' , case when wb.bin_type like '%RACK%' then plt.height - IFNULL(gctgg.reff2, 0 ) else plt.height end as 'sisatgg' from warehouse_bin wb inner join (SELECT dims.id, dims.width, dims.height FROM bin_dimensions as dims) as dim on dim.id = wb.bin_dimension_id left join (select bbs.warehouse_code, bbs.bin_code, bbs.pallet_number from bin_balances as bbs) as bb on bb.warehouse_code = wb.warehouse_code and bb.bin_code = wb.bin_code left join (SELECT plts.pallet_number, plts.pallet_type, plts.height from pallets as plts) as plt on plt.pallet_number = bb.pallet_number inner join artemis_global.general_codes gcbintpy on gcbintpy.section = 'WMS' and gcbintpy.label = 'MASTER_BIN' and gcbintpy.reff1 = 'BIN_TYPE' and gcbintpy.reff2 = wb.bin_type inner join artemis_global.general_codes gccrt on gccrt.section = 'WMS' and gccrt.label = 'FM_PENCARIAN_BIN' and gccrt.reff1 = 'CRITERIA' and gccrt.reff2 = wb.criteria left join artemis_global.general_codes gcplt on gcplt.section = 'WMS' and gcplt.label = 'FM_PENCARIAN_BIN' and gcplt.reff1 = 'TOLERANCE_LENGTH' left join artemis_global.general_codes gctgg on gctgg.section = 'WMS' and gctgg.label = 'FM_PENCARIAN_BIN' and gctgg.reff1 = 'TOLERANCE_HEIGHT' left join (select warehouse_bin_id, pallet_type, count(id) as 'count' from warehouse_bin_details group by warehouse_bin_id, pallet_type ) as cntbindet on cntbindet.warehouse_bin_id = wb.id and cntbindet.pallet_type = plt.pallet_type where wb.bin_status = 'GUNA' and wb.warehouse_code = 'SDRAM' and wb.bin_type not in (select gc.reff2 from artemis_global.general_codes gc where gc.section = 'WMS' and gc.label = 'FM_PENCARIAN_BIN' and gc.reff1 = 'LOC_TYPE_NO_STOK') group by wb.warehouse_code, wb.bin_code, wb.warehouse_level, wb.bin_type, wb.zone, wb.criteria, cntbindet.count, wb.remaining_volume, wb.remaining_weight, gccrt.reff2, gcplt.reff2, gctgg.reff2) as `i1` left join (select wo_req.warehouse_code, wbf.bin_code, sum(wo_req.volume) as 'sumtmpvol', sum(wo_req.weight) as 'sumtmpwei' from artemis_temporary.wo_req_temporaries wo_req left join dbtrialjef_wm.warehouse_bin wbf on wbf.id = wo_req.bin_id_to and wo_req.bin_tabel_name_to = 'warehouse_bin' left join dbtrialjef_wm.warehouse_bin_details wbdf on wbdf.id = wo_req.bin_id_to and wo_req.bin_tabel_name_to = 'warehouse_bin_details' left join dbtrialjef_wm.warehouse_bin wbdhf on wbdhf.id = wbdf.warehouse_bin_id group by warehouse_code, bin_code) as `i2` on `i1`.`warehouse_code` = `i2`.`warehouse_code` and `i1`.`bin_code` = `i2`.`bin_code` left join (select wb.id, wb.warehouse_code as warehouse, wb.warehouse_level as level, wb.bin_code as bin, bb.part_demand, bbd.batch, sum(bbd.admin_qty) as 'sumadmqty' from bin_balances bb inner join warehouse_bin wb on wb.warehouse_code = bb.warehouse_code and wb.bin_code = bb.bin_code inner join bin_detail_balances bbd on bbd.warehouse_code = bb.warehouse_code and bbd.location = bb.location and bbd.part_number = bb.part_number and bbd.lot = bb.lot and bbd.bin_code = bb.bin_code and bbd.pallet_number = bb.pallet_number where wb.bin_type not in (select reff2 from artemis_global.general_codes where section = 'WMS' and label = 'FM_PENCARIAN_BIN' and reff1 = 'LOC_TYPE_NO_STOK') and wb.warehouse_code = 'SDRAM' group by wb.id, wb.warehouse_code, wb.warehouse_level, wb.bin_code, bb.part_demand, bbd.batch) as `i3` on `i3`.`warehouse` = `i2`.`warehouse_code` and `i3`.`bin` = `i2`.`bin_code` left join (select wb.id, wb.warehouse_code as warehouse, wb.warehouse_level as level, wb.bin_code as bin, bb.part_demand, bbd.batch, sum(bbd.admin_qty) as 'sumadmqty' from bin_balances bb inner join warehouse_bin wb on wb.warehouse_code = bb.warehouse_code and wb.bin_code = bb.bin_code inner join bin_detail_balances bbd on bbd.warehouse_code = bb.warehouse_code and bbd.location = bb.location and bbd.part_number = bb.part_number and bbd.lot = bb.lot and bbd.bin_code = bb.bin_code and bbd.pallet_number = bb.pallet_number where wb.bin_type not in (select reff2 from artemis_global.general_codes where section = 'WMS' and label = 'FM_PENCARIAN_BIN' and reff1 = 'LOC_TYPE_NO_STOK') and wb.warehouse_code = 'SDRAM' group by wb.id, wb.warehouse_code, wb.warehouse_level, wb.bin_code, bb.part_demand, bbd.batch) as `i4` on `i4`.`warehouse` = `i2`.`warehouse_code` and `i4`.`bin` = `i2`.`bin_code` where `i1`.`warehouse_code` = 'sdram' and i1.sisavol-coalesce(i2.sumtmpvol,0) >= 3920000000.0000000 and `zone` = 'L2.0' and `zone` = 'L2.0'