CREATE OR REPLACE FUNCTION ALTERACAO_APROVACAO( P_FORNECEDOR  IN VARCHAR2,
                                                P_COMPRADOR   IN VARCHAR2,
                                                P_CDFILDEST   IN VARCHAR2,
                                                P_IDSITUACAO  IN VARCHAR2,
                                                P_DTAFINI     IN DATE,
                                                P_DTAFFIN     IN DATE,
                                                P_TDTAF       IN VARCHAR2,
                                                P_DTENTINI    IN DATE,
                                                P_DTENTFIN    IN DATE,
                                                P_TDTENT      IN VARCHAR2,
                                                P_CDAGRUPFILI IN VARCHAR2,
                                                P_PRODINI     IN VARCHAR2,
                                                P_PRODFIN     IN VARCHAR2,
                                                P_TPROD       IN VARCHAR2,
                                                P_AUTOFORNE   IN VARCHAR2,
                                                P_TITENS      IN VARCHAR2,
                                                P_CDOPERADOR  IN VARCHAR2 DEFAULT NULL
                                              ) RETURN ALTERACAO_APROVACAO_TABLE PIPELINED
IS
   V_IDAUDALTAF   VARCHAR2(1);
   V_IDATUSOLILAF VARCHAR2(1);
   V_IDOBLIBAFMAN VARCHAR2(1);
   V_CDARVPROD_MAX PRODUTO.CDARVPROD%TYPE;
   V_CDARVPROD_MIN PRODUTO.CDARVPROD%TYPE;
   V_DTAFINI      DATE;
   V_DTAFFIN      DATE;
   V_DTENTINI     DATE;
   V_DTENTFIN     DATE;
   OUT_RECORD ALTERACAO_APROVACAO_TYPE := ALTERACAO_APROVACAO_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, 0);
