In table FND_KF_CROSS_VAL_RULES, we have two columns CONDITION_FILTER and VALIDATION_FILTER of XMLTYPE. To view the content of these columns, we need to use GETCLOBVAL() function. Below is the example query.
/*********************************************************
*PURPOSE: SQL Query to extract data in XMLTYPE columns *
*AUTHOR: Shailender Thallam *
*********************************************************/
select
fksib.structure_instance_code
, fkcvr.rule_code
, fkcvr.description
, fkcvr.enabled_flag
, fkcvr.start_date_active
, fkcvr.end_date_active
, fkcvr.condition_filter.GETCLOBVAL() as condition_filter
, fkcvr.validation_filter.GETCLOBVAL() as validation_filter
, fmt.message_text
from
fnd_kf_cross_val_rules fkcvr
, fnd_kf_str_instances_b fksib
, fnd_messages_tl fmt
where
fkcvr.structure_instance_id =fksib.structure_instance_id
and fkcvr.error_msg_application_id=fmt.application_id
and fkcvr.error_msg_name =fmt.message_name
and fmt.language ='US'
and fmt.source_lang ='US';