External stored procedures: Driver/Employee

TMTEXT_DRIVER_CREATE

Creates a new Driver.

  • Inserting New Unit - To insert a new unit use the unitnumber and uniteffective parameters. An Until date will not be inserted on the initial insert. If uniteffective is NULL then the current server date will be used.
    @UNITNUMBER = ‘1010’,
    @OLDUNITNUMBER = NULL,
    @UNITEFFECTIVE = ‘4/19/2010’,
    @UNITUNTIL = NULL,

  • Editing Current Unit - For editing the current unit use the following parameters oldunitnumber, uniteffective, and unituntil. Unit number must be NULL, or it will do a replacement and not an update. If the UnitUntil date is left NULL and the UnitEffective date has a value, the effective date will be updated and the unituntil date will be set to the current date. The unituntil date can be changed afterward by including the unituntil date.

    You can also leave the uniteffective date null, and it will just insert the unituntil date. The update is intended for setting the unituntil date only, not for changing the uniteffective date if entered incorrectly.
    @UNITNUMBER = NULL,
    @OLDUNITNUMBER = ‘1010’,
    @UNITEFFECTIVE = ‘4/20/2010’,
    @UNITUNTIL = NULL,
    @UNITNUMBER = NULL,
    @OLDUNITNUMBER = ‘1010’,
    @UNITEFFECTIVE = ‘4/20/2010’,
    @UNITUNTIL = ‘4/20/2011’,

  • Unit Replacement- For a unit replacement the unitnumber will be the new unit, oldunitnumber will be the unit you are replacing. The uniteffective date will apply to unitnumber and oldunitnumber. The uniteffective date will become the untildate of the oldunitnumber and will be set as the uniteffective date of the new unit. In this case 1010’s until date will be 4/25 and 1012A’s effective date will be 4/25

    If the unituntil date will need to be set to something different, an update for unit 1010 can be run, which was shown above.
    @UNITNUMBER = ‘1012A’,
    @OLDUNITNUMBER = ‘1010’,
    @UNITEFFECTIVE = ‘4/25/2010’,
    @UNITUNTIL = NULL,

  • A unit can only be inserted once

  • To update a unit’s until date set the unit number to the oldunitnumber and the unituntil *date to the new date and leave the *unitnumber and uniteffective items null.

  • If you supply only unitnumber and oldunitnumber and leave both the uniteffective and unituntil null it will handle this by setting the unituntil date of old unit to today’s date and uniteffective date of the new unit to today’s date.

  • To swap units by set the current unit as oldunitnumber and the oldnumber to the unitnumber. You can set dates or leaving them null.

  • If a date is null, and the date in the database is not populated it should use today’s date. If there is a date on the unit assignment it won’t be overwritten if the values pushed in are null.

  • TMTEXT_DRIVER_CREATE will check to see if the driver is terminated. If he is it will change his status to TERM and add the termination date.

  • If the driver is not in the database it will add him as TERM if he has a status of terminated.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@DRIVERNUM

VARCHAR, (12), Required

Driver Identification Number

@EMPDRVID

INTEGER, Optional

Employee Driver Identification Number

@ACTIVECODE

CHAR, (1), Required

Is drivers account still active

@UNITNUMBER

VARCHAR, (24), Optional

The Unit ID displayed in Masters Units

@OLDUNITNUMBER

VARCHAR, (24), Optional

When driver started driving this unit

@UNITEFFECTIVE

DATETIME, Optional

When driver started driving this unit

@CUSTOMERNAME

VARCHAR, (12), Optional

Customer name associated with unit

@FIRSTNAME

VARCHAR, (30), Optional

Drivers First Name

@LASTNAME

VARCHAR, (30), Optional

Drivers Last Name

@STATUS

VARCHAR, (12), Optional

Work status of driver

@SHOPID

VARCHAR, (12), Optional

Shop Identification Number

@ADDRESS1

VARCHAR, (35), Optional

Home Address of Driver

@ADDRESS2

VARCHAR, (35), Optional

Home Address of Driver

@CITY

VARCHAR, (20), Optional

Home City of Driver

@STATE

VARCHAR, (12), Optional

Home State of Driver

