FISVD_TRK_DOC_11

@AbapCatalog.sqlViewName: 'FISV_TRK_DOC_11'
@EndUserText.label: 'Journal Entry History Convert GS Fields'
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.sizeCategory: #XL
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@AbapCatalog.preserveKey:true
@AbapCatalog.compiler.compareFilter:true

define view FISVD_TRK_DOC_11
  as select from(
    (
                    cdhdr
        inner join  FISCDS_TRK_DOC_04          on cdhdr.objectid = FISCDS_TRK_DOC_04.objectid
    )
      inner join    cdpos                      on  cdpos.objectclas = cdhdr.objectclas
                                               and cdpos.objectid   = cdhdr.objectid
                                               and cdpos.changenr   = cdhdr.changenr
                                               and cdpos.tabkey     = FISCDS_TRK_DOC_04.tabkey
  )
    left outer join t001                       on t001.bukrs = FISCDS_TRK_DOC_04.CompanyCode
    left outer join fins_glo_it_fl_t as glo_it on  t001.land1 = glo_it.country
                                               and fname      = glo_it.field_name
    left outer join fins_glo_ot_fl_t as glo_ot on  t001.land1 = glo_ot.country
                                               and fname      = glo_ot.field_name
{
  key CompanyCode,
  key Ledger,
  key AccountingDocument,
  key cdpos.changenr,
  key cdpos.tabname as TableName,
  key cdpos.tabkey,
  key fname,
  key FISCDS_TRK_DOC_04.JournalEntryItem,

      case
        when
         fname = 'GLO_RE1_OT' then
          'QRIBAN_ADDL_INFO'
        when
         fname like 'GLO_%_OT' then
          glo_ot.data_element
        when
         fname like 'GLO_%' then
          glo_it.data_element
        else
          fname
        end         as rollname,

      FiscalYear,
      PostingDate,
      DocumentDate,
      username      as ChangedBy,
      utime         as ChangedAt,
      value_old     as OldValue,
      value_new     as NewValue,
      udate         as ChangedOn,
      cdpos.text_case,
      CreationDate,
      CreationTime,
      AccountType,
      GLAccountNR,
      cdpos.chngind
}
where
  (
       cdhdr.objectclas = 'BELEG'
    or cdhdr.objectclas = 'BELEGR'
    or cdhdr.objectclas = 'ISJP_IT'
  )
  and  cdpos.fname != 'AEDAT'
  and  cdpos.fname != 'KEY'
  and(
       cdpos.tabname    = 'BSEG'
    or cdpos.tabname    = 'BSEC'
    or cdpos.tabname    = 'BSEGR'
    or cdpos.tabname    = 'ISJPINVSUMIT'
  )

union

select from(
  (
                 cdhdr
      inner join FISCDS_TRK_DOC_07 on cdhdr.objectid = FISCDS_TRK_DOC_07.objectid
  )
    inner join   cdpos             on  cdpos.objectclas = cdhdr.objectclas
                                   and cdpos.objectid   = cdhdr.objectid
                                   and cdpos.changenr   = cdhdr.changenr
                                   and cdpos.tabkey     = FISCDS_TRK_DOC_07.bkpf_tabkey
)
{
  key CompanyCode,
  key case cdpos.tabname when 'BKPF' then ''
    when 'BSEGR' then ''
    when 'ACDOCA' then substring(cdpos.tabkey, 4,2)
  end               as Ledger,
  key AccountingDocument,
  key cdpos.changenr,
  key cdpos.tabname as TableName,
  key cdpos.tabkey,
  key fname,
  key FISCDS_TRK_DOC_07.JournalEntryItem,
      fname         as rollname,
      FiscalYear,
      PostingDate,
      DocumentDate,
      username      as ChangedBy,
      utime         as ChangedAt,
      value_old     as OldValue,
      value_new     as NewValue,
      udate         as ChangedOn,
      cdpos.text_case,
      CreationDate,
      CreationTime,
      AccountType,
      GLAccountNR,
      cdpos.chngind
}
where
  (
        cdhdr.objectclas = 'BELEGR'
    and cdpos.tabname    = 'BSEGR'

  )
union

