Runbook for Normalization Management of LocalCode HoldingID normalization in Ayvens silver.localvalue_t and silver.holdingvalue_t.
In Ayvens fleet management operations, each country affiliate (subsidiary) manages lease assets in local ERP and fleet systems using local terminology, vehicle model codes, fuel descriptions, and contract classifications. To enable consolidated risk assessment, European remarketing, group pricing analytics, and customer portal reporting, all local codes must be conformed into standardized enterprise holding values within silver.localvalue_t and silver.holdingvalue_t.
When a local subsidiary modifies a label, introduces a new vehicle derivative, or enters an invalid holding mapping, downstream conformed ETL jobs reject the records. This produces missing assets in Gold reports and triggers LMS normalization incidents.
This runbook defines the operational diagnostic procedures and automated SQL Saga remediation flows for all Use Case 1 (UC1) patterns.
| Pattern ID | Intent Label | Ticket Prefix | Root Cause Description | Remediation Type |
|---|---|---|---|---|
| Pattern 1 | AYVENS_LMS_VALUE_CHANGE |
S1-* |
Typo, localized spelling amendment, or label update on an already mapped active code. | UPDATE localvalue_t + INSERT localvalue_history_t |
| Pattern 2 | AYVENS_LMS_NEW_VALUE |
S2-* |
New make, model, fuel type, or contract option created in subsidiary without holding entry. | INSERT localvalue_t + INSERT localvalue_history_t |
| Pattern 3 | AYVENS_LMS_MAPPING_ERROR |
S3-* |
Local code has NULL HOLDINGID or points to an invalid/obsolete holding identifier. |
Verify holdingvalue_t + UPDATE localvalue_t + INSERT localvalue_history_t |
| Pattern 4 | AYVENS_LMS_DEACTIVATION |
S4-* |
Deprecated vehicle model or obsolete contract variant retiring from local catalog. | UPDATE localvalue_t SET STATUS = 'I' |
To ensure 100% data consistency, Zero-Trust compliance, and zero production regressions, all remediation workflows execute as a strict 4-phase Saga:
[Phase 1: Dry-Run Probe]
| (Non-destructive validation of target record state)
v
[Phase 2: Mutating Patch]
| (Targeted update or insert in `alenia-aiops-dev`)
v
[Phase 3: Audit Trail Log]
| (Mandatory historical insert into `localvalue_history_t`)
v
[Phase 4: Post-Verification Probe]
| (Validate patch state; if probe fails -> trigger Rollback Query)
v
[Compensating Rollback] (Executed automatically on any failure)
All SQL statements MUST target the execution sandbox catalog `alenia-aiops-dev`. Direct mutation of aiops_participants_synthethic_data or aiops_hackathon_dev_ai is prohibited and blocked by AST safety rules.
AYVENS_LMS_VALUE_CHANGE / S1-*)Subsidiary 'FR' amended the local description for local code 'VOLVO_XC60' from 'Volvo XC-60 D4' to 'Volvo XC60 B4 AWD'. The holding mapping is unchanged.
Execute non-destructive read to verify current row state and confirm the change is legitimate:
SELECT CLASSID, SUBSIDIARYCODE, LOCALCODE, LOCALVALUE, HOLDINGID, STATUS
FROM `alenia-aiops-dev`.silver.localvalue_t
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'FR'
AND LOCALCODE = 'VOLVO_XC60';
Expected Result: Exactly 1 row returned with LOCALVALUE = 'Volvo XC-60 D4'.
Update the localized label in the sandbox catalog:
UPDATE `alenia-aiops-dev`.silver.localvalue_t
SET LOCALVALUE = 'Volvo XC60 B4 AWD'
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'FR'
AND LOCALCODE = 'VOLVO_XC60';
Insert change record into the immutable history log:
INSERT INTO `alenia-aiops-dev`.bronze.localvalue_history_t
(CLASSID, SUBSIDIARYCODE, LOCALCODE, LOCALVALUE, HOLDINGID, STATUS, IDUSER, EXTRACTION_DATE)
VALUES
(27, 'FR', 'VOLVO_XC60', 'Volvo XC60 B4 AWD', 'H-MAKE-VOLVO', 'A', 'AIOPS-REMEDIATION-ENGINE', current_timestamp());
SELECT LOCALVALUE
FROM `alenia-aiops-dev`.silver.localvalue_t
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'FR'
AND LOCALCODE = 'VOLVO_XC60';
Assertion: Result must equal 'Volvo XC60 B4 AWD'.
If post-verification fails or regression alert triggers:
UPDATE `alenia-aiops-dev`.silver.localvalue_t
SET LOCALVALUE = 'Volvo XC-60 D4'
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'FR'
AND LOCALCODE = 'VOLVO_XC60';
AYVENS_LMS_NEW_VALUE / S2-*)Subsidiary 'DE' introduces a new hybrid vehicle model code 'BYD_SEAL_U' not present in localvalue_t. Holding reference 'H-MAKE-BYD' already exists in holdingvalue_t.
Verify code does not exist in localvalue_t and verify target holding value exists in holdingvalue_t:
-- Check 1: Confirm absence in local value table
SELECT COUNT(*) AS exists_count
FROM `alenia-aiops-dev`.silver.localvalue_t
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'DE'
AND LOCALCODE = 'BYD_SEAL_U';
-- Check 2: Confirm valid holding reference
SELECT HOLDINGID, NAME
FROM `alenia-aiops-dev`.silver.holdingvalue_t
WHERE CLASSID = 27
AND HOLDINGID = 'H-MAKE-BYD';
Gate: exists_count must be 0, and H-MAKE-BYD must exist.
INSERT INTO `alenia-aiops-dev`.silver.localvalue_t
(CLASSID, SUBSIDIARYCODE, LOCALCODE, LOCALVALUE, HOLDINGID, STATUS)
VALUES
(27, 'DE', 'BYD_SEAL_U', 'BYD Seal U DM-i Design', 'H-MAKE-BYD', 'A');
INSERT INTO `alenia-aiops-dev`.bronze.localvalue_history_t
(CLASSID, SUBSIDIARYCODE, LOCALCODE, LOCALVALUE, HOLDINGID, STATUS, IDUSER, EXTRACTION_DATE)
VALUES
(27, 'DE', 'BYD_SEAL_U', 'BYD Seal U DM-i Design', 'H-MAKE-BYD', 'A', 'AIOPS-REMEDIATION-ENGINE', current_timestamp());
SELECT STATUS, HOLDINGID
FROM `alenia-aiops-dev`.silver.localvalue_t
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'DE'
AND LOCALCODE = 'BYD_SEAL_U';
Assertion: Returns STATUS = 'A' and HOLDINGID = 'H-MAKE-BYD'.
DELETE FROM `alenia-aiops-dev`.silver.localvalue_t
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'DE'
AND LOCALCODE = 'BYD_SEAL_U';
AYVENS_LMS_MAPPING_ERROR / S3-*)Subsidiary 'NL' has local code 'TESLA_MDL_Y' configured with HOLDINGID = NULL and STATUS = 'S'.
When a local code is unmapped, identify the canonical holding ID by consulting peer subsidiary mappings for identical or similar local values:
SELECT HOLDINGID, COUNT(*) AS peer_frequency
FROM `alenia-aiops-dev`.silver.localvalue_t
WHERE CLASSID = 27
AND (LOCALCODE = 'TESLA_MDL_Y' OR LOCALVALUE LIKE '%Tesla Model Y%')
AND HOLDINGID IS NOT NULL
GROUP BY HOLDINGID
ORDER BY peer_frequency DESC
LIMIT 1;
Result: Yields target holding identifier 'H-MAKE-TESLA'.
Validate that the candidate holding ID exists in group master catalog:
SELECT COUNT(*)
FROM `alenia-aiops-dev`.silver.holdingvalue_t
WHERE CLASSID = 27
AND HOLDINGID = 'H-MAKE-TESLA';
Gate: Count must equal 1.
Update holding mapping and promote status from 'S' (Suggested) to 'A' (Active):
UPDATE `alenia-aiops-dev`.silver.localvalue_t
SET HOLDINGID = 'H-MAKE-TESLA',
STATUS = 'A'
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'NL'
AND LOCALCODE = 'TESLA_MDL_Y';
INSERT INTO `alenia-aiops-dev`.bronze.localvalue_history_t
(CLASSID, SUBSIDIARYCODE, LOCALCODE, LOCALVALUE, HOLDINGID, STATUS, IDUSER, EXTRACTION_DATE)
VALUES
(27, 'NL', 'TESLA_MDL_Y', 'Tesla Model Y Long Range', 'H-MAKE-TESLA', 'A', 'AIOPS-REMEDIATION-ENGINE', current_timestamp());
SELECT HOLDINGID, STATUS
FROM `alenia-aiops-dev`.silver.localvalue_t
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'NL'
AND LOCALCODE = 'TESLA_MDL_Y';
Assertion: Must return HOLDINGID = 'H-MAKE-TESLA' and STATUS = 'A'.
UPDATE `alenia-aiops-dev`.silver.localvalue_t
SET HOLDINGID = NULL,
STATUS = 'S'
WHERE CLASSID = 27
AND SUBSIDIARYCODE = 'NL'
AND LOCALCODE = 'TESLA_MDL_Y';
S4_DEACTIVATION)An obsolete lease product code 'OLD_LEASE_STD' in subsidiary 'BE' is retired.
-- Step 1: Deactivate local mapping
UPDATE `alenia-aiops-dev`.silver.localvalue_t
SET STATUS = 'I'
WHERE CLASSID = 18
AND SUBSIDIARYCODE = 'BE'
AND LOCALCODE = 'OLD_LEASE_STD';
-- Step 2: Audit log
INSERT INTO `alenia-aiops-dev`.bronze.localvalue_history_t
(CLASSID, SUBSIDIARYCODE, LOCALCODE, LOCALVALUE, HOLDINGID, STATUS, IDUSER, EXTRACTION_DATE)
SELECT CLASSID, SUBSIDIARYCODE, LOCALCODE, LOCALVALUE, HOLDINGID, 'I', 'AIOPS-REMEDIATION-ENGINE', current_timestamp()
FROM `alenia-aiops-dev`.silver.localvalue_t
WHERE CLASSID = 18 AND SUBSIDIARYCODE = 'BE' AND LOCALCODE = 'OLD_LEASE_STD';
-- Step 3: Rollback (if required)
UPDATE `alenia-aiops-dev`.silver.localvalue_t
SET STATUS = 'A'
WHERE CLASSID = 18
AND SUBSIDIARYCODE = 'BE'
AND LOCALCODE = 'OLD_LEASE_STD';
Upon successful execution of Phase 4 (Post-Verification Probe), the remediation worker registers the action in the incident audit log and updates the official solutions table:
INSERT INTO `alenia-aiops-dev`.solutions.solutions
(TICKET_ID, LAYER, TABLE_NAME, ERROR_TYPE, ERROR_COLUMN, PK_COLUMN, PK_VALUE, ACTION, EMAIL_RECIPIENT)
VALUES
('S3-NL-0082', 'silver', 'localvalue_t', 'unmapped_code', 'HOLDINGID', 'LOCALCODE', 'TESLA_MDL_Y', 'update_holding_id', 'support.netherlands@ayvens.com');
The Cockpit incident is updated with status RESOLVED and the verified execution duration and SQL statements are saved in the DiagnosisTrail lifecycle trace.