DYNAMIC 3i STOCK MASTER TABLE (STKMST) CHANGES AND NEW ACTUAL COSTING TABLE (ACTCOST)

To facilitate the ability of Dynamics 3i to offer and operate under an actual costing methodology, certain items that were associated with the stock master table were re assigned to a new Actual Costing table (ACTCOST). This new table is warehouse dependent in the same manner as the Stock Master table with warehouse and product being the primary and mandatory access key.

Stock Master table items that were removed:
	QTY_ON_HAND
	QTY_PHYSICAL
	QTY_AT_PHYSICAL
	LAST_COST
	AVE_COST
	STANDARD_COST
	MATERIAL_COST
	LABOUR_COST
	SET_UP_COST
	VAR_OVERHEAD_COST
	FIX_OVERHEAD_COST
	COST_PRICE_PER
	LAST_ISSUE_DATE
	DUTY_COST


Fields that were directly moved to new Actual Cost table (ACTCOST) were:
	QTY_ON_HAND
	QTY_AT_PHYSICAL
	AVE_COST
	COST_PRICE_PER


The follow fields were removed / re-defined as they were redundant in their definition on the main inventory costing table - PRCMST:
	MATERAIL _COST
	LABOUR_COST
	SET_UP_COST
	VAR_OVERHEAD_COST
	FIX_OVERHEAD_COST
	DUTY_COST
	STANDARD_COST (as it is calculated from above items)

It should also be noted that the above fields are now “bundled” in what is called an Advanced Data Type (ADT) field. On the main Inventory Cost table (PRCMST) there are two new fields CUR_COSTS and NEW_COSTS with special types. These are the ADT fields that hold the current and new cost information about a product. Note. Currently there are three tables within Dynamic3i that utilize what is know as Advance Data Type fields (ADT) which are not supported by some third part ODBC connection software. In order to access information with these data types database views must first be defined. Since views are treated as tables in the database, the information can be accessed in the same manner and element selection process as if the view were an actual table. Current tables in Dynamic3i with ADT type fields: PRCMST - For new costs and current cost elements RMAHMST - For address information SADETIAL - For shipment dates Within Dynamic3i version 3.2 and higher the following views are pre-defined in order to access the ADT type information from the main Dynamic3i costing table (PRCMST), Retrun Authorization table (RMAHMST) and the Sales Analysis Detail Table (SADETAIL) Pre-Defined Views: PRCMSTVIEW PRCHISTVIEW RMAHMSTVIEW SADETAILVIEW The elements of these views can be seen after an ODBC - Data source connection is established. Below is the new PRCMST table as defined under version 3.x:

Name	Null? Type

WAREHOUSE_NO	NOT NULL VARCHAR2(3)
PRODUCT_NO	NOT NULL VARCHAR2(25)
CURRENCY_NO	NOT NULL VARCHAR2(4)
EFFECTIVE_DATE_NEW	DATE
EFFECTIVE_DATE_CUR	DATE
EXCHANGE_RATE_NEW	NUMBER(9,6)
EXCHANGE_RATE_CUR	NUMBER(9,6)
DUTY_RATE_NEW	NUMBER(18,2)
DUTY_RATE_CUR	NUMBER(18,2)
F_B_I_RATE_NEW	NUMBER(18,2)
F_B_I_RATE_CUR	NUMBER(18,2)
ENTRY_DATE	NOT NULL DATE
LAST_CHANGE_DATE	DATE
ENTRY_USER	NOT NULL VARCHAR2(30)
LAST_CHANGE_USER	VARCHAR2(30)
TARIFF_CODE	VARCHAR2(10)
CUR_COSTS	SDCTYPE.PRC_COSTS
NEW_COSTS	SDCTYPE.PRC_COSTS

CUR_COSTS and NEW_COSTS can be thought of as a table within a table. Each with a set of items associated with them. One complete set for current costs (CUR_COSTS) and one for new costs (NEW_COSTS). Each accessed with a preface of either cur_costs or new_cost depending upon what is required.

CUR_COSTS	SDCTYPE.PRC_COSTS

TRANSFER	NUMBER(18,3)
EXCHANGE	NUMBER(18,3)
DUTY	NUMBER(18,3)
F_B_I	NUMBER(18,3)
SET_UP	NUMBER(18,3)
LABOUR	NUMBER(18,3)
VAR_OVERHEAD	NUMBER(18,3)
FIX_OVERHEAD	NUMBER(18,3)
ROYALTIES	NUMBER(18,3)
INSTRUCTIONS	NUMBER(18,3)
PACKAGING	NUMBER(18,3)
ASSEMBLY	NUMBER(18,3)
MISC	NUMBER(18,3)
PRICE_PER	NUMBER(4)
FUNCTION LANDED_COST	RETURNS NUMBER
FUNCTION OTHER_COST	RETURNS NUMBER
FUNCTION MATERIAL_COST	RETURNS NUMBER
FUNCTION STANDARD_COST	RETURNS NUMBER

NEW_COSTS	SDCTYPE.PRC_COSTS

