Untitled

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_code = 'L2AX08101' 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 
) 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
	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

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			


/* ASLI */