Tuesday, July 24, 2018

Examples of CDS Views

@EndUserText.label: 'Billing Document Item'
@Analytics: { datacategory: #DIMENSION, dataExtraction.enabled: true }
@VDM.businessObject: 'BillingDocumentItem'
@VDM.viewtype: #BASIC
@AbapCatalog.sqlViewName: 'ESJIBILGDOCITMQ'

define view ESJI_BillingDocumentItemQuery
with parameters
    P_Language : syLangu
as select distinct from
vbrp
left outer join vbrk on vbrk.vbeln = vbrp.vbeln
left outer join vbkd on vbkd.vbeln = vbrp.aubel
//Association
association[1..1] to makt   on makt.matnr       = vbrp.matnr and makt.spras = $parameters.P_Language
association[0..1] to dd07v  on dd07v.domvalue_l = vbrk.rfbsk
                           and dd07v.ddlanguage = $parameters.P_Language
                           and dd07v.domname    = 'RFBSK'
association[1..1] to ESJI_BillingDocumentQuery  as _BillingDocument             on $projection.BillingDocument             = _BillingDocument.BillingDocument
association[0..*] to ESJI_SDDocumentItemPartner as _Partner                     on $projection.BillingDocument             = _Partner.SDDocument
                                                                               and $projection.BillingDocumentItem         = _Partner.SDDocumentItem
association[0..1] to ESJI_Material              as _Material                    on $projection.Material                    = _Material.Material
association[0..1] to ESJI_UnitOfMeasure         as _BillingQuantityUnit         on $projection.BillingQuantityUnit         = _BillingQuantityUnit.UnitOfMeasure
association[0..1] to ESJI_UnitOfMeasure         as _ItemWeightUnit              on $projection.ItemWeightUnit              = _ItemWeightUnit.UnitOfMeasure
//association[0..1] to ESJI_SDDocumentCategory    as _SDDocumentCategory          on $projection.sddocumentcategory          = _SDDocumentCategory.SDDocumentCategory //TODO: To be added once field is ready
association[0..1] to ESJI_User                  as _CreatedByUser               on $projection.CreatedByUser               = _CreatedByUser.UserID
association[0..1] to ESJI_Division              as _OrganizationDivision        on $projection.OrganizationDivision        = _OrganizationDivision.Division
association[0..1] to ESJI_Division              as _Division                    on $projection.Division                    = _Division.Division
association[0..1] to ESJI_UnitOfMeasure         as _BaseUnit                    on $projection.BaseUnit                    = _BaseUnit.UnitOfMeasure
association[0..1] to ESJI_UnitOfMeasure         as _ItemVolumeUnit              on $projection.ItemVolumeUnit              = _ItemVolumeUnit.UnitOfMeasure
association[0..1] to ESJI_Country               as _BillToPartyCountry          on $projection.BillToPartyCountry          = _BillToPartyCountry.Country
association[0..1] to ESJI_Region                as _BillToPartyRegion           on $projection.BillToPartyCountry          = _BillToPartyRegion.Country
                                                                               and $projection.BillToPartyRegion           = _BillToPartyRegion.Region
association[0..1] to ESJI_Currency              as _TransactionCurrency         on $projection.TransactionCurrency         = _TransactionCurrency.Currency
//Filtered Association for ReferenceSDDocumentItem
association[0..1] to ESJI_SalesDocumentItem     as _ReferenceSalesDocumentItem  on $projection.ReferenceSDDocument         = _ReferenceSalesDocumentItem.SalesDocument
                                                                               and $projection.ReferenceSDDocumentItem     = _ReferenceSalesDocumentItem.SalesDocumentItem
                                                                              and ($projection.ReferenceSDDocumentCategory = 'C'
                                                                               or $projection.ReferenceSDDocumentCategory  ='L')
association[0..1] to ESJI_SDDocumentCategory    as _ReferenceSDDocumentCategory on $projection.ReferenceSDDocumentCategory = _ReferenceSDDocumentCategory.SDDocumentCategory
association[0..1] to ESJI_SalesDocumentItem     as _SalesDocumentItem           on $projection.SalesDocument               = _SalesDocumentItem.SalesDocument
                                                                               and $projection.SalesDocumentItem           = _SalesDocumentItem.SalesDocumentItem
association[0..1] to ESJI_SDDocumentCategory    as _SalesSDDocumentCategory     on $projection.SalesSDDocumentCategory     = _SalesSDDocumentCategory.SDDocumentCategory
association[0..1] to ESJI_SDDocumentReason      as _SDDocumentReason            on $projection.SDDocumentReason            = _SDDocumentReason.SDDocumentReason
{
  //Key
  key vbrp.vbeln as BillingDocument,
  key vbrp.posnr as BillingDocumentItem,
  vbrp.posnr as HeaderText,
  'BUS2037' as ObjectType,
  //Category
//  VBTYP as SDDocumentCategory,
//  pstyv as SalesDocumentItemCategory,
//  posar as SalesDocumentItemType,
//
  //Admin
  vbrp.ernam as CreatedByUser,
//  @Semantics.date.creationDate
//  vbrp.erdat as CreationDate,
//  @Semantics.date.creationDate
//  vbrp.erzet as CreationTime,
//
//  //Organization
  vkorg as SalesOrganization,
  spara as OrganizationDivision,
  vbrp.spart as Division,
//  vkbur as SalesOffice,
//
  //Product
  matnr as Material,
//  werks as Plant,
//  lgort as StorageLocation,
//
  //Billing
  fkart as BillingDocumentType,
  makt.maktx as BillingDocumentItemText,
//  vbrp.fbuda as ServicesRenderedDate,
  @DefaultAggregation: #SUM
  @Semantics.quantity.unitOfMeasure: 'BillingQuantityUnit'
  fkimg as BillingQuantity,
  @Semantics.unitOfMeasure: true
  vrkme as BillingQuantityUnit,
//  @DefaultAggregation: #SUM
//  @Semantics.quantity.unitOfMeasure: 'BaseUnit'
//  fklmg as BillingQuantityInBaseUnit,
  @Semantics.unitOfMeasure: true
  meins as BaseUnit,
//  umvkn as BillingToBaseQuantityDnmntr,
//  umvkz as BillingToBaseQuantityNmrtr,
//  @DefaultAggregation: #SUM
//  @Semantics.quantity.unitOfMeasure: 'ItemWeightUnit'
//  brgew as ItemGrossWeight,
  @DefaultAggregation: #SUM
  @Semantics.quantity.unitOfMeasure: 'ItemWeightUnit'
  ntgew as ItemNetWeight,
  @Semantics.unitOfMeasure: true
  gewei as ItemWeightUnit,
//  @DefaultAggregation: #SUM
//  @Semantics.quantity.unitOfMeasure: 'ItemVolumeUnit'
//  volum as ItemVolume,
  @Semantics.unitOfMeasure: true
  voleh as ItemVolumeUnit,
  lland_auft as BillToPartyCountry,
  regio_auft as BillToPartyRegion,
//
  //Pricing
  @DefaultAggregation: #SUM
  @Semantics.amount.currencyCode: 'TransactionCurrency'
  vbrp.netwr as ItemNetAmountOfBillingDoc,
  @Semantics.currencyCode: true
  waerk as TransactionCurrency,
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  brtwr as ItemGrossAmountOfBillingDoc,
//  vbrp.prsdt as PricingDate,
//  vbrp.kursk as PriceDetnExchangeRate,
//  smeng as PricingScaleQuantityInBaseUnit,
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  mwsbp as TaxAmount,
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  wavwr as CostAmount,
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  kzwi1 as Subtotal1Amount,
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  kzwi2 as Subtotal2Amount,
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  kzwi3 as Subtotal3Amount,
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  kzwi4 as Subtotal4Amount,
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  kzwi5 as Subtotal5Amount,
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  kzwi6 as Subtotal6Amount,
//
//  kowrr as StatisticalValue,

  //PurchaseOrderByCustomer
  vbkd.bstkd as PurchaseOrderByCustomer,

  //PostingStatusID
  rfbsk as PostingStatusID,

//  //Payment
//  @DefaultAggregation: #SUM
//  @Semantics.amount.currencyCode: 'TransactionCurrency'
//  skfbp as EligibleAmountForCashDiscount,
//
//  //Accounting
//  gsber as BusinessArea,
//  prctr as ProfitCenter,
//  vbrp.kursk_dat as ExchangeRateDate,

  //Reference
  vgbel as ReferenceSDDocument,
  vgpos as ReferenceSDDocumentItem,
  vgtyp as ReferenceSDDocumentCategory,
  aubel as SalesDocument,
  aupos as SalesDocumentItem,
  autyp as SalesSDDocumentCategory,
//  vkgrp as SalesGroup,
  augru_auft as SDDocumentReason,
//  vstel as ShippingPoint,

 // PostingStatusText
  coalesce(dd07v.ddtext, rfbsk) as PostingStatusText,
  //association
  _BillingDocument,
  _Partner,
  _CreatedByUser,
  _OrganizationDivision,
  _Division,
  _Material,
  _BillingQuantityUnit,
  _BaseUnit,
  _ItemWeightUnit,
  _ItemVolumeUnit,
  _BillToPartyCountry,
  _BillToPartyRegion,
  _TransactionCurrency,
//  Filtered Association for ReferenceSDDocumentItem
  _ReferenceSalesDocumentItem,
  _ReferenceSDDocumentCategory,
  _SalesDocumentItem,
  _SalesSDDocumentCategory,
  _SDDocumentReason
}