RANBAXY User Requirement Specification

LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Prepared By Reviewed By Approved By

Name

Designation

Sign/Date

1. Overview
This URS covers the integration aspects between Demand planning module of
APO and BW. Also document provides the details level on Field definitions for
source data form R/3 and Non R/3

These user specifications cover Integration between DP and BW covers the following:

1. Staging of historical data from legacy systems (R/3 and Non R/3)

1.1 Using the Characteristics lying in the R/3 & Non R/3 Material master for
populating the characteristics.

1.2 Using the attribute lying in the R/3 & Non R/3 Material Master to populate the
Attributes.

1.3 Capture Key figures using transactional data ( R/3 and Non R/3) for the
purpose of Demand forecasting

2. Stage data from external sources like IMS, ORG, and Secondary Sales for the
purpose of display in the Interactive Planning book.

3. Capturing the field inputs and stage them in the BW InfoCube for usage in DP.

4. Staging for Data from legacy systems where there is no transaction history

This document provides how the data (master and transactional) will be staged first in
Business Warehouse by Interfacing with R/3 & Non R/3 and then transferred to Demand
Planning in APO.

1
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

2. Operational Requirements

Overall Data flow

Brief descriptions of the steps are as under;

Step 1 - Master and transaction data is loaded from R/3 and Non-R/3
transaction system into a BW InfoCube.

Step 2 - Data from the BW Infocube is loaded into APO DP Infocube

Step 3 - Data is loaded into the Planning area from the Infocube present in
APO DP.

Step 4 - After the conclusion of the Planning activity the data is extracted
into another Infocube in BW for backup / reporting.

Master Data Flow

Master data used in Demand Planning (DP) module consists of the following;

2
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

 Characteristics
 Attributes
 Key Figures
 Long & Medium text descriptions for every characteristics

Characteristics

Characteristics are planning levels at which data can be sliced and planning results can be
viewed and modified. The Characteristics are grouped as dimensions like Product,
Location and time.

Data Source:

The source of the values of the characteristics will be from R/3 and Ron R/3. Give below
is the source of each of the characteristics and the equivalent field in R/3. For the data
comes from Non R/3, it will be in the Flate file to load directly into BW cubes without
R/3 involvement. The attributes of the data elements from Non R/3 should be same as
R/3.

S. Characteristics Typical Source (R/3 field) Org. Level

No. Value/Description
Product Dimension
1 SAP Code 1002851, Altiva MARA - MATNR Active Material
200 MG number for the
sales org.
2 SKU Local Code IN_2250_1002851, ZBW_LOC_CONFIG Directly from Z
ZBW_LOCALCODE table
Altiva 200 MG
3 Strength 250 MG MARA-WRKST Basic data 2
4 Primary Pack Blisters MVKE-MVGR1 Material Group 1.
5 Dosage Form Tablets V_T179-PRODH Product Hierarchy
7 level
6 Brand Cefaclor V_T179-PRODH Product Hierarchy
6 level
7 Major Molecule Cephalexin ZMT_MATMOL Product Hierarchy
8 Extra Product 99 None None
Characteristic

Geography Dimension
9 Global Global Not relevant for R/3 Should be in the
BW InfoCube
(E.g. Transfer

3
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

rules)
10 Region Region 1A ZMT_CNTREGION- External Z table.
ZMRG This will different
to DF & API
Planning
11 Sub-region Region 1A ZMT_CNTREGION- External Z table.
ZMSRG This will different
to DF & API
Planning
12 Country IN, India T001-LAND1 Location/Plant
Master
13 Distribution 4105, New Delhi VBRP-WERKS Delivery Plant
Center Depot from Billing
document
14 Extra Geography 99 None None
Characteristic
Customer Dimension
14 Division 2250, CHC VBRP-VKORG Sales Org from
Billing document
15 Customer Group In Division 7000 KNVV-KVGR1 Customer Group 1
91, Mckesson from Customer
Master. Each key
99 (For others) customer relevant
for planning will
have one customer
group specific to
Division
16 Customer In Division 7000 KNA1-KUNNR Payer IDs for
91, Mckesson every customer.
The Planning will
99 (For others) be at the Payer
level
17 Extra Customer 99 None None
Characteristic

