Pph_Ddl_Mkal

@AbapCatalog.sqlViewName: 'V_PPH_MKAL'
@EndUserText.label: 'Production Version as Source of Supply'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientDependent: true
@ObjectModel.usageType: {serviceQuality: #X, sizeCategory: #L, dataClass: #MIXED}
define view Pph_Ddl_Mkal as 

-- MKALs without Receiving Storage Location (ALONG) 
-- -->  Valid for all MRP Areas of the corr. Plant --> Join with PPH_MATNR_BERID to return only the valid BERIDs of the material
  select from mkal as mkal
  inner join MDCV_E_T399D as t399d
    on t399d.werks = mkal.werks
  inner join tcs41 as tcs41  -- inner join needed since MKAL not valid for MRP if no TCS41 exists 
    on tcs41.cslid = t399d.cslid and tcs41.stlan = mkal.stlan
  inner join P_MatlMRPArea as mb
    on mb.Material = mkal.matnr and mb.MRPAreaPlant = mkal.werks --- and mbl.lgort = mkal.alort
  {
    key mkal.matnr,
    key mkal.werks,
    key mkal.verid,
    key mb.MRPArea as berid,
    case mkal.bdatu
      when '00000000' then '99991231'
      else mkal.bdatu
    end as bdatu,
    mkal.adatu,
    case mkal.bstma 
      when 0 then 9999999999.999
      else mkal.bstma
    end as bstma,
    mkal.bstmi,
    case mkal.mksp
      when '' then cast( 'X' as aut_source preserving type )
      else cast( '' as aut_source preserving type )
    end as aut_source,
    mkal.text1,
    mkal.plnty,
    mkal.plnnr,
    mkal.alnal,
    mkal.stlal,
    mkal.stlan,
    mkal.serkz as serkz,
    mkal.alort,
    mkal.elpro,
    mkal.prvbe,
    case mkal.stlan
      when '' then cast( '99' as cslpr preserving type )
      else tcs41.cslpr
    end as arang  
  }
  where mkal.mksp <> '1' and mkal.alort = ''
  
union all

-- MKALs with Receiving Storage Location (ALONG) 
-- --> Join with PPH_MATNR_BERID_LGORT to get only those BERIDs that belong to ALONG 
  select from mkal as mkal
  inner join MDCV_E_T399D as t399d
    on t399d.werks = mkal.werks
  inner join tcs41 as tcs41  -- inner join needed since MKAL not valid for MRP if no TCS41 exists
    on tcs41.cslid = t399d.cslid and tcs41.stlan = mkal.stlan
  inner join P_MatlMRPAreaStorLoc as mbl
    on mbl.Material = mkal.matnr and mbl.MRPAreaPlant = mkal.werks and mbl.StorageLocation = mkal.alort
  {
    key mkal.matnr,
    key mkal.werks,
    key mkal.verid,
    key mbl.MRPArea as berid,
    case mkal.bdatu
      when '00000000' then '99991231'
      else mkal.bdatu
    end as bdatu,
    mkal.adatu,
    case mkal.bstma 
      when 0 then 9999999999.999
      else mkal.bstma
    end as bstma,
    mkal.bstmi,
    case mkal.mksp
      when '' then cast( 'X' as aut_source preserving type )
      else cast( '' as aut_source preserving type )
    end as aut_source,
    mkal.text1,
    mkal.plnty,
    mkal.plnnr,
    mkal.alnal,
    mkal.stlal,
    mkal.stlan,
    mkal.serkz as serkz,
    mkal.alort,
    mkal.elpro,
    mkal.prvbe,
    case mkal.stlan
      when '' then cast( '99' as cslpr preserving type )
      else tcs41.cslpr
    end as arang  
  }
  where mkal.mksp <> '1' and mkal.alort <> ''
                
  
  
 /*+[internal] {
"BASEINFO":
{
"FROM":
[
"MDCV_E_T399D",
"P_MATLMRPAREA",
"P_MATLMRPAREASTORLOC",
"MKAL",
"TCS41"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/