Ehenv_Cdef_Amns_All_P

@AbapCatalog.sqlViewName: 'EHENVV_AM_CDEF'
@ClientHandling.algorithm: #SESSION_VARIABLE
@AccessControl.authorizationCheck: #NOT_REQUIRED
//@AbapCatalog.compiler.compareFilter: true

@EndUserText.label: 'All CDEFs for Amounts with parameter'
//CDS View Performance annotation

@ObjectModel.usageType:{ serviceQuality: #P,      
                         sizeCategory:  #L,         // < 10.000.000

                         dataClass: #MIXED }

/* Get all calculation definition keys for an amount */
define view Ehenv_Cdef_Amns_All_P
  with parameters
    p_amount_key : /bobf/conf_key
  as select from ehfndd_amns_root as amns
    inner join   ehenvd_sdef_samp as samp  on  amns.source_root_key_ref = samp.sampling_key_ref
                                           and amns.sourcetype          = '05' // finalized sampling

    inner join   ehenvd_sdef_head as head  on  samp.parent_key        = head.parent_key
                                           and amns.amountcategory    = head.chem_property
                                           and amns.subj_comp_type    = head.analyte_type
                                           and amns.subj_comp_key_ref = head.analyte_key
    inner join   ehenvd_cdef_inpt as input on head.db_key = input.data_source_key_ref
    inner join   ehenvd_scen_root as scen  on  input.scen_root_key_ref =  scen.db_key
                                           and scen.date_activated     <> 0
    inner join   ehenvd_cdef_root as cdef  on  input.parent_key =  cdef.db_key
                                           and cdef.exec_option <> '03' //exclude no calc calculations

    inner join   ehenvd_cdef_resh as resh  on  resh.parent_key  = cdef.db_key
                                           and resh.result_type = '00' //final result types

    inner join   ehenvd_cdef_res  as res   on  res.parent_key  = resh.db_key  

{
  amns.db_key                     as amountkey,
  amns.valuetimestamp             as valuetimestamp,
  amns.status                     as amountstatus,
  cdef.user_id_cr                 as calcuser,
  cdef.exec_option                as execoption,
  input.parent_key                as calckey,
  input.usage_period              as inputperiod,
  scen.db_key                     as scenkey,
  scen.status                     as scenstatus,
  scen.valid_from                 as scenvalidfrom,
  scen.valid_to                   as scenvalidto,
  scen.loc_root_key_ref           as locationkey,
  resh.usage_period               as usageperiod,
  resh.db_key                     as calcreshkey,
  resh.subject_type_code          as subjecttype,
  resh.subject_root_key_ref       as subjectkey,
  resh.unit                       as unit,
  head.chem_property              as chemprop,
  head.analyte_type               as analytetype,
  head.analyte_key                as analytekey,
  head.db_key                     as headkey,
  ''                              as timestamp,
  resh.result_type                as resulttype,
  cdef.exec_option_last_activ     as exec_option_last_activ,
  input.cron_pattern as cron_pattern,
  input.time_interval_pattern as time_interval_pattern,
  res.dirty_from_tstmp            as dirty_from_tstmp
}
where
  amns.db_key = $parameters.p_amount_key
  and amns.missing_ind <> 'X'

union select from ehfndd_amns_root as amns2
  inner join      ehenvd_cdef_inpt as input2 on amns2.source_root_key_ref = input2.data_source_key_ref
                                             and(
                                               amns2.sourcetype    = '02' // Collection

                                               or amns2.sourcetype = '04' // Calculation

                                               or amns2.sourcetype = '06' // Aggregated Data

                                             )                            
  inner join      ehenvd_scen_root as scen2  on  input2.scen_root_key_ref =  scen2.db_key
                                             and scen2.date_activated     <> 0
  inner join      ehenvd_cdef_root as cdef2  on  input2.parent_key =  cdef2.db_key
                                             and cdef2.exec_option <> '03' //exclude no calc calculations

  inner join      ehenvd_cdef_resh as resh2  on  resh2.parent_key  = cdef2.db_key
                                             and resh2.result_type = '00'
  inner join      ehenvd_cdef_res  as res2   on  res2.parent_key  = resh2.db_key                                               
{
  amns2.db_key                     as amountkey,
  amns2.valuetimestamp             as valuetimestamp,
  amns2.status                     as amountstatus,
  cdef2.user_id_cr                 as calcuser,
  cdef2.exec_option                as execoption,
  input2.parent_key                as calckey,
  input2.usage_period              as inputperiod,
  scen2.db_key                     as scenkey,
  scen2.status                     as scenstatus,
  scen2.valid_from                 as scenvalidfrom,
  scen2.valid_to                   as scenvalidto,
  scen2.loc_root_key_ref           as locationkey,
  resh2.usage_period               as usageperiod,
  resh2.db_key                     as calcreshkey,
  resh2.subject_type_code          as subjecttype,
  resh2.subject_root_key_ref       as subjectkey,
  resh2.unit                       as unit,
  ''                               as chemprop,
  ''                               as analytetype,
  hextobin('00000000000000000000000000000000') as analytekey,
  amns2.source_root_key_ref        as headkey, // just to comply with other part of the union

  ''                               as timestamp,
  resh2.result_type                as resulttype,
  cdef2.exec_option_last_activ     as exec_option_last_activ,
  input2.cron_pattern as cron_pattern,
  input2.time_interval_pattern as time_interval_pattern,
  res2.dirty_from_tstmp            as dirty_from_tstmp
}
where
  amns2.db_key = $parameters.p_amount_key 
  and amns2.missing_ind <> 'X'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"EHENVD_CDEF_INPT",
"EHENVD_CDEF_RES",
"EHENVD_CDEF_RESH",
"EHENVD_CDEF_ROOT",
"EHENVD_SCEN_ROOT",
"EHENVD_SDEF_HEAD",
"EHENVD_SDEF_SAMP",
"EHFNDD_AMNS_ROOT"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/