USP_DATAFORMTEMPLATE_VIEW_DISBURSEMENTPROCESSHEADER

The load procedure used by the view dataform template "Disbursement Process Summary Data 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
@STARTEDBY	nvarchar(256)	INOUT	Started by
@STARTEDON	datetime	INOUT	Started on
@TOTALDISBURSEMENTS	int	INOUT	Number of disbursements
@TOTALVOIDEDDISBURSEMENTS	int	INOUT	Number of voided disbursements
@TOTALPURGEDDISBURSEMENTS	int	INOUT	Number of voided disbursements
@TOTALAMOUNT	money	INOUT	Total amount
@TOTALVOIDEDAMOUNT	money	INOUT	Total voided amount
@TOTALPURGEDAMOUNT	money	INOUT	Total voided amount

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_DISBURSEMENTPROCESSHEADER
(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@DESCRIPTION nvarchar(255) = null output
  ,@BANKACCOUNT nvarchar(100) = null output
  ,@STARTEDBY nvarchar(256) = null output
  ,@STARTEDON datetime = null output
    ,@TOTALDISBURSEMENTS integer = null output
    ,@TOTALVOIDEDDISBURSEMENTS integer = null output
  ,@TOTALPURGEDDISBURSEMENTS integer = null output
    ,@TOTALAMOUNT money = null output
    ,@TOTALVOIDEDAMOUNT money = null output
  ,@TOTALPURGEDAMOUNT money = null output
)
as
    set nocount on; 
    set @DATALOADED = 0;

    declare @STATUS tinyint;

    select @DATALOADED = 1
           ,@DESCRIPTION = DP.DESCRIPTION
       ,@BANKACCOUNT = BA.ACCOUNTNAME
       ,@STARTEDBY = A.USERNAME
       ,@STARTEDON = DP.DATEADDED
             ,@STATUS = DP.STATUSCODE
    from dbo.DISBURSEMENTPROCESS as DP
    inner join dbo.BANKACCOUNT as BA on DP.BANKACCOUNTID = BA.ID
    inner join dbo.CHANGEAGENT as A on DP.ADDEDBYID = A.ID
    where 
        DP.ID = @ID;

    select @TOTALDISBURSEMENTS = COUNT(FT.ID)
        ,@TOTALVOIDEDDISBURSEMENTS = COUNT(CASE WHEN BAT.STATUSCODE = 4 AND BAT.DELETED = 0 THEN BAT.ID ELSE NULL END)
    ,@TOTALPURGEDDISBURSEMENTS = COUNT(CASE WHEN BAT.STATUSCODE = 4 AND BAT.DELETED = 1 THEN BAT.ID ELSE NULL END)
        ,@TOTALAMOUNT = SUM(FT.TRANSACTIONAMOUNT)
        ,@TOTALVOIDEDAMOUNT = SUM(CASE WHEN BAT.STATUSCODE = 4 AND BAT.DELETED = 0 THEN FT.TRANSACTIONAMOUNT ELSE 0 END)
    ,@TOTALPURGEDAMOUNT = SUM(CASE WHEN BAT.STATUSCODE = 4 AND BAT.DELETED = 1 THEN FT.TRANSACTIONAMOUNT ELSE 0 END)
    from dbo.FINANCIALTRANSACTION FT
    left outer join dbo.DISBURSEMENTPROCESSDISBURSEMENT DPD on DPD.ID = FT.ID and DPD.DISBURSEMENTPROCESSID = @ID
    left outer join dbo.BANKACCOUNTTRANSACTION BAT on BAT.ID = DPD.ID
    inner join dbo.DISBURSEMENTPROCESS DP on DP.ID = DPD.DISBURSEMENTPROCESSID
    where DP.ID = @ID

    return 0;