DROP TYPE TRACKING_AF_TABLE
/

DROP TYPE TRACKING_AF_TYPE
/

CREATE TYPE TRACKING_AF_TYPE AS
OBJECT(CDFILDEST           VARCHAR2(4),
       NMFILIAL            VARCHAR2(50),
       CDFILLANC           VARCHAR2(4),
       CDFILCENTCMP        VARCHAR2(4),
       NRAUTOFORN          VARCHAR2(10),
       CDOPERADOR          VARCHAR2(12),
       NMOPERADOR          VARCHAR2(50),
       DTAUTOFORN          DATE,
       VRTOTALAF           FLOAT,
       DTPROGENTAF         DATE,
       IDBAIXAAF           VARCHAR2(1),
       IDTIPOLANCAF        VARCHAR2(1), 
       IDAFEXPIRADA        VARCHAR2(1),
       IDITAFMIGRADA       VARCHAR2(1),
       IDAUTOFORNEX        VARCHAR2(1),
       IDSITUATENDIMENTOAF VARCHAR2(1),
       TXJUSTIFICRECAF     VARCHAR2(2000),
       DTCONFATENDIMENTOAF DATE,
       IDCONFRECEBIMENTO   VARCHAR2(1),
       DTCONFRECEBIMENTO   DATE,
       CDOPERCONFAF        VARCHAR2(12),
       NMOPERCONFAF        VARCHAR2(40),
       NMPAIS              VARCHAR2(30),
       SGESTADO            VARCHAR2(2),
       NMESTADO            VARCHAR2(30),
       NMREGIAOGEO         VARCHAR2(30),
       NMMUNICIPIO         VARCHAR2(30),
       CDFORNECED          VARCHAR2(21),
       NRINSJURFORN        VARCHAR2(20),
       NMRAZSOCFORN        VARCHAR2(100),
       NMFANTFORN          VARCHAR2(250),
       CDFILLANNF          VARCHAR2(4),
       NRLANCTONF          VARCHAR2(10),
       NRNOTAFISC          VARCHAR(20),
       DTEMISSAO           DATE, 
       DTENTRSAID          DATE,
       VRNOTAFISC          FLOAT,
       NRACESSONFE         VARCHAR2(50),
       VRPBRUTOVOLU        FLOAT,
       VRPLIQUVOLU         FLOAT,
       IDTPNOTAFISCAL      NUMBER,
       NMOPERLANCNF        VARCHAR2(100),
       NRORG               NUMBER)
/

CREATE TYPE TRACKING_AF_TABLE AS TABLE OF TRACKING_AF_TYPE
/

create or replace FUNCTION TRACKING_AF(P_CDFILCENTCMP   IN VARCHAR2,
                                       P_DTINIVALANT    IN DATE,
                                       P_DTFINVALANT    IN DATE,
                                       P_NRORG          IN VARCHAR2 DEFAULT '1'
                                       ) RETURN TRACKING_AF_TABLE PIPELINED
