3.4 29th August 2014 Corrections to text McIlwraith 3.5 25th September 2014 Inclusion of IntegralPLUS related fields PMO_NR and OP_NR to extracts Hamish Goddard 3.6 26th September 2014 Correction to PMO_NR label Hamish Goddard 3.7 24th February 2015 Convert SOR to History extract and changes to SOR extract (p33). Trish McIlwraith 3.8 24th March 2015 Convert Deleted Events extract from Master to (p 30) Trish McIlwraith 3.9 15th July 2015 Update to Datamart services and inclusion of SLAs – no change to extracts (p7) McIlwraith 4.0 24th September 2015 Inclusion of Geo-cordinates in Order Details extract and Order Invoice History extract. Inclusion of Used Qty in the Order Item details extract. Inclusion of new BASELINE_DATE field and change name of TARGET_DATE to FORECAST_DATE in Milestone Details delta extract. McIlwraith, Hamish Goddard 4.1 8 October 2015 Removed section 2.10 Service Level Agreement (redundant). Changes to Milestone Details and Milestone Type extracts. Hamish Goddard, Norval Hope 4.2 5 November 2015 Order Invoice History extract changes: Removal of ‘Subtype’ field GPS longitude and latitude field positions reordered Hamish Goddard 4.3 28 February 2017 Extension of document history extract field FORM_ID from 8 to 16 characters Hamish Goddard 5.0 14 June 2017 Introduction of Claim History extract Introduction of Batch Job extract. Extended length of Attribute Name in Attribute Master extract Extend length of Special Instructions in Order Details extract Extend Contract Details extract to identify method of invoicing and contract category Multiple changes to Document History extract Apply filter to Datamart processor to remove characters Introduce ‘Sequence’ to Order Invoice History extract Trish McIlwraith
ASSOR Order Assigned to Contract Administrator INCAN Invoice Cancelled QRESENT Supplier Resent AWI Customer has approved the Work Docket INCLO Invoice Closed (No longer used) QSENT Supplier Sent BACK Order has been reversed (no longer used) INREC Invoice Recalled to Supplier QUCRE Master Quote Created (No longer used) Cancelled Invoice Rejected Quote requested for Order (No longer used) CANCLM Disputed Claim Cancelled INV Invoice has been submitted (no longer used) REACT Invoice has been un-cancelled CAT Order Categorised INVPA Invoice validated and marked for extract (No longer used) Customer Response to Extension of Time Request CHGUG Order moved from workgroup to another (no longer used) IRECEB Invoice Exceeding Budget Recalled REEOTS Customer Response to Extension of Time Request Viewed by Supplier CLMODF Submission Overdue by 14 Days IRECUB Invoice Under Budget Recalled REESCL Re-Escalate from Response CLMODN Submission Overdue by 90 Days IRECEB Invoice Exceeding Budget Recalled REFUS Order Refused CLMREC Final Claim Recalled from Approval Manager MODIF Order Details Modified (No longer used) New Reinstatement CREQAP Claim Requires Manager Approval OIEC Invoiced Entity on Order Changed Rejected Job CLRESP Escalated FInal Claim Responded to by Manager OK Fax Order Acceptance has been Acknowledged (No longer used) RENOT Reinstatement Not Required
CRESPV Escalated Claim Response Viewed ORCLO Order Closed by Supplier REOPEN Reopened CLMRJ Claim Rejected by Approval Manager ORDCM Order Closed (No longer used) REACT Invoice has been un-cancelled CLONE Order Cloned (no longer used) ORDREC Order Reccalled from Approval Manager Order Requires Approval CLREC Claim Recalled ORDREJ Order Rejected by Approval Manager REREQ Reinstatement Required CLSTP Claim Order Stopped ORESP Manager Respornse to Manually Escalated Order RESSUP Customer Response to Variation Request Viewed by Supplier CMPREC Completed Order Recalled ORESPV Escalated Order Response Viewed REVAR Customer Response to Variation Request COMPL Order Completed ORDVR Reopened (No longer used) REVREC Customer Response to Variation Request Recalled COPY Copied ORREC Order Recalled (No longer used) RINSB Reinstatement Submitted CRQVAR Customer Requested Variation OVBGT Budget Exceeded RJREI Reinstatement Rejected DISCON Design Order Discontinued (no longer used) OVDRLC Overdue Order Reallocated Extension of Time Request Submitted DISP Final Claim Disputed Order Overdue RQVAR Variation Request Submitted DONE Order Marked Done PAP Payment Claim Approved by Manager RSBCL Claim Resubmitted EDOCER Order EDOC Error PAPREC Payment Claim Requiring Approval Recalled STOP Order Stopped
RECORD_TYPE String 20 - Identifies what information is stored in each row. The four available record types are: Last Good File – the file name of last successful Datamart extract Window Date – The start date/time of the processing window for last successful extract Window End Date– The end date/time of the processing window for last successful extract Line Count – as separate line for each file contained in the current extract FILE_NAME String 50 - For the Last_Good_File row, this field contains the Name of last successful Datamart extract (ie AppianDatamart_yymmdd_hhmmss.zip) For each Line Count row, this field contains the name of each data file in the current extract PHYSICAL_LINE_COUNT Integer 10 0 Contains a physical row count of the number of rows in each extract file. Design Rules: A processor will be run on completion of extraction of all current Datamart extract files that will determine a row count for each file. The row count that is indicated in the control file against each file type will be the physical row count in the csv file, including the header row. The record type Last_Good_File will contain the name of the last successful Datamart extract followed by the Window_Start_Date and Window_End_Date which defined the duration of the processing window for that file. After this, the names of each file contained in current extract will be listed against a Line_Count record type, sorted in ascending file name order. The time stamp of current extract will be shown against each current extract line, as will the row count of each file.
The possible values are: 'PRV' - Project View attributes; 'SR' - Service Request attributes; 'ORD' - Order attributes; and 'ORDITEM' - Order Item attributes. 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 may be useful in maintaining reporting consistency with the application. O / M BANNER Integer 1 0 Indicates whether the attribute is displayed in the entity banner (1) or not (0). The banner is the section displayed above the tabs for each entity. O / M
START_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date that the Project is to start O / M END_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date that the Project is to end O / M STATUS String 10 - The current status of the Project. O / M PROJECT_MANAGER String 71 - The name of the person who is currently managing the Project in ConSol O / O WORKGROUP_NAME String 50 - The name of the workgroup/area that is responsible for the Project. The Project Manager must be a member of this workgroup O / M PROJ_ESTIMATE Number 12 2 The estimated total cost of the Project. This is a value entered by the Project Manager when the Project is created or updated by the Project Manager during the life of the Project. O / O CREATE_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date that the Project was created in ConSol O / M INV_ENTITY_NAME String 60 - The Invoiced Entity responsible for the Project O / 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 WORK_TYPE_NO Integer 10 0 Foreign key. Unique identifier for each Work Type (see the Work Type Master table) O / M WORK_TYPE_NAME String 35 - The Work Type Name O / M INTERFACED_PROJECT String 1 Identifies whether Project was created through a web interface. 1 = Yes, or 0 = No O / O (Mandatory column indicates whether the field is mandatory or optional at the database and application levels) Design Rules: Only Project Views that the customer has access to will be included in the extract. If the Project View is owned by another customer, certain fields in the extract will not display information (ie. Work_type_no) however the Project View will be listed.
LOCATION_POSTCODE String 4 The postcode at which the work on the order is to be carried out This field should be populated for all orders O / O LOCATION_STATE String 3 The state in which the work on the order is to be carried out This field should be populated for all orders O / O WIP_EVENT String 4000 - The current WIP status of the order O / O WIP_EVENT_NO Integer 10 0 Foreign key. Unique identifier for each WIP Event (see the WIP Master table) 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 indicates the date at which this record was read from the application. M / M ACCEPT_DATE Date 23 - yyyy/mm/dd hh:mm:ss The latest date and time that the order was accepted by the Supplier O / O TRANSMITTED_DATE Date 23 - yyyy/mm/dd hh:mm:ss The latest date and time that the order was transmitted to the Supplier O / O VERIFIED_DATE Date 23 - yyyy/mm/dd hh:mm:ss The latest date and time that the submitted was verified O / O INV_ENTITY_NAME String 60 - The name of the Invoiced Entity responsible for the Order O / M SUPP_REF_NR String 30 - The Supplier Reference Number – input by the supplier on acceptance of the order and not visible to the customer O / O MASTER_QUOTE_ID String 8 - The Master Quote Number that the Supplier quote was issued from O / O QUOTE_CLOSE_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date that the has to be submitted by O / O PARENT_ORDER_ID String 8 - The Id of the that the current order was generated from – this could be as a copied order or as an order. O / O
PROJ_VIEW_NO Integer 10 0 Foreign key. Unique identifier for the Project to this attribute is attached if it is a project attribute, null otherwise. O / O ORDER_ID String 8 - Foreign key. The unique ConSol order ID to which this attribute is attached if it is a order attribute, null otherwise. O / O ORDER_ITEM_NO Integer 10 - Foreign key. A unique identifier for the order item to which this attribute is attached if it is an item attribute, null otherwise. O / O ATTRIBUTE_TYPE_NO Integer 10 0 Foreign key. Uniquely identifies the attribute type. O / M ATTRIBUTE_LIST_NO Integer 10 0 Foreign key. Uniquely identifies the attribute list value. O / O ATTRIBUTE_NAME String 64 - The name of the attribute whose value was changed. O / 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 format; ‘list’ – a defined list of coded values and descriptions; ‘currency’ – numeric value in currency format ($9999.99); and ‘number’ – numeric value. O / M ATTRIBUTE_VALUE String 255 - The Attribute Value 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 field is mandatory or optional at the database and application levels) Design Rules Extract includes current attribute value for each entity Each record will only have one of Proj_View_No, Order_id or Order_item_no populated
VARIATION_ACCEPTANCE String 10 - - The variation status of the Item (n/a, New, Agreed, Partial or Rejected) O / M SNAPSHOT_DETAIL String 2000 - - The snapshot type where the order item details can be viewed through the application O / O SNAPSHOT_CREATE_DATE Date 23 - yyyy/mm/dd hh:mm:ss Date and Time of Snapshot creation O / M UPDATE_USER String 71 - - The full name of the user who triggered the recording of the History log record for respective item version record O / M VERSION TYPE String 10 - The Item Version type (see item version type above O / 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. Should this be last row as per other extracts? M / M (Mandatory column indicates whether the is mandatory or optional at the database and application levels) Design Rules: The extract will include duplicate lines for every Item that has one or more snapshots generated or invoice activity during the processing window Item History records for items that no longer exist will be removed from this extract. Item Variation Extract Delta This is a table of all Item details for items where an item variation occurred Data Dictionary Field Name Type Precision Format Description Mandatory DB / App
MODIFY_DATE Date 23 - yyyy/mm/dd hh:mm:ss The date and time the attachment was added 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) O / O DOC_TYPE String 50 Name of associated user managed document type, or internal (ie Engagement, INVOICE, Mobile, Migrated, Batch,Field Completion Advice) O / O 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 is mandatory or optional at the database and application levels) Design Rules Extract includes document attachment id for documents added to Project Views, Orders or Invoices in ConSol application within the processing window that is visible to the customer.