General presentation

Objective of the application

The aim of this application is to provide a report based on Working Capital (WC) Stock query valuated with Integrated Margin cost unit (IMc)

The general idea is to have a report that looks like WC query “BW - Working Capital Inventories (Core Query)” (BW_QRY_MVFIWC01_0005)  but has quantities valuated at IM unit cost.

The application owner is Rosario Sevilla
The reporting coordinator is Charlotte Rollier.

Usage information

History

This application was created in 2018, requested by Technology Solution.

Roles & Access

Roles and access

This application is separated from Working Capital application (required to be in a separate role menu)

Authorization objects

Link to the BW Catalog of role

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

Same authorization as Working Capital application, except the WC account type, which is only "INVENTORY" for this application.  Moreover, as we needed the cube Material Stock (CUB_IC001) for Logistic Inventory at Integrated Cost report, and this cube doesn't contain the PRS company code, then the perimeter role on 0company is also required.

Dataflow overview

Reporting documentation drive folder:

BW folder : https://drive.google.com/drive/folders/10E2KpooKYimueCVIYp_T22FgekLM269-

Specifications : 

Dataflow :

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

Extraction to Data propagation explanations:

Why a query as Infoprovider?

• One of the main concern of this solution was to totally fit with Working Capital inventories key figures. In consequence, the query as Infoprovider option allowed to directly use WC key figures as data source and seemed the best way to keep synchronised if WC rules changed, contrary to a flow based on WC DSO. Moreover, the stock quantities must be valuated with the IM cost of the month to analyse so the valuation can't be done at a movement level or it would necessit to duplicate the movements for each concerned months.
• A solution using APD was tested first (before exclusion of Spare parts) but performances were bad (also dump occured (max number of cells reached) if the partionning option was not activated in the APD).
• Another solution could have been to calculate IM stock in the query:
  - using WC multiprovider and adding a new infoprovider with IM costs in MVFIWC01 => it allows to use WC Key figures but there is already a lot of infoproviders in MVFIWC01
  - using a new multiprovider based on WC inventory cubes and a new infoprovider with IM costs => we would have to reproduce WC key figures in the new multiprovider
  In any case, it would not have been possible to have all the needed characteristics in the IM costs provider (as the WC account sub type for instance), so "selection constant" would be necessary in the query, decreasing flexbility and performance of the query.

Note that the query as Infoprovider extracts the WC accounting view even if the conso view is the only view needed in the reports. However, the conso view use the most up-to-date percentage of integration of the company so can not be stored. In consequence, the consolidation view is recalculated in the reporting query.

Semantic groups

Semantic groups were defined in the DTP in order to control the size of the package, without defining semantic group the package size parameter in the DTP was not taken into account.

The following link provides interesting informations on the use of semantic groups with query as Infoprovider:

https://wiki.scn.sap.com/wiki/display/BI/OT-QPROV-SEMANTIC

Exclusion of valuation class Z120 (Spare parts) and Z175 (Office supplies)

This valuation classes are not relevant for the inventory at integrated cost reporting. The filter was added in the DTP and delete around 80% of the total volumetry.

Months to extract

0calmonth on the source query do not correspond to the month of the stock balance but to the month of the stock movement, so there is a restricted key figure by month that cumulates entry/out of stock.

Key figures are calculated for the month M set in MD c_glbfilt (stream = "INV_IM", rule = "APD_MTH") and the month M-1:

This entry in c_glbfilt is used to force reload of historical months, usually, active = "N" => the query is restricted to current month M and M-1.

Key figures transposition and 0calmonth determination

In the query, we created different KF for month M and M-1 and we use different rule groups in the transformation in order to create a line by month in the target DSO:

Months M and M-1 are based on the c_glbfilt entry (stream = "INV_IM", rule = "APD_MTH").

Query as Infoprovider - encountered issues

• Not full aggregation of values (the source package contains several lines on the same key)
  The following document warns about this problem, in particular when the query retrieves data from several providers. 
  https://wiki.scn.sap.com/wiki/display/BI/OT-QPROV-AGGR

  
  In my case, the problem occured with data from only one provider when BIA indexs were used (for instance concerning material 136776 and plant 7603 when no restriction on the material/plant in the DTP). It was solved using "summation" aggregation mode for the ratios in the transformation
  
  
• Unit decimal places are taken into account
  The ratios in the source package where rounded depending of the decimal place defined for the unit. Some quantities for those units contained decimals and it generated some gaps with the result in the WC query.
  For instance, CEN unit:
  SPRO → 
  
  
  The workaround was to use "no dim" formula in the source query in order to avoid the rounding. Those "No dim" key figures are used instead of normal key figures but reassigned to the correct unit in the transformation.

Useful link for any problem with query as Infoprovider:

https://wiki.scn.sap.com/wiki/display/BI/OT-QPROV

Data propagation to Business transformation layer explanations:

Two flows: account type S and M / GBU determination

