-- $Id:
-- $Revision: 1.7 $
-- $Date: 2004/04/21 16:29:02 $

-- shared functions between applications
-- please use the standard documentation format below

/*
=============================================
Author : XXX
Created: 01/2001
Updated: 02/2001
=============================================

The documentation

=============================================
*/



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


if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_AuthorTeamString]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_AuthorTeamString]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_AuthorTeamStringFull]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_AuthorTeamStringFull]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_CompareStrings]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_CompareStrings]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_StrSpaceAdd]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_StrSpaceAdd]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_cumulateBookNomRefCache]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_cumulateBookNomRefCache]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_cumulateJournalNomRefCache]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_cumulateJournalNomRefCache]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_getAuthorTeam]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_getAuthorTeam]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_getFullNomRefCache]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_getFullNomRefCache]

GO
if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_getFullRefCache]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_getFullRefCache]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_getNomRefCache]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_getNomRefCache]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_getNomRefCacheStringForCategory1]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_getNomRefCacheStringForCategory1]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_getNomRefCacheStringForCategory2]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_getNomRefCacheStringForCategory2]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_replNullChar]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_replNullChar]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_replNullInt]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_replNullInt]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_replNullTrim]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_replNullTrim]
GO

if EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[f_core_getRefCache]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP function [dbo].[f_core_getRefCache]
GO




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

 



SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*	AuthorTeamString	*/
/*	Author: Hahn, Güntsch	*/
/*	Created: 5/2001		*/
/*	Last update: 9/2001	*/
/* Creates the authorteam string for the given authorteam ID. This function is used to calculate the authorteam cache field
WITH a trigger */
CREATE FUNCTION dbo.f_core_AuthorTeamString (@AuthorTeamId AS INTEGER) RETURNS NVARCHAR(255)  AS
BEGIN
	DECLARE @TempName AS NVARCHAR(255)
	DECLARE @OneName AS NVARCHAR(40)
	DECLARE @Reverted AS NVARCHAR(255)
	DECLARE @Pos AS INT
	DECLARE CursAut CURSOR FAST_FORWARD FOR
		SELECT Abbrev
		FROM AuthorTeamSequence, Author
		WHERE (AuthorId = AuthorFk) AND (AuthorTeamFk = @AuthorTeamId)
		ORDER BY Sequence
	SET @TempName = ''
	SET @OneName = ''
	OPEN CursAut
	FETCH  NEXT FROM CursAut INTO @OneName
	WHILE (@@FETCH_STATUS <> -1) BEGIN
		SET @TempName = @TempName + @OneName + ', '
		FETCH NEXT FROM CursAut INTO @OneName
	END
	CLOSE CursAut
	DEALLOCATE CursAut
	IF (@TempName <> '') BEGIN
		SET @TempName = LEFT(@TempName, LEN(@TempName)-1)
		SET @Reverted = REVERSE(@TempName)
		IF CHARINDEX(',', @TempName) > 1 BEGIN
			SET @POS = LEN(@Reverted) - CHARINDEX(',', @Reverted)
		END ELSE BEGIN
			SET @POS = 0
		END
		IF (@POS > 0) BEGIN
			SET @TempName = LEFT(@TempName, @Pos) + ' &' + SUBSTRING(@TempName, @Pos+2, LEN(@TempName) - @Pos)
		END
	END

	RETURN(@TempName)
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO


/*
used by the BGBMModelEditor
=============================================
AuthorTeamStringFull
=============================================
Author : Döring
Created: 3/2003
Updated: 3/2003
=============================================
Creates the FULL authorteam string FOR the given
authorteam ID. This function IS used TO
calculate the FULL authorteam cache field
WITH a trigger.
=============================================
*/
CREATE  FUNCTION dbo.f_core_AuthorTeamStringFull (@AuthorTeamId AS INTEGER) RETURNS NVARCHAR(255)  AS
BEGIN
	DECLARE @TempName AS NVARCHAR(255)
	DECLARE @OneName AS NVARCHAR(40)
	DECLARE @Abbrev AS NVARCHAR(40)
	DECLARE @Initials AS NVARCHAR(40)
	DECLARE @Lastname AS NVARCHAR(250)
	DECLARE @Reverted AS NVARCHAR(255)
	DECLARE @Pos AS INT
	DECLARE CursAut CURSOR LOCAL FAST_FORWARD FOR
		SELECT LastName, Initials, Abbrev
		FROM AuthorTeamSequence, Author
		WHERE (AuthorId = AuthorFk) AND (AuthorTeamFk = @AuthorTeamId)
		ORDER BY Sequence
	SET @TempName = ''
	SET @OneName = ''
	OPEN CursAut
	FETCH  NEXT FROM CursAut INTO @Lastname, @Initials, @Abbrev
	WHILE (@@FETCH_STATUS <> -1) BEGIN
		IF len(@Lastname) > 0 BEGIN
			IF len(@Initials) > 0 BEGIN
				SET @OneName = @Lastname + ',' + @Initials
			END ELSE BEGIN
				SET @OneName = @Lastname
			END
		END ELSE BEGIN
			SET @OneName = @Abbrev
		END
		SET @TempName = @TempName + @OneName + ', '
		FETCH NEXT FROM CursAut INTO @Lastname, @Initials, @Abbrev
	END
	CLOSE CursAut
	DEALLOCATE CursAut
	IF (@TempName <> '') BEGIN
		SET @TempName = LEFT(@TempName, LEN(@TempName)-1)
		SET @Reverted = REVERSE(@TempName)
		IF CHARINDEX(',', @TempName) > 1 BEGIN
			SET @POS = LEN(@Reverted) - CHARINDEX(',', @Reverted)
		END ELSE BEGIN
			SET @POS = 0
		END
		IF (@POS > 0) BEGIN
			SET @TempName = LEFT(@TempName, @Pos) + ' &' + SUBSTRING(@TempName, @Pos+2, LEN(@TempName) - @Pos)
		END
	END

	RETURN(@TempName)
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



