UPO_INT_V_ALLOC_DATA

@AbapCatalog.sqlViewName: 'UPOS_ALLOC_DATA'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@EndUserText.label: 'UOM Alloc Data for a Run Id'
define view UPO_INT_V_ALLOC_DATA
  with parameters P_ALLOC_RUN_ID : gho_alloc_run_id
  as select from    gho_allocres_h   as header_res
    inner join      gho_allocres_i   as item_res     on item_res.alloc_run_id   = header_res.alloc_run_id
                                                     and(
                                                       item_res.theo_act_ind    = 'A'
                                                       or item_res.theo_act_ind = 'B'
                                                       or item_res.theo_act_ind = 'R'
                                                       or item_res.theo_act_ind = 'S'
                                                       or item_res.theo_act_ind = 'L'
                                                       or item_res.theo_act_ind = 'D'
                                                     )
    left outer join gho_allocres_i   as theo         on  theo.alloc_run_id   = item_res.alloc_run_id
                                                     and theo.origin_node_id = item_res.dest_node_id
                                                     and theo.theo_act_ind   = 'O'
    left outer join gho_allocres_i   as theo_g       on  theo_g.alloc_run_id   = item_res.alloc_run_id
                                                     and theo_g.origin_node_id = item_res.dest_node_id
                                                     and theo_g.theo_act_ind   = 'G'
    left outer join gho_allocres_i   as ReceiptMeter on  ReceiptMeter.alloc_run_id   = item_res.alloc_run_id
                                                     and ReceiptMeter.origin_node_id = item_res.origin_node_id
                                                     and ReceiptMeter.theo_act_ind   = 'C'
    inner join      gho_uom_rule_h   as uom_hrule    on  uom_hrule.net_id     =  header_res.netid
                                                     and uom_hrule.medium     =  header_res.medium
                                                     and uom_hrule.valid_from <= dats_tims_to_tstmp(
      header_res.prod_date, header_res.prod_time, 'UTC', $session.client, 'NULL'
    )
                                                     and uom_hrule.valid_to   >= dats_tims_to_tstmp(
      header_res.prod_date, header_res.prod_time, 'UTC', $session.client, 'NULL'
    )
    inner join      gho_iflot_ext    as iflot        on iflot.tplnr = header_res.netid
    inner join      gho_disposition  as dispo        on dispo.qty_type = item_res.qty_type
    inner join      upots_udm_vcqc   as vcqc         on vcqc.quant_cat = dispo.qty_category
    left outer join upoc_udm_vtqt_mp as vtqt         on vtqt.quant_type = item_res.qty_type
    inner join      gho_uom_rule_i   as uom_irule    on  uom_irule.uom_rule_id = uom_hrule.uom_rule_id
                                                     and uom_irule.uom         = item_res.unit
    inner join      gho_iflot_ext    as wlwc         on wlwc.tplnr = item_res.dest_node_id
    left outer join upot_int_nobj    as mpLabel      on mpLabel.tplnr = item_res.origin_node_id
    left outer join upot_int_nobj    as wlLabel      on wlLabel.tplnr = wlwc.gho_wc_ref_id
    left outer join upot_int_nobj    as wcLabel      on wcLabel.tplnr = wlwc.tplnr
    left outer join upot_int_nobj    as dnLabel      on dnLabel.tplnr = header_res.netid
    left outer join upot_int_nobj    as xwlLabel     on xwlLabel.tplnr = item_res.xref_wl_no
    left outer join upot_int_nobj    as xwcLabel     on xwcLabel.tplnr = item_res.xref_wc_no
{
  item_res.alloc_run_id,
  item_res.wcdvlh_no,
  header_res.netid,
  header_res.medium,
  item_res.origin_node_id,
  item_res.dest_node_id,
  mpLabel.converted_label                                                                                                                          as origin_mp_no,
  wlwc.gho_wc_ref_id                                                                                                                               as well_floc,
  wlLabel.converted_label                                                                                                                          as wl_no, //well id number

  wcLabel.converted_label                                                                                                                          as wc_no, //well completion number

  dnLabel.converted_label                                                                                                                          as dn_no, //delivery network number

  tstmp_to_dats(item_res.eff_from_dt,iflot.gho_pn_tz,$session.client,'NULL')                                                                       as eff_from_dt,
  tstmp_to_dats(item_res.eff_to_dt,iflot.gho_pn_tz,$session.client,'NULL')                                                                         as eff_to_dt,
  tstmp_to_dats(dats_tims_to_tstmp(header_res.prod_date,header_res.prod_time,'UTC',$session.client,'NULL'),iflot.gho_pn_tz,$session.client,'NULL') as prod_date,
  dats_tims_to_tstmp(header_res.prod_date,header_res.prod_time,'UTC',$session.client,'NULL')                                                       as prod_tstmp,
  uom_irule.dimension                                                                                                                              as dimension,
  header_res.frequency,
  item_res.material,
  item_res.matnr,
  item_res.qty_type,
  item_res.qty_category,
  item_res.prd_hrs                                                                                                                                 as prd_hrs,
  vtqt.vol_type,
  vcqc.vol_cat,
  uom_hrule.conv_grp                                                                                                                               as CONV_GRP, //Conversion Group (Oil, Natural Gas,..)

  item_res.theo_act_ind,
  item_res.unit,
  item_res.volume                                                                                                                                  as std_vol,
  theo.unit                                                                                                                                        as theo_unit,
  theo.volume                                                                                                                                      as theo_val,
  theo_g.volume                                                                                                                                    as theo_g_vol,
  ReceiptMeter.volume                                                                                                                              as glg_vol,
  ReceiptMeter.theo_act_ind                                                                                                                        as theo_g_ind,
  item_res.tkt_no                                                                                                                                  as TKT_NO, //Ticket number

  item_res.tkt_timestamp                                                                                                                           as TKT_TIMESTAMP, //Ticket Timestamp (Date)

  item_res.trnsp_no,
  item_res.trnsp_ref_no,
  item_res.invt_dt                                                                                                                                 as INVT_DT, //Inventory date

  xwlLabel.tplnr                                                                                                                                   as xref_wl_no,
  xwcLabel.tplnr                                                                                                                                   as xref_wc_no,
  item_res.orig_no                                                                                                                                 as ORIG_NO, //Original WCDVL No (Well Completion Daily Volume)

  item_res.orig_doc_year                                                                                                                           as ORIG_DOC_YEAR, //Fiscal Year

  case
   when header_res.changed_by is null
  then
  header_res.created_by
  else header_res.changed_by end                                                                                                                   as created_by,
  case
  when header_res.changed_tst is null
  then header_res.created_tst
  else header_res.changed_tst end                                                                                                                  as created_tst,
  iflot.gho_pn_tz
}
where
       header_res.alloc_run_id = :P_ALLOC_RUN_ID
  and  header_res.frequency    = 'M'
  and(
       header_res.alloc_status = '01'
    or header_res.alloc_status = '04'
  )
  and  header_res.final_ind    = 'X'
  and  wlwc.gho_netobj_type != 'GHO_METER'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"GHO_ALLOCRES_H",
"GHO_ALLOCRES_I",
"GHO_DISPOSITION",
"GHO_IFLOT_EXT",
"GHO_UOM_RULE_H",
"GHO_UOM_RULE_I",
"UPOC_UDM_VTQT_MP",
"UPOTS_UDM_VCQC",
"UPOT_INT_NOBJ"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/