aps_com_cs_csmpt

@AbapCatalog.sqlViewName: 'APS_COM_CSMPT'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
//@Search.searchable: true

@EndUserText.label: 'Custom Communication Scenarios'


//@ObjectModel.writeEnabled: true

//@ObjectModel.createEnabled:true

//@ObjectModel.deleteEnabled:true

//@ObjectModel.updateEnabled:true


define view aps_com_cs_csmpt
  as select from    APS_COM_CSCN_API_ROOT    as application
  // Count arrangements

    left outer join aps_com_cs_ca_c                       on aps_com_cs_ca_c.CommunicationScenario = application.communication_scenario_id

  //Join to the status of the scenario

    left outer join aps_com_d_cscn                        on aps_com_d_cscn.communication_scenario_id = application.communication_scenario_id

    left outer join aps_com_w_wl_prt                      on  aps_com_w_wl_prt.profile = application.ob_oauth2_client_profile
                                                          and aps_com_w_wl_prt.langu   = $session.system_language

    left outer join aps_user_ddl             as _User     on _User.UserID = application.change_user

    left outer join usr05                    as user05    on  user05.bname = $session.user
                                                          and user05.mandt = $session.client
                                                          and user05.parid = 'ATO_TEST_SERVICE'

    left outer join ATOV_U_ITEMS_EXPORTED_AT as _Exported on  _Exported.item_id   = application.communication_scenario_id
                                                          and _Exported.item_type = 'COM_SCN'

    inner join      aps_com_c_cscn                        on application.communication_scenario_id = aps_com_c_cscn.communication_scenario_id

    left outer join ato_setup                as setup     on setup.client = $session.client
  // Association to assigned catalogs

  association [0..*] to APS_COM_COMM_CSCNIB as _InboundServiceAssignment  on _InboundServiceAssignment.CommScenID = $projection.CommScenID
  association [0..*] to APS_COM_COMM_CSCNOB as _OutboundServiceAssignment on _OutboundServiceAssignment.CommScenID = $projection.CommScenID
  association [0..*] to aps_com_cs_ca       as _AssignedInArrangements    on _AssignedInArrangements.CommunicationScenario = application.communication_scenario_id
  association [0..*] to APS_COM_CS_AUTH     as _AuthorizationAssignments  on _AuthorizationAssignments.ScenarioId = $projection.CommScenID

{

      //@Search:{ defaultSearchElement: true, ranking: #HIGH, fuzzinessThreshold: 0.8 }

  key application.communication_scenario_id                                      as CommScenID,

      //@ObjectModel.readOnly: true

      application.description                                                    as Description,

      //@ObjectModel.readOnly: true

      @Consumption.filter.hidden : true
      application.change_timestamp                                               as LastChangeTime,

      //@ObjectModel.readOnly: true

      //_User.UserDescription                                                as LastChangedBy,

      case
        when setup.ext_dev_system = 'X' then _User.UserDescription
        when user05.parva = 'X' then _User.UserDescription
        else 'SYSTEM'
        end                                                                      as LastChangedBy,

      //@ObjectModel.readOnly: true

      @Consumption.filter.hidden : true
      aps_com_d_cscn.vtr_number                                                  as VTRNumber,

      //@ObjectModel.readOnly: true

      @Consumption.filter.hidden : true
      aps_com_d_cscn.vtr_status                                                  as VTRStatus,

      //@ObjectModel.readOnly: true

      @Consumption.filter.hidden : true
      aps_com_d_cscn.vtr_symptom                                                 as VTRSymptom,

      //@ObjectModel.readOnly: true

      @Consumption.filter.hidden : true
      case
      when aps_com_d_cscn.status is null then 'X'
      else aps_com_d_cscn.status
      end                                                                        as Status,


      case
        when (aps_com_d_cscn.status is null or aps_com_d_cscn.status = 'P') and (aps_com_d_cscn.vtr_number='' or aps_com_d_cscn.vtr_number is null) then 2 // Published

        //when aps_com_d_cscn.status = 'U' and aps_com_d_cscn.vtr_number = '' then 1

        when aps_com_d_cscn.destination_status = 'U' or aps_com_d_cscn.destination_status = '' then 1 // Unpublished

        when aps_com_d_cscn.vtr_number != '' and aps_com_d_cscn.vtr_status = '' then 3 // Publishing

        when aps_com_d_cscn.vtr_status != '' then 4 // Rejected

        else 1
      end                                                                        as ActualStatus,

      //@ObjectModel.readOnly: true

      @Consumption.filter.hidden : true
      aps_com_cs_ca_c.AssignedArrangementCount                                   as AssignCount,


      application.ob_oauth2_auth                                                 as ObOAuth2Active,
      application.ob_oauth2_client_profile                                       as ObOAuth2ClientProfile,
      aps_com_w_wl_prt.description                                               as ObOAuth2ClientProfileDesc,

      application.ob_x509_auth                                                   as ObX509Auth,
      application.ob_basic_auth                                                  as ObBasicAuth,
      application.ob_oauth1_auth                                                 as ObOAuth1Active,
      application.ob_none_auth                                                   as ObNoneAuth,
      
      application.abap_language_version                                          as AbapLanguageVersion,

      case
              when application.ob_oauth2_granttype = 0 then ''
              else cast(application.ob_oauth2_granttype as abap.char(11))
              end                                                                as ObOAuth2Granttype,

      _Exported.exported_at                                                      as Exported,

      case
        when _Exported.exported_at is null then 'N'
        when application.change_timestamp < _Exported.exported_at then 'Y'
        else 'C'
        end                                                                      as ExportStatus,

      //application.ob_oauth2_granttype                                      as ObOAuth2Granttype,


      _InboundServiceAssignment,
      _OutboundServiceAssignment,
      _AssignedInArrangements,
      _AuthorizationAssignments

}
where
       application.communication_scenario_id   not like 'SAP_%'
  and  application.communication_scenario_type = #APS_COM_CSCN_TYPE.'1'
  
  and // Customer Managed Scenarios

  (
       aps_com_c_cscn.scope_state              = #APS_BC_SCOPE_STATE.'3'
    or application.scope_dependent             = ''
  )   // In Scope or NOT scope dependent