External stored procedures: Units

TMTEXT_UNIT_CREATE

TMTEXT_UNIT_CREATE will create a unit and tie the default UFLUIDS, STDMETER and METERDEF to the new unit.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@DESCRIP

VARCHAR, (60), Optional

Description of the unit

@DOMICILE

VARCHAR, (12), Required

The units home shop

@COSTCTCODE

VARCHAR, (12), Optional

Units Cost Center

@DEPTCODE

VARCHAR, (12), Optional

Company Department a unit belongs to.

@ACTIVCODE

VARCHAR, (12), Optional

Units Activity Delivery, Service, etc.

@DIVISIONCD

VARCHAR, (12), Optional

Company Division a unit belongs to.

@STATUS

VARCHAR, (12), Optional

Whether Unit is Active, In Shop, Reports Only, etc.

@TYPE

VARCHAR, (12), Optional

Unit Type Tractor, Trailer, Retail, etc.

@MAKE

VARCHAR, (12), Optional

Units Make

@MODEL

VARCHAR, (12), Optional

Units Model

@MODELYEAR

SMALLINT, Optional

Units Model Year

@LICENSE

VARCHAR, (12), Optional

Units License

@PARTID

VARCHAR, (24), Optional

Unit Part ID if entered as part

@MFGPARTID

VARCHAR, (24), Optional

Manufacturer’s Identification Number for this Unit.

@SERIALNO

VARCHAR, (24), Optional

Serial Number for unit

@TITLE

VARCHAR, (24), Optional

Units Title

@ENGINE

VARCHAR, (12), Optional

Units Engine

@CAPACITY

VARCHAR, (12), Optional

Units capacity

@WHEELBASE

VARCHAR, (12), Optional

Units Wheelbase

@PRESSURE

VARCHAR, (12), Optional

Units Tire Pressure

@CUSTOMERID

VARCHAR, (12), Optional

Identification number for the customer.

@INSERVICE

DATETIME, Optional

Date the unit was first received

@WARRLIFE1

NUMERIC, (15,6), Optional

Number of days, miles, and so on, that the warranty is good for

@WARRLIFE2

NUMERIC, (15,6), Optional

Number of days, miles, and so on, that the warranty is good for

@WARRLIFE3

NUMERIC, (15,6), Optional

Number of days, miles, and so on, that the warranty is good for

@METERDEF1

INTEGER, Optional

Meter that warranty is measured in Odometer if it is miles, etc.

@METERDEF2

INTEGER, Optional

Meter that warranty is measured in Odometer if it is miles, etc.

@METERDEF3

INTEGER, Optional

Meter that warranty is measured in Odometer if it is miles, etc.

@PARENTMTR

CHAR, (1), Optional

Not currently in use

@PURCHFROM

VARCHAR, (60), Optional

Who unit was purchased from

@PURCHPRICE

NUMERIC, (15,6), Optional

Price paid for unit

@PURCHUOM

VARCHAR, (12), Optional

Currency used for purchase (US dollars, Canadian dollars, etc.)

@DEPRBASE

DATETIME, Optional

Date used for depreciation

@MONTHDEPR

NUMERIC, (15,6), Optional

Depreciation per month

@YEARDEPR

NUMERIC, (15,6), Optional

Depreciation per year

@TOTALDEPR

NUMERIC, (15,6), Optional

Total amount of depreciation

@DEPPERIOD

SMALLINT, Optional

Depreciation per period

@VENDOR

VARCHAR, (12), Optional

Vendor you purchased unit from

@COMPANYUNIT

CHAR, (1), Optional

Is the unit a company unit?

@UNITUSERFLD1

VARCHAR, (12), Optional

Used to track specific information for a unit

@UNITUSERFLD2

VARCHAR, (12), Optional

Used to track specific information for a unit

@UNITUSERFLD3

VARCHAR, (12), Optional

Used to track specific information for a unit

@UNITUSERFLD4

VARCHAR, (12), Optional

Used to track specific information for a unit

@UNITUSERFLD5

VARCHAR, (12), Optional

Used to track specific information for a unit

@UNITUSERFLD6

VARCHAR, (12), Optional

Used to track specific information for a unit

@UNITUSERFLD7

VARCHAR, (12), Optional

Used to track specific information for a unit

@UNITUSERFLD8

VARCHAR, (12), Optional

Used to track specific information for a unit

@ASSETNUM

VARCHAR, (24), Optional

Unit’s Company assigned asset number

@UNITWEIGHT

NUMERIC, (15,6), Optional

How much unit weighs

@PARKFACILITY

VARCHAR, (24), Optional

where the unit is parked when not in service

@PARKFACILITYNAME

VARCHAR, (60), Optional

