[Home] [Help]
74: FND_MSG_PUB.ADD;
75: RAISE FND_API.G_EXC_ERROR;
76: END IF;
77:
78: -- Now that the record is validated, we can insert the record in the table BSC_KPI_CAUSE_EFFECT_RELS
79: -- Only create the relation if it already does not exists
80: l_count := 0;
81: SELECT count(*) INTO l_count
82: FROM bsc_kpi_cause_effect_rels
78: -- Now that the record is validated, we can insert the record in the table BSC_KPI_CAUSE_EFFECT_RELS
79: -- Only create the relation if it already does not exists
80: l_count := 0;
81: SELECT count(*) INTO l_count
82: FROM bsc_kpi_cause_effect_rels
83: WHERE
84: cause_indicator = p_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator AND
85: effect_indicator = p_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator AND
86: NVL(cause_level, 'KPI') = p_Bsc_Cause_Effect_Rel_Rec.Cause_Level AND
87: NVL(effect_level, 'KPI') = p_Bsc_Cause_Effect_Rel_Rec.Effect_Level;
88:
89: IF l_count = 0 THEN
90:
91: l_dynamic_sql := 'INSERT INTO bsc_kpi_cause_effect_rels (cause_indicator,effect_indicator,'||
92: 'cause_level,effect_level)VALUES (:1,:2,:3,:4)';
93:
94:
95: EXECUTE IMMEDIATE l_dynamic_sql USING p_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator,p_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator,p_Bsc_Cause_Effect_Rel_Rec.Cause_Level,p_Bsc_Cause_Effect_Rel_Rec.Effect_Level;
138: BEGIN
139:
140: -- I do not need to make validations. It just delete the record if exists.
141: -- If it does not exist it is OK.
142: DELETE FROM bsc_kpi_cause_effect_rels
143: WHERE
144: cause_indicator = p_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator AND
145: effect_indicator = p_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator AND
146: NVL(cause_level, 'KPI') = p_Bsc_Cause_Effect_Rel_Rec.Cause_Level AND
191: BEGIN
192:
193: -- I do not need to make validations. It just delete the records if exists.
194: -- If it does not exist it is OK.
195: DELETE FROM bsc_kpi_cause_effect_rels
196: WHERE
197: (cause_indicator = p_indicator AND NVL(cause_level, 'KPI') = p_level) OR
198: (effect_indicator = p_indicator AND NVL(effect_level, 'KPI') = p_level);
199: