Friday 18 November 2016

Query to get the Defaulting rules defined in Order Managment

/* Formatted on 11/18/2016 4:01:35 PM (QP5 v5.114.809.3010) */
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'

4 comments:

  1. Thanks for the statistics. i discovered your blog very interesting ,our oracle R12 fusion financials online training supplier won the high commonplace name through international for its coaching.for more information about please check the site site
    Oracle R12 financials trainings

    ReplyDelete
  2. This is really too useful and have more ideas from yours blog. Very interesting, good job and thanks for sharing such a good blog............
    R12 Project Accounting Online Training

    ReplyDelete
  3. Great post! I am see the great contents and step by step read really nice information.I am gather this concepts and more information. It's helpful for me my friend. Also great blog here with all of the valuable information you have.
    Australia Education Consultants in Chennai

    ReplyDelete