Database tables

The management of stock in Sage X3 is organized around as set of tables that represent:

The stock itself. This stock is identified in the database by records in the STOCK table. Each of these records corresponds to a stock line. The management rules for these lines are detailed in a technical annex. A group of stock lines characterized by various attributes (lot, status, location...) corresponds to a given product, in a given warehouse. Each stock line contains a global quantity (the value, which is used as the basis for the calculation of the fiscal value of the stock, is stored in other tables).

The stock movement journal. It is identified in the database as records in the STOJOU and STOJOUVAL tables. It is used to recover in a chronological order the movements carried out whether as a quantity (receipt, issue or internal movement) or as a value only (depreciations, miscellaneous adjustments; as a matter of fact, the quantity field is entered even if the movement does not change the quantity in stock, because in the event of valued movements, it is important to know the quantity on which the change in value applies). Each record in this journal contains the characteristics of the stock line movement without formally pointing to it and it also points to the original movement that justifies it (document and document line at the origin of the movement: packing slip, material tracking, receipt note...). Each movement contains two values: the movement value, determined from setup rules, which impacts the global value of the stock contained on the lines, and the value originating from the original document. If the valuation method includes two methods, these values are displayed for both methods.

The lot table. It is identified in the database by records in the STOLOT table. It associates a lot and a given product with default characteristics (all warehouses included). It contains a total quantity and an informative value (expressed in reporting currency).

A table of the product/warehouse totals, ITMMVT, which contains a total quantity by status (A,Q,R, internal, dock, loaned, allocated, in subcontract...) as well as totals (base, quantity) used to carry out the calculation of the weighed average price. It also contains values such as the last purchase price, the last receipt price, the date of the last receipt and the last document. The information above is redundant: it can be traced back, for a given product at a given warehouse, by taking the last stock count values and by examining the stock journal.

A table containing product/warehouse totals per month, ITMMVTHIS, with totals by period and by fiscal year. This table is updated based on the allocation date of each movement.

A table containing the elements necessary for the management of the FIFO or LIFO stack, STOCOST, containing, for each product and warehouse, a set of lines characterized by a quantity and a global value. It should be noted that this table is used in the FIFO or LIFO valuation, but it has no links with physical locations or specific lots.

A table identifying allocated stock, STOALL, which is used to justify the allocated quantities and the shortages for each product and warehouse.

The rest of this document explains how the movements are recorded in the database, focusing mainly on how the valuation bases used for the weighed average cost and the FIFO/LIFO are updated. The calculations that are carried out depend on the valuation method used. This method is defined by a group of rules attached to an identifying code. This code is associated with the product-site, the valued being initialized from the product category.

Recording a stock movement

A stock movement is justified by a given line in a source document (that can be for example, production reporting, delivery advice note, receipt note, physical stock count note...). Its entry will create at least one line in the stock journal (there can be several if the line in question involves the movement of several lots or several units or serial numbers....).

In theory, this source document contains a quantity and a value (sometimes only one or the other). The following two values are found in the stock journal: the value carried by the source document and the movement value, determined according to the rules that have been set up (amongst which figures the possibility to pick up the value from the source document).

In fiscal terms, the stock valuation method used can be Standard cost, Revised standard cost, Average cost, FIFO or LIFO. According to the country and conditions, certain methods can be or not be allowed, these choices must therefore be confirmed by an accounting expert. This valuation rule can vary as a function of the product and even the product-site pair. This is authorized in exceptional cases (for example, different companies), but as a general rule, the most fiscally appropriate rules will be chosen.

By default, once this fiscal valuation rule has been chosen, the variation in the value of the stock as a result of a movement follows the simple rule shown below (that also applies for quantities):

final value = initial value+ movement value

The movement value is of course supposed to be signed (negative sign for an issue and positive sign for a receipt). When this rule is followed and the movement values are stored in the journal, it is easy to always have a true image of the fiscal value of the stocks in accounting if the stock movements are posted from the stock journal. It should also be noted that this posting can be carried out each time a movement is made or in a more global way (end of day, period or on request).

