General presentation

Objective of the application

The aim of this application is to give a P&L reporting adapted to Soda Ash GBU requirements.

It is based on the corporated P&L query and offered special axis and calculation for Soda Ash.

Specifications: https://drive.google.com/open?id=1Q-QjleNGzLF1tbhALtbjN0hdp9x9Y4zBrR2gURi3yw4

Owner : RtR reporting

IS RtR Reporting coordinator is Charlotte Rollier.

Usage information

History

The project was done in 2018.

Roles & Access

Roles and access

List of application role + menu role  and explanation if we have several applications role with specials rules.

Authorization objects

Link to the BW Catalog of role

https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I

Dataflow overview

https://drive.google.com/open?id=1KvtTRYpRfKjRJNCx0sxGMGQVSe04ARE9V0OnVvd70uI

Reporting documentation drive folder:

https://drive.google.com/drive/folders/1D0RpGQ3rTMDh_7S0AvhWBRkcT-2buhfZ

RtR reporting knowledge transfert documentation:

https://drive.google.com/open?id=1zwz7IlL6c9Eiurm325Whcl99J1OJ517mo22pIdqYRtw

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

Why a dedicated flow for Soda Ash P&L?

Due to existing P&L flows complexity, to enhance all P&L flows with axis and calculations required by Soda Ash would have been more risky, more expensive and would have badly impacted corporate P&L flows complexity and performance.

Moreover, the reporting requirements could not be achieved directly in a BeX query on the multiprovider MVCOPA01 or very difficult to maintain and with uncertain performances.

Extraction explanations:

Why a query as Infoprovider?

The flow could have been based directly on the multiprovider MVCOPA01, however, tests reveal it was a lot slower than to retrieve the data from a query. Moreover, it permits to retrieve some shared restricted key figures and to manage the quantity translation (material dependent) in the BeX query.

Query as Infoprovider - encountered issues

• The unit linked to the key figure "N8110 - Qty Sold - VKG" is always empty after the extraction => it seems that query as infprovider don't provide the unit for restricted key figures. The workaround was to use the unit of the key figure "Qty invoice - VKG" which is not restricted. Another possibility would have been to manage the VKG conversion in the flow and not in the query.
• It was necessary to add the detail material and unit in the infosource, else, the extraction dumped. It must be due to the fact that without this level of detail, different units are agregated. The dump occurs when the unit linked to the key figure "N8110 - Qty Sold - VKG" was not correctly filled, maybe the problem would not occur anymore at the moment the converted unit is in the key of the inforsource. 

Extraction perimeter

The global filters of the extraction are similar to the P&L query (BW_QRY_MVCOPA01_0002).

Except that the P&L by BU is not all GBU SD , but GBU SD for activities only

• 25

• 51

• 82

Also, we need only BFC lines until Contribution margin, so only necessary BFC accounts are extracted.

Quantity translation

Quantities are needed in VKG unit for calculations done inside the flow. The translation is managed in the source query. Nevertheless, if the quantity translation could have not been done, then the quantity is considered as VKG in the transformation (calculations are done in scenarii where there are few risks this case happen). 

Type of flow (C_FLOWTSD)

It can be #, internal or external.

The type of flow is used for internal / external calculation in Bex formulas: an external key figures has necessarly C_FLOWTSD = # or E, and an internal key figures has necessarly C_FLOWTSD = # or I.

It is used in order to be able to split a key figures internal and external part under different axis. For instance, for some scenarii, the internal part of the sales costs will be in BU Europe and the external part in the BU Seaborne. 

Type of flow (display) (C_FLWTDSD)

It can be #, internal or external (however, all amounts assigned to an identified Soda Ash scope should be either internal or external).

This type of flow is used for display only and not for the calculation of the internal / external part, which means that external part of a key figure could be displayed as internal in the report (for instance, for the scope E1, the internal sales are displayed as external). 

Business Rules:

Step 1:

The determination of Soda Ash axis is done using the rules in the table ZBW_PL_SD_RULES.

It consists of interval of values for several criteria : landscape, activity, partner activity, partner GBU, BFC management area, company, product hierarchy, division and GBU zone.

Reading the table ZBW_PL_SD_RULES the tranformation will obtain a scope ID for which corresponds a value of each Soda ash axis: Sourcing (origin), BU Soda Ash (can be Europe, NAFTA or Seaborne), Type of Flow, Type of flow (display).

Step 2:

Then reassignments and new lines are created, each case is identified by a specific SD value type:

• When BFC account is "N8110" and BU SD is Seaborne:
  • SD value type = "N8110 destocking" : 
    • the external part remained in Seaborne and displayed as external (c_flowtsd and c_flwtdsd = E) 
    • A new line is also created for the external part under BU SD Europe or NAFTA and displayed as internal (BU SD = sourcing, c_flowtsd = E and c_flwtdsd = I)
  • SD value type = "N8110 stocking" : 
    • the internal part is moved under BU SD Europe or NAFTA and displayed as internal (BU SD = sourcing, c_flowtsd = I and c_flwtdsd = I) 
  • SD value type = "R10000 stocking" :
    
    • A new line is created under BU SD Europe or NAFTA and displayed as internal, with amount = qty internal * transfert price (BFC account = R10000, BU SD = sourcing, c_flowtsd = I and c_flwtdsd = I). The quantities are cleared.
  • SD value type =  "R10000 destocking" : 
    • A new line is created under BU SD Europe or NAFTA and displayed as internal, with amount = qty external * transfert price (BFC account = R10000, BU SD = sourcing, c_flowtsd = E and c_flwtdsd = I). The quantities are cleared.
  • SD value type = "R15400 destocking" : 
    • A new line is created under BU Seaborne and displayed as external, with amount = - qty external * transfert price (BFC account = R15400, BU SD = SEA, c_flowtsd = E and c_flwtdsd = E). The quantities are cleared.
