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