External stored procedures: Fuel Ticket Creation

TMTEXT_ORDERS_FUELTICKET_CREATE will create a Fuel Ticket order in the Transman Database. Two Types of Fuel Orders: Company or Road

TMTEXT_METERMAID Used with Fuel Tickets

In order to correctly complete the order you will need to call TMTEXT_METERMAID in order to add a Meter for the order ID

Example call to TMTEXT_METERMAID:
SET @ORDERTYPE = 'FUEL TICKET'
SET @METERTYPE = 'ODOMETER'
SET @METERREADING = 45289
SET @METERDATE = '06/12/2004'
SET @ORDERID = @ORDERID this is from TMTEXT_ORDERS_FUELTICKET_CREATE @ORDERID

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

 — Return the error code
Select @ERRORS

TMTEXT_ORDERS_FUELTICKET_CREATE: Company

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@SHOPID

VARCHAR, (12), Required

Shop Identification Number

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@COMPANYUNIT

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

Is the unit a company unit

@AMTPAID

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

Amount fuel was purchased for

@CUSTID

VARCHAR, (12), Optional

Customer unit belongs to

@TAXOVERRIDE

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

Tells you if tax was overridden

@TAXABLE

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

Is item taxable

@BATCHID

INTEGER, Optional

If fuel ticket is part of batch what is the batch ID?

@QTYREQD

NUMERIC, (15,6), Required

How many gallons

@ FLUIDTYPE

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

Diesel, gas, propane, etc

@ TICKETNUM

VARCHAR, (12), Optional

Number from receipt for fuel

@ TRIPNUM

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

TMT Fleet Maintenance trip number

TMTEXT_ORDERS_FUELTICKET_CREATE Output Parameters:
@ORDERID — INTEGER
@SECTION — INTEGER
@LINEID — INTEGER
@ERRORS — INTEGER

TMTEXT_ORDERS_FUELTICKET_CREATE Example for Company:
DECLARE @SHOPID VARCHAR(12), @UNITID VARCHAR(12), @COMPANYUNIT CHAR(1), @VENDOR VARCHAR(12), @AMTPAID NUMERIC(15,6), @CUSTID VARCHAR(12), @TAXOVERRIDE CHAR(1), @TAXABLE CHAR(1), @BATCHID int, @TOTALAMT NUMERIC(15,6), @QTYREQD NUMERIC(15,6), @CHGAMT NUMERIC(15,6), @CHGUOM VARCHAR(12), @FLUIDTYPE VARCHAR(12), @TICKETNUM VARCHAR(12), @TRIPNUM int, @ORDERID int, @SECTION int, @LINEID int, @ERRORS int

 — Set parameter values
SET @UNITID= '1010'
SET @SHOPID='01'
SET @COMPANYUNIT= 'Y'
SET @BATCHID = 1
SET @QTYREQD = 7.6875
SET @CHGAMT= 10.250000
SET @FLUIDTYPE = 'GASOLINE'
SET @AMTPAID = 123.23

EXEC [TMTEXT_ORDERS_FUELTICKET_CREATE] @SHOPID, @UNITID, @COMPANYUNIT, @VENDOR, @AMTPAID, @CUSTID, @TAXOVERRIDE, @TAXABLE, @BATCHID, @TOTALAMT, @QTYREQD, @CHGAMT, @CHGUOM, @FLUIDTYPE, @TICKETNUM, @TRIPNUM, @ORDERID OUTPUT, @SECTION OUTPUT, @LINEID OUTPUT, @ERRORS OUTPUT

SELECT @ORDERID, @SECTION, @LINEID

 — Return the error code
Select @ERRORS

TMTEXT_ORDERS_FUELTICKET_CREATE: Road

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@SHOPID

VARCHAR, (12), Required

Shop Identification Number

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@COMPANYUNIT

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

Is the unit a company unit

@VENDOR

VARCHAR (12) Optional

Vendor providing fuel

@CUSTID

VARCHAR (12) Optional

Customer unit belongs to

@TAXOVERRIDE

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

Tells you if tax was overridden

@TAXABLE

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

Is item taxable

@BATCHID

INTEGER, Optional

If fuel ticket is part of batch what is the batch ID?

@TOTALAMT

NUMERIC, (15,6), Required

Amount paid for fuel

@QTYREQD

NUMERIC, (15,6), Required

How many gallons

@FLUIDTYPE

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

Diesel, gas, propane, etc

@TICKETNUM

VARCHAR ,(12), Optional