The rule for the calculation of this value is simple to apply in the case of an issue and is applied without exception: the stock valuation method is used to associate a value with the issued quantity and decreases the residual stock value by the same amount. This value is obtained either from the weighted average cost for the site (the global stock value is decreased in proportion to the issued quantity), or by decreasing the site price stack according to the selected order (FIFO or LIFO), or by using the Standard cost or the Revised standard cost associated with the site. In order to manage the exceptional cases where a zero price arises from this calculation a default value is planned.

On receipt, the basic principle consists in increasing the stock quantity and value (are updated the FIFO/LIFO stack and the quantity and value of the product-site used in the calculation of the Average Cost from the order amount). The receipt value of the movement is determined by a rule (defined by product-site) that can be Standard cost, Revised standard cost (the receipt values are thus fixed during a given period), or Order price (in this case, the value comes from the source document). Here also, a default value is planned if the value obtained is equal to zero. But the specific rules for the calculation of the stock value straight after a receipt are a little more complex and for two reasons :

    Firstly because the movement value cannot be perfectly identified at the time when the movement is recorded. The movement will nevertheless be carried out with an estimated value, but it is possible once the actual value of the movement is known, to perform an adjustment at a later date (described later in this document). These adjustments are converted into additional lines in the stock journal. These lines will also be posted if the accounting stock interface is used.

    then, because it can happen for certain exceptional movements, that a valuation rule is required that is not used by default for the issues. It is possible to set up this using an exception table (this rule will then be associated with an issue type, even a sub-type, identified by a code carried by the stock transaction). This exceptional receipt type can have impact on the accounting, in particular if the original movement has a different value to the receipt value retained. Since these two values are stored in the stock journal, it is possible to set up their use in accounting.

The STOJOUVAL table contains all the valuation information of stock movement. It is used, among others, to stock cost breakdown. This is a detail of the various fields that are available:

  • O*: Order amounts
  • DO*: Order amount delta
  • V*: Valuation amount
  • DV*: Valuation amount delta
  • V2*: Amount of the second valuation method
  • DV2*: Delta amount of the second valuation method
  • DEV*: Non-absorbed amount
  • DEV*2: Non-absorbed amount of the second valuation method
  • *MATTOT: Material total = total(MATCST)
  • *MACTOT: Machine total = total(MACCST)
  • *LABTOT: Labor cost total = total (LABCST)
  • *SCOTOT: Sub-contract proportion
  • *INVDTA: Invoicing elements proportion
  • *OVEMAT: Material overall costs proportion
  • *OVEMAC: Machine overall costs proportion
  • *OVELAB: Labor overall cost proportion
  • *OVESCO: Sub-contract overall proportion

Managing the value totals

The value totals contained in the tables are of two types: on the one hand, the FIFO/LIFO stack, on the other hand, the total in value from the ITMMVT table, which is used to calculate the weighted average cost. In both cases, the totals are up to date site by site. The global valuation for a company is obtained by totaling the values of the different sites, and the global weighted average cost for a company, by calculating the weighted average of the different weighted average costs of each site.

On each site, the basis of the weighted average cost calculation is updated in a very simple way:

*   On receipt, the receipt value is added to the value of the current stock value (it is the same for the quantities)

*   On issue, the quantities are decreased and the stock value is updated in proportion to the issued quantity.

The update of the FIFO/LIFO stack is performed for each site in the following way:

*   On receipt, a entry is created in the stack for each receipt movement, with the totaled quantities and corresponding prices.

*   On issue, the last or first entry in the stack is decreased, depending on the context.

It is important to note that this management of the stock value has no relation with the physical management. In other words, even if a product is perfectly identified (by its serial number, for example) and is not the first receipt when working with FIFO, the issue value is never recovered from the original value, but by following the issue valuation rule.

However, in the event of a receipt on returned goods, reversing the original movement is used, according to the setup, to recover the original movement price; if the setup of the receipt values uses the order price, it will be perfectly consistent. In the same way, a transfer from one site to another creates two identical movements (one an issue and the other a receipt). If the receipts are conducted based on the Order price, then the transfer will take place with a price that is identical on both sides.

Deleting or modifying the movement

Some movements can be canceled after they have been performed. This can be managed as follows:

*   The deletion of a receipt is only possible if the stock whose characteristics match the receipt is still available. From the valuation viewpoint, the deletion is processed as a negative receipt, that is to say the movement is carried out in the opposite direction. In addition, the entries in the stock journal corresponding to the original movement and its cancellation are flagged (the REGFLG flag is set to 2).

