External stored procedures: Parts/Inventory

TMTAMSEXT_GET_ITEM_CHANGES

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@INTEGRATIONID

INT

Identifies the integration the reading came from

DECLARE @INTEGRATIONID int

— Set parameter values.
SET @INTEGRATIONID = 9

EXECUTE [dbo].[TMTAMSEXT_GET_ITEM_CHANGES] @INTEGRATIONID

TMTAMSEXT_GET_ITEMXREF

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@INTEGRATIONID

INT

Identifies the integration the reading came from

DECLARE @INTEGRATIONID int

— Set parameter values
SET @INTEGRATIONID = 9

EXECUTE [dbo].[TMTAMSEXT_GET_ITEMXREF] @INTEGRATIONID

TMTAMSEXT_GET_ITEMREQUESTS

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@INTEGRATIONID

INT

Identifies the integration the reading came from

DECLARE @INTEGRATIONID int

— Set parameter values
SET @INTEGRATIONID = 9

EXECUTE [dbo].[TMTAMSEXT_GET_ITEMREQUESTS] @INTEGRATIONID

TMTEXT_INVENTORY_OUTOFBAL_ZERO

The purpose of this procedure is to set a part’s on hand value to 0. The Onhand values on the Definition tab and Bins tab will all be set to 0. The cost records will also be removed. The average cost and last cost will be retained.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@SHOPID

VARCHAR, (12), Required

Shop Identification Number

@ITEMNUM

VARCHAR, (24), Required

The part number for the part.

@MFGCODE

VARCHAR, (12), Required

The manufacturer of the part. This is required when tracking duplicate parts in inventory.

Example:
DECLARE @SHOPID VARCHAR(12), @ITEMNUM VARCHAR(24), @MFGCODE VARCHAR(12)

— Set parameter values here.
SET @SHOPID = '01'
SET @ITEMNUM = '0000450'
SET @MFGCODE = 'IMPER'

EXECUTE [dbo].[TMTEXT_INVENTORY_OUTOFBAL_ZERO] @SHOPID, @ITEMNUM, @MFGCODE

TMWAMSEXT_PHYSINVPARTADJ_UPD

The purpose of this procedure is to update an open physical inventory with inventory counts. The stored procedure requires the parameters of Shop, Bin, Part Number, and Actual Quantity.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@SHOPID

VARCHAR, (12)

Shop Identification Number

@PARTNUMBER

VARCHAR, (24)

The part number for the part.

@MFGCODE

VARCHAR, (12)

The manufacturer of the part. This is required when tracking duplicate parts in inventory.

@BINID

VARCHAR, (12)

The name of the bin.

@ACTUAL

NUMERIC (15,6)

The quantity on hand.

@ADJCOST

NUMERIC (15,6)

The dollar amount to be used as the unit cost for parts adjustment.

@ONORDER

NUMERIC (15,6)

The quantity of the part on order.

The following error messages may be received:
0 – Success
700 – Bad Bin for Part
701 – Item not on Inventory
702 – physical inventory does not exist for that part

Example:
DECLARE @ERRORID int, @SHOPID VARCHAR(12), @PARTNUMBER VARCHAR(24), @MFGCODE VARCHAR(12), @BINID VARCHAR(12), @ACTUAL NUMERIC(15,6), @ADJCOST NUMERIC(15,6), @ONORDER NUMERIC(15,6)

— Set parameter values
SET @SHOPID = '01'
SET @PARTNUMBER = '1157'
SET @ACTUAL = 4
SET @MFGCODE = 'NAPAX'
SET @BINID = 'BIN1'

EXEC [dbo].[TMWAMSEXT_PHYSINVPARTADJ_UPD] @ERRORID OUTPUT , @SHOPID, @PARTNUMBER, @MFGCODE, @BINID, @ACTUAL, @ADJCOST, @ONORDER

— Return the error code
Select @ERRORID

