USP_DATAFORMTEMPLATE_VIEW_PAYABLES_VENDOR

The load procedure used by the view dataform template "Payables Vendor 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.
@NAME	nvarchar(154)	INOUT	Name
@FORMATTEDADDRESS	nvarchar(300)	INOUT	Address
@PHONENUMBER	nvarchar(100)	INOUT	PHONENUMBER
@EMAILADDRESS	UDT_EMAILADDRESS	INOUT	EMAILADDRESS
@WEBADDRESS	UDT_WEBADDRESS	INOUT	WEBADDRESS
@LOOKUPID	nvarchar(100)	INOUT	Lookup ID
@ALLOWPAYMENTS	bit	INOUT	ALLOWPAYMENTS
@ALLOWTOCREATEINVOICES	bit	INOUT	ALLOWTOCREATEINVOICES
@INCLUDEIN1099	bit	INOUT	INCLUDEIN1099
@HAS1099ADJUSTMENTS	bit	INOUT	HAS1099ADJUSTMENTS
@PARENTORGNAME	nvarchar(154)	INOUT	Parent organization
@PARENTORGID	uniqueidentifier	INOUT	PARENTORGID
@CURRENTBALANCE	money	INOUT	Balance
@AMOUNTPASTDUE	money	INOUT	Amount past due
@LASTPAYMENTAMOUNT	money	INOUT	Last payment amount
@LASTPAYMENTDATE	datetime	INOUT	Last payment date
@PICTURE	varbinary	INOUT	PICTURE
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@ISORG	bit	INOUT	ISORG
@ISPROSPECT	bit	INOUT	ISPROSPECT
@ISFORMERPROSPECT	bit	INOUT	ISFORMERPROSPECT
@ISFUNDRAISER	bit	INOUT	ISFUNDRAISER
@ISFORMERFUNDRAISER	bit	INOUT	ISFORMERFUNDRAISER
@ISVOLUNTEER	bit	INOUT	ISVOLUNTEER
@ISFORMERVOLUNTEER	bit	INOUT	ISFORMERVOLUNTEER
@ISCOMMUNITYMEMBER	bit	INOUT	ISCOMMUNITYMEMBER
@ISREGISTRANT	bit	INOUT	ISREGISTRANT
@ISVENDOR	bit	INOUT	ISVENDOR
@APPUSERID	uniqueidentifier	INOUT	APPUSERID
@ISALUMNUS	bit	INOUT	ISALUMNUS
@ISSTUDENT	bit	INOUT	ISSTUDENT
@ISFORMERSTUDENT	bit	INOUT	ISFORMERSTUDENT
@ISGROUPMEMBER	bit	INOUT	ISGROUPMEMBER
@HASCOMMUNITYDATA	bit	INOUT	HASCOMMUNITYDATA
@ISPLANNEDGIVER	bit	INOUT	ISPLANNEDGIVER
@DONOTMAIL	bit	INOUT	DONOTMAIL
@DONOTEMAIL	bit	INOUT	DONOTEMAIL
@DONOTPHONE	bit	INOUT	DONOTPHONE
@PHONEISCONFIDENTIAL	bit	INOUT	PHONEISCONFIDENTIAL
@ADDRESSISCONFIDENTIAL	bit	INOUT	ADDRESSISCONFIDENTIAL
@ADDRESSID	uniqueidentifier	INOUT	ADDRESSID
@PHONENUMBERID	uniqueidentifier	INOUT	PHONENUMBERID
@EMAILADDRESSID	uniqueidentifier	INOUT	EMAILADDRESSID

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_PAYABLES_VENDOR 
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(154) = null output,
    @FORMATTEDADDRESS nvarchar(300) = null output,
    @PHONENUMBER nvarchar(100) = null output,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS = null output,
    @WEBADDRESS dbo.UDT_WEBADDRESS = null output,
    @LOOKUPID nvarchar(100) = null output,
    @ALLOWPAYMENTS bit = null output,
    @ALLOWTOCREATEINVOICES bit = null output,
    @INCLUDEIN1099 bit = null output,
    @HAS1099ADJUSTMENTS bit = null output,
    @PARENTORGNAME nvarchar(154) = null output,
    @PARENTORGID uniqueidentifier = null output,
    @CURRENTBALANCE money = null output,
    @AMOUNTPASTDUE money = null output,
    @LASTPAYMENTAMOUNT money = null output,
    @LASTPAYMENTDATE datetime = null output,
    @PICTURE varbinary(max) = null output,
    @CURRENTAPPUSERID uniqueidentifier,
    @ISORG bit = null output,
    @ISPROSPECT bit = null output,
    @ISFORMERPROSPECT bit = null output,
    @ISFUNDRAISER bit = null output,
    @ISFORMERFUNDRAISER bit = null output,
    @ISVOLUNTEER bit = null output,
    @ISFORMERVOLUNTEER bit = null output,
    @ISCOMMUNITYMEMBER bit = null output,
    @ISREGISTRANT bit = null output,
    @ISVENDOR bit = null output,
    @APPUSERID uniqueidentifier = null output,
    @ISALUMNUS bit = null output,
    @ISSTUDENT bit = null output,
    @ISFORMERSTUDENT bit = null output,
    @ISGROUPMEMBER bit = null output,
    @HASCOMMUNITYDATA bit = null output,
    @ISPLANNEDGIVER bit = null output,
    @DONOTMAIL bit = null output,
    @DONOTEMAIL bit = null output,
    @DONOTPHONE bit = null output,
    @PHONEISCONFIDENTIAL bit = null output,
    @ADDRESSISCONFIDENTIAL bit = null output,
    @ADDRESSID uniqueidentifier = null output,
    @PHONENUMBERID uniqueidentifier = null output,
    @EMAILADDRESSID uniqueidentifier = null output
)
as
    set nocount on;

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

  declare @ISINACTIVE bit;
  declare @DECEASED bit;
  declare @ISSELF bit;
  declare @ATTRIBUTEDEFINED bit;
  declare @SMARTFIELDDEFINED bit;
  declare @UPDATEPENDING bit;
  declare @CONSTITUENTID uniqueidentifier;
  declare @HASSPOUSE bit;
  declare @AGE int;
  declare @VALIDSEARCHCRITERIASTRICT bit;
  declare @VALIDSEARCHCRITERIARELAXED bit;
  declare @EDUCATIONATTRIBUTEDEFINED bit;
  declare @ISGROUP bit;
  declare @GROUPCANBEDONOR bit;
  declare @CURRENTMEMBERCOUNT integer;
  declare @PREVIOUSMEMBERCOUNT integer;
  declare @ISHOUSEHOLD bit;
  declare @HOUSEHOLDID uniqueidentifier;
  declare @MAPPINGCREDENTIALSEXIST bit;
  declare @HASSTEWARDSHIPPLAN bit;
  declare @DECEASINGOPTIONSSET bit;
  declare @ISDISSOLVED bit;
  declare @SPOUSERELATIONSHIPID uniqueidentifier;
  declare @ISPAYABLESVENDOR bit;

  exec dbo.USP_CONSTITUENTGETEXPRESSIONDATA @ID, @CURRENTAPPUSERID output, @DATALOADED output, @NAME output, @ISINACTIVE output, @DECEASED output, @ISSELF output, @ISORG output, @ISPROSPECT output, @ISFORMERPROSPECT output, @ISFUNDRAISER output, @ISFORMERFUNDRAISER output, @ISVOLUNTEER output, @ISFORMERVOLUNTEER output, @ISCOMMUNITYMEMBER output, @ISREGISTRANT output, @ISVENDOR output, @APPUSERID output, @ATTRIBUTEDEFINED output, @SMARTFIELDDEFINED output, @UPDATEPENDING output, @CONSTITUENTID output, @HASSPOUSE output, @AGE output, @ISALUMNUS output, @ISSTUDENT output, @ISFORMERSTUDENT output, @VALIDSEARCHCRITERIASTRICT output, @VALIDSEARCHCRITERIARELAXED output, @EDUCATIONATTRIBUTEDEFINED output, @HASCOMMUNITYDATA output, @ISGROUPMEMBER output, @ISGROUP output, @GROUPCANBEDONOR output, @CURRENTMEMBERCOUNT output, @PREVIOUSMEMBERCOUNT output, @ISHOUSEHOLD output, @HOUSEHOLDID output, @MAPPINGCREDENTIALSEXIST output, @HASSTEWARDSHIPPLAN output, @DECEASINGOPTIONSSET output, @ISDISSOLVED output, @SPOUSERELATIONSHIPID output, @ISPLANNEDGIVER output, @ISPAYABLESVENDOR output;

    -- 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.
    select @DATALOADED = 1,
        @FORMATTEDADDRESS = dbo.UFN_BUILDFULLADDRESS(null, A.ADDRESSBLOCK, A.CITY, A.STATEID, A.POSTCODE, A.COUNTRYID),
        @DONOTMAIL = A.DONOTMAIL,
        @ADDRESSISCONFIDENTIAL = A.ISCONFIDENTIAL,
        @ADDRESSID = A.ID,        
        @PHONENUMBER = P.NUMBER,
        @DONOTPHONE = P.DONOTCALL,
        @PHONEISCONFIDENTIAL = P.ISCONFIDENTIAL,
        @PHONENUMBERID = P.ID,
        @EMAILADDRESS = EA.EMAILADDRESS,
        @DONOTEMAIL = EA.DONOTEMAIL,
        @EMAILADDRESSID = EA.ID,
        @WEBADDRESS = C.WEBADDRESS,
        @LOOKUPID = C.LOOKUPID,
        @ALLOWPAYMENTS = V.ALLOWPAYMENTS,
        @ALLOWTOCREATEINVOICES = V.ALLOWTOCREATEINVOICES,
        @INCLUDEIN1099 = V.INCLUDEIN1099,
        @PARENTORGNAME = PARENTORG.NAME,
        @PARENTORGID = PARENTORG.ID,
        @PICTURE = C.PICTURETHUMBNAIL

    from dbo.VENDOR as V
    inner join dbo.CONSTITUENT AS C on C.ID = V.ID
      left outer join dbo.ADDRESS as A on C.ID = A.CONSTITUENTID and A.ISPRIMARY = 1  
    left outer join dbo.PHONE as P on C.ID = P.CONSTITUENTID and P.ISPRIMARY = 1
    left outer join dbo.EMAILADDRESS as EA on C.ID = EA.CONSTITUENTID and EA.ISPRIMARY = 1
    left outer join dbo.ORGANIZATIONDATA as ORG on ORG.ID = C.ID
    left outer join dbo.CONSTITUENT as PARENTORG on PARENTORG.ID = ORG.PARENTCORPID

    where V.ID = @ID
    and C.ISGROUP = 0 -- No Vendors are groups

    SET @CURRENTBALANCE = dbo.UFN_VENDOR_CURRENTBALANCE(@ID, NULL);
    SET @AMOUNTPASTDUE = dbo.UFN_VENDOR_CURRENTBALANCE(@ID, GETDATE());
    SET @LASTPAYMENTAMOUNT = 0;
    SET @LASTPAYMENTDATE = null;

 select top 1
      @LASTPAYMENTAMOUNT = BAT.AMOUNT
      ,@LASTPAYMENTDATE = FT.[DATE]
  from         
      dbo.BANKACCOUNTTRANSACTION as BAT
      inner join dbo.FINANCIALTRANSACTION as FT 
          on FT.ID = BAT.ID
  where    
      FT.CONSTITUENTID = @ID and STATUSCODE <> 4 and BAT.PROCESSING = 0
  order by
      FT.DATE DESC
      ,BAT.TRANSACTIONNUMBER DESC

  if exists(select * 
            from dbo.VENDOR1099SETTING VS
            inner join dbo.VENDOR1099ADJUSTMENT VA on VS.ID = VA.VENDOR1099SETTINGID
            where VS.ID = @ID)
    SET @HAS1099ADJUSTMENTS = 1;

    return 0;