TmxInventory Interface

The following properties define the dynamic behavior of the TmxInventory object. Some properties can only be read and some can only be written.

Name	COM Type	DB Type	Read/ Write	Description
PluNumber	BSTR	A18	Yes/Yes	A SKU, UPC, cross reference number, a bar code or any check digit number used to look up a price.
ItemType	VARIANT	A1	Yes/Yes	Indicates whether the item is a PLU item or a cross reference number item.
CanOrder	BOOL	A1	Yes/Yes	Indicates whether or not this item can be ordered by the way of Suggested Orders.
QuantityMinimum	VARIANT	N10,3	Yes/Yes	Minimum quantity on hand.
QuantityMaximum	VARIANT	N10,3	Yes/Yes	Maximum quantity on hand.
CreationDate	VARIANT	DT	Yes/No	The item creation date on the inventory control system.
QuantityPreissue	VARIANT	N10,3	Yes/No	Quantity of item in pre issued purchase orders.
QuantityCurrent	VARIANT	N10,3	Yes/No	A count indicating the number of a particular item currently in stock at the store.
LastOrderedDate	VARIANT	DT	Yes/No	The date the last time an inventory controlled item was ordered.
LastReceivedDate	VARIANT	DT	Yes/No	The date the inventory controlled item was received.
LastVendorCost	VARIANT	N10,2	Yes/Yes	The vendor cost of the last vendor item received from.
LastVendorID	BSTR	A10	Yes/Yes	Vendor ID of the last vendor item was received from.
LastSoldDate	VARIANT	DT	Yes/No	Contains the date the last time the item was sold.
LastReturnDate	VARIANT	DT	Yes/No	The date when the item was last returned.
PeriodStartDate	VARIANT	DT	Yes/No	Period start date.
InventoryControlled	BOOL	A1	Yes/Yes	Indicates whether or not this item is controlled or monitored through the inventory control system.
QuantityOnOrder	VARIANT	N10,3	Yes/No	Quantity on order in all purchase orders with a status of ‘issues’.
StoreNumber	VARIANT	SI	Yes/No	At the store level this is the number of the current store.
CollectionFormat	VARIANT		No/Yes	Selected properties which clients prefer to put into Collection property.
Collection	BSTR		Yes/No	A string contains those selected fields returned from inquiry method.
LastErrorNumber	VARIANT	Yes/No		The error code returned if there is an error. For known ADO error, the return value is the value returned from ADO function. If it is unknown ADO error, the value is 0x8000FFAAL. If it is TmxApi error, the value is defined as 0x8000FFBBL.
LastError	VARIANT	Yes/No		The message displayed if there is an error. If it is known ADO error, the description is return from ADO Error object. If it is unknown ADO error, the description specifies the method name where the error occurs. If it is TmxApi error, the description is defined with the failure reason starting with 'TmxApi'.
InputRecordsCount	VARIANT	Yes/No		Number of records contained in the update file.
UpdatedRecordsCount	VARIANT	Yes/No		Number of records actually updated.
WindowHandler	VARIANT	No/Yes		Valid Window Handler assigned by clients' application used to communicate to Xpress Server.
ServerName	VARIANT	No/Yes		TCP/IP address of Xpress Server assigned by clients used to establish communication to the Server.
ActiveConnection	VARIANT			The valid connection to the database. This property should be assigned after connecting to the database. For future use.

Method

HRESULT Inquiry (BSTR Item, SkuTypeEnum SkuType)

This method is used to get one record at a time based on the item. After this function call, the properties defined are updated with the current record value and each of them can be retrieved. If clients prefer to retrieve a collection of selected fields, use property Collection. This property defines a return value specified in the CollectionFormat property. For example, if the CollectionFormat is defined as QuantityMinimum,QuantityMaximum, LastOrderedDate, for the found record, the value of the Collection After Inquiry method can be 10,50,98/07/14 14:30:00. For a property with a null value, the return field will be filled with ‘all fields’.

Note Note

The CollectionFormat property should be defined before this method is called if you want to retrieve certain fields in one string.

End of the note.