The name of the parking facility where the vehicle is parked when not in service

@PARKSLOT

VARCHAR(12) Optional

slot number where the vehicle is parked when not in service

@COLOR

VARCHAR(12) Optional

Unit Color

@SALVAGEVALUE

NUMERIC, (15,6), Optional

Amount unit can be salvaged for

@EMPDRVID

INTEGER, Optional

Driver Identification number

@CUSTOMERNAME

VARCHAR, (12), Optional

Customer name associated with unit

@LICENSESTATE

VARCHAR, (12), Optional

State where unit is licensed

@PRICETBLID

INTEGER, Optional

Price Table ID determines rate unit is invoiced at

@COMPANYID

VARCHAR, (12), Optional

Not currently in use will default to TMT

@FLUIDTYPE

VARCHAR, (12), Optional

Type of fluid unit uses

@FLUIDTYPEPRIMARY

CHAR, (1), Optional

Main fluid for unit usually power fluid like Diesel.

@FLUIDTYPEUOM

VARCHAR, (12), Optional

Fluid Unit of Measure for example liter or gallon

@METERDEFID

INTEGER, Optional

Meter Definition Identification number

@METERDEFPRIMARY

CHAR, (1), Optional

Primary Meter for unit

@PMCOMPCDKEY

VARCHAR, (12), Optional

PM Component Codekey indicates the level System (000) Assembly (000-001) for the number of digits that it can be carried out. PMs can not go to the Part level 9 digits

@PMCOMPCODE

VARCHAR, (12), Optional

Component Code that represents a PM. Always starts with 000-

@PMSCHEDTYPE

CHAR, (1), Optional

Is PM independent or dependent

@PMLASTDONE

DATETIME, Optional

Date PM was last done

@MASTERONLY

CHAR, (1), Optional

If set to N it will create unit using standard parameters

@UNITPREFIX

VARCHAR, (12), Optional

Prefix for unit if used

@DED_UNITNUMBER

VARCHAR, (12), Optional

Dedicated unit number if used

TMTEXT_UNIT_CREATE Output Parameters:
@ERRORS — INTEGER

TMTEXT_UNIT_CREATE Example:
DECLARE @UNITID VARCHAR(24), @DESCRIP VARCHAR(60), @DOMICILE VARCHAR(12), @COSTCTCODE VARCHAR(12), @DEPTCODE VARCHAR(12), @ACTIVCODE VARCHAR(12), @DIVISIONCD VARCHAR(12), @STATUS VARCHAR(12), @TYPE VARCHAR(12), @MAKE VARCHAR(12), @MODEL VARCHAR(12), @MODELYEAR smallint, @LICENSE VARCHAR(12), @PARTID VARCHAR(24), @MFGPARTID VARCHAR(24), @SERIALNO VARCHAR(24), @TITLE VARCHAR(24), @ENGINE VARCHAR(12), @CAPACITY VARCHAR(12), @WHEELBASE VARCHAR(12), @PRESSURE VARCHAR(12), @CUSTOMERID VARCHAR(12), @INSERVICE datetime, @WARRLIFE1 NUMERIC(15,6), @WARRLIFE2 NUMERIC(15,6), @WARRLIFE3 NUMERIC(15,6), @METERDEF1 int, @METERDEF2 int, @METERDEF3 int, @PARENTMTR CHAR(1), @PURCHFROM VARCHAR(60), @PURCHPRICE NUMERIC(15,6), @PURCHUOM VARCHAR(12), @DEPRBASE datetime, @MONTHDEPR NUMERIC(15,6), @YEARDEPR NUMERIC(15,6), @TOTALDEPR NUMERIC(15,6), @DEPPERIOD smallint, @VENDOR VARCHAR(12), @COMPANYUNIT CHAR(1), @UNITUSERFLD1 VARCHAR(12), @UNITUSERFLD2 VARCHAR(12), @UNITUSERFLD3 VARCHAR(12), @UNITUSERFLD4 VARCHAR(12), @UNITUSERFLD5 VARCHAR(12), @UNITUSERFLD6 VARCHAR(12), @UNITUSERFLD7 VARCHAR(12), @UNITUSERFLD8 VARCHAR(12), @ASSETNUM VARCHAR(24), @UNITWEIGHT NUMERIC(15,6), @PARKFACILITY VARCHAR(24), @PARKFACILITYNAME VARCHAR(60), @PARKSLOT VARCHAR(12), @COLOR VARCHAR(12), @SALVAGEVALUE NUMERIC(15,6), @EMPDRVID int, @ERRORS int, @CUSTOMERNAME VARCHAR(12), @LICENSESTATE VARCHAR(12), @PRICETBLID int, @COMPANYID VARCHAR(12), @FLUIDTYPE VARCHAR(12), @FLUIDTYPEPRIMARY CHAR(1), @FLUIDTYPEUOM VARCHAR(12), @METERDEFID int, @METERDEFPRIMARY CHAR(1), @PMCOMPCDKEY VARCHAR(12), @PMCOMPCODE VARCHAR(12), @PMSCHEDTYPE CHAR(1), @PMLASTDONE datetime, @MASTERONLY CHAR(1), @UNITPREFIX VARCHAR(12), @DED_UNITNUMBER VARCHAR(12)

 — Set parameter values

