Document Links
| Jira | ||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Introduction
This Data Flow Specification (DFS) defines the end-to-end data flow required to meet the following requirements:
| Sub area | Data flowDriver | Purpose | Notes | Jira | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Financial Ledger Reporting | ACDOCA | Replicating business content for GLAccountLineItemRawData to create Semantic tags model that is used for financial reporting. It mirrors the CDS views used in S/4 but creates the semantic tag model in a more logical fashion that than in S/4 (removes the hard coding in the CDS views). This model provides all journal entry details. Organisational entities such as Company Code, Profit Centre, Cost Centre, Business Area, Functional Area, Financial dimensions like Ledger, G/L Account, Segment, Financial Document Type, Posting Key, Debit/Credit Code, are available in this data model. The model provides a source ledger as well as all the ledgers that apply to the relevant company codes. This model maps the journal entries to the semantic tags defined based on G/L Accounts. The semantic tags based on functional areas are not considered in this model. Differs in some way to the Functional Area Semantic Tag model Potentially need to create another AM to view different xxxx I_JournalEntryItem - this separates AMOUNT by Dr/Cr - suggested for data flows (and suggesting using BALANCE from acdoca) | Incld Headcount, Plan | Tax | Incld Headcount, Plan |
| ||||||||||||||||
| Net Working Capital | BSEG | The Operation Accounting Document item view provides details of an | Net Working Capital | BSEG | The Operation Accounting Document item view provides details of an operational accounting document item (sourced from BSEG) like clearing document details, payment data, and tax data.The The long-term vision is that table BSEG will be used only for open item management management. Used as the foundation of accounts receivable and accounts payable analysis, such as total payables and receivables, overdue receivables and payables, and future receivables. Generates a balance and movements model (else look at stock, treasury and Group Reporting for inspiration) There is a model that should cover the requirement for the 3 XREF fields not in ACDOCA .
| Leading ledger only | ||||||||||||||||
| Fixed Assets | Assets | Ability to show Original cost, depreciation and net book value
| There is no business content for this |
| ||||||||||||||||||
| Statistical Statistical Key Figures | SKF | SKF will be calculated in SAC and retracted to both S/4 systems based on company code. SKF can be amended in S/4 and need to be extracted back into DSP.
| There is no business content for this |
| ||||||||||||||||||
| Group Reporting | ACDOCU | There is new improved business content for matrix elimination in group reporting that we will use. Significantly it is now periodic rather than year to date. Cash flow is difficult to replicate into DSP and not included in the business content. As all the data is in a single system, the starting point will be to use SAC LIVE reporting on S/4. There are currently around 30 schedules required. They run quarterly, projecting the 3rd Quarter forwards to predict the year end picture. | Group Reporting | ACDOCU | Std model cash flow loads of schedules - does it make sense to add a planning model for data capture? eg goodwill breakdown I dont think it does the YTD model anymore They run quarterly. projecting the 3rd Q forwards to predict the end picture Reconciliation to Finance GL:
| Planning is not included in this model - need to add if required | ||||||||||||||||
| Restructuring Provisions | Plan | The workforce is analysed to consider any possible restructuring and create financial provisions.
|
| |||||||||||||||||||
| HSE Provisions | Plan | data Data is required to cater for for planning every quarter (2 forms in total, one for qualitative and one for quantitative analysis and inputs). |
| |||||||||||||||||||
| Insurance | Plan | Insurance is provided for the group centrally. Each site needs to provide a valuation for assets (including stock) and potential loss of income. |
| Insurance | Plan | Data regarding assets is required to cater for insurance planning every year | ||||||||||||||||
| Tax | Tax | Ability to plan tax changes and create journals to be posted in S/4 as provisions. |
| |||||||||||||||||||
| Stock valuation | Stock | Stock values including future prices. Join MATDOC to MBEW to ensure that you get a | Stock valuation | Stock | Stock values including future prices. Ensure you get value even if no stock on hand. Look at C_StockQtyCurrentValue_3 for inspiration | Balances | There is a CDS called C_GLAccountBalance_F0707A This is implemented via a class Predessor was probably C_CN_GLAccountBalanceSumQuery but this also uses a class DSP approach in NWC appears the best bet |
Vision:
The vision is to be able to run a sales report showing Net Sales from GR with a drill down to S/4
Concern is that any drill down will go to ACDOCU at best?
In a perfect world, we would create a KPI once and share it, to ensure consistency.
If a KPI is only calculated in the Analytical Model, it will not be shared. Reasons for defining at this level are:
- Performance on aggregated data is better
- Some KPI's must be calculated pre aggregation eg price x quantity
Can remove the measures for alternate currency /quantity as we will not use these as per KDD047 Universal parallel Ledgers. But then how does this fit in with currency roles?
Reporting is done in tons so store rather than calc. Where if we dont persist again
Probably the same for value at bud rate. It has been decided that this will not be persisted but rather calculated.
Commentary will be added per model. This is not ideal as there is no leverage.
Missing from diagram: depreciation, mbew, kinaxis LT plan
...
| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Source System Extractors
...
S/4 x
...
ACDOCA
...
I_GLACCOUNTLINEITEMRAWDATA
...
View does not implement the logic for extension ledgers (just basis ledgers)
Includes Balances Brought Forward (000)
...
I_FXDASTSTATISTICALLINEITEM
...
Statistical Depreciation areas
This will need to be exposed for extraction. Replicating the logic used in S/4 I_AssetDepreciationBalanceCube
Hopefully enabling valuation on Leased Assets
Restrict to VORGN <> AFAB, MOVCAT <> 05, SLALITTYPE not between 07203 and 07209 (check CDS for exact)
Consider need for I_AccountingPrincipleAssgmt
...
| There is no business content for this |
Gaps
| Item | Detail | Status |
|---|---|---|
| Planning | Planning for maintenance and production orders may be in ACDOCP. These are not R2R requirements | Open |
| Missing requirement | Stock valuation requirements. | Open |
| Headcount data | Headcount is shown but as a ghost as not in scope at present. Assuming will be covered in B2F where the seed data should be actuals. | Open |
| Kinaxis integration | Kinaxis Long Term plan has not been designed as expected to be covered in the B2F POD. | Open |
| Bridges | Opening, closing balance and difference. Assume this logic is possible in models based on 3VR_OpsItemTS. There is a CDS called C_GLAccountBalance_F0707A This is implemented via a class (predecessor was C_CN_GLAccountBalanceSumQuery). | Open |
| Functional Area Semantic Tag | No requirement as yet for this model. It is not included in SAP business content. The model is very similar to the Profit Centre model. | Open |
| Cashflow in GR | As this is a difficult area to recreate, it is envisaged that a plausible solution would be to run this using a LIVE model in SAC without DSP. | Open |
| Group Reporting data incomplete | Planning functionality may be required for parameters and data not available in S/4. | Open |
| Drill through in GR | Currently we do not believe this is plausible as data is captured from China using the data collector. | Open |
| Reuse of KPI | In a perfect world, we would create a KPI once and share it, to ensure consistency. If we stack models this will work, but this is probably not feasible in all cases If a KPI is only calculated in the Analytical Model, it will not be shared. Reasons for defining at this level are:
| Open |
Commentary | Commentary will be added per model. This is not ideal as there is no leverage. | Open |
Leases | Real Estate is being used for Leases but there are no requirements as yet outside of what is included within ACDOCA. | Open |
| Actual Cash Flow | A very restricted set of dimensions, consider expanding this based on business needs (eg customer and vendor) | Open |
| Fixed Assets |
| Open |
Data Flow Diagram
| draw.io Diagram | ||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
| Table of Contents | ||||||||
|---|---|---|---|---|---|---|---|---|
|
Orange font implies a change from SAP standard business content
Source System Extractors
| System | Code | Extractor Name | Purpose | Delta | Frequency | Jira Ref | Extended fields |
|---|---|---|---|---|---|---|---|
S/4 x | ACDOCA | I_GLACCOUNTLINEITEMRAWDATA | View does not implement the logic for extension ledgers (just basis ledgers). This is implemented in 2VR_GLAccItmV1 Includes Balances Brought Forward (000) | Y | Continuous | Yes | |
| S/4 x | BSEG | I_OPERATIONALACCTGDOCITEM | Open Item management | Continuous | |||
| S/4 x | FQMFLOWA | I_CASHLIQUIDITYACTUALFLOW | Cash liquidity actual flow for working capital inventory. | Y | Continuous | ||
| S/4 x | FQMFLOWF | I_CASHLIQUIDITYFORECASTFLOW | Same source as actual flows | Y | Continuous | ||
| S/4 x | DEPR | I_FXDASTSTATISTICALLINEITEM | Statistical Depreciation areas - This will need to be exposed for extraction. Replicating the logic used in S/4 I_AssetDepreciationBalanceCube Restrict to VORGN <> AFAB, MOVCAT <> 05, SLALITTYPE not between 07203 and 07209 (check CDS for exact) | TBC | |||
| S/4 x | FINSSKF | I_FINSTATISTICALKEYFIGITEMBSC | Statistical Key Figures | Y | |||
| S/4 | ACDOCU | I_CNSLDTNGROUPJRNLENTRITMDEX | Matrix consolidation | Y | Continuous | Potentially |
...
Inbound Layer
- No inbound field adjustments are applied. Standard technical fields (load date/time, source system) are retained as delivered.
- Persist data as found in the source system CDS view. This should be including any extensions.
- Any data sourced from both S/4 environments, will be created using delta capture and re-persisted in Harmonisation layer. Assumption is that this data can be in cold storage if available.
- In the Business Content, some calculated fields are populated with Null values to make the model compatible with SAP S/4HANA release 2020. These fields will now be added in the 1TL object and mapped, removing the NULL calculation.
| Code | Tech Name | Logic | Partitioning |
|---|---|---|---|
| 1TL_ACDOCA | 1TL_S4Hx_I_GLACCOUNTLINEITEMRAWDATA | Retain all 402 fields even though SAP Business Content has 60 fields less. As the BCT is based on SAP 2023 | Fiscal Year |
| 1TL_BSEG | 1TL_S4Hx_I_OPERATIONALACCTGDOCITEM | Adds flag for cleared, based on date | |
| 1TL_CashA | 1TL_S4Hx_I_CASHLIQUIDITYACTUALFLOW | Cash Actual Flow Lower levels of the CDS have more dimensions (see gaps) | |
| 1TL_CashF | 1TL_S4Hx_I_CASHLIQUIDITYFORECASTFLOW | Cash Forecast Flow | |
| 1TL_Depr | 1TL_S4Hx_I_FXDASTSTATISTICALLINEITEM | Depreciation Areas | |
| 1TL_SKF | 1TL_S4Hx_I_FINSTATISTICALKEYFIGITEMBSC | Statistical Key Figures | |
| 1TL_ConsolDEX | 1TL_S4Hx_I_CNSLDTNGROUPJRNLENTRITMDEX | Consolidation |
...
Harmonisation Layer
- Both transaction data or master data that is not tier
...
I_CNSLDTNGROUPJRNLENTRITMDEX
...
Inbound Layer
No inbound field adjustments are applied. Standard technical fields (load date/time, source system) are retained as delivered.
Persist data as found in the source system CDS view. This should be including any extensions.
Any data sourced from both S/4 environments, will be created using delta capture and re-persisted in Harmonisation layer. Assumption is that this data can be in cold storage if available.
In the Business Content, some calculated fields are populated with Null values to make the model compatible with SAP S/4HANA release 2020. These fields will now be added in the 1TL object and mapped, removing the NULL calculation.
...
1TL_S4HR_I_GLAccountLineItemRawData
...
Retain all 402 fields even though SAP Business Content has 60 fields less. As the BCT is based on SAP 2023, I assume that these are new fields
...
Source Ledger = 0L, CO, LG, LT, TX (will always be specified)
Fiscal Year = I guess can be specified
...
1TL_S4HR_I_OperationalAcctgDocItem
...
Adds flag for cleared, based on date
...
1TL_S4HR_I_CashLiquidityActualFlow
...
Lower levels of the CDS have more dimensions, why are they excluded? cust, vend, PC, WBS, material
...
1TL_S4HR_I_CashLiquidityForecastFlow
...
1TL_S4HR_I_FxdAstStatisticalLineItem
...
I_CnsldtnGroupJrnlEntrItmDex
CDS View I_SemTagGLACcount from SAP S/4HANA provides the mapping between the G/L accounts and semantic tags. It is the key component of this model.
This CDS view does not have any key fields. The replication of data without key fields from CDS Views is not supported by the Replication Flow at moment. Instead the local table SAP_FI_IL_I_SemTagGLAccount is used in this model to support both SAP S/4HANA Cloud and SAP S/4HANA source systems. The inbound layer view SAP_FI_IL_SemTagGLAccount is based on this table.
Customer should perform the following activities after importing the content package:
Create a remote table from CDS View I_SemTagGLAccount of the connected SAP S/4HANA system and replicate the data.
Create a dataflow to populate the local table SAP_FI_IL_I_SemTagGLAccount from the remote table. (For SAP S/4HANA Cloud sources a conversion from Date to String Datatype is needed in the dataflow.)
or
Replace the local table SAP_FI_IL_I_SemTagGLAccount with the remote table in the inbound layer view SAP_FI_IL_ SemTagGLAccount.
The Inbound layer view G/L Account with Semantic Target (IL) (SAP_FI_IL_ SemTagGLAccount) is based on the local table SAP_FI_IL_I_SemTagGLAccount and has some simple data type conversions like date conversion.
Harmonisation Layer
...
- 1 will require a union between environments and re-persisted. This is to improve performance.
- As soon as a local table is joined with another dataset, the reference is lost and the "I" from
...
- the extractor can be dropped
...
- .
- While it is very tempting to remove fields that are not required by Syensqo, we will keep them in the models. As we are converting the SQL views to Graphical views, the optimiser will exclude them. Examples here being industry solution specific such as Public Sector. However, SAP do remove deprecated fields such as Material which is now replaced with Product.
2TL_ACDOCA - 2TL_S4HARM_I_GLAccountLineItemRawData
Purpose:
Persist after union the 2 datasources into a single table for performance to avoid the runtime union.
Source / Union:
1TL_S4HR_I_GLAccountLineItemRawDataGLACCOUNTLINEITEMRAWDATA
1TL_S4HC_I_GLAccountLineItemRawDataGLACCOUNTLINEITEMRAWDATA
Filter:
Potentially consider (purely for performance)
- remove additional amount/quantity fields as we cannot use these (wanting to persist in this state to improve performance)
Formula:
Potentially consider (purely for performance)
- perform any calculations likes tonnes conversion storing the results
- conversion to budget group currency (appears they want to convert the other way around, comparing to prior year)
Key:
Key:
Ledger, CompanyCode, Ledger, CompanyCode, SourceLedger
Partitions:
Ledger, FiscalYear
Comments:
Set as Delta Capture
2VR_GLAccItm - 2VR_
...
S4HARM_GLAccountLineItem
Purpose:
Casting dates and add the statistical derivations from the next level
Source:
2TL_HARMS4HARM_I_GLAccountLineItemRawData
...
Formula:
Casting TO_DATE (PostingDate, DocumentDate, CreationDate, ServicesRenderedDate, PerformancePeriodStartDate, PerformancePeriodEndDate, ExchangeRateDate, ClearingDate, ValueDate, LastChangeDate).
add Add the statistical derivations here, rather than repeat in the level above
...
Changing name to replicate the corresponding CDS in S/4.
2VR_LeadLed - 2VR_
...
S4HARM_
...
GLAccountLineItemLeadingLedger
Purpose:
...
Restrict to Leading Ledger.
This should be the object that is shared with other PODs for reporting.
Source:
I2VR_S4HARM_LedgerCompanyCodeCrcyRoles GLAccountLineItem
I_LedgerSourceLedger_FI_Ledger
Filter:
FI_Ledger - IsLeadingLedger = 'X'
Projection:
FI_Ledger - Ledger
Join:
[Many.....Many1] 2VR_HARM_CoCodeLedgerSourceLedger as 2VR_HARM_LedgerCompanyCodeCrcyRoles LEFT JOIN 2VR_HARM__LedgerSourceLedger on Ledger S4HARM_GLAccountLineItem V1 INNER JOIN I_FI_Ledger on SourceLedger = Ledger
Projection:
Remove repeated Ledger
...
Formula:
Derivations applied at lower level, so not required here
2VR_
...
S4HARM_CoCodeLedgerSourceLedger
Purpose:
To provide all the ledgers available to a company codeThis has the effect of exploding the rows to ensure that all ledgers are created for every source ledgercurrency roles and Company Code Assignment of Ledgers.
Source:
2VR I_HARM_GLAccountLineItemLedgerCompanyCodeCrcyRoles
2VR_HARM_CoCodeLedgerSourceLedger
...
I_LedgerSourceLedger
Join:
[Many.....Many]2VR_S4HARM_CoCodeLedgerSourceLedger as 2VR_HARMS4HARM_GLAccountLineItem INNER JOIN I_LedgerCompanyCodeCrcyRoles on SourceLedger = SourceLedger, CompanyCode = CompanyCode
Formula:
Comments:
SAP incorrectly defined as semantic type FACT
2VR_LeadLed - 2VR_HARM_GLAccountLineItemLeadingLedger
Purpose:
...
LedgerCompanyCodeCrcyRoles LEFT JOIN 2VR_S4HARM__LedgerSourceLedger on Ledger = Ledger
Projection:
Remove repeated Ledger
Comments:
Not shown in diagram (hence no alias) as this is master data
2VR_GLAccItmV1 - 2VR_S4HARM_GLAccountLineItem V1
Purpose:
To provide all the ledgers available to a company code.
This has the effect of exploding the rows to ensure that all ledgers are created for every source ledger.
Source:
2VR_HARMS4HARM_GLAccountLineItem V1
I2VR_FIS4HARM_Ledger
Filter:
FI_Ledger - IsLeadingLedger = 'X'
Projection:
FI_Ledger - Ledger
CoCodeLedgerSourceLedger
Join:
[Many.....1Many] 2VR_HARMS4HARM_GLAccountLineItem V1 INNER JOINI_FI_LedgerJOIN I_LedgerCompanyCodeCrcyRoles on SourceLedger = Ledger
Formula:
Derivations applied at lower level, so not required here
Comments:
I do not understand why I apply this after adding all ledgers in the previous layer
I would suggest join should be ledger to account line item
SourceLedger, CompanyCode = CompanyCode
Comments:
SAP incorrectly defined as semantic type FACT
2TL_BSEG - 2TL_
...
S4HARM_I_OperationalAcctgDocItem
Purpose:
Persist after union for performance the 2 datasources into a single table for performance to avoid the runtime union.
Source / Union:
1TL_S4HR_I_OperationalAcctgDocItem
1TL_S4HC_I_OperationalAcctgDocItem
Filter:
...
Key:
CompanyCode, AccountingDocument, FiscalYear, AccountingDocumentItem
Partitions:
FiscalYear
Comments:
Set as Delta Capture
2VR_OpsItm - 2VR_
...
S4HARM_I_OperationalAcctgDocItem
Purpose:
Casting dates and adding cleared flag
Source:
2TL_HARMS4HARM_I_OperationalAcctgDocItem
...
These are related to deprecation of fields and the purpose of th
...
the relevance to the solution.
Formula:
Casting TO_DATE (ClearingDate, ClearingCreationDate, ValueDate, DueCalculationBaseDate, LastDunningDate, TaxDeterminationDate, PostingDate, DocumentDate, NetDueDate, CashDiscount1DueDate, CashDiscount2DueDate)
Adding IsCleared - case when ClearingDate = '00000000' then '' else 'X' end
Comments:
Note that the fields set to null in BCT are available to be mapped in this release.
2TL_CashA - 2TL_
...
S4HARM_I_CashLiquidityActualFlow
Purpose:
Persist after union the 2 datasources into a single table for performance to avoid the runtime union.
Source / Union:
1TL_S4HR_I_CashLiquidityActualFlow
1TL_S4HC_I_CashLiquidityActualFlow
...
Formula:
Comments:
Delta capture not required.
2VR_CashA - 2VR_
...
S4HARM_I_CashFlowActualItem
Purpose:
Cast dates , restrict fields and apply a filterand add fields.
Source:
2TL_HARMS4HARM_I_CashLiquidityActualFlow
Filter:
BankAccountInternalID <> '' AND BankAccountInternalID <> '0000000000'
Projection:
CashFlowID, CshFlwValdtyStrtDteTmeVal, CompanyCode, TransactionDate, PostingDate, TransactionCurrency, AmountInTransactionCurrency, CompanyCodeCurrency, AmountInCompanyCodeCurrency, GlobalCurrency, AmountInGlobalCurrency, BankAccountInternalID, Bank, BankName, BankCountry
Formula:
Cast TO_DATE for ( TransactionDate and PostingDate)
Comments:
Convert to Graphical
alias - full
Purpose:
Source:
Filter:
Join:
Formula:
Comments:
...
...
...
...
Read from CashLiquidityActualFlow
- adding fields that dont get populated)
- Amend to include the previous layer (casting date)
...
Convert SQL to graphical?
Combine inbound layer (CashLiquidityActualFlow)
Adding ControllingArea, ProfitCentre,Segment, Customer, Supplier as NULL (these are now standard fields in the CDS).
Comments:
Convert to Graphical
Combine SAP_FI_HL_CashFlowActualItem (adding NULL fields) and SAP_FI_IL_CashLiquidityActualFlow (casting)
2VR_CashAL - 2VR_S4HARM_I_CashLiquidityActualFlow
Purpose:
Cast dates and restrict to bank related.
Source:
2TL_S4HARM_I_CashLiquidityActualFlow
Filter:
BankAccountInternalID <> '' AND BankAccountInternalID <> '0000000000'
Formula:
Cast TO_DATE for ( TransactionDate and PostingDate)
Comments:
Convert to Graphical
2TL_CashF - 2TL_S4HARM_I_CashLiquidityForecastFlow
Purpose:
Persist the 2 datasources into a single table for performance to avoid the runtime union.
Source / Union:
1TL_S4HR_I_CASHLIQUIDITYFORECASTFLOW
1TL_S4HC_I_CASHLIQUIDITYFORECASTFLOW
Comments:
Delta capture not required.
2VR_CashF - 2VR_S4HARM_I_CashLiquidityForecastFlow
Purpose:
Casting dates and filtering for bank related.
Filter:
BankAccountInternalID <> '' AND BankAccountInternalID <> '0000000000'
Formula:
Cast TO_DATE for ( TransactionDate and PostingDate)
Comments:
Convert to Graphical combining the 2 views
...
2TL_SKF - 2TL_S4HARM_I_FinStatisticalKeyFigItemBsc
Purpose:
Persist the 2 datasources into a single table for performance to avoid the runtime union.
Source:
1TL_S4HR_I_FinStatisticalKeyFigItemBsc
1TL_S4HC_I_FinStatisticalKeyFigItemBsc
2VR_SKF - 2VR_S4HARM_I_FinStatisticalKeyFigItemBsc
Purpose:
Cast dates and remove deprecated fields.
Source:
2TL_S4HARM_I_FinStatisticalKeyFigItemBsc
Projection:
Remove deprecated fields (WBS and Project)
Formula:
TO_DATE (ValidityStartDate)
...
2TL_Depr - 2TL_S4HARM_I_FxdAstStatisticalLineItem
Purpose:
Persist the 2 datasources into a single table for performance to avoid the runtime union.
Source:
1TL_S4HR_I_FxdAstStatisticalLineItem
1TL_S4HC_I_FxdAstStatisticalLineItem
2VR_Depr - 2VR_S4HARM_I_FxdAstStatisticalLineItem
Purpose:
Cast dates and remove deprecated fields.
Source:
2TL_S4HARM_I_FxdAstStatisticalLineItem
Projection:
Remove fields not required (eg additional currencies that Syensqo do not intend to use).
Formula:
TO_DATE (ValidityStartDate)
...
2VR_GrpJnlItm - 2VR_S4HARM_GroupJournalEntryItem
Purpose:
Casting fields
Source:
1TL_S4HR_I_CNSLDTNGROUPJRNLENTRITMDEX
Formula:
TO_DECIMAL(AmountInTransactionCurrency,23,2)
TO_DECIMAL(AmountInLocalCurrency,23,2)
TO_DECIMAL(AmountInGroupCurrency,23,2)
TO_DATE(CreationDate)
Comments:
Relational semantic type.
...
Propagation Layer
SKF
Purpose:
Enrich with accounting principle
Source:
I_AccountingPrincipleAssgmt
3VR_AcPlSemTag - ActlPlnJournalEntryItemSemTag
Purpose:
Source:
Join:
Join ActPlan N:N SemTagGLAccount (filter for validity dates and excld FunctArea)
Filter:
Formula:
Comments:
Remove redundant measures
Can we reduce initial projection?
Need to add headcount somewhere, maybe here?
CDS View I_SemTagGLACcount from SAP S/4HANA provides the mapping between the G/L accounts and semantic tags. It is the key component of this model.
This CDS view does not have any key fields. The replication of data without key fields from CDS Views is not supported by the Replication Flow at moment. Instead the local table SAP_FI_IL_I_SemTagGLAccount is used in this model to support both SAP S/4HANA Cloud and SAP S/4HANA source systems. The inbound layer view SAP_FI_IL_SemTagGLAccount is based on this table.
Customer should perform the following activities after importing the content package:
Create a remote table from CDS View I_SemTagGLAccount of the connected SAP S/4HANA system and replicate the data.
Create a dataflow to populate the local table SAP_FI_IL_I_SemTagGLAccount from the remote table. (For SAP S/4HANA Cloud sources a conversion from Date to String Datatype is needed in the dataflow.)
or
Replace the local table SAP_FI_IL_I_SemTagGLAccount with the remote table in the inbound layer view SAP_FI_IL_ SemTagGLAccount.
The Inbound layer view G/L Account with Semantic Target (IL) (SAP_FI_IL_ SemTagGLAccount) is based on the local table SAP_FI_IL_I_SemTagGLAccount and has some simple data type conversions like date conversion.
3VR_VersMap - 3VR_SPOD_R2RGLR_VersionMapping
Purpose:
Union with itself
The first select is where group currency = group currency and the second select is where it is not equal
Relating to statistical postings and adjustments in Local Currency, not envisaged as being used in our scenario
Source:
2VR_S4HARM_GroupJournalEntryItem
Union / Join:
2VR_S4HARM_GroupJournalEntryItem
INNER JOIN ConsVersRelation on ConsolidationVersionElement = ConsolidationVersionElement
INNER JOIN ConsVers on ConsolidationVersion = ConsolidationVersion and GroupCurrency = GroupCurrency
UNION ALL
2VR_S4HARM_GroupJournalEntryItem
INNER JOIN ConsVersRelation on ConsolidationVersionElement = ConsolidationVersionElement
INNER JOIN ConsVers on ConsolidationVersion = ConsolidationVersion and GroupCurrency <> GroupCurrency
adding fields ConsolidationVersion and FiscalYearVariant
Projection:
Removing creationdate, creationtime, creationdatetime (the count difference is -1 as we add 2 and remove these 3)
Formula:
The second select does not update the group currency and amount in group currency
Comments:
Need to see if any entries where group currency <> group currency. Potentially GRDC data
Else items in local currency that dont have a translation
Switch to graphical view
3VR_GrpJnlEntry - 3VR_SPOD_R2RGLR_GroupJournalEntryItem1
Purpose:
Union with same source
Source:
2VR_S4HARM_VersionMapping
Union / Join:
2VR_S4HARM_VersionMapping
INNER JOIN ConsGrpStr on ConsolidationVersion = ConsolidationVersion, ConsolidationUnit = ConsolidationUnit and FiscalYearPeriod between FromFiscalYearPeriod and ToFiscalYearPeriod
INNER JOIN ConsGrpStr on ConsolidationVersion = ConsolidationVersion, PartnerConsolidationUnit = ConsolidationUnit and FiscalYearPeriod between FromFiscalYearPeriod and ToFiscalYearPeriod andConsolidationGroup = ConsolidationGroup
where PostingLevel = 20 (two-sided elimination entries)
Replacing the ConsolidationGroup from the joined table
UNION ALL
2VR_S4HARM_VersionMapping
INNER JOIN ConsGrpStr on ConsolidationVersion = ConsolidationVersion, ConsolidationUnit = ConsolidationUnit and FiscalYearPeriod between FromFiscalYearPeriod and ToFiscalYearPeriod andConsolidationGroup = ConsolidationGroup
where PostingLevel = 02 or 12 AND RecordType = 0, A, D or E
UNION ALL
2VR_S4HARM_VersionMapping
INNER JOIN ConsGrpStr on ConsolidationVersion = ConsolidationVersion, ConsolidationUnit = ConsolidationUnit and FiscalYearPeriod between FromFiscalYearPeriod and ToFiscalYearPeriod
INNER JOIN ConsGrpStr on ConsolidationVersion = ConsolidationVersion, PartnerConsolidationUnit = ConsolidationUnit and FiscalYearPeriod between FromFiscalYearPeriod and ToFiscalYearPeriod andConsolidationGroup = ConsolidationGroup
where PostingLevel = 22 (two-sided elimination entries) AND RecordType = 0, A, D or E
UNION ALL
2VR_S4HARM_VersionMapping
where RecordType = U OR (RecordType = 0 AND PostingLevel in ( '', '00', '01', '08', '0C', '0T', '10', '20', '30' )
Projection:
Formula:
Comments:
Probably move to Propagation layer
Create as Graphical
For Group Journal Entry Items with two-sided elimination entries, the consolidation unit and partner consolidation unit need to be assessed along a given consolidation hierarchy on which node in the hierarchy they meet. Above this hierarchy node, the posting needs to be eliminated. Therefore, the posting gets assigned to an elimination member on this hierarchy node.
For all other Group Journal Entry Items, the elimination member information should be still available to allow showing all postings along one common hierarchy in a report. In this case the elimination member is just to be filled with the consolidation unit if the consolidation unit exists for the chosen consolidation unit hierarchy.
...
2VR_HARM_I_CashLiquidityActualFlow
...
Read from CashLiquidityActualFlow
Restrict where BankIntID not NULL
...
Convert SQL to graphical?
Combine inbound layer
...
2TL_HARM_I_CashLiquidityForecastFlow
...
Persist after union for performance
...
...
2VR_HARM_I_CashLiquidityForecastFlow
...
Restrict where BankIntID not NULL
Amend to include the previous layer (casting date)
...
Convert SQL to graphical?
Combine inbound layer
Propagation Layer
Only at this layer will we add additional datasets like plan and headcount
I am worried that the BSEG leg does not use PLANheadcoun
Ensure we perform group by when aggregating......
| Type | Code | Tech Name | Root | Logic | Potential Changes |
|---|---|---|---|---|---|
| Headcount | |||||
| 2VR_ActPlnItm | ACDOCA/ PLAN | 2VF_HARMS4HARM_GLAccountLineItem V1 union with the plan data | VR | 3VR_AcPlSemTag | ActlPlnJournalEntryItemSemTag | ACDOCA/ PLAN | Join ActPlan N:N SemTagGLAccount (filter for validity dates and excld FunctArea) Remove redundant measures |
| VF | 3VF_PFSemTag | ProfitLossSemanticTag | ACDOCA/ PLAN | Create parameters for FinStatemtVersion and Category Reduce dimensions (need to align to our needs) Add associations | |
| SemTag Leading Ledger | ACDOCA/ PLAN | Dead end | |||
| VR | 3VR_JEBalTS | JournalEntryBalanceTimeSeries | ACDOCA | Every month includes all postings belonging to this month and all postings from the past, and aggregates the data as cumulative balances Therefore, it excludes all postings with Fiscal Period ‘000’, which can be viewed as balance carry-forward items, which otherwise would lead to double counted values. | Convert SQL to graphical? |
| VR | 3VR_JESTTim3VR_JEBalSTTim | JournalEntryBalanceSemTagTimeSeries | ACDOCA | Every month includes all postings belonging to this month and all postings from the past, and aggregates the data as cumulative balances Therefore, it excludes all postings with Fiscal Period ‘000’, which can be viewed as balance carry-forward items, which otherwise would lead to double counted values. | Convert SQL to graphical? |
| VR | 3VR_JESTTim | JournalEntrySemTagTimeSeries | ACDOCA | Each month shows the changes or postings that occurred in this month. The values of the Financial Data Model Configuration (FinancialDataModelConfiguration) table are not fixed and can be changed by the user. If you define a G/L Account Hierarchy before joining with G/L Account with Semantic Tag (IL), you restrict the data from Journal Entry item Operational View to a single G/L Account Hierarchy by default. This is a requirement for Net Working Capital Time Series Consumption (NetWorkingCapitalTimeSeries). | Convert SQL to graphical? |
| VF | 3VF_Invent | InventoryTimeSeries | ACDOCA | Combines data from “Journal Entry Balance Time Series”, “Journal Entry Balance Semantic Tag Time Series” and “Journal Entry Semantic Tag Time Series”. Some semantic tag KPIs must include data from the past months, whereas others need to consider only the movements of the relevant months. Every branch introduces a calculated field UnionBranch which is included in some KPIs to check which type of data should be considered.
| |
| Cash Balances |
|
| |||
| VR | 3VR_CashTS | CashBalanceTimeSeries | CashA | Read from CashFlowActualItem, returns cash balance with bank account on End of MonthDate
| SQL to Graphic View? |
| TL | 3TL_CashBalT | TotalCashBalanceTimeSeriesTable | CashA | Dataflow aggregates an interim result from CashBalanceTimeSeries and persists the results for performance. This table is updated on an hourly basis. | |
| VF | 3VF_CashBalC | TotalCashBalanceTimeSeriesCube | CashA | Projection of TotalCashBalanceTimeSeriesTable adding associations | |
| VF | 3VF_CashWk | CashActualFlowByCalendarWeek | CashA | Read from CashLiquidityActualFlow
| SQL to Graphic View? Combine with lower layer? |
| VF | 3VF_CashFcst | CashForecastFlow | CashF | Read from CashLiquidityForecastFlow
| SQL to Graphic View? |
| VF | 3VF_CashFcstC | CashForecastBalanceFlow | CashF / CashA | Union Actual and Forecast adding associations
| will we have migrated history? |
| VR | 3VR_CashAW | CashActualFlowByCalendarWeek |
| Read from CashLiquidityActualFlow
| SQL to Graphic View? |
| VT | 3VT_CashACWT | CashActualFlowByCalendarWeekTable |
| Read from CashActualFlowByCalendarWeek and persist for performance?
| DF to Transformation Flow |
| VF | 3VF_CashACW | CashActualFlowByWeek |
| Reads from CashActualFlowByWeek adding Associations | |
| Net Working Capital |
|
| |||
| VR | 3VR_GLAccOps | JournalEntryItemOperationalView | ACDOCA/ BSEG | ACDOCA (GLAccountLineItemLeadingLedger) + BSEG (OperationalAcctgDocItem). 1:N This will add the 3 XREF fields | |
| VR | 3VR_OpsItemTS | JournalEntryOperationalViewTimeSeries | ACDOCA/ BSEG | Reads using SQL, JournalEntryItemOperationalView with inner join on TimeSeriesMonthlyValues to restrict to monthly balances Restricted to Debtors and Creditors and excludes Statistical Items Flag for items that are sales-related or not (IsSalesRelated indicator). Performs the currency role selection | SQL to Graphic View? |
| 3VR_NWCTSUn | NetWorkingCapitalBalanceTimeSeriesUnion | ACDOCA/ BSEG | Union of:
| SQL to Graphic View? | |
| 3TL_NWCTable | NetWorkingCapitalBalanceTimeSeriesTable | ACDOCA/ BSEG | A dataflow retrieves the data from “Net Working Capital Balance Time Series Union” and aggregates an interim result that is stored in the Net Working Capital Balance Time Series table. When you combine data from multiple areas, this may result in complex KPIs on top and may lead to serious performance issues, as live data from the inbound layer is processed to calculate the KPIs.
| Replace DF with Transformation Flow | |
| VF | 3VF_NWCCube | NetWorkingCapitalBalanceTimeSeriesCube | ACDOCA/ BSEG | Just a projection (removing Amount in Group Currency?) on NetWorkingCapitalBalanceTimeSeriesTable | Add Grp Curr? |
| Payables / Receivables |
|
| |||
| VR | 3VR_OpsItemPr | OperationalAcctDocItemProj | BSEG | Convert SQl to Graphical view (just restricting the fields read from OperationalAcctgDocItem) | SQL to Graphic View? |
| VR | 3VR_APItmBase | APARLineItemBase | ACDOCA/ BSEG | Read JournalEntryItemOperationalView Returns cleared vendor and customer items that are sales-related, for example, invoice and credit memo, and excludes the statistical items and items from recurring documents.
| SQL to Graphic View? |
| VR | 3VR_APARWR | APAROpenItemWRJ | ACDOCA/ BSEG | Reading from APARLineItemBase and restricting by joining on OperationalAcctDocItemProj to sales related, doc type <> V/P , Inv ref not Null Selects open items with an invoice reference (payments) When calculating arrears days for partial payment cases for base invoice items, the net due date of the reference item would be used instead of the net due date of the base item. | SQL to Graphic View? |
| VR | 3VR_APARNorj | APAROpenItemNORJ | ACDOCA/ BSEG | Reading from APARLineItemBase and restricting to doc type V/P, Inv ref is Null Selects open items with no invoice reference (partial payments). The net due date of the base item is retrieved for further calculation. | SQL to Graphic View? |
| VR | 3VR_APARUnion | APARLineItemUnion | ACDOCA/ BSEG | Links AP/AR Open Item with the Reference Join (APAROpenItemWRJ) view and the AP/AR Open Item no Reference Join (APAROpenItemNORJ) view, which retrieves dimensions and amounts fields for both direct payment and partial payment cases of open items for further calculation. | SQL to Graphic View? |
| VR | 3VR_APAROpen | APAROpenItem | Reading from APARLineItemUnion
| SQL to Graphic View? Please dont use OIDs Anymore. Product and CostCenter are exceptions right now. | |
| VF | 3VF_APAROpenC | APAROpenItemC | ACDOCA/ BSEG | Adding Associations | |
| Days Sales Outstanding |
|
| |||
| VF | 3VF_DSOBase | DaysSalesOutStdgDrctBase | ACDOCA/ BSEG | Reading from JournalEntryItemOperationalView
| SQL to Graphic View? prior year? |
| VR | 3VF_DSOAgg | DaysSalesOutStdgDrctLineItem | ACDOCA/ BSEG | Nested select from DaysSalesOutStdgDrctBase (for calculations before group by?)
Why is it called line item when aggregated? | SQL to Graphic View? |
| VR | 3VF_DSOTS | DaysSalesOutStdgDrctLineItemTmeSers | ACDOCA/ BSEG | Nested select from CalendarMonth with left outer join on DaysSalesOutStdgDrctLineItem To perform if before aggregation? | SQL to Graphic View? perform string on lower level? |
| VF | 3VF_DSOCube | DSODrctLineItem | ACDOCA/ BSEG | Add associations | |
| Days Payable Outstanding |
|
| |||
| VF | 3VF_DPOBase | DaysPyblOutStdgDrctBase | ACDOCA/ BSEG | Reading from JournalEntryItemOperationalView
| SQL to Graphic View? prior year? |
| VR | 3VF_DPOAgg | DaysPyblOutStdgDirect | ACDOCA/ BSEG | Nested select from DaysPyblOutStdgDrctBase (for calculations before group by?)
Why is it called line item when aggregated? | SQL to Graphic View? |
| VR | 3VF_DPOTS | DaysPyblOutStdgDrctLineItemTmeSers | ACDOCA/ BSEG | Nested select from CalendarMonth with left outer join on DaysPyblOutStdgDrctLineItem To perform if before aggregation? | SQL to Graphic View? perform string on lower level? |
| VF | 3VF_DPOCube | DPODrctLineItem | ACDOCA/ BSEG | Add associations | |
| On Time Payments |
|
| |||
| VR | 3VR_OTPBase | OnTimePaymentRateBase | ACDOCA/ BSEG | Reading from JournalEntryItemOperationalView, returns cleared vendor items
| SQL to Graphic View? Time range as 2 years |
| VR | 3VR_OTPAgg | OnTimePaymentRateSum | ACDOCA/ BSEG | Nested select from OnTimePaymentRateBase (for calculations before group by?)
| SQL to Graphic View? |
| VF | 3VF_OTPCube | OnTimePaymentRate | ACDOCA/ BSEG | Add associations | |
| Cash Discounts |
|
| |||
| VR | 3VR_DiscBase | APCshDiscRealizedBase | ACDOCA/ BSEG | Reading from OperationalAcctgDocItem
| SQL to Graphic View? |
| VR | 3VR_DiscCnv | APCshDiscRealizedConvert | ACDOCA/ BSEG | Reading from APCshDiscRealizedBase, returns cleared vendor items
| SQL to Graphic View? |
| VR | 3VR_DiscAgg | APCshDiscRealized | ACDOCA/ BSEG | Nested select from APCshDiscRealizedConvert (for calculations before group by?)
| SQL to Graphic View? |
| VF | 3VF_DiscCube | APCshDiscRealizedC | ACDOCA/ BSEG | Add associations | |
| Assets | Read from Acdoca/Bseg
|
...