@EndUserText.label: 'Utilities Contract to Be Billed'
@VDM.viewType: #COMPOSITE
@AbapCatalog.sqlViewName: 'IECONTRTBBILLED'
@AccessControl.authorizationCheck: #CHECK
@Analytics.dataCategory: #CUBE
@AccessControl.personalData.blocking: #REQUIRED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.dataClass: #MIXED
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.allowExtensions:true
define view I_UtilsContrToBeBlld
as select from I_UtilsContrToBeBlldData as UtilsContrToBeBlldData
/* Joins to determine all KPI's */
left outer join I_UtilitiesBillingDocument as UtilitiesBillingDocument on UtilitiesBillingDocument.UtilitiesContract = UtilsContrToBeBlldData.UtilitiesContract
and UtilitiesBillingDocument.ScheduledUtilitiesBillingDate = UtilsContrToBeBlldData.ScheduledUtilitiesBillingDate
and UtilitiesBillingDocument.UtilitiesBillingReason = '01'
and UtilitiesBillingDocument.UtilsBillgSimulationReason = ''
and UtilitiesBillingDocument.UtilsBillgDocReversalDate = '00000000'
left outer join I_UtilitiesBillingOrder as UtilitiesBillingOrder on UtilitiesBillingOrder.UtilitiesInstallation = UtilsContrToBeBlldData.UtilitiesInstallation
and UtilitiesBillingOrder.ScheduledUtilitiesBillingDate = UtilsContrToBeBlldData.ScheduledUtilitiesBillingDate
and UtilitiesBillingOrder.UtilitiesBillingReason = '01'
and UtilitiesBillingOrder.SuppressedUtilsSchedldBillgDte = '00000000'
and UtilitiesBillingOrder.SuppressedUtilsBillgReason = ''
left outer join P_SuppressedUtilsBillingOrder as SuppressedUtilsBillingOrder on SuppressedUtilsBillingOrder.UtilitiesContract = UtilsContrToBeBlldData.UtilitiesContract
and SuppressedUtilsBillingOrder.UtilitiesInstallation = UtilsContrToBeBlldData.UtilitiesInstallation
and SuppressedUtilsBillingOrder.SuppressedUtilsSchedldBillgDte = UtilsContrToBeBlldData.ScheduledUtilitiesBillingDate
and SuppressedUtilsBillingOrder.SuppressedUtilsBillgReason = '01'
inner join I_ContractAccountPartner as ContractAccountPartner on ContractAccountPartner.ContractAccount = UtilsContrToBeBlldData.ContractAccount
/*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'
//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'
// )
//)
/* Associations for KPI texts */
association [1..1] to I_UtilsContrToBeBlldType as _UtilsContrToBeBlldType on $projection.UtilsContrToBeBlldType = _UtilsContrToBeBlldType.UtilsContrToBeBlldType
association [1..1] to I_UtilsContrToBeBlldDetsType as _UtilsContrToBeBlldDetsType on $projection.UtilsContrToBeBlldDetsType = _UtilsContrToBeBlldDetsType.UtilsContrToBeBlldDetsType
{
key UtilsContrToBeBlldData.UtilitiesContract,
@Consumption.filter.mandatory: true
@Semantics.businessDate.at: true
key UtilsContrToBeBlldData.ScheduledUtilitiesBillingDate,
@Consumption.filter.mandatory: true
@ObjectModel.foreignKey.association: '_UtilitiesPortion'
key UtilsContrToBeBlldData.UtilitiesPortion,
@Semantics.businessDate.at: true
key UtilsContrToBeBlldData.ScheduledMeterReadingDate,
@ObjectModel.foreignKey.association: '_UtilitiesBillingReason'
UtilsContrToBeBlldData.UtilitiesBillingReason,
@ObjectModel.foreignKey.association: '_CompanyCode'
UtilsContrToBeBlldData.CompanyCode,
@ObjectModel.foreignKey.association: '_Division'
UtilsContrToBeBlldData.Division,
@ObjectModel.foreignKey.association: '_UtilitiesInstallation'
UtilsContrToBeBlldData.UtilitiesInstallation,
@ObjectModel.foreignKey.association: '_ContrAcc'
ContractAccountPartner.ContractAccount,
@ObjectModel.foreignKey.association: '_BusinessPartner'
ContractAccountPartner.BusinessPartner,
@ObjectModel.foreignKey.association: '_UtilsBillgBlockReason'
UtilsContrToBeBlldData.UtilsBillgBlockReason,
@ObjectModel.foreignKey.association: '_UtilsBillgUnblockReason'
UtilsContrToBeBlldData.UtilsBillgUnblockReason,
@ObjectModel.foreignKey.association: '_MeterReadingUnit'
UtilsContrToBeBlldData.MeterReadingUnit,
@ObjectModel.foreignKey.association: '_UtilitiesRateCategory'
UtilsContrToBeBlldData.UtilitiesRateCategory,
@ObjectModel.foreignKey.association: '_UtilsBillingClass'
UtilsContrToBeBlldData.UtilitiesBillingClass,
@ObjectModel.foreignKey.association: '_MeterReadingReason'
UtilsContrToBeBlldData.MeterReadingReason,
@Semantics.businessDate.at: true
UtilsContrToBeBlldData.UtilitiesMoveInDate,
@Semantics.businessDate.at: true
UtilsContrToBeBlldData.UtilitiesMoveOutDate,
@ObjectModel.foreignKey.association: '_InvcUtilsContractsJointly'
UtilsContrToBeBlldData.UtilsContrJointInvcType,
@ObjectModel.foreignKey.association: '_UtilsAccountDetnCode'
UtilsContrToBeBlldData.UtilitiesAccountDetnCode,
@Semantics.businessDate.to: true
UtilsContrToBeBlldData.UtilsPortionScheduleRecordDate,
@Semantics.businessDate.at: true
UtilsContrToBeBlldData.SchedldUtilsInvcgPrintoutDate,
@ObjectModel.foreignKey.association: '_UtilsContrToBeBlldType'
case
when SuppressedUtilsBillingOrder.UtilitiesContract is not null
then cast('01' as e_contrtobeblld_vdm) //Suppressed
when (
UtilitiesBillingDocument.UtilitiesBillingDocument is not null
and UtilitiesBillingDocument.UtilsBillgDocIsNotReleased = ''
)
then cast('02' as e_contrtobeblld_vdm) //Billed
when (
(
UtilitiesBillingOrder.UtilitiesInstallation is null
and UtilitiesBillingDocument.UtilitiesBillingDocument is null //Missing
) or
(
UtilitiesBillingDocument.UtilitiesBillingDocument is not null
and UtilitiesBillingDocument.UtilsBillgDocIsNotReleased = 'X' //Outsorted
) or
(
UtilitiesBillingOrder.UtilitiesInstallation is not null //Billable or Incomplete
)
)
then cast('03' as e_contrtobeblld_vdm) //Unbilled
end as UtilsContrToBeBlldType,
@ObjectModel.foreignKey.association: '_UtilsContrToBeBlldDetsType'
case
when SuppressedUtilsBillingOrder.UtilitiesContract is not null
then cast(' ' as e_contrtobebllddets_vdm) //Suppressed
when (
UtilitiesBillingOrder.UtilitiesInstallation is not null
and UtilitiesBillingOrder.UtilsBillingOrderStatus = '2'
and UtilitiesBillingOrder.UtilsBillingOrderIndicator <> 'X'
)
then cast('01' as e_contrtobebllddets_vdm) //Billable
when (
UtilitiesBillingDocument.UtilitiesBillingDocument is not null
and UtilitiesBillingDocument.UtilsBillgDocIsNotReleased = ''
)
then cast('02' as e_contrtobebllddets_vdm) //Billed
when (
UtilitiesBillingOrder.UtilitiesInstallation is null
and UtilitiesBillingDocument.UtilitiesBillingDocument is null
)
then cast('03' as e_contrtobebllddets_vdm) //Missing
when (
UtilitiesBillingDocument.UtilitiesBillingDocument is not null
and UtilitiesBillingDocument.UtilsBillgDocIsNotReleased = 'X'
)
then cast('04' as e_contrtobebllddets_vdm) //Outsorted
when (
UtilitiesBillingOrder.UtilitiesInstallation is not null
and UtilitiesBillingOrder.UtilsBillingOrderStatus = '1'
)
then cast('05' as e_contrtobebllddets_vdm) //Incomplete
when (
UtilitiesBillingOrder.UtilitiesInstallation is not null
and UtilitiesBillingOrder.UtilsBillingOrderStatus = '2'
and UtilitiesBillingOrder.UtilsBillingOrderIndicator = 'X'
)
then cast('06' as e_contrtobebllddets_vdm) //Billed with Errors
end as UtilsContrToBeBlldDetsType,
@DefaultAggregation: #SUM
cast(1 as e_nrofutilscontr_vdm) as NmbrOfUtilsContractsToBeBilled,
@Aggregation: { default: #COUNT_DISTINCT, referenceElement: [ 'UtilitiesContract' ] }
case
when (
UtilsContrToBeBlldData.UtilsBillgBlockReason <> ''
and UtilsContrToBeBlldData.UtilsBillgUnblockReason = ''
)
then cast(1 as e_nrofutilscontr_vdm)
end as NmbrOfUtilsContrBlkdForBillg,
UtilsContrToBeBlldData.AuthorizationGroup,
/* Associations */
UtilsContrToBeBlldData._UtilitiesPortion,
UtilsContrToBeBlldData._UtilitiesBillingReason,
UtilsContrToBeBlldData._CompanyCode,
UtilsContrToBeBlldData._Division,
UtilsContrToBeBlldData._UtilitiesInstallation,
UtilsContrToBeBlldData._UtilsBillgBlockReason,
UtilsContrToBeBlldData._UtilsBillgUnblockReason,
UtilsContrToBeBlldData._MeterReadingUnit,
UtilsContrToBeBlldData._UtilitiesRateCategory,
UtilsContrToBeBlldData._UtilsBillingClass,
UtilsContrToBeBlldData._MeterReadingReason,
UtilsContrToBeBlldData._InvcUtilsContractsJointly,
UtilsContrToBeBlldData._UtilsAccountDetnCode,
ContractAccountPartner._ContrAcc,
ContractAccountPartner._BusinessPartner,
_UtilsInstIntAndExtPtOfDeliv,
_UtilsContrToBeBlldType,
_UtilsContrToBeBlldDetsType
}
where
UtilsContrToBeBlldData.UtilsInstTimeSliceIsValid = 'X'
and UtilsContrToBeBlldData.UtilitiesContractIsActive = 'X'
and ContractAccountPartner._RelshpOfBP.CABPIsAccountHolder = 'X'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CONTRACCTPRTNRELSHPTYPE",
"I_CONTRACTACCOUNTPARTNER",
"I_UTILITIESBILLINGDOCUMENT",
"I_UTILITIESBILLINGORDER",
"I_UTILSCONTRTOBEBLLDDATA",
"P_SUPPRESSEDUTILSBILLINGORDER"
],
"ASSOCIATED":
[
"I_BUSINESSPARTNER",
"I_COMPANYCODE",
"I_CONTRACTACCOUNTHEADER",
"I_DIVISION",
"I_INVCUTILSCONTRACTSJOINTLY",
"I_METERREADINGREASON",
"I_METERREADINGUNIT",
"I_UTILITIESBILLINGREASON",
"I_UTILITIESINSTALLATION",
"I_UTILITIESPORTION",
"I_UTILITIESRATECATEGORY",
"I_UTILSACCOUNTDETNCODE",
"I_UTILSBILLGBLOCKREASON",
"I_UTILSBILLGUNBLOCKREASON",
"I_UTILSBILLINGCLASS",
"I_UTILSCONTRTOBEBLLDDETSTYPE",
"I_UTILSCONTRTOBEBLLDTYPE",
"I_UTILSINSTINTANDEXTPTOFDELIV"
],
"BASE":
[
"I_CONTRACTACCOUNTPARTNER",
"I_UTILSCONTRTOBEBLLDDATA"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/