When there is an error, two properties (LastErrorNumber and LastError) should be used to determine the code of the error and proper description. For LastErrorNumber, there are mainly three categories: known ADO error, unknown ADO error and TmxApi error. For known ADO error, both LastErrorNumber and LastError are returned as the values returned from ADO. For unknown ADO error, the error code is 0x8000FFAAL and the description is based on which function causes the failure. For a TmxApi error (such as the wrong Sku type or cannot open the input file), the error code is defined as 0x8000FFBBL and the description is specified as TmxApi error and the reason.

If there is no record found in the database, the Collection property is defined as No Record Found, LastErrorNumber is defined as 0x8000FFCCL and LastError is defined as TmxApi Error: No Record Found in the Database!.

Note Note

For error handling in other TmxInventory methods, use the same logic to capture the error code and description.

End of the note.

Parameters
BSTR Item	PLU number or reference number depending on the item type.
SkuTypeEnum SkuType	Type of item to be searched. If it is InvItemPLU, the item is PLU number. If it is InvItemCrossReference, the item is a cross reference number.

Return
S_OK	If the record is successfully returned from the database.
S_FALSE	If there is an error during the search process.

Method

HRESULT InquiryBatch (BSTR ItemListFile, BSTR OutputFile)

This method is used to get a batch of records based on the items defined in ItemListFile parameter. After this function is called, all records are put into the defined output file in the format defined in CollectionFormat property. If this property is empty, all fields from the database will be put into the output file. Each time a new output file is generated.

For example, if the CollectionFormat is defined as:

QuantityMinimum,QuantityMaximum,QuantityCurrent

And ItemListFile is defined as:

1111,InvItemPLU

2222,InvItemPLU

3333,InvItemPLU

11,InvItemCrossReference

The output file may be:

10,50,30

18,59,33

15,60,40

19,20,50

If one item is not found in the database, the record in the output file will be ‘No Record Found’ but the inquiry process will not stop until all items are completed.

When there is an error, two properties (LastErrorNumber and LastError) should be used to determine the code of the error and proper description.

Parameters
BSTR ItemListFile	Input file that contains lists of PLU number or reference number and item type. The PLU number and item type is separated with comma ','. The valid item type is InvItemPLU and InvItemCrossReference. If it is InvItemPLU, the corresponding item is PLU number. If it is InvItemCrossReference, the corresponding item is a cross reference number.
BSTR OutputFile	The full file path name for output file. This file contains all found records with selected fields specified in the CollectionFormat property.

Return
S_OK	If the records are successfully returned from the database and written into the output file.
S_FALSE	If there is an error during the search process.

Method

HRESULT ManualAdjustment (BOOL UpdateTLOGFlag, BOOL StartInvControlFlag, VARIANT Cashier, VARIANT TransactionDate, BSTR Item, SkuTypeEnum SkuType, AdjustmentTypeEnum AdjustmentType, VARIANT Quantity, VARIANT ReasonID, BSTR Note)

This method is used to update the inventory_current table, update or insert a new record into inventory_history table and insert a new record into inventory_adjustment table if manual adjustment function is performed. Manual adjustment function may have three different types: loss, gain and inventory adjustment. It will change Quantity Adjusted filed in inventory_history table and provide accurate history information for this inventory operation.

LastErrorNumber and LastError should be used to capture errors properly. If the operation is successful, LastErrorNumber is 0 and LastError is empty.

The txn_manual_inv_adj is also updated during this inventory update operation. UpdateTLOGFlag is used to control whether or not the inventory update transaction is written into transaction TLOG file. If UpdateTLOGFlag is off, the transaction will not be written into the TLOG file. If UpdateTLOGFlag is on, the transaction will be written into TLOG file on the Server. To view the transaction TLOG file, CVTLOG command should be used to convert the transaction file from binary into ASCII format.

StartInvControlFlag is used to control whether or not to automatically start inventory control for an item which is not currently under inventory control. If StartInvControlFlag is on and the item is not currently under inventory control, the item would be automatically added to inventory control and inventory control flag in the PLU table is set to be Y. A new record is inserted into the inventory_current, inventory_history and inventory_adjustment tables.

