Untitled

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
        , wb.criteria
        , dim.width
        , dim.height
        , gccrt.reff2
        , bb.bin_code as 'blc_bin_code'
        , bb.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.sisavol - ((IFNULL(gcplt.reff2, 0 ) * (IFNULL(cntbindet.count, 0 ) + 1)) * IFNULL(dim.width, 0 ) * IFNULL(dim.height, 0 ))
            else wb.sisavol
        end as 'sisavol'
        , wb.sisabrt as 'sisabrt'
        , case 
            when wb.bin_type like '%RACK%' then bb.height - IFNULL(gctgg.reff2, 0 )
            else bb.height
        end as 'sisatgg'
        
        from 
        (
            select 
            wbs.warehouse_code
            , wbs.warehouse_level
            , wbs.bin_code
            , wbs.bin_type
            , wbs.zone
            , wbs.criteria
            , sum(wbs.remaining_volume) as 'sisavol'
            , sum(wbs.remaining_weight) as 'sisabrt'
            , wbs.bin_dimension_id
            , wbs.id
            from warehouse_bin wbs
            where wbs.bin_status = 'GUNA' and wbs.warehouse_code = 'sdram' and wbs.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 wbs.warehouse_code, wbs.bin_code  , wbs.bin_type , wbs.warehouse_level
            , wbs.zone
            , wbs.criteria
            , wbs.bin_dimension_id
            , wbs.id
        ) as 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, plts.pallet_type, plts.height from (
                SELECT a.warehouse_code, a.bin_code, a.pallet_number from bin_balances as a 
                GROUP BY a.warehouse_code, a.bin_code, a.pallet_number 
            ) bbs
            inner join pallets plts on plts.pallet_number = bbs.pallet_number
            GROUP by bbs.warehouse_code, bbs.bin_code, plts.pallet_type, plts.height
            having COUNT(plts.pallet_type) = 1
        ) as bb on bb.warehouse_code = wb.warehouse_code and bb.bin_code = wb.bin_code
        
        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
        and gccrt.reff3 = 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 = bb.pallet_type) 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 artemis_wm.warehouse_bin wbf on wbf.id = wo_req.bin_id_to and wo_req.bin_tabel_name_to = 'warehouse_bin'
        left join artemis_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 artemis_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` where `i1`.`warehouse_code` = 'sdram' and i1.sisavol-coalesce(i2.sumtmpvol,0) >= 3920000000.0000000