SPARCFulfillment Finance Billing Report Data Dictionary and Sample Report (v3.6.1)

This report details all protocol and participant level services for which fulfillment information has been added by the service provider for ease in extracting this information for billing purposes. Output is based on reporting access and the criteria selected for the report (i.e. date filters, all organizations, all protocols). The service request and study-specific info is included in this report, as well as the pricing and billing information.

A sample report is included at the end of this data dictionary to demonstrate how report results appear. The data contains information for both clinical and non-clinical services. Data will be absent where a column is not applicable for either type of service.

Filter options include:

Start/End Date (required)
Organization (required)
Protocol (required)

Report output includes the chosen parameter Dates, Organizations (Selected or All) and Protocols (Selected or All) and the following columns:

Column #	Column Name	Description	Database Name	Source	Type

Column #	Column Name	Description	Database Name	Source	Type
1	RMID	A unique numeric identifier that links a research study across multiple MUSC electronic research systems.	SPARC protocols.research_master_id	SPARCRequest Protocol page RMID column Displayed on Fulfillment homepage, RMID column	INT(11)
2	Protocol ID (SRID)	System generated from SPARCRequest	SPARC protocols.sparc_id	SPARCRequest Protocol page SRID column Displayed on Fulfillment homepage, SRID column	INT(11)
3	Request ID	System generated from SPARCRequest	SPARC sub_service_requests.ssr_id	SPARCRequest Protocol page SRID column Displayed on Fulfillment homepage, SRID column	VARCHAR(255)
4	Status	Sub Service Request Status, displaying progress as determined by service provider workflow	SPARCRequest sub_service_requests.status	SPARCRequest Protocol page Status column Displayed on Fulfillment homepage, Status column	VARCHAR(255)
5	Short Title	An abbreviated form of the title used to identify the study/project	SPARC protocols.short_title	SPARCDashboard study Homepage Status column Displayed on Fulfillment homepage, Short Title column	VARCHAR(255)
6	Proposal Funding Status	Status of funding for the study. Indicates whether or not funding has been applied for or obtained for the project	SPARC protocols.funding_status	SPARCDashboard, Protocol Information Page	VARCHAR(255)
7	Funding Start Date	Date funding starts for the project	SPARC protocols.funding_start_date	SPARCDashboard, Protocol Information Page	DATETIME
8	Funding Source	System required field which indicates primary source of support for a study, which drives system pricing.	SPARC protocols.funding_source,	SPARCDashboard, Protocol Information Page.	VARCHAR(255)
9	Previous Funding Source	Previous funding source selected for the project before the change was made to the new, current source	SPARC audits.audited_changes	SPARC database, audits table	TEXT
10	Funding Source Change Date	The date of the funding source change	SPARC audits.audited_changes	SPARC database, audits table	TEXT
11	Primary PI	Responsible person (aka Principal investigator)	SPARC project_roles.role = 'primary-pi'	SPARCDashboard, Protocol Information Page Displayed on Fulfillment homepage, Principal Investigator column	VARCHAR(255)
12	Primary PI Affiliation	Institution, College, Department, Division of the responsible person	SPARC identities.professional_organization_id (professional_organizations.name)	SPARCDashboard, Profile Page, Affiliation	TEXT
13	Billing/Business Manager(s)	Person responsible for reviewing and paying invoices	SPARC project_roles.role = 'business-grants-manager'	SPARCDashboard Authorized Users Table	VARCHAR(255)
14	Core/Program	The level of organization in which the service belongs	SPARC services.organization_id	SPARCCatalog Provider/ Program/Core Setup SPARCFulfillment Participant Tracker (displays Core for services in participant appointments)	VARCHAR(255)
15	Service Type	Where the service is Clinical (per-patient per-visit) service or a Non-clinical (one-time fee) service	Non-clinical = fulfillments table Clinical = procedures table	SPARCCatalog One-Time Fee indication SPARCDashboard, Requests, View Services	N/A
16	Service	The name of the procedure or study level activity completed for any date.	fulfillments.service_name, service_id procedures.service_name, service_id	SPARCDashboard Study Admin Edit Clinical Services/Non-clinical Services Tabs SPARCFulfillment completed services within Participant Tracker Appointments and Non-clinical Services Tabs	VARCHAR(255)
17	Performed By	Name of the provider who performed the procedure or study level activity	fulfillments.performer_id	Non-clinical Services Tab, Fulfillment List, “Performed By” Column	VARCHAR(255)
18	Components	The provider defined elements of a non-clinical services	components.components	SPARCCatalog Service Level Components (for SPARCFulfillment) SPARCFulfillment, Protocol, Non-clinical Services Tab, Fulfillments list, Component column	VARCHAR(255)
19	Contact	The person to contact regarding financial information related to a study	line_items.contact_name	SPARCFulfillment, Protocol, Non-clinical Services Tab, Contact column	VARCHAR(255)
20	Account #	User Defined Accounting Key (i.e. UDAK) Number	line_items.account_number	SPARCFulfillment, Protocol, Non-clinical Services Tab, Account# column	VARCHAR(255)
21	Patient Name	Given first and last name of the participant.	participants.first_name + participants.last_name	Patient Registry page “Participant Name” column	VARCHAR(255)
22	Patient ID	Participant's Medical Record Number	participants.mrn	Patient Registry page “MRN” column	VARCHAR(255)
23	Visit Name	The name the protocol uses to identify a visit	appointment.name	Participant Tracker name of the selected visit	VARCHAR(255)
24	Visit Date	The date the visit was started	appointment.start_date	Date of Participant Visit	DateTime
25	Notes	System generated information and clinical provider entered short informal messages	notes.comment	System generated status, date change, and manually entered notes from participant tracker page	TEXT
26	Fulfillment/ Completion Date	The date the procedure or study level activity is completed	procedures.completed_date fulfillments.fulfilled_at	Participant Tracker page “Completed Date” column Non-clinical Services Tab, Fulfillment List, Fulfillment Date column	DATETIME
28	Fiscal Year	Fiscal Year (from July - June, as FY23, FY24, FY25, etc)	Derived from procedures.completed_date / fulfillments.fulfilled_at	Converted from Procedure / Fulfillment date of completion	N/A
29	Quantity Completed	The number of how many procedures or study level activities have been completed	count (procedures) count (fulfillment)	Participant Tracker Number of Services Fulfilled	N/A
30	Quantity Type	Unit of measurement for procedures and study level activities	SPARC pricing_maps.unit_type	SPARCCatalog clinical service pricing map	VARCHAR(255)
31	Research Rate	The fee associated to the procedure or study level activity with research discounts. Includes whichever rate applies: service rate, funding discount rate, override rate or admin cost.	(procedures.service_cost)/100 (fulfillments.service_cost)/100	SPARCCatalog clinical service pricing map	INT(11)/100
32	Total Cost	The quantity completed times the research rate or admin rate equals the total cost	calculated field	N/A	N/A
33	Modified Rate	Whether the Total Cost is a modified rate where the service provider determined the cost (admin rate). Displays whether the rate has been modified (Y/N).	SPARC admin_rates (exist?)	SPARCDashboard Admin Edit, Your Cost, Modified Rate	N/A
34	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
35	Invoiced	Flag indicating status of service level invoicing. “Yes” is displayed if any of Core/Program’s fulfilled/completed services have been invoiced	procedures.invoiced fulfillments.invoiced	Participant Tracker Appointments/Calendar “Invoiced” Column and Non-clinical Services tab Fulfillment List, “Invoiced” Column	TINYINT
36	Invoiced Date	The date the procedure or study level activity is invoiced.	procedures.invoiced_date fulfillments.invoiced_date	Participant Tracker page, Clinical Services section, “Invoiced Date” column Fulfillment List, “Invoiced Date” Column	DateTime

Example report: