P_PlantReferences

@AbapCatalog.sqlViewName: 'PLANTREF'
@VDM.viewType: #BASIC
@AccessControl.authorizationCheck: #NOT_REQUIRED
@VDM.private: true

@ObjectModel.usageType.serviceQuality: #A
@ObjectModel.usageType.sizeCategory : #S
@ObjectModel.usageType.dataClass: #MASTER

@AbapCatalog.compiler.compareFilter:true
@ClientHandling.algorithm: #SESSION_VARIABLE

define view P_PlantReferences as select from t001w
    left outer to one join tvkov on  t001w.vkorg = tvkov.vkorg
                                 and t001w.vtweg = tvkov.vtweg
    left outer to one join twpa  on  t001w.mandt = twpa.mandt

{

  key t001w.werks      as Plant,
      t001w.vkorg      as SalesOrganization,
      t001w.vtweg      as DistributionChannel,
      //      t001w.spart,

      t001w.vlfkz      as PlantCategory,

      cast (
      case
      when t001w.vlfkz = 'B' and twpa.vlgvz = t001w.werks
        then ''
      when t001w.vlfkz = 'B'  //twpa.vlgvz <> t001w.werks

        then twpa.vlgvz
      else ''
      end as vlgvz )   as ReferenceDistributionCenter,



      cast (
      case
        when tvkov.vlgfi = t001w.werks and t001w.vlfkz = 'A'
          then ''  //t001w.werks is reference store

        when tvkov.vlgfi <> '' and t001w.vlfkz = 'A' //twpa.vlgfi <> t001w.werks

          then tvkov.vlgfi
        when tvkov.vlgfi = '' and t001w.vlfkz = 'A' and twpa.vlgfi = t001w.werks
          then ''  //t001w.werks is reference store

        when tvkov.vlgfi = '' and t001w.vlfkz = 'A' and twpa.vlgfi<> t001w.werks
          then twpa.vlgfi
        else ''  //no reference store maintained

      end as vlgfi )   as ReferenceStore,



      cast (
      case
        when t001w.vlfkz = 'B' and twpa.vlgvz = t001w.werks
          then 'X'
        else ''
      end as boole_d ) as IsReferenceDistributionCenter,



      cast (
      case
        when tvkov.vlgfi = t001w.werks and t001w.vlfkz = 'A'
          then 'X'
        when tvkov.vlgfi = '' and twpa.vlgfi = t001w.werks and t001w.vlfkz = 'A'
          then 'X'
        else ''
      end as boole_d ) as IsReferenceStore


}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"T001W",
"TVKOV",
"TWPA"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/