(Show Contents)

Database Schema Revision History

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).

Version 20.2 Database Schema Revision History (Database Version 137)

SCHEMA PREFERENCES

• Update Db Version Date to: 2012-08-06
• Update Db Version number to: 137

SCHEMA

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 )
   	

WORKFLOW RULE CHANGES

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"

   	

PROCESS NAVIGATOR CHANGES

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 
   	

DATA CHANGES

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)

Version 20.1 Database Schema Revision History (Database Version 136)

SCHEMA PREFERENCES

• Update Db Version Date to: 2012-02-13
• Update Db Version number to: 136

SCHEMA CHANGES

System:

• For Data Change Events:
  • -afm_wf_rules table: field rule_type: add enumeration value “DataEvent;DataEvent”.
  • -afm_data_event_log table: add this table
• afm_flds
  • -Change enum_list to size 850 to accommodate longer values needed for afm_flds_trans.change_type.
  • Change the data dictionary and then run: ALTER TABLE afm_flds MODIFY enum_list VARCHAR (850); Also, remove the CR/LF from the enum_list for afm_flds_trans.change_type. There appears to be only one; in "AFM Type".
• afm_flds_lang
  • Change the 13 enum_list_xx fields to size 1000
• Change afm_activity_params.param_value from size = 100 to size = 512 so that it can include longer values needed by virtual fields for KPIs.
• Reduce field sizes in the afm_notifications_log table to work with MSSQL server (3031640)
  • email_to from 5000 to 2000
  • email_message from 8000 to 5000
• afm_ptasks.security_group -- Change ML Heading from "Hotlist Security Group" to "Security Group".
• questions table: add field: is_required - SmallInt; Allow Null? NO; ML Heading: "Required?"; Enum: 0;No;1;Yes
• SCW: Add the following to the enum_list for afm_flds_trans.change_type:
  • CIRC_REF;Field has circular reference
  • NO_DB_VAL_IN_ENUM;Database Value not in Enum
  • TBL_IN_PROJ_ONLY;Table is only in project
• Change "Activity" titles to "Application" (3033017)
• Change size of the "cost" fields for the bu, dv, and dp tables from 9 to 12.
• afm_metric_definitions - FKey improperly defined in SQL database. Run:

  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;

• Add table and fields for afm_redlines (3034274)
• Update all fields of type "Document" to insure that they have the afm_flds.attributes values filled in (3034508)
• hactivity_log.assessment_id - change Numeric Format to NoSeparator (3032987)
• questionnaire table - increase field sizes (3034305)
  • Change questionnaire_id size from 32 to 64
  • --- also in questions table
  • Change title size from 64 to 96
  • Change all title_* fields sizes from 96 to 144
• Add default Win SC views to afm_tbls.

BIM

• Add tables, fields, and data for afm_bim_categories, afm_bim_families, and afm_bim_params tables. (3032400)
• Change all fields that store ehandles to 64 VarChar. Change ML Heading to: "Entity Handle / Unique ID"
• Change all fields that store drawing names to 128 VarChar
• Run Update Schema on:
  • All afm_bim% tables
  • All tables containing dwgname and ehandle fields:

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

• Add data for afm_bim_categories
• Add data for afm_bim_families
• Add data for afm_bim_params

Space Transactions

• Add changes to rmpct table for Space Transactions
• Add other table changes for 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)

• Add table hrmpct. Set the AFM Type for all fields to "Calculated".
• Remove validation on rmpct.em_id so that records that contain deleted employees stay in the rmpct table and not be archived right away.
  • Set "Validate Data?" to NO for rmpct.em_id
• Set the multi-line heading to “Occupancy Count” for count_em fields in location-based tables and in room category and type. Locations such as floors and buildings now store the total occupant count, not headcount, which allows us to count employees who may occupy more than one room.
• The Multi-line headings for rmpct.pct_id and rmpct.parent_pct_id are “Percentage Code” and “Parent Percentage Code” respectively. Change to reflect the new table name of “Workspace Transactions”.
• rmpct - add mo_id, primary_rm, primary_em fields.
• hrmpct - add mo_id field.
• Retitle the rmpct table: UPDATE afm_tbls SET title = 'Workspace Transactions' WHERE table_name = 'rmpct';
• - em, eq, ta tables -- set curr_bl_id and curr_site_id is_validated value to NO. (3034694)

Bldg Ops

• probtype.prob_type is now Hierarchical-Concat. Expand it's size to 32 chars to handle longer concatenated values. (3033137)
  • Change the size for all fields named "prob_type". This affects 16 tables:
    activity_log; activity_log_hactivity_log; activity_logview; activitytype; hactivity_log; hactivity_logmonth
    helpdesk_sla_request; hwr; hwr_month; hwrcfana; hwrsum; probtype; rrwrrestr; wr; wrhwr; wrview
• Correct inconsistency between the data dictionary and sql on field units_ord_iss from table pt (3032450).
• Remove hwrcf.scheduled_from_tr_id validatation to the non-historical tr table. (3032326)
• Remove the NULL value from the step_status enumerated lists. (3016544)
• Update Service Desk field definitions:

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';

• pt.units_ord_iss decimals differs from that in afm_flds: (3032450)
  ALTER TABLE pt MODIFY units_ord_iss NUMERIC(10,3) NOT NULL DEFAULT 1.0;
• Foreign keys wrongly defined in ARCHIBUS Data Dictionary. PKey for afm_wf_steps is: activity_id; status; step (3032162)
  1) In afm_flds set ref_table = null and dep_cols = null where table_name=’helpdesk_sla_steps’ and field_name=’status’.
  2) In afm_flds set ref_table = null where table_name=’helpdesk_step_log’ and field_name=’status’.
• Add Validating Table values of site and bl to the hwr table in afm_flds. However, make sure that "Validate Data?" is set to "No". (3033757)
• Add validation to bl_id and site_id in table hwr. Set "Validate Data?" to "No". (3033757)
• Update SQL views based on activity_log table to be more consistent with the activity_log table (3030454)

Multi Currency

• Add in MC/MU/VAT database changes. Tables added or updated:

  afm_currencies
vat_percent
afm_conversions
cost_tran%
invoice%
ls

• Add table for holding temporary values for cost summaries for REPM: ccost_sum

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)

• Set afm_conversions.is_currency to NOT allow NULL values.

Multi-Units

• Add field "display_units" to afm_users. "Control how area and length values are displayed for the user." (3032329)
• afm_flds.num_format field -- add the following enumeration list values to facilitate the core displaying appropriate units: 3;Area;4;Length;6;PaymentCurrency
• afm_flds.num_format field -- change enumeration display value "Money" to "BudgetCurrency".
• afm_flds.afm_type - To support the Currency Selector control, add the following to the enumeration List:

  2245;Currency

• Update application fields to include the appropriate formatting values:

  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:

• grp_trans table -- add "N/A;Not Applicable;" to the enumeration lists for the following fields and make 'NA' the default value: (3030908)

  lease_maintenance_ind
outgrant_indicator
utilization
disposition_method

• grp_trans - Change the following enumeration list fields and all fields except status and trans_type:
  Make the "ARCHIBUS Type" = None
  Add "NA;Not Applicable" to the following grp_trans fields but do NOT add a database default value (app logic fills in NULL for fields user does not change values for). (3030908)

  legal_interest_ind
lease_maintenance_ind
lease_authority_id
status_indicator
outgrant_indicator
historical_status
size_unit_of_measure
utilization
mission_dependency
disposition_method_id
sustainability

Green Building

• bill_unit.bill_unit_id string format was previously changed from UPPER to AnyChar. Change string format of bill_line.bill_unit_id from UPPER to AnyChar. (3030912)
• gb_cert_proj table changes: (3032313)
  

  - 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 gb_cert_proj.tot_final_score and tot_self_score DataType to numeric for BREEAM decimal score support (3034748)

  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

• ls_comm.activity_log_id. The Numeric Format should be changed from Default to NoSeparator. (3031963)
• activity_log.assessment_id - change Numeric format from Default to NoSeparator. (3032987)
• Added table and field definitions. Update Schema for tables: vn;activity_log;hactivity_log;probtype;project;waste_%;cb_%
• Schema changes for Clean Bldgs to existing fields in afm_flds: (3033423)

  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.

• Modify ls_comm.activity_log_id in afm_flds, change Numeric format from Default to NoSeparator. (3033422)
  The following fields are also integer FKs, but have Numeric format of Default, they should be set to NoSeparator:

  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

• Added table and field definitions

Material Safety Data Sheets

• Added table and field definitions: msds%;hazard%;company;ac;contact;projteam;visitors;vn
• vn table: add field: web_url Char(64); Allow Null; ML Heading: "Website URL"

Compliance

• Added table and field definitions: company;notifycat;notify_templates;notifications;reg%;activity_log;ls_comm;doccat;doctype;docfolder;docs_assigned;hactivity_log

• 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

• bill_unit table - Change default value for field Rollup Type to "None". (3031387)

Asset Management

• q table: change num_serial to size 32.

Reservations

• MS Outlook integration: Add field outlook_unique_id to the reserve table
• Add the outlook_unique_id field to the hreserve table. (3034728) - rrdayrmocc
• Add validation to bl_id and site_id but set "Validate Data?" to "No". (3033784)

Project Mgmt/Commissioning:

• work_pkgs - change the size of the doc and doc_acts_xfer fields from 64 to 128 (3034028)
  Project Table:
  Provide for auto-generating Project code values and add a new project_name field. (3034406)

  • 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

  • Add fields to eq and eqstd. Review fields with comments of "v20.1 Commissioning" prior to updating schema
  • Change ml_heading of all project_id fields to be "Project Code" to match the change of ml_heading in the project.project_id field.
  • work_pkgs table. Add fields:
    

  • 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”

• project table - date fields - Change all ML Headings to be like "Date -" (3034301)

Cost Admin

• Add FKey links to afm_flds for the cost_tran table to match cost_tran_recur and cost_tran_sched BUT set "Validate Data?" to NO! (3033804)

Forecasting

• bl and fl tables - add: std_area_per_em; default is 0

LOCALIZATION

• lang_files table (3032160):
  • Change the size of lang_files.constant from 255 to 2100.
  • Change the size of lang_files.string_trans from 2000 to 3000.
• change the lang_enum table definition in afm_flds (3033463)
  • enum_english -- increase size to 850

  • enum_trans -- increase size to 1000

• 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

System

• Add WFR to archive or un-archive document records based on afm_docs.axvw. Move its java handler from java Core to ab System. (3028794,3032193)
• For Data Change Events:
  • Add RoomTransactionDataChangeEventHandler
  • Add SecurityTablesDataChangeLogger
• Some rule_type values do not have the correct case.
  • UPDATE afm_wf_rules SET rule_type = 'Message' WHERE rule_type = 'message';
  • UPDATE afm_wf_rules SET rule_type = 'Scheduled' WHERE rule_type = 'scheduled';
• Some Message rules have non-Null xml_sched_props values.
  • UPDATE afm_wf_rules SET xml_sched_props = NULL WHERE rule_type <> 'Scheduled';
• Move Mgmt uses Proj. Mgmt logic -- to resolve licensing conflict move the activity log WFR’s to an activity log service in common resources (3032322)
• Rename solutions Java packages according to the new naming convention (3032600)
• refreshDataDictionary - package name and class name were changed: (3034238)

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';

• The AppUpdateWizard uses DWR now, therefore WFR can be removed from DB: (3034238)

  DELETE FROM afm_wf_rules WHERE rule_id = 'AppUpWizService';

• Add Drawing Service WFR (3034243)
• Add workflow rule for Search Service/File Dependency Walker (3033987)
• Add AbSystemAdministration-refreshDataDictionary WFR (3030210)

Space Transactions

• Add SynchWorkspaceTransactions and CollectTrendMetrics WFRs
• Add new class SpaceTransactionHandler.
• Add new ‘DataEvent’ WFR entry ‘AbSpaceRoomInventoryBAR-RoomTransactionRecorderForDataChangeEvent’

Asset Management

• Add WFR for Asset Mgmt (needed for paginated report) (3031164)

Capital Budgeting

• Add new WFR for AbCapitalBudgeting-CapitalBudgetingService.

Waste Management, Clean Building, MSDS, and EH&S

• Added WFRs.

Compliance

• Add WFR for ComplianceCommon (3034421)

PROCESS NAVIGATOR CHANGES

System

• Add "View Data Events Log" view (ab-data-events-log.axvw) under the / System Administration / ARCHIBUS Administrator / process.

• 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

• Rename the System Administrator "Application Update Wizard" task to the "Deployment Update Wizard".
• Change task name: "Deployment Update Wizard" to: "Package and Deploy Wizard" (3033751)

  Path: System Administration / ARCHIBUS System Administration / System Administrator / Deployment Update Wizard

• Synchronize naming of "Add or Edit User Roles" tasks (3031664).
  Change Smart Client task:
  Path: System Administration / ARCHIBUS System Administration / ARCHIBUS Administrator (SC) / Add or Edit Security Roles
  To: "Add or Edit User Roles"
• Add the Database Update and Schema Change Wizards to the PNav. Rename the App Up Wiz (3032425, 3032302)
• Split this process into two processes:
  System Administration / ARCHIBUS System Administration / ARCHIBUS Administrator
  ARCHIBUS Administrator – User and Security
  ARCHIBUS Administrator – Applications Configuration
• Add: Web C > System Administration > ARCHIBUS System Administration > ARCHIBUS Administrator - Application Configuration > Preload All Views (3034775) as the last item in the "Utilities" group.
  View is: "ab-preload-views.axvw" view.
• CAD Manager role on the Process Navigator updated to "CAD and BIM Manager" (3034457) System Administration/ARCHIBUS System Administration/CAD Manager:
  Change the Process Navigator role from "CAD Manager" to "CAD and BIM Manager"
  Add the new BIM tasks under this role. These tasks are:
  System Administration/ARCHIBUS System Administration/CAD and BIM Manager/ARCHIBUS BIM Categories
System Administration/ARCHIBUS System Administration/CAD and BIM Manager/ARCHIBUS BIM Families
System Administration/ARCHIBUS System Administration/CAD and BIM Manager/ARCHIBUS BIM Parameters
  

Space and Transactions

• Add PNav entries.
• Add additional afm_ptasks records to bring up a view that will enable or disable room transaction features (pnav and afm_roleprocs entries and activity param) for the user
• Add the Space Dashboard to the PNav for Personnel & Occupancy, and Space Inventory & Performance
• Change titles of key views to be more relevant to the business goal
• Remove Space processes from 'ARCHIBUS SYSTEM ADMINISTRATOR' role.
• Remove duplicate afm_ptasks records that contain old task names
• Disable Workspace Transactions functionality and make the existing Space processes the default
  Planning & Management / Space Inventory & Performance / Background Data / Enable or Disable Workspace Transactions:
  Click the “Disable” button on the second panel.
  UPDATE afm_wf_rules SET is_active = 0 WHERE activity_id = 'AbSpaceRoomInventoryBAR' AND rule_id = 'RoomTransactionRecorderForDataChangeEvent';
  DELETE from afm_roleprocs WHERE process_id LIKE '%rmtrans%';
• Include “Update Area Totals” in the reports PNav to accommodate a typical CAFM process

Multi-Currency and VAT:

• Add task for Global Portfolio Dashboard under Portfolio Mgmt / Portfolio Summary / (put at top of list) - ab-rplm-pfadmin-pnav-dashboard-global.axvw
• Add task: VAT Amount Balance ab-rplm-cost-mgmt-vat-bal.axvw (3033031) under activity: Cost Administration activity / Processes or Roles: Cost Administrator (WebC)
• Add the following Solution Template views under the Technologies activity:
  User Interface Add-Ins...
  

• 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:

• Add the Bldg Ops dashboards to the PNav (3031592)

Service Desk:

• - Add Redlining views.

Project. Management:

• Add "Define Locations" task to both the Proj Mgmt and Cap Bud Setup processes
• Add "View Project Metrics by Building" under Capital Project Management / Capital Budgeting / Prioritize & Estimate (Optional) / (3031353)

Capital Budgeting:

• Copy Define Funding Sources from Evaluate (optional) to Setup as funding is a critical part of all projects. Doing funding scenarios is the optional exercise.

Hoteling:

• Add afm_roleprocs record for: HOTEL ALL DPS --------- AbSpaceHotelling ------- Create and Review Bookings

Green Building:

• Add Bus Process PNav entries for Green Building (3034818)

Waste Management, Clean Building, MSDS, EH&S, Compliance and Commissioning applications

• Added Process Navigator

Solutions