EXEC [dbo].[TMTEXT_UNIT_CREATE] @UNITID, @DESCRIP, @DOMICILE, @COSTCTCODE, @DEPTCODE, @ACTIVCODE, @DIVISIONCD, @STATUS, @TYPE, @MAKE, @MODEL, @MODELYEAR, @LICENSE, @PARTID, @MFGPARTID, @SERIALNO, @TITLE, @ENGINE, @CAPACITY, @WHEELBASE, @PRESSURE, @CUSTOMERID, @INSERVICE, @WARRLIFE1, @WARRLIFE2, @WARRLIFE3, @METERDEF1, @METERDEF2, @METERDEF3, @PARENTMTR, @PURCHFROM, @PURCHPRICE, @PURCHUOM, @DEPRBASE, @MONTHDEPR, @YEARDEPR, @TOTALDEPR, @DEPPERIOD, @VENDOR, @COMPANYUNIT, @UNITUSERFLD1, @UNITUSERFLD2, @UNITUSERFLD3, @UNITUSERFLD4, @UNITUSERFLD5, @UNITUSERFLD6, @UNITUSERFLD7, @UNITUSERFLD8, @ASSETNUM, @UNITWEIGHT, @PARKFACILITY, @PARKFACILITYNAME, @PARKSLOT, @COLOR, @SALVAGEVALUE, @EMPDRVID, @ERRORS OUTPUT , @CUSTOMERNAME, @LICENSESTATE, @PRICETBLID, @COMPANYID, @FLUIDTYPE, @FLUIDTYPEPRIMARY, @FLUIDTYPEUOM, @METERDEFID, @METERDEFPRIMARY, @PMCOMPCDKEY, @PMCOMPCODE, @PMSCHEDTYPE, @PMLASTDONE, @MASTERONLY, @UNITPREFIX, @DED_UNITNUMBER

TMWAMSEXT_UNIT_COST_DIVISION_UPD

TMWAMSEXT_UNIT_COST_DIVISION_UPDATE is an external stored procedure that allows a unit’s Cost Center, Department, Division, Domicile Shop, User Field 7 and User Field 8 to be updated.

The stored procedure will update the unit file as follows:

  • If Cost Center or Division Code changes it will update the Unit ID master

  • If no changes to the code, master will not be updated.

  • If the unit does not exist, it will NOT add the unit

The stored procedure can return one of the following errors:

  • Invalid Cost Center: Error 513

  • Invalid Division: Error 570

  • Invalid Department: Error 569

  • Invalid ShopID (Domicile): Error 512

  • Invalid Unit: Error 517

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERRORID

INTEGER

Error ID supplied if an invalid parameter was entered, such as an invalid Cost Center

@UNITNUMBER

VARCHAR, (24)

The Unit ID displayed in Masters Units

@COSTCTCODE

VARCHAR, (12)

The Unit’s Cost Center

@DEPTCODE

VARCHAR, (12)

The Unit’s Department Code

@DOMICILE

VARCHAR, (12)

The Unit’s Domicile Shop

@DIVISION

VARCHAR, (12)

The Unit’s Division

@UNITUSERFLD7

VARCHAR, (12)

Used to track specific information for a unit

@UNITUSERFLD8

VARCHAR, (12)

Used to track specific information for a unit

TMWAMSEXT_UNIT_COST_DIVISION_UPD Example:
DECLARE @ERRORID INT, @UNITNUMBER VARCHAR(24), @COSTCTCODE VARCHAR(12), @DEPTCODE VARCHAR(12), @DOMICILE VARCHAR(12), @DIVISIONCD VARCHAR(12), @UNITUSERFLD7 VARCHAR(12), @UNITUSERFLD8 VARCHAR(12)

 — Set parameter values
SET @UNITNUMBER = '1010'
SET @COSTCTCODE = '1-2-01-1'
SET @DEPTCODE = 'MAINT'
SET @ DOMICILE = ’01’
SET @ DIVISIONCD = 'SOUTHERNOPS'
SET @UNITUSERFLD7 = 'DAY CAB'
SET @UNITUSERFLD8 = 'YELLOWSTRIPE'

