I_CommoditySubAccountVH

@AbapCatalog.sqlViewName: 'ICMMDTYDRVTVSA'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@AccessControl.personalData.blocking: #REQUIRED
@EndUserText.label: 'Commodity Subaccount Value Help View'
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType:{ serviceQuality: #C, sizeCategory : #XL, dataClass: #MIXED}
@VDM.viewType: #COMPOSITE
@ObjectModel.representativeKey: 'CommoditySubAccount'
@Metadata.ignorePropagatedAnnotations:true
@Search.searchable: true
@Consumption.ranked: true

define view I_CommoditySubAccountVH
  as select from    I_CommoditySubAccount        as CommoditySubAccount
    inner join      I_CmmdtyDrvtvContrMicPhys    as DcsMicCommodity  on  DcsMicCommodity.DerivativeContrSpecification = CommoditySubAccount.DerivativeContrSpecification
                                                                     and DcsMicCommodity.MarketIdentifierCode         = CommoditySubAccount.MarketIdentifierCode

  -- Association to get Counter Party F4 Values
    left outer join I_CmmdtyOrdReqCounterpartyVH as CounterpartyVH   on CommoditySubAccount.Counterparty = CounterpartyVH.BusinessPartner

  -- To get Company Code Name
    left outer join I_CompanyCode                as CompanyCode      on CommoditySubAccount.CompanyCode = CompanyCode.CompanyCode

  -- To get Broker Name
    left outer join I_BusinessPartner            as BusinessPartner  on BusinessPartner.BusinessPartner = CommoditySubAccount.CommodityDerivativeBroker

  -- To get Off footprint/on footprint flag
    left outer join I_CmmdtySubAcctFootPrint     as SubAcctFootPrint on CommoditySubAccount.CommoditySubAccount = SubAcctFootPrint.CommoditySubAccount

{
@ObjectModel.text.element: ['CommoditySubAccountName'] 
@Search:{defaultSearchElement:true,ranking:#HIGH,fuzzinessThreshold:0.7}
  key CommoditySubAccount.CommoditySubAccount                                                                         as CommoditySubAccount,
  
  @Semantics.text: true
@Search:{defaultSearchElement:true,ranking:#LOW,fuzzinessThreshold:0.7}  
      CommoditySubAccount.CommoditySubAccountName                                                                     as CommoditySubAccountName,
@Search:{defaultSearchElement:true,ranking:#LOW,fuzzinessThreshold:0.7}  
      SubAcctFootPrint.CmmdtySubAccountIsOffFootPrint                                                                 as CmmdtySubAccountIsOffFootPrint,
@Search:{defaultSearchElement:true,ranking:#LOW,fuzzinessThreshold:0.7}        
      CommoditySubAccount.Commodity                                                                                   as Commodity,
      @UI.hidden: true
      DcsMicCommodity.CommodityName                                                                                   as CommodityName,
@Search:{defaultSearchElement:true,ranking:#LOW,fuzzinessThreshold:0.7}  
      CommoditySubAccount.CompanyCode                                                                                 as CompanyCode,
      @UI.hidden: true
      CompanyCode.CompanyCodeName                                                                                     as CompanyCodeName,
@Search:{defaultSearchElement:true,ranking:#LOW,fuzzinessThreshold:0.7}  
      CommoditySubAccount.DerivativeContrSpecification                                                                as DerivativeContrSpecification,
      @UI.hidden: true
      DcsMicCommodity.DerivativeContrSpecName                                                                         as DerivativeContrSpecName,
@Search:{defaultSearchElement:true,ranking:#LOW,fuzzinessThreshold:0.7}  
      CommoditySubAccount.MarketIdentifierCode                                                                        as MarketIdentifierCode,
      @UI.hidden: true
      DcsMicCommodity.MarketIdentifierCodeName                                                                        as MarketIdentifierCodeName,
@Search:{defaultSearchElement:true,ranking:#LOW,fuzzinessThreshold:0.7}  
      CommoditySubAccount.CommodityDerivativeBroker                                                                   as CommodityDerivativeBroker,
      @UI.hidden: true
      BusinessPartner.BusinessPartnerFullName                                                                         as BusinessPartnerFullName,
      
      @UI.hidden: true
      CommoditySubAccount.ReferenceBrokerAccount                                                                      as ReferenceBrokerAccount,

      @UI.hidden: true
      CommoditySubAccount.Counterparty                                                                                as Counterparty,
      @UI.hidden: true
      cast( CounterpartyVH.BusinessPartnerFullName
       as cmmfsa_counterpartyname)                                                                                    as CounterpartyFullName,

      @UI.hidden: true
      DcsMicCommodity.UnitOfMeasureNumberOfDecimals                                                                   as UnitOfMeasureNumberOfDecimals

}
where
      CmmdtySubAccountStatus          =  '02' --Active
  and CmmdtySubAccountIsBlocked       =  ' '  --Not Blocked
  and CommodityDerivativeBroker       <> ''
  and DcsMicCommodity.ValidFrom       <= $session.system_date
  and DcsMicCommodity.DerivativeContractSpecStatus =  '002' -- DCS Released