@AbapCatalog.sqlViewName: 'PGLACCTBAL01'
@AbapCatalog.compiler.compareFilter: true
@VDM.private:true
@VDM.viewType: #COMPOSITE
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
define view P_GLAcctBalance01 with parameters
P_Ledger : fis_rldnr,
P_StartPostingDate : fis_budat,
P_EndPostingDate : fis_budat
as select from I_GLAccountLineItem as GLAccountLineItem
inner join I_LedgerCompanyCodeCrcyRoles as LedgerCompanyCodeCrcyRole
on LedgerCompanyCodeCrcyRole.CompanyCode = GLAccountLineItem.CompanyCode
and LedgerCompanyCodeCrcyRole.Ledger = GLAccountLineItem.Ledger
inner join I_FiscalCalYearPeriodForLedger as FiscalPeriodForLedger
// on GLAccountLineItem.FiscalYearVariant = FiscalPeriodForLedger.FiscalYearVariant
on GLAccountLineItem.Ledger = FiscalPeriodForLedger.Ledger
and GLAccountLineItem.CompanyCode = FiscalPeriodForLedger.CompanyCode
and GLAccountLineItem.FiscalPeriod = FiscalPeriodForLedger.FiscalPeriod
// and FiscalPeriodForLedger.FiscalPeriodStartDate <= $parameters.P_StartPostingDate
// and FiscalPeriodForLedger.FiscalPeriodEndDate >= $parameters.P_StartPostingDate
{
key GLAccountLineItem.Ledger as Ledger,
key GLAccountLineItem.CompanyCode as CompanyCode,
// key FiscalYear,
key GLAccountLineItem.FiscalYear as FiscalYear,
key AccountingDocument,
key LedgerGLLineItem,
LedgerFiscalYear,
ChartOfAccounts,
AccountingDocumentType,
GLAccount,
CountryChartOfAccounts,
AlternativeGLAccount,
ControllingArea,
CostCenter,
ProfitCenter,
FinancialTransactionType,
FunctionalArea,
BusinessArea,
Segment,
DebitCreditCode,
PostingDate,
// FiscalPeriod,
GLAccountLineItem.FiscalPeriod as FiscalPeriod,
PartnerCompany,
WBSElement,
GLAccountType,
LedgerCompanyCodeCrcyRole.CompanyCodeCurrencyRole,
LedgerCompanyCodeCrcyRole.GlobalCurrencyRole,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency1Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency2Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency3Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency4Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency5Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency6Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency7Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency8Role,
LedgerCompanyCodeCrcyRole.FunctionalCurrencyRole,
@Semantics.currencyCode:true
CompanyCodeCurrency,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
DebitAmountInCoCodeCrcy as DebitAmtInCoCodeCrcy,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
CreditAmountInCoCodeCrcy as CreditAmtInCoCodeCrcy,
@Semantics.currencyCode:true
GlobalCurrency,
@Semantics.amount.currencyCode: 'GlobalCurrency'
DebitAmountInGlobalCrcy as DebitAmtInGlobalCrcy,
@Semantics.amount.currencyCode: 'GlobalCurrency'
CreditAmountInGlobalCrcy as CreditAmtInGlobalCrcy,
@Semantics.currencyCode:true
FreeDefinedCurrency1,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency1'
DebitAmountInFreeDefinedCrcy1 as DebitAmtInFreeDefinedCrcy1,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency1'
CreditAmountInFreeDefinedCrcy1 as CreditAmtInFreeDefinedCrcy1,
@Semantics.currencyCode:true
FreeDefinedCurrency2,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency2'
DebitAmountInFreeDefinedCrcy2 as DebitAmtInFreeDefinedCrcy2,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency2'
CreditAmountInFreeDefinedCrcy2 as CreditAmtInFreeDefinedCrcy2,
@Semantics.currencyCode:true
FreeDefinedCurrency3,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency3'
DebitAmountInFreeDefinedCrcy3 as DebitAmtInFreeDefinedCrcy3,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency3'
CreditAmountInFreeDefinedCrcy3 as CreditAmtInFreeDefinedCrcy3,
@Semantics.currencyCode:true
FreeDefinedCurrency4,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency4'
DebitAmountInFreeDefinedCrcy4 as DebitAmtInFreeDefinedCrcy4,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency4'
CreditAmountInFreeDefinedCrcy4 as CreditAmtInFreeDefinedCrcy4,
@Semantics.currencyCode:true
FreeDefinedCurrency5,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency5'
DebitAmountInFreeDefinedCrcy5 as DebitAmtInFreeDefinedCrcy5,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency5'
CreditAmountInFreeDefinedCrcy5 as CreditAmtInFreeDefinedCrcy5,
@Semantics.currencyCode:true
FreeDefinedCurrency6,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency6'
DebitAmountInFreeDefinedCrcy6 as DebitAmtInFreeDefinedCrcy6,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency6'
CreditAmountInFreeDefinedCrcy6 as CreditAmtInFreeDefinedCrcy6,
@Semantics.currencyCode:true
FreeDefinedCurrency7,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency7'
DebitAmountInFreeDefinedCrcy7 as DebitAmtInFreeDefinedCrcy7,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency7'
CreditAmountInFreeDefinedCrcy7 as CreditAmtInFreeDefinedCrcy7,
@Semantics.currencyCode:true
FreeDefinedCurrency8,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency8'
DebitAmountInFreeDefinedCrcy8 as DebitAmtInFreeDefinedCrcy8,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency8'
CreditAmountInFreeDefinedCrcy8 as CreditAmtInFreeDefinedCrcy8,
@Semantics.currencyCode:true
FunctionalCurrency,
@Semantics.amount.currencyCode: 'FunctionalCurrency'
DebitAmountInFunctionalCrcy as DebitAmountInFunctionalCrcy,
@Semantics.amount.currencyCode: 'FunctionalCurrency'
CreditAmountInFunctionalCrcy as CreditAmountInFunctionalCrcy,
'R' as PeriodFlag
}
where
GLAccountLineItem.Ledger = $parameters.P_Ledger and
PostingDate >= $parameters.P_StartPostingDate and
PostingDate <= $parameters.P_EndPostingDate and
// (FiscalPeriod >= '001' or (FiscalPeriod = '000' and AccountingDocumentCategory = ''))
GLAccountLineItem.LedgerFiscalYear = FiscalPeriodForLedger.FiscalYear and
// GLAccountLineItem.FiscalPeriod >= '001'
(GLAccountLineItem.FiscalPeriod >= '001' or (GLAccountLineItem.FiscalPeriod = '000' and AccountingDocumentCategory <> 'C'))
// (AccountingDocumentCategory <> 'C')
and GLRecordType = '0'
union all
select distinct from I_GLAccountLineItem as GLAccountLineItem
inner join I_LedgerCompanyCodeCrcyRoles as LedgerCompanyCodeCrcyRole
on LedgerCompanyCodeCrcyRole.CompanyCode = GLAccountLineItem.CompanyCode
and LedgerCompanyCodeCrcyRole.Ledger = GLAccountLineItem.Ledger
// inner join I_FiscalCalendarDate as FiscalPeriodForLedger
// on GLAccountLineItem.FiscalYearVariant = FiscalPeriodForLedger.FiscalYearVariant
// and FiscalPeriodForLedger.CalendarDate = $parameters.P_StartPostingDate
inner join I_FiscalCalYearPeriodForLedger as FiscalPeriodForLedger
// on GLAccountLineItem.FiscalYearVariant = FiscalPeriodForLedger.FiscalYearVariant
on GLAccountLineItem.Ledger = FiscalPeriodForLedger.Ledger
and GLAccountLineItem.CompanyCode = FiscalPeriodForLedger.CompanyCode
and FiscalPeriodForLedger.FiscalPeriodStartDate <= $parameters.P_StartPostingDate
and FiscalPeriodForLedger.FiscalPeriodEndDate >= $parameters.P_StartPostingDate
{
key GLAccountLineItem.Ledger as Ledger,
key GLAccountLineItem.CompanyCode as CompanyCode,
key GLAccountLineItem.FiscalYear as FiscalYear,
key AccountingDocument,
key LedgerGLLineItem,
LedgerFiscalYear,
ChartOfAccounts,
AccountingDocumentType,
GLAccount,
CountryChartOfAccounts,
AlternativeGLAccount,
ControllingArea,
CostCenter,
ProfitCenter,
FinancialTransactionType,
FunctionalArea,
BusinessArea,
Segment,
DebitCreditCode,
PostingDate,
GLAccountLineItem.FiscalPeriod as FiscalPeriod,
PartnerCompany,
WBSElement,
GLAccountType,
LedgerCompanyCodeCrcyRole.CompanyCodeCurrencyRole,
LedgerCompanyCodeCrcyRole.GlobalCurrencyRole,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency1Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency2Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency3Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency4Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency5Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency6Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency7Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency8Role,
LedgerCompanyCodeCrcyRole.FunctionalCurrencyRole,
@Semantics.currencyCode:true
CompanyCodeCurrency,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
DebitAmountInCoCodeCrcy as DebitAmtInCoCodeCrcy,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
CreditAmountInCoCodeCrcy as CreditAmtInCoCodeCrcy,
@Semantics.currencyCode:true
GlobalCurrency,
@Semantics.amount.currencyCode: 'GlobalCurrency'
DebitAmountInGlobalCrcy as DebitAmtInGlobalCrcy,
@Semantics.amount.currencyCode: 'GlobalCurrency'
CreditAmountInGlobalCrcy as CreditAmtInGlobalCrcy,
@Semantics.currencyCode:true
FreeDefinedCurrency1,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency1'
DebitAmountInFreeDefinedCrcy1 as DebitAmtInFreeDefinedCrcy1,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency1'
CreditAmountInFreeDefinedCrcy1 as CreditAmtInFreeDefinedCrcy1,
@Semantics.currencyCode:true
FreeDefinedCurrency2,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency2'
DebitAmountInFreeDefinedCrcy2 as DebitAmtInFreeDefinedCrcy2,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency2'
CreditAmountInFreeDefinedCrcy2 as CreditAmtInFreeDefinedCrcy2,
@Semantics.currencyCode:true
FreeDefinedCurrency3,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency3'
DebitAmountInFreeDefinedCrcy3 as DebitAmtInFreeDefinedCrcy3,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency3'
CreditAmountInFreeDefinedCrcy3 as CreditAmtInFreeDefinedCrcy3,
@Semantics.currencyCode:true
FreeDefinedCurrency4,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency4'
DebitAmountInFreeDefinedCrcy4 as DebitAmtInFreeDefinedCrcy4,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency4'
CreditAmountInFreeDefinedCrcy4 as CreditAmtInFreeDefinedCrcy4,
@Semantics.currencyCode:true
FreeDefinedCurrency5,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency5'
DebitAmountInFreeDefinedCrcy5 as DebitAmtInFreeDefinedCrcy5,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency5'
CreditAmountInFreeDefinedCrcy5 as CreditAmtInFreeDefinedCrcy5,
@Semantics.currencyCode:true
FreeDefinedCurrency6,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency6'
DebitAmountInFreeDefinedCrcy6 as DebitAmtInFreeDefinedCrcy6,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency6'
CreditAmountInFreeDefinedCrcy6 as CreditAmtInFreeDefinedCrcy6,
@Semantics.currencyCode:true
FreeDefinedCurrency7,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency7'
DebitAmountInFreeDefinedCrcy7 as DebitAmtInFreeDefinedCrcy7,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency7'
CreditAmountInFreeDefinedCrcy7 as CreditAmtInFreeDefinedCrcy7,
@Semantics.currencyCode:true
FreeDefinedCurrency8,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency8'
DebitAmountInFreeDefinedCrcy8 as DebitAmtInFreeDefinedCrcy8,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency8'
CreditAmountInFreeDefinedCrcy8 as CreditAmtInFreeDefinedCrcy8,
@Semantics.currencyCode:true
FunctionalCurrency,
@Semantics.amount.currencyCode: 'FunctionalCurrency'
DebitAmountInFunctionalCrcy as DebitAmountInFunctionalCrcy,
@Semantics.amount.currencyCode: 'FunctionalCurrency'
CreditAmountInFunctionalCrcy as CreditAmountInFunctionalCrcy,
'Y' as PeriodFlag
}
where
GLAccountLineItem.Ledger = $parameters.P_Ledger and
GLAccountLineItem.LedgerFiscalYear = FiscalPeriodForLedger.FiscalYear and
// GLAccountLineItem.FiscalYear = FiscalPeriodForLedger.FiscalYear and
GLAccountLineItem.FiscalPeriod = '000' and
GLAccountLineItem.AccountingDocumentCategory = 'C'
and GLRecordType = '0'
union all
select from I_GLAccountLineItem as GLAccountLineItem
inner join I_LedgerCompanyCodeCrcyRoles as LedgerCompanyCodeCrcyRole
on LedgerCompanyCodeCrcyRole.CompanyCode = GLAccountLineItem.CompanyCode
and LedgerCompanyCodeCrcyRole.Ledger = GLAccountLineItem.Ledger
// inner join I_FiscalCalendarDate as FiscalPeriodForLedger
// on GLAccountLineItem.FiscalYearVariant = FiscalPeriodForLedger.FiscalYearVariant
// and FiscalPeriodForLedger.CalendarDate = $parameters.P_StartPostingDate
inner join I_FiscalCalYearPeriodForLedger as FiscalPeriodForLedger
// on GLAccountLineItem.FiscalYearVariant = FiscalPeriodForLedger.FiscalYearVariant
on GLAccountLineItem.Ledger = FiscalPeriodForLedger.Ledger
and GLAccountLineItem.CompanyCode = FiscalPeriodForLedger.CompanyCode
and GLAccountLineItem.FiscalPeriod = FiscalPeriodForLedger.FiscalPeriod
and FiscalPeriodForLedger.FiscalYearStartDate <= $parameters.P_StartPostingDate
and FiscalPeriodForLedger.FiscalYearEndDate >= $parameters.P_StartPostingDate
// and FiscalPeriodForLedger.FiscalPeriodStartDate <= $parameters.P_StartPostingDate
// and FiscalPeriodForLedger.FiscalPeriodEndDate >= $parameters.P_StartPostingDate
{
key GLAccountLineItem.Ledger as Ledger,
key GLAccountLineItem.CompanyCode as CompanyCode,
key GLAccountLineItem.FiscalYear as FiscalYear,
key AccountingDocument,
key LedgerGLLineItem,
LedgerFiscalYear,
ChartOfAccounts,
AccountingDocumentType,
GLAccount,
CountryChartOfAccounts,
AlternativeGLAccount,
ControllingArea,
CostCenter,
ProfitCenter,
FinancialTransactionType,
FunctionalArea,
BusinessArea,
Segment,
DebitCreditCode,
PostingDate,
GLAccountLineItem.FiscalPeriod as FiscalPeriod,
PartnerCompany,
WBSElement,
GLAccountType,
LedgerCompanyCodeCrcyRole.CompanyCodeCurrencyRole,
LedgerCompanyCodeCrcyRole.GlobalCurrencyRole,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency1Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency2Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency3Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency4Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency5Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency6Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency7Role,
LedgerCompanyCodeCrcyRole.FreeDefinedCurrency8Role,
LedgerCompanyCodeCrcyRole.FunctionalCurrencyRole,
@Semantics.currencyCode:true
CompanyCodeCurrency,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
DebitAmountInCoCodeCrcy as DebitAmtInCoCodeCrcy,
@Semantics.amount.currencyCode: 'CompanyCodeCurrency'
CreditAmountInCoCodeCrcy as CreditAmtInCoCodeCrcy,
@Semantics.currencyCode:true
GlobalCurrency,
@Semantics.amount.currencyCode: 'GlobalCurrency'
DebitAmountInGlobalCrcy as DebitAmtInGlobalCrcy,
@Semantics.amount.currencyCode: 'GlobalCurrency'
CreditAmountInGlobalCrcy as CreditAmtInGlobalCrcy,
@Semantics.currencyCode:true
FreeDefinedCurrency1,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency1'
DebitAmountInFreeDefinedCrcy1 as DebitAmtInFreeDefinedCrcy1,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency1'
CreditAmountInFreeDefinedCrcy1 as CreditAmtInFreeDefinedCrcy1,
@Semantics.currencyCode:true
FreeDefinedCurrency2,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency2'
DebitAmountInFreeDefinedCrcy2 as DebitAmtInFreeDefinedCrcy2,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency2'
CreditAmountInFreeDefinedCrcy2 as CreditAmtInFreeDefinedCrcy2,
@Semantics.currencyCode:true
FreeDefinedCurrency3,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency3'
DebitAmountInFreeDefinedCrcy3 as DebitAmtInFreeDefinedCrcy3,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency3'
CreditAmountInFreeDefinedCrcy3 as CreditAmtInFreeDefinedCrcy3,
@Semantics.currencyCode:true
FreeDefinedCurrency4,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency4'
DebitAmountInFreeDefinedCrcy4 as DebitAmtInFreeDefinedCrcy4,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency4'
CreditAmountInFreeDefinedCrcy4 as CreditAmtInFreeDefinedCrcy4,
@Semantics.currencyCode:true
FreeDefinedCurrency5,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency5'
DebitAmountInFreeDefinedCrcy5 as DebitAmtInFreeDefinedCrcy5,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency5'
CreditAmountInFreeDefinedCrcy5 as CreditAmtInFreeDefinedCrcy5,
@Semantics.currencyCode:true
FreeDefinedCurrency6,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency6'
DebitAmountInFreeDefinedCrcy6 as DebitAmtInFreeDefinedCrcy6,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency6'
CreditAmountInFreeDefinedCrcy6 as CreditAmtInFreeDefinedCrcy6,
@Semantics.currencyCode:true
FreeDefinedCurrency7,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency7'
DebitAmountInFreeDefinedCrcy7 as DebitAmtInFreeDefinedCrcy7,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency7'
CreditAmountInFreeDefinedCrcy7 as CreditAmtInFreeDefinedCrcy7,
@Semantics.currencyCode:true
FreeDefinedCurrency8,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency8'
DebitAmountInFreeDefinedCrcy8 as DebitAmtInFreeDefinedCrcy8,
@Semantics.amount.currencyCode: 'FreeDefinedCurrency8'
CreditAmountInFreeDefinedCrcy8 as CreditAmtInFreeDefinedCrcy8,
@Semantics.currencyCode:true
FunctionalCurrency,
@Semantics.amount.currencyCode: 'FunctionalCurrency'
DebitAmountInFunctionalCrcy as DebitAmountInFunctionalCrcy,
@Semantics.amount.currencyCode: 'FunctionalCurrency'
CreditAmountInFunctionalCrcy as CreditAmountInFunctionalCrcy,
'O' as PeriodFlag
}
where
GLAccountLineItem.Ledger = $parameters.P_Ledger and
// GLAccountLineItem.FiscalYear = FiscalPeriodForLedger.FiscalYear and
GLAccountLineItem.LedgerFiscalYear = FiscalPeriodForLedger.FiscalYear and
GLAccountLineItem.FiscalPeriod >= '001' and
GLAccountLineItem.PostingDate <= dats_add_days($parameters.P_StartPostingDate, -1, 'NULL')
and GLRecordType = '0'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_FISCALCALYEARPERIODFORLEDGER",
"I_GLACCOUNTLINEITEM",
"I_LEDGERCOMPANYCODECRCYROLES"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/