Number from receipt for fuel

@TRIPNUM

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

TMT Fleet Maintenance trip number

@DONOTCALCCHGAMTONRT

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

If set to “Y” the system will use the dollar amount from the fuel ticket and not recalculate the amount

TMTEXT_ORDERS_FUELTICKET_CREATE Output Parameters:
@ORDERID — INTEGER
@SECTION — INTEGER
@LINEID — INTEGER
@ERRORS — INTEGER

TMTEXT_ORDERS_FUELTICKET_CREATE Example for Road:
DECLARE @SHOPID VARCHAR(12), @UNITID VARCHAR(12), @COMPANYUNIT CHAR(1), @VENDOR VARCHAR(12), @AMTPAID NUMERIC(15,6), @CUSTID VARCHAR(12), @TAXOVERRIDE CHAR(1), @TAXABLE CHAR(1), @BATCHID int, @TOTALAMT NUMERIC(15,6), @QTYREQD NUMERIC(15,6), @CHGAMT NUMERIC(15,6), @CHGUOM VARCHAR(12), @FLUIDTYPE VARCHAR(12), @TICKETNUM VARCHAR(12), @TRIPNUM int, @ORDERID int, @SECTION int, @LINEID int, @ERRORS int

 — Set parameter values
SET @UNITID= '1010'
SET @SHOPID='01'
SET @COMPANYUNIT= 'Y'
SET @AMTUOM = 'US$'
SET @QTYREQD = 7.6875
SET @CHGAMT= 10.250000
SET @QTYUOM= 'GALLONS'
SET @FLUIDTYPE = 'GASOLINE'
SET @CHGAMT = 1.98
SET @VENDOR = 'BP/AMACO'
SET @TOTALAMT = 123

EXEC [TMTEXT_ORDERS_FUELTICKET_CREATE] @SHOPID, @UNITID, @COMPANYUNIT, @VENDOR, @AMTPAID, @CUSTID, @TAXOVERRIDE, @TAXABLE, @BATCHID, @TOTALAMT, @QTYREQD, @CHGAMT, @CHGUOM, @FLUIDTYPE, @TICKETNUM, @TRIPNUM, @ORDERID OUTPUT, @SECTION OUTPUT, @LINEID OUTPUT, @ERRORS OUTPUT

SELECT @ORDERID, @SECTION, @LINEID, @ERRORS

TMTEXT_ORDERS_FUELTICKET_WITHDATE_CREATE: Company

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@SHOPID

VARCHAR, (12), Required

Shop Identification Number

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@COMPANYUNIT

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

Is the unit a company unit

@AMTPAID

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

Amount fuel was purchased for

@CUSTID

VARCHAR, (12), Optional

Customer unit belongs to

@TAXOVERRIDE

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

Tells you if tax was overridden

@TAXABLE

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

Is item taxable

@BATCHID

INTEGER, Optional

If fuel ticket is part of batch what is the batch ID?

@QTYREQD

NUMERIC, (15,6), Required

How many gallons

@FLUIDTYPE

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

Diesel, gas, propane, etc

@TICKETNUM

VARCHAR, (12), Optional

Number from receipt for fuel

@TRIPNUM

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

TMT Fleet Maintenance trip number

@CREATEDATE

DATETIME, Optional

When fuel ticket was created

TMTEXT_ORDERS_FUELTICKET_WITHDATE_CREATE Output Parameters:
@ORDERID — INTEGER
@SECTION — INTEGER
@LINEID — INTEGER
@ERRORS — INTEGER

TMTEXT_ORDERS_FUELTICKET_WITHDATE_CREATE Example for Company:
DECLARE @SHOPID VARCHAR(12), @UNITID VARCHAR(12), @COMPANYUNIT CHAR(1), @VENDOR VARCHAR(12), @AMTPAID NUMERIC(15,6), @CUSTID VARCHAR(12), @TAXOVERRIDE CHAR(1), @TAXABLE CHAR(1), @BATCHID int, @TOTALAMT NUMERIC(15,6), @QTYREQD NUMERIC(15,6), @CHGAMT NUMERIC(15,6), @CHGUOM VARCHAR(12), @FLUIDTYPE VARCHAR(12), @TICKETNUM VARCHAR(12), @TRIPNUM int, @CREATEDATE datetime, @ORDERID int, @SECTION int, @LINEID int, @ERRORS int

 — Set parameter values
