External stored procedures: Plans and Repair Orders

TMTEXT_NEXT_SHOPORDNUM

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@SHOPID

VARCHAR, (12), Required

Shop Identification Number

@ORDERTYPE

VARCHAR, (12), Required

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

TMTEXT_NEXT_SHOPORDNUM Output Parameters:
@STRCONST — VARCHAR, (4)
@NEXTORDNUM — INTEGER

TMTEXT_NEXT_SHOPORDNUM Example:
DECLARE @SHOPID VARCHAR(12), @ORDERTYPE VARCHAR(12), @STRCONST VARCHAR(4), @NEXTORDNUM int

— Set parameter values

EXEC @RC = [dbo].[TMTEXT_NEXT_SHOPORDNUM] @SHOPID, @ORDERTYPE, @STRCONST OUTPUT, @NEXTORDNUM OUTPUT

TMTEXT_ORDERS_REPAIR_CREATE

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ORDERID

INTEGER, Required

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

@ORDERNUM

VARCHAR, (12), Optional

The number visible in TMT Fleet Maintenance to track RO’s, PO’s Fuel Tickets, etc

@SHOPID

VARCHAR, (12), Required

Shop Identification Number

@UNITID

VARCHAR, (12), Required

The Unit ID displayed in Masters Units

@COMPANYUNIT

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

Is the unit a company unit?

@STATUS

VARCHAR, (12), Optional (defaults to OPEN)

Whether Order is Open, Closed, Complete, etc.

@OPENED

DATETIME, Optional

When was the order opened

@CLOSED

DATETIME, Optional

When was the order closed

@PRIORITY

SMALLINT, Optional (defaults to 5)

The priority of the order

@VENDOR

VARCHAR, (12), Optional

Vendor providing service for order

@VENDACCT

VARCHAR, (12), Optional

Not currently in use

@AMTPAID

NUMERIC, (15,6), Optional (defaults to 0)

Total dollar amount paid

@AMTUOM

VARCHAR, (12), Optional

US or Canadian Dollars or other currency

@CUSTID

VARCHAR, (12), Optional

Identifies the customer

@TAXOVERRIDE

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

Tells you if tax was overridden

@BATCHID

INTEGER, Optional

Identification number of the batch

@OEMWARRANTY

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

Does item have OEM (Original Equipment Manufacturer) Warranty

@ROTYPE

VARCHAR, (12), Optional (defaults to STANDARD)

Standard or Vendor Repair Order

@REPCLASS

VARCHAR, (12), Optional (defaults to SCHEDULED)

Scheduled, Nonscheduled, or Emergency

@REPSITE

VARCHAR, (12), Optional (defaults to FACILITY)

Place where the repair was performed.

@ESTIMATE

NUMERIC, (15,6), Optional (defaults to 0)

Vendor estimate for repair.

@ACTUAL

NUMERIC, (15,6), Optional (defaults to 0)

Actual price of repair

@ESTCOST

NUMERIC, (15,6), Optional (defaults to 0)

Estimate from Shop Planner Estimates

@PROMISED

DATETIME, Optional

Date unit was promised

@REFINVOICE

INTEGER, Optional

ORDERID of Invoice associated with the order

@INVOICENO

VARCHAR, (12), Optional

Invoice number for order

@PONUMBER

VARCHAR, (12), Optional

Purchase Order Number - appears on repair order in Customer PO# field

TMTEXT_ORDERS_REPAIR_CREATE Output Parameters:
@OUTORDERID — INTEGER

TMTEXT_ORDERS_REPAIR_CREATE Example:
DECLARE @ORDERID int, @ORDERNUM VARCHAR(12), @SHOPID VARCHAR(12), @UNITID VARCHAR(12), @COMPANYUNIT CHAR(1), @STATUS VARCHAR(12), @OPENED datetime, @CLOSED datetime, @PRIORITY smallint, @VENDOR VARCHAR(12), @VENDACCT VARCHAR(12), @AMTPAID NUMERIC(15,6), @AMTUOM VARCHAR(12), @CUSTID VARCHAR(12), @TAXOVERRIDE CHAR(1), @BATCHID int, @OEMWARRANTY CHAR(1), @ROTYPE VARCHAR(12), @REPCLASS VARCHAR(12), @REPSITE VARCHAR(12), @ESTIMATE NUMERIC(15,6), @ACTUAL NUMERIC(15,6), @ESTCOST NUMERIC(15,6), @PROMISED datetime, @REFINVOICE int, @INVOICENO VARCHAR(12), @OUTORDERID int

