General presentation

OTIF is sub function of Global Sales (analysis of all documents for sales handling costumers or transfer with plants and GBUs).

The term OTIF means On Time In Full, that is an analysis if the delivery was done in correct time and the quantity was delivered in exact time. 

This sub function works with datal handled by Global Sales about sales order lines, scheduled lines, deliveries and invoices.

The load is done daily using the process chain scheduled with delta process. 

List of contacts

• Functional Owner of this report: Jean-Baptiste Cercueil
• IS Reporting Leader: Sandra Silveira (SBS IS)
• BW Coordinator: Guillaume Thevenet  (SBS IS) 
• BW Developer: Nestor Araujo  (SBS IS)

Objective of the application

• The main purpose is to have a detailed core report for all GBU's and also be the data source for SCE Dashboard in Qlikview. 
• Analyze the behaviour of sales processes since the goods issue until the shipment end process.
• Understand the reasons why the process was not finished with OTIF 
• One common & consolidated reporting space for the group
• Rationalization of the “live” solutions and reduction of evolutions requested
• Maintainable and sustainable solution.
• Enabler for productivity actions on OTIF process
• Drillable solution - allowing possibility to detect & analyse problems by showing the link from aggregated figures down to document in SAP.
• Few “core” queries/workbooks design for application (=> Use BW analysis functionalities and not just as extractor).

Reasons

• Revamped reporting BW solution based on RCS (WBP-TR002) but built on old historical architecture (no project on this application for years) - mainly designed for cost reporting.
• Only aggregated monthly data available on PF1.
• Specific solution (cross legacies) built for CO2 footprint with activation of some standard extractors on PQ1.
• More than 250 existing queries in SAP => almost impossible to maintain on an efficient way, most of then not or almost not documented.
• Some previous experiences of common transversal reporting tools : WISE (working cap.) and SPRINT (Purchasing and Procurement).
• “Drillable” solution - allowing possibility to detect & analyse problems by showing the link from aggregated figures down to document in SAP.
• Few “core” queries/workbooks design for application (=> Use BW analysis functionalities and not just as extractor).

History

The OTIF analysis is generated in BI 4 with data based on PPS application just for Rhodia side and load QV application for SBS SPE (Supply chain excelence).

The OTIF for Solvay was created in 2016 in BW after OBAS implementation and it is the source for SBS SPE also.

Usage information

Around 300 users, worldwide with daily update 

Statistics by infoproviders

PS: The providers marked in blue are important to be monitored, the other ones are referent to the old TR solution that should be desactivated soon.

Statistics by queries

General Rules

Functional Specifications

Main Rules

SCE defines 6 different OTIF indicators: On Time Delivery in Full (Customer Request) : Concatenates In full Requested and OTD CR On Time Delivery in Full (1st Confirmed) : Concatenates In full Confirmed and OTD FC On Time Delivery in Full (Last Confirmed) : Concatenates In full Confirmed and OTD LC On Time Shipment in Full (Customer Request) : Concatenates In full Requested and OTS CR On Time Shipment in Full (1st Confirmed) : Concatenates In full Confirmed and OTS FC On Time Shipment in Full (Last Confirmed) : Concatenates In full Confirmed and OTS LC All calculation in this query is managed at Order line level. When several schedule lines matching with one same order lines we are considering sums for quantities and maximum for dates.

1- Rules for OTIF Calculation (Solvay)

DAYS DELAY (Gross)

Achieved Delivery Date

=If([Transportation mode]="PICK UP" Or [Order Document Type Code]="KE";[V_Max Actual GI Date];If(IsNull([V_Max End of Shipment Calculated]);[V_Max Actual GI Date];[V_Max End of Shipment Calculated]))

OTD CR

=If([Transportation mode]="PICK UP";If(DaysBetween(Last Req Delivery];[Achieved Delivery Date])<0;0;DaysBetween(Last Req Delivery];[Achieved Delivery Date])))

OTD FC

=If([Transportation mode]="PICK UP";If(DaysBetween([V_Max 1st ATP];[Achieved Delivery Date])<0;0;DaysBetween([V_Max 1st ATP];[Achieved Delivery Date])))

OTD LC

=If([Transportation mode]="PICK UP";If(DaysBetween([V_Max Last ATP];[Achieved Delivery Date])<0;0;DaysBetween([V_Max Last ATP];[Achieved Delivery Date])))