*   The deletion of an issue is only possible if there is still space available in the location from where the products were issued. From the FIFO stack viewpoint, it is not always possible to re-establish the exact situation, because the empty stack entries are deleted as they appear. In the worst case, a FIFO stack entry is recreated with the value of the issue (that can be a weighted average of two stack entries having been purged in the meantime).

The modification of a movement (whether this is a quantity or value change) is processed by the cancellation and deletion, taking into account the controls previously defined.

Adjustment of a movement

The adjustment of a movement corresponds to the case where a movement, entered as a given value at a given moment, has its value changed by the entry of another document (and not by the modification of the source document).

Below are several concrete examples:

* A supplier receipt has been entered at a given price, the invoice arrives with a different price.

* Once the supplier invoice has been saved, an additional invoice (customs charges, carrier charges) to be distributed over the received products is received (this can occur more than once for the same products).

* A production reporting entry that has created receipts at a price calculated based on technical data; a production cost calculation establishes the final receipt price, which is different.

In the particular case of over-invoicing (the invoiced quantity is higher than the received quantity), it is considered that other receipts are still expected and that they will absorb the additional amount linked to the additional invoiced quantity. In that case, only the price difference by quantity and the invoicing elements are integrated in the first receipt.

Example:

  • An order of 60 products at a unit price of €11, and €60 of invoicing elements.
  • A first receipt of 50 products is carried out at a unit price of €10.
  • The over-invoicing of this receipt is calculated in the following way:
    - 60 products at €11 make €660
    - an invoicing element at €60 in proportion to the quantities.

    The cost price of the line is therefore (660 + 60) / 60 = €12.
  • The stock valuation processing does not determine:
    - €11 for the material, but €10.80
    - €1 for the invoicing elements, but €1.20.
    Indeed, the whole value of the invoicing elements is applied on the first over-invoiced receipt: 60 / 50 = €1.20. The material is therefore valued at: €12 - €1,20 = €10,80.
  • A second receipt of 10 products is made at a cost price of €12.

    The stock valuation calculation considers only the material at €12 because the invoicing elements have already been integrated to the stocks.

    When taking into account both calculations, the 'material / invoicing element' distribution is calculated as follows:

    - Material part: (50 * 10,80) + (10 * 12) = 540 + 120 = 660
    - Invoicing elements part: 50 * 1.20 = 60

In all the cases, these adjustments are made by creating additional entries in the stock journals. It should be noted that it is possible to prevent price adjustments for certain products( Adjustment (yes/no) flag is defined in the valuation methods setup). If this adjustment is made, it is carried out as follows:

  • The modifications are made on the total value used to calculate the moving average price. If the number of products remaining is low, the variances to be distributed can have a big impact on the moving average price. It is therefore possible to limit the variation in percentage of the moving average price that results in a given value. The MAXABSCOD - Valuation variance absorption parameter (chapter STO, group VAL) defines if this limitation must be used. It can take the values No, absorption base site, absorption base site/lot. If MAXABSCOD is set to No, the variation in the moving average price is not limited. If it is set to absorption base site, the weighted average price for the site is limited. If it is set to absorption base site/lot, the variation of the average price for the lot at the site is limited (and consequently, the variation of the weighted average price in an even more drastic way). The maximum variation range in percentage, when it is applied, is provided by the MAXABSPER - % maximum of over-absorption parameter (chapter STO, group VAL). The value most frequently used is 10, in other words, the variation of the weighted average cost or of the weighted average cost of the lot is limited to + or - 10%. If a variation limitation has been used, the variance that could not be absorbed is stored in the stock journal (It can be posted using an appropriate account).
  • The value modifications are distributed over all the entries in the FIFO stack in proportion to the quantities that are still available in these stack entries.

