USP_DUPLICATESEARH_PARTIALINFORMATIONSEARCHBUSINESSOWNERSHIP

This provides the ability to search for duplicates when adding a business ownership record to a constituent.

Parameters

Parameter	Parameter Type	Mode	Description
@PARTIALINFORMATIONSEARCHRESULTID	uniqueidentifier	IN	PIS result ID
@CONSTITUENTID	uniqueidentifier	IN	Constituent ID
@DUNSNUM	nvarchar(100)	IN	DUNS
@COMPANY	nvarchar(100)	IN	Company
@ADDRESS	nvarchar(100)	IN	Address
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



CREATE procedure dbo.USP_DUPLICATESEARH_PARTIALINFORMATIONSEARCHBUSINESSOWNERSHIP
(
    @PARTIALINFORMATIONSEARCHRESULTID uniqueidentifier = null,
    @CONSTITUENTID uniqueidentifier = null,
    @DUNSNUM nvarchar(100) = null,
    @COMPANY nvarchar(100) = null,
    @ADDRESS nvarchar(100) = null,
    @MAXROWS smallint = 500
)
as
    declare @DUNS nvarchar(100)

    select  @DUNS = right('000000000' + cast(DUNSNUM as nvarchar(9)), 9) from dbo.PARTIALINFORMATIONSEARCHRESULT_BUSINESSOWNERSHIP where ID = @PARTIALINFORMATIONSEARCHRESULTID

    select top(@MAXROWS)
        WP.ID as BUSINESSOWNERSHIPRECORDID,
        BO.DUNS,
        WP.COMPANY,
        dbo.UFN_PARTIALINFORMATIONSEARCH_BUILDADDRESS(WP.LINE1, '', WP.CITY, WP.STATE, WP.ZIP) as ADDRESS
    from 
        dbo.WPBUSINESSOWNERSHIP WP
    left join
        dbo.WPRELATIONSHIP_BO BO on BO.ID = WP.WPRELATIONSHIP_BO_ID
    where
        BO.DUNS = @DUNS and WP.WEALTHID = @CONSTITUENTID
    order by 
        WP.COMPANY asc