Warehouse table: Activities

The Activities warehouse table holds most of the activities referred to by the ActivityPointer CRM view (the "history" page in the CRM application):

  • Appointment
  • Email
  • CampaignActivity
  • PhoneCall
  • Fax
  • Letter
  • ServiceAppintment
  • Task 

The view vw_Extract_Activities is defined in the warehouse as well, and this view in turn draws from all of the views listed above in the CRM database.

Note: The OpportunityClose activity is not included here, but may be included in the future.

Note: The CampaignResponse activity is not included here;  this activity type is warehoused separately from the other activities. 

Unlike all other warehouse records, activity records may be joined to parent records either by the OpportunityGUID or by the PersonGUID;  all activity records will have a PersonGUID listed, but some may also have a more specific OpportunityGUID. 

 

This table is made available to clients via the Advanced Export.

 

This table represents the "Stage II" versioning of the warehouse.  For the stage I version, see below.


Warehouse tableWarehouse columnField typeCRM viewCRM columnNotesStandard Export nameCRM tab
ActivitiesActivityCategoryGUID
vw_Extract_Activitiesa422_rel_activitycategoryid   
ActivitiesActivityCategory vw_Extract_Activitiesa422_rel_activitycategoryidname   
ActivitiesActivityValueGUID vw_Extract_Activitiesa422_rel_activityvalueid   
ActivitiesActivityValue vw_Extract_Activitiesa422_rel_activityvalueidname   
ActivitiesCampaignJobGUID vw_Extract_Activitiescategorynew  
ActivitiesStaffGUID vw_Extract_Activitiesownerid   
ActivitiesStaff vw_Extract_Activitiesowneridname   
ActivitiesOpportunityGUID vw_Extract_Activitiesregardingobjectidfor regardingobjecttypecode = 3 
ActivitiesPersonGUID vw_Extract_Activitiesregardingobjectidfor regardingobjecttypecode = 2 
ActivitiesListGUID vw_Extract_Activitiessubcategorynew  
ActivitiesActivityGUID vw_Extract_Activitiesactivityidprimary key  
ActivitiesActualEnd vw_Extract_Activitiesactualend Activity_0N_DateOpportunity/History
ActivitiesDescription vw_Extract_Activitiesdescription   
ActivitiesSubject vw_Extract_Activitiessubject Activity_0N_ValueOpportunity/History
Activitiescreatedby vw_Extract_Activitiescreatedby   
Activitiescreatedbyname vw_Extract_Activitiescreatedbyname   
Activitiescreatedonutc vw_Extract_Activitiescreatedonutc   
Activitiesmodifiedby vw_Extract_Activitiesmodifiedby   
Activitiesmodifiedbyname vw_Extract_Activitiesmodifiedbyname   
Activitiesmodifiedonutc vw_Extract_Activitiesmodifiedonutc   
Activitiesstatecode vw_Extract_Activitiesstatecode   
Activitiesstatecodename vw_Extract_Activitiesstatecodename   
Activitiesstatuscode vw_Extract_Activitiesstatuscode   
Activitiesstatuscodename vw_Extract_Activitiesstatuscodename   

Activities

ActivityType vw_Extract_ActivitiesCRMActivityType   

 

As of version "Stage I":

Fields which have been struck through are recommended for removal.  Fields which have been bolded are recommended for addition.


"task" view"all" view"pointer" viewtypesamplewarehouseanalytics useexport use
a422_rel_activitycategoryidActivityCategoryId uniqueidentifier[guid]a422_rel_activitycategoryid  
a422_rel_activitycategoryidDsc  int0a422_rel_activitycategoryiddsc  
a422_rel_activitycategoryidNameActivityCategoryName nvarchar(100)Applicationa422_rel_activitycategoryidnamefilter = 'Campus Visit'  
a422_rel_activityvalueidActivityValueId uniqueidentifier[guid]a422_rel_activityvalueid  
a422_rel_activityvalueidDsc  int0a422_rel_activityvalueiddsc  
a422_rel_activityvalueidNameActivityValueName nvarchar(100)Official transcripta422_rel_activityvalueidnamefilter like 'Attended%'  
A422_Temp_ActivityCategory  int a422_temp_activitycategory  
   nvarchar(255) a422_temp_activitycategoryname  
A422_Temp_ActivityValue  int a422_temp_activityvalue  
   nvarchar(255) a422_temp_activityvaluename  
ActivityIdactivityidActivityIduniqueidentifier[guid]activityidprimary key  
ActualDurationMinutes ActualDurationMinutesint30actualdurationminutes  
Actual End (filtered only)

