@AbapCatalog.sqlViewName: 'CSAFTNOGLACCBAL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey:true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@VDM.viewType: #CONSUMPTION
@Consumption.dbHints: ['NO_CS_PROJECT']
@EndUserText.label: 'SAF-T Norway GL Accounts Balance'
define view C_NO_SAFTGenLedgerAcctBalance
with parameters
P_FromPostingDate : abap.dats,
P_ToPostingDate : abap.dats,
P_Ledger : fins_ledger,
P_FiscalYear : gjahr
as select from I_GLAccount as Account
left outer join I_SAFTAccountBalance( P_FromPostingDate: $parameters.P_FromPostingDate,
P_ToPostingDate: $parameters.P_ToPostingDate,
P_FiscalYear: $parameters.P_FiscalYear) as Balance on Balance.Ledger = $parameters.P_Ledger
and Balance.CompanyCode = Account.CompanyCode
and Balance.ChartOfAccounts = Account.ChartOfAccounts
and Balance.GLAccount = Account.GLAccount
left outer join I_NO_SAFTGrpgAccountMapping as GrpgAccountMap on Account.CompanyCode = GrpgAccountMap.CompanyCode
and Account.ChartOfAccounts = GrpgAccountMap.ChartOfAccounts
and Account.GLAccount = GrpgAccountMap.GLAccount
and GrpgAccountMap.StartDate <= $parameters.P_ToPostingDate
and GrpgAccountMap.EndDate >= $parameters.P_FromPostingDate
left outer join I_NO_SAFTAccountMapping as AccountMap on Account.ChartOfAccounts = AccountMap.ChartOfAccounts
and Account.GLAccount = AccountMap.GLAccount
and AccountMap.StartDate <= $parameters.P_ToPostingDate
and AccountMap.EndDate >= $parameters.P_FromPostingDate
left outer join P_NO_SAFTAccountMappingCount(P_StartDate: $parameters.P_FromPostingDate ,
P_EndDate: $parameters.P_ToPostingDate ) as AcctMapCount on Account.ChartOfAccounts = AcctMapCount.ChartOfAccounts
left outer join P_NO_SAFTGrpgAcctMappingCount(P_StartDate: $parameters.P_FromPostingDate ,
P_EndDate: $parameters.P_ToPostingDate ) as GrpAcctMapCount on Account.CompanyCode = GrpAcctMapCount.CompanyCode
and Account.ChartOfAccounts = GrpAcctMapCount.ChartOfAccounts
{
key Account.CompanyCode,
key Account.ChartOfAccounts,
key Account.GLAccount,
coalesce(Account._GLAccountInCompanyCode._Text[1: Language = 'O'].GLAccountLongName,
coalesce(Account._GLAccountInCompanyCode._Text[1: Language = 'E'].GLAccountLongName,
coalesce(Account._GLAccountInCompanyCode._Text[1: Language = $session.system_language ].GLAccountLongName,'NA'))) as GLAccountLongName,
Account._GLAccountInCompanyCode.CreationDate,
case when GrpAcctMapCount.QtdOfRecords > 0
then ''
else
case when AcctMapCount.QtdOfRecords > 0 and ( AccountMap.NO_SAFTStandardAccount is null or AccountMap.NO_SAFTStandardAccount is initial or AccountMap.NO_SAFTStandardAccount = '0000' )
then 'NA'
else AccountMap.NO_SAFTStandardAccount
end
end as NO_SAFTStandardAccount,
case when GrpAcctMapCount.QtdOfRecords > 0 and ( GrpgAccountMap.NO_SAFTAccountGroupCategory is null or GrpgAccountMap.NO_SAFTAccountGroupCategory is initial )
then 'NA'
else GrpgAccountMap.NO_SAFTAccountGroupCategory
end as NO_SAFTAccountGroupCategory,
case when GrpAcctMapCount.QtdOfRecords > 0 and ( GrpgAccountMap.NO_SAFTAccountGroup is null or GrpgAccountMap.NO_SAFTAccountGroup is initial )
then 'NA'
else GrpgAccountMap.NO_SAFTAccountGroup
end as NO_SAFTAccountGroup,
@Semantics.currencyCode:true
Account._CompanyCode.Currency as CompanyCodeCurrency,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when Balance.StartingBalanceAmtInCoCodeCrcy >= 0
then Balance.StartingBalanceAmtInCoCodeCrcy
else 0
end as DebitStartingBalAmtInCCCrcy,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when Balance.StartingBalanceAmtInCoCodeCrcy < 0
then abs( Balance.StartingBalanceAmtInCoCodeCrcy )
else 0
end as CreditStartingBalAmtInCCCrcy,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when Balance.EndingBalanceAmtInCoCodeCrcy >= 0
then Balance.EndingBalanceAmtInCoCodeCrcy
else 0
end as DebitEndingBalAmtInCCCrcy,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when Balance.EndingBalanceAmtInCoCodeCrcy < 0
then abs( Balance.EndingBalanceAmtInCoCodeCrcy )
else 0
end as CreditEndingBalAmtInCCCrcy,
'X' as ItemIsReportingRelevant
}
where
Account._GLAccountInChartOfAccounts.GLAccountType <> 'S'
and Account._CompanyCode.Country = 'NO'
and ( Account._CompanyCode.Currency = Balance.CompanyCodeCurrency
or Balance.CompanyCodeCurrency is null )
//The first select brings all the GL accounts of a Norwegian company
union
//The second select brings all the GL accounts of a non-Norwegian company, used in transactions in the selected period, with TaxCountry = 'NO'
select from I_GLAccount as Account
inner join P_NO_SAFTJOURNALITEMGLACCTS( P_FromPostingDate: $parameters.P_FromPostingDate,
P_ToPostingDate: $parameters.P_ToPostingDate ) as ReportAcct on ReportAcct.CompanyCode = Account.CompanyCode
and ReportAcct.GLAccount = Account.GLAccount
and ReportAcct.FiscalYear = $parameters.P_FiscalYear
and ReportAcct.Ledger = $parameters.P_Ledger
left outer join I_SAFTAccountBalance( P_FromPostingDate: $parameters.P_FromPostingDate,
P_ToPostingDate: $parameters.P_ToPostingDate,
P_FiscalYear: $parameters.P_FiscalYear) as Balance on Balance.Ledger = $parameters.P_Ledger
and Balance.CompanyCode = Account.CompanyCode
and Balance.ChartOfAccounts = Account.ChartOfAccounts
and Balance.GLAccount = Account.GLAccount
left outer join I_NO_SAFTGrpgAccountMapping as GrpgAccountMap on Account.CompanyCode = GrpgAccountMap.CompanyCode
and Account.ChartOfAccounts = GrpgAccountMap.ChartOfAccounts
and Account.GLAccount = GrpgAccountMap.GLAccount
and GrpgAccountMap.StartDate <= $parameters.P_ToPostingDate
and GrpgAccountMap.EndDate >= $parameters.P_FromPostingDate
left outer join I_NO_SAFTAccountMapping as AccountMap on Account.ChartOfAccounts = AccountMap.ChartOfAccounts
and Account.GLAccount = AccountMap.GLAccount
and AccountMap.StartDate <= $parameters.P_ToPostingDate
and AccountMap.EndDate >= $parameters.P_FromPostingDate
left outer join P_NO_SAFTAccountMappingCount(P_StartDate: $parameters.P_FromPostingDate ,
P_EndDate: $parameters.P_ToPostingDate ) as AcctMapCount on Account.ChartOfAccounts = AcctMapCount.ChartOfAccounts
left outer join P_NO_SAFTGrpgAcctMappingCount(P_StartDate: $parameters.P_FromPostingDate ,
P_EndDate: $parameters.P_ToPostingDate ) as GrpAcctMapCount on Account.CompanyCode = GrpAcctMapCount.CompanyCode
and Account.ChartOfAccounts = GrpAcctMapCount.ChartOfAccounts
{
key Account.CompanyCode,
key Account.ChartOfAccounts,
key Account.GLAccount,
coalesce(Account._GLAccountInCompanyCode._Text[1: Language = 'O'].GLAccountLongName,
coalesce(Account._GLAccountInCompanyCode._Text[1: Language = 'E'].GLAccountLongName,
coalesce(Account._GLAccountInCompanyCode._Text[1: Language = $session.system_language ].GLAccountLongName,'NA'))) as GLAccountLongName,
Account._GLAccountInCompanyCode.CreationDate,
case when GrpAcctMapCount.QtdOfRecords > 0
then ''
else
case when AcctMapCount.QtdOfRecords > 0 and ( AccountMap.NO_SAFTStandardAccount is null or AccountMap.NO_SAFTStandardAccount is initial or AccountMap.NO_SAFTStandardAccount = '0000' )
then 'NA'
else AccountMap.NO_SAFTStandardAccount
end
end as NO_SAFTStandardAccount,
case when GrpAcctMapCount.QtdOfRecords > 0 and ( GrpgAccountMap.NO_SAFTAccountGroupCategory is null or GrpgAccountMap.NO_SAFTAccountGroupCategory is initial )
then 'NA'
else GrpgAccountMap.NO_SAFTAccountGroupCategory
end as NO_SAFTAccountGroupCategory,
case when GrpAcctMapCount.QtdOfRecords > 0 and ( GrpgAccountMap.NO_SAFTAccountGroup is null or GrpgAccountMap.NO_SAFTAccountGroup is initial )
then 'NA'
else GrpgAccountMap.NO_SAFTAccountGroup
end as NO_SAFTAccountGroup,
@Semantics.currencyCode:true
Account._CompanyCode.Currency as CompanyCodeCurrency,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when Balance.StartingBalanceAmtInCoCodeCrcy >= 0
then Balance.StartingBalanceAmtInCoCodeCrcy
else 0
end as DebitStartingBalAmtInCCCrcy,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when Balance.StartingBalanceAmtInCoCodeCrcy < 0
then abs( Balance.StartingBalanceAmtInCoCodeCrcy )
else 0
end as CreditStartingBalAmtInCCCrcy,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when Balance.EndingBalanceAmtInCoCodeCrcy >= 0
then Balance.EndingBalanceAmtInCoCodeCrcy
else 0
end as DebitEndingBalAmtInCCCrcy,
@Semantics: { amount : {currencyCode: 'CompanyCodeCurrency'} }
case when Balance.EndingBalanceAmtInCoCodeCrcy < 0
then abs( Balance.EndingBalanceAmtInCoCodeCrcy )
else 0
end as CreditEndingBalAmtInCCCrcy,
'X' as ItemIsReportingRelevant
}
where
Account._GLAccountInChartOfAccounts.GLAccountType <> 'S'
and Account._CompanyCode.Currency = Balance.CompanyCodeCurrency
and Account._CompanyCode.Country <> 'NO'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_COMPANYCODE",
"I_GLACCOUNT",
"I_GLACCOUNTINCHARTOFACCOUNTS",
"I_GLACCOUNTINCOMPANYCODE",
"I_GLACCOUNTTEXTINCOMPANYCODE",
"I_NO_SAFTACCOUNTMAPPING",
"I_NO_SAFTGRPGACCOUNTMAPPING",
"I_SAFTACCOUNTBALANCE",
"P_NO_SAFTACCOUNTMAPPINGCOUNT",
"P_NO_SAFTGRPGACCTMAPPINGCOUNT",
"P_NO_SAFTJOURNALITEMGLACCTS"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/