ecc/FSVHierarchyFlattened.sql (63 lines of code) (raw):

--The granularity of this query is Client,Chart of accounts,Hierarchy name, --GLNode,GLAccount,Language Key. --## CORTEX-CUSTOMER Please filter on HierarchyName in case of multiple hierarchies --flattened in your system. WITH LanguageKey AS ( SELECT LanguageKey_SPRAS FROM `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.Languages_T002` WHERE LanguageKey_SPRAS IN UNNEST({{ sap_languages }}) ), ParentId AS ( SELECT DISTINCT fsv_parent.mandt, fsv_parent.parent, fsv_child.ergsl FROM `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.fsv_glaccounts` AS fsv_parent INNER JOIN `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.fsv_glaccounts` AS fsv_child ON fsv_parent.mandt = fsv_child.mandt AND fsv_parent.parent = fsv_child.node ) SELECT FSV.mandt AS Client_MANDT, FSV.chartofaccounts AS ChartOfAccounts, FSV.hiername AS HierarchyName, LanguageKey.LanguageKey_SPRAS, FSV.glaccount AS GeneralLedgerAccount, FSV.node AS GLNode, FSV.parent AS ParentNode, --The following text fields are language dependent. IF(FSV.Level = '02', FSV.hiername, GLParentText.FinancialStatementItemText_TXT45) AS ParentNodeText, COALESCE(GLNodeText.FinancialStatementItemText_TXT45, GLText.GlAccountLongText_TXT50) AS GLNodeText, FSV.level AS Level, FSV.isleafnode AS IsLeafNode FROM `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.fsv_glaccounts` AS FSV LEFT JOIN ParentId ON FSV.mandt = ParentId.mandt AND FSV.Parent = ParentId.Parent CROSS JOIN LanguageKey LEFT JOIN `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.FSVTextsMD` AS GLNodeText ON FSV.mandt = GLNodeText.Client_MANDT AND FSV.hiername = GLNodeText.FinancialStatementVersion_VERSN AND FSV.ergsl = GLNodeText.FinancialStatementItem_ERGSL AND GLNodeText.LanguageKey_SPRAS = LanguageKey.LanguageKey_SPRAS -- TextType_TXTYP = 'K' represents text for the node AND GLNodeText.TextType_TXTYP = 'K' LEFT JOIN `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.GLAccountsMD` AS GLText ON FSV.mandt = GLText.Client_MANDT AND FSV.chartofaccounts = GLText.ChartOfAccounts_KTOPL AND FSV.ergsl = GLText.GlAccountNumber_SAKNR AND GLText.Language_SPRAS = LanguageKey.LanguageKey_SPRAS LEFT JOIN `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.FSVTextsMD` AS GLParentText ON FSV.mandt = GLParentText.Client_MANDT AND FSV.hiername = GLParentText.FinancialStatementVersion_VERSN AND ParentID.ergsl = GLParentText.FinancialStatementItem_ERGSL AND GLParentText.LanguageKey_SPRAS = LanguageKey.LanguageKey_SPRAS -- TextType_TXTYP = 'K' represents text for the node AND GLParentText.TextType_TXTYP = 'K'