Description:
The document describes an application of Replacement Path with Query.
Requirement:
Define a query to show Closing Stock, Closing Stock value, Sales Qty, Sales Value, and Stock in No of days of material during entered period.
Format of report is as follows:
|
Material |
Date |
Closing Stock |
Closing Stock Value |
Sales Qty |
Sales Value |
Stock in No of Days |
|
XXX |
01.10.2013 |
|
|
|
|
|
|
|
02.10.2013 |
|
|
|
|
|
|
|
03.10.2013 |
|
|
|
|
|
|
YYY |
01.10.2013 |
|
|
|
|
|
|
|
02.10.2013 |
|
|
|
|
|
|
|
03.10.2013 |
|
|
|
|
|
Purpose of above report:
- The report provides information about closing stock day-wise for entered period
- If sale did not happen on a particular day, then the report tells whether there is any stock out situation
- The report explains how many times there was stock out situation for a particular material in entered period
Challenges:
- Major challenge was how to find the list of materials which are salable. No property of material tells that this material is salable
- If we take posted values of material from Billing Item infocube, then it will not show materials for which stock is there and no sale in entered period
- If we take materials from master data, there is huge list which is not required
- Rough definition of material is salable is as follows:
Material is shown in the report if at least one of following condition is true.
- Closing Stock for the period [ From and To ] is not equal to 0
- Sales QTY for the period [From and To] is not equal to 0
- Cumulative Sales Qty for previous three months is not equal to 0
Solution:
The report is defined in two steps:
- Find list of material with different conditions (highlighted in blue color). Query is for internal purpose. Not visible to user
- Use this list of materials to show Closing Stock, Closing Stock value, Sales Qty, Sales Value, Stock in No of days during entered period – Query is visible for user
Part A: Definition of Pre-Query
Keep variable entry screen for both queries same. Material will be optional variable in both and Calendar Day interval is compulsory field. Calendar Day interval is not defined on Filter tab under characteristics restriction because we need different time interval to find list of materials.
Material is shown in the report if at least one of following condition is true.
- Closing Stock for the period [ From and To ] is not equal to 0
- Sales QTY for the period [From and To] is not equal to 0
- Cumulative Sales Qty for previous three months is not equal to 0
Note:
- These 3 conditions are same as 3 columns restricted to Calendar day or month.
- We want a material satisfying at least one condition. Implies if all column values are zero, then we do not want to include that material. Do zero row suppression in Query -> Properties.
- Material Variable is of type ‘Characteristics Value’, ‘Manual Input’ and optional
Format of Pre-Query:
|
Material |
Closing Stock [ From and To ] |
Sales Qty [ From and To ] |
Stock in No of Days [ 3 Months previous to month found in date From] |
|
XXX |
|
|
|
|
YYY |
|
|
|
Different Scenarios:
|
Scenario |
Closing Stock Qty 01.02.2013 to 20.02.2013 |
Sales Qty 01.02.2013 to 20.02.2013 |
Cumulative Sales Qty 11.2012, 12.2012 & 01.2013 |
Material Visible /Not Visible in Output of Pre-Query |
|
1. No sale in previous 3 months. No Stock and No Sale during period |
0 |
0 |
0 |
Not Visible |
|
2. Sale in previous 3 months. No stock and no Sale during given period because of stock out |
0 |
0 |
XXX |
Visible |
|
3. No Stock may be from some day of the period till end. Sale during that period before stock out. No Sale in previous months because Material is launched during this period |
0 |
XXX |
0 |
Visible |
|
4. Stock is present. But no sale during given period and previous 3 months because material is launched during this period |
XXX |
0 |
0 |
Visible |
|
5. Material is launched and sold during this period. Therefore, no sale in previous 3 months |
XXX |
XXX |
0 |
Visible |
|
6. No Stock may be from some day of the period till end. Sale during that period before stock out & previous 3 months sale also |
0 |
XXX |
XXX |
Visible |
|
7. No sale during this period. But stock is available and previous 3 months sale present |
XXX |
0 |
XXX |
Visible |
|
8. Stock available and sold during the given period and previous 3 months also |
XXX |
XXX |
XXX |
Visible |
Conclusion:
From Pre-Query we will receive list of materials satisfying our requirements explained in 8 scenarios.
Part B:
This query is defined to show Closing Stock, Closing Stock value, Sales Qty, Sales Value, and Stock in No of days during entered period. List of materials is taken from Part A.
Format of report is as follows:
|
Material |
Date |
Closing Stock |
Closing Stock Value |
Sales Qty |
Sales Value |
Stock in No of Days |
|
XXX |
01.10.2013 |
|
|
|
|
|
|
|
02.10.2013 |
|
|
|
|
|
|
|
03.10.2013 |
|
|
|
|
|
|
YYY |
01.10.2013 |
|
|
|
|
|
|
|
02.10.2013 |
|
|
|
|
|
|
|
03.10.2013 |
|
|
|
|
|
Note:
- Material variable has type ‘ Characteristics Value’, ‘Replacement Path’ with selection of query on “Replacement Path” tab
- Choose master data for Date field in the Rows Section. This implies all dates will appear for entered interval
- Do not set Suppress Zero Row property from Query Properties. If a row is zero, implies the material has sale in previous 3 months. But on that day closing stock was zero and no sale happened further in entered interval