USP_DATALIST_GENERATEEFTFILEPROCESS

This datalist returns a list of saved generate direct debit file process parameter sets.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.

Definition

Copy



CREATE procedure dbo.USP_DATALIST_GENERATEEFTFILEPROCESS
(
    @CURRENTAPPUSERID uniqueidentifier
    ,@SITEFILTERMODE tinyint = 0
    ,@SITESSELECTED xml = null
    ,@SECURITYFEATUREID uniqueidentifier = null
    ,@SECURITYFEATURETYPE tinyint = null
)
as
set nocount on;

declare @BASICGL bit
set @BASICGL = dbo.UFN_VALID_BASICGL_INSTALLED()

select
    GENERATEEFTFILEPROCESS.ID,
    GENERATEEFTFILEPROCESS.NAME,
    GENERATEEFTFILEPROCESS.DESCRIPTION,
    case GENERATEEFTFILEPROCESS.DATETYPECODE
        when 0 then getdate()
        when 2 then dateadd(day,GENERATEEFTFILEPROCESS.FINDAYSAFTER,getdate())
        else GENERATEEFTFILEPROCESS.DATE
    end,
    BPI.OWNERID as OWNERID,
    BA.ACCOUNTNAME as BANKACCOUNT,
    coalesce(SITE.NAME,'All Sites') SITES,
    BANKINGSYSTEM.NAME BANKINGSYSTEM,
    BANKINGSYSTEM.CONDITIONSETTINGNAME BANKINGSYSTEMCONDITIONSETTING
from
    dbo.GENERATEEFTFILEPROCESS
left join dbo.BUSINESSPROCESSINSTANCE BPI on GENERATEEFTFILEPROCESS.ID = BPI.BUSINESSPROCESSPARAMETERSETID
left join dbo.BANKACCOUNT BA on GENERATEEFTFILEPROCESS.BANKACCOUNTID = BA.ID
left join dbo.SITE on SITE.ID = BPI.SITEID
left join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = BA.BANKINGSYSTEMID
where (dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, GENERATEEFTFILEPROCESS.ID) = 1)
    and (@BASICGL=0 or BA.PDACCOUNTSYSTEMID in (select ID from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CurrentAppUserID)))
    and (
                (
                    (--Check site security

                        select count(1) 
                        from dbo.UFN_BUSINESSPROCESSPARAMETERSET_GETSITES(GENERATEEFTFILEPROCESS.ID) as GENERATEEFTFILEPROCESSSITE 
                        where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[GENERATEEFTFILEPROCESSSITE].[SITEID] or (SITEID is null and [GENERATEEFTFILEPROCESSSITE].[SITEID] is null)))
                    ) > 0
                )
                and (--Apply site filter

                    @SITEFILTERMODE = 0
                        or BPI.SITEID in (
                            select SITEID
                            from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) 
                        )
                )
            )
order by
    GENERATEEFTFILEPROCESS.NAME;