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" )}))