@AbapCatalog.sqlViewName: 'PMMPOITEMHIST'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Purchase Order Item History'
@VDM.private: true
@VDM.viewType: #CONSUMPTION
@AccessControl.personalData.blocking: #NOT_REQUIRED
define view P_PurchaseOrderItemHistory
with parameters
P_StartDate : bedat,
P_EndDate : bedat,
P_DisplayCurrency : displaycurrency
as select from I_PurchaseOrderHistory as POHistory
association [1..1] to I_PurchaseOrder as _PurchaseOrder on $projection.PurchaseOrder = _PurchaseOrder.PurchaseOrder
association [1..1] to I_PurchaseOrderItem as _PurchaseOrderItem on $projection.PurchaseOrder = _PurchaseOrderItem.PurchaseOrder
and $projection.PurchaseOrderItem = _PurchaseOrderItem.PurchaseOrderItem
{
/* Keys */
key PurchaseOrder,
key PurchaseOrderItem,
key MaterialDocument,
key MaterialDocumentItem,
key MaterialDocumentYear,
key AccountAssignmentNumber,
/* Org Data*/
_PurchaseOrder.CompanyCode,
_PurchaseOrder.Supplier,
// cast( _PurchaseOrderItem._PurchaseOrder._Supplier._StandardAddress.Country as mm_a_supplier_country)as SupplierCountry,
_PurchaseOrder.PurchasingOrganization,
_PurchaseOrder.PurchasingGroup,
/* Purchase Order Date*/
_PurchaseOrder.PurchaseOrderDate,
/* Item Data*/
Material,
_PurchaseOrderItem.MaterialGroup,
Plant,
_PurchaseOrderItem.DocumentCurrency as DocumentCurrency,
POHistory.PurchaseOrderQuantityUnit,
POHistory.DocumentCurrency as Currency,
cast( :P_DisplayCurrency as displaycurrency) as DisplayCurrency,
POHistory.DocumentDate,
currency_conversion( amount => POHistory.PurchaseOrderAmount,
source_currency => POHistory.DocumentCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_date => POHistory.DocumentDate,
exchange_rate_type => 'M') as PurchaseOrderAmount, //3200657
// _PurchaseOrderItem.NetPriceQuantity, //2816465
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'PurchaseOrderQuantityUnit'
POHistory.Quantity as NetPriceQuantity,
currency_conversion( amount => _PurchaseOrderItem.EffectiveAmount,
source_currency => _PurchaseOrderItem.DocumentCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_date => _PurchaseOrder.PurchaseOrderDate,
exchange_rate_type => 'M') as EffectiveAmount, //3200657
_PurchaseOrderItem.NonDeductibleInputTaxAmount, //2833315
/* Measures */
max( case when DebitCreditCode = 'S' // 2743028
then dats_days_between(_PurchaseOrder.PurchaseOrderDate, PostingDate)
end) as DeliveryTime,
@DefaultAggregation: #SUM
sum(case when DebitCreditCode = 'S'
then (Quantity + VltdGdsRcptBlkdStkQtyInOrdUnit)
else - (Quantity + VltdGdsRcptBlkdStkQtyInOrdUnit) // 2743028
end ) as GoodsReceiptQty,
@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'PurchaseOrderQuantityUnit'
1 as WeightedReceiptQuantity, //Temporary Solution
@DefaultAggregation: #SUM
sum( case when DebitCreditCode = 'S'
then division( (Quantity + VltdGdsRcptBlkdStkQtyInOrdUnit) * _PurchaseOrderItem.OrdPriceUnitToOrderUnitDnmntr, _PurchaseOrderItem.OrderPriceUnitToOrderUnitNmrtr , 3 )
else division( - (Quantity + VltdGdsRcptBlkdStkQtyInOrdUnit) * _PurchaseOrderItem.OrdPriceUnitToOrderUnitDnmntr, _PurchaseOrderItem.OrderPriceUnitToOrderUnitNmrtr , 3 ) // 2743028
end ) as TotalQuantityInEntryUnit,
_PurchaseOrderItem,
/* Count */
@DefaultAggregation: #SUM
cast( 1 as abap.int4) as DeliveryItem
}
where
PurchaseOrderTransactionType = '1'
-- and DebitCreditCode = 'S' // 2743028
and _PurchaseOrderItem.IsReturnsItem = ''
and _PurchaseOrder.PurchasingCompletenessStatus != 'X'
and _PurchaseOrderItem.ProductType != '2'
and _PurchaseOrderItem.PurchaseOrderItemCategory != '9'
and _PurchaseOrderItem.PurchaseOrderItemCategory != '1'
and _PurchaseOrderItem.PurchaseOrderItemCategory != 'A'
and _PurchaseOrder.SupplyingPlant = ''
and _PurchaseOrder.PurchaseOrderDate >= $parameters.P_StartDate
and _PurchaseOrder.PurchaseOrderDate <= $parameters.P_EndDate
and(
_PurchaseOrderItem.IsStatisticalItem = ''
or _PurchaseOrderItem.Material <> ''
)
group by
PurchaseOrder,
PurchaseOrderItem,
MaterialDocument,
MaterialDocumentItem,
MaterialDocumentYear,
AccountAssignmentNumber,
_PurchaseOrder.CompanyCode,
_PurchaseOrder.Supplier,
_PurchaseOrder.PurchasingGroup,
_PurchaseOrder.PurchasingOrganization,
_PurchaseOrder.PurchaseOrderDate,
Material,
_PurchaseOrderItem.MaterialGroup,
Plant,
POHistory.DocumentDate,
_PurchaseOrderItem.DocumentCurrency,
POHistory.PurchaseOrderQuantityUnit,
POHistory.DocumentCurrency,
POHistory.PurchaseOrderAmount,
POHistory.Quantity,
_PurchaseOrderItem.OrdPriceUnitToOrderUnitDnmntr,
_PurchaseOrderItem.OrderPriceUnitToOrderUnitNmrtr,
_PurchaseOrderItem.EffectiveAmount, //2833315
_PurchaseOrderItem.NonDeductibleInputTaxAmount //2833315
// _PurchaseOrderItem.NetPriceAmount,
// _PurchaseOrderItem.NetPriceQuantity, //2816465
//_PurchaseOrderItem._PurchaseOrder._Supplier._StandardAddress.Country,
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_PURCHASEORDER",
"I_PURCHASEORDERHISTORY",
"I_PURCHASEORDERITEM"
],
"ASSOCIATED":
[
"I_PURCHASEORDER",
"I_PURCHASEORDERITEM"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/