TMWAMSEXT_RECEIVE_INVENTORY_ISO8601_ITEMS / TMWAMSEXT_RECEIVE_INVENTORY_ISO8601DATETIME_ITEMS

This stored procedure received PO receipts, updates inventory levels, and averages the price based on current inventory levels. This procedure accepts the received date in ISO8601 format and coverts it to mm/dd/yyyy hh:mm:ss.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERRORS

INT

Error ID supplied if an invalid parameter was entered

@INTEGRATIONID

INT

Identifies the integration the reading came from

@VENDORNAME

VARCHAR (24)

Name of the vendor

@SHOPID

VARCHAR (12)

Shop Identification Number

@PARTNUMBER

VARCHAR (24)

Part in TMT Fleet Maintenance

@MFGCODE

VARCHAR (12)

The part manufacturer

@LINETYPE

VARCHAR (12)

Is it Part, Labor, Tax, Comment, Service, etc.

@VENDORLINE

VARCHAR (1)

Is part vendor supplied

@QTYRCVD

NUMERIC (15,6)

Quantity Received

@CHGAMT

NUMERIC (15,6)

Charge Amount

@QTYONORDER

NUMERIC (15,6)

Quantity on Order

@UOM

CHAR (12)

Unit of Measure is it foot, meter, each, etc.

@RCVDDATE

VARCHAR (60)

Date received. This should be in ISO8601 format.
Example: 2011-03-10T00:00:00.000-06:00

@ORDERNUMBER

VARCHAR (12)

The Order Number that displays in TMT Fleet Maintenance

@SECTIONNUMBER

VARCHAR (12)

The Section Number that displays in TMT Fleet Maintenance

@ORDERID

INT

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

@SECTIONID

INT

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

@PONUMBER

VARCHAR (24)

Purchase Order Number

@POLINENUMBER

VARCHAR (24)

Purchase Order Line Number

@USERNAME

VARCHAR (40)

Person who ordered part

@DESCRIP

VARCHAR (60)

Part Description

DECLARE @ERRORS int, @INTEGRATIONID int, @VENDORNAME VARCHAR(24), @SHOPID VARCHAR(12), @PARTNUMBER VARCHAR(24), @MFGCODE VARCHAR(12), @LINETYPE VARCHAR(12), @VENDORLINE VARCHA (1), @QTYRCVD NUMERIC(15,6), @CHGAMT NUMERIC(15,6), @QTYONORDER NUMERIC(15,6), @UOM CHAR(12), @RCVDDATE VARCHAR(60), @ORDERNUMBER VARCHAR(12), @SECTIONNUMBER VARCHAR(12), @ORDERID int, @SECTIONID int, @PONUMBER VARCHAR(24), @POLINENUMBER VARCHAR(24), @USERNAME VARCHAR(40), @DESCRIP VARCHAR(60)

— Set parameter values here.
SET @INTEGRATIONID = 1
SET @VENDORNAME = 'BARKER TRUCK SALES INC'
SET @SHOPID = '0001'
SET @PARTNUMBER = 'R803054'
SET @QTYRCVD = 1
SET @CHGAMT = 50.24
SET @RCVDDATE = '2011-03-10T00:00:00.000-06:00'
SET @UOM = 'EACH'
SET @ORDERNUMBER = '0259-0082663'
SET @SECTIONNUMBER = '1'
SET @PONUMBER = '5566'
SET @POLINENUMBER = '1'
SET @USERNAME = 'SYSDBA'
SET @DESCRIP = 'TRUCK.PARTS'

EXECUTE [dbo].[TMWAMSEXT_RECEIVE_INVENTORY_ISO8601_ITEMS] @ERRORS OUTPUT, @INTEGRATIONID, @VENDORNAME, @SHOPID, @PARTNUMBER, @MFGCODE, @LINETYPE, @VENDORLINE, @QTYRCVD, @CHGAMT, @QTYONORDER, @UOM, @RCVDDATE, @ORDERNUMBER, @SECTIONNUMBER, @ORDERID, @SECTIONID, @PONUMBER, @POLINENUMBER, @USERNAME, @DESCRIP

