@AbapCatalog.sqlViewName: 'IFIGLACCTBALCUBE'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'G/L Account Balance Cube'
@VDM.viewType: #COMPOSITE
@Analytics: { dataCategory: #CUBE }
@AbapCatalog.dbHints: [ {dbSystem: #HDB, hint: 'NO_JOIN_THRU_AGGR'} ]
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.buffering.status: #NOT_ALLOWED
@Metadata.ignorePropagatedAnnotations: true
@Metadata.allowExtensions: true
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #MIXED
@AccessControl.personalData.blocking:#BLOCKED_DATA_EXCLUDED
@VDM.lifecycle.status: #DEPRECATED
@VDM.lifecycle.successor: 'I_CN_GLAcctBalanceSumCube'
define view I_GLAccountBalanceCube
with parameters
P_CompanyCode : fis_bukrs,
P_FiscalYear : fis_gjahr,
P_Ledger : fis_rldnr,
P_CurrencyRole : fac_crcyrole,
P_DisplayAltvAcct : figlcn_disalteracct,
P_FromPostingDate : fis_budat_from,
P_ToPostingDate : fis_budat_to,
P_FiscalPeriod : fins_fiscalperiod,
P_DspTimeDependentDesc: figlcn_distddesc
as select from
//part1, actual balance in periods for GL accounts which has posting
I_GLAcctBalance ( P_FromPostingDate: $parameters.P_FromPostingDate, P_ToPostingDate: $parameters.P_ToPostingDate ) as a1_GLAcctBalance
association [0..1] to I_LedgerCompanyCodeCrcyRoleVH as _Currency
on _Currency.CompanyCode = a1_GLAcctBalance.CompanyCode and _Currency.Ledger = a1_GLAcctBalance.Ledger
{
key $parameters.P_CompanyCode as CompanyCode,
key $parameters.P_FiscalYear as FiscalYear,
key $parameters.P_Ledger as Ledger,
//below for fix ATC
key SourceLedger,
key AccountingDocument,
key LedgerGLLineItem,
key GLAccountFlowType,
key FiscalPeriodDate,
//above for fix ATC
FiscalPeriod,
GLAccount,
ProfitCenter,
cast('' as setnamenew) as ProfitCenterGroup, //dummy field for filter
BusinessArea,
FunctionalArea,
Segment,
AlternativeGLAccount,
case $parameters.P_DisplayAltvAcct
when 'X' then cast(AlternativeGLAccount as figlcn_glaccountinfo)
else cast(GLAccount as figlcn_glaccountinfo)
end as GLAccountInfo,
cast('' as figlcn_desc) as GLAcctLineDescription,
GLAccountHierarchy,
LedgerFiscalYear,
//FiscalPeriodDate,
@Semantics.currencyCode:true
case $parameters.P_CurrencyRole
when _Currency.CompanyCodeCurrencyRole then CompanyCodeCurrency
when _Currency.GlobalCurrencyRole then GlobalCurrency
when _Currency.FreeDefinedCurrency1Role then FreeDefinedCurrency1
when _Currency.FreeDefinedCurrency2Role then FreeDefinedCurrency2
when _Currency.FreeDefinedCurrency3Role then FreeDefinedCurrency3
when _Currency.FreeDefinedCurrency4Role then FreeDefinedCurrency4
when _Currency.FreeDefinedCurrency5Role then FreeDefinedCurrency5
when _Currency.FreeDefinedCurrency6Role then FreeDefinedCurrency6
when _Currency.FreeDefinedCurrency7Role then FreeDefinedCurrency7
when _Currency.FreeDefinedCurrency8Role then FreeDefinedCurrency8
else CompanyCodeCurrency
end as DisplayCurrency,
_GLAccountInChartOfAccounts.GLAccountGroup as GLAccountGroup,
_GLAccountInCompanyCode.AccountIsMarkedForDeletion as AccountIsMarkedForDeletion,
'1' as GLAccountTransactionType,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'DisplayCurrency'} }
case $parameters.P_CurrencyRole
when _Currency.CompanyCodeCurrencyRole then EndingBalanceAmtInCoCodeCrcy
when _Currency.GlobalCurrencyRole then EndingBalanceAmtInGlobalCrcy
when _Currency.FreeDefinedCurrency1Role then EndingBalAmtInFreeDfndCrcy1
when _Currency.FreeDefinedCurrency2Role then EndingBalAmtInFreeDfndCrcy2
when _Currency.FreeDefinedCurrency3Role then EndingBalAmtInFreeDfndCrcy3
when _Currency.FreeDefinedCurrency4Role then EndingBalAmtInFreeDfndCrcy4
when _Currency.FreeDefinedCurrency5Role then EndingBalAmtInFreeDfndCrcy5
when _Currency.FreeDefinedCurrency6Role then EndingBalAmtInFreeDfndCrcy6
when _Currency.FreeDefinedCurrency7Role then EndingBalAmtInFreeDfndCrcy7
when _Currency.FreeDefinedCurrency8Role then EndingBalAmtInFreeDfndCrcy8
else EndingBalanceAmtInCoCodeCrcy
end as EndingBalAmtInDspCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'DisplayCurrency'} }
case $parameters.P_CurrencyRole
when _Currency.CompanyCodeCurrencyRole then DebitAmountInCoCodeCrcy
when _Currency.GlobalCurrencyRole then DebitAmountInGlobalCrcy
when _Currency.FreeDefinedCurrency1Role then DebitAmountInFreeDefinedCrcy1
when _Currency.FreeDefinedCurrency2Role then DebitAmountInFreeDefinedCrcy2
when _Currency.FreeDefinedCurrency3Role then DebitAmountInFreeDefinedCrcy3
when _Currency.FreeDefinedCurrency4Role then DebitAmountInFreeDefinedCrcy4
when _Currency.FreeDefinedCurrency5Role then DebitAmountInFreeDefinedCrcy5
when _Currency.FreeDefinedCurrency6Role then DebitAmountInFreeDefinedCrcy6
when _Currency.FreeDefinedCurrency7Role then DebitAmountInFreeDefinedCrcy7
when _Currency.FreeDefinedCurrency8Role then DebitAmountInFreeDefinedCrcy8
else DebitAmountInCoCodeCrcy
end as DebitAmountInDisplayCrcy,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'DisplayCurrency'} }
case $parameters.P_CurrencyRole
when _Currency.CompanyCodeCurrencyRole then CreditAmountInCoCodeCrcy
when _Currency.GlobalCurrencyRole then CreditAmountInGlobalCrcy
when _Currency.FreeDefinedCurrency1Role then CreditAmountInFreeDefinedCrcy1
when _Currency.FreeDefinedCurrency2Role then CreditAmountInFreeDefinedCrcy2
when _Currency.FreeDefinedCurrency3Role then CreditAmountInFreeDefinedCrcy3
when _Currency.FreeDefinedCurrency4Role then CreditAmountInFreeDefinedCrcy4
when _Currency.FreeDefinedCurrency5Role then CreditAmountInFreeDefinedCrcy5
when _Currency.FreeDefinedCurrency6Role then CreditAmountInFreeDefinedCrcy6
when _Currency.FreeDefinedCurrency7Role then CreditAmountInFreeDefinedCrcy7
when _Currency.FreeDefinedCurrency8Role then CreditAmountInFreeDefinedCrcy8
else CreditAmountInCoCodeCrcy
end as CreditAmountInDisplayCrcy
}
where a1_GLAcctBalance.CompanyCode = $parameters.P_CompanyCode
and a1_GLAcctBalance.FiscalYear = $parameters.P_FiscalYear
and a1_GLAcctBalance.Ledger = $parameters.P_Ledger
//this CDS view is not used anymore, and for fix ATC comment below codes
//union all
////part2, man-made zero balance (amount is 0) in periods(less than max period on selection screen) for GL accounts which has posting
//select from
//I_GLAccountLineItem as a2_GLAccountLineItem
//inner join I_FiscalYearPeriodForCmpnyCode as a3_FiscalYearPeriod
//on a2_GLAccountLineItem.CompanyCode = a3_FiscalYearPeriod.CompanyCode
//and a2_GLAccountLineItem.FiscalYear = a3_FiscalYearPeriod.FiscalYear
//association [0..1] to I_LedgerCompanyCodeCrcyRoleVH as _Currency
//on _Currency.CompanyCode = $parameters.P_CompanyCode and _Currency.Ledger = $parameters.P_Ledger
//{
//key $parameters.P_CompanyCode as CompanyCode,
//key $parameters.P_FiscalYear as FiscalYear,
//key $parameters.P_Ledger as Ledger,
//a3_FiscalYearPeriod.FiscalPeriod,
//GLAccount,
//ProfitCenter,
//cast('' as setnamenew) as ProfitCenterGroup, //dummy field for filter
//BusinessArea,
//FunctionalArea,
//Segment,
//AlternativeGLAccount,
//case $parameters.P_DisplayAltvAcct
//when 'X' then cast(AlternativeGLAccount as figlcn_glaccountinfo)
//else cast(GLAccount as figlcn_glaccountinfo)
//end as GLAccountInfo,
//cast('' as figlcn_desc) as GLAcctLineDescription,
//GLAccount as GLAccountHierarchy,
//$parameters.P_FiscalYear as LedgerFiscalYear,
//'00000000000' as FiscalPeriodDate,
//@Semantics.currencyCode:true
// case $parameters.P_CurrencyRole
// when _Currency.CompanyCodeCurrencyRole then _Currency.CompanyCodeCurrency
// when _Currency.GlobalCurrencyRole then _Currency.GlobalCurrency
// when _Currency.FreeDefinedCurrency1Role then _Currency.FreeDefinedCurrency1
// when _Currency.FreeDefinedCurrency2Role then _Currency.FreeDefinedCurrency2
// when _Currency.FreeDefinedCurrency3Role then _Currency.FreeDefinedCurrency3
// when _Currency.FreeDefinedCurrency4Role then _Currency.FreeDefinedCurrency4
// when _Currency.FreeDefinedCurrency5Role then _Currency.FreeDefinedCurrency5
// when _Currency.FreeDefinedCurrency6Role then _Currency.FreeDefinedCurrency6
// when _Currency.FreeDefinedCurrency7Role then _Currency.FreeDefinedCurrency7
// when _Currency.FreeDefinedCurrency8Role then _Currency.FreeDefinedCurrency8
// else _Currency.CompanyCodeCurrency
// end
// as DisplayCurrency,
//_GLAccountInChartOfAccounts.GLAccountGroup as GLAccountGroup,
//_GLAccountInCompanyCode.AccountIsMarkedForDeletion as AccountIsMarkedForDeletion,
//'1' as GLAccountTransactionType,
//@DefaultAggregation: #SUM
//@Semantics: { amount : {currencyCode: 'DisplayCurrency'} }
//cast(0 as abap.curr( 23, 2 ))
//as EndingBalAmtInDspCrcy,
//@DefaultAggregation: #SUM
//@Semantics: { amount : {currencyCode: 'DisplayCurrency'} }
//cast(0 as abap.curr( 23, 2 ))
//as DebitAmountInDisplayCrcy,
//@DefaultAggregation: #SUM
//@Semantics: { amount : {currencyCode: 'DisplayCurrency'} }
//cast(0 as abap.curr( 23, 2 ))
//as CreditAmountInDisplayCrcy
//}
//where a2_GLAccountLineItem.CompanyCode = $parameters.P_CompanyCode
// and a2_GLAccountLineItem.FiscalYear = $parameters.P_FiscalYear
// and a2_GLAccountLineItem.Ledger = $parameters.P_Ledger
// and a2_GLAccountLineItem.FiscalPeriod <= $parameters.P_FiscalPeriod
//
//union all
////part3, man-made zero balance (amount is 0) in periods for GL accounts which has no posting
//select from
//I_FiscalYearPeriodForCmpnyCode as a4_FiscalYearPeriod
//inner join I_GLAccount as a5_GLAccount
//on a4_FiscalYearPeriod.CompanyCode = a5_GLAccount.CompanyCode
//association [0..1] to I_LedgerCompanyCodeCrcyRoleVH as _Currency
//on _Currency.CompanyCode = $parameters.P_CompanyCode and _Currency.Ledger = $parameters.P_Ledger
//{
//key $parameters.P_CompanyCode as CompanyCode,
//key $parameters.P_FiscalYear as FiscalYear,
//key $parameters.P_Ledger as Ledger,
//a4_FiscalYearPeriod.FiscalPeriod,
//GLAccount,
//cast('##########' as prctr) as ProfitCenter,
//cast('' as setnamenew) as ProfitCenterGroup, //dummy field for filter
//cast('####' as gsber) as BusinessArea,
//cast('################' as fkber) as FunctionalArea,
//cast('##########' as fb_segment) as Segment,
//AlternativeGLAccount,
//case $parameters.P_DisplayAltvAcct
//when 'X' then cast(AlternativeGLAccount as figlcn_glaccountinfo)
//else cast(GLAccount as figlcn_glaccountinfo)
//end as GLAccountInfo,
//cast('' as figlcn_desc) as GLAcctLineDescription,
//GLAccount as GLAccountHierarchy,
//$parameters.P_FiscalYear as LedgerFiscalYear,
//'00000000000' as FiscalPeriodDate,
//@Semantics.currencyCode:true
// case $parameters.P_CurrencyRole
// when _Currency.CompanyCodeCurrencyRole then _Currency.CompanyCodeCurrency
// when _Currency.GlobalCurrencyRole then _Currency.GlobalCurrency
// when _Currency.FreeDefinedCurrency1Role then _Currency.FreeDefinedCurrency1
// when _Currency.FreeDefinedCurrency2Role then _Currency.FreeDefinedCurrency2
// when _Currency.FreeDefinedCurrency3Role then _Currency.FreeDefinedCurrency3
// when _Currency.FreeDefinedCurrency4Role then _Currency.FreeDefinedCurrency4
// when _Currency.FreeDefinedCurrency5Role then _Currency.FreeDefinedCurrency5
// when _Currency.FreeDefinedCurrency6Role then _Currency.FreeDefinedCurrency6
// when _Currency.FreeDefinedCurrency7Role then _Currency.FreeDefinedCurrency7
// when _Currency.FreeDefinedCurrency8Role then _Currency.FreeDefinedCurrency8
// else _Currency.CompanyCodeCurrency
// end as DisplayCurrency,
//GLAccountGroup,
//_GLAccountInCompanyCode.AccountIsMarkedForDeletion,
//'2' as GLAccountTransactionType,
//@DefaultAggregation: #SUM
//@Semantics: { amount : {currencyCode: 'DisplayCurrency'} }
//cast(0 as abap.curr( 23, 2 ))
//as EndingBalAmtInDspCrcy,
//@DefaultAggregation: #SUM
//@Semantics: { amount : {currencyCode: 'DisplayCurrency'} }
//cast(0 as abap.curr( 23, 2 ))
//as DebitAmountInDisplayCrcy,
//@DefaultAggregation: #SUM
//@Semantics: { amount : {currencyCode: 'DisplayCurrency'} }
//cast(0 as abap.curr( 23, 2 ))
//as CreditAmountInDisplayCrcy
//}
//where a4_FiscalYearPeriod.CompanyCode = $parameters.P_CompanyCode
// and a4_FiscalYearPeriod.FiscalYear = $parameters.P_FiscalYear
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_GLACCOUNTINCHARTOFACCOUNTS",
"I_GLACCOUNTINCOMPANYCODE",
"I_GLACCTBALANCE",
"I_LEDGERCOMPANYCODECRCYROLEVH"
],
"ASSOCIATED":
[
"I_LEDGERCOMPANYCODECRCYROLEVH"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/