P_Matval_Manage_Cost

@AbapCatalog.sqlViewName: 'PMATVALMGMCOST'
@VDM.private: true
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_Matval_Manage_Cost
  as select from ckmlpr                         as pr
    inner join   mbew                           as m    on m.kaln1 = pr.kalnr
    inner join   t001k                          as tk   on tk.bwkey = m.bwkey
    inner join   I_MatlLedgerValnCrcyRoleName   as ml   on  ml.CompanyCode                 = tk.bukrs
                                                        and ml.LedgerValuationCurrencyRole = pr.curtp
    inner join   P_LedgerValuationCrcyRole      as lvcr on lvcr.curtype = pr.curtp
    inner join   fvmkld                         as f    on  f.bukrs      = tk.bukrs
                                                        and f.valuation  = lvcr.valutyp
                                                        and f.valusubtyp = lvcr.valusubtyp
                                                        and f.rldnr      = ml.Ledger
                                                        and (
                                                           ( //marked

                                                             f.poper     = pr.pprdz
                                                             and f.bdatj = pr.pdatz
                                                           )
                                                           or( //current

                                                             f.poper     = pr.pprdl
                                                             and f.bdatj = pr.pdatl
                                                           )
                                                         )
    inner join   I_ProductCostEstimate          as k    on  k.CostingReferenceObject = '0'
                                                        and (
                                                           k.CostEstimate            = pr.kalnr
                                                           or k.CostEstimate         = m.kalnr
                                                         )
                                                        and (
                                                           k.CostEstimateStatus      = 'VO'
                                                           or k.CostEstimateStatus   = 'FR'
                                                         )
                                                        and k.ValuationVariant       = f.bwvar
                                                        and k.CostingType            = f.kalka
                                                        and k.CostingVersion         = f.tvers
                                                        and k.PostingFiscalYear      = f.bdatj
                                                        and k.PostingPeriod          = f.poper

    inner join   mara                           as ma   on ma.matnr = m.matnr

    inner join   R_MatlPriceDataMigrationStatus as tf   on(
        tf.MatlPriceDataMigrationStatus                                                   = 'X'
      )
                                                        or(
                                                          tf.MatlPriceDataMigrationStatus = 'R'
                                                        )

{
  key pr.kalnr                                   as CostEstimate,
  key k.CostingVersion                           as CostingVersion,
  key k.ValuationVariant                         as ValuationVariant,
  key pr.curtp                                   as LedgerValuationCurrencyRole,
  key m.bwkey                                    as ValuationArea,
  key tk.bukrs                                   as CompanyCode,

      f.valuation                                as AccountingValuationView,
      f.valusubtyp                               as AccountingValuationSubview,
      k.CostEstimateStatus                       as CostingStatus,
      k.CostingVariant                           as CostingVariant,
      k.CostingType                              as CostingType,
      f.valuation                                as ControllingValuationType,
      k.CostEstimateMarkingDate                  as CostEstimateMarkingDate,
      k.CostEstimateMarkingUser                  as CostEstimateMarkingUser,
      k.CostEstimateReleaseDate                  as CostEstimateReleaseDate,
      k.CostEstimateReleaseUser                  as CostEstimateReleaseUser,
      case k.CostEstimateStatus
        when 'VO' then cast(pr.zplpr as fml_plpr)
        when 'VF' then cast(pr.zplpr as fml_plpr)
        when 'FR' then cast(pr.lplpr as fml_plpr)
        when 'FF' then cast(pr.lplpr as fml_plpr)
        else cast(0 as fml_plpr)
      end                                        as PlannedPrice,
      case k.CostEstimateStatus
      when 'VO' then cast(pr.fplpx as fml_plpx)
      when 'VF' then cast(pr.fplpx as fml_plpx)
      when 'FR' then cast(pr.lplpx as fml_plpx)
      when 'FF' then cast(pr.lplpx as fml_plpx)
      else cast(0 as fml_plpx)
      end                                        as FixedPortionOfPlannedPrice,
      ml.Currency                                as Currency,
      m.matnr                                    as Material,
      cast(pr.peinh as ck_peinh_1)               as MaterialPriceUnitQty,
      ma.meins                                   as BaseUnit,
      // Valuation UOM for Catch Weight Management

      cast (case when ma./cwm/xcwmat = 'X' and
                    ma./cwm/valum <> '' then ma./cwm/valum
                else ma.meins end as /cwm/valum) as ValuationQuantityUnit,
      ma./cwm/xcwmat,

      k.CostEstimateValidityStartDate            as CostEstimateValidityStartDate
}
where
  (
         pr.kalkz                        = 'X'
    and(
         k.CostEstimateStatus            = 'VO'
      or k.CostEstimateStatus            = 'VF'
    )
  )
  or(
         pr.kalkl                        = 'X'
    and(
         k.CostEstimateStatus            = 'FR'
      or k.CostEstimateStatus            = 'FF'
    )
  )

  and(
         tf.MatlPriceDataMigrationStatus = 'X'

    or   pr.sdm_version                  is initial
  )

