Looks like just a few syntax issues.  Try these changes...

--Check for Object existence
IF (object_id('''+@DB_NAME+'.dbo.revenue_type'') is not null)
BEGIN

INSERT INTO @IDTABLE
select distinct...