External stored procedures: Meters/Location

There are two types of meter insert procedures: TMTEXT_METERMAID and TMTEXT_METERMAID_WITHVALIDATE. TMTEXT_METERMAID will create a Meter if there are no errors. TMTEXT_METERMAID_WITHVALIDATE has a parameter named VALIDATEONLY which will be ‘T’/’F’ If you select ‘T’ then the system will just validate the post but will not post the meter. Then the system will have to repost with the call of ‘F’. These can apply against both non-physical and physical meters.

TMTEXT_METERDEF_INSERT

If you receive an error 552 you can create the Meter for the Unit using TMTEXT_METERDEF_INSERT Stored procedure. It is critical at this point to understand what a Physical meter is before creating a new meter in the system, a Non-Physical meter will allow negative readings that reflect in Life to Date Utilization. PMs are based on the Life to date Utilization, if there is a PM that is set up for a thirty thousand-mile interval that PM will be triggered every thirty thousand miles regardless of the total in Life to Date Utilization.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@METERTYPE

VARCHAR, (12), Required

The type of meter used Odometer, Hub, etc.

@DESCRIP

VARCHAR, (60), Required

Description of meter

@PHYSICAL

CHAR, (1), Optional (defaults to N)

Is the meter a physical meter

@METERUOM

VARCHAR, (12), Required

Is meter measuring miles, days, gallons, etc

TMTEXT_METERDEF_INSERT Output Parameters:
@ERRORS — INTEGER

TMTEXT_METERDEF_INSERT Example:
DECLARE @METERTYPE VARCHAR(12), @DESCRIP VARCHAR(60), @PHYSICAL CHAR(1), @METERUOM VARCHAR(12), @ERRORS int

 — Set parameter values

EXEC @RC = [dbo].[TMTEXT_METERDEF_INSERT] @METERTYPE, @DESCRIP, @PHYSICAL, @METERUOM, @ERRORS OUTPUT

 — Return the error code
SELECT @ERRORS

TMTEXT_METERMAID

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@ORDERTYPE

VARCHAR, (12), Optional

Is order RO, PO, Fuel Ticket, Indirect, Campaign, etc.

@ORDERID

INTEGER, Optional

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

@METERTYPE

CHAR, (12), Required

The type of meter used Odometer, Hub, etc.

@METERREADING

INTEGER, Required

The amount of miles, hours, and so on, since the last entry

@METERDATE

DATETIME, Required

The date the last meter was read

@CUSTOMERNAME

VARCHAR, (12), Optional

Customer name associated with unit

@INTEGRATIONID

INTEGER, Optional

Identifies the integration the meter reading came from

TMTEXT_METERMAID Output Parameters:
@ERRORS — INTEGER

TMTEXT_METERMAID Example:
DECLARE @UNITID VARCHAR(24), @ORDERTYPE VARCHAR(12), @ORDERID int, @METERTYPE VARCHAR(12), @METERREADING NUMERIC(15,6), @METERDATE datetime, @ERRORS int, @CUSTOMERNAME VARCHAR(12), @INTEGRATIONID int

 — Set parameter values

EXEC @RC = [dbo].[TMTEXT_METERMAID] @UNITID, @ORDERTYPE, @ORDERID, @METERTYPE, @METERREADING, @METERDATE, @ERRORS OUTPUT, @CUSTOMERNAME, @INTEGRATIONID

 — Return the error code
SELECT @ERRORS

TMTEXT_METERMAID_WITHVALIDATE

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@ORDERTYPE

VARCHAR, (12), Optional

Is order RO, PO, Fuel Ticket, Indirect, Campaign, etc.

@ORDERID

INTEGER, Optional

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

@METERTYPE

CHAR, (12), Required

The type of meter used Odometer, Hub, etc.

@METERREADING

INTEGER, Required

The amount of miles, hours, and so on, since the last entry

@METERDATE

DATETIME, Required

The date the last meter was read

@VALIDATEONLY

CHAR, (1), Optional (defaults to F)

Validates that a meter can be inserted.

@CUSTOMERNAME

VARCHAR, (12), Optional

Customer name associated with unit

@INTEGRATIONID

INTEGER, Optional

Identifies the integration the meter reading came from

TMTEXT_METERMAID_WITHVALIDATE Output Parameters:
@ERRORS — INTEGER

TMTEXT_METERMAID_WITHVALIDATE Example:
DECLARE @UNITID VARCHAR(24), @ORDERTYPE VARCHAR(12), @ORDERID int, @METERTYPE VARCHAR(12), @METERREADING NUMERIC(15,6), @METERDATE datetime, @VALIDATEONLY CHAR(1), @ERRORS int, @CUSTOMERNAME VARCHAR(12), @INTEGRATIONID int

 — Set parameter values

EXEC [dbo].[TMTEXT_METERMAID_WITHVALIDATE] @UNITID, @ORDERTYPE, @ORDERID, @METERTYPE, @METERREADING, @METERDATE, @VALIDATEONLY, @ERRORS OUTPUT, @CUSTOMERNAME, @INTEGRATIONID

 — Return the error code
