I_UTILSBILLINGORDER

@EndUserText.label: 'Utilities Billing Order'
@VDM.viewType: #COMPOSITE
@AbapCatalog.sqlViewName: 'IEINBLLGORDER'
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #('TRANSACTIONAL_DATA')
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
define view I_UtilsBillingOrder
  as select from    I_UtilitiesBillingOrder        as UtilitiesBillingOrder
    inner join      I_UtilitiesContract            as UtilitiesContract            on UtilitiesContract.UtilitiesInstallation = UtilitiesBillingOrder.UtilitiesInstallation
  /* If the Billing Order is not Periodic Billing related, all fields from the join will be blank */
    left outer join I_UtilitiesPortionScheduleRecd as UtilitiesPortionScheduleRecd on  UtilitiesPortionScheduleRecd.UtilitiesPortion              = UtilitiesBillingOrder.UtilitiesPortion
                                                                                   and UtilitiesPortionScheduleRecd.ScheduledUtilitiesBillingDate = UtilitiesBillingOrder.ScheduledUtilitiesBillingDate
                                                                                   and UtilitiesPortionScheduleRecd.UtilitiesBillingReason        = UtilitiesBillingOrder.UtilitiesBillingReason
    inner join      I_UtilitiesInstallationHist    as UtilitiesInstallationHist    on UtilitiesInstallationHist.UtilitiesInstallation = UtilitiesBillingOrder.UtilitiesInstallation
    inner join      I_ContractAccountPartner       as ContractAccountPartner       on ContractAccountPartner.ContractAccount = UtilitiesContract.ContractAccount
  association [0..1] to I_NonBllbleUtilsBillgOrderCntr as _NonBllbleUtilsBillgOrderCntr on  $projection.UtilitiesInstallation = _NonBllbleUtilsBillgOrderCntr.UtilitiesInstallation
                                                                                        and $projection.UtilitiesContract     = _NonBllbleUtilsBillgOrderCntr.UtilitiesContract
  association [1..1] to I_UtilsBillgOrdChkBfrBilling   as _UtilsBillgOrdChkBfrBilling   on  $projection.UtilsBillgOrdChkBfrBilling = _UtilsBillgOrdChkBfrBilling.UtilsBillgOrdChkBfrBilling
  /*Cardinality should be 0..1, but due to ATC checks set to 0..n */
  association [0..*] to I_UtilsInstIntAndExtPtOfDeliv  as _UtilsInstIntAndExtPtOfDeliv  on  $projection.UtilitiesInstallation                         = _UtilsInstIntAndExtPtOfDeliv.UtilitiesInstallation
                                                                                        and _UtilsInstIntAndExtPtOfDeliv.UtilsPoDAllocValidityEndDate = '99991231'
                                                                                        and _UtilsInstIntAndExtPtOfDeliv.UtilsPoDAllocValidityEndTime = '235959'
{
  key UtilitiesContract.UtilitiesContract,

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

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

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

  key UtilitiesInstallationHist.UtilsInstHistValidityEndDate,

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

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

      UtilitiesBillingOrder.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'
      UtilitiesBillingOrder.MeterReadingUnit,

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

      cast(UtilitiesContract.UtilsContrIsBilledManually as e_contrisblldmnlly_vdm preserving type) as UtilsContrIsBilledManually,

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

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

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

      //      ContractAccountPartner.ContractAccount,


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

      UtilitiesContract.UtilitiesMoveInDate,

      UtilitiesContract.UtilitiesMoveOutDate,

      @ObjectModel.foreignKey.association: '_UtilsBillingOrderStatus'
      UtilitiesBillingOrder.UtilsBillingOrderStatus,

      @ObjectModel.foreignKey.association: '_UtilsBillingOrderIndicator'
      UtilitiesBillingOrder.UtilsBillingOrderIndicator,

      @ObjectModel.foreignKey.association: '_SuppressedUtilsBillgReason'
      UtilitiesBillingOrder.SuppressedUtilsBillgReason,

      UtilitiesBillingOrder.SuppressedUtilsSchedldBillgDte,

      UtilitiesBillingOrder.SuppressedSchedldMtrRdngDte,

      @ObjectModel.foreignKey.association: '_UtilsContrSpcfcBdgtBillgCyc'
      UtilitiesContract.UtilsContrSpcfcBdgtBillgCycle,

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

      @ObjectModel.foreignKey.association: '_UtilsBillgOutsortingGroup'
      UtilitiesContract.UtilsBillgOutsortingReason,

      _UtilsInstIntAndExtPtOfDeliv.UtilsExternalPointOfDelivery,

      @ObjectModel.foreignKey.association: '_UtilsBillgOrdChkBfrBilling'
      case
        when UtilitiesContract.UtilsBillgBlockReason != ''
             and UtilitiesContract.UtilsBillgUnblockReason = ''
              then cast('01' as e_prebllgchkbllgord_vdm) // Contract is Blocked

        when (
                UtilitiesContract.UtilsBillgBlockReason  = ''
                or UtilitiesContract.UtilsBillgUnblockReason != ''
             )
             and _NonBllbleUtilsBillgOrderCntr.UtilitiesInstallation is not null
             and _NonBllbleUtilsBillgOrderCntr.ScheduledUtilitiesBillingDate < UtilitiesBillingOrder.ScheduledUtilitiesBillingDate
             and UtilitiesBillingOrder.UtilsBillingOrderStatus = '2'
              then cast('02' as e_prebllgchkbllgord_vdm) // Non-Billable Order Exist (only for billable)

        when (
                UtilitiesContract.UtilsBillgBlockReason  = ''
                or UtilitiesContract.UtilsBillgUnblockReason != ''
             )
             and
             (
                _NonBllbleUtilsBillgOrderCntr.UtilitiesInstallation is null
                or _NonBllbleUtilsBillgOrderCntr.ScheduledUtilitiesBillingDate >= UtilitiesBillingOrder.ScheduledUtilitiesBillingDate
                or UtilitiesBillingOrder.UtilsBillingOrderStatus != '2'
             )
              then ' ' //No Notes

      end                                                                                          as UtilsBillgOrdChkBfrBilling,

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

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

      UtilitiesContract.AuthorizationGroup,

      @DefaultAggregation: #SUM
      cast(1 as e_billgords_nmbr_vdm)                                                              as NrOfUtilitiesBillingOrders,

      /* Associations */
      UtilitiesBillingOrder._UtilitiesInstallation,
      UtilitiesBillingOrder._UtilitiesBillingReason,
      UtilitiesBillingOrder._UtilitiesPortion,
      UtilitiesContract._CompanyCode,
      UtilitiesContract._Division,
      UtilitiesContract._UtilsBillgBlockReason,
      UtilitiesContract._UtilsBillgUnblockReason,
      UtilitiesBillingOrder._MeterReadingUnit,
      UtilitiesInstallationHist._UtilitiesRateCategory,
      UtilitiesInstallationHist._UtilsBillingClass,
      ContractAccountPartner._ContrAcc,
      ContractAccountPartner._BusinessPartner,
      UtilitiesBillingOrder._UtilsBillingOrderStatus,
      UtilitiesBillingOrder._UtilsBillingOrderIndicator,
      UtilitiesBillingOrder._SuppressedUtilsBillgReason,
      UtilitiesContract._UtilsContrSpcfcBdgtBillgCyc,
      UtilitiesContract._UtilsAccountDetnCode,
      UtilitiesContract._UtilsBillgOutsortingGroup,
      _NonBllbleUtilsBillgOrderCntr,
      _UtilsBillgOrdChkBfrBilling,
      _UtilsInstIntAndExtPtOfDeliv
}
where
  ContractAccountPartner._RelshpOfBP.CABPIsAccountHolder = 'X'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CONTRACCTPRTNRELSHPTYPE",
