HWM_RULE_SET_MBRS_HIST_D

Details

• Schema: FUSION

• Object owner: HWM

• Object type: VIEW

Columns

Name

RULE_SET_ID EFFECTIVE_START_DATE EFFECTIVE_END_DATE RULE_SET_UNQ_ID RULE_SET_NAME RULE_TYPE MEMBER_TYPE_1 MBR_RULE_ID_1 MBR_RULE_SET_ID_1 MBR_CND_RULE_ID_1 MBR_ID_1 PROCESSING_ORDER_1 MEMBER_NAME_1 TCAT_NAME_1 TCAT_ID_1 MEMBER_TYPE_2 MBR_RULE_ID_2 MBR_RULE_SET_ID_2 MBR_CND_RULE_ID_2 MBR_ID_2 PROCESSING_ORDER_2 MEMBER_NAME_2 TCAT_NAME_2 TCAT_ID_2 MEMBER_TYPE_3 MBR_RULE_ID_3 MBR_RULE_SET_ID_3 MBR_CND_RULE_ID_3 MBR_ID_3 PROCESSING_ORDER_3 MEMBER_NAME_3 TCAT_NAME_3 TCAT_ID_3 MEMBER_TYPE_4 MBR_RULE_ID_4 MBR_RULE_SET_ID_4 MBR_CND_RULE_ID_4 MBR_ID_4 PROCESSING_ORDER_4 MEMBER_NAME_4 TCAT_NAME_4 TCAT_ID_4 MEMBER_TYPE_5 MBR_RULE_ID_5 MBR_RULE_SET_ID_5 MBR_CND_RULE_ID_5 MBR_ID_5 PROCESSING_ORDER_5 MEMBER_NAME_5 TCAT_NAME_5 TCAT_ID_5 MEMBER_TYPE_6 MBR_RULE_ID_6 MBR_RULE_SET_ID_6 MBR_CND_RULE_ID_6 MBR_ID_6 PROCESSING_ORDER_6 TCAT_ID_6 MEMBER_NAME_6 TCAT_NAME_6 MEMBER_TYPE_7 MBR_RULE_ID_7 MBR_RULE_SET_ID_7 MBR_CND_RULE_ID_7 MBR_ID_7 PROCESSING_ORDER_7 MEMBER_NAME_7 TCAT_NAME_7 TCAT_ID_7 MEMBER_TYPE_8 MBR_RULE_ID_8 MBR_RULE_SET_ID_8 MBR_CND_RULE_ID_8 MBR_ID_8 PROCESSING_ORDER_8 MEMBER_NAME_8 TCAT_NAME_8 TCAT_ID_8 MEMBER_TYPE_9 MBR_RULE_ID_9 MBR_RULE_SET_ID_9 MBR_CND_RULE_ID_9 MBR_ID_9 PROCESSING_ORDER_9 MEMBER_NAME_9 TCAT_NAME_9 TCAT_ID_9 MEMBER_TYPE_10 MBR_RULE_ID_10 MBR_RULE_SET_ID_10 MBR_CND_RULE_ID_10 MBR_ID_10 PROCESSING_ORDER_10 MEMBER_NAME_10 TCAT_NAME_10 TCAT_ID_10 MEMBER_TYPE_11 MBR_RULE_ID_11 MBR_RULE_SET_ID_11 MBR_CND_RULE_ID_11 MBR_ID_11 PROCESSING_ORDER_11 MEMBER_NAME_11 TCAT_NAME_11 TCAT_ID_11 MEMBER_TYPE_12 MBR_RULE_ID_12 MBR_RULE_SET_ID_12 MBR_CND_RULE_ID_12 MBR_ID_12 PROCESSING_ORDER_12 MEMBER_NAME_12 TCAT_NAME_12 TCAT_ID_12 MEMBER_TYPE_13 MBR_RULE_ID_13 MBR_RULE_SET_ID_13 MBR_CND_RULE_ID_13 MBR_ID_13 PROCESSING_ORDER_13 MEMBER_NAME_13 TCAT_NAME_13 TCAT_ID_13 MEMBER_TYPE_14 MBR_RULE_ID_14 MBR_RULE_SET_ID_14 MBR_CND_RULE_ID_14 MBR_ID_14 PROCESSING_ORDER_14 MEMBER_NAME_14 TCAT_NAME_14 TCAT_ID_14 MEMBER_TYPE_15 MBR_RULE_ID_15 MBR_RULE_SET_ID_15 MBR_CND_RULE_ID_15 MBR_ID_15 PROCESSING_ORDER_15 MEMBER_NAME_15 TCAT_NAME_15 TCAT_ID_15 MEMBER_TYPE_16 MBR_RULE_ID_16 MBR_RULE_SET_ID_16 MBR_CND_RULE_ID_16 MBR_ID_16 PROCESSING_ORDER_16 MEMBER_NAME_16 TCAT_NAME_16 TCAT_ID_16 MEMBER_TYPE_17 MBR_RULE_ID_17 MBR_RULE_SET_ID_17 MBR_CND_RULE_ID_17 MBR_ID_17 PROCESSING_ORDER_17 MEMBER_NAME_17 TCAT_NAME_17 TCAT_ID_17 MEMBER_TYPE_18 MBR_RULE_ID_18 MBR_RULE_SET_ID_18 MBR_CND_RULE_ID_18 MBR_ID_18 PROCESSING_ORDER_18 MEMBER_NAME_18 TCAT_NAME_18 TCAT_ID_18 MEMBER_TYPE_19 MBR_RULE_ID_19 MBR_RULE_SET_ID_19 MBR_CND_RULE_ID_19 MBR_ID_19 PROCESSING_ORDER_19 MEMBER_NAME_19 TCAT_NAME_19 TCAT_ID_19 MEMBER_TYPE_20 MBR_RULE_ID_20 MBR_RULE_SET_ID_20 MBR_CND_RULE_ID_20 MBR_ID_20 PROCESSING_ORDER_20 MEMBER_NAME_20 TCAT_NAME_20 TCAT_ID_20 ENTERPRISE_ID

