External stored procedures: Purchase Orders

TMWAMSEXT_CLOSE_POS_EXTENDED

The purpose of this procedure is to allow POs to be closed or an individual PO receipt to be closed. For the PO or receipt to be closed, each receipt must be associated with an invoice. Vendor ROs must be in complete status to be closed and other PO types must be in received status. A Blanket PO requires a section to be passed to the procedure.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ORDERNUM

VARCHAR, (24), Required

The Order Number that displays in TMT Fleet Maintenance

@VENDORID

VARCHAR, (12), Required

Vendor Identification Number

@SHOPID

VARCHAR, (12) Required if more than one shop will have a PURCHASE ORDER with the same number.

Shop Identification Number

@AMOUNT

NUMERIC, (15, 6)

The amount of the PO or the amount of the receipt. This field is required.

@PERCENTALLOWED

NUMERIC, (15, 6)

The percentage the @AMOUNT can be within the receipt or the total PO amount and allow the PO or receipt to be closed. If not provided, .5% is used.

@SECTION

INTEGER

If provided, the receipt to be closed. Passing a receipt will only work for POs that are not Vendor RO POs as Vendor RO POs only have one receipt. If not provided, will attempt to close the entire PO. A Blanket PO requires that a receipt be passed.

The following error messages may be received:
530 - Invalid Vendor
549 - Empty Required Meter
597 - Invoice does not exist
635 - Order Closed
662 - PO Number does not exist
663 - PO Number not received (includes partial)
664 - PO Number and Amount does not match
665 - PO Number exists more than once. Need ShopID
667 - Vendor RO Number not in complete status
669 - PO Does Not have lines

Example: DECLARE @ERRORID [INTEGER], @ERRORDESC [VARCHAR](255), @ORDERNUM [VARCHAR](24), @VENDORID [VARCHAR](12), @SHOPID [VARCHAR](12), @AMOUNT [NUMERIC](15,6), @PERCENTALLOWED [NUMERIC](15,6), @LCID INT, @SECTION INT

SET @ORDERNUM = '000000000062'
SET @VENDORID = 'A&ATIRE'
SET @AMOUNT = 24.20
SET @PERCENTALLOWED = 6
SET @SECTION=1

EXEC [dbo].[TMWAMSEXT_CLOSE_POS_EXTENDED] @ERRORID OUTPUT, @ERRORDESC OUTPUT, @ORDERNUM, @VENDORID, @SHOPID, @AMOUNT, @PERCENTALLOWED, @LCID, @SECTION

— Return the error code
SELECT @ERRORID

TMWAMSEXT_CLOSE_POS_EXTENDED2

The purpose of this procedure is to allow POs to be closed or an individual PO receipt to be closed. For the PO or receipt to be closed, each receipt must be associated with an invoice. Vendor ROs must be in complete status to be closed and other PO types must be in received status. A Blanket PO requires a section to be passed to the procedure.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ORDERNUM

VARCHAR, (24), Required

The Order Number that displays in TMT Fleet Maintenance

@VENDORID

VARCHAR, (12), Required

Vendor Identification Number

@SHOPID

VARCHAR, (12) Required if more than one shop will have a PURCHASE ORDER with the same number.

Shop Identification Number

@AMOUNT

NUMERIC, (15,6)

The amount of the PO or the amount of the receipt. This field is required.

@PERCENTALLOWED

NUMERIC, (4, 2)

The percentage the @AMOUNT can be within the receipt or the total PO amount and allow the PO or receipt to be closed. If not provided, .5% is used.

@SECTION

INTEGER

If provided, the receipt to be closed. Passing a receipt will only work for POs that are not Vendor RO POs as Vendor RO POs only have one receipt. If not provided, will attempt to close the entire PO. A Blanket PO requires that a receipt be passed.

@VALIDATEONLY

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

Checks that all entered parameters contain valid inputs.

@UNITNUMBER

VARCHAR, (24), Optional

The Unit ID displayed in Masters Units

@STATEMENTNUMBER

VARCHAR, (24), Optional

If value is provided, inserts entered value into POInvoice table under column “CheckNo”.

@COMMENTS

VARCHAR, (255), Optional

Comments

@INVOICENO

VARCHAR, (12), Optional

Invoice number for order

@PAYMETHOD

VARCHAR, (12), Optional

The paymethod used to pay the invoice. This must be a valid TMT paymethod if provided. Standard paymethods provided are CASH, MASTERCARD, ONACCOUNT, and VISA.

@MODIFIEDBY

VARCHAR, (256), Optional

Who modified the record last

The following error messages may be received:
530 - Invalid Vendor
549 - Empty Required Meter
597 - Invoice does not exist
635 - Order Closed
662 - PO Number does not exist
663 - PO Number not received (includes partial)
664 - PO Number and Amount does not match
665 - PO Number exists more than once. Need ShopID
667 - Vendor RO Number not in complete status
668 - PO Amount Cannot be Zero
669 - PO Does Not have lines

