1. Tables and Views for Sales and Fusion Service
  2. ZSF_DS_ADJUSTMENT_VIEW

ZSF_DS_ADJUSTMENT_VIEW

Details

  • Schema: FUSION

  • Object owner: ZSF

  • Object type: VIEW

Columns

Name

METRIC_READ_FROM

IS_ADJUSTED

METRIC_CHG_ID

METRIC_CODE

METRIC_LEVEL

PERIOD_ID

DIM1_CODE

DIM1_KEY

DIM1_PARENT_KEY

DIM1_ANC_PATH

DIM1_ROOT_FLAG

DIM2_CODE

DIM2_KEY

DIM2_PARENT_KEY

DIM2_ANC_PATH

DIM2_ROOT_FLAG

DIM3_CODE

DIM3_KEY

DIM3_PARENT_KEY

DIM3_ANC_PATH

DIM3_ROOT_FLAG

DIM4_CODE

DIM4_KEY

DIM4_PARENT_KEY

DIM4_ANC_PATH

DIM4_ROOT_FLAG

DIM5_CODE

DIM5_KEY

DIM5_PARENT_KEY

DIM5_ANC_PATH

DIM5_ROOT_FLAG

METRIC_SRC_PK1

METRIC_SRC_OVN1

METRIC_SRC_PK2

METRIC_SRC_OVN2

METRIC_SRC_PK3

METRIC_SRC_OVN3

ADJUSTED_METRIC_VALUE

CREATED_BY

CREATION_DATE

LAST_UPDATE_DATE

LAST_UPDATED_BY

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

Query

SQL_Statement

SELECT

'metric_table_adjustment' metric_read_from,

1 is_adjusted,

met.metric_id metric_chg_id,

met.metric_code metric_code,

met.metric_level metric_level,

met.period_id period_id,

s.metric_dim1_code dim1_code,

met.metric_dim1_key dim1_key,

met.metric_dim1_key dim1_parent_key,

nvl2(met.metric_dim1_key, '/' || met.metric_dim1_key, NULL) dim1_anc_path,

met.dim1_root_flag dim1_root_flag,

s.metric_dim2_code dim2_code,

met.metric_dim2_key dim2_key,

met.metric_dim2_key dim2_parent_key,

nvl2(met.metric_dim2_key, '/' || met.metric_dim2_key, NULL) dim2_anc_path,

met.dim2_root_flag dim2_root_flag,

s.metric_dim3_code dim3_code,

met.metric_dim3_key dim3_key,

met.metric_dim3_key dim3_parent_key,

nvl2(met.metric_dim3_key, '/' || met.metric_dim3_key, NULL) dim3_anc_path,

met.dim3_root_flag dim3_root_flag,

s.metric_dim4_code dim4_code,

met.metric_dim4_key dim4_key,

met.metric_dim4_key dim4_parent_key,

nvl2(met.metric_dim4_key, '/' || met.metric_dim4_key, NULL) dim4_anc_path,

met.dim4_root_flag dim4_root_flag,

s.metric_dim5_code dim5_code,

met.metric_dim5_key dim5_key,

met.metric_dim5_key dim5_parent_key,

nvl2(met.metric_dim5_key, '/' || met.metric_dim5_key, NULL) dim5_anc_path,

met.dim5_root_flag dim5_root_flag,

NULL metric_src_pk1,

NULL metric_src_ovn1,

NULL metric_src_pk2,

NULL metric_src_ovn2,

NULL metric_src_pk3,

NULL metric_src_ovn3,

met.metric_value adjusted_metric_value,

met.created_by created_by,

met.creation_date creation_date,

met.last_update_date last_update_date,

met.last_updated_by last_updated_by,

met.last_update_login last_update_login,

met.object_version_number object_version_number

FROM

zsf_ds_metric met,

zsf_ds_metric_defn d,

zsf_ds_metric_src s

WHERE

0 = 0

AND d.metric_code = met.metric_code

AND d.status_code = 'ENABLED'

AND s.metric_src_code = d.metric_src_code

AND s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE')

AND met.snapshot_id = met.period_id

AND metric_level LIKE 'ADJUSTED:%'

