CREATE OR ALTER VIEW TBCENTRO_DE_CUSTO(
    CODIGO,
    DESCRICAO,
    STATUS,
    USUARIO_CODIGO,
    CONTA_CONTABIL,
    HISTORICO_CONTABIL,
    GP_ID,
    ESTABELECIMENTO_ID,
    PERFIL
)
AS
select
    iif(char_length(CODIGO) > 2, substring(CODIGO from 1 for 2)||fn_lpad(substring(CODIGO from 3 for 100),3,'0'), CODIGO) CODIGO,
    DESCRICAO,
    STATUS,
    USUARIO_CODIGO,
    CONTA_CONTABIL,
    HISTORICO_CONTABIL,
    GP_ID,
    ESTABELECIMENTO_ID,
    PERFIL
from(
    select
        substring(replace(s.codigo,'.','') from 2 for 100) as CODIGO,
        substring( s.nome from 1 for 40) as DESCRICAO,
        1 as STATUS,
        186 as USUARIO_CODIGO,
        '' as CONTA_CONTABIL,
        '' as HISTORICO_CONTABIL,
        0 as GP_ID,
        1 as ESTABELECIMENTO_ID,
        '' PERFIL
    from CENTRO_CUSTOS s
    where s.nome <> 'INDEFINIDO'
)
;

