Document Links

ERP-1041 - Getting issue details... STATUS  

Introduction

This Data Flow Specification (DFS) defines the end-to-end data flow required to meet the following requirements:

• Total stock balances (value and quantity) by Posting Date for every Material/Plant/Storage Location/Stock Type.
• Freight KPI scores (Freight Unit Compliance, FSD Timing, Freight Agreement Usage, Overall Compliance) tracked weekly and by business entity.
• Freight Cost analysis – breakdown of transportation charges by type, carrier, company code, purchasing organisation, plant, document and posting status.
• Carrier Performance – confirmed pick-up vs. actual, confirmed ETA vs. actual arrival, Proof-of-Delivery rate, and event-reporting rate.
• Freight Cost Allocation – breakdown of settled freight costs by company code, sales/purchase organisation, plant, order, material and G/L account.

Stock Balances

The objective of the Stock Balances Analytical Model is to provide a comprehensive view of Material/Plant/Storage Location/Stock Type values and quantities at a given date, which reconciles to MB52.

Quantities and values are obtained from a combination of Goods Movement (MM) documents and Fi adjustment postings. 

Snapshots of monthly and weekly inventories will be created and partitioned (how so it makes sense?)

A persisted/portioned flow, containing aggregated data will also be created. This aggregated view will carry the stock balances for a month by Material/Plant/Storage Location/Stock Type. The current month stock movements will be union-ed to it to get to the current month ending balance.

EWM Managed Plants:

No differentiation in the handling of EWM managed plants is required. EWM handles the process, but each movement is reflected as a GoodsMovementDocument in table MSEG. This allows for a single process to calculate Closing Inventory.

The standard CDS views for physical stock and plant stock for EWM managed plants will be extracted and made available for reporting (if required for cross-instance reporting).

Physical Inventory

Physical inventory adjustments are processed in SAP as material documents with movement types 701/702 (inventory difference posting). These postings are captured in MSEG and will flow through the standard I_GoodsMovementDocument CDS view. No separate extraction path is required; physical inventory adjustments are included in the GoodsMovementDocumentDEX flow and will be unioned with the period opening balance to produce the closing balance.

Source System Extractors

