General presentation

The application provides for a given month, the average sale prices observed during the X previous months.

Data is both :

• Available in reporting through AFO workbook
• Monthly extracted into 2 flat files likely to be loaded into WP1 and PF1.

.List of contacts

• Charlotte Rollier (SBS Finance Service Line) : Data and Reporting Manager
  
• Guillaume Thevenet  (SBS Information Services) : BW Coordinator

Objective of the application

The aim of the application is to calculate and provide to transfer pricing team the monthly average sale price by BFC Company Code, Material, Revenue Type.

Usage information

To be completed

History

This project was developed in September 2019 by Ludovic Depoix with BW technical support of Cyril Bianco.

Rules

Roles and access

Authorization objects

List of authorization objects mandatory for the application.

 See also file maintained by Authorization team : 

• BW Catalog of Roles: Authorization catalog
• BW Authorization:      BW Authorizations

Dataflow overview

Reporting documentation drive folder:

Exemple of dataflow overview :

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

Systems involved

• • Rhodia ERP WP1
  • Solvay ERP PF1

Subject

Retrieved data are coming from PF1 and WP1 COPA module.

Dependencies with other applications

Retrieved data is dependent from standard and advanced DSO (COPA Business Layer) loaded by others applications.

Data Model

Info providers and objects

InfoAreas

DSOs / aDSOs

implemented by another project

• DBCOPA30 - COPA - Solvay Legacy 

• ABCOPA01 - CDSA – Solstice

• ABCOPA04 - CDSA – Not Solstice

Implemented by the Transfer Price project

• APCOTP01 - Transfer price - Entity third party Except

Composite Providers

• CPCOTP01 - Controlling - TP - Sale Price 

Main Master Data

• C_TPREVTY – Revenue Type

Revenue type has been defined in order to segregate the different type of sales, in order to calculate the average sale price for each revenue type.

The revenue type is the combination of 3 components:

• C_TPPARTY - TP Partner Type
• C_TPGEOFL - TP Geographical Flow
• C_TPCUSTD - TP Custom Duty

Main Master Data & Business Rules 

• C_TPPARTY - TP Partner Type

