DROP TYPE DADOS_FORN_EMAIL_TABLE
/

DROP TYPE DADOS_FORN_EMAIL_TYPE
/

CREATE TYPE DADOS_FORN_EMAIL_TYPE AS
OBJECT( CDFORNECED VARCHAR2(21),
        DSEMAILFORN VARCHAR2(400))
/

CREATE TYPE DADOS_FORN_EMAIL_TABLE AS TABLE OF DADOS_FORN_EMAIL_TYPE
/

CREATE OR REPLACE FUNCTION DADOS_FORN_EMAIL (P_CDFORNECED  IN VARCHAR2,
                                             P_IDACESSOAF  IN VARCHAR2 DEFAULT NULL,
                                             P_IDACESSOCOT IN VARCHAR2 DEFAULT NULL,
                                             P_NRORG IN INTEGER DEFAULT 1) RETURN DADOS_FORN_EMAIL_TABLE PIPELINED
IS
   V_DSEMAILFORN VARCHAR2(400);
   OUT_RECORD DADOS_FORN_EMAIL_TYPE := DADOS_FORN_EMAIL_TYPE (NULL, NULL);
BEGIN
   FOR C_DADOSFORNECOMMERCE IN (SELECT CDFORNECED, DSEMAILECO, DSLOGINECO
                                  FROM DADOSFORNECOMMERCE
                                 WHERE CDFORNECED = P_CDFORNECED
                                   AND NRORG = P_NRORG
                                   AND IDACESSOAF = NVL(P_IDACESSOAF, IDACESSOAF)
                                   AND IDACESSOCOT = NVL(P_IDACESSOCOT, IDACESSOCOT)) LOOP
      IF C_DADOSFORNECOMMERCE.DSEMAILECO IS NOT NULL THEN
         V_DSEMAILFORN := V_DSEMAILFORN || C_DADOSFORNECOMMERCE.DSEMAILECO;
         V_DSEMAILFORN := REPLACE(V_DSEMAILFORN, ';', ',');
         IF (SUBSTR(V_DSEMAILFORN, -1) <> ',') THEN
            V_DSEMAILFORN := V_DSEMAILFORN || ',';
         END IF;

         V_DSEMAILFORN := V_DSEMAILFORN || C_DADOSFORNECOMMERCE.DSLOGINECO;
         V_DSEMAILFORN := REPLACE(V_DSEMAILFORN, ';', ',');
         IF (SUBSTR(V_DSEMAILFORN, -1) <> ',') THEN
            V_DSEMAILFORN := V_DSEMAILFORN || ',';
         END IF;
      ELSE
         V_DSEMAILFORN := V_DSEMAILFORN || C_DADOSFORNECOMMERCE.DSLOGINECO;
         V_DSEMAILFORN := REPLACE(V_DSEMAILFORN, ';', ',');
         IF (SUBSTR(V_DSEMAILFORN, -1) <> ',') THEN
            V_DSEMAILFORN := V_DSEMAILFORN || ',';
         END IF;
      END IF;
      OUT_RECORD.CDFORNECED  := C_DADOSFORNECOMMERCE.CDFORNECED;
   END LOOP;
   OUT_RECORD.DSEMAILFORN := V_DSEMAILFORN;
   PIPE ROW (OUT_RECORD);
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('DADOS_FORN_EMAIL','6.25.XXX'); END;
/
