USP_RECOGNITIONPROGRAM_CREATEQUERY

            CREATE procedure dbo.USP_RECOGNITIONPROGRAM_CREATEQUERY(
                @RECOGNITIONPROGRAMID uniqueidentifier,
                @PROGRAMNAME nvarchar(100),
                @CHANGEAGENTID uniqueidentifier
            )
            as
            begin
                declare @QUERYSPEC xml;

                begin try
                    select @QUERYSPEC = QUERYVIEWSPEC from dbo.QUERYVIEWCATALOG 
                    where ID = '1F975D0A-DD74-4A4B-88F0-8A515679E845';

                    --give new query spec a new id

                    declare @NEWID uniqueidentifier;
                    set @NEWID = NEWID();

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/@ID)[1] with sql:variable("@NEWID")
                        ');

                    --replace the name of the query view

                    declare @QUERYNAME nvarchar(150);
                    set @QUERYNAME = @PROGRAMNAME + ' Recognitions';

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/@Name)[1] with sql:variable("@QUERYNAME")
                        ');

                    --replace the description

                    declare @DESCRIPTION nvarchar(max);
                    set @DESCRIPTION = 'This provides the ability to query for recognitions in ' + @PROGRAMNAME + '.';

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/@Description)[1] with sql:variable("@DESCRIPTION")
                        ');

                    --replace view name

                    declare @VIEWNAME nvarchar(100);
                    set @VIEWNAME = 'V_QUERY_RECOGNITION_' + REPLACE(convert(nvarchar(36),@RECOGNITIONPROGRAMID),'-','');

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/ViewImplementation/@ViewName)[1] with sql:variable("@VIEWNAME")
                        ');

                    --add related query views

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                                insert <AddToParentView
                                    ParentView="V_QUERY_CONSTITUENTMARKETING"
                                    ParentViewRelatedField="CONSTITUENTID"
                                    Field="CONSTITUENTID"
                                    PathAlias="@QUERYNAME"
                                    Cardinality="OneToOptionalOne"/> 
                                    into (/QueryViewSpec/RelationshipOperations)[1]')

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                                insert <AddToParentView
                                    ParentView="V_QUERY_CONSTITUENT"
                                    ParentViewRelatedField="ID"
                                    Field="CONSTITUENTID"
                                    PathAlias="@QUERYNAME"
                                    Cardinality="OneToOptionalOne"/> 
                                    into (/QueryViewSpec/RelationshipOperations)[1]')

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/RelationshipOperations/AddToParentView[1]/@PathAlias)[1] with sql:variable("@QUERYNAME")
                        ');

                    set @QUERYSPEC.modify('declare default element namespace "bb_appfx_queryview";
                        replace value of (/QueryViewSpec/RelationshipOperations/AddToParentView[2]/@PathAlias)[1] with sql:variable("@QUERYNAME")
                        ');

                    set @QUERYSPEC = REPLACE(convert(nvarchar(max), @QUERYSPEC), '/*WHERECLAUSE*/', ' where CR.RECOGNITIONPROGRAMID = ''' + convert(nvarchar(36), @RECOGNITIONPROGRAMID) + '''')
                    set @QUERYSPEC = REPLACE(convert(nvarchar(max), @QUERYSPEC), 'null/*RECOGNITIONPROGRAMID*/', '''' + convert(nvarchar(36), @RECOGNITIONPROGRAMID) + '''')


                    --load it

                    exec dbo.USP_LOADSPEC @QUERYSPEC, @CHANGEAGENTID    

                    declare @RECOGNITIONQUERYSYSTEMPRIVILEGE uniqueidentifier = 'F981F50A-1F23-4212-8A4E-578B0F3565D5';

                    exec dbo.USP_SYSTEMROLE_COPYSYSTEMPRIVILEGEPERMISSIONSTOQUERYVIEW @NEWID, @RECOGNITIONQUERYSYSTEMPRIVILEGE, @CHANGEAGENTID

                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch
            end