P_ConfigurableProductPlantSmpl

@AbapCatalog.sqlViewName: 'PCONFPRPLSMPL'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED


@ClientHandling.algorithm: #SESSION_VARIABLE

@VDM.viewType: #CONSUMPTION
@VDM.private: true
define view P_ConfigurableProductPlantSmpl as
  select from v_marc_md as ProductPlant 
  inner join mara as Product on ProductPlant.matnr = Product.matnr
  inner join P_ReleasedConfigurationProfile as _ConfigurationProfile on ProductPlant.matnr = _ConfigurationProfile.ProductConfiguration
                                                                    and _ConfigurationProfile.ObjectDatabaseTable = 'MARA'
{

  key ProductPlant.matnr                                         as Product,
  key ProductPlant.werks                                         as Plant,
      Product.ernam                                              as CreatedByUser,
      Product.ersda                                              as CreationDate,
      case
        when Product.laeda is null or Product.laeda = '00000000'
             then Product.ersda 
        else Product.laeda 
      end                                                        as LastChangeDate,

      case
        when Product.laeda is null or Product.laeda = '00000000'
             then Product.ernam 
        else Product.aenam 
      end                                                        as LastChangedByUser,
                                     
      Product.begru                                              as ProductAuthorizationGroup,
      Product.mtart,
      Product.matkl
}
where
  Product.kzkfg  = 'X' and  //Configuration possible on Cross-Plant level (KZKFG)

  Product.lvorm != 'X' and  //Deletion marker

  ProductPlant.lvorm != 'X' //Deletion marker

  
union 

select from mara as Product
inner join P_ReleasedConfigurationProfile as _ConfigurationProfile on Product.matnr = _ConfigurationProfile.ProductConfiguration
                                                                  and _ConfigurationProfile.ObjectDatabaseTable = 'MARA'
{

  key  Product.matnr                                              as Product,
  key  ''                                                         as Plant, //There are no records with empty plants in I_ProductPlant


      Product.ernam                                               as CreatedByUser,
      Product.ersda                                               as CreationDate,
      case
        when Product.laeda is null or Product.laeda = '00000000'
             then Product.ersda 
        else Product.laeda 
      end                                                         as LastChangeDate,

      case
        when Product.laeda is null or Product.laeda = '00000000'
             then Product.ernam 
        else Product.aenam 
      end                                                         as LastChangedByUser,
                                     
      Product.begru                                               as ProductAuthorizationGroup,
      Product.mtart,
      Product.matkl
}
where
  Product.kzkfg = 'X'  and //Configuration possible on Cross-Plant level (KZKFG)

  Product.lvorm != 'X' //Deletion marker

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"P_RELEASEDCONFIGURATIONPROFILE",
"MARA",
"V_MARC_MD"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/