Untitled

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 DISTINCT bbs.warehouse_code, bbs.bin_code, plts.pallet_type, count(plts.pallet_type) from bin_balances as bbs
                        			inner join pallets plts on bbs.pallet_number = plts.pallet_number
                        			group by bbs.warehouse_code, bbs.bin_code, plts.pallet_type
                        			HAVING COUNT(plts.pallet_number) = 1
                              ) 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.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
                    where wb.bin_status = 'GUNA' and wb.warehouse_code = 'SDRAM' and wb.bin_code = 'L1AB02201' 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 */