P_PlanningScopeHeader

@AbapCatalog.sqlViewName: 'PPLNSCPHDR'
@ClientHandling.type : #INHERITED
@ClientHandling.algorithm : #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType: {serviceQuality: #C, sizeCategory: #L, dataClass: #MIXED}
@VDM.viewType: #COMPOSITE
@VDM.private: true
define view P_PlanningScopeHeader
  as select from    I_PlanningScopeHeaderDetail   as PlanningScopeHeader
    left outer join I_ProductStructureNodeVariant as PSMNode on  PlanningScopeHeader.ProductStructureNodeUUID = PSMNode.ProductStructureNodeUUID
                                                             and PlanningScopeHeader.SourceMaterial           = PSMNode.Material
{
  key PlanningScopeHeader.DocumentId                                 as DocumentId,
  key PlanningScopeHeader.PlanningScopeAlternative                   as PlanningScopeAlternative,
  key PlanningScopeHeader.SourceMaterial                             as SourceMaterial,
      PlanningScopeHeader.PlanningScopeDescription                   as PlanningScopeDescription,
      PlanningScopeHeader.Plant                                      as Plant,
      PlanningScopeHeader.LastChangeDateTime                         as LastChangeDateTime,
      PlanningScopeHeader.ChangeNumber                               as ChangeNumber,
      PlanningScopeHeader.DocumentInfoRecordDocType                  as DocumentInfoRecordDocType,
      PlanningScopeHeader.DocumentInfoRecordDocNumber                as DocumentInfoRecordDocNumber,
      PlanningScopeHeader.DocumentInfoRecordDocPart                  as DocumentInfoRecordDocPart,
      PlanningScopeHeader.DocumentInfoRecordDocVersion               as DocumentInfoRecordDocVersion,
      PlanningScopeHeader.MBOMUsage                                  as MBOMUsage,
      PlanningScopeHeader.LastChangedByUser                          as LastChangedByUser,
      PlanningScopeHeader.Material                                   as Material,
      PlanningScopeHeader.MfgBillOfMaterialCategory                  as MfgBillOfMaterialCategory,
      PlanningScopeHeader.RecordCreationDate                         as RecordCreationDate,
      PlanningScopeHeader.CreatedByUser                              as CreatedByUser,
      PlanningScopeHeader.SourcePlant                                as SourcePlant,
      PlanningScopeHeader.SourceBOMAlternative                       as SourceBOMAlternative,
      PlanningScopeHeader.SourceBOMUsage                             as SourceBOMUsage,
      PlanningScopeHeader.EBOMValidity                               as EBOMValidity,
      PlanningScopeHeader.SourceBOMType                              as SourceBOMType,
      PlanningScopeHeader.BOMIsActive                                as BOMIsActive,
      PlanningScopeHeader.PlanningSourceType                         as PlanningSourceType,
      PlanningScopeHeader.ConfigurationSimulationDate                as ConfigurationSimulationDate,
      PSMNode.ProductStructureNodeUUID                               as ProductStructureNodeUUID,
      PSMNode.ProductStructureUsage                                  as ProductStructureUsage,
      PSMNode.ProductStructureNodeName                               as ProductStructureNodeName,
      PSMNode.ProductStructureNodeType                               as ProductStructureNodeType,
      PSMNode.ClassType                                              as ClassType,
      PSMNode.Class                                                  as Class,
      cast ('19000101' as vmp_source_datum)                          as ValidityStartDate,
      cast ('99991231' as vmp_source_datum)                          as ValidityEndDate,
      PlanningScopeHeader.SourceChangeNumber                         as SourceChangeNumber
}

/*
union all select from I_DocumentInfoRecordObjectLink as DocumentInfoRecord
  left outer join     I_PlanningScopeHeaderDetail    as PSDocument on  PSDocument.DocumentInfoRecordDocType    = DocumentInfoRecord.DocumentInfoRecordDocType
                                                                   and PSDocument.DocumentInfoRecordDocNumber  = DocumentInfoRecord.DocumentInfoRecordDocNumber
                                                                   and PSDocument.DocumentInfoRecordDocVersion = DocumentInfoRecord.DocumentInfoRecordDocVersion
                                                                   and PSDocument.DocumentInfoRecordDocPart    = DocumentInfoRecord.DocumentInfoRecordDocPart
                                                                   and PSDocument.SourceMaterial               = DocumentInfoRecord.LinkedSAPObjectKey
{
  key cast( concat(concat(concat(DocumentInfoRecord.DocumentInfoRecordDocType,DocumentInfoRecord.DocumentInfoRecordDocNumber),DocumentInfoRecord.DocumentInfoRecordDocVersion),DocumentInfoRecord.DocumentInfoRecordDocPart) as vmp_plnscp_doc_id ) as DocumentId,
  key cast('' as vmp_plnscp_alt)                                                                                                                                                                                                                    as PlanningScopeAlternative,
      // Remove CAST as it is hampering with Free Text search

  key DocumentInfoRecord.LinkedSAPObjectKey                                                                                                                                                                                                         as SourceMaterial,
      // Remove CAST as it is hampering with Free Text search

      PSDocument.PlanningScopeDescription                                                                                                                                                                                                           as PlanningScopeDescription,
      cast('' as werks_d)                                                                                                                                                                                                                           as Plant,
      DocumentInfoRecord.ChangedDateTime                                                                                                                                                                                                            as LastChangeDateTime,
      cast('' as aennr)                                                                                                                                                                                                                             as ChangeNumber,
      DocumentInfoRecord.DocumentInfoRecordDocType                                                                                                                                                                                                  as DocumentInfoRecordDocType,
      DocumentInfoRecord.DocumentInfoRecordDocNumber                                                                                                                                                                                                as DocumentInfoRecordDocNumber,
      DocumentInfoRecord.DocumentInfoRecordDocPart                                                                                                                                                                                                  as DocumentInfoRecordDocPart,
      DocumentInfoRecord.DocumentInfoRecordDocVersion                                                                                                                                                                                               as DocumentInfoRecordDocVersion,
      cast('' as stlan)                                                                                                                                                                                                                             as MBOMUsage,
      cast('' as vmp_checkout_user)                                                                                                                                                                                                                 as LastChangedByUser,
      cast('' as matnr)                                                                                                                                                                                                                             as Material,
      cast('' as vmp_mbom_type)                                                                                                                                                                                                                     as MfgBillOfMaterialCategory,
      cast('00000000' as vmp_created_on)                                                                                                                                                                                                            as RecordCreationDate,
      cast('' as vmp_created_by)                                                                                                                                                                                                                    as CreatedByUser,
      cast('' as vmp_source_plant)                                                                                                                                                                                                                  as SourcePlant,
      cast('' as vmp_source_stalt)                                                                                                                                                                                                                  as SourceBOMAlternative,
      cast('' as vmp_source_stlan)                                                                                                                                                                                                                  as SourceBOMUsage,
      cast('00000000' as vmp_source_datum)                                                                                                                                                                                                          as EBOMValidity,
      cast('' as vmp_ebom_type)                                                                                                                                                                                                                     as SourceBOMType,
      cast('' as stlst)                                                                                                                                                                                                                             as BOMIsActive,
      cast('DBOM' as abap.sstring(4))                                                                                                                                                                                                               as PlanningSourceType,
      cast('' as dats)                                                                                                                                                                                                                              as ConfigurationSimulationDate,
      cast(hextobin('00000000000000000000000000000000') as /bobf/uuid preserving type )                                                                                                                                                             as ProductStructureNodeUUID,
      cast('' as vmp_ppe_use)                                                                                                                                                                                                                       as ProductStructureUsage,
      cast('' as pvs_pnode)                                                                                                                                                                                                                         as ProductStructureNodeName,
      cast('' as pvs_pnode_type)                                                                                                                                                                                                                    as ProductStructureNodeType,
      cast('' as /plmb/ppe_klassenart)                                                                                                                                                                                                              as ClassType,
      cast('' as klasse_d)                                                                                                                                                                                                                          as Class,
      cast ('19000101' as vmp_source_datum)                                                                                                                                                                                                         as ValidityStartDate,
      cast ('99991231' as vmp_source_datum)                                                                                                                                                                                                         as ValidityEndDate
}
where
        DocumentInfoRecord.LinkedSAPObject  = 'MARA'
  and(
        PSDocument.DocumentId               is null
    and PSDocument.PlanningScopeAlternative is null
    and DocumentInfoRecord.DocInfoRecdIsMarkedForDeletion != 'X'
  )

*/
union all select from I_MaterialBOM               as BomHeader
  left outer join     I_PlanningScopeHeaderDetail as PSEbom on  PSEbom.SourceMaterial       = BomHeader.Material
                                                            and PSEbom.SourceBOMAlternative = BomHeader.BillOfMaterialVariant
                                                            and PSEbom.SourceBOMUsage       = BomHeader.BillOfMaterialVariantUsage
                                                            and PSEbom.SourcePlant          = BomHeader.Plant
{

  key cast( concat(concat(concat(concat(concat(concat(BomHeader.BillOfMaterial,BomHeader.BillOfMaterialCategory),BomHeader.EngineeringChangeDocument),BomHeader.BillOfMaterialVariant), BomHeader.Material), BomHeader.Plant), BomHeader.BillOfMaterialVersion) as vmp_plnscp_doc_id ) as DocumentId,
  key cast('' as vmp_plnscp_alt )                                                                                                                                                                                                                                                      as PlanningScopeAlternative,
  key BomHeader.Material                                                                                                                                                                                                                                                               as SourceMaterial,
      // Remove CAST as it is hampering with Free Text search

      PSEbom.PlanningScopeDescription                                                                                                                                                                                                                                                  as PlanningScopeDescription,
      //'' as PlanningScopeDescription,

      cast('' as werks_d)                                                                                                                                                                                                                                                              as Plant,
      cast('00000000000000' as timestamp)                                                                                                                                                                                                                                              as LastChangeDateTime,
      cast('' as aennr)                                                                                                                                                                                                                                                                as ChangeNumber,
      cast('' as dokar)                                                                                                                                                                                                                                                                as DocumentInfoRecordDocType,
      cast('' as doknr)                                                                                                                                                                                                                                                                as DocumentInfoRecordDocNumber,
      cast('' as doktl_d)                                                                                                                                                                                                                                                              as DocumentInfoRecordDocPart,
      cast('' as dokvr)                                                                                                                                                                                                                                                                as DocumentInfoRecordDocVersion,
      cast('' as stlan)                                                                                                                                                                                                                                                                as MBOMUsage,
      cast('' as vmp_checkout_user)                                                                                                                                                                                                                                                    as LastChangedByUser,
      cast('' as matnr)                                                                                                                                                                                                                                                                as Material,
      cast('M' as vmp_mbom_type)                                                                                                                                                                                                                                                       as MfgBillOfMaterialCategory,
      BomHeader.RecordCreationDate                                                                                                                                                                                                                                                     as RecordCreationDate,
      cast('' as vmp_created_by)                                                                                                                                                                                                                                                       as CreatedByUser,
      BomHeader.Plant                                                                                                                                                                                                                                                                  as SourcePlant,
      BomHeader.BillOfMaterialVariant                                                                                                                                                                                                                                                  as SourceBOMAlternative,
      BomHeader.BillOfMaterialVariantUsage                                                                                                                                                                                                                                             as SourceBOMUsage,
      cast('00000000' as vmp_created_on)                                                                                                                                                                                                                                               as EBOMValidity,
      BomHeader.BillOfMaterialCategory                                                                                                                                                                                                                                                 as SourceBOMType,
      BomHeader.BillOfMaterialStatus                                                                                                                                                                                                                                                   as BOMIsActive,
      cast ('EBOM' as abap.sstring(4))                                                                                                                                                                                                                                                 as PlanningSourceType,
      //cast('' as dats)                                                                                                                                                                                                                                                                 as ConfigurationSimulationDate,

      $session.system_date                                                                                                                                                                                                                                                                 as ConfigurationSimulationDate,
      
      cast(hextobin('00000000000000000000000000000000') as /bobf/uuid preserving type )                                                                                                                                                                                                as ProductStructureNodeUUID,
      cast('' as vmp_ppe_use)                                                                                                                                                                                                                                                          as ProductStructureUsage,
      PSEbom.SourceMaterial                                                                                                                                                                                                                                                            as ProductStructureNodeName,
      cast('' as pvs_pnode_type)                                                                                                                                                                                                                                                       as ProductStructureNodeType,
      cast('' as /plmb/ppe_klassenart)                                                                                                                                                                                                                                                 as ClassType,
      cast('' as klasse_d)                                                                                                                                                                                                                                                             as Class,
      BomHeader.HeaderValidityStartDate                                                                                                                                                                                                                                                as ValidityStartDate,
      BomHeader.HeaderValidityEndDate                                                                                                                                                                                                                                                  as ValidityEndDate,
      cast('' as aennr)                                                                                                                                                                                                                                                                as SourceChangeNumber
}
where
  (
        PSEbom.DocumentId                 is null
    and PSEbom.PlanningScopeAlternative   is null
  )

  and(
        //     BomHeader.HeaderValidityStartDate <= $session.system_date

        //and BomHeader.HeaderValidityEndDate   > $session.system_date

        BomHeader.IsVersionBillOfMaterial = ' ' // To stop version BOM in worklist first release Version BOM not supported

  )

