I_OpenUtilsBillgDocAndOrd

@EndUserText.label: 'Open Utilities Billing Document and Order'
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #CUBE
@AbapCatalog.sqlViewName: 'IEOPENBILLGS'
@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_OpenUtilsBillgDocAndOrd
  as select from I_OpenUtilsBillgDocAndOrdData as OpenUtilsBillgDocAndOrdData
    inner join   I_ContractAccountPartner      as ContractAccountPartner   on ContractAccountPartner.ContractAccount = OpenUtilsBillgDocAndOrdData.ContractAccount
    inner join   I_MtrRsnCorrespgToBillgRsn    as MtrRsnCorrespgToBillgRsn on OpenUtilsBillgDocAndOrdData.UtilitiesBillingReason = MtrRsnCorrespgToBillgRsn.UtilitiesBillingReason
  // Cardinality is 0..1 but due to ATC checks is set to 0..*

  association [0..*] to I_UtilsInstIntAndExtPtOfDeliv as _UtilsInstIntAndExtPtOfDeliv on  $projection.UtilitiesInstallation                         = _UtilsInstIntAndExtPtOfDeliv.UtilitiesInstallation
                                                                                      and _UtilsInstIntAndExtPtOfDeliv.UtilsPoDAllocValidityEndDate = '99991231'
                                                                                      and _UtilsInstIntAndExtPtOfDeliv.UtilsPoDAllocValidityEndTime = '235959'
  //Interim Solution because of performance problems: access with timestamp (31.12.9999, 23:59) instead of move out date

  // and (

  //        (

  //           $projection.UtilitiesMoveOutDate <  _UtilsInstIntAndExtPtOfDeliv.UtilsPoDAllocValidityEndDate and

  //           $projection.UtilitiesMoveOutDate >= _UtilsInstIntAndExtPtOfDeliv.UtilsPoDAllocValidityStartDate

  //        ) or

  //        (

  //           $projection.UtilitiesMoveOutDate = _UtilsInstIntAndExtPtOfDeliv.UtilsPoDAllocValidityEndDate and

  //           _UtilsInstIntAndExtPtOfDeliv.UtilsPoDAllocValidityEndTime ='235959'

  //        )

  //)

  association [0..1] to I_ImplsblMtrRdngResultsCntr   as _ImplsblMtrRdngResultsCntr   on  $projection.UtilitiesInstallation         = _ImplsblMtrRdngResultsCntr.UtilitiesInstallation
                                                                                      and $projection.MeterReadingReason            = _ImplsblMtrRdngResultsCntr.MeterReadingReason
                                                                                      and $projection.ScheduledUtilitiesBillingDate = _ImplsblMtrRdngResultsCntr.ScheduledUtilitiesBillingDate
  association [0..1] to I_MissingMtrRdngResultsCntr   as _MissingMtrRdngResultsCntr   on  $projection.UtilitiesInstallation         = _MissingMtrRdngResultsCntr.UtilitiesInstallation
                                                                                      and $projection.MeterReadingReason            = _MissingMtrRdngResultsCntr.MeterReadingReason
                                                                                      and $projection.ScheduledUtilitiesBillingDate = _MissingMtrRdngResultsCntr.ScheduledUtilitiesBillingDate
                                                                                      and $projection.ScheduledMeterReadingDate     = _MissingMtrRdngResultsCntr.ScheduledMeterReadingDate
                                                                                      and $projection.MeterReadingUnit              = _MissingMtrRdngResultsCntr.MeterReadingUnit
  association [1..1] to I_OpnUtilsBillgDocAndOrdType  as _OpnUtilsBillgDocAndOrdType  on  $projection.OpnUtilsBillgDocAndOrdType = _OpnUtilsBillgDocAndOrdType.OpnUtilsBillgDocAndOrdType
  association [1..1] to I_MeterReadingStsGrp          as _MeterReadingStsGrp          on  $projection.MeterReadingStatusGroup = _MeterReadingStsGrp.MeterReadingStatusGroup

