JV_ACDOCA

@AbapCatalog.sqlViewName: 'CJVACDOCA'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AbapCatalog.dataMaintenance: #DISPLAY_ONLY
@AccessControl.personalData.blocking: #NOT_REQUIRED
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'JVA view on ACDOCA'
@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

  as select from acdoca

    inner join   t8jz on  t8jz.mandt = acdoca.rclnt
                      and t8jz.bukrs = acdoca.rbukrs

  //Join the ledger

  association [0..1] to I_JVALedger   on  I_JVALedger.CompanyCode = acdoca.rbukrs

  association [0..1] to fco_mig_nplnr on  fco_mig_nplnr.nplnr = acdoca.nplnr
                                      and fco_mig_nplnr.vornr = acdoca.nplnr_vorgn

{
  key acdoca.rclnt,
  key acdoca.rldnr,
  key acdoca.rbukrs,
  key acdoca.gjahr,
  key acdoca.belnr,
  key acdoca.docln,
      acdoca.buzei,

      acdoca.co_belnr,
      acdoca.co_buzei,

      acdoca.vorgn,
      acdoca.blart,

      acdoca.ryear,
      acdoca.poper,

      acdoca.budat,
      acdoca.valut,
      acdoca.bldat,

      acdoca.awref,
      acdoca.aworg,
      acdoca.awitem,
      acdoca.awtyp,
      acdoca.awsys,

      acdoca.prec_awref,
      acdoca.prec_aworg,
      acdoca.prec_awitem,
      acdoca.prec_awtyp,
      acdoca.prec_awsys,

      acdoca.src_awref,
      acdoca.src_aworg,
      acdoca.src_awitem,
      acdoca.src_awtyp,
      acdoca.src_awsys,

      acdoca.racct,
      acdoca.rbusa,
      acdoca.kokrs,

      acdoca.sgtxt,

      acdoca.mwskz,
      acdoca.ktosl,

      acdoca.kunnr,
      acdoca.lifnr,

      acdoca.prctr,
      acdoca.segment,

      acdoca.accasty,

      case when acdoca.accasty <> 'PR' and
                acdoca.accasty <> 'NP' and
                acdoca.accasty <> 'NV' and
                acdoca.accasty <> 'OR' and
                acdoca.accasty <> 'OV' then
          acdoca.rcntr
        else
          cast( '          ' as kostl preserving type )
      end                                   as rcntr,
      case when acdoca.accasty <> 'KS' and
                acdoca.accasty <> 'PR' and
                acdoca.accasty <> 'NP' and
                acdoca.accasty <> 'NV' then
          acdoca.aufnr
        else
          cast( '            ' as aufnr preserving type )
      end                                   as aufnr,
      case when acdoca.accasty <> 'KS' and
                acdoca.accasty <> 'NP' and
                acdoca.accasty <> 'NV' and
                acdoca.accasty <> 'OR' and
                acdoca.accasty <> 'OV' then
          acdoca.ps_psp_pnr
        else
          cast ('00000000' as ps_psp_pnr preserving type )
      end                                   as ps_psp_pnr,

      case when acdoca.accasty <> 'KS' and
                acdoca.accasty <> 'PR' and
                acdoca.accasty <> 'OR' and
                acdoca.accasty <> 'OV' then
          acdoca.nplnr
        else
          cast ('            ' as nplnr preserving type )
      end                                   as nplnr,
      case when acdoca.accasty <> 'KS' and
                acdoca.accasty <> 'PR' and
                acdoca.accasty <> 'OR' and
                acdoca.accasty <> 'OV' then
          acdoca.nplnr_vorgn
        else
          cast ('            ' as nplnr preserving type )
      end                                   as nplnr_vorgn,
      case when acdoca.accasty <> 'KS' and
                acdoca.accasty <> 'PR' and
                acdoca.accasty <> 'OR' and
                acdoca.accasty <> 'OV' then
          case when fco_mig_nplnr.vornr is null
               then cast( '0000000000'        as co_aufpl preserving type )
               else cast( fco_mig_nplnr.aufpl as co_aufpl preserving type )
          end
        else
          cast ('0000000000' as co_aufpl preserving type )
      end                                   as aufpl,
      case when acdoca.accasty <> 'KS' and
                acdoca.accasty <> 'PR' and
                acdoca.accasty <> 'OR' and
                acdoca.accasty <> 'OV' then
          case when fco_mig_nplnr.vornr is null
               then cast( '00000000'          as co_aplzl preserving type )
               else cast( fco_mig_nplnr.aplzl as co_aplzl preserving type )
          end
        else
          cast ('00000000' as co_aplzl preserving type )
        end                                 as aplzl,

      case when acdoca.accasty <> 'KS' and
                acdoca.accasty <> 'PR' and
                acdoca.accasty <> 'OR' and
                acdoca.accasty <> 'OV' then
        case when acdoca.nplnr       is not initial and
                  acdoca.nplnr_vorgn is not initial then
            acdoca.nplnr_vorgn
          else
            acdoca.vornr
          end
        else
          acdoca.vornr
      end                                   as vornr,

      acdoca.anln1,
      acdoca.anln2,
      acdoca.anbwa,

      acdoca.mat_pspnr,
      acdoca.matnr,
      acdoca.lstar,
      acdoca.ebeln,
      acdoca.ebelp,

      acdoca.vname,
      acdoca.egrup,
      acdoca.recid,
      acdoca.btype,
      acdoca.vptnr,
      acdoca.etype,
      acdoca.prodper,
      acdoca.billm,
      acdoca.pom,
      acdoca.jvactivity,
      acdoca.cbrunid,

      acdoca.drcrk,

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

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

      acdoca.msl,
      acdoca.runit,

      acdoca.usnam,

      acdoca.timestamp,
      cast( tstmp_to_dats(
        acdoca.timestamp,
        abap_system_timezone( $session.client, 'NULL' ),
        $session.client,
        'NULL' ) as cpudt preserving type ) as cpudt,

      cast( tstmp_to_tims(
        acdoca.timestamp,
        abap_system_timezone( $session.client, 'NULL' ),
        $session.client,
        'NULL' ) as cputm preserving type ) as cputm

}

where
  acdoca.rldnr = I_JVALedger.Ledger
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FCO_MIG_NPLNR",
"I_JVALEDGER",
"ACDOCA",
"T8JZ"
],
"ASSOCIATED":
[
"FCO_MIG_NPLNR",
"I_JVALEDGER"
],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/