Thursday, September 6, 2012

Query to get the Defaulting rules defined in Order Managment

SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       DECODE (
          src_type,
          'API', src_api_pkg || '.' || src_api_fn,
          'CONSTANT', src_constant_value,
          'SYSTEM', src_system_variable_expr,
          'SEQUENCE', src_sequence_name,
          'DATABASE', src_database_object_name || '.' || src_attribute_code,
          'WAD_ATTR', ' ',
          'WAD_OBJATTR', ' ',
          src_constant_value)
          source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d, oe_lookups l, oe_def_attr_rules_v k
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND d.attribute_code = k.attribute_code
       AND k.src_type NOT IN
              ('RELATED_RECORD', 'SAME_RECORD', 'PROFILE_OPTION')
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND (k.src_type = l.lookup_code
            OR (k.src_type = k.attribute_code AND l.lookup_code = 'CONSTANT'))
UNION
SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       a.object_name || '.' || a.name source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d,
       oe_lookups l,
       oe_def_attr_rules_v k,
       ak_object_attributes_vl a
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND d.attribute_code = k.attribute_code
       AND a.database_object_name(+) = k.src_database_object_name
       AND a.attribute_code(+) = k.src_attribute_code
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND k.src_type = l.lookup_code
       AND k.src_type = 'RELATED_RECORD'
UNION
SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       a.name source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d,
       oe_lookups l,
       oe_def_attr_rules_v k,
       ak_object_attributes_vl a
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND d.attribute_code = k.attribute_code
       AND a.database_object_name(+) = k.database_object_name
       AND a.attribute_code(+) = k.src_attribute_code
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND k.src_type = l.lookup_code
       AND k.src_type = 'SAME_RECORD'
UNION
SELECT d.database_object_display_name,
       d.attribute_display_name,
       d.precedence,
       d.display_name,
       d.system_flag,
       d.enabled_flag,
       k.sequence_no,
       l.meaning,
       a.user_profile_option_name source,
       src_type,
       k.attribute_code
  FROM oe_def_attr_condns_v d,
       oe_lookups l,
       oe_def_attr_rules_v k,
       fnd_profile_options_vl a
 WHERE     d.database_object_name = k.database_object_name
       AND d.attr_def_condition_id = k.attr_def_condition_id
       AND d.condition_id = k.condition_id
       AND a.profile_option_name(+) = k.src_profile_option
       AND l.lookup_type = 'DEFAULTING_SOURCE_TYPE'
       AND k.src_type = l.lookup_code
       AND k.src_type = 'PROFILE_OPTION'