VPA Groups Use Case: Bridge Tables (Service Contracts example)

Restricting by SLA

An equipment item can have different service contracts . For example, .an MRI machine might have a different contract for:

  • routine preventive maintenance (monitoring helium level and refilling as needed)
  • system administration (updating the software that runs the MRI)
  • on demand work (swapping out the MRI coil as the imaging has degraded).

When entering or updating data based on a change to a service contract, the account manager for the service provider changes the SLAs governed by that contract.

The Archibus schema relates work to service contract by these relationships:

  • Work Requests have an Equipment Code (eq_id) and an Activity Type (activity_type).
  • SLAs that govern those work orders have SLA Request Parameters (helpdesk_sla_request) with Equipment Code (eq_id) and Activity Type (activity_type)
  • SLAs that govern those work orders also have SLA Response Parameters (helpdesk_sla_response) with Activity Type (activity_type), and Ordering Sequence (ordering_seq)
  • SLA Response Parameters have Service Contracts (servcont.servcont_id).

You can set up service contracts by using a series of tables as bridge tables.

VPA Groups to Service Contracts Mapping Table (vpa_servcont)

VPA Group ID Service Contract ID
BOSTON-BIOMEDICAL 3332342
BOSTON-BIOMEDICAL 3332445
BOSTON-DIAGNOSTICS 3332888
BOSTON-FACILITIES 9234233
BOSTON-FACILITIES 9234234
BOSTON-MEDWASTE 1013411

Restrictions Table (vpa_rest)

Validating Table Type Query
eq Explicit Query

(EXISTS (SELECT 1 FROM ${sql.mainTable}, helpdesk_sla_request, helpdesk_sla_response, vpa_servcont
WHERE ${sql.mainTable}.eq_id = helpdesk_sla_request.eq_id AND ${sql.mainTable}.activity_type=helpdesk_sla_request.activity_type AND
helpdesk_sla_request.activity_type=helpdesk_sla_request.activity_type AND
helpdesk_sla_request.ordering_seq = helpdesk_sla_response.ordering_seq AND
${getVpaGroupsRestrictionForBridgeTable( "servcont", "helpdesk_sla_response" )}))