p_datacontrollersdmstatus

@AccessControl.authorizationCheck: #NOT_REQUIRED
//@EndUserText.label: 'Utility view for MDBPCV_DC SDM status'

@ObjectModel.usageType:{
  serviceQuality: #C,
  sizeCategory: #XL,
  dataClass: #TRANSACTIONAL
}
@AccessControl.personalData.blocking: #REQUIRED
@VDM.private: true
@VDM.viewType: #BASIC
@Metadata.ignorePropagatedAnnotations: true

define view entity p_datacontrollersdmstatus
 as select from           but_dc_link   as link
    inner join             but000        as but   on but.partner = link.partner
    inner join             cvi_cust_link as cust  on cust.partner_guid = but.partner_guid

    left outer to one join mdbpcv_dc     as cv_dc on  cust.customer    = cv_dc.idnumber
                                                  and cv_dc.idtype     = 'D'
                                                  and cv_dc.data_ctrlr = link.data_ctrlr
                                                  and cv_dc.purpose    = link.purpose

{
  key link.partner,
  key cust.customer         as idnumber,
  key cast( 'D' as char1 )  as idtype,
  key link.data_ctrlr,
  key link.purpose,
      link.asgmt_status,
      cast( '00' as char2 ) as sdm_version
}
where
      cv_dc.idnumber    is null
  and link.asgmt_status != 'I'
union select from but_dc_link   as link
  inner join      but000        as but   on but.partner = link.partner
  inner join      cvi_vend_link as vend  on vend.partner_guid = but.partner_guid
  left outer to one join mdbpcv_dc     as cv_dc on  vend.vendor      = cv_dc.idnumber
                                         and cv_dc.idtype     = 'K'
                                         and cv_dc.data_ctrlr = link.data_ctrlr
                                         and cv_dc.purpose    = link.purpose
{
  key link.partner,
  key vend.vendor           as idnumber,
  key cast( 'K' as char1 )  as idtype,
  key link.data_ctrlr,
  key link.purpose,
      link.asgmt_status,
      cast( '00' as char2 ) as sdm_version
}
where
      cv_dc.idnumber    is null
  and link.asgmt_status != 'I'
union select from but_dc_link      as link
  inner join      but000           as but   on but.partner = link.partner
  inner join      cvi_cust_ct_link as cp    on cp.partner_guid = but.partner_guid
  left outer to one join mdbpcv_dc        as cv_dc on  cp.customer_cont = cv_dc.idnumber
                                            and cv_dc.idtype     = 'C'
                                            and cv_dc.data_ctrlr = link.data_ctrlr
                                            and cv_dc.purpose    = link.purpose
{
  key link.partner,
  key cp.customer_cont      as idnumber,
  key cast( 'C' as char1 )  as idtype,
  key link.data_ctrlr,
  key link.purpose,
      link.asgmt_status,
      cast( '00' as char2 ) as sdm_version
}
where
      cv_dc.idnumber    is null
  and link.asgmt_status != 'I'
union select from but_dc_link      as link
  inner join      but000           as but   on but.partner = link.partner
  inner join      cvi_vend_ct_link as cp    on cp.partner_guid = but.partner_guid
  left outer to one join mdbpcv_dc        as cv_dc on  cp.vendor_cont   = cv_dc.idnumber
                                            and cv_dc.idtype     = 'C'
                                            and cv_dc.data_ctrlr = link.data_ctrlr
                                            and cv_dc.purpose    = link.purpose
{
  key link.partner,
  key cp.vendor_cont        as idnumber,
  key cast( 'C' as char1 )  as idtype,
  key link.data_ctrlr,
  key link.purpose,
      link.asgmt_status,
      cast( '00' as char2 ) as sdm_version
}
where
      cv_dc.idnumber    is null
  and link.asgmt_status != 'I'