An Application of Replacement Path with Query

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:

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:

1. Major challenge was how to find the list of materials which are salable. No property of material tells that this material is salable
2. 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
3. If we take materials from master data, there is huge list which is not required
4. 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:

1. Find list of material with different conditions (highlighted in blue color). Query is for internal purpose. Not visible to user
2. 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.

1. Closing Stock for the period [ From and To ] is not equal to 0
2. Sales QTY for the period [From and To] is not equal to 0
3. Cumulative Sales Qty for previous three months is not equal to 0

Note:

1. These 3 conditions are same as 3 columns restricted to Calendar day or month.
2. 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.
3. Material Variable is of type ‘Characteristics Value’, ‘Manual Input’ and optional

Format of Pre-Query:

Different Scenarios:

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:

Note:

1. Material variable has type ‘ Characteristics Value’, ‘Replacement Path’ with selection of query on “Replacement Path” tab
2. Choose master data for Date field in the Rows Section. This implies all dates will appear for entered interval
3. 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