C_HU_ECSlsLstMatlDocCsgnmtC

@VDM.viewType: #CONSUMPTION
@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #MANDATORY
@AccessControl.personalData.blocking: #BLOCKED_DATA_INCLUDED
@EndUserText.label: 'Hungarian ECSL Consignment - Cube'
@Metadata.ignorePropagatedAnnotations: true
@Metadata.allowExtensions: true
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #MIXED
@Analytics.internalName:#LOCAL 
@Analytics.dataCategory: #CUBE

define view entity C_HU_ECSlsLstMatlDocCsgnmtC as select from I_MaterialDocumentRecord as MatDoc

  //Restricting only to Purchase side:  

  inner join I_Supplier as Supplier 
    on MatDoc.Supplier = Supplier.Supplier
    
  left outer to one join I_Country as Country 
    on Supplier.Country = Country.Country

  left outer to one join I_BusinessPartnerSupplier as BPSupplier on BPSupplier.Supplier = MatDoc.Supplier
  
  //equivalent to lfas for land HU:  

  left outer to one join I_Businesspartnertaxnumber as BPTaxNumberHU0 on BPTaxNumberHU0.BusinessPartner = BPSupplier.BusinessPartner
                                                                     and BPTaxNumberHU0.BPTaxType = 'HU0'

  //Reversed Document in the same period:

  left outer to one join I_MaterialDocumentRecord as ReversedDocument on ReversedDocument.MaterialDocumentYear = MatDoc.ReversedMaterialDocumentYear
                                                                     and ReversedDocument.MaterialDocument = MatDoc.ReversedMaterialDocument 
                                                                     and ReversedDocument.MaterialDocumentItem = MatDoc.ReversedMaterialDocumentItem
                                                                     and ReversedDocument.CompanyCode = MatDoc.CompanyCode 
                                                                     and ReversedDocument.FiscalYearPeriod = MatDoc.FiscalYearPeriod 
  
  //Reversal Document in the same period:                                                                   

  left outer to one join I_MaterialDocumentRecord as ReversalDocument on ReversalDocument.ReversedMaterialDocumentYear = MatDoc.MaterialDocumentYear
                                                                     and ReversalDocument.ReversedMaterialDocument = MatDoc.MaterialDocument 
                                                                     and ReversalDocument.ReversedMaterialDocumentItem = MatDoc.MaterialDocumentItem
                                                                     and ReversalDocument.CompanyCode = MatDoc.CompanyCode 
                                                                     and ReversalDocument.FiscalYearPeriod = MatDoc.FiscalYearPeriod 
                                                      
{
  key MatDoc.CompanyCode,
  key MatDoc.MaterialDocumentYear, 
  key MatDoc.MaterialDocument,
  key MatDoc.MaterialDocumentItem,
  
  cast(MatDoc.Supplier as fis_lifnr preserving type ) as Creditor,
  
  // VAT number of the business partner

  case
    // If supplier's VAT number is not found at all, a "virtual" number is created and all unassigned suppliers from the same country are groupped by it

    when MatDoc.Supplier <> '' and Supplier.VATRegistration = '' and 
         Country.IsEuropeanUnionMember = 'X' and Supplier.Country <> 'HU' then concat(Country.CountryISOCode, '0')    
    else Supplier.VATRegistration
  end as VATRegistration,
  
  cast(MatDoc.PostingDate as glo_reporting_date ) as ReportingDate,
  MatDoc.PostingDate,
  MatDoc.DocumentDate,

  cast (
    case
      //Consignment Fill Up

      when MatDoc.GoodsMovementType = '101' then 'CSF'
      //Consignment Pick Up

      when MatDoc.GoodsMovementType = '102' then 'CSP'     
    end as hu_ecsl_operation
  ) as ECSlsLstOperation,
  MatDoc._BusinessPartner.BusinessPartner,
  MatDoc._BusinessPartner.BusinessPartnerName,
  MatDoc.GoodsMovementType,
  MatDoc.Material,
  MatDoc.ReversedMaterialDocumentYear,
  MatDoc.ReversedMaterialDocument,
  MatDoc.ReversedMaterialDocumentItem 
}
where
  ( MatDoc.GoodsMovementType = '101' or MatDoc.GoodsMovementType = '102' ) and
  MatDoc.InventorySpecialStockType = 'K' and
  Country.IsEuropeanUnionMember = 'X' and
  Supplier.Country <> 'HU' and
  BPTaxNumberHU0.BusinessPartner is null and
  ReversedDocument.MaterialDocument is null and
  ReversalDocument.MaterialDocument is null