VIEW: APPS.BIS_CAUSE_EFFECT_V

Source

View Text - Preformatted

SELECT
  cause_short_name,effect_short_name,max(cause_sequence) cause_sequence,max(effect_sequence) effect_sequence
FROM (
  SELECT
    causeInfo.short_name cause_short_name, effectInfo.effect_short_name, 0 cause_sequence,0 effect_sequence
  FROM
    bis_indicators causeInfo,
    ( SELECT
	ce.cause_indicator,i.short_name effect_short_name
      FROM
	bsc_kpi_cause_effect_rels ce,
	bis_indicators i
      WHERE
	i.dataset_id = ce.effect_indicator
	AND (ce.effect_level = 'DATASET' OR ce.cause_level = 'DATASET')
    ) effectInfo
  WHERE
    causeInfo.dataset_id = effectInfo.cause_indicator
  MINUS
  SELECT
    ce.cause_short_name,ce.effect_short_name, 0 cause_sequence,0 effect_sequence
  FROM
    bis_custom_cause_effect_rels ce
  WHERE
    cause_sequence = -1
    AND effect_sequence = -1
  UNION
  SELECT
    ce.cause_short_name,ce.effect_short_name, cause_sequence,effect_sequence
  FROM
    bis_custom_cause_effect_rels ce
  WHERE
    cause_sequence <> -1
    AND effect_sequence <> -1
)GROUP BY cause_short_name,effect_short_name

View Text - HTML Formatted

SELECT CAUSE_SHORT_NAME
, EFFECT_SHORT_NAME
, MAX(CAUSE_SEQUENCE) CAUSE_SEQUENCE
, MAX(EFFECT_SEQUENCE) EFFECT_SEQUENCE FROM ( SELECT CAUSEINFO.SHORT_NAME CAUSE_SHORT_NAME
, EFFECTINFO.EFFECT_SHORT_NAME
, 0 CAUSE_SEQUENCE
, 0 EFFECT_SEQUENCE FROM BIS_INDICATORS CAUSEINFO
, ( SELECT CE.CAUSE_INDICATOR
, I.SHORT_NAME EFFECT_SHORT_NAME FROM BSC_KPI_CAUSE_EFFECT_RELS CE
, BIS_INDICATORS I WHERE I.DATASET_ID = CE.EFFECT_INDICATOR AND (CE.EFFECT_LEVEL = 'DATASET' OR CE.CAUSE_LEVEL = 'DATASET') ) EFFECTINFO WHERE CAUSEINFO.DATASET_ID = EFFECTINFO.CAUSE_INDICATOR MINUS SELECT CE.CAUSE_SHORT_NAME
, CE.EFFECT_SHORT_NAME
, 0 CAUSE_SEQUENCE
, 0 EFFECT_SEQUENCE FROM BIS_CUSTOM_CAUSE_EFFECT_RELS CE WHERE CAUSE_SEQUENCE = -1
AND EFFECT_SEQUENCE = -1 UNION SELECT CE.CAUSE_SHORT_NAME
, CE.EFFECT_SHORT_NAME
, CAUSE_SEQUENCE
, EFFECT_SEQUENCE FROM BIS_CUSTOM_CAUSE_EFFECT_RELS CE WHERE CAUSE_SEQUENCE <> -1
AND EFFECT_SEQUENCE <> -1 )GROUP BY CAUSE_SHORT_NAME
, EFFECT_SHORT_NAME