UFN_DISBURSEMENTPROCESSTEMPLATE_VIEWSIGNATURES_TOITEMLISTXML

Get an xml list of the signatures for a given disbursement process template

Return

Return Type
xml

Parameters

Parameter	Parameter Type	Mode	Description
@DISBURSEMENTPROCESSTEMPLATEID	uniqueidentifier	IN
@TYPECODE	tinyint	IN

Definition

Copy



create function dbo.UFN_DISBURSEMENTPROCESSTEMPLATE_VIEWSIGNATURES_TOITEMLISTXML
(
  @DISBURSEMENTPROCESSTEMPLATEID uniqueidentifier,
  @TYPECODE tinyint
)
returns xml
as begin
    declare @XML xml
    if @TYPECODE = 0
    begin
    set @XML = (
      select TS.ID
      ,TS.FROMAMOUNT
      ,CASE when TS.BANKACCOUNTAUTHORIZEDSIGNATUREID is null then null else dbo.UFN_SIGNATURE_USERNAME(S.SIGNERCODE, S.NAME, S.APPUSERID) END [USERNAME]
      from dbo.DISBURSEMENTPROCESSTEMPLATESIGNATURE TS
      left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE B on B.ID = TS.BANKACCOUNTAUTHORIZEDSIGNATUREID
      left outer join dbo.SIGNATURE S on S.ID = B.SIGNATUREID
      where DISBURSEMENTPROCESSTEMPLATEID = @DISBURSEMENTPROCESSTEMPLATEID and TYPECODE = @TYPECODE
      order by FROMAMOUNT
      for xml raw('ITEM'), type, elements, root('SIGNATURE1COLLECTION'), BINARY BASE64)
    end
    else
        set @XML = (
      select TS.ID
      ,TS.FROMAMOUNT
      ,CASE when TS.BANKACCOUNTAUTHORIZEDSIGNATUREID is null then null else dbo.UFN_SIGNATURE_USERNAME(S.SIGNERCODE, S.NAME, S.APPUSERID) END [USERNAME]
      from dbo.DISBURSEMENTPROCESSTEMPLATESIGNATURE TS
      left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE B on B.ID = TS.BANKACCOUNTAUTHORIZEDSIGNATUREID
      left outer join dbo.SIGNATURE S on S.ID = B.SIGNATUREID
      where DISBURSEMENTPROCESSTEMPLATEID = @DISBURSEMENTPROCESSTEMPLATEID and TYPECODE = @TYPECODE
      order by FROMAMOUNT
      for xml raw('ITEM'), type, elements, root('SIGNATURE2COLLECTION'), BINARY BASE64)

    return @XML
end