• Add to Solutions an entry for enhanced functionality for document fields - task: 'Parts for Forms', 'Document Fields -- Direct Check In and Listeners'
• Add Pnav entry for the Chart View API enhancements (3031322)
  Path: Technologies / User Interface Add-Ins / Chart Views / Dynamically Set Properties for Pie Chart
  URL: ab-chart-pie-dynamic-properties.axvw
• Add: Technologies / User Interface Add-Ins / Chart Views / Dynamically Set Properties for Stacked Bar Chart
  Task file: ab-chart-stackedbar-dynamic-properties.axvw
• Add the Real Estate Summary Dashboard view to the Programming/Maps examples (3031141)
• Add: / Technologies / User Interface Add-ins / Assembles with Multiple Panes / Find-Manage with Column Report -- ab-ex-wr-alltypes-column.axvw (3016418)
• Add: Drawing Report with Custom SQL Highlight Data Source ab-ex-dwg-sql.axvw (3032376)
• Technologies / My ARCHIBUS Activity / Space Inventory Web Process / Reallocate Rooms Between Departments --- is outdated. (3027367)
  UPDATE afm_activities SET activity_id = 'AbExtensionsMyArchibus' WHERE activity_id = 'AbSolutionsMyArchibus';
  DELETE FROM afm_processes WHERE activity_id = 'AbExtensionsMyArchibus' AND process_type IN ('WINDOWS', 'OVERLAY');
• Update 3 Solutions Templates for Paginated reports to inlude the "pagereports/" prefix: (3034495)
  

• 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

DATA CHANGES

System

• For Data Change Events: Add activity parameters:
  

• AbSystemAdministration-DataChangeEventTablesToLog	value: afm_users;afm_groups;afm_roles;afm_groupsforroles;afm_roleprocs
  AbSpaceRoomInventoryBAR-ResynchRoomTransactionsTable	default value "No"".
  AbSpaceRoomInventoryBAR-UseRoomTransactions	default value "No"

• Add application parameter AbSystemAdministration - DateSampleDatesLastUpdated with value of 2012-01-01 (3034905)
• user Z-LOCALE-UK has the locale en_UK that is incorrect ISO code, it should be en_GB (3034805)
• Update afm_activity_params data to be in CamelCase (3032214) as shown below:

• Add new Activity Parameter for Multi-Units:
  AbCommonResources-ConvertAreasLengthsToUserUnits

User's Changes

• Add new roles to illustrate user-levels for activity-style users.
• Add a series of AIDEMOFR,DE,ES, etc. users with different locales for illustrating the MC&VAT features.
• Add a series of activity-style AFM* users to parallel the AI* users so we can illustrate the new features with either an activity-style or domain-style license.
• Migrate any remaining afm_userprocs to afm_roleprocs
• Delete all afm_userprocs records (since we now use the afm_roleprocs).
• Delete the SPACA, SPACC, and RPLM users as they are no longer referenced by the doc or the test plan
• Delete the FIM, FIM-REGIONAL, and FIM-DISPATCH roles.
• Uniquify email addresses (except for the reservations users, who are all mapped to millerj@tgd.com).
• Add "AIDEMO5" users
• AIDEMO and AFMDEMO users – link email to AI and AFM employees
• User DISPATCHER is currently assigned to role OPS SUPERVISOR. Change it to be assigned to role: 'OPS DISPATCHER'
• Add all processes for all active activities to each of these roles in the afm_roleprocs table:
  4 - Process OWNER
  4 - PROCESSD OWNER (ACP)
  ACTIVITY LICENSEE
• Update afm_roleprocs to assign all activities to the above roles:
  

• 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' AND afm_roleprocs.activity_id = afm_processes.activity_id AND afm_roleprocs.process_id = afm_processes.process_id);

Space Transactions

• Correct question text for Service Desk records in the "questions" table.
• Add SLAs for Individual Move, Group Move, and Department Space.
• Update activitytype data for Space Trans Service Request activitytypes to hide fields on the request forms that are not necessary, such as work location and equipment
• Update rm, em, activity_log, rmpct, and metric trends data
• Add two activity parameters that hold the dates that were used for the most recent Update Area Totals calculations.

Multi-Currency / Multi-Unit / VAT:

• Add new Activity Parameter for Multi-Currency:
  AbCommonResources-EnableVatAndMultiCurrency --- should be set to 0 or 1
• Update Country Code values to ISO Country Code format (3-Char)
• Add sample data to tables:
  "ac", "ctry", "regn", "state", "county", "city", "site", "property", "bl", "bl_amenity", "ot", "fl"
  "ls", "cost_tran_recur", "cost_tran_sched", "cost_tran", "invoice", "invoice_payment"
  afm_currencies, vat_percent, afm_conversions
• Add 22,000 records of cost_tran sample data:
• Add facility_type sample data
• Add headcount data needed by Global Portfolio Dashboard (3034061).

Asset Management

• Change fn and fn_trial asset types to be drawing-driven in afm_atyp.record_source (3033500)
• Add some images to Furniture Standards doc fields (3032470)
• Add Building VPA list for users assigned to the GLOBAL roles.
• Some width and depth values are incorect in the fnstd table (3033932).
  Update the width and depth values to match the descriptions.

BIM

• Update afm_bim_params data. (3034159)
• Add 20 new afm_bim_families records (3033096)
• Correct invalid data in activity_log table (3034562)
  UPDATE activity_log SET step_status = 'waiting' WHERE step_status = 'Waiting for disp';

Bldg Ops

• UPDATE activity_log SET step_status = 'none' WHERE step_status = ''; (3034202)

Risk and Environment

• Add the following to bill_units: (3031081, 3031091, 3034371, 3034378)
  

• 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

• Uppercase gb_cert_proj.project_name values (3030865,3030971,3030997)
• Make sure that all bl records have gross areas and occupancy data as Green Bldgs looks for that.
• Add BREEAM projects and scores for Green Building sample data: (3034900)
• - gb_cert_proj - add data for new fields: Capital Cost, Annual Savings, and Payback Period (3034817)

Clean Buildings

• Add “ASSESSMENT - HAZMAT” to activitytype and projecttype tables.
• Add data for HQ Basement drawing:
  fl
  afm_dwgs
  gros
  rm
  eqstd
  eq

MSDS

• Added sample data

• 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

• Add sample data
• Add messages values for notification messages for EH&S.

Compliance

• Add sample data
• Add activity_param: Events_DaysAllowEdit_AfterClose (3034421)

Commissioning

• Add sample data

• 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

Version 19.3 Multi-Lingual (Database Version 135)

Schema Preferences:

• Update Db Version Date to: 2011.03.24
• Update Db Version number to: 135

Schema changes:

Green Bldgs:

• Changed the afm_flds entries for project_name field in tables (gb_cert_docs, gb_cert_log, gb_cert_proj, gb_cert_scores) to modify the string_format to UPPER. (3030865)
• Changed the afm_flds entries for gb_fp_setup "*_version" and "subregion_code" FK fields to modify the string_format to UPPER, to match the definition of the referenced fields. (3030864)

WFR changes:

*** NONE ***

PNav changes:

System:

• Add a new System Administration view "Edit Messages" into the messages table. (3028118)
• CAD Manager process is missing icon in Smart Client Process. (3030807)

Proj. Mgmt.:

• Project Mgmt / Execute process - add at bottom of PNav an "Update Project Status / Close Project" view: ab-proj-project-update-status.axvw (3030895)

Data changes:

System:

• Add drawing revision data for Drawing File Mgmt. (3030684)

Green Bldgs:

• There is data inconsistency between tables gb_fp_emiss_data and gb_fp_sectors for sector_name column which at a certain point has the values: "Manufacturing industries/Construction" and "Manufacturing Industries/Construction". This is a problematic only with Oracle because is key-sensitive. (3030714)
  

Version 19.3 (Database Version 134)

Schema Preferences:

• Update Db Version Date to: 2011.03.08
• Update Db Version number to: 134

Schema changes:

System:

• Change the size of afm_notifications_log.email_message to 8000. This prevents UPW from crashing with MSSQL because the current 10,000 exceeds the max VARCHAR size for SqlServer.(3029999)
• Set A/FM Type for afm_dwgs.dwg_file field to “Calculated”.
• Update afm_flds for the enumeration list for afm_transfer_set.status: (3030442)
  • Remove the extra line return in "No Extract File".
  • Add: "UPDATED;Updated;"
• Add tables:
  • afm_metric_definitions
  • afm_metric_trend_values

BldgOps:

• probtype table: Copy ac.hierarchy_ids field definition in afm_flds to the probtype table.

Cap Bud:

• UPDATE afm_flds SET ml_heading = 'Date Available - End' WHERE table_name = 'funding' AND field_name = 'date_avail_end';

Green Bldgs:

• Change the enum list for bl.use1 to include values required by Energy Star: (3030313)
  • UNKNOWN;UNKNOWN;BANK/FINANCIAL INSTITUTION;BANK/FINANCIAL INSTITUTION;COURTHOUSE;COURTHOUSE;DATA CENTER;DATA CENTER;HOUSE OF WORSHIP;HOUSE OF WORSHIP;HOSPICE;HOSPICE;HOSPITAL;HOSPITAL;HOTEL;HOTEL;K-12 SCHOOL;K-12 SCHOOL;MANUFACTURING;MANUFACTURING;MEDICAL OFFICE;MEDICAL OFFICE;MIXED USE;MIXED USE;MULTIFAMILY HOUSING;MULTIFAMILY HOUSING;OFFICE;OFFICE;OTHER;OTHER;RESIDENCE HALL/DORMITORY;RESIDENCE HALL/DORMITORY;RETAIL;RETAIL;SALES OFFICE;SALES OFFICE;SENIOR CARE FACILITY;SENIOR CARE FACILITY;SPORT/RECREATION;SPORT/RECREATION;STORAGE;STORAGE;SUPERMARKET/GROCERY STORE;SUPERMARKET/GROCERY STORE;WAREHOUSE;WAREHOUSE;WAREHOUSE (REFRIGERATED);WAREHOUSE (REFRIGERATED);WASTEWATER TREATMENT PLANT;WASTEWATER TREATMENT PLANT
• Add field called scenario_type to scenario table: Data Type=Char, Size=16, ML Heading="Scenario Type", Comments="v19.3-Green Building". (3030182)
• Add field called is_dflt to bill_unit table: Data Type=Smallint, Heading="Default?", Comments="v19.3-Green Building", Default Value=0, Enumeration List="0;No;1;Yes" (3030160)
• Change size of bl.use1 from 16 to 32 (3030311)
• bill_type table:
  • Change bill_type.bill_type_id string format from Upper to AnyChar. (3029955)
    
    Needed to accommodate multi/mixed units support and international standard abbreviations for units, for which lower and mixed case strings are required (e.g., kg, mg, MT, kW)
  • Change All NULL for or bill_type.cost_cat_id from rom No to Yes (3030158)
    
    This is needed to accommodate multi/mixed units support. Without this change, a dummy record would need to be created in cost_cat table for GB to use in its bill_type entries. If the user deletes that dummy cost_cat record, ALL of GB's bill_type and bill_unit entries would be deleted via cascade delete, causing GB to stop functioning.
  • Add field activity_id. Copy from: afm_notifications_log. Allow Null = Yes, Validate Data? = No, Comments="v19.3-Green Building" (3030157)
    
    

  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.

• bill_unit table:
  • Change conversion_factor - change from (Size=12, Decimals=6) to (Size=28, Decimals=12). (3030579)
  • Change bill_unit_id string format from Upper to AnyChar. (3029955)
  • Add all gb_* tables for Green Bldgs activity.

Localization:

• Expand the sizes of the following lang_strings fields in order to handle the larger afm_activities.summary* field sizes that were expanded for v19.2: (3030083)
  • string_english from: 512 to: 800
  • string_trans from: 768 to: 1200
  • reference_info from: 512 to: 800
• 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:

System:

• Add WFR to remove unlicensed activity assignments to users
  • Activity: AbSystemAdministration
  • Rule name: removeUnlicensedActivities
  • Event handler class: com.archibus.eventhandler.security.RemoveUnlicensedActivities
  • Event handler method: removeUnlicensedActivities
  • Active: Yes
  • Security group: leave empty
  • Rule Type: Scheduled
  • XML Schedule Properties: <xml_schedule_properties><schedule startTime="01-01-2010 12:00:00" endTime="" runOnStartup="true"><simple repeatCount="-1" repeatInterval="315360000"/></schedule></xml_schedule_properties>

BldgOps:

• Canonical Db lost the "updateServiceRequestStatusFromWorkRequest" after the 2010-12-22 update. This WFR was added to the production databases. Add it to the canonical hq.db: Import: WFR-updateServiceRequestStatusFromWorkRequest.xml

Green Bldgs.:

• Add all WFRs for Green Bldgs activity.

PNav changes:

System:

• The localization process creates duplicate records in MSSQL and Oracle if Pkey fields have leading spaces. Remove the leading spaces from records in the following tables and fields that have leading spaces:
  • afm_hotlist.view_title
  • afm_subtasks.subtask
  • afm_tasks.task
• Add PNav entries for Drawing File Management (3030027)
• Create a new “CAD Manager” process in the System Administration activity beneath “ARCHIBUS Administrator” but before Add-in Manager”. Add this process on both Web Central and the Smart Client Process Navigators. Help link is: \Subsystems\webc\Content\cad_manager\cad_manager_over.htm
• Add the following views:
  • Set Asset Text Visibility (ab-cad-set-atxt-visibility.axvw)
  • Label
  • View Drawing Changes by Drawing ( ab-dwg-changes-by-dwg.axvw)
  • View Drawing Changes by Building Location (ab-dwg-changes-by-bl.axvw)
  • View Drawing Changes by User (ab-dwg-changes-by-user.axvw)
• Add: Technologies > Localization > Localization > Localization Analysis (ab-localization-analysis.axvw) (3028511,3030276)
• Change view for: AbSystemAdministration / ARCHIBUS Administrator (SC) / Assign Processes to Roles TO: \sys-admin-views\security\ab-sys-afm-roleprocs-by-role-gd.axvw (3030344)

Space Mgmt:

• Space Management / Space Inventory / Room Inventory / Highlight Vert Pen by Type --- Typo: the command line should be "afm_hvertxtype" (3029964)
• Add Ptasks for simple views for exporting space data.
• Add/Update Building Performance and Room Inventory process tasks for SC CAD extensions.

Green Bldgs:

• Add all PNav entries for Green Bldgs activity.

Data changes:

System:

• Add the following to the afm_activities.summary field for the Green Buildings activity: (3030178)
  • Sustainability officers and green building managers strive to increase green building performance and certification levels and to reduce carbon footprint. However, assessing progress and quantifying results requires complex calculations and a breadth of data spanning multiple years. This activity calculates net greenhouse gas equivalent impact, shows carbon footprint change over time for each building or an entire portfolio, tracks progress through sustainability certification projects, and manages data and documents required for requalification. It also shows anticipated payback on sustainability investments and correlates environmental data to facility use and operations thus providing the timely and reliable data required for making and defending strategic sustainability decisions.
• The following changes convert user "AFM" to an activity licensee user and add user "AI" as a domain license user:
• Copy user "AFM" to a new user "AI" (3030557)
• Rename the “AIDEMOACP” (role “ACTIVITY LICENSEE” role) user to “AFM” (still “ACTIVITY LICENSEE” role) (3030557)
• Copy the AFM user (role “ACTIVITY LICENSEE”) to user AIDEMOACP. (We need no other change as all other processes and groups come from the role.)
• Add em_id = "AI" to the em table similar to em_id = "AFM".
• Update afm_userprocs to assign user 'AI' to all processes:
  • INSERT INTO afm_userprocs ( activity_id, process_id, user_name )

    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.:

• Update project tasks to show dependencies.

Green Bldgs.:

• Change bl.use1 for HQ and JFKB from "MIXED USE" to "OFFICE"
  • The reason is that Energy Star export has a different spreadsheet template for each building use, and therefore a separate spreadsheet is required for each use.We have only tested the template for “OFFICE” so far and having several bldgs with ‘OFFICE’ will demonstrate export of multiple buildings to one spreadsheet

• Add all sample data for Green Bldgs.

BldgOps:

• Establish hierarchical sample data for problem types

REPM:

• Some data is missing from the ls_resp table in Oracle since clause_type_id = ‘amenity’ but in the primary table is stored as ‘Amenity’. To resolve:
  • UPDATE ls_resp SET clause_type_id='Amenity' WHERE resp_id in ('AM40', 'AM41', 'AM42', 'AM43');

Space:

• rm table - Delete room: Hq-17-000 (3024461)

Version 19.2 (Database Version 133)

