P_MATVAL_MANAGE_CKML

@AbapCatalog.sqlViewName: 'PMATVALMGMCKML'
@VDM.private: true
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_Matval_Manage_Ckml
  // use ckmlpr, because it exists also for header of split valuated material

  as select from    ckmlpr                         as pr
    inner join      R_MatlPriceDataMigrationStatus as tf   on(
        tf.MatlPriceDataMigrationStatus                                                      = 'X'
      )
                                                           or(
                                                             tf.MatlPriceDataMigrationStatus = 'R'
                                                           )
    left outer join ckmlhd                         as hd   on hd.kalnr = pr.kalnr
    left outer join mbew                           as m    on  m.kaln1 = pr.kalnr // for mother segment

                                                           and m.bwtar is initial
                                                           and m.bwtty is not initial
    inner join      t001k                          as tk   on tk.bwkey = hd.bwkey
                                                           or tk.bwkey = m.bwkey
    inner join      I_MatlLedgerValnCrcyRoleName   as lvcr on  lvcr.CompanyCode  = tk.bukrs // duplicate entries for additional ledgers

                                                           and lvcr.CurrencyRole = pr.curtp
    left outer join ckmlcr                         as cr   on  cr.kalnr  = pr.kalnr
                                                           and cr.curtp  = pr.curtp
                                                           and cr.untper = '000'
  association [0..1] to I_WBSElementBasicData as _WBSElementBasicData on hd.pspnr = _WBSElementBasicData.WBSElementInternalID
{
  key pr.kalnr                                      as CostEstimate,
  key lvcr.LedgerValuationCurrencyRole              as LedgerValuationCurrencyRole,
  key cr.bdatj                                      as FiscalYear,
  key cr.poper                                      as FiscalPeriod,
  key hd.bwkey                                      as ValuationArea,
  key hd.matnr                                      as Material,
  key hd.bwtar                                      as InventoryValuationType,

      hd.sobkz                                      as InvtryValnSpecialStockType,
      hd.vbeln                                      as SalesOrder,
      hd.posnr                                      as SalesOrderItem,
      hd.lifnr                                      as Supplier,
      hd.pspnr                                      as WBSElementInternalID,
      _WBSElementBasicData.WBSElementExternalID     as WBSElementExternalID,
      concat(cr.bdatj, cr.poper)                    as FiscalYearPeriod,

      hd.kzbws                                      as InventorySpecialStockValnType,
      cast(hd.mlast as fml_abst)                    as PriceDeterminationControl,

      cast(pr.zkprs as fml_dzkprs)                  as FuturePrice,
      cast(pr.zprsdat as fml_zprsdat)               as FuturePriceValidityStartDate,
      pr.zplpr                                      as FuturePlannedPrice,

      cast(cr.vprsv as inventoryvaluationprocedure) as MaterialPriceControl,
      cr.peinh                                      as MaterialPriceUnitQty,
      cr.pvprs                                      as MovingAveragePrice,
      cr.stprs                                      as StandardPrice,

      cr.waers                                      as Currency,

      _WBSElementBasicData
}
where
         pr.sdm_version                  is initial

  and(
         tf.MatlPriceDataMigrationStatus =                                              'X'

    or(
         hd.price_sdm_yearper            =                                              '0000000'

      or hd.price_sdm_yearper            >                                              concat(
                                cr.bdatj, cr.poper
                                )
    )
  )

union all select from I_MaterialLedgerPrice          as price
  inner join          R_MatlPriceDataMigrationStatus as tf    on(
       tf.MatlPriceDataMigrationStatus                                                          = 'F'
     )
                                                              or(
                                                                tf.MatlPriceDataMigrationStatus = 'R'
                                                              )
// get unique CURTP

  inner join          I_MatlLedgerValnCrcyRoleName   as lvcr  on  lvcr.CompanyCode  = price.CompanyCode
                                                              and lvcr.Ledger       = price.Ledger
                                                              and lvcr.CurrencyRole = price.CurrencyRole
// price_type = INVPR

  left outer join     I_MaterialLedgerPrice          as INVPR on  price.CostEstimate         = INVPR.CostEstimate
                                                              and price.Ledger               = INVPR.Ledger
                                                              and price.CurrencyRole         = INVPR.CurrencyRole
                                                              and price.CompanyCode          = INVPR.CompanyCode
                                                              and price.ValuationArea        = INVPR.ValuationArea
                                                              and INVPR.MaterialPriceType    = 'INVPR'
                                                              and INVPR.MaterialPriceSubtype = ''

  left outer join     ckmlhd                         as hd    on price.CostEstimate = hd.kalnr

association [0..1] to I_WBSElementBasicData as _WBSElementBasicData on  hd.pspnr = _WBSElementBasicData.WBSElementInternalID
association [0..1] to I_MaterialLedgerPrice as _STDPR               on  price.CostEstimate                    =  _STDPR.CostEstimate
                                                                    and price.Ledger                          =  _STDPR.Ledger
                                                                    and price.CurrencyRole                    =  _STDPR.CurrencyRole
                                                                    and price.CompanyCode                     =  _STDPR.CompanyCode
                                                                    and price.ValuationArea                   =  _STDPR.ValuationArea
                                                                    and _STDPR.MaterialPriceType              =  'STDPR'
                                                                    and _STDPR.MaterialPriceSubtype           =  ''
                                                                    and _STDPR.MatlPrcValdtyStrtFsclYearPerd  <= INVPR.MatlPrcValdtyStrtFsclYearPerd
                                                                    and _STDPR.MatlPrcValdtyEndFsclYearPeriod >= INVPR.MatlPrcValdtyEndFsclYearPeriod