— Set parameter values

EXEC @RC = [dbo].[TMTEXT_ORDERS_REPAIR_CREATE] @ORDERID, @ORDERNUM, @SHOPID, @UNITID, @COMPANYUNIT, @STATUS, @OPENED, @CLOSED, @PRIORITY, @VENDOR, @VENDACCT, @AMTPAID, @AMTUOM, @CUSTID, @TAXOVERRIDE, @BATCHID, @OEMWARRANTY, @ROTYPE, @REPCLASS, @REPSITE, @ESTIMATE, @ACTUAL, @ESTCOST, @PROMISED, @REFINVOICE, @INVOICENO, @OUTORDERID OUTPUT

SELECT @OUTORDERID

TMTEXT_PLAN_REPAIR_ORDER_CREATE

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@UNITID

VARCHAR, (12), Required

The Unit ID displayed in Masters Units

@SHOPID

VARCHAR, (12), Required

Shop Identification Number

@CUSTID

VARCHAR, (12), Optional

Identifies the customer

@VENDOR

VARCHAR, (12), Optional

Vendor providing service for order

@ORDERTYPE

VARCHAR, (12), Optional (defaults to REPAIR)

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

@DESCRIP

VARCHAR, (255), Optional (defaults to Default Plan)

The plan description

@ORDERID

INTEGER, Optional

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

@PRIORITY

SMALLINT, Optional (defaults to 5)

The priority of the order

@DATEOPENED

DATETIME, Required

When the order was opened

@DATECLOSED

DATETIME, Optional

When was the order closed

@STATUS

VARCHAR, (12), Optional (defaults to OPEN)

Whether Order is Open, Closed, Complete, etc.

@PONUMBER

VARCHAR, (12), Required

Purchase Order Number

@DRIVERLOC

VARCHAR, (255), Required

Driver Location as reported in Shop Planner plan

@INSPRPTID

INTEGER, Optional

Inspection Report ID

@INSPRPTNUM

VARCHAR, (12), Optional

Inspection Report Number

@DRIVERNAME

VARCHAR, (35), Required

Driver Name

@DRIVERPHONE

VARCHAR, (25), Required

Drivers Phone Number

@REFORDERID

INTEGER, Optional

When RO is tied to purchase order this will tell you the purchase order’s id

@ESTDATEIN

DATETIME, Required

Estimated Date In

@ESTDATEOUT

DATETIME, Optional

Estimated Date Out

@ESTTIME

NUMERIC, (15,6), Required

Estimated Time of Order

@COMPCODE

VARCHAR, (12), Required

Component Code

@CREATERO

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

If No (N) a plan is created If Yes (Y) a Repair Order is created

@REPREASON

VARCHAR, (12), Required

Repair Reason

@COMPLAINT

VARCHAR, (12), Required

Complaint

@INTNAME

VARCHAR, (12), Required

Not currently being used

@DURATION

INTEGER, Optional

Duration of Order, tied to calendar plan

@ALLDAYEVENT

BIT, Optional, (defaults to 0)

1 all day 0 not all day

@ENABLEREMINDER

BIT, Optional, (defaults to 0)

Reminder Yes or No

@REMINDERINTERVAL

INTEGER, Optional

In minutes

@SHOWTIMEAS

VARCHAR, (50), Optional

Not currently being used

@IMPORTANCE

INTEGER, Optional

Not currently being used

TMTEXT_PLAN_REPAIR_ORDER_CREATE Output Parameters:
@OUTORDERID — INTEGER
@ERROR — INTEGER

TMTEXT_PLAN_REPAIR_ORDER_CREATE Example:
DECLARE @ERROR INTEGER, @OUTORDERID INTEGER, @UNITID CHAR(12), @SHOPID CHAR(12), @CUSTID CHAR(12), @VENDORID CHAR(12), @ORDERTYPE CHAR(12), @DESCRIP VARCHAR(255), @ORDERID INTEGER, @PRIORITY SMALLINT, @DATEOPENED DATETIME, @DATECLOSED DATETIME, @STATUS CHAR(12), @PONUMBER CHAR(12), @DRIVERLOC CHAR(255), @INSPRPTID INTEGER, @INSPRPTNUM CHAR(12), @DRIVERNAME CHAR(35), @DRIVERPHONE CHAR(25), @REFORDERID INTEGER, @ESTDATEIN DATETIME, @ESTDATEOUT DATETIME, @ESTTIME NUMERIC (15,6), @COMPCODE VARCHAR(12), @CREATERO CHAR(1), @REPREASON CHAR(12), @COMPLAINT CHAR(12), @INTNAME CHAR(12)