datetimeDec 16 2009 11:32AMactualendFirst_Visit_ Date / MR_ Visit_DateActivity_0N_Date
ActualEndactualend ActualEnddatetimeNov 12 2009 12:56AMactualendutcFirst_Visit _Date / MR _Visit_Date (proposed) Activity_0N_Date (proposed)
ActualStart (filtered only) 
datetimeDec 16 2009 11:32AMactualstart  
ActualStart ActualStartdatetimeJan 7 2010 2:05PMactualstartutc  
Category  nvarchar(250) category  
CreatedBy CreatedByuniqueidentifier[guid]createdby  
CreatedByDsc CreatedByDscint0createdbydsc  
CreatedByName CreatedByNamenvarchar(160)Tiffany Hendrixcreatedbyname  
CreatedByYomiName CreatedByYomiNamenvarchar(160)Tiffany Hendrixcreatedbyyominame  
CreatedOn (filtered only) 
datetimeDec 16 2009 6:32AMcreatedon  
CreatedOn  CreatedOndatetimeNov 12 2009 12:56AMcreatedonutcfilter < @Today  
 CRMActivityType varchar(16) CRMActivityType (proposed)  
DeletionStateCode  int  filter = 0 filter = 0
DescriptiondescriptionDescriptionnvarchar(0)Communication flow: Scholarship Deadline Emaildescription  
 Eligible nvarchar(255)    
ImportSequenceNumber  int    
IsBilled  bit    
   bit1IsCurrentfilter = 1  
IsWorkflowCreated  bit    
ModifiedBy ModifiedByuniqueidentifier[guid]modifiedby  
ModifiedByDsc ModifiedByDscint0modifiedbydsc  
ModifiedByName ModifiedByNamenvarchar(160)Tiffany Hendrixmodifiedbyname  
ModifiedByYomiName ModifiedByYomiNamenvarchar(160)Tiffany Hendrixmodifiedbyyominame  
ModifiedOn (filtered only) 
datetimeDec 16 2009 6:32AMmodifiedon  
ModifiedOn  ModifiedOndatetimeDec 16 2009 11:32AMmodifiedonutcprimary key / sort order sort order



int1ord sort order
OverriddenCreatedOn  datetime    
OwnerId OwnerIduniqueidentifier[guid]ownerid 
OwnerIdDsc OwnerIdDscint0owneriddsc  
OwnerIdName OwnerIdNamenvarchar(160)Tiffany Hendrixowneridname  
OwnerIdType OwnerIdTypeint8owneridtype  
OwnerIdYomiName OwnerIdYomiNamenvarchar(160)Tiffany Hendrixowneridyominame  
OwningBusinessUnit OwningBusinessUnituniqueidentifier[guid]owningbusinessunit  
OwningUser OwningUseruniqueidentifier[guid]owninguser  
PercentComplete  int    
PriorityCode  int    
RegardingObjectIdregardingobjectidRegardingObjectIduniqueidentifier[guid]regardingobjectidjoinjoin
 OpportunityID uniqueidentifier[guid]opportunityid (proposed)join (proposed)join (proposed)
 ContactID uniqueidentifier[guid]contactid (proposed)join (proposed)join (proposed)
 CampaignID uniqueidentifier[guid]campaignid (proposed)  
 BulkOperationID uniqueidentifier[guid]bulkoperationid (proposed)  
RegardingObjectIdDsc RegardingObjectIdDscint0regardingobjectiddsc  
RegardingObjectIdNameregardingobjectidnameRegardingObjectIdNamenvarchar(400)Alicia Newton - 10 Fallregardingobjectidname  
RegardingObjectIdYomiName RegardingObjectIdYomiNamenvarchar(400) regardingobjectidyominame  
RegardingObjectTypeCode RegardingObjectTypeCodeint2regardingobjecttypecodefilter = 3 
ScheduledDurationMinutes ScheduledDurationMinutesint0scheduleddurationminutes  
ScheduledEnd (filtered only) 
datetimeOct 20 2008 12:00AMscheduledend  
ScheduledEnd ScheduledEnddatetimeOct 30 2009 3:37AMscheduledendutc  
ScheduledStart (filtered only) 
datetimeOct 20 2008 12:00AMscheduledstart  
ScheduledStart ScheduledStartdatetimeOct 30 2009 3:37AMscheduledstartutc  
ServiceId ServiceIduniqueidentifier[guid]serviceid  
StateCode StateCodeint1statecode  
  [FilteredStatusMap join] nvarchar(255)Completedstatecodename  
StatusCode StatusCodeint5statuscode  
  [FilteredStatusMap join]nvarchar(255)Completedstatuscodename  
Subcategory  nvarchar(250) subcategory  
SubjectsubjectSubjectnvarchar(200)Communication flow: Scholarship Deadline Emailsubject Activity_0N_Value
SubscriptionId  uniqueidentifier    
TimeZoneRuleVersionNumber TimeZoneRuleVersionNumberint0timezoneruleversionnumber  
UTCConversionTimeZoneCode UTCConversionTimeZoneCodeint utcconversiontimezonecode  
VersionNumber  timestamp    



Comments