CREATE OR REPLACE FUNCTION getMaterialLocationExemplaryStatus(p_exemplarystatusid int)
RETURNS TABLE (materiallocationid int) AS
/*************************************************************************************
  NAME: getMaterialLocationExemplaryStatus
  DESCRIPTION: Obtém as regras de localização de material que atendem o estado do exemplar.
**************************************************************************************/
$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 gtcmateriallocationexemplarystatus C1 WHERE C1.materiallocationid = PRINCIPAL.materiallocationid AND C1.rule = 'I') > 0 
                       THEN  
                           p_exemplarystatusid IN (SELECT I.exemplarystatusid FROM gtcmateriallocationexemplarystatus 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 gtcmateriallocationexemplarystatus C2 WHERE C2.materiallocationid = PRINCIPAL.materiallocationid AND C2.rule = 'E') > 0 
                       THEN
                           p_exemplarystatusid NOT IN (SELECT E.exemplarystatusid FROM gtcmateriallocationexemplarystatus E WHERE E.rule = 'E' AND E.materiallocationid = PRINCIPAL.materiallocationid)
                       ELSE
                           TRUE
                       END;    
          
END; 
$BODY$ 
language plpgsql;
