USP_SIMPLEDATALIST_MEMBERSHIPCARDFORMATLETTERTEMPLATE

CREATE procedure dbo.USP_SIMPLEDATALIST_MEMBERSHIPCARDFORMATLETTERTEMPLATE as

declare @INSTALLED table (ID uniqueidentifier, NAME nvarchar(100));
insert into @INSTALLED exec dbo.USP_INSTALLEDPRODUCTS_GETLIST

declare @REPORTS table (
    ID uniqueidentifier,
    LABEL nvarchar(100),
    VALUE nvarchar(38)
);


with PRELIMINARY_CTE as (
    select 
        ID,
        '0-' + cast(ID as nvarchar(36)) as VALUE,
        NAME as LABEL,
        REPORTSPECXML    
    from dbo.REPORTCATALOG
    where 
        convert(nvarchar(255),REPORTSPECXML.query('/*:ReportSpec/*:Folder')) like '%System Reports\Membership\Card Formats%' 
)
insert into @REPORTS(ID, VALUE, LABEL)
select distinct
    ID,
    VALUE,
    LABEL    
from PRELIMINARY_CTE
cross apply REPORTSPECXML.nodes('/*:ReportSpec/*:InstalledProductList/*:InstalledProduct') as T(C)
where C.value('(./@ID)', 'uniqueidentifier') in (select ID from @INSTALLED)

union all

select
    ID,
    '1-' + cast(ID as nvarchar(36)) as VALUE,
    NAME as LABEL
from dbo.LETTERTEMPLATE
where LETTERTEMPLATETYPECODE = 8



if exists (select 1 from @INSTALLED where ID = 'BB1C17BC-9E0B-4683-B490-EE40D511FA05') -- if Altru

    delete from @REPORTS
    where ID = '37255d28-6c4b-46c5-a32b-7c39ef35c86d' -- The original 3 per sheet report


select VALUE, LABEL
from @REPORTS
order by LABEL