create or replace
PROCEDURE IMPORTA_CLIENFILIAL_SAP
IS
   V_CDCLIENTE  INTEGER;
   V_CDFILIAL   INTEGER;
   V_CLIFILIAL  INTEGER;
   V_VRPERC     FLOAT;

   V_CDFILICLIE VARCHAR2(04);
   V_CDCLIEPRIN VARCHAR2(21);
BEGIN
   V_CDFILICLIE := '';
   V_CDCLIEPRIN := 'P';

   FOR C_CLIENFILIAL IN
       (SELECT /*CLIENFILIAL*/
               CDCLIENTE, CDFILIAL, NVL(IDCLIENPRIN, 'N') AS IDCLIENPRIN, NVL(IDUTILADIANT, 'N') AS IDUTILADIANT, VRPERATDESP, NVL(IDUTCONTCLPR, 'N') AS IDUTCONTCLPR, IDPERIODFATC, NVL(IDCOREFECLIFILI, 'I') AS IDCOREFECLIFILI,
               NVL(IDCONSMINEFECLI, 'I') AS IDCONSMINEFECLI, DTVENCONTRCLIE, NRDIAAVISOCVEN, CDEXCLIENFIL,
               /*USR_SAP_CLIENFILIAL*/
               IDINTERFACE, TPINTERFACE
          FROM USR_SAP_CLIENFILIAL
         WHERE DTINTERFACEDEST IS NULL
         ORDER BY CDFILIAL, DTINTERFACEORI)
   LOOP
      IF (V_CDCLIEPRIN <> 'P') AND (V_CDFILICLIE <> C_CLIENFILIAL.CDFILIAL) THEN
         UPDATE CLIENFILIAL
            SET IDCLIENPRIN = 'N'
          WHERE CDFILIAL = V_CDFILICLIE
            AND CDCLIENTE <> V_CDCLIEPRIN;

         V_CDCLIEPRIN := 'P';
      END IF;

      V_VRPERC := 0;
      SELECT COUNT(1) AS CDCLIENTE
        INTO V_CDCLIENTE
        FROM CLIENTE
       WHERE CDCLIENTE = C_CLIENFILIAL.CDCLIENTE;
      IF V_CDCLIENTE > 0 THEN
         SELECT COUNT(1) AS CDFILIAL
           INTO V_CDFILIAL
           FROM FILIAL
          WHERE CDFILIAL = C_CLIENFILIAL.CDFILIAL;
         IF V_CDFILIAL > 0 THEN
            SELECT COUNT(1) AS CLIFILI
              INTO V_CLIFILIAL
              FROM CLIENFILIAL
             WHERE CDCLIENTE = C_CLIENFILIAL.CDCLIENTE
               AND CDFILIAL = C_CLIENFILIAL.CDFILIAL;

            IF V_CLIFILIAL = 0 THEN
               IF C_CLIENFILIAL.VRPERATDESP IS NOT NULL THEN
                  IF C_CLIENFILIAL.VRPERATDESP > 100 THEN
                     V_VRPERC := 100;
                  ELSE
                     IF C_CLIENFILIAL.VRPERATDESP < 0 THEN
                        V_VRPERC := 0;
                     ELSE
                        V_VRPERC := C_CLIENFILIAL.VRPERATDESP;
                     END IF;
                  END IF;
               END IF;

               INSERT INTO CLIENFILIAL(CDCLIENTE, CDFILIAL, IDCLIENPRIN, IDUTILADIANT, VRPERATDESP, IDUTCONTCLPR, IDPERIODFATC, IDCOREFECLIFILI,
                                       IDCONSMINEFECLI, DTVENCONTRCLIE, NRDIAAVISOCVEN, CDEXCLIENFIL)
               VALUES (C_CLIENFILIAL.CDCLIENTE, C_CLIENFILIAL.CDFILIAL, C_CLIENFILIAL.IDCLIENPRIN, C_CLIENFILIAL.IDUTILADIANT, V_VRPERC, C_CLIENFILIAL.IDUTCONTCLPR, C_CLIENFILIAL.IDPERIODFATC, C_CLIENFILIAL.IDCOREFECLIFILI,
                       C_CLIENFILIAL.IDCONSMINEFECLI, C_CLIENFILIAL.DTVENCONTRCLIE, C_CLIENFILIAL.NRDIAAVISOCVEN, C_CLIENFILIAL.CDEXCLIENFIL);

               UPDATE USR_SAP_CLIENFILIAL SET DTINTERFACEDEST = SYSDATE, STATUS = 0
                WHERE IDINTERFACE   = C_CLIENFILIAL.IDINTERFACE;

               IF C_CLIENFILIAL.IDCLIENPRIN = 'S' THEN
                  V_CDCLIEPRIN := C_CLIENFILIAL.CDCLIENTE;
               END IF;

               INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
               VALUES (C_CLIENFILIAL.IDINTERFACE, 'I', 12, SYSDATE, 0, 'Cliente '|| C_CLIENFILIAL.CDCLIENTE || ' foi cadastrado para a filial '|| C_CLIENFILIAL.CDFILIAL ||' com sucesso.');
            ELSE
               UPDATE CLIENFILIAL SET IDCLIENPRIN = C_CLIENFILIAL.IDCLIENPRIN, IDUTILADIANT = C_CLIENFILIAL.IDUTILADIANT, VRPERATDESP = V_VRPERC, IDUTCONTCLPR = C_CLIENFILIAL.IDUTCONTCLPR,
                                      IDPERIODFATC = C_CLIENFILIAL.IDPERIODFATC, IDCOREFECLIFILI = C_CLIENFILIAL.IDCOREFECLIFILI, IDCONSMINEFECLI = C_CLIENFILIAL.IDCONSMINEFECLI, DTVENCONTRCLIE = C_CLIENFILIAL.DTVENCONTRCLIE,
                                      NRDIAAVISOCVEN = C_CLIENFILIAL.NRDIAAVISOCVEN, CDEXCLIENFIL = C_CLIENFILIAL.CDEXCLIENFIL
                WHERE CDCLIENTE = C_CLIENFILIAL.CDCLIENTE
                  AND CDFILIAL  = C_CLIENFILIAL.CDFILIAL;

               IF C_CLIENFILIAL.IDCLIENPRIN = 'S' THEN
                  V_CDCLIEPRIN := C_CLIENFILIAL.CDCLIENTE;
               END IF;

               UPDATE USR_SAP_CLIENFILIAL SET DTINTERFACEDEST = SYSDATE, STATUS = 0
                WHERE IDINTERFACE   = C_CLIENFILIAL.IDINTERFACE;

               INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
               VALUES (C_CLIENFILIAL.IDINTERFACE, 'A', 12, SYSDATE, 1, 'Cliente '||C_CLIENFILIAL.CDCLIENTE ||' atualizado com sucesso para a filial '|| C_CLIENFILIAL.CDFILIAL ||' no sistema Teknisa');
            END IF;
         ELSE
            INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
            VALUES (C_CLIENFILIAL.IDINTERFACE, 'I', 12, SYSDATE, 1, 'C'||CHR(243)||'digo da filial '|| C_CLIENFILIAL.CDFILIAL ||' n'||CHR(227)||'o cadastrado no sistema Teknisa');
            
            UPDATE USR_SAP_CLIENFILIAL SET DTINTERFACEDEST = SYSDATE, STATUS = 2
             WHERE IDINTERFACE   = C_CLIENFILIAL.IDINTERFACE;
         END IF;
      ELSE
         INSERT INTO USR_SAP_LOG(IDINTERFACE, IDSEQLOG, IDPROCESSO, DTLOG, STATUS, OBSSTATUS)
         VALUES (C_CLIENFILIAL.IDINTERFACE, 'I', 12, SYSDATE, 1, 'C'||CHR(243)||'digo do cliente '|| C_CLIENFILIAL.CDCLIENTE ||' n'||CHR(227)||'o cadastrado no sistema Teknisa');

         UPDATE USR_SAP_CLIENFILIAL SET DTINTERFACEDEST = SYSDATE, STATUS = 2
          WHERE IDINTERFACE   = C_CLIENFILIAL.IDINTERFACE;
      END IF;

      V_CDFILICLIE := C_CLIENFILIAL.CDFILIAL;
   END LOOP;

   IF (V_CDCLIEPRIN <> 'P') THEN
      UPDATE CLIENFILIAL
         SET IDCLIENPRIN = 'N'
       WHERE CDFILIAL = V_CDFILICLIE
         AND CDCLIENTE <> V_CDCLIEPRIN;

      V_CDCLIEPRIN := 'P';
   END IF;

   COMMIT;
END;
/

-- VERSAO --
BEGIN ATUVEROBJ('IMPORTA_CLIENFILIAL_SAP','5.42.XXX'); END;
/