DROP FUNCTION GERA_ACRES_PEDCOLETA;
/

DROP TYPE GERA_ACRES_PEDCOLETA_TABLE;
/

DROP TYPE GERA_ACRES_PEDCOLETA_TYPE;
/

CREATE TYPE GERA_ACRES_PEDCOLETA_TYPE AS OBJECT
  (
   CDREGIAO       VARCHAR2(4) , 
   NMREGIAO       VARCHAR2(30), 
   CDOPERADOR     VARCHAR2(12), 
   NMOPERADOR     VARCHAR2(40), 
   CDFORNECED     VARCHAR2(20), 
   NRINSJURFORN   VARCHAR2(14), 
   IDTPIJURFORN   VARCHAR2(3) , 
   NMRAZSOCFORN   VARCHAR2(40), 
   NMFANTFORN     VARCHAR2(40), 
   CDPRODUTO      VARCHAR2(12), 
   NMPRODUTO      VARCHAR2(50), 
   SGUNIDADE      VARCHAR2(12), 
   CDPRODESTO     VARCHAR2(12), 
   IDTPFRETFORN   VARCHAR2(3) , 
   CDMARCA        VARCHAR2(5) , 
   NMMARCA        VARCHAR2(20), 
   CDMARCAPADR    VARCHAR2(5) , 
   CDARVPROD      VARCHAR2(12), 
   QTACOMPRAR     INT         , 
   NRCOMPRA       VARCHAR2(20)  
  );
/

CREATE TYPE GERA_ACRES_PEDCOLETA_TABLE AS TABLE OF GERA_ACRES_PEDCOLETA_TYPE;
/

CREATE OR REPLACE FUNCTION GERA_ACRES_PEDCOLETA
                          (P_CDPRODINI       IN VARCHAR,
                           P_CDPRODFIN       IN VARCHAR,
                           P_CDFILIAL        IN VARCHAR,
                           P_MARCA           IN VARCHAR,
                           P_COMPRADOR       IN VARCHAR,
                           P_REGIAO          IN VARCHAR,
                           P_FORNECED        IN VARCHAR,
                           P_NRCOMPRA        IN VARCHAR,
                           P_DTINI           IN DATE   ,
                           P_DTFIN           IN DATE   ,
                           P_BOACRESITENCONS IN VARCHAR)
                          RETURN GERA_ACRES_PEDCOLETA_TABLE PIPELINED IS

   OUT_RECORD GERA_ACRES_PEDCOLETA_TYPE := GERA_ACRES_PEDCOLETA_TYPE (NULL, NULL,
                                                                      NULL, NULL,
                                                                      NULL, NULL,
                                                                      NULL, NULL,
                                                                      NULL, NULL,
                                                                      NULL, NULL,
                                                                      NULL, NULL,
                                                                      NULL, NULL,
                                                                      NULL, NULL,
                                                                      0,    NULL);
   V_T_CDFILIAL         VARCHAR2(1);
   V_DTPEDCOLETA        DATE;
   V_QTDACOMPRAR        INT   := 0;
   V_CONTADOR           INT   := 0;
   V_CDPRODINI            VARCHAR(12);
   V_CDPRODFIN            VARCHAR(12);
   
   /*VARIAVEIS PARA VALIDAÇÃO:      --TIPO DE VALIDAÇÃO*/
   V_VDPRODCMP          INT   := 0; --Produto por Comprador
   V_VDREFOREG          INT   := 0; --Região não atendida pelo fornecedor
   V_VDREFORFIL         INT   := 0; --Filial Atendida/Não Atendida pelo fornecedor
   V_VDMARCPROD         INT   := 0; --Marca por Produto
   V_VDMARCATIV         INT   := 0; --Marca ativa para pedido de coleta TABELA: MARCA
   V_VDMARCFORN         INT   := 0; --Marcas por Fornecedor para Pedido de Coleta TABELA: MARCFORN
   V_VDQUALIFORN        INT   := 0; --Qualificação de Fornecedores obs. join com a tabela de qualificacao
   V_VDQFOMARPROD       INT   := 0; --Qualificação de Marcas por Produtos
   V_VDQFOPRODFORN      INT   := 0; --Qualificação de Produtos por Fornecedor

