General presentation

Objective of the application

This report / application is to track the Contract price change information by change date. This information is gathered using the change document details. There is no report in ECC which can give this information.

Tool Leader + IT leader of the application: 

Reporting Coordinator : Duarte Costa (Lisbon)

Purchasing Data and P-t-P Interface Manager : Sylvie Severini (Brussels)

Usage information

D&A and buyers are the key users of this report

History

Business requested a BW report that can provide a structure similar to the above mentioned reports in PF1 and WP1 and that is able to provide the price changes per material Base Unit of Measure and also per last condition record unit of measure considering changes done not only to the condition records but the Price Unit information and its quantity conversions.

Requestor: Rui Cabrita

Roles & Access

Roles and access

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

Authorization objects

List of autorisation objects mandatory for the application.

Dataflow overview

The key logic was built in extractor. The extraction logic was built in such a way that the relevant field's (that affect the price changes) old value and new value should be captured in the extraction.

But, the key challenge is we always have the latest values in the tables. Previous values can only be captured using the change documents (CDHDR & CDPOS).

Example: 

If Purchase order unit of measure (EKPO-MEINS) changed from KG to TO...

We will have the value "TO" in the table EINA. But to get the previous value (KG), we need to scan through all the change documents for that Contract.

Extraction logic:

Only the below list of fields are considered for price differences calculations.

Using the function module "CHANGEDOCUMENT_READ", the extractor picks the change documents of the given date range. Only by keeping the above 12 field's changes, remaining changed were discarded.

In that selected documents, for each change document and its contract, the subsequent document will be verified in the same list that was selected. If the subsequent document found in the selected list, we will read that document to capture both old & new values. Old values are considered from old change document number and the new value considers from the next available subsequent change document for the same PIR.

If the subsequent document is not found in the initially selected documents, the program again scans for the next available change document of the contract number. This way of scanning continues for all the relevant 12 fields changes are found. Using both the change documents, it captures both old values & new values of each change document. If subsequent change document is not found for any of the contract, it means; that particular contract is not changed after. It means, the existing value for that contract is the new value for that field.

After capturing the both old & new values of each contract & change document, the extractor gets the outcome in below format.

Each record represents the above said 12 fields old and new values at change document level.

The extractor is available in both the Solvay & Rhodia systems (WP1 & PF1)

Note: Extractor brings both contracts as well as Scheduling agreement information. It will not consider purchase order information.

BW Data model:

Just for the sake of quick understanding here is the field mapping details between data source fields & BW info objects

C_O*→ Old Value

C_N*→ New Value

Extractor brings the data in to BW with both old & new values of the Contract at change document level.

The extraction data mapped to the propagation layer DSO, as it is.

The business logic and calculations are built in between propagation layer & business layer ADSO's. This include the currency conversions also. Since the conversion is based on the document change date, the currency conversion calculations are done at the transformation level using the conversion rate "CAR1".

Reporting documentation drive folder:

https://drive.google.com/drive/folders/1TCUoZDVUBcP1eC6og52brDN2fScOrAVd

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

All the KPI calculations are explained in this section

All the below calculations are performed in below two transformation's end routine.

ADSO APPURCNT1 -> ADSO ABPURCNT1

ADSO APPURCNT2 -> ADSO ABPURCNT2

1. Old Price in Order Price Unit in Info record currency = Old Net Price / Old Price Unit

2. New Price in Order Price Unit in Info record currency = New Net Price / New Price Unit

3. Delta % in point 1 & 2 = (1 - 2) / 1

4. Delta in point 1 & 2 = (1 - 2) 

5. Old price in base UoM in info record currency 

6. New price in base UoM in info record currency

7. Delta % of Old price in base UoM in info record currency & New price in base UoM in info record currency

8. Delta in Old price in base UoM in info record currency & New price in base UoM in info record currency

9. Old Price in Order Price Unit in Vendor Currency

Convert the NETPRICE to vendor currency using FM "Convert_To_Foreign_Currency"

10. New Price in Order Price Unit in Vendor Currency

Convert the NET_PRICE to vendor currency using FM "Convert_To_Foreign_Currency"

11. Delta % of old & New order price Unit in vendor currency

Convert the K_DELPUP to vendor currency using FM "Convert_To_Foreign_Currency"

12. Delta of old & New order price Unit in vendor currency

Convert the K_DELCUP to vendor currency using FM "Convert_To_Foreign_Currency"

13. Old price in base UoM in Vendor currency

Convert the K_IRBUOP to vendor currency using FM "Convert_To_Foreign_Currency"

14. New price in base UoM in Vendor currency

Convert the K_IRBUNP to vendor currency using FM "Convert_To_Foreign_Currency"

15. Delta % of Old price in base UoM in Vendor currency & New price in base UoM in Vendor currency

Convert the K_IRBUOP to vendor currency using FM "Convert_To_Foreign_Currency"

16. Delta in Old price in base UoM in Vendor currency & New price in base UoM in Vendor currency

Convert the K_IRBUNP to vendor currency using FM "Convert_To_Foreign_Currency"

Dependencies with other applications

This is an independent data model and do not dependent on any applications

Data loadings

Info providers and objects loaded

Process Chain: PC_SPRINT_CONT1

Loading frequency

Daily loads (except week end) using pseudo delta with current day - 4 days logic

Average performance

Record Keeping

Data loaded from 2018 - till date

Reporting

Queries End User Documentation

Main queries

The core Query: 

BW_QRY_CPPURCNT1_0001 - SPRINT - PUR : Contract Price Analysis (Core Query)

Filters:

Rows:

Columns:

All the key figures are displayed without dimensions (units) as requested by business

Exceptions:

As per the business requirement, there are two exceptions in the query to show in different color where...

Delta variance is greater than 5%

Variance greater than 200

Selection Screen:

Report Output:

Exception:

Main functionalities

Broadcast

Maintenance

Known bugs

Recurring procedure

Planned Evolution