union all select from    I_MaterialLedgerPrice          as price
  inner join             I_MatlLedgerValnCrcyRoleName   as ml   on  ml.CompanyCode  = price.CompanyCode
                                                                and ml.Ledger       = price.Ledger
                                                                and ml.CurrencyRole = price.CurrencyRole

  inner join             P_LedgerValuationCrcyRole      as lvcr on lvcr.curtype = ml.LedgerValuationCurrencyRole

  inner join             fvmkld                         as f    on  f.bukrs      = price.CompanyCode
                                                                and f.valuation  = lvcr.valutyp
                                                                and f.valusubtyp = lvcr.valusubtyp
                                                                and f.rldnr      = price.Ledger
                                                                and f.poper      = price.MatlPrcValdtyStartFiscalPeriod
                                                                and f.bdatj      = price.MatlPrcValidityStartFiscalYear

  inner join             I_ProductCostEstimate          as k    on  k.CostingReferenceObject = '0'
                                                                and k.CostEstimate           = price.CostEstimate
                                                                and k.CostingVariant         = f.klvar
                                                                and k.CostingVersion         = f.tvers
                                                                and k.PostingFiscalYear      = f.bdatj
                                                                and k.PostingPeriod          = f.poper

  inner join             mara                           as ma   on ma.matnr = price.Material
  inner join             R_MatlPriceDataMigrationStatus as tf   on(
      tf.MatlPriceDataMigrationStatus                                                             = 'F'
    )
                                                                or(
                                                                  tf.MatlPriceDataMigrationStatus = 'R'
                                                                )
  left outer to one join ckmlhd                         as hd   on hd.kalnr = price.CostEstimate
{
  key price.CostEstimate                         as CostEstimate,
  key k.CostingVersion                           as CostingVersion,
  key k.ValuationVariant                         as ValuationVariant,
  key ml.LedgerValuationCurrencyRole             as LedgerValuationCurrencyRole,
  key price.ValuationArea                        as ValuationArea,
  key price.CompanyCode                          as CompanyCode,

      f.valuation                                as AccountingValuationView,
      f.valusubtyp                               as AccountingValuationSubview,
      k.CostEstimateStatus                       as CostingStatus,
      k.CostingVariant                           as CostingVariant,
      k.CostingType                              as CostingType,
      f.valuation                                as ControllingValuationType,
      k.CostEstimateMarkingDate                  as CostEstimateMarkingDate,
      k.CostEstimateMarkingUser                  as CostEstimateMarkingUser,
      k.CostEstimateReleaseDate                  as CostEstimateReleaseDate,
      k.CostEstimateReleaseUser                  as CostEstimateReleaseUser,
      price.MaterialPrice                        as PlannedPrice,
      price.FixedPortionOfMaterialPrice          as FixedPortionOfPlannedPrice,
      price.Currency                             as Currency,
      price.Material                             as Material,
      price.MaterialPriceUnitQty                 as MaterialPriceUnitQty,
      ma.meins                                   as BaseUnit,
      // Valuation UOM for Catch Weight Management

      cast (case when ma./cwm/xcwmat = 'X' and
                    ma./cwm/valum <> '' then ma./cwm/valum
                else ma.meins end as /cwm/valum) as ValuationQuantityUnit,
      ma./cwm/xcwmat,

      k.CostEstimateValidityStartDate            as CostEstimateValidityStartDate
}
where
  (
    (
          price.MaterialPriceType              =  'FSTDCE'
      and k.CostEstimateStatus                 =  'VO'
    )
    or(
          price.MaterialPriceType              =  'STDCE'
      and price.MaterialPriceCostEstimateStage =  'C'
      and k.CostEstimateStatus                 =  'FR'
    )
  )
  and(
          tf.MatlPriceDataMigrationStatus      =  'F'

    or(
          hd.price_sdm_yearper                 <> '0000000'
      and hd.price_sdm_yearper                 <= price.MatlPrcValdtyStrtFsclYearPerd
    )
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIALLEDGERPRICE",
"I_MATLLEDGERVALNCRCYROLENAME",
"I_PRODUCTCOSTESTIMATE",
"P_LEDGERVALUATIONCRCYROLE",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLHD",
"CKMLPR",
"FVMKLD",
"MARA",
"MBEW",
"T001K"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/