OracleAppsDNA

How to extract XMLTYPE columns of table FND_KF_CROSS_VAL_RULES

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';

Exit mobile version