@AbapCatalog.sqlViewName: 'PFSHIERINCCODE'
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #COMPOSITE
@VDM.private: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@AbapCatalog.compiler.compareFilter: true
define view P_FinStmntHierarchyWithCoCode
with parameters
P_KeyDate : fis_fsv_key_date,
P_AlternativeGLAccountIsUsed : char1,
p_FinancialStatementHierarchy : hryid
as select from I_FinStmntExpandedHierNode as Hierarchy
// inner join I_GLAccountInCompanyCode as GLAccountInCompanyCode on
// Hierarchy.FunctionalAreaIsUsed = 'A' and
// Hierarchy.FinStatementHierarchyNodeValue = GLAccountInCompanyCode.GLAccount
inner join P_GLACCTWITHCOCODE(
p_FinancialStatementHierarchy: :p_FinancialStatementHierarchy,
P_AlternativeGLAccountIsUsed : :P_AlternativeGLAccountIsUsed) as fsvglacct on
Hierarchy.FunctionalAreaIsUsed = 'A'
and Hierarchy.FinStatementHierarchyNodeValue = fsvglacct.GLAccountExternal
association [0..*] to I_FinancialStatementHierNodeT as _Text on $projection.FinancialStatementHierarchy = _Text.FinancialStatementHierarchy
and $projection.HierarchyNode = _Text.HierarchyNode
and $projection.ValidityEndDate = _Text.ValidityEndDate
and $projection.FinancialStatementLeafItem = '' // just to show that this association is only to be followed if GLAcount is blank
association [0..*] to I_FinancialStatementHierNodeT as _ParentText on $projection.FinancialStatementHierarchy = _ParentText.FinancialStatementHierarchy
and $projection.ParentNode = _ParentText.HierarchyNode
and $projection.ValidityEndDate = _ParentText.ValidityEndDate
association [0..1] to I_FinancialStatementLeafItem as _FinancialStatementLeafItem on $projection.FinancialStatementLeafItem = _FinancialStatementLeafItem.FinancialStatementLeafItem
association [0..1] to I_FinancialStatementHier as _Hierarchy on $projection.FinancialStatementHierarchy = _Hierarchy.FinancialStatementHierarchy
and $projection.ValidityEndDate = _Hierarchy.ValidityEndDate // Hierarchy directory and hierarchy nodes have the same time slice
// 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 FinancialStatementHierarchy,
key HierarchyVersion,
@ObjectModel.text.association: '_Text'
key HierarchyNode,
@ObjectModel.text.association: '_ParentText'
key ParentNode,
@Semantics.businessDate.to: true
key ValidityEndDate,
key CompanyCode,
// key GLAccount,
// AlternativeGLAccount,
key fsvglacct.GLAccountExternal as GLAccount,
Hierarchy.ChartOfAccounts,
fsvglacct.CountryChartOfAccounts,
fsvglacct.AlternativeGLAccount,
cast( '' as fkber ) as FunctionalArea,
// ChartOfAccounts,
NodeType,
FinStatementHierarchyNodeValue,
DebitCreditCode,
FinancialStatementItem,
GLAccountInfo,
FunctionalAreaIsUsed,
FinancialStatementLeafItem,
SequenceNumber,
// HierarchyNodeSequence,
HierarchyLevel,
SignIsInverted,
_Text,
_ParentText,
_FinancialStatementLeafItem,
_Hierarchy
// _NodeAttribute
}
//where Hierarchy.ValidityEndDate = '99991231'
where Hierarchy.ValidityStartDate <= $parameters.P_KeyDate
and Hierarchy.ValidityEndDate >= $parameters.P_KeyDate
union all
select from I_FinStmntExpandedHierNode as Hierarchy
//select from I_FinStmntHierNodeForAll as Hierarchy
association [0..*] to I_FinancialStatementHierNodeT as _Text on $projection.FinancialStatementHierarchy = _Text.FinancialStatementHierarchy
and $projection.HierarchyNode = _Text.HierarchyNode
and $projection.ValidityEndDate = _Text.ValidityEndDate
and $projection.FinancialStatementLeafItem = '' // just to show that this association is only to be followed if GLAcount is blank
association [0..*] to I_FinancialStatementHierNodeT as _ParentText on $projection.FinancialStatementHierarchy = _ParentText.FinancialStatementHierarchy
and $projection.ParentNode = _ParentText.HierarchyNode
and $projection.ValidityEndDate = _ParentText.ValidityEndDate
association [0..1] to I_FinancialStatementLeafItem as _FinancialStatementLeafItem on $projection.FinancialStatementLeafItem = _FinancialStatementLeafItem.FinancialStatementLeafItem
association [0..1] to I_FinancialStatementHier as _Hierarchy on $projection.FinancialStatementHierarchy = _Hierarchy.FinancialStatementHierarchy
and $projection.ValidityEndDate = _Hierarchy.ValidityEndDate // Hierarchy directory and hierarchy nodes have the same time slice
// 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 FinancialStatementHierarchy,
key HierarchyVersion,
@ObjectModel.text.association: '_Text'
key HierarchyNode,
@ObjectModel.text.association: '_ParentText'
key ParentNode,
@Semantics.businessDate.to: true
key ValidityEndDate,
key cast('' as bukrs) as CompanyCode,
key cast('' as saknr) as GLAccount,
ChartOfAccounts,
cast('' as ktop2) as CountryChartOfAccounts,
cast('' as saknr) as AlternativeGLAccount,
cast('' as saknr) as FunctionalArea,
// cast(case when Hierarchy.FunctionalAreaIsUsed = 'F' then FinStatementHierarchyNodeValue
// else ''
// end as fkber ) as FunctionalArea,
NodeType,
FinStatementHierarchyNodeValue,
DebitCreditCode,
FinancialStatementItem,
GLAccountInfo,
FunctionalAreaIsUsed,
FinancialStatementLeafItem,
SequenceNumber,
// HierarchyNodeSequence,
HierarchyLevel,
SignIsInverted,
_Text,
_ParentText,
_FinancialStatementLeafItem,
_Hierarchy
// _NodeAttribute
}
where
// Hierarchy.ValidityEndDate = '99991231'
Hierarchy.ValidityStartDate <= $parameters.P_KeyDate
and Hierarchy.ValidityEndDate >= $parameters.P_KeyDate
and ( Hierarchy.NodeType = 'R' //Root
or Hierarchy.NodeType = 'N' //Node
or Hierarchy.NodeType = 'M' //ERGGV PLResult
or Hierarchy.NodeType = 'K' //ERGAK Net LOSS
or Hierarchy.NodeType = 'A' //ERGPA Net PROFIT
or Hierarchy.NodeType = 'J' //ERGAK Net LOSS duplicated
or Hierarchy.NodeType = 'B' //ERGPA Net PROFIT duplicated
or Hierarchy.NodeType = 'V' //ERGGV PLResult duplicated
or Hierarchy.NodeType = 'O' //Contra Items
// or Hierarchy.FunctionalAreaIsUsed = 'F' //Functional Area Leaf Item
)
// and Hierarchy.FunctionalAreaIsUsed = 'F' //Functional Area Leaf Item
union all
select from I_FinStmntExpandedHierNode as Hierarchy
inner join I_CompanyCode as _CompanyCode on
Hierarchy.FunctionalAreaIsUsed = 'F'
//select from I_FinStmntHierNodeForAll as Hierarchy
association [0..*] to I_FinancialStatementHierNodeT as _Text on $projection.FinancialStatementHierarchy = _Text.FinancialStatementHierarchy
and $projection.HierarchyNode = _Text.HierarchyNode
and $projection.ValidityEndDate = _Text.ValidityEndDate
and $projection.FinancialStatementLeafItem = '' // just to show that this association is only to be followed if GLAcount is blank
association [0..*] to I_FinancialStatementHierNodeT as _ParentText on $projection.FinancialStatementHierarchy = _ParentText.FinancialStatementHierarchy
and $projection.ParentNode = _ParentText.HierarchyNode
and $projection.ValidityEndDate = _ParentText.ValidityEndDate
association [0..1] to I_FinancialStatementLeafItem as _FinancialStatementLeafItem on $projection.FinancialStatementLeafItem = _FinancialStatementLeafItem.FinancialStatementLeafItem
association [0..1] to I_FinancialStatementHier as _Hierarchy on $projection.FinancialStatementHierarchy = _Hierarchy.FinancialStatementHierarchy
and $projection.ValidityEndDate = _Hierarchy.ValidityEndDate // Hierarchy directory and hierarchy nodes have the same time slice
// 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 FinancialStatementHierarchy,
key HierarchyVersion,
@ObjectModel.text.association: '_Text'
key HierarchyNode,
@ObjectModel.text.association: '_ParentText'
key ParentNode,
@Semantics.businessDate.to: true
key ValidityEndDate,
// key cast('' as bukrs) as CompanyCode,
key CompanyCode,
key cast('' as saknr) as GLAccount,
Hierarchy.ChartOfAccounts,
cast('' as ktop2) as CountryChartOfAccounts,
cast('' as saknr) as AlternativeGLAccount,
cast( FinStatementHierarchyNodeValue as fkber ) as FunctionalArea,
NodeType,
FinStatementHierarchyNodeValue,
DebitCreditCode,
FinancialStatementItem,
GLAccountInfo,
FunctionalAreaIsUsed,
FinancialStatementLeafItem,
SequenceNumber,
// HierarchyNodeSequence,
HierarchyLevel,
SignIsInverted,
_Text,
_ParentText,
_FinancialStatementLeafItem,
_Hierarchy
// _NodeAttribute
}
where
// Hierarchy.ValidityEndDate = '99991231'
Hierarchy.ValidityStartDate <= $parameters.P_KeyDate
and Hierarchy.ValidityEndDate >= $parameters.P_KeyDate
and Hierarchy.FunctionalAreaIsUsed = 'F' //Functional Area Leaf Item