USP_DATALIST_CAMPAIGNPRIORITIESANDSUBPRIORITIES

This datalist returns all of the priorities and subpriorities for a campaign.
Parameters

Parameter	Parameter Type	Mode	Description
@CAMPAIGNID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_CAMPAIGNPRIORITIESANDSUBPRIORITIES (
                    @CAMPAIGNID uniqueidentifier
                ) as begin

                    declare @BASECURRENCYID uniqueidentifier;

                    select 
                        @BASECURRENCYID = CAMPAIGN.BASECURRENCYID 
                    from
                        dbo.CAMPAIGN
                    where 
                        CAMPAIGN.ID = @CAMPAIGNID;

                    with CAMPAIGNPRIORITIES_CTE as (
                        select
                            CAMPAIGNPRIORITY.ID,
                            CAMPAIGNPRIORITY.CAMPAIGNID
                        from
                            dbo.CAMPAIGNPRIORITY
                        where
                            CAMPAIGNPRIORITY.CAMPAIGNID = @CAMPAIGNID
                    )
                    select
                        CAMPAIGNPRIORITY.ID,
                        null as PARENTID,
                        CAMPAIGNPRIORITYTYPECODE.DESCRIPTION as NAME,
                        CAMPAIGNPRIORITY.GOAL,
                        @BASECURRENCYID [BASECURRENCYID],
                        (
                          select top 1 
                              KPIINSTANCE.ID
                          from 
                              dbo.KPIINSTANCE 
                          where
                              KPIINSTANCE.KPICATALOGID = '578EC1B0-4A3E-4598-8C44-99CE532D2D2C'
                          and
                              KPIINSTANCE.CONTEXTRECORDID = convert(nvarchar(36), CAMPAIGNPRIORITY.ID)
                        ) as KPIINSTANCEID,
                        CAMPAIGNPRIORITY.ID as KPICONTEXTID,
                        0 as ISSUBPRIORITY
                    from
                        dbo.CAMPAIGNPRIORITY
                    inner join
                        CAMPAIGNPRIORITIES_CTE on CAMPAIGNPRIORITY.ID = CAMPAIGNPRIORITIES_CTE.ID
                    left outer join
                        CAMPAIGNPRIORITYTYPECODE on CAMPAIGNPRIORITY.CAMPAIGNPRIORITYTYPECODEID = CAMPAIGNPRIORITYTYPECODE.ID

                    union

                    select
                        CAMPAIGNSUBPRIORITY.ID,
                        CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID as PARENTID,
                        CAMPAIGNSUBPRIORITYNAMECODE.DESCRIPTION as NAME,
                        CAMPAIGNSUBPRIORITY.GOAL,
                        @BASECURRENCYID [BASECURRENCYID],
                        (
                          select top 1 
                              KPIINSTANCE.ID
                          from 
                              dbo.KPIINSTANCE 
                          where
                              KPIINSTANCE.KPICATALOGID = '16C1A10F-6FF6-451C-B5B3-8FD8F50B7115'
                          and
                              KPIINSTANCE.CONTEXTRECORDID = convert(nvarchar(36), CAMPAIGNSUBPRIORITY.ID)
                        ) as KPIINSTANCEID,
                        CAMPAIGNSUBPRIORITY.ID as KPICONTEXTID,
                        1 as ISSUBPRIORITY
                    from
                        dbo.CAMPAIGNSUBPRIORITY
                    inner join
                        CAMPAIGNPRIORITIES_CTE on CAMPAIGNSUBPRIORITY.CAMPAIGNPRIORITYID = CAMPAIGNPRIORITIES_CTE.ID
                    inner join 
                        dbo.CAMPAIGNSUBPRIORITYNAMECODE on CAMPAIGNSUBPRIORITYNAMECODE.ID = CAMPAIGNSUBPRIORITY.CAMPAIGNSUBPRIORITYNAMECODEID
                    order by NAME;

                end