EXEC [dbo].[TMWAMSEXT_UNIT_COST_DIVISION_UPD] @ERRORID OUTPUT, @UNITNUMBER, @COSTCTCODE, @DEPTCODE, @DOMICILE, @DIVISIONCD, @UNITUSERFLD7, @UNITUSERFLD8

 — Return the error code
SELECT @ERRORID

TMTEXT_UNIT_FUEL_UTILIZATION

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@METERTYPE

VARCHAR, (12), Required

The type of meter used Odometer, Hub, etc.

TMTEXT_UNIT_FUEL_UTILIZATION Output Parameters:
@UNITMPG — NUMERIC(15,6)

TMTEXT_UNIT_FUEL_UTILIZATION Example:
DECLARE @UNITID VARCHAR(24), @METERTYPE VARCHAR(12), @UNITMPG NUMERIC(15,6)

 — Set parameter values
SET @UNITID = '1010'
SET @METERTYPE = 'ODOMETER'

EXEC [dbo].[TMTEXT_UNIT_FUEL_UTILIZATION] @UNITID, @METERTYPE, @UNITMPG OUTPUT

SELECT @UNITMPG

TMTEXT_UNIT_STD_CREATE

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERROR

INT

Error ID supplied if an invalid parameter was entered

@UNITID

VARCHAR (12)

The Unit ID displayed in Masters Units

TMTEXT_UNIT_STD_CREATE Example:
DECLARE @UNITID VARCHAR(12), @ERROR INT

 — Set parameter values

EXECUTE @RC = [TFW1230v2].[dbo].[TMTEXT_UNIT_STD_CREATE] @UNITID, @ERROR OUTPUT

 — Return the error code
SELECT @ERROR

TMTEXT_UNIT_UTILIZATION

TMTEXT_UNIT_UTILIZATION will generate TOTALCOST, UNITUTIL, LASTRDING (meter) and UNITMPG for a given Unit and Meter.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@METERTYPE

VARCHAR, (12), Required

The type of meter used Odometer, Hub, etc.

@CUSTOMERNAME

VARCHAR, (12), Required

Customer name associated with unit

TMTEXT_UNIT_UTILIZATION Output Parameters:
@UNITUTIL — NUMERIC (15,6)
@TOTALCOST — NUMERIC (15,6)
@LASTRDING — NUMERIC (15,6)

TMTEXT_UNIT_UTILIZATION Example:
DECLARE @UNITID VARCHAR(24), @METERTYPE VARCHAR(12), @UNITUTIL NUMERIC(15,6), @TOTALCOST NUMERIC(15,6), @LASTRDING NUMERIC(15,6), @CUSTOMERNAME VARCHAR(12)

--Set parameter values
SET @UNITID = '1010'
SET @METERTYPE = 'ODOMETER'

EXEC [dbo].[TMTEXT_UNIT_UTILIZATION] @UNITID, @METERTYPE, @UNITUTIL OUTPUT, @TOTALCOST OUTPUT, @LASTRDING OUTPUT

SELECT @UNITUTIL,@TOTALCOST,@LASTRDING

TMWAMSEXT_UNIT_REPAIRSTATUS_GET

TMWAMSEXT_UNIT_REPAIRSTATUS_GET will return the following information on any unit with a repair order in OPEN status: OrderNum, UnitNumber, CustomerName, Shop, VendorNumber, VendorName, VendorAddress1, VendorAddress2, VendorCity, VendorState, VendorZipCode, RepType, RepStatus, DownDate, ETA, UnitID, OrderID, CallUserFld1, CmponentStr, and SystemComment.

Field Field Description

ORDERNUM

The repair order number.

UNITNUMBER

The unit number.

CUSTOMERNAME

If the unit is associated with a customer, the customer’s number.

SHOP

The repair shop number.

VENDORNUMBER

If the repair order is a VENDOR RO, the vendor number.

VENDORNAME

If the repair order is a VENDOR RO, the vendor’s name.

VENDORADDRESS1

If the repair order is a VENDOR RO, the vendor’s address 1 information.

VENDORADDRESS2

If the repair order is a VENDOR RO, the vendor’s address 2 information.

VENDORCITY

If the repair order is a VENDOR RO, the vendor’s city.

VENDORSTATE

If the repair order is a VENDOR RO, the vendor’s state.

VENDORZIPCODE

If the repair order is a VENDOR RO, the vendor’s zip code.

REPTYPE

Will always return REPAIR

REPSTATUS

Will always return IN-SHOP

DOWNDATE

The opened date of the repair order

ETA

If the Repair Order has a Promised By Date, that date will be displayed. If the Repair Order does not have a Promised by Date but is associated with a plan, the Estimated Date Out will be displayed. If neither date has a value, the field will be null.

