@AbapCatalog.sqlViewName: 'PPEPURFRGNIA'
@AbapCatalog.compiler.compareFilter: true
@VDM.private:true
@VDM.viewType: #CONSUMPTION
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #MIXED
define view P_PE_PurFrgnSuplrEntryAggrgd
as select from I_JournalEntry as JournalEntry
inner join P_PE_PurFrgnSuplrJEItemAggrgd as JournalEntryItem on JournalEntryItem.CompanyCode = JournalEntry.CompanyCode
and JournalEntryItem.AccountingDocument = JournalEntry.AccountingDocument
and JournalEntryItem.FiscalYear = JournalEntry.FiscalYear
inner join I_TaxItem as TaxItem on TaxItem.CompanyCode = JournalEntry.CompanyCode
and TaxItem.AccountingDocument = JournalEntry.AccountingDocument
and TaxItem.FiscalYear = JournalEntry.FiscalYear
inner join I_Country as Country on Country.Country = 'PE'
inner join P_PE_JrnlEntryWthOfclDocClass as JrnlEntryWthOfclDocClass on JrnlEntryWthOfclDocClass.CompanyCode = JournalEntry.CompanyCode
and JrnlEntryWthOfclDocClass.AccountingDocument = JournalEntry.AccountingDocument
and JrnlEntryWthOfclDocClass.FiscalYear = JournalEntry.FiscalYear
left outer to one join I_Withholdingtaxitem as WithholdingTaxItem on WithholdingTaxItem.CompanyCode = JournalEntry.CompanyCode
and WithholdingTaxItem.AccountingDocument = JournalEntry.AccountingDocument
and WithholdingTaxItem.FiscalYear = JournalEntry.FiscalYear
left outer to one join I_OneTimeAccountBP as OneTimeAccountBP on OneTimeAccountBP.CompanyCode = JournalEntry.CompanyCode
and OneTimeAccountBP.AccountingDocument = JournalEntry.AccountingDocument
and OneTimeAccountBP.FiscalYear = JournalEntry.FiscalYear
left outer to one join I_Supplier as Supplier on Supplier.Supplier = JournalEntryItem.Supplier
left outer to one join I_PE_ConventionDoubleTaxation as PurchaseConvention on PurchaseConvention.Country = OneTimeAccountBP.Country
or PurchaseConvention.Country = Supplier.Country
left outer to one join I_ExchangeRate as ExchangeRate on ExchangeRate.SourceCurrency = JournalEntry.TransactionCurrency
and ExchangeRate.TargetCurrency = Country.CountryCurrency
and JournalEntry.TransactionCurrency <> Country.CountryCurrency
and ExchangeRate.ExchangeRateType = JournalEntry.ExchangeRateType
and ExchangeRate.ExchangeRateEffectiveDate <= JournalEntry.DocumentDate
{
key JournalEntry.CompanyCode,
key JournalEntry.FiscalYear,
key JournalEntry.AccountingDocument,
key JournalEntryItem.Ledger,
JournalEntry.FiscalPeriod,
cast( concat( JournalEntry.FiscalYear,
substring(JournalEntry.FiscalPeriod,2,2) )
as vdm_yearmonth ) as CalendarYearMonth,
cast( concat( concat( JournalEntry.FiscalYear,
substring(JournalEntry.FiscalPeriod,2,2) ), '00' )
as fis_fiscalperiod_date ) as FiscalPeriodDate,
JournalEntry.DocumentDate,
cast( case
when JournalEntry.DocumentReferenceID is not initial
then case instr(substring(JournalEntry.DocumentReferenceID,1,3), '-')
when 3
then substring(JournalEntry.DocumentReferenceID,1,2)
else ''
end
else JrnlEntryWthOfclDocClass.ODNDocumentClass
end
as fipe_document_type ) as PE_OfficialDocumentType,
cast( case
when JournalEntry.DocumentReferenceID is not initial
then
case instr(substring(JournalEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(JournalEntry.DocumentReferenceID,4,1)
when 3
then substring(JournalEntry.DocumentReferenceID,4,2)
when 4
then substring(JournalEntry.DocumentReferenceID,4,3)
when 5
then substring(JournalEntry.DocumentReferenceID,4,4)
end
end
as fipe_serial_number ) as PE_DocumentSerialNumber,
cast( case
when JournalEntry.DocumentReferenceID is not initial
then
case instr(substring(JournalEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(JournalEntry.DocumentReferenceID,6,11)
when 3
then substring(JournalEntry.DocumentReferenceID,7,10)
when 4
then substring(JournalEntry.DocumentReferenceID,8,9)
when 5
then substring(JournalEntry.DocumentReferenceID,9,8)
end
end
as fipe_official_doc_number ) as PE_OfficialDocumentNumber,
JrnlEntryWthOfclDocClass.ReportingDate,
JrnlEntryWthOfclDocClass.ODNDocumentClass,
cast( abs( TaxItem.TaxBaseAmountInTransCrcy ) as fwbas_shl ) as TaxBaseAmountInTransCrcy,
cast(
abs( JournalEntryItem.AmountInTransactionCurrency ) -
abs( TaxItem.TaxBaseAmountInTransCrcy )
as fipe_additional_concept_amount ) as PE_AddlConceptAmtInCtryCrcy,
cast( abs( JournalEntryItem.AmountInTransactionCurrency ) as fis_wsl ) as AmountInTransactionCurrency,
JournalEntry.TransactionCurrency,
Country.CountryCurrency,
JournalEntry.ExchangeRateType,
max( ExchangeRate.ExchangeRateEffectiveDate ) as ExchangeRateEffectiveDate,
JournalEntryItem.ClearingDate,
PurchaseConvention.PE_SUNATCountryCode,
cast( case
when OneTimeAccountBP.BusinessPartnerName1 is not null
then OneTimeAccountBP.BusinessPartnerName1
else Supplier.OrganizationBPName1
end
as suppliername ) as SupplierName,
cast( case
when OneTimeAccountBP.AccountingDocument is not null
then OneTimeAccountBP.TaxID1
else Supplier.TaxNumber1
end
as stcd1 ) as TaxNumber1,
cast( abs( JournalEntryItem.AmountInCompanyCodeCurrency ) as fis_hsl ) as AmountInCompanyCodeCurrency,
cast( abs( WithholdingTaxItem.WhldgTaxBaseAmtInCoCodeCrcy ) -
abs( JournalEntryItem.AmountInCompanyCodeCurrency )
as fipe_deduction_amount ) as PE_DeductionAmtInCtryCrcy,
cast( abs( WithholdingTaxItem.WhldgTaxBaseAmtInCoCodeCrcy ) as fis_qsshh ) as WhldgTaxBaseAmtInCoCodeCrcy,
WithholdingTaxItem.WithholdingTaxPercent,
cast( abs( WithholdingTaxItem.WhldgTaxAmtInCoCodeCrcy ) as fis_qbshh ) as WhldgTaxAmtInCoCodeCrcy,
cast( abs( WithholdingTaxItem.WhldgTaxAmtInTransacCrcy ) as fis_qbshb ) as WhldgTaxAmtInTransacCrcy,
PurchaseConvention.PE_ConventionAvoidDoubleTxn
}
where
JournalEntry.ReverseDocument = ''
and(
(
OneTimeAccountBP.Country is not null
and OneTimeAccountBP.Country <> 'PE'
)
or(
Supplier.Country is not null
and Supplier.Country <> 'PE'
)
)
group by
JournalEntry.CompanyCode,
JournalEntry.FiscalYear,
JournalEntry.AccountingDocument,
JournalEntryItem.Ledger,
JournalEntry.FiscalPeriod,
JournalEntry.DocumentDate,
JournalEntry.DocumentReferenceID,
JrnlEntryWthOfclDocClass.ReportingDate,
JrnlEntryWthOfclDocClass.ODNDocumentClass,
TaxItem.TaxBaseAmountInTransCrcy,
JournalEntryItem.AmountInTransactionCurrency,
JournalEntryItem.ClearingDate,
JournalEntry.TransactionCurrency,
Country.CountryCurrency,
JournalEntry.ExchangeRateType,
PurchaseConvention.PE_SUNATCountryCode,
OneTimeAccountBP.BusinessPartnerName1,
OneTimeAccountBP.AccountingDocument,
OneTimeAccountBP.TaxID1,
Supplier.OrganizationBPName1,
Supplier.TaxNumber1,
JournalEntryItem.AmountInCompanyCodeCurrency,
WithholdingTaxItem.WhldgTaxBaseAmtInCoCodeCrcy,
WithholdingTaxItem.WhldgTaxAmtInTransacCrcy,
WithholdingTaxItem.WhldgTaxAmtInCoCodeCrcy,
WithholdingTaxItem.WithholdingTaxPercent,
PurchaseConvention.PE_ConventionAvoidDoubleTxn
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_COUNTRY",
"I_EXCHANGERATE",
"I_JOURNALENTRY",
"I_ONETIMEACCOUNTBP",
"I_PE_CONVENTIONDOUBLETAXATION",
"I_SUPPLIER",
"I_TAXITEM",
"I_WITHHOLDINGTAXITEM",
"P_PE_JRNLENTRYWTHOFCLDOCCLASS",
"P_PE_PURFRGNSUPLRJEITEMAGGRGD"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/