In addition to the information, details of accessing the fields can also be found in
Appendix 1.

4
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Timing and Frequency

Regular Products

Uploading of characteristics for the existing products is a one time activity for 36 months
history. This is required to create the Characteristic value combination (CVC) in Demand
Planning.

New products created between two planning cycles will be created at the end of the
month before the planning cycle.

New Products

All Products which are launched and are made available in the R/3 product master will
need to be populated in the BW Infocube as well.New product Master data combination
will be available in R/3 table ZMI_CVC_T as per the functional specification in
Appendix 1II

The periodicity of the update of the master data for the new products will be before the
planning cycle. This will ensure that the master data is available for planning during the
next cycle.

Products not sourced from India

Products which are not manufactured in India and not maintained in the R/3 Material
master( All Non R/3 Markets) needs to be collected in a template to be provided by the
Business Warehouse team from Non – R/3 markets.

The frequency of this upload will follow the same frequency of the regular products
which are sourced from India.

Attributes

Attributes will be made available in the R/3 Material master in the following fields.
These would be used to populate or upload to the respective Attributes attached to the
Local code (SKU Code). The frequency of upload of this document will be the same as
the characteristics.

S. No. Attribute Typical Value Source (R/3 Org. Level

5
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

field)
1 Sales Pack Size S10 MVKE-VRKME Sales Unit. Sales
Pack Unit will be only
one at the market
level. SAP R/3 will be
taken care to avoid diff
sales pack at Dist
channel.
2 Status Regular, P1, … MVKE-MVGR4 Material Group 4.
P7 Division
dependent
3 Regulatory Approved MVKE-MVGR2 Material Group 2.
Division
dependent
4 Product Class A, B, C etc. MVKE-MVGR3 Material Group 3.
Division
dependent
5 Currency INR, USD Country Planning Currency
Currency from pertaining to
country config in Country.
R/3

V_T001-WAERS
6 Combo Molecule Cephalexin + MVKE-PRODH 5 level Product
Cefaclour hierarchy.
7 Dummy Attribute 99 None None

In addition to the information, details of accessing the fields can also be found in
Appendix 1.

6
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

InfoCubes
This section of the document addresses key design points pertaining to InfoCubes.

Number of InfoCubes

The data that needs to be sourced from transaction / external system via BW are either
available on a daily basis or only at a monthly basis.

S. Key Figures Upload Source(s) InfoCube

NO frequency
1 New Products Master data info cube with One Monthly R/3 and InfoCube1
key fugure( KG conversion) Non R/3
2 Daily R/3, Other Transactional
Actual Bonus transaction data Cube
Free Replacement Scheme systems
Gross Sales Quantity
Impact of Promotion
Live Back Orders
Net Sales Quantity
Net Shipment Quantity
Sales Order Quantity
Samples to Market & Physicians
Short Dated Quantity
3 Monthly External Non R/3
Customer Forecast sources ranbaxy
like Flat
Estimated Primary sales files ES3
Field Forecast etc…
Secondary Sales
4 Monthly External Non R/3 Non
sources Ranbaxy
Market Prescription Data like Flat
files IMS,
Market Data (KG) ORG etc.
Market Data in Sales Pack
5 Monthly R/3 NSP India

Exchange Rate

7
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Net Selling Price

6 Monthly R/3 KG
KG Conversion to API Conversion
7 Monthly R/3 NSP CHC
Exchange Rate

Net Selling Price

8 Monthly R/3 Weekly Split
Weekly Split Ratio Ratio
9 Once Excel Cutover
Adjusted History Upload Cube
Adjusted History (Kg)
Base Forecast Previous cycle
BR Fcst Devtn
Field Forecast Previous Cycle
Final Forecast Previous cycle
RDP Fcst Devtn
Sales Head Forecast % Deviation
SC Head Forecast % Deviation
SCH Forecast Previous cycle
SH Forecast Previous cycle
Statistical Forecast Previous Cycle