/*
CHECK CASE sensitive whether the two given strings ARE equal. If so, return 1.
Return 0 otherwise
*/
CREATE FUNCTION [dbo].f_core_CompareStrings (@Str1 VARCHAR(255), @Str2 VARCHAR(255)) RETURNS INT
AS
BEGIN
	DECLARE @i AS INTEGER
	DECLARE @Result AS INTEGER
	IF (Len(@Str1) <> Len(@Str2))  BEGIN
 		SET @Result = 0
	END ELSE BEGIN
		SET @i =1
		SET @Result = 1
		WHILE ((@i <= Len(@Str1)) AND (@Result = 1)) BEGIN
			IF (Ascii(SUBSTRING(@Str1, @i, 1)) <> Ascii(SUBSTRING(@Str2, @i, 1))) BEGIN
				SET @Result = 0
			END ELSE BEGIN
				SET @i = @i + 1
			END
		END
	END
	RETURN @Result
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*	StrSpaceAdd		*/
/*	Author: A. Güntsch	*/
/*	Created: 5/2001		*/
/*	Last update: 9/2001	*/
/* Concatenate 2 Strings S1, S2 as S1 + ' ' + S2. But:
    NULL + NULL = NULL
    S1 + NULL = S1
    NULL + S2 = S2 */
CREATE FUNCTION dbo.f_core_StrSpaceAdd (@Str1 NVARCHAR(80), @Str2 NVARCHAR(80)) RETURNS NVARCHAR(160)
AS
BEGIN
  DECLARE @Result AS NVARCHAR(160)
  IF ((@Str1 IS NULL) AND (@Str2 IS NULL)) BEGIN
     SET @Result = NULL
  END
  IF ((@Str1 IS NOT NULL) AND (@Str2 IS NULL)) BEGIN
     SET @Result = @Str1
  END
  IF ((@Str1 IS NULL) AND (@Str2 IS NOT NULL)) BEGIN
     SET @Result = @Str2
  END
  IF ((@Str1 IS NOT NULL) AND (@Str2 IS NOT NULL)) BEGIN
     SET @Result = @Str1 + ' ' + @Str2
  END
  RETURN @Result
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



--Created Frank Specht
	--14.05.2003
--Updated Marc Geoffroy
	--13.10.2003
CREATE FUNCTION dbo.f_core_cumulateBookNomRefCache (@Authorteam AS nvarchar(100), @TitelAbbrev AS nvarchar(500), @Edition AS nvarchar(100), @Series AS nvarchar(100), @Volume AS nvarchar(100), @Refyear AS nvarchar(100), @prefixEdition varchar(10), @prefixSeries varchar(10), @prefixVolume varchar(10), @blank CHAR(1), @comma CHAR(1), @dot CHAR(1))
RETURNS NVARCHAR(2000) AS
BEGIN


	DECLARE @NomRefCache AS NVarchar(2000)
	DECLARE @LastCharIsDouble AS INTEGER
	DECLARE @Len AS INTEGER
	DECLARE @LastChar AS VARCHAR (255)
	DECLARE @CHAR AS VARCHAR (255)
	SET @CHAR = '.'
	SET @LEN = Len(@TitelAbbrev)
	SET @LastChar = SUBSTRING(@TitelAbbrev,@LEN-1,1)
	SET @LastCharIsDouble = dbo.f_core_CompareStrings(RIGHT(@TitelAbbrev,1),@CHAR)

	IF (@LastCharIsDouble = 1  AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0 ) SET @TitelAbbrev = SUBSTRING(@TitelAbbrev,1,@LEN-1)

--Edition and series are null or numeric


	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma +  @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev +  @blank + @prefixSeries+ @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) = 0  AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev +  @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev +  @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition+ @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries+ @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition + @blank + @Edition +  @comma +  @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition + @blank + @Edition +  @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma +  @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) = 0  AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma +  @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixVolume+ @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition+ @blank + @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixEdition + @blank + @Edition + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = NULL






