USP_DATAFORMTEMPLATE_EDITLOAD_DISBURSEMENTPROCESS_PRINT_EDIT

The load procedure used by the edit dataform template "Disbursement Process Print Checks"

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.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@CHECKS_DISBURSEMENTDATE	date	INOUT	Disbursement date
@CHECKS_NUMBEROFDISBURSEMENTS	int	INOUT	Number of disbursements
@CHECKS_TOTALAMOUNT	money	INOUT	Total amount
@ASSIGNDISBURSEMENTSCODE	tinyint	INOUT	Assign disbursements value
@CHECKS_NEXTUNUSED	int	INOUT	Start number
@RANGESGRID	xml	INOUT
@CHECKS_PRINTERID	uniqueidentifier	INOUT	Printer
@CHECKS_FORMAT	uniqueidentifier	INOUT	Format

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_DISBURSEMENTPROCESS_PRINT_EDIT(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@TSLONG bigint = 0 output
    ,@CHECKS_DISBURSEMENTDATE date = null output
    ,@CHECKS_NUMBEROFDISBURSEMENTS int = null output
    ,@CHECKS_TOTALAMOUNT money = null output
    ,@ASSIGNDISBURSEMENTSCODE tinyint = null output
    ,@CHECKS_NEXTUNUSED int = null output
    ,@RANGESGRID xml = null output
    ,@CHECKS_PRINTERID uniqueidentifier = null output
    ,@CHECKS_FORMAT uniqueidentifier = null output
)
as

    set nocount on;

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

    set @DATALOADED = 0
    set @TSLONG = 0

  -- Make sure this disbursement process has a disbursement format.

  -- ****

  declare @COUNT integer;

  select 
    @COUNT = count(*)
  from
      dbo.DISBURSEMENTPROCESSFORMAT as DPF 
  where
    DPF.DISBURSEMENTPROCESSID = @ID

  if @COUNT = 0
    raiserror ('A disbursement format is required.', 16, 1)
  -- ****


  -- Make sure at least one transaction is included.

  -- $$$$

  if not exists
  (
    select * 
    from dbo.DISBURSEMENTPROCESS as DP
      inner join dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD on DP.ID = DPD.DISBURSEMENTPROCESSID
      inner join dbo.FINANCIALTRANSACTION as FT on DPD.ID= FT.ID
      inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FT.ID = FTA.FINANCIALTRANSACTIONID AND FTA.AMOUNT <> 0
    where DP.ID = @ID
  )
  begin
    raiserror('At least one transaction must be included before creating disbursements.', 16, 1)
  end
  -- $$$$



    select 
        @DATALOADED = 1
        ,@CHECKS_DISBURSEMENTDATE = DP.DISBURSEMENTDATE
        ,@CHECKS_NUMBEROFDISBURSEMENTS = DISBSUMMARY.NUMBEROFDISBURSEMENTS
        ,@CHECKS_TOTALAMOUNT = DISBSUMMARY.TOTALAMOUNT
        ,@CHECKS_NEXTUNUSED = (
            select 
        case when max(TRANSACTIONNUMBER)>0 then max(TRANSACTIONNUMBER) else 0 end
            from 
                dbo.BANKACCOUNTTRANSACTION as BAT
            where
                BAT.BANKACCOUNTID = DP.BANKACCOUNTID and BAT.DELETED = 0
            )+1
        ,@CHECKS_PRINTERID = DPF.PRINTERID
    ,@CHECKS_FORMAT = DF.ID
    from    
        dbo.DISBURSEMENTPROCESS as DP
        inner join dbo.DISBURSEMENTPROCESSFORMAT as DPF on DP.ID = DPF.DISBURSEMENTPROCESSID
        inner join dbo.DISBURSEMENTFORMAT as DF on DPF.DISBURSEMENTFORMATID    = DF.ID and DF.PAYMENTMETHODCODE = 0
        left outer join 
        (
            select    
                COUNT(*) as NUMBEROFDISBURSEMENTS
        ,SUM(FT.TRANSACTIONAMOUNT) as TOTALAMOUNT
          ,DPD.DISBURSEMENTPROCESSID
      from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
        inner join dbo.FINANCIALTRANSACTION as FT on DPD.ID = FT.ID AND TYPECODE = 255
            where FT.ID in (select DPD.ID 
                from dbo.DISBURSEMENTPROCESSDISBURSEMENT as DPD
                inner join dbo.FINANCIALTRANSACTIONAPPLICATION FTA on FTA.FINANCIALTRANSACTIONID = DPD.ID
                where FTA.AMOUNT != 0)
      group by DPD.DISBURSEMENTPROCESSID
        ) as DISBSUMMARY on DISBSUMMARY.DISBURSEMENTPROCESSID = DP.ID            
    where
        DP.ID = @ID

    return 0;