UFN_TREASURY_BUILDDEPOSITMICRLINE

This function will return a formatted MICR line for the deposit specified.

Return

Return Type
nvarchar(100)

Parameters

Parameter	Parameter Type	Mode	Description
@DEPOSITID	uniqueidentifier	IN

Definition

Copy



CREATE function [dbo].[UFN_TREASURY_BUILDDEPOSITMICRLINE]
(
  @DEPOSITID uniqueidentifier
)
returns nvarchar(100)
WITH EXECUTE AS CALLER
as begin;

    declare @ret nvarchar(100);SET @ret='<0000000000< :000000000:                    <' 
    declare @depositnum nvarchar(10);
    declare @routingtransitnum nvarchar(9);
    declare @accountnum nvarchar(19);

    SELECT @depositnum=TRANSACTIONNUMBER, @routingtransitnum=ROUTINGNUMBER, @accountnum=REPLACE(ACCOUNTNUMBER,'-','=') FROM dbo.BANKACCOUNTTRANSACTION INNER JOIN dbo.BANKACCOUNT ON BANKACCOUNTTRANSACTION.BANKACCOUNTID=BANKACCOUNT.ID WHERE BANKACCOUNTTRANSACTION.ID=@DEPOSITID
    SET @ret=stuff(@ret, 12-len(@depositnum),len(@depositnum),@depositnum)
    SET @ret=stuff(@ret, 24-len(@routingtransitnum),len(@routingtransitnum),@routingtransitnum)
    SET @ret=stuff(@ret, 45-len(@accountnum),len(@accountnum),@accountnum)
    return rtrim(@ret)

end;