Nine InfoCubes will be used to manage the data between DP and BW. The reason for
using nine InfoCubes is primarily to segregate the update frequency, Hierarchy Level and
source systems to these cubes. The Info cube 1 will be used to update only Master data
using MPOS and other cubes will be loaded into the planning area depending on the
frequency at which it gets uploaded using Load planning area. Attached is the document
of Infocubes

The detailed design in terms of the characteristics and the key figures for each of the cube
has been enclosed

8
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Key Figures

Key figures that will be sourced from the Business warehouse application will be divided
into two categories.
 Transaction data from Legacy systems
 External data like IMS and Secondary sales

The list of key figures that are required to be staged in BW and their frequency of upload
is detailed in in the key fugure list attachments.

Weekly proportion calculation

Kindly refer schematic on the next page.

9
RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Day of Month/ Day of Week Num Weekly Proportion Monthly Daily Proportion Monthly % Daily Demand Weekly Demand
Month Year Week (Month Base) % Forecast %
(5 day week)

1 012004 Thursday 3.66% 68

2 012004 Friday 3.66% 68
3 012004 Saturday 0.00% 0
4 012004 Sunday 0.00% 0
5 012004 Monday 3.98% 74
6 012004 Tuesday 3.98% 74
7 012004 Wednesday 3.98% 74
8 012004 Thursday 1 19.91% 3.98% 74 371
9 012004 Friday 3.98% 74
10 012004 Saturday 0.00% 0
11 012004 Sunday 0.00% 0
12 012004 Monday 4.53% 84
13 012004 Tuesday 4.53% 84
14 012004 Wednesday 4.53% 84
15 012004 Thursday 2 22.65% 4.53% 84 422
16 012004 Friday 2000 4.53% 107.32% 84
17 012004 Saturday 0.00% 0
18 012004 Sunday 0.00% 0
19 012004 Monday 6.38% 119
20 012004 Tuesday 6.38% 119
21 012004 Wednesday 6.38% 119
22 012004 Thursday 3 31.88% 6.38% 119 594
23 012004 Friday 6.38% 119
24 012004 Saturday 0.00% 0
25 012004 Sunday 0.00% 0
26 012004 Monday 5.11% 95
27 012004 Tuesday 5.11% 95
28 012004 Wednesday 5.11% 95
29 012004 Thursday 4 25.56% 5.11% 95 476
30 012004 Friday 5.11% 95
31 012004 Saturday 0.00% 0
1 022004 Sunday 0.00% 0
2 022004 Monday 5.10% 76
3 022004 Tuesday 5.10% 76
4 022004 Wednesday 5.10% 76
5 022004 Thursday 5 25.48% 5.10% 76 382
6 022004 Friday 5.10% 76
7 022004 Saturday 0.00% 0
8 022004 Sunday 0.00% 0
9 022004 Monday 5.40% 81
10 022004 Tuesday 5.40% 81
11 022004 Wednesday 5.40% 81
12 022004 Thursday 6 27.00% 5.40% 81 405
13 022004 Friday 5.40% 81
14 022004 Saturday 0.00% 0
15 022004 Sunday 1500 0.00% 100.00% 0
16 022004 Monday 5.11% 77
17 022004 Tuesday 5.11% 77
18 022004 Wednesday 5.11% 77
19 022004 Thursday 7 25.54% 5.11% 77 383
20 022004 Friday 5.11% 77
21 022004 Saturday 0.00% 0
22 022004 Sunday 0.00% 0
23 022004 Monday 4.39% 66
24 022004 Tuesday 4.39% 66
25 022004 Wednesday 4.39% 66
26 022004 Thursday 8 21.97% 4.39% 66 330
27 022004 Friday 4.39% 66
28 022004 Saturday 0.00% 0
29 022004 Sunday 0.00% 0
1 032004 Monday 3.82% 101
2 032004 Tuesday 3.82% 101
3 032004 Wednesday 3.82% 101
4 032004 Thursday 9 19.12% 3.82% 101 504
5 032004 Friday 3.82% 101
6 032004 Saturday 0.00% 0
7 032004 Sunday 0.00% 0
8 032004 Monday 3.82% 101
9 032004 Tuesday 3.82% 101
10 032004 Wednesday 3.82% 101
11 032004 Thursday 10 19.12% 3.82% 101 504