UNITID

Number TMT Fleet Maintenance assigns to a unit to track it. This number is used by the database.

ORDERID

Number TMT Fleet Maintenance assigns to an order to track it. This number is used by the database.

CALLUSERFLD1

CMPONENTSTR

Will display a comma separated description list of all the components used on each section of the repair order.

SYSTEMCOMMENT

Will display all section comments and any line level comments.
Example:

SECTION NUMBER: 1
Exhaust is not working correctly.

SECTION NUMBER: 2
;

LINE TYPE: COMMENT
Repair Exhaust

If an integration of 'UNTREPSTAT_GET' is created in the Integration Configuration with Road Call shops, the comments here will be from the Road Call instead of the Repair Order for any Repair Orders created from a Road Call.

Example:

Truck Stuck side of Road.
Added By: JSMITH 8/12/2015 2:52:22 PM

Called wrecker service.
Added By: JSMITH 8/12/2015 2:52:29 PM

Code to set the 'UNTREPSTAT_GET' is as follows:
DECLARE @INTCONFID int, @ERRORCODE int, @INTID int, @INTMAPTYPE CHAR(1), @INTMAPPING VARCHAR(24), @INTSECTION VARCHAR(24), @INTKEY VARCHAR(60), @INTVALUE VARCHAR(1000), @INTDATID int, @MODIFIEDBY VARCHAR(40);

 — Set parameter values here.
SELECT @INTID = [dbo].[TMT_INTEGRATIONID]('TMWAMS')
SET @INTMAPTYPE = 'C'
SET @INTMAPPING = 'TMT'
SET @INTSECTION = 'TMT Fleet Maintenance'
SET @INTKEY = 'UNTREPSTAT_GET'

 — List of Road Call Shops
SET @INTVALUE = '05;01;02;03'

EXECUTE [dbo].[USP_INTEGRATION_CONF_CREATE] @INTCONFID OUTPUT, @ERRORCODE OUTPUT, @INTID, @INTMAPTYPE, @INTMAPPING, @INTSECTION, @INTKEY, @INTVALUE, @INTDATID, @MODIFIEDBY

TMWAMSEXT_UNIT_REPAIRSTATUS_GET Example:

EXEC dbo.[TMWAMSEXT_UNIT_REPAIRSTATUS_GET]

TMWAMSEXT_UNIT_UPDATE_CREATE

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERRORID

INT

@UNITNUMBER

VARCHAR (24)

The Unit ID displayed in Masters Units

@DESCRIP

VARCHAR (60)

Description of the unit

@DOMICILE

VARCHAR (12)

The units home shop

@COSTCTCODE

VARCHAR (12)

Units Cost Center

@DEPTCODE

VARCHAR (12)

Company Department a unit belongs to.

@ACTIVCODE

VARCHAR (12)

Units Activity Delivery, Service, etc.

@DIVISIONCD

VARCHAR (12)

Company Division a unit belongs to.

@STATUS

VARCHAR (12)

Whether Unit is Active, In Shop, Reports Only, etc.

@TYPE

VARCHAR (12)

Unit Type Tractor, Trailer, Retail, etc.

@MAKE

VARCHAR (12)

Units Make

@MODEL

VARCHAR (12)

Units Model

@MODELYEAR

SMALLINT

Units Model Year

@LICENSE

VARCHAR (12)

Units License

@PARTID

VARCHAR (24)

Unit Part ID if entered as part

@MFGPARTID

VARCHAR (24)

Manufacturer’s Identification Number for this Unit.

@SERIALNO

VARCHAR (24)

Serial Number for unit

@TITLE

VARCHAR (24)

Units Title

@ENGINE

VARCHAR (12)

Units Engine

@CAPACITY

VARCHAR (12)

Units capacity

@WHEELBASE

VARCHAR (12)

Units Wheelbase

@PRESSURE

VARCHAR (12)

Units Tire Pressure

@CUSTOMERID

VARCHAR (12)

Identification number for the customer.

@INSERVICE

DATETIME

Date the unit was first received

@WARRLIFE1

NUMERIC (15,6)

Number of days, miles, and so on, that the warranty is good for

@WARRLIFE2

NUMERIC (15,6)

Number of days, miles, and so on, that the warranty is good for

@WARRLIFE3

NUMERIC (15,6)

Number of days, miles, and so on, that the warranty is good for

@METERDEF1

INT

Meter that warranty is measured in Odometer if it is miles, etc.

@METERDEF2

INT

Meter that warranty is measured in Odometer if it is miles, etc.

@METERDEF3

INT

Meter that warranty is measured in Odometer if it is miles, etc.

