USP_DATAFORMTEMPLATE_VIEW_HOUSEHOLDRECOGNITIONSETTINGS

The load procedure used by the view dataform template "Household Recognition Settings View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@HOUSEHOLDRECOGNIZEHOUSEHOLD	bit	INOUT	Household
@HOUSEHOLDRECOGNIZEMEMBERS	tinyint	INOUT	All members
@MEMBERRECOGNIZEHOUSEHOLD	bit	INOUT	Household
@MEMBERRECOGNIZEMEMBER	bit	INOUT	Member
@MEMBERRECOGNIZEOTHERMEMBERS	tinyint	INOUT	All other members
@HOUSEHOLDRECOGNIZEMEMBERSBOOL	bit	INOUT	All members
@MEMBERRECOGNIZEOTHERMEMBERSBOOL	bit	INOUT	All other members
@HOUSEHOLDREVENUERECOGNITIONTYPE	nvarchar(100)	INOUT	Household recognition credit type
@MEMBERREVENUERECOGNITIONTYPE	nvarchar(100)	INOUT	Member recognition credit type
@MEMBERHOUSEHOLDREVENUERECOGNITIONTYPE	nvarchar(100)	INOUT	Member household recognition credit type

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_HOUSEHOLDRECOGNITIONSETTINGS
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @HOUSEHOLDRECOGNIZEHOUSEHOLD bit = null output,
  @HOUSEHOLDRECOGNIZEMEMBERS tinyint = null output,
  @MEMBERRECOGNIZEHOUSEHOLD bit = null output,
  @MEMBERRECOGNIZEMEMBER bit = null output,
  @MEMBERRECOGNIZEOTHERMEMBERS tinyint = null output,
  @HOUSEHOLDRECOGNIZEMEMBERSBOOL bit = null output,
  @MEMBERRECOGNIZEOTHERMEMBERSBOOL bit = null output,
  @HOUSEHOLDREVENUERECOGNITIONTYPE nvarchar(100) = null output,
  @MEMBERREVENUERECOGNITIONTYPE nvarchar(100) = null output,
  @MEMBERHOUSEHOLDREVENUERECOGNITIONTYPE nvarchar(100) = null output
)
as
  set nocount on;

  select
    @HOUSEHOLDRECOGNIZEHOUSEHOLD = RECINFO.HOUSEHOLDRECOGNIZEHOUSEHOLD,
    @HOUSEHOLDRECOGNIZEMEMBERS = RECINFO.HOUSEHOLDRECOGNIZEMEMBERSCODE,
    @MEMBERRECOGNIZEHOUSEHOLD = RECINFO.MEMBERRECOGNIZEHOUSEHOLD, 
    @MEMBERRECOGNIZEMEMBER = RECINFO.MEMBERRECOGNIZEMEMBER, 
    @MEMBERRECOGNIZEOTHERMEMBERS = RECINFO.MEMBERRECOGNIZEOTHERMEMBERSCODE,

    @HOUSEHOLDRECOGNIZEMEMBERSBOOL = case when RECINFO.HOUSEHOLDRECOGNIZEMEMBERSCODE = 1 then 1 else 0 end,
    @MEMBERRECOGNIZEOTHERMEMBERSBOOL = case when RECINFO.MEMBERRECOGNIZEOTHERMEMBERSCODE = 1 then 1 else 0 end,
    @HOUSEHOLDREVENUERECOGNITIONTYPE = HHCODE.DESCRIPTION,
    @MEMBERREVENUERECOGNITIONTYPE = MEMBERCODE.DESCRIPTION,
    @MEMBERHOUSEHOLDREVENUERECOGNITIONTYPE = MEMBERHOUSEHOLDCODE.DESCRIPTION,

    @DATALOADED = 1
  from
    dbo.UFN_HOUSEHOLD_GETRECOGNITIONSETTINGS(@ID) RECINFO
  left join dbo.REVENUERECOGNITIONTYPECODE HHCODE on RECINFO.HOUSEHOLDREVENUERECOGNITIONTYPECODEID = HHCODE.ID
  left join dbo.REVENUERECOGNITIONTYPECODE MEMBERCODE on RECINFO.MEMBERREVENUERECOGNITIONTYPECODEID = MEMBERCODE.ID                    
  left join dbo.REVENUERECOGNITIONTYPECODE MEMBERHOUSEHOLDCODE on RECINFO.MEMBERHOUSEHOLDREVENUERECOGNITIONTYPECODEID = MEMBERHOUSEHOLDCODE.ID