CREATE OR REPLACE FUNCTION QUERY_FILIAL_DFS48000 (P_FILOPELOG        IN VARCHAR2,   /*CDFILIOPELOG*/
                                                  P_DATAENT          IN VARCHAR2,   /*0 Dt Enrega  OU 1 Dt Embarque*/
                                                  P_DTENTREGA        IN DATE,       /*Data*/
                                                  P_CDFILIAL         IN VARCHAR2,   /*Lista filial ou T*/
                                                  P_ROTA             IN VARCHAR2,   /*Lista Rota ou NULL*/
                                                  P_PEDOLEXTR        IN VARCHAR2,   /*S ou N ou T*/
                                                  P_CDLOTENVIOL      IN VARCHAR2,   /*Lista Lote, N?o considerada = 'N' e sem lote = 'T'*/
                                                  P_FILCLIE          IN VARCHAR2,   /*Filial = "L" ou Cliente = "C'*/
                                                  P_SUBGRUPO         IN VARCHAR2,   /*Lista subrgupo ou T*/
                                                  P_VERIFICACNPJ     IN VARCHAR2,   /*NUll ou D ou I*/
                                                  P_NRINSJUREMPR     IN VARCHAR2,   /*N?mero de inscri??o*/
                                                  P_CDCLIENTE        IN VARCHAR2,   /*Lista cliente ou T*/
                                                  P_STATUS           IN VARCHAR2,   /*'0_9_1_2'*/
                                                  P_IDUTILCONSSOLIOL IN VARCHAR2) RETURN QUERY_FILIAL_DFS48000_TABLE PIPELINED
IS
   OUT_RECORD QUERY_FILIAL_DFS48000_TYPE := QUERY_FILIAL_DFS48000_TYPE (NULL, NULL, NULL, NULL,
                                                                        NULL, NULL, NULL, NULL,
                                                                        NULL,NULL, NULL, NULL,
                                                                        NULL, NULL, NULL, NULL,
                                                                        NULL, 0, 0, 0, 0);
   TYPE CURTYPE       IS REF CURSOR;
   C_FILIAL           CURTYPE;
   V_CDFILIAL         VARCHAR2(21);
   V_DSFILIAL         VARCHAR2(50);
   V_CDEMPRESA        VARCHAR2(2);
   V_CDSERIE          VARCHAR2(5);
   V_IDSTATUS         VARCHAR2(1);
   V_NRAUTOFORN       VARCHAR2(10);
   V_USR_NRPEDIDO     VARCHAR2(10);
   V_CDFILDEST        VARCHAR2(4);
   V_CDFILLANC        VARCHAR2(4);
   V_IDSTATUSE        VARCHAR2(1);
   V_IDPGTOFRETE      VARCHAR2(1);
   V_IDTPIJURTRAN     VARCHAR2(1);
   V_NRINSJURTRAN     VARCHAR2(20);
   V_USR_NMROTATRANS  VARCHAR2(50);
   V_CDLOTENVITOL     VARCHAR2(10);
   V_COUNT_IDSTATUSE  FLOAT;
   V_COUNT_IDSTATUS   FLOAT;
   V_COUNT_ITEM       NUMBER;
   V_IDCONMAXITEM     SERIENF.IDCONMAXITEM%TYPE;
   V_NRMAXITNF        SERIENF.NRMAXITNF%TYPE;
   V_CHAVE_FILOPELOG   VARCHAR2(4) := '_';

