I_OpenUtilsBillgDocAndOrdData

@EndUserText.label: 'Open Utilities Billing Document and Order Data'
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #CUBE
@AbapCatalog.sqlViewName: 'IEOPENBILLGSU'
@AccessControl.authorizationCheck: #CHECK
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@AccessControl.personalData.blocking: #('TRANSACTIONAL_DATA')
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.allowExtensions:true
define view I_OpenUtilsBillgDocAndOrdData
  as select from I_UtilsBillingOrder as UtilitiesBillingOrder
  association [1..1] to I_UtilitiesBillingReason     as _UtilitiesBillingReason     on $projection.UtilitiesBillingReason = _UtilitiesBillingReason.UtilitiesBillingReason
  association [1..1] to I_OpnUtilsBillgDocAndOrdType as _OpnUtilsBillgDocAndOrdType on $projection.OpnUtilsBillgDocAndOrdType = _OpnUtilsBillgDocAndOrdType.OpnUtilsBillgDocAndOrdType
{
  key UtilitiesBillingOrder.UtilitiesContract,

      @ObjectModel.foreignKey.association:'_UtilitiesInstallation'
  key UtilitiesBillingOrder.UtilitiesInstallation,

      @Semantics.businessDate.at: true
  key UtilitiesBillingOrder.ScheduledUtilitiesBillingDate,

      @ObjectModel.foreignKey.association:'_UtilitiesBillingReason'
  key UtilitiesBillingOrder.UtilitiesBillingReason,

  key UtilitiesBillingOrder.UtilsInstHistValidityEndDate,

  key UtilitiesBillingOrder.ContractAccount,

      @ObjectModel.foreignKey.association:'_UtilitiesPortion'
      UtilitiesBillingOrder.UtilitiesPortion,

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

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

      @ObjectModel.foreignKey.association:'_Division'
      UtilitiesBillingOrder.Division,

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

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

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

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

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

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

      UtilitiesBillingOrder.UtilitiesMoveInDate,

      UtilitiesBillingOrder.UtilitiesMoveOutDate,

      //      UtilitiesBillingOrder.ContractAccount,


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

      case when UtilitiesBillingOrder.UtilsBillingOrderStatus = '1'
             then '01'   //Incomplete

           when (UtilitiesBillingOrder.UtilsBillingOrderStatus = '2' and UtilitiesBillingOrder.UtilsBillingOrderIndicator <> 'X')
             then '02'   //Billable

           when (UtilitiesBillingOrder.UtilsBillingOrderStatus = '2' and UtilitiesBillingOrder.UtilsBillingOrderIndicator = 'X')
             then '03'   //Billed with Error

      end as OpnUtilsBillgDocAndOrdType,

      UtilitiesBillingOrder.UtilitiesContractIsActive,

      UtilitiesBillingOrder.UtilsInstTimeSliceIsValid,

      UtilitiesBillingOrder.AuthorizationGroup,

      /* Associations */
      UtilitiesBillingOrder._UtilitiesInstallation,
      _UtilitiesBillingReason,
      UtilitiesBillingOrder._UtilitiesPortion,
      UtilitiesBillingOrder._CompanyCode,
      UtilitiesBillingOrder._Division,
      UtilitiesBillingOrder._UtilsBillgBlockReason,
      UtilitiesBillingOrder._UtilsBillgUnblockReason,
      UtilitiesBillingOrder._MeterReadingUnit,
      UtilitiesBillingOrder._UtilitiesRateCategory,
      UtilitiesBillingOrder._UtilsBillingClass,
      UtilitiesBillingOrder._UtilsAccountDetnCode,
      _OpnUtilsBillgDocAndOrdType
}
where
      UtilitiesBillingOrder.UtilitiesContractIsActive = 'X'
  and UtilitiesBillingOrder.UtilsInstTimeSliceIsValid = 'X'

union all

select from  I_UtilitiesBillingDocumentTP as UtilitiesBillingDocumentTP
  inner join I_UtilitiesContract          as UtilitiesContract         on UtilitiesBillingDocumentTP.UtilitiesContract = UtilitiesContract.UtilitiesContract
  inner join I_UtilitiesInstallationHist  as UtilitiesInstallationHist on UtilitiesInstallationHist.UtilitiesInstallation = UtilitiesBillingDocumentTP.UtilitiesInstallation