Example: DECLARE @ERRORID [INTEGER], @ERRORDESC [VARCHAR](255), @ORDERNUM [VARCHAR](24), @VENDORID [VARCHAR](12), @SHOPID [VARCHAR](12), @AMOUNT [NUMERIC](15,6), @PERCENTALLOWED [NUMERIC](15,6), @LCID INT, @SECTION INT

SET @ORDERNUM = '000000000062'
SET @VENDORID = 'A&ATIRE'
SET @AMOUNT = 24.20
SET @PERCENTALLOWED = 6
SET @SECTION=1

EXEC [dbo].[TMWAMSEXT_CLOSE_POS_EXTENDED] @ERRORID OUTPUT, @ERRORDESC OUTPUT, @ORDERNUM, @VENDORID, @SHOPID, @AMOUNT, @PERCENTALLOWED, @LCID, @SECTION

— Return the error code
SELECT @ERRORID

TMTAMSEXT_ORDERS_POINVOIC_UPD

The purpose of this procedure is to retrieve invoice payment information from other programs and upload it to TMT Fleet Maintenance. TMT Fleet Maintenance requires that the import contain a valid Invoice Number, a valid Purchase Order Number and a valid Shop ID which comes from that shop.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERRORID

INTEGER

Error ID supplied if an invalid parameter was enter, ie an invalid PO Number

@PONO

VARCHAR, (24), Required

must be a valid PO Number tied to @SHOPID if you do not have a Valid shopid and PO number you will get an error 539

@SHOPID

VARCHAR, (12), Required

Shop Identification Number

@INVOICENO

VARCHAR, (24), Required

Invoice number for order

@INVOICEDATE

SMALLDATETIME

if not given system will use current date.

@INVOICECOMMENT

VARCHAR, (8000), Optional

Comments from the Invoice

@PAYMETHOD

VARCHAR, (12), Required

must be a valid pay method if not valid then it will give an error 526

@PAYDATE

SMALLDATETIME

if not given system will use current date.

@PAYCHECKNO

VARCHAR, (12), Required

Check number for the payment

@PAYAMT

NUMERIC, (12), Required

Amount Paid

@PACKLISTNO

VARCHAR, (12), Optional

The Packing List Number

TMTAMSEXT_ORDERS_POINVOIC_UPD Example:
DECLARE @ERRORID [INTEGER], @PONU [VARCHAR](24), @SHOPID [VARCHAR](12), @INVOICENU [VARCHAR](24), @INVOICEDATE [SMALLDATETIME], @INVOICECOMMENT [VARCHAR](8000), @PAYMETHOD [VARCHAR](12), @PAYDATE [SMALLDATETIME], @CHECKNU [VARCHAR](12), @AMTPAID [NUMERIC](15,6), @PACKLISTNO [VARCHAR](12)

— Set parameter values
SET @PONU = '4453'
SET @SHOPID = 'TA'
SET @INVOICENU = 'my invoice'
SET @INVOICEDATE = GETDATE()
SET @PAYMETHOD = 'CASH'
SET @PAYDATE = GETDATE()
SET @CHECKNU = 'my check'
SET @AMTPAID = 110.00
SET @INVOICECOMMENT = 'imported from external system'
SET @PACKLISTNO = '1234567890'

EXEC [dbo].[TMTAMSEXT_ORDERS_POINVOIC_CREATE] @ERRORID OUTPUT, @PONU,@SHOPID, @INVOICENU, @INVOICEDATE, @INVOICECOMMENT, @PAYMETHOD, @PAYDATE, @CHECKNU, @AMTPAID, @PACKLISTNO

— Return the error code
SELECT @ERRORID

TMWAMSEXT_PO_RECEIVED_CLOSE

The purpose of this procedure is to check to see if a PO is RECEIVED and then set the PO CLOSED if not in a RECEIVED status.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERRORID

INTEGER

ERRORID supplied if an invalid parameter was entered. See list of errors above

@ERRORDESC

VARCHAR (255)

Description of the error, See list of errors above

@ORDERNUM

VARCHAR (24)

The Order Number that displays in TMT Fleet Maintenance

@VENDORID

VARCHAR (12)

Vendor Identification Number

@SHOPID

VARCHAR (12)

Shop Identification Number

@AMOUNT

NUMERIC (15,6)

Dollar amount of the PO

@PERCENTALLOWED

NUMERIC (4,2)

The percent that the po can vary from the input amount

The following messages may be received:
0 - Success
530 - Invalid vendor
535 - Order Closed
549 - Empty Required Meter
662 - PO Number does not exist
663 - PO Number not received (includes partial)
664 - PO Number and Amount does not match
665 - PO Number exists more than once. Need ShopID
667 - Vendor RO Number not in complete status
668 - PO Amount Cannot be Zero
669 - PO Does Not have lines