TRANSFER	NUMBER(18,3)
EXCHANGE	NUMBER(18,3)
DUTY	NUMBER(18,3)
F_B_I	NUMBER(18,3)
SET_UP	NUMBER(18,3)
LABOUR	NUMBER(18,3)
VAR_OVERHEAD	NUMBER(18,3)
FIX_OVERHEAD	NUMBER(18,3)
ROYALTIES	NUMBER(18,3)
INSTRUCTIONS	NUMBER(18,3)
PACKAGING	NUMBER(18,3)
ASSEMBLY	NUMBER(18,3)
MISC	NUMBER(18,3)
PRICE_PER	NUMBER(4)
FUNCTION LANDED_COST	RETURNS NUMBER
FUNCTION OTHER_COST	RETURNS NUMBER
FUNCTION MATERIAL_COST	RETURNS NUMBER
FUNCTION STANDARD_COST	RETURNS NUMBER

Note. There are four special functions to return the Landed cost, Other cost, Material cost and the Standard cost of an item.
To select from these fields it is imperative that an alias be used.

Examples:

select a.cur_costs.labour from prcmst a

To use one of the special functions the std. Oracle function call is used with aliases:

select a.cur_costs.standard_cost() from prcmst a (Note. standard_cost is a function)

The QTY_PHYSICAL field was removed, as it was not used. The LAST_COST and LAST_ISSUE_DATE of an item is now calculated directly from the inventory movement table (IMVMST).

New Actual Cost table (ACTCOST):


	WAREHOUSE_NO
	PRODUCT_NO
	AVAILABLE
	DATE_RECEIVED
	MOVEMENT_CODE
	DOCUMENT
	BIN_LOCATION
	LOT_NUMBER
	QTY_RECEIVED
	QTY_ON_HAND
	REPORTING_COST
	AVE_COST
	COST_PRICE_PER
	ENTRY_DATE
	LAST_CHANGE_DATE
	ENTRY_USER
	LAST_CHANGE_USER
	QTY_AT_PHYSICAL

If Actual Costing is used, the new Actual Cost table (ACTCOST) will carry the actual cost information of the item. This table also replaces the Lot/Bin table (LOTBIN) and in conjunction with a new Lot table (LOTMST) and existing Bin table (BINMST) tracks all information and cost associated with lot and/or binned controlled items.

The REPORTING _COST item olds the associated cost of the item. If Standard Costing is used then this is the standard cost. If Average Costing is used then this is the average cost and if Actual Costing is used then this is the actual cost.

To obtain the current QTY_ON_HAND of an item under version 3.x the ACTCOST table must be summed if actual costing is being used. If standard costing is being used there will only be one entry in this table.

Example used to get the current qty_on_hand:

Select sum(qty_on_hand)
from actcost
where product_no = :product
and warehouse_no = :warehouse

If existing third party interfaces or database queries such as Microsoft Excel – MS Query are using fields that were on the stkmst then these queries would have to be re-written.

Example:

Old query:


SELECT PRDMST.PRODUCT_NO,
	PRDMST.DESCRIPTION,
	STKMST.QTY_SAFETY,
	STKMST.QTY_MINIMUM,
	STKMST.QTY_ON_WORDER,
	STKMST.QTY_ON_HAND,
	STKMST.QTY_BACK_ORDER,
	STKMST.QTY_ALLOCATED_SALES,
	STKMST.PRODUCT_NO,
	STKMST.WAREHOUSE_NO

FROM PRDMST, STKMST
WHERE PRDMST.PRODUCT_NO = STKMST.PRODUCT_NO
	AND ((PRDMST.PRODUCT_NO<'600-00-0000') AND (STKMST.QTY_SAFETY>0)
	AND (STKMST.WAREHOUSE_NO='1'))


New Query:

SELECT PRDMST.PRODUCT_NO,
	PRDMST.DESCRIPTION,
	STKMST.QTY_SAFETY,
	STKMST.QTY_MINIMUM,
	STKMST.QTY_ON_WORDER,
	stk.QTY_ON_HAND,
	STKMST.QTY_BACK_ORDER,
	STKMST.QTY_ALLOCATED_SALES,
	STKMST.PRODUCT_NO,
	STKMST.WAREHOUSE_NO

FROM PRDMST, STKMST,
	(select product_no, warehouse_no, sum(qty_on_hand) qty_on_hand
	from actcost
	group by product_no, warehouse_no) stk

WHERE PRDMST.PRODUCT_NO = STKMST.PRODUCT_NO
	and stk.product_no = prdmst.product_no
	and stk.warehouse_no = stkmst.warehouse_no
	AND ((PRDMST.PRODUCT_NO<'600-00-0000')
	AND (STKMST.QTY_SAFETY>0) AND (STKMST.WAREHOUSE_NO='1'))

Notice that the ACTCOST query for the qty_on_hand as been written as an embedded query table called ‘stk’

In the case of a lot/bin controlled item this table is treated in the same manner as the old LOTBIN table as there will be multiple records for any given lot/bin combinations. Queries can be joined with the new LOTMST table to obtain expiry date, unit and factor information.