--Edition and/or Series is not numeric

	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma +  @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev +  @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma +  @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma +  @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @comma + @blank + @Series + @comma +  @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @comma + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot

	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Edition +  @comma + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Edition +  @comma + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Edition +  @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Edition + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Edition +  @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Series + @dot




--Edition is numeric and series is not numeric
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition+ @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @comma +  @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixEdition + @blank + @Edition +  @comma + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixEdition + @blank + @Edition+  @comma + @blank + @Series + @dot



--Series is numeric and editon is not numeric
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=1 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @comma + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @TitelAbbrev + @blank + @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma +  @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @prefixVolume + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Edition +  @comma + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Edition) > 0 AND isnumeric(@Edition)=0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Edition+  @comma + @blank + @prefixSeries + @blank + @Series + @dot

--Changes (Marc Geoffroy)

	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0)  SET @NomRefCache = NULL


	Return @NomRefCache

END











GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



--Created Frank Specht
	--14.05.2003
--Updated Marc Geoffroy
	--04.03.20034
CREATE FUNCTION [dbo].[f_core_cumulateJournalNomRefCache] (@Authorteam AS nvarchar(100), @TitelAbbrev AS nvarchar(500), @Series AS nvarchar(100), @Volume AS nvarchar(100), @Refyear AS nvarchar(100), @prefixSeries varchar(10), @prefixReferenceJounal varchar(10), @blank CHAR(1), @comma CHAR(1), @dot CHAR(1))
RETURNS NVARCHAR(2000) AS
BEGIN

	DECLARE @NomRefCache AS NVarchar(2000)



--Journal
--Series is numeric
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam  +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) = 0  AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @dot +  @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev +  @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @prefixSeries + @blank + @Series + @comma + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @prefixSeries + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=1 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @prefixSeries + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank+ @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) = 0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank+ @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) = 0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = NULL


--series is not numeric
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Series + @comma + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam  +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Series + @comma + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Series + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @Series + @comma + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @Series + @comma + @blank + @Volume + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) > 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @Authorteam +@blank +  @prefixReferenceJounal + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Series + @comma + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Series + @comma + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Series + @dot +  @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) > 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @TitelAbbrev + @blank + @Series + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @Series + @comma + @blank + @Volume + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) > 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @Series + @comma + @blank + @Volume + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) > 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @Series + @dot + @blank + @Refyear + @dot
	IF (len(@Authorteam) = 0 AND len(@TitelAbbrev) = 0 AND len(@Series) > 0 AND isnumeric(@Series)=0 AND len(@Volume) = 0 AND len(@RefYear) = 0) SET @NomRefCache = @prefixReferenceJounal + @blank + @Series + @dot


--Changes (Marc Geoffroy)

	SET @NomRefCache = REPLACE (@NomRefCache, '..', '.')


	Return @NomRefCache

END








GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



--create by Frank Specht
--7.07.2003
CREATE FUNCTION [dbo].[f_core_getAuthorTeam] (@AuthorTeamFk AS INT)
RETURNS NVARCHAR(500) AS
BEGIN
	DECLARE @AuthorTeam  AS NVarchar(500)
		SELECT
			@AuthorTeam = AuthorTeamCache
		FROM AuthorTeam
		WHERE AuthorTeamId=@AuthorTeamFk

		SET @AuthorTeam = @AuthorTeam
	RETURN @AuthorTeam
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



--updated from Frank Specht 16.05.03
--updated from M. Geoffroy 06.01.04
CREATE  FUNCTION dbo.f_core_getFullNomRefCache (@RefDetailId AS INT) RETURNS NVARCHAR(2000)
AS
BEGIN
	DECLARE @RefFk INT
	DECLARE @InRefFk INT
	DECLARE @RefYear nvarchar(255)
	DECLARE @AuthorteamFk INT
	DECLARE @lenAuthorteam INT
	DECLARE @Details nvarchar(255)
	DECLARE @RefCategoryFk INT
	DECLARE @NomRefCache nvarchar(2000)
	DECLARE @FullNomRefCache nvarchar(2000)
	DECLARE @LastCharIsDouble AS INTEGER
	DECLARE @Len AS INTEGER
	DECLARE @LastChar AS VARCHAR (255)
	DECLARE @CHAR AS VARCHAR (255)
	DECLARE @LenRefYear AS INT
	DECLARE @NomTitleAbbrev nvarchar ( 500)
	DECLARE @NomAuthorTeamFk INT
	DECLARE @Edition nvarchar ( 50 )
	DECLARE @Volume nvarchar ( 20 )
	DECLARE @Series nvarchar ( 20 )
	DECLARE @Authorteam nvarchar(255)

	DECLARE @prefixEdition varchar(10)
	DECLARE @prefixSeries varchar(10)
	DECLARE @prefixVolume varchar(10)
	DECLARE @blank CHAR(1)
	DECLARE @comma CHAR(1)
	DECLARE @dot CHAR(1)
	DECLARE @prefixReference varchar(10)
	DECLARE @prefixReferenceJounal varchar(10)
	--Declare @subformat nvarchar(2000)
		SET @prefixEdition = 'ed.'
		SET @prefixSeries = 'ser.'
		SET @prefixVolume = 'vol.'
		SET @blank = ' '
		SET @comma =','
		SET @dot = '.'
		SET @prefixReference = 'in'
		SET @prefixReferenceJounal = 'in'
