MLDOC_UCB_EXTRACT

@AbapCatalog.viewEnhancementCategory: [#NONE]
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'MLDOC with joined UCB all records'
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType:{
  serviceQuality: #X,
  sizeCategory: #S,
  dataClass: #MIXED
}
define view entity MLDOC_UCB_EXTRACT 
  as

// MLDOC_EXTRACT records with reference UCB records from MLDOCUCB, CURTP-independent

   select from mldoc_extract as doc inner join mldocucb as ucb 
     on doc.pdocref = ucb.docref 
    and doc.curtp   = '10'    //this is to select the UCB record only once

    and ucb.curtp   = ''
{
    key doc.kalnr                   as kalnr,
    key doc.jahrper                 as jahrper, 
    key doc.docref                  as docref, 
    key ucb.curtp                   as curtp,
    key ucb.ucb_method              as ucb_method,
    key ucb.ucb_component           as ucb_component,
    key ucb.svrel                   as svrel, 
        doc.rldnr                   as rldnr,  
        ucb.waers                   as waers,      
        ucb.quantity_unit           as quantity_unit,     
        @Semantics.quantity.unitOfMeasure: 'quantity_unit'
        cast(round( ucb.quant_tot_ps / ucb.scale_denominator * doc.quant, 3 ) -
             round((ucb.quant_tot_ps - ucb.quant_tot) / ucb.scale_denominator * doc.quant, 3 ) as fucb_quantity)
                                    as quant_tot, 
        @Semantics.quantity.unitOfMeasure: 'quantity_unit'  
        cast(round( ucb.quant_fix_ps / ucb.scale_denominator * doc.quant, 3 ) - 
             round((ucb.quant_fix_ps - ucb.quant_fix) / ucb.scale_denominator * doc.quant, 3 ) as fucb_quantity) 
                                    as quant_fix,
        @Semantics.amount.currencyCode: 'waers'                   
         cast('0' as ml4h_total)    as tot,
       @Semantics.amount.currencyCode: 'waers'
        cast('0' as ml4h_fix)       as fix,      
        ' '                         as persisted                    
}
    where doc.pdocref           <> '00000000000000000000'
      and ucb.scale_denominator <> 0
  
  
  union all   
 
// MLDOC_EXTRACT records with reference UCB records from MLDOCUCB, CURTP-dependent 

   select from mldoc_extract as doc inner join mldocucb as ucb 
     on doc.pdocref = ucb.docref 
    and doc.curtp   = ucb.curtp  
{
    key doc.kalnr                   as kalnr,
    key doc.jahrper                 as jahrper, 
    key doc.docref                  as docref, 
    key doc.curtp                   as curtp,
    key ucb.ucb_method              as ucb_method,
    key ucb.ucb_component           as ucb_component,
    key ucb.svrel                   as svrel, 
        doc.rldnr                   as rldnr,  
        doc.waers                   as waers,      
        ucb.quantity_unit           as quantity_unit,   
       cast('0' as  fucb_quantity)     as quant_tot,
       cast('0' as  fucb_quantity)     as quant_fix,    
       
        case ucb.scale_type 
          when 'A' then   
            cast(round( cast(ucb.tot_ps as fucb_scale_denominator) / ucb.scale_denominator * cast(doc.stval as fucb_scale_denominator), 2 ) -
                 round((cast(ucb.tot_ps as fucb_scale_denominator) - cast(ucb.tot as fucb_scale_denominator)) / ucb.scale_denominator * cast(doc.stval as fucb_scale_denominator), 2 ) as ml4h_total)  
          else
            cast('0' as ml4h_total)         
        end                         as tot,                                               
        case ucb.scale_type 
          when 'A' then   
            cast(round( cast(ucb.fix_ps as fucb_scale_denominator) / ucb.scale_denominator * cast(doc.stval as fucb_scale_denominator), 2 ) -
                 round((cast(ucb.fix_ps as fucb_scale_denominator) - cast(ucb.fix as fucb_scale_denominator)) / ucb.scale_denominator * cast(doc.stval as fucb_scale_denominator), 2 ) as ml4h_fix)  
          else
            cast('0' as ml4h_fix)                
        end                          as fix,   
        ' '                          as persisted              
}
    where doc.pdocref           <> '00000000000000000000'
      and ucb.scale_denominator <> 0
 
 
  union all   
 
// Persisted MLDOCUCB_EXTRACT records, CURTP-independent and CURTP-dependent   

   select from mldocucb_extract 
{
    key kalnr                   as kalnr,
    key jahrper                 as jahrper, 
    key docref                  as docref, 
    key curtp                   as curtp,
    key ucb_method              as ucb_method,
    key ucb_component           as ucb_component,
    key svrel                   as svrel, 
        rldnr                   as rldnr,  
        waers                   as waers,      
        quantity_unit           as quantity_unit,      
        quant_tot               as quant_tot, 
        quant_fix               as quant_fix,    
        tot                     as tot,                                               
        fix                     as fix,
        'X'                     as persisted                                                  
}