USP_SEARCHLIST_PAYABLEVENDOR

Search for and view vendor records.
Parameters

Parameter	Parameter Type	Mode	Description
@KEYNAME	nvarchar(50)	IN	Last/Org name
@FIRSTNAME	nvarchar(50)	IN	First name
@LOOKUPID	nvarchar(36)	IN	Lookup ID
@CUSTOMERNUMBER	nvarchar(20)	IN	Customer number
@INDUSTRYCODEID	uniqueidentifier	IN	Industry
@ADDRESSBLOCK	nvarchar(100)	IN	Address
@CITY	nvarchar(100)	IN	City
@STATEID	uniqueidentifier	IN	State
@POSTCODE	nvarchar(20)	IN	ZIP/Postal code
@COUNTRYID	uniqueidentifier	IN	Country
@EXACTMATCHONLY	bit	IN	Match all criteria exactly
@INCLUDEINDIVIDUALS	bit	IN	Individuals
@INCLUDEORGANIZATIONS	bit	IN	Organizations
@CHECKALIASES	bit	IN	Check aliases
@PRIMARYADDRESSONLY	bit	IN	Only search primary addresses
@CHECKALTERNATELOOKUPIDS	bit	IN	Check alternate lookup IDs
@FUZZYSEARCHONNAME	bit	IN	Include fuzzy search on name
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
Definition

Copy


