How stock level calculations work in Exo Business

SQL Objects

Relevant database objects are the following stored procedures:

• Stored Procedures:

  • STOCK_LEVELS

  • STOCK_IN_LOCATION

  • STOCK_IN_LOC

• Tables:

  • PURCHORD_HDR

  • PURCHORD_LINES

  • SALESORD_HDR

  • SALESORD_LINES

  • STOCK_LOCATIONS

  • STOCK_LOC_INFO

  • WORKSORD_HDR

  • WORKSORD_LINES

If you are unfamiliar with a customer’s database/customizations, I would recommend checking the 3 stored procedures listed above as a first step as it is common for these to have been customised.

Note that STOCK_LOCATIONS is usually called by STOCK_IN_LOC.

User Profiles

The following user profiles are relevant to stock level calculations:

• ORDER_MAX_FWD_DAYS

• PO_ORDER_MAX_FWD_DAYS

• WO_ORDER_MAX_FWD_DAYS

• USEWORKSORDERS

Methodology

Stock quantities are checked anytime they’re referenced on screen, or if you try to save some kind of supply or adjustment to stock.

STOCK_LEVELS tends to be used when displaying overall stock levels for an entire company.

STOCK_IN_LOCATIION tends to be used when checking stock quantities for supply actions as it is aimed at single locations.

The values of the MAX_FWD_DAYS profiles indicate what range of sales orders/purchase orders are counted to supply metrics. Changing these profiles can have a big effect on how stock levels calculate.

Common Problems

Not all sales/purchase/works orders seem to be getting counted - check Profiles

If a sales/purchase/works order are entered too far into the future, then they may not be counted, dependent on the value in the relevant MAX_FWD_DAYS profile. This is intentional as these profiles are meant to act as an estimate on near future stock quantities, but they can be extended (Or shortened) in config if desired.

Overcommitted stock for Sales Orders I can’t find - check SALESORD_LINES.HDR_STATUS

The default stock level calculations reference SALESORD_LINES.HDR_STATUS when determining if a line should count towards committed stock levels.
This can lead to a discrepancy in stock commitments if lines weren’t cleaned up properly during order supply/deletion/some other procedure.

This can be fixed in one of three ways:

• Modify the STOCK_LEVELS and STOCK_IN_LOCATIION procedures so that they refer to SALESORD_HDR.STATUS instead of SALESORD_LINES.HDR_STATUS

• Run a recalculation, making sure that the SALESORD_LINES.HDR_STATUS field matches SALESORD_HDR.STATUS. An example is:

  SQL

  -- Identify
  SELECT H.seqno AS OrderNumber
       , H.status AS OrderStatus
       , L.hdr_status as LineHeaderStatus
       , CASE WHEN (L.hdr_status <2 OR L.hdr_status IN (4,5)) AND (H.status<2 OR H.status IN (4,5))  THEN 'No effect on calculation, both counted'
              WHEN (L.hdr_status >=2 AND L.hdr_status NOT IN (4,5)) AND (H.status>=2 AND H.status NOT IN (4,5)) THEN 'Probably fine, both not counted'
              WHEN (L.hdr_status <2 OR L.hdr_status IN (4,5)) AND (H.status>=2 AND H.status NOT IN (4,5)) THEN 'Potentally overcommited'
              WHEN (L.hdr_status >=2 AND L.hdr_status NOT IN (4,5)) AND (H.status<2 OR H.status IN (4,5)) THEN 'Potentially Uundercommitted'
              ELSE 'Probably Fine - case statement did not include'
              END AS StatusDiagnosis
       , L.linked_stockcode AS StockcodeOnLine
       , L.solineid AS SOLineID
  FROM salesord_lines AS L
  INNER JOIN salesord_hdr AS H
      on H.SEQNO=L.HDR_SEQNO
  WHERE l.HDR_STATUS<>H.STATUS
  
  -- Update mismatches
  UPDATE L
  SET Hdr_status = H.status
  FROM salesord_lines AS L
  INNER JOIN salesord_hdr AS H
      on H.SEQNO=L.HDR_SEQNO
  WHERE l.HDR_STATUS<>H.STATUS

  Before running the above, please be aware that it assumes the Header status of the order is correct.

• Check line quantities and updated CORRECTION_QUANT as appropriate
  Run a recalculation, making sure that the supply state quantities of every line are correct. If the CORRECTION_QUANT isn’t correct, an order might misrepresent how much it has left to supply.
  At a database level, nothing stops an order being oversupplied and potentially indicating a negative commitment to the engine if the values are wrong.

Less incoming stock for Purchase Orders I can’t find - check PURCHORD_LINES

The above problems can also apply in general to PURCHORD_LINES if you are seeing problems with incoming quantities.

Related information

There are also several white papers related to stock in the education centre:

Stock Integration

Stock Serial Numbers

Stock Take

Stock Valuation

Entity relationship diagram for Stock ledger