JV_ACDOCA_JVSO1

@AbapCatalog.sqlViewName: 'CACDOCA_JVSO1'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AbapCatalog.dataMaintenance: #DISPLAY_ONLY
@AccessControl.personalData.blocking: #NOT_REQUIRED
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'ACDOCA-like view on JVSO1'
@ObjectModel.supportedCapabilities: [ #SQL_DATA_SOURCE ]
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #X
@ClientHandling.algorithm: #SESSION_VARIABLE

define view JV_ACDOCA_JVSO1

  as select from           JV_JVSO1_COMB as jvso1

  //Join the ledger

    left outer to one join I_JVALedger on I_JVALedger.CompanyCode = jvso1.rbukrs

  association [0..1] to fco_mig_nplnr on  fco_mig_nplnr.nplnr = jvso1.nplnr
                                      and fco_mig_nplnr.aufpl = jvso1.aufpl
                                      and fco_mig_nplnr.aplzl = jvso1.aplzl

{
  key jvso1.rclnt,
  key I_JVALedger.Ledger                                   as rldnr,
  key jvso1.rbukrs,
  key jvso1.ryear                                          as gjahr,
  key case when jvso1.reffidoc is not initial then
             jvso1.reffidoc
           when jvso1.refdocnr is not initial then
             jvso1.refdocnr
           else
             jvso1.docnr
      end                                                  as belnr,
  key cast( concat( '000', jvso1.docln )
                           as docln6 preserving type )     as docln,

      jvso1.reffidln                                       as buzei,

      case jvso1.refdocct
        when 'K' then
          jvso1.refdocnr
        else
          cast( '          ' as co_belnr preserving type )
      end                                                  as co_belnr,
      case jvso1.refdocct
        when 'K' then
          cast( substring( jvso1.refdocln, 3, 3) as co_buzei )
        else
          cast( '000' as co_buzei preserving type )
      end                                                  as co_buzei,

      jvso1.refdocnr                                       as awref,
      jvso1.aworg,
      cast( concat( '0', jvso1.refdocln ) as fins_awitem ) as awitem,
      jvso1.awtyp,
      jvso1.logsys                                         as awsys,

      cast( '          ' as belnr_d preserving type )      as prec_awref,
      cast( '          ' as aworg preserving type )        as prec_aworg,
      cast( '000000'     as fins_awitem preserving type )  as prec_awitem,
      cast( '     '      as awtyp preserving type )        as prec_awtyp,
      cast( '          ' as logsys preserving type )       as prec_awsys,

      cast( '          ' as belnr_d preserving type )      as src_awref,
      cast( '          ' as aworg preserving type )        as src_aworg,
      cast( '000000'     as fins_awitem preserving type )  as src_awitem,
      cast( '     '      as awtyp preserving type )        as src_awtyp,
      cast( '          ' as logsys preserving type )       as src_awsys,

      jvso1.ryear,
      jvso1.poper,

      jvso1.budat,
      jvso1.bldat,
      jvso1.valut,

      jvso1.sgtxt,

      jvso1.refactiv                                       as vorgn,
      jvso1.docty                                          as blart,

      jvso1.racct,
      jvso1.rbusa,
      jvso1.rkokrs                                         as kokrs,

      jvso1.mwskz,
      jvso1.ktosl,

      jvso1.cbcustomer                                     as kunnr,
      jvso1.cbvendor                                       as lifnr,

      jvso1.rprctr                                         as prctr,

      case when jvso1.rcntr is not initial
        then cast( 'KS' as accasty preserving type )
        else case when jvso1.rprojk is not initial
          then cast( 'PR' as accasty preserving type )
          else case when jvso1.rordnr is not initial
            then case when jvso1.nplnr is initial and jvso1.vornr is not initial
              then cast( 'OV' as accasty preserving type )
              else cast( 'OR' as accasty preserving type ) end
            else case when jvso1.nplnr is not initial
              then case when jvso1.aufpl is not initial
                then cast( 'NV' as accasty preserving type )
                else cast( 'NP' as accasty preserving type ) end
              else cast( '  ' as accasty preserving type )
            end
          end
        end
      end                                                  as accasty,

      jvso1.rcntr,
      jvso1.rordnr                                         as aufnr,
      jvso1.rprojk                                         as ps_psp_pnr,

      jvso1.nplnr,
      case when fco_mig_nplnr.vornr is null
        then cast( '    '              as npvrg preserving type )
        else cast( fco_mig_nplnr.vornr as npvrg preserving type )
      end                                                  as nplnr_vorgn,
      jvso1.aufpl,
      jvso1.aplzl,

      jvso1.vornr,

      jvso1.ranln1                                         as anln1,
      jvso1.ranln2                                         as anln2,
      jvso1.ranbwa                                         as anbwa,

      jvso1.matnr,
      jvso1.lstar,
      jvso1.ebeln,
      jvso1.ebelp,

      jvso1.rjvnam                                         as vname,
      jvso1.regrou                                         as egrup,
      jvso1.rrecin                                         as recid,
      jvso1.bilid                                          as btype,
      jvso1.rpartn                                         as vptnr,
      cast( '   ' as jv_etype preserving type )            as etype,
      jvso1.prodper,
      jvso1.billm,
      jvso1.pom,
      case jvso1.activ
        when 'RGL0' then cast( 'CC' as jv_activity preserving type )
        when 'JVCB' then cast( 'CI' as jv_activity preserving type )
        when 'JVEC' then cast( 'EC' as jv_activity preserving type )
        when 'JVE1' then cast( 'E1' as jv_activity preserving type )
        when 'JVE2' then cast( 'E2' as jv_activity preserving type )
        when 'JVS1' then cast( 'SU' as jv_activity preserving type )
        when 'JVS2' then cast( 'US' as jv_activity preserving type )
        else cast( '  ' as jv_activity preserving type )
      end                                                  as jvactivity,

      cast( 999 as timestamp )                             as cbrunid,

      jvso1.drcrk,

      jvso1.rwcur,
      jvso1.rhcur,
      jvso1.rkcur,
      jvso1.rocur,
      jvso1.rvcur,
      jvso1.rbcur,
      jvso1.rccur,
      jvso1.rdcur,
      jvso1.recur,
      jvso1.rfcur,
      jvso1.rgcur,

      @Semantics.amount.currencyCode: 'rwcur'
      jvso1.wsl,
      @Semantics.amount.currencyCode: 'rhcur'
      jvso1.hsl,
      @Semantics.amount.currencyCode: 'rkcur'
      jvso1.ksl,
      @Semantics.amount.currencyCode: 'rocur'
      jvso1.osl,
      @Semantics.amount.currencyCode: 'rvcur'
      jvso1.vsl,
      @Semantics.amount.currencyCode: 'rbcur'
      jvso1.bsl,
      @Semantics.amount.currencyCode: 'rccur'
      jvso1.csl,
      @Semantics.amount.currencyCode: 'rdcur'
      jvso1.dsl,
      @Semantics.amount.currencyCode: 'recur'
      jvso1.esl,
      @Semantics.amount.currencyCode: 'rfcur'
      jvso1.fsl,
      @Semantics.amount.currencyCode: 'rgcur'
      jvso1.gsl,

      jvso1.msl,
      jvso1.runit,

      jvso1.usnam,

      cast( dats_tims_to_tstmp(
        jvso1.cpudt,
        jvso1.cputm,
        abap_system_timezone( $session.client, 'NULL' ),
        $session.client,
        'NULL' ) as timestamp preserving type )            as timestamp,

      jvso1.cpudt,
      jvso1.cputm,

      //Save JVA docnr

      jvso1.docnr                                          as jv_docnr

}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FCO_MIG_NPLNR",
"I_JVALEDGER",
"JV_JVSO1_COMB"
],
"ASSOCIATED":
[
"FCO_MIG_NPLNR"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/