CREATE OR REPLACE FUNCTION FCT_BUSCA_DESCRICAO_ESCOPO (
    P_NRTIPOORCAMENTO  IN  FCT_DESCAGRUPESCOPO.NRTIPOORCAMENTO%TYPE,
    P_IDTPFATURA       IN  FCT_DESCAGRUPESCOPO.IDTPFATURA%TYPE,
    P_CDFILIALFATURA   IN  FCT_DESCAGRUPESCOPO.CDFILIALFATURA%TYPE,
    P_NRPARCFATURA     IN  FCT_DESCAGRUPESCOPO.NRPARCFATURA%TYPE,
    P_CDTIPOOPERFATURA IN  FCT_DESCAGRUPESCOPO.CDTIPOOPERFATURA%TYPE,
    P_CDSERIENF        IN  FCT_DESCAGRUPESCOPO.CDSERIENF%TYPE,
    P_CDEMPRESA        IN  FCT_DESCAGRUPESCOPO.CDEMPRESA%TYPE,
    P_CDINSCESTA       IN  FCT_DESCAGRUPESCOPO.CDINSCESTA%TYPE,
    P_CDINSCMUNI       IN  FCT_DESCAGRUPESCOPO.CDINSCMUNI%TYPE,
    P_NRORG            IN  FCT_DESCAGRUPESCOPO.NRORG%TYPE,
    P_IDPREVCOBRFUT    IN  FCT_DESCAGRUPESCOPO.IDPREVCOBRFUT%TYPE DEFAULT 'N',
	  P_IDIOMA           IN  IDIOMA.CDIDIOM%TYPE DEFAULT NULL
)
RETURN VARCHAR2
AS
    V_DESCRICAO FCT_DESCAGRUPESCOPO.DSNOMEAGRUPAMENTO%TYPE;
	V_NRRELACIDIOMATRAD NUMBER;
BEGIN
    BEGIN 
		SELECT NRRELACIDIOMATRAD
		  INTO V_NRRELACIDIOMATRAD
		  FROM IDIOMA
		 WHERE CDIDIOM = P_IDIOMA;
	EXCEPTION WHEN NO_DATA_FOUND THEN
        V_NRRELACIDIOMATRAD := NULL;
    END;
    BEGIN
        SELECT DSNOMEAGRUPAMENTO
          INTO V_DESCRICAO
          FROM (
              SELECT NVL(TRAD.VALUE, DSAG.DSNOMEAGRUPAMENTO) AS DSNOMEAGRUPAMENTO
                FROM FCT_DESCAGRUPESCOPO DSAG
				LEFT JOIN ZH_WORDS_TRL TRAD
                  ON TRAD.TABLE_KEY = 'FCT_DESCAGRUPESCOPO_'||DSAG.NRDESCAGRUPESCOPO
                 AND TRAD.LANGUAGE_ID = V_NRRELACIDIOMATRAD
               WHERE DSAG.NRTIPOORCAMENTO = P_NRTIPOORCAMENTO
                 AND DSAG.IDTPFATURA = P_IDTPFATURA
                 AND (DSAG.CDFILIALFATURA = P_CDFILIALFATURA OR DSAG.CDFILIALFATURA IS NULL)
                 AND (DSAG.NRPARCFATURA = P_NRPARCFATURA OR DSAG.NRPARCFATURA IS NULL)
                 AND (DSAG.CDTIPOOPERFATURA = P_CDTIPOOPERFATURA OR DSAG.CDTIPOOPERFATURA IS NULL)
                 AND (DSAG.CDSERIENF = P_CDSERIENF OR DSAG.CDSERIENF IS NULL)
                 AND (DSAG.CDEMPRESA = P_CDEMPRESA OR DSAG.CDEMPRESA IS NULL)
                 AND (DSAG.CDINSCESTA = P_CDINSCESTA OR DSAG.CDINSCESTA IS NULL)
                 AND (DSAG.CDINSCMUNI = P_CDINSCMUNI OR DSAG.CDINSCMUNI IS NULL)
                 AND (DSAG.NRORG = P_NRORG OR DSAG.NRORG IS NULL)
                 AND (DSAG.IDPREVCOBRFUT = P_IDPREVCOBRFUT OR DSAG.IDPREVCOBRFUT IS NULL)
               ORDER BY DSAG.NRTIPOORCAMENTO,
                        DSAG.IDTPFATURA,
                        DSAG.CDFILIALFATURA,
                        DSAG.NRPARCFATURA,
                        DSAG.CDTIPOOPERFATURA,
                        DSAG.CDSERIENF,
                        DSAG.CDEMPRESA,
                        DSAG.CDINSCESTA,
                        DSAG.CDINSCMUNI
          )
         WHERE ROWNUM = 1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
        V_DESCRICAO := NULL;
    END;
    RETURN V_DESCRICAO;
END;
/