select distinct rul.rule_id as rule_id,
rdef.rule_name rule_name,
(SELECT meaning
FROM okc_lookups_v
WHERE lookup_type = 'OKC_XPRT_RULE_STATUS'
AND lookup_code = rdef.status_code) as rule_status_code,
rdef.rule_type rule_type,
(SELECT meaning
FROM okc_lookups_v
WHERE lookup_type = 'OKC_XPRT_RULE_TYPE'
AND lookup_code = rdef.rule_type) as rule_type_meaning,
rul.rule_expr_type,
(SELECT meaning
FROM okc_lookups_v
WHERE lookup_type = 'OKC_XPRT_CONDITION_CRITERIA'
AND lookup_code = rul.rule_expr_type) as condition_expr_code_meaning,
decode(rul.rule_expr_type, 'ALL',decode(( SELECT distinct 'N'
FROM OKC_XPRT_RULE_EVAL_RESULT_T
where rule_id = rul.rule_id
and doc_id = :testCaseId
and doc_type = :docType
and condition_id IS NOT null
and (result = 'N' or result is null)), 'N', (SELECT meaning
FROM okc_lookups_v
WHERE lookup_type = 'OKC_XPRT_RES_OUTCOME'
AND lookup_code = 'N'), (SELECT meaning
FROM okc_lookups_v
WHERE lookup_type = 'OKC_XPRT_RES_OUTCOME'
AND lookup_code = 'Y')),'ANY',decode((SELECT distinct 'Y'
FROM OKC_XPRT_RULE_EVAL_RESULT_T
where rule_id = rul.rule_id
and doc_id = :testCaseId
and doc_type = :docType
and condition_id IS NOT null
and result = 'Y'), 'Y', (SELECT meaning
FROM okc_lookups_v
WHERE lookup_type = 'OKC_XPRT_RES_OUTCOME'
AND lookup_code = 'Y'), (SELECT meaning
FROM okc_lookups_v
WHERE lookup_type = 'OKC_XPRT_RES_OUTCOME'
AND lookup_code = 'N'))) rule_outcome_val,
decode(rul.rule_expr_type, 'ALL',decode(( SELECT distinct 'N'
FROM OKC_XPRT_RULE_EVAL_RESULT_T
where rule_id = rul.rule_id
and doc_id = :testCaseId
and doc_type = :docType
and condition_id IS NOT null
and (result = 'N' or result is null)), 'N', 'N', 'Y'),'ANY',decode((SELECT distinct 'Y'
FROM OKC_XPRT_RULE_EVAL_RESULT_T
where rule_id = rul.rule_id
and doc_id = :testCaseId
and doc_type = :docType
and condition_id IS NOT null
and result = 'Y'), 'Y', 'Y', 'N')) rule_outcome_val_code
from OKC_XPRT_RULE_EVAL_RESULT_T rul, OKC_XPRT_RULE_HDRS_ALL_V rdef
where doc_id = :testCaseId
and doc_type = :docType
AND condition_id is not null
and rul.rule_id = rdef.rule_id
order by rule_name |