AND NOT EXISTS (

SELECT

1

FROM

zsf_ds_adjustment_calc adc

WHERE 0 = 0

and adc.metric_code = met.metric_code

and adc.period_id = met.period_id

and adc.dim1_parent_key = met.metric_dim1_key

and adc.process_status = 'N'

and adc.metric_src_pk1 IS NULL

)

AND NOT EXISTS (

SELECT

1

FROM

zsf_ds_adjustment_calc adc

WHERE 0 = 0

and adc.period_id = met.period_id

and adc.dim1_parent_key = met.metric_dim1_key

and adc.process_status = 'N'

and adc.metric_src_pk1 IS NOT NULL

)

AND NOT EXISTS (

select

1

from

zsf_ds_metric_src_chg_log chg

, ( select 1 as col

from dual d1

where rownum = 1

union all

select 2 as col

from dual d2

where rownum = 1 ) mlvl

, zsf_ds_metric_dim_rf_dn dim1

, zsf_ds_metric_dim_rf_dn dim2

, zsf_ds_metric_dim_rf_dn dim3

, zsf_ds_metric_dim_rf_dn dim4

, zsf_ds_metric_dim_rf_dn dim5

, zsf_ds_metric_defn d

, zsf_ds_metric_src s

where 0 = 0

and d.metric_code = chg.metric_code

and s.metric_src_code = d.metric_src_code

and d.status_code = 'ENABLED'

and chg.metric_dim1_key = dim1.metric_dim_key

and chg.metric_dim2_key = dim2.metric_dim_key (+)

and chg.metric_dim3_key = dim3.metric_dim_key (+)

and chg.metric_dim4_key = dim4.metric_dim_key (+)

and chg.metric_dim5_key = dim5.metric_dim_key (+)

and dim1.metric_dim_code(+) = s.metric_dim1_code

and dim2.metric_dim_code(+) = s.metric_dim2_code

and dim3.metric_dim_code(+) = s.metric_dim3_code

and dim4.metric_dim_code(+) = s.metric_dim4_code

and dim5.metric_dim_code(+) = s.metric_dim5_code

and chg.metric_code = met.metric_code

and chg.period_id = met.period_id

and dim1.metric_dim_parent_key = met.metric_dim1_key

and s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE')

)

UNION ALL

select

NVL2(adcj.METRIC_CODE,

'item_adjustment',

unadj.METRIC_READ_FROM) metric_read_from

, NVL2(adcj.METRIC_CODE,1,

unadj.IS_ADJUSTED) is_adjusted

, unadj.METRIC_CHG_ID metric_chg_id

, NVL(adcj.METRIC_CODE,

unadj.METRIC_CODE) metric_code

, NVL(adcj.metric_level,

unadj.metric_level) metric_level

, unadj.PERIOD_ID period_id

, unadj.DIM1_CODE dim1_code

, unadj.DIM1_KEY dim1_key

, unadj.DIM1_PARENT_KEY dim1_parent_key

, unadj.DIM1_ANC_PATH dim1_anc_path

, unadj.DIM1_ROOT_FLAG dim1_root_flag

, unadj.DIM2_CODE dim2_code

, unadj.DIM2_KEY dim2_key

, unadj.DIM2_PARENT_KEY dim2_parent_key

, unadj.DIM2_ANC_PATH dim2_anc_path

, unadj.DIM2_ROOT_FLAG dim2_root_flag

, unadj.DIM3_CODE dim3_code

, unadj.DIM3_KEY dim3_key

, unadj.DIM3_PARENT_KEY dim3_parent_key

, unadj.DIM3_ANC_PATH dim3_anc_path

, unadj.DIM3_ROOT_FLAG dim3_root_flag

, unadj.DIM4_CODE dim4_code

, unadj.DIM4_KEY dim4_key

, unadj.DIM4_PARENT_KEY dim4_parent_key

, unadj.DIM4_ANC_PATH dim4_anc_path

, unadj.DIM4_ROOT_FLAG dim4_root_flag

, unadj.DIM5_CODE dim5_code

, unadj.DIM5_KEY dim5_key

, unadj.DIM5_PARENT_KEY dim5_parent_key

, unadj.DIM5_ANC_PATH dim5_anc_path

, unadj.DIM5_ROOT_FLAG dim5_root_flag

, unadj.METRIC_SRC_PK1 metric_src_pk1

, unadj.METRIC_SRC_OVN1 metric_src_ovn1

