CREATE OR REPLACE FUNCTION getMaterialLocationKnowledgeArea(p_knowledgeareaid varchar)
RETURNS TABLE (materiallocationid int) AS
/*************************************************************************************
  NAME: getMaterialLocationKnowledgeArea
  DESCRIPTION: Obtém as regras de localização de material que atende a área de conhecimento.
**************************************************************************************/
$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 gtcmateriallocationknowledgearea C1 WHERE C1.materiallocationid = PRINCIPAL.materiallocationid AND C1.rule = 'I') > 0 
                       THEN  
                           p_knowledgeareaid IN (SELECT I.knowledgeareaid FROM gtcmateriallocationknowledgearea I WHERE I.rule = 'I' AND I.materiallocationid = PRINCIPAL.materiallocationid) 
                       ELSE
                           TRUE
                       END
                       -- Se tiver filtros 'E', faz a pesquisa por eles, caso contrário desconsidera o filtro.
                       AND CASE WHEN (SELECT count(*) FROM gtcmateriallocationknowledgearea C2 WHERE C2.materiallocationid = PRINCIPAL.materiallocationid AND C2.rule = 'E') > 0 
                       THEN
                           p_knowledgeareaid NOT IN (SELECT E.knowledgeareaid FROM gtcmateriallocationknowledgearea E WHERE E.rule = 'E' AND E.materiallocationid = PRINCIPAL.materiallocationid)
                       ELSE
                           TRUE
                       END;    
          
END; 
$BODY$ 
language plpgsql;
