USP_DATALIST_ORGANIZATIONPROFILEMERGEDATA

CREATE procedure dbo.USP_DATALIST_ORGANIZATIONPROFILEMERGEDATA
(
  @ORGANIZATIONCONSTITUENTID uniqueidentifier = null
)
  as

set nocount on;

declare @RELATIONSHIPTYPECODEID uniqueidentifier = null;  
declare @RECIPROCALTYPECODEID uniqueidentifier = null;
declare @IndividualConstituentID uniqueidentifier = null;

select top 1 @RELATIONSHIPTYPECODEID=PRIMARYBUSINESSRELATIONSHIPTYPECODEID, 
    @RECIPROCALTYPECODEID=PRIMARYBUSINESSRECIPROCALTYPECODEID
from dbo.NETCOMMUNITYDEFAULTCODEMAP(nolock);

if @RELATIONSHIPTYPECODEID is not null and @RECIPROCALTYPECODEID is not null
    select @IndividualConstituentID = RECIPROCALCONSTITUENTID 
    from RELATIONSHIP(nolock) 
    where RELATIONSHIPCONSTITUENTID = @ORGANIZATIONCONSTITUENTID
        and RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID
        and RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID

select  
  (case when exists (select * from REGISTRANT where CONSTITUENTID = @IndividualConstituentID) then 1 else 0 end) IsAnOrganizationRegistration
    , NF.NAME as OrganiztionName
    , CONSTITUENT.WEBADDRESS as WebSite
    , ADDRESSTYPECODE.DESCRIPTION as AddressType
    , COUNTRY.DESCRIPTION as Country
    , ADDRESS.ADDRESSBLOCK as Address
    , ADDRESS.City
    , ADDRESS.DonotMail
    , DONOTMAILREASONCODE.DESCRIPTION DonotMailReason
    , dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as State
    , ADDRESS.POSTCODE as ZIP
    , case when CONSTITUENT.ISGROUP = 0 and CONSTITUENT.ISORGANIZATION = 0 then 'Individual' when CONSTITUENT.ISORGANIZATION = 1 then 'Organization' when GD.GROUPTYPECODE=0 then 'Household' else 'Group'  end Type
    , PHONETYPECODE.DESCRIPTION as PhoneType
    , PHONE.NUMBER as PhoneNumber
    , EMAILADDRESS.EmailAddress
    , CONSTITUENT.PICTURE as Logo
    from dbo.CONSTITUENT with (nolock)
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) NF
    left outer join dbo.ADDRESS with (nolock) on CONSTITUENT.ID = ADDRESS.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
    left outer join dbo.ADDRESSTYPECODE with (nolock) on ADDRESSTYPECODE.ID = ADDRESS.ADDRESSTYPECODEID 
    left outer join dbo.DONOTMAILREASONCODE with (nolock) on DONOTMAILREASONCODE.ID = ADDRESS.DONOTMAILREASONCODEID 
    left outer join dbo.COUNTRY with (nolock) on COUNTRY.ID = ADDRESS.COUNTRYID
    left outer join PHONE with (nolock) ON PHONE.CONSTITUENTID = CONSTITUENT.ID AND PHONE.ISPRIMARY = 1
    left outer join PHONETYPECODE with (nolock) ON PHONE.PHONETYPECODEID = PHONETYPECODE.ID 
    left outer join EMAILADDRESS with (nolock) ON EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID AND EMAILADDRESS.ISPRIMARY = 1
    left outer join dbo.GROUPDATA GD with (nolock) on CONSTITUENT.ID = GD.ID
    where CONSTITUENT.ISCONSTITUENT = 1 
         and not exists (select top 1 ID from dbo.DECEASEDCONSTITUENT with (nolock) where DECEASEDCONSTITUENT.ID = CONSTITUENT.ID)
         and (CONSTITUENT.ISORGANIZATION = 1 or CONSTITUENT.ISGROUP = 1)
         and CONSTITUENT.ISGROUP = 0
         and CONSTITUENT.ISINACTIVE = 0
         and CONSTITUENT.ID = @ORGANIZATIONCONSTITUENTID