Achieved Shipment Date

=If([Transportation mode]="PICK UP" Or [Order Document Type Code]="KE";[V_Max Actual GI Date])

OTS CR

=If([Transportation mode]="PICK UP";If(DaysBetween([V_Max Last Req GI]; ";[V_Max Actual GI Date] )<0;0;DaysBetween([V_Max Last Req GI]; ";[V_Max Actual GI Date]

OTS FC

=iF([Transportation mode]="PICK UP";If(DaysBetween([V_Max 1st ATP GI]; ";[V_Max Actual GI Date] <0;0;DaysBetween([V_Max 1st ATP GI]; [V_Max Actual GI Date] )))

OTS LC

=If([Transportation mode]="PICK UP";If(DaysBetween([V_Max Last ATP GI]; ";[V_Max Actual GI Date] <0;0;DaysBetween([V_Max Last ATP GI]; ";[V_Max Actual GI Date]

DAYS DELAY NET (APPLY THE TOLERANCE)

TOLERANCES

AB";"AC";"AE";"AF => 7 days   (ocean)

AJ => 1 day  (LTL)

AK AH AI => 0  (FTL)

AU AP => 3          (Barge / Rail)

AV => 2       (multimodal)

“AL” “AW” “AX” => 0 999 days)     (Pick Up)

=If([OTD CR - Delay Days]=999;999;If([OTD CR - Delay Days]=0;0;If(Abs([OTD CR - Delay Days])<=[Tolerance Days];0;If([OTD CR - Delay Days]<0;[Tolerance Days]+[OTD CR - Delay Days];[OTD CR - Delay Days]-[Tolerance Days]))))

IN FULL

IN FULL REQUESTED – IFR

(Order line qty – Sum Delivery quantity) + tolerance that comes from the sales order

IN FULL CONFIRMED – IFC

(Sum Scheduled Confirmed Qty by sales order item  – Sum Delivery quantity) + tolerance that comes from the sales order

LEAD TIMES

7 lead-times have been defined by SCE (to be challenged for rationalisation)

Leadtime 1 : The Lead-time is the difference, in days, between the customer PO Date (order line level - VBKD-BSTDK), and the requested material availability date (Max in the scheduled line level).

=DaysBetween([V_Max Cust PO];[V_Max Req Material Availability])

Leadtime 2 : The Lead-time is the difference, in days, between the customer PO Date (VBKD-BSTDK), and the achieved GI date

=DaysBetween([V_Max Cust PO];[Max Actual GI Date])

Leadtime 3 : The Lead-time is the difference, in days, between the customer PO Date, and the requested delivery date.

=DaysBetween([V_Max Cust PO];[V_Max Last Req Delivery])

Leadtime 4 : The Lead-time is the difference, in days, between the customer PO Date, and the achieved delivery date

=DaysBetween([V_Max Cust PO];[Achieved Delivery Date])

Leadtime 5 : The Lead-time is the difference, in days, between the creation date and the last customer req Goods Issue date

Leadtime 6 : The Lead-time is the difference, in days, between the creation date and the schedule line 1st ATP (confirmed) GI date

Leadtime 7 : The Lead-time is the difference, in days, between the creation date and the actual GI date

 

QUERY FILTER

1 - Sales Documents In List

Below Rhodia’s codes, check Solvay

My proposition with PF1 is to use the same documents used for Order Book OBAS:

 

GCTA	Standard Order
KE	Consig issue
SB	SB Third-p.dir order
SO	SO Rush Order
TA	Standard Order
TAF	Standard Order (FPl)
TAM	Delivery Order
TAV	Standard Order (VMI)
TD	TD Standard Order
TDIV	TDIV Misc Op
TSA	Telesales
ZITI	Sales Order ITI BR
ZORB	Standard Order BR
ZOUT	Other Outputs BR
ZPVA	ZPVA Ord.immed.ship.
ZVEX	Fut. Dely Invoice BR
ZVEY	Fut. Dely Shipmnt BR

 

Also we do not have the NB types (purchase order) together in the same DSO because the data structure is different and we do now, with BW, a straight extraction!. Are you sure that is necessary measure the OTIF for the NB Orders? For information with Order Book, we have one for sales order and another for PO's.

2 – Flag Intra GBU = NO

3 – Reason for rejection: VBAP-ABGRU is null or equal 98

 

2- Rules for OTIF Calculation (Rhodia)

2.1 - In Full Calculation "In Full Request Order Quantity fits with Delivery Quantity In Full Confirmed Scheduled Line Confirmed Quantity fits with Delivery Quantity "

2.2 - On Time Calculation

2.2.1 - On Time Delivery "On Time Delivery: it means that the calculation is based on date of arrival, they compare an achieved arrival date with a target arrival date. Achieved Delivery Date:

For “PICK UP” transportation mode : Last transfer order confirmation date for the order line --> FIELD LTAP-QDATU else Last Good Issue date

For other transportation mode : The last “Actual Shipment end” if it exists else the last “ planned shipment end” if it exists else the last “Actual good Issue”

OTD CR: If Difference between Achieved delivery date and Order line requested delivery Date is lower than – Early tolerance then “EARLY” else if Difference between Achieved delivery date and Order line requested delivery Date is greater than Late tolerance then ""LATE"" else ""OTD""

OTD FC: If Difference between Achieved delivery date and Orderline 1st confirmed delivery Date is lower than – Early tolerance then “EARLY” else if Difference between Achieved delivery date and Order line 1st conf delivery Date is greater than Late tolerance then ""LATE"" else ""OTD""

OTD LC: If Difference between Achieved delivery date and Order line last confirmed delivery Date is lower than – Early tolerance then “EARLY” else if Difference between Achieved delivery date and Order line last conf delivery Date is greater than Late tolerance then ""LATE"" else ""OTD"" "

2.2.2 - On Time Shipment "On Time Shipment: it means that the calculation is based on date of good issue, they compare an achieved shipment date with a target shipment date (calculated in SAP from requested delivery date, 1confirmed delivery date and last confirmed delivery date). Achieved Shipment Date: For “PICK UP” transportation mode : Last transfer order confirmation date for the order line --> FIELD LTAP-QDATU else Last Good Issue date For other transportation mode : The last “Actual GI” date

OTS CR: If Difference between Achieved shipment date and Order line requested GI Date is lower than – Early tolerance then “EARLY” else if Difference between Achieved shipment date and Order line requested GI Date is greater than Late tolerance then ""LATE"" else OTS.

OTS FC: If Difference between Achieved shipment date and Order line 1st confirmed GI Date is lower than – Early tolerance then “EARLY” else if Difference between Achieved shipment date and Order line 1st Conf GI Date is greater than Late tolerance then ""LATE"" else ""OTS""

OTS LC: If Difference between Achieved shipment date and Order line last confirmed GI Date is lower than – Early tolerance then “EARLY” else if Difference between Achieved shipment date and Order line Last confirmed GI Date is greater than Late tolerance then ""LATE"" else ""OTS"""

3- Tolerances:

"Number of days based on Transportation Mode (Order Shipping Condition) OCEAN = 7 RAIL = 3 BARGE = 3 INTERMODAL = 2 ROAD = 1 AIR = 0 OTHER = 0 "

4 - Exceptions:

"Customer Pick Up: uses Last transfer order confirmation date in calculation Customer Pick Up Early = OTIF Order Non OTIF Reason code starting with 7 = OTIF "

5 - DOCUMENTS TYPES

 The document types to be considered are:

PS: NOT CONSIDER NB TYPE - CAUSE THEY ARE IN THE PO'S PART

Roles and access

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

NB: The role menu ZR_RCS_ALL_MENU "Role composite with all role Menu and perimeters" gives access to all role menu. When asking for a new role menu, do not forget it is added to that one.

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

Multi Provider MVSDSO50 - OTIF Global

Details

Cubes

CRSDSO10 - Solvay

CRSDSO50 - Rhodia

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

Main documentation:

Preparation

• Kickoff
• Project Quality Plan
• Design Validation

Design

• Functional Specification
• Map of Datasources
• Profiles Management
• Queries Definition
• Technical documentation

Deployment

• E Learning

Rule to get the GBU code

For Rhodia

Using distribution channel and division from material, get the IECRA code in master data C_CDSA

Using IECRA code, get the sub-activity in master data g_cwwe01 

For Solvay

using business area, get the technical business area in master data c_techba

The function above is used to fill zeros left

The code above takes the GBU code from master data sub-activity (g_cwwe01)

Rule to define picked good issue (PGI) If doc type is KE or incorterm is ERW or FCA

Calculation Rules

Rule to define other date types and ship end date variable according to the content of fields actual shipment, planned shipment of GI date

Rule to define the good issue date

Rule to redefine the good issue date when the order is a pickup

Rule to define the achieved delivery date and achieved shipment datte

Rule to determine In Full requested considering the lower and upper tolerance 

Rule to generate the in full requested detailed with terms light, heavy or yes 

Rule to determine In Full confirmed measure considering the lower and upper tolerance 

Rule to generate the in full confirmed detailed with terms light, heavy or yes

Rule to determine the differences between delivery quantity and requested quantity

Rule to generate on time delivery customer request (OTD CR)

Rule to Consider special NON OTIF reasons as OTIF

Rule  to generate group of delays for OTDCR

Rule to  generate OTDCR delay days

The same rules are applied for OTDFC, OTDLC, OTSCR, OTSFC, OTSLC just changing the date to be considered, see below:

For OTDFC uses the dates → v_dif_date = v_fstatp - v_shp_eact (difference between max first ATP and shipment end date)

For OTDFC uses the dates → v_dif_date = v_lstatp - v_shp_eact (difference between max last ATP and shipment end date)

Rule  to define the dates used in OTS CR (ON TIME SHIPMENT Cust request)

For OTSCR used the dates  → v_dif_date =  v_lstrqgi - v_gi_date (difference between max actual GI date and last req delivery)

For OTSFC used the dates  →  v_dif_date =  v_firstgi - v_gi_date(difference between max actual GI date and first ATP GI)

For OTSLC used the dates  →  v_dif_date =   v_lastgi - v_gi_date (difference between max actual GI date and last ATP GI)

Special rule to determine the delivery date for SP (Special Pelimerer)

Unit conversions

The Unit conversion is done in the Bex queries (Quantity Conversion Type), after user chooses the unit conversion on reports

Transactionnal data are stored in sales unit

• if base unit / po unit is managed in dimension MASS, conversion within table T006 works well

• if base unit / po unit is not managed in dimension MASS, conversions for KG/LB/TO have to be managed on ERP side (MM03 / Alternative Conversion).

There are many "special" units on Solvay ERP, ie: 1KG, 1TN, VKG, KRE, 

Important : 

Quantities are not converted when loading data, and are not store into cubes (The conversion is done when running the report)

So, if a conversion is missing for a material ask the material referent to 

1- manage the conversion on ERP Side by managed the material master (MM03) / unit of measure tab

2- the day after, when the DSO UOMCMAT2 is filled, the conversion is applied in the reports. It applies to whole data without any reloading.

The conversion type created by TIERS queries is QCT_MATNR4 (see below the setup for it).

BW Quantity Conversion

BW Updated Qty Unit by Material

Dependencies with other applications

• It shares extractors (2LIS_08TRTK, 2LIS_08TRTLP, 2LIS_08TRFKZ) with the legacy TR application for WP1 and PF1.
• It was created some master data in order to have the attributes shared with several applications: shipment doc, delivery doc and shipment cost item doc.
• Data is consumed by Qlikview dashboard. The loadings in QV are daily. It runs a fixed scheduled, the loadings must be finished when the next start.
• There is an integration with SPRINT (purchase schedule lines) to store there the service purchases (freight)
• There are links between TIERS and Value Stream and Industrial dashboard (both for Rhodia legacy)

• There is an integration with SPRINT to store the profit center (C_SUBACT2) for purchase services

Rhodia PO schedule lines (DB_PUSL1)

Solvay PO schedule lines (DB_PUSL2)

• There is an integration with Value Stream to store there the cost of transports

Rhodia CUB_VS (value stream)

• There is an integration with Industrial dashboard to store there the cost of transports

Rhodia CUB_DIPL (industrial dashboard)

Data loadings

Info providers and objects loaded

1 - Main Process Chain

This process chain is responsible to trigger and syncronize all TIERS processes chains for Solvay and Rhodia. It is scheduled to run every workday inside the Daily process chain (RSP_DAILY) after general master data PC, around 3AM (French time).

2 - Propagation Layer

 This process chain is responsible to run the loads between source system and first layer in BW.

3 - Business Layer

 This process chain is responsible to load the second and third layers from propagation layer.

4 - Master data

 This process chain is responsible to load all the master data attributes in BW.

5 - Text & maintenance

 This process chain is responsible to load all the master data texts in BW.

Loading frequency

Daily inside the daily process chain

Average performance & monitoring

Daily process chain is loaded in 2 hours with around 30k lines in DELTA mode for a total of 10M lines in cubes. 

See below an extract of 1 month of monitoring

For details about application load monitoring, click in following link: Application Monitoring

Record Keeping

User defined that 3 full years stored is enough to be maintained in BW.

There is no automatic process to remove historical data, so any request about historical deletion should be done by user using Freshdesk ticket.

Modeling

Info areas

Master Data

Propagation Layer

Business Layer

Reporting Layer

Virtual Layer

Multi provider

1 - MVSDTR01 - Shipm & Delivery & Cost & Cond & PO/Inv (Sol&Rho)

Data Flow

Dimensions

Key figures

2 - MVSDTR02 - TR Vir: Shipment Events (Solvay & Rhodia)

Data Flow

Dimensions

Key figures

Main functionalities

• E Learning - user guide
• Leaflet Impression
• Q&A Synthesis
• WeLearn Course Description

Main datasources based on SAP tables

• 2lis_08TRTK    – Shipment Header

• 2lis_08TRTLP  – Shipment & Deliveries

• 2lis_08TRFKZ  – Shipment Costs
• DTS_TRKONV   – Shipment Conditions
• DTS_TRFI_ESSR – Invoices & Purchases History
• DTS_TRFI_VFKP – Invoices & Purchases Issues

Main dimensions for the data

• C_POINT2   –  Connection Point (it can be a customer, vendor, plant, node point or shipping point)

• C_3RPARTY  –  3rd Party (Cust / Vend) - it cab be a vendor or a customer

• C_ROUTE     –  Route
• C_SHIPNU2   –  Shipment Number
• C_SHCSTI2   –  Shipment Cost item
• C_DELITM2   –  Delivery Item
• C_CNDTYP2  – Condition Type
• C_SHPTP_2   –  Shipping Type
• C_SHPMTP2   – Shipment Type
• C_SHCITTY   –  Item category shipment costs 
• C_VTADD01   –  Ship. Shipping Line 
• C_SHIPCD2   –  Shipping conditions
• 0TRZONE    –  Transportation Zone 
• C_TRPPNT2   –  Transportation planning point 
• C_VSTGA   –  Event Reason f.deviation
• C_EVTTYP   –  Event Type

Reporting

Calculated & Restricted key figures

Main queries

1 - Shipment cost details analysis

• Technical Name: BW_QRY_MVSDTR01_0010
• Propose a detailed analysis of shipment cost accruals, detailled by materials. Make shipment costs accruals understandable.
• Allows benchmarking between materials, between customers.
• Gives some possibilities of drill down to get details on a macro figure.
• Public: sites log managers / GBU log process teams, base for Qlikview reportings

Configuration

3 - Shipment status and events 

Broadcast

• None

Maintenance

Known bugs

• Shipment conditions deleted in ERP (WP1 & PF1) sometimes are not deleted in BW, because the extractor is generic based on a view, so the delta is not possible because several lines can be changed at the same time. When the shipment cost document is deleted in ERP, in this case the shipment condition is deleted in BW.
• The shipment condition value is not correctly linked with currency when these is more than 1 currency. The extractor DTS_TRKONV extracts data from ERP table KONV and the currency is not aligned correctly in this table. Solution proposed is to take the currency from shipment cost, that is aligned with shipment condition value.
• Sometimes the unit conversion is not done in query due the unit is not mapped correctly  in table UOMCMAT2 by material.

Recurring procedure

• When the process chain fails, the process type must be analyzed and repeated.
• Sometimes a full load by period is required by user in order to syncronize all transportation data.

Planned Evolution

• Most part of enhancements in standard extractors should be removed in future in order to obtain a better performance.
• The old transportation flow (CUB_TR001, CUB_TR002, CUB_TR005, CUB_TR006, CUB_TR007, CUB_TR008) will be deleted and removed from schedule in 2016.
• Some broadcasts existed in old transportation flow should be developed in new TIERS.

Useful transactions to be used for checking data

https://wiki.solvay.com/display/TECHREP/BW+Authorizations?src=contextnavpagetreemode