, unadj.METRIC_SRC_PK2 metric_src_pk2

, unadj.METRIC_SRC_OVN2 metric_src_ovn2

, unadj.METRIC_SRC_PK3 metric_src_pk3

, unadj.METRIC_SRC_OVN3 metric_src_ovn3

, decode(

adcj.adjusted_metric_value

, -1, 0

, NULL, unadj.METRIC_VALUE

, adcj.adjusted_metric_value) adjusted_metric_value

, unadj.CREATED_BY created_by

, unadj.CREATION_DATE creation_date

, unadj.LAST_UPDATE_DATE last_update_date

, unadj.LAST_UPDATED_BY last_updated_by

, unadj.LAST_UPDATE_LOGIN last_update_login

, unadj.OBJECT_VERSION_NUMBER object_version_number

from

ZSF_DS_METRIC_CALC_VIEW unadj

, zsf_ds_adjustment_calc adcj

WHERE

adcj.METRIC_SRC_PK1 (+) IS NOT NULL

AND adcj.operation_code (+) = 'ADJUST'

AND unadj.metric_src_pk1 = adcj.metric_src_pk1 (+)

AND NVL(unadj.metric_src_pk2,0) = NVL(adcj.metric_src_pk2 (+) ,0)

AND NVL(unadj.metric_src_pk3,0) = NVL(adcj.metric_src_pk3 (+) ,0)

AND unadj.dim1_parent_key = adcj.dim1_parent_key (+)

AND NVL(unadj.dim2_parent_key,0) = NVL(adcj.dim2_parent_key (+) ,0)

AND NVL(unadj.dim3_parent_key,0) = NVL(adcj.dim3_parent_key (+) ,0)

AND NVL(unadj.dim4_parent_key,0) = NVL(adcj.dim4_parent_key (+) ,0)

AND NVL(unadj.dim5_parent_key,0) = NVL(adcj.dim5_parent_key (+) ,0)

AND case when nvl(adcj.metric_level (+), 'X') = 'OWN' AND unadj.metric_level != 'TOTAL' then 'OWN' ELSE unadj.metric_level END = adcj.metric_level (+)

AND unadj.period_id = adcj.period_id (+)

AND NOT EXISTS (

SELECT 1

FROM zsf_ds_adjustment_calc adci

WHERE length(adcj.adjusted_by_dim1_anc_path) > length(adci.adjusted_by_dim1_anc_path)

AND adci.METRIC_SRC_PK1 IS NOT NULL

AND adci.metric_src_pk1 = adcj.metric_src_pk1

AND NVL(adci.metric_src_pk2,0) = NVL(adcj.metric_src_pk2,0)

AND NVL(adci.metric_src_pk3,0) = NVL(adcj.metric_src_pk3,0)

AND adci.dim1_parent_key = adcj.dim1_parent_key

AND NVL(adci.dim2_parent_key,0) = NVL(adcj.dim2_parent_key,0)

AND NVL(adci.dim3_parent_key,0) = NVL(adcj.dim3_parent_key,0)

AND NVL(adci.dim4_parent_key,0) = NVL(adcj.dim4_parent_key,0)

AND NVL(adci.dim5_parent_key,0) = NVL(adcj.dim5_parent_key,0)

AND adci.metric_level = adcj.metric_level

AND adci.period_id = adcj.period_id

AND adci.operation_code = 'ADJUST'

)

AND NOT EXISTS (

select 1 from

(SELECT

metric_defn_id,

metric_code,

REGEXP_SUBSTR(DEP_LIST, '[^,]+', 1, level) AS dep_list

FROM (

select

defn.metric_defn_id,

defn.metric_code,

defn.depnd_metric_code_list DEP_LIST

from

zsf_ds_metric_defn defn,

zsf_ds_metric_src s,

zsf_ds_param p

where

p.param_name = 'ACTIVE_METRIC_SOURCE'

and defn.status_code = 'ENABLED'

and s.metric_src_code = p.param_value

and s.metric_src_code = defn.metric_src_code

and defn.depnd_metric_code_list IS NOT NULL

)

WHERE

NVL(PRIOR metric_code, metric_code) = metric_code

AND NVL(PRIOR metric_defn_id, metric_defn_id) = metric_defn_id

connect by REGEXP_SUBSTR(DEP_LIST, '[^,]+', 1, level) IS NOT NULL ) dep_codes

where

unadj.metric_code = dep_codes.dep_list

and adcj.METRIC_CODE IS NOT NULL

)

