--use DiversityCollection

IF (SELECT DBO.VERSION()) <> '02.05.00'
BEGIN
RAISERROR ('WRONG VERION. Script is scheduled as update for version 02.05.00', 18, 1) 
END
GO

--#####################################################################################################################
-- Removing redundant objects -----------------------------------------------------------------------------------------
--#####################################################################################################################


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExpeditionTopID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ExpeditionTopID]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[wbCurrentUserID]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[wbCurrentUserID]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NextAccessionNumber]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[NextAccessionNumber]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NextAccNr]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[NextAccNr]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExpeditionChildNodes]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ExpeditionChildNodes]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExpeditionHierarchy]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ExpeditionHierarchy]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ExpeditionSuperiorHierarchy]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[ExpeditionSuperiorHierarchy]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GeographicalNameList_Africa]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[GeographicalNameList_Africa]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Label_Main20]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[Label_Main20]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoanChildNodes]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[LoanChildNodes]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[LoanHierarchy]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[LoanHierarchy]

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xx_CuratorCollectionHierarchyList]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[xx_CuratorCollectionHierarchyList]



--#####################################################################################################################
-- New Function VersionClient -------------------------------------------------------------------------------------
--#####################################################################################################################

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VersionClient]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'ALTER FUNCTION [dbo].[VersionClient] ()  
RETURNS nvarchar(11)
AS
BEGIN
RETURN ''02.05.05.00''
END
' 
END
ELSE
BEGIN
execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[VersionClient] ()  
RETURNS nvarchar(11)
AS
BEGIN
RETURN ''02.05.05.00''
END
' 
END
GO
GRANT EXECUTE ON [dbo].[VersionClient] TO [DiversityCollectionUser]
GO




--#####################################################################################################################
/* Update Function Version -------------------------------------------------------------------------------------*/
--#####################################################################################################################

/****** Objekt:  UserDefinedFunction [dbo].[Version]    Skriptdatum: 08/12/2008 10:35:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Version]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'ALTER FUNCTION [dbo].[Version] ()  
RETURNS nvarchar(8)
AS
BEGIN
RETURN ''02.05.01''
END
' 
END
ELSE
BEGIN
	execute dbo.sp_executesql @statement = N'CREATE FUNCTION [dbo].[Version] ()  
	RETURNS nvarchar(8)
	AS
	BEGIN
	RETURN ''02.05.01''
	END
	' 
END
GO
GRANT EXECUTE ON [dbo].[Version] TO [DiversityCollectionUser]
GO

--#####################################################################################################################

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ManagerCollectionList] ()  
RETURNS @CollectionList TABLE ([CollectionID] [int] Primary key ,
	[CollectionParentID] [int] NULL ,
	[CollectionName] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[CollectionAcronym] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
	[AdministrativeContactName] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
	[AdministrativeContactAgentURI] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
	[Description] [nvarchar] (500) COLLATE Latin1_General_CI_AS NULL ,
	[Location] [nvarchar] (1000) COLLATE Latin1_General_CI_AS NULL ,
	[CollectionOwner]  [nvarchar]  (255) COLLATE Latin1_General_CI_AS NULL ,
	[DisplayOrder] [varchar] (255) COLLATE Latin1_General_CI_AS NULL)

/*
Returns a table that lists all the collections a Manager has access to, including the child collections.
MW 02.08.2008

Test:
select * from dbo.ManagerCollectionList()
*/
AS
BEGIN
	-- Filling the AdmininstratingCollections in the temp list
	DECLARE @CollectionID INT
	DECLARE @TempAdminCollectionID TABLE (CollectionID int primary key)
	DECLARE @TempCollectionID TABLE (CollectionID int primary key)
	INSERT @TempAdminCollectionID (CollectionID) 
		SELECT AdministratingCollectionID FROM CollectionManager WHERE (LoginName = USER_NAME()) 
	INSERT @TempCollectionID (CollectionID) 
		SELECT AdministratingCollectionID FROM CollectionManager WHERE (LoginName = USER_NAME()) 

	DECLARE HierarchyCursor  CURSOR for
	select CollectionID from @TempAdminCollectionID
	open HierarchyCursor
	FETCH next from HierarchyCursor into @CollectionID
	WHILE @@FETCH_STATUS = 0
	BEGIN
		insert into @TempCollectionID select CollectionID 
		from dbo.CollectionChildNodes (@CollectionID) where @CollectionID not in (select CollectionID from @TempCollectionID)
		FETCH NEXT FROM HierarchyCursor into @CollectionID
	END
	CLOSE HierarchyCursor
	DEALLOCATE HierarchyCursor

	-- copy the child nodes into the result list
	INSERT @CollectionList
	SELECT CollectionID, CollectionParentID, CollectionName, CollectionAcronym, AdministrativeContactName, AdministrativeContactAgentURI, Description, Location, CollectionOwner, DisplayOrder FROM dbo.Collection
	WHERE CollectionID in (SELECT CollectionID FROM @TempCollectionID)
	

	RETURN
END
GO

--#####################################################################################################################
-- Korrektur Beschreibung
--#####################################################################################################################


EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'Refers to the IdentificationSequence in Identification (= foreign key). The name under which the collection specimen resp. this unit is published within an exsiccata.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'IdentificationUnit', @level2type=N'COLUMN',@level2name=N'ExsiccataIdentification'
GO

EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'Number of stock units if the specimen is stored in separated units e.g. several boxes or vessels (max. 255)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionSpecimenPart', @level2type=N'COLUMN',@level2name=N'Stock'
GO

EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=N'The duration of the processing including the unit (e.g. 5 min) or the end of the processing starting at the processingDate (e.g. 23.05.2008)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionSpecimenProcessing', @level2type=N'COLUMN',@level2name=N'ProcessingDuration'
GO


GRANT SELECT ON [dbo].[ManagerCollectionList] TO [DiversityCollectionManager]
GO
GRANT SELECT ON [dbo].[RequesterCollectionList] TO [DiversityCollectionRequester]
GO
GRANT SELECT ON [dbo].[CollectionSpecimenTransactionRequest] TO [DiversityCollectionRequester]
GO



--#####################################################################################################################
-- New views for GridMode --------------------------------------------------------------------------------------
--#####################################################################################################################
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesAltitude]'))
DROP VIEW [dbo].[FirstLinesAltitude]
GO



CREATE VIEW [dbo].[FirstLinesAltitude]
AS
SELECT     CollectionEventID, LocalisationSystemID, Location1, Location2, LocationAccuracy, LocationNotes, DeterminationDate, DistanceToLocation, 
                      DirectionToLocation, ResponsibleName, ResponsibleAgentURI, AverageAltitudeCache, AverageLatitudeCache, AverageLongitudeCache
FROM         dbo.CollectionEventLocalisation
WHERE     (LocalisationSystemID = 4)
GO

GRANT SELECT ON [dbo].[FirstLinesAltitude] TO [DiversityCollectionUser]
GO


--#####################################################################################################################
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesChronostratigraphy]'))
DROP VIEW [dbo].[FirstLinesChronostratigraphy]
GO



CREATE VIEW [dbo].[FirstLinesChronostratigraphy]
AS
SELECT     CollectionEventID, PropertyID, DisplayText, PropertyURI, PropertyHierarchyCache, PropertyValue, ResponsibleName, ResponsibleAgentURI, Notes, 
                      AverageValueCache
FROM         dbo.CollectionEventProperty
WHERE     (PropertyID = 20)
GO

GRANT SELECT ON [dbo].[FirstLinesChronostratigraphy] TO [DiversityCollectionUser]
GO



--#####################################################################################################################
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesCollectionAgent]'))
DROP VIEW [dbo].[FirstLinesCollectionAgent]
GO



CREATE VIEW [dbo].[FirstLinesCollectionAgent]
AS
SELECT     CollectionSpecimenID, CollectorsName, CollectorsAgentURI, CollectorsSequence, CollectorsNumber, Notes, DataWithholdingReason
FROM         dbo.CollectionAgent AS Agent
WHERE     EXISTS
                          (SELECT     CollectionSpecimenID
                            FROM          dbo.CollectionAgent AS A
                            GROUP BY CollectionSpecimenID
                            HAVING      (Agent.CollectionSpecimenID = CollectionSpecimenID) AND (MIN(CollectorsName) = Agent.CollectorsName))
GO

GRANT SELECT ON [dbo].[FirstLinesCollectionAgent] TO [DiversityCollectionUser]
GO



--#####################################################################################################################
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesCollectionSpecimenPart]'))
DROP VIEW [dbo].[FirstLinesCollectionSpecimenPart]
GO



CREATE VIEW [dbo].[FirstLinesCollectionSpecimenPart]
AS
SELECT     CollectionSpecimenID, SpecimenPartID, DerivedFromSpecimenPartID, PreparationMethod, PreparationDate, AccessionNumber, PartSublabel, 
                      CollectionID, MaterialCategory, StorageLocation, Stock, Notes
FROM         dbo.CollectionSpecimenPart
WHERE     EXISTS
                          (SELECT     CollectionSpecimenID
                            FROM          dbo.CollectionSpecimenPart AS P
                            GROUP BY CollectionSpecimenID
                            HAVING      (CollectionSpecimenID = dbo.CollectionSpecimenPart.CollectionSpecimenID) AND (MIN(SpecimenPartID) 
                                                   = dbo.CollectionSpecimenPart.SpecimenPartID))
GO

GRANT SELECT ON [dbo].[FirstLinesCollectionSpecimenPart] TO [DiversityCollectionUser]
GO


--#####################################################################################################################
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesCoordinatesWGS84]'))
DROP VIEW [dbo].[FirstLinesCoordinatesWGS84]
GO


CREATE VIEW [dbo].[FirstLinesCoordinatesWGS84]
AS
SELECT     CollectionEventID, LocalisationSystemID, Location1, Location2, LocationAccuracy, LocationNotes, DeterminationDate, DistanceToLocation, 
                      DirectionToLocation, ResponsibleName, ResponsibleAgentURI, AverageAltitudeCache, AverageLatitudeCache, AverageLongitudeCache
FROM         dbo.CollectionEventLocalisation
WHERE     (LocalisationSystemID = 8)

GO

GRANT SELECT ON [dbo].[FirstLinesCoordinatesWGS84] TO [DiversityCollectionUser]
GO


--#####################################################################################################################
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesGeographicRegion]'))
DROP VIEW [dbo].[FirstLinesGeographicRegion]
GO


CREATE VIEW [dbo].[FirstLinesGeographicRegion]
AS
SELECT     CollectionEventID, PropertyID, DisplayText, PropertyURI, PropertyHierarchyCache, PropertyValue, ResponsibleName, ResponsibleAgentURI, Notes, 
                      AverageValueCache
FROM         dbo.CollectionEventProperty
WHERE     (PropertyID = 10)

GO


GRANT SELECT ON [dbo].[FirstLinesGeographicRegion] TO [DiversityCollectionUser]
GO


--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesIdentificationUnit]'))
DROP VIEW [dbo].[FirstLinesIdentificationUnit]
GO


CREATE VIEW [dbo].[FirstLinesIdentificationUnit]
AS
SELECT     CollectionSpecimenID, IdentificationUnitID, LastIdentificationCache, FamilyCache, OrderCache, TaxonomicGroup, OnlyObserved, RelatedUnitID, 
                      RelationType, ColonisedSubstratePart, LifeStage, Gender, NumberOfUnits, ExsiccataNumber, ExsiccataIdentification, UnitIdentifier, UnitDescription, 
                      Circumstances, DisplayOrder, Notes