TMWAMSEXT_PO_RECEIVED_CLOSE Example:
DECLARE @ERRORID [INTEGER], @ERRORDESC [VARCHAR](255), @ORDERNUM [VARCHAR](24), @VENDORID [VARCHAR](12), @SHOPID [VARCHAR](12), @AMOUNT [NUMERIC](15,6), @PERCENTALLOWED [NUMERIC](4,2)

— Set parameter values
SET @ORDERNUM = '000000000001'
SET @VENDORID = 'NAPA1'

— SET @SHOPID = '01'
SET @AMOUNT = 47.7
SET @PERCENTALLOWED = 0

EXEC [dbo].[TMWAMSEXT_PO_RECEIVED_CLOSE] @ERRORID OUTPUT, @ERRORDESC OUTPUT, @ORDERNUM, @VENDORID, @SHOPID, @AMOUNT, @PERCENTALLOWED

SELECT @ERRORID,@ERRORDESC

TMTAMSEXT_POINVOIC_CREATE

The purpose of this procedure is to create an invoice on a purchase order and associate it with a receipt.

Input Parameters

Parameter Name

Parameter Information

Parameter Description

@ERRORID

INTEGER

Output parameter. Error ID supplied if an error occurs during the execution.

@ORDERID

INTEGER, Required

Must be a valid Order ID for a purchase order.

@INVOICENO

VARCHAR, (24), Required

Invoice number for order

@INVDATE

DATETIME, Required

The date of the invoice.

@AMTBILLED

NUMERIC (15,6)

The amount of the invoice. If not provided, will default to 0.00.

@AMTPAID

NUMERIC (15,6)

The amount paid on the invoice. If not provided, will default to 0.00.

@CHECKNO

CHAR (24)

The check number used to pay the invoice.

@PAYDATE

DATETIME

The date the invoice was paid.

@DESCRIP

VARCHAR (60)

A description for the invoice.

@DISCPCT

NUMERIC(15,6)

The amount of discount taken as a percentage.

@DISCTAKEN

CHAR (1)

Identifies if a discount was taken. Y and N are valid values. Defaults to N if not provided.

@COMMENTS

VARCHAR (Max)

Comments concerning the invoice.

@PACKLISTNO

VARCHAR (12)

The packing list number for the invoice.

@REMITVENDORID

VARCHAR (12)

The remit to vendor id.

@PAYMETHOD

VARCHAR, (12)

The paymethod used to pay the invoice. This must be a valid TMT paymethod if provided. Standard paymethods provided are CASH, MASTERCARD, ONACCOUNT, and VISA.

@ MODIFIEDBY

VARCHAR (40)

Identifies who modified or created the invoice record in the system. If not provided, will use the SQL user that executed the stored procedure.

@ AMOUNTPAIDUPDATE

VARCHAR (1)

Determines if the amount paid will be updated. Valid values are Y and N. If N, only the comments will be updated on the invoice. If not provided, defaults to Y.

@SECTION

INTEGER

The receipt to associate the invoice with on the purchase order. If more than one section needs to be associated with the invoice, the stored procedure can be executed multiple times with the same values with each section number.

TMTAMSEXT_POINVOIC_CREATE Example:
DECLARE @ERRORID int, @ORDERID int, @INVOICENO VARCHAR(24), @INVDATE datetime, @AMTBILLED NUMERIC(15,6), @AMTPAID NUMERIC(15,6), @CHECKNO CHAR(24), @PAYDATE datetime, @DESCRIP VARCHAR(60), @DISCPCT NUMERIC(15,6), @DISCTAKEN CHAR(1), @COMMENTS VARCHAR(max), @PACKLISTNO VARCHAR(12), @REMITVENDORID VARCHAR(12), @PAYMETHOD VARCHAR(12), @MODIFIEDBY VARCHAR(40), @AMOUNTPAIDUPDATE CHAR(1), @SECTION int

— Set Parameters
SET @ORDERID = 573
SET @INVOICENO = '20150908-1'
SET @INVDATE = '08/01/2015'
SET @AMTBILLED = 274.43
SET @SECTION =675

EXECUTE [dbo].[TMTAMSEXT_POINVOIC_CREATE] @ERRORID OUTPUT, @ORDERID, @INVOICENO, @INVDATE, @AMTBILLED, @AMTPAID, @CHECKNO, @PAYDATE, @DESCRIP, @DISCPCT, @DISCTAKEN, @COMMENTS, @PACKLISTNO, @REMITVENDORID, @PAYMETHOD, @MODIFIEDBY, @AMOUNTPAIDUPDATE, @SECTION

— Return the error code
SELECT @ERRORID