union all select from I_ProductStructureNodeVariant as ProductStructure
  left outer join     I_PlanningScopeHeaderDetail   as PSProductStructure on PSProductStructure.ProductStructureNodeUUID = ProductStructure.ProductStructureNodeUUID
                      and PSProductStructure.SourceMaterial = ProductStructure.Material
{
  key cast(concat(concat(bintohex(ProductStructure.ProductStructureNodeUUID) , bintohex( ProductStructure.ProductStructureVariantUUID )), ProductStructure.ProdStrucVariantChgStateIntID) as vmp_plnscp_doc_id )                                       as DocumentId,
  key cast('' as vmp_plnscp_alt)                                                                                                                                                              as PlanningScopeAlternative,
      // Remove CAST as it is hampering with Free Text search

  key ProductStructure.Material                                                                                                                                                               as SourceMaterial,
      // Remove CAST as it is hampering with Free Text search

      PSProductStructure.PlanningScopeDescription                                                                                                                                             as PlanningScopeDescription,
      cast('' as werks_d)                                                                                                                                                                     as Plant,
      cast('00000000000000' as timestamp)                                                                                                                                                     as LastChangeDateTime,
      cast('' as aennr)                                                                                                                                                                       as ChangeNumber,
      cast('' as dokar)                                                                                                                                                                       as DocumentInfoRecordDocType,
      cast('' as doknr)                                                                                                                                                                       as DocumentInfoRecordDocNumber,
      cast('' as doktl_d)                                                                                                                                                                     as DocumentInfoRecordDocPart,
      cast('' as dokvr)                                                                                                                                                                       as DocumentInfoRecordDocVersion,
      cast('' as stlan)                                                                                                                                                                       as MBOMUsage,
      cast('' as vmp_checkout_user)                                                                                                                                                           as LastChangedByUser,
      cast('' as matnr)                                                                                                                                                                       as Material,
      cast('M' as vmp_mbom_type)                                                                                                                                                              as MfgBillOfMaterialCategory,
      cast('00000000' as vmp_created_on)                                                                                                                                                      as RecordCreationDate,
      cast('' as vmp_created_by)                                                                                                                                                              as CreatedByUser,
      ProductStructure.Plant                                                                                                                                                                  as SourcePlant,
      cast('' as vmp_source_stalt)                                                                                                                                                            as SourceBOMAlternative,
      cast('' as vmp_source_stlan)                                                                                                                                                            as SourceBOMUsage,
      cast('00000000' as vmp_source_datum)                                                                                                                                                    as EBOMValidity,
      cast('' as vmp_ebom_type)                                                                                                                                                               as SourceBOMType,
      cast('' as stlst)                                                                                                                                                                       as BOMIsActive,
      cast('PSM' as abap.sstring(4))                                                                                                                                                          as PlanningSourceType,
      PSProductStructure.ConfigurationSimulationDate                                                                                                                                         as ConfigurationSimulationDate,
      ProductStructure.ProductStructureNodeUUID                                                                                                                                               as ProductStructureNodeUUID,
      ProductStructure.ProductStructureUsage                                                                                                                                                  as ProductStructureUsage,
      ProductStructure.ProductStructureNodeName                                                                                                                                               as ProductStructureNodeName,
      ProductStructure.ProductStructureNodeType                                                                                                                                               as ProductStructureNodeType,
      ProductStructure.ClassType                                                                                                                                                              as ClassType,
      ProductStructure.Class                                                                                                                                                                  as Class,
      cast ('19000101' as vmp_source_datum)                                                                                                                                                   as ValidityStartDate,
      cast ('99991231' as vmp_source_datum)                                                                                                                                                   as ValidityEndDate,
      cast('' as aennr)                                                                                                                                                                       as SourceChangeNumber
}
where
  (
        PSProductStructure.DocumentId               is null
    and PSProductStructure.PlanningScopeAlternative is null
    and ProductStructure.ProductStructureNodeType   = 'S_HD'
    and ProductStructure.Material                   <> ''
    and ProductStructure.ProductStructureUsage      <> ''
  )