FROM         dbo.IdentificationUnit
WHERE     EXISTS
                          (SELECT     CollectionSpecimenID
                            FROM          dbo.IdentificationUnit AS U
                            WHERE      (DisplayOrder > 0)
                            GROUP BY CollectionSpecimenID
                            HAVING      (CollectionSpecimenID = dbo.IdentificationUnit.CollectionSpecimenID) AND (MIN(DisplayOrder) = dbo.IdentificationUnit.DisplayOrder))
GO


GRANT SELECT ON [dbo].[FirstLinesIdentificationUnit] TO [DiversityCollectionUser]
GO




--#####################################################################################################################
/****** Object:  View [dbo].[FirstLinesIdentification]    Script Date: 11/07/2008 16:12:28 ******/
--#####################################################################################################################


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesIdentification]'))
DROP VIEW [dbo].[FirstLinesIdentification]
GO

CREATE VIEW [dbo].[FirstLinesIdentification]
AS
SELECT     dbo.Identification.CollectionSpecimenID, dbo.Identification.IdentificationUnitID, dbo.Identification.IdentificationSequence, 
                      dbo.Identification.IdentificationDate, dbo.Identification.IdentificationDay, dbo.Identification.IdentificationMonth, dbo.Identification.IdentificationYear, 
                      dbo.Identification.IdentificationDateSupplement, dbo.Identification.IdentificationDateCategory, dbo.Identification.VernacularTerm, 
                      dbo.Identification.TaxonomicName, dbo.Identification.NameURI, dbo.Identification.IdentificationCategory, dbo.Identification.IdentificationQualifier, 
                      dbo.Identification.TypeStatus, dbo.Identification.TypeNotes, dbo.Identification.ReferenceTitle, dbo.Identification.ReferenceURI, dbo.Identification.Notes, 
                      dbo.Identification.ResponsibleName, dbo.Identification.ResponsibleAgentURI, dbo.Identification.LogCreatedWhen, dbo.Identification.LogCreatedBy, 
                      dbo.Identification.LogUpdatedWhen, dbo.Identification.LogUpdatedBy
FROM         dbo.Identification INNER JOIN
                      dbo.FirstLinesIdentificationUnit ON dbo.Identification.CollectionSpecimenID = dbo.FirstLinesIdentificationUnit.CollectionSpecimenID AND 
                      dbo.Identification.IdentificationUnitID = dbo.FirstLinesIdentificationUnit.IdentificationUnitID
WHERE     EXISTS
                          (SELECT     CollectionSpecimenID
                            FROM          dbo.Identification AS I
                            GROUP BY CollectionSpecimenID, IdentificationUnitID
                            HAVING      (CollectionSpecimenID = dbo.Identification.CollectionSpecimenID) AND (IdentificationUnitID = dbo.Identification.IdentificationUnitID) AND 
                                                   (MAX(IdentificationSequence) = dbo.Identification.IdentificationSequence))

GO


GRANT SELECT ON [dbo].[FirstLinesIdentification] TO [DiversityCollectionUser]
GO



--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesLithostratigraphy]'))
DROP VIEW [dbo].[FirstLinesLithostratigraphy]
GO

CREATE VIEW [dbo].[FirstLinesLithostratigraphy]
AS
SELECT     CollectionEventID, PropertyID, DisplayText, PropertyURI, PropertyHierarchyCache, PropertyValue, ResponsibleName, ResponsibleAgentURI, Notes, 
                      AverageValueCache
FROM         dbo.CollectionEventProperty
WHERE     (PropertyID = 30)


GO


GRANT SELECT ON [dbo].[FirstLinesLithostratigraphy] TO [DiversityCollectionUser]
GO


--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesMTB]'))
DROP VIEW [dbo].[FirstLinesMTB]
GO

CREATE VIEW [dbo].[FirstLinesMTB]
AS
SELECT     CollectionEventID, LocalisationSystemID, Location1, Location2, LocationAccuracy, LocationNotes, DeterminationDate, DistanceToLocation, 
                      DirectionToLocation, ResponsibleName, ResponsibleAgentURI, AverageAltitudeCache, AverageLatitudeCache, AverageLongitudeCache
FROM         dbo.CollectionEventLocalisation
WHERE     (LocalisationSystemID = 3)

GO

GRANT SELECT ON [dbo].[FirstLinesMTB] TO [DiversityCollectionUser]
GO



--#####################################################################################################################


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesNamedArea]'))
DROP VIEW [dbo].[FirstLinesNamedArea]
GO

CREATE VIEW [dbo].[FirstLinesNamedArea]
AS
SELECT     CollectionEventID, LocalisationSystemID, Location1, Location2, LocationAccuracy, LocationNotes, DeterminationDate, DistanceToLocation, 
                      DirectionToLocation, ResponsibleName, ResponsibleAgentURI, AverageAltitudeCache, AverageLatitudeCache, AverageLongitudeCache
FROM         dbo.CollectionEventLocalisation
WHERE     (LocalisationSystemID = 7)

GO

GRANT SELECT ON [dbo].[FirstLinesNamedArea] TO [DiversityCollectionUser]
GO


--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesSecondUnit]'))
DROP VIEW [dbo].[FirstLinesSecondUnit]
GO

CREATE VIEW [dbo].[FirstLinesSecondUnit]
AS
SELECT     U2.CollectionSpecimenID, U2.IdentificationUnitID, U2.LastIdentificationCache, U2.FamilyCache, U2.OrderCache, U2.TaxonomicGroup, 
                      U2.OnlyObserved, U2.RelatedUnitID, U2.RelationType, U2.ColonisedSubstratePart, U2.LifeStage, U2.Gender, U2.NumberOfUnits, U2.ExsiccataNumber, 
                      U2.ExsiccataIdentification, U2.UnitIdentifier, U2.UnitDescription, U2.Circumstances, U2.DisplayOrder, U2.Notes
FROM         dbo.IdentificationUnit AS U2 INNER JOIN
                      dbo.CollectionSpecimenID_UserAvailable AS AV ON U2.CollectionSpecimenID = AV.CollectionSpecimenID
WHERE     EXISTS
                          (SELECT     MIN(U.IdentificationUnitID) AS Expr1, U.CollectionSpecimenID
                            FROM          dbo.IdentificationUnit AS U INNER JOIN
                                                   dbo.FirstLinesIdentificationUnit AS U1 ON U.CollectionSpecimenID = U1.CollectionSpecimenID AND 
                                                   U.DisplayOrder > U1.DisplayOrder
                            GROUP BY U.CollectionSpecimenID
                            HAVING      (U2.CollectionSpecimenID = U.CollectionSpecimenID) AND (U2.IdentificationUnitID = MIN(U.IdentificationUnitID)))
GO


GRANT SELECT ON [dbo].[FirstLinesSecondUnit] TO [DiversityCollectionUser]
GO



--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesSecondUnitIdentification]'))
DROP VIEW [dbo].[FirstLinesSecondUnitIdentification]
GO

CREATE VIEW [dbo].[FirstLinesSecondUnitIdentification]
AS
SELECT     dbo.Identification.CollectionSpecimenID, dbo.Identification.IdentificationUnitID, dbo.Identification.IdentificationSequence, 
                      dbo.Identification.IdentificationDate, dbo.Identification.IdentificationDay, dbo.Identification.IdentificationMonth, dbo.Identification.IdentificationYear, 
                      dbo.Identification.IdentificationDateSupplement, dbo.Identification.IdentificationDateCategory, dbo.Identification.VernacularTerm, 
                      dbo.Identification.TaxonomicName, dbo.Identification.NameURI, dbo.Identification.IdentificationCategory, dbo.Identification.IdentificationQualifier, 
                      dbo.Identification.TypeStatus, dbo.Identification.TypeNotes, dbo.Identification.ReferenceTitle, dbo.Identification.ReferenceURI, dbo.Identification.Notes, 
                      dbo.Identification.ResponsibleName, dbo.Identification.ResponsibleAgentURI
FROM         dbo.FirstLinesSecondUnit INNER JOIN
                      dbo.Identification ON dbo.FirstLinesSecondUnit.CollectionSpecimenID = dbo.Identification.CollectionSpecimenID AND 
                      dbo.FirstLinesSecondUnit.IdentificationUnitID = dbo.Identification.IdentificationUnitID
WHERE     EXISTS
                          (SELECT     CollectionSpecimenID
                            FROM          dbo.Identification AS I
                            GROUP BY CollectionSpecimenID, IdentificationUnitID
                            HAVING      (CollectionSpecimenID = dbo.Identification.CollectionSpecimenID) AND (MAX(IdentificationSequence) 
                                                   = dbo.Identification.IdentificationSequence) AND (IdentificationUnitID = dbo.Identification.IdentificationUnitID))

GO



GRANT SELECT ON [dbo].[FirstLinesSecondUnitIdentification] TO [DiversityCollectionUser]
GO


--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesTransaction]'))
DROP VIEW [dbo].[FirstLinesTransaction]
GO


CREATE VIEW [dbo].[FirstLinesTransaction]
AS
SELECT     dbo.FirstLinesCollectionSpecimenPart.CollectionSpecimenID, dbo.FirstLinesCollectionSpecimenPart.SpecimenPartID, 
                      MIN(dbo.CollectionSpecimenTransaction.TransactionID) AS TransactionID
FROM         dbo.FirstLinesCollectionSpecimenPart INNER JOIN
                      dbo.CollectionSpecimenTransaction ON 
                      dbo.FirstLinesCollectionSpecimenPart.CollectionSpecimenID = dbo.CollectionSpecimenTransaction.CollectionSpecimenID AND 
                      dbo.FirstLinesCollectionSpecimenPart.SpecimenPartID = dbo.CollectionSpecimenTransaction.SpecimenPartID
GROUP BY dbo.FirstLinesCollectionSpecimenPart.CollectionSpecimenID, dbo.FirstLinesCollectionSpecimenPart.SpecimenPartID

GO

GRANT SELECT ON [dbo].[FirstLinesTransaction] TO [DiversityCollectionUser]
GO


--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesCollectionSpecimenTransaction]'))
DROP VIEW [dbo].[FirstLinesCollectionSpecimenTransaction]
GO

CREATE VIEW [dbo].[FirstLinesCollectionSpecimenTransaction]
AS
SELECT     dbo.CollectionSpecimenTransaction.CollectionSpecimenID, dbo.[Transaction].TransactionTitle AS _Transaction, 
                      dbo.[Transaction].TransactionType AS Transaction_type, dbo.[Transaction].TransactionID AS _TransactionID
FROM         dbo.CollectionSpecimenTransaction INNER JOIN
                      dbo.FirstLinesCollectionSpecimenPart ON 
                      dbo.CollectionSpecimenTransaction.CollectionSpecimenID = dbo.FirstLinesCollectionSpecimenPart.CollectionSpecimenID AND 
                      dbo.CollectionSpecimenTransaction.SpecimenPartID = dbo.FirstLinesCollectionSpecimenPart.SpecimenPartID INNER JOIN
                      dbo.[Transaction] ON dbo.CollectionSpecimenTransaction.TransactionID = dbo.[Transaction].TransactionID INNER JOIN
                      dbo.FirstLinesTransaction ON dbo.CollectionSpecimenTransaction.CollectionSpecimenID = dbo.FirstLinesTransaction.CollectionSpecimenID AND 
                      dbo.CollectionSpecimenTransaction.SpecimenPartID = dbo.FirstLinesTransaction.SpecimenPartID AND 
                      dbo.CollectionSpecimenTransaction.TransactionID = dbo.FirstLinesTransaction.TransactionID AND 
                      dbo.[Transaction].TransactionID = dbo.FirstLinesTransaction.TransactionID
GROUP BY dbo.CollectionSpecimenTransaction.CollectionSpecimenID, dbo.[Transaction].TransactionType, dbo.[Transaction].TransactionTitle, 
                      dbo.[Transaction].TransactionID

GO

