CREATE OR REPLACE PROCEDURE IMPORTA_CONSUMIDOR(
  P_boCdIdConsumid    IN VARCHAR2,
  P_boCdExConsumid    IN VARCHAR2,
  P_boNmConsumidor    IN VARCHAR2,
  P_boVrAviDebCons    IN VARCHAR2,
  P_boVrLimDebCons    IN VARCHAR2,
  P_boDsEndeCons      IN VARCHAR2,
  P_boNmBairCons      IN VARCHAR2,
  P_boNrCepCons       IN VARCHAR2,
  P_boNrTele2Cons     IN VARCHAR2,
  P_boNrTeleCons      IN VARCHAR2,
  P_boNrRamal1Cons    IN VARCHAR2,
  P_boNrRamal2Cons    IN VARCHAR2,
  P_boNrFaxCons       IN VARCHAR2,
  P_boDsEmailCons     IN VARCHAR2,
  P_boVrPeSubCons     IN VARCHAR2,
  P_boVrMeSubCons     IN VARCHAR2,
  P_boCdTipoCons      IN VARCHAR2,
  P_boCdCCusClie      IN VARCHAR2,
  P_boIdVerSaldCon    IN VARCHAR2,
  P_boVrDiaSubCons    IN VARCHAR2,
  P_boCdVendedor      IN VARCHAR2,
  P_boNrRgConsumid    IN VARCHAR2,
  P_boCdSenhaCons     IN VARCHAR2,
  P_boNrCpfRespCon    IN VARCHAR2,
  P_boNmRespCons      IN VARCHAR2,
  P_boNmLoginCons     IN VARCHAR2,
  P_boDtNasCCons      IN VARCHAR2,
  P_boVrMaxDebCons    IN VARCHAR2,
  P_boVrLimCredCons   IN VARCHAR2,
  P_boVrAviCredCons   IN VARCHAR2,
  P_boVrMaxCredCons   IN VARCHAR2,
  P_boCdSenhaSite     IN VARCHAR2,
  P_boNrAcessoSite    IN VARCHAR2,
  P_boCdPais          IN VARCHAR2,
  P_boSgEstado        IN VARCHAR2,
  P_boCdMunicipio     IN VARCHAR2,
  P_boIdTpVendaCons   IN VARCHAR2,
  P_boNrEndeCons      IN VARCHAR2,
  P_boNrCelularCons   IN VARCHAR2,
  P_boDsComplEndeCons IN VARCHAR2,
  P_boIdImpCpfCupom   IN VARCHAR2,
  P_boIdPerConsProdEx IN VARCHAR2,
  P_boIdCadConfLibCon IN VARCHAR2,
  P_boDsReferEndeCons IN VARCHAR2,
  P_CDFILIAL          IN VARCHAR2,
  P_CDCLIENTE         IN VARCHAR2,
  P_INATIVACONS       IN VARCHAR2,
  P_MODOFILIAL        IN VARCHAR2,
  P_boIdTpSeLManha    IN VARCHAR2,
  P_boIdTpSeLTarde    IN VARCHAR2,
  P_boIdTpSeAlmoco    IN VARCHAR2,
  P_boVrSubsBasico    IN VARCHAR2,
  P_boIdBloqSaldFs    IN VARCHAR2,
  P_boCdFamiliSaldImp IN VARCHAR2,
  P_boVrSaldoCredImp  IN VARCHAR2,
  P_boVrMaxConsMensal IN VARCHAR2,
  P_boCdBairro          IN VARCHAR2
)
AS
  V_CdIdConsumid_INS    VARCHAR2(21);
  V_CdExConsumid_INS    VARCHAR2(21);
  V_NmConsumidor_INS    VARCHAR2(50);
  V_VrAviDebCons_INS    FLOAT;
  V_VrLimDebCons_INS    FLOAT;
  V_DsEndeCons_INS      VARCHAR2(50);
  V_NmBairCons_INS      VARCHAR2(30);
  V_NrCepCons_INS       VARCHAR2(15);
  V_NrTele2Cons_INS     VARCHAR2(25);
  V_NrTeleCons_INS      VARCHAR2(25);
  V_NrRamal1Cons_INS    VARCHAR2(4);
  V_NrRamal2Cons_INS    VARCHAR2(4);
  V_NrFaxCons_INS       VARCHAR2(25);
  V_DsEmailCons_INS     VARCHAR2(50);
  V_VrPeSubCons_INS     FLOAT;
  V_VrMeSubCons_INS     FLOAT;
  V_CdTipoCons_INS      VARCHAR2(2);
  V_CdCCusClie_INS      VARCHAR2(10);
  V_IdVerSaldCon_INS    VARCHAR2(1);
  V_VrDiaSubCons_INS    FLOAT;
  V_NmLoginCons_INS     VARCHAR2(200);
  V_CdSenhaSite_INS     VARCHAR2(40);
  V_CdVendedor_INS      VARCHAR2(4);
  V_NrRgConsumid_INS    VARCHAR2(20);
  V_CdSenhaCons_INS     VARCHAR2(40);
  V_NrCpfRespCon_INS    VARCHAR2(11);
  V_NmRespCons_INS      VARCHAR2(50);
  V_DtNasCCons_INS      DATE;
  V_VrMaxDebCons_INS    FLOAT;
  V_VrLimCredCons_INS   FLOAT;
  V_VrAviCredCons_INS   FLOAT;
  V_VrMaxCredCons_INS   FLOAT;
  V_NrAcessoSite_INS    FLOAT;
  V_CdPais_INS          VARCHAR2(4);
  V_SgEstado_INS        VARCHAR2(2);
  V_CdMunicipio_INS     VARCHAR2(4);
  V_IdTpVendaCons_INS   VARCHAR2(1);
  V_NrEndeCons_INS      VARCHAR2(5);
  V_NrCelularCons_INS   VARCHAR2(25);
  V_DsComplEndeCons_INS VARCHAR2(100);
  V_IdImpCpfCupom_INS   VARCHAR2(1);
  V_IdPerConsProdEx_INS VARCHAR2(1);
  V_IdCadConfLibCon_INS VARCHAR2(1);
  V_DsReferEndeCons_INS VARCHAR2(2000);
  V_IdTpSeLManha_INS    VARCHAR2(1);
  V_IdTpSeLTarde_INS    VARCHAR2(1);
  V_IdTpSeAlmoco_INS    VARCHAR2(1);
  V_VrSubsBasico_INS    FLOAT;
  V_IdBloqSaldFs_INS    VARCHAR2(1);
  V_CdFamiliSaldImp_INS VARCHAR2(3);
  V_VrSaldoCredImp_INS  FLOAT;
  V_VrMaxConsMensal_INS FLOAT;
  V_CdBairro_INS          VARCHAR2(10);

  V_CdIdConsumid_ATUAL    VARCHAR2(21);
  V_CdExConsumid_ATUAL    VARCHAR2(21);
  V_NmConsumidor_ATUAL    VARCHAR2(50);
  V_VrAviDebCons_ATUAL    FLOAT;
  V_VrLimDebCons_ATUAL    FLOAT;
  V_DsEndeCons_ATUAL      VARCHAR2(50);
  V_NmBairCons_ATUAL      VARCHAR2(30);
  V_NrCepCons_ATUAL       VARCHAR2(15);
  V_NrTele2Cons_ATUAL     VARCHAR2(25);
  V_NrTeleCons_ATUAL      VARCHAR2(25);
  V_NrRamal1Cons_ATUAL    VARCHAR2(4);
  V_NrRamal2Cons_ATUAL    VARCHAR2(4);
  V_NrFaxCons_ATUAL       VARCHAR2(25);
  V_DsEmailCons_ATUAL     VARCHAR2(50);
  V_VrPeSubCons_ATUAL     FLOAT;
  V_VrMeSubCons_ATUAL     FLOAT;
  V_CdTipoCons_ATUAL      VARCHAR2(2);
  V_CdCCusClie_ATUAL      VARCHAR2(10);
  V_IdVerSaldCon_ATUAL    VARCHAR2(1);
  V_VrDiaSubCons_ATUAL    FLOAT;
  V_NmLoginCons_ATUAL     VARCHAR2(200);
  V_CdSenhaSite_ATUAL     VARCHAR2(40);
  V_CdVendedor_ATUAL      VARCHAR2(4);
  V_NrRgConsumid_ATUAL    VARCHAR2(20);
  V_CdSenhaCons_ATUAL     VARCHAR2(40);
  V_NrCpfRespCon_ATUAL    VARCHAR2(11);
  V_NmRespCons_ATUAL      VARCHAR2(50);
  V_DtNasCCons_ATUAL      DATE;
  V_VrMaxDebCons_ATUAL    FLOAT;
  V_VrLimCredCons_ATUAL   FLOAT;
  V_VrAviCredCons_ATUAL   FLOAT;
  V_VrMaxCredCons_ATUAL   FLOAT;
  V_NrAcessoSite_ATUAL    FLOAT;
  V_CdPais_ATUAL          VARCHAR2(4);
  V_SgEstado_ATUAL        VARCHAR2(2);
  V_CdMunicipio_ATUAL     VARCHAR2(4);
  V_IdTpVendaCons_ATUAL   VARCHAR2(1);
  V_NrEndeCons_ATUAL      VARCHAR2(5);
  V_NrCelularCons_ATUAL   VARCHAR2(25);
  V_DsComplEndeCons_ATUAL VARCHAR2(100);
  V_IdImpCpfCupom_ATUAL   VARCHAR2(1);
  V_IdPerConsProdEx_ATUAL VARCHAR2(1);
  V_IdCadConfLibCon_ATUAL VARCHAR2(1);
  V_DsReferEndeCons_ATUAL VARCHAR2(2000);
  V_IdTpSeLManha_ATUAL    VARCHAR2(1);
  V_IdTpSeLTarde_ATUAL    VARCHAR2(1);
  V_IdTpSeAlmoco_ATUAL    VARCHAR2(1);
  V_VrSubsBasico_ATUAL    FLOAT;
  V_IdBloqSaldFs_ATUAL    VARCHAR2(1);
  V_CdFamiliSaldImp_ATUAL VARCHAR2(3);
  V_VrSaldoCredImp_ATUAL  FLOAT;
  V_VrMaxConsMensal_ATUAL FLOAT;
  V_CdBairro_ATUAL          VARCHAR2(10);
  
  V_CDCLIENTE    VARCHAR2(21);
  V_CDCONSUMIDOR VARCHAR2(21);
  V_CLIENTE_AUX  VARCHAR2(21);
  V_CENTRO_CUSTO VARCHAR2(10);
  V_CONTADOR     NUMERIC;
  V_NRREG        NUMERIC;
  V_NRREG_AUX    NUMERIC;
  V_INATIVACONS  VARCHAR2(1);
  V_COUNT NUMBER;
  