— Set parameter values
SET @UNITID = '1010'
SET @SHOPID = '01'
SET @CUSTID= NULL
SET @VENDORID = 'A&ATIRE'
SET @ORDERTYPE = 'REPAIR'
SET @DESCRIP = 'This is a Test Plan/Ro'
SET @ORDERID = NULL
SET @PRIORITY = 5
SET @DATEOPENED = '10/25/2004'
SET @DATECLOSED = NULL
SET @STATUS = 'OPEN'
SET @PONUMBER = 'PO-1234'
SET @DRIVERLOC = 'lost In the back woods'
SET @INSPRPTID = NULL
SET @INSPRPTNUM = NULL
SET @DRIVERNAME = 'Fred Stone'
SET @DRIVERPHONE = '919-555-1212'
SET @REFORDERID = NULL
SET @ESTDATEIN = '10/25/2004'
SET @ESTDATEOUT = '10/27/2004'
SET @ESTTIME = 0
SET @COMPCODE = '045'
SET @CREATERO = 'Y'
SET @REPREASON = 'BREAKDOWN'
SET @COMPLAINT = 'BINDING'
SET @INTNAME = 'TMWSUITE'

EXEC [dbo].[TMTEXT_PLAN_REPAIR_ORDER_CREATE] @UNITID, @SHOPID, @CUSTID, @VENDORID, @ORDERTYPE, @DESCRIP, @ORDERID, @PRIORITY, @DATEOPENED, @DATECLOSED, @STATUS, @PONUMBER, @DRIVERLOC, @INSPRPTID, @INSPRPTNUM, @DRIVERNAME, @DRIVERPHONE, @REFORDERID, @ESTDATEIN, @ESTDATEOUT, @ESTTIME, @COMPCODE, @CREATERO, @REPREASON, @COMPLAINT, @INTNAME, @OUTORDERID OUTPUT, @ERROR OUTPUT

TMTEXT_PLAN_REPAIR_ORDER_CREATE_COMMODITY

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERROR

INT

Error ID supplied if an invalid parameter was entered

@ORDERID

INT

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

@UNITID

VARCHAR (24)

Number assigned by database to track the unit. Only seen in database

@SHOPID

VARCHAR (12)

Shop Identification Number

@CUSTID

VARCHAR (12)

Identifies the customer

@VENDOR

VARCHAR (12)

Vendor providing service for order

@ORDERTYPE

VARCHAR (12)

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

@DESCRIP

VARCHAR (255)

Description of the plan

@PRIORITY

SMALLINT

The priority of the order

@DATEOPENED

DATETIME

When the order was opened

@DATECLOSED

DATETIME

When the order was closed

@STATUS

VARCHAR (12)

Whether Order is Open, Closed, Complete, etc.

@PONUMBER

VARCHAR (24)

Purchase Order Number

@DRIVERLOC

VARCHAR (255)

Driver Location as reported in Shop Planner plan

@INSPRPTID

INT

Inspection Report ID

@INSPRPTNUM

VARCHAR (12)

Inspection Report Number

@DRIVERNAME

VARCHAR (35)

Driver Name

@DRIVERPHONE

VARCHAR (25)

Drivers Phone Number

@REFORDERID

INT

When RO is tied to purchase order this will tell you the purchase order’s id

@ESTDATEIN

DATETIME

Estimated Date In

@ESTDATEOUT

DATETIME

Estimated Date Out

@ESTTIME

NUMERIC (15,6)

Estimated Time of Order

@COMPCODE

VARCHAR (12)

Component Code

@CREATERO

CHAR (1)

If No (N) a plan is created If Yes (Y) a Repair Order is created

@REPREASON

VARCHAR (12)

Repair Reason

@COMPLAINT

VARCHAR (12)

Complaint

@INTNAME

VARCHAR (12)

