USP_DATAFORMTEMPLATE_EDIT_PROSPECTSEGMENTATION_LOAD

The load procedure used by the edit dataform template "Prospect Segmentation Edit Form"
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@OTHERSCANMODIFY	bit	INOUT	Others can modify
@RESEARCHGROUPID	uniqueidentifier	INOUT	Research group
@IDSETREGISTERID	uniqueidentifier	INOUT	Selection
@RECORDTYPEID	uniqueidentifier	INOUT
@PRIMARYVARIABLE	tinyint	INOUT	Select score/rating
@SECONDARYVARIABLE1	tinyint	INOUT	Select score/rating
@SECONDARYVARIABLE2	tinyint	INOUT	Secondary variable 2
@PRIMARYSECTIONS	xml	INOUT
@SECONDARYSECTIONS1	xml	INOUT
@SECONDARYSECTIONS2	xml	INOUT
Definition

Copy

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_PROSPECTSEGMENTATION_LOAD (
                        @ID uniqueidentifier,
                        @CURRENTAPPUSERID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @TSLONG bigint = 0 output,
                        @NAME nvarchar(100) = null output,
                        @DESCRIPTION nvarchar(255) = null output,
                        @OTHERSCANMODIFY bit = null output,
                        @RESEARCHGROUPID uniqueidentifier = null output,
                    @IDSETREGISTERID uniqueidentifier = null output,
                        @RECORDTYPEID uniqueidentifier = null output,
                        @PRIMARYVARIABLE tinyint = null output,
                        @SECONDARYVARIABLE1 tinyint = null output,
                        @SECONDARYVARIABLE2 tinyint = null output,
                        @PRIMARYSECTIONS xml = null output,
                        @SECONDARYSECTIONS1 xml = null output,
                        @SECONDARYSECTIONS2 xml = null output    

                    ) as
                        set nocount on;

                        set @DATALOADED = 0;

                        select
                            @RECORDTYPEID = ID
                        from
                            dbo.RECORDTYPE
                        where
                            upper(NAME) = 'CONSTITUENT';

            declare @PRIMARYSECTIONSXML xml;
            declare @SECONDARYSECTIONSXML xml;

                        select
                            @DATALOADED =                          1,
                            @NAME =                                    PROSPECTSEGMENTATION.NAME,
                            @DESCRIPTION =                      PROSPECTSEGMENTATION.DESCRIPTION,
                            @TSLONG =                                PROSPECTSEGMENTATION.TSLONG,
                          @OTHERSCANMODIFY =                  PROSPECTSEGMENTATION.OTHERSCANMODIFY,
                            @RESEARCHGROUPID =                  PROSPECTSEGMENTATION.RESEARCHGROUPID,
                            @IDSETREGISTERID =                  PROSPECTSEGMENTATION.IDSETREGISTERID,
                            @PRIMARYVARIABLE =                  PRIMARYVARIABLE.TYPECODE,            
                            @SECONDARYVARIABLE1 =              SECONDARYVARIABLE.TYPECODE,
                            @SECONDARYVARIABLE2    =              SECONDARYVARIABLE.TYPE2CODE,
                  @PRIMARYSECTIONSXML =                dbo.UFN_PROSPECTSEGMENTATION_SECTIONS_PRIMARY_TOITEMLISTXML(PRIMARYVARIABLE.ID),
                          @SECONDARYSECTIONSXML =         dbo.UFN_PROSPECTSEGMENTATION_SECTIONS_SECONDARY_TOITEMLISTXML(SECONDARYVARIABLE.ID)   

                        from
                            dbo.PROSPECTSEGMENTATION 

                        left join dbo.PROSPECTSEGMENTATIONVARIABLE PRIMARYVARIABLE on
                            PRIMARYVARIABLE.PROSPECTSEGMENTATIONID = PROSPECTSEGMENTATION.ID and
              PRIMARYVARIABLE.ISPRIMARY = 1

            left join dbo.PROSPECTSEGMENTATIONVARIABLE SECONDARYVARIABLE on
                            SECONDARYVARIABLE.PROSPECTSEGMENTATIONID = PROSPECTSEGMENTATION.ID and
              SECONDARYVARIABLE.ISPRIMARY = 0

            where PROSPECTSEGMENTATION.ID = @ID


                        set @PRIMARYSECTIONS =
            (select
                            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                            T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
                            T.c.value('(LABEL)[1]','nvarchar(20)') AS 'LABEL',
                            nullif(T.c.value('(RANGELOW)[1]','int'),-1) AS 'RANGELOW',
                            nullif(T.c.value('(RANGEHIGH)[1]','int'), -1) AS 'RANGEHIGH',
                            T.c.value('(ESTIMATEDWEALTHLOWID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHLOWID',
                            T.c.value('(ESTIMATEDWEALTHHIGHID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHHIGHID',                                        
                            T.c.value('(MAJORGIVINGCAPACITYLOWID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYLOWID',
                            T.c.value('(MAJORGIVINGCAPACITYHIGHID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYHIGHID',
                            T.c.value('(TARGETGIFTRANGELOWID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGELOWID',
                            T.c.value('(TARGETGIFTRANGEHIGHID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGEHIGHID',                                        
                            T.c.value('(WEALTHESTIMATORRATINGLOWID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGLOWID',
                            T.c.value('(WEALTHESTIMATORRATINGHIGHID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGHIGHID'
                        from
              @PRIMARYSECTIONSXML.nodes('/PRIMARYSECTIONS/ITEM') T(c)   
            for
              xml raw('ITEM'),type,elements,root('PRIMARYSECTIONS'),BINARY BASE64
                        );

                        set @SECONDARYSECTIONS1 = (select
                            T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                            T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
                            T.c.value('(LABEL)[1]','nvarchar(20)') AS 'LABEL',
                            nullif(T.c.value('(RANGELOW)[1]','int'),-1) AS 'RANGELOW',
                            nullif(T.c.value('(RANGEHIGH)[1]','int'), -1) AS 'RANGEHIGH',
                            T.c.value('(ESTIMATEDWEALTHLOWID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHLOWID',
                            T.c.value('(ESTIMATEDWEALTHHIGHID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHHIGHID',                                        
                            T.c.value('(MAJORGIVINGCAPACITYLOWID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYLOWID',
                            T.c.value('(MAJORGIVINGCAPACITYHIGHID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYHIGHID',
                            T.c.value('(TARGETGIFTRANGELOWID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGELOWID',
                            T.c.value('(TARGETGIFTRANGEHIGHID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGEHIGHID',                                        
                            T.c.value('(WEALTHESTIMATORRATINGLOWID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGLOWID',
                            T.c.value('(WEALTHESTIMATORRATINGHIGHID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGHIGHID'
                        from 
              @SECONDARYSECTIONSXML.nodes('/SECONDARYSECTIONS/ITEM') T(c) 
            for 
              xml raw('ITEM'),type,elements,root('SECONDARYSECTIONS1'),BINARY BASE64
                        );

                        set @SECONDARYSECTIONS2 = 
            (select
                            T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
                            T.c.value('(LABEL2)[1]','nvarchar(20)') AS 'LABEL',
                            nullif(T.c.value('(RANGE2LOW)[1]','int'),-1) AS 'RANGELOW',
                            nullif(T.c.value('(RANGE2HIGH)[1]','int'), -1) AS 'RANGEHIGH',
                            T.c.value('(ESTIMATEDWEALTH2LOWID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHLOWID',
                            T.c.value('(ESTIMATEDWEALTH2HIGHID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHHIGHID',                                        
                            T.c.value('(MAJORGIVINGCAPACITY2LOWID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYLOWID',
                            T.c.value('(MAJORGIVINGCAPACITY2HIGHID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYHIGHID',
                            T.c.value('(TARGETGIFTRANGE2LOWID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGELOWID',
                            T.c.value('(TARGETGIFTRANGE2HIGHID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGEHIGHID',                                        
                            T.c.value('(WEALTHESTIMATORRATING2LOWID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGLOWID',
                            T.c.value('(WEALTHESTIMATORRATING2HIGHID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGHIGHID'
                        from 
              @SECONDARYSECTIONSXML.nodes('/SECONDARYSECTIONS/ITEM') T(c)
            where
              T.c.value('(LABEL2)[1]','nvarchar(20)') != ''
            for 
              xml raw('ITEM'),type,elements,root('SECONDARYSECTIONS2'),BINARY BASE64


                        );

                        return 0;