@PARENTMTR

CHAR (1)

Not currently in use

@PURCHFROM

VARCHAR (60)

Who unit was purchased from

@PURCHPRICE

NUMERIC (15,6)

Price paid for unit

@PURCHUOM

VARCHAR (12)

Currency used for purchase (US dollars, Canadian dollars, etc.)

@DEPRBASE

DATETIME

Date used for depreciation

@MONTHDEPR

NUMERIC (15,6)

Depreciation per month

@YEARDEPR

NUMERIC (15,6)

Depreciation per year

@TOTALDEPR

NUMERIC (15,6)

Total amount of depreciation

@DEPPERIOD

SMALLINT

Depreciation per period

@VENDOR

VARCHAR (12)

Vendor you purchased unit from

@COMPANYUNIT

CHAR (1)

Is the unit a company unit?

@UNITUSERFLD1

VARCHAR (12)

Used to track specific information for a unit

@UNITUSERFLD2

VARCHAR (12)

Used to track specific information for a unit

@UNITUSERFLD3

VARCHAR (12)

Used to track specific information for a unit

@UNITUSERFLD4

VARCHAR (12)

Used to track specific information for a unit

@UNITUSERFLD5

VARCHAR (12)

Used to track specific information for a unit

@UNITUSERFLD6

VARCHAR (12)

Used to track specific information for a unit

@UNITUSERFLD7

VARCHAR (12)

Used to track specific information for a unit

@UNITUSERFLD8

VARCHAR (12)

Used to track specific information for a unit

@ASSETNUM

VARCHAR (24)

Unit’s Company assigned asset number

@UNITWEIGHT

NUMERIC (15,6)

How much unit weighs

@PARKFACILITY

VARCHAR (24)

where the unit is parked when not in service

@PARKFACILITYNAME

VARCHAR (60)

The name of the parking facility where the vehicle is parked when not in service

@PARKSLOT

VARCHAR (12)

slot number where the vehicle is parked when not in service

@COLOR

VARCHAR (12)

Unit Color

@SALVAGEVALUE

NUMERIC (15,6)

Amount unit can be salvaged for

@EMPDRVID

INT

Driver Identification number

@CUSTOMERNAME

VARCHAR (12)

Customer name associated with unit

@LICENSESTATE

VARCHAR (12)

State where unit is licensed

@PRICETBLI

INT

Price Table ID determines rate unit is invoiced at

@COMPANYID

VARCHAR (12)

Not currently in use will default to TMT

@FLUIDTYPE

VARCHAR (12)

Type of fluid unit uses

@FLUIDTYPEPRIMARY

CHAR (1)

Main fluid for unit usually power fluid like Diesel.

@FLUIDTYPEUOM

VARCHAR (12)

Fluid Unit of Measure for example liter or gallon

@METERDEFID

INT

Meter Definition Identification number

@METERDEFPRIMARY

CHAR (1)

Primary Meter for unit

@PMCOMPCDKEY

VARCHAR (12)

PM Component Codekey indicates the level System (000) Assembly (000-001) for the number of digits that it can be carried out. PMs can not go to the Part level 9 digits

@PMCOMPCODE

VARCHAR (12)

Component Code that represents a PM. Always starts with 000-

@PMSCHEDTYPE

CHAR (1)

Is PM independent or dependent

@PMLASTDONE

DATETIME

Date PM was last done

@MASTERONLY

CHAR (1)

If set to N it will create unit using standard parameters

@UNITPREFIX

VARCHAR (12)

Prefix for unit if used

@DED_UNITNUMBER

VARCHAR (12)

Dedicated unit number if used

@PHYLOCATION

VARCHAR (12)

The Physical location of unit as found on Masters > Units > Misc

@PHYSHOPLOCATION

VARCHAR (12)

Current Shop for unit as found on Masters > Units > Misc

@REPAIRLIMIT

NUMERIC (15,6)

The dollar limit assigned to the unit that can be used for repairs

@FLEETID

INT

ID used to identify Year, Make, Model, Capacity, Unit Type, Engine & wheelbase of Unit. Any combination of these can be used.

