Determine which users are not using the default UoM
Article ID: 417585
Updated On:
Products
Issue/Introduction
Which users are not using the default Unit of Measurement (UoM) general setting? What is the user’s personalization of the UoM setting? How can this be determined?
Environment
Clarity 16.4.0
Resolution
This information can be determined through a query to the Clarity database. Starting in Clarity 16.4.0 a new "Queries" module will allow you to run these queries through the MUX. These queries can also be run directly against the database as needed. The queries are provided for each db platform:
/* PostgreSQL
This query returns the Unit of Measure personalization setting per user, if they personalized it
*/
WITH u AS (
SELECT p.user_id,
LOWER(TRIM(p.personalizations::json->>'units')) AS units
FROM cmn_ui_personalizations p
WHERE p.component = 'general'
)
SELECT p.user_id,
r.unique_name AS resource_code,
r.full_name,
p.last_updated_date,
CASE
u.units
WHEN 'pct' THEN '% Availability'
WHEN 'hours' THEN 'Hours'
WHEN 'fte' THEN 'FTE'
WHEN 'days' THEN 'Days'
ELSE u.units
END AS units
FROM cmn_ui_personalizations p
JOIN srm_resources r ON r.user_id = p.user_id
JOIN u ON u.user_id = p.user_id
WHERE p.component = 'general'
AND u.units IN ('hours', 'fte', 'days', 'pct')
/* Oracle
This query returns the Unit of Measure personalization setting per user, if they personalized it
*/
SELECT p.user_id,
r.unique_name AS resource_code,
r.full_name,
p.last_updated_date,
CASE
WHEN u.units = 'pct' THEN '% Availability'
WHEN u.units = 'hours' THEN 'Hours'
WHEN u.units = 'fte' THEN 'FTE'
WHEN u.units = 'days' THEN 'Days'
ELSE u.units
END AS units
FROM cmn_ui_personalizations p
JOIN srm_resources r ON r.user_id = p.user_id
CROSS APPLY (
SELECT LOWER(
TRIM(
JSON_VALUE(
p.personalizations,
'$.units'
RETURNING VARCHAR2(20) NULL ON ERROR NULL ON EMPTY
)
)
) AS units
FROM dual
) u
WHERE p.component = 'general'
AND u.units IN ('hours', 'fte', 'days', 'pct')
/* MSSQL
This query returns the Unit of Measure personalization setting per user, if they personalized it
*/
SELECT p.user_id,
r.unique_name AS resource_code,
r.full_name,
p.last_updated_date,
CASE
WHEN u.units = 'pct' THEN '% Availability'
WHEN u.units = 'hours' THEN 'Hours'
WHEN u.units = 'fte' THEN 'FTE'
WHEN u.units = 'days' THEN 'Days'
ELSE u.units
END AS units
FROM cmn_ui_personalizations p
JOIN srm_resources r ON r.user_id = p.user_id
CROSS APPLY (
SELECT LOWER(TRIM(JSON_VALUE(p.personalizations, '$.units'))) AS units
) AS u
WHERE p.component = 'general'
AND u.units IN ('hours', 'fte', 'days', 'pct')
Feedback