GRANT SELECT ON [dbo].[FirstLinesCollectionSpecimenTransaction] TO [DiversityCollectionUser]
GO


--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesLoan]'))
DROP VIEW [dbo].[FirstLinesLoan]
GO

CREATE VIEW [dbo].[FirstLinesLoan]
AS
SELECT     dbo.CollectionSpecimenTransaction.CollectionSpecimenID, dbo.CollectionSpecimenTransaction.SpecimenPartID, 
                      MIN(dbo.CollectionSpecimenTransaction.TransactionID) AS TransactionID
FROM         dbo.FirstLinesCollectionSpecimenPart INNER JOIN
                      dbo.CollectionSpecimenTransaction ON 
                      dbo.FirstLinesCollectionSpecimenPart.CollectionSpecimenID = dbo.CollectionSpecimenTransaction.CollectionSpecimenID AND 
                      dbo.FirstLinesCollectionSpecimenPart.SpecimenPartID = dbo.CollectionSpecimenTransaction.SpecimenPartID
WHERE     (dbo.CollectionSpecimenTransaction.IsOnLoan = 1)
GROUP BY dbo.CollectionSpecimenTransaction.CollectionSpecimenID, dbo.CollectionSpecimenTransaction.SpecimenPartID

GO

GRANT SELECT ON [dbo].[FirstLinesLoan] TO [DiversityCollectionUser]
GO


--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesCollectionSpecimenOnLoan]'))
DROP VIEW [dbo].[FirstLinesCollectionSpecimenOnLoan]
GO

CREATE VIEW [dbo].[FirstLinesCollectionSpecimenOnLoan]
AS
SELECT     dbo.CollectionSpecimenTransaction.CollectionSpecimenID, dbo.[Transaction].TransactionTitle AS _Transaction, 
                      dbo.[Transaction].TransactionID AS _TransactionID
FROM         dbo.CollectionSpecimenTransaction INNER JOIN
                      dbo.FirstLinesCollectionSpecimenPart ON 
                      dbo.CollectionSpecimenTransaction.CollectionSpecimenID = dbo.FirstLinesCollectionSpecimenPart.CollectionSpecimenID AND 
                      dbo.CollectionSpecimenTransaction.SpecimenPartID = dbo.FirstLinesCollectionSpecimenPart.SpecimenPartID INNER JOIN
                      dbo.[Transaction] ON dbo.CollectionSpecimenTransaction.TransactionID = dbo.[Transaction].TransactionID INNER JOIN
                      dbo.FirstLinesLoan ON dbo.CollectionSpecimenTransaction.CollectionSpecimenID = dbo.FirstLinesLoan.CollectionSpecimenID AND 
                      dbo.CollectionSpecimenTransaction.SpecimenPartID = dbo.FirstLinesLoan.SpecimenPartID AND 
                      dbo.CollectionSpecimenTransaction.TransactionID = dbo.FirstLinesLoan.TransactionID AND 
                      dbo.[Transaction].TransactionID = dbo.FirstLinesLoan.TransactionID
WHERE     (dbo.CollectionSpecimenTransaction.IsOnLoan = 1)
GROUP BY dbo.CollectionSpecimenTransaction.CollectionSpecimenID, dbo.[Transaction].TransactionType, dbo.[Transaction].TransactionTitle, 
                      dbo.[Transaction].TransactionID
HAVING      (dbo.[Transaction].TransactionType = N'loan')
GO

GRANT SELECT ON [dbo].[FirstLinesCollectionSpecimenOnLoan] TO [DiversityCollectionUser]
GO


--#####################################################################################################################

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesCollectionSpecimen]'))
DROP VIEW [dbo].[FirstLinesCollectionSpecimen]
GO

CREATE VIEW [dbo].[FirstLinesCollectionSpecimen]
AS
SELECT     dbo.CollectionSpecimen.CollectionSpecimenID, dbo.CollectionSpecimen.AccessionNumber AS Accession_number, 
                      dbo.CollectionSpecimen.DataWithholdingReason AS Data_withholding_reason, 
                      dbo.CollectionEvent.DataWithholdingReason AS Data_withholding_reason_for_collection_event, 
                      dbo.FirstLinesCollectionAgent.DataWithholdingReason AS Data_withholding_reason_for_collector, 
                      dbo.CollectionEvent.CollectorsEventNumber AS Collectors_event_number, dbo.CollectionEvent.CollectionDay AS Collection_day, 
                      dbo.CollectionEvent.CollectionMonth AS Collection_month, dbo.CollectionEvent.CollectionYear AS Collection_year, 
                      dbo.CollectionEvent.CollectionDateSupplement AS Collection_date_supplement, dbo.CollectionEvent.CollectionTime AS Collection_time, 
                      dbo.CollectionEvent.CollectionTimeSpan AS Collection_time_span, dbo.CollectionEvent.CountryCache AS Country, 
                      dbo.CollectionEvent.LocalityDescription AS Locality_description, dbo.CollectionEvent.HabitatDescription AS Habitat_description, 
                      dbo.CollectionEvent.CollectingMethod AS Collecting_method, dbo.CollectionEvent.Notes AS Collection_event_notes, 
                      dbo.FirstLinesNamedArea.Location1 AS Named_area, dbo.FirstLinesNamedArea.DistanceToLocation AS Distance_to_location, 
                      dbo.FirstLinesNamedArea.DirectionToLocation AS Direction_to_location, dbo.FirstLinesCoordinatesWGS84.Location1 AS Longitude, 
                      dbo.FirstLinesCoordinatesWGS84.Location2 AS Latitude, NULL AS Link_to_GoogleMaps, dbo.FirstLinesAltitude.Location1 AS Altitude_from, 
                      dbo.FirstLinesAltitude.Location2 AS Altitude_to, dbo.FirstLinesMTB.Location1 AS MTB, dbo.FirstLinesMTB.Location2 AS Quadrant, 
                      dbo.FirstLinesMTB.LocationNotes AS Notes_for_MTB, dbo.FirstLinesGeographicRegion.DisplayText AS Geographic_region, 
                      dbo.FirstLinesLithostratigraphy.DisplayText AS Lithostratigraphy, dbo.FirstLinesChronostratigraphy.DisplayText AS Chronostratigraphy, 
                      dbo.FirstLinesCollectionAgent.CollectorsName AS Collectors_name, dbo.FirstLinesCollectionAgent.CollectorsAgentURI AS Link_to_DiversityAgents, 
                      dbo.FirstLinesCollectionAgent.CollectorsNumber AS Collectors_number, dbo.FirstLinesCollectionAgent.Notes AS Notes_about_collector, 
                      dbo.CollectionSpecimen.AccessionDay AS Accession_day, dbo.CollectionSpecimen.AccessionMonth AS Accession_month, 
                      dbo.CollectionSpecimen.AccessionYear AS Accession_year, dbo.CollectionSpecimen.AccessionDateSupplement AS Accession_date_supplement, 
                      dbo.CollectionSpecimen.DepositorsName AS Depositors_name, dbo.CollectionSpecimen.DepositorsAgentURI AS Depositors_link_to_DiversityAgents, 
                      dbo.CollectionSpecimen.DepositorsAccessionNumber AS Depositors_accession_number, 
                      dbo.CollectionSpecimen.ExsiccataAbbreviation AS Exsiccata_abbreviation, dbo.CollectionSpecimen.ExsiccataURI AS Link_to_DiversityExsiccatae, 
                      dbo.FirstLinesIdentificationUnit.ExsiccataNumber AS Exsiccata_number, dbo.CollectionSpecimen.OriginalNotes AS Original_notes, 
                      dbo.CollectionSpecimen.AdditionalNotes AS Additional_notes, dbo.CollectionSpecimen.InternalNotes AS Internal_notes, 
                      dbo.CollectionSpecimen.LabelTitle AS Label_title, dbo.CollectionSpecimen.LabelType AS Label_type, 
                      dbo.CollectionSpecimen.LabelTranscriptionState AS Label_transcription_state, 
                      dbo.CollectionSpecimen.LabelTranscriptionNotes AS Label_transcription_notes, dbo.CollectionSpecimen.Problems, 
                      dbo.FirstLinesIdentificationUnit.TaxonomicGroup AS Taxonomic_group, dbo.FirstLinesIdentificationUnit.RelationType AS Relation_type, 
                      dbo.FirstLinesIdentificationUnit.ColonisedSubstratePart AS Colonised_substrate_part, dbo.FirstLinesIdentificationUnit.LifeStage AS Life_stage, 
                      dbo.FirstLinesIdentificationUnit.Gender, dbo.FirstLinesIdentificationUnit.NumberOfUnits AS Number_of_units, 
                      dbo.FirstLinesIdentificationUnit.Circumstances, dbo.FirstLinesIdentificationUnit.OrderCache AS Order_of_taxon, 
                      dbo.FirstLinesIdentificationUnit.FamilyCache AS Family_of_taxon, dbo.FirstLinesIdentificationUnit.UnitIdentifier AS Identifier_of_organism, 
                      dbo.FirstLinesIdentificationUnit.UnitDescription AS Description_of_organism, dbo.FirstLinesIdentificationUnit.Notes AS Notes_for_organism, 
                      dbo.FirstLinesIdentification.TaxonomicName AS Taxonomic_name, dbo.FirstLinesIdentification.NameURI AS Link_to_DiversityTaxonNames, 
                      dbo.FirstLinesIdentification.VernacularTerm AS Vernacular_term, dbo.FirstLinesIdentification.IdentificationDay AS Identification_day, 
                      dbo.FirstLinesIdentification.IdentificationMonth AS Identification_month, dbo.FirstLinesIdentification.IdentificationYear AS Identification_year, 
                      dbo.FirstLinesIdentification.IdentificationQualifier AS Identification_qualifier, dbo.FirstLinesIdentification.TypeStatus AS Type_status, 
                      dbo.FirstLinesIdentification.TypeNotes AS Type_notes, dbo.FirstLinesIdentification.Notes AS Notes_for_identification, 
                      dbo.FirstLinesIdentification.ReferenceTitle AS Reference_title, dbo.FirstLinesIdentification.ReferenceURI AS Link_to_DiversityReferences, 
                      dbo.FirstLinesIdentification.ResponsibleName AS Responsible, 
                      dbo.FirstLinesIdentification.ResponsibleAgentURI AS Link_to_DiversityAgents_for_responsible, 
                      dbo.FirstLinesSecondUnit.TaxonomicGroup AS Taxonomic_group_of_second_organism, 
                      dbo.FirstLinesSecondUnitIdentification.TaxonomicName AS Taxonomic_name_of_second_organism, 
                      dbo.FirstLinesSecondUnitIdentification.NameURI AS Link_to_DiversityTaxonNames_of_second_organism, 
                      dbo.FirstLinesSecondUnitIdentification.VernacularTerm AS Vernacular_term_of_second_organism, 
                      dbo.FirstLinesCollectionSpecimenPart.CollectionID AS Collection, dbo.FirstLinesCollectionSpecimenPart.MaterialCategory AS Material_category, 
                      dbo.FirstLinesCollectionSpecimenPart.StorageLocation AS Storage_location, dbo.FirstLinesCollectionSpecimenPart.Stock, 
                      dbo.FirstLinesCollectionSpecimenPart.PreparationMethod AS Preparation_method, 
                      dbo.FirstLinesCollectionSpecimenPart.PreparationDate AS Preparation_date, dbo.FirstLinesCollectionSpecimenPart.Notes AS Notes_for_part, 
                      dbo.FirstLinesCollectionSpecimenTransaction._TransactionID, dbo.FirstLinesCollectionSpecimenTransaction._Transaction, 
                      dbo.FirstLinesCollectionSpecimenOnLoan._TransactionID AS On_loan, dbo.CollectionEvent.CollectionEventID AS _CollectionEventID, 
                      dbo.FirstLinesIdentificationUnit.IdentificationUnitID AS _IdentificationUnitID, 
                      dbo.FirstLinesIdentification.IdentificationSequence AS _IdentificationSequence, dbo.FirstLinesSecondUnit.IdentificationUnitID AS _SecondUnitID, 
                      dbo.FirstLinesSecondUnitIdentification.IdentificationSequence AS _SecondSequence, 
                      dbo.FirstLinesCollectionSpecimenPart.SpecimenPartID AS _SpecimenPartID, 
                      dbo.FirstLinesCoordinatesWGS84.LocationAccuracy AS _CoordinatesLocationAccuracy, 
                      dbo.FirstLinesCoordinatesWGS84.AverageLatitudeCache AS _CoordinatesAverageLatitudeCache, 
                      dbo.FirstLinesCoordinatesWGS84.AverageLongitudeCache AS _CoordinatesAverageLongitudeCache, 
                      dbo.FirstLinesCoordinatesWGS84.LocationNotes AS _CoordinatesLocationNotes, 
                      dbo.FirstLinesGeographicRegion.PropertyURI AS _GeographicRegionPropertyURI, 
                      dbo.FirstLinesLithostratigraphy.PropertyURI AS _LithostratigraphyPropertyURI, 
                      dbo.FirstLinesChronostratigraphy.PropertyURI AS _ChronostratigraphyPropertyURI, dbo.FirstLinesNamedArea.Location2 AS _NamedAreaLocation2, 
                      dbo.FirstLinesNamedArea.AverageLatitudeCache AS _NamedAverageLatitudeCache, 
                      dbo.FirstLinesNamedArea.AverageLongitudeCache AS _NamedAverageLongitudeCache, 
                      dbo.FirstLinesLithostratigraphy.PropertyHierarchyCache AS _LithostratigraphyPropertyHierarchyCache, 
                      dbo.FirstLinesChronostratigraphy.PropertyHierarchyCache AS _ChronostratigraphyPropertyHierarchyCache, 
                      dbo.FirstLinesSecondUnit.FamilyCache AS _SecondUnitFamilyCache, dbo.FirstLinesSecondUnit.OrderCache AS _SecondUnitOrderCache, 
                      dbo.FirstLinesAltitude.AverageAltitudeCache AS _AverageAltitudeCache