BEGIN
   IF P_TPROD = 'T' THEN
      SELECT RPAD(MAX(CDARVPROD), 20, 'Z')
        INTO V_CDARVPROD_MAX
        FROM PRODUTO
       WHERE CDPRODESTO IS NOT NULL;
      SELECT MIN(CDARVPROD)
        INTO V_CDARVPROD_MIN
        FROM PRODUTO
       WHERE CDPRODESTO IS NOT NULL;
   ELSE
      V_CDARVPROD_MAX := P_PRODFIN;
      V_CDARVPROD_MIN := P_PRODINI;
   END IF;

   IF P_TDTAF = 'T' THEN
      V_DTAFINI := TRUNC((SYSDATE-366)); 
      V_DTAFFIN  := TRUNC((SYSDATE+900));
   ELSE
      V_DTAFINI := P_DTAFINI;
      V_DTAFFIN := P_DTAFFIN;
   END IF;

   IF P_TDTENT = 'T' THEN
      V_DTENTINI := TRUNC((SYSDATE-366)); 
      V_DTENTFIN := TRUNC((SYSDATE+900));
   ELSE
      V_DTENTINI := P_DTENTINI; 
      V_DTENTFIN := P_DTENTFIN;
   END IF;
   FOR C_FILIAL IN (SELECT FL.CDFILIAL, FL.NMFILIAL, FL.CDFILCENTCMP, FL.NRORG
                      FROM FILIAL FL INNER JOIN FILGRUPFILI FG ON FG.CDFILIAL = FL.CDFILIAL
                     WHERE ((INSTR(P_CDFILDEST,  FL.CDFILIAL  ) <> 0) OR (P_CDFILDEST = 'T' ))
                       AND (FG.CDGRUPFILI = P_CDAGRUPFILI)
                       AND (P_CDAGRUPFILI IS NOT NULL)
                       AND FL.CDREGIAO IS NOT NULL
                       AND NVL(FL.IDCNTRRESC, 'N') <> 'S'
                     UNION
                    SELECT FL.CDFILIAL, FL.NMFILIAL, FL.CDFILCENTCMP, FL.NRORG
                      FROM FILIAL FL
                     WHERE ((INSTR(P_CDFILDEST,  FL.CDFILIAL  ) <> 0) OR (P_CDFILDEST = 'T' ))
                       AND (P_CDAGRUPFILI IS NULL)
                       AND FL.CDREGIAO IS NOT NULL
                       AND NVL(FL.IDCNTRRESC, 'N') <> 'S' )
   LOOP
      BEGIN
         SELECT NVL(IDAUDALTAF, 'N') IDAUDALTAF,
                NVL(IDATUSOLILAF, 'N') IDATUSOLILAF
           INTO V_IDAUDALTAF,
                V_IDATUSOLILAF
           FROM PARASUPRI
          WHERE CDFILIAL = NVL(C_FILIAL.CDFILCENTCMP, C_FILIAL.CDFILIAL);
      EXCEPTION
         WHEN NO_DATA_FOUND THEN BEGIN
            V_IDAUDALTAF   := 'N';
            V_IDATUSOLILAF := 'N';
         END;
      END;

      BEGIN
         SELECT IDOBLIBAFMAN
           INTO V_IDOBLIBAFMAN
           FROM PARCMPSUP
          WHERE CDFILIAL = C_FILIAL.CDFILIAL;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_IDOBLIBAFMAN := '';
      END;

      FOR C_AUTOFORNE IN (SELECT DISTINCT
                                 A.CDFILDEST||A.CDFILLANC||A.NRAUTOFORN AS CONCCAMPO,
                                 A.DTAUTOFORN,
                                 A.CDFILDEST,
                                 A.NRAUTOFORN,
                                 A.CDFILLANC,
                                 A.CDFORNECED,
                                 FO.NMRAZSOCFORN,
                                 (SELECT CASE COUNT(DISTINCT ITEMAUTF.DTPROGENTAF) WHEN 1 THEN MAX(DTPROGENTAF) ELSE NULL END
                                 FROM ITEMAUTF
                                 WHERE A.CDFILDEST  = ITEMAUTF.CDFILDEST
                                 AND A.CDFILLANC  = ITEMAUTF.CDFILLANC
                                 AND A.NRAUTOFORN = ITEMAUTF.NRAUTOFORN) DTPROGENTAF,
                                 A.VRTOTALAF,
                                 A.CDOPERADOR,
                                 A.DSINCONSAF,
                                 FO.IDGERAFDTENT,
                                 A.IDTIPOLANCAF,
                                 FO.IDTPENVAFFOR,
                                 FO.IDTPENVAFNORMAL,
                                 A.NRSEQAPURAF,
                                 A.IDSITUATUAAF,
                                 A.IDAFFORALIM,
                                 A.IDBAIXAAF,
                                 O.NMOPERADOR
                            FROM AUTOFORNE A INNER JOIN ITEMAUTF I    ON A.CDFILDEST  = I.CDFILDEST
                                                                     AND A.CDFILLANC  = I.CDFILLANC
                                                                     AND A.NRAUTOFORN = I.NRAUTOFORN
                                             INNER JOIN FORNECEDOR FO ON A.CDFORNECED = FO.CDFORNECED
                                             INNER JOIN PRODUTO P     ON I.CDPRODUTO  = P.CDPRODUTO
                                             INNER JOIN OPERADOR O    ON A.CDOPERADOR = O.CDOPERADOR
                                             CROSS JOIN PARAMGERAL
                           WHERE A.CDFILDEST = C_FILIAL.CDFILIAL
                             AND ((INSTR(P_FORNECEDOR, A.CDFORNECED  ) <> 0) OR (P_FORNECEDOR = 'T'))
                             AND ((INSTR(P_COMPRADOR,  A.CDOPERADOR  ) <> 0) OR (P_COMPRADOR  = 'T'))
                             AND (INSTR('_'||P_IDSITUACAO||'_', A.IDTIPOLANCAF) <> 0) 
                             AND ((INSTR(P_AUTOFORNE,  A.NRAUTOFORN  ) <> 0) OR (P_AUTOFORNE  = 'T'))
                             AND ((A.DTAUTOFORN  BETWEEN V_DTAFINI  AND V_DTAFFIN ))
                             AND ((I.DTPROGENTAF BETWEEN V_DTENTINI AND V_DTENTFIN))
                             AND ((P.CDARVPROD   BETWEEN V_CDARVPROD_MIN  AND V_CDARVPROD_MAX ))
                             AND ((A.IDBAIXAAF IS NULL) OR (A.IDBAIXAAF = '7'))
                             AND NVL(I.IDBAIXITMAF, '0') = '0'
                             AND NVL(I.NRAUTOFORNORIG, 'W') = 'W'
                             AND NOT EXISTS(SELECT 1
                                              FROM ITEMAUTF IF
                                             WHERE A.CDFILDEST  = IF.CDFILDEST
                                               AND A.CDFILLANC  = IF.CDFILLANC
                                               AND A.NRAUTOFORN = IF.NRAUTOFORN
                                               AND NVL(IF.IDBAIXITMAF, '0') <> '0' )
                             AND (NVL(I.IDITMSEPARADO, 'N') = 'N')
                             AND ((P_TITENS = 'S')
                             OR  ((P_TITENS = 'N') AND (NVL(I.IDTPGERARUP, 'S') <> 'A') AND((NVL(I.QTNOVAPROGAF, I.QTPROGENTAF) <> I.QTPROGENTAF)  OR ((NVL(I.DTNOVAENTRAF, I.DTPROGENTAF) <> I.DTPROGENTAF)))))
                             AND ((PARAMGERAL.IDRESTRFILIOPER = 'N') OR
                                  (P_CDOPERADOR IS NULL            ) OR EXISTS (SELECT 1
						                                                                            FROM FILIOPER
						                                                                           WHERE FILIOPER.CDFILIAL   = C_FILIAL.CDFILIAL
						                                                                             AND FILIOPER.CDOPERADOR = P_CDOPERADOR))
                         )
      LOOP
         OUT_RECORD.CONCCAMPO       := C_AUTOFORNE.CONCCAMPO;
         OUT_RECORD.DTAUTOFORN      := C_AUTOFORNE.DTAUTOFORN;
         OUT_RECORD.CDFILDEST       := C_AUTOFORNE.CDFILDEST;
         OUT_RECORD.NRAUTOFORN      := C_AUTOFORNE.NRAUTOFORN;
         OUT_RECORD.CDFILLANC       := C_AUTOFORNE.CDFILLANC;
         OUT_RECORD.CDFORNECED      := C_AUTOFORNE.CDFORNECED;
         OUT_RECORD.NMRAZSOCFORN    := C_AUTOFORNE.NMRAZSOCFORN;
         OUT_RECORD.DTPROGENTAF     := C_AUTOFORNE.DTPROGENTAF;
         OUT_RECORD.VRTOTALAF       := C_AUTOFORNE.VRTOTALAF;
         OUT_RECORD.CDOPERADOR      := C_AUTOFORNE.CDOPERADOR;
         OUT_RECORD.DSINCONSAF      := C_AUTOFORNE.DSINCONSAF;
         OUT_RECORD.IDAUDALTAF      := V_IDAUDALTAF;
         OUT_RECORD.IDGERAFDTENT    := C_AUTOFORNE.IDGERAFDTENT;
         OUT_RECORD.IDOBLIBAFMAN    := V_IDOBLIBAFMAN;
         OUT_RECORD.IDTIPOLANCAF    := C_AUTOFORNE.IDTIPOLANCAF ;
         OUT_RECORD.IDTPENVAFFOR    := C_AUTOFORNE.IDTPENVAFFOR;
         OUT_RECORD.IDTPENVAFNORMAL := C_AUTOFORNE.IDTPENVAFNORMAL;
         OUT_RECORD.NRSEQAPURAF     := C_AUTOFORNE.NRSEQAPURAF;
         OUT_RECORD.IDSITUATUAAF    := C_AUTOFORNE.IDSITUATUAAF;
         OUT_RECORD.IDAFFORALIM     := C_AUTOFORNE.IDAFFORALIM ;
         OUT_RECORD.IDBAIXAAF       := C_AUTOFORNE.IDBAIXAAF;
         OUT_RECORD.IDATUSOLILAF    := V_IDATUSOLILAF;
         OUT_RECORD.NMFILIAL        := C_FILIAL.NMFILIAL;
         OUT_RECORD.NMOPERADOR      := C_AUTOFORNE.NMOPERADOR;
         OUT_RECORD.NRORG           := C_FILIAL.NRORG;
         PIPE ROW (OUT_RECORD);
      END LOOP;
   END LOOP;
END;
/