Jump to content
Sign in to follow this  
João Lemos

Cubo Orçado X Realizado - Informações Contabéis

Recommended Posts

Pessoal boa tarde,

Estou disponibilizando este cubo de orçamento onde acho que vai atender(ajudar) várias pessoas, pois ele unifica informações de "N" módulos, como : Nuclues, Fluxus, Labore e Saldus.

É necessário realizar algumas configurações dentro das naturezas orçamentárias.

Em anexo vai algumas informações uteis e o cubo.

Att,

João

CUBO.rar

Share this post


Link to post
Share on other sites

Eusébio,

Creio que deva ter dado problema, pq tem um ponto no meio do nome do arquivo, e isso faz com que o sistema (windows) pense que a extensão é a partir dali. Renomeie tirando aquele ponto, que deverá dar certo.

A extensão correta deve ser .cub ou .totvscub ( creio que seja essa a nova do Cubo.Net ).

Abraço.

Share this post


Link to post
Share on other sites

Jair,

Obrigado pela ajuda, mas o erro persiste.

Talvez seja minha versão, ainda estou na 11.40

Eusebio,

Boa tarde!

Eu não estou mais na empresa onde crie o cubo mais vou tentar pegar o código com o colega que está la agora e postar aqui.

Share this post


Link to post
Share on other sites

Eusebio,

Boa tarde!

Eu não estou mais na empresa onde crie o cubo mais vou tentar pegar o código com o colega que está la agora e postar aqui.

Valeu João, obrigado pela ajuda.

abs

Share this post


Link to post
Share on other sites

Eusebio,

Boa noite!

Segue o Script atualizado.( Para o cubo funcionar corretemente é necessário realizar algumas parametrizações internas)

SELECT
TORCAMENTO.CODCOLIGADA AS COLIGADA,
COALESCE(TTBORCAMENTO.CAMPOLIVRE, '') AS CAMPOLIVRE,
DATEPART (MONTH,TITMPERIODOORCAMENTO.DATAFIM) AS MES,
DATEPART (YEAR,TITMPERIODOORCAMENTO.DATAFIM) AS ANO,
TORCAMENTO.CODCCUSTO + ' - '+ GCCUSTO.NOME AS CENTRO_CUSTO,
TORCAMENTO.CODTBORCAMENTO + ' - ' + TTBORCAMENTO.DESCRICAO AS CLASSIFICACAO,
0 AS REF,
TITMORCAMENTO.VALORORCADO AS ORCADO,
0 AS REALIZADO,
'O' AS ORIGEM
FROM
TORCAMENTO(NOLOCK)
INNER JOIN TITMORCAMENTO(NOLOCK) ON (TORCAMENTO.CODCOLIGADA=TITMORCAMENTO.CODCOLIGADA AND TORCAMENTO.IDORCAMENTO=TITMORCAMENTO.IDORCAMENTO AND TORCAMENTO.IDPERIODO=TITMORCAMENTO.IDPERIODO)
INNER JOIN TITMPERIODOORCAMENTO(NOLOCK) ON (TITMORCAMENTO.CODCOLIGADA=TITMPERIODOORCAMENTO.CODCOLIGADA AND TITMORCAMENTO.IDPERIODO=TITMPERIODOORCAMENTO.IDPERIODO AND TITMORCAMENTO.IDITMPERIODO=TITMPERIODOORCAMENTO.IDITMPERIODO)
INNER JOIN GCCUSTO(NOLOCK) ON (TORCAMENTO.CODCOLIGADA=GCCUSTO.CODCOLIGADA AND TORCAMENTO.CODCCUSTO=GCCUSTO.CODCCUSTO)
INNER JOIN TTBORCAMENTO(NOLOCK) ON (TORCAMENTO.CODCOLTBORCAMENTO=TTBORCAMENTO.CODCOLIGADA AND TORCAMENTO.CODTBORCAMENTO=TTBORCAMENTO.CODTBORCAMENTO)
WHERE
TTBORCAMENTO.CODTBORCAMENTO NOT IN ('2.11.009','2.07.008','2.07.009','2.07.001','2.06.008','2.01.028','2.10.026','2.11.900','2.10.003','2.08.026','2.03.014','2.09.035','2.11.008','2.11.901','1.05.015','1.05.007','2.10.001','2.07.006','2.10.009','2.09.031','2.10.019','2.11.900','2.11.901','2.10.003','2.08.026','2.09.035','2.11.008','2.11.901','1.05.014','2.07.006','2.10.001','2.10.004','2.10.007','1.05.014','1.05.007','2.09.035')

