I_OpenUtilsBillgDocAndOrdData

DDL: I_OPENUTILSBILLGDOCANDORDDATA SQL: IEOPENBILLGSU Type: view COMPOSITE

Open Utilities Billing Document and Order Data

I_OpenUtilsBillgDocAndOrdData is a Composite CDS View (Cube) that provides data about "Open Utilities Billing Document and Order Data" in SAP S/4HANA. It reads from 4 data sources (I_UtilitiesBillingDocumentTP, I_UtilsBillingOrder, I_UtilitiesContract, I_UtilitiesInstallationHist) and exposes 66 fields with key fields UtilitiesContract, UtilitiesInstallation, ScheduledUtilitiesBillingDate, UtilitiesBillingReason, UtilsInstHistValidityEndDate. It has 5 associations to related views.

4 data source(s) 66 fields

Data Sources (4)

SourceAliasJoin Type
I_UtilitiesBillingDocumentTP UtilitiesBillingDocumentTP union_all
I_UtilsBillingOrder UtilitiesBillingOrder from
I_UtilitiesContract UtilitiesContract inner
I_UtilitiesInstallationHist UtilitiesInstallationHist inner

Associations (5)

CardinalityTargetAliasCondition
[1..1] I_UtilitiesBillingReason _UtilitiesBillingReason $projection.UtilitiesBillingReason = _UtilitiesBillingReason.UtilitiesBillingReason
[1..1] I_OpnUtilsBillgDocAndOrdType _OpnUtilsBillgDocAndOrdType $projection.OpnUtilsBillgDocAndOrdType = _OpnUtilsBillgDocAndOrdType.OpnUtilsBillgDocAndOrdType
[1..1] I_UtilitiesInstallation _UtilitiesInstallation $projection.UtilitiesInstallation = _UtilitiesInstallation.UtilitiesInstallation
[1..1] I_MeterReadingUnit _MeterReadingUnit $projection.MeterReadingUnit = _MeterReadingUnit.MeterReadingUnit
[1..1] I_UtilitiesPortion _UtilitiesPortion $projection.UtilitiesPortion = _UtilitiesPortion.UtilitiesPortion

Annotations (10)

NameValueLevelField
EndUserText.label Open Utilities Billing Document and Order Data view
VDM.viewType #COMPOSITE view
Analytics.dataCategory #CUBE view
AbapCatalog.sqlViewName IEOPENBILLGSU view
AccessControl.authorizationCheck #CHECK view
ObjectModel.usageType.serviceQuality #D view
ObjectModel.usageType.sizeCategory #XXL view
ObjectModel.usageType.dataClass #MIXED view
ClientHandling.algorithm #SESSION_VARIABLE view
Metadata.allowExtensions true view

Fields (66)

