CSALDOCONTABILVIEW

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CODCOLIGADA

DCODCOLIGADA

null

DATA

datetime

16,3

null

CONTA

DCODCONTA

√

null

VLRDEBITO

numeric

40,4

√

null

VLRCREDITO

numeric

40,4

√

null

VALOR

numeric

40,4

√

null

MOEDA

varchar

250

√

null

CODCCUSTO

varchar

√

null

CODFILIAL

smallint

null

NATUREZACONTA

varchar

null

View Definition

CREATE VIEW CSALDOCONTABILVIEW
AS
(
	(
		SELECT
			CPARTIDA.CODCOLIGADA,
			CPARTIDA.DATA,
			CPARTIDA.DEBITO					AS CONTA,
			SUM(CASE
			WHEN( CCONTA.NATSPED = '04'
				AND ISNULL(CHISTP.HISTFECHA, 0) = 1 ) THEN 0
			ELSE ISNULL(CPARTIDAMULTIMOEDA.VALOR, 0)
			END)							AS VLRDEBITO,
			0								AS VLRCREDITO,
			0								AS VALOR
			,CPARTIDAMULTIMOEDA.MOEDA
			,CPARTIDA.CODCCUSTO
			,CPARTIDA.CODFILIAL
			, (CASE
				WHEN (CCONTA.NATUREZA = 0)
					THEN 'Credora'
				ELSE 'Devedora'
				END)							AS NATUREZACONTA
		FROM CPARTIDA (NOLOCK)
		JOIN CCONTA (NOLOCK) ON CCONTA.CODCOLIGADA = CPARTIDA.CODCOLDEBITO
							AND CCONTA.CODCONTA = CPARTIDA.DEBITO
		LEFT JOIN CHISTP(NOLOCK) ON CHISTP.CODCOLIGADA = CPARTIDA.CODCOLIGADA
								AND CHISTP.CODHISTP = CPARTIDA.CODHISTP
		INNER JOIN CPARTIDAMULTIMOEDA (NOLOCK) ON CPARTIDAMULTIMOEDA.CODCOLIGADA = CPARTIDA.CODCOLIGADA
											  AND CPARTIDAMULTIMOEDA.IDPARTIDA = CPARTIDA.IDPARTIDA
		WHERE CPARTIDA.DEBITO IS NOT NULL
		  AND CPARTIDA.CODLOTE = 0
		GROUP BY CPARTIDA.CODCOLIGADA, CPARTIDA.DATA, CPARTIDA.DEBITO, CPARTIDAMULTIMOEDA.MOEDA,
		CPARTIDA.VALOR, CPARTIDA.CODCCUSTO, CPARTIDA.CODFILIAL, CCONTA.NATUREZA
	)
	UNION ALL
	(
		SELECT CPARTIDA.CODCOLIGADA,
				CPARTIDA.DATA,
				CPARTIDA.CREDITO AS CONTA,
				0                AS VLRDEBITO,
				SUM(CASE
						WHEN ( CCONTA.NATSPED = '04'
							AND ISNULL(CHISTP.HISTFECHA, 0) = 1 ) THEN 0
						ELSE ( -1 * ISNULL(CPARTIDAMULTIMOEDA.VALOR, 0) )
					END)         AS VLRCREDITO,
				0                AS VALOR
				,CPARTIDAMULTIMOEDA.MOEDA
				,CPARTIDA.CODCCUSTO
				,CPARTIDA.CODFILIAL
				, (CASE
					WHEN (CCONTA.NATUREZA = 0)
					 THEN 'Credora'
					ELSE 'Devedora'
				   END)                                 AS NATUREZACONTA
		FROM CPARTIDA (NOLOCK)
			JOIN CCONTA (NOLOCK)
					ON CCONTA.CODCOLIGADA = CPARTIDA.CODCOLCREDITO
					AND CCONTA.CODCONTA = CPARTIDA.CREDITO
			LEFT JOIN CHISTP (NOLOCK)
					ON CHISTP.CODCOLIGADA = CPARTIDA.CODCOLIGADA
							AND CHISTP.CODHISTP = CPARTIDA.CODHISTP
			INNER JOIN CPARTIDAMULTIMOEDA (NOLOCK)
					ON CPARTIDAMULTIMOEDA.CODCOLIGADA = CPARTIDA.CODCOLIGADA
							AND CPARTIDAMULTIMOEDA.IDPARTIDA = CPARTIDA.IDPARTIDA
		WHERE CPARTIDA.CREDITO IS NOT NULL
				AND CPARTIDA.CODLOTE = 0
		GROUP BY CPARTIDA.CODCOLIGADA, CPARTIDA.CREDITO, CPARTIDA.DATA, CPARTIDAMULTIMOEDA.MOEDA,
		CPARTIDA.VALOR, CPARTIDA.CODCCUSTO, CPARTIDA.CODFILIAL, CCONTA.NATUREZA
	)
	UNION ALL
	(
		SELECT CPARTIDA.CODCOLIGADA,
				CPARTIDA.DATA,
				CPARTIDA.DEBITO                          AS CONTA,
				0                                        AS VLRDEBITO,
				0                                        AS VLRCREDITO,
				SUM(ISNULL(CPARTIDAMULTIMOEDA.VALOR, 0)) AS VALOR
				,CPARTIDAMULTIMOEDA.MOEDA
				,CPARTIDA.CODCCUSTO
				,CPARTIDA.CODFILIAL
				, (CASE
					WHEN (CCONTA.NATUREZA = 0)
					 THEN 'Credora'
					ELSE 'Devedora'
				   END)									 AS NATUREZACONTA
		FROM CPARTIDA (NOLOCK)
			JOIN CCONTA (NOLOCK)
					ON CCONTA.CODCOLIGADA = CPARTIDA.CODCOLDEBITO
					AND CCONTA.CODCONTA = CPARTIDA.DEBITO
			INNER JOIN CPARTIDAMULTIMOEDA (NOLOCK)
						ON CPARTIDAMULTIMOEDA.CODCOLIGADA = CPARTIDA.CODCOLIGADA
							AND CPARTIDAMULTIMOEDA.IDPARTIDA = CPARTIDA.IDPARTIDA
		WHERE CPARTIDA.DEBITO IS NOT NULL
				AND CPARTIDA.CODLOTE = 0
		GROUP BY CPARTIDA.CODCOLIGADA,CPARTIDA.DEBITO, CPARTIDA.DATA, CPARTIDAMULTIMOEDA.MOEDA,
		CPARTIDA.VALOR, CPARTIDA.CODCCUSTO, CPARTIDA.CODFILIAL, CCONTA.NATUREZA
	)
	UNION ALL
	(
		SELECT CPARTIDA.CODCOLIGADA,
				CPARTIDA.DATA,
				CPARTIDA.CREDITO                              AS CONTA,
				0                                             AS VLRDEBITO,
				0                                             AS VLRCREDITO,
				SUM(-1 * ISNULL(CPARTIDAMULTIMOEDA.VALOR, 0)) AS VALOR
				,CPARTIDAMULTIMOEDA.MOEDA
				,CPARTIDA.CODCCUSTO
				,CPARTIDA.CODFILIAL
				, (CASE
					WHEN (CCONTA.NATUREZA = 0)
					 THEN 'Credora'
					ELSE 'Devedora'
				   END)										  AS NATUREZACONTA
		FROM   CPARTIDA (NOLOCK)
				JOIN CCONTA (NOLOCK)
					ON CCONTA.CODCOLIGADA = CPARTIDA.CODCOLCREDITO
					AND CCONTA.CODCONTA = CPARTIDA.CREDITO
				INNER JOIN CPARTIDAMULTIMOEDA (NOLOCK)
						ON CPARTIDAMULTIMOEDA.CODCOLIGADA = CPARTIDA.CODCOLIGADA
							AND CPARTIDAMULTIMOEDA.IDPARTIDA = CPARTIDA.IDPARTIDA
		WHERE CPARTIDA.CREDITO IS NOT NULL
				AND CPARTIDA.CODLOTE = 0
		GROUP BY CPARTIDA.CODCOLIGADA, CPARTIDA.CREDITO,CPARTIDA.DATA, CPARTIDAMULTIMOEDA.MOEDA,
		CPARTIDA.VALOR, CPARTIDA.CODCCUSTO, CPARTIDA.CODFILIAL, CCONTA.NATUREZA
	)
	UNION ALL
	(
		SELECT CSALDOEXERCICIO.CODCOLIGADA,
				CEXERCICIO.DATAFINAL							 AS DATA,
				CSALDOEXERCICIO.CODCONTA                         AS CONTA,
				0                                                AS VLRDEBITO,
				0                                                AS VLRCREDITO,
				SUM(ISNULL(CSALDOEXERCICIOMULTIMOEDA.VALOR, 0))  AS VALOR
				, CSALDOEXERCICIOMULTIMOEDA.MOEDA
				, CSALDOEXERCICIO.CODCCUSTO
				, CSALDOEXERCICIO.CODFILIAL
				, (CASE
					WHEN (CCONTA.NATUREZA = 0)
						THEN 'Credora'
					ELSE 'Devedora'
					END)                                         AS NATUREZACONTA
		FROM CSALDOEXERCICIO (NOLOCK)
		JOIN CEXERCICIO ON (CEXERCICIO.CODCOLIGADA = CSALDOEXERCICIO.CODCOLIGADA
						AND CEXERCICIO.IDEXERCICIO = CSALDOEXERCICIO.IDEXERCICIO)
		JOIN CCONTA (NOLOCK) ON CCONTA.CODCOLIGADA = CSALDOEXERCICIO.CODCOLCONTA
							AND CCONTA.CODCONTA = CSALDOEXERCICIO.CODCONTA
		LEFT JOIN CSALDOEXERCICIOMULTIMOEDA (NOLOCK) ON CSALDOEXERCICIOMULTIMOEDA.CODCOLIGADA = CSALDOEXERCICIO.CODCOLIGADA
													AND CSALDOEXERCICIOMULTIMOEDA.IDSALDOEXERCICIO = CSALDOEXERCICIO.IDSALDOEXERCICIO
		WHERE CSALDOEXERCICIO.IDEXERCICIO = (SELECT MAX(IDEXERCICIO) FROM CEXERCICIO (NOLOCK)
											WHERE CODCOLIGADA = CSALDOEXERCICIO.CODCOLIGADA)
		AND CSALDOEXERCICIO.VALOR <> 0
		GROUP BY CSALDOEXERCICIO.CODCOLIGADA, CSALDOEXERCICIO.CODCONTA, CEXERCICIO.DATAFINAL,
		CSALDOEXERCICIOMULTIMOEDA.MOEDA, CSALDOEXERCICIO.CODCCUSTO, CSALDOEXERCICIO.CODFILIAL, CCONTA.NATUREZA
	)
)
UNION ALL
(
	SELECT CPARTIDA.CODCOLIGADA,
			CPARTIDA.DATA,
			CPARTIDA.DEBITO										AS CONTA,
		CAST(SUM (CASE
					WHEN ( CCONTA.NATSPED = '04'
					AND  ISNULL(CHISTP.HISTFECHA, 0) = 1 ) THEN 0
					ELSE ISNULL(CPARTIDA.VALOR, 0)
			      END) AS NUMERIC(15,4))						AS VLRDEBITO,
			0													AS VLRCREDITO,
			0													AS VALOR
			, (SELECT TEXTO FROM CPARAM
			   WHERE CODCOLIGADA = CPARTIDA.CODCOLIGADA
			   AND ID = 79)										AS MOEDA
			,CPARTIDA.CODCCUSTO
			,CPARTIDA.CODFILIAL
			, (CASE
			    WHEN (CCONTA.NATUREZA = 0)
			     THEN 'Credora'
			    ELSE 'Devedora'
			   END)                                             AS NATUREZACONTA
	FROM  CPARTIDA (NOLOCK)
		JOIN CCONTA (NOLOCK)
				ON CCONTA.CODCOLIGADA = CPARTIDA.CODCOLDEBITO
				AND CCONTA.CODCONTA = CPARTIDA.DEBITO
		LEFT JOIN CHISTP(NOLOCK)
					ON CHISTP.CODCOLIGADA = CPARTIDA.CODCOLIGADA
						AND CHISTP.CODHISTP = CPARTIDA.CODHISTP
	WHERE CPARTIDA.DEBITO IS NOT NULL
			AND CPARTIDA.CODLOTE = 0
	GROUP BY CPARTIDA.CODCOLIGADA, CPARTIDA.DATA, CPARTIDA.DEBITO,
	CPARTIDA.VALOR, CPARTIDA.CODCCUSTO, CPARTIDA.CODFILIAL, CCONTA.NATUREZA
	UNION ALL
	SELECT CPARTIDA.CODCOLIGADA,
			CPARTIDA.DATA,
			CPARTIDA.CREDITO									AS CONTA,
			0													AS VLRDEBITO,
		CAST(SUM(CASE
					WHEN (CCONTA.NATSPED = '04'
					AND ISNULL(CHISTP.HISTFECHA, 0) = 1 ) THEN 0
					ELSE ( -1 * ISNULL(CPARTIDA.VALOR, 0) )
				 END) AS NUMERIC(15,4))							AS VLRCREDITO,
			0													AS VALOR
			, (SELECT TEXTO FROM CPARAM
			   WHERE CODCOLIGADA = CPARTIDA.CODCOLIGADA
			   AND ID = 79)										AS MOEDA
			,CPARTIDA.CODCCUSTO
			,CPARTIDA.CODFILIAL
			, (CASE
			    WHEN (CCONTA.NATUREZA = 0)
			     THEN 'Credora'
			    ELSE 'Devedora'
			   END)                                             AS NATUREZACONTA
	FROM CPARTIDA (NOLOCK)
		JOIN CCONTA (NOLOCK)
				ON CCONTA.CODCOLIGADA = CPARTIDA.CODCOLCREDITO
				AND CCONTA.CODCONTA = CPARTIDA.CREDITO
		LEFT JOIN CHISTP (NOLOCK)
					ON CHISTP.CODCOLIGADA = CPARTIDA.CODCOLIGADA
						AND CHISTP.CODHISTP = CPARTIDA.CODHISTP
	WHERE CPARTIDA.CREDITO IS NOT NULL
			AND CPARTIDA.CODLOTE = 0
	GROUP BY CPARTIDA.CODCOLIGADA,CPARTIDA.CREDITO, CPARTIDA.DATA,
	CPARTIDA.VALOR, CPARTIDA.CODCCUSTO, CPARTIDA.CODFILIAL, CCONTA.NATUREZA
UNION ALL
	SELECT CPARTIDA.CODCOLIGADA,
			CPARTIDA.DATA,
			CPARTIDA.DEBITO							      AS CONTA,
			0										      AS VLRDEBITO,
			0										      AS VLRCREDITO,
			CAST(SUM(ISNULL(CPARTIDA.VALOR, 0))	AS NUMERIC(15,4)) AS VALOR
			, (SELECT TEXTO FROM CPARAM
			   WHERE CODCOLIGADA = CPARTIDA.CODCOLIGADA
			   AND ID = 79)								  AS MOEDA
			,CPARTIDA.CODCCUSTO
			,CPARTIDA.CODFILIAL
			, (CASE
			    WHEN (CCONTA.NATUREZA = 0)
			     THEN 'Credora'
			    ELSE 'Devedora'
			   END)                                       AS NATUREZACONTA
	FROM CPARTIDA (NOLOCK)
		JOIN CCONTA (NOLOCK)
				ON CCONTA.CODCOLIGADA = CPARTIDA.CODCOLDEBITO
				AND CCONTA.CODCONTA = CPARTIDA.DEBITO
	WHERE CPARTIDA.DEBITO IS NOT NULL
			AND CPARTIDA.CODLOTE = 0
	GROUP BY CPARTIDA.CODCOLIGADA, CPARTIDA.DEBITO, CPARTIDA.DATA,
	CPARTIDA.VALOR, CPARTIDA.CODCCUSTO, CPARTIDA.CODFILIAL, CCONTA.NATUREZA
UNION ALL
    SELECT CPARTIDA.CODCOLIGADA,
			CPARTIDA.DATA,
            CPARTIDA.CREDITO						      AS CONTA,
            0										      AS VLRDEBITO,
            0										      AS VLRCREDITO,
            CAST(SUM(-1 * ISNULL(CPARTIDA.VALOR, 0)) AS NUMERIC(15,4)) AS VALOR
			, (SELECT TEXTO FROM CPARAM
			   WHERE CODCOLIGADA = CPARTIDA.CODCOLIGADA
			   AND ID = 79)								  AS MOEDA
			, CPARTIDA.CODCCUSTO
			, CPARTIDA.CODFILIAL
			, (CASE
			    WHEN (CCONTA.NATUREZA = 0)
			     THEN 'Credora'
			    ELSE 'Devedora'
			   END)                                       AS NATUREZACONTA
    FROM CPARTIDA (NOLOCK)
         JOIN CCONTA (NOLOCK)
                ON CCONTA.CODCOLIGADA = CPARTIDA.CODCOLCREDITO
                AND CCONTA.CODCONTA = CPARTIDA.CREDITO
    WHERE CPARTIDA.CREDITO IS NOT NULL
            AND CPARTIDA.CODLOTE = 0
    GROUP BY CPARTIDA.CODCOLIGADA, CPARTIDA.CREDITO, CPARTIDA.DATA,
	         CPARTIDA.VALOR, CPARTIDA.CODCCUSTO, CPARTIDA.CODFILIAL, CCONTA.NATUREZA
UNION ALL
	SELECT CSALDOEXERCICIO.CODCOLIGADA,
			CEXERCICIO.DATAFINAL								AS DATA,
            CSALDOEXERCICIO.CODCONTA							AS CONTA,
            0													AS VLRDEBITO,
            0													AS VLRCREDITO,
            CAST(SUM(ISNULL(CSALDOEXERCICIO.VALOR, 0)) AS NUMERIC(15,4)) AS VALOR
			, (SELECT TEXTO FROM CPARAM
			   WHERE CODCOLIGADA = CSALDOEXERCICIO.CODCOLIGADA
			   AND ID = 79)										AS MOEDA
			, CSALDOEXERCICIO.CODCCUSTO
			, CSALDOEXERCICIO.CODFILIAL
			, (CASE
			    WHEN (CCONTA.NATUREZA = 0)
			     THEN 'Credora'
			    ELSE 'Devedora'
			   END)												AS NATUREZACONTA
    FROM CSALDOEXERCICIO (NOLOCK)
	JOIN CEXERCICIO ON (CEXERCICIO.CODCOLIGADA = CSALDOEXERCICIO.CODCOLIGADA
					AND CEXERCICIO.IDEXERCICIO = CSALDOEXERCICIO.IDEXERCICIO)
    JOIN CCONTA (NOLOCK) ON (CCONTA.CODCOLIGADA = CSALDOEXERCICIO.CODCOLCONTA
						 AND CCONTA.CODCONTA = CSALDOEXERCICIO.CODCONTA)
    WHERE CSALDOEXERCICIO.IDEXERCICIO = (SELECT MAX(IDEXERCICIO)
										  FROM CEXERCICIO (NOLOCK)
										  WHERE CODCOLIGADA = CSALDOEXERCICIO.CODCOLIGADA)
	AND CSALDOEXERCICIO.VALOR <> 0
    GROUP BY CSALDOEXERCICIO.CODCOLIGADA, CSALDOEXERCICIO.CODCONTA, CEXERCICIO.DATAFINAL,
	CSALDOEXERCICIO.CODCCUSTO, CSALDOEXERCICIO.CODFILIAL, CCONTA.NATUREZA
)

Columns

View Definition

Possibly Referenced Tables/Views