USP_DATALIST_PDACCOUNTSEGMENTDESIGNATIONMAP

Returns a list of account segment values mapped to designations.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DESIGNATIONREPORT1CODEID	uniqueidentifier	IN	Report code 1
@DESIGNATIONREPORT2CODEID	uniqueidentifier	IN	Report code 2
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SEGMENTNAMEORVALUE	nvarchar(200)	IN

Definition

Copy



CREATE procedure dbo.USP_DATALIST_PDACCOUNTSEGMENTDESIGNATIONMAP(
    @ID uniqueidentifier,
    @DESIGNATIONREPORT1CODEID uniqueidentifier = null,
    @DESIGNATIONREPORT2CODEID 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) ;

select ID, LONGDESCRIPTION, LONGDESCRIPTIONID, PDACCOUNTSEGMENTVALUEID, SHORTDESCRIPTION, PDACCOUNTSTRUCTUREID, ISDEFAULT, DESIGNATIONREPORT1CODE, DESIGNATIONREPORT2CODE from (select
    SM.ID,
    LONGDESCRIPTION,
    SM.ID as LONGDESCRIPTIONID,
    PDACCOUNTSEGMENTVALUEID,
    SV.SHORTDESCRIPTION,
    SM.PDACCOUNTSTRUCTUREID,
    ISDEFAULT,
    NULL AS DESIGNATIONREPORT1CODE,
    NULL AS DESIGNATIONREPORT2CODE
from dbo.PDACCOUNTSEGMENTMAPPING SM
    left join dbo.PDACCOUNTSEGMENTVALUE SV on SM.PDACCOUNTSEGMENTVALUEID = SV.ID
where SM.PDACCOUNTSTRUCTUREID = @ID
    and SM.ISDEFAULT = 1
union all
select 
COALESCE(SM.ID,NEWID()) as ID
, D.NAME as LONGDESCRIPTION
, D.ID as LONGDESCRIPTIONID
, SV.ID as PDACCOUNTSEGMENTVALUEID
, SV.SHORTDESCRIPTION
, @ID AS PDACCOUNTSTRUCTUREID
, COALESCE(SM.ISDEFAULT,0) as ISDEFAULT 
, RC1.DESCRIPTION as DESIGNATIONREPORT1CODE
, RC2.DESCRIPTION as DESIGNATIONREPORT2CODE
from dbo.UFN_DESIGNATION_BUILDNAME_BULK() D 
left join dbo.PDACCOUNTSEGMENTMAPPING SM on D.ID=SM.LONGDESCRIPTIONID and SM.PDACCOUNTSTRUCTUREID = @ID 
left join dbo.PDACCOUNTSEGMENTVALUE SV ON SM.PDACCOUNTSEGMENTVALUEID = SV.ID
left join dbo.DESIGNATIONREPORT1CODE RC1 on D.DESIGNATIONREPORT1CODEID=RC1.ID  
left join dbo.DESIGNATIONREPORT2CODE RC2 on D.DESIGNATIONREPORT2CODEID=RC2.ID  
where 
(@DESIGNATIONREPORT1CODEID is null or RC1.ID=@DESIGNATIONREPORT1CODEID)
and (@DESIGNATIONREPORT2CODEID is null or RC2.ID=@DESIGNATIONREPORT2CODEID)
) T
where
  (T.LONGDESCRIPTION like @SEGMENTNAMEORVALUE escape '/' or T.SHORTDESCRIPTION like @SEGMENTNAMEORVALUE escape '/')
order by T.ISDEFAULT DESC, T.LONGDESCRIPTION