Fetching Item Pricing from MAS 500

MAS 500 has many, many stored procedures that allow various actions to be performed while enforcing business rules, such as adding, updating, or removing records, or retrieving values from the system.  This article will describe one of the more useful procedures available for fetching pricing data from MAS 500.

The procedure to retrieve pricing is called "spimIMSItemGetItemPrice". 

Inputs

The procedure takes the following Input parameters:

@_iWhskeKey – Integer value and represents the warehouse key for the item to be priced. Optional.

@_iItemKey – Integer value representing the item key for the item to be priced. Mandatory.

@_iCustKey – Integer value representing the customer key that the item is being priced for.  Optional.

@_iCustAddrKey – Integer value representing the customer’s address key.   Optional.

@_iCurrid – Varchar(3) representing the currency ID of the currency to use in pricing.  Optional.

@_iReqUOMKey – Integer value describing the unit of measure for the item being priced.  Mandatory.

@_iQuantity – Float, describes the quantity of items in relation to the UOM being priced.  Mandatory.

@_iCompanyID – Varchar(3) represents the MAS Company ID that is requesting the pricing information.  Mandatory.

@_iDate – Datetime represents the date the price request is for.  This parameter can influence the result if using date based pricing.  Mandatory.

@_iExchRate – Float, identifies the currency exchange rate in the case of multi-currency requests.  Optional.

@_iPriceInquiry – SmallInt.  Flag to determine if it is a price inquiry or retrieval.  Use 0 for a retrieval, 1 for an inquiry.  Assumes 0 if not value entered.

@_iConfrimNo – Varchar(10).  A number used as a reference for Internet Orders, from tsoSalesOrder.

Outputs

The procedure returns the following output parameters:

@_oReturnValue  – Integer result of the procedure.  The following are the results that can be generated:

— 0                     : Execution OK.
— -101                  : Unpredictable Error.
— 2                     : Invalid Warehouse Key.
— 3                     : Item not specified.
— 4                     : Invalid Item Key.
— 5                     : Price UOM Key NULL.
— 6                     : Invalid Customer Key.
— 7                     : Invalid Customer Address Key.
— 8                     : Invalid Currency ID.
— 9                     : Currency ID passed is not in use.
— 10                    : UOM is not supplied.
— 11                    : Invalid UOM Key.
— 12                    : Quantity is not supplied.
— 13                    : Quantity cannot be less than 0.
— 14                    : Business date is not supplied.
— 15                    : Multi-currency Option is not available for this company.
— 16                    : Error in Unit of Measure Conversion.
— 17                    : Error in Multi-currency Conversion.
— 18                    : Cyclic reference found in the Pricing Warehouse.
— 19                    : Exchange rate should be greater than zero.
— 50                    : If arithmetic overflow occurs.

@_oPriceMethod – Integer output Optional parameter for Price Inquiry = 1.  Returns pricing method for grid column heading.

@_oSchedPriceDeterm – Integer result.  Returns how the the Schedule Price was determined, codes 10 through 900.

@_oSystemPriceDeterm – Integer result.  Returns how the Pricing was determined, codes 10 through 900.

@_oUnitPriceFromSched – Float result.  Returns the Unit Price determined by the system before any Sales Promotions..

@_oUnitPriceFromPromo – Float result.  Returns the lowest Unit Price determined by any Sales Promotions.

@_oSalesPromotionID  – Returns varchar(15) representing the Sales Promotion ID that the Unit Price From Promo came from.

@_oSalesPromotionKey – Integer result representing the the Sales Promotion Key for the above Sales Promotion ID.

Other Notes

The following pricing considerations are taken into account in returning a price by the API:

• Customer Price Sheets, including quantity and date based pricing (Maintain Item Price Sheets)
• Customer cost basis pricing (Maintain Inventory)
• Sales promotional pricing (Maintain Promotional Pricing)

This pricing will not return the following pricing methods:

• Contract Pricing (Maintain Contract Pricing – Item Tab and PPG Tab)
• National Account Contract Pricing (Maintain National Account Pricing)
• Matrix Pricing (Matrix Pricing by Customer Group)
• Inventory pricing (Maintain Inventory Pricing)
• Stand alone PPG (Maintain Product Price Groups)

These pricing level require the return of a Pricing Key and therefor will not work against this procedure.  Another stored procedure, "spimPriceGetPricingKey" can be used for these methods.

T-SQL Sample

DECLARE
@_oUnitPrice FLOAT,
@_oReturnValue INTEGER,
@_oPriceMethod        INTEGER,
@_oSchedPriceDeterm   SMALLINT,
@_oSystemPriceDeterm  SMALLINT,
@_oUnitPriceFromSched FLOAT,
@_oUnitPriceFromPromo FLOAT,
@_oSalesPromotionID   VARCHAR(15),
@_oSalesPromotionKey  INTEGER

EXEC dbo.spimIMSItemGetItemPrice
4,– @_iWhseKey
’25’,–@_iItemKey
30,–@_iCustKey
133,–@_iCustAddrKey
‘USD’,–@_iCurrID
2 ,–@_iReqUOMKey
5,–@_iQuantity 
‘SOA’,–@_iCompanyID
’10/20/2007′,–@_iDate
null,–@_iExchRate Constant
0,–@_iPriceInquiry Constant
null,–@_iConfirmNo Constant
@_oUnitPrice OUTPUT,
@_oReturnValue OUTPUT,
@_oPriceMethod OUTPUT,
@_oSchedPriceDeterm OUTPUT,
@_oSystemPriceDeterm OUTPUT,
@_oUnitPriceFromSched OUTPUT,
@_oUnitPriceFromPromo OUTPUT,
@_oSalesPromotionID OUTPUT,
@_oSalesPromotionKey OUTPUT

SELECT
@_oUnitPrice unitprice,
@_oReturnValue returnvalue,
@_oPriceMethod PriceMethod,
@_oSchedPriceDeterm SchedDet,
@_oSystemPriceDeterm PriceDet,
@_oUnitPriceFromSched unitpricesched,
@_oUnitPriceFromPromo unitpricepromo,
@_oSalesPromotionID promoid,
@_oSalesPromotionKey promokey