USP_DATAFORMTEMPLATE_EDITLOAD_INVOICE

The load procedure used by the edit dataform template "Invoice 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.
@NUMBER	nvarchar(60)	INOUT	Invoice number
@VENDORID	uniqueidentifier	INOUT	Vendor
@AMOUNT	money	INOUT	Invoice amount
@DATE	datetime	INOUT	Invoice date
@PURCHASEORDERID	nvarchar(20)	INOUT	PO number
@DATEDUE	datetime	INOUT	Due date
@POSTSTATUSCODE	tinyint	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@DISCOUNTPERCENT	decimal(20, 4)	INOUT	Discount percent
@DISCOUNTAMOUNT	money	INOUT	Discount amount
@DISCOUNTEXPIRATIONDATE	datetime	INOUT	Discount expiration date
@DISCOUNTTOTAL	money	INOUT	Amount after discount
@PAYMENTMETHODCODE	tinyint	INOUT	Payment method
@BANKACCOUNTID	uniqueidentifier	INOUT	Bank account
@REMITADDRESSID	uniqueidentifier	INOUT	Remit to address
@SEPARATEPAYMENT	bit	INOUT	Create a separate disbursement for this invoice
@ISEDITFORM	bit	INOUT
@FREQUENCYCODE	smallint	INOUT	Frequency
@SCHEDULESTARTDATE	date	INOUT	Starting on
@NUMBEROFINSTALLMENTS	int	INOUT	No. installments
@INVOICESCHEDULES	xml	INOUT	Invoice schedules
@HASSCHEDULE	bit	INOUT	Has Schedule
@BALANCE	money	INOUT
@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.
@DESCRIPTION	nvarchar(100)	INOUT	Invoice description
@LINEITEMS	xml	INOUT	Line items
@PAID	bit	INOUT
@PAYMENTPOSTED	bit	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_INVOICE(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NUMBER nvarchar(60) = null output,
    @VENDORID uniqueidentifier = null output,
    @AMOUNT money = null output,
    @DATE datetime = null output,
    @PURCHASEORDERID nvarchar(20) = null output,
    @DATEDUE datetime = null output,
    @POSTSTATUSCODE tinyint = null output,
    @POSTDATE datetime = null output,
    @DISCOUNTPERCENT decimal(20,4) = null output,
    @DISCOUNTAMOUNT money = null output,
    @DISCOUNTEXPIRATIONDATE datetime = null output,
  @DISCOUNTTOTAL money = null output,
    @PAYMENTMETHODCODE tinyint = null output,
    @BANKACCOUNTID uniqueidentifier = null output,
    @REMITADDRESSID uniqueidentifier = null output,
    @SEPARATEPAYMENT bit = null output,
  @ISEDITFORM bit = null output
  ,@FREQUENCYCODE smallint = null output
  ,@SCHEDULESTARTDATE date = null output
  ,@NUMBEROFINSTALLMENTS integer = null output
  ,@INVOICESCHEDULES xml = null output
  ,@HASSCHEDULE bit = null output -- Because Infinity does NOT have nulls except sometimes, like here on the left.
  ,@BALANCE money = null output -- used internally to enable/disable fields
  ,@TSLONG bigint = 0 output
    ,@DESCRIPTION nvarchar(100) = null output
    ,@LINEITEMS xml = null output
  ,@PAID bit = null output
  ,@PAYMENTPOSTED bit = 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 top 1
        @DATALOADED = case when (select DISBURSEMENTPROCESSID from dbo.INVOICE where ID = @ID) is null then 1 else 0 end,
    @TSLONG = I.TSLONG,
        @NUMBER = F.USERDEFINEDID,
        @VENDORID = F.CONSTITUENTID,
        @AMOUNT = F.TRANSACTIONAMOUNT,
        @DATE = F.[DATE],
        @PURCHASEORDERID = I.PURCHASEORDERID,
        @DATEDUE = I.DATEDUE,
        @POSTSTATUSCODE = F.POSTSTATUSCODE,
        @POSTDATE = F.POSTDATE,
        @DISCOUNTPERCENT = I.DISCOUNTPERCENT,
        @DISCOUNTAMOUNT = I.DISCOUNTAMOUNT,
        @DISCOUNTEXPIRATIONDATE = I.DISCOUNTEXPIRATIONDATE,
    @DISCOUNTTOTAL = (F.TRANSACTIONAMOUNT - I.DISCOUNTAMOUNT),
        @PAYMENTMETHODCODE = 1,
        @BANKACCOUNTID = I.BANKACCOUNTID,
        @REMITADDRESSID = I.REMITADDRESSID,
        @SEPARATEPAYMENT = I.SEPARATEPAYMENT,
    @ISEDITFORM = 1
    ,@SCHEDULESTARTDATE = COALESCE(ISI.STARTDATE, I.DATEDUE)
    ,@NUMBEROFINSTALLMENTS = COALESCE(ISI.NUMBEROFINSTALLMENTS,1) -- Since the platform does validate even if the field has no meaning when HASSCHEDULE is false.
    ,@FREQUENCYCODE = COALESCE( ISI.FREQUENCYCODE,1) 
    ,@INVOICESCHEDULES = dbo.UFN_INVOICE_SCHEDULES_TOITEMLISTXML(I.ID)
    ,@HASSCHEDULE = case when ISI.STARTDATE is null then 0 else 1 end
    ,@BALANCE = I.BALANCE
        ,@DESCRIPTION = F.DESCRIPTION
        ,@LINEITEMS = dbo.UFN_FINANCIALTRANSACTION_LINEITEMS_TOITEMLISTXML(@ID)
    ,@PAID = dbo.UFN_FINANCIALTRANSACTION_PAID(@ID)
    ,@PAYMENTPOSTED = dbo.UFN_FINANCIALTRANSACTION_PAYMENTPOSTED(@ID)
    from dbo.INVOICE I
      inner join dbo.FINANCIALTRANSACTION F on F.ID = I.ID and F.TYPECODE = 101
    left outer join dbo.INVOICESCHEDULEINFORMATION as ISI
      on I.ID = ISI.ID
    where F.ID = @ID;


    return 0;