USP_DATALIST_BATCHNUMBERINGSCHEMES

CREATE procedure dbo.USP_DATALIST_BATCHNUMBERINGSCHEMES
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,
        BATCHNUMBERINGSCHEME.NAME,
        case BATCHNUMBERINGSCHEME.NUMBERINGSCHEMECODE
            when 0 then dbo.UFN_BATCHNUMBERINGSCHEME_GETNEXTBATCHNUMBER(BATCHNUMBERINGSCHEME.ID)
            else '<' + BATCHNUMBERINGSCHEME.NUMBERINGSCHEME + '>'
        end as NEXTBATCHNUMBER,
        BATCHNUMBERINGSCHEME.ISGLOBAL,
        BATCHNUMBERINGSCHEME.NUMBERINGSCHEMECODE
    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
    --to sort global to top--

        BATCHNUMBERINGSCHEME.ISGLOBAL DESC, BATCHNUMBERINGSCHEME.NAME