CREATE OR REPLACE FUNCTION CONSULTA_ENVIO(P_CDFILCENTCMP    IN VARCHAR2,
                                          P_CDFILDEST       IN VARCHAR2,
                                          P_CDFORNECED      IN VARCHAR2,
                                          P_CDOPERADOR      IN VARCHAR2,
                                          P_CDCOMPRADOR     IN VARCHAR2,
                                          P_NRAPURPRECO     IN VARCHAR2,
                                          P_SITUAF          IN VARCHAR2,
                                          P_IDTCLASSENVAFOR IN VARCHAR2,
                                          P_DTAUTFINI       IN DATE,
                                          P_DTAUTFFIM       IN DATE,
                                          P_TDTAUTOFORN     IN VARCHAR2,
                                          P_DTPROGINI       IN DATE,
                                          P_DTPROGFIM       IN DATE,
                                          P_TDTPROGENTAF    IN VARCHAR2,
                                          P_DTENVIOAF_INI   IN DATE,
                                          P_DTENVIOAF_FIN   IN DATE,
                                          P_TDTENVIOAF      IN VARCHAR2,
                                          P_IDCOINCDTAPU    IN VARCHAR2,
                                          P_DTAPURAAF_INI   IN DATE,
                                          P_DTAPURAAF_FIN   IN DATE,
                                          P_TDTAPURAAF      IN VARCHAR2,
                                          P_ENVIADO         IN VARCHAR2,
                                          P_RETORNADO       IN VARCHAR2
                                           ) RETURN CONSULTA_ENVIO_TABLE PIPELINED
IS
   V_IDAUDALTAF   VARCHAR2(1);
   V_IDATUSOLILAF VARCHAR2(1);
   V_IDOBLIBAFMAN VARCHAR2(1);
   OUT_RECORD CONSULTA_ENVIO_TYPE := CONSULTA_ENVIO_TYPE (NULL, NULL, NULL, NULL, NULL, NULL,
                                                          NULL, NULL, NULL, 0, 0, NULL,
                                                          NULL, NULL, NULL, NULL, NULL, NULL);
