USP_DATAFORMTEMPLATE_VIEW_INVOICE

The load procedure used by the view dataform template "Invoice 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.
@AMOUNT	money	INOUT	Invoice amount
@BALANCE	money	INOUT	Balance
@INVOICEDATE	datetime	INOUT	Invoice date
@DATEDUE	datetime	INOUT	Due date
@DAYSPASTDUE	int	INOUT	Days past due
@POSTSTATUS	nvarchar(14)	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@PAYMENTMETHOD	nvarchar(50)	INOUT	Payment method
@DISCOUNTPERCENT	decimal(20, 4)	INOUT	Discount percent
@DISCOUNTEDTOTAL	money	INOUT	Amount after discount
@DISCOUNTAMOUNT	money	INOUT	Discount amount
@DISCOUNTEXPIRATIONDATE	datetime	INOUT	Discount expiration date
@HOLDPAYMENT	bit	INOUT	On hold
@PURCHASEORDERID	nvarchar(20)	INOUT	PO number
@ALLOWVENDORPAYMENTS	bit	INOUT	Payments are not allowed for this vendor
@APPLIEDAMOUNTS	money	INOUT	Payments and discounts taken
@ISLOCKED	bit	INOUT	Invoice is locked
@ZEROBALANCE	bit	INOUT	Invoice is fully paid
@HADDISCOUNT	bit	INOUT	Has discount

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_INVOICE
                (
                  @ID uniqueidentifier,
                  @DATALOADED bit = 0 output,
                  @AMOUNT money = null output,
                  @BALANCE money = null output,
                  @INVOICEDATE datetime = null output,
                  @DATEDUE datetime = null output,
                  @DAYSPASTDUE integer = null output,
                  @POSTSTATUS nvarchar(14) = null output,
                  @POSTDATE datetime = null output,
                  @PAYMENTMETHOD nvarchar(50) = null output,
                  @DISCOUNTPERCENT decimal(20,4) = null output,
                  @DISCOUNTEDTOTAL money = null output,
                  @DISCOUNTAMOUNT money = null output,
                  @DISCOUNTEXPIRATIONDATE datetime = null output,
                  @HOLDPAYMENT bit = null output,
                  @PURCHASEORDERID nvarchar(20) = null output,
          @ALLOWVENDORPAYMENTS bit = null output
          ,@APPLIEDAMOUNTS money = null output
          ,@ISLOCKED bit = null output
          ,@ZEROBALANCE bit = null output
            ,@HADDISCOUNT bit = 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.

                  -- Make sure to update Balance when the Payment Information comes in.
                  select top 1
                         @DATALOADED = 1,
                         @AMOUNT = F.TRANSACTIONAMOUNT,
                         @BALANCE = I.BALANCE,
                         @INVOICEDATE = F.[DATE],
                         @DATEDUE = I.DATEDUE,
                         @POSTSTATUS = F.POSTSTATUS,
                         @POSTDATE = F.POSTDATE,
                         @PAYMENTMETHOD = I.PAYMENTMETHOD,
                         @DAYSPASTDUE = (case 
                              when DATEDIFF(D, I.DATEDUE, GETDATE()) > 0 
                              then DATEDIFF(D, I.DATEDUE, GETDATE())
                              else 0 end),
                         @DISCOUNTPERCENT = I.DISCOUNTPERCENT,
                         @DISCOUNTEDTOTAL = I.DISCOUNTEDTOTAL,
                         @DISCOUNTAMOUNT = I.DISCOUNTAMOUNT,
                         @DISCOUNTEXPIRATIONDATE = I.DISCOUNTEXPIRATIONDATE,
                         @HOLDPAYMENT = I.HOLDPAYMENT,
                         @PURCHASEORDERID = I.PURCHASEORDERID,
             @ALLOWVENDORPAYMENTS = PV.ALLOWPAYMENTS,
             @ISLOCKED = case when I.DISBURSEMENTPROCESSID is null then 0 else 1 end,
             @ZEROBALANCE = I.ZEROBALANCE
                  from dbo.INVOICE I
                      inner join dbo.FINANCIALTRANSACTION F on F.ID = I.ID and F.TYPECODE = 101
                      inner join dbo.CONSTITUENT C on F.CONSTITUENTID = C.ID
            inner join dbo.VENDOR PV on PV.ID = C.ID
                  where F.ID = @ID;

          select 
            @APPLIEDAMOUNTS = sum(FTA.AMOUNT)
          from 
            dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
            inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID
          where
            FTS.FINANCIALTRANSACTIONID = @ID and FTS.DELETED = 0
            and FTA.STATUSCODE = 1 -- applied;            

                    select @HADDISCOUNT = 1
                    from dbo.FINANCIALTRANSACTIONSCHEDULE as FTS
            inner join dbo.FINANCIALTRANSACTIONAPPLICATION as FTA on FTS.ID = FTA.FINANCIALTRANSACTIONSCHEDULEID
          where
            FTS.FINANCIALTRANSACTIONID = @ID and FTS.DELETED = 0
            and FTA.STATUSCODE = 1 -- applied 
                        and FTA.TYPECODE = 1 --Discount;

                    if @HADDISCOUNT is null
                        set @HADDISCOUNT = 0;

                  return 0;