KeyFieldSource TableSource FieldDescription
KEY UtilitiesContract I_UtilsBillingOrder UtilitiesContract
KEY UtilitiesInstallation I_UtilsBillingOrder UtilitiesInstallation
KEY ScheduledUtilitiesBillingDate I_UtilsBillingOrder ScheduledUtilitiesBillingDate
KEY UtilitiesBillingReason I_UtilsBillingOrder UtilitiesBillingReason
KEY UtilsInstHistValidityEndDate I_UtilsBillingOrder UtilsInstHistValidityEndDate
KEY ContractAccount I_UtilsBillingOrder ContractAccount
UtilitiesPortion I_UtilsBillingOrder UtilitiesPortion
ScheduledMeterReadingDate I_UtilsBillingOrder ScheduledMeterReadingDate
CompanyCode I_UtilsBillingOrder CompanyCode
Division I_UtilsBillingOrder Division
UtilsBillgBlockReason I_UtilsBillingOrder UtilsBillgBlockReason
UtilsBillgUnblockReason I_UtilsBillingOrder UtilsBillgUnblockReason
MeterReadingUnit I_UtilsBillingOrder MeterReadingUnit
UtilitiesRateCategory I_UtilsBillingOrder UtilitiesRateCategory
UtilitiesBillingClass I_UtilsBillingOrder UtilitiesBillingClass
UtilsPortionScheduleRecordDate I_UtilsBillingOrder UtilsPortionScheduleRecordDate
UtilitiesMoveInDate I_UtilsBillingOrder UtilitiesMoveInDate
UtilitiesMoveOutDate I_UtilsBillingOrder UtilitiesMoveOutDate
UtilitiesAccountDetnCode I_UtilsBillingOrder UtilitiesAccountDetnCode
UtilitiesContractIsActive I_UtilsBillingOrder UtilitiesContractIsActive
UtilsInstTimeSliceIsValid I_UtilsBillingOrder UtilsInstTimeSliceIsValid
AuthorizationGroup I_UtilsBillingOrder AuthorizationGroup
_UtilitiesInstallation I_UtilsBillingOrder _UtilitiesInstallation
_UtilitiesBillingReason _UtilitiesBillingReason
_UtilitiesPortion I_UtilsBillingOrder _UtilitiesPortion
_CompanyCode I_UtilsBillingOrder _CompanyCode
_Division I_UtilsBillingOrder _Division
_UtilsBillgBlockReason I_UtilsBillingOrder _UtilsBillgBlockReason
_UtilsBillgUnblockReason I_UtilsBillingOrder _UtilsBillgUnblockReason
_MeterReadingUnit I_UtilsBillingOrder _MeterReadingUnit
_UtilitiesRateCategory I_UtilsBillingOrder _UtilitiesRateCategory
_UtilsBillingClass I_UtilsBillingOrder _UtilsBillingClass
_UtilsAccountDetnCode I_UtilsBillingOrder _UtilsAccountDetnCode
UtilitiesContract
KEY UtilitiesInstallation I_UtilitiesBillingDocumentTP UtilitiesInstallation
KEY ScheduledUtilitiesBillingDate I_UtilitiesBillingDocumentTP ScheduledUtilitiesBillingDate
KEY UtilitiesBillingReason I_UtilitiesBillingDocumentTP UtilitiesBillingReason
KEY UtilsInstHistValidityEndDate I_UtilitiesInstallationHist UtilsInstHistValidityEndDate
KEY ContractAccount I_UtilitiesContract ContractAccount
UtilitiesPortion I_UtilitiesBillingDocumentTP UtilitiesPortion
ScheduledMeterReadingDate I_UtilitiesBillingDocumentTP ScheduledMeterReadingDate
CompanyCode I_UtilitiesContract CompanyCode
Division I_UtilitiesContract UtilitiesDivision
UtilsBillgBlockReason I_UtilitiesContract UtilsBillgBlockReason
UtilsBillgUnblockReason I_UtilitiesContract UtilsBillgUnblockReason
MeterReadingUnit I_UtilitiesBillingDocumentTP MeterReadingUnit
UtilitiesRateCategory I_UtilitiesInstallationHist UtilitiesRateCategory
UtilitiesBillingClass I_UtilitiesInstallationHist UtilitiesBillingClass
UtilsPortionScheduleRecordDate I_UtilitiesBillingDocumentTP UtilsBillgPeriodEndDate
UtilitiesMoveInDate I_UtilitiesContract UtilitiesMoveInDate
UtilitiesMoveOutDate I_UtilitiesContract UtilitiesMoveOutDate
UtilitiesAccountDetnCode I_UtilitiesContract UtilitiesAccountDetnCode
OpnUtilsBillgDocAndOrdType
AuthorizationGroup I_UtilitiesContract AuthorizationGroup
_UtilitiesInstallation _UtilitiesInstallation
_UtilitiesBillingReason I_UtilitiesBillingDocumentTP _UtilitiesBillingReason
_UtilitiesPortion _UtilitiesPortion
_CompanyCode I_UtilitiesContract _CompanyCode
_Division I_UtilitiesContract _Division
_UtilsBillgBlockReason I_UtilitiesContract _UtilsBillgBlockReason
_UtilsBillgUnblockReason I_UtilitiesContract _UtilsBillgUnblockReason
_MeterReadingUnit _MeterReadingUnit
_UtilitiesRateCategory I_UtilitiesInstallationHist _UtilitiesRateCategory
_UtilsBillingClass I_UtilitiesInstallationHist _UtilsBillingClass
_UtilsAccountDetnCode I_UtilitiesContract _UtilsAccountDetnCode
_OpnUtilsBillgDocAndOrdType _OpnUtilsBillgDocAndOrdType 
@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'

Learn More