@AbapCatalog: {
buffering.status: #NOT_ALLOWED,
compiler.compareFilter: true,
sqlViewName: 'PSRCO1001JEOPLVW'
}
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType: {
dataClass: #MIXED,
serviceQuality: #P,
sizeCategory: #XXL
}
@VDM: {
private: true,
viewType: #CONSUMPTION
}
define view P_CO_DIANFrmt1001JrnlEntrOplVw
with parameters
P_Ledger : fins_ledger,
P_FromPostingDate : fis_budat_from,
P_ToPostingDate : fis_budat_to
/* The GROUP BY is used to ensure a single item.
Some customers use document splitting in new general ledger and as result in the ACDOCA table, two lines are stored for the same item, but splitted by profit or cost centers.
*/
as select from I_JournalEntryOperationalView as OperationalView
inner join I_StRpJournalEntryHeaderLog as ReportedItemLog on ReportedItemLog.CompanyCode = OperationalView.CompanyCode
and ReportedItemLog.AccountingDocument = OperationalView.AccountingDocument
and ReportedItemLog.FiscalYear = OperationalView.FiscalYear
inner join P_CO_DIANFrmt1001RefDocType as ReferenceDocumentType on ReferenceDocumentType.ReferenceDocumentType = OperationalView.ReferenceDocumentType
association [0..1] to I_Currency as _ReportingCurrency on _ReportingCurrency.Currency = $projection.ReportingCurrency
{
key ReportedItemLog.StatryRptgEntity as StatryRptgEntity,
key ReportedItemLog.StatryRptCategory as StatryRptCategory,
key ReportedItemLog.StatryRptRunID as StatryRptRunID,
key OperationalView.Ledger as Ledger,
key OperationalView.CompanyCode as CompanyCode,
key cast( OperationalView.FiscalYear as fis_gjahr_no_conv preserving type ) as FiscalYear,
key OperationalView.AccountingDocument as AccountingDocument,
key OperationalView.AccountingDocumentItem as AccountingDocumentItem,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'ReportingCurrency'} }
key cast( case
when OperationalView._OperationalAcctgDocItem.TransactionCurrency = 'COP' then OperationalView._OperationalAcctgDocItem.OriginalTaxBaseAmount
when OperationalView._OperationalAcctgDocItem.CompanyCodeCurrency = 'COP' then OperationalView._OperationalAcctgDocItem.OriglTaxBaseAmountInCoCodeCrcy
when OperationalView._OperationalAcctgDocItem.AdditionalCurrency1 = 'COP' then OperationalView._OperationalAcctgDocItem.OriginalTaxBaseAmtInAddlCrcy1
when OperationalView._OperationalAcctgDocItem.AdditionalCurrency2 = 'COP' then OperationalView._OperationalAcctgDocItem.OriginalTaxBaseAmtInAddlCrcy2
else cast( 0 as abap.curr( 23, 2 ) )
end as fis_ksl preserving type ) as TaxBaseAmount,
@DefaultAggregation: #SUM
@Semantics: { amount : {currencyCode: 'ReportingCurrency'} }
key cast( case
when OperationalView.CompanyCodeCurrency = 'COP' then OperationalView.AmountInCompanyCodeCurrency
when OperationalView.TransactionCurrency = 'COP' then OperationalView.AmountInTransactionCurrency
when OperationalView.GlobalCurrency = 'COP' then OperationalView.AmountInGlobalCurrency
when OperationalView.FreeDefinedCurrency1 = 'COP' then OperationalView.AmountInFreeDefinedCurrency1
when OperationalView.FreeDefinedCurrency2 = 'COP' then OperationalView.AmountInFreeDefinedCurrency2
when OperationalView.FreeDefinedCurrency3 = 'COP' then OperationalView.AmountInFreeDefinedCurrency3
when OperationalView.FreeDefinedCurrency4 = 'COP' then OperationalView.AmountInFreeDefinedCurrency4
when OperationalView.FreeDefinedCurrency5 = 'COP' then OperationalView.AmountInFreeDefinedCurrency5
when OperationalView.FreeDefinedCurrency6 = 'COP' then OperationalView.AmountInFreeDefinedCurrency6
when OperationalView.FreeDefinedCurrency7 = 'COP' then OperationalView.AmountInFreeDefinedCurrency7
when OperationalView.FreeDefinedCurrency8 = 'COP' then OperationalView.AmountInFreeDefinedCurrency8
else cast( 0 as abap.curr( 23, 2 ) )
end as fis_ksl preserving type ) as AmountInReportingCurrency,
OperationalView.AccountingDocumentType as AccountingDocumentType,
OperationalView.TaxCode as TaxCode,
OperationalView.TransactionTypeDetermination as TransactionTypeDetermination,
OperationalView.ChartOfAccounts as ChartOfAccounts,
OperationalView.GLAccount as GLAccount,
OperationalView.CountryChartOfAccounts as CountryChartOfAccounts,
OperationalView.AlternativeGLAccount as AlternativeGLAccount,
OperationalView.PostingDate as PostingDate,
OperationalView.FinancialAccountType as FinancialAccountType,
OperationalView.PurchasingDocument as PurchasingDocument,
OperationalView.PurchasingDocumentItem as PurchasingDocumentItem,
OperationalView.AccountAssignmentNumber as AccountAssignmentNumber,
OperationalView.ReferenceDocumentType as ReferenceDocumentType,
OperationalView.ReferenceDocumentContext as ReferenceDocumentFiscalYear,
OperationalView.ReferenceDocument as ReferenceDocument,
right( OperationalView.ReferenceDocumentItem, 4 ) as ReferenceDocumentItem,
OperationalView._CompanyCode.Country as CompanyCodeCountry,
OperationalView.Supplier as Supplier,
OperationalView.Customer as Customer,
OperationalView.OffsettingAccountType as OffsettingAccountType,
OperationalView.OffsettingAccount as OffsettingAccount,
/*+[hideWarning] { "IDS" : [ "DOUBLE_JOIN" ] } */
OperationalView._OperationalAcctgDocItem.OplAcctgDocItmCntrySpcfcRef1 as OplAcctgDocItmCntrySpcfcRef1,
OperationalView.Reference1IDByBusinessPartner as Reference1IDByBusinessPartner,
OperationalView.Reference3IDByBusinessPartner as Reference3IDByBusinessPartner,
OperationalView.BusinessPlace as BusinessPlace,
OperationalView.Product as Product,
OperationalView.ValuationArea as ValuationArea,
OperationalView.InventoryValuationType as InventoryValuationType,
@ObjectModel.foreignKey.association: '_ReportingCurrency'
@Semantics.currencyCode: true
cast( 'COP' as ficodian_reporting_currency ) as ReportingCurrency,
//Associations
OperationalView._AccountingDocumentType as _AccountingDocumentType,
OperationalView._AlternativeGLAccount as _AlternativeGLAccount,
OperationalView._ChartOfAccounts as _ChartOfAccounts,
OperationalView._CompanyCode as _CompanyCode,
OperationalView._CompanyCode._Country as _Country,
OperationalView._CountryChartOfAccounts as _CountryChartOfAccounts,
OperationalView._Customer as _Customer,
OperationalView._FinancialAccountType as _FinancialAccountType,
OperationalView._FiscalYear as _FiscalYear,
OperationalView._GLAccountInChartOfAccounts as _GLAccountInChartOfAccounts,
OperationalView._InventoryValuationType as _InventoryValuationType,
OperationalView._JournalEntry as _JournalEntry,
OperationalView._Ledger as _Ledger,
OperationalView._Product as _Product,
OperationalView._OffsettingAccountType as _OffsettingAccountType,
OperationalView._OffsettingAccount as _OffsettingAccountWithBP,
OperationalView._PurchasingDocument as _PurchasingDocument,
OperationalView._PurchasingDocumentItem as _PurchasingDocumentItem,
OperationalView._PurReqValuationArea as _PurReqValuationArea,
OperationalView._ReferenceDocumentType as _ReferenceDocumentType,
OperationalView._Supplier as _Supplier,
_ReportingCurrency
}
where
OperationalView.Ledger = $parameters.P_Ledger
and OperationalView.PostingDate between $parameters.P_FromPostingDate and $parameters.P_ToPostingDate
/*
Only expense accounts are required
*/
and(
OperationalView.FinancialAccountType = 'A' -- Asset
or OperationalView.FinancialAccountType = 'M' -- Material
or OperationalView.FinancialAccountType = 'S' -- G/L Account
)
/*
This is an exception: material posting with no vendors should not be retrieved.
They are just internal movements between warehouses or inventory to assets.
*/
and not(
(
OperationalView.FinancialAccountType = 'M' -- Material
or OperationalView.FinancialAccountType = 'A' -- Asset
)
and(
(
OperationalView.Supplier = ''
or OperationalView.Supplier is null
)
and OperationalView.OffsettingAccountType <> 'K' -- Vendor
)
and(
OperationalView.ReferenceDocumentType = 'MKPF' -- Material document
or OperationalView.ReferenceDocumentType = 'RMRP' -- Invoice receipt
)
)
group by
ReportedItemLog.StatryRptgEntity,
ReportedItemLog.StatryRptCategory,
ReportedItemLog.StatryRptRunID,
OperationalView.Ledger,
OperationalView.CompanyCode,
OperationalView.AccountingDocument,
OperationalView.AccountingDocumentItem,
OperationalView.AccountingDocumentType,
OperationalView.TaxCode,
OperationalView.ReferenceDocumentItem,
OperationalView.FiscalYear,
OperationalView.TransactionTypeDetermination,
OperationalView.ChartOfAccounts,
OperationalView.GLAccount,
OperationalView.CountryChartOfAccounts,
OperationalView.AlternativeGLAccount,
OperationalView.PostingDate,
OperationalView.FinancialAccountType,
OperationalView.PurchasingDocument,
OperationalView.PurchasingDocumentItem,
OperationalView.AccountAssignmentNumber,
OperationalView.ReferenceDocumentType,
OperationalView.ReferenceDocumentContext,
OperationalView.ReferenceDocument,
OperationalView._CompanyCode.Country,
OperationalView.Supplier,
OperationalView.Customer,
OperationalView.OffsettingAccountType,
OperationalView.OffsettingAccount,
OperationalView._OperationalAcctgDocItem.OplAcctgDocItmCntrySpcfcRef1,
OperationalView.Reference1IDByBusinessPartner,
OperationalView.Reference3IDByBusinessPartner,
OperationalView.BusinessPlace,
OperationalView.Product,
OperationalView.ValuationArea,
OperationalView.InventoryValuationType,
OperationalView._OperationalAcctgDocItem.TransactionCurrency,
OperationalView._OperationalAcctgDocItem.CompanyCodeCurrency,
OperationalView._OperationalAcctgDocItem.AdditionalCurrency1,
OperationalView._OperationalAcctgDocItem.AdditionalCurrency2,
OperationalView.CompanyCodeCurrency,
OperationalView.TransactionCurrency,
OperationalView.GlobalCurrency,
OperationalView.FreeDefinedCurrency1,
OperationalView.FreeDefinedCurrency2,
OperationalView.FreeDefinedCurrency3,
OperationalView.FreeDefinedCurrency4,
OperationalView.FreeDefinedCurrency5,
OperationalView.FreeDefinedCurrency6,
OperationalView.FreeDefinedCurrency7,
OperationalView.FreeDefinedCurrency8,
OperationalView.AmountInCompanyCodeCurrency,
OperationalView.AmountInGlobalCurrency,
OperationalView.AmountInFreeDefinedCurrency1,
OperationalView.AmountInFreeDefinedCurrency2,
OperationalView.AmountInFreeDefinedCurrency3,
OperationalView.AmountInFreeDefinedCurrency4,
OperationalView.AmountInFreeDefinedCurrency5,
OperationalView.AmountInFreeDefinedCurrency6,
OperationalView.AmountInFreeDefinedCurrency7,
OperationalView.AmountInFreeDefinedCurrency8,
OperationalView._OperationalAcctgDocItem.OriginalTaxBaseAmount,
OperationalView._OperationalAcctgDocItem.OriglTaxBaseAmountInCoCodeCrcy,
OperationalView._OperationalAcctgDocItem.OriginalTaxBaseAmtInAddlCrcy1,
OperationalView._OperationalAcctgDocItem.OriginalTaxBaseAmtInAddlCrcy2,
OperationalView.AmountInTransactionCurrency
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_COMPANYCODE",
"I_JOURNALENTRYOPERATIONALVIEW",
"I_OPERATIONALACCTGDOCITEM",
"I_STRPJOURNALENTRYHEADERLOG",
"P_CO_DIANFRMT1001REFDOCTYPE"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENTTYPE",
"I_CHARTOFACCOUNTS",
"I_COMPANYCODE",
"I_COUNTRY",
"I_CURRENCY",
"I_CUSTOMER",
"I_FINANCIALACCOUNTTYPE",
"I_FISCALYEARFORCOMPANYCODE",
"I_GLACCOUNTINCHARTOFACCOUNTS",
"I_INVENTORYVALUATIONTYPE",
"I_JOURNALENTRY",
"I_LEDGER",
"I_PRODUCT",
"I_PURCHASINGDOCUMENT",
"I_PURCHASINGDOCUMENTITEM",
"I_PURREQVALUATIONAREA",
"I_REFERENCEDOCUMENTTYPE",
"I_SUPPLIER"
],
"BASE":
[
"I_COMPANYCODE",
"I_JOURNALENTRYOPERATIONALVIEW"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/