CREATE OR REPLACE FUNCTION getMaterialLocationClassification(p_classification varchar)
RETURNS TABLE (materiallocationid int) AS
/*************************************************************************************
  NAME: getMaterialLocationClassification
  DESCRIPTION: Obtém as regras de localização de material que atendem a classificação.
**************************************************************************************/
$BODY$ 
BEGIN
    RETURN QUERY SELECT distinct(PRINCIPAL.materiallocationid) 
                   FROM gtcmateriallocation PRINCIPAL 
                  WHERE 
                        -- Se tiver filtros 'Inclui', faz a pesquisa por eles, caso contrário desconsidera o filtro.
                        CASE WHEN (SELECT count(*) FROM gtcmateriallocationclassification C1 WHERE C1.materiallocationid = PRINCIPAL.materiallocationid) > 0 
                        THEN  
                            PRINCIPAL.materiallocationid IN (SELECT I.materiallocationid 
                                                               FROM gtcmateriallocationclassification I 
                                                              WHERE preparetopographicindex(removePrefixForClassification(split_part(p_classification, ' ', 1)), split_part(p_classification, ' ', 2)) 
                                                            BETWEEN preparetopographicindex(removePrefixForClassification( split_part(I.beginclassification, ' ', 1)), split_part(I.beginclassification, ' ', 2)) 
                                                                AND preparetopographicindex(removePrefixForClassification(split_part(I.endclassification, ' ', 1) ), split_part(I.endclassification, ' ', 2)) 
                                                                AND I.materiallocationid = PRINCIPAL.materiallocationid ) 
                        ELSE
                            TRUE
                        END;
END; 
$BODY$ 
language plpgsql;
