[Home] [Help]
PACKAGE BODY: APPS.OKS_REV_DISTR_PUB
Source
1 Package Body OKS_REV_DISTR_PUB AS
2 /* $Header: OKSPRDSB.pls 120.1 2006/01/13 04:17:52 mchoudha noship $ */
3
4 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
5 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKS_REV_DISTR_PUB';
6 G_rdsV_REC rdsV_REC_TYPE;
7 procedure reset(p_rdsv_rec IN rdsv_rec_type) is
8 begin
9 g_rdsv_rec.id := p_rdsv_rec.id;
10 g_rdsv_rec.object_version_number := p_rdsv_rec.object_version_number;
11 g_rdsv_rec.created_by := p_rdsv_rec.created_by;
12 g_rdsv_rec.creation_date := p_rdsv_rec.creation_date;
13 g_rdsv_rec.last_updated_by := p_rdsv_rec.last_updated_by;
14 g_rdsv_rec.last_update_date := p_rdsv_rec.last_update_date;
15
16 end reset;
17
18
19 procedure Insert_Revenue_Distr(p_api_version IN NUMBER,
20 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
21 x_return_status OUT NOCOPY VARCHAR2,
22 x_msg_count OUT NOCOPY NUMBER,
23 x_msg_data OUT NOCOPY VARCHAR2,
24 p_rdsv_rec IN rdsv_rec_type,
25 x_rdsv_rec OUT NOCOPY rdsv_rec_type) is
26 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Revenue_Distr';
27 l_api_version CONSTANT NUMBER := 1;
28 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
29 begin
30 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
31 p_init_msg_list,
32 '_PUB',
33 x_return_status);
34 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
35 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
36 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
37 RAISE OKC_API.G_EXCEPTION_ERROR;
38 END IF;
39 --
40 -- Call Before Logic Hook
41 --
42 g_rdsv_rec := p_rdsv_rec;
43 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
44 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
45 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
46 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
47 raise OKC_API.G_EXCEPTION_ERROR;
48 END IF;
49 reset(p_rdsv_rec);
50 OKS_REV_DISTR_PVT.create_Revenue_Distr(p_api_version,
51 p_init_msg_list,
52 x_return_status,
53 x_msg_count,
54 x_msg_data,
55 g_rdsv_rec,
56 x_rdsv_rec);
57 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
58 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
59 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
60 RAISE OKC_API.G_EXCEPTION_ERROR;
61 END IF;
62 --
63 -- Call After Logic Hook
64 --
65 g_rdsv_rec := x_rdsv_rec;
66 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
67 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
68 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
69 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
70 raise OKC_API.G_EXCEPTION_ERROR;
71 END IF;
72 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
73 EXCEPTION
74 WHEN OKC_API.G_EXCEPTION_ERROR THEN
75 x_return_status := OKC_API.HANDLE_EXCEPTIONS
76 (substr(l_api_name,1,26),
77 G_PKG_NAME,
78 'OKC_API.G_RET_STS_ERROR',
79 x_msg_count,
80 x_msg_data,
81 '_PUB');
82 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
83 x_return_status := OKC_API.HANDLE_EXCEPTIONS
84 (substr(l_api_name,1,26),
85 G_PKG_NAME,
86 'OKC_API.G_RET_STS_UNEXP_ERROR',
87 x_msg_count,
88 x_msg_data,
89 '_PUB');
90 WHEN OTHERS THEN
91 x_return_status := OKC_API.HANDLE_EXCEPTIONS
92 (substr(l_api_name,1,26),
93 G_PKG_NAME,
94 'OTHERS',
95 x_msg_count,
96 x_msg_data,
97 '_PUB');
98 end Insert_Revenue_Distr;
99
100 procedure Insert_Revenue_Distr(p_api_version IN NUMBER,
101 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
102 x_return_status OUT NOCOPY VARCHAR2,
103 x_msg_count OUT NOCOPY NUMBER,
104 x_msg_data OUT NOCOPY VARCHAR2,
105 p_rdsv_tbl IN rdsv_tbl_type,
106 x_rdsv_tbl OUT NOCOPY rdsv_tbl_type) is
107 i NUMBER;
108 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
109 begin
110 OKC_API.init_msg_list(p_init_msg_list);
111 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
112 if (p_rdsv_tbl.COUNT>0) then
113 i := p_rdsv_tbl.FIRST;
114 LOOP
115 Insert_Revenue_Distr(p_api_version=>p_api_version,
116 p_init_msg_list=>OKC_API.G_FALSE,
117 x_return_status=>l_return_status,
118 x_msg_count=>x_msg_count,
119 x_msg_data=>x_msg_data,
120 p_rdsv_rec=>p_rdsv_tbl(i),
121 x_rdsv_rec=>x_rdsv_tbl(i));
122 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
123 x_return_status := l_return_status;
124 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
125 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
126 x_return_status := l_return_status;
127 end if;
128 EXIT WHEN (i=p_rdsv_tbl.LAST);
129 i := p_rdsv_tbl.NEXT(i);
130 END LOOP;
131 end if;
132 exception
133 when others then NULL;
134 end Insert_Revenue_Distr;
135
136 procedure update_Revenue_Distr(p_api_version IN NUMBER,
137 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
138 x_return_status OUT NOCOPY VARCHAR2,
139 x_msg_count OUT NOCOPY NUMBER,
140 x_msg_data OUT NOCOPY VARCHAR2,
141 p_rdsv_rec IN rdsv_rec_type,
142 x_rdsv_rec OUT NOCOPY rdsv_rec_type) is
143 l_api_name CONSTANT VARCHAR2(30) := 'update_Revenue_Distr';
144 l_api_version CONSTANT NUMBER := 1;
145 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
146 begin
147 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
148 p_init_msg_list,
149 '_PUB',
150 x_return_status);
151 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
152 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
153 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
154 RAISE OKC_API.G_EXCEPTION_ERROR;
155 END IF;
156 --
157 -- Call Before Logic Hook
158 --
159 g_rdsv_rec := p_rdsv_rec;
160 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
161 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
162 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
163 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
164 raise OKC_API.G_EXCEPTION_ERROR;
165 END IF;
166 reset(p_rdsv_rec);
167 OKS_REV_DISTR_PVT.update_Revenue_Distr(p_api_version,
168 p_init_msg_list,
169 x_return_status,
170 x_msg_count,
171 x_msg_data,
172 g_rdsv_rec,
173 x_rdsv_rec);
174 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
175 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
176 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
177 RAISE OKC_API.G_EXCEPTION_ERROR;
178 END IF;
179 --
180 -- Call After Logic Hook
181 --
182 g_rdsv_rec := x_rdsv_rec;
183 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
184 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
185 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
186 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
187 raise OKC_API.G_EXCEPTION_ERROR;
188 END IF;
189 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
190 EXCEPTION
191 WHEN OKC_API.G_EXCEPTION_ERROR THEN
192 x_return_status := OKC_API.HANDLE_EXCEPTIONS
193 (substr(l_api_name,1,26),
194 G_PKG_NAME,
195 'OKC_API.G_RET_STS_ERROR',
196 x_msg_count,
197 x_msg_data,
198 '_PUB');
199 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
200 x_return_status := OKC_API.HANDLE_EXCEPTIONS
201 (substr(l_api_name,1,26),
202 G_PKG_NAME,
203 'OKC_API.G_RET_STS_UNEXP_ERROR',
204 x_msg_count,
205 x_msg_data,
206 '_PUB');
207 WHEN OTHERS THEN
208 x_return_status := OKC_API.HANDLE_EXCEPTIONS
209 (substr(l_api_name,1,26),
210 G_PKG_NAME,
211 'OTHERS',
212 x_msg_count,
213 x_msg_data,
214 '_PUB');
215 end update_Revenue_Distr;
216
217 procedure update_Revenue_Distr(p_api_version IN NUMBER,
218 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2,
222 p_rdsv_tbl IN rdsv_tbl_type,
223 x_rdsv_tbl OUT NOCOPY rdsv_tbl_type) is
224 i NUMBER;
225 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
226 begin
227 OKC_API.init_msg_list(p_init_msg_list);
228 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
229 if (p_rdsv_tbl.COUNT>0) then
230 i := p_rdsv_tbl.FIRST;
231 LOOP
232 update_Revenue_Distr(p_api_version=>p_api_version,
233 p_init_msg_list=>OKC_API.G_FALSE,
234 x_return_status=>l_return_status,
235 x_msg_count=>x_msg_count,
236 x_msg_data=>x_msg_data,
237 p_rdsv_rec=>p_rdsv_tbl(i),
238 x_rdsv_rec=>x_rdsv_tbl(i));
239 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
240 x_return_status := l_return_status;
241 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
242 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
243 x_return_status := l_return_status;
244 end if;
245 EXIT WHEN (i=p_rdsv_tbl.LAST);
246 i := p_rdsv_tbl.NEXT(i);
247 END LOOP;
248 end if;
249 exception
250 when others then NULL;
251 end update_Revenue_Distr;
252
253 procedure delete_Revenue_Distr(p_api_version IN NUMBER,
254 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2,
258 p_rdsv_rec IN rdsv_rec_type) is
259 l_api_name CONSTANT VARCHAR2(30) := 'delete_Revenue_Distr';
260 l_api_version CONSTANT NUMBER := 1;
261 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
262 begin
263 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
264 p_init_msg_list,
265 '_PUB',
266 x_return_status);
267 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
268 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
269 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
270 RAISE OKC_API.G_EXCEPTION_ERROR;
271 END IF;
272 --
273 -- Call Before Logic Hook
274 --
275 g_rdsv_rec := p_rdsv_rec;
276 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
277 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
278 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
279 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
280 raise OKC_API.G_EXCEPTION_ERROR;
281 END IF;
282 reset(p_rdsv_rec);
283 OKS_REV_DISTR_PVT.delete_Revenue_Distr(p_api_version,
284 p_init_msg_list,
285 x_return_status,
286 x_msg_count,
287 x_msg_data,
288 g_rdsv_rec);
289 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
290 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
291 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
292 RAISE OKC_API.G_EXCEPTION_ERROR;
293 END IF;
294 --
295 -- Call After Logic Hook
296 --
297 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
298 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
299 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
300 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
301 raise OKC_API.G_EXCEPTION_ERROR;
302 END IF;
303 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
304 EXCEPTION
305 WHEN OKC_API.G_EXCEPTION_ERROR THEN
306 x_return_status := OKC_API.HANDLE_EXCEPTIONS
307 (substr(l_api_name,1,26),
308 G_PKG_NAME,
309 'OKC_API.G_RET_STS_ERROR',
310 x_msg_count,
311 x_msg_data,
312 '_PUB');
313 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
314 x_return_status := OKC_API.HANDLE_EXCEPTIONS
315 (substr(l_api_name,1,26),
316 G_PKG_NAME,
317 'OKC_API.G_RET_STS_UNEXP_ERROR',
318 x_msg_count,
319 x_msg_data,
320 '_PUB');
321 WHEN OTHERS THEN
322 x_return_status := OKC_API.HANDLE_EXCEPTIONS
323 (substr(l_api_name,1,26),
324 G_PKG_NAME,
325 'OTHERS',
326 x_msg_count,
327 x_msg_data,
328 '_PUB');
329 end delete_Revenue_Distr;
330
331 procedure delete_Revenue_Distr(p_api_version IN NUMBER,
332 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
333 x_return_status OUT NOCOPY VARCHAR2,
334 x_msg_count OUT NOCOPY NUMBER,
335 x_msg_data OUT NOCOPY VARCHAR2,
336 p_rdsv_tbl IN rdsv_tbl_type) is
337 i NUMBER;
338 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
339 begin
340 OKC_API.init_msg_list(p_init_msg_list);
341 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
342 if (p_rdsv_tbl.COUNT>0) then
343 i := p_rdsv_tbl.FIRST;
344 LOOP
345 delete_Revenue_Distr(p_api_version=>p_api_version,
346 p_init_msg_list=>OKC_API.G_FALSE,
347 x_return_status=>l_return_status,
348 x_msg_count=>x_msg_count,
349 x_msg_data=>x_msg_data,
350 p_rdsv_rec=>p_rdsv_tbl(i));
351 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
352 x_return_status := l_return_status;
353 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
354 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
355 x_return_status := l_return_status;
356 end if;
357 EXIT WHEN (i=p_rdsv_tbl.LAST);
358 i := p_rdsv_tbl.NEXT(i);
359 END LOOP;
360 end if;
361 exception
362 when others then NULL;
363 end delete_Revenue_Distr;
364
365 procedure lock_Revenue_Distr(p_api_version IN NUMBER,
366 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
367 x_return_status OUT NOCOPY VARCHAR2,
368 x_msg_count OUT NOCOPY NUMBER,
369 x_msg_data OUT NOCOPY VARCHAR2,
370 p_rdsv_rec IN rdsv_rec_type) is
371 l_api_name CONSTANT VARCHAR2(30) := 'lock_Revenue_Distr';
372 l_api_version CONSTANT NUMBER := 1;
373 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
374 begin
375 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
376 p_init_msg_list,
377 '_PUB',
378 x_return_status);
379 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
380 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
381 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
382 RAISE OKC_API.G_EXCEPTION_ERROR;
383 END IF;
384 --
385 -- Call Before Logic Hook
386 --
387 g_rdsv_rec := p_rdsv_rec;
388 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
389 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
390 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
391 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
392 raise OKC_API.G_EXCEPTION_ERROR;
393 END IF;
394 reset(p_rdsv_rec);
395 OKS_REV_DISTR_PVT.lock_Revenue_Distr(p_api_version,
396 p_init_msg_list,
397 x_return_status,
398 x_msg_count,
399 x_msg_data,
400 g_rdsv_rec);
401 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
402 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
403 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
404 RAISE OKC_API.G_EXCEPTION_ERROR;
405 END IF;
406 --
407 -- Call After Logic Hook
408 --
409 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
410 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
411 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
412 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
413 raise OKC_API.G_EXCEPTION_ERROR;
414 END IF;
415 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
416 EXCEPTION
417 WHEN OKC_API.G_EXCEPTION_ERROR THEN
418 x_return_status := OKC_API.HANDLE_EXCEPTIONS
419 (substr(l_api_name,1,26),
420 G_PKG_NAME,
421 'OKC_API.G_RET_STS_ERROR',
422 x_msg_count,
423 x_msg_data,
424 '_PUB');
425 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
426 x_return_status := OKC_API.HANDLE_EXCEPTIONS
427 (substr(l_api_name,1,26),
428 G_PKG_NAME,
429 'OKC_API.G_RET_STS_UNEXP_ERROR',
430 x_msg_count,
431 x_msg_data,
432 '_PUB');
433 WHEN OTHERS THEN
434 x_return_status := OKC_API.HANDLE_EXCEPTIONS
435 (substr(l_api_name,1,26),
436 G_PKG_NAME,
437 'OTHERS',
438 x_msg_count,
439 x_msg_data,
440 '_PUB');
441 end lock_Revenue_Distr;
442
443
444 procedure lock_Revenue_Distr(p_api_version IN NUMBER,
445 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
446 x_return_status OUT NOCOPY VARCHAR2,
447 x_msg_count OUT NOCOPY NUMBER,
448 x_msg_data OUT NOCOPY VARCHAR2,
449 p_rdsv_tbl IN rdsv_tbl_type) is
450 i NUMBER;
451 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
452 begin
453 OKC_API.init_msg_list(p_init_msg_list);
454 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
455 if (p_rdsv_tbl.COUNT>0) then
456 i := p_rdsv_tbl.FIRST;
457 LOOP
458 lock_Revenue_Distr(p_api_version=>p_api_version,
459 p_init_msg_list=>OKC_API.G_FALSE,
460 x_return_status=>l_return_status,
461 x_msg_count=>x_msg_count,
462 x_msg_data=>x_msg_data,
463 p_rdsv_rec=>p_rdsv_tbl(i));
464 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
465 x_return_status := l_return_status;
466 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
467 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
468 x_return_status := l_return_status;
469 end if;
470 EXIT WHEN (i=p_rdsv_tbl.LAST);
471 i := p_rdsv_tbl.NEXT(i);
472 END LOOP;
473 end if;
474 exception
475 when others then NULL;
476 end lock_Revenue_Distr;
477
478 procedure validate_Revenue_Distr(p_api_version IN NUMBER,
479 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
480 x_return_status OUT NOCOPY VARCHAR2,
481 x_msg_count OUT NOCOPY NUMBER,
482 x_msg_data OUT NOCOPY VARCHAR2,
483 p_rdsv_rec IN rdsv_rec_type) is
484 l_api_name CONSTANT VARCHAR2(30) := 'validate_Revenue_Distr';
485 l_api_version CONSTANT NUMBER := 1;
486 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
487 begin
488 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
489 p_init_msg_list,
490 '_PUB',
491 x_return_status);
492 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
493 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
494 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
495 RAISE OKC_API.G_EXCEPTION_ERROR;
496 END IF;
497 --
498 -- Call Before Logic Hook
499 --
500 g_rdsv_rec := p_rdsv_rec;
501 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'B');
502 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
503 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
504 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
505 raise OKC_API.G_EXCEPTION_ERROR;
506 END IF;
507 reset(p_rdsv_rec);
508 OKS_REV_DISTR_PVT.validate_Revenue_Distr(p_api_version,
509 p_init_msg_list,
510 x_return_status,
511 x_msg_count,
512 x_msg_data,
513 g_rdsv_rec);
514 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
515 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
516 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
517 RAISE OKC_API.G_EXCEPTION_ERROR;
518 END IF;
519 --
520 -- Call After Logic Hook
521 --
522 okc_util.call_user_hook(x_return_status, g_pkg_name, l_api_name, 'A');
523 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
524 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
525 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
526 raise OKC_API.G_EXCEPTION_ERROR;
527 END IF;
528 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
529 EXCEPTION
530 WHEN OKC_API.G_EXCEPTION_ERROR THEN
531 x_return_status := OKC_API.HANDLE_EXCEPTIONS
532 (substr(l_api_name,1,26),
533 G_PKG_NAME,
534 'OKC_API.G_RET_STS_ERROR',
535 x_msg_count,
536 x_msg_data,
537 '_PUB');
538 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
539 x_return_status := OKC_API.HANDLE_EXCEPTIONS
540 (substr(l_api_name,1,26),
541 G_PKG_NAME,
542 'OKC_API.G_RET_STS_UNEXP_ERROR',
543 x_msg_count,
544 x_msg_data,
545 '_PUB');
546 WHEN OTHERS THEN
547 x_return_status := OKC_API.HANDLE_EXCEPTIONS
548 (substr(l_api_name,1,26),
549 G_PKG_NAME,
550 'OTHERS',
551 x_msg_count,
552 x_msg_data,
553 '_PUB');
554 end validate_Revenue_Distr;
555
556
557
558
559
560
561
562
563 procedure validate_Revenue_Distr(p_api_version IN NUMBER,
564 p_init_msg_list IN VARCHAR2 default OKC_API.G_FALSE,
565 x_return_status OUT NOCOPY VARCHAR2,
566 x_msg_count OUT NOCOPY NUMBER,
567 x_msg_data OUT NOCOPY VARCHAR2,
568 p_rdsv_tbl IN rdsv_tbl_type) is
569 i NUMBER;
570 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
571 begin
572 OKC_API.init_msg_list(p_init_msg_list);
573 x_return_status:= OKC_API.G_RET_STS_SUCCESS;
574 if (p_rdsv_tbl.COUNT>0) then
575 i := p_rdsv_tbl.FIRST;
576 LOOP
577 validate_Revenue_Distr(p_api_version=>p_api_version,
578 p_init_msg_list=>OKC_API.G_FALSE,
579 x_return_status=>l_return_status,
580 x_msg_count=>x_msg_count,
581 x_msg_data=>x_msg_data,
582 p_rdsv_rec=>p_rdsv_tbl(i));
583 if (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) then
584 x_return_status := l_return_status;
585 raise OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
586 elsif (l_return_status = OKC_API.G_RET_STS_ERROR) then
587 x_return_status := l_return_status;
588 end if;
589 EXIT WHEN (i=p_rdsv_tbl.LAST);
590 i := p_rdsv_tbl.NEXT(i);
591 END LOOP;
592 end if;
593 exception
594 when others then NULL;
595 end validate_Revenue_Distr;
596
597 FUNCTION GET_GL_CODE_COMBINATION ( P_Id IN Varchar2,
598 P_Org_Id IN Number,
599 x_return_status OUT NOCOPY Varchar2 ) RETURN Varchar2 IS
600
601 --Cursor modified as part of bug# 3599223
602 -- CURSOR CUR_CHART_ACC Is
603 -- SELECT HOU.ORGANIZATION_ID
604 -- FROM hr_organization_units HOU
605 -- ,MTL_PARAMETERS MP
606 -- ,FND_PRODUCT_GROUPS
607 -- WHERE HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
608 -- AND EXISTS ( SELECT 1
609 -- FROM HR_ORGANIZATION_INFORMATION HOI1
610 -- WHERE HOI1.ORGANIZATION_ID = HOU.ORGANIZATION_ID
611 -- AND HOI1.ORG_INFORMATION1 = 'INV'
612 -- AND HOI1.ORG_INFORMATION2 = 'Y'
613 -- AND (HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS')
614 -- AND EXISTS ( SELECT 1
615 -- FROM HR_ORGANIZATION_INFORMATION HOI2,
616 -- GL_SETS_OF_BOOKS GSOB
617 -- WHERE HOI2.ORGANIZATION_ID = HOU.ORGANIZATION_ID
618 -- AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
619 -- AND TO_NUMBER(HOI2.ORG_INFORMATION1) = GSOB.SET_OF_BOOKS_ID)
620 -- AND HOU.ORGANIZATION_ID = P_ORG_ID;
621 --mchoudha bug#4939280
622 --Commented the FND_PRODUCT_GROUPS in the select clause to prevent the
623 --Merge cartesian join
624 CURSOR CUR_CHART_ACC Is
625 SELECT GSOB.CHART_OF_ACCOUNTS_ID
626 FROM HR_ORGANIZATION_UNITS HOU
627 ,MTL_PARAMETERS MP
628 --,FND_PRODUCT_GROUPS
629 ,GL_SETS_OF_BOOKS GSOB
630 ,HR_ORGANIZATION_INFORMATION HOI2
631 WHERE HOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
632 AND EXISTS ( SELECT 1
633 FROM HR_ORGANIZATION_INFORMATION HOI1
634 WHERE HOI1.ORGANIZATION_ID = HOU.ORGANIZATION_ID
635 AND HOI1.ORG_INFORMATION1 = 'INV'
636 AND HOI1.ORG_INFORMATION2 = 'Y'
637 AND (HOI1.ORG_INFORMATION_CONTEXT || '') = 'CLASS')
638 AND HOI2.ORGANIZATION_ID = HOU.ORGANIZATION_ID
639 AND ( HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
640 AND TO_NUMBER(HOI2.ORG_INFORMATION1) = GSOB.SET_OF_BOOKS_ID
641 AND HOU.ORGANIZATION_ID = P_ORG_ID;
642
643 l_chart_id NUMBER;
644
645 Cursor cur_delr IS
646 select CONCATENATED_SEGMENT_DELIMITER
647 from fnd_id_flex_structures_vl
648 where APPLICATION_ID = 101
649 and ID_FLEX_CODE = 'GL#'
650 and ID_FLEX_NUM = l_chart_id;
651
652 Cursor cur_col_name IS
653 select APPLICATION_COLUMN_NAME
654 from fnd_id_flex_segments_vl
655 where APPLICATION_ID = 101
656 and ID_FLEX_CODE = 'GL#'
657 and ID_FLEX_NUM = l_chart_id
658 order by SEGMENT_NUM;
659
660 l_delimiter Varchar2(5);
661 l_column Varchar2(600);
662 first_segment Boolean:= TRUE;
663 l_sel_column Varchar2(1000);
664 l_select_stmt Varchar2(4000);
665 l_return Varchar2(1995);
666
667 BEGIN
668
669 x_return_status := OKC_API.G_RET_STS_SUCCESS;
670
671 open cur_chart_acc;
672 fetch cur_chart_acc into l_chart_id;
673 close cur_chart_acc;
674
675 open cur_delr;
676 fetch cur_delr into l_delimiter;
677 close cur_delr;
678
679 open cur_col_name;
680 fetch cur_col_name INTO l_column;
681 loop
682 exit when cur_col_name%notfound;
683
684 if first_segment then
685 l_sel_column := l_column;
686 else
687 l_sel_column := l_sel_column || '||' || '''' || l_delimiter || '''' || '||' || l_column;
688 end if;
689
690 fetch cur_col_name INTO l_column;
691 first_segment := False;
692 End loop;
693 Close cur_col_name;
694
695 l_select_stmt := 'Select ' || l_sel_column ||
696 ' From Gl_Code_combinations Where Code_Combination_Id = ' || p_id;
697
698 EXECUTE IMMEDIATE l_select_stmt into l_return;
699
700 RETURN(l_return);
701 EXCEPTION
702 WHEN OTHERS THEN
703 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
704 return(l_return);
705 END GET_GL_CODE_COMBINATION;
706
707 END OKS_REV_DISTR_PUB ;