FAA_SDM_DOCIT_CP121

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

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

define view FAA_SDM_DOCIT_CP121
  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 <> ''
    left outer join(
                               faat_doc_it        as doc_it
        inner join             FAA_CFG_DEPR_AREA  as cfg_depr_area on  doc_it.bukrs = cfg_depr_area.comp_code
                                                                   and doc_it.afabe = cfg_depr_area.depr_area
                                                                   and sdm_status   = '19'
        inner join             fins_tech_ledger       as ledger_it     on  ledger_it.rldnr       =  cfg_depr_area.ledger
                                                                   and ledger_it.tech_ledger <> ''
        left outer to one join acdoca             as fi_doc        on  doc_it.has_fi_doc = 'X'
                                                                   and fi_doc.rldnr      = ledger_it.rldnr
                                                                   and fi_doc.awtyp      = doc_it.awtyp
                                                                   and fi_doc.awref      = doc_it.awref
                                                                   and fi_doc.aworg      = doc_it.aworg
                                                                   and fi_doc.awsys      = doc_it.awsys
                                                                   and fi_doc.rbukrs     = doc_it.bukrs
                                                                   and fi_doc.anln1      = doc_it.anln1
                                                                   and fi_doc.anln2      = doc_it.anln2
                                                                   and fi_doc.gjahr      = doc_it.gjahr
                                                                   and fi_doc.subta      = doc_it.subta
                                                                   and fi_doc.afabe      = doc_it.afabe_fi_doc
                                                                   and fi_doc.movcat     = doc_it.movcat
    )
    on  acdoca.rbukrs     = doc_it.bukrs
    and acdoca.anln1      = doc_it.anln1
    and acdoca.anln2      = doc_it.anln2
    and acdoca.gjahr      = doc_it.gjahr
    and acdoca.awtyp      = doc_it.awtyp
    and acdoca.awref      = doc_it.awref
    and acdoca.aworg      = doc_it.aworg
    and acdoca.awsys      = doc_it.awsys
    and acdoca.subta      = doc_it.subta
    and acdoca.afabe      = doc_it.afabe
    and acdoca.slalittype = doc_it.slalittype
    and acdoca.drcrk      = doc_it.drcrk
    left outer join            FAA_SDM_DOCIT_CP02 as split_number  on  split_number.bukrs        = doc_it.bukrs
                                                                   and split_number.anln1        = doc_it.anln1
                                                                   and split_number.anln2        = doc_it.anln2
                                                                   and split_number.gjahr        = doc_it.gjahr
                                                                   and split_number.awtyp        = doc_it.awtyp
                                                                   and split_number.awref        = doc_it.awref
                                                                   and split_number.aworg        = doc_it.aworg
                                                                   and split_number.awsys        = doc_it.awsys
                                                                   and split_number.subta        = doc_it.subta
                                                                   and split_number.afabe        = doc_it.afabe
                                                                   and split_number.slalittype   = doc_it.slalittype
                                                                   and split_number.drcrk        = doc_it.drcrk
                                                                   and split_number.source_table = 'DOC_IT'
{
  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.slalittype,
  key acdoca.drcrk,
  key cast( acdoca.rldnr as fins_technical_ledger preserving type )          as rldnr,
  key acdoca.belnr,
  key acdoca.docln,
      cast( case
              when split_number.counter is null then 0
              else split_number.counter
            end as int4 preserving type )                                    as split_number,

      doc_it.awitem                                                          as s_awitem,
      acdoca.awitem                                                          as t_awitem,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.awitem <> acdoca.awitem then 'X'
        else ''
      end                                                                    as awitem_d,

      cast( ledger_it.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 doc_it.bukrs is null then 'X'
        when ledger_it.tech_ledger <> acdoca.rldnr then 'X'
        else ''
      end                                                                    as tech_ledger_d,

      doc_it.vorgn                                                           as s_vorgn,
      acdoca.vorgn                                                           as t_vorgn,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.vorgn <> acdoca.vorgn then 'X'
        else ''
      end                                                                    as vorgn_d,

      doc_it.budat                                                           as s_budat,
      acdoca.budat                                                           as t_budat,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.budat <> acdoca.budat then 'X'
        else ''
      end                                                                    as budat_d,

      doc_it.bzdat                                                           as s_bzdat,
      acdoca.bzdat                                                           as t_bzdat,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.bzdat <> acdoca.bzdat then 'X'
        else ''
      end                                                                    as bzdat_d,

      doc_it.poper                                                           as s_poper,
      acdoca.poper                                                           as t_poper,
      case
        when doc_it.bukrs is null then 'X'
        when acdoca.movcat <> '00' and doc_it.poper is initial and acdoca.poper is initial then 'X'
        when acdoca.movcat <> '00' and doc_it.poper is not initial and doc_it.poper <> acdoca.poper then 'X'
        when acdoca.movcat = '00' and doc_it.poper <> acdoca.poper then 'X'
        else ''
      end                                                                    as poper_d,

      doc_it.bwasl                                                           as s_bwasl,
      cast( acdoca.anbwa as bwasl preserving type )                          as t_bwasl,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.bwasl <> acdoca.anbwa then 'X'
        else ''
      end                                                                    as bwasl_d,

      doc_it.movcat                                                          as s_movcat,
      acdoca.movcat                                                          as t_movcat,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.movcat <> acdoca.movcat then 'X'
        else ''
      end                                                                    as movcat_d,

      @Semantics.amount.currencyCode: 's_rhcur'
      doc_it.hsl                                                             as s_hsl,
      @Semantics.amount.currencyCode: 't_rhcur'
      acdoca.hsl                                                             as t_hsl,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.hsl <> acdoca.hsl then 'X'
        else ''
      end                                                                    as hsl_d,

      @Semantics.amount.currencyCode: 's_rkcur'
      doc_it.ksl                                                             as s_ksl,
      @Semantics.amount.currencyCode: 't_rkcur'
      acdoca.ksl                                                             as t_ksl,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.ksl <> acdoca.ksl then 'X'
        else ''
      end                                                                    as ksl_d,

      @Semantics.amount.currencyCode: 's_rocur'
      doc_it.osl                                                             as s_osl,
      @Semantics.amount.currencyCode: 't_rocur'
      acdoca.osl                                                             as t_osl,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.osl <> acdoca.osl then 'X'
        else ''
      end                                                                    as osl_d,

      @Semantics.amount.currencyCode: 's_rvcur'
      doc_it.vsl                                                             as s_vsl,
      @Semantics.amount.currencyCode: 't_rvcur'
      acdoca.vsl                                                             as t_vsl,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.vsl <> acdoca.vsl then 'X'
        else ''
      end                                                                    as vsl_d,

      @Semantics.currencyCode: true
      doc_it.rhcur                                                           as s_rhcur,
      @Semantics.currencyCode: true
      acdoca.rhcur                                                           as t_rhcur,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.rhcur <> acdoca.rhcur then 'X'
        else ''
      end                                                                    as rhcur_d,

      @Semantics.currencyCode: true
      doc_it.rkcur                                                           as s_rkcur,
      @Semantics.currencyCode: true
      acdoca.rkcur                                                           as t_rkcur,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.rkcur is initial and acdoca.rkcur is initial then 'X'
        when doc_it.rkcur is not initial and doc_it.rkcur <> acdoca.rkcur then 'X'
        else ''
      end                                                                    as rkcur_d,

      @Semantics.currencyCode: true
      doc_it.rocur                                                           as s_rocur,
      @Semantics.currencyCode: true
      acdoca.rocur                                                           as t_rocur,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.rocur <> acdoca.rocur then 'X'
        else ''
      end                                                                    as rocur_d,

      @Semantics.currencyCode: true
      doc_it.rvcur                                                           as s_rvcur,
      @Semantics.currencyCode: true
      acdoca.rvcur                                                           as t_rvcur,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.rvcur <> acdoca.rvcur then 'X'
        else ''
      end                                                                    as rvcur_d,

      @Semantics.currencyCode: true
      cast( '' as fins_currfc )                                              as s_rfccur,
      @Semantics.currencyCode: true
      acdoca.rfccur                                                          as t_rfccur,
      case
        when doc_it.bukrs is null then 'X'
        when acdoca.rfccur is initial then 'X'
        else ''
      end                                                                    as rfccur_d,

      doc_it.xreversing                                                      as s_xreversing,
      acdoca.xreversing                                                      as t_xreversing,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.xreversing <> acdoca.xreversing then 'X'
        else ''
      end                                                                    as xreversing_d,

      doc_it.xreversed                                                       as s_xreversed,
      acdoca.xreversed                                                       as t_xreversed,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.xreversed <> acdoca.xreversed then 'X'
        else ''
      end                                                                    as xreversed_d,

      doc_it.xsettling                                                       as s_xsettling,
      acdoca.xsettling                                                       as t_xsettling,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.xsettling <> acdoca.xsettling then 'X'
        else ''
      end                                                                    as xsettling_d,

      doc_it.xsettled                                                        as s_xsettled,
      acdoca.xsettled                                                        as t_xsettled,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.xsettled <> acdoca.xsettled then 'X'
        else ''
      end                                                                    as xsettled_d,

      doc_it.prec_awtyp                                                      as s_prec_awtyp,
      acdoca.prec_awtyp                                                      as t_prec_awtyp,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.movcat = '00' and acdoca.prec_awtyp <> '' then 'X'
        when doc_it.movcat <> '00' and doc_it.prec_awtyp <> acdoca.prec_awtyp then 'X'
        else ''
      end                                                                    as prec_awtyp_d,

      doc_it.prec_aworg                                                      as s_prec_aworg,
      acdoca.prec_aworg                                                      as t_prec_aworg,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.prec_aworg <> acdoca.prec_aworg then 'X'
        else ''
      end                                                                    as prec_aworg_d,

      doc_it.prec_awref                                                      as s_prec_awref,
      acdoca.prec_awref                                                      as t_prec_awref,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.prec_awref <> acdoca.prec_awref then 'X'
        else ''
      end                                                                    as prec_awref_d,

      doc_it.prec_subta                                                      as s_prec_subta,
      acdoca.prec_subta                                                      as t_prec_subta,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.prec_subta <> acdoca.prec_subta then 'X'
        else ''
      end                                                                    as prec_subta_d,

      doc_it.awtyp_rev                                                       as s_awtyp_rev,
      acdoca.awtyp_rev                                                       as t_awtyp_rev,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.awtyp_rev <> acdoca.awtyp_rev then 'X'
        else ''
      end                                                                    as awtyp_rev_d,

      doc_it.awref_rev                                                       as s_awref_rev,
      acdoca.awref_rev                                                       as t_awref_rev,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.awref_rev <> acdoca.awref_rev then 'X'
        else ''
      end                                                                    as awref_rev_d,

      doc_it.aworg_rev                                                       as s_aworg_rev,
      acdoca.aworg_rev                                                       as t_aworg_rev,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.aworg_rev <> acdoca.aworg_rev then 'X'
        else ''
      end                                                                    as aworg_rev_d,

      doc_it.subta_rev                                                       as s_subta_rev,
      acdoca.subta_rev                                                       as t_subta_rev,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.subta_rev <> acdoca.subta_rev then 'X'
        else ''
      end                                                                    as subta_rev_d,

      doc_it.anlkl                                                           as s_anlkl,
      acdoca.anlkl                                                           as t_anlkl,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.anlkl <> acdoca.anlkl then 'X'
        else ''
      end                                                                    as anlkl_d,

      doc_it.ktogr                                                           as s_ktogr,
      acdoca.ktogr                                                           as t_ktogr,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.ktogr <> acdoca.ktogr then 'X'
        else ''
      end                                                                    as ktogr_d,

      doc_it.panl1                                                           as s_panl1,
      acdoca.panl1                                                           as t_panl1,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.panl1 <> acdoca.panl1 then 'X'
        else ''
      end                                                                    as panl1_d,

      doc_it.panl2                                                           as s_panl2,
      acdoca.panl2                                                           as t_panl2,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.panl2 <> acdoca.panl2 then 'X'
        else ''
      end                                                                    as panl2_d,

      doc_it.mig_source                                                      as s_mig_source,
      acdoca.mig_source                                                      as t_mig_source,
      case
        when doc_it.bukrs is null then 'X'
        when ( doc_it.sdm_status = '19' and acdoca.mig_source <> 'A') or
             ( doc_it.sdm_status <> '19' and doc_it.mig_source <> acdoca.mig_source ) then 'X'
        else ''
      end                                                                    as mig_source_d,

      doc_it.recid                                                           as s_recid,
      acdoca.recid                                                           as t_recid,
      case
        when doc_it.bukrs is null then 'X'
        when doc_it.recid <> acdoca.recid then 'X'
        else ''
      end                                                                    as recid_d,

      case
        when fi_doc.rbukrs is null then doc_it.bldat
        else fi_doc.bldat
      end                                                                    as s_bldat,
      acdoca.bldat                                                           as t_bldat,
      case
        when doc_it.bukrs is null then 'X'
        when fi_doc.rbukrs is null and doc_it.bldat <> acdoca.bldat then 'X'
        when fi_doc.rbukrs is not null and fi_doc.bldat <> acdoca.bldat then 'X'
        else ''
      end                                                                    as bldat_d,

      case
        when fi_doc.rbukrs is null then doc_it.sgtxt
        else fi_doc.sgtxt
      end                                                                    as s_sgtxt,
      acdoca.sgtxt                                                           as t_sgtxt,
      case
        when doc_it.bukrs is null then 'X'
        when fi_doc.rbukrs is null and doc_it.sgtxt <> acdoca.sgtxt then 'X'
        when fi_doc.rbukrs is not null and fi_doc.sgtxt <> acdoca.sgtxt then 'X'
        else ''
      end                                                                    as sgtxt_d,

      case
        when fi_doc.rbukrs is null then doc_it.zuonr
        else fi_doc.zuonr
      end                                                                    as s_zuonr,
      acdoca.zuonr                                                           as t_zuonr,
      case
        when doc_it.bukrs is null then 'X'
        when fi_doc.rbukrs is null and doc_it.zuonr <> acdoca.zuonr then 'X'
        when fi_doc.rbukrs is not null and fi_doc.zuonr <> acdoca.zuonr then 'X'
        else ''
      end                                                                    as zuonr_d,

      case
        when fi_doc.rbukrs is null then doc_it.obart
        when fi_doc.rbukrs is not null and fi_doc.parob1 is initial then doc_it.obart
        else cast( substring(fi_doc.parob1,1,2) as a_obart preserving type )
      end                                                                    as s_obart,
      cast( substring(acdoca.parob1,1,2)
            as a_obart preserving type )                                     as t_obart,
      case
        when doc_it.bukrs is null then 'X'
        when fi_doc.rbukrs is null and doc_it.obart <> substring(acdoca.parob1,1,2) then 'X'
        when fi_doc.rbukrs is not null and fi_doc.parob1 is initial and doc_it.obart <> substring(acdoca.parob1,1,2) then 'X'
        when fi_doc.rbukrs is not null and fi_doc.parob1 is not initial and fi_doc.parob1 <> acdoca.parob1 then 'X'
        else ''
      end                                                                    as obart_d,

      case
        when fi_doc.rbukrs is null then doc_it.objid
        when fi_doc.rbukrs is not null and fi_doc.parob1 is initial then doc_it.objid
        else cast( substring(fi_doc.parob1,3,20) as a_objid preserving type )
      end                                                                    as s_objid,
      cast( substring(acdoca.parob1,3,20)
            as a_objid preserving type )                                     as t_objid,
      case
        when doc_it.bukrs is null then 'X'
        when fi_doc.rbukrs is null and doc_it.objid <> substring(acdoca.parob1,3,20) then 'X'
        when fi_doc.rbukrs is not null and fi_doc.parob1 is initial and doc_it.objid <> substring(acdoca.parob1,3,20) then 'X'
        when fi_doc.rbukrs is not null and fi_doc.parob1 is not initial and fi_doc.parob1 <> acdoca.parob1 then 'X'
        else ''
      end                                                                    as objid_d,

      case
        when fi_doc.rbukrs is null then doc_it.ubzdt_pn
        else fi_doc.ubzdt_pn
      end                                                                    as s_ubzdt_pn,
      acdoca.ubzdt_pn                                                        as t_ubzdt_pn,
      case
        when doc_it.bukrs is null then 'X'
        when fi_doc.rbukrs is null and doc_it.ubzdt_pn <> acdoca.ubzdt_pn then 'X'
        when fi_doc.rbukrs is not null and fi_doc.ubzdt_pn <> acdoca.ubzdt_pn then 'X'
        else ''
      end                                                                    as ubzdt_pn_d,

      case
        when fi_doc.xvabg_pn = 'X' then fi_doc.xvabg_pn
        else doc_it.xvabg_pn
      end                                                                    as s_xvabg_pn,
      acdoca.xvabg_pn                                                        as t_xvabg_pn,
      case
        when fi_doc.xvabg_pn = 'X' and fi_doc.xvabg_pn <> acdoca.xvabg_pn then 'X'
        when fi_doc.xvabg_pn <> 'X' and doc_it.xvabg_pn <> acdoca.xvabg_pn then 'X'
        else ''
      end                                                                    as xvabg_pn_d,

      case
        when fi_doc.rbukrs is null then doc_it.prozs_pn
        else fi_doc.prozs_pn
      end                                                                    as s_prozs_pn,
      acdoca.prozs_pn                                                        as t_prozs_pn,
      case
        when doc_it.bukrs is null then 'X'
        when fi_doc.rbukrs is null and doc_it.prozs_pn <> acdoca.prozs_pn then 'X'
        when fi_doc.rbukrs is not null and fi_doc.prozs_pn <> acdoca.prozs_pn then 'X'
        else ''
      end                                                                    as prozs_pn_d,

      case
        when fi_doc.rbukrs is null then doc_it.xmanpropval_pn
        else fi_doc.xmanpropval_pn
      end                                                                    as s_xmanpropval_pn,
      acdoca.xmanpropval_pn                                                  as t_xmanpropval_pn,
      case
        when doc_it.bukrs is null then 'X'
        when fi_doc.rbukrs is null and doc_it.xmanpropval_pn <> acdoca.xmanpropval_pn then 'X'
        when fi_doc.rbukrs is not null and fi_doc.xmanpropval_pn <> acdoca.xmanpropval_pn then 'X'
        else ''
      end                                                                    as xmanpropval_pn_d,

      @EndUserText.label: 'Different'
      @EndUserText.quickInfo: 'Has some differences'
      case
        when doc_it.bukrs is null then 'Y'
        else ''
      end                                                                    as different,

      cast( '-' as char01 preserving type )                                  as direction,
      acdoca.anlkl,
      acdoca.movcat,
      cast( acdoca.anbwa as bwasl preserving type )                          as bwasl,
      acdoca.vorgn,
      cast( '19' as faa_sdm_status )                                         as sdm_status
}
where
      acdoca.awtyp      <> 'AMDP'
  and acdoca.mig_source =  'A'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FAA_CFG_DEPR_AREA",
"FAA_SDM_DOCIT_CP02",
"ACDOCA",
"FAAT_DOC_IT",
"FINS_TECH_LEDGER"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/