@AbapCatalog.sqlViewName: 'UPOSPRNWRBA'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@ObjectModel.usageType.serviceQuality: #X
@ObjectModel.usageType.sizeCategory: #L
@ObjectModel.usageType.dataClass: #TRANSACTIONAL
@EndUserText.label: 'Event Detail Ref - Before/After data'
define view UPOV_PPN_NETRULE_BA
as select from gho_ppa_request as ppa_request
left outer join upot_int_ppnuuid as ppn_ids on ppn_ids.ppa_req_id = ppa_request.ppa_req_id
// to get the object type needed for finding labels
left outer join upov_pr_processtype_fieldlist as ppnlabels on ppnlabels.PlanningNr = ppa_request.planchngnr
and ppnlabels.Seqnr <> '000'
left outer join UPOV_NETOBJS as funclocdn on funclocdn.tplnr = ppa_request.netid
left outer join iflotx as funclocdn_desc on funclocdn_desc.tplnr = ppa_request.netid
and funclocdn_desc.spras = $session.system_language
left outer join gho_frequency as frequency on frequency.period_unit = 'MON'
left outer join gho_frequency_t as frequency_text on frequency_text.frequency_code = frequency.frequency_code
and frequency_text.language_id = $session.system_language
left outer join pcdpos as changeitems on changeitems.changenr = ppa_request.planchngnr
and changeitems.fname = ppnlabels.fieldname
left outer join gho_meas_cat_t as meascatdescold on meascatdescold.gho_meas_cat_id = changeitems.value_old
and meascatdescold.spras = $session.system_language
and changeitems.fname = 'GHO_MEAS_CAT_ID'
left outer join gho_meas_cat_t as meascatdescnew on meascatdescnew.gho_meas_cat_id = changeitems.value_new
and meascatdescnew.spras = $session.system_language
and changeitems.fname = 'GHO_MEAS_CAT_ID'
{
key ppn_ids.event_uuid as EVENT_UUID, //PPN Trigger Event UUID
key ppnlabels.Seqnr as seq_no, //Sequence number
cast('0000' as abap.numc(4)) as GRP, //Group (Production)
cast(ppnlabels.FieldText as abap.char( 25 )) as DTL_LAB_NM, // Field label/name
//Old values
case
when ppnlabels.fieldname = 'NET_ID' then funclocdn.strno
when ppnlabels.fieldname = 'FREQUENCY' then frequency.frequency_code
when ppnlabels.fieldname = 'VALID_FROM' then
tstmp_to_dats( ppa_request.change_start_period,
'UTC',
$session.client,
'NULL')
when ppnlabels.fieldname = 'MEDIUM' then ppa_request.medium
when ppnlabels.fieldname = 'ALLOC_BASIS'
or ppnlabels.fieldname = 'TCM'
or ppnlabels.fieldname = 'ALLOCATE_FLAG'
or ppnlabels.fieldname = 'AUTO_FLOW'
or ppnlabels.fieldname = 'DECIMAL_RANGE'
or ppnlabels.fieldname = 'DIMENSION'
or ppnlabels.fieldname = 'ROUNDING'
or ppnlabels.fieldname = 'GHO_MEAS_TYPE'
or ppnlabels.fieldname = 'GHO_MEAS_CAT_ID'
or ppnlabels.fieldname = 'GHO_DEFAULT_CAT'
then changeitems.value_old
else ''
end as DTL_OLD_CD, //Code prior to change
//Field Description - Old values
case
when ppnlabels.fieldname = 'NET_ID' then cast(funclocdn_desc.pltxt as abap.char(35))
when ppnlabels.fieldname = 'FREQUENCY' then cast(frequency_text.frequency_desc as abap.char( 35 ))
when ppnlabels.fieldname = 'TCM' then
case
when changeitems.value_old = '01' then 'Latest Test'
when changeitems.value_old = '02' then 'Average Test'
when changeitems.value_old = '03' then 'Weighted Average Test'
when changeitems.value_old = '04' then 'Average for the month'
when changeitems.value_old = '05' then 'Metered Volume'
when changeitems.value_old = '06' then 'Single Well Completion'
when changeitems.value_old = '07' then 'PQ Curve Test'
else ''
end
when ppnlabels.fieldname = 'ALLOC_BASIS' then
case
when changeitems.value_old = '01' then 'Volume'
when changeitems.value_old = '02' then 'Energy'
when changeitems.value_old = '03' then 'Mass'
when changeitems.value_old = '04' then 'Dual'
else ''
end
when ppnlabels.fieldname = 'ALLOCATE_FLAG'
or ppnlabels.fieldname = 'AUTO_FLOW'
then
case
when changeitems.value_old = 'X' then 'Yes'
else 'No'
end
when ppnlabels.fieldname = 'GHO_MEAS_CAT_ID'
then
case
when changeitems.value_old is not null then meascatdescold.gho_cat_desc
else ''
end
else ''
end as DTL_OLD_DE, //Field value prior to change
//New values
case
when ppnlabels.fieldname = 'NET_ID' then funclocdn.strno
when ppnlabels.fieldname = 'FREQUENCY' then frequency.frequency_code
when ppnlabels.fieldname = 'VALID_FROM' then
tstmp_to_dats( ppa_request.change_start_period,
'UTC',
$session.client,
'NULL')
when ppnlabels.fieldname = 'MEDIUM' then ppa_request.medium
when ppnlabels.fieldname = 'ALLOC_BASIS'
or ppnlabels.fieldname = 'TCM'
or ppnlabels.fieldname = 'ALLOCATE_FLAG'
or ppnlabels.fieldname = 'AUTO_FLOW'
or ppnlabels.fieldname = 'DECIMAL_RANGE'
or ppnlabels.fieldname = 'DIMENSION'
or ppnlabels.fieldname = 'ROUNDING'
or ppnlabels.fieldname = 'GHO_MEAS_TYPE'
or ppnlabels.fieldname = 'GHO_MEAS_CAT_ID'
or ppnlabels.fieldname = 'GHO_DEFAULT_CAT'
then changeitems.value_new
else ''
end as DTL_NEW_CD, //Code following change
//Field Description - New values
case
when ppnlabels.fieldname = 'NET_ID' then cast(funclocdn_desc.pltxt as abap.char(35))
when ppnlabels.fieldname = 'FREQUENCY' then cast(frequency_text.frequency_desc as abap.char( 35 ))
when ppnlabels.fieldname = 'TCM' then
case
when changeitems.value_new = '01' then 'Latest Test'
when changeitems.value_new = '02' then 'Average Test'
when changeitems.value_new = '03' then 'Weighted Average Test'
when changeitems.value_new = '04' then 'Average for the month'
when changeitems.value_new = '05' then 'Metered Volume'
when changeitems.value_new = '06' then 'Single Well Completion'
when changeitems.value_new = '07' then 'PQ Curve Test'
else ''
end
when ppnlabels.fieldname = 'ALLOC_BASIS' then
case
when changeitems.value_new = '01' then 'Volume'
when changeitems.value_new = '02' then 'Energy'
when changeitems.value_new = '03' then 'Mass'
when changeitems.value_new = '04' then 'Dual'
else ''
end
when ppnlabels.fieldname = 'ALLOCATE_FLAG'
or ppnlabels.fieldname = 'AUTO_FLOW'
then
case
when changeitems.value_new = 'X' then 'Yes'
else 'No'
end
when ppnlabels.fieldname = 'GHO_MEAS_CAT_ID'
then
case
when changeitems.value_old is not null then meascatdescnew.gho_cat_desc
else ''
end
else ''
end as DTL_NEW_DE //Field value following change
}
where
ppa_request.process_type = 'NETRULES'
or ppa_request.process_type = 'UOMRULE'
or ppa_request.process_type = 'MEASPRIO'
and ppa_request.action <> 'D'