SPARCRequest Wiki
SPARCFulfillment Invoice Report Data Dictionary and Sample Report (v3.6.1)
This report details all services for which fulfillment information has been added by the service provider, based on the criteria selected for the report (i.e. all protocols by service or by specific protocol, etc). The protocol ID, service request ID, and study-specific info is included in this report, as well as the billing department information needed by the service provider. The service requested, service provider and quantity and pricing information is also displayed. The status of each service with fulfillment data is displayed even if it has not yet been invoiced.
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.
Clinical Services
No. | Column Name | Description | Database Name | Source | Type |
|---|---|---|---|---|---|
1 | Protocol ID (SRID) | System generated from SPARCRequest | protocols.sparc_id | Homepage, SRID column | INT(11) |
2 | Request ID | System generated from SPARCRequest | sub_service_requests.ssr_id | Homepage, SRID column | VARCHAR(255) |
3 | RMID | A unique numeric identifier that links a research study across multiple MUSC electronic research systems. | protocols.research_master_id | Homepage, RMID column | INT(11) |
4 | Short Title | An abbreviated form of the title used to identify your study/project | protocols.short_title | Homepage, Short Title column | VARCHAR(255) |
5 | Funding Source | System required field which indicates primary source of support for a study, which drives system pricing. | protocols.funding_source, protocols.potential_funding_source | SPARCDashboard, Protocol Information view. | VARCHAR(255) |
6 | Communication indicator which displays progress, determined by service provider workflow | sub_service_requests.status | Homepage, Status column | VARCHAR(255) | |
7 | Primary PI | Responsible person (aka Principal investigator) | project_roles.role = 'primary-pi' | Homepage, Principal Investigator column | VARCHAR(255) |
8 | Primary PI Affiliation | Institution, College, Department, Division of the responsible person | identities.professional_organization_id (professional_organizations.name) | SPARCDashboard, Profile Page, Affiliation | TEXT |
9 | Billing/Business Manager(s) | Person responsible for reviewing and paying invoices | project_roles.role = 'business-grants-manager' | SPARCDashboard Authorized Users Table | VARCHAR(255) |
10 | Core/Program | The level of organization in which the service belongs | services.organization_id | Provider/ Program/Core Column | VARCHAR(255) |
11 | Service | The name of the procedure or study level activity | fulfillments.service_name | Participant Tracker Page “Procedures” column | VARCHAR(255) |
12 | Service Completion Date | The date the procedure or study level activity is completed | procedures.completed_date | Participant Tracker page “Completed Date” column | DATETIME |
13 | Patient Name | Given first and last name of the participant. | participants.first_name + participants.last_name | Patient Registry page “Participant Name” column | VARCHAR(255) |
14 | Patient ID | Participant's Medical Record Number | participants.mrn | Patient Registry page “MRN” column | VARCHAR(255) |
15 | 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 |
|
16 | Visit Name | The name the protocol uses to identify a visit | appointment.name | Participant Tracker name of the selected visit | VARCHAR(255) |
17 | Visit Date | The date the visit was started | appointment.start_date | Date of Participant Visit | DateTime |
18 | Quantity Completed | The number of how many procedures or study level activities have been completed | count (procedures) | Participant Tracker Number of Services Fulfilled | (calculated) |
19 | Clinical Quantity Type | Unit of measurement for procedures and study level activities | pricing_maps.unit_type | SPARCCatalog clinical service pricing map | VARCHAR(255) |
20 | Research Rate | The fee associated to the procedure or study level activity with research discounts. | (procedures.service_cost)/100 | SPARCCatalog clinical service pricing map | INT(11)/100 |
21 | Total Cost | The quantity completed times the research rate or admin rate equals the total cost | calculated field | N/A | N/A |
22 | Modified Rate | Whether the Total Cost is a modified rate where the service provider determined cost (admin rate). Displays whether the rate has been modified (Y/N). | admin_rates (exist?) | SPARCDashboard Admin Edit, Modified Rate | N/A |
23 | Percent Subsidy | Percent of Current Request Cost to be covered by Service Provider | subsidies.percent_subsidy | Entered in SPARCRequest Step 3A Percent Subsidy | FLOAT |
24 | Invoiced | Flag indicating status of service level invoicing | procedures.invoiced | Participant Tracker page, Clinical Services section, Invoiced column | TINYINT |
25 | Invoiced Date | The date the procedure or study level activity is invoiced. | procedures.invoiced_date | Participant Tracker page, Clinical Services section, Invoiced Date column | DateTime |
26 | Non-Clinical and Clinical Services Total | The total cost of the one-time-fee (non-clinical) per-patient (clinical) services | calculated field | N/A | N/A |
27 | Total Cost After Subsidy | The total cost of Non-clinical and Clinical services Total after percent of Current Request Cost has been removed | calculated field | N/A | N/A |
Non-clinical Services
No. | Column Name | Description | Database Name | Source | Type |
|---|---|---|---|---|---|
1 | Protocol ID (SRID) | System generated from SPARCRequest | protocols.sparc_id | Homepage, SRID column | INT (11) |
2 | Request ID | System generated from SPARCRequest | sub_service_requests.ssr_id | Homepage, SRID column | VARCHAR(255) |
3 | RMID | Research Master ID created by study team | protocols.research_master_id | Homepage, RMID column | INT(11) |
4 | Short Title | An abbreviated form of the title used to identify your study/project | protocols.short_title | Homepage, Short Title column | VARCHAR(255) |
5 | Funding Source | System required field which indicates primary source of support for a study, which drives system pricing. | protocols.funding_source, protocols.potential_funding_source | SPARCDashboard, Protocol Information view. | VARCHAR(255) |
6 | Status | Communication indicator which displays progress, determined by service a provider workflow | sub_service_requests.status | Homepage, Status column | VARCHAR(255) |
7 | Primary PI | Principal investigator, Responsible person” or “Responsible person of a study | project_roles.role = primary-pi | Homepage, Principal Investigator column | VARCHAR(255) |
8 | Primary PI Affiliation | Institution, College, Department, Division of the responsible person | identities.professional_organization_id (professional_organizations.name) | SPARCDashboard, Profile Page, Affiliation | TEXT |
9 | Billing/Business Manager(s) | Person responsible for reviewing and paying invoices | project_roles.role = 'business-grants-manager' | SPARCDashboard Authorized Users Table | VARCHAR(255) |
10 | Core/Program | The level of organization in which the service belongs | services.organization_id | Provider/ Program/Core Column | VARCHAR(255) |
11 | Service | The name of the study level activity | fulfillments.service_name | Inside a protocol, Non-clinical Services Tab, “Service” column | VARCHAR(255) |
12 | Fulfillment Date | the date the clinical or non-clinical service is completed at | fulfillments.fulfilled_at | Non-clinical Services Tab, Fulfillment List, Fulfillment Date column | DATETIME |
13 | 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) |
14 | Components | The provider defined elements of a service | components.component | Inside a protocol, “Non-clinical Services” tab Components column | VARCHAR(255) |
15 | 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 | VARCHAR(255) |
16 | Contact | The person to contact regarding financial information related to a study | line_items.contact_name | Inside a protocol, “Non-clinical Services” tab Contact column | VARCHAR(255) |
17 | Account # | User Defined Accounting Key (i.e. UDAK) Number | line_items.account_number | Inside a protocol, “Non-clinical Services” tab Account column | VARCHAR(255) |
18 | Quantity Completed | The number of how many study level activities (fulfillment quantities) have been completed | sum (fulfillments.quantity) | Non-clinical Services Tab, Fulfillment List, “Quantity” Column | (calculated) |
19 | Quantity Type | Unit of measurement for Non-clinical services | pricing_maps.quantity_type | SPARCCatalog non-clinical service pricing map | VARCHAR(255) |
20 | Research Rate | The fee associated to the study level activity with research discounts. Includes whichever rate applies: service rate, funding discount rate, override rate or admin cost. | (fulfillments.service_cost)/100 | SPARCCatalog clinical service pricing map | INT(11)/100 |
21 | Total Cost | The quantity completed times the research rate equals the total cost. | calculated | N/A | N/A |
22 | Modified Rate | Whether the Total Cost is a modified rate where the service provider determined cost. Displays whether the rate has been modified (Y/N). | admin_rates (exist?) | SPARCDashboard Admin Edit, Modified Rate | N/A |
23 | Invoiced |
Copyright © 2011-2025 MUSC Foundation for Research Development