FAA_SDM_DOCIT_CP22

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

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

define view FAA_SDM_DOCIT_CP22
  as select from    acdoca             as acdoca
    inner join      fins_tech_ledger       as ledger_a      on  acdoca.rldnr         =  ledger_a.tech_ledger
                                                        and ledger_a.tech_ledger <> ''
                                                        and acdoca.awtyp         =  'AMDP'
                                                        and acdoca.mig_source    =  'A'
    left outer join(
                    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_p      on ledger_p.rldnr = cfg_depr_area.ledger
    )
                                                    on  acdoca.rbukrs     = plan_values.bukrs
                                                    and acdoca.anln1      = plan_values.anln1
                                                    and acdoca.anln2      = plan_values.anln2
                                                    and acdoca.gjahr      = plan_values.gjahr
                                                    and acdoca.afabe      = plan_values.afabe
                                                //    and acdoca.poper      = plan_values.poper

                                                    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 acdoca.slalittype = plan_values.slalittype
    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 acdoca.rbukrs                                                         as bukrs,
  key acdoca.anln1,
  key acdoca.anln2,
  key acdoca.gjahr,
      //key acdoca.awtyp,

      //key acdoca.awref,

      //key acdoca.aworg,

      //key acdoca.awsys,

      //key acdoca.subta,

  key acdoca.afabe,
  key acdoca.depr_period                                                    as poper,
  key acdoca.slalittype,
      //key acdoca.drcrk,

  key cast( acdoca.rldnr as fins_technical_ledger preserving type )         as rldnr,
  key acdoca.belnr,
  key acdoca.docln,

      acdoca.poper                                                          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_p.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 plan_values.bukrs is null then 'X'
        when ledger_p.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 plan_values.bukrs is null then 'X'

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

//        else ''

//      end                                                                   as poper_d,

      case
        when plan_values.bukrs 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 plan_values.bukrs 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 plan_values.bukrs 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 plan_values.bukrs 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 plan_values.bukrs 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 plan_values.bukrs 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 plan_values.bukrs 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 plan_values.bukrs is null then 'X'
        when ledger_p.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 plan_values.bukrs is null then ''
        else 'X'
      end                                                                   as redundant,
      @EndUserText.label: 'Migrated'
      @EndUserText.quickInfo: 'Has been migrated'
      cast( '-' as char01 preserving type )                                 as direction,
      acdoca.anlkl,
      acdoca.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":""
}
}*/