CREATE OR REPLACE FUNCTION CONSPLA_NAO_COTADOS (P_CDFILIAL     IN VARCHAR2,
                                                P_CDFILCENTCMP IN VARCHAR2,
                                                P_CDREGIAO     IN VARCHAR2,
                                                P_CDCOMPRADOR  IN VARCHAR2,
                                                P_DTINICIAL    IN DATE,
                                                P_DTFINAL      IN DATE,
                                                P_PEACRESCIMO  IN NUMBER DEFAULT 0,
                                                P_CDPRODINI    IN VARCHAR2,
                                                P_CDPRODFIN    IN VARCHAR2) RETURN CONSPLA_NAO_COTADOS_TB PIPELINED IS
   OUT_RECORD CONSPLA_NAO_COTADOS_TYPE := CONSPLA_NAO_COTADOS_TYPE (NULL, NULL, NULL, NULL, 0);

   V_IDUTILFATCOR PARAMPLANEJA.IDUTILFATCOR%TYPE;
   V_IDUTILFATCOC PARAMPLANEJA.IDUTILFATCOC%TYPE;
   V_IDTIPCALCFAT PARAMPLANEJA.IDTIPCALCFAT%TYPE;
   V_CDSETCONPERC PARAMPLANEJA.CDSETCONPERC%TYPE;
   V_CDSETCONFIXO PARAMPLANEJA.CDSETCONFIXO%TYPE;
   V_QTCOMESTICAR CARDADIA.QTCOMESTICAR%TYPE;
   V_FERIADO      NUMBER;
   V_IDCARDAPIO   VARCHAR2(1);
   V_IDCONSFIXO   VARCHAR2(1);
   V_IDCONSPERC   VARCHAR2(1);
   V_CONTTURNO    NUMBER;
   V_CONTTURNCARD NUMBER;
   V_QTPRATO      NUMBER;
   V_DTLIMITE     DATE;
   V_HRLIMITE     VARCHAR2(4);

   CUR_CONSPLAN   UTIL_CONSPLAN_PLA.CONSPLAN_CURTYPE;
   REC_CONSPLAN   UTIL_CONSPLAN_PLA.CONSPLAN_RECTYPE;
   CUR_COMPCARD   UTIL_CONSPLAN_PLA.COMPCARD_CURTYPE;
   REC_COMPCARD   UTIL_CONSPLAN_PLA.COMPCARD_RECTYPE;
   CUR_PRODPRAT   UTIL_CONSPLAN_PLA.PRODPRAT_CURTYPE;
   REC_PRODPRAT   UTIL_CONSPLAN_PLA.PRODPRAT_RECTYPE;
   CUR_CONSSERV   UTIL_CONSPLAN_PLA.CONSSERV_CURTYPE;
   REC_CONSSERV   UTIL_CONSPLAN_PLA.CONSSERV_RECTYPE;