SET @UNITID= '1010'
SET @SHOPID='01'
SET @COMPANYUNIT= 'Y'
SET @AMTUOM = 'US$'
SET @QTYREQD = 7.6875
SET @CHGAMT= 10.250000
SET @QTYUOM= 'GALLONS'
SET @FLUIDTYPE = 'GASOLINE'
SET @AMTPAID = 123.23
SET @ CREATEDATE = '4/01/2009'

EXEC [TMTEXT_ORDERS_FUELTICKET_WITHDATE_CREATE] @SHOPID, @UNITID, @COMPANYUNIT, @VENDOR, @AMTPAID, @CUSTID, @TAXOVERRIDE, @TAXABLE, @BATCHID, @TOTALAMT, @QTYREQD, @CHGAMT, @CHGUOM,@FLUIDTYPE, @TICKETNUM, @TRIPNUM, @CREATEDATE, @ORDERID OUTPUT, @SECTION OUTPUT, @LINEID OUTPUT, @ERRORS OUTPUT

SELECT @ORDERID, @SECTION, @ LINEID, @ERRORS

TMTEXT_ORDERS_FUELTICKET_WITHDATE_CREATE: ROAD

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@SHOPID

VARCHAR, (12), Required

Shop Identification Number

@UNITID

VARCHAR, (24), Required

The Unit ID displayed in Masters Units

@COMPANYUNIT

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

Is the unit a company unit

@VENDOR

VARCHAR (12), Optional

Vendor providing fuel

@CUSTID

VARCHAR (12), Optional

Customer unit belongs to

@TAXOVERRIDE

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

Tells you if tax was overridden

@TAXABLE

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

Is item taxable

@BATCHID

INTEGER, Optional

If fuel ticket is part of batch what is the batch ID?

@TOTALAMT

NUMERIC, (15,6), Required

Amount paid for fuel

@QTYREQD

NUMERIC, (15,6), Required

How many gallons

@FLUIDTYPE

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

Diesel, gas, propane, etc

@TICKETNUM

VARCHAR ,(12), Optional

Number from receipt for fuel

@TRIPNUM

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

TMT Fleet Maintenance trip number

@CREATEDATE

DATETIME, Optional

When fuel ticket was created

@DONOTCALCCHGAMTONRT

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

If set to “Y” the system will use the dollar amount from the road ticket and not recalculate the amount

TMTEXT_ORDERS_FUELTICKET_WITHDATE_CREATE Output Parameters:
@ORDERID — INTEGER
@SECTION — INTEGER
@LINEID — INTEGER
@ERRORS — INTEGER

TMTEXT_ORDERS_FUELTICKET_WITHDATE_CREATE Example for Road:
DECLARE @SHOPID VARCHAR(12), @UNITID VARCHAR(12), @COMPANYUNIT CHAR(1), @VENDOR VARCHAR(12), @AMTPAID NUMERIC(15,6), @CUSTID VARCHAR(12), @TAXOVERRIDE CHAR(1), @TAXABLE CHAR(1), @BATCHID int, @TOTALAMT NUMERIC(15,6), @QTYREQD NUMERIC(15,6), @CHGAMT NUMERIC(15,6), @CHGUOM VARCHAR(12), @FLUIDTYPE VARCHAR(12), @TICKETNUM VARCHAR(12), @TRIPNUM int, @CREATEDATE datetime, @ORDERID int, @SECTION int, @LINEID int, @ERRORS int

 — Set parameter values
SET @UNITID= '1010'
SET @SHOPID='01'
SET @COMPANYUNIT= 'Y'
SET @AMTUOM = 'US$'
SET @QTYREQD = 7.6875
SET @CHGAMT= 10.250000
SET @QTYUOM= 'GALLONS'
SET @FLUIDTYPE = 'GASOLINE'
SET @CHGAMT = 1.98
SET @VENDOR = 'BP/AMACO'
SET @TOTALAMT = 123
SET @ CREATEDATE = '4/01/2009'

EXEC [TMTEXT_ORDERS_FUELTICKET_CREATE] @SHOPID, @UNITID, @COMPANYUNIT, @VENDOR ,@AMTPAID, @CUSTID, @TAXOVERRIDE, @TAXABLE, @BATCHID, @TOTALAMT, @QTYREQD, @CHGAMT, @CHGUOM, @FLUIDTYPE,@TICKETNUM, @TRIPNUM, @CREATEDATE, @ORDERID OUTPUT, @SECTION OUTPUT, @LINEID OUTPUT, @ERRORS OUTPUT

SELECT @ORDERID, @SECTION, @LINEID, @ERRORS