@ZIPCODE

VARCHAR, (16), Optional

Home Zip Code of Driver

@SSN

VARCHAR, (16), Optional

Drivers Social Security Number

@HIRED

DATETIME, Optional

Drivers date of hire

@TERMED

DATETIME, Optional

Drivers termination date

@HOMEPH

VARCHAR, (20), Optional

Home phone of Driver

@EMERGPH

VARCHAR, (20), Optional

Emergency phone for Driver

@OFFICEPH

VARCHAR, (20), Optional

Office phone for Driver

@OFFICEEXT

CHAR, (6), Optional

Office phone extension for Driver

@PAGERNO

VARCHAR, (20), Optional

Drivers pager number

@CELLPHONE

VARCHAR, (20), Optional

Drivers cell phone number

@EMERGCNTCT

VARCHAR, (40), Optional

Emergency contact for driver

@EMAIL

VARCHAR, (60), Optional

Drivers email address

@EMPCLASS

VARCHAR, (12), Optional

Work Classification

@PAYGRADEID

VARCHAR, (12), Optional

Paygrade

@PRIMSHIFT

VARCHAR, (12), Optional

Primary shift they work

@DRIVERLIC

VARCHAR, (24), Optional

Drivers License Number

@DRVLICCLAS

VARCHAR, (24), Optional

License class CDL, A, B, C

@DRIVERCLASS

VARCHAR, (12), Optional

Not currently in use

@CRDCARDNO

VARCHAR, (24), Optional

Corporate Credit Card Number

@EMPMGRID

INTEGER, Optional

Manager ID

@EMPGREMPID

INTEGER, Optional

Not currently in use

@DEPTCODE

VARCHAR, (12), Optional

Department within the company Division

@DIVISION

VARCHAR, (12), Optional

Division of the company

TMTEXT_DRIVER_CREATE Output Parameters:
@ERROR — INTEGER
@EMPDRVID — INTEGER

TMTEXT_DRIVER_CREATE Example:
DECLARE @ERROR int, @EMPDRVID int, @DRIVERNUM VARCHAR(12), @ACTIVECODE CHAR(1), @UNITNUMBER VARCHAR(24), @OLDUNITNUMBER VARCHAR(24), @UNITEFFECTIVE datetime, @UNITUNTIL datetime, @CUSTOMERNAME VARCHAR(12), @FIRSTNAME VARCHAR(30), @LASTNAME VARCHAR(30), @STATUS VARCHAR(12), @SHOPID VARCHAR(12), @ADDRESS1 VARCHAR(35), @ADDRESS2 VARCHAR(35), @CITY VARCHAR(20), @STATE VARCHAR(12), @ZIPCODE VARCHAR(16), @SSN VARCHAR(16), @HIRED datetime, @TERMED datetime, @HOMEPH VARCHAR(20), @EMERGPH VARCHAR(20), @OFFICEPH VARCHAR(20), @OFFICEEXT CHAR(6), @PAGERNO VARCHAR(20), @CELLPHONE VARCHAR(20), @EMERGCNTCT VARCHAR(40), @EMAIL VARCHAR(60), @EMPCLASS VARCHAR(12), @PAYGRADEID VARCHAR(12), @PRIMSHIFT VARCHAR(12), @DRIVERLIC VARCHAR(24), @DRVLICCLAS VARCHAR(24), @DRIVERCLASS VARCHAR(12), @CRDCARDNO VARCHAR(24), @EMPMGRID int, @EMPGREMPID int, @DEPTCODE VARCHAR(12), @DIVISION VARCHAR(12)

 — Set parameter values
EXEC [dbo].[TMTEXT_DRIVER_CREATE] @ERROR OUTPUT, @EMPDRVID OUTPUT, @DRIVERNUM, @ACTIVECODE, @UNITNUMBER, @OLDUNITNUMBER, @UNITEFFECTIVE, @UNITUNTIL, @CUSTOMERNAME, @FIRSTNAME, @LASTNAME, @STATUS, @SHOPID, @ADDRESS1, @ADDRESS2, @CITY, @STATE, @ZIPCODE, @SSN, @HIRED, @TERMED, @HOMEPH, @EMERGPH, @OFFICEPH, @OFFICEEXT, @PAGERNO, @CELLPHONE, @EMERGCNTCT, @EMAIL, @EMPCLASS, @PAYGRADEID, @PRIMSHIFT, @DRIVERLIC, @DRVLICCLAS, @DRIVERCLASS, @CRDCARDNO, @EMPMGRID, @EMPGREMPID, @DEPTCODE, @DIVISION

 — Return the error code
