Table of Contents:

Overview on this Blog

Hi SAP Community,
This is part one of a three part blog series on How to Build a GL Account Hierarchy for SAC Planning with CDS Views.

Part1 – Loading the ID & DESC of the GLPart2 – Loading the hierarchyPart3 – Loading the Desc for the Hierarchy nodes

Prerequisites for this blog

Basic understanding of SAC, SAC Planning & Public Dimensions.CDS Views, ODATA, creating a service.FSV, GL Account Hierarchy.

Problem Statement

Let’s understand our objective first before we begin with the technical details. We will be building the CDS view required for populating the GL account (Public dimension) in SAC Planning. Our end goal is to build a fully functioning future-proof GL hierarchy for our Public dimension in SAC.

GL Account Hierarchy in SAC

Modeling Approach

With any SAC Planning implementation project, Our end goal would be to create input sheets for planning users to input their budget data, ultimately retracting the data to S4/HANA ACDOCP.

In order for the input sheets to be meaningful, we need to have relevant dimensions in place for building the model. In most cases, these planning are to be done at a GL account Level. These GL’s are maintained in a Financial Statement version (FSV) in the S4/HANA System. It can be previewed using the transaction code OB58.

Now that we know we need to fetch these GL accounts, ‘Why don’t we import all the GL’s into one public dimension and build its hierarchy?’ , Short answer would be, it will be messy and having too much unwanted information. In SAC Planning, this directly impacts input form usability and planning accuracy.

The best approach would be to tackle planning in modules, i.e., SALES, OPEX, WORKFORCE, CAPEX, INVENTORY rather than one big FINANCE planning. So inorder to handle these individual modules we need to have independent GL account public dimensions for these processes. Eg.(Z_SALES_GLACCOUNT, Z_OPEX_GLACCOUNT,.. etc).

Next question, ‘There must be a way to selectively filter out only the GL’s needed for my process. How do we restrict only a portion of the FSV dynamically?’

The condition is, it should work seamlessly irrespective of any new GLs added into the system. Hardcoding GL accounts & their hierarchy will definitely fail when a new GL gets added or when the GL gets restructured into a different node.

FSV

The master node of the FSV is not likely to change. Hence we will create a mechanism that lets us capture the GLs underneath one of the master nodes. These structures are called FSV Item key. Each branch of the FSV has its own FSV Item key and it can traverse to a maximum depth of Level 8. Let’s uncover one thing at time. So if we need to build a GL Account dimension for storing the master data of all the GLs related to Sales and its hierarchy, we just need to consider the GLs under nodes Turnover(08), CostofSales(09), OtherIncome(11) and Taxation(17). On Doing so, all the nodes under these and the respective GLs would be captured. This approach ensures that newly added GLs automatically flow into SAC without any redesign

Technical Details and Source code

This is a 3 part blog, I’ll summarize the content as below:

Part1 – Loading the ID & DESC of the GLPart2 – Loading the hierarchy (Leaf & Headers)Part3 – Loading the Desc for the Hierarchy nodes

Query Details

In this Part1 blog, we will create Query1 [1 ID and Desc Mapping], to populate the ID and Description of every SALES related GL from our FSV.

Step 1:
First we need to obtain all the GL IDs and Desc present within the system.

@AbapCatalog.sqlViewName: ‘ZSQL_GL_M’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘For getting GL Master’
define view ZCDS_GL_MASTER as select distinct from
ska1 as a inner join skat as b
on a.saknr = b.saknr
{
a.saknr, –GL
b.txt20, –Short Text desc
b.txt50 –Long Text desc
}
where a.ktopl = ‘CGC1′ and b.spras=’E’ and b.ktopl=’CGC1′

Step 2:

Secondly, we need to restrict the GLs belonging to our FSV (FSV1). Joining ZCDS_GL_MASTER with HRRP_NODE will enable us to apply filter on HRYID field (For selecting our targeted FSV).