{
  key OpenUtilsBillgDocAndOrdData.UtilitiesContract,

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

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

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

  key OpenUtilsBillgDocAndOrdData.UtilsInstHistValidityEndDate,

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

      @ObjectModel.foreignKey.association: '_BusinessPartner'
  key ContractAccountPartner.BusinessPartner,

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

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

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

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

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

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

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

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

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

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

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

      OpenUtilsBillgDocAndOrdData.UtilitiesMoveInDate,

      OpenUtilsBillgDocAndOrdData.UtilitiesMoveOutDate,

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

      //      @ObjectModel.foreignKey.association: '_ContrAcc'

      //      ContractAccountPartner.ContractAccount,


      //      @ObjectModel.foreignKey.association: '_BusinessPartner'

      //      ContractAccountPartner.BusinessPartner,


      @ObjectModel.foreignKey.association: '_OpnUtilsBillgDocAndOrdType'
      cast ( OpenUtilsBillgDocAndOrdData.OpnUtilsBillgDocAndOrdType as e_opnbllgdocandord_vdm ) as OpnUtilsBillgDocAndOrdType,

      @ObjectModel.foreignKey.association: '_MeterReadingStsGrp'
      case
        when OpenUtilsBillgDocAndOrdData.OpnUtilsBillgDocAndOrdType = '01'
          then
            case
              when (_MissingMtrRdngResultsCntr.UtilitiesInstallation is not null and _ImplsblMtrRdngResultsCntr.UtilitiesInstallation is null)
                 then cast( '06' as e_mrstatus_group_vdm )  //Missing

              when (_MissingMtrRdngResultsCntr.UtilitiesInstallation is not null and _ImplsblMtrRdngResultsCntr.UtilitiesInstallation is not null)
                 then cast( '07' as e_mrstatus_group_vdm )  //Missing and Implausible

              when (_MissingMtrRdngResultsCntr.UtilitiesInstallation is null and _ImplsblMtrRdngResultsCntr.UtilitiesInstallation is not null)
                 then cast( '08' as e_mrstatus_group_vdm )  //Implausible

            end
        when OpenUtilsBillgDocAndOrdData.OpnUtilsBillgDocAndOrdType <> '01'
          then cast( '09' as e_mrstatus_group_vdm )  //Plausible

      end                                                                                       as MeterReadingStatusGroup,

      case
        when OpnUtilsBillgDocAndOrdType = '01' and _ImplsblMtrRdngResultsCntr.NmbrOfImplausibleMtrRdngRslts is not null
             then _ImplsblMtrRdngResultsCntr.NmbrOfImplausibleMtrRdngRslts
        when OpnUtilsBillgDocAndOrdType = '01' and _ImplsblMtrRdngResultsCntr.NmbrOfImplausibleMtrRdngRslts is null
             then 0
      end                                                                                       as NmbrOfImplausibleMtrRdngRslts,

      OpenUtilsBillgDocAndOrdData.AuthorizationGroup,

      @DefaultAggregation: #SUM
      cast(1 as e_opbbillgdocsandords_nmbr_vdm)                                                 as NrOfOpenUtilsBillgDocsAndOrds,

      @Aggregation: { default: #COUNT_DISTINCT, referenceElement:  [ 'UtilitiesContract' ] }
      case
        when (OpenUtilsBillgDocAndOrdData.UtilsBillgBlockReason <> '' and OpenUtilsBillgDocAndOrdData.UtilsBillgUnblockReason = '')
          then cast(1 as e_nrofutilscontr_vdm)
      end                                                                                       as NmbrOfUtilsContrBlkdForBillg,


      /* Associations */
      OpenUtilsBillgDocAndOrdData._UtilitiesInstallation,
      OpenUtilsBillgDocAndOrdData._UtilitiesBillingReason,
      OpenUtilsBillgDocAndOrdData._UtilitiesPortion,
      OpenUtilsBillgDocAndOrdData._CompanyCode,
      OpenUtilsBillgDocAndOrdData._Division,
      OpenUtilsBillgDocAndOrdData._UtilsBillgBlockReason,
      OpenUtilsBillgDocAndOrdData._UtilsBillgUnblockReason,
      OpenUtilsBillgDocAndOrdData._MeterReadingUnit,
      OpenUtilsBillgDocAndOrdData._UtilitiesRateCategory,
      OpenUtilsBillgDocAndOrdData._UtilsBillingClass,
      MtrRsnCorrespgToBillgRsn._MeterReadingReason,
      OpenUtilsBillgDocAndOrdData._UtilsAccountDetnCode,
      ContractAccountPartner._ContrAcc,
      ContractAccountPartner._BusinessPartner,
      _UtilsInstIntAndExtPtOfDeliv,
      _ImplsblMtrRdngResultsCntr,
      _MissingMtrRdngResultsCntr,
      _OpnUtilsBillgDocAndOrdType,
      _MeterReadingStsGrp
}
where
      OpenUtilsBillgDocAndOrdData.UtilitiesContractIsActive  = 'X' //Contract is Active

  and OpenUtilsBillgDocAndOrdData.UtilsInstTimeSliceIsValid  = 'X' //Installation Time Slice is Valid

  and ContractAccountPartner._RelshpOfBP.CABPIsAccountHolder = 'X' //Business Partner is Contract Account Holder

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CONTRACCTPRTNRELSHPTYPE",
"I_CONTRACTACCOUNTPARTNER",
"I_IMPLSBLMTRRDNGRESULTSCNTR",
"I_MISSINGMTRRDNGRESULTSCNTR",
"I_MTRRSNCORRESPGTOBILLGRSN",
"I_OPENUTILSBILLGDOCANDORDDATA"
],
"ASSOCIATED":
[
"I_BUSINESSPARTNER",
"I_COMPANYCODE",
"I_CONTRACTACCOUNTHEADER",
"I_DIVISION",
"I_IMPLSBLMTRRDNGRESULTSCNTR",
"I_METERREADINGREASON",
"I_METERREADINGSTSGRP",
"I_METERREADINGUNIT",
"I_MISSINGMTRRDNGRESULTSCNTR",
"I_OPNUTILSBILLGDOCANDORDTYPE",
"I_UTILITIESBILLINGREASON",
"I_UTILITIESINSTALLATION",
"I_UTILITIESPORTION",
"I_UTILITIESRATECATEGORY",
"I_UTILSACCOUNTDETNCODE",
"I_UTILSBILLGBLOCKREASON",
"I_UTILSBILLGUNBLOCKREASON",
"I_UTILSBILLINGCLASS",
"I_UTILSINSTINTANDEXTPTOFDELIV"
],
"BASE":
[
"I_CONTRACTACCOUNTPARTNER",
"I_MTRRSNCORRESPGTOBILLGRSN",
"I_OPENUTILSBILLGDOCANDORDDATA"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/