-- Lucas - Sol. 12506 - Req. 8 - Inclusão do script para atualização dos parâmetros de empresas que são empregadores domésticos.
SET TERM ^ ;

create or alter procedure SP_ATU_DOMESTICOS
as
declare variable WSQL varchar(15000);
declare variable WCODEMP integer;
declare variable WCODFPASTERC integer;
declare variable WCODREC integer;
declare variable WCNAERATEMP numeric(15,6);
declare variable WSIMPESGPS integer;
declare variable WFAP numeric(15,6);
begin

  wSql = 'select '||
           'X.BDCODEMP, '||
           'X.BDCODFPASTERC, '||
           'X.BDCODREC, '||
           'X.BDCNAERATEMP, '||
           'X.BDSIMPLESGPS, '||
           'X.BDFAP '||
         'from vrhf_base_tempgps X '||
         'join ( '||
           'select bdcodemp, max(bdrefemp) bdREFEMP from  vrhf_base_tempgps where BDREFEMP <= 201510 '||
           'group by BDCODEMP '||
         ') Y on (Y.BDCODEMP = X.BDCODEMP and Y.BDREFEMP = X.BDREFEMP) '||
         'where X.BDCODFPASTERC is not null and '||
         'exists ( '||
           'select Z.BDCODFPASTERC from VRH_BASE_TFPAS_TERCEIROS Z where Z.BDCODFPAS = 868 and Z.BDCODFPASTERC = X.BDCODFPASTERC '||
         ') ';

  for execute statement wsql into :wcodemp, :wcodfpasterc, :wcodrec, :wcnaeratemp, :wsimpesgps, :wfap do
    begin
      update or insert into VRHF_BASE_TEMPGPS (BDCODEMP,BDREFEMP,BDCODFPASTERC,BDCODREC,BDCNAERATEMP,BDEMPEMP,BDAUTEMP,BDFUNEMP,BDSIMPLESGPS,BDFAP,BDINDCOMP) values
         (:wcodemp, 201510, :wcodfpasterc, :wcodrec, :wcnaeratemp, 0, 0, 8, :wsimpesgps, :wfap, 3.20) matching (bdcodemp,bdrefemp);
    end
end^

SET TERM ; ^


insert into tempresas_ref (BDCODEMP,BDREFEMP,BDCODPLAPADRAO,BDCODHIST,BDCODCENTROS,BDCODCID,BDCODCONT,BDCODCONTEF,BDCODCONTRH,BDCODNATJUR,BDCODCNAE,BDCNAEPREP,BDNOMEMP,BDAPEEMP,BDENDEMP,
                           BDNUMEMP,BDBAIEMP,BDCEPEMP,BDDDDEMP,BDFONEEMP,BDFAXEMP,BDEMAILEMP,BDCOMPLEMP,BDLOGREMP,BDIEEMP,BDIMEMP,BDSITEMP,BDDDDFAXEMP,BDDOCAUXILIAR,BDSUFRAMA,
                           BDCODPLANOONLINE,BDNOMREDUZEMP,BDCODCONTABLALUR,BDCODPLATRIB,BDCODMOEDAS,BDNAOPERMITERATEIOGRUPO,BDABRIRRATEIO,BDCODPLAPADRAOREF)
select Z.BDCODEMP,201510,Z.BDCODPLAPADRAO,Z.BDCODHIST,Z.BDCODCENTROS,Z.BDCODCID,Z.BDCODCONT,Z.BDCODCONTEF,Z.BDCODCONTRH,Z.BDCODNATJUR,Z.BDCODCNAE,Z.BDCNAEPREP,Z.BDNOMEMP,Z.BDAPEEMP,
       Z.BDENDEMP,Z.BDNUMEMP,Z.BDBAIEMP,Z.BDCEPEMP,Z.BDDDDEMP,Z.BDFONEEMP,Z.BDFAXEMP,Z.BDEMAILEMP,Z.BDCOMPLEMP,Z.BDLOGREMP,Z.BDIEEMP,Z.BDIMEMP,Z.BDSITEMP,Z.BDDDDFAXEMP,Z.BDDOCAUXILIAR,
       Z.BDSUFRAMA,Z.BDCODPLANOONLINE,Z.BDNOMREDUZEMP,Z.BDCODCONTABLALUR,Z.BDCODPLATRIB,Z.BDCODMOEDAS,Z.BDNAOPERMITERATEIOGRUPO,Z.BDABRIRRATEIO,Z.BDCODPLAPADRAOREF
from tempresas_ref Z
join (
  select X.BDCODEMP, MAX(X.BDREFEMP) BDREFEMP
  from tempresas_ref X
  where not exists
    (select Y.BDCODEMP, Y.BDREFEMP from tempresas_ref Y where Y.BDCODEMP = X.BDCODEMP and Y.BDREFEMP = 201510)
    and X.BDREFEMP <= 201510
  group by X.BDCODEMP
) W on (W.BDCODEMP = Z.BDCODEMP and W.BDREFEMP = Z.BDREFEMP);
COMMIT WORK;

