USP_DATALIST_CONSTITUENTRECOGNITIONBENEFITS

A list of benefits for a constituent recognition program.
Parameters

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

Copy


            CREATE procedure dbo.USP_DATALIST_CONSTITUENTRECOGNITIONBENEFITS
                (
                @CONSTITUENTRECOGNITIONID uniqueidentifier
                )
                as 
                set nocount on;

                declare @PROGRAMID uniqueidentifier;
                declare @CONSTITUENTID uniqueidentifier;

                select @PROGRAMID = RECOGNITIONPROGRAMID,
                    @CONSTITUENTID = CONSTITUENTID
                from dbo.CONSTITUENTRECOGNITION
                where ID = @CONSTITUENTRECOGNITIONID;

                select 
                    CONSTITUENTRECOGNITIONBENEFIT.ID,
                    RECOGNITIONLEVEL.NAME AS RECOGNITIONLEVELNAME,
                    CONSTITUENTRECOGNITION.JOINDATE AS DATERECEIVED,
                    BENEFIT.NAME as BENEFIT,
                    CONSTITUENTRECOGNITIONBENEFIT.QUANTITY,
                    CONSTITUENTRECOGNITIONBENEFIT.UNITVALUE,
                    (CONSTITUENTRECOGNITIONBENEFIT.QUANTITY * CONSTITUENTRECOGNITIONBENEFIT.UNITVALUE) AS [TOTALVALUE],
                    CONSTITUENTRECOGNITIONBENEFIT.DETAILS as DETAILS,
                    CONSTITUENTRECOGNITIONBENEFIT.BASECURRENCYID,
                    CONSTITUENTRECOGNITIONBENEFIT.DATEADDED
                from dbo.CONSTITUENTRECOGNITION
                    inner join     dbo.CONSTITUENTRECOGNITIONBENEFIT on CONSTITUENTRECOGNITIONBENEFIT.CONSTITUENTRECOGNITIONID = CONSTITUENTRECOGNITION.ID
                    inner join dbo.BENEFIT on CONSTITUENTRECOGNITIONBENEFIT.BENEFITID = BENEFIT.ID
                    inner join dbo.RECOGNITIONLEVEL on CONSTITUENTRECOGNITION.RECOGNITIONLEVELID = RECOGNITIONLEVEL.ID
                where CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID=@PROGRAMID    
                    and CONSTITUENTRECOGNITION.CONSTITUENTID = @CONSTITUENTID
                ;