I_CmmdtyIntCounterpartySubAcct

@AbapCatalog.sqlViewName: 'ICMMDTYCNTRSUB'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Subaccounts assgn. to int. Counterparty'
@VDM.viewType: #BASIC
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.sizeCategory: #S
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.dataClass: #MASTER
@AccessControl.personalData.blocking : #REQUIRED

define view I_CmmdtyIntCounterpartySubAcct
  as select from    cmmfsa_d_cntrsub
    inner join      cmmfsa_d_subacct                                           on  cmmfsa_d_cntrsub.mandt         = cmmfsa_d_subacct.mandt
                                                                               and cmmfsa_d_cntrsub.subaccount_id = cmmfsa_d_subacct.commoditysubaccount

    left outer join cmmfsa_d_offfoot                                           on  cmmfsa_d_cntrsub.mandt         = cmmfsa_d_offfoot.mandt
                                                                               and cmmfsa_d_cntrsub.subaccount_id = cmmfsa_d_offfoot.commoditysubaccount

    left outer join I_DerivativeContractSpecText as DerivativeContractSpecText on  DerivativeContractSpecText.DerivativeContrSpecification = cmmfsa_d_subacct.derivativecontrspecification
                                                                               and DerivativeContractSpecText.Language                     = $session.system_language

    left outer join i_MarketIdentifierCodeText   as MarketIdentifierCodeText     on  MarketIdentifierCodeText.MarketIdentifierCode = cmmfsa_d_subacct.marketidentifiercode
                                                                                 and MarketIdentifierCodeText.Language             = $session.system_language

    left outer join I_CmmdtyText                 as CmmdtyText                                 on  CmmdtyText.Commodity = cmmfsa_d_subacct.commodity
                                                                                               and CmmdtyText.Language  = $session.system_language

  -- To get Broker Name
    left outer join I_BusinessPartner            as BusinessPartner                       on BusinessPartner.BusinessPartner = cmmfsa_d_subacct.commodityderivativebroker

{
  key cmmfsa_d_cntrsub.int_counterparty                                   as Counterparty,
  key cmmfsa_d_subacct.commoditysubaccount                                as CommoditySubAccount,
      cmmfsa_d_subacct.commoditysubaccountuuid                            as CommoditySubAccountUUID,
      cmmfsa_d_subacct.commoditysubaccountname                            as CommoditySubAccountName,
      cmmfsa_d_offfoot.is_off_footprint                                   as CmmdtySubAccountIsOffFootPrint,

      cmmfsa_d_subacct.commodity                                          as Commodity,
      CmmdtyText.CommodityName                                            as CommodityName,

      cmmfsa_d_subacct.marketidentifiercode                               as MarketIdentifierCode,
      MarketIdentifierCodeText.MarketIdentifierCodeName                   as MarketIdentifierCodeName,

      cmmfsa_d_subacct.derivativecontrspecification                       as DerivativeContrSpecification,
      DerivativeContractSpecText.DerivativeContrSpecName                  as DerivativeContrSpecName,

      cmmfsa_d_subacct.commodityderivativebroker                          as CommodityDerivativeBroker,
      BusinessPartner.BusinessPartnerFullName                             as BusinessPartnerFullName,
      cmmfsa_d_subacct.companycode                                        as CompanyCode,
      cmmfsa_d_subacct.referencebrokeraccount                             as ReferenceBrokerAccount

}
where
      cmmfsa_d_subacct.cmmdtysubaccountstatus = '02' // Status:active

  and cmmfsa_d_subacct.succ_archive           = ' ' // not archived

  and cmmfsa_d_cntrsub.mandt                  = $session.client