SELECT @ERRORS

TMTEXT_PHYLOCATION_UPD

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@LOCATION

VARCHAR, (24), Required

Physical location of unit

@SHOPID

VARCHAR, (24), Required

Shop Identification Number

@PARKFACILITYNAME

VARCHAR, (60), Optional

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

@PARKFACILITY

VARCHAR, (24), Optional

where the unit is parked when not in service

@PARKSLOT

VARCHAR, (12), Optional

slot number where the vehicle is parked when not in service

@ASSETNUM

VARCHAR, (24), Optional

Unit’s Company assigned asset number

TMTEXT_PHYLOCATION_UPD Output Parameters:
@ERRORS — INTEGER

TMTEXT_PHYLOCATION_UPD Example:
DECLARE @UNITID VARCHAR(24), @LOCATION VARCHAR(12), @SHOPID VARCHAR(12), @ERRORS int, @PARKFACILITYNAME VARCHAR(60), @PARKFACILITY VARCHAR(24), @PARKSLOT VARCHAR(12), @ASSETNUM VARCHAR(24)

 — Set parameter values

EXEC @RC = [dbo].[TMTEXT_PHYLOCATION_UPD] @UNITID, @LOCATION, @SHOPID, @ERRORS OUTPUT, @PARKFACILITYNAME, @PARKFACILITY, @PARKSLOT, @ASSETNUM

 — Return the error code
SELECT @ERRORS

TMWAMSEXT_UNITLONGLAT_BASE_CREATE

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERRORID

INTEGER

Error ID supplied if an invalid parameter was entered

@UNITLONGLATID

INTEGER

Unit Longitude and Latitude ID so it can be associated with orders, plans and road calls

@UNITNUMBER

VARCHAR(24)

The Unit ID displayed in Masters Units

@CUSTOMERNAME

VARCHAR(12)

Customer name associated with unit

@LONG

NUMERIC(15,6)

The longitude of the unit

@LAT

NUMERIC(15,6)

The latitude of the unit

@INTEGRATIONID

INTEGER

Identifies the integration the reading came from

@MODIFIEDBY

VARCHAR(40)

Who modified the record last

TMWAMSEXT_UNITLONGLAT_BASE_CREATE Output Parameters:
@ERRORID — INTEGER
@UNITLONGLATID — INTEGER

TMWAMSEXT_UNITLONGLAT_BASE_CREATE Example:
DECLARE @ERRORID int, @UNITLONGLATID int, @UNITNUMBER VARCHAR(24), @CUSTOMERNAME VARCHAR(12), @LONG NUMERIC(15,6), @LAT NUMERIC(15,6), @INTEGRATIONID int, @MODIFIEDBY VARCHAR(40)

 — Set parameter values

EXEC [dbo].[TMWAMSEXT_UNITLONGLAT_BASE_CREATE] @ERRORID OUTPUT, @UNITLONGLATID OUTPUT, @UNITNUMBER, @CUSTOMERNAME, @LONG, @LAT, @INTEGRATIONID, @MODIFIEDBY

 — Return the error code
SELECT @ERRORID

TMWAMSEXT_UNITLONGLAT_CREATE

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERRORID

INTEGER

Error ID supplied if an invalid parameter was entered

@UNITLONGLATID

INTEGER

Unit Longitude and Latitude ID so it can be associated with orders, plans and road calls

@UNITNUMBER

VARCHAR(24)

The Unit ID displayed in Masters Units

@CUSTOMERNAME

VARCHAR(12)

Customer name associated with unit

@LONG

NUMERIC(15,6)

The longitude of the unit

@LAT

NUMERIC(15,6)

The latitude of the unit

@CALLID

INTEGER

Number TMT Fleet Maintenance assigns to a Road Call to track it. This number is used in the database

@ORDERID

INTEGER

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

@PLANNERID

INTEGER

Number TMT Fleet Maintenance assigns to a Shop Plan to track it. This number is used in the database

@INTEGRATIONID

INTEGER

Identifies the integration the reading came from

@MODIFIEDBY

VARCHAR(40)

Who modified the record last

TMWAMSEXT_UNITLONGLAT_BASE_CREATE Output Parameters:
@ERRORID — INTEGER
@UNITLONGLATID — INTEGER

TMWAMSEXT_UNITLONGLAT_BASE_CREATE Example:
DECLARE @ERRORID int, @UNITLONGLATID int, @UNITNUMBER VARCHAR(24), @CUSTOMERNAME VARCHAR(12), @LONG NUMERIC(15,6), @LAT NUMERIC(15,6), @CALLID int, @ORDERID int, @PLANNERID int, @INTEGRATIONID int, @MODIFIEDBY VARCHAR(40)

 — Set parameter values

EXEC [dbo].[TMWAMSEXT_UNITLONGLAT_CREATE] @ERRORID OUTPUT, @UNITLONGLATID OUTPUT, @UNITNUMBER, @CUSTOMERNAME, @LONG, @LAT, @CALLID, @ORDERID, @PLANNERID, @INTEGRATIONID, @MODIFIEDBY

 — Return the error code
SELECT @ERRORID