@EndUserText.label: 'Utilities Contract Product Changes'
@Analytics.dataCategory: #CUBE
@VDM.viewType: #COMPOSITE
@AbapCatalog.sqlViewName: 'IECONTPRODCHGS'
@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_UtilsContractProductChanges
with parameters
@Environment.systemField: #SYSTEM_DATE
P_StartDate : sydate,
@Environment.systemField: #SYSTEM_DATE
P_EndDate : sydate
as select from I_UtilitiesContract as UtilitiesContract
left outer join I_UtilitiesContractHistory as UtilitiesContractHistory on UtilitiesContract.UtilitiesContract = UtilitiesContractHistory.UtilitiesContract
left outer join I_UtilitiesMoveOutDocument as UtilitiesMoveOutDocument on UtilitiesContract.UtilitiesContract = UtilitiesMoveOutDocument.UtilitiesContract
and UtilitiesMoveOutDocument.UtilsMoveOutDocumentIsReversed = ''
left outer join I_UtilsContrNextValidityPeriod as UtilsContrNextValidityPeriod on UtilitiesContractHistory.UtilitiesContract = UtilsContrNextValidityPeriod.UtilitiesContract
and UtilitiesContractHistory.UtilsContractValidityStartDate = UtilsContrNextValidityPeriod.UtilsContrNextValidityPeriod
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
key cast(UtilitiesContractHistory.UtilsContractValidityEndDate as e_utilsprodcenddate_vdm preserving type ) as UtilsProdAllocationEndDate,
@ObjectModel.foreignKey.association: '_CompanyCode'
UtilitiesContract.CompanyCode,
@ObjectModel.foreignKey.association: '_Division'
UtilitiesContract.UtilitiesDivision,
@ObjectModel.foreignKey.association:'_UtilitiesPortion'
UtilitiesContract.AlternativeUtilitiesPortion,
UtilitiesContract.UtilsContrIsBilledManually,
@ObjectModel.foreignKey.association: '_UtilsBillgBlockReason'
UtilitiesContract.UtilsBillgBlockReason,
@ObjectModel.foreignKey.association:'_UtilsBillgUnblockReason'
UtilitiesContract.UtilsBillgUnblockReason,
@ObjectModel.foreignKey.association: '_UtilitiesInstallation'
UtilitiesContract.UtilitiesInstallation,
@Semantics.businessDate.from: true
UtilitiesContract.UtilitiesMoveInDate,
@Semantics.businessDate.to: true
UtilitiesContract.UtilitiesMoveOutDate,
@Semantics.businessDate.at: true
UtilitiesContract.UtilsContractCreationDate,
@Semantics.user.createdBy: true
UtilitiesContract.UtilsContractCreatedByUser,
@Semantics.businessDate.at: true
UtilitiesContract.UtilsContractLastChangeDate,
@Semantics.user.lastChangedBy: true
UtilitiesContract.UtilsContractLastChangedByUser,
@ObjectModel.foreignKey.association: '_UtilsPlantOrCompanyCnsmpn'
UtilitiesContract.UtilsPlantOrCompanyConsumption,
@ObjectModel.foreignKey.association: '_InvcUtilsContractsJointly'
UtilitiesContract.UtilsContrJointInvcType,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesContractCanclnDate,
@Semantics.businessDate.at: true
UtilitiesContract.UtilitiesContractRenewalDate,
@ObjectModel.foreignKey.association: '_CADunningProcedure'
UtilitiesContract.CADunningProcedure,
@ObjectModel.foreignKey.association: '_UtilitiesServiceProvider'
UtilitiesContract.UtilitiesServiceProvider,
@ObjectModel.foreignKey.association: '_UtilsContrProcessingStatus'
UtilitiesContract.UtilsContractProcessingSts,
UtilitiesContract.AuthorizationGroup,
UtilitiesContract.DeletionIndicator,
@ObjectModel.foreignKey.association: '_UtilsOutsrtgChkGrpBillg'
UtilitiesContract.UtilsOutsrtgCheckGrpForBillg,
@ObjectModel.foreignKey.association: '_UtilsAccountDetnCode'
UtilitiesContract.UtilitiesAccountDetnCode,
@ObjectModel.foreignKey.association: '_UtilsContrSpcfcBdgtBillgCyc'
UtilitiesContract.UtilsContrSpcfcBdgtBillgCycle,
@ObjectModel.foreignKey.association: '_UtilsBillgOutsortingGroup'
UtilitiesContract.UtilsBillgOutsortingReason,
//Contract History Data
@Semantics.businessDate.at: true
cast(UtilitiesMoveOutDocument.UtilsMoveOutDocCreationDate as e_utilscontrtermdate_vdm preserving type ) as UtilsContractTerminationDate,
@Semantics.businessDate.at: true
cast(UtilitiesContractHistory.UtilsContractValidityStartDate as e_utilsprodcstartdate_vdm preserving type ) as UtilsProdAllocationStartDate,
cast(UtilitiesContractHistory.UtilitiesProduct as e_targetutilsproduct_vdm preserving type ) as UtilitiesProduct,
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,
//Next Validity period Data
cast(UtilsContrNextValidityPeriod.UtilitiesProduct as e_oldutilsproduct_vdm preserving type ) as UtilitiesPreviousProduct,
@DefaultAggregation: #SUM
case when UtilsContrNextValidityPeriod.UtilitiesProduct <> ''
and UtilsContrNextValidityPeriod.UtilitiesProduct != UtilitiesContractHistory.UtilitiesProduct
and UtilsContrNextValidityPeriod.UtilsContractValidityEndDate >= $parameters.P_StartDate
and UtilsContrNextValidityPeriod.UtilsContractValidityEndDate <= $parameters.P_EndDate
then cast(1 as e_nrofproductchanges_vdm)
else cast(0 as e_nrofproductchanges_vdm)
end as NumberOfProductChanges,
//Calendar Date Data
cast(MoveInDate.YearMonth as e_moveinasyearmonth_vdm preserving type ) as UtilsMoveInDateAsYearMonth,
cast(MoveOutDate.YearMonth as e_moveoutasyearmonth_vdm preserving type ) as UtilsMoveOutDateAsYearMonth,
/* Associations */
UtilitiesContract._Division,
UtilitiesContract._CompanyCode,
UtilitiesContract._UtilsBillgBlockReason,
UtilitiesContract._UtilsBillgUnblockReason,
UtilitiesContract._CreatedByUser,
UtilitiesContract._LastChangedByUser,
UtilitiesContract._UtilitiesPortion,
UtilitiesContract._UtilitiesInstallation,
UtilitiesContract._UtilsOutsrtgChkGrpBillg,
UtilitiesContract._UtilsContrSpcfcBdgtBillgCyc,
UtilitiesContract._UtilsAccountDetnCode,
UtilitiesContract._UtilsPlantOrCompanyCnsmpn,
UtilitiesContract._InvcUtilsContractsJointly,
UtilitiesContract._UtilsContrProcessingStatus,
UtilitiesContract._UtilsBillgOutsortingGroup,
UtilitiesContract._UtilitiesServiceProvider,
UtilitiesContract._CADunningProcedure,
UtilitiesContract._UtilitiesContractHistory
// key UtilitiesContract.UtilitiesContract,
// @Semantics.businessDate.at: true
// key 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,
// @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: '_InvcUtilsContractsJointly'
// UtilitiesContract.UtilsContrJointInvcType,
// @ObjectModel.foreignKey.association: '_UtilsBillgBlockReason'
// UtilitiesContract.UtilsBillgBlockReason,
// @ObjectModel.foreignKey.association:'_UtilsBillgUnblockReason'
// UtilitiesContract.UtilsBillgUnblockReason,
// UtilitiesContract.UtilsContrIsBilledManually,
// @ObjectModel.foreignKey.association: '_UtilitiesInstallation'
// UtilitiesContract.UtilitiesInstallation,
// @ObjectModel.foreignKey.association: '_UtilsContrProcessingStatus'
// UtilitiesContract.UtilsContractProcessingSts,
// @ObjectModel.foreignKey.association: '_CADunningProcedure'
// UtilitiesContract.CADunningProcedure,
// @Semantics.businessDate.from: true
// UtilitiesContract.UtilitiesMoveInDate,
// @Semantics.businessDate.to: true
// UtilitiesContract.UtilitiesMoveOutDate,
// cast(MoveInDate.YearMonth as e_moveinasyearmonth_vdm preserving type ) as UtilsMoveInDateAsYearMonth,
// cast(MoveOutDate.YearMonth as e_moveoutasyearmonth_vdm preserving type ) as UtilsMoveOutDateAsYearMonth,
// @Semantics.businessDate.at: true
// UtilitiesContract.UtilsContractCreationDate,
// @Semantics.user.createdBy: true
// UtilitiesContract.UtilsContractCreatedByUser,
// @Semantics.businessDate.at: true
// UtilitiesContract.UtilsContractLastChangeDate,
// @Semantics.user.lastChangedBy: true
// UtilitiesContract.UtilsContractLastChangedByUser,
// @ObjectModel.foreignKey.association: '_UtilitiesServiceProvider'
// UtilitiesContract.UtilitiesServiceProvider,
// @Semantics.businessDate.at: true
// UtilitiesContract.UtilitiesContractCanclnDate,
// @Semantics.businessDate.at: true
// UtilitiesContract.UtilitiesContractRenewalDate,
// cast(UtilsContrNextValidityPeriod.UtilitiesProduct as e_oldutilsproduct_vdm preserving type ) as UtilitiesOldProduct,
// UtilitiesContractHistory.UtilitiesProduct,
// UtilitiesContract.DeletionIndicator,
// cast(UtilitiesMoveOutDocument.UtilsMoveOutDocCreationDate as e_utilscontrtermdate_vdm preserving type ) as UtilsContractTerminationDate,
// @DefaultAggregation: #SUM
// case when UtilsContrNextValidityPeriod.UtilitiesProduct <> ''
// and UtilsContrNextValidityPeriod.UtilitiesProduct != UtilitiesContractHistory.UtilitiesProduct
// and UtilsContrNextValidityPeriod.UtilsContractValidityEndDate >= $parameters.P_StartDate
// and UtilsContrNextValidityPeriod.UtilsContractValidityEndDate <= $parameters.P_EndDate
// then cast(1 as e_nrofproductchanges_vdm)
// else cast(0 as e_nrofproductchanges_vdm)
// end as NumberOfProductChanges,
// 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,
// /* Associations */
// UtilitiesContract._CompanyCode,
// UtilitiesContract._Division,
// UtilitiesContract._UtilitiesServiceProvider,
// UtilitiesContract._CADunningProcedure,
// UtilitiesContract._UtilitiesInstallation,
// UtilitiesContract._UtilsAccountDetnCode,
// UtilitiesContract._UtilsBillgBlockReason,
// UtilitiesContract._UtilsBillgUnblockReason,
// UtilitiesContract._UtilsContrProcessingStatus,
// UtilitiesContract._UtilsPlantOrCompanyCnsmpn,
// UtilitiesContract._InvcUtilsContractsJointly
//
// as _YearMonth
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CALENDARDATE",
"I_UTILITIESCONTRACT",
"I_UTILITIESCONTRACTHISTORY",
"I_UTILITIESMOVEOUTDOCUMENT",
"I_UTILSCONTRNEXTVALIDITYPERIOD"
],
"ASSOCIATED":
[
"I_CADUNNINGPROCEDURE",
"I_COMPANYCODE",
"I_DIVISION",
"I_INVCUTILSCONTRACTSJOINTLY",
"I_USER",
"I_UTILITIESCONTRACTHISTORY",
"I_UTILITIESINSTALLATION",
"I_UTILITIESPORTION",
"I_UTILITIESSERVICEPROVIDER",
"I_UTILSACCOUNTDETNCODE",
"I_UTILSBILLGBLOCKREASON",
"I_UTILSBILLGOUTSORTINGGROUP",
"I_UTILSBILLGUNBLOCKREASON",
"I_UTILSCONTRPROCESSINGSTATUS",
"I_UTILSCONTRSPCFCBDGTBILLGCYC",
"I_UTILSOUTSRTGCHKGRPBILLG",
"I_UTILSPLANTORCOMPANYCNSMPN"
],
"BASE":
[
"I_UTILITIESCONTRACT"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/