TMWAMSEXT_UNIT_UPDATE_CREATE Example:
DECLARE @ERRORID INT, @UNITNUMBER VARCHAR(24), @DESCRIP VARCHAR(60), @DOMICILE VARCHAR(12), @COSTCTCODE VARCHAR(12), @DEPTCODE VARCHAR(12), @ACTIVCODE VARCHAR(12), @DIVISIONCD VARCHAR(12), @STATUS VARCHAR(12), @TYPE VARCHAR(12), @MAKE VARCHAR(12), @MODEL VARCHAR(12), @MODELYEAR SMALLINT, @LICENSE VARCHAR(12), @PARTID VARCHAR(24), @MFGPARTID VARCHAR(24), @SERIALNO VARCHAR(24), @TITLE VARCHAR(24), @ENGINE VARCHAR(12), @CAPACITY VARCHAR(12), @WHEELBASE VARCHAR(12), @PRESSURE VARCHAR(12), @CUSTOMERID VARCHAR(12), @INSERVICE DATETIME, @WARRLIFE1 NUMERIC(15,6), @WARRLIFE2 NUMERIC(15,6), @WARRLIFE3 NUMERIC(15,6), @METERDEF1 INT, @METERDEF2 INT, @METERDEF3 INT, @PARENTMTR CHAR(1), @PURCHFROM VARCHAR(60), @PURCHPRICE NUMERIC(15,6), @PURCHUOM VARCHAR(12), @DEPRBASE DATETIME, @MONTHDEPR NUMERIC(15,6), @YEARDEPR NUMERIC(15,6), @TOTALDEPR NUMERIC(15,6), @DEPPERIOD SMALLINT, @VENDOR VARCHAR(12), @COMPANYUNIT CHAR(1), @UNITUSERFLD1 VARCHAR(12), @UNITUSERFLD2 VARCHAR(12), @UNITUSERFLD3 VARCHAR(12), @UNITUSERFLD4 VARCHAR(12), @UNITUSERFLD5 VARCHAR(12), @UNITUSERFLD6 VARCHAR(12), @UNITUSERFLD7 VARCHAR(12), @UNITUSERFLD8 VARCHAR(12), @ASSETNUM VARCHAR(24), @UNITWEIGHT NUMERIC(15,6), @PARKFACILITY VARCHAR(24), @PARKFACILITYNAME VARCHAR(60), @PARKSLOT VARCHAR(12), @COLOR VARCHAR(12), @SALVAGEVALUE NUMERIC(15,6), @EMPDRVID INT, @CUSTOMERNAME VARCHAR(12), @LICENSESTATE VARCHAR(12), @PRICETBLID INT, @COMPANYID VARCHAR(12), @FLUIDTYPE VARCHAR(12), @FLUIDTYPEPRIMARY CHAR(1), @FLUIDTYPEUOM VARCHAR(12), @METERDEFID INT, @METERDEFPRIMARY CHAR(1), @PMCOMPCDKEY VARCHAR(12), @PMCOMPCODE VARCHAR(12), @PMSCHEDTYPE CHAR(1), @PMLASTDONE DATETIME, @MASTERONLY CHAR(1), @UNITPREFIX VARCHAR(12), @DED_UNITNUMBER VARCHAR(12), @PHYLOCATION VARCHAR(12), @PHYSHOPLOCATION VARCHAR(12), @REPAIRLIMIT NUMERIC(15,6), @FLEETID INT

 — Set parameter values

EXECUTE [dbo].[TMWAMSEXT_UNIT_UPDATE_CREATE] ERRORID OUTPUT, @UNITNUMBER, @DESCRIP, @DOMICILE, @COSTCTCODE, @DEPTCODE, @ACTIVCODE, @DIVISIONCD, @STATUS, @TYPE, @MAKE, @MODEL, @MODELYEAR, @LICENSE, @PARTID, @MFGPARTID, @SERIALNO, @TITLE, @ENGINE, @CAPACITY, @WHEELBASE, @PRESSURE, @CUSTOMERID, @INSERVICE, @WARRLIFE1, @WARRLIFE2, @WARRLIFE3, @METERDEF1, @METERDEF2, @METERDEF3, @PARENTMTR, @PURCHFROM, @PURCHPRICE, @PURCHUOM, @DEPRBASE, @MONTHDEPR, @YEARDEPR, @TOTALDEPR, @DEPPERIOD, @VENDOR, @COMPANYUNIT, @UNITUSERFLD1, @UNITUSERFLD2, @UNITUSERFLD3, @UNITUSERFLD4, @UNITUSERFLD5, @UNITUSERFLD6, @UNITUSERFLD7, @UNITUSERFLD8, @ASSETNUM, @UNITWEIGHT, @PARKFACILITY, @PARKFACILITYNAME, @PARKSLOT, @COLOR, @SALVAGEVALUE, @EMPDRVID, @CUSTOMERNAME, @LICENSESTATE, @PRICETBLID, @COMPANYID, @FLUIDTYPE, @FLUIDTYPEPRIMARY, @FLUIDTYPEUOM, @METERDEFID, @METERDEFPRIMARY, @PMCOMPCDKEY, @PMCOMPCODE, @PMSCHEDTYPE, @PMLASTDONE, @MASTERONLY, @UNITPREFIX, @DED_UNITNUMBER, @PHYLOCATION, @PHYSHOPLOCATION, @REPAIRLIMIT, @FLEETID

