FAA_SDM_DOCIT_CP21

@AbapCatalog.sqlViewName: 'FAAVDOCITCP21'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Compare from PLAN_VALUE to ACDOCA'

@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #XXL

define view FAA_SDM_DOCIT_CP21
  as select from    faagtt_plan_val    as plan_values
    inner join      FAA_CFG_DEPR_AREA  as cfg_depr_area on  plan_values.bukrs          = cfg_depr_area.comp_code
                                                        and plan_values.afabe          = cfg_depr_area.depr_area
                                                        and cfg_depr_area.POSTING_TYPE = '0'
  //and plan_values.sdm_status     = '19'

    inner join      fins_tech_ledger       as ledger        on  ledger.rldnr       =  cfg_depr_area.ledger
                                                        and ledger.tech_ledger <> ''
    left outer join acdoca             as acdoca        on  plan_values.bukrs              =  acdoca.rbukrs
                                                        and plan_values.anln1              =  acdoca.anln1
                                                        and plan_values.anln2              =  acdoca.anln2
                                                        and plan_values.gjahr              =  acdoca.gjahr
                                                        and plan_values.afabe              =  acdoca.afabe
                                                        and plan_values.poper              =  acdoca.depr_period
//                                                        and (

//                                                           (

//                                                             plan_values.poper_shifted     =  '000'

//                                                             and plan_values.poper         =  acdoca.poper

//                                                             and plan_values.poper         =  acdoca.depr_period

//                                                           )

//                                                           or(

//                                                             plan_values.poper_shifted     <> '000'

//                                                             and plan_values.poper_shifted =  acdoca.poper

//                                                             and plan_values.poper         =  acdoca.depr_period

//                                                           )