association [0..1] to I_MaterialLedgerPrice as _ACT                 on  _ACT.CostEstimate                   =  INVPR.CostEstimate
                                                                    and _ACT.CurrencyRole                   =  INVPR.CurrencyRole
                                                                    and _ACT.Ledger                         =  INVPR.Ledger
                                                                    and _ACT.CompanyCode                    =  INVPR.CompanyCode
                                                                    and _ACT.ValuationArea                  =  INVPR.ValuationArea
                                                                    and _ACT.MaterialPriceType              =  'ACT'
                                                                    and _ACT.MaterialPriceSubtype           =  'ACT'
                                                                    and _ACT.MatlPrcValdtyStrtFsclYearPerd  <= INVPR.MatlPrcValdtyStrtFsclYearPerd
                                                                    and _ACT.MatlPrcValdtyEndFsclYearPeriod >= INVPR.MatlPrcValdtyEndFsclYearPeriod
association [0..1] to I_MaterialLedgerPrice as _MAP                 on  _MAP.CostEstimate                   =  INVPR.CostEstimate
                                                                    and _MAP.CurrencyRole                   =  INVPR.CurrencyRole
                                                                    and _MAP.Ledger                         =  INVPR.Ledger
                                                                    and _MAP.CompanyCode                    =  INVPR.CompanyCode
                                                                    and _MAP.ValuationArea                  =  INVPR.ValuationArea
                                                                    and _MAP.MaterialPriceType              =  'MAP'
                                                                    and _MAP.MaterialPriceSubtype           =  ''
                                                                    and _MAP.MatlPrcValdtyStrtFsclYearPerd  <= INVPR.MatlPrcValdtyStrtFsclYearPerd
                                                                    and _MAP.MatlPrcValdtyEndFsclYearPeriod >= INVPR.MatlPrcValdtyEndFsclYearPeriod
association [0..1] to I_MaterialLedgerPrice as _FSTDCE              on  _FSTDCE.CostEstimate                  = INVPR.CostEstimate
                                                                    and _FSTDCE.CurrencyRole                  = INVPR.CurrencyRole
                                                                    and _FSTDCE.Ledger                        = INVPR.Ledger
                                                                    and _FSTDCE.CompanyCode                   = INVPR.CompanyCode
                                                                    and _FSTDCE.ValuationArea                 = INVPR.ValuationArea
                                                                    and _FSTDCE.MaterialPriceType             = 'FSTDCE'
                                                                    and _FSTDCE.MaterialPriceSubtype          = ''
                                                                    and _FSTDCE.MatlPrcValdtyStrtFsclYearPerd = INVPR.MatlPrcValdtyStrtFsclYearPerd


{
  key price.CostEstimate,
  key lvcr.LedgerValuationCurrencyRole          as LedgerValuationCurrencyRole,
  key INVPR.MatlPrcValidityStartFiscalYear      as FiscalYear,
  key INVPR.MatlPrcValdtyStartFiscalPeriod      as FiscalPeriod,
  key price.ValuationArea,
  key price.Material,
  key price.InventoryValuationType,

      price.InvtryValnSpecialStockType,
      hd.vbeln                                  as SalesOrder,
      hd.posnr                                  as SalesOrderItem,
      hd.lifnr                                  as Supplier,
      hd.pspnr                                  as WBSElementInternalID,
      _WBSElementBasicData.WBSElementExternalID as WBSElementExternalID,

      INVPR.MatlPrcValdtyStrtFsclYearPerd       as FiscalYearPeriod,

      hd.kzbws                                  as InventorySpecialStockValnType,
      cast(hd.mlast as fml_abst)                as PriceDeterminationControl,

      price.MaterialPrice                       as FuturePrice,
      price.MaterialPriceValidityStartDate      as FuturePriceValidityStartDate,
      _FSTDCE.MaterialPrice                     as FuturePlannedPrice,

      INVPR.MaterialPriceControl                as MaterialPriceControl,
      price.MaterialPriceUnitQty                as MaterialPriceUnitQty,


      case INVPR.MaterialPriceControl
        when 'S'
            then case hd.mlast
                when '2' then coalesce(_MAP.MaterialPrice,0)
                when '3' then coalesce(_ACT.MaterialPrice,0)
            end
        when 'V'
            then coalesce(INVPR.MaterialPrice,0)
        end                                     as MovingAveragePrice,

      case INVPR.MaterialPriceControl
        when 'S' then coalesce(INVPR.MaterialPrice,0)
        else coalesce(_STDPR.MaterialPrice,0)
        end                                     as StandardPrice,

      price.Currency                            as Currency,

      _WBSElementBasicData
}

where
           price.MaterialPriceType              =  'FUTURE'

  and(
           tf.MatlPriceDataMigrationStatus      =  'F'

    or(
           hd.price_sdm_yearper                 <> '0000000'
      and(
           hd.price_sdm_yearper                 <= INVPR.MatlPrcValdtyStrtFsclYearPerd
        or INVPR.MatlPrcValidityStartFiscalYear is null
      )
    )
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_MATERIALLEDGERPRICE",
"I_MATLLEDGERVALNCRCYROLENAME",
"I_WBSELEMENTBASICDATA",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLCR",
"CKMLHD",
"CKMLPR",
"MBEW",
"T001K"
],
"ASSOCIATED":
[
"I_WBSELEMENTBASICDATA"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/