
PRINT 'EXECUTE 025'

if exists (select * from dbo.sysobjects where id = object_id(N'p_mclBook_getAcceptedGeneraByTribe') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_mclBook_getAcceptedGeneraByTribe]
GO


CREATE PROCEDURE dbo.p_mclBook_getAcceptedGeneraByTribe
    @TribeNameId int
AS

    SELECT     
        dbo.v_mclBook_acceptedGeni.*, 
        dbo.RelPTaxon.PTNameFk2 AS TribeNameId, 
        dbo.Name.NameCache AS TribeName, 
        dbo.Name.RankFk
    FROM         
        dbo.RelPTQualifier INNER JOIN
        dbo.RelPTaxon ON dbo.RelPTQualifier.RelPTQualifierId = dbo.RelPTaxon.RelQualifierFk INNER JOIN
        dbo.v_mclBook_acceptedGeni ON dbo.RelPTaxon.PTNameFk1 = dbo.v_mclBook_acceptedGeni.PTNameFk AND 
        dbo.RelPTaxon.PTRefFk1 = dbo.v_mclBook_acceptedGeni.PTRefFk INNER JOIN
        dbo.Name ON dbo.RelPTaxon.PTNameFk2 = dbo.Name.NameId
    WHERE     
        (dbo.RelPTQualifier.RelPTQualifierId = 1) AND 
        (dbo.RelPTaxon.PTNameFk2 = @TribeNameId) AND 
        (dbo.RelPTaxon.PTRefFk2 IN (7000000,7700000)) AND 
        (dbo.Name.RankFk = 30)
        
    ORDER BY
        dbo.v_mclBook_acceptedGeni.MCLNumber
        

GO


