USP_DATAFORMTEMPLATE_EDITLOAD_INVOICESCHEDULE

The load procedure used by the edit dataform template "Invoice Schedule Edit 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.
@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.
@INVOICEAMOUNT	money	INOUT	Invoice amount
@INVOICEUNSCHEDULEDBALANCE	money	INOUT	Unscheduled balance
@INVOICESCHEDULES	xml	INOUT	Invoice schedule

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_INVOICESCHEDULE(
    @ID uniqueidentifier
    ,@DATALOADED bit = 0 output
    ,@TSLONG bigint = 0 output
    ,@INVOICEAMOUNT money = null output
    ,@INVOICEUNSCHEDULEDBALANCE money = null output
    ,@INVOICESCHEDULES xml = null output
)
as

    set nocount on;

    -- be sure to set these, in case the select returns no rows
    set @DATALOADED = 0;
    set @TSLONG = 0;

    select 
    @DATALOADED = 1
    ,@TSLONG = I.TSLONG
    ,@INVOICEAMOUNT = FT.TRANSACTIONAMOUNT
    ,@INVOICEUNSCHEDULEDBALANCE = APP.UNSCHEDULEDBALANCE
    ,@INVOICESCHEDULES = dbo.UFN_INVOICE_SCHEDULES_TOITEMLISTXML(I.ID)
    from dbo.INVOICE as I
    inner join dbo.FINANCIALTRANSACTION as FT on I.ID = FT.ID
    inner join (
      select
        FTS.FINANCIALTRANSACTIONID
        ,sum(FTS.AMOUNT) as UNSCHEDULEDBALANCE -- Sum the amount of the schedule
      from dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
        left outer join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA 
          on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID
      where
        FTA.ID is null  -- Where there was no application whatsoever
                and FTS.DELETED = 0
      group by
        FTS.FINANCIALTRANSACTIONID -- Link with the financialtransaction id
    ) as APP 
      on APP.FINANCIALTRANSACTIONID = FT.ID        
    where I.ID = @ID
    and I.DISBURSEMENTPROCESSID is NULL;

  if @DATALOADED = 0 
    raiserror('ERR_INVOICE_FULLY_PAID',16,1);

    return 0;