USP_SIMPLEDATALIST_ORGANIZATIONCONTACTADDRESS

Retrieve a list of contact addresses based on the organization's primary address.

Parameters

Parameter	Parameter Type	Mode	Description
@ADDRESSID	uniqueidentifier	IN	Address

Definition

Copy



                CREATE procedure dbo.USP_SIMPLEDATALIST_ORGANIZATIONCONTACTADDRESS(
                    @ADDRESSID uniqueidentifier = null
                )
                as
                    set nocount on;

                    select
                        a2.ID VALUE,
                        a2.ID LABEL
                    from dbo.ADDRESS a1
                        inner join dbo.RELATIONSHIP r on a1.CONSTITUENTID=r.RELATIONSHIPCONSTITUENTID
                        inner join dbo.ADDRESS a2 on a2.RELATIONSHIPID=r.ID
                    where 
                        a1.ID=@ADDRESSID and
                        r.ISCONTACT=1 and
                        a1.COUNTRYID=a2.COUNTRYID and
                        (a1.STATEID=a2.STATEID or (a1.STATEID is null and a2.STATEID is null))  and
                        (a1.ADDRESSBLOCK=a2.ADDRESSBLOCK or (a1.ADDRESSBLOCK is null and a2.ADDRESSBLOCK is null))  and
                        (a1.CITY=a2.CITY or (a1.CITY is null and a2.CITY is null))  and
                        (a1.POSTCODE=a2.POSTCODE or (a1.POSTCODE is null and a2.POSTCODE is null))