2500 94.85%

10
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Realignment of Customer Group

Characteristics “Customer” and “Customer Groups” will be used by certain countries like
UK and US for the purpose of forecasting at a SKU + Key account level.

Only a small number (Key accounts) of the customers will be mapped and the rest of
them will be bundled together as one group (customer group 99). This small number of
key accounts is however subject to change over time and the data in the BW / DP needs
to be managed accordingly.

In the illustration above, the key customers A and B are mapped separately as customers
while customers C to F are grouped together as one. This will facilitate working on key
accounts separately and grouping others in one customer group.

Over time the customers will either become key accounts or will fall in the group which
is a cluster of all the customers. This will be handled by changes in the master data in
R/3. The following illustration captures this scenario.

In the above example, Customer B has been merged into the common group and
Customer D has now become a key customer at which planning will be carried out.

Source of Customer Group and Customer:

These fields are available in the R/3 product master and will map exactly as illustrated
above. As and when the customer groups are changed in the R/3 material master, BW
will pick the master data fields of the customer groups.

 There needs to be an identification of the key accounts that need to be mapped

individually and others that go together as a group.

11
RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

 Over a period of time the individual Key accounts will change and the key
accounts clubbed together also will change in terms of its composition.

12
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Planning process: Input from Field

This will be uploaded to BW via VB tool & can be fetched in APO Infocube

List of key figures

The key figures required for this purpose of making it available in demand planning
differs in certain geographies and the details are indicated below;

13
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Data Archiving
After the completion of the planning run, the planning inputs provided by each of the
entities need to be extracted to a BW InfoCube from APO Infocube.

This is to facilitate a backup strategy in the event of a loss of planning data and reporting
on the forecasting key figures. The scope of archiving would include all the key figures
that are present in the live-cache. The key figures present in the InfoCube do not need to
be backed up as they are anyway sourced from the InfoCube in BW.

List of key figures that need to be archived from each of the planning areas are the ones
that are present in the live cache. This means every key figure other than the ones staged
by BW needs to be archived.

The same concept holds true for API Planning area as well.

3.0 Lifecycle Section: Not Applicable

4.0 Glossary : Not Required

14
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Appendix 1: Characteristics and Attributes

All the below specifications are for R/3 Extractions only. For Non-R/3
extraction it will be a external flat files upload from the respective market as per the
template required for BW.

1.1 Local code:

There will be two Z tables ZBW_LOC_CONFIG & ZBW_LOCALCODE for maintaining Local
codes. If the market is planning based on some legacy code, it will be populated in table
ZBW_LOC_CONFIG. If the market plans at the SAP material level, then the local code will be
prefix with country and division in the second table ZBW_LOCALCODE.

1.2 Region & Sub-region:

Based on the country key from customer Master, the region and sub-region can be
extracted from the table ZMT_CNTREGION. This will be picked up based on the
material types. For finished goods & API Planning we will have a different Region &
Sub-regions.

1.3 Customer Group1:

Even though the customer group 1 fields available from the Billing table, it is advisable
to bring the customer group 1 field from customer Master table KNVV-KVGR1 so that if
there is a change in the customer group1, it will be taken care of the past records also for
realignment of CVS in BW and APO

1.4 Location:

Location will be picked up from T000 Table. Location idenfitification for CVC
generation will be passed on the material master availability in sales org level in R/3 .
( Division level as per APO terminology)

1.5 Country & Currency:

