sql scripts with cursor to calculate zero stock in journal stock table
Budget: $30 – $250 USD
Stock module on SAGE X3
1. Understanding SAGE X3’s stock module
2. How the stock module operates
3. Zero Stock & SQL
1. It is important to know when an article has reached zero inventory/zero stock (Zero Stock Days). Whenever an article reaches zero, the information must be consigned in a table that holds these specific elements: - Article’s reference – The date of the stock reaching zero – The date of the stock entry picking up again (optional).
2. One of the most important elements to know is the Stock journal. In our case, the Stock journal table goes by the name of 1STOJOU, and follows the FIFO (First in First out method) – where all of the movements of stock are consigned in the table starting from the first entry point of the article in the stock.
Before the entry was made, the stock was at zero; therefore, after the first entry is made, the movement of the stock is consigned in the table STOJOU with the mention of the article’s reference, the quantity and the date of imputation. The predecessor movement can be in this case a client’s delivery, a supplier’s reception or other stock entries.
To summarize the logic, it is a table that consists of pluses and minuses (positive and negative movements):
Stock Entry Positive Movement (+)
Stock Exit Negative Movement (-)
In general, the Stock Journal (STOJOU) table consists of:
- Article reference
- Date of imputation
- Type of movement
- Quantity of movement
- Time of movement
The Stock begins with a 0 and from then, each time a movement is made, depending on its nature (entry or exit), the table must be made. When the total of the pluses and minuses is zero, this means that we are “out of stock”/ 2zero stock.
3. The SQL script must be made and should allow us to analyse the STOJOU table and to keep track of the entry and exit movements. An SQL cursor must be used to allow us to position each time the stock has reached zero, such as “The cursor has verified zero stock”.
1. Understanding SAGE X3’s stock module
2. How the stock module operates
3. Zero Stock & SQL
1. It is important to know when an article has reached zero inventory/zero stock (Zero Stock Days). Whenever an article reaches zero, the information must be consigned in a table that holds these specific elements: - Article’s reference – The date of the stock reaching zero – The date of the stock entry picking up again (optional).
2. One of the most important elements to know is the Stock journal. In our case, the Stock journal table goes by the name of 1STOJOU, and follows the FIFO (First in First out method) – where all of the movements of stock are consigned in the table starting from the first entry point of the article in the stock.
Before the entry was made, the stock was at zero; therefore, after the first entry is made, the movement of the stock is consigned in the table STOJOU with the mention of the article’s reference, the quantity and the date of imputation. The predecessor movement can be in this case a client’s delivery, a supplier’s reception or other stock entries.
To summarize the logic, it is a table that consists of pluses and minuses (positive and negative movements):
Stock Entry Positive Movement (+)
Stock Exit Negative Movement (-)
In general, the Stock Journal (STOJOU) table consists of:
- Article reference
- Date of imputation
- Type of movement
- Quantity of movement
- Time of movement
The Stock begins with a 0 and from then, each time a movement is made, depending on its nature (entry or exit), the table must be made. When the total of the pluses and minuses is zero, this means that we are “out of stock”/ 2zero stock.
3. The SQL script must be made and should allow us to analyse the STOJOU table and to keep track of the entry and exit movements. An SQL cursor must be used to allow us to position each time the stock has reached zero, such as “The cursor has verified zero stock”.