-------------------------------------------------------------------------------------------
----------------------GENERAR CONSECUTIVOS AL SEr DESAGREGADOS VARIAS VECES----------------
-------------------------------------------------------------------------------------------
PROCEDURE PR_TABLE_TEMP_CONSE_HIST(
                                    P_tipoTransfer        VARCHAR2,
                                    ErrorCode             OUT NUMBER,
                                    ErrorDescription      OUT VARCHAR2)
IS
    v_rows              NUMBER;
    v_id_Padre          NUMBER;
    v_RowIdPadre        NUMBER := 0;
    v_RowNumber         NUMBER := 1;
    v_countRepeat       NUMBER := 0;
    v_validarConse      NUMBER := 0;
    v_conse_1           VARCHAR(30);
    v_conse_valida      VARCHAR(30) := NULL;
BEGIN
      EXECUTE IMMEDIATE 'TRUNCATE TABLE temp_Consecutivo';

      SELECT COUNT(IDPADRE)
        INTO v_RowIdPadre
        FROM (
                  SELECT TRF.TRF_IDPADRE AS IDPADRE
                    FROM SIMIT.TRF_TRANSFERENCIAS TRF
                   WHERE TRF.TRF_IDPADRE IS NOT NULL
                     AND TRF.TRF_IDTIPOTRANSFER  = P_tipoTransfer
                     AND TRF.TRF_ESTADOTRANSFERH = 'A'
                GROUP BY TRF.TRF_IDPADRE
             );

      WHILE v_RowIdPadre > 0
      LOOP
             INSERT INTO temp_Consecutivo ( temp_consecutivo,
                                            temp_id_transferencia,
                                            Temp_Id_Padre)
                  SELECT TRFC.TRF_CONSECUTIVO || '_' || ROW_NUMBER() OVER (ORDER BY TRFC.TRF_ID_TRANSFERENCIA ASC),
                         TRFC.TRF_ID_TRANSFERENCIA,
                         TRFC.TRF_IDPADRE
                    FROM TRF_TRANSFERENCIAS TRFC
                   WHERE TRFC.TRF_IDPADRE         = ( SELECT IDPADRE
                                                        FROM (
                                                                SELECT TRF.TRF_IDPADRE                                   AS IDPADRE,
                                                                       ROW_NUMBER() OVER (ORDER BY TRF.TRF_IDPADRE ASC)  AS RANGO
                                                                  FROM TRF_TRANSFERENCIAS TRF
                                                                 WHERE TRF.TRF_IDPADRE IS NOT NULL
                                                                   AND TRF.TRF_IDTIPOTRANSFER   = P_tipoTransfer
                                                                   AND TRF.TRF_ESTADOTRANSFERH  = 'A'
                                                              GROUP BY TRF.TRF_IDPADRE
                                                             )
                                                       WHERE RANGO = v_RowNumber
                                                    )
                     AND TRFC.TRF_IDTIPOTRANSFER  = P_tipoTransfer
                     AND TRFC.TRF_ESTADOTRANSFERH = 'A';
           v_rows := SQL%ROWCOUNT;

           v_RowIdPadre := v_RowIdPadre - 1;
           v_RowNumber := v_RowNumber + 1;
           IF (v_rows > 0) THEN
             COMMIT;
             ErrorCode       := 0;
             ErrorDescription:= 'SIN ERRORES';
           ELSE
             ROLLBACK;
             ErrorCode       := -1;
             ErrorDescription:= 'ERROR AL INSERTAR REGISTRO TEMPORAL CONSECUTIVO';
           END IF;
      END LOOP;

      SELECT COUNT(REPETIDO)
        INTO v_countRepeat
        FROM (  SELECT TEMP.temp_Consecutivo AS REPETIDO
                  FROM temp_Consecutivo TEMP
                 WHERE CASE WHEN SUBSTR(TEMP.TEMP_CONSECUTIVO,LENGTH(TEMP.TEMP_CONSECUTIVO)-1) != '_1' THEN SUBSTR(TEMP.TEMP_CONSECUTIVO,LENGTH(TEMP.TEMP_CONSECUTIVO)-2)
                       ELSE SUBSTR(TEMP.TEMP_CONSECUTIVO,LENGTH(TEMP.TEMP_CONSECUTIVO)-1) END = '_1'
              GROUP BY TEMP.temp_Consecutivo
                HAVING COUNT(TEMP.temp_Consecutivo) > 1
             );

      WHILE (v_countRepeat > 0)
        LOOP

                SELECT CONSECUTIVO INTO v_conse_valida
                FROM (  SELECT TEMP.temp_Consecutivo AS CONSECUTIVO,ROW_NUMBER() OVER (ORDER BY TEMP.temp_Consecutivo ASC) AS RANGO
                        FROM temp_Consecutivo TEMP
                        GROUP BY TEMP.temp_Consecutivo
                        HAVING COUNT(TEMP.temp_Consecutivo) > 1
                        ORDER BY TEMP.temp_Consecutivo ASC
                      )
                WHERE ROWNUM = 1;

                SELECT temp_Consecutivo,
                       TEMP_ID_PADRE
                  INTO v_conse_1,v_id_Padre
                  FROM ( SELECT TP.temp_Consecutivo,
                                TP.TEMP_ID_PADRE,ROW_NUMBER() OVER (ORDER BY TP.TEMP_ID_PADRE ASC) AS RANGO
                           FROM temp_Consecutivo TP
                          WHERE TP.temp_Consecutivo = (  SELECT CONSECUTIVO
                                                           FROM (  SELECT TEMP.temp_Consecutivo                                  AS CONSECUTIVO,
                                                                          ROW_NUMBER() OVER (ORDER BY TEMP.temp_Consecutivo ASC) AS RANGO
                                                                     FROM temp_Consecutivo TEMP
                                                                 GROUP BY TEMP.temp_Consecutivo
                                                                   HAVING COUNT(TEMP.temp_Consecutivo) > 1
                                                                 ORDER BY TEMP.temp_Consecutivo ASC
                                                                 )
                                                           WHERE ROWNUM = 1
                                                       )
                        )
                 WHERE RANGO = 2
              ORDER BY TEMP_ID_PADRE ASC;

                SELECT T.temp_Consecutivo
                  INTO v_conse_1
                  FROM temp_Consecutivo T
                 WHERE T.TEMP_ID_TRANSFERENCIA = v_id_Padre;

                MERGE INTO SIMIT.temp_Consecutivo UTP1
                USING (
                         SELECT v_conse_1 || '_' || ROW_NUMBER() OVER (ORDER BY TRFC.TRF_ID_TRANSFERENCIA ASC) AS ConsecutivoFinal,
                                TRFC.TRF_ID_TRANSFERENCIA
                           FROM SIMIT.TRF_TRANSFERENCIAS TRFC
                          WHERE TRFC.TRF_IDPADRE            = (  SELECT IDPADRE
                                                                   FROM (
                                                                            SELECT TRF.TRF_IDPADRE                                  AS IDPADRE,
                                                                                   ROW_NUMBER() OVER (ORDER BY TRF.TRF_IDPADRE ASC) AS RANGO
                                                                             FROM SIMIT.TRF_TRANSFERENCIAS TRF
                                                                            WHERE TRF.TRF_IDPADRE          = v_id_Padre
                                                                              AND TRF.TRF_IDTIPOTRANSFER   = P_tipoTransfer
                                                                              AND TRF.TRF_ESTADOTRANSFERH  = 'A'
                                                                         )
                                                                   WHERE RANGO = 1
                                                               )
                            AND TRFC.TRF_IDTIPOTRANSFER     = P_tipoTransfer
                            AND TRFC.TRF_ESTADOTRANSFERH    = 'A'
                       )UTP2
                ON(UTP1.TEMP_ID_TRANSFERENCIA = UTP2.TRF_ID_TRANSFERENCIA)
                WHEN MATCHED THEN UPDATE SET
                  UTP1.temp_Consecutivo = UTP2.ConsecutivoFinal;

                v_rows := SQL%ROWCOUNT;

                IF (v_rows > 0) THEN
                   COMMIT;
                   ErrorCode        := 0;
                   ErrorDescription := 'SIN ERRORES';
                ELSE
                   ROLLBACK;
                   ErrorCode        := -1;
                   ErrorDescription := 'ERROR AL ACTUALIZAR CONSECUTIVO';
                END IF;

                SELECT COUNT(REPETIDO)
                  INTO v_validarConse
                  FROM ( SELECT TEMP.temp_Consecutivo AS REPETIDO
                           FROM temp_Consecutivo TEMP
                          WHERE TEMP.temp_Consecutivo = v_conse_valida
                       GROUP BY TEMP.temp_Consecutivo
                         HAVING COUNT(TEMP.temp_Consecutivo) > 1
                       );

                IF (v_validarConse = 0)THEN
                   v_countRepeat := v_countRepeat - 1;
                END IF;

        END LOOP;

        IF(v_RowIdPadre = 0) THEN
            ErrorCode        := 0;
            ErrorDescription := 'SIN ERRORES';
        END IF;

        COMMIT;
END PR_TABLE_TEMP_CONSE_HIST;