Database Schema Revision History Version 20.1 (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 accomdate 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 Default value = 0 (3032161, 3030109) - 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 can 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). - Add a basic status step 'IN PROCESS - ON HOLD’ to table afm_wf_steps (3032830) - Add step for status 'COMPLETED-V' to afm_wf_steps - 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') OR (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 Bldgs: - 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 Bldgs: - 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 Mgmt: - Added table and field definitions MSDS: - 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 EH&S: - Added table and field definitions: ehs%;docs_assigned;work_categ% Compliance: - Added table and field definitions - Change the enumeration list for the fields is_requirement and sched_loc of regcompliance table in afm_flds to: "0;No;1;Yes". (3034487) - change the ml_heading in afm_flds for regulation.reg_category to "Regulation Super Category". (3034461) - regulation.regulation: Change AFM Type from None to Hierarchical. (3034333) - regulation.related_reg refers to the regulation table. Set "Validate Data?" to NO. (3034891) Energy Mgmt: - bill_unit table - Change default value for field Rollup Type to "None". (3031387) Asset Mgmt: - eq 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 la*st 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 ); v20.1 Database Revision History ____________ 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 Mgmt: - Add WFR for Asset Mgmt (needed for paginated report) (3031164) Capital Budgeting: - Add new WFR for AbCapitalBudgeting-CapitalBudgetingService. Waste and Clean: - Added WFRs. MSDS: - Added WFRs EH&S: - Added WFRs Compliance: - Add WFR for ComplianceCommon (3034421) v20.1 Database Revision History __________________________ 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 Space 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 Grouping 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. Proj. Mgmt.: - 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 Bldg: - Add Bus Process PNav entries for Green Building (3034818) Waste: - Added Process Navigator Clean: - Added Process Navigator MSDS: - Added Process Navigator EH&S: - Added Process Navigator Compliance: - Added Process Navigator Commissioning: - 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 v20.1 Database Revision History _____________ 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) UPDATE AFM.afm_activity_params SET param_id='Acceptance_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='ACCEPTANCE_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Approval_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='APPROVAL_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Auto_Archive' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='AUTO_ARCHIVE'; UPDATE AFM.afm_activity_params SET param_id='Dispatch_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='DISPATCH_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Escalation_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='ESCALATION_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Review_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='REVIEW_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Survey_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='SURVEY_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Update_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='UPDATE_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Verification_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='VERIFICATION_VIEW'; UPDATE AFM.afm_activity_params SET param_id='View_View' WHERE activity_id='AbBldgOpsHelpDesk' AND param_id='VIEW_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Approval_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='APPROVAL_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Assign_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='ASSIGN_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Auto_Archive' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='AUTO_ARCHIVE'; UPDATE AFM.afm_activity_params SET param_id='CF_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='CF_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Dispatch_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='DISPATCH_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Estimation_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='ESTIMATION_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Issue_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='ISSUE_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Manage_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='MANAGE_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Review_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='REVIEW_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Scheduling_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='SCHEDULING_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Update_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='UPDATE_VIEW'; UPDATE AFM.afm_activity_params SET param_id='Verification_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='VERIFICATION_VIEW'; UPDATE AFM.afm_activity_params SET param_id='View_View' WHERE activity_id='AbBldgOpsOnDemandWork' AND param_id='VIEW_VIEW'; UPDATE AFM.afm_activity_params SET param_id='IncludeGroupsInUnifiedSpaceCalcs' WHERE activity_id='AbCommonResources' AND param_id='includeGroupsInUnifiedSpaceCalcs'; UPDATE AFM.afm_activity_params SET param_id='Avg_Copy_Rate' WHERE activity_id='AbRiskGreenBuilding' AND param_id='avg_copy_rate'; UPDATE AFM.afm_activity_params SET param_id='ch4_Gwp' WHERE activity_id='AbRiskGreenBuilding' AND param_id='ch4_gwp'; UPDATE AFM.afm_activity_params SET param_id='Egrid_Ver_Default' WHERE activity_id='AbRiskGreenBuilding' AND param_id='egrid_ver_default'; UPDATE AFM.afm_activity_params SET param_id='Energy_Mgmt' WHERE activity_id='AbRiskGreenBuilding' AND param_id='energy_mgmt'; UPDATE AFM.afm_activity_params SET param_id='Energy_Mgmt_Cost_Cat' WHERE activity_id='AbRiskGreenBuilding' AND param_id='energy_mgmt_cost_cat'; UPDATE AFM.afm_activity_params SET param_id='ch4_gwp' WHERE activity_id='AbRiskGreenBuilding' AND param_id='ch4_Gwp'; UPDATE AFM.afm_activity_params SET param_id='Factors_Ver_Delete' WHERE activity_id='AbRiskGreenBuilding' AND param_id='factors_ver_delete'; UPDATE AFM.afm_activity_params SET param_id='LS_Alerts_View' WHERE activity_id='AbRPLMLeaseAdministration' AND param_id='LS_ALERTS_VIEW'; UPDATE AFM.afm_activity_params SET param_id='OP_Alerts_View' WHERE activity_id='AbRPLMLeaseAdministration' AND param_id='OP_ALERTS_VIEW'; - Add new Activity Parameter for Multi-Units: AbCommonResources-ConvertAreasLengthsToUserUnits Users 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 - PROCESS 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 ); INSERT INTO afm_roleprocs ( activity_id, process_id, role_name ) SELECT afm_processes.activity_id, afm_processes.process_id, '4 - PROCESS OWNER (ACP)' 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 (ACP)' AND afm_roleprocs.activity_id = afm_processes.activity_id AND afm_roleprocs.process_id = afm_processes.process_id ); INSERT INTO afm_roleprocs ( activity_id, process_id, role_name ) SELECT afm_processes.activity_id, afm_processes.process_id, 'ACTIVITY LICENSEE' 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 = 'ACTIVITY LICENSEE' AND afm_roleprocs.activity_id = afm_processes.activity_id AND afm_roleprocs.process_id = afm_processes.process_id ); DELETE FROM afm_roleprocs WHERE (SELECT is_active FROM afm_processes WHERE afm_roleprocs.activity_id = afm_processes.activity_id AND afm_roleprocs.process_id = afm_processes.process_id ) = 0 OR (SELECT is_active FROM afm_activities WHERE afm_roleprocs.activity_id = afm_activities.activity_id ) = 0; 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 Mgmt: - 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'; BldgOps: - UPDATE activity_log SET step_status = 'none' WHERE step_status = ''; (3034202) Risk & Env: - 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.621371192237 DISTANCE-MILES km 0.621371192237 Green Bldgs: - 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 Bldgs: - 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: a. 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 b. For each MSDS record shown in the middle panel: i. Click the record ii. Click the Document tab in the bottom panel iii. If there is a value for “MSDS Source URL” in the form, click the >>Download action button. iv. Click >>Save EH&S: - 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 - Planning -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