select from(
  (
                  cdhdr
      inner join  FISCDS_TRK_DOC_07          on cdhdr.objectid = FISCDS_TRK_DOC_07.objectid
  )
    inner join    cdpos                      on  cdpos.objectclas = cdhdr.objectclas
                                             and cdpos.objectid   = cdhdr.objectid
                                             and cdpos.changenr   = cdhdr.changenr

)
  left outer join t001                       on t001.bukrs = FISCDS_TRK_DOC_07.CompanyCode
  left outer join fins_glo_hd_fl_t as glo_hd on  t001.land1 = glo_hd.country
                                             and fname      = glo_hd.field_name
{
  key CompanyCode,
  key case cdpos.tabname when 'BKPF' then ''
    when 'BSEGR' then ''
    when 'ACDOCA' then substring(cdpos.tabkey, 4,2)
  end               as Ledger,
  key AccountingDocument,
  key cdpos.changenr,
  key cdpos.tabname as TableName,
  key cdpos.tabkey,
  key fname,
  key FISCDS_TRK_DOC_07.JournalEntryItem,
      case when
        fname like 'GLO_%_HD' then
          glo_hd.data_element
        else
          fname
        end         as rollname,
      FiscalYear,
      PostingDate,
      DocumentDate,
      username      as ChangedBy,
      utime         as ChangedAt,
      value_old     as OldValue,
      value_new     as NewValue,
      udate         as ChangedOn,
      cdpos.text_case,
      CreationDate,
      CreationTime,
      AccountType,
      GLAccountNR,
      cdpos.chngind
}
where
      cdhdr.objectclas = 'BELEG'
  and cdpos.fname != 'AEDAT'
  and cdpos.tabname    = 'BKPF'

union

select from(
  (
                 cdhdr
      inner join FISCDS_TRK_DOC_03 on cdhdr.objectid = FISCDS_TRK_DOC_03.objectid

  )
    inner join   cdpos             on  cdpos.objectclas = cdhdr.objectclas
                                   and cdpos.objectid   = cdhdr.objectid
                                   and cdpos.changenr   = cdhdr.changenr
                                   and cdpos.tabkey     = FISCDS_TRK_DOC_03.bsegadd_tabkey

)
{
  key CompanyCode,
  key Ledger,
  key AccountingDocument,
  key cdpos.changenr,
  key cdpos.tabname as TableName,
  key cdpos.tabkey,
  key fname,
  key FISCDS_TRK_DOC_03.JournalEntryItem,
      fname         as rollname,
      FiscalYear,
      PostingDate,
      DocumentDate,
      username      as ChangedBy,
      utime         as ChangedAt,
      value_old     as OldValue,
      value_new     as NewValue,
      udate         as ChangedOn,
      cdpos.text_case,
      CreationDate,
      CreationTime,
      AccountType,
      GLAccountNR,
      cdpos.chngind
}
where
  (
       cdhdr.objectclas = 'BELEG'
    or cdhdr.objectclas = 'BELEGR'
    or cdhdr.objectclas = 'ISJP_IT'
  )
  and  cdpos.fname != 'AEDAT'
  and  cdpos.fname != 'KEY'
  and(
       cdpos.tabname    = 'BSEG_ADD'
    or cdpos.tabname    = 'ACDOCA'
  )

union

select from(
  (
                  cdhdr
      inner join  FISCDS_TRK_DOC_05          on cdhdr.objectid = FISCDS_TRK_DOC_05.objectid
  )
    inner join    cdpos                      on  cdpos.objectclas = cdhdr.objectclas
                                             and cdpos.objectid   = cdhdr.objectid
                                             and cdpos.changenr   = cdhdr.changenr
                                             and cdpos.tabkey     = FISCDS_TRK_DOC_05.bkpf_tabkey
)
  left outer join t001                       on t001.bukrs = FISCDS_TRK_DOC_05.CompanyCode
  left outer join fins_glo_hd_fl_t as glo_hd on  t001.land1 = glo_hd.country
                                             and fname      = glo_hd.field_name
{
  key CompanyCode,
  key Ledger,
  key AccountingDocument,
  key cdpos.changenr,
  key cdpos.tabname as TableName,
  key cdpos.tabkey,
  key fname,
  key FISCDS_TRK_DOC_05.JournalEntryItem,
      case when
        fname like 'GLO_%_HD' then
          glo_hd.data_element
        else
          fname
        end         as rollname,
      FiscalYear,
      PostingDate,
      DocumentDate,
      username      as ChangedBy,
      utime         as ChangedAt,
      value_old     as OldValue,
      value_new     as NewValue,
      udate         as ChangedOn,
      cdpos.text_case,
      CreationDate,
      CreationTime,
      AccountType,
      GLAccountNR,
      cdpos.chngind
}
where
      cdhdr.objectclas = 'BELEG'
  and cdpos.tabname    = 'BKPF'
  and cdpos.fname != 'AEDAT'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"FISCDS_TRK_DOC_03",
"FISCDS_TRK_DOC_04",
"FISCDS_TRK_DOC_05",
"FISCDS_TRK_DOC_07",
"CDHDR",
"CDPOS",
"FINS_GLO_HD_FL_T",
"FINS_GLO_IT_FL_T",
"FINS_GLO_OT_FL_T",
"T001"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/