Select @ERROR

Example 2: Marking a driver Terminated. Added with version 11.20:
DECLARE @ERROR int, @EMPDRVID int, @DRIVERNUM VARCHAR(12), @ACTIVECODE CHAR(1), @UNITNUMBER VARCHAR(24), @OLDUNITNUMBER VARCHAR(24), @UNITEFFECTIVE datetime, @UNITUNTIL datetime, @CUSTOMERNAME VARCHAR(12), @FIRSTNAME VARCHAR(30), @LASTNAME VARCHAR(30), @STATUS VARCHAR(12), @SHOPID VARCHAR(12), @ADDRESS1 VARCHAR(35), @ADDRESS2 VARCHAR(35), @CITY VARCHAR(20), @STATE VARCHAR(12), @ZIPCODE VARCHAR(16), @SSN VARCHAR(16), @HIRED datetime, @TERMED datetime, @HOMEPH VARCHAR(20), @EMERGPH VARCHAR(20), @OFFICEPH VARCHAR(20), @OFFICEEXT CHAR(6), @PAGERNO VARCHAR(20), @CELLPHONE VARCHAR(20), @EMERGCNTCT VARCHAR(40), @EMAIL VARCHAR(60), @EMPCLASS VARCHAR(12), @PAYGRADEID VARCHAR(12), @PRIMSHIFT VARCHAR(12), @DRIVERLIC VARCHAR(24), @DRVLICCLAS VARCHAR(24), @DRIVERCLASS VARCHAR(12), @CRDCARDNO VARCHAR(24), @EMPMGRID int, @EMPGREMPID int, @DEPTCODE VARCHAR(12), @DIVISION VARCHAR(12)

 — Set parameter values
SET @EMPDRVID = 5
SET @TERMED = '01/02/2010'

SELECT @DRIVERNUM = EMPID, @ACTIVECODE = ACTIVECODE, @FIRSTNAME = FIRSTNAME, @LASTNAME = LASTNAME, @STATUS = STATUS, @SHOPID = SHOPID, @ADDRESS1 = ADDRESS1, @ADDRESS2 = ADDRESS2, @CITY = CITY, @STATE = STATE, @ZIPCODE = ZIPCODE, @SSN = SSN, @HIRED = HIRED, @HOMEPH = HOMEPH, @EMERGPH = EMERGPH, @OFFICEPH = OFFICEPH, @OFFICEEXT = OFFICEEXT, @PAGERNO = PAGERNO, @CELLPHONE = CELLPHONE, @EMERGCNTCT = EMERGCNTCT, @EMAIL = EMAIL, @EMPCLASS = EMPCLASS, @PAYGRADEID = PAYGRADEID, @PRIMSHIFT = PRIMSHIFT, @DRIVERLIC = DRIVERLIC, @DRVLICCLAS = DRVLICCLAS, @CRDCARDNO = CRDCARDNO, @EMPMGRID = EMPMGRID, @EMPGREMPID = EMPGREMPID, @DEPTCODE = DEPTCODE, @DIVISION = DIVISION FROM VIEW_DRIVER WHERE EMPDRVID = @EMPDRVID

EXEC [dbo].[TMTEXT_DRIVER_CREATE] @ERROR OUTPUT, @EMPDRVID OUTPUT, @DRIVERNUM, @ACTIVECODE, @UNITNUMBER, @OLDUNITNUMBER, @UNITEFFECTIVE, @UNITUNTIL, @CUSTOMERNAME, @FIRSTNAME, @LASTNAME, @STATUS, @SHOPID, @ADDRESS1, @ADDRESS2, @CITY, @STATE, @ZIPCODE, @SSN, @HIRED, @TERMED, @HOMEPH, @EMERGPH, @OFFICEPH, @OFFICEEXT, @PAGERNO, @CELLPHONE, @EMERGCNTCT, @EMAIL, @EMPCLASS, @PAYGRADEID, @PRIMSHIFT, @DRIVERLIC, @DRVLICCLAS, @DRIVERCLASS, @CRDCARDNO, @EMPMGRID, @EMPGREMPID, @DEPTCODE, @DIVISION

