fac_cds_uh_version

@AbapCatalog.sqlViewName: 'FACV_UH_VRSN'
//@ClientHandling.algorithm: #SESSION_VARIABLE

//@AbapCatalog.compiler.compareFilter: true

@AccessControl.authorizationCheck: #CHECK
@AccessControl.privilegedAssociations:  [ 'to_node' ]
@EndUserText.label: 'Universal Hierarchy Version'

@ObjectModel.usageType: {
  dataClass: #MASTER,
  serviceQuality: #C,
  sizeCategory: #S
}
define view fac_cds_uh_version
  as select from    uhdt_vrsn    as v

    left outer join uhdt_hier    as h   on  v.hier_hid  = h.hier_hid
                                        and v.hier_catg = h.hier_catg
                                        and h.lang      = $session.system_language

    left outer join uhdt_hid     as hid on  v.hier_hid  = hid.hier_hid
                                        and v.hier_catg = hid.hier_catg
                                        and hid.ver_id  = ''

    left outer join uhdt_job_log as actjob on v.ver_id = actjob.ver_id and actjob.action = '1' //for activate job

    left outer join uhdt_job_log as downloadjob on v.ver_id = downloadjob.ver_id and downloadjob.action = '3' //for download job

  association [1..1] to fac_cds_uh_category           as to_category        on $projection.HierarchyCategory = to_category.CategoryKey
  association [1..*] to fac_cds_uh_node               as to_node            on $projection.calVersionId = to_node.VersionId
  association [1..*] to fac_cds_uh_clfd_value         as to_clfd_value      on $projection.VersionId = to_clfd_value.ObjectId
  association [1..1] to fac_cds_uh_futb_hd_multiattr  as to_merged_attrvals on $projection.VersionId = to_merged_attrvals.Ver_Id
  association [0..*] to FAC_CDS_UH_CD                 as to_changelog       on $projection.VersionId = to_changelog.VersionId
  association [1..1] to FAC_CDS_FUNCTBL_VRSN_MULTITAG as to_vrsntag         on $projection.VersionId = to_vrsntag.ver_id

{
  key v.ver_id                                     as VersionId,
      ver_pid                                      as VersionParentId,
      case when ver_status = 'A' and locked_by <> ''
           then 'I'
           else ver_status       end               as VersionStatus,
      //ver_status                   as  VersionStatus,

      ver_vldfm                                    as ValidityStartDate,
      ver_vldto                                    as ValidityEndDate,
      ver_msg                                      as VersionMessage,
      ver_nbr                                      as VersionNumber,
      ver_idx                                      as VersionIndex,
      v.hier_hid                                   as HierarchyId,
      v.hier_catg                                  as HierarchyCategory,
      v.updated_at                                 as UpdatedAt,
      v.updated_by                                 as UpdatedBy,
      v.created_at                                 as CreatedAt,
      v.created_by                                 as CreatedBy,
      v.node_timedep                               as HierarchyNodeIsTimeDependent,
      h.hier_desc                                  as HierarchyText,
      //cast( cast(substring( cast(created_at as abap.char(30)) , 1 , 8) as abap.dats(8))  as fis_cpdat ) as CreatedOn,

      //tstmp_to_dats( v.created_at,

                     //abap_system_timezone( $session.client,'NULL' ),

                     //$session.client,

                     //'NULL' )                      as CreatedOn,

      tstmp_to_dats( v.created_at,
                     abap_user_timezone( $session.user,$session.client,'NULL' ),
                     $session.client,
                     'NULL' )                      as CreatedOn,               
      locked_by                                    as LockedBy,
      locked_at                                    as LockedAt,
      allow_legacy_tcode_usage                     as AllowLegacyTCodeUsage,

      case when locked_by <> '' and locked_by <> $session.user
           then concat(v.ver_id, 'T')
           else v.ver_id         end               as calVersionId,

      to_category.CategoryText                     as CategoryText,
      to_merged_attrvals.AttributeValues           as AttributeValues,
      maintenance_lang                             as MAINTENANCE_LANG,
      to_node,
      to_clfd_value,
      to_category,
      to_changelog,
      concat(concat(',',to_vrsntag.hier_tags),',') as HierarchyTags,
      to_vrsntag.hier_tagst                        as HierarchyTagsText,
      to_vrsntag,
      hid.isprivate                                as IsPrivate,

      ''                                           as ViaJob,
      actjob.jobname                               as ActJobName,                        
      actjob.jobcount                              as ActJobCount,
      actjob.status                                as ActJobStatus,
      actjob.action                                as ActJobAction,
      downloadjob.jobname                          as DownloadJobName,                        
      downloadjob.jobcount                         as DownloadJobCount,
      downloadjob.status                           as DownloadJobStatus,
      downloadjob.action                           as DownloadJobAction
}
where
      ver_status <> 'T' //filter out Temporary status

  and ver_status <> 'B' //filter out Temporary status