Based on the company code from Billing table VBRK-BUKRS, Currency needs to be
picked up from V_T001-WAERS and the country can be picked up from V_T001-
LAND1

15
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

1.6 Combo Molecule, Major Molecule, Dosage form and Brand:

For every material, product hierarchy will be picked up from sales table MVKE-PRODH.
For example material 1000405 will have a value as 010010010020400101.

From the table V_T179-PRODH , the first 13 digit ( level 5 ) 0100100100204 defines
the Molecule and the first 16 digit ( level 6) 0100100100204001 will provide the value of
Brand and the 18 digit code ( level 7 ) 010010010020400101 will provide the Dosage
form.

Product Hierarchy can be access thru Standard Function Module

RV_PRODUKTHIERARCHIE_PRED_GET

At the market level, the major molecule will be picked up from Z table ZMT_MATMOL
directly. This table will hold both information about major and combo molecule.

1.7 Strength

Strength will be picked up from MARA-WRKST

1.8 Pack size:

First pick up the Pack size from MVKE-VRKME and then go to T006A-MSEHL to get
the description. BW will need to keep both the unit and description but only unit will be
moved to APO for planning.

1.9 Attributes:

For all the Attributes (product status, Sales Pack unit, and Regulatory and Division
class) system will filter the materials based on the Sales Org status (MVKE-VMSTA)
which has field values 05, 11, 91 & 92. This can happen between BW & APO for CVC
generation. Ranbaxy requires only active CVC for planning. Any materials has
discontinued status is not required for APO Planning.

1.10 Primary Pack

Primary pack will be directly picked from R/3 material master at the market level
from MVKE-MVGR1

16
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Appendix 2: Key Figure List

# Key Figure Definition

1 Gross Sales Qty The sum total of ‘invoiced’ quantity of a SKU for a location during
a time bucket. ‘Invoiced’ means quantity against which the
business (Country) has earned revenue during the bucket. This
quantity is charged / invoiced at a pre-defined rate during a period

2 Net Sales Quantity This figure is derived by netting out the Saleable Stocks Quantity
returned by the market (Market Returns) from the Gross Invoiced
Sales Quantity, during the same bucket.

The net invoiced sale indicates the net stock which has been
invoiced to the market in that bucket.

3 Planned Bonus Stocks issued free against the invoiced quantity

Ex: 10 + 1, which indicates against every 10 units of purchase, 1

unit would be given away as free. (10%)
4 Actual Bonus Bonus Stocks actually issued (free) in the time bucket in which the
Bonus was Planned
5 Planned Promotion Promotion offered for the SKU in Quantity - Sales UOM
6 Impact of Promotion Lift in Sales because of the Planned Promotion in History
7 Free Delivery / FRS Stock issued to the market out of saleable stock.
FRS is that quantity of a SKU, which is issued free (zero charge)
to the customer in a time bucket. This is against the claims raised
by the customer.

This includes -
1. Goods issued under FRS: Free Replacement Scheme
document. Stocks are issued in to settle claims of stockists /
distributors
2. Samples stocks being created out of saleable stock
3. Stocks for trials distributed out of saleable stock
8 Short Dated Qty Sales Quantity sold to salvage stocks from expiry write-off in a time
bucket. The stocks with residual shelf life, may not be acceptable
to the normal sales channel, & may be sold to select distributors /
institutions at a discount / special terms. Such sales are termed as
Short dated sales.
9 Net Sales Orders Quantity The Sales Order history relevant to each bucket at SKU level.
These are sum total of customer orders raised towards Ranbaxy
in that country / location. These sales orders will be the sum of
serviced & un-serviced orders, so as to reflect the SKU demand.
10 Live Back Orders Sales Orders which were to be seviced in the last month, but
could not be serviced for want of stocks and are valid to be
serviced in the current month.

Such order are termed as "Live Back Orders"

17
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

# Key Figure Definition

