USP_SIMPLEDATALIST_BBISBATCHTEMPLATELIST2

CREATE procedure dbo.USP_SIMPLEDATALIST_BBISBATCHTEMPLATELIST2(  
  @TRANSACTIONTYPEID varchar(40) = null,
  @BATCHTEMPLATEID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)  
as  

  Declare @BATCHTYPECATALOGID uniqueidentifier = null  

  SET @BATCHTYPECATALOGID = (  
      CASE @TRANSACTIONTYPEID  
          WHEN '{5705B543-4033-4A3A-BBCD-A731403EE1E6}' THEN '326C43A6-D162-4FD4-8D61-FEF9A0EE8C5E' --donation  

          WHEN '{172A5D9A-5241-493C-A2C2-EAC164C095B0}' THEN 'C66C39BC-ACD1-4C30-98DB-C5BA8F90EBEC' --event  

          WHEN '{D7D6143F-823D-4c74-AC2F-947CC96B7008}' THEN '196a2540-005a-4547-91a7-b301c464e28c' --membership  

          WHEN '{5F84002B-ABB1-4F50-A244-D4B14FBB1579}' THEN '877DBC83-98F9-4008-98A0-902FDB35E819' --signup  

          WHEN '{DCEAFCC7-0290-488E-A194-953DE66D6AAB}' THEN '98208E65-8334-4D3A-8496-4175352F1430' -- profileupdate  

      END)  

if @BATCHTYPECATALOGID = 'C66C39BC-ACD1-4C30-98DB-C5BA8F90EBEC' or @BATCHTYPECATALOGID = '98208E65-8334-4D3A-8496-4175352F1430' or @BATCHTYPECATALOGID is null
  begin  
    select   
      distinct BATCHTEMPLATE.ID as VALUE, BATCHTEMPLATE.NAME as LABEL   
   from   
      dbo.BATCHTEMPLATE   
     left join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID  
   where   
     (@BATCHTYPECATALOGID = BATCHTYPECATALOG.ID or (@BATCHTYPECATALOGID is null and BATCHTYPECATALOG.ID in ('326C43A6-D162-4FD4-8D61-FEF9A0EE8C5E','C66C39BC-ACD1-4C30-98DB-C5BA8F90EBEC',
     '196a2540-005a-4547-91a7-b301c464e28c','877DBC83-98F9-4008-98A0-902FDB35E819','98208E65-8334-4D3A-8496-4175352F1430'))) 
     and dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHPROCESSOR(@CURRENTAPPUSERID, BATCHTEMPLATE.ID) = 1
     and BATCHTEMPLATE.CUSTOM = 0
     and (BATCHTEMPLATE.ACTIVE = 1 or BATCHTEMPLATE.ID = @BATCHTEMPLATEID)
     and 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED  
      (  
       BATCHTYPECATALOG.SPECXML.query  
        (  
         'declare namespace common="bb_appfx_commontypes";  
         /*/common:InstalledProductList'  
        )  
      )
      order by BATCHTEMPLATE.NAME
  end
else
  begin
     select  
        BATCHTEMPLATE.ID as VALUE, BATCHTEMPLATE.NAME as LABEL 
        from 
        dbo.BATCHTEMPLATE
          left join dbo.BATCHTYPECATALOG on BATCHTEMPLATE.BATCHTYPECATALOGID = BATCHTYPECATALOG.ID
        left join dbo.BATCHWORKFLOW on BATCHTEMPLATE.BATCHWORKFLOWID = BATCHWORKFLOW.ID
        left join dbo.BATCHWORKFLOWSTATE on BATCHWORKFLOWSTATE.BATCHWORKFLOWID = BATCHWORKFLOW.ID and BATCHWORKFLOWSTATE.ISINITIALSTATE = 1 and BATCHWORKFLOWSTATE.ALLOWEDIT = 1
        where 
        BATCHTYPECATALOG.ID = @BATCHTYPECATALOGID
        and dbo.UFN_SECURITY_APPUSER_GRANTED_BATCHPROCESSOR(@CURRENTAPPUSERID, BATCHTEMPLATE.ID) = 1
          and BATCHTEMPLATE.CUSTOM = 0 
        and (BATCHTEMPLATE.ACTIVE = 1 or BATCHTEMPLATE.ID = @BATCHTEMPLATEID)
        and BATCHWORKFLOWSTATE.ISINITIALSTATE = 1 and BATCHWORKFLOWSTATE.ALLOWEDIT = 1
          and 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
              (
                  BATCHTYPECATALOG.SPECXML.query
                      (
                          'declare namespace common="bb_appfx_commontypes";
                          /*/common:InstalledProductList'
                      )
              )
      order by BATCHTEMPLATE.NAME
  end