C_PH_InventoryQuery

@AbapCatalog.sqlViewName: 'CPHINVENTORYQRY'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@VDM.viewType: #CONSUMPTION
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@ObjectModel.usageType.serviceQuality:#D
@ObjectModel.usageType.sizeCategory : #XL
@ObjectModel.usageType.dataClass:#MIXED
@ClientHandling.algorithm: #AUTOMATED
@EndUserText.label: 'Ph Inventory Query Philippines'

define view C_PH_InventoryQuery
  as select from C_PH_StockQuery
    inner join   I_Material         on  C_PH_StockQuery.Material = I_Material.Material
    inner join   I_MaterialText     on  C_PH_StockQuery.Material = I_MaterialText.Material
                                    and I_MaterialText.Language  = 'E'
    //inner join   t001k              on  C_PH_StockQuery.Plant    = t001k.bwkey

   // inner join   t001               on  t001k.bukrs = t001.bukrs                                

    inner join   I_ProductValuation on  C_PH_StockQuery.Material = I_ProductValuation.Product
                                    and C_PH_StockQuery.Plant    = I_ProductValuation.ValuationArea
                                    and I_ProductValuation.ValuationType = ' '

{

  key C_PH_StockQuery.Plant                                                        as  Plant,
  key C_PH_StockQuery.Material                                                     as  Material,
  key C_PH_StockQuery.ValuationType                                                as  ValuationType,
  key I_ProductValuation.CompanyCode                                               as  CompanyCode,
 // key t001.bukrs                                                                   as  CompanyCode,

  key C_PH_StockQuery.FiscalYearCurrentPeriod                                      as  FiscalYearCurrentPeriod,
  key C_PH_StockQuery.IsCurrentFiscalPeriod                                        as  IsCurrentFiscalPeriod,
  
  cast(
    concat(
    concat(
      concat( 
        concat(C_PH_StockQuery.IsCurrentFiscalPeriod,'/'),
      substring(
        cast(dats_add_days(
          dats_add_months(
              cast( concat( concat( C_PH_StockQuery.FiscalYearCurrentPeriod, C_PH_StockQuery.IsCurrentFiscalPeriod ), '01' ) as abap.dats ),
              1, 'FAIL'),
            -1, 'FAIL'
          ) as char8)
        , 7, 2)),'/'), 
        C_PH_StockQuery.FiscalYearCurrentPeriod)
    as char10) as FiscalEndDateText,
    
  
      C_PH_StockQuery.MovingAveragePrice,
      C_PH_StockQuery.StandardPrice,
      C_PH_StockQuery.ProductPriceControl,
      C_PH_StockQuery.TotalValStockQuantity,
      C_PH_StockQuery.TotalValStockValue,

      case
      when C_PH_StockQuery.ProductPriceControl = 'S'
      then
      C_PH_StockQuery.StandardPrice
      when C_PH_StockQuery.ProductPriceControl = 'V'
      then
      C_PH_StockQuery.MovingAveragePrice
      else
      C_PH_StockQuery.StandardPrice
      end                                                                          as  ItemPrice,

      I_ProductValuation.Currency,
      //t001.waers                                                                   as Currency,

      C_PH_StockQuery.ValuationClass,
      I_Material.MaterialType,
      I_Material.MaterialBaseUnit,
     // I_MaterialText.MaterialName

      //concat('"', concat(I_MaterialText.MaterialName, '"')) as MaterialName

      concat('"', concat(replace(I_MaterialText.MaterialName,'"','""'), '"')) as MaterialName

}where C_PH_StockQuery.ValuationType = ' ';