Parameters
BOOL UpdateTLOGFlag	Flag to indicate whether or not to update TLOG on the Xpress Server. TRUE – Update Tlog FALSE – Not update Tlog
BOOL StartInvControlFlag	Flag to indicate whether or not to automatically start inventory control for the item which is not currently under inventory control. If this flag is set on and the item is not found in inventory_current table, a new record will added into inventory_current and inventory controlled flag in PLU table will be set to be Y. If this flag is set off and the item is not found in inventory_current table, nothing is updated.
VARIANT Cashier	Cashier who made the adjustment. The maximum number is 9999.
VARIANT TransactionDate	Current date of adjustment. The format used should be valid for related database system.
BSTR Item	PLU number or cross reference number depending on the item type.
SkuTypeEnum SkuType	Type of item to be searched. Use one of the enumeration values defined: InvItemPLU — PLU number, InvItemCrossReference — cross reference number.
AdjustmentTypeEnum AdjustmentType	Use one of the enumeration values defined in this interface: InvLoss – inventory loss InvGain – inventory gain InvQuantityOnHand – inventory quantity on hand. If the type of adjustment is loss, the new quantity on hand will be the current quantity minus quantity passed in the next field.
VARIANT Quantity	Quantity indicated by theType of Adjustment field.
BSTR Note	Note attached to Adjustment. Pass empty string if it is not used.

Return
S_OK	If the record is successfully updated.
S_FALSE	If there is an error during the update process.

Method

HRESULT PhysicalInventory (BOOL UpdateTLOGFlag, BOOL StartInvControlFlag,VARIANT Cashier, VARIANT TransactionDate, BSTR Item, SkuTypeEnum SkuType, VARIANT Quantity, VARIANT ReasonID, BSTR Note)

This method is used to update the inventory_current table, update or insert a new record into the inventory_history table, or insert a new record into the inventory_adjustment table for physical inventory operation. This inventory operation changes the quantity on hand data because of performing physical inventory counting and quantity on hand will be equal to the physical quantity entered.

LastErrorNumber and LastError are used to capture errors. If the operation is successful, LastErrorNumber returns 0 and LastError is empty.

The txn_merchandise_inventory table is also updated during the inventory update operation. UpdateTLOGFlagis used to control whether or not the inventory update transaction is written into transaction TLOG file. If UpdateTLOGFlag is off, the transaction will not be written into the TLOG file. If UpdateTLOGFlag is on, the transaction will be written into TLOG file on the Server. To view the transaction TLOG file, CVTLOG command (part of the SAP Application System) should be used to convert the transaction file from binary into ASCII format.

StartInvControlFlag is used to control whether or not to automatically start inventory control for an item which is not currently under inventory control. If StartInvControlFlag is on and the item is not currently under inventory control, the item is automatically added to inventory control and the inventory control flag in the PLU table is set to Y. A new record is inserted into inventory_current, inventory_history, and inventory_adjustment tables respectively.

Parameters
BOOL UpdateTLOGFlag	Flag to indicate whether or not to update the TLOG on the Xpress Server. TRUE – Update the TLOG FALSE – Do not update the TLOG
BOOL StartInvControlFlag	Flag to indicate whether or not to automatically start inventory control for an item which is not currently under inventory control. If this flag is set to On and the item is not found in the inventory_current table, a new record is added to the inventory_current table and the inventory control flag in the PLU table is set to Y. If this flag is set off and the item is not found in theinventory_current table, nothing is updated.
VARIANT Cashier	Cashier who made the physical inventory operation. The maximum number is 9999.
VARIANT TransactionDate	Current date of the operation. The format used should be valid for the related database system.
BSTR Item	PLU number or cross reference number, depending on the item type.
SkuTypeEnum SkuType	Type of item to be searched. Use one of the values defined: InvItemPLU — PLU number, InvItemCrossReference — cross reference number.
VARIANT Quantity	Physical quantity.
VARIANT ReasonID	It is set up by Store Manager. Default value 0 should be used for this operation if you do not use this field.
BSTR Note	Note attached to Physical Inventory operation. Pass empty string if it is not used.