BEGIN
  
      FOR C_AUTOFORNE IN (SELECT '1' IDANALISE, AP.NRAPURPREC,
                                 EV.IDTCLASSENVAFOR, FO.NMFANTFORN, FO.NMRAZSOCFORN,
                                 TO_DATE(EV.DTENVIOAFFORN, 'DD/MM/YYYY') DTENVIOAFFORN, TO_DATE(EV.DTRECEBEENVAF, 'DD/MM/YYYY') DTRECEBEENVAF,
                                 EV.IDTIPENVAFFORN, EV.NRSEQENVIOAFORN,
                                 EV.QTAFSENVIOFOR,  EV.QTAFSNENVFOR,  EV.SGESTADO,   EV.IDTIPENVLIDO,
                                 EV.DSARQENVIOAF,   EV.IDCONCENVIOAF, AF.CDFORNECED, AP.CDFILLANC,
                                 AP.IDSITAPUPREC
                            FROM APURPRECO AP, AUTOFORNE AF, ENVIOAFFORN EV, FORNECEDOR FO, FILIAL FI
                           WHERE ((INSTR(P_CDFILDEST, FI.CDFILIAL) <> 0   ) OR (P_CDFILDEST = 'T'))
                             AND (AF.CDFILDEST       = FI.CDFILIAL        )
                             AND (AP.NRAPURPREC      = AF.NRSEQAPURAF     )
                             AND (AP.CDFILLANC       = AF.CDFILLANC       )
                             AND (AP.CDCOMPRADOR     = AF.CDOPERADOR      )
                             AND (AP.CDFILCENTCMP    = P_CDFILCENTCMP     )
                             AND (AF.CDFORNECED      = EV.CDFORNECED      )
                             AND (AF.NRSEQENVIOAFORN = EV.NRSEQENVIOAFORN )
                             AND (AP.DTINIAPUPREC    = EV.DTINIAPURAFFORN )
                             AND (AP.DTFINAPUPREC    = EV.DTFINAPURAFFORN )
                             AND (AF.CDFORNECED      = FO.CDFORNECED      )
                             AND (AF.NRSEQENVIOAFORN IS NOT NULL          )
                             AND ((INSTR(P_CDFORNECED,      AF.CDFORNECED     ) <> 0) OR (P_CDFORNECED  = 'T'))
                             AND ((INSTR(P_CDOPERADOR,      AP.CDOPERADOR     ) <> 0) OR (P_CDOPERADOR  = 'T'))
                             AND ((INSTR(P_CDCOMPRADOR,     AP.CDCOMPRADOR    ) <> 0) OR (P_CDCOMPRADOR = 'T'))
                             AND ((INSTR(P_NRAPURPRECO,     AP.NRAPURPREC     ) <> 0) OR (P_NRAPURPRECO = 'T'))
                             AND  (INSTR(P_SITUAF,          AP.IDSITAPUPREC   ) <> 0)
                             AND  (INSTR(P_IDTCLASSENVAFOR, EV.IDTCLASSENVAFOR) <> 0)
                             AND  (INSTR(EV.LISTNRSEQAPUR,  AP.NRAPURPREC     ) <> 0)
                             AND ((TO_CHAR(AF.DTAUTOFORN, 'DD/MM/YYYY')    BETWEEN P_DTAUTFINI     AND P_DTAUTFFIM    ) OR (P_TDTAUTOFORN  = 'T'))
                             AND ((TO_CHAR(EV.DTENVIOAFFORN, 'DD/MM/YYYY') BETWEEN P_DTENVIOAF_INI AND P_DTENVIOAF_FIN) OR (P_TDTENVIOAF   = 'T'))
                             AND ((P_IDCOINCDTAPU = 'S' AND ((AP.DTINIAPUPREC  = P_DTAPURAAF_INI AND AP.DTFINAPUPREC  = P_DTAPURAAF_FIN) OR (P_TDTAPURAAF = 'T')))
                              OR  (P_IDCOINCDTAPU = 'N' AND ((AP.DTINIAPUPREC >= P_DTAPURAAF_INI AND AP.DTFINAPUPREC <= P_DTAPURAAF_FIN) OR (P_TDTAPURAAF = 'T'))))
                             AND ((P_ENVIADO      = 'S' AND (DTRECEBEENVAF IS NULL))
                              OR  (P_RETORNADO    = 'S' AND (DTRECEBEENVAF IS NOT NULL)))
                             AND EXISTS(SELECT 1
                                          FROM ITEMAUTF IT
                                         WHERE (AF.CDFILDEST  = IT.CDFILDEST)
                                           AND (AF.CDFILLANC  = IT.CDFILLANC)
                                           AND (AF.NRAUTOFORN = IT.NRAUTOFORN
                                           AND ((TO_CHAR(IT.DTPROGENTAF, 'DD/MM/YYYY') BETWEEN P_DTPROGINI AND P_DTPROGFIM) OR (P_TDTPROGENTAF = 'T'))))
                            GROUP BY AP.NRAPURPREC,
                                 EV.IDTCLASSENVAFOR, FO.NMFANTFORN, FO.NMRAZSOCFORN,
                                 TO_DATE(EV.DTENVIOAFFORN, 'DD/MM/YYYY'), TO_DATE(EV.DTRECEBEENVAF, 'DD/MM/YYYY'),
                                 EV.IDTIPENVAFFORN, EV.NRSEQENVIOAFORN,
                                 EV.QTAFSENVIOFOR, EV.QTAFSNENVFOR, EV.SGESTADO, EV.IDTIPENVLIDO,
                                 EV.DSARQENVIOAF, EV.IDCONCENVIOAF, AP.CDFILLANC,
                                 AP.IDSITAPUPREC, AF.CDFORNECED

                          UNION

                          SELECT '2' IDANALISE, AP.NRAPURPREC,
                                 '' IDTCLASSENVAFOR, FO.NMFANTFORN, FO.NMRAZSOCFORN,
                                 TO_DATE('') DTENVIOAFFORN,  TO_DATE('') DTRECEBEENVAF,
                                 '' IDTIPENVAFFORN, '' NRSEQENVIOAFORN,
                                 0 QTAFSENVIOFOR,   0 QTAFSNENVFOR,   '' SGESTADO,   '' IDTIPENVLIDO,
                                 '' DSARQENVIOAF,   '' IDCONCENVIOAF, AF.CDFORNECED, AP.CDFILLANC,
                                 AP.IDSITAPUPREC
                            FROM APURPRECO AP, AUTOFORNE AF, FORNECEDOR FO, FILIAL FI
                           WHERE ((INSTR(P_CDFILDEST, FI.CDFILIAL ) <> 0) OR (P_CDFILDEST = 'T'))
                             AND (AF.CDFILDEST    = FI.CDFILIAL   )
                             AND (AP.NRAPURPREC   = AF.NRSEQAPURAF)
                             AND (AP.CDFILLANC    = AF.CDFILLANC  )
                             AND (AP.CDCOMPRADOR  = AF.CDOPERADOR )
                             AND (AP.CDFILCENTCMP = P_CDFILCENTCMP)
                             AND (AF.CDFORNECED   = FO.CDFORNECED )
                             AND (AF.NRSEQENVIOAFORN IS NULL      )
                             AND ((INSTR(P_CDFORNECED , AF.CDFORNECED  ) <> 0) OR (P_CDFORNECED  = 'T'))
                             AND ((INSTR(P_CDOPERADOR , AP.CDOPERADOR  ) <> 0) OR (P_CDOPERADOR  = 'T'))
                             AND ((INSTR(P_CDCOMPRADOR, AP.CDCOMPRADOR ) <> 0) OR (P_CDCOMPRADOR = 'T'))
                             AND ((INSTR(P_NRAPURPRECO, AP.NRAPURPREC  ) <> 0) OR (P_NRAPURPRECO = 'T'))
                             AND  (INSTR(P_SITUAF,      AP.IDSITAPUPREC) <> 0)
                             AND ((TO_CHAR(AF.DTAUTOFORN, 'DD/MM/YYYY' ) BETWEEN P_DTAUTFINI AND P_DTAUTFFIM) OR (P_TDTAUTOFORN  = 'T'))
                             AND ((P_IDCOINCDTAPU = 'S' AND ((AP.DTINIAPUPREC  = P_DTAPURAAF_INI AND AP.DTFINAPUPREC  = P_DTAPURAAF_FIN) OR (P_TDTAPURAAF = 'T')))
                              OR  (P_IDCOINCDTAPU = 'N' AND ((AP.DTINIAPUPREC >= P_DTAPURAAF_INI AND AP.DTFINAPUPREC <= P_DTAPURAAF_FIN) OR (P_TDTAPURAAF = 'T'))))
                             AND EXISTS(SELECT 1
                                          FROM ITEMAUTF IT
                                         WHERE (AF.CDFILDEST  = IT.CDFILDEST)
                                           AND (AF.CDFILLANC  = IT.CDFILLANC)
                                           AND (AF.NRAUTOFORN = IT.NRAUTOFORN
                                           AND ((TO_CHAR(IT.DTPROGENTAF, 'DD/MM/YYYY') BETWEEN P_DTPROGINI AND P_DTPROGFIM) OR (P_TDTPROGENTAF = 'T')))))
      LOOP
         OUT_RECORD.IDANALISE       := C_AUTOFORNE.IDANALISE;
         OUT_RECORD.NRAPURPREC      := C_AUTOFORNE.NRAPURPREC;
         OUT_RECORD.IDTCLASSENVAFOR := C_AUTOFORNE.IDTCLASSENVAFOR;
         OUT_RECORD.NMFANTFORN      := C_AUTOFORNE.NMFANTFORN;
         OUT_RECORD.NMRAZSOCFORN    := C_AUTOFORNE.NMRAZSOCFORN;
         OUT_RECORD.DTENVIOAFFORN   := C_AUTOFORNE.DTENVIOAFFORN;
         OUT_RECORD.DTRECEBEENVAF   := C_AUTOFORNE.DTRECEBEENVAF;
         OUT_RECORD.IDTIPENVAFFORN  := C_AUTOFORNE.IDTIPENVAFFORN;
         OUT_RECORD.NRSEQENVIOAFORN := C_AUTOFORNE.NRSEQENVIOAFORN;
         OUT_RECORD.QTAFSENVIOFOR   := C_AUTOFORNE.QTAFSENVIOFOR;
         OUT_RECORD.QTAFSNENVFOR    := C_AUTOFORNE.QTAFSNENVFOR;
         OUT_RECORD.SGESTADO        := C_AUTOFORNE.SGESTADO;
         OUT_RECORD.IDTIPENVLIDO    := C_AUTOFORNE.IDTIPENVLIDO;
         OUT_RECORD.DSARQENVIOAF    := C_AUTOFORNE.DSARQENVIOAF;
         OUT_RECORD.IDCONCENVIOAF   := C_AUTOFORNE.IDCONCENVIOAF;
         OUT_RECORD.CDFORNECED      := C_AUTOFORNE.CDFORNECED;
         OUT_RECORD.CDFILLANC       := C_AUTOFORNE.CDFILLANC;
         OUT_RECORD.IDSITAPUPREC    := C_AUTOFORNE.IDSITAPUPREC;
         PIPE ROW (OUT_RECORD);
      END LOOP;
 
END;
/
