@EndUserText.label: 'Key Date-Based Utilities Contract Analysis'
@Analytics.dataCategory: #CUBE
@VDM.viewType: #COMPOSITE
@AbapCatalog.sqlViewName: 'IEUTILSCONTRANLY'
@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.allowExtensions: true
define view I_UtilsContractKeyDateAnalysis
with parameters
@Environment.systemField: #SYSTEM_DATE
P_KeyDate : sydate
as select from I_UtilitiesContract as UtilitiesContract
inner join I_UtilitiesContractHistory as UtilitiesContractHistory on UtilitiesContract.UtilitiesContract = UtilitiesContractHistory.UtilitiesContract
and(
UtilitiesContractHistory.UtilsContractValidityStartDate <= $parameters.P_KeyDate
and UtilitiesContractHistory.UtilsContractValidityEndDate >= $parameters.P_KeyDate
)
left outer join I_CalendarDate as MoveOutDate on UtilitiesContract.UtilitiesMoveOutDate = MoveOutDate.CalendarDate
left outer join I_CalendarDate as MoveInDate on UtilitiesContract.UtilitiesMoveInDate = MoveInDate.CalendarDate
{
key UtilitiesContract.UtilitiesContract,
@Semantics.businessDate.at: true
cast(UtilitiesContractHistory.UtilsContractValidityEndDate as e_utilsprodcenddate_vdm preserving type ) as UtilsProdAllocationEndDate,
@Semantics.businessDate.at: true
cast(UtilitiesContractHistory.UtilsContractValidityStartDate as e_utilsprodcstartdate_vdm preserving type ) as UtilsProdAllocationStartDate,
UtilitiesContractHistory.UtilitiesProduct,
@ObjectModel.foreignKey.association: '_CompanyCode'
UtilitiesContract.CompanyCode,
@ObjectModel.foreignKey.association: '_Division'
UtilitiesContract.UtilitiesDivision,
@ObjectModel.foreignKey.association: '_UtilsAccountDetnCode'
UtilitiesContract.UtilitiesAccountDetnCode,
@ObjectModel.foreignKey.association: '_UtilsPlantOrCompanyCnsmpn'
UtilitiesContract.UtilsPlantOrCompanyConsumption,
@ObjectModel.foreignKey.association: '_UtilsBillgBlockReason'
UtilitiesContract.UtilsBillgBlockReason,
@ObjectModel.foreignKey.association:'_UtilsBillgUnblockReason'
UtilitiesContract.UtilsBillgUnblockReason,
UtilitiesContract.UtilsContrIsBilledManually,
@ObjectModel.foreignKey.association: '_UtilsContrProcessingStatus'
UtilitiesContract.UtilsContractProcessingSts,
@ObjectModel.foreignKey.association: '_CADunningProcedure'
UtilitiesContract.CADunningProcedure,
@Semantics.user.createdBy: true
UtilitiesContract.UtilsContractCreatedByUser,
@Semantics.businessDate.at: true
UtilitiesContract.UtilsContractCreationDate,
@Semantics.user.lastChangedBy: true
UtilitiesContract. UtilsContractLastChangedByUser,
@Semantics.businessDate.at: true
UtilitiesContract.UtilsContractLastChangeDate,
@ObjectModel.foreignKey.association: '_UtilitiesServiceProvider'
UtilitiesContract.UtilitiesServiceProvider,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesContractCanclnDate,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesContractRenewalDate,
case when UtilitiesContractHistory.UtilsContractValidityStartDate <= $parameters.P_KeyDate
and UtilitiesContractHistory.UtilsContractValidityEndDate >= $parameters.P_KeyDate
then cast ('X' as e_contrisact_vdm preserving type)
else cast ('' as e_contrisact_vdm preserving type)
end as UtilitiesContractIsActive,
@DefaultAggregation: #SUM
cast(1 as e_nrofutilscontr_vdm) as NumberOfUtilitiesContracts,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesMoveInDate,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesMoveOutDate,
@Semantics.businessDate.at: true
cast($parameters.P_KeyDate as e_utilscontrkeydate_vdm preserving type ) as UtilitiesAnalysisKeyDate,
@ObjectModel.foreignKey.association: '_UtilitiesInstallation'
UtilitiesContract.UtilitiesInstallation,
@ObjectModel.foreignKey.association: '_InvcUtilsContractsJointly'
UtilitiesContract.UtilsContrJointInvcType,
cast(MoveInDate.YearMonth as e_moveinasyearmonth_vdm preserving type ) as UtilsMoveInDateAsYearMonth,
cast(MoveOutDate.YearMonth as e_moveoutasyearmonth_vdm preserving type ) as UtilsMoveOutDateAsYearMonth,
case when UtilitiesContractHistory.UtilitiesContract is null or
UtilitiesContractHistory.UtilitiesContract = '0000000000'
then cast('X' as e_utilscontrnohist_vdm preserving type )
else cast('' as e_utilscontrnohist_vdm preserving type )
end as UtilsContrHistoryIsNotExisting,
UtilitiesContract.DeletionIndicator,
UtilitiesContract.AuthorizationGroup,
/* Associations */
UtilitiesContract._CompanyCode,
UtilitiesContract._Division,
UtilitiesContract._UtilsAccountDetnCode,
UtilitiesContract._UtilsBillgBlockReason,
UtilitiesContract._UtilsBillgUnblockReason,
UtilitiesContract._UtilsContrProcessingStatus,
UtilitiesContract._UtilsPlantOrCompanyCnsmpn,
UtilitiesContract._CADunningProcedure,
UtilitiesContract._UtilitiesInstallation,
UtilitiesContract._InvcUtilsContractsJointly,
UtilitiesContract._UtilitiesServiceProvider
}
union all select from I_UtilitiesContract as UtilitiesContract
left outer join I_UtilitiesContractHistory as UtilitiesContractHistory on UtilitiesContract.UtilitiesContract = UtilitiesContractHistory.UtilitiesContract
and UtilitiesContractHistory.UtilsContractValidityEndDate = UtilitiesContract.UtilitiesMoveOutDate
left outer join I_CalendarDate as MoveOutDate on UtilitiesContract.UtilitiesMoveOutDate = MoveOutDate.CalendarDate
left outer join I_CalendarDate as MoveInDate on UtilitiesContract.UtilitiesMoveInDate = MoveInDate.CalendarDate
association [0..1] to I_UtilitiesContractHistory as _UtilitiesContractHistory on(
$projection.UtilitiesContract = _UtilitiesContractHistory.UtilitiesContract
and _UtilitiesContractHistory.UtilsContractValidityStartDate <= $parameters.P_KeyDate
and _UtilitiesContractHistory.UtilsContractValidityEndDate >= $parameters.P_KeyDate
)
{
key UtilitiesContract.UtilitiesContract,
@Semantics.businessDate.at: true
cast(UtilitiesContractHistory.UtilsContractValidityEndDate as e_utilsprodcenddate_vdm preserving type ) as UtilsProdAllocationEndDate,
@Semantics.businessDate.at: true
cast(UtilitiesContractHistory.UtilsContractValidityStartDate as e_utilsprodcstartdate_vdm preserving type ) as UtilsProdAllocationStartDate,
UtilitiesContractHistory.UtilitiesProduct,
@ObjectModel.foreignKey.association: '_CompanyCode'
UtilitiesContract.CompanyCode,
@ObjectModel.foreignKey.association: '_Division'
UtilitiesContract.UtilitiesDivision,
@ObjectModel.foreignKey.association: '_UtilsAccountDetnCode'
UtilitiesContract.UtilitiesAccountDetnCode,
@ObjectModel.foreignKey.association: '_UtilsPlantOrCompanyCnsmpn'
UtilitiesContract.UtilsPlantOrCompanyConsumption,
@ObjectModel.foreignKey.association: '_UtilsBillgBlockReason'
UtilitiesContract.UtilsBillgBlockReason,
@ObjectModel.foreignKey.association:'_UtilsBillgUnblockReason'
UtilitiesContract.UtilsBillgUnblockReason,
UtilitiesContract.UtilsContrIsBilledManually,
@ObjectModel.foreignKey.association: '_UtilsContrProcessingStatus'
UtilitiesContract.UtilsContractProcessingSts,
@ObjectModel.foreignKey.association: '_CADunningProcedure'
UtilitiesContract.CADunningProcedure,
@Semantics.user.createdBy: true
UtilitiesContract.UtilsContractCreatedByUser,
@Semantics.businessDate.at: true
UtilitiesContract.UtilsContractCreationDate,
@Semantics.user.lastChangedBy: true
UtilitiesContract. UtilsContractLastChangedByUser,
@Semantics.businessDate.at: true
UtilitiesContract.UtilsContractLastChangeDate,
@ObjectModel.foreignKey.association: '_UtilitiesServiceProvider'
UtilitiesContract.UtilitiesServiceProvider,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesContractCanclnDate,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesContractRenewalDate,
case when UtilitiesContractHistory.UtilsContractValidityStartDate <= $parameters.P_KeyDate
and UtilitiesContractHistory.UtilsContractValidityEndDate >= $parameters.P_KeyDate
then cast ('X' as e_contrisact_vdm preserving type)
else cast ('' as e_contrisact_vdm preserving type)
end as UtilitiesContractIsActive,
@DefaultAggregation: #SUM
cast(1 as e_nrofutilscontr_vdm) as NumberOfUtilitiesContracts,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesMoveInDate,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesMoveOutDate,
@Semantics.businessDate.at: true
cast($parameters.P_KeyDate as e_utilscontrkeydate_vdm preserving type ) as UtilitiesAnalysisKeyDate,
@ObjectModel.foreignKey.association: '_UtilitiesInstallation'
UtilitiesContract.UtilitiesInstallation,
@ObjectModel.foreignKey.association: '_InvcUtilsContractsJointly'
UtilitiesContract.UtilsContrJointInvcType,
cast(MoveInDate.YearMonth as e_moveinasyearmonth_vdm preserving type ) as UtilsMoveInDateAsYearMonth,
cast(MoveOutDate.YearMonth as e_moveoutasyearmonth_vdm preserving type ) as UtilsMoveOutDateAsYearMonth,
case when UtilitiesContractHistory.UtilitiesContract is null or
UtilitiesContractHistory.UtilitiesContract = '0000000000'
then cast('X' as e_utilscontrnohist_vdm preserving type )
else cast('' as e_utilscontrnohist_vdm preserving type )
end as UtilsContrHistoryIsNotExisting,
UtilitiesContract.DeletionIndicator,
UtilitiesContract.AuthorizationGroup,
/* Associations */
UtilitiesContract._CompanyCode,
UtilitiesContract._Division,
UtilitiesContract._UtilsAccountDetnCode,
UtilitiesContract._UtilsBillgBlockReason,
UtilitiesContract._UtilsBillgUnblockReason,
UtilitiesContract._UtilsContrProcessingStatus,
UtilitiesContract._UtilsPlantOrCompanyCnsmpn,
UtilitiesContract._CADunningProcedure,
UtilitiesContract._UtilitiesInstallation,
UtilitiesContract._InvcUtilsContractsJointly,
UtilitiesContract._UtilitiesServiceProvider
}
where
_UtilitiesContractHistory.UtilsContractValidityEndDate is null
or _UtilitiesContractHistory.UtilsContractValidityEndDate = '00000000'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CALENDARDATE",
"I_UTILITIESCONTRACT",
"I_UTILITIESCONTRACTHISTORY"
],
"ASSOCIATED":
[
"I_CADUNNINGPROCEDURE",
"I_COMPANYCODE",
"I_DIVISION",
"I_INVCUTILSCONTRACTSJOINTLY",
"I_UTILITIESINSTALLATION",
"I_UTILITIESSERVICEPROVIDER",
"I_UTILSACCOUNTDETNCODE",
"I_UTILSBILLGBLOCKREASON",
"I_UTILSBILLGUNBLOCKREASON",
"I_UTILSCONTRPROCESSINGSTATUS",
"I_UTILSPLANTORCOMPANYCNSMPN"
],
"BASE":
[
"I_UTILITIESCONTRACT"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/