• When BFC account is "R10000" and BU SD is Seaborne:
  • SD value type = "R10000 to SEA":
    • the external part remained in Seaborne and displayed as external (c_flowtsd and c_flwtdsd = E) 
    • The internal part is moved under BU SD Europe or NAFTA and displayed as internal (BU SD = sourcing, c_flowtsd = I and c_flwtdsd = I)
• When BFC account is "R15400" and BU SD is Seaborne:
  • SD value type = "R15400 to SEA":
    • the external and internal are moved under BU SD Europe or NAFTA and displayed as internal (BU SD = sourcing, c_flowtsd = # and c_flwtdsd = I)
• When BFC account is "R10600" and BU SD is Seaborne:
  • • the external part remained in Seaborne and displayed as external (c_flowtsd and c_flwtdsd = E) 
    • The internal part is moved under BU SD Europe or NAFTA and displayed as internal (BU SD = sourcing, c_flowtsd = I and c_flwtdsd = I) (may not have internal part for this BFC account)
• When BFC account is "R12900" or "R12910" 
  • if plant = "52LB", "54PB" or "5B00" and ship-to party = "8820122" (values to maintain in c_glbfilt PL_SD/THPT_PLT and PL_SD/THPT_SHIPT) :
    • SD value type = "Thruput to Seaborne", BU = Seaborne, c_flwtdsd = E
  • Else if plant = "GRM" and ship-to party = "2106497","2107912" or "2108850" (values to maintain in c_glbfilt PL_SD/ RSTK_PLT and PL_SD/ RSTK_SHIPT):
    • SD value type = "Restock to Seaborne", BU = Seaborne, c_flwtdsd = E
• When BFC account is "R12600" 
  • if plant = "52LB", "54PB" or "5B00" (values to maintain in c_glbfilt PL_SD/ ROYA_PLT):
    • SD value type = "Royalties to Seaborne", Sourcing = NAFTA, BU = Seaborne, c_flwtdsd = E
• Else
  • BU SD = Sourcing, c_flwtdsd= I (normal case, these key figures are not relevant in P&L Seaborne)

When new lines are created, the flag c_nwline is filled, so, to restrict key figures on c_nwline = # enable to retrieve the original P&L lines. Note that when a line is split in flow type I (Internal) and E (external), it is not considered as a new line because flow type I must be used in queries only for the internal part and flow type E only for the external part, so, in the reporting, the sum of the 2 lines is equivalent to one line with flow type #.

Rules maintenance (table ZBW_PL_SD_RULES)

The table is maintained by Reporting Plateform using the program ZBW_PL_BY_BU? (TODO: to confirm).

The file to import is maintained here: https://drive.google.com/open?id=1xpkDgqQ4uO5TERMCTrdXClBIEmhH1sgJ

In order to simply the maintenance of this file, there is only one line by scopes. All the criteria are defined as intervals of values, if a criteria is not relevant for a scope, an interval containing all values must be put in the cell and not an empty cell. 

The program ZBW_PL_BY_BU will create a line for each interval combinations and a column "From" and "To" for each criteria and put it into the table ZBW_PL_SD_RULES with information of the user who executed the program and the timestamp of the update.

All the table content is deleted before the update.

The program checks there is not duplicated entries in the file.

The "Description" column of the file is used to update the text of the MD c_scopesd. To avoid any problem, only this file should be used for updating the text of the MD c_scopesd and not a manual update in the MD.

DO NOT USE SM30 in order to update the table ZBW_PL_SD_RULES, the manual change would be replaced at the next update using the program ZBW_PL_BY_BU if the file was not synchronised.

Transfer prices maintenance (table ZBW_PL_SD_TRFPRC)

The table contains the transfer prices for 1 TON for NAFTA and Europe, on a validity period.

The table is maintained in SM30 by Reporting Plateform? (TODO: to confirm).

Redetermination of navigational attributes stored in the cube

Because some navigational attributes are required for the Business Rules implemented in the flow or in order to keep the cube as small as possible for performance purposes, the following navigational attributes are stored in the cube and in consequence, the cube need to be reloaded regularly in case of reorganisation (TODO: full reload to define):

• CPFCTR1_2 BFC Global Business Unit
• C_INTGBUF Intra GBU Flag
• C_PACTIV Partner activity
• C_PGBU Partner BFC GBU
• C_MNG_ACC Magnitude Account
• 0PROD_HIER Product hierarchy
• C_ZONEH1 GBU zone (hierarchy 1)
• C_ZONEH2 GBU zone (hierarchy 2)
• C_DIVISN Division
• C_MNGAREA BFC Gestion Area
• C_COMPPRS PRS Company code

Dependencies with other applications

Data comes from P&L application.

Data loadings

Info providers and objects loaded

Loading frequency

Average performance

Record Keeping

Reporting

Queries End User Documentation

Main queries

Main functionalities

Broadcast

Maintenance

Known bugs

Recurring procedure

Planned Evolution