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),
       NRAUTOFORN          VARCHAR2(10),
       CDOPERADOR          VARCHAR2(12),
       NMOPERADOR          VARCHAR2(50),
       DTAUTOFORN          DATE,
       VRTOTALAF           FLOAT,
       DTPROGENTAF         DATE,
       IDBAIXAAF           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(40),
       CDFILLANNF          VARCHAR2(4),
       NRLANCTONF          VARCHAR2(10),
       NRNOTAFISC          VARCHAR(20),
       DTEMISSAO           DATE, 
       DTENTRSAID          DATE,
       VRNOTAFISC          FLOAT,
       NRACESSONFE         VARCHAR2(50),
       VRPBRUTOVOLU        FLOAT,
       VRPLIQUVOLU         FLOAT,
       IDTPNOTAFISCAL      NUMBER,
       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
                                       ) RETURN TRACKING_AF_TABLE PIPELINED
IS

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

   V_CDFILDEST                VARCHAR2(4);
   V_NMFILIAL                 VARCHAR2(50);
   V_CDFILLANC                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_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);

  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);
BEGIN
   OPEN C_AF FOR
        SELECT DISTINCT AUTOFORNE.CDFILDEST, FILIAL.NMFILIAL, AUTOFORNE.CDFILLANC, AUTOFORNE.NRAUTOFORN, 
               AUTOFORNE.CDOPERADOR, OPERADOR.NMOPERADOR,
               AUTOFORNE.CDFORNECED, AUTOFORNE.DTAUTOFORN,  ITEMAUTF.DTPROGENTAF, AUTOFORNE.IDBAIXAAF, AUTOFORNE.IDSITUATENDIMENTOAF, AUTOFORNE.TXJUSTIFICRECAF,
               AUTOFORNE.DTCONFATENDIMENTOAF, NVL(AUTOFORNE.IDCONFRECEBIMENTO, 'X') AS IDCONFRECEBIMENTO, AUTOFORNE.DTCONFRECEBIMENTO, AUTOFORNE.NRORG, 
               AUTOFORNE.VRTOTALAF, AUTOFORNE.CDOPERCONFAF
          FROM AUTOFORNE, ITEMAUTF, FILIAL, OPERADOR, PARCMPSUP
         WHERE AUTOFORNE.CDFILDEST = ITEMAUTF.CDFILDEST
           AND AUTOFORNE.CDFILLANC = ITEMAUTF.CDFILLANC
           AND AUTOFORNE.NRAUTOFORN = ITEMAUTF.NRAUTOFORN
           AND FILIAL.CDFILCENTCMP = P_CDFILCENTCMP
           AND AUTOFORNE.CDFILDEST = FILIAL.CDFILIAL
           AND NVL(AUTOFORNE.IDBAIXAAF,'0') <> '6'
           AND NVL(AUTOFORNE.IDAFEXPIRADA, 'N') = 'N'
           AND AUTOFORNE.CDOPERADOR = OPERADOR.CDOPERADOR
           AND NVL(ITEMAUTF.IDITAFMIGRADA, 'N') <> 'S'
           AND ITEMAUTF.DTPROGENTAF BETWEEN P_DTINIVALANT AND P_DTFINVALANT
           AND PARCMPSUP.CDFILIAL = AUTOFORNE.CDFILDEST
           AND NVL(PARCMPSUP.IDUTLCONFRECAF, 'N') = 'S'
         ORDER BY AUTOFORNE.CDFILDEST;
   LOOP
      FETCH C_AF INTO V_CDFILDEST, V_NMFILIAL, V_CDFILLANC, V_NRAUTOFORN,
                      V_CDOPERADOR, V_NMOPERADOR, V_CDFORNECED, V_DTAUTOFORN, 
                      V_DTPROGENTAF, V_IDBAIXAAF, V_IDSITUATENDIMENTOAF, 
                      V_TXJUSTIFICRECAF, V_DTCONFATENDIMENTOAF, V_IDCONFRECEBIMENTO, 
                      V_DTCONFRECEBIMENTO, V_NRORG, V_VRTOTALAF, V_CDOPERCONFAF;
      EXIT WHEN C_AF%NOTFOUND;
      OUT_RECORD.CDFILDEST           := V_CDFILDEST;
      OUT_RECORD.NMFILIAL            := V_NMFILIAL;
      OUT_RECORD.CDFILLANC           := V_CDFILLANC;
      OUT_RECORD.NRAUTOFORN          := V_NRAUTOFORN;
      OUT_RECORD.CDOPERADOR          := V_CDOPERADOR;
      OUT_RECORD.NMOPERADOR          := V_NMOPERADOR;
      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;


      /*ENDERECO 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.IDTPENDEFILI = 'P'
            AND ENDEFILI.CDPAIS = PAIS.CDPAIS
            AND ENDEFILI.SGESTADO = ESTADO.SGESTADO
            AND ENDEFILI.CDMUNICIPIO = MUNICIPIO.CDMUNICIPIO
            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;
      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;
      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.CDFILIAL = NOTAFISCAL.CDFILIAL
                    AND NFRELACAF.NRLANCTONF = NOTAFISCAL.NRLANCTONF;
      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 CDFILIAL, NRLANCTONF, NRNOTAFISC, DTEMISSAO, DTENTRSAID, VRNOTAFISC, N.NRACESSONFE, 0, 0, 2 AS IDTPNOTAFISCAL
                      FROM INTG_NOTAFISCAL N, INTG_ITEMAF I
                     WHERE N.NRACESSONFE = I.NRACESSONFE
                       AND N.CDFILIRECE  = V_CDFILDEST
                       AND I.NRAUTOFORNE = 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;
      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;
      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;

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