`

Inbound Layer

Extension of the cds views SCTMS_D required to include the expected extensions mentioned in ERP-1792 'Manual updated charges vs Freight Agreement calculated amount'

TMS KPI to source mapping

Harmonisation Layer

2TL_S2HARM_GoodsMovementDocumentDEX

Purpose:

• Delta load of Material Documents
• Set keys
• Filled by Transformation Flow 2TF_S4HARM_I_GoodsMovementDocumentDEX

2TF_S4HARM_I_GoodsMovementDocumentDEX

Source: 1TL_S4Hx_I_GOODSMOVEMENTDOCUMENTDEX

Projection:

Note, keep keys MaterialDocumentKey1 - 6 for Delta Capture.

2VR_S2HARM_GoodsMovementDocumentDEX

Projection:

2TL_S4HARM_C_TranspOrdItemAnlytsSDEX

Source: 1TL_S4Hx_I_GOODSMOVEMENTDOCUMENTDEX

Purpose:

• Delta load of Transportation Order Items
• Filled by Transformation Flow 2TF_S4HARM_C_TranspOrdItemAnlytsSDEX

2TF_S4HARM_C_TranspOrdItemAnlytsSDEX

Source: 1TL_S4HR_C_TranspOrdItemAnlytsSDEX

• No transformations

2VR_S4HARM_C_TranspOrdItemAnlytsSDEX

Filter:

Formula:

2TF_S4HARM_I_TranspOrdStageAnalytics

Source: 1TL_S4Hx_I_TRANSPORDSTAGEANALYTICS

• No transformations

2TL_S4HARM_I_TranspOrdStageAnalytics

Purpose:

• Delta load of Transportation Order Items
• Filled by Transformation Flow 2TF_S4HARM_I_TranspOrdStageAnalytics

2VR_S4HARM_I_TranspOrdStageAnalytics

Filter:

Projection: Exclude ALL, except: 

Formula:

Aggregation

2TL_S4HARM_I_TranspChargesRoot  (2TL_TransChRoot)

Purpose:

• Delta load of Freight Order Charge Root records (/SCMTMS/D_TCHRGR)
• Filled by Transformation Flow 2TF_S4HARM_I_TranspChargesRoot

2TF_S4HARM_I_TranspChargesRoot

Source: 1TL_S4Hx_I_TranspChargesRoot  (1TL_TransChRoot)

• No transformations

2VR_S4HARM_I_TranspChargesRoot  (2VR_TransChRoot)

Filter:

CALC_STATUS <> ''

Formula:

2TL_S4HARM_I_TranspChargesItem  (2TL_TransChitem)

Purpose:

• Delta load of Freight Order Charge Item records (/SCMTMS/D_TCHRGI)
• Filled by Transformation Flow 2TF_S4HARM_I_TranspChargesItem

2TF_S4HARM_I_TranspChargesItem

Source: 1TL_S4Hx_I_TranspChargesItem  (1TL_TransChitem)

• No transformations

2VR_S4HARM_I_TranspChargesItem  (2VR_TransChitem)

Filter:

HOST_KEY IS NOT NULL

Formula:

2TL_S4HARM_I_TranspChargesItemElement  (2TL_TransChItemE)

Purpose:

• Delta load of Freight Order Charge Element records (/SCMTMS/D_TCHRGE)
• Filled by Transformation Flow 2TF_S4HARM_I_TranspChargesItemElement

2TF_S4HARM_I_TranspChargesItemElement

Source: 1TL_S4Hx_I_TranspChargesItemElement  (1TL_TransChItemE)

• No transformations

2VR_S4HARM_I_TranspChargesItemElement  (2VR_TransChItemE)

Filter:

ROOT_KEY IS NOT NULL AND INVOICING <> ''

Formula:

2TL_S4HARM_I_TranspExecData  (2TL_TransExec)

Purpose:

• Delta load of Transportation Order Execution events (/SCMTMS/D_TOREXE)
• Provides event codes, actual dates/times, and discrepancy indicators for carrier performance KPIs (ERP-1782)
• Filled by Transformation Flow 2TF_S4HARM_I_TranspExecData

2TF_S4HARM_I_TranspExecData

Source: 1TL_S4Hx_I_TranspExecData  (1TL_TransExec)

• No transformations

2VR_S4HARM_I_TranspExecData  (2VR_TransExec)

Filter:

EVENT_REVOKED <> 'X' AND EVENT_STATUS <> ''

Formula:

2TL_S4HARM_I_TranspRootNode  (2TL_TransRootN)

Purpose:

• Delta load of Freight Settlement Documents (/SCMTMS/D_SF_ROT)
• Used to determine FSD creation timing relative to actual/planned departure for FSD Timing Compliance KPI (ERP-1795)
• Provides settlement document header data for joining with Customer Settlement (ERP-2359)
• Filled by Transformation Flow 2TF_S4HARM_I_TranspRootNode

2TF_S4HARM_I_TranspRootNode

Source: 1TL_S4Hx_I_TranspRootNode  (1TL_TransRootN)

• No transformations

2VR_S4HARM_I_TranspRootNode  (2VR_TransRootN)

Filter:

SFIR_TYPE <> '' AND LIFECYCLE <> 'X'

Formula:

2TL_S4HARM_C_CustSettlmtDEX  (2TL_CustSettlmt)

Purpose:

• Delta load of Customer/Freight Settlement documents (C_CustSettlmtDEX – sourced from WBRP/WBRK)
• Provides settlement item data required for Freight Cost Allocation report (ERP-2359)
• Filled by Transformation Flow 2TF_S4HARM_C_CustSettlmtDEX

2TF_S4HARM_C_CustSettlmtDEX

Source: 1TL_S4Hx_C_CustSettlmtDEX  (1TL_CustSettlmt)

• No transformations

2VR_S4HARM_C_CustSettlmtDEX  (2VR_CustSettlmt)

Filter:

CustSettlmt IS NOT NULL

Projection:

Propagation Layer

3VR_P2FINC_MaterialStock

Source: 2VR_S2HARM_GoodsMovementDocumentDEX & 2VR_S4HARM_I_GLAccountLineItemRAW

Filters:

Projection:

• 2VR_S4HARM_I_GLAccountLineItemRAW
  • Only for join
• 2VR_S4HARM_I_GoodsMovementDocumentDEX
  • Only for join

Union:

Accept default union mappings

3VF_P2FINV_MaterialStock

Source: 3VR_P2FINC_MaterialStock

Calculation:

3VR_P2FTMS_TransportationOrderSql  (3VR_TranOrdSql)

Source: 2VR_S4HARM_C_TranspOrdItemAnlytsDEX (2VR_TrItemDex)

Purpose:

• SQL-level aggregation and pre-join of Transportation Order items with Charges Root, Item, and Element data.
• Receives 2VR_S4HARM_I_TranspChargesRoot (2VR_TransChRoot), 2VR_S4HARM_I_TranspChargesItem (2VR_TransChitem), 2VR_S4HARM_I_TranspChargesItemElement (2VR_TransChItemE), and 2VR_S4HARM_I_TranspExecData (2VR_TransExec) via join.
• Filters and projects fields required downstream for 3VR_P2FTMS_TransportationOrder and the KPI analytical models.

3VR_P2FTMS_TransportationOrder  (3VR_TranOrd)

Source: 3VR_P2FTMS_TransportationOrderSql (3VR_TranOrdSql) left join 2VR_S4HARM_C_TranspOrdStageAnalytics (2VR_TrStg)

Join Condition:

TransportationOrderSql.TransportationOrderUUID = TrStg.TransportationOrderUUID

Filter:

TransportationOrder <> '' AND TranspOrdLifeCycleStatus NOT IN ('X', 'Z')

Projection – include:

3VF_P2FTMS_TransportationOrder  (3VF_TranOrd)

Source: 3VR_P2FTMS_TransportationOrder  (3VR_TranOrd)

• Formula / Calculation view applying any final field-level derivations and unit conversions before exposure to the analytical model.

3VR_P2FTMS_FrtCstAlloc  (3VR_FrtCstAlloc)

Source: 2VR_S4HARM_I_TranspRootNode (2VR_TransRootN)  JOIN  2VR_S4HARM_C_CustSettlmtDEX (2VR_CustSettlmt)  JOIN  2VR_S4HARM_I_GLAccountLineItemRAW (2VR_GL_Acc_Line_Itm)

Join Condition (ERP-2359 specification):

G.CompanyCode = S.CompanyCode AND G.FiscalYear = S.SettlementFiscalYear AND G.ReferenceDocument = S.CustSettlmt AND G.ReferenceDocumentItem = S.CustSettlmtItem AND G.ReferenceDocumentContext = 'WB' AND N.REF_KEY = S.SettlmtRefDoc (link FSD to Settlement Doc)

Filter:

ReferenceDocumentContext = 'WB' AND S.CustSettlmt IS NOT NULL AND N.LIFECYCLE <> 'X'

Projection – from Customer Settlement (2VR_CustSettlmt):

Projection – from GL Account Line Item (2VR_GL_Acc_Line_Itm):

Projection – from TranspRootNode (2VR_TransRootN):

3VF_P2FTMS_FrtCstAlloc  (3VF_FrtCstAlloc)

Source: 3VR_P2FTMS_FrtCstAlloc  (3VR_FrtCstAlloc)

• Formula / Calculation view. Applies any final derivations (e.g. account type classification Cost vs. Material) before exposure to the analytical model.

Reporting Layer

4MA_P2FINV_DowntimeFact

Supports:

Includes technical details for:

Calculated Measures (Post Aggregation Calculations / exception aggregation etc)

4MA_P2FTMS_TransportationOrder  (4MA_TranOrd)

Source: 3VF_P2FTMS_TransportationOrder  (3VF_TranOrd)

• Supports: Transportation order and freight charge analysis.
• This model provides the base analytical structure from which the Freight KPI Report (ERP-1795), Freight Cost Report (ERP-1792) and Carrier Performance Report (ERP-1782) SAC stories will be built.

Calculated Measures:

Detailed calculated measure specifications for 4MA_P2FTMS_TransportationOrder are pending story design for ERP-1795, ERP-1792, and ERP-1782. 

xxxxxxxxxxxxxx – Freight KPI Report  (ERP-1795)

Intended KPI measures (draft – subject to model naming decision):

xxxxxxxxxxxxx– Freight Cost Report  (ERP-1792)

Intended measures (draft):

[Not yet defined] – Carrier Performance Report  (ERP-1782)

The analytical model for the Carrier Performance Report has not yet been assigned a name in the data flow diagram. This section is reserved once the model name is confirmed.

Intended measures (draft):

Note: Event code mappings (pick-up, arrival, POD) must be configured as input parameters or a reference table in DSP, as they are client-specific customising values in /SCMTMS/D_TOREXE.

4MA_P2FTMS_FrtCstAlloc  (4MA_FrtCstAlloc)

Source: 3VF_P2FTMS_FrtCstAlloc  (3VF_FrtCstAlloc)

• Supports: ERP-2359 – Freight Cost Allocation Report.

Calculated Measures:

Dimensions:

• Company Code, Purchasing Organisation, Sales Organisation (where populated)
• Plant, Material, Sales Order / Purchase Order
• G/L Account, Cost Center, Controlling Area, Business Area
• Period (ActualSettlmtDate)

Out of Scope:

Breakdown by charge type or carrier – refer to Freight Cost Report (ERP-1792) for that analysis.

Restricted Measures

tbd

Currency Conversions

tbd

Variables

Data access controls

Outbound Layer