-- =============================================================================
-- Get the affected RefDetailId and the Reference it is attached to
-- =============================================================================
	SELECT @RefFk=RefFk, @Details=dbo.f_core_replNullTrim(Details) FROM RefDetail WHERE RefDetailId=@RefDetailId


-- =============================================================================
-- Get the data in Reference needed for Construction of FullNomRefCache
-- =============================================================================

	SELECT  @RefCategoryFk=RefCategoryFk,
		@InRefFk=InRefFk,
		@NomRefCache=NomRefCache,
		@NomTitleAbbrev=dbo.f_core_replNullTrim(NomTitleAbbrev),
		@NomAuthorTeamFk=NomAuthorTeamFk,
		@Edition=	dbo.f_core_replNullTrim(Edition),
		@Volume=	dbo.f_core_replNullTrim(Volume),
		@Series=	dbo.f_core_replNullTrim(Series),
		@RefYear=	dbo.f_core_replNullTrim(RefYear)
	FROM Reference WHERE RefId=@RefFk


--===============================================================================
-- select dot in details, fs
--===============================================================================
	SET @LEN = Len(@Details)
	IF @LEN = 0 BEGIN
		SET @FullNomRefCache= @NomRefCache
	END ELSE BEGIN

		SET @CHAR = '.'
		SET @LastChar = SUBSTRING(@Details,@LEN-1,1)
		SET @LastCharIsDouble = dbo.f_core_CompareStrings(RIGHT(@Details,1),@CHAR)

		IF @LastCharIsDouble = 1  BEGIN
			SET @Details = SUBSTRING(@Details,1,@LEN-1)
		END


		SET @LenRefYear = Len(@RefYear)

		If @LenRefYear =  0 BEGIN
			SET @FullNomRefCache= @NomRefCache + @blank + @Details + @dot
		END ELSE



		If @RefCategoryFk in (3, 10) BEGIN --book, fs or unresolved
			SELECT @AuthorteamFk=NomAuthorteamFk FROM Reference WHERE RefId=@RefFk
			SELECT @LenAuthorteam= (len(dbo.f_core_replNullTrim(AuthorteamCache))) FROM Authorteam WHERE AuthorteamId= @AuthorteamFk
			SET @LenAuthorteam = @LenAuthorteam + 2

				If len(@Volume) > 0 BEGIN
				SET @NomRefCache=RIGHT(@NomRefCache,len(@NomRefCache)-@LenAuthorteam)
				SET @NomRefCache=LEFT(@NomRefCache,len(@NomRefCache)-(len(@RefYear)+3))
				SET @FullNomRefCache= @NomRefCache + @comma + @blank + @Details + @dot  + @blank + @RefYear + @dot
				END ELSE If len(@Series) > 0 BEGIN

					SET @NomRefCache=RIGHT(@NomRefCache,len(@NomRefCache)-@LenAuthorteam)
					SET @NomRefCache=LEFT(@NomRefCache,len(@NomRefCache)-(len(@RefYear)+3))
					SET @FullNomRefCache= @NomRefCache + @comma + @blank + @Details + @dot  + @blank + @RefYear + @dot


				END ELSE If len(@Edition) > 0 BEGIN

					SET @NomRefCache=RIGHT(@NomRefCache,len(@NomRefCache)-@LenAuthorteam)
					SET @NomRefCache=LEFT(@NomRefCache,len(@NomRefCache)-(len(@RefYear)+3))
					SET @FullNomRefCache= @NomRefCache + @comma + @blank + @Details + @dot  + @blank + @RefYear + @dot

				END ELSE If len(@NomTitleAbbrev) > 0 BEGIN
					SET @LastChar = SUBSTRING(@NomTitleAbbrev,len(@NomTitleAbbrev)-1,1)
					SET @LastCharIsDouble = dbo.f_core_CompareStrings(RIGHT(@NomTitleAbbrev,1),@dot)

					IF @LastCharIsDouble = 1  BEGIN
						SET @NomRefCache=LEFT(@NomRefCache,len(@NomRefCache)-(len(@RefYear)+2))
						SET @NomRefCache=RIGHT(@NomRefCache,len(@NomRefCache)-@LenAuthorteam)
						SET @FullNomRefCache= dbo.f_core_replNullTrim(@NomRefCache) + @comma + @blank + @Details + @dot  + @blank + @RefYear + @dot
					END ELSE BEGIN
						SET @NomRefCache=RIGHT(@NomRefCache,len(@NomRefCache)-@LenAuthorteam)
						SET @NomRefCache=LEFT(@NomRefCache,len(@NomRefCache)-(len(@RefYear)+3))
						SET @FullNomRefCache= dbo.f_core_replNullTrim(@NomRefCache)
						SET @FullNomRefCache= @FullNomRefCache  + @comma + @blank + @Details + @dot  + @blank + @RefYear + @dot
				END
			END

		END


		If @RefCategoryFk < 3 BEGIN -- get the appropriate authorteam and cut it of -- if it is not a journal
			SELECT @AuthorteamFk=NomAuthorteamFk FROM Reference WHERE RefId=@RefFk
			SELECT @LenAuthorteam= len(dbo.f_core_replNullTrim(AuthorteamCache)) FROM Authorteam WHERE AuthorteamId= @AuthorteamFk
		END

