CREATE TYPE MSGREGRAFISC_TYPE AS
OBJECT (CDREGRAFISC      VARCHAR2(10),
        NRMENSREGRAFISC  VARCHAR2(10),
        DSMENSREGRAFISC  VARCHAR2(150),
        NRVINCFISCREGRA  VARCHAR2(10))
/

CREATE TYPE MSGREGRAFISC_TABLE AS
TABLE OF MSGREGRAFISC_TYPE
/

CREATE OR REPLACE FUNCTION MSG_REGRAFISCAL
(P_CDREGRAFISC   IN REGRAFISCAL.CDREGRAFISC%TYPE,
 P_CDEMPRESA     IN NOTAFISCAL.CDEMPRESA%TYPE DEFAULT NULL,
 P_IDENTRSAIDOP  IN NOTAFISCAL.IDENTRSAIDOP%TYPE DEFAULT NULL,
 P_CDTIPOOPER    IN NOTAFISCAL.CDTIPOOPER%TYPE DEFAULT NULL,
 P_CDINSCESTA    IN NOTAFISCAL.CDINSCESTA%TYPE DEFAULT NULL) RETURN MSGREGRAFISC_TABLE PIPELINED
IS
V_CDREGRAFISC      REGRAFISCAL.CDREGRAFISC%TYPE;
V_NRVINCFISCREGRA  VINCULOFISREGRA.NRVINCFISCREGRA%TYPE;
V_NRMENSREGRAFISC  MENSAGEMREGFISC.NRMENSREGRAFISC%TYPE;
V_DSMENSREGRAFISC  MENSAGEMREGFISC.DSMENSREGRAFISC%TYPE;
OUT_RECORD MSGREGRAFISC_TYPE := MSGREGRAFISC_TYPE (NULL, NULL, NULL, NULL);
BEGIN
   FOR C_MENSAGEM IN
      (SELECT NRMENSREGRAFISC, DSMENSREGRAFISC, '' NRVINCFISCREGRA
         FROM MENSAGEMREGFISC
        WHERE CDREGRAFISC = P_CDREGRAFISC
        UNION ALL
       SELECT NRMENSREGRAFISC, DSMENSREGRAFISC, NRVINCFISCREGRA
         FROM MSGVINCULOREGRA
        WHERE CDREGRAFISC = P_CDREGRAFISC
          AND NRVINCFISCREGRA IN (SELECT NRVINCFISCREGRA
                                    FROM VINCULOFISREGRA
                                   WHERE (CDREGRAFISC                  = P_CDREGRAFISC)
                                     AND (VINCULOFISREGRA.CDEMPRESA    = P_CDEMPRESA)
                                     AND (VINCULOFISREGRA.IDENTRSAIDOP = P_IDENTRSAIDOP OR IDENTRSAIDOP IS NULL)
                                     AND (VINCULOFISREGRA.CDTIPOOPER   = P_CDTIPOOPER   OR CDTIPOOPER   IS NULL)
                                     AND (VINCULOFISREGRA.CDINSCESTA   = P_CDINSCESTA   OR CDINSCESTA   IS NULL))
        ORDER BY NRMENSREGRAFISC)
   LOOP
      V_CDREGRAFISC     := P_CDREGRAFISC;
      V_NRVINCFISCREGRA := C_MENSAGEM.NRVINCFISCREGRA;
      V_NRMENSREGRAFISC := C_MENSAGEM.NRMENSREGRAFISC;
      V_DSMENSREGRAFISC := C_MENSAGEM.DSMENSREGRAFISC;
      IF V_CDREGRAFISC IS NOT NULL THEN
         IF V_NRMENSREGRAFISC IS NOT NULL OR V_DSMENSREGRAFISC IS NOT NULL OR V_NRVINCFISCREGRA IS NOT NULL THEN
            OUT_RECORD.CDREGRAFISC        := V_CDREGRAFISC;
            OUT_RECORD.NRMENSREGRAFISC    := V_NRMENSREGRAFISC;
            OUT_RECORD.DSMENSREGRAFISC    := SUBSTR(V_DSMENSREGRAFISC, 1, 120);
            OUT_RECORD.NRVINCFISCREGRA    := V_NRVINCFISCREGRA;
            PIPE ROW (OUT_RECORD);
         END IF;
      END IF;
   END LOOP;
END;
/