Not currently being used

@COMMODITY1

VARCHAR (12)

An item being hauled

@HAZMAT1

CHAR (1)

Is commodity a Hazmat item? Y/N

@HAZMATCLASS1

VARCHAR (12)

What is Hazmat Classification?

@HAZMATSUBCLASS1

VARCHAR (12)

What is Hazmat Sub-Classification?

@LADINGNUMBER1

VARCHAR (12)

The Bill of Lading Number

@COMMODITY2

VARCHAR (12)

An item being hauled

@HAZMAT2

CHAR (1)

Is commodity a Hazmat item? Y/N

@HAZMATCLASS2

VARCHAR (12)

What is Hazmat Classification?

@HAZMATSUBCLASS2

VARCHAR (12)

What is Hazmat Sub-Classification?

@LADINGNUMBER2

VARCHAR (12)

The Bill of Lading Number

@COMMODITY3

VARCHAR (12)

An item being hauled

@HAZMAT3

CHAR (1)

Is commodity a Hazmat item? Y/N

@HAZMATCLASS3

VARCHAR (12)

What is Hazmat Classification?

@HAZMATSUBCLASS3

VARCHAR (12)

What is Hazmat Sub-Classification?

@LADINGNUMBER3

VARCHAR (12)

The Bill of Lading Number

@COMMODITY4

VARCHAR (12)

An item being hauled

@HAZMAT4

CHAR (1)

Is commodity a Hazmat item? Y/N

@HAZMATCLASS4

VARCHAR (12)

What is Hazmat Classification?

@HAZMATSUBCLASS4

VARCHAR (12)

What is Hazmat Sub-Classification?

@LADINGNUMBER4

VARCHAR (12)

The Bill of Lading Number

@HAULDATE

DATETIME

Date the commodity was hauled

@COMMENTS

VARCHAR (255)

Comments

@DURATION

INT

Duration of Order, tied to calendar plan

@ALLDAYEVENT

BIT

1 all day 0 not all day

@ENABLEREMINDER

BIT

Reminder Yes or No

@REMINDERINTERVAL

INT

In minutes

@SHOWTIMEAS

VARCHAR (60)

Not currently being used

@EXTPONUMBER

VARCHAR (12)

External PO Number

TMTEXT_PLAN_REPAIR_ORDER_CREATE_COMMODITY Example:
DECLARE @ERROR int, @ORDERID INT, @UNITID VARCHAR(24), @SHOPID VARCHAR(12), @CUSTID VARCHAR(12), @VENDOR VARCHAR(12), @ORDERTYPE VARCHAR(12), @DESCRIP VARCHAR(255), @PRIORITY SMALLINT, @DATEOPENED DATETIME, @DATECLOSED DATETIME, @STATUS VARCHAR(12), @PONUMBER VARCHAR(24), @DRIVERLOC VARCHAR(255), @INSPRPTID INT, @INSPRPTNUM VARCHAR(12), @DRIVERNAME VARCHAR(35), @DRIVERPHONE VARCHAR(25), @REFORDERID INT, @ESTDATEIN DATETIME, @ESTDATEOUT DATETIME, @ESTTIME NUMERIC(15,6), @COMPCODE VARCHAR(12), @CREATERO CHAR(1), @REPREASON VARCHAR(12), @COMPLAINT VARCHAR(12), @INTNAME VARCHAR(12), @COMMODITY1 VARCHAR(12), @HAZMAT1 CHAR(1), @HAZMATCLASS1 VARCHAR(12), @HAZMATSUBCLASS1 VARCHAR(12), @LADINGNUMBER1 VARCHAR(12), @COMMODITY2 VARCHAR(12), @HAZMAT2 CHAR(1), @HAZMATCLASS2 VARCHAR(12), @HAZMATSUBCLASS2 VARCHAR(12), @LADINGNUMBER2 VARCHAR(12), @COMMODITY3 VARCHAR(12), @HAZMAT3 CHAR(1), @HAZMATCLASS3 VARCHAR(12), @HAZMATSUBCLASS3 VARCHAR(12), @LADINGNUMBER3 VARCHAR(12), @COMMODITY4 VARCHAR(12), @HAZMAT4 CHAR(1), @HAZMATCLASS4 VARCHAR(12), @HAZMATSUBCLASS4 VARCHAR(12), @LADINGNUMBER4 VARCHAR(12), @HAULDATE DATETIME, @COMMENTS VARCHAR(255), @DURATION INT, @ALLDAYEVENT BIT, @ENABLEREMINDER BIT, @REMINDERINTERVAL INT, @SHOWTIMEAS VARCHAR(60), @EXTPONUMBER VARCHAR(12)

