SQL programmers can calculate stock ageing on SAP HANA databases by using SQLScript. The calculation is based on the FIFO (First In First Out) valuation method, which is demonstrated in this tutorial.
To summarise the problem that was addressed and solved in this HANA database SQLScript tutorial for SAP calculation developers, the following is presented:
Inbound movement types are utilised for the purpose of bringing stock items or products into plants or storage areas.
These transactions involving stock movement are saved in a table within the HANA database.
Additionally, information regarding outbound movement types or stock-out transactions can be found stored in this SAP table.
SAP calculations for new build programmers can easily solve the problem of stock ageing by making use of the SQLScript features available on the HANA database.
For the purpose of calculating inventory ageing or stock ageing on sample HANA database tables, multiple CTE expressions are utilised, running sum calculations with the SUM() aggregation function is utilised, the row number() function and the CASE expression are utilised, and the days between() datetime function is utilised.
Sample Table and Data from the SAP HANA Database Used for an Example of Stock Aging
Executing the DDL commands for table creation allows SQLScript developers to create the column-based database tables listed below on the development instance of the SAP HANA database.
After that, I presented some sample information for each table as well.
The StockMovements table is the primary focus of our attention right now.
Lookup tables for the definitions of products (or inventory items) and plants make up the remainder of the database tables (warehouses, etc)
For the purpose of performing stock ageing or inventory ageing calculations as outlined in this database tutorial, SQL programmers will be executing codes written in SQLScript on these tables.
Query in SQLScript to Determine the Incoming and Outgoing Stock Movements
You can get a summary of inbound or outbound inventory movements based on the data in the StockMovements table by executing the following SQLScript SELECT statement against the HANA database.
The results of the SQL query are displayed in the screenshot that can be found below.
In this tutorial, I want to demonstrate to developers of SAP HANA databases how they can use SQLScript to determine the amount of time that has passed since an item was added to an inventory (in this case, plants).
I will not calculate product ageing based on plants separately in order to keep things simple and to better understand how SQLScript queries work. Instead, I will calculate product ageing based on a single stock place that contains all products.
SQL code for the "Net Quantity" after accounting for Stock Movements
To begin, developers are able to determine the resultant net amounts for each product after accounting for inbound and outbound stock movements by making use of the following SQLScript SELECT statement, which makes use of a SQL CTE expression.
In essence, SQL CTE expressions are nothing more than sub-select statements that can be referred to multiple times within a single SQLScript SELECT statement.
Note for the person developing the SQLScript database: SQL CTE stands for Common Table Expression.
Database developers are able to report the most recent status of product quantities in stock when the SQLScript CTE query that was just described is executed.
Following the application of the SUM() aggregation function to each individual product, the following is the current status of our inventory.
Calculating the Running Total for Stock with an Unbounded Preceding Clause
In the following stage, I will segment stock movements using the MovementType field to create separate CTE expressions for inbound and outbound stock movements, respectively. I will refer to these segments as IN for inbound stock movements and OUT for outbound stock movements.
Check out the query that follows in SQLScript.
Take special care to focus on the locations where the SUM() aggregate function is used.
SQL programmers are given the ability to calculate sums on quantity columns by using the SUM() function in conjunction with the Partition By clause. In this particular example, the sum will be calculated for ProductId.
The addition of unbounded rows preceding the partitioning of data
The HANA database developers have an easier time calculating running sums thanks to the By clause in the SUM() aggregation function.
Let's put the above SQLScript code block to work and take a look at what it produces.
As you can see from my annotations on the screenshot, the preceding SQL statement actually provides two different running sum calculations.
The first one grows larger as time passes, while the second one grows larger beginning with the most recent date and continuing until the first date of the stock movement.
Determine the Stock's Age Using the HANA Database
Calculations for net quantity, inbound stock movements, and output stock movements are combined into a single SQLScript query and performed using the following HANA database SQL script.
Programmers working with the SAP HANA database should focus their attention primarily on the last three columns: SumInDesc(ending), Net, and Diff(erence). The value of the NET column is the same for all rows pertaining to the same product, indicating the current resultant quantity following all stock movements.
Since the SumInDesc(ending) field is for keeping a running total of quantities in descending order, the most effective way to calculate the age of stocks is to work backwards, beginning with the most recent stock movement.
The value of the Diff(ence) field can be determined easily by applying the formula Net-SumInDesc.
When Diff has a positive value, it indicates that products from the related stock movement record are currently present in the stock.
Therefore, a calculation of the stock's age should be performed for that particular inbound stock movement.
This is a very important point.
In the event that Diff is negative, the algorithm determines that all of the stock from the related inbound stock movement has already been used for the subsequent outbound stock movement.
Briary Energy can help you determine the sap flow of your buildings. We have a sap calculator that can help you estimate the sap production of your properties. Call us today to learn more about our sap calculation services.