P_MstrProjHierarchy

@AbapCatalog.sqlViewName: 'PMPHIER'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass:  #MIXED
@ObjectModel.usageType.serviceQuality: #A
@VDM.viewType: #COMPOSITE
@VDM.private:true
@AbapCatalog.viewEnhancementCategory: [#PROJECTION_LIST,#UNION]
define view P_MstrProjHierarchy
  as select from I_MstrProjStructure as StructureElement
{
  StructureElement.MasterProjectUUID,
  StructureElement.MasterProject,
  StructureElement.MasterProjectName,
  StructureElement.MasterProjectOrganization,
  StructureElement.MasterProjectIsConfidential,
  StructureElement.MasterProjectType,
  StructureElement.Currency,

  StructureElement.Project,
  StructureElement.ProjectDescription,
  StructureElement.BusinessArea,
  StructureElement.CompanyCode,
  StructureElement.ControllingArea,
  cast(StructureElement.ControllingObject as abap.char(34))                                                                                     as ControllingObject,
  cast(StructureElement.ControllingObjectExternalID as abap.char(34))                                                                           as ControllingObjectExternalID,
  cast(StructureElement.ControllingObjectType as /cpd/pws_ws_mp_rel_obj)                                                                        as ControllingObjectType,
  StructureElement.ObjectInternalID,
  cast('' as vbeln)                                                                                                                             as SalesDocument,
  cast('' as posnr)                                                                                                                             as SalesDocumentItem,
  StructureElement.ObjectName,
  StructureElement.ProjectObject as MstrProjAnchorObjectInternalID,
  StructureElement.MstrProjCutOffDate,
  StructureElement.ControllingObjectCurrency,
  $session.system_date as CurrentDate,
  StructureElement.WBSElementObject,
  StructureElement.WBSElement,
  StructureElement.ExternalObjectIdentifier
}

union select from I_MstrProjStructure  as StructureElement1
  left outer join I_SDItemOrganization as SalesOrg on StructureElement1.ObjectInternalID = SalesOrg.WBSElement
{
  StructureElement1.MasterProjectUUID,
  StructureElement1.MasterProject,
  StructureElement1.MasterProjectName,
  StructureElement1.MasterProjectOrganization,
  StructureElement1.MasterProjectIsConfidential,
  StructureElement1.MasterProjectType,
  StructureElement1.Currency,

  StructureElement1.Project,
  StructureElement1.ProjectDescription,
  StructureElement1.BusinessArea,
  StructureElement1.CompanyCode,
  StructureElement1.ControllingArea,

  cast(SalesOrg.SalesDocument  as abap.char(34))                                                          as ControllingObject,
  cast(SalesOrg.SalesDocument  as abap.char(34))                                                          as ControllingObjectExternalID,
  cast('SOI' as /cpd/pws_ws_mp_rel_obj)                                                                   as ControllingObjectType,
  cast('' as ps_posnr)                                                                                    as ObjectInternalID,
  SalesOrg.SalesDocument,
  SalesOrg.SalesDocumentItem,
  SalesOrg.SalesDocumentItemText as ObjectName,
  StructureElement1.ProjectObject as MstrProjAnchorObjectInternalID,
  StructureElement1.MstrProjCutOffDate,
  StructureElement1.ControllingObjectCurrency,
  $session.system_date as CurrentDate,
  StructureElement1.WBSElementObject,
  StructureElement1.WBSElement,
  cast('' as iaom_ext_object_id) as ExternalObjectIdentifier

}where SalesOrg.SalesDocument is not null

union select from I_MasterProjectItemList as MPItem
  inner join      I_SDItemOrganization    as SDItem on MPItem.MasterProjectItem = SDItem.SalesDocument
{
  MPItem.MasterProjectUUID,
  MPItem.MasterProject,
  MPItem.MasterProjectName,
  MPItem.MasterProjectOrganization,
  MPItem.MasterProjectIsConfidential,

  MPItem.MasterProjectType,
  MPItem.Currency,
  cast(' ' as abap.char( 22 ))                                                                                               as Project,
  cast('' as post1)                                                                                                          as ProjectDescription,
  SDItem.BusinessArea,
  SDItem.CompanyCode,
  SDItem.ControllingArea,

  cast(SDItem.SalesDocument  as abap.char(34))                                                                               as ControllingObject,
  cast(SDItem.SalesDocument  as abap.char(34))                                                                               as ControllingObjectExternalID,
  MPItem.MasterProjectItemObjectType                                                                                         as ControllingObjectType,
  cast('' as ps_posnr)                                                                                                       as ObjectInternalID,
  SDItem.SalesDocument,
  SDItem.SalesDocumentItem                                                                                                   as SalesDocumentItem,
  SDItem.SalesDocumentItemText as ObjectName,
  MPItem.MasterProjectItem as MstrProjAnchorObjectInternalID,
  MPItem.MstrProjCutOffDate,
  SDItem._SalesDocument.TransactionCurrency as ControllingObjectCurrency,
  $session.system_date as CurrentDate,
  cast('' as ps_s4_objnr)    as WBSElementObject,
  cast('' as ps_posid)    as WBSElement,
  cast('' as iaom_ext_object_id) as ExternalObjectIdentifier

}where SDItem.SalesDocument is not null

union
select from I_MasterProjectItemList as MasterProject
  inner join      I_Project               as ProjectDefinition on MasterProject.MasterProjectItem = ProjectDefinition.ProjectObject
  inner join      I_MPWBSObject           as WBSElement        on ProjectDefinition.Project = WBSElement.Project
  left outer join I_CProjectList          as CProject          on WBSElement.WBSElementObject = CProject.ProjectObject
                                                              and MasterProject.MasterProjectUUID = CProject.MasterProjectUUID      //Note 3256267

{
  MasterProject.MasterProjectUUID,
  MasterProject.MasterProject,
  MasterProject.MasterProjectName,
  MasterProject.MasterProjectOrganization,
  MasterProject.MasterProjectIsConfidential,

  MasterProject.MasterProjectType,
  MasterProject.Currency,
  WBSElement.Project,
  WBSElement.ProjectDescription,
  WBSElement.BusinessArea,
  WBSElement.CompanyCode,
  WBSElement.ControllingArea,

  cast(CProject.ProjectExternalID as abap.char(34))                                                                                                    as ControllingObject,
  cast(CProject.ProjectExternalID as abap.char(34))                                                                                                    as ControllingObjectExternalID,
  CProject.MasterProjectItemObjectType                                                                                                                 as ControllingObjectType,


  cast('' as ps_posnr)                                                                                                                                 as ObjectInternalID,
  cast('' as vbeln)                                                                                                                                    as SalesDocument,
  cast('' as posnr)                                                                                                                                    as SalesDocumentItem,
  cast('' as ps_s4_post1) as ObjectName,
  MasterProject.MasterProjectItem as MstrProjAnchorObjectInternalID,
  MasterProject.MstrProjCutOffDate,
  WBSElement.Currency as ControllingObjectCurrency,
  $session.system_date as CurrentDate,
  cast('' as ps_s4_objnr)    as WBSElementObject,
  cast('' as ps_posid)    as WBSElement,
  
  CProject.ProjectExternalID as ExternalObjectIdentifier
}where CProject.ProjectExternalID is not null
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_CPROJECTLIST",
"I_MASTERPROJECTITEMLIST",
"I_MPWBSOBJECT",
"I_MSTRPROJSTRUCTURE",
"I_PROJECT",
"I_SALESDOCUMENT",
"I_SDITEMORGANIZATION"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/