C_BillingDocumentItemBasicDEX

@ClientHandling.algorithm: #SESSION_VARIABLE 
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.serviceQuality: #C
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.representativeKey: 'BillingDocumentItem'
@EndUserText.label: 'Billing Document Item Basic Extraction Data'
@VDM.viewType: #CONSUMPTION
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.modelingPattern: #NONE
@ObjectModel.supportedCapabilities:  [ #EXTRACTION_DATA_SOURCE ]
@Analytics: { 
    dataCategory: #FACT,
    dataExtraction: {
        enabled: true,
        delta.changeDataCapture: {
            mapping:[
                {
                    table: 'vbrp', role: #MAIN,
                    viewElement: ['BillingDocument', 'BillingDocumentItem'],
                    tableElement: ['vbeln', 'posnr']
                },
                {
                    table: 'vbrk', role: #LEFT_OUTER_TO_ONE_JOIN,
                    viewElement: ['BillingDocument'],
                    tableElement: ['vbeln']},
                {
                    table: 't001', role: #LEFT_OUTER_TO_ONE_JOIN,
                    viewElement: ['CompanyCode'],
                    tableElement: ['bukrs']},
                {
                    table: 'tvko', role: #LEFT_OUTER_TO_ONE_JOIN,
                    viewElement: ['SalesOrganization'],
                    tableElement: ['vkorg']}                 
            ]
        }
    }
 }
@AccessControl.authorizationCheck:#CHECK
@AbapCatalog.sqlViewName: 'CSDBILDOCITMBCDX'
@AbapCatalog.preserveKey: true


define view C_BillingDocumentItemBasicDEX

as select from I_BillingDocExtdItemBasic        as BillingDocumentItemBasic

//Extension

association [0..1] to E_BillingDocumentItem     as _ExtensionItem         on  $projection.BillingDocument     = _ExtensionItem.BillingDocument
                                                                          and $projection.BillingDocumentItem = _ExtensionItem.BillingDocumentItem

association [0..1] to E_BillingDocument         as _ExtensionHeader       on  $projection.BillingDocument     = _ExtensionHeader.BillingDocument
                                                                          
{
  //key

   @ObjectModel.foreignKey.association: '_BillingDocumentBasic'
key BillingDocument,
key BillingDocumentItem,
  
  //Category

    SalesDocumentItemCategory,
    SalesDocumentItemType,
    ReturnItemProcessingType,   ---   <-- SHKZG !
    
    BillingDocumentType,
    BillingDocumentCategory,
    SDDocumentCategory,
  
  //Admin

    --CreatedByUser, -- from Cube
    @Semantics.systemDate.createdAt: true
    CreationDate,
    @Semantics.systemTime.createdAt 
    CreationTime, -- from Cube
    --ReferenceLogicalSystem,
    
    _BillingDocumentBasic.LastChangeDate,
    BillingDocumentDate,
    
   BillingDocumentIsTemporary,
  //Organization

    OrganizationDivision,
    Division,
    SalesOffice,  
    
    SalesOrganization,
    DistributionChannel,
    
  //Product

    Material,
    @Analytics.internalName: #LOCAL
    Product,
    OriginallyRequestedMaterial,
    InternationalArticleNumber,
    PricingReferenceMaterial, -- Cube
    Batch,
    --ProductHierarchyNode, -- Outdated and not used in Cloud
    MaterialGroup,
    @Analytics.internalName: #LOCAL
    ProductGroup,
    AdditionalMaterialGroup1,
    AdditionalMaterialGroup2,
    AdditionalMaterialGroup3,
    AdditionalMaterialGroup4,
    AdditionalMaterialGroup5,
    --ProductConfiguration,      
    MaterialCommissionGroup,
    Plant,
    StorageLocation,
    --ReplacementPartType,
    --MaterialGroupHierarchy1,
    --MaterialGroupHierarchy2,
    --PlantRegion,
    --PlantCounty,
    --PlantCity,
    --BOMExplosion,
    --MaterialDeterminationType,
    
  //Billing

    --BillingDocumentItemText,
    _BillingDocumentBasic.BillingDocumentIsCancelled, -- Cube
    _BillingDocumentBasic.CancelledBillingDocument, -- Cube
    BillingDocumentItemText, -- Cube
    ServicesRenderedDate, 
    @Semantics.quantity.unitOfMeasure: 'BillingQuantityUnit' 
    BillingQuantity,
    @Semantics.unitOfMeasure: true
    BillingQuantityUnit,
    @Semantics.quantity.unitOfMeasure: 'BaseUnit'
    BillingQuantityInBaseUnit,
    @Semantics.unitOfMeasure: true
    BaseUnit,
    @Semantics.quantity.unitOfMeasure: 'BaseUnit'
    MRPRequiredQuantityInBaseUnit, -- Cube
    BillingToBaseQuantityDnmntr, -- Cube
    BillingToBaseQuantityNmrtr, -- Cube
    @Semantics.quantity.unitOfMeasure: 'ItemWeightUnit'
    ItemGrossWeight,
    @Semantics.quantity.unitOfMeasure: 'ItemWeightUnit'
    ItemNetWeight,
    @Semantics.unitOfMeasure: true
    ItemWeightUnit,
    @Semantics.quantity.unitOfMeasure: 'ItemVolumeUnit'
    ItemVolume,
    @Semantics.unitOfMeasure: true
    ItemVolumeUnit,
    BillToPartyCountry,
    BillToPartyRegion,
    BillingPlanRule,
    BillingPlan,
    BillingPlanItem, -- Cube
    
  //Pricing

    CustomerPriceGroup, -- Cube
    _BillingDocumentBasic.PriceListType, --cube
    _BillingDocumentBasic.TaxDepartureCountry, -- Cube
    _BillingDocumentBasic.VATRegistration, -- Cube
    _BillingDocumentBasic.VATRegistrationCountry, -- Cube
    _BillingDocumentBasic.VATRegistrationOrigin, -- Cube
    _BillingDocumentBasic.CustomerTaxClassification1, -- Cube
    _BillingDocumentBasic.CustomerTaxClassification2, -- Cube
    _BillingDocumentBasic.CustomerTaxClassification3, -- Cube
    _BillingDocumentBasic.CustomerTaxClassification4, -- Cube
    _BillingDocumentBasic.CustomerTaxClassification5, -- Cube
    _BillingDocumentBasic.CustomerTaxClassification6, -- Cube    
    _BillingDocumentBasic.CustomerTaxClassification7, -- Cube
    _BillingDocumentBasic.CustomerTaxClassification8, -- Cube
    _BillingDocumentBasic.CustomerTaxClassification9, -- Cube
    _BillingDocumentBasic.SDPricingProcedure,
    @Semantics.amount.currencyCode: 'TransactionCurrency' //Natascha

    NetAmount,
    @Semantics.currencyCode: true
    TransactionCurrency,
    @Semantics.amount.currencyCode: 'TransactionCurrency' 
    GrossAmount,
    PricingDate,
    PriceDetnExchangeRate,
    @Semantics.quantity.unitOfMeasure: 'BaseUnit'
    PricingScaleQuantityInBaseUnit,
    @Semantics.amount.currencyCode: 'TransactionCurrency'
    TaxAmount,
    @Semantics.amount.currencyCode: 'TransactionCurrency'
    CostAmount,
    @Semantics.amount.currencyCode: 'TransactionCurrency'
    Subtotal1Amount,
    @Semantics.amount.currencyCode: 'TransactionCurrency'
    Subtotal2Amount,
    @Semantics.amount.currencyCode: 'TransactionCurrency'
    Subtotal3Amount,
    @Semantics.amount.currencyCode: 'TransactionCurrency'
    Subtotal4Amount,
    @Semantics.amount.currencyCode: 'TransactionCurrency'
    Subtotal5Amount,
    @Semantics.amount.currencyCode: 'TransactionCurrency'
    Subtotal6Amount,
    
    StatisticalValueControl,
    --CashDiscountIsDeductible,
    --CustomerConditionGroup1,
    --CustomerConditionGroup2,
    --CustomerConditionGroup3,
    --CustomerConditionGroup4,
    --CustomerConditionGroup5,
    --ManualPriceChangeType,
    --MaterialPricingGroup,
    StatisticsExchangeRate,
    --MainItemPricingRefMaterial,
    --MainItemMaterialPricingGroup,
    @Semantics.currencyCode: true
    _BillingDocumentBasic.StatisticsCurrency,
    
    @Semantics.currencyCode: true
    _SalesOrganization.SalesOrganizationCurrency,
    
  //Tax

    --DepartureCountry,
    --TaxJurisdiction,
    --ProductTaxClassification1,
    --ProductTaxClassification2,
    --ProductTaxClassification3,
    --ProductTaxClassification4,
    --ProductTaxClassification5,
    --ProductTaxClassification6,
    --ProductTaxClassification7,
    --ProductTaxClassification8,
    --ProductTaxClassification9,
    --ZeroVATRsn,
    --TaxCode,
    --TaxRateValidityStartDate,
    
  //Payment

    @Semantics.amount.currencyCode: 'TransactionCurrency'
    EligibleAmountForCashDiscount, -- Cube
    _BillingDocumentBasic.ContractAccount, -- Cube
    _BillingDocumentBasic.CustomerPaymentTerms,-- Cube
    _BillingDocumentBasic.PaymentMethod, -- Cube
    _BillingDocumentBasic.PaymentReference, -- Cube
    _BillingDocumentBasic.FixedValueDate, -- Cube
    _BillingDocumentBasic.AdditionalValueDays, -- Cube
    PayerParty,

  //Accounting

    CompanyCode,
    _BillingDocumentBasic.FiscalYear, -- Cube
    _BillingDocumentBasic.FiscalPeriod, --Cube
    _BillingDocumentBasic.CustomerAccountAssignmentGroup, --Cube
    BusinessArea, --Cube
    ProfitCenter, --Cube
    OrderID, --Cube
    --WBSElement, --Cube
    ControllingArea,
    
    -- ProfitabilitySegment, --Cube -- BK 14.02.2021. Deprecation
    @API.element.releaseState: #DECOMMISSIONED 
    @API.element.successor:    'ProfitabilitySegment_2'
    cast( case when ProfitabilitySegment > '9999999999' then '9999999999' else lpad( ProfitabilitySegment, 10, '0' ) end as rkeobjnr_numc )  as ProfitabilitySegment,
    @Analytics.internalName: #LOCAL
    ProfitabilitySegment_2, 
    
    CostCenter, --Cube
    OriginSDDocument, --Cube
    OriginSDDocumentItem, --Cube
    PriceDetnExchangeRateDate,

   -- MatlAccountAssignmentGroup,
   
    _BillingDocumentBasic.ExchangeRateType,
    _CompanyCode.FiscalYearVariant,
    _CompanyCode.Currency,
    _BillingDocumentBasic.AccountingExchangeRate,
    _BillingDocumentBasic.AccountingExchangeRateIsSet,
   
  //Reference

    ReferenceSDDocument,
    ReferenceSDDocumentItem,
    ReferenceSDDocumentCategory,
    SalesDocument,
    SalesDocumentItem,
    SalesSDDocumentCategory,
    HigherLevelItem, -- cube
    BillingDocumentItemInPartSgmt, --cube
    --ExternalReferenceDocument,
    --ExternalReferenceDocumentItem,
    
  //Sales

    SalesGroup,
    AdditionalCustomerGroup1,
    AdditionalCustomerGroup2,
    AdditionalCustomerGroup3,
    AdditionalCustomerGroup4,
    AdditionalCustomerGroup5,
    SDDocumentReason, -- Cube
--    RetailPromotion, out of scope
    
    ItemIsRelevantForCredit, -- Cube
    @Semantics.amount.currencyCode: 'TransactionCurrency'
    CreditRelatedPrice, -- Cube
--    SalesDeal, -- out of scope
    --SalesPromotion,
    --SalesOrderSalesDistrict,
    --SalesOrderCustomerGroup,
    --SalesOrderCustomerPriceGroup,
    --SalesOrderPriceListType,
    --SalesOrderSalesOrganization,
    --SalesOrderDistributionChannel,
    --SalesDocIsCreatedFromReference,
    
    SalesDistrict,
    CustomerGroup,
    SoldToParty,
    Country,
    
     ShipToParty,
     BillToParty, //added as soon as the I_View contains the field


     --cast(COALESCE(ItemStandardPartner.SalesEmployee, StandardPartner.SalesEmployee) as sales_empl)     as  SalesEmployee,
     --cast(COALESCE(ItemStandardPartner.ResponsibleEmployee, StandardPartner.ResponsibleEmployee) as resp_empl) as ResponsibleEmployee, -- Cube
     //Sales Employee ID & Responsible Employee ID

//     cast(COALESCE(ItemStandardPartner.SalesEmployeeWorkAgreement, StandardPartner.SalesEmployeeWorkAgreement) as sales_empl) as SalesEmployeeWorkAgreement, --Cube

//     cast(COALESCE(ItemStandardPartner.ResponsibleEmployeeWorkAgrmt, StandardPartner.ResponsibleEmployeeWorkAgrmt) as resp_empl) as ResponsibleEmployeeWorkAgrmt, --Cube

     
    
  //Shipping

    ShippingPoint,
  _BillingDocumentBasic.IncotermsVersion,
  _BillingDocumentBasic.IncotermsClassification,
  _BillingDocumentBasic.IncotermsTransferLocation,
  _BillingDocumentBasic.IncotermsLocation1,
  _BillingDocumentBasic.IncotermsLocation2,
  
  _BillingDocumentBasic.ShippingCondition,
  
  
  // Status

     BillingDocumentItemBasic.OverallSDProcessStatus,
     BillingDocumentItemBasic.OverallBillingStatus,
     
     _BillingDocumentBasic.AccountingPostingStatus,
     _BillingDocumentBasic.AccountingTransferStatus,
     _BillingDocumentBasic.BillingIssueType,
     _BillingDocumentBasic.InvoiceListStatus,
     _BillingDocumentBasic.OvrlItmGeneralIncompletionSts,
     _BillingDocumentBasic.OverallPricingIncompletionSts,
     
     
    
  //CRM

    --ServiceDocumentType,
    --ServiceDocument,
    --ServiceDocumentItem,      

  //Other fields

    --HigherLevelItemUsage,
    
    //associations

    _BillingDocumentBasic
    
}
where
     SDDocumentCategory = 'M' // Invoice

  or SDDocumentCategory = 'N' // Invoice Canceled

  or SDDocumentCategory = 'O' // Credit Memo

  or SDDocumentCategory = 'P' // Debit Memo

  or SDDocumentCategory = 'S' // Credit Memo Canceled

  or SDDocumentCategory = 'U' // Pro Forma Invoice

  or SDDocumentCategory = '5' // Intercompany Invoice

  or SDDocumentCategory = '6' // Intercompany Credit Memo

  or SDDocumentCategory = 'PBD' // Preliminary Billing Document

/*+[internal] {
"BASEINFO":
{
"FROM":
[
"I_BILLINGDOCEXTDITEMBASIC",
"I_BILLINGDOCUMENTBASIC",
"I_COMPANYCODE",
"I_SALESORGANIZATION"
],
"ASSOCIATED":
[
"E_BILLINGDOCUMENT",
"E_BILLINGDOCUMENTITEM",
"I_BILLINGDOCUMENTBASIC"
],
"BASE":
[
"I_BILLINGDOCEXTDITEMBASIC"
],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/