CREATE OR REPLACE
PROCEDURE USR_EXPO_AUTOFORNE_SAP(P_ALTINCSAP IN VARCHAR2 DEFAULT 'INC')
IS
   V_CCOMMIT                 INTEGER;
   V_DATAENTREGA             DATE;
   V_ALTERACAO               VARCHAR2(01);
   V_STATUS                  INTEGER;
   V_LOCALNTREGA             VARCHAR2(10);
   V_LOCALFATURAMENTOCLIENTE VARCHAR2(10);
   V_LOCALCOBRANCACLIENTE    VARCHAR2(10);
   V_CDTIPOFATUR             VARCHAR2(03);
   V_TPINTERFACE             VARCHAR2(1);
   V_USR_SAP_AF_IDINTERFACE  NUMBER;
BEGIN
   V_CCOMMIT   := 0;
   /* Fixo T */
   V_ALTERACAO := 'T';
   /* 0 - Ok
      1 - Novo
      2 - Erro
      3 - Em processamento */
   V_STATUS := 1;

   FOR C_AUTOFORNE IN (SELECT A.CDFILDEST, A.CDFILLANC, A.NRAUTOFORN,
                              P.DSOBSFILIAL, P.DSPERIENTFIL, F.NRINSJURFORN, A.DTAUTOFORN, A.CDOPERADOR,
                              A.CDFORMPGTO, A.IDTPFORPGTAF, A.IDTIPOEMITAF, A.CDEMPRESA, A.NRPARCPGTOAF,
                              A.IDUTDIAPGTAF, A.VRTOTALAF, A.IDBAIXAAF, A.IDBAIXAFSAI, A.IDAFEXPIRADA,
                              CASE A.VRTOTALAF
                                 WHEN 0
                                 THEN 'S'
                                 ELSE CASE NVL(IDBAIXAAF, '0') WHEN '4' THEN 'S'
                                                               WHEN '5' THEN 'S'
                                                               ELSE 'N' END
                              END CANCELADO, NVL(A.USR_TPINTERFACE_SAP,'I') USR_TPINTERFACE_SAP,
                              A.NRDIAPGFIXAF, NVL(A.USR_IDINTEGRAMESAP, 'N') USR_IDINTEGRAMESAP
                         FROM AUTOFORNE A INNER JOIN FORNECEDOR F  ON F.CDFORNECED      = A.CDFORNECED
                                          INNER JOIN ENVIOAFFORN E ON E.NRSEQENVIOAFORN = A.NRSEQENVIOAFORN
                                                                  --AND E.CDFORNECED      = A.CDFORNECED
                                          LEFT  JOIN PARCMPSUP  P  ON P.CDFILIAL         = A.CDFILDEST
                        WHERE NVL(A.USR_IDINTEGRAMESAP, 'N') = 'S'
                          AND NVL(F.USR_INTEGRAMESAP, 'N')   = 'S'
                          AND INSTR(E.LISTNRSEQAPUR, A.NRSEQAPURAF) > 0) LOOP

      SELECT MIN(DTPROGENTAF)
        INTO V_DATAENTREGA
        FROM ITEMAUTF
       WHERE CDFILDEST  = C_AUTOFORNE.CDFILDEST
         AND CDFILLANC  = C_AUTOFORNE.CDFILLANC
         AND NRAUTOFORN = C_AUTOFORNE.NRAUTOFORN;
      IF V_DATAENTREGA IS NULL THEN
         SELECT MIN(DATAENTREGA)
           INTO V_DATAENTREGA
           FROM USR_SAP_AUTOFORNE
          WHERE IDENTIFIC = C_AUTOFORNE.NRAUTOFORN;
      END IF;
      /*ENTREGA*/
      BEGIN
         SELECT NVL(MAX(IDUSR_SAP_ME_ENDERECO), '')
           INTO V_LOCALNTREGA
           FROM ENDEFILI E
          WHERE E.CDFILIAL = C_AUTOFORNE.CDFILDEST
            AND E.IDUSR_SAP_ME_ENDERECO IS NOT NULL
            AND E.IDTPENDEFILI = CASE (SELECT IDTPENDEFILI
                                         FROM ENDEFILI
                                        WHERE IDTPENDEFILI = 'T'
                                          AND CDFILIAL = E.CDFILIAL) WHEN 'T'
                                                                     THEN 'T'
                                                                     ELSE 'P' END;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_LOCALNTREGA := NULL;
      END;

      BEGIN
         SELECT NVL(MAX(CDTIPOFATUR), '')
           INTO V_CDTIPOFATUR
           FROM PARAMFILIAL
          WHERE CDFILIAL = C_AUTOFORNE.CDFILDEST;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_CDTIPOFATUR := NULL;
      END;

      /*FATURAMENTO*/
      BEGIN
         IF V_CDTIPOFATUR = '003' THEN
            SELECT NVL(MAX(IDENTIFIC), '')
              INTO V_LOCALFATURAMENTOCLIENTE
              FROM USR_SAP_ME_ENDERECO
             WHERE CDFILIAL = C_AUTOFORNE.CDFILDEST
               AND IDTIPOENDERECO = 'F'
               AND IDTABALTTEK    = 'C';
         ELSE
            IF C_AUTOFORNE.IDTIPOEMITAF = '1' THEN
               SELECT NVL(MAX(IDENTIFIC), '')
                 INTO V_LOCALFATURAMENTOCLIENTE
                 FROM USR_SAP_ME_ENDERECO
                WHERE CDFILIAL = C_AUTOFORNE.CDFILDEST
                  AND IDTIPOENDERECO = 'F'
                  AND IDTABALTTEK    = '1';
            ELSE
               SELECT NVL(MAX(ER.IDUSR_SAP_ME_ENDERECO), '')
                 INTO V_LOCALFATURAMENTOCLIENTE
                 FROM (SELECT EF.IDUSR_SAP_ME_ENDERECO_FAT IDUSR_SAP_ME_ENDERECO
                         FROM EMPRCNTRFIL EF
                        WHERE EF.CDFILIAL  = C_AUTOFORNE.CDFILDEST
                          AND EF.CDEMPRESA = C_AUTOFORNE.CDEMPRESA
                          AND EF.IDUSR_SAP_ME_ENDERECO IS NOT NULL
                        UNION
                       SELECT IDENTIFIC IDUSR_SAP_ME_ENDERECO
                         FROM USR_SAP_ME_ENDERECO
                        WHERE CDFILIAL = C_AUTOFORNE.CDFILDEST
                          AND IDTIPOENDERECO = 'F'
                          AND IDTABALTTEK    = '1'
                          AND NOT EXISTS (SELECT 1
                                            FROM EMPRCNTRFIL EF
                                           WHERE EF.CDFILIAL  = C_AUTOFORNE.CDFILDEST
                                             AND EF.CDEMPRESA = C_AUTOFORNE.CDEMPRESA
                                             AND EF.IDUSR_SAP_ME_ENDERECO IS NOT NULL)) ER;
            END IF;
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_LOCALFATURAMENTOCLIENTE := NULL;
      END;
      /*COBRANCA*/
      BEGIN
         IF C_AUTOFORNE.IDTIPOEMITAF = '3' THEN
            SELECT NVL(MAX(IDENTIFIC), '')
              INTO V_LOCALCOBRANCACLIENTE
              FROM USR_SAP_ME_ENDERECO
             WHERE CDFILIAL       = C_AUTOFORNE.CDFILDEST
               AND IDTIPOENDERECO = 'C'
               AND IDTABALTTEK    = 'C';
         ELSE
            IF C_AUTOFORNE.IDTIPOEMITAF = '1' THEN
               SELECT NVL(MAX(IDENTIFIC), '')
                 INTO V_LOCALCOBRANCACLIENTE
                 FROM USR_SAP_ME_ENDERECO
                WHERE CDFILIAL = C_AUTOFORNE.CDFILDEST
                  AND IDTIPOENDERECO = 'C'
                  AND IDTABALTTEK    = 'E';
            ELSE
               SELECT NVL(MAX(ER.IDUSR_SAP_ME_ENDERECO), '')
                 INTO V_LOCALCOBRANCACLIENTE
                 FROM (SELECT EF.IDUSR_SAP_ME_ENDERECO
                         FROM EMPRCNTRFIL EF
                        WHERE EF.CDFILIAL  = C_AUTOFORNE.CDFILDEST
                          AND EF.CDEMPRESA = C_AUTOFORNE.CDEMPRESA
                          AND EF.IDUSR_SAP_ME_ENDERECO IS NOT NULL
                        UNION
                       SELECT IDENTIFIC IDUSR_SAP_ME_ENDERECO
                         FROM USR_SAP_ME_ENDERECO
                        WHERE CDFILIAL = C_AUTOFORNE.CDFILDEST
                          AND IDTIPOENDERECO = 'C'
                          AND IDTABALTTEK    = 'E'
                          AND NOT EXISTS (SELECT 1
                                            FROM EMPRCNTRFIL EF
                                           WHERE EF.CDFILIAL  = C_AUTOFORNE.CDFILDEST
                                             AND EF.CDEMPRESA = C_AUTOFORNE.CDEMPRESA
                                             AND EF.IDUSR_SAP_ME_ENDERECO IS NOT NULL)) ER;
            END IF;
         END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_LOCALCOBRANCACLIENTE := NULL;
      END;

      IF (V_LOCALNTREGA IS NOT NULL) AND (V_LOCALFATURAMENTOCLIENTE IS NOT NULL) AND (V_LOCALCOBRANCACLIENTE IS NOT NULL) THEN
         BEGIN
            SELECT NVL(MAX('A'), 'I')
              INTO V_TPINTERFACE
              FROM USR_SAP_AUTOFORNE
             WHERE IDENTIFIC = C_AUTOFORNE.NRAUTOFORN;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               V_TPINTERFACE := 'I';
         END;
         V_USR_SAP_AF_IDINTERFACE := USR_SAP_AF_IDINTERFACE.NEXTVAL;
         INSERT INTO USR_SAP_AUTOFORNE(ALTERACAO, IDENTIFIC, RESUMO, OBSCLI, DSPERIENTFIL,
                                       DATAENTREGA, EXTRA, LOCALENTREGA, LOCALFATURAMENTOCLIENTE, LOCALCOBRANCACLIENTE,
                                       CENTRO, FORNECEDORCLIENTE, DTAUTOFORN, TAG, LOGIN,
                                       CDFORMPGTO, IDTPFORPGTAF, IDTIPOEMITAF, EMPRESA, NRPARCPGTOAF,
                                       NRDIAPGFIXAF, IDUTDIAPGTAF, VRTOTALAF, IDBAIXAAF, IDBAIXAFSAI,
                                       IDAFEXPIRADA, CANCELADO, IDINTERFACE, TPINTERFACE, DTINTERFACEORI,
                                       DTINTERFACEDEST, STATUS, APPORIGEM, APPDESTINO)
              VALUES (V_ALTERACAO, C_AUTOFORNE.NRAUTOFORN, C_AUTOFORNE.NRAUTOFORN, C_AUTOFORNE.DSOBSFILIAL, C_AUTOFORNE.DSPERIENTFIL,
                      V_DATAENTREGA, NULL, LPAD(V_LOCALNTREGA,10,'0'), LPAD(V_LOCALFATURAMENTOCLIENTE,10,'0'), LPAD(V_LOCALCOBRANCACLIENTE,10,'0'),
                      C_AUTOFORNE.CDFILDEST, C_AUTOFORNE.NRINSJURFORN, C_AUTOFORNE.DTAUTOFORN, C_AUTOFORNE.CDOPERADOR, C_AUTOFORNE.CDOPERADOR,
                      C_AUTOFORNE.CDFORMPGTO, C_AUTOFORNE.IDTPFORPGTAF, C_AUTOFORNE.IDTIPOEMITAF, C_AUTOFORNE.CDEMPRESA, C_AUTOFORNE.NRPARCPGTOAF,
                      C_AUTOFORNE.NRDIAPGFIXAF, C_AUTOFORNE.IDUTDIAPGTAF, NVL(C_AUTOFORNE.VRTOTALAF, 0), C_AUTOFORNE.IDBAIXAAF, C_AUTOFORNE.IDBAIXAFSAI,
                      C_AUTOFORNE.IDAFEXPIRADA, C_AUTOFORNE.CANCELADO, V_USR_SAP_AF_IDINTERFACE, V_TPINTERFACE, SYSDATE,
                      NULL, V_STATUS, 1, 4);

         UPDATE AUTOFORNE
            SET USR_IDINTEGRAMESAP  = 'N',
                USR_TPINTERFACE_SAP = C_AUTOFORNE.USR_TPINTERFACE_SAP
         WHERE CDFILDEST  = C_AUTOFORNE.CDFILDEST
           AND CDFILLANC    = C_AUTOFORNE.CDFILLANC
           AND NRAUTOFORN   = C_AUTOFORNE.NRAUTOFORN;

         IF C_AUTOFORNE.CANCELADO = 'S' THEN
            UPDATE ITEMAUTF
               SET USR_IDINTEGRAMESAP = 'S'
             WHERE CDFILDEST  = C_AUTOFORNE.CDFILDEST
               AND CDFILLANC  = C_AUTOFORNE.CDFILLANC
               AND NRAUTOFORN = C_AUTOFORNE.NRAUTOFORN;
         END IF;
         USR_EXPO_ITEMAUTF_SAP('INC', C_AUTOFORNE.NRAUTOFORN, V_USR_SAP_AF_IDINTERFACE);
      END IF;
   END LOOP;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('USR_EXPO_AUTOFORNE_SAP','6.02.xxx'); END;
/