Return
S_OK	If the record is successfully updated.
E_FALSE	If there is an error during the update process.

Method

HRESULT Receiving (BOOL UpdateTLOGFlag, BOOL StartInvControlFlag, VARIANT Cashier, VARIANT TransactionDate, BSTR Item, SkuTypeEnum SkuType, VARIANT Quantity, VARIANT ReasonID, BSTR Note, VARIANT LastVendorCost, BSTR LastVendorID)

This method is used to update inventory_current table, update or insert a new record into inventory_history table and insert a new record into inventory_adjustment table for receiving operation. This inventory operation change quantity on hand, last vendor cost and last vendor and quantity on hand will be the current quantity plus quantity received. This operation could be used when items are received based on the purchase order or from another store.

LastErrorNumber and LastError should be used to capture errors properly. If the operation is successful, LastErrorNumber is 0 and LastError is empty.

The txn_merchandise_receiving will be updated as well during this inventory update operation. UpdateTLOGFlag is used to control whether or not the inventory update transaction is written into transaction TLOG file. If UpdateTLOGFlag is off, the transaction will not be written into the TLOG file. If UpdateTLOGFlag is on, the transaction will be written into TLOG file on the Server. To view the transaction TLOGfile, CVTLOG command (part of SAP Application System) should be used to convert the transaction file from binary into ASCII format. StartInvControlFlag is used to control whether or not to automatically start inventory control for an item which is not currently under inventory control.

If StartInvControlFlag is on and the item is not currently under inventory control, the item would be automatically added to inventory control and inventory control flag in the PLU table is set to be Y. A new record is actually insert into inventory_current, inventory_history and inventory_adjustment respectively.

Parameters
BOOL UpdateTLOGFlag	Flag to indicate whether or not to update Tlog on the Xpress Server. TRUE — Update the TLOG. FALSE — Do not update the TLOG.
BOOL StartInvControlFlag	Flag to indicate whether or not to automatically start inventory control for the item which is not currently under inventory control. If this flag is set to On and the item is not found in the inventory_current table, a new record will added into inventory_current and inventory controlled flag in the PLU table will be set to Y. If this flag is set off and the item is not found in inventory_current table, nothing is updated.
VARIANT Cashier	Cashier who perform the operation. The maximum number is 9999.
VARIANT TransactionDate	Date of the operation. The format used should be valid for related database system.
BSTR Item	PLU number or cross reference number depending on the item type.
SkuTypeEnum SkuType	Type of item to be searched. Use one of the values defined: InvItemPLU — PLU number, InvItemCrossReference — cross reference number.
VARIANT Quantity	Quantity received.
VARIANT ReasonID	It is set up by Store Manager. Default is 0 for other function Ids. If clients do not use this field, the default value 0 should be used.
BSTR Note	Note attached to this operation. Pass empty string if it is not used.
VARIANT LastVendorCost	Used to update inventory_history table. Pass 0 if the field is not used for this inventory update.
VARIANT LastVendorID	Used to update inventory_history table. Pass empty string if the field is not used for this inventory update.

Return
S_OK	If the record is successfully updated.
S_FALSE	If there is an error during the update process.

Method

HRESULT Transfer (BOOL UpdateTLOGFlag, BOOL StartInvControlFlag, VARIANT Cashier, VARIANT TransactionDate, BSTR Item, SkuTypeEnum SkuType, FunctionTypeEnum FunctionType, VARIANT Quantity, VARIANT ReasonID, BSTR Note, BSTR LastVendorID)

This method is used to update the inventory_current table, update or insert a new record into the inventory_history table and insert a new record into inventory_adjustment table for transfer in or transfer out operation. Transfer operation is used when there are items transferred in or out which will affect the quantity on hand.

LastErrorNumber and LastError should be used to capture errors properly. If the operation is successful, LastErrorNumber returns 0 and LastError is empty.

The txn_transfer table will be updated as well during this inventory update operation. UpdateTLOGFlag is used to control whether or not the inventory update transaction is written into transaction TLOG file. If UpdateTLOGFlag is off, the transaction will not be written into the TLOG file. If UpdateTLOGFlag is on, the transaction will be written into TLOG file on the Server. To view the transaction TLOG file, the CVTLOG command (part of the SAP POS application system) should be used to convert the transaction file from binary into ASCII format.