UNION ALL

SELECT
FLAN.CODCOLIGADA AS COLIGADA,
COALESCE(TTBORCAMENTO.CAMPOLIVRE, '') AS CAMPOLIVRE,
DATEPART (MONTH,COALESCE(M.DATAEMISSAO, FLAN.DATABAIXA)) AS MES,
DATEPART (YEAR,COALESCE(M.DATAEMISSAO, FLAN.DATABAIXA)) AS ANO,
FLANRATCCU.CODCCUSTO+ ' - '+ GCCUSTO.NOME AS CENTRO_CUSTO,
FLANRATCCU.CODNATFINANCEIRA+ ' - ' + TTBORCAMENTO.DESCRICAO AS CLASIFICACAO,
FLAN.IDLAN AS REF,
0 AS ORCADO,
FLANRATCCU.VALOR * (CASE PAGREC WHEN 1 THEN -1 ELSE 1 END) AS REALIZADO,
'F' AS ORIGEM
FROM
FLAN(NOLOCK)
INNER JOIN FLANRATCCU(NOLOCK) ON (FLAN.CODCOLIGADA=FLANRATCCU.CODCOLIGADA AND FLAN.IDLAN=FLANRATCCU.IDLAN)
INNER JOIN GCCUSTO(NOLOCK) ON (FLANRATCCU.CODCOLIGADA=GCCUSTO.CODCOLIGADA AND FLANRATCCU.CODCCUSTO=GCCUSTO.CODCCUSTO)
INNER JOIN TTBORCAMENTO(NOLOCK) ON (FLANRATCCU.CODCOLNATFINANCEIRA=TTBORCAMENTO.CODCOLIGADA AND FLANRATCCU.CODNATFINANCEIRA=TTBORCAMENTO.CODTBORCAMENTO)
LEFT OUTER JOIN TMOV M(NOLOCK) ON (FLAN.CODCOLIGADA = M.CODCOLIGADA AND FLAN.IDMOV = M.IDMOV)
WHERE
FLAN.STATUSLAN = 1 AND
FLAN.NFOUDUP <> 1 AND
FLAN.CODAPLICACAO = 'F' AND
FLAN.CODTDO NOT IN ('04', '05', '06', '07', '08', '10', '11', '12', '14', '22', '34', '37', '38', '39', '40', '41') AND
FLANRATCCU.CODNATFINANCEIRA NOT IN ('2.11.009','2.07.008','2.07.009','2.07.001','2.06.008','2.01.028','2.10.026','2.11.900','2.10.003','2.08.026','2.03.014','2.09.035','2.11.008','2.11.901','1.05.015','1.05.007','2.10.001','2.07.006','2.10.009','2.09.031','2.10.019','2.11.900','2.11.901','2.10.003','2.08.026','2.09.035','2.11.008','2.11.901','1.05.014','2.07.006','2.10.001','2.10.004','2.10.007','1.05.014','1.05.007','2.09.035')

UNION ALL

SELECT
FXCX.CODCOLIGADA AS COLIGADA,
COALESCE(TTBORCAMENTO.CAMPOLIVRE, '') AS CAMPOLIVRE,
DATEPART (MONTH,FXCX.DATACOMPENSACAO) AS MES,
DATEPART (YEAR,FXCX.DATACOMPENSACAO) AS ANO,
FXCX.CODCCUSTO+ ' - '+ GCCUSTO.NOME AS CENTRO_CUSTO,
FXCX.CODNATFINANCEIRA+ ' - ' + TTBORCAMENTO.DESCRICAO AS CLASIFICACAO,
FXCX.IDXCX AS REF,
0 AS ORCADO,
FXCX.VALOR * (CASE TIPO WHEN 1 THEN -1 ELSE 1 END) AS REALIZADO,
'FX' AS ORIGEM
FROM
FXCX(NOLOCK)
INNER JOIN GCCUSTO(NOLOCK) ON (FXCX.CODCOLIGADA=GCCUSTO.CODCOLIGADA AND FXCX.CODCCUSTO=GCCUSTO.CODCCUSTO)
INNER JOIN TTBORCAMENTO(NOLOCK) ON (FXCX.CODCOLNATFINANCEIRA=TTBORCAMENTO.CODCOLIGADA AND FXCX.CODNATFINANCEIRA=TTBORCAMENTO.CODTBORCAMENTO)

WHERE

FXCX.COMPENSADO = 1 AND
FXCX.CONTABIL = 1 AND
FXCX.CODNATFINANCEIRA NOT IN ('2.11.009','2.07.008','2.07.009','2.07.001','2.06.008','2.01.028','2.10.026','2.11.900','2.10.003','2.08.026','2.03.014','2.09.035','2.11.008','2.11.901','1.05.015','1.05.007','2.10.001','2.07.006','2.10.009','2.09.031','2.10.019','2.11.900','2.11.901','2.10.003','2.08.026','2.09.035','2.11.008','2.11.901','1.05.014','2.07.006','2.10.001','2.10.004','2.10.007','1.05.014','1.05.007','2.09.035')
UNION ALL

SELECT
R.CODCOLIGADA AS COLIGADA,
COALESCE(O.CAMPOLIVRE, '') AS CAMPOLIVRE,
DATEPART (MONTH,COALESCE(M.DATACONTABILIZACAO, M.DATAEMISSAO, I.DATAORCAMENTO)) AS MES,
DATEPART (YEAR,COALESCE(M.DATACONTABILIZACAO, M.DATAEMISSAO, I.DATAORCAMENTO)) AS ANO,
R.CODCCUSTO + ' - '+ CC.NOME AS CENTRO_CUSTO,
I.CODTBORCAMENTO + ' - ' + O.DESCRICAO AS CLASIFICACAO,
R.IDMOV AS REF,
0 AS ORCADO,
R.VALOR AS REALIZADO,
'N' AS ORIGEM
FROM
TITMMOVRATCCU R(NOLOCK)
INNER JOIN GCCUSTO CC(NOLOCK) ON (R.CODCOLIGADA = CC.CODCOLIGADA AND R.CODCCUSTO = CC.CODCCUSTO)
INNER JOIN TITMMOV I(NOLOCK) ON (R.CODCOLIGADA = I.CODCOLIGADA AND R.IDMOV = I.IDMOV AND R.NSEQITMMOV = I.NSEQITMMOV)
INNER JOIN TTBORCAMENTO O(NOLOCK) ON (I.CODCOLTBORCAMENTO=O.CODCOLIGADA AND I.CODTBORCAMENTO=O.CODTBORCAMENTO)
INNER JOIN TMOV M(NOLOCK) ON (R.CODCOLIGADA = M.CODCOLIGADA AND R.IDMOV = M.IDMOV)
WHERE
M.CODEVENTO IS NOT NULL AND
M.STATUS NOT IN ('C') AND
I.CODTBORCAMENTO NOT IN ('2.11.009','2.07.008','2.07.009','2.07.001','2.06.008','2.01.028','2.10.026','2.11.900','2.10.003','2.08.026','2.03.014','2.09.035','2.11.008','2.11.901','1.05.015','1.05.007','2.10.001','2.07.006','2.10.009','2.09.031','2.10.019','2.11.900','2.11.901','2.10.003','2.08.026','2.09.035','2.11.008','2.11.901','1.05.014','2.07.006','2.10.001','2.10.004','2.10.007','1.05.014','1.05.007','2.09.035')

UNION ALL