@AbapCatalog.sqlViewName: ‘ZSQL_GLM’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘For Mapping the GL account master’
@Analytics.dataExtraction.enabled: true
@OData.publish: true
define view ZCDS_GLACCOUNT_ID_DESC
as select from
ZCDS_GL_MASTER as a inner join hrrp_node as b
on a.saknr = b.nodevalue
{
key a.saknr, –GL Account
key b.hryid, –Hierarchy ID
key b.parnode, –Parent
a.txt20, –Short text desc
a.txt50, –Long text desc
b.nodetype. –Node Type
}

where (b.nodetype=’D’ or b.nodetype=’L’) and (b.hryid=’FSV1′)

Now that we have the ZCDS_GLACCOUNT_ID_DESC CDS view ready, We need to expose it via ODATA, create a service and then consume it in our SAC Public dimension.

Once the OData has been exposed and the respective Service has been created, We can now go to our SAC Public Dimension which we have created and choose S4/HANA connection and type in our CDS view query to fetch the data.

Create the First query with the below selected fields and the FSV item keys suitable for your Business process for Loading the ID and Description.

Be sure to map GL to ID and GL Account long text to Description

The sample result would be like this.

Description

Hierarchy

Account type

40010000

Revenue – Good (Export)

40010001

Revenue – Good (Service)

Conclusion

In this first part of the series, we focused on loading the GL Account ID and Description required for SAC Planning by selectively filtering GLs from the Financial Statement Version using CDS views. This step ensures that only relevant GL accounts are brought into the SAC public dimension, keeping the model clean and usable for planning.

In Part 2, we will build on this foundation and cover how to load the GL hierarchy structure, including both leaf and header nodes, using CDS views. This will help complete the hierarchy needed for drill-up and planning input in SAC.

If you have any doubts along the way, feel free to reach out to me in the comments below and Don’t forget to Follow my profile if you find this BlogPost helpful. 👍