StartInvControlFlag is used to control whether or not to automatically start inventory control for an item which is not currently under inventory control. If StartInvControlFlag is on and the item is not currently under inventory control, the item would be automatically added to inventory control and inventory control flag in the PLU table is set to Y. A new record is actually insert into inventory_current, inventory_history, and inventory_adjustment respectively.

Parameters
BOOL UpdateTLOGFlag	Flag to indicate whether or not to update the TLOG on the Xpress Server. TRUE – Update the TLOG FALSE – Do not update the TLOG
BOOL StartInvControlFlag	Flag to indicate whether or not to automatically start inventory control for the item which is not currently under inventory control. If this flag is set on and the item is not found in inventory_current table, a new record will added into inventory_current and inventory controlled flag in the PLU table will be set to Y. If this flag is set off and the item is not found in the inventory_current table, nothing is updated.
VARIANT Cashier	Cashier who made the transfer operation. The maximum number is 9999.
VARIANT TransactionDate	Date of the operation. The format used should be the valid for related database system.
BSTR Item	PLU number or cross reference number depending on the item type.
SkuTypeEnum SkuType	Type of item to be searched. Use one of the values defined: InvItemPLU — PLU number, InvItemCrossReference — cross reference number.
FunctionTypeEnum FunctionType	One of the values defined: InvTransferIn — the operation is transfer in, InvTransferOut — the operation is transfer out.
VARIANT Quantity	Quantity transferred.
VARIANT ReasonID	It is set up by Store Manager. A default value of 0 should be used for this operation if clients do not use this field.
BSTR Note	Note attached to the operation. Pass empty string if it is not used.
VARIANT LastVendorID	Used to update the inventory_history table. Pass empty string if the field is not used for this inventory update.

Return
S_OK	If the record is successfully updated.
S_FALSE	If there is an error during the update process.

Method

HRESULT StartInventoryControl (VARIANT TransactionDate, BSTR Item, SkuTypeEnum SkyType, BOOL CanOrder, VARIANT MinStockAmt, VARIANT MaxStockAmt, VARIANT OpenStock).

This method is used to start inventory control for an item. It changes inventory controlled field in PLU table to 'Yes' and insert a new record into inventory_current and inventory_history tables.

LastErrorNumber and LastError should be used to capture errors properly. If the operation is successful, LastErrorNumber is 0 and LastError is empty.

Parameters

VARIANT TransactionDate	Date of the operation. The format used should be valid for related database system.
BSTR Item	PLU number or a cross reference number depending on the item type.
SkuTypeEnum SkuType	Type of item to be searched. Use one of the values defined: InvItemPLU — PLU number, InvItemCrossReference — cross reference number.
BOOL CanOrder	If True, this item will be included in the Store Manager’s Suggested Orders function. The default is False.
VARIANT MinStockAmt	Indicates the minimum stock quantity defined for this inventory controlled item.
VARIANT MaxStockAmt	Indicates the maximum stock quantity defined for this inventory controlled item.
VARIANT OpenStock	Open stock quantity for this inventory controlled item.

Return
S_OK	If the record is successfully updated.
S_FALSE	If there is an error during the update process.

Method

HRESULT StopInventoryControl( BSTR Item)

This method is used to stop inventory control for an item. It changes the Inventory Controlled field in the PLU table to No and deletes the related record from the inventory_current and inventory_history tables.

LastErrorNumber and LastError should be used to capture errors properly. If the operation is successful, LastErrorNumber returns 0 and LastError is empty.

Parameters
BSTR Item	PLU number.

Return
S_OK	If the record is successfully updated.
S_FALSE	If there is an error during the update process.

Method

HRESULT ChangeInventoryControl (BSTR Item, BOOL CanOrder, VARIANT MinStockAmt, VARIANT MaxStockAmt)

This method is used to change inventory controlled parameters for the item. It updates inventory_current table with new values specified.

LastErrorNumber and LastError should be used to capture errors properly. If the operation is successful, LastErrorNumber is 0 and LastError is empty.

