USP_DATAFORMTEMPLATE_VIEW_DISBURSEMENTPROCESSTEMPLATE

The load procedure used by the view dataform template "Disbursement Process Template View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@DESCRIPTION	nvarchar(255)	INOUT	Description
@BANKACCOUNT	nvarchar(100)	INOUT	Bank account
@FILTERCODE	tinyint	INOUT	Filter
@DUEDATE	nvarchar(100)	INOUT	Include by due date through
@INCLUDEALLUNPAID	bit	INOUT	Include all unpaid transactions
@SELECTION	nvarchar(300)	INOUT	Include selected transactions
@INCLUDEINVOICESWITHDISCOUNT	bit	INOUT	Include invoice with discount
@DISCOUNTEXPIRATIONDATE	nvarchar(100)	INOUT	Include by discount expiration date through
@CALCULATEDISCOUNTS	nvarchar(100)	INOUT	Calculate discounts as of
@CREATEOUTPUT	bit	INOUT	Create output selection
@OVERWRITEEXISTINGSELECTION	bit	INOUT	Overwrite existing selection
@OUTPUTSELECTIONNAME	nvarchar(100)	INOUT	Output selection name
@OUTPUTSELECTIONTYPE	nvarchar(50)	INOUT	Output selection type
@DISBURSEMENTDATE	nvarchar(100)	INOUT	Disbursement date
@POSTSTATUS	nvarchar(50)	INOUT	Post status
@POSTDATE	nvarchar(100)	INOUT	Post date
@AUTOSIGNATURE1	nvarchar(max)	INOUT	Auto-signature 1
@AUTOSIGNATURE2	nvarchar(max)	INOUT	Auto-signature 2
@FORMATNAMES	nvarchar(max)	INOUT	Format names

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DISBURSEMENTPROCESSTEMPLATE
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @DESCRIPTION nvarchar(255) = null output,
    @BANKACCOUNT nvarchar(100) = null output,
  @FILTERCODE tinyint = null output,
  @DUEDATE nvarchar(100) = null output,
  @INCLUDEALLUNPAID bit = null output,
  @SELECTION nvarchar(300) = null output,
  @INCLUDEINVOICESWITHDISCOUNT bit = null output,
  @DISCOUNTEXPIRATIONDATE nvarchar(100) = null output,
  @CALCULATEDISCOUNTS nvarchar(100) = null output,
  @CREATEOUTPUT bit = null output,
  @OVERWRITEEXISTINGSELECTION bit = null output,
  @OUTPUTSELECTIONNAME nvarchar(100) = null output,
  @OUTPUTSELECTIONTYPE nvarchar(50) = null output,
  @DISBURSEMENTDATE nvarchar(100) = null output,
  @POSTSTATUS nvarchar(50) = null output,
  @POSTDATE nvarchar(100) = null output,
  @AUTOSIGNATURE1 nvarchar(max) = null output,
  @AUTOSIGNATURE2 nvarchar(max) = null output,
  @FORMATNAMES nvarchar(max) = null output
)
as
    set nocount on;

    -- be sure to set this, in case the select returns no rows

    set @DATALOADED = 0;

    -- populate the output parameters, which correspond to fields on the form.  Note that

    -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

    -- will display a "no data loaded" message.

    select @DATALOADED = 1,
             @DESCRIPTION = T.DESCRIPTION,
             @BANKACCOUNT = B.ACCOUNTNAME,
         @FILTERCODE = T.FILTERCODE,
         @DUEDATE = dbo.UFN_DISBURSEMENTPROCESSTEMPLATE_GETDATESTRING(T.DUEDATENUMBEROFDAYS, T.DUEDATECODE),
         @INCLUDEALLUNPAID = CASE WHEN T.FILTERCODE = 1 THEN 1 ELSE 0 END,
         @SELECTION = S.NAME,
         @INCLUDEINVOICESWITHDISCOUNT = T.INCLUDEINVOICESWITHDISCOUNT,
         @DISCOUNTEXPIRATIONDATE = dbo.UFN_DISBURSEMENTPROCESSTEMPLATE_GETDATESTRING(T.DISCOUNTDATENUMBEROFDAYS, T.DISCOUNTDATECODE),
         @CALCULATEDISCOUNTS = dbo.UFN_DISBURSEMENTPROCESSTEMPLATE_GETDATESTRING(T.CALCULATEDISCOUNTDAYS, 1),
         @CREATEOUTPUT = T.CREATESELECTIONFROMRESULT,
         @OVERWRITEEXISTINGSELECTION = T.OVERWRITEOUTPUTIDSET,
         @OUTPUTSELECTIONNAME = CASE WHEN T.CREATESELECTIONFROMRESULT = 1 THEN T.OUTPUTIDSETNAME ELSE '' END,
         @OUTPUTSELECTIONTYPE = CASE WHEN T.CREATESELECTIONFROMRESULT = 1 THEN R.NAME ELSE '' END,
         @DISBURSEMENTDATE = dbo.UFN_DISBURSEMENTPROCESSTEMPLATE_GETDATESTRING(T.DISBURSEMENTDATENUMBEROFDAYS, null),
         @POSTSTATUS = T.POSTSTATUS,
         @POSTDATE = CASE WHEN T.POSTSTATUSCODE = 3 THEN '' ELSE dbo.UFN_DISBURSEMENTPROCESSTEMPLATE_GETDATESTRING(T.POSTDATENUMBEROFDAYS, null) END,
         @AUTOSIGNATURE1 = CASE SIGNATURE1OPTIONCODE
                            WHEN 0 THEN dbo.UFN_SIGNATURE_USERNAME(SIG1.SIGNERCODE, SIG1.NAME, SIG1.APPUSERID)
                            WHEN 1 THEN SIGNATURE1OPTION 
                            WHEN 2 THEN 'Based on amount' END,
         @AUTOSIGNATURE2 = CASE SIGNATURE2OPTIONCODE
                            WHEN 0 THEN dbo.UFN_SIGNATURE_USERNAME(SIG2.SIGNERCODE, SIG2.NAME, SIG2.APPUSERID)
                            WHEN 1 THEN SIGNATURE2OPTION 
                            WHEN 2 THEN 'Based on amount' END,
         @FORMATNAMES = dbo.UFN_DISBURSEMENTPROCESSTEMPLATE_FORMATS(T.ID)
    from dbo.DISBURSEMENTPROCESSTEMPLATE T
  inner join dbo.BANKACCOUNT B on T.BANKACCOUNTID = B.ID
  left outer join dbo.IDSETREGISTER S on S.ID = T.IDSETREGISTERID
  left outer join dbo.RECORDTYPE R on R.ID = T.OUTPUTIDSETRECORDTYPEID
  left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE B1 on B1.ID = T.SIGNATURE1ID
  left outer join dbo.SIGNATURE SIG1 on SIG1.ID = B1.SIGNATUREID
  left outer join dbo.BANKACCOUNTAUTHORIZEDSIGNATURE B2 on B2.ID = T.SIGNATURE2ID
    left outer join dbo.SIGNATURE SIG2 on SIG2.ID = B2.SIGNATUREID
  where T.ID = @ID

    return 0;