UPOV_COMMINGLED_WC

@AbapCatalog.sqlViewName: 'UPOS_CMNGLD_WC'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm:#SESSION_VARIABLE
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass: #MASTER
@EndUserText.label: 'Commingled WC'
define view UPOV_COMMINGLED_WC
  as select from    UPOV_PR_WCDHH_PRA  as praCommingled
    inner join      upot_mig_comm_wc   as migCommingled on  migCommingled.wl_no = praCommingled.wl_no
                                                        and migCommingled.wc_no = praCommingled.wc_no

    left outer join UPOV_MIGRATED_WELL as migWL         on  migWL.wl_no        = praCommingled.wl_no
                                                        and migWL.migr_step_id = '01'
{
  praCommingled.wl_no,
  praCommingled.wc_no,
  praCommingled.majpd_cd,
  praCommingled.eff_from_dt,
  praCommingled.eff_to_dt
}
where
  migWL.wl_no is not null

union

select from       gho_com_wc_h       as downhole_cm_well_cm
  inner join      upot_int_nobj      as wl            on wl.tplnr = downhole_cm_well_cm.objid
  left outer join upot_mig_comm_wc   as migCommingled on migCommingled.wl_no = wl.converted_label

{
  cast(wl.converted_label as oiu_wl_no)               as  WL_NO, // Well ID number

  cast('CMGLD' as oiu_wc_no)                          as  WC_NO, // Well com ID number


  // For now only 3 type of medium has been mapped

  case
  when downhole_cm_well_cm.medium = 'OIL' then '1'
  when downhole_cm_well_cm.medium = 'GAS' then '2'
  when downhole_cm_well_cm.medium = 'WATER' then '5'
  else ''
  end                                                 as  MAJPD_CD, // Major product code


  tstmp_to_dats(downhole_cm_well_cm.valid_from,
          'UTC',
          $session.client,'NULL')                     as  EFF_FROM_DT, // Effective from date


  tstmp_to_dats(downhole_cm_well_cm.valid_to,
         'UTC',
         $session.client,'NULL')                      as  EFF_TO_DT // Effective from date

}
where
  migCommingled.wl_no is null
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"UPOV_MIGRATED_WELL",
"UPOV_PR_WCDHH_PRA",
"GHO_COM_WC_H",
"UPOT_INT_NOBJ",
"UPOT_MIG_COMM_WC"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/