SPARCRequest Wiki
SPARCFulfillment Invoice Report Data Dictionary and Sample Report (v3.7.0)
This report details all clinical and non-clinical services for which fulfillment information has been added by the service provider, based on the criteria selected for the report (fulfilled services start/end dates, organizations (providers) and protocols (service requests)). Study-specific info is included in this report to support billing department needs.
Below are columns are included in queried results, as well as a sample report.
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 | Flag indicating status of service level invoicing |