VWPPPDATASFINAIS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CODCOLIGADA

DCODCOLIGADA

null

CHAPA

DCHAPA

null

DTFINAL

datetime

16,3

√

null

TOMADOR

varchar

√

null

View Definition

CREATE VIEW VWPPPDATASFINAIS
AS
(
		SELECT PFHSTFCO.CODCOLIGADA
			,PFHSTFCO.CHAPA
			,DATEADD(DAY, - 1, PFHSTFCO.DTMUDANCA) DTFINAL
			,NULL TOMADOR
		FROM PFHSTFCO
		WHERE NOT EXISTS (
				SELECT 1
				FROM VFUNCNVINC
				WHERE PFHSTFCO.CODCOLIGADA = VFUNCNVINC.CODCOLIGADA
					AND PFHSTFCO.CHAPA = VFUNCNVINC.CHAPA
					AND (
						VFUNCNVINC.DATAINICIAL <= PFHSTFCO.DTMUDANCA
						AND (
							VFUNCNVINC.DATAFINAL >= PFHSTFCO.DTMUDANCA
							OR VFUNCNVINC.DATAFINAL IS NULL
							)
						)
				)
			AND NOT EXISTS (
				SELECT TOP 1 1
				FROM PFHSTFCO P  WITH (NOLOCK)
				WHERE P.CODFUNCAO = PFHSTFCO.CODFUNCAO
					AND P.CHAPA = PFHSTFCO.CHAPA
					AND P.CODCOLIGADA = PFHSTFCO.CODCOLIGADA
					AND P.DTMUDANCA = (
						SELECT MAX(PP.DTMUDANCA)
						FROM PFHSTFCO PP  WITH (NOLOCK)
						WHERE PP.CHAPA = P.CHAPA
							AND PP.CODCOLIGADA = P.CODCOLIGADA
							AND PP.DTMUDANCA < PFHSTFCO.DTMUDANCA
						)
				)
		UNION
		SELECT DISTINCT CODCOLIGADA
			,CHAPA
			,CONVERT(DATETIME, '31/12/01', 3) DTINICIO
			,NULL TOMADOR
		FROM PFUNC
		WHERE (
				DATADEMISSAO IS NULL
				OR DATADEMISSAO > '01/01/2002'
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PFHSTFCO
				WHERE PFHSTFCO.CODCOLIGADA = PFUNC.CODCOLIGADA
					AND PFHSTFCO.CHAPA = PFUNC.CHAPA
					AND DTMUDANCA = '01/01/2002'
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PFHSTSEC
				WHERE PFHSTSEC.CODCOLIGADA = PFUNC.CODCOLIGADA
					AND PFHSTSEC.CHAPA = PFUNC.CHAPA
					AND DTMUDANCA = '01/01/2002'
				)
			AND NOT EXISTS (
				SELECT 1
				FROM VFUNCNVINC
				WHERE PFUNC.CODCOLIGADA = VFUNCNVINC.CODCOLIGADA
					AND PFUNC.CHAPA = VFUNCNVINC.CHAPA
					AND (
						VFUNCNVINC.DATAINICIAL <= '01/01/2002'
						AND (
							VFUNCNVINC.DATAFINAL >= '01/01/2002'
							OR VFUNCNVINC.DATAFINAL IS NULL
							)
						)
				)
		UNION
		SELECT PFHSTSEC.CODCOLIGADA
			,PFHSTSEC.CHAPA
			,DATEADD(DAY, - 1, PFHSTSEC.DTMUDANCA) DTINICIO
			,NULL TOMADOR
		FROM PFHSTSEC
		WHERE NOT EXISTS (
				SELECT 1
				FROM VFUNCNVINC
				WHERE PFHSTSEC.CODCOLIGADA = VFUNCNVINC.CODCOLIGADA
					AND PFHSTSEC.CHAPA = VFUNCNVINC.CHAPA
					AND (
						VFUNCNVINC.DATAINICIAL <= PFHSTSEC.DTMUDANCA
						AND (
							VFUNCNVINC.DATAFINAL >= PFHSTSEC.DTMUDANCA
							OR VFUNCNVINC.DATAFINAL IS NULL
							)
						)
				)
		UNION
		SELECT CODCOLIGADA
			,CHAPA
			,DATEADD(DAY, - 1, DATAINICIAL) DTINICIO
			,NULL TOMADOR
		FROM VFUNCNVINC
		UNION
		SELECT CODCOLIGADA
			,CHAPA
			,DATEADD(DAY, - 1, DTMUDANCA) DTINICIO
			,NULL TOMADOR
		FROM PHSTOCORRENCIA
		WHERE NOT EXISTS (
				SELECT 1
				FROM VFUNCNVINC
				WHERE PHSTOCORRENCIA.CODCOLIGADA = VFUNCNVINC.CODCOLIGADA
					AND PHSTOCORRENCIA.CHAPA = VFUNCNVINC.CHAPA
					AND (
						VFUNCNVINC.DATAINICIAL <= PHSTOCORRENCIA.DTMUDANCA
						AND (
							VFUNCNVINC.DATAFINAL >= PHSTOCORRENCIA.DTMUDANCA
							OR VFUNCNVINC.DATAFINAL IS NULL
							)
						)
				)
		UNION
		SELECT CODCOLIGADA
			,CHAPASUBST
			,DATEADD(DAY, - 1, DATAINICIO) DTINICIO
			,NULL TOMADOR
		FROM PSUBSTCHEFE
		WHERE NOT EXISTS (
				SELECT 1
				FROM VFUNCNVINC
				WHERE PSUBSTCHEFE.CODCOLIGADA = VFUNCNVINC.CODCOLIGADA
					AND PSUBSTCHEFE.CHAPASUBST = VFUNCNVINC.CHAPA
					AND (
						VFUNCNVINC.DATAINICIAL <= PSUBSTCHEFE.DATAINICIO
						AND (
							VFUNCNVINC.DATAFINAL >= PSUBSTCHEFE.DATAINICIO
							OR VFUNCNVINC.DATAFINAL IS NULL
							)
						)
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTCHEFE X
				WHERE X.CODCOLIGADA = PSUBSTCHEFE.CODCOLIGADA
					AND X.CHAPASUBST = PSUBSTCHEFE.CHAPASUBST
					AND (
						X.DATAINICIO < PSUBSTCHEFE.DATAINICIO
						AND (
							X.DATAFIM >= PSUBSTCHEFE.DATAINICIO
							OR X.DATAFIM IS NULL
							)
						)
				)
		UNION
		SELECT CODCOLIGADA
			,CHAPASUBST
			,DATEADD(DAY, - 1, DATAINICIO) DTINICIO
			,NULL TOMADOR
		FROM PSUBSTSUP
		WHERE NOT EXISTS (
				SELECT 1
				FROM VFUNCNVINC
				WHERE PSUBSTSUP.CODCOLIGADA = VFUNCNVINC.CODCOLIGADA
					AND PSUBSTSUP.CHAPASUBST = VFUNCNVINC.CHAPA
					AND (
						VFUNCNVINC.DATAINICIAL <= PSUBSTSUP.DATAINICIO
						AND (
							VFUNCNVINC.DATAFINAL >= PSUBSTSUP.DATAINICIO
							OR VFUNCNVINC.DATAFINAL IS NULL
							)
						)
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTCHEFE
				WHERE PSUBSTSUP.CODCOLIGADA = PSUBSTCHEFE.CODCOLIGADA
					AND PSUBSTSUP.CHAPASUBST = PSUBSTCHEFE.CHAPASUBST
					AND (
						PSUBSTCHEFE.DATAINICIO <= PSUBSTSUP.DATAINICIO
						AND (
							PSUBSTCHEFE.DATAFIM >= PSUBSTSUP.DATAINICIO
							OR PSUBSTCHEFE.DATAFIM IS NULL
							)
						)
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTSUP X
				WHERE X.CODCOLIGADA = PSUBSTSUP.CODCOLIGADA
					AND X.CHAPASUBST = PSUBSTSUP.CHAPASUBST
					AND (
						X.DATAINICIO < PSUBSTSUP.DATAINICIO
						AND (
							X.DATAFIM >= PSUBSTSUP.DATAINICIO
							OR X.DATAFIM IS NULL
							)
						)
				)
		UNION
		SELECT CODCOLIGADA
			,CHAPASUBST
			,DATAFIM DTINICIO
			,NULL TOMADOR
		FROM PSUBSTCHEFE
		WHERE NOT EXISTS (
				SELECT 1
				FROM VFUNCNVINC
				WHERE PSUBSTCHEFE.CODCOLIGADA = VFUNCNVINC.CODCOLIGADA
					AND PSUBSTCHEFE.CHAPASUBST = VFUNCNVINC.CHAPA
					AND (
						VFUNCNVINC.DATAINICIAL <= PSUBSTCHEFE.DATAFIM
						AND (
							VFUNCNVINC.DATAFINAL >= PSUBSTCHEFE.DATAFIM
							OR VFUNCNVINC.DATAFINAL IS NULL
							)
						)
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTCHEFE X
				WHERE X.CODCOLIGADA = PSUBSTCHEFE.CODCOLIGADA
					AND X.CHAPASUBST = PSUBSTCHEFE.CHAPASUBST
					AND (
						X.DATAINICIO < PSUBSTCHEFE.DATAFIM
						AND (
							X.DATAFIM > PSUBSTCHEFE.DATAFIM
							OR X.DATAFIM IS NULL
							)
						)
				)
		UNION
		SELECT CODCOLIGADA
			,CHAPASUBST
			,DATAFIM DTINICIO
			,NULL TOMADOR
		FROM PSUBSTSUP
		WHERE NOT EXISTS (
				SELECT 1
				FROM VFUNCNVINC
				WHERE PSUBSTSUP.CODCOLIGADA = VFUNCNVINC.CODCOLIGADA
					AND PSUBSTSUP.CHAPASUBST = VFUNCNVINC.CHAPA
					AND (
						VFUNCNVINC.DATAINICIAL <= PSUBSTSUP.DATAFIM
						AND (
							VFUNCNVINC.DATAFINAL >= PSUBSTSUP.DATAFIM
							OR VFUNCNVINC.DATAFINAL IS NULL
							)
						)
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTCHEFE
				WHERE PSUBSTSUP.CODCOLIGADA = PSUBSTCHEFE.CODCOLIGADA
					AND PSUBSTSUP.CHAPASUBST = PSUBSTCHEFE.CHAPASUBST
					AND (
						PSUBSTCHEFE.DATAINICIO <= PSUBSTSUP.DATAFIM
						AND (
							PSUBSTCHEFE.DATAFIM >= PSUBSTSUP.DATAFIM
							OR PSUBSTCHEFE.DATAFIM IS NULL
							)
						)
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTSUP X
				WHERE X.CODCOLIGADA = PSUBSTSUP.CODCOLIGADA
					AND X.CHAPASUBST = PSUBSTSUP.CHAPASUBST
					AND (
						X.DATAINICIO < PSUBSTSUP.DATAFIM
						AND (
							X.DATAFIM > PSUBSTSUP.DATAFIM
							OR X.DATAFIM IS NULL
							)
						)
				)
		UNION
		SELECT PFHSTFCO.CODCOLIGADA
			,PFHSTFCO.CHAPA
			,DATEADD(DAY, - 1, PFHSTFCO.DTMUDANCA) DTINICIO
			,CODCFO TOMADOR
		FROM PFHSTFCO
			,VTOMADORSERVICOVINC
		WHERE PFHSTFCO.CODCOLIGADA = VTOMADORSERVICOVINC.CODCOLIGADA
			AND PFHSTFCO.CHAPA = VTOMADORSERVICOVINC.CHAPA
			AND PFHSTFCO.DTMUDANCA >= VTOMADORSERVICOVINC.DATAINICIAL
			AND (
				PFHSTFCO.DTMUDANCA <= VTOMADORSERVICOVINC.DATAFINAL
				OR VTOMADORSERVICOVINC.DATAFINAL IS NULL
				)
			AND NOT EXISTS (
				SELECT TOP 1 1
				FROM PFHSTFCO P  WITH (NOLOCK)
				WHERE P.CODFUNCAO = PFHSTFCO.CODFUNCAO
					AND P.CHAPA = PFHSTFCO.CHAPA
					AND P.CODCOLIGADA = PFHSTFCO.CODCOLIGADA
					AND P.DTMUDANCA = (
						SELECT MAX(PP.DTMUDANCA)
						FROM PFHSTFCO PP  WITH (NOLOCK)
						WHERE PP.CHAPA = P.CHAPA
							AND PP.CODCOLIGADA = P.CODCOLIGADA
							AND PP.DTMUDANCA < PFHSTFCO.DTMUDANCA
						)
				)
		UNION
		SELECT CODCOLIGADA
			,CHAPA
			,DATEADD(DAY, - 1, DATAINICIAL) DTINICIO
			,CODCFO TOMADOR
		FROM VTOMADORSERVICOVINC
		UNION
		SELECT DISTINCT CODCOLIGADA
			,CHAPA
			,CONVERT(DATETIME, '31/12/01', 3) DTINICIO
			,CODCFO TOMADOR
		FROM VTOMADORSERVICOVINC
		WHERE VTOMADORSERVICOVINC.DATAINICIAL < '01/01/2002'
			AND (
				VTOMADORSERVICOVINC.DATAFINAL >= '01/01/2002'
				OR VTOMADORSERVICOVINC.DATAFINAL IS NULL
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PFHSTFCO
				WHERE PFHSTFCO.CODCOLIGADA = VTOMADORSERVICOVINC.CODCOLIGADA
					AND PFHSTFCO.CHAPA = VTOMADORSERVICOVINC.CHAPA
					AND DTMUDANCA = '01/01/2002'
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PFHSTSEC
				WHERE PFHSTSEC.CODCOLIGADA = VTOMADORSERVICOVINC.CODCOLIGADA
					AND PFHSTSEC.CHAPA = VTOMADORSERVICOVINC.CHAPA
					AND DTMUDANCA = '01/01/2002'
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PHSTOCORRENCIA
				WHERE PHSTOCORRENCIA.CODCOLIGADA = VTOMADORSERVICOVINC.CODCOLIGADA
					AND PHSTOCORRENCIA.CHAPA = VTOMADORSERVICOVINC.CHAPA
					AND DTMUDANCA = '01/01/2002'
				)
		UNION
		SELECT PFHSTSEC.CODCOLIGADA
			,PFHSTSEC.CHAPA
			,DATEADD(DAY, - 1, PFHSTSEC.DTMUDANCA) DTINICIO
			,CODCFO TOMADOR
		FROM PFHSTSEC
			,VTOMADORSERVICOVINC
		WHERE PFHSTSEC.CODCOLIGADA = VTOMADORSERVICOVINC.CODCOLIGADA
			AND PFHSTSEC.CHAPA = VTOMADORSERVICOVINC.CHAPA
			AND PFHSTSEC.DTMUDANCA >= VTOMADORSERVICOVINC.DATAINICIAL
			AND (
				PFHSTSEC.DTMUDANCA <= VTOMADORSERVICOVINC.DATAFINAL
				OR VTOMADORSERVICOVINC.DATAFINAL IS NULL
				)
		UNION
		SELECT CODCOLIGADA
			,CHAPA
			,DATADEMISSAO DTINICIO
			,NULL TOMADOR
		FROM PFUNC
		WHERE DATADEMISSAO IS NOT NULL
		UNION
		SELECT CODCOLIGADA
			,CHAPA
			,DATAFINAL DTINICIO
			,CODCFO TOMADOR
		FROM VTOMADORSERVICOVINC
		WHERE DATAFINAL IS NOT NULL
		UNION
		SELECT PSUBSTCHEFE.CODCOLIGADA
			,PSUBSTCHEFE.CHAPASUBST
			,DATEADD(DAY, - 1, PSUBSTCHEFE.DATAINICIO) DTINICIO
			,VTOMADORSERVICOVINC.CODCFO TOMADOR
		FROM PSUBSTCHEFE
			,VTOMADORSERVICOVINC
		WHERE PSUBSTCHEFE.CODCOLIGADA = VTOMADORSERVICOVINC.CODCOLIGADA
			AND PSUBSTCHEFE.CHAPASUBST = VTOMADORSERVICOVINC.CHAPA
			AND VTOMADORSERVICOVINC.DATAINICIAL <= PSUBSTCHEFE.DATAINICIO
			AND (
				VTOMADORSERVICOVINC.DATAFINAL >= PSUBSTCHEFE.DATAINICIO
				OR VTOMADORSERVICOVINC.DATAFINAL IS NULL
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTCHEFE X
				WHERE X.CODCOLIGADA = PSUBSTCHEFE.CODCOLIGADA
					AND X.CHAPASUBST = PSUBSTCHEFE.CHAPASUBST
					AND (
						X.DATAINICIO < PSUBSTCHEFE.DATAINICIO
						AND (
							X.DATAFIM >= PSUBSTCHEFE.DATAINICIO
							OR X.DATAFIM IS NULL
							)
						)
				)
		UNION
		SELECT PSUBSTSUP.CODCOLIGADA
			,PSUBSTSUP.CHAPASUBST
			,DATEADD(DAY, - 1, PSUBSTSUP.DATAINICIO) DTINICIO
			,VTOMADORSERVICOVINC.CODCFO TOMADOR
		FROM PSUBSTSUP
			,VTOMADORSERVICOVINC
		WHERE PSUBSTSUP.CODCOLIGADA = VTOMADORSERVICOVINC.CODCOLIGADA
			AND PSUBSTSUP.CHAPASUBST = VTOMADORSERVICOVINC.CHAPA
			AND VTOMADORSERVICOVINC.DATAINICIAL <= PSUBSTSUP.DATAINICIO
			AND (
				VTOMADORSERVICOVINC.DATAFINAL >= PSUBSTSUP.DATAINICIO
				OR VTOMADORSERVICOVINC.DATAFINAL IS NULL
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTCHEFE
				WHERE PSUBSTSUP.CODCOLIGADA = PSUBSTCHEFE.CODCOLIGADA
					AND PSUBSTSUP.CHAPASUBST = PSUBSTCHEFE.CHAPASUBST
					AND PSUBSTCHEFE.DATAINICIO <= PSUBSTSUP.DATAINICIO
					AND (
						PSUBSTCHEFE.DATAFIM >= PSUBSTSUP.DATAINICIO
						OR PSUBSTCHEFE.DATAFIM IS NULL
						)
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTSUP X
				WHERE X.CODCOLIGADA = PSUBSTSUP.CODCOLIGADA
					AND X.CHAPASUBST = PSUBSTSUP.CHAPASUBST
					AND (
						X.DATAINICIO < PSUBSTSUP.DATAINICIO
						AND (
							X.DATAFIM >= PSUBSTSUP.DATAINICIO
							OR X.DATAFIM IS NULL
							)
						)
				)
		UNION
		SELECT PSUBSTCHEFE.CODCOLIGADA
			,PSUBSTCHEFE.CHAPASUBST
			,PSUBSTCHEFE.DATAFIM DTINICIO
			,VTOMADORSERVICOVINC.CODCFO TOMADOR
		FROM PSUBSTCHEFE
			,VTOMADORSERVICOVINC
		WHERE PSUBSTCHEFE.CODCOLIGADA = VTOMADORSERVICOVINC.CODCOLIGADA
			AND PSUBSTCHEFE.CHAPASUBST = VTOMADORSERVICOVINC.CHAPA
			AND VTOMADORSERVICOVINC.DATAINICIAL <= PSUBSTCHEFE.DATAFIM
			AND (
				VTOMADORSERVICOVINC.DATAFINAL >= PSUBSTCHEFE.DATAFIM
				OR VTOMADORSERVICOVINC.DATAFINAL IS NULL
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTCHEFE X
				WHERE X.CODCOLIGADA = PSUBSTCHEFE.CODCOLIGADA
					AND X.CHAPASUBST = PSUBSTCHEFE.CHAPASUBST
					AND (
						X.DATAINICIO < PSUBSTCHEFE.DATAFIM
						AND (
							X.DATAFIM > PSUBSTCHEFE.DATAFIM
							OR X.DATAFIM IS NULL
							)
						)
				)
		UNION
		SELECT PSUBSTSUP.CODCOLIGADA
			,PSUBSTSUP.CHAPASUBST
			,PSUBSTSUP.DATAFIM DTINICIO
			,VTOMADORSERVICOVINC.CODCFO TOMADOR
		FROM PSUBSTSUP
			,VTOMADORSERVICOVINC
		WHERE PSUBSTSUP.CODCOLIGADA = VTOMADORSERVICOVINC.CODCOLIGADA
			AND PSUBSTSUP.CHAPASUBST = VTOMADORSERVICOVINC.CHAPA
			AND VTOMADORSERVICOVINC.DATAINICIAL <= PSUBSTSUP.DATAFIM
			AND (
				VTOMADORSERVICOVINC.DATAFINAL >= PSUBSTSUP.DATAFIM
				OR VTOMADORSERVICOVINC.DATAFINAL IS NULL
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTCHEFE
				WHERE PSUBSTSUP.CODCOLIGADA = PSUBSTCHEFE.CODCOLIGADA
					AND PSUBSTSUP.CHAPASUBST = PSUBSTCHEFE.CHAPASUBST
					AND PSUBSTCHEFE.DATAINICIO <= PSUBSTSUP.DATAFIM
					AND (
						PSUBSTCHEFE.DATAFIM >= PSUBSTSUP.DATAFIM
						OR PSUBSTCHEFE.DATAFIM IS NULL
						)
				)
			AND NOT EXISTS (
				SELECT 1
				FROM PSUBSTSUP X
				WHERE X.CODCOLIGADA = PSUBSTSUP.CODCOLIGADA
					AND X.CHAPASUBST = PSUBSTSUP.CHAPASUBST
					AND (
						X.DATAINICIO < PSUBSTSUP.DATAFIM
						AND (
							X.DATAFIM > PSUBSTSUP.DATAFIM
							OR X.DATAFIM IS NULL
							)
						)
				)
		)

Columns

View Definition

Possibly Referenced Tables/Views