Schema Preferences:

• Update Db Version Date to: 2011.01.21
• Update Db Version number to: 133

Schema changes:

System:

• Support afm_notifications_log table and fields for email notification logging (this table was added in v19.1 but not supported in that release).
• Remove "Edit Group" values from afm_flds table for table afm_notifications_log (3029732)
• Add afm_dwgs and afm_dwgvers tables and fields for Drawing File Mgmt. Note that the Drawing File Mgmt features are not supported in v19.2.
• To support Radio Frequency ID functionality:
  • - Add rf_reader and rf_activity tables and fields
  • - Add current location fields to eq, ta, and em tables including: curr_site_id, curr_bl_id, curr_fl_id, curr_rm_id
• Add hpattern_acad field to the dv table (3029883).
• afm_activities.summary and afm_products.summary - change size from 512 to 800. Change sizes of all summary_* fields to 1200.
• Set afm_type to "Calculated" for all enumeration list fields that do not have a default value:
  • First update lang_lang.lang_to_fld_mapping to have a default value of '03'
  • Then: 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:

• Add cost fields to bl table that match those in the property table. (3028864)
  • cost_operating_total
  • cost_utility_total
  • cost_tax_total
  • cost_other_total
  • income_total
  • date_costs_start
  • date_costs_end
  • date_costs_last_calcd
• Add table: bl_amenity

Building Operations:

• Run System / Schema / Define / Update Service Desk and On Demand Work SQL Views using latest helpdesk_views.sql updated in 12/2010 to modify the hwr_month SQL view to support new fields such as supervisor. (3028984)
• Change probtype.prob_type to be of A/FM Type “Hierarchical-Concat”. (Note that this change is not sufficient for making prob_type hierarchical; in the next release a trace_id field will be added to the prob_type table.)
• Correct Validating table settings for the following fields:
  • helpdesk_sla_response activity_type
  • helpdesk_sla_steps activity_id
  • helpdesk_sla_steps activity_type
  • helpdesk_sla_steps ordering_seq
• add field "pmp_id" to the activity_log tables to facilitate SLA matching for Preventive Maintenance work requests. (3029391)
  • activity_log
  • hactivity_log

Reservations:

• rmpct.day_part -- change enum list to: "0;Full Day;1;Morning;2;Afternoon;" (3029345)

Capital Budgets:

• funding table - add new field date_avail_end. (3027862)

Workflow Rules (WFR) Additions and Changes:

New WFRs added:

• AbCommonResources / GraphicsService - deletes enterprise graphics files. (3029657)
• LeaseAdministrationService. The duplicateLease method provides for copying lease templates: (3028657)
• ProjectGanttService. The cascadeTaskDependencies method cascades dates for linked project tasks. (3027995)

New WFRs added for Building Operations applications:

• AbBldgOpsOnDemandWork
  • invokeSLAForWorkRequests -- integrates non-Run Anywhere Work Request processes with the Run Anywhere applications
  • updateWrDateAssigned -- integrates non-Run Anywhere Work Request processes with the Run Anywhere applications
  • updateWorkTeamFromSupervisor
• AbBldgOpsHelpDesk
  • updateServiceRequestStatusFromWorkRequest -- integrates non-Run Anywhere Work Request processes with the Run Anywhere applications
• AbBldgOpsBackgroundData
  • calculateWorkResourceValues -- provides parts inventory calculations

Energy Management:

• Change repeatInterval for the Energy Mgmt scheduled WFRs from "86400000" (every 24000 hours) to "86400" (every 24 hours).

Process Navigator (PNav) changes:

System:

• Add task: System Administration/Add In Manager/Add-in Application License Generator
• Add task: "View Notifications Log" in Utilities section under the ARCHIBUS Administrator process
• Updated elevator stories (summaries) for many of the domains (products) and activities.
• Remove unused Domain and Activities:

  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);

• Add Document Management views to Web PNav under the System Administration/ARCHIBUS Administrator role. (3028794)

Real Estate & Property Management:

• PNav updated to add additional views and reports, to correct some view links, and to add some help links.

Moves:

• PNav updated to correct file names particularly in regards to file name changes to match convention. (3026577); (3028765); (3028030)

Space Planning & Inventory:

• Correct duplicate dashboard names. (3028740)
• Add PNav entries to support Highlighting Groups and Rooms in the AutoCAD Extension.

Building Operations:

• PNav updated to add additional views and reports, to correct some view links, and to add some help links.

Asset Management:

• Add "Define Furniture Standards by Room" task (3027316)
• Add "View Tagged Furniture Inventory Counts by" views (3028167)
• - Remove leading spaces from tasks as the leading spaces will not work on MSSQL (3029898)

  UPDATE afm_ptasks SET task_id = '...' + SUBSTRING( task_id, 6, LENGTH( task_id ) ) WHERE task_id LIKE ' %';

Capital Budgeting:

• Remove: Capital Budgeting > Prioritize & Estimate > Create Facility Condition Index Scenarios as this functionality is now incorporated in the Analyze Facility Condition Index Scenarios view. (3029741)

Solutions:

• Add the following new solution templates (Technologies) tasks:

  Software Engineering / Software Engineer Views:

  • Miniconsole Views / Miniconsole with columns that can be shown, hidden, or sorted
  • Tree Views / Tree View with Multiple Selection Checkboxes

  User Interface Add-Ins / Parts for Forms:

  • Advanced Form Techniques / Console with Multiple-value Restrictions

  User Interface Add-Ins / Parts for Reports:

  • Report with Complex Parsed Restriction

  Business Process Owner Views / Paginated Reports:

  • Report with Print-out Restriction
  • Report with Passed Restrictions and Parameters

Data changes:

System:

• Drawing File Mgmt -- Add a user Z-CADHQ with a VPA Building Code List value of “HQ”. Use this user to test the VPA restriction on the Drawing List.
• Change user "CHARLES" to have a role at a higher level (3029667)
• Add a new activity parameter (afm_activity_params) record (3029026):
• AbSystemAdministration-highlightRGBColorEncoding, “Use Web Central-style RGB Encoding?”, 0/1
  Email notifications logging - add a new record in the afm_activity_params table to store the Notification Logging Level the system administrator requires:

Localization:

• afm_flds_lang updated to include records for the new v19.2 fields

Building Operations:

• Create a “default SLA” for the Hoteling service request type (3029808)
• Significant sample data added.

Project Management:

• Update project and activity_log sample data to include site and bl id values so that appropriate data is displayed when VPA is in use (3029743)

Space:

• Vertical and service areas hpattern values updated in rmtype table.
• Add values to 9 records in db table for dv.hpattern_acad field.

Real Estate & Property Management:

• Add "Amendment to Lease Agreement 001.docx" to the one record in the ls_amendment table. That record has a value in its doc field, however, no corresponding document records currently exist in the afm_docs or afm_docvers tables. (3028241)
• Update sample data for the following tables:
  • ot
  • property
  • bl
  • ls
  • prop_amenity
  • bl_amenity
  • ls_resp
  • cost_tran_recur
  • cost_tran_sched
  • cost_tran

Version 19.1 (Certified International Release) Database Version 132

Schema Preferences

• Update Db Version Date to: 2010.08.16
• Update Db Version number to: 132

Schema Changes:

• Make translatable fields wider to accept translations . resource_std.resource_name_% change from 32 to 64 (3028043)
• afm_roles table - change the AbCoreLevel4Activity display value to "Activity ACP"; set the Default Value for License Level to be "AbCoreLevel4Activity". (3028016)
• afm_flds_trans table - change the Default value to "AUTOINCREMENT" (it was Null).
• wo table - Remove the Edit Group value of "CALCULATED" from wo.date_closed and wo.time_closed. (3208048)
• afm_wf_steps table - Change the size of the following fields from 32 to 48 (3028043)
  • step_01
  • step_02
  • step_03
  • step_ch
  • step_de
  • step_es
  • step_fr
  • step_it
  • step_jp
  • step_ko
  • step_nl
  • step_no
  • step_zh
• afm_notifications_log table - Add this Notifications Log table (this is only needed for v19.2 but is being added now so work can proceed.). afm_notifications_log - holds notification history.

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.

• geo_region table - Add "Geographic Business Regions" table with fields:

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

• ctry table - add Fkey field to geo_region_id

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 )

• Run Comparator50.abs to verify database and especially FKeys.

WFR changes:

PNav changes:

• Correct error on some assets when trying to use the Number command. (3028055)

  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';

• Three context-sensitive Help links not working from three Smart Client Extension processes (3028233). Import items-3028233-doc1.xls.
• Add "Define Employee Standards" under "Move Management" -> "Enterprise Move Management" -> "Business Process Owner" (3028159)

Data changes:

System:

• Correct Wrong name for "Portfolio Management" in PNav’s "Summary" (3028080). The name of the Activity in the PNav is "Portfolio Management", but in the "Summary" is "Portfolio Administration" (old name).
• In the afm_users table, change the role for "TRAM" to “2 - WORKFLOW PROCESS”

Project Management:

• Add bl_id values to project table so that the following query returns values:

  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:

• - hreserve_rs table where rsres_id = 16 --- 9/26/2006 Cold food catering resource is reserved for -6 hours, which is not correct. (3028222). time_end value of 12:00am, which is not correct. Because 12:00 am is earlier than its time_start (8:00am).

Version 19.1 Database Version 131

A summary of database changes required per activity or domain when upgrading from v18.2 to v19.1 is listed in the below table.

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

• Add a field: help_system (enum: System;End User) to both the afm_processes and the afm_activities tables (3023526)
• Add a new field, afm_atyp.dflt_view_sc, same as afm_atyp.dflt_view except expand the column to be 64 characters long instead of the current 8 (3025039)
• Change "A/FM" to "ARCHIBUS":

  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

• Change the string format on the afm_users.user_pwd field from Upper to AnyChar (3024785)
• Add Data Transfer Status field to all system tables (afm_*) -- run CopyDataTransferFlds.abs
• afm_processes.process_type:
• Change default value to: WEB
• Add new values to enum list and add "Client/Server to Windows C/S items:WEB;
  Web PNav;WEB-DASH;Web Dashboard;SC;Smart Client;SCOVERLAY;Smart Client Overlay;WINDOWS;Client/Server;OVERLAY;Client/Server Overlay;
• afm_ptasks.task_type and afm_psubtasks.task_type: enum -- add two new values for Smart Client Grid and CAD Grid views; add "c/S" to Windows C/S items:
  LABEL;Label;WEB URL;Web URL;GRID;Smart Client Grid;GRIDCAD;Smart Client CAD Grid;OVERLAY ACTION;Overlay Action;VIEW;C/S View;FIXED-FORMAT VIEW;C/S Fixed-Format View;DRAWING VIEW;C/S Drawing View;BASICSCRIPT ACTION;C/S BasicScript Action;ACTIVEX ACTION;C/S ActiveX Action;WINDOWS CONSOLE URL;C/S Windows Console URL;SHELLEXECUTE;C/S ShellExecute;WINDOWS BROWSER URL;C/S Windows Browser URL;
  • afm_scmpref: Add two fields (similar to fields from afm_dwgs):
    • units -- "Base Units" -- smallint -- default: 2 -- enum: 0;Metric;1;Imperial;2;Per Locale;

      NOTE: value set for this field will be "1" even though the default is "2".

    • base_metric_units -- "Default Base Metric Units" -- smallint -- default: 1 -- enum: 0;mm;1;cm;2;Metres
• afm_dwgpub:

  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

• afm_tbls:

  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' );

• Add table: afm_transfer_set:

  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;

• Add table: afm_flds_trans:

  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;

• afm_flds_trans -- Make the autonumbered_id field the primary key
• Add the following to the enum for afm_flds.afm_type:

  ;2175;Questionnaire;

• Set the AFM Type for the following fields to be "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

• For performance add an index to em.email:

  CREATE INDEX em_email ON em (email) (3025541)

• Change afm_atyp.dflt_view and afm_atyp.dflt_view_sc to allow Null values.
• Remove circular references - Set "Validate Data?" to No for the following 2 fields:

  afm_atyp.pop_layer

  dp.approving_mgr

• Change afm_user.user_pwd and sql_pwd size to 128 (3025455)
• afm_ptasks.task_action change from size of 64 to size of 128 to accomodate longer Overlay commands
• Licensing and Revit changes ( \\coleburn\thetis\Spec\Yalta\Yalta9\discussion\V.19.1 Licensing Changes.docx ). Add new fields:

  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:

• LEVEL 1 users can not access certain validating fields that they need to use for executing self-service tasks:

  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

• Add a new su.doc_image field of type document as an alternative to the su.image_file field.

Asset Management Domain

• eqstd; fnstd: add 2 document fields (doc_graphic, doc_block) to use instead of the image fields (image_file, image_of_block)
• Add a document field to the warranty table: doc_warranty (3026700)
• Add a doc_image field to the following 3 tables:
  • pnstd
  • fpstd
  • pbstd

Move Management Domain

• add mo_scenario table for move scenarios.
• add mo_scenario_em table for employee move scenarios.
• add hist_em_count table for historical churn reporting.
• mo.phone_dept_contact change ml_heading to be the same as for project.phone_dept_contact
• mo_scenario table -- make the comments and the description fields to have string format "Memo" (3026045)

Service Desk/On Demand Activities

• Change the numeric format for helpdesk_step_log.pkey_value. This field stores either service request or work request ids.

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)

• Update SQL views used by SD/OD due to added fields in the activity_log table (3025620)

  Run: System /Schema /Define /Update Service Desk and On Demand Work SQL Views

Space

• change fl.area_gross_ext to have a default of "0.0" instead of "0".

Condition Assessment Activity

• Change multi-line headings:

  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

• Update the Reservations SQL Views: run reservationSqlViews.abs (2010-3-4 version)

Hoteling Activity

• rmpct:

  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

• visitors:

  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"

• activity_log and hactivity_log:

  Add: recurring_rule -- copy from reserve table

• rm:

  Add: hotelable -- "Is Hotelable?" -- SmallInt -- Default: 0 -- enum: 0;No;1;Yes

• rmstd_emstd -- Add table:

  Add: rm_std -- copy from rm table; make Pkey 1; Not Null

  Add: em_std -- copy from em table; make Pkey 2; Not Null

• rmstd:

  Add: doc_graphic -- copy eqstd.doc_graphic; change ML Heading

  Add: doc_block -- eqstd.doc_block; change ML Heading

• dp:

  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

• Add records to afm_tbls:
  • Add vn_ac table
  • Add bill* tables
  • Add weather* tables
  • Add energy* tables
• Add records to afm_flds -- these are all records where afm_flds.comments LIKE 'v19.1 Energy Management%'

In addition to the fields for the tables listed above added fields to bl table

• Add enum value ('Energ;Energy;') to vn.vendor_type

Localization:

• Change lang_strings.string_trans size to 768 and data type to VarChar. (3024613)
• Add field "locale" to the lang_lang table. (3027425) 

ML Heading - "Default Locale to write to lang file"

CHAR - length 5

Default value - 'en_US'

• Change lang_lang.is_default_language to SmallInt with a default of 0. (It is a "0;No;1;Yes" enum but was oddly Char 512)
• To get the new fields from the new fields in 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 )

Work Flow Rules

System:

• Add rule for data transfer by record
• Add WFR for Gantt control
• DELETE FROM afm_wf_rules WHERE rule_id = 'appUpWizServiceRule'; because we are using a different one and errors occur in the archibus.log (3026060)
• DELETE FROM afm_wf_rules WHERE rule_id = 'dataTransferInByRecord'; (AbSystemAdministration) as this should not be called from views.
• PDF Forms - add rule for pdfLiveCycle.
• Add two WFR's for Basic Rule Wizard:

  Activity: AbCommonResources
  Rule: BasicRules
  

  Activity: AbSystemAdministration
  Rule: BasicRuleWizService

• Add new WFRs for AbCommonResources services:

  Activity: AbCommonResources
  Rule: DataSourceService

  Activity: AbCommonResources
  Rule: JobManagerService

• Add a new WFR:

  Activity: AbSystemAdministration
  Rule: PasswordManagerService

• Add WFRs in for AbSolutionsLogicAddIns (formally known as AbSolutionsLogicCookbook). (3027012)

Hoteling Activity

• Add WFRs for checking Hoteling

Service Desk/On Demand Work Activities

• Consolidate OD/SD WFR's to service level rules instead of individual method rules

Portfolio Administration Activity

• Remove rule that was replaced with JavaScript:

  DELETE FROM afm_wf_rules WHERE rule_id = 'checkOwnership' AND activity_id = 'AbRPLMPortfolioAdministration';

Portfolio Forecasting Activity

