@AbapCatalog.sqlViewName: 'PPEPURAGGRGD'
@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_PurDmstcSuplrEntryAggrgd
as select from I_JournalEntry as JournalEntry
inner join I_Country as Country on Country.Country = 'PE'
inner join I_JournalEntryItem as JournalEntryItem on JournalEntryItem.CompanyCode = JournalEntry.CompanyCode
and JournalEntryItem.AccountingDocument = JournalEntry.AccountingDocument
and JournalEntryItem.FiscalYear = JournalEntry.FiscalYear
inner join I_AddlCompanyCodeInformation as CompanyCodeInfo on CompanyCodeInfo.CompanyCode = JournalEntry.CompanyCode
and CompanyCodeInfo.CompanyCodeParameterType = 'TAXNR'
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_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 P_PE_PurchaseOriginalJrnlEntry as OriginalDocument on OriginalDocument.CompanyCode = JournalEntry.CompanyCode
and OriginalDocument.FiscalYear = JournalEntry.FiscalYear
and ( OriginalDocument.ClearingAccountingDocument = JournalEntry.AccountingDocument
or ( OriginalDocument.InvoiceReference <> ''
and OriginalDocument.AccountingDocument = JournalEntry.AccountingDocument ) )
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 ( ( OriginalDocument.DocumentDate is null and
ExchangeRate.ExchangeRateEffectiveDate <= JournalEntry.DocumentDate ) or
( ExchangeRate.ExchangeRateEffectiveDate <= OriginalDocument.DocumentDate ) )
association [0..1] to I_CompanyCode as _CompanyCode on $projection.CompanyCode = _CompanyCode.CompanyCode
{
key JournalEntry.CompanyCode,
key JournalEntry.FiscalYear,
key JournalEntry.AccountingDocument,
JournalEntry.FiscalPeriod,
JournalEntryItem.Ledger,
cast ( CompanyCodeInfo.CompanyCodeParameterValue as fipe_company_code_tax_number ) as PE_CompanyCodeTaxNumber,
_CompanyCode.CompanyCodeName,
cast( concat( JournalEntry.FiscalYear, substring(JournalEntry.FiscalPeriod,2,2) ) as vdm_yearmonth ) as CalendarYearMonth,
JournalEntry.DocumentDate,
JrnlEntryWthOfclDocClass.ReportingDate,
JrnlEntryWthOfclDocClass.ODNDocumentClass,
Country.CountryCurrency,
Country.TaxCalculationProcedure,
cast ( case
when JournalEntry.DocumentReferenceID is not initial
then
case substring(JournalEntry.DocumentReferenceID,1,2)
when '14'
then JournalEntryItem.NetDueDate
when '46'
then JournalEntryItem.NetDueDate
when '50'
then JournalEntryItem.NetDueDate
when '51'
then JournalEntryItem.NetDueDate
when '52'
then JournalEntryItem.NetDueDate
when '53'
then JournalEntryItem.NetDueDate
when '54'
then JournalEntryItem.NetDueDate
else ''
end
else ''
end as faedt_fpos ) as DocumentNetDueDate,
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 substring(JournalEntry.DocumentReferenceID,1,2)
when '50'
then
case
when JournalEntry.DocumentReferenceID is not initial
then
case instr(substring(JournalEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(JournalEntry.DocumentReferenceID,6,4)
when 3
then substring(JournalEntry.DocumentReferenceID,7,4)
when 4
then substring(JournalEntry.DocumentReferenceID,8,4)
when 5
then substring(JournalEntry.DocumentReferenceID,9,4)
else ''
end
else ''
end
when '51'
then
case
when JournalEntry.DocumentReferenceID is not initial
then
case instr(substring(JournalEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(JournalEntry.DocumentReferenceID,6,4)
when 3
then substring(JournalEntry.DocumentReferenceID,7,4)
when 4
then substring(JournalEntry.DocumentReferenceID,8,4)
when 5
then substring(JournalEntry.DocumentReferenceID,9,4)
else ''
end
else ''
end
when '52'
then substring(JournalEntry.DocumentDate,1,4)
when '53'
then substring(JournalEntry.DocumentDate,1,4)
when '54'
then substring(JournalEntry.DocumentDate,1,4)
else '0000'
end as lfbja ) as ReferenceDocumentFiscalYear,
cast( case substring(JournalEntry.DocumentReferenceID,1,2)
when '50'
then
case
when JournalEntry.DocumentReferenceID is not initial
then
case instr(substring(JournalEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(JournalEntry.DocumentReferenceID,11,6)
when 3
then substring(JournalEntry.DocumentReferenceID,12,5)
when 4
then substring(JournalEntry.DocumentReferenceID,13,4)
when 5
then substring(JournalEntry.DocumentReferenceID,14,3)
end
end
when '51'
then
case
when JournalEntry.DocumentReferenceID is not initial
then
case instr(substring(JournalEntry.DocumentReferenceID,4,13), '-')
when 2
then substring(JournalEntry.DocumentReferenceID,11,6)
when 3
then substring(JournalEntry.DocumentReferenceID,12,5)
when 4
then substring(JournalEntry.DocumentReferenceID,13,4)
when 5
then substring(JournalEntry.DocumentReferenceID,14,3)
end
end
else
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,
cast( case
when OneTimeAccountBP.AccountingDocument is not null
then OneTimeAccountBP.TaxNumberType
else Supplier.TaxNumberType
end
as j_1atoid ) as TaxNumberType,
cast( case
when OneTimeAccountBP.AccountingDocument is not null
then OneTimeAccountBP.TaxID1
else Supplier.TaxNumber1
end
as stcd1 ) as TaxNumber1,
cast( case
when OneTimeAccountBP.BusinessPartnerName1 is not null
then OneTimeAccountBP.BusinessPartnerName1
else Supplier.OrganizationBPName1
end
as suppliername ) as SupplierName,
JournalEntry.TransactionCurrency,
JournalEntry.CompanyCodeCurrency,
JournalEntry.ExchangeRateType,
cast( case
when JournalEntry.TransactionCurrency <> 'PEN'
then max( ExchangeRate.ExchangeRateEffectiveDate )
else JournalEntry.DocumentDate
end as dats ) as ExchangeRateEffectiveDate,
cast( case
when OriginalDocument.DocumentDate is not initial
then OriginalDocument.DocumentDate
else ''
end as fipe_original_document_date ) as PE_OriginalDocumentDate,
cast( case
when OriginalDocument.DocumentReferenceID is not initial
then substring(OriginalDocument.DocumentReferenceID,1,2)
end
as fipe_original_document_type ) as PE_OriginalDocumentType,
cast( case
when OriginalDocument.DocumentReferenceID is not initial
then
case instr(substring(OriginalDocument.DocumentReferenceID,4,13), '-')
when 2
then substring(OriginalDocument.DocumentReferenceID,4,1)
when 3
then substring(OriginalDocument.DocumentReferenceID,4,2)
when 4
then substring(OriginalDocument.DocumentReferenceID,4,3)
when 5
then substring(OriginalDocument.DocumentReferenceID,4,4)
end
end
as fipe_original_document_series ) as PE_OriginalDocumentSeries,
cast( case instr(substring(OriginalDocument.DocumentReferenceID,4,13), '-')
when 2
then substring(OriginalDocument.DocumentReferenceID,6,11)
when 3
then substring(OriginalDocument.DocumentReferenceID,7,10)
when 4
then substring(OriginalDocument.DocumentReferenceID,8,9)
when 5
then substring(OriginalDocument.DocumentReferenceID,9,8)
end
as fipe_original_document_number ) as PE_OriginalDocumentNumber
}
where
JournalEntry.ReverseDocument = ''
and JournalEntryItem.FinancialAccountType = 'K'
and(
Supplier.Country = 'PE'
or OneTimeAccountBP.Country = 'PE'
)
group by
JournalEntry.CompanyCode,
JournalEntry.FiscalYear,
JournalEntry.AccountingDocument,
JournalEntryItem.Ledger,
JrnlEntryWthOfclDocClass.ReportingDate,
JrnlEntryWthOfclDocClass.ODNDocumentClass,
Country.CountryCurrency,
Country.TaxCalculationProcedure,
JournalEntry.DocumentDate,
JournalEntry.FiscalPeriod,
JournalEntryItem.NetDueDate,
JournalEntry.DocumentReferenceID,
JournalEntry.TransactionCurrency,
JournalEntry.CompanyCodeCurrency,
JournalEntry.ExchangeRateType,
CompanyCodeInfo.CompanyCodeParameterType,
CompanyCodeInfo.CompanyCodeParameterValue,
_CompanyCode.CompanyCodeName,
OneTimeAccountBP.AccountingDocument,
OneTimeAccountBP.TaxNumberType,
OneTimeAccountBP.TaxID1,
OneTimeAccountBP.BusinessPartnerName1,
Supplier.TaxNumberType,
Supplier.TaxNumber1,
Supplier.OrganizationBPName1,
OriginalDocument.DocumentReferenceID,
OriginalDocument.DocumentDate
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDLCOMPANYCODEINFORMATION",
"I_COMPANYCODE",
"I_COUNTRY",
"I_EXCHANGERATE",
"I_JOURNALENTRY",
"I_JOURNALENTRYITEM",
"I_ONETIMEACCOUNTBP",
"I_SUPPLIER",
"P_PE_JRNLENTRYWTHOFCLDOCCLASS",
"P_PE_PURCHASEORIGINALJRNLENTRY"
],
"ASSOCIATED":
[
"I_COMPANYCODE"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/