Find Data in the OLTP Database

An entity relationship diagram for Prospects in an Infinity database is here: Prospects ERD

One part of the diagram that relates to this report example is:

An interaction is associated with a plan outline step through a foreign key. The column is PLANOUTLINESTEPID of type uniqueidentifier. The GUIDs in the INTERACTION.PLANOUTLINESTEPID column correspond to ID in the PLANOUTLINESTEP table.

A plan outline step is associated with a plan outline through a foreign key. The column is PLANOUTLINEID of type uniqueidentifier. The GUIDs in the PLANOUTLINESTEP.PLANOUTLINEID column correspond to ID in the PLANOUTLINE table.

An interaction is associated with a constituent through a foreign key. The column is CONSTITUENTID of type uniqueidentifier. The GUIDs in the INTERACTION.CONSTITUENTID column correspond to ID in the CONSTITUENT table.

An interaction is associated with a prospect plan through a foreign key. The column is PROSPECTPLANID and the fields are of type uniqueidentifier. The GUIDs in the INTERACTION.PROSPECTPLANID column correspond to ID in the PROSPECTPLAN table.

Notice PROSPECTPLANSTATUSCODEID appears on the INTERACTION, PLANOUTLINESTEP, and PROSPECTPLAN tables. PROSPECTPLANSTATUSCODEID indicates which stage of the plan to which a step belongs. PROSPECTPLANSTATUSCODEID identifies an entry on the PROSPECTPLANSTATUSCODE code table. The code table is called Prospect Plan Stage. But the table name is PROSPECTPLANSTATUSCODE.

PROSPECTPLANSTATUSCODE (Prospect Plan Stage) can be managed from Administration > Code Tables. The category for the Prospect Plan Stage code table is Major Giving. Typical entries include Identification, Cultivation, Solicitation, and Negotiation.

The INTERACTION table contains other status information in the STATUSCODE, STATUS, and COMPLETED columns. STATUSCODE is a tinyint column that maintains these codes: 0=Planned, 1=Pending, 2=Completed, 3=Unsuccessful, 4=Cancelled, 5=Declined. COMPLETED is a computed int column with this expression: case when STATUSCODE in (2,3,4,5) then 1 else 0 end. So COMPLETED is true (1) when STATUSCODE contains the tinyint code representation for Completed, Unsuccessful, Cancelled, or Declined. STATUS is a computed field which provides a translation for STATUSCODE:

CASE [STATUSCODE] 

WHEN 0 THEN N'Planned' 

WHEN 1 THEN N'Pending' 

WHEN 2 THEN N'Completed' 

WHEN 3 THEN N'Unsuccessful' 

WHEN 4 THEN N'Cancelled' 

WHEN 5 THEN N'Declined' 

END

Note: There are also status-related columns for funding requests. But these columns support Foundations functionality.

On the PROSPECTPLAN table, PROSPECTPLANSTATUSCODEID indicates Current plan stage. This is the stage of the most recently completed plan step. On the INTERACTION table, PROSPECTPLANSTATUSCODEID indicates the stage associated with the interaction, which also represents a step. On the PLANOUTLINESTEP table, PROSPECTPLANSTATUSCODEID indicates the stage associated with the plan outline step. A plan outline step is not a step in a prospect plan. A plan outline step is a step in a plan outline. Plan outline steps and plan outlines are template mechanisms. Plan outlines establish the default steps created when you add a plan based on a plan outline.

So for the purposes of reporting, plan outlines and plan outline steps are not the a primary concern unless the goal is to audit the plan outlines and plan outline steps. But the current plan stage as maintained in PROSPECTPLANSTATUSCODEID in PROSPECTPLAN and the stages associated with steps as maintained in PROSPECTPLANSTATUSCODEID in INTERACTION are useful for reporting on the transitions between stages. INTERACTION contains all of the steps. To report on the transition between stages, INTERACTION is the table to query. But to report on the transition from the start of a plan to the current status, PROSPECTPLAN is the table to query.

A constituent can be associated with more than one plan and more than one plan of a given type. A report that breaks out information by constituent and plan type must address that complication.

Later in the document, the core of our OLTP queries will use the PROSPECTPLAN, INTERACTION, and PROSPECTPLANSTATUSCODE tables as shown in the following database diagram created in SQL Server Management Studio.