USP_DATAFORMTEMPLATE_VIEW_ACTION_ITEM

The load procedure used by the view dataform template "Action Item 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.
@NAME	nvarchar(250)	INOUT	Name
@SYNOPSIS	nvarchar(4000)	INOUT	Synopsis
@DESCRIPTION	nvarchar(max)	INOUT	Description
@TYPECODE	tinyint	INOUT	TypeCode
@STATUSCODE	tinyint	INOUT	StatusCode
@TOPICCODE	tinyint	INOUT	TopicCode
@STARTDATE	datetime	INOUT	Startdate
@ENDDATE	datetime	INOUT	Enddate
@ISEMAILDELIVERY	bit	INOUT	Isemaildelivery
@ISFAXDELIVERY	bit	INOUT	Isfaxdelivery
@ISLETTERDELIVERY	bit	INOUT	Isletterdelivery
@ISDELIVERYNONE	bit	INOUT	Isdeliverynone
@ISFAXBACKUPFOREMAILSENABLED	bit	INOUT	Isfaxbackupforemailsenabled
@ISEMAILBACKUPFORFAXENABLED	bit	INOUT	Isemailbackupforfaxenabled
@TARGETOPTIONCODE	tinyint	INOUT	Targetoption
@TARGETDATABASE	nvarchar(100)	INOUT	Targetdatabase
@CONFIRMATIONTEXT	nvarchar(max)	INOUT	Confirmationtext
@RESULTTEXT	nvarchar(max)	INOUT	Resulttext
@TYPE	nvarchar(16)	INOUT	Type
@STATUS	nvarchar(16)	INOUT	STATUS
@TOPIC	nvarchar(30)	INOUT	TOPIC
@FOLDERID	int	INOUT	Folderid
@SUBJECT	nvarchar(250)	INOUT	Subject
@MESSAGETEXT	nvarchar(max)	INOUT	Messagetext
@INSTRUCTIONS	nvarchar(1000)	INOUT	Instructions
@THANKYOUMESSAGETEXT	nvarchar(max)	INOUT	Thankyoumessagetext
@SENDCOPYTONPO	bit	INOUT	Sendcopytonpo
@OVERWRITECODE	tinyint	INOUT	Overwritetype
@OVERWRITE	varchar(50)	INOUT	Overwrite
@ISTOPOSTALCODEDEPENDENT	bit	INOUT	IsToPostalcodeDependent
@ISCCPOSTALCODEDEPENDENT	bit	INOUT	IsCCPostalcodeDependent
@PERSONALIZATIONCODE	tinyint	INOUT	Persoanlizationtype
@PERSONALIZATION	varchar(50)	INOUT	Personalization
@SCHEDULEACTIVATION	bit	INOUT	ScheduleActivation
@ISTODMADEPENDENT	bit	INOUT	IsToDMADependent
@ISCCDMADEPENDENT	bit	INOUT	IsCCDMADependent
@EMAILTEMPLATEID	int	INOUT	EmailTemplateID
@NEWSCHANNELID	int	INOUT	NewsChannelID
@CONTENTID	int	INOUT	ContentID
@ACTIONDATA	xml	INOUT	ActionData

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ACTION_ITEM 
(
    @ID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @NAME nvarchar(250) = null output,
    @SYNOPSIS nvarchar(4000) = null output,
    @DESCRIPTION nvarchar(max) = null output,
    @TYPECODE tinyint = null output,
    @STATUSCODE tinyint = null output,
    @TOPICCODE tinyint = null output,
    @STARTDATE datetime = null output,
    @ENDDATE datetime = null output,
    @ISEMAILDELIVERY bit = null output,
    @ISFAXDELIVERY bit = null output,
    @ISLETTERDELIVERY bit = null output,
    @ISDELIVERYNONE bit = null output,
    @ISFAXBACKUPFOREMAILSENABLED bit = null output,
    @ISEMAILBACKUPFORFAXENABLED bit = null output,
    @TARGETOPTIONCODE tinyint = null output,
    @TARGETDATABASE nvarchar(100) = null output,
    @CONFIRMATIONTEXT nvarchar(max) = null output,
    @RESULTTEXT nvarchar(max) = null output,
    @TYPE nvarchar(16) = null output,
    @STATUS nvarchar(16) = null output,
    @TOPIC nvarchar(30) = null output,
    @FOLDERID int = null output,
    @SUBJECT nvarchar(250) = null output,
    @MESSAGETEXT nvarchar(max) = null output,
    @INSTRUCTIONS nvarchar(1000) = null output,
    @THANKYOUMESSAGETEXT nvarchar(max) = null output,
    @SENDCOPYTONPO bit = null output,
    @OVERWRITECODE tinyint = null output,
    @OVERWRITE varchar(50) = null output,
    @ISTOPOSTALCODEDEPENDENT bit = null output,
    @ISCCPOSTALCODEDEPENDENT bit = null output,
--    @THANKYOUEMAILSUBJECT nvarchar(250) =  null output,

--    @THANKYOUEMAILTEXT nvarchar(max) =  null output,

    @PERSONALIZATIONCODE tinyint = null output,
    @PERSONALIZATION varchar(50) = null output,
    @SCHEDULEACTIVATION bit = null output,
    @ISTODMADEPENDENT bit = null output,
    @ISCCDMADEPENDENT bit = null output,
  @EMAILTEMPLATEID int = null output,
  @NEWSCHANNELID int = null output,
  @CONTENTID int = null output,
  @ACTIONDATA xml = null output 
)
as
    set nocount on;

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

    set @DATALOADED = 0;

    -- Used to get action center part xml data

      DECLARE @Property nvarchar(400)
      SET @Property = 'BBNCCUSTOM'
      declare @XMLData table([SiteContentID] int,[data] xml)
      insert into @XMLData
      select SiteContent.ID,  CAST(XMLData as nvarchar(max))
      FROM dbo.ActionCenter 
     JOIN  SiteContent on ActionCenter.SiteContentID = SiteContent.ID 
        WHERE ActionCenter.ActionItemID = @ID

    -- 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,
           @NAME = AI.NAME, 
           @SYNOPSIS = AI.SYNOPSIS, 
           @DESCRIPTION = AI.DESCRIPTION, 
           @TYPECODE = AI.TYPECODE, 
           @STATUSCODE = AI.STATUSCODE, 
           @TOPICCODE = AI.TOPICCODE, 
           @STARTDATE = AI.STARTDATE, 
           @ENDDATE = AI.ENDDATE, 
           @ISEMAILDELIVERY = AI.ISEMAILDELIVERY, 
           @ISFAXDELIVERY = AI.ISFAXDELIVERY, 
           @ISLETTERDELIVERY = AI.ISLETTERDELIVERY, 
           @ISDELIVERYNONE = AI.ISDELIVERYNONE, 
           @ISFAXBACKUPFOREMAILSENABLED = AI.ISFAXBACKUPFOREMAILSENABLED, 
           @ISEMAILBACKUPFORFAXENABLED = AI.ISEMAILBACKUPFORFAXENABLED, 
           @TARGETOPTIONCODE = AI.TARGETOPTIONCODE, 
           @TARGETDATABASE = AI.TARGETDATABASE, 
           @CONFIRMATIONTEXT = AI.CONFIRMATIONTEXT, 
           @RESULTTEXT = AI.RESULTTEXT,
           @TYPE = AI.TYPE,
           @STATUS = AI.STATUS,
           @TOPIC = AI.TOPIC,
           @FOLDERID = AF.FOLDERID,
           @SUBJECT = MT.SUBJECT, 
           @MESSAGETEXT = MT.MESSAGETEXT, 
           @INSTRUCTIONS = MT.INSTRUCTIONS, 
           @THANKYOUMESSAGETEXT = MT.THANKYOUMESSAGETEXT, 
           @SENDCOPYTONPO = MT.SENDCOPYTONPO,
           @ISTOPOSTALCODEDEPENDENT = AI.ISTOPOSTALCODEDEPENDENT,
           @ISCCPOSTALCODEDEPENDENT = AI.ISCCPOSTALCODEDEPENDENT,
        --   @THANKYOUEMAILSUBJECT = MT.THANKYOUEMAILSUBJECT,

        --   @THANKYOUEMAILTEXT = MT.THANKYOUEMAILTEXT,

           @OVERWRITECODE = MT.OVERWRITECODE,
           @PERSONALIZATIONCODE = MT.PERSONALIZATIONCODE,
           @PERSONALIZATION = MT.PERSONALIZATION,
           @OVERWRITE = MT.OVERWRITE,
           @SCHEDULEACTIVATION = AI.SCHEDULEACTIVATION,
           @ISTODMADEPENDENT = AI.ISTODMADEPENDENT,
           @ISCCDMADEPENDENT = AI.ISCCDMADEPENDENT,
       @EMAILTEMPLATEID = MT.EMAILTEMPLATEID,
       @NEWSCHANNELID = ( SELECT TOP 1 NewsChannels.ID as NewsChannel
                FROM NewsChannels 
                JOIN ACTIONCENTER 
                    ON NewsChannels.SiteContentID = ACTIONCENTER.SITECONTENTID 
                where ACTIONCENTER.ACTIONITEMID = @ID ),
       @CONTENTID = ( SELECT TOP 1 SITECONTENTID FROM ACTIONCENTER 
                      WHERE ACTIONITEMID = @ID ),
       @ACTIONDATA = (     SELECT top 1
            CAST(data.query(  '/XMLHashTable/ItemArray/Item[@Name=sql:variable("@Property")]/Value/text()') as nvarchar(max))
          from @XMLData )
    from dbo.ACTION_ITEM AI
    LEFT OUTER JOIN dbo.ACTIONITEMFOLDER AF
    ON AI.ID = AF.ACTIONITEMID
    LEFT OUTER JOIN dbo.ACTIONITEMMESSAGETEXT MT
    ON MT.ACTIONITEMID = AI.ID
    where AI.ID = @ID

    return 0;