P_INVENTORYPRICEBYKEYDATE

@AbapCatalog.sqlViewName: 'PINVPRKEYDATE'
@VDM.private: true
@VDM.viewType: #BASIC
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
define view P_InventoryPriceByKeydate
  with parameters
    P_CalendarDate : calendardate

  as select from P_InvPrice_Union_Old(P_CalendarDate :
                 $parameters.P_CalendarDate)    as invprice_old
    inner join   R_MatlPriceDataMigrationStatus as tf on(
      tf.MatlPriceDataMigrationStatus                                                   = 'X'
    )
                                                      or(
                                                        tf.MatlPriceDataMigrationStatus = 'R'
                                                      )
    left outer to one join t001                      as t1 on t1.bukrs = invprice_old.CompanyCode
    left outer to one join I_Distinct_Postingdate    as dp on  dp.FiscalYearVariant = t1.periv
                                                           and dp.CalendarDate      = $parameters.P_CalendarDate
                                                           and dp.IsSpecialPeriod   <> 'X'  

{
  key   invprice_old.CostEstimate as CostEstimate,
  key   invprice_old.CurrencyRole as CurrencyRole,
        invprice_old.Ledger as Ledger,
        dp.FiscalPeriod as FiscalPeriod,
        cast( dp.FiscalYear as fins_gjahr ) as FiscalYear,           
        invprice_old.Material as Material,
        invprice_old.ValuationArea as ValuationArea,
        invprice_old.InventoryValuationType as InventoryValuationType,
        invprice_old.SalesOrder as SalesOrder,
        invprice_old.SalesOrderItem as SalesOrderItem,
        invprice_old.Supplier as Supplier,
        invprice_old.WBSElementInternalID as WBSElementInternalID,
        invprice_old.CompanyCode as CompanyCode,
        invprice_old.BaseUnit as BaseUnit,
        invprice_old.InventorySpecialStockType as InventorySpecialStockType,
        invprice_old.InventorySpecialStockValnType as InventorySpecialStockValnType,
        invprice_old.IsSupplierStockValuation as IsSupplierStockValuation,
        invprice_old.PriceDeterminationControl as PriceDeterminationControl,

        invprice_old.MaterialPriceControl as MaterialPriceControl,
        invprice_old.MaterialPriceUnitQty as MaterialPriceUnitQty,
        invprice_old.Currency as Currency,
        invprice_old.InventoryPrice as InventoryPrice,
        invprice_old.StandardPrice as StandardPrice,
        @Semantics.amount.currencyCode: 'Currency'
        case invprice_old.MaterialPriceControl
            when 'V' then coalesce(invprice_old.InventoryPrice, 0)
            else coalesce(invprice_old.MovingAveragePrice, 0)
        end                                 as ActualPrice,        

        invprice_old.LedgerName as LedgerName,
        invprice_old.CurrencyRoleName as CurrencyRoleName,
        invprice_old.LegalCurrencyRole as LegalCurrencyRole,
        invprice_old.LegalCurrencyRoleName as LegalCurrencyRoleName,
        invprice_old.AccountingValuationView as AccountingValuationView,
        invprice_old.AcctgValnVwSubviewCombinedName as AcctgValnVwSubviewCombinedName,
        invprice_old.LedgerValnCrcyRoleIsUsedInLogs as LedgerValnCrcyRoleIsUsedInLogs

}

where
  (
    (
         invprice_old.MatlPrcMigratedUntilFsclYrPerd = '0000000'
      or invprice_old.MatlPrcMigratedUntilFsclYrPerd > invprice_old.MaxFiscPerCKMLPP
    )
    or   tf.MatlPriceDataMigrationStatus             = 'X'
  )

union all

select from  I_MaterialLedgerPrice          as invprice
  inner join R_MatlPriceDataMigrationStatus as tf on(
    tf.MatlPriceDataMigrationStatus                                                 = 'F'
  )
                                                  or(
                                                    tf.MatlPriceDataMigrationStatus = 'R'
                                                  )
  inner join ckmlhd                         as hd on hd.kalnr = invprice.CostEstimate
  left outer to one join t001                      as t1 on t1.bukrs = invprice.CompanyCode
  left outer to one join I_Distinct_Postingdate    as dp on  dp.FiscalYearVariant = t1.periv
                                                         and dp.CalendarDate      = $parameters.P_CalendarDate
                                                         and dp.IsSpecialPeriod   <> 'X'  
association [0..1] to I_MaterialLedgerPrice        as _StandardPrice  on  _StandardPrice.CostEstimate                   = invprice.CostEstimate
                                                                      and _StandardPrice.Ledger                         = invprice.Ledger
                                                                      and _StandardPrice.CurrencyRole                   = invprice.CurrencyRole
                                                                      and _StandardPrice.MaterialPriceType              = 'STDPR'
                                                                      and _StandardPrice.MaterialPriceSubtype           = ' '
                                                                      and _StandardPrice.MaterialPriceValidityStartDate <= $parameters.P_CalendarDate
                                                                      and _StandardPrice.MaterialPriceValidityEndDate   >= $parameters.P_CalendarDate
