BI Data Extract Guide
From CTMS
Introduction
Data exports are available to populate BI or MIS systems. These exports are based upon all columns in the following tables within the CTMS database.
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_ORDER_ITEMS SCH_ORDER_ITEMS_REASONS SCH_PRODUCT_SUMMARY SCH_REASON_CODE SCH_SCHEDULE SCH_TRIP SCH_TRIP_STOP
Note: Other tables are available and may be exported – these are the most common. The tables and fields exported can be configured.
Schematic
The following schemas indicate the links between the tables (Primary & Foreign keys)
Data Fields
The tables are listed. It is expected that each column will be delimited with a '®' symbol and that extract can be scheduled and run on with records created or updated in the last 4 days.
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
CNT_CHARGE CHRG_PARAM VARCHAR2(12) 1
VALUE NUMBER(23,5) 2
CREATED_BY VARCHAR2(40) 3
CREATED_DATE DATE 4
UPDATED_BY VARCHAR2(40) 5
UPDATED_DATE DATE 6
CHARGE_ID NUMBER(20,0) 7
CONDITION_NAME VARCHAR2(12) 8
CONDITION_VALUE VARCHAR2(12) 9
TIER_ID NUMBER(12,0) 10
CHARGEABLE_QUANTITY NUMBER(12,0) 11
CHARGE_TYPE VARCHAR2(12) 12
EFFECTIVE_FROM DATE 13
EXPIRY_DATE DATE 14
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
CNT_CHARGE_TIER UPPER_LIMIT NUMBER(12,2) 1
TARIFF_ID NUMBER(20,0) 2
TIER_NAME VARCHAR2(20) 3
MIN_CHARGE NUMBER(20,2) 4
MAX_CHARGE NUMBER(20,2) 5
CREATED_BY VARCHAR2(40) 6
CREATED_DATE DATE 7
UPDATED_BY VARCHAR2(40) 8
UPDATED_DATE DATE 9
TIER_ID NUMBER(12,0) 10
ADD_UPPER_LIMIT NUMBER(12,2) 11
ADD_TIER_NAME VARCHAR2(20) 12
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
CNT_CONTRACT CONTRACT_ID NUMBER(20,0) 1
CONTRACT_TYPE VARCHAR2(12) 2
LOCAL_PARTY VARCHAR2(12) 3
COUNTER_PARTY VARCHAR2(12) 4
EFFECTIVE_FROM DATE 5
CREATED_BY VARCHAR2(40) 6
CREATED_DATE DATE 7
UPDATED_BY VARCHAR2(40) 8
UPDATED_DATE DATE 9
DEFAULT_CCY VARCHAR2(3) 10
EXPIRY_DATE DATE 11
ORDER_MOVEMENT_RATE VARCHAR2(1) 12
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
CNT_TARIFF CONTRACT_ID NUMBER(20,0) 1
DEL_TYPE VARCHAR2(35) 2
TARIFF_ID NUMBER(20,0) 3
TARIFF_NAME VARCHAR2(50) 4
TIER_UNITS VARCHAR2(12) 5
EXPIRY_DATE DATE 6
CREATED_BY VARCHAR2(40) 7
CREATED_DATE DATE 8
UPDATED_BY VARCHAR2(40) 9
UPDATED_DATE DATE 10
CCY_CODE VARCHAR2(3) 11
CHARGE_TYPE VARCHAR2(12) 12
EFFECTIVE_FROM DATE 13
SEQUENCE NUMBER(5,0) 14
ADD_TIER_UNITS VARCHAR2(12) 15
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
GEO_LOCATION LOCATION_ID VARCHAR2(12) 1
DEPOT VARCHAR2(12) 2
LOCATION_NAME VARCHAR2(35) 3
EXT_REF VARCHAR2(50) 4
ROUTE_CODE VARCHAR2(12) 5
ADDRESS_LINE1 VARCHAR2(50) 6
ADDRESS_LINE2 VARCHAR2(50) 7
ADDRESS_LINE3 VARCHAR2(50) 8
TOWN VARCHAR2(50) 9
COUNTY VARCHAR2(50) 10
COUNTRY VARCHAR2(50) 11
COUNTRY_CODE VARCHAR2(3) 12
POSTCODE VARCHAR2(9) 13
POSTAL_REGION VARCHAR2(15) 14
PHONE VARCHAR2(50) 15
FAX VARCHAR2(50) 16
LATITUDE NUMBER(9,5) 17
LONGITUDE NUMBER(9,5) 18
LOADING_RATE VARCHAR2(12) 19
UNLOADING_RATE VARCHAR2(12) 20
SCHEDULE_OFFSET NUMBER(2,0) 21
RESPONSIBLE_COST_CENTRE VARCHAR2(50) 22
PARENT_LOC_ID VARCHAR2(12) 23
VENDOR_CODE VARCHAR2(12) 24
OPEN_MON VARCHAR2(5) 25
CLOSE_MON VARCHAR2(5) 26
OPEN_FRI VARCHAR2(5) 27
CLOSE_FRI VARCHAR2(5) 28
CREATE_EMPTIES VARCHAR2(1) 29
COMMENTS VARCHAR2(255) 30
INACTIVE VARCHAR2(1) 31
TRAILER_TYPE VARCHAR2(35) 32
ALT_COLL_LOC VARCHAR2(12) 33
ALT_DEL_LOC VARCHAR2(12) 34
DEFAULT_XDOCK VARCHAR2(12) 35
DFLT_SC_HANGING VARCHAR2(12) 36
DFLT_SC_BOXED VARCHAR2(12) 37
PROFILE_ID VARCHAR2(12) 38
CREATED_BY VARCHAR2(40) 39
CREATED_DATE DATE 40
UPDATED_BY VARCHAR2(40) 41
UPDATED_DATE DATE 42
CONTROL_TOWER VARCHAR2(1) 43
PLANNING_REGION VARCHAR2(5) 44
COST_CENTRE_NAME VARCHAR2(12) 45
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
GEO_LOCATION_USAGE LOCATION_ID VARCHAR2(12) 1
USAGE_TYPE VARCHAR2(12) 2
USAGE_ID VARCHAR2(12) 3
CREATED_BY VARCHAR2(40) 4
CREATED_DATE DATE 5
UPDATED_BY VARCHAR2(40) 6
UPDATED_DATE DATE 7
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
IMP_DECODE_ENTRY DECODE_NAME VARCHAR2(35) 1
SOURCE_VALUE VARCHAR2(256) 2
TARGET_VALUE VARCHAR2(256) 3
CUSTOMER_ID VARCHAR2(12) 4
DECODE_TYPE VARCHAR2(12) 5
CREATED_BY VARCHAR2(40) 6
CREATED_DATE DATE 7
UPDATED_BY VARCHAR2(40) 8
UPDATED_DATE DATE 9
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
INT_BOOKING_DETAIL INT_RECORD_ID VARCHAR2(100) 1
REC_NUM NUMBER(22,0) 2
FROM_LOC VARCHAR2(12) 3
TO_LOC VARCHAR2(12) 4
QUANTITY NUMBER(20,5) 5
DEL_DATE VARCHAR2(12) 6
PRODUCT_TYPE VARCHAR2(12) 7
DU_TYPE VARCHAR2(12) 8
BOOKING_REF VARCHAR2(20) 9
TRANSHIP_IND VARCHAR2(1) 10
RECORD_STATUS VARCHAR2(10) 11
VALIDATION_ERROR VARCHAR2(2000) 12
CREATED_DATE DATE 13
CREATED_BY VARCHAR2(40) 14
UPDATED_DATE DATE 15
UPDATED_BY VARCHAR2(40) 16
IDENTIFIER VARCHAR2(10) 17
ACTION VARCHAR2(1) 18
PRODUCT_ITEM VARCHAR2(100) 19
UOM VARCHAR2(3) 20
DEL_PRIORITY VARCHAR2(2) 21
HANDLING_CODE1 VARCHAR2(2) 22
HANDLING_CODE2 VARCHAR2(2) 23
HANDLING_CODE3 VARCHAR2(2) 24
CARRIER_CODE VARCHAR2(3) 25
INSP_REQ VARCHAR2(2) 26
TEST_REQ VARCHAR2(2) 27
WAREHOUSE_CODE VARCHAR2(3) 28
STORAGE_CODE VARCHAR2(3) 29
SOURCE_SYSTEM VARCHAR2(25) 30
DU_QTY NUMBER(24,4) 31
WEIGHT NUMBER(12,2) 32
VOLUME NUMBER(20,5) 33
BKG_PLANT VARCHAR2(4) 34
BKG_STORAGE_LOC VARCHAR2(4) 35
BKG_SHELF_LIFE DATE 36
BKG_REQUESTOR VARCHAR2(20) 37
BKG_TELEPHONE VARCHAR2(30) 38
BKG_ACCOUNT_NO VARCHAR2(20) 39
BKG_BUILDING VARCHAR2(10) 40
BKG_ROOM VARCHAR2(8) 41
BKG_COMMENTS VARCHAR2(200) 42
ASN_HANDLING_UNIT VARCHAR2(20) 43
OLD_MAT_NO VARCHAR2(18) 44
CHBNO VARCHAR2(30) 45
LAB_TEST VARCHAR2(80) 46
SHELF_LIFE_IND VARCHAR2(1) 47
MIN_REM_SHELF VARCHAR2(4) 48
TEMP_COND VARCHAR2(20) 49
REQ_NAME VARCHAR2(40) 50
REQ_PHONE VARCHAR2(30) 51
REQ_ACC_NO VARCHAR2(20) 52
REQ_BUILD VARCHAR2(20) 53
REQ_ROOM VARCHAR2(10) 54
COMMENTS VARCHAR2(80) 55
ADD_DATA_1 VARCHAR2(40) 56
ADD_DATA_2 VARCHAR2(40) 57
ADD_DATA_3 VARCHAR2(40) 58
BKG_ADD_DATA_1 VARCHAR2(40) 59
BKG_ADD_DATA_2 VARCHAR2(40) 60
BKG_ADD_DATA_3 VARCHAR2(40) 61
PRODUCT_DESC VARCHAR2(100) 62
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
ORG_CUSTOMER CUSTOMER_ID VARCHAR2(12) 1
CUSTOMER_NAME VARCHAR2(50) 2
CONTACT_NAME VARCHAR2(50) 3
LOCATION_ID VARCHAR2(12) 4
MTM_CUST_CODE VARCHAR2(2) 5
SELF_BILL VARCHAR2(1) 6
CUST_GROUP VARCHAR2(12) 7
COST_CENTRE_NAME VARCHAR2(12) 8
VAT_COUNTRY VARCHAR2(3) 9
VAT_REG_NO VARCHAR2(50) 10
CREATED_BY VARCHAR2(40) 11
CREATED_DATE DATE 12
UPDATED_BY VARCHAR2(40) 13
UPDATED_DATE DATE 14
COUNTRY VARCHAR2(3) 15
USE_LATE_ORDERS VARCHAR2(1) 16
USE_MANUAL_ORDERS VARCHAR2(1) 17
USE_URGENT_ORDERS VARCHAR2(1) 18
USE_TRANSACTION_CHARGING VARCHAR2(1) 19
TYPE VARCHAR2(12) 20
LATE_ORDER_DAYS_THRESHOLD NUMBER(5) 21
INT_ORD_TO_LOGNET VARCHAR2(1) 22
UNISON_IF_VALUE VARCHAR2(3) 23
TOK_POD_AVAIL VARCHAR2(1) 24
CONSOLIDATE_ORDERS VARCHAR2(1) 25
CONSOL_ALLOC_METHOD VARCHAR2(12) 26
ORDER_REVENUE_CHARGING_TYPE_ID NUMBER 27
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
RES_CARRIER CARRIER_ID VARCHAR2(12) 1
CARRIER_NAME VARCHAR2(50) 2
GROUP_NAME VARCHAR2(35) 3
COST_CENTRE_NAME VARCHAR2(12) 4
CARRIER_TYPE_ID VARCHAR2(35) 5
CURRENT_HAULAGE_UNITS NUMBER(5,0) 6
FIXED_COST_WEEKDAY NUMBER(20,2) 7
FIXED_COST_SAT NUMBER(20,2) 8
FIXED_COST_SUN NUMBER(20,2) 9
HUB_LOCATION VARCHAR2(12) 10
ENFORCE_START_AT_HUB VARCHAR2(1) 11
ENFORCE_END_AT_HUB VARCHAR2(1) 12
BARCODE_TYPE VARCHAR2(12) 13
TMS_REF VARCHAR2(12) 14
HQ_LOCATION VARCHAR2(12) 15
EXPORTFILE_TYPE VARCHAR2(12) 16
VAT_NUMBER VARCHAR2(35) 17
VAT_COUNTRY VARCHAR2(3) 18
STD_SHIFT_HOURS NUMBER(4,2) 19
STD_FACTOR NUMBER(4,2) 20
ALTERNATE_CARRIER VARCHAR2(1) 21
OVERTIME_FACTOR NUMBER(4,2) 22
MAX_SHIFT_HOURS NUMBER(4,2) 23
TRIP_THRESHOLD_HOURS NUMBER(4,2) 24
MAX_DRIVING_HOURS NUMBER(4,2) 25
ADDRESS_LINE1 VARCHAR2(35) 26
ADDRESS_LINE2 VARCHAR2(35) 27
CREATED_BY VARCHAR2(40) 28
ADDRESS_LINE3 VARCHAR2(35) 29
CREATED_DATE DATE 30
TOWN VARCHAR2(35) 31
UPDATED_BY VARCHAR2(40) 32
UPDATED_DATE DATE 33
COUNTY VARCHAR2(35) 34
COUNTRY VARCHAR2(35) 35
COUNTRY_CODE VARCHAR2(3) 36
POSTCODE VARCHAR2(9) 37
REGION VARCHAR2(2) 38
PHONE VARCHAR2(35) 39
FAX VARCHAR2(35) 40
DRIVER_BREAK_THRESHOLD_HOURS NUMBER(4,2) 41
DRIVER_BREAK_MINS NUMBER(3,0) 42
WORK_BREAK_THRESHOLD_HOURS NUMBER(4,2) 43
WORK_BREAK_MINS NUMBER(3,0) 44
DISPLAY_TYPE VARCHAR2(8) 45
PERMIT_DEBRIEF VARCHAR2(1) 46
CONTACT VARCHAR2(100) 47
WORK_STOPOVER_THRESHOLD_HOURS NUMBER(4,2) 48
STOPOVER_MINS NUMBER(3,0) 49
DRIVE_STOPOVER_THRESHOLD_HOURS NUMBER(4,2) 50
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
RES_CARRIER_TYPE CARRIER_TYPE_ID VARCHAR2(35) 1
TRIP_COST_METHOD VARCHAR2(12) 2
ORD_COST_METHOD VARCHAR2(12) 3
CREATED_BY VARCHAR2(40) 4
CREATED_DATE DATE 5
UPDATED_BY VARCHAR2(40) 6
UPDATED_DATE DATE 7
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
RES_PERSON ID NUMBER(22,0) 1
SURNAME VARCHAR2(50) 2
FORENAME VARCHAR2(50) 3
JOB_TITLE VARCHAR2(50) 4
LOCATION_ID VARCHAR2(12) 5
CREATED_BY VARCHAR2(40) 6
CREATED_DATE DATE 7
UPDATED_BY VARCHAR2(40) 8
UPDATED_DATE DATE 9
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
RES_TRAILER_TYPE TRAILER_TYPE VARCHAR2(12) 1
TEMPERATURE_TYPE VARCHAR2(12) 2
DESCRIPTION VARCHAR2(50) 3
MAX_KG NUMBER(20,2) 4
VOLUME NUMBER(20,5) 5
MAX_RPE NUMBER(8,2) 6
REFRIGERATED VARCHAR2(1) 7
TRAILER_LENGTH NUMBER(3,0) 8
COST_OF_USE NUMBER(3,0) 9
INACTIVE VARCHAR2(1) 10
RIGID VARCHAR2(1) 11
CREATED_BY VARCHAR2(40) 12
CREATED_DATE DATE 13
UPDATED_BY VARCHAR2(40) 14
UPDATED_DATE DATE 15
DRIVE_TIME_MODIFIER NUMBER(3,2) 16
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SAP_PRODUCT_ITEM PRODUCT_ITEM_ID VARCHAR2(18) 1
PRODUCT_ITEM_DESC VARCHAR2(100) 2
DEF_DU_TYPE VARCHAR2(12) 3
DEF_PRODUCT_TYPE VARCHAR2(12) 4
CREATED_BY VARCHAR2(40) 5
CREATED_DATE DATE 6
UPDATED_BY VARCHAR2(40) 7
UPDATED_DATE DATE 8
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_BOOKING SOURCE VARCHAR2(12) 1
DESTINATION VARCHAR2(12) 2
DELIVERY_DATE DATE 3
PROD_TYPE_ID VARCHAR2(12) 4
SCHEDULE_DATE DATE 5
TOTAL_WEIGHT NUMBER(12,2) 6
TOTAL_VOLUME NUMBER(20,5) 7
EFFECTIVE_WEIGHT NUMBER(12,2) 8
BOOKING_REF VARCHAR2(20) 9
COMMENTS VARCHAR2(100) 10
SPECIAL_FLAG VARCHAR2(35) 11
PLANNED VARCHAR2(1) 12
ROUTE VARCHAR2(12) 13
BROUGHT_IN VARCHAR2(1) 14
CREATED_BY VARCHAR2(40) 15
CREATED_DATE DATE 16
UPDATED_BY VARCHAR2(40) 17
UPDATED_DATE DATE 18
SCHED_NAME VARCHAR2(12) 19
TRANSHIP_IND VARCHAR2(1) 20
ORIG_PROD_TYPE VARCHAR2(12) 21
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_HAULAGE_ACTIVITY STOP_ID NUMBER(22,0) 1
SEQUENCE NUMBER(8,0) 2
OMS_REF VARCHAR2(12) 3
ACTIVITY_NAME VARCHAR2(35) 4
COMMENTS VARCHAR2(255) 5
CREATED_BY VARCHAR2(40) 6
CREATED_DATE DATE 7
UPDATED_BY VARCHAR2(40) 8
UPDATED_DATE DATE 9
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_ORD OMS_REF VARCHAR2(12) 1
ORDER_ID VARCHAR2(12) 2
STATUS VARCHAR2(12) 3
SOURCE_SYSTEM VARCHAR2(12) 4
SCHED_NAME VARCHAR2(12) 5
COST_CENTRE_NAME VARCHAR2(12) 6
CUSTOMER VARCHAR2(12) 7
DELIVERY_TYPE_ID VARCHAR2(35) 8
TEMP_COMBO_ID VARCHAR2(12) 9
PARENT_ORDER VARCHAR2(12) 10
TEMPLATE_ID VARCHAR2(35) 11
FROM_LOC VARCHAR2(12) 12
TO_LOC VARCHAR2(12) 13
ORIG_FROM_LOC VARCHAR2(12) 14
FINAL_TO_LOC VARCHAR2(12) 15
CONTRACT_FROM_LOC VARCHAR2(12) 16
CONTRACT_TO_LOC VARCHAR2(12) 17
TARGET_TIME VARCHAR2(5) 18
EARLY_AVAIL DATE 19
LATE_AVAIL DATE 20
EARLY_DEL DATE 21
LATE_DEL DATE 22
TARGET VARCHAR2(12) 23
DISTANCE NUMBER(12,2) 24
TOTAL_RPE_QTY NUMBER(8,2) 25
TOTAL_WEIGHT NUMBER(20,5) 26
TOTAL_VOLUME NUMBER(20,5) 27
TOTAL_PALLETS NUMBER(24,4) 28
TOTAL_PIECES NUMBER(24,4) 29
TOTAL_EFFECTIVE_WEIGHT NUMBER(20,2) 30
TOTAL_ACTUAL_RPE_QTY NUMBER(8,2) 31
CONTRACTUAL_WEIGHT NUMBER(20,2) 32
CONTRACTUAL_PALLETS NUMBER(24,4) 33
CONTRACTUAL_RPE_QTY NUMBER(8,2) 34
ACTUAL_WEIGHT NUMBER(20,2) 35
ACTUAL_VOLUME NUMBER(20,5) 36
ACTUAL_PALLETS NUMBER(24,4) 37
ACTUAL_PIECES NUMBER(24,4) 38
ACTUAL_EFFECTIVE_WEIGHT NUMBER(20,2) 39
DELIVERED_TIME DATE 40
ORD_STD_COST NUMBER(20,2) 41
DO_NOT_CALC_STD_COST VARCHAR2(1) 42
REVENUE_STATUS VARCHAR2(1) 43
ORD_REVENUE NUMBER(20,2) 44
REVENUE_VAT NUMBER(20,2) 45
COST_STATUS VARCHAR2(1) 46
ORD_COST NUMBER(20,2) 47
COST_VAT NUMBER(20,2) 48
COST_ALLOC NUMBER(20,2) 49
ORD_REVENUE_VS NUMBER(20,2) 50
PROFIT NUMBER(20,2) 51
MARGIN NUMBER(8,2) 52
ORD_TIER_NAME VARCHAR2(50) 53
SJC_DATA VARCHAR2(2000) 54
EXTERNAL_REF VARCHAR2(20) 55
COMMENTS VARCHAR2(2000) 56
DELIVERY_INSTRUCTIONS VARCHAR2(60) 57
POD_NAME VARCHAR2(50) 58
LAST_ERROR VARCHAR2(256) 59
SEND_TO_MTM VARCHAR2(1) 60
GROUP_NAME VARCHAR2(12) 61
TRANSCODE VARCHAR2(1) 62
MTM_SPLIT_ID VARCHAR2(20) 63
MTM_FLF VARCHAR2(1) 64
CONF_NO VARCHAR2(12) 65
SHIPPING_REF VARCHAR2(12) 66
CREATED_USER VARCHAR2(50) 67
CREATED_DATE DATE 68
CHANGED_USER VARCHAR2(50) 69
CHANGED_DATE DATE 70
INFO VARCHAR2(255) 71
CREATE_EMPTIES VARCHAR2(1) 72
NUM_ADV_NOTES NUMBER(4,0) 73
AETC_REF VARCHAR2(20) 74
DEL_POINT_REF VARCHAR2(20) 75
DROP_COUNT NUMBER(8,0) 76
COLLECTED_BY VARCHAR2(1) 77
DELIVERED_BY VARCHAR2(1) 78
POC VARCHAR2(1) 79
POD VARCHAR2(1) 80
ORDER_COMMENTS VARCHAR2(2000) 81
LANE_COMMENTS VARCHAR2(2000) 82
SCHED_DATE DATE 83
BOOKING_REF VARCHAR2(20) 84
SAVING NUMBER(20,2) 85
LATE_ORDER VARCHAR2(1) 86
MANUAL_ORDER VARCHAR2(1) 87
URGENT_ORDER VARCHAR2(1) 88
SERVICE_TYPE VARCHAR2(20) 89
SOURCE_REF VARCHAR2(50) 90
CURRENT_DEPOT VARCHAR2(12) 91
CCY_CODE VARCHAR2(3) 92
CONTAINER_NO VARCHAR2(50) 93
BILL_OF_LADING VARCHAR2(50) 94
TOTAL_CASES NUMBER(8,0) 95
NUM_ORDER_LINES NUMBER(22,0) 96
SPECIAL_INSTRUCTIONS VARCHAR2(4000) 97
BOOKED_IN VARCHAR2(1) 98
STD_COST_DATA VARCHAR2(2000) 99
ORD_REV_DATA VARCHAR2(2000) 100
INTERNAL_CHARGE_DATA VARCHAR2(2000) 101
ACTUAL_DELIVERED_PALLETS NUMBER(24,4) 102
ACTUAL_DELIVERED_PIECES NUMBER(24,4) 103
TOTAL_ACTUAL_DELIVERED_RPE_QTY NUMBER(8,2) 104
ACTUAL_DESPATCHED_PALLETS NUMBER(24,4) 105
ACTUAL_DESPATCHED_PIECES NUMBER(24,4) 106
TOTAL_ACTUAL_DESPTCHD_RPE_QTY NUMBER(8,2) 107
BOOKING_SEQ NUMBER(22,0) 108
TRANSHIP_IND VARCHAR2(1) 109
UOM VARCHAR2(3) 110
ORIGINAL_QTY NUMBER(22,0) 111
EXC_FIXED_ROUTES VARCHAR2(1) 112
DEL_PRIORITY VARCHAR2(2) 113
HANDLING_CODE1 VARCHAR2(2) 114
HANDLING_CODE2 VARCHAR2(2) 115
HANDLING_CODE3 VARCHAR2(2) 116
CARRIER_CODE VARCHAR2(3) 117
INSP_REQ VARCHAR2(2) 118
TEST_REQ VARCHAR2(2) 119
SAP_PRODUCT_ITEM VARCHAR2(18) 120
WAREHOUSE_CODE VARCHAR2(3) 121
STORAGE_CODE VARCHAR2(3) 122
EFX_REF VARCHAR2(50) 123
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_ORD_NON_CONFORM OMS_REF VARCHAR2(12) 1
ACTIVITY VARCHAR2(20) 2
REASON_CODE VARCHAR2(12) 3
COMMENTS VARCHAR2(256) 4
NON_CONFORM_ID NUMBER 5
TRIP_ID VARCHAR2(12) 6
SCHED_NAME VARCHAR2(12) 7
CREATED_DATE DATE 8
CREATED_BY VARCHAR2(50) 9
UPDATED_DATE DATE 10
UPDATED_BY VARCHAR2(50) 11
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_ORDER_LINE ORDER_LINE_ID NUMBER(20,0) 1
OMS_REF VARCHAR2(12) 2
LINE_NO NUMBER(8,0) 3
PRODUCT_TYPE VARCHAR2(12) 4
DU_TYPE VARCHAR2(12) 5
QUANTITY NUMBER(24,4) 6
WEIGHT NUMBER(20,2) 7
CUBE NUMBER(20,5) 8
ACTUAL_QUANTITY NUMBER(24,4) 9
ACTUAL_WEIGHT NUMBER(20,2) 10
ACTUAL_CUBE NUMBER(12,5) 11
ACTUAL_RPE_QTY NUMBER(8,2) 12
EFFECTIVE_WEIGHT NUMBER(20,2) 13
ACTUAL_EFFECTIVE_WEIGHT NUMBER(20,2) 14
SCH_PROD_SUM_ID NUMBER(20,0) 15
ORIGINAL_DEL_DATE DATE 16
SPECIAL VARCHAR2(12) 17
RPE_QTY NUMBER(8,2) 18
ACTUAL_CASES NUMBER(6,0) 19
UPDATED_BY VARCHAR2(40) 20
UPDATED_DATE DATE 21
CREATED_BY VARCHAR2(40) 22
CREATED_DATE DATE 23
CASES NUMBER(8,0) 24
ACTUAL_DELIVERED_QUANTITY NUMBER(24,4) 25
ACTUAL_DELIVERED_RPE_QTY NUMBER(24,4) 26
ACTUAL_DESPATCHED_QUANTITY NUMBER(24,4) 27
ACTUAL_DESPATCHED_RPE_QTY NUMBER(24,4) 28
MEDIA_RETURNS_DU_TYPE VARCHAR2(12) 29
MEDIA_RETURNS_QUANTITY NUMBER(24,4) 30
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ----------------------------- ---------------- -----------
SCH_ORD_ITEMS OMS_REF VARCHAR2(48) 1
CUSTOMER VARCHAR2(48) 2
EXTERNAL_REF VARCHAR2(80) 3
PROD_TYPE_ID VARCHAR2(48) 4
ITEM_IDENTIFIER VARCHAR2(80) 5
ITEM_AKA_CODE VARCHAR2(120) 6
ITEM_DESCRIPTION VARCHAR2(488) 7
ITEM_FACTOR VARCHAR2(48) 8
LIFTS NUMBER(8,2) 9
STACK NUMBER(3,0) 10
QTY_ORDERED NUMBER(8,0) 11
QTY_TO_DELIVER NUMBER(8,0) 12
QTY_DELIVERED NUMBER(8,0) 13
WEIGHT NUMBER(20,2) 14
VOLUME NUMBER(20,4) 15
CREATED_DATE DATE 16
CREATED_BY VARCHAR2(160) 17
UPDATED_DATE DATE 18
UPDATED_BY VARCHAR2(160) 19
DU_TYPE VARCHAR2(48) 20
CURRENT_LOCATION VARCHAR2(48) 21
SAP_LINE_NO NUMBER(12,0) 22
DAMAGED_FLAG VARCHAR2(4) 23
ALTERNATIVE_ITEM_ID VARCHAR2(80) 24
PALLET_ID VARCHAR2(80) 25
ORDER_ITEM_ID NUMBER(20,0) 26
HEIGHT NUMBER(5,2) 27
WIDTH NUMBER(5,2) 28
LENGTH NUMBER(5,2) 29
CLASS VARCHAR2(400) 30
DG VARCHAR2(4) 31
ACTUAL_LENGTH NUMBER(5,2) 32
ACTUAL_WIDTH NUMBER(5,2) 33
ACTUAL_HEIGHT NUMBER(5,2) 34
ACTUAL_WEIGHT NUMBER(20,2) 35
ACTUAL_DIMENSIONAL_WEIGHT NUMBER(20,2) 36
CONTRACTUAL_WEIGHT NUMBER(20,2) 37
DIMENSIONAL_WEIGHT NUMBER(20,2) 38
UNITS NUMBER(20,0) 39
COMMODITY VARCHAR2(200) 40
GEL_PACK_QTY NUMBER(8,0) 41
DRY_ICE_QTY NUMBER(10,2) 42
EQ VARCHAR2(4) 43
PARENT_ITEM_ID VARCHAR2(80) 44
ITEM_LONG_DESCRIPTION VARCHAR2(4000) 45
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ----------------------------- ---------------- -----------
SCH_ORD_ITEMS_REASONS OMS_REF VARCHAR2(48) 1
CUSTOMER VARCHAR2(48) 2
EXTERNAL_REF VARCHAR2(80) 3
PROD_TYPE_ID VARCHAR2(48) 4
ITEM_IDENTIFIER VARCHAR2(80) 5
REASON_CODE VARCHAR2(48) 6
QTY NUMBER(8,0) 7
REASON_COMMENTS VARCHAR2(800) 8
CREATED_DATE DATE 9
CREATED_BY VARCHAR2(160) 10
UPDATED_DATE DATE 11
UPDATED_BY VARCHAR2(160) 12
DEPOT VARCHAR2(48) 13
TRIP_ID VARCHAR2(48) 14
SCAN_TYPE VARCHAR2(80) 15
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_PRODUCT_SUMMARY SOURCE VARCHAR2(12) 1
DESTINATION VARCHAR2(12) 2
DELIVERY_DATE DATE 3
PROD_TYPE_ID VARCHAR2(12) 4
DU_TYPE VARCHAR2(12) 5
SUM_CASES NUMBER(20,0) 6
LEFTOFF_QTY NUMBER(24,4) 7
LEFTOFF_PLANNED NUMBER(24,4) 8
SUM_QTY NUMBER(24,4) 9
ADJ_SUM_QTY NUMBER(24,4) 10
PREV_ADV_QTY NUMBER(24,4) 11
PLANNED_QTY NUMBER(24,4) 12
VARIANCE_QTY NUMBER(24,4) 13
ADVANCE_QTY NUMBER(24,4) 14
ADV_PLANNED NUMBER(24,4) 15
ACTUAL_QTY NUMBER(24,4) 16
SCH_PROD_SUM_ID NUMBER(20,0) 17
LOG 18
ORIG_SUM_QTY NUMBER(24,4) 19
GROUP_NAME VARCHAR2(12) 20
CREATED_BY VARCHAR2(40) 21
CREATED_DATE DATE 22
UPDATED_BY VARCHAR2(40) 23
UPDATED_DATE DATE 24
SCHED_NAME VARCHAR2(12) 25
TRANSHIP_IND VARCHAR2(1) 26
BOOKING_REF VARCHAR2(20) 27
UOM VARCHAR2(3) 28
ORIGINAL_QTY NUMBER(22,0) 29
STATUS VARCHAR2(20) 30
WEIGHT NUMBER(12,2) 31
VOLUME NUMBER(20,5) 32
SOURCE_SYSTEM VARCHAR2(12) 33
DEL_PRIORITY VARCHAR2(2) 34
HANDLING_CODE1 VARCHAR2(2) 35
HANDLING_CODE2 VARCHAR2(2) 36
HANDLING_CODE3 VARCHAR2(2) 37
CARRIER_CODE VARCHAR2(3) 38
INSP_REQ VARCHAR2(2) 39
TEST_REQ VARCHAR2(2) 40
SAP_PRODUCT_ITEM VARCHAR2(18) 41
WAREHOUSE_CODE VARCHAR2(3) 42
STORAGE_CODE VARCHAR2(3) 43
ORIG_PROD_TYPE VARCHAR2(12) 44
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_REASON_CODE REASON_CODE VARCHAR2(12) 1
USAGE VARCHAR2(12) 2
DESCRIPTION VARCHAR2(35) 3
CREATED_BY VARCHAR2(40) 4
CREATED_DATE DATE 5
UPDATED_BY VARCHAR2(40) 6
UPDATED_DATE DATE 7
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_SCHEDULE SCHED_NAME VARCHAR2(12) 1
COST_CENTRE VARCHAR2(12) 2
SCHED_STATUS VARCHAR2(12) 3
UPDATES_PENDING VARCHAR2(1) 4
SCHED_START DATE 5
SCHED_END DATE 6
LOCKED_BY VARCHAR2(35) 7
CREATED_BY VARCHAR2(40) 8
CREATED_DATE DATE 9
UPDATED_BY VARCHAR2(40) 10
UPDATED_DATE DATE 11
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_TRIP SCHED_NAME VARCHAR2(12) 1
TRIP_ID VARCHAR2(12) 2
TRIP_STATUS VARCHAR2(12) 3
EXT_REF VARCHAR2(50) 4
EXT_SHIP_DATE DATE 5
COST_CENTRE VARCHAR2(12) 6
CARRIER_ID VARCHAR2(12) 7
OWNING_DEPOT VARCHAR2(12) 8
HU_ID NUMBER(10,0) 9
DRIVER_ID NUMBER(10,0) 10
TRIP_COST NUMBER(20,2) 11
ALLOCABLE_COST NUMBER(20,2) 12
TRIP_COST_CCY VARCHAR2(3) 13
TRIP_COST_UPDATED DATE 14
COST_VAT NUMBER(20,2) 15
TRIP_REVENUE NUMBER(20,2) 16
TRIP_REVENUE_CCY VARCHAR2(3) 17
TRIP_REVENUE_UPDATED DATE 18
REVENUE_VAT NUMBER(20,2) 19
MTM_COST NUMBER(20,2) 20
TRIP_STD_COST NUMBER(20,2) 21
PAYMENT_REF VARCHAR2(50) 22
DISTANCE NUMBER(12,2) 23
SOURCE_SYSTEM VARCHAR2(12) 24
LAST_ERROR VARCHAR2(256) 25
TRAILER_TYPE VARCHAR2(12) 26
PROFIT NUMBER(20,2) 27
MARGIN NUMBER(8,2) 28
TRAILER_ID VARCHAR2(50) 29
TRACTOR_ID VARCHAR2(12) 30
BILL_OF_LADING VARCHAR2(12) 31
START_TIME DATE 32
END_TIME DATE 33
ELAPSED_TIME NUMBER(5,0) 34
DRIVE_TIME NUMBER(5,0) 35
MANUAL_MOD VARCHAR2(1) 36
LAST_UPDATED DATE 37
SEAL_NO NUMBER(35,0) 38
ODO_START NUMBER(7,0) 39
ODO_END NUMBER(7,0) 40
FUEL_DRAWN NUMBER(7,1) 41
MAX_SHIFT_HOURS_BREACH VARCHAR2(1) 42
MAX_DRIVING_HOURS_BREACH VARCHAR2(1) 43
HOURS_BREACH_USERNAME VARCHAR2(40) 44
HOURS_BREACH_DATETIME DATE 45
BREAKS_BREACH VARCHAR2(1) 46
UTILISATION NUMBER(8,2) 47
MTM_PS_CREATED VARCHAR2(1) 48
MTM_FEASIBLE VARCHAR2(1) 49
ROUTE_CODE VARCHAR2(12) 50
FIX_TRAILER VARCHAR2(1) 51
DRIVER_ID2 NUMBER(10,0) 52
DRIVER_ID3 NUMBER(10,0) 53
SVC_TYPE VARCHAR2(12) 54
LAST_INVITATION_DATE DATE 55
LAST_ACCEPTED_DATE DATE 56
LAST_REJECTED_DATE DATE 57
TRIP_AMENDED_FLAG VARCHAR2(1) 58
CTM_CHASER_COUNT NUMBER(5,0) 59
ROUTE_CREATION_DAYNO NUMBER(22,0) 60
SEND_AMENDED_EMAIL VARCHAR2(1) 61
SJC_DATA VARCHAR2(2000) 62
TOTAL_TRIP_WORK NUMBER(5,0) 63
TOTAL_TRIP_BREAK NUMBER(5,0) 64
SHIFT_START DATE 65
SHIFT_END DATE 66
CREATED_DATE DATE 67
CREATED_USER VARCHAR2(50) 68
CARRIER_REF VARCHAR2(25) 69
CCY_CODE VARCHAR2(3) 70
CONTAINER_NO VARCHAR2(50) 71
OWNING_DEPOT_MAN_MOD VARCHAR2(1) 72
SYS_DELETED VARCHAR2(1) 73
PLANNED_ARRIVE_FIRST_STOP DATE 74
PLANNED_ARRIVE_LAST_STOP DATE 75
LAST_ENROUTE_DATE DATE 76
LAST_COMPLETED_DATE DATE 77
OUTBOUND_VEHICLE_FILL NUMBER(5,2) 78
INBOUND_VEHICLE_FILL NUMBER(5,2) 79
TRIP_HAS_COLLECTION VARCHAR2(1) 80
TRIP_EXPIRY_TIME DATE 81
EFX_REF VARCHAR2(50) 82
EFX_SEND_FLAG VARCHAR2(1) 83
TRAILER_ID2 VARCHAR2(50) 84
FULL_LOAD VARCHAR2(10) 85
TABLE_NAME COLUMN_NAME DATA_TYPE_SIZE COLUMN_POSN
-------------------- ------------------------------ ---------------- -----------
SCH_TRIP_STOP STOP_ID NUMBER(22,0) 1
SCHED_NAME VARCHAR2(12) 2
TRIP_ID VARCHAR2(12) 3
STOP_NO NUMBER(13,5) 4
STOP_TYPE VARCHAR2(2) 5
LOCATION_ID VARCHAR2(12) 6
ARRIVE DATE 7
LOAD_START DATE 8
LOAD_COMPLETE DATE 9
DEPART DATE 10
LAYOVER VARCHAR2(6) 11
DISTANCE_FROM_PREV_STOP NUMBER(10,0) 12
DRIVE_TIME_FROM_PREV_STOP NUMBER(10,0) 13
ACTUAL_ARRIVE DATE 14
ACTUAL_DEPART DATE 15
WEIGHT_ON_DEPART NUMBER(20,2) 16
VOLUME_ON_DEPART NUMBER(20,5) 17
RPE_ON_DEPART NUMBER(22,2) 18
TRAILER_ID VARCHAR2(12) 19
FIXED VARCHAR2(1) 20
TRAILER_TYPE VARCHAR2(12) 21
SEAL_NO VARCHAR2(50) 22
DT_CALC_METHOD VARCHAR2(12) 23
RPE_ON_DEPART_ROUND NUMBER(20,0) 24
MANUAL_APPLY_STOPOVER VARCHAR2(1) 25
MANUAL_OVERRIDE_STOPOVER VARCHAR2(1) 26
STOPOVER VARCHAR2(1) 27
WAIT_TIME_MINS NUMBER(10,0) 28
LOAD_TIME_MINS NUMBER(10,0) 29
LAYOVER_MINS NUMBER(10,0) 30
BOOKING_TIME VARCHAR2(25) 31
PICK_BY DATE 32
DU_QTY_ON_DEPART NUMBER(24,4) 33
TRAILER_ID2 VARCHAR2(12) 34
CREATED_BY VARCHAR2(40) 35
CREATED_DATE DATE 36
UPDATED_BY VARCHAR2(40) 37
UPDATED_DATE DATE 38
Extraction
Data is scheduled to run on an Oracle DMBS_JOB that will produce delimited flat files that will be pushed to an agreed 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 SCH_ORDER_ITEMS SCH_ORDER_ITEMS_REASONS
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 agreed columns on the agreed tables.
All tables will contain columns to identify record created date and records updated date.
Empty csv files will be produced if there is no new data to send to the MIS solution.
There is no method to identify when records are deleted. Therefore records removed from CTMS will not be extracted.