BEGIN
   FOR CUR_CLIENTE IN (
      SELECT DISTINCT CDCLIENTETRAB
      FROM   TRABCONSUMIDOR) LOOP
      FOR CUR_CENTRO IN (
         SELECT DISTINCT CDCCUSCLIETRAB
         FROM   TRABCONSUMIDOR
         WHERE  CDCLIENTETRAB = CUR_CLIENTE.CDCLIENTETRAB
           AND  CDCCUSCLIETRAB NOT IN (SELECT CDCCUSCLIE FROM CCUSCLIE WHERE CDCLIENTE = CUR_CLIENTE.CDCLIENTETRAB)) LOOP
         INSERT INTO CCUSCLIE
          (CDCLIENTE,CDCCUSCLIE,NMCCUSCLIE,DTULTATUCUSC)
         VALUES
          (CUR_CLIENTE.CDCLIENTETRAB,CUR_CENTRO.CDCCUSCLIETRAB,'IMP.AUTOMATICA',SYSDATE);
      END LOOP;
	END LOOP;

   FOR CUR_TIPOCONS IN (
      SELECT DISTINCT CDTIPOCONSTRAB
      FROM   TRABCONSUMIDOR
      WHERE  CDTIPOCONSTRAB NOT IN (SELECT CDTIPOCONS FROM TIPOCONS)) LOOP
      INSERT INTO TIPOCONS
       (CDTIPOCONS,NMTIPOCONS,DTULTATUTCON,IDREGDUPCATR)
      VALUES
       (CUR_TIPOCONS.CDTIPOCONSTRAB,'IMP.AUTOMATICA',SYSDATE,'N');
   END LOOP;

   V_CONTADOR := 0;
   FOR CUR_CONS IN (
      SELECT CDCLIENTETRAB       , CDCONSUMIDORTRAB    , CDIDCONSUMIDTRAB  , CDEXCONSUMIDTRAB    ,
             NMCONSUMIDORTRAB    , VRAVIDEBCONSTRAB    , VRLIMDEBCONSTRAB  , DSENDECONSTRAB      ,
             NMBAIRCONSTRAB      , NRCEPCONSTRAB       , NRTELE2CONSTRAB   , NRTELECONSTRAB      ,
             NRRAMAL1CONSTRAB    , NRRAMAL2CONSTRAB    , NRFAXCONSTRAB     , DSEMAILCONSTRAB     ,
             VRPESUBCONSTRAB     , VRMESUBCONSTRAB     , CDTIPOCONSTRAB    , CDCCUSCLIETRAB      ,
             IDVERSALDCONTRAB    , VRDIASUBCONSTRAB    , NMLOGINCONSTRAB   , CDSENHASITETRAB     ,
             CDVENDEDORTRAB      , NRRGCONSUMIDTRAB    , CDSENHACONSTRAB   , NRCPFRESPCONTRAB    ,
             NMRESPCONSTRAB      , DTNASCCONSTRAB      , VRMAXDEBCONSTRAB  , VRLIMCREDCONSTRAB   ,
             VRAVICREDCONSTRAB   , VRMAXCREDCONSTRAB   , NRACESSOSITETRAB  , CDPAISTRAB          ,
             SGESTADOTRAB        , CDMUNICIPIOTRAB     , IDTPVENDACONSTRAB , NRENDECONSTRAB      ,
             NRCELULARCONSTRAB   , DSCOMPLENDECONSTRAB , IDIMPCPFCUPOMTRAB , IDPERCONSPRODEXTRAB ,
             IDCADCONFLIBCONTRAB , DSREFERENDECONSTRAB , IDTPSELMANHATRAB  , IDTPSEALMOCOTRAB    ,
             IDTPSELTARDETRAB    , VRSUBSBASICOTRAB    , IDBLOQSALDFSTRAB  , CDFAMILISALDIMPTRAB ,
             VRSALDOCREDIMPTRAB  , VRMAXCONSMENSALTRAB,  CDBAIRROTRAB
      FROM   TRABCONSUMIDOR) LOOP

      SELECT COUNT (*)
        INTO V_NRREG
        FROM CONSUMIDOR
       WHERE CDCLIENTE    = CUR_CONS.CDCLIENTETRAB
         AND CDCONSUMIDOR = CUR_CONS.CDCONSUMIDORTRAB;

      V_COUNT := 0;
      BEGIN
         SELECT COUNT(*)
           INTO V_COUNT
           FROM CONSUMIDOR
          WHERE CDIDCONSUMID = CUR_CONS.CDIDCONSUMIDTRAB;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            V_COUNT := 0;
      END;
      
      IF V_COUNT > 0 AND P_INATIVACONS = 'S' THEN
         UPDATE CONSUMIDOR C
            SET IDSITCONSUMI   = '2'
          WHERE CDIDCONSUMID = CUR_CONS.CDIDCONSUMIDTRAB
            AND IDCRACHAMESTRE = 'N'
            AND EXISTS (SELECT 1
                          FROM TRABCONSUMIDOR T
                         WHERE T.CDCLIENTETRAB = C.CDCLIENTE);
      END IF;

      IF V_NRREG > 0 THEN
         SELECT CdIdConsumid  , CdExConsumid    , NmConsumidor    , VrAviDebCons    ,
                VrLimDebCons  , DsEndeCons      , NmBairCons      , NrCepCons       ,
                NrTele2Cons   , NrTeleCons      , NrRamal1Cons    , NrRamal2Cons    ,
                NrFaxCons     , DsEmailCons     , VrPeSubCons     , VrMeSubCons     ,
                CdTipoCons    , CdCCusClie      , IdVerSaldCon    , VrDiaSubCons    ,
                NmLoginCons   , CdSenhaSite     , CdVendedor      , NrRgConsumid    ,
                CdSenhaCons   , NrCpfRespCon    , NmRespCons      , DtNascCons      ,
                VrMaxDebCons  , VrLimCredCons   , VrAviCredCons   , VrMaxCredCons   ,
                NrAcessoSite  , CdPais          , SgEstado        , CdMunicipio     ,
                IdTpVendaCons , NrEndeCons      , NrCelularCons   , DsComplEndeCons ,
                IdImpCpfCupom , IdPerConsProdEx , IdCadConfLibCon , DsReferEndeCons ,
                IdTpSeLManha  , IdTpSeLTarde    , IdTpSeAlmoco    , VrSubsBasico    ,
                IdBloqSaldFs  , CdFamiliSaldImp , VrSaldoCredImp  , VrMaxConsMensal,
	 CdBairro
         INTO   V_CdIdConsumid_ATUAL  , V_CdExConsumid_ATUAL    , V_NmConsumidor_ATUAL    , V_VrAviDebCons_ATUAL    ,
                V_VrLimDebCons_ATUAL  , V_DsEndeCons_ATUAL      , V_NmBairCons_ATUAL      , V_NrCepCons_ATUAL       ,
                V_NrTele2Cons_ATUAL   , V_NrTeleCons_ATUAL      , V_NrRamal1Cons_ATUAL    , V_NrRamal2Cons_ATUAL    ,
                V_NrFaxCons_ATUAL     , V_DsEmailCons_ATUAL     , V_VrPeSubCons_ATUAL     , V_VrMeSubCons_ATUAL     ,
                V_CdTipoCons_ATUAL    , V_CdCCusClie_ATUAL      , V_IdVerSaldCon_ATUAL    , V_VrDiaSubCons_ATUAL    ,
                V_NmLoginCons_ATUAL   , V_CdSenhaSite_ATUAL     , V_CdVendedor_ATUAL      , V_NrRgConsumid_ATUAL    ,
                V_CdSenhaCons_ATUAL   , V_NrCpfRespCon_ATUAL    , V_NmRespCons_ATUAL      , V_DtNascCons_ATUAL      ,
                V_VrMaxDebCons_ATUAL  , V_VrLimCredCons_ATUAL   , V_VrAviCredCons_ATUAL   , V_VrMaxCredCons_ATUAL   ,
                V_NrAcessoSite_ATUAL  , V_CdPais_ATUAL          , V_SgEstado_ATUAL        , V_CdMunicipio_ATUAL     ,
                V_IdTpVendaCons_ATUAL , V_NrEndeCons_ATUAL      , V_NrCelularCons_ATUAL   , V_DsComplEndeCons_ATUAL ,
                V_IdImpCpfCupom_ATUAL , V_IdPerConsProdEx_ATUAL , V_IdCadConfLibCon_ATUAL , V_DsReferEndeCons_ATUAL ,
                V_IdTpSeLManha_ATUAL  , V_IdTpSeLTarde_ATUAL    , V_IdTpSeAlmoco_ATUAL    , V_VrSubsBasico_ATUAL    ,
                V_IdBloqSaldFs_ATUAL  , V_CdFamiliSaldImp_ATUAL , V_VrSaldoCredImp_ATUAL  , V_VrMaxConsMensal_ATUAL,
	 V_CdBairro_ATUAL
         FROM CONSUMIDOR
         WHERE CDCLIENTE    = CUR_CONS.CDCLIENTETRAB
           AND CDCONSUMIDOR = CUR_CONS.CDCONSUMIDORTRAB;

         IF (P_boCdIdConsumid = 'S') THEN
            IF (CUR_CONS.CDIDCONSUMIDTRAB IS NULL) THEN
                V_CDIDCONSUMID_INS := V_CDIDCONSUMID_ATUAL;
             ELSE
                V_CDIDCONSUMID_INS := CUR_CONS.CDIDCONSUMIDTRAB;
             END IF;
         ELSE
            V_CDIDCONSUMID_INS := V_CDIDCONSUMID_ATUAL;
         END IF;

         IF (P_boCdExConsumid = 'S') THEN
            V_CdExConsumid_INS := CUR_CONS.CdExConsumidTRAB;
         ELSE
            V_CdExConsumid_INS := V_CdExConsumid_ATUAL;
         END IF;

         IF (P_boNmConsumidor = 'S') THEN
            V_NmConsumidor_INS := CUR_CONS.NmConsumidorTRAB;
         ELSE
            V_NmConsumidor_INS := V_NmConsumidor_ATUAL;
         END IF;

         IF (P_boVrAviDebCons = 'S') THEN
            V_VrAviDebCons_INS := CUR_CONS.VrAviDebConsTRAB;
         ELSE
            V_VrAviDebCons_INS := V_VrAviDebCons_ATUAL;
         END IF;

         IF (P_boVrLimDebCons = 'S') THEN
            V_VrLimDebCons_INS := CUR_CONS.VrLimDebConsTRAB;
         ELSE
            V_VrLimDebCons_INS := V_VrLimDebCons_ATUAL;
         END IF;

         IF (P_boDsEndeCons = 'S') THEN
            V_DsEndeCons_INS := CUR_CONS.DsEndeConsTRAB;
         ELSE
            V_DsEndeCons_INS := V_DsEndeCons_ATUAL;
         END IF;

         IF (P_boNmBairCons = 'S') THEN
            V_NmBairCons_INS := CUR_CONS.NmBairConsTRAB;
         ELSE
            V_NmBairCons_INS := V_NmBairCons_ATUAL;
         END IF;

         IF (P_boNrCepCons = 'S') THEN
            V_NrCepCons_INS := CUR_CONS.NrCepConsTRAB;
         ELSE
            V_NrCepCons_INS := V_NrCepCons_ATUAL;
         END IF;

         IF (P_boNrTele2Cons = 'S') THEN
            V_NrTele2Cons_INS := CUR_CONS.NrTele2ConsTRAB;
         ELSE
            V_NrTele2Cons_INS := V_NrTele2Cons_ATUAL;
         END IF;

         IF (P_boNrTeleCons = 'S') THEN
            V_NrTeleCons_INS := CUR_CONS.NrTeleConsTRAB;
         ELSE
            V_NrTeleCons_INS := V_NrTeleCons_ATUAL;
         END IF;

         IF (P_boNrRamal1Cons = 'S') THEN
            V_NrRamal1Cons_INS := CUR_CONS.NrRamal1ConsTRAB;
         ELSE
            V_NrRamal1Cons_INS := V_NrRamal1Cons_ATUAL;
         END IF;

         IF (P_boNrRamal2Cons = 'S') THEN
            V_NrRamal2Cons_INS := CUR_CONS.NrRamal2ConsTRAB;
         ELSE
            V_NrRamal2Cons_INS := V_NrRamal2Cons_ATUAL;
         END IF;
         IF (P_boNrFaxCons = 'S') THEN
            V_NrFaxCons_INS := CUR_CONS.NrFaxConsTRAB;
         ELSE
            V_NrFaxCons_INS := V_NrFaxCons_ATUAL;
         END IF;
         IF (P_boDsEmailCons = 'S') THEN
            V_DsEmailCons_INS := CUR_CONS.DsEmailConsTRAB;
         ELSE
            V_DsEmailCons_INS := V_DsEmailCons_ATUAL;
         END IF;
         IF (P_boVrPeSubCons = 'S') THEN
            V_VrPeSubCons_INS := CUR_CONS.VrPeSubConsTRAB;
         ELSE
            V_VrPeSubCons_INS := V_VrPeSubCons_ATUAL;
         END IF;
         IF (P_boVrMeSubCons = 'S') THEN
            V_VrMeSubCons_INS := CUR_CONS.VrMeSubConsTRAB;
         ELSE
            V_VrMeSubCons_INS := V_VrMeSubCons_ATUAL;
         END IF;
         IF (P_boVrDiaSubCons = 'S') THEN
            V_VrDiaSubCons_INS := CUR_CONS.VrDiaSubConsTRAB;
         ELSE
            V_VrDiaSubCons_INS := V_VrDiaSubCons_ATUAL;
         END IF;
         IF (P_boCdTipoCons = 'S') THEN
            V_CdTipoCons_INS := CUR_CONS.CdTipoConsTRAB;
         ELSE
            V_CdTipoCons_INS := V_CdTipoCons_ATUAL;
         END IF;
         IF (P_boCdCCusClie = 'S') THEN
            V_CdCCusClie_INS := CUR_CONS.CdCCusClieTRAB;
         ELSE
            V_CdCCusClie_INS := V_CdCCusClie_ATUAL;
         END IF;
         IF (P_boIdVerSaldCon = 'S') THEN
            V_IdVerSaldCon_INS := CUR_CONS.IdVerSaldConTRAB;
         ELSE
            V_IdVerSaldCon_INS := V_IdVerSaldCon_ATUAL;
         END IF;
         IF (P_boNmLoginCons = 'S') THEN
            V_NmLoginCons_INS := CUR_CONS.NmLoginConsTRAB;
         ELSE
            V_NmLoginCons_INS := V_NmLoginCons_ATUAL;
         END IF;
         IF (P_boCdSenhaSite = 'S') THEN
            V_CdSenhaSite_INS := CUR_CONS.CdSenhaSiteTRAB;
         ELSE
            V_CdSenhaSite_INS := V_CdSenhaSite_ATUAL;
         END IF;
         IF (P_boCdVendedor = 'S') THEN
            V_CdVendedor_INS := CUR_CONS.CdVendedorTRAB;
         ELSE
            V_CdVendedor_INS := V_CdVendedor_ATUAL;
         END IF;
         IF (P_boNrRgConsumid = 'S') THEN
            V_NrRgConsumid_INS := CUR_CONS.NrRgConsumidTRAB;
         ELSE
            V_NrRgConsumid_INS := V_NrRgConsumid_ATUAL;
         END IF;
         IF (P_boCdSenhaCons = 'S') THEN
            V_CdSenhaCons_INS := CUR_CONS.CdSenhaConsTRAB;
         ELSE
            V_CdSenhaCons_INS := V_CdSenhaCons_ATUAL;
         END IF;
         IF (P_boNrCpfRespCon = 'S') THEN
            V_NrCpfRespCon_INS := CUR_CONS.NrCpfRespConTRAB;
         ELSE
            V_NrCpfRespCon_INS := V_NrCpfRespCon_ATUAL;
         END IF;
         IF (P_boNmRespCons = 'S') THEN
            V_NmRespCons_INS := CUR_CONS.NmRespConsTRAB;
         ELSE
            V_NmRespCons_INS := V_NmRespCons_ATUAL;
         END IF;
         IF (P_boDtNasCCons = 'S') THEN
            V_DtNasCCons_INS := CUR_CONS.DtNasCConsTRAB;
         ELSE
            V_DtNasCCons_INS := V_DtNasCCons_ATUAL;
         END IF;
         IF (P_boVrMaxDebCons = 'S') THEN
            V_VrMaxDebCons_INS := CUR_CONS.VrMaxDebConsTRAB;
         ELSE
            V_VrMaxDebCons_INS := V_VrMaxDebCons_ATUAL;
         END IF;
         IF (P_boVrLimCredCons = 'S') THEN
            V_VrLimCredCons_INS := CUR_CONS.VrLimCredConsTRAB;
         ELSE
            V_VrLimCredCons_INS := V_VrLimCredCons_ATUAL;
         END IF;
         IF (P_boVrAviCredCons = 'S') THEN
            V_VrAviCredCons_INS := CUR_CONS.VrAviCredConsTRAB;
         ELSE
            V_VrAviCredCons_INS := V_VrAviCredCons_ATUAL;
         END IF;
         IF (P_boVrMaxCredCons = 'S') THEN
            V_VrMaxCredCons_INS := CUR_CONS.VrMaxCredConsTRAB;
         ELSE
            V_VrMaxCredCons_INS := V_VrMaxCredCons_ATUAL;
         END IF;
         IF (P_boNrAcessoSite = 'S') THEN
            V_NrAcessoSite_INS := CUR_CONS.NrAcessoSiteTRAB;
         ELSE
            V_NrAcessoSite_INS := V_NrAcessoSite_ATUAL;
         END IF;
         IF (P_boCdPais = 'S') THEN
            V_CdPais_INS := CUR_CONS.CdPaisTRAB;
         ELSE
            V_CdPais_INS := V_CdPais_ATUAL;
         END IF;
         IF (P_boSgEstado = 'S') THEN
            V_SgEstado_INS := CUR_CONS.SgEstadoTRAB;
         ELSE
            V_SgEstado_INS := V_SgEstado_ATUAL;
         END IF;
         IF (P_boCdMunicipio = 'S') THEN
            V_CdMunicipio_INS := CUR_CONS.CdMunicipioTRAB;
         ELSE
            V_CdMunicipio_INS := V_CdMunicipio_ATUAL;
         END IF;
         IF (P_boIdTpVendaCons = 'S') THEN
            V_IdTpVendaCons_INS := CUR_CONS.IdTpVendaConsTRAB;
         ELSE
            V_IdTpVendaCons_INS := V_IdTpVendaCons_ATUAL;
         END IF;
         IF (P_boNrEndeCons = 'S') THEN
            V_NrEndeCons_INS := CUR_CONS.NrEndeConsTRAB;
         ELSE
            V_NrEndeCons_INS := V_NrEndeCons_ATUAL;
         END IF;
         IF (P_boNrCelularCons = 'S') THEN
            V_NrCelularCons_INS := CUR_CONS.NrCelularConsTRAB;
         ELSE
            V_NrCelularCons_INS := V_NrCelularCons_ATUAL;
         END IF;        
         IF (P_boDsComplEndeCons = 'S') THEN
            V_DsComplEndeCons_INS := CUR_CONS.DSCOMPLENDECONSTRAB;
         ELSE
            V_DsComplEndeCons_INS := V_DsComplEndeCons_ATUAL;
         END IF;             
         IF (P_boIdImpCpfCupom = 'S') THEN
            V_IdImpCpfCupom_INS := CUR_CONS.IDIMPCPFCUPOMTRAB;
         ELSE
            V_IdImpCpfCupom_INS := V_IdImpCpfCupom_ATUAL;
         END IF;            
         IF (P_boIdPerConsProdEx = 'S') THEN
            V_IdPerConsProdEx_INS := CUR_CONS.IDPERCONSPRODEXTRAB;
         ELSE
            V_IdPerConsProdEx_INS := V_IdPerConsProdEx_ATUAL;
         END IF;            
         IF (P_boIdCadConfLibCon = 'S') THEN
            V_IdCadConfLibCon_INS := CUR_CONS.IDCADCONFLIBCONTRAB;
         ELSE
            V_IdCadConfLibCon_INS := V_IdCadConfLibCon_ATUAL;
         END IF;
         IF (P_boDsReferEndeCons = 'S') THEN
            V_DsReferEndeCons_INS := CUR_CONS.DSREFERENDECONSTRAB ;
         ELSE
            V_DsReferEndeCons_INS := V_DsReferEndeCons_ATUAL;
         END IF;
         IF (P_boIdTpSeLManha = 'S') THEN
            V_IdTpSeLManha_INS := CUR_CONS.IDTPSELMANHATRAB;
         ELSE
            V_IdTpSeLManha_INS := V_IdTpSeLManha_ATUAL;
         END IF;
         IF (P_boIdTpSeLTarde = 'S') THEN
            V_IdTpSeLTarde_INS := CUR_CONS.IDTPSELTARDETRAB;
         ELSE
            V_IdTpSeLTarde_INS := V_IdTpSeLTarde_ATUAL;
         END IF;
         IF (P_boIdTpSeAlmoco = 'S') THEN
            V_IdTpSeAlmoco_INS := CUR_CONS.IDTPSEALMOCOTRAB;
         ELSE
            V_IdTpSeAlmoco_INS := V_IdTpSeAlmoco_ATUAL;
         END IF;
		 IF (P_boVrSubsBasico = 'S') THEN
            V_VrSubsBasico_INS := CUR_CONS.VRSUBSBASICOTRAB;
         ELSE
            V_VrSubsBasico_INS := V_VrSubsBasico_ATUAL;
         END IF;
         IF (P_boIdBloqSaldFs = 'S') THEN
            V_IdBloqSaldFs_INS := CUR_CONS.IDBLOQSALDFSTRAB;
         ELSE
            V_IdBloqSaldFs_INS := V_IdBloqSaldFs_ATUAL;
         END IF;
		 IF (P_boVrMaxConsMensal = 'S') THEN
            V_VrMaxConsMensal_INS := CUR_CONS.VRMAXCONSMENSALTRAB;
         ELSE
            V_VrMaxConsMensal_INS := V_VrMaxConsMensal_ATUAL;
         END IF;   
	    IF (P_boCdFamiliSaldImp = 'S') THEN
            V_CdFamiliSaldImp_INS := CUR_CONS.CDFAMILISALDIMPTRAB;
         ELSE
            V_CdFamiliSaldImp_INS := V_CdFamiliSaldImp_ATUAL;
         END IF;
         IF (P_boVrSaldoCredImp = 'S') THEN
            V_VrSaldoCredImp_INS := CUR_CONS.VRSALDOCREDIMPTRAB;
         ELSE
            V_VrSaldoCredImp_INS := V_VrSaldoCredImp_ATUAL;
         END IF;
         IF (P_boCdBairro = 'S') THEN
            V_CdBairro_INS := CUR_CONS.CDBAIRROTRAB;
         END IF;
         UPDATE CONSUMIDOR
         SET VRMESUBCONS     = V_VRMESUBCONS_INS,
             VRPESUBCONS     = V_VRPESUBCONS_INS,
             NMCONSUMIDOR    = V_NMCONSUMIDOR_INS,
             CDIDCONSUMID    = V_CDIDCONSUMID_INS,
             CDEXCONSUMID    = V_CDEXCONSUMID_INS,
             VRAVIDEBCONS    = V_VRAVIDEBCONS_INS,
             VRLIMDEBCONS    = V_VRLIMDEBCONS_INS,
             DSENDECONS      = V_DSENDECONS_INS,
             NMBAIRCONS      = V_NMBAIRCONS_INS,
             NRCEPCONS       = V_NRCEPCONS_INS,
             NRTELE2CONS     = V_NRTELE2CONS_INS,
             NRTELECONS      = V_NRTELECONS_INS,
             NRRAMAL1CONS    = V_NRRAMAL1CONS_INS,
             NRRAMAL2CONS    = V_NRRAMAL2CONS_INS,
             NRFAXCONS       = V_NRFAXCONS_INS,
             DSEMAILCONS     = V_DSEMAILCONS_INS,
             CDTIPOCONS      = V_CDTIPOCONS_INS,
             CDCCUSCLIE      = V_CDCCUSCLIE_INS,
             IDSITCONSUMI    = '1',
             IDVERSALDCON    = V_IDVERSALDCON_INS,
             DTULTATUCSUM    = SYSDATE,
             VRDIASUBCONS    = V_VRDIASUBCONS_INS,
             CDVENDEDOR      = V_CDVENDEDOR_INS,
             NRRGCONSUMID    = V_NRRGCONSUMID_INS,
             CDSENHACONS     = V_CDSENHACONS_INS,
             NRCPFRESPCON    = V_NRCPFRESPCON_INS,
             NMRESPCONS      = V_NMRESPCONS_INS,
             NMLOGINCONS     = V_NMLOGINCONS_INS,
             DTNASCCONS      = V_DTNASCCONS_INS,
             VRMAXDEBCONS    = V_VRMAXDEBCONS_INS,
             VRLIMCREDCONS   = V_VRLIMCREDCONS_INS,
             VRAVICREDCONS   = V_VRAVICREDCONS_INS,
             VRMAXCREDCONS   = V_VRMAXCREDCONS_INS,
             CDSENHASITE     = V_CDSENHASITE_INS,
             NRACESSOSITE    = V_NRACESSOSITE_INS,
             CDPAIS          = V_CDPAIS_INS,
             SGESTADO        = V_SGESTADO_INS,
             CDMUNICIPIO     = V_CDMUNICIPIO_INS,
             IDTPVENDACONS   = V_IDTPVENDACONS_INS,
             NRENDECONS      = V_NRENDECONS_INS,
             NRCELULARCONS   = V_NRCELULARCONS_INS,
             DSCOMPLENDECONS = V_DSCOMPLENDECONS_INS,
             IDIMPCPFCUPOM   = V_IDIMPCPFCUPOM_INS,
             IDPERCONSPRODEX = V_IDPERCONSPRODEX_INS,
             IDCADCONFLIBCON = V_IDCADCONFLIBCON_INS,
             DSREFERENDECONS = V_DSREFERENDECONS_INS,
             IDTPSELMANHA    = V_IDTPSELMANHA_INS,
             IDTPSELTARDE    = V_IDTPSELTARDE_INS,
             IDTPSEALMOCO    = V_IDTPSEALMOCO_INS,
             VRSUBSBASICO    = V_VRSUBSBASICO_INS,
             IDBLOQSALDFS    = V_IDBLOQSALDFS_INS,
	         CDFAMILISALDIMP = V_CDFAMILISALDIMP_INS,
             VRSALDOCREDIMP  = V_VRSALDOCREDIMP_INS,
			 VRMAXCONSMENSAL = V_VRMAXCONSMENSAL_INS,
             CDBAIRRO        = V_CDBAIRRO_INS
         WHERE CDCLIENTE    = CUR_CONS.CDCLIENTETRAB
           AND CDCONSUMIDOR = CUR_CONS.CDCONSUMIDORTRAB;
      ELSE
         INSERT INTO CONSUMIDOR
            (CDCLIENTE       , CDCONSUMIDOR    , CDIDCONSUMID  , CDEXCONSUMID    ,
             NMCONSUMIDOR    , VRAVIDEBCONS    , VRLIMDEBCONS  , DSENDECONS      ,
             NMBAIRCONS      , NRCEPCONS       , NRTELE2CONS   , NRTELECONS      ,
             NRRAMAL1CONS    , NRRAMAL2CONS    , NRFAXCONS     , DSEMAILCONS     ,
             VRPESUBCONS     , VRMESUBCONS     , CDTIPOCONS    , CDCCUSCLIE      ,
             IDVERSALDCON    , VRDIASUBCONS    , CDVENDEDOR    , NRRGCONSUMID    ,
             CDSENHACONS     , NRCPFRESPCON    , NMRESPCONS    , NMLOGINCONS     ,
             DTNASCCONS      , VRMAXDEBCONS    , VRLIMCREDCONS , VRAVICREDCONS   ,
             VRMAXCREDCONS   , CDSENHASITE     , NRACESSOSITE  , CDPAIS          ,
             SGESTADO        , CDMUNICIPIO     , IDTPVENDACONS , NRENDECONS      ,
             NRCELULARCONS   , DSCOMPLENDECONS , IDIMPCPFCUPOM , IDPERCONSPRODEX ,
             IDCADCONFLIBCON , DSREFERENDECONS , IDTPSELMANHA  , IDTPSELTARDE    ,
             IDTPSEALMOCO    , VRSUBSBASICO    , IDBLOQSALDFS  , CDFAMILISALDIMP ,
             VRSALDOCREDIMP  , VRMAXCONSMENSAL, CDBAIRRO )
         VALUES
            (CUR_CONS.CDCLIENTETRAB       , CUR_CONS.CDCONSUMIDORTRAB    , CUR_CONS.CDIDCONSUMIDTRAB  , CUR_CONS.CDEXCONSUMIDTRAB    ,
             CUR_CONS.NMCONSUMIDORTRAB    , CUR_CONS.VRAVIDEBCONSTRAB    , CUR_CONS.VRLIMDEBCONSTRAB  , CUR_CONS.DSENDECONSTRAB      ,
             CUR_CONS.NMBAIRCONSTRAB      , CUR_CONS.NRCEPCONSTRAB       , CUR_CONS.NRTELE2CONSTRAB   , CUR_CONS.NRTELECONSTRAB      ,
             CUR_CONS.NRRAMAL1CONSTRAB    , CUR_CONS.NRRAMAL2CONSTRAB    , CUR_CONS.NRFAXCONSTRAB     , CUR_CONS.DSEMAILCONSTRAB     ,
             CUR_CONS.VRPESUBCONSTRAB     , CUR_CONS.VRMESUBCONSTRAB     , CUR_CONS.CDTIPOCONSTRAB    , CUR_CONS.CDCCUSCLIETRAB      ,
             CUR_CONS.IDVERSALDCONTRAB    , CUR_CONS.VRDIASUBCONSTRAB    , CUR_CONS.CDVENDEDORTRAB    , CUR_CONS.NRRGCONSUMIDTRAB    ,
             CUR_CONS.CDSENHACONSTRAB     , CUR_CONS.NRCPFRESPCONTRAB    , CUR_CONS.NMRESPCONSTRAB    , CUR_CONS.NMLOGINCONSTRAB     ,
             CUR_CONS.DTNASCCONSTRAB      , CUR_CONS.VRMAXDEBCONSTRAB    , CUR_CONS.VRLIMCREDCONSTRAB , CUR_CONS.VRAVICREDCONSTRAB   ,
             CUR_CONS.VRMAXCREDCONSTRAB   , CUR_CONS.CDSENHASITETRAB     , CUR_CONS.NRACESSOSITETRAB  , CUR_CONS.CDPAISTRAB          ,
             CUR_CONS.SGESTADOTRAB        , CUR_CONS.CDMUNICIPIOTRAB     , CUR_CONS.IDTPVENDACONSTRAB , CUR_CONS.NRENDECONSTRAB      ,
             CUR_CONS.NRCELULARCONSTRAB   , CUR_CONS.DSCOMPLENDECONSTRAB , CUR_CONS.IDIMPCPFCUPOMTRAB , CUR_CONS.IDPERCONSPRODEXTRAB ,
             CUR_CONS.IDCADCONFLIBCONTRAB , CUR_CONS.DSREFERENDECONSTRAB , CUR_CONS.IDTPSELMANHATRAB  , CUR_CONS.IDTPSELTARDETRAB    ,
             CUR_CONS.IDTPSEALMOCOTRAB    , CUR_CONS.VRSUBSBASICOTRAB    , CUR_CONS.IDBLOQSALDFSTRAB  , CUR_CONS.CDFAMILISALDIMPTRAB ,
             CUR_CONS.VRSALDOCREDIMPTRAB  , CUR_CONS.VRMAXCONSMENSALTRAB, CUR_CONS.CDBAIRROTRAB );
      END IF;

      /* TRATAMENTO DA CONSUMIFILIAL - SOLICITACAO 108140 */
      /* INSERE A FILIAL DO CONSUMIDOR */
      IF P_MODOFILIAL = 'S' THEN
         SELECT  COUNT(*)
           INTO  V_NRREG
           FROM  CONSUMIFILIAL
          WHERE  CDCLIENTE     = CUR_CONS.CDCLIENTETRAB
            AND  CDCONSUMIDOR  = CUR_CONS.CDCONSUMIDORTRAB
            AND  CDFILIAL      = P_CDFILIAL;

         IF V_NRREG = 0 THEN
            INSERT INTO CONSUMIFILIAL
               (CDCLIENTE,CDCONSUMIDOR,CDFILIAL)
            VALUES
               (CUR_CONS.CDCLIENTETRAB, CUR_CONS.CDCONSUMIDORTRAB, P_CDFILIAL);
         END IF;
      END IF;

      /* UPDATA O CRACHA COM O CODIGO DO CONSUMIDOR */
      UPDATE CONSUMIDOR
         SET CDIDCONSUMID  = CUR_CONS.CDCONSUMIDORTRAB
       WHERE CDCLIENTE     = CUR_CONS.CDCLIENTETRAB
         AND CDCONSUMIDOR  = CUR_CONS.CDCONSUMIDORTRAB
         AND CDIDCONSUMID IS NULL;

      V_CONTADOR := V_CONTADOR + 1;

	END LOOP;

   /* TRATAMENTO DA INATIVACAO DOS CONSUMIDORES QUE NAO VIERAM NO ARQUIVO */
   IF P_INATIVACONS = 'S' THEN

      SELECT COUNT(*)
        INTO V_NRREG
        FROM CONSUMIDOR
       WHERE (INSTR(P_CDCLIENTE, CDCLIENTE) <> 0)
         AND CDCLIENTE||CDCONSUMIDOR NOT IN (SELECT CDCLIENTETRAB||CDCONSUMIDORTRAB FROM TRABCONSUMIDOR);

      /* ENCONTROU CONSUMIDORES QUE NAO VIERAM NO ARQUIVO DE IMPORTACAO */
      IF V_NRREG > 0 THEN

         /* NAVEGA NESTES CONSUMIDORES PARA VERIFICAR SE DEVE INATIVA-LOS OU NAO DE ACORDO COM A FILIAL */
         FOR CUR_FILIAL IN (
            SELECT  CDCLIENTE, CDCONSUMIDOR, IDCRACHAMESTRE, CDTIPOCONS
              FROM  CONSUMIDOR
              WHERE (INSTR(P_CDCLIENTE, CDCLIENTE) <> 0)
                AND CDCLIENTE||CDCONSUMIDOR NOT IN (SELECT CDCLIENTETRAB||CDCONSUMIDORTRAB FROM TRABCONSUMIDOR)) LOOP

            SELECT  COUNT(*)
              INTO  V_NRREG
              FROM  CONSUMIFILIAL
              WHERE CDCLIENTE    = CUR_FILIAL.CDCLIENTE
                AND CDCONSUMIDOR = CUR_FILIAL.CDCONSUMIDOR;
				
			/* VERIFICA SE ESSE TIPO DE CONSUMIDOR DEVE SER INATIVADO */
			BEGIN
				SELECT IDINACONSIMPARQ
				  INTO V_INATIVACONS
				  FROM TIPOCONS
			     WHERE CDTIPOCONS = CUR_FILIAL.CDTIPOCONS;
			EXCEPTION
				WHEN NO_DATA_FOUND THEN BEGIN
					V_INATIVACONS := 'S';
				END;
			END;
			
			IF (V_INATIVACONS = 'S') THEN
				/* SE O CONSUMIDOR NAO ESTIVER ASSOCIADO A NENHUMA FILIAL DEVE SER INATIVADO */
				IF V_NRREG = 0 THEN
					UPDATE CONSUMIDOR
					   SET IDSITCONSUMI   = '2'
                     WHERE CDCLIENTE      = CUR_FILIAL.CDCLIENTE
                       AND CDCONSUMIDOR   = CUR_FILIAL.CDCONSUMIDOR
                       AND IDCRACHAMESTRE = 'N';

				ELSE
					/* SE O CONSUMIDOR ESTIVER ASSOCIADO A APENAS UMA FILIAL */
					/* DESASSOCIA O CONSUMIDOR DA FILIAL DE IMPORTACAO */
					DELETE
                      FROM CONSUMIFILIAL
                     WHERE CDCLIENTE    = CUR_FILIAL.CDCLIENTE
                       AND CDCONSUMIDOR = CUR_FILIAL.CDCONSUMIDOR
                       AND CDFILIAL     = P_CDFILIAL;
					/* COMO O CONSUMIDOR NAO ESTA ASSOCIADO A NENHUMA FILIAL, POSSO INATIVA-LO */
					UPDATE CONSUMIDOR
                       SET IDSITCONSUMI   = '2'
                     WHERE CDCLIENTE      = CUR_FILIAL.CDCLIENTE
                       AND CDCONSUMIDOR   = CUR_FILIAL.CDCONSUMIDOR
                       AND IDCRACHAMESTRE = 'N';
				END IF;
			END IF;
         END LOOP;

      END IF;
   END IF;

   /* APAGA A TABELA TEMPORARIA */
   DELETE FROM TRABCONSUMIDOR;

   COMMIT;
END;
/