USP_SIMPLEDATALIST_BATCHNUMBERINGSCHEME

CREATE procedure dbo.USP_SIMPLEDATALIST_BATCHNUMBERINGSCHEME
as
    set nocount on;

    with BATCHNUMBERINGSCHEMES_CTE as (
        select distinct
            BATCHTEMPLATE.BATCHNUMBERINGSCHEMEID,
            case
                when exists (
                    select null  
                    from
                        dbo.V_INSTALLED_BATCHTYPECATALOG
                    where
                        V_INSTALLED_BATCHTYPECATALOG.ID = BATCHTEMPLATE.BATCHTYPECATALOGID
                    ) then 1
                else 0
            end as INSTALLEDPRODUCT
        from
            dbo.BATCHTEMPLATE
        where
            BATCHTEMPLATE.CUSTOM = 0
    )
    select
        BATCHNUMBERINGSCHEME.ID as 'value',
        BATCHNUMBERINGSCHEME.NAME as 'label',
        BATCHNUMBERINGSCHEME.NUMBERINGSCHEME as 'description'
    from
        dbo.BATCHNUMBERINGSCHEME
    left join 
        BATCHNUMBERINGSCHEMES_CTE on BATCHNUMBERINGSCHEMES_CTE.BATCHNUMBERINGSCHEMEID = BATCHNUMBERINGSCHEME.ID                
    where
        BATCHNUMBERINGSCHEMES_CTE.INSTALLEDPRODUCT is null or BATCHNUMBERINGSCHEMES_CTE.INSTALLEDPRODUCT = 1
    order by
        BATCHNUMBERINGSCHEME.NAME;