11 Samples to Market & Physicians Samples to the Market & Physicians
12 Net Sales Price Net Sales Price (applicable to the month). The rate at which the
sales of the SKU would be registered for the market.
13 Net Selling Price- Editable Editable NSP to represent changes to NSP in future if relevant

14 Secondary Sales Secondary Sales data from the Market which indicates the SKU-
wise sales at the same level of UOM and geographical level at
which forecasting is being done.

The bucket too should be the same for the past history

15 Market Data in Sales Packs Market data in Sales Packs will be given by Country based on the
Characteristic levels identified
The data in Excel format will be uploaded by BW
16 Market Data (KG) Market data of API in Kg (as per label claim) as in DF Sales pack

17 Market Prescription Data IMS Prescription data in DF Sales Pack

18 Estimated primary Sales An estimation of Primary Sales based on the stockist' / Dealer'
stock levels, sales in the past month & the status (local or
upcountry)

19 Adjusted History History Used for Forecasting after manual outlier correction in a
data view.

This correction / adjustment will be done by RDP in conjunction

with the Sales Head

Adjustment will take into account the impact of information Key

Figures - FRS / Bonus / Promotions

18
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

# Key Figure Definition

20 Adjusted History in KG History Used for Forecasting after manual outlier correction in a
data view in KGS

This correction / adjustment will be done by the US Supply Chain

Head.

21 Final Forecast Previous Cycle Final Forecast Generated in the last Planning Cycle

22 Final Forecast % Deviation Hit Ratio (FF vs Actuals). This will be calculated as (A/FF)
Accuracy computed based on Actuals of M1 generated in M0

23 Base Forecast - Previous Cycle Base Forecast from Previous month

24 Base Forecast % Deviation Hit Ratio (BF vs Actuals). This will be calculated as (A/BF)

Accuracy computed based on Actuals of M1 generated in M0

25 Sales Head Forecast Previous Sales Head Forecast from Previous month
Cycle
26 Sales Head Forecast % Deviation Hit Ratio (SHF vs Actuals). This will be calculated as (A/SHF)

Accuracy computed based on Actuals of M1 generated in M0

27 SC Head Forecast Previous cycle Supply Chain Head Forecast from Previous month

19
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

# Key Figure Definition

28 SC Head Forecast %Deviation Hit Ratio (SCHF vs Actuals). This will be calculated as (A/SCHF)

Accuracy computed based on Actuals of M1 generated in M0

29 Customer Forecast Customer Forecast

30 Statistical Forecast Previous Statistical forecast generated by the system in the previous cycle
Cycle

31 Statistical Forecast Statistical Forecast generated by the system as per forecast

profile in Sales Packs

32 Statistical Forecast - 2 Statistical Forecast 2 - KF for Trials

33 Statistical Forecast (KG) Statistical Forecast generated by the system as per forecast
profile in Kg of API. KG conversion will be based on strength as
mentioned on Sales Pack/ Label Claim

34 Base Forecast Base Forecast is generated by the RDP in Sales Pack on monthly
basis

20
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

# Key Figure Definition

35 Field Forecast Forecast provided by Field Sales Managers. This could be at a
key account level or at a DC Level as relevant to the market.

36 Sales Head Forecast Sales Head's Forecast in UOM

37 SC Head Forecast SC Head Forecast in Sales UOM

38 Final Forecast Final consensus forecast in Sales UOM after Business Review

39 Base Forecast (KG) Base Forecast in Kg of API

40 Sales Head Forecast (KG) Sales Head's Forecast in Kg of API

41 SC Head Forecast (KG) SCH Forecast in Kg of API. KG conversion will be based on

strength as mentioned on Salt Conversion

21
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

# Key Figure Definition

42 Final Forecast (KG) Final Forecast in Kg of API

43 Sales Head Forecast (Value) Sales Head's Forecast in Currency

44 Final Forecast (Value) Business Approver's Final Forecast in Currency after Business
Review

45 Release Base Forecast Base forecast which will be released to Sales Head after RDP has
generated the Base forecast.
For US - the same is released to the Supply Chain Head