For instance:

  • A receipt of 36 products is carried out at a unit price of €10, although the previous stock is equal to zero.
  • An issue of 12 products is carried out.
  • A receipt of 6 products is carried out at a unit price of €18.
  • The receipt of 36 products is valued at €11; thus it is necessary to apply a variance of €36 in total, on the 30 remaining products, that is, a revaluation of €1.20 per product.

    Because the residual value of the stock before revaluation is equal to 24*10+6*18, that is €348 (weighted average cost=€11.60), if the maximum absorption setup is set to 10%, it is not possible to include more than €34.80 to the stock value.
    Therefore, the stock value will move to €382.80, which yields a weighted average cost of €12.76, and the variance is posted separately (€1.20) in the stock journal.

    In the FIFO stack, this variance is distributed in proportion to the quantities.
    Thus, a sixth (6/30) of the absorbed variance is distributed to the last entry in the stack.
    €5.80 is thus distributed over 6 products: this represents an increase in the unit value of these 6 products of €0.96666 (it was €18 at the start).
    The remainder (5/6) of the variance, i.e. €29, is distributed over the remaining 24 products, that is, an increase in the unit value of €1.2083 (it started as €10).

* Adjusting receipt movements: when breaking down the weighted average cost by cost group, the potential negative values originating from the calculation are distributed over other cost groups as follows:

    • If the negative cost applies to the material cost group, this amount is distributed in the following order of priority:
      - on the other material cost groups
      - on the non material cost groups (machine, labor and sub-contracting costs)
      - on the four overheads (material, machine, labor and sub-contracting)
    • If the negative cost applies to the non material cost group, this amount is distributed in the following order of priority:
      - on the other non material cost groups
      - on the material cost groups
      - on the four overheads
    • If the negative cost applies to the material overheads, this amount is distributed in the following order of priority:
      - on the other overheads
      - on the other material cost groups
      - on the non material cost groups
    • If the negative cost applies to the non material overheads, this amount is distributed in the following order of priority:
      - on the other overheads
      - on the other non material cost groups
      - on the material cost groups

SEEINFO When such an adjustment is performed, the data recorded in the next fields of the STOJOUVAL table are irrelevant. This data is related to the second valuation method, which does not require any weighted average cost breakdown:

·  DV2*: Delta amount of the second valuation method

·  DEV*2: Non-absorbed amount of the second valuation method.

* Stock adjustment in case of purchase credit memo on invoices: credit memos in value and in quantity.

Credit memo in value

Credit memo in quantity

Credit memo quantity equal to the invoice and different price

Credit memo quantity equal to the invoice and same price


Quantity different from the invoice (price equal or different)
  • Case of credit memos in value:
    This mainly concerns price corrections.
    A credit memo in value only impacts the stock value. The stock valuation is based on the variance between the credit memo and invoice.
    The order or receipt are always considered as invoiced.

    Example 1: credit memo with no quantity

    Units

    Unit price

    Amount

    Stock value

    Administrative

    10

    10

    100:

    100:

    Invoice

    10

    9

    90

    90

    Credit memo

    0

    6

    6

    84


    The receipt is considered as invoiced and closed.

    Example 2: credit memo on overall quantity

    Units

    Unit price

    Amount

    Stock value

    Administrative

    10

    10

    100:

    100:

    Invoice

    10

    9

    90

    90

    Credit memo

    10

    1

    10

    80


    The receipt is considered as invoiced and closed.

  • Case of credit memos in quantity:
    A credit memo in quantity decreases the amount invoiced on the order and on the receipt. The stock value is updated when the corrected invoice price is different from that of the receipt, or when the credit memo price is different the that of the invoice. The stock valuation is based on the variance between the credit memo and receipt.

    Example 1: unit price amount of the credit memo greater than the invoiced amount

    Units

    Unit price

    Amount

    Stock value

    Administrative

    10

    10

    100:

    100:

    Invoice

    10

    9

    90

    90

    Credit memo

    1

    12

    12

    88 (1)
    Remaining to be invoiced: 1


    (1)  88 = (credit memo unit price - receipt unit price) * (- credit memo qty) =  12 - 10 * (-1) = -2
    The stock value was 90. It was adjusted through the calculation: 90 -2 = 88.

    The full calculation is as follows:
    Stock amount = 9 UN (invoiced quantity) * 9 (invoiced price for 9 UN) + 1 UN * 10 (not invoiced receipt price) + (invoice price = 9 - credit memo price =12) * 1 UN (that should be equal to the invoice price).
    Stock amount = 81 + 10 - 3 = 88

    Let us assume now that a corrective invoice is received for a quantity of 1 unit and a unit price of 9. The stock value is: 90 - 12 + 9 = 87.

    Example 2: unit price amount of the credit memo lower than the invoiced amount

    Units

    Unit price

    Amount

    Stock value

    Administrative

    10

    10

    100:

    100:

    Invoice

    10

    9

    90

    90

    Credit memo

    1

    6

    6

    94 (2)
    Remaining to be invoiced: 1


    (2)  94 = (credit memo unit price - receipt unit price) * (- credit memo qty) =  6 - 10 * (-1) = 4
    The stock value was 90. It was adjusted through the calculation: 90 + 4 = 94.

    The full calculation is as follows:
    Stock amount = 9 UN (invoiced quantity) * 9 (invoiced price for 9 UN) + 1 UN * 10 (not invoiced receipt price) + (invoice price = 9 - credit memo price =6) * 1 UN (that should be equal to the invoice price).
    Stock amount = 81 + 10 + 3 = 94

    Let us assume now that a corrective invoice is received for a quantity of 1 unit and a unit price of 9. The stock value is: 90 - 6 + 9 = 93.

    Example 3: unit price amount of the credit memo equal to the invoiced amount

    Units

    Unit price

    Amount

    Stock value

    Administrative

    10

    10

    100:

    100:

    Invoice

    10

    9

    90

    90

    Credit memo

    1

    9

    9

    91 (3)
    Remaining to be invoiced: 1


    (3)  91 = (credit memo unit price - receipt unit price) * (- credit memo qty) =  9 - 10 * (-1) = 1
    The stock value was 90. It was adjusted through the calculation: 90 + 1 = 91.

    The full calculation is as follows:
    Stock amount = 9 UN (invoiced quantity) * 9 (invoiced price for 9 UN) + 1 UN * 10 (not invoiced receipt price) + (invoice price = 9 - credit memo price =9) * 1 UN (that should be equal to the invoice price).
    Stock amount = 81 + 10 = 91

    Let us assume now that a corrective invoice is received for a quantity of 1 unit and a unit price of 9. The stock value is: 90 - 9 + 9 = 90.