TMTAMSEXT_RECEIVE_INVENTORY_ITEMS

This stored procedure received PO receipts, updates inventory levels, and averages the price based on current inventory levels.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERRORS

INT

Error ID supplied if an invalid parameter was entered

@INTEGRATIONID

INT

Identifies the integration the reading came from

@VENDORNAME

VARCHAR (24)

Name of the vendor

@SHOPID

VARCHAR (12)

Shop Identification Number

@PARTNUMBER

VARCHAR (24)

Part in TMT Fleet Maintenance

@MFGCODE

VARCHAR (12)

The part manufacturer

@LINETYPE

VARCHAR (12)

Part, Labor, Tax, Comment, etc.

@VENDORLINE

VARCHAR (1)

Is part vendor supplied

@QTYRCVD

NUMERIC (15,6)

Quantity Received

@CHGAMT

NUMERIC (15,6)

Charge Amount

@QTYONORDER

NUMERIC (15,6)

Quantity on Order

@UOM

VARCHAR (12)

Unit of Measure is it foot, meter, each, quart, etc.

@RCVDDATE

DATETIME

Date received

@ORDERNUMBER

VARCHAR (12)

The Order Number that displays in TMT Fleet Maintenance

@SECTIONNUMBER

VARCHAR (12)

The Section Number that displays in TMT Fleet Maintenance

@ORDERID

INT

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

@SECTIONID

INT

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

@PONUMBER

VARCHAR (24)

Purchase Order Number

@POLINENUMBER

VARCHAR (24)

Purchase Order Line Number

@USERNAME

VARCHAR (40)

Person who ordered part

@DESCRIP

VARCHAR (60)

Part Description

DECLARE @ERRORS int, @INTEGRATIONID int, @VENDORNAME VARCHAR(24), @SHOPID VARCHAR(12), @PARTNUMBER VARCHAR(24), @MFGCODE VARCHAR(12), @LINETYPE VARCHAR(12), @VENDORLINE VARCHAR(1), @QTYRCVD NUMERIC(15,6), @CHGAMT NUMERIC(15,6), @QTYONORDER NUMERIC(15,6), @UOM CHAR(12), @RCVDDATE DATETIME, @ORDERNUMBER VARCHAR(12), @SECTIONNUMBER VARCHAR(12), @ORDERID int, @SECTIONID int, @PONUMBER VARCHAR(24), @POLINENUMBER VARCHAR(24), @USERNAME VARCHAR(40), @DESCRIP VARCHAR(60)

— Set parameter values here.
SET @INTEGRATIONID = 1
SET @VENDORNAME = 'BARKER TRUCK SALES INC'
SET @SHOPID = '0001'
SET @PARTNUMBER = 'R803054'
SET @QTYRCVD = 1
SET @CHGAMT = 50.24
SET @RCVDDATE = '03/03/2015 10:00:00'
SET @UOM = 'EACH'
SET @ORDERNUMBER = '0259-0082663'
SET @SECTIONNUMBER = '1'
SET @PONUMBER = '5566'
SET @POLINENUMBER = '1'
SET @USERNAME = 'SYSDBA'
SET @DESCRIP = 'TRUCK.PARTS'

EXECUTE [dbo].[TMTAMSEXT_RECEIVE_INVENTORY_ITEMS] @ERRORS OUTPUT, @INTEGRATIONID, @VENDORNAME, @SHOPID, @PARTNUMBER, @MFGCODE, @LINETYPE, @VENDORLINE, @QTYRCVD, @CHGAMT, @QTYONORDER, @UOM, @RCVDDATE, @ORDERNUMBER, @SECTIONNUMBER, @ORDERID, @SECTIONID, @PONUMBER, @POLINENUMBER, @USERNAME, @DESCRIP