CREATE PROCEDURE CP_Tab_Reportes_Copiar @Id_Reporte VARCHAR(50)
AS

DECLARE @Mensaje			VARCHAR(100)
BEGIN TRY
	BEGIN TRAN

	DECLARE @nro SMALLINT
	DECLARE @Id_Reporte_Nuevo VARCHAR(50)
	DECLARE @Descripcion_Nueva VARCHAR(200)
	DECLARE @Id_Banda INT
	DECLARE @Id_Banda_Nueva INT

	IF (NOT EXISTS (SELECT 1 FROM Tab_Reportes WHERE Id_Reporte = @Id_Reporte))
	BEGIN
		SELECT	 1					AS ErrorNumber 
				,0					AS ErrorSeverity  
				,0					AS ErrorState 
				,0					AS ErrorProcedure  
				,0					AS ErrorLine		
				,'No Existe Reporte Origen'			AS ErrorMessage
				,@Id_Reporte	AS Id_Reporte
		ROLLBACK
		RETURN 1
    END



	SET @nro = 1
	SET @Id_Reporte_Nuevo = RTRIM(LTRIM(@Id_Reporte)) +':'+ CAST(@nro AS VARCHAR(50))
	SET @Descripcion_Nueva = RTRIM(LTRIM((SELECT Descripcion FROM Tab_Reportes WHERE Id_Reporte = @Id_Reporte)))  +':'+ CAST(@nro AS VARCHAR(50))

	WHILE (EXISTS (SELECT 1 FROM Tab_Reportes WHERE Id_Reporte = @Id_Reporte_Nuevo))
	BEGIN
		SET @nro = @nro + 1
		IF @nro = 1000 
		BEGIN
			SELECT	 1					AS ErrorNumber 
					,0					AS ErrorSeverity  
					,0					AS ErrorState 
					,0					AS ErrorProcedure  
					,0					AS ErrorLine		
					,'Sueperado Limite de 1000 en nuevo Id'			AS ErrorMessage
					,@Id_Reporte	AS Id_Reporte
			ROLLBACK
			RETURN 1
		END

		SET @Id_Reporte_Nuevo = RTRIM(LTRIM(@Id_Reporte)) +':'+ CAST(@nro AS VARCHAR(50))
		SET @Descripcion_Nueva = RTRIM(LTRIM((SELECT Descripcion FROM Tab_Reportes WHERE Id_Reporte = @Id_Reporte)))  +':'+ CAST(@nro AS VARCHAR(50))
	END 

	/*agregago tab_reportes*/
	SELECT * INTO #TempRpt FROM Tab_Reportes WHERE Id_Reporte = @Id_Reporte
	UPDATE #TempRpt SET Id_Reporte = @Id_Reporte_Nuevo, Descripcion = @Descripcion_Nueva
	INSERT INTO Tab_Reportes SELECT * FROM #TempRpt 
	DROP TABLE #TempRpt

	

	/*agregago tab_reportes_bandas*/
	DECLARE Cursor_Bandas CURSOR FOR SELECT Id_Banda FROM Tab_Reportes_Bandas WHERE Id_Reporte = @Id_Reporte
	OPEN Cursor_Bandas
	FETCH NEXT FROM Cursor_Bandas INTO @Id_Banda
	WHILE (@@FETCH_STATUS=0)
	BEGIN
	    SELECT * INTO #TempRptBandas FROM Tab_Reportes_Bandas WHERE Id_Banda = @Id_Banda
		UPDATE #TempRptBandas SET Id_Reporte = @Id_Reporte_Nuevo
		ALTER TABLE #TempRptBandas DROP COLUMN Id_Banda
		INSERT INTO Tab_Reportes_Bandas	SELECT * FROM #TempRptBandas 
		SET @Id_Banda_Nueva = @@IDENTITY
		DROP TABLE #TempRptBandas

		/*agregago tab_reportes_col*/
		SELECT * INTO #TempRptCol FROM Tab_Reportes_Col WHERE Id_Banda = @Id_Banda
		UPDATE #TempRptCol SET Id_Reporte = @Id_Reporte_Nuevo, Id_Banda = @Id_Banda_Nueva
		ALTER TABLE #TempRptCol DROP COLUMN Id_Rpt_Col
		INSERT INTO Tab_Reportes_Col	SELECT * FROM #TempRptCol 
		DROP TABLE #TempRptCol

		FETCH NEXT FROM Cursor_Bandas INTO @Id_Banda
	END
	CLOSE Cursor_Bandas
	DEALLOCATE Cursor_Bandas

	/*agrego tab_reportes_user*/
	SELECT * INTO #TempRptUsers FROM Tab_Reportes_Users WHERE Id_Reporte = @Id_Reporte
	UPDATE #TempRptUsers SET Id_Reporte = @Id_Reporte_Nuevo
	INSERT INTO Tab_Reportes_Users	SELECT * FROM #TempRptUsers 
	DROP TABLE #TempRptUsers

	/*agrego tab_reportes_xcampos*/
	SELECT * INTO #TempRptxCampos FROM Tab_Reportes_xCampos WHERE Id_Reporte = @Id_Reporte
	UPDATE #TempRptxCampos SET Id_Reporte = @Id_Reporte_Nuevo
	INSERT INTO Tab_Reportes_xCampos SELECT * FROM #TempRptxCampos 
	DROP TABLE #TempRptxCampos


	COMMIT
	SELECT	 0					AS ErrorNumber 
			,0					AS ErrorSeverity  
			,0					AS ErrorState 
			,0					AS ErrorProcedure  
			,0					AS ErrorLine		
			,@Mensaje			AS ErrorMessage
			,@Id_Reporte_Nuevo	AS Id_Reporte

	


END TRY  


BEGIN CATCH  
    EXECUTE usp_GetErrorInfo;  
	ROLLBACK
END CATCH;   