Parameters
BSTR Item	PLU number
BOOL CanOrder	If TRUE, this item will be included in the Store Managers Suggested Orders function. The default is FALSE.
VARIANT MinStockAmt	Indicates the minimum stock quantity defined for this inventory controlled item.
VARIANT MaxStockAmt	Indicates the maximum stock quantity defined for this inventory controlled item.

Return
S_OK	If the record is successfully updated.
S_FALSE	If there is an error during the update process.

Method

HRESULT UpdateBatch (BOOL UpdateTLOGFlag, BOOL StartInvControlFlag, BSTR FileName)

Parameters
BOOL UpdateTLOGFlag	Flag to indicate whether or not to update TLOG on the Xpress Server. TRUE – Update TLog. FALSE – Not update TLog.
BOOL StartInvControlFlag	Flag to indicate whether or not to automatically start inventory control for the item which is not currently under inventory control. If this flag is set to On and the item is not found in inventory_current table, a new record will added into inventory_current and inventory controlled flag in PLU table will be set to be Y. If this flag is set off and the item is not found in inventory_current table, nothing is updated.
BSTR FileName	Full file path name with the format as specified in Update.

Return
S_OK	If the file is successfully updated.
S_FALSE	If there is an error during the update process.

Method

HRESULT ResetQuantityOnHand ( )

This method is used to reset quantity on hand field to 0 for all items found in inventory_current table. There is no change made to inventory_history and inventory_adjustment.

LastErrorNumber and LastError should be used to capture errors properly if the returned value is S_FALSE. If the operation is successful, LastErrorNumber is 0 and LastError is empty.

Parameters

No parameters are required for this method.

Return
S_OK	If all records are successfully reset.
S_FALSE	If there is an error during the reset process.

This method is used to update inventory information for a list of items. The data type and size should be the same as defined in those update methods (ManualAdjustment, Transfer and so forth). If no data for certain fields, the filed can be left empty, but a ‘,’ (eliminator) is required in order to keep the right field number. Valid or default data should be provided for these mandatory fields.

LastErrorNumber and LastError should be used to capture errors properly. If the operation is successful, LastErrorNumber is 0 and LastError is empty.

is used to control whether or not the inventory update transaction is written into transaction TLOG file. If UpdateTLOGFlag is off, the transaction will not be written into the TLOG file on the server. To view the transaction TLOG file, CVTLOG command should be used to convert the transaction file from binary into ASCII format.

StartInvControlFlag is used to control whether or not to automatically start inventory control for an item which is not currently under inventory control. If StartInvControlFlag is on and the item is not currently under inventory control, the item would be automatically added to inventory control and inventory control flag in the plu table is set to be ‘Y’. A new record is actually insert into inventory_current, inventory_history and inventory_adjustment respectively.

The file (icupdate.asc) format is defined as:

Cashier	(M)
TransactionDate	(M)
Item	(M)
SkuTypeEnum	(M)
AdjustmentTypeEnum	(M)
Quantity	(M)
ReasonID	(M)
FunctionTypeEnum	(M)
Note
LastVendorCost
LastVendorID
CanOrder
MinStockAmt
MaxStockAmt
OpenStock

(M) = mandatory field

Since VARIANT data type is used, all the fields are defined in the format shown above and the method will convert to the proper data format internally. For each record, certain checks have to be done, such as:

number of fields
TransactionDate field cannot be empty
itemtype field should be either InvItemPLU or InvItemCrossReference

If there is any format error or stored procedure error, the record will not be updated and written into a log file (with the same file name as input, but with .log as the file extension. If there were any error to extract the proper file name from input file name, the default one is defined as c:\temp\icupdate.log). Two properties have been defined to indicate how many records are contained in the file and how many records are properly updated:

InputRecordsCount: the number of records defined in the input file
UpdatedRecordsCount: the number of records that have been properly updated

By comparing these two values, you can tell that some records have not been updated into the database if these two values are not the same. You should then check the log file and find which one is failed. After the fixes to those records are done, the function can be called again. This file should be deleted after each use, otherwise the record will be attached to the existing .log file.