Table of Contents:Overview on this BlogHi SAP Community,This is part one of a three part blog series on How to Build a GL Account Hierarchy for SAC Planning with CDS Views.Part1 – Loading the ID & DESC of the GLPart2 – Loading the hierarchyPart3 – Loading the Desc for the Hierarchy nodes Prerequisites for this blogBasic understanding of SAC, SAC Planning & Public Dimensions.CDS Views, ODATA, creating a service.FSV, GL Account Hierarchy.Problem StatementLet’s understand our objective first before we begin with the technical details. We will be building the CDS view required for populating the GL account (Public dimension) in SAC Planning. Our end goal is to build a fully functioning future-proof GL hierarchy for our Public dimension in SAC.GL Account Hierarchy in SACModeling ApproachWith any SAC Planning implementation project, Our end goal would be to create input sheets for planning users to input their budget data, ultimately retracting the data to S4/HANA ACDOCP.In order for the input sheets to be meaningful, we need to have relevant dimensions in place for building the model. In most cases, these planning are to be done at a GL account Level. These GL’s are maintained in a Financial Statement version (FSV) in the S4/HANA System. It can be previewed using the transaction code OB58. Now that we know we need to fetch these GL accounts, ‘Why don’t we import all the GL’s into one public dimension and build its hierarchy?’ , Short answer would be, it will be messy and having too much unwanted information. In SAC Planning, this directly impacts input form usability and planning accuracy.The best approach would be to tackle planning in modules, i.e., SALES, OPEX, WORKFORCE, CAPEX, INVENTORY rather than one big FINANCE planning. So inorder to handle these individual modules we need to have independent GL account public dimensions for these processes. Eg.(Z_SALES_GLACCOUNT, Z_OPEX_GLACCOUNT,.. etc).Next question, ‘There must be a way to selectively filter out only the GL’s needed for my process. How do we restrict only a portion of the FSV dynamically?’ The condition is, it should work seamlessly irrespective of any new GLs added into the system. Hardcoding GL accounts & their hierarchy will definitely fail when a new GL gets added or when the GL gets restructured into a different node. FSV The master node of the FSV is not likely to change. Hence we will create a mechanism that lets us capture the GLs underneath one of the master nodes. These structures are called FSV Item key. Each branch of the FSV has its own FSV Item key and it can traverse to a maximum depth of Level 8. Let’s uncover one thing at time. So if we need to build a GL Account dimension for storing the master data of all the GLs related to Sales and its hierarchy, we just need to consider the GLs under nodes Turnover(08), CostofSales(09), OtherIncome(11) and Taxation(17). On Doing so, all the nodes under these and the respective GLs would be captured. This approach ensures that newly added GLs automatically flow into SAC without any redesign Technical Details and Source codeThis is a 3 part blog, I’ll summarize the content as below:Part1 – Loading the ID & DESC of the GLPart2 – Loading the hierarchy (Leaf & Headers)Part3 – Loading the Desc for the Hierarchy nodes Query DetailsIn this Part1 blog, we will create Query1 [1 ID and Desc Mapping], to populate the ID and Description of every SALES related GL from our FSV.Step 1:First we need to obtain all the GL IDs and Desc present within the system.@AbapCatalog.sqlViewName: ‘ZSQL_GL_M’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘For getting GL Master’
define view ZCDS_GL_MASTER as select distinct from
ska1 as a inner join skat as b
on a.saknr = b.saknr
{
a.saknr, –GL
b.txt20, –Short Text desc
b.txt50 –Long Text desc
}
where a.ktopl = ‘CGC1′ and b.spras=’E’ and b.ktopl=’CGC1’Step 2:Secondly, we need to restrict the GLs belonging to our FSV (FSV1). Joining ZCDS_GL_MASTER with HRRP_NODE will enable us to apply filter on HRYID field (For selecting our targeted FSV).@AbapCatalog.sqlViewName: ‘ZSQL_GLM’
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: ‘For Mapping the GL account master’
@Analytics.dataExtraction.enabled: true
@OData.publish: true
define view ZCDS_GLACCOUNT_ID_DESC
as select from
ZCDS_GL_MASTER as a inner join hrrp_node as b
on a.saknr = b.nodevalue
{
key a.saknr, –GL Account
key b.hryid, –Hierarchy ID
key b.parnode, –Parent
a.txt20, –Short text desc
a.txt50, –Long text desc
b.nodetype. –Node Type
}

where (b.nodetype=’D’ or b.nodetype=’L’) and (b.hryid=’FSV1′)Now that we have the ZCDS_GLACCOUNT_ID_DESC CDS view ready, We need to expose it via ODATA, create a service and then consume it in our SAC Public dimension.Once the OData has been exposed and the respective Service has been created, We can now go to our SAC Public Dimension which we have created and choose S4/HANA connection and type in our CDS view query to fetch the data.Create the First query with the below selected fields and the FSV item keys suitable for your Business process for Loading the ID and Description.Be sure to map GL to ID and GL Account long text to DescriptionThe sample result would be like this. IDDescriptionHierarchyAccount type40010000Revenue – Good (Export) 40010001Revenue – Good (Service) ConclusionIn this first part of the series, we focused on loading the GL Account ID and Description required for SAC Planning by selectively filtering GLs from the Financial Statement Version using CDS views. This step ensures that only relevant GL accounts are brought into the SAC public dimension, keeping the model clean and usable for planning.In Part 2, we will build on this foundation and cover how to load the GL hierarchy structure, including both leaf and header nodes, using CDS views. This will help complete the hierarchy needed for drill-up and planning input in SAC.If you have any doubts along the way, feel free to reach out to me in the comments below and Don’t forget to Follow my profile if you find this BlogPost helpful. 👍 Read More Technology Blog Posts by Members articles

#SAP

#SAPTechnologyblog