• Update WFR's to new service format: (3025763)

Government Property RegistryActivity

• Update WFR's to new service format:
• UPDATE afm_wf_rules SET rule_id = 'GovPropertyRegistryService' WHERE activity_id = 'AbRPLMGovPropertyRegistry' AND rule_id = 'ActivityService';

Capital Budgeting/ Project Management Activities

• Update WFR's to new service format (ProjectManagementService, MsProjectService and CapitalProjectsService) (3025759)

Emergency Preparedness Activity

• Add EP Common Handler (3026971)

Energy Management

• Add WFRs

Reservations Activity

• Consolidate Reservations WFR's

Move Management Activity

• Consolidate Move Mgmt WFR's

Asset Management Activity

• Add WFRs

Package and Deploy Wizard

• Add new afm_wf_rules record for the App Up Wiz

Process Navigator

Smart Client:

• Add Smart Client Process Navigators

System

• Update AbSolutions (3026908)
• Rename and re-order Sys Man ptasks (3027052)
• UPDATE afm_ptasks SET task_file = 'ab-sys-user-procs.axvw' WHERE task_file = 'ab-ex-user-procs.axvw' AND activity_id = 'AbSystemAdministration';
• Rename the �Risk Management� domain to "Environmental & Risk Management" Domain.
• Add: System Administration / Business Process Owner / Run Basic Rule Wizard --- ab-basic-rule-wizard.axvw
  under "Run Dashboard Definition Wizard"
• Add task for: AbSystemAdministration / ARCHIBUS Administrator / Encrypt Passwords in Configuration Files
• Overlay Process Navigator task updates
• Update help links.
• Add "Report with Data Transfer of Documents" --- ab-ex-dt-activity-log-with-docs.axvw
  under: Technologies / User Interface Add-Ins / Parts for Reports / Report with DOC, PDF, Excel, and Data Transfer Actions
• Change afm_pubgraphics to afm_pubgraphicst to get the command line form of this command.
• Rename the Technologies/System Integrator Views to User Interface Add Ins.
• Rename the Business Logic Integrator Views to the Business Logic Add Ins.
• Rename "extensions" to "Add In Management".
• Delete Workflow Manager process under under System Administration activity (moved items to Add In Manager ).
• Move AbSolutionsLogicCookbook activity tasks into AbSolutionsLogicAddIns.
• Delete AbSolutionsLogicCookbook activity.
• Create a Basic Rule Wizard ptasks in the AbSolutionsLogicAddIns process.
• Move ARCHIBUS Administrator tasks (password and user management) from System Administrator to ARCHIBUS Administrator.
• Rename the System Administration/System Integrator process to Add In Management.
• Move the Add In Management tasks (workflow rules, views, pnav tasks) from Business Process Owner to Add In Management.
• Remove "/FM" from some activity descriptions.
• Delete "Workflow" process and ptasks from beneath the User Interface Add Ins (as it is now under Business Logic Add Ins).
• Create new views for the System Administration Activity--Assign Processes to Roles, Create User Identities from Employees, Synchronize User and Employee Identities and for the Technologies/Parts for Forms--Adding Panel Instructions (kb 3024782)
• Create two new WFRs--assignAllActivitiesToRole, deleteAllProcessesAssignedToRole

Background views

• Update PNav to remove old background views and replace them with the new Define Locations and Define Organizations views (3024964):

Move Management Activity

• Remove old Define views and replace with Define Locations and Define Organizations
• Update Move Process Navigator with new role and tasks:
  • Change Define Employees, under BPO, to point to: ab-sp-def-em.axvw
  • Delete existing Churn Reports from afm_ptasks
  • Add Scenarios
  • Add an afm_userprocs record assigning activity_id="AbMoveManagement" process_id="Move Scenario Planner" to users AFM and Abernathy
• Move Business Process Owner process to first in list (KB 3025859)

• Add a view for defining Tagged Furn
• Remove the task "Examine Group Move Spreadsheet"
• Add Dashboards
• Remove remaining "Spreadsheet" views. (3027185)

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

• Make the following changes to the Web Central Process Navigators to make them consistent with the additions for the Smart Client Process Navigators:
  • Asset Portal - Background Data (Smart Client Only � do not put on Web)
  • Asset Portal - Equipment Asset Control (updated process, add new URLs, don�t add grid views)
  • Asset Portal - Furniture Asset Control (updated process, add new URLs, don�t add grid views)
  • Asset Portal - Depreciation (new process)

• Add some telecom views
• Add Furn. Std. views
• Under Equipment, under Telecom Console, add: "View Jack and Equipment Plan" (ab-ap-eq-plans.axvw )
• Change "Telecom Assets Console" to "Equipment Assets Console" (3026259)
• Web Central, move Edit Furniture Standards from the Tagged Furniture process to the Background Data process, as that is where all other standards data is developed.
• Change both "Tagged Furniture" process titles to just "Furniture" since furniture standards inventory tasks are included.
• Software - View and Edit Software Standards - Move to Background process.
• Add rules to publish Tagged Furniture and Furniture Standards to the drawing publishing rules table.
• Add a Process task to publish furniture graphics.

Space

• Add reports in Shared Workplace Chargeback: "View Over-allocated and Under-allocated Rooms", "View Overall Utilization by Department", and "View Overall Utilization by Room".
• Import - afm_ptasks_view_overallocated_rooms.xml
• Update Space Planning & Management P-Nav to make it clearer and more organized (3024947)
• Add three tasks to the Space Inventory & Performance activity to add/edit hatch patterns. (3026975)

Emergency Preparedness

• Change "AbSpaceEmergencyPreparedness" to "AbRiskEmergencyPreparedness".
• Add Smart Client PNav entries
• Add some PNav entries for Overlay to publish EP Zone and Reg Compliance info. (3024441)
• Change the "Business Unit Managers" / "Review Site Status by ..." task names to "Review Room Status by ..."
• UPDATE afm_ptasks SET task_file = 'ab-ep-draw-fire-zones-gd.axvw
  'WHERE activity_id = 'AbSpaceEmergencyPreparedness' AND process_id = 'Define Systems and Zones (SC)' AND task_id = 'Draw Fire Zones';
• First Responders -- Call Up Egress Plans/Hazmat Plans tasks -- Change to "View Egress Plans", etc.
• Change "Emergency Response Team" process to "Emergency Response & Recovery Teams" (3026809)
• Change Zone tasks to be consistent (3026797):

  "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}

• "Define Systems and Zones (CAD)" and "Define Systems and Zones (SC)" - Add views for: (3026750)

  "Draw Egress Plans" -- ab-ep-draw-egress-plan-gd.axvw

  "Draw Hazardous Material Plans" -- ab-ep-draw-hazmat-plan-gd.axvw REG-EGRESS

• Web Process Navigator - Add the following two views to the Web Emergency Preparedness Business Process Owner process:

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

• Add two new views to the Web Emergency Preparedness Business Process Owner process:

Define Geographic Locations----------ab-def-geo-loc.axvw

Define Locations

Define Organizations

Define Companies ---------- ab-rplm-companies-define.axvw

Define System Types

• Smart Client Process Navigator - Add "Define System Types" - The new view should be: ab-ep-def-system-types-gd.axvw.
• Business Process Owner - Remove Define Recovery Team Call List and add to Emergency Response & Recovery Teams process (3026788)
• (3026830):

  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';

• (3026831):

  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

