USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPMARKETING

The load procedure used by the view dataform template "Sponsorship Marketing Information View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@FINDERNUMBER	bigint	INOUT	Finder number
@SOURCECODE	nvarchar(20)	INOUT	Source code
@APPEAL	nvarchar(510)	INOUT	Appeal
@MAILING	nvarchar(200)	INOUT	Effort
@INBOUNDCHANNEL	nvarchar(200)	INOUT	Inbound channel
@REVENUEREFERENCE	nvarchar(510)	INOUT	Reference
@REVCATEGORY	nvarchar(200)	INOUT	Revenue category

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_SPONSORSHIPMARKETING]
(
  @ID uniqueidentifier, --sponsorship id

  @DATALOADED bit = 0 output,
  @FINDERNUMBER bigint = null output,
  @SOURCECODE nvarchar(20) = null output,
  @APPEAL nvarchar(510) = null output,
  @MAILING nvarchar(200) = null output,
  @INBOUNDCHANNEL nvarchar(200) = null output,
  @REVENUEREFERENCE nvarchar(510) = null output,
  @REVCATEGORY nvarchar(200) = null output
)
as
  set nocount on;

  -- be sure to set this, in case the select returns no rows

  set @DATALOADED = 0;

  -- populate the output parameters, which correspond to fields on the form.  Note that

  -- we set @DATALOADED = 1 to indicate that the load was successful.  Otherwise, the system

  -- will display a "no data loaded" message.

  select
    @DATALOADED = 1,
    @FINDERNUMBER = nullif([REVENUE_EXT].[FINDERNUMBER], 0),
    @SOURCECODE = [REVENUE_EXT].[SOURCECODE],
    @APPEAL = (select [A].[DESCRIPTION] from dbo.[APPEAL] [A] where [A].[ID] = [REVENUE_EXT].[APPEALID]),
    @MAILING = (select [MS].[NAME] from dbo.[MKTSEGMENTATION] [MS] where [MS].[ID] = [REVENUE_EXT].[MAILINGID]),
    @INBOUNDCHANNEL = [CC].[DESCRIPTION],
    @REVENUEREFERENCE = [RR].[REFERENCE],
    @REVCATEGORY = [G].[REVENUECATEGORYNAME]
  from
    dbo.[FINANCIALTRANSACTION]
    inner join dbo.[REVENUE_EXT] on [REVENUE_EXT].[ID] = [FINANCIALTRANSACTION].[ID]
    inner join dbo.[FINANCIALTRANSACTIONLINEITEM] on [FINANCIALTRANSACTION].[ID] = [FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONID]
    inner join dbo.[SPONSORSHIP] [S] on [S].[REVENUESPLITID] = [FINANCIALTRANSACTIONLINEITEM].[ID]
    left join dbo.[CHANNELCODE] [CC] on [CC].[ID] = [REVENUE_EXT].[CHANNELCODEID]
    left join dbo.[REVENUEREFERENCE] [RR] on [RR].[ID] = [FINANCIALTRANSACTION].[ID]
    left join dbo.[REVENUECATEGORY] [RC] on [FINANCIALTRANSACTIONLINEITEM].[ID] = [RC].[ID]
    left join dbo.[GLREVENUECATEGORYMAPPING] [G] on [G].[ID] = [RC].[GLREVENUECATEGORYMAPPINGID]
  where
    [S].[ID] = @ID
    and [FINANCIALTRANSACTION].[TYPECODE] in (0,1,2,3,4,5,6,7,8,9)
    and [FINANCIALTRANSACTION].[DELETEDON] is null
    and [FINANCIALTRANSACTIONLINEITEM].[DELETEDON] is null;

  return 0;