-- if it is an In-Citation get the year of the publication it is attached to
		If @RefCategoryFk = 2  BEGIN -- part of title authorteam and year
		SELECT @RefYear=dbo.f_core_replNullTrim(RefYear) FROM Reference WHERE RefId=@InRefFk
		END

		 If @RefCategoryFk = 2 BEGIN --part of title, fs- it is a journal

			SET @NomRefCache=LEFT(@NomRefCache,len(@NomRefCache)-(len(@RefYear)+3))
			SET @NomRefCache=RIGHT(@NomRefCache,len(@NomRefCache)-@LenAuthorteam)
			SET @FullNomRefCache= dbo.f_core_replNullTrim(@NomRefCache) + ', ' + @Details + '.'  + ' ' + @RefYear + '.'
		END
		 If @RefCategoryFk = 1 BEGIN -- periodical, fs
			SET @NomRefCache=LEFT(@NomRefCache,len(@NomRefCache)-(len(@RefYear)+3))
			SET @NomRefCache=RIGHT(@NomRefCache,len(@NomRefCache)-@LenAuthorteam)
			SET @FullNomRefCache= dbo.f_core_replNullTrim(@NomRefCache) + ': ' + @Details + '.'  + ' ' + @RefYear + '.'
		END
		 If @RefCategoryFk = 9 BEGIN -- if it is a journal, fs
			SET @FullNomRefCache = @NomRefCache
		END

	END

	RETURN @FullNomRefCache
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*
=============================================
	getFullRefCache Function
=============================================
	Author: Markus Döring
	Created: 01/2003
	Updated: 01/2003
=============================================
Builds the FullRefCache String FOR a given RefDetailId.
FOR the TIME being it ONLY appends the Details TO the RefCache.
=============================================
*/
CREATE      FUNCTION dbo.f_core_getFullRefCache (@RefDetId AS INT) RETURNS NVARCHAR(1000)
AS
BEGIN
	DECLARE @Cache AS NVARCHAR(1000)
	DECLARE @RefCache AS NVARCHAR(1000)
	DECLARE @Details AS NVARCHAR(100)
	SELECT @RefCache = RefCache FROM Reference, RefDetail
		WHERE RefId = RefFk AND RefDetailId = @RefDetId
	IF len(ltrim(rtrim(@RefCache))) = 0 BEGIN
		SET @Cache = NULL
	END	ELSE BEGIN
		SELECT @Details = dbo.f_core_replNullChar(Details) FROM RefDetail WHERE RefDetailId = @RefDetId
		SET @Cache = @RefCache + @Details
	END
	RETURN @Cache
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



-------------------------------------
--created by F. Specht 7.07.2003
--updated by M. Geoffroy 06.01.2004
---------------------------------------
CREATE   FUNCTION dbo.f_core_getNomRefCache (@RefId AS INT) RETURNS NVARCHAR(2000)
AS
BEGIN
DECLARE @NomRefcache nvarchar(2000)
DECLARE @RefCategoryFk INT
DECLARE @InRefFk INT
DECLARE @NomTitleAbbrev nvarchar ( 500)
DECLARE @NomAuthorTeamFk INT
DECLARE @Edition nvarchar ( 50 )
DECLARE @Volume nvarchar ( 20 )
DECLARE @Series nvarchar ( 20 )
DECLARE @RefYear nvarchar ( 40 )
DECLARE @Authorteam nvarchar(255)

DECLARE @prefixEdition varchar(10)
DECLARE @prefixSeries varchar(10)
DECLARE @prefixVolume varchar(10)
DECLARE @blank CHAR(1)
DECLARE @comma CHAR(1)
DECLARE @dot CHAR(1)
DECLARE @prefixReference varchar(10)
DECLARE @prefixReferenceJounal varchar(10)
	SET @prefixEdition = 'ed.'
	SET @prefixSeries = 'ser.'
	SET @prefixVolume = 'vol.'
	SET @blank = ' '
	SET @comma =','
	SET @dot = '.'
	SET @prefixReference = 'in'
	SET @prefixReferenceJounal = 'in'


 SELECT @RefCategoryFk=RefCategoryFk, -- Get the Reference category
		@InRefFk=InRefFk,
		@NomTitleAbbrev=dbo.f_core_replNullTrim(NomTitleAbbrev) ,
		@NomAuthorTeamFk=NomAuthorTeamFk,
		@Edition=	dbo.f_core_replNullTrim(Edition),
		@Volume=	dbo.f_core_replNullTrim(Volume),
		@Series=	dbo.f_core_replNullTrim(Series),
		@RefYear=	dbo.f_core_replNullTrim(RefYear)
 FROM   Reference
 WHERE  RefId =@RefId
	if @NomAuthorTeamFk IS NULL OR @NomAuthorTeamFk = 0
	BEGIN
		SET @AuthorTeam = ""
	END ELSE BEGIN

		SET @AuthorTeam = dbo.f_core_getAuthorTeam(@NomAuthorTeamFk)
	END