FROM         dbo.FirstLinesCollectionSpecimenTransaction RIGHT OUTER JOIN
                      dbo.FirstLinesCollectionSpecimenOnLoan RIGHT OUTER JOIN
                      dbo.CollectionSpecimenID_UserAvailable INNER JOIN
                      dbo.CollectionSpecimen ON dbo.CollectionSpecimenID_UserAvailable.CollectionSpecimenID = dbo.CollectionSpecimen.CollectionSpecimenID ON 
                      dbo.FirstLinesCollectionSpecimenOnLoan.CollectionSpecimenID = dbo.CollectionSpecimen.CollectionSpecimenID ON 
                      dbo.FirstLinesCollectionSpecimenTransaction.CollectionSpecimenID = dbo.CollectionSpecimen.CollectionSpecimenID LEFT OUTER JOIN
                      dbo.FirstLinesIdentificationUnit LEFT OUTER JOIN
                      dbo.FirstLinesIdentification ON dbo.FirstLinesIdentificationUnit.CollectionSpecimenID = dbo.FirstLinesIdentification.CollectionSpecimenID AND 
                      dbo.FirstLinesIdentificationUnit.IdentificationUnitID = dbo.FirstLinesIdentification.IdentificationUnitID ON 
                      dbo.CollectionSpecimen.CollectionSpecimenID = dbo.FirstLinesIdentificationUnit.CollectionSpecimenID LEFT OUTER JOIN
                      dbo.FirstLinesSecondUnit LEFT OUTER JOIN
                      dbo.FirstLinesSecondUnitIdentification ON 
                      dbo.FirstLinesSecondUnit.CollectionSpecimenID = dbo.FirstLinesSecondUnitIdentification.CollectionSpecimenID AND 
                      dbo.FirstLinesSecondUnit.IdentificationUnitID = dbo.FirstLinesSecondUnitIdentification.IdentificationUnitID ON 
                      dbo.CollectionSpecimen.CollectionSpecimenID = dbo.FirstLinesSecondUnit.CollectionSpecimenID LEFT OUTER JOIN
                      dbo.FirstLinesCollectionAgent ON 
                      dbo.CollectionSpecimen.CollectionSpecimenID = dbo.FirstLinesCollectionAgent.CollectionSpecimenID LEFT OUTER JOIN
                      dbo.FirstLinesAltitude RIGHT OUTER JOIN
                      dbo.FirstLinesMTB RIGHT OUTER JOIN
                      dbo.FirstLinesChronostratigraphy RIGHT OUTER JOIN
                      dbo.CollectionEvent ON dbo.FirstLinesChronostratigraphy.CollectionEventID = dbo.CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesLithostratigraphy ON dbo.CollectionEvent.CollectionEventID = dbo.FirstLinesLithostratigraphy.CollectionEventID ON 
                      dbo.FirstLinesMTB.CollectionEventID = dbo.CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesGeographicRegion ON dbo.CollectionEvent.CollectionEventID = dbo.FirstLinesGeographicRegion.CollectionEventID ON 
                      dbo.FirstLinesAltitude.CollectionEventID = dbo.CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesCoordinatesWGS84 ON dbo.CollectionEvent.CollectionEventID = dbo.FirstLinesCoordinatesWGS84.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesNamedArea ON dbo.CollectionEvent.CollectionEventID = dbo.FirstLinesNamedArea.CollectionEventID ON 
                      dbo.CollectionSpecimen.CollectionEventID = dbo.CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      dbo.FirstLinesCollectionSpecimenPart ON dbo.CollectionSpecimen.CollectionSpecimenID = dbo.FirstLinesCollectionSpecimenPart.CollectionSpecimenID

GO

GRANT SELECT ON [dbo].[FirstLinesCollectionSpecimen] TO [DiversityCollectionUser]
GO

--#####################################################################################################################
declare @i int
set @i = (select count(*) from CollMaterialCategory_Enum where Code = 'incomplete skeleton')
if (@i = 0)
begin
INSERT INTO CollMaterialCategory_Enum
                      (Code, Description, DisplayText, DisplayOrder, DisplayEnable, InternalNotes, ParentCode)
VALUES     ('incomplete skeleton', 'incomplete skeleton', 'incomplete skeleton', 505, 1, NULL, 'bones')
end

--#####################################################################################################################
--- Adaptions for InternalNotes in CollectionSpecimen -----------------------------------------------------------------
--#####################################################################################################################

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trgDelCollectionSpecimen]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trgDelCollectionSpecimen] ON [dbo].[CollectionSpecimen] 
FOR DELETE AS 

/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 31.08.2007  */ 


/* saving the original dataset in the logging table */ 
INSERT INTO CollectionSpecimen_Log (CollectionSpecimenID, Version, CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, Problems, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogState) 
SELECT deleted.CollectionSpecimenID, deleted.Version, deleted.CollectionEventID, deleted.CollectionID, deleted.AccessionNumber, deleted.AccessionDate, deleted.AccessionDay, deleted.AccessionMonth, deleted.AccessionYear, deleted.AccessionDateSupplement, deleted.AccessionDateCategory, deleted.DepositorsName, deleted.DepositorsAgentURI, deleted.DepositorsAccessionNumber, deleted.LabelTitle, deleted.LabelType, deleted.LabelTranscriptionState, deleted.LabelTranscriptionNotes, deleted.ExsiccataURI, deleted.ExsiccataAbbreviation, deleted.OriginalNotes, deleted.AdditionalNotes, deleted.InternalNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.Problems, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  ''D''
FROM DELETED
' 
GO

--#####################################################################################################################

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trgUpdCollectionSpecimen]'))
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trgUpdCollectionSpecimen] ON [dbo].[CollectionSpecimen] 
FOR UPDATE AS

/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 31.08.2007  */ 

if not update(Version) 
BEGIN

/* setting the version in the main table */ 
DECLARE @i int 
DECLARE @ID int
DECLARE @Version int

set @i = (select count(*) from deleted) 

if @i = 1 
BEGIN 
   SET  @ID = (SELECT CollectionSpecimenID FROM deleted)
   EXECUTE procSetVersionCollectionSpecimen @ID
END 


/* saving the original dataset in the logging table */ 
INSERT INTO CollectionSpecimen_Log (CollectionSpecimenID, Version, CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, Problems, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogState) 
SELECT deleted.CollectionSpecimenID, deleted.Version, deleted.CollectionEventID, deleted.CollectionID, deleted.AccessionNumber, deleted.AccessionDate, deleted.AccessionDay, deleted.AccessionMonth, deleted.AccessionYear, deleted.AccessionDateSupplement, deleted.AccessionDateCategory, deleted.DepositorsName, deleted.DepositorsAgentURI, deleted.DepositorsAccessionNumber, deleted.LabelTitle, deleted.LabelType, deleted.LabelTranscriptionState, deleted.LabelTranscriptionNotes, deleted.ExsiccataURI, deleted.ExsiccataAbbreviation, deleted.OriginalNotes, deleted.AdditionalNotes, deleted.InternalNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.Problems, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  ''U''
FROM DELETED

END

/* updating the logging columns */
Update CollectionSpecimen
set LogUpdatedWhen = getdate(), LogUpdatedBy = current_user
FROM CollectionSpecimen, deleted 
where 1 = 1 
AND CollectionSpecimen.CollectionSpecimenID = deleted.CollectionSpecimenID
' 
GO




--#####################################################################################################################
--#####################################################################################################################


ALTER  PROCEDURE [dbo].[procInsertCollectionSpecimenCopy] 
	(@CollectionSpecimenID int output ,
	@OriginalCollectionSpecimenID int ,
	@AccessionNumber  nvarchar(50))
AS
declare @count int

-- CollectionSpecimen
INSERT INTO CollectionSpecimen (CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, 
	AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, 
	LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, 
	Problems, DataWithholdingReason, ExternalDatasourceID, ExternalIdentifier)
SELECT CollectionEventID, CollectionID, @AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, 
	AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, 
	LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, 
	Problems, DataWithholdingReason, ExternalDatasourceID, ExternalIdentifier
FROM CollectionSpecimen
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

SELECT @CollectionSpecimenID = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])

-- CollectionProject
INSERT INTO CollectionProject (CollectionSpecimenID, ProjectID)
SELECT @CollectionSpecimenID, ProjectID
FROM CollectionProject
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionAgent
INSERT INTO CollectionAgent ( CollectionSpecimenID, CollectorsName, CollectorsAgentURI, CollectorsSequence, CollectorsNumber, Notes, DataWithholdingReason)
SELECT   @CollectionSpecimenID, CollectorsName, CollectorsAgentURI, CollectorsSequence, CollectorsNumber, Notes, DataWithholdingReason
FROM CollectionAgent
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionSpecimenPart
INSERT INTO CollectionSpecimenPart ( CollectionSpecimenID, SpecimenPartID, DerivedFromSpecimenPartID, PreparationMethod, PreparationDate, 
	AccessionNumber, PartSublabel, CollectionID, MaterialCategory, StorageLocation, Stock, Notes)
SELECT @CollectionSpecimenID, SpecimenPartID, DerivedFromSpecimenPartID, PreparationMethod, PreparationDate, 
	AccessionNumber, PartSublabel, CollectionID, MaterialCategory, StorageLocation, Stock, Notes
