@EndUserText.label: 'Scheduled Contract Has not Been Invoiced'
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #CUBE
@AbapCatalog.sqlViewName: 'IESCHCONTRNTINV'
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #('TRANSACTIONAL_DATA')
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XXL
@ClientHandling.algorithm: #SESSION_VARIABLE
@Metadata.allowExtensions:true
define view I_SchedldUtilsContrNotInvcd
as select from I_UtilsContrToBeBlldData as UtilsContrToBeBlldData
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'
left outer join I_UtilitiesBillingDocument as UtilitiesBillingDocument on UtilitiesBillingDocument.UtilitiesContract = UtilsContrToBeBlldData.UtilitiesContract
and UtilitiesBillingDocument.ScheduledUtilitiesBillingDate = UtilsContrToBeBlldData.ScheduledUtilitiesBillingDate
and UtilitiesBillingDocument.UtilitiesBillingReason = '01' //Periodic Billing
and UtilitiesBillingDocument.UtilsBillgSimulationReason = '' //Not Simulated
and UtilitiesBillingDocument.UtilsBillgDocReversalDate = '00000000' //Not Reversed
left outer join I_UtilsBillgDocInvcgHistory as UtilsBillgDocInvcgHistory on UtilsBillgDocInvcgHistory.UtilitiesBillingDocument = UtilitiesBillingDocument.UtilitiesBillingDocument
and UtilsBillgDocInvcgHistory.UtilsInvcgDocIsSimulated = '' //Not Simulated
and UtilsBillgDocInvcgHistory.ReversalUtilsInvcgDocument = '' //Not Reversed
left outer join I_UtilitiesInvoicingDocument as UtilitiesInvoicingDocument on UtilitiesInvoicingDocument.UtilitiesInvoicingDocument = UtilsBillgDocInvcgHistory.UtilitiesInvoicingDocument
and UtilitiesInvoicingDocument.UtilsInvcgDocIsSimulated = '' //Not Simulated
and UtilitiesInvoicingDocument.UtilsInvcgDocIsReversed = '' //Not Reversed
left outer join I_UtilitiesInvoicingOrder as UtilitiesInvoicingOrder on UtilitiesInvoicingOrder.UtilitiesBillingDocument = UtilitiesBillingDocument.UtilitiesBillingDocument
and UtilitiesInvoicingOrder.UtilitiesBillingReason = '01' //Periodic Billing
and UtilitiesInvoicingOrder.UtilsBillgSimulationReason = '' //Not Simulated
and UtilitiesInvoicingOrder.UtilsBillgDocIsNotReleased = '' //Not Released
and UtilitiesInvoicingOrder.UtilsInvoicingOrdSrceType = 'A' //Billing Document
left outer join I_UtilsContrAcctBlkdByJntInvcg as UtilsContrAcctBlkdByJntInvcg on UtilsContrAcctBlkdByJntInvcg.ContractAccount = UtilsContrToBeBlldData.ContractAccount
and UtilsContrAcctBlkdByJntInvcg.UtilitiesPortion = UtilsContrToBeBlldData.UtilitiesPortion
and UtilsContrAcctBlkdByJntInvcg.ScheduledUtilitiesBillingDate = UtilsContrToBeBlldData.ScheduledUtilitiesBillingDate
inner join I_ContractAccountPartner as ContractAccountPartner on ContractAccountPartner.ContractAccount = UtilsContrToBeBlldData.ContractAccount
left outer join I_ActvUtilsCAInvcgLockRsn as ActvUtilsCAInvcgLockRsn on ActvUtilsCAInvcgLockRsn.ContractAccount = UtilsContrToBeBlldData.ContractAccount
left outer join I_ActvUtilsContrInvcgLockRsn as ActvUtilsContrInvcgLockRsn on ActvUtilsContrInvcgLockRsn.CABusinessLockObject = UtilsContrToBeBlldData.UtilitiesContract
association [0..1] to I_UtilsContrToBeInvcdPrgrsSts as _UtilsContrToBeInvcdPrgrsSts on $projection.UtilsContrToBeInvcdPrgrsSts = _UtilsContrToBeInvcdPrgrsSts.UtilsContrToBeInvcdPrgrsSts
association [0..1] to I_CAInvcgLockReason as _CAInvcgLockReason on $projection.CABusinessLockReason = _CAInvcgLockReason.CAInvcgLockReason
{
key UtilsContrToBeBlldData.UtilitiesContract,
@Semantics.businessDate.at: true
key UtilsContrToBeBlldData.ScheduledUtilitiesBillingDate,
@ObjectModel.foreignKey.association: '_UtilitiesPortion'
key UtilsContrToBeBlldData.UtilitiesPortion,
@Semantics.businessDate.at: true
key UtilsContrToBeBlldData.ScheduledMeterReadingDate,
@ObjectModel.foreignKey.association: '_BusinessPartner'
ContractAccountPartner.BusinessPartner,
UtilsContrToBeBlldData.ContractAccount,
@ObjectModel.foreignKey.association: '_CompanyCode'
UtilsContrToBeBlldData.CompanyCode,
@ObjectModel.foreignKey.association: '_Division'
UtilsContrToBeBlldData.Division,
@ObjectModel.foreignKey.association: '_UtilitiesInstallation'
UtilsContrToBeBlldData.UtilitiesInstallation,
@ObjectModel.foreignKey.association: '_UtilsAccountDetnCode'
UtilsContrToBeBlldData.UtilitiesAccountDetnCode,
@ObjectModel.foreignKey.association: '_AcctDetnCode'
cast(ContractAccountPartner.CAAccountDeterminationCode as e_kofiz_ca_vdm preserving type) as CAAccountDeterminationCode,
@ObjectModel.foreignKey.association: '_UtilitiesRateCategory'
UtilsContrToBeBlldData.UtilitiesRateCategory,
@ObjectModel.foreignKey.association: '_UtilsBillingClass'
UtilsContrToBeBlldData.UtilitiesBillingClass,
@ObjectModel.foreignKey.association: '_AltvContrAcc'
cast(ContractAccountPartner.AltvContractAcctForCollvBills as e_collvbillacct_vdm preserving type) as AltvContractAcctForCollvBills,
@ObjectModel.foreignKey.association: '_CAInvcgLockReason'
case
when ActvUtilsCAInvcgLockRsn.CABusinessLockObject is not null
and (
ActvUtilsContrInvcgLockRsn.CABusinessLockObject is not null
or ActvUtilsContrInvcgLockRsn.CABusinessLockObject is null
)
then cast(ActvUtilsCAInvcgLockRsn._UtilsCAInvcgLockRsn.CABusinessLockReason as e_invcglockreason_vdm preserving type) //Contract Account Lock
when ActvUtilsCAInvcgLockRsn.CABusinessLockObject is null
and ActvUtilsContrInvcgLockRsn.CABusinessLockObject is not null
then cast(ActvUtilsContrInvcgLockRsn._UtilsCAInvcgLockRsn.CABusinessLockReason as e_invcglockreason_vdm preserving type) //Contract Lock
end as CABusinessLockReason,
@ObjectModel.foreignKey.association: '_UtilsContrToBeInvcdPrgrsSts'
case
when UtilitiesInvoicingOrder.UtilitiesBillingDocument is not null
and UtilitiesInvoicingOrder.UtilsInvoicingOrderIndicator = ''
and UtilsContrAcctBlkdByJntInvcg.ContractAccount is null
then cast('03' as e_contrtobebinvprsts_vdm) //Ready for Invoicing
when UtilitiesInvoicingOrder.UtilitiesBillingDocument is not null
and UtilitiesInvoicingOrder.UtilsInvoicingOrderIndicator = 'X'
and UtilsContrAcctBlkdByJntInvcg.ContractAccount is null
then cast('04' as e_contrtobebinvprsts_vdm) //Invoiced with Errors
when UtilitiesInvoicingDocument.UtilitiesInvoicingDocument is not null
and UtilitiesInvoicingDocument.UtilsInvcgDocumentIsPosted = ''
and UtilitiesInvoicingDocument.UtilsInvcgDocIsNotReleased = 'X'
then cast('05' as e_contrtobebinvprsts_vdm) //Outsorted Invoicing Document
when UtilitiesInvoicingOrder.UtilitiesBillingDocument is not null
and UtilsContrAcctBlkdByJntInvcg.ContractAccount is not null
then cast('06' as e_contrtobebinvprsts_vdm) //Blocked by Joint Invoicing
when UtilitiesInvoicingOrder.UtilitiesBillingDocument is null
then cast('07' as e_contrtobebinvprsts_vdm) //Not Ready for Invoicing
end as UtilsContrToBeInvcdPrgrsSts,
UtilsContrToBeBlldData.AuthorizationGroup,
@DefaultAggregation: #SUM
cast(1 as e_nrofutilscontr_vdm) as NmbrOfUtilsContrToBeInvoiced,
/* Associations */
UtilsContrToBeBlldData._UtilitiesPortion,
ContractAccountPartner._BusinessPartner,
UtilsContrToBeBlldData._CompanyCode,
UtilsContrToBeBlldData._Division,
UtilsContrToBeBlldData._UtilitiesInstallation,
UtilsContrToBeBlldData._UtilsAccountDetnCode,
ContractAccountPartner._AcctDetnCode,
UtilsContrToBeBlldData._UtilitiesRateCategory,
UtilsContrToBeBlldData._UtilsBillingClass,
ContractAccountPartner._AltvContrAcc,
_UtilsContrToBeInvcdPrgrsSts,
_CAInvcgLockReason
}
where
SuppressedUtilsBillingOrder.UtilitiesContract is null //Not Suppressed
and UtilsContrToBeBlldData.UtilitiesContractIsActive = 'X' //Active Contracts
and UtilsContrToBeBlldData.UtilsInstTimeSliceIsValid = 'X' //Valid Installation Time Slice
and ContractAccountPartner._RelshpOfBP.CABPIsAccountHolder = 'X' //Account Holder
and(
(
UtilitiesInvoicingDocument.UtilitiesInvoicingDocument is not null //Uninvoiced
and UtilitiesInvoicingDocument.UtilsInvcgDocumentIsPosted = ''
)
or UtilitiesInvoicingDocument.UtilitiesInvoicingDocument is null
)