46 Release SCH Forecast Suply Chain Head' forecast which will be released to Sales Head
(only for US)

47 Release Sales Head Forecast Sales Head forecast which will be released to Business Approver

48 Release Final Forecast Final forecast which will be released for Weekly Split & Release to
SNP

22
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

# Key Figure Definition

49 Exchange Rate Exchange Rate for conversion to USD

50 Forecast in USD Valuation in USD

51 SNP Proportional Factor Split into Calendar Weeks from monthly forecast for release to
SNP

52 SNP Release Forecast Weekly forecast to SNP

53 Forecast Scenario (KG) New Product Forecast in KG derived as percentage of IMS data

54 Tender Award Quantity Tender Quantity awarded / contract

55 Dummy Amount KF 1 Dummy Amount for future Use

23
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

# Key Figure Definition

56 Dummy Amount KF 2 Dummy Amount for future Use

57 Dummy Number KF 1 Dummy Number for future Use

58 Dummy Number KF 2 Dummy number for future Use

59 Dummy Quantity KF 1 Dummy Qty for future Use

60 Dummy Quantity KF 2 Dummy Qty for future Use

61 Dummy Quantity KF 3 Dummy Qty for future Use

62 Dummy Quantity KF 4 Dummy Qty for future Use

24
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

# Key Figure Definition

63 Dummy Quantity KF 5 Dummy Qty for future Use

64 Dummy Quantity KF 6 Dummy Qty for future Use

65 Field Forecast Previous Cycle Field Forecast from the previous planning cycle

66 New Product Statistical Forecast New Product Statistical forecast

67 Net Shipment Qty Net Shipment Qty will be used to minus net shipments from the
forecast before releasing the forecast to supply

68 KG Conversion to API Used for calculating the KG value of the forecsast, where
applicable

69 Base Forecast (Value) Used to value the base forecast

25
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

# Key Figure Definition

70 Percentage Free Quantity Percentage Free Quantity

71 Adjusted History – IMS Adjusted IMS history will be used to run statistical forecasting for
new products. This KF will also then allow like modellin
functionality to be used.

72 Weekly Final Forecast Weekly Final Forecast

73 Weekly Split Ratio Weekly Split Ratio

74 Weekly Split Editable Weekly Split Ratio which is editable, before releasing the final
forecast to Supply

26
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Appendix 3: CVC creation for new products from R/3.

Functional Description

The objective of this document is to create master data combination in R/3 for the
purpose of generating CVC for APO Demand planning. The master combination
will be populated into Z table initially for the past 12 months and thereafter every 2
weeks.

BW will pick this Masters from the Z table mentioned above for extracting it into
their info cube for new CVC Creation and finally this info cube will populate the
APO info cube which will finally create CVC in Demand planning

Following is the list of Characteristics & logic required from various sources in
R/3.

S. Characteristics Typical Source (R/3 Extraction logic

No. Value field)
Customer Dimension
1 Division ( sales 7000 V_TVKO- Consider only the sales orgs
org) VKORG which are relevant for planning
based on the flag on the field
V_TVKO-BOAVO
2 Division Name US RPI V_TVKO- Name of the sales org
VTEXT
3 Customer Group In Division A613- For the above selected sales org, If
7000 KVGR1 the condition record exists for
CustomerGroup in A613, pickup
CG 91 – all the condition records of valid
McKesson from 01/01/04 with the material
CC92 - combination. Write the customer
amerisource group in this column. Knockout
CG 99 –All the line items which are having
deletion indicators based on the
Other filed KONP-LOEVM_KO and
customers also then keep all the Materials
with sales org & cutomer group
combination in the internal table.
Based on the condition record
ZCVC, Pick up the sold –to ID
from KNVV table based on the

27
RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

customer group1, sales org.

