@EndUserText.label: 'Analysis of Contract to Be Billed and Invoiced'
@VDM.viewType: #COMPOSITE
@Analytics.dataCategory: #CUBE
@AbapCatalog.sqlViewName: 'IECONTRTBBILINV'
@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_UtilsContrToBeBlldInvcdAnlys
as select from I_UtilsContrToBeBlldData as UtilsContrToBeBlldData
inner join I_ContractAccountPartner as ContractAccountPartner on ContractAccountPartner.ContractAccount = UtilsContrToBeBlldData.ContractAccount
//Billing Order
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'
//Billing Document
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
//Billing Document and Invoicing History
left outer join I_UtilsBillgDocInvcgHistory as UtilsBillgDocInvcgHistory on UtilsBillgDocInvcgHistory.UtilitiesBillingDocument = UtilitiesBillingDocument.UtilitiesBillingDocument
and UtilsBillgDocInvcgHistory.UtilsInvcgDocIsSimulated = '' //Not Simulated
and UtilsBillgDocInvcgHistory.ReversalUtilsInvcgDocument = '' //Not Reversed
//Invoicing Document
left outer join I_UtilitiesInvoicingDocument as UtilitiesInvoicingDocument on UtilitiesInvoicingDocument.UtilitiesInvoicingDocument = UtilsBillgDocInvcgHistory.UtilitiesInvoicingDocument
and UtilitiesInvoicingDocument.UtilsInvcgDocIsSimulated = '' //Not simulated
and UtilitiesInvoicingDocument.UtilsInvcgDocIsReversed = '' //Not reversed
//Invoicing Order
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 reversed
and UtilitiesInvoicingOrder.UtilsInvoicingOrdSrceType = 'A' //Billing Document
//Blocked by Joint Invoicing
left outer join I_UtilsContrAcctBlkdByJntInvcg as UtilsContrAcctBlkdByJntInvcg on UtilsContrAcctBlkdByJntInvcg.ContractAccount = UtilsContrToBeBlldData.ContractAccount
and UtilsContrAcctBlkdByJntInvcg.UtilitiesPortion = UtilsContrToBeBlldData.UtilitiesPortion
and UtilsContrAcctBlkdByJntInvcg.ScheduledUtilitiesBillingDate = UtilsContrToBeBlldData.ScheduledUtilitiesBillingDate
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 [1..1] to I_ContractAccountHeader as _ContractAccountHeader on $projection.ContractAccount = _ContractAccountHeader.ContractAccount
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
association [1..1] to I_UtilsContrToBeInvcdPrgrs as _UtilsContrToBeInvcdPrgrs on $projection.UtilsContrToBeInvcdPrgrs = _UtilsContrToBeInvcdPrgrs.UtilsContrToBeInvcdPrgrs
association [1..1] to I_UtilsContrToBeInvcdPrgrsSts as _UtilsContrToBeInvcdPrgrsSts on $projection.UtilsContrToBeInvcdPrgrsSts = _UtilsContrToBeInvcdPrgrsSts.UtilsContrToBeInvcdPrgrsSts
association [0..1] to I_CAInvcgLockReason as _CAInvcgLockReason on $projection.CABusinessLockReason = _CAInvcgLockReason.CAInvcgLockReason
/*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'
// )
//)
{
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,
@ObjectModel.foreignKey.association: '_ContractAccountHeader'
UtilsContrToBeBlldData.ContractAccount,
@ObjectModel.foreignKey.association: '_UtilsBillgBlockReason'
UtilsContrToBeBlldData.UtilsBillgBlockReason,
@ObjectModel.foreignKey.association: '_UtilsBillgUnblockReason'
UtilsContrToBeBlldData.UtilsBillgUnblockReason,
@ObjectModel.foreignKey.association: '_CompanyCode'
UtilsContrToBeBlldData.CompanyCode,
@ObjectModel.foreignKey.association: '_Division'
UtilsContrToBeBlldData.Division,
@ObjectModel.foreignKey.association: '_UtilitiesInstallation'
UtilsContrToBeBlldData.UtilitiesInstallation,
@ObjectModel.foreignKey.association: '_AcctDetnCode'
cast(ContractAccountPartner.CAAccountDeterminationCode as e_kofiz_ca_vdm preserving type) as CAAccountDeterminationCode,
@ObjectModel.foreignKey.association: '_UtilsAccountDetnCode'
UtilsContrToBeBlldData.UtilitiesAccountDetnCode,
@ObjectModel.foreignKey.association: '_MeterReadingUnit'
UtilsContrToBeBlldData.MeterReadingUnit,
UtilsContrToBeBlldData.UtilitiesMoveInDate,
UtilsContrToBeBlldData.UtilitiesMoveOutDate,
@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: '_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,
@ObjectModel.foreignKey.association: '_UtilsContrToBeInvcdPrgrs'
case
when SuppressedUtilsBillingOrder.UtilitiesContract is not null
then 'XX' //Suppressed
when (
UtilitiesInvoicingDocument.UtilitiesInvoicingDocument is not null
and UtilitiesInvoicingDocument.UtilsInvcgDocumentIsPosted = 'X'
and UtilitiesInvoicingDocument.UtilsInvcgDocIsNotReleased = ''
)
then cast('01' as e_contrtobebinvprgrs_vdm) //Invoiced
when (
UtilitiesInvoicingDocument.UtilitiesInvoicingDocument is not null
and UtilitiesInvoicingDocument.UtilsInvcgDocumentIsPosted = ''
and UtilitiesInvoicingDocument.UtilsInvcgDocIsNotReleased = 'X'
)
or UtilitiesInvoicingDocument.UtilitiesInvoicingDocument is null
then cast('02' as e_contrtobebinvprgrs_vdm) //Uninvoiced
end as UtilsContrToBeInvcdPrgrs,
@ObjectModel.foreignKey.association: '_UtilsContrToBeInvcdPrgrsSts'
case
when SuppressedUtilsBillingOrder.UtilitiesContract is not null
then 'XX' //Suppressed
when (
UtilitiesInvoicingDocument.UtilitiesInvoicingDocument is not null
and UtilitiesInvoicingDocument.UtilsInvcgDocumentIsPosted = 'X'
and UtilitiesInvoicingDocument.UtilsInvcgDocIsNotReleased = ''
and UtilitiesInvoicingDocument.UtilitiesInvoicingDocPrintDate != '00000000'
) then cast('01' as e_contrtobebinvprsts_vdm) //Printed
when (
UtilitiesInvoicingDocument.UtilitiesInvoicingDocument is not null
and UtilitiesInvoicingDocument.UtilsInvcgDocumentIsPosted = 'X'
and UtilitiesInvoicingDocument.UtilsInvcgDocIsNotReleased = ''
and UtilitiesInvoicingDocument.UtilitiesInvoicingDocPrintDate = '00000000'
) then cast('02' as e_contrtobebinvprsts_vdm) //Not Printed
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._UtilsBillgBlockReason,
UtilsContrToBeBlldData._UtilsBillgUnblockReason,
UtilsContrToBeBlldData._CompanyCode,
UtilsContrToBeBlldData._Division,
UtilsContrToBeBlldData._UtilitiesInstallation,
UtilsContrToBeBlldData._UtilsAccountDetnCode,
ContractAccountPartner._AcctDetnCode,
UtilsContrToBeBlldData._MeterReadingUnit,
UtilsContrToBeBlldData._UtilitiesRateCategory,
UtilsContrToBeBlldData._UtilsBillingClass,
ContractAccountPartner._AltvContrAcc,
_ContractAccountHeader,
_UtilsContrToBeBlldType,
_UtilsContrToBeBlldDetsType,
_UtilsContrToBeInvcdPrgrs,
_UtilsContrToBeInvcdPrgrsSts,
_CAInvcgLockReason,
_UtilsInstIntAndExtPtOfDeliv
}
where
UtilsContrToBeBlldData.UtilitiesContractIsActive = 'X' //Active Contracts
and UtilsContrToBeBlldData.UtilsInstTimeSliceIsValid = 'X' //Valid Installation Time Slice
and ContractAccountPartner._RelshpOfBP.CABPIsAccountHolder = 'X' //Account Holder