@AbapCatalog.sqlViewName: 'CPESLSLC'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #MANDATORY
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #MIXED
@VDM.viewType: #CONSUMPTION
@Analytics: { dataCategory: #CUBE }
@Analytics.internalName: #LOCAL
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@EndUserText.label: 'Sales Ledger Peru - Cube'
@Metadata.allowExtensions:true
@Consumption.dbHints: ['USE_HEX_PLAN']
define view C_PE_StRpSalesLedgerCube
with parameters
P_ReportingCurrency : glo_reporting_currency,
P_TaxConversionIsNotEnabled : fipe_deactivate_currency_conv
as select from P_PE_SalesTaxAggrgd(
P_ReportingCurrency: $parameters.P_ReportingCurrency,
P_TaxConversionIsNotEnabled: $parameters.P_TaxConversionIsNotEnabled ) as SalesTaxEntry
inner join I_CompanyCode as CompanyCode on SalesTaxEntry.CompanyCode = CompanyCode.CompanyCode
inner join I_Country as Country on Country.Country = 'PE'
inner join I_AddlCompanyCodeInformation as CompanyCodeInfo on CompanyCodeInfo.CompanyCode = SalesTaxEntry.CompanyCode
and CompanyCodeInfo.CompanyCodeParameterType = 'TAXNR'
left outer to one join I_JournalEntryOneTimeAccount as OneTimeAccountBP on OneTimeAccountBP.CompanyCode = SalesTaxEntry.CompanyCode
and OneTimeAccountBP.AccountingDocument = SalesTaxEntry.AccountingDocument
and OneTimeAccountBP.FiscalYear = SalesTaxEntry.FiscalYear
left outer to one join I_Customer as Customer on Customer.Customer = SalesTaxEntry.Customer
left outer to one join I_DomainFixedValueText as ReversedDocumentsText on ReversedDocumentsText.SAPDataDictionaryDomain = 'FIPE_REVERSED_DOCUMENTS_TEXT'
and ReversedDocumentsText.DomainValue = '1'
and ReversedDocumentsText.Language = $session.system_language
and ReversedDocumentsText.DomainActivationState = 'A'
and ReversedDocumentsText.DomainValuePosition = '0001'
and ReversedDocumentsText.DomainVersion = '0000'
{
key SalesTaxEntry.CompanyCode,
key SalesTaxEntry.FiscalYear,
key SalesTaxEntry.AccountingDocument,
key SalesTaxEntry.StatryRptCategory,
key SalesTaxEntry.StatryRptgEntity,
key SalesTaxEntry.StatryRptRunID,
key SalesTaxEntry.Ledger,
SalesTaxEntry.OriginalReferenceDocument,
cast( CompanyCodeInfo.CompanyCodeParameterValue as fipe_company_code_tax_number ) as PE_CompanyCodeTaxNumber,
cast( concat_with_space( concat_with_space( concat_with_space (
CompanyCode._OrgAddressDefaultRprstn.AddresseeName1,
CompanyCode._OrgAddressDefaultRprstn.AddresseeName2, 1),
CompanyCode._OrgAddressDefaultRprstn.AddresseeName3, 1),
CompanyCode._OrgAddressDefaultRprstn.AddresseeName4, 1) as glo_company_code_full_name ) as CompanyCodeName,
cast( substring(SalesTaxEntry.ReportingDate,1,6 ) as vdm_yearmonth ) as CalendarYearMonth,
SalesTaxEntry.DocumentDate,
SalesTaxEntry.DocumentNetDueDate,
cast( case instr(substring(SalesTaxEntry.DocumentReferenceID,1,3), '-')
when 3
then substring(SalesTaxEntry.DocumentReferenceID,1,2)
else ''
end as fipe_document_type ) as PE_OfficialDocumentType,
cast( case instr(substring(SalesTaxEntry.DocumentReferenceID,1,3), '-')
when 3
then case instr(substring(SalesTaxEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(SalesTaxEntry.DocumentReferenceID,4,1)
when 3
then substring(SalesTaxEntry.DocumentReferenceID,4,2)
when 4
then substring(SalesTaxEntry.DocumentReferenceID,4,3)
when 5
then substring(SalesTaxEntry.DocumentReferenceID,4,4)
else ''
end
else ''
end as fipe_serial_number ) as PE_DocumentSerialNumber,
cast( case substring(SalesTaxEntry.DocumentReferenceID,1,2)
when '50'
then case instr(substring(SalesTaxEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(SalesTaxEntry.DocumentReferenceID,11,6)
when 3
then substring(SalesTaxEntry.DocumentReferenceID,12,5)
when 4
then substring(SalesTaxEntry.DocumentReferenceID,13,4)
when 5
then substring(SalesTaxEntry.DocumentReferenceID,14,3)
end
when '51'
then case instr(substring(SalesTaxEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(SalesTaxEntry.DocumentReferenceID,11,6)
when 3
then substring(SalesTaxEntry.DocumentReferenceID,12,5)
when 4
then substring(SalesTaxEntry.DocumentReferenceID,13,4)
when 5
then substring(SalesTaxEntry.DocumentReferenceID,14,3)
end
else case instr(substring(SalesTaxEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(SalesTaxEntry.DocumentReferenceID,6,11)
when 3
then substring(SalesTaxEntry.DocumentReferenceID,7,10)
when 4
then substring(SalesTaxEntry.DocumentReferenceID,8,9)
when 5
then substring(SalesTaxEntry.DocumentReferenceID,9,8)
end
end as fipe_official_doc_number ) as PE_OfficialDocumentNumber,
case
when SalesTaxEntry.IsReversed = 'X'
then ''
when OneTimeAccountBP.AccountingDocument is not null
then OneTimeAccountBP.TaxNumberType
else Customer.TaxNumberType
end as TaxNumberType,
case
when SalesTaxEntry.IsReversed = 'X'
then ''
when OneTimeAccountBP.AccountingDocument is not null
then OneTimeAccountBP.TaxID1
else Customer.TaxNumber1
end as TaxNumber1,
cast( case
when SalesTaxEntry.IsReversed = 'X'
then ReversedDocumentsText.DomainText
when OneTimeAccountBP.BusinessPartnerName1 is not null
then OneTimeAccountBP.BusinessPartnerName1
else Customer.BusinessPartnerName1
end as md_customer_name ) as CustomerName,
@Semantics.currencyCode:true
cast( Country.CountryCurrency as glo_country_currency preserving type ) as CountryCurrency,
@Semantics.currencyCode:true
SalesTaxEntry.TransactionCurrency,
@Semantics.currencyCode:true
SalesTaxEntry.ReportingCurrency,
SalesTaxEntry.PE_OriginalDocumentDate,
cast( case instr(substring(SalesTaxEntry.OriginalDocumentReferenceID,1,3), '-')
when 3
then substring(SalesTaxEntry.OriginalDocumentReferenceID,1,2)
else ''
end as fipe_original_document_type ) as PE_OriginalDocumentType,
cast( case instr(substring(SalesTaxEntry.OriginalDocumentReferenceID,4,13), '-')
when 2
then substring(SalesTaxEntry.OriginalDocumentReferenceID,4,1)
when 3
then substring(SalesTaxEntry.OriginalDocumentReferenceID,4,2)
when 4
then substring(SalesTaxEntry.OriginalDocumentReferenceID,4,3)
when 5
then substring(SalesTaxEntry.OriginalDocumentReferenceID,4,4)
else ''
end as fipe_original_document_series ) as PE_OriginalDocumentSeries,
cast( case instr(substring(SalesTaxEntry.OriginalDocumentReferenceID,4,13), '-')
when 2
then substring(SalesTaxEntry.OriginalDocumentReferenceID,6,11)
when 3
then substring(SalesTaxEntry.OriginalDocumentReferenceID,7,10)
when 4
then substring(SalesTaxEntry.OriginalDocumentReferenceID,8,9)
when 5
then substring(SalesTaxEntry.OriginalDocumentReferenceID,9,8)
else ''
end as fipe_original_document_number ) as PE_OriginalDocumentNumber,
SalesTaxEntry.ExchangeRateDate,
SalesTaxEntry.ExchangeRate,
SalesTaxEntry.FiscalPeriod,
SalesTaxEntry.ReportingDate,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_ExportAmountInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_TaxableBaseAmountInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_DiscountBaseAmtInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_VATAmountInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_VATDiscountAmountInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_ExemptTaxAmountInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_SelectiveAcqnAmtInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_TxBaseOnRiceAmtInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_TaxOnRiceAmountInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_TxOnPlasticBagAmtInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_OthTxAndChrgsAmtInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
SalesTaxEntry.PE_NonTxblBaseAmtInCtryCrcy,
@Semantics.amount.currencyCode: 'ReportingCurrency'
cast( coalesce(SalesTaxEntry.PE_ExportAmountInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_TaxableBaseAmountInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_DiscountBaseAmtInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_VATAmountInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_VATDiscountAmountInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_ExemptTaxAmountInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_NonTxblBaseAmtInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_SelectiveAcqnAmtInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_TxBaseOnRiceAmtInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_TaxOnRiceAmountInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_TxOnPlasticBagAmtInCtryCrcy, 0)
+ coalesce(SalesTaxEntry.PE_OthTxAndChrgsAmtInCtryCrcy, 0)
as fipe_total_tax_documents ) as TotalAmountInLocalCurrency,
cast( '' as logsystem ) as LogicalSystem,
cast( '' as fipe_exchange_rate_text ) as CurrencyExchangeRateAsText,
cast( '' as fins_cfin_av_sales_document) as SalesDocument
}