"I_CONTRACTACCOUNTPARTNER",
"I_NONBLLBLEUTILSBILLGORDERCNTR",
"I_UTILITIESBILLINGORDER",
"I_UTILITIESCONTRACT",
"I_UTILITIESINSTALLATIONHIST",
"I_UTILITIESPORTIONSCHEDULERECD",
"I_UTILSINSTINTANDEXTPTOFDELIV"
],
"ASSOCIATED":
[
"I_BUSINESSPARTNER",
"I_COMPANYCODE",
"I_CONTRACTACCOUNTHEADER",
"I_DIVISION",
"I_METERREADINGUNIT",
"I_NONBLLBLEUTILSBILLGORDERCNTR",
"I_UTILITIESBILLINGREASON",
"I_UTILITIESINSTALLATION",
"I_UTILITIESPORTION",
"I_UTILITIESRATECATEGORY",
"I_UTILSACCOUNTDETNCODE",
"I_UTILSBILLGBLOCKREASON",
"I_UTILSBILLGORDCHKBFRBILLING",
"I_UTILSBILLGOUTSORTINGGROUP",
"I_UTILSBILLGUNBLOCKREASON",
"I_UTILSBILLINGCLASS",
"I_UTILSBILLINGORDERINDICATOR",
"I_UTILSBILLINGORDERSTATUS",
"I_UTILSCONTRSPCFCBDGTBILLGCYC",
"I_UTILSINSTINTANDEXTPTOFDELIV"
],
"BASE":
[
"I_CONTRACTACCOUNTPARTNER",
"I_UTILITIESBILLINGORDER",
"I_UTILITIESCONTRACT",
"I_UTILITIESINSTALLATIONHIST"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/