-- TMS KPI catalog seed — 11 new MKPI rows + matching MKPISTATUS RAG bands (3 per KPI), posted by -- TMSBLL.KpiPosting.TmsKpiPostingJob (a nightly Quartz job hosted in HRFinanceHost, mirroring -- FMBLL.AssetActivitySchedulerJob's own real per-tenant iteration — NOT the FFINANCE-hardcoded -- AnalyticsBLL.KPIEvaluationBLL engine, which this job bypasses entirely). -- -- IDs confirmed live-unused on GB5DEMO before this migration: -- MKPI: MIN(KPIID) across every existing row was -1799999887 — every id below is picked -- strictly more negative than that (-1799999900..-1799999910). -- MKPISTATUS: existing rows span -1799999877..-1799999718 (excluding the global -1/-1 "no band -- matched" sentinel row) — every id below (-1799999950 downward) sits safely outside -- that range. -- MODULEID -1399999902 = the real, live "Training" module row (confirmed via MMODULE). -- ACTUALTYPE = 1 (Calculated) deliberately — AnalyticsBLL.KPIEvaluationBLL.GetComputableKpis only -- evaluates ACTUALTYPE IN (0,3) rows via MKPI.ACTUALVALUEEXPRESSION; ACTUALTYPE=1 keeps these 11 -- rows fully invisible to that engine's own posting pass, so only TmsKpiPostingJob ever writes to -- them. DIMOUID/DIM1-5ID/HIERARCHY*/LEVEL* all take their DB-side -1 defaults (unmodified), -- matching every existing Finance KPI row's own convention. -- -- Every VALUEFROM/VALUETO band below is an illustrative starter threshold — tenant admins can -- retune via the existing MKPISTATUS admin UI without any code change. INSERT INTO MKPI (KPIID, KPINAME, DESCRIPTION, MODULEID, SECTION, MEASURETYPE, DESIREDDIRECTION, ACTUALTYPE, DATAFORMAT, CREATEDBYID, MODIFIEDBYID) VALUES (-1799999900, 'TMS Compliance Coverage %', 'Mandatory needs: target headcount vs. actual completed headcount.', -1399999902, 'Compliance', 0, 0, 1, '999.99', -1, -1), (-1799999901, 'TMS Nomination Approval TAT (Days)', 'Avg calendar days from nomination to HR approval.', -1399999902, 'Nomination', 0, 1, 1, '999.99', -1, -1), (-1799999902, 'TMS Cost per Trained Employee', 'Instance cost per participant, avg over employees completed in period.', -1399999902, 'Cost', 0, 1, 1, '999,999,999.99', -1, -1), (-1799999903, 'TMS Venue Utilization %', 'Avg enrolled headcount vs. venue max capacity for instances in period.', -1399999902, 'Utilization', 0, 0, 1, '999.99', -1, -1), (-1799999904, 'TMS Assessment Pass Rate %', 'Overall assessment pass rate, attempts completed in period.', -1399999902, 'Assessment', 0, 0, 1, '999.99', -1, -1), (-1799999905, 'TMS First-Attempt Pass Rate %', 'Assessment pass rate, first attempts only.', -1399999902, 'Assessment', 0, 0, 1, '999.99', -1, -1), (-1799999906, 'TMS Skill Upgrade Approval Rate %', 'Approved vs. Approved+Rejected skill upgrades reviewed in period.', -1399999902, 'Skill', 0, 0, 1, '999.99', -1, -1), (-1799999907, 'TMS Certificate Renewal Compliance %', 'Certs due (expiry <= period end) Active/RenewalInProgress vs. lapsed.', -1399999902, 'Certification', 0, 0, 1, '999.99', -1, -1), (-1799999908, 'TMS Expiring Certificates (30 Days)', 'Active certificates expiring within 30 days, as of today.', -1399999902, 'Certification', 0, 1, 1, '9,999', -1, -1), (-1799999909, 'TMS Feedback Response Rate %', 'Avg post-training evaluation response rate, instances in period.', -1399999902, 'Feedback', 0, 0, 1, '999.99', -1, -1), (-1799999910, 'TMS Learning Gain %', 'Avg pre/post-assessment score improvement, completions in period.', -1399999902, 'Effectiveness', 0, 0, 1, '999.99', -1, -1); INSERT INTO MKPISTATUS (KPISTATUSID, KPIID, SLNO, VALUEFROM, VALUETO, STATUS, SCORE, COLOR) VALUES -- TMS Compliance Coverage % (higher better) (-1799999950, -1799999900, 1, 0.00, 69.99, 1, 40, 0), (-1799999951, -1799999900, 2, 70.00, 89.99, 2, 60, 0), (-1799999952, -1799999900, 3, 90.00, 100.00, 3, 100, 0), -- TMS Nomination Approval TAT (Days) (lower better) (-1799999953, -1799999901, 1, 0.00, 3.00, 3, 100, 0), (-1799999954, -1799999901, 2, 3.01, 7.00, 2, 60, 0), (-1799999955, -1799999901, 3, 7.01, 999.00, 1, 40, 0), -- TMS Cost per Trained Employee (lower better — illustrative starter thresholds) (-1799999956, -1799999902, 1, 0.00, 5000.00, 3, 100, 0), (-1799999957, -1799999902, 2, 5000.01, 15000.00, 2, 60, 0), (-1799999958, -1799999902, 3, 15000.01, 999999999.00, 1, 40, 0), -- TMS Venue Utilization % (higher better) (-1799999959, -1799999903, 1, 0.00, 49.99, 1, 40, 0), (-1799999960, -1799999903, 2, 50.00, 79.99, 2, 60, 0), (-1799999961, -1799999903, 3, 80.00, 100.00, 3, 100, 0), -- TMS Assessment Pass Rate % (higher better) (-1799999962, -1799999904, 1, 0.00, 69.99, 1, 40, 0), (-1799999963, -1799999904, 2, 70.00, 89.99, 2, 60, 0), (-1799999964, -1799999904, 3, 90.00, 100.00, 3, 100, 0), -- TMS First-Attempt Pass Rate % (higher better) (-1799999965, -1799999905, 1, 0.00, 59.99, 1, 40, 0), (-1799999966, -1799999905, 2, 60.00, 84.99, 2, 60, 0), (-1799999967, -1799999905, 3, 85.00, 100.00, 3, 100, 0), -- TMS Skill Upgrade Approval Rate % (higher better) (-1799999968, -1799999906, 1, 0.00, 49.99, 1, 40, 0), (-1799999969, -1799999906, 2, 50.00, 79.99, 2, 60, 0), (-1799999970, -1799999906, 3, 80.00, 100.00, 3, 100, 0), -- TMS Certificate Renewal Compliance % (higher better) (-1799999971, -1799999907, 1, 0.00, 79.99, 1, 40, 0), (-1799999972, -1799999907, 2, 80.00, 94.99, 2, 60, 0), (-1799999973, -1799999907, 3, 95.00, 100.00, 3, 100, 0), -- TMS Expiring Certificates (30 Days) (lower better) (-1799999974, -1799999908, 1, 0.00, 5.00, 3, 100, 0), (-1799999975, -1799999908, 2, 6.00, 15.00, 2, 60, 0), (-1799999976, -1799999908, 3, 16.00, 999999.00, 1, 40, 0), -- TMS Feedback Response Rate % (higher better) (-1799999977, -1799999909, 1, 0.00, 39.99, 1, 40, 0), (-1799999978, -1799999909, 2, 40.00, 69.99, 2, 60, 0), (-1799999979, -1799999909, 3, 70.00, 100.00, 3, 100, 0), -- TMS Learning Gain % (higher better) (-1799999980, -1799999910, 1, 0.00, 9.99, 1, 40, 0), (-1799999981, -1799999910, 2, 10.00, 24.99, 2, 60, 0), (-1799999982, -1799999910, 3, 25.00, 100.00, 3, 100, 0);