USP_DATALIST_TRIBUTEACKLETTERCODES

CREATE procedure dbo.USP_DATALIST_TRIBUTEACKLETTERCODES
(
    @INCLUDEINACTIVE bit = 0
)
as
    set nocount on;

    select 
        TRIBUTELETTERCODE.ID,
        TRIBUTELETTERCODE.NAME as LETTERNAME,
        TRIBUTELETTERCODE.DESCRIPTION,
        TRIBUTELETTERCODE.WORDTEMPLATEFILENAME,
        null,
        BUSINESSPROCESSVIEW.CAPTION,
        case TRIBUTELETTERCODE.ISACTIVE when 1 then 'Active' else 'Inactive' end as STATUS,
        TRIBUTELETTERCODE.WORDTEMPLATEFILENAME,
        'WORDFILE',
        'bf6dba76-2a2b-4b00-b0c3-c421c9bb756c' as VIEWFORMID,
        EXPORTDEFINITION.NAME
    from
        dbo.TRIBUTELETTERCODE
      left join dbo.QUERYVIEWCATALOG on TRIBUTELETTERCODE.QUERYVIEWID = QUERYVIEWCATALOG.ID
      left join dbo.BUSINESSPROCESSVIEW on QUERYVIEWCATALOG.ID = BUSINESSPROCESSVIEW.QUERYVIEWCATALOGID
      left join dbo.EXPORTDEFINITION on EXPORTDEFINITION.ID = TRIBUTELETTERCODE.EXPORTDEFINITIONID
    where
        (@INCLUDEINACTIVE = 1    or (@INCLUDEINACTIVE = 0 and TRIBUTELETTERCODE.ISACTIVE = 1))
        and (
          -- hide marketing effort export record types

          TRIBUTELETTERCODE.EXPORTDEFINITIONID is null
          or [EXPORTDEFINITION].[RECORDTYPEID] not in (select [ID] from dbo.[RECORDTYPE] where [NAME] in ('Direct Marketing Effort Segment Member Export', 'Marketing Acknowledgement Segment Member Export', 'Membership Effort Segment Member Export', 'Sponsorship Effort Segment Member Export'))
      )        
    order by
        LETTERNAME;