-- ==============================================================================
-- Concatenate the NomRefCache
-- ==============================================================================



			If @RefCategoryFk in (3, 10) BEGIN -- book or unresolved
			SET @NomRefCache = dbo.f_core_cumulateBookNomRefCache(@AuthorTeam, @NomTitleAbbrev, @Edition, @Series, @Volume, @RefYear, @prefixEdition, @prefixSeries, @prefixVolume, @blank, @comma, @dot)
			END ELSE If @RefCategoryFk = 9 BEGIN -- journal
			SET @NomRefCache = @NomTitleAbbrev
			END ELSE If @RefCategoryFk = 1 BEGIN --article in periodical
			SET @NomTitleAbbrev = dbo.f_core_getNomRefCacheStringForCategory1(@InRefFk)
			SET @NomRefCache = dbo.f_core_cumulateJournalNomRefCache(@AuthorTeam, @NomTitleAbbrev, @Series, @Volume, @RefYear, @prefixSeries, @prefixReferenceJounal, @blank, @comma, @dot)
			END ELSE If @RefCategoryFk = 2 BEGIN -- part of title
			SET @NomRefCache = @AuthorTeam + @blank + @prefixReference + @blank + dbo.f_core_getNomRefCacheStringForCategory2(@InRefFk)
			END  ELSE If @RefCategoryFk IS NULL OR @RefCategoryFk BETWEEN 4 AND  8 BEGIN --no nomenclatur citation
			SET @NomRefCache = NULL
                		END
-- ==============================================================================
-- Return the NomRefCache of this record to the trigger
-- ==============================================================================
Return @NomRefCache
-- =============================================================================
END










GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



--create from Frank Specht
--7.07.2003
-- =========================================================================
-- Get the data in a two level for books or three level hierarchy for Journal (this record)
-- =========================================================================
CREATE   FUNCTION dbo.f_core_getNomRefCacheStringForCategory1 (@RefId AS INT) RETURNS NVARCHAR(2000)
AS
BEGIN
DECLARE @NomRefCache nvarchar(2000)
DECLARE @RefCategoryFk INT
DECLARE @NomRefcache1 nvarchar(2000)
 SELECT @RefCategoryFk=RefCategoryFk, -- Get the Reference category
	  @NomRefcache1 = NomRefCache
 FROM   Reference
 WHERE  RefId =@RefId

		If @RefCategoryFk = 9 BEGIN
			-- journal
			SET @NomRefCache =   @NomRefcache1
		END
-- ==============================================================================
-- Return the NomRefCache of this record to the trigger
-- ==============================================================================
Return @NomRefCache
-- =============================================================================
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



--create from Frank Specht
--7.07.2003
-- =========================================================================
-- Get the data in a two level for books or three level hierarchy for Journal (this record)
-- =========================================================================
CREATE   FUNCTION dbo.f_core_getNomRefCacheStringForCategory2 (@RefId INT) RETURNS NVARCHAR(2000)
AS
BEGIN
DECLARE @NomRefCache nvarchar(2000)
DECLARE @RefCategoryFk INT
DECLARE @NomRefcache1 nvarchar(2000)
DECLARE @Authorteam  nvarchar(500)
DECLARE @NomAuthorteamFk INT
DECLARE @CutLenAutorteam  INT
 SELECT @RefCategoryFk=RefCategoryFk, -- Get the Reference category
	@NomRefcache1 = NomRefCache,
	@NomAuthorteamFk = NomAuthorTeamFk
 FROM   Reference
 WHERE  RefId =@RefId

		If  @NomAuthorteamFk IS NULL  OR @NomAuthorteamFk = 0 BEGIN
			SET @CutLenAutorteam = 3
		END ELSE BEGIN
			SET @Authorteam = dbo.f_core_getAuthorTeam(@NomAuthorteamFk)
			SET @CutLenAutorteam = len(@Authorteam) + 4
		END
		If @RefCategoryFk = 3 BEGIN
			-- book
			SET @NomRefCache =   @NomRefcache1
		END
			-- journal
		If @RefCategoryFk = 1 AND @CutLenAutorteam = 3 BEGIN
			SET @NomRefCache =  RIGHT(@NomRefcache1, len(@NomRefcache1)-@CutLenAutorteam)
		END
		If @RefCategoryFk = 1 AND @CutLenAutorteam > 3 BEGIN
			SET @NomRefCache =  @Authorteam + ',' + ' ' + RIGHT(@NomRefcache1, len(@NomRefcache1)-@CutLenAutorteam)
		END
