IF OBJECT_ID('V_GWEB_SCRIPTS_TOTAL_PENDENTES_FILIAIS') IS NOT NULL
	DROP VIEW V_GWEB_SCRIPTS_TOTAL_PENDENTES_FILIAIS
GO

CREATE VIEW V_GWEB_SCRIPTS_TOTAL_PENDENTES_FILIAIS AS
	SELECT RS.CD_FILIAL, SUM(RS.TOTAL) AS TOTAL 
	FROM (
		SELECT 0 AS CD_FILIAL, COUNT(*) AS TOTAL
		FROM DSYNC_CH_SCRIPTS SC
		LEFT JOIN DSYNC_CH_SCRIPTS_STATUS SS
			ON SS.CRC_FILE = SC.CRC_FILE
		WHERE (SS.STS_SCRIPT = 2
			OR SS.STS_SCRIPT IS NULL)
			AND SC.DB_ID IN (1,2,3)
	UNION
		SELECT DC.CID AS CD_FILIAL, COUNT(*) AS TOTAL
		FROM DSYNC_CLIENT DC
		LEFT JOIN DSYNC_CH_SCRIPTS SC
			ON SC.CRC_FILE IS NOT NULL
		LEFT JOIN DSYNC_CH_SCRIPTS_STATUS SS
			ON SS.CRC_FILE = SC.CRC_FILE
			AND SS.CD_FILIAL = DC.CID
		WHERE (SS.STS_SCRIPT = 2
			OR SS.STS_SCRIPT IS NULL)
			AND SC.DB_ID IN (4,5)
			AND LTRIM(RTRIM(DC.CHOST)) <> 'LOCALHOST'
			AND DC.ATIVO = 1
		GROUP BY DC.CID
	) AS RS
	GROUP BY RS.CD_FILIAL