
CREATE VIEW [dbo].[SPICE_TaxonId2AccId_VIEW]
AS

-- for accepted taxa get their own taxonid as the accepted one
SELECT     dbo.PTaxon.RIdentifier as TaxonId, dbo.PTaxon.RIdentifier as AccTaxonId
FROM         dbo.PTaxon 
WHERE     dbo.PTaxon.StatusFk = 1 AND dbo.PTaxon.PTRefFk IN (select refid from SPICE_PublishedSecRefs)

UNION ALL

-- get the calculated accepted taxid for:
--   accepted taxa of other sec refs (misapplied names) 
--   non accepted taxa of the same sec ref
SELECT     dbo.PTaxon.RIdentifier, dbo.f_species2000_getAcceptedSpeciesID(dbo.PTaxon.RIdentifier)
FROM       dbo.PTaxon 
WHERE     (dbo.PTaxon.StatusFk = 1 AND dbo.PTaxon.PTRefFk not IN (select refid from SPICE_PublishedSecRefs) )
      OR  (dbo.PTaxon.StatusFk <> 1 AND dbo.PTaxon.PTRefFk IN (select refid from SPICE_PublishedSecRefs) )





GO
