[Home] [Help]
PACKAGE BODY: APPS.OKL_VP_RULE_PUB
Source
1 PACKAGE BODY OKL_VP_RULE_PUB AS
2 /* $Header: OKLPRLGB.pls 115.5 2004/04/21 06:46:33 rnaik noship $ */
3 G_GEN_COMMENTS VARCHAR2(1) := 'T';
4
5 ---------------------------------------------------------------------------
6 -- FUNCTION migrate_rgpv
7 ---------------------------------------------------------------------------
8 FUNCTION migrate_rgpv (
9 p_rgpv_rec1 IN rgpv_rec_type,
10 p_rgpv_rec2 IN rgpv_rec_type
11 ) RETURN rgpv_rec_type IS
12 l_rgpv_rec rgpv_rec_type;
13 BEGIN
14 l_rgpv_rec.id := p_rgpv_rec1.id;
15 l_rgpv_rec.object_version_number := p_rgpv_rec1.object_version_number;
16 l_rgpv_rec.created_by := p_rgpv_rec1.created_by;
17 l_rgpv_rec.creation_date := p_rgpv_rec1.creation_date;
18 l_rgpv_rec.last_updated_by := p_rgpv_rec1.last_updated_by;
19 l_rgpv_rec.last_update_date := p_rgpv_rec1.last_update_date;
20 l_rgpv_rec.last_update_login := p_rgpv_rec1.last_update_login;
21 l_rgpv_rec.rgd_code := p_rgpv_rec2.rgd_code;
22 l_rgpv_rec.sat_code := p_rgpv_rec2.sat_code;
23 l_rgpv_rec.rgp_type := p_rgpv_rec2.rgp_type;
24 l_rgpv_rec.cle_id := p_rgpv_rec2.cle_id;
25 l_rgpv_rec.chr_id := p_rgpv_rec2.chr_id;
26 l_rgpv_rec.dnz_chr_id := p_rgpv_rec2.dnz_chr_id;
27 l_rgpv_rec.parent_rgp_id := p_rgpv_rec2.parent_rgp_id;
28 l_rgpv_rec.sfwt_flag := p_rgpv_rec2.sfwt_flag;
29 l_rgpv_rec.comments := p_rgpv_rec2.comments;
30 l_rgpv_rec.attribute_category := p_rgpv_rec2.attribute_category;
31 l_rgpv_rec.attribute1 := p_rgpv_rec2.attribute1;
32 l_rgpv_rec.attribute2 := p_rgpv_rec2.attribute2;
33 l_rgpv_rec.attribute3 := p_rgpv_rec2.attribute3;
34 l_rgpv_rec.attribute4 := p_rgpv_rec2.attribute4;
35 l_rgpv_rec.attribute5 := p_rgpv_rec2.attribute5;
36 l_rgpv_rec.attribute6 := p_rgpv_rec2.attribute6;
37 l_rgpv_rec.attribute7 := p_rgpv_rec2.attribute7;
38 l_rgpv_rec.attribute8 := p_rgpv_rec2.attribute8;
39 l_rgpv_rec.attribute9 := p_rgpv_rec2.attribute9;
40 l_rgpv_rec.attribute10 := p_rgpv_rec2.attribute10;
41 l_rgpv_rec.attribute11 := p_rgpv_rec2.attribute11;
42 l_rgpv_rec.attribute12 := p_rgpv_rec2.attribute12;
43 l_rgpv_rec.attribute13 := p_rgpv_rec2.attribute13;
44 l_rgpv_rec.attribute14 := p_rgpv_rec2.attribute14;
45 l_rgpv_rec.attribute15 := p_rgpv_rec2.attribute15;
46 RETURN (l_rgpv_rec);
47 END migrate_rgpv;
48 --------------------------------------
49 -- PROCEDURE create_rule_group
50 --------------------------------------
51 PROCEDURE create_rule_group(
52 p_api_version IN NUMBER,
53 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
54 x_return_status OUT NOCOPY VARCHAR2,
55 x_msg_count OUT NOCOPY NUMBER,
56 x_msg_data OUT NOCOPY VARCHAR2,
57 p_rgpv_rec IN rgpv_rec_type,
58 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
59 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
60 l_api_name CONSTANT VARCHAR2(30) := 'create_rule_group';
61 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
62 BEGIN
63 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
64 p_init_msg_list,
65 '_PUB',
66 x_return_status);
67 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
68 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
69 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
70 raise OKC_API.G_EXCEPTION_ERROR;
71 END IF;
72 g_rgpv_rec := p_rgpv_rec;
73 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
74 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
75 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
76 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
77 raise OKC_API.G_EXCEPTION_ERROR;
78 END IF;
79 l_rgpv_rec := migrate_rgpv(l_rgpv_rec, g_rgpv_rec);
80 OKL_VP_RULE_PVT.create_rule_group(
81 p_api_version => p_api_version,
82 p_init_msg_list => p_init_msg_list,
83 x_return_status => x_return_status,
84 x_msg_count => x_msg_count,
85 x_msg_data => x_msg_data,
86 p_rgpv_rec => l_rgpv_rec,
87 x_rgpv_rec => x_rgpv_rec);
88 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
89 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
90 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
91 raise OKC_API.G_EXCEPTION_ERROR;
92 END IF;
93 g_rgpv_rec := x_rgpv_rec;
94 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
95 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
96 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
97 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
98 raise OKC_API.G_EXCEPTION_ERROR;
99 END IF;
100 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
101 EXCEPTION
102 WHEN OKC_API.G_EXCEPTION_ERROR THEN
103 x_return_status := OKC_API.HANDLE_EXCEPTIONS
104 (l_api_name
105 ,G_PKG_NAME
106 ,'OKC_API.G_RET_STS_ERROR'
107 ,x_msg_count
108 ,x_msg_data
109 ,'_PUB');
110 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
111 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
112 (l_api_name
113 ,G_PKG_NAME
114 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
115 ,x_msg_count
116 ,x_msg_data
117 ,'_PUB');
118 WHEN OTHERS THEN
119 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
120 (l_api_name
121 ,G_PKG_NAME
122 ,'OTHERS'
123 ,x_msg_count
124 ,x_msg_data
125 ,'_PUB');
126 END create_rule_group;
127 --------------------------------------
128 -- PROCEDURE create_rule_group
129 --------------------------------------
130 PROCEDURE create_rule_group(
131 p_api_version IN NUMBER,
132 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_msg_count OUT NOCOPY NUMBER,
135 x_msg_data OUT NOCOPY VARCHAR2,
136 p_rgpv_tbl IN rgpv_tbl_type,
137 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type) IS
138 i NUMBER := 0;
139 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
140 BEGIN
141 x_return_status := OKC_API.G_RET_STS_SUCCESS;
142 IF p_rgpv_tbl.COUNT > 0 THEN
143 i := p_rgpv_tbl.FIRST;
144 LOOP
145 create_rule_group(
146 p_api_version => p_api_version,
147 p_init_msg_list => p_init_msg_list,
148 x_return_status => l_return_status,
149 x_msg_count => x_msg_count,
150 x_msg_data => x_msg_data,
151 p_rgpv_rec => p_rgpv_tbl(i),
152 x_rgpv_rec => x_rgpv_tbl(i));
153 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
154 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
155 x_return_status := l_return_status;
156 raise G_EXCEPTION_HALT_VALIDATION;
157 ELSE
158 x_return_status := l_return_status;
159 END IF;
160 END IF;
161 EXIT WHEN (i = p_rgpv_tbl.LAST);
162 i := p_rgpv_tbl.NEXT(i);
163 END LOOP;
164 END IF;
165 EXCEPTION
166 WHEN G_EXCEPTION_HALT_VALIDATION THEN
167 NULL;
168 WHEN OTHERS THEN
169 -- store SQL error message on message stack
170 OKC_API.SET_MESSAGE(
171 p_app_name => G_APP_NAME,
172 p_msg_name => G_UNEXPECTED_ERROR,
173 p_token1 => G_SQLCODE_TOKEN,
174 p_token1_value => SQLCODE,
175 p_token2 => G_SQLERRM_TOKEN,
176 p_token2_value => SQLERRM);
177 -- notify caller of an error as UNEXPETED error
178 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
179 END create_rule_group;
180 --------------------------------------
181 -- PROCEDURE update_rule_group
182 --------------------------------------
183 PROCEDURE update_rule_group(
184 p_api_version IN NUMBER,
185 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
186 x_return_status OUT NOCOPY VARCHAR2,
187 x_msg_count OUT NOCOPY NUMBER,
188 x_msg_data OUT NOCOPY VARCHAR2,
189 p_rgpv_rec IN rgpv_rec_type,
190 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
191 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
192 l_api_name CONSTANT VARCHAR2(30) := 'update_rule_group';
193 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
194 BEGIN
195 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
196 p_init_msg_list,
197 '_PUB',
198 x_return_status);
199 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
200 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
201 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
202 raise OKC_API.G_EXCEPTION_ERROR;
203 END IF;
204 g_rgpv_rec := p_rgpv_rec;
205 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
206 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
207 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
208 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
209 raise OKC_API.G_EXCEPTION_ERROR;
210 END IF;
211 l_rgpv_rec := migrate_rgpv(l_rgpv_rec, g_rgpv_rec);
212 OKL_VP_RULE_PVT.update_rule_group(
213 p_api_version => p_api_version,
214 p_init_msg_list => p_init_msg_list,
215 x_return_status => x_return_status,
216 x_msg_count => x_msg_count,
217 x_msg_data => x_msg_data,
218 p_rgpv_rec => l_rgpv_rec,
219 x_rgpv_rec => x_rgpv_rec);
220 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
221 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
222 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
223 raise OKC_API.G_EXCEPTION_ERROR;
224 END IF;
225 g_rgpv_rec := x_rgpv_rec;
226 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
227 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
228 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
229 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
230 raise OKC_API.G_EXCEPTION_ERROR;
231 END IF;
232 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
233 EXCEPTION
234 WHEN OKC_API.G_EXCEPTION_ERROR THEN
235 x_return_status := OKC_API.HANDLE_EXCEPTIONS
236 (l_api_name
237 ,G_PKG_NAME
238 ,'OKC_API.G_RET_STS_ERROR'
239 ,x_msg_count
240 ,x_msg_data
241 ,'_PUB');
242 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
243 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
244 (l_api_name
245 ,G_PKG_NAME
246 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
247 ,x_msg_count
248 ,x_msg_data
249 ,'_PUB');
250 WHEN OTHERS THEN
251 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
252 (l_api_name
253 ,G_PKG_NAME
254 ,'OTHERS'
255 ,x_msg_count
256 ,x_msg_data
257 ,'_PUB');
258 END update_rule_group;
259 --------------------------------------
260 -- PROCEDURE update_rule_group
261 --------------------------------------
262 PROCEDURE update_rule_group(
263 p_api_version IN NUMBER,
264 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
265 x_return_status OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER,
267 x_msg_data OUT NOCOPY VARCHAR2,
268 p_rgpv_tbl IN rgpv_tbl_type,
269 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type) IS
270 i NUMBER := 0;
271 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
272 BEGIN
273 x_return_status := OKC_API.G_RET_STS_SUCCESS;
274 IF p_rgpv_tbl.COUNT > 0 THEN
275 i := p_rgpv_tbl.FIRST;
276 LOOP
277 update_rule_group(
278 p_api_version => p_api_version,
279 p_init_msg_list => p_init_msg_list,
280 x_return_status => l_return_status,
281 x_msg_count => x_msg_count,
282 x_msg_data => x_msg_data,
283 p_rgpv_rec => p_rgpv_tbl(i),
284 x_rgpv_rec => x_rgpv_tbl(i));
285 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
286 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
287 x_return_status := l_return_status;
288 raise G_EXCEPTION_HALT_VALIDATION;
289 ELSE
290 x_return_status := l_return_status;
291 END IF;
292 END IF;
293 EXIT WHEN (i = p_rgpv_tbl.LAST);
294 i := p_rgpv_tbl.NEXT(i);
295 END LOOP;
296 END IF;
297 EXCEPTION
298 WHEN G_EXCEPTION_HALT_VALIDATION THEN
299 NULL;
300 WHEN OTHERS THEN
301 -- store SQL error message on message stack
302 OKC_API.SET_MESSAGE(
303 p_app_name => G_APP_NAME,
304 p_msg_name => G_UNEXPECTED_ERROR,
305 p_token1 => G_SQLCODE_TOKEN,
306 p_token1_value => SQLCODE,
307 p_token2 => G_SQLERRM_TOKEN,
308 p_token2_value => SQLERRM);
309 -- notify caller of an error as UNEXPETED error
310 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
311 END update_rule_group;
312 --------------------------------------
313 -- PROCEDURE delete_rule_group
314 --------------------------------------
315 PROCEDURE delete_rule_group(
316 p_api_version IN NUMBER,
317 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
318 x_return_status OUT NOCOPY VARCHAR2,
319 x_msg_count OUT NOCOPY NUMBER,
320 x_msg_data OUT NOCOPY VARCHAR2,
321 p_rgpv_rec IN rgpv_rec_type) IS
322 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
323 l_api_name CONSTANT VARCHAR2(30) := 'delete_rule_group';
324 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
325 BEGIN
326 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
327 p_init_msg_list,
328 '_PUB',
329 x_return_status);
330 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
331 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
332 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
333 raise OKC_API.G_EXCEPTION_ERROR;
334 END IF;
335 g_rgpv_rec := p_rgpv_rec;
336 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
337 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
338 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
339 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
340 raise OKC_API.G_EXCEPTION_ERROR;
341 END IF;
342 OKL_VP_RULE_PVT.delete_rule_group(
343 p_api_version => p_api_version,
344 p_init_msg_list => p_init_msg_list,
345 x_return_status => x_return_status,
346 x_msg_count => x_msg_count,
347 x_msg_data => x_msg_data,
348 p_rgpv_rec => p_rgpv_rec);
349 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
350 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
351 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
352 raise OKC_API.G_EXCEPTION_ERROR;
353 END IF;
354 g_rgpv_rec := l_rgpv_rec;
355 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
356 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
357 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
358 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
359 raise OKC_API.G_EXCEPTION_ERROR;
360 END IF;
361 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
362 EXCEPTION
363 WHEN OKC_API.G_EXCEPTION_ERROR THEN
364 x_return_status := OKC_API.HANDLE_EXCEPTIONS
365 (l_api_name
366 ,G_PKG_NAME
367 ,'OKC_API.G_RET_STS_ERROR'
368 ,x_msg_count
369 ,x_msg_data
370 ,'_PUB');
371 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
372 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
373 (l_api_name
374 ,G_PKG_NAME
375 ,'OKC_API.G_RET_STS_UNEXP_ERROR'
376 ,x_msg_count
377 ,x_msg_data
378 ,'_PUB');
379 WHEN OTHERS THEN
380 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
381 (l_api_name
382 ,G_PKG_NAME
383 ,'OTHERS'
384 ,x_msg_count
385 ,x_msg_data
386 ,'_PUB');
387 END delete_rule_group;
388 --------------------------------------
389 -- PROCEDURE delete_rule_group
390 --------------------------------------
391 PROCEDURE delete_rule_group(
392 p_api_version IN NUMBER,
393 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER,
396 x_msg_data OUT NOCOPY VARCHAR2,
397 p_rgpv_tbl IN rgpv_tbl_type) IS
398 i NUMBER := 0;
399 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
400 BEGIN
401 x_return_status := OKC_API.G_RET_STS_SUCCESS;
402 IF p_rgpv_tbl.COUNT > 0 THEN
403 i := p_rgpv_tbl.FIRST;
404 LOOP
405 delete_rule_group(
406 p_api_version => p_api_version,
407 p_init_msg_list => p_init_msg_list,
408 x_return_status => l_return_status,
409 x_msg_count => x_msg_count,
410 x_msg_data => x_msg_data,
411 p_rgpv_rec => p_rgpv_tbl(i));
412 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
413 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
414 x_return_status := l_return_status;
415 raise G_EXCEPTION_HALT_VALIDATION;
416 ELSE
417 x_return_status := l_return_status;
418 END IF;
419 END IF;
420 EXIT WHEN (i = p_rgpv_tbl.LAST);
421 i := p_rgpv_tbl.NEXT(i);
422 END LOOP;
423 END IF;
424 EXCEPTION
425 WHEN G_EXCEPTION_HALT_VALIDATION THEN
426 NULL;
427 WHEN OTHERS THEN
428 -- store SQL error message on message stack
429 OKC_API.SET_MESSAGE(
430 p_app_name => G_APP_NAME,
431 p_msg_name => G_UNEXPECTED_ERROR,
432 p_token1 => G_SQLCODE_TOKEN,
433 p_token1_value => SQLCODE,
434 p_token2 => G_SQLERRM_TOKEN,
435 p_token2_value => SQLERRM);
436 -- notify caller of an error as UNEXPETED error
437 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
438 END delete_rule_group;
439
440 END OKL_VP_RULE_PUB;