USP_DATALIST_PDACCOUNTSEGMENTPURPOSEMAP

Returns a list of account segment values mapped to fundraising purpose.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DESIGNATIONLEVELTYPEID	uniqueidentifier	IN	Type
@DESIGNATIONLEVELCATEGORYCODEID	uniqueidentifier	IN	Category
@SITEID	uniqueidentifier	IN	Site
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SEGMENTNAMEORVALUE	nvarchar(200)	IN

Definition

Copy



CREATE procedure dbo.USP_DATALIST_PDACCOUNTSEGMENTPURPOSEMAP(
    @ID uniqueidentifier,
    @DESIGNATIONLEVELTYPEID uniqueidentifier = null,
    @DESIGNATIONLEVELCATEGORYCODEID uniqueidentifier = null,
    @SITEID uniqueidentifier = null,
    @CURRENTAPPUSERID uniqueidentifier = null,
  @SEGMENTNAMEORVALUE nvarchar(200) = null
    )
    as

      set nocount on;
    --sanitize the search values, adding mask chars at beginning and end so it performs a 'contains' style match
      set @SEGMENTNAMEORVALUE = dbo.UFN_TEXTSEARCH_SANITIZE(@SEGMENTNAMEORVALUE,'/',DEFAULT,DEFAULT,DEFAULT) ;

        declare @PURPOSE table(ID uniqueidentifier)
        insert into @PURPOSE
        select ID from dbo.DESIGNATIONLEVEL T2
        where
            (
                (@DESIGNATIONLEVELTYPEID is null)
                or
                (T2.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID)
            )
        and
            (
                (@DESIGNATIONLEVELCATEGORYCODEID is null)
                or
                (T2.DESIGNATIONLEVELCATEGORYCODEID = @DESIGNATIONLEVELCATEGORYCODEID)
            )
        and
            (
                (@SITEID is null)
                or
                (T2.SITEID = @SITEID)
            )

    select * from (
        select
            T1.ID,
            LONGDESCRIPTION,
            T1.ID as LONGDESCRIPTIONID,
            PDACCOUNTSEGMENTVALUEID,
            T3.SHORTDESCRIPTION,
            T1.PDACCOUNTSTRUCTUREID,
            ISDEFAULT
        from dbo.PDACCOUNTSEGMENTMAPPING T1
            left join dbo.PDACCOUNTSEGMENTVALUE T3 on T1.PDACCOUNTSEGMENTVALUEID = T3.ID
        where T1.PDACCOUNTSTRUCTUREID = @ID
            and T1.ISDEFAULT = 1

        union all

        select
            T1.ID,
            LONGDESCRIPTION,
            LONGDESCRIPTIONID,
            PDACCOUNTSEGMENTVALUEID,
            T3.SHORTDESCRIPTION,
            T1.PDACCOUNTSTRUCTUREID,
            ISDEFAULT
        from dbo.PDACCOUNTSEGMENTMAPPING T1
            left join dbo.PDACCOUNTSEGMENTVALUE T3 on T1.PDACCOUNTSEGMENTVALUEID = T3.ID
            inner join @PURPOSE T2 on nullif(T1.LONGDESCRIPTIONID,'') = T2.ID
        where T1.PDACCOUNTSTRUCTUREID = @ID

        union all

        select
            NEWID(),
            V1.LONGDESCRIPTION,
            V1.LONGDESCRIPTIONID,
            null,
            null,
            v1.PDACCOUNTSTRUCTUREID,
            0
        from
            dbo.PDACCOUNTSEGMENTMAPPINGVIEW V1
            inner join @PURPOSE T2 on V1.LONGDESCRIPTIONID = T2.ID
        where 
            V1.PDACCOUNTSTRUCTUREID = @ID
            and
            V1.LONGDESCRIPTIONID not in (select LONGDESCRIPTIONID from PDACCOUNTSEGMENTMAPPING where PDACCOUNTSTRUCTUREID = @ID)) ALLITEMS
    where
      (ALLITEMS.LONGDESCRIPTION like @SEGMENTNAMEORVALUE escape '/' or ALLITEMS.SHORTDESCRIPTION like @SEGMENTNAMEORVALUE escape '/')

        order by LONGDESCRIPTION