Middletong: /* Focus Area */

2009-12-16T11:50:11Z

Focus Area

← Older revision		Revision as of 13:50, 16 December 2009
Line 33:		Line 33:

	'''Business Area Trip'''		'''Business Area Trip'''

	SCH_HAULAGE_ACTIVITY		SCH_HAULAGE_ACTIVITY
	SCH_TRIP		SCH_TRIP
Line 38:		Line 39:

	'''Business Area Resources'''		'''Business Area Resources'''

	IMP_DECODE_ENTRY		IMP_DECODE_ENTRY
	ORG_CUSTOMER		ORG_CUSTOMER
Line 46:		Line 48:

	'''Business Area Orders'''		'''Business Area Orders'''

	SCH_ORD		SCH_ORD
	SCH_ORD_NON_CONFORM		SCH_ORD_NON_CONFORM
Line 51:		Line 54:

	'''Business Area Locations'''		'''Business Area Locations'''

	GEO_LOCATION		GEO_LOCATION
	GEO_LOCATION_USAGE		GEO_LOCATION_USAGE

	'''Business Area Contracts'''		'''Business Area Contracts'''

	CNT_CHARGE		CNT_CHARGE
	CNT_CHARGE_TIER		CNT_CHARGE_TIER
Line 61:		Line 66:

	'''Business Area Bookings'''		'''Business Area Bookings'''

	INT_BOOKING_DETAIL		INT_BOOKING_DETAIL
	SAP_PRODUCT_ITEM		SAP_PRODUCT_ITEM
Line 68:		Line 74:

	'''Business Area Schedule'''		'''Business Area Schedule'''

	SCH_SCHEDULE		SCH_SCHEDULE

Middletong: /* Focus Area */

2009-12-16T11:49:40Z

Focus Area

← Older revision		Revision as of 13:49, 16 December 2009
Line 33:		Line 33:

	'''Business Area Trip'''		'''Business Area Trip'''
	SCH_HAULAGE_ACTIVITY		SCH_HAULAGE_ACTIVITY
	SCH_TRIP		SCH_TRIP
	SCH_TRIP_STOP		SCH_TRIP_STOP

	'''Business Area Resources'''		'''Business Area Resources'''
	IMP_DECODE_ENTRY		IMP_DECODE_ENTRY
	ORG_CUSTOMER		ORG_CUSTOMER
	RES_CARRIER		RES_CARRIER
	RES_CARRIER_TYPE		RES_CARRIER_TYPE
	RES_PERSON		RES_PERSON
	RES_TRAILER_TYPE		RES_TRAILER_TYPE

	'''Business Area Orders'''		'''Business Area Orders'''
	SCH_ORD		SCH_ORD
	SCH_ORD_NON_CONFORM		SCH_ORD_NON_CONFORM
	SCH_ORDER_LINE		SCH_ORDER_LINE

	'''Business Area Locations'''		'''Business Area Locations'''
	GEO_LOCATION		GEO_LOCATION
	GEO_LOCATION_USAGE		GEO_LOCATION_USAGE

	'''Business Area Contracts'''		'''Business Area Contracts'''
	CNT_CHARGE		CNT_CHARGE
	CNT_CHARGE_TIER		CNT_CHARGE_TIER
	CNT_CONTRACT		CNT_CONTRACT
	CNT_TARIFF		CNT_TARIFF

	'''Business Area Bookings'''		'''Business Area Bookings'''
	INT_BOOKING_DETAIL		INT_BOOKING_DETAIL
	SAP_PRODUCT_ITEM		SAP_PRODUCT_ITEM
	SCH_BOOKING		SCH_BOOKING
	SCH_PRODUCT_SUMMARY		SCH_PRODUCT_SUMMARY
	SCH_REASON_CODE		SCH_REASON_CODE

	'''Business Area Schedule'''		'''Business Area Schedule'''
	SCH_SCHEDULE		SCH_SCHEDULE

	The export will include all columns on the above tables.		The export will include all columns on the above tables.


	==Scope==		==Scope==

Middletong: New page: =262150 - GP-7PVK3S/ Quote fo MIS Extracts for HCR= Copyright OBS Logistics © 2009 The information contained herein is the property of OBS Logistics and is supplied without liability f...

2009-12-16T11:48:50Z

New page

=262150 - GP-7PVK3S/ Quote fo MIS Extracts for HCR=

Copyright OBS Logistics © 2009

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

=Functiona Overview=

==Project Background==
About two years ago Visual Metrics (VM) were commissioned to build a data warehouse for MTS and MTS_CIM for Consumer. From this Paul Calvert has proposed an MIS system to provide across-application reporting.

A central data warehouse based on SQL Server, traditionally VM pull the data as required, however, DHL would prefer it to be message-driven, i.e. pushed out as then DHL do not need to understand the underlying schema or take into account changes in that schema, this remains the responsibility of the system / service provider.

MTS consists of a number of databases including one for the DHL customer Healthcare UK (HCLV), see appendix A for list of databases. It has been decided that this extract will be taken from HCLV and based on the CMLV extract.

==Client Requirement==
Provide standard MIS extracts from Healthcare MTS as per Staples and Aramco.

==Solution==
Providing users with timely MIS information
A suite of functions will be developed to extract new and updated transactions from the Healthcare MTS database in the last 4 days. 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. The functionality will be written so that this scope is controlled dynamically by administration data tables. This means changes to the scope of data extracted can (in principle) be made quickly without re-coding.
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 Healthcare UK data model.
The Healthcare UK 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. Any changes to the format of the current MIS extract may incur additional costs.

==Focus Area==

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.

'''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.6 on HCRTST and once approved HCRPRD.

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.

=Functional Description=
The data for the export will be based upon all columns in the following tables for the HCLV 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 below with any new columns with database triggers required highlighted in red. It is expected that each column will be delimited with a '®' symbol and that extract will be run for records created or updated in the last 4 days.

The columns may be added as follows, for example:

alter table RES_PERSON add created_by varchar2(40);
alter table RES_PERSON add created_date date;
alter table RES_PERSON add updated_by varchar2(40);
alter table RES_PERSON add updated_date date;

= References =

{| Border="1"
| <center>'''Ref No'''</center>
| <center>'''Document Title & ID'''</center>
| <center>'''Version'''</center>
| <center>'''Date'''</center>

|-
| <center>1</center>
| EST-262150 GP-7PVK3S Quote for MIS extracts for HCR v1.0.doc
| <center>1</center>
| <center>12/03/09</center>
|}

= Document History =

{| Border="1"
| <center>'''Version'''</center>
| <center>'''Date'''</center>
| <center>'''Status'''</center>
| <center>'''Reason'''</center>
| <center>'''Initials'''</center>

|-
| <center>0.1</center>
| <center>20/03/09</center>
| <center>Draft</center>
| Initial version
| <center>LAD</center>

|-
| <center>1.0</center>
| <center>23/03/09</center>
| <center>Issue</center>
| Reviewed and Issued
| <center>MJC</center>
|}

= Authorised By =

{| Border="1"
| '''''Matt Crisford'''''
| Development Manager
|

|-
| '''''Suk Sandhu'''''
| TMSCC MTS Product Manager
|

|}

262150 - Revision history

Middletong: /* Focus Area */

Middletong: /* Focus Area */

Middletong: New page: =262150 - GP-7PVK3S/ Quote fo MIS Extracts for HCR= Copyright OBS Logistics © 2009 The information contained herein is the property of OBS Logistics and is supplied without liability f...