FROM CollectionSpecimenPart
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionRelation
INSERT INTO CollectionSpecimenRelation ( CollectionSpecimenID, RelatedSpecimenURI, RelatedSpecimenDisplayText, RelationType, 
	RelatedSpecimenCollectionID, RelatedSpecimenDescription, Notes, IsInternalRelationCache)
SELECT @CollectionSpecimenID, RelatedSpecimenURI, RelatedSpecimenDisplayText, RelationType, 
	RelatedSpecimenCollectionID, RelatedSpecimenDescription, Notes, IsInternalRelationCache
FROM CollectionSpecimenRelation
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionSpecimenProcessing
INSERT INTO CollectionSpecimenProcessing
	(CollectionSpecimenID, ProcessingDate, ProcessingID, Protocoll, SpecimenPartID, ProcessingDuration,  
	ResponsibleName, ResponsibleAgentURI,Notes)
SELECT @CollectionSpecimenID, ProcessingDate, ProcessingID, Protocoll, SpecimenPartID, ProcessingDuration,  
	ResponsibleName, ResponsibleAgentURI,Notes
FROM CollectionSpecimenProcessing
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID


-- IdentificationUnit
DECLARE @TempUnitTable TABLE (IdentificationUnitID int primary key,
   OriginalIdentificationUnitID int NULL)
DECLARE @IdentificationUnitID int
DECLARE @OriginalIdentificationUnitID int

DECLARE UnitCursor CURSOR FOR
SELECT IdentificationUnitID FROM IdentificationUnit WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID
OPEN UnitCursor
FETCH NEXT FROM UnitCursor INTO @OriginalIdentificationUnitID
WHILE @@FETCH_STATUS = 0
BEGIN
	-- IdentificationUnit
	INSERT INTO IdentificationUnit (CollectionSpecimenID, LastIdentificationCache, FamilyCache, OrderCache, TaxonomicGroup, 
		OnlyObserved, RelationType, ColonisedSubstratePart, LifeStage, Gender, NumberOfUnits, ExsiccataNumber, ExsiccataIdentification, 
		Circumstances, DisplayOrder, Notes)
	SELECT @CollectionSpecimenID, LastIdentificationCache, FamilyCache, OrderCache, TaxonomicGroup, 
		OnlyObserved, RelationType, ColonisedSubstratePart, LifeStage, Gender, NumberOfUnits, ExsiccataNumber, ExsiccataIdentification, 
		Circumstances, DisplayOrder, Notes
	FROM IdentificationUnit
	WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID
	AND IdentificationUnitID = @OriginalIdentificationUnitID

	SELECT @IdentificationUnitID = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])
	INSERT INTO @TempUnitTable (IdentificationUnitID, OriginalIdentificationUnitID) VALUES (@IdentificationUnitID, @OriginalIdentificationUnitID)

	-- Identification
	INSERT INTO Identification (CollectionSpecimenID, IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, 
		  IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, 
		  TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, Notes, ResponsibleName, ResponsibleAgentURI)
	SELECT @CollectionSpecimenID, @IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, 
		  IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, 
		  TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, Notes, ResponsibleName, ResponsibleAgentURI
	FROM Identification
	WHERE Identification.CollectionSpecimenID = @OriginalCollectionSpecimenID
	and Identification.IdentificationUnitID = @OriginalIdentificationUnitID

	-- IdentificationUnitInPart
	INSERT INTO IdentificationUnitInPart (CollectionSpecimenID, IdentificationUnitID, SpecimenPartID, DisplayOrder)
	SELECT @CollectionSpecimenID, @IdentificationUnitID, SpecimenPartID, DisplayOrder
	FROM IdentificationUnitInPart
	WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID
	AND IdentificationUnitInPart.IdentificationUnitID = @OriginalIdentificationUnitID

   	FETCH NEXT FROM UnitCursor INTO @OriginalIdentificationUnitID
END
CLOSE UnitCursor
DEALLOCATE UnitCursor

DECLARE @I INT
SET @I = (SELECT COUNT(*) FROM @TempUnitTable)
IF @I > 0
BEGIN
	UPDATE N SET N.RelatedUnitID = NH.IdentificationUnitID
	FROM IdentificationUnit N, IdentificationUnit NH, @TempUnitTable T, @TempUnitTable TH, IdentificationUnit O--, IdentificationUnit OH
	WHERE N.IdentificationUnitID = T.IdentificationUnitID
	AND O.IdentificationUnitID = T.OriginalIdentificationUnitID
	AND O.RelatedUnitID = TH.OriginalIdentificationUnitID
	AND TH.IdentificationUnitID = NH.IdentificationUnitID
END

SELECT @CollectionSpecimenID

GO


--#####################################################################################################################


ALTER  PROCEDURE [dbo].[procInsertCollectionSpecimenEventCopy] 
	(@CollectionSpecimenID int output ,
	@OriginalCollectionSpecimenID int ,
	@AccessionNumber  nvarchar(50),
	@EventCopyMode int)
AS

/*
Copy a collection specimen
@EventCopyMode
-1: dont copy the event, leave the entry in table CollectionSpecimen empty
0:  take same event as original specimen
1:  create new event with the same data as the old specimen
*/
declare @count int
declare @EventID int

if (@EventCopyMode = 0) set @EventID = (SELECT CollectionEventID FROM CollectionSpecimen WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID)
if (@EventCopyMode < 0) set @EventID = null
if (@EventCopyMode > 0) 
begin
	DECLARE @RC int
	DECLARE @CollectionEventID int
	DECLARE @OriginalCollectionEventID int
	set @OriginalCollectionEventID = (SELECT CollectionEventID FROM CollectionSpecimen WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID)


	-- CollectionEvent
	INSERT INTO CollectionEvent
		  (SeriesID, CollectorsEventNumber, CollectionDate, CollectionDay, CollectionMonth, CollectionYear, CollectionDateSupplement, 
		  CollectionDateCategory, CollectionTime, CollectionTimeSpan, LocalityDescription, HabitatDescription, ReferenceTitle, ReferenceURI, CollectingMethod, 
		  Notes, CountryCache, DataWithholdingReason)
	SELECT SeriesID, CollectorsEventNumber, CollectionDate, CollectionDay, CollectionMonth, CollectionYear, CollectionDateSupplement, 
		  CollectionDateCategory, CollectionTime, CollectionTimeSpan, LocalityDescription, HabitatDescription, ReferenceTitle, ReferenceURI, CollectingMethod, 
		  Notes, CountryCache, DataWithholdingReason
	FROM  CollectionEvent
	WHERE CollectionEventID = @OriginalCollectionEventID

	SET @CollectionEventID = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])

	-- CollectionEventImage
	INSERT INTO CollectionEventImage
		(CollectionEventID, URI, ResourceURI, ImageType, Notes)
	SELECT @CollectionEventID, URI, ResourceURI, ImageType, Notes
	FROM CollectionEventImage
	WHERE (CollectionEventID = @OriginalCollectionEventID)

	-- CollectionEventLocalisation
	INSERT INTO CollectionEventLocalisation
		(CollectionEventID, LocalisationSystemID, Location1, Location2, LocationAccuracy, LocationNotes, DeterminationDate, DistanceToLocation, 
		DirectionToLocation, ResponsibleName, ResponsibleAgentURI, AverageAltitudeCache, AverageLatitudeCache, AverageLongitudeCache)
	SELECT @CollectionEventID, LocalisationSystemID, Location1, Location2, LocationAccuracy, LocationNotes, DeterminationDate, DistanceToLocation, 
		DirectionToLocation, ResponsibleName, ResponsibleAgentURI, AverageAltitudeCache, AverageLatitudeCache, AverageLongitudeCache
	FROM CollectionEventLocalisation
	WHERE (CollectionEventID = @OriginalCollectionEventID)

	-- CollectionEventProperty
	INSERT INTO CollectionEventProperty
		(CollectionEventID, PropertyID, DisplayText, PropertyURI, PropertyHierarchyCache, PropertyValue,  
		ResponsibleName, ResponsibleAgentURI, Notes,AverageValueCache)
	SELECT CollectionEventID, PropertyID, DisplayText, PropertyURI, PropertyHierarchyCache, PropertyValue,  
		ResponsibleName, ResponsibleAgentURI, Notes,AverageValueCache
	FROM CollectionEventProperty
	WHERE (CollectionEventID = @OriginalCollectionEventID)

	set @EventID = @CollectionEventID
end

-- CollectionSpecimen
INSERT INTO CollectionSpecimen (CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, 
	AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, 
	LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, 
	Problems, DataWithholdingReason, ExternalDatasourceID, ExternalIdentifier)
SELECT @EventID, CollectionID, @AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, 
	AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, 
	LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, 
	Problems, DataWithholdingReason, ExternalDatasourceID, ExternalIdentifier
FROM CollectionSpecimen
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

SET @CollectionSpecimenID = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])

-- CollectionProject
INSERT INTO CollectionProject (CollectionSpecimenID, ProjectID)
SELECT @CollectionSpecimenID, ProjectID
FROM CollectionProject
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionAgent
INSERT INTO CollectionAgent ( CollectionSpecimenID, CollectorsName, CollectorsAgentURI, CollectorsSequence, CollectorsNumber, Notes, DataWithholdingReason)
SELECT   @CollectionSpecimenID, CollectorsName, CollectorsAgentURI, CollectorsSequence, CollectorsNumber, Notes, DataWithholdingReason
FROM CollectionAgent
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionSpecimenPart
INSERT INTO CollectionSpecimenPart ( CollectionSpecimenID, SpecimenPartID, DerivedFromSpecimenPartID, PreparationMethod, PreparationDate, 
	AccessionNumber, PartSublabel, CollectionID, MaterialCategory, StorageLocation, Stock, Notes)
SELECT @CollectionSpecimenID, SpecimenPartID, DerivedFromSpecimenPartID, PreparationMethod, PreparationDate, 
	AccessionNumber, PartSublabel, CollectionID, MaterialCategory, StorageLocation, Stock, Notes
FROM CollectionSpecimenPart
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionRelation
INSERT INTO CollectionSpecimenRelation ( CollectionSpecimenID, RelatedSpecimenURI, RelatedSpecimenDisplayText, RelationType, 
	RelatedSpecimenCollectionID, RelatedSpecimenDescription, Notes, IsInternalRelationCache)
SELECT @CollectionSpecimenID, RelatedSpecimenURI, RelatedSpecimenDisplayText, RelationType, 
	RelatedSpecimenCollectionID, RelatedSpecimenDescription, Notes, IsInternalRelationCache
FROM CollectionSpecimenRelation
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID

-- CollectionSpecimenProcessing
INSERT INTO CollectionSpecimenProcessing
	(CollectionSpecimenID, ProcessingDate, ProcessingID, Protocoll, SpecimenPartID, ProcessingDuration,  
	ResponsibleName, ResponsibleAgentURI,Notes)
SELECT @CollectionSpecimenID, ProcessingDate, ProcessingID, Protocoll, SpecimenPartID, ProcessingDuration,  
	ResponsibleName, ResponsibleAgentURI,Notes
FROM CollectionSpecimenProcessing
WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID


-- IdentificationUnit
DECLARE @TempUnitTable TABLE (IdentificationUnitID int primary key,
   OriginalIdentificationUnitID int NULL)
DECLARE @IdentificationUnitID int
DECLARE @OriginalIdentificationUnitID int

