The new wiki link for this data flow is here:

Technical Documentation - Transfer Price historical sales

Please update the doc there and no longer here.

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.

A new OFFSET parameters has been added to the data flow in June 2022 by Ludovic Depoix with BW technical support of Charles Benier.

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 :

Image Added

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

Image Added

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 

Image Added

Image Added

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:

Image Added

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

Image Added

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 :

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

...

ZR_RCS_CA_M67

...

Transfert Price Historical Sales

...

Menu Role

...

 ZR_*_CA_P05

...

 GBU perimeter roles

...

 Role Analysis authorizations

...

 ZBI_RCS_CO_A25

...

 TP – Transfert Price Historical Sales - End User role

...

 Application Role

Authorization objects

List of authorization objects mandatory for the application.

...

CPFCTR1_2

...

BFC Global Business Unit

...

C_AUTHMA

...

Authorization Scope

 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

Image Removed

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 

Image Removed

Image Removed

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 PCin PC_FMCO_TP_01 
• CALCNBPER : allows to manage the number of used priods periods during the Average Sale Price Calculation

Main Extractors

Transactional data extractors

• 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

Image Added

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

Image Modified

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 :

Image Modified

• CV_FMCO_CO_TP_MATNR2_SCOPE

  Image Modified

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 

Image Modified

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 : 

Image Modified

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 :

Image Modified

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

Image Modified

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

Image Modified

CV_FMCO_CO_TP_COPA_SOLVAY

Image Modified

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 :

Image Modified

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

Image Modified

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

Image Modified

• CV_FMCO_CO_TP_SALE_PRICE_BUSRULES_01

Image Modified

• CV_FMCO_CO_TP_SALE_PRICE_BUSRULES_01 - New block for OFFSET parameter restriction

Image Added

This calculation 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

Image Modified

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

Image Modified

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

Image Modified

Image Modified

Image Removed

• CV_FMCO_CO_TP_SALE_PRICE_COMPOSITE

Image Removed

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

Image Removed

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.

Image Removed

The DTP extract data in full mode with some restriction

Image Added

• CV_FMCO_CO_TP_SALE_PRICE_COMPOSITE

Image Added

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

Image Added

Data extract (BW)

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

Image Added

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

20/06/2023 :  PO2 project .

In SAP Transfer Price process will be split in ECO and SCO and will take place at SPIN OFF ECO/SCO  (not D1) 

4 new Open hub has been added  to produce the new files (1 ECO , 1 SCO per SAP system) 

Image Added

The structure of the files has been modified compared to SOLVAY one : Authorization object has been added.

Morever , we apply a filter on the Authorization scope (C_COMPCDE__C_AUTHMA) when files are generated (filter in the DTP) 

The file location is also /exploit/BW/TP/ 

fsl_tpdata_from_bw_to_erp_eco.csv

fsl_tpdata_from_bw_to_erp_sco.csvThe file location is /exploit/BW/TP/ 
fsl_tpdata_from_bw_to_rcs_erpeco.csv

fsl_tpdata_from_bw_to_rcs_sco.csv

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

Image RemovedImage Added

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 CALCNBPERparameters 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 calculationand 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 if CALCNBPER is active  (ACTIVE = Y) the calcuation take is into account,
• if CALCNBPER if OFFSET is inactive (ACTIVE <>Y)  the the calcuation use the default value = 3

Data loading

Process Chain

PC_FCMO_TP_MD : Controlling - TP - Master data

Image Modified

PC_FCMO_TP_01  : Controlling - TP - Sale Price Extracts

This process chain extracts launch the extract through Open Hub.

Image Modified

20/06/2023 :  PO2 project : Generation of the 4 new files will be added in Process chain before PO2 Spin Off

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 :

Image Modified

Reporting

Queries End User Documentation

Queries

The 2 mains used query are  :

Workbooks

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

• manage the layout respecting the finance corporate identity.

Image Modified

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

Image Modified

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.

Image Modified

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

Image Modified

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)