USP_SIMPLEDATALIST_LETTERCODE_ALL_REVENUELETTER

Returns a list of all revenue letters.

Parameters

Parameter	Parameter Type	Mode	Description
@INCLUDEINACTIVE	bit	IN	Include inactive
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SECURITYFEATUREID	uniqueidentifier	IN
@SECURITYFEATURETYPE	tinyint	IN
@REVENUELETTERID	uniqueidentifier	IN	Revenue letter ID
@REQUIRESELECTIONOREXPORTDEFINITION	bit	IN	Require selection or export definition

Definition

Copy



CREATE procedure dbo.[USP_SIMPLEDATALIST_LETTERCODE_ALL_REVENUELETTER]
(
  @INCLUDEINACTIVE bit = 0,
  @CURRENTAPPUSERID uniqueidentifier,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @REVENUELETTERID uniqueidentifier,
  @REQUIRESELECTIONOREXPORTDEFINITION bit = 1
)
as
  set nocount on;

  select 
    [LETTERCODE].[ID] as [VALUE],
    [LETTERCODE].[NAME] as [LABEL]
  from dbo.[LETTERCODE]
  left outer join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [LETTERCODE].[EXPORTDEFINITIONID]
  left outer join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [EXPORTDEFINITION].[RECORDTYPEID]
  left outer join dbo.[LETTERCODESITE] on [LETTERCODE].[ID] = [LETTERCODESITE].[LETTERCODEID]
  where (@INCLUDEINACTIVE = 1 or [LETTERCODE].[ISACTIVE] = 1)
  and
  (
    (
      @REQUIRESELECTIONOREXPORTDEFINITION = 1
      and
      (
        [LETTERCODE].[IDSETREGISTERID] is not null
        or
        ([LETTERCODE].[EXPORTDEFINITIONID] is not null and dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPE]([RECORDTYPE].[NAME]) in (1, 5))
      )
    )
    or
    (
      @REQUIRESELECTIONOREXPORTDEFINITION = 0
      and
      (
        ([LETTERCODE].[EXPORTDEFINITIONID] is null)
        or
        ([LETTERCODE].[EXPORTDEFINITIONID] is not null and dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPE]([RECORDTYPE].[NAME]) in (1, 5))
      )
    )
  )
  and [LETTERCODE].[ISSYSTEM] = 0
  and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[LETTERCODESITE].[SITEID] or (SITEID is null and [LETTERCODESITE].[SITEID] is null)))

  union

  select 
    [LETTERCODE].[ID] as [VALUE],
    [LETTERCODE].[NAME] as [LABEL]
  from dbo.[LETTERCODE]
  inner join dbo.[REVENUELETTER] on [LETTERCODE].[ID] = [REVENUELETTER].[LETTERCODEID]
  left outer join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [LETTERCODE].[EXPORTDEFINITIONID]
  left outer join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [EXPORTDEFINITION].[RECORDTYPEID]
  where (@INCLUDEINACTIVE = 1 or [LETTERCODE].[ISACTIVE] = 1)
  and
  (
    (
      @REQUIRESELECTIONOREXPORTDEFINITION = 1
      and
      (
        [LETTERCODE].[IDSETREGISTERID] is not null
        or
        ([LETTERCODE].[EXPORTDEFINITIONID] is not null and dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPE]([RECORDTYPE].[NAME]) in (1, 5))
      )
    )
    or
    (
      @REQUIRESELECTIONOREXPORTDEFINITION = 0
      and
      (
        ([LETTERCODE].[EXPORTDEFINITIONID] is null)
        or
        ([LETTERCODE].[EXPORTDEFINITIONID] is not null and dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPE]([RECORDTYPE].[NAME]) in (1, 5))
      )
    )
  )
  and [LETTERCODE].[ISSYSTEM] = 0
  and [REVENUELETTER].[ID] = @REVENUELETTERID

  order by [LABEL];

  return 0;