create or replace FUNCTION MYM_QTDE_REPOSTAS_REC 
  (P_NRQUESTAOANTERIOR IN NUMBER,
   P_TXQUESTAOANTERIOR IN VARCHAR,
   P_NRRESPOSTAANTERIOR IN NUMBER,
   P_DSRESPOSTAANTERIOR IN VARCHAR,
   P_QTDE_QUESTAO IN NUMBER,
   P_CDFILIAL IN FILIAL.CDFILIAL%TYPE,
   P_NRLOCALPESQ IN LOCALPESQ.NRLOCALPESQ%TYPE,
   P_NRQUESTIONARIO IN NUMBER,
   P_IDAPLICATIVO IN VARCHAR,
   P_NRTURNO IN NUMBER,
   P_PERIODO IN VARCHAR,
   P_DTRESPOSTA IN DATE,
   P_DTINICIO IN DATE,
   P_DTFIM IN DATE,
   P_NRPROXIMAQUESTAO IN NUMBER,
   P_NRORDEMQUESTAO IN VARCHAR,
   P_NRORG IN NUMBER,
   P_ICONES IN VARCHAR
)  
   RETURN MYM_QTDE_REPOSTAS_REC_TABLE PIPELINED IS

   OUT_RECORD MYM_QTDE_REPOSTAS_REC_TYPE := MYM_QTDE_REPOSTAS_REC_TYPE
   (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

V_ORDEMQUESTAO VARCHAR2(2000);
V_COUNT NUMBER;
V_TIPO NUMBER;
BEGIN
V_TIPO := 1;
V_COUNT := 1;
V_ORDEMQUESTAO := '';
   FOR C_QUESTOES IN(
      SELECT P_NRQUESTAOANTERIOR AS NRQUESTAOANTERIOR, P_TXQUESTAOANTERIOR AS TXQUESTAOANTERIOR, P_NRRESPOSTAANTERIOR AS NRRESPOSTAANTERIOR, P_DSRESPOSTAANTERIOR AS DSRESPOSTAANTERIOR, 
             Q.NRQUESTAO, Q.TXQUESTAO, R.NRRESPOSTA, R.NRORDENARESPOST, R.DSRESPOSTA, P_QTDE_QUESTAO AS QTDE_QUESTAO, R.NRPROXIMAQUESTAO, R.VRPESORESPOSTA, R.IDTIPORESPOSTA,
             (SELECT COUNT(NVL(TO_CHAR(RP.NRRESPOSTA), RP.TXRESPOSTAPESQ))
                FROM RESPOSTAPESQ RP INNER JOIN EXECUCAOPESQ EX ON RP.NRORG = EX.NRORG AND RP.NREXECUCAOPESQ = EX.NREXECUCAOPESQ 
               WHERE (EX.NRORG = P_NRORG) AND
                     (EX.CDFILIAL = P_CDFILIAL) AND NVL(EX.NRLOCALPESQ,0) = NVL(P_NRLOCALPESQ,0) AND
                     (EX.NRQUESTIONARIO = P_NRQUESTIONARIO) AND (EX.IDAPLICATIVO = P_IDAPLICATIVO) AND
                     (((TRUNC(EX.DTHRFIMPESQ) BETWEEN P_DTINICIO AND P_DTFIM) AND P_PERIODO = 'P') OR (P_PERIODO = 'D' AND TRUNC(EX.DTHRFIMPESQ) = P_DTRESPOSTA)) AND
                     (TURNO_LOCAL(EX.NRLOCALPESQ, TO_CHAR(EX.DTHRFIMPESQ, 'HH24MI'), EX.NRORG) = P_NRTURNO) AND
                     (RP.NRQUESTAO = R.NRQUESTAO) AND NVL(TO_CHAR(RP.NRRESPOSTA), RP.TXRESPOSTAPESQ) = TO_CHAR(R.NRRESPOSTA) AND
                     (NVL(RP.NRRESPOSTAANTERIOR,0) = P_NRRESPOSTAANTERIOR)) AS QTDE_RESPOSTA 
        FROM QUESTAO Q 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 AND Q.NRQUESTAO = P_NRPROXIMAQUESTAO
        ORDER BY R.NRORDENARESPOST    
   )LOOP
      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;
      END IF;
      V_ORDEMQUESTAO :=  P_NRORDEMQUESTAO||'.'||V_COUNT;
      V_COUNT := V_COUNT + 1;
         
      IF C_QUESTOES.NRPROXIMAQUESTAO IS NOT NULL AND V_TIPO = 0 THEN
      
         OUT_RECORD.NRQUESTAOANTERIOR  := C_QUESTOES.NRQUESTAOANTERIOR;
         OUT_RECORD.TXQUESTAOANTERIOR  := C_QUESTOES.TXQUESTAOANTERIOR;
         OUT_RECORD.NRRESPOSTAANTERIOR := C_QUESTOES.NRRESPOSTAANTERIOR;
         OUT_RECORD.DSRESPOSTAANTERIOR := C_QUESTOES.DSRESPOSTAANTERIOR;
         OUT_RECORD.NRQUESTAO          := C_QUESTOES.NRQUESTAO;
         OUT_RECORD.TXQUESTAO          := C_QUESTOES.TXQUESTAO;
         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_QUESTAO       := C_QUESTOES.QTDE_QUESTAO;
         OUT_RECORD.QTDE_RESPOSTA      := C_QUESTOES.QTDE_RESPOSTA;
         OUT_RECORD.NRORDEMQUESTAO     := V_ORDEMQUESTAO;
         OUT_RECORD.IDTIPORESPOSTA     := C_QUESTOES.IDTIPORESPOSTA;
         PIPE ROW (OUT_RECORD);             


         FOR C_REC IN (
            SELECT NRQUESTAOANTERIOR, 
                   TXQUESTAOANTERIOR, 
                   NRRESPOSTAANTERIOR, 
                   DSRESPOSTAANTERIOR, 
                   NRORDEMQUESTAO,
                   NRQUESTAO, 
                   TXQUESTAO, 
                   NRRESPOSTA, 
                   NRORDENARESPOST, 
                   DSRESPOSTA, 
                   VRPESORESPOSTA,
                   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, P_CDFILIAL,
                                                P_NRLOCALPESQ, P_NRQUESTIONARIO, P_IDAPLICATIVO, P_NRTURNO, P_PERIODO, P_DTRESPOSTA, P_DTINICIO, P_DTFIM, C_QUESTOES.NRPROXIMAQUESTAO,
                                                V_ORDEMQUESTAO, P_NRORG, P_ICONES))     
         ) LOOP
            OUT_RECORD.NRQUESTAOANTERIOR  := C_REC.NRQUESTAOANTERIOR;
            OUT_RECORD.TXQUESTAOANTERIOR  := C_REC.TXQUESTAOANTERIOR;
            OUT_RECORD.NRRESPOSTAANTERIOR := C_REC.NRRESPOSTAANTERIOR;
            OUT_RECORD.DSRESPOSTAANTERIOR := C_REC.DSRESPOSTAANTERIOR;
            OUT_RECORD.NRQUESTAO          := C_REC.NRQUESTAO;
            OUT_RECORD.TXQUESTAO          := C_REC.TXQUESTAO;
            OUT_RECORD.NRORDENARESPOST    := C_REC.NRORDENARESPOST;      
            OUT_RECORD.NRRESPOSTA         := C_REC.NRRESPOSTA;
            OUT_RECORD.DSRESPOSTA         := C_REC.DSRESPOSTA;
            OUT_RECORD.VRPESORESPOSTA     := C_REC.VRPESORESPOSTA;
            OUT_RECORD.NRPROXIMAQUESTAO   := C_REC.NRPROXIMAQUESTAO;
            OUT_RECORD.QTDE_QUESTAO       := C_REC.QTDE_QUESTAO;
            OUT_RECORD.QTDE_RESPOSTA      := C_REC.QTDE_RESPOSTA;
            OUT_RECORD.NRORDEMQUESTAO     := C_REC.NRORDEMQUESTAO;
            OUT_RECORD.IDTIPORESPOSTA     := C_REC.IDTIPORESPOSTA;
            PIPE ROW (OUT_RECORD);
         END LOOP;
      ELSE

         OUT_RECORD.NRQUESTAOANTERIOR  := C_QUESTOES.NRQUESTAOANTERIOR;
         OUT_RECORD.TXQUESTAOANTERIOR  := C_QUESTOES.TXQUESTAOANTERIOR;
         OUT_RECORD.NRRESPOSTAANTERIOR := C_QUESTOES.NRRESPOSTAANTERIOR;
         OUT_RECORD.DSRESPOSTAANTERIOR := C_QUESTOES.DSRESPOSTAANTERIOR;
         OUT_RECORD.NRQUESTAO          := C_QUESTOES.NRQUESTAO;
         OUT_RECORD.TXQUESTAO          := C_QUESTOES.TXQUESTAO;
         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_QUESTAO       := C_QUESTOES.QTDE_QUESTAO;
         OUT_RECORD.QTDE_RESPOSTA      := C_QUESTOES.QTDE_RESPOSTA;
         OUT_RECORD.NRORDEMQUESTAO     := V_ORDEMQUESTAO;
         OUT_RECORD.IDTIPORESPOSTA     := C_QUESTOES.IDTIPORESPOSTA;
            
         PIPE ROW (OUT_RECORD);       
      END IF;
   END LOOP;

   RETURN;
   
END;
/