In order to 100% follow Working Capital rules, activity/GBU determination must be based on c_subact2 attributes for account type S and must be based on c_matpnt3 attributes for account type M. In consequence, as we don't want to store the MD attributes in the flow, it was decided to create 2 separated flows and gather the infoproviders under a unique multiprovider. In the multiprovider, for account type M, c_matpnt3 attributes are assigned under c_subact2 attributes which are used in the reporting.

The infosource IB_FIGL_13 is used to share the same business rules between the 2 flows. 

Integrated Margin valuation

IM unit costs are read from DSO  ODS_PCP4 for cost variant ZFO, and if not find, for other cost variants except ZIP.

If IM costs change in ODS_PCP4 for historical data, then it is necessary to manually reload this flow in order to take into account the new unit costs.

Selective deletion / write optimized DSO:

Deletion of data before loading is necessary to delete old keys that could not exist after a reloading. To use a standard DSO with activation of request was then not necessary, so write optimised DSO were used for performance reason and the suppression of data is done by selective deletion in the Process Chains using the TVARVC variable IMWC_MTH_TO_LOAD. Moreover, we keep only 2 years of data, this is also managed in TVARVC variable IMWC_MTH_TO_LOAD.

The program Z_UPDATE_TVARVC_IMWC_MTH in the Process Chain is used to update TVARVC variable IMWC_MTH_TO_LOAD, using variant Z_COMPLETE it will lead to the deletion of months [M-1;M] and < current Month-23.

The option "Delete TVARVC selection?" of Z_UPDATE_TVARVC_IMWC_MTH ensures that all previous selection that was added  in TVARVC IMWC_MTH_TO_LOAD is deleted before to add the new selection.

Reloading procedure:

If the months to reload are not current month or current month -1, enter the month you have to reload in MD c_glbfilt (stream = "INV_IM", rule = "APD_MTH" and c_active = "Y") then execute the Process Chain PC_FIWC_10 => it will reload month [M-1;M].

When the reload is finished, you have to move back c_active = "N" in c_glbfilt or daily process chain will still load the month you had set.

Init procedure:

In order to reinit the flow, you can use the Process Chain PC_FIWC_09 that will automatically reload the 2 years of data but you need to manually delete data in the DSO and cubes first.

Please note that the init Process Chain will update c_glbfilt MD  (stream = "INV_IM", rule = "APD_MTH" and c_active = "Y") and put c_active to "N" at the end. In consequence, if the Process Chain was not ended correctly or you decided to abort it, you must put c_active to "N" manually in order to allow the daily Process chain to retrieve.

Master Data

Product Group (IC) (c_pgmi2__c_grppmi) : it corresponds to the product group (Item Class) from PGMI table and was created for the Dynasis project. For the moment, only TS product groups are needed (MD restricted on group IC*).

Dependencies with other applications

Inventory key figures comes from Working Capital application.

IM costs are retrieved in ODS_PCP4.

Data loadings

Info providers and objects loaded

Main process chains are:

PC_FIWC_10 : Global Process Chain

PC_FIWC_09 : Init Process Chain

Loading frequency

Daily, start around 8pm => the global Process Chain is executed on event Z_EVT_IMWC triggered at the end of D4 Process Chain (complete PC only). The PC variant used to trigger the event is the ABAP program APGR_BTC_EVENT_RAISE_09.

Average performance

Record Keeping

Keep only data greater than M-24 (automatic deletion in daily process chain).

Reporting

Queries End User Documentation

Main queries

• BW_QRY_MVFIWC04_0001: Inventory report at integrated costs (Core Query)
  
  

  This query give KPI "Stock quantity" and "Stock value" that are identical to Working Capital inventory query (BW_QRY_MVFIWC01_0005). Moreover, it provides "Stock IM value" KPI that are the Stock quantity valuated at integrated costs.

  Conversion of unit is done using the conversion type QCT_MATNR3 (using T006 if available otherwise C_MATNR2).
  
  Conversion of currrency is using the conversion type CTK_ZRH2SP (Exchange rate ZRH2 at conversion date determined with variable V_0DATE_0041 = current date by default, can be change in selection screen)

  KPI are in conso view only.

• BW_QRY_MVFIWC04_0002: Inventory report at integrated costs - Monthly FX (Core Query)
  
  Idem BW_QRY_MVFIWC04_0001 except that the conversion of currency is using the conversion type CTK_ZRH2EM (Exchange rate ZRH2 at conversion date = end of month 0calmonth). In consequence, it leads to results different than in the Working Capital report.

Main functionalities

There is a structure with two selections "Months selected" and "Last known day". "Last known day" corresponds to the stock of the current month, so the current stock and must be always available in the reporting, independently of the months selected by the user.

The query use formula variable, same as Working Capital queries, in order to calculate the conso view: it consists to apply the integration rate of the PRS Company (c_compprs__k_intrat) to the KPI.

Broadcast

No Broadcast

Maintenance

Known bugs

Recurring procedure

Planned Evolution