*Stock adjustment in the event of credit memos on invoices when the 'PIHCPR - Invoice price adjustment' parameter is set to 'With landed costs'.
In that case, the landed costs are directly taken into account in the invoice and they are never matched against an additional invoice. Consequently, they represent costs added to stock, like, for example, global costs that are not subject to invoicing.

When a credit memo is associated with such an invoice (that includes landed costs), the part corresponding to these additional costs is never reversed. The stock adjustment linked to the credit memo only reverses those differences concerning quantities or prices.

Example 1:

  • Receipt of 10 units at $10 with a landed cost coefficient of 1.1 and a fixed cost of $1 per unit.
    The stock cost is equal to (10*10)*1.1+10 = $120.
  • The invoice is saved with a unit price of $20 with a landed cost coefficient of 1.1 and a fixed cost of $1 per unit.
    The adjusted stock cost is equal to (20*10)*1.1+10 = $230.
  • Now, a credit memo is created to reduce the invoice by $1 per unit and decrease the invoiced price to $19.
    In that case, the costs linked to the landed costs are not impacted.

The adjusted stock cost is equal to (20*10*1.1)+10 - (10*1) = $220.

Example 2:

  • Receipt of 10 units at $10 with a landed cost coefficient of 1.1 and a fixed cost of $1 per unit.
    The stock cost is equal to (10*10)*1.1+10 = $120.
  • The invoice is saved with a unit price of $20 with a landed cost coefficient of 1.1 and a fixed cost of $1 per unit.
    The adjusted stock cost is equal to (20*10)*1.1+10 = $230.
  • Now, a credit memo is created for the invoiced price as a whole, with a view to cancel the invoice (credit memo of 10 units at $20).
    In that case, the landed costs are applied to the cost declared on receipt.

The adjusted stock cost is equal to (10*10)*1.1+10 = $120.


Physical stock count variance

A positive physical stock count variance is processed like a receipt, with a negative physical stock count variance being processed like an issue. In addition to the update of the quantities in the stock and their adjustment in the stock line detail and lot totals, this variance has an impact on the valuation basis. The valuation methods setup is used to define the positive and negative physical stock count variance values in a different way to a normal receipt or issue. It should be noted that the validation of a physical stock count both creates an entry in the movement variances in the stock journal plus entries giving the detail to each stock line.