USP_DATAFORMTEMPLATE_VIEW_INVOICEPAGEDATA

The load procedure used by the view dataform template "Invoice Page Expression 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.
@INVOICENUMBER	nvarchar(60)	INOUT	Invoice number
@VENDORNAME	nvarchar(100)	INOUT	Vendor name
@VENDORID	uniqueidentifier	INOUT	Vendor ID
@AMOUNT	money	INOUT	Invoice amount
@INVOICEDATE	datetime	INOUT	Invoice date
@HOLDPAYMENT	bit	INOUT	On hold
@ALLOWVENDORPAYMENTS	bit	INOUT	Payments are not allowed for this vendor
@IS1099VENDOR	bit	INOUT	Is 1099 vendor?
@HAS1099INFO	bit	INOUT	Invoice has 1099 info
@ISLOCKED	bit	INOUT	Invoice is locked
@HASSCHEDULE	bit	INOUT	Invoice has a schedule
@HASPAYMENTS	bit	INOUT	Invoice has a payment
@ISFULLYPAID	bit	INOUT	Invoice is fully paid

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_INVOICEPAGEDATA
                (
                  @ID uniqueidentifier
                  ,@DATALOADED bit = 0 output
                  ,@INVOICENUMBER nvarchar(60) = null output
                  ,@VENDORNAME nvarchar(100) = null output
                  ,@VENDORID uniqueidentifier = null output
                  ,@AMOUNT money = null output
                  ,@INVOICEDATE datetime = null output
                  ,@HOLDPAYMENT bit = null output
          ,@ALLOWVENDORPAYMENTS bit = null output
          ,@IS1099VENDOR bit = null output
          ,@HAS1099INFO bit = null output
              ,@ISLOCKED bit = null output
          ,@HASSCHEDULE bit = null output
          ,@HASPAYMENTS bit = null output
          ,@ISFULLYPAID 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
                         ,@INVOICENUMBER = F.USERDEFINEDID
                         ,@VENDORNAME = C.NAME
                         ,@VENDORID = C.ID
                         ,@AMOUNT = F.TRANSACTIONAMOUNT
                         ,@INVOICEDATE = F.[DATE]
                         ,@HOLDPAYMENT = I.HOLDPAYMENT
             ,@ALLOWVENDORPAYMENTS = PV.ALLOWPAYMENTS
             ,@IS1099VENDOR = PV.INCLUDEIN1099
                   ,@ISLOCKED = case when I.DISBURSEMENTPROCESSID is null then 0 else 1 end
             ,@HASSCHEDULE = case when ISI.ID is null then 0 else 1 end
             ,@ISFULLYPAID = case when I.BALANCE = 0 then 1 else 0 end
                  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
            left outer join dbo.INVOICESCHEDULEINFORMATION as ISI on F.ID = ISI.ID            
                  where F.ID = @ID;

                set @HASPAYMENTS = dbo.UFN_PAYABLES_HASPAYMENTS(@ID);

          if exists(select * from dbo.FINANCIALTRANSACTION1099DISTRIBUTION where FINANCIALTRANSACTIONID = @ID)
            SET @HAS1099INFO = 1;

            return 0;