USP_DATALIST_PDACCOUNTSEGMENTAPPEALMAP

Returns a list of account segment values mapped to appeals.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@APPEALCATEGORYCODEID	uniqueidentifier	IN	Category
@BUSINESSUNITCODEID	uniqueidentifier	IN	Business unit
@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_PDACCOUNTSEGMENTAPPEALMAP](
    @ID uniqueidentifier,
    @APPEALCATEGORYCODEID uniqueidentifier = null,
    @BUSINESSUNITCODEID 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 @APPEALS table (id uniqueidentifier primary key, name nvarchar(100))
        insert into @APPEALS
        select ID, NAME from dbo.APPEAL T2
        where
            (
                (@APPEALCATEGORYCODEID is null)
                or
                (T2.APPEALCATEGORYCODEID = @APPEALCATEGORYCODEID)
            )
        and
            (
                (@BUSINESSUNITCODEID is null)
                or
                (T2.BUSINESSUNITCODEID = @BUSINESSUNITCODEID)
        or 
        exists(select ID from dbo.APPEALBUSINESSUNIT where APPEALBUSINESSUNIT.BUSINESSUNITCODEID=@BUSINESSUNITCODEID and APPEALBUSINESSUNIT.APPEALID=T2.ID)
            )
        and
            (
                (@SITEID is null)
                or
                (T2.SITEID = @SITEID)
            )


    select * from (
        select
            T1.ID,
            '<Not used>' as 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,
            T2.NAME,
            isnull(nullif(LONGDESCRIPTIONID,''),T1.ID ),
            PDACCOUNTSEGMENTVALUEID,
            T3.SHORTDESCRIPTION,
            T1.PDACCOUNTSTRUCTUREID,
            ISDEFAULT
        from dbo.PDACCOUNTSEGMENTMAPPING T1
            left join dbo.PDACCOUNTSEGMENTVALUE T3 on T1.PDACCOUNTSEGMENTVALUEID = T3.ID
            inner join @APPEALS T2 on 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 @APPEALS 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
        option (recompile)