DECLARE UnitCursor CURSOR FOR
SELECT IdentificationUnitID FROM IdentificationUnit WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID
OPEN UnitCursor
FETCH NEXT FROM UnitCursor INTO @OriginalIdentificationUnitID
WHILE @@FETCH_STATUS = 0
BEGIN
	-- IdentificationUnit
	INSERT INTO IdentificationUnit (CollectionSpecimenID, LastIdentificationCache, FamilyCache, OrderCache, TaxonomicGroup, 
		OnlyObserved, RelationType, ColonisedSubstratePart, LifeStage, Gender, NumberOfUnits, ExsiccataNumber, ExsiccataIdentification, 
		Circumstances, DisplayOrder, Notes)
	SELECT @CollectionSpecimenID, LastIdentificationCache, FamilyCache, OrderCache, TaxonomicGroup, 
		OnlyObserved, RelationType, ColonisedSubstratePart, LifeStage, Gender, NumberOfUnits, ExsiccataNumber, ExsiccataIdentification, 
		Circumstances, DisplayOrder, Notes
	FROM IdentificationUnit
	WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID
	AND IdentificationUnitID = @OriginalIdentificationUnitID

	SET @IdentificationUnitID = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])
	INSERT INTO @TempUnitTable (IdentificationUnitID, OriginalIdentificationUnitID) VALUES (@IdentificationUnitID, @OriginalIdentificationUnitID)

	-- Identification
	INSERT INTO Identification (CollectionSpecimenID, IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, 
		  IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, 
		  TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, Notes, ResponsibleName, ResponsibleAgentURI)
	SELECT @CollectionSpecimenID, @IdentificationUnitID, IdentificationSequence, IdentificationDate, IdentificationDay, IdentificationMonth, IdentificationYear, 
		  IdentificationDateSupplement, IdentificationDateCategory, VernacularTerm, TaxonomicName, NameURI, IdentificationCategory, IdentificationQualifier, 
		  TypeStatus, TypeNotes, ReferenceTitle, ReferenceURI, Notes, ResponsibleName, ResponsibleAgentURI
	FROM Identification
	WHERE Identification.CollectionSpecimenID = @OriginalCollectionSpecimenID
	and Identification.IdentificationUnitID = @OriginalIdentificationUnitID

	-- IdentificationUnitInPart
	INSERT INTO IdentificationUnitInPart (CollectionSpecimenID, IdentificationUnitID, SpecimenPartID, DisplayOrder)
	SELECT @CollectionSpecimenID, @IdentificationUnitID, SpecimenPartID, DisplayOrder
	FROM IdentificationUnitInPart
	WHERE CollectionSpecimenID = @OriginalCollectionSpecimenID
	AND IdentificationUnitInPart.IdentificationUnitID = @OriginalIdentificationUnitID

   	FETCH NEXT FROM UnitCursor INTO @OriginalIdentificationUnitID
END
CLOSE UnitCursor
DEALLOCATE UnitCursor

DECLARE @I INT
SET @I = (SELECT COUNT(*) FROM @TempUnitTable)
IF @I > 0
BEGIN
	UPDATE N SET N.RelatedUnitID = NH.IdentificationUnitID
	FROM IdentificationUnit N, IdentificationUnit NH, @TempUnitTable T, @TempUnitTable TH, IdentificationUnit O--, IdentificationUnit OH
	WHERE N.IdentificationUnitID = T.IdentificationUnitID
	AND O.IdentificationUnitID = T.OriginalIdentificationUnitID
	AND O.RelatedUnitID = TH.OriginalIdentificationUnitID
	AND TH.IdentificationUnitID = NH.IdentificationUnitID
END

SELECT @CollectionSpecimenID

GO


--#####################################################################################################################


UPDATE CollIdentificationQualifier_Enum SET Description = 'sensu lato'
FROM         CollIdentificationQualifier_Enum
WHERE     (Code = N's. l.')

UPDATE CollIdentificationQualifier_Enum SET Description = 'sensu stricto'
FROM         CollIdentificationQualifier_Enum
WHERE     (Code = N's. str.')




--#####################################################################################################################
--- Adaptions for External in CollectionSpecimen ----------------------------------------------------------------------
--#####################################################################################################################


IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CollectionExternalDatasource]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[CollectionExternalDatasource](
	[ExternalDatasourceID] [int] IDENTITY(1,1) NOT NULL,
	[ExternalDatasourceName] [nvarchar](255) NULL,
	[ExternalDatasourceVersion] [nvarchar](255) NULL,
	[Rights] [nvarchar](500) NULL,
	[ExternalDatasourceAuthors] [nvarchar](200) NULL,
	[ExternalDatasourceURI] [nvarchar](300) NULL,
	[ExternalDatasourceInstitution] [nvarchar](300) NULL,
	[InternalNotes] [nvarchar](1500) NULL,
	[ExternalAttribute_NameID] [nvarchar](255) NULL,
	[PreferredSequence] [tinyint] NULL,
	[Disabled] [bit] NULL,
 CONSTRAINT [PK_CollectionExternalDatasource] PRIMARY KEY CLUSTERED 
(
	[ExternalDatasourceID] ASC
)) 

ALTER TABLE [dbo].[CollectionSpecimen]  WITH CHECK ADD  CONSTRAINT [FK_CollectionSpecimen_CollectionExternalDatasource] FOREIGN KEY([ExternalDatasourceID])
REFERENCES [dbo].[CollectionExternalDatasource] ([ExternalDatasourceID])

END
GRANT SELECT ON [dbo].[CollectionExternalDatasource] TO [DiversityCollectionUser]
GRANT INSERT ON [dbo].[CollectionExternalDatasource] TO [DiversityCollectionAdministrator]
GRANT UPDATE ON [dbo].[CollectionExternalDatasource] TO [DiversityCollectionAdministrator]
GRANT DELETE ON [dbo].[CollectionExternalDatasource] TO [DiversityCollectionAdministrator]
GO


IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'ExternalDatasourceID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'An ID to identify an external data collection of collection specimen (primary key, the ID has no meaning outside of the DiversityWorkbench system)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'ExternalDatasourceID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'ExternalDatasourceName'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The name of the data collection that has been integrated or can be linked to for further analysis' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'ExternalDatasourceName'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'ExternalDatasourceVersion'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The version of this data collection (either official version number, or dates when the collection was integrated)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'ExternalDatasourceVersion'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'Rights'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A description of copyright agreements or permission to use data from the external database' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'Rights'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'ExternalDatasourceAuthors'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The persons or institutions responsible for the external database' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'ExternalDatasourceAuthors'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'ExternalDatasourceURI'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The URI of the database provider resp. the external database' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'ExternalDatasourceURI'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'ExternalDatasourceInstitution'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The institution responsible for the external database' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'ExternalDatasourceInstitution'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'InternalNotes'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Additional notes concerning this data collection' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'InternalNotes'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'ExternalAttribute_NameID'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The table and field name in the external data collection to which CollectionExternalID refers' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'ExternalAttribute_NameID'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'PreferredSequence'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'For selection in e.g. picklists: of several equal names only the name from the source with the lowest preferred sequence will be provided.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'PreferredSequence'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', N'COLUMN',N'Disabled'))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If this source should be disabled for selection of names e.g. in picklists' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource', @level2type=N'COLUMN',@level2name=N'Disabled'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'Caption' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'Caption', @value=N'The table of the database DiversityCollections containing source databases for taxonomic names' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource'
GO
IF NOT EXISTS (SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'CollectionExternalDatasource', NULL,NULL))
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'CollectionExternalDatasource document the sources of the names.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CollectionExternalDatasource'
GO

--#####################################################################################################################


IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trgDelCollectionSpecimen]'))
begin
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trgDelCollectionSpecimen] ON [dbo].[CollectionSpecimen] 
FOR DELETE AS 

/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 31.08.2007  */ 


/* saving the original dataset in the logging table */ 
INSERT INTO CollectionSpecimen_Log (CollectionSpecimenID, Version, CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, Problems, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogState, ExternalDatasourceID, ExternalIdentifier) 
SELECT deleted.CollectionSpecimenID, deleted.Version, deleted.CollectionEventID, deleted.CollectionID, deleted.AccessionNumber, deleted.AccessionDate, deleted.AccessionDay, deleted.AccessionMonth, deleted.AccessionYear, deleted.AccessionDateSupplement, deleted.AccessionDateCategory, deleted.DepositorsName, deleted.DepositorsAgentURI, deleted.DepositorsAccessionNumber, deleted.LabelTitle, deleted.LabelType, deleted.LabelTranscriptionState, deleted.LabelTranscriptionNotes, deleted.ExsiccataURI, deleted.ExsiccataAbbreviation, deleted.OriginalNotes, deleted.AdditionalNotes, deleted.InternalNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.Problems, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  ''D'', deleted.ExternalDatasourceID, deleted.ExternalIdentifier
FROM DELETED
' 
end
else
begin
EXEC dbo.sp_executesql @statement = N'ALTER TRIGGER [dbo].[trgDelCollectionSpecimen] ON [dbo].[CollectionSpecimen] 
FOR DELETE AS 

/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 31.08.2007  */ 


/* saving the original dataset in the logging table */ 
INSERT INTO CollectionSpecimen_Log (CollectionSpecimenID, Version, CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, Problems, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogState, ExternalDatasourceID, ExternalIdentifier) 
SELECT deleted.CollectionSpecimenID, deleted.Version, deleted.CollectionEventID, deleted.CollectionID, deleted.AccessionNumber, deleted.AccessionDate, deleted.AccessionDay, deleted.AccessionMonth, deleted.AccessionYear, deleted.AccessionDateSupplement, deleted.AccessionDateCategory, deleted.DepositorsName, deleted.DepositorsAgentURI, deleted.DepositorsAccessionNumber, deleted.LabelTitle, deleted.LabelType, deleted.LabelTranscriptionState, deleted.LabelTranscriptionNotes, deleted.ExsiccataURI, deleted.ExsiccataAbbreviation, deleted.OriginalNotes, deleted.AdditionalNotes, deleted.InternalNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.Problems, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  ''D'', deleted.ExternalDatasourceID, deleted.ExternalIdentifier
FROM DELETED
' 
end
GO

--#####################################################################################################################

IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trgUpdCollectionSpecimen]'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE TRIGGER [dbo].[trgUpdCollectionSpecimen] ON [dbo].[CollectionSpecimen] 
FOR UPDATE AS

/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 31.08.2007  */ 

if not update(Version) 
BEGIN

/* setting the version in the main table */ 
DECLARE @i int 
DECLARE @ID int
DECLARE @Version int

set @i = (select count(*) from deleted) 

if @i = 1 
BEGIN 
   SET  @ID = (SELECT CollectionSpecimenID FROM deleted)
   EXECUTE procSetVersionCollectionSpecimen @ID
END 


/* saving the original dataset in the logging table */ 
INSERT INTO CollectionSpecimen_Log (CollectionSpecimenID, Version, CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, Problems, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogState, ExternalDatasourceID, ExternalIdentifier) 
SELECT deleted.CollectionSpecimenID, deleted.Version, deleted.CollectionEventID, deleted.CollectionID, deleted.AccessionNumber, deleted.AccessionDate, deleted.AccessionDay, deleted.AccessionMonth, deleted.AccessionYear, deleted.AccessionDateSupplement, deleted.AccessionDateCategory, deleted.DepositorsName, deleted.DepositorsAgentURI, deleted.DepositorsAccessionNumber, deleted.LabelTitle, deleted.LabelType, deleted.LabelTranscriptionState, deleted.LabelTranscriptionNotes, deleted.ExsiccataURI, deleted.ExsiccataAbbreviation, deleted.OriginalNotes, deleted.AdditionalNotes, deleted.InternalNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.Problems, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  ''U'', deleted.ExternalDatasourceID, deleted.ExternalIdentifier
FROM DELETED

END

/* updating the logging columns */
Update CollectionSpecimen
set LogUpdatedWhen = getdate(), LogUpdatedBy = current_user
FROM CollectionSpecimen, deleted 
where 1 = 1 
AND CollectionSpecimen.CollectionSpecimenID = deleted.CollectionSpecimenID
' 
END
ELSE
BEGIN
EXEC dbo.sp_executesql @statement = N'ALTER TRIGGER [dbo].[trgUpdCollectionSpecimen] ON [dbo].[CollectionSpecimen] 
FOR UPDATE AS