BEGIN
   IF (P_BOACRESITENCONS = '1') THEN -- '1' REPRESENTA O TRUE DO PARÂMETRO.
      
      SELECT RPAD(P_CDPRODINI, 10, '0')
      INTO V_CDPRODINI
      FROM DUAL;
      
      SELECT RPAD(P_CDPRODFIN, 10, 'Z')
      INTO V_CDPRODFIN
      FROM DUAL;
      
      IF (P_CDFILIAL = 'T') THEN
         V_T_CDFILIAL := 'T';
      ELSE
         V_T_CDFILIAL := '';
      END IF;
      FOR CUR_PREVCONS IN 
      (
         SELECT P.CDPRODUTO, P.CDFILIAL, SUM(QTPREVCONS) AS QTPREVCONS
         FROM PREVCONS P
         WHERE 
            (INSTR(P_CDFILIAL, P.CDFILIAL) > 0 OR V_T_CDFILIAL = 'T') AND
            (P.CDPRODUTO BETWEEN V_CDPRODINI AND V_CDPRODFIN) AND
            (DTPREVCONS  BETWEEN P_DTINI     AND P_DTFIN)     AND 
            NOT EXISTS
               (
                  SELECT CDPRODUTO
                  FROM TMP_PEDCOLETA TPC
                  WHERE
                     (TPC.CDPRODUTO = P.CDPRODUTO)
               )
         GROUP BY
            P.CDPRODUTO, P.CDFILIAL
      )
      LOOP
         
         V_CONTADOR := 0;
         FOR CUR_ITENS IN -- SE O PRODUTO NÃO ESTIVER NA TABELA TEMPORARIA, ADICIONA ELE AO PEDIDO DE COLETA.
         (
            SELECT
               IPC.CDMARCA, IPC.CDREGIAO, IPC.CDFORNECED, PC.CDOPERADOR, MAX(DTPEDCOLETA),
               OP.NMOPERADOR, RE.NMREGIAO, MA.NMMARCA, FO.NRINSJURFORN, FO.IDTPIJURFORN,
               FO.NMRAZSOCFORN, FO.NMFANTFORN, PR.NMPRODUTO, PR.CDARVPROD, PR.SGUNIDADE,
               FO.IDTPFRETFORN, PR.CDPRODESTO, PS.CDMARCA AS CDMARCAPADR
            FROM 
               PEDCOLETA PC, ITEMPEDCOL IPC,
               OPERADOR OP, REGIAO RE, MARCA MA,
               FORNECEDOR FO, PRODUTO PR,
               PARASUPRI PS
            WHERE
               MA.CDMARCA        = IPC.CDMARCA        AND
               PS.CDFILIAL       = PC.CDFILIAL        AND
               PC.CDFILIAL       = IPC.CDFILIAL       AND
               PC.CDFILIAL       = P_CDFILIAL         AND
               PC.NRPEDCOLETA    = IPC.NRPEDCOLETA    AND
               PC.CDREGIAO       = IPC.CDREGIAO       AND
               RE.CDREGIAO       = PC.CDREGIAO        AND
               PC.CDFORNECED     = IPC.CDFORNECED     AND
               FO.CDFORNECED     = PC.CDFORNECED      AND
               OP.CDOPERADOR     = PC.CDOPERADOR      AND
               PR.CDPRODUTO      = IPC.CDPRODUTO      AND
               IPC.CDPRODUTO     = CUR_PREVCONS.CDPRODUTO
            GROUP BY
               IPC.CDMARCA,
               IPC.CDREGIAO,
               IPC.CDFORNECED,
               PC.CDOPERADOR,
               OP.NMOPERADOR,
               RE.NMREGIAO,
               MA.NMMARCA,
               FO.NRINSJURFORN,
               FO.IDTPIJURFORN,
               FO.NMRAZSOCFORN,
               FO.NMFANTFORN,
               PR.NMPRODUTO,
               PR.CDARVPROD,
               PR.SGUNIDADE,
               FO.IDTPFRETFORN,
               PR.CDPRODESTO,
               PS.CDMARCA
         )
         LOOP
            
            SELECT SUM(NVL(QTSOLIPROD,0)) AS QTACOMPRAR
            INTO V_QTDACOMPRAR
            FROM ITEMSOLI ITS
            WHERE
               (ITS.DTPROGENTR BETWEEN P_DTINI AND P_DTFIN) AND
               (ITS.CDPRODUTO = CUR_PREVCONS.CDPRODUTO) AND
               (ITS.CDFILSOLI = P_CDFILIAL);
            
            V_CONTADOR := V_CONTADOR + 1;  --VARIAVEL DE CONTROLE
            OUT_RECORD.CDPRODUTO    := CUR_PREVCONS.CDPRODUTO;
            OUT_RECORD.SGUNIDADE    := CUR_ITENS.SGUNIDADE;
            OUT_RECORD.CDMARCA      := CUR_ITENS.CDMARCA;
            OUT_RECORD.CDREGIAO     := CUR_ITENS.CDREGIAO;
            OUT_RECORD.CDFORNECED   := CUR_ITENS.CDFORNECED;
            OUT_RECORD.CDOPERADOR   := CUR_ITENS.CDOPERADOR;
            OUT_RECORD.NMOPERADOR   := CUR_ITENS.NMOPERADOR;
            OUT_RECORD.NMREGIAO     := CUR_ITENS.NMREGIAO;
            OUT_RECORD.NMMARCA      := CUR_ITENS.NMMARCA;
            OUT_RECORD.CDARVPROD    := CUR_ITENS.CDARVPROD;
            OUT_RECORD.NRINSJURFORN := CUR_ITENS.NRINSJURFORN;
            OUT_RECORD.IDTPIJURFORN := CUR_ITENS.IDTPIJURFORN;
            OUT_RECORD.NMRAZSOCFORN := CUR_ITENS.NMRAZSOCFORN;
            OUT_RECORD.NMFANTFORN   := CUR_ITENS.NMFANTFORN;
            OUT_RECORD.IDTPFRETFORN := CUR_ITENS.IDTPFRETFORN;
            OUT_RECORD.CDPRODESTO   := CUR_ITENS.CDPRODESTO;
            OUT_RECORD.CDMARCAPADR  := CUR_ITENS.CDMARCAPADR;
            IF V_QTDACOMPRAR <> 0 THEN
               OUT_RECORD.QTACOMPRAR := V_QTDACOMPRAR;
            ELSE
               OUT_RECORD.QTACOMPRAR := CUR_PREVCONS.QTPREVCONS;
            END IF;
            OUT_RECORD.NRCOMPRA     := P_NRCOMPRA;
            PIPE ROW (OUT_RECORD);  -- COLOCAR DEPOIS DE TODOS OUT_RECORD
         END LOOP;
         IF (V_CONTADOR = 0) THEN   --CASO O PRODUTO NUNCA TENHA TIDO UM PEDIDO DE COLETA, ELE ENTRA NESSA PARTE.
            
            FOR CUR_ACRES IN
            (
               SELECT DISTINCT 
                  MA.CDMARCA, RE.CDREGIAO, FO.CDFORNECED, OP.CDOPERADOR, /*MAX(DTPEDCOLETA),*/
                  OP.NMOPERADOR, RE.NMREGIAO, MA.NMMARCA, FO.NRINSJURFORN, FO.IDTPIJURFORN,
                  FO.NMRAZSOCFORN, FO.NMFANTFORN, PR.NMPRODUTO, PR.CDARVPROD, PR.SGUNIDADE,
                  FO.IDTPFRETFORN, PR.CDPRODESTO, PS.CDMARCA AS CDMARCAPADR
               FROM
                  OPERADOR OP, REGIAO RE, MARCA MA,
                  FORNECEDOR FO, PRODUTO PR,
                  PARASUPRI PS
               WHERE
                  PS.CDFILIAL = P_CDFILIAL  AND
                  (INSTR(P_MARCA, MA.CDMARCA)        > 0
                     OR (P_MARCA = 'T')
                  ) AND
                  (INSTR(P_COMPRADOR, OP.CDOPERADOR) > 0
                     OR (P_COMPRADOR = 'T')
                  ) AND
                  (INSTR(P_REGIAO, RE.CDREGIAO)      > 0
                     OR (P_REGIAO = 'T')
                  ) AND
                  (INSTR(P_FORNECED, FO.CDFORNECED)  > 0
                     OR (P_FORNECED = 'T')
                  ) AND
                  PR.CDPRODUTO = CUR_PREVCONS.CDPRODUTO
            )
            LOOP
            /*VALIDAÇÕES PARA VER QUAIS OS DADOS CORRETOS DO PRODUTO*/
               --Produto por Comprador
               SELECT 
                  COUNT(PC.CDPRODUTO)
               INTO
                  V_VDPRODCMP
               FROM
                  PRODCOMP PC
               WHERE
                  (INSTR(P_COMPRADOR,PC.CDOPERADOR) > 0) AND
                  (PC.CDPRODUTO IN 
                     (
                        SELECT P2.CDARVPROD
                        FROM PRODUTO P1, PRODUTO P2
                        WHERE P1.CDPRODUTO = CUR_PREVCONS.CDPRODUTO AND
                           P1.CDARVPROD LIKE P2.CDARVPROD || '%'
                     )
                  );
               
               
               IF V_VDPRODCMP > 0 THEN
                  --Região não atendida pelo fornecedor
                  /*--------------------------------------------------------
                     CASO A REGIAO NÃO SEJA ATENDIDA PELO FORNECEDOR,       
                     V_VDREFOREG > 0, NÃO ENTRA NO IF.                      
                  --------------------------------------------------------*/
                  SELECT 
                     COUNT(REFOREG.CDREGIAO)
                  INTO
                     V_VDREFOREG
                  FROM
                     REFOREG
                  WHERE
                     REFOREG.CDREGIAO    = CUR_ACRES.CDREGIAO   AND
                     REFOREG.CDFORNECED  = CUR_ACRES.CDFORNECED;
                  
                  IF V_VDREFOREG = 0 THEN
                     --Filial Atendida/Não Atendida pelo fornecedor
                     /*--------------------------------------------------------
                        VERIFICA SE A FILIAL ESTÁ NA TABELA DAS NÃO ATENDIDAS, 
                        SE ESTIVER, V_VDREFORFIL > 0, NÃO ENTRA NO IF.         
                     --------------------------------------------------------*/
                     SELECT 
                       COUNT(REFORFIL.CDFILIAL)
                     INTO
                        V_VDREFORFIL
                     FROM
                       REFORFIL
                     WHERE
                       REFORFIL.CDFILIAL   = P_CDFILIAL  AND
                       CDPERIENTR IS NULL                  AND
                       REFORFIL.CDFORNECED = CUR_ACRES.CDFORNECED;
                     
                     IF V_VDREFORFIL = 0 THEN
                        --Marca por Produto
                        SELECT COUNT(*) 
                        INTO V_VDMARCPROD
                        FROM MARCPROD MP
                        WHERE
                          (CUR_ACRES.CDMARCA = MP.CDMARCA) AND
                          (MP.CDPRODUTO IN
                              (
                                 SELECT P2.CDARVPROD
                                 FROM PRODUTO P1, PRODUTO P2
                                 WHERE P1.CDPRODUTO = CUR_PREVCONS.CDPRODUTO AND
                                    P1.CDARVPROD LIKE P2.CDARVPROD || '%'
                              )
                          );
                        
                        IF V_VDMARCPROD > 0 THEN
                           --Marca ativa para pedido de coleta
                           SELECT COUNT(CDMARCA) 
                           INTO V_VDMARCATIV
                           FROM MARCA
                           WHERE
                             (CDMARCA = CUR_ACRES.CDMARCA) AND
                             (IDMARCAATIVA = 'A');
                           
                           IF V_VDMARCATIV > 0 THEN
                              --Marcas por Fornecedor para Pedido de Coleta
                              SELECT COUNT(*)
                              INTO V_VDMARCFORN
                              FROM MARCFORNPEDCOL
                              WHERE
                                (CDMARCA = CUR_ACRES.CDMARCA) AND
                                (CDFORNECED = CUR_ACRES.CDFORNECED);
                              
                              IF V_VDMARCFORN > 0 THEN
                                 --Qualificação de Fornecedores
                                 SELECT
                                    COUNT(F.CDFORNECED)
                                 INTO V_VDQUALIFORN
                                 FROM FORNECEDOR F,
                                    QUALIFICACAO Q
                                 WHERE
                                    (Q.CDQUALIF(+) = F.CDQUALIF)  AND
                                    (F.CDFORNECED = CUR_ACRES.CDFORNECED)  AND
                                    ( F.DTVALQUALIF > P_DTINI) AND
                                    /*((Q.IDPERCOTCMP IN (:P_PERMITE, :P_NPERMITE))) AND*/
                                    ((F.IDUTLALVFORN = 'S' AND DTALVARAFORN IS NOT NULL) OR (F.IDUTLALVFORN = 'N'));
                                 
                                 IF V_VDQUALIFORN > 0 THEN
                                    --Qualificação de Marcas por Produtos
                                    SELECT SUM(MARCAS) AS MARCA
                                    INTO V_VDQFOMARPROD
                                    FROM(
                                       SELECT
                                       QFOMARPROD.CDMARCA AS MARCAS
                                       FROM
                                       QFOMARPROD
                                       WHERE
                                       QFOMARPROD.CDPRODUTO = CUR_PREVCONS.CDPRODUTO AND
                                       QFOMARPROD.CDMARCA   = CUR_ACRES.CDMARCA
                                 
                                       UNION
                                       
                                       SELECT
                                       PARASUPRI.CDMARCA AS MARCAS
                                       FROM
                                       PARASUPRI
                                       WHERE
                                       PARASUPRI.CDFILIAL    = P_CDFILIAL AND
                                       (PARASUPRI.IDMARSEMQUA = 'N' OR PARASUPRI.IDMARSEMQUA IS NULL)
                                       );
                                    
                                    IF V_VDQFOMARPROD > 0 THEN
                                       --Qualificação de Produtos por Fornecedor
                                       SELECT
                                          COUNT(QFOPRODFORN.CDPRODUTO)
                                       INTO
                                          V_VDQFOPRODFORN
                                       FROM
                                          QFOPRODFORN,
                                          PARASUPRI
                                       WHERE
                                       PARASUPRI.CDFILIAL = P_CDFILIAL  AND
                                       (QFOPRODFORN.CDPRODUTO = CUR_PREVCONS.CDPRODUTO) AND
                                       (QFOPRODFORN.CDFORNECED = CUR_ACRES.CDFORNECED) AND
                                       ((QFOPRODFORN.IDFORNAPROV = 'N' AND QFOPRODFORN.IDFORNAUTOR = 'N') OR
                                          ((QFOPRODFORN.IDFORNAPROV = 'S') AND
                                             ((PARASUPRI.IDULTIDTVALQ = 'M' AND
                                             P_DTFIN < LAST_DAY(QFOPRODFORN.DTVALPRFORN)) OR
                                             (PARASUPRI.IDULTIDTVALQ = 'D' AND
                                             P_DTFIN < QFOPRODFORN.DTVALPRFORN)) ) );
                                       
                                       IF V_VDQFOPRODFORN > 0 THEN
                                       
                                          OUT_RECORD.CDPRODUTO    := CUR_PREVCONS.CDPRODUTO;
                                          OUT_RECORD.SGUNIDADE    := CUR_ACRES.SGUNIDADE;
                                          OUT_RECORD.CDMARCA      := CUR_ACRES.CDMARCA;
                                          OUT_RECORD.CDREGIAO     := CUR_ACRES.CDREGIAO;
                                          OUT_RECORD.CDFORNECED   := CUR_ACRES.CDFORNECED;
                                          OUT_RECORD.CDOPERADOR   := CUR_ACRES.CDOPERADOR;
                                          OUT_RECORD.NMOPERADOR   := CUR_ACRES.NMOPERADOR;
                                          OUT_RECORD.NMREGIAO     := CUR_ACRES.NMREGIAO;
                                          OUT_RECORD.NMMARCA      := CUR_ACRES.NMMARCA;
                                          OUT_RECORD.CDARVPROD    := CUR_ACRES.CDARVPROD;
                                          OUT_RECORD.NRINSJURFORN := CUR_ACRES.NRINSJURFORN;
                                          OUT_RECORD.IDTPIJURFORN := CUR_ACRES.IDTPIJURFORN;
                                          OUT_RECORD.NMRAZSOCFORN := CUR_ACRES.NMRAZSOCFORN;
                                          OUT_RECORD.NMFANTFORN   := CUR_ACRES.NMFANTFORN;
                                          OUT_RECORD.IDTPFRETFORN := CUR_ACRES.IDTPFRETFORN;
                                          OUT_RECORD.CDPRODESTO   := CUR_ACRES.CDPRODESTO;
                                          OUT_RECORD.CDMARCAPADR  := CUR_ACRES.CDMARCAPADR;
                                          OUT_RECORD.QTACOMPRAR   := CUR_PREVCONS.QTPREVCONS;
                                          OUT_RECORD.NRCOMPRA     := P_NRCOMPRA;
                                          PIPE ROW (OUT_RECORD);  -- COLOCAR DEPOIS DE TODOS OUT_RECORD
                                       END IF; --VDQFOPRODFORN
                                       
                                    END IF; --VDQFOMARPROD
                                    
                                 END IF; --VDQUALIFORN
                                 
                              END IF; --VDMARCFORN
                              
                           END IF; --VDMARCATIV
                           
                        END IF; --VDMARCPROD
                     
                     END IF; --VDREFORFIL
                  
                  END IF; --VDREFOREG
                     
               END IF; --VDPRODNCMP

            END LOOP;
            
         END IF;
         
      END LOOP;
      
      RETURN;
      
   END IF;
   
END;
/

--RAISE_APPLICATION_ERROR (-20001, CUR_PREVCONS.CDPRODUTO || ' ' || V_VDREFOREG);

-- VERSAO --
BEGIN ATUVEROBJ('GERA_ACRES_PEDCOLETA','5.70.XXX'); END;
/