Skip to Content

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:

  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:

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:

  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
To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply