/*

Navicat SQL Server Data Transfer

Source Server : db5-emswin-neotomaDev

Source Server Version : 110000

Source Host : db5.emswin.psu.edu:1433

Source Database : Neotoma

Source Schema : TI

Target Server Type : SQL Server

Target Server Version : 110000

File Encoding : 65001

Date: 2018-06-27 14:34:15

*/

-- ----------------------------

-- Procedure structure for EPD_GetAgeBasis

-- ----------------------------

CREATE PROCEDURE [EPD_GetAgeBasis](@ENTITYNR int, @CHRONNR int)

AS

SELECT TOP (100) PERCENT DepthCM, Thickness, Age, AgeUp, AgeLo, RCode

FROM EPD.dbo.AGEBASIS

WHERE (E# = @ENTITYNR) AND (Chron# = @CHRONNR)

ORDER BY DepthCM

GO

-- ----------------------------

-- Procedure structure for EPD_GetAllSites

-- ----------------------------

CREATE PROCEDURE [EPD_GetAllSites]

AS

SELECT EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation, EPD.dbo.SITELOC.AreaOfSite,

EPD.dbo.POLDIV1.Name AS PolDiv1, EPD.dbo.POLDIV2.Name AS PolDiv2, EPD.dbo.POLDIV3.Name AS PolDiv3

FROM EPD.dbo.SITELOC LEFT OUTER JOIN

EPD.dbo.POLDIV3 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV3.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV3.PolDiv2 AND

EPD.dbo.SITELOC.PolDiv3 = EPD.dbo.POLDIV3.PolDiv3 LEFT OUTER JOIN

EPD.dbo.POLDIV2 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV2.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV2.PolDiv2 LEFT OUTER JOIN

EPD.dbo.POLDIV1 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV1.PolDiv1

ORDER BY EPD.dbo.SITELOC.Site#

GO

-- ----------------------------

-- Procedure structure for EPD_GetAuthors

-- ----------------------------

CREATE PROCEDURE [EPD_GetAuthors](@PUBLNR int)

AS

SELECT TOP (100) PERCENT EPD.dbo.AUTHORS.[Order], EPD.dbo.WORKERS.Worker#

FROM EPD.dbo.AUTHORS INNER JOIN

EPD.dbo.WORKERS ON EPD.dbo.AUTHORS.Author# = EPD.dbo.WORKERS.Worker#

WHERE (EPD.dbo.AUTHORS.Publ# = @PUBLNR)

ORDER BY EPD.dbo.AUTHORS.[Order]

GO

-- ----------------------------

-- Procedure structure for EPD_GetC14

-- ----------------------------

CREATE PROCEDURE [EPD_GetC14](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT Sample#, AgeBP, AgeSDUp, AgeSDLo, GrThanAge, Basis, LabNumber, DeltaC13, Notes

FROM EPD.dbo.C14

WHERE (E# = @ENTITYNR)

ORDER BY Sample#

GO

-- ----------------------------

-- Procedure structure for EPD_GetChron

-- ----------------------------

CREATE PROCEDURE [EPD_GetChron](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT EPD.dbo.CHRON.Chron#, EPD.dbo.CHRON.[Default], EPD.dbo.CHRON.Name, EPD.dbo.CHRON.DatePrepared, EPD.dbo.CHRON.Model,

EPD.dbo.CHRON.Notes, EPD.dbo.CHRON_PreparedBy.NeotomaContactID, EPD.dbo.CHRON_PreparedBy.Worker#,

EPD.dbo.AGEBOUND.[Top], EPD.dbo.AGEBOUND.Bottom

FROM EPD.dbo.CHRON LEFT OUTER JOIN

EPD.dbo.CHRON_PreparedBy ON EPD.dbo.CHRON.PreparedBy = EPD.dbo.CHRON_PreparedBy.PreparedBy LEFT OUTER JOIN

EPD.dbo.AGEBOUND ON EPD.dbo.CHRON.E# = EPD.dbo.AGEBOUND.E# AND EPD.dbo.CHRON.Chron# = EPD.dbo.AGEBOUND.Chron#

WHERE (EPD.dbo.CHRON.E# = @ENTITYNR)

ORDER BY EPD.dbo.CHRON.Chron#

GO

-- ----------------------------

-- Procedure structure for EPD_GetCountriesWithSitesNotInNeotoma

-- ----------------------------

CREATE PROCEDURE [EPD_GetCountriesWithSitesNotInNeotoma]

AS

SELECT TOP (100) PERCENT EPD.dbo.POLDIV1.Name AS PolDiv1, NDB.GeoPoliticalUnits.GeoPoliticalID

FROM EPD.dbo.P_ENTITY INNER JOIN

EPD.dbo.ENTITY ON EPD.dbo.P_ENTITY.E# = EPD.dbo.ENTITY.E# INNER JOIN

EPD.dbo.SITELOC ON EPD.dbo.ENTITY.Site# = EPD.dbo.SITELOC.Site# INNER JOIN

EPD.dbo.POLDIV1 INNER JOIN

NDB.GeoPoliticalUnits ON EPD.dbo.POLDIV1.Name = NDB.GeoPoliticalUnits.GeoPoliticalName ON

EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV1.PolDiv1 LEFT OUTER JOIN

EPD.dbo.EntityDatasetID ON EPD.dbo.ENTITY.E# = EPD.dbo.EntityDatasetID.E#

WHERE (EPD.dbo.EntityDatasetID.E# IS NULL) AND (EPD.dbo.ENTITY.E# > 722)

GROUP BY EPD.dbo.POLDIV1.Name, NDB.GeoPoliticalUnits.Rank, NDB.GeoPoliticalUnits.GeoPoliticalID, EPD.dbo.P_ENTITY.UseStatus

HAVING (EPD.dbo.P_ENTITY.UseStatus = N'U') AND (NDB.GeoPoliticalUnits.Rank = 1)

ORDER BY PolDiv1

/*

SELECT TOP (100) PERCENT EPD.dbo.POLDIV1.Name AS PolDiv1, NDB.GeoPoliticalUnits.GeoPoliticalID

FROM EPD.dbo.P_ENTITY INNER JOIN

EPD.dbo.ENTITY INNER JOIN

EPD.dbo.SITELOC ON EPD.dbo.ENTITY.Site# = EPD.dbo.SITELOC.Site# ON EPD.dbo.P_ENTITY.E# = EPD.dbo.ENTITY.E# LEFT OUTER JOIN

NDB.Sites ON EPD.dbo.SITELOC.SiteName = NDB.Sites.SiteName LEFT OUTER JOIN

EPD.dbo.POLDIV1 LEFT OUTER JOIN

NDB.GeoPoliticalUnits ON EPD.dbo.POLDIV1.Name = NDB.GeoPoliticalUnits.GeoPoliticalName ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV1.PolDiv1

WHERE (NDB.Sites.SiteID IS NULL)

GROUP BY EPD.dbo.POLDIV1.Name, NDB.GeoPoliticalUnits.Rank, NDB.GeoPoliticalUnits.GeoPoliticalID, EPD.dbo.P_ENTITY.UseStatus

HAVING (NDB.GeoPoliticalUnits.Rank = 1) AND (EPD.dbo.P_ENTITY.UseStatus = N'U')

ORDER BY PolDiv1

*/

GO

-- ----------------------------

-- Procedure structure for EPD_GetCountry

-- ----------------------------

CREATE PROCEDURE [EPD_GetCountry](@POLDIV1 nvarchar(3))

AS

SELECT Name

FROM EPD.dbo.POLDIV1

WHERE (PolDiv1 = @POLDIV1)

GO

-- ----------------------------

-- Procedure structure for EPD_GetEntitiesBySiteNr

-- ----------------------------

CREATE PROCEDURE [EPD_GetEntitiesBySiteNr](@SITENR int)

AS

SELECT EPD.dbo.ENTITY.E#, EPD.dbo.ENTITY.Sigle, EPD.dbo.ENTITY.Name

FROM EPD.dbo.SITELOC INNER JOIN

EPD.dbo.ENTITY ON EPD.dbo.SITELOC.Site# = EPD.dbo.ENTITY.Site# INNER JOIN

EPD.dbo.P_ENTITY ON EPD.dbo.ENTITY.E# = EPD.dbo.P_ENTITY.E# LEFT OUTER JOIN

EPD.dbo.EntityDatasetID ON EPD.dbo.ENTITY.E# = EPD.dbo.EntityDatasetID.E#

WHERE (EPD.dbo.P_ENTITY.UseStatus = N'U') AND (EPD.dbo.SITELOC.Site# = @SITENR) AND (EPD.dbo.ENTITY.E# > 722) AND (EPD.dbo.EntityDatasetID.DatasetID IS NULL)

/*

SELECT EPD.dbo.ENTITY.E#, EPD.dbo.ENTITY.Sigle, EPD.dbo.ENTITY.Name

FROM EPD.dbo.SITELOC INNER JOIN

EPD.dbo.ENTITY ON EPD.dbo.SITELOC.Site# = EPD.dbo.ENTITY.Site# INNER JOIN

EPD.dbo.P_ENTITY ON EPD.dbo.ENTITY.E# = EPD.dbo.P_ENTITY.E#

WHERE (EPD.dbo.P_ENTITY.UseStatus = N'U') AND (EPD.dbo.SITELOC.Site# = @SITENR)

*/

GO

-- ----------------------------

-- Procedure structure for EPD_GetEntity

-- ----------------------------

CREATE PROCEDURE [EPD_GetEntity](@ENTITYNR int)

AS

SELECT EPD.dbo.ENTITY.Sigle, EPD.dbo.ENTITY.Name, EPD.dbo.ENTITY.IsCore, EPD.dbo.ENTITY.IsSect, EPD.dbo.ENTITY.IsSSamp, EPD.dbo.DESCR.DepEnvt, EPD.dbo.ENTITY.EntLoc, EPD.dbo.ENTITY.LocalVeg,

EPD.dbo.ENTITY.Coll#, EPD.dbo.ENTITY.SampDate, EPD.dbo.ENTITY.DepthAtLoc, EPD.dbo.ENTITY.SampDevice, EPD.dbo.ENTITY.CoreDiamCM, EPD.dbo.ENTITY.Notes

FROM EPD.dbo.ENTITY INNER JOIN

EPD.dbo.DESCR ON EPD.dbo.ENTITY.Descriptor = EPD.dbo.DESCR.Descriptor

WHERE (EPD.dbo.ENTITY.E# = @ENTITYNR)

GO

-- ----------------------------

-- Procedure structure for EPD_GetGeochron

-- ----------------------------

CREATE PROCEDURE [EPD_GetGeochron](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT Sample#, Method, DepthCM, Thickness, MaterialDated, Publ#

FROM EPD.dbo.GEOCHRON

WHERE (E# = @ENTITYNR)

ORDER BY DepthCM

GO

-- ----------------------------

-- Procedure structure for EPD_GetLithology

-- ----------------------------

CREATE PROCEDURE [EPD_GetLithology](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT Descript, DepthTopCM, DepthBotCM, LoBoundary

FROM EPD.dbo.LITHOLGY

WHERE (E# = @ENTITYNR)

ORDER BY Lith#

GO

-- ----------------------------

-- Procedure structure for EPD_GetLOI

-- ----------------------------

CREATE PROCEDURE [EPD_GetLOI](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT DepthCM, Thickness, TempLo, LOILo, TempHi, LOIHi, BulkDens

FROM EPD.dbo.LOI

WHERE (E# = @ENTITYNR)

ORDER BY DepthCM

GO

-- ----------------------------

-- Procedure structure for EPD_GetMADCAPAgeModel

-- ----------------------------

CREATE PROCEDURE [EPD_GetMADCAPAgeModel](@SIGLE nvarchar(255))

AS

SELECT AgeModelType

FROM EPD.dbo.MADCAP_AgeModels

WHERE (Sigle = @SIGLE)

GO

-- ----------------------------

-- Procedure structure for EPD_GetMADCAPChronControls

-- ----------------------------

CREATE PROCEDURE [EPD_GetMADCAPChronControls](@SIGLE nvarchar(255))

AS

SELECT DepthCM, Thickness, RCode, LabNr, Age14C, AgeCal, SD, Reservoir

FROM EPD.dbo.MADCAP_ChronControls

WHERE (Sigle = @SIGLE)

GO

-- ----------------------------

-- Procedure structure for EPD_GetMADCAPContactNotes

-- ----------------------------

CREATE PROCEDURE [EPD_GetMADCAPContactNotes](@SIGLE nvarchar(255))

AS

SELECT WorkerNr, Notes

FROM EPD.dbo.MADCAP_ContactsNotes

WHERE (Sigle = @SIGLE)

GO

-- ----------------------------

-- Procedure structure for EPD_GetMADCAPReliableAges

-- ----------------------------

CREATE PROCEDURE [EPD_GetMADCAPReliableAges](@SIGLE nvarchar(255))

AS

SELECT TOP (100) PERCENT MIN(Age) AS MinAge, MAX(Age) AS MaxAge

FROM EPD.dbo.MADCAP_ReliableAges

WHERE (Sigle = @SIGLE)

GO

-- ----------------------------

-- Procedure structure for EPD_GetMADCAPSampleAges

-- ----------------------------

CREATE PROCEDURE [EPD_GetMADCAPSampleAges](@SIGLE nvarchar(255))

AS

SELECT TOP (100) PERCENT DepthCM, Age, AgeMin, AgeMax

FROM EPD.dbo.MADCAP_AllAges

WHERE (Sigle = @SIGLE)

ORDER BY DepthCM

GO

-- ----------------------------

-- Procedure structure for EPD_GetPAgeDpt

-- ----------------------------

CREATE PROCEDURE [EPD_GetPAgeDpt](@ENTITYNR int, @CHRONNR int)

AS

SELECT Sample#, AgeBP, AgeUp, AgeLo

FROM EPD.dbo.P_AGEDPT

WHERE (E# = @ENTITYNR) AND (Chron# = @CHRONNR)

GO

-- ----------------------------

-- Procedure structure for EPD_GetPb210

-- ----------------------------

CREATE PROCEDURE [EPD_GetPb210](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT Sample#, AgeAD, AgeSE, GrThanAge, Notes

FROM EPD.dbo.PB210

WHERE (E# = @ENTITYNR)

ORDER BY Sample#

GO

-- ----------------------------

-- Procedure structure for EPD_GetPCounts

-- ----------------------------

CREATE PROCEDURE [EPD_GetPCounts](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT Sample#, Var#, Count

FROM EPD.dbo.P_COUNTS

WHERE (E# = @ENTITYNR)

GO

-- ----------------------------

-- Procedure structure for EPD_GetPEntity

-- ----------------------------

CREATE PROCEDURE [EPD_GetPEntity](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT Contact#, DataSource

FROM EPD.dbo.P_ENTITY

WHERE (E# = @ENTITYNR)

GO

-- ----------------------------

-- Procedure structure for EPD_GetPSample

-- ----------------------------

CREATE PROCEDURE [EPD_GetPSample](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT Sample#, DepthCM, Thickness, Analyst#, AnalyDate, Notes

FROM EPD.dbo.P_SAMPLE

WHERE (E# = @ENTITYNR)

GO

-- ----------------------------

-- Procedure structure for EPD_GetPubl

-- ----------------------------

CREATE PROCEDURE [EPD_GetPubl](@PUBLNR int)

AS

SELECT TOP (100) PERCENT Publ#, YearOfPubl, Citation

FROM EPD.dbo.PUBL

WHERE (Publ# = @PUBLNR)

GO

-- ----------------------------

-- Procedure structure for EPD_GetPublEnt

-- ----------------------------

CREATE PROCEDURE [EPD_GetPublEnt](@ENTITYNR int)

AS

SELECT EPD.dbo.PUBL.Publ#, EPD.dbo.PUBL.YearOfPubl, EPD.dbo.PUBL.Citation

FROM EPD.dbo.PUBLENT INNER JOIN

EPD.dbo.PUBL ON EPD.dbo.PUBLENT.Publ# = EPD.dbo.PUBL.Publ#

WHERE (EPD.dbo.PUBLENT.E# = @ENTITYNR)

GO

-- ----------------------------

-- Procedure structure for EPD_GetPVars

-- ----------------------------

CREATE PROCEDURE [EPD_GetPVars](@ENTITYNR int)

AS

DECLARE @COUNT int

DECLARE @VARS TABLE

(

ID int NOT NULL primary key identity(1,1),

Var# int,

VarName nvarchar(80),

TaxonCode nvarchar(64),

TaxonName nvarchar(80),

Element nvarchar(255),

Units nvarchar(64),

Context nvarchar(64),

Taphonomy nvarchar(64),

EcolGroup nvarchar(4)

)

INSERT INTO @VARS (Var#, VarName, TaxonName, Element, Units, Context, Taphonomy)

SELECT TOP (100) PERCENT EPD.dbo.P_VARS.Var#, EPD.dbo.P_VARS.VarName, EPD.dbo.P_VARS_NeotomaTaxa.NeotomaTaxonName, EPD.dbo.P_VARS_NeotomaTaxa.Element,

EPD.dbo.P_VARS_NeotomaTaxa.Units, EPD.dbo.P_VARS_NeotomaTaxa.Context, EPD.dbo.P_VARS_NeotomaTaxa.Taphonomy

FROM EPD.dbo.P_COUNTS INNER JOIN

EPD.dbo.P_VARS ON EPD.dbo.P_COUNTS.Var# = EPD.dbo.P_VARS.Var# INNER JOIN

EPD.dbo.P_VARS_NeotomaTaxa ON EPD.dbo.P_VARS.Var# = EPD.dbo.P_VARS_NeotomaTaxa.Var#

GROUP BY EPD.dbo.P_COUNTS.E#, EPD.dbo.P_VARS.Var#, EPD.dbo.P_VARS.VarName, EPD.dbo.P_VARS_NeotomaTaxa.NeotomaTaxonName, EPD.dbo.P_VARS_NeotomaTaxa.Element,

EPD.dbo.P_VARS_NeotomaTaxa.Units, EPD.dbo.P_VARS_NeotomaTaxa.Context, EPD.dbo.P_VARS_NeotomaTaxa.Taphonomy

HAVING (EPD.dbo.P_COUNTS.E# = @ENTITYNR)

ORDER BY EPD.dbo.P_VARS_NeotomaTaxa.NeotomaTaxonName

DECLARE @NROWS int = @@ROWCOUNT

DECLARE @CURRENTID int = 0

DECLARE @VARNAME nvarchar(80)

DECLARE @TAXONNAME nvarchar(80)

DECLARE @TAXONCODE nvarchar(64)

DECLARE @ECOLGROUP nvarchar(4)

DECLARE @SYNONYM nvarchar(80)

WHILE @CURRENTID < @NROWS

BEGIN

SET @CURRENTID = @CURRENTID+1

SET @TAXONNAME = (SELECT TaxonName FROM @VARS WHERE ID = @CURRENTID)

SET @COUNT = (SELECT COUNT(TaxonCode) FROM NDB.Taxa WHERE (TaxonName = @TAXONNAME))

IF @COUNT > 1

SET @TAXONCODE = (SELECT NDB.Taxa.TaxonCode FROM NDB.Taxa WHERE ((NDB.Taxa.TaxonName = @TAXONNAME)) AND (NDB.Taxa.TaxaGroupID = N'VPL'))

ELSE

SET @TAXONCODE = (SELECT NDB.Taxa.TaxonCode FROM NDB.Taxa WHERE (NDB.Taxa.TaxonName = @TAXONNAME))

IF @TAXONCODE IS NOT NULL

BEGIN

UPDATE @VARS

SET TaxonCode = @TAXONCODE

WHERE ID = @CURRENTID

END

SET @COUNT = (SELECT COUNT(NDB.EcolGroups.EcolGroupID) FROM NDB.Taxa INNER JOIN

NDB.EcolGroups ON NDB.Taxa.TaxonID = NDB.EcolGroups.TaxonID

WHERE (NDB.Taxa.TaxonName = @TAXONNAME))

IF @COUNT > 1

SET @ECOLGROUP = (SELECT NDB.EcolGroups.EcolGroupID

FROM NDB.Taxa INNER JOIN NDB.EcolGroups ON NDB.Taxa.TaxonID = NDB.EcolGroups.TaxonID

WHERE ((NDB.Taxa.TaxonName = @TAXONNAME) AND (NDB.Taxa.TaxaGroupID = N'VPL'))

GROUP BY NDB.EcolGroups.EcolGroupID)

ELSE

SET @ECOLGROUP = (SELECT NDB.EcolGroups.EcolGroupID

FROM NDB.Taxa INNER JOIN NDB.EcolGroups ON NDB.Taxa.TaxonID = NDB.EcolGroups.TaxonID

WHERE (NDB.Taxa.TaxonName = @TAXONNAME)

GROUP BY NDB.EcolGroups.EcolGroupID)

IF @ECOLGROUP IS NOT NULL

BEGIN

UPDATE @VARS

SET EcolGroup = @ECOLGROUP

WHERE ID = @CURRENTID

END

SET @VARNAME = (SELECT VarName FROM @VARS WHERE (ID = @CURRENTID))

SET @VARNAME = (SELECT REPLACE(@VARNAME,N'subfam.',N'subf.'))

SET @VARNAME = (SELECT REPLACE(@VARNAME,N'subgen.',N'subg.'))

SET @VARNAME = (SELECT REPLACE(@VARNAME,N'undifferentiated',N'undiff.'))

IF @VARNAME = @TAXONNAME

BEGIN

UPDATE @VARS

SET VarName = null

WHERE ID = @CURRENTID

END

ELSE

BEGIN

SET @SYNONYM = (SELECT NDB.Taxa.TaxonName FROM NDB.Taxa WHERE (NDB.Taxa.TaxonName = @VARNAME))

BEGIN

UPDATE @VARS

SET VarName = @SYNONYM

WHERE ID = @CURRENTID

END

END

END

SELECT TOP (100) PERCENT Var#, VarName, TaxonCode, TaxonName, Element, Units, Context, Taphonomy, EcolGroup

FROM @VARS

ORDER BY TaxonName

GO

-- ----------------------------

-- Procedure structure for EPD_GetSiteByName

-- ----------------------------

CREATE PROCEDURE [EPD_GetSiteByName](@SITENAME nvarchar(128))

AS

SELECT TOP (100) PERCENT EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV1.Name AS PolDiv1, EPD.dbo.POLDIV2.Name AS PolDiv2, EPD.dbo.POLDIV3.Name AS PolDiv3,

EPD.dbo.P_ENTITY.UseStatus

FROM EPD.dbo.SITELOC INNER JOIN

EPD.dbo.ENTITY ON EPD.dbo.SITELOC.Site# = EPD.dbo.ENTITY.Site# INNER JOIN

EPD.dbo.P_ENTITY ON EPD.dbo.ENTITY.E# = EPD.dbo.P_ENTITY.E# LEFT OUTER JOIN

EPD.dbo.EntityDatasetID ON EPD.dbo.ENTITY.E# = EPD.dbo.EntityDatasetID.E# LEFT OUTER JOIN

EPD.dbo.POLDIV3 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV3.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV3.PolDiv2 AND

EPD.dbo.SITELOC.PolDiv3 = EPD.dbo.POLDIV3.PolDiv3 LEFT OUTER JOIN

EPD.dbo.POLDIV2 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV2.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV2.PolDiv2 LEFT OUTER JOIN

EPD.dbo.POLDIV1 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV1.PolDiv1

WHERE (EPD.dbo.EntityDatasetID.DatasetID IS NULL) AND (EPD.dbo.ENTITY.E# > 722)

GROUP BY EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV1.Name, EPD.dbo.POLDIV2.Name, EPD.dbo.POLDIV3.Name, EPD.dbo.P_ENTITY.UseStatus

HAVING (EPD.dbo.SITELOC.SiteName LIKE @SITENAME)

ORDER BY EPD.dbo.SITELOC.Site#

/*

SELECT TOP (100) PERCENT EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV1.Name AS PolDiv1, EPD.dbo.POLDIV2.Name AS PolDiv2, EPD.dbo.POLDIV3.Name AS PolDiv3,

EPD.dbo.P_ENTITY.UseStatus

FROM EPD.dbo.SITELOC INNER JOIN

EPD.dbo.ENTITY ON EPD.dbo.SITELOC.Site# = EPD.dbo.ENTITY.Site# INNER JOIN

EPD.dbo.P_ENTITY ON EPD.dbo.ENTITY.E# = EPD.dbo.P_ENTITY.E# LEFT OUTER JOIN

NDB.Sites ON EPD.dbo.SITELOC.SiteName = NDB.Sites.SiteName LEFT OUTER JOIN

EPD.dbo.POLDIV3 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV3.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV3.PolDiv2 AND

EPD.dbo.SITELOC.PolDiv3 = EPD.dbo.POLDIV3.PolDiv3 LEFT OUTER JOIN

EPD.dbo.POLDIV2 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV2.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV2.PolDiv2 LEFT OUTER JOIN

EPD.dbo.POLDIV1 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV1.PolDiv1

GROUP BY EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV1.Name, EPD.dbo.POLDIV2.Name, EPD.dbo.POLDIV3.Name, EPD.dbo.P_ENTITY.UseStatus, NDB.Sites.SiteID

HAVING (NDB.Sites.SiteID IS NULL) AND (EPD.dbo.SITELOC.SiteName LIKE @SITENAME)

ORDER BY EPD.dbo.SITELOC.Site#

*/

GO

-- ----------------------------

-- Procedure structure for EPD_GetSiteDescription

-- ----------------------------

CREATE PROCEDURE [EPD_GetSiteDescription](@SITENR int)

AS

SELECT SiteDescript, Physiography, SurroundVeg, VegFormation

FROM EPD.dbo.SITEDESC

WHERE (Site# = @SITENR)

GO

-- ----------------------------

-- Procedure structure for EPD_GetSitesInNeotoma

-- ----------------------------

CREATE PROCEDURE [EPD_GetSitesInNeotoma]

AS

SELECT TOP (100) PERCENT EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV2.Name AS PolDiv2, EPD.dbo.POLDIV3.Name AS PolDiv3, TI.GeoPol1.GeoPolName1, NDB.Sites.SiteID

FROM EPD.dbo.POLDIV3 RIGHT OUTER JOIN

EPD.dbo.SITELOC ON EPD.dbo.POLDIV3.PolDiv1 = EPD.dbo.SITELOC.PolDiv1 AND EPD.dbo.POLDIV3.PolDiv2 = EPD.dbo.SITELOC.PolDiv2 AND

EPD.dbo.POLDIV3.PolDiv3 = EPD.dbo.SITELOC.PolDiv3 LEFT OUTER JOIN

EPD.dbo.POLDIV2 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV2.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV2.PolDiv2 LEFT OUTER JOIN

NDB.Sites INNER JOIN

TI.GeoPol1 ON NDB.Sites.SiteID = TI.GeoPol1.SiteID INNER JOIN

EPD.dbo.POLDIV1 ON TI.GeoPol1.GeoPolName1 = EPD.dbo.POLDIV1.Name ON EPD.dbo.SITELOC.SiteName = NDB.Sites.SiteName AND

EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV1.PolDiv1

WHERE (NDB.Sites.SiteID IS NOT NULL)

ORDER BY EPD.dbo.SITELOC.Site#

GO

-- ----------------------------

-- Procedure structure for EPD_GetSitesNotInNeotoma

-- ----------------------------

CREATE PROCEDURE [EPD_GetSitesNotInNeotoma]

AS

SELECT TOP (100) PERCENT EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV1.Name AS PolDiv1, EPD.dbo.POLDIV2.Name AS PolDiv2, EPD.dbo.POLDIV3.Name AS PolDiv3

FROM EPD.dbo.POLDIV1 RIGHT OUTER JOIN

NDB.Sites RIGHT OUTER JOIN

EPD.dbo.POLDIV3 RIGHT OUTER JOIN

EPD.dbo.SITELOC ON EPD.dbo.POLDIV3.PolDiv1 = EPD.dbo.SITELOC.PolDiv1 AND EPD.dbo.POLDIV3.PolDiv2 = EPD.dbo.SITELOC.PolDiv2 AND

EPD.dbo.POLDIV3.PolDiv3 = EPD.dbo.SITELOC.PolDiv3 LEFT OUTER JOIN

EPD.dbo.POLDIV2 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV2.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV2.PolDiv2 ON

NDB.Sites.SiteName = EPD.dbo.SITELOC.SiteName ON EPD.dbo.POLDIV1.PolDiv1 = EPD.dbo.SITELOC.PolDiv1

WHERE (NDB.Sites.SiteID IS NULL)

ORDER BY EPD.dbo.SITELOC.Site#

GO

-- ----------------------------

-- Procedure structure for EPD_GetSitesNotInNeotomaByCountry

-- ----------------------------

CREATE PROCEDURE [EPD_GetSitesNotInNeotomaByCountry](@COUNTRY nvarchar(45))

AS

SELECT TOP (100) PERCENT EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV1.Name AS PolDiv1, EPD.dbo.POLDIV2.Name AS PolDiv2, EPD.dbo.POLDIV3.Name AS PolDiv3,

EPD.dbo.P_ENTITY.UseStatus

FROM EPD.dbo.SITELOC INNER JOIN

EPD.dbo.ENTITY ON EPD.dbo.SITELOC.Site# = EPD.dbo.ENTITY.Site# INNER JOIN

EPD.dbo.P_ENTITY ON EPD.dbo.ENTITY.E# = EPD.dbo.P_ENTITY.E# LEFT OUTER JOIN

EPD.dbo.EntityDatasetID ON EPD.dbo.ENTITY.E# = EPD.dbo.EntityDatasetID.E# LEFT OUTER JOIN

EPD.dbo.POLDIV3 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV3.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV3.PolDiv2 AND

EPD.dbo.SITELOC.PolDiv3 = EPD.dbo.POLDIV3.PolDiv3 LEFT OUTER JOIN

EPD.dbo.POLDIV2 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV2.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV2.PolDiv2 LEFT OUTER JOIN

EPD.dbo.POLDIV1 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV1.PolDiv1

WHERE (EPD.dbo.EntityDatasetID.DatasetID IS NULL) AND (EPD.dbo.ENTITY.E# > 722)

GROUP BY EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV1.Name, EPD.dbo.POLDIV2.Name, EPD.dbo.POLDIV3.Name, EPD.dbo.P_ENTITY.UseStatus

HAVING (EPD.dbo.POLDIV1.Name = @COUNTRY)

ORDER BY EPD.dbo.SITELOC.Site#

/*

SELECT TOP (100) PERCENT EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV1.Name AS PolDiv1, EPD.dbo.POLDIV2.Name AS PolDiv2, EPD.dbo.POLDIV3.Name AS PolDiv3,

EPD.dbo.P_ENTITY.UseStatus

FROM EPD.dbo.SITELOC INNER JOIN

EPD.dbo.ENTITY ON EPD.dbo.SITELOC.Site# = EPD.dbo.ENTITY.Site# INNER JOIN

EPD.dbo.P_ENTITY ON EPD.dbo.ENTITY.E# = EPD.dbo.P_ENTITY.E# LEFT OUTER JOIN

NDB.Sites ON EPD.dbo.SITELOC.SiteName = NDB.Sites.SiteName LEFT OUTER JOIN

EPD.dbo.POLDIV3 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV3.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV3.PolDiv2 AND

EPD.dbo.SITELOC.PolDiv3 = EPD.dbo.POLDIV3.PolDiv3 LEFT OUTER JOIN

EPD.dbo.POLDIV2 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV2.PolDiv1 AND EPD.dbo.SITELOC.PolDiv2 = EPD.dbo.POLDIV2.PolDiv2 LEFT OUTER JOIN

EPD.dbo.POLDIV1 ON EPD.dbo.SITELOC.PolDiv1 = EPD.dbo.POLDIV1.PolDiv1

GROUP BY EPD.dbo.SITELOC.Site#, EPD.dbo.SITELOC.SiteName, EPD.dbo.SITELOC.LatDD, EPD.dbo.SITELOC.LonDD, EPD.dbo.SITELOC.Elevation,

EPD.dbo.SITELOC.AreaOfSite, EPD.dbo.POLDIV1.Name, EPD.dbo.POLDIV2.Name, EPD.dbo.POLDIV3.Name, EPD.dbo.P_ENTITY.UseStatus, NDB.Sites.SiteID

HAVING (NDB.Sites.SiteID IS NULL) AND (EPD.dbo.POLDIV1.Name = @COUNTRY)

ORDER BY EPD.dbo.SITELOC.Site#

*/

GO

-- ----------------------------

-- Procedure structure for EPD_GetSynEvent

-- ----------------------------

CREATE PROCEDURE [EPD_GetSynEvent](@ENTITYNR int)

AS

SELECT EPD.dbo.SYNEVENT.DepthCM, EPD.dbo.SYNEVENT.Thickness, EPD.dbo.EVENT.Name, EPD.dbo.EVENT.AgeBP, EPD.dbo.EVENT.AgeUncertUp, EPD.dbo.EVENT.AgeUncertLo

FROM EPD.dbo.SYNEVENT INNER JOIN

EPD.dbo.EVENT ON EPD.dbo.SYNEVENT.Event# = EPD.dbo.EVENT.Event#

WHERE (EPD.dbo.SYNEVENT.E# = @ENTITYNR)

GO

-- ----------------------------

-- Procedure structure for EPD_GetTL

-- ----------------------------

CREATE PROCEDURE [EPD_GetTL](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT Sample#, AgeBP, ErrorLimits, GrainSize, LabNumber, Notes

FROM EPD.dbo.TL

WHERE (E# = @ENTITYNR)

ORDER BY Sample#

GO

-- ----------------------------

-- Procedure structure for EPD_GetUSeries

-- ----------------------------

CREATE PROCEDURE [EPD_GetUSeries](@ENTITYNR int)

AS

SELECT TOP (100) PERCENT Sample#, AgeBP, ErrorLimits, LabNumber, Notes

FROM EPD.dbo.USERIES

WHERE (E# = @ENTITYNR)

ORDER BY Sample#

GO

-- ----------------------------

-- Procedure structure for EPD_GetWorker

-- ----------------------------

CREATE PROCEDURE [EPD_GetWorker](@WORKERNR int)

AS

SELECT Status, LastName, Initials, FirstName, Suffix, Title, Country, Phone, Fax, EMailAddr, Address

FROM EPD.dbo.WORKERS

WHERE (Worker# = @WORKERNR)

GO

-- ----------------------------

-- Procedure structure for GetAgeTypeID

-- ----------------------------

CREATE PROCEDURE [GetAgeTypeID](@AGETYPE nvarchar(64))

AS

SELECT AgeTypeID

FROM NDB.AgeTypes

WHERE (AgeType = @AGETYPE)

GO

-- ----------------------------

-- Procedure structure for GetAgeTypesTable

-- ----------------------------

CREATE PROCEDURE [GetAgeTypesTable]

AS

SELECT AgeTypeID, AgeType, Precedence, ShortAgeType

FROM NDB.AgeTypes

GO

-- ----------------------------

-- Procedure structure for GetAggregateChronByDatasetID

-- ----------------------------

CREATE PROCEDURE [GetAggregateChronByDatasetID](@AGGREGATEDATASETID int)

AS

SELECT AggregateChronID, AggregateDatasetID, AgeTypeID, IsDefault, ChronologyName, AgeBoundYounger, AgeBoundOlder, Notes

FROM NDB.AggregateChronologies

WHERE (AggregateDatasetID = @AGGREGATEDATASETID)

GO

-- ----------------------------

-- Procedure structure for GetAggregateDatasetByName

-- ----------------------------

CREATE PROCEDURE [GetAggregateDatasetByName](@NAME nvarchar(255))

AS

SELECT AggregateDatasetID, AggregateDatasetName, AggregateOrderTypeID, Notes

FROM NDB.AggregateDatasets

WHERE (AggregateDatasetName = @NAME)

GO

-- ----------------------------

-- Procedure structure for GetAggregateOrderTypes

-- ----------------------------

CREATE PROCEDURE [GetAggregateOrderTypes]

AS

SELECT AggregateOrderTypeID, AggregateOrderType

FROM NDB.AggregateOrderTypes

GO

-- ----------------------------

-- Procedure structure for GetAliasContactNames

-- ----------------------------

CREATE PROCEDURE [GetAliasContactNames]

AS

SELECT Contacts_1.ContactID AS AliasContactID, NDB.Contacts.ContactName AS AliasContactName, NDB.Contacts.ContactID AS CurrentContactID,

Contacts_1.ContactName AS CurrentContactName

FROM NDB.Contacts INNER JOIN

NDB.Contacts AS Contacts_1 ON NDB.Contacts.AliasID = Contacts_1.ContactID

WHERE (NDB.Contacts.AliasID <> NDB.Contacts.ContactID)

GO

-- ----------------------------

-- Procedure structure for GetAllDatasetAuthors

-- ----------------------------