USP_BBNC_GETOPENBATCHES

            CREATE procedure [dbo].[USP_BBNC_GETOPENBATCHES]
            (
                @CURRENTAPPUSERID uniqueidentifier = null,
        @BATCHTEMPLATEID uniqueidentifier = null,
        @SITEFILTERMODE tinyint = 0,
        @OWNER uniqueidentifier = null,
        @SITESSELECTED xml = null,
        @SECURITYFEATUREID uniqueidentifier = null,
        @SECURITYFEATURETYPE tinyint = null
            )
            as

            set nocount on;

            select
                BATCH.ID,
                BATCH.BATCHNUMBER,
                BATCH.DESCRIPTION,
                (select NAME from dbo.BATCHWORKFLOWSTATE where ID = BATCH.BATCHWORKFLOWSTATEID) as STATUS,
                (select case when APPUSER.DISPLAYNAME = '' then APPUSER.USERNAME else APPUSER.DISPLAYNAME end from dbo.APPUSER where ID = BATCH.APPUSERID) as OWNER,
                BATCHTYPECATALOG.BASETABLENAME,
                dbo.UFN_BATCHWORKFLOWSTATE_TASKSAVAILABLE(BATCH.BATCHWORKFLOWSTATEID) TASKSAVAILABLE,
                (select ALLOWCOMMIT from dbo.BATCHWORKFLOWSTATE where BATCHWORKFLOWSTATE.ID = BATCH.BATCHWORKFLOWSTATEID) ENABLECOMMIT,
        BATCHTEMPLATE.NAME as BATCHTEMPLATENAME,
                (select NAME from SITE where ID = BATCHTEMPLATE.SITEID) as BATCHTEMPLATESITEID
            from
                dbo.BATCH 
                inner join dbo.BATCHTEMPLATE on BATCH.BATCHTEMPLATEID = BATCHTEMPLATE.ID
                inner join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
            where
                (BATCHTYPECATALOG.CATEGORY = 'NetCommunity' OR BATCHTYPECATALOG.CATEGORY = 'Blackbaud Internet Solutions'
                or (BATCHTYPECATALOG.ID = '196A2540-005A-4547-91A7-B301C464E28C'
                        and exists(select BATCHMEMBERSHIPDUESBBNCINFO.ID 
                                    from dbo.BATCHMEMBERSHIPDUESBBNCINFO
                                    inner join dbo.BATCHMEMBERSHIPDUES on BATCHMEMBERSHIPDUESBBNCINFO.BATCHMEMBERSHIPDUESID = BATCHMEMBERSHIPDUES.ID
                                    where BATCHID = BATCH.ID
                                    )
                    )

                or (BATCHTYPECATALOG.ID = '877DBC83-98F9-4008-98A0-902FDB35E819'
                        and exists(select BATCHCONSTITUENTUPDATEBBNCINFO.ID 
                                    from dbo.BATCHCONSTITUENTUPDATEBBNCINFO
                                    inner join dbo.BATCHCONSTITUENTUPDATE on BATCHCONSTITUENTUPDATEBBNCINFO.BATCHCONSTITUENTUPDATEID = BATCHCONSTITUENTUPDATE.ID
                                    where BATCHID = BATCH.ID
                                    )
                    )    

               or (BATCHTYPECATALOG.ID = '326C43A6-D162-4FD4-8D61-FEF9A0EE8C5E'
                        and exists(select BATCHREVENUEBBNCINFO.ID 
                                    from dbo.BATCHREVENUEBBNCINFO
                                    inner join dbo.BATCHREVENUE on BATCHREVENUEBBNCINFO.BATCHREVENUEID = BATCHREVENUE.ID
                                    where BATCHID = BATCH.ID
                                    )
                    )         

                    )
                and BATCH.STATUSCODE = 0
                and ((dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHOWNER(@CURRENTAPPUSERID, BATCH.ID) = 1 or @CURRENTAPPUSERID is null) 
            and(BATCHTEMPLATE.SITEID is null or (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[BATCHTEMPLATE].[SITEID] or (SITEID is null and [BATCHTEMPLATE].[SITEID] is null)))))
        and (@OWNER is null or @OWNER = '00000000-0000-0000-0000-000000000000' or BATCH.APPUSERID = @OWNER)
        and (@BATCHTEMPLATEID is null or @BATCHTEMPLATEID = '00000000-0000-0000-0000-000000000000' or BATCHTEMPLATE.ID = @BATCHTEMPLATEID)
        and 
                (
                    @SITEFILTERMODE = 0 
                    or BATCHTEMPLATE.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
                )

      order by [BATCH].[BATCHNUMBER];