@ClientHandling.algorithm: #SESSION_VARIABLE //Inserted by VDM CDS Suite Plugin
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@AbapCatalog.sqlViewName: 'CQTYCONTRACCT'
//Commented by VDM CDS Suite Plugin:@ClientDependent: true
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #BLOCKED_DATA_EXCLUDED
@VDM.viewType : #CONSUMPTION
@Analytics.dataCategory: #CUBE
@Metadata.allowExtensions:true
@EndUserText.label: 'Quantity Contract Account Assignment'
define view C_QuantityContractAcctAssgmt
with parameters
P_DisplayCurrency : displaycurrency,
P_StartDate : vdm_validitystart,
P_EndDate : vdm_validitystart
as select from P_QuantityContractAcctAssgmt( P_DisplayCurrency:$parameters.P_DisplayCurrency,
P_StartDate:$parameters.P_StartDate,
P_EndDate:$parameters.P_EndDate) as PurchaseContractItem
left outer join I_CalendarDate as RelevantDates on ReleaseOrderDate = RelevantDates.CalendarDate
left outer join I_PurchasingCategoryMatlGroup as PurchasingCategoryMatlGroup on PurchaseContractItem.MaterialGroup = PurchasingCategoryMatlGroup.MaterialGroup
left outer join P_UNSGNDMATGRPTXT as _Doc on DocNumber = _Doc.DocNumber
association [1..1] to I_Material as _Material on $projection.Material = _Material.Material
association [1..1] to I_MaterialGroup as _MaterialGroup on $projection.MaterialGroup = _MaterialGroup.MaterialGroup
association [1..1] to I_Supplier as _Supplier on $projection.Supplier = _Supplier.Supplier
association [1..1] to I_Plant as _Plant on $projection.Plant = _Plant.Plant
association [1..1] to I_PurchasingOrganization as _PurchasingOrganization on $projection.PurchasingOrganization = _PurchasingOrganization.PurchasingOrganization
association [1..1] to I_PurchasingGroup as _PurchasingGroup on $projection.PurchasingGroup = _PurchasingGroup.PurchasingGroup
association [1..1] to I_CompanyCode as _CompanyCode on $projection.CompanyCode = _CompanyCode.CompanyCode
association [1..1] to I_PurchasingDocumentType as _PurchasingDocumentType on $projection.PurchaseContractType = _PurchasingDocumentType.PurchasingDocumentType
and $projection.PurchasingDocumentCategory = _PurchasingDocumentType.PurchasingDocumentCategory
association [1..1] to I_PurgDocumentItemCategory as _PurgDocumentItemCategory on $projection.PurchasingDocumentItemCategory = _PurgDocumentItemCategory.PurchasingDocumentItemCategory
association [1..1] to I_PurchasingDocumentCategory as _PurchaseOrderCategory on $projection.PurchasingDocumentCategory = _PurchaseOrderCategory.PurchasingDocumentCategory //2968854
{
key PurchaseContractItem.PurchaseContract,
key PurchaseContractItem.PurchaseContractItem,
key AccountAssignmentNumber,
key ReleaseOrder,
key ReleaseOrderItem,
concat(ReleaseOrder,ReleaseOrderItem) as PurchaseOrderItemUniqueID,
FormattedPurchaseContractItem,
cast('1' as exbu_type ) as DocNumber,
@ObjectModel.foreignKey.association: '_Supplier'
Supplier,
@ObjectModel.foreignKey.association: '_PurchasingGroup'
PurchaseContractItem.PurchasingGroup,
@ObjectModel.foreignKey.association: '_PurchasingOrganization'
PurchaseContractItem.PurchasingOrganization,
@ObjectModel.foreignKey.association: '_CompanyCode'
PurchaseContractItem.CompanyCode,
@ObjectModel.foreignKey.association: '_PurchaseOrderCategory' //2968854
PurchasingDocumentCategory,
@ObjectModel.foreignKey.association: '_PurchasingDocumentType'
PurchaseContractType,
@ObjectModel.foreignKey.association: '_PurgDocumentItemCategory'
PurchasingDocumentItemCategory,
@ObjectModel.foreignKey.association: '_Material'
PurchaseContractItem.Material,
@ObjectModel.foreignKey.association: '_MaterialGroup'
PurchaseContractItem.MaterialGroup,
@ObjectModel.foreignKey.association: '_Plant'
PurchaseContractItem.Plant,
CostCenter,
cast(coalesce(PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory,'') as /srmsmc/purchasing_category_id) as PurchasingCategory,
case when PurchasingCategoryMatlGroup._PurchasingCategory.PurgCatName is null then
_Doc.DomainText // 'Material Group not linked to purchasing category'
else PurchasingCategoryMatlGroup._PurchasingCategory.PurgCatName end as PurgCatName,
CreatedByUser,
ValidityEndDate,
@Semantics.calendar.year: true
RelevantDates.CalendarYear,
RelevantDates.CalendarQuarter,
RelevantDates.CalendarMonth,
RelevantDates.CalendarWeek,
@Semantics.currencyCode: true
PurchaseContractItem.DisplayCurrency,
@Semantics.unitOfMeasure: true
PurchaseContractItem.OrderQuantityUnit,
//@Aggregation.default: #SUM
@Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
PurchaseContractItem.TargetQuantity,
@Aggregation.default: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast( ContractNetPriceAmount * AverageTargetQuantity as mm_a_target_amount ) as TargetAmount,
@Aggregation.default: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast( ContractNetPriceAmount * TargetQuantity as mm_a_target_amount ) as RelOrdLvlContractTargetAmount,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast(ReleaseOrderItemNetAmount as mm_a_release_ord_net_amt_21_2 ) as ReleaseOrderItemNetAmount, //3229617
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast( ContractNetPriceAmount * ReleaseOrderItemOrderQuantity as mm_a_release_ord_net_amt_21_2 ) as ContrNetPrRltvReleasedAmount, //3229617
//@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
ReleaseOrderItemOrderQuantity,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast(0 as mm_a_release_order_net_amount ) as PredictedConsumptionAmount,
@DefaultAggregation: #MAX
cast ( '00000000' as mm_a_cntr_expiry_predict_date) as ContractExpiryPredictedDate,
@DefaultAggregation: #MAX
cast(0 as abap.int4 ) as PredictedDaysToContrExpiry,
@DefaultAggregation: #MAX
cast( 0 as abap.dec(15,2) ) as PredictiveConsumptionInPct,
// Added for ATC,not used in KPI,dont remove as the CDS has @Metadata.allowExtensions:true
@Consumption.hidden: true
@DefaultAggregation: #SUM
@EndUserText.label: 'Number of Purchase Orders'
cast(0 as abap.int4 ) as NumberOfPurchaseOrders,
_PurchaseOrderCategory, //2968854
_PurchasingDocumentType,
_PurgDocumentItemCategory,
_Material,
_MaterialGroup,
_Plant,
_Supplier,
_PurchasingGroup,
_PurchasingOrganization,
_CompanyCode
}
union all select from P_QuantityContractPrediction(P_DisplayCurrency:$parameters.P_DisplayCurrency,
P_StartDate:$parameters.P_StartDate,
P_EndDate:$parameters.P_EndDate) as ContractPrediction
left outer join I_PurchasingCategoryMatlGroup as PurchasingCategoryMatlGroup on ContractPrediction.MaterialGroup = PurchasingCategoryMatlGroup.MaterialGroup
left outer join I_CalendarDate as RelevantDates on ContractPrediction.ContractExpiryPredictedDate = RelevantDates.CalendarDate
left outer join P_UNSGNDMATGRPTXT as _Doc on DocNumber = _Doc.DocNumber
association [1..1] to I_Material as _Material on $projection.Material = _Material.Material
association [1..1] to I_MaterialGroup as _MaterialGroup on $projection.MaterialGroup = _MaterialGroup.MaterialGroup
association [1..1] to I_Supplier as _Supplier on $projection.Supplier = _Supplier.Supplier
association [1..1] to I_Plant as _Plant on $projection.Plant = _Plant.Plant
association [1..1] to I_PurchasingOrganization as _PurchasingOrganization on $projection.PurchasingOrganization = _PurchasingOrganization.PurchasingOrganization
association [1..1] to I_PurchasingGroup as _PurchasingGroup on $projection.PurchasingGroup = _PurchasingGroup.PurchasingGroup
association [1..1] to I_CompanyCode as _CompanyCode on $projection.CompanyCode = _CompanyCode.CompanyCode
association [1..1] to I_PurchasingDocumentType as _PurchasingDocumentType on $projection.PurchaseContractType = _PurchasingDocumentType.PurchasingDocumentType
and $projection.PurchasingDocumentCategory = _PurchasingDocumentType.PurchasingDocumentCategory
association [1..1] to I_PurgDocumentItemCategory as _PurgDocumentItemCategory on $projection.PurchasingDocumentItemCategory = _PurgDocumentItemCategory.PurchasingDocumentItemCategory
association [1..1] to I_PurchasingDocumentCategory as _PurchaseOrderCategory on $projection.PurchasingDocumentCategory = _PurchaseOrderCategory.PurchasingDocumentCategory //2968854
{
key PurchaseContract as PurchaseContract,
key PurchaseContractItem as PurchaseContractItem,
key AccountAssignmentNumber,
key ReleaseOrder,
key ReleaseOrderItem,
concat(ReleaseOrder,ReleaseOrderItem) as PurchaseOrderItemUniqueID,
FormattedPurchaseContractItem,
cast('1' as exbu_type ) as DocNumber,
@ObjectModel.foreignKey.association: '_Supplier'
Supplier,
@ObjectModel.foreignKey.association: '_PurchasingGroup'
PurchasingGroup,
@ObjectModel.foreignKey.association: '_PurchasingOrganization'
PurchasingOrganization,
@ObjectModel.foreignKey.association: '_CompanyCode'
CompanyCode,
@ObjectModel.foreignKey.association: '_PurchaseOrderCategory' //2968854
PurchasingDocumentCategory,
@ObjectModel.foreignKey.association: '_PurchasingDocumentType'
PurchaseContractType,
@ObjectModel.foreignKey.association: '_PurgDocumentItemCategory'
PurchasingDocumentItemCategory,
@ObjectModel.foreignKey.association: '_Material'
Material,
@ObjectModel.foreignKey.association: '_MaterialGroup'
ContractPrediction.MaterialGroup,
@ObjectModel.foreignKey.association: '_Plant'
Plant,
CostCenter,
cast(coalesce(PurchasingCategoryMatlGroup._PurchasingCategory.PurchasingCategory,'') as /srmsmc/purchasing_category_id) as PurchasingCategory,
case when PurchasingCategoryMatlGroup._PurchasingCategory.PurgCatName is null then
_Doc.DomainText // 'Material Group not linked to purchasing category'
else PurchasingCategoryMatlGroup._PurchasingCategory.PurgCatName end as PurgCatName,
CreatedByUser,
ValidityEndDate,
RelevantDates.CalendarYear,
RelevantDates.CalendarQuarter,
RelevantDates.CalendarMonth,
RelevantDates.CalendarWeek,
@Semantics.currencyCode: true
DisplayCurrency,
@Semantics.unitOfMeasure: true
OrderQuantityUnit,
//@Aggregation.default: #SUM
@Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
0 as TargetQuantity,
@Aggregation.default: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast( 0 as mm_a_target_amount ) as TargetAmount,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
0 as RelOrdLvlContractTargetAmount,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast(0 as mm_a_release_ord_net_amt_21_2 ) as ReleaseOrderItemNetAmount,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
cast(0 as mm_a_release_ord_net_amt_21_2 ) as ContrNetPrRltvReleasedAmount, //3229617
//@DefaultAggregation: #SUM
@Semantics.quantity.unitOfMeasure: 'OrderQuantityUnit'
cast( 0 as mm_a_release_order_quantity ) as ReleaseOrderItemOrderQuantity,
@DefaultAggregation: #SUM
@Semantics.amount.currencyCode: 'DisplayCurrency'
PredictedConsumptionAmount,
@DefaultAggregation: #MAX
ContractExpiryPredictedDate,
@DefaultAggregation: #MAX
PredictedDaysToContrExpiry,
@DefaultAggregation: #MAX
cast( 1 as abap.dec(15,2) ) as PredictiveConsumptionInPct,
// Added for ATC,not used in KPI,dont remove as the CDS has @Metadata.allowExtensions:true
@Consumption.hidden: true
@DefaultAggregation: #SUM
@EndUserText.label: 'Number of Purchase Orders'
cast(0 as abap.int4 ) as NumberOfPurchaseOrders,
_PurchaseOrderCategory, //2968854
_PurchasingDocumentType,
_PurgDocumentItemCategory,
_Material,
_MaterialGroup,
_Plant,
_Supplier,
_PurchasingGroup,
_PurchasingOrganization,
_CompanyCode
//
// _PurchaseContractItem,
// _PurchaseContract,
// _CalendarDate
}
where
PredictedConsumptionAmount >= 0