UFN_HOUSEHOLD_GETRECOGNITIONSETTINGS

Returns the recognition settings for a household.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@HOUSEHOLDID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_HOUSEHOLD_GETRECOGNITIONSETTINGS
            (
                @HOUSEHOLDID uniqueidentifier
            )
            returns @settings table
            (
                HOUSEHOLDRECOGNIZEHOUSEHOLD bit,
                HOUSEHOLDRECOGNIZEMEMBERSCODE tinyint,
                MEMBERRECOGNIZEHOUSEHOLD bit,
                MEMBERRECOGNIZEMEMBER bit,
                MEMBERRECOGNIZEOTHERMEMBERSCODE tinyint,
                HOUSEHOLDREVENUERECOGNITIONTYPECODEID uniqueidentifier,
                MEMBERREVENUERECOGNITIONTYPECODEID uniqueidentifier,
                MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID uniqueidentifier
            )
            as
            begin

                insert into @settings
                (
                    HOUSEHOLDRECOGNIZEHOUSEHOLD,
                    HOUSEHOLDRECOGNIZEMEMBERSCODE,
                    MEMBERRECOGNIZEHOUSEHOLD,
                    MEMBERRECOGNIZEMEMBER,
                    MEMBERRECOGNIZEOTHERMEMBERSCODE,
                    HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                    MEMBERREVENUERECOGNITIONTYPECODEID,
                    MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID
                )
                select top 1 
                    HOUSEHOLDRECOGNIZEHOUSEHOLD,
                    HOUSEHOLDRECOGNIZEMEMBERSCODE,
                    MEMBERRECOGNIZEHOUSEHOLD,
                    MEMBERRECOGNIZEMEMBER,
                    MEMBERRECOGNIZEOTHERMEMBERSCODE,
                    HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                    MEMBERREVENUERECOGNITIONTYPECODEID,
                    MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID
                from
                (
                    select
                        HOUSEHOLDRECOGNIZEHOUSEHOLD,
                        HOUSEHOLDRECOGNIZEMEMBERSCODE,
                        MEMBERRECOGNIZEHOUSEHOLD,
                        MEMBERRECOGNIZEMEMBER,
                        MEMBERRECOGNIZEOTHERMEMBERSCODE,
                        HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                        MEMBERREVENUERECOGNITIONTYPECODEID,
                        MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                        1 as ORDERCOL
                    from dbo.HOUSEHOLDRECOGNITIONSETTINGS where ID = @HOUSEHOLDID

                    union all

                    -- If there aren't specific settings for this household, try to use global default settings

                    select top 1
                        HOUSEHOLDRECOGNIZEHOUSEHOLD,
                        HOUSEHOLDRECOGNIZEMEMBERS,
                        MEMBERRECOGNIZEHOUSEHOLD,
                        MEMBERRECOGNIZEMEMBER,
                        MEMBERRECOGNIZEOTHERMEMBERS,
                        HOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                        MEMBERREVENUERECOGNITIONTYPECODEID,
                        MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID,
                        2 as ORDERCOL
                    from dbo.HOUSEHOLDINFO
                    order by DATEADDED

                    union all

                    -- If there aren't any default settings, return none for all values

                    select
                        0,
                        0,
                        0,
                        0,
                        0,
                        null,
                        null,
                        null,
                        3 as ORDERCOL
                ) as SETTINGS
                order by ORDERCOL

                return
            end