OSS_BI_ENTITLEMENT_RULES_V

Details

• Schema: FUSION

• Object owner: OSS

• Object type: VIEW

Columns

Name
COVERAGE_HEADER_ID DYNAMIC_MATRIX_RULE_ID COVERAGE_NAME DYNAMIC_MATRIX_CLASS_NAME DYNAMIC_MTX_RULE_GUID RULE_START_DATE RULE_END_DATE SEVERITY_CODE SEVERITY CHANNEL_TYPE_CODE CHANNEL_TYPE CALENDAR FIRST_RESPONSE_METRIC FIRST_RES_WARN_THRESHOLD RESOLUTION_METRIC RESOLUTION_WARN_THRESHOLD

Query

SQL_Statement

SELECT rules.coverage_header_id, rules.dynamic_matrix_rule_id, rules.coverage_name, rules.dynamic_matrix_class_name, rules.dynamic_mtx_rule_guid, rules.rule_start_date, rules.rule_end_date, rules.severity_code, coalesce( ( SELECT meaning FROM fnd_lookup_values_vl WHERE lookup_type = rules.severitycodelookup AND lookup_code = rules.severity_code ), '' ) severity, rules.channel_type_code, coalesce( ( SELECT meaning FROM fnd_lookup_values_vl WHERE lookup_type = rules.channeltypecodelookup AND lookup_code = rules.channel_type_code ), '' ) channel_type, coalesce( ( SELECT schedule_name FROM oss_coverage_schedules WHERE schedule_id = rules.calendarcode ), '' ) calendar, rules.first_response_metric, rules.first_res_warn_threshold, rules.resolution_metric, rules.resolution_warn_threshold FROM ( SELECT ruleeo.dynamic_matrix_rule_id, ruleeo.dynamic_mtx_rule_guid, coverageheaderpeo.coverage_header_id, coverageheaderpeo.coverage_name, matrixclasspeo.dynamic_matrix_class_name, ( SELECT ( SELECT CASE WHEN id1.map_to_rule_col_num = 1 THEN value_string1 WHEN id1.map_to_rule_col_num = 2 THEN value_string2 WHEN id1.map_to_rule_col_num = 3 THEN value_string3 WHEN id1.map_to_rule_col_num = 4 THEN value_string4 WHEN id1.map_to_rule_col_num = 5 THEN value_string5 WHEN id1.map_to_rule_col_num = 6 THEN value_string6 WHEN id1.map_to_rule_col_num = 7 THEN value_string7 WHEN id1.map_to_rule_col_num = 8 THEN value_string8 WHEN id1.map_to_rule_col_num = 9 THEN value_string9 WHEN id1.map_to_rule_col_num = 10 THEN value_string10 END FROM qp_matrix_rules ruleseo1 WHERE ruleseo1.dynamic_matrix_rule_id = ruleeo.dynamic_matrix_rule_id ) FROM qp_matrix_inherited_dimensions id1 LEFT OUTER JOIN qp_matrix_dimensions_b b ON b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id WHERE b.source_code_name = 'SeverityCode' AND id1.dynamic_matrix_id = coverageheaderpeo.matrix_id ) severity_code, ( SELECT mdvl.domain_look_up_code FROM qp_matrix_dimensions_b b, qp_matrix_inherited_dimensions id1, qp_matrix_dimensions_vl mdvl WHERE b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id AND b.dynamic_matrix_dimension_id = mdvl.dynamic_matrix_dimension_id AND b.source_code_name = 'SeverityCode' AND id1.dynamic_matrix_id = coverageheaderpeo.matrix_id ) severitycodelookup, ( SELECT ( SELECT CASE WHEN id1.map_to_rule_col_num = 1 THEN value_string1 WHEN id1.map_to_rule_col_num = 2 THEN value_string2 WHEN id1.map_to_rule_col_num = 3 THEN value_string3 WHEN id1.map_to_rule_col_num = 4 THEN value_string4 WHEN id1.map_to_rule_col_num = 5 THEN value_string5 WHEN id1.map_to_rule_col_num = 6 THEN value_string6 WHEN id1.map_to_rule_col_num = 7 THEN value_string7 WHEN id1.map_to_rule_col_num = 8 THEN value_string8 WHEN id1.map_to_rule_col_num = 9 THEN value_string9 WHEN id1.map_to_rule_col_num = 10 THEN value_string10 END FROM qp_matrix_rules ruleseo1 WHERE ruleseo1.dynamic_matrix_rule_id = ruleeo.dynamic_matrix_rule_id ) FROM qp_matrix_inherited_dimensions id1 LEFT OUTER JOIN qp_matrix_dimensions_b b ON b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id WHERE b.source_code_name = 'ChannelTypeCode' AND id1.dynamic_matrix_id = coverageheaderpeo.matrix_id ) channel_type_code, ( SELECT mdvl.domain_look_up_code FROM qp_matrix_dimensions_b b, qp_matrix_inherited_dimensions id1, qp_matrix_dimensions_vl mdvl WHERE b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id AND b.dynamic_matrix_dimension_id = mdvl.dynamic_matrix_dimension_id AND b.source_code_name = 'ChannelTypeCode' AND id1.dynamic_matrix_id = coverageheaderpeo.matrix_id ) channeltypecodelookup, ( SELECT ( SELECT CASE WHEN id1.map_to_rule_col_num = 1 THEN value_number1 WHEN id1.map_to_rule_col_num = 2 THEN value_number2 WHEN id1.map_to_rule_col_num = 3 THEN value_number3 WHEN id1.map_to_rule_col_num = 4 THEN value_number4 WHEN id1.map_to_rule_col_num = 5 THEN value_number5 WHEN id1.map_to_rule_col_num = 6 THEN value_number6 WHEN id1.map_to_rule_col_num = 7 THEN value_number7 WHEN id1.map_to_rule_col_num = 8 THEN value_number8 WHEN id1.map_to_rule_col_num = 9 THEN value_number9 WHEN id1.map_to_rule_col_num = 10 THEN value_number10 END FROM qp_matrix_rules ruleseo1 WHERE ruleseo1.dynamic_matrix_rule_id = ruleeo.dynamic_matrix_rule_id ) FROM qp_matrix_inherited_dimensions id1 LEFT OUTER JOIN qp_matrix_dimensions_b b ON b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id WHERE b.source_code_name = 'FirstResponseMetricCode' AND id1.dynamic_matrix_id = coverageheaderpeo.matrix_id ) first_response_metric, ( SELECT ( SELECT CASE WHEN id1.map_to_rule_col_num = 1 THEN value_number1 WHEN id1.map_to_rule_col_num = 2 THEN value_number2 WHEN id1.map_to_rule_col_num = 3 THEN value_number3 WHEN id1.map_to_rule_col_num = 4 THEN value_number4 WHEN id1.map_to_rule_col_num = 5 THEN value_number5 WHEN id1.map_to_rule_col_num = 6 THEN value_number6 WHEN id1.map_to_rule_col_num = 7 THEN value_number7 WHEN id1.map_to_rule_col_num = 8 THEN value_number8 WHEN id1.map_to_rule_col_num = 9 THEN value_number9 WHEN id1.map_to_rule_col_num = 10 THEN value_number10 END FROM qp_matrix_rules ruleseo1 WHERE ruleseo1.dynamic_matrix_rule_id = ruleeo.dynamic_matrix_rule_id ) FROM qp_matrix_inherited_dimensions id1 LEFT OUTER JOIN qp_matrix_dimensions_b b ON b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id WHERE b.source_code_name = 'FirstResponseWarningMetricCode' AND id1.dynamic_matrix_id = coverageheaderpeo.matrix_id ) first_res_warn_threshold, ( SELECT ( SELECT CASE WHEN id1.map_to_rule_col_num = 1 THEN value_number1 WHEN id1.map_to_rule_col_num = 2 THEN value_number2 WHEN id1.map_to_rule_col_num = 3 THEN value_number3 WHEN id1.map_to_rule_col_num = 4 THEN value_number4 WHEN id1.map_to_rule_col_num = 5 THEN value_number5 WHEN id1.map_to_rule_col_num = 6 THEN value_number6 WHEN id1.map_to_rule_col_num = 7 THEN value_number7 WHEN id1.map_to_rule_col_num = 8 THEN value_number8 WHEN id1.map_to_rule_col_num = 9 THEN value_number9 WHEN id1.map_to_rule_col_num = 10 THEN value_number10 END FROM qp_matrix_rules ruleseo1 WHERE ruleseo1.dynamic_matrix_rule_id = ruleeo.dynamic_matrix_rule_id ) FROM qp_matrix_inherited_dimensions id1 LEFT OUTER JOIN qp_matrix_dimensions_b b ON b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id WHERE b.source_code_name = 'ResolutionMetricCode' AND id1.dynamic_matrix_id = coverageheaderpeo.matrix_id ) resolution_metric, ( SELECT ( SELECT CASE WHEN id1.map_to_rule_col_num = 1 THEN value_number1 WHEN id1.map_to_rule_col_num = 2 THEN value_number2 WHEN id1.map_to_rule_col_num = 3 THEN value_number3 WHEN id1.map_to_rule_col_num = 4 THEN value_number4 WHEN id1.map_to_rule_col_num = 5 THEN value_number5 WHEN id1.map_to_rule_col_num = 6 THEN value_number6 WHEN id1.map_to_rule_col_num = 7 THEN value_number7 WHEN id1.map_to_rule_col_num = 8 THEN value_number8 WHEN id1.map_to_rule_col_num = 9 THEN value_number9 WHEN id1.map_to_rule_col_num = 10 THEN value_number10 END FROM qp_matrix_rules ruleseo1 WHERE ruleseo1.dynamic_matrix_rule_id = ruleeo.dynamic_matrix_rule_id ) FROM qp_matrix_inherited_dimensions id1 LEFT OUTER JOIN qp_matrix_dimensions_b b ON b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id WHERE b.source_code_name = 'ResolutionWarningMetricCode' AND id1.dynamic_matrix_id = coverageheaderpeo.matrix_id ) resolution_warn_threshold, ( SELECT ( SELECT CASE WHEN id1.map_to_rule_col_num = 1 THEN value_number1 WHEN id1.map_to_rule_col_num = 2 THEN value_number2 WHEN id1.map_to_rule_col_num = 3 THEN value_number3 WHEN id1.map_to_rule_col_num = 4 THEN value_number4 WHEN id1.map_to_rule_col_num = 5 THEN value_number5 WHEN id1.map_to_rule_col_num = 6 THEN value_number6 WHEN id1.map_to_rule_col_num = 7 THEN value_number7 WHEN id1.map_to_rule_col_num = 8 THEN value_number8 WHEN id1.map_to_rule_col_num = 9 THEN value_number9 WHEN id1.map_to_rule_col_num = 10 THEN value_number10 END FROM qp_matrix_rules ruleseo1 WHERE ruleseo1.dynamic_matrix_rule_id = ruleeo.dynamic_matrix_rule_id ) FROM qp_matrix_inherited_dimensions id1 LEFT OUTER JOIN qp_matrix_dimensions_b b ON b.dynamic_matrix_dimension_id = id1.dynamic_matrix_dimension_id WHERE b.source_code_name = 'CalendarCode' AND id1.dynamic_matrix_id = coverageheaderpeo.matrix_id ) calendarcode, ruleeo.rule_start_date, ruleeo.rule_end_date FROM oss_coverage_headers coverageheaderpeo INNER JOIN qp_matrix_rules ruleeo ON coverageheaderpeo.matrix_id = ruleeo.dynamic_matrix_id INNER JOIN qp_matrix_classes_vl matrixclasspeo ON coverageheaderpeo.dynamic_matrix_class_id = matrixclasspeo.dynamic_matrix_class_id ) rules