I_UtilsInstKeyDateAnalysisC

@EndUserText.label: 'Key Date - Based Installation Analysis'
@Analytics.dataCategory: #CUBE
@VDM.viewType: #COMPOSITE
@AbapCatalog.sqlViewName: 'IEINSTKEYDTEANLY'
@AccessControl.authorizationCheck: #MANDATORY
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.representativeKey: 'UtilitiesInstallation'
@Metadata.allowExtensions: true

define view I_UtilsInstKeyDateAnalysisC

  with parameters
    @Environment.systemField: #SYSTEM_DATE
    P_KeyDate : e_keydate_vdm

  as select from I_UtilitiesInstallation as _UtilitiesInstallation

  association [0..*] to I_UtilitiesInstallationHist as _UtilitiesInstallationHist on  $projection.UtilitiesInstallation                         = _UtilitiesInstallationHist.UtilitiesInstallation
                                                                                  and _UtilitiesInstallationHist.UtilsInstHistValidityStartDate <= $parameters.P_KeyDate
                                                                                  and _UtilitiesInstallationHist.UtilsInstHistValidityEndDate   >= $parameters.P_KeyDate

  association [0..*] to I_UtilitiesContractHistory  as _UtilitiesContractHistory  on  $projection.UtilitiesInstallation                        =  _UtilitiesContractHistory.UtilitiesInstallation
                                                                                  and _UtilitiesContractHistory.UtilsContractValidityStartDate <= $parameters.P_KeyDate
                                                                                  and _UtilitiesContractHistory.UtilsContractValidityEndDate   >= $parameters.P_KeyDate
                                                                                  and _UtilitiesContractHistory.DeletionIndicator              <> 'X'

  association [0..1] to I_UtilitiesContract         as _UtilitiesContract         on  $projection.UtilitiesInstallation    =  _UtilitiesContract.UtilitiesInstallation
                                                                                  and _UtilitiesContract.DeletionIndicator <> 'X'

{
  key UtilitiesInstallation,

      cast (case when _UtilitiesContractHistory.UtilitiesContract is null or
                _UtilitiesContractHistory.UtilitiesContract = ''
            then 'X'    //no assigned contrcact(s)!

            else ''
      end  as e_instissuspd_vdm preserving type) as UtilsInstallationIsSuspended,

      Division,
      @ObjectModel.foreignKey.association: '_UTILITIESPREMISE'
      UtilitiesPremise,
      @ObjectModel.foreignKey.association: '_UtilsInstallationType'
      UtilitiesInstallationType,

      _UtilitiesContractHistory.UtilitiesContract,

      @ObjectModel.foreignKey.association: '_UtilsBillingClass'
      _UtilitiesInstallationHist.UtilitiesBillingClass,

      @ObjectModel.foreignKey.association: '_UtilitiesRateCategory'
      _UtilitiesInstallationHist.UtilitiesRateCategory,

      _UtilitiesInstallationHist.UtilsInstDvceAllctnIsNotAllwd,

      @ObjectModel.foreignKey.association: '_MeterReadingUnit'
      _UtilitiesInstallationHist.MeterReadingUnit,
      _UtilitiesInstallationHist.IndustrySector,

      @ObjectModel.foreignKey.association: '_UtilsMtrRdngBlockReason'
      MeterReadingBlockingReason,
      UtilitiesSrvcProviderRole,
      UtilsInstDeregulationSts,
      UtilsInstGuarOfSupReason,

      // Adress fields

      _UtilitiesPremise._UtilsTechObjLocationAddr._Address2.Country,
      _UtilitiesPremise._UtilsTechObjLocationAddr._Address2.Region,
      _UtilitiesPremise._UtilsTechObjLocationAddr._Address2.CityName,
      _UtilitiesPremise._UtilsTechObjLocationAddr._Address2.StreetName,
      _UtilitiesPremise._UtilsTechObjLocationAddr._Address2.HouseNumber,
      _UtilitiesPremise._UtilsTechObjLocationAddr._Address2.HouseNumberSupplementText,

      @Consumption.hidden: true
      AuthorizationGroup,

      @Consumption.hidden: true
      _UtilitiesContract.CompanyCode,

      @Aggregation: {default: #COUNT_DISTINCT, referenceElement:  [ 'UtilitiesInstallation' ] }
      cast(1 as e_nroutilsinst_vdm)              as NumberOfUtilitiesInstallation,


      /* Associations */
      _Division,
      _UtilitiesContract,
      _UtilitiesContract._CompanyCode,
      _UtilitiesPremise,
      _UtilsInstallationType,
      _UtilitiesInstallationHist._UtilsBillingClass,
      _UtilitiesInstallationHist._MeterReadingUnit,
      _UtilitiesInstallationHist._UtilitiesRateCategory,
      _UtilsMtrRdngBlockReason,
      _UtilsServiceProviderRole,
      _UtilsInstDeregulationSts,
      _UtilsInstGuarOfSupRsn
}
where
      _UtilitiesInstallation.UtilsInstCreationDate <= $parameters.P_KeyDate
  and _UtilitiesInstallation.IsMarkedForDeletion   <> 'X'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ADDRESSDEFAULTREPRESENTATION",
"I_UTILITIESCONTRACT",
"I_UTILITIESCONTRACTHISTORY",
"I_UTILITIESINSTALLATION",
"I_UTILITIESINSTALLATIONHIST",
"I_UTILITIESPREMISE",
"I_UTILSTECHOBJLOCATIONADDR"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_DIVISION",
"I_METERREADINGUNIT",
"I_UTILITIESCONTRACT",
"I_UTILITIESCONTRACTHISTORY",
"I_UTILITIESINSTALLATIONHIST",
"I_UTILITIESPREMISE",
"I_UTILITIESRATECATEGORY",
"I_UTILSBILLINGCLASS",
"I_UTILSINSTALLATIONTYPE",
"I_UTILSINSTDEREGULATIONSTS",
"I_UTILSINSTGUAROFSUPRSN",
"I_UTILSMTRRDNGBLOCKREASON",
"I_UTILSSERVICEPROVIDERROLE"
],
"BASE":
[
"I_UTILITIESCONTRACT",
"I_UTILITIESINSTALLATION",
"I_UTILITIESINSTALLATIONHIST"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/