USP_BANKACCOUNT_COMMONDATALIST_2

CREATE procedure dbo.USP_BANKACCOUNT_COMMONDATALIST_2
(
  @BANKID uniqueidentifier = null,
  @EXCLUDECLOSEDITEMS smallint = 0,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  exec dbo.USP_GET_KEY_ACCESS;

  select
    BANKACCOUNT.ID as ACCOUNTID,
    CONSTITUENT.NAME BANKNAME,
    BANKACCOUNT.ACCOUNTNAME,
    BANKACCOUNT.ACCOUNTTYPE,
    BANKACCOUNT.STATUSCODE,
    BANKINGSYSTEM.NAME as BANKINGSYSTEMNAME,
    BANKINGSYSTEM.CONDITIONSETTINGNAME as BANKINGSYSTEMCONDITIONSETTINGNAME,
    BANKACCOUNT.ROUTINGNUMBER,
    BANKACCOUNT.SORTCODE,
    BANKACCOUNT.BIC,
    BANKACCOUNT.BANKCODE,
    case when LEN(coalesce(convert(nvarchar(50), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)),'')) = 0 then ''  
    else  REPLICATE('x', LEN(coalesce(convert(nvarchar(50), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)),''))- LEN(RIGHT(coalesce(convert(nvarchar(50), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)),''),4))) + RIGHT(coalesce(convert(nvarchar(50), DecryptByKey(BANKACCOUNT.ACCOUNTNUMBER)),''),4) end as ACCOUNTNUMBER,
    dbo.UFN_CURRENCY_GETDESCRIPTION(BANKACCOUNT.TRANSACTIONCURRENCYID) as CURRENCY,
    dbo.UFN_BANKACCOUNT_GETCURRENTBALANCE(BANKACCOUNT.ID) as CURRENTBALANCE,
    dbo.UFN_BANKACCOUNT_GETENDINGBALANCE(BANKACCOUNT.ID) as ENDINGBALANCE,
    PDACCOUNTSYSTEM.NAME,
    BANKACCOUNT.TRANSACTIONCURRENCYID
  from
    dbo.BANKACCOUNT
    inner join dbo.PDACCOUNTSYSTEM on PDACCOUNTSYSTEM.ID = BANKACCOUNT.PDACCOUNTSYSTEMID
    inner join dbo.CONSTITUENT on CONSTITUENT.ID = BANKACCOUNT.BANKID
    left join dbo.BANKINGSYSTEM on BANKINGSYSTEM.ID = BANKACCOUNT.BANKINGSYSTEMID
  where
    (@BANKID is null or BANKACCOUNT.BANKID = @BANKID) 
    and BANKACCOUNT.STATUSCODE in (1, @EXCLUDECLOSEDITEMS) 
    and PDACCOUNTSYSTEM.ID in (select T1.ID from dbo.UFN_PDACCOUNTSYSTEM_GETSYSTEMIDSFORUSER(@CURRENTAPPUSERID) T1)
  order by
    BANKNAME, 
    BANKACCOUNT.ACCOUNTNAME;

  exec USP_CLOSE_KEY_ACCESS;