BEGIN
   IF P_IDUTILCONSSOLIOL = 'S' THEN  /*CONSULTA PEDIDO OL*/
      IF P_FILCLIE = 'L' THEN        /*CONSULTA PEDIDO DE FILIAL*/
         IF P_ROTA IS NULL THEN /*CONSULTA PEDIDO DE FILIAL SEM ROTA*/
            OPEN C_FILIAL FOR
               SELECT CDFILIAL, DSFILIAL, CDEMPRESA, CDSERIE, CASE NVL(IDSTATUS, '0')
                                                                 WHEN '0'
                                                                 THEN CASE TO_CHAR(QTATENDPEDOL)
                                                                         WHEN '-1'
                                                                         THEN NVL(IDSTATUS, '0')
                                                                         ELSE '9'
                                                                      END
                                                                 ELSE NVL(IDSTATUS, '0')
                                                              END IDSTATUS, NRAUTOFORN,
                      USR_NRPEDIDO, CDFILDEST, CDFILLANC, IDSTATUSE, CDLOTENVITOL, COUNT_IDSTATUS,
                      COUNT_IDSTATUSE, IDPGTOFRETE, IDTPIJURTRAN, NRINSJURTRAN, USR_NMROTATRANS,
                      COUNT(CDFILLANC) OVER (PARTITION BY CDFILIAL, NRAUTOFORN) COUNT_ITEM
                 FROM (SELECT P.CDFILDEST CDFILIAL, P.CDFILDEST||' - '||F.NMFILIAL AS DSFILIAL,
                              F.CDEMPRESA,  F.CDSERIE, MAX(I.IDBAIXAOLSAI) AS IDSTATUS, P.NRPEDEXOL as NRAUTOFORN,
                              P.USR_NRPEDIDO, P.CDFILDEST, P.CDFILLANC, MAX(I.IDBAIXENPEOL) AS IDSTATUSE,
                              NVL(I.CDLOTENVITOL, 'T') CDLOTENVITOL, MAX(NVL(QTATENDPEDOL, -1)) QTATENDPEDOL,
                              COUNT(I.IDBAIXAOLSAI) COUNT_IDSTATUS, COUNT(I.IDBAIXENPEOL) COUNT_IDSTATUSE,
                              '' IDPGTOFRETE, '' IDTPIJURTRAN, '' NRINSJURTRAN, '' USR_NMROTATRANS
                         FROM PEDIDOEXOL P, ITEMPEDEXOL I, FILIAL F
                        WHERE P.CDFILIOPELOG = P_FILOPELOG
                          AND P.CDFILDEST = I.CDFILDEST
                          AND P.CDFILLANC = I.CDFILLANC
                          AND DECODE(P_DATAENT, 0, I.DTPROGENPEOL, I.DTPROGEMBARQ) = P_DTENTREGA
                          AND P.CDFILDEST = F.CDFILIAL
                          AND ((INSTR(P_CDFILIAL, F.CDFILIAL) > 0) OR (P_CDFILIAL ='T'))
                          --AND NVL(F.IDFUNCAOFILI, 'F') <> 'O'
                          AND NVL(I.IDBAIXAOLSAI, '1') <> '3'
                          AND P.NRPEDEXOL = I.NRPEDEXOL
                          AND ((P.IDPEDEXOLEXTR = P_PEDOLEXTR) OR (P_PEDOLEXTR = 'T'))
                          AND ((INSTR(P_CDLOTENVIOL, I.CDLOTENVITOL) > 0) OR (NVL(I.CDLOTENVITOL, P_CDLOTENVIOL) = 'T') OR (P_CDLOTENVIOL = 'N'))
                          AND NVL(I.IDITEMAJUSTE, 'N') = 'N'
                          AND P_FILCLIE = 'L'
                          AND (P_SUBGRUPO =  'T' OR EXISTS (SELECT 1
                                                               FROM PRSUBGRPROD PR, PARASUPRI PA
                                                              WHERE PR.CDPRODUTO  = I.CDPRODUTO AND
                                                                    PR.CDGRUPPROD = PA.CDGRUPPROD   AND
                                                                    NVL(F.CDFILCENTCMP, F.CDFILIAL) = PA.CDFILIAL AND
                                                                    PR.CDFILISUBGR = 'P' AND
                                                                    (INSTR(P_SUBGRUPO, PR.CDSUBGRPROD) > 0)))
                          AND CDCLIENTEEXT IS NULL
                          AND ((P_VERIFICACNPJ IS NULL) OR
                               (EXISTS (SELECT 1
                                         FROM FILIAL
                                        WHERE CDFILIAL       = P.CDFILDEST
                                          AND P_VERIFICACNPJ =  CASE NRINSJURFILI
                                                                    WHEN P_NRINSJUREMPR
                                                                    THEN 'I'
                                                                    ELSE 'D'
                                                                 END)))
                        GROUP BY P.CDFILDEST, F.NMFILIAL, F.CDEMPRESA, F.CDSERIE, P.NRPEDEXOL,
                                 P.USR_NRPEDIDO, P.CDFILDEST, P.CDFILLANC, NVL(I.CDLOTENVITOL, 'T'));
         ELSE /*CONSULTA PEDIDO DE FILIAL COM ROTA*/
            OPEN C_FILIAL FOR
               SELECT CDFILIAL, DSFILIAL, CDEMPRESA, CDSERIE, CASE NVL(IDSTATUS, '0')
                                                                 WHEN '0'
                                                                 THEN CASE TO_CHAR(QTATENDPEDOL)
                                                                         WHEN '-1'
                                                                         THEN NVL(IDSTATUS, '0')
                                                                         ELSE '9'
                                                                      END
                                                                 ELSE NVL(IDSTATUS, '0')
                                                              END IDSTATUS, NRAUTOFORN,
                      USR_NRPEDIDO, CDFILDEST, CDFILLANC, IDSTATUSE, CDLOTENVITOL, COUNT_IDSTATUS,
                      COUNT_IDSTATUSE, IDPGTOFRETE, IDTPIJURTRAN, NRINSJURTRAN, USR_NMROTATRANS,
                      COUNT(CDFILLANC) OVER (PARTITION BY CDFILIAL, NRAUTOFORN) COUNT_ITEM
                 FROM (SELECT P.CDFILDEST CDFILIAL, P.CDFILDEST||' - '||F.NMFILIAL AS DSFILIAL,
                              F.CDEMPRESA,  F.CDSERIE, MAX(I.IDBAIXAOLSAI) AS IDSTATUS, P.NRPEDEXOL as NRAUTOFORN,
                              P.USR_NRPEDIDO, P.CDFILDEST, P.CDFILLANC, MAX(I.IDBAIXENPEOL) AS IDSTATUSE,
                              NVL(I.CDLOTENVITOL, 'T') CDLOTENVITOL, MAX(NVL(QTATENDPEDOL, -1)) QTATENDPEDOL,
                              COUNT(I.IDBAIXAOLSAI) COUNT_IDSTATUS, COUNT(I.IDBAIXENPEOL) COUNT_IDSTATUSE,
                              '' IDPGTOFRETE, '' IDTPIJURTRAN, '' NRINSJURTRAN, '' USR_NMROTATRANS
                         FROM PEDIDOEXOL P, ITEMPEDEXOL I, FILIAL F
                        WHERE P.CDFILIOPELOG = P_FILOPELOG
                          AND P.CDFILDEST = I.CDFILDEST
                          AND P.CDFILLANC = I.CDFILLANC
                          AND DECODE(P_DATAENT, 0, I.DTPROGENPEOL, I.DTPROGEMBARQ) = P_DTENTREGA
                          AND P.CDFILDEST = F.CDFILIAL
                          AND ((INSTR(P_CDFILIAL, F.CDFILIAL) > 0) OR (P_CDFILIAL ='T'))
                          --AND NVL(F.IDFUNCAOFILI, 'F') <> 'O'
                          AND NVL(I.IDBAIXAOLSAI, '1') <> '3'
                          AND P.NRPEDEXOL = I.NRPEDEXOL
                          AND EXISTS (SELECT 1
                                        FROM FILIALROTA R
                                       WHERE (INSTR(P_ROTA, '_'||CDROTATRANS||'_') > 0)
                                         AND P.CDFILDEST = R.CDFILIAL)
                          AND ((P.IDPEDEXOLEXTR = P_PEDOLEXTR) OR (P_PEDOLEXTR = 'T'))
                          AND ((INSTR(P_CDLOTENVIOL, I.CDLOTENVITOL) > 0) OR (NVL(I.CDLOTENVITOL, P_CDLOTENVIOL) = 'T') OR (P_CDLOTENVIOL = 'N'))
                          AND NVL(I.IDITEMAJUSTE, 'N') = 'N'
                          AND P_FILCLIE = 'L'
                          AND (P_SUBGRUPO =  'T' OR EXISTS (SELECT 1
                                                               FROM PRSUBGRPROD PR, PARASUPRI PA
                                                              WHERE PR.CDPRODUTO  = I.CDPRODUTO AND
                                                                    PR.CDGRUPPROD = PA.CDGRUPPROD   AND
                                                                    NVL(F.CDFILCENTCMP, F.CDFILIAL) = PA.CDFILIAL AND
                                                                    PR.CDFILISUBGR = 'P' AND
                                                                    (INSTR(P_SUBGRUPO, PR.CDSUBGRPROD) > 0)))
                          AND CDCLIENTEEXT IS NULL
                          AND ((P_VERIFICACNPJ IS NULL) OR
                               (EXISTS (SELECT 1
                                         FROM FILIAL
                                        WHERE CDFILIAL       = P.CDFILDEST
                                          AND P_VERIFICACNPJ =  CASE NRINSJURFILI
                                                                    WHEN P_NRINSJUREMPR
                                                                    THEN 'I'
                                                                    ELSE 'D'
                                                                 END)))
                        GROUP BY P.CDFILDEST, F.NMFILIAL, F.CDEMPRESA, F.CDSERIE, P.NRPEDEXOL,
                                 P.USR_NRPEDIDO, P.CDFILDEST, P.CDFILLANC, NVL(I.CDLOTENVITOL, 'T'));
         END IF;
      ELSE                           /*CONSULTA PEDIDO DE CLIENTE*/
         IF P_ROTA IS NULL THEN /*CONSULTA PEDIDO DE CLIENTE SEM ROTA*/
            OPEN C_FILIAL FOR
               SELECT CDFILIAL, DSFILIAL, CDEMPRESA, CDSERIE, CASE NVL(IDSTATUS, '0')
                                                                 WHEN '0'
                                                                 THEN CASE TO_CHAR(QTATENDPEDOL)
                                                                         WHEN '-1'
                                                                         THEN NVL(IDSTATUS, '0')
                                                                         ELSE '9'
                                                                      END
                                                                 ELSE NVL(IDSTATUS, '0')
                                                              END IDSTATUS, NRAUTOFORN,
                      USR_NRPEDIDO, CDFILDEST, CDFILLANC, IDSTATUSE, CDLOTENVITOL, COUNT_IDSTATUS,
                      COUNT_IDSTATUSE, IDPGTOFRETE, IDTPIJURTRAN, NRINSJURTRAN, USR_NMROTATRANS,
                      COUNT(CDFILLANC) OVER (PARTITION BY CDFILIAL, NRAUTOFORN) COUNT_ITEM
                 FROM (SELECT C.CDCLIENTE CDFILIAL, C.NRINSJURCLIE||' - '||C.NMFANTCLIE AS DSFILIAL,
                              F.CDEMPRESA,  F.CDSERIE, MAX(I.IDBAIXAOLSAI) AS IDSTATUS, COUNT(I.IDBAIXAOLSAI) AS COUNT_IDSTATUS, P.NRPEDEXOL as NRAUTOFORN,
                              P.USR_NRPEDIDO, P.CDFILDEST, P.CDFILLANC, MAX(I.IDBAIXENPEOL) AS IDSTATUSE, COUNT(I.IDBAIXENPEOL) AS COUNT_IDSTATUSE,
                              NVL(I.CDLOTENVITOL, 'T') CDLOTENVITOL, MAX(NVL(QTATENDPEDOL, -1)) QTATENDPEDOL,
                              '' IDPGTOFRETE, '' IDTPIJURTRAN, '' NRINSJURTRAN, '' USR_NMROTATRANS
                         FROM PEDIDOEXOL P, ITEMPEDEXOL I, FILIAL F, CLIENTE C
                        WHERE P.CDFILIOPELOG = P_FILOPELOG
                          AND P.CDFILDEST = I.CDFILDEST
                          AND P.CDFILLANC = I.CDFILLANC
                          AND DECODE(P_DATAENT, 0, I.DTPROGENPEOL, I.DTPROGEMBARQ) = P_DTENTREGA
                          AND P.CDFILDEST = F.CDFILIAL
                          --AND ((INSTR(P_CDFILIAL, F.CDFILIAL) > 0) OR (P_CDFILIAL ='T'))
                          --AND NVL(F.IDFUNCAOFILI, 'F') <> 'O'
                          AND P.NRPEDEXOL = I.NRPEDEXOL
                          AND NVL(I.IDBAIXAOLSAI, '1') <> '3'
                          AND ((P.IDPEDEXOLEXTR = P_PEDOLEXTR) OR (P_PEDOLEXTR = 'T'))
                          AND ((INSTR(P_CDCLIENTE, C.CDCLIENTE) > 0) OR (P_CDCLIENTE ='T'))
                          AND C.CDCLIENTE = P.CDCLIENTEEXT
                          AND ((INSTR(P_CDLOTENVIOL, I.CDLOTENVITOL) > 0) OR (NVL(I.CDLOTENVITOL, P_CDLOTENVIOL) = 'T') OR (P_CDLOTENVIOL = 'N'))
                          AND NVL(I.IDITEMAJUSTE, 'N') = 'N'
                          AND (P_SUBGRUPO =  'T' OR EXISTS (SELECT 1
                                                               FROM PRSUBGRPROD PR, PARASUPRI PA
                                                              WHERE PR.CDPRODUTO  = I.CDPRODUTO AND
                                                                    PR.CDGRUPPROD = PA.CDGRUPPROD   AND
                                                                    NVL(F.CDFILCENTCMP, F.CDFILIAL) = PA.CDFILIAL AND
                                                                    PR.CDFILISUBGR = 'P' AND
                                                                    (INSTR(P_SUBGRUPO, PR.CDSUBGRPROD) > 0)))
                          AND P_FILCLIE = 'C'
                          AND CDCLIENTEEXT IS NOT NULL
                          AND ((P_VERIFICACNPJ IS NULL) OR
                               (P_VERIFICACNPJ =  CASE C.NRINSJURCLIE
                                                      WHEN P_NRINSJUREMPR
                                                      THEN 'I'
                                                      ELSE 'D'
                                                   END))
                        GROUP BY C.CDCLIENTE, C.NRINSJURCLIE, C.NMFANTCLIE, F.CDEMPRESA, F.CDSERIE,
                                 P.NRPEDEXOL, P.USR_NRPEDIDO, P.CDFILDEST, P.CDFILLANC, NVL(I.CDLOTENVITOL, 'T'));
         ELSE /*CONSULTA PEDIDO DE CLIENTE COM ROTA*/
            OPEN C_FILIAL FOR
               SELECT CDFILIAL, DSFILIAL, CDEMPRESA, CDSERIE, CASE NVL(IDSTATUS, '0')
                                                                 WHEN '0'
                                                                 THEN CASE TO_CHAR(QTATENDPEDOL)
                                                                         WHEN '-1'
                                                                         THEN NVL(IDSTATUS, '0')
                                                                         ELSE '9'
                                                                      END
                                                                 ELSE NVL(IDSTATUS, '0')
                                                              END IDSTATUS, NRAUTOFORN,
                      USR_NRPEDIDO, CDFILDEST, CDFILLANC, IDSTATUSE, CDLOTENVITOL, COUNT_IDSTATUS,
                      COUNT_IDSTATUSE, IDPGTOFRETE, IDTPIJURTRAN, NRINSJURTRAN, USR_NMROTATRANS,
                      COUNT(CDFILLANC) OVER (PARTITION BY CDFILIAL, NRAUTOFORN) COUNT_ITEM
                 FROM (SELECT C.CDCLIENTE CDFILIAL, C.NRINSJURCLIE||' - '||C.NMFANTCLIE AS DSFILIAL,
                              F.CDEMPRESA,  F.CDSERIE, MAX(I.IDBAIXAOLSAI) AS IDSTATUS, COUNT(I.IDBAIXAOLSAI) AS COUNT_IDSTATUS, P.NRPEDEXOL as NRAUTOFORN,
                              P.USR_NRPEDIDO, P.CDFILDEST, P.CDFILLANC, MAX(I.IDBAIXENPEOL) AS IDSTATUSE, COUNT(I.IDBAIXENPEOL) AS COUNT_IDSTATUSE,
                              NVL(I.CDLOTENVITOL, 'T') CDLOTENVITOL, MAX(NVL(QTATENDPEDOL, -1)) QTATENDPEDOL,
                              '' IDPGTOFRETE, '' IDTPIJURTRAN, '' NRINSJURTRAN, '' USR_NMROTATRANS
                         FROM PEDIDOEXOL P, ITEMPEDEXOL I, FILIAL F, CLIENTE C
                        WHERE P.CDFILIOPELOG = P_FILOPELOG
                          AND P.CDFILDEST = I.CDFILDEST
                          AND P.CDFILLANC = I.CDFILLANC
                          AND DECODE(P_DATAENT, 0, I.DTPROGENPEOL, I.DTPROGEMBARQ) = P_DTENTREGA
                          AND P.CDFILDEST = F.CDFILIAL
                          AND P.NRPEDEXOL = I.NRPEDEXOL
                          AND EXISTS (SELECT 1
                                        FROM FILIALROTA R
                                       WHERE (INSTR(P_ROTA, '_'||CDROTATRANS||'_') > 0)
                                         AND P.CDFILDEST = R.CDFILIAL)
                          AND NVL(I.IDBAIXAOLSAI, '1') <> '3'
                          AND ((P.IDPEDEXOLEXTR = P_PEDOLEXTR) OR (P_PEDOLEXTR = 'T'))
                          AND ((INSTR(P_CDCLIENTE, C.CDCLIENTE) > 0) OR (P_CDCLIENTE ='T'))
                          AND C.CDCLIENTE = P.CDCLIENTEEXT
                          AND ((INSTR(P_CDLOTENVIOL, I.CDLOTENVITOL) > 0) OR (NVL(I.CDLOTENVITOL, P_CDLOTENVIOL) = 'T') OR (P_CDLOTENVIOL = 'N'))
                          AND NVL(I.IDITEMAJUSTE, 'N') = 'N'
                          AND (P_SUBGRUPO =  'T' OR EXISTS (SELECT 1
                                                               FROM PRSUBGRPROD PR, PARASUPRI PA
                                                              WHERE PR.CDPRODUTO  = I.CDPRODUTO AND
                                                                    PR.CDGRUPPROD = PA.CDGRUPPROD   AND
                                                                    NVL(F.CDFILCENTCMP, F.CDFILIAL) = PA.CDFILIAL AND
                                                                    PR.CDFILISUBGR = 'P' AND
                                                                    (INSTR(P_SUBGRUPO, PR.CDSUBGRPROD) > 0)))
                          AND P_FILCLIE = 'C'
                          AND CDCLIENTEEXT IS NOT NULL
                          AND ((P_VERIFICACNPJ IS NULL) OR
                               (P_VERIFICACNPJ =  CASE C.NRINSJURCLIE
                                                      WHEN P_NRINSJUREMPR
                                                      THEN 'I'
                                                      ELSE 'D'
                                                   END))
                        GROUP BY C.CDCLIENTE, C.NRINSJURCLIE, C.NMFANTCLIE, F.CDEMPRESA, F.CDSERIE,
                                 P.NRPEDEXOL, P.USR_NRPEDIDO, P.CDFILDEST, P.CDFILLANC, NVL(I.CDLOTENVITOL, 'T'));
         END IF;
      END IF;
   ELSE                            /*CONSULTA AF DE FILIAL*/
      IF P_ROTA IS NULL THEN  /*CONSULTA AF DE FILIAL SEM ROTA*/
         OPEN C_FILIAL FOR
            SELECT CDFILIAL, DSFILIAL, CDEMPRESA, CDSERIE, CASE NVL(IDSTATUS, '0')
                                                              WHEN '0'
                                                              THEN CASE TO_CHAR(QTATENDPEDOL)
                                                                      WHEN '-1'
                                                                      THEN NVL(IDSTATUS, '0')
                                                                      ELSE '9'
                                                                   END
                                                              ELSE NVL(IDSTATUS, '0')
                                                           END IDSTATUS, NRAUTOFORN,
                   USR_NRPEDIDO, CDFILDEST, CDFILLANC, IDSTATUSE, CDLOTENVITOL, COUNT_IDSTATUS,
                   COUNT_IDSTATUSE, IDPGTOFRETE, IDTPIJURTRAN, NRINSJURTRAN, USR_NMROTATRANS,
                   COUNT(CDFILLANC) OVER (PARTITION BY CDFILIAL, NRAUTOFORN) COUNT_ITEM
              FROM (SELECT F.CDFILIAL, F.CDFILIAL||' - '||F.NMFILIAL AS DSFILIAL, MAX(NVL(QTATENDPEDOL, -1)) QTATENDPEDOL,
                           F.CDEMPRESA,  F.CDSERIE, MIN(I.IDBAIXITAFS) AS IDSTATUS,
                           I.NRAUTOFORN, I.CDFILDEST, I.CDFILLANC, MAX(I.IDBAIXITMAF) AS IDSTATUSE,
                           N.IDPGTOFRETE, N.IDTPIJURTRAN, N.NRINSJURTRAN, N.USR_NMROTATRANS, 'T' CDLOTENVITOL,
                           N.USR_NRPEDIDO, COUNT(I.IDBAIXITAFS) COUNT_IDSTATUS, COUNT(I.IDBAIXITMAF) COUNT_IDSTATUSE
                        FROM
                           ITEMAUTF I, FILIAL F, AUTOFORNE N
                        WHERE
                           N.CDFILIOPELOG = P_FILOPELOG         AND
                           N.CDFILDEST    = I.CDFILDEST          AND
                           I.DTPROGENTAF  = P_DTENTREGA         AND
                           N.CDFILDEST    = F.CDFILIAL           AND
                           I.CDFILDEST    = N.CDFILDEST          AND
                           I.CDFILLANC    = N.CDFILLANC          AND
                           I.NRAUTOFORN   = N.NRAUTOFORN         AND
                           NVL(N.IDBAIXAAF, 1) NOT IN ('4', '5', '6') AND
                           NVL(F.IDFUNCAOFILI, 'F') <> 'O'       AND
                           ((INSTR(P_CDFILIAL, F.CDFILIAL) > 0) OR (P_CDFILIAL ='T')) AND
                           NVL(I.IDTPGERARUP, 'S') <> 'T'        AND
                           NVL(I.IDITAFMIGRADA, 'N') = 'N'       AND
                           ((N.IDAFOPERLOG  = '2')                OR
                           EXISTS (SELECT 1
                                     FROM NFRELACAFSAI
                                    WHERE NFRELACAFSAI.CDFILDEST  = N.CDFILDEST
                                      AND NFRELACAFSAI.CDFILLANC  = N.CDFILLANC
                                      AND NFRELACAFSAI.NRAUTOFORN = N.NRAUTOFORN))
                        GROUP BY
                           F.CDFILIAL, F.NMFILIAL, F.CDEMPRESA, F.CDSERIE, I.NRAUTOFORN, I.CDFILDEST, I.CDFILLANC,
                           N.IDPGTOFRETE, N.IDTPIJURTRAN, N.NRINSJURTRAN, N.USR_NMROTATRANS, N.USR_NRPEDIDO);
      ELSE  /*CONSULTA AF DE FILIAL COM ROTA*/
         OPEN C_FILIAL FOR
            SELECT CDFILIAL, DSFILIAL, CDEMPRESA, CDSERIE, CASE NVL(IDSTATUS, '0')
                                                              WHEN '0'
                                                              THEN CASE TO_CHAR(QTATENDPEDOL)
                                                                      WHEN '-1'
                                                                      THEN NVL(IDSTATUS, '0')
                                                                      ELSE '9'
                                                                   END
                                                              ELSE NVL(IDSTATUS, '0')
                                                           END IDSTATUS, NRAUTOFORN,
                   USR_NRPEDIDO, CDFILDEST, CDFILLANC, IDSTATUSE, CDLOTENVITOL, COUNT_IDSTATUS,
                   COUNT_IDSTATUSE, IDPGTOFRETE, IDTPIJURTRAN, NRINSJURTRAN, USR_NMROTATRANS,
                   COUNT(CDFILLANC) OVER (PARTITION BY CDFILIAL, NRAUTOFORN) COUNT_ITEM
              FROM (SELECT F.CDFILIAL, F.CDFILIAL||' - '||F.NMFILIAL AS DSFILIAL, MAX(NVL(QTATENDPEDOL, -1)) QTATENDPEDOL,
                           F.CDEMPRESA,  F.CDSERIE, MIN(I.IDBAIXITAFS) AS IDSTATUS,
                           I.NRAUTOFORN, I.CDFILDEST, I.CDFILLANC, MAX(I.IDBAIXITMAF) AS IDSTATUSE,
                           N.IDPGTOFRETE, N.IDTPIJURTRAN, N.NRINSJURTRAN, N.USR_NMROTATRANS, 'T' CDLOTENVITOL,
                           N.USR_NRPEDIDO, COUNT(I.IDBAIXITAFS) COUNT_IDSTATUS, COUNT(I.IDBAIXITMAF) COUNT_IDSTATUSE
                        FROM
                           ITEMAUTF I, FILIAL F, AUTOFORNE N
                        WHERE
                           N.CDFILIOPELOG = P_FILOPELOG         AND
                           N.CDFILDEST    = I.CDFILDEST          AND
                           I.DTPROGENTAF  = P_DTENTREGA         AND
                           N.CDFILDEST    = F.CDFILIAL           AND
                           I.CDFILDEST    = N.CDFILDEST          AND
                           I.CDFILLANC    = N.CDFILLANC          AND
                           I.NRAUTOFORN   = N.NRAUTOFORN         AND
                           NVL(N.IDBAIXAAF, 1) NOT IN ('4', '5', '6') AND
                           NVL(F.IDFUNCAOFILI, 'F') <> 'O'       AND
                           ((INSTR(P_CDFILIAL, F.CDFILIAL) > 0) OR (P_CDFILIAL ='T')) AND
                           (EXISTS(SELECT 1
                                     FROM FILIALROTA
                                    WHERE FILIALROTA.CDFILIROTA = I.CDFILDEST
                                      AND INSTR(P_ROTA, '_'||CDROTATRANS||'_') > 0)) AND
                           NVL(I.IDTPGERARUP, 'S') <> 'T'        AND
                           NVL(I.IDITAFMIGRADA, 'N') = 'N'       AND
                           ((N.IDAFOPERLOG  = '2')                OR
                           EXISTS (SELECT 1
                                     FROM NFRELACAFSAI
                                    WHERE NFRELACAFSAI.CDFILDEST  = N.CDFILDEST
                                      AND NFRELACAFSAI.CDFILLANC  = N.CDFILLANC
                                      AND NFRELACAFSAI.NRAUTOFORN = N.NRAUTOFORN))
                        GROUP BY
                           F.CDFILIAL, F.NMFILIAL, F.CDEMPRESA, F.CDSERIE, I.NRAUTOFORN, I.CDFILDEST, I.CDFILLANC,
                           N.IDPGTOFRETE, N.IDTPIJURTRAN, N.NRINSJURTRAN, N.USR_NMROTATRANS, N.USR_NRPEDIDO);
      END IF;
   END IF;
   LOOP
   FETCH C_FILIAL INTO V_CDFILIAL, V_DSFILIAL, V_CDEMPRESA, V_CDSERIE, V_IDSTATUS, V_NRAUTOFORN,
                       V_USR_NRPEDIDO, V_CDFILDEST, V_CDFILLANC, V_IDSTATUSE,V_CDLOTENVITOL,
                       V_COUNT_IDSTATUS, V_COUNT_IDSTATUSE,V_IDPGTOFRETE, V_IDTPIJURTRAN,
                       V_NRINSJURTRAN, V_USR_NMROTATRANS, V_COUNT_ITEM;
   EXIT WHEN C_FILIAL%NOTFOUND;
      IF INSTR(P_STATUS, V_IDSTATUS) > 0 THEN
         IF NVL(V_CDSERIE, 'W') <> 'W' THEN
            IF V_CHAVE_FILOPELOG <> P_FILOPELOG THEN
               V_CHAVE_FILOPELOG := P_FILOPELOG;
               SELECT NVL(MAX(IDCONMAXITEM), 'N'), NVL(MAX(NRMAXITNF), 0)
                 INTO V_IDCONMAXITEM, V_NRMAXITNF
                 FROM SERIENF S, FILIAL F
                WHERE F.CDFILIAL   = V_CDFILIAL
                  AND F.CDEMPRESA  = S.CDEMPRESA
                  AND F.CDINSCESTA = S.CDINSCESTA
                  AND F.CDINSCMUNI = S.CDINSCMUNI
                  AND S.CDSERIE    = F.CDSERIE;
            END IF;
         END IF;
         OUT_RECORD.IDCHECK          := 'U';
         OUT_RECORD.CDFILIAL         := V_CDFILIAL;
         OUT_RECORD.DSFILIAL         := V_DSFILIAL;
         OUT_RECORD.CDEMPRESA        := V_CDEMPRESA;
         OUT_RECORD.CDSERIE          := V_CDSERIE;
         OUT_RECORD.IDSTATUS         := V_IDSTATUS ;
         OUT_RECORD.NRAUTOFORN       := V_NRAUTOFORN;
         OUT_RECORD.USR_NRPEDIDO     := V_USR_NRPEDIDO;
         OUT_RECORD.CDFILDEST        := V_CDFILDEST;
         OUT_RECORD.CDFILLANC        := V_CDFILLANC;
         OUT_RECORD.IDSTATUSE        := V_IDSTATUSE;
         OUT_RECORD.IDPGTOFRETE      := V_IDPGTOFRETE;
         OUT_RECORD.IDTPIJURTRAN     := V_IDTPIJURTRAN;
         OUT_RECORD.NRINSJURTRAN     := V_NRINSJURTRAN;
         OUT_RECORD.USR_NMROTATRANS  := V_USR_NMROTATRANS;
         OUT_RECORD.CDLOTENVITOL     := V_CDLOTENVITOL;
         OUT_RECORD.COUNT_IDSTATUSE  := V_COUNT_IDSTATUSE;
         OUT_RECORD.COUNT_IDSTATUS   := V_COUNT_IDSTATUS;
         OUT_RECORD.COUNT_ITEM       := V_COUNT_ITEM;
         OUT_RECORD.IDCONMAXITEM     := V_IDCONMAXITEM;
         OUT_RECORD.NRMAXITNF        := V_NRMAXITNF;
         PIPE ROW (OUT_RECORD);
      END IF;   
   END LOOP;
   CLOSE C_FILIAL;
END;
/