SELECT
	CONVERT(VARCHAR(1000), 'INSERT INTO [dbo].[OrgReferenceCodeDesc] ([IpOrg],[RefTypeCode],[RefVersion],[RefCode],[LanguageCode],[ShortDescription],[LongDescription],[ScreenSortOrder]) SELECT '
		+ CONVERT(VARCHAR,[IpOrg]) + ','
		+ '''' + [RefTypeCode] + ''','
		+ '''' + [RefVersion] + ''','
		+ '''' + [RefCode] + ''','
		+ '''' + [LanguageCode] + ''','
		+ '''' + [ShortDescription] + ''','
		+ '''' + [LongDescription] + ''','
		+ CONVERT(VARCHAR, [ScreenSortOrder])
			+ ' WHERE NOT EXISTS (SELECT 1 FROM [dbo].[OrgReferenceCodeDesc]'
				+ ' WHERE [IpOrg] = ' + CONVERT(VARCHAR,[IpOrg])
				+ ' AND [RefTypeCode] = ''' + [RefTypeCode] + ''''
				+ ' AND [RefVersion] = ''' + [RefVersion] + ''''
				+ ' AND [RefCode] = ''' + [RefCode] + ''');') AS INSERT_SQL,
	CONVERT(VARCHAR(1000), 'UPDATE [dbo].[OrgReferenceCodeDesc] SET '
		+ '[LanguageCode] = ''' + [LanguageCode] + ''','
		+ '[ShortDescription] = ''' + [ShortDescription] + ''','
		+ '[LongDescription] = ''' + [LongDescription] + ''','
		+ '[ScreenSortOrder] = ' + CONVERT(VARCHAR, [ScreenSortOrder])
			+ ' WHERE [IpOrg] = ' + CONVERT(VARCHAR,[IpOrg])
			+ ' AND [RefTypeCode] = ''' + [RefTypeCode] + ''''
			+ ' AND [RefVersion] = ''' + [RefVersion] + ''''
			+ ' AND [RefCode] = ''' + [RefCode] + ''';') AS UPDATE_SQL,
	[IpOrg],
	[RefTypeCode],
	[RefVersion],
	[RefCode],
	[LanguageCode],
	[ShortDescription],
	[LongDescription],
	[ScreenSortOrder]
FROM
	[dbo].[OrgReferenceCodeDesc]
WHERE
		[RefTypeCode] = 'ROLE'
	AND	[RefCode] IN ('REPORTCONSUMEREXTERNAL','REPORTCONSUMERINTERNAL','SERVICEDESK')
	AND	[RefVersion] = (SELECT MAX([RefVersion]) FROM [dbo].[OrgReferenceCodeDesc] WHERE [RefTypeCode] = 'ROLE' AND [RefCode] IN ('REPORTCONSUMEREXTERNAL','REPORTCONSUMERINTERNAL','SERVICEDESK'))