IS

   TYPE CURTYPE               IS REF CURSOR;
   C_AF                       CURTYPE;
   C_NOTAFISCAL               CURTYPE;
   C_UNIDADE                  CURTYPE;
   C_COMPRADOR                CURTYPE;

   V_UNIDADE                  VARCHAR2(4);
   V_COMPRADOR                VARCHAR2(12);

   V_CDFILDEST                VARCHAR2(4);
   V_NMFILIAL                 VARCHAR2(50);
   V_CDFILLANC                VARCHAR2(4);
   V_CDFILCENTCMP             VARCHAR2(4);
   V_NRAUTOFORN               VARCHAR2(10);
   V_CDOPERADOR               VARCHAR2(12);
   V_NMOPERADOR               VARCHAR2(50);
   V_CDFORNECED               VARCHAR2(21);
   V_DTAUTOFORN               DATE;
   V_DTPROGENTAF              DATE;
   V_IDBAIXAAF                VARCHAR2(1);
   V_IDAFEXPIRADA             VARCHAR2(1); 
   V_IDTIPOLANCAF             VARCHAR2(1);
   V_IDITAFMIGRADA            VARCHAR2(1);
   V_IDAUTOFORNEX             VARCHAR2(1);
   V_IDSITUATENDIMENTOAF      VARCHAR2(1);
   V_TXJUSTIFICRECAF          VARCHAR2(2000);
   V_DTCONFATENDIMENTOAF      DATE;
   V_IDCONFRECEBIMENTO        VARCHAR2(1);
   V_DTCONFRECEBIMENTO        DATE;
   V_NRORG                    NUMBER;
   V_VRTOTALAF                FLOAT;

   V_NMPAIS                   VARCHAR2(30);
   V_SGESTADO                 VARCHAR2(2);
   V_NMESTADO                 VARCHAR2(30);
   V_NMREGIAOGEO              VARCHAR2(30);
   V_NMMUNICIPIO              VARCHAR2(30);

   V_NRINSJURFORN             VARCHAR2(20);
   V_NMRAZSOCFORN             VARCHAR2(100);
   V_NMFANTFORN               VARCHAR2(40);

   V_CDFILLANNF               VARCHAR2(4);
   V_NRLANCTONF               VARCHAR2(10);
   V_NRNOTAFISC               VARCHAR(20);
   V_DTEMISSAO                DATE; 
   V_DTENTRSAID               DATE;
   V_VRNOTAFISC               FLOAT;
   V_NRACESSONFE              VARCHAR2(50);
   V_VRPBRUTOVOLU             FLOAT;
   V_VRPLIQUVOLU              FLOAT;
   V_IDTPNOTAFISCAL           INTEGER;

   V_CDOPERCONFAF             VARCHAR2(12);
   V_NMOPERCONFAF             VARCHAR2(40);
   
   V_NMOPERLANCNF             VARCHAR2(100);          

  OUT_RECORD TRACKING_AF_TYPE := TRACKING_AF_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, NULL, NULL, NULL,
                                                   NULL, NULL, NULL, NULL, NULL, NULL,
                                                   NULL, NULL, NULL, NULL, NULL, NULL,
                                                   NULL);
