@AbapCatalog.sqlViewName: 'IPPSXPOEKPH'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #MANDATORY
@EndUserText.label: 'History Per Purchasing Document'
@ObjectModel.usageType:{
serviceQuality: #X,
sizeCategory: #XXL,
dataClass: #TRANSACTIONAL
}
@VDM.viewType: #BASIC
define view I_PPS_PurchasingHistory
as select from ekbe as PurchasingHistory
inner join ekko as PurchasingDocument on PurchasingDocument.ebeln = PurchasingHistory.ebeln
inner join ekpo as PurchasingDocumentItem on PurchasingDocumentItem.ebeln = PurchasingHistory.ebeln
and PurchasingDocumentItem.ebelp = PurchasingHistory.ebelp
association [1..1] to I_CompanyCode as _CompanyCode on PurchasingDocument.bukrs = _CompanyCode.CompanyCode
association [0..1] to I_PPS_SupplierInvoice as _SupplierInvoice on _SupplierInvoice.PurchasingHistoryDocument = PurchasingHistory.belnr
and _SupplierInvoice.FiscalYear = PurchasingHistory.gjahr
and _SupplierInvoice.PurchasingHistoryDocumentItem = PurchasingHistory.buzei
association [0..1] to I_PPS_MaterialDocument as _MaterialDocument on _MaterialDocument.PurchasingHistoryDocument = PurchasingHistory.belnr
and _MaterialDocument.FiscalYear = PurchasingHistory.gjahr
and _MaterialDocument.PurchasingHistoryDocumentItem = PurchasingHistory.buzei
association [0..1] to I_AccountingDocument as _AccountingDocument on _AccountingDocument.AccountingDocument = PurchasingHistory.belnr
and _AccountingDocument.CompanyCode = PurchasingDocument.bukrs
and _AccountingDocument.FiscalYear = PurchasingHistory.gjahr
{
PurchasingHistory.ebeln as PurchasingDocument,
PurchasingHistory.ebelp as PurchasingDocumentItem,
PurchasingHistory.vgabe as PPSPurgHistDocumentCategory,
PurchasingDocument.bukrs as CompanyCode,
PurchasingHistory.gjahr as FiscalYear,
PurchasingHistory.belnr as PurchasingHistoryDocument,
PurchasingHistory.buzei as PurchasingHistoryDocumentItem,
PurchasingDocument.bsart as PurchasingDocumentType,
PurchasingDocument.ekorg as PurchasingOrganization,
PurchasingDocument.ekgrp as PurchasingGroup,
case
when PurchasingHistory.xblnr = ' ' then
case
when PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = 'C' or
PurchasingHistory.vgabe = '5' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = '1' or
PurchasingHistory.vgabe = '2' or
PurchasingHistory.vgabe = 'V'
then PurchasingHistory.ebeln
else ' '
end
else PurchasingHistory.xblnr
end as PPSReferenceDocument,
case
when PurchasingHistory.xblnr = ' ' then
cast (
case
when PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = 'C' or
PurchasingHistory.vgabe = '5' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = '1' or
PurchasingHistory.vgabe = '2' or
PurchasingHistory.vgabe = 'V'
then PurchasingHistory.ebelp
else ' '
end as numc05 )
else
cast( PurchasingHistory.ebelp as numc05 )
end as PPSReferenceDocumentItem,
case
when PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = 'C' or
PurchasingHistory.vgabe = '5' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = '1' or
PurchasingHistory.vgabe = '2' or
PurchasingHistory.vgabe = 'V'
then PurchasingDocument.bsart
else ' '
end as PPSReferenceDocumentType,
case
when PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = 'C' or
PurchasingHistory.vgabe = '5' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = '1' or
PurchasingHistory.vgabe = '2' or
PurchasingHistory.vgabe = 'V'
then PurchasingHistory.vgabe
else ' '
end as ReferenceDocumentCategory,
case
when PurchasingHistory.xblnr = ' ' then
cast ( case
when PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = 'C' or
PurchasingHistory.vgabe = '5' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = '1' or
PurchasingHistory.vgabe = '2' or
PurchasingHistory.vgabe = 'V'
then ' '
else ' '
end as bukrs )
else PurchasingDocument.bukrs
end as ReferenceCompanyCode,
case
when PurchasingHistory.xblnr = ' ' then
cast ( case
when PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = 'C' or
PurchasingHistory.vgabe = '5' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = '1' or
PurchasingHistory.vgabe = '2' or
PurchasingHistory.vgabe = 'V'
then ' '
else ' '
end as gjahr )
else PurchasingHistory.gjahr
end as ReferenceDocumentFiscalYear,
cast ( case
when PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = 'C' or
PurchasingHistory.vgabe = '5' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = 'V'
then 'AccountingDocument'
when PurchasingHistory.vgabe = '1'
then 'MaterialMovement'
when PurchasingHistory.vgabe = '2'
then 'SupplierInvoice'
else ' '
end as pps_e_semantic_object ) as SemanticObject,
case
when PurchasingHistory.xblnr = ' ' then
cast ( case
when PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = 'C' or
PurchasingHistory.vgabe = '5' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = 'V'
then 'AccountingDocument'
when PurchasingHistory.vgabe = '1'
then 'PurchaseOrder'
when PurchasingHistory.vgabe = '2'
then 'PurchaseOrder'
else ' '
end as pps_e_semantic_object )
when PurchasingHistory.xblnr <> ' ' then
cast ( case
when ( PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = 'C' or
PurchasingHistory.vgabe = '5' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = 'V' ) and
PurchasingHistory.xblnr <> PurchasingHistory.ebeln
then 'AccountingDocument'
else 'PurchaseOrder'
end as pps_e_semantic_object )
else ''
end as ReferenceSemanticObject,
case
when PurchasingHistory.vgabe = '1'
then _MaterialDocument.PPSPurgHistDocumentType
when PurchasingHistory.vgabe = '2' or
PurchasingHistory.vgabe = '3'
then _SupplierInvoice.PPSPurgHistDocumentType
when PurchasingHistory.vgabe = 'A' or
PurchasingHistory.vgabe = '4' or
PurchasingHistory.vgabe = 'V' or
PurchasingHistory.vgabe = 'C'
then _AccountingDocument.AccountingDocumentType
else 'X'
end as PPSPurgHistDocumentType,
PurchasingHistory.budat as PostingDate,
@Semantics.quantity.unitOfMeasure: 'UnitOfMeasure'
case PurchasingHistory.shkzg
when 'H' then sum ( PurchasingHistory.menge ) * -1
else sum ( PurchasingHistory.menge )
end as Quantity,
@Semantics.unitOfMeasure: true
PurchasingDocumentItem.meins as UnitOfMeasure,
@Semantics.quantity.unitOfMeasure: 'POUnitOfMeasure'
case PurchasingHistory.shkzg
when 'H' then sum ( PurchasingHistory.bpmng ) * -1
else sum ( PurchasingHistory.bpmng )
end as PurchaseOrderQty,
@Semantics.unitOfMeasure: true
PurchasingDocumentItem.bprme as POUnitOfMeasure,
@Semantics.amount.currencyCode: 'LocalCurrency'
case PurchasingHistory.shkzg
when 'H' then sum( PurchasingHistory.dmbtr ) * -1
else sum( PurchasingHistory.dmbtr )
end as AmountInLocalCurrency,
@Semantics.currencyCode:true
_CompanyCode.Currency as LocalCurrency,
@Semantics.amount.currencyCode: 'DocumentCurrency'
case PurchasingHistory.shkzg
when 'H' then sum( PurchasingHistory.wrbtr ) * -1
else sum( PurchasingHistory.wrbtr )
end as NetValueAmountInDocCurrency,
@Semantics.currencyCode:true
PurchasingHistory.waers as DocumentCurrency
}
group by
PurchasingHistory.ebeln,
PurchasingHistory.ebelp,
PurchasingHistory.vgabe,
PurchasingDocument.bukrs,
PurchasingHistory.gjahr,
PurchasingHistory.belnr,
PurchasingHistory.buzei,
PurchasingHistory.budat,
PurchasingHistory.shkzg,
PurchasingHistory.xblnr,
PurchasingHistory.lfpos,
PurchasingHistory.lfgja,
PurchasingDocumentItem.meins,
PurchasingDocumentItem.bprme,
_CompanyCode.Currency,
PurchasingDocument.bsart,
PurchasingHistory.waers,
_SupplierInvoice.PPSPurgHistDocumentType,
_MaterialDocument.PPSPurgHistDocumentType,
_AccountingDocument.AccountingDocumentType,
PurchasingDocument.ekorg,
PurchasingDocument.ekgrp
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ACCOUNTINGDOCUMENT",
"I_COMPANYCODE",
"I_PPS_MATERIALDOCUMENT",
"I_PPS_SUPPLIERINVOICE",
"EKBE",
"EKKO",
"EKPO"
],
"ASSOCIATED":
[
"I_ACCOUNTINGDOCUMENT",
"I_COMPANYCODE",
"I_PPS_MATERIALDOCUMENT",
"I_PPS_SUPPLIERINVOICE"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/