AND NOT EXISTS (

SELECT

1

FROM

zsf_ds_adjustment_calc adc

WHERE

0 = 0

AND adc.operation_code = 'ADJUST'

AND adc.METRIC_SRC_PK1 IS NULL

AND unadj.metric_code = adc.metric_code

AND unadj.metric_level = adc.metric_level

AND adc.period_id = unadj.period_id

AND unadj.dim1_anc_path LIKE adc.dim1_anc_path || '%'

)

AND NOT EXISTS (

SELECT

1

FROM

zsf_ds_adjustment_calc adc

WHERE

0 = 0

AND adc.operation_code = 'ADJUST'

AND adc.METRIC_SRC_PK1 IS NULL

AND adcj.metric_code IS NOT NULL

AND adcj.metric_code = adc.metric_code

AND adcj.metric_level = adc.metric_level

AND adc.period_id = adcj.period_id

AND adcj.dim1_anc_path LIKE adc.dim1_anc_path || '%'

)

AND EXISTS (

select

1

from

zsf_ds_metric_src_chg_log chg

, ( select 1 as col

from dual d1

where rownum = 1

union all

select 2 as col

from dual d2

where rownum = 1 ) mlvl

, zsf_ds_metric_dim_rf_dn dim1

, zsf_ds_metric_dim_rf_dn dim2

, zsf_ds_metric_dim_rf_dn dim3

, zsf_ds_metric_dim_rf_dn dim4

, zsf_ds_metric_dim_rf_dn dim5

, zsf_ds_metric_defn d

, zsf_ds_metric_src s

where 0 = 0

and d.metric_code = chg.metric_code

and s.metric_src_code = d.metric_src_code

and d.status_code = 'ENABLED'

and chg.metric_dim1_key = dim1.metric_dim_key

and chg.metric_dim2_key = dim2.metric_dim_key (+)

and chg.metric_dim3_key = dim3.metric_dim_key (+)

and chg.metric_dim4_key = dim4.metric_dim_key (+)

and chg.metric_dim5_key = dim5.metric_dim_key (+)

and dim1.metric_dim_code(+) = s.metric_dim1_code

and dim2.metric_dim_code(+) = s.metric_dim2_code

and dim3.metric_dim_code(+) = s.metric_dim3_code

and dim4.metric_dim_code(+) = s.metric_dim4_code

and dim5.metric_dim_code(+) = s.metric_dim5_code

and chg.period_id = unadj.period_id

and dim1.metric_dim_parent_key = unadj.dim1_parent_key

and s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE')

UNION ALL

SELECT

1

FROM

zsf_ds_adjustment_calc adci

WHERE 0 = 0

and unadj.period_id = adci.period_id

and unadj.dim1_parent_key = adci.dim1_parent_key

and adci.process_status = 'N'

and unadj.metric_code = adci.metric_code

)

UNION ALL

SELECT

'adjustment_calc' metric_read_from,

1 is_adjusted,

adc.adjustment_id metric_chg_id,

adc.metric_code metric_code,

adc.metric_level metric_level,

adc.period_id period_id,

s.metric_dim1_code dim1_code,

adc.dim1_key dim1_key,

adc.dim1_parent_key dim1_parent_key,

adc.dim1_anc_path dim1_anc_path,

adc.dim1_root_flag dim1_root_flag,

s.metric_dim2_code dim2_code,

adc.dim2_key dim2_key,

adc.dim2_parent_key dim2_parent_key,

adc.dim2_anc_path dim2_anc_path,

adc.dim2_root_flag dim2_root_flag,

s.metric_dim3_code dim3_code,

adc.dim3_key dim3_key,

adc.dim3_parent_key dim3_parent_key,

adc.dim3_anc_path dim3_anc_path,

adc.dim3_root_flag dim3_root_flag,

s.metric_dim4_code dim4_code,

adc.dim4_key dim4_key,

adc.dim4_parent_key dim4_parent_key,

adc.dim4_anc_path dim4_anc_path,

adc.dim4_root_flag dim4_root_flag,

