P_Valid_VersionBomitems

@AbapCatalog.sqlViewName: 'PVALIDVERSNBOM'
@ClientHandling.type : #INHERITED
@ClientHandling.algorithm : #AUTOMATED
@VDM.private: true
@VDM.viewType: #BASIC
@AbapCatalog.compiler.compareFilter: true
@ObjectModel.usageType: {serviceQuality: #B, sizeCategory: #XL, dataClass: #TRANSACTIONAL}
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.lifecycle.contract.type: #NONE

define view P_Valid_VersionBomitems
 as select distinct from  stas                   as valid_items
    inner join            stpo                   as stpo             on  stpo.stlty = valid_items.stlty
                                                                     and stpo.stlnr = valid_items.stlnr
                                                                     and stpo.stlkn = valid_items.stlkn

    left outer join       I_Mast                 as MaterialBOM      on  MaterialBOM.BillOfMaterial         = stpo.stlnr
                                                                     and MaterialBOM.BillOfMaterialVariant  = valid_items.stlal
                                                                     and MaterialBOM.BillOfMaterialCategory = stpo.stlty

    left outer join       I_Mast                 as ChildMaterialBOM on  ChildMaterialBOM.Material               = stpo.idnrk
                                                                     and ChildMaterialBOM.BillOfMaterialCategory = stpo.stlty
                                                                     and ChildMaterialBOM.Plant                  = MaterialBOM.Plant

    left outer join       t460a                  as t460a            on  t460a.werks = MaterialBOM.Plant
                                                                     and t460a.sobsl = stpo.itsob
    left outer join       t414                   as t414             on stpo.dspst = t414.dispo
    inner join            tcs03                  as tcs03            on tcs03.agb29 = '29'


{
  key stpo.guidx                            as    BillOfMaterialItemUUID,
      stpo.mandt                            as    SAPClient,
      valid_items.stlty                     as    BillOfMaterialCategory,
      valid_items.stlnr                     as    BillOfMaterial,
      valid_items.stlal                     as    BillOfMaterialVariant,
      valid_items.stlkn                     as    BillOfMaterialItemNodeNumber,
      stpo.stpoz                            as    BOMItemInternalChangeCount,
      valid_items.stvkn                     as    InheritedNodeNumberForBOMItem,
      stpo.stvkn_versn                      as    InheritedNodeNumberVersionBOM,
      valid_items.bom_versn                 as    BillOfMaterialVersion,
      @Semantics.businessDate.from: true
      valid_items.datuv                     as    ValidityStartDate,
      @Semantics.businessDate.to: true
      
      case 
      when stpo.valid_to is null 
      then cast('99991231' as datub )
      else stpo.valid_to
      end as    ValidityEndDate,
      stpo.aennr                           as    ChangeNumberFrom,
      stpo.ecn_to                           as    ChangeNumberTo,
      @ObjectModel.readOnly: true
      @Semantics.businessDate.createdAt: true
      stpo.andat                            as    BOMItemRecordCreationDate,
      @ObjectModel.readOnly: true
      stpo.annam                            as    BOMItemCreatedByUser,
      @ObjectModel.readOnly: true
      @Semantics.businessDate.changedAt: true
      stpo.aedat                            as    BOMItemLastChangeDate,
      @ObjectModel.readOnly: true
      stpo.aenam                            as    BOMItemLastChangedByUser,
      stpo.idnrk                            as    BillOfMaterialComponent,
      stpo.postp                            as    BillOfMaterialItemCategory,
      stpo.posnr                            as    BillOfMaterialItemNumber,
      @Semantics.unitOfMeasure: true
      stpo.meins                            as    BillOfMaterialItemUnit,
      @Semantics.quantity.unitOfMeasure: 'BillOfMaterialItemUnit'
      stpo.menge                            as    BillOfMaterialItemQuantity,
      stpo.upskz                            as    IsSubItem,
      stpo.sortf                            as    SortString,
      stpo.sortf                            as    BOMItemSorter,
      stpo.fmeng                            as    FixedQuantity,
      stpo.ekgrp                            as    PurchasingGroup,
      @Semantics.currencyCode: true
      stpo.waers                            as    Currency,
      @Semantics.amount.currencyCode : 'Currency'
      stpo.preis                            as    MaterialComponentPrice,
      stpo.itmid                            as    IdentifierBOMItem,
      stpo.peinh                            as    MaterialPriceUnitQty,
      stpo.ausch                            as    ComponentScrapInPercent,
      stpo.avoau                            as    OperationScrapInPercent,
      stpo.netau                            as    IsNetScrap,
      stpo.roanz                            as    NumberOfVariableSizeItem,
      stpo.romen                            as    QuantityVariableSizeItem,
      stpo.rform                            as    FormulaKey,
      stpo.potx1                            as    BOMItemDescription,
      stpo.potx2                            as    BOMItemText2,
      stpo.matkl                            as    MaterialGroup,
      stpo.class                            as    ClassNumber,
      stpo.klart                            as    ClassType,
      stpo.potpr                            as    ResultingItemCategory,
      stpo.knobj                            as    DependencyObjectNumber,
      stpo.objty                            as    ObjectType,
      stpo.kzclb                            as    IsClassificationRelevant,
      stpo.schgt                            as    IsBulkMaterial,
      stpo.erskz                            as    BOMItemIsSparePart,
      stpo.rvrel                            as    BOMItemIsSalesRelevant,
      stpo.sanfe                            as    IsProductionRelevant,
      stpo.sanin                            as    BOMItemIsPlantMaintRelevant,
      stpo.sanka                            as    BOMItemIsCostingRelevant,
      stpo.sanko                            as    IsEngineeringRelevant,
      stpo.itsob                            as    SpecialProcurementType,
      stpo.rekrs                            as    IsBOMRecursiveAllowed,
      stpo.nlfzv                            as    OperationLeadTimeOffset,
      stpo.nlfmv                            as    OpsLeadTimeOffsetUnit,
      stpo.aedat                            as    BOMItemChangeDate,
      stpo.beikz                            as    IsMaterialProvision,
      stpo.rekri                            as    BOMIsRecursive,
      stpo.cadpo                            as    DocumentIsCreatedByCAD,
      stpo.verti                            as    DistrKeyCompConsumption,
      stpo.lifzt                            as    DeliveryDurationInDays,
      stpo.lifnr                            as    Creditor,
      stpo.sakto                            as    CostElement,
      stpo.roms1                            as    Size1,
      stpo.roms2                            as    Size2,
      stpo.roms3                            as    Size3,
      stpo.romei                            as    UnitOfMeasureForSize1To3,
      stpo.webaz                            as    GoodsReceiptDuration,
      stpo.ekorg                            as    PurchasingOrganization,
      stpo.clobk                            as    RequiredComponent,
      stpo.clmul                            as    MultipleSelectionAllowed,
      stpo.lgort                            as    ProdOrderIssueLocation,
      stpo.kzkup                            as    MaterialIsCoProduct,
      stpo.dspst                            as    ExplosionType,
      stpo.alpgr                            as    AlternativeItemGroup,
      stpo.nfgrp                            as    FollowUpGroup,
      stpo.nfeag                            as    DiscontinuationGroup,
      stpo.cview                            as    ReinsurContrOrgUnit,
      stpo.awakz                            as    IsConfigurableBOM,
      stpo.rfpnt                            as    ReferencePoint,
      stpo.nlfzt                            as    LeadTimeOffset,
      stpo.prvbe                            as    ProductionSupplyArea,
      stpo.lkenz                            as    IsDeleted,
      stpo.alekz                            as    IsALE,
      tcs03.bmein                           as    UnitOfMeasureForVarSizeItem,
     case when ( ChildMaterialBOM.Material is not null or ChildMaterialBOM.Material != '' or stpo.idnrk != '' or stpo.idnrk is not null ) and
                ( ChildMaterialBOM.Plant = MaterialBOM.Plant )
           then
           cast('X' as xfeld preserving type)
           else
           cast(' ' as xfeld preserving type)
      end                                   as    IsAssembly,

      case
       when t460a.dumps = 'X' 
      then cast('X' as xfeld preserving type)
      else
      cast('' as xfeld preserving type)
      end                                   as    IsPhantomItem
} where valid_items.bom_versn <> '';
  
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MAST",
"STAS",
"STPO",
"T414",
"T460A",
"TCS03"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/