273803
273803 - KM-82CDLY / Quote for MIS extracts for CON
Copyright OBS Logistics © 2010
The information contained herein is the property of OBS Logistics and is supplied without liability for errors or omissions. No part may be reproduced or used except as authorised by contract or other written permission. The copyright and foregoing restriction on reproduction and use extend to all media in which the information may be embodied
FUNCTIONAL OVERVIEW
Client Requirement
Provide standard MIS extracts from Consumer Networks as per Staples and Aramco.
Providing users with timely MIS information
Solution
The suite of functions developed to extract new and updated transactions will be applied to the Consumer Networks MTS database. Any data added or updated in the last 4 days will be extracted. The data set will be formatted into delimited flat text files which will be moved to a folder ready to be pulled into the MIS infrastructure by ftp. The file transfer will be controlled by the MIS solution. The scope of tables and data referenced and created for transfer into the MIS solution will be documented in the specification of the MIS extract solution. Most of the tables in scope already have insert and update date and time stamps but those that do not will be changed to include these fields. This will avoid full table transfers on all tables included, including the master data like locations. OBS will analyse the current data map provided that was created from the existing UK Secondary MIS solution (to be superseded) and document all data structure differences on the Consumer Networks data model. The Consumer Networks system does not require additional tables to be included for a booking module (unlike Saudi Aramco). OBS will where appropriate apply new database indexes on the insert and created columns to improve the efficiency of data extract queries depending on volumes. N.B. This MIS extract will be based on the current MIS extract set up for UK Secondary and Healthcare databases. Any changes to the format of the current MIS extract may incur additional costs.
Provide data extraction run on a number of Oracle DMBS_JOB that will produce delimited flat files that will be collected from the MTS server(s) from the following tables. The MIS solution will manage the file transfer on a pull basis and it is assumed will delete the files it collects or moves them to an archive store on the OBS database server.
Focus Area
Business Area Trip
SCH_HAULAGE_ACTIVITY
SCH_TRIP
SCH_TRIP_STOP
Business Area Resources
IMP_DECODE_ENTRY
ORG_CUSTOMER
RES_CARRIER
RES_CARRIER_TYPE
RES_PERSON
RES_TRAILER_TYPE
Business Area Orders
SCH_ORD
SCH_ORD_NON_CONFORM
SCH_ORDER_LINE
Business Area Locations
GEO_LOCATION
GEO_LOCATION_USAGE
Business Area Contracts
CNT_CHARGE
CNT_CHARGE_TIER
CNT_CONTRACT
CNT_TARIFF
Business Area Bookings
INT_BOOKING_DETAIL
SAP_PRODUCT_ITEM
SCH_BOOKING
SCH_PRODUCT_SUMMARY
SCH_REASON_CODE
Business Area Schedule
SCH_SCHEDULE
The export will include all columns on the above tables.
Scope
This development will be applied to system version 10.5 on CONTST and once approved CONPRD.
The basic building blocks used for CMLV will be re-used in this instance.
The MTS extract will form an intrinsic module of the transport solution and any future development / release of MTS must consider the impact on data extract for the MIS solution, in particular table changes or additions to the underlying data structure.
A data take on exercise will be conducted that will create the same file formats but for historic date ranges. It is anticipated that this data will be sent to VM as a one-off using DVD media. The larger tables might need to be split down and provided as monthly history to keep the take-on file size manageable.
Data
The number of files, file content and frequency of extract will be controlled by a new suite of administration data tables. This means that should the scope of the solution be changed, this can be managed through data configuration rather than re-coding of the solution. The new system tables (MIS_EXTRACT_HEADER, MIS_EXTRACT_DETAILS and MIS_EXTRACT_RUN_DETAIL) will be created to control what tables and columns are to be exported
Database packages DP_MIS.sql and DP_MIS_ALL.sql, currently used in CMLV, will be applied to CONPRD. Any alterations required will be done but all changes will be made to keep the package as generic as possible so that it can be applied to all databases when required. The packages should, however, be generic, as it is the data in the new MIS tables (outlined above) that will change per database.
DBMS_JOB(s) will be created to call either one or a number of extracts to be run at a specific time. It is envisaged that there could be a number of jobs depending on the number of extracts needed.
For example, if the MIS system only requires an extract, for example, from GEO_LOCATION once a day but they want to extract data from SCH_ORD once every hour these will be on a different DBMS_JOB, extracts can be grouped on the DBMS_JOB.
FUNCTIONAL DESCRIPTION
The data for the export will be based upon all columns in the following tables for the COLV database.
The following tables will be required:
CNT_CHARGE
CNT_CONTRACT
CNT_CHARGE_TIER
CNT_TARIFF
GEO_LOCATION
GEO_LOCATION_USAGE
IMP_DECODE_ENTRY
INT_BOOKING_DETAIL
ORG_CUSTOMER
RES_CARRIER
RES_CARRIER_TYPE
RES_PERSON
RES_TRAILER_TYPE
SAP_PRODUCT_ITEM
SCH_BOOKING
SCH_HAULAGE_ACTIVITY
SCH_ORD
SCH_ORD_NON_CONFORM
SCH_ORDER_LINE
SCH_PRODUCT_SUMMARY
SCH_REASON_CODE
SCH_SCHEDULE
SCH_TRIP
SCH_TRIP_STOP
The tables are listed in ‘Appendix A’ any new columns with database triggers required highlighted in red. It is expected that each column will be delimited with a '®' symbol (except ‘INT_BOOKING_DETAIL’ which will use a '¤') and that the extract will be run for records created or updated in the last 4 days.
It should be noted that the column positions in the tables may be changed so that the sequence of the extract may be maintained as required.
The extract may also include extra columns not contained on the tables, for example, ‘SPARE_DECIMAL’, ‘SPARE_TEXT’ and ‘SPARE_DATE’. These extra columns may be maintained as required for each table extract.
REFERENCES
| EST-273803 KM-82CDLY Quote for MIS extracts for CON v1.0.doc |
DOCUMENT HISTORY
| Initial version | ||||
| Reviewed and Issued |
AUTHORISED BY
| Matt Crisford | Development Manager | |
| Peter Greer | TMSCC MTS Product Manager |
Back to CONPRD Release - 10/03/2010
