USP_STEWARDSHIPPLANSTEP_GETDETAILS

CREATE procedure dbo.USP_STEWARDSHIPPLANSTEP_GETDETAILS
(
    @ID uniqueidentifier,
    @CURRENTAPPUSERID uniqueidentifier,
    @DATALOADED bit = 0 output,
    @CATEGORY nvarchar(100) = null output,
    @OBJECTIVE nvarchar(100) = null output,
    @STATUS nvarchar(50) = null output,
    @STATUSCODE smallint = null output,
    @DATELOCKED bit = null output,
    @TARGETDATE datetime = null output,
    @ACTUALDATE datetime = null output,
    @STARTDATE datetime = null output,
    @ENDDATE datetime = null output,
    @CONTACTPERSON nvarchar(154) = null output,
    @CONTACTMETHOD nvarchar(100) = null output,
    @ASSIGNEDTO nvarchar(154) = null output,
    @TEMPLATE nvarchar(100) = null output,
    @ASSOCIATEDPLANS xml = null output,
    @FREQUENCY nvarchar(50) = null output,
    @RECURSCODE smallint = null output,
    @ISOWNERINDIVIDUAL bit = null output,

    @BENEFITID uniqueidentifier = null output,
    @EVENTID uniqueidentifier = null output,
    @MAILINGID uniqueidentifier = null output,

    @BENEFITNAME nvarchar(100) = null output,
    @BENEFITVALUE money = null output,

    @EVENTNAME nvarchar(100) = null output,
    @EVENTSTARTDATE datetime = null output,
    @EVENTENDDATE datetime = null output,
    @EVENTLOCATION nvarchar(100) = null output,
    @EVENTCONTACT nvarchar(154) = null output,

    @MAILINGNAME nvarchar(100) = null output,
    @MAILINGPLAN nvarchar(max) = null output,
    @MAILDATE datetime = null output,
    @MAILINGACTIVATED bit = null output,
    @MAILINGTESTS integer = null output,
    @MAILINGDESCRIPTION nvarchar(255) = null output,
    @MAILINGCODE nvarchar(10) = null output,

    @TARGETSTARTTIME dbo.UDT_HOURMINUTE = null output,
    @TARGETENDTIME dbo.UDT_HOURMINUTE = null output,
    @TIMEZONEENTRYID nvarchar(255) = null output,
    @ACTUALSTARTTIME dbo.UDT_HOURMINUTE = null output,
    @ACTUALENDTIME dbo.UDT_HOURMINUTE = null output,
    @PARTICIPANTS nvarchar(1000) = null output
)
as
begin
    set @DATALOADED = 0;

    select
        @DATALOADED = 1,
        @CATEGORY = STEWARDSHIPSTEPCATEGORYCODE.DESCRIPTION,
        @OBJECTIVE = STEP.OBJECTIVE,
        @STATUS = STEP.STATUS,
        @STATUSCODE = STEP.STATUSCODE,
        @TEMPLATE = STEP.TEMPLATE,
        @DATELOCKED = STEP.DATELOCKED,
        @TARGETDATE = STEP.TARGETDATE,
        @ACTUALDATE = STEP.ACTUALDATE,
        @STARTDATE = STEP.STARTDATE,
        @ENDDATE = STEP.ENDDATE,
        @CONTACTPERSON = NF_CONTACT.NAME,
        @CONTACTMETHOD = ITC.DESCRIPTION,
        @ASSIGNEDTO = NF_ASSIGNEDTO.NAME,
        @ASSOCIATEDPLANS = dbo.UFN_STEWARDSHIPPLANSTEP_ASSOCIATEDPLANS_TOITEMLISTXML(@ID, @CURRENTAPPUSERID),
        @FREQUENCY = STEP.RECURS,
        @RECURSCODE = STEP.RECURSCODE,
        @ISOWNERINDIVIDUAL = 
            case
                when OWNER.ISORGANIZATION = 1 or OWNER.ISGROUP = 1 then 0
                else 1
            end,

        @BENEFITID = STEP.BENEFITID,
        @EVENTID = STEP.EVENTID,
        @MAILINGID = STEP.MAILINGID,

        @BENEFITNAME = BENEFIT.NAME,
        @BENEFITVALUE = BENEFIT.VALUE,

        @EVENTNAME = EVENT.NAME,
        @EVENTSTARTDATE = EVENT.STARTDATE,
        @EVENTENDDATE = EVENT.ENDDATE,
        @EVENTLOCATION = EVENTLOCATION.NAME,
        @EVENTCONTACT = NF_EVENTCONTACT.NAME,

        @MAILINGNAME = MAILING.NAME,
        @MAILINGPLAN = dbo.UFN_MKTMARKETINGPLANITEM_PATH(MAILING.ID,1),
        @MAILDATE = MAILING.MAILDATE,
        @MAILINGACTIVATED = MAILING.ACTIVE,
        @MAILINGTESTS = (select count(*) from dbo.MKTSEGMENTATION where MKTSEGMENTATION.PARENTSEGMENTATIONID = MAILING.ID),
        @MAILINGDESCRIPTION = MAILING.DESCRIPTION,
        @MAILINGCODE = MAILING.CODE,

        @TARGETSTARTTIME = STEP.TARGETSTARTTIME,
        @TARGETENDTIME = STEP.TARGETENDTIME,
        @TIMEZONEENTRYID =     (select DISPLAYNAME from dbo.TIMEZONEENTRY where TIMEZONEENTRY.ID = STEP.TIMEZONEENTRYID),
        @ACTUALSTARTTIME = STEP.ACTUALSTARTTIME,
        @ACTUALENDTIME = STEP.ACTUALENDTIME,
        @PARTICIPANTS = (select dbo.UDA_BUILDLIST(NAME) from dbo.UFN_STEWARDSHIPPLANSTEP_PARTICIPANTS(STEP.ID))

    from dbo.STEWARDSHIPPLANSTEP STEP
    inner join
      dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEP.PLANID
    inner join
      dbo.CONSTITUENT OWNER on OWNER.ID = STEWARDSHIPPLAN.CONSTITUENTID
    left join dbo.INTERACTIONTYPECODE ITC 
        on STEP.CONTACTMETHODCODEID = ITC.ID
    left outer join dbo.STEWARDSHIPSTEPCATEGORYCODE
        on STEP.CATEGORYCODEID = STEWARDSHIPSTEPCATEGORYCODE.ID        
    left outer join dbo.BENEFIT
        on STEP.BENEFITID = BENEFIT.ID        
    left outer join dbo.EVENT
        on STEP.EVENTID = EVENT.ID    
    left outer join dbo.EVENTLOCATION
        on EVENT.EVENTLOCATIONID = EVENTLOCATION.ID        
    left outer join dbo.MKTSEGMENTATION MAILING
        on STEP.MAILINGID = MAILING.ID        
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONSTITUENTID) NF_ASSIGNEDTO
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEP.CONTACTPERSONID) NF_CONTACT
    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(EVENT.EVENTLOCATIONCONTACTID) NF_EVENTCONTACT
    where
        STEP.ID = @ID

    return 0
end