@AbapCatalog.sqlViewName: 'UPOS_PR_MPVL_UOM'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass:#TRANSACTIONAL
@EndUserText.label: 'Measurement Point Volumetric Data (UOM)'
define view UPOV_PR_MPVL_UOM
as select from upot_int_mpvnqci as mpvl
inner join gho_imrg_std_h as stdh on stdh.bus_key = mpvl.bus_key
and stdh.frequency = 'M' // Line added for N_3390754
inner join gho_iflot_ext as iflot on iflot.tplnr = stdh.netid
inner join upot_int_nobj as dnLabel on dnLabel.tplnr = stdh.netid
inner join upot_int_nobj as mpLabel on mpLabel.tplnr = stdh.netobj
and not(
mpLabel.gho_netobj_type = 'GHO_PN'
or mpLabel.gho_netobj_type = 'GHO_WELL'
or mpLabel.gho_netobj_type = 'GHO_WC'
)
inner join upoc_udm_vtqt_mp as vtqt on vtqt.quant_type = stdh.quantity_type
inner join gho_disposition as dispo on dispo.qty_type = stdh.quantity_type
inner join upots_udm_vcqc as vcqc on vcqc.quant_cat = dispo.qty_category
inner join gho_imrg_ext as imrge on imrge.mdocm = stdh.mdocm
and imrge.measmnt_freq = stdh.frequency
// inner join gho_imptt_ext as impt on impt.measmnt_point = imrge.measmnt_point
left outer join UPOV_STD_VOLUMES as StandardVolume on StandardVolume.BusinessKey = stdh.bus_key
and StandardVolume.Dimension = 'VOLUME'
left outer join UPOV_STD_VOLUMES as StandardEnergy on StandardEnergy.BusinessKey = stdh.bus_key
and StandardEnergy.Dimension = 'ENERGY'
left outer join UPOV_STD_VOLUMES as StandardHvalue on StandardHvalue.BusinessKey = stdh.bus_key
and StandardHvalue.Dimension = 'HVALUE'
left outer join UPOV_STD_VOLUMES as StandardDensity on StandardDensity.BusinessKey = stdh.bus_key
and StandardDensity.Dimension = 'DENSI'
{
key mpvl.mpvl_no as MPVL_NO, //MP volume Number
key mpvl.mpvl_year as MPVL_YEAR, //Measurement point document year
cast(mpLabel.converted_label as oiu_mp_no) as MP_NO, //Measurement point number
vtqt.vol_type as VT_CD, //Volume Type Code
cast('A' as oiu_vc_cd) as VC_CD, //Volume class code
stdh.trnsp_no as TRNSP_NO, //Transporter number
stdh.trnsp_ref_no as TRNSP_REF_NO, //Transporter Reference Number
stdh.alloc_material as MATNR, //Material Number
cast(stdh.frequency as oiu_freq_cd) as FREQ_CD, //Frequency
cast(dnLabel.converted_label as oiu_dn_no) as DN_NO, //Delivery network number
cast(' ' as oiu_trnsp_mth_cd) as TRNSP_MTH_CD, //Transportion method code
cast(' ' as oiu_ct_no) as CT_NO, //Contract Number
cast(stdh.tkt_no as oiu_tkt_no) as TKT_NO, //Ticket number
cast('0' as oiu_tkt_timestmp) as TKT_TIMESTAMP, //Ticket timestamp
cast(
case stdh.frequency
when 'Y' then concat(concat(left(tstmp_to_dats(stdh.prod_timestamp,iflot.gho_pn_tz,$session.client,'NULL'),4),'12'),'31')
when 'M'
then DATS_ADD_DAYS(DATS_ADD_MONTHS(cast( CONCAT(LEFT(tstmp_to_dats(stdh.prod_timestamp,iflot.gho_pn_tz,$session.client,'NULL'), 6), '01') as abap.dats),1, 'UNCHANGED'),-1, 'UNCHANGED')
else
tstmp_to_dats(stdh.prod_timestamp,iflot.gho_pn_tz,$session.client,'NULL')
end as oiu_prd_dt) as PRD_DT, //Production date
stdh.created_tst as ENT_TIMESTAMP, //Entry timestamp
imrge.idate as EFF_FROM_DT, //Effective from date
cast(
case stdh.frequency
when 'D' then imrge.idate
when 'Y' then concat(concat(left(imrge.idate,4),'12'),'31')
when 'M' then
DATS_ADD_DAYS(DATS_ADD_MONTHS(cast( CONCAT(LEFT(imrge.idate, 6), '01') as abap.dats),1, 'UNCHANGED'),-1, 'UNCHANGED')
else imrge.idate
end as oiu_eff_to_dt) as EFF_TO_DT, //Effective To Date
dats_tims_to_tstmp(imrge.idate, cast('000000' as abap.tims),
'UTC', $session.client, 'NULL') as METER_START, //Meter start time
dats_tims_to_tstmp(cast(
case stdh.frequency
when 'D' then imrge.idate
when 'Y' then concat(concat(left(imrge.idate,4),'12'),'31')
when 'M' then
DATS_ADD_DAYS(DATS_ADD_MONTHS(cast( CONCAT(LEFT(imrge.idate, 6), '01') as abap.dats),1, 'UNCHANGED'),-1, 'UNCHANGED')
else imrge.idate
end as abap.dats), cast('235959' as abap.tims),
'UTC', $session.client, 'NULL') as METER_STOP, //Meter stop time
StandardVolume.ConversionGroup as CONV_GRP, //Conversion Group (Oil, Natural Gas,..)
cast(' ' as oiu_tkbtry_mp_no) as TKBTRY_MP_NO, //Tank battery measurement point number
cast('1' as oiu_vs_cd) as VS_CD, //Volume Source Code
vcqc.vol_cat as VL_CAT_CD, //Volume Category Code
cast(' ' as oiu_ld_oil_dn_no) as LD_OIL_DN_NO, //Load oil delivery network
cast(' ' as oiu_ld_oil_wl_no) as LD_OIL_WL_NO, //Load oil well number
cast(' ' as oiu_ld_oil_wc_no) as LD_OIL_WC_NO, //Load oil well completion
cast(' ' as oiu_ld_oil_mp_no) as LD_OIL_MP_NO, //Load oil measurement point
cast(StandardVolume.StdVolume as abap.quan( 13, 3 )) as STD_VOL, //Standard volume
StandardVolume.Unit as STD_VOL_U, //Standard volume unit
cast(StandardEnergy.StdVolume as abap.quan( 13, 3 )) as ENERGY, //Energy quantity
StandardEnergy.Unit as ENERGY_U, //Energy quantity unit
cast('0' as oiu_mol_volume) as MOL_VOLUME, //Gas mol volume
cast(StandardHvalue.StdVolume as abap.quan( 13, 3 )) as HEAT_VAL, //Heating value
StandardHvalue.Unit as HEAT_VAL_U, //Heating Value Unit
cast(StandardDensity.StdVolume as oiu_bdich) as BASE_DENSITY, //Oil/gas density at standard/base conditions
cast(StandardDensity.Unit as oiu_bdneh) as BASE_DENSITY_U, //Unit for densities at standard/base conditions
cast(' ' as oiu_dntyp) as DENTYP, //Density Type
cast(' ' as oiu_rec_type_cd) as REC_TYPE_CD, //Record type code
cast(' ' as oiu_edi_sts_cd) as EDI_STS_CD, //EDI status code
cast(mpvl.me_docnr as oiu_me_docnr) as ME_DOCNR, //Meas. document number
cast(mpvl.me_docyr as oiu_me_docyr) as ME_DOCYR, //Meas. document year
cast(' ' as oiu_orig_mp_no) as ORIG_MP_NO, //Original measurement point number
cast(
case stdh.frequency
when 'D' then imrge.idate
when 'Y' then concat(concat(left(imrge.idate,4),'12'),'31')
when 'M' then
DATS_ADD_DAYS(DATS_ADD_MONTHS(cast( CONCAT(LEFT(imrge.idate, 6), '01') as abap.dats),1, 'UNCHANGED'),-1, 'UNCHANGED')
else imrge.idate
end as oiu_inv_dt) as INVT_DT, //Inventory date
stdh.created_by as OIU_CRUSER, //Name of Person who Created the Object
stdh.created_tst as OIU_TIMESTAMP, //UTC Time Stamp in Short Form (YYYYMMDDhhmmss)
cast(' ' as oiu_ld_oil_wo_recov) as LD_OIL_WO_RECOV //Load Oil Transfer without Recovery
}
/*+[internal] {
"BASEINFO":
{
"FROM":
[
"UPOV_STD_VOLUMES",
"GHO_DISPOSITION",
"GHO_IFLOT_EXT",
"GHO_IMRG_EXT",
"GHO_IMRG_STD_H",
"UPOC_UDM_VTQT_MP",
"UPOTS_UDM_VCQC",
"UPOT_INT_MPVNQCI",
"UPOT_INT_NOBJ"
],
"ASSOCIATED":
[],
"BASE":
[],
"ANNO_REF":
[],
"SCALAR_FUNCTION":
[],
"VERSION":0,
"ANNOREF_EVALUATION_ERROR":""
}
}*/