SPARCFulfillment Auditing Report Data Dictionary and Sample Report (v3.5.1)

1

Protocol ID

System generated from SPARCRequest

protocols.sparc_id

Protocol page SRID column

INT(11)

2

RMID

A unique numeric identifier that links a research study across multiple MUSC electronic research systems.

protocols.research_master_id

Protocol page RMID column

INT(11)

3

Patient Name

Name of participant

participants.first_name + participants.last_name

Participant Tracker header “Participant Name” column

VARCHAR(255)

4

Patient ID

Participant's Medical Record Number

participants.mrn

Participant Tracker header “ID” column

VARCHAR(255)

5

Arm Name

The name of the group or subgroup of participants in a clinical trial.

arms.name

Participant Tracker header “Current Arm” column

VARCHAR(255)

6

Visit Name

The name the protocol uses to identify a visit.

appointment.name

Participant Tracker name of the selected visit

VARCHAR(255)

7

Service Completion Date

 The date the procedure is completed

procedures.completed_date

Participant Tracker “Completed Date” date selector

DATETIME

8

Marked as Incomplete Date

The date the service is indicated as not complete

procedures.incompleted_date

System generated date stamp when a Procedure status is changed to “Incomplete” on Participant Tracker

DATETIME

9

Marked with Follow-Up Date

The date the service is being rescheduled

tasks.due_at

System generated date stamp when a Procedure “Follow Up” button is clicked upon on Participant Tracker

DATETIME

10

Added?

Field to identify if the service is part of the original study schedule or if it has been added as a one-off for a participant

Added: meaning Procedures.visit_id is NULL

Created when “Add Service” button is clicked upon on Participant Tracker

(not an actual database attribute)

11

Nexus Core

Grouping of the services by each Nexus core (MUSC specific)

procedures.sparc_core_name

Participant Tracker “Procedures” table Organization grouping  

VARCHAR(255)

12

Core Services Start Time

The time entered to indicate when the Visit’s group of Core/Program procedures began for participant. Unique times apply to each Core/Program’s services, rather than to individual services.

procedure_groups.start_time

Participant Tracker Appointments Core/Program Start Time

DATETIME

13

Core Services End Time

The time entered to indicate when the Visit’s group of Core/Program procedures ended for participant. Unique times apply to each Core/Program’s services, rather than to individual services.

procedure_groups.end_time

Participant Tracker Appointments Core/Program End Time

DATETIME

14

Service Name

The name of the procedure or study level activity

fulfillments.service_name

Participant Tracker  page “Procedures” column

VARCHAR(255)

15

Completed?

Whether the chosen procedure has been completed

procedures.status = complete?

Participant Tracker page “Status” column switch button

VARCHAR(255)

16

Billing Type (R/T/O)

Payor of the chosen service (Research, Third Party, Other?)

procedures.billing_type

Participant Tracker page “R/T” column dropdown menu

VARCHAR(255)

17

If not completed, reason and comment

The reason added for not completing a clinical service

Notes.notable_type = Procedures & notes.kind = reason;

notes.reason, and notes.comment

Participant Tracker “Add Note” popup window when “Incomplete” button is clicked on a procedure

VARCHAR(255);

VARCHAR(255)

18

Follow-Up date and comment

The date and comment entered for the assigned clinical provider to follow up with fulfilling a clinical service

Notes.notable_type = Procedures & notes.kind = followup;

Tasks.due_at;

notes.comment,

Participant Tracker  “Follow Up” popup window when clicking “Follow Up”

DATE;

VARCHAR(255)

19

Cost

The cost of the fulfilled clinical service. Is whichever rate applies: service rate, funding discount rate, override rate or modified rate.

procedures.service_cost/100

Logic-drive cost from pricing map set up in SPARCCatalog or admin rate

INT(11)

20

Notes

System generated information and clinical provider short, informal messages

notes.comment

System generated status, date change, and manually entered notes from participant tracker page