• Change legacy background data views to use newer location and organization views ((3024786)

Hoteling Activity

• Activate the 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 Hoteling Process Navigator entries:

  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

• For two records in afm_ptasks, the task_file is set to "ab-sp-def-loc.axvw", but it should be set to "ab-sp-def-loc-rm.axvw". (3024964)
• Activity AbWorkplaceReservations, process Reservation Manager
• Activity AbWorkplaceReservations, process Develop Background Data

Service Desk/On Demand/Preventative Maintenance Activities

• Add a task to allow clients to review and submit previously un-submitted service requests. (3021037)
• Put Bus. Proc. Owner process at top

  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';

• Add vendors to On Demand Work under Inventory Manager (3026448)
• Consolidate the views that supervisors and craftspersons use to issue, update, and update labor hours for work requests.
• Add Define Work Teams task (3026500)
• Update afm_ptasks to include Define Geographical Locations in the Business Process Owner processes for Service Desk, On Demand Work, and Preventive Maintenance.
  Along with the inclusion of the Timezone field on the City tab, this will allow users to define timezones for use in these applications.

Energy Management Activity

• Change activity_id = "AbRPLMEnergyManagement" to "AbRiskEnergyManagement"
• Add Process Navigator entries

Environmental. Sustainability:

• Add PNav entries

Hoteling Activity

• Add PNav entries

Real Estate Portfolio Management Domain

• New Suite view additions listed in: \\Coleburn\Thetis\Spec\Yalta\Yalta9\active\Smart Client Process Navigator Views.docx

  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

• Portfolio Management / Background Data --- under Define Locations add Define Counties - ab-repm-counties-define.axvw
• Update task list for: AbRPLMLeaseAdministration / Lease Portfolio

Project Management Activity

• UPDATE afm_ptasks SET task_file = 'ab-proj-projects-calendar.axvw' WHERE task_file = 'ab-proj-projects-calendar-mssql.axvw';
• Add afm_ptask for �View Active Projects Timeline� ( ab-proj-active-projects-timeline.axvw ) under Capital Project Management / Project Management / Monitor / View Projects Map

Localization Activity

• Add view for: Edit Language Glossary Table (lang_glossary) (3027060)

Licensing

• Define a new ACTIVITY LICENSEE role for application-style licensing.
• Create a new AIDEMOACT for testing activity style licenses and Z-NAMED% users for testing named licensing.

Technologies Domain

• Add 3D chart views
• Add new grid and paginated report sample views to the Technologies Process Navigator (3027629)

Data changes: 

System:

• Set default Smart Client Asset Type views.
• Set afm_scmpref.units to 1
• messages table - Update message for AbSystemAdministration / SEND_REQUEST_NEW_PASSWORD_BODY to be:
  
  ** This is an automatically generated message. Please do not respond to this e-mail ***
  
  User {0} has requested a change of password. (3024741)
• afm_flds - enum list for afm_dwgpub.rule_type � remove extra line return in �QUERY TEXT�
• Update afm_dwgpub rules for SWF and JSON rules:
  
  Import: afm_dwgpubUpdateFor191.xml
• afm_scmpref.preferences - add: <field table="questionnaire" field="title" /> (3026427)
• Add data afm_roles, afm_users, and afm_roleprocs data to support new licensing changes
• Add the '%' security group to the level roles so that users are not locked out from editing any fields.
• Add user Z-ACP for testing.
• Update afm_atyp to make a number of tables database driven instead of drawing driven.
• Remove obsolete activity parameter code entry AbSystemAdministration / ESRIArcWebServicesKey (3027882)
• Change the afm_activities.summary values for AbRiskEs and AbSpaceHoteling to remove "Extension License"
• Import lang_lang.locale data. (3027425)
• Update AFM password to be lowercase:
  
  UPDATE afm_users SET user_pwd = 'afm' WHERE user_pwd = 'AFM';
• Update afm_userprocs to assign user 'AFM' to all processes:
  
  

  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

• Update helpdesk_sla_response and helpdesk_sla_steps for an extra Verification step in one of the PM SLAs.

Hoteling Activity

• Add Activity Parameters.
• Add activity_type of �SERVICE DESK � HOTELING�
• Add afm_groupsforroles data for hoteling
• Add afm_userprocs data for hoteling
• Add the "SPAC%" security group to the roles: HOTEL ALL DPS and HOTEL NO APPROVAL (3025571)

Asset Management Domain

• Add records to afm_dwgpub for jk and eq assets:

  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

Version 18.2 Database Version 130

Schema Preferences

• Update Db Version Date to: 2009.09.13
• Update Db Version number to: 130

Schema changes

System

• afmroles: changed vpa_restriction to be 5000 characters in size. (3022214)
• Set the following field's afm_type to "Calculated":

• rm: Added data transfer field
• em: Added a document field for Employee photo.

Real Estate Portfolio Management Domain

• contact: Added a document field for contact photo.

Condition Assessment Activity

• Added assessment_id to activity_log and hactivity_log tables. Same as activity_log_id, calculated so can not manually edit. Automatically filled in.

Service Desk/On Demand Work Activities

• Ran "System/ Schema/ Define/ Update Service Desk and On Demand Work SQL Views" to recreate hactivity_logmonth SQL view (3023701)

Preventive Maintenance Activity

• Support SLA's for PM Work Requests:
  • Added field helpdesk_sla_request.pmp_id
  • Added new table named “pmgen” and titled “PM Work Order Generation Rules”. (Provides for defining schedules, for filtered conditions, for when PM Work Orders will be auto-generated.)
• Added field pmp.pmp_ids_to_suppress

Project Management Activity

• project table; fields date_app_mgr2 and date_app_mgr are currently Char(10); changed to be date fields (33024244)

Localization Activity

• Add Localization activity data (3023984)

  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

• Added Data Transfer WFR
• Added Cascading Updates and Deletes WFR

Condition Assessment Activity

• Added ConditionAssessmentService WFR

Preventive Maintenance Activity

• Deleted all existing WFR's for PM and then added PMEventHandlers for v18.2

Capital Budgeting:

• Update xml_rule_props field for copyTemplateActionsToProject to handle V1.0/V2.0 views

Process Navigator

System

• Added task "Merge Primary Key" under System Admin/ Business Process Owner/
• Changed: /Technologies /System Integrator Views /Parts for Reports / "Report with PDF and Excel Export Actions" to: Report with DOC, PDF, Excel, and Data Transfer Actions
• Added sub-folder values to the afm_activities table for those records that are Active, have Web Processes, and are missing values for the sub-folder. (Needed for localization).
• Added "Report with Passed Restrictions & Parameters" to Business Process Owner Views / Paginated Reports
• Removed help links from some afm_activities
• Updated help links for afm_processes

Preventive Maintenance

• Added a process on the Process Navigator “Service Desk Manager”.
• Added a task “Manage Work Order Generation Rules” to the Maintenance Manager process.
• Added a task “Planning Board” to the Supervisor and Supervisor (Work Requests) processes within AbBldgOpsPM.
• Changed the task name “View Unissued PM Work Orders” to “View Active PM Work Orders”.
• Added a task “Overdue PM Work Requests” to the Reports process.
• Changed file for PM / Service Desk Manager / "Manage Service Level Agreements" task
• Added PM Dashboard

Condition Assessment

• Implemented the Process Navigator for Web Central

Localization Activity

• Added an afm_activities record for the Activity: “AbSolutionsLocalization”
• Assigned the AbSolutionsLocalization activity to the AbSolutionTemplates (Technologies) Domain with an afm_actprods record.

Space

• Added an additional Space Mgmt dashboard

Capital Budgeting:

• Updated Process Navigator

  • 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

• Added a new view: View Users Logged Into ARCHIBUS (3023925)

  Beneath this current view: System Administration / System Administrator / View Program Information and License Usage

Data changes

Condition Assessment

• Added and updated sample data for the following tables:
  • project
  • afm_processes
  • afm_ptasks
  • afm_userprocs
  • wo
  • activity_log
  • wr
  • wrcf
  • wrtr
• Add image data to 4 activity_log records

• Add to projecttype table: project_type="ASSESSMENT - ENVIRONMENTAL"; description="Environmental Assessment project"
• Copied Condition Assessment images from image fields to document fields.

Preventive Maintenance

• Sample data changes:
  • pmgp_add_ahu_route.xml
  • pms_update_pm_group.txt
  • cf_add_jeff_to_market_team.txt
  • pmp_for_suppression.xml
  • pmgen_for_ahu_pm_pmgp.xml
  • helpdesk_sla_request_for_pm_slas.xml
  • helpdesk_sla_response_for_pm_slas.xml
  • afm_ptasks_correct_sla_task_file.txt

Service Desk/On Demand Work

• Changed link in Notification of Escalation for Response (3023826):

Space

• Updated room types and room standards hatch patterns because highlight pattern legends did not display because the Hpattern Acad value was either null or white. (3023798)
• Added building images to the bl and property tables doc fields.

Project Management

• Updated dates for activity_log records assigned to projects so that the tasks are more current (3015504)

Version 18.1 Database Version 129

Schema Preferences

• Updated Db Version to 129
• Updated Db Version Date to 2009.06.29

Web - Core

Schema:

• afm_flds table: set "Is Asset Text?" to 4 for rm.area
• Add fields for default width and height for drawings in paginated reports at tabloid (11x17) or at A3 size (as opposed to the A4 size). (3022469)
• Localization - Update afm_flds_lang from afm_flds

Workflow Rules:

• Add new WFR's for v18.1 Space Mgmt, RPLM, and core (3021444)
• DELETE FROM afm_processes WHERE display_order = 0 AND process_type = 'WINDOWS'; (3021903)
• Add new AbSystemAdministration WFR: AbSystemAdministration-ConfigHandlers
• All AbSolutionsViewExamples WFRs have been removed and replaced by four new WFRs:
• AbSolutionsViewExamples-LogicExamples
• AbSolutionsViewExamples-JobExamples
• AbSolutionsViewExamples-TimelineExamples
• AbSolutionsViewExamples-WSClientExamples
• Import .../ab-products/common/AbCommonResources-workflowrules.xml ( AbCommonResources-SpaceService WFR was previously incorrect )

• Paginated reporting - import - afm-docx-paginated-report-wrokflow-rule.xml
• DELETE FROM afm_wf_rules WHERE activity_id = 'AbRPLMPortfolioAdministration' AND rule_id = 'getMapData'
• Import: paginated_reporting_wfr.xml (this adds the generatePaginatedReport rule with the ReportBuilderJob as opposed to the previously added generateDocxReport rule for ReportBuilder.)
• Import: PerUserFileExpiration_afm_wf_rules.xml (3022579)
• Four scheduled rules use incorrect repeatInterval value.
• First, restrict afm_wf_rules WHERE xml_sched_props LIKE '%repeatInterval="86400000"%'
• Delete the xml_sched_props value from the AbBldgOpsHelpDesk/ testTemplate rule.
• Then, change "86400000" to "86400" (these values should be seconds in a day; NOT milliseconds).
• Add AbCommonResources-EmailService and AbCommonResources-HighlightPatternService
• Import the new rules defined in ab-products/solutions/logic-cookbook/AbSolutionsLogicCookbook-workflowrules.xml
• Import the workflow rules from AbSolutionsWorkflow-workflowrules.xml

Process Navigator:

• UPDATE afm_ptasks SET task_file = 'ab-setup-license-file.axvw' WHERE task_file = 'setup-license-file.axvw' (3022747)
• UPDATE afm_ptasks SET task_action = 'afm_vertservtorm' WHERE task_action = 'AfmConvertVertServToRoom'; (3023147)
• DWF to SWF changes:
  • Import afm_ptasks_DWF_to_SWF_AbEmergPrep.xml
• Consolidate Enhanced All Room Inventory and Basic All Room Inventory in Windows and CAD PNavs. (3023221)
• Added help links for new v18.1 Web processes.
• Update online help links. Import: misc_help_links_v181.xml
• Two "Calculated Field" reports added to AbSolutionsViewsSystemIntegrator (calculated_SQL_field_ptasks.xml)
• Five "Cross-table" views added to AbSolutionsViewsSystemIntegrator (cross_table_ptasks.xml); (cross_table_ptasks_additional.xml)
• Update GIS views (3022315)
• Change Move Management activity to Enterprise Move Management (3022528)
• Remove the "/FM" from the ARCHIBUS/FM System Administration activity title (3022528)
• Update for help links.
• Under: Technologies > Software Engineer Views > Advanced Techniques. Import:
• questionnaires_ptasks.xml
  
• Questionnaire Wizard (solutions/programming/questionnaire/ab-ex-prg-questionnaire-wizard.axvw)
• Virtual XML Fields: A Questionnaire Report (solutions/programming/questionnaire/ab-ex-prg-questionnaire-report.axvw)
• Virtual XML Fields: A Questionnaire Form (solutions/programming/questionnaire/ab-ex-prg-questionnaire-form.axvw)
• Generating a Questionnaire Action Response (solutions/programming/questionnaire/ab-ex-prg-questionnaire-action-response.axvw)
• Add the AbSolutionsLogicCookbook to the PNav: (3022729)
• Make inactive: Workplace Portal activity
• Make inactive: Building Ops/Building Ops activity
• Add new icons for Product level in PNav. Import: afm_products_32x32_icon_names.xml
• Correct the large icons for the Activity and Move Management processes. Import: afm_process_16x16_icon_names.xml
• Add paginated report views to the Technologies PNav. Import:
• 18-1_afm_processes_entries_Paginated_Reports.xls
• 18-1_afm_ptasks_entries_Paginated_Reports.xls
• afm_userprocs_entries_PaginatedReports.xml

Data:

• Import items-3023183-doc1.xml -- adds Drawing Publishing rules: AbPubRegcompliance, AbPubZone
• UPDATE afm_dwgs SET report_height_tabloid = 9.5 (3022912)
• Table ac - corrected sample data in hierarchy trace field
• add to afm_scmpref.preferences:
• <field table="programtype" field="program_type" />
• <field table="programtype" field="description" />
• In cases for which we are listing “Reports” as the process, don't repeat the activity name in the Report process name.
• Bad data in contact table: UPDATE contact SET contact_id = UPPER( contact_id ) (3022636)
• Add perUserFileExpiration activity param. Import: items-3022578-doc1.xml (3022578, 3022579)
• Add group publishing rule and update descriptions of enterprise graphics rules. Import: afm_pubdwg_entgraphics_rules.xml (3022586)
• Add the ESRI ArcGIS Online Activity Param: ESRIArcGisOnlineServicesKey -- Import: ESRIArcGisOnlineServicesKey.xml (3022621)

Real Estate Portfolio Management Domain

Schema:

• Add fields:To invoice_payment table (An invoice_payment record with a NULL invoice_id can be used to record overpayments):

• contact_id --- to record who the payment is from, validates on contact table
• payment_method --- Char 6 -- enum: CHECK;Check;CREDIT;Credit Card;DEBIT;Debit Card;PAYPAL;Paypal;WIRE;Wire Transfer;
• Forecasting:
  • Add table: portfolio_scenario (portfolio_scenario_id and description fields)
  • gp table - Add field: portfolio_scenario_id
  • Create a validating Portfolio Scenario table.Make sure this always has a "Baseline" default scenario record.
• Change ml_heading of portfolio_scenario_id from: "Portfolio Scenario Code" to: "Portfolio Scenario"
• Add portfolio_scenario as validating table for gp.portfolio_scenario_id in afm_flds
• Change the fields ( area_common, area_rentable, area_usable ) in the ls table to be Numeric 10.2 instead of Numeric 9.1 (3022851)

Workflow Rules:

• Import ...\schema\ab-products\rplm\common\AbRPLMPortfolioAdministration-workflowrules.xml ( updates the AbRPLMLeaseAdministration-calculateCashFlowProjection eventhandler to use the new "simple" workflow methods. )
• Import: afm_wf_rules_AbRPLMGroupSpaceAllocation_updateGroupAllocationCosts.xml
• Import: RPLM_Portfolio_Scenarios_wfr.xml
• Import WFR: ...\schema\ab-products\rplm\common\AbRPLMChargebackInvoice-workflowrules.xml file

Process Navigator:

• Add new PNav entries for Cost Admin and Cost Chargeback and Recievables
• Change the domain "Real Property Portfolio Management" to "Real Estate Portfolio Management"
• Make inactive: Real Property and Lease Portal activity
• Add REPM dashboards. Import:
• REPM_Dashboard_afm_processes.xml
• REPM_Dashboard_afm_ptasks.xml
• REPM_Dashboard_afm_userprocs.xml
• Add To: Real Property Portfolio Management – Cost Chargeback & Invoicing – Business Process Owner:
• Add: under - Real Estate – Portfolio Management – Portfolio Management – Reports. Import
• PortfolioAdministration_Reports_Afm_processes.xml
• PortfolioAdministration_Reports_afm_ptasks.xml
• PortfolioAdministration_Reports_Afm_userprocs.xml

• Buildings by ...
  • ... Country ab-rplm-pfadmin-bldgs-by-country-report.axvw
  • ... City ab-rplm-pfadmin-bldgs-by-city-report.axvw
• All Buildings and their Book Values (View file name: ab-rplm-pfadmin-bldgs-book-values-report.axvw)
• Structures by:
  • ... Country (View file name: ab-rplm-pfadmin-struc-by-country-report.axvw)
  • ... City (View file name: ab-rplm-pfadmin-struc-by-city-report.axvw)
• Land by:
  • ... Country (View file name: ab-rplm-pfadmin-land-by-country-report.axvw)
  • ... City (View file name: ab-rplm-pfadmin-land-by-city-report.axvw)
• All Properties and their Book Values (View file name: ab-rplm-pfadmin-props-book-values-report.axvw)
• Portfolio Investments (View file name: ab-rplm-pfadmin-investments.axvw)
• Portfolio Sales (View file name: ab-rplm-pfadmin-sales.axvw)

• Add: Portfolio Forecasting activity

Data:

• Import sample data for Forecasting activities:
• LXBuilding.xml
• LXFloor.xml
• LXGroups.xml
• LXCosts.xml
• Update cost_tran_recur records for rent costs in HQ bldg that expire in 2008 and 2009 to expire later
• Import: prepayments_for_invoice_payment_table.xml
• ls table - Updated dates; updated and filled in area negotiated. Import: ls_date_update_and_filling_the_area_neg_rentable_field.xml
• Invoice - Closed an old invoice. Make Several new invoices. Import: RPLM_v18_1_invoice.xml
• Invoice_payment - Added some invoice payments. Added some prepayments. Import: RPLM_v18_1_invoice_payment.xml
• Cost_tran_recur - An update to the cost dates. Import: cost_tran_recur_plus_three_years.xml
• Cost_tran_sched - An update to the cost dates. Import: cost_tran_sched_plus_three_years.xml
• Cost_tran - An update to the cost dates. Some costs were marked with Cost Status as Receivable but the Date Paid was not NULL.
• Updated some of the cost statuses to Received. Added Invoice Code (since several invoices were added).
• Import: cost_tran_plus_three_years_and_updated_invoice_id.xml

Space Management Domain

Schema:

• Add fields:
• rmcat table:
  • used_in_calcs: Char 20 default "all_totals"
  • enum list: all_totals;All Totals;rm_totals;Room Totals Only;dp_comn_ocup_totals;Dept., Common, Ocup Totals Only
  • supercat: Char 4 default "PERS"
  • enum list: VERT;Vertical Penetration;SERV;Service Area;PERS;Personnel Area;OTHR;Other Area
  • cost_sqft (same as in bl and fl tables)
• rm table:
  • cost_sqft (same as in bl and fl tables)
  • cap_em (Employee Capacity)
  • rmcat.used_in_calcs -- add ";no_totals;No Totals" to the enum list.
  • rmcat.supercat -- change the PERS value in the enum to: USBL;Usable Area; change the default value to "USBL"
• Also: UPDATE rmcat SET supercat = 'USBL';
• Decimal places set to 0 in the fl table in the physical database (not in afm_flds). Run Update Schema on the fl table to resolve. (3022415)
• In afm_flds table, REPLACE( ml_heading, 'Comn.', 'Common') WHERE ml_heading LIKE '%Comn.%' (3022852)
• fl table -- add "Total" to the ML Heading for the Room Area fields (3022853)

Workflow Rules:

• Import: workflow-highlight-dialog.xml

Process Navigator:

• Add new PNav entries for v18.1 Space Mgmt Web Activities
• Remap existing Web Space activity ptasks to new views. Import: afm_ptasks_webc_space_legacy_views.xml
• Change the titles of the Process Navigator tasks that generate these graphics from:
• Publish to SWF (This Drawing)
• Publish to SWF (All Drawings)
  
  To
  
  

• Publish Enterprise Graphics (This Drawing)
• Publish Enterprise Graphics (All Drawings)

• Update Windows and CAD PNavs. Import:
• Space_Win_CAD_afm_activities.xml
• Space_Win_CAD_afm_processes2.xml
• Space_Win_CAD_afm_ptasks.xml
• Space_Win_CAD_afm_userprocs.xml

Data:

• Add an Activity Parameter: AbCommonResources includeGroupsInUnifiedSpaceCalcs value = 1
• UPDATE rmcat SET supercat = 'VERT' WHERE rm_cat = 'VERT';
• UPDATE rmcat SET supercat = 'SERV' WHERE rm_cat = 'SERV';
• Eliminate group data from HQ 17, 18, 19: DELETE FROM gp WHERE bl_id='HQ' and fl_id in ('17','18','19');
• Import new Space sample data:
• NewRoomCategories.xml
• NewRoomTypes.xml
• HQ15HQ01Floors.xml
• HQ15HQ01Drawings.xml
• HQ15HQ01Gross.xml
• HQ01Groups.xml
• HQ15HQ01Rooms.xml
• HQ17HQ18HQ19Rooms.xml (New Vertical and Service Rooms)
• XC02XC03XC04Rooms.xml (New Vertical and Service Rooms)
• HQ15Employees.xml
• Run Space Planning & Management / Space ChargebackDevelop / Space Chargeback:
• Update Area Totals
• Perform Chargeback
• Run Space Planning & Management / Personnel and Occupancy / Develop Background Data:
• Update Employee Headcounts
• UPDATE rm SET cap_em = (SELECT std_em FROM rmstd WHERE rmstd.rm_std = rm.rm_std) WHERE rm_std IS NOT NULL AND cap_em = 0;
• Update hatch patterns. Import: dp_facilities_highlight_pattern.xml
• Drawing publishing rules: AbPubGp -- is missing "gp" in Assigned table column. Add it.

Service Desk and On Demand Work activities

Schema:

• Work Team support for Work Orders (3021926, 3021301)
  • Add work_team_id to wo and hwo tables (copy from table wr and hwr)
  • Update the enumerated list for two columns in helpdesk_sla_response: (3021917)
  • interval_to_complete and interval_to_respond. Add minutes:n;Minute;h;Hour;d;Day;w;Week;m;Month
  • Update translations in afm_flds_lang: Import items-3021917-doc2.xml
• messages.description - change to be size 512
• messages.is_rich_msg_format - Add. smallint; Default 0; enumeration list - 0;No;1;Yes
• support configurable e-mail messages (3022032, 3021340)
  • afm_wf_steps - add fields "attachments", "body_message_id", and "subject_message_id" - import KB3022032-afm_wf_steps-fields.xml
  • afm_wf_rules - add new WFR's - import KB3022032-afm_wf_rules_data.xml
  • afm_wf_steps - adds sample data - import KB3022032-afm_wf_steps_data.xml
• Ran DBUPWW.abs to add work_team_id to the SQL view wohwo
• Forwarding SD/OD steps (3020144) -
  • afm_wf_steps data updated
  • 3 afm_wf_rules added
  • Fields with field_name like 'step_status%' - updated enum_lists.
• custom notification support (3021340) -
  • import 2 new (DISPATCH_VIEW, ASSIGN_VIEW) and update 1 (REVIEW_VIEW) afm_activity_params records.
  • Import new and updated messages for Service Desk and On Demand Work.
  • Add 3 fields:
  • helpdesk_sla_response.notify_craftsperson
  • helpdesk_sla_response.notify_service_provider
  • helpdesk_sla_steps.notify_responsible
  • Update SQL views for SD/OD
• Step Type enum list updated to have a display value of “Edit and Approve” where the data value is “review” (update for v17.3 missed some).

Workflow Rules:

• Import ...\schema\ab-products\bldgops\common\AbBldgOps-workflowrules.xml

Process Navigator:

• new navigator tasks for SD/OD / System Integrator - Manage Notifications; and for Searching for requests
• DELETE FROM afm_ptasks WHERE activity_id = 'AbBldgOpsHelpDesk' AND process_id = 'Service Desk Manager'
  AND (task_id = 'Review all Service Requests and Work Requests' OR task_id = 'Review all Escalated Service Requests');
• DELETE FROM afm_ptasks WHERE activity_id = 'AbBldgOpsOnDemandWork' AND process_id = 'Service Desk Manager'
  AND (task_id = 'Review all Service Requests and Work Requests' OR task_id = 'Review all Escalated Service Requests');

Data:

• Add new step in afm_wf_steps called "Forward to Work Team" - import items-3021926-doc2.xml (3021926, 3021301)
• Add the ability to forward service requests in the Requested status with Search. Import: bldgops_forward_requested.xml

Reservations activity

Schema:

• run new reservationsSQLViews.abs

Data:

• Import messages - 2008-01-13-Reservations-Timeout-message.xml (3021386)
• Add new messages to database to support additional timezone functionality - items-3021928-doc1.xml (3021928)
• Import rm_config_data.xml
• Import rm_arrange_data.xml

Preventive Maintenance activity

Process Navigator:

• Consolidate all PM reports into one process on P-Nav: (3021910)
• DELETE FROM afm_processes WHERE activity_id = 'AbBldgOpsPM' AND process_id LIKE 'Preventive Maintenance%';
• import items-3021910-doc1.xml, items-3021910-doc2.xml, items-3021910-doc3.xml

US Federal Property Registry activity

Data:

• Update sample data:
• UPDATE grp_trans SET size_unit_of_measure = NULL WHERE size_unit_of_measure = 0;
• UPDATE grp_trans SET mission_dependency = NULL WHERE mission_dependency = 0;
• UPDATE grp_trans SET historical_status = NULL WHERE historical_status = 0;
• UPDATE grp_trans SET utilization = NULL WHERE utilization = 0;

Version 17.3 Database Version 128

Schema Preferences

• Updated Db Version to 128
• Updated Db Version Date to 2008-01-08

Web – Core

Schema:

• Added the sql_pwd and sql_uid fields to the afm_users table (same as in afm_roles) (3020695,3018685).
• afm_flds_lang updated to include all fields from afm_flds.

Workflow Rules:

• Imported latest WFRs from: /schema/ab-products/solutions/common/AbSolutionsViewExamples-workflowrules.xml.
• Imported AbCommonResources Workflow rules to get the new job rules.
• Imported AbSystemAdministration-runFileSearch.xml.
• Removed schedule_properties from the five WFRs that service the View Definition Wizard: deleteFile; getListOfFilesToConvert; getViewContents; moveAndRenameFile; writeViewContents.

Process Navigator:

• Added view for "Assign Security Groups to Roles" (ab-edit-groupsforroles.axvw).
• Added "Find Files with Duplicate File Names" under AbSystemAdministration / System Integrator.
• Removed "Users and Their Profiles" task and views from System Administration->Business Process Owner. It duplicated "Add or Edit Users" task, and used an outdated edit form, which did not use the correct edit form for password change. (3020726)
• Help links added and updated .

Data:

• Changed ARCHIBUS SYSTEM ADMINISTRATOR role to NOT have sql_pwd and sql_uid values. Moved those sql_pwd and sql_uid values to another user. (3020671)

Preventive Maintenance activity

Schema:

• pmp table:
  • Added “doc” field.
  • Added “eq_std” field.
  • Changed display value for pmp.pmp_type from "Housekeeping" to "Location" where data value is "HK". (3020450)
• pmps table:
  • Added "doc" field.
• pms table:
• Added "site_id" field.
• Default value of field “fixed” changed to “1”. (3020450)
• wo (and hwo) tables:
  • Changed display value for wo.wo_type (and hwo.wo_type) from "Housekeeping Prev. Maint." to "Location Prev. Maint." where data value is "HSPM". (3020450)

Workflow Rules:

• Added for Web C Prev. Maint. business logic (afm_wf_rules).

Process Navigator:

• Additions for Web C Prev. Maint. processes and tasks (afm_processes, afm_ptasks ).

US Federal Property Registry activity

Schema:

• Added tables:
  • grp Government Real Property
  • grp_agency_and_bureau Government Agencies and Bureaus
  • grp_trans Data Transactions - Government Real Property
  • grp_type Government Real Property Types
  • grp_use Government Real Property Uses
• Added "Property Unique ID" field from gpr table to the bl, property and eq tables. grp_uid (copy grp.unique_identifier). This field is NOT validated since the typical user process is to first add the grp_uid values to the bl, property, or eq tables and then later on perhaps add them to the grp table.

Workflow Rules:

• Added workflow rules for this activity.

Process Navigator:

• Additions for US Federal Property Registry; processes and tasks (afm_processes, afm_ptasks ).

Data:

• Imported standard gov. agencies into the grp_agency table.
• Added a new security group: Government RPLM Inventory Process Owner.
  • Added a new role: Government RPLM Inventory Process Owner.
  • Assigned the new group to the new role.
  • Created a new user "Government RPLM Inventory Process Owner" and assigned it to the role: Government RPLM Inventory Process Owner.

Reservations activity

Schema:

• Added Timezones table. Added FKey from city table to Timezones. Added standard timezones data to Timezones table. (3020246)
• Updated Foreign Keys for reserve_rm to re-establish the existing defined FKeys.

Workflow Rules:

• Added to afm_wf_rules table (3018035).

Data:

• Messages added to the messages table for new functionality so that reservations cancellation times will consider different time zone needs. (3018035, 3021176)

Portfolio Administration and Lease Administration activities

Schema:

• Updated su.facility_type_id to validate against the facility_type table. (3020405)
• Changed enum list for op.who_can_exercise to be all caps: LANDLORD;LANDLORD;TENANT;TENANT;BOTH;BOTH (3021144) Also, the default value for op.who_can_exercise changed from "Both" to "BOTH".

Process Navigator:

• Added view: “Lease Expirations by Year - Chart” (ab-rplm-lsadmin-leases-expiring-per-year-chart.axvw).

Service Desk and On Demand Work activities:

Process Navigator:

• Changed the task names in afm_ptasks of the following tasks within activity_id = "AbBldgOpsOnDemandWork" and process_id = "Inventory Manager". (3020788)
  • changed "View and Edit Equipment Standards" to "Define Equipment Standards"
  • changed "View and Edit Equipment Inventory" to "Define Equipment"
  • changed "View and Edit Parts Inventory" to "Define Parts Inventory"
  • changed "Edit Inventory of Tools" to "Define Tools"

Data

• Corrected standard SLA response: UPDATE helpdesk_sla_response SET serv_window_days = '0,1,1,1,1,1,0' WHERE serv_window_days = '0,1,1,1,1,0'; (3020457)

Version 17.2 Database Version 127

Schema Preferences: The Database Version schema preference (Afm_Scmprefs) was set to 127. Database Version date was set to 2008.10.23.

Schema Changes:

• Ran the latest reservationSqlViews.abs to update the Reservations activity SQL views (3017224)
• afm_wf_steps.display_order -- changed size in afm_flds from 10 to 5. (3017390)
• Added date_pwd_changed and num_retries to the afm_users table. (3017913)
• Change wr_step_waiting.step_type from size of 32 to size of 16 to match the other step_type field sizes.
• Add table definition for workflow_substitutes (3016298):
  • Added new table: workflow_substitutes
  • Modified existing table to add field: helpdesk_sla_request.default_priority
  • Added review;Review;escalation;Escalation;forward;Forward to enumeration list for field step_type in tables: afm_wf_steps,helpdesk_sla_steps, helpdesk_step_log, activity_log_step_waiting, wr_step_waiting, helpdesk_roles
  • Changed surey to survey in enumeration list for field step_type in tables: afm_wf_steps,helpdesk_sla_steps, helpdesk_step_log, activity_log_step_waiting, wr_step_waiting, helpdesk_roles
• RPLM changes for new Property Administration and Lease Administration activities:
  • Added tables:
    docs_assigned {Assigned Documents}
    ot {Ownership Transactions}
    facility_type {Facility Types}
    ls_clause_type ls_amendment
    ls_alert_definition
  • Table changes:
    table:op --- fields added
    table: ls_resp --- fields added
    table: su add fields: name description facility_type_id {validates on the facility_type table} table: property add fields: property_type {enum: "Land;Land;Structure;Structure" } area_manual address1 address2 prop_photo
    table: ls add fields: automatic_renewal {enum: Yes/No} use_as_template {enum: Yes/No} template_name description {varchar 512} pr_id {validates on property table}
    table: bl add fields: bldg_photo {document field} value_book {same as in property table} value_market {same as in property table} date_book_val {same as in property table} date_market_val {same as in property table}
• Change afm_ptasks.task_file to have a size of 128 (was 64) to accommodate longer file names and the fact that adding items to "My Favorites" appends the user name to the file.
• Change afm_processes.dashboard_view to have a size of 128 (was 64)
  Set the following fields to have an ARCHIBUS Type of Calculated:
  activity_log.step_status
  helpdesk_sla_steps.step_status
  helpdesk_step_log.step_status_result
  wr.step_status
• op table:
  Add field: op.date_exercising_applicable
  Remove field: date_end
  Change Multi-line headings: date_option to "Date Option Window Ends" date_start to "Date Option Window Starts"
• pms.schedule_type -- change display value for "meter" and "manual" to "Meter" and "Manual"
• Add fields for IWMS Suite:
  gp table: area_manual, date_end, date_start, description, pct_floor, count_em
  fl table: area_manual
• Cost of resources in Reservations should go out to two decimal places (3018032) Make the following fields have two decimal places (they have none now):
  rm_arrange.cost_per_unit
  rm_arrange.cost_per_unit_ext
  resources.cost_per_unit
  resources.cost_per_unit_ext
• Change afm_users.home_page to be an enumerated field in the database, to have a default value, and not allow NULL.
  Change the default value for afm_users.home_page to be: ab-dashboard.axvw
  UPDATE afm_users SET home_page='navigator-details.axvw' WHERE home_page = 'solutionexplorer-details.axvw'
  UPDATE afm_users SET home_page='ab-dashboard.axvw' WHERE home_page = 'dashboard.axvw' ; (3019063, 3018304)
• Change the enum for afm_users.home_page to make dashboard.axvw be ab-dashboard.axvw
• UPDATE afm_flds SET enum_list = 'ab-dashboard.axvw;Dashboard;navigator-details.axvw;Process Navigator;accessible-details.axvw;Accessible Navigator' WHERE table_name = 'afm_users' AND field_name = 'home_page' (3019691, 3018816, 3019781)
• Add site_id to table pms for PM
• Add eq_std to table pmp for PM
• Change the Lease Clauses table's primary key's multiline heading from Responsibility Code to Clause Code
• Update the enumeration list for action_approval_expired fields in the resources and rm_arrange tables
• Changes for problem 3019438
  • Remove field "em_id" from the afm_flds for "table": wrhwr
  • Re-create the SQL view "wrview" by running dbupww.abs.
    
• SD/OD: change data type to “smallint” for the following two fields:
  helpdesk_sla_response.time_to_complete
  helpdesk_sla_response.time_to_respond
• Expand mo.option2 to 35 characters to match em_id (3019423)
• Change fixed_resource_id size from 32 to 64 characters
• Change the primary key of the rm_resource_std table to be combination of fields:
  bl_id , fl_id , rm_id , config_id , rm_arrange_type_id , fixed_resource_id
  Since, in this case, the new Pkey includes the existing PKey as the last part and has all of the other fields that would be in the Pkey are already set to NOT allow Null the change for this PKey should not present any problems for users existing data.(3015539)
• Changes to support changing step name from "Review" to "Edit and Approve". (3018622)
  • Change afm_wf_step.step to "Edit and Approve" where afm_wf_step.step_type = "review".
  • Change the display value in the enum list for the "review" data value to "Edit and Approve" for afm_wf_steps.step_type.

Workflow Rule (WFR) Changes:

• Added, changed, and deleted workflow rules records for:
  AbRPLMPortfolioAdministration
  AbRPLMLeaseAdministration
  AbWorkplaceReservations
  AbSolutionsViewExamples
  AbSolutionsMyAdn
  AbSolutionsWorkflow
  AbSystemAdministration
  AbCommonResources
• Delete all afm_wf_rules entries for AbSolutionsExtras.
• Change AbWorkplaceReservations-closeReservations from Type Message rule to a Scheduled rule.
• AbCommonResources activity in afm_activities is NOT active by default - set it to active (3019314)
• Service Desk and On Demand Work WFR Changes
  • Changed input of workflow rule: AbBldgOpsHelpDesk-archiveRequest
  • Added new workflow rules:
    AbBldgOpsHelpDesk-checkRequestDuplicates
    AbBldgOpsHelpDesk-reviewRequest
    AbBldgOpsHelpDesk-forwardApproval
    AbBldgOpsHelpDesk-forwardRequest
    AbBldgOpsOnDemandWork-cancelWorkRequests
    AbBldgOpsOnDemandWork-cancelWorkRequest
    AbBldgOpsOnDemandWork-closeWorkRequests
    AbBldgOpsOnDemandWork-closeWorkRequest
    AbBldgOpsOnDemandWork - issueWorkRequests

Process Navigator Changes:

• Add Process Navigator entries for:
  • AbRPLMPortfolioAdministration
  • AbRPLMLeaseAdministration
• Update help links for Web Central.
• Add default dashboards for Reservations and Service Desk/On Demand Workd(3018856)
• Solutions Templates Process Navigator entries added and changed
• Drawing Views Process Navigator entries added and changed
• UPDATE afm_users SET home_page = 'navigator-details.axvw' WHERE user_name like 'RESERVATION%' (3018822,3018856)
• Add new tasks to the "System Administrator" process of the AbSystemAdministration activity:
  • Run Dashboard Definition Wizard
  • Run View Converter
• Service Desk and On Demand Work changes:
  • Added processes Supervisor_WR and Craftsperson_WR for activity AbBldgOpsOnDemandWork.
  • Changed task files for ptasks for processes Client, Supervisor and Craftsperson in activity AbBuildingOperations.
  • Changed task file for AbBldgOpsOnDemandWork – Client – View Maintenance Service Request.
  • Added ptask "Determine Ordering Sequence of Service Level Agreements" for process "Service Desk Manager" in activities AbBldgOpsHelpDesk and AbBldgOpsOnDemandWork.
  • Added ptask "Define Vendors for process Business Process Owner" in activity AbBldgOpsHelpDesk.
  • Removed task "Review/Archive Closed Work Orders" from the PNav in the AbBldgOpsOnDemandWork activity. (3019854)

Data Changes:

• Drawing Publishing Rules (afm_dwgpbub) added and updated. (3018192)
• Activity Parameters table - records added for AbRPLMPortfolioAdministration.
• Changed records for Service Desk and On Demand Work
• Messages table: Added records for:
  • AbRPLMPortfolioAdministration
  • AbWorkplaceReservations
  • AbSystemAdministration (password messages) (3018223)
• Service Desk and On Demand Work Data Changes: added Escalation, Review, and Forward steps in afm_wf_steps
• Update bldgops_helpdesk_sla_response data (3017470)
• Add the following new activities (3018095):
  • AbRPLMEsriExtensions Geospatial Extensions for ESRI
  • AbRPLMEsriArcWeb Geospatial Extension for ESRI ArcWeb Services
• Add the "ED" security group as assigned to the "RESERVATION MANAGER" role.
• Field attribute value has a white space in afm_scmpref.preferences. Remove the extra white space  : <field table="afm_wf_steps" field="step " /> (3017406)
• Sample data additions for RPLM:
  site
  property
  ls
  su
  contact
  cost_tran_recur
  ls_amendment (ls_clause_type)
  ls_resp ( docs_assigned ls_alert_definition)
• Sample data changes for RPLM:
  property
  bl
  ls
  su
  ot
• Employee email: Email addresses for employees do not match their updated names. Imported data from kb3018342.xls (3018342)

Version 17.1: Database Version 126

Schema Preferences: The Database Version schema preference (Afm_Scmprefs) was set to 126. Database Version date was set to 2008.02.25

Schema Changes:

• Archiving fails in Svc Desk/OD Work because hactivity_log table is set to AUTOINCREMENT (3016730). To correct:
  • UPDATE afm_flds SET dflt_val=NULL WHERE table_name='hactivity_log' AND field_name='activity_log_id'
  • alter table hactivity_log: delete primary key
  • alter table hactivity_log: modify activity_log_id integer not null default null
  • alter table hactivity_log: add primary key(activity_log_id)
• The Process Dashboard requires new columns in the afm_processes table: (3016787). To correct:
  • dashboard_layout char(64) : name of the layout AXVW file; used to generate default dashboard view from afm_ptasks records;
  • dashboard_view char(64) : optional, name of the custom AXVW dashboard view; if specified, dashboard_layout is not used.
  • Also, the afm_process.process_type enumeration needs to be updated, to allow the individual processes to be displayed either in Process Navigator or in Process Dashboards:
    Current enumeration: OVERLAY;Overlay;WEB;Web;WINDOWS;Windows;
    New enumeration: OVERLAY;Overlay;WEB;Web PNav;WEB-DASH;Web Dashboard;WINDOWS;Windows;
• Multi-language fields added to resource_std table and to afm_scmpref.preferences (3017007).
• Work-around for UPW issue with copying data on tables with circular references. (The UPW fails to check the "validated_data" when it prepares to copy table data; it only checks the "validating table" field instead. So it decides that there is a circular reference even before the actual copy.)
  • UPDATE afm_flds SET ref_table = NULL WHERE table_name = 'activity_log' AND field_name = 'wr_id''
• There are two enums (afm_conversions.is_currency and survey.report) that use similar enum lists but in different case. (3017165) This creates problems for the localization kit which does not distinguish enum lists by field but only by the enum list itself. Altering survey.report has the least affect on users thus the fix is to update the database per the following:
  • UPDATE afm_flds SET enum_list = 'YES;YES;NO;NO', dflt_val = 'YES' WHERE table_name = 'survey' AND field_name = 'report'
  • ALTER TABLE survey MODIFY report CHAR(4) NOT NULL DEFAULT 'YES'
  • UPDATE survey SET report = 'YES' WHERE report = 'yes'
  • UPDATE survey SET report = 'NO' WHERE report = 'no'

Workflow Rule (WFR) Changes:

• Added WFR for Move Mgmt - Edit Multiple. (3016919)

Data Changes:

• Missing data in afm_wf_steps: INSERT INTO afm_wf_steps (activity_id, status, step, display_order, step_type,) VALUES ( 'AbBldgOpsOnDemandWork', 'Basic', 'Can', 600, 'basic') (3016740)
• Add default security groups to the abCommonResources workflow rules (3015663).
• 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 WHERE afm_flds.table_name = afm_flds_lang.table_name AND afm_flds.field_name = afm_flds_lang.field_name ))
• Some translatable fields are missing from afm_scmpref.preferences (3017089, 3017240):
  • Add: afm_wf_steps.step. (Also add the localization fields for afm_wf_steps.step to afm_flds.)
  • afm_wf_steps.step_type and afm_wf_steps.status are Enumerated fields. Remove them from afm_scmpref.preferences.
  • helpdesk_roles.step_type is an Enumerated field. Remove it from afm_scmpref.preferences.
  • helpdesk_sla_response.priority_label and messages.message_text. These do have localization fields so it is appropriate to have these in the afm_scmpref.prefereces field.
