@AbapCatalog.sqlViewName: 'FINOCV_RTWBSFILL'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #L
@Metadata.ignorePropagatedAnnotations: true
@EndUserText.label: 'Orgl Change: Fill Runtime for WBS Elmnt'
define view FINOC_RT_WBS_FILL
// Select billing items for Customer Projects (Cloud).
as select from I_OrglChangeWBSElement as BillingItem
inner join I_WBSElementBasicData as BillingItemBasicData on BillingItem.WBSElementInternalID = BillingItemBasicData.WBSElementInternalID
inner join I_ProjectBasicData as ProjectBasicData on BillingItemBasicData.ProjectInternalID = ProjectBasicData.ProjectInternalID
{
key BillingItem.WBSElementInternalID as WBSElementInternalID,
key BillingItem.OrganizationalChange as OrganizationalChange,
BillingItemBasicData.ControllingArea as ControllingArea,
BillingItemBasicData.CompanyCode as CompanyCode,
BillingItem.ProfitCenter as ProfitCenterBeforeOrglChange,
BillingItem.ProfitCenterByOrglChange as ProfitCenterByOrglChange,
cast(' ' as finoc_prctr_drvtn_source_type preserving type) as ProfitCenterDerivationSrceType,
cast('00000000' as finoc_srce_ps_psp_pnr preserving type) as SourceWBSElementInternalID,
cast('00000000' as finoc_srce_ps_prj_pnr preserving type) as SourceProjectInternalID,
BillingItemBasicData.WBSElementObject as ObjectNumber,
cast('00000000' as finoc_billing_item preserving type) as UpperBillingElementInternalID,
'BI ' as SemanticObject, // for debugging: billing item
BillingItemBasicData.WBSElementExternalID as WBSElementExternalID // for debugging
}
where
ProjectBasicData.ProjectProfileCode = 'P001' // cloud customer project
and BillingItemBasicData.WBSElementIsBillingElement = 'X' // Billing elements (prps-fakkz = 'X') only
union all // ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// Select work packages directly below billing items (selection via "RAP" Tables).
select from I_OrglChangeWBSElement as BillingItem
inner join I_WBSElementStructure as WorkPackage on BillingItem.WBSElementInternalID = WorkPackage.WBSElementParentInternalID
inner join I_WBSElementBasicData as BillingItemBasicData on BillingItem.WBSElementInternalID = BillingItemBasicData.WBSElementInternalID
inner join I_ProjectBasicData as ProjectBasicData on BillingItemBasicData.ProjectInternalID = ProjectBasicData.ProjectInternalID
inner join I_WBSElementBasicData as WorkPackageBasicData on WorkPackage.WBSElementInternalID = WorkPackageBasicData.WBSElementInternalID
inner join I_OrganizationalChange as OrglChange on BillingItem.OrganizationalChange = OrglChange.OrganizationalChange
left outer join I_ProfitCenterToWBSElement as ProfitCenterToWorkPackage on ProfitCenterToWorkPackage.WBSElementInternalID = WorkPackage.WBSElementInternalID
and ProfitCenterToWorkPackage.ValidityStartDate = OrglChange.OrglChangeEffectiveDate
{
key WorkPackage.WBSElementInternalID as WBSElementInternalID,
key BillingItem.OrganizationalChange as OrganizationalChange,
WorkPackageBasicData.ControllingArea as ControllingArea,
WorkPackageBasicData.CompanyCode as CompanyCode,
BillingItem.ProfitCenter as ProfitCenterBeforeOrglChange,
BillingItem.ProfitCenterByOrglChange as ProfitCenterByOrglChange,
cast('PR' as finoc_prctr_drvtn_source_type preserving type) as ProfitCenterDerivationSrceType,
BillingItem.WBSElementInternalID as SourceWBSElementInternalID,
cast('00000000' as finoc_srce_ps_prj_pnr preserving type) as SourceProjectInternalID,
WorkPackageBasicData.WBSElementObject as ObjectNumber,
BillingItem.WBSElementInternalID as UpperBillingElementInternalID,
'WP_BI' as SemanticObject, // for debugging: work package below billing item
WorkPackage._WBSElement.WBSElementExternalID as WBSElementExternalID // for debugging
}
where
ProjectBasicData.ProjectProfileCode = 'P001' // cloud customer project
and WorkPackageBasicData.WBSElementIsBillingElement <> 'X' // no billing elements (prps-fakkz <> 'X')
and(
WorkPackageBasicData.ProfitCenter = BillingItem.ProfitCenter // Profit Center of object shall be either the "old" one
or( // ... or
WorkPackageBasicData.ProfitCenter = ProfitCenterToWorkPackage.ProfitCenter // ... the "new" one found in the object's runtime tables
and ProfitCenterToWorkPackage.OrglChangeReassignmentStatus = 'DONE' // ... but only if this org change has made this change.
)
)
union all // ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// Select project summary tasks below project headers for Internal Projects and Customer Projects (Cloud).
select from I_OrganizationalChangeProject as Project
inner join I_WBSElementStructure as ProjSummaryTask on Project.ProjectInternalID = ProjSummaryTask.ProjectInternalID
and ProjSummaryTask.WBSElementParentInternalID = '00000000' // top node = project summary task
inner join I_WBSElementBasicData as ProjSummaryTaskBasicData on ProjSummaryTask.WBSElementInternalID = ProjSummaryTaskBasicData.WBSElementInternalID
inner join I_ProjectBasicData as ProjectBasicData on Project.ProjectInternalID = ProjectBasicData.ProjectInternalID
{
key ProjSummaryTask.WBSElementInternalID as WBSElementInternalID,
key Project.OrganizationalChange as OrganizationalChange,
ProjSummaryTaskBasicData.ControllingArea as ControllingArea,
ProjSummaryTaskBasicData.CompanyCode as CompanyCode,
Project.ProfitCenter as ProfitCenterBeforeOrglChange,
Project.ProfitCenterByOrglChange as ProfitCenterByOrglChange,
cast('PD' as finoc_prctr_drvtn_source_type preserving type) as ProfitCenterDerivationSrceType,
cast('00000000' as finoc_srce_ps_psp_pnr preserving type) as SourceWBSElementInternalID,
Project.ProjectInternalID as SourceProjectInternalID,
ProjSummaryTaskBasicData.WBSElementObject as ObjectNumber,
cast('00000000' as finoc_billing_item preserving type) as UpperBillingElementInternalID,
'PST ' as SemanticObject, // for debugging: project summary task
ProjSummaryTaskBasicData.WBSElementExternalID as WBSElementExternalID // for debugging
}
where
ProjectBasicData.ProjectProfileCode = 'P001' // customer project
or ProjectBasicData.ProjectProfileCode = 'P002' // internal project
union all // ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// Select work packages below project summary tasks of internal projects (selection via "RAP" tables).
select from I_OrganizationalChangeProject as Project
inner join I_ProjectBasicData as ProjectBasicData on Project.ProjectInternalID = ProjectBasicData.ProjectInternalID
inner join I_WBSElementStructure as ProjSummaryTask on Project.ProjectInternalID = ProjSummaryTask.ProjectInternalID
and ProjSummaryTask.WBSElementParentInternalID = '00000000' // top node = project summary task
inner join I_WBSElementStructure as WorkPackage on ProjSummaryTask.WBSElementInternalID = WorkPackage.WBSElementParentInternalID // work package below PST
inner join I_WBSElementBasicData as WorkPackageBasicData on WorkPackage.WBSElementInternalID = WorkPackageBasicData.WBSElementInternalID
inner join I_OrganizationalChange as OrglChange on Project.OrganizationalChange = OrglChange.OrganizationalChange
left outer join I_ProfitCenterToWBSElement as ProfitCenterToWorkPackage on ProfitCenterToWorkPackage.WBSElementInternalID = WorkPackage.WBSElementInternalID
and ProfitCenterToWorkPackage.ValidityStartDate = OrglChange.OrglChangeEffectiveDate
{
key WorkPackageBasicData.WBSElementInternalID as WBSElementInternalID,
key Project.OrganizationalChange as OrganizationalChange,
WorkPackageBasicData.ControllingArea as ControllingArea,
WorkPackageBasicData.CompanyCode as CompanyCode,
Project.ProfitCenter as ProfitCenterBeforeOrglChange,
Project.ProfitCenterByOrglChange as ProfitCenterByOrglChange,
cast('PD' as finoc_prctr_drvtn_source_type preserving type) as ProfitCenterDerivationSrceType,
cast('00000000' as finoc_srce_ps_psp_pnr preserving type) as SourceWBSElementInternalID,
Project.ProjectInternalID as SourceProjectInternalID,
WorkPackageBasicData.WBSElementObject as ObjectNumber,
cast('00000000' as finoc_billing_item preserving type) as UpperBillingElementInternalID,
'WP_IP' as SemanticObject, // for debugging: work package of an internal project
WorkPackageBasicData.WBSElementExternalID as WBSElementExternalID // for debugging
}
where
ProjectBasicData.ProjectProfileCode = 'P002' // internal project
and(
WorkPackageBasicData.ProfitCenter = Project.ProfitCenter // Profit Center of object shall be either the "old" one
or( // ... or
WorkPackageBasicData.ProfitCenter = ProfitCenterToWorkPackage.ProfitCenter // ... the "new" one found in the object's runtime tables
and ProfitCenterToWorkPackage.OrglChangeReassignmentStatus = 'DONE' // ... but only if this org change has made this change.
)
)
union all // ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// Select work packages entered at the UI for EPPM projects
select from I_OrglChangeWBSElement as WbsElementUi
inner join I_WBSElementBasicData as WbsElement on WbsElementUi.WBSElementInternalID = WbsElement.WBSElementInternalID
inner join I_ProjectBasicData as ProjectBasicData on WbsElement.ProjectInternalID = ProjectBasicData.ProjectInternalID
inner join I_OrganizationalChange as OrglChange on WbsElementUi.OrganizationalChange = OrglChange.OrganizationalChange
// left outer join I_ProfitCenterToWBSElement as ProfitCenterToWbsElement on ProfitCenterToWbsElement.WBSElementInternalID = WbsElement.WBSElementInternalID
// and ProfitCenterToWbsElement.ValidityStartDate = OrglChange.OrglChangeEffectiveDate
{
key WbsElement.WBSElementInternalID as WBSElementInternalID,
key WbsElementUi.OrganizationalChange as OrganizationalChange,
WbsElement.ControllingArea as ControllingArea,
WbsElement.CompanyCode as CompanyCode,
WbsElementUi.ProfitCenter as ProfitCenterBeforeOrglChange,
WbsElementUi.ProfitCenterByOrglChange as ProfitCenterByOrglChange,
cast(' ' as finoc_prctr_drvtn_source_type preserving type) as ProfitCenterDerivationSrceType,
cast('00000000' as finoc_srce_ps_psp_pnr preserving type) as SourceWBSElementInternalID,
cast('00000000' as finoc_srce_ps_prj_pnr preserving type) as SourceProjectInternalID,
WbsElement.WBSElementObject as ObjectNumber,
cast('00000000' as finoc_billing_item preserving type) as UpperBillingElementInternalID,
'WB_UI' as SemanticObject, // for debugging: wbs element entered at UI
WbsElement.WBSElementExternalID as WBSElementExternalID // for debugging
}
where
ProjectBasicData.ProjectProfileCode <> 'P001' // customer project
and ProjectBasicData.ProjectProfileCode <> 'P002' // internal project
and ProjectBasicData.ProjectProfileCode <> 'YP05' // EPPM project with revenue
union all // ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// Select wbs elements below project headers for EPPM projects (not EPPM projects with revenue) - not entered at UI.
select from I_OrganizationalChangeProject as ProjectUi
inner join I_WBSElementBasicData as WbsElement on ProjectUi.ProjectInternalID = WbsElement.ProjectInternalID
inner join I_ProjectBasicData as Project on ProjectUi.ProjectInternalID = Project.ProjectInternalID
left outer join I_OrglChangeWBSElement as WbsElementUi on WbsElement.WBSElementInternalID = WbsElementUi.WBSElementInternalID
{
key WbsElement.WBSElementInternalID as WBSElementInternalID,
key ProjectUi.OrganizationalChange as OrganizationalChange,
WbsElement.ControllingArea as ControllingArea,
WbsElement.CompanyCode as CompanyCode,
ProjectUi.ProfitCenter as ProfitCenterBeforeOrglChange,
ProjectUi.ProfitCenterByOrglChange as ProfitCenterByOrglChange,
cast('PD' as finoc_prctr_drvtn_source_type preserving type) as ProfitCenterDerivationSrceType,
cast('00000000' as finoc_srce_ps_psp_pnr preserving type) as SourceWBSElementInternalID,
ProjectUi.ProjectInternalID as SourceProjectInternalID,
WbsElement.WBSElementObject as ObjectNumber,
cast('00000000' as finoc_billing_item preserving type) as UpperBillingElementInternalID,
'EP_PD' as SemanticObject, // for debugging: project summary task
WbsElement.WBSElementExternalID as WBSElementExternalID // for debugging
}
where
WbsElementUi.WBSElementInternalID is null
and(
Project.ProjectProfileCode = 'YP03' // overhead project
or Project.ProjectProfileCode = 'YP04' // statistical project
)
union all // ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
// Select work packages added from rules
select from finoc_rule_t_wbs as WbsElement
inner join I_WBSElementBasicData as WbsElementDetails on WbsElementDetails.WBSElementInternalID = WbsElement.ps_psp_pnr
inner join I_ProjectBasicData as ProjectDetails on WbsElementDetails.ProjectInternalID = ProjectDetails.ProjectInternalID
left outer join I_OrglChangeWBSElement as WbsElementAtUi on WbsElementAtUi.WBSElementInternalID = WbsElement.ps_psp_pnr
and WbsElementAtUi.OrganizationalChange = WbsElement.orgl_change
{
key WbsElement.ps_psp_pnr as WBSElementInternalID,
key WbsElement.orgl_change as OrganizationalChange,
WbsElement.kokrs as ControllingArea,
WbsElement.bukrs as CompanyCode,
WbsElement.prctr_old as ProfitCenterBeforeOrglChange,
WbsElement.prctr as ProfitCenterByOrglChange,
WbsElement.prctr_drvtn_source_type as ProfitCenterDerivationSrceType,
WbsElement.srce_ps_psp_pnr as SourceWBSElementInternalID,
WbsElement.srce_ps_prj_pnr as SourceProjectInternalID,
WbsElement.objnr as ObjectNumber,
WbsElement.billing_item as UpperBillingElementInternalID,
'WB_RL' as SemanticObject, // for debugging: wbs element from rules implementation
WbsElementDetails.WBSElementExternalID as WBSElementExternalID // for debugging
}
where
( ProjectDetails.ProjectProfileCode <> 'P001'
and ProjectDetails.ProjectProfileCode <> 'P002'
and ProjectDetails.ProjectProfileCode <> 'YP03'
and ProjectDetails.ProjectProfileCode <> 'YP04'
) and (
WbsElementAtUi.WBSElementInternalID is null // elements entered at the UI have priority
or ProjectDetails.ProjectProfileCode = 'YP05' // EPPM project with revenue handled i FINOC_RULE_WBS_DEFAULT_CE.
)
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_ORGANIZATIONALCHANGE",
"I_ORGANIZATIONALCHANGEPROJECT",
"I_ORGLCHANGEWBSELEMENT",
"I_PROFITCENTERTOWBSELEMENT",
"I_PROJECTBASICDATA",
"I_WBSELEMENTBASICDATA",
"I_WBSELEMENTSTRUCTURE",
"FINOC_RULE_T_WBS"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/