TEXT

1

Protocol ID (SRID)

System generated from SPARCRequest

protocols.sparc_id

Protocol page header SRID column

Int(11)

2

RMID

Research Master ID created by study team

protocols.research_master_id

Protocol page RMID column

Int(11)

3

Short Title

An abbreviated form of the title used to identify your study/project

protocols.short_title

Homepage Short Title Column

Varchar(255)

4

Principal Investigator

Primary PI; responsible person of a study

Project_roles.role = ‘primary_pi’

Protocol Page header “Principal Investigator” column

Varchar(255)

5

Organization

The SPARCCatalog abbreviation of the service provider organization

(SPARCRequest) organizations.abbreviation

Homepage Provider/Program/Core

Varchar(255)

6

Service Name

The name of the procedure or study level activity

line_items.service_id, (SPARCRequest) services.name

Protocol Page “Non-clinical Services” tab Service column

Varchar(255)

7

Account

User Defined Accounting Key (i.e. UDAK) Number

line_items.account_number

Protocol Page “Non-clinical Services” tab Account column

Varchar(255)

8

Contact

The person to contact regarding financial information related to a study

line_items.contact_name

Protocol Page “Non-clinical Services” tab Contact column

Varchar(255)

9

Quantity Type

Unit of measurement for Non-clinical services

line_item.quantity_type

Protocol Page “Non-clinical Services” tab Type column

Varchar(255)

10

Unit Cost

Cost per quantity type measurement. Is whichever rate applies: service rate, funding discount rate, override rate or modified rate.

Logic-driven calculation based on pricing map, funding source and admin rate

SPARCRequest Service Cost

N/A

11

Quantity Requested

Number of Non-clinical services requested for the study

line_items.quantity_requested

Protocol Page “Non-clinical Services” tab Requested column

Decimal (10,2)

12

Quantity Fulfilled

The number of how many study level activities (fulfillment quantities) have been completed

sum (fulfillments.quantity)

Non-clinical Services Tab, Fulfillments List, “Quantity” Column

(calculated)

13

Quantity Remaining

Requested Non-clinical services not yet fulfilled

Calculated value

Protocol Page “Non-clinical Services” tab Remaining column

N/A

14

Total Cost

The quantity fulfilled times the unit cost equals the total cost

Calculated value

N/A

N/A

15

Percent Subsidy

Percent of Current Request Cost to be covered by Service Provider

SPARC subsidies.percent_subsidy

Entered in SPARCRequest Step 3A Percent Subsidy

FLOAT

16

Service Started Date

The date the service has begun

line_items.started_at

Protocol Page “Non-clinical Services” tab Started column

Datetime

17

Components

The provider defined elements of a service

components.component

Protocol Page “Non-clinical Services” tab Components column

Varchar(255)

18

Last Fulfillment Date

The last date the procedure or study level activity has been completed

max(fulfillments.fulfilled_at)

Protocol Page “Non-clinical Services” tab Last Fulfillment column

Datetime

19

Notes

Short informal messages

notes.comment (notable_type = “LineItem”)

Protocol Page “Non-clinical Services” tab Actions column “Notes”

Text

20

Documents

Uploaded electronic information

documents (documentable_type = “Fulfillment”)

Protocol Page “Non-clinical Services” tab Actions column “Documents”

Document

21

Fields Modified

Non Clinical service audit history as edited in SPARCRequest/SPARCDashboard

generated from versions.object_changes

N/A

N/A

22

Date

The date which fields have been modified

versions.created_at

N/A

N/A

23

Non-Clinical Services Total

The total cost of the Non-clinical Services. The sum of Total Cost of individual services. Each listed service is calculated once.

Calculated value

N/A

N/A

24

Total Cost after Subsidy

The total cost of Non-clinical Total after percent of Current Request Cost has been removed. Total Cost minus % Subsidy.

Calculated value

N/A

N/A