I_UtilsContrToBeBlldData

@EndUserText.label: 'Utilities Contract to Be Billed Data'
@VDM.viewType: #COMPOSITE
@AbapCatalog.sqlViewName: 'IECONTRTBBILLEDD'
@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
define view I_UtilsContrToBeBlldData
  /* For contracts with alternative portion */
  as select from I_UtilitiesContract            as UtilitiesContract
    inner join   I_UtilitiesPortionScheduleRecd as UtilitiesPortionScheduleRecd on UtilitiesPortionScheduleRecd.UtilitiesPortion = UtilitiesContract.AlternativeUtilitiesPortion
    inner join   I_UtilitiesInstallationHist    as UtilitiesInstallationHist    on UtilitiesInstallationHist.UtilitiesInstallation = UtilitiesContract.UtilitiesInstallation
    inner join   I_MtrRsnCorrespgToBillgRsn     as MtrRsnCorrespgToBillgRsn     on MtrRsnCorrespgToBillgRsn.UtilitiesBillingReason = UtilitiesPortionScheduleRecd.UtilitiesBillingReason
    inner join   I_MtrRdngUnitScheduleRecord    as MtrRdngUnitScheduleRecord    on  MtrRdngUnitScheduleRecord.MeterReadingUnit              = UtilitiesInstallationHist.MeterReadingUnit
                                                                                and MtrRdngUnitScheduleRecord.MeterReadingReason            = MtrRsnCorrespgToBillgRsn.MeterReadingReason
                                                                                and MtrRdngUnitScheduleRecord.MtrRdngUnitScheduleRecordDate = UtilitiesPortionScheduleRecd.UtilsPortionScheduleRecordDate
{
  UtilitiesContract.UtilitiesContract,

  @Consumption.filter.mandatory: true
  @Semantics.businessDate.at: true
  UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate,

  @Consumption.filter.mandatory: true
  @ObjectModel.foreignKey.association: '_UtilitiesPortion'
  UtilitiesPortionScheduleRecd.UtilitiesPortion,

  @Semantics.businessDate.at: true
  MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate,

  @ObjectModel.foreignKey.association: '_UtilitiesBillingReason'
  UtilitiesPortionScheduleRecd.UtilitiesBillingReason,

  @ObjectModel.foreignKey.association: '_CompanyCode'
  UtilitiesContract.CompanyCode,

  @ObjectModel.foreignKey.association: '_Division'
  UtilitiesContract.UtilitiesDivision as Division,

  @ObjectModel.foreignKey.association: '_UtilitiesInstallation'
  UtilitiesContract.UtilitiesInstallation,

  UtilitiesContract.ContractAccount,

  @ObjectModel.foreignKey.association: '_UtilsBillgBlockReason'
  UtilitiesContract.UtilsBillgBlockReason,

  @ObjectModel.foreignKey.association: '_UtilsBillgUnblockReason'
  UtilitiesContract.UtilsBillgUnblockReason,

  @ObjectModel.foreignKey.association: '_MeterReadingUnit'
  UtilitiesInstallationHist.MeterReadingUnit,

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

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

  @ObjectModel.foreignKey.association: '_MeterReadingReason'
  MtrRsnCorrespgToBillgRsn.MeterReadingReason,

  @Semantics.businessDate.at: true
  UtilitiesContract.UtilitiesMoveInDate,

  @Semantics.businessDate.at: true
  UtilitiesContract.UtilitiesMoveOutDate,

  @ObjectModel.foreignKey.association: '_InvcUtilsContractsJointly'
  UtilitiesContract.UtilsContrJointInvcType,

  @ObjectModel.foreignKey.association: '_UtilsAccountDetnCode'
  UtilitiesContract.UtilitiesAccountDetnCode,

  @Semantics.businessDate.to: true
  UtilitiesPortionScheduleRecd.UtilsPortionScheduleRecordDate,

  @Semantics.businessDate.at: true
  UtilitiesPortionScheduleRecd.SchedldUtilsInvcgPrintoutDate,

  case
    when (UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate >= UtilitiesContract.UtilitiesMoveInDate
     and  UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate <= UtilitiesContract.UtilitiesMoveOutDate)
        then 'X'
  end                                 as UtilitiesContractIsActive,

  case
    when (UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate >= UtilitiesInstallationHist.UtilsInstHistValidityStartDate
     and  UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate <= UtilitiesInstallationHist.UtilsInstHistValidityEndDate)
        then 'X'
  end                                 as UtilsInstTimeSliceIsValid,

  UtilitiesContract.AuthorizationGroup,

  /* Associations */
  UtilitiesPortionScheduleRecd._UtilitiesPortion,
  UtilitiesPortionScheduleRecd._UtilitiesBillingReason,
  UtilitiesContract._CompanyCode,
  UtilitiesContract._Division,
  UtilitiesContract._UtilitiesInstallation,
  UtilitiesContract._UtilsBillgBlockReason,
  UtilitiesContract._UtilsBillgUnblockReason,
  UtilitiesInstallationHist._MeterReadingUnit,
  UtilitiesInstallationHist._UtilitiesRateCategory,
  UtilitiesInstallationHist._UtilsBillingClass,
  MtrRsnCorrespgToBillgRsn._MeterReadingReason,
  UtilitiesContract._InvcUtilsContractsJointly,
  UtilitiesContract._UtilsAccountDetnCode

}