• UPDATE afm_flds SET dep_cols = NULL WHERE ref_table IS NULL. There are a handful of fields that have values in Fields to Validate even though the Validating Table is Null. There are no functional problems, that we know of, due to this; but it is not clean. The above statement cleans this up. The fields with this condition are listed below and can be found by a restriction on afm_flds WHERE dep_cols NOT NULL AND ref_table IS NULL.
  
  

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

Version 17.1 Database Version 125

• Schema Preferences: The Database Version schema preference (Afm_Scmprefs) was set to 125. Database Version date set to 12/03/2007
• Schema Changes:
  • Added new tables for new Service Desk and On Demand Work activities:
    activity_log_hactivity_log
    activity_log_step_waiting
    activity_logview afm_holiday_dates
    afm_wf_steps hactivity_log
    hactivity_logmonth
    helpdesk_roles
    helpdesk_sla_request
    helpdesk_sla_response
    helpdesk_sla_steps
    helpdesk_step_log
    hwr_month work_team
    wr_step_waiting
    
    
  • Modified existing tables to add Service Desk and On Demand Work fields or to modify existing fields:
    activity_log activitytype
    afm_activities
    cf
    hwo
    hwr
    hwrcf
    hwrtr
    hwrtt
    servcont
    wo
    wr
    wrcf
    wrtr
    wrtt
    
  • Added "APPROVED" to activity_log.status enumeration list (3015324)
  • Added actscns table. (3011777)
  • Added Latitude and Longitude fields for property and bl tables to support ESRI integrations (3014386)
  • mo table: Changed mo.to_fax and mo.from_fax to 20 chars. (3015676)
  • mo table: from_comp_type and to_comp_type fields -- enum lists - add “N/A;N/A;” and make “N/A” the default value
  • mo.hours_actual --- set to NOT calculated
  • hreserve.res_type; resview.res_type -- removed "continuous;Continuous" from the enum list
  • activity_log table: doc field added (3016592)
  • The following 2 New enums are added to the is_active field of the afm_dwgpubrules table: (3016027)
    SWF;SWF
    JSON;JSON
    
    
  • fl table: Added Nominal Elevation and Nominal Height fields for the ArcObjects script for exporting 3d rooms to GIS. (3016274)
  • cf.email -- changed ARCHIBUS Type from "Calculated" to "None"
  • Ran the new reservationSqlViews.abs to add the new SQL view for Reservations report (3015618)
  • Ran System / Schema / Define / Update Service Desk and On Demand Work SQL Views
  • Some fields in tables that are SQL Views have an ARCHIBUS type of None; changed to be Calculated :
    hwr_month
    wr_step_waiting tables
    activity_log_hactivity_log
    activity_logview
    resrmview
    resview
    wohwo
    wrhwr
    wrview
    
    