EXECUTE PROCEDURE SP_ATU_DOMESTICOS;
COMMIT WORK;
DROP PROCEDURE SP_ATU_DOMESTICOS;
UPDATE OR INSERT INTO TCNAE (BDCODCNAE, BDSECAO, BDTIPOCNAE, BDCLASSIFICACAO, BDDESCCNAE) VALUES (9700500, 'T', 4, '97.00-5/00', 'Serviços domésticos') MATCHING (BDCODCNAE);
COMMIT WORK;
UPDATE OR INSERT INTO TCNAE_REF (BDCODCNAE, BDREFCNAE, BDTAXACNAE) VALUES (9700500, 198501, 0) MATCHING (BDCODCNAE, BDREFCNAE);
UPDATE OR INSERT INTO TCNAE_REF (BDCODCNAE, BDREFCNAE, BDTAXACNAE) VALUES (9700500, 200706, 0) MATCHING (BDCODCNAE, BDREFCNAE);
UPDATE OR INSERT INTO TCNAE_REF (BDCODCNAE, BDREFCNAE, BDTAXACNAE) VALUES (9700500, 201001, 0) MATCHING (BDCODCNAE, BDREFCNAE);
UPDATE OR INSERT INTO TCNAE_REF (BDCODCNAE, BDREFCNAE, BDTAXACNAE) VALUES (9700500, 201510, 0.80) MATCHING (BDCODCNAE, BDREFCNAE);
COMMIT WORK;

UPDATE OR INSERT INTO TDOCARRECADACAO (BDCODDOCARREC, BDDESCDOCARREC, BDCODSIS, BDEXPORTA, BDFORMREL, BDCODESF, BDCODUF, BDNUMVIAS)
                               VALUES (31, 'DAE', 2, NULL, NULL, 1, NULL, 1)
                             MATCHING (BDCODDOCARREC);

set term ^;
EXECUTE BLOCK
AS
  DECLARE wNome varchar(200);
BEGIN
  select BDDESCIMP from TIMPOSTO WHERE upper(BDDESCIMP) = 'DAE - DOCUMENTO DE ARRECADAÇÃO ÚNICO' into wNome;

  IF ((wNome = '') or (wNome is null)) THEN
    BEGIN
      UPDATE OR INSERT INTO TIMPOSTO (BDCODIMP, BDDESCIMP, BDDATACRIACAO, BDDATAEXTINTO, BDCOMPETENCIA, BDDOCARRECADACAO, BDPERAPURACAO, BDDIAVENCTO, BDANTECIPA, BDMESVENCTO, BDJUROSDIA, BDVLRJUROS, BDJUROSDIAMES, BDMULTADIA, BDVLRMULTA, BDMULTADIAMES, BDVLRMAXMULTA, BDALIQIMPOSTO, BDLIMITEISENCAO, BDTIPORETENCAO, BDCODREC, BDCODRECVAR, BDVARIACAO, BDCODIND, BDRETBCCALCULOIMP, BDFORMULAOUTENT, BDCODRECOUTENT, BDGPSPRINCIPAL, BDCODSIS, BDFORMULAVALIMP, BDCHAVESWS, BDTIPOIMPDOC, BDCLASSIMP, BDTIPOEMISSAOGUIA, BDCODTIPIMP, BDALERTADIAS, BDCODCID, BDCODUF, BDREADONLY, BDTIPOCLASSIMP, BDPREVHORA, BDCTAVCON, BDCLASSEVENC, BDMESVENCE, BDENTREGA, BDTEMPOESTIMADO)
                              VALUES (NULL, 'DAE - Documento de arrecadação único', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, 1, NULL, 1, 0, NULL, 0, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 2, NULL, NULL, NULL, '01.01.01.08', NULL, 1, NULL, NULL, NULL, 'TRUE', 0, NULL, NULL, NULL, NULL, NULL, 0)
                            MATCHING (BDCODIMP);
    END
END^
set term ;^

set term ^;
EXECUTE BLOCK
AS
  DECLARE wNome varchar(200);
BEGIN
  select BDDESCIMP from TIMPOSTO WHERE upper(BDDESCIMP) = 'GUIA DE PAGAMENTO - EMPREGADOR DOMÉSTICO' into wNome;

  IF ((wNome = '') or (wNome is null)) THEN
    BEGIN
      UPDATE OR INSERT INTO TIMPOSTO (BDCODIMP, BDDESCIMP, BDDATACRIACAO, BDDATAEXTINTO, BDCOMPETENCIA, BDDOCARRECADACAO, BDPERAPURACAO, BDDIAVENCTO, BDANTECIPA, BDMESVENCTO, BDJUROSDIA, BDVLRJUROS, BDJUROSDIAMES, BDMULTADIA, BDVLRMULTA, BDMULTADIAMES, BDVLRMAXMULTA, BDALIQIMPOSTO, BDLIMITEISENCAO, BDTIPORETENCAO, BDCODREC, BDCODRECVAR, BDVARIACAO, BDCODIND, BDRETBCCALCULOIMP, BDFORMULAOUTENT, BDCODRECOUTENT, BDGPSPRINCIPAL, BDCODSIS, BDFORMULAVALIMP, BDCHAVESWS, BDTIPOIMPDOC, BDCLASSIMP, BDTIPOEMISSAOGUIA, BDCODTIPIMP, BDALERTADIAS, BDCODCID, BDCODUF, BDREADONLY, BDTIPOCLASSIMP, BDPREVHORA, BDCTAVCON, BDCLASSEVENC, BDMESVENCE, BDENTREGA, BDTEMPOESTIMADO)
                              VALUES (NULL, 'Guia de pagamento - Empregador doméstico', NULL, NULL, 2, 31, 6, 0, 'FALSE', 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, NULL, NULL, NULL, NULL, '', NULL, 'FALSE', 2, '', NULL, NULL, '01.01.01.08.0100', NULL, NULL, 0, NULL, NULL, 'FALSE', 1, '000:00', NULL, NULL, 0, 0, 0)
                            MATCHING (BDCODIMP);
    END
END^
set term ;^

COMMIT WORK;