Business Rule : If trading partner (C_PCOMPAN) not empty and consolidation method different from  90 sold and BFC Company of the Trading partner (C_COMPANY__0COMPANY)  not found in exception table (/!\ Posting date in Valid From and Valid To of exclusion table => Related Party

Else => Third Party

• C_TPGEOFL - TP Geographical Flow

Business Rule : We will compare :

- the recipient country (ship to customer country)  C_SHIPID_0COUNTRY

- And  the country of the selling company (company code country):C_PCOMPRS__0COUNTRY  (= C_COMPCDE__0COUNTRY)

- Abroad-export :both countries are different and one of them at least is outside E.U.

- Abroad-EU : both countries are different and inside E.U.

- Domestic (same country)

• C_TPCUSTD - TP Custom Duty
  

The third distinction is only for chinese companies 

If the company code is not a chinese company ( C_PCOMPRS__0COUNTRY  or C_COMPCDE__0COUNTRY <> CN) = > domestic included duties 

If the company code (C_COMPRS) is a chinese company ; to split the domestic line by "included customs duties" or "duty free", based on:

- the invoicing currency (0DOC_CURRCY) <> Local company currency(C_COMPRS_0CURRENCY ) => domestic duty free

- the invoicing currency = local company currency (CNY), the legal entity is SSP CHANGSHU (5991) and the customer is related party => domestic duty free

- other cases => domestic included duties 

• C_GLBFILT - Global Filter
  

The global filter has been used in order to manage some parameters :

• MATNR_WX1 : allows to manage some Material Type exclusion for WP1 line
• MATNR_XF1 : allows to manage some Material Type exclusion for XF1 line
• EXTRPERIOD :  allows to manage the recovery period for the file extraction done in PC_FMCO_TP_01 
• CALCNBPER : allows to manage the number of used periods during the Average Sale Price Calculation
• OFFSET : allows to manage the number of month take in account by Material, plant and Company type into the "CALCNBPER" selection. IF OFFSET= CALCNBPER, the OFFSET parameter have no impact

CALCNBPER (4) & OFFSET (2) parameters example

Main Extractors

Transactional data extractors

• Transcational data was already available in the COPA data model, no new transactional extractor has been activated

Master data extractors

• DTS_DD07T through ODP from WP1 in order to populate info objects’ texts like C_TPSTAT and C_TPREVTY

Data Flow

As shown in the architecture schema, the Sale Price application is based on an hybrid achitecture HANA Calculation feeding a BW composite provider.

The the composite provider is used for bith extraction and reporting purpose.

HANA Calculation views

• CV_FMCO_CO_TP_EU_COUNTRY

This calculation view provide all contries belonging to EU, including Monaco which in not considered as a EU member in ECC T005 table.

this is done through the folling filter expression :

• CV_FMCO_CO_TP_MATNR2_SCOPE

  

This caclulation view provides the scope of MATERIAL that have to be taken into account, after application of the exclusion MATL_TYPE managed through the global filter table. 

The restriction is done through the following filter expression 

Where IP_TP_C_GLBFILT_RULE is an input parameter populated by the master view which embeds the current view either :

• CV_FMCO_CO_TP_COPA_RHODIA

• CV_FMCO_CO_TP_COPA_SOLVAY

The parameter is populated by a constant in the calculation view input parameter mapping.

• CV_FMCO_CO_TP_COPA_RHODIA : 

this Calculation views takes in charge

• all the Rhodia specific business rules in particluar 
• UNION of data coming from the differnt SOLSTICE aDSOs
  • Value Field restriction based on C_FIELDN master data
  • GBU recovery using C _TECMAT attributes
  • the restriction on Material scope based on CV_FMCO_CO_TP_MATNR2_SCOPE with the following Input Parameter :

• In addition, there is a restriction on the scope of COPA document based on the following filter : 
  
  

where Input parameters are driven either by DTP or Query exit variables and related BADIs

CV_FMCO_CO_TP_COPA_SOLVAY

this Calculation views takes in charge

• all the Rhodia specific business rules in particluar 
  
  • Value Field restriction based on C_FIELDN2 master data
  • GBU recovery using C _TECPCTR attributes
  • the restriction on Material scope based on CV_FMCO_CO_TP_MATNR2_SCOPE with the following Input Parameter :

• In addition, there is a restriction on the scope of COPA document based on the following filter : 
  
  

where Input parameters are driven either by DTP or Query exit variables and related BADIs

• CV_FMCO_CO_TP_SALE_PRICE_BUSRULES_01

• CV_FMCO_CO_TP_SALE_PRICE_BUSRULES_01 - New block for OFFSET parameter restriction

This calculation view

• merges the data RHODIA and SOLVAY data coming from the specific sub views
• enrichs the data in order to apply the corporate business rules, 
  • Axys of analysis clauclation in particular the REVENUE TYPE and its compoundings
  • KPI calculation
• Restrict data by month by keys from the offset parameters

• CV_FMCO_CO_TP_SALE_PRICE_BUSRULES_02

This calculation view takes in charge all the UNIT CONVERSION logic using joins with T006 and UOMCMAT2 DSO.

• CV_FMCO_CO_TP_SALE_PRICE_REPORTING

This view

• manages the aggregation at the COPA item document level, using the KEEP_FLAG option on the relevant fields, in order to insure the consistancy of the KPI calculation in the upper level.
• applies the HANA standard conversion function on :
  • UNIT (based on KPI calculatred in CV_FMCO_CO_TP_SALE_PRICE_BUSRULES_02
  • CURRENCY in order to manage the Decimal Shift properly

the customizing is done in the semantic layer using semantic functions

• CV_FMCO_CO_TP_SALE_PRICE_COMPOSITE

This view insure the pushdown HANA of the aggregation at the level requested by the reporting layer.

Data extract (BW)

Only 2 data flows which take in charget through Open Hub of  the extract of  2 Sale price's files likely to be copied respectively in WP1 and PF1.

The DTP extract data in full mode with some restriction

The file location is /exploit/BW/TP/ 

fsl_tpdata_from_bw_to_erp.csv
fsl_tpdata_from_bw_to_rcs.csv

The loading is scheduled by PC PC_FMCO_TP_01 on 3rd working day each month at 5:30

MATERIAL : must be different the Initial

SOURCE SYSTEM : 

• For Rhodia is driven by the following OLAP variable V_0LOGSYS_0011 
• For Solvay is driven by the following OLAP variable V_0LOGSYS_0012 

INPUT PARAMETERS  IPCALMONFROM & IPCALMONTO : 

• Are calculated using gloabl filters parameters EXTRPERIOD and CALCNBPER.
• if EXTRPERIOD is active (ACTIVE = Y) we are in recovery process and the LOW field value is taken into account for the calculation
• if EXTRPERIOD is inactive (ACTIVE <>Y) we are in recurring process and the current period is taken into account for the calculation
• if CALCNBPER is active (ACTIVE = Y) the calcuation take is into account,
• if CALCNBPER is inactive (ACTIVE <>Y) the calcuation use the default value = 3
• if OFFSET is active (ACTIVE = Y) the calcuation take is into account,
• if OFFSET is inactive (ACTIVE <>Y) the calcuation use the default value = 3

Data loading

Process Chain

PC_FCMO_TP_MD : Controlling - TP - Master data

PC_FCMO_TP_01  : Controlling - TP - Sale Price Extracts

This process chain extracts launch the extract through Open Hub.

Loading frequency

The Process chain is likely to be triggered once a month after the closing date (~2nd working day of each month acoording to FR calendar )

Average performance

In WB1 which was a referesh of WBP, and so with volumes likely to be equivalent to those obeserved in production in 2019S1 :

Reporting

Queries End User Documentation

Queries

The 2 mains used query are  :

Workbooks

The query  BW_QRY_CPCOTP01_0003 has been embedded in  the BW_WBK_COTP_0001, in order to  both :

• manage the layout respecting the finance corporate identity.

• provide  some technical information, in case of off line broadcast

Jump

A query jump has been customized from the Synthesis Query to the delailed one, in order to allow the user to get the detail at the Item COPA document level for a specific record of the synthesis.

Of course, the number of retrieved records and overall results are in aligned with the synthesis source record

Broadcast

Maintenance

Init/Recovery procedure

By init/recovery procedure we mean manage the reference period which drives  the extraction of the  2 files in process chain : PC_FCMO_TP_01 .

The common parameter that have to be managed is located in the C_GLBFILT master data attributes 

For the following key

You have to set  :

• Global Filter Active = Y
• Global Filter Low Value = the wished month (ie 201904)
  

Once the recovery process is over, do not forget to inactive the parameter for the recurring process which takes into account the current month.

You have to set  :

• Global Filter Active = N
• Global Filter Low Value = the wished month (ie 201904)