/*  Created by DiversityWorkbench Administration.  */ 
/*  DiversityWorkbenchMaintenance  2.0.0.3 */ 
/*  Date: 31.08.2007  */ 

if not update(Version) 
BEGIN

/* setting the version in the main table */ 
DECLARE @i int 
DECLARE @ID int
DECLARE @Version int

set @i = (select count(*) from deleted) 

if @i = 1 
BEGIN 
   SET  @ID = (SELECT CollectionSpecimenID FROM deleted)
   EXECUTE procSetVersionCollectionSpecimen @ID
END 


/* saving the original dataset in the logging table */ 
INSERT INTO CollectionSpecimen_Log (CollectionSpecimenID, Version, CollectionEventID, CollectionID, AccessionNumber, AccessionDate, AccessionDay, AccessionMonth, AccessionYear, AccessionDateSupplement, AccessionDateCategory, DepositorsName, DepositorsAgentURI, DepositorsAccessionNumber, LabelTitle, LabelType, LabelTranscriptionState, LabelTranscriptionNotes, ExsiccataURI, ExsiccataAbbreviation, OriginalNotes, AdditionalNotes, InternalNotes, ReferenceTitle, ReferenceURI, Problems, DataWithholdingReason, LogCreatedWhen, LogCreatedBy, LogUpdatedWhen, LogUpdatedBy,  LogState, ExternalDatasourceID, ExternalIdentifier) 
SELECT deleted.CollectionSpecimenID, deleted.Version, deleted.CollectionEventID, deleted.CollectionID, deleted.AccessionNumber, deleted.AccessionDate, deleted.AccessionDay, deleted.AccessionMonth, deleted.AccessionYear, deleted.AccessionDateSupplement, deleted.AccessionDateCategory, deleted.DepositorsName, deleted.DepositorsAgentURI, deleted.DepositorsAccessionNumber, deleted.LabelTitle, deleted.LabelType, deleted.LabelTranscriptionState, deleted.LabelTranscriptionNotes, deleted.ExsiccataURI, deleted.ExsiccataAbbreviation, deleted.OriginalNotes, deleted.AdditionalNotes, deleted.InternalNotes, deleted.ReferenceTitle, deleted.ReferenceURI, deleted.Problems, deleted.DataWithholdingReason, deleted.LogCreatedWhen, deleted.LogCreatedBy, deleted.LogUpdatedWhen, deleted.LogUpdatedBy,  ''U'', deleted.ExternalDatasourceID, deleted.ExternalIdentifier
FROM DELETED

END

/* updating the logging columns */
Update CollectionSpecimen
set LogUpdatedWhen = getdate(), LogUpdatedBy = current_user
FROM CollectionSpecimen, deleted 
where 1 = 1 
AND CollectionSpecimen.CollectionSpecimenID = deleted.CollectionSpecimenID
' 
END
GO


--#####################################################################################################################


declare @i int
set @i = (select count(*) from CollMaterialCategory_Enum where Code = 'shell')
if (@i = 0)
begin
INSERT INTO [CollMaterialCategory_Enum]
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable]
           ,[ParentCode])
     VALUES
           ('shell'
           ,'shell'
           ,'shell'
           ,600
           ,1
           ,'preserved specimen')
END


set @i = (select count(*) from CollMaterialCategory_Enum where Code = 'tooth')
if (@i = 0)
BEGIN
INSERT INTO [CollMaterialCategory_Enum]
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable]
           ,[ParentCode])
     VALUES
           ('tooth'
           ,'tooth'
           ,'tooth'
           ,508
           ,1
           ,'bones')
END
GO


--#####################################################################################################################
-- Rechte fuer Transaction


GRANT UPDATE ON [dbo].[Transaction] TO [DiversityCollectionManager] AS [dbo]
GO


--#####################################################################################################################