SELECT
P.CODCOLIGADA AS COLIGADA,
COALESCE(O.CAMPOLIVRE, '') AS CAMPOLIVRE,
DATEPART (MONTH,P.DATA) AS MES,
DATEPART (YEAR,P.DATA) AS ANO,
P.CODCCUSTO+ ' - '+ C.NOME AS CENTRO_CUSTO,
OC.CODTBORCAMENTO + ' - ' + O.DESCRICAO AS CLASIFICACAO,
P.IDPARTIDA AS REF,
0 AS ORCADO,
P.VALOR * ( CASE CT.NATUREZA
WHEN 0 THEN -1
WHEN 1 THEN 1
ELSE 1 END) AS REALIZADO,
'D' AS ORIGEM
FROM
CPARTIDA P(NOLOCK)
INNER JOIN GCCUSTO C(NOLOCK) ON (P.CODCOLIGADA=C.CODCOLIGADA AND P.CODCCUSTO=C.CODCCUSTO)
INNER JOIN ZMDVINCULOORCAMENTARIO OC(NOLOCK) ON (P.CODCOLDEBITO=OC.CODCOLCONTA AND P.DEBITO=OC.CODCONTA)
INNER JOIN TTBORCAMENTO O(NOLOCK) ON (OC.CODCOLTBORCAMENTO=O.CODCOLIGADA AND OC.CODTBORCAMENTO=O.CODTBORCAMENTO)
INNER JOIN CCONTA CT(NOLOCK) ON (OC.CODCOLCONTA=CT.CODCOLIGADA AND OC.CODCONTA=CT.CODCONTA)
WHERE
P.CODLOTE = 0 AND
P.INTEGRAAPLICACAO NOT IN ('T', 'F')AND
OC.CODTBORCAMENTO NOT IN ('2.11.009','2.07.008','2.07.009','2.07.001','2.06.008','2.01.028','2.10.026','2.11.900','2.10.003','2.08.026','2.03.014','2.09.035','2.11.008','2.11.901','1.05.015','1.05.007','2.10.001','2.07.006','2.10.009','2.09.031','2.10.019','2.11.900','2.11.901','2.10.003','2.08.026','2.09.035','2.11.008','2.11.901','1.05.014','2.07.006','2.10.001','2.10.004','2.10.007','1.05.014','1.05.007','2.09.035')

UNION ALL

SELECT
P.CODCOLIGADA AS COLIGADA,
COALESCE(O.CAMPOLIVRE, '') AS CAMPOLIVRE,
DATEPART (MONTH,P.DATA) AS MES,
DATEPART (YEAR,P.DATA) AS ANO,
P.CODCCUSTO+ ' - '+ C.NOME AS CENTRO_CUSTO,
OC.CODTBORCAMENTO + ' - ' + O.DESCRICAO AS CLASIFICACAO,
P.IDPARTIDA AS REF,
0 AS ORCADO,
P.VALOR * ( CASE CT.NATUREZA
WHEN 0 THEN 1
WHEN 1 THEN -1
ELSE 1 END) AS REALIZADO,
'C' AS ORIGEM
FROM
CPARTIDA P(NOLOCK)
INNER JOIN GCCUSTO C(NOLOCK) ON (P.CODCOLIGADA=C.CODCOLIGADA AND P.CODCCUSTO=C.CODCCUSTO)
INNER JOIN ZMDVINCULOORCAMENTARIO OC(NOLOCK) ON (P.CODCOLCREDITO=OC.CODCOLCONTA AND P.CREDITO=OC.CODCONTA)
INNER JOIN TTBORCAMENTO O(NOLOCK) ON (OC.CODCOLTBORCAMENTO=O.CODCOLIGADA AND OC.CODTBORCAMENTO=O.CODTBORCAMENTO)
INNER JOIN CCONTA CT(NOLOCK) ON (OC.CODCOLCONTA=CT.CODCOLIGADA AND OC.CODCONTA=CT.CODCONTA)
WHERE
P.CODLOTE = 0 AND
P.INTEGRAAPLICACAO NOT IN ('T', 'F')

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.