Reference Sheets
A reference sheet is a user-configured schedule that can be used to support configuration rules that rely on dynamic logic.
The business need is the availability of relatively large amounts of records in a user-defined structure that can be searched in dynamic logic, for example a function. As an example take a payment function (type of a reimbursement method) where the allowed amount is determined by a mathematical function rather than a predefined contracted rate. When applicable, payment functions can use reference sheets to incorporate lists of weights and indices used in the calculation.
To appreciate the applicability of the payment function reimbursement method, consider the following scenario:
Anesthesia is reimbursed in 15 minute units. Separate from the charged units, each individual anesthesia service code corresponds to a 'base' units value. The number of base units is listed, per individual Service Code, in a look-up table. To determine the claim line allowed amount, the sum of the base unit value and the number charged units is multiplied by the anesthesia fee.
The following configuration example implements the anesthesia scenario described earlier. First, a sheet that holds base unit values is set up:
ANESTHESIA_BASE_UNITS
Service Code | Start Date | End Date | Supplement |
---|---|---|---|
00100 |
01-01-2012 |
12-31-2012 |
5 |
00102 |
01-01-2012 |
12-31-2012 |
6 |
00103 |
01-01-2012 |
12-31-2012 |
5 |
… |
… |
… |
… |
ANESTHESIA_FEES
Contract Reference | Start Date | End Date | Fee |
---|---|---|---|
23432 |
01-01-2012 |
12-31-2012 |
100 |
24234 |
01-01-2012 |
12-31-2012 |
90 |
25455 |
01-01-2012 |
12-31-2012 |
105 |
… |
… |
… |
… |
Note that only the first three lines in the sheet are shown; typically reference sheets hold hundreds of lines.
Reference Sheet Configuration
The columns on a reference sheet are defined by dynamic record definition (for more detail see the "Extensibility" chapter in the Developer Guide). In addition, a reference sheet can be configured to have an embedded start / end date for each line in the sheet. The following settings control how the reference sheet can be accessed:
- The Usage Name
-
The name that is used by interfaces and dynamic logic scripts. The name can be specified in mixed case, to support the coding conventions in use.
- The Display Name
-
The label of the reference sheet as shown in the user interface (specifically the name of the tab)
- The Resource Name
-
The name that is used in the url
This leads to the following configuration settings:
Reference Sheet
Field | Description |
---|---|
Dynamic Record Definition |
The dynamic record definition that defines the structure |
Usage Name |
The usage name of the reference sheet |
Display Name |
The display name of the reference sheet |
Resource Name |
The name that is used in the url |
Indicator Time Validity |
Does the reference sheet use start and end dates? |
Usage Name and Resource Name should not be same |
Reference sheets contain the settings that specify how its lines are structured and how they can be accessed (identified). The actual data - the reference sheet lines - are stored in a detail table that has a somewhat different technical structure to provide:
-
Support for large numbers of reference sheet lines
-
Possibility to define a composite unique key without restriction on the datatype of a key field (for more detail see the "Extensibility" chapter of the Developer Guide)
-
Audit-ability of reference sheet lines
-
Search capability on reference sheet lines.
The structure is:
Reference Sheet Line
Field | Description |
---|---|
Reference Sheet |
The reference sheet to which this line belongs |
Key |
The concatenation of all key attributes. If no attributes are configured as key, this becomes purely a technical attribute |
Payload |
The payload consisting of all configured dimensions |
Start Date |
The start date of the reference sheet line |
End Date |
The end date of the reference sheet line |
A separate user interface is provided to maintain both the reference sheet definitions and the actual data.
For the example above, a reference sheet pointing to dynamic record definition 'ANESTHESIA_BASE_UNITS' could be defined with usage name 'supplements', display name 'Anesthesia Supplements' and time validity indicator checked. The dynamic record definition 'ANESTHESIA_BASE_UNITS' would consist of flex code field usages for 'procedureCode' and 'supplement'. Likewise another reference sheet 'ANESTHESIA_FEES' is defined with usage name 'fees'.
Quite often reference sheets contain fields that are sent in from external systems and are already set up as existing tables in the application. Examples are procedure groups, diagnosis groups, countries, modifiers and location types. The need arises to make use of the contents of such existing tables. In general there are two ways of doing this:
-
Pick List with validation
A flex code field usage that points to a flex code definition will automatically support an LOV that validates the field, which means that it is impossible to enter a value that is not part of the LOV. In the example above, the field procedureCode could point to a procedure flex code definition, for example a single definition like CPT_CODES or a flex code set like DRG_CODES or just PROCEDURES.
-
Pick List without validation
A flex code field usage that points to a character field can be configured to provide an LOV that does not validate the field, which means that the LOV can be used to pick a value from, but it is also possible to enter a value that is not part of the list. In the example above, the field contractReferences could point to the table Contract References. The LOV that is shown is the same LOV that is used in other pages - like the Claims pages - with the same search functionality. Note that this is generic functionality for dynamic record definitions that the reference sheets make use of. The list of tables that can be chosen as a pick list is seeded and consists of:
-
Authorization forms
-
Baskets
-
Claim forms
-
Claim form types
-
Contract references
-
Countries
-
Country region groups
-
Country regions
-
Currencies
-
Diagnosis groups
-
Diagnosis types
-
Financial transaction sets
-
Funding arrangements
-
Location type groups
-
Location types
-
Modifiers
-
Procedures
-
Procedure groups
-
Product families
-
Product lines
-
Provider groups
-
Service types
-
Specialties
The way the data can be accessed is explained in the next section.
Accessibility through Dynamic Logic
To allow for a controlled way of accessing reference sheets, for each piece of dynamic logic one can configure the reference sheets that should be accessible.
This cross reference table has the following structure:
Dynamic Logic Reference Sheet
Field | Description |
---|---|
Dynamic Logic |
The dynamic logic |
Reference Sheet |
The reference sheet |
There are two reasons for linking reference sheets:
-
For example, when migrating a function dynamic logic, the migration function provides the option to automatically include the accompanying reference sheets and reference sheet data.
-
Some pages (specifically the provider pricing clause set up page) generate additional tabs where the configuration user can add context specific reference sheet entries. The display name of these tabs comes from the display name that is configured in the reference sheet: for each reference sheet linked to the dynamic logic a tab is generated.
For the example above, the reference sheet can be accessed as follows in 'ANESTHESIA_CALCULATION':
def search = new SearchBuilder(ReferenceSheetLine.class).join("supplements") search = search.by("procedureCode").eq(claimLine.procedure.code) search = search.and().by("startDate").lte(claimLine.priceInputDate) search = search.and().group().by("endDate").eq(null) search = search.or().by("endDate").gte(claimLine.priceInputDate).end() def sups = search.execute() search = new SearchBuilder(ReferenceSheetLine.class).join("fees") search = search.by("contractReference").eq(providerPricingClause.contractReference.code) search = search.and().by("startDate").lte(claimLine.priceInputDate) search = search.and().group().by("endDate").eq(null) search = search.or().by("endDate").gte(claimLine.priceInputDate).end() def rates = search.execute() def sup = sups.size() > 0 ? sups.get(0) : null def rate = rates.size() > 0 ? rates.get(0) : null return Money.create((claimLine.priceInputNumberOfUnits + (sup?.payloadValues?.supplement ?:0)) * (rate?.payloadValues?.fee?:0))
Usage in Payment Functions
The last part of the setup is to reference the configured dynamic logic. In the example above, this means that the function dynamic logic 'ANESTHESIA_CALCULATION' is referenced by the payment function 'ANESTHESIA' that is used in a provider pricing clause. For this to work, the function dynamic logic 'ANESTHESIA_CALCULATION' should have the right signature, that is 'PaymentFunction'.
Using Reference Sheets in Payment Functions is a special case, as overrides can be defined on a provider pricing clause which is why the provider pricing clause is passed in the Groovy binding.
The override reference sheet can be accessed as:
def overrides = providerPricingClause.referenceSheets.supplements
A complete example:
def overrides = providerPricingClause.referenceSheets.supplements Closure criteria = { it -> it.payloadValues.procedureCode == claimLine.procedure.code && it.startDate <= claimLine.priceInputDate && (it.endDate == null || it.endDate >= claimLine.priceInputDate) } def sup = overrides.find(criteria) if (sup == null) { // No override found def search = new SearchBuilder(ReferenceSheetLine.class).join("supplements") search = search.by("procedureCode").eq(claimLine.procedure.code) search = search.and().by("startDate").lte(claimLine.priceInputDate) search = search.and().group().by("endDate").eq(null) search = search.or().by("endDate").gte(claimLine.priceInputDate).end() sups = search.execute() sup = sups.size() > 0 ? sups.get(0) : null } def search = new SearchBuilder(ReferenceSheetLine.class).join("fees") search = search.by("contractReference").eq(providerPricingClause.contractReference.code) search = search.and().by("startDate").lte(claimLine.priceInputDate) search = search.and().group().by("endDate").eq(null) search = search.or().by("endDate").gte(claimLine.priceInputDate).end() def rates = search.execute() def rate = rates.size() > 0 ? rates.get(0) : null return Money.create((claimLine.priceInputNumberOfUnits + (sup?.payloadValues.supplement ?:0)) * (rate?.payloadValues.fee ?:0))
Please note that 'like' searches on flexcode based columns in a reference sheet do not support escape sequence. |