• Data Changes
• Added some afm_activity_params values for Reservations Activity (3015323)
• Added some messages table values for Reservations Activity (3015369, 3015372)
• Added activity_params values for ESRI ArcWebServices integration
• Added new drawing rules for exporting architectural info to SWF and asset info to JSON format (3016027)
• Added standard and sample data to the new SLA tables added for Service Desk and On Demand Work
• Added Service Desk and On Demand Work Process Navigation and activity data to the following tables: userprocs
  groups
  roles
  groupsforroles
  activity_params
  afm_wf_steps
  messages
  activitytype
  questionnaire
  questions
  helpdesk_roles
  afm_holiday_dates
  activitytype
• Sample Data changes:
  Change some resources.room_service values to "Yes"
  activity_log Some Action Titles updated so that the data does not all appear the same. (3016516)
  Added latitude and longitude values to the property and bl tables
• WRF Changes
• Changes required by the updated Capital Budgeting views
• Changes required by the updated Project Management views
• Added one new WFR for the Create FCI Scenarios view
• Changes required by the updated Move Management views and for (3016541, 3015787, and 3015757)
• Added for View Definition Wizard - Added Service Desk and On Demand Work WFRs
• PNav changes
• Added Service Desk and On Demand Work Process Navigator Entries (activities, actprods, processes, ptasks)
• Changed title of AbBldgOps, in afm_products table, to be "Operations Management"
• Replaced the existing Web Central Building Operations Management views with the equivalent new On Demand forms, as listed in the following table. (3016496)
  

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

• Added the View Definition Wizard to the Process Navigator under System Administration/ System Administrator: ab-viewdef-wizard.axvw (3016505)
• Capital Budgeting: Added view: "Analyze Facility Condition Index Scenarios" (3011777)
• Changed the afm_activities.subfolder value for the activity “AbSystemAdministration” from “\ab-system\system-administration” to “\ab-products\system-administration” (3015821)
• Added Tree Control test views to Solution Templates under AbSolutionsViewsSystemIntegrator: Added a new process "Tree Views" and under that process added:
  

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

• Add a new process: "GIS Views" under Solution Templates \ System Integrator Views. In that process added five tasks:
  Geocode Properties
  Geocode Buildings
  Manage Property Portfolio
  Manage Leases
  Manage Operations
• Assigned the "GIS Views" process to the AFM and ABERNATHY users
• Solution Templates / Business Process Owner Views / Edit Views: Added Edit Form Work Requests (All Fields) - RI (3015391)
• All the “fm_web_central_online_help.htm” help links changed to “afm.htm” (3015325)
• Added two items to the ARCHIBUS Client/Server System module: "Update Service Desk and On Demand Work SQL Views"and "Run SQL Script"

Version 16.3 Database Version 124

• Schema Preferences

• The Database Version schema preference (Afm_Scmprefs) was set to 124.
• Schema Changes
• Implemented "Reservations" activity tables and fields:
• Tables and fields added have the comment: "Reservations-v16.3"
• New Tables added were:
• hreserve Historical Reservations
• hreserve_rm Historical Room Reservations (Enhanced)
• hreserve_rs Historical Resource Reservations
• reserve Reservations
• reserve_rm Room Reservations (Enhanced)
• reserve_rs Resource Reservations
• resource_std Resource Standards
• resources Resources
• rm_arrange Room Arrangements
• rm_arrange_type Room Arrangement Types
• rm_config Room Configurations
• rm_resource_std Room (Fixed) Resource Standards
• In the existing tables,  fields were added to these tables:
• rm
• vn
• tr
• wr
• hwr
• Ran reservationSqlViews.abs to add Reservations SQL views
• Added  vn_id to wr and hwr tables. ML heading of "Primary Vendor"
• Ran DBUPWW.abs to add vn_id to wrhwr SQL view and to the wrhwr table in afm_flds
• Added messages table.
• Added afm_activity_params "Activity Parameters" table.
• Adedd an "email" field to cf table. Same as em.email and afm_users.email.
• UPDATE afm_users SET locale = 'DEFAULT' WHERE locale IS NULL
• Set afm_users.locale to NOT allow NULLs
• Added Schema Preferences fields needed for clustering (3014615):
• afm_scmpref.cluster_internal_use
• afm_scmpref.cluster_num_servers
• Changed mo.em_id to ALLOW NULL = yes. (3013578)
• Ran the "Add Work Wizard SQL Views" task to re-create the wr SQL views due to new fields.
• Added Cellular Number (cellular) to Contacts table (contact) (3014346)

• Data Changes

• Sample data added for Reservations activity
• Added RES. SETUP and RES. CLEANUP to probtype table (field is 16 chars so "RESERVATION" had to be abbreviated.)
• Added data to afm_activity_params
• Added reservations messages to the messages table
• Reservations: security
• created the following security groups:
  
  RESERVATION APPROVER
  RESERVATION ASSISTANT
  RESERVATION HOST
  RESERVATION MANAGER
  RESERVATION SERVICE DESK
  RESERVATION TRADES
• assigned each security group to corresponding role
• created one user for each of the roles
• Assigned all Reservations processes to the Reservation users and to user AFM
• Added SPAC% security group to the RESERVATIONS roles
• Made the following rm.reservable = yes, and added rm_config and rm_arrange data for those that did not have it:
  
  HQ 17 109
  HQ 17 127
  HQ 18 109
  HQ 19 107
  HQ 19 110
• Linked Reservations users to employee "AFM": UPDATE afm_users SET email='afm@tgd.com' WHERE role_name LIKE 'RES%'
• Made inactive some afm_dwgpub records. (3013398)
• Updated dwgname and ehandle data for the Workflow Rule Diagrams (3015143)
• Fixed problem with running the RM_Asset_Qtext publishing rule in the sample data causing error (3013438)
• Changed bldg "DC" to "XC"
•  Imported data for XC bldg (data added to tables: fl, afm_dwgs, vert, serv, gros, rm, em, eqstd, eq, fn, jk, su)
•  Made several changes to HQ sample data for use with the v16.3 exercises. (3014638 through 3014640, 3014642 through 3014647, 3014649, 3014650)
• Set the following publishing rules to be active: (3015162)
  
  AbHltBl
  AbLyrMisc
  AbURLBl

• Workflow Rule Changes

• Added and updated afm_wf_rules records for the AbSolutions* activities
• Added and updated afm_wf_rule record for the AbCommonResources activity
• Added new afm_wf_rules records for the Reservation activity
• Updated some of the WFR rules for abMoveManagement*, Proj. Mgmt, Capital Budgeting, and Workplace Portal
• Set to NULL the Security Group value for ALL of the WFRs (remove SYSTEM MGR from all rules)
• Process Navigator Changes
•  Added records to the following tables for Reservations:
  afm_processes
  afm_roles
  afm_roleprocs
  afm_ptasks
• Added "Update Reservations SQL Views" navigator task to System/ Schema/ Define to run: reservationSqlViews.abs
• For AbSolutions, changed PNav task name for Furn. Plan (3013446)
• Change subfolders on several solution template activities. (3015186)
• Added PNav labels for (All Room) and (Composite) activities (3013473)
• Changed afm_ptasks Enter Faceplates task to use file xfp.avw instead of fp.avw (3014275)
• DWG Editor Draw Telecom Grid command was removed from pnavs (3013520)
• Updated the help links in database (3013584)
• Added  to PNav - Proj Mgmt/ Proj Mgmt / Request  --- under Add or Edit Programs: "Add or Edit Programs by Type" --- ab-programs-edit-by-type.axvw. (3011669)
•  Added PNav entry for axvw to add/edit/delete Project Phases (projphase table). (3012450)
• Added two new views to PNav: (3011830)
• Compare Project Costs to Budget Costs: ab-proj-projects-compare-to-budget.axvw
• Compare Project Costs to Budget Costs by Year: ab-proj-projects-compare-to-budget-2d.axvw
• Changed the file names for the following two PNav entries (3012046):
• AbCapitalBudgeting / Prioritize & Estimate (Optional) / Add or Edit Actions: ab-project-capbud-actions-edit.axvw
• AbCapitalBudgeting / Prioritize & Estimate (Optional) / Estimate Baseline Schedule and Durations: ab-project-capbud-actions-est-base-sched-edit.axvw
•  Added afm_activity_params.avw to System/Schema/Control Navigator (3014372)
• Added messages_trans.avw to System/Schema/Translate Navigator
• Set Dashboard frame heights to zero. (3014565)
• Changed ab-wf-rules-container.axvw to ab-wf-rules-by-activity.axvw for the record: AbSystemAdministration > Workflow Process Manager > Workflow Diagrams
•  Reassigned the AbWorkplacePortal and AbWorkplaceReservations activities to the AbWorkplaceServices domain.
• Made the AbWorkplaceServices domain active=Yes.
• Deleted  the (now empty) AbWorkplace Portal domain.
• In the ARCHIBUS Domains table, changed Domains Large Icon  to be:
• AbAsset         fc_facil.gif
• AbRisk          ab-act-emergeprep.gif
• AbTelecom       ab-role-security.gif
•  AbSMP           ab-act-smp.gif
• For Processes, changed "Process or Role" and Titles for them from “Facility Information Manager” to "Business Process Owner.”
• For AbMoveManagement / Business Process Owner, changed Large Icon be “ab-role-fim.gif”.
• Added record with link to setup-license-file.axvw (3014779)
• Added to DNav: System / Schema / Control / Schema Preferences / Messages (messages.avw)  3015173

Version 16.1 Database Version 123

• Schema Preferences
• The Database Version schema preference (Afm_Scmprefs) was set to 123.
• Process Navigator
• Schema Changes:
• afm_ptasks.view_type_override - Added. (3012446)
• afm_ptasks.security_group - Updated Multi-Line Heading. (3009173)
• afm_ptasks.comments - Updated string_format for to memo.
• afm_processes.summary - Updated to reduce size from 512 to 320 to better facilitate localization. (3012372)
• Data Changes:
• afm_products, afm_activities, afm_processes, and afm_ptasks tables - Extensive additions and updates of Process Navigator records.
• Domain Navigator
• Schema Changes:
• help_file field in all domain navigator tables - Updated multi-line heading from "Help File" to "Help Sub-Folder". Note: v16.1 does not provide any functionality for this field. (3013104)
• afm_modules - Table title changed from "Modules" to "Domains"
• Data Changes:
• afm_modules - Display order changes; Title changes; assigned Activities changed. (3012121 + other changes)

• Drawing Publishing
• Schema Changes:
• afm_dwgpub.comments - New field added.
• afm_dwg.pub_rule_type - Added new enumeration value (Dynamic). (3012359)
• Data Changes:
• Drawing Publishing rules added. (3011950, 3011958)
• Dynamic drawing publishing rules added.
• Added task to Domain Navigator: System/Schema/Update/Convert GDI Patterns to AutoCAD (3012496)
• Work Flow Rules

• Data changes:
• afm_wf_rules - Rules updated to add dwg_name and ehandle values. (3012735)
• HQ Sample Data
• Data Changes:
• dp, rmcat, rmtype, gpstd, hprorate, regn - Added sample AutoCad Hatch patterns to existing records.
•  VPA restriction for Role "Z-VPA-TBL" -- Uupdated to change "optionX" fields to "vpa_optionX" in the restriction sql. (3008506)

Version 15.1 Database Version 122

• Schema Preferences

• Database Version Number updated to 122
• Schema Changes

• Sybase database rebuilt as UTF8 (3012170)
• afm_layr.title_% - changed size from 40 to 64 to accommodate translations
• afm_users.clr_scheme enumerations list - added: ;SLATE-LARGE;Slate - Large Font (3012286, 3012289)
• mo.mo_type - changed default from NULL to 'Employee' (3011776)
• activity_log.date_planned_for - changed default from NULL to CURRENT (3011789)
• work_pkgs.doc - added this document field (3011779)
• System Data Changes
• afm_users.clr_scheme - set to SLATE for those that did not have values
• afm_scmpref.preferences - added fields from the questions table to the list of translatable fields

• Process Navigator
• afm_products.help_link - replaced @dbExtension with @helpExtension (3012001, 3011941)
• afm_ptasks:
• Added web view for Add and Enter Business Units in Move Mgmt (3011596)
• Added Primary Trades web view in Bldg Ops and Move Mgmt. (3011584)
• Updated Solution Template tasks to correct search paths (3012236)

