VWPPPDATASINICIAIS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

CODCOLIGADA

DCODCOLIGADA

null

CHAPA

DCHAPA

null

DTINICIO

datetime

16,3

√

null

TOMADOR

varchar

√

null

View Definition

CREATE VIEW VWPPPDATASINICIAIS
AS
(
		SELECT PFHSTFCO.CODCOLIGADA
			,PFHSTFCO.CHAPA
			,PFHSTFCO.DTMUDANCA DTINICIO
			,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
			,'01/01/2002' 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
			,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, DATAFINAL) DTINICIO
			,NULL TOMADOR
		FROM VFUNCNVINC
		UNION
		SELECT CODCOLIGADA
			,CHAPA
			,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
			,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
			,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
			,DATEADD(DAY, 1, 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
			,DATEADD(DAY, 1, 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
			,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
			,DATAINICIAL DTINICIO
			,CODCFO TOMADOR
		FROM VTOMADORSERVICOVINC
		UNION
		SELECT DISTINCT CODCOLIGADA
			,CHAPA
			,'01/01/2002' 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
			,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
			,DATAINICIAL DTINICIO
			,CODCFO TOMADOR
		FROM VTOMADORSERVICOVINC
		UNION
		SELECT PSUBSTCHEFE.CODCOLIGADA
			,PSUBSTCHEFE.CHAPASUBST
			,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
			,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
			,DATEADD(DAY, 1, 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
			,DATEADD(DAY, 1, 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