@EndUserText.label: 'Service Contract Financial Data Cube'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog: {
sqlViewName: 'ISERVCONTRFINCBE',
compiler.compareFilter: true,
preserveKey: true
}
@VDM: {
viewType: #COMPOSITE,
lifecycle.contract.type: #PUBLIC_LOCAL_API
}
@AccessControl: {
authorizationCheck: #CHECK,
personalData.blocking: #('TRANSACTIONAL_DATA')
}
@ObjectModel: {
// representativeKey: 'ServiceContract',
usageType: {
dataClass: #MIXED,
serviceQuality: #D,
sizeCategory: #XXL
}
}
@Analytics: {
dataCategory: #CUBE,
internalName: #LOCAL
}
@Metadata.allowExtensions:true
//@Metadata.ignorePropagatedAnnotations: true
define view I_ServiceContractFinDataCube
with parameters
P_DisplayCurrency : vdm_v_display_currency
as select from I_ServiceDocumentEnhcd
association [0..1] to P_SrvcContrFinSum as _NonPeriodicFinData on $projection.ServiceContract = _NonPeriodicFinData.ServiceContract
association [0..1] to P_SrvcContrDays as _ContractDays on $projection.ServiceContract = _ContractDays.ServiceDocument
and $projection.ServiceObjectType = _ContractDays.ServiceObjectType
association [0..1] to I_CalendarDate as _CurrentDate on _CurrentDate.CalendarDate = $session.system_date
association [0..1] to I_Currency as _TransactionCurrency on $projection.TransactionCurrency = _TransactionCurrency.Currency
association [0..1] to I_Currency as _CompanyCodeCurrency on $projection.CompanyCodeCurrency = _CompanyCodeCurrency.Currency
association [0..1] to P_SrvcContrList as _Contract on $projection.ServiceContract = _Contract.ServiceContract
association [0..1] to I_CalendarMonth as _CalendarMonth on $projection.CalendarMonth = _CalendarMonth.CalendarMonth
association [0..1] to I_CalendarQuarter as _CalendarQuarter on $projection.CalendarQuarter = _CalendarQuarter.CalendarQuarter
association [0..1] to E_ServiceDocument as _ServiceDocumentExt on $projection.ServiceObjectType = _ServiceDocumentExt.ServiceCategory
and $projection.ServiceContract = _ServiceDocumentExt.ServiceOrder
{
@ObjectModel.foreignKey.association: '_ServiceObjType'
key ServiceObjectType,
key ServiceDocument as ServiceContract,
ServiceDocumentCharUUID as ServiceContractUUID,
ServiceDocumentDescription,
@ObjectModel.foreignKey.association: '_ServiceDocumentType'
ServiceDocumentType,
_ServiceDocumentType,
@ObjectModel.foreignKey.association: '_SrvcDocLifecycleStatus'
ServiceDocumentStatus,
ServiceContractStartDate,
ServiceContractEndDate,
ServiceContractStartDateTime,
ServiceContractEndDateTime,
@ObjectModel.foreignKey.association: '_TransactionCurrency'
@Semantics.currencyCode:true
TransactionCurrency,
@ObjectModel.foreignKey.association: '_SalesOrganization'
SalesOrganization,
@ObjectModel.foreignKey.association: '_DistributionChannel'
DistributionChannel,
@ObjectModel.foreignKey.association: '_Division'
Division,
@ObjectModel.foreignKey.association: '_SalesOffice'
SalesOffice,
@ObjectModel.foreignKey.association: '_SalesGroup'
SalesGroup,
@ObjectModel.foreignKey.association: '_SalesOrganizationOrgUnit_2'
SalesOrganizationOrgUnitID,
@ObjectModel.foreignKey.association: '_SalesOfficeOrgUnit_2'
SalesOfficeOrgUnitID,
@ObjectModel.foreignKey.association: '_SalesGroupOrgUnit_2'
SalesGroupOrgUnitID,
@ObjectModel.foreignKey.association: '_SoldToPartyCountry'
SoldToPartyCountry,
_SoldToPartyCountry,
@ObjectModel.foreignKey.association: '_SoldToPartyRegion'
SoldToPartyRegion,
_SoldToPartyRegion,
@ObjectModel.foreignKey.association: '_SoldToParty'
SoldToParty,
_SoldToParty.BusinessPartnerFullName as BusinessPartnerFullName,
ServiceOrganization,
@ObjectModel.foreignKey.association: '_RespEmployee'
ResponsibleEmployee,
///////////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////// List of Contracts by Contract Value ///////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////
@ObjectModel.foreignKey.association: '_CompanyCodeCurrency'
@Semantics.currencyCode: true
_NonPeriodicFinData.CompanyCodeCurrency as CompanyCodeCurrency,
@Semantics.currencyCode: true
cast(:P_DisplayCurrency as currency) as Currency,
@Semantics: { amount : {currencyCode: 'Currency'} }
@DefaultAggregation: #SUM
currency_conversion( amount => ServiceDocNetAmount,
source_currency => TransactionCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_type => 'M',
exchange_rate_date => cast( $session.system_date as abap.dats),
error_handling => 'KEEP_UNCONVERTED'
) as ServiceDocNetAmount,
@Semantics: { amount : {currencyCode: 'Currency'} }
@DefaultAggregation: #SUM
cast(currency_conversion( amount => _Contract.TotalSrvcContrValueInDC,
source_currency => _NonPeriodicFinData.CompanyCodeCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_type => 'M',
exchange_rate_date => cast( $session.system_date as abap.dats),
error_handling => 'KEEP_UNCONVERTED'
) as fis_revenue_hsl) as TotalSrvcContrValueInDC,
@Semantics: { amount : {currencyCode: 'Currency'} }
@DefaultAggregation: #SUM
currency_conversion( amount => _Contract.AnnualSrvcContrValueInDC,
source_currency => TransactionCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_type => 'M',
exchange_rate_date => cast( $session.system_date as abap.dats),
error_handling => 'KEEP_UNCONVERTED'
) as AnnualSrvcContrValueInDC,
@Semantics: { amount : {currencyCode: 'Currency'} }
@DefaultAggregation: #SUM
currency_conversion( amount => _NonPeriodicFinData.SrvcContrProfitabilityInDC,
source_currency => _NonPeriodicFinData.CompanyCodeCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_type => 'M',
exchange_rate_date => cast( $session.system_date as abap.dats),
error_handling => 'KEEP_UNCONVERTED'
) as SrvcContrProfitabilityInDC,
@Semantics: { amount : {currencyCode: 'Currency'} }
@DefaultAggregation: #SUM
currency_conversion( amount => _NonPeriodicFinData.TotalSrvcContrCostAmtInCc,
source_currency => _NonPeriodicFinData.CompanyCodeCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_type => 'M',
exchange_rate_date => cast( $session.system_date as abap.dats),
error_handling => 'KEEP_UNCONVERTED'
) as TotalSrvcContrCostAmtInCc,
// TotalSrvcContrCostAmtInCc as TotalSrvcContrCostInDC,
@Semantics: { amount : {currencyCode: 'Currency'} }
@DefaultAggregation: #SUM
currency_conversion( amount => _NonPeriodicFinData.TotalSrvcContrRevnAmtInCc,
source_currency => _NonPeriodicFinData.CompanyCodeCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_type => 'M',
exchange_rate_date => cast( $session.system_date as abap.dats),
error_handling => 'KEEP_UNCONVERTED'
) as TotalSrvcContrRevnAmtInCc,
// TotalSrvcContrRevnAmtInCc as TotalSrvcContrRevnInDC,
@Semantics: { amount : {currencyCode: 'Currency'} }
@DefaultAggregation: #SUM
currency_conversion( amount => _NonPeriodicFinData.YTDSrvcContrCostInDC,
source_currency => _NonPeriodicFinData.CompanyCodeCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_type => 'M',
exchange_rate_date => cast( $session.system_date as abap.dats),
error_handling => 'KEEP_UNCONVERTED'
) as YTDSrvcContrCostInDC,
@Semantics: { amount : {currencyCode: 'Currency'} }
@DefaultAggregation: #SUM
currency_conversion( amount => _NonPeriodicFinData.YTDSrvcContrRevnAmtInDC ,
source_currency => _NonPeriodicFinData.CompanyCodeCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_type => 'M',
exchange_rate_date => cast( $session.system_date as abap.dats),
error_handling => 'KEEP_UNCONVERTED'
) as YTDSrvcContrRevnAmtInDC,
@Semantics: { amount : {currencyCode: 'Currency'} }
@DefaultAggregation: #SUM
currency_conversion( amount => _NonPeriodicFinData.YTDSrvcContrPrftMarginAmtInDC ,
source_currency => _NonPeriodicFinData.CompanyCodeCurrency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_type => 'M',
exchange_rate_date => cast( $session.system_date as abap.dats),
error_handling => 'KEEP_UNCONVERTED'
) as YTDSrvcContrPrftMarginAmtInDC,
cast( '' as calendaryear) as CalendarYear,
cast( '' as calendarweek) as CalendarWeek,
cast( '1' as calendarquarter) as CalendarQuarter,
cast( '01' as calendarmonth) as CalendarMonth,
///////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////// Expired Contracts ////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////////
@DefaultAggregation: #SUM
cast( case when ServiceContractEndDate < $session.system_date then cast (1 as abap.int4)
else cast(0 as abap.int4)
end as abap.int4) as NmbrOfExpiredSrvcContracts,
@DefaultAggregation: #SUM
cast( case when ServiceContractEndDate >= $session.system_date
and ServiceContractEndDate <= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,6,'FAIL')
then cast (1 as abap.int4)
end as abap.int4 ) as NmbrOfExpiredSrvcContrCurWeek,
@DefaultAggregation: #SUM
cast( case when ServiceContractEndDate >= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,7,'FAIL')
and ServiceContractEndDate <= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,13,'FAIL')
then cast (1 as abap.int4)
end as abap.int4 ) as NmbrOfExpiredSrvcContrNextWeek,
@DefaultAggregation: #SUM
cast( case when ServiceContractEndDate >= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,14,'FAIL')
and ServiceContractEndDate <= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,20,'FAIL')
then cast (1 as abap.int4)
end as abap.int4 ) as NmbrOfExpiredSrvcContrIn2Weeks,
@DefaultAggregation: #SUM
cast( case when ServiceContractEndDate >= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,21,'FAIL')
and ServiceContractEndDate <= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,27,'FAIL')
then cast (1 as abap.int4)
end as abap.int4 ) as NmbrOfExpiredSrvcContrIn3Weeks,
case
when ServiceContractEndDate < $session.system_date then 'SC01'
when ServiceContractEndDate >= $session.system_date and
ServiceContractEndDate <= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,6,'FAIL')
then 'SC02'
when ServiceContractEndDate >= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,7,'FAIL') and
ServiceContractEndDate <= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,13,'FAIL')
then 'SC03'
when ServiceContractEndDate >= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,14,'FAIL') and
ServiceContractEndDate <= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,20,'FAIL')
then 'SC04'
when ServiceContractEndDate >= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,21,'FAIL') and
ServiceContractEndDate <= DATS_ADD_DAYS( _CurrentDate.FirstDayOfWeekDate,27,'FAIL')
then 'SC05'
end as IsExpired,
_CalendarMonth,
_CalendarQuarter,
_ServiceObjType,
_CompanyCodeCurrency,
_TransactionCurrency,
_CurrentDate,
_SoldToParty,
_SalesOrganization,
_DistributionChannel,
_Division,
_SalesOffice,
_SalesGroup,
@API.element.releaseState: #DEPRECATED
@API.element.successor: '_SalesOrganizationOrgUnit_2'
_SalesOrganizationOrgUnit,
_SalesOrganizationOrgUnit_2,
@API.element.releaseState: #DEPRECATED
@API.element.successor: '_SalesOfficeOrgUnit_2'
_SalesOfficeOrgUnit,
_SalesOfficeOrgUnit_2,
@API.element.releaseState: #DEPRECATED
@API.element.successor: '_SalesGroupOrgUnit_2'
_SalesGroupOrgUnit,
_SalesGroupOrgUnit_2,
@ObjectModel.association.boundFields.dataMatchesSourceAndTargetTypes: true
@API.element.releaseState: #DEPRECATED
@API.element.successor: '_SrvcDocLifecycleStatus'
_ServiceDocumentStatus,
_SrvcDocLifecycleStatus,
_RespEmployee
}
where
ServiceObjectType = 'BUS2000112'
and ServiceContractEndDate is not initial