union all

/* For all contracts without alternative portion */
select from  I_UtilitiesContract            as UtilitiesContract
  inner join I_UtilitiesInstallationHist    as UtilitiesInstallationHist    on UtilitiesInstallationHist.UtilitiesInstallation = UtilitiesContract.UtilitiesInstallation
  inner join I_MeterReadingUnit             as MeterReadingUnit             on MeterReadingUnit.MeterReadingUnit = UtilitiesInstallationHist.MeterReadingUnit
  inner join I_UtilitiesPortionScheduleRecd as UtilitiesPortionScheduleRecd on UtilitiesPortionScheduleRecd.UtilitiesPortion = MeterReadingUnit.UtilitiesPortion
  inner join I_MtrRsnCorrespgToBillgRsn     as MtrRsnCorrespgToBillgRsn     on MtrRsnCorrespgToBillgRsn.UtilitiesBillingReason = UtilitiesPortionScheduleRecd.UtilitiesBillingReason
  inner join I_MtrRdngUnitScheduleRecord    as MtrRdngUnitScheduleRecord    on  MtrRdngUnitScheduleRecord.MeterReadingUnit              = UtilitiesInstallationHist.MeterReadingUnit
                                                                            and MtrRdngUnitScheduleRecord.MeterReadingReason            = MtrRsnCorrespgToBillgRsn.MeterReadingReason
                                                                            and MtrRdngUnitScheduleRecord.MtrRdngUnitScheduleRecordDate = UtilitiesPortionScheduleRecd.UtilsPortionScheduleRecordDate
{
  key UtilitiesContract.UtilitiesContract,

      @Consumption.filter.mandatory: true
      @Semantics.businessDate.at: true
  key UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate,

      @Consumption.filter.mandatory: true
      @ObjectModel.foreignKey.association: '_UtilitiesPortion'
  key UtilitiesPortionScheduleRecd.UtilitiesPortion,

      @Semantics.businessDate.at: true
  key MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate,

      @ObjectModel.foreignKey.association: '_UtilitiesBillingReason'
      UtilitiesPortionScheduleRecd.UtilitiesBillingReason,

      @ObjectModel.foreignKey.association: '_CompanyCode'
      UtilitiesContract.CompanyCode,

      @ObjectModel.foreignKey.association: '_Division'
      UtilitiesContract.UtilitiesDivision as Division,

      @ObjectModel.foreignKey.association: '_UtilitiesInstallation'
      UtilitiesContract.UtilitiesInstallation,

      UtilitiesContract.ContractAccount,

      @ObjectModel.foreignKey.association: '_UtilsBillgBlockReason'
      UtilitiesContract.UtilsBillgBlockReason,

      @ObjectModel.foreignKey.association: '_UtilsBillgUnblockReason'
      UtilitiesContract.UtilsBillgUnblockReason,

      @ObjectModel.foreignKey.association: '_MeterReadingUnit'
      UtilitiesInstallationHist.MeterReadingUnit,

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

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

      @ObjectModel.foreignKey.association: '_MeterReadingReason'
      MtrRsnCorrespgToBillgRsn.MeterReadingReason,

      @Semantics.businessDate.at: true
      UtilitiesContract.UtilitiesMoveInDate,

      @Semantics.businessDate.at: true
      UtilitiesContract.UtilitiesMoveOutDate,

      @ObjectModel.foreignKey.association: '_InvcUtilsContractsJointly'
      UtilitiesContract.UtilsContrJointInvcType,

      @ObjectModel.foreignKey.association: '_UtilsAccountDetnCode'
      UtilitiesContract.UtilitiesAccountDetnCode,

      @Semantics.businessDate.to: true
      UtilitiesPortionScheduleRecd.UtilsPortionScheduleRecordDate,

      @Semantics.businessDate.at: true
      UtilitiesPortionScheduleRecd.SchedldUtilsInvcgPrintoutDate,

      case
        when (UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate >= UtilitiesContract.UtilitiesMoveInDate
         and  UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate <= UtilitiesContract.UtilitiesMoveOutDate)
            then 'X'
      end                                 as UtilitiesContractIsActive,

      case
        when (UtilitiesInstallationHist.UtilsInstHistValidityStartDate <= MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate
         and  UtilitiesInstallationHist.UtilsInstHistValidityEndDate   >= MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate)
            then 'X'
      end                                 as UtilsInstTimeSliceIsValid,

      UtilitiesContract.AuthorizationGroup,

      /* Associations */
      UtilitiesPortionScheduleRecd._UtilitiesPortion,
      UtilitiesPortionScheduleRecd._UtilitiesBillingReason,
      UtilitiesContract._CompanyCode,
      UtilitiesContract._Division,
      UtilitiesContract._UtilitiesInstallation,
      UtilitiesContract._UtilsBillgBlockReason,
      UtilitiesContract._UtilsBillgUnblockReason,
      UtilitiesInstallationHist._MeterReadingUnit,
      UtilitiesInstallationHist._UtilitiesRateCategory,
      UtilitiesInstallationHist._UtilsBillingClass,
      MtrRsnCorrespgToBillgRsn._MeterReadingReason,
      UtilitiesContract._InvcUtilsContractsJointly,
      UtilitiesContract._UtilsAccountDetnCode

}
where
  UtilitiesContract.AlternativeUtilitiesPortion = ' '
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_METERREADINGUNIT",
"I_MTRRDNGUNITSCHEDULERECORD",
"I_MTRRSNCORRESPGTOBILLGRSN",
"I_UTILITIESCONTRACT",
"I_UTILITIESINSTALLATIONHIST",
"I_UTILITIESPORTIONSCHEDULERECD"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_DIVISION",
"I_INVCUTILSCONTRACTSJOINTLY",
"I_METERREADINGREASON",
"I_METERREADINGUNIT",
"I_UTILITIESBILLINGREASON",
"I_UTILITIESINSTALLATION",
"I_UTILITIESPORTION",
"I_UTILITIESRATECATEGORY",
"I_UTILSACCOUNTDETNCODE",
"I_UTILSBILLGBLOCKREASON",
"I_UTILSBILLGUNBLOCKREASON",
"I_UTILSBILLINGCLASS"
],
"BASE":
[
"I_MTRRSNCORRESPGTOBILLGRSN",
"I_UTILITIESCONTRACT",
"I_UTILITIESINSTALLATIONHIST",
"I_UTILITIESPORTIONSCHEDULERECD"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/