This topic contains the list of changes to the database schema file since ARCHIBUS 11 Revision 1. This information is provided for the benefit of users with customized schemas so they can see if they need to migrate any changes from the released schema to their own schema. Sites using the standard ARCHIBUS schema do not need this information.
Even sites with custom schema might not need this information. New versions of ARCHIBUS are backward-compatible with previous schema formats. You can review this information if you need some new database object on which a new desired feature depends. For instance, you need to include only the BOMA 96 enumeration changes if you need the BOMA 96 calculations.
Since database schema versions can run with different releases of the ARCHIBUS program, databases are tagged with an "ARCHIBUS Database Version" number. View your ARCHIBUS Database version by checking the ARCHIBUS Schema Preferences table (scmpref.avw).
Schema Preferences:
- Update Db Version Date to: 2014.09.18
- Update Db Version number to: 140
______________________________
SCHEMA CHANGES
System:
-SCHEMA - Table type inconsistency for ARCHIBUS tables ( 3043386 )
UPDATE afm_tbls SET table_type='APPLICATION DICTIONARY' WHERE table_name in ('afm_bim_categories', 'afm_bim_param');
UPDATE afm_tbls SET table_type='PROCESS NAVIGATOR' WHERE table_name in ('afm_tasks', 'afm_subtasks');
-SCHEMA, FILES - Create an index on afm_metric_trend_values for fields 'metric_date', 'collect_group_by' ( 3042579 )
-SCHEMA - Correct the definition of view activity_log_hactivity_log( 3045021 )
Metrics:
-SCHEMA Add fields 'values_remove_older_than' and 'values_remove_method' to afm_metric_definition ( 3043301 )
-SCHEMA - METRICS - Create archive table afm_hmetric_trend_values, in order to archive old metric values( 3043313 )
-SCHEMA - Greek-ing metrics titles ( 3042918 )
Connectors:
-SCHEMA - Add tables/fields/wfr/connector data ( 3044625 )
Real Estate Portfolio Management:
-SCHEMA - SCHEMA - Widen the field size of gp.name to 64( 3045365 )
UPDATE afm_flds SET afm_size=64 WHERE table_name='gp' AND field_name='name';
-SCHEMA - Changes for Portfolio Forecast Wizard ( 3044649 )
Lease Administration:
-SCHEMA - Create new table 'ls_contacts' for lease multiple contacts ( 3044759 )
-SCHEMA - Follow up KB 3043753 - allow null value for lessor_id field in ta_lease table ( 3045002 )
UPDATE afm_flds SET allow_null=1 WHERE table_name='ta_lease' and field_name='lessor_id';
Government Real Property:
-SCHEMA - MS SQL - ORCL - Add missing fields for view ab-rplm-gvmnt-rp-add-new-prop.axvw. ( 3042646 )
Capital Project Management
-SCHEMA - Set afm_flds.max_val=9 for project.priority ( 3045515 )
UPDATE afm_flds set max_val=9 where table_name='project' and field_name='priority';
Space Planning & Management:
-SCHEMA - Adding Date Last Surveyed to the rm table ( 3044922 )
-SCHEMA - Remove the AUTOINCREMENT for pct_id in hrmpct table ( 3043869 )
Move Management:
-SCHEMA - Update ml_heading for fields date_app_mgr2 and date_app_mgr3 ( 3043493 )
UPDATE afm_flds SET ml_heading = 'Date Manager 2 Approved' WHERE table_name = 'mo' AND field_name = 'date_app_mgr2';
UPDATE afm_flds SET ml_heading = 'Date Manager 3 Approved' WHERE table_name = 'mo' AND field_name = 'date_app_mgr3';
Asset Management:
-SCHEMA - add new redline document fields (survey_redline_eq) to tables eq and eq_audit ( 3044834 )
-SCHEMA - YDim field types do not have ft/M appended to their titles( 3044814 )
SET numeric_format TO length FOR standard depth
UPDATE afm_flds SET num_format=4 WHERE table_name='eqstd' AND field_name='spec_depth';
Building Operations:
-SCHEMA - set minimum values to zero for Building Operations costs fields ( 3044373 )
-SCHEMA - add new table for Building Operations: cf_work_team ( 3044606 )
Energy Management:
-SCHEMA - Add tables and fields for BAS / sub-meter analytics features ( 3044921 )
-SCHEMA - Add indexes for Energy sub-metering analytics / BAS reports ( 3045295 )
IF EXISTS (SELECT 1 FROM sysindex WHERE index_name='bas_data_clean_num_comboIndex') DROP INDEX bas_data_clean_num.bas_data_clean_num_comboIndex;
CREATE INDEX bas_data_clean_num_comboIndex ON bas_data_clean_num (date_measured, time_measured, process_status);
IF EXISTS (SELECT 1 FROM sysindex WHERE index_name='bas_data_time_norm_num_comboIndex') DROP INDEX bas_data_time_norm_num.bas_data_time_norm_num_comboIndex;
CREATE INDEX bas_data_time_norm_num_comboIndex ON bas_data_time_norm_num (date_measured, time_measured, interval);
-SCHEMA / PNAV / DATA - Changes to support Energy Management Easy On-Ramp ( 3045302 )
MSDS:
-SCHEMA - For Bali3: MSDS data - url field length expansion needed ( 3044910 )
UPDATE afm_flds SET afm_size = 512 WHERE table_name = 'msds_data' AND field_name = 'url';
UPDATE afm_flds SET afm_size = 512 WHERE table_name = 'msds_h_data' AND field_name = 'url';
Workplace Services Portal:
-SCHEMA - Add new field to Work_team table ( 3044603 )
-SCHEMA - Enable auto-closing completed Service Requests and Work Requests ( 3044286 )
HelpDesk / ServiceDesk:
-SCHEMA - create new table hhelpdesk_step_log ( 3044604 )
Hoteling:
-SCHEMA, DATA - changes to support check-in feature for Hoteling ( 3043986 )
Reservations:
-SCHEMA - Database changes and new fields for Reservations, regarding Attendees.( 3044099 )
-SCHEMA - Database required changes for Reservations, regarding Attendees fields and Attendees Message ( 3044100 )
-SCHEMA - Reservations - Conference Calls - database changes required ( 3044911 )
-SCHEMA - View resview definition seems missing fields outlook_unique_id, occurrence_index ( 3045172 )
DELETE FROM afm_flds where field_name = 'outlook_unique_id' and table_name='resview'
Mobile:
-SCHEMA - Mobile Maintenance Manager application - add new tables and fields ( 3044292 )
-SCHEMA - New single line heading definitions for 21.2 Mobile updates ( 3043267 )
-SCHEMA - Mobile Space Occupancy application 21.3 enhancements - add new table and fields ( 3044530 )
-SCHEMA - Add Questionnaire schema additions required for Mobile Assessments ( 3044951 )
UPDATE afm_flds SET allow_null=1 where table_name='activity_log_sync' and field_name='questionnaire_id';
-SCHEMA - update rmpctmob_sync.pct_id field to accept values from rmpct.pct_id and also values like ‘MobileId-1’ ( 3044983 )
UPDATE afm_flds SET data_type=1 WHERE table_name='rmpctmob_sync' AND field_name='pct_id';
-SCHEMA - Mobile Space Occupancy application 21.3 enhancements - add new fields( 3044720 )
-SCHEMA - Match the field size for eq_audit.mob_locked_by field size to that of the field afm_users.user_name (64) ( 3043212 )
UPDATE afm_flds set afm_size=64 where field_name='mob_locked_by';
GIS:
-SCHEMA - Modify schema to support Extensions for ArcGIS ( 3044264 )
-SCHEMA - Update schema to support standard and enhanced mapping ( 3044632 )
-SCHEMA - Update schema to support standard and enhanced mapping ( 3044632 )
Add lat, lon, and geo_objectid to activity_log_hactivity_log
-SCHEMA - VIEWS - lat, lon, and geo_objectid fields are missing from the wrhwr sql view ( 3045046 )
Drawings:
-SCHEMA - Correct dwgname String Format values for all tables where used ( 3045548 )
UPDATE afm_flds SET string_format=10 where field_name='dwgname' and string_format <> 10;
Localization:
- To get the new fields from the last update into afm_flds_lang run:
INSERT INTO afm_flds_lang (table_name, field_name)
SELECT table_name, field_name FROM afm_flds
WHERE NOT EXISTS (SELECT 1 FROM afm_flds_lang afm_flds_lang_inner
WHERE afm_flds_lang_inner.table_name = afm_flds.table_name
AND afm_flds_lang_inner.field_name = afm_flds.field_name )
______________________________
_
WFR CHANGES:
Metrics:
-WFR definition for delete/archive scheduled WFR ( 3043301 )
Connectors:
-WFR - ConnectorHandler WFR is deprecated with addition of ConnectorService. ( 3045145, 3045457 )
DELETE FROM afm.afm_wf_rules WHERE rule_id = 'ConnectorHandler';
Building Operations:
-WFR add workflow rule BldgopsPartInventoryService ( 3044607 )
Energy Management:
-WFR - For Bali3: Add new WFR entry required for BAS / submeter analytics ( 3044956 )
-WFR - New WFR required for Energy - BAS process data ( 3045314 )
GIS:
-WFR - Add new WFR for ArcGIS Services ( 3043213 )
-WFR - Add Workflow Rules for the Extensions for ArcGIS ( 3044662 )
Page Navigation:
-WFR - Register a workflow rule ( 'AbSystemAdministration', 'getPageNavigationPublishedDates' ) needed for Page Navigation Publishing ( 3043993 )
-WFR - Register the workflow rule ( 'AbSystemAdministration','updateOrderOfMyFavorites' ) to allow favorites reordering in one transaction from Page Navigation ( 3043897 )
-WFR - Remove rule AbCommonResources - getDataRecordsPlusProjectInfo from canonical database ( 3043590 )
DELETE FROM afm_wf_rules WHERE activity_id = 'AbCommonResources' AND rule_id = 'getDataRecordsPlusProjectInfo';
_____________________________________________
PNAV CHANGES
System:
-PNAV - Assign AbRiskEHS Activity to TRAM to enable Mobile ( 3045194 )
INSERT INTO afm_roleprocs(role_name,activity_id,process_id) VALUES ('2 - WORKFLOW PROCESS (ACP)', 'AbRiskEHS', 'EHS - Incident Reporting');
-PNAV - Add the Scheduled Workflow Rules view to the System Administration / Add-In Manager process ( 3045322 )
UPDATE afm_ptasks SET task_id='Scheduled Workflow Rules', task_file='ab-wf-rules-scheduled.axvw' where task_id = 'Use Workflow Process Monitor';
DELETE FROM afm_ptasks WHERE task_file = 'ab-running-rules-list-ui.axvw';
-PNAV - Replace "Assign Processes to Roles" and "Assign Processes to Users" with new task "Assign Processes to Roles or Users" ( 3044179 )
UPDATE afm_ptasks SET task_id='Assign Processes to Roles or Users', task_file='ab-sys-role-user-procs.axvw'
WHERE activity_id='AbSystemAdministration' AND process_id='ARCHIBUS Administrator - UserSec' AND task_id='Assign Processes to Roles';
DELETE FROM afm_ptasks WHERE activity_id='AbSystemAdministration' AND process_id='ARCHIBUS Administrator - UserSec'AND task_id='Assign Processes to Users';
Real Estate Portfolio Management:
-PNAV - PNav and User updates for new 21.3 Portfolio Forecasting Console( 3044647 )
-PNAV - Update the task_file for Portfolio Forecast Wizard in the PNav ( 3044725 )
UPDATE afm_ptasks SET task_file='ab-alloc-wiz.axvw' where activity_id='AbRPLMGroupSpaceAllocation' and process_id='Portfolio Forecasting' and task_id='Portfolio Forecast Wizard' and task_file='ab-alloc-group.axvw';
Lease Administration:
-PNAV - Reword the PN task to match view name: Leases Expiring within 90 Days ( 3045074 )
UPDATE afm_ptasks SET task_id='View Leases Expiring within 90 Days' where task_id = 'View Leases About to Expire Within 90 Days';
Capital Project Management:
-PNAV - CPM Home page change to view file name ( 3042608 )
UPDATE afm_ptasks SET task_file = 'ab-proj-fcpm-invs.axvw' where task_id='Invoices by Vendors' AND task_file = 'ab-proj-fcpm-invs-rcd.axvw';
Space Planning & Management:
-PNAV - Add "Locate Employee" to the Client process within Personnel & Occupancy ( 3044810 )
-PNAV- Change the PNAV to have the Space Console appear first in the display order ( 3043004 )
Move Management:
-PNAV - Add a “Define Problem Types” view to the 'Business Process Owner' process for Move Management ( 3043420 )
INSERT INTO afm_ptasks (display_order, icon_small, is_hotlist, task_file,task_type, activity_id, process_id, task_id)
VALUES (150, 'ab-icon-task.gif', 0, 'ab-probtype-edit.axvw', 'WEB URL', 'AbMoveManagement', 'Business Process Owner', 'Define Problem Types');
Asset Management:
-PNAV - add entries for Asset Management new views ( 3043497 )
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file,icon_small)
VALUES('AbAssetManagement','Equipment','-',1300,'LABEL', NULL, 'ab-icon-task-label.gif') ;
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file,icon_small)
VALUES('AbAssetManagement','Equipment','View and Edit Lessors',1400,'WEB URL', 'ab-eq-lessor-edit.axvw', 'ab-icon-task-console.gif') ;
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file,icon_small)
VALUES('AbAssetManagement','Equipment','View and Edit Leases',1500,'WEB URL', 'ab-eq-lease-edit.axvw', 'ab-icon-task-console.gif') ;
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file,icon_small)
VALUES('AbAssetManagement','Equipment','View Equipment by Lease',1600,'WEB URL', 'ab-eq-eq-by-lease.axvw', 'ab-icon-task-rpt3.gif') ;
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file,icon_small)
VALUES('AbAssetManagement','Equipment','View Lease by Lessor',1700,'WEB URL', 'ab-eq-lease-by-lessor.axvw', 'ab-icon-task-rpt3.gif') ;
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file,icon_small)
VALUES('AbAssetManagement','Equipment','View Leases About to Expire Within 90 Days',1800,'WEB URL', 'ab-eq-lease-expire-90-days.axvw', 'ab-icon-task-rpt3.gif') ;
-PNAV - Update the title for the views 'ab-eq-eq-by-lease.axvw' and 'ab-eq-lease-by-lessor.axvw' ( 3045069 )
UPDATE afm_ptasks SET task_id='View and Assign Equipment by Lease' WHERE task_file='ab-eq-eq-by-lease.axvw';
UPDATE afm_ptasks SET task_id='View and Assign Leases by Lessor' WHERE task_file='ab-eq-lease-by-lessor.axvw';
Building Operations:
-PNAV - add new views to Building Operations processes ( 3044406 )
-PNAV - Create new processes for use with the new Building Operations Console in the HQ - Canonic database ( 3043612 )
-PNAV - corrections to Building Ops Console processes ( 3044761 )
DELETE FROM afm_processes WHERE process_id IN ('BPO BldgOpsConsole', 'Maintenance BldgOpsConsole');
Energy Management:
-PNAV - Add PNav entries for Energy BAS / sub-metering features ( 3044978 )
Workplace Services Portal:
-PNAV - Add two tasks to the System Administration / Mobile Apps Manager process to support Mobile Workplace Services Portal ( 3044944 )
Hoteling:
-PNAV - Add Confirm Bookings view to navigation.( 3044897 )
Mobile:
-PNAV - PNav Items for Questionnaires for Mobile Assessment Application ( 3044967 )
GIS:
-PNAV - Update PNAV for GIS Views for v21.3 ( 3044272 )
-PNAV - Assign Extensions for ArcGIS (SC-CAD) Process to AFM( 3044865 )
-PNAV - Add PNAV tasks for the Extensions for ArcGIS ( 3044958 )
-PNAV - Add PNAV Processes & Tasks for Extensions for ArcGIS - Smart Client Extension ( 3044664 )
HTML5 Charting:
-PNAV - Add HTML5 charting process navigator entries to Hq sample database ( 3044407 )
-PNAV - Add HTML5/SVG Drawing entries to Process Navigator ( 3044427 )
Drawing control:
-PNAV - Add new drawing tool links to AutoCAD PNAV ( 3043223 )
-PNAV - Add new tasks to Process Navigator for drawing control templates ( 3044485 )
Page Navigation - Quick-Start Home Page:
-PNAV - Additional tasks for the Quick-Start Home Page ( 3044069 )
-PNAV - Update the P-Nav tasks in ACAD for Quick-Start role ( 3042620 )
-PNAV - Add Help links for Quick-Start home page( 3042348 )
Technologies:
-PNAV - Refine Technologies processes to distinguish between HTML5 and Flash ( 3044549 )
UPDATE afm_processes SET title='Flash Drawing Views' WHERE activity_id='AbSolutionsUIAddIns' and process_id='Drawing Views';
UPDATE afm_processes SET title='Flash Chart Views' WHERE activity_id='AbSolutionsUIAddIns' and process_id='Chart Views';
UPDATE afm_processes SET title='Flash Chart Views in 3D' WHERE activity_id='AbSolutionsUIAddIns' and process_id='Chart Views in 3D';
_____________________________________________
DATA CHANGES
System:
-DATA - Change the Base Units in the schema.db to be "per Locale" ( 3042683 )
UPDATE afm_scmpref SET units=2;
-DATA - Add security group to new role OPS APPROVER ( 3042619 )
INSERT INTO afm_groupsforroles (group_name, role_name) VALUES ('%', 'OPS APPROVER');
-DATA - AbExtensionsMyArchibus activity should not be assigned in default databases ( 3043178 )
DELETE from afm_activities WHERE activity_id='AbExtensionsMyArchibus';
-DATA - Add sample data for AIDEMOxx users ( 3042976 )
Metrics:
-DATA - Transform ops_OntimePMCompletion_percent_monthly metric to ratio metric( 3043695 )
-DATA - Rename appropriate "income" metrics to "revenue" metrics. ( 3042837 )
-DATA - Check and correct binding expressions in metrics formulas or where clauses ( 3043658 )
-DATA - Update definition for the the Facility Condition Index metric. ( 3042563 )
-DATA - Update metric definition for spac_GrossArea_monthly ( 3042523 )
-DATA - create two Alert Metrics for Building Operations - Part Inventory: ops_alert_PartEstimates_InStockNotReserved, ops_alert_Parts_Understocked( 3044920 )
-DATA - Add new metric - Scale up "Carbon Footprint to Revenue" by a factor of 1 million ( 3042339 )
-DATA - Remaining REPM, PROJ, OPS Metrics , Create biz_implication and assumption values for these metrics. ( 3045353 )
-DATA - EHS, ENV and RISK Metrics, Create biz_implication and assumption values for these metrics. ( 3045025 )
-DATA - Add assumptions and biz implication values to select metric definitions( 3044633 )
Connectors:
-DATA - Populate tables for Connectors ( 3044625 )
-DATA - Validate Add and Populate table connector rules ( 3045217 )
Real Estate Portfolio Management - Lease Administration:
-DATA - Add sample data to ls_contacts ( 3044759 )
-DATA - Sample data required for Lease Portfolio Console and lease contacts ( 3045402 )
-DATA - Sample Data for 21.3 Portfolio Scenario Wizard( 3044657 )
Capital Project Management:
-DATA - Updates to HQ-BUILD-HQ-NEW dataset for CPM ( 3042019 )
DELETE FROM invoice WHERE project_id='BUILD-HQ-NEW' AND invoice_id IN ('2005000005','2005000003');
-DATA - Sample Data revisions for Portfolio Forecasting Wizard( 3044726 )
-DATA - FCPM - Invoice sample data status change to "Sent To Finance'( 3042421 )
UPDATE invoice SET status = 'SENT', fac_org_level_01 = '1', fac_org_level_02 = '2', fac_org_level_03 = '3',
fac_org_level_04 = '4', fac_org_level_05 = '5', fac_org_level_06 = '6'
WHERE project_id IS NOT NULL AND vn_id IS NOT NULL AND status = 'ISSUED';
Space Planning & Management:
-DATA - Add a new application parameter to control whether or not the Space Console loads on open( 3044215 )
-DATA - Dallas Office building has extra floors - remove and update area values - ( 3045346 )
Move Management:
-DATA - Move, Furniture, Copy and Department space have the same screens and fields as maintenance.( 3042647 )
-DATA - Employee data for SRL building ( 3044990 )
Asset Management:
-DATA - Enhancements to Part Inventory sample data ( 3045283 )
Building Operations - HelpDesk:
-DATA - Add MobileWorkRequestsMaxQuantityToSync Activity Parameter for AbBldgOpsHelpDesk application with default value of 250 ( 3039780 )
-SCRIPT - Update to Database production SQL script - adjust afm_roleprocs to account for new Bldg Ops processes ( 3044734 )
*DELETE FROM afm_roleprocs WHERE (SELECT is_active FROM afm_processes
WHERE afm_roleprocs.activity_id = afm_processes.activity_id AND afm_roleprocs.process_id = afm_processes.process_id ) = 0
OR (SELECT is_active FROM afm_activities WHERE afm_roleprocs.activity_id = afm_activities.activity_id ) = 0
OR process_id LIKE '%rmtrans%' OR process_id LIKE '%BldgOpsConsole%';
-DATA - Update the date fields of open work requests so they are current ( 3045077 )
-DATA - changes to BOSMED Service Level Agreements ( 3045299 )
-DATA - Add new application parameter for Building Operations called EditEstAndSchedAfterStepComplete ( 3044605 )
Environmental & Risk Management:
-DATA - Delete the extra city value of COPENHAGEN associated with the state PA in the city table. ( 3045418 )
-DATA - Compliance Map Demo - Add missing latitude / longitude data for SPRING_N and DALLASHQ( 3043226 )
-DATA - For Compliance, fix value in regulation.hierarchy_ids field for one record (NESHAP) ( 3042754 )
UPDATE regulation SET hierarchy_ids = 'CAA|NESHAP|' WHERE regulation='NESHAP';
-DATA - Incidents Map Demo Data - Fix invalid/inconsistent geographic hierarchy data in the building table( 3045443 )
-DATA - Change Responsible Person for Programs and Requirements to employees with mapped afm_user ( 3043416 )
UPDATE regrequirement SET em_id='GREEN, JARED' WHERE em_id='ABBOT, PAUL';
UPDATE regprogram SET em_id='CARLO, ALFRED' WHERE em_id='ABBOT, PAUL';
UPDATE regprogram SET em_id='DAMON, BEN' WHERE em_id='ACEVEDO,BRETT';
UPDATE regprogram SET em_id='FINERS, CHRIS' WHERE em_id='ANDERSON, CAROLINE';
UPDATE regprogram SET em_id='KOSTER, MARK' WHERE em_id='APPLEBY, STEVE';
UPDATE regprogram SET em_id='SMITH, ALBERT' WHERE em_id='BACHMAN, ELLEN';
UPDATE regprogram SET em_id='SMITH, PAUL' WHERE em_id='BAKER, GEORGE';
UPDATE regprogram SET em_id='SMITH, SALLY' WHERE em_id='BARNES, GREG';
Energy Management:
-DATA - Additional records for Energy / BAS submetering analytics demo data ( 3045453 )
-DATA - Add example data for Energy - sub-meter analytics ( 3045370 )
-DATA - Remove the application parameter ESRIArcGisOnlineServicesKey since users do not need to enter any value ( 3043778 )
DELETE FROM afm_activity_params WHERE param_id = 'ESRIArcGisOnlineServicesKey';
-DATA - Correct energy conversion sample data ( 3043935 )
UPDATE bill_unit SET rollup_type = 'Energy',conversion_factor = 0.1025, description = 'Hundred Cubic Feet (CCF). Heat content value obtained from http://www.eia.gov/tools/faqs/faq.cfm?id=45&t=8.'
WHERE bill_type_id = 'GAS - NATURAL' AND bill_unit_id = 'CCF';
Workplace Services Portal:
-DATA - Remove unnecessary SLA step records from Canonic DB ( 3042985 )
UPDATE helpdesk_sla_steps SET activity_id = 'AbBldgOpsHelpDesk', status = 'REQUESTED' WHERE step = 'Edit and Approve';
DELETE FROM afm_wf_steps WHERE status = 'R' AND step != 'Basic';
DELETE FROM afm_wf_steps WHERE status = 'A' AND step = 'Dispatch';
DELETE FROM afm_wf_steps WHERE status = 'Com' AND step = 'Satisfaction Survey';
DELETE FROM helpdesk_step_log WHERE date_response IS NULL AND step = 'Edit and Approve';
Reservations:
-DATA - Reservations - new activity parameter required for limiting the maximum occurrences for recurring reservations ( 3042232 )
-DATA - Add new activity parameter for Reservations( 3044093)
-DATA - Reservations Demo Data - ( 3045363 )
-DATA - Database updates required for Reservations KB 3044774 ( 3045183 )
-DATA - Reservations PlugIn for Microsoft Outlook - Adjust city time zones so that they are accurate in HQ canonic database ( 3044198 )
UPDATE city SET timezone_id = 'America/New_York' WHERE timezone_id = 'EST' AND state_id != 'IN';
UPDATE city SET timezone_id = 'America/Chicago' WHERE state_id = 'IN';
UPDATE city SET timezone_id = 'America/Chicago' WHERE timezone_id = 'CST' AND state_id != 'MEXI';
UPDATE city SET timezone_id = 'America/Mexico_City' WHERE state_id = 'MEXI';
UPDATE city SET timezone_id = 'America/Denver' WHERE state_id = 'CO';
UPDATE city SET timezone_id = 'America/Los_Angeles' WHERE timezone_id = 'PST';
Mobile:
-DATA - Add plan type for Mobile Workplace Services Portal mobile application for My Department Space( 3042618 )
-DATA - Activate Mobile Solutions application, by adding it into afm_mobile_apps table ( 3043946 )
INSERT INTO afm_groups(group_name,group_title) VALUES ('SYSTEM-MGR-MOB', 'Solutions Templates');
INSERT INTO afm_mobile_apps(activity_id, main_page_url, title, security_group) VALUES('AbSolutions', 'Solutions/index.html', 'Solutions Templates', 'SYSTEM-MGR-MOB');
-DATA - Add new records in active_plantypes table( 3044743 )
-DATA - Change GUEST-MOBILE user from role "1 - SELF SERVICE" to "1 - SELF SERVICE (ACP)" ( 3044937 )
-DATA - Mobile sample database - add SRL building ( 3043557 )
-DATA - Add user account changes to make mobile applications and roles conform to licensing.( 3044843 )
-DATA - Several new users need changes to their user account to sign in with mobile. ( 3045551 )
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START' AND group_name = 'ASSET-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START' AND group_name = 'OPS-CA-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START' AND group_name = 'RISK-IR-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START' AND group_name = 'WORKSVC-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START' AND group_name = 'SYSTEM-MGR-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START' AND group_name = 'WORKSVC-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START (ACP)' AND group_name = 'ASSET-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START (ACP)' AND group_name = 'OPS-CA-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START (ACP)' AND goup_name = 'RISK-IR-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START (ACP)' AND group_name = 'WORKSVC-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START (ACP)' AND group_name = 'SYSTEM-MGR-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = '4 - PROCESS OWNER - QUICK-START (ACP)' AND group_name = 'WORKSVC-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = 'Z-APP-AND-DOM' AND group_name = 'OPS-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = 'Z-APP-AND-DOM' AND group_name = 'SPAC-MOB';
DELETE FROM afm_groupsforroles WHERE role_name = 'Z-APP-AND-DOM' AND group_name = 'SPAC-SURVEY';
DELETE FROM afm_groupsforroles WHERE role_name = 'Z-APP-AND-DOM' AND group_name = 'SPAC-SURVEY-POST';
INSERT INTO afm_roleprocs (role_name, activity_id, process_id) VALUES ('4 - PROCESS OWNER - QUICK-START', 'AbSpacePersonnelInventory', 'Manager Dash 3');
INSERT INTO afm_roleprocs (role_name, activity_id, process_id) VALUES ('4 - PROCESS OWNER - QUICK-START (ACP)', 'AbSpacePersonnelInventory', 'Manager Dash 3');
INSERT INTO afm_roleprocs (role_name, activity_id, process_id) VALUES ('2 - WORKFLOW PROCESS', 'AbSpacePersonnelInventory', 'Client RmTrans');
INSERT INTO afm_roleprocs (role_name, activity_id, process_id) VALUES ('2 - WORKFLOW PROCESS (ACP)', 'AbSpacePersonnelInventory', 'Client RmTrans');
INSERT INTO afm_roleprocs (role_name, activity_id, process_id) VALUES ('FIELD TECHNICIAN', 'AbSpacePersonnelInventory', 'Client RmTrans');
INSERT INTO afm_roleprocs (role_name, activity_id, process_id) VALUES ('FIELD TECHNICIAN (ACP)', 'AbSpacePersonnelInventory', 'Client RmTrans');
INSERT INTO afm_roleprocs (role_name, activity_id, process_id) VALUES ('FIELD TECHNICIAN', 'AbRiskEHS', 'EHS - Track');
INSERT INTO afm_roleprocs (role_name, activity_id, process_id) VALUES ('FIELD TECHNICIAN (ACP)', 'AbRiskEHS', 'EHS - Track');
-DATA - Site Pictures for Mobile Marquee Images ( 3045563 )
-DATA - Mobile dataset -- add document to assessment ( 3045308 )
-DATA - Mobile data set - add photos to site and building ( 3045310 )
-DATA - Enable Mobile for user HAPET ( 3045227 )
-DATA - Add mobile security groups to roles EXECUTIVE MANAGER and EXECUTIVE MANAGER (ACP) - ( 3045367 )
-DATA - Mobile data set -- additions and corrections ( 3045312 )
-DATA - Mobile Assessment questionnaire sample data attached ( 3045421 )
-DATA - New Problem Types for Mobile Workplace Service Portal. ( 3042647 )
GIS:
-DATA - Add Connectors and Connector Fields for Extensions for ArcGIS ( 3044946 )
-DATA - Update Connectors for the Extensions for ArcGIS - ( 3045289 )
Drawings:
-DATA - Demo data - Populate rm.dwgname field for HQ-RF record( 3043211 )
UPDATE rm SET dwgname='HQRF' WHERE bl_id='HQ' AND fl_id='RF';
-DATA - Change the file name of BOSMED drawing ( 3042611 )
-DATA - Correct the drawing names and drawing files names of the BOSMED floors (1 and 2) to match the Revit model ( 3043373 )
UPDATE afm_dwgs SET dwg_name = 'BOSMED01_02-LEVEL 1', dwg_file = 'bosmed01_02-level 1.rvt' WHERE dwg_name = 'BOSMED01';
UPDATE afm_dwgs SET dwg_name = 'BOSMED01_02-LEVEL 2', dwg_file = 'bosmed01_02-level 2.rvt' WHERE dwg_name = 'BOSMED02';
UPDATE rm SET dwgname = 'bosmed01_02-level 1' WHERE bl_id = 'BOSMED' AND fl_id = '01';
UPDATE rm SET dwgname = 'bosmed01_02-level 2' WHERE bl_id = 'BOSMED' AND fl_id = '02';
-DATA - Suites layer title- Update the title for the suites layer title from 'Commercial' to 'Suites' ( 3029146 )
UPDATE afm_layr SET title='Suite' WHERE layer_name IN ('SU', 'SU$', 'SU$TXT');
BIM:
-DATA - Change afm_users.bim_license value for user AFMSTART( 3042663 )
-DATA - Add missing values in the afm_dwgs table for the BOSMED01_02 Revit model, to prevent Publish Model to fail ( 3044801 )
-DATA - Record added to afm_bim_params table to create fn. json.zlib file for furniture from Revit ( 3042531 )
-DATA - In canonic HQ DB, change drawing names and drawing file names for BOSMED drawings that attach to Revit ( 3042706 )
- Update Db Version Date to: 2013.11.11
- Update Db Version number to: 139
SYSTEM:
- Increase afm_flds.dflt_val size to VARCHAR(128); also in afm_flds_trans ( 3042049 )
UPDATE afm_flds SET afm_size=128, data_type=12 WHERE table_name IN ('afm_flds', 'afm_flds_trans') AND field_name='dflt_val';ALTER TABLE afm_flds MODIFY dflt_val VARCHAR(128) DEFAULT NULL;
ALTER TABLE afm_flds_trans MODIFY dflt_val VARCHAR(128) DEFAULT NULL;
- Update the Process Type enum display value for "PAGES" to be more descriptive for navigation pages( 3041703 )
UPDATE afm_flds SET enum_list='WEB;Web PNav;WEB-DASH;Web Dashboard;WEB-PAGENAV;Web Page Navigator;SC;Smart Client;SCOVERLAY;Smart Client Extension;WINDOWS;Client/Server;OVERLAY;Client/Server Overlay;PAGES;Nav Page Processes;WEB&PAGES;Web Pnav & Pages'where table_name='afm_processes' and field_name='process_type';
- Update the Process Type enum display value for "PAGES" to be more descriptive for navigation pages. (3041703)
UPDATE afm_flds SET enum_list='WEB;Web Process Navigator;WEB-DASH;Web Dashboard;WEB-PAGENAV;Web Page Navigator;SC;Smart Client;SCOVERLAY;Smart Client Extension;WINDOWS;Client/Server;OVERLAY;Client/Server Overlay;PAGES;Web Page Navigator Process;WEB&PAGES;Web Pnav & Pages'WHERE table_name='afm_processes' AND field_name='process_type';
- Add 'deleteEm' and 'deleteDv' values for enum list field rmpctmob_sync.action (3042577)
UPDATE afm_flds SET enum_list = 'N/A;N/A;insert;insert;update;update;delete;delete;deleteEm;deleteEm;deleteDv;deleteDv' WHERE table_name = 'rmpctmob_sync' AND field_name = 'action';
- Unable to create work requests on Sybase Runtime v9 - triggers not supported ( 3042271 )
DROP TRIGGER WRAUTONUMBER;
DROP TRIGGER WOAUTONUMBER;
METRICS:
- Add schema changes for metrics subsystem ( 3040574)
- Delete field afm_metric_trend_values.aggregate_as ( 3040816)
- Add the Display Format (Metric) column afm_metric_definitions.value_disp_format_m field. ( 3040989 )
CAD:
- Add support for saving the intermediate format of the model when publishing to SVG ( 3040219)
UPDATE afm_flds set enum_list='NONE;None;ALL;All;SVG;SVG;SVG and JSON;SVG and JSON;SWF;SWF;EMF;EMF;SWF and EMF;SWF and EMF;JSON;JSON;BY OWNER;By Owner;LAYERED BY OWNER;Layered By Owner;QUERY TEXT;Query Text;QUERY TABLE;Query Table;ASSET;Asset;RESTRICTION;Restriction;URL;URL;DYNAMIC;Dynamic' where afm_flds.table_name = 'afm_dwgpub' and afm_flds.field_name = 'rule_type'MOBILE:- Add new fields: activity_log_id, project_id, activity_type, cost_estimated in activity_log_sync table ( 3039842)
- Add new fields: wr_sync.time_assigned/time_requested ( 3040169)
- Schema changes for Space Book mobile app enhancements ( 3040554)
- Main App page: the application names under the icons are in English ( 3040041)
Add <field table="afm_mobile_apps" field="title"/> into afm_scmpref.preferences
- Equipment Status field should include "Missing" as an option ( 3039601)
UPDATE afm_flds SET enum_list = enum_list + ';miss;Missing' WHERE table_name IN ('eq', 'eq_audit') AND field_name='status'; UPDATE afm_flds SET dflt_val = 'miss' WHERE table_name IN ('eq', 'eq_audit') AND field_name='status';- Add new fields: hs_hide and hs_hide2 into active_plantypes table ( 3040284)
- Add sync tables for Incident Reporting mobile app ( 3039985)
- Add Db changes for Asset And Equipment Survey mobile app enhancements ( 3040477)
- Add field wr_sync.date_escalation_completion (copy of the wr.date_escalation_completion field ( 3040840)
- Change afm_mobile_apps.activity_id field definition to ANY CHAR, and change its values to CamelCase ( 3040962 )
UPDATE afm_flds SET string_format=5 WHERE table_name='afm_mobile_apps' AND field_name='activity_id'
- Add survey photo and comments fields into rm table ( 3041704 )
- Add display_order field to the afm_mobile_apps table ( 3041693 )
- Add table and fields for the menus in the WorkplacePortal mobile app ( 3041676 )
afm_mobile_menu - new table
activitytype.menu_icon,mobile_action,display_order,title,description - new fields
- Add fields in activity_log_sync table for Workplace Portal mobile app ( 3041706 )
activity_log_sync.prob_type
activity_log_sync.date_requested
activity_log_sync.status
activity_log_sync.requestor
activity_log_sync.phone_requestor
- Add rmpctmob_sync.activity_log_id field - copy of rmpct.activity_log_id ( 3041793 )
- Add the value 'delete' enum_list for field rmpctmob_sync.action.
- Set rmpctmob_sync.pct_id to allow null values( 3041709 )
- Loading the surveyrm_sync table in SC gives an error (3040305)
UPDATE afm_flds SET dep_cols = 'survey_id' WHERE table_name = 'surveyrm_sync' AND field_name = 'survey_id';
BLDG OPS:
- Add new fields ( 3040684)
- Update Enumeration lists ( 3040683)
- Add field "role_name" to wr_step_waiting view ( 3040716 )
- Change data type and increase size for table_name 'helpdesk_sla_response' AND field_name IN ('workflow_name', 'service_name') ( 3041516 ) UPDATE afm_flds SET data_type=12, afm_size=128 WHERE table_name = 'helpdesk_sla_response' AND field_name IN ('workflow_name', 'service_name');RESERVATIONS:
- Database updates required for Reservations ( 3039833)
- New field required for Room Reservations track of show/no show ( 3041270 )
- import items-3041270-doc1.xml
- copy field to hreserve_rm
- copy the field to resrmview
- re-create resrmview view
- Reservations SQL views definitions scripts may be outdated ( 3040922 )
UPDATE afm_flds SET afm_size=24 WHERE table_name IN ('rrdayrmres','rrdayrmresplus', 'rrdayrresplus', 'rrwrrestr') AND field_name='phone';UPDATE afm_flds SET afm_size=32 WHERE table_name 'rrwrrestr' AND field_name='prob_type';
UPDATE the sql scripts: \schema\ab-products\bldgops\common\schemawiz-update-reservation-sql-views-*.sql
EH&S:
- Remove ehs_incident_witness_sync.mob_incident_id validation ( 3041037 )
UPDATE afm_flds SET validate_data=0 WHERE table_name='ehs_incident_witness_sync' and field_name='mob_incident_id'
UPDATE afm_flds SET dep_cols=null WHERE table_name='ehs_incidents_sync' and field_name='mob_incident_id'
- Fields with enum_list "0;No;1;Yes" should have data_type SmallInt( 3041119 )
UPDATE afm_flds set data_type=5 where enum_list = '0;No;1;Yes' and data_type <> 5
- Add “Closed;Audit Closed” to the enumeration list in survey.status field ( 3040477 )
UPDATE afm_flds SET enum_list='Issued;Issued to Auditor;On Hold;Audit On Hold;Completed;Audit Completed;Closed;Audit Closed' WHERE table_name='survey' AND field_name='status'
ENERGY MGMT:
- Modify qty_energy field in Energy Mgmt tables, increase the size from 12 to 16 and decimals precision from 2 to 6 ( 3041351 )
WASTE MGMT:
- A few fields violate schema best practices ( 3039696 )
- Set numeric IDs to no separator
UPDATE afm_flds SET num_format = 2 WHERE table_name = 'ehs_chemicals' AND field_name = 'msds_id';
UPDATE afm_flds SET num_format = 2 WHERE table_name = 'waste_out' AND field_name = 'waste_id';
- Set numerics to default value of zero and not allow null
UPDATE afm_flds SET dflt_val = 0, allow_null = 0 WHERE table_name LIKE 'waste%' AND field_name = 'number_containers';
- Set to not allow null
UPDATE afm_flds SET allow_null = 0 WHERE table_name = 'waste_manifests' AND field_name IN ('discrepancy_type','discrepancy_residue','discrepancy_qty','discrepancy_partial_rejection','discrepancy_full_rejection','international_shipments'); UPDATE afm_flds SET allow_null = 0 WHERE table_name = 'waste_profiles' AND field_name IN ('transp_hazardous','specific_gravity','is_recyclable','status'); UPDATE afm_flds SET allow_null = 0 WHERE table_name = 'waste_facilities' AND field_name IN ('treatment','recycle','disposal','status');- Change from char to Varchar
UPDATE afm_flds SET data_type = 12 WHERE data_type = 1 AND table_name='waste_mgmt_methods' AND field_name IN ('method_name','method_group');UPDATE afm_flds SET data_type = 12 WHERE data_type = 1 AND table_name='waste_manifests' AND field_name = 'handling_instructions';
UPDATE afm_flds SET data_type = 12 WHERE data_type = 1 AND table_name='waste_profiles' AND field_name = 'waste_name';
UPDATE afm_flds SET data_type = 12 WHERE data_type = 1 AND table_name='waste_mgmt_method' AND field_name = 'method_group';
- Add comments for allowable exceptions to the norm
UPDATE afm_flds SET comments = comments + ' - Note: Allowing null and no default value is appropriate for this field, even though of a numeric data type.' WHERE field_name IN ('lat','lon');UPDATE afm_flds SET comments = comments + ' - Note: Allowing null and no default value is appropriate for this field, even though of a numeric data type.' FROM afm_flds WHERE field_name = 'msds_id' AND allow_null = 1;
CPM:
- table and fields additions/update, forecast, invoice ( 3041136 )
SPACE:
- New table/fields and tables for Space Occupancy mobile app ( 3041709 )
- New Fields:
-surveyrm_sync.cap_em - Employee Capacity-smallint - as copy from rm.cap_em
-surveymob_sync.survey_type - Survey Type- enum:N/A;N/A;SpaceBook;SpaceBook;Occupancy;Occupancy
- New Table:rmpctmob_sync
- Remove two values from the enumeration list in rmcat.used_in_calcs ( 3041160 )
UPDATE afm_flds SET enum_list='all_totals;All Totals;no_totals;No Totals' where table_name='rmcat' and field_name='used_in_calcs';
LOCALIZATION:
- Localize afm_currencies.description field ( 3040207 )
- Make changes for the 21.2 ML Single-line headings( 3040474)
1) Add Single-Line Headings fields for each of the languages (e.g. sl_heading_nl, sl_heading_fr, etc.) to the ARCHIBUS Fields Language Translations (afm_flds_lang) table. Use the same characteristics as the (afm_flds.sl_heading) field.
INSERT INTO afm_flds ( afm_module,afm_size,afm_type,allow_null,attributes,comments,data_type,decimals,dep_cols,dflt_val,edit_group,edit_mask,enum_list,field_grouping,field_name,is_atxt,
is_tc_traceable,max_val,min_val,ml_heading,num_format,primary_key,ref_table,review_group,sl_heading,string_format,table_name,validate_data,transfer_status)
SELECT 0,64,2050,allow_null,attributes,'v21.2 Mobile',data_type,decimals,NULL,dflt_val,edit_group,edit_mask,enum_list,field_grouping,'sl' || substring(field_name,3,length(field_name)),is_atxt,
is_tc_traceable,max_val,min_val,'SL' || substring(ml_heading,3,length(ml_heading)),num_format,primary_key,NULL,review_group,sl_heading,5, table_name,validate_data,transfer_status
FROM afm_flds WHERE table_name = 'afm_flds_lang' AND field_name like 'ml_heading%'
2) Add a new entry for afm_flds.sl_headings (shown below) to Schema Preferences (XML) (afm_scmpref.preferences). <field table="afm_flds" field="sl_heading" translationTable="afm_flds_lang"/>
3) In the ARCHIBUS Fields table, add an additional enum option: "3;TYPE_SL_HEADING" for the lang_strings.string_type field. (We don't need this for 21.1 ML, but we will for the localization kit for 21.2).
UPDATE afm_flds SET enum_list='0;TYPE_DEFAULT;2;TYPE_ML_HEADING;3;TYPE_SL_HEADING' WHERE table_name='lang_strings' AND field_name='string_type'
- Update the afm_flds_lang table with new fields from afm_flds
INSERT INTO afm_flds_lang (table_name, field_name)
SELECT table_name, field_name FROM afm_flds
WHERE NOT EXISTS (SELECT 1 FROM afm_flds_lang afm_flds_lang_inner
WHERE afm_flds_lang_inner.table_name = afm_flds.table_name
AND afm_flds_lang_inner.field_name = afm_flds.field_name )
Run schema update wizard for tables:
'activity_log_sync;docs_assigned_sync;ehs_incident_witness_sync;ehs_incidents_sync;eq;eq_audit;survey;surveyrm_sync;wr_sync;active_plantypes;afm_metric%;rm_config;afm_flds_lang'
'invoice;project;wr_step_waiting;proj_forecast%;afm_currencies'
'bill;bill_archive;bill_line;bill_line_archive;rm;afm_mobile_%;afm_metric_definitions;surveyrm_sync;surveymob_sync;rmpctmob_sync;activity_log_sync;activitytype'
'rmpctmob_sync;helpdesk_sla_response;waste_out;waste_manifests;waste_profiles;waste_mgmt_methods;waste_facilities'
Re-create SQL views
SYSTEM:
- WFR errors in archibus.log ( 3041100 )
update scheduled WFR to run 5 min apart.
- In afm_wf_rules table, XML Schedule Properties field not empty for non-scheduled WFRs( 3041014 )
UPDATE afm_wf_rules SET xml_sched_props=null WHERE xml_sched_props IS NOT NULL AND rule_type<>'Scheduled'
- Disable non-essential WFRs from running on startup as on startup the SmartClient can crash because of a database error caused by rule ApplyIndexesService ( 3042287 )
UPDATE afm_wf_rules SET xml_sched_props = REPLACE(xml_sched_props, 'runOnStartup="true"', 'runOnStartup="false"')
WHERE is_active=1 AND rule_type='Scheduled' AND xml_sched_props LIKE '%runOnStartup="true"%'
AND activity_id||'-'||rule_id NOT IN
(SELECT activity_id||'-'||rule_id FROM afm_wf_rules WHERE is_active=1 AND rule_type='Scheduled'
AND
((activity_id='AbBldgOpsHelpDesk' AND rule_id='runSLAEscalations') OR
(activity_id='AbCommonResources' AND rule_id='ScheduledNotificationService') OR
(activity_id='AbSpaceRoomInventoryBAR' AND rule_id='updateRmAndEmFromRmpct') OR
(activity_id='AbSystemAdministration' AND rule_id='removeUnlicensedActivities'))
)
MOBILE:
- Add a new workflow rule for Incident Reporting mobile app ( 3039984 )
INSERT INTO afm_wf_rules(is_active,transfer_status,description,dwgname,ehandle,activity_id,rule_id,rule_type,group_name,xml_rule_props,xml_sched_props) VALUES ('1','NO CHANGE','EHS Mobile Service',NULL,NULL,'AbRiskEHS','EHSMobileService','Message',NULL,'<xml_rule_properties description="EHS Mobile Service"><eventHandlers><eventHandler class="com.archibus.eventhandler.ehs.mobile.EhsMobileService" method=""><inputs/></eventHandler></eventHandlers></xml_rule_properties>',NULL);- Add AssessmentMobileService workflow rule ( 3040076 )
INSERT INTO afm_wf_rules(is_active,transfer_status,description,dwgname,ehandle,activity_id,rule_id,rule_type,group_name,xml_rule_props,xml_sched_props) VALUES ('1','NO CHANGE',NULL,NULL,NULL,'AbCapitalPlanningCA','AssessmentMobileService','Message',NULL,'<xml_rule_properties description=""><eventHandlers><eventHandler class="com.archibus.app.assessment.mobile.service.impl.AssessmentMobileService" method=""><inputs/></eventHandler></eventHandlers></xml_rule_properties>','<xml_schedule_properties><schedule startTime="01-01-2004 12:00:00" endTime="" runOnStartup="false"><simple repeatCount="-1" repeatInterval="86400000"/></schedule></xml_schedule_properties>');- Add new WFR for Page Navigator ( 3040687 )
INSERT INTO afm_wf_rules (xml_rule_props, is_active, rule_type, description, activity_id, rule_id) VALUES ('<xml_rule_properties description="Initiates navigation page generation for a particular role-locale record"> <eventHandlers> <eventHandler class="com.archibus.eventhandler.NavigationPagesPublisher" method="generatePagesForRole"> <inputs> </inputs> </eventHandler> </eventHandlers> </xml_rule_properties>', 1, 'Message','Initiates navigation page generation for a particular role-locale record','AbCommonResources','generateNavigationPagesForRole') INSERT INTO afm_wf_rules (xml_rule_props, is_active, rule_type, description, activity_id,rule_id) values ('<xml_rule_properties description="Returns data records as for the report grid augmented with additional properties"> <eventHandlers> <eventHandler class="com.archibus.eventhandler.ViewHandlers" method="getDataRecordsPlusProjectInfo"> <inputs> </inputs> </eventHandler> </eventHandlers> </xml_rule_properties>', 1, 'Message','Returns data records for Bali 1 Page Navigator', 'AbCommonResources', 'getDataRecordsPlusProjectInfo')- New Workflow rule, 'WorkplacePortalMobileService', for Workplace Portal mobile app ( 3041705 )
- New Workflow rule, 'SpaceOccupancyMobileService', for Space Occupancy mobile app( 3041710 )
- Update the AbWorkplacePortal-WorkplacePortalMobileService WFR to point to the renamed package (com.archibus.app.workplace.mobile.service.impl) ( 3041796 )
- Add Workflow rule definition for Workplace Portal mobile app - Reservations and Hoteling modules( 3041925 )
METRICS:
- Add New WFR for metrics calculation ( 3040790)
SYSTEM ADMIN:
- Add a task for "Add Field" ( ab-schema-add-field.axvw ) to the System Admin process ( 3039861 )
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file) VALUES('AbSystemAdministration', 'Add-In Manager', 'Add Field', 950, 'WEB URL','ab-schema-add-field.axvw')- Add or replace the task "Edit ARCHIBUS Data Dictionary" ( 3039999 )
UPDATE afm_ptasks SET task_file='ab-edit-data-dictionary.axvw' WHERE task_file = 'ab-data-dictionary.axvw';
- Database changes to support the 21.2 Page Navigation functionality ( 3040687 )
INSERT INTO afm_activities (activity_id,summary,title,display_order,version) VALUES ('AbNavigationPages','Navigation activity to facilitate role-to-process or role-to-page relation.', 'Page Navigator',0,1)INSERT INTO afm_ptasks (display_order, icon_small, task_file, task_type, activity_id, process_id, task_id) VALUES (450,'ab-icon-task.gif','ab-publish-navigation-pages.axvw','WEB URL','AbSystemAdministration','Add-In Manager','Publish Navigation Pages by Role')
- Add "Use Building Operations Console" to the PNav in System Administration( 3040919 )
- Add Pnav entry for 'Review Record Counts' - ab-records-count.axvw ( 3041654 )
- Add roles and page navigators for the AI* and AFM* users ( 3041698 )
MOBILE:
- Add the AIM, AFMM mobile page navigator users, their processes, ptasks, and roleprocs ( 3042091 )
- View 'Select Space Book Plan Types' should be renamed since it is used also by Space Occupancy app ( 3041897 )
METRICS:
- Add PNav entries for Metrics:
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file) VALUES('AbSystemAdministration', 'Add-In Manager', 'Metrics', 460, 'LABEL',''); INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file) VALUES('AbSystemAdministration', 'Add-In Manager', 'Define Metrics', 465, 'WEB URL','ab-ex-metric-metrics-def.axvw'); INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file) VALUES('AbSystemAdministration', 'Add-In Manager', 'Define Granularities', 470, 'WEB URL','ab-ex-metric-gran-def.axvw'); INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file) VALUES('AbSystemAdministration', 'Add-In Manager', 'Define Scorecards', 480, 'WEB URL','ab-ex-metric-scorecard-def.axvw');- Add new 'Metric Trend Values' task ( 3041671 )
SOLUTION TEMPLATES:
- Add the CAD paginated report to the PNav ( 3039838 )
- Add/update Solution Templates tasks ( 3040170 )
ASSET MGMT:
- The equipment asset console view in SmartClient does not add titles like the same view in WebCentral ( 3039084 )
UPDATE afm_ptasks SET task_file = 'ab-eq-locate.axvw' WHERE task_file='ab-ap-eq-locate.axvw'
- Copy the Asset & Equipment Survey Console view to the Asset Management Domain ( 3040487 )
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file) VALUES('AbAssetManagement', 'Equipment', 'Manage Asset & Equipment Surveys', 250, 'WEB URL', 'ab-eq-survey-console.axvw')- Update Pnav for Defining/Editing Equipment ( 3041155 )
UPDATE afm_ptasks SET task_file = 'ab-eq-edit.axvw' WHERE task_file = 'ab-ca-def-eq.axvw'
PROJ MGMT:
- Add Define Vendors view to setup menu ( 3040416 )
INSERT INTO afm_ptasks(activity_id, process_id, task_id, display_order, task_type, task_file) VALUES('AbProjectManagement', 'Set Up', 'Define Vendors', 1670, 'WEB URL','ab-vn-def.axvw')RPLM:
- Add Define Fiscal Year task under Cost Administration activity ( 3037600 )
INSERT INTO afm_ptasks (display_order, icon_small, task_file, task_type, activity_id, process_id, task_id)VALUES (450,'ab-icon-task.gif','ab-repm-lsadmin-fiscal-year-def.axvw','WEB URL','AbRPLMCosts','Business Process Owner','Define Fiscal Year')
SPACE:
- Add entries for the new Space Console and associated views ( 3040900 )
- afm_ptask changes for Space Mgmt ( 3040863 )
- Add "Quick Start" users and roles ( 3040921 )
- Add a PNav Pick for Space survey console to space menu ( 3042330 )
Space & Ops Quick-Start:
- Add record to afm_ptasks to include a link to the Quick-Start intro help page ( 3042155 )
- Process Navigator and Role Assignment changes ( 3041505 )
- Update afm_ptasks for Employee Capacity views ( 3041944 )
- Add a task to define quick-start metric limits ( 3042016 )
- Page Navigation, add several missing afm_roleprocs for the self-service home page ( 3042112 )
- Smart Client tasks and SC Extension tasks for Quick-Start roles ( 3042020 & 3041939 )
- Process Navigator and Role Assignment changes ( 3041505, 3041730 )
RESERVATIONS:
- New task required for Room Reservations track of show/no show ( 3041569 )
afm_roleprocs updates:
- Add 'Executive Reports' and 'Process' processes for all active applications to the 'EXECUTIVE MANAGER' and 'EXECUTIVE MANAGER (ACP)' roles in the afm_roleprocs table:
- Add all processes for all active activities to each of these roles in the afm_roleprocs table:
4 - PROCESS OWNER
4 - PROCESS OWNER (ACP)
ACTIVITY LICENSEE
- Add all processes for the 'AbSystemAdministration' activity to each of these roles in the afm_roleprocs table:
4 - SYSTEM ADMINISTRATOR
SYSTEM ADMINISTRATOR
SYSTEM ADMINISTRATOR (ACP)
SYSTEM:
- New activity parameter required for recurring schedule service ( 3041474 )
- Update AFM_CURRENCIES.CURRENCY_SYMBOL ( 3041417 )
- Change the locale for select level 4 and executive managment users from "English (United States)" to "Default" ( 3042060 )
UPDATE afm_users SET locale='DEFAULT' where user_name in ('AI','AIX','ABERNATHY','AIDEMO5','AFMDEMO5');USERS:
- Add the AIM, AFMM mobile page navigator users, their processes, ptasks, and roleprocs (3042091)
UPDATE afm_users SET clr_scheme = 'IMPACT' WHERE user_name IN ( 'AIM', 'AFMM' );
- Current HQ users cannot login if WebCentral default language is not en-US (3042437)
UPDATE afm_users SET locale = 'en_US' WHERE user_name IN ( 'AIX', 'AFMX' );
METRICS:
- Add Metrics sample data ( 3040907 )
- Add afm_metric_definitions sample data ( 3040991, 3041684 )
- label all afm_metric_trend_values records as "example" data (3042543)
UPDATE afm_metric_trend_values SET collect_err_msg = 'example';
- Add sample data for metric, granularity, and scorecard definitions and sample values ...
- Set all metrics, except legacy ones as Active, but Not Tested( 3042040 )
- Add metric records for the Quick-Start home page ( 3041794 )
- Import the new metric scorecard assignments ( 3042092 )
- Set all metrics, except legacy ones as Active, but Not Tested( 3042040 )
MOBILE:
- Add record in afm_mobile_apps table ( 3040076)
INSERT INTO afm_mobile_apps(activity_id, title, security_group, main_page_url) VALUES('ABCONDITIONASSESSMENT', 'Assessment', 'OPS-CA-MOB', 'ConditionAssessment/index.html')- Add record in afm_mobile_apps table ( 3040441)
INSERT INTO afm_mobile_apps(activity_id, title, main_page_url) VALUES('ABINCIDENTREPORTING', 'Incidents Reporting', 'IncidentReporting/index.html')- Update description field text for two Clean Building problem type records( 3040429)
- Change afm_mobile_apps.activity_id field values to CamelCase ("AbConditionAssessment", "AbEquipmentSurvey", "AbIncidentReporting", "AbSpaceBook", "AbWorkRequest") - ( 3040962 )- Mobile Maintenance app: add "10 - MAINTENANCE" plan type( 3040949 )
- Additional Activity Parameters needed for Mobile Framework ( 3041166 )
INSERT INTO afm_activity_params(activity_id, param_id, param_value, description, applies_to) VALUES('AbSystemAdministration', 'MobileAppsShowSampleCameraImages', 1, 'Indicates that the camera icon should be displayed when running the apps in a browser.', 'Mobile') INSERT INTO afm_activity_params(activity_id, param_id, param_value, description, applies_to) VALUES('AbSystemAdministration', 'BackgroundDataExpiration', 12, 'Indicates the number of hours that the background sync data is valid.', 'Mobile')- Create user group for Incidents Reporting app( 3041596 )
- Add new security group for Space Occupancy mobile app ( 3041712 )
- Activate Space Occupancy mobile app by adding it into afm_mobile_apps table ( 3041715 )
- Add security group for Workplace Services Portal mobile app ( 3041472 )
- Add the Workplace Services Portal mobile app entry ( 3041471 )
- Insert afm_mobile_menu records for WorkplacePortal mobile app ( 3041675 )
- Upload photo icons for to the Workplace Portal Request form ( 3041641 )
- Add plan types for Workplace Portal mobile app for Locating Employees and Locating Rooms ( 3041735 )
- Add sort order values for afm_mobile_apps ( 3041693 )
- Add plan types for Workplace Portal mobile app for Reservations and Hoteling ( 3041923 )
- Update and insert activitytype records for WorkplacePortal mobile app ( 3041674 )
RESERVATIONS:
- New activity parameter required for limiting the maximum occurrences for recurring reservations( 3042232 )
CPM:
- Make AIX the appr_mgr1 for projects WHERE AI is the proj_mgr (3042519)
UPDATE project SET apprv_mgr1 = 'AIX' WHERE proj_mgr = 'AI';
- Capital Project legacy data and Facilty Construction Project Manager sample data ( 3041295 )
- Sample data adjustments for invoices, activity_log, and project forecasts for CPM ( 3041742 )
- FCPM project status values need update ( 3041546 )
- Capital Budgeting home page sample data ( 3041688 )
- Updates to HQ-BUILD-HQ-NEW dataset for CPM ( 3042019 )
- Invoices with status ISSUED should not have amount_closed values ( 3042152 )
BLDGOPS:
- Update the division and department assigned to employee DISPATCHER ( 3042545 )
- Bad data in default database for two afm_wf_steps records ( 3039981)
UPDATE afm_wf_steps SET subject_message_id=UPPER(subject_message_id) WHERE subject_message_id is not null;
UPDATE afm_wf_steps SET body_message_id=UPPER(body_message_id) WHERE body_message_id is not null;
- Change task_id from 'Operations Console' to 'Building Operations Console' ( 3041757 )
- Sample data changes ( 3041507 )
- Remove old activity params ( 3041507 )
DELETE FROM afm_activity_params WHERE param_id IN ('ShowEquipmentOnCreateRequest', 'ShowProblemTypesOnCreateRequest');- Add 'Survey_View' activity parameter for use by BldgOps Console( 3042322 )
- Add BldgOps notification messages ( 3042299 )
- Change some data in table afm_wf_steps, due to recently reported issues ( 3042231 )
UPDATE afm_wf_steps SET step_status_result = 'surveyed' WHERE step = 'Satisfaction Survey';
UPDATE afm_wf_steps SET step = 'Scheduling' WHERE step_type = 'scheduling';
SPACE:
- Sample data updates for Space and BldgOps ( 3041137 )
- change the site of some buildings back to their v20.2 values (revert KB 3039008) ( 3041015 )
UPDATE bl SET bl.site_id='JFK', bl.dwgname=null, bl.ehandle=null WHERE bl.bl_id LIKE 'JFK%';
UPDATE bl SET bl.site_id='OLDCITY', bl.dwgname=null, bl.ehandle=null WHERE bl.bl_id = 'XC';
- Sample data changes ( 3041506 )
- Database changes Building Operations and Space Management( 3040686 )
Add AbBldgOpsOnDemandWork.TranslationsForWorkRequestStatus parameter
ASSET MGMT:
- Update inconsistent values in enum_list for status field in eq and eq_audit tables( 3041211 )
UPDATE afm_flds SET enum_list = 'in;In service;out;Out of Service;rep;In Repair;stor;In storage;salv;Salvaged;sold;Sold;miss;Missing' WHERE table_name IN ('eq', 'eq_audit') AND field_name='status';ENERGY:
- OSHA Reports - Injury types are not defined - Add records to HQ ( 3039781 )
- Update weather station assignments for a selection of buildings in the HQ demo data set ( 3039937 )
- Add DALLASOF bill_archive data ( 3039863 )
CLEAN BUILDING:
- Capital Projects need Project Type re-assignment for some Hazmat projects ( 3041543 )
UPDATE project SET project_type = 'Study' WHERE project_type = 'ASSESSMENT - HAZMAT' AND prob_type IS NULL
- Remove and clean up some Clean Building activity_log sample data ( 3041651 )
EH&S:
- Training Program Hours report does not have supporting sample data ( 3039525 )
- One description content in ehs_incident_injury_cat table needs to be updated (3042412)
- Update enumeration list DATA for EHS restriction_type_id ( 3038453)
UPDATE afm_flds SET enum_list='Permanent;Permanent;Temporary;Temporary' WHERE table_name='ehs_restrictions' AND field_name='restriction_type_id';
UPDATE ehs_restrictions SET restriction_type_id = 'Temporary' WHERE restriction_type_id = 'Temp';
COMPLIANCE:
- Insert additional Compliance demo data for LICENSE compliance programs ( 3041692 )
- Turn off Compliance notifications in sample data ( 3042285 )
UPDATE notifications SET is_active=0;
MSDS:
- Modify historical MSDS records to be more realistic and demonstrate new use case support( 3041645 )
- Reduce prominence of hand sanitizer vs other products in MSDS locations data ( 3041731 )
WASTE MGMT:
- Update one waste record to enable testing and display of one report highlight feature( 3041646 )
- Insert additional waste and waste manifest records to populate report view ( 3041644 )
- Demonstration data to be added to Waste Management, to have more manifests and see results in some reports ( 3041611 )
GREEN BLDGS:
- Green Buildings demonstration data moved forward in time( 3041195 )
- Green Buildings gb_fp_setup.calc_year demonstration data moved forward in time ( 3041195 )
ALL:
- Execute the Basic Rule BasicRules_AdvanceSampleDataDates.java ( 3040412 )
Update Db Version Date to: 2013.05.15
Update Db Version number to: 138
System:
- New fields added:
afm_dwgpub color_mapping
afm_timezones localization fields- Rename user color scheme Slate - Large Font to Slate - Small Font ( 3038597 )
UPDATE afm_flds SET enum_list='SLATE;Slate;IMPACT;High Impact;SLATE-SMALL;Slate - Small Font' WHERE table_name='afm_users' AND field_name='clr_scheme'- Add publishing option to convert all CAD and Revit features to a single color (usually black or a dark grey) ( 3035810 )
Add color_mapping field to afm_dwgpub table- Add capability for numeric answers for questionnaire fields ( 3028733
UPDATE afm_flds SET enum_list = enum_list || 'Num;Number;Int;Integer' WHERE table_name='questions' AND field_name='format_type'- Add SVG Type to the drawing publishing rules ( 3038961 )
- Phone fields for all tables set to size = 24. (3039150)
Tables updated: bl;combext;company;em;hreserve;insurer;jk;jkext;lessor;mo;project;reserve;rm;telext;visitors;vn;waste_facilities
Also a number of SQL view afm_flds values updated- Data type differs for 2 Foreign Keys for Sql Server DB ( 3039475 )
UPDATE afm_flds SET data_type=1 WHERE table_name='ehs_incidents' and field_name = 'short_term_ca'
UPDATE afm_flds SET data_type=12 WHERE table_name='afm_redlines' and field_name = 'dwg_name'- Schema Change Wizard shows "Database value not in enum" warnings ( 3039476 )
UPDATE afm_flds SET enum_list =
2050;None;2090;Block;2100;Dwgname;2105;Ehandle;2110;Std;2115;Layr;2120;Area;2125;Len;2055;Desc;2075;XDim;2080;YDim;2085;ZDim;2095;Std. Area;2150;Hierarchical;2155;Hierarchical-Concat;2160;Hierarchical Trace;2135;HPattern;2140;HPattern Acad;2145;HPattern Acad Ext;2060;Graphic;2165;Document;2065;Bar code;2245;Currency;2070;Calculated;2068;Calc-Bar Code;2200;Tc Level;2210;Tc Container;2220;Tc Multiplexing;2230;Tc Contained Tbls;2240;Tc nPositions;2241;Tc Ca Std Max Length;2242;Tc Ca Std Layer;2243;Tc Ca Std Width;2244;Tc Ca Std Color;2170;Doc Stg;2175;Questionnaire'
WHERE table_name = 'afm_flds' and field_name = 'afm_type;'
UPDATE afm_flds set enum_list =
'N/A;N/A;COMMERCIAL;COMMERCIAL;HOSPICE;HOSPICE;HOSPITAL;HOSPITAL;HOTEL;HOTEL;MANUFACTURING;MANUFACTURING;MIXED USE;MIXED USE;OFFICE;OFFICE;RESIDENTIAL;RESIDENTIAL;RETAIL;RETAIL;SPORT/RECREATION;SPORT/RECREATION;CALL CENTER;CALL CENTER;DATA CENTER;DATA CENTER;LABORATORY;LABORATORY;WAREHOUSE;WAREHOUSE'
WHERE table_name = 'ls' AND field_name = 'space_use';
Bldg Ops:
- Update the SQL views: (3037362)
Run (from Win C\S): System \ Schema \ Define \ Update Service Desk and On Demand Work SQL Views- Enum list for scheduling substitututes re-organized and edited for consistency ( 3037944 )
UPDATE afm_flds SET enum_list='acceptance;Service Provider;craftsperson;Craftsperson;supervisor;Supervisor;manager;Service Desk Manager;approval;Approve;review;Edit and Approve;dispatch;Dispatch;estimation;Estimate;scheduling;Schedule;verification;Verify;survey;Survey' WHERE table_name='workflow_substitutes' AND field_name='steptype_or_role'
Gov. Real Prop:
- New enum fields for FRPP 2012 need default values to indicate no change:
UPDATE afm_flds SET enum_list = 'Z;Not Applicable;Y;Yes;N;No;' WHERE enum_list = 'Y;Yes;N;No' AND table_name = 'grp_trans';
UPDATE afm_flds SET enum_list = 'NA;Not Applicable;LX;(LX) Lease Termination;LE;(LE) Lease Expiration;UN;(UN) Unknown' WHERE enum_list = 'LX;(LX) Lease Termination;LE;(LE) Lease Expiration;UN;(UN) Unknown' AND table_name = 'grp_trans';
Mobile:
- New tables added:
afm_mobile_apps
active_plantypes
activity_log_sync
surveyrm_sync
wr_other_sync
wr_sync
wrcf_sync
wrpt_sync- New Fields added
afm_users:
mob_device_id
mob_devide_enabled
afm_activity_params.applies_to
eq_audit:
marked_for_deletion
mob_locked_by
mob_is_changed
transfer_status
eqstd.hpattern_acad
survey.status- Get Background Data got error when control Mobile App access by Security Group (3039061)
UPDATE afm_flds SET review_group = NULL WHERE table_name = 'bl' AND field_name = 'name'
- Modify the eq_audit table, make bl_id, fl_id and rm_id fields allow null values and add site_id to the eq_audit table (3039370)
Risk & Env:
- Schema changes for EH&S KB items:
New fields added: ( 3038494, 3038495 )
ehs_restrictions restriction_class
ehs_training hours_training
ehs_training vn_id
ehs_training_results hours_trainingKB 3038503: SCHEMA - schema and sample data modifications for EH&S days_lost_work
UPDATE afm_flds SET ml_heading='Lost Productivity (Days)' WHERE table_name = 'ehs_incidents' and field_name = 'lost_work_days';
UPDATE afm_flds SET ml_heading='Lost Work (Days), Manual Entry' WHERE table_name = 'ehs_restrictions' and field_name = 'lost_work_days';
INSERT afm_flds VALUES ('ehs_restrictions', 'restriction_class', '2050','0','v21.1 EHS',1,0,null,'Away',null,null,'Away;Away from work;Remained-Restricted;Remained at work: Job transfer or restriction;Remained-Other;Remained at work: Other recordable cases','0',null,null,'Restriction Classification',1,0,0,null,null,24,null,5,0,null,null,1,'NO CHANGE');
UPDATE ehs_restrictions SET restriction_type_id = 'Temporary' WHERE restriction_id = 4;
UPDATE ehs_restrictions SET restriction_class = 'Remained-Restricted' WHERE restriction_id NOT IN ('4','16','18','26');
UPDATE ehs_restrictions SET restriction_class = 'Away' WHERE restriction_id IN ('4','16','18','26');
REPM:
- Cost Indexing:
New tables added:
cost_index
cost_index_trans
cost_index_values
ls_index_profile
New fields added:
ls cost_index
ccost_sum date_costs_last_calc (3039356)
- CAM Cost Reconciliation:
New tables added:
ls_cam_profile
ls_cam_rec_report
New fields added:
cost_tran cam_cost
cost_tran_recur cam_cost
cost_tran_sched cam_cost
- Costs that have had VAT amount overridden to 0 are not charged back correctly ( 3039540 )
cost_tran_recur
UPDATE afm_flds SET dflt_val = '-1.0' WHERE table_name = 'cost_tran_recur' and field_name = 'vat_amount_override';
ALTER TABLE cost_tran_recur MODIFY vat_amount_override NUMERIC(12,2) NOT NULL DEFAULT -1.0;
UPDATE cost_tran_recur SET vat_amount_override = -1.0 WHERE vat_amount_override = 0;cost_tran_sched UPDATE afm_flds SET dflt_val = '-1.0' WHERE table_name = 'cost_tran_sched' and field_name = 'vat_amount_override';
ALTER TABLE cost_tran_sched MODIFY vat_amount_override NUMERIC(12,2) NOT NULL DEFAULT -1.0;
UPDATE cost_tran_sched SET vat_amount_override = -1.0 WHERE vat_amount_override = 0;cost_tran
UPDATE afm_flds SET dflt_val = '-1.0' WHERE table_name = 'cost_tran' and field_name = 'vat_amount_override';
ALTER TABLE cost_tran MODIFY vat_amount_override NUMERIC(12,2) NOT NULL DEFAULT -1.0;
UPDATE cost_tran SET vat_amount_override = -1.0 WHERE vat_amount_override = 0;
Localization:
- List of countries in the Time Zone field for geographical locations are not translated (3030066)
Add <field table="afm_timezones" field="timezone_id" /> to afm_scmpref.preferences- To get the new fields from the last update into afm_flds_lang run:
INSERT INTO afm_flds_lang (table_name, field_name)
SELECT table_name, field_name FROM afm_flds
WHERE NOT EXISTS (SELECT 1 FROM afm_flds_lang afm_flds_lang_inner
WHERE afm_flds_lang_inner.table_name = afm_flds.table_name
AND afm_flds_lang_inner.field_name = afm_flds.field_name )
System:
- Remove the AbCommonResources-ScheduleNotificationService record from afm_wf_rules as it is a duplicate of: AbCommonResources-ScheduledNotificationService ( 3037561 )
Mobile:
- Add afm_wf_rules:
| Primary Application | Rule Code | Class Name |
| AbCommonResources | DrawingSvgService | com.archibus.app.common.drawing.service.DrawingSvgService |
| AbSpaceRoomInventoryBAR | SpaceMobileService | com.archibus.app.space.service.SpaceMobileService |
| AbAssetManagement | AssetMobileService | com.archibus.app.asset.service.AssetMobileService |
REPM:
- Add WFR for AbCommonResources.CostIndexingService
- Add WFR for Cost reporting service ( 3039046 )
Project Mgmt:
- Add WFR for AbProjectManagement.ProjectCostsService for "Budget vs Spend" reports
Space:
- Update AbSpaceRoomInventoryBAR.RoomTransactionRecorderForDataChangeEvent WFR definition ( 3038644 )
UPDATE afm_wf_rules SET is_active='1',transfer_status='UPDATED',description='Room Transaction Recorder for DataChange events for room table.' ,dwgname=NULL,ehandle=NULL,rule_type='DataEvent',group_name=NULL,xml_rule_props='<xml_rule_properties description="Room Transaction Recorder for DataChange events for room table."> <eventHandlers><eventHandler class="com.archibus.service.space.datachangeevent.RoomTransactionDataEventListener" method=""> <inputs></inputs></eventHandler></eventHandlers></xml_rule_properties>',xml_sched_props='<xml_schedule_properties> <schedule startTime="01-01-2004 12:00:00" endTime="" runOnStartup="false"><simple repeatCount="-1" repeatInterval="86400000"/> </schedule> </xml_schedule_properties>' WHERE (activity_id='AbSpaceRoomInventoryBAR' AND rule_id='RoomTransactionRecorderForDataChangeEvent');
Localization:
- Remove old localization WFRs ( 3039051 )
Solutions:
- Include WFR definitions for AbSolutionsViewExamples.DocumentJob and AbSolutionsViewExamples.EmployeesJob ( 3037342 )
INSERT INTO afm_wf_rules(is_active,transfer_status,description,dwgname,ehandle,activity_id,rule_id,rule_type,group_name,xml_rule_props)
VALUES ('0','NO CHANGE','Example of Job uploading document from WebCentral to MS SharePoint server, using WebService.',NULL,NULL,'AbSolutionsViewExamples','DocumentJob','Message',NULL,'
<xml_rule_properties description=""><eventHandlers>
<eventHandler class="com.archibus.app.solution.common.webservice.document.DocumentJob" method="uploadDocument"><inputs/> </eventHandler></eventHandlers></xml_rule_properties>')
INSERT INTO afm_wf_rules(is_active,transfer_status,description,dwgname,ehandle,activity_id,rule_id,rule_type,group_name,xml_rule_props)
VALUES ('0','NO CHANGE','Example of Job calling EmployeeService
WebService.',NULL,NULL,'AbSolutionsViewExamples','EmployeesJob','Message',NULL,
'<xml_rule_properties description=""><eventHandlers>
<eventHandler class="com.archibus.app.solution.common.webservice.employee.EmployeesJob" method="importAllEmployees">
<inputs/></eventHandler></eventHandlers></xml_rule_properties>')
System:
- Move the localization process to System domain so that it is retained in the schema.db (3035480)
- Move the localization tasks to a new "Localization Manager" process beneath the Add In Manager process in the System Admin activity.
- Remove the afm_actprods record for AbSolutionTemplates-Localization.
- Remove the AbSolutionsLocalization afm_activity record- Update the PNav icons
- Make the Equipment Standard Edit form consistent throughout all Domains ( 3038787 )
- Add license levels to new PNav processes ( 3037314 )
- AIADMIN and ADMIN missing user management process ( 3037368 )
- Add all processes for the 'AbSystemAdministration' activity to each of these roles in the afm_roleprocs table:
4 - SYSTEM ADMINISTRATOR
SYSTEM ADMINISTRATOR
SYSTEM ADMINISTRATOR (ACP)
- The Drawing tasks under the Add-In Manager on the Smart Client Process Navigator would be easier to find under the CAD and BIM Manager Role (3039287)
Asset Mgmt:
- Add Space and Org Hierarchies to the background menu for Asset Mgmt ( 3038786 )
- Add Service Contracts and CSI to Process Navigator ( 3038793 )
- On the Asset Managment/Asset Portal/Background Date PNav process, there is inconsistency in use of verbs in task names ( 3038979 )
BldgOps:
- Manage Un-Submitted Requests view is available only in Service Desk, not On Demand Work ( 3038894 )
- Add tasks for defining Work Teams to Building Operations Business Process Owner Process ( 3026500 )
Env. & Risk:
- EH&S - Correct two tasks duplicated for Define Employee (3037198)
- Green Bldgs - Add 2 reports to show Deleted Buildings Carbon Footprint data (3030622)
- Emergency Prep additions for Egress and Haz Mat plans ( 3038007 )
- Emergency Prep additions for Business Value Scripts ( 3038230 )
Mobile:
- Add "Mobile Apps Manager" Process under System Administration / ARCHIBUS System Administration, just beneath the CAD and BIM Manager
Project Mgmt:
- Add "Define Contacts" view to the "Set Up" process for Proj. Mgmt. and to the "Background Data" process for Lease Administration. (3037410)
REPM:
- Add PNav changes for Cost Indexing and CAM Cost Reconciliation.
- Change navigator entries for some reports ( 3038840 )
- Remove old entries; add new entries for new views ( 3038840 )
Space:
- Navigation shows both non-transaction and transaction for space management ( 3038971 )
- In the Building Performance Smart Client CAD process, add a Set Buildings task. CAD Specialists use this task to draw buildings on their campus plan.
Solutions:
- Add new Solutions Template view for Display Temporary Message (3037712).
- GIS - Update GIS task descriptions to be more consistent and to more accurately reflect their purpose; distinguish Flash controls from JavaScript. (3036729)
- Reusable components - Add example of reusable component 'Enable or Disable field actions' ( 3037885
- Add 21.1 Solution Templates views to Process Navigator ( 3038796 )
- Correct example view demonstrating the row clicks events ( 3038969 )
The list below primarily covers data changes that may affect user projects or sample data issues that cause exception errors. Most HQ project sample data changes, of which there were many, are not listed.
System:
- activities table - remove translations ( 3037025 )
UPDATE afm_activities SET summary_ch=NULL, summary_de=NULL, summary_es=NULL, summary_fr=NULL, summary_
it=NULL, summary_nl=NULL, title_ch=NULL,title_de=NULL, title_fr=NULL, title_it=NULL, title_nl=NULL WHERE activity_id='AbRiskCompliance'
- One record of afm_dwgs data is incorrect and causes an exception in the SC (3037489).
UPDATE afm_dwgs SET dwg_file = 'hqb1.dwg' WHERE dwg_name = 'HQB1';
- Updates to afm_roleprocs table:
Add 'Executive Reports' and 'Process' processes for all active applications to the 'EXECUTIVE MANAGER' and 'EXECUTIVE MANAGER (ACP)' roles
Add all processes for all active activities to each of these roles in the afm_roleprocs table:
4 - PROCESS OWNER
4 - PROCESS OWNER (ACP)
ACTIVITY LICENSEE
Add all processes for the 'AbSystemAdministration' activity to each of these roles in the afm_roleprocs table:
4 - SYSTEM ADMINISTRATOR
SYSTEM ADMINISTRATOR
SYSTEM ADMINISTRATOR (ACP)
CPM:
- Add activity parameter for Facility Condition Index: ( 3037905 )
INSERT INTO afm_activity_params(activity_id,transfer_status,param_id,description,param_value) VALUE
('AbCapitalPlanningCA','NO CHANGE','FacilityConditionIndex','Facility Condition Index formula','(1000 * ( activity_log.cost_estimated + activity_log.cost_est_cap) / ${sql.replaceZero(''activity_log.cost_to_replace'')})');
- Create a Generic Project Template ( 3037968 )
Mobile:
- Add some drawing publishing rules for mobile apps:
| Rules Code | Title | Active | Type | Additional Layers |
| AbPubBlSvg | Publish Buildings for Campus Plans | Yes | SVG | BL*;PL*;SI*; |
| AbPubRmSvg | Publish Rooms for Floor Plans | Yes | SVG | RM*;DR;WA-EXT;WA;WN;GRID;SI-MISC;PL |
| AbPubRmAndEqSvg | Publish Rooms and Equipment for Floor Plans | Yes | SVG | RM*;EQ*;DR;WA-EXT;WA;WN;GRID;SI-MISC;PL |
- Add the device ID to the GUEST-MOBILE account. ( 3039577 )
REPM:
- Add activity_parameters for Cost Indexing and CAM
INSERT INTO afm_activity_params(activity_id,applies_to,transfer_status,param_id,description,param_value VALUES ('AbRPLMCosts','NA','NO CHANGE','Base_Rent_Category','Alternative names for the RENT-BASE RENT recurring cost category','RENT - BASE RENT');
INSERT INTO afm_activity_params(activity_id,applies_to,transfer_status,param_id,description,param_value) VALUES ('AbRPLMCosts','NA','NO CHANGE','CAM_Estimate','Alternative names for the ''RENT-CAM ESTIMATE'' cost category','RENT - CAM ESTIMATE');
INSERT INTO afm_activity_params(activity_id,applies_to,transfer_status,param_id,description,param_value) VALUES ('AbRPLMCosts','NA','NO CHANGE','CAM_Reconciliation','Alternative names for the ''RENT - CAM RECONCILIATION'' cost category','RENT - CAM RECONCILIATION');
- Add new Cost Categories CAM(XML file)
INSERT INTO cost_cat(rollup_prorate,cost_cat_id,cost_class_id,cost_type,option1,option2) VALUES ('LEASE-BLDG-NONE','RENT - CAM ESTIMATE','OPERATING COST','OPERATING EXP.',NULL,NULL);
INSERT INTO cost_cat(rollup_prorate,cost_cat_id,cost_class_id,cost_type,option1,option2) VALUES ('LEASE-BLDG-NONE','RENT - CAM RECONCILIATION','OPERATING COST','OPERATING EXP.',NULL,NULL);
Service Desk:
- SLA responses - make autoschedule and autoissue match (3037134)
UPDATE helpdesk_sla_response SET autoschedule = 1 WHERE autoschedule = 0 AND autoissue = 1;
- Add data into afm_activity_params (Mobile)
INSERT INTO afm_activity_params (description,param_value,activity_id,param_id,transfer_status,applies_to)
Values('Asset Management Equipment Field to Survey','bl_id;fl_id;rm_id;em_id;eq_id;status','AbAssetManagement','EquipmentFieldsToSurvey','NO CHANGE','NA')
- Fix default database SLA records ( 3037134 )
UPDATE helpdesk_sla_response SET autoschedule = 1 WHERE autoschedule = 0 AND autoissue = 1; (1 record to be updated)
Reservations:
- Comments should be visible in reservations work requests. One record inserted into messages table ( 3038222 )
Energy Mgmt:
- Set default values for Energy Management bill units ( 3039573 )
UPDATE bill_unit SET is_dflt = 1 WHERE bill_type_id = 'ELECTRIC' AND bill_unit_id = 'KWH';
UPDATE bill_unit SET is_dflt = 1 WHERE bill_type_id = 'GAS - NATURAL' AND bill_unit_id = 'CCF';
UPDATE bill_unit SET is_dflt = 1 WHERE bill_type_id = 'WATER' AND bill_unit_id = 'TGAL';
System
- Make sure all SQL views do not try to cascade: UPDATE afm_flds, afm_tbls SET validate_data = 0 WHERE afm_flds.table_name = afm_tbls.table_name AND is_sql_view = 1 AND ref_table IS NOT NULL AND validate_data = 1; - Change size of afm_data_event_log.field_list from 800 to 900 (3036082)
Service Desk
- Work orders can't be closed when there is a Redlined drawing attached ( 3035526 ) Drop the foreign key from afm_redlines.activity_log_id to the activity_log table. - Provide for workflow substitutes for all steps. (3023429) - DELETE FROM afm_flds WHERE table_name = 'workflow_substitutes' AND field_name = 'substitute_id'; - Import afm_flds_workflow_substitutes.xml Update Schema - recreate table. DATA: Import: messages_workflow_substitutes.xml Import: afm_activity_params_workflow_substitutes.xml
Building Operations
- Two fields have the same ML Heading (3036959): ML heading for helpdesk_sla_response.interval_to_complete should be “Interval to Complete” ML heading for helpdesk_sla_response.interval_to_respond should be “Interval to Respond”
Moves
- set "Validate Data?" to No for activity_log.mo_id. (3035395)
Reservations
- Remove obsolete fields from the afm_flds for the resview SQL view (3035968) guests_external guests_internal is_continuous is_recurring
US Federal Property Registry
- Data dictionary updates for US Real Property Federal Registry 2011 changes (3036940) Import grp table field additions: items-3036940-doc1.xls Import grp_trans table field additions: items-3036940-doc2.xls - label for field "Is Asset Potential Candidate For Sale? (30.a):" (sale_candidate, appears in table grp and grp_trans) is incorrect (3036856) >> "Is Asset Potential Canidate For Sale? (30.a):" (canDidate) - grp.lease_option_to_term_early is an enumerated field and should have default value defined as “N”; and Allows Null value set to “No”.
Compliance
- Incorporate schema changes for Compliance (3035674) - In afm_flds, change name (field_name) of notifications.notity_type to notify_type to fix typo (from kb 3035753). Also change notify_templates.notity_type to notify_type to fix typo. - In afm_flds, delete record for field regulation.web_ref2 (from kb 3035768).
Environmental Health and Safety
- v20.2 EHS changes -- NOTE: Compliance changes must be done first. (3035965) - UPDATE afm_flds SET ref_table = NULL, dep_cols = NULL WHERE table_name = 'ehs_restrictions' AND field_name = 'date_actual'; (3035549) - Two document fields size must be increased to avoid errors. (3035243) ehs_em_ppe_types.doc - needs to be changed from 64 to 144 ehs_training_results.doc - needs to be changed from 64 to 96 - Update field ML Headings including MTCO2 units or similar ones. (3030889) - Change Footprint sources tables field heading for kg_co2. (3030661) - activity_log table: description field - expand to 4000 characters in order to hold info from EHS incididents reduce hcm_loc_notes to 1,000 reduce recurring_rule to 512 *** Make the same changes above in the hactivity_log table and any related SQL view!!!*** - Add Help links for EHS Processes (3036262) - EHS/Incident Reporting -- \Subsystems\webc\Content\ehs\employee_review\incident_reporting.htm - EHS/Management Reports -- \Subsystems\webc\Content\ehs\management_reports\mgmt_reports.htm - UPDATE afm_flds SET ml_heading = 'Delivery Receipt' WHERE table_name='ehs_em_ppe_types' AND field_name='doc'; (3036726) - Remove a total of 6 records, all of which have already been replaced with new ones that shorten the field names to st_reg% or lt_reg%: DELETE FROM afm_flds WHERE table_name = 'ehs_incidents' AND field_name LIKE 'short_term_reg%'; DELETE FROM afm_flds WHERE table_name = 'ehs_incidents' AND field_name LIKE 'long_term_reg%'; - incident_id field is missing from the hactivity_log table (3037218) INSERT INTO afm_flds ( afm_module,afm_size,afm_type,allow_null,attributes,comments,data_type,decimals,dep_cols,dflt_val,edit_group,edit_mask,enum_list,field_grouping,field_name,is_atxt, is_tc_traceable,max_val,min_val,ml_heading,num_format,primary_key,ref_table,review_group,sl_heading,string_format,table_name,validate_data,transfer_status) SELECT afm_module,afm_size,afm_type,allow_null,attributes,comments,data_type,decimals,NULL,dflt_val,edit_group,edit_mask,enum_list,field_grouping,field_name,is_atxt, is_tc_traceable,max_val,min_val,ml_heading,num_format,primary_key,NULL,review_group,sl_heading,string_format,'hactivity_log',validate_data,transfer_status FROM afm_flds WHERE table_name = 'activity_log' AND field_name = 'incident_id'; THEN run USW on hactivity_log
Waste
- waste_out.container_cat field ml heading - Change from "Container Type" to "Container Category" (3035138)
MSDS
- Remove exterraneous FK references from msds_h_% tables (3035496) UPDATE afm_flds SET ref_table=NULL WHERE table_name = 'msds_h_haz_classification' AND field_name = 'msds_id'; UPDATE afm_flds SET ref_table=NULL WHERE table_name = 'msds_h_location' AND field_name = 'msds_id'; UPDATE afm_flds SET ref_table=NULL WHERE table_name = 'msds_h_constituent' AND field_name = 'msds_id'; UPDATE afm_flds SET ref_table=NULL WHERE table_name = 'msds_h_constituent' AND field_name = 'chemical_id'; -- Then run update schema on tables like msds_h_haz_classification;msds_h_location;msds_h_constituent - Correction to afm_type value for two fields (3036131) UPDATE afm_flds SET afm_type = '2050' WHERE field_name = 'msds_id' and table_name = 'msds_h_data'; UPDATE afm_flds SET afm_type = '2050' WHERE field_name = 'auto_number' and table_name = 'msds_h_location';
Localization
- To get the new fields from the last update into afm_flds_lang run: INSERT INTO afm_flds_lang (table_name, field_name) SELECT table_name, field_name FROM afm_flds WHERE NOT EXISTS (SELECT 1 FROM afm_flds_lang afm_flds_lang_inner WHERE afm_flds_lang_inner.table_name = afm_flds.table_name AND afm_flds_lang_inner.field_name = afm_flds.field_name )
System
- Change wfr name. (3035625) from: AbSystemAdministration-SecurityTablesDataChangeLogger to: AbSystemAdministration-DataChangeLogger - AddViewToMyFavorites and RemoveViewFromMyFavorites workflow rules are assigned the SYSTEM MGR security group. Remove the security group from them. (3036618) - Notifications - add scheduled WFR: ComplianceNotificationSchedule Import ScheduleNotificationService - wfr.xml - Add a new WFR used for statistic rows (min, max, average and totals for grid with grouping data source ) calculated on server. (3035797; 3036523) Import StatisticDataService_wfr.xml
Compliance
- Add WFRs
Project Management
- Add new WFR for Project Mgmt "S" Curve reports: eventHandler class="com.archibus.app.projectmgmt.SCurveHandlers" activity_id="AbProjectManagement" rule_id="SCurveHandlers" rule_type="Message"
System
- Add the "Run Report Only View Definition Wizard" to the PNav (3027601) - Add: Path: Technologies / User Interface Add-Ins / Geographical Information System (GIS) Views / Locate Asset Example URL: http://localhost:8080/archibus/ab-arcgis-locate-asset.axvw - Changes in Archibus Data Dictionary views (3031358) Application: AbSystemAdministration Process or Role: Add-In Manager: i. Rename the task View ARCHIBUS Data Dictionary to: Edit ARCHIBUS Data Dictionary ii. Add the task Edit ARCHIBUS Tables Dictionary with the Task File: ab-data-dictionary-tables.axvw iii. Rename the task View ARCHIBUS Field Dictionary to: Edit ARCHIBUS Fields Dictionary - SC Assign Processes to Roles task record does not match task file (3037073) System Administration > ARCHIBUS System Administrator > ARCHIBUS Administrator - Users and Security > Assign Processes to Roles --- change view type to "web URL".
Space Transactions
- DELETE from afm_roleprocs WHERE process_id LIKE '%rmtrans%'; - Move "Enable or Disable Workspace Transactions" task to System Administration ( 3035693 ) Under "System Administration / ARCHIBUS System Administration / ARCHIBUS Administrator - Application Configuration" create a new Label at the end called "Domain-Specific" and put the task "Enable or Disable Workspace Transactions" under it. - Add Define Geo-Locations task to Space. Import: afm_ptasks_add_ab_def_geo_loc_to_space.xml
Real Estate Portfolio Management
- Add the following views for Lease Communications (3036574) Add the following editable views, grouped as "Lease Communications", under: Real Estate Portfolio Management / Lease Administration / Lease Portfolio /: Communication Log Items: ab-repm-lsadmin-comm-log.axvw Communication Log Items by Lease: ab-repm-lsadmin-comm-log-by-lease.axvw Communication Log Items by Activity Log Item: ab-repm-lsadmin-comm-log-by-activity-log.axvw Communication Log Items by Activity Log Item by Project: ab-repm-lsadmin-comm-log-by-project.axvw and the following reports, grouped as "Lease Communications" , under: Real Estate Portfolio Management / Lease Administration / Reports /: Communication Log Items: ab-repm-lsadmin-comm-log.axvw Communication Log Items by Lease: ab-repm-lsadmin-comm-log-by-lease.axvw Communication Log Items by Activity Log Item: ab-repm-lsadmin-comm-log-by-activity-log.axvw Communication Log Items by Activity Log Item by Project: ab-repm-lsadmin-comm-log-by-project.axvw Communication Log Items by Contact: ab-repm-lsadmin-comm-log-by-contact.axvw Communication Log Items by Date: ab-repm-lsadmin-comm-log-by-date.axvw Add also the new view "Define Communication Types" (ab-def-comm-types.axvw) to the PNav's Background Data for: - Lease Admin - Project Management - Cost Chargeback and Invoicing / BPO -- add Define Contacts view (3028178)
Capital Budgeting
- New view like "View Unallocated Program Funds" - but with the end date sort and highlights. ( 3030852 ) Put on Pnav just below the above view: Process:Capital Project Management > Capital Budgeting > Allocate Task Id: View Unallocated Funds by End Date Task File: ab-funds-available-by-end-date.axvw
Project Management
- Add S-Curve view (3030871). Path: Capital Project Management / Project Management / Monitor / View S-Curve Analysis file: ab-proj-s-curve.axvw
Environmental and Risk
- Change PNav order to be the following: (3037183) Emergency Preparedness Compliance Management Environmental Sustainability Assessment Environmental Health and Safety Clean Building Material Safety Data Sheets Waste Management Energy Management Green Building
Emergency Preparedness
- Correct task: Application = 'AbRiskEmergencyPrepardness' / Process or Role = 'Define Systems and Zones (SCCAD)' / Task = 'Publish Enterprise Graphics' (3035646) Set the Task Action to (a single line, but appears as 2 in this kb): ^C^C(AfmPubFlashBackground "")(c:AfmPublish_ExampleMultipleFilesPerAssetType)
EHS
- Modify PTask label for EHS Track Employee Training (3036969) UPDATE afm_ptasks SET task_id = 'Track Training Program Assignments and Results' WHERE task_file = 'ab-ehs-track-em-training.axvw' AND process_id = 'EHS - Track'; - DELETE FROM afm_ptasks WHERE activity_id ='AbRiskEHS' AND process_id = 'EHS - Documentation' AND task_id = 'Track EHS Documents'; - Remove “Define Training Chemicals/Substances” from afm_ptasks. This task is under the "EHS - Background Data" process. (3036376) - Modify ptask label for two EHS processes (3036968) UPDATE afm_ptasks SET task_id = 'Incidents by Location' WHERE task_file = 'ab-ehs-rpt-incident-details.axvw' AND process_id = 'EHS - Operational Reports'; UPDATE afm_ptasks SET task_id = 'Incidents by Type' WHERE task_file = 'ab-ehs-rpt-incident-report.axvw' AND process_id = 'EHS - Operational Reports'; - Modify ptask label for two EHS processes (3036968) UPDATE afm_ptasks SET task_id = 'Incidents by Location' WHERE task_file = 'ab-ehs-rpt-incident-details.axvw' AND process_id = 'EHS - Operational Reports'; UPDATE afm_ptasks SET task_id = 'Incidents by Type' WHERE task_file = 'ab-ehs-rpt-incident-report.axvw' AND process_id = 'EHS - Operational Reports';
Energy
- Remove Win C/S afm_hotlist views for Energy that reference non-existent .avws. (3036568)
Commissioning
- Add the Proj. Mgmt. Setup process to the Commissioning application. - Change PNav task title (3035815) UPDATE afm_ptasks SET task_id='Add or Edit Work Packages' WHERE task_file='ab-proj-create-work-pkgs.axvw'; - Add Task "View and Edit Equipment Commissioning Details" (3035406) To be placed under Capital Project Management > Commissioning > Post-Construction > View and Edit Equipment Information activity_id = 'AbProjCommissioning' process_id='Cx Post Construction' task file: ab-comm-eq-details.axvw - Rename some PTasks (3037222) Capital Project Management / Commissioning / Post-Construction / View and Edit Equipment Commissioning Details 1) Change order on PNav to put this at the top of the process. 2) Rename it to: "Commission Equipment" Capital Project Management / Commissioning / Design / Approved Commissioning Specifications Change to: "Review Approved Commissioning Specifications" Capital Project Management / Commissioning / Construction / Assigned Commissioning Agents Checklists Change to: "Assign Checklists to Commissioning Agents" Capital Project Management / Commissioning / Construction / Commissioning Project Scoreboard Change to: "Scoreboard Commissioning Projects" Capital Project Management / Commissioning / Construction / Equipment Assessments by Classification Level Change to: "View Equipment Assessments" Capital Project Management / Commissioning / Construction / Commissioning Project Close-out Change to: "Close Out Commissioning Projects" Capital Project Management / Commissioning / Process / Asset Life Cycle by Floor Remove this view and the 2 below it. Replace with: "Commission Equipment" task file: ab-comm-eq-details.axvw
Corrections
- Space Planning and Managemen/ Space Inventory and Performance/ Building Performance (3030625) Notice that the Highlight Suites by lease command is available in this process. Instead this should be placed in the Real Estate Portofolio Management/ Lease Administration/ Suite Analysis process
System
- Add activity_parameter fields for use by the Report only View Def Wiz (3027601)
Import: items-3027601-doc1.xml
- In messages table, set [Is Rich Text Format?] field to Yes for formatted records (3036565)
UPDATE messages SET is_rich_msg_format=1 WHERE message_text LIKE '%{%}%';
- Fix inconsistencies in the default user accounts, particularly in the ADMIN account. (3036103)
(1) Change the "Entire ARCHIBUS System -- System Administrator" ARCHIBUS role:
o Change Role Name to "System Administrator (ACP)"
o Change title to "System Administrator (ACP)". (to be consistent with System Administrator -- the domain-style role).
o Change License Level to Activity ACP (from 4 - Process Owner).
Without the change above, the ADMIN user cannot log in to correct licenses on sites with activity-style licensing.
Sites that do not automatically delete unlicensed activities for user accounts are then stuck.
(2) Change "5 - SYSTEM ADMINISTRATOR" ARCHIBUS role:
o Change the role name to "4 - SYSTEM ADMINSTRATOR" (to match the name of it's Licence Level).
(3) Change the "EXECUTIVE MANAGER (ACP)" role:
o Change the title to "Executive Manager (Activity-Style License)" (to match the other activity-style roles).
(4) Change the "AFMDEMO2" ARCHIBUS user to be "2 - WORKFLOW PROCESS (ACP)".
- Give all sample data afm_users unique email addresses (3036914)
AI*, AFM*, and RESERVATION* users will need corresponding em records with matching email addresses.
UPDATE afm_users SET email = LCASE(user_name) || '@tgd.com' WHERE user_name LIKE 'AFM%' OR user_name LIKE 'AI%';
INSERT INTO em (em_id, email) SELECT user_name, email FROM afm_users
WHERE (user_name LIKE 'AFM%' OR user_name LIKE 'AI%') AND user_name NOT IN ('AFM','AI');
UPDATE em SET bl_id = 'HQ', dv_id = 'ELECTRONIC SYS.', dp_id = 'ENGINEERING', em_std = 'EXEC-SR'
WHERE (em_id LIKE 'AFM%' OR em_id LIKE 'AI%') AND em_id NOT IN ('AFM','AI');
INSERT INTO em (em_id, email, bl_id, dv_id, dp_id) SELECT user_name, email, 'HQ', 'FACILITIES', 'CONSTRUCTION'
FROM afm_users WHERE user_name LIKE 'RESERVATION%';
- Add two users that have the executive manager role (to which we assign all the business-value demo pnavs) but that do not have a "demo" account:
User AIX - Role EXECUTIVE MANAGER
User AFMX - Role EXECUTIVE MANAGER (ACP)
Reservations
- Add the ARCHIBUS Room Reservations plugin activity params (3035757) afm_activity_params.activity_id param_id param_value description ------------------------------------------------------------------------------------------------------------------------------------------------------- AbWorkplaceReservations PlugInFullReservationView ab-rr-my-reservations.axvw View loaded by the ARCHIBUS Room Reservations Plug In for Outlook when the user loads the full Web Central View. AbWorkplaceReservations PlugInRoomInformationView ab-rr-rm-arrange-details-urlparams.axvw View loaded by the ARCHIBUS Room Reservations Plugin for Outlook when the user presses the "I" button to get more information on the room.
Extended Global Feature Set
- Allow DEMO users to view data WHERE bl_id is Null. (3035132) UPDATE afm_users SET bl_id_list = bl_id_list || ', NULL' WHERE bl_id_list IS NOT NULL AND user_name LIKE '%DEMO%'; - SmartClient Connection failure on MSSQL due to bad translation data included in the definition for afm_conversions. (3036037) UPDATE afm_tbls SET title_ch = NULL, title_zh = NULL, title_de = NULL, title_fr = NULL, title_it = NULL, title_es = NULL, title_jp = NULL, title_ko = NULL, title_no = NULL, title_nl = NULL WHERE table_name = 'afm_conversions';
Service Desk
- Fix default database SLA records where Autoschedule != Autoissue. (3037134) UPDATE helpdesk_sla_response SET autoschedule = 0 WHERE autoschedule = 1 AND autoissue = 0;
Compliance
- hpattern values in table regulation are not recognized by AFM W/C (3036657) UPDATE regulation SET hpattern = ' ' + hpattern WHERE hpattern IS NOT NULL;
EHS
- Add incident notifications messages (3030075)
Energy
- Restore weather model station assignments to sample building table records (3037113)
System:
ALTER TABLE afm_metric_definitions ADD FOREIGN KEY afm_metric_definitions_collect_date_field(collect_table,collect_date_field)
REFERENCES afm_flds(table_name,field_name) ON UPDATE CASCADE ON DELETE CASCADE;
BIM
activity_log;afm_dwgs;afm_dwgvers;afm_wf_rules;bl;ca;city;county;ctry;dr
em;em_compinvtrial;em_trial;eq;eq_compinvtrial;eq_trial;fl;
fn;fn_compinvtrial;fn_comptrialtrial;fn_trial;fp;gp;grid;gros;hactivity_log;jk
netdev;parcel;parking;pb;pn;property;rack;regcompliance;rf;rm;rm_trial;runoffarea
serv;site;state;su;ta;ta_compinvtrial;ta_trial;vert;wn;wy;zone
Space Transactions
rm (new column org_id; change to data type of count_em column)
questions (addition to the enumerated list of the existing format_type column)
mo (new column activity_log_id)
helpdesk_sla_response (new column activity_id) (3032099)
fl (change to data type of count_em column)
bl (change to data type of count_em column)
site (change to data type of count_em column)
rmcat (change to data type of count_em column)
rmtype (change to data type of count_em column)
Bldg Ops
UPDATE afm_flds SET dflt_val = 'none' WHERE (table_name ='helpdesk_step_log' AND field_name='step_status_result')
R (table_name IN ('activity_log','hactivity_log','activity_log_hactivity_log','wr','hwr','wrhwr','helpdesk_sla_steps','hactivity_logmonth','activity_logview','wrview') AND field_name='step_status')
OR (table_name='afm_wf_steps' AND field_name IN ('step_status_result','step_status_rejected'))
UPDATE afm_flds SET enum_list = 'none;None;approved;Approved;accepted;Accepted;surveyed;Surveyed;verified;Verified;dispatched;Dispatched;estimated;Estimated;scheduled;Scheduled;rejected;Rejected;declined;Declined;waiting;Waiting for Step'
WHERE table_name IN ('activity_log','hactivity_log','activity_log_hactivity_log','wr','hwr','wrhwr','hactivity_logmonth','activity_logview','wrview') AND field_name='step_status'
UPDATE afm_flds SET enum_list='none;None;rejected;Rejected;declined;Declined' WHERE table_name='afm_wf_steps' AND field_name='step_status_rejected'; UPDATE afm_flds SET enum_list='none;None;approved;Approved;accepted;Accepted;surveyed;Surveyed;verified;Verified;dispatched;Dispatched;estimated;Estimated;scheduled;Scheduled' WHERE table_name='afm_wf_steps' AND field_name='step_status_result';
UPDATE afm_flds SET enum_list='none;None;approved;Approved;accepted;Accepted;surveyed;Surveyed;verified;Verified;dispatched;Dispatched;estimated;Estimated;scheduled;Scheduled;rejected;Rejected;declined;Declined;forwarded;Forwarded' WHERE table_name='helpdesk_step_log' AND field_name='step_status_result'; ALTER TABLE activity_log MODIFY step_status DEFAULT 'none'; ALTER TABLE hactivity_log MODIFY step_status DEFAULT 'none';
ALTER TABLE wr MODIFY step_status DEFAULT 'none';
ALTER TABLE hwr MODIFY step_status DEFAULT 'none';
ALTER TABLE helpdesk_sla_steps MODIFY step_status DEFAULT 'none';
ALTER TABLE helpdesk_step_log MODIFY step_status_result DEFAULT 'none';
ALTER TABLE afm_wf_steps MODIFY step_status_result DEFAULT 'none';
ALTER TABLE afm_wf_steps MODIFY step_status_rejected DEFAULT 'none';
Multi Currency
afm_currenciesvat_percentafm_conversionscost_tran%invoice%ls
afm_users table -- Update locale enum display values to include the Country since this value is also used for determining a user's default currency. (3032802)
| Locale | Current Value | To be Changed |
| ar_SA | Arabic Arabic | (Saudi Arabia) |
| zh_CN | Chinese(Simplified) | Chinese (People's Republic of China) |
| zh_TW | Chinese(Traditional) | Chinese (Taiwan) |
| nl_NL | Dutch | Dutch (Netherlands) |
| de_DE | Germany | German (Germany) |
| iw_IL | Hebrew | Hebrew (Israel) |
| it_IT | Italian | Italian (Italy) |
| ja_JP | Japanese | Japanese (Japan) |
| ko_KR | Korean | Korean (Korea) |
| pt_BR | Portuguese | Portuguese (Brazil) |
| es_ES | Spanish | Spanish (Spain) |
Multi-Units
2245;Currency
UPDATE afm_flds SET afm_type = 2245 WHERE field_name = 'currency_budget' OR field_name = 'currency_payment' OR field_name = 'currency_invoice';
UPDATE afm_flds SET num_format = 3 WHERE field_name LIKE '%area%' AND data_type = 2;
UPDATE afm_flds SET num_format = 4 WHERE ( field_name LIKE '%length%' OR field_name LIKE '%width%' OR field_name LIKE '%height%' ) AND data_type = 2;
UPDATE afm_flds SET num_format = 1 WHERE ( field_name LIKE 'amount_income%' OR field_name LIKE 'amount_expense%' OR field_name LIKE 'vat_amount_override%' ) AND field_name NOT LIKE '%payment%';
UPDATE afm_flds SET num_format = 1 WHERE ( field_name LIKE 'amount%' AND table_name LIKE 'invoice%' );
UPDATE afm_flds SET num_format = 6 WHERE ( field_name LIKE 'amount_income%' OR field_name LIKE 'amount_expense%' OR field_name LIKE 'vat_amount_override%' ) AND field_name LIKE '%payment%';
Fed. Real Prop:
lease_maintenance_indoutgrant_indicatorutilizationdisposition_method
legal_interest_indlease_maintenance_indlease_authority_idstatus_indicator
outgrant_indicator
historical_status
size_unit_of_measureutilizationmission_dependencydisposition_method_idsustainability
Green Building
- add tot_annual_savings, tot_capital_cost, and tot_payback_period fields
- Change fields tot_final_score and tot_self_score from Numberic (5,0) to Numeric (8,2).
Change:
[Data Type] from Smallint to Numeric.
Allow Null? to No
Default Value to 0.
For the 3 fields [tot_annual_savings, tot_capital_cost, tot_payback_period], change Allow Null? to No. They already have a Default Value.
Clean Building
vn.vendor_type: append "LAB;Testing Lab" to Enumeration List.
Change the size and data type of vn.web_url in afm_flds from Char 64 to Varchar 256 to make the url field conform with the url precedent in other existing tables.
contact.contact_type: append "HAZMAT;HAZMAT" to Enumeration List.
repairty.repair_type: Increase Size from 16 to 32.
Also apply to all FKs to this field.
helpdesk_sla_steps.ordering_seq
activity_log.po_id
activity_log.regcomp_id
cost_tran_sched.activity_log_id
helpdesk_sla_response.ordering_seq
Waste Management
Material Safety Data Sheets
Compliance
Added table and field definitions
Change the enumeration list for the fields is_requirement and sched_loc of regcompliance table in afm_flds to: "0;No;1;Yes". (3034487)
Change the ml_heading in afm_flds for regulation.reg_category to "Regulation Super Category". (3034461)
regulation.regulation: Change AFM Type from None to Hierarchical. (3034333)
regulation.related_reg refers to the regulation table. Set "Validate Data?" to NO. (3034891)
Environmental Health & Safey
Added table and field definitions: ehs%;docs_assigned;work_categ%
Energy Management
Asset Management
Reservations
Project Mgmt/Commissioning:
Change the ML Heading of the field project.project_id from "Project Name" to "Project Code."
Add a new field: project.project_name --- character (64), allow null, non-validating; ML Heading of "Project Name"
Add new field: funding_probability
Change Multi-Line Heading for field project.doc from ‘Project Document’ to ‘Project Business Case’.
Add three additional document fields to project table using project.doc as a template:
| Field Name | ML Heading |
| doc_risk_mgmt | Project Risk Management |
| doc_charter | Project Charter |
| doc_impl_plan | Project Implementation Plan |
| pct_complete | “Work Package % Complete” |
| pct_complete_cx_docs | “Cx Documentation % Complete” |
| cx_docs_provided_by | “Cx Documentation Provided By” validates on Vendor table (vn_id) |
| date_cx_docs_provided | “Date Cx Docs Provided” |
| cx_docs_verified_by | “Cx Documentation Verified By” |
| date_cx_docs_verified | “Date Cx Docs Verified” |
| cx_docs_approved_by | “Cx Documentation Approved By” |
| date_cx_docs_approved | “Date Cx Docs Approved” |
Cost Admin
Forecasting
enum_trans -- increase size to 1000
INSERT INTO afm_flds_lang (table_name, field_name)
SELECT table_name, field_name FROM afm_flds
WHERE NOT EXISTS (SELECT 1 FROM afm_flds_lang afm_flds_lang_inner
WHERE afm_flds_lang_inner.table_name = afm_flds.table_name
AND afm_flds_lang_inner.field_name = afm_flds.field_name );
System
UPDATE afm_wf_rules SET xml_rule_props = REPLACE( xml_rule_props,
'com.archibus.eventhandler.schemupwiz.ProjUpWizDbObjects','com.archibus.app.sysadmin.updatewizard.
schema.prepare.UpdateArchibusSchemaUtilities')
WHERE rule_id LIKE 'refreshDataDictionary';
DELETE FROM afm_wf_rules WHERE rule_id = 'AppUpWizService';
Space Transactions
Asset Management
Capital Budgeting
Waste Management, Clean Building, MSDS, and EH&S
Compliance
System
Change SmartClient "Assign Processes to Roles" view to use the one Web C uses: ab-sys-role-procs.axvw (3031428)
System Administration / ARCHIBUS System Administration / ARCHIBUS Administrator (SC) / Assign Processes to Roles
Path: System Administration / ARCHIBUS System Administration / System Administrator / Deployment Update Wizard
System Administration/ARCHIBUS System Administration/CAD and BIM Manager/ARCHIBUS BIM CategoriesSystem Administration/ARCHIBUS System Administration/CAD and BIM Manager/ARCHIBUS BIM FamiliesSystem Administration/ARCHIBUS System Administration/CAD and BIM Manager/ARCHIBUS BIM Parameters
Space and Transactions
Multi-Currency and VAT:
| Parts for Reports | |
| Report With Currency Expressions | ab-ex-report-grid-currency.axvw |
| Report With Area Fields | ab-ex-report-grid-units.axvw |
| Parts for Forms | |
| With Currency Expressions | ab-ex-form-panel-currency.axvw |
| Chart Views | |
| Chart With Currency Expressions | ab-chart-currency.axvw |
| Chart With Area Fields | ab-chart-units.axvw |
| Group Views | |
| Cross-table View With Currency Expressions | ab-ex-crosstable-currency.axvw |
| Cross-table View With Area Fields | ab-ex-crosstable-units.axvw |
| Business Process Owner Views / Paginated Reports | |
| Report With Area Fields | ab-ex-report-units.axvw |
| Report With Currency Expressions | ab-ex-report-currency.axvw |
Bldg Ops:
Service Desk:
Project. Management:
Capital Budgeting:
Hoteling:
Green Building:
Waste Management, Clean Building, MSDS, EH&S, Compliance and Commissioning applications
Solutions
| Application | Process or Role Task | Task File | |
| AbSolutionsViewsProcessOwner | Paginated Reports | Report with Area Fields | pagereports/ab-ex-report-units.axvw |
| Report with Currency Expressions | pagereports/ab-ex-report-currency.axvw | ||
| Report with Drawing and a Custom SQL Highlight Data Source | pagereports/ab-ex-dwg-sql.axvw | ||
System
AbSystemAdministration-DataChangeEventTablesToLog |
value: afm_users;afm_groups;afm_roles;afm_groupsforroles;afm_roleprocs |
AbSpaceRoomInventoryBAR-ResynchRoomTransactionsTable
|
default value "No"". |
AbSpaceRoomInventoryBAR-UseRoomTransactions
|
default value "No"
|
|
UPDATE AFM.afm_activity_params SET param_id='Acceptance_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='ACCEPTANCE_VIEW';
|
User's Changes
| INSERT INTO afm_roleprocs ( activity_id, process_id, role_name ) |
|
SELECT afm_processes.activity_id, afm_processes.process_id, '4 - PROCESS OWNER'
FROM afm_processes, afm_activities
WHERE afm_activities.activity_id = afm_processes.activity_id
AND afm_activities.is_active = 1 AND afm_processes.is_active = 1
AND NOT EXISTS (SELECT 1 FROM afm_roleprocs WHERE role_name = '4 - PROCESS OWNER' |
| INSERT INTO afm_roleprocs ( activity_id, process_id, role_name ) |
|
SELECT afm_processes.activity_id, afm_processes.process_id, '4 - PROCESS OWNER (ACP)'
|
| INSERT INTO afm_roleprocs ( activity_id, process_id, role_name ) |
|
|
| DELETE FROM afm_roleprocs WHERE (SELECT is_active FROM afm_processes WHERE |
|
|
Space Transactions
Multi-Currency / Multi-Unit / VAT:
Asset Management
BIM
Bldg Ops
Risk and Environment
| AIRCRAFT FUEL CONSUMPTION | litros/hora | 0.264172052358 |
| AIRCRAFT FUEL CONSUMPTION | liters/stunde | 0.264172052358 |
| AIRCRAFT FUEL CONSUMPTION | litres/heures | 0.264172052358 |
| DISTANCE-MILES | kilometros | 0.62137119223 |
| DISTANCE-MILES | km | 0.621371192237 |
Green Buildings
Clean Buildings
MSDS
In WebCentral, perform the following steps to re-load msds .pdf documents in to the document management system:
1. Navigate to: Path: Environmental & Risk Management / Material Safety Data Sheets / Manage MSDSs / Define Material Safety Data Sheets (MSDSs) URL: http://localhost:8080/archibus/ab-msds-def-msds.axvw
2. For each MSDS record shown in the middle panel:
a) Click the record
b) Click the Document tab in the bottom panel
c) If there is a value for “MSDS Source URL” in the form, click the >>Download action button.
d). Click >>Save
Environmental Health & Safety
Compliance
Commissioning
Add Document data:
|
1
|
Cx - Planning - Edit My Projects... Project Code: 2012-000009 Project Business Case ... Add doc: Filename: Business Case for Project 2012 PN002.pdf |
|
2
|
Cx - Planning -Create Work Packages Project Code: 2012-000008 ... Work Pkg: 8 - Specialties Elevators - 40% Design Submission ... REQUESTED Filename: Construction Checklist Div-14200 Elevator.pdf |
|
3
|
Elevators - 100% Design Submission ... APPROVED Filename: Construction Checklist Div-14200 Elevator.pdf Elevators - 60% Design Submission ... REQUESTED Elevators - 100% Design Submission ... APPROVED Filename: Construction Checklist Div-14200 Elevator.pdf |
|
4
|
Cx - Planing -Create Work Packages
Project Code: 2012-000008 ... Work Pkg: 4 - Automatic Temperature Control System (ATC) WBS Code: 4.07 Sequence control, AHU, H&V Action Type: CX - DESIGN SUBMISSIONS Status: REQUESTED Filename: Performance Verification Testing Div-15950 HVAC Controls.pdf |
END VERSION 20.1 CHANGES
Green Bldgs:
*** NONE ***
System:
Proj. Mgmt.:
System:
Green Bldgs:
Schema Preferences:
System:
BldgOps:
Cap Bud:
Green Bldgs:
This is needed to accommodate multi/mixed units support.It will be used by multi-unit enabled applications, such as Green Building, to filter bill_type entries to only their own entries. It will also be used by Energy Management to display only its own entries, which are those where activity_id is NULL. We’ve specified that multi-units applications will, in their Define Units task, show all units with activity_id equal to the ID of the activity OR AbAll. Some unit types are generic, like “VOLUME” or “DISTANCE”, that can be shared. Adding a dummy activity_id of AbAll to the activity table didn’t seem like a good option.
Localization:
System:
BldgOps:
Green Bldgs.:
System:
Space Mgmt:
Green Bldgs:
System:
SELECT afm_processes.activity_id, afm_processes.process_id, 'AI*'
FROM afm_processes, afm_activities
WHERE afm_activities.activity_id = afm_processes.activity_id
AND afm_activities.is_active = 1 AND afm_processes.is_active = 1
AND NOT EXISTS (SELECT 1 FROM afm_userprocs WHERE user_name = 'AI'
AND afm_userprocs.activity_id = afm_processes.activity_id
AND afm_userprocs.process_id = afm_processes.process_id );
Proj. Mgmt.:
Green Bldgs.:
BldgOps:
REPM:
Space:
Schema Preferences:
Schema changes:
System:
UPDATE afm_flds SET afm_type = 2070 WHERE enum_list IS NOT NULL AND dflt_val IS NULL AND afm_type <> 2070 AND table_name <> 'afm_wf_steps';Real Estate & Property Management:
Building Operations:
Reservations:
Capital Budgets:
Workflow Rules (WFR) Additions and Changes:
New WFRs added:
New WFRs added for Building Operations applications:
Energy Management:
Process Navigator (PNav) changes:
System:
DELETE FROM afm_products WHERE product_id = 'AbCapitalBudgets';
DELETE FROM afm_activities
WHERE NOT EXISTS (SELECT 1 FROM afm_processes WHERE afm_processes.activity_id = afm_activities.activity_id)
AND NOT EXISTS (SELECT 1 FROM afm_wf_rules WHERE afm_wf_rules.activity_id = afm_activities.activity_id);
Real Estate & Property Management:
Moves:
Space Planning & Inventory:
Building Operations:
Asset Management:
UPDATE afm_ptasks SET task_id = '...' + SUBSTRING( task_id, 6, LENGTH( task_id ) ) WHERE task_id LIKE ' %';
Capital Budgeting:
Solutions:
Software Engineering / Software Engineer Views:
User Interface Add-Ins / Parts for Forms:
User Interface Add-Ins / Parts for Reports:
Business Process Owner Views / Paginated Reports:
Data changes:
System:
| Field name | Field value |
|---|---|
| activity_id | 'AbSystemAdministration' |
| param_id | 'NotificationLoggingLevel' |
| param_value | 'off'|'subject'|'message' Default value is 'message' |
| description |
'Indicate the level of e-mail notification logging required. Valid values are: • "off": to disable logging; • "subject": to log only the subject; • "message": to log the complete message text.' |
| transfer_status | 'NO CHANGE' |
Localization:
Building Operations:
Project Management:
Space:
Real Estate & Property Management:
| Field name | Type and Size | Description |
| auto_number | Integer (10) | Auto-numbered PKey |
| email_from | varchar(50) | The e-mail address of the sender. |
| email_to | varchar(5000) | The e-mail addresses of the recipients, comma-delimited. |
| email_subject | varchar(200) | |
| email_message | varchar(10000) | |
| email_host | Varchar(256) | |
| server_date_sent | date | |
| server_time_sent | time | |
| status | char(16) | Enumerated value that shows the send status. Valid values are: "sent"; "error"; |
| status_message | varchar(1000) | The status message (typically, this is an error message). |
| activity_id | char(32) | The activity from which the message was sent. |
| Field name | Type and Size | Description |
| geo_region_id | Char 8 | Example values: AMER; EMEA; APAC |
| geo_region_name | Char 64 | Example values: AMER (Americas); EMEA (Europe, Middle East, Africa); APAC (Asia Pacific, Japan) |
| description | Varchar 1000 | |
| hpattern_acad | Char 16 | |
| option1 | Char 16 | |
| option2 | har 16 |
INSERT INTO afm_flds_lang (table_name, field_name)
SELECT table_name, field_name FROM afm_flds
WHERE NOT EXISTS (SELECT 1 FROM afm_flds_lang afm_flds_lang_inner
WHERE afm_flds_lang_inner.table_name = afm_flds.table_name
AND afm_flds_lang_inner.field_name = afm_flds.field_name )
DELETE FROM afm_ptasks WHERE process_id LIKE 'Group%' AND task_id = 'Number';
DELETE FROM afm_ptasks WHERE process_id LIKE 'Fur%' AND task_id = 'Number';
DELETE FROM afm_ptasks WHERE process_id LIKE 'Equip%' AND task_id = 'Number';
System:
Project Management:
SELECT project.bl_id, SUM(cost_est_design_exp) + SUM(cost_est_design_cap) FROM activity_log, project
WHERE activity_log.project_id = project.project_id
AND ( project.status LIKE 'Approved%' OR project.status LIKE 'Issued%' )
GROUP BY project.bl_id
Reservations:
A summary of database changes required per activity or domain when upgrading from v18.2 to v19.1 is listed in the below table.
| Activity or Domain | Tables/Fields Changed* | SQL Views changed | PNav Changed | afm_wf_rules changes |
| System | Yes | No | Yes | Yes |
| Asset Management | Yes | No | Yes | Yes |
| Space | No | No | Yes | No |
| Service Desk | No | Yes | Yes | Yes |
| On Demand Work | No | Yes | Yes | Yes |
| Move Management | Yes | No | Yes | Yes |
| Project Management | No | No | Yes | Yes |
| Reservations | No | Yes | No | Yes |
| Emergency Prep | No | No | Yes | Yes |
| REPM | No | No | Order and Titles only | Yes |
| Condition Assessment | No | No | No | No |
| Hoteling | Yes | No | Yes | Yes |
| Energy Management | Yes | No | Yes | Yes |
| Localization | Yes | No | Yes | Yes |
Note: When reviewing this list of changes, note that some of the View 1.0 view files or ARCHIBUS Windows Client/Server view files referenced in this document are now obsolete and no longer shipped.
Schema changes
System
UPDATE afm_tbls SET title = REPLACE( title, 'A/FM', 'ARCHIBUS') WHERE title LIKE '%A/FM%';
UPDATE afm_flds SET ml_heading = REPLACE( ml_heading, 'A/FM', 'ARCHIBUS') WHERE ml_heading LIKE '%A/FM%';
Remove the "/FM" from ml_heading for afm_scmpref.afm_cascades_changes
NOTE: value set for this field will be "1" even though the default is "2".
Add: rule_suffix -- "Suffix" -- Char(32)
Add: dwg_view -- "AutoCAD Drawing View" -- Char(32)
Change: rule_type:
- change default to "ALL"
- add values to enum for Ent. Graphics Publishing: NONE;None;ALL;All;SWF;SWF;EMF;EMF;SWF and EMF;SWF and EMF;JSON;JSON;BY OWNER;By Owner;LAYERED BY OWNER;Layered By Owner;QUERY TEXT;Query Text;QUERY TABLE;Query Table;ASSET;Asset;RESTRICTION;Restriction;URL;URL;DYNAMIC;Dynamic
Add: table_type -- "Table Type" -- Char(32) -- default: PROJECT DATA -- enum: PROJECT SECURITY;Project Security;PROJECT APPLICATION DATA;Project Application Data;PROJECT DATA;Project Data;DATA DICTIONARY;Data Dictionary;APPLICATION DICTIONARY;Application Dictionary;PROCESS NAVIGATOR;Process Navigator;
Add: default_view -- "Default View" -- char(64)
DOCUMENTATION NOTE:
The Update Project Wizard doesn't create the fields:
- afm_tbls.default_view
- afm_tbls.table_type
- afm_tbls.transfer_status
- afm_flds.transfer_status
- afm_groups.transfer_status
These are new fields for afm_flds, afm_tbls and afm_groups that the Update Project Wizard doesn�t add.
The solution is to run Update Project Wizard again with (Table like=afm_flds;afm_tbls;afm_groups)
UPDATE afm_tbls SET table_type = 'PROJECT SECURITY' WHERE table_name IN ( 'afm_groups','afm_users','afm_groupsforroles','afm_roles','afm_userprocs','afm_roleprocs' );
UPDATE afm_tbls SET table_type = 'PROJECT APPLICATION DATA' WHERE table_name IN ( 'afm_hotlist', 'afm_tclevel', 'afm_wf_steps' );
UPDATE afm_tbls SET table_type = 'DATA DICTIONARY' WHERE table_name IN ( 'afm_flds', 'afm_tbls', 'afm_flds_lang' );
UPDATE afm_tbls SET table_type = 'APPLICATION DICTIONARY' WHERE table_name IN ( 'afm_wf_rules','afm_atyp','afm_dwgpub','afm_layr', 'messages', 'afm_activity_params', 'afm_scmpref' );
UPDATE afm_tbls SET table_type = 'PROCESS NAVIGATOR' WHERE table_name IN ( 'afm_products','afm_activity_cats','afm_actprods','afm_activities','afm_processes','afm_ptasks','afm_psubtasks' );
autonumbered_id -- "Autonumbered ID" -- (pkey) Integer -- Default: AUTOINCREMENT
set_name -- "Data Transfer Set" -- Char (32)
table_name -- "Table Name" -- Char (32) Does not validate on afm_tables.
table_type -- "Table Type" -- Char(32)
nrecords_dest -- "Records in Destination Table" -- Integer
nrecords_source -- "Records in Source Extract" -- Integer
nrecords_inserted -- "Records Inserted" -- Integer
nrecords_updated -- "Records Updated" -- Integer
nrecords_missing -- "Records Missing" -- Integer
processing_order -- "Processing Order" -- SmallInt
status -- "Status" -- Char(16) -- Default: NONE -- Enum: NONE;None;PENDING;Pending;EXPORTED;Exported;COMPARED;
Compared;IMPORTED;Imported;NO EXTRACT FILE;No Extract File;NO PROJECT TABLE;No Project Table;NOT PROCESSED;Not Processed;
autonumbered_id -- "Autonumbered ID" -- (pkey) Integer -- Default: AUTOINCREMENT
** Add ALL fields from afm_flds table. Remove validating links.
change_type -- "Field Differs In" -- Char (32) Default: NONE -- Enum List:
NONE;None;NO_DEFAULT;Default Value not in Enum;NEW;Field is new;PROJECT_ONLY;Field is only in project;ALLOW_NULL;Is Null?;AFM_TYPE;AFM Type;ATTRIBUTES;Attributes;COMMENTS;Comments;DATA_TYPE;Data Type;DECIMALS;Decimals;DEP_COLS;Dependent Columns;DFLT_VAL;Default Value;EDIT_GROUP;Edit Group;EDIT_MASK;Edit Mask;ENUM_LIST;Enum List;FIELD_GROUPING;Field Grouping;IS_ATXT;Is Asset Text;IS_TC_TRACEABLE;Is Tc_Traceable;MAX_VAL;Maximum Val;MIN_VAL;Minimum Val;ML_HEADING;Multiline Heading;NUM_FORMAT;Numeric Format;PRIMARY_KEY;Primary Key;REF_TABLE;Reference Table;REVIEW_GROUP;Review Group;SIZE;Size;SL_HEADING;Single Line Heading;STRING_FORMAT;String Format;VALIDATE;Validate Data;data_dict_diffs -- "Data Dictionary Differences" -- VarChar[ 256 ]
sql_table_diffs -- "SQL Table Differences" -- VarChar[ 256 ]
rec_action -- "Action - Recommended" -- Char[ 32 ] -- Default: NO ACTION
Enum: NO ACTION;No Action;APPLY CHANGE;Apply Change;KEEP EXISTING;Keep Existing;DELETE FIELD;Delete Field;chosen_action -- "Action - Chosen" -- Char[ 32 ] -- Default: NO ACTION
Enum: NO ACTION;No Action;APPLY CHANGE;Apply Change;KEEP EXISTING;Keep Existing;DELETE FIELD;Delete Field;
;2175;Questionnaire;
| activity_log | act_quest |
| activity_log_hactivity_log | act_quest |
| activity_logview | act_quest |
| hactivity_log | act_quest |
| hactivity_logmonth | act_quest |
| mo | mo_quest |
| project | project_quest |
CREATE INDEX em_email ON em (email) (3025541)
afm_atyp.pop_layer
dp.approving_mgr
| afm_dwgs | model_level |
| afm_dwgs | model_name |
| afm_processes | license_level |
| afm_roles | license_level |
| afm_users | bim_license |
| afm_users | is_named_user |
Security:
UPDATE afm_flds SET review_group = NULL WHERE table_name <> 'afm_users' AND review_group IS NOT NULL AND ( primary_key > 0 OR ref_table IS NOT NULL);
Real Estate Portfolio Management Domain
Asset Management Domain
Move Management Domain
Service Desk/On Demand Activities
Right now, the work request ids show up as comma-separated because the numeric format is set to "default".
This should change to "NoSeparator". (3024523)
Run: System /Schema /Define /Update Service Desk and On Demand Work SQL Views
Space
Condition Assessment Activity
Activity_log.cond_priority
from: Conditions Priority to Condition Priority (remove "s" at the end of Conditions")Activity_log.cond_value
from: Conditions Value to Condition Value (remove "s" at the end of Conditions")
Reservations Activity
Hoteling Activity
Add: status -- "Status" -- SmallInt -- Default: 1 -- enum: 0;Requested;1;Approved
Add: resources -- "Resources" -- "Hoteling Resources Required" -- VarChar (5000) -- Memo
Add: visitor_id -- copy from visitors table; add validation; remove Pkey; Allow NUll
Add: day_part -- "Part of Day" -- SmallInt -- Default: 0 -- enum: 0;Full;1;Morning;2;Afternoon
Add: activity_log_id -- copy from cost_tran table
Add: parent_pct_id -- "Parent Percentage Code" -- Integer
Add: em_id -- copy from eq table; change ML Heading to: "Contact Employee"
Add: dv_id -- copy from eq table; change ML Heading to: "Contact Division"
Add: dp_id -- copy from eq table; change ML Heading to: "Contact Department"
Add: visitor_photo -- same as "em.em_photo"
Add: recurring_rule -- copy from reserve table
Add: hotelable -- "Is Hotelable?" -- SmallInt -- Default: 0 -- enum: 0;No;1;Yes
Add: rm_std -- copy from rm table; make Pkey 1; Not Null
Add: em_std -- copy from em table; make Pkey 2; Not Null
Add: doc_graphic -- copy eqstd.doc_graphic; change ML Heading
Add: doc_block -- eqstd.doc_block; change ML Heading
Add: approving_mgr -- copy work_pkg_bids.approved_by; change ML Heading to: "Approved By"; change Fields to Validate to: approving_mgr
Energy Management Activity
In addition to the fields for the tables listed above added fields to bl table
Localization:
ML Heading - "Default Locale to write to lang file"
CHAR - length 5
Default value - 'en_US'
INSERT INTO afm_flds_lang (table_name, field_name)
SELECT table_name, field_name FROM afm_flds
WHERE NOT EXISTS (SELECT 1 FROM afm_flds_lang afm_flds_lang_inner
WHERE afm_flds_lang_inner.table_name = afm_flds.table_name
AND afm_flds_lang_inner.field_name = afm_flds.field_name )
Work Flow Rules
System:
Activity: AbCommonResources
Rule: BasicRules
Activity: AbSystemAdministration
Rule: BasicRuleWizService
Activity: AbCommonResources
Rule: DataSourceService
Activity: AbCommonResources
Rule: JobManagerService
Activity: AbSystemAdministration
Rule: PasswordManagerService
Hoteling Activity
Service Desk/On Demand Work Activities
Portfolio Administration Activity
DELETE FROM afm_wf_rules WHERE rule_id = 'checkOwnership' AND activity_id = 'AbRPLMPortfolioAdministration';
Portfolio Forecasting Activity
Government Property RegistryActivity
Capital Budgeting/ Project Management Activities
Emergency Preparedness Activity
Energy Management
Reservations Activity
Move Management Activity
Asset Management Activity
Package and Deploy Wizard
Process Navigator
Smart Client:
System
Background views
Move Management Activity
Delete the following:
Activity Process or Role Task AbMoveManagement Craftsperson Examine Group Move Spreadsheet AbMoveManagement Data Coordinator Examine Group Move Spreadsheet AbMoveManagement Voice Coordinator Examine Group Move Spreadsheet
Asset Management Activity
Space
Emergency Preparedness
"Define Systems and Zones (CAD)" and similar for "Define Systems and Zones (SC)":
Set Fire Zones ---> Set Fire or Smoke Zones
Set Sprinler Zones ---> Set Sprinkler Zones
Set Security Zones ---> {same}
Set Emergency Zones ---> Set Emergency Lighting Zones
Set HVAC Zones ---> {same}
"Draw Egress Plans" -- ab-ep-draw-egress-plan-gd.axvw
"Draw Hazardous Material Plans" -- ab-ep-draw-hazmat-plan-gd.axvw REG-EGRESS
Define Systems -- need a new .axvw - ab-ep-def-systems.axvw --- Change Smart Client PNav to refer to this Web view since the -gd view is actually a Web View
Define Systems Dependencies -- need a new .axvw - ab-ep-def-system-dependencies.axvw
Define Geographic Locations----------ab-def-geo-loc.axvw
Define Locations
Define Organizations
Define Companies ---------- ab-rplm-companies-define.axvw
Define System Types
UPDATE afm_ptasks SET task_id = 'Define Employee Emergency Information' WHERE activity_id = 'AbSpaceEmergencyPreparedness' AND process_id = 'Business Process Owner' AND task_id = 'Enter Employees Emergency Contact Information';
UPDATE afm_ptasks SET task_id = 'Update Advisory Bulletin' WHERE activity_id = 'AbSpaceEmergencyPreparedness' AND process_id = 'Business Unit Managers' AND task_id = 'Edit Advisory Bulletin and Instructions for Managers';
Reservations Activity
Hoteling Activity
UPDATE afm_activities SET is_active = 1 WHERE activity_id = 'AbSpaceHotelling';
INSERT INTO afm_actprods (product_id, activity_id, activity_cat_id) VALUES ('AbWorkplaceServices', 'AbSpaceHotelling', 'NONE');
Create three new users with three new roles: HOTEL ADMIN, HOTEL ALL DPS, and HOTEL NO APPROVAL (both user name and role name)
Create three new groups: HOTELING ADMINISTRATION, HOTEL BOOKINGS ALL DEPARTMENTS, HOTEL BOOKINGS WITHOUT APPROVAL
Assign HOTEL ADMIN to HOTELING ADMINISTRATION, HOTEL ADD DPS to HOTEL BOOKINGS ALL DEPARTMENTS, and HOTEL NO APPROVAL to HOTEL BOOKINGS WITHOUT APPROVAL:
Reservations Activity
Service Desk/On Demand/Preventative Maintenance Activities
UPDATE afm_processes SET display_order = 50 WHERE activity_id = 'AbBldgOpsHelpDesk' AND process_id = 'Business Process Owner';
UPDATE afm_processes SET display_order = 50 WHERE activity_id = 'AbBldgOpsOnDemandWork' AND process_id = 'Business Process Owner';
UPDATE afm_processes SET display_order = 50 WHERE activity_id = 'AbBldgOpsPM' AND process_id = 'Facilities';
UPDATE afm_processes SET display_order = 75 WHERE activity_id = 'AbBldgOpsPM' AND process_id = 'Maintenance';
UPDATE afm_processes SET display_order = 700 WHERE activity_id = 'AbBldgOpsPM' AND process_id = 'Service Desk Manager';
Energy Management Activity
Environmental. Sustainability:
Hoteling Activity
Real Estate Portfolio Management Domain
For Suites should be under Lease Administration Suite Inventory (CAD)
Also the last eight views should be on the Web PNav under Suite Analysis
Also add to afm_userprocs for user AFM
Project Management Activity
Localization Activity
Licensing
Technologies Domain
Data changes:
System:
INSERT INTO afm_userprocs ( activity_id, process_id, user_name )
SELECT afm_processes.activity_id, afm_processes.process_id, 'AFM'
FROM afm_processes, afm_activities
WHERE afm_activities.activity_id = afm_processes.activity_id
AND afm_activities.is_active = 1 AND afm_processes.is_active = 1
AND NOT EXISTS (SELECT 1 FROM afm_userprocs WHERE user_name = 'AFM'
AND afm_userprocs.activity_id = afm_processes.activity_id
AND afm_userprocs.process_id = afm_processes.process_id );
DELETE FROM afm_userprocs WHERE (SELECT is_active FROM afm_processes WHERE afm_userprocs.activity_id = afm_processes.activity_id AND afm_userprocs.process_id = afm_processes.process_id ) = 0
OR (SELECT is_active FROM afm_activities WHERE afm_userprocs.activity_id = afm_activities.activity_id ) = 0;
Preventive Maintenance Activity
Hoteling Activity
Asset Management Domain
rule_id = AbPubJk; table_assigned = jk; rule_type = JSON; is_active = JSON; title = Publish Jacks
rule_id = AbPubEq; table_assigned = eq; rule_type = JSON; is_active = JSON; title = Publish Equipment
Schema Preferences
Schema changes
System
| Table Name | Field Name |
|---|---|
| afm_holiday_dates | auto_number |
| fn | fn_id |
| fn_trial | fn_id |
| gp | gp_id |
| grp_trans | grp_trans_id |
| mo | mo_id |
| pms | pms_id |
| reserve | res_id |
| reserve_rm | rmres_id |
| reserve_rs | rsres_id |
| rmpct | pct_id |
| serv | serv_id |
| system_dep | auto_number |
| vert | vert_id |
Real Estate Portfolio Management Domain
Condition Assessment Activity
Service Desk/On Demand Work Activities
Preventive Maintenance Activity
Project Management Activity
Localization Activity
8 tables affected:
afm_tbls (definitions added for: lang_lang, lang_strings, lang_files, lang_enum, lang_glossary )
afm_flds (definitions added for: lang_lang, lang_strings, lang_files, lang_enum, lang_glossary )
afm_activities
afm_actprods
afm_processes
afm_ptasks
afm_userprocs
afm_wf_rules
Localization
To get the new fields into afm_flds_lang ran:
INSERT INTO afm_flds_lang (table_name, field_name)
SELECT table_name, field_name FROM afm_flds
WHERE NOT EXISTS (SELECT 1 FROM afm_flds_lang afm_flds_lang_inner
WHERE afm_flds_lang_inner.table_name = afm_flds.table_name
AND afm_flds_lang_inner.field_name = afm_flds.field_name )
Workflow Rules
SysAdmin
Condition Assessment Activity
Preventive Maintenance Activity
Capital Budgeting:
Process Navigator
System
Preventive Maintenance
Condition Assessment
Localization Activity
Space
Capital Budgeting:
Changed task file for Capital Budgeting > Prioritize & Estimate (Optional) > Add or Edit Actions view
Added new task Prioritize & Estimate (Optional) > Create Facility Condition Index Scenarios
Changed task file for Prioritize & Estimate (Optional) > Analyze Facility Condition Index Scenarios
System Administrator
Beneath this current view: System Administration / System Administrator / View Program Information and License Usage
Data changes
Condition Assessment
Preventive Maintenance
Service Desk/On Demand Work
Space
Project Management
Schema Preferences
Web - Core
Schema:
Workflow Rules:
Process Navigator:
Data:
Real Estate Portfolio Management Domain
Schema:
Workflow Rules:
Process Navigator:
Data:
Space Management Domain
Schema:
Workflow Rules:
Process Navigator:
Data:
Service Desk and On Demand Work activities
Schema:
Workflow Rules:
Process Navigator:
Data:
Reservations activity
Schema:
Data:
Preventive Maintenance activity
Process Navigator:
US Federal Property Registry activity
Data:
Schema:
Workflow Rules:
Process Navigator:
Data:
Schema:
Workflow Rules:
Process Navigator:
Schema:
Workflow Rules:
Process Navigator:
Data:
Schema:
Workflow Rules:
Data:
Schema:
Process Navigator:
Process Navigator:
Data
Schema Preferences: The Database Version schema preference (Afm_Scmprefs) was set to 127. Database Version date was set to 2008.10.23.
Schema Changes:
Workflow Rule (WFR) Changes:
Process Navigator Changes:
Data Changes:
Schema Preferences: The Database Version schema preference (Afm_Scmprefs) was set to 126. Database Version date was set to 2008.02.25
Schema Changes:
Workflow Rule (WFR) Changes:
Data Changes:
| Table | Field | Field to Validate |
| activity_logview | dispatcher | dispatcher |
| activity_logview | manager | manager |
| activity_logview | requestor | requestor |
| activity_logview | supervisor | supervisor |
| cf | position | |
| cf | skills | |
| hwo | date_closed | |
| hwo | name_authorized | name_authorized |
| hwo | name_of_contact | name_of_contact |
| hwo | name_of_planner | name_of_planner |
| hwo | supervisor | supervisor |
| wo | date_closed | |
| wohwo | date_closed |
|
From |
To |
|
REQUESTOR |
CLIENT |
|
Create Work Request |
Create Maintenance Service Request |
|
Review Status of Work Requests |
Review Maintenance Service Request |
|
SUPERVISOR |
SUPERVISOR |
|
Create and Review as above |
Create and Review as above |
|
Approve and Issue Work Requests |
Issue and Print Work Orders |
|
Update Work Requests |
Update Work Orders and Work Requests |
|
CRAFTSPERSON |
CRAFTSPERSON |
|
Create and Review as above |
Create and Review as above |
|
Add Craftspersons to Work |
(Removed) |
|
Update Work Request |
Update Work Orders and Work Requests |
|
Task |
View |
|
Simple Tree - Departments by Division |
ab-ex-simple-tree-dpxdv.axvw |
|
Tree with Bridge table - Emp. by Room by Div. |
ab-ex-bridged-tree-emxrmxdv.axvw |
|
Tree with Console - Work Requests by Emp. |
ab-ex-tree-with-console-wrxem.axvw |
|
H ierarchical Tree - CSI codes |
ab-ex-simple-hierarchy-tree-csi.axvw |
|
Hierarchical Tree - Accounts |
ab-ex-simple-hierarchy-tree-ac.axvw |
|
Tree with many leaves - all rooms per every |
Emp. ab-ex-loading-test-large-rm-em.axvw |
|
Select Values Tree |
ab-ex-tree-selval-wr.axvw |
Field |
invoice.project_id |
invoice.work_pkg_id |
invoice.vn_id |
invoice.qty_accepted |
invoice.qty_invoiced |
invoice.qty_invoiced |
Field |
ls_comm.recorded_by |
ls_comm.parent_key |
ls_comm.summary |
ls_comm.project_id |
ls_comm.comm_type |
ls_comm.doc |
ls_comm.priority |
|
Field |
|
budget.site_id |
|
budget.program_type |
|
budget.year_start |
|
budget.year_end |
Field |
afm_dwgs.dwg_file |
afm_scmpref.date_lang_en |
Afm_flds.validated_data |
Afm_flds.attributes |
ls.doc |
activity_log.doc |
invoice.doc |
activitytype.instructions |
activitytype.description |
activitytype.autogen_wr |
activitytype.standard_cost |
activitytype.prob_type |
zone.layer_name |
trial_project.project_id |
invoice_payment.amount_expense |
afm_ptasks.iframe_width |
afm_ptasks.iframe_height |
Field |
activity_log.doc_file1 activity_log.doc_file2 activity_log.doc_file3 |
ls.image_doc1 |
ls_comm.image_doc1 |
op.image_doc1 |
|
Title |
Name |
Use |
|
Documents |
afm_docs |
List of all documents in the document management system. |
|
Documents Versions |
afm_docvers |
Versions of documents. |
|
Archived Documents Versions |
afm_docversarch |
Archives of documents. |
|
Questions |
questions |
List of questions for each questionnaire. |
|
Questionnaires |
questionnnaires |
List of all questionnaires in the system. |
|
Activity Sub-Types |
activity_subtype |
Contains standard action descriptions to refine the Activity Types table. |
|
Workflow Controller Log |
afm_wf_log |
Used for workflow rules. |
|
Workflow Rules |
afm_wf_rules |
Used for workflow rules |
|
ARCHIBUS Calendar Dates |
afm_cal_dates |
Used for calendar reporting by ARCHIBUS Web Central View Analysis. |
|
ARCHIBUS Activity Tables |
afm_act_tbls |
Used for importing actions from other applications. |
|
Document Templates |
doc_templates |
Holds templates that can be used with ARCHIBUS Web Central and doc management system. |
|
Project Types |
projecttype |
Capital Budgeting/Project Mgmnt activities |
|
Project Phases |
projphase |
Capital Budgeting/Project Mgmnt activities |
|
Activity Item Transactions |
activity_log_trans |
Capital Budgeting/Project Mgmnt activities |
|
Status Log |
status_log |
Capital Budgeting/Project Mgmnt activities |
|
Project Team |
proj_team |
Capital Budgeting/Project Mgmnt activities |
|
Scenarios |
scenario |
Capital Budgeting/Project Mgmnt activities |
|
Project Scenarios |
projscns |
Capital Budgeting/Project Mgmnt activities |
|
Funding Sources |
funding |
Capital Budgeting/Project Mgmnt activities |
|
Funding Allocations |
projfunds |
Capital Budgeting/Project Mgmnt activities |
|
Program Budget Items |
prog_budget_items |
Capital Budgeting/Project Mgmnt activities |
|
Program Types |
programtype |
Capital Budgeting/Project Mgmnt activities |
|
Programs |
program |
Capital Budgeting/Project Mgmnt activities |
|
Status Log |
status_log |
Capital Budgeting/Project Mgmnt activities |
|
Communications Log Log |
ls_comm_log |
Capital Budgeting/Project Mgmnt activities |
|
Communication Subscriptions |
commsubs |
Capital Budgeting/Project Mgmnt activities |
|
Communication Types |
commtype |
Capital Budgeting/Project Mgmnt activities |
|
Work Packages |
work_pkgs |
Capital Budgeting/Project Mgmnt activities |
|
Work Package Bids and Contracts |
work_pkg_bids |
Capital Budgeting/Project Mgmnt activities |
|
Table |
Field |
|
Afm_activities |
Summary |
|
Afm_activities |
Summary_de |
|
Afm_activities |
Summary_es |
|
Afm_activities |
Summary_fr |
|
Afm_activities |
Summary_nl |
|
Afm_activities |
Summary_no |
|
Afm_processes |
Summary |
|
Afm_processes |
Summary_de |
|
Afm_processes |
Summary_es |
|
Afm_processes |
Summary_fr |
|
Afm_processes |
Summary_nl |
|
Afm_processes |
Summary_no |
|
Afm_wf_rules |
Event_filter |
|
Afm_wf_rules |
Msg_action |
|
Afm_wf_rules |
Recipient_list |
|
Afm_wf_rules |
Sql_action |
|
Table |
Field |
|
Afm_activities |
Summary_01 |
|
Afm_activities |
Title_01 |
|
Afm_activity_cats |
Summary_01 |
|
Afm_activity_cats |
Title_01 |
|
Afm_flds_lang |
Enum_list_01 |
|
Afm_flds_lang |
Ml_heading_01 |
|
Afm_layr |
Title_01 |
|
Afm_processes |
Summary_01 |
|
Afm_processes |
Title_01 |
|
Afm_products |
Summary_01 |
|
Afm_products |
Title_01 |
|
Afm_psubtasks |
Task_01 |
|
Afm_ptasks |
Task_01 |
|
Afm_tbls |
Title_01 |
|
Table |
Field |
|
Afm_mods |
Afm_module |
|
Afm_class |
Act_class |
|
Afm_acts |
Act |
|
Afm_cats |
Task_cat |
|
Afm_tasks |
Task |
|
Afm_subtasks |
Subtask |
|
Afm_hotlist |
Super_cat |
|
Afm_hotlist |
Category |
|
Afm_hotlist |
View_title |
|
Title |
Name |
Use |
|
Products |
afm_products |
Process Navigator |
|
Activity Categories |
afm_activity_cats |
Process Navigator |
|
Product Activities |
afm_actprods |
Process Navigator |
|
Activities |
afm_activities |
Process Navigator |
|
Processes and Roles |
afm_processes |
Process Navigator |
|
Process Tasks |
afm_ptasks |
Process Navigator |
|
Process Subtasks |
afm_psubtasks |
Not used by v14.3 |
|
User Process Assignments |
afm_userprocs |
Process Navigator |
|
ARCHIBUS Roles |
afm_roles |
Security Roles |
|
ARCHIBUS Groups for Roles |
afm_groupsforroles |
Security Roles |
|
Workflow Process |
afm_wf_process |
Workflow |
|
Workflow Rules |
afm_wf_rules |
Workflow |
|
Visitors |
visitors |
Workplace Portal Activity |
|
Field |
|
afm_tbls.title_ch, de, es, fr, etc. |
|
afm_users.bl_id_list |
|
afm_users.site_id_list |
|
afm_users.email |
|
afm_users.home_page |
|
afm_users.locale |
|
afm_users.preferences |
|
afm_users.role_name |
|
afm_users.vpa_option1,2,3, and 4 |
|
afm_dwgpub.layer_background |
|
Field |
New Size |
Old Size |
|
afm_acts.security_group |
64 |
10 |
|
afm_cats.security_group |
64 |
10 |
|
afm_class.security_group |
64 |
10 |
|
afm_flds.edit_group |
64 |
10 |
|
afm_flds.review_group |
64 |
10 |
|
afm_group.group_name |
64 |
10 |
|
afm_hotlist.security_group |
64 |
10 |
|
afm_mods.security_group |
64 |
10 |
|
afm_tasks.security_group |
64 |
10 |
|
afm_subtasks.security_group |
64 |
10 |
|
afm_atyp.dflt_symbol |
64 |
8 |
|
it.performed_by |
32 |
16 |
|
rmcat.description |
96 |
25 |
|
rmtype.description |
96 |
26 |
|
Field |
Old Value |
New Value |
|
afm_scmpref. afm_db_version_num |
101 |
1.01 |
|
afm_scmpref. afm_exe_release_num |
107 (default) |
1.07 |
|
.cf.issue_work_in_format |
<blank> |
None |
|
tr.issue_work_in_format |
<blank> |
None |
These fields were added to the Activity Logs table (activity_log):
|
New Fields in Activity Logs table |
|
assessed_by |
|
capital_program |
|
completed_by |
|
cond_priority |
|
cond_value |
|
cost_act_cap, cost_est_cap |
|
cost_to_replace |
|
csi_id |
|
date_assessed, date_verified |
|
doc_file1,doc_file2, doc_file3 |
|
eq_id |
|
fl_id, location |
|
rec_action |
|
rm_id |
|
site_id |
|
sust_priority |
|
verified_by |
The following fields were changed in the Activity Log table (activity_log):
" N/A
" Trial
" Budgeted
" Planned
" Scheduled
" In Progress
" Completed
" Completed and Verified
Table | Old Title | New Title |
blbu | Bldg. Ops. Budgets for Buildings | Bldg. Ops. Building |
hwrtr | Historical WR Trade Requirements | Historical WR |
mo_eq | Move Order Equipment Assignments | Move Order Equip. Assignments |
mo_ta | Move Order Tagged Furn. Assignments | Process Navigator |
pmpsum | PM Procedure Forecast Summaries | Move Order Tagged Furn. Assig. |
ta_compinvsur | Tagged Furn. Inventory vs. Audit | Tagged Furn. Inv. vs. Audit |
ta_compinvtrial | Tagged Furn. Inventory vs. Trial | Tagged Furn. Inventory vs. Trial (extra spaces removed) |
wrtr | Work Requests Trade Requirements | Work Request Trade Requirement
|
o Type set to Hpattern Acad Ext
o Field width increased to 64 characters
From: ar;All Room;arp;All Room Percentage;ci;Composite;cib;Composite - BOMA;cie;Composite - EBOMA
To: ar;All Room;arp;All Room Percentage;ci;Composite;cib;Composite - BOMA;cie;Composite - EBOMA;ci9;Composite - BOMA 96
This field is used by the SMP module's Allocation view action for determining the calculation to use when recalculating floors usable area (spalloc.abs).
From: B;BOMA;E;Enhanced BOMA;G;Group
To: B;BOMA;E;Enhanced BOMA;9;BOMA 96;G;Group
This field is used by the Lease module for determining the calculations to use when charging back common areas to lease areas. (lsarea.abs).
| Copyright © 1984-2015, ARCHIBUS, Inc. All rights reserved. |