SELECT * FROM VIEW_DRIVER WHERE EMPDRVID = @EMPDRVID

 — Return the error code
Select @ERROR

TMTEXT_EMPUNIT_CREATE

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@DRIVERNUM

VARCHAR, (12), Required

Driver Identification Number

@UNITNUMBER

VARCHAR, (24), Optional

The Unit ID displayed in Masters Units

@OLDUNITNUMBER

VARCHAR, (24), Optional

Previous Unit ID

@UNITEFFECTIVE

DATETIME, Optional

When driver started driving unit

@UNITUNTIL

DATETIME, Optional

When driver stopped driving unit

@CUSTOMERNAME

VARCHAR, (12), Optional

Customer name associated with unit

TMTEXT_EMPUNIT_CREATE Output Parameters:
@EMPUNITID — INTEGER
@ERROR — INTEGER

TMTEXT_EMPUNIT_CREATE Example:
DECLARE @EMPUNITID int, @ERROR int, @DRIVERNUM VARCHAR(12), @UNITNUMBER VARCHAR(24), @OLDUNITNUMBER VARCHAR(24), @UNITEFFECTIVE datetime, @UNITUNTIL datetime, @CUSTOMERNAME VARCHAR(12)

 — Set parameter values
EXEC [dbo].[TMTEXT_EMPUNIT_CREATE] @EMPUNITID OUTPUT, @ERROR OUTPUT, @DRIVERNUM, @UNITNUMBER, @OLDUNITNUMBER, @UNITEFFECTIVE, @UNITUNTIL, @CUSTOMERNAME

SELECT @EMPUNITID

 — Return the error code
Select @ERROR

Here is a detailed example:
The 3rd parameter is the Driver ID from TMT
The 4th is the new UnitID you want to assign
The 5th is the unit that is currently assigned
The 6th is the effective date of the new unit
The 7th is the until date of the old unit.
The 6th can be left NULL, but the 7th should always have a date that is greater than the 6th parameter, even if only for 1 second. The 5th parameter should always be the unit that does not currently have an UNTIL date. The 4th needs to always be a new Unit(can be one that existed previously but should not be the same unit as what is currently assigned).

EXEC @RC = [dbo].[TMTEXT_EMPUNIT_CREATE] @EMPUNITID OUTPUT, @ERROR OUTPUT, 'JBOB', '31001', '1001', '2009-06-02 13:18:01.000', '2009-06-02 13:18:05.000'

TMTEXT_SET_EMPUNIT

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@DRIVERNUM

CHAR, (12), Required

Driver Identification Number

@UNITID

CHAR, (12), Required

The Unit ID displayed in Masters Units

@EFFECTIVE

DATETIME, Optional

Date driver was assigned unit

@UNTIL

DATETIME, Optional

Date driver was unassigned unit

TMTEXT_SET_EMPUNIT Output Parameters:
@EMPUNITID — INTEGER
@ERROR — INTEGER

TMTEXT_SET_EMPUNIT Example:
DECLARE @DRIVERNUM CHAR(12), @UNITID CHAR(12), @EFFECTIVE DATETIME, @UNTIL DATETIME, @EMPUNITID INT, @ERROR INT

 — Set parameter values
SET @DRIVERNUM = '32' – Driver Num
SET @UNITID = '1010' – Unit ID
SET @EFFECTIVE = '06/01/2004' – Date you wish to assign to Driver
SET @UNIT = '06/12/2004' – optional; this will Assign for the given period

EXEC [dbo].[TMTEXT_SET_EMPUNIT] @DRIVERNUM, @UNITID, @EFFECTIVE, @UNTIL, @EMPUNITID OUTPUT, @ERROR OUTPUT

SELECT @EMPUNITID

 — Return the error code
Select @ERROR