ConSol Datamart Specification Version 6.0 Published 13 February 2018 Yarris Pty Ltd Copyright and Disclaimer Notice This document and any attachments/appendices are confidential and may contain legally privileged information and/or copyright material of Yarris Pty Ltd or third parties. Recipients of this document shall not copy, reproduce, distribute or commercialise any part of the material without explicit written consent from Yarris Pty Ltd.
1.14 2nd November 2012 Extend field length for Project_Ref_Id (4.4.1) and Reference_Nr (4.6.1) fields to accommodate imported characters McIlwraith 1.15 9nd November 2012 Remove Project No from Other Customer Order in Order Detail extract McIlwraith 2.0 02nd January 2013 Extend to include Work type Master extract and Work Type in Project View and Order Details extracts. Review and tidy up of all extracts. Include Contract & Milestone extracts Trish McIlwraith 2.1 08th January 2013 Inclusion of Metadata and App Survey extracts Trish McIlwraith 2.2 14h January 2013 Revision after feedback from Marcus & Ian Trish McIlwraith 2.3 30h January 2013 Inclusion of Supplier Operations user into Order Details extract Trish McIlwraith 2.4 13th February 2013 Inclusion of Attribute Details extract Trish McIlwraith 2.5 21st February 2013 Inclusion of Refuse Master extract Trish McIlwraith 2.6 14th March 2013 Changes after review from the customer Trish McIlwraith 2.7 14th May 2013 Update – Include Docket Dates on Invoices, Compliance expiry date, Interface flag on Orders & PV, Document extract, Survey Action extract, column name changes McIlwraith 2.8 9th June 2013 Include additional column in App Survey Extract. Rename ‘Refused Order Master’ to ‘Removed Order Master’. Clarify file creation and naming conventions McIlwraith 2.9 2nd July 2013 Change SOR Master to SOR Detail extract, Modify Primary key definition in Compliance Document Master extract to ensure uniqueness Trish McIlwraith 2.10 7th October 2013 Minor changes to file naming convention, changes to mandatory nature of some fields, Extended code set for new PV status codes Trish McIlwraith 3.0 28th February 2014 Update Code lists. Separate Address Location in Order Details extract into different fields. Update Milestone status table, Milestone Details extract and introduce Milestone History extract McIlwraith 3.1 18th 2014 Inclusion of GST & Auditable on SOR extract. Inclusion of ‘Deleted Records’ extract Trish McIlwraith 3.2 25th March 2014 Inclusion of Effective From and Effective To in SOR extract; supplier company number in invoice details extract Trish McIlwraith 3.3 16th May 2014 Removed Id from Milestone History extract McIlwraith
INTRODUCTION Summary ConSol customers require the facility to extract key information from the application system so that they can report on it in own BI environment. This approach will provide the following advantages: Greater flexibility and agility in delivery of reports. Ability to report across whole of business using in-house data integration approaches. Protection from future technology changes in Yarris. The extracted data, referred to as the Datamart, will be the single reporting entity that will be utilised by customers. This document details the scope and structure of the Datamart. Scope The extracts included within the Datamart correspond, to some extent, with the main ConSol entities. This presents a rationalised, business-oriented view of the application data. Three types of extract files are created: Master Extracts – information/code sets: Removed Orders, Attributes, WIP Events, Compliance Docs, Work Type and Milestone Type Detail Extracts – detail relating to the core reporting entities: Projects, Orders, Items, Invoices, Contracts, Rates, Documents, Attributes and Milestones History Extracts – changes that have occurred within the processing window relating to core reporting entities Data Master Extracts: ∙ Attribute Master ∙ Attribute List Master ∙ WIP Master . Work Type Master . Milestone Type Master ∙ Compliance Documents Master ∙ Removed Orders Master Entity Detail Extracts: ∙ Summary ∙ Project Details Delta ∙ Details Delta ∙ Order Item Details Delta ∙ Attribute Details ∙ Invoice Details Delta ∙ Invoice Item Details Delta . Item Variation Delta ∙ Contract Details Delta . Milestone Details Delta . Metadata Details Delta . App Survey Details
presented for selection on orders with the specified ESA attribute values. This flag indicates that the attribute is available to restrict work order allocation on contracts. BUSINESS_OBJECT String 8 - Attributes can be attached to different ConSol entities. This indicates which entity the attribute applies to. The possible values are: 'PRV' - Project View attributes; 'SR' - Service Request attributes; 'ORD' - Order attributes; and 'ORDITEM' - Order Item attributes. O / M VIEW_BY String 8 - Identifies who is allowed to view the attribute within the application. The possible values are: 'SUPPLIER' - only the supplier can view the attribute; 'CUSTOMER' - Only the customer can view the attribute; 'BOTH' – both customer and supplier can view the attribute M / M EDIT_BY String 8 - Identifies who is able to edit the attribute within the application. The possible values are: 'SUPPLIER' - only the supplier can edit the attribute; 'CUSTOMER' - Only the customer can edit the attribute; 'BOTH' – both customer and supplier can edit the attribute M / M DATA_TYPE String 15 - ConSol currently supports five distinct data types for attributes. These are: 'text' – a string of text that accepts most character values; 'date' – a date in dd/mm/yyyy format; 'list' – a defined list of coded values and descriptions; 'currency' – numeric value in currency format ($9999.99); and 'number' – numeric value. O / M DISPLAY_LEVEL String 15 - Attributes in the application can be marked with two distinct display levels: 'header' - displayed at the top of the form, 'body' - displayed lower down in forms. This O / M
Compliance Document Master Extract This is a table of all Compliance requirements for each contract visible to the customer, showing when compliance documents have been submitted, when they expire and what the current status is of the compliance requirement. Data Dictionary Name Type Length Precision Format Description Mandatory DB / App CONTRACT_NO Integer 10 0 Foreign key. Unique identifier for each Contract M / M COMPLIANCE_NO String 20 - Primary key. Unique identifier for each Compliance Document (concatenation of Insurance_Required_no + Insurance No + Document No for Insurance documents and Compliance No for Compliance documents. M / M DOCUMENT_NO String 10 - Unique Identifier for each document uploaded O / O DOC_NAME String 200 - The Insurance Policy Type or the Contract Document Name M /M DOC_TYPE String 25 - The Insurance Compliance Type or the Contract Document Type M /M UPDATE_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date and time at which the compliance document was loaded O / M UPDATE_USER String 71 - The full name of the user who uploaded the Compliance document O / M STATUS String 30 - The Status of the Compliance Document M / M COMMENT String 2000 - If the Compliance document was rejected, the reason for the rejection O / O DATA_REFRESH_DATE Date 23 - yyyy/mm/dd hh:mm:ss Data propagates from the application to the Datamart at defined intervals. This field indicates the date at which this record was read from the application. M / M
Data Dictionary Field Name Type Length Precision Format Description Mandatory DB / App DEL_AUDIT_NO Integer 10 0 Primary key. Unique identifier for each Deleted record M / M TARGET String 32 - The file that the record was deleted from. This will be one of the following: Milestone, Item, Invoice M / M TARGET_KEY String 32 The name of the Foreign key. This will either be Milestone_no, Order_Item_no or Invoice No) M / M TARGET_VALUE String 128 - Foreign key. The ConSol value for the Target Key M / M CONTEXT String 32 - The entity that the deleted item related to – Either Project View or Order O / O CONTEXT_ID String 32 - Foreign key. Either the Order Id or the Project View No that the deleted item related to O / O DELETED_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date and time at which the deletion occurred M / M DELETED_BY String 71 - The full name of the user who undertook the action that triggered the deletion M / M DESCRIPTION String 1000 - Text Description of the action that occurred M / M DATA_REFRESH_DATE Date 23 - yyyy/mm/dd hh:mm:ss Data propagates from the application to the Datamart at defined intervals. This field indicates the date at which this record was read from the application. M / M (Mandatory column indicates whether the field is mandatory or optional at the database and application levels) Design Rules: Extract includes list of deleted Invoices, Milestones & Items, where deletion occurred within the processing window. All deleted records can be associated to an Order or PV and if that Order or PV has either been issued to or Issued by the customer, the deleted item would have been included in an extract and should be removed. Records in other extracts that reference the deleted event as a Foreign key should also be deleted
COMPLIANCE_MGR String 71 - The full name of the user who is responsible for the checking all Compliance requirements O / O ISSUE_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date the contract was issued M / M EXPIRY_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date the contract expires O / O KPI_START_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date that KPI statistics start processing O / M SCHEDULE String 10 - The id for the schedule of rates which apply to the order. O / M SUPPLIER String 70 - The name of the supplier company engaged this contract. M / M VENDOR_NO String 20 - The Vendor number of the Supplier Company O / M PAYMENT_TERMS Number 2 - The payment terms applying to the contract O / M SUPPLIER_ABN String 11 The Supplier company ABN O / O SUPPLIER _CONTACT_NAME String 71 - The full name of the user who is identified as primary Supplier Contact O / M STAR_RATING Number 1 - On a scale of 1-5, the calculated rating of supplier performance O / O OVERRIDE_STAR_RATING Number 1 - On a of 1-5, the manual override rating of supplier performance O / O DATA_REFRESH_DATE Date 23 - yyyy/mm/dd hh:mm:ss Data propagates from the application to the Datamart at defined intervals. This field indicates the date at which this record was read from the application. M / M (Mandatory column indicates whether the is mandatory or optional at the database and application levels) Design Rules: Only the customer contracts will be included in the Extract – this includes contracts issued to the customer as well as contracts raised by the customer The details of the Contract which are extracted to the Datamart are the latest values stored against the Contract at processing time.
MODIFY_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date and time the attachment was or its content modified. O / O MODIFIER String 71 - The full name of the user who attached the document O / O STATUS String 20 - Document Review status O / O DOC_VERSION String 10 - Version of the attached document O / O DOC_SIZE Integer 12 - Size of the attached document (in bytes) DOC_TYPE String 50 Name of associated user managed document type, or internal types “Engagement, "INVOICE”, "Mobile, "Migrated”, "Batch”,"Field Completion Advice” etc VIEW_BY String 32 - Whether the document is viewable by customer & supplier (SUPP), supplier only (SUPP_ONLY) or customer only (CUST) O / O SUPPLIER_UPLOADED Number 1 - Whether the document was uploaded by a supplier O / O GPS_LONGITUDE Number 9 6 The Longitude co-ordinates from which the document was attached from the mobile device O / O GPS_LATITUDE Number 9 6 The Latitude co-ordinates from which the document was attached from the mobile device O / O GPS_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date that the document was uploaded from the mobile device. M / M DATA_REFRESH_DATE Date 23 - yyyy/mm/dd hh:mm:ss Data propagates from the application to the Datamart at defined intervals. This field indicates the date at which this record was read from the application. M / M (Mandatory column indicates whether the field is mandatory or optional at the database and application levels)