BEGIN
   FOR C_PRINCIPAL IN (SELECT MIN(P_CDPRODINI) CDARVINI, MAX(P_CDPRODFIN) CDARVFIN,
                              FILIAL.CDFILIAL, FILIAL.CDREGIAO, MAX(PRODCOMP.CDOPERADOR) CDOPERADOR
                         FROM PRODCOMP, FILIAL, PRODUTO PC,
                              (SELECT CDPRODUTO, CDARVPROD
                                 FROM PRODUTO
                                WHERE PRODUTO.CDARVPROD BETWEEN P_CDPRODINI AND P_CDPRODFIN) P
                        WHERE PRODCOMP.CDPRODUTO = PC.CDPRODUTO
                          AND P.CDARVPROD        LIKE PC.CDARVPROD||'%'
                          AND ((INSTR(P_CDFILIAL, FILIAL.CDFILIAL) > 0) OR (P_CDFILIAL = 'T'))
                          AND ((INSTR(P_CDREGIAO, FILIAL.CDREGIAO) > 0) OR (P_CDREGIAO = 'T'))
                          AND ((INSTR(P_CDCOMPRADOR, PRODCOMP.CDOPERADOR) > 0) OR (P_CDCOMPRADOR = 'T'))
                          AND NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL) = P_CDFILCENTCMP
                          AND EXISTS(SELECT 1
                                       FROM SERVICO
                                      WHERE SERVICO.CDFILIAL = FILIAL.CDFILIAL
                                        AND NVL(SERVICO.IDSERVATIV, 'N') = 'S')
                        GROUP BY FILIAL.CDFILIAL, FILIAL.CDREGIAO
                        ORDER BY FILIAL.CDFILIAL, CDARVINI)
   LOOP
      V_IDCARDAPIO   := SUBSTR('CFP', 1, 1);
      V_IDCONSFIXO   := SUBSTR('CFP', 2, 1);
      V_IDCONSPERC   := SUBSTR('CFP', 3, 1);
      
      IF (V_IDCARDAPIO = 'C') THEN
         SELECT MAX(IDUTILFATCOR), 
                MAX(IDUTILFATCOC),
                MAX(IDTIPCALCFAT)
           INTO V_IDUTILFATCOR,
                V_IDUTILFATCOC,
                V_IDTIPCALCFAT
           FROM PARAMPLANEJA
          WHERE CDFILIAL = C_PRINCIPAL.CDFILIAL;
         
         IF V_IDUTILFATCOC IS NULL THEN
            V_IDUTILFATCOC := 'S';
         END IF;
         
         IF V_IDUTILFATCOR IS NULL THEN
            V_IDUTILFATCOR := 'S';
         END IF;
         
         IF V_IDTIPCALCFAT IS NULL THEN
            V_IDTIPCALCFAT := 'T';
         END IF;
         
         UTIL_CONSPLAN_PLA.OPEN_CONSPLAN_CURSOR(CUR_CONSPLAN,
                                                C_PRINCIPAL.CDFILIAL,
                                                P_DTINICIAL,
                                                P_DTFINAL,
                                                'T',
                                                'C',
                                                '');
         LOOP
            FETCH CUR_CONSPLAN INTO REC_CONSPLAN;
            EXIT WHEN CUR_CONSPLAN%NOTFOUND;

            IF (REC_CONSPLAN.CDCLIENTE IS NULL) OR (REC_CONSPLAN.CDCLIENTE = 'XXX') THEN
               V_CONTTURNCARD := 0;
               IF NOT REC_CONSPLAN.QTESTITURNO1 IS NULL THEN
                  V_CONTTURNCARD := V_CONTTURNCARD + 1;
               END IF;
               
               IF NOT REC_CONSPLAN.QTESTITURNO2 IS NULL THEN
                  V_CONTTURNCARD := V_CONTTURNCARD + 1;
               END IF;
               
               IF NOT REC_CONSPLAN.QTESTITURNO3 IS NULL THEN
                  V_CONTTURNCARD := V_CONTTURNCARD + 1;
               END IF;
               
               IF NOT REC_CONSPLAN.QTESTITURNO4 IS NULL THEN
                  V_CONTTURNCARD := V_CONTTURNCARD + 1;
               END IF;
               
               SELECT COUNT(*)
                 INTO V_CONTTURNO
                 FROM TURNOSERV
                WHERE CDFILIAL   = C_PRINCIPAL.CDFILIAL
                  AND CDSERVFILI = REC_CONSPLAN.CDSERVFILI;
               
               IF V_CONTTURNO = 0 THEN
                  REC_CONSPLAN.QTESTITURNO1 := NULL;
                  REC_CONSPLAN.QTESTITURNO2 := NULL;
                  REC_CONSPLAN.QTESTITURNO3 := NULL;
                  REC_CONSPLAN.QTESTITURNO4 := NULL;
               ELSE
                  SELECT COUNT(*) 
                    INTO V_CONTTURNO 
                    FROM TURNOSERV
                   WHERE CDFILIAL = C_PRINCIPAL.CDFILIAL 
                     AND CDSERVFILI = REC_CONSPLAN.CDSERVFILI
                     AND CDTURNO = '1';
                  
                  IF (V_CONTTURNO = 0) THEN 
                     REC_CONSPLAN.QTESTITURNO1 := NULL; 
                  END IF;
                  
                  SELECT COUNT(*) 
                    INTO V_CONTTURNO 
                    FROM TURNOSERV
                   WHERE CDFILIAL = C_PRINCIPAL.CDFILIAL 
                     AND CDSERVFILI = REC_CONSPLAN.CDSERVFILI 
                     AND CDTURNO = '2';
                  
                  IF (V_CONTTURNO = 0) THEN 
                     REC_CONSPLAN.QTESTITURNO2 := NULL; 
                  END IF;
                  
                  SELECT COUNT(*) 
                    INTO V_CONTTURNO 
                    FROM TURNOSERV
                   WHERE CDFILIAL = C_PRINCIPAL.CDFILIAL
                     AND CDSERVFILI = REC_CONSPLAN.CDSERVFILI 
                     AND CDTURNO = '3';
                  
                  IF (V_CONTTURNO = 0) THEN 
                     REC_CONSPLAN.QTESTITURNO3 := NULL; 
                  END IF;
                  
                  SELECT COUNT(*) 
                    INTO V_CONTTURNO 
                    FROM TURNOSERV
                   WHERE CDFILIAL = C_PRINCIPAL.CDFILIAL 
                     AND CDSERVFILI = REC_CONSPLAN.CDSERVFILI 
                     AND CDTURNO = '4';
                  
                  IF (V_CONTTURNO = 0) THEN 
                     REC_CONSPLAN.QTESTITURNO4 := NULL; 
                  END IF;
               END IF;
               IF REC_CONSPLAN.QTESTITURNO1 IS NULL THEN REC_CONSPLAN.QTESTITURNO1 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO2 IS NULL THEN REC_CONSPLAN.QTESTITURNO2 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO3 IS NULL THEN REC_CONSPLAN.QTESTITURNO3 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO4 IS NULL THEN REC_CONSPLAN.QTESTITURNO4 := 0; END IF;
            END IF;
            
            IF REC_CONSPLAN.QTCOMESTICAR <> 0 THEN
               UTIL_CONSPLAN_PLA.OPEN_COMPCARD_CURSOR(CUR_COMPCARD,
                                                      C_PRINCIPAL.CDFILIAL,
                                                      REC_CONSPLAN.NRCARDAPIO);
               LOOP  
                  FETCH CUR_COMPCARD INTO REC_COMPCARD;
                  EXIT WHEN CUR_COMPCARD%NOTFOUND;
                  IF REC_COMPCARD.VRPECONSCARD <> 0 THEN
                     V_QTPRATO := (REC_COMPCARD.VRPECONSCARD * REC_CONSPLAN.QTCOMESTICAR)/ 100 ;
                     UTIL_CONSPLAN_PLA.OPEN_PRODPRAT_CURSOR(CUR_PRODPRAT,
                                                            C_PRINCIPAL.CDFILIAL,
                                                            REC_CONSPLAN.CDSERVFILI,
                                                            REC_COMPCARD.CDFILAUXPRAT,
                                                            REC_COMPCARD.CDPRATO,
                                                            V_QTPRATO,
                                                            C_PRINCIPAL.CDARVINI,
                                                            C_PRINCIPAL.CDARVFIN,
                                                            V_IDUTILFATCOR,
                                                            V_IDUTILFATCOC,
                                                            V_IDTIPCALCFAT);
                     LOOP
                        FETCH CUR_PRODPRAT INTO REC_PRODPRAT;
                        EXIT WHEN CUR_PRODPRAT%NOTFOUND;
                        REC_PRODPRAT.QTPRODUTO := REC_PRODPRAT.QTPRODUTO * V_QTPRATO;
                        REC_PRODPRAT.QTBRUTAUNR := REC_PRODPRAT.QTBRUTAUNR * V_QTPRATO;
                        V_DTLIMITE := TO_DATE(TO_CHAR(REC_CONSPLAN.DTDATA, 'DD/MM/YYYY')||REC_CONSPLAN.HRINICATEN, 'DD/MM/YYYYHH24MI');
                        SUBTHTOD_PLA(V_DTLIMITE,REC_PRODPRAT.HRTEMPPREP,V_DTLIMITE);
                        V_HRLIMITE := TO_CHAR(V_DTLIMITE, 'HH24MI');
                          
                        OUT_RECORD.CDFILIAL         := C_PRINCIPAL.CDFILIAL;
                        OUT_RECORD.CDREGIAO         := C_PRINCIPAL.CDREGIAO;
                        OUT_RECORD.CDPRODUTO        := REC_PRODPRAT.CDPRODUTO;
                        OUT_RECORD.DTPREVCONS       := V_DTLIMITE;
                        OUT_RECORD.QTPREVCONS       := (REC_PRODPRAT.QTPRODUTO) * (1 + (P_PEACRESCIMO / 100));
                        PIPE ROW (OUT_RECORD);
                     END LOOP;
                     CLOSE CUR_PRODPRAT;
                  END IF;
               END LOOP;
               CLOSE CUR_COMPCARD;
            END IF;
         END LOOP;
         CLOSE CUR_CONSPLAN;
      END IF;
      
      IF (V_IDCONSFIXO = 'F') OR (V_IDCONSPERC = 'P') THEN
         SELECT MAX(CDSETCONFIXO),
                MAX(CDSETCONPERC)
           INTO V_CDSETCONFIXO,
                V_CDSETCONPERC
           FROM PARAMPLANEJA
          WHERE CDFILIAL = C_PRINCIPAL.CDFILIAL;
         
         IF (V_IDCONSPERC = 'P') THEN
            UTIL_CONSPLAN_PLA.OPEN_CONSPLAN_CURSOR(CUR_CONSPLAN,
                                                   C_PRINCIPAL.CDFILIAL,
                                                   P_DTINICIAL,
                                                   P_DTFINAL,
                                                   'T',
                                                   'P',
                                                   '');
            LOOP
               FETCH CUR_CONSPLAN INTO REC_CONSPLAN;
               EXIT WHEN CUR_CONSPLAN%NOTFOUND;
               IF REC_CONSPLAN.QTESTITURNO1 IS NULL THEN REC_CONSPLAN.QTESTITURNO1 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO2 IS NULL THEN REC_CONSPLAN.QTESTITURNO2 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO3 IS NULL THEN REC_CONSPLAN.QTESTITURNO3 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO4 IS NULL THEN REC_CONSPLAN.QTESTITURNO4 := 0; END IF;
               
               SELECT COUNT(*)
                 INTO V_FERIADO
                 FROM FERIFILI
                WHERE CDFILIAL   = C_PRINCIPAL.CDFILIAL
                  AND DTFERIFILI = REC_CONSPLAN.DTDATA;

               UTIL_CONSPLAN_PLA.OPEN_CONSSERV_CURSOR(CUR_CONSSERV,
                                                      C_PRINCIPAL.CDFILIAL,
                                                      REC_CONSPLAN.CDSERVFILI,
                                                      REC_CONSPLAN.DTDATA,
                                                      V_FERIADO,
                                                      C_PRINCIPAL.CDARVINI,
                                                      C_PRINCIPAL.CDARVFIN,
                                                      'P');
               LOOP
                  FETCH CUR_CONSSERV INTO REC_CONSSERV;
                  EXIT WHEN CUR_CONSSERV%NOTFOUND;
               
                  IF((V_IDCONSPERC = 'P') AND (NOT REC_CONSPLAN.NRCARDAPIO IS NULL)) THEN
                     V_QTCOMESTICAR := (REC_CONSSERV.QTCONSSERV * REC_CONSPLAN.QTCOMESTICAR) * (1 + (P_PEACRESCIMO / 100));
                     IF(V_IDCONSPERC = 'P' AND REC_CONSSERV.IDQTDECONS = 'P') THEN
                        OUT_RECORD.CDFILIAL         := C_PRINCIPAL.CDFILIAL;
                        OUT_RECORD.CDREGIAO         := C_PRINCIPAL.CDREGIAO;
                        OUT_RECORD.CDPRODUTO        := REC_CONSSERV.CDPRODUTO;
                        OUT_RECORD.DTPREVCONS       := REC_CONSPLAN.DTDATA;
                        OUT_RECORD.QTPREVCONS       := V_QTCOMESTICAR;
                        PIPE ROW (OUT_RECORD);
                     END IF;
                  END IF;
               END LOOP;
               CLOSE CUR_CONSSERV;
            END LOOP;
            CLOSE CUR_CONSPLAN;
         END IF;

         IF (V_IDCONSFIXO = 'F') THEN
            UTIL_CONSPLAN_PLA.OPEN_CONSPLAN_CURSOR(CUR_CONSPLAN,
                                                   C_PRINCIPAL.CDFILIAL,
                                                   P_DTINICIAL,
                                                   P_DTFINAL,
                                                   'T',
                                                   'F',
                                                   '');
            LOOP
               FETCH CUR_CONSPLAN INTO REC_CONSPLAN;
               EXIT WHEN CUR_CONSPLAN%NOTFOUND;
               IF REC_CONSPLAN.QTESTITURNO1 IS NULL THEN REC_CONSPLAN.QTESTITURNO1 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO2 IS NULL THEN REC_CONSPLAN.QTESTITURNO2 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO3 IS NULL THEN REC_CONSPLAN.QTESTITURNO3 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO4 IS NULL THEN REC_CONSPLAN.QTESTITURNO4 := 0; END IF;
            
               SELECT COUNT(*)
                 INTO V_FERIADO
                 FROM FERIFILI
                WHERE CDFILIAL   = C_PRINCIPAL.CDFILIAL
                  AND DTFERIFILI = REC_CONSPLAN.DTDATA;
               
               UTIL_CONSPLAN_PLA.OPEN_CONSSERV_CURSOR(CUR_CONSSERV,
                                                      C_PRINCIPAL.CDFILIAL,
                                                      REC_CONSPLAN.CDSERVFILI,
                                                      REC_CONSPLAN.DTDATA,
                                                      V_FERIADO,
                                                      C_PRINCIPAL.CDARVINI,
                                                      C_PRINCIPAL.CDARVFIN,
                                                      'F');
               LOOP
                  FETCH CUR_CONSSERV INTO REC_CONSSERV;
                  EXIT WHEN CUR_CONSSERV%NOTFOUND;
               
                  IF ((V_IDCONSFIXO = 'F') AND (REC_CONSSERV.IDQTDECONS = 'F')) THEN
                     IF REC_CONSSERV.IDQTDECONS = 'F' THEN
                        V_QTCOMESTICAR := (REC_CONSSERV.QTCONSSERV) * (1 + (P_PEACRESCIMO / 100));
                     END IF;
               
                     IF (V_IDCONSFIXO = 'F' AND REC_CONSSERV.IDQTDECONS = 'F') THEN
                        OUT_RECORD.CDFILIAL         := C_PRINCIPAL.CDFILIAL;
                        OUT_RECORD.CDREGIAO         := C_PRINCIPAL.CDREGIAO;
                        OUT_RECORD.CDPRODUTO        := REC_CONSSERV.CDPRODUTO;
                        OUT_RECORD.DTPREVCONS       := REC_CONSPLAN.DTDATA;
                        OUT_RECORD.QTPREVCONS       := V_QTCOMESTICAR;
                        PIPE ROW (OUT_RECORD);
                     END IF;
                  END IF;
               END LOOP;
               CLOSE CUR_CONSSERV;
            END LOOP;
            CLOSE CUR_CONSPLAN;

            UTIL_CONSPLAN_PLA.OPEN_CONSPLAN_CURSOR(CUR_CONSPLAN,
                                                   C_PRINCIPAL.CDFILIAL,
                                                   P_DTINICIAL,
                                                   P_DTFINAL,
                                                   'T',
                                                   'E',
                                                   '');
            LOOP
               FETCH CUR_CONSPLAN INTO REC_CONSPLAN;
               EXIT WHEN CUR_CONSPLAN%NOTFOUND;
               IF REC_CONSPLAN.QTESTITURNO1 IS NULL THEN REC_CONSPLAN.QTESTITURNO1 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO2 IS NULL THEN REC_CONSPLAN.QTESTITURNO2 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO3 IS NULL THEN REC_CONSPLAN.QTESTITURNO3 := 0; END IF;
               IF REC_CONSPLAN.QTESTITURNO4 IS NULL THEN REC_CONSPLAN.QTESTITURNO4 := 0; END IF;
               
               SELECT COUNT(*)
                 INTO V_FERIADO
                 FROM FERIFILI
                WHERE CDFILIAL   = C_PRINCIPAL.CDFILIAL
                  AND DTFERIFILI = REC_CONSPLAN.DTDATA;

               UTIL_CONSPLAN_PLA.OPEN_CONSSERV_CURSOR(CUR_CONSSERV,
                                                      C_PRINCIPAL.CDFILIAL,
                                                      REC_CONSPLAN.CDSERVFILI,
                                                      REC_CONSPLAN.DTDATA,
                                                      V_FERIADO,
                                                      C_PRINCIPAL.CDARVINI,
                                                      C_PRINCIPAL.CDARVFIN,
                                                      'E');
               LOOP
                  FETCH CUR_CONSSERV INTO REC_CONSSERV;
                  EXIT WHEN CUR_CONSSERV%NOTFOUND;
                  
                  IF ((V_IDCONSFIXO = 'F') AND (REC_CONSSERV.IDQTDECONS = 'F')) THEN
                     IF REC_CONSSERV.IDQTDECONS = 'F' THEN
                        V_QTCOMESTICAR := (REC_CONSSERV.QTCONSSERV) * (1 + (P_PEACRESCIMO / 100));
                     END IF;
                     
                     IF (V_IDCONSFIXO = 'F' AND REC_CONSSERV.IDQTDECONS = 'F') THEN
                        OUT_RECORD.CDFILIAL         := C_PRINCIPAL.CDFILIAL;
                        OUT_RECORD.CDREGIAO         := C_PRINCIPAL.CDREGIAO;
                        OUT_RECORD.CDPRODUTO        := REC_CONSSERV.CDPRODUTO;
                        OUT_RECORD.DTPREVCONS       := REC_CONSPLAN.DTDATA;
                        OUT_RECORD.QTPREVCONS       := V_QTCOMESTICAR;
                        PIPE ROW (OUT_RECORD);
                     END IF;
                  END IF;
               END LOOP;
               CLOSE CUR_CONSSERV;
            END LOOP;
            CLOSE CUR_CONSPLAN;
         END IF;
      END IF;
   END LOOP;
   RETURN;
END CONSPLA_NAO_COTADOS;
/