IF OBJECT_ID('SP_GWEB_SCRIPTS_PENDENTES') IS NOT NULL
	DROP PROCEDURE SP_GWEB_SCRIPTS_PENDENTES
	
GO

CREATE PROCEDURE SP_GWEB_SCRIPTS_PENDENTES(@CD_FILIAL INT)
AS
BEGIN
	IF @CD_FILIAL = 0
	BEGIN
		SELECT SC.CRC_FILE, SC.FILE_NAME, SC.NUMBER, SC.DB_ID,
		SV.ID AS ID_VERSION, SV.VERSION AS PACK_VERSION, SV.DT_UPDATE AS DT_VERSION, 
		SS.CD_FILIAL, COALESCE(SS.STS_SCRIPT,0) AS STS_SCRIPT, COALESCE(SS.MSG_ERRO,'') AS MSG_ERRO, SS.DT_UPDATE AS DT_STATUS
		FROM DSYNC_CH_SCRIPTS SC
		LEFT JOIN DSYNC_CH_SCRIPTS_STATUS SS
			ON SC.CRC_FILE = SS.CRC_FILE
			AND SS.CD_FILIAL = 0
		LEFT JOIN DSYNC_CH_VERSION SV
			ON SC.VERSION_ID = SV.ID
		WHERE COALESCE(SS.STS_SCRIPT,0) <> 1
			AND SC.DB_ID IN (1,2,3)
		ORDER BY SC.DB_ID, SC.NUMBER
	END
	ELSE 
	BEGIN
		SELECT SC.CRC_FILE, SC.FILE_NAME, SC.NUMBER, SC.DB_ID,
		SV.ID AS ID_VERSION, SV.VERSION AS PACK_VERSION, SV.DT_UPDATE AS DT_VERSION, 
		SS.CD_FILIAL, COALESCE(SS.STS_SCRIPT,0) AS STS_SCRIPT, COALESCE(SS.MSG_ERRO,'') AS MSG_ERRO, SS.DT_UPDATE AS DT_STATUS
		FROM DSYNC_CLIENT DS
		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 = DS.CID
		LEFT JOIN DSYNC_CH_VERSION SV
			ON SC.VERSION_ID = SV.ID
		WHERE COALESCE(SS.STS_SCRIPT,0) <> 1
			AND SC.DB_ID IN (4,5)
			AND DS.CID = @CD_FILIAL
		ORDER BY SC.DB_ID, SC.NUMBER
	END
END