I_FinStmntExpandedHierNode

@AbapCatalog.sqlViewName: 'IFISTMNTEXPND'
@VDM.viewType: #COMPOSITE
@ObjectModel: { dataCategory: #HIERARCHY }
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@AbapCatalog.buffering.status: #NOT_ALLOWED
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass:  #MASTER
@ObjectModel.usageType.serviceQuality: #C
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.ignorePropagatedAnnotations: true
@EndUserText.label: 'Financial Statement Expanded Hier Node'
define view I_FinStmntExpandedHierNode  
  as select from I_FinancialStatementHier as hier
  left outer join R_HierRuntimeRprstnAttrib as source on hier.FinancialStatementHierarchy = source.HierarchyID
                                and hier.ValidityEndDate = source.ValidityEndDate
                                and source.HierarchyAttributeName = 'SOURCE'
                                and ( hier.ValidityEndDate = '99991231' or source.HierAttributeRangeFromVal = 'UH' )                                
  left outer join R_HierRuntimeRprstnNode as node on hier.FinancialStatementHierarchy = node.HierarchyID
                                and hier.ValidityEndDate = node.ValidityEndDate                                
  left outer join R_HierRuntimeRprstnNodeAttrib as attrib on node.HierarchyID = attrib.HierarchyID
                                                         and node.HierarchyVersion = attrib.HierarchyVersion
                               and node.ValidityEndDate = attrib.ValidityEndDate
                               and node.HierarchyNodeClass = attrib.HierarchyNodeClass
                               and node.HierarchyNode = attrib.HierarchyNode
                               and node.ParentNode = attrib.ParentNode
                               and attrib.HierarchyNodeAttributeName = 'SIGN'
                               
  association [0..*] to I_FinancialStatementHierNodeT as _Text            on     $projection.FinancialStatementHierarchy = _Text.FinancialStatementHierarchy
                                 and $projection.ValidityEndDate     = _Text.ValidityEndDate    
                                 and $projection.HierarchyNode = _Text.HierarchyNode
  association [0..*] to I_FinancialStatementHierNodeT as _ParentText           on     $projection.FinancialStatementHierarchy = _ParentText.FinancialStatementHierarchy
                                 and $projection.ValidityEndDate     = _ParentText.ValidityEndDate   
                                 and $projection.HierarchyNode = _ParentText.HierarchyNode
  association [0..1] to I_FinancialStatementHier as _Hierarchy on $projection.FinancialStatementHierarchy = _Hierarchy.FinancialStatementHierarchy
                                                         and $projection.ValidityEndDate     = _Hierarchy.ValidityEndDate
  association [0..1] to I_FinancialStatementLeafItem              as _FinancialStatementLeafItem  on  $projection.FinancialStatementLeafItem = _FinancialStatementLeafItem.FinancialStatementLeafItem
  association [0..*] to P_HRRP_NODE_ATTR as _NodeAttribute on  $projection.FinancialStatementHierarchy = _NodeAttribute.HierarchyID
                                                                and $projection.HierarchyVersion      = _NodeAttribute.HierarchyVersion
                                                                and $projection.ValidityEndDate      = _NodeAttribute.ValidityEndDate
                                                                and $projection.ChartOfAccounts      = _NodeAttribute.NodeClass
                                                                and $projection.HierarchyNode      = _NodeAttribute.HierarchyNode
                                                                and $projection.ParentNode      = _NodeAttribute.ParentNode
  {
  
//      @ObjectModel.foreignKey.association: '_Hierarchy'

  key node.HierarchyID                              as FinancialStatementHierarchy,
  key node.HierarchyVersion,
      @ObjectModel.text.association: '_Text'
  key node.HierarchyNode,
      @ObjectModel.text.association: '_ParentText'
  key node.ParentNode,
      @Semantics.businessDate.to: true
  key node.ValidityEndDate,
      node.ValidityStartDate,
      node.HierarchyNodeClass                            as ChartOfAccounts,
      node.NodeType,
      node.HierarchyNodeVal                          as FinStatementHierarchyNodeValue,
      node.DebitCreditCode,

      cast( 
        case when NodeType = 'R' or NodeType = 'N' or NodeType = 'M' 
                or NodeType = 'K' or NodeType = 'A' or NodeType = 'O' then
          node.HierarchyNodeVal
        else ''
      end as ergsl )                               as FinancialStatementItem,
      
      cast( case 
        when NodeType = 'L' or NodeType = 'D' then
          case node.HierarchyNodeClass
            when '' then  concat(HierarchyNodeVal,'F') // Functional Area

            else
                concat(node.HierarchyNodeClass,lpad(HierarchyNodeVal,12,'0'))
          end
        else  ''
      end as hrynode)                              as GLAccountInfo,
      
      case 
        when NodeType = 'L' or NodeType = 'D' then
          case node.HierarchyNodeClass
            when '' then  'F' // Functional Area

            else  'A'
          end
        else  ''
      end                                          as FunctionalAreaIsUsed,

      @ObjectModel.foreignKey.association: '_FinancialStatementLeafItem'
      cast( case 
        when NodeType = 'L' or NodeType = 'D' then
          case node.HierarchyNodeClass
            when '' then  concat(HierarchyNodeVal,'F') // Functional Area

            else
              case DebitCreditCode
                when 'S' then concat(node.HierarchyNodeClass,concat(HierarchyNodeVal,'1A'))
                when 'H' then concat(node.HierarchyNodeClass,concat(HierarchyNodeVal,'2A'))
                else concat(node.HierarchyNodeClass,concat(HierarchyNodeVal,'0A'))
//                when 'S' then concat(node.HierarchyNodeClass,concat(lpad(HierarchyNodeVal,12,'0'),'1A'))

//                when 'H' then concat(node.HierarchyNodeClass,concat(lpad(HierarchyNodeVal,12,'0'),'2A'))

//                else concat(node.HierarchyNodeClass,concat(lpad(HierarchyNodeVal,12,'0'),'0A'))

              end
          end
        else  ''
      end as hrynode)                              as FinancialStatementLeafItem,
      
      SequenceNumber,
//      SequenceNumber as HierarchyNodeSequence,

      HierarchyNodeLevel as HierarchyLevel,
      attrib.HierNodeAttributeRangeFromVal                  as SignIsInverted,

       _Text,
       _ParentText,
       _FinancialStatementLeafItem,
       _Hierarchy
}

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_FINANCIALSTATEMENTHIER",
"R_HIERRUNTIMERPRSTNATTRIB",
"R_HIERRUNTIMERPRSTNNODE",
"R_HIERRUNTIMERPRSTNNODEATTRIB"
],
"ASSOCIATED":
[
"I_FINANCIALSTATEMENTHIER",
"I_FINANCIALSTATEMENTHIERNODET",
"I_FINANCIALSTATEMENTLEAFITEM",
"P_HRRP_NODE_ATTR"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/