-- ==============================================================================
-- Return the NomRefCache of this record to the trigger
-- ==============================================================================
Return @NomRefCache
-- =============================================================================
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


CREATE FUNCTION dbo.f_core_replNullChar (@Variable nvarchar(255))
RETURNS nvarchar(255) AS
BEGIN
if (@Variable IS NULL) SET @Variable='' ELSE SET @Variable = @Variable
return @Variable
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO


/*
=============================================
	replNullInt Function
=============================================
	Author: Markus Döring
	Created: 01/2003
	Updated: 01/2003
=============================================
Returns 0 if the passed parameter IS NULL OR 0.
Otherwise returns original VALUE.
=============================================
*/
CREATE   FUNCTION dbo.f_core_replNullInt (@Variable INT)
RETURNS INT AS
BEGIN
if (@Variable IS NULL) SET @Variable=0 ELSE SET @Variable = @Variable
return @Variable
END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



/****** Object:  User Defined Function dbo.f_core_replNull    Script Date: 23.01.2003 18:53:05 ******/
CREATE    FUNCTION dbo.f_core_replNullTrim (@Variable nvarchar(1000))
RETURNS nvarchar(1000) AS
BEGIN
	if (@Variable IS NULL) SET @Variable=''
	ELSE SET @Variable = rtrim(ltrim(@Variable))
return @Variable
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO




SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO



/*
used by the BGBMModelEditor
=============================================
getRefCache
=============================================
Author : Markus Döring
Created: 01/2003
Updated: 03/2003
=============================================
Builds the RefCache String FOR a given RefId.
FOR IN-Citations it uses the parental RefCache.
Series AND Editions ARE prefixed by Ser. & Ed. respectively.
A non valid reference will result IN a NULL cache.
FOR a valid reference the following IS needed:
ARTICLE:AuthorString & journal RefCache
BOOK:	AuthorString & Title
JOURNAL:	Title
PART OF:AuthorString & parental RefCache
INFORMAL:	AuthorString OR Title
DATABASE:	Title
WEBSITE:	Title
CD:			Title
UNRESOLVED: Title
=============================================
*/
CREATE  FUNCTION dbo.f_core_getRefCache (@RefId AS INT) RETURNS NVARCHAR(1000)
AS
BEGIN
	DECLARE @Cache AS NVARCHAR(1000)
	DECLARE @InRefCache AS NVARCHAR(1000)
	DECLARE @Title AS NVARCHAR(1000)
	DECLARE @Author AS NVARCHAR(1000)
	DECLARE @YEAR AS NVARCHAR(1000)
	DECLARE @Edition AS NVARCHAR(1000)
	DECLARE @Series AS NVARCHAR(1000)
	DECLARE @Volume AS NVARCHAR(1000)
	DECLARE @Pagestring AS NVARCHAR(100)
	DECLARE @Publisher AS NVARCHAR(100)
	DECLARE @Town AS NVARCHAR(100)
	DECLARE @URL AS NVARCHAR(100)
	DECLARE @Datestring AS NVARCHAR(100)
	DECLARE @tmp AS NVARCHAR(100)
	DECLARE @RefCatFk AS INT
	-- get data from this record
	SELECT @Title = dbo.f_core_replNullTrim(Title),
		@Author = dbo.f_core_replNullTrim(RefAuthorString),
		@Edition = dbo.f_core_replNullTrim(Edition),
		@Series = dbo.f_core_replNullTrim(Series),
		@Volume = dbo.f_core_replNullTrim(Volume),
		@YEAR = dbo.f_core_replNullTrim(RefYear),
		@Datestring = dbo.f_core_replNullTrim(DateString),
		@Pagestring = dbo.f_core_replNullTrim(PageString),
		@Publisher = dbo.f_core_replNullTrim(Publisher),
		@Town = dbo.f_core_replNullTrim(PublicationTown),
		@URL = dbo.f_core_replNullTrim(URL),
		@RefCatFk = RefCategoryFk
		FROM Reference
		WHERE RefId = @RefId
	-- add linking chars and prefixes
	IF len(@Edition) > 0 SET @Edition = ', Ed. ' +@Edition
	IF len(@Series) > 0 SET @Series = ', Ser. ' +@Series
	IF len(@Volume) > 0 SET @Volume = ' ' +@Volume
	IF len(@Pagestring) > 0 SET @Pagestring = ': ' +@Pagestring
	IF len(@Datestring) > 0 SET @Datestring = ', ' +@Datestring
	IF len(@Publisher) > 0 SET @Publisher = ' ' +@Publisher
	IF len(@Town) > 0 SET @Town = ' ' +@Town
	-- BUILD CACHE STRINGS
	IF @RefCatFk = 9 BEGIN
		-- a journal
		SET @Cache = @Title +@Publisher +@Town +@Datestring
		IF len(@Cache) < 1 SET @Cache = NULL
	END ELSE IF @RefCatFk = 3 BEGIN
		-- a book
		IF len(@Author) > 0 AND len(@Title) > 0 BEGIN
			IF len(@YEAR) > 0 SET @YEAR = ' ' +@YEAR
			SET @Cache = @Author +' - ' +@Title +@Edition +@Series +@Volume +@Publisher +@Town +@YEAR +@Pagestring +'.'
		END ELSE BEGIN
			-- no title and/or author
			SET @Cache = NULL
		END
	END ELSE IF @RefCatFk = 10 BEGIN
		-- unresolved
		IF len(@Title) > 0 BEGIN
			IF len(@YEAR) > 0 SET @YEAR = ' ' +@YEAR
			SET @Cache = @Author +' - ' +@Title +@Edition +@Series +@Volume +@Publisher +@Town +@YEAR +@Pagestring +'.'
		END ELSE BEGIN
			-- no title and/or author
			SET @Cache = NULL
		END
	END ELSE IF @RefCatFk = 4 BEGIN
		-- a database
		IF len(@Title) > 0 BEGIN
			IF len(@YEAR) > 0 SET @YEAR = ' ' +@YEAR
			IF len(@Author) > 0 SET @Author = @Author +' - '
			IF len(@URL) > 0 SET @URL = ' - ' +@URL
			SET @Cache = @Author +@Title +@Edition +@URL +@YEAR +@Datestring +@Pagestring +'.'
		END ELSE BEGIN
			-- no title and author
			SET @Cache = NULL
		END
	END ELSE IF @RefCatFk = 5 BEGIN
		-- informal reference
		IF len(@Author) > 0 OR len(@Title) > 0 BEGIN
			IF len(@Author) > 0 AND len(@Title) > 0 BEGIN
				IF len(@Title) > 0 SET @Title = ' - ' +@Title
			END
			IF len(@YEAR) > 0 SET @YEAR = ' ' +@YEAR
			SET @Cache = @Author +@Title +@YEAR +@Datestring +@Pagestring +'.'

		END ELSE BEGIN
			-- no title nor author
			SET @Cache = NULL
		END
	END ELSE IF @RefCatFk = 7 BEGIN
		-- website
		IF len(@Title) > 0 BEGIN
			IF len(@YEAR) > 0 SET @YEAR = ' ' +@YEAR
			IF len(@Author) > 0 SET @Author = @Author +' - '
			IF len(@URL) > 0 SET @URL = ' - ' +@URL
			SET @Cache = @Author +@Title +@Edition +@URL +@YEAR +@Datestring +@Pagestring +'.'
		END ELSE BEGIN
			-- no title and author
			SET @Cache = NULL
		END
	END ELSE IF @RefCatFk = 8 BEGIN
		-- a CD ROM
		IF len(@Title) > 0 BEGIN
			IF len(@YEAR) > 0 SET @YEAR = ' ' +@YEAR
			IF len(@Author) > 0 SET @Author = @Author +' - '
			IF len(@URL) > 0 SET @URL = ' - ' +@URL
			SET @Cache = @Author +@Title +@Edition +@URL +@YEAR +@Datestring +@Pagestring +'.'
		END ELSE BEGIN
			-- no title and author
			SET @Cache = NULL
		END
	END ELSE BEGIN
		-- categories that need data from their higher parent record.
		SELECT @InRefCache = dbo.f_core_replNullTrim(r2.RefCache)
			FROM  Reference AS r1, Reference AS r2
			WHERE r1.RefId = @RefId
			AND r2.RefId = r1.InRefFk
		IF len(@YEAR) > 0 SET @YEAR = ' (' +@YEAR +')'
		-- construct cache string for those categories.
		IF @RefCatFk = 2 BEGIN
			-- In Citation !!!
			IF len(@Author) > 0 AND len(@InRefCache) > 0 BEGIN
				IF len(@Title) > 0 BEGIN
					SET @Title = @Title +' - In: '
					SET @tmp = ': '
				END  ELSE  BEGIN
					SET @tmp = ' In: '
				END
				SET @Cache = @Author +@YEAR +@tmp +@Title +  @InRefCache
			END ELSE BEGIN
				SET @Cache = NULL
			END
		END ELSE IF @RefCatFk = 1 BEGIN
			-- an article
			IF len(@Author) > 0 AND len(@InRefCache) > 0 BEGIN
				IF len(@Title) > 0 SET @Title = @Title +' - '
				SET @Cache = @Author +@YEAR +' - ' +@Title +@InRefCache +@Edition +@Volume +@Publisher +@Town +@Pagestring +'.'
			END ELSE BEGIN
				SET @Cache = NULL
			END
		END ELSE BEGIN
			-- unknown reference type
			SET @Cache = NULL
		END
	END
	RETURN @Cache
END




GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
