Queries for FIC

1. Getting BU

select distinct bu.bu_id, bu.bu_name from fun_all_business_units_v bu, fun_business_functions_b bf, fun_bu_usages_v buu where bu.bu_id = buu.business_unit_id and bf.business_function_code = 'INCENTIVE_COMPENSATION_BF' and bf.business_function_id = buu.module_id

====================================

2. BU Master Tables

Manage parameter data goes here.
CN_REPOSITORIES_ALL_TL
CN_REPOSITORIES_ALL_b

================================

3. Earning Type
CN_CREDIT_TYPES_ALL_tl
CN_CREDIT_TYPES_ALL_b

=============
4. RULES

CN_RS_RULES_ALL_TL

select A.name, b.* from CN_RS_RULES_ALL_TL A,

              CN_RS_RULES_ALL_B B

WHERE a.rule_id = b.rule_id


select b.name, a.* from CN_RS_RULE_ASSIGNMENTS_ALL a,
              CN_RS_RULES_ALL_tl  b
where a.rule_id = b.rule_id

=======

select b.name, a.rule_id, a.rule_attribute_id , c.* from CN_RS_RULE_ATTRIBUTES_ALL a,

              CN_RS_RULES_ALL_tl  b,

CN_RS_RULE_ATTR_VALUES_ALL c

where a.rule_id = b.rule_id

and 

a.rule_attribute_id = c.rule_attribute_id
============

Query to find credit rules hirerachy
-------------------------

select a.name, b.rule_id, b.enabled_flag, b.parent_rule_id,
b.rank,b.num_winners, b.absolute_rank, b.relative_rank,
b.attr_relation_product
  from CN_RS_RULES_ALL_TL a ,
                   CN_RS_RULES_ALL_B b
where a.rule_id = b.rule_id
connect by prior b.rule_id = b.parent_rule_id
start with b.rule_id = 300000004113199

==========================================================

Rule hierarchy with assignments


select a.name, b.rule_id, b.enabled_flag, b.parent_rule_id,
b.rank,b.num_winners, b.absolute_rank, b.relative_rank,
b.attr_relation_product , c.assigned_object_type,
c.org_id, c.role_id, c.start_date, c.end_date, c.split_pct, c.revenue_type,
 c.rollup_flag, c.group_member_credit_flag,c.summary_flag
  from CN_RS_RULES_ALL_TL a ,
                  (select * from  CN_RS_RULES_ALL_B b  connect by prior b.rule_id = b.parent_rule_id
start with b.rule_id = 300000004113199) b,
CN_RS_RULE_ASSIGNMENTS_ALL c
where a.rule_id = b.rule_id
and a.rule_id = c.rule_id

==============


Rule attribute joins

select b1.rule_attr_value_id,b1.rule_attribute_id, b1.comparison_operator,b1.low_value_char, b1.high_value_char, b1.low_value_number,
b1.high_value_number,
 a1.rule_id, a1.rule_attribute_id, a1.attr_usage_id, a1.org_id
 from CN_RS_RULE_ATTRIBUTES_ALL a1,
              CN_RS_RULE_ATTR_VALUES_ALL b1
 where rule_id in (300000004113199,
300000004115269,
300000004113254,
300000004113255,
300000004113257,
300000004113268
)
and a1.rule_attribute_id = b1.rule_attribute_id
==============

Debug table joins

select l.message_text
 from cn_process_audits_all a, cn_process_audit_lines_all l
where a.request_id = :process_id
  and a.process_audit_id = l.process_audit_id
order by l.process_audit_line_id desc


select l.message_text , l.process_audit_line_id
 from cn_process_audits_all a, cn_process_audit_lines_all l
where a.request_id = :process_id
  and a.process_audit_id = l.process_audit_id
  and l.message_text like '%%'
order by l.process_audit_line_id desc
==========================

Run the following to check restrictions:

1. Get participant_id for the participant to switch
   sql> select participant_id, participant_name from cn_srp_participants_all
         where participant_name like :participant_name 

2.  Check if participant is not used in any credit rule
   sql> select * from cn_rs_rule_assignments_all 
         where assigned_object_id in (:participant_id)

3. Create if participant is associated to transactions and credits
   sql> select participant_id, count(1)
          from cn_tp_transactions_all
         where participant_id in (:participant_id)
      group by participant_id

     select credited_participant_id, count(1)
       from cn_tp_credits_all
      where credited_participant_id in (:participant_id)
    group by credited_participant_id

4.  Check if assigned to compensation plan
    select * from cn_srp_comp_plans_all 

====================================

update cn_srp_participants_all
        set participant_type = 'ANALYST'
      where participant_id  in (:participant_id )
        and participant_type = 'PARTICIPANT' ;
===================


select * from cn_srp_comp_plans_all where participant_id = 40348
==================
Query for finding compensation plans

select a.comp_plan_name, c.plan_component_name , d.formula_sequence, d.formula_weight, d.incentive_formula_flag , e.formula_name , g.expression_name,
h.RENDERED_EXPRESSION_DISP , h.SQL_SELECT , h.SQL_FROM , h.PIPED_SQL_SELECT,
h.PIPED_SQL_FROM
               from CN_COMP_PLANS_ALL_TL a ,
                           CN_COMP_PLAN_COMPONENTS_ALL  b,
                           CN_PLAN_COMPONENTS_ALL_TL c ,
                           CN_PLAN_COMPONENT_FORMULAS_ALL d,
                           CN_FORMULAS_ALL_tl e ,
  CN_FORMULAS_ALL_B f ,
  CN_EXPRESSIONS_ALL_TL g ,
                     CN_EXPRESSIONS_ALL_B h
where a.comp_plan_name = 'Q12015RANK_TEST'
and a.comp_plan_id = b.comp_plan_id
and b.plan_component_id = c.plan_component_id
and b.plan_component_id = d.plan_component_id
and d.formula_id = e.formula_id
and e.formula_id = f.formula_id
and f.output_exp_id = g.expression_id
and g.expression_id = h.expression_id