/****** Object:  Index [IX_CollectionAgentSequence]    Script Date: 12/05/2008 15:45:03 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CollectionAgent]') AND name = N'IX_CollectionAgentSequence')
CREATE UNIQUE NONCLUSTERED INDEX [IX_CollectionAgentSequence] ON [dbo].[CollectionAgent] 
(
	[CollectionSpecimenID] ASC,
	[CollectorsSequence] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

--#####################################################################################################################


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[CollectionSpecimen_Core]'))
DROP VIEW [dbo].[CollectionSpecimen_Core]
GO


CREATE VIEW [dbo].[CollectionSpecimen_Core]
AS
SELECT     dbo.CollectionSpecimen.CollectionSpecimenID, dbo.CollectionSpecimen.Version, dbo.CollectionSpecimen.CollectionEventID, 
                      dbo.CollectionSpecimen.CollectionID, dbo.CollectionSpecimen.AccessionNumber, dbo.CollectionSpecimen.AccessionDate, 
                      dbo.CollectionSpecimen.AccessionDay, dbo.CollectionSpecimen.AccessionMonth, dbo.CollectionSpecimen.AccessionYear, 
                      dbo.CollectionSpecimen.AccessionDateSupplement, dbo.CollectionSpecimen.AccessionDateCategory, dbo.CollectionSpecimen.DepositorsName, 
                      dbo.CollectionSpecimen.DepositorsAgentURI, dbo.CollectionSpecimen.DepositorsAccessionNumber, dbo.CollectionSpecimen.LabelTitle, 
                      dbo.CollectionSpecimen.LabelType, dbo.CollectionSpecimen.LabelTranscriptionState, dbo.CollectionSpecimen.LabelTranscriptionNotes, 
                      dbo.CollectionSpecimen.ExsiccataURI, dbo.CollectionSpecimen.ExsiccataAbbreviation, dbo.CollectionSpecimen.OriginalNotes, 
                      dbo.CollectionSpecimen.AdditionalNotes, dbo.CollectionSpecimen.ReferenceTitle, dbo.CollectionSpecimen.ReferenceURI, 
                      dbo.CollectionSpecimen.Problems, dbo.CollectionSpecimen.DataWithholdingReason, CASE WHEN dbo.CollectionEvent.CollectionDate IS NULL AND 
                      dbo.CollectionEvent.CollectionYear IS NULL AND dbo.CollectionEvent.CollectionMonth IS NULL AND dbo.CollectionEvent.CollectionDay IS NULL 
                      THEN NULL ELSE CASE WHEN dbo.CollectionEvent.CollectionDate IS NULL THEN CASE WHEN dbo.CollectionEvent.CollectionYear IS NULL 
                      THEN '-' ELSE CAST(dbo.CollectionEvent.CollectionYear AS varchar) END + '/' + CASE WHEN dbo.CollectionEvent.CollectionMonth IS NULL 
                      THEN '-' ELSE CAST(dbo.CollectionEvent.CollectionMonth AS varchar) END + '/' + CASE WHEN dbo.CollectionEvent.CollectionDay IS NULL 
                      THEN '-' ELSE CAST(dbo.CollectionEvent.CollectionDay AS varchar) END ELSE CAST(year(dbo.CollectionEvent.CollectionDate) AS varchar) 
                      + '/' + CAST(month(dbo.CollectionEvent.CollectionDate) AS varchar) + '/' + CAST(day(dbo.CollectionEvent.CollectionDate) AS varchar) 
                      END END + CASE WHEN dbo.CollectionEvent.LocalityDescription IS NULL 
                      THEN '' ELSE '   ' + dbo.CollectionEvent.LocalityDescription END AS CollectionDate, dbo.CollectionEvent.LocalityDescription AS Locality, 
                      dbo.CollectionEvent.HabitatDescription AS Habitat, dbo.CollectionSpecimen.InternalNotes, dbo.CollectionSpecimen.ExternalDatasourceID, 
                      dbo.CollectionSpecimen.ExternalIdentifier
FROM         dbo.CollectionSpecimen INNER JOIN
                      dbo.CollectionSpecimenID_UserAvailable ON 
                      dbo.CollectionSpecimen.CollectionSpecimenID = dbo.CollectionSpecimenID_UserAvailable.CollectionSpecimenID LEFT OUTER JOIN
                      dbo.CollectionEvent ON dbo.CollectionSpecimen.CollectionEventID = dbo.CollectionEvent.CollectionEventID
GO



GRANT SELECT ON [dbo].[CollectionSpecimen_Core] TO [DiversityCollectionUser]
GO


--#####################################################################################################################


IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[FirstLinesCollectionAgent]'))
DROP VIEW [dbo].[FirstLinesCollectionAgent]
GO


CREATE VIEW [dbo].[FirstLinesCollectionAgent]
AS
SELECT     CollectionSpecimenID, CollectorsName, CollectorsAgentURI, CollectorsSequence, CollectorsNumber, Notes, DataWithholdingReason
FROM         dbo.CollectionAgent AS Agent
WHERE     EXISTS
                          (SELECT     CollectionSpecimenID
                            FROM          dbo.CollectionAgent AS A
                            GROUP BY CollectionSpecimenID
                            HAVING      (Agent.CollectionSpecimenID = CollectionSpecimenID) AND (MIN(CollectorsSequence) = Agent.CollectorsSequence))
GO


GRANT SELECT ON [dbo].[FirstLinesCollectionAgent] TO [DiversityCollectionUser]
GO



--#####################################################################################################################


declare @i int
set @i = (select count(*) from CollTypeStatus_Enum where Code = 'original material')
if (@i = 0)
begin
INSERT INTO CollTypeStatus_Enum
           ([Code]
           ,[Description]
           ,[DisplayText]
           ,[DisplayOrder]
           ,[DisplayEnable]
           )
     VALUES
           ('original material'
           ,'Specimens and illustrations indicated in the protologue of a name (see ICBN Art. 9 Note 2 for details)'
           ,'original material'
           ,24
           ,1
           )
END
GO

--#####################################################################################################################

declare @i int
set @i = (select count(*) from LocalisationSystem where LocalisationSystemID = 13)
if (@i = 0)
begin
INSERT INTO LocalisationSystem
           (LocalisationSystemID
           ,LocalisationSystemName
           ,ParsingMethodName
           ,DisplayText
           ,DisplayEnable
           ,DisplayOrder
           ,[Description]
           )
     VALUES
           (13
           ,'Sampling plot'
           ,'SamplingPlot'
           ,'Sampling plot'
           ,1
           ,20
           ,'Either user defined Sampling plots or named geographical areas e.g. Capensis'
           )
END
GO

--#####################################################################################################################
use DiversityCollection

ALTER VIEW [dbo].[FirstLinesCollectionSpecimen]
AS
SELECT     CollectionSpecimen.CollectionSpecimenID, CollectionSpecimen.AccessionNumber AS Accession_number, 
                      CollectionSpecimen.DataWithholdingReason AS Data_withholding_reason, 
                      CollectionEvent.DataWithholdingReason AS Data_withholding_reason_for_collection_event, 
                      FirstLinesCollectionAgent.DataWithholdingReason AS Data_withholding_reason_for_collector, 
                      CollectionEvent.CollectorsEventNumber AS Collectors_event_number, CollectionEvent.CollectionDay AS Collection_day, 
                      CollectionEvent.CollectionMonth AS Collection_month, CollectionEvent.CollectionYear AS Collection_year, 
                      CollectionEvent.CollectionDateSupplement AS Collection_date_supplement, CollectionEvent.CollectionTime AS Collection_time, 
                      CollectionEvent.CollectionTimeSpan AS Collection_time_span, CollectionEvent.CountryCache AS Country, 
                      CollectionEvent.LocalityDescription AS Locality_description, CollectionEvent.HabitatDescription AS Habitat_description, 
                      CollectionEvent.CollectingMethod AS Collecting_method, CollectionEvent.Notes AS Collection_event_notes, 
                      FirstLinesNamedArea.Location1 AS Named_area, FirstLinesNamedArea.DistanceToLocation AS Distance_to_location, 
                      FirstLinesNamedArea.DirectionToLocation AS Direction_to_location, FirstLinesCoordinatesWGS84.Location1 AS Longitude, 
                      FirstLinesCoordinatesWGS84.Location2 AS Latitude, NULL AS Link_to_GoogleMaps, FirstLinesAltitude.Location1 AS Altitude_from, 
                      FirstLinesAltitude.Location2 AS Altitude_to, FirstLinesAltitude.LocationAccuracy AS Altitude_accuracy, FirstLinesMTB.Location1 AS MTB, 
                      FirstLinesMTB.Location2 AS Quadrant, FirstLinesMTB.LocationNotes AS Notes_for_MTB, 
                      FirstLinesGeographicRegion.DisplayText AS Geographic_region, FirstLinesLithostratigraphy.DisplayText AS Lithostratigraphy, 
                      FirstLinesChronostratigraphy.DisplayText AS Chronostratigraphy, FirstLinesCollectionAgent.CollectorsName AS Collectors_name, 
                      FirstLinesCollectionAgent.CollectorsAgentURI AS Link_to_DiversityAgents, FirstLinesCollectionAgent.CollectorsNumber AS Collectors_number, 
                      FirstLinesCollectionAgent.Notes AS Notes_about_collector, CollectionSpecimen.AccessionDay AS Accession_day, 
                      CollectionSpecimen.AccessionMonth AS Accession_month, CollectionSpecimen.AccessionYear AS Accession_year, 
                      CollectionSpecimen.AccessionDateSupplement AS Accession_date_supplement, CollectionSpecimen.DepositorsName AS Depositors_name, 
                      CollectionSpecimen.DepositorsAgentURI AS Depositors_link_to_DiversityAgents, 
                      CollectionSpecimen.DepositorsAccessionNumber AS Depositors_accession_number, 
                      CollectionSpecimen.ExsiccataAbbreviation AS Exsiccata_abbreviation, CollectionSpecimen.ExsiccataURI AS Link_to_DiversityExsiccatae, 
                      FirstLinesIdentificationUnit.ExsiccataNumber AS Exsiccata_number, CollectionSpecimen.OriginalNotes AS Original_notes, 
                      CollectionSpecimen.AdditionalNotes AS Additional_notes, CollectionSpecimen.InternalNotes AS Internal_notes, 
                      CollectionSpecimen.LabelTitle AS Label_title, CollectionSpecimen.LabelType AS Label_type, 
                      CollectionSpecimen.LabelTranscriptionState AS Label_transcription_state, CollectionSpecimen.LabelTranscriptionNotes AS Label_transcription_notes, 
                      CollectionSpecimen.Problems, FirstLinesIdentificationUnit.TaxonomicGroup AS Taxonomic_group, 
                      FirstLinesIdentificationUnit.RelationType AS Relation_type, FirstLinesIdentificationUnit.ColonisedSubstratePart AS Colonised_substrate_part, 
                      FirstLinesIdentificationUnit.LifeStage AS Life_stage, FirstLinesIdentificationUnit.Gender, 
                      FirstLinesIdentificationUnit.NumberOfUnits AS Number_of_units, FirstLinesIdentificationUnit.Circumstances, 
                      FirstLinesIdentificationUnit.OrderCache AS Order_of_taxon, FirstLinesIdentificationUnit.FamilyCache AS Family_of_taxon, 
                      FirstLinesIdentificationUnit.UnitIdentifier AS Identifier_of_organism, FirstLinesIdentificationUnit.UnitDescription AS Description_of_organism, 
                      FirstLinesIdentificationUnit.OnlyObserved AS Only_observed, FirstLinesIdentificationUnit.Notes AS Notes_for_organism, 
                      FirstLinesIdentification.TaxonomicName AS Taxonomic_name, FirstLinesIdentification.NameURI AS Link_to_DiversityTaxonNames, 
                      FirstLinesIdentification.VernacularTerm AS Vernacular_term, FirstLinesIdentification.IdentificationDay AS Identification_day, 
                      FirstLinesIdentification.IdentificationMonth AS Identification_month, FirstLinesIdentification.IdentificationYear AS Identification_year, 
                      FirstLinesIdentification.IdentificationQualifier AS Identification_qualifier, FirstLinesIdentification.TypeStatus AS Type_status, 
                      FirstLinesIdentification.TypeNotes AS Type_notes, FirstLinesIdentification.Notes AS Notes_for_identification, 
                      FirstLinesIdentification.ReferenceTitle AS Reference_title, FirstLinesIdentification.ReferenceURI AS Link_to_DiversityReferences, 
                      FirstLinesIdentification.ResponsibleName AS Responsible, FirstLinesIdentification.ResponsibleAgentURI AS Link_to_DiversityAgents_for_responsible, 
                      FirstLinesSecondUnit.TaxonomicGroup AS Taxonomic_group_of_second_organism, 
                      FirstLinesSecondUnitIdentification.TaxonomicName AS Taxonomic_name_of_second_organism, 
                      FirstLinesSecondUnitIdentification.NameURI AS Link_to_DiversityTaxonNames_of_second_organism, 
                      FirstLinesSecondUnitIdentification.VernacularTerm AS Vernacular_term_of_second_organism, 
                      FirstLinesCollectionSpecimenPart.CollectionID AS Collection, FirstLinesCollectionSpecimenPart.MaterialCategory AS Material_category, 
                      FirstLinesCollectionSpecimenPart.StorageLocation AS Storage_location, FirstLinesCollectionSpecimenPart.Stock, 
                      FirstLinesCollectionSpecimenPart.PreparationMethod AS Preparation_method, FirstLinesCollectionSpecimenPart.PreparationDate AS Preparation_date, 
                      FirstLinesCollectionSpecimenPart.Notes AS Notes_for_part, FirstLinesCollectionSpecimenTransaction._TransactionID, 
                      FirstLinesCollectionSpecimenTransaction._Transaction, FirstLinesCollectionSpecimenOnLoan._TransactionID AS On_loan, 
                      CollectionEvent.CollectionEventID AS _CollectionEventID, FirstLinesIdentificationUnit.IdentificationUnitID AS _IdentificationUnitID, 
                      FirstLinesIdentification.IdentificationSequence AS _IdentificationSequence, FirstLinesSecondUnit.IdentificationUnitID AS _SecondUnitID, 
                      FirstLinesSecondUnitIdentification.IdentificationSequence AS _SecondSequence, 
                      FirstLinesCollectionSpecimenPart.SpecimenPartID AS _SpecimenPartID, 
                      FirstLinesCoordinatesWGS84.LocationAccuracy AS _CoordinatesLocationAccuracy, 
                      FirstLinesCoordinatesWGS84.AverageLatitudeCache AS _CoordinatesAverageLatitudeCache, 
                      FirstLinesCoordinatesWGS84.AverageLongitudeCache AS _CoordinatesAverageLongitudeCache, 
                      FirstLinesCoordinatesWGS84.LocationNotes AS _CoordinatesLocationNotes, 
                      FirstLinesGeographicRegion.PropertyURI AS _GeographicRegionPropertyURI, 
                      FirstLinesLithostratigraphy.PropertyURI AS _LithostratigraphyPropertyURI, 
                      FirstLinesChronostratigraphy.PropertyURI AS _ChronostratigraphyPropertyURI, FirstLinesNamedArea.Location2 AS _NamedAreaLocation2, 
                      FirstLinesNamedArea.AverageLatitudeCache AS _NamedAverageLatitudeCache, 
                      FirstLinesNamedArea.AverageLongitudeCache AS _NamedAverageLongitudeCache, 
                      FirstLinesLithostratigraphy.PropertyHierarchyCache AS _LithostratigraphyPropertyHierarchyCache, 
                      FirstLinesChronostratigraphy.PropertyHierarchyCache AS _ChronostratigraphyPropertyHierarchyCache, 
                      FirstLinesSecondUnit.FamilyCache AS _SecondUnitFamilyCache, FirstLinesSecondUnit.OrderCache AS _SecondUnitOrderCache, 
                      FirstLinesAltitude.AverageAltitudeCache AS _AverageAltitudeCache
FROM         FirstLinesCollectionSpecimenTransaction RIGHT OUTER JOIN
                      FirstLinesCollectionSpecimenOnLoan RIGHT OUTER JOIN
                      CollectionSpecimenID_UserAvailable INNER JOIN
                      CollectionSpecimen ON CollectionSpecimenID_UserAvailable.CollectionSpecimenID = CollectionSpecimen.CollectionSpecimenID ON 
                      FirstLinesCollectionSpecimenOnLoan.CollectionSpecimenID = CollectionSpecimen.CollectionSpecimenID ON 
                      FirstLinesCollectionSpecimenTransaction.CollectionSpecimenID = CollectionSpecimen.CollectionSpecimenID LEFT OUTER JOIN
                      FirstLinesIdentificationUnit LEFT OUTER JOIN
                      FirstLinesIdentification ON FirstLinesIdentificationUnit.CollectionSpecimenID = FirstLinesIdentification.CollectionSpecimenID AND 
                      FirstLinesIdentificationUnit.IdentificationUnitID = FirstLinesIdentification.IdentificationUnitID ON 
                      CollectionSpecimen.CollectionSpecimenID = FirstLinesIdentificationUnit.CollectionSpecimenID LEFT OUTER JOIN
                      FirstLinesSecondUnit LEFT OUTER JOIN
                      FirstLinesSecondUnitIdentification ON FirstLinesSecondUnit.CollectionSpecimenID = FirstLinesSecondUnitIdentification.CollectionSpecimenID AND 
                      FirstLinesSecondUnit.IdentificationUnitID = FirstLinesSecondUnitIdentification.IdentificationUnitID ON 
                      CollectionSpecimen.CollectionSpecimenID = FirstLinesSecondUnit.CollectionSpecimenID LEFT OUTER JOIN
                      FirstLinesCollectionAgent ON CollectionSpecimen.CollectionSpecimenID = FirstLinesCollectionAgent.CollectionSpecimenID LEFT OUTER JOIN
                      FirstLinesAltitude RIGHT OUTER JOIN
                      FirstLinesMTB RIGHT OUTER JOIN
                      FirstLinesChronostratigraphy RIGHT OUTER JOIN
                      CollectionEvent ON FirstLinesChronostratigraphy.CollectionEventID = CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      FirstLinesLithostratigraphy ON CollectionEvent.CollectionEventID = FirstLinesLithostratigraphy.CollectionEventID ON 
                      FirstLinesMTB.CollectionEventID = CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      FirstLinesGeographicRegion ON CollectionEvent.CollectionEventID = FirstLinesGeographicRegion.CollectionEventID ON 
                      FirstLinesAltitude.CollectionEventID = CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      FirstLinesCoordinatesWGS84 ON CollectionEvent.CollectionEventID = FirstLinesCoordinatesWGS84.CollectionEventID LEFT OUTER JOIN
                      FirstLinesNamedArea ON CollectionEvent.CollectionEventID = FirstLinesNamedArea.CollectionEventID ON 
                      CollectionSpecimen.CollectionEventID = CollectionEvent.CollectionEventID LEFT OUTER JOIN
                      FirstLinesCollectionSpecimenPart ON CollectionSpecimen.CollectionSpecimenID = FirstLinesCollectionSpecimenPart.CollectionSpecimenID


GO


--#####################################################################################################################



ALTER FUNCTION [dbo].[Version] ()  
RETURNS nvarchar(8)
AS
BEGIN
RETURN '02.05.03'
END