Query

SQL_Statement

SELECT rule_set_id , effective_start_date , effective_end_date , rule_set_unq_id , rule_set_name , rule_type , MAX(DECODE ( rn , 1, member_type )) member_type_1 , MAX(DECODE ( rn , 1, MBR_RULE_ID)) mbr_rule_id_1 , MAX(DECODE ( rn , 1, MBR_RULE_SET_ID)) mbr_rule_set_id_1 , MAX(DECODE ( rn , 1, mbr_cndl_rule_id)) mbr_cnd_rule_id_1 , MAX(DECODE ( rn , 1, mbr_id)) mbr_id_1 , MAX(DECODE ( rn , 1, processing_order)) processing_order_1 , MAX(DECODE ( rn , 1, member_name)) member_name_1 , MAX(DECODE ( rn , 1, tcat_name)) tcat_name_1 , MAX(DECODE ( rn , 1, tcat_id )) tcat_id_1 , MAX(DECODE ( rn , 2, member_type )) member_type_2 , MAX(DECODE ( rn , 2, MBR_RULE_ID)) mbr_rule_id_2 , MAX(DECODE ( rn , 2, MBR_RULE_SET_ID)) mbr_rule_set_id_2 , MAX(DECODE ( rn , 2, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_2 , MAX(DECODE ( rn , 2, mbr_id)) mbr_id_2 , MAX(DECODE ( rn , 2, processing_order)) processing_order_2 , MAX(DECODE ( rn , 2, member_name)) member_name_2 , MAX(DECODE ( rn , 2, tcat_name)) tcat_name_2 , MAX(DECODE ( rn , 2, tcat_id )) tcat_id_2 , MAX(DECODE ( rn , 3, member_type )) member_type_3 , MAX(DECODE ( rn , 3, MBR_RULE_ID)) mbr_rule_id_3 , MAX(DECODE ( rn , 3, MBR_RULE_SET_ID)) mbr_rule_set_id_3 , MAX(DECODE ( rn , 3, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_3 , MAX(DECODE ( rn , 3, mbr_id)) mbr_id_3 , MAX(DECODE ( rn , 3, processing_order)) processing_order_3 , MAX(DECODE ( rn , 3, member_name)) member_name_3 , MAX(DECODE ( rn , 3, tcat_name)) tcat_name_3 , MAX(DECODE ( rn , 3, tcat_id )) tcat_id_3 , MAX(DECODE ( rn , 4, member_type )) member_type_4 , MAX(DECODE ( rn , 4, MBR_RULE_ID)) mbr_rule_id_4 , MAX(DECODE ( rn , 4, MBR_RULE_SET_ID)) mbr_rule_set_id_4 , MAX(DECODE ( rn , 4, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_4 , MAX(DECODE ( rn , 4, mbr_id)) mbr_id_4 , MAX(DECODE ( rn , 4, processing_order)) processing_order_4 , MAX(DECODE ( rn , 4, member_name)) member_name_4 , MAX(DECODE ( rn , 4, tcat_name)) tcat_name_4 , MAX(DECODE ( rn , 4, tcat_id )) tcat_id_4 , MAX(DECODE ( rn , 5, member_type )) member_type_5 , MAX(DECODE ( rn , 5, MBR_RULE_ID)) mbr_rule_id_5 , MAX(DECODE ( rn , 5, MBR_RULE_SET_ID)) mbr_rule_set_id_5 , MAX(DECODE ( rn , 5, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_5 , MAX(DECODE ( rn , 5, mbr_id)) mbr_id_5 , MAX(DECODE ( rn , 5, processing_order)) processing_order_5 , MAX(DECODE ( rn , 5, member_name)) member_name_5 , MAX(DECODE ( rn , 5, tcat_name)) tcat_name_5 , MAX(DECODE ( rn , 5, tcat_id )) tcat_id_5 , MAX(DECODE ( rn , 6, member_type )) member_type_6 , MAX(DECODE ( rn , 6, MBR_RULE_ID)) mbr_rule_id_6 , MAX(DECODE ( rn , 6, MBR_RULE_SET_ID)) mbr_rule_set_id_6 , MAX(DECODE ( rn , 6, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_6 , MAX(DECODE ( rn , 6, mbr_id)) mbr_id_6 , MAX(DECODE ( rn , 6, processing_order)) processing_order_6 , MAX(DECODE ( rn , 6, tcat_id )) tcat_id_6 , MAX(DECODE ( rn , 6, member_name)) member_name_6 , MAX(DECODE ( rn , 6, tcat_name)) tcat_name_6 , MAX(DECODE ( rn , 7, member_type )) member_type_7 , MAX(DECODE ( rn , 7, MBR_RULE_ID)) mbr_rule_id_7 , MAX(DECODE ( rn , 7, MBR_RULE_SET_ID)) mbr_rule_set_id_7 , MAX(DECODE ( rn , 7, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_7 , MAX(DECODE ( rn , 7, mbr_id)) mbr_id_7 , MAX(DECODE ( rn , 7, processing_order)) processing_order_7 , MAX(DECODE ( rn , 7, member_name)) member_name_7 , MAX(DECODE ( rn , 7, tcat_name)) tcat_name_7 , MAX(DECODE ( rn , 7, tcat_id )) tcat_id_7 , MAX(DECODE ( rn , 8, member_type )) member_type_8 , MAX(DECODE ( rn , 8, MBR_RULE_ID)) mbr_rule_id_8 , MAX(DECODE ( rn , 8, MBR_RULE_SET_ID)) mbr_rule_set_id_8 , MAX(DECODE ( rn , 8, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_8 , MAX(DECODE ( rn , 8, mbr_id)) mbr_id_8 , MAX(DECODE ( rn , 8, processing_order)) processing_order_8 , MAX(DECODE ( rn , 8, member_name)) member_name_8 , MAX(DECODE ( rn , 8, tcat_name)) tcat_name_8 , MAX(DECODE ( rn , 8, tcat_id )) tcat_id_8 , MAX(DECODE ( rn , 9, member_type )) member_type_9 , MAX(DECODE ( rn , 9, MBR_RULE_ID)) mbr_rule_id_9 , MAX(DECODE ( rn , 9, MBR_RULE_SET_ID)) mbr_rule_set_id_9 , MAX(DECODE ( rn , 9, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_9 , MAX(DECODE ( rn , 9, mbr_id)) mbr_id_9 , MAX(DECODE ( rn , 9, processing_order)) processing_order_9 , MAX(DECODE ( rn , 9, member_name)) member_name_9 , MAX(DECODE ( rn , 9, tcat_name)) tcat_name_9 , MAX(DECODE ( rn , 9, tcat_id )) tcat_id_9 , MAX(DECODE ( rn , 10, member_type )) member_type_10 , MAX(DECODE ( rn , 10, MBR_RULE_ID)) mbr_rule_id_10 , MAX(DECODE ( rn , 10, MBR_RULE_SET_ID)) mbr_rule_set_id_10 , MAX(DECODE ( rn , 10, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_10 , MAX(DECODE ( rn , 10, mbr_id)) mbr_id_10 , MAX(DECODE ( rn , 10, processing_order)) processing_order_10 , MAX(DECODE ( rn , 10, member_name)) member_name_10 , MAX(DECODE ( rn , 10, tcat_name)) tcat_name_10 , MAX(DECODE ( rn , 10, tcat_id )) tcat_id_10 , MAX(DECODE ( rn , 11, member_type )) member_type_11 , MAX(DECODE ( rn , 11, MBR_RULE_ID)) mbr_rule_id_11 , MAX(DECODE ( rn , 11, MBR_RULE_SET_ID)) mbr_rule_set_id_11 , MAX(DECODE ( rn , 11, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_11 , MAX(DECODE ( rn , 11, mbr_id)) mbr_id_11 , MAX(DECODE ( rn , 11, processing_order)) processing_order_11 , MAX(DECODE ( rn , 11, member_name)) member_name_11 , MAX(DECODE ( rn , 11, tcat_name)) tcat_name_11 , MAX(DECODE ( rn , 11, tcat_id )) tcat_id_11 , MAX(DECODE ( rn , 12, member_type )) member_type_12 , MAX(DECODE ( rn , 12, MBR_RULE_ID)) mbr_rule_id_12 , MAX(DECODE ( rn , 12, MBR_RULE_SET_ID)) mbr_rule_set_id_12 , MAX(DECODE ( rn , 12, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_12 , MAX(DECODE ( rn , 12, mbr_id)) mbr_id_12 , MAX(DECODE ( rn , 12, processing_order)) processing_order_12 , MAX(DECODE ( rn , 12, member_name)) member_name_12 , MAX(DECODE ( rn , 12, tcat_name)) tcat_name_12 , MAX(DECODE ( rn , 12, tcat_id )) tcat_id_12 , MAX(DECODE ( rn , 13, member_type )) member_type_13 , MAX(DECODE ( rn , 13, MBR_RULE_ID)) mbr_rule_id_13 , MAX(DECODE ( rn , 13, MBR_RULE_SET_ID)) mbr_rule_set_id_13 , MAX(DECODE ( rn , 13, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_13 , MAX(DECODE ( rn , 13, mbr_id)) mbr_id_13 , MAX(DECODE ( rn , 13, processing_order)) processing_order_13 , MAX(DECODE ( rn , 13, member_name)) member_name_13 , MAX(DECODE ( rn , 13, tcat_name)) tcat_name_13 , MAX(DECODE ( rn , 13, tcat_id )) tcat_id_13 , MAX(DECODE ( rn , 14, member_type )) member_type_14 , MAX(DECODE ( rn , 14, MBR_RULE_ID)) mbr_rule_id_14 , MAX(DECODE ( rn , 14, MBR_RULE_SET_ID)) mbr_rule_set_id_14 , MAX(DECODE ( rn , 14, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_14 , MAX(DECODE ( rn , 14, mbr_id)) mbr_id_14 , MAX(DECODE ( rn , 14, processing_order)) processing_order_14 , MAX(DECODE ( rn , 14, member_name)) member_name_14 , MAX(DECODE ( rn , 14, tcat_name)) tcat_name_14 , MAX(DECODE ( rn , 14, tcat_id )) tcat_id_14 , MAX(DECODE ( rn , 15, member_type )) member_type_15 , MAX(DECODE ( rn , 15, MBR_RULE_ID)) mbr_rule_id_15 , MAX(DECODE ( rn , 15, MBR_RULE_SET_ID)) mbr_rule_set_id_15 , MAX(DECODE ( rn , 15, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_15 , MAX(DECODE ( rn , 15, mbr_id)) mbr_id_15 , MAX(DECODE ( rn , 15, processing_order)) processing_order_15 , MAX(DECODE ( rn , 15, member_name)) member_name_15 , MAX(DECODE ( rn , 15, tcat_name)) tcat_name_15 , MAX(DECODE ( rn , 15, tcat_id )) tcat_id_15 , MAX(DECODE ( rn , 16, member_type )) member_type_16 , MAX(DECODE ( rn , 16, MBR_RULE_ID)) mbr_rule_id_16 , MAX(DECODE ( rn , 16, MBR_RULE_SET_ID)) mbr_rule_set_id_16 , MAX(DECODE ( rn , 16, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_16 , MAX(DECODE ( rn , 16, mbr_id)) mbr_id_16 , MAX(DECODE ( rn , 16, processing_order)) processing_order_16 , MAX(DECODE ( rn , 16, member_name)) member_name_16 , MAX(DECODE ( rn , 16, tcat_name)) tcat_name_16 , MAX(DECODE ( rn , 16, tcat_id )) tcat_id_16 , MAX(DECODE ( rn , 17, member_type )) member_type_17 , MAX(DECODE ( rn , 17, MBR_RULE_ID)) mbr_rule_id_17 , MAX(DECODE ( rn , 17, MBR_RULE_SET_ID)) mbr_rule_set_id_17 , MAX(DECODE ( rn , 17, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_17 , MAX(DECODE ( rn , 17, mbr_id)) mbr_id_17 , MAX(DECODE ( rn , 17, processing_order)) processing_order_17 , MAX(DECODE ( rn , 17, member_name)) member_name_17 , MAX(DECODE ( rn , 17, tcat_name)) tcat_name_17 , MAX(DECODE ( rn , 17, tcat_id )) tcat_id_17 , MAX(DECODE ( rn , 18, member_type )) member_type_18 , MAX(DECODE ( rn , 18, MBR_RULE_ID)) mbr_rule_id_18 , MAX(DECODE ( rn , 18, MBR_RULE_SET_ID)) mbr_rule_set_id_18 , MAX(DECODE ( rn , 18, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_18 , MAX(DECODE ( rn , 18, mbr_id)) mbr_id_18 , MAX(DECODE ( rn , 18, processing_order)) processing_order_18 , MAX(DECODE ( rn , 18, member_name)) member_name_18 , MAX(DECODE ( rn , 18, tcat_name)) tcat_name_18 , MAX(DECODE ( rn , 18, tcat_id )) tcat_id_18 , MAX(DECODE ( rn , 19, member_type )) member_type_19 , MAX(DECODE ( rn , 19, MBR_RULE_ID)) mbr_rule_id_19 , MAX(DECODE ( rn , 19, MBR_RULE_SET_ID)) mbr_rule_set_id_19 , MAX(DECODE ( rn , 19, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_19 , MAX(DECODE ( rn , 19, mbr_id)) mbr_id_19 , MAX(DECODE ( rn , 19, processing_order)) processing_order_19 , MAX(DECODE ( rn , 19, member_name)) member_name_19 , MAX(DECODE ( rn , 19, tcat_name)) tcat_name_19 , MAX(DECODE ( rn , 19, tcat_id )) tcat_id_19 , MAX(DECODE ( rn , 20, member_type )) member_type_20 , MAX(DECODE ( rn , 20, MBR_RULE_ID)) mbr_rule_id_20 , MAX(DECODE ( rn , 20, MBR_RULE_SET_ID)) mbr_rule_set_id_20 , MAX(DECODE ( rn , 20, MBR_CNDL_RULE_ID)) mbr_cnd_rule_id_20 , MAX(DECODE ( rn , 20, mbr_id)) mbr_id_20 , MAX(DECODE ( rn , 20, processing_order)) processing_order_20 , MAX(DECODE ( rn , 20, member_name)) member_name_20 , MAX(DECODE ( rn , 20, tcat_name)) tcat_name_20 , MAX(DECODE ( rn , 20, tcat_id )) tcat_id_20 , enterprise_id FROM (SELECT rs.rule_set_id , rl.rule_set_name , rl.effective_start_date, rl.effective_end_date , rl.rule_set_unq_id , rl.rule_type , rs.processing_order , rs.tcat_id , rs.member_type , rs.MBR_RULE_ID , rs.MBR_RULE_SET_ID , rs.mbr_cndl_rule_id , rs.mbr_id , mids.member_name , tcat.tcat_name , rs.enterprise_id , row_number() over ( partition BY rs.rule_set_unq_id order by rs.processing_order ) rn FROM hwm_rule_sets_f rl , (SELECT rule_set_id , rule_set_unq_id , processing_order , tcat_id , member_type , mbr_rule_id , mbr_rule_set_id , mbr_cndl_rule_id , NVL( mbr_rule_set_id , NVL(mbr_cndl_rule_id , mbr_rule_id)) mbr_id, enterprise_id FROM hwm_rule_set_mbrs ) rs , (SELECT 'RULE' member_type , r.rule_id member_id , r.rule_name member_name FROM hwm_rules r UNION SELECT 'RULESET' MEMBER_TYPE, rule_set_id MEMBER_ID , rule_set_name MEMBER_NAME FROM HWM_RULE_SETS_F rs WHERE trunc(sysdate) BETWEEN trunc(rs.effective_start_date) AND trunc(rs.effective_end_date) UNION SELECT 'CONDRULE' member_type, cndl_rule_id member_id , cndl_rule_name member_name FROM hwm_rules_cndl ) mids , ( SELECT tcat_id, tcat_name FROM HWM_TCATS_VL ) tcat WHERE rl.rule_set_id = rs.rule_set_id AND rl.rule_set_unq_id = rs.rule_set_unq_id AND rl.enterprise_id = rs.enterprise_id AND rs.member_type = mids.member_type AND rs.mbr_id = mids.member_id AND rs.tcat_id = tcat.tcat_id(+) ) ta GROUP BY rule_set_id , effective_start_date, effective_end_date , rule_set_unq_id , enterprise_id , rule_set_name , rule_type