C_CmmdtyOrderRequestStatus

@AbapCatalog.sqlViewName: 'CCMMDTYORDSTATUS'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Consumption View for Order Req Status'
@VDM.viewType: #CONSUMPTION
@Analytics.dataCategory: #CUBE
@Metadata.allowExtensions               : true
@ObjectModel: {
    usageType: {
                 serviceQuality : #D,
                 sizeCategory   : #L,
                 dataClass      : #TRANSACTIONAL } }
@OData.publish: true

define view C_CmmdtyOrderRequestStatus
  as select from    I_CommodityOrderRequest

  -- To get Order Request Kind
    left outer join I_CmmdtyOrderRequestType   as CmmdtyOrderRequestType on I_CommodityOrderRequest.CmmdtyOrderRequestType = CmmdtyOrderRequestType.CmmdtyOrderRequestType

  -- To get Messages Count
    left outer join I_CmmdtyOrdReqMsgsByOrdReq as MessageCount           on I_CommodityOrderRequest.CommodityOrderRequestUUID = MessageCount.CommodityOrderRequestUUID

{

  -- Transmitted Orders
  @DefaultAggregation: #SUM
  case   I_CommodityOrderRequest.CmmdtyOrderRequestStatus
  when '02' then
  cast(1  as cmmfdor_cmmdtytransmittedords)
  else
    0
  end as   CmmdtyOrderNrOfTransmittedOrds,

  -- Working Orders
  @DefaultAggregation: #SUM
  case   I_CommodityOrderRequest.CmmdtyOrderRequestStatus
  when '03' then
  cast(1  as cmmfdor_cmmdtyworkingorders)
  else
    0
  end as   CmmdtyOrdReqNrOfWorkingOrders,

  -- Partially Filled Orders
  @DefaultAggregation: #SUM
  case   I_CommodityOrderRequest.CmmdtyOrderRequestStatus
  when '04' then
  cast(1  as cmmfdor_cmmdtyprtlyfilledords)
  else
    0
  end as   CmmdtyOrdNrOfPrtlyFilledOrds,

  -- Hedge Orders
  @DefaultAggregation: #SUM
  case
  when CmmdtyOrderRequestType.CmmdtyOrderRequestKind = 'HO' and MessageCount.NumberOfOpenMessages > 0 then
  cast(1  as cmmfdor_cmmdtyhedgeorders)
  else
    0
  end as   CmmdtyOrderReqNrOfHedgeOrders,

  -- Pricing Orders
  @DefaultAggregation: #SUM
  case
  when CmmdtyOrderRequestType.CmmdtyOrderRequestKind = 'PO' and MessageCount.NumberOfOpenMessages > 0 then
  cast(1  as cmmfdor_cmmdtypricingorders)
  else
    0
  end as   CmmdtyOrdReqNrOfPricingOrders,

  -- Total Orders
  @DefaultAggregation: #SUM
  case
  when  I_CommodityOrderRequest.CommodityOrderRequestUUID is not null then
  cast(1  as cmmfdor_cmmdtytotalorders)
  else
    0
  end as   NumberOfOrders,     

--Orders with Messages
 @DefaultAggregation: #SUM
  case
  when  MessageCount.NumberOfOpenMessages > 0 then
  cast(1  as cmmfdor_cmmdtytotalorders)
  else
    0
  end as   NumberOfErrorRecords 

}