//                                                         )

                                                        and plan_values.slalittype         =  acdoca.slalittype
                                                        and ledger.tech_ledger             =  acdoca.rldnr
                                                        and acdoca.awtyp                   =  'AMDP'
                                                        and acdoca.mig_source              =  'A'
    left outer join FAA_SDM_DOCIT_CP02 as split_number  on  split_number.bukrs        = plan_values.bukrs
                                                        and split_number.anln1        = plan_values.anln1
                                                        and split_number.anln2        = plan_values.anln2
                                                        and split_number.gjahr        = plan_values.gjahr
                                                        and split_number.afabe        = plan_values.afabe
                                                        and split_number.poper        = plan_values.poper
                                                        and split_number.slalittype   = plan_values.slalittype
                                                        and split_number.source_table = 'PLAN_VALUES'
{
  key plan_values.bukrs,
  key plan_values.anln1,
  key plan_values.anln2,
  key plan_values.gjahr,
      //key cast( '     ' as awtyp preserving type )                            as awtyp,

      //key cast( '          ' as awref preserving type )                       as awref,

      //key cast( '          ' as aworg preserving type )                       as aworg,

      //key cast( '          ' as awsys preserving type )                       as awsys,

      //key cast( '000000' as fins_subta preserving type )                      as subta,

  key plan_values.afabe,
  key plan_values.poper,
  key plan_values.slalittype,
      //key cast( ' ' as shkzg preserving type )                                as drcrk,

  key cast( acdoca.rldnr as fins_technical_ledger preserving type )       as rldnr,
  key acdoca.belnr,
  key acdoca.docln,
      
      plan_values.poper_shifted                                           as poper_shifted,
      cast( case
              when split_number.counter is null then 0
              else split_number.counter
            end as int4 preserving type )                                 as split_number,
      //cast( '000000' as fins_awitem preserving type )                     as s_awitem,

      //cast( '000000' as fins_awitem preserving type )                     as t_awitem,

      cast( ledger.tech_ledger as fins_technical_ledger preserving type ) as s_tech_ledger,
      cast( acdoca.rldnr as fins_technical_ledger preserving type )       as t_tech_ledger,
      case
        when acdoca.rbukrs is null then 'X'
        when ledger.tech_ledger <> acdoca.rldnr then 'X'
        else ''
      end                                                                 as tech_ledger_d,
      //cast( '    ' as vorgn preserving type )                             as s_vorgn,

      //cast( '    ' as vorgn preserving type )                             as t_vorgn,

      //cast( '00000000' as budat )                                         as s_budat,

      //cast( '00000000' as budat )                                         as t_budat,

      //cast( '00000000' as bzdat )                                         as s_bzdat,

      //cast( '00000000' as bzdat )                                         as t_bzdat,


      case
        when plan_values.poper_shifted <> '000'
          then plan_values.poper_shifted
        else plan_values.poper
      end                                                                 as s_poper,
      //      plan_values.poper                                                   as s_poper,

      acdoca.poper                                                        as t_poper,
      case
        when acdoca.rbukrs is null then 'X'
        when ( ( plan_values.poper_shifted = '000' and
                 plan_values.poper         <> acdoca.poper ) 
               or
               ( plan_values.poper_shifted <> '000' and
                 plan_values.poper_shifted <> acdoca.poper
               )
             ) then 'X'
        else ''
      end                                                                 as poper_d,
      //cast( '   ' as bwasl preserving type )                              as s_bwasl,

      //cast( '   ' as bwasl preserving type )                              as t_bwasl,

      plan_values.movcat                                                  as s_movcat,
      acdoca.movcat                                                       as t_movcat,
      case
        when acdoca.rbukrs is null then 'X'
        when ( plan_values.movcat = '46' and acdoca.movcat <> '06' ) or
             ( plan_values.movcat = '65' and acdoca.movcat <> '06' ) or
             ( plan_values.movcat <> '46' and plan_values.movcat <> '65' and plan_values.movcat <> acdoca.movcat ) then 'X'
        else ''
      end                                                                 as movcat_d,
      @Semantics.amount.currencyCode: 's_rhcur'
      plan_values.hsl                                                     as s_hsl,
      @Semantics.amount.currencyCode: 't_rhcur'
      acdoca.hsl                                                          as t_hsl,
      case
        when acdoca.rbukrs is null then 'X'
        when plan_values.hsl <> acdoca.hsl then 'X'
        else ''
      end                                                                 as hsl_d,
      @Semantics.amount.currencyCode: 's_rkcur'
      plan_values.ksl                                                     as s_ksl,
      @Semantics.amount.currencyCode: 't_rkcur'
      acdoca.ksl                                                          as t_ksl,
      ''                                                                  as ksl_d,
      @Semantics.amount.currencyCode: 's_rocur'
      plan_values.osl                                                     as s_osl,
      @Semantics.amount.currencyCode: 't_rocur'
      acdoca.osl                                                          as t_osl,
      ''                                                                  as osl_d,
      @Semantics.amount.currencyCode: 's_rvcur'
      plan_values.vsl                                                     as s_vsl,
      @Semantics.amount.currencyCode: 't_rvcur'
      acdoca.vsl                                                          as t_vsl,
      ''                                                                  as vsl_d,
      @Semantics.currencyCode: true
      plan_values.rhcur                                                   as s_rhcur,
      @Semantics.currencyCode: true
      acdoca.rhcur                                                        as t_rhcur,
      case
        when acdoca.rbukrs is null then 'X'
        when plan_values.rhcur <> acdoca.rhcur then 'X'
        else ''
      end                                                                 as rhcur_d,
      @Semantics.currencyCode: true
      plan_values.rkcur                                                   as s_rkcur,
      @Semantics.currencyCode: true
      acdoca.rkcur                                                        as t_rkcur,
      ''                                                                  as rkcur_d,
      @Semantics.currencyCode: true
      plan_values.rocur                                                   as s_rocur,
      @Semantics.currencyCode: true
      acdoca.rocur                                                        as t_rocur,
      ''                                                                  as rocur_d,
      @Semantics.currencyCode: true
      plan_values.rvcur                                                   as s_rvcur,
      @Semantics.currencyCode: true
      acdoca.rvcur                                                        as t_rvcur,
      ''                                                                  as rvcur_d,
      //cast( '' as fins_xreversing preserving type )                       as s_xreversing,

      //cast( '' as fins_xreversing preserving type )                       as t_xreversing,

      //cast( '' as fins_xreversed preserving type )                        as s_xreversed,

      //cast( '' as fins_xreversed preserving type )                        as t_xreversed,

      //cast( '' as fins_xsettling preserving type )                        as s_xsettling,

      //cast( '' as fins_xsettling preserving type )                        as t_xsettling,

      //cast( '' as fins_xsettled preserving type )                         as s_xsettled,

      //cast( '' as fins_xsettled preserving type )                         as t_xsettled,

      //cast( '     ' as fins_prec_awtyp preserving type )                  as s_prec_awtyp,

      //cast( '     ' as fins_prec_awtyp preserving type )                  as t_prec_awtyp,

      //cast( '          ' as fins_prec_aworg preserving type )             as s_prec_aworg,

      //cast( '          ' as fins_prec_aworg preserving type )             as t_prec_aworg,

      //cast( '          ' as fins_prec_awref preserving type )             as s_prec_awref,

      //cast( '          ' as fins_prec_awref preserving type )             as t_prec_awref,

      //cast( '000000' as fins_prec_subta preserving type )                 as s_prec_subta,

      //cast( '000000' as fins_prec_subta preserving type )                 as t_prec_subta,

      //cast( '     ' as awtyp_rev preserving type )                        as s_awtyp_rev,

      //cast( '     ' as awtyp_rev preserving type )                        as t_awtyp_rev,

      //cast( '          ' as awref_rev preserving type )                   as s_awref_rev,

      //cast( '          ' as awref_rev preserving type )                   as t_awref_rev,

      //cast( '          ' as aworg_rev preserving type )                   as s_aworg_rev,

      //cast( '          ' as aworg_rev preserving type )                   as t_aworg_rev,

      //cast( '000000' as fins_subta_rev preserving type )                  as s_subta_rev,

      //cast( '000000' as fins_subta_rev preserving type )                  as t_subta_rev,

      plan_values.anlkl                                                   as s_anlkl,
      acdoca.anlkl                                                        as t_anlkl,
      case
        when acdoca.rbukrs is null then 'X'
        when plan_values.anlkl = '' and acdoca.anlkl = '' then 'X'
        when plan_values.anlkl <> '' and plan_values.anlkl <> acdoca.anlkl then 'X'
        else ''
      end                                                                 as anlkl_d,
      plan_values.ktogr                                                   as s_ktogr,
      acdoca.ktogr                                                        as t_ktogr,
      case
        when acdoca.rbukrs is null then 'X'
        when plan_values.ktogr = '' and acdoca.ktogr = '' then 'X'
        when plan_values.ktogr <> '' and plan_values.ktogr <> acdoca.ktogr then 'X'
        else ''
      end                                                                 as ktogr_d,
      //cast( '            ' as panl1 preserving type )                     as s_panl1,

      //cast( '            ' as panl1 preserving type )                     as t_panl1,

      //cast( '    ' as panl2 preserving type )                             as s_panl2,

      //cast( '    ' as panl2 preserving type )                             as t_panl2,

      plan_values.mig_source                                              as s_mig_source,
      acdoca.mig_source                                                   as t_mig_source,
      ''                                                                  as mig_source_d,
      plan_values.recid                                                   as s_recid,
      acdoca.recid                                                        as t_recid,
      case
        when acdoca.rbukrs is null then 'X'
        when plan_values.recid <> acdoca.recid then 'X'
        else ''
      end                                                                 as recid_d,
      //cast( '00000000' as bldat )                                         as s_bldat,

      //cast( '00000000' as bldat )                                         as t_bldat,

      //''                                                                  as s_sgtxt,

      //''                                                                  as t_sgtxt,

      //''                                                                  as s_zuonr,

      //''                                                                  as t_zuonr,

      //cast( '  ' as a_obart preserving type )                             as s_obart,

      //cast( '  ' as a_obart preserving type )                             as t_obart,

      //''                                                                  as s_objid,

      //''                                                                  as t_objid,

      //cast( '00000000' as ubzdt )                                         as s_ubzdt_pn,

      //cast( '00000000' as ubzdt )                                         as t_ubzdt_pn,

      //cast( '' as xvabg preserving type )                                 as s_xvabg_pn,

      //cast( '' as xvabg preserving type )                                 as t_xvabg_pn,

      //cast( 0 as prozs )                                                  as s_prozs_pn,

      //cast( 0 as prozs )                                                  as t_prozs_pn,

      //cast( '' as xmanpropval preserving type )                           as s_xmanpropval_pn,

      //cast( '' as xmanpropval preserving type )                           as t_xmanpropval_pn,

      @EndUserText.label: 'Different'
      @EndUserText.quickInfo: 'Has some differences'
      case
        when acdoca.rbukrs is null then 'X'
        when ledger.tech_ledger <> acdoca.rldnr then 'X'
//        when plan_values.poper <> acdoca.poper then 'X'


        when ( ( plan_values.poper_shifted = '000' and
                 plan_values.poper         <> acdoca.poper ) 
               or
               ( plan_values.poper_shifted <> '000' and
                 plan_values.poper_shifted <> acdoca.poper
               )
             )                                                            then 'X'
        when ( plan_values.movcat = '46' and acdoca.movcat <> '06' ) or
             ( plan_values.movcat = '65' and acdoca.movcat <> '06' ) or
             ( plan_values.movcat <> '46' and plan_values.movcat <> '65' and plan_values.movcat <> acdoca.movcat ) then 'X'
        when plan_values.hsl <> acdoca.hsl then 'X'
      //when plan_values.ksl <> acdoca.ksl then 'X'

      //when plan_values.osl <> acdoca.osl then 'X'

      //when plan_values.vsl <> acdoca.vsl then 'X'

        when plan_values.rhcur <> acdoca.rhcur then 'X'
      //when plan_values.rkcur <> acdoca.rkcur then 'X'

      //when plan_values.rocur <> acdoca.rocur then 'X'

      //when plan_values.rvcur <> acdoca.rvcur then 'X'

      //when plan_values.anlkl <> acdoca.anlkl then 'X'

        when ( plan_values.anlkl = '' and acdoca.anlkl = '' ) or
             ( plan_values.anlkl <> '' and plan_values.anlkl <> acdoca.anlkl ) then 'X'
      //when plan_values.ktogr <> acdoca.ktogr then 'X'

        when ( plan_values.ktogr = '' and acdoca.ktogr = '' ) or
             ( plan_values.ktogr <> '' and plan_values.ktogr <> acdoca.ktogr ) then 'X'
      //when ( plan_values.sdm_status = '19' and acdoca.mig_source <> 'A') or

      //     ( plan_values.sdm_status <> '19' and plan_values.mig_source <> acdoca.mig_source ) then 'X'

        when plan_values.recid <> acdoca.recid then 'X'
        else ''
      end                                                                 as different,
      @EndUserText.label: 'Redundant'
      @EndUserText.quickInfo: 'Redundant in both tables'
      case
        when acdoca.rbukrs is null then ''
        else 'X'
      end                                                                 as redundant,
      @EndUserText.label: 'Migrated'
      @EndUserText.quickInfo: 'Has been migrated'
      cast( '+' as char01 preserving type )                               as direction,
      plan_values.anlkl,
      plan_values.movcat
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FAA_CFG_DEPR_AREA",
"FAA_SDM_DOCIT_CP02",
"ACDOCA",
"FAAGTT_PLAN_VAL",
"FINS_TECH_LEDGER"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/