USP_SECURITY_APPUSER_GRANTED_BATCHPROCESSOR_BULK

create procedure dbo.USP_SECURITY_APPUSER_GRANTED_BATCHPROCESSOR_BULK 
    (
        @GRANTEDBATCHTEMPLATEXML xml,
        @CURRENTAPPUSERID uniqueidentifier
    )
as
    /*
        This store procedure will return a list of all Batch Template IDs and NAMEs based on app user id whether a user has been granted or denied rights for data entry to the batch for a system role.
        GRANT or DENY logic needs to be covered in the calling procedure.
        The logic is same as UFN_SECURITY_APPUSER_GRANTED_BATCHPROCESSOR.

        @GRANTEDBATCHTEMPLATEXML - Mandatory Parameter. Contains xml list of all batch templates and their corresponding site ids. Filtering records to improve performance.
                It contains three fields BATCHTEMPLATEID, BATCHTEMPLATENAME and SITEID for a batch template.
        @CURRENTAPPUSERID - Mandatory Parameter. Current app user Id.
    */
begin
    set nocount on;

    declare @RETURNTABLE table (
    VALUE uniqueidentifier,
    LABEL varchar(1000),
    GRANTED bit
    );

    insert into @RETURNTABLE
    select
        distinct 
        GRANTEDBATCHTEMPLATE.NODE.value('@VALUE', 'uniqueidentifier') as VALUE,
        GRANTEDBATCHTEMPLATE.NODE.value('@LABEL', 'nvarchar(500)') as LABEL,
        PERMISSIONS.GRANTORDENY as GRANTED
    from 
        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHWORKFLOWSTATE as PERMISSIONS
        inner join dbo.BATCHWORKFLOWSTATE on BATCHWORKFLOWSTATE.ID = PERMISSIONS.BATCHWORKFLOWSTATEID
        inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.BATCHWORKFLOWID = BATCHWORKFLOWSTATE.BATCHWORKFLOWID
        inner join @GRANTEDBATCHTEMPLATEXML.nodes('/GRANTEDBATCHTEMPLATES/GRANTEDBATCHTEMPLATE') GRANTEDBATCHTEMPLATE(NODE) on GRANTEDBATCHTEMPLATE.NODE.value('@VALUE', 'uniqueidentifier') = BATCHTEMPLATE.ID
        left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.APPUSERID = PERMISSIONS.APPUSERID and SYSTEMROLEAPPUSER.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID
    where 
        PERMISSIONS.APPUSERID = @CURRENTAPPUSERID 
        and BATCHWORKFLOWSTATE.ISINITIALSTATE = 1
        and
        (
            (
                SITEPERMISSION.SITEID = GRANTEDBATCHTEMPLATE.NODE.value('@SITEID', 'uniqueidentifier')                                                                    --Records with specific Sites

                or GRANTEDBATCHTEMPLATE.NODE.value('@SITEID', 'uniqueidentifier') is null                                                                                                --Records with no sites & 

                                                                                                                                                                                                                                                                --Batch template site security special case, specific site + no site records based on feature permission

            )
            or
            SYSTEMROLEAPPUSER.SECURITYMODECODE = 0                                                                                                                                                                        --All records

        )
        order by PERMISSIONS.GRANTORDENY ASC;

    --Only add those batchtemplates that are not added above.

    insert into @RETURNTABLE
    select 
        distinct 
        GRANTEDBATCHTEMPLATE.NODE.value('@VALUE', 'uniqueidentifier') as VALUE,
        GRANTEDBATCHTEMPLATE.NODE.value('@LABEL', 'nvarchar(500)') as LABEL ,
        PERMISSIONS.GRANTORDENY as GRANTED
    from 
        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTEMPLATE as PERMISSIONS
        inner join @GRANTEDBATCHTEMPLATEXML.nodes('/GRANTEDBATCHTEMPLATES/GRANTEDBATCHTEMPLATE') GRANTEDBATCHTEMPLATE(NODE) on GRANTEDBATCHTEMPLATE.NODE.value('@VALUE', 'uniqueidentifier') = PERMISSIONS.BATCHTEMPLATEID
        left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.APPUSERID = PERMISSIONS.APPUSERID and SYSTEMROLEAPPUSER.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID
    where 
        PERMISSIONS.APPUSERID = @CURRENTAPPUSERID 
        and
        (
            (
                SITEPERMISSION.SITEID = GRANTEDBATCHTEMPLATE.NODE.value('@SITEID', 'uniqueidentifier')                                                                    --Records with specific Sites

                or GRANTEDBATCHTEMPLATE.NODE.value('@SITEID', 'uniqueidentifier') is null                                                                                                --Records with no sites & 

                                                                                                                                                                                                                                                                --Batch template site security special case, specific site + no site records based on feature permission

            )
            or
            SYSTEMROLEAPPUSER.SECURITYMODECODE = 0                                                                                                                                                                        --All records

        )
        and not exists (select 1 from @RETURNTABLE where VALUE = GRANTEDBATCHTEMPLATE.NODE.value('@VALUE', 'uniqueidentifier'))
        order by PERMISSIONS.GRANTORDENY ASC;

    --Only add those batchtemplates that are not added above.

    select 
        distinct 
        GRANTEDBATCHTEMPLATE.NODE.value('@VALUE', 'uniqueidentifier') as VALUE,
        GRANTEDBATCHTEMPLATE.NODE.value('@LABEL', 'nvarchar(500)') as LABEL,
        PERMISSIONS.GRANTORDENY as GRANTED
    from 
        dbo.V_SECURITY_SYSTEMROLEASSIGNMENT_USER_BATCHTYPE as PERMISSIONS
        inner join dbo.BATCHTEMPLATE on PERMISSIONS.BATCHTYPECATALOGID = BATCHTEMPLATE.BATCHTYPECATALOGID
        inner join @GRANTEDBATCHTEMPLATEXML.nodes('/GRANTEDBATCHTEMPLATES/GRANTEDBATCHTEMPLATE') GRANTEDBATCHTEMPLATE(NODE) on GRANTEDBATCHTEMPLATE.NODE.value('@VALUE', 'uniqueidentifier') = BATCHTEMPLATE.ID
        left join dbo.SITEPERMISSION on SITEPERMISSION.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID and SITEPERMISSION.APPUSERID = PERMISSIONS.APPUSERID
        inner join dbo.SYSTEMROLEAPPUSER on SYSTEMROLEAPPUSER.APPUSERID = PERMISSIONS.APPUSERID and SYSTEMROLEAPPUSER.SYSTEMROLEID = PERMISSIONS.SYSTEMROLEID
    where 
        PERMISSIONS.APPUSERID = @CURRENTAPPUSERID
        and PERMISSIONS.GRANTORDENY = 1
        and
        (
            (
                SITEPERMISSION.SITEID = GRANTEDBATCHTEMPLATE.NODE.value('@SITEID', 'uniqueidentifier')                                                                    --Records with specific Sites

                or GRANTEDBATCHTEMPLATE.NODE.value('@SITEID', 'uniqueidentifier') is null                                                                                                --Records with no sites & 

                                                                                                                                                                                                                                                                --Batch template site security special case, specific site + no site records based on feature permission

            )
            or
            SYSTEMROLEAPPUSER.SECURITYMODECODE = 0                                                                                                                                                                        --All records

        )
        and not exists (select 1 from @RETURNTABLE where VALUE = GRANTEDBATCHTEMPLATE.NODE.value('@VALUE', 'uniqueidentifier'))
        order by PERMISSIONS.GRANTORDENY ASC;

    select 
        VALUE,
        LABEL,
        GRANTED 
    from @RETURNTABLE;

end