UFN_SELECTION_CONSTITUENT_COMPANYOFFICERPROSPECTRESEARCHCONSTITUENTS

Returns IDs of constituents who do not have wealth updates disabled and have a business ownership, securities, or income/compensation record with a title/relationship containing 'chair', 'chief', 'owner', 'ceo', 'c.e.o', or that starts with 'pres'.

Return

Return Type
table

Definition

Copy



            CREATE function dbo.UFN_SELECTION_CONSTITUENT_COMPANYOFFICERPROSPECTRESEARCHCONSTITUENTS()
            returns table
            as return
                select distinct
                    C.ID
                from dbo.CONSTITUENT C
                left join dbo.DISABLEDWEALTHUPDATES DWU on DWU.ID = C.ID
                left join dbo.WPBUSINESSOWNERSHIP BO on BO.WEALTHID = C.ID
                left join dbo.WPSECURITIES S on S.WEALTHID = C.ID
                left join dbo.WPINCOMECOMPENSATION IC on IC.WEALTHID = C.ID
                where
                    C.ISORGANIZATION = 0 and
                    DWU.ID is null and
                    (BO.TITLE like '%chair%' or BO.TITLE like '%chief%' or BO.TITLE like '%owner%' or BO.TITLE like '%ceo%' or BO.TITLE like '%c.e.o.%' or BO.TITLE like 'pres%' or
                    S.RELATIONSHIP like '%chair%' or S.RELATIONSHIP like '%chief%' or S.RELATIONSHIP like '%owner%' or S.RELATIONSHIP like '%ceo%' or S.RELATIONSHIP like '%c.e.o.%' or S.RELATIONSHIP like 'pres%' or
                    IC.LONGTITLE like '%chair%' or IC.LONGTITLE like '%chief%' or IC.LONGTITLE like '%owner%' or IC.LONGTITLE like '%ceo%' or IC.LONGTITLE like '%c.e.o.%' or IC.LONGTITLE like 'pres%');