CREATE OR REPLACE  
FUNCTION FORMATA_CDCONTCTBL(
   P_CDCONTCTBL VARCHAR2) RETURN VARCHAR2
AS
   V_MASCARA VARCHAR2(50);
   V_FORMATADO VARCHAR2(50);
   V_CONTATEMP VARCHAR2(50);
   V_IDTIPOCONT VARCHAR2(01);
   V_POSICAO NUMBER;
BEGIN
   V_MASCARA := NULL;
   V_FORMATADO := P_CDCONTCTBL;
   V_CONTATEMP := P_CDCONTCTBL;
   BEGIN
      SELECT IDTIPOCONT
        INTO V_IDTIPOCONT
        FROM CONTCTBL
       WHERE CDCONTCTBL = P_CDCONTCTBL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_IDTIPOCONT := 'S';
   END;
   
   BEGIN
      SELECT CDPICTCCTB
        INTO V_MASCARA
        FROM PARAMGERAL;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         V_MASCARA := NULL;
   END;

   IF V_MASCARA IS NOT NULL THEN
      V_FORMATADO := '';
      IF V_IDTIPOCONT = 'A' THEN
         BEGIN
            SELECT REPLACE(TO_CHAR(P_CDCONTCTBL, REPLACE(V_MASCARA, '.', ',')), ',', '.')
              INTO V_FORMATADO
              FROM DUAL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_FORMATADO := NULL;
         END;
         IF V_FORMATADO IS NULL THEN
            RETURN(P_CDCONTCTBL);
         ELSE
            RETURN(V_FORMATADO);
         END IF;
      ELSE
         V_POSICAO := 0;
         BEGIN
            SELECT INSTR(V_MASCARA, '.', 1, 1)
              INTO V_POSICAO
              FROM DUAL;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_POSICAO := 0;
         END;
         WHILE (V_POSICAO > 0) LOOP
            V_FORMATADO := V_FORMATADO || SUBSTR(V_CONTATEMP, 1, V_POSICAO-1) || '.';
            V_MASCARA := SUBSTR(V_MASCARA, V_POSICAO+1, LENGTH(V_MASCARA) - V_POSICAO);
            V_CONTATEMP := SUBSTR(V_CONTATEMP, V_POSICAO, LENGTH(V_CONTATEMP) - (V_POSICAO-1));
            V_POSICAO := 0;
            IF V_CONTATEMP IS NOT NULL THEN
               BEGIN
                  SELECT INSTR(V_MASCARA, '.', 1, 1)
                    INTO V_POSICAO
                    FROM DUAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_POSICAO := 0;
               END;
            ELSE
               BEGIN
                  SELECT INSTR(V_FORMATADO, '.', -1, 1)
                    INTO V_POSICAO
                    FROM DUAL;
               EXCEPTION
                  WHEN NO_DATA_FOUND THEN
                     V_POSICAO := 0;
               END;
               V_FORMATADO := SUBSTR(V_FORMATADO, 1, V_POSICAO-1);
               V_POSICAO := 0;
            END IF;
         END LOOP;
         RETURN(V_FORMATADO);
      END IF;
   ELSE
      RETURN(P_CDCONTCTBL);
   END IF;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('FORMATA_CDCONTCTBL','5.85.xxx'); END;
/
