[Home] [Help]
PACKAGE BODY: APPS.BIS_CUSTOM_CAUSE_EFFECT_PVT
Source
1 PACKAGE BODY BIS_CUSTOM_CAUSE_EFFECT_PVT AS
2 /* $Header: BISVCECB.pls 120.0 2006/08/04 17:13:46 appldev noship $ */
3
4 PROCEDURE Create_Custom_Cause_Effect_Rel(
5 p_commit IN VARCHAR2 := FND_API.G_FALSE
6 ,p_Cause_Short_Name IN bis_custom_cause_effect_rels.cause_short_name%TYPE
7 ,p_Effect_Short_Name IN bis_custom_cause_effect_rels.effect_short_name%TYPE
8 ,p_Cause_Sequence IN bis_custom_cause_effect_rels.cause_sequence%TYPE
9 ,p_Effect_Sequence IN bis_custom_cause_effect_rels.effect_sequence%TYPE
10 ,x_return_status OUT NOCOPY VARCHAR2
11 ,x_msg_count OUT NOCOPY NUMBER
12 ,x_msg_data OUT NOCOPY VARCHAR2
13 ) IS
14 BEGIN
15
16 FND_MSG_PUB.Initialize;
17 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
18
19 IF p_Cause_Short_Name IS NULL THEN
20 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_CAUSE_MEASURE');
21 FND_MSG_PUB.ADD;
22 RAISE FND_API.G_EXC_ERROR;
23 END IF;
24
25 IF p_Effect_Short_Name IS NULL THEN
26 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EFFECT_MEASURE');
27 FND_MSG_PUB.ADD;
28 RAISE FND_API.G_EXC_ERROR;
29 END IF;
30
31 IF p_Cause_Sequence IS NULL OR p_Cause_Sequence = FND_API.G_MISS_NUM THEN
32 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_CAUSE_SEQUENCE');
33 FND_MSG_PUB.ADD;
34 RAISE FND_API.G_EXC_ERROR;
35 END IF;
36
37 IF p_Effect_Sequence IS NULL OR p_Effect_Sequence = FND_API.G_MISS_NUM THEN
38 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EFFECT_SEQUENCE');
39 FND_MSG_PUB.ADD;
40 RAISE FND_API.G_EXC_ERROR;
41 END IF;
42
43 INSERT INTO BIS_CUSTOM_CAUSE_EFFECT_RELS
44 (
45 ID,
46 CAUSE_SHORT_NAME,
47 EFFECT_SHORT_NAME,
48 CAUSE_SEQUENCE,
49 EFFECT_SEQUENCE,
50 CREATED_BY,
51 CREATION_DATE,
52 LAST_UPDATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATE_LOGIN
55 )
56 VALUES
57 (
58 BIS_CAUSE_EFFECT_S.nextVal,
59 p_Cause_Short_Name,
60 p_Effect_Short_Name,
61 p_Cause_Sequence,
62 p_Effect_Sequence,
63 FND_GLOBAL.USER_ID,
64 SYSDATE,
65 FND_GLOBAL.USER_ID,
66 SYSDATE,
67 FND_GLOBAL.LOGIN_ID
68 );
69
70 IF (p_commit = FND_API.G_TRUE) THEN
71 COMMIT;
72 END IF;
73
74 EXCEPTION
75 WHEN OTHERS THEN
76 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
77 IF (x_msg_data IS NOT NULL) THEN
78 x_msg_data := x_msg_data||' -> BIS_CUSTOM_CAUSE_EFFECT_PVT.Create_Custom_Cause_Effect_Rel ';
79 ELSE
80 x_msg_data := SQLERRM||' at BIS_CUSTOM_CAUSE_EFFECT_PVT.Create_Custom_Cause_Effect_Rel ';
81 END IF;
82 END Create_Custom_Cause_Effect_Rel;
83
84 PROCEDURE Update_Custom_Cause_Effect_Rel(
85 p_commit IN VARCHAR2 := FND_API.G_FALSE
86 ,p_Cause_Short_Name IN bis_custom_cause_effect_rels.cause_short_name%TYPE
87 ,p_Effect_Short_Name IN bis_custom_cause_effect_rels.effect_short_name%TYPE
88 ,p_Cause_Sequence IN bis_custom_cause_effect_rels.cause_sequence%TYPE
89 ,p_Effect_Sequence IN bis_custom_cause_effect_rels.effect_sequence%TYPE
90 ,x_return_status OUT NOCOPY VARCHAR2
91 ,x_msg_count OUT NOCOPY NUMBER
92 ,x_msg_data OUT NOCOPY VARCHAR2
93 )IS
94
95 l_count NUMBER := 0;
96 CURSOR rec_Count(x_cause_short_name IN bis_custom_cause_effect_rels.cause_short_name%TYPE,x_effect_short_name IN bis_custom_cause_effect_rels.effect_short_name%TYPE)
97 IS
98 SELECT COUNT(1)
99 FROM bis_custom_cause_effect_rels
100 WHERE cause_short_name = x_cause_short_name
101 AND effect_short_name = x_effect_short_name;
102
103 BEGIN
104 FND_MSG_PUB.Initialize;
105 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
106
107 IF p_Cause_Short_Name IS NULL THEN
108 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_CAUSE_MEASURE');
109 FND_MSG_PUB.ADD;
110 RAISE FND_API.G_EXC_ERROR;
111 END IF;
112
113 IF p_Effect_Short_Name IS NULL THEN
114 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EFFECT_MEASURE');
115 FND_MSG_PUB.ADD;
116 RAISE FND_API.G_EXC_ERROR;
117 END IF;
118
119 IF p_Cause_Sequence IS NULL OR p_Cause_Sequence = FND_API.G_MISS_NUM THEN
120 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_CAUSE_SEQUENCE');
121 FND_MSG_PUB.ADD;
122 RAISE FND_API.G_EXC_ERROR;
123 END IF;
124
125 IF p_Effect_Sequence IS NULL OR p_Effect_Sequence = FND_API.G_MISS_NUM THEN
126 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EFFECT_SEQUENCE');
127 FND_MSG_PUB.ADD;
128 RAISE FND_API.G_EXC_ERROR;
129 END IF;
130
131 OPEN rec_Count(p_Cause_Short_Name,p_Effect_Short_Name);
132 FETCH rec_Count INTO l_count;
133 CLOSE rec_Count;
134
135 IF l_count > 0 THEN
136 UPDATE bis_custom_cause_effect_rels
137 SET
138 cause_sequence = p_Cause_Sequence
139 , effect_sequence = p_Effect_Sequence
140 , last_updated_by = FND_GLOBAL.USER_ID
141 , last_update_date = SYSDATE
142 , last_update_login = FND_GLOBAL.LOGIN_ID
143 WHERE
144 cause_short_name = p_Cause_Short_Name AND
145 effect_short_name = p_Effect_Short_Name;
146 END IF;
147
148 IF (p_commit = FND_API.G_TRUE) THEN
149 COMMIT;
150 END IF;
151
152 EXCEPTION
153 WHEN OTHERS THEN
154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155 IF (x_msg_data IS NOT NULL) THEN
156 x_msg_data := x_msg_data||' -> BIS_CUSTOM_CAUSE_EFFECT_PVT.Update_Custom_Cause_Effect_Rel ';
157 ELSE
158 x_msg_data := SQLERRM||' at BIS_CUSTOM_CAUSE_EFFECT_PVT.Update_Custom_Cause_Effect_Rel ';
159 END IF;
160 END Update_Custom_Cause_Effect_Rel;
161
162
163 PROCEDURE Delete_Custom_Cause_Effect_Rel(
164 p_commit IN VARCHAR2 := FND_API.G_FALSE
165 ,p_Cause_Short_Name IN bis_custom_cause_effect_rels.cause_short_name%TYPE
166 ,p_Effect_Short_Name IN bis_custom_cause_effect_rels.effect_short_name%TYPE
167 ,x_return_status OUT NOCOPY VARCHAR2
168 ,x_msg_count OUT NOCOPY NUMBER
169 ,x_msg_data OUT NOCOPY VARCHAR2
170 ) IS
171 l_count NUMBER := 0;
172 CURSOR rec_Count(x_cause_short_name IN bis_custom_cause_effect_rels.cause_short_name%TYPE,x_effect_short_name IN bis_custom_cause_effect_rels.effect_short_name%TYPE)
173 IS
174 SELECT COUNT(1)
175 FROM bis_custom_cause_effect_rels
176 WHERE cause_short_name = x_cause_short_name
177 AND effect_short_name = x_effect_short_name;
178
179 BEGIN
180 FND_MSG_PUB.Initialize;
181 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
182
183 IF p_Cause_Short_Name IS NULL THEN
184 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_CAUSE_MEASURE');
185 FND_MSG_PUB.ADD;
186 RAISE FND_API.G_EXC_ERROR;
187 END IF;
188
189 IF p_Effect_Short_Name IS NULL THEN
190 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EFFECT_MEASURE');
191 FND_MSG_PUB.ADD;
192 RAISE FND_API.G_EXC_ERROR;
193 END IF;
194
195 OPEN rec_Count(p_Cause_Short_Name,p_Effect_Short_Name);
196 FETCH rec_Count INTO l_count;
197 CLOSE rec_Count;
198
199 IF l_count > 0 THEN
200 DELETE FROM bis_custom_cause_effect_rels
201 WHERE
202 cause_short_name = p_Cause_Short_Name AND
203 effect_short_name = p_Effect_Short_Name;
204 END IF;
205
206 IF (p_commit = FND_API.G_TRUE) THEN
207 COMMIT;
208 END IF;
209
210 EXCEPTION
211 WHEN OTHERS THEN
212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
213 IF (x_msg_data IS NOT NULL) THEN
214 x_msg_data := x_msg_data||' -> BIS_CUSTOM_CAUSE_EFFECT_PVT.Delete_Custom_Cause_Effect_Rel ';
215 ELSE
216 x_msg_data := SQLERRM||' at BIS_CUSTOM_CAUSE_EFFECT_PVT.Delete_Custom_Cause_Effect_Rel ';
217 END IF;
218
219 END Delete_Custom_Cause_Effect_Rel;
220
221
222 PROCEDURE Delete_Custom_Cause_Effect_Rel(
223 p_commit IN VARCHAR2 := FND_API.G_FALSE
224 ,p_Cause_DataSetId IN bis_indicators.dataset_id%TYPE
225 ,p_Effect_DataSetId IN bis_indicators.dataset_id%TYPE
226 ,x_return_status OUT NOCOPY VARCHAR2
227 ,x_msg_count OUT NOCOPY NUMBER
228 ,x_msg_data OUT NOCOPY VARCHAR2
229 )
230 IS
231 l_Cause_Short_Name bis_custom_cause_effect_rels.cause_short_name%TYPE;
232 l_Effect_Short_Name bis_custom_cause_effect_rels.cause_short_name%TYPE;
233
234 CURSOR c_short_Name(l_dataset_id NUMBER)
235 IS
236 SELECT
237 short_name
238 FROM
239 bis_indicators
240 WHERE
241 dataset_id = l_dataset_id;
242
243 BEGIN
244 FND_MSG_PUB.Initialize;
245 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
246
247 IF p_Cause_DataSetId IS NULL THEN
248 FND_MESSAGE.SET_NAME('BIS','BSC_INVALID_CAUSE_INDICATOR');
249 FND_MSG_PUB.ADD;
250 RAISE FND_API.G_EXC_ERROR;
251 END IF;
252
253 IF p_Effect_DataSetId IS NULL THEN
254 FND_MESSAGE.SET_NAME('BIS','BSC_INVALID_EFFECT_INDICATOR');
255 FND_MSG_PUB.ADD;
256 RAISE FND_API.G_EXC_ERROR;
257 END IF;
258
259 OPEN c_short_Name(p_Cause_DataSetId);
260 FETCH c_short_Name INTO l_Cause_Short_Name;
261 CLOSE c_short_Name;
262
263 OPEN c_short_Name(p_Effect_DataSetId);
264 FETCH c_short_Name INTO l_Effect_Short_Name;
265 CLOSE c_short_Name;
266
267 Delete_Custom_Cause_Effect_Rel(
268 p_commit => p_commit
269 , p_Cause_Short_Name => l_Cause_Short_Name
270 , p_Effect_Short_Name => l_Effect_Short_Name
271 , x_return_status => x_return_status
272 , x_msg_count => x_msg_count
273 , x_msg_data => x_msg_data
274 );
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 IF (x_msg_data IS NOT NULL) THEN
280 x_msg_data := x_msg_data||' -> BIS_CUSTOM_CAUSE_EFFECT_PVT.Delete_Custom_Cause_Effect_Rel ';
281 ELSE
282 x_msg_data := SQLERRM||' at BIS_CUSTOM_CAUSE_EFFECT_PVT.Delete_Custom_Cause_Effect_Rel ';
283 END IF;
284
285 END Delete_Custom_Cause_Effect_Rel;
286
287 END BIS_CUSTOM_CAUSE_EFFECT_PVT;