association [1..1] to I_UtilitiesInstallation      as _UtilitiesInstallation      on $projection.UtilitiesInstallation = _UtilitiesInstallation.UtilitiesInstallation
association [1..1] to I_MeterReadingUnit           as _MeterReadingUnit           on $projection.MeterReadingUnit = _MeterReadingUnit.MeterReadingUnit
association [1..1] to I_UtilitiesPortion           as _UtilitiesPortion           on $projection.UtilitiesPortion = _UtilitiesPortion.UtilitiesPortion
association [1..1] to I_OpnUtilsBillgDocAndOrdType as _OpnUtilsBillgDocAndOrdType on $projection.OpnUtilsBillgDocAndOrdType = _OpnUtilsBillgDocAndOrdType.OpnUtilsBillgDocAndOrdType

{
  key UtilitiesContract.UtilitiesContract,

      @ObjectModel.foreignKey.association:'_UtilitiesInstallation'
  key UtilitiesBillingDocumentTP.UtilitiesInstallation,

      @Semantics.businessDate.at: true
  key UtilitiesBillingDocumentTP.ScheduledUtilitiesBillingDate,

      @ObjectModel.foreignKey.association:'_UtilitiesBillingReason'
  key UtilitiesBillingDocumentTP.UtilitiesBillingReason,

  key UtilitiesInstallationHist.UtilsInstHistValidityEndDate,

      @ObjectModel.foreignKey.association: '_ContrAcc'
  key UtilitiesContract.ContractAccount,

      @ObjectModel.foreignKey.association:'_UtilitiesPortion'
      UtilitiesBillingDocumentTP.UtilitiesPortion,

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

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

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

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

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

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

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

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

      @Semantics.businessDate.to: true
      UtilitiesBillingDocumentTP.UtilsBillgPeriodEndDate as UtilsPortionScheduleRecordDate,

      UtilitiesContract.UtilitiesMoveInDate,

      UtilitiesContract.UtilitiesMoveOutDate,

      //      UtilitiesContract.ContractAccount,


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

      '04'                                               as OpnUtilsBillgDocAndOrdType, //Outsorted


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

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

      UtilitiesContract.AuthorizationGroup,


      /* Associations */
      _UtilitiesInstallation,
      UtilitiesBillingDocumentTP._UtilitiesBillingReason,
      _UtilitiesPortion,
      UtilitiesContract._CompanyCode,
      UtilitiesContract._Division,
      UtilitiesContract._UtilsBillgBlockReason,
      UtilitiesContract._UtilsBillgUnblockReason,
      _MeterReadingUnit,
      UtilitiesInstallationHist._UtilitiesRateCategory,
      UtilitiesInstallationHist._UtilsBillingClass,
      UtilitiesContract._UtilsAccountDetnCode,
      _OpnUtilsBillgDocAndOrdType

}
where
      UtilitiesBillingDocumentTP.UtilsBillgDocIsNotReleased =  'X'
  and UtilitiesBillingDocumentTP.UtilsBillgDocReversalDate  =  '00000000'
  and UtilitiesBillingDocumentTP.UtilsBillgSimulationReason =  ' '
  and UtilitiesBillingDocumentTP.UtilitiesBillingReason     <> '06'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_UTILITIESBILLINGDOCUMENTTP",
"I_UTILITIESCONTRACT",
"I_UTILITIESINSTALLATIONHIST",
"I_UTILSBILLINGORDER"
],
"ASSOCIATED":
[
"I_COMPANYCODE",
"I_DIVISION",
"I_METERREADINGUNIT",
"I_OPNUTILSBILLGDOCANDORDTYPE",
"I_UTILITIESBILLINGREASON",
"I_UTILITIESINSTALLATION",
"I_UTILITIESPORTION",
"I_UTILITIESRATECATEGORY",
"I_UTILSACCOUNTDETNCODE",
"I_UTILSBILLGBLOCKREASON",
"I_UTILSBILLGUNBLOCKREASON",
"I_UTILSBILLINGCLASS"
],
"BASE":
[
"I_UTILSBILLINGORDER"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/