USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTRECOGNITIONDECLINEDLEVELSBYPROGRAM

Parameters

Parameter	Parameter Type	Mode
@ID	nvarchar(72)	IN
@DATALOADED	bit	INOUT
@TSLONG	bigint	INOUT
@RECOGNITIONPROGRAMID	uniqueidentifier	INOUT
@DECLINEPROGRAM	bit	INOUT
@DECLINEDRECOGNITIONLEVELS	xml	INOUT
@REASONCODE	uniqueidentifier	INOUT

Definition

Copy


        CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_CONSTITUENTRECOGNITIONDECLINEDLEVELSBYPROGRAM
        (
            @ID nvarchar(72),
            @DATALOADED bit = 0 output,
            @TSLONG bigint = 0 output,
              @RECOGNITIONPROGRAMID uniqueidentifier = null output,
              @DECLINEPROGRAM bit = null output,
              @DECLINEDRECOGNITIONLEVELS xml = null output,
            @REASONCODE uniqueidentifier = null output
        )
        as

            set nocount on;

            set @DATALOADED = 0

          declare @CONSTITUENTID uniqueidentifier

            set    @RECOGNITIONPROGRAMID = cast(substring(@ID,1,36) as uniqueidentifier)
            set @CONSTITUENTID = cast(substring(@ID,37,72) as uniqueidentifier)


         set @DECLINEPROGRAM = case when exists(select ID from dbo.CONSTITUENTRECOGNITIONDECLINEDPROGRAM where RECOGNITIONPROGRAMID =    @RECOGNITIONPROGRAMID and CONSTITUENTID = @CONSTITUENTID) then 1
                                else 0
                                end;

         set @REASONCODE =  (select top 1 REASONCODEID from dbo.CONSTITUENTRECOGNITIONDECLINEDLEVEL where @RECOGNITIONPROGRAMID=RECOGNITIONPROGRAMID and @CONSTITUENTID = CONSTITUENTID)

          select
                  @DATALOADED = 1,
              @DECLINEDRECOGNITIONLEVELS =  (SELECT [DECLINED], [DECLINEDLEVELID], [RECOGNITIONLEVELID],[RECOGNITIONLEVELNAME]
                                              FROM dbo.[UFN_CONSTITUENTRECOGNITION_GETDECLINEDLEVELS](@CONSTITUENTID, @RECOGNITIONPROGRAMID)
                                              for xml raw('ITEM'),type,elements,root('DECLINEDRECOGNITIONLEVELS'),BINARY BASE64)                      

            return 0;