I_UtilsContractToBeBilled

@EndUserText.label: 'Contract To Be Billed' //same as DDL description

@Analytics.dataCategory: #CUBE //or #FACT

@VDM.viewType: #COMPOSITE
@AbapCatalog.sqlViewName: 'IECONTRTOBEBLLD' //must start with "I"

@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_UtilsContractToBeBilled //must start with "I_"; same as DDL source name in upper-camelcase notation

as select from I_UtilitiesPortionScheduleRecd as UtilitiesPortionScheduleRecd

 inner join I_MeterReadingUnit as MeterReadingUnit on MeterReadingUnit.UtilitiesPortion = UtilitiesPortionScheduleRecd.UtilitiesPortion
 inner join I_MtrRsnCorrespgToBillgRsn as MtrRsnCorrespgToBillgRsn on MtrRsnCorrespgToBillgRsn.UtilitiesBillingReason = UtilitiesPortionScheduleRecd.UtilitiesBillingReason
 inner join I_MtrRdngUnitScheduleRecord as MtrRdngUnitScheduleRecord on MtrRdngUnitScheduleRecord.MeterReadingUnit = MeterReadingUnit.MeterReadingUnit 
                                                                     and  MtrRdngUnitScheduleRecord.MeterReadingReason = MtrRsnCorrespgToBillgRsn.MeterReadingReason 
 inner join I_UtilitiesInstallationHist as UtilitiesInstallationHist on UtilitiesInstallationHist.MeterReadingUnit = MeterReadingUnit.MeterReadingUnit 
 inner join I_UtilitiesContract as UtilitiesContract on UtilitiesContract.UtilitiesInstallation = UtilitiesInstallationHist.UtilitiesInstallation 
 
 association[1..1] to I_UtilitiesContract as _UtilitiesContract
       on $projection.UtilitiesContract = _UtilitiesContract.UtilitiesContract                                                            
{
    @ObjectModel.foreignKey.association: '_UtilitiesContract'
key UtilitiesContract.UtilitiesContract,
    @ObjectModel.foreignKey.association: '_UtilitiesPortion'
key UtilitiesPortionScheduleRecd.UtilitiesPortion,
    @Semantics.businessDate.at: true
key UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate,
    @ObjectModel.foreignKey.association:'_Division'   
    UtilitiesContract.UtilitiesDivision,
    @ObjectModel.foreignKey.association:'_CompanyCode'   
    UtilitiesContract.CompanyCode,  
    UtilitiesContract.AuthorizationGroup, 
    @DefaultAggregation: #SUM
    cast (1 as e_contrtobebilled_vdm) as NmbrOfUtilsContractsToBeBilled,
     UtilitiesPortionScheduleRecd._UtilitiesPortion,
    UtilitiesContract._Division,
    UtilitiesContract._CompanyCode,
    _UtilitiesContract

}
where 
  ( MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate > UtilitiesPortionScheduleRecd.UtilsBillgPrevSchedRecdEndDate 
     and 
    MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate <= UtilitiesPortionScheduleRecd.UtilsPortionScheduleRecordDate ) 
and
  ( UtilitiesInstallationHist.UtilsInstHistValidityStartDate <= MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate 
     and 
    UtilitiesInstallationHist.UtilsInstHistValidityEndDate >=  MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate ) 
and UtilitiesInstallationHist._UtilitiesInstallation.MeterReadingBlockingReason = ' '
and 
  ( UtilitiesContract.UtilitiesMoveInDate <= MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate 
     and
    UtilitiesContract.UtilitiesMoveOutDate >= MtrRdngUnitScheduleRecord.ScheduledMeterReadingDate ) 
and UtilitiesContract.AlternativeUtilitiesPortion = ' ' 
 
// contracts to the alternative portion

union all select from I_UtilitiesPortionScheduleRecd as UtilitiesPortionScheduleRecd

 inner join  I_UtilitiesContract as UtilitiesContract on UtilitiesPortionScheduleRecd.UtilitiesPortion = UtilitiesContract.AlternativeUtilitiesPortion
 
 association[1..1] to I_UtilitiesContract as _UtilitiesContract
       on $projection.UtilitiesContract = _UtilitiesContract.UtilitiesContract 
{
      @ObjectModel.foreignKey.association: '_UtilitiesContract'
key   UtilitiesContract.UtilitiesContract,
      @ObjectModel.foreignKey.association: '_UtilitiesPortion'
key   UtilitiesPortionScheduleRecd.UtilitiesPortion,
      @Semantics.businessDate.to: true
key   UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate,
      @ObjectModel.foreignKey.association:'_Division'   
      UtilitiesContract.UtilitiesDivision,
      @ObjectModel.foreignKey.association:'_CompanyCode'   
      UtilitiesContract.CompanyCode,  
      UtilitiesContract.AuthorizationGroup, //CBnew

      @DefaultAggregation: #SUM
      cast (1 as e_contrtobebilled_vdm) as NmbrOfUtilsContractsToBeBilled,
      UtilitiesPortionScheduleRecd._UtilitiesPortion,
      UtilitiesContract._Division,
      UtilitiesContract._CompanyCode,
      _UtilitiesContract
    
}

where 
  ( UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate >= UtilitiesContract.UtilitiesMoveInDate  
     and
    UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate <= UtilitiesContract.UtilitiesMoveOutDate );
                                                                                            
  
  
  
  
  
  
  
  
  
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_METERREADINGUNIT",
"I_MTRRDNGUNITSCHEDULERECORD",
"I_MTRRSNCORRESPGTOBILLGRSN",
"I_UTILITIESCONTRACT",
"I_UTILITIESINSTALLATION",
"I_UTILITIESINSTALLATIONHIST",
"I_UTILITIESPORTIONSCHEDULERECD"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_DIVISION",
"I_UTILITIESCONTRACT",
"I_UTILITIESPORTION"
],
"BASE":
[
"I_UTILITIESCONTRACT",
"I_UTILITIESPORTIONSCHEDULERECD"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/