UFN_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_UNACKNOWLEDGEDREVENUEEXISTS

Returns whether or not a marketing acknowledgement template has any unacknowledged revenue.

Return

Return Type
bit

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.[UFN_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_UNACKNOWLEDGEDREVENUEEXISTS]
(
  @ID uniqueidentifier
)
returns bit
as begin
  declare @EXISTS bit;

  if exists(select top 1 1
            from dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS]
            inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[PARAMETERSETID]
            inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION] on [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID]
            where [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ID
            and [MKTACKNOWLEDGEMENTMAILINGPROCESSSEGMENTATION].[ACKNOWLEDGEDATE] is null) or
     exists(select top 1 1
            from dbo.[V_MKTREVENUELETTER]
            inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS] on [V_MKTREVENUELETTER].[MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUSID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[ID]
            inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID] = [MKTACKNOWLEDGEMENTMAILINGPROCESSSTATUS].[PARAMETERSETID]
            where [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ID
            and [V_MKTREVENUELETTER].[ACKNOWLEDGEDATE] is null)
    set @EXISTS = 1;
  else
    set @EXISTS = 0;

  return @EXISTS;
end