Monday, August 8, 2016

Sales Order Process Constraint SQL Query

SELECT   cc.constraint_id, c.constraint_id, e.entity_display_name entity,
         c.column_display_name ATTRIBUTE, l1.meaning opeartion,
         l2.meaning user_action, l4.meaning seeded, cc.group_number,
         l3.meaning SCOPE, cc.validation_entity_display_name val_entity,
         cc.record_set_display_name record_set,
         DECODE (cc.modifier_flag, 'Y', NULL, '        ') modifier,
         cc.validation_tmplt_display_name val_template,
         l5.meaning seeded_flag
    FROM oe_pc_constraints_v c,
         oe_pc_entities_v e,
         oe_pc_constraint_cnds_v cc,
         oe_lookups l1,
         oe_lookups l2,
         oe_lookups l3,
         oe_lookups l4,
         oe_lookups l5
   WHERE c.entity_id = e.entity_id(+)
     AND l1.lookup_code(+) = c.constrained_operation
     AND l1.lookup_type(+) = 'PC_OPERATION'
     AND l2.lookup_code(+) = c.on_operation_action
     AND l2.lookup_type(+) = 'PC_ON_OPERATION_ACTION'
     AND l4.lookup_code(+) = c.system_flag
     AND l4.lookup_type(+) = 'YES_NO'
     AND c.constraint_id = cc.constraint_id(+)
     AND l3.lookup_code(+) = cc.scope_op
     AND l3.lookup_type(+) = 'PC_SCOPE_OP'
     AND l5.lookup_code(+) = cc.system_flag
     AND l5.lookup_type(+) = 'YES_NO'
     AND e.entity_display_name = 'Order Line'
     AND l1.meaning = 'Cancel'
     --   and cc.validation_tmplt_display_name='Cancel Line'
     AND l2.meaning LIKE 'Require Reason, History and Raise Integration Event'
ORDER BY e.entity_display_name,
         NVL (l1.meaning, 'A'),
         NVL (c.column_display_name, 'A'),
         cc.group_number;

No comments: