/* Server version: WI-V6.3.1.26351 Firebird 2.5 
   SQLDialect: 3. ODS: 11.2. Forced writes: On. Sweep inteval: 0.
   Page size: 16384. Cache pages: 2000 (32000 Kb). Read-only: False. */
SET NAMES WIN1252;

SET SQL DIALECT 3;

CONNECT '192.168.10.199:c:\sci\bancopadrao\vsci.fdb' USER 'SYSDBA' PASSWORD 'masterkey';

SET AUTODDL ON;

/* Create Procedure... */
SET TERM ^ ;

CREATE OR ALTER PROCEDURE VSUC_SP_RETORNA_BALANCO_PATRI_X(PRCODEMP VARCHAR(1024),
PRDATINI DATE,
PRDATFIN DATE,
PRIGNZER INTEGER,
PRCTALONINI VARCHAR(40),
PRCTALONFIN VARCHAR(40),
PRCODPLAPADRAO INTEGER,
PRNAOMOSTRACONTACOMP INTEGER,
PRCTASEMMOV INTEGER,
PRREFEMP INTEGER,
PRCONSOLIDADA INTEGER,
PRDETALHARCTAPARTICIPANTE INTEGER,
PRCONCATENACODPARTICIPANTE INTEGER,
PRNIVEISCONTA VARCHAR(50),
PRCONTABILIZACAO INTEGER,
PRMATRIZFILIALCONSOLIDADO INTEGER)
 RETURNS(BDCODEMP INTEGER,
BDCODMATRIZ INTEGER,
BDCODEMPFILIAL INTEGER,
BDNOMEMP DM_BDNOMEMP,
BDCNPJEMP VARCHAR(20),
BDCODPLAPADRAO INTEGER,
BDCODCTA INTEGER,
BDCTALON VARCHAR(40),
BDNOMCTA VARCHAR(100),
BDTIPCTA INTEGER,
BDSEQCTA VARCHAR(40),
BDSALANT NUMERIC(15,2),
BDSALMOV NUMERIC(15,2),
BDSALATU NUMERIC(15,2),
BDANAVER NUMERIC(15,2),
BDANAHOR NUMERIC(15,2),
BDFORMULA VARCHAR(1024),
BDGRPCTA INTEGER,
BDAPECTA VARCHAR(20))
 AS
 BEGIN SUSPEND; END
^


/* Alter View (Drop, Create)... */
/* Drop altered view: VW_VRH_BASE_TTERC_VINC */
SET TERM ; ^

DROP VIEW VW_AUTONOMOS_REF_ATUAL;

DROP VIEW VW_VRH_BASE_TTERC_VINC;

/* Create altered view: VW_VRH_BASE_TTERC_VINC */
/* Create view: VW_VRH_BASE_TTERC_VINC */
CREATE VIEW VW_VRH_BASE_TTERC_VINC(
BDCODCOL,
BDREFCOL,
BDCODCLA,
BDCODCBO,
BDCODCBOTSIN,
BDCODSINTRAB)
 AS 
SELECT
    X.bdcodter,
    X.bdrefTERC,
    X.bdcodcla,
    x.bdcodcbo,
    x.bdcodcbotsin,
    x.bdcodsintrab
FROM vrh_base_tterc_vinc X
JOIN VW_VRH_BASE_TTERC_VINC_MAX_MIN y ON (x.bdcodter = y.bdcodter AND x.bdrefterc = y.bdrefter)
;

/* Drop altered view: VW_VRHF_BASE_TEMPGPS */
DROP VIEW VW_VRHF_EMPRESAS_REF_ATUAL;

DROP VIEW VW_VRHF_BASE_TEMPGPS;

/* Create altered view: VW_VRHF_BASE_TEMPGPS */
/* Create view: VW_VRHF_BASE_TEMPGPS */
CREATE VIEW VW_VRHF_BASE_TEMPGPS(
BDCODEMP,
BDCODGPS,
BDCNAERATEMP,
BDEMPEMP,
BDAUTEMP,
BDFUNEMP,
BDCODFPASTERC,
BDSIMPLESGPS,
BDFAP,
BDCODFPAS,
BDCODTERC,
BDPERTERC,
BDCCATVCONCOM,
BDINDCOMP)
 AS 
SELECT
    BASE.bdcodemp,
    VIN.bdcodrec,
    VIN.bdcnaeratemp,
    VIN.bdempemp,
    VIN.bdautemp,
    VIN.bdfunemp,
    VIN.bdcodfpasterc,
    VIN.bdsimplesgps,
    VIN.BDFAP,
    w.bdcodfpas,
    w.bdcodterc,
    w.bdperterc,
    VIN.BDCCATVCONCOM,
    VIN.BDINDCOMP
FROM
(
  SELECT
      X.bdcodemp,
      COALESCE
  (
      (SELECT MAX(d2.bdrefemp) FROM vrhf_base_tempgps d2 WHERE d2.bdcodemp = x.bdcodemp AND CAST(d2.BDREFEMP AS VARCHAR(6)) <= (SELECT TRIM(rdb$get_context('USER_SESSION', 'W_ANOMES')) FROM rdb$database)),
      (SELECT MIN(d1.bdrefemp) FROM vrhf_base_tempgps d1 WHERE d1.bdcodemp = x.bdcodemp)
  ) AS bdrefemp
  FROM TEMPRESAS x

) AS BASE
JOIN vrhf_base_tempgps VIN ON (VIN.BDCODEMP = BASE.BDCODEMP AND VIN.BDREFEMP = BASE.BDREFEMP)
LEFT OUTER JOIN VRH_BASE_TFPAS_TERCEIROS w ON (w.bdcodfpasterc = VIN.bdcodfpasterc)
;

/* Drop altered view: VWGR_SCI_EMPRESAS */
DROP VIEW VWGR_SCI_EMPRESAS;

/* Create altered view: VWGR_SCI_EMPRESAS */
/* Create view: VWGR_SCI_EMPRESAS */
CREATE VIEW VWGR_SCI_EMPRESAS(
BDCODEMP,
BDCODMATRIZ,
BDCNPJEMP,
BDDATAINIATIVEMP,
BDORGAOEMP,
BDDATAREGEMP,
BDDESCCARTEMP,
BDDATAFIMATIVEMP,
BDREFEMP,
BDCODPLAPADRAO,
BDCODHIST,
BDCODCENTROS,
BDCODPLATRIB,
BDCODCID,
BDCODCONT,
BDCODNATJUR,
BDCODCNAE,
BDNOMEMP,
BDAPEEMP,
BDENDEMP,
BDNUMEMP,
BDBAIEMP,
BDCEPEMP,
BDDDDEMP,
BDFONEEMP,
BDFAXEMP,
BDEMAILEMP,
BDCOMPLEMP,
BDLOGREMP,
BDIEEMP,
BDIMEMP,
BDSITEMP,
BDCODPLANOONLINE,
BDLOGOEMP,
BDNOMESC,
BDINIATIVESC,
BDFIMATIVESC,
BDLOGOESC,
BDLOGRESC,
BDENDESC,
BDNUMESC,
BDBAIESC,
BDCEPESC,
BDCNPJESC,
BDDDDESC,
BDFONEESC,
BDRAMALESC,
BDEMAILESC,
BDCONTTERCESC,
BDCOMPLESC,
BDFAXESC,
BDCODCIDESC,
BDCRCESC,
BDAPEPLA,
BDNOMPLA,
BDNOMCID,
BDANIVCID,
BDSIGLAUF,
BDDESCUF,
BDNOMECONT,
BDCPFCONT,
BDCRCCONT,
BDENDCONT,
BDLOGRCONT,
BDNUMEROCONT,
BDCOMPLCONT,
BDCEPCONT,
BDBAIRROCONT,
BDDDDCONT,
BDFONECONT,
BDFAXCONT,
BDEMAILCONT,
BDCMCCONT,
BDESCOLARIDADECONT,
BDTIPOCONTABILISTA,
BDCODOCUPA,
BDCODQUA,
BDREGJUN,
BDCODCIDSOC,
BDNOMSOC,
BDENDSOC,
BDNUMSOC,
BDCOMPLSOC,
BDBAISOC,
BDCEPSOC,
BDCGCCEISOC,
BDDDSOC,
BDTELRESSOC,
BDTELCELSOC,
BDEMAILSOC,
BDLOGRSOC,
BDQUALIRESPLEGALSOC,
BDEXTERIORSOC,
BDDATAENTSOC,
BDDATASAISOC,
BDCODPAISSOC,
BDQUALIFICACAOSOC,
BDDESCQUALISOC,
BDRESPONSAVELSOC,
BDCPFRESPLEGALSOC,
BDPARAMREL,
BDEXPQUALISOC,
BDQUALISOCDIPJ,
BDDESCQUALISOCREP,
BDDESCQUALISOCSPED,
BDPERSONALIZACAO,
BDQTDCOTASOC,
BDVALCOTASOC,
BDPERCCOTASOC,
BDCODCONTEF,
BDNOMECONTEF,
BDCPFCONTEF,
BDCRCCONTEF,
BDENDCONTEF,
BDLOGRCONTEF,
BDNUMEROCONTEF,
BDCOMPLCONTEF,
BDCEPCONTEF,
BDBAIRROCONTEF,
BDDDDCONTEF,
BDFONECONTEF,
BDFAXCONTEF,
BDEMAILCONTEF,
BDCMCCONTEF,
BDESCOLARIDADECONTEF,
BDTIPOCONTABILISTAEF,
BDCODOCUPAEF,
BDCODCONTRH,
BDNOMECONTRH,
BDCPFCONTRH,
BDCRCCONTRH,
BDENDCONTRH,
BDLOGRCONTRH,
BDNUMEROCONTRH,
BDCOMPLCONTRH,
BDCEPCONTRH,
BDBAIRROCONTRH,
BDDDDCONTRH,
BDFONECONTRH,
BDFAXCONTRH,
BDEMAILCONTRH,
BDCMCCONTRH,
BDESCOLARIDADECONTRH,
BDTIPOCONTABILISTARH,
BDCODOCUPARH,
BDLIVRO,
BDFOLHA,
BDNOMECARTORIO,
BDRGSOC,
BDORGEMISOC,
BDSIGLAUFRG,
BDDESCUFRG,
BDESTCIVILSOC,
BDCODCBO,
BDDESCCBO,
BDCODACESSOSN,
BDSECAOCNAE,
BDTIPOCNAE,
BDCLASSIFICACAOCNAE,
BDDESCCNAE,
BDDESCCNAEADICIONAIS,
BDNOMCIDESC,
BDSIGLAUFESC,
BDCODESC,
BDCODSOC,
BDCODFPAS)
 AS 
SELECT temp.BDCODEMP, temp.BDCODMATRIZ, temp.BDCNPJEMP, temp.BDDATAINIATIVEMP,
       temp.BDORGAOEMP, temp.BDDATAREGEMP, temp.BDDESCCARTEMP,
       temp.BDDATAFIMATIVEMP,

       tref.BDREFEMP, tref.BDCODPLAPADRAO, tref.BDCODHIST, tref.BDCODCENTROS, tref.bdcodplatrib,
       tref.BDCODCID,
       tref.BDCODCONT, tref.BDCODNATJUR, tref.BDCODCNAE, tref.BDNOMEMP,
       tref.BDAPEEMP, tref.BDENDEMP, tref.BDNUMEMP, tref.BDBAIEMP, tref.BDCEPEMP,
       tref.BDDDDEMP, tref.BDFONEEMP, tref.BDFAXEMP, tref.BDEMAILEMP, tref.BDCOMPLEMP,
       tref.BDLOGREMP, tref.BDIEEMP, tref.BDIMEMP, tref.BDSITEMP, TREF.BDCODPLANOONLINE, timg.BDLOGOEMP,

       tesc.BDNOMESC, tesc.BDINIATIVESC, tesc.BDFIMATIVESC, tesc.BDLOGOESC, tesc.BDLOGRESC,
       tesc.BDENDESC, tesc.BDNUMESC, tesc.BDBAIESC, tesc.BDCEPESC, tesc.BDCNPJESC,
       tesc.BDDDDESC, tesc.BDFONEESC, tesc.BDRAMALESC, tesc.BDEMAILESC, tesc.BDCONTTERCESC,
       tesc.BDCOMPLESC, tesc.BDFAXESC, tesc.BDCODCID "BDCODCIDESC", tesc.BDCRCESC,

       tpla.BDAPELIDO "BDAPEPLA", tpla.bdnome "BDNOMPLA",

       tcid.bdnomcid, tcid.bdanivcid,

       tuf.bdsiglauf, tuf.bddescuf,

       tcon.bdnomecont, tcon.bdcpfcont, tcon.bdcrccont, tcon.bdendcont,
       tcon.bdlogrcont, tcon.bdnumerocont, tcon.bdcomplcont, tcon.bdcepcont,
       tcon.bdbairrocont, tcon.bddddcont, tcon.bdfonecont, tcon.bdfaxcont,
       tcon.bdemailcont, tcon.bdcmccont, tcon.bdescolaridadecont, tcon.bdtipocontabilista,
       tcon.bdcodocupa, tqcont.bddescqualisoc,

       vsuc_base_temp.bdregjun,

       tsoc.BDCODCID "BDCODCIDSOC", tsoc.BDNOMSOC, tsoc.BDENDSOC, tsoc.BDNUMSOC,
       tsoc.BDCOMPLSOC, tsoc.BDBAISOC, tsoc.BDCEPSOC, tsoc.BDCGCCEISOC, tsoc.BDDDSOC,
       tsoc.BDTELRESSOC, tsoc.BDTELCELSOC, tsoc.BDEMAILSOC,
       tsoc.BDLOGRSOC, tsoc.BDQUALIRESPLEGALSOC, tsoc.BDEXTERIORSOC, tsoc.BDDATAENTSOC,
       tsoc.BDDATASAISOC, tsoc.BDCODPAIS "BDCODPAISSOC", tqua.bddescqualisoc, tqua.bddescqualisoc,
       tsoc.BDRESPONSAVELSOC, tsoc.BDCPFRESPLEGALSOC,
       0, tqua.BDEXPQUALISOC,
       tqdipj.bddescqualisoc, tqrep.bddescqualisoc, tqsped.bddescqualisoc,
       CASE WHEN (tesc.bdprintesc = 1) THEN tesc.bdnomesc
            WHEN (tesc.bdprintesc = 2) THEN tesc.bdnomesc||' - '||(SELECT FIRST 1 BDPERSONALIZACAO FROM TPERSONALIZACAO)
            ELSE
            (SELECT FIRST 1 BDPERSONALIZACAO FROM TPERSONALIZACAO) END AS "bdpersonalizacao",
       tcot.bdqtdcota, tcot.bdvalcota, tcot.bdperccota,

       tconef.bdcodcont, tconef.bdnomecont, tconef.bdcpfcont, tconef.bdcrccont, tconef.bdendcont,
       tconef.bdlogrcont, tconef.bdnumerocont, tconef.bdcomplcont, tconef.bdcepcont,
       tconef.bdbairrocont, tconef.bddddcont, tconef.bdfonecont, tconef.bdfaxcont,
       tconef.bdemailcont, tconef.bdcmccont, tconef.bdescolaridadecont, tconef.bdtipocontabilista,
       tconef.bdcodocupa,

       tconrh.bdcodcont, tconrh.bdnomecont, tconrh.bdcpfcont, tconrh.bdcrccont, tconrh.bdendcont,
       tconRH.bdlogrcont, tconRH.bdnumerocont, tconRH.bdcomplcont, tconrh.bdcepcont,
       tconRH.bdbairrocont, tconRH.bddddcont, tconRH.bdfonecont, tconRH.bdfaxcont,
       tconRH.bdemailcont, tconRH.bdcmccont, tconRH.bdescolaridadecont, tconRH.bdtipocontabilista,
       tconRH.bdcodocupa, temp.bdlivro, temp.bdfolha, temp.bdnomecartorio, tsoc.BDRGSOC, tsoc.BDORGEMISOC,
       tUFSoc.bdsiglauf "BDSIGLAUFRG", tUFSoc.bddescuf "BDDESCUFRG",
       CASE WHEN (tsoc.bdestcivilsoc = 0) THEN 'Solteiro'
            WHEN (tsoc.bdestcivilsoc = 1) THEN 'Casado'
            WHEN (tsoc.bdestcivilsoc = 2) THEN 'Divorciado'
            WHEN (tsoc.bdestcivilsoc = 3) THEN 'Viúvo'
            WHEN (tsoc.bdestcivilsoc = 4) THEN 'Separado judicialmente'
            WHEN (tsoc.bdestcivilsoc = 5) THEN 'União estável'
            WHEN (tsoc.bdestcivilsoc = 6) THEN 'Outros'
            ELSE
            'Não informado' END AS "BDESTCIVILSOC",
       tsoc.bdcodcbo, tCBO.bddesccbo, parfed.bdcodacessosn,
       cnae.bdsecao, cnae.bdtipocnae, cnae.bdclassificacao, cnae.bddesccnae,
       CAST(TRIM(COALESCE(cnae.bddesccnae, '')||CASE WHEN (cnae.bddesccnae <> '' AND cnaeadc.BDDESCCNAEAdicionais <> '') THEN '; ' ELSE '' END||COALESCE(cnaeadc.BDDESCCNAEAdicionais, '')) AS VARCHAR(10000)),
       tcidesc.bdnomcid, ufesc.bdsiglauf, tesc.bdcodesc, tsoc.bdcodsoc, fpas.bdcodfpas

FROM tempresas temp
JOIN vw_tempresas_ref tref ON (tref.bdcodemp = temp.bdcodemp)
LEFT OUTER JOIN tempresas_img timg ON (timg.bdcodemp = temp.bdcodemp)
LEFT OUTER JOIN tplano_padrao tpla ON (tpla.bdcodplapadrao = tref.bdcodplapadrao)
LEFT OUTER JOIN tcidade tcid ON (tcid.bdcodcid = tref.bdcodcid)
LEFT OUTER JOIN tuf ON (tuf.bdcoduf = tcid.bdcoduf)
LEFT OUTER JOIN tcontador_padrao tcon ON (tcon.bdcodcont = tref.bdcodcont)
LEFT OUTER JOIN tcontador_padrao tconef ON (tconef.bdcodcont = tref.bdcodcontef)
LEFT OUTER JOIN tcontador_padrao tconrh ON (tconrh.bdcodcont = tref.bdcodcontrh)
LEFT OUTER JOIN vsuc_base_temp ON (vsuc_base_temp.bdcodemp = temp.bdcodemp)
LEFT OUTER JOIN tescritorio tesc ON (tesc.bdcodesc = tcon.bdcodesc)
LEFT OUTER JOIN empresas_tsoc tsoc ON (tsoc.bdcodemp = temp.bdcodemp
                                       AND tsoc.bdcodsoc = (SELECT FIRST 1 empresas_tsoc.bdcodsoc FROM empresas_tsoc
                                                            WHERE empresas_tsoc.bdcodemp = temp.bdcodemp
                                                            AND empresas_tsoc.bddataentsoc <= CURRENT_DATE
                                                            AND (empresas_tsoc.bddatasaisoc >= CURRENT_DATE OR (empresas_tsoc.bddatasaisoc IS NULL))
                                                            AND empresas_tsoc.bdresponsavelsoc = 'TRUE'
                                                            ORDER BY empresas_tsoc.bddataentsoc DESC))
LEFT OUTER JOIN tqualificacaosoc tqua ON (tqua.bdqualificacaosoc = tsoc.bdqualificacaosoc)                    
LEFT OUTER JOIN empresas_tsoccotas tcot ON (tcot.bdcodemp = tsoc.bdcodemp AND tcot.bdcodsoc = tsoc.bdcodsoc
                                            AND tcot.bdrefcota = (SELECT FIRST 1 empresas_tsoccotas.bdrefcota FROM empresas_tsoccotas
                                                                  WHERE empresas_tsoccotas.bdcodemp = tsoc.bdcodemp
                                                                  AND empresas_tsoccotas.bdcodsoc = tsoc.bdcodsoc
                                                                  AND empresas_tsoccotas.bdrefcota = (SELECT TRIM(rdb$get_context('USER_SESSION', 'W_ANOMES')) FROM rdb$database)
                                                                  ORDER BY empresas_tsoccotas.bdrefcota DESC))
LEFT OUTER JOIN tuf tUFSoc ON (tUFSoc.bdcoduf = tsoc.bdcodufrg)
LEFT OUTER JOIN tqualificacaosoc tqdipj ON (tqdipj.bdqualificacaosoc = tsoc.bdqualilegaldipjsoc)
LEFT OUTER JOIN tqualificacaosoc tqrep ON (tqrep.bdqualificacaosoc = tsoc.bdqualiresplegalsoc)
LEFT OUTER JOIN tqualificacaosoc tqsped ON (tqsped.bdqualificacaosoc = tsoc.bdqualispedsoc)
LEFT OUTER JOIN tqualificacaosoc tqcont ON (tqcont.bdqualificacaosoc = tcon.bdqualificacaosoc)
LEFT OUTER JOIN VRH_BASE_TCBO tCBO ON (tCBO.bdcodcbo = tsoc.bdcodcbo)
LEFT OUTER JOIN VW_VEF_BASE_TEMPPARFED parfed ON (parfed.bdcodemp = temp.bdcodemp AND parfed.bdrefemp = tref.bdrefemp)
LEFT OUTER JOIN TCNAE cnae ON (cnae.bdcodcnae = tref.bdcodcnae)
LEFT OUTER JOIN (SELECT LIST(cnaeadd.BDDESCCNAE, '; ') AS BDDESCCNAEAdicionais, cnaeadicionais.bdcodemp FROM VRH_BASE_EMP_CNAEADIC cnaeadicionais
                 LEFT OUTER JOIN TCNAE cnaeadd ON (cnaeadd.BDCODCNAE = cnaeadicionais.BDCODCNAE)
                 GROUP BY cnaeadicionais.bdcodemp) AS cnaeadc ON (cnaeadc.bdcodemp = tref.BDCODEMP)
LEFT OUTER JOIN tcidade tcidesc ON (tcidesc.bdcodcid = tesc.bdcodcid)
LEFT OUTER JOIN tuf ufesc ON (ufesc.bdcoduf = tcidesc.bdcoduf)
LEFT OUTER JOIN VRHF_BASE_TEMPGPS gps ON (gps.bdcodemp = temp.bdcodemp) and (gps.bdrefemp = tref.bdrefemp)
LEFT OUTER JOIN VRH_BASE_TFPAS_TERCEIROS fpas ON (fpas.bdcodfpasterc = gps.bdcodfpasterc)
;

/* Drop altered view: VWRH_EMPMENSAL_ABA_GPS */
DROP VIEW VWRH_EMPMENSAL_ABA_GPS;

/* Create altered view: VWRH_EMPMENSAL_ABA_GPS */
/* Create view: VWRH_EMPMENSAL_ABA_GPS */
CREATE VIEW VWRH_EMPMENSAL_ABA_GPS(
BDCODEMP,
BDREFEMP,
BDCODFPASTERC,
BDSIMPLESGPS,
BDEMPEMP,
BDAUTEMP,
BDFUNEMP,
BDFAP,
BDCODFPAS,
BDCODTERC,
BDPERTERC,
BDDESCFPAS,
BDDESCTERC,
BDCCATVCONCOM,
BDINDCOMP)
 AS 
SELECT
  RHGPS.BDCODEMP,
  RHGPS.BDREFEMP,
  RHGPS.BDCODFPASTERC,
  CASE
    WHEN RHGPS.BDSIMPLESGPS = 1 THEN 'Optante'
    WHEN RHGPS.BDSIMPLESGPS = 2 THEN 'Optante - fat. anual superior a R$ 1.200.000,00'
    WHEN RHGPS.BDSIMPLESGPS = 3 THEN 'Não Optante - prod.Rural PF (CEI e FPAS 604)'
    WHEN RHGPS.BDSIMPLESGPS = 4 THEN 'Não Optante - c/liminar p/não pagto.Contr.Soc.'
    WHEN RHGPS.BDSIMPLESGPS = 5 THEN 'Optante - C/liminar p/não pagto.Contr.Social'
    WHEN RHGPS.BDSIMPLESGPS IS NULL THEN 'Não Optante'
    WHEN RHGPS.BDSIMPLESGPS NOT IN (1,2,3,4,5) THEN 'Não Optante'
  END BDSIMPLESGPS,
  RHGPS.BDEMPEMP,
  RHGPS.BDAUTEMP,
  RHGPS.BDFUNEMP,
  RHGPS.BDFAP,
  RHFPASTERC.BDCODFPAS,
  RHFPASTERC.BDCODTERC,
  CASE
    WHEN RHGPS.BDSIMPLESGPS IN (1,2,5) THEN
      0
    ELSE
      RHFPASTERC.BDPERTERC
  END,
  CAST(FPAS.BDDESCFPAS AS VARCHAR(10000)) BDDESCFPAS,
  TTERC.BDDESCTERC,
  RHGPS.BDCCATVCONCOM,
  RHGPS.BDINDCOMP
FROM
  VRHF_BASE_TEMPGPS RHGPS
LEFT OUTER JOIN VRH_BASE_TFPAS_TERCEIROS RHFPASTERC ON (RHGPS.BDCODFPASTERC = RHFPASTERC.BDCODFPASTERC)
LEFT OUTER JOIN VRH_BASE_TFPAS FPAS ON (RHFPASTERC.BDCODFPAS = FPAS.BDCODFPAS)
LEFT OUTER JOIN VRH_BASE_TTERCEIROS TTERC ON (RHFPASTERC.BDCODTERC = TTERC.BDCODTERC)
;

/* Alter Procedure... */
/* Alter (SP_FICHA_REGISTROS_SALARIOS) */
SET TERM ^ ;

ALTER PROCEDURE SP_FICHA_REGISTROS_SALARIOS(PRCODEMP VARCHAR(20000),
PRCODCOL VARCHAR(20000),
PRREF INTEGER,
PRRETORNAVAZIO INTEGER,
PRTRAZERSALINI INTEGER = 0)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDNOMCOL VARCHAR(70),
BDDATAADMCOL DATE,
BDSALINICOL NUMERIC(18,6),
BDDATACOL DATE,
BDSALBASECOL NUMERIC(18,6),
BDCODMAS INTEGER,
BDDESCMAS VARCHAR(50),
BDCODTFUC INTEGER,
BDCODSHE INTEGER,
BDDESCSHE VARCHAR(50))
 AS
DECLARE VARIABLE WCODEMP INTEGER;
DECLARE VARIABLE WCODCOL INTEGER;
DECLARE VARIABLE WNOMCOL VARCHAR(70);
DECLARE VARIABLE WDATAADMCOL DATE;
DECLARE VARIABLE WSALINICOL NUMERIC(18,6);
DECLARE VARIABLE WDATACOL DATE;
DECLARE VARIABLE WSALBASECOL NUMERIC(18,6);
DECLARE VARIABLE WCODMAS INTEGER;
DECLARE VARIABLE WSQL VARCHAR(30000);
DECLARE VARIABLE WSQL2 VARCHAR(1000);
DECLARE VARIABLE WCONT INTEGER;
DECLARE VARIABLE WDESCMAS VARCHAR(50);
DECLARE VARIABLE WCODTFUC INTEGER;
DECLARE VARIABLE WCODSHE INTEGER;
DECLARE VARIABLE WTIPOCOL INTEGER;
DECLARE VARIABLE WCAMPOSSHE VARCHAR(100);
DECLARE VARIABLE WJOINSHE VARCHAR(250);
DECLARE VARIABLE WCAMPODTSHE VARCHAR(25);
DECLARE VARIABLE WDESCSHE VARCHAR(50);
BEGIN
  IF (PRRETORNAVAZIO = 1) THEN
     BEGIN
       EXECUTE PROCEDURE SP_SETA_COMPETENCIA(PRREF);
       WSQL = ' select BDCODEMP, BDCODCOL, BDTIPOCOL from VW_VRHF_EMP_TCOL_CALC COL ';

       IF ((prcodemp IS NOT NULL) AND (prcodemp <> '')) THEN
          BEGIN
             WSQL = WSQL || ' where COL.BDCODEMP = '||prcodemp;
             IF ((PRCODCOL IS NOT NULL) AND (PRCODCOL <> '')) THEN
                WSQL = WSQL || ' AND COL.bdcodcol = '||PRCODCOL;
          END
       ELSE
          BEGIN
             IF ((PRCODCOL IS NOT NULL) AND (PRCODCOL <> '')) THEN
                WSQL = WSQL || ' where COL.bdcodcol = '||PRCODCOL;
          END

       FOR EXECUTE STATEMENT wsql INTO
         WCODEMP,
         WCODCOL,
         WTIPOCOL
       DO
       BEGIN
         IF (WTIPOCOL <> 4) THEN
            BEGIN
              WCAMPOSSHE = ' COLDIA.BDDATACOL, COLDIA.BDSALBASECOL, 0 bdcodshe, CAST(null as varchar(50)) BDDESCSHE ';
              WJOINSHE =  ' join VRH_EMP_TCOLCON_CALCDIA COLDIA on (COLDIA.bdcodemp = COL.bdcodemp and COLDIA.bdcodcol = COL.bdcodcol) ';
              WCAMPODTSHE = ' COLDIA.bddatacol ';
            END
         ELSE
            BEGIN
              WCAMPOSSHE = ' COLDIA.BDDATASHE, COLDIA.BDSALSHE, COLDIA.bdcodshe, BSHE.BDDESCSHE ';
              WJOINSHE = ' join VRH_EMP_TCOL_SHE COLDIA on (COLDIA.BDCODEMP = COL.BDCODEMP and COLDIA.BDCODCOL = COL.BDCODCOL) '||
                         ' join VRH_BASE_TSALHORESP BSHE on (BSHE.bdcodemp = COLDIA.bdcodemp and BSHE.bdcodshe = COLDIA.bdcodshe)';
              WCAMPODTSHE = ' COLDIA.BDDATASHE ';
            END

           WSQL2 = 'select COL.bdcodemp, COL.bdcodcol, COL.bdnomcol, COL.bddataadmcol, ADM.bdsalinicol , COLDIA.bdcodmas, MAUMSAL.BDDESCMAS, VINCDIA.BDCODTFUC, '||WCAMPOSSHE;
           WSQL2 = WSQL2 || ' from VRH_EMP_TCOLCON COL ';
           WSQL2 = WSQL2 || WJOINSHE;
           WSQL2 = WSQL2 || ' left outer join VRH_EMP_TCOL_ADM ADM ON (ADM.bdcodemp = COL.bdcodemp AND ADM.bdcodcol = COL.bdcodcol) ';
           WSQL2 = WSQL2 || ' left outer join VRH_BASE_TMOTAUMSAL MAUMSAL on (MAUMSAL.bdcodmas = COLDIA.bdcodmas) ';
           WSQL2 = WSQL2 || ' left outer join VRH_EMP_TCOL_VINCDIA VINCDIA on (VINCDIA.bdcodemp = COLDIA.bdcodemp and VINCDIA.bdcodcol = COLDIA.bdcodcol and VINCDIA.BDDATACOL = '||WCAMPODTSHE||') ';
           WSQL2 = WSQL2 || ' where COL.bdcodemp = '||wcodemp||' and COL.bdcodcol = '||wcodcol;
           WSQL2 = WSQL2 || ' order by COL.bdcodemp, COL.bdcodcol ,'||WCAMPODTSHE;
    
           WCONT = 0;
           FOR EXECUTE STATEMENT WSQL2 INTO
             WCODEMP,
             WCODCOL,
             WNOMCOL,
             WDATAADMCOL,
             WSALINICOL,
             WCODMAS,
             WDESCMAS,
             WCODTFUC,
             WDATACOL,
             WSALBASECOL,
             WCODSHE,
             WDESCSHE
           DO
           BEGIN
             IF (wcont = 0) THEN
                BEGIN
                   BDCODEMP = WCODEMP;
                   BDCODCOL = WCODCOL;
                   BDNOMCOL = WNOMCOL;
                   BDDATAADMCOL = WDATAADMCOL;
                   IF (WSALINICOL IS NULL) THEN
                      BDSALINICOL = 0;
                   ELSE
                      BDSALINICOL = WSALINICOL;
                   BDDATACOL = WDATACOL;
                   BDSALBASECOL = WSALBASECOL;
                   BDCODMAS = WCODMAS;
                   BDDESCMAS = WDESCMAS;
                   BDCODSHE = WCODSHE;
                   BDDESCSHE = WDESCSHE;
                   IF ((PRTRAZERSALINI = 1) OR (BDSALBASECOL <> BDSALINICOL)) THEN
                      SUSPEND;
                END
             ELSE
                BEGIN
                  IF ((BDCODEMP <> WCODEMP) OR (BDCODCOL <> WCODCOL) OR (BDSALBASECOL <> WSALBASECOL) OR (BDCODTFUC <> WCODTFUC)) THEN
                     BEGIN
                        BDCODEMP = WCODEMP;
                        BDCODCOL = WCODCOL;
                        BDNOMCOL = WNOMCOL;
                        BDDATAADMCOL = WDATAADMCOL;
                        IF (WSALINICOL IS NULL) THEN
                           BDSALINICOL = 0;
                        ELSE
                           BDSALINICOL = WSALINICOL;
                        BDDATACOL = WDATACOL;
                        BDSALBASECOL = WSALBASECOL;
                        BDCODMAS = WCODMAS;
                        BDDESCMAS = WDESCMAS;
                        BDCODTFUC = WCODTFUC;
                        BDCODSHE = WCODSHE;
                        BDDESCSHE = WDESCSHE;
                        IF ((PRTRAZERSALINI = 1) OR (BDSALBASECOL <> BDSALINICOL)) THEN
                           SUSPEND;
                     END
                END
    
             WCONT = 1;
           END
       END
     END
   ELSE
     SUSPEND;
END
^

/* Alter (SP_VEF_EXPREG30_GIASP) */
ALTER PROCEDURE SP_VEF_EXPREG30_GIASP(PRCODEMP VARCHAR(10000),
PRREFERENCIAINICIAL INTEGER,
PRREFERENCIAFINAL INTEGER)
 RETURNS(BDCODEMP INTEGER,
BDREFERENCIA INTEGER,
BDCODEST INTEGER,
BDCODDIPAM VARCHAR(3),
BDVALOR NUMERIC(18,6))
 AS
DECLARE VARIABLE WSQL VARCHAR(32700);
DECLARE VARIABLE WWHERE VARCHAR(32700);
DECLARE VARIABLE WPREFIXONAT INTEGER;
DECLARE VARIABLE WCODESTSP INTEGER;
DECLARE VARIABLE WSIGLAUF VARCHAR(2);
BEGIN

  EXECUTE PROCEDURE sp_seta_competencia(PRREFERENCIAFINAL, NULL, NULL);

  SELECT c.bdcodgiars FROM tuf u,tcidade c WHERE u.bdcoduf = c.bdcoduf AND (UPPER(c.bdnomcid) = 'SÃO PAULO')  AND UPPER(u.bdsiglauf) = 'SP' INTO WCODESTSP;

  wwhere = '';
  IF (:PRCODEMP IS NOT NULL) THEN
    wwhere = :PRCODEMP;

  IF (:PRREFERENCIAINICIAL IS NOT NULL) THEN
     BEGIN
       IF (wwhere <> '') THEN
          wwhere = wwhere||' and ';
--       else
--          wwhere = wwhere||' where ';

       wwhere = wwhere||'M.bdreflan >= '||:PRREFERENCIAINICIAL;

       IF (wwhere <> '') THEN
          wwhere = wwhere||' and ';

       wwhere = wwhere||'M.bdreflan <= '||:PRREFERENCIAFINAL;
     END

  wsql = 'select ent.bdcodemp,ent.bdreflan referencia,null bdprefixonat, ';
  wsql = wsql||'       upper(ent.bdsiglauf),ent.bdcodest,''1.1'' BDCODDIPAM, ';
  wsql = wsql||'       (coalesce(ent.bdbaseicms, 0) + coalesce(ent.bdisentasicms, 0) + coalesce(ent.bdoutrasicms, 0)) bdVALOR ';
  wsql = wsql||'from (select ent.bdcodemp,ent.bdreflan,uf.bdsiglauf,cid.bdcodgiars bdcodest, ';
  wsql = wsql||'             coalesce(sum(itens.bdbaseicms), 0) bdbaseicms, ';
  wsql = wsql||'             coalesce(sum(itens.bdisentasicms), 0) bdisentasicms, ';
  wsql = wsql||'             coalesce(sum(itens.bdoutrasicms), 0) bdoutrasicms ';
  wsql = wsql||'      from (select m.* from vef_emp_tmovent m where '||wwhere||') ent ';
  wsql = wsql||'      inner join vef_emp_tmoventitens itens on itens.bdcodemp = ent.bdcodemp and itens.bdchave = ent.bdchave and substring(itens.bdprefixonat from 1 for 1) = ''1'' ';
  wsql = wsql||'      inner join vw_tterceiro ter on ter.bdcodter = ent.bdcodter and upper(ter.bdpruralter) = ''TRUE'' ';
  wsql = wsql||'      left outer join tcidade cid on cid.bdcodcid = ter.bdcodcid ';
  wsql = wsql||'      left outer join tuf uf on uf.bdcoduf = cid.bdcoduf and uf.bdsiglauf <> ''EX'' ';
  wsql = wsql||'      group by bdcodemp, bdreflan, bdsiglauf, bdcodest) ent ';

  FOR EXECUTE STATEMENT wsql
  INTO
    BDCODEMP,
    BDREFERENCIA,
    WPREFIXONAT,
    WSIGLAUF,
    BDCODEST,
    BDCODDIPAM,
    BDVALOR
  DO
  BEGIN
    SUSPEND;
  END

  wsql = 'SELECT m.bdcodemp, m.bdreflan referencia, i.bdprefixonat, upper(u.bdsiglauf),c.bdcodest, NULL BDCODDIPAM, ';
  wsql = wsql||' SUM(coalesce(i.bdbaseicms, 0) + coalesce(i.bdisentasicms, 0) + coalesce(i.bdoutrasicms, 0)) bdVALOR ';
  wsql = wsql||' FROM vef_emp_tmovSAI m, vef_emp_tmovSAIitens i, vw_tterceiro t, tcidade c, tuf u';
  wsql = wsql||' WHERE (i.bdcodemp = m.bdcodemp AND i.bdchave = m.bdchave)';
  wsql = wsql||' and (t.bdcodter = m.bdcodter)';
  wsql = wsql||' and (m.bdcodcid = c.bdcodcid and u.bdCODUF = c.bdCODUF)';
  wsql = wsql||' and (u.bdsiglauf <> '''||'EX'||''')';
  wsql = wsql||' and ((coalesce(i.bdbaseicms, 0) + coalesce(i.bdisentasicms, 0) + coalesce(i.bdoutrasicms, 0)) > 0) AND (';
  wsql = wsql||wwhere;
  wsql = wsql||') and (((i.bdprefixonat >= 5351) and (i.bdprefixonat <= 5399))';
  wsql = wsql||' or (((i.bdprefixonat >= 6351) and (i.bdprefixonat <= 6399)) or';
  wsql = wsql||' ((i.bdprefixonat >= 7351) and (i.bdprefixonat <= 7399)) or';
  wsql = wsql||' ((i.bdprefixonat >= 5301) and (i.bdprefixonat <= 5349)) or';
  wsql = wsql||' ((i.bdprefixonat >= 6301) and (i.bdprefixonat <= 6349)) or';
  wsql = wsql||' ((i.bdprefixonat >= 7301) and (i.bdprefixonat <= 7349)) or';
  wsql = wsql||' ((i.bdprefixonat >= 5251) and (i.bdprefixonat <= 5299)) or';
  wsql = wsql||' ((i.bdprefixonat >= 6251) and (i.bdprefixonat <= 6299)) or';
  wsql = wsql||' ((i.bdprefixonat >= 7251) and (i.bdprefixonat <= 7299)))';
  wsql = wsql||') group by m.bdcodemp, referencia, i.bdprefixonat, u.bdsiglauf,c.bdcodest';

  FOR EXECUTE STATEMENT wsql
  INTO
    BDCODEMP,
    BDREFERENCIA,
    WPREFIXONAT,
    WSIGLAUF,
    BDCODEST,
    BDCODDIPAM,
    BDVALOR
  DO
  BEGIN
    IF (WSIGLAUF = 'SP') THEN
       BDCODEST = WCODESTSP;

    -- Transportes
    IF (((wprefixonat >= 5351) AND (wprefixonat <= 5399)) OR
       ((wprefixonat >= 6351) AND (wprefixonat <= 6399)) OR
       ((wprefixonat >= 7351) AND (wprefixonat <= 7399))) THEN
       BDCODDIPAM = '2.3';
    ELSE
       -- Comunicações
       IF (((wprefixonat >= 5301) AND (wprefixonat <= 5349)) OR
          ((wprefixonat >= 6301) AND (wprefixonat <= 6349)) OR
          ((wprefixonat >= 7301) AND (wprefixonat <= 7349))) THEN
          BDCODDIPAM = '2.4';
       ELSE
          -- Energia Elétrica
          IF (((wprefixonat >= 5251) AND (wprefixonat <= 5299)) OR
             ((wprefixonat >= 6251) AND (wprefixonat <= 6299)) OR
             ((wprefixonat >= 7251) AND (wprefixonat <= 7299))) THEN
             BDCODDIPAM = '2.5';
    SUSPEND;
  END
END
^

/* Alter (SP_VRH_DECLARACAO_RENDIMENTOS_7) */
ALTER PROCEDURE SP_VRH_DECLARACAO_RENDIMENTOS_7(PRCODEMP VARCHAR(32000),
PRCODCOL VARCHAR(32000),
PRANO INTEGER,
PREXPORTACAO INTEGER = 0)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDCODDPN INTEGER,
BDTIPO INTEGER,
BDINFORMACAO VARCHAR(250),
BDVALOR NUMERIC(18,6))
 AS
declare variable WSQL varchar(5000);
declare variable WCODEMP integer;
declare variable WCODCOL integer;
declare variable WCODDPN integer;
declare variable WNOMDPN varchar(70);
declare variable WCPFDPN varchar(14);
declare variable WVALDPN numeric(18,6);
declare variable WVALREND numeric(18,6);
declare variable WDESCREND varchar(200);
declare variable WCNPJ varchar(18);
declare variable WRS varchar(80);
declare variable WNRBENEF varchar(20);
BEGIN
  /*
    PENSÃO ALIMENTÍCIA
  */

  wsql = 'select FOL.BDCODDPN,FOL.BDCODEMP,FOL.BDCODCOL,DEP.BDNOMBENEF BDNOMEDPN,DEP.BDCPFBENEF BDCPFDPN, SUM(FOL.BDVALPEND) '||
         'from VWRH_PENSAO_DEP FOL '||
         'join VRH_EMP_TCOLCON_BENEF DEP on (DEP.BDCODEMP = FOL.BDCODEMP and DEP.BDCODCOL = FOL.BDCODCOL and DEP.BDCODBENEF = FOL.BDCODDPN) ';

  wsql = wsql||'where FOL.BDTIPO <> 9 and FOL.BDTIPO <> 1 and FOL.BDREFPAGPEND >= '||prano||'01 and FOL.BDREFPAGPEND <= '||prano||'12 ';

  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||' and '||prcodemp;

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||' and '||prcodcol;

  wsql = wsql||' group by FOL.BDCODDPN,FOL.BDCODEMP,FOL.BDCODCOL,DEP.BDNOMBENEF,DEP.BDCPFBENEF ';

  FOR EXECUTE STATEMENT wsql INTO :wcoddpn, :wcodemp, :wcodcol, :wnomdpn, :wcpfdpn, :wvaldpn DO
    BEGIN
      bdcodemp     = :wcodemp;
      bdcodcol     = :wcodcol;
      bdcoddpn     = :wcoddpn;
      bdtipo       = 0;
      bdinformacao = 'Pensão alimentícia: '||COALESCE(:wnomdpn, '')||', CPF: '||COALESCE(:wcpfdpn, '   .   .   -  ');
      bdvalor      = wvaldpn;
      SUSPEND;
    END

  wsql = 'select FOL.BDCODDPN,FOL.BDCODEMP,FOL.BDCODCOL,DEP.BDNOMBENEF BDNOMEDPN,DEP.BDCPFBENEF BDCPFDPN, SUM(FOL.BDVALPEND) '||
         'from VWRH_PENSAO_DEP FOL '||
         'join VRH_EMP_TCOLCON_BENEF DEP on (DEP.BDCODEMP = FOL.BDCODEMP and DEP.BDCODCOL = FOL.BDCODCOL and DEP.BDCODBENEF = FOL.BDCODDPN) ';

  wsql = wsql||'where FOL.BDTIPO = 1 and FOL.BDREFPAGPEND >= '||prano||'01 and FOL.BDREFPAGPEND <= '||prano||'12 ';

  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||' and '||prcodemp;

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||' and '||prcodcol;

  wsql = wsql||' group by FOL.BDCODDPN,FOL.BDCODEMP,FOL.BDCODCOL,DEP.BDNOMBENEF,DEP.BDCPFBENEF ';

  FOR EXECUTE STATEMENT wsql INTO :wcoddpn, :wcodemp, :wcodcol, :wnomdpn, :wcpfdpn, :wvaldpn DO
    BEGIN
      bdcodemp     = :wcodemp;
      bdcodcol     = :wcodcol;
      bdcoddpn     = :wcoddpn;
      bdtipo       = 0;
      bdinformacao = 'Pensão alimentícia 13º salário: '||COALESCE(:wnomdpn, '')||', CPF: '||COALESCE(:wcpfdpn, '   .   .   -  ');
      bdvalor      = wvaldpn;
      SUSPEND;
    END

  wsql = 'select FOL.BDCODEMP,FOL.BDCODCOL,SUM(FOL.BDVALPEND) '||
         'from VWRH_PENSAO_DEP FOL ';

  wsql = wsql||'where FOL.BDTIPO = 9 and FOL.BDREFPAGPEND >= '||prano||'01 and FOL.BDREFPAGPEND <= '||prano||'12 ';

  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||' and '||prcodemp;

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||' and '||prcodcol;

  wsql = wsql||' group by FOL.BDCODEMP,FOL.BDCODCOL ';

  FOR EXECUTE STATEMENT wsql INTO :wcodemp, :wcodcol, :wvaldpn DO
    BEGIN
      bdcodemp     = :wcodemp;
      bdcodcol     = :wcodcol;
      bdcoddpn     = :wcoddpn;
      bdtipo       = 0;
      bdinformacao = 'Pensão alimentícia outros: ';
      bdvalor      = wvaldpn;
      SUSPEND;
    END


  /*
    RENDIMENTOS ISENTOS
  */

  wsql = 'select FOL.BDCODEMP,FOL.BDCODCOL,SUM(COALESCE(FOL.BDRENDISENTOS, 0.00)),COALESCE(FOL.BDDRENDISENTOS, '''||''||''') BDDESC from SP_VRHF_DADOS_IR_DIRF(';

  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||''''||prcodemp||''',';
  ELSE
     wsql = wsql||'null,';

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||''''||prcodcol||''',';
  ELSE
     wsql = wsql||'null,';

  wsql = wsql||prano||'01,'||prano||'12,0,'||''''||''''||','||prexportacao||') FOL ';

  wsql = wsql||'where FOL.BDRENDISENTOS > 0 ';

  wsql = wsql||' group by FOL.BDCODEMP,FOL.BDCODCOL,BDDESC';

  FOR EXECUTE STATEMENT wsql
  INTO
    :bdcodemp,
    :bdcodcol,
    :wvalrend,
    :wdescrend
  DO
    BEGIN
      bdinformacao = 'Rendimentos isentos: '||:wdescrend;
      bdtipo       = 1;
      bdvalor      = :wvalrend;
      bdcoddpn     = 0;
      SUSPEND;
    END


  /*
    PLANOS DE SAÚDE
  */
  if (PREXPORTACAO = 0) then -- Quando for exportação não precisa levar a descrição do plano de saúde Almir 16/02/2015
     begin
       wsql = 'select X.BDCODEMP, X.BDCODCOL, X.BDCODDPN, X.BDNOME, X.BDCPF, X.BDCNPJ, X.BDRAZAOSOCIAL, SUM(X.BDVALOR) BDVALOR '||
              'from SP_VRH_DEC_REND_7_2(';
     
       IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
          wsql = wsql||''''||prcodemp||''',';
       ELSE
          wsql = wsql||'null,';
     
       IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
          wsql = wsql||''''||prcodcol||''',';
       ELSE
          wsql = wsql||'null,';
     
       wsql = wsql||prano||') X ';
       wsql = wsql||'group by X.BDCODEMP,X.BDCODCOL,X.BDCODDPN,X.BDNOME,X.BDCPF,X.BDCNPJ,X.BDRAZAOSOCIAL ';
     
       FOR EXECUTE STATEMENT wsql
       INTO
         :bdcodemp,
         :bdcodcol,
         :bdcoddpn,
         :wnomdpn,
         :wcpfdpn,
         :wcnpj,
         :wrs, 
         :wvaldpn
       DO
         BEGIN
           IF (bdcoddpn = 0) THEN
              bdinformacao = 'Saúde: CNPJ: '||TRIM(:wcnpj)||', '||TRIM(:wrs)||', CPF: '||:wcpfdpn||', Beneficiário: '||TRIM(:wnomdpn);
           ELSE
              bdinformacao = 'Saúde: CNPJ: '||TRIM(:wcnpj)||', '||TRIM(:wrs)||', CPF: '||:wcpfdpn||', Dependente: '||TRIM(:wnomdpn);
           bdtipo       = 3;
           bdvalor      = :wvaldpn;
           SUSPEND;
         END
     end


  /*
    PLR
  */
  wsql = 'select X.BDCODEMP, X.BDCODCOL, SUM(COALESCE(X.BDRENDTRIBUTAVEL,0)) - SUM(COALESCE(X.BDINSS,0)) - SUM(COALESCE(X.BDDESPMED,0)) - SUM(COALESCE(X.BDPENJUD,0)) - '||
         'SUM(COALESCE(X.BDPENALIM,0)) - SUM(COALESCE(X.BDIR,0)) BDLIQUIDO '||
         'from SP_VRHF_DADOS_IR_FA(';



  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||''''||prcodemp||''',';
  ELSE
     wsql = wsql||'null,';

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||''''||prcodcol||''',';
  ELSE
     wsql = wsql||'null,';

  wsql = wsql||prano||'01,'||prano||'12,0,null,1,0) X ';

  wsql = wsql||'join VRH_BASE_TTIPOCALCAVUL FA on (FA.BDCODTCA = X.BDCODTCA and FA.BDCODCTP = 7) ';

  wsql = wsql||' group by X.BDCODEMP,X.BDCODCOL ';

  FOR EXECUTE STATEMENT wsql
  INTO
    :bdcodemp,
    :bdcodcol,
    :wvaldpn
  DO
    BEGIN
      IF (wvaldpn > 0) THEN
         BEGIN
           bdcoddpn     = 0;
           bdinformacao = 'O total informado na Linha 03 do Quadro 5 já inclui o valor total pago a título de PLR correspondente a';
           bdtipo       = 4;
           bdvalor      = :wvaldpn;
           SUSPEND;
        END
    END


  /*
    PREVIDÊNCIA PRIVADA
   */

  wsql = 'select BDCODEMP, BDCODCOL, SUM(BDPREVPRIV) from (';
  wsql = wsql||'select BDCODEMP, BDCODCOL, BDPREVPRIV from SP_VRHF_DADOS_IR_FN(';

  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||''''||prcodemp||''',';
  ELSE
     wsql = wsql||'null,';

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||''''||prcodcol||''',';
  ELSE
     wsql = wsql||'null,';

  wsql = wsql||prano||'01,'||prano||'12,0,null,1) ';
  wsql = wsql||') ';
  wsql = wsql||' group by BDCODEMP,BDCODCOL ';


  for execute statement wsql into :wcodemp, :wcodcol, :wvaldpn do
    begin
      if (wvaldpn <> 0) then
         begin
           bdcodemp     = wcodemp;
           bdcodcol     = wcodcol;
           bdcoddpn     = 0;
           bdinformacao = 'Valor pago a título de Previdência Privada';
           bdtipo       = 5;
           bdvalor      = :wvaldpn;
           SUSPEND;
         end
    end


  /*

    Lançamentos anuais para DIRF.

   */

  wsql = 'select FOL.BDCODEMP,FOL.BDCODCOL, FOL.BDPREVPRIVFANO, SUBSTRING(FOL.BDQUADRO3L3 FROM 1 FOR 250) from VRH_EMP_TCOLCON_ANO FOL where FOL.BDANOFUNC = '||prano;
  wsql = wsql||' and FOL.BDPREVPRIVFANO <> 0 ';

  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||' and '||prcodemp;

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||' and '||prcodcol;

  for execute statement wsql into :bdcodemp, :bdcodcol, :bdvalor, :bdinformacao do
    begin
      bdcoddpn = 0;
      bdtipo = 6;
      suspend;
    end

  wsql = 'select FOL.BDCODEMP,FOL.BDCODCOL, FOL.BDOUT13FANO, SUBSTRING(FOL.BDQUADRO5L3 FROM 1 FOR 250) from VRH_EMP_TCOLCON_ANO FOL where FOL.BDANOFUNC = '||prano;
  wsql = wsql||' and FOL.BDOUT13FANO <> 0 ';

  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||' and '||prcodemp;

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||' and '||prcodcol;

  for execute statement wsql into :bdcodemp, :bdcodcol, :bdvalor, :bdinformacao do
    begin
      bdcoddpn = 0;
      bdtipo = 7;
      suspend;
    end

  /*

   Lançamentos anuais para pensão da DIRF.

   */

  wsql = 'select FOL.BDCODEMP, FOL.BDCODCOL, FOL.BDCODBENEF, FOL.BDVALDIRF, BEN.BDNOMBENEF, BEN.BDCPFBENEF from VRH_EMP_TVALBENEF_DIRF FOL '||
         'join VRH_EMP_TCOLCON_BENEF BEN on (BEN.BDCODEMP = FOL.BDCODEMP and BEN.BDCODCOL = FOL.BDCODCOL and BEN.BDCODBENEF = FOL.BDCODBENEF) '||
         'where FOL.BDANODIRF = '||prano;

  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||' and '||prcodemp;

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||' and '||prcodcol;

  for execute statement wsql into :wcodemp, :wcodcol, :wcoddpn, :wvaldpn, :wnomdpn, :wcpfdpn do
    begin
      bdcodemp = wcodemp;
      bdcodcol = wcodcol;
      bdcoddpn = wcoddpn;
      bdtipo       = 8;
      bdinformacao = 'Pensão alimentícia: '||COALESCE(:wnomdpn, '')||', CPF: '||COALESCE(:wcpfdpn, '   .   .   -  ');
      bdvalor      = wvaldpn;
      suspend;
    end

  /*
    Afastamentos com benefícios
  */

  wsql = 'select FOL.BDCODEMP, FOL.BDCODCOL, COALESCE(S.BDNBENEFICIOSIT, '' '') from VRH_EMP_TCOLCON FOL '||
         'join VRH_EMP_TSITCOL S on (S.BDCODEMP = FOL.BDCODEMP and S.BDCODCOL = FOL.BDCODCOL) '||
         'where ((S.BDDATAINISEFIP between '||'''01/01/'||prano||''' and '||'''12/31/'||prano||''') or (s.BDDATAFIMSIT between '||'''01/01/'||prano||''' and '||'''12/31/'||prano||''')) and '||
         ' (coalesce(S.BDNBENEFICIOSIT, ''0'') <> ''0'') and (trim(S.BDNBENEFICIOSIT) <> '''')';

  IF (prcodemp IS NOT NULL AND prcodemp <> '') THEN
     wsql = wsql||' and '||prcodemp;

  IF (prcodcol IS NOT NULL AND prcodcol <> '') THEN
     wsql = wsql||' and '||prcodcol;

  for execute statement wsql into :wcodemp, :wcodcol, :wnrbenef do
    begin
      bdcodemp = :wcodemp;
      bdcodcol = :wcodcol;
      bdinformacao = 'Número do benefício: '||:wnrbenef;
      bdvalor = 0;
      suspend;
    end
END
^

/* empty dependent procedure body */
/* Clear: SP_VRHM_FF_OP_REF for: SP_VRHM_FF_OP_ANO */
ALTER PROCEDURE SP_VRHM_FF_OP_REF(PREMP VARCHAR(32000) = '',
PRCOL VARCHAR(32000) = '',
PRREFINI INTEGER = 0,
PRREFFIM INTEGER = 0)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDREF INTEGER,
BDNOME VARCHAR(70),
BDTIPO INTEGER,
BDCODPLACONVSAU INTEGER,
BDCODOPERADORA INTEGER,
BDCODCONVENIO INTEGER,
BDMENSALIDADE NUMERIC(15,6),
BDCOPARTICIPACAO NUMERIC(15,6))
 AS
 BEGIN SUSPEND; END
^

/* Alter (SP_VRHM_FF_OP_ANO) */
ALTER PROCEDURE SP_VRHM_FF_OP_ANO(PREMP VARCHAR(32000) = '',
PRCOL VARCHAR(32000) = '',
PRANO INTEGER = 0)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDREF INTEGER,
BDNOME VARCHAR(70),
BDTIPO INTEGER,
BDCODPLACONVSAU INTEGER,
BDCODOPERADORA INTEGER,
BDCODCONVENIO INTEGER,
BDMENSALIDADE NUMERIC(15,6),
BDCOPARTICIPACAO NUMERIC(15,6))
 AS
declare variable WDATA varchar(10);
declare variable WINI integer;
declare variable WFIM integer;
declare variable WCOMPANT integer;
declare variable WSQL varchar(5000);
declare variable WEMP varchar(20000);
declare variable WCOL varchar(30000);
declare variable WDESPMED numeric(18,6);
BEGIN
  /* Procedure Text */
--  suspend;
 wIni = CAST(CAST(prano AS VARCHAR(4))||'01' AS INTEGER);
 wfim = CAST(CAST(prano AS VARCHAR(4))||'12' AS INTEGER);
 WCOMPANT = 0;

 IF ((premp IS NULL) OR (premp = '')) THEN
    wemp = 'null';
 ELSE
    wemp = ''''||SUBSTRING(premp FROM 1 FOR 20000)||'''';

 IF ((prcol IS NULL) OR (prcol = '')) THEN
    wcol = 'null';
 ELSE
    wcol = ''''||SUBSTRING(prcol FROM 1 FOR 30000)||'''';


 wsql = ' select a.bdref,a.bdcodemp,a.bdcodcol '||
          ' from ('||
              'select * from SP_VRHF_DADOS_IR_DIRF('||wemp||','||wcol||','||wini||','||wfim||') where (BDDESPMED > 0) and BDTIPO < 9000) a ';
 wsql = wsql||' group by a.bdref, a.bdcodemp, a.bdcodcol ';
 wsql = wsql||' order by a.bdref';

 FOR EXECUTE STATEMENT wsql INTO
   bdref,bdcodemp,bdcodcol DO
   BEGIN
     IF (wcompant <> bdref) THEN
        BEGIN
          wData = SUBSTRING(''||:bdref FROM 5 FOR 2)||'/01/'||SUBSTRING(''||:bdref FROM 1 FOR 4);
          EXECUTE PROCEDURE sp_seta_competencia(:bdref,:wData,NULL);
          wcompant = bdref;
        END

     FOR SELECT bdnome, bdtipo, b.bdcodplaconvsau, b.bdcodoperadora, b.bdcodconvenio, b.bdvalormensalidade, b.bdvalorcopar FROM vw_vrhm_emp_val_tod_op_atu b
       WHERE bdcodemp = :bdcodemp AND bdcodcol = :bdcodcol AND ((COALESCE(b.bdvalormensalidade,0) > 0) OR (COALESCE(b.bdvalorcopar,0) > 0))
       INTO bdnome,bdtipo,bdcodplaconvsau, bdcodoperadora, bdcodconvenio,bdmensalidade, bdcoparticipacao DO
       BEGIN
         SUSPEND;
       END
   END

 wIni = CAST(CAST(prano AS VARCHAR(4))||'01' AS INTEGER);
 wfim = CAST(CAST(prano AS VARCHAR(4))||'12' AS INTEGER);
 WCOMPANT = 0;

 IF ((premp IS NULL) OR (premp = '')) THEN
    wemp = 'null';
 ELSE
    wemp = ''''||SUBSTRING(premp FROM 1 FOR 20000)||'''';

 IF ((prcol IS NULL) OR (prcol = '')) THEN
    wcol = 'null';
 ELSE
    wcol = ''''||SUBSTRING(prcol FROM 1 FOR 30000)||'''';


 wsql = ' select a.bdref,a.bdcodemp,a.bdcodcol,a.bddespmed '||
          ' from ('||
              'select * from SP_VRHF_DADOS_IR_DIRF('||wemp||','||wcol||','||wini||','||wfim||') where (BDDESPMED > 0) and BDTIPO >= 9000) a ';
 wsql = wsql||' group by a.bdref,a.bdcodemp,a.bdcodcol,a.bddespmed ';
 wsql = wsql||' order by a.bdref';

 FOR EXECUTE STATEMENT wsql INTO
   bdref,bdcodemp,bdcodcol,:wdespmed DO
   BEGIN
     IF (wcompant <> bdref) THEN
        BEGIN
          wData = SUBSTRING(''||:bdref FROM 5 FOR 2)||'/01/'||SUBSTRING(''||:bdref FROM 1 FOR 4);
          EXECUTE PROCEDURE sp_seta_competencia(:bdref,:wData,NULL);
          wcompant = bdref;
        END

     FOR SELECT bdnome, 9000, b.bdcodplaconvsau, b.bdcodoperadora, b.bdcodconvenio, :wdespmed, 0
         FROM vw_vrhm_emp_val_tod_op_atu b
         WHERE bdcodemp = :bdcodemp AND bdcodcol = :bdcodcol AND ((COALESCE(b.bdvalormensalidade,0) > 0) OR (COALESCE(b.bdvalorcopar,0) > 0))
     INTO bdnome,bdtipo,bdcodplaconvsau, bdcodoperadora, bdcodconvenio,bdmensalidade, bdcoparticipacao DO
       BEGIN
         SUSPEND;
       END
   END

END
^

/* Alter (SP_VRHM_FF_OP_REF) */
ALTER PROCEDURE SP_VRHM_FF_OP_REF(PREMP VARCHAR(32000) = '',
PRCOL VARCHAR(32000) = '',
PRREFINI INTEGER = 0,
PRREFFIM INTEGER = 0)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDREF INTEGER,
BDNOME VARCHAR(70),
BDTIPO INTEGER,
BDCODPLACONVSAU INTEGER,
BDCODOPERADORA INTEGER,
BDCODCONVENIO INTEGER,
BDMENSALIDADE NUMERIC(15,6),
BDCOPARTICIPACAO NUMERIC(15,6))
 AS
DECLARE VARIABLE WANOINI INTEGER;
DECLARE VARIABLE WANOFIM INTEGER;
BEGIN
  /* Procedure Text */

  wAnoIni = CAST(SUBSTRING(CAST(prrefini AS VARCHAR(6)) FROM 1 FOR 4) AS INTEGER);
  wAnoFim = CAST(SUBSTRING(CAST(prreffim AS VARCHAR(6)) FROM 1 FOR 4) AS INTEGER);

  WHILE (wanoini <= wanofim) DO
    BEGIN
      FOR SELECT BDCODEMP, BDCODCOL, BDREF, BDNOME, BDTIPO, BDCODPLACONVSAU, BDCODOPERADORA, BDCODCONVENIO, BDMENSALIDADE, BDCOPARTICIPACAO
      FROM sp_vrhm_ff_op_ano(:premp,:prcol,:wanoini)
         INTO :BDCODEMP, :BDCODCOL, :BDREF, :BDNOME, :BDTIPO, :BDCODPLACONVSAU, :BDCODOPERADORA, :BDCODCONVENIO, :BDMENSALIDADE, :BDCOPARTICIPACAO DO
        BEGIN
          IF ((bdref >= prrefini) AND (bdref <= prreffim)) THEN
             SUSPEND;

        END



      wanoini = wanoini + 1;
    END

--  suspend;
END
^

/* empty dependent procedure body */
/* Clear: SP_VAL_RAIS_FF for: VRHF_EMP_VAL_GRRF */
ALTER PROCEDURE SP_VAL_RAIS_FF(PREMP INTEGER = 0,
PRCOL INTEGER = 0,
PRANO INTEGER = 0)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDREF INTEGER,
BDAVPREV NUMERIC(15,6),
BDFERIND NUMERIC(15,6),
BDGRATIF NUMERIC(15,6),
BDMULTA NUMERIC(15,6),
BDSINDICAL NUMERIC(15,6),
BDASSISTENCIAL NUMERIC(15,6),
BDCONFEDERATIVA NUMERIC(15,6),
BDBASEFN NUMERIC(15,6),
BDBASEA13 NUMERIC(15,6),
BDBASE13 NUMERIC(15,6),
BDBASEC13 NUMERIC(15,6),
BDORIGEM INTEGER,
BDMENSALIDADE NUMERIC(15,6))
 AS
 BEGIN SUSPEND; END
^

/* Alter (VRHF_EMP_VAL_GRRF) */
ALTER PROCEDURE VRHF_EMP_VAL_GRRF(PREMPRESA INTEGER,
PRCOLABORADOR INTEGER,
PRANOMES INTEGER,
PRANOMESPAG INTEGER,
PRTIPOCALCULO INTEGER)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDBFGTSMESANT NUMERIC(15,6),
BDFGTSMESANT NUMERIC(15,6),
BDBFGTSMES NUMERIC(15,6),
BDFGTSMES NUMERIC(15,6),
BDSALDO NUMERIC(15,6),
BDFGTS40 NUMERIC(15,6),
BDFGTS10 NUMERIC(15,6),
BDTOTAL NUMERIC(15,6))
 AS
declare variable BDMULTAPRES varchar(5);
declare variable BDMULTA10PRES varchar(5);
declare variable WVALORMULTA numeric(15,6);
declare variable WVALORMULTA2 numeric(15,6);
declare variable WVALORMULTA3 numeric(15,6);
declare variable WVALORMULTA10 numeric(15,6);
declare variable WVALORMULTA102 numeric(15,6);
declare variable WVALORMULTA103 numeric(15,6);
declare variable WSQL varchar(3000);
declare variable WSQLAUX varchar(3000);
declare variable WFGTSMESANT varchar(5);
declare variable WANOMES integer;
BEGIN
  /*PRTIPOCALCULO
    0 = normal
    1 = folha complementar
    2 = previa
  */

  IF (prtipocalculo IS NULL) THEN
     prtipocalculo = 0;

  IF (PRANOMESPAG IS NULL) THEN
     pranomespag = pranomes;

  /* Procedure Text */
  bdcodemp = prempresa;
  bdcodcol = prcolaborador;
  bdbfgtsmesant = 0;
  bdfgtsmesant = 0;
  bdbfgtsmes = 0;
  bdfgtsmes = 0;
  bdSaldo = 0;
  bdfgts40 = 0;
  bdfgts10 = 0;
  bdtotal = 0;

  IF (PRTIPOCALCULO = 2) THEN
     BEGIN
       wsqlaux = 'select p.BDMULTAPRES,p.BDMULTA10PRES,x.bdcalcfgtsmesantprev,x.bdsaldofgtsprev '||
                 'from vrh_emp_trescisao_previa x '||
                 'join vrh_base_tprepresc p on (x.bdcodpres = p.bdcodpres) '||
                 'join VW_VRHF_BASE_TEMPGPS EMP on (EMP.BDCODEMP = X.BDCODEMP) '||
                 'join VRH_BASE_TFPAS_TERCEIROS FPTER on (FPTER.BDCODFPASTERC = EMP.BDCODFPASTERC) and '||
                 '     ((FPTER.BDCODFPAS <> 868) or ((FPTER.BDCODFPAS = 868) and (X.BDREFRESCISAO < 201510))) '||
                 ' where (case when x.bddatarescisaoprev is null then '||''''||'0'||''''||
                 '             when x.bddatarescisaoprev is not null then (select bdaaaamm from aaaamm(x.bddatarescisaoprev)) '||
                 '       end) = '||''''||pranomes||''''||
                 '   and p.bdgrrfpres = 2'||
                 '   and x.bdcodcol = '||prcolaborador||
                 '   and x.bdcodemp = '||prempresa;
     END
  ELSE
  IF (PRTIPOCALCULO = 1) THEN
    wsqlaux = 'select P.BDMULTAPRES, P.BDMULTA10PRES, X.BDCALCFGTSMESANT, X.BDSALDOFGTS '||
              'from VRH_EMP_TRESCISAO X '||
              'join VRH_BASE_TPREPRESC P on (X.BDCODPRES = P.BDCODPRES) '||
              'join VW_VRHF_BASE_TEMPGPS EMP on (EMP.BDCODEMP = X.BDCODEMP) '||
              'join VRH_BASE_TFPAS_TERCEIROS FPTER on (FPTER.BDCODFPASTERC = EMP.BDCODFPASTERC) and '||
              '     ((FPTER.BDCODFPAS <> 868) or ((FPTER.BDCODFPAS = 868) and (X.BDREFRESCISAO < 201510))) '||
              'where X.BDREFRESCISAO <= '||pranomes||
              '  and P.BDGRRFPRES = 2'||
              '  and X.BDCODCOL = '||prcolaborador||
              '  and X.BDCODEMP = '||prempresa;
  ELSE
    wsqlaux = 'select P.BDMULTAPRES, P.BDMULTA10PRES, X.BDCALCFGTSMESANT, X.BDSALDOFGTS '||
              'from VRH_EMP_TRESCISAO X '||
              'join VRH_BASE_TPREPRESC P on (X.BDCODPRES = P.BDCODPRES) '||
              'join VW_VRHF_BASE_TEMPGPS EMP on (EMP.BDCODEMP = X.BDCODEMP) '||
              'join VRH_BASE_TFPAS_TERCEIROS FPTER on (FPTER.BDCODFPASTERC = EMP.BDCODFPASTERC) and '||
              '     ((FPTER.BDCODFPAS <> 868) or ((FPTER.BDCODFPAS = 868) and (X.BDREFRESCISAO < 201510))) '||
              'where X.BDREFRESCISAO = '||''''||pranomes||''''||
              '  and P.BDGRRFPRES = 2 '||
              '  and X.BDCODCOL = '||prcolaborador||
              '  and X.BDCODEMP = '||prempresa;


  FOR EXECUTE STATEMENT wsqlaux INTO :BDMULTAPRES, :BDMULTA10PRES, :wfgtsmesant, bdSaldo DO
    BEGIN
      IF (prtipocalculo = 2) THEN
        wsql = 'select coalesce(x.BDBFGTS,0)+coalesce(x.BDBFGTS13,0)+coalesce(x.BDBFGTSFEFOL,0),coalesce(x.BDFGTS,0)+coalesce(x.BDFGTS13,0)+coalesce(x.BDFGTSFEFOL,0)'||
               ' from VWRH_UNION_FNPR_FTPR_MES_BASES x '||
               ' where x.bdcodemp = '||prempresa||' and x.bdcodcol = '||prcolaborador||' and x.bdreffn = '||pranomes;
      ELSE
      IF (prtipocalculo = 0) THEN
         BEGIN
           wsql = 'select x.bdbfgtsfn+x.bdbfgts13fn+x.bdbfgtsfefn+COALESCE(sal13.bdbasefgts13,0)+COALESCE(ad13.bdbasefgtsa13,0),x.bdfgtsfn+x.bdfgts13fn+x.bdfgtsfefn+COALESCE(sal13.bdfgts13,0)+COALESCE(ad13.bdfgtsa13,0) from vrhf_sp_tfolha_normal('||''''||'colcon.bdcodemp = '||prempresa||''''||','
                                                                                                     ||''''||'colcon.bdcodcol = '||prcolaborador||''''||','||
                                                                                                     pranomes||','||pranomes||',1) x';
           wsql = wsql||' left outer join vrhf_emp_t13sal sal13 on (sal13.bdcodemp = x.bdcodemp and sal13.bdcodcol = x.bdcodcol and sal13.bdref13 = '||:pranomes||')';
           wsql = wsql||' left outer join vrhf_emp_ta13sal ad13 on (ad13.bdcodemp = x.bdcodemp and ad13.bdcodcol = x.bdcodcol and ad13.bdrefa13 = '||:pranomes||')';
         END
      ELSE
          wsql = 'select SUM(COALESCE(x.bdbfgtsfc,0)+COALESCE(x.bdbfgts13fc,0)+COALESCE(x.bdbfgtsfefc,0)),SUM(COALESCE(x.bdfgtsfc,0)+COALESCE(x.bdfgts13fc,0)+COALESCE(x.bdfgtsfefc,0)) from vrhf_sp_tfolhacomp('||''''||'colcon.bdcodemp = '||prempresa||''''||','
                                                                                                   ||''''||'colcon.bdcodcol = '||prcolaborador||''''||','||
                                                                                                   pranomes||','||pranomes||
                                                                                                   ','||pranomespag||','||pranomespag||',1) x';


      IF (prtipocalculo IN (0,1,2)) THEN
         EXECUTE STATEMENT wsql INTO : bdbfgtsmes, : bdfgtsmes;

      IF ((wfgtsmesant = 'TRUE') AND (prtipocalculo IN (0,2))) THEN
         BEGIN
           IF (SUBSTRING(CAST(pranomes AS VARCHAR(6)) FROM 5 FOR 2) = '01') THEN
              wanomes = pranomes - 1;
           ELSE
              wanomes = pranomes - 1;

           wsql = 'select COALESCE(x.bdbfgtsfn,0)+COALESCE(x.bdbfgts13fn,0)+COALESCE(x.bdbfgtsfefn,0)+COALESCE(sal13.bdbasefgts13,0)+COALESCE(ad13.bdbasefgtsa13,0),COALESCE(x.bdfgtsfn,0)+COALESCE(x.bdfgts13fn,0)+COALESCE(x.bdfgtsfefn,0)+COALESCE(sal13.bdfgts13,0)+COALESCE(ad13.bdfgtsa13,0) from vrhf_sp_tfolha_normal('||''''||'colcon.bdcodemp = '||prempresa||''''||','
                                                                                                        ||''''||'colcon.bdcodcol = '||prcolaborador||''''||','||
                                                                                                        wanomes||','||wanomes||',1) x';
           wsql = wsql||' left outer join vrhf_emp_t13sal sal13 on (sal13.bdcodemp = x.bdcodemp and sal13.bdcodcol = x.bdcodcol and sal13.bdref13 = '||:wanomes||')';
           wsql = wsql||' left outer join vrhf_emp_ta13sal ad13 on (ad13.bdcodemp = x.bdcodemp and ad13.bdcodcol = x.bdcodcol and ad13.bdrefa13 = '||:wanomes||')';
           EXECUTE STATEMENT wsql INTO : bdbfgtsmesant, : bdfgtsmesant;
         END


      IF (prtipocalculo = 1) THEN
         bdsaldo = 0;

      bdfgts10 = 0;
      bdfgts40 = 0;
      IF ((prtipocalculo IN (0,1,2)) AND (UPPER(BDMULTAPRES) = 'FALSE')) THEN
         BEGIN
           WVALORMULTA  = CAST((bdsaldo * 0.50)-0.005 AS NUMERIC(15,2));
           WVALORMULTA2 = CAST((bdfgtsmes * 0.50)-0.005 AS NUMERIC(15,2));
           WVALORMULTA3 = CAST((bdfgtsmesant * 0.50)-0.005 AS NUMERIC(15,2));

           WVALORMULTA10  = CAST((bdsaldo * 0.10)-0.005 AS NUMERIC(15,2));
           WVALORMULTA102 = CAST((bdfgtsmes * 0.10)-0.005 AS NUMERIC(15,2));
           WVALORMULTA103 = CAST((bdfgtsmesant * 0.10)-0.005 AS NUMERIC(15,2));

           IF (WVALORMULTA > 0) THEN
              BEGIN
                bdfgts10 = bdfgts10 + wvalormulta10;
                bdfgts40 = bdfgts40 + wvalormulta - bdfgts10;

                IF (UPPER(BDMULTA10PRES) <> 'TRUE') THEN
                   bdfgts10 = 0;
              END

           IF (WVALORMULTA2 > 0) THEN
              BEGIN
                bdfgts10 = bdfgts10 + wvalormulta102;
                bdfgts40 = bdfgts40 + wvalormulta2 - wvalormulta102;

                IF (UPPER(BDMULTA10PRES) <> 'TRUE') THEN
                   bdfgts10 = 0;
              END

           IF (WVALORMULTA3 > 0) THEN
              BEGIN
                bdfgts10 = bdfgts10 + wvalormulta103;
                bdfgts40 = bdfgts40 + wvalormulta3 - wvalormulta103;

                IF (UPPER(BDMULTA10PRES) <> 'TRUE') THEN
                   bdfgts10 = 0;
              END
         END
      bdtotal = bdfgts10+bdfgts40+bdfgtsmes+bdfgtsmesant;
    END
         
  SUSPEND;
END
^

/* Alter (VRHF_SP_CONS_IR_TERCEIROS) */
ALTER PROCEDURE VRHF_SP_CONS_IR_TERCEIROS(PRCODEMP VARCHAR(10000),
PRCODTER VARCHAR(32700),
PRREFINI INTEGER,
PRREFFIM INTEGER)
 RETURNS(BDCODEMP INTEGER,
BDNOMEMP DM_BDNOMEMP,
BDCODTER INTEGER,
BDNOMTER VARCHAR(100),
BDCODSERV INTEGER,
BDDATASERV DATE,
BDDATAPAGSERV DATE,
BDREF VARCHAR(6),
BDBASEIR NUMERIC(15,6),
BDVALORIR NUMERIC(15,6),
BDVALORDEP NUMERIC(15,6),
BDBASEINSS NUMERIC(15,6),
BDVALORINSS NUMERIC(15,6),
BDCPFTER VARCHAR(14),
BDCODRECOF VARCHAR(4),
BDBASEIRTOT NUMERIC(18,6))
 AS
DECLARE VARIABLE WSQL VARCHAR(32700);
DECLARE VARIABLE WWHERE VARCHAR(32700);
DECLARE VARIABLE WBDCATEGORIATER integer;
DECLARE VARIABLE WBDPERTRANSPIRTER numeric(15,2);
BEGIN
  EXECUTE PROCEDURE sp_seta_competencia(:prreffim, NULL, NULL);

  wsql = 'select SERVICO.bdcodemp, empresas.bdnomemp , SERVICO.bdcodter, tterc.bdnometer, SERVICO.BDCODSERV, SERVICO.bddataserv , SERVICO.BDDATAPAGSERV, SERVICO.bdbaseir, ' ||
               'SERVICO.bdvalorir, SERVICO.bdvalordep , SERVICO.bdbaseinss , SERVICO.bdvalorinss, tterc_doc.bdcpfter, codrec.bdcodrecof, SERVICO.BDBASEIRTOT, TERC_FGTS.bdcategoriater, TERC_FGTS.bdpertranspirter '||
         'from (';

  wsql = wsql||
         'select '||
           'calc_terc.bdcodemp, calc_terc.bdcodter, calc_terc.BDCODSERV, Serv.bddataserv , Serv.BDDATAPAGSERV, calc_terc.bdbaseir , calc_terc.bdvalorir, '||
           'calc_terc.bdvalordep , calc_terc.bdbaseinss , calc_terc.bdvalorinss, calc_terc.BDBASEIRTOT '||
         'from VRH_BASE_TSERVTERC SERV '||
         'join VRHF_BASE_CALC_TERC calc_terc on (SERV.BDCODEMP = calc_terc.BDCODEMP and SERV.BDCODTER = calc_terc.BDCODTER and SERV.BDCODSERV = calc_terc.BDCODSERV) ';

  wwhere = '';
  IF (:prrefini IS NOT NULL) THEN
     wwhere = wwhere||' Serv.BDREFPAGSERV >= '||prrefini||' ';

  IF (:prreffim IS NOT NULL) THEN
    BEGIN
       IF (wwhere <> '') THEN
          wwhere = wwhere||' and ';

       wwhere = wwhere||'Serv.BDREFPAGSERV <= '||prreffim||' ';
    END

  IF (:PRCODEMP IS NOT NULL) THEN
     BEGIN
       IF (wwhere <> '') THEN
          wwhere = wwhere||' and ';

       wwhere = wwhere||:PRCODEMP;
     END

  IF (:prcodter IS NOT NULL) THEN
     BEGIN
       IF (wwhere <> '') THEN
          wwhere = wwhere||' and ';

       wwhere = wwhere||:prcodter;
     END

  IF (wwhere <> '') THEN
     wwhere = ' where '||wwhere;

  wsql = wsql||wwhere;

  wsql = wsql||' union all ';

  wsql = wsql||
         'select '||
           'calc_terc.bdcodemp, calc_terc.bdcodter, calc_terc.bdcodserv, SERV.bddataserv, SERV.bddatapagserv, '||
           'calc_terc.bdbaseir - calc_terc.bdvalordep - calc_terc.bdvalorinss, calc_terc.bdvalorir, calc_terc.bdvalordep, calc_terc.bdbaseinss, calc_terc.bdvalorinss, calc_terc.BDBASEIRTOT '||
          'from VRH_BASE_TSERVTERC SERV '||
          'join vrhf_emp_tfichairrf_terc calc_terc on (SERV.BDCODEMP = calc_terc.BDCODEMP and SERV.BDCODTER = calc_terc.BDCODTER and SERV.BDCODSERV = calc_terc.BDCODSERV) ';

  wsql = wsql||wwhere;
  wsql = wsql||') SERVICO ';

  wsql = wsql||
--         'join VRH_BASE_TTERC TTERC on (TTERC.bdcodter = SERVICO.BDCODTER) '||

         'join (select X.* from tterceiro_ref X '||
           'join '||
             '( '||
               'select serv.BDCODTER, MAX(serv.BDREFEMP) BDREFEMP from tterceiro_ref serv where serv.BDREFEMP <= '||prreffim||' ';

  if (prcodter is not null and prcodter <> '') then
     wsql = wsql||' and '||prcodter||' ';

  wsql = wsql||
               'group by serv.BDCODTER '||
             ') Y on (Y.BDCODTER = X.BDCODTER and Y.BDREFEMP = X.BDREFEMP) '||
         ') TTERC on (TTERC.BDCODTER = SERVICO.BDCODTER) '||
         'join VW_VRH_BASE_TTERC_DOC TTERC_DOC on (TTERC_DOC.bdcodter = SERVICO.BDCODTER) '||
         'left outer join VW_VRH_BASE_TTERC_VINC TTERC_VINC on (TTERC_VINC.bdcodcol = SERVICO.BDCODTER) '||
         'left outer join VRH_BASE_TCLASSE CLASSE on (CLASSE.BDCODCLA = TTERC_VINC.BDCODCLA) '||
         'left outer join TCODREC CODREC on (CODREC.BDCODREC = CLASSE.BDCODREC) '||
         'left outer join vw_tempresas_ref empresas on (empresas.bdcodemp = SERVICO.bdcodemp ) '||
         'left outer join (select tfgts.bdcodter, tfgts.bdcategoriater, tfgts.bdpertranspirter from VRHF_BASE_TTERC_FGTSGPS tfgts '||
         ' join VW_VRHF_TTERC_FGTSGPS_REFATU TREF on (TREF.bdcodter = TFGTS.BDCODTER and TREF.bdrefter = TFGTS.bdrefterc)) TERC_FGTS on (TERC_FGTS.BDCODTER = TTERC.bdcodter) '||
         'order by SERVICO.bdcodemp, SERVICO.bdcodter, SERVICO.BDDATAPAGSERV ';


  FOR EXECUTE STATEMENT wsql
  INTO
    :BDCODEMP,
    :bdnomemp,
    :bdcodter,
    :bdnomter,
    :bdcodserv,
    :bddataserv,
    :bddatapagserv,
    :bdbaseir,
    :bdvalorir,
    :bdvalordep,
    :bdbaseinss,
    :bdvalorinss,
    :bdcpfter,
    :bdcodrecof,
    :bdbaseirtot,
    :wbdcategoriater,
    :wbdpertranspirter
  DO
  BEGIN
    IF (wbdcategoriater in (15,16,18,23,25)) THEN
      BEGIN
        bdbaseir = bdbaseirtot * (wbdpertranspirter/100);
        bdbaseirtot = bdbaseirtot - bdbaseir;
      END
    ELSE
      BEGIN
        bdbaseir = bdbaseir + bdvalordep + bdvalorinss;
      END

    bdref = SUBSTRING(CAST(BDDATAPAGSERV AS VARCHAR(10)) FROM 1 FOR 4) || SUBSTRING(CAST(BDDATAPAGSERV AS VARCHAR(10)) FROM 6 FOR 2);

    SUSPEND;
  END
END
^

/* Alter (VRHF_SP_FICHAFGTS2) */
ALTER PROCEDURE VRHF_SP_FICHAFGTS2(PRCODEMP VARCHAR(10000),
PRCODCOL VARCHAR(32700),
PRREFFIM INTEGER)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDREF INTEGER,
BDCODIND INTEGER,
BDBFGTS NUMERIC(18,6),
BDFGTS NUMERIC(18,6),
BDBFGTSFC NUMERIC(18,6),
BDFGTSFC NUMERIC(18,6),
BDBASEFGTSA13 NUMERIC(18,6),
BDFGTSA13 NUMERIC(18,6),
BDBASEFGTS13 NUMERIC(18,6),
BDFGTS13 NUMERIC(18,6),
BDBASEFGTSC13 NUMERIC(18,6),
BDFGTSC13 NUMERIC(18,6),
BDINDICEJAM NUMERIC(18,6),
BDSALDOANTFGTS NUMERIC(18,6),
BDVALORJAM NUMERIC(18,6),
BDSALDOFGTS NUMERIC(18,6),
BDSOMAFGTS NUMERIC(18,6),
BDPERCINDCOMP NUMERIC(18,6),
BDVALORINDCOMP NUMERIC(18,6))
 AS
DECLARE VARIABLE WSQLAUX VARCHAR(3000);
DECLARE VARIABLE WSQL VARCHAR(32700);
DECLARE VARIABLE WWHERE VARCHAR(32700);
DECLARE VARIABLE WANO INTEGER;
DECLARE VARIABLE WMES INTEGER;
DECLARE VARIABLE WDATA DATE;
DECLARE VARIABLE WTOTALFGTS NUMERIC(15,6);
DECLARE VARIABLE WCONTAJAM INTEGER;
DECLARE VARIABLE WVALORMES1 NUMERIC(15,6);
DECLARE VARIABLE WVALORMES2 NUMERIC(15,6);
DECLARE VARIABLE WINDJAMANT NUMERIC(15,6);
DECLARE VARIABLE WCODEMP INTEGER;
DECLARE VARIABLE WCODCOL INTEGER;
BEGIN
  IF (prreffim IS NULL) THEN
     BEGIN
       FOR SELECT CURRENT_DATE FROM rdb$database INTO :wdata DO
       wano = EXTRACT(YEAR FROM :wdata);
       wmes = EXTRACT(MONTH FROM :wdata);
       EXECUTE PROCEDURE sp_seta_competencia(wano||wmes, NULL, NULL);
     END
  ELSE
     EXECUTE PROCEDURE sp_seta_competencia(prreffim, NULL, NULL);

  IF ((PRCODEMP IS NOT NULL) AND (prcodemp <> '')) THEN
     BEGIN
       prcodemp = UPPER(prcodemp);
       IF (POSITION('TAB.BDCODEMP',prcodemp) > 0) THEN
          BEGIN
            SELECT bdresult FROM strtran(:prcodemp,'TAB.BDCODEMP','EMP.BDCODEMP') INTO prcodemp;
          END
     END

  wsql = 'select X.BDCODEMP, '||
                'X.BDCODCOL, '||
                'X.BDREF, '||
                'EMP.BDCODIND, '||
                'SUM(X.BDBFGTSFN), '||
                'SUM(X.BDFGTSFN), '||
                'SUM(X.BDBFGTSFC), '||
                'SUM(X.BDFGTSFC), '||
                'SUM(X.BDBASEFGTSA13), '||
                'SUM(X.BDFGTSA13), '||
                'SUM(X.BDBASEFGTS13), '||
                'SUM(X.BDFGTS13), '||
                'SUM(X.BDBASEFGTSC13), '||
                'SUM(X.BDFGTSC13) ';

  wsql = wsql||'from VW_UNION_FGTS X, VW_VRHF_BASE_TEMPVINC EMP ';

  wwhere = 'where EMP.BDCODEMP = X.BDCODEMP';

  IF ((PRCODEMP IS NOT NULL) AND (prcodemp <> '')) THEN
     wwhere = wwhere||' and ('||PRCODEMP||') ';

  IF ((PRCODCOL IS NOT NULL) AND (prcodcol <> '')) THEN
     wwhere = wwhere||' and ('||PRCODCOL||') ';

  -- Group by
  wwhere = wwhere||
           ' group by X.BDCODEMP, X.BDCODCOL, X.BDREF, EMP.BDCODIND ';

  wsql = wsql||wwhere;
  wsql = wsql||' order by X.BDCODEMP, X.BDCODCOL, X.BDREF ';

  windjamant = 0;
  wcodemp = 0;
  wcodcol = 0;

  FOR EXECUTE STATEMENT wsql
    INTO
      :BDCODEMP,
      :BDCODCOL,
      :BDREF,
      :BDCODIND,
      :BDBFGTS,
      :BDFGTS,
      :BDBFGTSFC,
      :BDFGTSFC,
      :BDBASEFGTSA13,
      :BDFGTSA13,
      :BDBASEFGTS13,
      :BDFGTS13,
      :BDBASEFGTSC13,
      :BDFGTSC13
    DO
      BEGIN
        IF (wcodemp <> :bdcodemp OR (wcodcol <> :bdcodcol)) THEN
           BEGIN
             wtotalfgts = 0;
             wcontajam = 0;
             wvalormes1 = NULL;
             wvalormes2 = NULL;
      
             wcodemp = :bdcodemp;
             wcodcol = :bdcodcol;
           END
    

        wcontajam = wcontajam + 1;
    
        bdindicejam = 0;
    
        IF (wcontajam > 2) THEN
        BEGIN
          IF ((:bdcodind > 0) AND (:bdref > 0)) THEN
          BEGIN
            wsqlaux = 'select x.bdvalind from TINDVAL x where x.bdcodind = '||:bdcodind||' and x.bdrefind = '||:bdref;
            EXECUTE STATEMENT wsqlaux INTO :bdindicejam;
          END
          ELSE
          BEGIN
            bdindicejam = 0;
          END
        END

        wsqlaux = 'select first 1 coalesce(bdindcomp, 0) bdindcomp from VRHF_BASE_TEMPGPS x where x.bdcodemp = '||wcodemp||' and x.bdrefemp <= '||:bdref||' order by bdcodemp, bdrefemp desc';
        EXECUTE STATEMENT wsqlaux INTO :BDPERCINDCOMP;

        BDVALORINDCOMP = 0;
        if (BDPERCINDCOMP > 0) then
          begin
            BDVALORINDCOMP = TRUNC((BDBFGTS * (BDPERCINDCOMP / 100)), 2) +
                             TRUNC((BDBFGTSFC * (BDPERCINDCOMP / 100)), 2) +
                             TRUNC((BDBASEFGTSA13 * (BDPERCINDCOMP / 100)), 2) +
                             TRUNC((BDBASEFGTS13 * (BDPERCINDCOMP / 100)), 2) +
                             TRUNC((BDBASEFGTSC13 * (BDPERCINDCOMP / 100)), 2);
          end
    
        wtotalfgts = wtotalfgts + bdfgts + bdfgts13 + bdfgtsa13 + bdfgtsfc;
        bdsaldoantfgts = 0;
        bdsomafgts = bdfgts + bdfgts13 + bdfgtsa13 + bdfgtsfc;
    
        IF ((wvalormes1 IS NULL) AND (wvalormes2 IS NULL)) THEN
           wvalormes1 = wtotalfgts;
        ELSE
        IF ((wvalormes1 IS NOT NULL) AND (wvalormes2 IS NULL)) THEN
           wvalormes2 = wtotalfgts;
        ELSE
        IF ((wvalormes1 IS NOT NULL) AND (wvalormes2 IS NOT NULL)) THEN
          BEGIN
            bdsaldoantfgts = wvalormes1;
            wvalormes1 = wvalormes2;
            wvalormes2 = wtotalfgts;
          END
    
        IF (TRUNC((bdsaldoantfgts * CAST(bdindicejam AS NUMERIC(15, 6))), 2) >= 0) THEN
           BEGIN
             bdvalorjam = TRUNC(((bdsaldoantfgts + windjamant) * CAST(bdindicejam AS NUMERIC(15, 6))), 2);
             windjamant = bdvalorjam;
    
             IF (wvalormes2 IS NOT NULL) THEN
                wvalormes2 = wvalormes2 + windjamant;
           END
        ELSE
           BEGIN
             bdvalorjam = 0;
             windjamant = 0;
           END
    
    
        bdsaldofgts = wtotalfgts + bdvalorjam;
    
        wtotalfgts = wtotalfgts + bdvalorjam;
    
    --    if (prrefini > 0)  then
    --      begin
    --        if (bdref >= prrefini) then
    --           suspend;
    --      end
    --    else
        SUSPEND;
    
      END

  EXECUTE PROCEDURE sp_seta_competencia(NULL, NULL, NULL);
END
^

/* Alter (VSUC_SP_RETORNA_RAZAO_CONTAS_X) */
ALTER PROCEDURE VSUC_SP_RETORNA_RAZAO_CONTAS_X(PRCODEMP VARCHAR(1024),
PRCLASSIFICACAO_INICIAL VARCHAR(40),
PRCLASSIFICACAO_FINAL VARCHAR(40),
PRDATA_INICIAL DATE,
PRDATA_FINAL DATE,
PRDETALHARCC INTEGER,
PRLCC VARCHAR(1024),
PRLANCAMENTOSEMCC INTEGER,
PRORDEMPRESA INTEGER,
PRREFEMP INTEGER,
PRDETALHARCTAPARTICIPANTE INTEGER,
PRAGRUPARPARTICIPANTEPORCNPJ INTEGER,
PRCONCATENACODPARTICIPANTE INTEGER,
PRPARTINI INTEGER,
PRPARTFIN INTEGER,
PRCONTABILIZACAO INTEGER,
PRMATRIZFILIALCONSOLIDADO INTEGER,
PRDETALHARFILIAL INTEGER,
PRIGNORAZERAMENTO INTEGER = 0)
 RETURNS(PRHISTORICO VARCHAR(100),
PRDATA DATE,
PRCHAVE INTEGER,
PRCONTA INTEGER,
PRCONTRA INTEGER,
PRCREDITO NUMERIC(18,2),
PRDEBITO NUMERIC(18,2),
PRCONCILIADO VARCHAR(5),
PREMPRESA INTEGER,
PRNOMEEMPRESA DM_BDNOMEMP,
PRCC INTEGER,
PRNOME_CONTA VARCHAR(100),
PRCLASS_CONTA VARCHAR(40),
PRCLASS_CONTRA VARCHAR(40),
PRNOME_CONTA_CONTRA VARCHAR(100),
PRLOTE VARCHAR(10),
PRDCTO VARCHAR(15),
PRCOMPL VARCHAR(200),
PRCNPJEMP VARCHAR(18),
PRCODMATRIZ INTEGER,
PRGRPCTA INTEGER,
PRCODHIST INTEGER,
PRCODPLAPADRAO INTEGER,
PRCODTPHI INTEGER,
PRPARTICIPANTECONTA INTEGER,
PRPARTICIPANTECONTRA INTEGER,
PRCODTERCEIROCONTA INTEGER,
PRCODTERCEIROCONTRA INTEGER,
PRCODEMPFILIAL INTEGER,
PRTIPOLANCFILIAL INTEGER,
PRTIPOLAN INTEGER,
PRCHAVEONLINE VARCHAR(120),
PRCODCENTRO INTEGER,
PRTRANSACAO INTEGER,
PRTRANFECHADA DM_BOOLEAN)
 AS
declare variable WSQL varchar(30000) character set WIN1252;
declare variable WGRPCTAC integer;
declare variable WGRPCTAD integer;
declare variable WDEBCRE integer;
declare variable WPOS integer;
declare variable WFILIAL integer;
declare variable WPERMITE integer;
declare variable WEMPPRINC integer;
declare variable WSELECTCTA integer;
declare variable WPOS_PERNAMATRIZ integer;
declare variable WPOS_MATRIZ integer;
declare variable WPOS_FILIAL integer;
declare variable WCAMPO integer;
declare variable WTIPCTACONTA integer;
declare variable WTIPCTACONTRA integer;
declare variable WCONCILIADOCONTRA varchar(5) character set WIN1252;
declare variable WNOMETER varchar(100) character set WIN1252;
BEGIN
  wsql = 'select first 1 case when (temp.bdcodmatriz is null) then temp.bdcodemp ';
  wpos = POSITION(',', :prcodemp);
  IF (wpos <> 0) THEN
     wsql = wsql||'            when (temp.bdcodmatriz is not null) then temp.bdcodmatriz ';
  ELSE
     BEGIN
       wsql = wsql||'          when ((temp.bdcodmatriz is not null) and (temp.bdcodemp not in ('||prcodemp||'))) then temp.bdcodmatriz ';
       wsql = wsql||'          when (temp.bdcodemp in ('||prcodemp||')) then temp.bdcodemp ';
     END
  wsql = wsql||'       end bdcodemp ';
  wsql = wsql||'from tempresas temp ';
  wsql = wsql||'where temp.bdcodemp in ('||prcodemp||')';
  EXECUTE STATEMENT wsql INTO :prempresa;

  /* alt:12505 - 07/10/2015 - Comentadas variáveis sem uso. Pedro
  wdatini   = SUBSTRING(CAST(prdata_inicial AS VARCHAR(10)) FROM 6 FOR 2)||'/'||
              SUBSTRING(CAST(prdata_inicial AS VARCHAR(10)) FROM 9 FOR 2)||'/'||
              SUBSTRING(CAST(prdata_inicial AS VARCHAR(10)) FROM 1 FOR 4);
  wdatfin   = SUBSTRING(CAST(prdata_final AS VARCHAR(10)) FROM 6 FOR 2)||'/'||
              SUBSTRING(CAST(prdata_final AS VARCHAR(10)) FROM 9 FOR 2)||'/'||
              SUBSTRING(CAST(prdata_final AS VARCHAR(10)) FROM 1 FOR 4); */

  --Retorna a empresa principal
  wpos = POSITION(',', :prcodemp);
  IF (wpos = 0) THEN
     wempprinc = prcodemp;
  ELSE
     wempprinc = SUBSTRING(prcodemp FROM 1 FOR (wpos - 1));

  SELECT FIRST 1 bdcodtpla FROM vsuc_select_contas INTO :wselectcta;


  wsql = '';
  wsql = wsql||'select tx.bdchave, tx.bddata, ';
  wsql = wsql||'       tx.bdcodemp, emp.bdnomemp, emp.bdcnpjemp, ';
  wsql = wsql||'       tx.bdcodplapadrao, ';
  wsql = wsql||'       tx.bdlote, tx.bdcompl, tx.bddcto, ';
  wsql = wsql||'       tx.bdfilial, tx.bdtipolancfilial, ';
  wsql = wsql||'       tx.bdtipolan, tx.bdchaveonline, ';
  wsql = wsql||'       tx.bdcodhist, tx.bdcodtphi, this.bdnome,  ';
  wsql = wsql||'       tx.bdconta, tx.bdcodter as bdcodter_conta, tx.bdconc as bdconc_conta, tx.bdctalon as ctalo_conta, tx.bdnomcta as bdnomcta_conta, tx.bdgrupocta as bdgrupocta_conta, tx.bdtipcta as bdtipcta_conta, tx.bdparticipante as bdparticipante_conta, ';
  wsql = wsql||'       tx.bdcontra, tx.bdcodterceiroc as bdcodter_contra, tx.bdconcc as bdconc_contra, tpla.bdctalon as bdctalon_contra, tpla.bdnomcta as bdnomcta_contra, tpla.bdgrupocta as bdgrupocta_contra, tpla.bdtipcta as bdtipcta_contras, tpla.bdparticipante as bdparticipante_contra, ';
  wsql = wsql||'       tx.bddebito, tx.bdcredito, tx.debcre, ';
  wsql = wsql||'       tx.bdcodcentro, tx.bdcodtpcc, tx.bdtransacao, tx.BDTRANFECHADA, ';
  IF (prdetalharctaparticipante = 1) THEN
     wsql = wsql||' (select first 1 terc2.bdnometer from tterceiro_ref terc2 where (terc2.bdcodter = tx.bdcodter) order by terc2.bdcodter desc, terc2.bdrefemp desc) as bdnometer ';
  ELSE
     wsql = wsql||' null as bdnometer ';
  wsql = wsql||'from ( ';
  -- debito -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  wsql = wsql||'select tlan.bdchave, tlan.bddata, tlan.bdcodemp, tlan.bdcodplapadrao, ';
  wsql = wsql||'       tlan.bdlote, tlan.bdcompl, tlan.bddcto, ';
  wsql = wsql||'       tlan.bdfilial, tlan.bdtipolancfilial,  ';
  wsql = wsql||'       tlan.bdtipolan, tlan.bdchaveonline, ';
  wsql = wsql||'       tlan.bdcodhist, tlan.bdcodtphi, ';
  wsql = wsql||'       tlan.bddebito as bdconta, tlan.bdcodterceirod as bdcodter, tlan.bdconcd as bdconc, tplad.bdctalon, tplad.bdnomcta, tplad.bdgrupocta, tplad.bdtipcta, tplad.bdparticipante, ';
  wsql = wsql||'       tlan.bdcredito as bdcontra, tlan.bdcodterceiroc, tlan.bdconcc, tlan.bdtransacao, tlan.BDTRANFECHADA, ';

  --alt:1186 - Colocado aqui em cima e adicionada validação do novo parâmetro, pois precisa o novo parâmetro antes do LancamentoSemCC. Pedro
  IF ((prlcc = '') OR (prdetalharcc = 0))  THEN
     BEGIN
       wsql = wsql||'       tlan.bdvalor as bddebito, null as bdcredito, 0 as debcre, ';
       wsql = wsql||'       null as bdcodcentro, null as bdcodtpcc ';
       wsql = wsql||'from vsuc_empresas_tlan tlan ';
     END
  ELSE
  --alt:1186 - Fim
  --alt:7794 - Adicionada a opção de mostrar lançamentos sem centro de custo. Pedro
  IF (prlancamentosemcc <> 0) THEN
     BEGIN
       wsql = wsql||'       case when TLCC.BDVALOR is null then TLAN.BDVALOR else TLCC.BDVALOR end as BDDEBITO, ';
       wsql = wsql||'       null as bdcredito, 0 as debcre, TLCC.BDCODCENTRO, TLCC.BDCODTPCC ';
       wsql = wsql||'from vsuc_empresas_tlan tlan ';
       --alt:9149 - Adicionado "TLCC.BDDEBCRE = 0" porque se tiver centro de custo no crédito e não tiver no débito, não retorna o sem cento de custo do débito. Pedro
       wsql = wsql||'left outer join VSUC_EMPRESAS_TLCC TLCC on (TLAN.BDCODEMP = TLCC.BDCODEMP and TLAN.BDCHAVE = TLCC.BDCHAVE and TLCC.BDDEBCRE = 0) ';
     END
  ELSE
  --alt:7794 - Fim
  -- centro de custo a debito -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     BEGIN
       wsql = wsql||'       tlcc.bdvalor as bddebito, null as bdcredito, 0 as debcre, ';
       wsql = wsql||'       tlcc.bdcodcentro, tlcc.bdcodtpcc ';
       wsql = wsql||'from vsuc_empresas_tlcc tlcc ';
       wsql = wsql||'inner join vsuc_empresas_tlan tlan on (tlan.bdcodemp = tlcc.bdcodemp and tlan.bdchave = tlcc.bdchave) ';
     END
  wsql = wsql||'left outer join planos_tpla tplad on (tplad.bdcodplapadrao = tlan.bdcodplapadrao and tplad.bdcodtpla = tlan.bddebito) ';
  -- seleção de contas -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  IF ((wselectcta IS NOT NULL) AND (wselectcta > 0)) THEN
     wsql = wsql||'where (tlan.bddebito in (select bdcodtpla from vsuc_select_contas tsel)) ';
  ELSE
     wsql = wsql||'where (tplad.bdctalon between '''||prclassificacao_inicial||''' and '''||prclassificacao_final||''') ';
  -- contabilização -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  IF (prcontabilizacao <> 99) THEN
     wsql = wsql||'and (tlan.bdcontabilizacao in (0,'||prcontabilizacao||')) ';
  -- filtro de centro de custo a debito -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --alt:7794 - Adicionada a opção de mostrar lançamentos sem centro de custo. Pedro
  IF (prlcc <> '') THEN
     BEGIN
       --alt:1186 - Adicionada essa validação para aqui tratar quando não for para detalhar por centro de custo mas deve validar os
       --centros de custos selecionados ou sem centro de custos, caso que ocorre no Razão em Vídeo. Pedro
       IF (prdetalharcc = 0) THEN
         BEGIN
           wsql = wsql||'and (exists (select first 1 * from VSUC_EMPRESAS_TLCC tlcc where tlcc.bdcodemp = tlan.bdcodemp and tlcc.bdchave = tlan.bdchave ';
           wsql = wsql||'and tlcc.bdcodtpcc in ('||TRIM(prlcc)||') and tlcc.bddebcre = 0) ';
           IF (prlancamentosemcc <> 0) THEN
             wsql = wsql||'or not exists (select first 1 * from VSUC_EMPRESAS_TLCC tlcc where tlcc.bdcodemp = tlan.bdcodemp and tlcc.bdchave = tlan.bdchave and tlcc.bddebcre = 0)) ';
           ELSE
             wsql = wsql||')';
         END
       --alt:1186 - Fim
       ELSE
       IF (prlancamentosemcc <> 0) THEN
          wsql = wsql||'and ((tlcc.bdcodtpcc in ('||TRIM(prlcc)||') and tlcc.bddebcre = 0) or (TLCC.BDCODTPCC is null)) ';
       ELSE
          wsql = wsql||'and (tlcc.bdcodtpcc in ('||TRIM(prlcc)||') and tlcc.bddebcre = 0) ';
     END

  /*if (prlcc <> '') then
     wsql = wsql||'and (tlcc.bdcodtpcc in ('||trim(prlcc)||') and tlcc.bddebcre = 0) '; */
  --alt:7794 - Fim

  wsql = wsql||'and (tlan.bddata between '''||prdata_inicial||''' and '''||prdata_final||''') ';
  -- filtro de participante a debito -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --if ((prpartini is not null) and (prpartfin is not null)) then  --alt:9167 - Adicionada validação do parâmetro de agrupar participante por CNPJ, pois se for para agrupar, deve validar no select externo. Pedro
  IF ((prpartini IS NOT NULL) AND (prpartfin IS NOT NULL) AND (PRAGRUPARPARTICIPANTEPORCNPJ = 0)) THEN
--     wsql = wsql||'and (tlan.bdcodterceirod between '||prpartini||' and '||prpartfin||') '; Gilberto - 13/01/2015 - ALT:#10519 - deve trazer inclusive quem não tem conta de participante
     -- #alt: 11705 (Carlos Mello) wsql = wsql||'and ((tlan.bdcodterceirod between '||prpartini||' and '||prpartfin||') or (tlan.bdcodterceirod is null)) ';
     wsql = wsql||'and ((tlan.bdcodterceirod between '||prpartini||' and '||prpartfin||') or (coalesce(tlan.bdcodterceirod,0) = 0)) ';

  -- ignora zeramento ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  IF (prignorazeramento <> 0) THEN
     wsql = wsql||'and (tlan.bdtipolan <> 10) ';
  wsql = wsql||'and ((tlan.bdcodemp in ('||prcodemp||')) or (tlan.bdfilial in ('||prcodemp||'))) ';

  wsql = wsql||'union ';
  -- credito -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  wsql = wsql||'select tlan.bdchave, tlan.bddata, tlan.bdcodemp, tlan.bdcodplapadrao, ';
  wsql = wsql||'       tlan.bdlote, tlan.bdcompl, tlan.bddcto, ';
  wsql = wsql||'       tlan.bdfilial, tlan.bdtipolancfilial,  ';
  wsql = wsql||'       tlan.bdtipolan, tlan.bdchaveonline, ';
  wsql = wsql||'       tlan.bdcodhist, tlan.bdcodtphi, ';
  wsql = wsql||'       tlan.bdcredito as bdconta, tlan.bdcodterceiroc as bdcodter, tlan.bdconcc as bdconc, tplac.bdctalon, tplac.bdnomcta, tplac.bdgrupocta, tplac.bdtipcta, tplac.bdparticipante, ';
  wsql = wsql||'       tlan.bddebito as bdcontra, tlan.bdcodterceirod, tlan.bdconcd, tlan.bdtransacao, tlan.BDTRANFECHADA, ';

  --alt:1186 - Colocado aqui em cima e adicionada validação do novo parâmetro, pois precisa o novo parâmetro antes do LancamentoSemCC. Pedro
  IF ((prlcc = '') OR (prdetalharcc = 0))  THEN
     BEGIN
       wsql = wsql||'       null as bddebito, tlan.bdvalor as bdcredito, 1 as debcre, ';
       wsql = wsql||'       null as bdcodcentro, null as bdcodtpcc ';
       wsql = wsql||'from vsuc_empresas_tlan tlan ';
     END
  ELSE
  --alt:1186 - Fim
  --alt:7794 - Adicionada a opção de mostrar lançamentos sem centro de custo. Pedro
  IF (prlancamentosemcc <> 0) THEN
     BEGIN
       wsql = wsql||'       null as bddebito, case when TLCC.BDVALOR is null then TLAN.BDVALOR else TLCC.BDVALOR end as BDCREDITO, ';
       wsql = wsql||'       1 as debcre, TLCC.BDCODCENTRO, TLCC.BDCODTPCC ';
       wsql = wsql||'from vsuc_empresas_tlan tlan ';
       --alt:9149 - Adicionado "TLCC.BDDEBCRE = 1" porque se tiver centro de custo no débito e não tiver no crédito, não retorna o sem cento de custo do crédito. Pedro
       wsql = wsql||'left outer join VSUC_EMPRESAS_TLCC TLCC on (TLAN.BDCODEMP = TLCC.BDCODEMP and TLAN.BDCHAVE = TLCC.BDCHAVE and TLCC.BDDEBCRE = 1) ';
     END
  ELSE
  --alt:7794 - Fim
  -- centro de custo a credito -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     BEGIN
       wsql = wsql||'       null as bddebito, tlcc.bdvalor as bdcredito, 1 as debcre, ';
       wsql = wsql||'       tlcc.bdcodcentro, tlcc.bdcodtpcc ';
       wsql = wsql||'from vsuc_empresas_tlcc tlcc ';
       wsql = wsql||'inner join vsuc_empresas_tlan tlan on (tlan.bdcodemp = tlcc.bdcodemp and tlan.bdchave = tlcc.bdchave) ';
     END
  wsql = wsql||'left outer join planos_tpla tplac on (tplac.bdcodplapadrao = tlan.bdcodplapadrao and tplac.bdcodtpla = tlan.bdcredito) ';
  -- seleção de contas -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  IF ((wselectcta IS NOT NULL) AND (wselectcta > 0)) THEN
     wsql = wsql||'where (tlan.bdcredito in (select bdcodtpla from vsuc_select_contas tsel)) ';
  ELSE
     wsql = wsql||'where (tplac.bdctalon between '''||prclassificacao_inicial||''' and '''||prclassificacao_final||''') ';
  -- contabilização -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  IF (prcontabilizacao <> 99) THEN
     wsql = wsql||'and (tlan.bdcontabilizacao in (0,'||prcontabilizacao||')) ';
  -- filtro de centro de custo a credito -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --alt:7794 - Adicionada a opção de mostrar lançamentos sem centro de custo. Pedro
  IF (prlcc <> '') THEN
     BEGIN
       --alt:1186 - Adicionada essa validação para aqui tratar quando não for para detalhar por centro de custo mas deve validar os
       --centros de custos selecionados ou sem centro de custos, caso que ocorre no Razão em Vídeo. Pedro
       IF (prdetalharcc = 0) THEN
         BEGIN
           wsql = wsql||'and (exists (select first 1 * from VSUC_EMPRESAS_TLCC tlcc where tlcc.bdcodemp = tlan.bdcodemp and tlcc.bdchave = tlan.bdchave ';
           wsql = wsql||'and tlcc.bdcodtpcc in ('||TRIM(prlcc)||') and tlcc.bddebcre = 1) ';
           IF (prlancamentosemcc <> 0) THEN
             wsql = wsql||'or not exists (select first 1 * from VSUC_EMPRESAS_TLCC tlcc where tlcc.bdcodemp = tlan.bdcodemp and tlcc.bdchave = tlan.bdchave and tlcc.bddebcre = 1)) ';
           ELSE
             wsql = wsql||')';
         END
       --alt:1186 - Fim
       ELSE
       IF (prlancamentosemcc <> 0) THEN
          wsql = wsql||'and ((tlcc.bdcodtpcc in ('||TRIM(prlcc)||') and tlcc.bddebcre = 1) or (TLCC.BDCODTPCC is null)) ';
       ELSE
          wsql = wsql||'and (tlcc.bdcodtpcc in ('||TRIM(prlcc)||') and tlcc.bddebcre = 1) ';
     END

  /*if (prlcc <> '') then
     wsql = wsql||'and (tlcc.bdcodtpcc in ('||trim(prlcc)||') and tlcc.bddebcre = 1) ';*/
  --alt:7794 - Fim

  wsql = wsql||'and (tlan.bddata between '''||prdata_inicial||''' and '''||prdata_final||''') ';
  -- filtro de participante a credito -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  --if ((prpartini is not null) and (prpartfin is not null)) then  --alt:9167 - Adicionada validação do parâmetro de agrupar participante por CNPJ, pois se for para agrupar, deve validar no select externo. Pedro
  IF ((prpartini IS NOT NULL) AND (prpartfin IS NOT NULL) AND (PRAGRUPARPARTICIPANTEPORCNPJ = 0)) THEN
--     wsql = wsql||'and (tlan.bdcodterceiroc between '||prpartini||' and '||prpartfin||') '; Gilberto - 13/01/2015 - ALT:#10519 - deve trazer inclusive quem não tem conta de participante
     -- #alt: 11705 (Carlos Mello) wsql = wsql||'and ((tlan.bdcodterceiroc between '||prpartini||' and '||prpartfin||') or (tlan.bdcodterceiroc is null)) ';
     wsql = wsql||'and ((tlan.bdcodterceiroc between '||prpartini||' and '||prpartfin||') or (coalesce(tlan.bdcodterceiroc,0) = 0)) ';

  -- ignora zeramento ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  IF (prignorazeramento <> 0) THEN
     wsql = wsql||'and (tlan.bdtipolan <> 10) ';
  wsql = wsql||'and ((tlan.bdcodemp in ('||prcodemp||')) or (tlan.bdfilial in ('||prcodemp||'))) ';
  wsql = wsql||') tx ';
  wsql = wsql||'left outer join hist_tphi this on (this.bdcodhist = tx.bdcodhist and this.bdcodtphi = tx.bdcodtphi) ';
  wsql = wsql||'left outer join planos_tpla tpla on (tpla.bdcodplapadrao = tx.bdcodplapadrao and tpla.bdcodtpla = tx.bdcontra) ';

--  IF (prdetalharctaparticipante = 1) THEN
--     wsql = wsql||'left outer join (select bdcodter, bdnometer, bdcgcter from vw_tterceiro) terc on (terc.bdcodter = tx.bdcodter) ';

  wsql = wsql||'inner join vw_empresas_ref_atual emp on (emp.bdcodemp = tx.bdcodemp) ';

  --alt:9167 - Caso seja para agrupar participante por CNPJ, foi adicionada essa cláusula where para fazer isso. Pedro
  IF ((prpartini IS NOT NULL) AND (prpartfin IS NOT NULL) AND (PRAGRUPARPARTICIPANTEPORCNPJ = 1)) THEN
  begin
--     wsql = wsql||'where (substring(terc.BDCGCTER from 1 for 10) in (select substring(BDCGCTER from 1 for 10) from VW_TTERCEIRO where bdcodter between '||prpartini||' and '||prpartfin||')) ';
    wsql = wsql || ' inner join tterceiro terc on terc.bdcodter = tx.bdcodter ';
    wsql = wsql || 'where ';
    wsql = wsql || 'exists ';
    wsql = wsql || '( ';
    wsql = wsql || '  select bdcodter from tterceiro t3 where t3.bdcodter between '||prpartini||' and '||prpartfin||' and ';
    wsql = wsql || '  substring(terc.BDCGCTER from 1 for 10) ';
    wsql = wsql || '  = ';
    wsql = wsql || '  substring(t3.BDCGCTER from 1 for 10) ';
    wsql = wsql || ') ';
  end
  --alt:9167 - Fim

  wsql = wsql||'order by bdcodemp, bddata, bdconta, tx.bdcodter ';

  FOR EXECUTE STATEMENT wsql
  INTO :prchave, :prdata,
       :prempresa, :prnomeempresa, :prcnpjemp,
       :prcodplapadrao,
       :prlote, :prcompl, :prdcto,
       :wfilial, :prtipolancfilial,
       :prtipolan, :prchaveonline,
       :prcodhist, :prcodtphi, :prhistorico,
       :prconta, :prcodterceiroconta, :prconciliado, :prclass_conta, :prnome_conta, :prgrpcta, :wtipctaconta, :prparticipanteconta,
       :prcontra, :prcodterceirocontra, :wconciliadocontra, :prclass_contra, :prnome_conta_contra, :wgrpctac, :wtipctacontra, :prparticipantecontra,
       :prdebito, :prcredito, :wdebcre,
       :prcodcentro, :prcc, :prtransacao, :prtranfechada, :wnometer DO
  BEGIN
    --alt:12505 - 07/10/2015 - Alterada regra do campo prempresa e prempresafilial, para detalhar corretamente matriz e filial de acordo com a opção "Lançar a". Pedro
    /*prcodempfilial = prempresa;
    wpos = POSITION(',', :prcodemp);
    IF (wpos <> 0) THEN
       BEGIN
         prcodempfilial = prempresa;
         IF (prcodmatriz IS NULL) THEN
            prcodmatriz = prempresa;
         prempresa = prcodmatriz;
       END*/

    if ((prtipolancfilial = 1) and (prdebito <> 0)) then
      prcodempfilial = wfilial;
    else if ((prtipolancfilial = 2) and (prcredito <> 0)) then
      prcodempfilial = wfilial;
    else
      prcodempfilial = prempresa;

    if (prcodmatriz is null) then
      prcodmatriz = prempresa;
    prempresa = prcodmatriz;
    --alt:12505 - Fim

    --Foi criado essas variáveis auxiliares pois quando esta por detalhar participante
    --ele precisa verificar a classificação pela conta original
    --wctalonaxuc = wctalonc;
    --wctalonaxud = wctalond;

    IF (prlcc = '') THEN
       prcc = 0;
    IF ((prdetalharctaparticipante = 1) AND (prparticipanteconta = 1) AND (wtipctaconta = 1)) THEN
       BEGIN
--         select bdclassificacao, bdnome from vsuc_sp_nomenclatura_cta_partic(:wcodterceirod,:wctalond,:prconcatenacodparticipante) into :wctalond,:wnomctad;
         prclass_conta = REVERSE(prclass_conta);
         prclass_conta = SUBSTRING(prclass_conta FROM POSITION('.', prclass_conta) FOR strlen(prclass_conta));
         prclass_conta = REVERSE(prclass_conta);
         prclass_conta = prclass_conta||LPAD(prcodterceiroconta, 8, '0');
       
         IF (prconcatenacodparticipante = 1) THEN
           BEGIN
             wpos = CHARACTER_LENGTH(:prcodterceiroconta);
             wpos = ((100 - wpos) - 3);
             wnometer = prcodterceiroconta||' - '||SUBSTRING(wnometer FROM 1 FOR wpos);
           END
         prnome_conta = SUBSTRING(wnometer FROM 1 FOR 100);
       END


    --alt:#6524 - implementado rotina "POS" aqui, por questão de desempenho, precisa apenas uma vez para as duas verificações
--    select bdpermite from vsuc_sp_permitecalcsaldolancmf(1,:wempprinc,:prempresa,:wfilial,:wtipolancfilial,:prmatrizfilialconsolidado,:prcodemp) into :wpermite;
    IF (NOT ((prtipolancfilial = 0) AND (COALESCE(wfilial, 0) = 0))) THEN
       BEGIN
       --alt:#6535 - gilberto - 05/09/2013
         wpos_pernamatriz = POSITION(wempprinc, prcodemp);
         wpos_matriz = POSITION(prempresa, prcodemp);
         wpos_filial = POSITION(wfilial, prcodemp);
      END
    wcampo = wdebcre + 1;

    IF ((prtipolancfilial = 0) AND (COALESCE(wfilial, 0) = 0)) THEN
       wpermite = 1;
    ELSE
    IF (((wempprinc = prempresa) AND (wfilial = 0)) OR
        --Estou na filial mas o lançamento foi feito na matriz para a filial(uma perna)
        ((wempprinc = wfilial) AND (wcampo = prtipolancfilial)) OR
        --Estou na matriz e tem uma perna do lançamento na filial, mas só quero os lançamentos da matriz(a perna da matriz) e que a matriz esteja no filtro
        ((wempprinc = prempresa) AND (wfilial <> 0) AND (wcampo <> prtipolancfilial) AND (wpos_pernamatriz > 0)) OR
        --Estou na matriz mas também quero os lançamentos da filial conforme o filtro(Lançamentos que foram feito na filial -> BDCODEMP = Filial)
        ((prmatrizfilialconsolidado = 1) AND (wempprinc <> prempresa) AND (wfilial = 0) AND (wpos_matriz > 0)) OR
        --Estou na matriz mas também quero a perna que está na filial conforme o filtro
        ((prmatrizfilialconsolidado = 1) AND (wempprinc = prempresa) AND (wfilial <> 0) AND (wcampo = prtipolancfilial) AND (wpos_filial > 0))) THEN
       wpermite = 1;
    ELSE
       wpermite = 0;
    -------------------------------------------------------------------alt:#6524
    IF (wpermite = 1) THEN
       SUSPEND;
  END
END
^

/* Alter (VSUC_SP_RETORNA_RAZAO_SALDO_X) */
ALTER PROCEDURE VSUC_SP_RETORNA_RAZAO_SALDO_X(PRCODEMP VARCHAR(1024),
PRCLASSIFICACAO_INICIAL VARCHAR(40),
PRCLASSIFICACAO_FINAL VARCHAR(40),
PRDATA_INICIAL DATE,
PRDATA_FINAL DATE,
PRDETALHARCC INTEGER,
PRLCC VARCHAR(1024),
PRLANCAMENTOSEMCC INTEGER,
PRORDEMPRESA INTEGER,
PRREFEMP INTEGER,
PRDETALHARCTAPARTICIPANTE INTEGER,
PRAGRUPARPARTICIPANTEPORCNPJ INTEGER,
PRCONCATENACODPARTICIPANTE INTEGER,
BDCONCILIADOS INTEGER,
PRPARTINI INTEGER,
PRPARTFIN INTEGER,
PRAGRUPAR VARCHAR(1024),
PRCONTABILIZACAO INTEGER,
PRMATRIZFILIALCONSOLIDADO INTEGER,
PRDETALHARFILIAL INTEGER,
PRDCDOISLANCAMENTOS INTEGER,
PRIGNORAZERAMENTO INTEGER = 0,
PRIGNORASALDOANTERIOR INTEGER = 0)
 RETURNS(BDHISTORICO VARCHAR(400),
BDDATA DATE,
BDCHAVE INTEGER,
BDCONTA INTEGER,
BDCONTRA INTEGER,
BDCREDITO NUMERIC(15,2),
BDDEBITO NUMERIC(15,2),
BDSALDO NUMERIC(15,2),
BDCONCILIADO VARCHAR(5),
BDEMPRESA INTEGER,
BDNOMEEMPRESA VARCHAR(100),
BDCODCENTRO INTEGER,
BDCC INTEGER,
BDNOME_CONTA VARCHAR(100),
BDCLASS_CONTA VARCHAR(40),
BDCOD_TERCEIRO_CONTA INTEGER,
BDCLASS_CONTRA VARCHAR(40),
BDNOME_CONTA_CONTRA VARCHAR(100),
BDCOD_TERCEIRO_CONTRA INTEGER,
BDVALOR NUMERIC(15,2),
BDORDSALDO INTEGER,
BDLOTE VARCHAR(10),
BDDCTO VARCHAR(15),
BDCOMPL VARCHAR(200),
BDCNPJEMP VARCHAR(18),
BDSALDOANTERIOR NUMERIC(15,2),
BDVALORSEMSINAL NUMERIC(15,2),
BDGRUPOCONTA INTEGER,
BDCODHIST INTEGER,
BDCODPLAPADRAO INTEGER,
BDREF VARCHAR(8),
BDCODTPHI INTEGER,
BDEMPRESAFILIAL INTEGER,
BDTIPOLANCFILIAL INTEGER,
BDVLRCONCILIADO NUMERIC(15,2),
BDVLRACONCILIAR NUMERIC(15,2),
BDCHAVEONLINE VARCHAR(120),
BDTRANSACAO INTEGER,
BDTRANFECHADA DM_BOOLEAN,
BDTIPOLAN INTEGER,
BDSEQCTA VARCHAR(7))
 AS
declare variable wconta integer;
declare variable wsaldo numeric(15,2);
declare variable wclass varchar(40);
declare variable wvalor numeric(15,2);
declare variable wordem integer;
declare variable wclassini varchar(40);
declare variable wclassfin varchar(40);
declare variable wtam integer;
declare variable wcredito numeric(15,2);
declare variable wdebito numeric(15,2);
declare variable wsqlsaldo varchar(1024);
declare variable wsql varchar(10240);
declare variable wparticipante integer;
declare variable wclass_ant varchar(40);
declare variable wpos integer;
declare variable wsqlemp varchar(10240);
declare variable wcodempefilial integer;
declare variable wcodmatriz integer;
declare variable wcodter integer;
declare variable wcc_atu integer;
BEGIN
  -- BDCONCILIADOS = 0 TODOS 1 CONCILIADOS 2 NAO CONCILIADOS

  wconta     = 0;
  wsaldo     = 0;
  wordem     = 0;
  bdvlraconciliar = 0;
  bdvlrconciliado = 0;
  wclass_ant = '';

  wsql = '';
  IF (pragrupar <> '') THEN
     wsql = wsql||'select * from (';

  wsql = wsql||'select TRazao.prhistorico, TRazao.prdata, TRazao.prchave, TRazao.prconta, ';
  wsql = wsql||'TRazao.prcontra, TRazao.prcredito, TRazao.prdebito, ';
  wsql = wsql||'TRazao.prconciliado, TRazao.prempresa, ';
  wsql = wsql||'TRazao.prnomeempresa, TRazao.prcc, TRazao.prnome_conta, TRazao.prclass_conta, ';
  wsql = wsql||'TRazao.prclass_contra, TRazao.prnome_conta_contra, TRAZAO.prlote, TRAZAO.prdcto, TRAZAO.prcompl, TRAZAO.prcnpjemp, ';
  wsql = wsql||'TRAZAO.PRGRPCTA, TRAZAO.PRCODHIST, TRAZAO.PRCODTPHI, TRAZAO.PRCODPLAPADRAO , ';
  wsql = wsql||'(substring((CAST(TRazao.prdata AS VARCHAR(10))) from 6 for 2) || ''/'' || substring((CAST(TRazao.prdata AS VARCHAR(10))) from 1 for 4)) as "BDREF", ';
  wsql = wsql||'TRAZAO.prparticipanteconta, tRAZAO.prcodterceiroconta, tRAZAO.prcodterceirocontra, trazao.PRCODEMPFILIAL, trazao.prcodmatriz, trazao.PRCHAVEONLINE, trazao.PRCODCENTRO, ';
  wsql = wsql||'TRAZAO.prtransacao, TRAZAO.prtranfechada, TRAZAO.prtipolan, trazao.prtipolancfilial ';

  wsql = wsql||'from vsuc_sp_retorna_razao_contas_x('''||TRIM(prcodemp)||''','''||TRIM(prclassificacao_inicial)||''','''||TRIM(prclassificacao_final)||''','''||prdata_inicial||''','''||prdata_final||''',';
--  wsql = wsql||                                   trim(prlcc)||''','||trim(prordempresa)||','||prrefemp||','||prdetalharctacliente||','||prdetalharctafornecedor||','||PRCONCATENACODCLIFOR||',null,null) TRazao ';
  --wsql = wsql||                                   trim(prlcc)||''',';
  --alt:1186 - Adicionados parâmetros. Pedro
  wsql = wsql||PRDETALHARCC||','''||TRIM(prlcc)||''','||prlancamentosemcc||',';
  wsql = wsql||TRIM(prordempresa)||',';
  wsql = wsql||prrefemp||',';
  wsql = wsql||prdetalharctaparticipante||',';
  --alt:9167 - Passado novo parâmetro para trazer todos os participantes com a mesma raiz de CNPJ. Pedro
  wsql = wsql||PRAGRUPARPARTICIPANTEPORCNPJ||',';
  wsql = wsql||PRCONCATENACODPARTICIPANTE||',';
  IF (prpartini IS NOT NULL) THEN
     wsql = wsql||prpartini||',';
  ELSE
     wsql = wsql||'null,';
  IF (prpartfin IS NOT NULL) THEN
     wsql = wsql||prpartfin||',';
  ELSE
     wsql = wsql||'null,';
  --alt:7794 - Adicionado novo parâmetro para mostrar ou não lançamentos sem centro de custo. Pedro
  --alt:1186 - Adicionados realocado para cima. Pedro
  wsql = wsql||prcontabilizacao||','||prmatrizfilialconsolidado||','||prdetalharfilial||','||prignorazeramento||') TRazao ';
  --wsql = wsql||prcontabilizacao||','||prmatrizfilialconsolidado||','||prdetalharfilial||','||prignorazeramento||','||prlancamentosemcc||') TRazao ';
  --alt:7794 - Fim

  IF (bdconciliados = 1) THEN
     wsql = wsql|| ' where (Upper(TRazao.prconciliado) = '||'''TRUE'''||')';
  ELSE
  IF (bdconciliados = 2) THEN
     wsql = wsql|| ' where ((Upper(TRazao.prconciliado) = '||'''FALSE'''||') or (TRazao.prconciliado is null))';

  -- agrupa lançamentos ________________________________________________________
  IF (pragrupar <> '') THEN
     BEGIN
       IF (bdconciliados IN (1,2)) THEN
          wsql = wsql||' and trazao.PRTIPOLAN not in ('||pragrupar||') ';
       ELSE
          wsql = wsql||' where trazao.PRTIPOLAN not in ('||pragrupar||') ';

       wsql = wsql||'union all ';
       wsql = wsql||'select null as prhistorico, TRazao.prdata, min(TRazao.prchave), TRazao.prconta, ';
       wsql = wsql||'TRazao.prcontra, sum(TRazao.prcredito), sum(TRazao.prdebito), ';
       wsql = wsql||'null as prconciliado, TRazao.prempresa, ';
       wsql = wsql||'TRazao.prnomeempresa, TRazao.prcc, TRazao.prnome_conta, TRazao.prclass_conta, ';
       wsql = wsql||'TRazao.prclass_contra, TRazao.prnome_conta_contra, null as prlote, null as prdcto, null as prcompl, TRAZAO.prcnpjemp, ';
       wsql = wsql||'TRAZAO.PRGRPCTA, TRAZAO.PRCODHIST, TRAZAO.PRCODTPHI, TRAZAO.PRCODPLAPADRAO , ';
       wsql = wsql||'(substring((CAST(TRazao.prdata AS VARCHAR(10))) from 6 for 2) || ''/'' || substring((CAST(TRazao.prdata AS VARCHAR(10))) from 1 for 4)) as "BDREF", ';
       wsql = wsql||'null as prparticipanteconta, null as prcodterceiroconta, null as prcodterceirocontra, trazao.PRCODEMPFILIAL, trazao.prcodmatriz, trazao.PRCHAVEONLINE, trazao.PRCODCENTRO, ';
       wsql = wsql||'TRAZAO.prtransacao, TRAZAO.prtranfechada, TRAZAO.prtipolan, trazao.prtipolancfilial ';
     
       wsql = wsql||'from vsuc_sp_retorna_razao_contas_x('''||TRIM(prcodemp)||''','''||TRIM(prclassificacao_inicial)||''','''||TRIM(prclassificacao_final)||''','''||prdata_inicial||''','''||prdata_final||''',';
       --wsql = wsql||                                   trim(prlcc)||''',';
       --alt:1186 - Adicionados parâmetros. Pedro
       wsql = wsql||PRDETALHARCC||','''||TRIM(prlcc)||''','||prlancamentosemcc||',';
       wsql = wsql||TRIM(prordempresa)||',';
       wsql = wsql||prrefemp||',';
       wsql = wsql||prdetalharctaparticipante||',';
       --alt:9167 - Passado novo parâmetro para trazer todos os participantes com a mesma raiz de CNPJ. Pedro
       wsql = wsql||PRAGRUPARPARTICIPANTEPORCNPJ||',';
       wsql = wsql||PRCONCATENACODPARTICIPANTE||',';
       IF (prpartini IS NOT NULL) THEN
          wsql = wsql||prpartini||',';
       ELSE
          wsql = wsql||'null,';
       IF (prpartfin IS NOT NULL) THEN
          wsql = wsql||prpartfin||',';
       ELSE
          wsql = wsql||'null,';
       --alt:7794 - Adicionado novo parâmetro para mostrar ou não lançamentos sem centro de custo. Pedro
       --alt:1186 - Adicionados realocado para cima. Pedro
       wsql = wsql||prcontabilizacao||','||prmatrizfilialconsolidado||','||prdetalharfilial||','||prignorazeramento||') TRazao ';
       --wsql = wsql||prcontabilizacao||','||prmatrizfilialconsolidado||','||prdetalharfilial||','||prignorazeramento||','||prlancamentosemcc||') TRazao ';
       --alt:7794 - Fim
     
       IF (bdconciliados = 1) THEN
          wsql = wsql|| ' where (Upper(TRazao.prconciliado) = '||'''TRUE'''||')';
       ELSE
       IF (bdconciliados = 2) THEN
          wsql = wsql|| ' where ((Upper(TRazao.prconciliado) = '||'''FALSE'''||') or (TRazao.prconciliado is null))';
       IF (bdconciliados IN (1,2)) THEN
          wsql = wsql||' and trazao.PRTIPOLAN in ('||pragrupar||') ';
       ELSE
          wsql = wsql||' where trazao.PRTIPOLAN in ('||pragrupar||') ';

/* gilberto - 08/01/2014 - alt#:7480
       wsql = wsql||'group by TRazao.prdata, trazao.PRCHAVEONLINE, TRazao.prconta, ';
       wsql = wsql||'TRazao.prcontra, ';
       wsql = wsql||'TRazao.prempresa, ';
       wsql = wsql||'TRazao.prnomeempresa, TRazao.prcc, TRazao.prnome_conta, TRazao.prclass_conta, ';
       wsql = wsql||'TRazao.prclass_contra, TRazao.prnome_conta_contra, TRAZAO.prcnpjemp, ';
       wsql = wsql||'TRAZAO.PRGRPCTA, TRAZAO.PRCODHIST, TRAZAO.PRCODTPHI, TRAZAO.PRCODPLAPADRAO, ';
       wsql = wsql||'trazao.PRCODEMPFILIAL, trazao.prcodmatriz) TRAZAO ';
*/
       wsql = wsql||'group by TRazao.prdata, TRazao.prconta, ';
       wsql = wsql||'TRazao.prcontra, ';
       wsql = wsql||'TRazao.prempresa, TRazao.prnomeempresa, TRazao.prcc, TRazao.prnome_conta, ';
       wsql = wsql||'TRazao.prclass_conta, TRazao.prclass_contra, TRazao.prnome_conta_contra, ';
       wsql = wsql||'TRAZAO.prcnpjemp, ';
       wsql = wsql||'TRAZAO.PRGRPCTA, TRAZAO.PRCODHIST, TRAZAO.PRCODTPHI, TRAZAO.PRCODPLAPADRAO , ';
       wsql = wsql||'trazao.PRCODEMPFILIAL, trazao.prcodmatriz, trazao.PRCHAVEONLINE, trazao.PRCODCENTRO, trazao.PRTRANSACAO, trazao.PRTRANFECHADA, TRAZAO.prtipolan, trazao.prtipolancfilial) TRAZAO ';
     END

  IF (prordempresa = 1) THEN
     BEGIN
/* gilberto - 13/01/2015 - ALT:#10519 - Razão alterado para detalhar participante quendo seleciona todas as contas, dessa forma é necessário ordenar sempre pela conta
       IF (prdetalharctaparticipante = 0) THEN
--          wsql = wsql||'order by TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prconta, TRazao.prdata, TRazao.prchave'; -- gilberto - 26/07/2013 - 6208
          wsql = wsql||'order by TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prclass_conta, TRazao.prdata, TRazao.prchave';
       ELSE
          wsql = wsql||'order by TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.PRCODTERCEIROCONTA, TRazao.prdata, TRazao.prchave';*/
       wsql = wsql||'order by TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prclass_conta, TRazao.prdata, TRazao.prchave';
     END
  ELSE
  IF (prordempresa = 2) THEN
     BEGIN
/* gilberto - 13/01/2015 - ALT:#10519 - Razão alterado para detalhar participante quendo seleciona todas as contas, dessa forma é necessário ordenar sempre pela conta
       IF (prdetalharctaparticipante = 0) THEN
          wsql = wsql||'order by TRazao.prnome_conta, TRazao.prdata, TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prchave';
       ELSE
          wsql = wsql||'order by TRazao.PRCODTERCEIROCONTA, TRazao.prdata, TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prchave';*/
       wsql = wsql||'order by TRazao.prnome_conta, TRazao.prdata, TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prchave';
     END
  ELSE
  IF (prordempresa = 3) THEN
     BEGIN
/* gilberto - 13/01/2015 - ALT:#10519 - Razão alterado para detalhar participante quendo seleciona todas as contas, dessa forma é necessário ordenar sempre pela conta
       IF (prdetalharctaparticipante = 0) THEN
--          wsql = wsql||'order by TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prdata, TRazao.prconta, TRazao.prchave'; -- gilberto - 26/07/2013 - 6208
          wsql = wsql||'order by TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prdata, TRazao.prclass_conta, TRazao.prchave'; -- gilberto - 26/07/2013 - 6208
       ELSE
          wsql = wsql||'order by TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prdata, TRazao.PRCODTERCEIROCONTA, TRazao.prchave';*/
       wsql = wsql||'order by TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prdata, TRazao.prclass_conta, TRazao.prchave';
     END
  ELSE
     BEGIN
/* gilberto - 13/01/2015 - ALT:#10519 - Razão alterado para detalhar participante quendo seleciona todas as contas, dessa forma é necessário ordenar sempre pela conta
       IF (prdetalharctaparticipante = 0) THEN
--          wsql = wsql||'order by TRazao.prconta, TRazao.prdata, TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prchave'; -- gilberto - 26/07/2013 - 6208
  --        wsql = wsql||'order by TRazao.prclass_conta, TRazao.prdata, TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prchave'; -- gilberto - 26/07/2013 - 6208
  --     else
  --        wsql = wsql||'order by TRazao.PRCODTERCEIROCONTA, TRazao.prdata, TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prchave';
       --alt:7823 - Adicionado a ordem por centro de custo para trazer corretamente o saldo anterior e na sequência correta. Pedro
          wsql = wsql||'order by TRazao.prclass_conta, TRazao.prcc, TRazao.prdata, TRazao.prchave, TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prchave';
       ELSE
          wsql = wsql||'order by TRazao.PRCODTERCEIROCONTA, TRazao.prcc, TRazao.prdata, TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prchave';
       --alt:7823 - Fim*/
       wsql = wsql||'order by TRazao.prclass_conta, TRazao.prcc, TRazao.prdata, TRazao.prchave, TRazao.prcodmatriz, TRazao.prempresa, trazao.PRCODEMPFILIAL, TRazao.prchave';
     END
/*
  for select TRazao.prhistorico, TRazao.prdata, TRazao.prchave, TRazao.prconta,
             TRazao.prcontra, TRazao.prentrada, TRazao.prsaida, TRazao.prconciliado, TRazao.prempresa,
             TRazao.prnomeempresa, TRazao.prcc, TRazao.prnome_conta, TRazao.prclass_conta,
             TRazao.prclass_contra, TRAZAO.prlote, TRAZAO.prdcto, TRAZAO.prcompl, TRAZAO.prcnpjemp
      from vsuc_sp_retorna_razao_contas(:prcodigo_empresa, :wclassini, :wclassfin,
                                        :prdata_inicial, :prdata_final, :prfiliais, :prlcc) TRazao
      order by TRazao.prconta, TRazao.prdata
*/
  FOR EXECUTE STATEMENT wsql
      INTO :bdhistorico, :bddata, :bdchave, :bdconta, :bdcontra, :bdcredito, :bddebito, /*:prcomplemento,
           /*:prcontra_longa, */:bdconciliado, :bdempresa, :bdnomeempresa, :bdcc, :bdnome_conta,
           :bdclass_conta, :bdclass_contra, :bdnome_conta_contra, :bdlote, :bddcto, :bdcompl, :bdcnpjemp, :bdgrupoconta, :bdcodhist,
           :bdcodtphi, :bdcodplapadrao, :bdref, :wparticipante, :bdcod_terceiro_conta, :bdcod_terceiro_contra, :BDEMPRESAFILIAL, :wcodmatriz, :bdchaveonline, :bdcodcentro,
           :bdtransacao, :bdtranfechada, :bdtipolan, :bdtipolancfilial DO
      BEGIN
        bdsaldoanterior = 0;
        BDSEQCTA = null; --alt:10873 - Se for zero é lançamento normal, não deve excluir. Pedro

        --Incluido a verificação (wconta <> bdcontra), sem isso entrava nesta rotina sempre. Bruno Borges
/* gilberto - 22/05/2013 - 5772
        if (((wconta <> bdconta) and ((wconta <> bdcontra) or (bdcontra is null and wconta is not null))) or
            (((prdetalharctaparticipante = 1) and (wparticipante = 1)) and
             (bdclass_conta <> wclass_ant)))then*/
--        if ((((wconta <> bdconta) and ((wconta <> bdcontra) or (bdcontra is null and wconta is not null))) or (bdclass_conta <> wclass_ant)) or
--            ((bdclass_conta <> wclass_ant) and ((prdetalharctaparticipante = 1) and (wparticipante = 1)))) then

        --alt:7823 - Alterado para verificar também se o centro de custo mudou, pois sem isso ele trazia o saldo anterior incorreto. E foi adicionado o coalesce para ter certeza. Pedro
        IF ((COALESCE(wconta, 0) <> COALESCE(bdconta, 0)) OR (COALESCE(wcc_atu, 0) <> COALESCE(bdcc, 0)) OR ((bdcod_terceiro_conta <> wcodter) AND (prdetalharctaparticipante = 1) AND (wparticipante = 1))) THEN
           BEGIN
             bdvlraconciliar = 0;
             bdvlrconciliado = 0;

             --alt:10644 - 24/03/2015 - Adicionado parâmetro para ignorar o saldo anterior. Pedro
             wsaldo = 0;
             if (PRIGNORASALDOANTERIOR = 0) then
               begin
                 /* INICIO Giovanni, precisa fazer um laco das empresas para trazer o saldo anterior correto */
                 -- retorna as empresas selecionadas
                 wsqlemp = 'select temp.bdcodemp bdempresasefilial ';
                 wsqlemp = wsqlemp||'from tempresas temp ';
                 wsqlemp = wsqlemp||'inner join vw_empresas_ref_atual tref on (tref.bdcodemp = temp.bdcodemp) ';
                 wsqlemp = wsqlemp||'where temp.bdcodemp in ('||prcodemp||') ';
                 wsqlemp = wsqlemp||'order by temp.bdcodmatriz asc, temp.bdcodemp asc ';
                 -- executa o sql das empresas
    
                 FOR EXECUTE STATEMENT wsqlemp
                     INTO :wcodempefilial DO
    --             wcodempefilial = bdempresa;
                 BEGIN
                   wsaldo = 0;
                   -- Detalhado por participante
                   IF ((prdetalharctaparticipante = 1) AND (wparticipante = 1) AND (bdcod_terceiro_conta IS NOT NULL)) THEN
                      SELECT FIRST 1 bdsaldo_anterior FROM vsuc_sp_retorna_saldos_ctaparty(:wcodempefilial,:bdcodplapadrao,:prdata_inicial,:prdata_final,:bdconta,
                                                                                   0,:bdcod_terceiro_conta,0,:bdconciliados,:prcodemp,:prcontabilizacao,:prmatrizfilialconsolidado)
                      INTO :wsaldo;
                   ELSE
                      --alt:7823 - Alterado para filtrar pelo código do participante inicial e final quando ambos forem informados.
                      --Se não fizer essa filtragem, retorna o saldo de todos os participantes ou apenas de um específico, o que gera uma diferença contábil. Pedro
                      /*select SALDO_ANTERIOR from sp_retorna_saldo_emp_x(:wcodempefilial, :bdconta, :prdata_inicial, :prdata_final, 0, :bdcc, null,
                                                                        :prrefemp,:bdconciliados,:prcontabilizacao ,:prmatrizfilialconsolidado) */
                      BEGIN
                        IF ((prdetalharctaparticipante = 0) AND (PRPARTINI > 0) AND (PRPARTFIN > 0)) THEN
                           SELECT SALDO_ANTERIOR FROM sp_retorna_saldo_emp_x(:wcodempefilial, :bdconta, :prdata_inicial, :prdata_final, 0, :bdcc, NULL,
                                                                             :prrefemp,:bdconciliados,:prcontabilizacao ,:prmatrizfilialconsolidado, :PRPARTINI, :PRPARTFIN)
                           INTO :wsaldo;
                        ELSE
                           SELECT SALDO_ANTERIOR FROM sp_retorna_saldo_emp_x(:wcodempefilial, :bdconta, :prdata_inicial, :prdata_final, 0, :bdcc, NULL,
                                                                             :prrefemp,:bdconciliados,:prcontabilizacao ,:prmatrizfilialconsolidado)
                           INTO :wsaldo;
                      END
                      --alt:7823 - Fim
    
    /*
                   wsqlsaldo = 'select sum(coalesce(bdsaldo,0)) from vsuc_empresas_trazao_saldo ';
                   wsqlsaldo = wsqlsaldo||'where bdcodemp = '||wcodempefilial;
                   wsqlsaldo = wsqlsaldo||' and bdcodtpla = '||bdconta;
                   if (((prdetalharctaparticipante is null) or (prdetalharctaparticipante = 0)) or ((bdcod_terceiro_conta is null) or (bdcod_terceiro_conta = 0))) then
                      begin
                        wsqlsaldo = wsqlsaldo||' and bdparticipante is null ';
                        if ((bdcc is not null) and (bdcc <> 0)) then
                           begin
                             wsqlsaldo = wsqlsaldo||' and bdcodtpcc = '||bdcc;
                           end
                      end
                   else
                      begin
                        wsqlsaldo = wsqlsaldo||' and bdparticipante = '||bdcod_terceiro_conta;
                        if ((bdcc is not null) and (bdcc <> 0)) then
                           begin
                             wsqlsaldo = wsqlsaldo||' and bdcodtpcc = '||bdcc;
                           end
                      end
                   execute statement wsqlsaldo into :wsaldo;
    */
                   bdsaldoanterior = bdsaldoanterior + COALESCE(wsaldo,0);
                   wsaldo = bdsaldoanterior;
                 END
               end
               --alt:10644 - Fim

--             if ((bdcodcentro is null) and (prlcc <> '')) then
--                select first 1 bdcodcentro from vsuc_empresas_trazao_saldo where bdcodcentro > 0 into :bdcodcentro;

             wordem = 0;

             IF (bdgrupoconta IN (1,2,3)) THEN
                BEGIN
                  wsaldo = (wsaldo + COALESCE(bdcredito, 0) - COALESCE(bddebito, 0));
                  wvalor = COALESCE(bdcredito, 0) - COALESCE(bddebito, 0);
                END
             ELSE
                BEGIN
                  wsaldo = (wsaldo + COALESCE(bddebito, 0) - COALESCE(bdcredito, 0));
                  wvalor = COALESCE(bddebito, 0) - COALESCE(bdcredito, 0);
                END
           END
        ELSE
           BEGIN
             bdsaldoanterior = wsaldo;

             IF (bdgrupoconta IN (1,2,3)) THEN
                BEGIN
                  wsaldo = (wsaldo + COALESCE(bdcredito, 0) - COALESCE(bddebito, 0));
                  wvalor = COALESCE(bdcredito, 0) - COALESCE(bddebito, 0);
                END
             ELSE
                BEGIN
                  wsaldo = (wsaldo + COALESCE(bddebito, 0) - COALESCE(bdcredito, 0));
                  wvalor = COALESCE(bddebito, 0) - COALESCE(bdcredito, 0);
                END
           END
        wordem = wordem + 1;
        bdordsaldo = wordem;
        bdsaldo = wsaldo;
        IF (wvalor < 0) THEN
           bdvalorsemsinal = wvalor * -1;
        ELSE
           bdvalorsemsinal = wvalor;
        bdvalor     = wvalor;

       IF (bdhistorico IS NOT NULL) THEN
         BEGIN
           wpos = POSITION('*', :bdhistorico);
             IF (wpos > 0) THEN
                bdhistorico = bdcompl;
             ELSE
                bdhistorico = bdhistorico || ' ' || bdcompl;
         END
       ELSE
         bdhistorico = bdcompl;

       IF ((UPPER(bdconciliado) <> 'TRUE') OR (bdconciliado IS NULL)) THEN
          bdvlraconciliar = bdvlraconciliar + wvalor;
       ELSE
          bdvlrconciliado = bdvlrconciliado + wvalor;

        wclass_ant  = bdclass_conta;
        wconta = bdconta;
        wcodter = bdcod_terceiro_conta;

        --alt:7823 - Copia o centro de que será gravado para a variável do centro de custo, que faz verificação de qual centro de custo é o atual. Pedro
        wcc_atu = bdcc;
        SUSPEND;

        --alt:10873 - 17/03/2015 - Adicionada função para trazer lançamentos da mesma transação para poder mostrar a contrapartida "virtual", de acordo com a opção selecionada. Pedro
        if (prdcdoislancamentos = 1) then
          begin
            wsql = 'select count (BDTRANSACAO) from VSUC_EMPRESAS_TLAN where BDCODEMP in ('||:prcodemp||') and BDTRANFECHADA = '''||'TRUE'||''' and '||
                   'BDTRANSACAO = '||coalesce(:bdtransacao, 0)||' and (BDDEBITO is null or BDCREDITO is null) and '||
                   '(coalesce(BDDEBITO, 0) <> coalesce('||:bdconta||', 0) and coalesce(BDCREDITO, 0) <> coalesce('||:bdconta||', 0))';
            execute statement wsql into :wtam;
            if (wtam = 1) then
              begin
                wsql = 'SELECT TLAN.BDCHAVE, TLAN.BDDATA, TLAN.BDCODPLAPADRAO, TLAN.BDTRANFECHADA, TLAN.BDTRANSACAO, '||
                       '/*Conta*/ case when TLAN.BDDEBITO = TPLA.BDCODTPLA then TLAN.BDDEBITO else TLAN.BDCREDITO end as BDCONTA, '||
                       --alt:10873 - 17/03/2015 - Nunca irá trazer BDCONTRA ("Crédito"), pois o Débito ou o Crédito precisa ser nulo nesse caso.
                       '/*Valor débito*/ case when TLAN.BDDEBITO = TPLA.BDCODTPLA then TLAN.BDVALOR end as BDDEBITO, '||
                       '/*Valor crédito*/ case when (TLAN.BDDEBITO is null and TLAN.BDCREDITO is not null) then TLAN.BDVALOR end as BDCREDITO '||
                       'FROM VSUC_EMPRESAS_TLAN TLAN '||
                       'LEFT OUTER JOIN PLANOS_TPLA TPLA on (tpla.BDCODPLAPADRAO = tlan.BDCODPLAPADRAO and (tpla.BDCODTPLA = tlan.BDDEBITO or tpla.BDCODTPLA = tlan.BDCREDITO)) '||
                       'WHERE BDCODEMP in ('||:prcodemp||') and BDTRANFECHADA = '''||'TRUE'||''' and BDTRANSACAO = '||:bdtransacao||' and (BDDEBITO is null or BDCREDITO is null) and '||
                       '      (coalesce(BDDEBITO, 0) <> coalesce('||:bdconta||', 0) and coalesce(BDCREDITO, 0) <> coalesce('||:bdconta||', 0))';
                execute statement wsql into :bdchave, :bddata, :bdcodplapadrao, :bdtranfechada, :bdtransacao, :bdconta, :bddebito, :bdcredito;
                  begin
                    BDSEQCTA = 'DEL'; --alt:10873 - 17/03/2015 - Se for 'DEL' é lançamento utilizado para verificar contrapartida, deve excluir depois de usar. Pedro
                    suspend;
                  end
              end
          end
        --alt:10873 - 17/03/2015 - Fim
      END
END
^

/* Restore proc. body: SP_VAL_RAIS_FF */
ALTER PROCEDURE SP_VAL_RAIS_FF(PREMP INTEGER = 0,
PRCOL INTEGER = 0,
PRANO INTEGER = 0)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDREF INTEGER,
BDAVPREV NUMERIC(15,6),
BDFERIND NUMERIC(15,6),
BDGRATIF NUMERIC(15,6),
BDMULTA NUMERIC(15,6),
BDSINDICAL NUMERIC(15,6),
BDASSISTENCIAL NUMERIC(15,6),
BDCONFEDERATIVA NUMERIC(15,6),
BDBASEFN NUMERIC(15,6),
BDBASEA13 NUMERIC(15,6),
BDBASE13 NUMERIC(15,6),
BDBASEC13 NUMERIC(15,6),
BDORIGEM INTEGER,
BDMENSALIDADE NUMERIC(15,6))
 AS
declare variable WSQLAUX varchar(32000);
declare variable WSQL varchar(32000);
declare variable WREFRESC integer;
declare variable WREF integer;
declare variable WVALOR numeric(15,6);
declare variable WCLASS varchar(300);
declare variable WCLASS2 varchar(300);
declare variable WCLASS3 varchar(300);
declare variable WCLASS4 varchar(300);
declare variable WCLASS1 varchar(10);
declare variable WBASRAIS varchar(5);
declare variable WTIPOCOD integer;
declare variable WVALAUX numeric(15,6);
declare variable WJAN integer;
declare variable WFEV integer;
declare variable WMAR integer;
declare variable WABR integer;
declare variable WMAI integer;
declare variable WJUN integer;
declare variable WJUL integer;
declare variable WAGO integer;
declare variable WSET integer;
declare variable WOUT integer;
declare variable WNOV integer;
declare variable WDEZ integer;
declare variable WMES integer;
declare variable WEXISTE integer;
declare variable WRESCORIGINAL integer;
BEGIN
  /* Procedure Text */
--  wsql = 'select fol.bdreffn, fol.bdvalv, ver.bdclaver from VW_VRHF_CONS_CALC_FOLHA_VERBAS fol '||
--         ' left outer join vrh_base_tverbas ver on (ver.bdcodver = fol.bdcodver) '||


  bdorigem = 0;
  if (prano >= 2014) then
     begin
       wsql = 'select RAIS.BDREFFN, RAIS.BDVALV, VER.BDCLAVER, VERREF.BDRAISVER, VERREF.BDTIPOCODVER, RAIS.BDREFRESCISAO, RAIS.BDREFRESCORIGINAL '||
              'from (select FOL.BDREFFN, FOL.BDVALVFN BDVALV,  '||
                           '0 BDREFRESCISAO, 0 BDREFRESCORIGINAL, FOL.BDCODVER '||
                    'from (select 1 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFFN, BDREFFN BDREFFILTRO, BDREFVFN, BDREFAUXVFN, '||
                                 'BDVALVFN, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                 'cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBAFN COLCON '||
                          'union all '||
                          'select 1 BDTIPOFICHA, COLCON.BDCODEMP, COLCON.BDCODCOL, COLCON.BDCODVER, COLCON.BDREFTOMADOR, '||
                                 'COLCON.BDREFTOMADOR BDREFFILTRO, COLCON.BDREFVFT, COLCON.BDREFAUXVFT, COLCON.BDVALVFT, '||
                                 'CC.BDDATAENTRADA, CC.BDCODCENTRO, CC.BDCODTPCC '||
                          'from VRHF_EMP_TVERBATOMADOR COLCON '||
                          'join VRHF_EMP_TPREPFN CC on (COLCON.BDCODEMP = CC.BDCODEMP and COLCON.BDCODCOL = CC.BDCODCOL and COLCON.BDORDEM = CC.BDORDEM) '||
                          'union all '||
                          'select 2 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFFN, BDREFFN, BDREFVFC, BDREFAUXVFC, BDVALVFC, '||
                                 'cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBAFC FC '||
                          'union all '||
                          'select 2 BDTIPOFICHA, COLCON.BDCODEMP, COLCON.BDCODCOL, COLCON.BDCODVER, COLCON.BDREFTOMADOR, '||
                                 'COLCON.BDREFTOMADOR, COLCON.BDREFVFC, COLCON.BDREFAUXVFC, COLCON.BDVALVFC, CC.BDDATAENTRADA, '||
                                 'CC.BDCODCENTRO, CC.BDCODTPCC '||
                          'from VRHF_EMP_TVERBAFCTOMADOR COLCON '||
                          'join VRHF_EMP_TPREPFN CC on (COLCON.BDCODEMP = CC.BDCODEMP and COLCON.BDCODCOL = CC.BDCODCOL and COLCON.BDORDEM = CC.BDORDEM) '||
                          'union all '||
                          'select 3 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFA13, BDREFA13 BDREFFILTRO, BDREFVA13, BDREFAUXVA13, '||
                                 'BDVALVA13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                 'cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBAA13 FC '||
                          'union all '||
                          'select 4 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREF13, BDREF13 BDREFFILTRO, BDREFV13, BDREFAUXV13, '||
                                 'BDVALV13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                 'cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBA13 FC '||
                          'union all '||
                          'select 4 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFC13, BDREFC13 BDREFFILTRO, BDREFVC13, BDREFAUXVC13, '||
                                 'BDVALVC13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                 'cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBAC13 FC) FOL '||
                    'where (FOL.BDCODEMP = '||premp||') and '||
                          '(substring(FOL.BDREFFILTRO from 1 for 4) = '''||prano||''') and '||
                          'FOL.BDCODCOL = '||prcol||' '||
                    'union all '||
                    'select FOL.BDREFFN, FOL.BDVALVFN BDVALV, '||
                           'case '||
                             'when PRES.BDINIPRES = 1 and RES.BDPROJECAOAVISO is not null then RES.BDREFPROJECAOAVISO '||
                             'else RES.BDREFRESCISAO '||
                           'end BDREFRESCISAO, '||
                           'RES.BDREFRESCISAO BDREFRESCORIGINAL, FOL.BDCODVER '||
                    'from VRH_EMP_TRESCISAO RES '||
                    'join(select 1 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFFN, BDREFFN BDREFFILTRO, BDREFVFN, BDREFAUXVFN, '||
                                'BDVALVFN, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBAFN COLCON '||
                         'union all '||
                         'select 1 BDTIPOFICHA, COLCON.BDCODEMP, COLCON.BDCODCOL, COLCON.BDCODVER, COLCON.BDREFTOMADOR, '||
                                'COLCON.BDREFTOMADOR BDREFFILTRO, COLCON.BDREFVFT, COLCON.BDREFAUXVFT, COLCON.BDVALVFT, '||
                                'CC.BDDATAENTRADA, CC.BDCODCENTRO, CC.BDCODTPCC '||
                         'from VRHF_EMP_TVERBATOMADOR COLCON '||
                         'join VRHF_EMP_TPREPFN CC on (COLCON.BDCODEMP = CC.BDCODEMP and COLCON.BDCODCOL = CC.BDCODCOL and COLCON.BDORDEM = CC.BDORDEM) '||
                         'union all '||
                         'select 2 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFFN, BDREFFN BDREFFILTRO, BDREFVFC, BDREFAUXVFC, '||
                                'BDVALVFC, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBAFC FC '||
                         'union all '||
                         'select 2 BDTIPOFICHA, COLCON.BDCODEMP, COLCON.BDCODCOL, COLCON.BDCODVER, COLCON.BDREFTOMADOR, '||
                                'COLCON.BDREFTOMADOR BDREFFILTRO, COLCON.BDREFVFC, COLCON.BDREFAUXVFC, COLCON.BDVALVFC, CC.BDDATAENTRADA, '||
                                'CC.BDCODCENTRO, CC.BDCODTPCC '||
                         'from VRHF_EMP_TVERBAFCTOMADOR COLCON '||
                         'join VRHF_EMP_TPREPFN CC on (COLCON.BDCODEMP = CC.BDCODEMP and COLCON.BDCODCOL = CC.BDCODCOL and COLCON.BDORDEM = CC.BDORDEM) '||
                         'union all '||
                         'select 3 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFA13, BDREFA13 BDREFFILTRO, BDREFVA13, BDREFAUXVA13, '||
                                'BDVALVA13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBAA13 FC '||
                         'union all '||
                         'select 4 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREF13, BDREF13 BDREFFILTRO, BDREFV13, BDREFAUXV13, '||
                                'BDVALV13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBA13 FC '||
                         'union all '||
                         'select 4 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFC13, BDREFC13 BDREFFILTRO, BDREFVC13, BDREFAUXVC13, '||
                                'BDVALVC13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBAC13 FC) FOL on (FOL.BDCODEMP = RES.BDCODEMP and FOL.BDCODCOL = RES.BDCODCOL and FOL.BDREFFN = RES.BDREFRESCISAO) '||
                    'join VRH_BASE_TPREPRESC PRES on (PRES.BDCODPRES = RES.BDCODPRES) '||
                    'where (FOL.BDCODEMP = '||premp||') and '||
                          '((substring(RES.BDREFRESCISAO from 1 for 4) = '''||prano||''') or (substring(RES.BDREFPROJECAOAVISO from 1 for 4) = '''||prano||''')) and '||
                          '(substring(RES.BDREFRESCISAO from 1 for 4) > '''||'2013'||''') and '||
                          'FOL.BDCODCOL = '||prcol||') RAIS '||

              'join VRH_BASE_TVERBAS VER on (VER.BDCODVER = RAIS.BDCODVER) '||
              'join VW_VRH_BASE_TVERBAS_REF VERREF on (VERREF.BDCODVER = RAIS.BDCODVER) '||
              ' where VERREF.BDTIPOCODVER <= 1 '||
              'order by RAIS.BDREFFN, RAIS.BDREFRESCISAO, RAIS.BDCODVER ';
     end
  else
     begin
       wsql = ' select fol.bdreffn,  fol.bdvalvfn BDVALV, ver.bdclaver,  '||
                 ' verref.bdraisver, verref.bdtipocodver, res.bdrefrescisao, 0 bdrefrescoriginal from '||
                 ' ( '||
     --            // Folha normal
                 ' select  BDCODEMP, BDCODCOL,  bdcodver,  bdreffn,  bdrefvfn,  bdrefauxvfn,  bdvalvfn, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC  from vrhf_emp_tverbafn COLCON '||
                 ' UNION all '||
     --              // Folha normal tomador
                 ' select COLCON.BDCODEMP, COLCON.BDCODCOL,  COLCON.BDCODVER,  COLCON.BDREFTOMADOR,  COLCON.BDREFVFT,  COLCON.BDREFAUXVFT,  COLCON.BDVALVFT, cc.bddataentrada, cc.bdcodcentro, cc.bdcodtpcc '||
                 ' from vrhf_emp_tverbatomador COLCON '||
                 ' join VRHF_EMP_TPREPFN cc on (COLCON.bdcodemp = cc.bdcodemp and  COLCON.bdcodcol = cc.bdcodcol and COLCON.bdordem = cc.bdordem) '||
     --              // Folha Complementar
                 ' union all '||
                 ' select  bdcodemp,  bdcodcol,  bdcodver,  bdreffn,  bdrefvfc,  bdrefauxvfc,  bdvalvfc, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC  from vrhf_emp_tverbafc fc '||
                 ' UNION all '||
     --              // Folha Complementar tomador
                 ' select COLCON.BDCODEMP, COLCON.BDCODCOL,  COLCON.BDCODVER,  COLCON.BDREFTOMADOR,  COLCON.BDREFVFc,  COLCON.BDREFAUXVFc,  COLCON.BDVALVFc, cc.bddataentrada, cc.bdcodcentro, cc.bdcodtpcc '||
                 ' from vrhf_emp_tverbafctomador COLCON '||
                 ' join VRHF_EMP_TPREPFN cc on (COLCON.bdcodemp = cc.bdcodemp and  COLCON.bdcodcol = cc.bdcodcol and COLCON.bdordem = cc.bdordem) '||
     --              // Adiantamento 13º salario
                 ' union all '||
                 ' select  bdcodemp,  bdcodcol,  bdcodver,  bdrefa13,  bdrefva13,  bdrefauxva13,  bdvalva13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC '||
                 ' from vrhf_emp_tverbaa13 fc '||
     --              // 13º salário
                 ' union all '||
                 ' select  bdcodemp,  bdcodcol,  bdcodver,  bdref13,  bdrefv13,  bdrefauxv13,  bdvalv13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC '||
                 ' from vrhf_emp_tverba13 fc '||
     --              // Complemento 13º salário
                 ' union all '||
                 ' select  bdcodemp,  bdcodcol,  bdcodver,  bdrefc13,  bdrefvc13,  bdrefauxvc13,  bdvalvc13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC '||
                 ' from vrhf_emp_tverbac13 fc '||
                 ' ) fol '||
                 ' join vrh_base_tverbas ver on (ver.bdcodver = fol.bdcodver) '||
                 ' join vw_vrh_base_tverbas_ref verref on (verref.bdcodver = fol.bdcodver) '||
                 ' left outer join vrh_emp_trescisao res on (fol.bdcodemp = res.bdcodemp and fol.bdcodcol = res.bdcodcol) '||
     
     
              ' where verref.bdtipocodver <= 1 and (fol.bdcodemp = '||premp||')  and (cast (substring(fol.bdreffn from 1 for 4) as integer) = '||prano||') AND FOL.bdcodcol = '||prcol||
              ' order by bdreffn, fol.bdcodver ';
     end

  bdcodcol = prcol;
  bdcodemp = premp;
  bdref = 0;

  wjan = 0;
  wfev = 0;
  wmar = 0;
  wabr = 0;
  wmai = 0;
  wjun = 0;
  wjul = 0;
  wago = 0;
  wset = 0;
  wout = 0;
  wnov = 0;
  wdez = 0;

  WEXISTE = 0;

  FOR EXECUTE STATEMENT wsql INTO wref,wvalor,wclass,wbasrais,wtipocod,wrefresc,:wrescoriginal DO
    BEGIN
      WEXISTE = 1;

      IF (bdref <> wref) THEN
         BEGIN
           IF (bdref <> 0) THEN
              begin
                if (prano >= 2014) then
                   begin
                     if (substring(wrefresc from 1 for 4) = cast(prano as varchar(4)) and wref = wrescoriginal) then
                        bdref = wrefresc;
                   end

                SUSPEND;
              end

           if (prano >= 2014) then
              begin
                if (substring(wrefresc from 1 for 4) = cast(prano as varchar(4)) and wref = wrescoriginal) then
                   wmes = CAST(SUBSTRING(CAST(wrefresc AS VARCHAR(6)) FROM 5 FOR 6) AS INTEGER);
                else
                   wmes = CAST(SUBSTRING(CAST(wref AS VARCHAR(6)) FROM 5 FOR 6) AS INTEGER);
              end
           else
              wmes = CAST(SUBSTRING(CAST(wref AS VARCHAR(6)) FROM 5 FOR 6) AS INTEGER);

           IF (wmes = 1) THEN
              wjan = 1;
           IF (wmes = 2) THEN
              wfev = 1;
           IF (wmes = 3) THEN
              wmar = 1;
           IF (wmes = 4) THEN
              wabr = 1;
           IF (wmes = 5) THEN
              wmai = 1;
           IF (wmes = 6) THEN
              wjun = 1;
           IF (wmes = 7) THEN
              wjul = 1;
           IF (wmes = 8) THEN
              wago = 1;
           IF (wmes = 9) THEN
              wset = 1;
           IF (wmes = 10) THEN
              wout = 1;
           IF (wmes = 11) THEN
              wnov = 1;
           IF (wmes = 12) THEN
              wdez = 1;

           bdref = wref;

           BDMULTA = 0;

           if (prano >= 2014) then
              begin
                IF ((wref = wrescoriginal) and (substring(wrefresc from 1 for 4) = CAST(prano as VARCHAR(4)))) THEN
                   BEGIN
                     SELECT bdfgts40 FROM vrhf_emp_val_grrf(:premp,:prcol,:wrescoriginal,:wrescoriginal, 0) INTO bdmulta;
                   END
              end
           else
              begin
                IF (wref = wrefresc) THEN
                   BEGIN
                     SELECT bdfgts40 FROM vrhf_emp_val_grrf(:premp,:prcol,:wref,:wref, 0) INTO bdmulta;
                   END
              end

           /* Zera todos valores */
           BDAVPREV = 0;
           BDFERIND = 0;
           BDGRATIF = 0;
           BDSINDICAL = 0;
           BDASSISTENCIAL = 0;
           BDCONFEDERATIVA = 0;
           bdbase13 = 0;
           bdbasea13 = 0;
           bdbasec13 = 0;
           bdbasefn = 0;
           bdmensalidade = 0;
         END

      wclass1 = SUBSTRING(wclass FROM 1 FOR 1);
      wclass2 = SUBSTRING(wclass FROM 1 FOR 5);
      wclass3 = SUBSTRING(wclass FROM 1 FOR 7);
      wclass4 = SUBSTRING(wclass FROM 1 FOR 9);

      IF (wbasrais = 'TRUE') THEN
         BEGIN
           wvalaux = wvalor;
           IF (wtipocod = 1) THEN
              wvalaux = wvalaux * -1;


           IF (wclass1 = '4') THEN
              BEGIN
                if (prano >= 2014) then
                   begin
                     if (wrefresc = 0) then
                        bdbasea13 = bdbasea13 + wvalaux;
                   end
                else
                   bdbasea13 = bdbasea13 + wvalaux;
              END
--           // Verba de 13º
           ELSE IF ((wclass1 = '5') OR (wclass2 = '7.060') OR
                   (wclass2 = '7.050') OR (wclass2 = '7.061')) THEN
              BEGIN
                if (prano >= 2014) then
                   begin
                     if (wrefresc = 0) then
                        bdbase13 = bdbase13 + wvalaux;
                   end
                else
                   bdbase13 = bdbase13 + wvalaux;
              END
--           // Verba de Complemento 13º
           ELSE IF ((wclass1 = '6')) THEN
              BEGIN
                if (prano >= 2014) then
                   begin
                     if (wrefresc = 0) then
                        bdbasec13 = bdbasec13 + wvalaux;
                   end
                else
                   bdbasec13 = bdbasec13 + wvalaux;
              END
--           // Demais Verbas participa da folha normal
           ELSE
              BEGIN
                if ((wclass3 <> '7.001.3') and (wclass2 <> '7.010') and (wclass2 <> '7.030') and (wclass2 <> '7.032') and (wclass2 <> '7.012') and
                    (wclass2 <> '7.031') and (wclass2 <> '7.033') and (wclass2 <> '7.020') and (wclass2 <> '7.021')) then
                   begin
                     if (prano >= 2014) then
                        begin
                          if (wrefresc = 0) then
                             bdbasefn = bdbasefn + wvalaux;
                        end
                     else
                        bdbasefn = bdbasefn + wvalaux;
                   end
              END
         END

      -- aviso prévio indenizado // 7.001.3
      if (prano >= 2014) then
         begin
           IF (substring(wrefresc from 1 for 4) = cast(prano as varchar(4)) and wclass3 = '7.001.3') THEN
              bdavprev = bdavprev + wvalor;
         end
      else
         begin
           IF (wclass3 = '7.001.3') THEN
              bdavprev = bdavprev + wvalor;
         end


      -- férias indenizadas // 7.020 - 7.021

      if (prano >= 2014) then
         begin
           if (substring(wrefresc from 1 for 4) = cast(prano as varchar(4))) then
              begin
                IF (((wclass2 = '7.010') OR (wclass2 = '7.012')) OR
                   ((wclass2 = '7.030') OR (wclass2 = '7.031')) OR
                   ((wclass2 = '7.032') OR (wclass2 = '7.033'))) THEN
                   bdferind = bdferind + wvalor;
              end
         end
      else
         begin
           IF (((wclass2 = '7.010') OR (wclass2 = '7.012')) OR
              ((wclass2 = '7.030') OR (wclass2 = '7.031')) OR
              ((wclass2 = '7.032') OR (wclass2 = '7.033'))) THEN
              bdferind = bdferind + wvalor;
         end

      -- gratificações // 0.003.0.0
      if (prano >= 2014) then
         begin
           IF (wref = wrefresc) THEN
              BEGIN
                IF (wclass4 = '0.003.0.0') THEN
                   bdgratif = bdgratif + wvalor;
              END
         end
      else
         begin
           IF (wref = wrefresc) THEN
              BEGIN
                IF (wclass4 = '0.003.0.0') THEN
                   bdgratif = bdgratif + wvalor;
              END
         end

      -- multa rescisória // 7.001.1.0 é a multa de FGTS
--      if (wclass4 = '7.001.1.0') then
--         bdmulta = bdmulta + wvalor;

      -- contribuição sindical // 0.004.0.1 - 0.004.0.0
      if (prano >= 2014) then
         begin
           IF ((wclass4 = '0.004.0.1') OR (wclass4 = '0.004.0.0') OR (wclass4 = '0.710.0.1') OR (wclass4 = '0.710.0.0') OR
               (wclass4 = '4.047.0.1') OR (wclass4 = '4.047.0.0') OR (wclass4 = '5.070.0.1') OR (wclass4 = '5.070.0.0') OR
               (wclass4 = '6.070.0.1') OR (wclass4 = '6.070.0.0')) THEN
              begin
                if ((wrefresc = 0) or (wrefresc = wrescoriginal) or (substring(wrefresc from 1 for 4) <> cast(prano as varchar(4)))) then
                   bdsindical = bdsindical + wvalor;
              end
         end
      else
         begin
           IF ((wclass4 = '0.004.0.1') OR (wclass4 = '0.004.0.0') OR (wclass4 = '0.710.0.1') OR (wclass4 = '0.710.0.0') OR
               (wclass4 = '4.047.0.1') OR (wclass4 = '4.047.0.0') OR (wclass4 = '5.070.0.1') OR (wclass4 = '5.070.0.0') OR
               (wclass4 = '6.070.0.1') OR (wclass4 = '6.070.0.0')) THEN
              bdsindical = bdsindical + wvalor;
         end

      if (prano >= 2014) then
         begin
           -- contribuição assistencial // 0.004.0.3
           IF ((wclass4 = '0.004.0.3') OR (wclass4 = '0.710.0.3') OR (wclass4 = '4.047.0.3') OR (wclass4 = '5.070.0.3') OR (wclass4 = '6.070.0.3')) THEN
              begin
                if ((wrefresc = 0) or (wrefresc = wrescoriginal) or (substring(wrefresc from 1 for 4) <> cast(prano as varchar(4)))) then
                   bdassistencial = bdassistencial + wvalor;
              end
     
           -- contribuição confederativa // 0.004.0.2
           IF ((wclass4 = '0.004.0.2') OR (wclass4 = '0.710.0.2') OR (wclass4 = '4.047.0.2') OR (wclass4 = '5.070.0.2') OR (wclass4 = '6.070.0.2')) THEN
              begin
                if ((wrefresc = 0) or (wrefresc = wrescoriginal) or (substring(wrefresc from 1 for 4) <> cast(prano as varchar(4)))) then
                   bdconfederativa = bdconfederativa + wvalor;
              end
     
           -- Mensalidade sindical
           IF ((wclass4 = '0.004.0.0') OR (wclass4 = '0.710.0.0') OR (wclass4 = '4.047.0.0') OR (wclass4 = '5.070.0.0') OR (wclass4 = '6.070.0.0')) THEN
              begin
                if ((wrefresc = 0) or (wrefresc = wrescoriginal) or (substring(wrefresc from 1 for 4) <> cast(prano as varchar(4)))) then
                   bdmensalidade = bdmensalidade + wvalor;
              end
         end
      else
         begin
           -- contribuição assistencial // 0.004.0.3
           IF ((wclass4 = '0.004.0.3') OR (wclass4 = '0.710.0.3') OR (wclass4 = '4.047.0.3') OR (wclass4 = '5.070.0.3') OR (wclass4 = '6.070.0.3')) THEN
              bdassistencial = bdassistencial + wvalor;
     
           -- contribuição confederativa // 0.004.0.2
           IF ((wclass4 = '0.004.0.2') OR (wclass4 = '0.710.0.2') OR (wclass4 = '4.047.0.2') OR (wclass4 = '5.070.0.2') OR (wclass4 = '6.070.0.2')) THEN
              bdconfederativa = bdconfederativa + wvalor;
     
           -- Mensalidade sindical
           IF ((wclass4 = '0.004.0.0') OR (wclass4 = '0.710.0.0') OR (wclass4 = '4.047.0.0') OR (wclass4 = '5.070.0.0') OR (wclass4 = '6.070.0.0')) THEN
              bdmensalidade = bdmensalidade + wvalor;
         end
    END

  IF (wexiste = 1) THEN
     SUSPEND;

  bdmensalidade = 0;
  -- busca valores da ficha financeira para meses que não tem informações
  wsqlaux = 'select a.bdreffrais,coalesce(a.bdrendfrais,0), coalesce(a.bda13frais,0), coalesce(a.bd13frais,0), coalesce(a.bdferiasrescisaofrais,0), '||
              ' coalesce(a.bdmultarescfrais,0), coalesce(a.bdcontrsindfrais,0), coalesce(a.bdcontassistfrais,0), '||
              ' coalesce(a.bdcontconffrais,0), coalesce(a.bdavisopreviofrais,0), coalesce(a.bdc13frais,0), '||
              ' coalesce(a.bdgratificacaofrais,0), coalesce(BDCONTAASS1FRAIS,0) from vrhf_emp_tficharais a '||
              ' where a.bdcodemp = '||:premp||' and a.bdcodcol = '||:prcol||
              ' and a.bdreffrais = '||:prano;
  bdorigem = 1;
  IF (wjan = 0) THEN
     BEGIN
       wsql = wsqlaux||'01';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wfev = 0) THEN
     BEGIN
       wsql = wsqlaux||'02';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wmar = 0) THEN
     BEGIN
       wsql = wsqlaux||'03';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wabr = 0) THEN
     BEGIN
       wsql = wsqlaux||'04';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wmai = 0) THEN
     BEGIN
       wsql = wsqlaux||'05';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wjun = 0) THEN
     BEGIN
       wsql = wsqlaux||'06';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wjul = 0) THEN
     BEGIN
       wsql = wsqlaux||'07';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wago = 0) THEN
     BEGIN
       wsql = wsqlaux||'08';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wset = 0) THEN
     BEGIN
       wsql = wsqlaux||'09';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wout = 0) THEN
     BEGIN
       wsql = wsqlaux||'10';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wnov = 0) THEN
     BEGIN
       wsql = wsqlaux||'11';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wdez = 0) THEN
     BEGIN
       wsql = wsqlaux||'12';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END
END
^

/* Restore proc. body: VSUC_SP_RETORNA_BALANCO_PATRI_X */
ALTER PROCEDURE VSUC_SP_RETORNA_BALANCO_PATRI_X(PRCODEMP VARCHAR(1024),
PRDATINI DATE,
PRDATFIN DATE,
PRIGNZER INTEGER,
PRCTALONINI VARCHAR(40),
PRCTALONFIN VARCHAR(40),
PRCODPLAPADRAO INTEGER,
PRNAOMOSTRACONTACOMP INTEGER,
PRCTASEMMOV INTEGER,
PRREFEMP INTEGER,
PRCONSOLIDADA INTEGER,
PRDETALHARCTAPARTICIPANTE INTEGER,
PRCONCATENACODPARTICIPANTE INTEGER,
PRNIVEISCONTA VARCHAR(50),
PRCONTABILIZACAO INTEGER,
PRMATRIZFILIALCONSOLIDADO INTEGER)
 RETURNS(BDCODEMP INTEGER,
BDCODMATRIZ INTEGER,
BDCODEMPFILIAL INTEGER,
BDNOMEMP DM_BDNOMEMP,
BDCNPJEMP VARCHAR(20),
BDCODPLAPADRAO INTEGER,
BDCODCTA INTEGER,
BDCTALON VARCHAR(40),
BDNOMCTA VARCHAR(100),
BDTIPCTA INTEGER,
BDSEQCTA VARCHAR(40),
BDSALANT NUMERIC(15,2),
BDSALMOV NUMERIC(15,2),
BDSALATU NUMERIC(15,2),
BDANAVER NUMERIC(15,2),
BDANAHOR NUMERIC(15,2),
BDFORMULA VARCHAR(1024),
BDGRPCTA INTEGER,
BDAPECTA VARCHAR(20))
 AS
declare variable WCODEMP integer;
declare variable WCODMATRIZ integer;
declare variable WCODEMPFILIAL integer;
declare variable WNOMEMP DM_BDNOMEMP;
declare variable WCNPJEMP varchar(20);
declare variable WCODPLAPADRAO integer;
declare variable WCODCTA integer;
declare variable WCTALON varchar(40);
declare variable WNOMCTA varchar(100);
declare variable WTIPCTA integer;
declare variable WFORMULA varchar(1024);
declare variable WGRPCTA integer;
declare variable WAPECTA varchar(20);
declare variable WSALANT numeric(15,2);
declare variable WSALATU numeric(15,2);
declare variable WPARTICIPANTE integer;
declare variable WCODCTAINI integer;
declare variable WCODCTAFIN integer;
declare variable WPOS integer;
declare variable WVALTIT numeric(15,2);
declare variable WSEQCTA varchar(100);
declare variable WCONTAANT integer;
declare variable WTERCEIROANT integer;
declare variable WEMPRESAANT integer;
declare variable WSQL varchar(2048);
begin
  -- retorna a primeira conta referente a classificacao
  select first 1 planos_tpla.bdcodtpla from planos_tpla
  where planos_tpla.bdcodplapadrao = :prcodplapadrao
  and planos_tpla.bdctalon = :prctalonini
  order by planos_tpla.bdctalon asc into :wcodctaini;
  -- retorna a ultima conta referente a classificacao
  select first 1 planos_tpla.bdcodtpla from planos_tpla
  where planos_tpla.bdcodplapadrao = :prcodplapadrao
  and planos_tpla.bdctalon = :prctalonfin
  order by planos_tpla.bdctalon asc into :wcodctafin;
    
  delete from vsuc_empresas_tbalancete_3 where bdcodemp <> 0;
  wcontaant = 0;
  wterceiroant = 0;
  wempresaant = 0;

  -- laco do calculo dos saldos do plano de contas
  wSQL = 'select bdcodemp, bdcodmatriz, bdcodempfilial, bdnomemp, bdcnpjemp,';
  wSQL = wSQL||'   bdcodplapadrao, bdcodcta, bdctalon, bdnomcta, bdtipcta, bdformula, bdgrpcta, bdapecta,';
  wSQL = wSQL||'   bdsaldo_anterior, bdsaldo_atual, bdcodter';
  wSQL = wSQL||' from vsuc_sp_retorna_balancete('''||:prcodemp||''', '''||:prdatini||''', '''||:prdatfin||''', '||:prignzer||', '||:wcodctaini||', '||:wcodctafin||', '''||''||''', ';
  wSQL = wSQL||'                                '||:prrefemp||', 0, 0, '||:prnaomostracontacomp||', '||:prconsolidada||', '||:prdetalharctaparticipante||', 0, ';
  wSQL = wSQL||'                                '||:prconcatenacodparticipante||', '''||:prniveisconta||''', 0, '||:prcontabilizacao||', 0, '||'0)';
  if (prmatrizfilialconsolidado = 0) then
    wSQL = wSQL||' order by BDCODEMPFILIAL, BDCTALON';
  else
    wSQL = wSQL||' order by BDCTALON, BDCODEMPFILIAL';
  for execute statement wSQL into :wcodemp, :wcodmatriz, :wcodempfilial, :wnomemp, :wcnpjemp,
                                  :wcodplapadrao, :wcodcta, :wctalon, :wnomcta, :wtipcta, :wformula, :wgrpcta, :wapecta,
                                  :wsalant, :wsalatu, :wparticipante do
  begin
    if ((((prctasemmov = 0) and ((bdsalatu <> 0) or (bdsalmov <> 0))) or (prctasemmov = 1)) and
        (((wcontaant > 0) and (wcontaant <> wcodcta)) or (wterceiroant <> wparticipante)  or ((prmatrizfilialconsolidado = 0) and (wempresaant > 0) and (wempresaant <> wcodempfilial)))) then
      begin
        suspend;
        BDCODEMP = 0;
        BDCODMATRIZ = 0;
        BDCODEMPFILIAL = 0;
        BDNOMEMP = '';
        BDCNPJEMP = '';
        BDCODPLAPADRAO = 0;
        BDCODCTA = 0;
        BDCTALON = '';
        BDNOMCTA = '';
        BDTIPCTA = 0;
        BDSEQCTA = '';
        BDSALANT = 0;
        BDSALMOV = 0;
        BDSALATU = 0;
        BDANAVER = 0;
        BDFORMULA = '';
        BDGRPCTA = 0;
        BDAPECTA = '';
      end

    -- Detalhado por participante
    if ((prdetalharctaparticipante = 1) and (wparticipante > 0) and (bdtipcta = 1) and (prconcatenacodparticipante = 1)) then
      begin
        wpos     = character_length(:wparticipante);
                   -- 100 -> máximo de caracteres para nome da conta
                   -- 03 -> qtd de caracteres " - ";
        wpos     = ((100 - wpos) - 3);
        bdnomcta = wparticipante||' - '||substring(bdnomcta from 1 for wpos);
      end

    BDCODEMP = wcodemp;
    BDNOMEMP = wnomemp;
    BDCNPJEMP = wcnpjemp;
    BDCODPLAPADRAO = wcodplapadrao;
    BDCODCTA = wcodcta;
    BDCTALON = wctalon;
    BDNOMCTA = wnomcta;
    BDTIPCTA = wtipcta;
    BDAPECTA = wapecta;
    BDSEQCTA = wseqcta;
    BDSALANT = coalesce(BDSALANT, 0) + wSalAnt;
    BDSALATU = coalesce(BDSALATU, 0) + wSalAtu;
    BDSALMOV = BDSALATU - BDSALANT;
    BDFORMULA = wformula;
    BDGRPCTA = wgrpcta;

    if (prmatrizfilialconsolidado = 1) then
      begin
        BDCODMATRIZ =  wcodemp;
        BDCODEMPFILIAL = wcodemp;
      end
    else
      begin
        BDCODMATRIZ =  wcodmatriz;
        BDCODEMPFILIAL = wcodempfilial;
      end

    -- sql para verificar se a conta atual é uma titulo geral
    if ((position('.', bdctalon) = 0) or ((prmatrizfilialconsolidado = 0) and (wempresaant > 0) and (wempresaant <> wcodempfilial))) then
      begin
        wseqcta = bdnomcta;
        if (bdsalatu is null) then
          wvaltit = 0;
        else
          wvaltit = bdsalatu;
      end
    bdseqcta = wseqcta;

    -- cálculo do percentual da analise vertical
    if (wvaltit = 0) then
      bdanaver = 0;
    else
      bdanaver = (bdsalatu * 100) / wvaltit;

    wcontaant = bdcodcta;
    wterceiroant = wparticipante;
    wempresaant = bdcodempfilial;
  end

  if ((((prctasemmov = 0) and ((bdsalatu <> 0) or (bdsalmov <> 0))) or (prctasemmov = 1)) and
      (((wcontaant > 0) and (wcontaant <> wcodcta)) or (wterceiroant <> wparticipante) or ((prmatrizfilialconsolidado = 0) and (wempresaant > 0) and (wempresaant <> wcodempfilial)))) then
    suspend;
end
^

/* Create Views... */
/* Create view: VW_AUTONOMOS_REF_ATUAL */
SET TERM ; ^

CREATE VIEW VW_AUTONOMOS_REF_ATUAL(
BDCODCOL,
BDCONTRCOL,
BDCODCLA,
BDGRUPOCLA,
BDCODMEN,
BDCODSINTRAB,
BDCODBAN,
BDCODAG,
BDCTADEPCOL,
BDDIGCTADEPCOL,
BDTIPOCTACOL,
BDMODOPAGTOCOL,
BDCARTAOSALCOL,
BDFORMAPAGTOCOL,
BDNRODEPIRCOL,
BDBASEIRRFADCOL,
BDVALORPREVPRIVNCOL,
BDVALORPREVPRIV13COL,
BDDATAOPCAOFGTSCOL,
BDCTAFGTSCOL,
BDCATEGORIACOL,
BDPERCFGTSCOL,
BDOCORRENCIACOL,
BDPERCRATCOL,
BDPERCDESCFPASCOL,
BDTIPOCOL,
BDHORASMENSAISCOL,
BDHORASSEMANAISCOL,
BDHORASDIARIASCOL,
BDPERCINSALUBCOL,
BDINCIDINSALUBCOL,
BDPERCPERCULCOL,
BDINCIDPERICULCOL,
BDPERCNOTURNOCOL,
BDINCIDNOTURNOCOL,
BDNRODEPSALFAMCOL,
BDIGNORAFALTASFERCOL,
BDCALCADIANTACOL,
BDNAOARREDONDARCOL,
BDIMPETIQUETACOL,
BDCALCDSRRVCOL,
BDCALCDSRHECOL,
BDCALCDSRSALCOL,
BDCALCVALTRANSCOL,
BDCALCVALREFCOL,
BDOBSCOL,
BDCODGPS,
BDDESCINSSDOIRCOL)
 AS 
SELECT COLCON.BDCODTER,
       0,
       COLCON_VINC.BDCODCLA,
       CLASSE.BDGRUPOCLA,
       0,
       0,
       COLCON_BANCO.BDCODBAN,
       COLCON_BANCO.BDCODAG,
       COLCON_BANCO.BDCTADEPCOL,
       COLCON_BANCO.BDDIGCTADEPCOL ,
       COLCON_BANCO.BDTIPOCTACOL,
       COLCON_BANCO.BDMODOPAGTOCOL,
       COLCON_BANCO.BDCARTAOSALCOL,
       0,
       COLCON_CALC.BDNRODEPIRCOL,
       COLCON_CALC.BDBASEIRRFADCOL ,
       COLCON_CALC.BDVALORPREVPRIVNCOL,
       COLCON_CALC.BDVALORPREVPRIV13COL, 
       COLCON_FGTSGPS.BDDATAOPCAOFGTSCOL,
       CAST('' AS VARCHAR(11)),
       COLCON_FGTSGPS.BDCATEGORIACOL ,
       COLCON_FGTSGPS.BDPERCFGTSCOL,
       COLCON_FGTSGPS.BDOCORRENCIACOL ,
       COLCON_FGTSGPS.BDPERCRATCOL,
       COLCON_FGTSGPS.BDPERCDESCFPASCOL,
       0,
       0.0,
       0.0,
       0.0,
       0.0,
       0,
       0.0,
       0,
       0.0,
       0,
       0,
       CAST('' AS VARCHAR(5)),
       CAST('' AS VARCHAR(5)),
       CAST('' AS VARCHAR(5)),
       CAST('' AS VARCHAR(5)),
       CAST('' AS VARCHAR(5)),
       CAST('' AS VARCHAR(5)),
       CAST('' AS VARCHAR(5)),
       CAST('' AS VARCHAR(5)),
       CAST('' AS VARCHAR(5)),
       CAST('' AS VARCHAR(5)),
       0,
       CAST('' AS VARCHAR(5))

FROM vw_VRH_BASE_TTERC COLCON
LEFT OUTER JOIN VW_vrh_base_tterc_vinc COLCON_VINC
  ON (COLCON_VINC.BDCODCOL = COLCON.BDCODTER)
LEFT OUTER JOIN VW_VRHF_BASE_TTERC_BANCO COLCON_BANCO
  ON (COLCON_BANCO.BDCODCOL = COLCON.BDCODTER)
LEFT OUTER JOIN VW_vrh_base_tterc_ref COLCON_CALC
  ON (COLCON_CALC.BDCODCOL = COLCON.BDCODTER)
LEFT OUTER JOIN VW_vrhf_base_tterc_fgtsgps COLCON_FGTSGPS
  ON (COLCON_FGTSGPS.BDCODCOL = COLCON.BDCODTER)
LEFT OUTER JOIN VRH_BASE_TCLASSE CLASSE
  ON (COLCON_VINC.BDCODCLA = CLASSE.BDCODCLA)
;

/* Create view: VW_VRHF_EMPRESAS_REF_ATUAL */
CREATE VIEW VW_VRHF_EMPRESAS_REF_ATUAL(
BDCODEMP,
BDPAGEMP,
BDFECHAMESEMP,
BDFECHAVTEMP,
BDARREMP,
BDTIPOARREMP,
BDTIPOFEREMP,
BDSOMSALFEREMP,
BDTIPOAD13EMP,
BDSOMSALAD13EMP,
BDPERCADSALEMP,
BDREFHOREMP,
BDLICREMFEREMP,
BDESTOUROEMP,
BDESTOURORESEMP,
BDFALFEREMP,
BDFALHORFEREMP,
BDDEPIRRESCEMP,
BDMESDSREMP,
BDCODGPS,
BDCNAERATEMP,
BDEMPEMP,
BDAUTEMP,
BDFUNEMP,
BDCODFPASTERC,
BDFAP,
BDCODFPAS,
BDCODTERC,
BDPERTERC,
BDSIMPLESGPS,
BDCODSINPAT,
BDCAPSOCEMP,
BDETIQEMP,
BDTAXSEREMP,
BDREFCAGEDEMP,
BDCALCFCNAFNEMP,
BDCALCC13NAFNEMP,
BDTIPOCONTAB,
BDTIPOEMIGUIAS,
BDPROPADMEMP,
BDPROPRESEMP,
BDPROPFEREMP,
BDPROPSITEMP,
BDCALCMEDIASTEMP,
BDNATESTEMP,
BDDATABASEEMP,
BDPATEMP,
BDNROPROPEMP,
BDSERPROPRIOPATEMP,
BDPATREFCONVENIOEMP,
BDPATCESTAALIMENTOEMP,
BDPATADMCOZINHAEMP,
BDPATREFTRANSPEMP,
BDPATALICONVENIOEMP,
BDSINDICALIZEMP,
BDCODBAN,
BDCODAG,
BDIDENTEMP,
BDCODUFSEFIP,
BDCATSEFIPEMP,
BDISENFILEMP,
BDCETRTALIZAEMP,
BDCODSEFIPEMP,
BDCODIR,
BDCODINSS,
BDCODMEN,
BDCODPQUA,
BDPROPTOMEMP,
BDDIASTRABADI,
BDANOSAVPREVIOIND,
BDCODPLACONVSAU,
BDTIPCALCFERCOLINF,
BDTIPCALCFERCOLSUP,
BDLIMINARAVPREVEMP,
BDARREDONDAFN,
BDARREDONDAFC,
BDARREDONDAFE,
BDARREDONDAA13,
BDARREDONDA13,
BDARREDONDAC13,
BDARREDONDAAD,
BDARREDONDAAU,
BDARREDONDAFA,
BDPAGEMPCONTR)
 AS 
SELECT EMP_REF.BDCODEMP,
       EMP_CALC.BDPAGEMP,
       EMP_CALC.BDFECHAMESEMP,
       EMP_CALC.BDFECHAVTEMP,
       EMP_CALC.BDARREMP,
       EMP_CALC.BDTIPOARREMP,
       EMP_CALC.BDTIPOFEREMP,
       EMP_CALC.BDSOMSALFEREMP,
       EMP_CALC.BDTIPOAD13EMP,
       EMP_CALC.BDSOMSALAD13EMP,
       EMP_CALC.BDPERCADSALEMP,
       EMP_CALC.BDREFHOREMP,
       EMP_CALC.BDLICREMFEREMP,
       EMP_CALC.BDESTOUROEMP,
       EMP_CALC.BDESTOURORESEMP,
       EMP_CALC.BDFALFEREMP,
       EMP_CALC.BDFALHORFEREMP,
       EMP_CALC.BDDEPIRRESCEMP,
       EMP_CALC.BDMESDSREMP,
       EMP_GPS.BDCODGPS,
       EMP_GPS.BDCNAERATEMP,
       EMP_GPS.BDEMPEMP,
       EMP_GPS.BDAUTEMP,
       EMP_GPS.BDFUNEMP,
       EMP_GPS.BDCODFPASTERC,
       EMP_GPS.BDFAP,
       EMP_GPS.BDCODFPAS,
       EMP_GPS.BDCODTERC,
       EMP_GPS.BDPERTERC,
       EMP_GPS.BDSIMPLESGPS,
       EMP_GRCS.BDCODSINPAT,
       EMP_GRCS.BDCAPSOCEMP,
       EMP_PAR.BDETIQEMP,
       EMP_PAR.BDTAXSEREMP,
       EMP_PAR.BDREFCAGEDEMP,
       EMP_PAR.BDCALCFCNAFNEMP,
       EMP_PAR.BDCALCC13NAFNEMP,
       EMP_PAR.BDTIPOCONTAB,
       EMP_PAR.BDTIPOEMIGUIAS,
       EMP_PROP.BDPROPADMEMP,
       EMP_PROP.BDPROPRESEMP,
       EMP_PROP.BDPROPFEREMP,
       EMP_PROP.BDPROPSITEMP,
       EMP_PROP.BDCALCMEDIASTEMP,
       EMP_RAIS.BDNATESTEMP,
       EMP_RAIS.BDDATABASEEMP,
       EMP_RAIS.BDPATEMP,
       EMP_RAIS.BDNROPROPEMP,
       EMP_RAIS.BDSERPROPRIOPATEMP,
       EMP_RAIS.BDPATREFCONVENIOEMP,
       EMP_RAIS.BDPATCESTAALIMENTOEMP,
       EMP_RAIS.BDPATADMCOZINHAEMP,
       EMP_RAIS.BDPATREFTRANSPEMP,
       EMP_RAIS.BDPATALICONVENIOEMP,
       EMP_RAIS.BDSINDICALIZEMP,
       EMP_SEFIP.BDCODBAN,
       EMP_SEFIP.BDCODAG,
       EMP_SEFIP.BDIDENTEMP,
       EMP_SEFIP.bdcoduf,
       EMP_SEFIP.BDCATSEFIPEMP,
       EMP_SEFIP.BDISENFILEMP,
       EMP_SEFIP.BDCETRTALIZAEMP,
       EMP_SEFIP.BDCODSEFIPEMP,
       EMP_VINC.BDCODIR,
       EMP_VINC.BDCODINSS,
       EMP_VINC.BDCODMEN,
       EMP_VINC.BDCODPQUA,
       EMP_PROP.BDPROPTOMEMP,
       EMP_PROP.BDDIASTRABADI,
       EMP_CALC.bdanosavprevioind,
       EMP_VINC.BDCODPLACONVSAU,
       EMP_CALC.BDTIPCALCFERCOLINF,
       EMP_CALC.BDTIPCALCFERCOLSUP,
       EMP_CALC.BDLIMINARAVPREVEMP,
       EMP_CALC.BDARREDONDAFN,
       EMP_CALC.BDARREDONDAFC,
       EMP_CALC.BDARREDONDAFE,
       EMP_CALC.BDARREDONDAA13,
       EMP_CALC.BDARREDONDA13,
       EMP_CALC.BDARREDONDAC13,
       EMP_CALC.BDARREDONDAAD,
       EMP_CALC.BDARREDONDAAU,
       EMP_CALC.BDARREDONDAFA,
       EMP_CALC.BDPAGEMPCONTR


FROM TEMPRESAS EMP_REF
LEFT OUTER JOIN VW_VRHF_BASE_TEMPCALC EMP_CALC ON (EMP_CALC.BDCODEMP = EMP_REF.BDCODEMP)
LEFT OUTER JOIN VW_VRHF_BASE_TEMPGPS EMP_GPS ON (EMP_GPS.BDCODEMP = EMP_REF.BDCODEMP)
LEFT OUTER JOIN VW_VRHF_BASE_TEMPGRCS EMP_GRCS ON (EMP_GRCS.BDCODEMP = EMP_REF.BDCODEMP)
LEFT OUTER JOIN VW_VRHF_BASE_TEMPPAR EMP_PAR ON (EMP_PAR.BDCODEMP = EMP_REF.BDCODEMP)
LEFT OUTER JOIN VW_VRHF_BASE_TEMPPROP EMP_PROP ON (EMP_PROP.BDCODEMP = EMP_REF.BDCODEMP)
LEFT OUTER JOIN VW_VRHF_BASE_TEMPRAIS EMP_RAIS ON (EMP_RAIS.BDCODEMP = EMP_REF.BDCODEMP)
LEFT OUTER JOIN VW_VRHF_BASE_TEMPSEFIP EMP_SEFIP ON (EMP_SEFIP.BDCODEMP = EMP_REF.BDCODEMP)
LEFT OUTER JOIN VW_VRHF_BASE_TEMPVINC EMP_VINC ON (EMP_VINC.BDCODEMP = EMP_REF.BDCODEMP)
;


/* Altering existing trigger... */
SET TERM ^ ;

ALTER TRIGGER TEMPRESAS_REF_AI1
AS
  DECLARE VARIABLE vCount INTEGER;
  DECLARE VARIABLE vrefant INTEGER;
  DECLARE VARIABLE vrefini INTEGER;
  DECLARE VARIABLE vreffin INTEGER;
  DECLARE VARIABLE vrefaux INTEGER;
  DECLARE VARIABLE vcodmatriz INTEGER;
  DECLARE VARIABLE vcodimp INTEGER;
  DECLARE VARIABLE vcodimp1 INTEGER;
  DECLARE VARIABLE vcodimp2 INTEGER;
  DECLARE VARIABLE vclassimp VARCHAR(20);
  DECLARE VARIABLE vcodplatrib INTEGER;
BEGIN
  EXECUTE PROCEDURE sp_atualiza_apelido_ie_emp(NEW.bdcodemp,NEW.bdrefemp);

  /* IMPOSTOS COM MAIS DE UMA REFERENCIA EM ABERTO */
  for select bdcodimp, bdrefemp, bdreffimimp from tempresaimposto imp
      where bdcodemp = new.bdcodemp
      and (select count(*) from tempresaimposto where bdcodemp = imp.bdcodemp and bdcodimp = imp.bdcodimp and bdreffimimp is null) > 1
      order by bdcodemp, bdcodimp, bdrefemp desc
      into :vcodimp, :vrefini, :vreffin do
      begin
        if (vcodimp <> vcodimp1) then
           vrefant = null;
        if (vrefant is not null) then
        begin
          IF (SUBSTRING(vrefant FROM 5 FOR 2) = '01') THEN
             vrefant = vrefant - 89;
          ELSE
             vrefant = vrefant - 1;
          update tempresaimposto set bdreffimimp = :vrefant where bdcodemp = new.bdcodemp and bdcodimp = :vcodimp and bdrefemp = :vrefini;
        end
        vcodimp1 = vcodimp;
        vrefant = vrefini;
      end
  /* OBRIGAÇÕES COM MAIS DE UMA REFERENCIA EM ABERTO */
  for select bdcodobr, bdrefini, bdreffin from vct_tributario_empresa trib
      where bdcodemp = NEW.BDCODEMP
      and bdcodobr is not null
      and (select count(bdcodemp) from vct_tributario_empresa where bdcodemp = trib.bdcodemp and bdcodobr = trib.bdcodobr and bdreffin is null) > 1
      order by bdcodemp, bdcodobr, bdrefini desc
      into :vcodimp, :vrefini, :vreffin do
      begin
        if (vcodimp <> vcodimp1) then
           vrefant = null;
        if (vrefant is not null) then
        begin
          IF (SUBSTRING(vrefant FROM 5 FOR 2) = '01') THEN
             vrefant = vrefant - 89;
          ELSE
             vrefant = vrefant - 1;
          update VCT_TRIBUTARIO_EMPRESA set bdreffiN = :vrefant where bdcodemp = new.bdcodemp and bdcodOBR = :vcodimp and bdrefINI = :vrefini;
        end
        vcodimp1 = vcodimp;
        vrefant = vrefini;
      end
  /* SERVIÇOS COM MAIS DE UMA REFERENCIA EM ABERTO */
  for select bdcodobr, bdrefini, bdreffin from vct_tributario_empresa trib
      where bdcodemp = NEW.BDCODEMP
      and bdcodSER is not null
      and (select count(bdcodemp) from vct_tributario_empresa where bdcodemp = trib.bdcodemp and bdcodSER = trib.bdcodser and bdreffin is null) > 1
      order by bdcodemp, bdcodobr, bdrefini desc
      into :vcodimp, :vrefini, :vreffin do
      begin
        if (vcodimp <> vcodimp1) then
           vrefant = null;
        if (vrefant is not null) then
        begin
          IF (SUBSTRING(vrefant FROM 5 FOR 2) = '01') THEN
             vrefant = vrefant - 89;
          ELSE
             vrefant = vrefant - 1;
          update VCT_TRIBUTARIO_EMPRESA set bdreffiN = :vrefant where bdcodemp = new.bdcodemp and bdcodSER = :vcodimp and bdrefINI = :vrefini;
        end
        vcodimp1 = vcodimp;
        vrefant = vrefini;
      end

  /* APENAS SE HOUVER MUDANÇA DE PLANO TRIBUTÁRIO ----------------------------------------------------------------------------------------------------------------------------------------------------- */
  IF (COALESCE(NEW.bdcodplatrib, 0) <> COALESCE(OLD.bdcodplatrib, 0)) THEN
  BEGIN
    /* PLANO TRIBUTÀRIO ANTERIOR */
    IF (OLD.bdcodplatrib IS NULL) THEN
       SELECT FIRST 1 BDCODPLATRIB FROM tempresas_ref WHERE BDCODEMP = NEW.BDCODEMP AND BDREFEMP < NEW.bdrefemp ORDER BY BDREFEMP DESC INTO :vcodplatrib;
    ELSE
       vcodplatrib = OLD.bdcodplatrib;
    /* REFERENCIA ANTERIOR */
    vrefant = NEW.bdrefemp;
    IF (SUBSTRING(vrefant FROM 5 FOR 2) = '01') THEN
       vrefant = vrefant - 89;
    ELSE
       vrefant = vrefant - 1;
    /* MATRIZ */
    SELECT bdcodmatriz FROM tempresas WHERE bdcodemp = NEW.bdcodemp INTO :vcodmatriz;

/* IMPOSTOS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- */
    /* LISTA OS IMPOSTOS DOS DOIS PLANOS, ANTERIOR E ATUAL, PARA COMPARAR AS MUDANÇAS E COM ISSO FINALIZA OU INSERE OS IMPOSTOS */
    FOR SELECT DISTINCT --bdcodplatrib1, bdcodplatrib2,
               CASE WHEN (bdcodimp1 IS NULL) THEN bdcodimp2 ELSE bdcodimp1 END bdcodimp,
               bdcodimp1, bdcodimp2, imp.bdclassimp AS bdclassimp2
        FROM (
        SELECT trib1.bdcodplatrib AS bdcodplatrib1, trib1.bdcodimp AS bdcodimp1, trib2.bdcodplatrib AS bdcodplatrib2, trib2.bdcodimp AS bdcodimp2 FROM vct_tributos_plano_tributario trib1
        LEFT JOIN vct_tributos_plano_tributario trib2 ON (trib2.bdcodplatrib = NEW.BDCODPLATRIB AND trib2.bdcodimp IS NOT NULL AND trib1.bdcodimp = trib2.bdcodimp)
        WHERE trib1.bdcodplatrib = :vcodplatrib
        AND trib1.bdcodimp IS NOT NULL
        UNION
        SELECT trib2.bdcodplatrib, trib2.bdcodimp, trib1.bdcodplatrib, trib1.bdcodimp FROM vct_tributos_plano_tributario trib1
        LEFT JOIN vct_tributos_plano_tributario trib2 ON (trib2.bdcodplatrib = :vcodplatrib AND trib2.bdcodimp IS NOT NULL AND trib1.bdcodimp = trib2.bdcodimp)
        WHERE trib1.bdcodplatrib = NEW.BDCODPLATRIB
        AND trib1.bdcodimp IS NOT NULL
        )
        LEFT OUTER JOIN timposto imp ON (imp.bdcodimp = bdcodimp2 AND imp.bdperapuracao <> 11)
        ORDER BY bdcodimp
        INTO :vcodimp, :vcodimp1, :vcodimp2, :vclassimp DO
        BEGIN
          IF (vcodimp2 IS NULL) THEN
          BEGIN
            UPDATE tempresaimposto SET bdreffimimp = :vrefant WHERE bdcodemp = NEW.bdcodemp AND bdcodimp = :vcodimp AND (bdreffimimp IS NULL OR (bdreffimimp = 0));
          END
          ELSE
          /* quando for filial, não importar esses tributos, pois os mesmos são calculados apenas pela matriz */
          IF ((vcodmatriz IS NULL) OR
              ((vcodmatriz IS NOT NULL) AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.01.03')) <> '01.01.01.03') AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.01.04')) <> '01.01.01.04') AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.02.01')) <> '01.01.02.01') AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.02.02')) <> '01.01.02.02') AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.02.03')) <> '01.01.02.03') AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.02.04')) <> '01.01.02.04') AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.02.06')) <> '01.01.02.06') AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.02.08')) <> '01.01.02.08') AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.02.09')) <> '01.01.02.09') AND
               (SUBSTRING(vclassimp FROM 1 FOR CHAR_LENGTH('01.01.02.10')) <> '01.01.02.10'))) THEN
          BEGIN
            vcount = NULL;
            SELECT FIRST 1 bdcodimp FROM tempresaimposto WHERE bdcodemp = NEW.bdcodemp AND bdcodimp = :vcodimp AND bdrefemp <= NEW.bdrefemp AND bdreffimimp IS NULL INTO :vcount;
            IF (vcount IS NULL) THEN
               UPDATE OR INSERT INTO tempresaimposto (bdcodemp, bdrefemp, bdcodimp, bdreffimimp) VALUES (NEW.bdcodemp, NEW.bdrefemp, :vcodimp, NULL) MATCHING (bdcodemp, bdrefemp, bdcodimp);

            vrefaux = NULL;
            FOR SELECT bdrefemp FROM tempresaimposto WHERE bdcodemp = NEW.bdcodemp AND bdcodimp = :vcodimp AND bdrefemp > NEW.bdrefemp AND bdreffimimp IS NULL INTO :vrefaux DO
            BEGIN
              IF (vrefaux IS NOT NULL) THEN
              BEGIN
                IF (SUBSTRING(vrefaux FROM 5 FOR 2) = '01') THEN
                   vrefaux = vrefaux - 89;
                ELSE
                   vrefaux = vrefaux - 1;
  
                UPDATE tempresaimposto SET bdreffimimp = :vrefaux WHERE bdcodemp = NEW.bdcodemp AND bdcodimp = :vcodimp AND bdrefemp = NEW.bdrefemp AND bdreffimimp IS NULL;
              END
            END
          END
        END
    /* AJUSTA REFERÊNCIAS QUANDO ALTERA UM ANTERIOR A OUTRA QUE JÁ FOI INSERIDA */
    vcodimp1 = 0;
    FOR SELECT bdcodimp, bdrefemp, bdreffimimp
        FROM tempresaimposto imp
        WHERE bdcodemp = NEW.bdcodemp
        AND (SELECT COUNT(*) FROM tempresaimposto WHERE bdcodemp = imp.bdcodemp AND bdcodimp = imp.bdcodimp) > 1
        ORDER BY bdcodemp, bdcodimp, bdrefemp DESC
        INTO :vcodimp, :vrefini, :vreffin DO
        BEGIN
          IF (vcodimp1 <> vcodimp) THEN
             vrefant = 0;

          IF ((vrefini IS NOT NULL) AND (vrefant <> 0)) THEN
          BEGIN
            IF (NOT ((vreffin >= vrefini) AND (vreffin <= vrefant))) THEN
            BEGIN
              UPDATE tempresaimposto SET bdreffimimp = :vrefant WHERE bdcodemp = NEW.bdcodemp AND bdcodimp = :vcodimp AND bdrefemp = :vrefini;
            END
          END

          vcodimp1 = vcodimp;
          IF (SUBSTRING(vrefini FROM 5 FOR 2) = '01') THEN
             vrefant = vrefini - 89;
          ELSE
             vrefant = vrefini - 1;
        END

/* OBRIGAÇÕES ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- */
    /* LISTA OS IMPOSTOS DOS DOIS PLANOS, ANTERIOR E ATUAL, PARA COMPARAR AS MUDANÇAS E COM ISSO FINALIZA OU INSERE OS IMPOSTOS */
    FOR SELECT DISTINCT --bdcodplatrib1, bdcodplatrib2,
               CASE WHEN (bdcodOBR1 IS NULL) THEN bdcodOBR2 ELSE bdcodOBR1 END bdcodimp,
               bdcodOBR1, bdcodOBR2
        FROM (
        SELECT trib1.bdcodplatrib AS bdcodplatrib1, trib1.bdcodobr AS bdcodOBR1, trib2.bdcodplatrib AS bdcodplatrib2, trib2.bdcodobr AS bdcodOBR2 FROM vct_tributos_plano_tributario trib1
        LEFT JOIN vct_tributos_plano_tributario trib2 ON (trib2.bdcodplatrib = NEW.bdcodplatrib AND trib2.bdcodOBR IS NOT NULL AND trib1.bdcodOBR = trib2.bdcodobr)
        WHERE trib1.bdcodplatrib = :vcodplatrib
        AND trib1.bdcodOBR IS NOT NULL
        UNION
        SELECT trib2.bdcodplatrib, trib2.bdcodOBR, trib1.bdcodplatrib, trib1.bdcodobr FROM vct_tributos_plano_tributario trib1
        LEFT JOIN vct_tributos_plano_tributario trib2 ON (trib2.bdcodplatrib = :vcodplatrib AND trib2.bdcodOBR IS NOT NULL AND trib1.bdcodOBR = trib2.bdcodOBR)
        WHERE trib1.bdcodplatrib = NEW.bdcodplatrib 
        AND trib1.bdcodOBR IS NOT NULL
        )
        LEFT OUTER JOIN vct_base_obrigacao imp ON (imp.bdcodobrser = bdcodOBR2 AND imp.bdperiodicidade <> 11)
        ORDER BY bdcodimp
        INTO :vcodimp, :vcodimp1, :vcodimp2 DO
        BEGIN
          IF (vcodimp2 IS NULL) THEN
          BEGIN
            UPDATE vct_tributario_empresa SET bdreffiN = :vrefant WHERE bdcodemp = NEW.bdcodemp AND bdcodOBR = :vcodimp AND (bdreffiN IS NULL OR (bdreffiN = 0));
          END
          ELSE
          BEGIN
            vcount = NULL;
            SELECT FIRST 1 bdcodOBR FROM vct_tributario_empresa WHERE bdcodemp = NEW.bdcodemp AND bdcodOBR = :vcodimp AND bdrefINI <= NEW.bdrefemp AND bdreffiN IS NULL INTO :vcount;
            IF (vcount IS NULL) THEN
               UPDATE OR INSERT INTO vct_tributario_empresa (bdcodemp, bdrefINI, bdcodOBR, bdreffiN) VALUES (NEW.bdcodemp, NEW.bdrefemp, :vcodimp, NULL) MATCHING (bdcodemp, bdrefINI, bdcodOBR);

            vrefaux = NULL;
            FOR SELECT bdrefINI FROM vct_tributario_empresa WHERE bdcodemp = NEW.bdcodemp AND bdcodOBR = :vcodimp AND bdrefINI > NEW.bdrefemp AND bdreffiN IS NULL INTO :vrefaux DO
            BEGIN
              IF (vrefaux IS NOT NULL) THEN
              BEGIN
                IF (SUBSTRING(vrefaux FROM 5 FOR 2) = '01') THEN
                   vrefaux = vrefaux - 89;
                ELSE
                   vrefaux = vrefaux - 1;
  
                UPDATE vct_tributario_empresa SET bdreffiN = :vrefaux WHERE bdcodemp = NEW.bdcodemp AND bdcodOBR = :vcodimp AND bdrefINI = NEW.bdrefemp AND bdreffiN IS NULL;
              END
            END
          END
        END
    /* AJUSTA REFERÊNCIAS QUANDO ALTERA UM ANTERIOR A OUTRA QUE JÁ FOI INSERIDA */
    vcodimp1 = 0;
    FOR SELECT bdcodobr, bdrefini, bdreffin
        FROM vct_tributario_empresa imp
        WHERE bdcodemp = NEW.bdcodemp
        AND bdcodobr IS NOT NULL
        AND (SELECT COUNT(*) FROM vct_tributario_empresa WHERE bdcodemp = imp.bdcodemp AND bdcodobr = imp.bdcodobr) > 1
        ORDER BY bdcodemp, bdcodobr, bdrefini DESC
        INTO :vcodimp, :vrefini, :vreffin DO
        BEGIN
          IF (vcodimp1 <> vcodimp) THEN
             vrefant = 0;

          IF ((vrefini IS NOT NULL) AND (vrefant <> 0)) THEN
          BEGIN
            IF (NOT ((vreffin >= vrefini) AND (vreffin <= vrefant))) THEN
            BEGIN
              UPDATE vct_tributario_empresa SET bdreffin = :vrefant WHERE bdcodemp = NEW.bdcodemp AND bdcodobr = :vcodimp AND bdrefini = :vrefini;
            END
          END

          vcodimp1 = vcodimp;
          IF (SUBSTRING(vrefini FROM 5 FOR 2) = '01') THEN
             vrefant = vrefini - 89;
          ELSE
             vrefant = vrefini - 1;
        END

/* SERVIÇOS ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- */
    /* LISTA OS IMPOSTOS DOS DOIS PLANOS, ANTERIOR E ATUAL, PARA COMPARAR AS MUDANÇAS E COM ISSO FINALIZA OU INSERE OS IMPOSTOS */
    FOR SELECT DISTINCT --bdcodplatrib1, bdcodplatrib2,
               CASE WHEN (bdcodSER1 IS NULL) THEN bdcodSER2 ELSE bdcodSER1 END bdcodimp,
               bdcodSER1, bdcodSER2
        FROM (
        SELECT trib1.bdcodplatrib AS bdcodplatrib1, trib1.bdcodSER AS bdcodSER1, trib2.bdcodplatrib AS bdcodplatrib2, trib2.bdcodSER AS bdcodSER2 FROM vct_tributos_plano_tributario trib1
        LEFT JOIN vct_tributos_plano_tributario trib2 ON (trib2.bdcodplatrib = NEW.bdcodplatrib AND trib2.bdcodser IS NOT NULL AND trib1.bdcodSER = trib2.bdcodSER)
        WHERE trib1.bdcodplatrib = :vcodplatrib
        AND trib1.bdcodSER IS NOT NULL
        UNION
        SELECT trib2.bdcodplatrib, trib2.bdcodSER, trib1.bdcodplatrib, trib1.bdcodSER FROM vct_tributos_plano_tributario trib1
        LEFT JOIN vct_tributos_plano_tributario trib2 ON (trib2.bdcodplatrib = :vcodplatrib AND trib2.bdcodSER IS NOT NULL AND trib1.bdcodSER = trib2.bdcodSER)
        WHERE trib1.bdcodplatrib = NEW.bdcodplatrib 
        AND trib1.bdcodSER IS NOT NULL
        )
        LEFT OUTER JOIN vct_base_obrigacao imp ON (imp.bdcodobrser = bdcodSER2 AND imp.bdperiodicidade <> 11)
        ORDER BY bdcodimp
        INTO :vcodimp, :vcodimp1, :vcodimp2 DO
        BEGIN
          IF (vcodimp2 IS NULL) THEN
          BEGIN
            UPDATE vct_tributario_empresa SET bdreffiN = :vrefant WHERE bdcodemp = NEW.bdcodemp AND bdcodser = :vcodimp AND (bdreffiN IS NULL OR (bdreffiN = 0));
          END
          ELSE
          BEGIN
            vcount = NULL;
            SELECT FIRST 1 bdcodser FROM vct_tributario_empresa WHERE bdcodemp = NEW.bdcodemp AND bdcodser = :vcodimp AND bdrefINI <= NEW.bdrefemp AND bdreffiN IS NULL INTO :vcount;
            IF (vcount IS NULL) THEN
               UPDATE OR INSERT INTO vct_tributario_empresa (bdcodemp, bdrefINI, bdcodser, bdreffiN) VALUES (NEW.bdcodemp, NEW.bdrefemp, :vcodimp, NULL) MATCHING (bdcodemp, bdrefINI, bdcodser);

            vrefaux = NULL;
            FOR SELECT bdrefINI FROM vct_tributario_empresa WHERE bdcodemp = NEW.bdcodemp AND bdcodser = :vcodimp AND bdrefINI > NEW.bdrefemp AND bdreffiN IS NULL INTO :vrefaux DO
            BEGIN
              IF (vrefaux IS NOT NULL) THEN
              BEGIN
                IF (SUBSTRING(vrefaux FROM 5 FOR 2) = '01') THEN
                   vrefaux = vrefaux - 89;
                ELSE
                   vrefaux = vrefaux - 1;
  
                UPDATE vct_tributario_empresa SET bdreffiN = :vrefaux WHERE bdcodemp = NEW.bdcodemp AND bdcodser = :vcodimp AND bdrefINI = NEW.bdrefemp AND bdreffiN IS NULL;
              END
            END
          END
        END
    /* AJUSTA REFERÊNCIAS QUANDO ALTERA UM ANTERIOR A OUTRA QUE JÁ FOI INSERIDA */
    vcodimp1 = 0;
    FOR SELECT bdcodser, bdrefini, bdreffin
        FROM vct_tributario_empresa imp
        WHERE bdcodemp = NEW.bdcodemp
        AND bdcodser IS NOT NULL
        AND (SELECT COUNT(*) FROM vct_tributario_empresa WHERE bdcodemp = imp.bdcodemp AND bdcodser = imp.bdcodser) > 1
        ORDER BY bdcodemp, bdcodser, bdrefini DESC
        INTO :vcodimp, :vrefini, :vreffin DO
        BEGIN
          IF (vcodimp1 <> vcodimp) THEN
             vrefant = 0;

          IF ((vrefini IS NOT NULL) AND (vrefant <> 0)) THEN
          BEGIN
            IF (NOT ((vreffin >= vrefini) AND (vreffin <= vrefant))) THEN
            BEGIN
              UPDATE vct_tributario_empresa SET bdreffin = :vrefant WHERE bdcodemp = NEW.bdcodemp AND bdcodser = :vcodimp AND bdrefini = :vrefini;
            END
          END

          vcodimp1 = vcodimp;
          IF (SUBSTRING(vrefini FROM 5 FOR 2) = '01') THEN
             vrefant = vrefini - 89;
          ELSE
             vrefant = vrefini - 1;
        END
  END

  /* CASO REFERENCIA FINAL FOR MENOR QUE INICIAL */
  UPDATE tempresaimposto SET bdreffimimp = bdrefemp WHERE bdreffimimp < bdrefemp;
  UPDATE vct_tributario_empresa SET bdreffin = bdrefINI WHERE bdreffin < bdrefini AND BDCODOBR IS NOT NULL;
  UPDATE vct_tributario_empresa SET bdreffin = bdrefINI WHERE bdreffin < bdrefini AND BDCODSER IS NOT NULL;
  /* ELIMINA IMPOSTOS INDEFINIDOS */
  DELETE FROM tempresaimposto IMP WHERE EXISTS (SELECT * FROM TIMPOSTO WHERE BDCODIMP = IMP.BDCODIMP AND bdperapuracao = 11);
  DELETE FROM vct_tributario_empresa IMP WHERE EXISTS (SELECT * FROM vct_base_obrigacao WHERE BDCODobrser = IMP.BDCODOBR AND bdperiodicidade = 11);
  DELETE FROM vct_tributario_empresa IMP WHERE EXISTS (SELECT * FROM vct_base_obrigacao WHERE BDCODobrser = IMP.bdcodser AND bdperiodicidade = 11);
END
^

/* Altering existing trigger... */
ALTER TRIGGER VCT_EMPRESA_TSOLICITATAREFA_AI0
AS
  DECLARE VARIABLE vcodser INTEGER;
  declare variable vnomusu varchar(40);
  declare variable vcodusu integer;
BEGIN
  IF (NEW.bdcodtipo = 501) THEN
  BEGIN
    IF ((NEW.bdcodimp IS NOT NULL) AND ((SELECT COUNT(bdcodusu) FROM vct_empresa_usuario_imp WHERE bdcodemp = NEW.bdcodemp AND bdcodimp = NEW.bdcodimp) > 0)) THEN
    BEGIN
      INSERT INTO VCT_EMPRESA_THISTORICOTAREFA (BDCODEMP, BDCODST, BDCODUSURESPONSAVEL, BDDATA, BDHORA, BDSTATUS, BDOBSERVACAO)
      SELECT imp.bdcodemp, NEW.bdcodst, imp.bdcodusu,
             CURRENT_DATE AS bddata, CURRENT_TIME AS bdhora, 6 AS bdstatus,
             'Lançado pelo processo automático de geração de tarefas para o usuário "'||usu.bdnomusu||'", com base nas configurações do cadastro de impostos.' AS bdobservacao
      FROM vct_empresa_usuario_imp imp
      INNER JOIN tusuario usu ON (usu.bdcodusu = imp.bdcodusu)
      WHERE imp.bdcodemp = NEW.bdcodemp
      AND imp.bdcodimp = NEW.bdcodimp;
    END
    ELSE
    IF ((NEW.bdcodobrigacao IS NOT NULL) AND ((SELECT COUNT(bdcodusu) FROM vct_empresa_usuario_obr WHERE bdcodemp = NEW.bdcodemp AND bdcodobrser = NEW.bdcodobrigacao)) > 0) THEN
    BEGIN
      INSERT INTO VCT_EMPRESA_THISTORICOTAREFA (BDCODEMP, BDCODST, BDCODUSURESPONSAVEL, BDDATA, BDHORA, BDSTATUS, BDOBSERVACAO)
      SELECT obr.bdcodemp, NEW.bdcodst, obr.bdcodusu,
             CURRENT_DATE AS bddata, CURRENT_TIME AS bdhora, 6 AS bdstatus,
             'Lançado pelo processo automático de geração de tarefas para o usuário "'||usu.bdnomusu||'", com base nas configurações do cadastro de obrigações.' AS bdobservacao
      FROM vct_empresa_usuario_obr obr
      INNER JOIN tusuario usu ON (usu.bdcodusu = obr.bdcodobrser)
      WHERE obr.bdcodemp = NEW.bdcodemp
      AND obr.bdcodobrser = NEW.bdcodimp;
    END
    ELSE
    IF ((NEW.bdcodservico IS NOT NULL) AND ((SELECT COUNT(bdcodusu) FROM vct_empresa_usuario_ser WHERE bdcodemp = NEW.bdcodemp AND bdcodobrser = NEW.bdcodservico)) > 0) THEN
    BEGIN
      INSERT INTO VCT_EMPRESA_THISTORICOTAREFA (BDCODEMP, BDCODST, BDCODUSURESPONSAVEL, BDDATA, BDHORA, BDSTATUS, BDOBSERVACAO)
      SELECT ser.bdcodemp, NEW.bdcodst, ser.bdcodusu,
             CURRENT_DATE AS bddata, CURRENT_TIME AS bdhora, 6 AS bdstatus,
             'Lançado pelo processo automático de geração de tarefas para o usuário "'||usu.bdnomusu||'", com base nas configurações do cadastro de serviços.' AS bdobservacao
      FROM vct_empresa_usuario_ser ser
      INNER JOIN tusuario usu ON (usu.bdcodusu = ser.bdcodobrser)
      WHERE ser.bdcodemp = NEW.bdcodemp
      AND ser.bdcodobrser = NEW.bdcodimp;
    END
    ELSE
    BEGIN
      INSERT INTO VCT_EMPRESA_THISTORICOTAREFA (BDCODEMP, BDCODST, BDCODUSURESPONSAVEL, BDDATA, BDHORA, BDSTATUS, BDOBSERVACAO)
      SELECT tar.bdcodemp, tar.bdcodst,
             CASE WHEN (tui.bdcodusu IS NOT NULL) THEN tui.bdcodusu
                  WHEN (tuo.bdcodusu IS NOT NULL) THEN tuo.bdcodusu
                  WHEN (tus.bdcodusu IS NOT NULL) THEN tus.bdcodusu
                  WHEN (tar.bdcodusu IS NOT NULL) THEN tar.bdcodusu
                  ELSE 1
             END AS bdcodusu,
             CURRENT_DATE AS bddata, CURRENT_TIME AS bdhora, 6 AS bdstatus,
             'Lançado pelo processo automático de geração de tarefas'||
             CASE WHEN (tui.bdcodusu IS NOT NULL) THEN ' para o usuário "'||tusu_ui.bdnomusu||'"'
                  WHEN (tuo.bdcodusu IS NOT NULL) THEN ' para o usuário "'||tusu_uo.bdnomusu||'"'
                  WHEN (tus.bdcodusu IS NOT NULL) THEN ' para o usuário "'||tusu_us.bdnomusu||'"'
                  WHEN (tar.bdcodusu IS NOT NULL) THEN ' para o usuário "'||tusu.bdnomusu||'"'
                  ELSE ''
             END
             ||', com base nas configurações do cadastro de '||
             CASE WHEN (tar.bdcodimp IS NOT NULL) THEN 'impostos.'
                  WHEN (tar.bdcodobrigacao IS NOT NULL) THEN 'obrigações.'
                  WHEN (tar.bdcodservico IS NOT NULL) THEN 'serviços.'
                  ELSE ''
             END AS bdobservacao
      FROM vct_empresa_tsolicitatarefa tar
      LEFT OUTER JOIN vct_base_usuarioimposto tui ON (tui.bdcodimp = tar.bdcodimp)
      LEFT OUTER JOIN tusuario tusu_ui ON (tusu_ui.bdcodusu = tui.bdcodusu)
      LEFT OUTER JOIN vct_base_usuarioobrigacao tuo ON (tuo.bdcodobrser = tar.bdcodobrigacao)
      LEFT OUTER JOIN tusuario tusu_uo ON (tusu_uo.bdcodusu = tuo.bdcodusu)
      LEFT OUTER JOIN vct_base_usuarioservico tus ON (tus.bdcodobrser = tar.bdcodservico)
      LEFT OUTER JOIN tusuario tusu_us ON (tusu_us.bdcodusu = tus.bdcodusu)

      LEFT OUTER JOIN tusuario tusu ON (tusu.bdcodusu = tar.bdcodusu)
      WHERE bdcodemp = NEW.bdcodemp
      AND bdcodst = NEW.bdcodst;
--      AND ((tui.bdcodusu IS NOT NULL) OR (tuo.bdcodusu IS NOT NULL) OR (tus.bdcodusu IS NOT NULL) OR (tar.bdcodusu IS NOT NULL));
    END
  END
  ELSE
  IF (NEW.bdcodtipo <> 501) THEN
  BEGIN
    select bdcodusu, bdnomusu from tusuario usu WHERE usu.bdcodusu = NEW.bdcodusu into :vcodusu, :vnomusu;
    if (vnomusu is not null) then
       vnomusu = 'Lançado manualmente para o usuário "'||:vnomusu||'".';
    else
       vnomusu = 'Lançado manualmente.';
    INSERT INTO VCT_EMPRESA_THISTORICOTAREFA (BDCODEMP, BDCODST, BDCODUSURESPONSAVEL, BDDATA, BDHORA, BDSTATUS, BDOBSERVACAO)
    values (NEW.bdcodemp, NEW.bdcodst, :vcodusu, CURRENT_DATE, CURRENT_TIME, 6, :vnomusu);
  END

  -- Serviços subordinadas
  IF (NEW.bdcodservico IS NOT NULL) THEN
     BEGIN
       FOR SELECT BDCODSERSUB FROM VCT_BASE_SERVICOSUBORDINADO WHERE BDCODOBRSER = NEW.bdcodservico INTO :vcodser DO
           BEGIN
             INSERT INTO vct_empresa_tsolicitatarefa
             (BDCODEMP, BDCODST, BDCODUSU, BDCODCOL, BDCODIMP, BDCODOBRIGACAO, BDCODSERVICO,
              BDCODTIPO, BDDATASOL, BDFATURAR, BDDATASOLICITADA, BDDATAPREVISAO, BDDATAFIM,
              BDSERIE, BDREFSOL, BDQUANTIDADE, BDVALORUNITARIO, BDVALORDESCONTO, BDTIPODESCONTO,
              BDPARCELAS, BDTIPOSOLICITACAO, BDCHAVEWEB, BDAGUARD, BDVENCIMENTO, BDENTREGA,
              BDALERTA, BDHORAPREVISAO, BDTEMPOESTIMADO, BDFILIAL)
             VALUES
             (NEW.BDCODEMP, NULL, NEW.BDCODUSU, NEW.BDCODCOL, NULL, NULL, :vcodser,
              NEW.BDCODTIPO, NEW.BDDATASOL, NEW.BDFATURAR, NEW.BDDATASOLICITADA, NEW.BDDATAPREVISAO, NEW.BDDATAFIM,
              NEW.BDSERIE, NEW.BDREFSOL, NEW.BDQUANTIDADE, NEW.BDVALORUNITARIO, NEW.BDVALORDESCONTO, NEW.BDTIPODESCONTO,
              NEW.BDPARCELAS, NEW.BDTIPOSOLICITACAO, NEW.BDCHAVEWEB, NEW.BDAGUARD, NEW.BDVENCIMENTO, NEW.BDENTREGA,
              NEW.BDALERTA, NEW.BDHORAPREVISAO, NEW.BDTEMPOESTIMADO, NEW.BDFILIAL);
           END
     END
END
^

/* Alter Procedure... */
/* empty dependent procedure body */
/* Clear: SP_VRH_VAL_RAIS_FF for: SP_VAL_RAIS_FF */
ALTER PROCEDURE SP_VRH_VAL_RAIS_FF(PREMP VARCHAR(32000),
PRCOL VARCHAR(32000),
PRANO INTEGER)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDREF INTEGER,
BDAVPREV NUMERIC(15,6),
BDFERIND NUMERIC(15,6),
BDGRATIF NUMERIC(15,6),
BDMULTA NUMERIC(15,6),
BDSINDICAL NUMERIC(15,6),
BDASSISTENCIAL NUMERIC(15,6),
BDCONFEDERATIVA NUMERIC(15,6),
BDBASEFN NUMERIC(15,6),
BDBASEA13 NUMERIC(15,6),
BDBASE13 NUMERIC(15,6),
BDBASEC13 NUMERIC(15,6),
BDORIGEM INTEGER,
BDMENSALIDADE NUMERIC(15,6))
 AS
 BEGIN SUSPEND; END
^

/* Alter (SP_VAL_RAIS_FF) */
ALTER PROCEDURE SP_VAL_RAIS_FF(PREMP INTEGER = 0,
PRCOL INTEGER = 0,
PRANO INTEGER = 0)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDREF INTEGER,
BDAVPREV NUMERIC(15,6),
BDFERIND NUMERIC(15,6),
BDGRATIF NUMERIC(15,6),
BDMULTA NUMERIC(15,6),
BDSINDICAL NUMERIC(15,6),
BDASSISTENCIAL NUMERIC(15,6),
BDCONFEDERATIVA NUMERIC(15,6),
BDBASEFN NUMERIC(15,6),
BDBASEA13 NUMERIC(15,6),
BDBASE13 NUMERIC(15,6),
BDBASEC13 NUMERIC(15,6),
BDORIGEM INTEGER,
BDMENSALIDADE NUMERIC(15,6))
 AS
declare variable WSQLAUX varchar(32000);
declare variable WSQL varchar(32000);
declare variable WREFRESC integer;
declare variable WREF integer;
declare variable WVALOR numeric(15,6);
declare variable WCLASS varchar(300);
declare variable WCLASS2 varchar(300);
declare variable WCLASS3 varchar(300);
declare variable WCLASS4 varchar(300);
declare variable WCLASS1 varchar(10);
declare variable WBASRAIS varchar(5);
declare variable WTIPOCOD integer;
declare variable WVALAUX numeric(15,6);
declare variable WJAN integer;
declare variable WFEV integer;
declare variable WMAR integer;
declare variable WABR integer;
declare variable WMAI integer;
declare variable WJUN integer;
declare variable WJUL integer;
declare variable WAGO integer;
declare variable WSET integer;
declare variable WOUT integer;
declare variable WNOV integer;
declare variable WDEZ integer;
declare variable WMES integer;
declare variable WEXISTE integer;
declare variable WRESCORIGINAL integer;
BEGIN
  /* Procedure Text */
--  wsql = 'select fol.bdreffn, fol.bdvalv, ver.bdclaver from VW_VRHF_CONS_CALC_FOLHA_VERBAS fol '||
--         ' left outer join vrh_base_tverbas ver on (ver.bdcodver = fol.bdcodver) '||


  bdorigem = 0;
  if (prano >= 2014) then
     begin
       wsql = 'select RAIS.BDREFFN, RAIS.BDVALV, VER.BDCLAVER, VERREF.BDRAISVER, VERREF.BDTIPOCODVER, RAIS.BDREFRESCISAO, RAIS.BDREFRESCORIGINAL '||
              'from (select FOL.BDREFFN, FOL.BDVALVFN BDVALV,  '||
                           '0 BDREFRESCISAO, 0 BDREFRESCORIGINAL, FOL.BDCODVER '||
                    'from (select 1 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFFN, BDREFFN BDREFFILTRO, BDREFVFN, BDREFAUXVFN, '||
                                 'BDVALVFN, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                 'cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBAFN COLCON '||
                          'union all '||
                          'select 1 BDTIPOFICHA, COLCON.BDCODEMP, COLCON.BDCODCOL, COLCON.BDCODVER, COLCON.BDREFTOMADOR, '||
                                 'COLCON.BDREFTOMADOR BDREFFILTRO, COLCON.BDREFVFT, COLCON.BDREFAUXVFT, COLCON.BDVALVFT, '||
                                 'CC.BDDATAENTRADA, CC.BDCODCENTRO, CC.BDCODTPCC '||
                          'from VRHF_EMP_TVERBATOMADOR COLCON '||
                          'join VRHF_EMP_TPREPFN CC on (COLCON.BDCODEMP = CC.BDCODEMP and COLCON.BDCODCOL = CC.BDCODCOL and COLCON.BDORDEM = CC.BDORDEM) '||
                          'union all '||
                          'select 2 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFFN, BDREFFN, BDREFVFC, BDREFAUXVFC, BDVALVFC, '||
                                 'cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBAFC FC '||
                          'union all '||
                          'select 2 BDTIPOFICHA, COLCON.BDCODEMP, COLCON.BDCODCOL, COLCON.BDCODVER, COLCON.BDREFTOMADOR, '||
                                 'COLCON.BDREFTOMADOR, COLCON.BDREFVFC, COLCON.BDREFAUXVFC, COLCON.BDVALVFC, CC.BDDATAENTRADA, '||
                                 'CC.BDCODCENTRO, CC.BDCODTPCC '||
                          'from VRHF_EMP_TVERBAFCTOMADOR COLCON '||
                          'join VRHF_EMP_TPREPFN CC on (COLCON.BDCODEMP = CC.BDCODEMP and COLCON.BDCODCOL = CC.BDCODCOL and COLCON.BDORDEM = CC.BDORDEM) '||
                          'union all '||
                          'select 3 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFA13, BDREFA13 BDREFFILTRO, BDREFVA13, BDREFAUXVA13, '||
                                 'BDVALVA13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                 'cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBAA13 FC '||
                          'union all '||
                          'select 4 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREF13, BDREF13 BDREFFILTRO, BDREFV13, BDREFAUXV13, '||
                                 'BDVALV13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                 'cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBA13 FC '||
                          'union all '||
                          'select 4 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFC13, BDREFC13 BDREFFILTRO, BDREFVC13, BDREFAUXVC13, '||
                                 'BDVALVC13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                 'cast(null as integer) BDCODTPCC '||
                          'from VRHF_EMP_TVERBAC13 FC) FOL '||
                    'where (FOL.BDCODEMP = '||premp||') and '||
                          '(substring(FOL.BDREFFILTRO from 1 for 4) = '''||prano||''') and '||
                          'FOL.BDCODCOL = '||prcol||' '||
                    'union all '||
                    'select FOL.BDREFFN, FOL.BDVALVFN BDVALV, '||
                           'case '||
                             'when PRES.BDINIPRES = 1 and RES.BDPROJECAOAVISO is not null then RES.BDREFPROJECAOAVISO '||
                             'else RES.BDREFRESCISAO '||
                           'end BDREFRESCISAO, '||
                           'RES.BDREFRESCISAO BDREFRESCORIGINAL, FOL.BDCODVER '||
                    'from VRH_EMP_TRESCISAO RES '||
                    'join(select 1 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFFN, BDREFFN BDREFFILTRO, BDREFVFN, BDREFAUXVFN, '||
                                'BDVALVFN, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBAFN COLCON '||
                         'union all '||
                         'select 1 BDTIPOFICHA, COLCON.BDCODEMP, COLCON.BDCODCOL, COLCON.BDCODVER, COLCON.BDREFTOMADOR, '||
                                'COLCON.BDREFTOMADOR BDREFFILTRO, COLCON.BDREFVFT, COLCON.BDREFAUXVFT, COLCON.BDVALVFT, '||
                                'CC.BDDATAENTRADA, CC.BDCODCENTRO, CC.BDCODTPCC '||
                         'from VRHF_EMP_TVERBATOMADOR COLCON '||
                         'join VRHF_EMP_TPREPFN CC on (COLCON.BDCODEMP = CC.BDCODEMP and COLCON.BDCODCOL = CC.BDCODCOL and COLCON.BDORDEM = CC.BDORDEM) '||
                         'union all '||
                         'select 2 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFFN, BDREFFN BDREFFILTRO, BDREFVFC, BDREFAUXVFC, '||
                                'BDVALVFC, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBAFC FC '||
                         'union all '||
                         'select 2 BDTIPOFICHA, COLCON.BDCODEMP, COLCON.BDCODCOL, COLCON.BDCODVER, COLCON.BDREFTOMADOR, '||
                                'COLCON.BDREFTOMADOR BDREFFILTRO, COLCON.BDREFVFC, COLCON.BDREFAUXVFC, COLCON.BDVALVFC, CC.BDDATAENTRADA, '||
                                'CC.BDCODCENTRO, CC.BDCODTPCC '||
                         'from VRHF_EMP_TVERBAFCTOMADOR COLCON '||
                         'join VRHF_EMP_TPREPFN CC on (COLCON.BDCODEMP = CC.BDCODEMP and COLCON.BDCODCOL = CC.BDCODCOL and COLCON.BDORDEM = CC.BDORDEM) '||
                         'union all '||
                         'select 3 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFA13, BDREFA13 BDREFFILTRO, BDREFVA13, BDREFAUXVA13, '||
                                'BDVALVA13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBAA13 FC '||
                         'union all '||
                         'select 4 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREF13, BDREF13 BDREFFILTRO, BDREFV13, BDREFAUXV13, '||
                                'BDVALV13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBA13 FC '||
                         'union all '||
                         'select 4 BDTIPOFICHA, BDCODEMP, BDCODCOL, BDCODVER, BDREFC13, BDREFC13 BDREFFILTRO, BDREFVC13, BDREFAUXVC13, '||
                                'BDVALVC13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, '||
                                'cast(null as integer) BDCODTPCC '||
                         'from VRHF_EMP_TVERBAC13 FC) FOL on (FOL.BDCODEMP = RES.BDCODEMP and FOL.BDCODCOL = RES.BDCODCOL and FOL.BDREFFN = RES.BDREFRESCISAO) '||
                    'join VRH_BASE_TPREPRESC PRES on (PRES.BDCODPRES = RES.BDCODPRES) '||
                    'where (FOL.BDCODEMP = '||premp||') and '||
                          '((substring(RES.BDREFRESCISAO from 1 for 4) = '''||prano||''') or (substring(RES.BDREFPROJECAOAVISO from 1 for 4) = '''||prano||''')) and '||
                          '(substring(RES.BDREFRESCISAO from 1 for 4) > '''||'2013'||''') and '||
                          'FOL.BDCODCOL = '||prcol||') RAIS '||

              'join VRH_BASE_TVERBAS VER on (VER.BDCODVER = RAIS.BDCODVER) '||
              'join VW_VRH_BASE_TVERBAS_REF VERREF on (VERREF.BDCODVER = RAIS.BDCODVER) '||
              ' where VERREF.BDTIPOCODVER <= 1 '||
              'order by RAIS.BDREFFN, RAIS.BDREFRESCISAO, RAIS.BDCODVER ';
     end
  else
     begin
       wsql = ' select fol.bdreffn,  fol.bdvalvfn BDVALV, ver.bdclaver,  '||
                 ' verref.bdraisver, verref.bdtipocodver, res.bdrefrescisao, 0 bdrefrescoriginal from '||
                 ' ( '||
     --            // Folha normal
                 ' select  BDCODEMP, BDCODCOL,  bdcodver,  bdreffn,  bdrefvfn,  bdrefauxvfn,  bdvalvfn, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC  from vrhf_emp_tverbafn COLCON '||
                 ' UNION all '||
     --              // Folha normal tomador
                 ' select COLCON.BDCODEMP, COLCON.BDCODCOL,  COLCON.BDCODVER,  COLCON.BDREFTOMADOR,  COLCON.BDREFVFT,  COLCON.BDREFAUXVFT,  COLCON.BDVALVFT, cc.bddataentrada, cc.bdcodcentro, cc.bdcodtpcc '||
                 ' from vrhf_emp_tverbatomador COLCON '||
                 ' join VRHF_EMP_TPREPFN cc on (COLCON.bdcodemp = cc.bdcodemp and  COLCON.bdcodcol = cc.bdcodcol and COLCON.bdordem = cc.bdordem) '||
     --              // Folha Complementar
                 ' union all '||
                 ' select  bdcodemp,  bdcodcol,  bdcodver,  bdreffn,  bdrefvfc,  bdrefauxvfc,  bdvalvfc, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC  from vrhf_emp_tverbafc fc '||
                 ' UNION all '||
     --              // Folha Complementar tomador
                 ' select COLCON.BDCODEMP, COLCON.BDCODCOL,  COLCON.BDCODVER,  COLCON.BDREFTOMADOR,  COLCON.BDREFVFc,  COLCON.BDREFAUXVFc,  COLCON.BDVALVFc, cc.bddataentrada, cc.bdcodcentro, cc.bdcodtpcc '||
                 ' from vrhf_emp_tverbafctomador COLCON '||
                 ' join VRHF_EMP_TPREPFN cc on (COLCON.bdcodemp = cc.bdcodemp and  COLCON.bdcodcol = cc.bdcodcol and COLCON.bdordem = cc.bdordem) '||
     --              // Adiantamento 13º salario
                 ' union all '||
                 ' select  bdcodemp,  bdcodcol,  bdcodver,  bdrefa13,  bdrefva13,  bdrefauxva13,  bdvalva13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC '||
                 ' from vrhf_emp_tverbaa13 fc '||
     --              // 13º salário
                 ' union all '||
                 ' select  bdcodemp,  bdcodcol,  bdcodver,  bdref13,  bdrefv13,  bdrefauxv13,  bdvalv13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC '||
                 ' from vrhf_emp_tverba13 fc '||
     --              // Complemento 13º salário
                 ' union all '||
                 ' select  bdcodemp,  bdcodcol,  bdcodver,  bdrefc13,  bdrefvc13,  bdrefauxvc13,  bdvalvc13, cast(null as date) BDDATAENT, cast(null as integer) BDCODCENTO, cast(null as integer) BDCODTPCC '||
                 ' from vrhf_emp_tverbac13 fc '||
                 ' ) fol '||
                 ' join vrh_base_tverbas ver on (ver.bdcodver = fol.bdcodver) '||
                 ' join vw_vrh_base_tverbas_ref verref on (verref.bdcodver = fol.bdcodver) '||
                 ' left outer join vrh_emp_trescisao res on (fol.bdcodemp = res.bdcodemp and fol.bdcodcol = res.bdcodcol) '||
     
     
              ' where verref.bdtipocodver <= 1 and (fol.bdcodemp = '||premp||')  and (cast (substring(fol.bdreffn from 1 for 4) as integer) = '||prano||') AND FOL.bdcodcol = '||prcol||
              ' order by bdreffn, fol.bdcodver ';
     end

  bdcodcol = prcol;
  bdcodemp = premp;
  bdref = 0;

  wjan = 0;
  wfev = 0;
  wmar = 0;
  wabr = 0;
  wmai = 0;
  wjun = 0;
  wjul = 0;
  wago = 0;
  wset = 0;
  wout = 0;
  wnov = 0;
  wdez = 0;

  WEXISTE = 0;

  FOR EXECUTE STATEMENT wsql INTO wref,wvalor,wclass,wbasrais,wtipocod,wrefresc,:wrescoriginal DO
    BEGIN
      WEXISTE = 1;

      IF (bdref <> wref) THEN
         BEGIN
           IF (bdref <> 0) THEN
              begin
                if (prano >= 2014) then
                   begin
                     if (substring(wrefresc from 1 for 4) = cast(prano as varchar(4)) and wref = wrescoriginal) then
                        bdref = wrefresc;
                   end

                SUSPEND;
              end

           if (prano >= 2014) then
              begin
                if (substring(wrefresc from 1 for 4) = cast(prano as varchar(4)) and wref = wrescoriginal) then
                   wmes = CAST(SUBSTRING(CAST(wrefresc AS VARCHAR(6)) FROM 5 FOR 6) AS INTEGER);
                else
                   wmes = CAST(SUBSTRING(CAST(wref AS VARCHAR(6)) FROM 5 FOR 6) AS INTEGER);
              end
           else
              wmes = CAST(SUBSTRING(CAST(wref AS VARCHAR(6)) FROM 5 FOR 6) AS INTEGER);

           IF (wmes = 1) THEN
              wjan = 1;
           IF (wmes = 2) THEN
              wfev = 1;
           IF (wmes = 3) THEN
              wmar = 1;
           IF (wmes = 4) THEN
              wabr = 1;
           IF (wmes = 5) THEN
              wmai = 1;
           IF (wmes = 6) THEN
              wjun = 1;
           IF (wmes = 7) THEN
              wjul = 1;
           IF (wmes = 8) THEN
              wago = 1;
           IF (wmes = 9) THEN
              wset = 1;
           IF (wmes = 10) THEN
              wout = 1;
           IF (wmes = 11) THEN
              wnov = 1;
           IF (wmes = 12) THEN
              wdez = 1;

           bdref = wref;

           BDMULTA = 0;

           if (prano >= 2014) then
              begin
                IF ((wref = wrescoriginal) and (substring(wrefresc from 1 for 4) = CAST(prano as VARCHAR(4)))) THEN
                   BEGIN
                     SELECT bdfgts40 FROM vrhf_emp_val_grrf(:premp,:prcol,:wrescoriginal,:wrescoriginal, 0) INTO bdmulta;
                   END
              end
           else
              begin
                IF (wref = wrefresc) THEN
                   BEGIN
                     SELECT bdfgts40 FROM vrhf_emp_val_grrf(:premp,:prcol,:wref,:wref, 0) INTO bdmulta;
                   END
              end

           /* Zera todos valores */
           BDAVPREV = 0;
           BDFERIND = 0;
           BDGRATIF = 0;
           BDSINDICAL = 0;
           BDASSISTENCIAL = 0;
           BDCONFEDERATIVA = 0;
           bdbase13 = 0;
           bdbasea13 = 0;
           bdbasec13 = 0;
           bdbasefn = 0;
           bdmensalidade = 0;
         END

      wclass1 = SUBSTRING(wclass FROM 1 FOR 1);
      wclass2 = SUBSTRING(wclass FROM 1 FOR 5);
      wclass3 = SUBSTRING(wclass FROM 1 FOR 7);
      wclass4 = SUBSTRING(wclass FROM 1 FOR 9);

      IF (wbasrais = 'TRUE') THEN
         BEGIN
           wvalaux = wvalor;
           IF (wtipocod = 1) THEN
              wvalaux = wvalaux * -1;


           IF (wclass1 = '4') THEN
              BEGIN
                if (prano >= 2014) then
                   begin
                     if (wrefresc = 0) then
                        bdbasea13 = bdbasea13 + wvalaux;
                   end
                else
                   bdbasea13 = bdbasea13 + wvalaux;
              END
--           // Verba de 13º
           ELSE IF ((wclass1 = '5') OR (wclass2 = '7.060') OR
                   (wclass2 = '7.050') OR (wclass2 = '7.061')) THEN
              BEGIN
                if (prano >= 2014) then
                   begin
                     if (wrefresc = 0) then
                        bdbase13 = bdbase13 + wvalaux;
                   end
                else
                   bdbase13 = bdbase13 + wvalaux;
              END
--           // Verba de Complemento 13º
           ELSE IF ((wclass1 = '6')) THEN
              BEGIN
                if (prano >= 2014) then
                   begin
                     if (wrefresc = 0) then
                        bdbasec13 = bdbasec13 + wvalaux;
                   end
                else
                   bdbasec13 = bdbasec13 + wvalaux;
              END
--           // Demais Verbas participa da folha normal
           ELSE
              BEGIN
                if ((wclass3 <> '7.001.3') and (wclass2 <> '7.010') and (wclass2 <> '7.030') and (wclass2 <> '7.032') and (wclass2 <> '7.012') and
                    (wclass2 <> '7.031') and (wclass2 <> '7.033') and (wclass2 <> '7.020') and (wclass2 <> '7.021')) then
                   begin
                     if (prano >= 2014) then
                        begin
                          if (wrefresc = 0) then
                             bdbasefn = bdbasefn + wvalaux;
                        end
                     else
                        bdbasefn = bdbasefn + wvalaux;
                   end
              END
         END

      -- aviso prévio indenizado // 7.001.3
      if (prano >= 2014) then
         begin
           IF (substring(wrefresc from 1 for 4) = cast(prano as varchar(4)) and wclass3 = '7.001.3') THEN
              bdavprev = bdavprev + wvalor;
         end
      else
         begin
           IF (wclass3 = '7.001.3') THEN
              bdavprev = bdavprev + wvalor;
         end


      -- férias indenizadas // 7.020 - 7.021

      if (prano >= 2014) then
         begin
           if (substring(wrefresc from 1 for 4) = cast(prano as varchar(4))) then
              begin
                IF (((wclass2 = '7.010') OR (wclass2 = '7.012')) OR
                   ((wclass2 = '7.030') OR (wclass2 = '7.031')) OR
                   ((wclass2 = '7.032') OR (wclass2 = '7.033'))) THEN
                   bdferind = bdferind + wvalor;
              end
         end
      else
         begin
           IF (((wclass2 = '7.010') OR (wclass2 = '7.012')) OR
              ((wclass2 = '7.030') OR (wclass2 = '7.031')) OR
              ((wclass2 = '7.032') OR (wclass2 = '7.033'))) THEN
              bdferind = bdferind + wvalor;
         end

      -- gratificações // 0.003.0.0
      if (prano >= 2014) then
         begin
           IF (wref = wrefresc) THEN
              BEGIN
                IF (wclass4 = '0.003.0.0') THEN
                   bdgratif = bdgratif + wvalor;
              END
         end
      else
         begin
           IF (wref = wrefresc) THEN
              BEGIN
                IF (wclass4 = '0.003.0.0') THEN
                   bdgratif = bdgratif + wvalor;
              END
         end

      -- multa rescisória // 7.001.1.0 é a multa de FGTS
--      if (wclass4 = '7.001.1.0') then
--         bdmulta = bdmulta + wvalor;

      -- contribuição sindical // 0.004.0.1 - 0.004.0.0
      if (prano >= 2014) then
         begin
           IF ((wclass4 = '0.004.0.1') OR (wclass4 = '0.004.0.0') OR (wclass4 = '0.710.0.1') OR (wclass4 = '0.710.0.0') OR
               (wclass4 = '4.047.0.1') OR (wclass4 = '4.047.0.0') OR (wclass4 = '5.070.0.1') OR (wclass4 = '5.070.0.0') OR
               (wclass4 = '6.070.0.1') OR (wclass4 = '6.070.0.0')) THEN
              begin
                if ((wrefresc = 0) or (wrefresc = wrescoriginal) or (substring(wrefresc from 1 for 4) <> cast(prano as varchar(4)))) then
                   bdsindical = bdsindical + wvalor;
              end
         end
      else
         begin
           IF ((wclass4 = '0.004.0.1') OR (wclass4 = '0.004.0.0') OR (wclass4 = '0.710.0.1') OR (wclass4 = '0.710.0.0') OR
               (wclass4 = '4.047.0.1') OR (wclass4 = '4.047.0.0') OR (wclass4 = '5.070.0.1') OR (wclass4 = '5.070.0.0') OR
               (wclass4 = '6.070.0.1') OR (wclass4 = '6.070.0.0')) THEN
              bdsindical = bdsindical + wvalor;
         end

      if (prano >= 2014) then
         begin
           -- contribuição assistencial // 0.004.0.3
           IF ((wclass4 = '0.004.0.3') OR (wclass4 = '0.710.0.3') OR (wclass4 = '4.047.0.3') OR (wclass4 = '5.070.0.3') OR (wclass4 = '6.070.0.3')) THEN
              begin
                if ((wrefresc = 0) or (wrefresc = wrescoriginal) or (substring(wrefresc from 1 for 4) <> cast(prano as varchar(4)))) then
                   bdassistencial = bdassistencial + wvalor;
              end
     
           -- contribuição confederativa // 0.004.0.2
           IF ((wclass4 = '0.004.0.2') OR (wclass4 = '0.710.0.2') OR (wclass4 = '4.047.0.2') OR (wclass4 = '5.070.0.2') OR (wclass4 = '6.070.0.2')) THEN
              begin
                if ((wrefresc = 0) or (wrefresc = wrescoriginal) or (substring(wrefresc from 1 for 4) <> cast(prano as varchar(4)))) then
                   bdconfederativa = bdconfederativa + wvalor;
              end
     
           -- Mensalidade sindical
           IF ((wclass4 = '0.004.0.0') OR (wclass4 = '0.710.0.0') OR (wclass4 = '4.047.0.0') OR (wclass4 = '5.070.0.0') OR (wclass4 = '6.070.0.0')) THEN
              begin
                if ((wrefresc = 0) or (wrefresc = wrescoriginal) or (substring(wrefresc from 1 for 4) <> cast(prano as varchar(4)))) then
                   bdmensalidade = bdmensalidade + wvalor;
              end
         end
      else
         begin
           -- contribuição assistencial // 0.004.0.3
           IF ((wclass4 = '0.004.0.3') OR (wclass4 = '0.710.0.3') OR (wclass4 = '4.047.0.3') OR (wclass4 = '5.070.0.3') OR (wclass4 = '6.070.0.3')) THEN
              bdassistencial = bdassistencial + wvalor;
     
           -- contribuição confederativa // 0.004.0.2
           IF ((wclass4 = '0.004.0.2') OR (wclass4 = '0.710.0.2') OR (wclass4 = '4.047.0.2') OR (wclass4 = '5.070.0.2') OR (wclass4 = '6.070.0.2')) THEN
              bdconfederativa = bdconfederativa + wvalor;
     
           -- Mensalidade sindical
           IF ((wclass4 = '0.004.0.0') OR (wclass4 = '0.710.0.0') OR (wclass4 = '4.047.0.0') OR (wclass4 = '5.070.0.0') OR (wclass4 = '6.070.0.0')) THEN
              bdmensalidade = bdmensalidade + wvalor;
         end
    END

  IF (wexiste = 1) THEN
     SUSPEND;

  bdmensalidade = 0;
  -- busca valores da ficha financeira para meses que não tem informações
  wsqlaux = 'select a.bdreffrais,coalesce(a.bdrendfrais,0), coalesce(a.bda13frais,0), coalesce(a.bd13frais,0), coalesce(a.bdferiasrescisaofrais,0), '||
              ' coalesce(a.bdmultarescfrais,0), coalesce(a.bdcontrsindfrais,0), coalesce(a.bdcontassistfrais,0), '||
              ' coalesce(a.bdcontconffrais,0), coalesce(a.bdavisopreviofrais,0), coalesce(a.bdc13frais,0), '||
              ' coalesce(a.bdgratificacaofrais,0), coalesce(BDCONTAASS1FRAIS,0) from vrhf_emp_tficharais a '||
              ' where a.bdcodemp = '||:premp||' and a.bdcodcol = '||:prcol||
              ' and a.bdreffrais = '||:prano;
  bdorigem = 1;
  IF (wjan = 0) THEN
     BEGIN
       wsql = wsqlaux||'01';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wfev = 0) THEN
     BEGIN
       wsql = wsqlaux||'02';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wmar = 0) THEN
     BEGIN
       wsql = wsqlaux||'03';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wabr = 0) THEN
     BEGIN
       wsql = wsqlaux||'04';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wmai = 0) THEN
     BEGIN
       wsql = wsqlaux||'05';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wjun = 0) THEN
     BEGIN
       wsql = wsqlaux||'06';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wjul = 0) THEN
     BEGIN
       wsql = wsqlaux||'07';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wago = 0) THEN
     BEGIN
       wsql = wsqlaux||'08';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wset = 0) THEN
     BEGIN
       wsql = wsqlaux||'09';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wout = 0) THEN
     BEGIN
       wsql = wsqlaux||'10';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wnov = 0) THEN
     BEGIN
       wsql = wsqlaux||'11';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END

  IF (wdez = 0) THEN
     BEGIN
       wsql = wsqlaux||'12';

       FOR EXECUTE STATEMENT wsql INTO bdref,bdbasefn,bdbasea13,bdbase13, bdferind, bdmulta, bdsindical, bdassistencial, bdconfederativa, bdavprev,
            bdbasec13, bdgratif, bdmensalidade DO
       SUSPEND;
     END
END
^

/* Alter (SP_VRH_VAL_RAIS_FF) */
ALTER PROCEDURE SP_VRH_VAL_RAIS_FF(PREMP VARCHAR(32000),
PRCOL VARCHAR(32000),
PRANO INTEGER)
 RETURNS(BDCODEMP INTEGER,
BDCODCOL INTEGER,
BDREF INTEGER,
BDAVPREV NUMERIC(15,6),
BDFERIND NUMERIC(15,6),
BDGRATIF NUMERIC(15,6),
BDMULTA NUMERIC(15,6),
BDSINDICAL NUMERIC(15,6),
BDASSISTENCIAL NUMERIC(15,6),
BDCONFEDERATIVA NUMERIC(15,6),
BDBASEFN NUMERIC(15,6),
BDBASEA13 NUMERIC(15,6),
BDBASE13 NUMERIC(15,6),
BDBASEC13 NUMERIC(15,6),
BDORIGEM INTEGER,
BDMENSALIDADE NUMERIC(15,6))
 AS
DECLARE VARIABLE WSQLCOL VARCHAR(32000);
DECLARE VARIABLE WSQLFF VARCHAR(32000);
DECLARE VARIABLE WCOL INTEGER;
DECLARE VARIABLE WEMP INTEGER;
BEGIN
  /* Procedure Text */
--  suspend;
--  wsqlcol = 'select bdcodemp,bdcodcol from sp_somente_ativos('||''''||'01/01/'||prano||''''||','||''''||'12/31/'||prano||''''||')';
  wsqlcol = 'select col.bdcodemp,col.bdcodcol from vrh_emp_tcolcon col '||
            ' join sp_somente_ativos('||''''||'01/01/'||prano||''''||','||''''||'12/31/'||prano||''''||') filtro on (filtro.bdcodemp = col.bdcodemp and filtro.bdcodcol = col.bdcodcol)';

  IF ((premp <> '') AND (premp IS NOT NULL)) THEN
     BEGIN
       wsqlcol = wsqlcol||' where '||premp;

       IF ((prcol <> '') AND (prcol IS NOT NULL)) THEN
          wsqlcol = wsqlcol||' and '||prcol;
     END

  FOR EXECUTE STATEMENT wsqlcol INTO wemp,wcol DO
    BEGIN
      FOR SELECT BDCODEMP, BDCODCOL, BDREF, BDAVPREV, BDFERIND, BDGRATIF, BDMULTA,
                 BDSINDICAL, BDASSISTENCIAL, BDCONFEDERATIVA, BDBASEFN, BDBASEA13,
                 BDBASE13, BDBASEC13, BDORIGEM, bdmensalidade FROM sp_val_rais_ff(:wemp,:wcol,:prano)
          INTO BDCODEMP, BDCODCOL, BDREF, BDAVPREV, BDFERIND, BDGRATIF, BDMULTA,
                 BDSINDICAL, BDASSISTENCIAL, BDCONFEDERATIVA, BDBASEFN, BDBASEA13,
                 BDBASE13, BDBASEC13, BDORIGEM, bdmensalidade DO
          SUSPEND;
    END

END
^

SET TERM ; ^

