create or replace FUNCTION MYM_QTDE_REPOSTAS 
  (P_NRLISTA IN LISTA.NRLISTA%TYPE,
   P_NRQUESTIONARIO IN VARCHAR,
   P_IDAPLICATIVO IN VARCHAR,
   P_DTINICIO IN DATE,
   P_DTFIM IN DATE,
   P_NRORG IN NUMBER,
   P_PERIODO IN VARCHAR DEFAULT 'P',
   P_ICONES IN VARCHAR DEFAULT 'N'
)
   RETURN MYM_QTDE_REPOSTAS_TABLE PIPELINED IS

   OUT_RECORD MYM_QTDE_REPOSTAS_TYPE := MYM_QTDE_REPOSTAS_TYPE
   (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
V_COUNT NUMBER;
V_TIPO  NUMBER;
V_ORDEM NUMBER := 0;
V_NRLISTA VARCHAR2(20);
BEGIN
   V_NRLISTA := CASE WHEN P_NRLISTA = 'T' THEN P_NRLISTA ELSE '_NRLISTA='||P_NRLISTA END;
   FOR C_QUESTOES IN(
      SELECT A.CDFILIAL, A.NMFILIAL, A.NRLOCALPESQ, A.NMLOCALPESQ, A.NRQUESTIONARIO, A.NMQUESTIONARIO, A.NRTURNO, A.IDAPLICATIVO, A.DTHRFIMPESQ,  
             A.NRORDEMQUESTAO, A.NRQUESTAO, A.TXQUESTAO, A.VRPESOQUESTAO, 
             A.NRORDENARESPOST, A.NRRESPOSTA, A.DSRESPOSTA, A.VRPESORESPOSTA, A.NRPROXIMAQUESTAO, A.QTDE_EXECUCAO, 
             SUM(A.QTDE_QUESTAO) AS QTDE_QUESTAO, SUM(A.QTDE_RESPOSTA) AS QTDE_RESPOSTA, A.IDTIPORESPOSTA
      FROM(SELECT PQ.NRPROGRAMACAOPESQ, PQ.CDFILIAL, F.NMFILIAL, PQ.NRLOCALPESQ, LP.NMLOCALPESQ, QT.NRQUESTIONARIO, QT.NMQUESTIONARIO, EQ.IDAPLICATIVO,
                  TURNO_LOCAL(EQ.NRLOCALPESQ, TO_CHAR(EQ.DTHRFIMPESQ, 'HH24MI'), EQ.NRORG) AS NRTURNO, 
                  CASE WHEN P_PERIODO = 'P' THEN P_DTINICIO ELSE TRUNC(EQ.DTHRFIMPESQ) END AS DTHRFIMPESQ,
                  IQ.NRORDEMQUESTAO, IQ.NRQUESTAO, Q.TXQUESTAO, IQ.VRPESOQUESTAO, R.NRORDENARESPOST, R.NRRESPOSTA, R.DSRESPOSTA, R.VRPESORESPOSTA, R.NRPROXIMAQUESTAO,
                  COUNT(DISTINCT EQ.NREXECUCAOPESQ) OVER (PARTITION BY PQ.CDFILIAL, F.NMFILIAL, PQ.NRLOCALPESQ, LP.NMLOCALPESQ, QT.NRQUESTIONARIO, 
                                                                       TURNO_LOCAL(EQ.NRLOCALPESQ, TO_CHAR(EQ.DTHRFIMPESQ, 'HH24MI'), EQ.NRORG), EQ.IDAPLICATIVO) AS QTDE_EXECUCAO,
                  COUNT(DISTINCT RP.NRQUESTAO) OVER (PARTITION BY EQ.NRPROGRAMACAOPESQ, RP.NRQUESTAO,
                                                                  TURNO_LOCAL(EQ.NRLOCALPESQ, TO_CHAR(EQ.DTHRFIMPESQ, 'HH24MI'), EQ.NRORG)) AS QTDE_QUESTAO,
                  COUNT(DISTINCT NVL(TO_CHAR(RP.NRRESPOSTA),RP.TXRESPOSTAPESQ)) OVER (PARTITION BY EQ.NRPROGRAMACAOPESQ, RP.NRQUESTAO, NVL(TO_CHAR(RP.NRRESPOSTA),RP.TXRESPOSTAPESQ),
                                                                   TURNO_LOCAL(EQ.NRLOCALPESQ, TO_CHAR(EQ.DTHRFIMPESQ, 'HH24MI'), EQ.NRORG)) AS QTDE_RESPOSTA,
                  R.IDTIPORESPOSTA                                                
             FROM PROGRAMACAOPESQ PQ
             INNER JOIN EXECUCAOPESQ EQ ON EQ.NRORG = PQ.NRORG AND EQ.NRPROGRAMACAOPESQ = PQ.NRPROGRAMACAOPESQ AND (TRUNC(EQ.DTHRFIMPESQ) BETWEEN P_DTINICIO AND P_DTFIM)
             INNER JOIN QUESTIONARIO QT ON PQ.NRORG = QT.NRORG AND PQ.NRQUESTIONARIO = QT.NRQUESTIONARIO
             INNER JOIN ITEMQUESTIONARIO IQ ON IQ.NRORG = QT.NRORG AND IQ.NRQUESTIONARIO = QT.NRQUESTIONARIO
             INNER JOIN FILIAL F ON F.NRORG = PQ.NRORG AND F.CDFILIAL = PQ.CDFILIAL
             LEFT  JOIN LOCALPESQ LP ON LP.NRORG = PQ.NRORG AND LP.CDFILIAL = PQ.CDFILIAL AND LP.NRLOCALPESQ = PQ.NRLOCALPESQ
             INNER JOIN QUESTAO Q ON Q.NRORG = IQ.NRORG AND IQ.NRQUESTAO = Q.NRQUESTAO AND (NOT(IDTIPOQUESTAO = 'ABERTA_TEXTO' AND IDLAYOUTRESP = 'TEXT'))
             INNER JOIN (SELECT NRQUESTAO, NRRESPOSTA, DSRESPOSTA, NRORDENARESPOST, CASE WHEN NRRESPOSTA > 3  THEN 'PROMOTOR' WHEN NRRESPOSTA > 2  THEN 'NEUTRO' ELSE 'DETRATOR' END AS IDTIPORESPOSTA, NRPROXIMAQUESTAO, VRPESORESPOSTA 
                           FROM (SELECT Q.NRQUESTAO, ROW_NUMBER() OVER (PARTITION BY Q.NRQUESTAO ORDER BY DT.DTDATA) AS NRRESPOSTA, TO_CHAR(ROW_NUMBER() OVER (PARTITION BY Q.NRQUESTAO ORDER BY DT.DTDATA)) AS DSRESPOSTA, 
                                        ROW_NUMBER() OVER (PARTITION BY Q.NRQUESTAO ORDER BY DT.DTDATA) AS NRORDENARESPOST, NULL AS IDTIPORESPOSTA, NULL AS NRPROXIMAQUESTAO, 1 AS VRPESORESPOSTA 
                                   FROM QUESTAO Q CROSS JOIN DATA DT 
                                  WHERE Q.IDTIPOQUESTAO = 'ABERTA_TEXTO' AND Q.IDLAYOUTRESP = 'EMOJI' AND DT.DTDATA BETWEEN SYSDATE AND SYSDATE + 5 AND P_ICONES = 'S')
                         UNION ALL
                         SELECT NRQUESTAO, NRRESPOSTA, DSRESPOSTA, NRORDENARESPOST, CASE WHEN NRRESPOSTA > 3  THEN 'PROMOTOR' WHEN NRRESPOSTA > 2  THEN 'NEUTRO' ELSE 'DETRATOR' END AS IDTIPORESPOSTA, NRPROXIMAQUESTAO, VRPESORESPOSTA 
                           FROM (SELECT Q.NRQUESTAO, ROW_NUMBER() OVER (PARTITION BY Q.NRQUESTAO ORDER BY DT.DTDATA) AS NRRESPOSTA, TO_CHAR(ROW_NUMBER() OVER (PARTITION BY Q.NRQUESTAO ORDER BY DT.DTDATA)) AS DSRESPOSTA, 
                                        ROW_NUMBER() OVER (PARTITION BY Q.NRQUESTAO ORDER BY DT.DTDATA) AS NRORDENARESPOST, NULL AS IDTIPORESPOSTA, NULL AS NRPROXIMAQUESTAO, 1 AS VRPESORESPOSTA 
                                   FROM QUESTAO Q CROSS JOIN DATA DT
                                  WHERE IDTIPOQUESTAO = 'ABERTA_TEXTO' AND IDLAYOUTRESP = 'STAR' AND DTDATA BETWEEN SYSDATE AND SYSDATE + 5 AND P_ICONES = 'S')
                         UNION ALL
                         SELECT NRQUESTAO, NRRESPOSTA, DSRESPOSTA, NRORDENARESPOST, CASE WHEN NRRESPOSTA > 8  THEN 'PROMOTOR' WHEN NRRESPOSTA > 6  THEN 'NEUTRO' ELSE 'DETRATOR' END AS IDTIPORESPOSTA, NRPROXIMAQUESTAO, VRPESORESPOSTA 
                           FROM (SELECT Q.NRQUESTAO, ROW_NUMBER() OVER (PARTITION BY Q.NRQUESTAO ORDER BY DT.DTDATA) AS NRRESPOSTA, TO_CHAR(ROW_NUMBER() OVER (PARTITION BY Q.NRQUESTAO ORDER BY DT.DTDATA)) AS DSRESPOSTA, 
                                        ROW_NUMBER() OVER (PARTITION BY Q.NRQUESTAO ORDER BY DT.DTDATA) AS NRORDENARESPOST, NULL AS IDTIPORESPOSTA, NULL AS NRPROXIMAQUESTAO, 1 AS VRPESORESPOSTA
                                   FROM QUESTAO Q CROSS JOIN DATA DT
                                  WHERE IDTIPOQUESTAO = 'ABERTA_TEXTO' AND IDLAYOUTRESP = 'RANGE' AND DTDATA BETWEEN SYSDATE AND SYSDATE + 10 AND P_ICONES = 'S') 
                         UNION ALL
                         SELECT NRQUESTAO, NRRESPOSTA, DSRESPOSTA, NRORDENARESPOST, IDTIPORESPOSTA, NRPROXIMAQUESTAO, VRPESORESPOSTA 
                           FROM RESPOSTA) R ON R.NRQUESTAO = Q.NRQUESTAO
             LEFT JOIN RESPOSTAPESQ RP ON RP.NREXECUCAOPESQ = EQ.NREXECUCAOPESQ AND RP.NRQUESTAO = R.NRQUESTAO AND NVL(TO_CHAR(RP.NRRESPOSTA), RP.TXRESPOSTAPESQ) = TO_CHAR(R.NRRESPOSTA) AND RP.NRRESPOSTAANTERIOR IS NULL
            WHERE (PQ.NRORG = P_NRORG) AND
                  (INSTR(P_NRQUESTIONARIO, QT.NRQUESTIONARIO) <> 0 OR P_NRQUESTIONARIO = 'T') AND
                  (INSTR(P_IDAPLICATIVO, PQ.IDAPLICATIVO) <> 0 OR P_IDAPLICATIVO = 'T') AND
                  VALOR_NA_LISTA(PQ.CDFILIAL, V_NRLISTA) > 0) A
            GROUP BY A.CDFILIAL, A.NMFILIAL, A.NRLOCALPESQ, A.NMLOCALPESQ, A.NRQUESTIONARIO, A.NMQUESTIONARIO, A.NRTURNO, A.IDAPLICATIVO, A.DTHRFIMPESQ, A.NRQUESTAO, A.TXQUESTAO, A.VRPESOQUESTAO, 
                   A.NRRESPOSTA, A.DSRESPOSTA, A.VRPESORESPOSTA, A.NRPROXIMAQUESTAO, A.QTDE_EXECUCAO, A.NRORDEMQUESTAO, A.NRORDENARESPOST, A.IDTIPORESPOSTA 
            ORDER BY A.CDFILIAL, A.NRLOCALPESQ, A.NRQUESTIONARIO, A.NRTURNO, A.IDAPLICATIVO, A.NRORDEMQUESTAO, A.NRORDENARESPOST, A.DTHRFIMPESQ
   ) LOOP
      IF V_ORDEM <> C_QUESTOES.NRORDEMQUESTAO THEN
         V_ORDEM := C_QUESTOES.NRORDEMQUESTAO;
         V_COUNT := 0;
      END IF;
      OUT_RECORD.CDFILIAL           := C_QUESTOES.CDFILIAL;
      OUT_RECORD.NMFILIAL           := C_QUESTOES.NMFILIAL;
      OUT_RECORD.NRLOCALPESQ        := C_QUESTOES.NRLOCALPESQ;
      OUT_RECORD.NMLOCALPESQ        := C_QUESTOES.NMLOCALPESQ;
      OUT_RECORD.NRTURNOLOCALPESQ   := C_QUESTOES.NRTURNO;
      OUT_RECORD.IDAPLICATIVO       := C_QUESTOES.IDAPLICATIVO;
      OUT_RECORD.NRQUESTIONARIO     := C_QUESTOES.NRQUESTIONARIO;
      OUT_RECORD.NMQUESTIONARIO     := C_QUESTOES.NMQUESTIONARIO;
      OUT_RECORD.DATARESPOSTA       := C_QUESTOES.DTHRFIMPESQ;
      OUT_RECORD.NRQUESTAOANTERIOR  := NULL; 
      OUT_RECORD.TXQUESTAOANTERIOR  := NULL; 
      OUT_RECORD.NRRESPOSTAANTERIOR := NULL; 
      OUT_RECORD.DSRESPOSTAANTERIOR := NULL;
      OUT_RECORD.NRORDEM            := C_QUESTOES.NRORDEMQUESTAO;
      OUT_RECORD.NRORDEMQUESTAO     := C_QUESTOES.NRORDEMQUESTAO;
      OUT_RECORD.NRQUESTAO          := C_QUESTOES.NRQUESTAO;
      OUT_RECORD.TXQUESTAO          := C_QUESTOES.TXQUESTAO;
      OUT_RECORD.VRPESOQUESTAO      := C_QUESTOES.VRPESOQUESTAO;
      OUT_RECORD.NRORDENARESPOST    := C_QUESTOES.NRORDENARESPOST;
      OUT_RECORD.NRRESPOSTA         := C_QUESTOES.NRRESPOSTA;
      OUT_RECORD.DSRESPOSTA         := C_QUESTOES.DSRESPOSTA;
      OUT_RECORD.VRPESORESPOSTA     := C_QUESTOES.VRPESORESPOSTA;
      OUT_RECORD.NRPROXIMAQUESTAO   := C_QUESTOES.NRPROXIMAQUESTAO;
      OUT_RECORD.QTDE_EXECUCAO      := C_QUESTOES.QTDE_EXECUCAO;
      OUT_RECORD.QTDE_QUESTAO       := C_QUESTOES.QTDE_QUESTAO;
      OUT_RECORD.QTDE_RESPOSTA      := C_QUESTOES.QTDE_RESPOSTA;
      OUT_RECORD.IDTIPORESPOSTA     := C_QUESTOES.IDTIPORESPOSTA;

      PIPE ROW (OUT_RECORD);

      IF C_QUESTOES.NRPROXIMAQUESTAO IS NOT NULL THEN
         SELECT COUNT(*) 
           INTO V_TIPO
           FROM QUESTAO Q
          WHERE IDTIPOQUESTAO = 'ABERTA_TEXTO' AND IDLAYOUTRESP = 'TEXT' AND
                NRQUESTAO = C_QUESTOES.NRPROXIMAQUESTAO;
         IF V_TIPO = 0 THEN          
            V_COUNT := V_COUNT + 1;
            FOR C_QUESTOES_REC IN (
               SELECT NRQUESTAOANTERIOR, 
                      TXQUESTAOANTERIOR, 
                      NRRESPOSTAANTERIOR, 
                      DSRESPOSTAANTERIOR,
                      NRORDEMQUESTAO,
                      NRQUESTAO, 
                      TXQUESTAO, 
                      NRRESPOSTA,
                      VRPESORESPOSTA, 
                      NRORDENARESPOST, 
                      DSRESPOSTA, 
                      QTDE_QUESTAO, 
                      NRPROXIMAQUESTAO,
                      IDTIPORESPOSTA,
                      QTDE_RESPOSTA
                 FROM TABLE (MYM_QTDE_REPOSTAS_REC(C_QUESTOES.NRQUESTAO, C_QUESTOES.TXQUESTAO, C_QUESTOES.NRRESPOSTA, C_QUESTOES.DSRESPOSTA, C_QUESTOES.QTDE_RESPOSTA, C_QUESTOES.CDFILIAL,
                                                   C_QUESTOES.NRLOCALPESQ, C_QUESTOES.NRQUESTIONARIO, C_QUESTOES.IDAPLICATIVO, C_QUESTOES.NRTURNO, P_PERIODO, C_QUESTOES.DTHRFIMPESQ,
                                                   P_DTINICIO, P_DTFIM, C_QUESTOES.NRPROXIMAQUESTAO, V_COUNT, P_NRORG, P_ICONES)) 
               ORDER BY  NRQUESTAOANTERIOR, TO_CHAR(NRORDEMQUESTAO) ASC, NRORDENARESPOST
            )LOOP        
               OUT_RECORD.CDFILIAL           := C_QUESTOES.CDFILIAL;
               OUT_RECORD.NMFILIAL           := C_QUESTOES.NMFILIAL;
               OUT_RECORD.NRLOCALPESQ        := C_QUESTOES.NRLOCALPESQ;
               OUT_RECORD.NMLOCALPESQ        := C_QUESTOES.NMLOCALPESQ;
               OUT_RECORD.NRTURNOLOCALPESQ   := C_QUESTOES.NRTURNO;
               OUT_RECORD.IDAPLICATIVO       := C_QUESTOES.IDAPLICATIVO;               
               OUT_RECORD.NRQUESTIONARIO     := C_QUESTOES.NRQUESTIONARIO;
               OUT_RECORD.NMQUESTIONARIO     := C_QUESTOES.NMQUESTIONARIO;
               OUT_RECORD.DATARESPOSTA       := C_QUESTOES.DTHRFIMPESQ;
               OUT_RECORD.NRQUESTAOANTERIOR  := C_QUESTOES_REC.NRQUESTAOANTERIOR; 
               OUT_RECORD.TXQUESTAOANTERIOR  := C_QUESTOES_REC.TXQUESTAOANTERIOR; 
               OUT_RECORD.NRRESPOSTAANTERIOR := C_QUESTOES_REC.NRRESPOSTAANTERIOR; 
               OUT_RECORD.DSRESPOSTAANTERIOR := C_QUESTOES_REC.DSRESPOSTAANTERIOR; 
               OUT_RECORD.NRORDEM            := C_QUESTOES.NRORDEMQUESTAO;
               OUT_RECORD.NRORDEMQUESTAO     := C_QUESTOES.NRORDEMQUESTAO||'.'||C_QUESTOES_REC.NRORDEMQUESTAO;
               OUT_RECORD.NRQUESTAO          := C_QUESTOES_REC.NRQUESTAO;
               OUT_RECORD.TXQUESTAO          := C_QUESTOES_REC.TXQUESTAO;
               OUT_RECORD.VRPESOQUESTAO      := C_QUESTOES.VRPESOQUESTAO;
               OUT_RECORD.NRORDENARESPOST    := C_QUESTOES_REC.NRORDENARESPOST;
               OUT_RECORD.NRRESPOSTA         := C_QUESTOES_REC.NRRESPOSTA;
               OUT_RECORD.DSRESPOSTA         := C_QUESTOES_REC.DSRESPOSTA;
               OUT_RECORD.VRPESORESPOSTA     := C_QUESTOES_REC.VRPESORESPOSTA;
               OUT_RECORD.NRPROXIMAQUESTAO   := C_QUESTOES_REC.NRPROXIMAQUESTAO;
               OUT_RECORD.QTDE_EXECUCAO      := C_QUESTOES.QTDE_EXECUCAO;
               OUT_RECORD.QTDE_QUESTAO       := C_QUESTOES_REC.QTDE_QUESTAO;
               OUT_RECORD.QTDE_RESPOSTA      := C_QUESTOES_REC.QTDE_RESPOSTA;
               OUT_RECORD.IDTIPORESPOSTA     := C_QUESTOES_REC.IDTIPORESPOSTA;

               PIPE ROW (OUT_RECORD);
            END LOOP;
         END IF;
      END IF;      
   END LOOP;
   RETURN;
END;
/