Then validate the customer
based on the account groups
Z001 & ZU01. Then validate the
customers based on the
deletion Indicator (KNA1-
LOEVM and KNVV-LOEVM).
Then validate the customer
again for sales block (KNA1-
AUFSD, KNVV-AUFSD, KNA1-
CASSD and KNVV-CASSD).
 
 
Once selected the Sold-to, then
go to KNVP table to pickup the
payer ID and Ship-to ID.
 
Payer ID:   From table KNVP,
based on the sold-to customer
(KUNNR) and sales org
(VKORG), pickup all the payer
based on partner function RG.
At any point in time there
should be only one RG for a
sold-to.
 
Ship-to ID:  based on the table
entry on ZNCVC_SOLD_TO,
for those sold-tos, From table
KNVP, based on the sold-to
customer ( KUNNR) and sales
org ( VKORG), pickup all the
ship-to IDs based on the
Partner function WE. After that
filter the them by deletion
Indicators (KNA1-LOEVM and
KNVV-LOEVM) and blocks
(KNA1-AUFSD, KNVV-
AUFSD , KNA1-CASSD, KNA1-
LIFSD, KNVV-LIFSD, & KNVV-
CASSD). After this, do another
filter based on  KNVV-KVGR1
to validated the above customer
group from condition record.
Pickup only those ship-tos.
finally Create a master record
for each ship-to
.
If there is no sold-to found, then

28
RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

the condition record is in-valid. If

the sold-to is found and there is no
payer & ship-to, then Payer ID and
ship-to ID will be blank

The initial run will be for the past

12 month. We will require a
separate program which will be
used for every roll-out. Thereafter
this will run once a week based on
the condition record creation.

Product Dimension
1 SAP Material 1002851 MARA- The material codes are populated
Code MATNR into this Ztable thru two different
ways.

1. From the above internal table

get all the materials & validate
against tables MARA & MVKE
for any deletion indicator and if
there is no deletion indicators, then
populate this material field against
those sales org & customer group,
payer ID and sold-to ID along with
a date stamp.
2. Get Active Material numbers
created in the past 12 months from
MARA-ERSDA based on the
creation date and then filter by
deletion indicator in MARA-
LVORM. Then check for these
material availability in MVKE
table. If the material is not
available exit. If available Then do
further filters based on sales org
data MVKE-LVORM (There will
be multiple distribution channel
under one sales org. If it is active
at least in one dist.channel, it
should be considered) and then do
further filter based material status
from MVKE-VMSTA. Only
values 05, 91 & 11 will be
considered. Then the next filter
will be based on the field MVKE-
MVGR4 considering values from
P1 to P7. Then remove the
products if they have a value MTO

29
RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

in MVKE-MVGR3.

2 Material MAKT- Material description

Description MAKTX

Geography Dimension
1 DC code 1310 MARC - Based on the active sales org, all
MATNR the plants assigned to the sales org
will be picked up from table
TVKWZ-WERKS. Then system
will check the availability of the
above material in those plants from
MARC-MATNR. If available,
then populate the DC code for the
combination of sales org, customer
group, material & plant. If the
material has deletion status in
MARC- LVORM, don’t populate.
If the material is exists in more
than one plant from the above
plants for a specific sales org, then
create a new line item with the
sales org, customer group, and
material & Plant combination.

Execute the above program on a

week basis and check for new
additions in the table. If the there is
any new additions, then update the
date stamp.

2 Country code IN V_T001W- SAP country code assigned to the

LAND1 above plant.

3 DC name Kolkata Depot V_T001W- Name of the Plant

NAME1
Key figure
1 KG Conversion 1 MARA - Default Value as 1, unless
NORMT maintained by user

Note:

30
 RANBAXY User Requirement Specification
LABORATORIES LIMITED
SOP: User requirement Specification (DP Staging)
Document No. SCM_BW_URS_1.0 Effective date:
System Name: BW-DP System Version: 3.1 Release:
SDR/SCR Number: Project Owner Manager: Govind Jaju

Now check for those materials created in the past 12 months where the customer
group is blank, then populate those line items with customer group 99. When ever
the line under go changes, please change the date stamp.

31