REQUIREMENT
This blog post is regarding the validation of “Production Ratio” in Supply Chain Management for the monthly bucket in SAP HANA.
Read More: SAP HANATEC 12 Certification Preparation Guide
The client wanted to see “Production Ratio” of each month for a particular Product, Location and Product Version combination with validation. In my case, when I say validation, it means that “Production Ratio” was compared with a threshold value to decide whether it will remain as it is or gets added to the next valid “Production Ratio” of the same month (Production Ratio should be greater than or equal to the threshold value). Let us take the threshold value as 5, and move forward to understand the various scenarios below.
Case 1:-
In this case, we are having three different versions of a “PRODUCT” (i.e. material) in a particular month, i.e., 003, 004 and 005. Here, only one version (005) has a “PRATIO” (Production Ratio) 6, which is greater than 5 (threshold value). So, in this case, “PRATIO” value of versions 003 and 004 i.e., 1 and 2 respectively, will get added to the “PRATIO” value of version 005, and their “FINAL_PRATIO” value becomes 0 (as shown below).
Case 2:-
In this case, we are having three different versions of a “PRODUCT” in a particular month, i.e., 001, 002 and 003, among which two versions, 002 and 003 have a “PRATIO” (Production Ratio) greater than our threshold value(5). So in this case, “PRATIO” value of version 001, i.e., 1 will get added to the highest “PRATIO” value, i.e., of version 003 which is 8 (as shown below).
Case 3:-
In this case, we are having three different versions of a “PRODUCT” in a particular month, i.e., 001, 002 and 003, among which two versions, 002 and 003 have a “PRATIO” (Production Ratio) greater than our threshold value (5), similar to the above scenario. But, “PRATIO” value of versions 002 and 003 are the same (7), So in this case, “PRATIO” value of version 001 will get added to the latest version, i.e., 003 (As we can’t decide highest among them).
NOTE: – When we can’t decide the highest Production Ratio, then we consider the latest Product Version as valid.
Case 4:-
In this case, we are having three different versions of a “PRODUCT” in a particular month, i.e., 001, 002 and 003, among which all have “PRATIO” (Production Ratio) values less than our threshold value (5). So, in this case, “PRATIO” value of versions 001 and 002 will get added to the “PRATIO” value of 003 (having the highest PRATIO value among them).
Case 5:-
In this case, we are having three different versions of a “PRODUCT” in a particular month, i.e., 002, 003 and 004, among which all have “PRATIO” (Production Ratio) values are less than our threshold value (5). But, “PRATIO” value of all versions are the same (1). So, in this case, “PRATIO” value of versions 002 and 003 will get added to the “PRATIO” value of 004 (latest “PRODUCT VERSION” among them).
Case 6:-
In this case, we are having two different versions of a “PRODUCT” in a particular month, i.e. 002 and 003, among which both have “PRATIO” (Production Ratio) value greater than our threshold value (5). So, in this case, the values will remain as it is.
Case 7:-
In this case, we are having only one version of a “PRODUCT” in a particular month, i.e., 001 in the month of AUG and 002, in the month of SEPT. So, whenever we are having only one version in a month, we have to leave it as it is without even validating it with our threshold value.
To sum up the above scenarios (for a single month):-
- Production Ratio greater than the threshold value is considered the valid one. E.g.:- Case 1
- If more than one Production Ratio is greater than the threshold value, then the highest one among them is the valid one. E.g.:- Case 2
- If more than one Production Ratio is greater than the threshold value, and we can’t decide highest among them, then Production Ratio of the latest version is considered the valid one. E.g.:- Case 3
- If all Production Ratio is less than the threshold value, then the highest among them is the valid one. E.g.: – Case 4
- If all Production Ratios are less than the threshold value, and we can’t decide highest among them, then Production Ratio of the latest version is considered the valid one. E.g.:- Case 5
- If all Production Ratios are greater than the threshold value, then the values will remain the same. E.g.:- Case 6
- If we have only one Production Ratio in a month, then the value will remain the same. E.g.: – Case 7
Use Case / Business Scenario: Reapplying the production/ transportation quota to the most relevant BOM/ lane. This scenario is applicable in almost all the supply chain planning projects, where you take constrained/ unconstrained supply plan and extract production and transportation quotas for Inventory planning.
DATA and SOLUTION
Our reference table has five columns namely “PRODUCT”, “LOCATION”, “DATE”, “P_VERSION” (PRODUCT VERSION) and “PRATIO” (Production Ratio).
This table contains the values of Production Ratio for various versions of a Product (i.e. material) from a location (i.e. plant) in a particular month, where we have to apply our validation rules, to sum up, multiple rows.
To achieve this, I have used Table Function, which can be further consumed in a Calculation view to get the result.
TABLE T1 SELECT VALUES
Select all the values from the referenced table or VDM.
T1 = Select * From BASE_TABLE;
TABLE T2 SELECT MONTHS HAVING MULTIPLE VALUES
Select the months having multiple Product versions. By this, AUG and SEPT months will be left out, as these months have only one version.
T2 = select
A."PRODUCT",
A."LOCATION",
A."DATE",
A."P_VERSION",
A."PRATIO"
from :T1 as A inner join
(select
"PRODUCT",
"LOCATION",
"DATE",
count(*)
from :T1
group by "PRODUCT","LOCATION","DATE" having count(*) > 1) AS B
on A."PRODUCT" = B."PRODUCT"
and A."LOCATION" = B."LOCATION"
and A."DATE" = B."DATE"
order by "DATE",
"P_VERSION",
"PRATIO";
TABLE T3 SET FLAG WHERE PRODUCTION RATIO IS GREATER THAN THRESHOLD VALUE
Select values of table T2 and set FLAG as N, where the value of “PRATIO” (Production Ratio) is greater than the threshold value (5). Else, set FLAG as Y(less than the threshold value).
T3 = select
*,
CASE When "PRATIO" >= 5
Then 'N'
ELSE 'Y'
END AS "FLAG"
from :T2
order by "DATE",
"P_VERSION",
"PRATIO";
TABLE T4 SET PRATIO AS 0 WHERE FLAG IS ‘N’
Set value of “PRATIO” as 0, where FLAG is N. And for Y, the value will remain as it is, in a separate column “PRATIO_NEW”.
T4 = SELECT
*,
case when "FLAG" = 'Y'
then "PRATIO"
else 0
end as "PRATIO_NEW"
from :T3
order by "DATE",
"P_VERSION",
"PRATIO";
TABLE T5 RUNNING SUM ON “PRATIO_NEW” AND MAX OF “PRATIO”
Now, apply the “Running Sum” Function on the “PRATIO_NEW” column and create a new column as “PRATIO_SUM”. Create one more new column “MAX_VALUE”, which shows the highest value “PRATIO” of the particular month. By doing so, you would notice that we are having 3 as the highest value for the month of JAN (2019-01-01) under “MAX_VALUE” column.
NOTE: – “MAX_VALUE” will help us in that scenario where we need the highest value of “PRATIO” in a particular month.
T5 = SELECT
*,
SUM("PRATIO_NEW") OVER (PARTITION BY "PRODUCT","LOCATION","DATE" ORDER BY "DATE") AS "PRATIO_SUM",
MAX("PRATIO") OVER (PARTITION BY "PRODUCT","LOCATION",
"DATE" ORDER BY "DATE") AS "MAX_VALUE"
from :T4
order by "DATE",
"P_VERSION",
"PRATIO";
TABLE T6 APPLY ROW_NUMBER() AND CREATE A COLUMN “FINAL_SUM”
Apply ROW_NUMBER() Function on “PRODUCT”, “LOCATION”, and “DATE”, with Order By “DATE”, “PRATIO”. Moving ahead, you will notice how this helps us in getting the latest Product Version.
Create a new column “FINAL_SUM” by applying the formula (“MAX_VALUE” + “PRATIO_SUM”) – “PRATIO_NEW”. Moving ahead, you will notice how this helps us in getting the correct value of Final Pratio.
TABLE T7 APPLY MAX() ON ROW_NUM
Now, apply the “MAX” Function on the “ROW_NUM” column. By doing so, we will have the highest value of ROW_NUM for any particular month under “MAX_ROW” column. Down the line, this is going to help us in getting the latest Product Version.
T7 = SELECT
*,
MAX("ROW_NUM") OVER (PARTITION BY "PRODUCT","LOCATION","DATE") AS "MAX_ROW"
from :T6
order by "DATE",
"P_VERSION",
"PRATIO";
TABLE T8_Y VALUES HAVING FLAG ‘Y’
Select values from Table 7 where “FLAG” is ‘Y’.
T8_Y = SELECT
*
FROM :T7
WHERE "FLAG" = 'Y';
TABLE T8_N VALUES HAVING FLAG ‘N’
Select values from Table 7 where “FLAG” is ‘N’.
T8_N = SELECT
*
FROM :T7
WHERE "FLAG" = 'N';
Before going further, I would like to explain the reason we have divided the data set based on the “FLAG” is because we are going to implement various conditions on the basis of the “FLAG”.
Let us try to understand with the help of above image having colour coding.
For “FLAG” = ‘Y’,
For “FLAG” = ‘N’,
TABLE T9_Y
Select “PRODUCT”, “LOCATION”, “DATE”, “P_VERSION” and “PRATIO”. Create a new column “PRATIO_FINAL” with condition – When “MAX_ROW” = “ROW_NUM”, “FINAL_SUM” = “PRATIO_SUM” and “MAX_VALUE” = “PRATIO_NEW” then, “FINAL_SUM” will be “PRATIO_FINAL”, else, “PRATIO_FINAL” will be 0.
T9_Y = SELECT
"PRODUCT",
"LOCATION",
"DATE",
"P_VERSION",
"PRATIO",
CASE When "MAX_ROW" = "ROW_NUM"
AND "FINAL_SUM" = "PRATIO_SUM"
AND "MAX_VALUE" = "PRATIO_NEW"
Then "FINAL_SUM"
ELSE 0
END AS "PRATIO_FINAL"
FROM :T8_Y
order by "DATE",
"P_VERSION",
"PRATIO";
TABLE T9_N
Select “PRODUCT”, “LOCATION”, “DATE”, “P_VERSION” and “PRATIO”. Create a new column “PRATIO_FINAL” with condition- When “MAX_ROW” = “ROW_NUM” and “MAX_VALUE” = “PRATIO” then, “FINAL_SUM” will be “PRATIO_FINAL”, else, “PRATIO” will be “PRATIO_FINAL”.
T9_N = SELECT
"PRODUCT",
"LOCATION",
"DATE",
"P_VERSION",
"PRATIO",
CASE When "MAX_ROW" = "ROW_NUM"
AND "MAX_VALUE" = "PRATIO"
Then "FINAL_SUM"
ELSE "PRATIO"
END AS "PRATIO_FINAL"
FROM :T8_N
order by "DATE",
"P_VERSION",
"PRATIO";
TABLE T9 (T9_Y + T9_N)
Apply UNION ALL on tables T9_Y and T9_N and create a new table T9.
T9 =
SELECT * FROM :T9_Y
UNION ALL
SELECT * FROM :T9_N
order by "DATE",
"P_VERSION",
"PRATIO";
TABLE T10
Now, apply “Left Outer” join on table T1 and T9, keeping T1 (our base table) as LEFT and T9 (having “PRATIO_FINAL” column) as RIGHT. By this, we will have all the fields from table T1 along with our desired field “PRATIO_FINAL” from table T9.
You might have noticed that two rows AUG (2019-08-01) and SEPT (2019-09-01) having null values for “PRATIO_FINAL”, as these months were not present in the table T9. We will handle this in our next and final step.
“SELECT DISTINCT” will remove the duplicate entries.
T10 = SELECT
Distinct T1.*,
T9."PRATIO_FINAL"
from :T1 AS T1
LEFT OUTER JOIN :T9 as T9
on T1."PRODUCT" = T9."PRODUCT"
AND T1."LOCATION" = T9."LOCATION"
AND T1."DATE" = T9."DATE"
AND T1."P_VERSION" = T9."P_VERSION"
AND T1."PRATIO" = T9."PRATIO"
order by "DATE",
"P_VERSION",
"PRATIO";
FINAL TABLE
Select the required fields “PRODUCT”, “LOCATION”, “DATE”, “P_VERSION” and “PRATIO” from table T10.
Now, if we have a null value for “PRATIO_FINAL”, only then “PRATIO” will be picked up as “PRATIO_FINAL”. Else, “PRATIO_FINAL” will remain as it is. This will give us our final output.
VAR_OUT =
SELECT
"PRODUCT",
"LOCATION",
"DATE",
"P_VERSION",
"PRATIO",
CASE When "PRATIO_FINAL" IS NULL
Then "PRATIO"
ELSE "PRATIO_FINAL"
END AS "PRATIO_FINAL"
FROM :T10
order by "DATE",
"P_VERSION",
"PRATIO";
CONCLUSION
Now, once we are having the final values of “PRATIO” as “PRATIO_FINAL” for each month, we can simply use this Table Function as a data source in a Calculation View with the help of a Projection to see the result. By this, we were able to distribute the production/ transportation quota to the most relevant BOM/ lane.
CODE:-
FUNCTION "XXXXX"."YY.PPPPP::TF_PRATIO_VALIDATION_TEST" ( )
RETURNS TABLE (
"PRODUCT" NVARCHAR(10),
"LOCATION" NVARCHAR(5),
"DATE" NVARCHAR(10),
"P_VERSION" NVARCHAR(5),
"PRATIO" DECIMAL(12,3),
"PRATIO_FINAL" DECIMAL(12,3)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
/*****************************
Write your function logic
*****************************/
T1 = Select * from BASE_TABLE;
T2 = select
A."PRODUCT",
A."LOCATION",
A."DATE",
A."P_VERSION",
A."PRATIO"
from :T1 as A
inner join (select
"PRODUCT",
"LOCATION",
"DATE",
count(*)
from :T1
group by "PRODUCT",
"LOCATION",
"DATE" having count(*) > 1) AS B
on A."PRODUCT" = B."PRODUCT"
and A."LOCATION" = B."LOCATION"
and A."DATE" = B."DATE"
order by "DATE",
"P_VERSION",
"PRATIO";
T3 = select
*,
CASE When "PRATIO" < 5
Then 'Y'
ELSE 'N'
END AS "FLAG"
from :T2
order by "DATE",
"P_VERSION",
"PRATIO";
T4 = SELECT
*,
case when "FLAG" = 'Y'
then "PRATIO"
else 0
end as "PRATIO_NEW"
from :T3
order by "DATE",
"P_VERSION",
"PRATIO";
T5 = SELECT
*,
SUM("PRATIO_NEW") OVER (PARTITION BY "PRODUCT","LOCATION","DATE" ORDER BY "DATE") AS "PRATIO_SUM",
MAX("PRATIO") OVER (PARTITION BY "PRODUCT","LOCATION","DATE" ORDER BY "DATE") AS "MAX_VALUE"
from :T4
order by "DATE",
"P_VERSION",
"PRATIO";
T6 = SELECT
*,
("MAX_VALUE" + "PRATIO_SUM") - "PRATIO_NEW" as "FINAL_SUM",
ROW_NUMBER() OVER (PARTITION BY "PRODUCT","LOCATION","DATE" ORDER BY "DATE","PRATIO" ) AS "ROW_NUM"
from :T5
order by "DATE",
"P_VERSION",
"PRATIO";
T7 = SELECT
*,
MAX("ROW_NUM") OVER (PARTITION BY "PRODUCT","LOCATION","DATE") AS "MAX_ROW"
from :T6
order by "DATE",
"P_VERSION",
"PRATIO";
T8_Y = SELECT
*
FROM :T7
WHERE "FLAG" = 'Y';
T8_N = SELECT
*
FROM :T7
WHERE "FLAG" = 'N';
T9_Y = SELECT
"PRODUCT",
"LOCATION",
"DATE",
"P_VERSION",
"PRATIO",
CASE When "MAX_ROW" = "ROW_NUM"
AND "FINAL_SUM" = "PRATIO_SUM"
AND "MAX_VALUE" = "PRATIO_NEW"
Then "FINAL_SUM"
ELSE 0
END AS "PRATIO_FINAL"
FROM :T8_Y
order by "DATE",
"P_VERSION",
"PRATIO";
T9_N = SELECT
"PRODUCT",
"LOCATION",
"DATE",
"P_VERSION",
"PRATIO",
CASE When "MAX_ROW" = "ROW_NUM"
AND "MAX_VALUE" = "PRATIO"
Then "FINAL_SUM"
ELSE "PRATIO"
END AS "PRATIO_FINAL"
FROM :T8_N
order by "DATE",
"P_VERSION",
"PRATIO";
T9 =
SELECT * FROM :T9_Y
UNION ALL
SELECT * FROM :T9_N
order by "DATE",
"P_VERSION",
"PRATIO";
T10 = SELECT
distinct T1.*,
T9."PRATIO_FINAL"
from :T1 AS T1
LEFT OUTER JOIN :T9 as T9
on T1."PRODUCT" = T9."PRODUCT"
AND T1."LOCATION" = T9."LOCATION"
AND T1."DATE" = T9."DATE"
AND T1."P_VERSION" = T9."P_VERSION"
AND T1."PRATIO" = T9."PRATIO"
order by "DATE",
"P_VERSION",
"PRATIO";
VAR_OUT =
SELECT
"PRODUCT",
"LOCATION",
"DATE",
"P_VERSION",
"PRATIO",
CASE When "PRATIO_FINAL" IS NULL
Then "PRATIO"
ELSE "PRATIO_FINAL"
END AS "PRATIO_FINAL"
FROM :T10
order by "DATE",
"P_VERSION",
"PRATIO";
return :var_out;
END;