— Set parameter values

EXECUTE [dbo].[TMTEXT_PLAN_REPAIR_ORDER_CREATE_COMMODITY] @ERROR OUTPUT, @ORDERID OUTPUT, @UNITID, @SHOPID, @CUSTID, @VENDOR, @ORDERTYPE, @DESCRIP, @PRIORITY, @DATEOPENED, @DATECLOSED, @STATUS, @PONUMBER, @DRIVERLOC, @INSPRPTID, @INSPRPTNUM, @DRIVERNAME, @DRIVERPHONE, @REFORDERID, @ESTDATEIN, @ESTDATEOUT, @ESTTIME, @COMPCODE, @CREATERO, @REPREASON, @COMPLAINT, @INTNAME, @COMMODITY1, @HAZMAT1, @HAZMATCLASS1, @HAZMATSUBCLASS1, @LADINGNUMBER1, @COMMODITY2, @HAZMAT2, @HAZMATCLASS2, @HAZMATSUBCLASS2, @LADINGNUMBER2, @COMMODITY3, @HAZMAT3, @HAZMATCLASS3, @HAZMATSUBCLASS3, @LADINGNUMBER3, @COMMODITY4, @HAZMAT4, @HAZMATCLASS4, @HAZMATSUBCLASS4, @LADINGNUMBER4, @HAULDATE, @COMMENTS, @DURATION, @ALLDAYEVENT, @ENABLEREMINDER, @REMINDERINTERVAL, @SHOWTIMEAS, @EXTPONUMBER

TMTEXT_PLANNER_UPD

Updates existing plans in Shop Planner. The status will always be set to OPEN status. To change the status of the plan you will need to go through Shop Planner.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@PLANNERID

INTEGER, Required

Plan Identification Number

@UNITID

VARCHAR, (24), Optional

The Unit ID displayed in Masters Units

@SHOPID

VARCHAR, (12), Optional

Shop Identification Number

@CUSTID

VARCHAR, (12), Optional

Identifies the customer

@STATUS

VARCHAR, (12), Defaults to OPEN – not optional

Whether Order is Open, Closed, Complete, etc.

@ESTDATEIN

DATETIME, Optional

Estimated Date In

@PONUMBER

VARCHAR, (12), Optional

Purchase Order Number

@DESCRIP

VARCHAR, (255), Optional

Description of the plan

@DURATION

INTEGER, Optional

Duration of Order, tied to calendar plan

@ALLDAYEVENT

CHAR, (1), Optional

1 all day 0 not all day

@ENABLEREMINDER

CHAR, (1), Optional

Reminder Yes or No

@REMINDERINTERVAL

INTEGER, Optional

In minutes

@SHOWTIMEAS

VARCHAR, (50), Optional

Not currently being used

@IMPORTANCE

INTEGER, Optional

Not currently being used

TMTEXT_PLANNER_UPD Output Parameters:
@ERROR — INTEGER

TMTEXT_PLANNER_UPD Example:
DECLARE @PLANNERID INT, @UNITID VARCHAR(24), @SHOPID VARCHAR(12), @CUSTID VARCHAR(12), @STATUS VARCHAR(12), @ESTDATEIN DATETIME, @PONUMBER VARCHAR(12), @DESCRIP VARCHAR(255), @DURATION INT, @ALLDAYEVENT CHAR(1), @ENABLEREMINDER CHAR(1), @REMINDERINTERVAL INT, @SHOWTIMEAS VARCHAR(50), @IMPORTANCE INT, @ERROR INT

— Set parameter values

EXEC @RC = [dbo].[TMTEXT_PLANNER_UPD] @PLANNERID, @UNITID, @SHOPID, @CUSTID, @STATUS, @ESTDATEIN, @PONUMBER, @DESCRIP, @DURATION, @ALLDAYEVENT, @ENABLEREMINDER, @REMINDERINTERVAL, @SHOWTIMEAS, @IMPORTANCE, @ERROR OUTPUT

— Return the error code
SELECT @ERROR