Replacement for dbo.tx_xref

Hi all,

I am fairly new to Tessitura and am also new to SQL. We run a customer report that used table dbo.tx_xref which apparently is no longer in use with V11. Therefore the report is populating some old/incorrect information. I was wondering if there is a function that replaces this table, or if I have to join T_Association & T_Affiliation?

Here is the query:

USE [impresario]

GO

/****** Object:  UserDefinedFunction [dbo].[LFS_Assoc_name]    Script Date: 07/25/2013 13:59:57 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[LFS_Assoc_name]  (@customer_no int, @assoc_type int)

RETURNS varchar(255)

AS

/*****************************************************************************************************************

This will so need a v11 makeover.

*****************************************************************************************************************/

begin

declare @assoc_name varchar(255)

select @assoc_name = ''

select @assoc_name = @assoc_name + rtrim(a.assoc_name) + ','

from (select top 500 a.name+ ' '+ CASE n1n2_ind WHEN 1 then '(N1)' when 2 then '(N2)' when 3 then '(Both)' else null end as assoc_name

from dbo.tx_xref a

where a.customer_no = @customer_no

and a.type=@assoc_type

and (start_dt<getdate() or start_dt is null)

and (end_dt>getdate() or end_dt is null)) as a

If Datalength(@assoc_name) > 1 -- CWR 1/24/2006

select @assoc_name = replace(@assoc_name+'~', ',~', '')

RETURN @assoc_name

END