DROP TYPE ALTERACAO_APROVACAO_TABLE
/

DROP TYPE ALTERACAO_APROVACAO_TYPE
/

CREATE TYPE ALTERACAO_APROVACAO_TYPE AS
   OBJECT(  CONCCAMPO       VARCHAR2(18),
            DTAUTOFORN      DATE,
            CDFILDEST       VARCHAR2(4),
            NRAUTOFORN      VARCHAR2(10),
            CDFILLANC       VARCHAR2(4),
            CDFORNECED      VARCHAR2(21),
            NMRAZSOCFORN    VARCHAR2(100),
            DTPROGENTAF     DATE,
            VRTOTALAF       FLOAT,
            CDOPERADOR      VARCHAR2(12),
            DSINCONSAF      VARCHAR2(2000),
            IDAUDALTAF      VARCHAR2(1),
            IDGERAFDTENT    VARCHAR2(1),
            IDOBLIBAFMAN    VARCHAR2(1),
            IDTIPOLANCAF    VARCHAR2(1),
            IDTPENVAFFOR    VARCHAR2(1),
            IDTPENVAFNORMAL VARCHAR2(1),
            NRSEQAPURAF     VARCHAR2(10),
            IDSITUATUAAF    VARCHAR2(1),
            IDAFFORALIM     VARCHAR2(1),
            IDBAIXAAF       VARCHAR2(1),
            IDATUSOLILAF    VARCHAR2(1))
/

CREATE TYPE ALTERACAO_APROVACAO_TABLE AS TABLE OF ALTERACAO_APROVACAO_TYPE
/

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
                                              ) RETURN ALTERACAO_APROVACAO_TABLE PIPELINED
IS
   V_IDAUDALTAF   VARCHAR2(1);
   V_IDATUSOLILAF VARCHAR2(1);
   V_IDOBLIBAFMAN VARCHAR2(1);
   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);
BEGIN
   FOR C_FILIAL IN (SELECT FL.CDFILIAL, FL.CDFILCENTCMP
                      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.CDFILCENTCMP
                      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
                            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
                           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) OR (P_IDSITUACAO = 'T'))
                             AND ((INSTR(P_AUTOFORNE,  A.NRAUTOFORN  ) <> 0) OR (P_AUTOFORNE  = 'T'))
                             AND ((A.DTAUTOFORN  BETWEEN P_DTAFINI  AND P_DTAFFIN ) OR (P_TDTAF  = 'T'))
                             AND ((I.DTPROGENTAF BETWEEN P_DTENTINI AND P_DTENTFIN) OR (P_TDTENT = 'T'))
                             AND ((P.CDARVPROD   BETWEEN P_PRODINI  AND P_PRODFIN ) OR (P_TPROD  = 'T'))
                             AND ((A.IDBAIXAAF IS NULL) OR (A.IDBAIXAAF = '7'))
                             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)))))
                         )
      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;
         PIPE ROW (OUT_RECORD);
      END LOOP;
   END LOOP;
END;
/
