@AbapCatalog.sqlViewName: 'UPOS_PR_WC_UOM'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm:#SESSION_VARIABLE
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.dataClass: #MASTER
@EndUserText.label: 'UOM: Well Completion'
define view UPOV_PR_WC_UOM
as select from gho_iflot_ext as functionalloc
inner join upot_int_nobj as WCLabel on WCLabel.tplnr = functionalloc.tplnr
inner join upot_int_nobj as WL_Label on WL_Label.tplnr = functionalloc.gho_wc_ref_id
left outer join iflot as _funcloc on _funcloc.tplnr = functionalloc.tplnr
left outer join iflotx as _functionalloctext on _functionalloctext.tplnr = functionalloc.tplnr
and _functionalloctext.spras = $session.system_language
left outer join iloa as objloc on objloc.tplnr = functionalloc.tplnr
and objloc.owner = '2' // Functional Location
left outer join geoloc as geolocation on geolocation.addrnumber = objloc.adrnr
left outer join adrc as addressdata on addressdata.addrnumber = objloc.adrnr
left outer join upoc_udm_region as primary_loc on primary_loc.land1 = addressdata.country
and primary_loc.bland = addressdata.region
left outer join upot_int_entity as field on field.gho_pe_type = 'FIELD'
and field.gho_pe_id = functionalloc.gho_field
left outer join upot_int_entity as reservoir on reservoir.gho_pe_type = 'RESERVOIR'
and reservoir.gho_pe_id = functionalloc.gho_reservoir
left outer join upot_int_entity as platform on platform.gho_pe_type = 'PLATFORM'
and platform.gho_pe_id = functionalloc.gho_platform
left outer join oiu_cm_param as common_pra_param on common_pra_param.ep_active = 'X' // for WC Hierarchy
left outer join UPOV_NETOBJ_WITH_PN as dn on dn.NetObject = functionalloc.tplnr
left outer join gho_uom_rule_h as dn_rule on dn_rule.net_id = dn.ProductionNetwork
and dn_rule.valid_from <= tstmp_current_utctimestamp()
and dn_rule.valid_to >= tstmp_current_utctimestamp()
{
key cast(WL_Label.converted_label as oiu_wl_no) as WL_NO, // Well id
key cast(WCLabel.converted_label as oiu_wc_no) as WC_NO, // Well completion
functionalloc.api_wc_no as API_WC_NO, // API well completion number
cast(_functionalloctext.pltxt as oiu_wc_nm_conv) as NAME, // Well completion name
cast(_functionalloctext.pltxu as oiu_shtxt) as SHTXT, // Description
cast('NA1' as oiu_me_uomgr) as UOM_GRP, // UOM group - to be decided
//cast(dn_rule.uom_group as oiu_me_uomgr) as UOM_GRP, // UOM group - to be decided
cast(' ' as oiu_dev_pgm_no) as DEV_PGM_NO, // Development program number - out of scope
abs(cast(geolocation.latitude as oiu_lat_no)) as LAT_NO, // Latitude number
case
when geolocation.latitude < 0
then cast('S' as oiu_lat_dir_cd)
when geolocation.latitude > 0
then cast('N' as oiu_lat_dir_cd)
else cast('U' as oiu_lat_dir_cd) end as LAT_DIR_CD, // Latitude direction code
//cast(' ' as oiu_lat_dir_cd) as LAT_DIR_CD, // Latitude direction code -out of scope
abs(cast(geolocation.longitude as oiu_lng_no)) as LNG_NO, // Longitude
case
when geolocation.longitude < 0
then cast('W' as oiu_lat_dir_cd)
when geolocation.longitude > 0
then cast('E' as oiu_lng_dir_cd)
else cast('U' as oiu_lng_dir_cd) end as LNG_DIR_CD, // Latitude direction code
//cast(' ' as oiu_lng_dir_cd) as LNG_DIR_CD, // Longitude Direction Code - out of scope
functionalloc.gho_perf_uom as ELEV_DPH_U, // Elevation/Depth Unit
cast(0 as oiu_plg_dpth) as PLG_BCK_DPTH, // Plug hole depth - out of scope
cast(0 as oiu_datum_dpth) as DATUM_DPTH, // Datum depth
cast(0 as oiu_kb_elev) as KB_ELEV, // KB elevation
cast(' ' as oiu_base_bhp_u) as BASE_BHP_U, // Base bottom hole pressure- out of scope
cast(0 as oiu_base_bhp) as BASE_BHP, // Base bottom hole pressure (BHP)
addressdata.country as CNTRY_CD, // Country
cast(primary_loc.pri_geo_loc as oiu_pri_geo_loc) as PRI_GEO_LOC, // Primary geographical location (PRI geo. loc.)
functionalloc.sec_geo_loc as SEC_GEO_LOC, // Secondary geographic location (UDM)
cast(reservoir.converted_label as oiu_resrvid) as RESRV, // Reservoir ID
cast(' ' as oiu_resznid) as RESZN, // Reservoir zone - out of scope
cast(field.converted_label as oiu_fieldid) as FIELD, // Field ID
cast(platform.converted_label as oiu_pf_no) as PF_NO, // Platform number
cast(' ' as oiu_wl_pur_cd) as PUR_CD, // Well purpose code - out of scope
cast(' ' as oiu_le_wl_fl) as LSE_LE_WL_FL, // Lease line well flag - out of scope
cast(functionalloc.gho_perf_from as abap.quan( 17,3)) as BEG_PRF_DPH, // Begin Perforation Depth
cast(functionalloc.gho_perf_to as abap.quan( 17,3)) as END_PRF_DPH, // End Performation Depth
cast(functionalloc.gho_wc_name_alt as oiu_alt_wc_key) as ALT_WC_KEY, // Alternate WC Key
functionalloc.lcl_wl_no as LCL_WL_NO, // Local Well Number to be added as part of UDM
common_pra_param.hier_wc as WC_SET, // Well Completion Hierarchy Group
cast(' ' as oiupr_wc_docnr) as DOCNR, // well completion document number- to be added as part of UDM
cast(' ' as oiu_doc_year) as DOCYR, // Document year- to be added as part of UDM
functionalloc.or1_or_lvl_1_no as OR1_OR_LVL_1_NO, // Org. Level 1 - Company - to be added as part of UDM
functionalloc.or2_or_lvl_2_no as OR2_OR_LVL_2_NO, // Second-highest organization level- to be added as part of UDM
functionalloc.or3_or_lvl_3_no as OR3_OR_LVL_3_NO, // Third-highest organization level- to be added as part of UDM
functionalloc.or4_or_lvl_4_no as OR4_OR_LVL_4_NO, // Fourth-highest organization level - to be added as part of UDM
_funcloc.ernam as OIU_CRUSER, // Person Who Created Object
dats_tims_to_tstmp(_funcloc.erdat,
cast('000000' as abap.tims),
'UTC',
$session.client,
'NULL') as OIU_TIMESTAMP,
cast(0 as oiu_pack_dpth) as PCK_DPTH, // Packer Depth - to be added as part of UDM
functionalloc.api_sidt as API_SIDT,
cast(' ' as /pra/cbm_indicator) as CBM_INDICATOR // Coal Bed Methane Indicator- to be added as part of UDM
}
where
functionalloc.gho_netobj_type = 'GHO_WC'
union
//Commingled WC details
select distinct from UPOV_COMMINGLED_WC as commingled
inner join upot_int_nobj as WL_Label on WL_Label.converted_label = commingled.wl_no
inner join gho_iflot_ext as functionalloc on functionalloc.tplnr = WL_Label.tplnr
inner join upot_int_v_get_org_level_data as org_data on org_data.tplnr = functionalloc.tplnr
left outer join iflot as _funcloc on _funcloc.tplnr = functionalloc.tplnr
left outer join oiu_cm_param as common_pra_param on common_pra_param.ep_active = 'X' // for WC Hierarchy
left outer join UPOV_NETOBJ_WITH_PN as dn on dn.NetObject = functionalloc.tplnr
left outer join gho_uom_rule_h as dn_rule on dn_rule.net_id = dn.ProductionNetwork
and dn_rule.valid_from <= tstmp_current_utctimestamp()
and dn_rule.valid_to >= tstmp_current_utctimestamp()
{
key cast(WL_Label.converted_label as oiu_wl_no) as WL_NO, // Well id
key cast(commingled.wc_no as oiu_wc_no) as WC_NO, // Well completion
functionalloc.api_wc_no as API_WC_NO, // API well completion number
cast('Commingled WC - Dummy' as oiu_wc_nm_conv) as NAME, // Well completion name
cast('Commingled WC - Dummy' as oiu_shtxt) as SHTXT, // Description
cast('NA1' as oiu_me_uomgr) as UOM_GRP, // UOM group - to be decided
//cast(dn_rule.uom_group as oiu_me_uomgr) as UOM_GRP, // UOM group - to be decided
cast(' ' as oiu_dev_pgm_no) as DEV_PGM_NO, // Development program number - out of scope
cast ('0'as oiu_lat_no) as LAT_NO, // Latitude number
cast(' ' as oiu_lat_dir_cd) as LAT_DIR_CD, // Latitude direction code -out of scope
cast('0' as oiu_lng_no) as LNG_NO, // Longitude
cast(' ' as oiu_lng_dir_cd) as LNG_DIR_CD, // Longitude Direction Code - out of scope
functionalloc.gho_perf_uom as ELEV_DPH_U, // Elevation/Depth Unit
cast(0 as oiu_plg_dpth) as PLG_BCK_DPTH, // Plug hole depth - out of scope
cast(0 as oiu_datum_dpth) as DATUM_DPTH, // Datum depth
cast(0 as oiu_kb_elev) as KB_ELEV, // KB elevation
cast(' ' as oiu_base_bhp_u) as BASE_BHP_U, // Base bottom hole pressure- out of scope
cast(0 as oiu_base_bhp) as BASE_BHP, // Base bottom hole pressure (BHP)
'' as CNTRY_CD, // Country
cast('' as oiu_pri_geo_loc) as PRI_GEO_LOC, // Primary geographical location (PRI geo. loc.)
functionalloc.sec_geo_loc as SEC_GEO_LOC, // Secondary geographic location (UDM)
cast('' as oiu_resrvid) as RESRV, // Reservoir ID
cast(' ' as oiu_resznid) as RESZN, // Reservoir zone - out of scope
cast('' as oiu_fieldid) as FIELD, // Field ID
cast('' as oiu_pf_no) as PF_NO, // Platform number
cast(' ' as oiu_wl_pur_cd) as PUR_CD, // Well purpose code - out of scope
cast(' ' as oiu_le_wl_fl) as LSE_LE_WL_FL, // Lease line well flag - out of scope
cast(functionalloc.gho_perf_from as abap.quan( 17,3)) as BEG_PRF_DPH, // Begin Perforation Depth
cast(functionalloc.gho_perf_to as abap.quan( 17,3)) as END_PRF_DPH, // End Performation Depth
cast(functionalloc.gho_wc_name_alt as oiu_alt_wc_key) as ALT_WC_KEY, // Alternate WC Key
functionalloc.lcl_wl_no as LCL_WL_NO, // Local Well Number to be added as part of UDM
common_pra_param.hier_wc as WC_SET, // Well Completion Hierarchy Group
cast(' ' as oiupr_wc_docnr) as DOCNR, // well completion document number- to be added as part of UDM
cast(' ' as oiu_doc_year) as DOCYR, // Document year- to be added as part of UDM
org_data.or1_or_lvl_1_no as OR1_OR_LVL_1_NO, // Org. Level 1 - Company - to be added as part of UDM
org_data.or2_or_lvl_2_no as OR2_OR_LVL_2_NO, // Second-highest organization level- to be added as part of UDM
org_data.or3_or_lvl_3_no as OR3_OR_LVL_3_NO, // Third-highest organization level- to be added as part of UDM
org_data.or4_or_lvl_4_no as OR4_OR_LVL_4_NO, // Fourth-highest organization level - to be added as part of UDM
_funcloc.ernam as OIU_CRUSER, // Person Who Created Object
dats_tims_to_tstmp(_funcloc.erdat,
cast('000000' as abap.tims),
'UTC',
$session.client,
'NULL') as OIU_TIMESTAMP,
cast(0 as oiu_pack_dpth) as PCK_DPTH, // Packer Depth - to be added as part of UDM
functionalloc.api_sidt as API_SIDT,
cast(' ' as /pra/cbm_indicator) as CBM_INDICATOR // Coal Bed Methane Indicator- to be added as part of UDM
}
where
functionalloc.gho_netobj_type = 'GHO_WELL'
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"UPOT_INT_V_GET_ORG_LEVEL_DATA",
"UPOV_COMMINGLED_WC",
"UPOV_NETOBJ_WITH_PN",
"ADRC",
"GEOLOC",
"GHO_IFLOT_EXT",
"GHO_UOM_RULE_H",
"IFLOT",
"IFLOTX",
"ILOA",
"OIU_CM_PARAM",
"UPOC_UDM_REGION",
"UPOT_INT_ENTITY",
"UPOT_INT_NOBJ"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/