BEGIN
   OPEN C_UNIDADE FOR
      SELECT FILIAL.CDFILIAL
        FROM FILIAL, PARCMPSUP
       WHERE NVL(FILIAL.CDFILCENTCMP, FILIAL.CDFILIAL) = P_CDFILCENTCMP
         AND FILIAL.IDCNTRRESC = 'N'
         AND FILIAL.CDFILIAL = PARCMPSUP.CDFILIAL
         AND PARCMPSUP.IDUTLCONFRECAF = 'S'
         AND CDREGIAO IS NOT NULL
         AND FILIAL.NRORG = P_NRORG
         AND FILIAL.NRORG = PARCMPSUP.NRORG
       ORDER BY FILIAL.CDFILIAL;
   LOOP
      FETCH C_UNIDADE INTO V_UNIDADE;
      EXIT WHEN C_UNIDADE%NOTFOUND;

      OPEN C_COMPRADOR FOR
         SELECT DISTINCT CDOPERADOR
           FROM COLETAPR
          WHERE CDFILIAL = P_CDFILCENTCMP
            AND NRORG = P_NRORG;
      LOOP
         FETCH C_COMPRADOR INTO V_COMPRADOR;
         EXIT WHEN C_COMPRADOR%NOTFOUND;
         
         OPEN C_AF FOR
            SELECT AUTOFORNE.CDFILDEST, AUTOFORNE.CDFILLANC, AUTOFORNE.NRAUTOFORN, 
                   AUTOFORNE.CDOPERADOR, AUTOFORNE.CDFORNECED, AUTOFORNE.DTAUTOFORN,
                   (SELECT MAX(DTPROGENTAF)
                      FROM ITEMAUTF
                     WHERE ITEMAUTF.CDFILDEST  = AUTOFORNE.CDFILDEST 
                       AND ITEMAUTF.CDFILLANC  = AUTOFORNE.CDFILLANC
                       AND ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN
                       AND ITEMAUTF.NRORG      = AUTOFORNE.NRORG) AS DTPROGENTAF,
                   AUTOFORNE.IDBAIXAAF, 
                   AUTOFORNE.IDSITUATENDIMENTOAF, 
                   AUTOFORNE.TXJUSTIFICRECAF,
                   AUTOFORNE.DTCONFATENDIMENTOAF, 
                   NVL(AUTOFORNE.IDCONFRECEBIMENTO,'X') AS IDCONFRECEBIMENTO, 
                   AUTOFORNE.DTCONFRECEBIMENTO,
                   AUTOFORNE.NRORG, 
                   AUTOFORNE.VRTOTALAF, 
                   AUTOFORNE.CDOPERCONFAF, 
                   AUTOFORNE.IDTIPOLANCAF, 
                   AUTOFORNE.IDAFEXPIRADA, 
                   AUTOFORNE.IDAUTOFORNEX
              FROM AUTOFORNE
             WHERE AUTOFORNE.CDFILDEST  = V_UNIDADE
               AND AUTOFORNE.CDOPERADOR = V_COMPRADOR
               AND AUTOFORNE.NRORG = P_NRORG  
               AND EXISTS (SELECT 1
                             FROM ITEMAUTF
                            WHERE ITEMAUTF.CDFILDEST  = AUTOFORNE.CDFILDEST
                              AND ITEMAUTF.CDFILLANC  = AUTOFORNE.CDFILLANC
                              AND ITEMAUTF.NRAUTOFORN = AUTOFORNE.NRAUTOFORN
                              AND ITEMAUTF.NRORG      = AUTOFORNE.NRORG
                              AND ITEMAUTF.IDITAFMIGRADA = 'N' 
                              AND NVL(ITEMAUTF.IDBAIXITMAF, '1') IN ('1', '2')
                              AND (ITEMAUTF.DTPROGENTAF) BETWEEN (P_DTINIVALANT) AND (P_DTFINVALANT))
              ORDER BY  AUTOFORNE.CDFILDEST;
         LOOP
            FETCH C_AF INTO V_CDFILDEST,         V_CDFILLANC,           V_NRAUTOFORN,
                            V_CDOPERADOR,        V_CDFORNECED,          V_DTAUTOFORN, 
                            V_DTPROGENTAF,       V_IDBAIXAAF,           V_IDSITUATENDIMENTOAF, 
                            V_TXJUSTIFICRECAF,   V_DTCONFATENDIMENTOAF, V_IDCONFRECEBIMENTO, 
                            V_DTCONFRECEBIMENTO, V_NRORG,               V_VRTOTALAF, 
                            V_CDOPERCONFAF,      V_IDTIPOLANCAF,        V_IDAFEXPIRADA, 
                            V_IDAUTOFORNEX;
             EXIT WHEN C_AF%NOTFOUND;
             OUT_RECORD.CDFILDEST           := V_CDFILDEST;
             OUT_RECORD.CDFILLANC           := V_CDFILLANC;
             OUT_RECORD.NRAUTOFORN          := V_NRAUTOFORN;
             OUT_RECORD.CDOPERADOR          := V_CDOPERADOR;
             OUT_RECORD.CDFORNECED          := V_CDFORNECED;
             OUT_RECORD.DTAUTOFORN          := V_DTAUTOFORN;
             OUT_RECORD.DTPROGENTAF         := V_DTPROGENTAF;
             OUT_RECORD.IDBAIXAAF           := V_IDBAIXAAF;
             OUT_RECORD.IDSITUATENDIMENTOAF := V_IDSITUATENDIMENTOAF;
             OUT_RECORD.TXJUSTIFICRECAF     := V_TXJUSTIFICRECAF;
             OUT_RECORD.DTCONFATENDIMENTOAF := V_DTCONFATENDIMENTOAF;
             OUT_RECORD.IDCONFRECEBIMENTO   := V_IDCONFRECEBIMENTO;
             OUT_RECORD.DTCONFRECEBIMENTO   := V_DTCONFRECEBIMENTO;
             OUT_RECORD.NRORG               := V_NRORG;
             OUT_RECORD.VRTOTALAF           := V_VRTOTALAF;
             OUT_RECORD.IDAFEXPIRADA        := V_IDAFEXPIRADA;
             OUT_RECORD.IDTIPOLANCAF        := V_IDTIPOLANCAF;
             OUT_RECORD.IDAUTOFORNEX        := V_IDAUTOFORNEX;
             /*Verifica se a AF passou por migração*/
             BEGIN
                SELECT NVL(MAX(ITEMAUTF.IDITAFMIGRADA), 'N')
                  INTO V_IDITAFMIGRADA
                  FROM ITEMAUTF
                 WHERE CDFILDEST     = V_CDFILDEST
                   AND CDFILLANC     = V_CDFILLANC
                   AND NRAUTOFORN    = V_NRAUTOFORN
                   AND IDITAFMIGRADA = 'S'
                   AND NRORG         = V_NRORG;
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_IDITAFMIGRADA := 'N';
             END;
             OUT_RECORD.IDITAFMIGRADA := V_IDITAFMIGRADA;
             /*Nome da Unidade*/
             BEGIN
                SELECT NMFILIAL, NVL(CDFILCENTCMP, CDFILIAL)
                  INTO V_NMFILIAL, V_CDFILCENTCMP
                  FROM FILIAL
                 WHERE CDFILIAL = V_CDFILDEST
                   AND NRORG    = V_NRORG;
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_NMFILIAL     := NULL;
                   V_CDFILCENTCMP := NULL;
             END;
             OUT_RECORD.NMFILIAL     := V_NMFILIAL;
             OUT_RECORD.CDFILCENTCMP := V_CDFILCENTCMP;
             /*Nome do Comprador*/
             BEGIN
                SELECT NMOPERADOR
                  INTO V_NMOPERADOR
                  FROM OPERADOR
                 WHERE CDOPERADOR = V_CDOPERADOR
                   AND NRORG      = V_NRORG;
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_NMOPERADOR := NULL;
             END;
             OUT_RECORD.NMOPERADOR := V_NMOPERADOR;
             /*ENDEREÃ‡O DA UNIDADE*/
             BEGIN
                SELECT PAIS.NMPAIS, ENDEFILI.SGESTADO, ESTADO.NMESTADO, ESTADO.NMREGIAOGEO, MUNICIPIO.NMMUNICIPIO
                  INTO V_NMPAIS,    V_SGESTADO,        V_NMESTADO,      V_NMREGIAOGEO,      V_NMMUNICIPIO 
                  FROM ENDEFILI, PAIS, ESTADO, MUNICIPIO
                 WHERE ENDEFILI.CDFILIAL = V_CDFILDEST
                   AND ENDEFILI.NRORG    = V_NRORG
                   AND ENDEFILI.IDTPENDEFILI = 'P'
                   AND ENDEFILI.CDPAIS   = PAIS.CDPAIS
                   AND ENDEFILI.NRORG    = PAIS.NRORG
                   AND ENDEFILI.SGESTADO = ESTADO.SGESTADO
                   AND ENDEFILI.NRORG    = ESTADO.NRORG
                   AND ENDEFILI.CDMUNICIPIO = MUNICIPIO.CDMUNICIPIO
                   AND ENDEFILI.NRORG       = MUNICIPIO.NRORG
                   AND PAIS.CDPAIS = ESTADO.CDPAIS
                   AND PAIS.CDPAIS = MUNICIPIO.CDPAIS
                   AND ESTADO.SGESTADO = MUNICIPIO.SGESTADO;
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_NMPAIS      := NULL; 
                   V_SGESTADO    := NULL;
                   V_NMESTADO    := NULL; 
                   V_NMREGIAOGEO := NULL; 
                   V_NMMUNICIPIO := NULL;
             END;
             OUT_RECORD.NMPAIS              := V_NMPAIS;
             OUT_RECORD.SGESTADO            := V_SGESTADO;
             OUT_RECORD.NMESTADO            := V_NMESTADO;
             OUT_RECORD.NMREGIAOGEO         := V_NMREGIAOGEO;
             OUT_RECORD.NMMUNICIPIO         := V_NMMUNICIPIO;
             /*DADOS DO FORNECEDOR*/
             BEGIN
                SELECT FORMATMASK(IDTPIJURFORN, NRINSJURFORN) AS NRINSJURFORN, NMRAZSOCFORN, NMFANTFORN
                  INTO V_NRINSJURFORN, V_NMRAZSOCFORN, V_NMFANTFORN
                  FROM FORNECEDOR
                 WHERE CDFORNECED = V_CDFORNECED
                   AND NRORG      = V_NRORG;
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_NRINSJURFORN := NULL; 
                   V_NMRAZSOCFORN := NULL;
                   V_NMFANTFORN   := NULL; 
             END;
             OUT_RECORD.NRINSJURFORN        := V_NRINSJURFORN;
             OUT_RECORD.NMRAZSOCFORN        := V_NMRAZSOCFORN;
             OUT_RECORD.NMFANTFORN          := V_NMFANTFORN;
             /*DADOS DO OPERADOR QUE CONFIRMOU O RECEBIMENTO*/
             BEGIN
                SELECT NMOPERADOR
                  INTO V_NMOPERCONFAF
                  FROM OPERADOR
                 WHERE CDOPERADOR = V_CDOPERCONFAF
                   AND NRORG      = V_NRORG;
             EXCEPTION
                WHEN NO_DATA_FOUND THEN
                   V_NMOPERCONFAF := NULL; 
             END;
             OUT_RECORD.CDOPERCONFAF        := V_CDOPERCONFAF;
             OUT_RECORD.NMOPERCONFAF        := V_NMOPERCONFAF;
             /*DADOS DA NF FISCAL*/
             V_CDFILLANNF     := NULL;
             V_NRLANCTONF     := NULL;
             V_NRNOTAFISC     := NULL;
             V_DTEMISSAO      := NULL;
             V_DTENTRSAID     := NULL;
             V_VRNOTAFISC     := NULL;
             V_NRACESSONFE    := NULL;
             V_VRPBRUTOVOLU   := NULL;
             V_VRPLIQUVOLU    := NULL;
             V_IDTPNOTAFISCAL := NULL;
             OPEN C_NOTAFISCAL FOR
                        SELECT NFRELACAF.CDFILIAL, NFRELACAF.NRLANCTONF, NOTAFISCAL.NRNOTAFISC, NOTAFISCAL.DTEMISSAO, 
                               NOTAFISCAL.DTENTRSAID, NOTAFISCAL.VRNOTAFISC, NOTAFISCAL.NRACESSONFE,
                               NOTAFISCAL.VRPBRUTOVOLU, NOTAFISCAL.VRPLIQUVOLU, 1 AS IDTPNOTAFISCAL
                          FROM NFRELACAF, NOTAFISCAL
                         WHERE NFRELACAF.CDFILDEST  = V_CDFILDEST
                           AND NFRELACAF.CDFILLANC  = V_CDFILLANC
                           AND NFRELACAF.NRAUTOFORN = V_NRAUTOFORN
                           AND NFRELACAF.NRORG      = V_NRORG
                           AND NFRELACAF.CDFILIAL   = NOTAFISCAL.CDFILIAL
                           AND NFRELACAF.NRLANCTONF = NOTAFISCAL.NRLANCTONF
                           AND NFRELACAF.NRORG      = NOTAFISCAL.NRORG;
             LOOP
                FETCH C_NOTAFISCAL INTO V_CDFILLANNF, V_NRLANCTONF, V_NRNOTAFISC, V_DTEMISSAO,
                                        V_DTENTRSAID, V_VRNOTAFISC, V_NRACESSONFE, V_VRPBRUTOVOLU,
                                        V_VRPLIQUVOLU, V_IDTPNOTAFISCAL;
                EXIT WHEN C_NOTAFISCAL%NOTFOUND;
             END LOOP;
             IF V_IDTPNOTAFISCAL IS NULL THEN
                V_CDFILLANNF     := NULL;
                V_NRLANCTONF     := NULL;
                V_NRNOTAFISC     := NULL;
                V_DTEMISSAO      := NULL;
                V_DTENTRSAID     := NULL;
                V_VRNOTAFISC     := NULL;
                V_NRACESSONFE    := NULL;
                V_VRPBRUTOVOLU   := NULL;
                V_VRPLIQUVOLU    := NULL;
                V_IDTPNOTAFISCAL := NULL;
                --DADOS DA NF FISCAL
                OPEN C_NOTAFISCAL FOR
                           SELECT N.CDFILIAL, N.NRLANCTONF, N.NRNOTAFISC, N.DTEMISSAO, N.DTENTRSAID, N.VRNOTAFISC, N.NRACESSONFE, 0, 0, 2 AS IDTPNOTAFISCAL
                             FROM INTG_NOTAFISCAL N, INTG_ITEMPRODUTO I
                            WHERE N.NRACESSONFE = I.NRACESSONFE
                              AND N.CDFILIRECE  = V_CDFILDEST
                              AND I.NRAUTOFORN  = V_NRAUTOFORN
                              AND N.NRORG       = V_NRORG
                              AND N.NRORG       = I.NRORG;
                LOOP
                   FETCH C_NOTAFISCAL INTO V_CDFILLANNF, V_NRLANCTONF, V_NRNOTAFISC, V_DTEMISSAO,
                                           V_DTENTRSAID, V_VRNOTAFISC, V_NRACESSONFE, V_VRPBRUTOVOLU,
                                           V_VRPLIQUVOLU, V_IDTPNOTAFISCAL;
                   EXIT WHEN C_NOTAFISCAL%NOTFOUND;
                END LOOP;
             END IF;
             /*verifica log de lançamento da NF*/
             IF V_IDTPNOTAFISCAL = 1 THEN
                 BEGIN
                    SELECT (OPERADOR.CDOPERADOR||' - '||OPERADOR.NMOPERADOR) AS NMOPERLANCNF
                      INTO V_NMOPERLANCNF
                      FROM LOGOPERA, OPERADOR
                     WHERE LOGOPERA.NMTABELA    = 'NOTAFISCAL'
                       AND LOGOPERA.CDPESQITEM  = V_CDFILLANNF||V_NRLANCTONF
                       AND LOGOPERA.IDTIPOOPER  = 1
                       AND LOGOPERA.CDORESPOPER = OPERADOR.CDOPERADOR
                       AND LOGOPERA.NRORG       = V_NRORG
                       AND LOGOPERA.NRORG       = OPERADOR.NRORG;
                 EXCEPTION
                    WHEN NO_DATA_FOUND THEN
                       V_NMOPERLANCNF := NULL; 
                 END;
             END IF; 
             IF V_IDTPNOTAFISCAL IS NULL THEN
                V_CDFILLANNF     := NULL;
                V_NRLANCTONF     := NULL;
                V_NRNOTAFISC     := NULL;
                V_DTEMISSAO      := NULL;
                V_DTENTRSAID     := NULL;
                V_VRNOTAFISC     := NULL;
                V_NRACESSONFE    := NULL;
                V_VRPBRUTOVOLU   := NULL;
                V_VRPLIQUVOLU    := NULL;
                V_IDTPNOTAFISCAL := NULL;
                V_NMOPERLANCNF   := NULL;
             END IF;
             OUT_RECORD.CDFILLANNF          := V_CDFILLANNF;
             OUT_RECORD.NRLANCTONF          := V_NRLANCTONF;
             OUT_RECORD.NRNOTAFISC          := V_NRNOTAFISC;
             OUT_RECORD.DTEMISSAO           := V_DTEMISSAO;
             OUT_RECORD.DTENTRSAID          := V_DTENTRSAID;
             OUT_RECORD.VRNOTAFISC          := V_VRNOTAFISC;
             OUT_RECORD.NRACESSONFE         := V_NRACESSONFE;
             OUT_RECORD.VRPBRUTOVOLU        := V_VRPBRUTOVOLU;
             OUT_RECORD.VRPLIQUVOLU         := V_VRPLIQUVOLU;
             OUT_RECORD.IDTPNOTAFISCAL      := V_IDTPNOTAFISCAL;
             OUT_RECORD.NMOPERLANCNF        := V_NMOPERLANCNF;

             PIPE ROW (OUT_RECORD);
             CLOSE C_NOTAFISCAL;
         END LOOP;
         CLOSE C_AF;
      END LOOP;
      CLOSE C_COMPRADOR;
   END LOOP;
   CLOSE C_UNIDADE;
END;
/