CREATE procedure dbo.USP_SEARCHLIST_PAYABLEVENDOR
(
    @KEYNAME nvarchar(50) = null
  ,@FIRSTNAME nvarchar(50) = null
  ,@LOOKUPID nvarchar(36) = null
  ,@CUSTOMERNUMBER nvarchar(20) = null
  ,@INDUSTRYCODEID uniqueidentifier = null

  ,@ADDRESSBLOCK nvarchar(100) = null
  ,@CITY nvarchar(100) = null
  ,@STATEID uniqueidentifier = null
  ,@POSTCODE nvarchar(20) = null
  ,@COUNTRYID uniqueidentifier = null

  ,@EXACTMATCHONLY bit = null
  ,@INCLUDEINDIVIDUALS bit = null
  ,@INCLUDEORGANIZATIONS bit = null
  ,@CHECKALIASES bit = null
  ,@PRIMARYADDRESSONLY bit = null
  ,@CHECKALTERNATELOOKUPIDS bit = null
  ,@FUZZYSEARCHONNAME bit = null

    ,@MAXROWS smallint = 500
)
as
  -- Assume we are checking addresses unless ALL these requirements are met. 

  -- Empty strings are checked because of Infinity

  declare @CHECKADDRESS bit = 1;
  if (@ADDRESSBLOCK is null or @ADDRESSBLOCK = '')
      and (@CITY is null or @CITY = '') 
      and (@POSTCODE is null or @POSTCODE = '') 
      and @STATEID is null 
      and @COUNTRYID is null
    set @CHECKADDRESS = 0;

  set @EXACTMATCHONLY = COALESCE(@EXACTMATCHONLY, 0);
    set @INCLUDEINDIVIDUALS = COALESCE(@INCLUDEINDIVIDUALS, 0);
  set @INCLUDEORGANIZATIONS = COALESCE(@INCLUDEORGANIZATIONS, 0);
  set @PRIMARYADDRESSONLY = COALESCE(@PRIMARYADDRESSONLY, 0);
    set @CHECKALTERNATELOOKUPIDS = COALESCE(@CHECKALTERNATELOOKUPIDS, 0);
  set @FUZZYSEARCHONNAME = COALESCE(@FUZZYSEARCHONNAME, 0);

  if @EXACTMATCHONLY = 0
      begin
      set @KEYNAME = COALESCE(@KEYNAME,'') + '%';
          set @FIRSTNAME = COALESCE(@FIRSTNAME,'') + '%';
          set @LOOKUPID = COALESCE(@LOOKUPID,'') + '%';
      set @CUSTOMERNUMBER = COALESCE(@CUSTOMERNUMBER,'') + '%';
      set @ADDRESSBLOCK = COALESCE(@ADDRESSBLOCK,'') + '%';
      set @CITY = COALESCE(@CITY,'') + '%';
      set @POSTCODE = COALESCE(@POSTCODE,'') + '%';

      select top(@MAXROWS) 
          V.ID
          ,C.LOOKUPID [LOOKUPID]
          ,dbo.UFN_NAMEFORMAT_08(C.ID, C.KEYNAME, C.FIRSTNAME, C.MIDDLENAME, null, null, null, null, null, null, null) as SORTVENDORNAME
        ,C.NAME
          ,case 
            when C.ISGROUP = 0 and C.ISORGANIZATION = 0 then 'Individual'
            when C.ISORGANIZATION = 1 then 'Organization' 
           end as VENDORTYPE
          ,C.ISORGANIZATION
        ,V.CUSTOMERNUMBER
          ,A.ADDRESSBLOCK [ADDRESS]
          ,A.CITY
          ,S.ABBREVIATION [STATE]
          ,A.POSTCODE
          ,I.[DESCRIPTION] INDUSTRY
          ,C.WEBADDRESS
          ,E.EMAILADDRESS
          ,P.NUMBER [PHONE]
      from dbo.VENDOR V
          inner join dbo.CONSTITUENT C on V.ID = C.ID
          left join dbo.PHONE P on (P.ISPRIMARY = 1 and P.CONSTITUENTID = C.ID)
          left join dbo.EMAILADDRESS E on (E.ISPRIMARY = 1 and E.CONSTITUENTID = C.ID)
          left join dbo.[ADDRESS] A on (A.ISPRIMARY = 1 and A.CONSTITUENTID = C.ID)
          left join dbo.[STATE] S on A.STATEID = S.ID
          left join dbo.ORGANIZATIONDATA OD on (C.ISORGANIZATION = 1 and OD.ID = C.ID)
          left join dbo.CONSTITUENTJOBINFO CJ on (C.ISORGANIZATION = 0 and CJ.ID = C.ID)
          left join dbo.INDUSTRYCODE I on coalesce(OD.INDUSTRYCODEID, CJ.INDUSTRYCODEID) = I.ID
      where
        ((C.ISORGANIZATION = 1 and @INCLUDEORGANIZATIONS = 1) or (C.ISORGANIZATION = 0 and @INCLUDEINDIVIDUALS = 1))
        -- Last/Org Name & Fuzzy Search

        and (c.KEYNAME like @KEYNAME 
          or (C.KEYNAMESOUNDEX = soundex(@KEYNAME) and @FUZZYSEARCHONNAME <> 0)
          or (C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.KEYNAME like @KEYNAME) and @CHECKALIASES <> 0))
        -- First Name & Fuzzy Search

        and (c.FIRSTNAME like @FIRSTNAME 
          or (soundex(C.FIRSTNAME) = soundex(@FIRSTNAME) and @FUZZYSEARCHONNAME <> 0 and C.ISORGANIZATION = 0)
          or (C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.FIRSTNAME like @FIRSTNAME) and @CHECKALIASES <> 0 and C.ISORGANIZATION = 0))
        -- Lookup ID

        and ( (C.LOOKUPID like @LOOKUPID) 
          or ((C.ID in (select AL.CONSTITUENTID from dbo.ALTERNATELOOKUPID AL where AL.ALTERNATELOOKUPID like @LOOKUPID) 
            and @CHECKALTERNATELOOKUPIDS <> 0)) )
        -- Customer Number

        and (V.CUSTOMERNUMBER like @CUSTOMERNUMBER)
        -- Industry

        and (@INDUSTRYCODEID is null or I.ID = @INDUSTRYCODEID)
        -- Address fields

        and (
          (A.ADDRESSBLOCK like @ADDRESSBLOCK
          and A.CITY like @CITY
          and A.POSTCODE like @POSTCODE
          and (A.STATEID = @STATEID or @STATEID is null)
          and (A.COUNTRYID = @COUNTRYID or @COUNTRYID is null)) 
          or (A.ID is null and @CHECKADDRESS = 0) 
          or (@PRIMARYADDRESSONLY = 0 and @CHECKADDRESS = 1
              and C.ID in (select AD.CONSTITUENTID 
                           from dbo.[ADDRESS] AD 
                           where 
                            AD.ADDRESSBLOCK like @ADDRESSBLOCK 
                            and AD.CITY like @CITY 
                            and AD.POSTCODE like @POSTCODE 
                            and (AD.STATEID = @STATEID or @STATEID is null) 
                            and (AD.COUNTRYID = @COUNTRYID or @COUNTRYID is null)))
        )
      order by 
        SORTVENDORNAME ASC
    end
    else
    begin
      select top(@MAXROWS) 
          V.ID
          ,C.LOOKUPID [LOOKUPID]
          ,dbo.UFN_NAMEFORMAT_08(C.ID, C.KEYNAME, C.FIRSTNAME, C.MIDDLENAME, null, null, null, null, null, null, null) as SORTVENDORNAME
        ,C.NAME 
          ,case 
            when C.ISGROUP = 0 and C.ISORGANIZATION = 0 then 'Individual'
            when C.ISORGANIZATION = 1 then 'Organization' 
           end as VENDORTYPE
          ,C.ISORGANIZATION
        ,V.CUSTOMERNUMBER
          ,A.ADDRESSBLOCK [ADDRESS]
          ,A.CITY
          ,S.ABBREVIATION [STATE]
          ,A.POSTCODE
          ,I.[DESCRIPTION] INDUSTRY
          ,C.WEBADDRESS
          ,E.EMAILADDRESS
          ,P.NUMBER [PHONE]
      from dbo.VENDOR V
          inner join dbo.CONSTITUENT C on V.ID = C.ID
          left join dbo.PHONE P on (P.ISPRIMARY = 1 and P.CONSTITUENTID = C.ID)
          left join dbo.EMAILADDRESS E on (E.ISPRIMARY = 1 and E.CONSTITUENTID = C.ID)
          left join dbo.[ADDRESS] A on (A.ISPRIMARY = 1 and A.CONSTITUENTID = C.ID)
          left join dbo.[STATE] S on A.STATEID = S.ID
          left join dbo.ORGANIZATIONDATA OD on (C.ISORGANIZATION = 1 and OD.ID = C.ID)
          left join dbo.CONSTITUENTJOBINFO CJ on (C.ISORGANIZATION = 0 and CJ.ID = C.ID)
          left join dbo.INDUSTRYCODE I on coalesce(OD.INDUSTRYCODEID, CJ.INDUSTRYCODEID) = I.ID
      where
        ((C.ISORGANIZATION = 1 and @INCLUDEORGANIZATIONS = 1) or (C.ISORGANIZATION = 0 and @INCLUDEINDIVIDUALS = 1))
        -- Last/Org Name & Fuzzy Search

        and ((@KEYNAME is null) 
          or (c.KEYNAME = @KEYNAME 
            or (C.KEYNAMESOUNDEX = soundex(@KEYNAME) and @FUZZYSEARCHONNAME <> 0) 
            or (C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.KEYNAME = @KEYNAME) and @CHECKALIASES <> 0)))
        -- First Name & Fuzzy Search

        and ((@FIRSTNAME is null) 
          or (c.FIRSTNAME = @FIRSTNAME 
            or (soundex(C.FIRSTNAME) = soundex(@FIRSTNAME) and @FUZZYSEARCHONNAME <> 0 and C.ISORGANIZATION = 0)
            or (C.ID in (select A.CONSTITUENTID from dbo.ALIAS A where A.FIRSTNAME = @FIRSTNAME) and @CHECKALIASES <> 0)))
        -- Lookup ID

        and (((@LOOKUPID is null) 
          or (C.LOOKUPID = @LOOKUPID) 
            or ((C.ID in (select AL.CONSTITUENTID from dbo.ALTERNATELOOKUPID AL where @LOOKUPID is not null and AL.ALTERNATELOOKUPID = @LOOKUPID) 
              and @CHECKALTERNATELOOKUPIDS <> 0))) )
        -- Customer Number

        and (@CUSTOMERNUMBER is null or v.CUSTOMERNUMBER = @CUSTOMERNUMBER)
        -- Industry

        and (@INDUSTRYCODEID is null or I.ID = @INDUSTRYCODEID)
        -- Address fields

        and (
          ((A.ADDRESSBLOCK = @ADDRESSBLOCK or @ADDRESSBLOCK is null)
          and (A.CITY = @CITY or @CITY is null)
          and (A.POSTCODE = @POSTCODE or @POSTCODE is null)
          and (A.STATEID = @STATEID or @STATEID is null)
          and (A.COUNTRYID = @COUNTRYID or @COUNTRYID is null)) 
          or (A.ID is null and @CHECKADDRESS = 0) 
          or (@PRIMARYADDRESSONLY = 0 and @CHECKADDRESS = 1
              and C.ID in (select AD.CONSTITUENTID 
                           from dbo.[ADDRESS] AD 
                           where 
                            (AD.ADDRESSBLOCK = @ADDRESSBLOCK or @ADDRESSBLOCK is null)
                            and (AD.CITY = @CITY or @CITY is null)
                            and (AD.POSTCODE = @POSTCODE or @POSTCODE is null)
                            and (AD.STATEID = @STATEID or @STATEID is null) 
                            and (AD.COUNTRYID = @COUNTRYID or @COUNTRYID is null)))
        )
      order by 
        SORTVENDORNAME ASC
    end