DIM_SPONSORSHIP_STAGE

Intermediate storage for sponsorship records

Fields

Field	Field Type	Null	Notes	Description
SPONSORSHIPSYSTEMID	uniqueidentifier	yes		dbo.[SPONSORSHIP].[ID]
SPONSORCONSTITUENTSYSTEMID	uniqueidentifier	yes		dbo.[SPONSORSHIP].[CONSTITUENTID]
SPONSORSHIPCOMMITMENTSYSTEMID	uniqueidentifier	yes		dbo.[SPONSORSHIPTRANSACTION].[SPONSORSHIPCOMMITMENTID]
SPONSORSHIPCOMMITMENTSEQUENCE	smallint	yes		dbo.[SPONSORSHIPCOMMITMENT].[COMMITMENTSEQUENCE]
SPONSORSHIPCOMMITMENTLOOKUPID	nvarchar(100)	yes		dbo.[SPONSORSHIPCOMMITMENT].[LOOKUPID]
SPONSORSHIPPROGRAMNAME	nvarchar(100)	yes		dbo.[SPONSORSHIPPROGRAM].[NAME]
SPONSORSHIPPROGRAMAMOUNT	money	yes		dbo.[SPONSORSHIPPROGRAM].[AMOUNT]
SPONSORSHIPPROGRAMISACTIVE	bit	yes		dbo.[SPONSORSHIPPROGRAM].[ISINACTIVE]
ISAFFILIATESPONSORSHIPPROGRAM	bit	yes		Derived from dbo.[SPONSORSHIPAFFILIATEPROGRAM].[ID]
AFFILIATECONSTITUENTSYSTEMID	uniqueidentifier	yes		dbo.[SPONSORSHIPAFFILIATEPROGRAM].[AFFILIATEID]
SPONSORSHIPOPPORTUNITYGROUPNAME	nvarchar(100)	yes		dbo.[SPONSORSHIPOPPORTUNITYGROUP].[NAME]
SPONSORSHIPOPPORTUNITYGROUPSEQUENCE	int	yes		dbo.[SPONSORSHIPOPPORTUNITYGROUP].[SEQUENCE]
SPONSORSHIPOPPORTUNITYGROUPISACTIVE	bit	yes		Derived from dbo.[SPONSORSHIPOPPORTUNITYGROUP].[ISINACTIVE]
SPONSORSPEROPPORTUNITY	smallint	yes		dbo.[SPONSORSHIPOPPORTUNITYGROUP].[SPONSORSPEROPPORTUNITY]
SPONSORSHIPOPPORTUNITYSYSTEMID	uniqueidentifier	yes		dbo.[SPONSORSHIPOPPORTUNITY].[ID]
SPONSORSHIPOPPORTUNITY	nvarchar(154)	yes		dbo.[SPONSORSHIPOPPORTUNITYPROJECT].[NAME] or dbo.[SPONSORSHIPOPPORTUNITYCHILD].[NAME]
SPONSORSHIPOPPORTUNITYTYPECODE	smallint	yes		dbo.[SPONSORSHIPOPPORTUNITYGROUP].[SPONSORSHIPOPPORTUNITYTYPECODE]
SPONSORSHIPOPPORTUNITYTYPE	nvarchar(25)	yes		dbo.[SPONSORSHIPOPPORTUNITYGROUP].[SPONSORSHIPOPPORTUNITYTYPE]
SPONSORSHIPOPPORTUNITYELIGIBILITYCODE	smallint	yes		dbo.[SPONSORSHIPOPPORTUNITY].[ELIGIBILITYCODE]
SPONSORSHIPOPPORTUNITYELIGIBILITY	nvarchar(25)	yes		dbo.[SPONSORSHIPOPPORTUNITY].[ELIGIBILITY]
INELIGIBILESPONSORSHIPREASONDIMID	int	yes		Reference key to the sponsorship reason dimension, derived from dbo.[SPONSORSHIPOPPORTUNITY].[SPONSORSHIPREASONID]
INELIGIBILESPONSORSHIPREASON	nvarchar(100)	yes		dbo.[SPONSORSHIPREASON].[REASON]
SPONSORSHIPOPPORTUNITYAVAILABILITYCODE	smallint	yes		dbo.[SPONSORSHIPOPPORTUNITY].[AVAILABILITYCODE]
SPONSORSHIPOPPORTUNITYAVAILABILITY	nvarchar(25)	yes		dbo.[SPONSORSHIPOPPORTUNITY].[AVAILABILITY]
SPONSORSHIPOPPORTUNITYLOOKUPID	nvarchar(100)	yes		dbo.[SPONSORSHIPOPPORTUNITY].[LOOKUPID]
SPONSORSHIPOPPORTUNITYSTATUS	nvarchar(25)	yes		If dbo.[SPONSORSHIPOPPORTUNITYCHILD].[ID] is null and dbo.[SPONSORSHIPOPPORTUNITY].[ELIGIBILITYCODE] = 1 then 'OPEN', If dbo.[SPONSORSHIPOPPORTUNITYCHILD].[ID] is null and dbo.[SPONSORSHIPOPPORTUNITY].[ELIGIBILITYCODE] = 2 then 'CLOSED'.
LASTDOCUMENTATIONDATEDIMID	int	yes		Reference key to the date dimension, derived from dbo.[SPONSORSHIPOPPORTUNITY].[LASTDOCUMENTATIONDATE]
LASTDOCUMENTATIONDATE	datetime	yes		dbo.[SPONSORSHIPOPPORTUNITY].[LASTDOCUMENTATIONDATE]
SPONSORSHIPOPPORTUNITYCHILDCONSTITUENTSYSTEMID	uniqueidentifier	yes		dbo.[SPONSORSHIPOPPORTUNITYCHILD].[CONSTITUENTID]
SPONSORSHIPOPPORTUNITYCHILDFULLNAME	nvarchar(154)	yes		dbo.[SPONSORSHIPOPPORTUNITYCHILD].[NAME]
SPONSORSHIPOPPORTUNITYPROJECTNAME	nvarchar(100)	yes		dbo.[SPONSORSHIPOPPORTUNITYPROJECT].[NAME]
SPONSORSHIPSTATUSCODE	smallint	yes		dbo.[SPONSORSHIP].[STATUSCODE]
SPONSORSHIPSTATUS	nvarchar(25)	yes		dbo.[SPONSORSHIP].[STATUS]
SPONSORSHIPSTARTDATEDIMID	int	yes		Reference key to the date dimension, derived from dbo.[SPONSORSHIP].[STARTDATE]
SPONSORSHIPSTARTDATE	datetime	yes		dbo.[SPONSORSHIP].[STARTDATE]
SPONSORSHIPENDDATEDIMID	int	yes		Reference key to the date dimension, derived from dbo.[SPONSORSHIP].[ENDDATE]
SPONSORSHIPENDDATE	datetime	yes		dbo.[SPONSORSHIP].[ENDDATE]
SPONSORSHIPPLANNEDENDDATEDIMID	int	yes		Reference key to the date dimension, derived from dbo.[SPONSORSHIP].[PLANNEDENDDATE]
SPONSORSHIPPLANNEDENDDATE	datetime	yes		dbo.[SPONSORSHIP].[PLANNEDENDDATE]
CHILDGENDERCODE	smallint	yes		dbo.[SPONSORSHIP].[CHILDGENDERCODE]
CHILDGENDER	nvarchar(25)	yes		dbo.[SPONSORSHIP].[CHILDGENDER]
ISHIVPOSITIVECODE	smallint	yes		dbo.[SPONSORSHIP].[ISHIVPOSITIVECODE]
ISHIVPOSITIVE	nvarchar(25)	yes		dbo.[SPONSORSHIP].[ISHIVPOSITIVE]
HASCONDITIONCODE	smallint	yes		dbo.[SPONSORSHIP].[HASCONDITIONCODE]
HASCONDITION	nvarchar(25)	yes		dbo.[SPONSORSHIP].[HASCONDITION]
ISORPHANEDCODE	smallint	yes		dbo.[SPONSORSHIP].[ISORPHANEDCODE]
ISORPHANED	nvarchar(25)	yes		dbo.[SPONSORSHIP].[ISORPHANED]
AGERANGE	nvarchar(100)	yes		dbo.[SPONSORSHIPOPPORTUNITYAGERANGE].[DISPLAYNAME]
SPONSORSHIPLOCATIONDIMID	int	yes		Reference key to the sponsorship location dimension, derived from dbo.[SPONSORSHIP].[LOACTIONID]
SPONSORSHIPLOCATIONNAME	nvarchar(100)	yes		dbo.[SPONSORSHIPLOCATION].[NAME]
SPONSORSHIPLOCATIONLOOKUPID	nvarchar(100)	yes		dbo.[SPONSORSHIPLOCATION].[LOOKUPID]
ISMOSTRECENTFORMCOMMITMENT	bit	yes		dbo.[SPONSORSHIP].[ISMOSTRECENTFORCOMMITMENT]
EXPIRATIONSPONSORSHIPREASONDIMID	int	yes		Reference key to the sponsorship reason dimension, derived from dbo.[SPONSORSHIP].[EXPIRATIONREASONID]
EXPIRATIONSPONSORSHIPREASON	nvarchar(100)	yes		dbo.[SPONSORSHIPREASON].[REASON]
SPONSORSHIPOPPORTUNITYPROJECTCATEGORYCODESYSTEMID	uniqueidentifier	yes		dbo.[SPONSORSHIP].[SPROPPPROJECTCATEGORYCODEID]
SPONSORSHIPOPPORTUNITYPROJECTCATEGORY	nvarchar(100)	yes		dbo.[SPROPPPROJECTCATEGORYCODE].[DESCRIPTION]
ISSOLESPONSORSHIP	bit	yes		dbo.[SPONSORSHIP].[ISSOLESPONSORSHIP]
ISGIFTSPONSORSHIP	bit	yes		If dbo.[FINANCIALTRANSACTION].[CONSTITUENTID] = dbo.[SPONSORSHIP].[CONSTITUENTID] or dbo.[SPONSORSHIP].[REVENUESPLITID] is null then 0 else 1.
FINANCIALSPONSORCONSTITUENTSYSTEMID	uniqueidentifier	yes		dbo.[FINANCIALTRANSACTION].[CONSTITUENTID]
FINANCIALTRANSACTIONLINEITEMSYSTEMID	uniqueidentifier	yes		dbo.[SPONSORSHIP].[REVENUESPLITID]
ISINCLUDED	bit	yes		Flag indicating when data should be included in results.
SOURCEDIMID	int	yes		Source system used.
ETLCONTROLID	int	yes		ID generated through the ETL process.
OPPORTUNITYSPONSORSHIPLOCATIONDIMID	int	yes		Reference key to the sponsorship location dimension, derived from dbo.[SPONSORSHIPOPPORTUNITY].[SPONSORSHIPLOCATIONID]