• Domain Navigator

• Renamed "Product" to "Domain" in System/ Schema /Navigate Activities tasks (3012125)
• Work Flow Rules
• afm_wf_rules - add two workflow rules that implement batch report generation (3011813)
• afm_wf_rules - rules updated (3012082)
• HQ Sample Data
• added users to afm_users for load testing
• changed hpattern from old format to new format for tables: ls, dp, rmstd, rmtype (3012310, 3012331)

Version 15.1 Database Version 121

• Schema Preferences
• The Database Version schema preference (Afm_Scmprefs) was set to 121.
• Added date_lang_en field to afm_scmpref for tracking canonic changes
• ARCHIBUS Navigator Changes
• The Document Management, Questionnaires, and Workflow Rules activities added to the System module.
• In System module, renamed Step Up and Connectivity activity to Connectivity. Removed Step Up task category and its tasks from the activity.
• "Update to V15 Status Codes" task added to the System/Schema/Define task category.
• "Tables licensed by Activity" and "Licensed Tables and Their Activities" tasks added to the System/ Schema/ Navigate Activities task category.
• Process Navigator Changes
• The Move Management, Capital Budgeting, and Project Management activities added. The System Administration/Workflow Process Manager activity added.
• Products changed to Domains.
• Workplace Services product removed and its activities reorganized into new domains, such as the Building Operations activity moved to the Operations domain.
• Asset/Asset Control activity added. Telecom Asset Control process changed to Equipment Asset Control.
• The Emergency Preparedness activity added to Space domain.
• Fields
• Projects, Move Orders, Vendors, Activity Logs tables: Several new  fields were added to these  tables. For a complete list, load the ARCHIBUS Fields table  and set a restriction for: "Comments" "LIKE" "%Trin%". All fields added or changed for version 15.1 have  "Trinidad" is in the Comments field.
• For the amount_tot_invoice field in the Invoices table, change ARCHIBUS Type to None from Calculated for both the HQ and Schema databases (Sybase only). Also, the following new fields were added to the Invoices (invoice) table.

  Field

  invoice.project_id

  invoice.work_pkg_id

  invoice.vn_id

  invoice.qty_accepted

  invoice.qty_invoiced

  invoice.qty_invoiced

   

• The following new fields were added to the Communications Log (ls_comm) table.

  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

• The following new fields were added to the Budgets table.

• Field

  budget.site_id

  budget.program_type

  budget.year_start

  budget.year_end

• These new fields were added to various tables.

  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

• The following fields had their ARCHIBUS Type changed from Graphic to Document:

  Field

  activity_log.doc_file1 activity_log.doc_file2 activity_log.doc_file3

  ls.image_doc1 ls.image_doc2 ls.image_doc3

  ls_comm.image_doc1

  op.image_doc1 op.image_doc2

• afm_users.locale enumeration list changed to include "no_NO;Norwegian".
• afm_flds.enum_list and afm_flds_lang.enum_list_xx size changed from 600 to 750.
• project.project_type changed from enum to validate on projecttype table.
• Set the bl, fl, rm, dv, and dp fields of rm_trial table to not validate.
• activity_log table: approved_by, assessed_by, completed_by, created_by, verified_by size is expanded to match the em table.
• Added a Comments field to the following tables: afm_activities, afm_activity_cats, afm_actprods, afm_acts, afm_cats, afm_class, afm_hotlist. afm_mods. afm_processes, afm_products. afm_psubtasks. afm_ptasks, afm_subtasks, afm_tasks
• afm_flds.afm_type has new values in enum list: "Document" and "Doc.Stg"
• Table Changes
• These tables were added:

• 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

• C-LEVEL (dashboard user) added to ARCHIBUS Roles table."Smith" added to Users table and assigned to this role. Process-to-user assignments for Smith.
• In Employees table, added SMITH@tgd.com to the record for SMITH, SALLY so the dashboard user is someone with an ARCHIBUS identity. Also, added em photo for Smith. So that items appear on dashboard, assigned activity log items, projects, hotlist items.  Added  projects assigned to "smith, sall"y as project manager.(must be assigned to em).
• For User SYSTEM, added afm_userpocs records to match user AFM.
• afm_wf_rule, afm_wf_activity,  afm_wf_activitydef,  afm_wf_incoming_messages tables used by previous workflow component were removed.
• ARCHIBUS Layers table now has records for RM-TRIAL1-MO, RM-TRIAL2-MO and RM-TRIAL3-MO.
• TDB record added to Contacts table.
• Set defaults for baseline and design dates and durations in activity_log in both HQ and Schema databases.(Sybase only).
• The following records were added to the activity_type table (Schema database only; Sybase only):PROJECT - CHANGE ORDER, PROJECT - MILESTONE, PROJECT - DOCUMENT, PROJECT - TASK

Version 14.3 Database Version 120

• Preferences. The database version schema preference (afm_scmprefs) was set to 120.
• Navigator Changes. The Navigator items under System/Security were updated.
• Database Format. Sybase and Oracle databases ship with UTF-8 (Unicode Transformation Format-8) encoding.
• Field Changes
• The afm_users.user_pwd field has been increased from 10 to 64.
• The afm_users.locale field was made an enumerated field with the following values:
• Default
• ar_SA;Arabic
• zh_CN;Chinese (Simplified)
• zh_TW;Chinese (Traditional)
• nl_NL;Dutch
• en_AU;English (Australia)
• en_CA;English (Canada)
• en_GB;English (United Kingdom)
• en_US;English (United States)
• fr_CA;French (Canada)
• fr_FR;French (France)
• de_DE;German
• iw_IL;Hebrew
• it_IT;Italian
• ja_JP;Japanese
• ko_KR;Korean
• pt_BR;Portuguese
• es_ES;Spanish
• The data types of the following fields changed from CHAR to VARCHAR

• Table Changes. The following table changes were made:
• Added XML Schema Preferences data for translatable fields to the preferences field in the afm_scmpref table.
• To record the date that translated strings were last updated date_lang_** fields were added to the afm_scmpref table for each language field.
• title_* fields added to the afm_layr table.
• To provide for additional language translations each of the following table and field combinations added _01, _02, and _03 fields:

• To provided for translation, each of the following tables and fields added fields with the internationalization extensions: (_01, _02, _03, _ch, _de, _es, _fr, _it, _jp, _ko, _nl, _no, _zh):

Version 14.3 Database Version 119

• Preferences.  The database version schema preference (afm_scmprefs) was set to 119.
• Navigator Changes.  A Process Navigator for ARCHIBUS Web Central was added.
• Drawing Publishing Rules (afm_dwgpub).  rule_type enumerated field values were extended to include QUERY TABLE;Query Table;.
• New Tables. These tables were added:

• New Fields.  These new fields were added.

• Field Size Changes.  These fields were enlarged.

• Field Default Value Changes.  These fields had default value changes.

Version 14.2 Database Version 118

• Preferences.  The database version schema preference (afm_scmprefs) was set to 118.
• Navigator Changes.  The following changes / additions were made to the Domain Navigator:
• A new Additional Activities module was added.
• A Condition Assessment activity was added under the new Additional Activities module.
• A Environmental Sustainability activity was added under the new Additional Activities module.
• General Table and Field Changes.  The following table and field changes were made:
• A Classifications table and fields were added to support the new Condition Assessment activity.
• An Organizations table was added. This table is a standard (non-concatenated) Hierarchical table and provides a template for those users who wish to track their organization assignments using an unlimited hierarchy rather than the standard 3-tier hierarchy of Business Units, Divisions, and Departments.
• In the Accounts table (ac) a hierarchy_id field was added and the ac_id field was changed to ARCHIBUS_type Hierarchical-Concat.
• The schem_prefs table now includes a hierarchy_delim field.
• The equipment table (eq) now includes a csi_id field.
• The historical work orders (hwr) table now includes an activity_log_id field.
• The project tables project_type field now includes an assessment value in the enumeration list.
• The work request (wr) and historical work requests (hwr) tables now include the activity_log_id field.
• The Work Requests (wrview) and the All Work Requests (wrhwr) now include the activity_log_id field.
• The Work Requests (wrview) SQL view and the All Work Requests (wrhwr) SQL view now include the activity_log_id field.  
• The communications log (ls_comm) table has been changed as follows:
• The hierarchy_ids field was added.
• The auto_number afm_type was changed to hierarchical.

• Changes to Activity Logs table

These fields were added to the Activity Logs table (activity_log):

The following fields were changed in the Activity Log table (activity_log):

• The cost_actual field multi-line header (ml_heading) was changed to Cost Actual (Expense).
• The cost_estimated field multi-line header was changed to Cost-Estimated (Expense).
• The status fields enumeration list now contains the following values:

" N/A

" Trial

" Budgeted

" Planned

" Scheduled

" In Progress

" Completed

" Completed and Verified

• Changes to the afm_Flds table

• The afm_type fields now has the following new values:
• Hierarchical
• Hierarchical-Concat
• Hierarchical Trace
• The enum_list field has been increased to 600 to accommodate additional enumeration values.

Version 14.1 Database Version 117

• Schema Preferences.  The Database Version schema preference (Afm_Scmprefs) was set to 117.
• Schema Table Title Changes.  Several table titles have been changed as follows in order to resolve a Data Transfer to Excel issue.  Excel has a 31-character limit for sheet titles and several ARCHIBUS table titles previously exceeded this limit.

  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

• Drawing Publishing. The Afm_Dwgpub table and fields for the Drawing Publishing feature were added.  Additionally the Drawings Task Category (DrawingPublishing) and tasks were added as follows:
• Drawing Publishing Rules (afm_dwgpub.avw)
• Drawing Publishing Rules Summary (afm_dwgpub_tbl.avw)
• Asset Types and Publishing Rules (atypes.avw)
• Publish Drawings (afm_dwgpub.abs)
• Schema. Added Type.Afm_Flds.  An "Hpattern Acad Ext" (Extended) type added to the Afm_Atype table.
• Schema. Field Change.  The Hpattern_Acad Field has been changed in 14 tables as follows:

o Type set to Hpattern Acad Ext

o Field width increased to 64 characters

• Schema. Afm_Atyp Table.  A rule_id field has been added to the Afm_Atyp table.
• Schema. New Tables. The following tables were removed:
• 'afm_activities'   
• 'afm_activity_cats'
• 'afm_groupsforroles'
• 'afm_processes'
• 'afm_prodmeths'
• 'afm_products'
• 'afm_psubtasks'
• 'afm_ptasks'
• 'afm_roles'
• 'afm_userprocs'
• Schema.  Navigation Changes.  Database Version 117 contains the following Navigation changes.
• afm_hotlist.  A new record was added to load the HTML form example under: Space/C: Operations Management/1. Rooms/Reassign Rooms and Employees.     

Version 12.2 Database Version 116

• Schema Preferences.  The Database Version schema preference was set to 116.
• Emergency Preparation activity.  The tables and fields for the Space/Emergency Preparedness activity were added to the ARCHIBUS Tables and ARCHIBUS fields tables. They are marked with "Emergency Preparedness" in the Comments field.  Similarly, the activities and tasks for the Space/Emergency Preparedness activity were added to the Domain Navigator tables.
• Add Field task.  There is a new Navigator task, System/ Schema/ Define/Add Field. It is located beneath the "Copy Field(s)" task.
• Context-Sensitive Help from Navigator.  For all Help Topic fields in the Domain Navigator tables that use keywords, the dbupHelpLinks.abs routine updates the Help Topic fields to now use the topics' context strings. Also, in the ARCHIBUS Tasks table, the Help File fields for System/Schema/Update/ Audit Logs and System/Schema/Update/Currency and Conversion were updated to link to afm_sysman.hlp, rather than afm.hlp.
• ARCHIBUS Hotlist table.  Changed afm_hotlist.view_title from 64 to 96 characters so that long localized strings can fit.
• ARCHIBUS Conversions table.  afm_conversions table definition and physical table updated.
• ARCHIBUS Workflow Activity Instances table.  There is a new field in the afm_wf_activity_def table: "is_retired".
• Properties table.  property.detail_dwg was updated to be 64 characters wide from 8 characters.
• Size of Auto-Incremented Integers.  Standardized the auto-incremented integers sizes in afm_flds. All were changed to 10. This is purely cosmetic, as all integers field sizes are defined by the database engine.
• ARCHIBUS Asset Types table. Removed pop_loc from the Fields to Validate in afm_atyp, as it has no effect.
• Groups table.  gp.function changed to gp.gp_function for Microsoft SQL Server 2000 compatibility.

Version 12.1 Database Version 115

• Schema Preferences.  The  Database Version schema preference was set to 115.
• Work Flow Controller.  The afm_wf% tables were added. Also, fields for the Work Flow Controller were Added to the wr, hwr, wrcf, hwrcf, tr, and cf tables.
• Work Wizard.  Required database changes for the Work Wizard included the following:

• Added fields to wr and hwr , including wr.satisfaction and wr.satisfaction_notes.
• Removed WOAUTONUMBER and WRAUTONUMBER triggers, as the triggers are not supported by the Runtime Sybase engine. Authoring and Multi-user users can run the Navigator action to re-create these triggers.
• In the ARCHIBUS Groups table, the afm_groups.ww_preferences fields was added.
• The following SQL views were added: wohwo, wrhwr, wrcfhwrcf, and wrpthwrpt.
• wr.time_requested now has a default of CURRENT.
• System/Schema/Define/Add Work Wizard SQL Views added to Navigator.
• SQL view wrview updated so that now both wr and hwr records can be viewed in the tree control.

• System/Schema activity reorganized.  On the Domain Navigator, the System/Schema activity was reorganized. The Connect tasks were removed from System/Schema and System/Security. Also, the tasks in System/Schema/Control were re-organized. Help Topic fields for these items updated to match changes.

• ARCHIBUS Hotlist table.  To implement multi-language ARCHIBUS Web Central, changes for multi-language support were made to the ARCHIBUS Hotlist table.  

• The afm_hotlist.inet_file values were set to lower case.
• Updated .cfm Hotlist capitalization.
• VoloView and Toolbar views added to hotlist via HotLsChg.abs.
• "VPID" set to uppercase for Help links.
•  Dwg Filename fields. All detail_dwg, symbol, title_block, and "dwgnames" fields changed from 8 to 64 characters to support long filenames. Previously, other filename fields (including "dwgname") had been changed to 64, but these had been missed.
• Drawing Assets.  Centered Boundary Asset Text Generation style added. Also, most pline asset types changed to Centered Boundary.
• Real Property & Lease Management module. Extra title white space in the Real Property & Lease Management module was removed from the Navigator.
• Telecom. Indexes were re-created.  
• Online Help.  For System module Navigator entries, the Help File field was changed from afm11_sysman.hlp to afm_sysman.hlp.
• F&E Audit vs. Inventory tables.  Removed validation from the ta_compinvsur and eq_compinvsur tables.
• Employee Standards table. emstd.em_std set to UPPER.
• Triggers and SQL views.  System/Schema/Define Indexes, triggers, and  SQL views were re-created.

Version 11.3 Database Version 114

• Fields changed from 32 to 64 characters.  For localization purposes, all act_class and act  fields were changed from 32 characters to 64. Changed  afm_tbls.title from 32 char to 64. Update Schema, Alter table. Updated appropriate foreign keys.
• Navigator titles and multi-line headings. Some Navigator titles and multi-line field headings cleaned up based on localization feedback.

Version 11.2 Database Version 113

• BOMA 96. The space calculations offer a BOMA 96 option in addition to the BOMA 90 and Enhanced BOMA calculations.  
• afm_scmpref.space_area_type ("Space Area Method") changed:

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).

• afm_scmpref.lease_proration_method ("Group Area Prorate Method") changed:

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).

• Microsoft SQL Server Reserved Words.  The "use" and "image" fields were renamed to avoid conflicts with reserved  words in MS SQL Server.  These are now named "use1" and "image_file".
• Use ARCHIBUS Function for Cascade.  A new schema preference field, afm_scmpref.afm_cascades_changes, has been added to support the application level cascading updates and deletes for Microsoft SQL Server and Oracle.
• Real Fields Changed to Numeric.  All fields that were type "Real" in the standard schema have been changed to "Numeric" in order to avoid slight rounding differences between the values displayed on the screen and the values stored in the database.  If you use the Update Project Wizard to apply these changes to your current database, your stored values will be rounded to the number of decimal places specified for each field in the afm_flds table.     

Version 11.1 Database Version 112

• First V11 Schema.  Database version 112 is the benchmark schema from which all other V11 changes are noted.  If you have a previous version of the schema, please review the AddToV10.abs file for a list of the changes made to Version 10 schema.