SELECT
	'INSERT INTO [dbo].[OrgReferenceCode] ([IpOrg],[RefTypeCode],[RefVersion],[RefCode],[RefLowValue],[RefHighValue],[ScreenSortOrder],[RefCodeImage]) SELECT '
		+ CONVERT(VARCHAR,[IpOrg]) + ','
		+ '''' + [RefTypeCode] + ''','
		+ '''' + [RefVersion] + ''','
		+ '''' + [RefCode] + ''','
		+ CONVERT(VARCHAR, [RefLowValue]) + ','
		+ CONVERT(VARCHAR, [RefHighValue]) + ','
		+ CONVERT(VARCHAR, [ScreenSortOrder]) + ','
		+ CONVERT(VARCHAR, [RefCodeImage])
			+ ' WHERE NOT EXISTS (SELECT 1 FROM [dbo].[OrgReferenceCode]'
				+ ' WHERE [IpOrg] = ' + CONVERT(VARCHAR,[IpOrg])
				+ ' AND [RefTypeCode] = ''' + [RefTypeCode] + ''''
				+ ' AND [RefVersion] = ''' + [RefVersion] + ''''
				+ ' AND [RefCode] = ''' + [RefCode] + ''');' AS INSERT_SQL,
	'UPDATE [dbo].[OrgReferenceCode] SET '
		+ '[RefLowValue] = ' + CONVERT(VARCHAR, [RefLowValue]) + ','
		+ '[RefHighValue] = ' + CONVERT(VARCHAR, [RefHighValue]) + ','
		+ '[ScreenSortOrder] = ' + CONVERT(VARCHAR, [ScreenSortOrder]) + ','
		+ '[RefCodeImage] = ' + CONVERT(VARCHAR, [RefCodeImage])
			+ ' WHERE [IpOrg] = ' + CONVERT(VARCHAR,[IpOrg])
			+ ' AND [RefTypeCode] = ''' + [RefTypeCode] + ''''
			+ ' AND [RefVersion] = ''' + [RefVersion] + ''''
			+ ' AND [RefCode] = ''' + [RefCode] + ''';' AS UPDATE_SQL,
	[IpOrg],
	[RefTypeCode],
	[RefVersion],
	[RefCode],
	[RefLowValue],
	[RefHighValue],
	[ScreenSortOrder],
	[RefCodeColour],
	[RefCodeImage]
FROM
	[dbo].[OrgReferenceCode]
WHERE
		[RefTypeCode] = 'ROLE'
	AND	[RefCode] IN ('REPORTCONSUMEREXTERNAL','REPORTCONSUMERINTERNAL','SERVICEDESK')
	AND	[RefVersion] = (SELECT MAX([RefVersion]) FROM [dbo].[OrgReferenceCode] WHERE [RefTypeCode] = 'ROLE' AND [RefCode] IN ('REPORTCONSUMEREXTERNAL','REPORTCONSUMERINTERNAL','SERVICEDESK'))