- Post History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on
12-14-2022
09:39 PM
- edited on
12-14-2022
10:14 PM
by
Mahesh Bhoothpo
This article is meant only for customers who are running the following plugins in a ServiceNow instance:
- "Alignment Planner Workspace" v4.1.1 or earlier version.
- "Alignment Planner Workspace with PPM, Agile 2.0 and SAFe" v3.01 or earlier versions.
- And, would like to upgrade to “Alignment Planner Workspace” v5.0.1 and “Alignment Planner Workspace with PPM, Agile 2.0 and SAFe” v4.0.0
Why do I need data migration?
The concepts of Planning Hierarchy and Planning Organization are obsoleted from “Alignment Planner Workspace” v5.0.1 onwards and the new concept of Lens structure has been introduced. Now, users will be able to use any table (Example: business_unit, cmn_department, pm_portfolio, core_company, or any platform table) as a planning organization in a Lens structure.
As part of the above change, after the data from your existing planning organization and platform tables has been migrated to the targeted tables, this migration script helps you to update the existing references of the Planning Organization to the targeted tables in the Strategic priority [sn_gf_strategy] and Goal [sn_gf_goal] tables.
When to perform this process?
The following steps are required to be completed before running this script to update the planning organization references in the Strategic priority and Goal tables:
- Ensure that the “Alignment Planner Workspace” application and all its dependent applications are upgraded successfully.
- Ensure that the Planning Hierarchies data is migrated to the Lens structure and the Planning items are mapped to the bottom entity of the lens. For information on how to migrate the data, see Migrate Planning Hierarchies to Lens Structure on upgrading to new Alignment Planner Workspace.
Note: This data migration process to update the planning organization references is performed for one Planning Organization type at a time.
How to perform data migration?
Pre-requisite: For the planning organization type that you want to migrate, ensure the Planning Hierarchies data is migrated to the Lens structure and the Planning items are mapped to the bottom entity of the lens. For information on how to migrate the data, see Migrate Planning Hierarchies to Lens Structure on upgrading to new Alignment Planner Workspace.
Run this script to update the existing references of Planning Organization to the targeted tables in the Strategic priority [sn_gf_strategy] and Goal [sn_gf_goal] tables.
The script considers the apw_core_po_table, migrated_to_table, strategic_priority_table_column as input and migrates the Planning Organization field for goals and strategic priority.
- Strategic priority: The script considers the Planning organization ID on the Strategic priority table and updates the input (strategic_priority_table_column) field with the migrated table’s reference.
For example, if a planning organization table (sn_align_core_business_unit) is migrated to business_unit table, the script populates the field provided in the strategic_priority_table_column input with the newly created business unit value. - Goal: The script populates one of the four fields (Business unit, Department, Portfolio, and Company) on the Goal form based on the existing reference value in the Planning Organization field. If there was no reference to one of these four fields, the script creates an m2m relationship between the goal and the targeted table.
/*
The below script takes inputs:
apw_core_po_table : The planning organization table that was migrated
migrated_to_table : The table where the new records were created (target table)
strategic_priority_table_column: The column on strategic Priority table that refers to "migrated_to_table"
and migrates the Planning Organization for Strategic priority and Goals
*/
(function () {
migratePoOnGoalsStrategicPriority();
function migratePoOnGoalsStrategicPriority() {
var apw_core_po_table = 'sn_align_core_department';
var migrated_to_table = 'cmn_department';
var strategic_priority_table_column = 'department';
var msg = validateInput(apw_core_po_table, migrated_to_table, strategic_priority_table_column)
if (msg)
gs.info(msg);
migratePoOnStrategicPrioirity(strategic_priority_table_column, apw_core_po_table);
migratePoOnGoals(migrated_to_table, apw_core_po_table);
}
function validateInput(apw_core_po_table, migrated_to_table, strategic_priority_table_column) {
var msg = '';
if (!gs.tableExists(apw_core_po_table)) {
msg = 'Table ' + apw_core_po_table + ' does not exist';
return msg;
}
if (!gs.tableExists(migrated_to_table)) {
msg = 'Table ' + migrated_to_table + ' does not exist';
return msg;
}
if (!gs.nil(strategic_priority_table_column)) {
var strategyRecord = new GlideRecord('sn_gf_strategy');
if (!strategyRecord.isValidField(strategic_priority_table_column)) {
msg = 'field name : ' + strategic_priority_table_column + ' is not a valid field in table sn_gf_strategy';
return msg;
}
var refFieldTableName = strategyRecord.getElement(strategic_priority_table_column).getRefRecord().getTableName();
if (gs.nil(refFieldTableName) || refFieldTableName !== migrated_to_table) {
msg = 'Reference field Type for column ' + strategic_priority_table_column + 'is not same as Execution PO table Name' + migrated_to_table;
return msg;
}
}
}
function migratePoOnStrategicPrioirity(strategic_priority_table_column, apw_core_po_table) {
var gr = new GlideRecord(apw_core_po_table);
var secQuery = gr.addJoinQuery('sn_gf_strategy', 'sys_id', 'planning_org_id');
secQuery.addCondition(strategic_priority_table_column, '=', '');
gr.addNotNullQuery('execution_entity_item');
gr.query();
while (gr.next()) {
var migrated_to_sys_id = gr.getValue('execution_entity_item');
var strategyRecords = new GlideRecord('sn_gf_strategy');
strategyRecords.addQuery('planning_org_id', gr.getValue('sys_id'));
strategyRecords.addNullQuery(strategic_priority_table_column);
strategyRecords.query();
strategyRecords.setValue(strategic_priority_table_column, migrated_to_sys_id);
strategyRecords.updateMultiple();
}
}
function migratePoOnGoals(migrated_to_table, apw_core_po_table) {
var gr = new GlideRecord(apw_core_po_table);
gr.addJoinQuery('sn_gf_goal', 'sys_id', 'planning_org_id');
gr.addNotNullQuery('execution_entity_item');
gr.query();
while (gr.next()) {
var migrated_to_sys_id = gr.getValue('execution_entity_item');
_updateGoalsRecords(gr, migrated_to_table, migrated_to_sys_id)
}
}
function _updateGoalsRecords(alignmentPlanningOrgGr, migrated_to_table, execution_entity_sysId) {
var gr = new GlideRecord(sn_gf.GoalFrameworkConstants.GOAL_TABLE);
gr.addQuery('planning_org_id', alignmentPlanningOrgGr.getUniqueValue());
if (sn_gf.GoalFrameworkConstants.ORG_TABLE_TO_FIELD_MAP[migrated_to_table]) {
gr.addNullQuery(sn_gf.GoalFrameworkConstants.ORG_TABLE_TO_FIELD_MAP[migrated_to_table])
gr.query();
//update the goals reference
gr.setValue(sn_gf.GoalFrameworkConstants.ORG_TABLE_TO_FIELD_MAP[migrated_to_table], execution_entity_sysId);
gr.updateMultiple();
} else {
gr.query();
while (gr.next()) {
if (!_m2mRelationshipExists(gr.getValue('sys_id'), execution_entity_sysId)) {
var goalRelationshipGr = new GlideRecord(sn_gf.GoalFrameworkConstants.GOAL_RELATIONSHIP_TABLE);
goalRelationshipGr.initialize();
goalRelationshipGr.setValue('goal', gr.getValue('sys_id'));
goalRelationshipGr.setValue('entity_id', execution_entity_sysId);
goalRelationshipGr.setValue('table_name', migrated_to_table);
goalRelationshipGr.insert();
}
}
}
}
function _m2mRelationshipExists(goalId, entityId) {
var goalRelationshipGr = new GlideRecord(sn_gf.GoalFrameworkConstants.GOAL_RELATIONSHIP_TABLE);
goalRelationshipGr.addQuery('goal', goalId);
goalRelationshipGr.addQuery('entity_id', entityId);
goalRelationshipGr.query();
return goalRelationshipGr.hasNext();
}
})()