TMWAMSEXT_UNITCHILD_CREATE

Adds and/or removes unit assignments, for example assigns a trailer to a tractor.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@INTEGRATIONID

INTEGER

Identifies the integration the meter reading came from

@UNITNUMBER

VARCHAR, (24)

The Unit ID displayed in Masters Units

@CHILDUNITNUMBER

VARCHAR, (24)

Number assigned to unit to associate the unit with another unit

@OLDCHILDUNITNUMBER

VARCHAR, (24)

Number previously used to associate a unit with another unit

@EFFECTIVE

DATETIME

Date when new assignment starts

@UNTIL

DATETIME

Date when assignment ends

@CHILDTYPE

VARCHAR, (12)

Whether child is tractor, trailer, reefer, etc

@CREATEDBY

VARCHAR, (40)

Person who created the assignment

@MODIFIEDBY

VARCHAR, (40)

Person who changed the assignment

Output Parameters

@ERROR_CODE

INTEGER

Error message displayed

@UNITCHILDID

INTEGER

Number assigned to unit in database to track the parent child relationship

TMWAMSEXT_UNITCHILD_CREATE Example:
DECLARE @ERRORID INT, @UNITCHILDID INT, @INTEGRATIONID INT, @UNITNUMBER VARCHAR(24), @CHILDUNITNUMBER VARCHAR(24), @OLDCHILDUNITNUMBER VARCHAR(24), @EFFECTIVE DATETIME, @UNTIL DATETIME, @CHILDTYPE VARCHAR(12), @CREATEDBY VARCHAR(40), @MODIFIEDBY VARCHAR(40)

 — Set parameter values
SET @UNITNUMBER = '1010'
SET @CHILDUNITNUMBER = '0020'

EXECUTE [dbo].[TMWAMSEXT_UNITCHILD_CREATE], @ERRORID OUTPUT, @UNITCHILDID OUTPUT, @INTEGRATIONID, @UNITNUMBER, @CHILDUNITNUMBER, @OLDCHILDUNITNUMBER, @EFFECTIVE, @UNTIL, @CHILDTYPE, @CREATEDBY, @MODIFIEDBY

select @ERRORID, @UNITCHILDID

 — Remove a unit Child Example:
DECLARE @ERRORID INT, @UNITCHILDID INT, @INTEGRATIONID INT, @UNITNUMBER VARCHAR(24), @CHILDUNITNUMBER VARCHAR(24), @OLDCHILDUNITNUMBER VARCHAR(24), @EFFECTIVE DATETIME, @UNTIL DATETIME, @CHILDTYPE VARCHAR(12), @CREATEDBY VARCHAR(40), @MODIFIEDBY VARCHAR(40)

SET @UNITNUMBER = '1010'
SET @OLDCHILDUNITNUMBER = '0020' — give Old child
SET @UNTIL = GETDATE() — give Until

EXECUTE [dbo].[TMWAMSEXT_UNITCHILD_CREATE] @ERRORID OUTPUT, @UNITCHILDID OUTPUT, @INTEGRATIONID, @UNITNUMBER, @CHILDUNITNUMBER, @OLDCHILDUNITNUMBER, @EFFECTIVE, @UNTIL, @CHILDTYPE, @CREATEDBY, @MODIFIEDBY

 — Add a new Unit Child and remove a unit child Example:
DECLARE @ERRORID INT, @UNITCHILDID INT, @INTEGRATIONID INT, @UNITNUMBER VARCHAR(24), @CHILDUNITNUMBER VARCHAR(24), @OLDCHILDUNITNUMBER VARCHAR(24), @EFFECTIVE DATETIME, @UNTIL DATETIME, @CHILDTYPE VARCHAR(12), @CREATEDBY VARCHAR(40), @MODIFIEDBY VARCHAR(40)

SET @UNITNUMBER = '1010'
SET @CHILDUNITNUMBER = '0018'
SET @OLDCHILDUNITNUMBER = '0020' — give Old child
SET @UNTIL = GETDATE() — give Until

EXECUTE [dbo].[TMWAMSEXT_UNITCHILD_CREATE] @ERRORID OUTPUT, @UNITCHILDID OUTPUT, @INTEGRATIONID, @UNITNUMBER, @CHILDUNITNUMBER, @OLDCHILDUNITNUMBER, @EFFECTIVE, @UNTIL, @CHILDTYPE, @CREATEDBY, @MODIFIEDBY

select @ERRORID, @UNITCHILDID

Note: If a Unit Child already exists and you do not remove it then the Unit will have X number of Children assigned to that unit.