association [0..1] to I_MaterialLedgerPrice        as _ActualPrice    on  _ActualPrice.CostEstimate                   = invprice.CostEstimate
                                                                      and _ActualPrice.Ledger                         = invprice.Ledger
                                                                      and _ActualPrice.CurrencyRole                   = invprice.CurrencyRole
                                                                      and _ActualPrice.MaterialPriceType              = 'ACT'
                                                                      and _ActualPrice.MaterialPriceSubtype           = 'ACT'
                                                                      and _ActualPrice.MaterialPriceValidityStartDate <= $parameters.P_CalendarDate
                                                                      and _ActualPrice.MaterialPriceValidityEndDate   >= $parameters.P_CalendarDate                                                                      
association [0..1] to R_LedgerValuationCrcyRole    as _CurrencyInfo   on  _CurrencyInfo.CurrencyRole = invprice.CurrencyRole
                                                                      and _CurrencyInfo.Ledger       = invprice.Ledger
association [0..1] to I_MatlLedgerValnCrcyRoleName as _MLCurrencyInfo on  _MLCurrencyInfo.CompanyCode  = invprice.CompanyCode
                                                                      and _MLCurrencyInfo.CurrencyRole = invprice.CurrencyRole
                                                                      and _MLCurrencyInfo.Ledger       = invprice.Ledger

{
  key invprice.CostEstimate,
  key invprice.CurrencyRole,
      invprice.Ledger,

      dp.FiscalPeriod                                             as FiscalPeriod,
      cast( dp.FiscalYear as fins_gjahr )                         as FiscalYear,      
      hd.matnr                                                    as Material,
      hd.bwkey                                                    as ValuationArea,
      hd.bwtar                                                    as InventoryValuationType,
      hd.vbeln                                                    as SalesOrder,
      hd.posnr                                                    as SalesOrderItem,
      hd.lifnr                                                    as Supplier,
      hd.pspnr                                                    as WBSElementInternalID,
      invprice.CompanyCode                                        as CompanyCode,
      invprice.ValuationQuantityUnit                              as BaseUnit,
      hd.sobkz                                                    as InventorySpecialStockType,
      hd.kzbws                                                    as InventorySpecialStockValnType,
      hd.xobew                                                    as IsSupplierStockValuation,
      hd.mlast                                                    as PriceDeterminationControl,

      invprice.MaterialPriceControl,
      invprice.MaterialPriceUnitQty,
      invprice.Currency,
      @Semantics.amount.currencyCode: 'Currency'
      invprice.MaterialPrice                                      as InventoryPrice,
      @Semantics.amount.currencyCode: 'Currency'
      case invprice.MaterialPriceControl
          when 'S' then coalesce(invprice.MaterialPrice, 0)
          else coalesce(_StandardPrice.MaterialPrice, 0)
      end                                                         as StandardPrice,
      @Semantics.amount.currencyCode: 'Currency' 
      _ActualPrice.MaterialPrice                                  as ActualPrice,
      _CurrencyInfo.LedgerName                                    as LedgerName,
      _CurrencyInfo.CurrencyRoleName                              as CurrencyRoleName,
      _CurrencyInfo.LegalCurrencyRole                             as LegalCurrencyRole,
      _CurrencyInfo.LegalCurrencyRoleName                         as LegalCurrencyRoleName,
      cast(_CurrencyInfo.AccountingValuationView as fins_valutyp) as AccountingValuationView,
      _CurrencyInfo.AcctgValnVwSubviewCombinedName                as AcctgValnVwSubviewCombinedName,
      _MLCurrencyInfo.LedgerValnCrcyRoleIsUsedInLogs              as LedgerValnCrcyRoleIsUsedInLogs

}
where
  (
    (
          hd.price_sdm_yearper                    <> '0000000'
      and hd.price_sdm_yearper                    <= invprice.MatlPrcValdtyStrtFsclYearPerd
    )
    or    tf.MatlPriceDataMigrationStatus         =  'F'
  )
  and(
          invprice.MaterialPriceType              =  'INVPR'
    and   invprice.MaterialPriceSubtype           =  ' '
    and   invprice.MaterialPriceValidityStartDate <= $parameters.P_CalendarDate
    and   invprice.MaterialPriceValidityEndDate   >= $parameters.P_CalendarDate
  )
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_DISTINCT_POSTINGDATE",
"I_MATERIALLEDGERPRICE",
"I_MATLLEDGERVALNCRCYROLENAME",
"P_INVPRICE_UNION_OLD",
"R_LEDGERVALUATIONCRCYROLE",
"R_MATLPRICEDATAMIGRATIONSTATUS",
"CKMLHD",
"T001"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/