1 PACKAGE BODY BSC_CAUSE_EFFECT_REL_PVT AS
2 /* $Header: BSCVCAEB.pls 120.0 2005/06/01 16:04:30 appldev noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BSC_CAUSE_EFFECT_REL_PVT';
5 g_db_object VARCHAR2(30) := NULL;
6
7 PROCEDURE Create_Cause_Effect_Rel(
8 p_commit IN VARCHAR2 := FND_API.G_FALSE
9 ,p_Bsc_Cause_Effect_Rel_Rec IN BSC_CAUSE_EFFECT_REL_PUB.Bsc_Cause_Effect_Rel_Rec
10 ,x_return_status OUT NOCOPY VARCHAR2
11 ,x_msg_count OUT NOCOPY NUMBER
12 ,x_msg_data OUT NOCOPY VARCHAR2
13 ) IS
14
15 l_count NUMBER;
16 l_dynamic_sql VARCHAR2(32000);
17
18 BEGIN
19
20 -- Check that all the information is provided
21 IF p_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator IS NULL THEN
22 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_CAUSE_INDICATOR');
23 FND_MSG_PUB.ADD;
24 RAISE FND_API.G_EXC_ERROR;
25 END IF;
26
27 IF p_Bsc_Cause_Effect_Rel_Rec.Cause_Level NOT IN ('KPI', 'DATASET') THEN
28 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_CAUSE_LEVEL');
29 FND_MSG_PUB.ADD;
30 RAISE FND_API.G_EXC_ERROR;
31 END IF;
32
33 IF p_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator IS NULL THEN
34 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_EFFECT_INDICATOR');
35 FND_MSG_PUB.ADD;
36 RAISE FND_API.G_EXC_ERROR;
37 END IF;
38
39 IF p_Bsc_Cause_Effect_Rel_Rec.Effect_Level NOT IN ('KPI', 'DATASET') THEN
40 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_EFFECT_LEVEL');
41 FND_MSG_PUB.ADD;
42 RAISE FND_API.G_EXC_ERROR;
43 END IF;
44
45 -- Check that the kpi (if KPI level) or dataset (DATASET level) already exists
46 l_count := 0;
47 IF p_Bsc_Cause_Effect_Rel_Rec.Cause_Level = 'KPI' THEN
48 SELECT count(*) INTO l_count
49 FROM bsc_kpis_b
50 WHERE indicator = p_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator;
51 ELSE
52 SELECT count(*) INTO l_count
53 FROM bsc_sys_datasets_b
54 WHERE dataset_id = p_Bsc_Cause_Effect_Rel_Rec.Cause_Indicator;
55 END IF;
56 IF l_count = 0 THEN
57 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_CAUSE_INDICATOR');
58 FND_MSG_PUB.ADD;
59 RAISE FND_API.G_EXC_ERROR;
60 END IF;
61
62 l_count := 0;
63 IF p_Bsc_Cause_Effect_Rel_Rec.Effect_Level = 'KPI' THEN
64 SELECT count(*) INTO l_count
65 FROM bsc_kpis_b
66 WHERE indicator = p_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator;
67 ELSE
68 SELECT count(*) INTO l_count
69 FROM bsc_sys_datasets_b
70 WHERE dataset_id = p_Bsc_Cause_Effect_Rel_Rec.Effect_Indicator;
71 END IF;
72 IF l_count = 0 THEN
73 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_EFFECT_INDICATOR');
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
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;
96 END IF;
97 IF (p_commit = FND_API.G_TRUE) THEN
98 COMMIT;
99 END IF;
100
101 EXCEPTION
102 WHEN FND_API.G_EXC_ERROR THEN
103 rollback;
104 x_return_status := FND_API.G_RET_STS_ERROR;
105 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
106 ,p_data => x_msg_data);
107 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
108 rollback;
109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
111 ,p_data => x_msg_data);
112 WHEN NO_DATA_FOUND THEN
113 rollback;
114 x_return_status := FND_API.G_RET_STS_ERROR;
115 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
116 ,p_data => x_msg_data);
117 WHEN OTHERS THEN
118 rollback;
119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
120 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
121 ,p_data => x_msg_data);
122
123 END Create_Cause_Effect_Rel;
124
125 /************************************************************************************
126 ************************************************************************************/
127
128 PROCEDURE Delete_Cause_Effect_Rel(
129 p_commit IN VARCHAR2 := FND_API.G_FALSE
130 ,p_Bsc_Cause_Effect_Rel_Rec IN BSC_CAUSE_EFFECT_REL_PUB.Bsc_Cause_Effect_Rel_Rec
131 ,x_return_status OUT NOCOPY VARCHAR2
132 ,x_msg_count OUT NOCOPY NUMBER
133 ,x_msg_data OUT NOCOPY VARCHAR2
134 ) IS
135
136 l_count NUMBER;
137
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
147 NVL(effect_level, 'KPI') = p_Bsc_Cause_Effect_Rel_Rec.Effect_Level;
148
149 IF (p_commit = FND_API.G_TRUE) THEN
150 COMMIT;
151 END IF;
152
153 EXCEPTION
154 WHEN FND_API.G_EXC_ERROR THEN
155 rollback;
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
158 ,p_data => x_msg_data);
159 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
160 rollback;
161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
163 ,p_data => x_msg_data);
164 WHEN NO_DATA_FOUND THEN
165 rollback;
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
168 ,p_data => x_msg_data);
169 WHEN OTHERS THEN
170 rollback;
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
173 ,p_data => x_msg_data);
174
175 END Delete_Cause_Effect_Rel;
176
177 /************************************************************************************
178 ************************************************************************************/
179
180 PROCEDURE Delete_All_Cause_Effect_Rels(
181 p_commit IN VARCHAR2 := FND_API.G_FALSE
182 ,p_indicator IN NUMBER
183 ,p_level IN VARCHAR2
184 ,x_return_status OUT NOCOPY VARCHAR2
185 ,x_msg_count OUT NOCOPY NUMBER
186 ,x_msg_data OUT NOCOPY VARCHAR2
187 ) IS
188
189 l_count NUMBER;
190
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
200 IF (p_commit = FND_API.G_TRUE) THEN
201 COMMIT;
202 END IF;
203
204 EXCEPTION
205 WHEN FND_API.G_EXC_ERROR THEN
206 rollback;
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
209 ,p_data => x_msg_data);
210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
211 rollback;
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
214 ,p_data => x_msg_data);
215 WHEN NO_DATA_FOUND THEN
216 rollback;
217 x_return_status := FND_API.G_RET_STS_ERROR;
218 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
219 ,p_data => x_msg_data);
220 WHEN OTHERS THEN
221 rollback;
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
223 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
224 ,p_data => x_msg_data);
225
226 END Delete_All_Cause_Effect_Rels;
227
228 /************************************************************************************
229 ************************************************************************************/
230
231 END BSC_CAUSE_EFFECT_REL_PVT;
[Home] [Help]