s.metric_dim5_code dim5_code,

adc.dim5_key dim5_key,

adc.dim5_parent_key dim5_parent_key,

adc.dim5_anc_path dim5_anc_path,

adc.dim5_root_flag dim5_root_flag,

adc.metric_src_pk1 metric_src_pk1,

adc.metric_src_ovn1 metric_src_ovn1,

adc.metric_src_pk2 metric_src_pk2,

adc.metric_src_ovn2 metric_src_ovn2,

adc.metric_src_pk3 metric_src_pk3,

adc.metric_src_ovn3 metric_src_ovn3,

adc.adjusted_metric_value adjusted_metric_value,

adc.created_by created_by,

adc.creation_date creation_date,

adc.last_update_date last_update_date,

adc.last_updated_by last_updated_by,

adc.last_update_login last_update_login,

adc.object_version_number object_version_number

FROM

zsf_ds_adjustment_calc adc,

zsf_ds_metric_defn d,

zsf_ds_metric_src s

WHERE

0 = 0

AND adc.operation_code = 'ADJUST'

AND adc.METRIC_SRC_PK1 IS NULL

AND d.metric_code = adc.metric_code

AND d.status_code = 'ENABLED'

AND s.metric_src_code = d.metric_src_code

AND s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE')

AND NOT EXISTS (

SELECT

1

FROM

zsf_ds_adjustment_calc adci

WHERE

adci.operation_code = 'ADJUST'

AND adci.METRIC_SRC_PK1 IS NULL

AND adc.dim1_anc_path LIKE adci.dim1_anc_path || '/%'

AND adc.metric_code = adci.metric_code

AND adc.metric_level = adci.metric_level

AND adc.period_id = adci.period_id

)

AND NOT EXISTS (

SELECT

1

FROM

zsf_ds_adjustment_calc adci

WHERE

adci.operation_code = 'ADJUST'

AND adci.METRIC_SRC_PK1 IS NULL

AND adc.metric_code = adci.metric_code

AND adc.metric_level = adci.metric_level

AND adc.period_id = adci.period_id

AND adc.adjusted_by_dim1_anc_path LIKE adci.adjusted_by_dim1_anc_path || '/%'

AND adc.dim1_anc_path = adci.dim1_anc_path

)

AND EXISTS (

select

1

from

zsf_ds_metric_src_chg_log chg

, ( select 1 as col

from dual d1

where rownum = 1

union all

select 2 as col

from dual d2

where rownum = 1 ) mlvl

, zsf_ds_metric_dim_rf_dn dim1

, zsf_ds_metric_dim_rf_dn dim2

, zsf_ds_metric_dim_rf_dn dim3

, zsf_ds_metric_dim_rf_dn dim4

, zsf_ds_metric_dim_rf_dn dim5

, zsf_ds_metric_defn d

, zsf_ds_metric_src s

where 0 = 0

and d.metric_code = chg.metric_code

and s.metric_src_code = d.metric_src_code

and d.status_code = 'ENABLED'

and chg.metric_dim1_key = dim1.metric_dim_key

and chg.metric_dim2_key = dim2.metric_dim_key (+)

and chg.metric_dim3_key = dim3.metric_dim_key (+)

and chg.metric_dim4_key = dim4.metric_dim_key (+)

and chg.metric_dim5_key = dim5.metric_dim_key (+)

and dim1.metric_dim_code(+) = s.metric_dim1_code

and dim2.metric_dim_code(+) = s.metric_dim2_code

and dim3.metric_dim_code(+) = s.metric_dim3_code

and dim4.metric_dim_code(+) = s.metric_dim4_code

and dim5.metric_dim_code(+) = s.metric_dim5_code

and chg.metric_code = adc.metric_code

and chg.period_id = adc.period_id

and dim1.metric_dim_parent_key = adc.dim1_parent_key

and s.metric_src_code = (SELECT param_value from zsf_ds_param where param_name='ACTIVE_METRIC_SOURCE')

UNION ALL

SELECT

1

FROM

zsf_ds_adjustment_calc adci

WHERE 0 = 0

and adc.metric_code = adci.metric_code

and adc.period_id = adci.period_id

and adc.dim1_parent_key = adci.dim1_parent_key

and adci.metric_level = 'TOTAL'

and adci.process_status = 'N'

)